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