[Sommaire] [Imprimer]

   1  <?php # $Id: sqlite.inc.php 1670 2007-04-10 13:23:34Z 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  if (!function_exists('sqlite_open')) {
   6      @dl('sqlite.so');
   7      @dl('sqlite.dll');
   8  }
   9  
  10  /**
  11   * Tells the DB Layer to start a DB transaction.
  12   *
  13   * @access public
  14   */
  15  function serendipity_db_begin_transaction(){
  16      serendipity_db_query('begin transaction');
  17  }
  18  
  19  /**
  20   * Tells the DB Layer to end a DB transaction.
  21   *
  22   * @access public
  23   * @param  boolean  If true, perform the query. If false, rollback.
  24   */
  25  function serendipity_db_end_transaction($commit){
  26      if ($commit){
  27          serendipity_db_query('commit transaction');
  28      }else{
  29          serendipity_db_query('rollback transaction');
  30      }
  31  }
  32  
  33  /**
  34   * Connect to the configured Database
  35   *
  36   * @access public
  37   * @return  ressource   connection handle
  38   */
  39  function serendipity_db_connect()
  40  {
  41      global $serendipity;
  42  
  43      if (isset($serendipity['dbConn'])) {
  44          return $serendipity['dbConn'];
  45      }
  46  
  47      if (isset($serendipity['dbPersistent']) && $serendipity['dbPersistent']) {
  48          $function = 'sqlite_popen';
  49      } else {
  50          $function = 'sqlite_open';
  51      }
  52  
  53  
  54      $serendipity['dbConn'] = $function($serendipity['serendipityPath'] . $serendipity['dbName'] . '.db');
  55  
  56      return $serendipity['dbConn'];
  57  }
  58  
  59  function serendipity_db_reconnect() {
  60  }
  61  
  62  /**
  63   * Returns a escaped string, so that it can be safely included in a SQL string encapsulated within quotes, without allowing SQL injection.
  64   *
  65   * @access  public
  66   * @param   string   input string
  67   * @return  string   output string
  68   */
  69  function serendipity_db_escape_string($string)
  70  {
  71      static $search  = array("\x00", '%',   "'",   '\"');
  72      static $replace = array('%00',  '%25', "''", '\\\"');
  73  
  74      return str_replace($search, $replace, $string);
  75  }
  76  
  77  /**
  78   * Returns the number of affected rows of a SQL query
  79   *
  80   * @access public
  81   * @return int      Number of affected rows
  82   */
  83  function serendipity_db_affected_rows()
  84  {
  85      global $serendipity;
  86  
  87      return sqlite_changes($serendipity['dbConn']);
  88  }
  89  
  90  /**
  91   * Returns the number of updated rows in a SQL query
  92   *
  93   * @access public
  94   * @return int  Number of updated rows
  95   */
  96  function serendipity_db_updated_rows()
  97  {
  98      global $serendipity;
  99      // It is unknown whether sqllite returns rows MATCHED or rows UPDATED
 100      return sqlite_changes($serendipity['dbConn']);
 101  }
 102  
 103  /**
 104   * Returns the number of matched rows in a SQL query
 105   *
 106   * @access public
 107   * @return int  Number of matched rows
 108   */
 109  function serendipity_db_matched_rows()
 110  {
 111      global $serendipity;
 112      // It is unknown whether sqllite returns rows MATCHED or rows UPDATED
 113      return sqlite_changes($serendipity['dbConn']);
 114  }
 115  
 116  /**
 117   * Returns the latest INSERT_ID of an SQL INSERT INTO command, for auto-increment columns
 118   *
 119   * @access public
 120   * @return int      Value of the auto-increment column
 121   */
 122  function serendipity_db_insert_id()
 123  {
 124      global $serendipity;
 125  
 126      return sqlite_last_insert_rowid($serendipity['dbConn']);
 127  }
 128  
 129  /**
 130   * Parse result arrays into expected format for further operations
 131   *
 132   * SQLite does not support to return "e.entryid" within a $row['entryid'] return.
 133   * So this function manually iteratse through all result rows and rewrites 'X.yyyy' to 'yyyy'.
 134   * Yeah. This sucks. Don't tell me!
 135   *
 136   * @access private
 137   * @param  ressource    The row ressource handle
 138   * @param  int          Bitmask to tell whether to fetch numerical/associative arrays
 139   * @return array        Propper array containing the ressource results
 140   */
 141  function serendipity_db_sqlite_fetch_array($res, $type = SQLITE_BOTH)
 142  {
 143      static $search  = array('%00',  '%25');
 144      static $replace = array("\x00", '%');
 145  
 146      $row = sqlite_fetch_array($res, $type);
 147      if (!is_array($row)) {
 148          return $row;
 149      }
 150  
 151      /* strip any slashes, correct fieldname */
 152      foreach ($row as $i => $v) {
 153          // TODO: If a query of the format 'SELECT a.id, b.text FROM table' is used,
 154          //       the sqlite extension will give us key indizes 'a.id' and 'b.text'
 155          //       instead of just 'id' and 'text' like in mysql/postgresql extension.
 156          //       To fix that, we use a preg-regex; but that is quite performance costy.
 157          //       Either we always need to use 'SELECT a.id AS id, b.text AS text' in query,
 158          //       or the sqlite extension may get fixed. :-)
 159          $row[preg_replace('@^.+\.(.*)@', '\1', $i)] = str_replace($search, $replace, $v);
 160      }
 161  
 162      return $row;
 163  }
 164  
 165  /**
 166   * Assemble and return SQL condition for a "IN (...)" clause
 167   *
 168   * @access public
 169   * @param  string   table column name
 170   * @param  array    referenced array of values to search for in the "IN (...)" clause
 171   * @param  string   condition of how to associate the different input values of the $search_ids parameter
 172   * @return string   resulting SQL string
 173   */
 174  function serendipity_db_in_sql($col, &$search_ids, $type = ' OR ') {
 175      $sql = array();
 176      if (!is_array($search_ids)) {
 177          return false;
 178      }
 179  
 180      foreach($search_ids AS $id) {
 181          $sql[] = $col . ' = ' . $id;
 182      }
 183  
 184      $cond = '(' . implode($type, $sql) . ')';
 185      return $cond;
 186  }
 187  
 188  /**
 189   * Perform a DB Layer SQL query.
 190   *
 191   * This function returns values dependin on the input parameters and the result of the query.
 192   * It can return:
 193   *   false if there was an error,
 194   *   true if the query succeeded but did not generate any rows
 195   *   array of field values if it returned a single row and $single is true
 196   *   array of array of field values if it returned row(s) [stacked array]
 197   *
 198   * @access public
 199   * @param   string      SQL query to execute
 200   * @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!
 201   * @param   string      Result type of the array indexing. Can be one of "assoc" (associative), "num" (numerical), "both" (numerical and associative, default)
 202   * @param   boolean     If true, errors will be reported. If false, errors will be ignored.
 203   * @param   string      A possible array key name, so that you can control the multi-dimensional mapping of an array by the key column
 204   * @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.
 205   * @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)
 206   * @return  mixed       Returns the result of the SQL query, depending on the input parameters
 207   */
 208  function &serendipity_db_query($sql, $single = false, $result_type = "both", $reportErr = true, $assocKey = false, $assocVal = false, $expectError = false)
 209  {
 210      global $serendipity;
 211      static $type_map = array(
 212                           'assoc' => SQLITE_ASSOC,
 213                           'num'   => SQLITE_NUM,
 214                           'both'  => SQLITE_BOTH,
 215                           'true'  => true,
 216                           'false' => false
 217      );
 218  
 219      static $debug = false;
 220  
 221      if ($debug) {
 222          // Open file and write directly. In case of crashes, the pointer needs to be killed.
 223          $fp = @fopen('sqlite.log', 'a');
 224          fwrite($fp, '[' . date('d.m.Y H:i') . '] SQLITE QUERY: ' . $sql . "\n\n");
 225          fclose($fp);
 226      }
 227  
 228      if ($reportErr && !$expectError) {
 229          $res = sqlite_query($sql, $serendipity['dbConn']);
 230      } else {
 231          $res = @sqlite_query($sql, $serendipity['dbConn']);
 232      }
 233  
 234      if (!$res) {
 235          if (!$expectError && !$serendipity['production']) {
 236              var_dump($res);
 237              var_dump($sql);
 238              $msg = "problem with query";
 239              return $msg;
 240          }
 241          if ($debug) {
 242              $fp = @fopen('sqlite.log', 'a');
 243              fwrite($fp, '[' . date('d.m.Y H:i') . '] [ERROR] ' . "\n\n");
 244              fclose($fp);
 245          }
 246  
 247          return $type_map['false'];
 248      }
 249  
 250      if ($res === true) {
 251          return $type_map['true'];
 252      }
 253  
 254      if (sqlite_num_rows($res) == 0) {
 255          if ($single) {
 256              return $type_map['false'];
 257          }
 258          return $type_map['true'];
 259      } else {
 260          $rows = array();
 261  
 262          while (($row = serendipity_db_sqlite_fetch_array($res, $type_map[$result_type]))) {
 263              if (!empty($assocKey)) {
 264                  // You can fetch a key-associated array via the two function parameters assocKey and assocVal
 265                  if (empty($assocVal)) {
 266                      $rows[$row[$assocKey]] = $row;
 267                  } else {
 268                      $rows[$row[$assocKey]] = $row[$assocVal];
 269                  }
 270              } else {
 271                  $rows[] = $row;
 272              }
 273          }
 274  
 275          if ($debug) {
 276              $fp = @fopen('sqlite.log', 'a');
 277              fwrite($fp, '[' . date('d.m.Y H:i') . '] SQLITE RESULT: ' . print_r($rows, true). "\n\n");
 278              fclose($fp);
 279          }
 280  
 281          if ($single && count($rows) == 1) {
 282              return $rows[0];
 283          }
 284  
 285          return $rows;
 286      }
 287  }
 288  
 289  /**
 290   * Try to connect to the configured Database (during installation)
 291   *
 292   * @access public
 293   * @param  array     input configuration array, holding the connection info
 294   * @param  array     referenced array which holds the errors that might be encountered
 295   * @return boolean   return true on success, false on error
 296   */
 297  function serendipity_db_probe($hash, &$errs)
 298  {
 299      global $serendipity;
 300  
 301      $dbName = (isset($hash['sqlitedbName']) ? $hash['sqlitedbName'] : $hash['dbName']);
 302  
 303      if (!function_exists('sqlite_open')) {
 304          $errs[] = 'SQLite extension not installed. Run "pear install sqlite" on your webserver or contact your systems administrator regarding this problem.';
 305          return false;
 306      }
 307  
 308      if (defined('S9Y_DATA_PATH')) {
 309          // Shared installations!
 310          $dbfile = S9Y_DATA_PATH . $dbName . '.db';
 311      } else {
 312          $dbfile = $serendipity['serendipityPath'] . $dbName . '.db';
 313      }
 314  
 315      $serendipity['dbConn'] = sqlite_open($dbfile);
 316  
 317      if ($serendipity['dbConn']) {
 318          return true;
 319      }
 320  
 321      $errs[] = "Unable to open \"$dbfile\" - check permissions (directory needs to be writeable for webserver)!";
 322      return false;
 323  }
 324  
 325  /**
 326   * Prepares a Serendipty query input to fully valid SQL. Replaces certain "template" variables.
 327   *
 328   * @access public
 329   * @param  string   SQL query with template variables to convert
 330   * @return ressource    SQL ressource handle of the executed query
 331   */
 332  function serendipity_db_schema_import($query)
 333  {
 334      static $search  = array('{AUTOINCREMENT}', '{PRIMARY}', '{UNSIGNED}', '{FULLTEXT}', '{BOOLEAN}', '{UTF_8}');
 335      static $replace = array('INTEGER', 'PRIMARY KEY', '', '', 'BOOLEAN NOT NULL', '');
 336  
 337      if (stristr($query, '{FULLTEXT_MYSQL}')) {
 338          return true;
 339      }
 340  
 341      $query = trim(str_replace($search, $replace, $query));
 342      if ($query{0} == '@') {
 343          // Errors are expected to happen (like duplicate index creation)
 344          return serendipity_db_query(substr($query, 1), false, 'both', false, false, false, true);
 345      } else {
 346          return serendipity_db_query($query);
 347      }
 348  }
 349  
 350  /**
 351   * Returns the option to a LIMIT SQL statement, because it varies accross DB systems
 352   *
 353   * @access public
 354   * @param  int      Number of the first row to return data from
 355   * @param  int      Number of rows to return
 356   * @return string   SQL string to pass to a LIMIT statement
 357   */
 358  function serendipity_db_limit($start, $offset) {
 359      return $start . ', ' . $offset;
 360  }
 361  
 362  /**
 363   * Return a LIMIT SQL option to the DB Layer as a full LIMIT statement
 364   *
 365   * @access public
 366   * @param   SQL string of a LIMIT option
 367   * @return  SQL string containing a full LIMIT statement
 368   */
 369  function serendipity_db_limit_sql($limitstring) {
 370      return ' LIMIT ' . $limitstring;
 371  }
 372  
 373  /**
 374   * Returns the SQL code used for concatenating strings
 375   *
 376   * @access public
 377   * @param  string   Input string/column to concatenate
 378   * @return string   SQL parameter
 379   */
 380  function serendipity_db_concat($string) {
 381      return 'concat(' . $string . ')';
 382  }
 383  
 384  /* vim: set sts=4 ts=4 expandtab : */