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;
35 use Wikimedia\Timestamp\ConvertibleTimestamp;
36 
44 class SQLPlatform implements ISQLPlatform {
46  protected $tableAliases = [];
48  protected $indexAliases = [];
50  protected $currentDomain;
52  protected $schemaVars;
54  protected $quoter;
56  protected $logger;
58  protected $errorLogger;
59 
60  public function __construct(
62  LoggerInterface $logger = null,
64  $errorLogger = null
65 
66  ) {
67  $this->quoter = $quoter;
68  $this->logger = $logger ?? new NullLogger();
69  $this->currentDomain = $currentDomain;
70  $this->errorLogger = $errorLogger ?? static function ( Throwable $e ) {
71  trigger_error( get_class( $e ) . ': ' . $e->getMessage(), E_USER_WARNING );
72  };
73  }
74 
79  public function bitNot( $field ) {
80  return "(~$field)";
81  }
82 
87  public function bitAnd( $fieldLeft, $fieldRight ) {
88  return "($fieldLeft & $fieldRight)";
89  }
90 
95  public function bitOr( $fieldLeft, $fieldRight ) {
96  return "($fieldLeft | $fieldRight)";
97  }
98 
103  public function addIdentifierQuotes( $s ) {
104  return '"' . str_replace( '"', '""', $s ) . '"';
105  }
106 
110  public function buildGreatest( $fields, $values ) {
111  return $this->buildSuperlative( 'GREATEST', $fields, $values );
112  }
113 
117  public function buildLeast( $fields, $values ) {
118  return $this->buildSuperlative( 'LEAST', $fields, $values );
119  }
120 
135  protected function buildSuperlative( $sqlfunc, $fields, $values ) {
136  $fields = is_array( $fields ) ? $fields : [ $fields ];
137  $values = is_array( $values ) ? $values : [ $values ];
138 
139  $encValues = [];
140  foreach ( $fields as $alias => $field ) {
141  if ( is_int( $alias ) ) {
142  $encValues[] = $this->addIdentifierQuotes( $field );
143  } else {
144  $encValues[] = $field; // expression
145  }
146  }
147  foreach ( $values as $value ) {
148  if ( is_int( $value ) || is_float( $value ) ) {
149  $encValues[] = $value;
150  } elseif ( is_string( $value ) ) {
151  $encValues[] = $this->quoter->addQuotes( $value );
152  } elseif ( $value === null ) {
153  throw new DBLanguageError( 'Null value in superlative' );
154  } else {
155  throw new DBLanguageError( 'Unexpected value type in superlative' );
156  }
157  }
158 
159  return $sqlfunc . '(' . implode( ',', $encValues ) . ')';
160  }
161 
162  public function buildComparison( string $op, array $conds ): string {
163  if ( !in_array( $op, [ '>', '>=', '<', '<=' ] ) ) {
164  throw new InvalidArgumentException( "Comparison operator must be one of '>', '>=', '<', '<='" );
165  }
166  if ( count( $conds ) === 0 ) {
167  throw new InvalidArgumentException( "Empty input" );
168  }
169 
170  // Construct a condition string by starting with the least significant part of the index, and
171  // adding more significant parts progressively to the left of the string.
172  //
173  // For example, given $conds = [ 'a' => 4, 'b' => 7, 'c' => 1 ], this will generate a condition
174  // like this:
175  //
176  // WHERE a > 4
177  // OR (a = 4 AND (b > 7
178  // OR (b = 7 AND (c > 1))))
179  //
180  // …which is equivalent to the following, which might be easier to understand:
181  //
182  // WHERE a > 4
183  // OR a = 4 AND b > 7
184  // OR a = 4 AND b = 7 AND c > 1
185  //
186  // …and also equivalent to the following, using tuple comparison syntax, which is most intuitive
187  // but apparently performs worse:
188  //
189  // WHERE (a, b, c) > (4, 7, 1)
190 
191  $sql = '';
192  foreach ( array_reverse( $conds ) as $field => $value ) {
193  if ( is_int( $field ) ) {
194  throw new InvalidArgumentException(
195  'Non-associative array passed to buildComparison() (typo?)'
196  );
197  }
198  $encValue = $this->quoter->addQuotes( $value );
199  if ( $sql === '' ) {
200  $sql = "$field $op $encValue";
201  // Change '>=' to '>' etc. for remaining fields, as the equality is handled separately
202  $op = rtrim( $op, '=' );
203  } else {
204  $sql = "$field $op $encValue OR ($field = $encValue AND ($sql))";
205  }
206  }
207  return $sql;
208  }
209 
210  public function makeList( array $a, $mode = self::LIST_COMMA ) {
211  $first = true;
212  $list = '';
213 
214  foreach ( $a as $field => $value ) {
215  if ( $first ) {
216  $first = false;
217  } else {
218  if ( $mode == self::LIST_AND ) {
219  $list .= ' AND ';
220  } elseif ( $mode == self::LIST_OR ) {
221  $list .= ' OR ';
222  } else {
223  $list .= ',';
224  }
225  }
226 
227  if ( ( $mode == self::LIST_AND || $mode == self::LIST_OR ) && is_numeric( $field ) ) {
228  $list .= "($value)";
229  } elseif ( $mode == self::LIST_SET && is_numeric( $field ) ) {
230  $list .= "$value";
231  } elseif (
232  ( $mode == self::LIST_AND || $mode == self::LIST_OR ) && is_array( $value )
233  ) {
234  // Remove null from array to be handled separately if found
235  $includeNull = false;
236  foreach ( array_keys( $value, null, true ) as $nullKey ) {
237  $includeNull = true;
238  unset( $value[$nullKey] );
239  }
240  if ( count( $value ) == 0 && !$includeNull ) {
241  throw new InvalidArgumentException(
242  __METHOD__ . ": empty input for field $field" );
243  } elseif ( count( $value ) == 0 ) {
244  // only check if $field is null
245  $list .= "$field IS NULL";
246  } else {
247  // IN clause contains at least one valid element
248  if ( $includeNull ) {
249  // Group subconditions to ensure correct precedence
250  $list .= '(';
251  }
252  if ( count( $value ) == 1 ) {
253  // Special-case single values, as IN isn't terribly efficient
254  // Don't necessarily assume the single key is 0; we don't
255  // enforce linear numeric ordering on other arrays here.
256  $value = array_values( $value )[0];
257  $list .= $field . " = " . $this->quoter->addQuotes( $value );
258  } else {
259  $list .= $field . " IN (" . $this->makeList( $value ) . ") ";
260  }
261  // if null present in array, append IS NULL
262  if ( $includeNull ) {
263  $list .= " OR $field IS NULL)";
264  }
265  }
266  } elseif ( $value === null ) {
267  if ( $mode == self::LIST_AND || $mode == self::LIST_OR ) {
268  $list .= "$field IS ";
269  } elseif ( $mode == self::LIST_SET ) {
270  $list .= "$field = ";
271  }
272  $list .= 'NULL';
273  } else {
274  if (
275  $mode == self::LIST_AND || $mode == self::LIST_OR || $mode == self::LIST_SET
276  ) {
277  $list .= "$field = ";
278  }
279  $list .= $mode == self::LIST_NAMES ? $value : $this->quoter->addQuotes( $value );
280  }
281  }
282 
283  return $list;
284  }
285 
286  public function makeWhereFrom2d( $data, $baseKey, $subKey ) {
287  $conds = [];
288  foreach ( $data as $base => $sub ) {
289  if ( count( $sub ) ) {
290  $conds[] = $this->makeList(
291  [ $baseKey => $base, $subKey => array_map( 'strval', array_keys( $sub ) ) ],
293  );
294  }
295  }
296 
297  if ( !$conds ) {
298  throw new InvalidArgumentException( "Data for $baseKey and $subKey must be non-empty" );
299  }
300 
301  return $this->makeList( $conds, self::LIST_OR );
302  }
303 
304  public function factorConds( $condsArray ) {
305  if ( count( $condsArray ) === 0 ) {
306  throw new InvalidArgumentException(
307  __METHOD__ . ": empty condition array" );
308  }
309  $condsByFieldSet = [];
310  foreach ( $condsArray as $conds ) {
311  if ( !count( $conds ) ) {
312  throw new InvalidArgumentException(
313  __METHOD__ . ": empty condition subarray" );
314  }
315  $fieldKey = implode( ',', array_keys( $conds ) );
316  $condsByFieldSet[$fieldKey][] = $conds;
317  }
318  $result = '';
319  foreach ( $condsByFieldSet as $conds ) {
320  if ( $result !== '' ) {
321  $result .= ' OR ';
322  }
323  $result .= $this->factorCondsWithCommonFields( $conds );
324  }
325  return $result;
326  }
327 
335  private function factorCondsWithCommonFields( $condsArray ) {
336  $first = $condsArray[array_key_first( $condsArray )];
337  if ( count( $first ) === 1 ) {
338  // IN clause
339  $field = array_key_first( $first );
340  $values = [];
341  foreach ( $condsArray as $conds ) {
342  $values[] = $conds[$field];
343  }
344  return $this->makeList( [ $field => $values ], self::LIST_AND );
345  }
346 
347  $field1 = array_key_first( $first );
348  $nullExpressions = [];
349  $expressionsByField1 = [];
350  foreach ( $condsArray as $conds ) {
351  $value1 = $conds[$field1];
352  unset( $conds[$field1] );
353  if ( $value1 === null ) {
354  $nullExpressions[] = $conds;
355  } else {
356  $expressionsByField1[$value1][] = $conds;
357  }
358 
359  }
360  $wrap = false;
361  $result = '';
362  foreach ( $expressionsByField1 as $value1 => $expressions ) {
363  if ( $result !== '' ) {
364  $result .= ' OR ';
365  $wrap = true;
366  }
367  $factored = $this->factorCondsWithCommonFields( $expressions );
368  $result .= "($field1 = " . $this->quoter->addQuotes( $value1 ) .
369  " AND $factored)";
370  }
371  if ( count( $nullExpressions ) ) {
372  $factored = $this->factorCondsWithCommonFields( $nullExpressions );
373  if ( $result !== '' ) {
374  $result .= ' OR ';
375  $wrap = true;
376  }
377  $result .= "($field1 IS NULL AND $factored)";
378  }
379  if ( $wrap ) {
380  return "($result)";
381  } else {
382  return $result;
383  }
384  }
385 
390  public function buildConcat( $stringList ) {
391  return 'CONCAT(' . implode( ',', $stringList ) . ')';
392  }
393 
398  public function limitResult( $sql, $limit, $offset = false ) {
399  if ( !is_numeric( $limit ) ) {
400  throw new DBLanguageError(
401  "Invalid non-numeric limit passed to " . __METHOD__
402  );
403  }
404  // This version works in MySQL and SQLite. It will very likely need to be
405  // overridden for most other RDBMS subclasses.
406  return "$sql LIMIT "
407  . ( ( is_numeric( $offset ) && $offset != 0 ) ? "{$offset}," : "" )
408  . "{$limit} ";
409  }
410 
417  public function escapeLikeInternal( $s, $escapeChar = '`' ) {
418  return str_replace(
419  [ $escapeChar, '%', '_' ],
420  [ "{$escapeChar}{$escapeChar}", "{$escapeChar}%", "{$escapeChar}_" ],
421  $s
422  );
423  }
424 
429  public function buildLike( $param, ...$params ) {
430  if ( is_array( $param ) ) {
431  $params = $param;
432  } else {
433  $params = func_get_args();
434  }
435 
436  $s = '';
437 
438  // We use ` instead of \ as the default LIKE escape character, since addQuotes()
439  // may escape backslashes, creating problems of double escaping. The `
440  // character has good cross-DBMS compatibility, avoiding special operators
441  // in MS SQL like ^ and %
442  $escapeChar = '`';
443 
444  foreach ( $params as $value ) {
445  if ( $value instanceof LikeMatch ) {
446  $s .= $value->toString();
447  } else {
448  $s .= $this->escapeLikeInternal( $value, $escapeChar );
449  }
450  }
451 
452  return ' LIKE ' .
453  $this->quoter->addQuotes( $s ) . ' ESCAPE ' . $this->quoter->addQuotes( $escapeChar ) . ' ';
454  }
455 
456  public function anyChar() {
457  return new LikeMatch( '_' );
458  }
459 
460  public function anyString() {
461  return new LikeMatch( '%' );
462  }
463 
468  public function unionSupportsOrderAndLimit() {
469  return true; // True for almost every DB supported
470  }
471 
476  public function unionQueries( $sqls, $all, $options = [] ) {
477  $glue = $all ? ') UNION ALL (' : ') UNION (';
478 
479  $sql = '(' . implode( $glue, $sqls ) . ')';
480  if ( !$this->unionSupportsOrderAndLimit() ) {
481  return $sql;
482  }
483  $sql = $sql . $this->makeOrderBy( $options );
484  $limit = $options['LIMIT'] ?? null;
485  $offset = $options['OFFSET'] ?? false;
486  if ( $limit !== null ) {
487  $sql = $this->limitResult( $sql, $limit, $offset );
488  }
489 
490  return $sql;
491  }
492 
497  public function conditional( $cond, $caseTrueExpression, $caseFalseExpression ) {
498  if ( is_array( $cond ) ) {
499  $cond = $this->makeList( $cond, self::LIST_AND );
500  }
501 
502  return "(CASE WHEN $cond THEN $caseTrueExpression ELSE $caseFalseExpression END)";
503  }
504 
509  public function strreplace( $orig, $old, $new ) {
510  return "REPLACE({$orig}, {$old}, {$new})";
511  }
512 
517  public function timestamp( $ts = 0 ) {
518  $t = new ConvertibleTimestamp( $ts );
519  // Let errors bubble up to avoid putting garbage in the DB
520  return $t->getTimestamp( TS_MW );
521  }
522 
523  public function timestampOrNull( $ts = null ) {
524  if ( $ts === null ) {
525  return null;
526  } else {
527  return $this->timestamp( $ts );
528  }
529  }
530 
535  public function getInfinity() {
536  return 'infinity';
537  }
538 
539  public function encodeExpiry( $expiry ) {
540  return ( $expiry == '' || $expiry == 'infinity' || $expiry == $this->getInfinity() )
541  ? $this->getInfinity()
542  : $this->timestamp( $expiry );
543  }
544 
545  public function decodeExpiry( $expiry, $format = TS_MW ) {
546  if ( $expiry == '' || $expiry == 'infinity' || $expiry == $this->getInfinity() ) {
547  return 'infinity';
548  }
549 
550  return ConvertibleTimestamp::convert( $format, $expiry );
551  }
552 
557  public function buildSubstring( $input, $startPosition, $length = null ) {
558  $this->assertBuildSubstringParams( $startPosition, $length );
559  $functionBody = "$input FROM $startPosition";
560  if ( $length !== null ) {
561  $functionBody .= " FOR $length";
562  }
563  return 'SUBSTRING(' . $functionBody . ')';
564  }
565 
578  protected function assertBuildSubstringParams( $startPosition, $length ) {
579  if ( $startPosition === 0 ) {
580  // The DBMSs we support use 1-based indexing here.
581  throw new InvalidArgumentException( 'Use 1 as $startPosition for the beginning of the string' );
582  }
583  if ( !is_int( $startPosition ) || $startPosition < 0 ) {
584  throw new InvalidArgumentException(
585  '$startPosition must be a positive integer'
586  );
587  }
588  if ( !( is_int( $length ) && $length >= 0 || $length === null ) ) {
589  throw new InvalidArgumentException(
590  '$length must be null or an integer greater than or equal to 0'
591  );
592  }
593  }
594 
599  public function buildStringCast( $field ) {
600  // In theory this should work for any standards-compliant
601  // SQL implementation, although it may not be the best way to do it.
602  return "CAST( $field AS CHARACTER )";
603  }
604 
609  public function buildIntegerCast( $field ) {
610  return 'CAST( ' . $field . ' AS INTEGER )';
611  }
612 
617  public function implicitOrderby() {
618  return true;
619  }
620 
629  public function indexName( $index ) {
630  return $this->indexAliases[$index] ?? $index;
631  }
632 
637  public function setTableAliases( array $aliases ) {
638  $this->tableAliases = $aliases;
639  }
640 
645  public function setIndexAliases( array $aliases ) {
646  $this->indexAliases = $aliases;
647  }
648 
652  public function getTableAliases() {
653  return $this->tableAliases;
654  }
655 
656  public function setPrefix( $prefix ) {
657  $this->currentDomain = new DatabaseDomain(
658  $this->currentDomain->getDatabase(),
659  $this->currentDomain->getSchema(),
660  $prefix
661  );
662  }
663 
664  public function setCurrentDomain( DatabaseDomain $currentDomain ) {
665  $this->currentDomain = $currentDomain;
666  }
667 
672  public function selectSQLText(
673  $table, $vars, $conds = '', $fname = __METHOD__, $options = [], $join_conds = []
674  ) {
675  if ( is_array( $table ) ) {
676  $tables = $table;
677  } elseif ( $table === '' || $table === null || $table === false ) {
678  $tables = [];
679  } elseif ( is_string( $table ) ) {
680  $tables = [ $table ];
681  } else {
682  throw new DBLanguageError( __METHOD__ . ' called with incorrect table parameter' );
683  }
684 
685  if ( is_array( $vars ) ) {
686  $fields = implode( ',', $this->fieldNamesWithAlias( $vars ) );
687  } else {
688  $fields = $vars;
689  }
690 
691  $options = (array)$options;
692 
693  $useIndexByTable = $options['USE INDEX'] ?? [];
694  if ( !is_array( $useIndexByTable ) ) {
695  if ( count( $tables ) <= 1 ) {
696  $useIndexByTable = [ reset( $tables ) => $useIndexByTable ];
697  } else {
698  $e = new DBLanguageError( __METHOD__ . " got ambiguous USE INDEX ($fname)" );
699  ( $this->errorLogger )( $e );
700  }
701  }
702 
703  $ignoreIndexByTable = $options['IGNORE INDEX'] ?? [];
704  if ( !is_array( $ignoreIndexByTable ) ) {
705  if ( count( $tables ) <= 1 ) {
706  $ignoreIndexByTable = [ reset( $tables ) => $ignoreIndexByTable ];
707  } else {
708  $e = new DBLanguageError( __METHOD__ . " got ambiguous IGNORE INDEX ($fname)" );
709  ( $this->errorLogger )( $e );
710  }
711  }
712 
713  if (
714  $this->selectOptionsIncludeLocking( $options ) &&
715  $this->selectFieldsOrOptionsAggregate( $vars, $options )
716  ) {
717  // Some DB types (e.g. postgres) disallow FOR UPDATE with aggregate
718  // functions. Discourage use of such queries to encourage compatibility.
719  $this->logger->warning(
720  __METHOD__ . ": aggregation used with a locking SELECT ($fname)"
721  );
722  }
723 
724  if ( count( $tables ) ) {
725  $from = ' FROM ' . $this->tableNamesWithIndexClauseOrJOIN(
726  $tables,
727  $useIndexByTable,
728  $ignoreIndexByTable,
729  $join_conds
730  );
731  } else {
732  $from = '';
733  }
734 
735  [ $startOpts, $preLimitTail, $postLimitTail ] = $this->makeSelectOptions( $options );
736 
737  if ( is_array( $conds ) ) {
738  $where = $this->makeList( $conds, self::LIST_AND );
739  } elseif ( $conds === null || $conds === false ) {
740  $where = '';
741  $this->logger->warning(
742  __METHOD__
743  . ' called from '
744  . $fname
745  . ' with incorrect parameters: $conds must be a string or an array',
746  [ 'db_log_category' => 'sql' ]
747  );
748  } elseif ( is_string( $conds ) ) {
749  $where = $conds;
750  } else {
751  throw new DBLanguageError( __METHOD__ . ' called with incorrect parameters' );
752  }
753 
754  // Keep historical extra spaces after FROM to avoid testing failures
755  if ( $where === '' || $where === '*' ) {
756  $sql = "SELECT $startOpts $fields $from $preLimitTail";
757  } else {
758  $sql = "SELECT $startOpts $fields $from WHERE $where $preLimitTail";
759  }
760 
761  if ( isset( $options['LIMIT'] ) ) {
762  $sql = $this->limitResult( $sql, $options['LIMIT'], $options['OFFSET'] ?? false );
763  }
764  $sql = "$sql $postLimitTail";
765 
766  if ( isset( $options['EXPLAIN'] ) ) {
767  $sql = 'EXPLAIN ' . $sql;
768  }
769 
770  return $sql;
771  }
772 
777  private function selectOptionsIncludeLocking( $options ) {
778  $options = (array)$options;
779  foreach ( [ 'FOR UPDATE', 'LOCK IN SHARE MODE' ] as $lock ) {
780  if ( in_array( $lock, $options, true ) ) {
781  return true;
782  }
783  }
784 
785  return false;
786  }
787 
793  private function selectFieldsOrOptionsAggregate( $fields, $options ) {
794  foreach ( (array)$options as $key => $value ) {
795  if ( is_string( $key ) ) {
796  if ( preg_match( '/^(?:GROUP BY|HAVING)$/i', $key ) ) {
797  return true;
798  }
799  } elseif ( is_string( $value ) ) {
800  if ( preg_match( '/^(?:DISTINCT|DISTINCTROW)$/i', $value ) ) {
801  return true;
802  }
803  }
804  }
805 
806  $regex = '/^(?:COUNT|MIN|MAX|SUM|GROUP_CONCAT|LISTAGG|ARRAY_AGG)\s*\\(/i';
807  foreach ( (array)$fields as $field ) {
808  if ( is_string( $field ) && preg_match( $regex, $field ) ) {
809  return true;
810  }
811  }
812 
813  return false;
814  }
815 
822  protected function fieldNamesWithAlias( $fields ) {
823  $retval = [];
824  foreach ( $fields as $alias => $field ) {
825  if ( is_numeric( $alias ) ) {
826  $alias = $field;
827  }
828  $retval[] = $this->fieldNameWithAlias( $field, $alias );
829  }
830 
831  return $retval;
832  }
833 
843  public function fieldNameWithAlias( $name, $alias = false ) {
844  if ( !$alias || (string)$alias === (string)$name ) {
845  return $name;
846  } else {
847  return $name . ' AS ' . $this->addIdentifierQuotes( $alias ); // PostgreSQL needs AS
848  }
849  }
850 
862  $tables,
863  $use_index = [],
864  $ignore_index = [],
865  $join_conds = []
866  ) {
867  $ret = [];
868  $retJOIN = [];
869  $use_index = (array)$use_index;
870  $ignore_index = (array)$ignore_index;
871  $join_conds = (array)$join_conds;
872 
873  foreach ( $tables as $alias => $table ) {
874  if ( !is_string( $alias ) ) {
875  // No alias? Set it equal to the table name
876  $alias = $table;
877  }
878 
879  if ( is_array( $table ) ) {
880  // A parenthesized group
881  if ( count( $table ) > 1 ) {
882  $joinedTable = '(' .
883  $this->tableNamesWithIndexClauseOrJOIN(
884  $table, $use_index, $ignore_index, $join_conds ) . ')';
885  } else {
886  // Degenerate case
887  $innerTable = reset( $table );
888  $innerAlias = key( $table );
889  $joinedTable = $this->tableNameWithAlias(
890  $innerTable,
891  is_string( $innerAlias ) ? $innerAlias : $innerTable
892  );
893  }
894  } else {
895  $joinedTable = $this->tableNameWithAlias( $table, $alias );
896  }
897 
898  // Is there a JOIN clause for this table?
899  if ( isset( $join_conds[$alias] ) ) {
900  Assert::parameterType( 'array', $join_conds[$alias], "join_conds[$alias]" );
901  [ $joinType, $conds ] = $join_conds[$alias];
902  $tableClause = $this->normalizeJoinType( $joinType );
903  $tableClause .= ' ' . $joinedTable;
904  if ( isset( $use_index[$alias] ) ) { // has USE INDEX?
905  $use = $this->useIndexClause( implode( ',', (array)$use_index[$alias] ) );
906  if ( $use != '' ) {
907  $tableClause .= ' ' . $use;
908  }
909  }
910  if ( isset( $ignore_index[$alias] ) ) { // has IGNORE INDEX?
911  $ignore = $this->ignoreIndexClause(
912  implode( ',', (array)$ignore_index[$alias] ) );
913  if ( $ignore != '' ) {
914  $tableClause .= ' ' . $ignore;
915  }
916  }
917  $on = $this->makeList( (array)$conds, self::LIST_AND );
918  if ( $on != '' ) {
919  $tableClause .= ' ON (' . $on . ')';
920  }
921 
922  $retJOIN[] = $tableClause;
923  } elseif ( isset( $use_index[$alias] ) ) {
924  // Is there an INDEX clause for this table?
925  $tableClause = $joinedTable;
926  $tableClause .= ' ' . $this->useIndexClause(
927  implode( ',', (array)$use_index[$alias] )
928  );
929 
930  $ret[] = $tableClause;
931  } elseif ( isset( $ignore_index[$alias] ) ) {
932  // Is there an INDEX clause for this table?
933  $tableClause = $joinedTable;
934  $tableClause .= ' ' . $this->ignoreIndexClause(
935  implode( ',', (array)$ignore_index[$alias] )
936  );
937 
938  $ret[] = $tableClause;
939  } else {
940  $tableClause = $joinedTable;
941 
942  $ret[] = $tableClause;
943  }
944  }
945 
946  // We can't separate explicit JOIN clauses with ',', use ' ' for those
947  $implicitJoins = implode( ',', $ret );
948  $explicitJoins = implode( ' ', $retJOIN );
949 
950  // Compile our final table clause
951  return implode( ' ', [ $implicitJoins, $explicitJoins ] );
952  }
953 
962  protected function normalizeJoinType( string $joinType ) {
963  switch ( strtoupper( $joinType ) ) {
964  case 'JOIN':
965  case 'INNER JOIN':
966  return 'JOIN';
967 
968  case 'LEFT JOIN':
969  return 'LEFT JOIN';
970 
971  case 'STRAIGHT_JOIN':
972  case 'STRAIGHT JOIN':
973  // MySQL only
974  return 'JOIN';
975 
976  default:
977  return $joinType;
978  }
979  }
980 
992  protected function tableNameWithAlias( $table, $alias = false ) {
993  if ( is_string( $table ) ) {
994  $quotedTable = $this->tableName( $table );
995  } elseif ( $table instanceof Subquery ) {
996  $quotedTable = (string)$table;
997  } else {
998  throw new InvalidArgumentException( "Table must be a string or Subquery" );
999  }
1000 
1001  if ( $alias === false || $alias === $table ) {
1002  if ( $table instanceof Subquery ) {
1003  throw new InvalidArgumentException( "Subquery table missing alias" );
1004  }
1005 
1006  return $quotedTable;
1007  } else {
1008  return $quotedTable . ' ' . $this->addIdentifierQuotes( $alias );
1009  }
1010  }
1011 
1016  public function tableName( $name, $format = 'quoted' ) {
1017  if ( $name instanceof Subquery ) {
1018  throw new DBLanguageError(
1019  __METHOD__ . ': got Subquery instance when expecting a string'
1020  );
1021  }
1022 
1023  # Skip the entire process when we have a string quoted on both ends.
1024  # Note that we check the end so that we will still quote any use of
1025  # use of `database`.table. But won't break things if someone wants
1026  # to query a database table with a dot in the name.
1027  if ( $this->isQuotedIdentifier( $name ) ) {
1028  return $name;
1029  }
1030 
1031  # Lets test for any bits of text that should never show up in a table
1032  # name. Basically anything like JOIN or ON which are actually part of
1033  # SQL queries, but may end up inside of the table value to combine
1034  # sql. Such as how the API is doing.
1035  # Note that we use a whitespace test rather than a \b test to avoid
1036  # any remote case where a word like on may be inside of a table name
1037  # surrounded by symbols which may be considered word breaks.
1038  if ( preg_match( '/(^|\s)(DISTINCT|JOIN|ON|AS)(\s|$)/i', $name ) !== 0 ) {
1039  $this->logger->warning(
1040  __METHOD__ . ": use of subqueries is not supported this way",
1041  [
1042  'exception' => new RuntimeException(),
1043  'db_log_category' => 'sql',
1044  ]
1045  );
1046 
1047  return $name;
1048  }
1049 
1050  # Split database and table into proper variables.
1051  [ $database, $schema, $prefix, $table ] = $this->qualifiedTableComponents( $name );
1052 
1053  # Quote $table and apply the prefix if not quoted.
1054  # $tableName might be empty if this is called from Database::replaceVars()
1055  $tableName = "{$prefix}{$table}";
1056  if ( $format === 'quoted'
1057  && !$this->isQuotedIdentifier( $tableName )
1058  && $tableName !== ''
1059  ) {
1060  $tableName = $this->addIdentifierQuotes( $tableName );
1061  }
1062 
1063  # Quote $schema and $database and merge them with the table name if needed
1064  $tableName = $this->prependDatabaseOrSchema( $schema, $tableName, $format );
1065  $tableName = $this->prependDatabaseOrSchema( $database, $tableName, $format );
1066 
1067  return $tableName;
1068  }
1069 
1076  public function qualifiedTableComponents( $name ) {
1077  # We reverse the explode so that database.table and table both output the correct table.
1078  $dbDetails = explode( '.', $name, 3 );
1079  if ( $this->currentDomain ) {
1080  $currentDomainPrefix = $this->currentDomain->getTablePrefix();
1081  } else {
1082  $currentDomainPrefix = null;
1083  }
1084  if ( count( $dbDetails ) == 3 ) {
1085  [ $database, $schema, $table ] = $dbDetails;
1086  # We don't want any prefix added in this case
1087  $prefix = '';
1088  } elseif ( count( $dbDetails ) == 2 ) {
1089  [ $database, $table ] = $dbDetails;
1090  # We don't want any prefix added in this case
1091  $prefix = '';
1092  # In dbs that support it, $database may actually be the schema
1093  # but that doesn't affect any of the functionality here
1094  $schema = '';
1095  } else {
1096  [ $table ] = $dbDetails;
1097  if ( isset( $this->tableAliases[$table] ) ) {
1098  $database = $this->tableAliases[$table]['dbname'];
1099  $schema = is_string( $this->tableAliases[$table]['schema'] )
1100  ? $this->tableAliases[$table]['schema']
1101  : $this->relationSchemaQualifier();
1102  $prefix = is_string( $this->tableAliases[$table]['prefix'] )
1103  ? $this->tableAliases[$table]['prefix']
1104  : $currentDomainPrefix;
1105  } else {
1106  $database = '';
1107  $schema = $this->relationSchemaQualifier(); # Default schema
1108  $prefix = $currentDomainPrefix; # Default prefix
1109  }
1110  }
1111 
1112  return [ $database, $schema, $prefix, $table ];
1113  }
1114 
1119  protected function relationSchemaQualifier() {
1120  if ( $this->currentDomain ) {
1121  return $this->currentDomain->getSchema();
1122  }
1123  return null;
1124  }
1125 
1132  private function prependDatabaseOrSchema( $namespace, $relation, $format ) {
1133  if ( $namespace !== null && $namespace !== '' ) {
1134  if ( $format === 'quoted' && !$this->isQuotedIdentifier( $namespace ) ) {
1135  $namespace = $this->addIdentifierQuotes( $namespace );
1136  }
1137  $relation = $namespace . '.' . $relation;
1138  }
1139 
1140  return $relation;
1141  }
1142 
1143  public function tableNames( ...$tables ) {
1144  $retVal = [];
1145 
1146  foreach ( $tables as $name ) {
1147  $retVal[$name] = $this->tableName( $name );
1148  }
1149 
1150  return $retVal;
1151  }
1152 
1153  public function tableNamesN( ...$tables ) {
1154  $retVal = [];
1155 
1156  foreach ( $tables as $name ) {
1157  $retVal[] = $this->tableName( $name );
1158  }
1159 
1160  return $retVal;
1161  }
1162 
1173  public function isQuotedIdentifier( $name ) {
1174  return strlen( $name ) > 1 && $name[0] === '"' && $name[-1] === '"';
1175  }
1176 
1190  public function useIndexClause( $index ) {
1191  return '';
1192  }
1193 
1203  public function ignoreIndexClause( $index ) {
1204  return '';
1205  }
1206 
1218  protected function makeSelectOptions( array $options ) {
1219  $preLimitTail = $postLimitTail = '';
1220  $startOpts = '';
1221 
1222  $noKeyOptions = [];
1223 
1224  foreach ( $options as $key => $option ) {
1225  if ( is_numeric( $key ) ) {
1226  $noKeyOptions[$option] = true;
1227  }
1228  }
1229 
1230  $preLimitTail .= $this->makeGroupByWithHaving( $options );
1231 
1232  $preLimitTail .= $this->makeOrderBy( $options );
1233 
1234  if ( isset( $noKeyOptions['FOR UPDATE'] ) ) {
1235  $postLimitTail .= ' FOR UPDATE';
1236  }
1237 
1238  if ( isset( $noKeyOptions['LOCK IN SHARE MODE'] ) ) {
1239  $postLimitTail .= ' LOCK IN SHARE MODE';
1240  }
1241 
1242  if ( isset( $noKeyOptions['DISTINCT'] ) || isset( $noKeyOptions['DISTINCTROW'] ) ) {
1243  $startOpts .= 'DISTINCT';
1244  }
1245 
1246  # Various MySQL extensions
1247  if ( isset( $noKeyOptions['STRAIGHT_JOIN'] ) ) {
1248  $startOpts .= ' /*! STRAIGHT_JOIN */';
1249  }
1250 
1251  if ( isset( $noKeyOptions['SQL_BIG_RESULT'] ) ) {
1252  $startOpts .= ' SQL_BIG_RESULT';
1253  }
1254 
1255  if ( isset( $noKeyOptions['SQL_BUFFER_RESULT'] ) ) {
1256  $startOpts .= ' SQL_BUFFER_RESULT';
1257  }
1258 
1259  if ( isset( $noKeyOptions['SQL_SMALL_RESULT'] ) ) {
1260  $startOpts .= ' SQL_SMALL_RESULT';
1261  }
1262 
1263  if ( isset( $noKeyOptions['SQL_CALC_FOUND_ROWS'] ) ) {
1264  $startOpts .= ' SQL_CALC_FOUND_ROWS';
1265  }
1266 
1267  return [ $startOpts, $preLimitTail, $postLimitTail ];
1268  }
1269 
1278  protected function makeGroupByWithHaving( $options ) {
1279  $sql = '';
1280  if ( isset( $options['GROUP BY'] ) ) {
1281  $gb = is_array( $options['GROUP BY'] )
1282  ? implode( ',', $options['GROUP BY'] )
1283  : $options['GROUP BY'];
1284  $sql .= ' GROUP BY ' . $gb;
1285  }
1286  if ( isset( $options['HAVING'] ) ) {
1287  $having = is_array( $options['HAVING'] )
1288  ? $this->makeList( $options['HAVING'], self::LIST_AND )
1289  : $options['HAVING'];
1290  $sql .= ' HAVING ' . $having;
1291  }
1292 
1293  return $sql;
1294  }
1295 
1304  protected function makeOrderBy( $options ) {
1305  if ( isset( $options['ORDER BY'] ) ) {
1306  $ob = is_array( $options['ORDER BY'] )
1307  ? implode( ',', $options['ORDER BY'] )
1308  : $options['ORDER BY'];
1309 
1310  return ' ORDER BY ' . $ob;
1311  }
1312 
1313  return '';
1314  }
1315 
1320  public function buildGroupConcatField(
1321  $delim, $table, $field, $conds = '', $join_conds = []
1322  ) {
1323  $fld = "GROUP_CONCAT($field SEPARATOR " . $this->quoter->addQuotes( $delim ) . ')';
1324 
1325  return '(' . $this->selectSQLText( $table, $fld, $conds, __METHOD__, [], $join_conds ) . ')';
1326  }
1327 
1328  public function buildSelectSubquery(
1329  $table, $vars, $conds = '', $fname = __METHOD__,
1330  $options = [], $join_conds = []
1331  ) {
1332  return new Subquery(
1333  $this->selectSQLText( $table, $vars, $conds, $fname, $options, $join_conds )
1334  );
1335  }
1336 
1337  public function insertSqlText( $table, array $rows ) {
1338  $encTable = $this->tableName( $table );
1339  [ $sqlColumns, $sqlTuples ] = $this->makeInsertLists( $rows );
1340 
1341  return [
1342  "INSERT INTO $encTable ($sqlColumns) VALUES $sqlTuples",
1343  "INSERT INTO $encTable ($sqlColumns) VALUES '?'"
1344  ];
1345  }
1346 
1359  public function makeInsertLists( array $rows, $aliasPrefix = '', array $typeByColumn = [] ) {
1360  $firstRow = $rows[0];
1361  if ( !is_array( $firstRow ) || !$firstRow ) {
1362  throw new DBLanguageError( 'Got an empty row list or empty row' );
1363  }
1364  // List of columns that define the value tuple ordering
1365  $tupleColumns = array_keys( $firstRow );
1366 
1367  $valueTuples = [];
1368  foreach ( $rows as $row ) {
1369  $rowColumns = array_keys( $row );
1370  // VALUES(...) requires a uniform correspondence of (column => value)
1371  if ( $rowColumns !== $tupleColumns ) {
1372  throw new DBLanguageError(
1373  'Got row columns (' . implode( ', ', $rowColumns ) . ') ' .
1374  'instead of expected (' . implode( ', ', $tupleColumns ) . ')'
1375  );
1376  }
1377  // Make the value tuple that defines this row
1378  $valueTuples[] = '(' . $this->makeList( $row, self::LIST_COMMA ) . ')';
1379  }
1380 
1381  $magicAliasFields = [];
1382  foreach ( $tupleColumns as $column ) {
1383  $magicAliasFields[] = $aliasPrefix . $column;
1384  }
1385 
1386  return [
1387  $this->makeList( $tupleColumns, self::LIST_NAMES ),
1388  implode( ',', $valueTuples ),
1389  $this->makeList( $magicAliasFields, self::LIST_NAMES )
1390  ];
1391  }
1392 
1393  public function insertNonConflictingSqlText( $table, array $rows ) {
1394  $encTable = $this->tableName( $table );
1395  [ $sqlColumns, $sqlTuples ] = $this->makeInsertLists( $rows );
1396  [ $sqlVerb, $sqlOpts ] = $this->makeInsertNonConflictingVerbAndOptions();
1397 
1398  return [
1399  rtrim( "$sqlVerb $encTable ($sqlColumns) VALUES $sqlTuples $sqlOpts" ),
1400  rtrim( "$sqlVerb $encTable ($sqlColumns) VALUES '?' $sqlOpts" )
1401  ];
1402  }
1403 
1410  return [ 'INSERT IGNORE INTO', '' ];
1411  }
1412 
1413  public function insertSelectNativeSqlText(
1414  $destTable,
1415  $srcTable,
1416  array $varMap,
1417  $conds,
1418  $fname,
1419  array $insertOptions,
1420  array $selectOptions,
1421  $selectJoinConds
1422  ) {
1423  [ $sqlVerb, $sqlOpts ] = $this->isFlagInOptions( 'IGNORE', $insertOptions )
1424  ? $this->makeInsertNonConflictingVerbAndOptions()
1425  : [ 'INSERT INTO', '' ];
1426  $encDstTable = $this->tableName( $destTable );
1427  $sqlDstColumns = implode( ',', array_keys( $varMap ) );
1428  $selectSql = $this->selectSQLText(
1429  $srcTable,
1430  array_values( $varMap ),
1431  $conds,
1432  $fname,
1433  $selectOptions,
1434  $selectJoinConds
1435  );
1436 
1437  return rtrim( "$sqlVerb $encDstTable ($sqlDstColumns) $selectSql $sqlOpts" );
1438  }
1439 
1446  public function isFlagInOptions( $option, array $options ) {
1447  foreach ( array_keys( $options, $option, true ) as $k ) {
1448  if ( is_int( $k ) ) {
1449  return true;
1450  }
1451  }
1452 
1453  return false;
1454  }
1455 
1463  public function makeKeyCollisionCondition( array $rows, array $uniqueKey ) {
1464  if ( !$rows ) {
1465  throw new DBLanguageError( "Empty row array" );
1466  } elseif ( !$uniqueKey ) {
1467  throw new DBLanguageError( "Empty unique key array" );
1468  }
1469 
1470  if ( count( $uniqueKey ) == 1 ) {
1471  // Use a simple IN(...) clause
1472  $column = reset( $uniqueKey );
1473  $values = array_column( $rows, $column );
1474  if ( count( $values ) !== count( $rows ) ) {
1475  throw new DBLanguageError( "Missing values for unique key ($column)" );
1476  }
1477 
1478  return $this->makeList( [ $column => $values ], self::LIST_AND );
1479  }
1480 
1481  $nullByUniqueKeyColumn = array_fill_keys( $uniqueKey, null );
1482 
1483  $orConds = [];
1484  foreach ( $rows as $row ) {
1485  $rowKeyMap = array_intersect_key( $row, $nullByUniqueKeyColumn );
1486  if ( count( $rowKeyMap ) != count( $uniqueKey ) ) {
1487  throw new DBLanguageError(
1488  "Missing values for unique key (" . implode( ',', $uniqueKey ) . ")"
1489  );
1490  }
1491  $orConds[] = $this->makeList( $rowKeyMap, self::LIST_AND );
1492  }
1493 
1494  return count( $orConds ) > 1
1495  ? $this->makeList( $orConds, self::LIST_OR )
1496  : $orConds[0];
1497  }
1498 
1499  public function deleteJoinSqlText( $delTable, $joinTable, $delVar, $joinVar, $conds ) {
1500  if ( !$conds ) {
1501  throw new DBLanguageError( __METHOD__ . ' called with empty $conds' );
1502  }
1503 
1504  $delTable = $this->tableName( $delTable );
1505  $joinTable = $this->tableName( $joinTable );
1506  $sql = "DELETE FROM $delTable WHERE $delVar IN (SELECT $joinVar FROM $joinTable ";
1507  if ( $conds != '*' ) {
1508  $sql .= 'WHERE ' . $this->makeList( $conds, self::LIST_AND );
1509  }
1510  $sql .= ')';
1511 
1512  return $sql;
1513  }
1514 
1520  public function deleteSqlText( $table, $conds ) {
1521  $isCondValid = ( is_string( $conds ) || is_array( $conds ) ) && $conds;
1522  if ( !$isCondValid ) {
1523  throw new DBLanguageError( __METHOD__ . ' called with empty conditions' );
1524  }
1525 
1526  $encTable = $this->tableName( $table );
1527  $sql = "DELETE FROM $encTable";
1528 
1529  $condsSql = '';
1530  $cleanCondsSql = '';
1531  if ( $conds !== self::ALL_ROWS && $conds !== [ self::ALL_ROWS ] ) {
1532  $cleanCondsSql = ' WHERE ' . $this->scrubArray( $conds );
1533  if ( is_array( $conds ) ) {
1534  $conds = $this->makeList( $conds, self::LIST_AND );
1535  }
1536  $condsSql .= ' WHERE ' . $conds;
1537  }
1538  return new Query(
1539  $sql . $condsSql,
1540  self::QUERY_CHANGE_ROWS,
1541  'DELETE',
1542  $table,
1543  $sql . $cleanCondsSql
1544  );
1545  }
1546 
1547  private function scrubArray( $array, $listType = self::LIST_AND ) {
1548  if ( is_array( $array ) ) {
1549  $scrubbedArray = [];
1550  foreach ( $array as $key => $value ) {
1551  $scrubbedArray[$key] = '?';
1552  }
1553  return $this->makeList( $scrubbedArray, $listType );
1554  }
1555  return '?';
1556  }
1557 
1558  public function updateSqlText( $table, $set, $conds, $options ) {
1559  $isCondValid = ( is_string( $conds ) || is_array( $conds ) ) && $conds;
1560  if ( !$isCondValid ) {
1561  throw new DBLanguageError( __METHOD__ . ' called with empty conditions' );
1562  }
1563  $encTable = $this->tableName( $table );
1564  $opts = $this->makeUpdateOptions( $options );
1565  $sql = "UPDATE $opts $encTable";
1566  $condsSql = " SET " . $this->makeList( $set, self::LIST_SET );
1567  $cleanCondsSql = " SET " . $this->scrubArray( $set, self::LIST_SET );
1568 
1569  if ( $conds && $conds !== self::ALL_ROWS && $conds !== [ self::ALL_ROWS ] ) {
1570  $cleanCondsSql .= ' WHERE ' . $this->scrubArray( $conds );
1571  if ( is_array( $conds ) ) {
1572  $conds = $this->makeList( $conds, self::LIST_AND );
1573  }
1574  $condsSql .= ' WHERE ' . $conds;
1575  }
1576  return new Query(
1577  $sql . $condsSql,
1578  self::QUERY_CHANGE_ROWS,
1579  'UPDATE',
1580  $table,
1581  $sql . $cleanCondsSql
1582  );
1583  }
1584 
1592  protected function makeUpdateOptions( $options ) {
1593  $opts = $this->makeUpdateOptionsArray( $options );
1594 
1595  return implode( ' ', $opts );
1596  }
1597 
1605  protected function makeUpdateOptionsArray( $options ) {
1606  $options = $this->normalizeOptions( $options );
1607 
1608  $opts = [];
1609 
1610  if ( in_array( 'IGNORE', $options ) ) {
1611  $opts[] = 'IGNORE';
1612  }
1613 
1614  return $opts;
1615  }
1616 
1622  final public function normalizeOptions( $options ) {
1623  if ( is_array( $options ) ) {
1624  return $options;
1625  } elseif ( is_string( $options ) ) {
1626  return ( $options === '' ) ? [] : [ $options ];
1627  } else {
1628  throw new DBLanguageError( __METHOD__ . ': expected string or array' );
1629  }
1630  }
1631 
1632  public function dropTableSqlText( $table ) {
1633  // https://mariadb.com/kb/en/drop-table/
1634  // https://dev.mysql.com/doc/refman/8.0/en/drop-table.html
1635  // https://www.postgresql.org/docs/9.2/sql-truncate.html
1636  return "DROP TABLE " . $this->tableName( $table ) . " CASCADE";
1637  }
1638 
1643  public function getQueryVerb( $sql ) {
1644  return QueryBuilderFromRawSql::buildQuery( $sql, 0 )->getVerb();
1645  }
1646 
1660  public function isTransactableQuery( Query $sql ) {
1661  return !in_array(
1662  $sql->getVerb(),
1663  [
1664  'BEGIN',
1665  'ROLLBACK',
1666  'ROLLBACK TO SAVEPOINT',
1667  'COMMIT',
1668  'SET',
1669  'SHOW',
1670  'CREATE',
1671  'ALTER',
1672  'USE',
1673  'SHOW'
1674  ],
1675  true
1676  );
1677  }
1678 
1697  public function isWriteQuery( $sql, $flags ) {
1698  return QueryBuilderFromRawSql::buildQuery( $sql, $flags )->isWriteQuery();
1699  }
1700 
1701  public function buildExcludedValue( $column ) {
1702  /* @see Database::upsert() */
1703  // This can be treated like a single value since __VALS is a single row table
1704  return "(SELECT __$column FROM __VALS)";
1705  }
1706 
1707  public function savepointSqlText( $identifier ) {
1708  return 'SAVEPOINT ' . $this->addIdentifierQuotes( $identifier );
1709  }
1710 
1711  public function releaseSavepointSqlText( $identifier ) {
1712  return 'RELEASE SAVEPOINT ' . $this->addIdentifierQuotes( $identifier );
1713  }
1714 
1715  public function rollbackToSavepointSqlText( $identifier ) {
1716  return 'ROLLBACK TO SAVEPOINT ' . $this->addIdentifierQuotes( $identifier );
1717  }
1718 
1719  public function rollbackSqlText() {
1720  return 'ROLLBACK';
1721  }
1722 
1723  public function dispatchingInsertSqlText( $table, $rows, $options ) {
1724  $rows = $this->normalizeRowArray( $rows );
1725  if ( !$rows ) {
1726  return false;
1727  }
1728 
1729  $options = $this->normalizeOptions( $options );
1730  if ( $this->isFlagInOptions( 'IGNORE', $options ) ) {
1731  [ $sql, $cleanSql ] = $this->insertNonConflictingSqlText( $table, $rows );
1732  } else {
1733  [ $sql, $cleanSql ] = $this->insertSqlText( $table, $rows );
1734  }
1735  return new Query( $sql, self::QUERY_CHANGE_ROWS, 'INSERT', $table, $cleanSql );
1736  }
1737 
1743  final protected function normalizeRowArray( array $rowOrRows ) {
1744  if ( !$rowOrRows ) {
1745  $rows = [];
1746  } elseif ( isset( $rowOrRows[0] ) ) {
1747  $rows = $rowOrRows;
1748  } else {
1749  $rows = [ $rowOrRows ];
1750  }
1751 
1752  foreach ( $rows as $row ) {
1753  if ( !is_array( $row ) ) {
1754  throw new DBLanguageError( "Got non-array in row array" );
1755  } elseif ( !$row ) {
1756  throw new DBLanguageError( "Got empty array in row array" );
1757  }
1758  }
1759 
1760  return $rows;
1761  }
1762 
1771  final public function normalizeUpsertParams( $uniqueKeys, &$rows ) {
1772  $rows = $this->normalizeRowArray( $rows );
1773  if ( !$uniqueKeys ) {
1774  throw new DBLanguageError( 'No unique key specified for upsert/replace' );
1775  }
1776  $uniqueKey = $this->normalizeUpsertKeys( $uniqueKeys );
1777  $this->assertValidUpsertRowArray( $rows, $uniqueKey );
1778 
1779  return $uniqueKey;
1780  }
1781 
1788  final public function normalizeConditions( $conds, $fname ) {
1789  if ( $conds === null || $conds === false ) {
1790  $this->logger->warning(
1791  __METHOD__
1792  . ' called from '
1793  . $fname
1794  . ' with incorrect parameters: $conds must be a string or an array',
1795  [ 'db_log_category' => 'sql' ]
1796  );
1797  return [];
1798  } elseif ( $conds === '' ) {
1799  return [];
1800  }
1801 
1802  return is_array( $conds ) ? $conds : [ $conds ];
1803  }
1804 
1810  private function normalizeUpsertKeys( $uniqueKeys ) {
1811  if ( is_string( $uniqueKeys ) ) {
1812  return [ $uniqueKeys ];
1813  } elseif ( !is_array( $uniqueKeys ) ) {
1814  throw new DBLanguageError( 'Invalid unique key array' );
1815  } else {
1816  if ( count( $uniqueKeys ) !== 1 || !isset( $uniqueKeys[0] ) ) {
1817  throw new DBLanguageError(
1818  "The unique key array should contain a single unique index" );
1819  }
1820 
1821  $uniqueKey = $uniqueKeys[0];
1822  if ( is_string( $uniqueKey ) ) {
1823  // Passing a list of strings for single-column unique keys is too
1824  // easily confused with passing the columns of composite unique key
1825  $this->logger->warning( __METHOD__ .
1826  " called with deprecated parameter style: " .
1827  "the unique key array should be a string or array of string arrays",
1828  [
1829  'exception' => new RuntimeException(),
1830  'db_log_category' => 'sql',
1831  ] );
1832  return $uniqueKeys;
1833  } elseif ( is_array( $uniqueKey ) ) {
1834  return $uniqueKey;
1835  } else {
1836  throw new DBLanguageError( 'Invalid unique key array entry' );
1837  }
1838  }
1839  }
1840 
1846  final protected function assertValidUpsertRowArray( array $rows, array $uniqueKey ) {
1847  foreach ( $rows as $row ) {
1848  foreach ( $uniqueKey as $column ) {
1849  if ( !isset( $row[$column] ) ) {
1850  throw new DBLanguageError(
1851  "NULL/absent values for unique key (" . implode( ',', $uniqueKey ) . ")"
1852  );
1853  }
1854  }
1855  }
1856  }
1857 
1864  final public function assertValidUpsertSetArray(
1865  array $set,
1866  array $uniqueKey,
1867  array $rows
1868  ) {
1869  if ( !$set ) {
1870  throw new DBLanguageError( "Update assignment list can't be empty for upsert" );
1871  }
1872 
1873  // Sloppy callers might construct the SET array using the ROW array, leaving redundant
1874  // column definitions for unique key columns. Detect this for backwards compatibility.
1875  $soleRow = ( count( $rows ) == 1 ) ? reset( $rows ) : null;
1876  // Disallow value changes for any columns in the unique key. This avoids additional
1877  // insertion order dependencies that are unwieldy and difficult to implement efficiently
1878  // in PostgreSQL.
1879  foreach ( $set as $k => $v ) {
1880  if ( is_string( $k ) ) {
1881  // Key is a column name and value is a literal (e.g. string, int, null, ...)
1882  if ( in_array( $k, $uniqueKey, true ) ) {
1883  if ( $soleRow && array_key_exists( $k, $soleRow ) && $soleRow[$k] === $v ) {
1884  $this->logger->warning(
1885  __METHOD__ . " called with redundant assignment to column '$k'",
1886  [
1887  'exception' => new RuntimeException(),
1888  'db_log_category' => 'sql',
1889  ]
1890  );
1891  } else {
1892  throw new DBLanguageError(
1893  "Cannot reassign column '$k' since it belongs to the provided unique key"
1894  );
1895  }
1896  }
1897  } elseif ( preg_match( '/^([a-zA-Z0-9_]+)\s*=/', $v, $m ) ) {
1898  // Value is of the form "<unquoted alphanumeric column> = <SQL expression>"
1899  if ( in_array( $m[1], $uniqueKey, true ) ) {
1900  throw new DBLanguageError(
1901  "Cannot reassign column '{$m[1]}' since it belongs to the provided unique key"
1902  );
1903  }
1904  }
1905  }
1906  }
1907 
1912  final public function extractSingleFieldFromList( $var ) {
1913  if ( is_array( $var ) ) {
1914  if ( !$var ) {
1915  $column = null;
1916  } elseif ( count( $var ) == 1 ) {
1917  $column = $var[0] ?? reset( $var );
1918  } else {
1919  throw new DBLanguageError( __METHOD__ . ': got multiple columns' );
1920  }
1921  } else {
1922  $column = $var;
1923  }
1924 
1925  return $column;
1926  }
1927 
1928  public function setSchemaVars( $vars ) {
1929  $this->schemaVars = is_array( $vars ) ? $vars : null;
1930  }
1931 
1938  protected function getSchemaVars() {
1939  return $this->schemaVars ?? $this->getDefaultSchemaVars();
1940  }
1941 
1951  protected function getDefaultSchemaVars() {
1952  return [];
1953  }
1954 
1976  public function replaceVars( $ins ) {
1977  $vars = $this->getSchemaVars();
1978  return preg_replace_callback(
1979  '!
1980  /\* (\$wgDBprefix|[_i]) \*/ (\w*) | # 1-2. tableName, indexName
1981  \'\{\$ (\w+) }\' | # 3. addQuotes
1982  `\{\$ (\w+) }` | # 4. addIdentifierQuotes
1983  /\*\$ (\w+) \*/ # 5. leave unencoded
1984  !x',
1985  function ( $m ) use ( $vars ) {
1986  // Note: Because of <https://bugs.php.net/bug.php?id=51881>,
1987  // check for both nonexistent keys *and* the empty string.
1988  if ( isset( $m[1] ) && $m[1] !== '' ) {
1989  if ( $m[1] === 'i' ) {
1990  return $this->indexName( $m[2] );
1991  } else {
1992  return $this->tableName( $m[2] );
1993  }
1994  } elseif ( isset( $m[3] ) && $m[3] !== '' && array_key_exists( $m[3], $vars ) ) {
1995  return $this->quoter->addQuotes( $vars[$m[3]] );
1996  } elseif ( isset( $m[4] ) && $m[4] !== '' && array_key_exists( $m[4], $vars ) ) {
1997  return $this->addIdentifierQuotes( $vars[$m[4]] );
1998  } elseif ( isset( $m[5] ) && $m[5] !== '' && array_key_exists( $m[5], $vars ) ) {
1999  return $vars[$m[5]];
2000  } else {
2001  return $m[0];
2002  }
2003  },
2004  $ins
2005  );
2006  }
2007 
2008  public function lockSQLText( $lockName, $timeout ) {
2009  throw new RuntimeException( 'locking must be implemented in subclasses' );
2010  }
2011 
2012  public function lockIsFreeSQLText( $lockName ) {
2013  throw new RuntimeException( 'locking must be implemented in subclasses' );
2014  }
2015 
2016  public function unlockSQLText( $lockName ) {
2017  throw new RuntimeException( 'locking must be implemented in subclasses' );
2018  }
2019 }
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
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:50
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:60
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:52
ignoreIndexClause( $index)
IGNORE INDEX clause.
makeOrderBy( $options)
Returns an optional ORDER BY.
string[] $indexAliases
Current map of (index alias => index)
Definition: SQLPlatform.php:48
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:95
isTransactableQuery(Query $sql)
Determine whether a SQL statement is sensitive to isolation level.
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...
assertValidUpsertSetArray(array $set, array $uniqueKey, array $rows)
bitAnd( $fieldLeft, $fieldRight)
string
Definition: SQLPlatform.php:87
array[] $tableAliases
Current map of (table => (dbname, schema, prefix) map)
Definition: SQLPlatform.php:46
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)
unionQueries( $sqls, $all, $options=[])
Construct a UNION query.This is used for providing overload point for other DB abstractions not compa...
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)
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 "OR" condition with pairs from a two-dimensional array.
assertValidUpsertRowArray(array $rows, array $uniqueKey)
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.
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:58
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...
This is to contain any regex on SQL work and get rid of them eventually.
static buildQuery(string $sql, $flags, string $tablePrefix='')
Holds information on Query to be executed.
Definition: Query.php:31
Interface for query language.