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