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