MediaWiki  master
DatabaseSqlite.php
Go to the documentation of this file.
1 <?php
24 namespace Wikimedia\Rdbms;
25 
26 use NullLockManager;
27 use PDO;
28 use PDOException;
29 use Exception;
30 use LockManager;
31 use FSLockManager;
33 use stdClass;
34 
38 class DatabaseSqlite extends Database {
40  protected $dbDir;
42  protected $dbPath;
44  protected $trxMode;
45 
48 
50  protected $conn;
51 
53  protected $lockMgr;
54 
56  private $sessionAttachedDbs = [];
57 
59  private static $VALID_TRX_MODES = [ '', 'DEFERRED', 'IMMEDIATE', 'EXCLUSIVE' ];
60 
68  public function __construct( array $params ) {
69  if ( isset( $params['dbFilePath'] ) ) {
70  $this->dbPath = $params['dbFilePath'];
71  if ( !strlen( $params['dbname'] ) ) {
72  $params['dbname'] = self::generateDatabaseName( $this->dbPath );
73  }
74  } elseif ( isset( $params['dbDirectory'] ) ) {
75  $this->dbDir = $params['dbDirectory'];
76  }
77 
78  parent::__construct( $params );
79 
80  $this->trxMode = strtoupper( $params['trxMode'] ?? '' );
81 
82  $lockDirectory = $this->getLockFileDirectory();
83  if ( $lockDirectory !== null ) {
84  $this->lockMgr = new FSLockManager( [
85  'domain' => $this->getDomainID(),
86  'lockDirectory' => $lockDirectory
87  ] );
88  } else {
89  $this->lockMgr = new NullLockManager( [ 'domain' => $this->getDomainID() ] );
90  }
91  }
92 
93  protected static function getAttributes() {
94  return [
95  self::ATTR_DB_IS_FILE => true,
96  self::ATTR_DB_LEVEL_LOCKING => true
97  ];
98  }
99 
109  public static function newStandaloneInstance( $filename, array $p = [] ) {
110  $p['dbFilePath'] = $filename;
111  $p['schema'] = null;
112  $p['tablePrefix'] = '';
114  $db = Database::factory( 'sqlite', $p );
115 
116  return $db;
117  }
118 
122  public function getType() {
123  return 'sqlite';
124  }
125 
126  protected function open( $server, $user, $pass, $dbName, $schema, $tablePrefix ) {
127  $this->close();
128 
129  // Note that for SQLite, $server, $user, and $pass are ignored
130 
131  if ( $schema !== null ) {
132  throw $this->newExceptionAfterConnectError( "Got schema '$schema'; not supported." );
133  }
134 
135  if ( $this->dbPath !== null ) {
137  } elseif ( $this->dbDir !== null ) {
138  $path = self::generateFileName( $this->dbDir, $dbName );
139  } else {
140  throw $this->newExceptionAfterConnectError( "DB path or directory required" );
141  }
142 
143  // Check if the database file already exists but is non-readable
144  if (
145  !self::isProcessMemoryPath( $path ) &&
146  file_exists( $path ) &&
147  !is_readable( $path )
148  ) {
149  throw $this->newExceptionAfterConnectError( 'SQLite database file is not readable' );
150  } elseif ( !in_array( $this->trxMode, self::$VALID_TRX_MODES, true ) ) {
151  throw $this->newExceptionAfterConnectError( "Got mode '{$this->trxMode}' for BEGIN" );
152  }
153 
154  $this->server = 'localhost';
155 
156  $attributes = [];
157  if ( $this->getFlag( self::DBO_PERSISTENT ) ) {
158  // Persistent connections can avoid some schema index reading overhead.
159  // On the other hand, they can cause horrible contention with DBO_TRX.
160  if ( $this->getFlag( self::DBO_TRX ) || $this->getFlag( self::DBO_DEFAULT ) ) {
161  $this->connLogger->warning(
162  __METHOD__ . ": ignoring DBO_PERSISTENT due to DBO_TRX or DBO_DEFAULT",
163  $this->getLogContext()
164  );
165  } else {
166  $attributes[PDO::ATTR_PERSISTENT] = true;
167  }
168  }
169 
170  try {
171  // Open the database file, creating it if it does not yet exist
172  $this->conn = new PDO( "sqlite:$path", null, null, $attributes );
173  } catch ( PDOException $e ) {
174  throw $this->newExceptionAfterConnectError( $e->getMessage() );
175  }
176 
177  $this->currentDomain = new DatabaseDomain( $dbName, null, $tablePrefix );
178 
179  try {
180  $flags = self::QUERY_IGNORE_DBO_TRX | self::QUERY_NO_RETRY;
181  // Enforce LIKE to be case sensitive, just like MySQL
182  $this->query( 'PRAGMA case_sensitive_like = 1', __METHOD__, $flags );
183  // Apply optimizations or requirements regarding fsync() usage
184  $sync = $this->connectionVariables['synchronous'] ?? null;
185  if ( in_array( $sync, [ 'EXTRA', 'FULL', 'NORMAL', 'OFF' ], true ) ) {
186  $this->query( "PRAGMA synchronous = $sync", __METHOD__, $flags );
187  }
189  } catch ( Exception $e ) {
190  throw $this->newExceptionAfterConnectError( $e->getMessage() );
191  }
192  }
193 
199  public function getDbFilePath() {
200  return $this->dbPath ?? self::generateFileName( $this->dbDir, $this->getDBname() );
201  }
202 
206  public function getLockFileDirectory() {
207  if ( $this->dbPath !== null && !self::isProcessMemoryPath( $this->dbPath ) ) {
208  return dirname( $this->dbPath ) . '/locks';
209  } elseif ( $this->dbDir !== null && !self::isProcessMemoryPath( $this->dbDir ) ) {
210  return $this->dbDir . '/locks';
211  }
212 
213  return null;
214  }
215 
220  protected function closeConnection() {
221  $this->conn = null;
222 
223  return true;
224  }
225 
233  public static function generateFileName( $dir, $dbName ) {
234  if ( $dir == '' ) {
235  throw new DBUnexpectedError( null, __CLASS__ . ": no DB directory specified" );
236  } elseif ( self::isProcessMemoryPath( $dir ) ) {
237  throw new DBUnexpectedError(
238  null,
239  __CLASS__ . ": cannot use process memory directory '$dir'"
240  );
241  } elseif ( !strlen( $dbName ) ) {
242  throw new DBUnexpectedError( null, __CLASS__ . ": no DB name specified" );
243  }
244 
245  return "$dir/$dbName.sqlite";
246  }
247 
252  private static function generateDatabaseName( $path ) {
253  if ( preg_match( '/^(:memory:$|file::memory:)/', $path ) ) {
254  // E.g. "file::memory:?cache=shared" => ":memory":
255  return ':memory:';
256  } elseif ( preg_match( '/^file::([^?]+)\?mode=memory(&|$)/', $path, $m ) ) {
257  // E.g. "file:memdb1?mode=memory" => ":memdb1:"
258  return ":{$m[1]}:";
259  } else {
260  // E.g. "/home/.../some_db.sqlite3" => "some_db"
261  return preg_replace( '/\.sqlite\d?$/', '', basename( $path ) );
262  }
263  }
264 
269  private static function isProcessMemoryPath( $path ) {
270  return preg_match( '/^(:memory:$|file:(:memory:|[^?]+\?mode=memory(&|$)))/', $path );
271  }
272 
277  static function getFulltextSearchModule() {
278  static $cachedResult = null;
279  if ( $cachedResult !== null ) {
280  return $cachedResult;
281  }
282  $cachedResult = false;
283  $table = 'dummy_search_test';
284 
285  $db = self::newStandaloneInstance( ':memory:' );
286  if ( $db->query( "CREATE VIRTUAL TABLE $table USING FTS3(dummy_field)", __METHOD__, true ) ) {
287  $cachedResult = 'FTS3';
288  }
289  $db->close();
290 
291  return $cachedResult;
292  }
293 
306  public function attachDatabase( $name, $file = false, $fname = __METHOD__ ) {
307  $file = is_string( $file ) ? $file : self::generateFileName( $this->dbDir, $name );
308  $encFile = $this->addQuotes( $file );
309 
310  return $this->query(
311  "ATTACH DATABASE $encFile AS $name",
312  $fname,
313  self::QUERY_IGNORE_DBO_TRX
314  );
315  }
316 
317  protected function isWriteQuery( $sql ) {
318  return parent::isWriteQuery( $sql ) && !preg_match( '/^(ATTACH|PRAGMA)\b/i', $sql );
319  }
320 
321  protected function isTransactableQuery( $sql ) {
322  return parent::isTransactableQuery( $sql ) && !in_array(
323  $this->getQueryVerb( $sql ),
324  [ 'ATTACH', 'PRAGMA' ],
325  true
326  );
327  }
328 
335  protected function doQuery( $sql ) {
336  $res = $this->getBindingHandle()->query( $sql );
337  if ( $res === false ) {
338  return false;
339  }
340 
341  $resource = ResultWrapper::unwrap( $res );
342  $this->lastAffectedRowCount = $resource->rowCount();
343  $res = new ResultWrapper( $this, $resource->fetchAll() );
344 
345  return $res;
346  }
347 
351  function freeResult( $res ) {
352  if ( $res instanceof ResultWrapper ) {
353  $res->free();
354  }
355  }
356 
361  function fetchObject( $res ) {
362  $resource =& ResultWrapper::unwrap( $res );
363 
364  $cur = current( $resource );
365  if ( is_array( $cur ) ) {
366  next( $resource );
367  $obj = new stdClass;
368  foreach ( $cur as $k => $v ) {
369  if ( !is_numeric( $k ) ) {
370  $obj->$k = $v;
371  }
372  }
373 
374  return $obj;
375  }
376 
377  return false;
378  }
379 
384  function fetchRow( $res ) {
385  $resource =& ResultWrapper::unwrap( $res );
386  $cur = current( $resource );
387  if ( is_array( $cur ) ) {
388  next( $resource );
389 
390  return $cur;
391  }
392 
393  return false;
394  }
395 
402  function numRows( $res ) {
403  // false does not implement Countable
404  $resource = ResultWrapper::unwrap( $res );
405 
406  return is_array( $resource ) ? count( $resource ) : 0;
407  }
408 
413  function numFields( $res ) {
414  $resource = ResultWrapper::unwrap( $res );
415  if ( is_array( $resource ) && count( $resource ) > 0 ) {
416  // The size of the result array is twice the number of fields. (T67578)
417  return count( $resource[0] ) / 2;
418  } else {
419  // If the result is empty return 0
420  return 0;
421  }
422  }
423 
429  function fieldName( $res, $n ) {
430  $resource = ResultWrapper::unwrap( $res );
431  if ( is_array( $resource ) ) {
432  $keys = array_keys( $resource[0] );
433 
434  return $keys[$n];
435  }
436 
437  return false;
438  }
439 
440  protected function doSelectDomain( DatabaseDomain $domain ) {
441  if ( $domain->getSchema() !== null ) {
442  throw new DBExpectedError(
443  $this,
444  __CLASS__ . ": domain '{$domain->getId()}' has a schema component"
445  );
446  }
447 
448  $database = $domain->getDatabase();
449  // A null database means "don't care" so leave it as is and update the table prefix
450  if ( $database === null ) {
451  $this->currentDomain = new DatabaseDomain(
452  $this->currentDomain->getDatabase(),
453  null,
454  $domain->getTablePrefix()
455  );
456 
457  return true;
458  }
459 
460  if ( $database !== $this->getDBname() ) {
461  throw new DBExpectedError(
462  $this,
463  __CLASS__ . ": cannot change database (got '$database')"
464  );
465  }
466 
467  return true;
468  }
469 
477  function tableName( $name, $format = 'quoted' ) {
478  // table names starting with sqlite_ are reserved
479  if ( strpos( $name, 'sqlite_' ) === 0 ) {
480  return $name;
481  }
482 
483  return str_replace( '"', '', parent::tableName( $name, $format ) );
484  }
485 
491  function insertId() {
492  // PDO::lastInsertId yields a string :(
493  return intval( $this->getBindingHandle()->lastInsertId() );
494  }
495 
500  function dataSeek( $res, $row ) {
501  $resource =& ResultWrapper::unwrap( $res );
502  reset( $resource );
503  if ( $row > 0 ) {
504  for ( $i = 0; $i < $row; $i++ ) {
505  next( $resource );
506  }
507  }
508  }
509 
513  function lastError() {
514  if ( !is_object( $this->conn ) ) {
515  return "Cannot return last error, no db connection";
516  }
517  $e = $this->conn->errorInfo();
518 
519  return $e[2] ?? '';
520  }
521 
525  function lastErrno() {
526  if ( !is_object( $this->conn ) ) {
527  return "Cannot return last error, no db connection";
528  } else {
529  $info = $this->conn->errorInfo();
530 
531  return $info[1];
532  }
533  }
534 
538  protected function fetchAffectedRowCount() {
540  }
541 
542  function tableExists( $table, $fname = __METHOD__ ) {
543  $tableRaw = $this->tableName( $table, 'raw' );
544  if ( isset( $this->sessionTempTables[$tableRaw] ) ) {
545  return true; // already known to exist
546  }
547 
548  $encTable = $this->addQuotes( $tableRaw );
549  $res = $this->query(
550  "SELECT 1 FROM sqlite_master WHERE type='table' AND name=$encTable",
551  __METHOD__,
552  self::QUERY_IGNORE_DBO_TRX
553  );
554 
555  return $res->numRows() ? true : false;
556  }
557 
568  function indexInfo( $table, $index, $fname = __METHOD__ ) {
569  $sql = 'PRAGMA index_info(' . $this->addQuotes( $this->indexName( $index ) ) . ')';
570  $res = $this->query( $sql, $fname, self::QUERY_IGNORE_DBO_TRX );
571  if ( !$res || $res->numRows() == 0 ) {
572  return false;
573  }
574  $info = [];
575  foreach ( $res as $row ) {
576  $info[] = $row->name;
577  }
578 
579  return $info;
580  }
581 
588  function indexUnique( $table, $index, $fname = __METHOD__ ) {
589  $row = $this->selectRow( 'sqlite_master', '*',
590  [
591  'type' => 'index',
592  'name' => $this->indexName( $index ),
593  ], $fname );
594  if ( !$row || !isset( $row->sql ) ) {
595  return null;
596  }
597 
598  // $row->sql will be of the form CREATE [UNIQUE] INDEX ...
599  $indexPos = strpos( $row->sql, 'INDEX' );
600  if ( $indexPos === false ) {
601  return null;
602  }
603  $firstPart = substr( $row->sql, 0, $indexPos );
604  $options = explode( ' ', $firstPart );
605 
606  return in_array( 'UNIQUE', $options );
607  }
608 
609  protected function makeSelectOptions( array $options ) {
610  // Remove problematic options that the base implementation converts to SQL
611  foreach ( $options as $k => $v ) {
612  if ( is_numeric( $k ) && ( $v === 'FOR UPDATE' || $v === 'LOCK IN SHARE MODE' ) ) {
613  $options[$k] = '';
614  }
615  }
616 
617  return parent::makeSelectOptions( $options );
618  }
619 
624  protected function makeUpdateOptionsArray( $options ) {
625  $options = parent::makeUpdateOptionsArray( $options );
626  $options = self::fixIgnore( $options );
627 
628  return $options;
629  }
630 
635  static function fixIgnore( $options ) {
636  # SQLite uses OR IGNORE not just IGNORE
637  foreach ( $options as $k => $v ) {
638  if ( $v == 'IGNORE' ) {
639  $options[$k] = 'OR IGNORE';
640  }
641  }
642 
643  return $options;
644  }
645 
650  function makeInsertOptions( $options ) {
651  $options = self::fixIgnore( $options );
652 
653  return parent::makeInsertOptions( $options );
654  }
655 
656  function insert( $table, $rows, $fname = __METHOD__, $options = [] ) {
657  if ( !count( $rows ) ) {
658  return true;
659  }
660 
661  # SQLite can't handle multi-row inserts, so divide up into multiple single-row inserts
662  $multi = $this->isMultiRowArray( $rows );
663  if ( $multi ) {
664  $affectedRowCount = 0;
665  try {
666  $this->startAtomic( $fname, self::ATOMIC_CANCELABLE );
667  foreach ( $rows as $row ) {
668  parent::insert( $table, $row, "$fname/multi-row", $options );
669  $affectedRowCount += $this->affectedRows();
670  }
671  $this->endAtomic( $fname );
672  } catch ( Exception $e ) {
673  $this->cancelAtomic( $fname );
674  throw $e;
675  }
676  $this->affectedRowCount = $affectedRowCount;
677  } else {
678  parent::insert( $table, $rows, "$fname/single-row", $options );
679  }
680 
681  return true;
682  }
683 
690  function replace( $table, $uniqueIndexes, $rows, $fname = __METHOD__ ) {
691  if ( !count( $rows ) ) {
692  return;
693  }
694 
695  # SQLite can't handle multi-row replaces, so divide up into multiple single-row queries
696  if ( isset( $rows[0] ) && is_array( $rows[0] ) ) {
697  $affectedRowCount = 0;
698  try {
699  $this->startAtomic( $fname, self::ATOMIC_CANCELABLE );
700  foreach ( $rows as $v ) {
701  $this->nativeReplace( $table, $v, "$fname/multi-row" );
702  $affectedRowCount += $this->affectedRows();
703  }
704  $this->endAtomic( $fname );
705  } catch ( Exception $e ) {
706  $this->cancelAtomic( $fname );
707  throw $e;
708  }
709  $this->affectedRowCount = $affectedRowCount;
710  } else {
711  $this->nativeReplace( $table, $rows, "$fname/single-row" );
712  }
713  }
714 
723  function textFieldSize( $table, $field ) {
724  return -1;
725  }
726 
731  return false;
732  }
733 
739  function unionQueries( $sqls, $all ) {
740  $glue = $all ? ' UNION ALL ' : ' UNION ';
741 
742  return implode( $glue, $sqls );
743  }
744 
748  function wasDeadlock() {
749  return $this->lastErrno() == 5; // SQLITE_BUSY
750  }
751 
755  function wasReadOnlyError() {
756  return $this->lastErrno() == 8; // SQLITE_READONLY;
757  }
758 
759  public function wasConnectionError( $errno ) {
760  return $errno == 17; // SQLITE_SCHEMA;
761  }
762 
763  protected function wasKnownStatementRollbackError() {
764  // ON CONFLICT ROLLBACK clauses make it so that SQLITE_CONSTRAINT error is
765  // ambiguous with regard to whether it implies a ROLLBACK or an ABORT happened.
766  // https://sqlite.org/lang_createtable.html#uniqueconst
767  // https://sqlite.org/lang_conflict.html
768  return false;
769  }
770 
771  public function serverIsReadOnly() {
772  $this->assertHasConnectionHandle();
773 
774  $path = $this->getDbFilePath();
775 
776  return ( !self::isProcessMemoryPath( $path ) && !is_writable( $path ) );
777  }
778 
782  public function getSoftwareLink() {
783  return "[{{int:version-db-sqlite-url}} SQLite]";
784  }
785 
789  function getServerVersion() {
790  $ver = $this->getBindingHandle()->getAttribute( PDO::ATTR_SERVER_VERSION );
791 
792  return $ver;
793  }
794 
803  function fieldInfo( $table, $field ) {
804  $tableName = $this->tableName( $table );
805  $sql = 'PRAGMA table_info(' . $this->addQuotes( $tableName ) . ')';
806  $res = $this->query( $sql, __METHOD__, self::QUERY_IGNORE_DBO_TRX );
807  foreach ( $res as $row ) {
808  if ( $row->name == $field ) {
809  return new SQLiteField( $row, $tableName );
810  }
811  }
812 
813  return false;
814  }
815 
816  protected function doBegin( $fname = '' ) {
817  if ( $this->trxMode != '' ) {
818  $this->query( "BEGIN {$this->trxMode}", $fname );
819  } else {
820  $this->query( 'BEGIN', $fname );
821  }
822  }
823 
828  function strencode( $s ) {
829  return substr( $this->addQuotes( $s ), 1, -1 );
830  }
831 
836  function encodeBlob( $b ) {
837  return new Blob( $b );
838  }
839 
844  function decodeBlob( $b ) {
845  if ( $b instanceof Blob ) {
846  $b = $b->fetch();
847  }
848 
849  return $b;
850  }
851 
856  function addQuotes( $s ) {
857  if ( $s instanceof Blob ) {
858  return "x'" . bin2hex( $s->fetch() ) . "'";
859  } elseif ( is_bool( $s ) ) {
860  return (int)$s;
861  } elseif ( strpos( (string)$s, "\0" ) !== false ) {
862  // SQLite doesn't support \0 in strings, so use the hex representation as a workaround.
863  // This is a known limitation of SQLite's mprintf function which PDO
864  // should work around, but doesn't. I have reported this to php.net as bug #63419:
865  // https://bugs.php.net/bug.php?id=63419
866  // There was already a similar report for SQLite3::escapeString, bug #62361:
867  // https://bugs.php.net/bug.php?id=62361
868  // There is an additional bug regarding sorting this data after insert
869  // on older versions of sqlite shipped with ubuntu 12.04
870  // https://phabricator.wikimedia.org/T74367
871  $this->queryLogger->debug(
872  __FUNCTION__ .
873  ': Quoting value containing null byte. ' .
874  'For consistency all binary data should have been ' .
875  'first processed with self::encodeBlob()'
876  );
877  return "x'" . bin2hex( (string)$s ) . "'";
878  } else {
879  return $this->getBindingHandle()->quote( (string)$s );
880  }
881  }
882 
883  public function buildSubstring( $input, $startPosition, $length = null ) {
884  $this->assertBuildSubstringParams( $startPosition, $length );
885  $params = [ $input, $startPosition ];
886  if ( $length !== null ) {
887  $params[] = $length;
888  }
889  return 'SUBSTR(' . implode( ',', $params ) . ')';
890  }
891 
897  public function buildStringCast( $field ) {
898  return 'CAST ( ' . $field . ' AS TEXT )';
899  }
900 
907  public function deadlockLoop( ...$args ) {
908  $function = array_shift( $args );
909 
910  return $function( ...$args );
911  }
912 
917  protected function replaceVars( $s ) {
918  $s = parent::replaceVars( $s );
919  if ( preg_match( '/^\s*(CREATE|ALTER) TABLE/i', $s ) ) {
920  // CREATE TABLE hacks to allow schema file sharing with MySQL
921 
922  // binary/varbinary column type -> blob
923  $s = preg_replace( '/\b(var)?binary(\(\d+\))/i', 'BLOB', $s );
924  // no such thing as unsigned
925  $s = preg_replace( '/\b(un)?signed\b/i', '', $s );
926  // INT -> INTEGER
927  $s = preg_replace( '/\b(tiny|small|medium|big|)int(\s*\(\s*\d+\s*\)|\b)/i', 'INTEGER', $s );
928  // floating point types -> REAL
929  $s = preg_replace(
930  '/\b(float|double(\s+precision)?)(\s*\(\s*\d+\s*(,\s*\d+\s*)?\)|\b)/i',
931  'REAL',
932  $s
933  );
934  // varchar -> TEXT
935  $s = preg_replace( '/\b(var)?char\s*\(.*?\)/i', 'TEXT', $s );
936  // TEXT normalization
937  $s = preg_replace( '/\b(tiny|medium|long)text\b/i', 'TEXT', $s );
938  // BLOB normalization
939  $s = preg_replace( '/\b(tiny|small|medium|long|)blob\b/i', 'BLOB', $s );
940  // BOOL -> INTEGER
941  $s = preg_replace( '/\bbool(ean)?\b/i', 'INTEGER', $s );
942  // DATETIME -> TEXT
943  $s = preg_replace( '/\b(datetime|timestamp)\b/i', 'TEXT', $s );
944  // No ENUM type
945  $s = preg_replace( '/\benum\s*\([^)]*\)/i', 'TEXT', $s );
946  // binary collation type -> nothing
947  $s = preg_replace( '/\bbinary\b/i', '', $s );
948  // auto_increment -> autoincrement
949  $s = preg_replace( '/\bauto_increment\b/i', 'AUTOINCREMENT', $s );
950  // No explicit options
951  $s = preg_replace( '/\)[^);]*(;?)\s*$/', ')\1', $s );
952  // AUTOINCREMENT should immedidately follow PRIMARY KEY
953  $s = preg_replace( '/primary key (.*?) autoincrement/i', 'PRIMARY KEY AUTOINCREMENT $1', $s );
954  } elseif ( preg_match( '/^\s*CREATE (\s*(?:UNIQUE|FULLTEXT)\s+)?INDEX/i', $s ) ) {
955  // No truncated indexes
956  $s = preg_replace( '/\(\d+\)/', '', $s );
957  // No FULLTEXT
958  $s = preg_replace( '/\bfulltext\b/i', '', $s );
959  } elseif ( preg_match( '/^\s*DROP INDEX/i', $s ) ) {
960  // DROP INDEX is database-wide, not table-specific, so no ON <table> clause.
961  $s = preg_replace( '/\sON\s+[^\s]*/i', '', $s );
962  } elseif ( preg_match( '/^\s*INSERT IGNORE\b/i', $s ) ) {
963  // INSERT IGNORE --> INSERT OR IGNORE
964  $s = preg_replace( '/^\s*INSERT IGNORE\b/i', 'INSERT OR IGNORE', $s );
965  }
966 
967  return $s;
968  }
969 
970  public function lock( $lockName, $method, $timeout = 5 ) {
971  $status = $this->lockMgr->lock( [ $lockName ], LockManager::LOCK_EX, $timeout );
972  if (
973  $this->lockMgr instanceof FSLockManager &&
974  $status->hasMessage( 'lockmanager-fail-openlock' )
975  ) {
976  throw new DBError( $this, "Cannot create directory \"{$this->getLockFileDirectory()}\"" );
977  }
978 
979  return $status->isOK();
980  }
981 
982  public function unlock( $lockName, $method ) {
983  return $this->lockMgr->unlock( [ $lockName ], LockManager::LOCK_EX )->isGood();
984  }
985 
992  function buildConcat( $stringList ) {
993  return '(' . implode( ') || (', $stringList ) . ')';
994  }
995 
996  public function buildGroupConcatField(
997  $delim, $table, $field, $conds = '', $join_conds = []
998  ) {
999  $fld = "group_concat($field," . $this->addQuotes( $delim ) . ')';
1000 
1001  return '(' . $this->selectSQLText( $table, $fld, $conds, null, [], $join_conds ) . ')';
1002  }
1003 
1012  function duplicateTableStructure( $oldName, $newName, $temporary = false, $fname = __METHOD__ ) {
1013  $res = $this->query(
1014  "SELECT sql FROM sqlite_master WHERE tbl_name=" .
1015  $this->addQuotes( $oldName ) . " AND type='table'",
1016  $fname
1017  );
1018  $obj = $this->fetchObject( $res );
1019  if ( !$obj ) {
1020  throw new RuntimeException( "Couldn't retrieve structure for table $oldName" );
1021  }
1022  $sql = $obj->sql;
1023  $sql = preg_replace(
1024  '/(?<=\W)"?' .
1025  preg_quote( trim( $this->addIdentifierQuotes( $oldName ), '"' ), '/' ) .
1026  '"?(?=\W)/',
1027  $this->addIdentifierQuotes( $newName ),
1028  $sql,
1029  1
1030  );
1031  if ( $temporary ) {
1032  if ( preg_match( '/^\\s*CREATE\\s+VIRTUAL\\s+TABLE\b/i', $sql ) ) {
1033  $this->queryLogger->debug(
1034  "Table $oldName is virtual, can't create a temporary duplicate.\n" );
1035  } else {
1036  $sql = str_replace( 'CREATE TABLE', 'CREATE TEMPORARY TABLE', $sql );
1037  }
1038  }
1039 
1040  $res = $this->query( $sql, $fname, self::QUERY_PSEUDO_PERMANENT );
1041 
1042  // Take over indexes
1043  $indexList = $this->query( 'PRAGMA INDEX_LIST(' . $this->addQuotes( $oldName ) . ')' );
1044  foreach ( $indexList as $index ) {
1045  if ( strpos( $index->name, 'sqlite_autoindex' ) === 0 ) {
1046  continue;
1047  }
1048 
1049  if ( $index->unique ) {
1050  $sql = 'CREATE UNIQUE INDEX';
1051  } else {
1052  $sql = 'CREATE INDEX';
1053  }
1054  // Try to come up with a new index name, given indexes have database scope in SQLite
1055  $indexName = $newName . '_' . $index->name;
1056  $sql .= ' ' . $indexName . ' ON ' . $newName;
1057 
1058  $indexInfo = $this->query( 'PRAGMA INDEX_INFO(' . $this->addQuotes( $index->name ) . ')' );
1059  $fields = [];
1060  foreach ( $indexInfo as $indexInfoRow ) {
1061  $fields[$indexInfoRow->seqno] = $indexInfoRow->name;
1062  }
1063 
1064  $sql .= '(' . implode( ',', $fields ) . ')';
1065 
1066  $this->query( $sql );
1067  }
1068 
1069  return $res;
1070  }
1071 
1080  function listTables( $prefix = null, $fname = __METHOD__ ) {
1081  $result = $this->select(
1082  'sqlite_master',
1083  'name',
1084  "type='table'"
1085  );
1086 
1087  $endArray = [];
1088 
1089  foreach ( $result as $table ) {
1090  $vars = get_object_vars( $table );
1091  $table = array_pop( $vars );
1092 
1093  if ( !$prefix || strpos( $table, $prefix ) === 0 ) {
1094  if ( strpos( $table, 'sqlite_' ) !== 0 ) {
1095  $endArray[] = $table;
1096  }
1097  }
1098  }
1099 
1100  return $endArray;
1101  }
1102 
1111  public function dropTable( $tableName, $fName = __METHOD__ ) {
1112  if ( !$this->tableExists( $tableName, $fName ) ) {
1113  return false;
1114  }
1115  $sql = "DROP TABLE " . $this->tableName( $tableName );
1116 
1117  return $this->query( $sql, $fName, self::QUERY_IGNORE_DBO_TRX );
1118  }
1119 
1120  public function setTableAliases( array $aliases ) {
1121  parent::setTableAliases( $aliases );
1122  if ( $this->isOpen() ) {
1124  }
1125  }
1126 
1130  private function attachDatabasesFromTableAliases() {
1131  foreach ( $this->tableAliases as $params ) {
1132  if (
1133  $params['dbname'] !== $this->getDBname() &&
1134  !isset( $this->sessionAttachedDbs[$params['dbname']] )
1135  ) {
1136  $this->attachDatabase( $params['dbname'] );
1137  $this->sessionAttachedDbs[$params['dbname']] = true;
1138  }
1139  }
1140  }
1141 
1142  public function resetSequenceForTable( $table, $fname = __METHOD__ ) {
1143  $encTable = $this->addIdentifierQuotes( 'sqlite_sequence' );
1144  $encName = $this->addQuotes( $this->tableName( $table, 'raw' ) );
1145  $this->query(
1146  "DELETE FROM $encTable WHERE name = $encName",
1147  $fname,
1148  self::QUERY_IGNORE_DBO_TRX
1149  );
1150  }
1151 
1152  public function databasesAreIndependent() {
1153  return true;
1154  }
1155 
1156  protected function doHandleSessionLossPreconnect() {
1157  $this->sessionAttachedDbs = [];
1158  }
1159 
1163  protected function getBindingHandle() {
1164  return parent::getBindingHandle();
1165  }
1166 }
1167 
1171 class_alias( DatabaseSqlite::class, 'DatabaseSqlite' );
deadlockLoop(... $args)
No-op version of deadlockLoop.
if(PHP_SAPI !='cli-server') if(!isset( $_SERVER['SCRIPT_FILENAME'])) $file
Item class for a filearchive table row.
Definition: router.php:42
static newStandaloneInstance( $filename, array $p=[])
close( $fname=__METHOD__, $owner=null)
Close the database connection.
Definition: Database.php:892
FSLockManager $lockMgr
(hopefully on the same server as the DB)
tableName( $name, $format='quoted')
Use MySQL&#39;s naming (accounts for prefix etc) but remove surrounding backticks.
getFlag( $flag)
Returns a boolean whether the flag $flag is set for this connection.
Definition: Database.php:802
string null $dbDir
Directory for SQLite database files listed under their DB name.
affectedRows()
Get the number of rows affected by the last write query.
Definition: Database.php:4276
resetSequenceForTable( $table, $fname=__METHOD__)
unlock( $lockName, $method)
Release a lock.
replace( $table, $uniqueIndexes, $rows, $fname=__METHOD__)
Result wrapper for grabbing data queried from an IDatabase object.
__construct(array $params)
Additional params include:
assertBuildSubstringParams( $startPosition, $length)
Check type and bounds for parameters to self::buildSubstring()
Definition: Database.php:2369
int $flags
Current bit field of class DBO_* constants.
Definition: Database.php:96
getDomainID()
Return the currently selected domain ID.
Definition: Database.php:806
startAtomic( $fname=__METHOD__, $cancelable=self::ATOMIC_NOT_CANCELABLE)
Begin an atomic section of SQL statements.
Definition: Database.php:3838
static getFulltextSearchModule()
Returns version of currently supported SQLite fulltext search module or false if none present...
nativeReplace( $table, $rows, $fname)
REPLACE query wrapper for MySQL and SQLite, which have a native REPLACE statement.
Definition: Database.php:2931
attachDatabase( $name, $file=false, $fname=__METHOD__)
Attaches external database to the connection handle.
endAtomic( $fname=__METHOD__)
Ends an atomic section of SQL statements.
Definition: Database.php:3868
query( $sql, $fname=__METHOD__, $flags=0)
Run an SQL query and return the result.
Definition: Database.php:1155
string $server
Server that this instance is currently connected to.
Definition: Database.php:75
attachDatabasesFromTableAliases()
Issue ATTATCH statements for all unattached foreign DBs in table aliases.
insertId()
This must be called after nextSequenceVal.
newExceptionAfterConnectError( $error)
Definition: Database.php:1634
const DBO_PERSISTENT
Definition: defines.php:14
if( $line===false) $args
Definition: cdb.php:64
getDBname()
Get the current DB name.
Definition: Database.php:2429
listTables( $prefix=null, $fname=__METHOD__)
List all tables on the database.
textFieldSize( $table, $field)
Returns the size of a text field, or -1 for "unlimited" In SQLite this is SQLITE_MAX_LENGTH, by default 1GB.
doQuery( $sql)
SQLite doesn&#39;t allow buffered results or data seeking etc, so we&#39;ll use fetchAll as the result...
assertHasConnectionHandle()
Make sure there is an open connection handle (alive or not) as a sanity check.
Definition: Database.php:970
setTableAliases(array $aliases)
Make certain table names use their own database, schema, and table prefix when passed into SQL querie...
indexName( $index)
Allows for index remapping in queries where this is not consistent across DBMS.
Definition: Database.php:2754
const LOCK_EX
Definition: LockManager.php:70
fieldInfo( $table, $field)
Get information about a given field Returns false if the field does not exist.
insert( $table, $rows, $fname=__METHOD__, $options=[])
INSERT wrapper, inserts an array into a table.
integer null $affectedRowCount
Rows affected by the last query to query() or its CRUD wrappers.
Definition: Database.php:171
indexInfo( $table, $index, $fname=__METHOD__)
Returns information about an index Returns false if the index does not exist.
tableExists( $table, $fname=__METHOD__)
Query whether a given table exists.
databasesAreIndependent()
Returns true if DBs are assumed to be on potentially different servers.
selectSQLText( $table, $vars, $conds='', $fname=__METHOD__, $options=[], $join_conds=[])
Take the same arguments as IDatabase::select() and return the SQL it would use.
Definition: Database.php:1831
const DBO_TRX
Definition: defines.php:12
string null $dbPath
Explicit path for the SQLite database file.
cancelAtomic( $fname=__METHOD__, AtomicSectionIdentifier $sectionId=null)
Cancel an atomic section of SQL statements.
Definition: Database.php:3902
static generateFileName( $dir, $dbName)
Generates a database file name.
numRows( $res)
The PDO::Statement class implements the array interface so count() will work.
const DBO_DEFAULT
Definition: defines.php:13
Class to handle database/schema/prefix specifications for IDatabase.
doSelectDomain(DatabaseDomain $domain)
getLogContext(array $extras=[])
Create a log context to pass to PSR-3 logger functions.
Definition: Database.php:881
selectRow( $table, $vars, $conds, $fname=__METHOD__, $options=[], $join_conds=[])
Wrapper to IDatabase::select() that only fetches one row (via LIMIT)
Definition: Database.php:1914
Relational database abstraction object.
Definition: Database.php:49
select( $table, $vars, $conds='', $fname=__METHOD__, $options=[], $join_conds=[])
Execute a SELECT query constructed using the various parameters provided.
Definition: Database.php:1823
lock( $lockName, $method, $timeout=5)
Acquire a named lock.
closeConnection()
Does not actually close the connection, just destroys the reference for GC to do its work...
indexUnique( $table, $index, $fname=__METHOD__)
dropTable( $tableName, $fName=__METHOD__)
Override due to no CASCADE support.
string $user
User that this instance is currently connected under the name of.
Definition: Database.php:77
addIdentifierQuotes( $s)
Escape a SQL identifier (e.g.
Definition: Database.php:2775
buildGroupConcatField( $delim, $table, $field, $conds='', $join_conds=[])
Build a GROUP_CONCAT or equivalent statement for a query.
duplicateTableStructure( $oldName, $newName, $temporary=false, $fname=__METHOD__)
buildConcat( $stringList)
Build a concatenation list to feed into a SQL query.
open( $server, $user, $pass, $dbName, $schema, $tablePrefix)
static string [] $VALID_TRX_MODES
See https://www.sqlite.org/lang_transaction.html.
int $lastAffectedRowCount
The number of rows affected as an integer.
buildSubstring( $input, $startPosition, $length=null)
static factory( $type, $params=[], $connect=self::NEW_CONNECTED)
Construct a Database subclass instance given a database type and parameters.
Definition: Database.php:389
string $trxMode
Transaction mode.
array $sessionAttachedDbs
List of shared database already attached to this connection.
return true
Definition: router.php:92
Database error base class.
Definition: DBError.php:30
static & unwrap(&$res)
Get the underlying RDBMS driver-specific result resource.
Base class for the more common types of database errors.