Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
82.95%
107 / 129
61.90%
13 / 21
 CRAP
0.00%
0 / 1
Pgsql
82.95%
107 / 129
61.90%
13 / 21
 87.61
0.00%
0 / 1
 __construct
100.00%
2 / 2
100.00%
1 / 1
 2
 connect
90.00%
9 / 10
0.00%
0 / 1
 7.05
 setOptions
94.44%
17 / 18
0.00%
0 / 1
 8.01
 hasOptions
100.00%
1 / 1
100.00%
1 / 1
 3
 beginTransaction
100.00%
5 / 5
100.00%
1 / 1
 1
 commit
100.00%
5 / 5
100.00%
1 / 1
 1
 rollback
100.00%
5 / 5
100.00%
1 / 1
 1
 isSuccess
0.00%
0 / 1
0.00%
0 / 1
 12
 query
58.82%
10 / 17
0.00%
0 / 1
 10.42
 prepare
56.25%
9 / 16
0.00%
0 / 1
 7.09
 bindParams
100.00%
6 / 6
100.00%
1 / 1
 3
 execute
90.91%
10 / 11
0.00%
0 / 1
 7.04
 fetch
100.00%
3 / 3
100.00%
1 / 1
 2
 fetchAll
100.00%
4 / 4
100.00%
1 / 1
 2
 disconnect
100.00%
3 / 3
100.00%
1 / 1
 2
 escape
100.00%
1 / 1
100.00%
1 / 1
 2
 getLastId
100.00%
3 / 3
100.00%
1 / 1
 1
 getNumberOfRows
66.67%
2 / 3
0.00%
0 / 1
 2.15
 getNumberOfAffectedRows
57.14%
4 / 7
0.00%
0 / 1
 3.71
 getVersion
100.00%
2 / 2
100.00%
1 / 1
 1
 getTables
100.00%
6 / 6
100.00%
1 / 1
 3
1<?php
2/**
3 * Pop PHP Framework (https://www.popphp.org/)
4 *
5 * @link       https://github.com/popphp/popphp-framework
6 * @author     Nick Sagona, III <dev@noladev.com>
7 * @copyright  Copyright (c) 2009-2026 NOLA Interactive, LLC.
8 * @license    https://www.popphp.org/license     New BSD License
9 */
10
11/**
12 * @namespace
13 */
14namespace Pop\Db\Adapter;
15
16/**
17 * PostgreSQL database adapter class
18 *
19 * @category   Pop
20 * @package    Pop\Db
21 * @author     Nick Sagona, III <dev@noladev.com>
22 * @copyright  Copyright (c) 2009-2026 NOLA Interactive, LLC.
23 * @license    https://www.popphp.org/license     New BSD License
24 * @version    6.7.0
25 */
26class Pgsql extends AbstractAdapter
27{
28
29    /**
30     * Statement index
31     * @var int
32     */
33    protected static int $statementIndex = 0;
34
35    /**
36     * Connection string
37     * @var ?string
38     */
39    protected ?string $connectionString = null;
40
41    /**
42     * Prepared statement name
43     * @var ?string
44     */
45    protected ?string $statementName = null;
46
47    /**
48     * Prepared statement string
49     * @var string
50     */
51    protected ?string $statementString = null;
52
53    /**
54     * Prepared statement parameters
55     * @var array
56     */
57    protected array $parameters = [];
58
59    /**
60     * Constructor
61     *
62     * Instantiate the PostgreSQL database connection object
63     *
64     * @param  array $options
65     */
66    public function __construct(array $options = [])
67    {
68        if (!empty($options)) {
69            $this->connect($options);
70        }
71    }
72
73    /**
74     * Connect to the database
75     *
76     * @param  array $options
77     * @return Pgsql
78     */
79    public function connect(array $options = []): Pgsql
80    {
81        if (!empty($options)) {
82            $this->setOptions($options);
83        } else if (!$this->hasOptions()) {
84            $this->throwError('Error: The proper database credentials were not passed.');
85        }
86
87        $pg_connect = (isset($this->options['persist']) && ($this->options['persist'])) ? 'pg_pconnect' : 'pg_connect';
88
89        $this->connection = (isset($this->options['type'])) ?
90            $pg_connect($this->connectionString, $this->options['type']) : $pg_connect($this->connectionString);
91
92        if (!$this->connection) {
93            $this->throwError('PostgreSQL Connection Error: Unable to connect to the database.');
94        }
95
96        return $this;
97    }
98
99    /**
100     * Set database connection options
101     *
102     * @param  array $options
103     * @return Pgsql
104     */
105    public function setOptions(array $options): Pgsql
106    {
107        if (!isset($options['host'])) {
108            $options['host'] = 'localhost';
109        }
110
111        $this->options = $options;
112
113        if (!$this->hasOptions()) {
114            $this->throwError('Error: The proper database credentials were not passed.');
115        }
116
117        $this->connectionString = "host=" . $this->options['host'] . " dbname=" . $this->options['database'] .
118            " user=" . $this->options['username'] . " password=" . $this->options['password'];
119
120        if (isset($this->options['port'])) {
121            $this->connectionString .= " port=" . $this->options['port'];
122        }
123        if (isset($this->options['hostaddr'])) {
124            $this->connectionString .= " hostaddr=" . $this->options['hostaddr'];
125        }
126        if (isset($this->options['connect_timeout'])) {
127            $this->connectionString .= " connect_timeout=" . $this->options['connect_timeout'];
128        }
129        if (isset($this->options['options'])) {
130            $this->connectionString .= " options=" . $this->options['options'];
131        }
132        if (isset($this->options['sslmode'])) {
133            $this->connectionString .= " sslmode=" . $this->options['sslmode'];
134        }
135
136        return $this;
137    }
138
139    /**
140     * Has database connection options
141     *
142     * @return bool
143     */
144    public function hasOptions(): bool
145    {
146        return (isset($this->options['database']) && isset($this->options['username']) && isset($this->options['password']));
147    }
148
149    /**
150     * Begin a transaction
151     *
152     * @return Pgsql
153     */
154    public function beginTransaction(): Pgsql
155    {
156        $this->getTransactionManager()->enter(
157            beginFunc: function () { $this->query('BEGIN TRANSACTION'); },
158            savepointFunc: function (string $sp) { $this->query('SAVEPOINT ' . $sp); },
159        );
160
161        return $this;
162    }
163
164    /**
165     * Commit a transaction
166     *
167     * @return Pgsql
168     */
169    public function commit(): Pgsql
170    {
171        $this->getTransactionManager()->leave(true,
172            commitFunc: function () { $this->query('COMMIT'); },
173            savepointReleaseFunc: function (string $sp) { $this->query('RELEASE SAVEPOINT ' . $sp); },
174        );
175
176        return $this;
177    }
178
179    /**
180     * Rollback a transaction
181     *
182     * @return Pgsql
183     */
184    public function rollback(): Pgsql
185    {
186        $this->getTransactionManager()->leave(false,
187            rollbackFunc: function () { $this->query('ROLLBACK'); },
188            savepointRollbackFunc: function (string $sp) { $this->query('ROLLBACK TO SAVEPOINT ' . $sp); },
189        );
190
191        return $this;
192    }
193
194    /**
195     * Check if transaction is success
196     *
197     * @return bool
198     */
199    public function isSuccess(): bool
200    {
201        return ((($this->result !== null) && ($this->result !== false)) && (!$this->hasError()));
202    }
203
204    /**
205     * Execute a SQL query directly
206     *
207     * @param  mixed $sql
208     * @return Pgsql
209     */
210    public function query(mixed $sql): Pgsql
211    {
212        if ($sql instanceof \Pop\Db\Sql\AbstractSql) {
213            $sql = (string)$sql;
214        }
215
216        if (!($this->result = pg_query($this->connection, $sql))) {
217            $pgError = pg_last_error($this->connection);
218            if ($this->profiler !== null) {
219                $this->profiler->addStep();
220                $this->profiler->current->setQuery($sql);
221                $this->profiler->current->addError($pgError);
222            }
223            $this->throwError($pgError);
224        } else if ($this->profiler !== null) {
225            $this->profiler->addStep();
226            $this->profiler->current->setQuery($sql);
227        }
228
229        if ($this->profiler !== null) {
230            $this->profiler->current->finish();
231            if ($this->profiler->hasDebugger()) {
232                $this->profiler->debugger()->save();
233            }
234        }
235
236        return $this;
237    }
238
239    /**
240     * Prepare a SQL query
241     *
242     * @param  mixed $sql
243     * @return Pgsql
244     */
245    public function prepare(mixed $sql): Pgsql
246    {
247        if ($sql instanceof \Pop\Db\Sql\AbstractSql) {
248            $sql = (string)$sql;
249        }
250
251        $this->statementString = $sql;
252        $this->statementName   = 'pop_db_adapter_pgsql_statement_' . ++static::$statementIndex;
253        $this->statement       = pg_prepare($this->connection, $this->statementName, $this->statementString);
254
255        if ($this->statement === false) {
256            $pgError = pg_last_error();
257            if ($this->profiler !== null) {
258                $this->profiler->addStep();
259                $this->profiler->current->setQuery($sql);
260                $this->profiler->current->addError($pgError);
261            }
262            $this->throwError('PostgreSQL Statement Error: ' . $pgError);
263        } else if ($this->profiler !== null) {
264            $this->profiler->addStep();
265            $this->profiler->current->setQuery($sql);
266        }
267
268        return $this;
269    }
270
271    /**
272     * Bind parameters to a prepared SQL query
273     *
274     * @param  array $params
275     * @return Pgsql
276     */
277    public function bindParams(array $params): Pgsql
278    {
279        if ($this->profiler !== null) {
280            $this->profiler->current->addParams($params);
281        }
282
283        $this->parameters = [];
284
285        foreach ($params as $param) {
286            $this->parameters[] = $param;
287        }
288
289        return $this;
290    }
291
292    /**
293     * Execute a prepared SQL query
294     *
295     * @return Pgsql
296     */
297    public function execute(): Pgsql
298    {
299        if (($this->statement === null) || ($this->statementString === null) || ($this->statementName === null)) {
300            $this->throwError('Error: The database statement resource is not currently set.');
301        }
302
303        if (count($this->parameters) > 0)  {
304            $this->result     = pg_execute($this->connection, $this->statementName, $this->parameters);
305            $this->parameters = [];
306        } else {
307            $this->query($this->statementString);
308        }
309
310        if ($this->profiler !== null) {
311            $this->profiler->current->finish();
312            if ($this->profiler->hasDebugger()) {
313                $this->profiler->debugger()->save();
314            }
315        }
316
317        return $this;
318    }
319
320    /**
321     * Fetch and return a row from the result
322     *
323     * @return mixed
324     */
325    public function fetch(): mixed
326    {
327        if ($this->result === null) {
328            $this->throwError('Error: The database result resource is not currently set.');
329        }
330
331        return pg_fetch_array($this->result, null, PGSQL_ASSOC);
332    }
333
334    /**
335     * Fetch and return all rows from the result
336     *
337     * @return array
338     */
339    public function fetchAll(): array
340    {
341        $rows = [];
342
343        while (($row = $this->fetch())) {
344            $rows[] = $row;
345        }
346
347        return $rows;
348    }
349
350    /**
351     * Disconnect from the database
352     *
353     * @return void
354     */
355    public function disconnect(): void
356    {
357        if ($this->isConnected()) {
358            pg_close($this->connection);
359        }
360
361        parent::disconnect();
362    }
363
364    /**
365     * Escape the value
366     *
367     * @param  ?string $value
368     * @return string
369     */
370    public function escape(?string $value = null): string
371    {
372        return (!empty($value)) ? pg_escape_string($this->connection, $value) : '';
373    }
374
375    /**
376     * Return the last ID of the last query
377     *
378     * @return int
379     */
380    public function getLastId(): int
381    {
382        $insertQuery = pg_query($this->connection, "SELECT lastval();");
383        $insertRow   = pg_fetch_row($insertQuery);
384        return $insertRow[0];
385    }
386
387    /**
388     * Return the number of rows from the last query
389     *
390     * @throws Exception
391     * @return int
392     */
393    public function getNumberOfRows(): int
394    {
395        if ($this->result === null) {
396            $this->throwError('Error: The database result resource is not currently set.');
397        }
398
399        return pg_num_rows($this->result);
400    }
401
402    /**
403     * Return the number of affected rows from the last query
404     *
405     * @throws Exception
406     * @return int
407     */
408    public function getNumberOfAffectedRows(): int
409    {
410        $count = 0;
411
412        if ($this->statement !== null) {
413            $count = pg_affected_rows($this->statement);
414        } else if ($this->result !== null) {
415            $count = pg_affected_rows($this->result);
416        } else {
417            $this->throwError('Error: The database result resource is not currently set.');
418        }
419
420        return $count;
421    }
422
423    /**
424     * Return the database version
425     *
426     * @return string
427     */
428    public function getVersion(): string
429    {
430        $version = pg_version($this->connection);
431        return 'PostgreSQL ' . $version['server'];
432    }
433
434    /**
435     * Return the tables in the database
436     *
437     * @return array
438     */
439    public function getTables(): array
440    {
441        $tables = [];
442
443        $this->query("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'");
444        while (($row = $this->fetch())) {
445            foreach($row as $value) {
446                $tables[] = $value;
447            }
448        }
449
450        return $tables;
451    }
452
453}