[Sommaire] [Imprimer]

   1  <?php # $Id: mysql.inc.php 1636 2007-02-27 12:15:00Z garvinhicking $
   2  # Copyright (c) 2003-2005, Jannis Hermanns (on behalf the Serendipity Developer Team)
   3  # All rights reserved.  See LICENSE file for licensing details
   4  
   5  /**
   6   * Tells the DB Layer to start a DB transaction.
   7   *
   8   * @access public
   9   */
  10  function serendipity_db_begin_transaction(){
  11      serendipity_db_query('start transaction');
  12  }
  13  
  14  /**
  15   * Tells the DB Layer to end a DB transaction.
  16   *
  17   * @access public
  18   * @param  boolean  If true, perform the query. If false, rollback.
  19   */
  20  function serendipity_db_end_transaction($commit){
  21      if ($commit){
  22          serendipity_db_query('commit');
  23      }else{
  24          serendipity_db_query('rollback');
  25      }
  26  }
  27  
  28  /**
  29   * Assemble and return SQL condition for a "IN (...)" clause
  30   *
  31   * @access public
  32   * @param  string   table column name
  33   * @param  array    referenced array of values to search for in the "IN (...)" clause
  34   * @param  string   condition of how to associate the different input values of the $search_ids parameter
  35   * @return string   resulting SQL string
  36   */
  37  function serendipity_db_in_sql($col, &$search_ids, $type = ' OR ') {
  38      return $col . " IN (" . implode(', ', $search_ids) . ")";
  39  }
  40  
  41  /**
  42   * Perform a DB Layer SQL query.
  43   *
  44   * This function returns values dependin on the input parameters and the result of the query.
  45   * It can return:
  46   *   false if there was an error,
  47   *   true if the query succeeded but did not generate any rows
  48   *   array of field values if it returned a single row and $single is true
  49   *   array of array of field values if it returned row(s) [stacked array]
  50   *
  51   * @access public
  52   * @param   string      SQL query to execute
  53   * @param   boolean     Toggle whether the expected result is a single row (TRUE) or multiple rows (FALSE). This affects whether the returned array is 1 or 2 dimensional!
  54   * @param   string      Result type of the array indexing. Can be one of "assoc" (associative), "num" (numerical), "both" (numerical and associative, default)
  55   * @param   boolean     If true, errors will be reported. If false, errors will be ignored.
  56   * @param   string      A possible array key name, so that you can control the multi-dimensional mapping of an array by the key column
  57   * @param   string      A possible array field name, so that you can control the multi-dimensional mapping of an array by the key column and the field value.
  58   * @param   boolean     If true, the executed SQL error is known to fail, and should be disregarded (errors can be ignroed on DUPLICATE INDEX queries and the likes)
  59   * @return  mixed       Returns the result of the SQL query, depending on the input parameters
  60   */
  61  function &serendipity_db_query($sql, $single = false, $result_type = "both", $reportErr = false, $assocKey = false, $assocVal = false, $expectError = false) {
  62      global $serendipity;
  63      static $type_map = array(
  64                           'assoc' => MYSQL_ASSOC,
  65                           'num'   => MYSQL_NUM,
  66                           'both'  => MYSQL_BOTH,
  67                           'true'  => true,
  68                           'false' => false
  69      );
  70      static $benchmark = false;
  71  
  72      // highlight_string(var_export($sql, 1));
  73  
  74      if (!is_resource($serendipity['dbConn'])) {
  75          return false;
  76      }
  77  
  78      if ($benchmark) {
  79          $start = microtime_float();
  80      }
  81      if ($expectError) {
  82          $c = @mysql_query($sql, $serendipity['dbConn']);
  83      } else {
  84          $c = mysql_query($sql, $serendipity['dbConn']);
  85      }
  86      if ($benchmark) {
  87          $end = microtime_float();
  88          mysql_query("INSERT INTO BLOGLOG (request, timestamp, sql, exec_time, ip) VALUES ('" . serendipity_db_escape_string($_SERVER['REQUEST_URI']) . "', NOW(), '" . serendipity_db_escape_string($sql) . "', '" . (number_format($end-$start, 10)) . "', '" . serendipity_db_escape_string($_SERVER['REMOTE_ADDR']) . "')");
  89          
  90          $psql = $sql;
  91          $psql = preg_replace('@[0-9]{10}@', 'TIMESTAMP', $psql);
  92          mysql_query("UPDATE BLOGLOG_TOTAL SET counter = counter + 1 WHERE sql = '" . serendipity_db_escape_string($psql) . "'");
  93          if (mysql_affected_rows() < 1) {
  94              mysql_query("INSERT INTO BLOGLOG_TOTAL (sql, counter) VALUES ('" . serendipity_db_escape_string($psql) . "', 1)");
  95          }
  96      }
  97  
  98      if (!$expectError && mysql_error($serendipity['dbConn']) != '') {
  99          $msg = '<pre>' . $sql . '</pre> / ' . mysql_error($serendipity['dbConn']);
 100          return $msg;
 101      }
 102  
 103      if (!$c) {
 104          if (!$expectError && !$serendipity['production']) {
 105              print '<pre>' . $sql . '</pre> / ' . mysql_error($serendipity['dbConn']);
 106              if (function_exists('debug_backtrace') && $reportErr == true) {
 107                  highlight_string(var_export(debug_backtrace(), 1));
 108              }
 109          }
 110  
 111          return $type_map['false'];
 112      }
 113      if ($c === true) {
 114          return $type_map['true'];
 115      }
 116  
 117      $result_type = $type_map[$result_type];
 118  
 119      switch(mysql_num_rows($c)) {
 120          case 0:
 121              if ($single) {
 122                  return $type_map['false'];
 123              }
 124              return $type_map['true'];
 125          case 1:
 126              if ($single) {
 127                  return mysql_fetch_array($c, $result_type);
 128              }
 129          default:
 130              if ($single) {
 131                  return mysql_fetch_array($c, $result_type);
 132              }
 133  
 134              $rows = array();
 135              while (($row = mysql_fetch_array($c, $result_type))) {
 136                  if (!empty($assocKey)) {
 137                      // You can fetch a key-associated array via the two function parameters assocKey and assocVal
 138                      if (empty($assocVal)) {
 139                          $rows[$row[$assocKey]] = $row;
 140                      } else {
 141                          $rows[$row[$assocKey]] = $row[$assocVal];
 142                      }
 143                  } else {
 144                      $rows[] = $row;
 145                  }
 146              }
 147              return $rows;
 148      }
 149  }
 150  
 151  /**
 152   * Returns the latest INSERT_ID of an SQL INSERT INTO command, for auto-increment columns
 153   *
 154   * @access public
 155   * @return int      Value of the auto-increment column
 156   */
 157  function serendipity_db_insert_id() {
 158      global $serendipity;
 159  
 160      return mysql_insert_id($serendipity['dbConn']);
 161  }
 162  
 163  /**
 164   * Returns the number of affected rows of a SQL query
 165   *
 166   * @access public
 167   * @return int      Number of affected rows
 168   */
 169  function serendipity_db_affected_rows() {
 170      global $serendipity;
 171  
 172      return mysql_affected_rows($serendipity['dbConn']);
 173  }
 174  
 175  /**
 176   * Returns the number of updated rows in a SQL query
 177   *
 178   * @access public
 179   * @return int  Number of updated rows
 180   */
 181  function serendipity_db_updated_rows() {
 182      global $serendipity;
 183  
 184      preg_match(
 185          "/^[^0-9]+([0-9]+)[^0-9]+([0-9]+)[^0-9]+([0-9]+)/",
 186          mysql_info(),
 187          $arr);
 188          // mysql_affected_rows returns 0 if rows were matched but not changed.
 189          // mysql_info returns rows matched AND rows changed
 190          return $arr[2];
 191  }
 192  
 193  /**
 194   * Returns the number of matched rows in a SQL query
 195   *
 196   * @access public
 197   * @return int  Number of matched rows
 198   */
 199  function serendipity_db_matched_rows() {
 200      global $serendipity;
 201  
 202      preg_match(
 203          "/^[^0-9]+([0-9]+)[^0-9]+([0-9]+)[^0-9]+([0-9]+)/",
 204          mysql_info(),
 205          $arr);
 206          // mysql_affected_rows returns 0 if rows were matched but not changed.
 207          // mysql_info returns rows matched AND rows changed
 208          return $arr[1];
 209  }
 210  
 211  /**
 212   * Returns a escaped string, so that it can be safely included in a SQL string encapsulated within quotes, without allowing SQL injection.
 213   *
 214   * @access  public
 215   * @param   string   input string
 216   * @return  string   output string
 217   */
 218  function serendipity_db_escape_string($string) {
 219      global $serendipity;
 220  
 221      if (is_resource($serendipity['dbConn']) && function_exists('mysql_real_escape_string')) {
 222          return mysql_real_escape_string($string, $serendipity['dbConn']);
 223      } else {
 224          return mysql_escape_string($string);
 225      }
 226  }
 227  
 228  /**
 229   * Returns the option to a LIMIT SQL statement, because it varies accross DB systems
 230   *
 231   * @access public
 232   * @param  int      Number of the first row to return data from
 233   * @param  int      Number of rows to return
 234   * @return string   SQL string to pass to a LIMIT statement
 235   */
 236  function serendipity_db_limit($start, $offset) {
 237      return $start . ', ' . $offset;
 238  }
 239  
 240  /**
 241   * Return a LIMIT SQL option to the DB Layer as a full LIMIT statement
 242   *
 243   * @access public
 244   * @param   SQL string of a LIMIT option
 245   * @return  SQL string containing a full LIMIT statement
 246   */
 247  function serendipity_db_limit_sql($limitstring) {
 248      return ' LIMIT ' . $limitstring;
 249  }
 250  
 251  /**
 252   * Connect to the configured Database
 253   *
 254   * @access public
 255   * @return  ressource   connection handle
 256   */
 257  function serendipity_db_connect() {
 258      global $serendipity;
 259  
 260      if (isset($serendipity['dbConn'])) {
 261          return $serendipity['dbConn'];
 262      }
 263  
 264      if (isset($serendipity['dbPersistent']) && $serendipity['dbPersistent']) {
 265          $function = 'mysql_pconnect';
 266      } else {
 267          $function = 'mysql_connect';
 268      }
 269  
 270      $serendipity['dbConn'] = $function($serendipity['dbHost'], $serendipity['dbUser'], $serendipity['dbPass']);
 271      mysql_select_db($serendipity['dbName']);
 272      serendipity_db_reconnect();
 273  
 274      return $serendipity['dbConn'];
 275  }
 276  
 277  function serendipity_db_reconnect() {
 278      global $serendipity;
 279  
 280      if (isset($serendipity['dbCharset'])) {
 281          mysql_query("SET NAMES " . $serendipity['dbCharset'], $serendipity['dbConn']);
 282          define('SQL_CHARSET_INIT', true);
 283      } elseif (defined('SQL_CHARSET') && $serendipity['dbNames'] && !defined('SQL_CHARSET_INIT')) {
 284          mysql_query("SET NAMES " . SQL_CHARSET, $serendipity['dbConn']);
 285      }
 286  }
 287  
 288  /**
 289   * Prepares a Serendipty query input to fully valid SQL. Replaces certain "template" variables.
 290   *
 291   * @access public
 292   * @param  string   SQL query with template variables to convert
 293   * @return ressource    SQL ressource handle of the executed query
 294   */
 295  function serendipity_db_schema_import($query) {
 296      static $search  = array('{AUTOINCREMENT}', '{PRIMARY}',
 297          '{UNSIGNED}', '{FULLTEXT}', '{FULLTEXT_MYSQL}', '{BOOLEAN}');
 298      static $replace = array('int(11) not null auto_increment', 'primary key',
 299          'unsigned'  , 'FULLTEXT', 'FULLTEXT', 'enum (\'true\', \'false\') NOT NULL default \'true\'');
 300      static $is_utf8 = null;
 301      global $serendipity;
 302  
 303      if ($is_utf8 === null) {
 304          $search[] = '{UTF_8}';
 305          if (  (isset($_POST['charset']) && $_POST['charset'] == 'UTF-8/') ||
 306                $serendipity['charset'] == 'UTF-8/' ||
 307                $serendipity['POST']['charset'] == 'UTF-8/' ||
 308                LANG_CHARSET == 'UTF-8' ) {
 309              $replace[] = '/*!40100 CHARACTER SET utf8 COLLATE utf8_unicode_ci */';
 310          } else {
 311              $replace[] = '';
 312          }
 313      }
 314  
 315      $query = trim(str_replace($search, $replace, $query));
 316  
 317      if ($query{0} == '@') {
 318          // Errors are expected to happen (like duplicate index creation)
 319          return serendipity_db_query(substr($query, 1), false, 'both', false, false, false, true);
 320      } else {
 321          return serendipity_db_query($query);
 322      }
 323  }
 324  
 325  /**
 326   * Try to connect to the configured Database (during installation)
 327   *
 328   * @access public
 329   * @param  array     input configuration array, holding the connection info
 330   * @param  array     referenced array which holds the errors that might be encountered
 331   * @return boolean   return true on success, false on error
 332   */
 333  function serendipity_db_probe($hash, &$errs) {
 334      global $serendipity;
 335  
 336      if (!function_exists('mysql_connect')) {
 337          $errs[] = 'No mySQL extension found. Please check your webserver installation or contact your systems administrator regarding this problem.';
 338          return false;
 339      }
 340  
 341      if (!($c = @mysql_connect($hash['dbHost'], $hash['dbUser'], $hash['dbPass']))) {
 342          $errs[] = 'Could not connect to database; check your settings.';
 343          $errs[] = 'The mySQL error was: ' . mysql_error();
 344          return false;
 345      }
 346  
 347      $serendipity['dbConn'] = $c;
 348  
 349      if (!@mysql_select_db($hash['dbName'])) {
 350          $errs[] = 'The database you specified does not exist.';
 351          $errs[] = 'The mySQL error was: ' . mysql_error();
 352          return false;
 353      }
 354  
 355      return true;
 356  }
 357  
 358  /**
 359   * Returns the SQL code used for concatenating strings
 360   *
 361   * @access public
 362   * @param  string   Input string/column to concatenate
 363   * @return string   SQL parameter
 364   */
 365  function serendipity_db_concat($string) {
 366      return 'concat(' . $string . ')';
 367  }
 368  
 369  /* vim: set sts=4 ts=4 expandtab : */