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 Throwable;
27 use Wikimedia\Assert\Assert;
33 use Wikimedia\Timestamp\ConvertibleTimestamp;
34 
42 class SQLPlatform implements ISQLPlatform {
44  protected $tableAliases = [];
46  protected $indexAliases = [];
48  protected $currentDomain;
50  protected $schemaVars;
52  protected $quoter;
54  protected $logger;
56  protected $errorLogger;
57 
58  public function __construct(
60  LoggerInterface $logger = null,
62  $errorLogger = null
63 
64  ) {
65  $this->quoter = $quoter;
66  $this->logger = $logger ?? new NullLogger();
67  $this->currentDomain = $currentDomain;
68  $this->errorLogger = $errorLogger ?? static function ( Throwable $e ) {
69  trigger_error( get_class( $e ) . ': ' . $e->getMessage(), E_USER_WARNING );
70  };
71  }
72 
77  public function bitNot( $field ) {
78  return "(~$field)";
79  }
80 
85  public function bitAnd( $fieldLeft, $fieldRight ) {
86  return "($fieldLeft & $fieldRight)";
87  }
88 
93  public function bitOr( $fieldLeft, $fieldRight ) {
94  return "($fieldLeft | $fieldRight)";
95  }
96 
101  public function addIdentifierQuotes( $s ) {
102  return '"' . str_replace( '"', '""', $s ) . '"';
103  }
104 
108  public function buildGreatest( $fields, $values ) {
109  return $this->buildSuperlative( 'GREATEST', $fields, $values );
110  }
111 
115  public function buildLeast( $fields, $values ) {
116  return $this->buildSuperlative( 'LEAST', $fields, $values );
117  }
118 
133  protected function buildSuperlative( $sqlfunc, $fields, $values ) {
134  $fields = is_array( $fields ) ? $fields : [ $fields ];
135  $values = is_array( $values ) ? $values : [ $values ];
136 
137  $encValues = [];
138  foreach ( $fields as $alias => $field ) {
139  if ( is_int( $alias ) ) {
140  $encValues[] = $this->addIdentifierQuotes( $field );
141  } else {
142  $encValues[] = $field; // expression
143  }
144  }
145  foreach ( $values as $value ) {
146  if ( is_int( $value ) || is_float( $value ) ) {
147  $encValues[] = $value;
148  } elseif ( is_string( $value ) ) {
149  $encValues[] = $this->quoter->addQuotes( $value );
150  } elseif ( $value === null ) {
151  throw new DBLanguageError( 'Null value in superlative' );
152  } else {
153  throw new DBLanguageError( 'Unexpected value type in superlative' );
154  }
155  }
156 
157  return $sqlfunc . '(' . implode( ',', $encValues ) . ')';
158  }
159 
160  public function buildComparison( string $op, array $conds ): string {
161  if ( !in_array( $op, [ '>', '>=', '<', '<=' ] ) ) {
162  throw new InvalidArgumentException( "Comparison operator must be one of '>', '>=', '<', '<='" );
163  }
164  if ( count( $conds ) === 0 ) {
165  throw new InvalidArgumentException( "Empty input" );
166  }
167 
168  // Construct a condition string by starting with the least significant part of the index, and
169  // adding more significant parts progressively to the left of the string.
170  //
171  // For example, given $conds = [ 'a' => 4, 'b' => 7, 'c' => 1 ], this will generate a condition
172  // like this:
173  //
174  // WHERE a > 4
175  // OR (a = 4 AND (b > 7
176  // OR (b = 7 AND (c > 1))))
177  //
178  // …which is equivalent to the following, which might be easier to understand:
179  //
180  // WHERE a > 4
181  // OR a = 4 AND b > 7
182  // OR a = 4 AND b = 7 AND c > 1
183  //
184  // …and also equivalent to the following, using tuple comparison syntax, which is most intuitive
185  // but apparently performs worse:
186  //
187  // WHERE (a, b, c) > (4, 7, 1)
188 
189  $sql = '';
190  foreach ( array_reverse( $conds ) as $field => $value ) {
191  $encValue = $this->quoter->addQuotes( $value );
192  if ( $sql === '' ) {
193  $sql = "$field $op $encValue";
194  // Change '>=' to '>' etc. for remaining fields, as the equality is handled separately
195  $op = rtrim( $op, '=' );
196  } else {
197  $sql = "$field $op $encValue OR ($field = $encValue AND ($sql))";
198  }
199  }
200  return $sql;
201  }
202 
203  public function makeList( array $a, $mode = self::LIST_COMMA ) {
204  $first = true;
205  $list = '';
206 
207  foreach ( $a as $field => $value ) {
208  if ( $first ) {
209  $first = false;
210  } else {
211  if ( $mode == self::LIST_AND ) {
212  $list .= ' AND ';
213  } elseif ( $mode == self::LIST_OR ) {
214  $list .= ' OR ';
215  } else {
216  $list .= ',';
217  }
218  }
219 
220  if ( ( $mode == self::LIST_AND || $mode == self::LIST_OR ) && is_numeric( $field ) ) {
221  $list .= "($value)";
222  } elseif ( $mode == self::LIST_SET && is_numeric( $field ) ) {
223  $list .= "$value";
224  } elseif (
225  ( $mode == self::LIST_AND || $mode == self::LIST_OR ) && is_array( $value )
226  ) {
227  // Remove null from array to be handled separately if found
228  $includeNull = false;
229  foreach ( array_keys( $value, null, true ) as $nullKey ) {
230  $includeNull = true;
231  unset( $value[$nullKey] );
232  }
233  if ( count( $value ) == 0 && !$includeNull ) {
234  throw new InvalidArgumentException(
235  __METHOD__ . ": empty input for field $field" );
236  } elseif ( count( $value ) == 0 ) {
237  // only check if $field is null
238  $list .= "$field IS NULL";
239  } else {
240  // IN clause contains at least one valid element
241  if ( $includeNull ) {
242  // Group subconditions to ensure correct precedence
243  $list .= '(';
244  }
245  if ( count( $value ) == 1 ) {
246  // Special-case single values, as IN isn't terribly efficient
247  // Don't necessarily assume the single key is 0; we don't
248  // enforce linear numeric ordering on other arrays here.
249  $value = array_values( $value )[0];
250  $list .= $field . " = " . $this->quoter->addQuotes( $value );
251  } else {
252  $list .= $field . " IN (" . $this->makeList( $value ) . ") ";
253  }
254  // if null present in array, append IS NULL
255  if ( $includeNull ) {
256  $list .= " OR $field IS NULL)";
257  }
258  }
259  } elseif ( $value === null ) {
260  if ( $mode == self::LIST_AND || $mode == self::LIST_OR ) {
261  $list .= "$field IS ";
262  } elseif ( $mode == self::LIST_SET ) {
263  $list .= "$field = ";
264  }
265  $list .= 'NULL';
266  } else {
267  if (
268  $mode == self::LIST_AND || $mode == self::LIST_OR || $mode == self::LIST_SET
269  ) {
270  $list .= "$field = ";
271  }
272  $list .= $mode == self::LIST_NAMES ? $value : $this->quoter->addQuotes( $value );
273  }
274  }
275 
276  return $list;
277  }
278 
279  public function makeWhereFrom2d( $data, $baseKey, $subKey ) {
280  $conds = [];
281 
282  foreach ( $data as $base => $sub ) {
283  if ( count( $sub ) ) {
284  $conds[] = $this->makeList(
285  [ $baseKey => $base, $subKey => array_map( 'strval', array_keys( $sub ) ) ],
287  );
288  }
289  }
290 
291  if ( $conds ) {
292  return $this->makeList( $conds, self::LIST_OR );
293  } else {
294  // Nothing to search for...
295  return false;
296  }
297  }
298 
299  public function factorConds( $condsArray ) {
300  if ( count( $condsArray ) === 0 ) {
301  throw new InvalidArgumentException(
302  __METHOD__ . ": empty condition array" );
303  }
304  $condsByFieldSet = [];
305  foreach ( $condsArray as $conds ) {
306  if ( !count( $conds ) ) {
307  throw new InvalidArgumentException(
308  __METHOD__ . ": empty condition subarray" );
309  }
310  $fieldKey = implode( ',', array_keys( $conds ) );
311  $condsByFieldSet[$fieldKey][] = $conds;
312  }
313  $result = '';
314  foreach ( $condsByFieldSet as $conds ) {
315  if ( $result !== '' ) {
316  $result .= ' OR ';
317  }
318  $result .= $this->factorCondsWithCommonFields( $conds );
319  }
320  return $result;
321  }
322 
330  private function factorCondsWithCommonFields( $condsArray ) {
331  $first = $condsArray[array_key_first( $condsArray )];
332  if ( count( $first ) === 1 ) {
333  // IN clause
334  $field = array_key_first( $first );
335  $values = [];
336  foreach ( $condsArray as $conds ) {
337  $values[] = $conds[$field];
338  }
339  return $this->makeList( [ $field => $values ], self::LIST_AND );
340  }
341 
342  $field1 = array_key_first( $first );
343  $nullExpressions = [];
344  $expressionsByField1 = [];
345  foreach ( $condsArray as $conds ) {
346  $value1 = $conds[$field1];
347  unset( $conds[$field1] );
348  if ( $value1 === null ) {
349  $nullExpressions[] = $conds;
350  } else {
351  $expressionsByField1[$value1][] = $conds;
352  }
353 
354  }
355  $wrap = false;
356  $result = '';
357  foreach ( $expressionsByField1 as $value1 => $expressions ) {
358  if ( $result !== '' ) {
359  $result .= ' OR ';
360  $wrap = true;
361  }
362  $factored = $this->factorCondsWithCommonFields( $expressions );
363  $result .= "($field1 = " . $this->quoter->addQuotes( $value1 ) .
364  " AND $factored)";
365  }
366  if ( count( $nullExpressions ) ) {
367  $factored = $this->factorCondsWithCommonFields( $nullExpressions );
368  if ( $result !== '' ) {
369  $result .= ' OR ';
370  $wrap = true;
371  }
372  $result .= "($field1 IS NULL AND $factored)";
373  }
374  if ( $wrap ) {
375  return "($result)";
376  } else {
377  return $result;
378  }
379  }
380 
385  public function buildConcat( $stringList ) {
386  return 'CONCAT(' . implode( ',', $stringList ) . ')';
387  }
388 
393  public function limitResult( $sql, $limit, $offset = false ) {
394  if ( !is_numeric( $limit ) ) {
395  throw new DBLanguageError(
396  "Invalid non-numeric limit passed to " . __METHOD__
397  );
398  }
399  // This version works in MySQL and SQLite. It will very likely need to be
400  // overridden for most other RDBMS subclasses.
401  return "$sql LIMIT "
402  . ( ( is_numeric( $offset ) && $offset != 0 ) ? "{$offset}," : "" )
403  . "{$limit} ";
404  }
405 
412  public function escapeLikeInternal( $s, $escapeChar = '`' ) {
413  return str_replace(
414  [ $escapeChar, '%', '_' ],
415  [ "{$escapeChar}{$escapeChar}", "{$escapeChar}%", "{$escapeChar}_" ],
416  $s
417  );
418  }
419 
424  public function buildLike( $param, ...$params ) {
425  if ( is_array( $param ) ) {
426  $params = $param;
427  } else {
428  $params = func_get_args();
429  }
430 
431  $s = '';
432 
433  // We use ` instead of \ as the default LIKE escape character, since addQuotes()
434  // may escape backslashes, creating problems of double escaping. The `
435  // character has good cross-DBMS compatibility, avoiding special operators
436  // in MS SQL like ^ and %
437  $escapeChar = '`';
438 
439  foreach ( $params as $value ) {
440  if ( $value instanceof LikeMatch ) {
441  $s .= $value->toString();
442  } else {
443  $s .= $this->escapeLikeInternal( $value, $escapeChar );
444  }
445  }
446 
447  return ' LIKE ' .
448  $this->quoter->addQuotes( $s ) . ' ESCAPE ' . $this->quoter->addQuotes( $escapeChar ) . ' ';
449  }
450 
451  public function anyChar() {
452  return new LikeMatch( '_' );
453  }
454 
455  public function anyString() {
456  return new LikeMatch( '%' );
457  }
458 
463  public function unionSupportsOrderAndLimit() {
464  return true; // True for almost every DB supported
465  }
466 
471  public function unionQueries( $sqls, $all ) {
472  $glue = $all ? ') UNION ALL (' : ') UNION (';
473 
474  return '(' . implode( $glue, $sqls ) . ')';
475  }
476 
481  public function conditional( $cond, $caseTrueExpression, $caseFalseExpression ) {
482  if ( is_array( $cond ) ) {
483  $cond = $this->makeList( $cond, self::LIST_AND );
484  }
485 
486  return "(CASE WHEN $cond THEN $caseTrueExpression ELSE $caseFalseExpression END)";
487  }
488 
493  public function strreplace( $orig, $old, $new ) {
494  return "REPLACE({$orig}, {$old}, {$new})";
495  }
496 
501  public function timestamp( $ts = 0 ) {
502  $t = new ConvertibleTimestamp( $ts );
503  // Let errors bubble up to avoid putting garbage in the DB
504  return $t->getTimestamp( TS_MW );
505  }
506 
507  public function timestampOrNull( $ts = null ) {
508  if ( $ts === null ) {
509  return null;
510  } else {
511  return $this->timestamp( $ts );
512  }
513  }
514 
519  public function getInfinity() {
520  return 'infinity';
521  }
522 
523  public function encodeExpiry( $expiry ) {
524  return ( $expiry == '' || $expiry == 'infinity' || $expiry == $this->getInfinity() )
525  ? $this->getInfinity()
526  : $this->timestamp( $expiry );
527  }
528 
529  public function decodeExpiry( $expiry, $format = TS_MW ) {
530  if ( $expiry == '' || $expiry == 'infinity' || $expiry == $this->getInfinity() ) {
531  return 'infinity';
532  }
533 
534  return ConvertibleTimestamp::convert( $format, $expiry );
535  }
536 
541  public function buildSubstring( $input, $startPosition, $length = null ) {
542  $this->assertBuildSubstringParams( $startPosition, $length );
543  $functionBody = "$input FROM $startPosition";
544  if ( $length !== null ) {
545  $functionBody .= " FOR $length";
546  }
547  return 'SUBSTRING(' . $functionBody . ')';
548  }
549 
562  protected function assertBuildSubstringParams( $startPosition, $length ) {
563  if ( $startPosition === 0 ) {
564  // The DBMSs we support use 1-based indexing here.
565  throw new InvalidArgumentException( 'Use 1 as $startPosition for the beginning of the string' );
566  }
567  if ( !is_int( $startPosition ) || $startPosition < 0 ) {
568  throw new InvalidArgumentException(
569  '$startPosition must be a positive integer'
570  );
571  }
572  if ( !( is_int( $length ) && $length >= 0 || $length === null ) ) {
573  throw new InvalidArgumentException(
574  '$length must be null or an integer greater than or equal to 0'
575  );
576  }
577  }
578 
583  public function buildStringCast( $field ) {
584  // In theory this should work for any standards-compliant
585  // SQL implementation, although it may not be the best way to do it.
586  return "CAST( $field AS CHARACTER )";
587  }
588 
593  public function buildIntegerCast( $field ) {
594  return 'CAST( ' . $field . ' AS INTEGER )';
595  }
596 
601  public function implicitOrderby() {
602  return true;
603  }
604 
613  public function indexName( $index ) {
614  return $this->indexAliases[$index] ?? $index;
615  }
616 
621  public function setTableAliases( array $aliases ) {
622  $this->tableAliases = $aliases;
623  }
624 
629  public function setIndexAliases( array $aliases ) {
630  $this->indexAliases = $aliases;
631  }
632 
636  public function getTableAliases() {
637  return $this->tableAliases;
638  }
639 
640  public function setPrefix( $prefix ) {
641  $this->currentDomain = new DatabaseDomain(
642  $this->currentDomain->getDatabase(),
643  $this->currentDomain->getSchema(),
644  $prefix
645  );
646  }
647 
648  public function setCurrentDomain( DatabaseDomain $currentDomain ) {
649  $this->currentDomain = $currentDomain;
650  }
651 
656  public function selectSQLText(
657  $table, $vars, $conds = '', $fname = __METHOD__, $options = [], $join_conds = []
658  ) {
659  if ( is_array( $table ) ) {
660  $tables = $table;
661  } elseif ( $table === '' || $table === null || $table === false ) {
662  $tables = [];
663  } elseif ( is_string( $table ) ) {
664  $tables = [ $table ];
665  } else {
666  throw new DBLanguageError( __METHOD__ . ' called with incorrect table parameter' );
667  }
668 
669  if ( is_array( $vars ) ) {
670  $fields = implode( ',', $this->fieldNamesWithAlias( $vars ) );
671  } else {
672  $fields = $vars;
673  }
674 
675  $options = (array)$options;
676 
677  $useIndexByTable = $options['USE INDEX'] ?? [];
678  if ( !is_array( $useIndexByTable ) ) {
679  if ( count( $tables ) <= 1 ) {
680  $useIndexByTable = [ reset( $tables ) => $useIndexByTable ];
681  } else {
682  $e = new DBLanguageError( __METHOD__ . " got ambiguous USE INDEX ($fname)" );
683  ( $this->errorLogger )( $e );
684  }
685  }
686 
687  $ignoreIndexByTable = $options['IGNORE INDEX'] ?? [];
688  if ( !is_array( $ignoreIndexByTable ) ) {
689  if ( count( $tables ) <= 1 ) {
690  $ignoreIndexByTable = [ reset( $tables ) => $ignoreIndexByTable ];
691  } else {
692  $e = new DBLanguageError( __METHOD__ . " got ambiguous IGNORE INDEX ($fname)" );
693  ( $this->errorLogger )( $e );
694  }
695  }
696 
697  if (
698  $this->selectOptionsIncludeLocking( $options ) &&
699  $this->selectFieldsOrOptionsAggregate( $vars, $options )
700  ) {
701  // Some DB types (e.g. postgres) disallow FOR UPDATE with aggregate
702  // functions. Discourage use of such queries to encourage compatibility.
703  $this->logger->warning(
704  __METHOD__ . ": aggregation used with a locking SELECT ($fname)"
705  );
706  }
707 
708  if ( count( $tables ) ) {
709  $from = ' FROM ' . $this->tableNamesWithIndexClauseOrJOIN(
710  $tables,
711  $useIndexByTable,
712  $ignoreIndexByTable,
713  $join_conds
714  );
715  } else {
716  $from = '';
717  }
718 
719  [ $startOpts, $preLimitTail, $postLimitTail ] = $this->makeSelectOptions( $options );
720 
721  if ( is_array( $conds ) ) {
722  $where = $this->makeList( $conds, self::LIST_AND );
723  } elseif ( $conds === null || $conds === false ) {
724  $where = '';
725  $this->logger->warning(
726  __METHOD__
727  . ' called from '
728  . $fname
729  . ' with incorrect parameters: $conds must be a string or an array',
730  [ 'db_log_category' => 'sql' ]
731  );
732  } elseif ( is_string( $conds ) ) {
733  $where = $conds;
734  } else {
735  throw new DBLanguageError( __METHOD__ . ' called with incorrect parameters' );
736  }
737 
738  // Keep historical extra spaces after FROM to avoid testing failures
739  if ( $where === '' || $where === '*' ) {
740  $sql = "SELECT $startOpts $fields $from $preLimitTail";
741  } else {
742  $sql = "SELECT $startOpts $fields $from WHERE $where $preLimitTail";
743  }
744 
745  if ( isset( $options['LIMIT'] ) ) {
746  $sql = $this->limitResult( $sql, $options['LIMIT'], $options['OFFSET'] ?? false );
747  }
748  $sql = "$sql $postLimitTail";
749 
750  if ( isset( $options['EXPLAIN'] ) ) {
751  $sql = 'EXPLAIN ' . $sql;
752  }
753 
754  return $sql;
755  }
756 
761  private function selectOptionsIncludeLocking( $options ) {
762  $options = (array)$options;
763  foreach ( [ 'FOR UPDATE', 'LOCK IN SHARE MODE' ] as $lock ) {
764  if ( in_array( $lock, $options, true ) ) {
765  return true;
766  }
767  }
768 
769  return false;
770  }
771 
777  private function selectFieldsOrOptionsAggregate( $fields, $options ) {
778  foreach ( (array)$options as $key => $value ) {
779  if ( is_string( $key ) ) {
780  if ( preg_match( '/^(?:GROUP BY|HAVING)$/i', $key ) ) {
781  return true;
782  }
783  } elseif ( is_string( $value ) ) {
784  if ( preg_match( '/^(?:DISTINCT|DISTINCTROW)$/i', $value ) ) {
785  return true;
786  }
787  }
788  }
789 
790  $regex = '/^(?:COUNT|MIN|MAX|SUM|GROUP_CONCAT|LISTAGG|ARRAY_AGG)\s*\\(/i';
791  foreach ( (array)$fields as $field ) {
792  if ( is_string( $field ) && preg_match( $regex, $field ) ) {
793  return true;
794  }
795  }
796 
797  return false;
798  }
799 
806  protected function fieldNamesWithAlias( $fields ) {
807  $retval = [];
808  foreach ( $fields as $alias => $field ) {
809  if ( is_numeric( $alias ) ) {
810  $alias = $field;
811  }
812  $retval[] = $this->fieldNameWithAlias( $field, $alias );
813  }
814 
815  return $retval;
816  }
817 
827  public function fieldNameWithAlias( $name, $alias = false ) {
828  if ( !$alias || (string)$alias === (string)$name ) {
829  return $name;
830  } else {
831  return $name . ' AS ' . $this->addIdentifierQuotes( $alias ); // PostgreSQL needs AS
832  }
833  }
834 
846  $tables,
847  $use_index = [],
848  $ignore_index = [],
849  $join_conds = []
850  ) {
851  $ret = [];
852  $retJOIN = [];
853  $use_index = (array)$use_index;
854  $ignore_index = (array)$ignore_index;
855  $join_conds = (array)$join_conds;
856 
857  foreach ( $tables as $alias => $table ) {
858  if ( !is_string( $alias ) ) {
859  // No alias? Set it equal to the table name
860  $alias = $table;
861  }
862 
863  if ( is_array( $table ) ) {
864  // A parenthesized group
865  if ( count( $table ) > 1 ) {
866  $joinedTable = '(' .
867  $this->tableNamesWithIndexClauseOrJOIN(
868  $table, $use_index, $ignore_index, $join_conds ) . ')';
869  } else {
870  // Degenerate case
871  $innerTable = reset( $table );
872  $innerAlias = key( $table );
873  $joinedTable = $this->tableNameWithAlias(
874  $innerTable,
875  is_string( $innerAlias ) ? $innerAlias : $innerTable
876  );
877  }
878  } else {
879  $joinedTable = $this->tableNameWithAlias( $table, $alias );
880  }
881 
882  // Is there a JOIN clause for this table?
883  if ( isset( $join_conds[$alias] ) ) {
884  Assert::parameterType( 'array', $join_conds[$alias], "join_conds[$alias]" );
885  [ $joinType, $conds ] = $join_conds[$alias];
886  $tableClause = $this->normalizeJoinType( $joinType );
887  $tableClause .= ' ' . $joinedTable;
888  if ( isset( $use_index[$alias] ) ) { // has USE INDEX?
889  $use = $this->useIndexClause( implode( ',', (array)$use_index[$alias] ) );
890  if ( $use != '' ) {
891  $tableClause .= ' ' . $use;
892  }
893  }
894  if ( isset( $ignore_index[$alias] ) ) { // has IGNORE INDEX?
895  $ignore = $this->ignoreIndexClause(
896  implode( ',', (array)$ignore_index[$alias] ) );
897  if ( $ignore != '' ) {
898  $tableClause .= ' ' . $ignore;
899  }
900  }
901  $on = $this->makeList( (array)$conds, self::LIST_AND );
902  if ( $on != '' ) {
903  $tableClause .= ' ON (' . $on . ')';
904  }
905 
906  $retJOIN[] = $tableClause;
907  } elseif ( isset( $use_index[$alias] ) ) {
908  // Is there an INDEX clause for this table?
909  $tableClause = $joinedTable;
910  $tableClause .= ' ' . $this->useIndexClause(
911  implode( ',', (array)$use_index[$alias] )
912  );
913 
914  $ret[] = $tableClause;
915  } elseif ( isset( $ignore_index[$alias] ) ) {
916  // Is there an INDEX clause for this table?
917  $tableClause = $joinedTable;
918  $tableClause .= ' ' . $this->ignoreIndexClause(
919  implode( ',', (array)$ignore_index[$alias] )
920  );
921 
922  $ret[] = $tableClause;
923  } else {
924  $tableClause = $joinedTable;
925 
926  $ret[] = $tableClause;
927  }
928  }
929 
930  // We can't separate explicit JOIN clauses with ',', use ' ' for those
931  $implicitJoins = implode( ',', $ret );
932  $explicitJoins = implode( ' ', $retJOIN );
933 
934  // Compile our final table clause
935  return implode( ' ', [ $implicitJoins, $explicitJoins ] );
936  }
937 
946  protected function normalizeJoinType( string $joinType ) {
947  switch ( strtoupper( $joinType ) ) {
948  case 'JOIN':
949  case 'INNER JOIN':
950  return 'JOIN';
951 
952  case 'LEFT JOIN':
953  return 'LEFT JOIN';
954 
955  case 'STRAIGHT_JOIN':
956  case 'STRAIGHT JOIN':
957  // MySQL only
958  return 'JOIN';
959 
960  default:
961  return $joinType;
962  }
963  }
964 
976  protected function tableNameWithAlias( $table, $alias = false ) {
977  if ( is_string( $table ) ) {
978  $quotedTable = $this->tableName( $table );
979  } elseif ( $table instanceof Subquery ) {
980  $quotedTable = (string)$table;
981  } else {
982  throw new InvalidArgumentException( "Table must be a string or Subquery" );
983  }
984 
985  if ( $alias === false || $alias === $table ) {
986  if ( $table instanceof Subquery ) {
987  throw new InvalidArgumentException( "Subquery table missing alias" );
988  }
989 
990  return $quotedTable;
991  } else {
992  return $quotedTable . ' ' . $this->addIdentifierQuotes( $alias );
993  }
994  }
995 
1000  public function tableName( $name, $format = 'quoted' ) {
1001  if ( $name instanceof Subquery ) {
1002  throw new DBLanguageError(
1003  __METHOD__ . ': got Subquery instance when expecting a string'
1004  );
1005  }
1006 
1007  # Skip the entire process when we have a string quoted on both ends.
1008  # Note that we check the end so that we will still quote any use of
1009  # use of `database`.table. But won't break things if someone wants
1010  # to query a database table with a dot in the name.
1011  if ( $this->isQuotedIdentifier( $name ) ) {
1012  return $name;
1013  }
1014 
1015  # Lets test for any bits of text that should never show up in a table
1016  # name. Basically anything like JOIN or ON which are actually part of
1017  # SQL queries, but may end up inside of the table value to combine
1018  # sql. Such as how the API is doing.
1019  # Note that we use a whitespace test rather than a \b test to avoid
1020  # any remote case where a word like on may be inside of a table name
1021  # surrounded by symbols which may be considered word breaks.
1022  if ( preg_match( '/(^|\s)(DISTINCT|JOIN|ON|AS)(\s|$)/i', $name ) !== 0 ) {
1023  $this->logger->warning(
1024  __METHOD__ . ": use of subqueries is not supported this way",
1025  [
1026  'exception' => new RuntimeException(),
1027  'db_log_category' => 'sql',
1028  ]
1029  );
1030 
1031  return $name;
1032  }
1033 
1034  # Split database and table into proper variables.
1035  [ $database, $schema, $prefix, $table ] = $this->qualifiedTableComponents( $name );
1036 
1037  # Quote $table and apply the prefix if not quoted.
1038  # $tableName might be empty if this is called from Database::replaceVars()
1039  $tableName = "{$prefix}{$table}";
1040  if ( $format === 'quoted'
1041  && !$this->isQuotedIdentifier( $tableName )
1042  && $tableName !== ''
1043  ) {
1044  $tableName = $this->addIdentifierQuotes( $tableName );
1045  }
1046 
1047  # Quote $schema and $database and merge them with the table name if needed
1048  $tableName = $this->prependDatabaseOrSchema( $schema, $tableName, $format );
1049  $tableName = $this->prependDatabaseOrSchema( $database, $tableName, $format );
1050 
1051  return $tableName;
1052  }
1053 
1060  public function qualifiedTableComponents( $name ) {
1061  # We reverse the explode so that database.table and table both output the correct table.
1062  $dbDetails = explode( '.', $name, 3 );
1063  if ( $this->currentDomain ) {
1064  $currentDomainPrefix = $this->currentDomain->getTablePrefix();
1065  } else {
1066  $currentDomainPrefix = null;
1067  }
1068  if ( count( $dbDetails ) == 3 ) {
1069  [ $database, $schema, $table ] = $dbDetails;
1070  # We don't want any prefix added in this case
1071  $prefix = '';
1072  } elseif ( count( $dbDetails ) == 2 ) {
1073  [ $database, $table ] = $dbDetails;
1074  # We don't want any prefix added in this case
1075  $prefix = '';
1076  # In dbs that support it, $database may actually be the schema
1077  # but that doesn't affect any of the functionality here
1078  $schema = '';
1079  } else {
1080  [ $table ] = $dbDetails;
1081  if ( isset( $this->tableAliases[$table] ) ) {
1082  $database = $this->tableAliases[$table]['dbname'];
1083  $schema = is_string( $this->tableAliases[$table]['schema'] )
1084  ? $this->tableAliases[$table]['schema']
1085  : $this->relationSchemaQualifier();
1086  $prefix = is_string( $this->tableAliases[$table]['prefix'] )
1087  ? $this->tableAliases[$table]['prefix']
1088  : $currentDomainPrefix;
1089  } else {
1090  $database = '';
1091  $schema = $this->relationSchemaQualifier(); # Default schema
1092  $prefix = $currentDomainPrefix; # Default prefix
1093  }
1094  }
1095 
1096  return [ $database, $schema, $prefix, $table ];
1097  }
1098 
1103  protected function relationSchemaQualifier() {
1104  if ( $this->currentDomain ) {
1105  return $this->currentDomain->getSchema();
1106  }
1107  return null;
1108  }
1109 
1116  private function prependDatabaseOrSchema( $namespace, $relation, $format ) {
1117  if ( $namespace !== null && $namespace !== '' ) {
1118  if ( $format === 'quoted' && !$this->isQuotedIdentifier( $namespace ) ) {
1119  $namespace = $this->addIdentifierQuotes( $namespace );
1120  }
1121  $relation = $namespace . '.' . $relation;
1122  }
1123 
1124  return $relation;
1125  }
1126 
1127  public function tableNames( ...$tables ) {
1128  $retVal = [];
1129 
1130  foreach ( $tables as $name ) {
1131  $retVal[$name] = $this->tableName( $name );
1132  }
1133 
1134  return $retVal;
1135  }
1136 
1137  public function tableNamesN( ...$tables ) {
1138  $retVal = [];
1139 
1140  foreach ( $tables as $name ) {
1141  $retVal[] = $this->tableName( $name );
1142  }
1143 
1144  return $retVal;
1145  }
1146 
1157  public function isQuotedIdentifier( $name ) {
1158  return $name[0] == '"' && substr( $name, -1, 1 ) == '"';
1159  }
1160 
1174  public function useIndexClause( $index ) {
1175  return '';
1176  }
1177 
1187  public function ignoreIndexClause( $index ) {
1188  return '';
1189  }
1190 
1202  protected function makeSelectOptions( array $options ) {
1203  $preLimitTail = $postLimitTail = '';
1204  $startOpts = '';
1205 
1206  $noKeyOptions = [];
1207 
1208  foreach ( $options as $key => $option ) {
1209  if ( is_numeric( $key ) ) {
1210  $noKeyOptions[$option] = true;
1211  }
1212  }
1213 
1214  $preLimitTail .= $this->makeGroupByWithHaving( $options );
1215 
1216  $preLimitTail .= $this->makeOrderBy( $options );
1217 
1218  if ( isset( $noKeyOptions['FOR UPDATE'] ) ) {
1219  $postLimitTail .= ' FOR UPDATE';
1220  }
1221 
1222  if ( isset( $noKeyOptions['LOCK IN SHARE MODE'] ) ) {
1223  $postLimitTail .= ' LOCK IN SHARE MODE';
1224  }
1225 
1226  if ( isset( $noKeyOptions['DISTINCT'] ) || isset( $noKeyOptions['DISTINCTROW'] ) ) {
1227  $startOpts .= 'DISTINCT';
1228  }
1229 
1230  # Various MySQL extensions
1231  if ( isset( $noKeyOptions['STRAIGHT_JOIN'] ) ) {
1232  $startOpts .= ' /*! STRAIGHT_JOIN */';
1233  }
1234 
1235  if ( isset( $noKeyOptions['SQL_BIG_RESULT'] ) ) {
1236  $startOpts .= ' SQL_BIG_RESULT';
1237  }
1238 
1239  if ( isset( $noKeyOptions['SQL_BUFFER_RESULT'] ) ) {
1240  $startOpts .= ' SQL_BUFFER_RESULT';
1241  }
1242 
1243  if ( isset( $noKeyOptions['SQL_SMALL_RESULT'] ) ) {
1244  $startOpts .= ' SQL_SMALL_RESULT';
1245  }
1246 
1247  if ( isset( $noKeyOptions['SQL_CALC_FOUND_ROWS'] ) ) {
1248  $startOpts .= ' SQL_CALC_FOUND_ROWS';
1249  }
1250 
1251  return [ $startOpts, $preLimitTail, $postLimitTail ];
1252  }
1253 
1262  protected function makeGroupByWithHaving( $options ) {
1263  $sql = '';
1264  if ( isset( $options['GROUP BY'] ) ) {
1265  $gb = is_array( $options['GROUP BY'] )
1266  ? implode( ',', $options['GROUP BY'] )
1267  : $options['GROUP BY'];
1268  $sql .= ' GROUP BY ' . $gb;
1269  }
1270  if ( isset( $options['HAVING'] ) ) {
1271  $having = is_array( $options['HAVING'] )
1272  ? $this->makeList( $options['HAVING'], self::LIST_AND )
1273  : $options['HAVING'];
1274  $sql .= ' HAVING ' . $having;
1275  }
1276 
1277  return $sql;
1278  }
1279 
1288  protected function makeOrderBy( $options ) {
1289  if ( isset( $options['ORDER BY'] ) ) {
1290  $ob = is_array( $options['ORDER BY'] )
1291  ? implode( ',', $options['ORDER BY'] )
1292  : $options['ORDER BY'];
1293 
1294  return ' ORDER BY ' . $ob;
1295  }
1296 
1297  return '';
1298  }
1299 
1301  $table,
1302  $vars,
1303  array $permute_conds,
1304  $extra_conds = '',
1305  $fname = __METHOD__,
1306  $options = [],
1307  $join_conds = []
1308  ) {
1309  // First, build the Cartesian product of $permute_conds
1310  $conds = [ [] ];
1311  foreach ( $permute_conds as $field => $values ) {
1312  if ( !$values ) {
1313  // Skip empty $values
1314  continue;
1315  }
1316  $values = array_unique( $values );
1317  $newConds = [];
1318  foreach ( $conds as $cond ) {
1319  foreach ( $values as $value ) {
1320  $cond[$field] = $value;
1321  $newConds[] = $cond; // Arrays are by-value, not by-reference, so this works
1322  }
1323  }
1324  $conds = $newConds;
1325  }
1326 
1327  $extra_conds = $extra_conds === '' ? [] : (array)$extra_conds;
1328 
1329  // If there's just one condition and no subordering, hand off to
1330  // selectSQLText directly.
1331  if ( count( $conds ) === 1 &&
1332  ( !isset( $options['INNER ORDER BY'] ) || !$this->unionSupportsOrderAndLimit() )
1333  ) {
1334  return $this->selectSQLText(
1335  $table, $vars, $conds[0] + $extra_conds, $fname, $options, $join_conds
1336  );
1337  }
1338 
1339  // Otherwise, we need to pull out the order and limit to apply after
1340  // the union. Then build the SQL queries for each set of conditions in
1341  // $conds. Then union them together (using UNION ALL, because the
1342  // product *should* already be distinct).
1343  $orderBy = $this->makeOrderBy( $options );
1344  $limit = $options['LIMIT'] ?? null;
1345  $offset = $options['OFFSET'] ?? false;
1346  $all = empty( $options['NOTALL'] ) && !in_array( 'NOTALL', $options );
1347  if ( !$this->unionSupportsOrderAndLimit() ) {
1348  unset( $options['ORDER BY'], $options['LIMIT'], $options['OFFSET'] );
1349  } else {
1350  if ( array_key_exists( 'INNER ORDER BY', $options ) ) {
1351  $options['ORDER BY'] = $options['INNER ORDER BY'];
1352  }
1353  if ( $limit !== null && is_numeric( $offset ) && $offset != 0 ) {
1354  // We need to increase the limit by the offset rather than
1355  // using the offset directly, otherwise it'll skip incorrectly
1356  // in the subqueries.
1357  $options['LIMIT'] = $limit + $offset;
1358  unset( $options['OFFSET'] );
1359  }
1360  }
1361 
1362  $sqls = [];
1363  foreach ( $conds as $cond ) {
1364  $sqls[] = $this->selectSQLText(
1365  $table, $vars, $cond + $extra_conds, $fname, $options, $join_conds
1366  );
1367  }
1368  $sql = $this->unionQueries( $sqls, $all ) . $orderBy;
1369  if ( $limit !== null ) {
1370  $sql = $this->limitResult( $sql, $limit, $offset );
1371  }
1372 
1373  return $sql;
1374  }
1375 
1380  public function buildGroupConcatField(
1381  $delim, $table, $field, $conds = '', $join_conds = []
1382  ) {
1383  $fld = "GROUP_CONCAT($field SEPARATOR " . $this->quoter->addQuotes( $delim ) . ')';
1384 
1385  return '(' . $this->selectSQLText( $table, $fld, $conds, __METHOD__, [], $join_conds ) . ')';
1386  }
1387 
1388  public function buildSelectSubquery(
1389  $table, $vars, $conds = '', $fname = __METHOD__,
1390  $options = [], $join_conds = []
1391  ) {
1392  return new Subquery(
1393  $this->selectSQLText( $table, $vars, $conds, $fname, $options, $join_conds )
1394  );
1395  }
1396 
1397  public function insertSqlText( $table, array $rows ) {
1398  $encTable = $this->tableName( $table );
1399  [ $sqlColumns, $sqlTuples ] = $this->makeInsertLists( $rows );
1400 
1401  return "INSERT INTO $encTable ($sqlColumns) VALUES $sqlTuples";
1402  }
1403 
1416  public function makeInsertLists( array $rows, $aliasPrefix = '', array $typeByColumn = [] ) {
1417  $firstRow = $rows[0];
1418  if ( !is_array( $firstRow ) || !$firstRow ) {
1419  throw new DBLanguageError( 'Got an empty row list or empty row' );
1420  }
1421  // List of columns that define the value tuple ordering
1422  $tupleColumns = array_keys( $firstRow );
1423 
1424  $valueTuples = [];
1425  foreach ( $rows as $row ) {
1426  $rowColumns = array_keys( $row );
1427  // VALUES(...) requires a uniform correspondence of (column => value)
1428  if ( $rowColumns !== $tupleColumns ) {
1429  throw new DBLanguageError(
1430  'Got row columns (' . implode( ', ', $rowColumns ) . ') ' .
1431  'instead of expected (' . implode( ', ', $tupleColumns ) . ')'
1432  );
1433  }
1434  // Make the value tuple that defines this row
1435  $valueTuples[] = '(' . $this->makeList( $row, self::LIST_COMMA ) . ')';
1436  }
1437 
1438  $magicAliasFields = [];
1439  foreach ( $tupleColumns as $column ) {
1440  $magicAliasFields[] = $aliasPrefix . $column;
1441  }
1442 
1443  return [
1444  $this->makeList( $tupleColumns, self::LIST_NAMES ),
1445  implode( ',', $valueTuples ),
1446  $this->makeList( $magicAliasFields, self::LIST_NAMES )
1447  ];
1448  }
1449 
1450  public function insertNonConflictingSqlText( $table, array $rows ) {
1451  $encTable = $this->tableName( $table );
1452  [ $sqlColumns, $sqlTuples ] = $this->makeInsertLists( $rows );
1453  [ $sqlVerb, $sqlOpts ] = $this->makeInsertNonConflictingVerbAndOptions();
1454 
1455  return rtrim( "$sqlVerb $encTable ($sqlColumns) VALUES $sqlTuples $sqlOpts" );
1456  }
1457 
1464  return [ 'INSERT IGNORE INTO', '' ];
1465  }
1466 
1467  public function insertSelectNativeSqlText(
1468  $destTable,
1469  $srcTable,
1470  array $varMap,
1471  $conds,
1472  $fname,
1473  array $insertOptions,
1474  array $selectOptions,
1475  $selectJoinConds
1476  ) {
1477  [ $sqlVerb, $sqlOpts ] = $this->isFlagInOptions( 'IGNORE', $insertOptions )
1478  ? $this->makeInsertNonConflictingVerbAndOptions()
1479  : [ 'INSERT INTO', '' ];
1480  $encDstTable = $this->tableName( $destTable );
1481  $sqlDstColumns = implode( ',', array_keys( $varMap ) );
1482  $selectSql = $this->selectSQLText(
1483  $srcTable,
1484  array_values( $varMap ),
1485  $conds,
1486  $fname,
1487  $selectOptions,
1488  $selectJoinConds
1489  );
1490 
1491  return rtrim( "$sqlVerb $encDstTable ($sqlDstColumns) $selectSql $sqlOpts" );
1492  }
1493 
1500  public function isFlagInOptions( $option, array $options ) {
1501  foreach ( array_keys( $options, $option, true ) as $k ) {
1502  if ( is_int( $k ) ) {
1503  return true;
1504  }
1505  }
1506 
1507  return false;
1508  }
1509 
1517  public function makeKeyCollisionCondition( array $rows, array $uniqueKey ) {
1518  if ( !$rows ) {
1519  throw new DBLanguageError( "Empty row array" );
1520  } elseif ( !$uniqueKey ) {
1521  throw new DBLanguageError( "Empty unique key array" );
1522  }
1523 
1524  if ( count( $uniqueKey ) == 1 ) {
1525  // Use a simple IN(...) clause
1526  $column = reset( $uniqueKey );
1527  $values = array_column( $rows, $column );
1528  if ( count( $values ) !== count( $rows ) ) {
1529  throw new DBLanguageError( "Missing values for unique key ($column)" );
1530  }
1531 
1532  return $this->makeList( [ $column => $values ], self::LIST_AND );
1533  }
1534 
1535  $nullByUniqueKeyColumn = array_fill_keys( $uniqueKey, null );
1536 
1537  $orConds = [];
1538  foreach ( $rows as $row ) {
1539  $rowKeyMap = array_intersect_key( $row, $nullByUniqueKeyColumn );
1540  if ( count( $rowKeyMap ) != count( $uniqueKey ) ) {
1541  throw new DBLanguageError(
1542  "Missing values for unique key (" . implode( ',', $uniqueKey ) . ")"
1543  );
1544  }
1545  $orConds[] = $this->makeList( $rowKeyMap, self::LIST_AND );
1546  }
1547 
1548  return count( $orConds ) > 1
1549  ? $this->makeList( $orConds, self::LIST_OR )
1550  : $orConds[0];
1551  }
1552 
1553  public function deleteJoinSqlText( $delTable, $joinTable, $delVar, $joinVar, $conds ) {
1554  if ( !$conds ) {
1555  throw new DBLanguageError( __METHOD__ . ' called with empty $conds' );
1556  }
1557 
1558  $delTable = $this->tableName( $delTable );
1559  $joinTable = $this->tableName( $joinTable );
1560  $sql = "DELETE FROM $delTable WHERE $delVar IN (SELECT $joinVar FROM $joinTable ";
1561  if ( $conds != '*' ) {
1562  $sql .= 'WHERE ' . $this->makeList( $conds, self::LIST_AND );
1563  }
1564  $sql .= ')';
1565 
1566  return $sql;
1567  }
1568 
1569  public function deleteSqlText( $table, $conds ) {
1570  $this->assertConditionIsNotEmpty( $conds, __METHOD__, false );
1571 
1572  $table = $this->tableName( $table );
1573  $sql = "DELETE FROM $table";
1574 
1575  if ( $conds !== self::ALL_ROWS ) {
1576  if ( is_array( $conds ) ) {
1577  $conds = $this->makeList( $conds, self::LIST_AND );
1578  }
1579  $sql .= ' WHERE ' . $conds;
1580  }
1581 
1582  return $sql;
1583  }
1584 
1585  public function updateSqlText( $table, $set, $conds, $options ) {
1586  $this->assertConditionIsNotEmpty( $conds, __METHOD__, true );
1587  $table = $this->tableName( $table );
1588  $opts = $this->makeUpdateOptions( $options );
1589  $sql = "UPDATE $opts $table SET " . $this->makeList( $set, self::LIST_SET );
1590 
1591  if ( $conds && $conds !== self::ALL_ROWS ) {
1592  if ( is_array( $conds ) ) {
1593  $conds = $this->makeList( $conds, self::LIST_AND );
1594  }
1595  $sql .= ' WHERE ' . $conds;
1596  }
1597 
1598  return $sql;
1599  }
1600 
1614  protected function assertConditionIsNotEmpty( $conds, string $fname, bool $deprecate ) {
1615  $isCondValid = ( is_string( $conds ) || is_array( $conds ) ) && $conds;
1616  if ( !$isCondValid ) {
1617  if ( $deprecate ) {
1618  wfDeprecated( $fname . ' called with empty $conds', '1.35', false, 4 );
1619  } else {
1620  throw new DBLanguageError( $fname . ' called with empty conditions' );
1621  }
1622  }
1623  }
1624 
1632  protected function makeUpdateOptions( $options ) {
1633  $opts = $this->makeUpdateOptionsArray( $options );
1634 
1635  return implode( ' ', $opts );
1636  }
1637 
1645  protected function makeUpdateOptionsArray( $options ) {
1646  $options = $this->normalizeOptions( $options );
1647 
1648  $opts = [];
1649 
1650  if ( in_array( 'IGNORE', $options ) ) {
1651  $opts[] = 'IGNORE';
1652  }
1653 
1654  return $opts;
1655  }
1656 
1662  final public function normalizeOptions( $options ) {
1663  if ( is_array( $options ) ) {
1664  return $options;
1665  } elseif ( is_string( $options ) ) {
1666  return ( $options === '' ) ? [] : [ $options ];
1667  } else {
1668  throw new DBLanguageError( __METHOD__ . ': expected string or array' );
1669  }
1670  }
1671 
1672  public function dropTableSqlText( $table ) {
1673  // https://mariadb.com/kb/en/drop-table/
1674  // https://dev.mysql.com/doc/refman/8.0/en/drop-table.html
1675  // https://www.postgresql.org/docs/9.2/sql-truncate.html
1676  return "DROP TABLE " . $this->tableName( $table ) . " CASCADE";
1677  }
1678 
1683  public function getQueryVerb( $sql ) {
1684  // Distinguish ROLLBACK from ROLLBACK TO SAVEPOINT
1685  return preg_match(
1686  '/^\s*(rollback\s+to\s+savepoint|[a-z]+)/i',
1687  $sql,
1688  $m
1689  ) ? strtoupper( $m[1] ) : null;
1690  }
1691 
1706  public function isTransactableQuery( $sql ) {
1707  return !in_array(
1708  $this->getQueryVerb( $sql ),
1709  [
1710  'BEGIN',
1711  'ROLLBACK',
1712  'ROLLBACK TO SAVEPOINT',
1713  'COMMIT',
1714  'SET',
1715  'SHOW',
1716  'CREATE',
1717  'ALTER',
1718  'USE',
1719  'SHOW'
1720  ],
1721  true
1722  );
1723  }
1724 
1742  public function isWriteQuery( $sql, $flags ) {
1743  // Check if a SQL wrapper method already flagged the query as a write
1744  if (
1745  $this->fieldHasBit( $flags, self::QUERY_CHANGE_ROWS ) ||
1746  $this->fieldHasBit( $flags, self::QUERY_CHANGE_SCHEMA )
1747  ) {
1748  return true;
1749  }
1750  // Check if a SQL wrapper method already flagged the query as a non-write
1751  if (
1752  $this->fieldHasBit( $flags, self::QUERY_CHANGE_NONE ) ||
1753  $this->fieldHasBit( $flags, self::QUERY_CHANGE_TRX ) ||
1754  $this->fieldHasBit( $flags, self::QUERY_CHANGE_LOCKS )
1755  ) {
1756  return false;
1757  }
1758 
1759  $this->logger->warning( __METHOD__ . ' fallback to regex', [
1760  'exception' => new RuntimeException(),
1761  'db_log_category' => 'sql',
1762  ] );
1763 
1764  // Treat SELECT queries without FOR UPDATE queries as non-writes. This matches
1765  // how MySQL enforces read_only (FOR SHARE and LOCK IN SHADE MODE are allowed).
1766  // Handle (SELECT ...) UNION (SELECT ...) queries in a similar fashion.
1767  if ( preg_match( '/^\s*\‍(?SELECT\b/i', $sql ) ) {
1768  return (bool)preg_match( '/\bFOR\s+UPDATE\‍)?\s*$/i', $sql );
1769  }
1770  // BEGIN and COMMIT queries are considered non-write queries here.
1771  // Database backends and drivers (MySQL, MariaDB, php-mysqli) generally
1772  // treat these as write queries, in that their results have "affected rows"
1773  // as meta data as from writes, instead of "num rows" as from reads.
1774  // But, we treat them as non-write queries because when reading data (from
1775  // either replica or primary DB) we use transactions to enable repeatable-read
1776  // snapshots, which ensures we get consistent results from the same snapshot
1777  // for all queries within a request. Use cases:
1778  // - Treating these as writes would trigger ChronologyProtector (see method doc).
1779  // - We use this method to reject writes to replicas, but we need to allow
1780  // use of transactions on replicas for read snapshots. This is fine given
1781  // that transactions by themselves don't make changes, only actual writes
1782  // within the transaction matter, which we still detect.
1783  return !preg_match(
1784  '/^\s*(BEGIN|ROLLBACK|COMMIT|SAVEPOINT|RELEASE|SET|SHOW|EXPLAIN|USE)\b/i',
1785  $sql
1786  );
1787  }
1788 
1794  final protected function fieldHasBit( int $flags, int $bit ) {
1795  return ( ( $flags & $bit ) === $bit );
1796  }
1797 
1798  public function buildExcludedValue( $column ) {
1799  /* @see Database::doUpsert() */
1800  // This can be treated like a single value since __VALS is a single row table
1801  return "(SELECT __$column FROM __VALS)";
1802  }
1803 
1804  public function savepointSqlText( $identifier ) {
1805  return 'SAVEPOINT ' . $this->addIdentifierQuotes( $identifier );
1806  }
1807 
1808  public function releaseSavepointSqlText( $identifier ) {
1809  return 'RELEASE SAVEPOINT ' . $this->addIdentifierQuotes( $identifier );
1810  }
1811 
1812  public function rollbackToSavepointSqlText( $identifier ) {
1813  return 'ROLLBACK TO SAVEPOINT ' . $this->addIdentifierQuotes( $identifier );
1814  }
1815 
1816  public function rollbackSqlText() {
1817  return 'ROLLBACK';
1818  }
1819 
1820  public function dispatchingInsertSqlText( $table, $rows, $options ) {
1821  $rows = $this->normalizeRowArray( $rows );
1822  if ( !$rows ) {
1823  return false;
1824  }
1825 
1826  $options = $this->normalizeOptions( $options );
1827  if ( $this->isFlagInOptions( 'IGNORE', $options ) ) {
1828  return $this->insertNonConflictingSqlText( $table, $rows );
1829  } else {
1830  return $this->insertSqlText( $table, $rows );
1831  }
1832  }
1833 
1839  final protected function normalizeRowArray( array $rowOrRows ) {
1840  if ( !$rowOrRows ) {
1841  $rows = [];
1842  } elseif ( isset( $rowOrRows[0] ) ) {
1843  $rows = $rowOrRows;
1844  } else {
1845  $rows = [ $rowOrRows ];
1846  }
1847 
1848  foreach ( $rows as $row ) {
1849  if ( !is_array( $row ) ) {
1850  throw new DBLanguageError( "Got non-array in row array" );
1851  } elseif ( !$row ) {
1852  throw new DBLanguageError( "Got empty array in row array" );
1853  }
1854  }
1855 
1856  return $rows;
1857  }
1858 
1868  final public function normalizeUpsertParams( $uniqueKeys, &$rows ) {
1869  $rows = $this->normalizeRowArray( $rows );
1870  if ( !$rows ) {
1871  return null;
1872  }
1873  if ( !$uniqueKeys ) {
1874  // For backwards compatibility, allow insertion of rows with no applicable key
1875  $this->logger->warning(
1876  "upsert/replace called with no unique key",
1877  [
1878  'exception' => new RuntimeException(),
1879  'db_log_category' => 'sql',
1880  ]
1881  );
1882  return null;
1883  }
1884  $identityKey = $this->normalizeUpsertKeys( $uniqueKeys );
1885  if ( $identityKey ) {
1886  $allDefaultKeyValues = $this->assertValidUpsertRowArray( $rows, $identityKey );
1887  if ( $allDefaultKeyValues ) {
1888  // For backwards compatibility, allow insertion of rows with all-NULL
1889  // values for the unique columns (e.g. for an AUTOINCREMENT column)
1890  $this->logger->warning(
1891  "upsert/replace called with all-null values for unique key",
1892  [
1893  'exception' => new RuntimeException(),
1894  'db_log_category' => 'sql',
1895  ]
1896  );
1897  return null;
1898  }
1899  }
1900  return $identityKey;
1901  }
1902 
1909  final public function normalizeConditions( $conds, $fname ) {
1910  if ( $conds === null || $conds === false ) {
1911  $this->logger->warning(
1912  __METHOD__
1913  . ' called from '
1914  . $fname
1915  . ' with incorrect parameters: $conds must be a string or an array',
1916  [ 'db_log_category' => 'sql' ]
1917  );
1918  return [];
1919  } elseif ( $conds === '' ) {
1920  return [];
1921  }
1922 
1923  return is_array( $conds ) ? $conds : [ $conds ];
1924  }
1925 
1932  private function normalizeUpsertKeys( $uniqueKeys ) {
1933  if ( is_string( $uniqueKeys ) ) {
1934  return [ $uniqueKeys ];
1935  } elseif ( !is_array( $uniqueKeys ) ) {
1936  throw new DBLanguageError( 'Invalid unique key array' );
1937  } else {
1938  if ( count( $uniqueKeys ) !== 1 || !isset( $uniqueKeys[0] ) ) {
1939  throw new DBLanguageError(
1940  "The unique key array should contain a single unique index" );
1941  }
1942 
1943  $uniqueKey = $uniqueKeys[0];
1944  if ( is_string( $uniqueKey ) ) {
1945  // Passing a list of strings for single-column unique keys is too
1946  // easily confused with passing the columns of composite unique key
1947  $this->logger->warning( __METHOD__ .
1948  " called with deprecated parameter style: " .
1949  "the unique key array should be a string or array of string arrays",
1950  [
1951  'exception' => new RuntimeException(),
1952  'db_log_category' => 'sql',
1953  ] );
1954  return $uniqueKeys;
1955  } elseif ( is_array( $uniqueKey ) ) {
1956  return $uniqueKey;
1957  } else {
1958  throw new DBLanguageError( 'Invalid unique key array entry' );
1959  }
1960  }
1961  }
1962 
1969  final protected function assertValidUpsertRowArray( array $rows, array $identityKey ) {
1970  $numNulls = 0;
1971  foreach ( $rows as $row ) {
1972  foreach ( $identityKey as $column ) {
1973  $numNulls += ( isset( $row[$column] ) ? 0 : 1 );
1974  }
1975  }
1976 
1977  if (
1978  $numNulls &&
1979  $numNulls !== ( count( $rows ) * count( $identityKey ) )
1980  ) {
1981  throw new DBLanguageError(
1982  "NULL/absent values for unique key (" . implode( ',', $identityKey ) . ")"
1983  );
1984  }
1985 
1986  return (bool)$numNulls;
1987  }
1988 
1995  final public function assertValidUpsertSetArray(
1996  array $set,
1997  array $identityKey,
1998  array $rows
1999  ) {
2000  if ( !$set ) {
2001  throw new DBLanguageError( "Update assignment list can't be empty for upsert" );
2002  }
2003 
2004  // Sloppy callers might construct the SET array using the ROW array, leaving redundant
2005  // column definitions for identity key columns. Detect this for backwards compatibility.
2006  $soleRow = ( count( $rows ) == 1 ) ? reset( $rows ) : null;
2007  // Disallow value changes for any columns in the identity key. This avoids additional
2008  // insertion order dependencies that are unwieldy and difficult to implement efficiently
2009  // in PostgreSQL.
2010  foreach ( $set as $k => $v ) {
2011  if ( is_string( $k ) ) {
2012  // Key is a column name and value is a literal (e.g. string, int, null, ...)
2013  if ( in_array( $k, $identityKey, true ) ) {
2014  if ( $soleRow && array_key_exists( $k, $soleRow ) && $soleRow[$k] === $v ) {
2015  $this->logger->warning(
2016  __METHOD__ . " called with redundant assignment to column '$k'",
2017  [
2018  'exception' => new RuntimeException(),
2019  'db_log_category' => 'sql',
2020  ]
2021  );
2022  } else {
2023  throw new DBLanguageError(
2024  "Cannot reassign column '$k' since it belongs to identity key"
2025  );
2026  }
2027  }
2028  } elseif ( preg_match( '/^([a-zA-Z0-9_]+)\s*=/', $v, $m ) ) {
2029  // Value is of the form "<unquoted alphanumeric column> = <SQL expression>"
2030  if ( in_array( $m[1], $identityKey, true ) ) {
2031  throw new DBLanguageError(
2032  "Cannot reassign column '{$m[1]}' since it belongs to identity key"
2033  );
2034  }
2035  }
2036  }
2037  }
2038 
2043  final public function extractSingleFieldFromList( $var ) {
2044  if ( is_array( $var ) ) {
2045  if ( !$var ) {
2046  $column = null;
2047  } elseif ( count( $var ) == 1 ) {
2048  $column = $var[0] ?? reset( $var );
2049  } else {
2050  throw new DBLanguageError( __METHOD__ . ': got multiple columns' );
2051  }
2052  } else {
2053  $column = $var;
2054  }
2055 
2056  return $column;
2057  }
2058 
2059  public function setSchemaVars( $vars ) {
2060  $this->schemaVars = is_array( $vars ) ? $vars : null;
2061  }
2062 
2069  protected function getSchemaVars() {
2070  return $this->schemaVars ?? $this->getDefaultSchemaVars();
2071  }
2072 
2082  protected function getDefaultSchemaVars() {
2083  return [];
2084  }
2085 
2107  public function replaceVars( $ins ) {
2108  $vars = $this->getSchemaVars();
2109  return preg_replace_callback(
2110  '!
2111  /\* (\$wgDBprefix|[_i]) \*/ (\w*) | # 1-2. tableName, indexName
2112  \'\{\$ (\w+) }\' | # 3. addQuotes
2113  `\{\$ (\w+) }` | # 4. addIdentifierQuotes
2114  /\*\$ (\w+) \*/ # 5. leave unencoded
2115  !x',
2116  function ( $m ) use ( $vars ) {
2117  // Note: Because of <https://bugs.php.net/bug.php?id=51881>,
2118  // check for both nonexistent keys *and* the empty string.
2119  if ( isset( $m[1] ) && $m[1] !== '' ) {
2120  if ( $m[1] === 'i' ) {
2121  return $this->indexName( $m[2] );
2122  } else {
2123  return $this->tableName( $m[2] );
2124  }
2125  } elseif ( isset( $m[3] ) && $m[3] !== '' && array_key_exists( $m[3], $vars ) ) {
2126  return $this->quoter->addQuotes( $vars[$m[3]] );
2127  } elseif ( isset( $m[4] ) && $m[4] !== '' && array_key_exists( $m[4], $vars ) ) {
2128  return $this->addIdentifierQuotes( $vars[$m[4]] );
2129  } elseif ( isset( $m[5] ) && $m[5] !== '' && array_key_exists( $m[5], $vars ) ) {
2130  return $vars[$m[5]];
2131  } else {
2132  return $m[0];
2133  }
2134  },
2135  $ins
2136  );
2137  }
2138 
2139  public function lockSQLText( $lockName, $timeout ) {
2140  throw new RuntimeException( 'locking must be implemented in subclasses' );
2141  }
2142 
2143  public function lockIsFreeSQLText( $lockName ) {
2144  throw new RuntimeException( 'locking must be implemented in subclasses' );
2145  }
2146 
2147  public function unlockSQLText( $lockName ) {
2148  throw new RuntimeException( 'locking must be implemented in subclasses' );
2149  }
2150 }
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.
if(!defined('MW_SETUP_CALLBACK'))
Definition: WebStart.php:88
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:48
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)
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...
__construct(DbQuoter $quoter, LoggerInterface $logger=null, DatabaseDomain $currentDomain=null, $errorLogger=null)
Definition: SQLPlatform.php:58
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:50
ignoreIndexClause( $index)
IGNORE INDEX clause.
makeOrderBy( $options)
Returns an optional ORDER BY.
string[] $indexAliases
Current map of (index alias => index)
Definition: SQLPlatform.php:46
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:93
fieldNameWithAlias( $name, $alias=false)
Get an aliased field name e.g.
buildComparison(string $op, array $conds)
Build a condition comparing multiple values, for use with indexes that cover multiple fields,...
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:85
array[] $tableAliases
Current map of (table => (dbname, schema, prefix) map)
Definition: SQLPlatform.php:44
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().
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...
makeInsertLists(array $rows, $aliasPrefix='', array $typeByColumn=[])
Make SQL lists of columns, row tuples, and column aliases for INSERT/VALUES expressions.
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...
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.
callable $errorLogger
Error logging callback.
Definition: SQLPlatform.php:56
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