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