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