[Sommaire] [Imprimer]

   1  <?php
   2  /** 

   3   * MySQL query_factory Class.

   4   * Class used for database abstraction to MySQL

   5   *

   6   * @package classes

   7   * @copyright Copyright 2003-2006 Zen Cart Development Team

   8   * @copyright Portions Copyright 2003 osCommerce

   9   * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0

  10   * @version $Id: query_factory.php 4854 2006-10-28 06:50:01Z drbyte $

  11   */
  12  if (!defined('IS_ADMIN_FLAG')) {
  13    die('Illegal Access');
  14  }
  15  /**

  16   * Queryfactory - A simple database abstraction layer

  17   *

  18   */
  19  class queryFactory extends base {
  20  
  21    function queryFactory() {
  22      $this->count_queries = 0;
  23      $this->total_query_time = 0;
  24    }
  25  
  26    function connect($zf_host, $zf_user, $zf_password, $zf_database, $zf_pconnect = 'false', $zp_real = false) {
  27  //@TODO error class required to virtualise & centralise all error reporting/logging/debugging

  28      $this->database = $zf_database;
  29      if (!function_exists('mysql_connect')) die ('Call to undefined function: mysql_connect().  Please install the MySQL Connector for PHP');
  30      if ($zf_pconnect != 'false') {
  31        $this->link = @mysql_connect($zf_host, $zf_user, $zf_password, true);
  32      } else {
  33      // pconnect disabled ... leaving it as "connect" here instead of "pconnect"

  34        $this->link = @mysql_connect($zf_host, $zf_user, $zf_password, true);
  35      }
  36      if ($this->link) {
  37        if (@mysql_select_db($zf_database, $this->link)) {
  38          $this->db_connected = true;
  39          return true;
  40        } else {
  41          $this->set_error(mysql_errno(),mysql_error(), $zp_real);
  42          return false;
  43        }
  44      } else {
  45        $this->set_error(mysql_errno(),mysql_error(), $zp_real);
  46        return false;
  47      }
  48    }
  49  
  50    function selectdb($zf_database) {
  51      @mysql_select_db($zf_database, $this->link);
  52    }
  53  
  54    function prepare_input($zp_string) {
  55      if (function_exists('mysql_real_escape_string')) {
  56        return mysql_real_escape_string($zp_string);
  57      } elseif (function_exists('mysql_escape_string')) {
  58        return mysql_escape_string($zp_string);
  59      } else {
  60        return addslashes($zp_string);
  61      }
  62    }
  63  
  64    function close() {
  65      @mysql_close($this->link);
  66    }
  67  
  68    function set_error($zp_err_num, $zp_err_text, $zp_fatal = true) {
  69      $this->error_number = $zp_err_num;
  70      $this->error_text = $zp_err_text;
  71      if ($zp_fatal && $zp_err_num != 1141) { // error 1141 is okay ... should not die on 1141, but just continue on instead
  72        $this->show_error();
  73        die();
  74      }
  75    }
  76  
  77    function show_error() {
  78      if ($this->error_number == 0 && $this->error_text == DB_ERROR_NOT_CONNECTED && !headers_sent() && file_exists('nddbc.html') ) include ('nddbc.html');
  79      echo '<div class="systemError">';
  80      echo $this->error_number . ' ' . $this->error_text;
  81      echo '<br />in:<br />[' . (strstr($this->zf_sql, 'db_cache') ? 'db_cache table' : $this->zf_sql) . ']<br />';
  82      if (defined('IS_ADMIN_FLAG') && IS_ADMIN_FLAG==true) echo 'If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.<br />';
  83      echo '</div>';
  84    }
  85  
  86    function Execute($zf_sql, $zf_limit = false, $zf_cache = false, $zf_cachetime=0) {
  87      // bof: collect database queries

  88      if (defined('STORE_DB_TRANSACTIONS') && STORE_DB_TRANSACTIONS=='true') {
  89        global $PHP_SELF, $box_id, $current_page_base;
  90        if (strtoupper(substr($zf_sql,0,6))=='SELECT' /*&& strstr($zf_sql,'products_id')*/) {
  91          $f=@fopen(DIR_FS_SQL_CACHE.'/query_selects_' . $current_page_base . '_' . time() . '.txt','a');
  92          if ($f) {
  93            fwrite($f,  "\n\n" . 'I AM HERE ' . $current_page_base . /*zen_get_all_get_params() .*/ "\n" . 'sidebox: ' . $box_id . "\n\n" . "Explain \n" . $zf_sql.";\n\n");
  94            fclose($f);
  95          }
  96          unset($f);
  97        }
  98      }
  99      // eof: collect products_id queries

 100      global $zc_cache;
 101      if ($zf_limit) {
 102        $zf_sql = $zf_sql . ' LIMIT ' . $zf_limit;
 103      }
 104      $this->zf_sql = $zf_sql;
 105      if ( $zf_cache AND $zc_cache->sql_cache_exists($zf_sql) AND !$zc_cache->sql_cache_is_expired($zf_sql, $zf_cachetime) ) {
 106        $obj = new queryFactoryResult;
 107        $obj->cursor = 0;
 108        $obj->is_cached = true;
 109        $obj->sql_query = $zf_sql;
 110        $zp_result_array = $zc_cache->sql_cache_read($zf_sql);
 111        $obj->result = $zp_result_array;
 112        if (sizeof($zp_result_array) > 0 ) {
 113          $obj->EOF = false;
 114          while (list($key, $value) = each($zp_result_array[0])) {
 115            $obj->fields[$key] = $value;
 116          }
 117          return $obj;
 118        } else {
 119          $obj->EOF = true;
 120        }
 121      } elseif ($zf_cache) {
 122        $zc_cache->sql_cache_expire_now($zf_sql);
 123        $time_start = explode(' ', microtime());
 124        $obj = new queryFactoryResult;
 125        $obj->sql_query = $zf_sql;
 126        if (!$this->db_connected) $this->set_error('0', DB_ERROR_NOT_CONNECTED);
 127        $zp_db_resource = @mysql_query($zf_sql, $this->link);
 128        if (!$zp_db_resource) $this->set_error(@mysql_errno(),@mysql_error());
 129        $obj->resource = $zp_db_resource;
 130        $obj->cursor = 0;
 131        $obj->is_cached = true;
 132        if ($obj->RecordCount() > 0) {
 133          $obj->EOF = false;
 134          $zp_ii = 0;
 135      while (!$obj->EOF) {
 136            $zp_result_array = @mysql_fetch_array($zp_db_resource);
 137            if ($zp_result_array) {
 138              while (list($key, $value) = each($zp_result_array)) {
 139                if (!ereg('^[0-9]', $key)) {
 140                  $obj->result[$zp_ii][$key] = $value;
 141                }
 142              }
 143            } else {
 144              $obj->Limit = $zp_ii;
 145              $obj->EOF = true;
 146            }
 147            $zp_ii++;
 148      }
 149          while (list($key, $value) = each($obj->result[$obj->cursor])) {
 150            if (!ereg('^[0-9]', $key)) {
 151              $obj->fields[$key] = $value;
 152        }
 153          }
 154          $obj->EOF = false;
 155        } else {
 156      $obj->EOF = true;
 157        }
 158        $zc_cache->sql_cache_store($zf_sql, $obj->result);
 159        $time_end = explode (' ', microtime());
 160        $query_time = $time_end[1]+$time_end[0]-$time_start[1]-$time_start[0];
 161        $this->total_query_time += $query_time;
 162        $this->count_queries++;
 163        return($obj);
 164      } else {
 165        $time_start = explode(' ', microtime());
 166        $obj = new queryFactoryResult;
 167        if (!$this->db_connected) $this->set_error('0', DB_ERROR_NOT_CONNECTED);
 168        $zp_db_resource = @mysql_query($zf_sql, $this->link);
 169        if (!$zp_db_resource) $this->set_error(@mysql_errno($this->link),@mysql_error($this->link));
 170        $obj->resource = $zp_db_resource;
 171        $obj->cursor = 0;
 172        if ($obj->RecordCount() > 0) {
 173          $obj->EOF = false;
 174          $zp_result_array = @mysql_fetch_array($zp_db_resource);
 175          if ($zp_result_array) {
 176            while (list($key, $value) = each($zp_result_array)) {
 177              if (!ereg('^[0-9]', $key)) {
 178                $obj->fields[$key] = $value;
 179              }
 180            }
 181            $obj->EOF = false;
 182          } else {
 183            $obj->EOF = true;
 184          }
 185        } else {
 186          $obj->EOF = true;
 187        }
 188  
 189        $time_end = explode (' ', microtime());
 190        $query_time = $time_end[1]+$time_end[0]-$time_start[1]-$time_start[0];
 191        $this->total_query_time += $query_time;
 192        $this->count_queries++;
 193        return($obj);
 194      }
 195    }
 196  
 197    function ExecuteRandomMulti($zf_sql, $zf_limit = 0, $zf_cache = false, $zf_cachetime=0) {
 198      $this->zf_sql = $zf_sql;
 199      $time_start = explode(' ', microtime());
 200      $obj = new queryFactoryResult;
 201      $obj->result = array();
 202      if (!$this->db_connected) $this->set_error('0', DB_ERROR_NOT_CONNECTED);
 203      $zp_db_resource = @mysql_query($zf_sql, $this->link);
 204      if (!$zp_db_resource) $this->set_error(mysql_errno(),mysql_error());
 205      $obj->resource = $zp_db_resource;
 206      $obj->cursor = 0;
 207      $obj->Limit = $zf_limit;
 208      if ($obj->RecordCount() > 0 && $zf_limit > 0) {
 209        $obj->EOF = false;
 210        $zp_Start_row = 0;
 211        if ($zf_limit) {
 212        $zp_start_row = zen_rand(0, $obj->RecordCount() - $zf_limit);
 213        }
 214        $obj->Move($zp_start_row);
 215        $obj->Limit = $zf_limit;
 216        $zp_ii = 0;
 217        while (!$obj->EOF) {
 218          $zp_result_array = @mysql_fetch_array($zp_db_resource);
 219          if ($zp_ii == $zf_limit) $obj->EOF = true;
 220          if ($zp_result_array) {
 221            while (list($key, $value) = each($zp_result_array)) {
 222              $obj->result[$zp_ii][$key] = $value;
 223            }
 224          } else {
 225            $obj->Limit = $zp_ii;
 226            $obj->EOF = true;
 227          }
 228          $zp_ii++;
 229        }
 230        $obj->result_random = array_rand($obj->result, sizeof($obj->result));
 231        if (is_array($obj->result_random)) {
 232          $zp_ptr = $obj->result_random[$obj->cursor];
 233        } else {
 234          $zp_ptr = $obj->result_random;
 235        }
 236        while (list($key, $value) = each($obj->result[$zp_ptr])) {
 237          if (!ereg('^[0-9]', $key)) {
 238            $obj->fields[$key] = $value;
 239      }
 240        }
 241        $obj->EOF = false;
 242      } else {
 243        $obj->EOF = true;
 244      }
 245  
 246  
 247      $time_end = explode (' ', microtime());
 248      $query_time = $time_end[1]+$time_end[0]-$time_start[1]-$time_start[0];
 249      $this->total_query_time += $query_time;
 250      $this->count_queries++;
 251      return($obj);
 252    }
 253  
 254    function insert_ID() {
 255      return @mysql_insert_id($this->link);
 256    }
 257  
 258    function metaColumns($zp_table) {
 259      $res = @mysql_query("select * from " . $zp_table . " limit 1", $this->link);
 260      $num_fields = @mysql_num_fields($res);
 261      for ($i = 0; $i < $num_fields; $i++) {
 262       $obj[strtoupper(@mysql_field_name($res, $i))] = new queryFactoryMeta($i, $res);
 263      }
 264      return $obj;
 265  
 266    }
 267  
 268    function get_server_info() {
 269      if ($this->link) {
 270        return mysql_get_server_info($this->link);
 271      } else {
 272        return UNKNOWN;
 273      }
 274    }
 275  
 276    function queryCount() {
 277      return $this->count_queries;
 278    }
 279  
 280    function queryTime() {
 281      return $this->total_query_time;
 282    }
 283    function perform ($tableName, $tableData, $performType='INSERT', $performFilter='', $debug=false) {
 284      switch (strtolower($performType)) {
 285        case 'insert':
 286        $insertString = "";
 287        $insertString = "INSERT INTO " . $tableName . " (";
 288        foreach ($tableData as $key => $value) {
 289          if ($debug === true) {
 290            echo $value['fieldName'] . '#';
 291          }
 292          $insertString .= $value['fieldName'] . ", ";
 293        }      
 294        $insertString = substr($insertString, 0, strlen($insertString)-2) . ') VALUES (';
 295        reset($tableData);
 296        foreach ($tableData as $key => $value) {
 297          $bindVarValue = $this->getBindVarValue($value['value'], $value['type']);
 298          $insertString .= $bindVarValue . ", ";
 299        }  
 300        $insertString = substr($insertString, 0, strlen($insertString)-2) . ')';   
 301        if ($debug === true) {
 302          echo $insertString;
 303          die(); 
 304        } else {
 305          $this->execute($insertString);
 306        }
 307        break;
 308        case 'update':
 309        $updateString ="";
 310        $updateString = 'UPDATE ' . $tableName . ' SET ';
 311        foreach ($tableData as $key => $value) {
 312          $bindVarValue = $this->getBindVarValue($value['value'], $value['type']);
 313          $updateString .= $value['fieldName'] . '=' . $bindVarValue . ', ';  
 314        }
 315        $updateString = substr($updateString, 0, strlen($updateString)-2);
 316        if ($performFilter != '') {
 317          $updateString .= ' WHERE ' . $performFilter;
 318        }
 319        if ($debug === true) {
 320          echo $updateString;
 321          die(); 
 322        } else {
 323          $this->execute($updateString);
 324        }
 325        break;
 326      }
 327    }
 328    function getBindVarValue($value, $type) {
 329      $typeArray = explode(':',$type);
 330      $type = $typeArray[0];
 331      switch ($type) {
 332        case 'csv':
 333          return $value;
 334        break;
 335        case 'passthru':
 336          return $value;
 337        break;
 338        case 'float':
 339          return (!zen_not_null($value) || $value=='' || $value == 0) ? 0 : $value;
 340        break;
 341        case 'integer':
 342          return (int)$value;
 343        break;
 344        case 'string':
 345          if (isset($typeArray[1])) {
 346            $regexp = $typeArray[1];
 347          }
 348          return '\'' . $this->prepare_input($value) . '\'';
 349        break;
 350        case 'noquotestring':
 351          return $this->prepare_input($value);
 352        break;
 353        case 'currency':
 354          return '\'' . $this->prepare_input($value) . '\'';
 355        break;
 356        case 'date':
 357          return '\'' . $this->prepare_input($value) . '\'';
 358        break;
 359        case 'enum':
 360          if (isset($typeArray[1])) {
 361            $enumArray = explode('|', $typeArray[1]);
 362          }
 363          return '\'' . $this->prepare_input($value) . '\'';
 364        default:
 365        die('var-type undefined: ' . $type . '('.$value.')');
 366      }
 367    }
 368  /** 

 369   * method to do bind variables to a query

 370  **/
 371    function bindVars($sql, $bindVarString, $bindVarValue, $bindVarType, $debug = false) {
 372      $bindVarTypeArray = explode(':', $bindVarType);
 373      $sqlNew = $this->getBindVarValue($bindVarValue, $bindVarType);
 374      $sqlNew = str_replace($bindVarString, $sqlNew, $sql);
 375      return $sqlNew;
 376    }  
 377    
 378    function prepareInput($string) {
 379      return @mysql_real_escape_string($string);
 380    }
 381  }
 382  
 383  class queryFactoryResult {
 384  
 385    function queryFactoryResult() {
 386      $this->is_cached = false;
 387    }
 388  
 389    function MoveNext() {
 390      global $zc_cache;
 391      $this->cursor++;
 392      if ($this->is_cached) {
 393        if ($this->cursor >= sizeof($this->result)) {
 394          $this->EOF = true;
 395        } else {
 396          while(list($key, $value) = each($this->result[$this->cursor])) {
 397        $this->fields[$key] = $value;
 398      }
 399        }
 400      } else {
 401        $zp_result_array = @mysql_fetch_array($this->resource);
 402        if (!$zp_result_array) {
 403          $this->EOF = true;
 404        } else {
 405          while (list($key, $value) = each($zp_result_array)) {
 406            if (!ereg('^[0-9]', $key)) {
 407              $this->fields[$key] = $value;
 408            }
 409          }
 410        }
 411      }
 412    }
 413  
 414    function MoveNextRandom() {
 415      $this->cursor++;
 416      if ($this->cursor < $this->Limit) {
 417        $zp_result_array = $this->result[$this->result_random[$this->cursor]];
 418        while (list($key, $value) = each($zp_result_array)) {
 419          if (!ereg('^[0-9]', $key)) {
 420            $this->fields[$key] = $value;
 421      }
 422        }
 423      } else {
 424        $this->EOF = true;
 425      }
 426    }
 427  
 428      function RecordCount() {
 429      return @mysql_num_rows($this->resource);
 430    }
 431  
 432    function Move($zp_row) {
 433      global $db;
 434      if (@mysql_data_seek($this->resource, $zp_row)) {
 435        $zp_result_array = @mysql_fetch_array($this->resource);
 436          while (list($key, $value) = each($zp_result_array)) {
 437            $this->fields[$key] = $value;
 438          }
 439        @mysql_data_seek($this->resource, $zp_row);
 440        $this->EOF = false;
 441        return;
 442      } else {
 443        $this->EOF = true;
 444        $db->set_error(mysql_errno(),mysql_error());
 445      }
 446    }
 447  }
 448  
 449  class queryFactoryMeta {
 450  
 451    function queryFactoryMeta($zp_field, $zp_res) {
 452      $this->type = @mysql_field_type($zp_res, $zp_field);
 453      $this->max_length = @mysql_field_len($zp_res, $zp_field);
 454    }
 455  }
 456  ?>