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