MediaWiki  master
SQLPlatform.php
Go to the documentation of this file.
1 <?php
20 namespace Wikimedia\Rdbms\Platform;
21 
22 use InvalidArgumentException;
23 use Psr\Log\LoggerInterface;
24 use Psr\Log\NullLogger;
25 use RuntimeException;
26 use Wikimedia\Assert\Assert;
32 use Wikimedia\Timestamp\ConvertibleTimestamp;
33 
41 class SQLPlatform implements ISQLPlatform {
43  protected $tableAliases = [];
45  protected $indexAliases = [];
47  protected $currentDomain;
49  protected $schemaVars;
51  protected $quoter;
53  protected $logger;
54 
55  public function __construct(
57  LoggerInterface $logger = null,
59  ) {
60  $this->quoter = $quoter;
61  $this->logger = $logger ?? new NullLogger();
62  $this->currentDomain = $currentDomain;
63  }
64 
69  public function bitNot( $field ) {
70  return "(~$field)";
71  }
72 
77  public function bitAnd( $fieldLeft, $fieldRight ) {
78  return "($fieldLeft & $fieldRight)";
79  }
80 
85  public function bitOr( $fieldLeft, $fieldRight ) {
86  return "($fieldLeft | $fieldRight)";
87  }
88 
93  public function addIdentifierQuotes( $s ) {
94  return '"' . str_replace( '"', '""', $s ) . '"';
95  }
96 
100  public function buildGreatest( $fields, $values ) {
101  return $this->buildSuperlative( 'GREATEST', $fields, $values );
102  }
103 
107  public function buildLeast( $fields, $values ) {
108  return $this->buildSuperlative( 'LEAST', $fields, $values );
109  }
110 
125  protected function buildSuperlative( $sqlfunc, $fields, $values ) {
126  $fields = is_array( $fields ) ? $fields : [ $fields ];
127  $values = is_array( $values ) ? $values : [ $values ];
128 
129  $encValues = [];
130  foreach ( $fields as $alias => $field ) {
131  if ( is_int( $alias ) ) {
132  $encValues[] = $this->addIdentifierQuotes( $field );
133  } else {
134  $encValues[] = $field; // expression
135  }
136  }
137  foreach ( $values as $value ) {
138  if ( is_int( $value ) || is_float( $value ) ) {
139  $encValues[] = $value;
140  } elseif ( is_string( $value ) ) {
141  $encValues[] = $this->quoter->addQuotes( $value );
142  } elseif ( $value === null ) {
143  throw new DBLanguageError( 'Null value in superlative' );
144  } else {
145  throw new DBLanguageError( 'Unexpected value type in superlative' );
146  }
147  }
148 
149  return $sqlfunc . '(' . implode( ',', $encValues ) . ')';
150  }
151 
152  public function makeList( array $a, $mode = self::LIST_COMMA ) {
153  $first = true;
154  $list = '';
155 
156  foreach ( $a as $field => $value ) {
157  if ( $first ) {
158  $first = false;
159  } else {
160  if ( $mode == self::LIST_AND ) {
161  $list .= ' AND ';
162  } elseif ( $mode == self::LIST_OR ) {
163  $list .= ' OR ';
164  } else {
165  $list .= ',';
166  }
167  }
168 
169  if ( ( $mode == self::LIST_AND || $mode == self::LIST_OR ) && is_numeric( $field ) ) {
170  $list .= "($value)";
171  } elseif ( $mode == self::LIST_SET && is_numeric( $field ) ) {
172  $list .= "$value";
173  } elseif (
174  ( $mode == self::LIST_AND || $mode == self::LIST_OR ) && is_array( $value )
175  ) {
176  // Remove null from array to be handled separately if found
177  $includeNull = false;
178  foreach ( array_keys( $value, null, true ) as $nullKey ) {
179  $includeNull = true;
180  unset( $value[$nullKey] );
181  }
182  if ( count( $value ) == 0 && !$includeNull ) {
183  throw new InvalidArgumentException(
184  __METHOD__ . ": empty input for field $field" );
185  } elseif ( count( $value ) == 0 ) {
186  // only check if $field is null
187  $list .= "$field IS NULL";
188  } else {
189  // IN clause contains at least one valid element
190  if ( $includeNull ) {
191  // Group subconditions to ensure correct precedence
192  $list .= '(';
193  }
194  if ( count( $value ) == 1 ) {
195  // Special-case single values, as IN isn't terribly efficient
196  // Don't necessarily assume the single key is 0; we don't
197  // enforce linear numeric ordering on other arrays here.
198  $value = array_values( $value )[0];
199  $list .= $field . " = " . $this->quoter->addQuotes( $value );
200  } else {
201  $list .= $field . " IN (" . $this->makeList( $value ) . ") ";
202  }
203  // if null present in array, append IS NULL
204  if ( $includeNull ) {
205  $list .= " OR $field IS NULL)";
206  }
207  }
208  } elseif ( $value === null ) {
209  if ( $mode == self::LIST_AND || $mode == self::LIST_OR ) {
210  $list .= "$field IS ";
211  } elseif ( $mode == self::LIST_SET ) {
212  $list .= "$field = ";
213  }
214  $list .= 'NULL';
215  } else {
216  if (
217  $mode == self::LIST_AND || $mode == self::LIST_OR || $mode == self::LIST_SET
218  ) {
219  $list .= "$field = ";
220  }
221  $list .= $mode == self::LIST_NAMES ? $value : $this->quoter->addQuotes( $value );
222  }
223  }
224 
225  return $list;
226  }
227 
228  public function makeWhereFrom2d( $data, $baseKey, $subKey ) {
229  $conds = [];
230 
231  foreach ( $data as $base => $sub ) {
232  if ( count( $sub ) ) {
233  $conds[] = $this->makeList(
234  [ $baseKey => $base, $subKey => array_map( 'strval', array_keys( $sub ) ) ],
236  );
237  }
238  }
239 
240  if ( $conds ) {
241  return $this->makeList( $conds, self::LIST_OR );
242  } else {
243  // Nothing to search for...
244  return false;
245  }
246  }
247 
248  public function factorConds( $condsArray ) {
249  if ( count( $condsArray ) === 0 ) {
250  throw new InvalidArgumentException(
251  __METHOD__ . ": empty condition array" );
252  }
253  $condsByFieldSet = [];
254  foreach ( $condsArray as $conds ) {
255  if ( !count( $conds ) ) {
256  throw new InvalidArgumentException(
257  __METHOD__ . ": empty condition subarray" );
258  }
259  $fieldKey = implode( ',', array_keys( $conds ) );
260  $condsByFieldSet[$fieldKey][] = $conds;
261  }
262  $result = '';
263  foreach ( $condsByFieldSet as $conds ) {
264  if ( $result !== '' ) {
265  $result .= ' OR ';
266  }
267  $result .= $this->factorCondsWithCommonFields( $conds );
268  }
269  return $result;
270  }
271 
279  private function factorCondsWithCommonFields( $condsArray ) {
280  $first = $condsArray[array_key_first( $condsArray )];
281  if ( count( $first ) === 1 ) {
282  // IN clause
283  $field = array_key_first( $first );
284  $values = [];
285  foreach ( $condsArray as $conds ) {
286  $values[] = $conds[$field];
287  }
288  return $this->makeList( [ $field => $values ], self::LIST_AND );
289  }
290 
291  $field1 = array_key_first( $first );
292  $nullExpressions = [];
293  $expressionsByField1 = [];
294  foreach ( $condsArray as $conds ) {
295  $value1 = $conds[$field1];
296  unset( $conds[$field1] );
297  if ( $value1 === null ) {
298  $nullExpressions[] = $conds;
299  } else {
300  $expressionsByField1[$value1][] = $conds;
301  }
302 
303  }
304  $wrap = false;
305  $result = '';
306  foreach ( $expressionsByField1 as $value1 => $expressions ) {
307  if ( $result !== '' ) {
308  $result .= ' OR ';
309  $wrap = true;
310  }
311  $factored = $this->factorCondsWithCommonFields( $expressions );
312  $result .= "($field1 = " . $this->quoter->addQuotes( $value1 ) .
313  " AND $factored)";
314  }
315  if ( count( $nullExpressions ) ) {
316  $factored = $this->factorCondsWithCommonFields( $nullExpressions );
317  if ( $result !== '' ) {
318  $result .= ' OR ';
319  $wrap = true;
320  }
321  $result .= "($field1 IS NULL AND $factored)";
322  }
323  if ( $wrap ) {
324  return "($result)";
325  } else {
326  return $result;
327  }
328  }
329 
334  public function buildConcat( $stringList ) {
335  return 'CONCAT(' . implode( ',', $stringList ) . ')';
336  }
337 
342  public function limitResult( $sql, $limit, $offset = false ) {
343  if ( !is_numeric( $limit ) ) {
344  throw new DBLanguageError(
345  "Invalid non-numeric limit passed to " . __METHOD__
346  );
347  }
348  // This version works in MySQL and SQLite. It will very likely need to be
349  // overridden for most other RDBMS subclasses.
350  return "$sql LIMIT "
351  . ( ( is_numeric( $offset ) && $offset != 0 ) ? "{$offset}," : "" )
352  . "{$limit} ";
353  }
354 
361  public function escapeLikeInternal( $s, $escapeChar = '`' ) {
362  return str_replace(
363  [ $escapeChar, '%', '_' ],
364  [ "{$escapeChar}{$escapeChar}", "{$escapeChar}%", "{$escapeChar}_" ],
365  $s
366  );
367  }
368 
373  public function buildLike( $param, ...$params ) {
374  if ( is_array( $param ) ) {
375  $params = $param;
376  } else {
377  $params = func_get_args();
378  }
379 
380  $s = '';
381 
382  // We use ` instead of \ as the default LIKE escape character, since addQuotes()
383  // may escape backslashes, creating problems of double escaping. The `
384  // character has good cross-DBMS compatibility, avoiding special operators
385  // in MS SQL like ^ and %
386  $escapeChar = '`';
387 
388  foreach ( $params as $value ) {
389  if ( $value instanceof LikeMatch ) {
390  $s .= $value->toString();
391  } else {
392  $s .= $this->escapeLikeInternal( $value, $escapeChar );
393  }
394  }
395 
396  return ' LIKE ' .
397  $this->quoter->addQuotes( $s ) . ' ESCAPE ' . $this->quoter->addQuotes( $escapeChar ) . ' ';
398  }
399 
400  public function anyChar() {
401  return new LikeMatch( '_' );
402  }
403 
404  public function anyString() {
405  return new LikeMatch( '%' );
406  }
407 
412  public function unionSupportsOrderAndLimit() {
413  return true; // True for almost every DB supported
414  }
415 
420  public function unionQueries( $sqls, $all ) {
421  $glue = $all ? ') UNION ALL (' : ') UNION (';
422 
423  return '(' . implode( $glue, $sqls ) . ')';
424  }
425 
430  public function conditional( $cond, $caseTrueExpression, $caseFalseExpression ) {
431  if ( is_array( $cond ) ) {
432  $cond = $this->makeList( $cond, self::LIST_AND );
433  }
434 
435  return "(CASE WHEN $cond THEN $caseTrueExpression ELSE $caseFalseExpression END)";
436  }
437 
442  public function strreplace( $orig, $old, $new ) {
443  return "REPLACE({$orig}, {$old}, {$new})";
444  }
445 
450  public function timestamp( $ts = 0 ) {
451  $t = new ConvertibleTimestamp( $ts );
452  // Let errors bubble up to avoid putting garbage in the DB
453  return $t->getTimestamp( TS_MW );
454  }
455 
456  public function timestampOrNull( $ts = null ) {
457  if ( $ts === null ) {
458  return null;
459  } else {
460  return $this->timestamp( $ts );
461  }
462  }
463 
468  public function getInfinity() {
469  return 'infinity';
470  }
471 
472  public function encodeExpiry( $expiry ) {
473  return ( $expiry == '' || $expiry == 'infinity' || $expiry == $this->getInfinity() )
474  ? $this->getInfinity()
475  : $this->timestamp( $expiry );
476  }
477 
478  public function decodeExpiry( $expiry, $format = TS_MW ) {
479  if ( $expiry == '' || $expiry == 'infinity' || $expiry == $this->getInfinity() ) {
480  return 'infinity';
481  }
482 
483  return ConvertibleTimestamp::convert( $format, $expiry );
484  }
485 
490  public function buildSubstring( $input, $startPosition, $length = null ) {
491  $this->assertBuildSubstringParams( $startPosition, $length );
492  $functionBody = "$input FROM $startPosition";
493  if ( $length !== null ) {
494  $functionBody .= " FOR $length";
495  }
496  return 'SUBSTRING(' . $functionBody . ')';
497  }
498 
511  protected function assertBuildSubstringParams( $startPosition, $length ) {
512  if ( $startPosition === 0 ) {
513  // The DBMSs we support use 1-based indexing here.
514  throw new InvalidArgumentException( 'Use 1 as $startPosition for the beginning of the string' );
515  }
516  if ( !is_int( $startPosition ) || $startPosition < 0 ) {
517  throw new InvalidArgumentException(
518  '$startPosition must be a positive integer'
519  );
520  }
521  if ( !( is_int( $length ) && $length >= 0 || $length === null ) ) {
522  throw new InvalidArgumentException(
523  '$length must be null or an integer greater than or equal to 0'
524  );
525  }
526  }
527 
532  public function buildStringCast( $field ) {
533  // In theory this should work for any standards-compliant
534  // SQL implementation, although it may not be the best way to do it.
535  return "CAST( $field AS CHARACTER )";
536  }
537 
542  public function buildIntegerCast( $field ) {
543  return 'CAST( ' . $field . ' AS INTEGER )';
544  }
545 
550  public function implicitOrderby() {
551  return true;
552  }
553 
562  public function indexName( $index ) {
563  return $this->indexAliases[$index] ?? $index;
564  }
565 
570  public function setTableAliases( array $aliases ) {
571  $this->tableAliases = $aliases;
572  }
573 
578  public function setIndexAliases( array $aliases ) {
579  $this->indexAliases = $aliases;
580  }
581 
585  public function getTableAliases() {
586  return $this->tableAliases;
587  }
588 
589  public function setPrefix( $prefix ) {
590  $this->currentDomain = new DatabaseDomain(
591  $this->currentDomain->getDatabase(),
592  $this->currentDomain->getSchema(),
593  $prefix
594  );
595  }
596 
598  $this->currentDomain = $currentDomain;
599  }
600 
605  public function selectSQLText(
606  $table, $vars, $conds = '', $fname = __METHOD__, $options = [], $join_conds = []
607  ) {
608  if ( is_array( $vars ) ) {
609  $fields = implode( ',', $this->fieldNamesWithAlias( $vars ) );
610  } else {
611  $fields = $vars;
612  }
613 
614  $options = (array)$options;
615  $useIndexes = ( isset( $options['USE INDEX'] ) && is_array( $options['USE INDEX'] ) )
616  ? $options['USE INDEX']
617  : [];
618  $ignoreIndexes = (
619  isset( $options['IGNORE INDEX'] ) &&
620  is_array( $options['IGNORE INDEX'] )
621  )
622  ? $options['IGNORE INDEX']
623  : [];
624 
625  if (
626  $this->selectOptionsIncludeLocking( $options ) &&
627  $this->selectFieldsOrOptionsAggregate( $vars, $options )
628  ) {
629  // Some DB types (e.g. postgres) disallow FOR UPDATE with aggregate
630  // functions. Discourage use of such queries to encourage compatibility.
631  $this->logger->warning(
632  __METHOD__ . ": aggregation used with a locking SELECT ($fname)"
633  );
634  }
635 
636  if ( is_array( $table ) ) {
637  if ( count( $table ) === 0 ) {
638  $from = '';
639  } else {
640  $from = ' FROM ' .
642  $table, $useIndexes, $ignoreIndexes, $join_conds );
643  }
644  } elseif ( $table != '' ) {
645  $from = ' FROM ' .
647  [ $table ], $useIndexes, $ignoreIndexes, [] );
648  } else {
649  $from = '';
650  }
651 
652  list( $startOpts, $useIndex, $preLimitTail, $postLimitTail, $ignoreIndex ) =
653  $this->makeSelectOptions( $options );
654 
655  if ( is_array( $conds ) ) {
656  $conds = $this->makeList( $conds, self::LIST_AND );
657  }
658 
659  if ( $conds === null || $conds === false ) {
660  $this->logger->warning(
661  __METHOD__
662  . ' called from '
663  . $fname
664  . ' with incorrect parameters: $conds must be a string or an array',
665  [ 'db_log_category' => 'sql' ]
666  );
667  $conds = '';
668  }
669 
670  if ( $conds === '' || $conds === '*' ) {
671  $sql = "SELECT $startOpts $fields $from $useIndex $ignoreIndex $preLimitTail";
672  } elseif ( is_string( $conds ) ) {
673  $sql = "SELECT $startOpts $fields $from $useIndex $ignoreIndex " .
674  "WHERE $conds $preLimitTail";
675  } else {
676  throw new DBLanguageError( __METHOD__ . ' called with incorrect parameters' );
677  }
678 
679  if ( isset( $options['LIMIT'] ) ) {
680  $sql = $this->limitResult( $sql, $options['LIMIT'],
681  $options['OFFSET'] ?? false );
682  }
683  $sql = "$sql $postLimitTail";
684 
685  if ( isset( $options['EXPLAIN'] ) ) {
686  $sql = 'EXPLAIN ' . $sql;
687  }
688 
689  return $sql;
690  }
691 
696  private function selectOptionsIncludeLocking( $options ) {
697  $options = (array)$options;
698  foreach ( [ 'FOR UPDATE', 'LOCK IN SHARE MODE' ] as $lock ) {
699  if ( in_array( $lock, $options, true ) ) {
700  return true;
701  }
702  }
703 
704  return false;
705  }
706 
712  private function selectFieldsOrOptionsAggregate( $fields, $options ) {
713  foreach ( (array)$options as $key => $value ) {
714  if ( is_string( $key ) ) {
715  if ( preg_match( '/^(?:GROUP BY|HAVING)$/i', $key ) ) {
716  return true;
717  }
718  } elseif ( is_string( $value ) ) {
719  if ( preg_match( '/^(?:DISTINCT|DISTINCTROW)$/i', $value ) ) {
720  return true;
721  }
722  }
723  }
724 
725  $regex = '/^(?:COUNT|MIN|MAX|SUM|GROUP_CONCAT|LISTAGG|ARRAY_AGG)\s*\\(/i';
726  foreach ( (array)$fields as $field ) {
727  if ( is_string( $field ) && preg_match( $regex, $field ) ) {
728  return true;
729  }
730  }
731 
732  return false;
733  }
734 
741  protected function fieldNamesWithAlias( $fields ) {
742  $retval = [];
743  foreach ( $fields as $alias => $field ) {
744  if ( is_numeric( $alias ) ) {
745  $alias = $field;
746  }
747  $retval[] = $this->fieldNameWithAlias( $field, $alias );
748  }
749 
750  return $retval;
751  }
752 
762  public function fieldNameWithAlias( $name, $alias = false ) {
763  if ( !$alias || (string)$alias === (string)$name ) {
764  return $name;
765  } else {
766  return $name . ' AS ' . $this->addIdentifierQuotes( $alias ); // PostgreSQL needs AS
767  }
768  }
769 
781  $tables,
782  $use_index = [],
783  $ignore_index = [],
784  $join_conds = []
785  ) {
786  $ret = [];
787  $retJOIN = [];
788  $use_index = (array)$use_index;
789  $ignore_index = (array)$ignore_index;
790  $join_conds = (array)$join_conds;
791 
792  foreach ( $tables as $alias => $table ) {
793  if ( !is_string( $alias ) ) {
794  // No alias? Set it equal to the table name
795  $alias = $table;
796  }
797 
798  if ( is_array( $table ) ) {
799  // A parenthesized group
800  if ( count( $table ) > 1 ) {
801  $joinedTable = '(' .
803  $table, $use_index, $ignore_index, $join_conds ) . ')';
804  } else {
805  // Degenerate case
806  $innerTable = reset( $table );
807  $innerAlias = key( $table );
808  $joinedTable = $this->tableNameWithAlias(
809  $innerTable,
810  is_string( $innerAlias ) ? $innerAlias : $innerTable
811  );
812  }
813  } else {
814  $joinedTable = $this->tableNameWithAlias( $table, $alias );
815  }
816 
817  // Is there a JOIN clause for this table?
818  if ( isset( $join_conds[$alias] ) ) {
819  Assert::parameterType( 'array', $join_conds[$alias], "join_conds[$alias]" );
820  list( $joinType, $conds ) = $join_conds[$alias];
821  $tableClause = $this->normalizeJoinType( $joinType );
822  $tableClause .= ' ' . $joinedTable;
823  if ( isset( $use_index[$alias] ) ) { // has USE INDEX?
824  $use = $this->useIndexClause( implode( ',', (array)$use_index[$alias] ) );
825  if ( $use != '' ) {
826  $tableClause .= ' ' . $use;
827  }
828  }
829  if ( isset( $ignore_index[$alias] ) ) { // has IGNORE INDEX?
830  $ignore = $this->ignoreIndexClause(
831  implode( ',', (array)$ignore_index[$alias] ) );
832  if ( $ignore != '' ) {
833  $tableClause .= ' ' . $ignore;
834  }
835  }
836  $on = $this->makeList( (array)$conds, self::LIST_AND );
837  if ( $on != '' ) {
838  $tableClause .= ' ON (' . $on . ')';
839  }
840 
841  $retJOIN[] = $tableClause;
842  } elseif ( isset( $use_index[$alias] ) ) {
843  // Is there an INDEX clause for this table?
844  $tableClause = $joinedTable;
845  $tableClause .= ' ' . $this->useIndexClause(
846  implode( ',', (array)$use_index[$alias] )
847  );
848 
849  $ret[] = $tableClause;
850  } elseif ( isset( $ignore_index[$alias] ) ) {
851  // Is there an INDEX clause for this table?
852  $tableClause = $joinedTable;
853  $tableClause .= ' ' . $this->ignoreIndexClause(
854  implode( ',', (array)$ignore_index[$alias] )
855  );
856 
857  $ret[] = $tableClause;
858  } else {
859  $tableClause = $joinedTable;
860 
861  $ret[] = $tableClause;
862  }
863  }
864 
865  // We can't separate explicit JOIN clauses with ',', use ' ' for those
866  $implicitJoins = implode( ',', $ret );
867  $explicitJoins = implode( ' ', $retJOIN );
868 
869  // Compile our final table clause
870  return implode( ' ', [ $implicitJoins, $explicitJoins ] );
871  }
872 
881  protected function normalizeJoinType( string $joinType ) {
882  switch ( strtoupper( $joinType ) ) {
883  case 'JOIN':
884  case 'INNER JOIN':
885  return 'JOIN';
886 
887  case 'LEFT JOIN':
888  return 'LEFT JOIN';
889 
890  case 'STRAIGHT_JOIN':
891  case 'STRAIGHT JOIN':
892  // MySQL only
893  return 'JOIN';
894 
895  default:
896  return $joinType;
897  }
898  }
899 
911  protected function tableNameWithAlias( $table, $alias = false ) {
912  if ( is_string( $table ) ) {
913  $quotedTable = $this->tableName( $table );
914  } elseif ( $table instanceof Subquery ) {
915  $quotedTable = (string)$table;
916  } else {
917  throw new InvalidArgumentException( "Table must be a string or Subquery" );
918  }
919 
920  if ( $alias === false || $alias === $table ) {
921  if ( $table instanceof Subquery ) {
922  throw new InvalidArgumentException( "Subquery table missing alias" );
923  }
924 
925  return $quotedTable;
926  } else {
927  return $quotedTable . ' ' . $this->addIdentifierQuotes( $alias );
928  }
929  }
930 
935  public function tableName( $name, $format = 'quoted' ) {
936  if ( $name instanceof Subquery ) {
937  throw new DBLanguageError(
938  __METHOD__ . ': got Subquery instance when expecting a string'
939  );
940  }
941 
942  # Skip the entire process when we have a string quoted on both ends.
943  # Note that we check the end so that we will still quote any use of
944  # use of `database`.table. But won't break things if someone wants
945  # to query a database table with a dot in the name.
946  if ( $this->isQuotedIdentifier( $name ) ) {
947  return $name;
948  }
949 
950  # Lets test for any bits of text that should never show up in a table
951  # name. Basically anything like JOIN or ON which are actually part of
952  # SQL queries, but may end up inside of the table value to combine
953  # sql. Such as how the API is doing.
954  # Note that we use a whitespace test rather than a \b test to avoid
955  # any remote case where a word like on may be inside of a table name
956  # surrounded by symbols which may be considered word breaks.
957  if ( preg_match( '/(^|\s)(DISTINCT|JOIN|ON|AS)(\s|$)/i', $name ) !== 0 ) {
958  $this->logger->warning(
959  __METHOD__ . ": use of subqueries is not supported this way",
960  [
961  'exception' => new RuntimeException(),
962  'db_log_category' => 'sql',
963  ]
964  );
965 
966  return $name;
967  }
968 
969  # Split database and table into proper variables.
970  list( $database, $schema, $prefix, $table ) = $this->qualifiedTableComponents( $name );
971 
972  # Quote $table and apply the prefix if not quoted.
973  # $tableName might be empty if this is called from Database::replaceVars()
974  $tableName = "{$prefix}{$table}";
975  if ( $format === 'quoted'
976  && !$this->isQuotedIdentifier( $tableName )
977  && $tableName !== ''
978  ) {
979  $tableName = $this->addIdentifierQuotes( $tableName );
980  }
981 
982  # Quote $schema and $database and merge them with the table name if needed
983  $tableName = $this->prependDatabaseOrSchema( $schema, $tableName, $format );
984  $tableName = $this->prependDatabaseOrSchema( $database, $tableName, $format );
985 
986  return $tableName;
987  }
988 
995  public function qualifiedTableComponents( $name ) {
996  # We reverse the explode so that database.table and table both output the correct table.
997  $dbDetails = explode( '.', $name, 3 );
998  if ( $this->currentDomain ) {
999  $currentDomainPrefix = $this->currentDomain->getTablePrefix();
1000  } else {
1001  $currentDomainPrefix = null;
1002  }
1003  if ( count( $dbDetails ) == 3 ) {
1004  list( $database, $schema, $table ) = $dbDetails;
1005  # We don't want any prefix added in this case
1006  $prefix = '';
1007  } elseif ( count( $dbDetails ) == 2 ) {
1008  list( $database, $table ) = $dbDetails;
1009  # We don't want any prefix added in this case
1010  $prefix = '';
1011  # In dbs that support it, $database may actually be the schema
1012  # but that doesn't affect any of the functionality here
1013  $schema = '';
1014  } else {
1015  list( $table ) = $dbDetails;
1016  if ( isset( $this->tableAliases[$table] ) ) {
1017  $database = $this->tableAliases[$table]['dbname'];
1018  $schema = is_string( $this->tableAliases[$table]['schema'] )
1019  ? $this->tableAliases[$table]['schema']
1020  : $this->relationSchemaQualifier();
1021  $prefix = is_string( $this->tableAliases[$table]['prefix'] )
1022  ? $this->tableAliases[$table]['prefix']
1023  : $currentDomainPrefix;
1024  } else {
1025  $database = '';
1026  $schema = $this->relationSchemaQualifier(); # Default schema
1027  $prefix = $currentDomainPrefix; # Default prefix
1028  }
1029  }
1030 
1031  return [ $database, $schema, $prefix, $table ];
1032  }
1033 
1038  protected function relationSchemaQualifier() {
1039  if ( $this->currentDomain ) {
1040  return $this->currentDomain->getSchema();
1041  }
1042  return null;
1043  }
1044 
1051  private function prependDatabaseOrSchema( $namespace, $relation, $format ) {
1052  if ( $namespace !== null && $namespace !== '' ) {
1053  if ( $format === 'quoted' && !$this->isQuotedIdentifier( $namespace ) ) {
1054  $namespace = $this->addIdentifierQuotes( $namespace );
1055  }
1056  $relation = $namespace . '.' . $relation;
1057  }
1058 
1059  return $relation;
1060  }
1061 
1062  public function tableNames( ...$tables ) {
1063  $retVal = [];
1064 
1065  foreach ( $tables as $name ) {
1066  $retVal[$name] = $this->tableName( $name );
1067  }
1068 
1069  return $retVal;
1070  }
1071 
1072  public function tableNamesN( ...$tables ) {
1073  $retVal = [];
1074 
1075  foreach ( $tables as $name ) {
1076  $retVal[] = $this->tableName( $name );
1077  }
1078 
1079  return $retVal;
1080  }
1081 
1092  public function isQuotedIdentifier( $name ) {
1093  return $name[0] == '"' && substr( $name, -1, 1 ) == '"';
1094  }
1095 
1109  public function useIndexClause( $index ) {
1110  return '';
1111  }
1112 
1122  public function ignoreIndexClause( $index ) {
1123  return '';
1124  }
1125 
1137  protected function makeSelectOptions( array $options ) {
1138  $preLimitTail = $postLimitTail = '';
1139  $startOpts = '';
1140 
1141  $noKeyOptions = [];
1142 
1143  foreach ( $options as $key => $option ) {
1144  if ( is_numeric( $key ) ) {
1145  $noKeyOptions[$option] = true;
1146  }
1147  }
1148 
1149  $preLimitTail .= $this->makeGroupByWithHaving( $options );
1150 
1151  $preLimitTail .= $this->makeOrderBy( $options );
1152 
1153  if ( isset( $noKeyOptions['FOR UPDATE'] ) ) {
1154  $postLimitTail .= ' FOR UPDATE';
1155  }
1156 
1157  if ( isset( $noKeyOptions['LOCK IN SHARE MODE'] ) ) {
1158  $postLimitTail .= ' LOCK IN SHARE MODE';
1159  }
1160 
1161  if ( isset( $noKeyOptions['DISTINCT'] ) || isset( $noKeyOptions['DISTINCTROW'] ) ) {
1162  $startOpts .= 'DISTINCT';
1163  }
1164 
1165  # Various MySQL extensions
1166  if ( isset( $noKeyOptions['STRAIGHT_JOIN'] ) ) {
1167  $startOpts .= ' /*! STRAIGHT_JOIN */';
1168  }
1169 
1170  if ( isset( $noKeyOptions['SQL_BIG_RESULT'] ) ) {
1171  $startOpts .= ' SQL_BIG_RESULT';
1172  }
1173 
1174  if ( isset( $noKeyOptions['SQL_BUFFER_RESULT'] ) ) {
1175  $startOpts .= ' SQL_BUFFER_RESULT';
1176  }
1177 
1178  if ( isset( $noKeyOptions['SQL_SMALL_RESULT'] ) ) {
1179  $startOpts .= ' SQL_SMALL_RESULT';
1180  }
1181 
1182  if ( isset( $noKeyOptions['SQL_CALC_FOUND_ROWS'] ) ) {
1183  $startOpts .= ' SQL_CALC_FOUND_ROWS';
1184  }
1185 
1186  if ( isset( $options['USE INDEX'] ) && is_string( $options['USE INDEX'] ) ) {
1187  $useIndex = $this->useIndexClause( $options['USE INDEX'] );
1188  } else {
1189  $useIndex = '';
1190  }
1191  if ( isset( $options['IGNORE INDEX'] ) && is_string( $options['IGNORE INDEX'] ) ) {
1192  $ignoreIndex = $this->ignoreIndexClause( $options['IGNORE INDEX'] );
1193  } else {
1194  $ignoreIndex = '';
1195  }
1196 
1197  return [ $startOpts, $useIndex, $preLimitTail, $postLimitTail, $ignoreIndex ];
1198  }
1199 
1208  protected function makeGroupByWithHaving( $options ) {
1209  $sql = '';
1210  if ( isset( $options['GROUP BY'] ) ) {
1211  $gb = is_array( $options['GROUP BY'] )
1212  ? implode( ',', $options['GROUP BY'] )
1213  : $options['GROUP BY'];
1214  $sql .= ' GROUP BY ' . $gb;
1215  }
1216  if ( isset( $options['HAVING'] ) ) {
1217  $having = is_array( $options['HAVING'] )
1218  ? $this->makeList( $options['HAVING'], self::LIST_AND )
1219  : $options['HAVING'];
1220  $sql .= ' HAVING ' . $having;
1221  }
1222 
1223  return $sql;
1224  }
1225 
1234  protected function makeOrderBy( $options ) {
1235  if ( isset( $options['ORDER BY'] ) ) {
1236  $ob = is_array( $options['ORDER BY'] )
1237  ? implode( ',', $options['ORDER BY'] )
1238  : $options['ORDER BY'];
1239 
1240  return ' ORDER BY ' . $ob;
1241  }
1242 
1243  return '';
1244  }
1245 
1247  $table,
1248  $vars,
1249  array $permute_conds,
1250  $extra_conds = '',
1251  $fname = __METHOD__,
1252  $options = [],
1253  $join_conds = []
1254  ) {
1255  // First, build the Cartesian product of $permute_conds
1256  $conds = [ [] ];
1257  foreach ( $permute_conds as $field => $values ) {
1258  if ( !$values ) {
1259  // Skip empty $values
1260  continue;
1261  }
1262  $values = array_unique( $values );
1263  $newConds = [];
1264  foreach ( $conds as $cond ) {
1265  foreach ( $values as $value ) {
1266  $cond[$field] = $value;
1267  $newConds[] = $cond; // Arrays are by-value, not by-reference, so this works
1268  }
1269  }
1270  $conds = $newConds;
1271  }
1272 
1273  $extra_conds = $extra_conds === '' ? [] : (array)$extra_conds;
1274 
1275  // If there's just one condition and no subordering, hand off to
1276  // selectSQLText directly.
1277  if ( count( $conds ) === 1 &&
1278  ( !isset( $options['INNER ORDER BY'] ) || !$this->unionSupportsOrderAndLimit() )
1279  ) {
1280  return $this->selectSQLText(
1281  $table, $vars, $conds[0] + $extra_conds, $fname, $options, $join_conds
1282  );
1283  }
1284 
1285  // Otherwise, we need to pull out the order and limit to apply after
1286  // the union. Then build the SQL queries for each set of conditions in
1287  // $conds. Then union them together (using UNION ALL, because the
1288  // product *should* already be distinct).
1289  $orderBy = $this->makeOrderBy( $options );
1290  $limit = $options['LIMIT'] ?? null;
1291  $offset = $options['OFFSET'] ?? false;
1292  $all = empty( $options['NOTALL'] ) && !in_array( 'NOTALL', $options );
1293  if ( !$this->unionSupportsOrderAndLimit() ) {
1294  unset( $options['ORDER BY'], $options['LIMIT'], $options['OFFSET'] );
1295  } else {
1296  if ( array_key_exists( 'INNER ORDER BY', $options ) ) {
1297  $options['ORDER BY'] = $options['INNER ORDER BY'];
1298  }
1299  if ( $limit !== null && is_numeric( $offset ) && $offset != 0 ) {
1300  // We need to increase the limit by the offset rather than
1301  // using the offset directly, otherwise it'll skip incorrectly
1302  // in the subqueries.
1303  $options['LIMIT'] = $limit + $offset;
1304  unset( $options['OFFSET'] );
1305  }
1306  }
1307 
1308  $sqls = [];
1309  foreach ( $conds as $cond ) {
1310  $sqls[] = $this->selectSQLText(
1311  $table, $vars, $cond + $extra_conds, $fname, $options, $join_conds
1312  );
1313  }
1314  $sql = $this->unionQueries( $sqls, $all ) . $orderBy;
1315  if ( $limit !== null ) {
1316  $sql = $this->limitResult( $sql, $limit, $offset );
1317  }
1318 
1319  return $sql;
1320  }
1321 
1326  public function buildGroupConcatField(
1327  $delim, $table, $field, $conds = '', $join_conds = []
1328  ) {
1329  $fld = "GROUP_CONCAT($field SEPARATOR " . $this->quoter->addQuotes( $delim ) . ')';
1330 
1331  return '(' . $this->selectSQLText( $table, $fld, $conds, __METHOD__, [], $join_conds ) . ')';
1332  }
1333 
1334  public function buildSelectSubquery(
1335  $table, $vars, $conds = '', $fname = __METHOD__,
1336  $options = [], $join_conds = []
1337  ) {
1338  return new Subquery(
1339  $this->selectSQLText( $table, $vars, $conds, $fname, $options, $join_conds )
1340  );
1341  }
1342 
1343  public function insertSqlText( $table, array $rows ) {
1344  $encTable = $this->tableName( $table );
1345  list( $sqlColumns, $sqlTuples ) = $this->makeInsertLists( $rows );
1346 
1347  return "INSERT INTO $encTable ($sqlColumns) VALUES $sqlTuples";
1348  }
1349 
1361  public function makeInsertLists( array $rows, $aliasPrefix = '' ) {
1362  $firstRow = $rows[0];
1363  if ( !is_array( $firstRow ) || !$firstRow ) {
1364  throw new DBLanguageError( 'Got an empty row list or empty row' );
1365  }
1366  // List of columns that define the value tuple ordering
1367  $tupleColumns = array_keys( $firstRow );
1368 
1369  $valueTuples = [];
1370  foreach ( $rows as $row ) {
1371  $rowColumns = array_keys( $row );
1372  // VALUES(...) requires a uniform correspondence of (column => value)
1373  if ( $rowColumns !== $tupleColumns ) {
1374  throw new DBLanguageError(
1375  'Got row columns (' . implode( ', ', $rowColumns ) . ') ' .
1376  'instead of expected (' . implode( ', ', $tupleColumns ) . ')'
1377  );
1378  }
1379  // Make the value tuple that defines this row
1380  $valueTuples[] = '(' . $this->makeList( $row, self::LIST_COMMA ) . ')';
1381  }
1382 
1383  $magicAliasFields = [];
1384  foreach ( $tupleColumns as $column ) {
1385  $magicAliasFields[] = $aliasPrefix . $column;
1386  }
1387 
1388  return [
1389  $this->makeList( $tupleColumns, self::LIST_NAMES ),
1390  implode( ',', $valueTuples ),
1391  $this->makeList( $magicAliasFields, self::LIST_NAMES )
1392  ];
1393  }
1394 
1395  public function insertNonConflictingSqlText( $table, array $rows ) {
1396  $encTable = $this->tableName( $table );
1397  list( $sqlColumns, $sqlTuples ) = $this->makeInsertLists( $rows );
1398  list( $sqlVerb, $sqlOpts ) = $this->makeInsertNonConflictingVerbAndOptions();
1399 
1400  return rtrim( "$sqlVerb $encTable ($sqlColumns) VALUES $sqlTuples $sqlOpts" );
1401  }
1402 
1409  return [ 'INSERT IGNORE INTO', '' ];
1410  }
1411 
1412  public function insertSelectNativeSqlText(
1413  $destTable,
1414  $srcTable,
1415  array $varMap,
1416  $conds,
1417  $fname,
1418  array $insertOptions,
1419  array $selectOptions,
1420  $selectJoinConds
1421  ) {
1422  list( $sqlVerb, $sqlOpts ) = $this->isFlagInOptions( 'IGNORE', $insertOptions )
1424  : [ 'INSERT INTO', '' ];
1425  $encDstTable = $this->tableName( $destTable );
1426  $sqlDstColumns = implode( ',', array_keys( $varMap ) );
1427  $selectSql = $this->selectSQLText(
1428  $srcTable,
1429  array_values( $varMap ),
1430  $conds,
1431  $fname,
1432  $selectOptions,
1433  $selectJoinConds
1434  );
1435 
1436  return rtrim( "$sqlVerb $encDstTable ($sqlDstColumns) $selectSql $sqlOpts" );
1437  }
1438 
1445  public function isFlagInOptions( $option, array $options ) {
1446  foreach ( array_keys( $options, $option, true ) as $k ) {
1447  if ( is_int( $k ) ) {
1448  return true;
1449  }
1450  }
1451 
1452  return false;
1453  }
1454 
1462  public function makeKeyCollisionCondition( array $rows, array $uniqueKey ) {
1463  if ( !$rows ) {
1464  throw new DBLanguageError( "Empty row array" );
1465  } elseif ( !$uniqueKey ) {
1466  throw new DBLanguageError( "Empty unique key array" );
1467  }
1468 
1469  if ( count( $uniqueKey ) == 1 ) {
1470  // Use a simple IN(...) clause
1471  $column = reset( $uniqueKey );
1472  $values = array_column( $rows, $column );
1473  if ( count( $values ) !== count( $rows ) ) {
1474  throw new DBLanguageError( "Missing values for unique key ($column)" );
1475  }
1476 
1477  return $this->makeList( [ $column => $values ], self::LIST_AND );
1478  }
1479 
1480  $nullByUniqueKeyColumn = array_fill_keys( $uniqueKey, null );
1481 
1482  $orConds = [];
1483  foreach ( $rows as $row ) {
1484  $rowKeyMap = array_intersect_key( $row, $nullByUniqueKeyColumn );
1485  if ( count( $rowKeyMap ) != count( $uniqueKey ) ) {
1486  throw new DBLanguageError(
1487  "Missing values for unique key (" . implode( ',', $uniqueKey ) . ")"
1488  );
1489  }
1490  $orConds[] = $this->makeList( $rowKeyMap, self::LIST_AND );
1491  }
1492 
1493  return count( $orConds ) > 1
1494  ? $this->makeList( $orConds, self::LIST_OR )
1495  : $orConds[0];
1496  }
1497 
1498  public function deleteJoinSqlText( $delTable, $joinTable, $delVar, $joinVar, $conds ) {
1499  if ( !$conds ) {
1500  throw new DBLanguageError( __METHOD__ . ' called with empty $conds' );
1501  }
1502 
1503  $delTable = $this->tableName( $delTable );
1504  $joinTable = $this->tableName( $joinTable );
1505  $sql = "DELETE FROM $delTable WHERE $delVar IN (SELECT $joinVar FROM $joinTable ";
1506  if ( $conds != '*' ) {
1507  $sql .= 'WHERE ' . $this->makeList( $conds, self::LIST_AND );
1508  }
1509  $sql .= ')';
1510 
1511  return $sql;
1512  }
1513 
1514  public function deleteSqlText( $table, $conds ) {
1515  $this->assertConditionIsNotEmpty( $conds, __METHOD__, false );
1516 
1517  $table = $this->tableName( $table );
1518  $sql = "DELETE FROM $table";
1519 
1520  if ( $conds !== self::ALL_ROWS ) {
1521  if ( is_array( $conds ) ) {
1522  $conds = $this->makeList( $conds, self::LIST_AND );
1523  }
1524  $sql .= ' WHERE ' . $conds;
1525  }
1526 
1527  return $sql;
1528  }
1529 
1530  public function updateSqlText( $table, $set, $conds, $options ) {
1531  $this->assertConditionIsNotEmpty( $conds, __METHOD__, true );
1532  $table = $this->tableName( $table );
1533  $opts = $this->makeUpdateOptions( $options );
1534  $sql = "UPDATE $opts $table SET " . $this->makeList( $set, self::LIST_SET );
1535 
1536  if ( $conds && $conds !== self::ALL_ROWS ) {
1537  if ( is_array( $conds ) ) {
1538  $conds = $this->makeList( $conds, self::LIST_AND );
1539  }
1540  $sql .= ' WHERE ' . $conds;
1541  }
1542 
1543  return $sql;
1544  }
1545 
1559  protected function assertConditionIsNotEmpty( $conds, string $fname, bool $deprecate ) {
1560  $isCondValid = ( is_string( $conds ) || is_array( $conds ) ) && $conds;
1561  if ( !$isCondValid ) {
1562  if ( $deprecate ) {
1563  wfDeprecated( $fname . ' called with empty $conds', '1.35', false, 4 );
1564  } else {
1565  throw new DBLanguageError( $fname . ' called with empty conditions' );
1566  }
1567  }
1568  }
1569 
1577  protected function makeUpdateOptions( $options ) {
1578  $opts = $this->makeUpdateOptionsArray( $options );
1579 
1580  return implode( ' ', $opts );
1581  }
1582 
1590  protected function makeUpdateOptionsArray( $options ) {
1591  $options = $this->normalizeOptions( $options );
1592 
1593  $opts = [];
1594 
1595  if ( in_array( 'IGNORE', $options ) ) {
1596  $opts[] = 'IGNORE';
1597  }
1598 
1599  return $opts;
1600  }
1601 
1607  final public function normalizeOptions( $options ) {
1608  if ( is_array( $options ) ) {
1609  return $options;
1610  } elseif ( is_string( $options ) ) {
1611  return ( $options === '' ) ? [] : [ $options ];
1612  } else {
1613  throw new DBLanguageError( __METHOD__ . ': expected string or array' );
1614  }
1615  }
1616 
1617  public function dropTableSqlText( $table ) {
1618  // https://mariadb.com/kb/en/drop-table/
1619  // https://dev.mysql.com/doc/refman/8.0/en/drop-table.html
1620  // https://www.postgresql.org/docs/9.2/sql-truncate.html
1621  return "DROP TABLE " . $this->tableName( $table ) . " CASCADE";
1622  }
1623 
1628  public function getQueryVerb( $sql ) {
1629  // Distinguish ROLLBACK from ROLLBACK TO SAVEPOINT
1630  return preg_match(
1631  '/^\s*(rollback\s+to\s+savepoint|[a-z]+)/i',
1632  $sql,
1633  $m
1634  ) ? strtoupper( $m[1] ) : null;
1635  }
1636 
1651  public function isTransactableQuery( $sql ) {
1652  return !in_array(
1653  $this->getQueryVerb( $sql ),
1654  [
1655  'BEGIN',
1656  'ROLLBACK',
1657  'ROLLBACK TO SAVEPOINT',
1658  'COMMIT',
1659  'SET',
1660  'SHOW',
1661  'CREATE',
1662  'ALTER',
1663  'USE',
1664  'SHOW'
1665  ],
1666  true
1667  );
1668  }
1669 
1687  public function isWriteQuery( $sql, $flags ) {
1688  // Check if a SQL wrapper method already flagged the query as a write
1689  if (
1690  $this->fieldHasBit( $flags, self::QUERY_CHANGE_ROWS ) ||
1691  $this->fieldHasBit( $flags, self::QUERY_CHANGE_SCHEMA )
1692  ) {
1693  return true;
1694  }
1695  // Check if a SQL wrapper method already flagged the query as a non-write
1696  if (
1697  $this->fieldHasBit( $flags, self::QUERY_CHANGE_NONE ) ||
1698  $this->fieldHasBit( $flags, self::QUERY_CHANGE_TRX ) ||
1699  $this->fieldHasBit( $flags, self::QUERY_CHANGE_LOCKS )
1700  ) {
1701  return false;
1702  }
1703  // Treat SELECT queries without FOR UPDATE queries as non-writes. This matches
1704  // how MySQL enforces read_only (FOR SHARE and LOCK IN SHADE MODE are allowed).
1705  // Handle (SELECT ...) UNION (SELECT ...) queries in a similar fashion.
1706  if ( preg_match( '/^\s*\‍(?SELECT\b/i', $sql ) ) {
1707  return (bool)preg_match( '/\bFOR\s+UPDATE\‍)?\s*$/i', $sql );
1708  }
1709  // BEGIN and COMMIT queries are considered non-write queries here.
1710  // Database backends and drivers (MySQL, MariaDB, php-mysqli) generally
1711  // treat these as write queries, in that their results have "affected rows"
1712  // as meta data as from writes, instead of "num rows" as from reads.
1713  // But, we treat them as non-write queries because when reading data (from
1714  // either replica or primary DB) we use transactions to enable repeatable-read
1715  // snapshots, which ensures we get consistent results from the same snapshot
1716  // for all queries within a request. Use cases:
1717  // - Treating these as writes would trigger ChronologyProtector (see method doc).
1718  // - We use this method to reject writes to replicas, but we need to allow
1719  // use of transactions on replicas for read snapshots. This is fine given
1720  // that transactions by themselves don't make changes, only actual writes
1721  // within the transaction matter, which we still detect.
1722  return !preg_match(
1723  '/^\s*(BEGIN|ROLLBACK|COMMIT|SAVEPOINT|RELEASE|SET|SHOW|EXPLAIN|USE)\b/i',
1724  $sql
1725  );
1726  }
1727 
1733  final protected function fieldHasBit( int $flags, int $bit ) {
1734  return ( ( $flags & $bit ) === $bit );
1735  }
1736 
1737  public function buildExcludedValue( $column ) {
1738  /* @see Database::doUpsert() */
1739  // This can be treated like a single value since __VALS is a single row table
1740  return "(SELECT __$column FROM __VALS)";
1741  }
1742 
1743  public function savepointSqlText( $identifier ) {
1744  return 'SAVEPOINT ' . $this->addIdentifierQuotes( $identifier );
1745  }
1746 
1747  public function releaseSavepointSqlText( $identifier ) {
1748  return 'RELEASE SAVEPOINT ' . $this->addIdentifierQuotes( $identifier );
1749  }
1750 
1751  public function rollbackToSavepointSqlText( $identifier ) {
1752  return 'ROLLBACK TO SAVEPOINT ' . $this->addIdentifierQuotes( $identifier );
1753  }
1754 
1755  public function rollbackSqlText() {
1756  return 'ROLLBACK';
1757  }
1758 
1759  public function dispatchingInsertSqlText( $table, $rows, $options ) {
1760  $rows = $this->normalizeRowArray( $rows );
1761  if ( !$rows ) {
1762  return false;
1763  }
1764 
1765  $options = $this->normalizeOptions( $options );
1766  if ( $this->isFlagInOptions( 'IGNORE', $options ) ) {
1767  return $this->insertNonConflictingSqlText( $table, $rows );
1768  } else {
1769  return $this->insertSqlText( $table, $rows );
1770  }
1771  }
1772 
1778  final protected function normalizeRowArray( array $rowOrRows ) {
1779  if ( !$rowOrRows ) {
1780  $rows = [];
1781  } elseif ( isset( $rowOrRows[0] ) ) {
1782  $rows = $rowOrRows;
1783  } else {
1784  $rows = [ $rowOrRows ];
1785  }
1786 
1787  foreach ( $rows as $row ) {
1788  if ( !is_array( $row ) ) {
1789  throw new DBLanguageError( "Got non-array in row array" );
1790  } elseif ( !$row ) {
1791  throw new DBLanguageError( "Got empty array in row array" );
1792  }
1793  }
1794 
1795  return $rows;
1796  }
1797 
1807  final public function normalizeUpsertParams( $uniqueKeys, &$rows ) {
1808  $rows = $this->normalizeRowArray( $rows );
1809  if ( !$rows ) {
1810  return null;
1811  }
1812  if ( !$uniqueKeys ) {
1813  // For backwards compatibility, allow insertion of rows with no applicable key
1814  $this->logger->warning(
1815  "upsert/replace called with no unique key",
1816  [
1817  'exception' => new RuntimeException(),
1818  'db_log_category' => 'sql',
1819  ]
1820  );
1821  return null;
1822  }
1823  $identityKey = $this->normalizeUpsertKeys( $uniqueKeys );
1824  if ( $identityKey ) {
1825  $allDefaultKeyValues = $this->assertValidUpsertRowArray( $rows, $identityKey );
1826  if ( $allDefaultKeyValues ) {
1827  // For backwards compatibility, allow insertion of rows with all-NULL
1828  // values for the unique columns (e.g. for an AUTOINCREMENT column)
1829  $this->logger->warning(
1830  "upsert/replace called with all-null values for unique key",
1831  [
1832  'exception' => new RuntimeException(),
1833  'db_log_category' => 'sql',
1834  ]
1835  );
1836  return null;
1837  }
1838  }
1839  return $identityKey;
1840  }
1841 
1848  final public function normalizeConditions( $conds, $fname ) {
1849  if ( $conds === null || $conds === false ) {
1850  $this->logger->warning(
1851  __METHOD__
1852  . ' called from '
1853  . $fname
1854  . ' with incorrect parameters: $conds must be a string or an array',
1855  [ 'db_log_category' => 'sql' ]
1856  );
1857  return [];
1858  } elseif ( $conds === '' ) {
1859  return [];
1860  }
1861 
1862  return is_array( $conds ) ? $conds : [ $conds ];
1863  }
1864 
1871  private function normalizeUpsertKeys( $uniqueKeys ) {
1872  if ( is_string( $uniqueKeys ) ) {
1873  return [ $uniqueKeys ];
1874  } elseif ( !is_array( $uniqueKeys ) ) {
1875  throw new DBLanguageError( 'Invalid unique key array' );
1876  } else {
1877  if ( count( $uniqueKeys ) !== 1 || !isset( $uniqueKeys[0] ) ) {
1878  throw new DBLanguageError(
1879  "The unique key array should contain a single unique index" );
1880  }
1881 
1882  $uniqueKey = $uniqueKeys[0];
1883  if ( is_string( $uniqueKey ) ) {
1884  // Passing a list of strings for single-column unique keys is too
1885  // easily confused with passing the columns of composite unique key
1886  $this->logger->warning( __METHOD__ .
1887  " called with deprecated parameter style: " .
1888  "the unique key array should be a string or array of string arrays",
1889  [
1890  'exception' => new RuntimeException(),
1891  'db_log_category' => 'sql',
1892  ] );
1893  return $uniqueKeys;
1894  } elseif ( is_array( $uniqueKey ) ) {
1895  return $uniqueKey;
1896  } else {
1897  throw new DBLanguageError( 'Invalid unique key array entry' );
1898  }
1899  }
1900  }
1901 
1908  final protected function assertValidUpsertRowArray( array $rows, array $identityKey ) {
1909  $numNulls = 0;
1910  foreach ( $rows as $row ) {
1911  foreach ( $identityKey as $column ) {
1912  $numNulls += ( isset( $row[$column] ) ? 0 : 1 );
1913  }
1914  }
1915 
1916  if (
1917  $numNulls &&
1918  $numNulls !== ( count( $rows ) * count( $identityKey ) )
1919  ) {
1920  throw new DBLanguageError(
1921  "NULL/absent values for unique key (" . implode( ',', $identityKey ) . ")"
1922  );
1923  }
1924 
1925  return (bool)$numNulls;
1926  }
1927 
1934  final public function assertValidUpsertSetArray(
1935  array $set,
1936  array $identityKey,
1937  array $rows
1938  ) {
1939  // Sloppy callers might construct the SET array using the ROW array, leaving redundant
1940  // column definitions for identity key columns. Detect this for backwards compatibility.
1941  $soleRow = ( count( $rows ) == 1 ) ? reset( $rows ) : null;
1942  // Disallow value changes for any columns in the identity key. This avoids additional
1943  // insertion order dependencies that are unwieldy and difficult to implement efficiently
1944  // in PostgreSQL.
1945  foreach ( $set as $k => $v ) {
1946  if ( is_string( $k ) ) {
1947  // Key is a column name and value is a literal (e.g. string, int, null, ...)
1948  if ( in_array( $k, $identityKey, true ) ) {
1949  if ( $soleRow && array_key_exists( $k, $soleRow ) && $soleRow[$k] === $v ) {
1950  $this->logger->warning(
1951  __METHOD__ . " called with redundant assignment to column '$k'",
1952  [
1953  'exception' => new RuntimeException(),
1954  'db_log_category' => 'sql',
1955  ]
1956  );
1957  } else {
1958  throw new DBLanguageError(
1959  "Cannot reassign column '$k' since it belongs to identity key"
1960  );
1961  }
1962  }
1963  } elseif ( preg_match( '/^([a-zA-Z0-9_]+)\s*=/', $v, $m ) ) {
1964  // Value is of the form "<unquoted alphanumeric column> = <SQL expression>"
1965  if ( in_array( $m[1], $identityKey, true ) ) {
1966  throw new DBLanguageError(
1967  "Cannot reassign column '{$m[1]}' since it belongs to identity key"
1968  );
1969  }
1970  }
1971  }
1972  }
1973 
1978  final public function extractSingleFieldFromList( $var ) {
1979  if ( is_array( $var ) ) {
1980  if ( !$var ) {
1981  $column = null;
1982  } elseif ( count( $var ) == 1 ) {
1983  $column = $var[0] ?? reset( $var );
1984  } else {
1985  throw new DBLanguageError( __METHOD__ . ': got multiple columns' );
1986  }
1987  } else {
1988  $column = $var;
1989  }
1990 
1991  return $column;
1992  }
1993 
1994  public function setSchemaVars( $vars ) {
1995  $this->schemaVars = is_array( $vars ) ? $vars : null;
1996  }
1997 
2004  protected function getSchemaVars() {
2005  return $this->schemaVars ?? $this->getDefaultSchemaVars();
2006  }
2007 
2017  protected function getDefaultSchemaVars() {
2018  return [];
2019  }
2020 
2042  public function replaceVars( $ins ) {
2043  $vars = $this->getSchemaVars();
2044  return preg_replace_callback(
2045  '!
2046  /\* (\$wgDBprefix|[_i]) \*/ (\w*) | # 1-2. tableName, indexName
2047  \'\{\$ (\w+) }\' | # 3. addQuotes
2048  `\{\$ (\w+) }` | # 4. addIdentifierQuotes
2049  /\*\$ (\w+) \*/ # 5. leave unencoded
2050  !x',
2051  function ( $m ) use ( $vars ) {
2052  // Note: Because of <https://bugs.php.net/bug.php?id=51881>,
2053  // check for both nonexistent keys *and* the empty string.
2054  if ( isset( $m[1] ) && $m[1] !== '' ) {
2055  if ( $m[1] === 'i' ) {
2056  return $this->indexName( $m[2] );
2057  } else {
2058  return $this->tableName( $m[2] );
2059  }
2060  } elseif ( isset( $m[3] ) && $m[3] !== '' && array_key_exists( $m[3], $vars ) ) {
2061  return $this->quoter->addQuotes( $vars[$m[3]] );
2062  } elseif ( isset( $m[4] ) && $m[4] !== '' && array_key_exists( $m[4], $vars ) ) {
2063  return $this->addIdentifierQuotes( $vars[$m[4]] );
2064  } elseif ( isset( $m[5] ) && $m[5] !== '' && array_key_exists( $m[5], $vars ) ) {
2065  return $vars[$m[5]];
2066  } else {
2067  return $m[0];
2068  }
2069  },
2070  $ins
2071  );
2072  }
2073 
2074  public function lockSQLText( $lockName, $timeout ) {
2075  throw new RuntimeException( 'locking must be implemented in subclasses' );
2076  }
2077 
2078  public function lockIsFreeSQLText( $lockName ) {
2079  throw new RuntimeException( 'locking must be implemented in subclasses' );
2080  }
2081 
2082  public function unlockSQLText( $lockName ) {
2083  throw new RuntimeException( 'locking must be implemented in subclasses' );
2084  }
2085 }
const LIST_NAMES
Definition: Defines.php:45
const LIST_COMMA
Definition: Defines.php:42
const LIST_SET
Definition: Defines.php:44
const LIST_OR
Definition: Defines.php:46
const LIST_AND
Definition: Defines.php:43
wfDeprecated( $function, $version=false, $component=false, $callerOffset=2)
Logs a warning that a deprecated feature was used.
Class to handle database/schema/prefix specifications for IDatabase.
Used by Database::buildLike() to represent characters that have special meaning in SQL LIKE clauses a...
Definition: LikeMatch.php:10
DatabaseDomain null $currentDomain
Definition: SQLPlatform.php:47
decodeExpiry( $expiry, $format=TS_MW)
Decode an expiry time into a DBMS independent format.
anyChar()
Returns a token for buildLike() that denotes a '_' to be used in a LIKE query.
buildConcat( $stringList)
Build a concatenation list to feed into a SQL query.Raw SQL expression list; caller is responsible fo...
insertSelectNativeSqlText( $destTable, $srcTable, array $varMap, $conds, $fname, array $insertOptions, array $selectOptions, $selectJoinConds)
makeInsertLists(array $rows, $aliasPrefix='')
Make SQL lists of columns, row tuples, and column aliases for INSERT/VALUES expressions.
getInfinity()
Find out when 'infinity' is.Most DBMSes support this. This is a special keyword for timestamps in Pos...
insertSqlText( $table, array $rows)
buildGreatest( $fields, $values)
Build a GREATEST function statement comparing columns/values.Integer and float values in $values will...
makeGroupByWithHaving( $options)
Returns an optional GROUP BY with an optional HAVING.
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...
strreplace( $orig, $old, $new)
Returns a SQL expression for simple string replacement (e.g.REPLACE() in mysql)Column to modify Colum...
replaceVars( $ins)
Database-independent variable replacement.
buildStringCast( $field)
Field or column to cast string 1.28 in IDatabase, moved to ISQLPlatform in 1.39
normalizeUpsertParams( $uniqueKeys, &$rows)
Validate and normalize parameters to upsert() or replace()
setIndexAliases(array $aliases)
Convert certain index names to alternative names before querying the DB.Note that this applies to ind...
qualifiedTableComponents( $name)
Get the table components needed for a query given the currently selected database.
makeKeyCollisionCondition(array $rows, array $uniqueKey)
Build an SQL condition to find rows with matching key values to those in $rows.
array null $schemaVars
Current variables use for schema element placeholders.
Definition: SQLPlatform.php:49
ignoreIndexClause( $index)
IGNORE INDEX clause.
makeOrderBy( $options)
Returns an optional ORDER BY.
string[] $indexAliases
Current map of (index alias => index)
Definition: SQLPlatform.php:45
buildLike( $param,... $params)
LIKE statement wrapper.This takes a variable-length argument list with parts of pattern to match cont...
bitOr( $fieldLeft, $fieldRight)
string
Definition: SQLPlatform.php:85
fieldNameWithAlias( $name, $alias=false)
Get an aliased field name e.g.
limitResult( $sql, $limit, $offset=false)
Construct a LIMIT query with optional offset.The SQL should be adjusted so that only the first $limit...
unionSupportsOrderAndLimit()
Determine if the RDBMS supports ORDER BY and LIMIT for separate subqueries within UNION....
buildExcludedValue( $column)
Build a reference to a column value from the conflicting proposed upsert() row.
timestamp( $ts=0)
Convert a timestamp in one of the formats accepted by ConvertibleTimestamp to the format used for ins...
buildSuperlative( $sqlfunc, $fields, $values)
Build a superlative function statement comparing columns/values.
tableNames(... $tables)
Fetch a number of table names into an associative array.
makeUpdateOptions( $options)
Make UPDATE options for the Database::update function.
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...
bitAnd( $fieldLeft, $fieldRight)
string
Definition: SQLPlatform.php:77
array[] $tableAliases
Current map of (table => (dbname, schema, prefix) map)
Definition: SQLPlatform.php:43
normalizeJoinType(string $joinType)
Validate and normalize a join type.
indexName( $index)
Allows for index remapping in queries where this is not consistent across DBMS.
buildSubstring( $input, $startPosition, $length=null)
unionConditionPermutations( $table, $vars, array $permute_conds, $extra_conds='', $fname=__METHOD__, $options=[], $join_conds=[])
Construct a UNION query for permutations of conditions.
assertValidUpsertRowArray(array $rows, array $identityKey)
getDefaultSchemaVars()
Get schema variables to use if none have been set via setSchemaVars().
__construct(DbQuoter $quoter, LoggerInterface $logger=null, DatabaseDomain $currentDomain=null)
Definition: SQLPlatform.php:55
factorConds( $condsArray)
Given an array of condition arrays representing an OR list of AND lists, for example:
dispatchingInsertSqlText( $table, $rows, $options)
updateSqlText( $table, $set, $conds, $options)
assertValidUpsertSetArray(array $set, array $identityKey, array $rows)
implicitOrderby()
Returns true if this database does an implicit order by when the column has an index For example: SEL...
anyString()
Returns a token for buildLike() that denotes a '' to be used in a LIKE query.
makeWhereFrom2d( $data, $baseKey, $subKey)
Build a partial where clause from a 2-d array such as used for LinkBatch.
isTransactableQuery( $sql)
Determine whether a SQL statement is sensitive to isolation level.
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...
deleteJoinSqlText( $delTable, $joinTable, $delVar, $joinVar, $conds)
buildLeast( $fields, $values)
Build a LEAST function statement comparing columns/values.Integer and float values in $values will no...
useIndexClause( $index)
USE INDEX clause.
encodeExpiry( $expiry)
Encode an expiry time into the DBMS dependent format.
setTableAliases(array $aliases)
Make certain table names use their own database, schema, and table prefix when passed into SQL querie...
isFlagInOptions( $option, array $options)
conditional( $cond, $caseTrueExpression, $caseFalseExpression)
Returns an SQL expression for a simple conditional.This doesn't need to be overridden unless CASE isn...
setSchemaVars( $vars)
Set schema variables to be used when streaming commands from SQL files or stdin.
setCurrentDomain(DatabaseDomain $currentDomain)
isWriteQuery( $sql, $flags)
Determine whether a query writes to the DB.
assertConditionIsNotEmpty( $conds, string $fname, bool $deprecate)
Check type and bounds conditions parameters for update.
timestampOrNull( $ts=null)
Convert a timestamp in one of the formats accepted by ConvertibleTimestamp to the format used for ins...
isQuotedIdentifier( $name)
Returns if the given identifier looks quoted or not according to the database convention for quoting ...
getSchemaVars()
Get schema variables.
makeList(array $a, $mode=self::LIST_COMMA)
Makes an encoded list of strings from an array.
insertNonConflictingSqlText( $table, array $rows)
tableName( $name, $format='quoted')
Format a table name ready for use in constructing an SQL query.This does two important things: it quo...
addIdentifierQuotes( $s)
Escape a SQL identifier (e.g.table, column, database) for use in a SQL queryDepending on the database...
Definition: SQLPlatform.php:93
escapeLikeInternal( $s, $escapeChar='`')
fieldNamesWithAlias( $fields)
Gets an array of aliased field names.
buildSelectSubquery( $table, $vars, $conds='', $fname=__METHOD__, $options=[], $join_conds=[])
Equivalent to IDatabase::selectSQLText() except wraps the result in Subquery.
buildIntegerCast( $field)
Field or column to cast string 1.31 in IDatabase, moved to ISQLPlatform in 1.39
tableNamesN(... $tables)
Fetch a number of table names into a zero-indexed numerical array.
makeUpdateOptionsArray( $options)
Make UPDATE options array for Database::makeUpdateOptions.
tableNameWithAlias( $table, $alias=false)
Get an aliased table name.
tableNamesWithIndexClauseOrJOIN( $tables, $use_index=[], $ignore_index=[], $join_conds=[])
Get the aliased table name clause for a FROM clause which might have a JOIN and/or USE INDEX or IGNOR...
unionQueries( $sqls, $all)
Construct a UNION query.This is used for providing overload point for other DB abstractions not compa...
Interface for query language.
foreach( $mmfl['setupFiles'] as $fileName) if( $queue) if(empty( $mmfl['quiet'])) $s