includes/ezSQL/ez_sql_core.php

   1 <?php
   2
   3         /**********************************************************************
   4         *  Author: Justin Vincent (jv@vip.ie)
   5         *  Web...: http://justinvincent.com
   6         *  Name..: ezSQL
   7         *  Desc..: ezSQL Core module - database abstraction library to make
   8         *          it very easy to deal with databases. ezSQLcore can not be used by
   9         *          itself (it is designed for use by database specific modules).
  10         *
  11         */
  12
  13         /**********************************************************************
  14         *  ezSQL Constants
  15         */
  16
  17         define('EZSQL_VERSION','2.17');
  18         define('OBJECT','OBJECT',true);
  19         define('ARRAY_A','ARRAY_A',true);
  20         define('ARRAY_N','ARRAY_N',true);
  21
  22         /**********************************************************************
  23         *  Core class containg common functions to manipulate query result
  24         *  sets once returned
  25         */
  26
  27         class ezSQLcore
  28         {
  29
  30                 var $trace            = false;  // same as $debug_all
  31                 var $debug_all        = false;  // same as $trace
  32                 var $debug_called     = false;
  33                 var $vardump_called   = false;
  34                 var $show_errors      = true;
  35                 var $num_queries      = 0;
  36                 var $last_query       = null;
  37                 var $last_error       = null;
  38                 var $col_info         = null;
  39                 var $captured_errors  = array();
  40                 var $cache_dir        = false;
  41                 var $cache_queries    = false;
  42                 var $cache_inserts    = false;
  43                 var $use_disk_cache   = false;
  44                 var $cache_timeout    = 24; // hours
  45                 var $timers           = array();
  46                 var $total_query_time = 0;
  47                 var $db_connect_time  = 0;
  48                 var $trace_log        = array();
  49                 var $use_trace_log    = false;
  50                 var $sql_log_file     = false;
  51                 var $do_profile       = false;
  52                 var $profile_times    = array();
  53
  54                 // == TJH == default now needed for echo of debug function
  55                 var $debug_echo_is_on = true;
  56
  57                 /**********************************************************************
  58                 *  Constructor
  59                 */
  60
  61                 function ezSQLcore()
  62                 {
  63                 }
  64
  65                 /**********************************************************************
  66                 *  Get host and port from an "host:port" notation.
  67                 *  Returns array of host and port. If port is omitted, returns $default
  68                 */
  69
  70                 function get_host_port( $host, $default = false )
  71                 {
  72                         $port = $default;
  73                         if ( false !== strpos( $host, ':' ) ) {
  74                                 list( $host, $port ) = explode( ':', $host );
  75                         }
  76                         return array( $host, $port );
  77                 }
  78
  79                 /**********************************************************************
  80                 *  Print SQL/DB error - over-ridden by specific DB class
  81                 */
  82
  83                 function register_error($err_str)
  84                 {
  85                         // Keep track of last error
  86                         $this->last_error = $err_str;
  87
  88                         // Capture all errors to an error array no matter what happens
  89                         $this->captured_errors[] = array
  90                         (
  91                                 'error_str' => $err_str,
  92                                 'query'     => $this->last_query
  93                         );
  94                 }
  95
  96                 /**********************************************************************
  97                 *  Turn error handling on or off..
  98                 */
  99
 100                 function show_errors()
 101                 {
 102                         $this->show_errors = true;
 103                 }
 104
 105                 function hide_errors()
 106                 {
 107                         $this->show_errors = false;
 108                 }
 109
 110                 /**********************************************************************
 111                 *  Kill cached query results
 112                 */
 113
 114                 function flush()
 115                 {
 116                         // Get rid of these
 117                         $this->last_result = null;
 118                         $this->col_info = null;
 119                         $this->last_query = null;
 120                         $this->from_disk_cache = false;
 121                 }
 122
 123                 /**********************************************************************
 124                 *  Get one variable from the DB - see docs for more detail
 125                 */
 126
 127                 function get_var($query=null,$x=0,$y=0)
 128                 {
 129
 130                         // Log how the function was called
 131                         $this->func_call = "\$db->get_var(\"$query\",$x,$y)";
 132
 133                         // If there is a query then perform it if not then use cached results..
 134                         if ( $query )
 135                         {
 136                                 $this->query($query);
 137                         }
 138
 139                         // Extract var out of cached results based x,y vals
 140                         if ( $this->last_result[$y] )
 141                         {
 142                                 $values = array_values(get_object_vars($this->last_result[$y]));
 143                         }
 144
 145                         // If there is a value return it else return null
 146                         return (isset($values[$x]) && $values[$x]!=='')?$values[$x]:null;
 147                 }
 148
 149                 /**********************************************************************
 150                 *  Get one row from the DB - see docs for more detail
 151                 */
 152
 153                 function get_row($query=null,$output=OBJECT,$y=0)
 154                 {
 155
 156                         // Log how the function was called
 157                         $this->func_call = "\$db->get_row(\"$query\",$output,$y)";
 158
 159                         // If there is a query then perform it if not then use cached results..
 160                         if ( $query )
 161                         {
 162                                 $this->query($query);
 163                         }
 164
 165                         // If the output is an object then return object using the row offset..
 166                         if ( $output == OBJECT )
 167                         {
 168                                 return $this->last_result[$y]?$this->last_result[$y]:null;
 169                         }
 170                         // If the output is an associative array then return row as such..
 171                         elseif ( $output == ARRAY_A )
 172                         {
 173                                 return $this->last_result[$y]?get_object_vars($this->last_result[$y]):null;
 174                         }
 175                         // If the output is an numerical array then return row as such..
 176                         elseif ( $output == ARRAY_N )
 177                         {
 178                                 return $this->last_result[$y]?array_values(get_object_vars($this->last_result[$y])):null;
 179                         }
 180                         // If invalid output type was specified..
 181                         else
 182                         {
 183                                 $this->show_errors ? trigger_error(" \$db->get_row(string query, output type, int offset) -- Output type must be one of: OBJECT, ARRAY_A, ARRAY_N",E_USER_WARNING) : null;
 184                         }
 185
 186                 }
 187
 188                 /**********************************************************************
 189                 *  Function to get 1 column from the cached result set based in X index
 190                 *  see docs for usage and info
 191                 */
 192
 193                 function get_col($query=null,$x=0)
 194                 {
 195
 196                         $new_array = array();
 197
 198                         // If there is a query then perform it if not then use cached results..
 199                         if ( $query )
 200                         {
 201                                 $this->query($query);
 202                         }
 203
 204                         // Extract the column values
 205                         for ( $i=0; $i < count($this->last_result); $i++ )
 206                         {
 207                                 $new_array[$i] = $this->get_var(null,$x,$i);
 208                         }
 209
 210                         return $new_array;
 211                 }
 212
 213
 214                 /**********************************************************************
 215                 *  Return the the query as a result set - see docs for more details
 216                 */
 217
 218                 function get_results($query=null, $output = OBJECT)
 219                 {
 220
 221                         // Log how the function was called
 222                         $this->func_call = "\$db->get_results(\"$query\", $output)";
 223
 224                         // If there is a query then perform it if not then use cached results..
 225                         if ( $query )
 226                         {
 227                                 $this->query($query);
 228                         }
 229
 230                         // Send back array of objects. Each row is an object
 231                         if ( $output == OBJECT )
 232                         {
 233                                 return $this->last_result;
 234                         }
 235                         elseif ( $output == ARRAY_A || $output == ARRAY_N )
 236                         {
 237                                 if ( $this->last_result )
 238                                 {
 239                                         $i=0;
 240                                         foreach( $this->last_result as $row )
 241                                         {
 242
 243                                                 $new_array[$i] = get_object_vars($row);
 244
 245                                                 if ( $output == ARRAY_N )
 246                                                 {
 247                                                         $new_array[$i] = array_values($new_array[$i]);
 248                                                 }
 249
 250                                                 $i++;
 251                                         }
 252
 253                                         return $new_array;
 254                                 }
 255                                 else
 256                                 {
 257                                         return array();
 258                                 }
 259                         }
 260                 }
 261
 262
 263                 /**********************************************************************
 264                 *  Function to get column meta data info pertaining to the last query
 265                 * see docs for more info and usage
 266                 */
 267
 268                 function get_col_info($info_type="name",$col_offset=-1)
 269                 {
 270
 271                         if ( $this->col_info )
 272                         {
 273                                 if ( $col_offset == -1 )
 274                                 {
 275                                         $i=0;
 276                                         foreach($this->col_info as $col )
 277                                         {
 278                                                 $new_array[$i] = $col->{$info_type};
 279                                                 $i++;
 280                                         }
 281                                         return $new_array;
 282                                 }
 283                                 else
 284                                 {
 285                                         return $this->col_info[$col_offset]->{$info_type};
 286                                 }
 287
 288                         }
 289
 290                 }
 291
 292                 /**********************************************************************
 293                 *  store_cache
 294                 */
 295
 296                 function store_cache($query,$is_insert)
 297                 {
 298
 299                         // The would be cache file for this query
 300                         $cache_file = $this->cache_dir.'/'.md5($query);
 301
 302                         // disk caching of queries
 303                         if ( $this->use_disk_cache && ( $this->cache_queries && ! $is_insert ) || ( $this->cache_inserts && $is_insert ))
 304                         {
 305                                 if ( ! is_dir($this->cache_dir) )
 306                                 {
 307                                         $this->register_error("Could not open cache dir: $this->cache_dir");
 308                                         $this->show_errors ? trigger_error("Could not open cache dir: $this->cache_dir",E_USER_WARNING) : null;
 309                                 }
 310                                 else
 311                                 {
 312                                         // Cache all result values
 313                                         $result_cache = array
 314                                         (
 315                                                 'col_info' => $this->col_info,
 316                                                 'last_result' => $this->last_result,
 317                                                 'num_rows' => $this->num_rows,
 318                                                 'return_value' => $this->num_rows,
 319                                         );
 320                                         file_put_contents($cache_file, serialize($result_cache));
 321                                         if( file_exists($cache_file . ".updating") )
 322                                                 unlink($cache_file . ".updating");
 323                                 }
 324                         }
 325
 326                 }
 327
 328                 /**********************************************************************
 329                 *  get_cache
 330                 */
 331
 332                 function get_cache($query)
 333                 {
 334
 335                         // The would be cache file for this query
 336                         $cache_file = $this->cache_dir.'/'.md5($query);
 337
 338                         // Try to get previously cached version
 339                         if ( $this->use_disk_cache && file_exists($cache_file) )
 340                         {
 341                                 // Only use this cache file if less than 'cache_timeout' (hours)
 342                                 if ( (time() - filemtime($cache_file)) > ($this->cache_timeout*3600) &&
 343                                         !(file_exists($cache_file . ".updating") && (time() - filemtime($cache_file . ".updating") < 60)) )
 344                                 {
 345                                         touch($cache_file . ".updating"); // Show that we in the process of updating the cache
 346                                 }
 347                                 else
 348                                 {
 349                                         $result_cache = unserialize(file_get_contents($cache_file));
 350
 351                                         $this->col_info = $result_cache['col_info'];
 352                                         $this->last_result = $result_cache['last_result'];
 353                                         $this->num_rows = $result_cache['num_rows'];
 354
 355                                         $this->from_disk_cache = true;
 356
 357                                         // If debug ALL queries
 358                                         $this->trace || $this->debug_all ? $this->debug() : null ;
 359
 360                                         return $result_cache['return_value'];
 361                                 }
 362                         }
 363
 364                 }
 365
 366                 /**********************************************************************
 367                 *  Dumps the contents of any input variable to screen in a nicely
 368                 *  formatted and easy to understand way - any type: Object, Var or Array
 369                 */
 370
 371                 function vardump($mixed='')
 372                 {
 373
 374                         // Start outup buffering
 375                         ob_start();
 376
 377                         echo "<p><table><tr><td bgcolor=ffffff><blockquote><font color=000090>";
 378                         echo "<pre><font face=arial>";
 379
 380                         if ( ! $this->vardump_called )
 381                         {
 382                                 echo "<font color=800080><b>ezSQL</b> (v".EZSQL_VERSION.") <b>Variable Dump..</b></font>\n\n";
 383                         }
 384
 385                         $var_type = gettype ($mixed);
 386                         print_r(($mixed?$mixed:"<font color=red>No Value / False</font>"));
 387                         echo "\n\n<b>Type:</b> " . ucfirst($var_type) . "\n";
 388                         echo "<b>Last Query</b> [$this->num_queries]<b>:</b> ".($this->last_query?$this->last_query:"NULL")."\n";
 389                         echo "<b>Last Function Call:</b> " . ($this->func_call?$this->func_call:"None")."\n";
 390                         echo "<b>Last Rows Returned:</b> ".count($this->last_result)."\n";
 391                         echo "</font></pre></font></blockquote></td></tr></table>".$this->donation();
 392                         echo "\n<hr size=1 noshade color=dddddd>";
 393
 394                         // Stop output buffering and capture debug HTML
 395                         $html = ob_get_contents();
 396                         ob_end_clean();
 397
 398                         // Only echo output if it is turned on
 399                         if ( $this->debug_echo_is_on )
 400                         {
 401                                 echo $html;
 402                         }
 403
 404                         $this->vardump_called = true;
 405
 406                         return $html;
 407
 408                 }
 409
 410                 /**********************************************************************
 411                 *  Alias for the above function
 412                 */
 413
 414                 function dumpvar($mixed)
 415                 {
 416                         $this->vardump($mixed);
 417                 }
 418
 419                 /**********************************************************************
 420                 *  Displays the last query string that was sent to the database & a
 421                 * table listing results (if there were any).
 422                 * (abstracted into a seperate file to save server overhead).
 423                 */
 424
 425                 function debug($print_to_screen=true)
 426                 {
 427
 428                         // Start outup buffering
 429                         ob_start();
 430
 431                         echo "<blockquote>";
 432
 433                         // Only show ezSQL credits once..
 434                         if ( ! $this->debug_called )
 435                         {
 436                                 echo "<font color=800080 face=arial size=2><b>ezSQL</b> (v".EZSQL_VERSION.") <b>Debug..</b></font><p>\n";
 437                         }
 438
 439                         if ( $this->last_error )
 440                         {
 441                                 echo "<font face=arial size=2 color=000099><b>Last Error --</b> [<font color=000000><b>$this->last_error</b></font>]<p>";
 442                         }
 443
 444                         if ( $this->from_disk_cache )
 445                         {
 446                                 echo "<font face=arial size=2 color=000099><b>Results retrieved from disk cache</b></font><p>";
 447                         }
 448
 449                         echo "<font face=arial size=2 color=000099><b>Query</b> [$this->num_queries] <b>--</b> ";
 450                         echo "[<font color=000000><b>$this->last_query</b></font>]</font><p>";
 451
 452                                 echo "<font face=arial size=2 color=000099><b>Query Result..</b></font>";
 453                                 echo "<blockquote>";
 454
 455                         if ( $this->col_info )
 456                         {
 457
 458                                 // =====================================================
 459                                 // Results top rows
 460
 461                                 echo "<table cellpadding=5 cellspacing=1 bgcolor=555555>";
 462                                 echo "<tr bgcolor=eeeeee><td nowrap valign=bottom><font color=555599 face=arial size=2><b>(row)</b></font></td>";
 463
 464
 465                                 for ( $i=0; $i < count($this->col_info); $i++ )
 466                                 {
 467                                         echo "<td nowrap align=left valign=top><font size=1 color=555599 face=arial>{$this->col_info[$i]->type} {$this->col_info[$i]->max_length}</font><br><span style='font-family: arial; font-size: 10pt; font-weight: bold;'>{$this->col_info[$i]->name}</span></td>";
 468                                 }
 469
 470                                 echo "</tr>";
 471
 472                                 // ======================================================
 473                                 // print main results
 474
 475                         if ( $this->last_result )
 476                         {
 477
 478                                 $i=0;
 479                                 foreach ( $this->get_results(null,ARRAY_N) as $one_row )
 480                                 {
 481                                         $i++;
 482                                         echo "<tr bgcolor=ffffff><td bgcolor=eeeeee nowrap align=middle><font size=2 color=555599 face=arial>$i</font></td>";
 483
 484                                         foreach ( $one_row as $item )
 485                                         {
 486                                                 echo "<td nowrap><font face=arial size=2>$item</font></td>";
 487                                         }
 488
 489                                         echo "</tr>";
 490                                 }
 491
 492                         } // if last result
 493                         else
 494                         {
 495                                 echo "<tr bgcolor=ffffff><td colspan=".(count($this->col_info)+1)."><font face=arial size=2>No Results</font></td></tr>";
 496                         }
 497
 498                         echo "</table>";
 499
 500                         } // if col_info
 501                         else
 502                         {
 503                                 echo "<font face=arial size=2>No Results</font>";
 504                         }
 505
 506                         echo "</blockquote></blockquote>".$this->donation()."<hr noshade color=dddddd size=1>";
 507
 508                         // Stop output buffering and capture debug HTML
 509                         $html = ob_get_contents();
 510                         ob_end_clean();
 511
 512                         // Only echo output if it is turned on
 513                         if ( $this->debug_echo_is_on && $print_to_screen)
 514                         {
 515                                 echo $html;
 516                         }
 517
 518                         $this->debug_called = true;
 519
 520                         return $html;
 521
 522                 }
 523
 524                 /**********************************************************************
 525                 *  Naughty little function to ask for some remuniration!
 526                 */
 527
 528                 function donation()
 529                 {
 530                         return "<font size=1 face=arial color=000000>If ezSQL has helped <a href=\"https://www.paypal.com/xclick/business=justin%40justinvincent.com&item_name=ezSQL&no_note=1&tax=0\" style=\"color: 0000CC;\">make a donation!?</a> &nbsp;&nbsp;<!--[ go on! you know you want to! ]--></font>";
 531                 }
 532
 533                 /**********************************************************************
 534                 *  Timer related functions
 535                 */
 536
 537                 function timer_get_cur()
 538                 {
 539                         list($usec, $sec) = explode(" ",microtime());
 540                         return ((float)$usec + (float)$sec);
 541                 }
 542
 543                 function timer_start($timer_name)
 544                 {
 545                         $this->timers[$timer_name] = $this->timer_get_cur();
 546                 }
 547
 548                 function timer_elapsed($timer_name)
 549                 {
 550                         return round($this->timer_get_cur() - $this->timers[$timer_name],2);
 551                 }
 552
 553                 function timer_update_global($timer_name)
 554                 {
 555                         if ( $this->do_profile )
 556                         {
 557                                 $this->profile_times[] = array
 558                                 (
 559                                         'query' => $this->last_query,
 560                                         'time' => $this->timer_elapsed($timer_name)
 561                                 );
 562                         }
 563
 564                         $this->total_query_time += $this->timer_elapsed($timer_name);
 565                 }
 566
 567                 /**********************************************************************
 568                 * Creates a SET nvp sql string from an associative array (and escapes all values)
 569                 *
 570                 *  Usage:
 571                 *
 572                 *     $db_data = array('login'=>'jv','email'=>'jv@vip.ie', 'user_id' => 1, 'created' => 'NOW()');
 573                 *
 574                 *     $db->query("INSERT INTO users SET ".$db->get_set($db_data));
 575                 *
 576                 *     ...OR...
 577                 *
 578                 *     $db->query("UPDATE users SET ".$db->get_set($db_data)." WHERE user_id = 1");
 579                 *
 580                 * Output:
 581                 *
 582                 *     login = 'jv', email = 'jv@vip.ie', user_id = 1, created = NOW()
 583                 */
 584
 585                 function get_set($params)
 586                 {
 587                         if( !is_array( $params ) )
 588                         {
 589                                 $this->register_error( 'get_set() parameter invalid. Expected array in '.__FILE__.' on line '.__LINE__);
 590                                 return;
 591                         }
 592                         $sql = array();
 593                         foreach ( $params as $field => $val )
 594                         {
 595                                 if ( $val === 'true' || $val === true )
 596                                         $val = 1;
 597                                 if ( $val === 'false' || $val === false )
 598                                         $val = 0;
 599
 600                                 switch( $val ){
 601                                         case 'NOW()' :
 602                                         case 'NULL' :
 603                                           $sql[] = "$field = $val";
 604                                                 break;
 605                                         default :
 606                                                 $sql[] = "$field = '".$this->escape( $val )."'";
 607                                 }
 608                         }
 609
 610                         return implode( ', ' , $sql );
 611                 }
 612
 613         }