[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                  try
 142                  {
 143                      $this->query_result = @$this->dbo->query($query);
 144                  }
 145                  catch (\Error $e)
 146                  {
 147                      // Do nothing as SQL driver will report the error
 148                  }
 149  
 150                  if ($this->query_result === false)
 151                  {
 152                      // Try to recover a lost database connection
 153                      if ($this->dbo && !@$this->dbo->lastErrorMsg())
 154                      {
 155                          if ($this->sql_connect($this->server, $this->user, '', $this->dbname))
 156                          {
 157                              $this->query_result = @$this->dbo->query($query);
 158                          }
 159                      }
 160  
 161                      if ($this->query_result === false)
 162                      {
 163                          $this->sql_error($query);
 164                      }
 165                  }
 166  
 167                  if ($this->debug_sql_explain)
 168                  {
 169                      $this->sql_report('stop', $query);
 170                  }
 171                  else if ($this->debug_load_time)
 172                  {
 173                      $this->sql_time += microtime(true) - $this->curtime;
 174                  }
 175  
 176                  if (!$this->query_result)
 177                  {
 178                      return false;
 179                  }
 180  
 181                  if ($cache && $cache_ttl)
 182                  {
 183                      $this->query_result = $cache->sql_save($this, $query, $this->query_result, $cache_ttl);
 184                  }
 185              }
 186              else if ($this->debug_sql_explain)
 187              {
 188                  $this->sql_report('fromcache', $query);
 189              }
 190          }
 191          else
 192          {
 193              return false;
 194          }
 195  
 196          return $this->query_result;
 197      }
 198  
 199      /**
 200      * Build LIMIT query
 201      *
 202      * @param    string    $query        The SQL query to execute
 203      * @param    int        $total        The number of rows to select
 204      * @param    int        $offset
 205      * @param    int        $cache_ttl    Either 0 to avoid caching or
 206      *                the time in seconds which the result shall be kept in cache
 207      * @return    mixed    Buffered, seekable result handle, false on error
 208      */
 209  	protected function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
 210      {
 211          $this->query_result = false;
 212  
 213          // if $total is set to 0 we do not want to limit the number of rows
 214          if ($total == 0)
 215          {
 216              $total = -1;
 217          }
 218  
 219          $query .= "\n LIMIT " . ((!empty($offset)) ? $offset . ', ' . $total : $total);
 220  
 221          return $this->sql_query($query, $cache_ttl);
 222      }
 223  
 224      /**
 225      * {@inheritDoc}
 226      */
 227  	public function sql_affectedrows()
 228      {
 229          return ($this->db_connect_id) ? $this->dbo->changes() : false;
 230      }
 231  
 232      /**
 233      * {@inheritDoc}
 234      */
 235  	public function sql_fetchrow($query_id = false)
 236      {
 237          global $cache;
 238  
 239          if ($query_id === false)
 240          {
 241              /** @var \SQLite3Result $query_id */
 242              $query_id = $this->query_result;
 243          }
 244  
 245          $safe_query_id = $this->clean_query_id($query_id);
 246          if ($cache && $cache->sql_exists($safe_query_id))
 247          {
 248              return $cache->sql_fetchrow($safe_query_id);
 249          }
 250  
 251          return is_object($query_id) ? @$query_id->fetchArray(SQLITE3_ASSOC) : false;
 252      }
 253  
 254      /**
 255       * {@inheritdoc}
 256       */
 257  	public function sql_last_inserted_id()
 258      {
 259          return ($this->db_connect_id) ? $this->dbo->lastInsertRowID() : false;
 260      }
 261  
 262      /**
 263      * {@inheritDoc}
 264      */
 265  	public function sql_freeresult($query_id = false)
 266      {
 267          global $cache;
 268  
 269          if ($query_id === false)
 270          {
 271              $query_id = $this->query_result;
 272          }
 273  
 274          $safe_query_id = $this->clean_query_id($query_id);
 275          if ($cache && $cache->sql_exists($safe_query_id))
 276          {
 277              return $cache->sql_freeresult($safe_query_id);
 278          }
 279  
 280          if ($query_id)
 281          {
 282              return @$query_id->finalize();
 283          }
 284      }
 285  
 286      /**
 287      * {@inheritDoc}
 288      */
 289  	public function sql_escape($msg)
 290      {
 291          return \SQLite3::escapeString($msg);
 292      }
 293  
 294      /**
 295      * {@inheritDoc}
 296      *
 297      * For SQLite an underscore is an unknown character.
 298      */
 299  	public function sql_like_expression($expression)
 300      {
 301          // Unlike LIKE, GLOB is unfortunately case sensitive.
 302          // We only catch * and ? here, not the character map possible on file globbing.
 303          $expression = str_replace(array(chr(0) . '_', chr(0) . '%'), array(chr(0) . '?', chr(0) . '*'), $expression);
 304  
 305          $expression = str_replace(array('?', '*'), array("\?", "\*"), $expression);
 306          $expression = str_replace(array(chr(0) . "\?", chr(0) . "\*"), array('?', '*'), $expression);
 307  
 308          return 'GLOB \'' . $this->sql_escape($expression) . '\'';
 309      }
 310  
 311      /**
 312      * {@inheritDoc}
 313      *
 314      * For SQLite an underscore is an unknown character.
 315      */
 316  	public function sql_not_like_expression($expression)
 317      {
 318          // Unlike NOT LIKE, NOT GLOB is unfortunately case sensitive
 319          // We only catch * and ? here, not the character map possible on file globbing.
 320          $expression = str_replace(array(chr(0) . '_', chr(0) . '%'), array(chr(0) . '?', chr(0) . '*'), $expression);
 321  
 322          $expression = str_replace(array('?', '*'), array("\?", "\*"), $expression);
 323          $expression = str_replace(array(chr(0) . "\?", chr(0) . "\*"), array('?', '*'), $expression);
 324  
 325          return 'NOT GLOB \'' . $this->sql_escape($expression) . '\'';
 326      }
 327  
 328      /**
 329      * return sql error array
 330      *
 331      * @return array
 332      */
 333  	protected function _sql_error()
 334      {
 335          if (class_exists('SQLite3', false) && isset($this->dbo))
 336          {
 337              $error = array(
 338                  'message'    => $this->dbo->lastErrorMsg(),
 339                  'code'        => $this->dbo->lastErrorCode(),
 340              );
 341          }
 342          else
 343          {
 344              $error = array(
 345                  'message'    => $this->connect_error,
 346                  'code'        => '',
 347              );
 348          }
 349  
 350          return $error;
 351      }
 352  
 353      /**
 354      * Build db-specific query data
 355      *
 356      * @param    string    $stage        Available stages: FROM, WHERE
 357      * @param    mixed    $data        A string containing the CROSS JOIN query or an array of WHERE clauses
 358      *
 359      * @return    string    The db-specific query fragment
 360      */
 361  	protected function _sql_custom_build($stage, $data)
 362      {
 363          return $data;
 364      }
 365  
 366      /**
 367      * Close sql connection
 368      *
 369      * @return    bool        False if failure
 370      */
 371  	protected function _sql_close()
 372      {
 373          return $this->dbo->close();
 374      }
 375  
 376      /**
 377      * Build db-specific report
 378      *
 379      * @param    string    $mode        Available modes: display, start, stop,
 380      *                                add_select_row, fromcache, record_fromcache
 381      * @param    string    $query        The Query that should be explained
 382      * @return    mixed        Either a full HTML page, boolean or null
 383      */
 384  	protected function _sql_report($mode, $query = '')
 385      {
 386          switch ($mode)
 387          {
 388              case 'start':
 389  
 390                  $explain_query = $query;
 391                  if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 392                  {
 393                      $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 394                  }
 395                  else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 396                  {
 397                      $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 398                  }
 399  
 400                  if (preg_match('/^SELECT/', $explain_query))
 401                  {
 402                      $html_table = false;
 403  
 404                      if ($result = @$this->dbo->query("EXPLAIN QUERY PLAN $explain_query"))
 405                      {
 406                          while ($row = $result->fetchArray(SQLITE3_ASSOC))
 407                          {
 408                              $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 409                          }
 410                      }
 411  
 412                      if ($html_table)
 413                      {
 414                          $this->html_hold .= '</table>';
 415                      }
 416                  }
 417  
 418              break;
 419  
 420              case 'fromcache':
 421                  $endtime = explode(' ', microtime());
 422                  $endtime = $endtime[0] + $endtime[1];
 423  
 424                  $result = $this->dbo->query($query);
 425                  if ($result)
 426                  {
 427                          while ($void = $result->fetchArray(SQLITE3_ASSOC))
 428                          {
 429                              // Take the time spent on parsing rows into account
 430                          }
 431                  }
 432  
 433                  $splittime = explode(' ', microtime());
 434                  $splittime = $splittime[0] + $splittime[1];
 435  
 436                  $this->sql_report('record_fromcache', $query, $endtime, $splittime);
 437  
 438              break;
 439          }
 440      }
 441  
 442      /**
 443      * {@inheritDoc}
 444      */
 445  	function sql_quote($msg)
 446      {
 447          return '\'' . $msg . '\'';
 448      }
 449  }