1: <?php
2:
3: /**
4: * PDO database 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 pdo_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 = null;
33:
34: /**
35: * Database connection information
36: *
37: * @access private
38: * @var array
39: */
40: private $obj_datasource;
41:
42: /**
43: * Database type
44: *
45: * @access private
46: * @var str
47: */
48: private $obj_db_type;
49:
50: /**********************************************
51: * Class methods
52: *********************************************/
53:
54: /**
55: * Constructor
56: *
57: * @access public
58: * @param array $datasource
59: */
60: public function __construct( $datasource, $dbtype )
61: {
62: $this->obj_datasource = $datasource;
63: $this->obj_db_type = $dbtype;
64: }
65:
66: /**
67: * Returns database connection object
68: *
69: * @access protected
70: * @return mixed
71: */
72: protected function obj_db_connection()
73: {
74: if ( $this->obj_db_type == 'sqlite3' )
75: {
76: $obj_DSN = "sqlite:{$this->obj_datasource[4]}";
77: }
78: else
79: {
80: if ( ( array_key_exists( 5, $this->obj_datasource ) ) && is_numeric( trim( $this->obj_datasource[5] ) ) )
81: {
82: if ( $this->obj_db_type == 'sqlsrv' )
83: $obj_DSN = "sqlsrv:Server={$this->obj_datasource[1]},{$this->obj_datasource[5]};Database={$this->obj_datasource[4]}";
84: else
85: $obj_DSN = "{$this->obj_db_type}:host={$this->obj_datasource[1]};port={$this->obj_datasource[5]};dbname={$this->obj_datasource[4]}";
86: }
87: else
88: {
89: if ( $this->obj_db_type == 'sqlsrv' )
90: $obj_DSN = "sqlsrv:Server={$this->obj_datasource[1]};Database={$this->obj_datasource[4]}";
91: else
92: $obj_DSN = "{$this->obj_db_type}:host={$this->obj_datasource[1]};dbname={$this->obj_datasource[4]}";
93: }
94: }
95:
96: $db_connection = new PDO( $obj_DSN, $this->obj_datasource[2], $this->obj_datasource[3] );
97:
98: return ( is_object( $db_connection ) ) ? $this->obj_connection = $db_connection : false;
99: }
100:
101: /**
102: * Closes connection to database server
103: *
104: * @access protected
105: * @return bool
106: */
107: protected function obj_db_close()
108: {
109: return $this->obj_connection = null;
110: }
111:
112: /**
113: * Returns error flag for current connection instance
114: *
115: * @access protected
116: * @return bool
117: */
118: protected function obj_db_error()
119: {
120: return ( $this->obj_connection->errorInfo()[1] != '' ) ? true : false;
121: }
122:
123: /**
124: * Escapes string data for database insertion
125: *
126: * @access protected
127: * @param mixed $data
128: * @return mixed
129: */
130: protected function obj_db_escape_data( $data )
131: {
132: if ( $this->obj_db_type == 'mysql' )
133: return str_replace( "'", "\'", $data );
134: else
135: return str_replace( "'", "''", $data );
136: }
137:
138: /**
139: * Returns error message for current connection instance
140: *
141: * @access protected
142: * @return mixed
143: */
144: protected function obj_db_message()
145: {
146: return ( $this->obj_db_error() ) ? $this->obj_connection->errorInfo()[2] : null;
147: }
148:
149: /**
150: * Returns database server information
151: *
152: * @access protected
153: * @return array
154: */
155: protected function obj_db_info()
156: {
157: if ( $this->obj_db_type == 'cubrid' )
158: {
159: $obj_get_info = $this->obj_connection->query( "SELECT COLLATION('abc')" );
160: $obj_encoding = $obj_get_info->fetch( PDO::FETCH_NUM )[0];
161: }
162: elseif ( $this->obj_db_type == 'mysql' )
163: {
164: $obj_get_info = $this->obj_connection->query( "SHOW TABLE STATUS FROM {$this->obj_datasource[4]}" );
165: $obj_encoding = $obj_get_info->fetch( PDO::FETCH_NUM )[14];
166: }
167: elseif ( $this->obj_db_type == 'pgsql' )
168: {
169: $obj_get_info = explode( ';', $this->obj_connection->getAttribute( constant( 'PDO::ATTR_SERVER_INFO' ) ) );
170: $obj_get_encoding = explode( ':', $obj_get_info[1] );
171: $obj_encoding = $obj_get_encoding[1];
172: }
173: elseif ( $this->obj_db_type == 'sqlsrv' )
174: {
175: $obj_get_info = $this->obj_connection->query( "SELECT collation_name FROM sys.databases WHERE NAME='{$this->obj_datasource[4]}'" );
176: $obj_encoding = $obj_get_info->fetch( PDO::FETCH_OBJ )->collation_name;
177: }
178: else
179: $obj_encoding = 'UTF8';
180:
181: $obj_get_info = null;
182:
183: return array( $this->obj_connection->getAttribute( constant( 'PDO::ATTR_SERVER_VERSION' ) ), $obj_encoding, $this->obj_datasource[4] );
184: }
185:
186: /**
187: * Returns row count for named table with arguments
188: * Returns -1 if undetermined or failure
189: *
190: * @access protected
191: * @param str $table
192: * @param str $cols
193: * @param str $where
194: * @return int
195: */
196: protected function obj_db_rowcount( $table, $cols=false, $where=false )
197: {
198: $query_cols = ( !trim( $cols ) ) ? '*' : $cols;
199: $query_where = ( !trim( $where ) ) ? '' : "WHERE $where";
200:
201: $query_stmt = $this->obj_connection->query( "SELECT COUNT($query_cols) FROM $table $query_where" );
202:
203: if ( !is_object( $query_stmt ) )
204: return -1;
205:
206: while ( $num_rows = $query_stmt->fetch( PDO::FETCH_NUM ) )
207: $rowcount = ( $num_rows[0] >= 0 ) ? $num_rows[0] : -1;
208:
209: $query_stmt = null;
210:
211: return $rowcount;
212: }
213: }
214:
215:
216: /*************************************************************************************************************
217: * End database connection class
218: ************************************************************************************************************/
219:
220:
221: /*************************************************************************************************************
222: * Begin database statement class
223: ************************************************************************************************************/
224:
225:
226: class pdo_statement
227: {
228: /**********************************************
229: * Internal variables
230: *********************************************/
231:
232: /**
233: * Database connection object
234: *
235: * @access private
236: * @var mixed
237: */
238: private $obj_connection;
239:
240: /**
241: * Database driver type
242: *
243: * @access private
244: * @var str
245: */
246: private $obj_db_type;
247:
248: /**
249: * Query string
250: *
251: * @access private
252: * @var str
253: */
254: private $obj_query;
255:
256: /**********************************************
257: * Class methods
258: *********************************************/
259:
260: /**
261: * Constructor
262: *
263: * @access public
264: * @param str $query
265: * @param mixed $connection
266: * @param str $dbtype
267: */
268: function __construct( $query, $connection, $dbtype )
269: {
270: $this->obj_connection = $connection->obj_connection;
271: $this->obj_db_type = $dbtype;
272: $this->obj_query = $query;
273: }
274:
275: /**
276: * Executes general query and returns resultset object
277: *
278: * @access public
279: * @return mixed
280: */
281: public function obj_query_execute()
282: {
283: $query_stmt = $this->obj_connection->query( $this->obj_query );
284:
285: return ( is_object( $query_stmt ) ) ? new pdo_resultset( $query_stmt, $this->obj_db_type ) : false;
286: }
287: }
288:
289:
290: /*************************************************************************************************************
291: * End database statement class
292: ************************************************************************************************************/
293:
294:
295: /*************************************************************************************************************
296: * Begin database prepared statement class
297: ************************************************************************************************************/
298:
299:
300: class pdo_prepare
301: {
302: /**********************************************
303: * Internal variables
304: *********************************************/
305:
306: /**
307: * Database connection object
308: *
309: * @access private
310: * @var mixed
311: */
312: private $obj_connection;
313:
314: /**
315: * Database type
316: *
317: * @access private
318: * @var str
319: */
320: private $obj_db_type;
321:
322: /**
323: * Set obj_bind parameter counter
324: *
325: * @access private
326: * @var int
327: */
328: private $obj_parameter_cnt = 0;
329:
330: /**
331: * Prepared statement instance
332: *
333: * @access private
334: * @var bool
335: */
336: private $obj_prepare_instance;
337:
338: /**********************************************
339: * Class methods
340: *********************************************/
341:
342: /**
343: * Constructor
344: *
345: * @access public
346: * @param mixed $connection
347: * @param str $query
348: * @param str $dbtype
349: */
350: function __construct( $query, $connection, $dbtype )
351: {
352: $this->obj_connection = $connection->obj_connection;
353: $this->obj_db_type = $dbtype;
354: $this->obj_prepare_init( $query );
355: }
356:
357: /**
358: * Sets parameters and parameter types for prepared statement
359: *
360: * @access public
361: * @param mixed $param
362: */
363: public function obj_bind( $param )
364: {
365: $this->obj_parameter_cnt++;
366:
367: return $this->obj_prepare_instance->bindParam( $this->obj_parameter_cnt, $param );
368: }
369:
370: /**
371: * Destroys prepared statement object
372: *
373: * @access public
374: * @return bool
375: */
376: public function obj_close_statement()
377: {
378: $this->obj_parameter_cnt = 0;
379: $this->obj_prepare_instance->closeCursor();
380:
381: return ( !is_object( $this->obj_prepare_instance = null ) ) ? true : false;
382: }
383:
384: /**
385: * Binds parameters, executes prepared statement and returns resultset object
386: *
387: * @access public
388: * @return mixed
389: */
390: public function obj_execute()
391: {
392: $query_stmt = $this->obj_prepare_instance->execute();
393:
394: if ( $query_stmt )
395: return new pdo_resultset( $this->obj_prepare_instance, $this->obj_db_type );
396: else
397: return false;
398: }
399:
400: /**
401: * Frees resultset memory from prepared statement object and resets binding parameters
402: *
403: * @access public
404: * @return bool
405: */
406: public function obj_free_statement()
407: {
408: $this->obj_parameter_cnt = 0;
409:
410: return $this->obj_prepare_instance->closeCursor();
411: }
412:
413: /**
414: * Returns prepared statement instance
415: *
416: * @access private
417: * @param str $query
418: * @return bool
419: */
420: private function obj_prepare_init( $query )
421: {
422: return $this->obj_prepare_instance = $this->obj_connection->prepare( $query );
423: }
424: }
425:
426:
427: /*************************************************************************************************************
428: * End database prepared statement class
429: ************************************************************************************************************/
430:
431:
432: /*************************************************************************************************************
433: * Begin database resultset class
434: ************************************************************************************************************/
435:
436:
437: class pdo_resultset
438: {
439: /**********************************************
440: * Internal variables
441: *********************************************/
442:
443: /**
444: * Database type
445: *
446: * @access private
447: * @var str
448: */
449: private $obj_db_type;
450:
451: /**
452: * Query record
453: *
454: * @access private
455: * @var array
456: */
457: private $obj_record = array();
458:
459: /**
460: * Query resultset object
461: *
462: * @access private
463: * @var mixed
464: */
465: private $obj_result = false;
466:
467: /**********************************************
468: * Class methods
469: *********************************************/
470: /**
471: * Constructor
472: *
473: * @access public
474: * @param mixed $result
475: * @param mixed $dbtype
476: */
477: public function __construct( $result, $dbtype )
478: {
479: $this->obj_result = $result;
480: $this->obj_db_type = $dbtype;
481: }
482:
483: /**
484: * Returns number of affected rows from insert/delete/update query
485: * Returns -1 if undetermined or failure
486: *
487: * @access public
488: * @return int
489: */
490: public function obj_affected_rows()
491: {
492: $affected_rows = $this->obj_result->rowCount();
493:
494: return ( $affected_rows >= 0 ) ? $affected_rows : -1;
495: }
496:
497: /**
498: * Returns resultset object as associative array
499: *
500: * @access public
501: * @return mixed
502: */
503: public function obj_fetch_assoc()
504: {
505: $result = $this->obj_result->fetch( PDO::FETCH_ASSOC );
506:
507: return ( is_array( $result ) && $result !== false ) ? $this->obj_record = $result : null;
508: }
509:
510: /**
511: * Returns resultset object as numeric array
512: *
513: * @access public
514: * @return mixed
515: */
516: public function obj_fetch_num()
517: {
518: $result = $this->obj_result->fetch( PDO::FETCH_NUM );
519:
520: return ( is_array( $result ) && $result !== false ) ? $this->obj_record = $result : null;
521: }
522:
523: /**
524: * Returns resultset object as object
525: *
526: * @access public
527: * @return mixed
528: */
529:
530: public function obj_fetch_object()
531: {
532: $result = $this->obj_result->fetch( PDO::FETCH_OBJ );
533:
534: return ( is_object( $result ) && $result !== false ) ? $this->obj_record = $result : null;
535: }
536:
537: /**
538: * Returns resultset record
539: *
540: * @access public
541: * @param mixed $field
542: * @return mixed
543: */
544: public function obj_field( $field )
545: {
546: if ( $this->obj_result )
547: {
548: //get_magic_quotes deprecated in php 5.4 - added for backwards compatibility
549: return ( get_magic_quotes_runtime() ) ? stripslashes( $this->obj_record[$field] ) : $this->obj_record[$field];
550: }
551: }
552:
553: /**
554: * Frees resultset memory and destroys resultset object
555: *
556: * @access public
557: * @return bool
558: */
559: public function obj_free_result()
560: {
561: $this->obj_record = array();
562:
563: return ( !is_object( $this->obj_result = null ) ) ? true : false;
564: }
565:
566: /**
567: * Returns number of fields from query
568: * Returns -1 if undetermined or failure
569: *
570: * @access public
571: * @return int
572: */
573: public function obj_num_fields()
574: {
575: $num_cols = $this->obj_result->columnCount();
576:
577: return ( $num_cols >= 0 ) ? $num_cols : -1;
578: }
579:
580: /**
581: * Returns number rows from query
582: * Returns -1 if undetermined or failure
583: *
584: * @access public
585: * @return int
586: */
587: public function obj_num_rows()
588: {
589: if ( $this->obj_db_type == 'sqlite3' || $this->obj_db_type == 'sqlsrv' )
590: $num_rows = count( $this->obj_result->fetchAll() );
591: else
592: $num_rows = $this->obj_result->rowCount();
593:
594: return ( $num_rows >= 0 ) ? $num_rows : -1;
595: }
596: }
597:
598:
599: /*************************************************************************************************************
600: * End database resultset class
601: ************************************************************************************************************/
602:
603:
604: /*************************************************************************************************************
605: * Begin database transaction class
606: ************************************************************************************************************/
607:
608:
609: class pdo_transaction
610: {
611: /**********************************************
612: * Internal variables
613: *********************************************/
614:
615: /**
616: * Database connection instance
617: *
618: * @access private
619: * @var mixed
620: */
621: private $obj_connection;
622:
623: /**
624: * Database type
625: *
626: * @access private
627: * @var mixed
628: */
629: private $obj_db_type;
630:
631: /**********************************************
632: * Class methods
633: *********************************************/
634:
635: /**
636: * Constructor
637: *
638: * @access public
639: * @param mixed $connection
640: * @param str $dbtype
641: */
642: public function __construct( $connection, $dbtype )
643: {
644: $this->obj_connection = $connection;
645: $this->obj_db_type = $dbtype;
646:
647: //turn off autocommit
648: $this->obj_connection->beginTransaction();
649: }
650:
651: /**
652: * Commits transaction for current transaction instance
653: *
654: * @access public
655: * @return bool
656: */
657: public function obj_commit()
658: {
659: return $this->obj_connection->commit();
660: }
661:
662: /**
663: * Rollbacks transaction for current transaction instance
664: *
665: * @access public
666: * @param str $savepoint
667: * @return bool
668: */
669: public function obj_rollback( $savepoint=false )
670: {
671: if ( !$savepoint )
672: $rollback = $this->obj_connection->rollBack();
673: else
674: {
675: if ( $this->obj_db_type == 'cubrid' )
676: $rollback = $this->obj_connection->query( "ROLLBACK WORK TO SAVEPOINT $savepoint" );
677: elseif ( $this->obj_db_type == 'sqlite3' )
678: $rollback = $this->obj_connection->query( "ROLLBACK TO $savepoint" );
679: elseif ( $this->obj_db_type == 'sqlsrv' )
680: $rollback = $this->obj_connection->query( "ROLLBACK TRANSACTION $savepoint" );
681: else
682: $rollback = $this->obj_connection->query( "ROLLBACK TO SAVEPOINT $savepoint" );
683: }
684:
685: return ( $rollback || is_object( $rollback ) ) ? true : false;
686: }
687:
688: /**
689: * Creates transaction savepoint for current transaction instance
690: *
691: * @access public
692: * @param str $savepoint
693: * @return bool
694: */
695: public function obj_savepoint( $savepoint )
696: {
697: if ( $this->obj_db_type == 'sqlsrv' )
698: return ( is_object( $this->obj_connection->query( "SAVE TRANSACTION $savepoint" ) ) ) ? true : false;
699: else
700: return ( is_object( $this->obj_connection->query( "SAVEPOINT $savepoint" ) ) ) ? true : false;
701: }
702: }
703:
704:
705: /*************************************************************************************************************
706: * End database transaction class
707: ************************************************************************************************************/
708:
709:
710: ?>