[Summary view] [Print] [Text view]

   1  <?php
   2  /**
   3  *
   4  * This file is part of the phpBB Forum Software package.
   5  *
   6  * @copyright (c) phpBB Limited <https://www.phpbb.com>
   7  * @license GNU General Public License, version 2 (GPL-2.0)
   8  *
   9  * For full copyright and license information, please see
  10  * the docs/CREDITS.txt file.
  11  *
  12  */
  13  
  14  namespace phpbb\db\driver;
  15  
  16  /**
  17  * SQLite3 Database Abstraction Layer
  18  * Minimum Requirement: 3.6.15+
  19  */
  20  class sqlite3 extends \phpbb\db\driver\driver
  21  {
  22      /**
  23      * @var    string        Stores errors during connection setup in case the driver is not available
  24      */
  25      protected $connect_error = '';
  26  
  27      /**
  28      * @var    \SQLite3    The SQLite3 database object to operate against
  29      */
  30      protected $dbo = null;
  31  
  32      /**
  33      * {@inheritDoc}
  34      */
  35  	public function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false, $new_link = false)
  36      {
  37          $this->persistency = false;
  38          $this->user = $sqluser;
  39          $this->server = $sqlserver . (($port) ? ':' . $port : '');
  40          $this->dbname = $database;
  41  
  42          if (!class_exists('SQLite3', false))
  43          {
  44              $this->connect_error = 'SQLite3 not found, is the extension installed?';
  45              return $this->sql_error('');
  46          }
  47  
  48          try
  49          {
  50              $this->dbo = new \SQLite3($this->server, SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE);
  51              $this->dbo->busyTimeout(60000);
  52              $this->db_connect_id = true;
  53          }
  54          catch (\Exception $e)
  55          {
  56              $this->connect_error = $e->getMessage();
  57              return array('message' => $this->connect_error);
  58          }
  59  
  60          return true;
  61      }
  62  
  63      /**
  64      * {@inheritDoc}
  65      */
  66  	public function sql_server_info($raw = false, $use_cache = true)
  67      {
  68          global $cache;
  69  
  70          if (!$use_cache || empty($cache) || ($this->sql_server_version = $cache->get('sqlite_version')) === false)
  71          {
  72              $version = \SQLite3::version();
  73  
  74              $this->sql_server_version = $version['versionString'];
  75  
  76              if (!empty($cache) && $use_cache)
  77              {
  78                  $cache->put('sqlite_version', $this->sql_server_version);
  79              }
  80          }
  81  
  82          return ($raw) ? $this->sql_server_version : 'SQLite ' . $this->sql_server_version;
  83      }
  84  
  85      /**
  86      * SQL Transaction
  87      *
  88      * @param    string    $status        Should be one of the following strings:
  89      *                                begin, commit, rollback
  90      * @return    bool    Success/failure of the transaction query
  91      */
  92  	protected function _sql_transaction($status = 'begin')
  93      {
  94          switch ($status)
  95          {
  96              case 'begin':
  97                  return $this->dbo->exec('BEGIN IMMEDIATE');
  98              break;
  99  
 100              case 'commit':
 101                  return $this->dbo->exec('COMMIT');
 102              break;
 103  
 104              case 'rollback':
 105                  return @$this->dbo->exec('ROLLBACK');
 106              break;
 107          }
 108  
 109          return true;
 110      }
 111  
 112      /**
 113      * {@inheritDoc}
 114      */
 115  	public function sql_query($query = '', $cache_ttl = 0)
 116      {
 117          if ($query != '')
 118          {
 119              global $cache;
 120  
 121              if ($this->debug_sql_explain)
 122              {
 123                  $this->sql_report('start', $query);
 124              }
 125              else if ($this->debug_load_time)
 126              {
 127                  $this->curtime = microtime(true);
 128              }
 129  
 130              $this->last_query_text = $query;
 131              $this->query_result = ($cache && $cache_ttl) ? $cache->sql_load($query) : false;
 132              $this->sql_add_num_queries($this->query_result);
 133  
 134              if ($this->query_result === false)
 135              {
 136                  if ($this->transaction === true && strpos($query, 'INSERT') === 0)
 137                  {
 138                      $query = preg_replace('/^INSERT INTO/', 'INSERT OR ROLLBACK INTO', $query);
 139                  }
 140  
 141                  if (($this->query_result = @$this->dbo->query($query)) === false)
 142                  {
 143                      // Try to recover a lost database connection
 144                      if ($this->dbo && !@$this->dbo->lastErrorMsg())
 145                      {
 146                          if ($this->sql_connect($this->server, $this->user, '', $this->dbname))
 147                          {
 148                              $this->query_result = @$this->dbo->query($query);
 149                          }
 150                      }
 151  
 152                      if ($this->query_result === false)
 153                      {
 154                          $this->sql_error($query);
 155                      }
 156                  }
 157  
 158                  if ($this->debug_sql_explain)
 159                  {
 160                      $this->sql_report('stop', $query);
 161                  }
 162                  else if ($this->debug_load_time)
 163                  {
 164                      $this->sql_time += microtime(true) - $this->curtime;
 165                  }
 166  
 167                  if (!$this->query_result)
 168                  {
 169                      return false;
 170                  }
 171  
 172                  if ($cache && $cache_ttl)
 173                  {
 174                      $this->query_result = $cache->sql_save($this, $query, $this->query_result, $cache_ttl);
 175                  }
 176              }
 177              else if ($this->debug_sql_explain)
 178              {
 179                  $this->sql_report('fromcache', $query);
 180              }
 181          }
 182          else
 183          {
 184              return false;
 185          }
 186  
 187          return $this->query_result;
 188      }
 189  
 190      /**
 191      * Build LIMIT query
 192      *
 193      * @param    string    $query        The SQL query to execute
 194      * @param    int        $total        The number of rows to select
 195      * @param    int        $offset
 196      * @param    int        $cache_ttl    Either 0 to avoid caching or
 197      *                the time in seconds which the result shall be kept in cache
 198      * @return    mixed    Buffered, seekable result handle, false on error
 199      */
 200  	protected function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
 201      {
 202          $this->query_result = false;
 203  
 204          // if $total is set to 0 we do not want to limit the number of rows
 205          if ($total == 0)
 206          {
 207              $total = -1;
 208          }
 209  
 210          $query .= "\n LIMIT " . ((!empty($offset)) ? $offset . ', ' . $total : $total);
 211  
 212          return $this->sql_query($query, $cache_ttl);
 213      }
 214  
 215      /**
 216      * {@inheritDoc}
 217      */
 218  	public function sql_affectedrows()
 219      {
 220          return ($this->db_connect_id) ? $this->dbo->changes() : false;
 221      }
 222  
 223      /**
 224      * {@inheritDoc}
 225      */
 226  	public function sql_fetchrow($query_id = false)
 227      {
 228          global $cache;
 229  
 230          if ($query_id === false)
 231          {
 232              /** @var \SQLite3Result $query_id */
 233              $query_id = $this->query_result;
 234          }
 235  
 236          $safe_query_id = $this->clean_query_id($query_id);
 237          if ($cache && $cache->sql_exists($safe_query_id))
 238          {
 239              return $cache->sql_fetchrow($safe_query_id);
 240          }
 241  
 242          return is_object($query_id) ? @$query_id->fetchArray(SQLITE3_ASSOC) : false;
 243      }
 244  
 245      /**
 246       * {@inheritdoc}
 247       */
 248  	public function sql_last_inserted_id()
 249      {
 250          return ($this->db_connect_id) ? $this->dbo->lastInsertRowID() : false;
 251      }
 252  
 253      /**
 254      * {@inheritDoc}
 255      */
 256  	public function sql_freeresult($query_id = false)
 257      {
 258          global $cache;
 259  
 260          if ($query_id === false)
 261          {
 262              $query_id = $this->query_result;
 263          }
 264  
 265          $safe_query_id = $this->clean_query_id($query_id);
 266          if ($cache && $cache->sql_exists($safe_query_id))
 267          {
 268              return $cache->sql_freeresult($safe_query_id);
 269          }
 270  
 271          if ($query_id)
 272          {
 273              return @$query_id->finalize();
 274          }
 275      }
 276  
 277      /**
 278      * {@inheritDoc}
 279      */
 280  	public function sql_escape($msg)
 281      {
 282          return \SQLite3::escapeString($msg);
 283      }
 284  
 285      /**
 286      * {@inheritDoc}
 287      *
 288      * For SQLite an underscore is an unknown character.
 289      */
 290  	public function sql_like_expression($expression)
 291      {
 292          // Unlike LIKE, GLOB is unfortunately case sensitive.
 293          // We only catch * and ? here, not the character map possible on file globbing.
 294          $expression = str_replace(array(chr(0) . '_', chr(0) . '%'), array(chr(0) . '?', chr(0) . '*'), $expression);
 295  
 296          $expression = str_replace(array('?', '*'), array("\?", "\*"), $expression);
 297          $expression = str_replace(array(chr(0) . "\?", chr(0) . "\*"), array('?', '*'), $expression);
 298  
 299          return 'GLOB \'' . $this->sql_escape($expression) . '\'';
 300      }
 301  
 302      /**
 303      * {@inheritDoc}
 304      *
 305      * For SQLite an underscore is an unknown character.
 306      */
 307  	public function sql_not_like_expression($expression)
 308      {
 309          // Unlike NOT LIKE, NOT GLOB is unfortunately case sensitive
 310          // We only catch * and ? here, not the character map possible on file globbing.
 311          $expression = str_replace(array(chr(0) . '_', chr(0) . '%'), array(chr(0) . '?', chr(0) . '*'), $expression);
 312  
 313          $expression = str_replace(array('?', '*'), array("\?", "\*"), $expression);
 314          $expression = str_replace(array(chr(0) . "\?", chr(0) . "\*"), array('?', '*'), $expression);
 315  
 316          return 'NOT GLOB \'' . $this->sql_escape($expression) . '\'';
 317      }
 318  
 319      /**
 320      * return sql error array
 321      *
 322      * @return array
 323      */
 324  	protected function _sql_error()
 325      {
 326          if (class_exists('SQLite3', false) && isset($this->dbo))
 327          {
 328              $error = array(
 329                  'message'    => $this->dbo->lastErrorMsg(),
 330                  'code'        => $this->dbo->lastErrorCode(),
 331              );
 332          }
 333          else
 334          {
 335              $error = array(
 336                  'message'    => $this->connect_error,
 337                  'code'        => '',
 338              );
 339          }
 340  
 341          return $error;
 342      }
 343  
 344      /**
 345      * Build db-specific query data
 346      *
 347      * @param    string    $stage        Available stages: FROM, WHERE
 348      * @param    mixed    $data        A string containing the CROSS JOIN query or an array of WHERE clauses
 349      *
 350      * @return    string    The db-specific query fragment
 351      */
 352  	protected function _sql_custom_build($stage, $data)
 353      {
 354          return $data;
 355      }
 356  
 357      /**
 358      * Close sql connection
 359      *
 360      * @return    bool        False if failure
 361      */
 362  	protected function _sql_close()
 363      {
 364          return $this->dbo->close();
 365      }
 366  
 367      /**
 368      * Build db-specific report
 369      *
 370      * @param    string    $mode        Available modes: display, start, stop,
 371      *                                add_select_row, fromcache, record_fromcache
 372      * @param    string    $query        The Query that should be explained
 373      * @return    mixed        Either a full HTML page, boolean or null
 374      */
 375  	protected function _sql_report($mode, $query = '')
 376      {
 377          switch ($mode)
 378          {
 379              case 'start':
 380  
 381                  $explain_query = $query;
 382                  if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 383                  {
 384                      $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 385                  }
 386                  else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 387                  {
 388                      $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 389                  }
 390  
 391                  if (preg_match('/^SELECT/', $explain_query))
 392                  {
 393                      $html_table = false;
 394  
 395                      if ($result = @$this->dbo->query("EXPLAIN QUERY PLAN $explain_query"))
 396                      {
 397                          while ($row = $result->fetchArray(SQLITE3_ASSOC))
 398                          {
 399                              $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 400                          }
 401                      }
 402  
 403                      if ($html_table)
 404                      {
 405                          $this->html_hold .= '</table>';
 406                      }
 407                  }
 408  
 409              break;
 410  
 411              case 'fromcache':
 412                  $endtime = explode(' ', microtime());
 413                  $endtime = $endtime[0] + $endtime[1];
 414  
 415                  $result = $this->dbo->query($query);
 416                  if ($result)
 417                  {
 418                          while ($void = $result->fetchArray(SQLITE3_ASSOC))
 419                          {
 420                              // Take the time spent on parsing rows into account
 421                          }
 422                  }
 423  
 424                  $splittime = explode(' ', microtime());
 425                  $splittime = $splittime[0] + $splittime[1];
 426  
 427                  $this->sql_report('record_fromcache', $query, $endtime, $splittime);
 428  
 429              break;
 430          }
 431      }
 432  
 433      /**
 434      * {@inheritDoc}
 435      */
 436  	function sql_quote($msg)
 437      {
 438          return '\'' . $msg . '\'';
 439      }
 440  }