1: <?php
2: /**
3: * This file contains the implementation of the abstract base class DBWorker
4: * for the PostreSQL database using the extension pgsql.
5: *
6: * @package Database
7: *
8: * @author Oleg Schildt
9: */
10:
11: namespace SmartFactory\DatabaseWorkers;
12:
13: use function \SmartFactory\debugger;
14:
15: /**
16: * This is the class for the PostreSQL database using the extension pgsql.
17: *
18: * This is a wrapper around the database connectivity. It offers a universal
19: * common way for working with databases of different types. Currently, MySQL, PostgreSQL and
20: * MS SQL are supported. If in the future, there will be a better solution, or
21: * the current solution turns out to be inefficient in a new version of PHP,
22: * we can easily reimplement the DB wrapper without touching the business logic code.
23: * Adding support for new database types is also much easier with this wrapping approach.
24: *
25: * @see PostgreSQL_DBWorker
26: * @see MSSQL_DBWorker
27: *
28: * @author Oleg Schildt
29: */
30: class PostgreSQL_DBWorker extends DBWorker
31: {
32: /**
33: * Flag for setting the connection to read only.
34: *
35: * @var bool $read_only
36: *
37: * @author Oleg Schildt
38: */
39: protected bool $read_only = false;
40:
41: /**
42: * Internal PgSql\Connection object.
43: *
44: * @var ?\PgSql\Connection
45: *
46: * @author Oleg Schildt
47: */
48: protected ?\PgSql\Connection $connection = null;
49:
50: /**
51: * Internal \PgSql\Result object.
52: *
53: * @var ?\PgSql\Result
54: *
55: * @author Oleg Schildt
56: */
57: protected ?\PgSql\Result $result = null;
58:
59: /**
60: * Internal \PgSql\Result object for statements.
61: *
62: * @var ?\PgSql\Result
63: *
64: * @author Oleg Schildt
65: */
66: protected ?\PgSql\Result $statement = null;
67:
68: /**
69: * Internal variable for storing of the last prepared query.
70: *
71: * @var ?string
72: *
73: * @author Oleg Schildt
74: */
75: protected ?string $prepared_query = null;
76:
77: /**
78: * Internal variable for storing of the current fetched row
79: * from the result of the last retrieving query.
80: *
81: * @var null|bool|array
82: *
83: * @author Oleg Schildt
84: */
85: protected null|bool|array $row = null;
86:
87: /**
88: * Internal variable for storing of the column names
89: * from the result of the last retrieving query.
90: *
91: * @var ?array
92: *
93: * @author Oleg Schildt
94: */
95: protected ?array $field_names = null;
96:
97: /**
98: * Creates a clone of the dbworker that is using the same open
99: * connection.
100: *
101: * This might be useful if you want to execute some additional
102: * queries while iteration through the active results of a select query.
103: *
104: * @return PostgreSQL_DBWorker
105: * Returns the clone of this dbworker.
106: *
107: * @author Oleg Schildt
108: */
109: public function create_clone(): PostgreSQL_DBWorker
110: {
111: $cln = new PostgreSQL_DBWorker();
112:
113: $cln->is_clone = true;
114:
115: $cln->db_server = $this->db_server;
116: $cln->db_port = $this->db_port;
117: $cln->db_name = $this->db_name;
118: $cln->db_user = $this->db_user;
119: $cln->db_password = $this->db_password;
120: $cln->read_only = $this->read_only;
121:
122: return $cln;
123: } // create_clone
124:
125: /**
126: * Constructor.
127: *
128: * @author Oleg Schildt
129: */
130: public function __construct()
131: {
132: } // __construct
133:
134: /**
135: * Destructor.
136: *
137: * @return void
138: *
139: * @author Oleg Schildt
140: */
141: public function __destruct()
142: {
143: if (!$this->is_clone) {
144: $this->close_connection();
145: }
146: } // __destruct
147:
148: /**
149: * This auxiliary function reads the data from large object.
150: *
151: * @param int $oid
152: * The oid of the large object.
153: *
154: * @return string
155: * Returns the data read from the largeobject.
156: *
157: * @used_by PostgreSQL_DBWorker::field_by_name()
158: * @used_by PostgreSQL_DBWorker::field_by_num()
159: *
160: * @throws DBWorkerException
161: * It throws an exception in the case of any errors.
162: *
163: * @author Oleg Schildt
164: */
165: protected function read_large_object(int $oid): string
166: {
167: $ohandle = @pg_lo_open($this->connection, $oid, "r");
168: if (empty($ohandle)) {
169: throw new DBWorkerException("Large object id is invalid!", DBWorker::ERR_STREAM_ERROR);
170: }
171:
172: $data = "";
173: while ($chunk = @pg_lo_read($ohandle)) {
174: $data .= $chunk;
175: }
176:
177: return $data;
178: } // read_large_object
179:
180: /**
181: * Initializes the dbworker with connection parameters.
182: *
183: * @param array $parameters
184: * Connection settings as an associative array in the form key => value:
185: *
186: * - $parameters["db_server"] - server address.
187: * - $parameters["db_port"] - server port.
188: * - $parameters["db_name"] - database name.
189: * - $parameters["db_user"] - user name.
190: * - $parameters["db_password"] - user password.
191: * - $parameters["read_only"] - this parameter sets the connection to the read only mode.
192: *
193: * @return void
194: *
195: * @author Oleg Schildt
196: */
197: public function init(array $parameters): void
198: {
199: if (!empty($parameters["db_server"])) {
200: $this->db_server = $parameters["db_server"];
201: }
202: if (!empty($parameters["db_port"])) {
203: $this->db_name = $parameters["db_port"];
204: }
205: if (!empty($parameters["db_name"])) {
206: $this->db_name = $parameters["db_name"];
207: }
208: if (!empty($parameters["db_user"])) {
209: $this->db_user = $parameters["db_user"];
210: }
211: if (!empty($parameters["db_password"])) {
212: $this->db_password = $parameters["db_password"];
213: }
214: if (!empty($parameters["read_only"])) {
215: $this->read_only = $parameters["read_only"];
216: }
217: } // init
218:
219: /**
220: * Checks whether the extension (pgsql) is installed which is required for work with
221: * the PostreSQL database.
222: *
223: * @return bool
224: * The method should return true if the extension is installed, otherwise false.
225: *
226: * @see PostgreSQL_DBWorker::get_extension_name()
227: *
228: * @author Oleg Schildt
229: */
230: public function is_extension_installed(): bool
231: {
232: return function_exists("pg_connect");
233: } // is_extension_installed
234:
235: /**
236: * Returns the name of the required PHP extension - "pgsql".
237: *
238: * @return string
239: * Returns the name of the required PHP extension - "pgsql".
240: *
241: * @see PostgreSQL_DBWorker::is_extension_installed()
242: *
243: * @author Oleg Schildt
244: */
245: public function get_extension_name(): string
246: {
247: return "pgsql";
248: } // get_extension_name
249:
250: /**
251: * Returns the name of the supported database - "PostgreSQL Server".
252: *
253: * @return string
254: * Returns the name of the supported database - "PostgreSQL Server".
255: *
256: * @author Oleg Schildt
257: */
258: public function get_rdbms_name(): string
259: {
260: return "PostgreSQL Server";
261: } // get_rdbms_name
262:
263: /**
264: * Returns the connection state.
265: *
266: * @return bool
267: * Returns true if the connection is open, otherwise false.
268: *
269: * @see DBWorker::check_connection()
270: * @see PostgreSQL_DBWorker::connect()
271: * @see PostgreSQL_DBWorker::close_connection()
272: *
273: * @author Oleg Schildt
274: */
275: public function is_connected(): bool
276: {
277: return (!empty($this->connection) && pg_connection_status($this->connection) == PGSQL_CONNECTION_OK);
278: } // is_connected
279:
280: /**
281: * Retrieves the last errors occurred while execution of the query.
282: *
283: * @return string
284: * Returns the string of errors separated by the new line symbol.
285: *
286: * @author Oleg Schildt
287: */
288: protected function sys_get_errors(): string
289: {
290: $errors = error_get_last();
291: if (empty($errors)) {
292: return "";
293: }
294:
295: $message_array = [];
296:
297: foreach ($errors as $error) {
298: $message_array[$error['message']] = $error['message'];
299: }
300:
301: return implode("\n", $message_array);
302: } // sys_get_errors
303:
304: /**
305: * Establishes the connection to the database using the connection
306: * settings parameters specified by the initialization.
307: *
308: * @return void
309: *
310: * @throws DBWorkerException
311: * It throws an exception in the case of any errors.
312: *
313: * @see DBWorker::check_connection()
314: * @see PostgreSQL_DBWorker::is_connected()
315: * @see PostgreSQL_DBWorker::close_connection()
316: *
317: * @author Oleg Schildt
318: */
319: public function connect(): void
320: {
321: if ($this->is_connected()) {
322: return;
323: }
324:
325: if (!$this->connection) {
326: if (empty($this->db_server) || empty($this->db_user) || empty($this->db_password)) {
327: throw new DBWorkerException("Connection data is incomplete", DBWorker::ERR_CONNECTION_DATA_INCOMPLETE);
328: }
329:
330: if (empty($this->db_name)) {
331: throw new DBWorkerException("The database must be specified immediately by the connection!", DBWorker::ERR_CONNECTION_DATA_INCOMPLETE);
332: }
333:
334: $connection_string = "connect_timeout=20 options='--client_encoding=UTF8' ";
335: if (!empty($this->db_server)) {
336: $connection_string .= " host=" . $this->db_server;
337: }
338: if (!empty($this->db_port)) {
339: $connection_string .= " port=" . $this->db_port;
340: }
341: if (!empty($this->db_user)) {
342: $connection_string .= " user=" . $this->db_user;
343: }
344: if (!empty($this->db_name)) {
345: $connection_string .= " dbname=" . $this->db_name;
346: }
347: if (!empty($this->db_password)) {
348: $connection_string .= " password=" . $this->db_password;
349: }
350:
351: $this->connection = pg_connect($connection_string);
352: }
353:
354: if (!$this->connection) {
355: $err = $this->sys_get_errors();
356: $this->connection = null;
357:
358: throw new DBWorkerException($err, DBWorker::ERR_CONNECTION_FAILED);
359: }
360: } // connect
361:
362: /**
363: * Sets the database as working database.
364: *
365: * @param string $db_name
366: * The name of the database to be set as working database.
367: *
368: * @return void
369: *
370: * @throws DBWorkerException
371: * It throws an exception in the case of any errors.
372: *
373: * @author Oleg Schildt
374: */
375: public function use_database(string $db_name): void
376: {
377: throw new DBWorkerException("The database must be specified immediately by the connection!", DBWorker::ERR_CONNECTION_DATA_INCOMPLETE);
378: } // use_database
379:
380: /**
381: * Returns the name of the database schema if applicable.
382: *
383: * @return string
384: * Returns the name of the database schema if applicable, or an empty string.
385: *
386: * @see PostgreSQL_DBWorker::qualify_name_with_schema()
387: *
388: * @author Oleg Schildt
389: */
390: public function get_schema(): string
391: {
392: return "public";
393: } // get_schema
394:
395: /**
396: * Completes the name of a database object with the schema name if applicable.
397: *
398: * @param string $name
399: * The name of the database object to be completed with the schema name.
400: *
401: * @return string
402: * Returns the name of the database object with the schema name if applicable,
403: * otherwise the name of the database object remains unchanged.
404: *
405: * @see PostgreSQL_DBWorker::get_schema()
406: *
407: * @author Oleg Schildt
408: */
409: public function qualify_name_with_schema(string $name): string
410: {
411: $schema = $this->get_schema();
412:
413: if (!empty($schema)) {
414: $schema .= ".";
415: }
416:
417: return $schema . $name;
418: } // qualify_name_with_schema
419:
420: /**
421: * Executes the SQL query.
422: *
423: * @param string $query_string
424: * The SQL query to be executed.
425: *
426: * @return void
427: *
428: * @throws \Exception|DBWorkerException
429: * It throws an exception in the case of any errors.
430: *
431: * @author Oleg Schildt
432: */
433: public function execute_query(string $query_string): void
434: {
435: $this->check_connection();
436:
437: $this->last_query = $query_string;
438:
439: if ($this->logging) {
440: debugger()->debugMessage($this->last_query, "sql.log");
441: }
442:
443: $this->result = @pg_query($this->connection, $query_string);
444: if (!$this->result) {
445: throw new DBWorkerException(pg_last_error(), DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
446: }
447: } // execute_query
448:
449: /**
450: * Prepares the SQL query with bindable variables.
451: *
452: * @param string $query_string
453: * The SQL query to be prepared.
454: *
455: * @return void
456: *
457: * @throws DBWorkerException
458: * It throws an exception in the case of any errors.
459: *
460: * @see PostgreSQL_DBWorker::execute_prepared_query()
461: * @see PostgreSQL_DBWorker::free_prepared_query()
462: *
463: * @author Oleg Schildt
464: */
465: public function prepare_query(string $query_string): void
466: {
467: $this->check_connection();
468:
469: if ($this->statement) {
470: @pg_free_result($this->statement);
471: $this->statement = null;
472: }
473:
474: $counter = 1;
475: $query_string = preg_replace_callback("/\\?/", function () use (&$counter) {
476: return "$" . ($counter++);
477: }, $query_string);
478:
479: $this->last_query = $query_string;
480: $this->prepared_query = $query_string;
481:
482: $this->statement = @pg_prepare($this->connection, "", $query_string);
483: if (!$this->statement) {
484: throw new DBWorkerException(pg_last_error($this->connection), DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
485: }
486: } // prepare_query
487:
488: /**
489: * Stores long data from a stream.
490: *
491: * @param string $query_string
492: * The SQL query to be used for storing the long data.
493: *
494: * @param resource $stream
495: * The opened valid stream for reading the long data.
496: *
497: * Example:
498: * ```php
499: * $stream = fopen(".../large_binary.jpg", "rb");
500: *
501: * if(!$dbw->stream_long_data("UPDATE LARGE_DATA SET BLOB_DATA = ? WHERE ID = 1", $stream))
502: * {
503: * error reporting ...;
504: * }
505: * ```
506: *
507: * @return void
508: *
509: * @throws DBWorkerException
510: * It throws an exception in the case of any errors.
511: *
512: * @author Oleg Schildt
513: */
514: public function stream_long_data(string $query_string, $stream): void
515: {
516: $this->check_connection();
517:
518: if (!is_resource($stream)) {
519: throw new DBWorkerException("Stream is invalid!", DBWorker::ERR_STREAM_ERROR);
520: }
521:
522: if ($this->statement) {
523: @pg_free_result($this->statement);
524: $this->statement = null;
525: }
526:
527: $this->last_query = $query_string;
528: $this->prepared_query = $query_string;
529:
530: $oid = pg_lo_create($this->connection);
531:
532: $this->statement = @pg_prepare($this->connection, "", $query_string);
533: if (!$this->statement) {
534: throw new DBWorkerException(pg_last_error($this->connection), DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
535: }
536:
537: $this->result = @pg_execute($this->connection, "", [$oid]);
538: if (!$this->result) {
539: throw new DBWorkerException(pg_last_error(), DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
540: }
541:
542: $ohandle = @pg_lo_open($this->connection, $oid, "w");
543: if (empty($ohandle)) {
544: throw new DBWorkerException("Error writing from stream to PG large object!", DBWorker::ERR_STREAM_ERROR);
545: }
546:
547: while (!feof($stream)) {
548: if (!@pg_lo_write($ohandle, fread($stream, 8192))) {
549: fclose($stream);
550: @pg_lo_close($ohandle);
551: throw new DBWorkerException("Error writing from stream to PG large object!", DBWorker::ERR_STREAM_ERROR);
552: }
553: }
554:
555: @pg_lo_close($ohandle);
556: } // stream_long_data
557:
558: /**
559: * Executes the prepared SQL query.
560: *
561: * @param mixed ...$args
562: * The number of parameters may vary and be zero. An array can also be passed.
563: * These are parameters of the prepared query.
564: *
565: * @return void
566: *
567: * @throws \Exception|DBWorkerException
568: * It throws an exception in the case of any errors.
569: *
570: * @see PostgreSQL_DBWorker::prepare_query()
571: * @see PostgreSQL_DBWorker::free_prepared_query()
572: *
573: * @author Oleg Schildt
574: */
575: public function execute_prepared_query(...$args): void
576: {
577: $this->check_connection();
578:
579: if (empty($this->prepared_query) || empty($this->statement)) {
580: throw new DBWorkerException("No prepared query defined", DBWorker::ERR_QUERY_FAILED);
581: }
582:
583: if (count($args) == 1 && is_array($args[0])) {
584: $args = $args[0];
585: }
586:
587: $this->last_query = $this->prepared_query;
588:
589: if ($this->logging) {
590: debugger()->debugMessage($this->last_query, "sql.log");
591: }
592:
593: $this->result = @pg_execute($this->connection, "", $args);
594: if (!$this->result) {
595: throw new DBWorkerException(pg_last_error(), DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
596: }
597: } // execute_prepared_query
598:
599: /**
600: * Executes the SQL stored procedure.
601: *
602: * @param string $procedure
603: * The name of the SQL stored procedure.
604: *
605: * All subsequent parameters are the parameters of the SQL stored procedure.
606: *
607: * @return void
608: *
609: * @throws DBWorkerException
610: * It throws an exception in the case of any errors.
611: *
612: * @author Oleg Schildt
613: */
614: public function execute_procedure(string $procedure /* arg list */): void
615: {
616: $this->check_connection();
617:
618: $args = func_get_args();
619:
620: // prepare the arguments for placing in eval()
621: // escape single quotes
622:
623: $this->last_query = "";
624:
625: if (count($args) > 0) {
626: $proc_name = "";
627: $arg_list = "";
628:
629: $first = true;
630: foreach ($args as $argval) {
631: if ($first) {
632: $proc_name = $argval;
633: $first = false;
634: continue;
635: }
636:
637: if ($argval === null) {
638: $arg_list .= "null, ";
639: } elseif (is_int($argval)) {
640: $arg_list .= "$argval, ";
641: } elseif (is_float($argval)) {
642: $arg_list .= "$argval, ";
643: } else {
644: $arg_list .= "'" . $this->escape($argval) . "', ";
645: }
646: }
647:
648: $arg_list = trim($arg_list, ", ");
649:
650: $this->last_query = "CALL $proc_name($arg_list);";
651: }
652:
653: $this->execute_query($this->last_query);
654: } // execute_procedure
655:
656: /**
657: * Frees the prepared query.
658: *
659: * It should be called after all executions of the prepared query.
660: *
661: * @return void
662: *
663: * @throws DBWorkerException
664: * It throws an exception in the case of any errors.
665: *
666: * @see PostgreSQL_DBWorker::prepare_query()
667: * @see PostgreSQL_DBWorker::execute_prepared_query()
668: *
669: * @author Oleg Schildt
670: */
671: public function free_prepared_query(): void
672: {
673: $this->check_connection();
674:
675: if ($this->statement) {
676: @pg_free_result($this->statement);
677:
678: $this->statement = null;
679: }
680:
681: $this->statement = null;
682: $this->last_query = null;
683: $this->prepared_query = null;
684: } // free_prepared_query
685:
686: /**
687: * Closes the currently opened connection.
688: *
689: * @return void
690: *
691: * @see PostgreSQL_DBWorker::is_connected()
692: * @see PostgreSQL_DBWorker::connect()
693: *
694: * @author Oleg Schildt
695: */
696: public function close_connection(): void
697: {
698: $this->last_query = null;
699: $this->prepared_query = null;
700: $this->row = null;
701: $this->field_names = null;
702:
703: if ($this->statement) {
704: @pg_free_result($this->statement);
705: }
706:
707: if ($this->result) {
708: @pg_free_result($this->result);
709: }
710:
711: if ($this->connection) {
712: @pg_close($this->connection);
713: }
714:
715: $this->statement = null;
716: $this->connection = null;
717: $this->result = null;
718: } // close_connection
719:
720: /**
721: * Starts the transation.
722: *
723: * @return void
724: *
725: * @throws DBWorkerException
726: * It throws an exception in the case of any errors.
727: *
728: * @see PostgreSQL_DBWorker::commit_transaction()
729: * @see PostgreSQL_DBWorker::rollback_transaction()
730: *
731: * @author Oleg Schildt
732: */
733: public function start_transaction(): void
734: {
735: $this->check_connection();
736:
737: $this->execute_query("BEGIN");
738: } // start_transaction
739:
740: /**
741: * Commits the transation.
742: *
743: * @return void
744: *
745: * @throws DBWorkerException
746: * It throws an exception in the case of any errors.
747: *
748: * @see PostgreSQL_DBWorker::start_transaction()
749: * @see PostgreSQL_DBWorker::rollback_transaction()
750: *
751: * @author Oleg Schildt
752: */
753: public function commit_transaction(): void
754: {
755: $this->check_connection();
756:
757: $this->execute_query("COMMIT");
758: } // commit_transaction
759:
760: /**
761: * Rolls back the transation.
762: *
763: * @return void
764: *
765: * @throws DBWorkerException
766: * It throws an exception in the case of any errors.
767: *
768: * @see PostgreSQL_DBWorker::start_transaction()
769: * @see PostgreSQL_DBWorker::commit_transaction()
770: *
771: * @author Oleg Schildt
772: */
773: public function rollback_transaction(): void
774: {
775: $this->check_connection();
776:
777: $this->execute_query("ROLLBACK");
778: } // rollback_transaction
779:
780: /**
781: * Frees the result of the previously executed retrieving query.
782: *
783: * It should be called only for the retrieving queries.
784: *
785: * @return void
786: *
787: * @throws DBWorkerException
788: * It throws an exception in the case of any errors.
789: *
790: * @author Oleg Schildt
791: */
792: public function free_result(): void
793: {
794: $this->check_connection();
795:
796: if ($this->result) {
797: @pg_free_result($this->result);
798:
799: $this->result = null;
800: }
801:
802: $this->row = null;
803: $this->field_names = null;
804: $this->last_query = null;
805: } // free_result
806:
807: /**
808: * Returns the value of the auto increment field by the last insertion.
809: *
810: * WARNING! This method only works
811: * if there are no triggers with insertion commands.
812: *
813: * PostgreSQL recommends using the sequences like Oracle. If you want to use sequences,
814: * generate ID values with passing to {@see PostgreSQL_DBWorker::execute_query()} the sequence generation commands
815: * and write your inserts with explicit ID values.
816: *
817: * @return ?int
818: * Returns the value of the auto increment field by the last insertion.
819: *
820: * @throws DBWorkerException
821: * It throws an exception in the case of any errors.
822: *
823: * @author Oleg Schildt
824: */
825: public function insert_id(): ?int
826: {
827: $this->check_connection();
828:
829: $this->execute_query("select lastval() as iid");
830:
831: if (!$this->fetch_row()) {
832: $err = "Identity field cannot be retrieved";
833:
834: $this->free_result();
835:
836: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], "select lastval() as iid");
837: }
838:
839: $id = $this->field_by_name("iid");
840:
841: $this->free_result();
842:
843: return $id;
844: } // insert_id
845:
846: /**
847: * Fetches the next row of data from the result of the execution of the retrieving query.
848: *
849: * @return bool
850: * Returns true if the next row exists and has been successfully fetched, otherwise false.
851: *
852: * Example:
853: * ```php
854: * if(!$dbw->execute_query("SELECT FIRST_NAME, LAST_NAME FROM USERS"))
855: * {
856: * error reporting ...;
857: * }
858: *
859: * while($dbw->fetch_row())
860: * {
861: * echo $dbw->field_by_name("FIRST_NAME") . " " . $dbw->field_by_name("LAST_NAME") . "<br>";
862: * }
863: *
864: * $dbw->free_result();
865: * ```
866: *
867: * @throws DBWorkerException
868: * It throws an exception in the case of any errors.
869: *
870: * @author Oleg Schildt
871: */
872: public function fetch_row(): bool
873: {
874: $this->check_connection();
875:
876: if (!$this->result) {
877: $err = "Result is empty!";
878: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
879: }
880:
881: $this->row = @pg_fetch_array($this->result, null, PGSQL_ASSOC);
882:
883: if (!$this->row) {
884: return false;
885: }
886:
887: if (!$this->field_names) {
888: $this->field_names = array_keys($this->row);
889: }
890:
891: return true;
892: } // fetch_row
893:
894: /**
895: * Fetches all rows from the result into an array.
896: *
897: * @param array &$rows
898: * Target array for loading the results.
899: *
900: * @param ?array $dimension_keys
901: * Array of the column names that should be used as dimensions.
902: *
903: * Per default, the rows are fetched as two-dimensional array.
904: *
905: * Example:
906: * ```php
907: * $rows = [];
908: * $dbw->fetch_array($rows);
909: *
910: * rows[n] = ["col1" => "val1", "col2" => "val2", "col3" => "val3", ...]
911: * ```
912: * If the dimension columns are specified, their values are used for the dimensions.
913: *
914: * Example:
915: * ```php
916: * $rows = [];
917: * $dbw->fetch_array($rows, ["col1", "col2"]);
918: *
919: * rows["val1"]["val2"] = ["col3" => "val3", ...]
920: * ```
921: *
922: * @return int
923: * Returns the number of the fetched rows. It might be also 0.
924: *
925: * @throws DBWorkerException
926: * It throws an exception in the case of any errors.
927: *
928: * @author Oleg Schildt
929: */
930: public function fetch_array(array &$rows, ?array $dimension_keys = null): int
931: {
932: $this->check_connection();
933:
934: $rows = [];
935:
936: if (!empty($dimension_keys)) {
937: $dimension_keys = array_flip($dimension_keys);
938: }
939:
940: $counter = 0;
941:
942: if (!$this->result) {
943: $err = "Result is empty!";
944: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
945: }
946:
947: while ($row = @pg_fetch_array($this->result, null, PGSQL_ASSOC)) {
948: $counter++;
949:
950: if (!$this->field_names) {
951: $this->field_names = array_keys($row);
952: }
953:
954: if (empty($dimension_keys)) {
955: $rows[] = $row;
956: } else {
957: $dimensions = array_intersect_key($row, $dimension_keys);
958: $row = array_diff_key($row, $dimension_keys);
959:
960: $reference = &$rows;
961:
962: foreach ($dimensions as $dval) {
963: if (empty($reference[$dval])) {
964: $reference[$dval] = [];
965: }
966: $reference = &$reference[$dval];
967: }
968:
969: $reference = $row;
970:
971: unset($reference);
972: }
973: }
974:
975: return $counter;
976: } // fetch_array
977:
978: /**
979: * Returns the number of the rows fetched by the last retrieving query.
980: *
981: * @return int
982: * Returns the number of the rows fetched by the last retrieving query.
983: *
984: * @throws DBWorkerException
985: * It throws an exception in the case of any errors.
986: *
987: * @author Oleg Schildt
988: */
989: public function fetched_count(): int
990: {
991: $this->check_connection();
992:
993: if (!$this->result || !is_object($this->result)) {
994: $err = "Result is empty!";
995: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
996: }
997:
998: return pg_affected_rows($this->result);
999: } // fetched_count
1000:
1001: /**
1002: * Returns the number of the rows affected by the last modification query.
1003: *
1004: * @return int
1005: * Returns the number of the rows affected by the last modification query.
1006: *
1007: * @throws DBWorkerException
1008: * It throws an exception in the case of any errors.
1009: *
1010: * @author Oleg Schildt
1011: */
1012: public function affected_count(): int
1013: {
1014: $this->check_connection();
1015:
1016: if (!$this->result || !is_object($this->result)) {
1017: $err = "Result is empty!";
1018: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
1019: }
1020:
1021: return pg_affected_rows($this->result);
1022: } // affected_count
1023:
1024: /**
1025: * Returns the number of the fields in the result of the last retrieving query.
1026: *
1027: * @return int
1028: * Returns the number of the fields in the result of the last retrieving query.
1029: *
1030: * @throws DBWorkerException
1031: * It throws an exception in the case of any errors.
1032: *
1033: * @author Oleg Schildt
1034: */
1035: public function field_count(): int
1036: {
1037: $this->check_connection();
1038:
1039: if (!$this->result || !is_object($this->result)) {
1040: $err = "Result is empty!";
1041: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
1042: }
1043:
1044: return pg_num_fields($this->result);
1045: } // field_count
1046:
1047: /**
1048: * Returns the value of a field specified by name.
1049: *
1050: * @param string $name
1051: * The name of the field.
1052: *
1053: * @param int $type
1054: * The type of the field.
1055: *
1056: * @return mixed
1057: * Returns the value of a field specified by name. In the case
1058: * of any error returns null.
1059: *
1060: * @throws DBWorkerException
1061: * It throws an exception in the case of any errors.
1062: *
1063: * @see PostgreSQL_DBWorker::field_by_num()
1064: * @see PostgreSQL_DBWorker::field_name()
1065: *
1066: * @author Oleg Schildt
1067: */
1068: public function field_by_name(string $name, int $type = self::DB_AS_IS): mixed
1069: {
1070: $this->check_connection();
1071:
1072: if (!$this->row) {
1073: return null;
1074: }
1075:
1076: if (!array_key_exists($name, $this->row)) {
1077: trigger_error("Field with the name '$name' does not exist in the result set!", E_USER_WARNING);
1078: return null;
1079: }
1080:
1081: if ($type == DBWorker::DB_LARGE_OBJECT_STREAM) {
1082: return $this->read_large_object($this->row[$name]);
1083: }
1084:
1085: if (($type == DBWorker::DB_DATE || $type == DBWorker::DB_DATETIME) && !empty($this->row[$name])) {
1086: return strtotime($this->row[$name]);
1087: }
1088:
1089: return $this->row[$name];
1090: } // field_by_name
1091:
1092: /**
1093: * Returns the value of a field specified by number.
1094: *
1095: * @param int $num
1096: * The number of the field.
1097: *
1098: * @param int $type
1099: * The type of the field.
1100: *
1101: * @return mixed
1102: * Returns the value of a field specified by number. In the case
1103: * of any error returns null.
1104: *
1105: * @throws DBWorkerException
1106: * It throws an exception in the case of any errors.
1107: *
1108: * @see PostgreSQL_DBWorker::field_by_name()
1109: * @see PostgreSQL_DBWorker::field_info_by_num()
1110: * @see PostgreSQL_DBWorker::field_name()
1111: *
1112: * @author Oleg Schildt
1113: */
1114: public function field_by_num(int $num, int $type = self::DB_AS_IS): mixed
1115: {
1116: $this->check_connection();
1117:
1118: if (!$this->row) {
1119: return null;
1120: }
1121:
1122: if (!array_key_exists($num, $this->field_names)) {
1123: trigger_error("Field with the index $num does not exist in the result set!", E_USER_WARNING);
1124: return null;
1125: }
1126:
1127: if ($type == DBWorker::DB_LARGE_OBJECT_STREAM) {
1128: return $this->read_large_object($this->row[$this->field_names[$num]]);
1129: }
1130:
1131: if (($type == DBWorker::DB_DATE || $type == DBWorker::DB_DATETIME) && !empty($this->row[$this->field_names[$num]])) {
1132: return strtotime($this->row[$this->field_names[$num]]);
1133: }
1134:
1135: return $this->row[$this->field_names[$num]];
1136: } // field_by_num
1137:
1138: /**
1139: * Returns the name of the field by number.
1140: *
1141: * @param int $num
1142: * The number of the field.
1143: *
1144: * @return ?string
1145: * Returns the value of a field specified by number as an object with properties. In the case
1146: * of any error returns null.
1147: *
1148: * @throws DBWorkerException
1149: * It throws an exception in the case of any errors.
1150: *
1151: * @see PostgreSQL_DBWorker::field_by_num()
1152: * @see PostgreSQL_DBWorker::field_info_by_num()
1153: *
1154: * @author Oleg Schildt
1155: */
1156: public function field_name(int $num): ?string
1157: {
1158: $this->check_connection();
1159:
1160: $info = $this->field_info_by_num($num);
1161: if (!$info) {
1162: return null;
1163: }
1164:
1165: return $info["name"] ?? "";
1166: } // field_name
1167:
1168: /**
1169: * Returns the meta information about the field as an object with properties.
1170: *
1171: * @param int $num
1172: * The number of the field.
1173: *
1174: * @return ?array
1175: * Returns the associative array with properties. In the case
1176: * of any error returns null.
1177: *
1178: * - $info["name"] - name of the field.
1179: * - $info["type"] - type of the field.
1180: * - $info["size"] - size of the field.
1181: * - $info["binary"] - whether the filed is binary.
1182: * - $info["numeric"] - whether the filed is numeric.
1183: * - $info["datetime"] - whether the filed is datetime.
1184: *
1185: * @throws DBWorkerException
1186: * It throws an exception in the case of any errors.
1187: *
1188: * @see PostgreSQL_DBWorker::field_by_num()
1189: * @see PostgreSQL_DBWorker::field_name()
1190: *
1191: * @author Oleg Schildt
1192: */
1193: public function field_info_by_num(int $num): ?array
1194: {
1195: $this->check_connection();
1196:
1197: if (!$this->result) {
1198: $err = "Result is empty!";
1199: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
1200: }
1201:
1202: $field_info = [];
1203:
1204: $field_info["name"] = pg_field_name($this->result, $num);
1205: $field_info["type"] = pg_field_type($this->result, $num);
1206: $field_info["size"] = pg_field_size($this->result, $num);
1207:
1208: $field_info["string"] = in_array($field_info["type"], ["text", "varchar", "char"]) ? 1 : 0;
1209: $field_info["binary"] = ($field_info["type"] == "bytea") ? 1 : 0;
1210: $field_info["numeric"] = ($field_info["type"] == "int" || $field_info["type"] == "float" || $field_info["type"] == "numeric") ? 1 : 0;
1211: $field_info["datetime"] = ($field_info["type"] == "timestamp" || $field_info["type"] == "date") ? 1 : 0;
1212:
1213: return $field_info;
1214: } // field_info_by_num
1215:
1216: /**
1217: * Escapes the string so that it can be used in the query without causing an error.
1218: *
1219: * @param string $str
1220: * The string to be escaped.
1221: *
1222: * @return string
1223: * Returns the escaped string.
1224: *
1225: * @see PostgreSQL_DBWorker::format_date()
1226: * @see PostgreSQL_DBWorker::format_datetime()
1227: *
1228: * @author Oleg Schildt
1229: */
1230: public function escape(string $str): string
1231: {
1232: return pg_escape_string($this->connection, $str);
1233: } // escape
1234:
1235: /**
1236: * Formats the date to a string compatible for the corresponding database.
1237: *
1238: * @param int $date
1239: * The date value as timestamp.
1240: *
1241: * @return string
1242: * Returns the string representation of the date compatible for the corresponding database.
1243: *
1244: * @see PostgreSQL_DBWorker::escape()
1245: * @see PostgreSQL_DBWorker::format_datetime()
1246: *
1247: * @author Oleg Schildt
1248: */
1249: public function format_date(int $date): string
1250: {
1251: if (empty($date)) {
1252: return $this->quotes_or_null("");
1253: }
1254:
1255: return $this->quotes_or_null(date("Y-m-d", $date));
1256: } // format_date
1257:
1258: /**
1259: * Formats the date/time to a string compatible for the corresponding database.
1260: *
1261: * @param int $datetime
1262: * The date/time value as timestamp.
1263: *
1264: * @return string
1265: * Returns the string representation of the date/time compatible for the corresponding database.
1266: *
1267: * @see PostgreSQL_DBWorker::escape()
1268: * @see PostgreSQL_DBWorker::format_date()
1269: *
1270: * @author Oleg Schildt
1271: */
1272: public function format_datetime(int $datetime): string
1273: {
1274: if (empty($datetime)) {
1275: return $this->quotes_or_null("");
1276: }
1277:
1278: return $this->quotes_or_null(date("Y-m-d H:i:s", $datetime));
1279: } // format_datetime
1280:
1281: /**
1282: * Prepares the value for putting to a query depending on its type. It does escaping, formatting
1283: * and quotation if necessary.
1284: *
1285: * @param mixed $value
1286: * The value to be formatted.
1287: *
1288: * @param int $type
1289: * The type of the value.
1290: *
1291: * @return string
1292: * Returns the prepared value.
1293: *
1294: * @throws DBWorkerException
1295: * It throws an exception in the case of any errors.
1296: *
1297: * @author Oleg Schildt
1298: */
1299: function prepare_for_query(mixed $value, int $type): string
1300: {
1301: if (empty($value) && (string)$value != "0") {
1302: return "null";
1303: } else {
1304: return match ($type) {
1305: DBWorker::DB_NUMBER => $this->number_or_null($value),
1306: DBWorker::DB_DATETIME => $this->format_datetime($value),
1307: DBWorker::DB_DATE => $this->format_date($value),
1308: default => $this->quotes_or_null($value)
1309: };
1310: }
1311: } // prepare_for_query
1312:
1313: /**
1314: * Builds simple select query based on parameters.
1315: *
1316: * It is used for building queries with limits.
1317: *
1318: * @param string $table
1319: * The name of the table.
1320: *
1321: * @param array $fields
1322: * The list of request fields.
1323: *
1324: * @param string $where_clause
1325: * The where clause that should restrict the result.
1326: *
1327: * @param string $order_clause
1328: * The order clause to sort the results.
1329: *
1330: * @param int $limit
1331: * The limit how many records should be loaded. 0 for unlimited.
1332: *
1333: * @return string
1334: * Returns the built query.
1335: *
1336: * @author Oleg Schildt
1337: */
1338: function build_select_query(string $table, array $fields, string $where_clause, string $order_clause, int $limit): string
1339: {
1340: $query = "select\n";
1341:
1342: $query .= implode(", ", $fields) . "\n";
1343:
1344: $query .= "from " . $table . "\n";
1345:
1346: if (!empty($where_clause)) {
1347: $query .= $where_clause . "\n";
1348: }
1349:
1350: if (!empty($where_clause)) {
1351: $query .= $order_clause . "\n";
1352: }
1353:
1354: if (!empty($limit)) {
1355: $query .= "limit " . $limit . "\n";
1356: }
1357:
1358: return $query;
1359: } // build_select_query
1360: } // PostgreSQL_DBWorker