MediaWiki  master
SqlitePlatform.php
Go to the documentation of this file.
1 <?php
20 namespace Wikimedia\Rdbms\Platform;
21 
26 class SqlitePlatform extends SQLPlatform {
27  public function buildGreatest( $fields, $values ) {
28  return $this->buildSuperlative( 'MAX', $fields, $values );
29  }
30 
31  public function buildLeast( $fields, $values ) {
32  return $this->buildSuperlative( 'MIN', $fields, $values );
33  }
34 
41  public function buildConcat( $stringList ) {
42  return '(' . implode( ') || (', $stringList ) . ')';
43  }
44 
50  public function unionQueries( $sqls, $all ) {
51  $glue = $all ? ' UNION ALL ' : ' UNION ';
52 
53  return implode( $glue, $sqls );
54  }
55 
59  public function unionSupportsOrderAndLimit() {
60  return false;
61  }
62 
63  public function buildSubstring( $input, $startPosition, $length = null ) {
64  $this->assertBuildSubstringParams( $startPosition, $length );
65  $params = [ $input, $startPosition ];
66  if ( $length !== null ) {
67  $params[] = $length;
68  }
69  return 'SUBSTR(' . implode( ',', $params ) . ')';
70  }
71 
77  public function buildStringCast( $field ) {
78  return 'CAST ( ' . $field . ' AS TEXT )';
79  }
80 
81  public function tableName( $name, $format = 'quoted' ) {
82  if ( preg_match( '/^sqlite_[a-z_]+$/', $name ) ) {
83  // Such names are reserved for internal SQLite tables
84  return $name;
85  }
86 
87  return parent::tableName( $name, $format );
88  }
89 
90  protected function makeSelectOptions( array $options ) {
91  // Remove problematic options that the base implementation converts to SQL
92  foreach ( $options as $k => $v ) {
93  if ( is_numeric( $k ) && ( $v === 'FOR UPDATE' || $v === 'LOCK IN SHARE MODE' ) ) {
94  $options[$k] = '';
95  }
96  }
97 
98  return parent::makeSelectOptions( $options );
99  }
100 
101  public function buildGroupConcatField(
102  $delim, $table, $field, $conds = '', $join_conds = []
103  ) {
104  $fld = "group_concat($field," . $this->quoter->addQuotes( $delim ) . ')';
105 
106  return '(' . $this->selectSQLText( $table, $fld, $conds, null, [], $join_conds ) . ')';
107  }
108 
110  return [ 'INSERT OR IGNORE INTO', '' ];
111  }
112 
117  protected function makeUpdateOptionsArray( $options ) {
118  $options = parent::makeUpdateOptionsArray( $options );
119  $options = $this->rewriteIgnoreKeyword( $options );
120 
121  return $options;
122  }
123 
128  private function rewriteIgnoreKeyword( $options ) {
129  # SQLite uses OR IGNORE not just IGNORE
130  foreach ( $options as $k => $v ) {
131  if ( $v == 'IGNORE' ) {
132  $options[$k] = 'OR IGNORE';
133  }
134  }
135 
136  return $options;
137  }
138 
139  public function dropTableSqlText( $table ) {
140  // No CASCADE support; https://www.sqlite.org/lang_droptable.html
141  return "DROP TABLE " . $this->tableName( $table );
142  }
143 
144  public function isTransactableQuery( $sql ) {
145  return parent::isTransactableQuery( $sql ) && !in_array(
146  $this->getQueryVerb( $sql ),
147  [ 'ATTACH', 'PRAGMA' ],
148  true
149  );
150  }
151 
152  public function isWriteQuery( $sql, $flags ) {
153  return parent::isWriteQuery( $sql, $flags ) && !preg_match( '/^(ATTACH|PRAGMA)\b/i', $sql );
154  }
155 
160  public function replaceVars( $s ) {
161  $s = parent::replaceVars( $s );
162  if ( preg_match( '/^\s*(CREATE|ALTER) TABLE/i', $s ) ) {
163  // CREATE TABLE hacks to allow schema file sharing with MySQL
164 
165  // binary/varbinary column type -> blob
166  $s = preg_replace( '/\b(var)?binary(\‍(\d+\‍))/i', 'BLOB', $s );
167  // no such thing as unsigned
168  $s = preg_replace( '/\b(un)?signed\b/i', '', $s );
169  // INT -> INTEGER
170  $s = preg_replace( '/\b(tiny|small|medium|big|)int(\s*\‍(\s*\d+\s*\‍)|\b)/i', 'INTEGER', $s );
171  // floating point types -> REAL
172  $s = preg_replace(
173  '/\b(float|double(\s+precision)?)(\s*\‍(\s*\d+\s*(,\s*\d+\s*)?\‍)|\b)/i',
174  'REAL',
175  $s
176  );
177  // varchar -> TEXT
178  $s = preg_replace( '/\b(var)?char\s*\‍(.*?\‍)/i', 'TEXT', $s );
179  // TEXT normalization
180  $s = preg_replace( '/\b(tiny|medium|long)text\b/i', 'TEXT', $s );
181  // BLOB normalization
182  $s = preg_replace( '/\b(tiny|small|medium|long|)blob\b/i', 'BLOB', $s );
183  // BOOL -> INTEGER
184  $s = preg_replace( '/\bbool(ean)?\b/i', 'INTEGER', $s );
185  // DATETIME -> TEXT
186  $s = preg_replace( '/\b(datetime|timestamp)\b/i', 'TEXT', $s );
187  // No ENUM type
188  $s = preg_replace( '/\benum\s*\‍([^)]*\‍)/i', 'TEXT', $s );
189  // binary collation type -> nothing
190  $s = preg_replace( '/\bbinary\b/i', '', $s );
191  // auto_increment -> autoincrement
192  $s = preg_replace( '/\bauto_increment\b/i', 'AUTOINCREMENT', $s );
193  // No explicit options
194  $s = preg_replace( '/\‍)[^);]*(;?)\s*$/', ')\1', $s );
195  // AUTOINCREMENT should immediately follow PRIMARY KEY
196  $s = preg_replace( '/primary key (.*?) autoincrement/i', 'PRIMARY KEY AUTOINCREMENT $1', $s );
197  } elseif ( preg_match( '/^\s*CREATE (\s*(?:UNIQUE|FULLTEXT)\s+)?INDEX/i', $s ) ) {
198  // No truncated indexes
199  $s = preg_replace( '/\‍(\d+\‍)/', '', $s );
200  // No FULLTEXT
201  $s = preg_replace( '/\bfulltext\b/i', '', $s );
202  } elseif ( preg_match( '/^\s*DROP INDEX/i', $s ) ) {
203  // DROP INDEX is database-wide, not table-specific, so no ON <table> clause.
204  $s = preg_replace( '/\sON\s+[^\s]*/i', '', $s );
205  } elseif ( preg_match( '/^\s*INSERT IGNORE\b/i', $s ) ) {
206  // INSERT IGNORE --> INSERT OR IGNORE
207  $s = preg_replace( '/^\s*INSERT IGNORE\b/i', 'INSERT OR IGNORE', $s );
208  }
209 
210  return $s;
211  }
212 }
buildSuperlative( $sqlfunc, $fields, $values)
Build a superlative function statement comparing columns/values.
assertBuildSubstringParams( $startPosition, $length)
Check type and bounds for parameters to self::buildSubstring()
selectSQLText( $table, $vars, $conds='', $fname=__METHOD__, $options=[], $join_conds=[])
Take the same arguments as IDatabase::select() and return the SQL it would use.This can be useful for...
isWriteQuery( $sql, $flags)
Determine whether a query writes to the DB.
buildConcat( $stringList)
Build a concatenation list to feed into a SQL query.
isTransactableQuery( $sql)
Determine whether a SQL statement is sensitive to isolation level.
buildGreatest( $fields, $values)
Build a GREATEST function statement comparing columns/values.Integer and float values in $values will...
buildLeast( $fields, $values)
Build a LEAST function statement comparing columns/values.Integer and float values in $values will no...
buildSubstring( $input, $startPosition, $length=null)
Stability: stableto override
buildGroupConcatField( $delim, $table, $field, $conds='', $join_conds=[])
Build a GROUP_CONCAT or equivalent statement for a query.This is useful for combining a field for sev...
makeSelectOptions(array $options)
Returns an optional USE INDEX clause to go after the table, and a string to go at the end of the quer...
tableName( $name, $format='quoted')
Format a table name ready for use in constructing an SQL query.This does two important things: it quo...
foreach( $mmfl['setupFiles'] as $fileName) if( $queue) if(empty( $mmfl['quiet'])) $s