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