1: <?php
  2: 
  3: /**
  4:  * Database access controller class
  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: class objSQL extends obj_access
 15: {
 16:     /**********************************************
 17:      * Internal private variables
 18:      *********************************************/
 19: 
 20:     private $obj_trans_object;
 21:     /**
 22:      * Database connection object
 23:      *
 24:      * @access public
 25:      * @var    mixed
 26:      */
 27:     public $obj_connection = false;
 28:     
 29:     /**
 30:      * Database type array contains all supported database drivers
 31:      *
 32:      * @access private
 33:      * @var    array
 34:      */
 35:     private $obj_db_array = array( 'cubrid',
 36:                                    'firebird',
 37:                                    'mysql',
 38:                                    'pgsql',
 39:                                    'sqlite3',
 40:                                    'sqlsrv',
 41:                                    'pdo:cubrid',
 42:                                    'pdo:firebird',
 43:                                    'pdo:mysql',
 44:                                    'pdo:pgsql',
 45:                                    'pdo:sqlite3',
 46:                                    'pdo:sqlsrv' );
 47: 
 48:     /**
 49:      * Database driver type as set in datasource argument.
 50:      *
 51:      * @access private
 52:      * @var    str
 53:      */
 54:     private $obj_db_driver;  
 55:     
 56:     /**
 57:      * Database type
 58:      *
 59:      * @access private
 60:      * @var    str
 61:      */
 62:     private $obj_db_type;  
 63:     
 64:     /**
 65:      * Database connection class instance
 66:      *
 67:      * @access private
 68:      * @var    mixed
 69:      */
 70:     private $obj_instance;
 71:     
 72:     /**
 73:      * Database prepared statement string
 74:      *
 75:      * @access private
 76:      * @var    str
 77:      */
 78:     private $obj_prepare;
 79:     
 80:     /**
 81:      * Database query string
 82:      *
 83:      * @access private
 84:      * @var    str
 85:      */
 86:     private $obj_statement;
 87:     
 88:     /**
 89:      * Database transaction string
 90:      *
 91:      * @access private
 92:      * @var    str
 93:      */
 94:     private $obj_transaction;
 95:     
 96:     /**
 97:      * objSQL version
 98:      *
 99:      * @access private
100:      * @var    str
101:      */
102:     private $obj_version = '3.3.0';
103:     
104:     /**********************************************
105:      * Public class methods
106:      *********************************************/
107: 
108:     /**
109:      * Constructor
110:      *
111:      * @access public
112:      * @param  mixed $datasource
113:      */
114:     public function __construct( $datasource )
115:     {
116:         $this->obj_helper_datasource( $datasource );
117:         $this->obj_trans_object = null;
118:     }
119:     
120:     /**
121:      * Closes current database connection
122:      *
123:      * @access public
124:      * @return bool
125:      */
126:     public function obj_close()
127:     {
128:         return $this->obj_instance->obj_db_close();
129:     }
130:     
131:     /**
132:      * Executes delete query and returns resultset object/resource
133:      *
134:      * @access public
135:      * @param str $table
136:      * @param str $where
137:      * @return mixed
138:      */
139:     public function obj_delete( $table, $where=false )
140:     {
141:         $query_stmt = new $this->obj_statement( $this->obj_helper_delete( $table, $where ), $this, $this->obj_db_type, $this->obj_trans_object );
142:         
143:         return $query_stmt->obj_query_execute();
144:     }
145:     
146:     /**
147:      * Database driver type
148:      *
149:      * @access public
150:      * @return str
151:      */
152:     public function obj_driver()
153:     {
154:         return $this->obj_db_driver;
155:     }
156:     
157:     /**
158:      * Returns error flag for current connection instance - true/false
159:      *
160:      * @access public
161:      * @return bool
162:      */
163:     public function obj_error()
164:     {
165:         return $this->obj_instance->obj_db_error();
166:     }
167:     
168:     /**
169:      * Returns error message for current connection instance
170:      *
171:      * @access public
172:      * @return str
173:      */
174:     public function obj_error_message()
175:     {
176:         return $this->obj_instance->obj_db_message();
177:     }
178:     
179:     /**
180:      * Returns escaped string data for database insertion
181:      *
182:      * @access public
183:      * @param  str $data
184:      * @return str
185:      */
186:     public function obj_escape( $data )
187:     {
188:         return $this->obj_instance->obj_db_escape_data( $data );
189:     }
190:     
191:     /**
192:      * Returns associative array with general database and script information
193:      *
194:      * @access public
195:      * @return array
196:      */
197:     public function obj_info()
198:     {
199:         return array( 'OBJSQL_VERSION'   => $this->obj_version,
200:                       'DATABASE_NAME'    => $this->obj_instance->obj_db_info()[2],
201:                       'DATABASE_TYPE'    => $this->obj_db_driver,
202:                       'DATABASE_VERSION' => $this->obj_instance->obj_db_info()[0],
203:                       'DATABASE_CHARSET' => $this->obj_instance->obj_db_info()[1],
204:                       'PHP_VERSION'      => phpversion() );
205:     }
206:     
207:     /**
208:      * executes insert query and returns resultset object/resource
209:      *
210:      * @access public
211:      * @param  str   $table
212:      * @param  array $data_array
213:      * @return mixed
214:      */
215:     public function obj_insert( $table, $data_array )
216:     {
217:         //$data_array MUST be a key value pair array - value can be a one dim array or comma delimited string
218:         $query_stmt = new $this->obj_statement( $this->obj_helper_insert( $table, $data_array ), $this, $this->obj_db_type, $this->obj_trans_object );
219: 
220:         return $query_stmt->obj_query_execute();
221:     }
222:     
223:     /**
224:      * Executes select paging query and returns resultset object/resource and number of pages via $limit
225:      *
226:      * @access public
227:      * @param  str $table
228:      * @param  str $cols
229:      * @param  str $where
230:      * @param  str $order_by
231:      * @param  int $limit
232:      * @param  int $offset
233:      * @return array
234:      */
235:     public function obj_paging( $table, $cols=false, $where=false, $order_by=false, $limit=1, $offset=1 )
236:     {
237:         //make sure $limit & $offset are unsigned ints > 0
238:         $query_offset = ( is_numeric( $offset ) && $offset > 0 ) ? ( int )$offset : 1;
239:         $query_limit  = ( is_numeric( $limit ) && $limit > 0 ) ? ( int )$limit : 1;
240:         // 04/21/13 - added where clause to fix last page bug
241:         $query_where = ( !trim( $where ) ) ? '' : $where; 
242:                 
243:         $query_stmt = new $this->obj_statement( $this->obj_helper_paging( $table, $cols, $query_where, $order_by, $query_limit, $query_offset ), $this, $this->obj_db_type );
244: 
245:         return array( $query_stmt->obj_query_execute(), ceil( $this->obj_row_count( $table, $cols, $query_where )/$query_limit ) );
246:     }
247:     
248:     /**
249:      * Returns prepared statement instance
250:      *
251:      * @access public
252:      * @param  str   $query
253:      * @param  array $param_vars
254:      * @return mixed
255:      */
256:     public function obj_prepare_statement( $query, $param_vars=false )
257:     {   
258:         return new $this->obj_prepare( $query, $this, $this->obj_db_type, $param_vars, $this->obj_trans_object );
259:     }
260:     
261:     /**
262:      * Executes non-prepared query and returns resultset object/resource
263:      *
264:      * @access public
265:      * @param  str $query
266:      * @return mixed
267:      */
268:     public function obj_query( $query )
269:     {
270:         $query_stmt = new $this->obj_statement( $query, $this, $this->obj_db_type, $this->obj_trans_object );
271: 
272:         return $query_stmt->obj_query_execute();
273:     }
274:     
275:     /**
276:      * Executes select count query and returns row count
277:      *
278:      * @access public
279:      * @param  str $table
280:      * @param  str $cols
281:      * @param  str $where
282:      * @return int
283:      */
284:     public function obj_row_count( $table, $cols=false, $where=false )
285:     {
286:         return $this->obj_instance->obj_db_rowcount( $table, $cols, $where );
287:     }
288:     
289:     /**
290:      * Executes select query and returns resultset object/resource
291:      *
292:      * @access public
293:      * @param  str $table
294:      * @param  str $cols
295:      * @param  str $where
296:      * @param  str $order_by
297:      * @param  str $sort_order
298:      * @return mixed
299:      */
300:     public function obj_select( $table, $cols=false, $where=false, $order_by=false, $sort_order=false )
301:     {
302:         $query_stmt = new $this->obj_statement( $this->obj_helper_select( $table, $cols, $where, $order_by, $sort_order ), $this, $this->obj_db_type, $this->obj_trans_object );
303:         
304:          return $query_stmt->obj_query_execute();
305:     }
306:     
307:     /**
308:      * Begins transaction     
309:      * 
310:      * @access public
311:      * @return mixed
312:      */
313:     public function obj_transaction()
314:     {   
315:         $obj_transaction = new $this->obj_transaction( $this->obj_connection, $this->obj_db_type );
316:         $this->obj_trans_object = ( $this->obj_db_type == 'firebird' || $this->obj_db_type == 'pdo:firebird' ) ? $obj_transaction->obj_trans_object : null;
317:         
318:         return $obj_transaction;
319:     }
320: 
321:     /**
322:      * Executes update query and returns resultset object/resource
323:      *
324:      * @access public
325:      * @param  str   $table
326:      * @param  array $data_array
327:      * @param  str   $where
328:      * @return mixed
329:      */
330:     public function obj_update( $table, $data_array, $where=false )
331:     {
332:         //$data_array MUST be a key value pair array
333:         $query_stmt = new $this->obj_statement( $this->obj_helper_update( $table, $data_array, $where ), $this, $this->obj_db_type, $this->obj_trans_object );
334:         
335:         return $query_stmt->obj_query_execute();
336:     }
337:     
338:     /**********************************************
339:      * Private helper methods
340:      *********************************************/
341:     
342:     /**
343:      * Sets database connection instance/vars and loads database drivers
344:      *
345:      * @access private
346:      * @param  mixed $datasource
347:      * @return mixed 
348:      */
349:     private function obj_helper_datasource( $datasource )
350:     {
351:         $obj_datasource = ( is_array( $datasource ) ) ? $datasource : explode( ',', $datasource );
352:         
353:         if ( in_array( strtolower( $obj_datasource[0] ), $this->obj_db_array ) )
354:         {
355:             $this->obj_db_driver = strtolower( $obj_datasource[0] );
356:             
357:             if ( substr_count( $this->obj_db_driver, 'pdo:' ) )
358:             {
359:                 $this->obj_db_type = str_replace( 'pdo:', '', $this->obj_db_driver );
360:                 $this->obj_statement = "pdo_statement";
361:                 $this->obj_prepare = "pdo_prepare";
362:                 $this->obj_transaction = "pdo_transaction";
363:                 $obj_connection = "pdo_connection";
364:                 
365:                 require_once "drivers/pdo.php";
366:             }
367:             else
368:             {
369:                 $this->obj_db_type = $this->obj_db_driver;
370:                 $this->obj_statement = "{$this->obj_db_type}_statement";
371:                 $this->obj_prepare = "{$this->obj_db_type}_prepare";
372:                 $this->obj_transaction = "{$this->obj_db_type}_transaction";
373:                 $obj_connection = "{$this->obj_db_type}_connection";
374:                 
375:                 require_once "drivers/{$this->obj_db_type}.php";
376:             }
377:             
378:             $this->obj_instance = new $obj_connection( $obj_datasource, $this->obj_db_type );
379:             
380:             if ( $this->obj_instance )
381:                 return $this->obj_connection = $this->obj_instance->obj_db_connection();
382:             else
383:                 return false;
384:         }
385:         else
386:         {
387:             trigger_error( "Unsupported database type/driver: {$obj_datasource[0]}", E_USER_WARNING ); 
388:             
389:             return false;
390:         }    
391:     }
392:     
393:     /**
394:      * Returns delete query SQL statement
395:      *
396:      * @access private
397:      * @param str $table
398:      * @param str $where
399:      * @return mixed
400:      */
401:     private function obj_helper_delete( $table, $where=false )
402:     {
403:         $query_where = ( !trim( $where ) ) ? '' : "WHERE $where"; 
404:         
405:         return "DELETE FROM $table $query_where";
406:     }
407:     
408:     /**
409:      * Returns insert query SQL statement
410:      *
411:      * @access private
412:      * @param  str   $table
413:      * @param  array $data_array
414:      * @return str
415:      */
416:     private function obj_helper_insert( $table, $data_array )
417:     {
418:         //$data_array MUST be a key value pair array - value can be a one dim array or comma delimited string
419:         $array_keys = array_keys( $data_array );
420:         $array_vals = array_values( $data_array );
421:         
422:         //if array_vals[$i] is a string, convert to array
423:         for ( $i = 0; $i < count( $array_vals ); $i++ )
424:         {
425:             if ( !is_array( $array_vals[$i] ) )
426:             {
427:                 $temp_array = explode( ',', $array_vals[$i] );
428:                 $array_vals[$i] = $temp_array;
429:             }
430:         }
431:         
432:         for ( $i = 0; $i < count( $array_vals[0] ); $i++ )
433:         {
434:             $query_insert[] = '(';
435:             
436:             for ( $j = 0; $j < count( $array_vals ); $j++ )
437:             {
438:                 if ( is_string( $array_vals[$j][$i] ) && !is_numeric( $array_vals[$j][$i] ) )
439:                     $query_insert[] = "'{$array_vals[$j][$i]}'";  
440:                 else    
441:                     $query_insert[] = $array_vals[$j][$i];
442:             }
443:             
444:             $query_insert[] = ')';
445:         }
446:         
447:         // 8/4/13 - removed .= operator
448:         $query_cols = implode( ',', $array_keys ); 
449:         $query_vals = str_replace( array( ',),(,', ',)', '(,' ), array( '),(', ')', '('), implode( ',', $query_insert ) );
450:                 
451:         return "INSERT INTO $table ($query_cols) VALUES $query_vals";
452:     }
453:     
454:     /**
455:      * Returns select paging query SQL statement
456:      *
457:      * @access private
458:      * @param  str $table
459:      * @param  int $limit 
460:      * @param  int $offset 
461:      * @param  str $cols
462:      * @param  str $where
463:      * @param  str $order_by
464:      * @return str
465:      */
466:     private function obj_helper_paging( $table, $cols=false, $where=false, $order_by=false, $limit=1, $offset=1 )
467:     {
468:         $query_cols  = ( !trim( $cols ) ) ? '*' : $cols;
469:         $query_where = ( !trim( $where ) ) ? '' : "WHERE $where"; 
470:         $query_order = ( !trim( $order_by ) ) ? '' : "ORDER BY $order_by";
471:         $query_offset = ( ( $offset - 1 ) * $limit );
472:         
473:         if ( $this->obj_db_type === 'sqlsrv' )
474:         {
475:             // check for SQL Server 2012+
476:             if ( explode( '.',  $this->obj_instance->obj_db_info()[0] ) < 11 )
477:             {
478:                 $query_sql = "WITH temp_table AS ( 
479:                               SELECT ROW_NUMBER() OVER ( $query_order ) AS RowNumber, $query_cols 
480:                               FROM $table $query_where ) 
481:                               SELECT * 
482:                               FROM temp_table 
483:                               WHERE RowNumber > $query_offset AND RowNumber <= ( $limit + $query_offset )";
484:             }
485:             else
486:             {
487:                 // added to support SQL Server 2012 - throws cursor exception using row_number method
488:                 $query_sql = "SELECT $query_cols FROM $table $query_where $query_order
489:                               OFFSET $query_offset ROWS
490:                               FETCH NEXT $limit ROWS ONLY"; 
491:             }                             
492:         }
493:         else
494:         {
495:             if ( $this->obj_db_type === 'pgsql' )
496:                 $limit_str = "LIMIT $limit OFFSET $query_offset";
497:             else
498:                 $limit_str = "LIMIT $query_offset,$limit"; 
499:                 
500:             $query_sql = "SELECT $query_cols FROM $table $query_where $query_order $limit_str";
501:         }
502:         
503:         return $query_sql;
504:     }
505:     
506:     /**
507:      * Returns select query SQL statement
508:      *
509:      * @access private
510:      * @param  str $table
511:      * @param  str $cols
512:      * @param  str $where
513:      * @param  str $order_by
514:      * @param  str $sort_order
515:      * @return str
516:      */
517:     private function obj_helper_select( $table, $cols=false, $where=false, $order_by=false, $sort_order=false )
518:     {
519:         $query_cols  = ( !trim( $cols ) ) ? '*' : $cols;
520:         $query_where = ( !trim( $where ) ) ? '' : "WHERE $where"; 
521:         $query_order = ( !trim( $order_by ) ) ? '' : "ORDER BY $order_by";
522:         $query_sort  = ( !trim( $sort_order ) || strtolower( $sort_order ) != 'desc' || strtolower( $sort_order ) != 'asc' ) ? '' : $sort_order;
523:         
524:         return "SELECT $query_cols FROM $table $query_where $query_order $query_sort";
525:     }
526:     
527:     /**
528:      * Returns update query SQL statement
529:      *
530:      * @access private
531:      * @param  str   $table
532:      * @param  array $data_array
533:      * @param  str   $where
534:      * @return str
535:      */
536:     private function obj_helper_update( $table, $data_array, $where=false )
537:     {
538:         //$array MUST be a key value pair array
539:         $query_cols   = array_keys( $data_array );
540:         $query_vals   = array_values( $data_array );
541:         $query_update = '';
542: 
543:         for ( $i = 0; $i < count( $data_array ); $i++ )
544:         {
545:             if ( is_string( $query_vals[$i] ) && !is_numeric( $query_vals[$i] ) )
546:                 $query_update .= "{$query_cols[$i]}='{$query_vals[$i]}',";
547:             else
548:                 $query_update .= "{$query_cols[$i]}={$query_vals[$i]},";
549:         }
550: 
551:         $query_vars = rtrim( $query_update, ',' );
552:         $query_where  = ( !trim( $where ) ) ? '' : "WHERE $where";
553:                 
554:         return "UPDATE $table SET $query_vars $query_where";
555:     }
556:     
557:     
558:     /*****************************************************************************************************
559:      * Abstract protected methods - prevents calling these methods outside of the objSQL controller class
560:      *****************************************************************************************************/
561:     
562:     /**
563:      * Protected methods from extended connection classes
564:      *
565:      * @access protected
566:      */
567:     protected function obj_db_close() {}
568:     protected function obj_db_connection() {}
569:     protected function obj_db_error() {}
570:     protected function obj_db_escape_data( $str=null ) {}
571:     protected function obj_db_message() {}
572:     protected function obj_db_info() {}
573:     protected function obj_db_rowcount( $table, $cols=false, $where=false ) {}
574: }
575: 
576: abstract class obj_access 
577: {
578:     /**
579:      * Abstract protected methods from extended connection classes
580:      *
581:      * @access protected
582:      */
583:     abstract protected function obj_db_close();
584:     abstract protected function obj_db_connection();
585:     abstract protected function obj_db_error();
586:     abstract protected function obj_db_escape_data( $str );
587:     abstract protected function obj_db_message();
588:     abstract protected function obj_db_info();
589:     abstract protected function obj_db_rowcount( $table, $cols, $where );
590: }
591: 
592: ?>