1: <?php
2:
3: /**
4: * Postgres database server access classes
5: *
6: * @package objSQL
7: * @version 3.2.0
8: * @author MT Jordan <mtjo62@gmail.com>
9: * @copyright 2013
10: * @license zlib/libpng
11: * @link http://objsql.sourceforge.net
12: */
13:
14:
15: /*************************************************************************************************************
16: * Begin database connection/utility class
17: ************************************************************************************************************/
18:
19:
20: class pgsql_connection extends obj_access
21: {
22: /**********************************************
23: * Internal variables
24: *********************************************/
25:
26: /**
27: * Database connection object
28: *
29: * @access private
30: * @var mixed
31: */
32: private $obj_connection;
33:
34: /**
35: * Database connection information
36: *
37: * @access private
38: * @var array
39: */
40: private $obj_datasource;
41:
42: /**********************************************
43: * Class methods
44: *********************************************/
45: /**
46: * Constructor
47: *
48: * @access public
49: * @param array $datasource
50: */
51: public function __construct( $datasource )
52: {
53: $this->obj_datasource = $datasource;
54: }
55:
56: /**
57: * Returns database connection instance
58: *
59: * @access protected
60: * @return mixed
61: */
62: protected function obj_db_connection()
63: {
64: if ( ( array_key_exists( 5, $this->obj_datasource ) ) && is_numeric( trim( $this->obj_datasource[5] ) ) )
65: $db_connection = pg_connect( "host={$this->obj_datasource[1]} port={$this->obj_datasource[5]} dbname={$this->obj_datasource[4]} user={$this->obj_datasource[2]} password={$this->obj_datasource[3]}" );
66: else
67: $db_connection = pg_connect( "host={$this->obj_datasource[1]} dbname={$this->obj_datasource[4]} user={$this->obj_datasource[2]} password={$this->obj_datasource[3]}" );
68:
69: return ( is_resource( $db_connection ) ) ? $this->obj_connection = $db_connection: false;
70: }
71:
72: /**
73: * Closes connection to database server
74: *
75: * @access protected
76: * @return bool
77: */
78: protected function obj_db_close()
79: {
80: return pg_close( $this->obj_connection );
81: }
82:
83: /**
84: * Returns error flag for current connection instance
85: *
86: * @access protected
87: * @return bool
88: */
89: protected function obj_db_error()
90: {
91: return ( pg_last_error( $this->obj_connection ) ) ? true : false;
92: }
93:
94: /**
95: * Returns error message for current connection instance
96: *
97: * @access protected
98: * @return str
99: */
100: protected function obj_db_message()
101: {
102: return ( $this->obj_db_error() ) ? pg_last_error( $this->obj_connection ) : null;
103: }
104:
105: /**
106: * Escapes string data for database insertion
107: *
108: * @access protected
109: * @param mixed $data
110: * @return mixed
111: */
112: protected function obj_db_escape_data( $data )
113: {
114: return pg_escape_string( $data );
115: }
116:
117: /**
118: * Returns database server information
119: *
120: * @access protected
121: * @return array
122: */
123: protected function obj_db_info()
124: {
125: $version = pg_version( $this->obj_connection );
126:
127: return array( $version['client'], pg_client_encoding( $this->obj_connection ), $this->obj_datasource[4] );
128: }
129:
130: /**
131: * Returns row count for named table with arguments
132: * Returns -1 if undetermined or failure
133: *
134: * @access protected
135: * @param str $table
136: * @param str $cols
137: * @param str $where
138: * @return int
139: */
140: protected function obj_db_rowcount( $table, $cols=false, $where=false )
141: {
142: $query_cols = ( !trim( $cols ) ) ? '*' : $cols;
143: $query_where = ( !trim( $where ) ) ? '' : "WHERE $where";
144:
145: $query_stmt = pg_query( $this->obj_connection, "SELECT COUNT($query_cols) FROM $table $query_where" );
146:
147: if ( !is_resource( $query_stmt ) )
148: return -1;
149:
150: while ( $num_rows = pg_fetch_row( $query_stmt ) )
151: $rowcount = ( $num_rows[0] >= 0 ) ? $num_rows[0] : -1;
152:
153: pg_free_result( $query_stmt );
154:
155: return $rowcount;
156: }
157: }
158:
159:
160: /*************************************************************************************************************
161: * End database connection class
162: ************************************************************************************************************/
163:
164:
165: /*************************************************************************************************************
166: * Begin database statement class
167: ************************************************************************************************************/
168:
169:
170: class pgsql_statement
171: {
172: /**********************************************
173: * Internal variables
174: *********************************************/
175:
176: /**
177: * Database connection object
178: *
179: * @access private
180: * @var mixed
181: */
182: private $obj_connection;
183:
184: /**
185: * Query string
186: *
187: * @access private
188: * @var str
189: */
190: private $obj_query;
191:
192: /**********************************************
193: * Class methods
194: *********************************************/
195:
196: /**
197: * Constructor
198: *
199: * @access public
200: * @param str $query
201: * @param mixed $connection
202: */
203: function __construct( $query, $connection )
204: {
205: $this->obj_connection = $connection->obj_connection;
206: $this->obj_query = $query;
207: }
208:
209: /**
210: * Executes general query and returns resultset resource
211: *
212: * @access public
213: * @return mixed
214: */
215: public function obj_query_execute()
216: {
217: $query_stmt = pg_query( $this->obj_connection, $this->obj_query );
218:
219: return ( is_resource( $query_stmt ) ) ? new pgsql_resultset( $query_stmt ) : false;
220: }
221: }
222:
223:
224: /*************************************************************************************************************
225: * End database statement class
226: ************************************************************************************************************/
227:
228:
229: /*************************************************************************************************************
230: * Begin database prepared statement class
231: ************************************************************************************************************/
232:
233:
234: class pgsql_prepare
235: {
236: /**********************************************
237: * Internal variables
238: *********************************************/
239:
240: /**
241: * Database connection object
242: *
243: * @access private
244: * @var mixed
245: */
246: private $obj_connection;
247:
248: /**
249: * Prepared statement binding parameters
250: *
251: * @access private
252: * @var array
253: */
254: private $obj_parameter = array();
255:
256: /**
257: * Prepared statement instance
258: *
259: * @access private
260: * @var mixed
261: */
262: private $obj_prepare_instance;
263:
264: /**
265: * Prepared statement name
266: *
267: * @access private
268: * @var str
269: */
270: private $obj_query_name;
271:
272: /**
273: * Resultset resource
274: *
275: * @access private
276: * @var mixed
277: */
278: private $obj_result_resource;
279:
280: /**********************************************
281: * Class methods
282: *********************************************/
283:
284: /**
285: * Constructor
286: *
287: * @access public
288: * @param str $query
289: * @param mixed $connection
290: */
291: function __construct( $query, $connection )
292: {
293: $this->obj_connection = $connection->obj_connection;
294: $this->obj_query_name = substr( MD5( microtime() ), 0, 12 );
295: $this->obj_prepare_init( $query );
296: }
297:
298: /**
299: * Sets parameters for prepared statement
300: *
301: * @access public
302: * @param mixed $param
303: */
304: public function obj_bind( $param )
305: {
306: $this->obj_parameter[] = trim( $param );
307: }
308:
309: /**
310: * Destroys prepared statement object
311: *
312: * @access public
313: * @return bool
314: */
315: public function obj_close_statement()
316: {
317: $query_stmt = pg_query( $this->obj_connection, "DEALLOCATE \"{$this->obj_query_name}\"" );
318:
319: return ( is_resource( $query_stmt ) ) ? true : false;
320: }
321:
322: /**
323: * Executes prepared statement and returns resultset resource
324: *
325: * @access public
326: * @return mixed
327: */
328: public function obj_execute()
329: {
330: $this->obj_result_resource = pg_execute( $this->obj_connection, $this->obj_query_name, $this->obj_parameter );
331:
332: return ( is_resource( $this->obj_result_resource ) ) ? new pgsql_resultset( $this->obj_result_resource ) : false;
333: }
334:
335: /**
336: * Releases statement resource memory and resets binding parameters
337: *
338: * @access public
339: * @return bool
340: */
341: public function obj_free_statement()
342: {
343: $this->obj_parameter = array();
344:
345: return pg_free_result( $this->obj_result_resource );
346: }
347:
348: /**
349: * Returns prepared statement resource
350: *
351: * @access private
352: * @param str $query
353: * @return mixed
354: */
355: private function obj_prepare_init( $query )
356: {
357: $this->obj_prepare_instance = pg_prepare( $this->obj_connection, $this->obj_query_name, $this->obj_prepare_sql( $query ) );
358:
359: return ( is_resource( $this->obj_prepare_instance ) ) ? $this->obj_prepare_instance : false;
360: }
361:
362: /**
363: * Replaces (?) markers in prepared statement query string with ($1,$2,etc)
364: *
365: * @access private
366: * @return str
367: */
368: private function obj_prepare_sql( $query )
369: {
370: $query_parts = explode( '?', $query );
371: $query_sql = $query_parts[0];
372:
373: for ( $i = 1; $i < count( $query_parts ); $i++ )
374: $query_sql .= "$$i {$query_parts[$i]}";
375:
376: return $query_sql;
377: }
378: }
379:
380:
381: /*************************************************************************************************************
382: * End database prepared statement class
383: ************************************************************************************************************/
384:
385:
386: /*************************************************************************************************************
387: * Begin database resultset class
388: ************************************************************************************************************/
389:
390:
391: class pgsql_resultset
392: {
393: /**********************************************
394: * Internal variables
395: *********************************************/
396:
397: /**
398: * Resultset query record
399: *
400: * @access private
401: * @var array
402: */
403: private $obj_record = array();
404:
405: /**
406: * Query result object
407: *
408: * @access private
409: * @var mixed
410: */
411: private $obj_result;
412:
413: /**********************************************
414: * Class methods
415: *********************************************/
416:
417: /**
418: * Constructor
419: *
420: * @access public
421: * @param mixed $result
422: */
423: public function __construct( $result )
424: {
425: $this->obj_result = $result;
426: }
427:
428: /**
429: * Return number of affected rows from insert/delete/update query
430: * Returns -1 if undetermined or failure
431: *
432: * @access public
433: * @return int
434: */
435: public function obj_affected_rows()
436: {
437: $affected_rows = pg_affected_rows( $this->obj_result );
438:
439: return ( $affected_rows >= 0 ) ? $affected_rows : -1;
440: }
441:
442: /**
443: * Returns resultset resource as associative array
444: *
445: * @access public
446: * @return mixed
447: */
448: public function obj_fetch_assoc()
449: {
450: $result = pg_fetch_assoc( $this->obj_result );
451:
452: return ( is_array( $result ) ) ? $this->obj_record = $result : null;
453: }
454:
455: /**
456: * Returns resultset resource as numeric array
457: *
458: * @access public
459: * @return mixed
460: */
461: public function obj_fetch_num()
462: {
463: $result = pg_fetch_row( $this->obj_result );
464:
465: return ( is_array( $result ) ) ? $this->obj_record = $result : null;
466: }
467:
468: /**
469: * Returns resultset resource as object
470: *
471: * @access public
472: * @return mixed
473: */
474: public function obj_fetch_object()
475: {
476: $result = pg_fetch_object( $this->obj_result );
477:
478: return ( is_object( $result ) ) ? $this->obj_record = $result : null;
479: }
480:
481: /**
482: * Returns resultset record
483: *
484: * @access public
485: * @param mixed $field
486: * @return mixed
487: */
488: public function obj_field( $field )
489: {
490: //get_magic_quotes deprecated in php 5.4 - added for backwards compatibility
491: return ( get_magic_quotes_runtime() ) ? stripslashes( $this->obj_record[$field] ) : $this->obj_record[$field];
492: }
493:
494: /**
495: * Frees resultset memory and destroys resultset object
496: *
497: * @access public
498: * @return bool
499: */
500: public function obj_free_result()
501: {
502: $this->obj_record = array();
503:
504: return pg_free_result( $this->obj_result );
505: }
506:
507: /**
508: * Return number of fields from query
509: * Returns -1 if undetermined or failure
510: *
511: * @access public
512: * @return int
513: */
514: public function obj_num_fields()
515: {
516: $num_cols = pg_num_fields( $this->obj_result );
517:
518: return ( $num_cols >= 0 ) ? $num_cols : -1;
519: }
520:
521: /**
522: * Return number rows from query
523: * Returns -1 if undetermined or failure
524: *
525: * @access public
526: * @return int
527: */
528: public function obj_num_rows()
529: {
530: $num_rows = pg_num_rows( $this->obj_result );
531:
532: return ( $num_rows >= 0 ) ? $num_rows : -1;
533: }
534: }
535:
536:
537: /*************************************************************************************************************
538: * End database resultset class
539: ************************************************************************************************************/
540:
541:
542: /*************************************************************************************************************
543: * Begin database transaction class
544: ************************************************************************************************************/
545:
546:
547: class pgsql_transaction
548: {
549: /**********************************************
550: * Internal variables
551: *********************************************/
552:
553: /**
554: * Database connection object
555: *
556: * @access private
557: * @var mixed
558: */
559: private $obj_connection;
560:
561: /**********************************************
562: * Class methods
563: *********************************************/
564:
565: /**
566: * Constructor
567: *
568: * @access public
569: * @param mixed $connection
570: */
571: public function __construct( $connection )
572: {
573: $this->obj_connection = $connection;
574:
575: //turn off autocommit
576: pg_query( $this->obj_connection, 'BEGIN' );
577: }
578:
579: /**
580: * Commits transaction for current transaction instance
581: *
582: * @access public
583: * @return bool
584: */
585: public function obj_commit()
586: {
587: return ( is_resource( pg_query( $this->obj_connection, 'END' ) ) ) ? true : false;
588: }
589:
590: /**
591: * Rollbacks transaction for current transaction instance
592: *
593: * @access public
594: * @param str $savepoint
595: * @return bool
596: */
597: public function obj_rollback( $savepoint=false )
598: {
599: $rollback = ( !$savepoint ) ? 'ROLLBACK' : "ROLLBACK TO SAVEPOINT $savepoint";
600:
601: return ( is_resource( pg_query( $this->obj_connection, $rollback ) ) ) ? true : false;
602: }
603:
604: /**
605: * Creates transaction savepoint for current transaction instance
606: *
607: * @access public
608: * @param str $savepoint
609: * @return bool
610: */
611: public function obj_savepoint( $savepoint )
612: {
613: return ( is_resource( pg_query( $this->obj_connection, "SAVEPOINT $savepoint" ) ) ) ? true : false;
614: }
615: }
616:
617:
618: /*************************************************************************************************************
619: * End database transaction class
620: ************************************************************************************************************/
621:
622:
623: ?>