MediaWiki master
SQLPlatform.php
Go to the documentation of this file.
1<?php
21
22use InvalidArgumentException;
23use Psr\Log\LoggerInterface;
24use Psr\Log\NullLogger;
25use RuntimeException;
26use Throwable;
27use Wikimedia\Assert\Assert;
38use Wikimedia\Timestamp\ConvertibleTimestamp;
39
47class SQLPlatform implements ISQLPlatform {
49 protected $tableAliases = [];
51 protected $indexAliases = [];
53 protected $currentDomain;
55 protected $schemaVars;
57 protected $quoter;
59 protected $logger;
61 protected $errorLogger;
62
63 public function __construct(
65 LoggerInterface $logger = null,
67 $errorLogger = null
68
69 ) {
70 $this->quoter = $quoter;
71 $this->logger = $logger ?? new NullLogger();
72 $this->currentDomain = $currentDomain ?: DatabaseDomain::newUnspecified();
73 $this->errorLogger = $errorLogger ?? static function ( Throwable $e ) {
74 trigger_error( get_class( $e ) . ': ' . $e->getMessage(), E_USER_WARNING );
75 };
76 }
77
78 public function bitNot( $field ) {
79 return "(~$field)";
80 }
81
82 public function bitAnd( $fieldLeft, $fieldRight ) {
83 return "($fieldLeft & $fieldRight)";
84 }
85
86 public function bitOr( $fieldLeft, $fieldRight ) {
87 return "($fieldLeft | $fieldRight)";
88 }
89
90 public function addIdentifierQuotes( $s ) {
91 if ( strcspn( $s, "\0\"`'." ) !== strlen( $s ) ) {
92 throw new DBLanguageError(
93 "Identifier must not contain quote, dot or null characters"
94 );
95 }
96 $quoteChar = $this->getIdentifierQuoteChar();
97 return $quoteChar . $s . $quoteChar;
98 }
99
104 protected function getIdentifierQuoteChar() {
105 return '"';
106 }
107
111 public function buildGreatest( $fields, $values ) {
112 return $this->buildSuperlative( 'GREATEST', $fields, $values );
113 }
114
118 public function buildLeast( $fields, $values ) {
119 return $this->buildSuperlative( 'LEAST', $fields, $values );
120 }
121
136 protected function buildSuperlative( $sqlfunc, $fields, $values ) {
137 $fields = is_array( $fields ) ? $fields : [ $fields ];
138 $values = is_array( $values ) ? $values : [ $values ];
139
140 $encValues = [];
141 foreach ( $fields as $alias => $field ) {
142 if ( is_int( $alias ) ) {
143 $encValues[] = $this->addIdentifierQuotes( $field );
144 } else {
145 $encValues[] = $field; // expression
146 }
147 }
148 foreach ( $values as $value ) {
149 if ( is_int( $value ) || is_float( $value ) ) {
150 $encValues[] = $value;
151 } elseif ( is_string( $value ) ) {
152 $encValues[] = $this->quoter->addQuotes( $value );
153 } elseif ( $value === null ) {
154 throw new DBLanguageError( 'Null value in superlative' );
155 } else {
156 throw new DBLanguageError( 'Unexpected value type in superlative' );
157 }
158 }
159
160 return $sqlfunc . '(' . implode( ',', $encValues ) . ')';
161 }
162
163 public function buildComparison( string $op, array $conds ): string {
164 if ( !in_array( $op, [ '>', '>=', '<', '<=' ] ) ) {
165 throw new InvalidArgumentException( "Comparison operator must be one of '>', '>=', '<', '<='" );
166 }
167 if ( count( $conds ) === 0 ) {
168 throw new InvalidArgumentException( "Empty input" );
169 }
170
171 // Construct a condition string by starting with the least significant part of the index, and
172 // adding more significant parts progressively to the left of the string.
173 //
174 // For example, given $conds = [ 'a' => 4, 'b' => 7, 'c' => 1 ], this will generate a condition
175 // like this:
176 //
177 // WHERE a > 4
178 // OR (a = 4 AND (b > 7
179 // OR (b = 7 AND (c > 1))))
180 //
181 // …which is equivalent to the following, which might be easier to understand:
182 //
183 // WHERE a > 4
184 // OR a = 4 AND b > 7
185 // OR a = 4 AND b = 7 AND c > 1
186 //
187 // …and also equivalent to the following, using tuple comparison syntax, which is most intuitive
188 // but apparently performs worse:
189 //
190 // WHERE (a, b, c) > (4, 7, 1)
191
192 $sql = '';
193 foreach ( array_reverse( $conds ) as $field => $value ) {
194 if ( is_int( $field ) ) {
195 throw new InvalidArgumentException(
196 'Non-associative array passed to buildComparison() (typo?)'
197 );
198 }
199 $encValue = $this->quoter->addQuotes( $value );
200 if ( $sql === '' ) {
201 $sql = "$field $op $encValue";
202 // Change '>=' to '>' etc. for remaining fields, as the equality is handled separately
203 $op = rtrim( $op, '=' );
204 } else {
205 $sql = "$field $op $encValue OR ($field = $encValue AND ($sql))";
206 }
207 }
208 return $sql;
209 }
210
211 public function makeList( array $a, $mode = self::LIST_COMMA ) {
212 $first = true;
213 $list = '';
214 $keyWarning = null;
215
216 foreach ( $a as $field => $value ) {
217 if ( $first ) {
218 $first = false;
219 } else {
220 if ( $mode == self::LIST_AND ) {
221 $list .= ' AND ';
222 } elseif ( $mode == self::LIST_OR ) {
223 $list .= ' OR ';
224 } else {
225 $list .= ',';
226 }
227 }
228
229 if ( ( $mode == self::LIST_AND || $mode == self::LIST_OR ) && is_numeric( $field ) ) {
230 if ( $value instanceof IExpression ) {
231 $list .= "(" . $value->toSql( $this->quoter ) . ")";
232 } elseif ( is_array( $value ) ) {
233 throw new InvalidArgumentException( __METHOD__ . ": unexpected array value without key" );
234 } elseif ( $value instanceof RawSQLValue ) {
235 throw new InvalidArgumentException( __METHOD__ . ": unexpected raw value without key" );
236 } else {
237 $list .= "($value)";
238 }
239 } elseif ( $value instanceof IExpression ) {
240 if ( $mode == self::LIST_AND || $mode == self::LIST_OR ) {
241 throw new InvalidArgumentException( __METHOD__ . ": unexpected key $field for IExpression value" );
242 } else {
243 throw new InvalidArgumentException( __METHOD__ . ": unexpected IExpression outside WHERE clause" );
244 }
245 } elseif ( $mode == self::LIST_SET && is_numeric( $field ) ) {
246 $list .= "$value";
247 } elseif (
248 ( $mode == self::LIST_AND || $mode == self::LIST_OR ) && is_array( $value )
249 ) {
250 // Remove null from array to be handled separately if found
251 $includeNull = false;
252 foreach ( array_keys( $value, null, true ) as $nullKey ) {
253 $includeNull = true;
254 unset( $value[$nullKey] );
255 }
256 if ( count( $value ) == 0 && !$includeNull ) {
257 throw new InvalidArgumentException(
258 __METHOD__ . ": empty input for field $field" );
259 } elseif ( count( $value ) == 0 ) {
260 // only check if $field is null
261 $list .= "$field IS NULL";
262 } else {
263 // IN clause contains at least one valid element
264 if ( $includeNull ) {
265 // Group subconditions to ensure correct precedence
266 $list .= '(';
267 }
268 if ( count( $value ) == 1 ) {
269 // Special-case single values, as IN isn't terribly efficient
270 // (but call makeList() so that warnings are emitted if needed)
271 $list .= $field . " = " . $this->makeList( $value );
272 } else {
273 $list .= $field . " IN (" . $this->makeList( $value ) . ") ";
274 }
275 // if null present in array, append IS NULL
276 if ( $includeNull ) {
277 $list .= " OR $field IS NULL)";
278 }
279 }
280 } elseif ( is_array( $value ) ) {
281 throw new InvalidArgumentException( __METHOD__ . ": unexpected nested array" );
282 } elseif ( $value === null ) {
283 if ( $mode == self::LIST_AND || $mode == self::LIST_OR ) {
284 $list .= "$field IS ";
285 } elseif ( $mode == self::LIST_SET ) {
286 $list .= "$field = ";
287 } elseif ( $mode === self::LIST_COMMA && !is_numeric( $field ) ) {
288 $keyWarning ??= [
289 __METHOD__ . ": array key {key} in list of values ignored",
290 [ 'key' => $field, 'exception' => new RuntimeException() ]
291 ];
292 } elseif ( $mode === self::LIST_NAMES && !is_numeric( $field ) ) {
293 $keyWarning ??= [
294 __METHOD__ . ": array key {key} in list of fields ignored",
295 [ 'key' => $field, 'exception' => new RuntimeException() ]
296 ];
297 }
298 $list .= 'NULL';
299 } else {
300 if (
301 $mode == self::LIST_AND || $mode == self::LIST_OR || $mode == self::LIST_SET
302 ) {
303 $list .= "$field = ";
304 } elseif ( $mode === self::LIST_COMMA && !is_numeric( $field ) ) {
305 $keyWarning ??= [
306 __METHOD__ . ": array key {key} in list of values ignored",
307 [ 'key' => $field, 'exception' => new RuntimeException() ]
308 ];
309 } elseif ( $mode === self::LIST_NAMES && !is_numeric( $field ) ) {
310 $keyWarning ??= [
311 __METHOD__ . ": array key {key} in list of fields ignored",
312 [ 'key' => $field, 'exception' => new RuntimeException() ]
313 ];
314 }
315 $list .= $mode == self::LIST_NAMES ? $value : $this->quoter->addQuotes( $value );
316 }
317 }
318
319 if ( $keyWarning ) {
320 // Only log one warning about this per function call, to reduce log spam when a dynamically
321 // generated associative array is passed
322 $this->logger->warning( ...$keyWarning );
323 }
324
325 return $list;
326 }
327
328 public function makeWhereFrom2d( $data, $baseKey, $subKey ) {
329 $conds = [];
330 foreach ( $data as $base => $sub ) {
331 if ( count( $sub ) ) {
332 $conds[] = $this->makeList(
333 [ $baseKey => $base, $subKey => array_map( 'strval', array_keys( $sub ) ) ],
334 self::LIST_AND
335 );
336 }
337 }
338
339 if ( !$conds ) {
340 throw new InvalidArgumentException( "Data for $baseKey and $subKey must be non-empty" );
341 }
342
343 return $this->makeList( $conds, self::LIST_OR );
344 }
345
346 public function factorConds( $condsArray ) {
347 if ( count( $condsArray ) === 0 ) {
348 throw new InvalidArgumentException(
349 __METHOD__ . ": empty condition array" );
350 }
351 $condsByFieldSet = [];
352 foreach ( $condsArray as $conds ) {
353 if ( !count( $conds ) ) {
354 throw new InvalidArgumentException(
355 __METHOD__ . ": empty condition subarray" );
356 }
357 $fieldKey = implode( ',', array_keys( $conds ) );
358 $condsByFieldSet[$fieldKey][] = $conds;
359 }
360 $result = '';
361 foreach ( $condsByFieldSet as $conds ) {
362 if ( $result !== '' ) {
363 $result .= ' OR ';
364 }
365 $result .= $this->factorCondsWithCommonFields( $conds );
366 }
367 return $result;
368 }
369
377 private function factorCondsWithCommonFields( $condsArray ) {
378 $first = $condsArray[array_key_first( $condsArray )];
379 if ( count( $first ) === 1 ) {
380 // IN clause
381 $field = array_key_first( $first );
382 $values = [];
383 foreach ( $condsArray as $conds ) {
384 $values[] = $conds[$field];
385 }
386 return $this->makeList( [ $field => $values ], self::LIST_AND );
387 }
388
389 $field1 = array_key_first( $first );
390 $nullExpressions = [];
391 $expressionsByField1 = [];
392 foreach ( $condsArray as $conds ) {
393 $value1 = $conds[$field1];
394 unset( $conds[$field1] );
395 if ( $value1 === null ) {
396 $nullExpressions[] = $conds;
397 } else {
398 $expressionsByField1[$value1][] = $conds;
399 }
400
401 }
402 $wrap = false;
403 $result = '';
404 foreach ( $expressionsByField1 as $value1 => $expressions ) {
405 if ( $result !== '' ) {
406 $result .= ' OR ';
407 $wrap = true;
408 }
409 $factored = $this->factorCondsWithCommonFields( $expressions );
410 $result .= "($field1 = " . $this->quoter->addQuotes( $value1 ) .
411 " AND $factored)";
412 }
413 if ( count( $nullExpressions ) ) {
414 $factored = $this->factorCondsWithCommonFields( $nullExpressions );
415 if ( $result !== '' ) {
416 $result .= ' OR ';
417 $wrap = true;
418 }
419 $result .= "($field1 IS NULL AND $factored)";
420 }
421 if ( $wrap ) {
422 return "($result)";
423 } else {
424 return $result;
425 }
426 }
427
432 public function buildConcat( $stringList ) {
433 return 'CONCAT(' . implode( ',', $stringList ) . ')';
434 }
435
436 public function limitResult( $sql, $limit, $offset = false ) {
437 if ( !is_numeric( $limit ) ) {
438 throw new DBLanguageError(
439 "Invalid non-numeric limit passed to " . __METHOD__
440 );
441 }
442 // This version works in MySQL and SQLite. It will very likely need to be
443 // overridden for most other RDBMS subclasses.
444 return "$sql LIMIT "
445 . ( ( is_numeric( $offset ) && $offset != 0 ) ? "{$offset}," : "" )
446 . "{$limit} ";
447 }
448
455 public function escapeLikeInternal( $s, $escapeChar = '`' ) {
456 return str_replace(
457 [ $escapeChar, '%', '_' ],
458 [ "{$escapeChar}{$escapeChar}", "{$escapeChar}%", "{$escapeChar}_" ],
459 $s
460 );
461 }
462
463 public function buildLike( $param, ...$params ) {
464 if ( is_array( $param ) ) {
465 $params = $param;
466 } else {
467 $params = func_get_args();
468 }
469 // @phan-suppress-next-line PhanParamTooFewUnpack
470 $likeValue = new LikeValue( ...$params );
471
472 return ' LIKE ' . $likeValue->toSql( $this->quoter );
473 }
474
475 public function anyChar() {
476 return new LikeMatch( '_' );
477 }
478
479 public function anyString() {
480 return new LikeMatch( '%' );
481 }
482
487 public function unionSupportsOrderAndLimit() {
488 return true; // True for almost every DB supported
489 }
490
491 public function unionQueries( $sqls, $all, $options = [] ) {
492 $glue = $all ? ') UNION ALL (' : ') UNION (';
493
494 $sql = '(' . implode( $glue, $sqls ) . ')';
495 if ( !$this->unionSupportsOrderAndLimit() ) {
496 return $sql;
497 }
498 $sql .= $this->makeOrderBy( $options );
499 $limit = $options['LIMIT'] ?? null;
500 $offset = $options['OFFSET'] ?? false;
501 if ( $limit !== null ) {
502 $sql = $this->limitResult( $sql, $limit, $offset );
503 }
504
505 return $sql;
506 }
507
508 public function conditional( $cond, $caseTrueExpression, $caseFalseExpression ) {
509 if ( is_array( $cond ) ) {
510 $cond = $this->makeList( $cond, self::LIST_AND );
511 }
512 if ( $cond instanceof IExpression ) {
513 $cond = $cond->toSql( $this->quoter );
514 }
515
516 return "(CASE WHEN $cond THEN $caseTrueExpression ELSE $caseFalseExpression END)";
517 }
518
519 public function strreplace( $orig, $old, $new ) {
520 return "REPLACE({$orig}, {$old}, {$new})";
521 }
522
523 public function timestamp( $ts = 0 ) {
524 $t = new ConvertibleTimestamp( $ts );
525 // Let errors bubble up to avoid putting garbage in the DB
526 return $t->getTimestamp( TS_MW );
527 }
528
529 public function timestampOrNull( $ts = null ) {
530 if ( $ts === null ) {
531 return null;
532 } else {
533 return $this->timestamp( $ts );
534 }
535 }
536
537 public function getInfinity() {
538 return 'infinity';
539 }
540
541 public function encodeExpiry( $expiry ) {
542 return ( $expiry == '' || $expiry == 'infinity' || $expiry == $this->getInfinity() )
543 ? $this->getInfinity()
544 : $this->timestamp( $expiry );
545 }
546
547 public function decodeExpiry( $expiry, $format = TS_MW ) {
548 if ( $expiry == '' || $expiry == 'infinity' || $expiry == $this->getInfinity() ) {
549 return 'infinity';
550 }
551
552 return ConvertibleTimestamp::convert( $format, $expiry );
553 }
554
559 public function buildSubstring( $input, $startPosition, $length = null ) {
560 $this->assertBuildSubstringParams( $startPosition, $length );
561 $functionBody = "$input FROM $startPosition";
562 if ( $length !== null ) {
563 $functionBody .= " FOR $length";
564 }
565 return 'SUBSTRING(' . $functionBody . ')';
566 }
567
580 protected function assertBuildSubstringParams( $startPosition, $length ) {
581 if ( $startPosition === 0 ) {
582 // The DBMSs we support use 1-based indexing here.
583 throw new InvalidArgumentException( 'Use 1 as $startPosition for the beginning of the string' );
584 }
585 if ( !is_int( $startPosition ) || $startPosition < 0 ) {
586 throw new InvalidArgumentException(
587 '$startPosition must be a positive integer'
588 );
589 }
590 if ( !( ( is_int( $length ) && $length >= 0 ) || $length === null ) ) {
591 throw new InvalidArgumentException(
592 '$length must be null or an integer greater than or equal to 0'
593 );
594 }
595 }
596
597 public function buildStringCast( $field ) {
598 // In theory this should work for any standards-compliant
599 // SQL implementation, although it may not be the best way to do it.
600 return "CAST( $field AS CHARACTER )";
601 }
602
603 public function buildIntegerCast( $field ) {
604 return 'CAST( ' . $field . ' AS INTEGER )';
605 }
606
607 public function implicitOrderby() {
608 return true;
609 }
610
619 public function indexName( $index ) {
620 return $this->indexAliases[$index] ?? $index;
621 }
622
623 public function setTableAliases( array $aliases ) {
624 $this->tableAliases = $aliases;
625 }
626
627 public function setIndexAliases( array $aliases ) {
628 $this->indexAliases = $aliases;
629 }
630
634 public function getTableAliases() {
635 return $this->tableAliases;
636 }
637
638 public function setPrefix( $prefix ) {
639 $this->currentDomain = new DatabaseDomain(
640 $this->currentDomain->getDatabase(),
641 $this->currentDomain->getSchema(),
642 $prefix
643 );
644 }
645
646 public function setCurrentDomain( DatabaseDomain $currentDomain ) {
647 $this->currentDomain = $currentDomain;
648 }
649
654 public function getCurrentDomain() {
655 return $this->currentDomain;
656 }
657
658 public function selectSQLText(
659 $tables, $vars, $conds = '', $fname = __METHOD__, $options = [], $join_conds = []
660 ) {
661 if ( !is_array( $tables ) ) {
662 if ( $tables === '' || $tables === null || $tables === false ) {
663 $tables = [];
664 } elseif ( is_string( $tables ) ) {
665 $tables = [ $tables ];
666 } else {
667 throw new DBLanguageError( __METHOD__ . ' called with incorrect table parameter' );
668 }
669 }
670
671 if ( is_array( $vars ) ) {
672 $fields = implode( ',', $this->fieldNamesWithAlias( $vars ) );
673 } else {
674 $fields = $vars;
675 }
676
677 $options = (array)$options;
678
679 $useIndexByTable = $options['USE INDEX'] ?? [];
680 if ( !is_array( $useIndexByTable ) ) {
681 if ( count( $tables ) <= 1 ) {
682 $useIndexByTable = [ reset( $tables ) => $useIndexByTable ];
683 } else {
684 $e = new DBLanguageError( __METHOD__ . " got ambiguous USE INDEX ($fname)" );
685 ( $this->errorLogger )( $e );
686 }
687 }
688
689 $ignoreIndexByTable = $options['IGNORE INDEX'] ?? [];
690 if ( !is_array( $ignoreIndexByTable ) ) {
691 if ( count( $tables ) <= 1 ) {
692 $ignoreIndexByTable = [ reset( $tables ) => $ignoreIndexByTable ];
693 } else {
694 $e = new DBLanguageError( __METHOD__ . " got ambiguous IGNORE INDEX ($fname)" );
695 ( $this->errorLogger )( $e );
696 }
697 }
698
699 if (
700 $this->selectOptionsIncludeLocking( $options ) &&
701 $this->selectFieldsOrOptionsAggregate( $vars, $options )
702 ) {
703 // Some DB types (e.g. postgres) disallow FOR UPDATE with aggregate
704 // functions. Discourage use of such queries to encourage compatibility.
705 $this->logger->warning(
706 __METHOD__ . ": aggregation used with a locking SELECT ($fname)"
707 );
708 }
709
710 if ( count( $tables ) ) {
711 $from = ' FROM ' . $this->tableNamesWithIndexClauseOrJOIN(
712 $tables,
713 $useIndexByTable,
714 $ignoreIndexByTable,
715 $join_conds
716 );
717 } else {
718 $from = '';
719 }
720
721 [ $startOpts, $preLimitTail, $postLimitTail ] = $this->makeSelectOptions( $options );
722
723 if ( is_array( $conds ) ) {
724 $where = $this->makeList( $conds, self::LIST_AND );
725 } elseif ( $conds instanceof IExpression ) {
726 $where = $conds->toSql( $this->quoter );
727 } elseif ( $conds === null || $conds === false ) {
728 $where = '';
729 $this->logger->warning(
730 __METHOD__
731 . ' called from '
732 . $fname
733 . ' with incorrect parameters: $conds must be a string or an array',
734 [ 'db_log_category' => 'sql' ]
735 );
736 } elseif ( is_string( $conds ) ) {
737 $where = $conds;
738 } else {
739 throw new DBLanguageError( __METHOD__ . ' called with incorrect parameters' );
740 }
741
742 // Keep historical extra spaces after FROM to avoid testing failures
743 if ( $where === '' || $where === '*' ) {
744 $sql = "SELECT $startOpts $fields $from $preLimitTail";
745 } else {
746 $sql = "SELECT $startOpts $fields $from WHERE $where $preLimitTail";
747 }
748
749 if ( isset( $options['LIMIT'] ) ) {
750 $sql = $this->limitResult( $sql, $options['LIMIT'], $options['OFFSET'] ?? false );
751 }
752 $sql = "$sql $postLimitTail";
753
754 if ( isset( $options['EXPLAIN'] ) ) {
755 $sql = 'EXPLAIN ' . $sql;
756 }
757
758 if (
759 $fname === static::CALLER_UNKNOWN ||
760 str_starts_with( $fname, 'Wikimedia\\Rdbms\\' ) ||
761 $fname === '{closure}'
762 ) {
763 $exception = new RuntimeException();
764
765 // Try to figure out and report the real caller
766 $caller = '';
767 foreach ( $exception->getTrace() as $call ) {
768 if ( str_ends_with( $call['file'] ?? '', 'Test.php' ) ) {
769 // Don't warn when called directly by test code, adding callers there is pointless
770 break;
771 } elseif ( str_starts_with( $call['class'] ?? '', 'Wikimedia\\Rdbms\\' ) ) {
772 // Keep looking for the caller of a rdbms method
773 } elseif ( str_ends_with( $call['class'] ?? '', 'SelectQueryBuilder' ) ) {
774 // Keep looking for the caller of any custom SelectQueryBuilder
775 } else {
776 // Warn about the external caller we found
777 $caller = implode( '::', array_filter( [ $call['class'] ?? null, $call['function'] ] ) );
778 break;
779 }
780 }
781
782 if ( $fname === '{closure}' ) {
783 // Someone did ->caller( __METHOD__ ) in a local function, e.g. in a callback to
784 // getWithSetCallback(), MWCallableUpdate or doAtomicSection(). That's not very helpful.
785 // Provide a more specific message. The caller has to be provided like this:
786 // $method = __METHOD__;
787 // function ( ... ) use ( $method ) { ... }
788 $warning = "SQL query with incorrect caller (__METHOD__ used inside a closure: {caller}): {sql}";
789 } else {
790 $warning = "SQL query did not specify the caller (guessed caller: {caller}): {sql}";
791 }
792
793 $this->logger->warning(
794 $warning,
795 [ 'sql' => $sql, 'caller' => $caller, 'exception' => $exception ]
796 );
797 }
798
799 return $sql;
800 }
801
806 private function selectOptionsIncludeLocking( $options ) {
807 $options = (array)$options;
808 foreach ( [ 'FOR UPDATE', 'LOCK IN SHARE MODE' ] as $lock ) {
809 if ( in_array( $lock, $options, true ) ) {
810 return true;
811 }
812 }
813
814 return false;
815 }
816
822 private function selectFieldsOrOptionsAggregate( $fields, $options ) {
823 foreach ( (array)$options as $key => $value ) {
824 if ( is_string( $key ) ) {
825 if ( preg_match( '/^(?:GROUP BY|HAVING)$/i', $key ) ) {
826 return true;
827 }
828 } elseif ( is_string( $value ) ) {
829 if ( preg_match( '/^(?:DISTINCT|DISTINCTROW)$/i', $value ) ) {
830 return true;
831 }
832 }
833 }
834
835 $regex = '/^(?:COUNT|MIN|MAX|SUM|GROUP_CONCAT|LISTAGG|ARRAY_AGG)\s*\\(/i';
836 foreach ( (array)$fields as $field ) {
837 if ( is_string( $field ) && preg_match( $regex, $field ) ) {
838 return true;
839 }
840 }
841
842 return false;
843 }
844
851 protected function fieldNamesWithAlias( $fields ) {
852 $retval = [];
853 foreach ( $fields as $alias => $field ) {
854 if ( is_numeric( $alias ) ) {
855 $alias = $field;
856 }
857 $retval[] = $this->fieldNameWithAlias( $field, $alias );
858 }
859
860 return $retval;
861 }
862
872 public function fieldNameWithAlias( $name, $alias = false ) {
873 if ( !$alias || (string)$alias === (string)$name ) {
874 return $name;
875 } else {
876 return $name . ' AS ' . $this->addIdentifierQuotes( $alias ); // PostgreSQL needs AS
877 }
878 }
879
891 $tables,
892 $use_index = [],
893 $ignore_index = [],
894 $join_conds = []
895 ) {
896 $ret = [];
897 $retJOIN = [];
898 $use_index = (array)$use_index;
899 $ignore_index = (array)$ignore_index;
900 $join_conds = (array)$join_conds;
901
902 foreach ( $tables as $alias => $table ) {
903 if ( !is_string( $alias ) ) {
904 // No alias? Set it equal to the table name
905 $alias = $table;
906 }
907
908 if ( is_array( $table ) ) {
909 // A parenthesized group
910 if ( count( $table ) > 1 ) {
911 $joinedTable = '(' .
912 $this->tableNamesWithIndexClauseOrJOIN(
913 $table, $use_index, $ignore_index, $join_conds ) . ')';
914 } else {
915 // Degenerate case
916 $innerTable = reset( $table );
917 $innerAlias = key( $table );
918 $joinedTable = $this->tableNameWithAlias(
919 $innerTable,
920 is_string( $innerAlias ) ? $innerAlias : $innerTable
921 );
922 }
923 } else {
924 $joinedTable = $this->tableNameWithAlias( $table, $alias );
925 }
926
927 // Is there a JOIN clause for this table?
928 if ( isset( $join_conds[$alias] ) ) {
929 Assert::parameterType( 'array', $join_conds[$alias], "join_conds[$alias]" );
930 [ $joinType, $conds ] = $join_conds[$alias];
931 $tableClause = $this->normalizeJoinType( $joinType );
932 $tableClause .= ' ' . $joinedTable;
933 if ( isset( $use_index[$alias] ) ) { // has USE INDEX?
934 $use = $this->useIndexClause( implode( ',', (array)$use_index[$alias] ) );
935 if ( $use != '' ) {
936 $tableClause .= ' ' . $use;
937 }
938 }
939 if ( isset( $ignore_index[$alias] ) ) { // has IGNORE INDEX?
940 $ignore = $this->ignoreIndexClause(
941 implode( ',', (array)$ignore_index[$alias] ) );
942 if ( $ignore != '' ) {
943 $tableClause .= ' ' . $ignore;
944 }
945 }
946 $on = $this->makeList( (array)$conds, self::LIST_AND );
947 if ( $on != '' ) {
948 $tableClause .= ' ON (' . $on . ')';
949 }
950
951 $retJOIN[] = $tableClause;
952 } elseif ( isset( $use_index[$alias] ) ) {
953 // Is there an INDEX clause for this table?
954 $tableClause = $joinedTable;
955 $tableClause .= ' ' . $this->useIndexClause(
956 implode( ',', (array)$use_index[$alias] )
957 );
958
959 $ret[] = $tableClause;
960 } elseif ( isset( $ignore_index[$alias] ) ) {
961 // Is there an INDEX clause for this table?
962 $tableClause = $joinedTable;
963 $tableClause .= ' ' . $this->ignoreIndexClause(
964 implode( ',', (array)$ignore_index[$alias] )
965 );
966
967 $ret[] = $tableClause;
968 } else {
969 $tableClause = $joinedTable;
970
971 $ret[] = $tableClause;
972 }
973 }
974
975 // We can't separate explicit JOIN clauses with ',', use ' ' for those
976 $implicitJoins = implode( ',', $ret );
977 $explicitJoins = implode( ' ', $retJOIN );
978
979 // Compile our final table clause
980 return implode( ' ', [ $implicitJoins, $explicitJoins ] );
981 }
982
991 protected function normalizeJoinType( string $joinType ) {
992 switch ( strtoupper( $joinType ) ) {
993 case 'JOIN':
994 case 'INNER JOIN':
995 return 'JOIN';
996
997 case 'LEFT JOIN':
998 return 'LEFT JOIN';
999
1000 case 'STRAIGHT_JOIN':
1001 case 'STRAIGHT JOIN':
1002 // MySQL only
1003 return 'JOIN';
1004
1005 default:
1006 return $joinType;
1007 }
1008 }
1009
1021 protected function tableNameWithAlias( $table, $alias = false ) {
1022 if ( is_string( $table ) ) {
1023 $quotedTable = $this->tableName( $table );
1024 } elseif ( $table instanceof Subquery ) {
1025 $quotedTable = (string)$table;
1026 } else {
1027 throw new InvalidArgumentException( "Table must be a string or Subquery" );
1028 }
1029
1030 if ( $alias === false || $alias === $table ) {
1031 if ( $table instanceof Subquery ) {
1032 throw new InvalidArgumentException( "Subquery table missing alias" );
1033 }
1034
1035 return $quotedTable;
1036 } else {
1037 return $quotedTable . ' ' . $this->addIdentifierQuotes( $alias );
1038 }
1039 }
1040
1041 public function tableName( string $name, $format = 'quoted' ) {
1042 $prefix = $this->currentDomain->getTablePrefix();
1043
1044 // Warn about table names that look qualified
1045 if (
1046 str_contains( $name, '.' ) ||
1047 ( $prefix !== '' && str_starts_with( $name, $prefix ) ) ) {
1048 $this->logger->warning(
1049 __METHOD__ . ' called with qualified table ' . $name,
1050 [ 'db_log_category' => 'sql' ]
1051 );
1052 }
1053
1054 // Extract necessary database, schema, table identifiers and quote them as needed
1055 $formattedComponents = [];
1056 foreach ( $this->qualifiedTableComponents( $name ) as $component ) {
1057 if ( $format === 'quoted' ) {
1058 $formattedComponents[] = $this->addIdentifierQuotes( $component );
1059 } else {
1060 $formattedComponents[] = $component;
1061 }
1062 }
1063
1064 return implode( '.', $formattedComponents );
1065 }
1066
1089 public function qualifiedTableComponents( $name ) {
1090 $identifiers = $this->extractTableNameComponents( $name );
1091 if ( count( $identifiers ) > 3 ) {
1092 throw new DBLanguageError( "Too many components in table name '$name'" );
1093 }
1094 // Table alias config and prefixes only apply to unquoted single-identifier names
1095 if ( count( $identifiers ) == 1 && !$this->isQuotedIdentifier( $identifiers[0] ) ) {
1096 [ $table ] = $identifiers;
1097 if ( isset( $this->tableAliases[$table] ) ) {
1098 // This is an "alias" table that uses a different db/schema/prefix scheme
1099 $database = $this->tableAliases[$table]['dbname'];
1100 $schema = is_string( $this->tableAliases[$table]['schema'] )
1101 ? $this->tableAliases[$table]['schema']
1102 : $this->relationSchemaQualifier();
1103 $prefix = is_string( $this->tableAliases[$table]['prefix'] )
1104 ? $this->tableAliases[$table]['prefix']
1105 : $this->currentDomain->getTablePrefix();
1106 } else {
1107 // Use the current database domain to resolve the schema and prefix
1108 $database = '';
1109 $schema = $this->relationSchemaQualifier();
1110 $prefix = $this->currentDomain->getTablePrefix();
1111 }
1112 $qualifierIdentifiers = [ $database, $schema ];
1113 $tableIdentifier = $prefix . $table;
1114 } else {
1115 $qualifierIdentifiers = array_slice( $identifiers, 0, -1 );
1116 $tableIdentifier = end( $identifiers );
1117 }
1118
1119 $components = [];
1120 foreach ( $qualifierIdentifiers as $identifier ) {
1121 if ( $identifier !== null && $identifier !== '' ) {
1122 $components[] = $this->isQuotedIdentifier( $identifier )
1123 ? substr( $identifier, 1, -1 )
1124 : $identifier;
1125 }
1126 }
1127 $components[] = $this->isQuotedIdentifier( $tableIdentifier )
1128 ? substr( $tableIdentifier, 1, -1 )
1129 : $tableIdentifier;
1130
1131 return $components;
1132 }
1133
1140 public function extractTableNameComponents( string $name ) {
1141 $quoteChar = $this->getIdentifierQuoteChar();
1142 $components = [];
1143 foreach ( explode( '.', $name ) as $component ) {
1144 if ( $this->isQuotedIdentifier( $component ) ) {
1145 $unquotedComponent = substr( $component, 1, -1 );
1146 } else {
1147 $unquotedComponent = $component;
1148 }
1149 if ( str_contains( $unquotedComponent, $quoteChar ) ) {
1150 throw new DBLanguageError(
1151 'Table name component contains unexpected quote or dot character' );
1152 }
1153 $components[] = $component;
1154 }
1155 return $components;
1156 }
1157
1183 public function getDatabaseAndTableIdentifier( string $table ) {
1184 $components = $this->qualifiedTableComponents( $table );
1185 switch ( count( $components ) ) {
1186 case 1:
1187 return [ $this->currentDomain->getDatabase(), $components[0] ];
1188 case 2:
1189 return $components;
1190 default:
1191 throw new DBLanguageError( 'Too many table components' );
1192 }
1193 }
1194
1199 protected function relationSchemaQualifier() {
1200 return $this->currentDomain->getSchema();
1201 }
1202
1203 public function tableNames( ...$tables ) {
1204 wfDeprecated( __METHOD__, '1.39' );
1205
1206 $retVal = [];
1207
1208 foreach ( $tables as $name ) {
1209 $retVal[$name] = $this->tableName( $name );
1210 }
1211
1212 return $retVal;
1213 }
1214
1215 public function tableNamesN( ...$tables ) {
1216 $retVal = [];
1217
1218 foreach ( $tables as $name ) {
1219 $retVal[] = $this->tableName( $name );
1220 }
1221
1222 return $retVal;
1223 }
1224
1234 public function isQuotedIdentifier( $name ) {
1235 $quoteChar = $this->getIdentifierQuoteChar();
1236 return strlen( $name ) > 1 && $name[0] === $quoteChar && $name[-1] === $quoteChar;
1237 }
1238
1252 public function useIndexClause( $index ) {
1253 return '';
1254 }
1255
1264 public function ignoreIndexClause( $index ) {
1265 return '';
1266 }
1267
1279 protected function makeSelectOptions( array $options ) {
1280 $preLimitTail = $postLimitTail = '';
1281 $startOpts = '';
1282
1283 $noKeyOptions = [];
1284
1285 foreach ( $options as $key => $option ) {
1286 if ( is_numeric( $key ) ) {
1287 $noKeyOptions[$option] = true;
1288 }
1289 }
1290
1291 $preLimitTail .= $this->makeGroupByWithHaving( $options );
1292
1293 $preLimitTail .= $this->makeOrderBy( $options );
1294
1295 if ( isset( $noKeyOptions['FOR UPDATE'] ) ) {
1296 $postLimitTail .= ' FOR UPDATE';
1297 }
1298
1299 if ( isset( $noKeyOptions['LOCK IN SHARE MODE'] ) ) {
1300 $postLimitTail .= ' LOCK IN SHARE MODE';
1301 }
1302
1303 if ( isset( $noKeyOptions['DISTINCT'] ) || isset( $noKeyOptions['DISTINCTROW'] ) ) {
1304 $startOpts .= 'DISTINCT';
1305 }
1306
1307 # Various MySQL extensions
1308 if ( isset( $noKeyOptions['STRAIGHT_JOIN'] ) ) {
1309 $startOpts .= ' /*! STRAIGHT_JOIN */';
1310 }
1311
1312 if ( isset( $noKeyOptions['SQL_BIG_RESULT'] ) ) {
1313 $startOpts .= ' SQL_BIG_RESULT';
1314 }
1315
1316 if ( isset( $noKeyOptions['SQL_BUFFER_RESULT'] ) ) {
1317 $startOpts .= ' SQL_BUFFER_RESULT';
1318 }
1319
1320 if ( isset( $noKeyOptions['SQL_SMALL_RESULT'] ) ) {
1321 $startOpts .= ' SQL_SMALL_RESULT';
1322 }
1323
1324 if ( isset( $noKeyOptions['SQL_CALC_FOUND_ROWS'] ) ) {
1325 $startOpts .= ' SQL_CALC_FOUND_ROWS';
1326 }
1327
1328 return [ $startOpts, $preLimitTail, $postLimitTail ];
1329 }
1330
1339 protected function makeGroupByWithHaving( $options ) {
1340 $sql = '';
1341 if ( isset( $options['GROUP BY'] ) ) {
1342 $gb = is_array( $options['GROUP BY'] )
1343 ? implode( ',', $options['GROUP BY'] )
1344 : $options['GROUP BY'];
1345 $sql .= ' GROUP BY ' . $gb;
1346 }
1347 if ( isset( $options['HAVING'] ) ) {
1348 $having = is_array( $options['HAVING'] )
1349 ? $this->makeList( $options['HAVING'], self::LIST_AND )
1350 : $options['HAVING'];
1351 $sql .= ' HAVING ' . $having;
1352 }
1353
1354 return $sql;
1355 }
1356
1365 protected function makeOrderBy( $options ) {
1366 if ( isset( $options['ORDER BY'] ) ) {
1367 $ob = is_array( $options['ORDER BY'] )
1368 ? implode( ',', $options['ORDER BY'] )
1369 : $options['ORDER BY'];
1370
1371 return ' ORDER BY ' . $ob;
1372 }
1373
1374 return '';
1375 }
1376
1377 public function buildGroupConcatField(
1378 $delim, $tables, $field, $conds = '', $join_conds = []
1379 ) {
1380 $fld = "GROUP_CONCAT($field SEPARATOR " . $this->quoter->addQuotes( $delim ) . ')';
1381
1382 return '(' . $this->selectSQLText( $tables, $fld, $conds, static::CALLER_SUBQUERY, [], $join_conds ) . ')';
1383 }
1384
1385 public function buildSelectSubquery(
1386 $tables, $vars, $conds = '', $fname = __METHOD__,
1387 $options = [], $join_conds = []
1388 ) {
1389 return new Subquery(
1390 $this->selectSQLText( $tables, $vars, $conds, $fname, $options, $join_conds )
1391 );
1392 }
1393
1394 public function insertSqlText( $table, array $rows ) {
1395 $encTable = $this->tableName( $table );
1396 [ $sqlColumns, $sqlTuples ] = $this->makeInsertLists( $rows );
1397
1398 return [
1399 "INSERT INTO $encTable ($sqlColumns) VALUES $sqlTuples",
1400 "INSERT INTO $encTable ($sqlColumns) VALUES '?'"
1401 ];
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( array_values( $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 [
1456 rtrim( "$sqlVerb $encTable ($sqlColumns) VALUES $sqlTuples $sqlOpts" ),
1457 rtrim( "$sqlVerb $encTable ($sqlColumns) VALUES '?' $sqlOpts" )
1458 ];
1459 }
1460
1467 return [ 'INSERT IGNORE INTO', '' ];
1468 }
1469
1471 $destTable,
1472 $srcTable,
1473 array $varMap,
1474 $conds,
1475 $fname,
1476 array $insertOptions,
1477 array $selectOptions,
1478 $selectJoinConds
1479 ) {
1480 [ $sqlVerb, $sqlOpts ] = $this->isFlagInOptions( 'IGNORE', $insertOptions )
1481 ? $this->makeInsertNonConflictingVerbAndOptions()
1482 : [ 'INSERT INTO', '' ];
1483 $encDstTable = $this->tableName( $destTable );
1484 $sqlDstColumns = implode( ',', array_keys( $varMap ) );
1485 $selectSql = $this->selectSQLText(
1486 $srcTable,
1487 array_values( $varMap ),
1488 $conds,
1489 $fname,
1490 $selectOptions,
1491 $selectJoinConds
1492 );
1493
1494 return rtrim( "$sqlVerb $encDstTable ($sqlDstColumns) $selectSql $sqlOpts" );
1495 }
1496
1503 public function isFlagInOptions( $option, array $options ) {
1504 foreach ( array_keys( $options, $option, true ) as $k ) {
1505 if ( is_int( $k ) ) {
1506 return true;
1507 }
1508 }
1509
1510 return false;
1511 }
1512
1520 public function makeKeyCollisionCondition( array $rows, array $uniqueKey ) {
1521 if ( !$rows ) {
1522 throw new DBLanguageError( "Empty row array" );
1523 } elseif ( !$uniqueKey ) {
1524 throw new DBLanguageError( "Empty unique key array" );
1525 }
1526
1527 if ( count( $uniqueKey ) == 1 ) {
1528 // Use a simple IN(...) clause
1529 $column = reset( $uniqueKey );
1530 $values = array_column( $rows, $column );
1531 if ( count( $values ) !== count( $rows ) ) {
1532 throw new DBLanguageError( "Missing values for unique key ($column)" );
1533 }
1534
1535 return $this->makeList( [ $column => $values ], self::LIST_AND );
1536 }
1537
1538 $nullByUniqueKeyColumn = array_fill_keys( $uniqueKey, null );
1539
1540 $orConds = [];
1541 foreach ( $rows as $row ) {
1542 $rowKeyMap = array_intersect_key( $row, $nullByUniqueKeyColumn );
1543 if ( count( $rowKeyMap ) != count( $uniqueKey ) ) {
1544 throw new DBLanguageError(
1545 "Missing values for unique key (" . implode( ',', $uniqueKey ) . ")"
1546 );
1547 }
1548 $orConds[] = $this->makeList( $rowKeyMap, self::LIST_AND );
1549 }
1550
1551 return count( $orConds ) > 1
1552 ? $this->makeList( $orConds, self::LIST_OR )
1553 : $orConds[0];
1554 }
1555
1556 public function deleteJoinSqlText( $delTable, $joinTable, $delVar, $joinVar, $conds ) {
1557 if ( !$conds ) {
1558 throw new DBLanguageError( __METHOD__ . ' called with empty $conds' );
1559 }
1560
1561 $delTable = $this->tableName( $delTable );
1562 $joinTable = $this->tableName( $joinTable );
1563 $sql = "DELETE FROM $delTable WHERE $delVar IN (SELECT $joinVar FROM $joinTable ";
1564 if ( $conds != '*' ) {
1565 $sql .= 'WHERE ' . $this->makeList( $conds, self::LIST_AND );
1566 }
1567 $sql .= ')';
1568
1569 return $sql;
1570 }
1571
1577 public function deleteSqlText( $table, $conds ) {
1578 $isCondValid = ( is_string( $conds ) || is_array( $conds ) ) && $conds;
1579 if ( !$isCondValid ) {
1580 throw new DBLanguageError( __METHOD__ . ' called with empty conditions' );
1581 }
1582
1583 $encTable = $this->tableName( $table );
1584 $sql = "DELETE FROM $encTable";
1585
1586 $condsSql = '';
1587 $cleanCondsSql = '';
1588 if ( $conds !== self::ALL_ROWS && $conds !== [ self::ALL_ROWS ] ) {
1589 $cleanCondsSql = ' WHERE ' . $this->scrubArray( $conds );
1590 if ( is_array( $conds ) ) {
1591 $conds = $this->makeList( $conds, self::LIST_AND );
1592 }
1593 $condsSql .= ' WHERE ' . $conds;
1594 }
1595 return new Query(
1596 $sql . $condsSql,
1597 self::QUERY_CHANGE_ROWS,
1598 'DELETE',
1599 $table,
1600 $sql . $cleanCondsSql
1601 );
1602 }
1603
1604 private function scrubArray( $array, $listType = self::LIST_AND ) {
1605 if ( is_array( $array ) ) {
1606 $scrubbedArray = [];
1607 foreach ( $array as $key => $value ) {
1608 if ( $value instanceof IExpression ) {
1609 $scrubbedArray[$key] = $value->toGeneralizedSql();
1610 } else {
1611 $scrubbedArray[$key] = '?';
1612 }
1613 }
1614 return $this->makeList( $scrubbedArray, $listType );
1615 }
1616 return '?';
1617 }
1618
1619 public function updateSqlText( $table, $set, $conds, $options ) {
1620 $isCondValid = ( is_string( $conds ) || is_array( $conds ) ) && $conds;
1621 if ( !$isCondValid ) {
1622 throw new DBLanguageError( __METHOD__ . ' called with empty conditions' );
1623 }
1624 $encTable = $this->tableName( $table );
1625 $opts = $this->makeUpdateOptions( $options );
1626 $sql = "UPDATE $opts $encTable";
1627 $condsSql = " SET " . $this->makeList( $set, self::LIST_SET );
1628 $cleanCondsSql = " SET " . $this->scrubArray( $set, self::LIST_SET );
1629
1630 if ( $conds && $conds !== self::ALL_ROWS && $conds !== [ self::ALL_ROWS ] ) {
1631 $cleanCondsSql .= ' WHERE ' . $this->scrubArray( $conds );
1632 if ( is_array( $conds ) ) {
1633 $conds = $this->makeList( $conds, self::LIST_AND );
1634 }
1635 $condsSql .= ' WHERE ' . $conds;
1636 }
1637 return new Query(
1638 $sql . $condsSql,
1639 self::QUERY_CHANGE_ROWS,
1640 'UPDATE',
1641 $table,
1642 $sql . $cleanCondsSql
1643 );
1644 }
1645
1652 protected function makeUpdateOptions( $options ) {
1653 $opts = $this->makeUpdateOptionsArray( $options );
1654
1655 return implode( ' ', $opts );
1656 }
1657
1665 protected function makeUpdateOptionsArray( $options ) {
1666 $options = $this->normalizeOptions( $options );
1667
1668 $opts = [];
1669
1670 if ( in_array( 'IGNORE', $options ) ) {
1671 $opts[] = 'IGNORE';
1672 }
1673
1674 return $opts;
1675 }
1676
1682 final public function normalizeOptions( $options ) {
1683 if ( is_array( $options ) ) {
1684 return $options;
1685 } elseif ( is_string( $options ) ) {
1686 return ( $options === '' ) ? [] : [ $options ];
1687 } else {
1688 throw new DBLanguageError( __METHOD__ . ': expected string or array' );
1689 }
1690 }
1691
1692 public function dropTableSqlText( $table ) {
1693 // https://mariadb.com/kb/en/drop-table/
1694 // https://dev.mysql.com/doc/refman/8.0/en/drop-table.html
1695 // https://www.postgresql.org/docs/9.2/sql-truncate.html
1696 return "DROP TABLE " . $this->tableName( $table ) . " CASCADE";
1697 }
1698
1704 public function getQueryVerb( $sql ) {
1705 wfDeprecated( __METHOD__, '1.42' );
1706 return QueryBuilderFromRawSql::buildQuery( $sql, 0 )->getVerb();
1707 }
1708
1721 public function isTransactableQuery( Query $sql ) {
1722 return !in_array(
1723 $sql->getVerb(),
1724 [
1725 'BEGIN',
1726 'ROLLBACK',
1727 'ROLLBACK TO SAVEPOINT',
1728 'COMMIT',
1729 'SET',
1730 'SHOW',
1731 'CREATE',
1732 'ALTER',
1733 'USE',
1734 'SHOW'
1735 ],
1736 true
1737 );
1738 }
1739
1740 public function buildExcludedValue( $column ) {
1741 /* @see Database::upsert() */
1742 // This can be treated like a single value since __VALS is a single row table
1743 return "(SELECT __$column FROM __VALS)";
1744 }
1745
1746 public function savepointSqlText( $identifier ) {
1747 return 'SAVEPOINT ' . $this->addIdentifierQuotes( $identifier );
1748 }
1749
1750 public function releaseSavepointSqlText( $identifier ) {
1751 return 'RELEASE SAVEPOINT ' . $this->addIdentifierQuotes( $identifier );
1752 }
1753
1754 public function rollbackToSavepointSqlText( $identifier ) {
1755 return 'ROLLBACK TO SAVEPOINT ' . $this->addIdentifierQuotes( $identifier );
1756 }
1757
1758 public function rollbackSqlText() {
1759 return 'ROLLBACK';
1760 }
1761
1762 public function dispatchingInsertSqlText( $table, $rows, $options ) {
1763 $rows = $this->normalizeRowArray( $rows );
1764 if ( !$rows ) {
1765 return false;
1766 }
1767
1768 $options = $this->normalizeOptions( $options );
1769 if ( $this->isFlagInOptions( 'IGNORE', $options ) ) {
1770 [ $sql, $cleanSql ] = $this->insertNonConflictingSqlText( $table, $rows );
1771 } else {
1772 [ $sql, $cleanSql ] = $this->insertSqlText( $table, $rows );
1773 }
1774 return new Query( $sql, self::QUERY_CHANGE_ROWS, 'INSERT', $table, $cleanSql );
1775 }
1776
1782 final protected function normalizeRowArray( array $rowOrRows ) {
1783 if ( !$rowOrRows ) {
1784 $rows = [];
1785 } elseif ( isset( $rowOrRows[0] ) ) {
1786 $rows = $rowOrRows;
1787 } else {
1788 $rows = [ $rowOrRows ];
1789 }
1790
1791 foreach ( $rows as $row ) {
1792 if ( !is_array( $row ) ) {
1793 throw new DBLanguageError( "Got non-array in row array" );
1794 } elseif ( !$row ) {
1795 throw new DBLanguageError( "Got empty array in row array" );
1796 }
1797 }
1798
1799 return $rows;
1800 }
1801
1810 final public function normalizeUpsertParams( $uniqueKeys, &$rows ) {
1811 $rows = $this->normalizeRowArray( $rows );
1812 if ( !$uniqueKeys ) {
1813 throw new DBLanguageError( 'No unique key specified for upsert/replace' );
1814 }
1815 $uniqueKey = $this->normalizeUpsertKeys( $uniqueKeys );
1816 $this->assertValidUpsertRowArray( $rows, $uniqueKey );
1817
1818 return $uniqueKey;
1819 }
1820
1827 final public function normalizeConditions( $conds, $fname ) {
1828 if ( $conds === null || $conds === false ) {
1829 $this->logger->warning(
1830 __METHOD__
1831 . ' called from '
1832 . $fname
1833 . ' with incorrect parameters: $conds must be a string or an array',
1834 [ 'db_log_category' => 'sql' ]
1835 );
1836 return [];
1837 } elseif ( $conds === '' ) {
1838 return [];
1839 }
1840
1841 return is_array( $conds ) ? $conds : [ $conds ];
1842 }
1843
1849 private function normalizeUpsertKeys( $uniqueKeys ) {
1850 if ( is_string( $uniqueKeys ) ) {
1851 return [ $uniqueKeys ];
1852 } elseif ( !is_array( $uniqueKeys ) ) {
1853 throw new DBLanguageError( 'Invalid unique key array' );
1854 } else {
1855 if ( count( $uniqueKeys ) !== 1 || !isset( $uniqueKeys[0] ) ) {
1856 throw new DBLanguageError(
1857 "The unique key array should contain a single unique index" );
1858 }
1859
1860 $uniqueKey = $uniqueKeys[0];
1861 if ( is_string( $uniqueKey ) ) {
1862 // Passing a list of strings for single-column unique keys is too
1863 // easily confused with passing the columns of composite unique key
1864 $this->logger->warning( __METHOD__ .
1865 " called with deprecated parameter style: " .
1866 "the unique key array should be a string or array of string arrays",
1867 [
1868 'exception' => new RuntimeException(),
1869 'db_log_category' => 'sql',
1870 ] );
1871 return $uniqueKeys;
1872 } elseif ( is_array( $uniqueKey ) ) {
1873 return $uniqueKey;
1874 } else {
1875 throw new DBLanguageError( 'Invalid unique key array entry' );
1876 }
1877 }
1878 }
1879
1885 final protected function assertValidUpsertRowArray( array $rows, array $uniqueKey ) {
1886 foreach ( $rows as $row ) {
1887 foreach ( $uniqueKey as $column ) {
1888 if ( !isset( $row[$column] ) ) {
1889 throw new DBLanguageError(
1890 "NULL/absent values for unique key (" . implode( ',', $uniqueKey ) . ")"
1891 );
1892 }
1893 }
1894 }
1895 }
1896
1903 final public function assertValidUpsertSetArray(
1904 array $set,
1905 array $uniqueKey,
1906 array $rows
1907 ) {
1908 if ( !$set ) {
1909 throw new DBLanguageError( "Update assignment list can't be empty for upsert" );
1910 }
1911
1912 // Sloppy callers might construct the SET array using the ROW array, leaving redundant
1913 // column definitions for unique key columns. Detect this for backwards compatibility.
1914 $soleRow = ( count( $rows ) == 1 ) ? reset( $rows ) : null;
1915 // Disallow value changes for any columns in the unique key. This avoids additional
1916 // insertion order dependencies that are unwieldy and difficult to implement efficiently
1917 // in PostgreSQL.
1918 foreach ( $set as $k => $v ) {
1919 if ( is_string( $k ) ) {
1920 // Key is a column name and value is a literal (e.g. string, int, null, ...)
1921 if ( in_array( $k, $uniqueKey, true ) ) {
1922 if ( $soleRow && array_key_exists( $k, $soleRow ) && $soleRow[$k] === $v ) {
1923 $this->logger->warning(
1924 __METHOD__ . " called with redundant assignment to column '$k'",
1925 [
1926 'exception' => new RuntimeException(),
1927 'db_log_category' => 'sql',
1928 ]
1929 );
1930 } else {
1931 throw new DBLanguageError(
1932 "Cannot reassign column '$k' since it belongs to the provided unique key"
1933 );
1934 }
1935 }
1936 } elseif ( preg_match( '/^([a-zA-Z0-9_]+)\s*=/', $v, $m ) ) {
1937 // Value is of the form "<unquoted alphanumeric column> = <SQL expression>"
1938 if ( in_array( $m[1], $uniqueKey, true ) ) {
1939 throw new DBLanguageError(
1940 "Cannot reassign column '{$m[1]}' since it belongs to the provided unique key"
1941 );
1942 }
1943 }
1944 }
1945 }
1946
1951 final public function extractSingleFieldFromList( $var ) {
1952 if ( is_array( $var ) ) {
1953 if ( !$var ) {
1954 $column = null;
1955 } elseif ( count( $var ) == 1 ) {
1956 $column = $var[0] ?? reset( $var );
1957 } else {
1958 throw new DBLanguageError( __METHOD__ . ': got multiple columns' );
1959 }
1960 } else {
1961 $column = $var;
1962 }
1963
1964 return $column;
1965 }
1966
1967 public function setSchemaVars( $vars ) {
1968 $this->schemaVars = is_array( $vars ) ? $vars : null;
1969 }
1970
1977 protected function getSchemaVars() {
1978 return $this->schemaVars ?? $this->getDefaultSchemaVars();
1979 }
1980
1990 protected function getDefaultSchemaVars() {
1991 return [];
1992 }
1993
2014 public function replaceVars( $ins ) {
2015 $vars = $this->getSchemaVars();
2016 return preg_replace_callback(
2017 '!
2018 /\* (\$wgDBprefix|[_i]) \*/ (\w*) | # 1-2. tableName, indexName
2019 \'\{\$ (\w+) }\' | # 3. addQuotes
2020 `\{\$ (\w+) }` | # 4. addIdentifierQuotes
2021 /\*\$ (\w+) \*/ # 5. leave unencoded
2022 !x',
2023 function ( $m ) use ( $vars ) {
2024 // Note: Because of <https://bugs.php.net/bug.php?id=51881>,
2025 // check for both nonexistent keys *and* the empty string.
2026 if ( isset( $m[1] ) && $m[1] !== '' ) {
2027 if ( $m[1] === 'i' ) {
2028 return $this->indexName( $m[2] );
2029 } else {
2030 return $this->tableName( $m[2] );
2031 }
2032 } elseif ( isset( $m[3] ) && $m[3] !== '' && array_key_exists( $m[3], $vars ) ) {
2033 return $this->quoter->addQuotes( $vars[$m[3]] );
2034 } elseif ( isset( $m[4] ) && $m[4] !== '' && array_key_exists( $m[4], $vars ) ) {
2035 return $this->addIdentifierQuotes( $vars[$m[4]] );
2036 } elseif ( isset( $m[5] ) && $m[5] !== '' && array_key_exists( $m[5], $vars ) ) {
2037 return $vars[$m[5]];
2038 } else {
2039 return $m[0];
2040 }
2041 },
2042 $ins
2043 );
2044 }
2045
2046 public function lockSQLText( $lockName, $timeout ) {
2047 throw new RuntimeException( 'locking must be implemented in subclasses' );
2048 }
2049
2050 public function lockIsFreeSQLText( $lockName ) {
2051 throw new RuntimeException( 'locking must be implemented in subclasses' );
2052 }
2053
2054 public function unlockSQLText( $lockName ) {
2055 throw new RuntimeException( 'locking must be implemented in subclasses' );
2056 }
2057}
wfDeprecated( $function, $version=false, $component=false, $callerOffset=2)
Logs a warning that a deprecated feature was used.
array $params
The job parameters.
if(!defined('MW_SETUP_CALLBACK'))
Definition WebStart.php:81
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
Content of like value.
Definition LikeValue.php:14
buildGroupConcatField( $delim, $tables, $field, $conds='', $join_conds=[])
Build a GROUP_CONCAT or equivalent statement for a query.
decodeExpiry( $expiry, $format=TS_MW)
Decode an expiry time into a DBMS independent format.
getIdentifierQuoteChar()
Get the character used for identifier quoting.
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.string
insertSelectNativeSqlText( $destTable, $srcTable, array $varMap, $conds, $fname, array $insertOptions, array $selectOptions, $selectJoinConds)
getInfinity()
Find out when 'infinity' is.
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)
makeGroupByWithHaving( $options)
Returns an optional GROUP BY with an optional HAVING.
buildSelectSubquery( $tables, $vars, $conds='', $fname=__METHOD__, $options=[], $join_conds=[])
Equivalent to IDatabase::selectSQLText() except wraps the result in Subquery.
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.
replaceVars( $ins)
Database-independent variable replacement.
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.
qualifiedTableComponents( $name)
Get the table components needed for a query given the currently selected database/schema.
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.
ignoreIndexClause( $index)
IGNORE INDEX clause.
makeOrderBy( $options)
Returns an optional ORDER BY.
string[] $indexAliases
Current map of (index alias => index)
buildLike( $param,... $params)
LIKE statement wrapper.
bitOr( $fieldLeft, $fieldRight)
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.
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()
getDatabaseAndTableIdentifier(string $table)
Get the database identifer and prefixed table name identifier for a table.
assertValidUpsertSetArray(array $set, array $uniqueKey, array $rows)
bitAnd( $fieldLeft, $fieldRight)
array[] $tableAliases
Current map of (table => (dbname, schema, prefix) map)
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.
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.
tableName(string $name, $format='quoted')
Format a table name ready for use in constructing an SQL query.
assertValidUpsertRowArray(array $rows, array $uniqueKey)
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.
setSchemaVars( $vars)
Set schema variables to be used when streaming commands from SQL files or stdin.
setCurrentDomain(DatabaseDomain $currentDomain)
selectSQLText( $tables, $vars, $conds='', $fname=__METHOD__, $options=[], $join_conds=[])
Take the same arguments as IDatabase::select() and return the SQL it would use.
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)
addIdentifierQuotes( $s)
Escape a SQL identifier (e.g.
extractTableNameComponents(string $name)
Extract the dot-separated components of a table name, preserving identifier quotation.
escapeLikeInternal( $s, $escapeChar='`')
fieldNamesWithAlias( $fields)
Gets an array of aliased field names.
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.
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
Raw SQL value to be used in query builders.
Interface for query language.