1: <?php
2:
3: /**
4: * Postgres database server access classes
5: *
6: * @package objSQL
7: * @version 3.0.1
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:
132: /*************************************************************************************************************
133: * End database connection class
134: ************************************************************************************************************/
135:
136:
137: /*************************************************************************************************************
138: * Begin database statement class
139: ************************************************************************************************************/
140:
141:
142: class pgsql_statement
143: {
144: /**********************************************
145: * Internal variables
146: *********************************************/
147:
148: /**
149: * Database connection object
150: *
151: * @access private
152: * @var mixed
153: */
154: private $obj_connection;
155:
156: /**
157: * Query string
158: *
159: * @access private
160: * @var str
161: */
162: private $obj_query;
163:
164: /**********************************************
165: * Class methods
166: *********************************************/
167:
168: /**
169: * Constructor
170: *
171: * @access public
172: * @param str $query
173: * @param mixed $connection
174: */
175: function __construct( $query, $connection )
176: {
177: $this->obj_connection = $connection->obj_connection;
178: $this->obj_query = $query;
179: }
180:
181: /**
182: * Executes general query and returns resultset resource
183: *
184: * @access public
185: * @return mixed
186: */
187: public function obj_query_execute()
188: {
189: $query_stmt = pg_query( $this->obj_connection, $this->obj_query );
190:
191: return ( is_resource( $query_stmt ) ) ? new pgsql_resultset( $query_stmt ) : false;
192: }
193: }
194:
195:
196: /*************************************************************************************************************
197: * End database statement class
198: ************************************************************************************************************/
199:
200:
201: /*************************************************************************************************************
202: * Begin database prepared statement class
203: ************************************************************************************************************/
204:
205:
206: class pgsql_prepare
207: {
208: /**********************************************
209: * Internal variables
210: *********************************************/
211:
212: /**
213: * Database connection object
214: *
215: * @access private
216: * @var mixed
217: */
218: private $obj_connection;
219:
220: /**
221: * Prepared statement binding parameters
222: *
223: * @access private
224: * @var array
225: */
226: private $obj_parameter = array();
227:
228: /**
229: * Prepared statement instance
230: *
231: * @access private
232: * @var mixed
233: */
234: private $obj_prepare_instance;
235:
236: /**
237: * Prepared statement name
238: *
239: * @access private
240: * @var str
241: */
242: private $obj_query_name;
243:
244: /**
245: * Resultset resource
246: *
247: * @access private
248: * @var mixed
249: */
250: private $obj_result_resource;
251:
252: /**********************************************
253: * Class methods
254: *********************************************/
255:
256: /**
257: * Constructor
258: *
259: * @access public
260: * @param str $query
261: * @param mixed $connection
262: */
263: function __construct( $connection, $query )
264: {
265: $this->obj_connection = $connection->obj_connection;
266: $this->obj_query_name = substr( MD5( microtime() ), 0, 12 );
267: $this->obj_prepare_init( $query );
268: }
269:
270: /**
271: * Sets parameters for prepared statement
272: *
273: * @access public
274: * @param mixed $param
275: */
276: public function obj_bind( $param )
277: {
278: $this->obj_parameter[] = trim( $param );
279: }
280:
281: /**
282: * Destroys prepared statement object
283: *
284: * @access public
285: * @return bool
286: */
287: public function obj_close_statement()
288: {
289: $query_stmt = pg_query( $this->obj_connection, "DEALLOCATE \"{$this->obj_query_name}\"" );
290:
291: return ( is_resource( $query_stmt ) ) ? true : false;
292: }
293:
294: /**
295: * Executes prepared statement and returns resultset resource
296: *
297: * @access public
298: * @return mixed
299: */
300: public function obj_execute()
301: {
302: $this->obj_result_resource = pg_execute( $this->obj_connection, $this->obj_query_name, $this->obj_parameter );
303:
304: return ( is_resource( $this->obj_result_resource ) ) ? new pgsql_resultset( $this->obj_result_resource ) : false;
305: }
306:
307: /**
308: * Releases statement resource memory and resets binding parameters
309: *
310: * @access public
311: * @return bool
312: */
313: public function obj_free_statement()
314: {
315: $this->obj_parameter = array();
316:
317: return pg_free_result( $this->obj_result_resource );
318: }
319:
320: /**
321: * Returns prepared statement resource
322: *
323: * @access private
324: * @param str $query
325: * @return mixed
326: */
327: private function obj_prepare_init( $query )
328: {
329: $this->obj_prepare_instance = pg_prepare( $this->obj_connection, $this->obj_query_name, $this->obj_prepare_sql( $query ) );
330:
331: return ( is_resource( $this->obj_prepare_instance ) ) ? $this->obj_prepare_instance : false;
332: }
333:
334: /**
335: * Replaces (?) markers in prepared statement query string with ($1,$2,etc)
336: *
337: * @access private
338: * @return str
339: */
340: private function obj_prepare_sql( $query )
341: {
342: $query_parts = explode( '?', $query );
343: $query_sql = $query_parts[0];
344:
345: for ( $i = 1; $i < count( $query_parts ); $i++ )
346: $query_sql .= "$$i {$query_parts[$i]}";
347:
348: return $query_sql;
349: }
350: }
351:
352:
353: /*************************************************************************************************************
354: * End database prepared statement class
355: ************************************************************************************************************/
356:
357:
358: /*************************************************************************************************************
359: * Begin database resultset class
360: ************************************************************************************************************/
361:
362:
363: class pgsql_resultset
364: {
365: /**********************************************
366: * Internal variables
367: *********************************************/
368:
369: /**
370: * Resultset query record
371: *
372: * @access private
373: * @var array
374: */
375: private $obj_record = array();
376:
377: /**
378: * Query result object
379: *
380: * @access private
381: * @var mixed
382: */
383: private $obj_result;
384:
385: /**********************************************
386: * Class methods
387: *********************************************/
388:
389: /**
390: * Constructor
391: *
392: * @access public
393: * @param mixed $result
394: */
395: public function __construct( $result )
396: {
397: $this->obj_result = $result;
398: }
399:
400: /**
401: * Return number of affected rows from insert/delete/update query
402: * Returns -1 if undetermined or failure
403: *
404: * @access public
405: * @return int
406: */
407: public function obj_affected_rows()
408: {
409: $affected_rows = pg_affected_rows( $this->obj_result );
410:
411: return ( $affected_rows >= 0 ) ? $affected_rows : -1;
412: }
413:
414: /**
415: * Returns resultset resource as associative array
416: *
417: * @access public
418: * @return mixed
419: */
420: public function obj_fetch_assoc()
421: {
422: $result = pg_fetch_assoc( $this->obj_result );
423:
424: return ( is_array( $result ) ) ? $this->obj_record = $result : null;
425: }
426:
427: /**
428: * Returns resultset resource as numeric array
429: *
430: * @access public
431: * @return mixed
432: */
433: public function obj_fetch_num()
434: {
435: $result = pg_fetch_row( $this->obj_result );
436:
437: return ( is_array( $result ) ) ? $this->obj_record = $result : null;
438: }
439:
440: /**
441: * Returns resultset resource as object
442: *
443: * @access public
444: * @return mixed
445: */
446: public function obj_fetch_object()
447: {
448: $result = pg_fetch_object( $this->obj_result );
449:
450: return ( is_object( $result ) ) ? $this->obj_record = $result : null;
451: }
452:
453: /**
454: * Returns resultset record
455: *
456: * @access public
457: * @param mixed $field
458: * @return mixed
459: */
460: public function obj_field( $field )
461: {
462: //get_magic_quotes deprecated in php 5.4 - added for backwards compatibility
463: return ( get_magic_quotes_runtime() ) ? stripslashes( $this->obj_record[$field] ) : $this->obj_record[$field];
464: }
465:
466: /**
467: * Frees resultset memory and destroys resultset object
468: *
469: * @access public
470: * @return bool
471: */
472: public function obj_free_result()
473: {
474: $this->obj_record = array();
475:
476: return pg_free_result( $this->obj_result );
477: }
478:
479: /**
480: * Return number of fields from query
481: * Returns -1 if undetermined or failure
482: *
483: * @access public
484: * @return int
485: */
486: public function obj_num_fields()
487: {
488: $num_cols = pg_num_fields( $this->obj_result );
489:
490: return ( $num_cols >= 0 ) ? $num_cols : -1;
491: }
492:
493: /**
494: * Return number rows from query
495: * Returns -1 if undetermined or failure
496: *
497: * @access public
498: * @return int
499: */
500: public function obj_num_rows()
501: {
502: $num_rows = pg_num_rows( $this->obj_result );
503:
504: return ( $num_rows >= 0 ) ? $num_rows : -1;
505: }
506: }
507:
508:
509: /*************************************************************************************************************
510: * End database resultset class
511: ************************************************************************************************************/
512:
513:
514: /*************************************************************************************************************
515: * Begin database transaction class
516: ************************************************************************************************************/
517:
518:
519: class pgsql_transaction
520: {
521: /**********************************************
522: * Internal variables
523: *********************************************/
524:
525: /**
526: * Database connection object
527: *
528: * @access private
529: * @var mixed
530: */
531: private $obj_connection;
532:
533: /**********************************************
534: * Class methods
535: *********************************************/
536:
537: /**
538: * Constructor
539: *
540: * @access public
541: * @param mixed $connection
542: */
543: public function __construct( $connection )
544: {
545: $this->obj_connection = $connection;
546:
547: //turn off autocommit
548: $this->obj_autocommit_mode( pg_query( $this->obj_connection, 'BEGIN' ) );
549: }
550:
551: /**
552: * Returns autocommit mode
553: *
554: * @access public
555: * @param mixed $query
556: * @return bool
557: */
558: public function obj_autocommit_mode( $query )
559: {
560: return ( is_resource( $query ) ) ? true : false;
561: }
562:
563: /**
564: * Commits transaction for current transaction instance
565: *
566: * @access public
567: * @return bool
568: */
569: public function obj_commit()
570: {
571: return ( is_resource( pg_query( $this->obj_connection, 'COMMIT' ) ) ) ? true : false;
572: }
573:
574: /**
575: * Rollbacks transaction for current transaction instance
576: *
577: * @access public
578: * @param str $savepoint
579: * @return bool
580: */
581: public function obj_rollback( $savepoint=false )
582: {
583: $rollback = ( !$savepoint ) ? 'ROLLBACK' : "ROLLBACK TO SAVEPOINT $savepoint";
584:
585: return ( is_resource( pg_query( $this->obj_connection, $rollback ) ) ) ? true : false;
586: }
587:
588: /**
589: * Creates transaction savepoint for current transaction instance
590: *
591: * @access public
592: * @param str $savepoint
593: * @return bool
594: */
595: public function obj_savepoint( $savepoint )
596: {
597: return ( is_resource( pg_query( $this->obj_connection, "SAVEPOINT $savepoint" ) ) ) ? true : false;
598: }
599: }
600:
601:
602: /*************************************************************************************************************
603: * End database transaction class
604: ************************************************************************************************************/
605:
606:
607: ?>