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