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