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 = [];
52 protected $schemaVars;
53 protected DbQuoter $quoter;
54 protected LoggerInterface $logger;
56 protected $errorLogger;
57
58 public function __construct(
60 ?LoggerInterface $logger = null,
62 $errorLogger = null
63 ) {
64 $this->quoter = $quoter;
65 $this->logger = $logger ?? new NullLogger();
66 $this->currentDomain = $currentDomain ?? DatabaseDomain::newUnspecified();
67 $this->errorLogger = $errorLogger ?? static function ( Throwable $e ) {
68 trigger_error( get_class( $e ) . ': ' . $e->getMessage(), E_USER_WARNING );
69 };
70 }
71
72 public function bitNot( $field ) {
73 return "(~$field)";
74 }
75
76 public function bitAnd( $fieldLeft, $fieldRight ) {
77 return "($fieldLeft & $fieldRight)";
78 }
79
80 public function bitOr( $fieldLeft, $fieldRight ) {
81 return "($fieldLeft | $fieldRight)";
82 }
83
84 public function addIdentifierQuotes( $s ) {
85 if ( strcspn( $s, "\0\"`'." ) !== strlen( $s ) ) {
86 throw new DBLanguageError(
87 "Identifier must not contain quote, dot or null characters: got '$s'"
88 );
89 }
90 $quoteChar = $this->getIdentifierQuoteChar();
91 return $quoteChar . $s . $quoteChar;
92 }
93
98 protected function getIdentifierQuoteChar() {
99 return '"';
100 }
101
105 public function buildGreatest( $fields, $values ) {
106 return $this->buildSuperlative( 'GREATEST', $fields, $values );
107 }
108
112 public function buildLeast( $fields, $values ) {
113 return $this->buildSuperlative( 'LEAST', $fields, $values );
114 }
115
130 protected function buildSuperlative( $sqlfunc, $fields, $values ) {
131 $fields = is_array( $fields ) ? $fields : [ $fields ];
132 $values = is_array( $values ) ? $values : [ $values ];
133
134 $encValues = [];
135 foreach ( $fields as $alias => $field ) {
136 if ( is_int( $alias ) ) {
137 $encValues[] = $this->addIdentifierQuotes( $field );
138 } else {
139 $encValues[] = $field; // expression
140 }
141 }
142 foreach ( $values as $value ) {
143 if ( is_int( $value ) || is_float( $value ) ) {
144 $encValues[] = $value;
145 } elseif ( is_string( $value ) ) {
146 $encValues[] = $this->quoter->addQuotes( $value );
147 } elseif ( $value === null ) {
148 throw new DBLanguageError( 'Null value in superlative' );
149 } else {
150 throw new DBLanguageError( 'Unexpected value type in superlative' );
151 }
152 }
153
154 return $sqlfunc . '(' . implode( ',', $encValues ) . ')';
155 }
156
157 public function buildComparison( string $op, array $conds ): string {
158 if ( !in_array( $op, [ '>', '>=', '<', '<=' ] ) ) {
159 throw new InvalidArgumentException( "Comparison operator must be one of '>', '>=', '<', '<='" );
160 }
161 if ( count( $conds ) === 0 ) {
162 throw new InvalidArgumentException( "Empty input" );
163 }
164
165 // Construct a condition string by starting with the least significant part of the index, and
166 // adding more significant parts progressively to the left of the string.
167 //
168 // For example, given $conds = [ 'a' => 4, 'b' => 7, 'c' => 1 ], this will generate a condition
169 // like this:
170 //
171 // WHERE a > 4
172 // OR (a = 4 AND (b > 7
173 // OR (b = 7 AND (c > 1))))
174 //
175 // …which is equivalent to the following, which might be easier to understand:
176 //
177 // WHERE a > 4
178 // OR a = 4 AND b > 7
179 // OR a = 4 AND b = 7 AND c > 1
180 //
181 // …and also equivalent to the following, using tuple comparison syntax, which is most intuitive
182 // but apparently performs worse:
183 //
184 // WHERE (a, b, c) > (4, 7, 1)
185
186 $sql = '';
187 foreach ( array_reverse( $conds ) as $field => $value ) {
188 if ( is_int( $field ) ) {
189 throw new InvalidArgumentException(
190 'Non-associative array passed to buildComparison() (typo?)'
191 );
192 }
193 $encValue = $this->quoter->addQuotes( $value );
194 if ( $sql === '' ) {
195 $sql = "$field $op $encValue";
196 // Change '>=' to '>' etc. for remaining fields, as the equality is handled separately
197 $op = rtrim( $op, '=' );
198 } else {
199 $sql = "$field $op $encValue OR ($field = $encValue AND ($sql))";
200 }
201 }
202 return $sql;
203 }
204
205 public function makeList( array $a, $mode = self::LIST_COMMA ) {
206 $first = true;
207 $list = '';
208 $keyWarning = null;
209
210 foreach ( $a as $field => $value ) {
211 if ( $first ) {
212 $first = false;
213 } else {
214 if ( $mode == self::LIST_AND ) {
215 $list .= ' AND ';
216 } elseif ( $mode == self::LIST_OR ) {
217 $list .= ' OR ';
218 } else {
219 $list .= ',';
220 }
221 }
222
223 if ( ( $mode == self::LIST_AND || $mode == self::LIST_OR ) && is_numeric( $field ) ) {
224 if ( $value instanceof IExpression ) {
225 $list .= "(" . $value->toSql( $this->quoter ) . ")";
226 } elseif ( is_array( $value ) ) {
227 throw new InvalidArgumentException( __METHOD__ . ": unexpected array value without key" );
228 } elseif ( $value instanceof RawSQLValue ) {
229 throw new InvalidArgumentException( __METHOD__ . ": unexpected raw value without key" );
230 } else {
231 $list .= "($value)";
232 }
233 } elseif ( $value instanceof IExpression ) {
234 if ( $mode == self::LIST_AND || $mode == self::LIST_OR ) {
235 throw new InvalidArgumentException( __METHOD__ . ": unexpected key $field for IExpression value" );
236 } else {
237 throw new InvalidArgumentException( __METHOD__ . ": unexpected IExpression outside WHERE clause" );
238 }
239 } elseif ( $mode == self::LIST_SET && is_numeric( $field ) ) {
240 $list .= "$value";
241 } elseif (
242 ( $mode == self::LIST_AND || $mode == self::LIST_OR ) && is_array( $value )
243 ) {
244 // Remove null from array to be handled separately if found
245 $includeNull = false;
246 foreach ( array_keys( $value, null, true ) as $nullKey ) {
247 $includeNull = true;
248 unset( $value[$nullKey] );
249 }
250 if ( count( $value ) == 0 && !$includeNull ) {
251 throw new InvalidArgumentException(
252 __METHOD__ . ": empty input for field $field" );
253 } elseif ( count( $value ) == 0 ) {
254 // only check if $field is null
255 $list .= "$field IS NULL";
256 } else {
257 // IN clause contains at least one valid element
258 if ( $includeNull ) {
259 // Group subconditions to ensure correct precedence
260 $list .= '(';
261 }
262 if ( count( $value ) == 1 ) {
263 // Special-case single values, as IN isn't terribly efficient
264 // (but call makeList() so that warnings are emitted if needed)
265 $list .= $field . " = " . $this->makeList( $value );
266 } else {
267 $list .= $field . " IN (" . $this->makeList( $value ) . ") ";
268 }
269 // if null present in array, append IS NULL
270 if ( $includeNull ) {
271 $list .= " OR $field IS NULL)";
272 }
273 }
274 } elseif ( is_array( $value ) ) {
275 throw new InvalidArgumentException( __METHOD__ . ": unexpected nested array" );
276 } elseif ( $value === null ) {
277 if ( $mode == self::LIST_AND || $mode == self::LIST_OR ) {
278 $list .= "$field IS ";
279 } elseif ( $mode == self::LIST_SET ) {
280 $list .= "$field = ";
281 } elseif ( $mode === self::LIST_COMMA && !is_numeric( $field ) ) {
282 $keyWarning ??= [
283 __METHOD__ . ": array key {key} in list of values ignored",
284 [ 'key' => $field, 'exception' => new RuntimeException() ]
285 ];
286 } elseif ( $mode === self::LIST_NAMES && !is_numeric( $field ) ) {
287 $keyWarning ??= [
288 __METHOD__ . ": array key {key} in list of fields ignored",
289 [ 'key' => $field, 'exception' => new RuntimeException() ]
290 ];
291 }
292 $list .= 'NULL';
293 } else {
294 if (
295 $mode == self::LIST_AND || $mode == self::LIST_OR || $mode == self::LIST_SET
296 ) {
297 $list .= "$field = ";
298 } elseif ( $mode === self::LIST_COMMA && !is_numeric( $field ) ) {
299 $keyWarning ??= [
300 __METHOD__ . ": array key {key} in list of values ignored",
301 [ 'key' => $field, 'exception' => new RuntimeException() ]
302 ];
303 } elseif ( $mode === self::LIST_NAMES && !is_numeric( $field ) ) {
304 $keyWarning ??= [
305 __METHOD__ . ": array key {key} in list of fields ignored",
306 [ 'key' => $field, 'exception' => new RuntimeException() ]
307 ];
308 }
309 $list .= $mode == self::LIST_NAMES ? $value : $this->quoter->addQuotes( $value );
310 }
311 }
312
313 if ( $keyWarning ) {
314 // Only log one warning about this per function call, to reduce log spam when a dynamically
315 // generated associative array is passed
316 $this->logger->warning( ...$keyWarning );
317 }
318
319 return $list;
320 }
321
322 public function makeWhereFrom2d( $data, $baseKey, $subKey ) {
323 $conds = [];
324 foreach ( $data as $base => $sub ) {
325 if ( count( $sub ) ) {
326 $conds[] = $this->makeList(
327 [ $baseKey => $base, $subKey => array_map( 'strval', array_keys( $sub ) ) ],
328 self::LIST_AND
329 );
330 }
331 }
332
333 if ( !$conds ) {
334 throw new InvalidArgumentException( "Data for $baseKey and $subKey must be non-empty" );
335 }
336
337 return $this->makeList( $conds, self::LIST_OR );
338 }
339
340 public function factorConds( $condsArray ) {
341 if ( count( $condsArray ) === 0 ) {
342 throw new InvalidArgumentException(
343 __METHOD__ . ": empty condition array" );
344 }
345 $condsByFieldSet = [];
346 foreach ( $condsArray as $conds ) {
347 if ( !count( $conds ) ) {
348 throw new InvalidArgumentException(
349 __METHOD__ . ": empty condition subarray" );
350 }
351 $fieldKey = implode( ',', array_keys( $conds ) );
352 $condsByFieldSet[$fieldKey][] = $conds;
353 }
354 $result = '';
355 foreach ( $condsByFieldSet as $conds ) {
356 if ( $result !== '' ) {
357 $result .= ' OR ';
358 }
359 $result .= $this->factorCondsWithCommonFields( $conds );
360 }
361 return $result;
362 }
363
371 private function factorCondsWithCommonFields( $condsArray ) {
372 $first = $condsArray[array_key_first( $condsArray )];
373 if ( count( $first ) === 1 ) {
374 // IN clause
375 $field = array_key_first( $first );
376 $values = [];
377 foreach ( $condsArray as $conds ) {
378 $values[] = $conds[$field];
379 }
380 return $this->makeList( [ $field => $values ], self::LIST_AND );
381 }
382
383 $field1 = array_key_first( $first );
384 $nullExpressions = [];
385 $expressionsByField1 = [];
386 foreach ( $condsArray as $conds ) {
387 $value1 = $conds[$field1];
388 unset( $conds[$field1] );
389 if ( $value1 === null ) {
390 $nullExpressions[] = $conds;
391 } else {
392 $expressionsByField1[$value1][] = $conds;
393 }
394
395 }
396 $wrap = false;
397 $result = '';
398 foreach ( $expressionsByField1 as $value1 => $expressions ) {
399 if ( $result !== '' ) {
400 $result .= ' OR ';
401 $wrap = true;
402 }
403 $factored = $this->factorCondsWithCommonFields( $expressions );
404 $result .= "($field1 = " . $this->quoter->addQuotes( $value1 ) .
405 " AND $factored)";
406 }
407 if ( count( $nullExpressions ) ) {
408 $factored = $this->factorCondsWithCommonFields( $nullExpressions );
409 if ( $result !== '' ) {
410 $result .= ' OR ';
411 $wrap = true;
412 }
413 $result .= "($field1 IS NULL AND $factored)";
414 }
415 if ( $wrap ) {
416 return "($result)";
417 } else {
418 return $result;
419 }
420 }
421
425 public function buildConcat( $stringList ) {
426 return 'CONCAT(' . implode( ',', $stringList ) . ')';
427 }
428
429 public function limitResult( $sql, $limit, $offset = false ) {
430 if ( !is_numeric( $limit ) ) {
431 throw new DBLanguageError(
432 "Invalid non-numeric limit passed to " . __METHOD__
433 );
434 }
435 // This version works in MySQL and SQLite. It will very likely need to be
436 // overridden for most other RDBMS subclasses.
437 return "$sql LIMIT "
438 . ( ( is_numeric( $offset ) && $offset != 0 ) ? "{$offset}," : "" )
439 . "{$limit} ";
440 }
441
447 public function escapeLikeInternal( $s, $escapeChar = '`' ) {
448 return str_replace(
449 [ $escapeChar, '%', '_' ],
450 [ "{$escapeChar}{$escapeChar}", "{$escapeChar}%", "{$escapeChar}_" ],
451 $s
452 );
453 }
454
455 public function buildLike( $param, ...$params ) {
456 if ( is_array( $param ) ) {
457 $params = $param;
458 $param = array_shift( $params );
459 }
460 $likeValue = new LikeValue( $param, ...$params );
461
462 return ' LIKE ' . $likeValue->toSql( $this->quoter );
463 }
464
465 public function anyChar() {
466 return new LikeMatch( '_' );
467 }
468
469 public function anyString() {
470 return new LikeMatch( '%' );
471 }
472
476 public function unionSupportsOrderAndLimit() {
477 return true; // True for almost every DB supported
478 }
479
480 public function unionQueries( $sqls, $all, $options = [] ) {
481 $glue = $all ? ') UNION ALL (' : ') UNION (';
482
483 $sql = '(' . implode( $glue, $sqls ) . ')';
484 if ( !$this->unionSupportsOrderAndLimit() ) {
485 return $sql;
486 }
487 $sql .= $this->makeOrderBy( $options );
488 $limit = $options['LIMIT'] ?? null;
489 $offset = $options['OFFSET'] ?? false;
490 if ( $limit !== null ) {
491 $sql = $this->limitResult( $sql, $limit, $offset );
492 }
493
494 return $sql;
495 }
496
497 public function conditional( $cond, $caseTrueExpression, $caseFalseExpression ) {
498 if ( is_array( $cond ) ) {
499 $cond = $this->makeList( $cond, self::LIST_AND );
500 }
501 if ( $cond instanceof IExpression ) {
502 $cond = $cond->toSql( $this->quoter );
503 }
504
505 return "(CASE WHEN $cond THEN $caseTrueExpression ELSE $caseFalseExpression END)";
506 }
507
508 public function strreplace( $orig, $old, $new ) {
509 return "REPLACE({$orig}, {$old}, {$new})";
510 }
511
512 public function timestamp( $ts = 0 ) {
513 $t = new ConvertibleTimestamp( $ts );
514 // Let errors bubble up to avoid putting garbage in the DB
515 return $t->getTimestamp( TS_MW );
516 }
517
518 public function timestampOrNull( $ts = null ) {
519 if ( $ts === null ) {
520 return null;
521 } else {
522 return $this->timestamp( $ts );
523 }
524 }
525
526 public function getInfinity() {
527 return 'infinity';
528 }
529
530 public function encodeExpiry( $expiry ) {
531 return ( $expiry == '' || $expiry == 'infinity' || $expiry == $this->getInfinity() )
532 ? $this->getInfinity()
533 : $this->timestamp( $expiry );
534 }
535
536 public function decodeExpiry( $expiry, $format = TS_MW ) {
537 if ( $expiry == '' || $expiry == 'infinity' || $expiry == $this->getInfinity() ) {
538 return 'infinity';
539 }
540
541 return ConvertibleTimestamp::convert( $format, $expiry );
542 }
543
547 public function buildSubstring( $input, $startPosition, $length = null ) {
548 $this->assertBuildSubstringParams( $startPosition, $length );
549 $functionBody = "$input FROM $startPosition";
550 if ( $length !== null ) {
551 $functionBody .= " FOR $length";
552 }
553 return 'SUBSTRING(' . $functionBody . ')';
554 }
555
568 protected function assertBuildSubstringParams( $startPosition, $length ) {
569 if ( $startPosition === 0 ) {
570 // The DBMSs we support use 1-based indexing here.
571 throw new InvalidArgumentException( 'Use 1 as $startPosition for the beginning of the string' );
572 }
573 if ( !is_int( $startPosition ) || $startPosition < 0 ) {
574 throw new InvalidArgumentException(
575 '$startPosition must be a positive integer'
576 );
577 }
578 if ( !( ( is_int( $length ) && $length >= 0 ) || $length === null ) ) {
579 throw new InvalidArgumentException(
580 '$length must be null or an integer greater than or equal to 0'
581 );
582 }
583 }
584
585 public function buildStringCast( $field ) {
586 // In theory this should work for any standards-compliant
587 // SQL implementation, although it may not be the best way to do it.
588 return "CAST( $field AS CHARACTER )";
589 }
590
591 public function buildIntegerCast( $field ) {
592 return 'CAST( ' . $field . ' AS INTEGER )';
593 }
594
595 public function implicitOrderby() {
596 return true;
597 }
598
599 public function setTableAliases( array $aliases ) {
600 $this->tableAliases = $aliases;
601 }
602
606 public function getTableAliases() {
607 return $this->tableAliases;
608 }
609
610 public function setPrefix( $prefix ) {
611 $this->currentDomain = new DatabaseDomain(
612 $this->currentDomain->getDatabase(),
613 $this->currentDomain->getSchema(),
614 $prefix
615 );
616 }
617
618 public function setCurrentDomain( DatabaseDomain $currentDomain ) {
619 $this->currentDomain = $currentDomain;
620 }
621
626 public function getCurrentDomain() {
627 return $this->currentDomain;
628 }
629
630 public function selectSQLText(
631 $tables, $vars, $conds = '', $fname = __METHOD__, $options = [], $join_conds = []
632 ) {
633 if ( !is_array( $tables ) ) {
634 if ( $tables === '' || $tables === null || $tables === false ) {
635 $tables = [];
636 } elseif ( is_string( $tables ) ) {
637 $tables = [ $tables ];
638 } else {
639 throw new DBLanguageError( __METHOD__ . ' called with incorrect table parameter' );
640 }
641 }
642
643 if ( is_array( $vars ) ) {
644 $fields = implode( ',', $this->fieldNamesWithAlias( $vars ) );
645 } else {
646 $fields = $vars;
647 }
648
649 $options = (array)$options;
650
651 $useIndexByTable = $options['USE INDEX'] ?? [];
652 if ( !is_array( $useIndexByTable ) ) {
653 if ( count( $tables ) <= 1 ) {
654 $useIndexByTable = [ reset( $tables ) => $useIndexByTable ];
655 } else {
656 $e = new DBLanguageError( __METHOD__ . " got ambiguous USE INDEX ($fname)" );
657 ( $this->errorLogger )( $e );
658 }
659 }
660
661 $ignoreIndexByTable = $options['IGNORE INDEX'] ?? [];
662 if ( !is_array( $ignoreIndexByTable ) ) {
663 if ( count( $tables ) <= 1 ) {
664 $ignoreIndexByTable = [ reset( $tables ) => $ignoreIndexByTable ];
665 } else {
666 $e = new DBLanguageError( __METHOD__ . " got ambiguous IGNORE INDEX ($fname)" );
667 ( $this->errorLogger )( $e );
668 }
669 }
670
671 if (
672 $this->selectOptionsIncludeLocking( $options ) &&
673 $this->selectFieldsOrOptionsAggregate( $vars, $options )
674 ) {
675 // Some DB types (e.g. postgres) disallow FOR UPDATE with aggregate
676 // functions. Discourage use of such queries to encourage compatibility.
677 $this->logger->warning(
678 __METHOD__ . ": aggregation used with a locking SELECT ($fname)"
679 );
680 }
681
682 if ( count( $tables ) ) {
683 $from = ' FROM ' . $this->tableNamesWithIndexClauseOrJOIN(
684 $tables,
685 $useIndexByTable,
686 $ignoreIndexByTable,
687 $join_conds
688 );
689 } else {
690 $from = '';
691 }
692
693 [ $startOpts, $preLimitTail, $postLimitTail ] = $this->makeSelectOptions( $options );
694
695 if ( is_array( $conds ) ) {
696 $where = $this->makeList( $conds, self::LIST_AND );
697 } elseif ( $conds instanceof IExpression ) {
698 $where = $conds->toSql( $this->quoter );
699 } elseif ( $conds === null || $conds === false ) {
700 $where = '';
701 $this->logger->warning(
702 __METHOD__
703 . ' called from '
704 . $fname
705 . ' with incorrect parameters: $conds must be a string or an array',
706 [ 'db_log_category' => 'sql' ]
707 );
708 } elseif ( is_string( $conds ) ) {
709 $where = $conds;
710 } else {
711 throw new DBLanguageError( __METHOD__ . ' called with incorrect parameters' );
712 }
713
714 // Keep historical extra spaces after FROM to avoid testing failures
715 if ( $where === '' || $where === '*' ) {
716 $sql = "SELECT $startOpts $fields $from $preLimitTail";
717 } else {
718 $sql = "SELECT $startOpts $fields $from WHERE $where $preLimitTail";
719 }
720
721 if ( isset( $options['LIMIT'] ) ) {
722 $sql = $this->limitResult( $sql, $options['LIMIT'], $options['OFFSET'] ?? false );
723 }
724 $sql = "$sql $postLimitTail";
725
726 if ( isset( $options['EXPLAIN'] ) ) {
727 $sql = 'EXPLAIN ' . $sql;
728 }
729
730 if (
731 $fname === static::CALLER_UNKNOWN ||
732 str_starts_with( $fname, 'Wikimedia\\Rdbms\\' ) ||
733 $fname === '{closure}'
734 ) {
735 $exception = new RuntimeException();
736
737 // Try to figure out and report the real caller
738 $caller = '';
739 foreach ( $exception->getTrace() as $call ) {
740 if ( str_ends_with( $call['file'] ?? '', 'Test.php' ) ) {
741 // Don't warn when called directly by test code, adding callers there is pointless
742 break;
743 } elseif ( str_starts_with( $call['class'] ?? '', 'Wikimedia\\Rdbms\\' ) ) {
744 // Keep looking for the caller of a rdbms method
745 } elseif ( str_ends_with( $call['class'] ?? '', 'SelectQueryBuilder' ) ) {
746 // Keep looking for the caller of any custom SelectQueryBuilder
747 } else {
748 // Warn about the external caller we found
749 $caller = implode( '::', array_filter( [ $call['class'] ?? null, $call['function'] ] ) );
750 break;
751 }
752 }
753
754 if ( $fname === '{closure}' ) {
755 // Someone did ->caller( __METHOD__ ) in a local function, e.g. in a callback to
756 // getWithSetCallback(), MWCallableUpdate or doAtomicSection(). That's not very helpful.
757 // Provide a more specific message. The caller has to be provided like this:
758 // $method = __METHOD__;
759 // function ( ... ) use ( $method ) { ... }
760 $warning = "SQL query with incorrect caller (__METHOD__ used inside a closure: {caller}): {sql}";
761 } else {
762 $warning = "SQL query did not specify the caller (guessed caller: {caller}): {sql}";
763 }
764
765 $this->logger->warning(
766 $warning,
767 [ 'sql' => $sql, 'caller' => $caller, 'exception' => $exception ]
768 );
769 }
770
771 return $sql;
772 }
773
778 private function selectOptionsIncludeLocking( $options ) {
779 $options = (array)$options;
780 foreach ( [ 'FOR UPDATE', 'LOCK IN SHARE MODE' ] as $lock ) {
781 if ( in_array( $lock, $options, true ) ) {
782 return true;
783 }
784 }
785
786 return false;
787 }
788
794 private function selectFieldsOrOptionsAggregate( $fields, $options ) {
795 foreach ( (array)$options as $key => $value ) {
796 if ( is_string( $key ) ) {
797 if ( preg_match( '/^(?:GROUP BY|HAVING)$/i', $key ) ) {
798 return true;
799 }
800 } elseif ( is_string( $value ) ) {
801 if ( preg_match( '/^(?:DISTINCT|DISTINCTROW)$/i', $value ) ) {
802 return true;
803 }
804 }
805 }
806
807 $regex = '/^(?:COUNT|MIN|MAX|SUM|GROUP_CONCAT|LISTAGG|ARRAY_AGG)\s*\\(/i';
808 foreach ( (array)$fields as $field ) {
809 if ( is_string( $field ) && preg_match( $regex, $field ) ) {
810 return true;
811 }
812 }
813
814 return false;
815 }
816
823 protected function fieldNamesWithAlias( $fields ) {
824 $retval = [];
825 foreach ( $fields as $alias => $field ) {
826 if ( is_numeric( $alias ) ) {
827 $alias = $field;
828 }
829 $retval[] = $this->fieldNameWithAlias( $field, $alias );
830 }
831
832 return $retval;
833 }
834
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 ) {
1002 if ( $table instanceof Subquery ) {
1003 throw new InvalidArgumentException( "Subquery table missing alias" );
1004 }
1005 $quotedTableWithAnyAlias = $quotedTable;
1006 } elseif (
1007 $alias === $table &&
1008 (
1009 str_contains( $alias, '.' ) ||
1010 $this->tableName( $alias, 'raw' ) === $table
1011 )
1012 ) {
1013 $quotedTableWithAnyAlias = $quotedTable;
1014 } else {
1015 $quotedTableWithAnyAlias = $quotedTable . ' ' . $this->addIdentifierQuotes( $alias );
1016 }
1017
1018 return $quotedTableWithAnyAlias;
1019 }
1020
1021 public function tableName( string $name, $format = 'quoted' ) {
1022 $prefix = $this->currentDomain->getTablePrefix();
1023
1024 // Warn about table names that look qualified
1025 if (
1026 (
1027 str_contains( $name, '.' ) &&
1028 !preg_match( '/^information_schema\.[a-z_0-9]+$/', $name )
1029 ) ||
1030 ( $prefix !== '' && str_starts_with( $name, $prefix ) )
1031 ) {
1032 $this->logger->warning(
1033 __METHOD__ . ' called with qualified table ' . $name,
1034 [ 'db_log_category' => 'sql' ]
1035 );
1036 }
1037
1038 // Extract necessary database, schema, table identifiers and quote them as needed
1039 $formattedComponents = [];
1040 foreach ( $this->qualifiedTableComponents( $name ) as $component ) {
1041 if ( $format === 'quoted' ) {
1042 $formattedComponents[] = $this->addIdentifierQuotes( $component );
1043 } else {
1044 $formattedComponents[] = $component;
1045 }
1046 }
1047
1048 return implode( '.', $formattedComponents );
1049 }
1050
1073 public function qualifiedTableComponents( $name ) {
1074 $identifiers = $this->extractTableNameComponents( $name );
1075 if ( count( $identifiers ) > 3 ) {
1076 throw new DBLanguageError( "Too many components in table name '$name'" );
1077 }
1078 // Table alias config and prefixes only apply to unquoted single-identifier names
1079 if ( count( $identifiers ) == 1 && !$this->isQuotedIdentifier( $identifiers[0] ) ) {
1080 [ $table ] = $identifiers;
1081 if ( isset( $this->tableAliases[$table] ) ) {
1082 // This is an "alias" table that uses a different db/schema/prefix scheme
1083 $database = $this->tableAliases[$table]['dbname'];
1084 $schema = is_string( $this->tableAliases[$table]['schema'] )
1085 ? $this->tableAliases[$table]['schema']
1086 : $this->relationSchemaQualifier();
1087 $prefix = is_string( $this->tableAliases[$table]['prefix'] )
1088 ? $this->tableAliases[$table]['prefix']
1089 : $this->currentDomain->getTablePrefix();
1090 } else {
1091 // Use the current database domain to resolve the schema and prefix
1092 $database = '';
1093 $schema = $this->relationSchemaQualifier();
1094 $prefix = $this->currentDomain->getTablePrefix();
1095 }
1096 $qualifierIdentifiers = [ $database, $schema ];
1097 $tableIdentifier = $prefix . $table;
1098 } else {
1099 $qualifierIdentifiers = array_slice( $identifiers, 0, -1 );
1100 $tableIdentifier = end( $identifiers );
1101 }
1102
1103 $components = [];
1104 foreach ( $qualifierIdentifiers as $identifier ) {
1105 if ( $identifier !== null && $identifier !== '' ) {
1106 $components[] = $this->isQuotedIdentifier( $identifier )
1107 ? substr( $identifier, 1, -1 )
1108 : $identifier;
1109 }
1110 }
1111 $components[] = $this->isQuotedIdentifier( $tableIdentifier )
1112 ? substr( $tableIdentifier, 1, -1 )
1113 : $tableIdentifier;
1114
1115 return $components;
1116 }
1117
1124 public function extractTableNameComponents( string $name ) {
1125 $quoteChar = $this->getIdentifierQuoteChar();
1126 $components = [];
1127 foreach ( explode( '.', $name ) as $component ) {
1128 if ( $this->isQuotedIdentifier( $component ) ) {
1129 $unquotedComponent = substr( $component, 1, -1 );
1130 } else {
1131 $unquotedComponent = $component;
1132 }
1133 if ( str_contains( $unquotedComponent, $quoteChar ) ) {
1134 throw new DBLanguageError(
1135 'Table name component contains unexpected quote or dot character' );
1136 }
1137 $components[] = $component;
1138 }
1139 return $components;
1140 }
1141
1167 public function getDatabaseAndTableIdentifier( string $table ) {
1168 $components = $this->qualifiedTableComponents( $table );
1169 switch ( count( $components ) ) {
1170 case 1:
1171 return [ $this->currentDomain->getDatabase(), $components[0] ];
1172 case 2:
1173 return $components;
1174 default:
1175 throw new DBLanguageError( 'Too many table components' );
1176 }
1177 }
1178
1182 protected function relationSchemaQualifier() {
1183 return $this->currentDomain->getSchema();
1184 }
1185
1186 public function tableNamesN( ...$tables ) {
1187 $retVal = [];
1188
1189 foreach ( $tables as $name ) {
1190 $retVal[] = $this->tableName( $name );
1191 }
1192
1193 return $retVal;
1194 }
1195
1205 public function isQuotedIdentifier( $name ) {
1206 $quoteChar = $this->getIdentifierQuoteChar();
1207 return strlen( $name ) > 1 && $name[0] === $quoteChar && $name[-1] === $quoteChar;
1208 }
1209
1222 public function useIndexClause( $index ) {
1223 return '';
1224 }
1225
1234 public function ignoreIndexClause( $index ) {
1235 return '';
1236 }
1237
1248 protected function makeSelectOptions( array $options ) {
1249 $preLimitTail = $postLimitTail = '';
1250 $startOpts = '';
1251
1252 $noKeyOptions = [];
1253
1254 foreach ( $options as $key => $option ) {
1255 if ( is_numeric( $key ) ) {
1256 $noKeyOptions[$option] = true;
1257 }
1258 }
1259
1260 $preLimitTail .= $this->makeGroupByWithHaving( $options );
1261
1262 $preLimitTail .= $this->makeOrderBy( $options );
1263
1264 if ( isset( $noKeyOptions['FOR UPDATE'] ) ) {
1265 $postLimitTail .= ' FOR UPDATE';
1266 }
1267
1268 if ( isset( $noKeyOptions['LOCK IN SHARE MODE'] ) ) {
1269 $postLimitTail .= ' LOCK IN SHARE MODE';
1270 }
1271
1272 if ( isset( $noKeyOptions['DISTINCT'] ) || isset( $noKeyOptions['DISTINCTROW'] ) ) {
1273 $startOpts .= 'DISTINCT';
1274 }
1275
1276 # Various MySQL extensions
1277 if ( isset( $noKeyOptions['STRAIGHT_JOIN'] ) ) {
1278 $startOpts .= ' /*! STRAIGHT_JOIN */';
1279 }
1280
1281 if ( isset( $noKeyOptions['SQL_BIG_RESULT'] ) ) {
1282 $startOpts .= ' SQL_BIG_RESULT';
1283 }
1284
1285 if ( isset( $noKeyOptions['SQL_BUFFER_RESULT'] ) ) {
1286 $startOpts .= ' SQL_BUFFER_RESULT';
1287 }
1288
1289 if ( isset( $noKeyOptions['SQL_SMALL_RESULT'] ) ) {
1290 $startOpts .= ' SQL_SMALL_RESULT';
1291 }
1292
1293 if ( isset( $noKeyOptions['SQL_CALC_FOUND_ROWS'] ) ) {
1294 $startOpts .= ' SQL_CALC_FOUND_ROWS';
1295 }
1296
1297 return [ $startOpts, $preLimitTail, $postLimitTail ];
1298 }
1299
1308 protected function makeGroupByWithHaving( $options ) {
1309 $sql = '';
1310 if ( isset( $options['GROUP BY'] ) ) {
1311 $gb = is_array( $options['GROUP BY'] )
1312 ? implode( ',', $options['GROUP BY'] )
1313 : $options['GROUP BY'];
1314 $sql .= ' GROUP BY ' . $gb;
1315 }
1316 if ( isset( $options['HAVING'] ) ) {
1317 if ( $options['HAVING'] instanceof IExpression ) {
1318 $having = $options['HAVING']->toSql( $this->quoter );
1319 } elseif ( is_array( $options['HAVING'] ) ) {
1320 $having = $this->makeList( $options['HAVING'], self::LIST_AND );
1321 } else {
1322 $having = $options['HAVING'];
1323 }
1324
1325 $sql .= ' HAVING ' . $having;
1326 }
1327
1328 return $sql;
1329 }
1330
1339 protected function makeOrderBy( $options ) {
1340 if ( isset( $options['ORDER BY'] ) ) {
1341 $ob = is_array( $options['ORDER BY'] )
1342 ? implode( ',', $options['ORDER BY'] )
1343 : $options['ORDER BY'];
1344
1345 return ' ORDER BY ' . $ob;
1346 }
1347
1348 return '';
1349 }
1350
1351 public function buildGroupConcatField(
1352 $delim, $tables, $field, $conds = '', $join_conds = []
1353 ) {
1354 $fld = "GROUP_CONCAT($field SEPARATOR " . $this->quoter->addQuotes( $delim ) . ')';
1355
1356 return '(' . $this->selectSQLText( $tables, $fld, $conds, static::CALLER_SUBQUERY, [], $join_conds ) . ')';
1357 }
1358
1359 public function buildSelectSubquery(
1360 $tables, $vars, $conds = '', $fname = __METHOD__,
1361 $options = [], $join_conds = []
1362 ) {
1363 return new Subquery(
1364 $this->selectSQLText( $tables, $vars, $conds, $fname, $options, $join_conds )
1365 );
1366 }
1367
1368 public function insertSqlText( $table, array $rows ) {
1369 $encTable = $this->tableName( $table );
1370 [ $sqlColumns, $sqlTuples ] = $this->makeInsertLists( $rows );
1371
1372 return [
1373 "INSERT INTO $encTable ($sqlColumns) VALUES $sqlTuples",
1374 "INSERT INTO $encTable ($sqlColumns) VALUES '?'"
1375 ];
1376 }
1377
1390 public function makeInsertLists( array $rows, $aliasPrefix = '', array $typeByColumn = [] ) {
1391 $firstRow = $rows[0];
1392 if ( !is_array( $firstRow ) || !$firstRow ) {
1393 throw new DBLanguageError( 'Got an empty row list or empty row' );
1394 }
1395 // List of columns that define the value tuple ordering
1396 $tupleColumns = array_keys( $firstRow );
1397
1398 $valueTuples = [];
1399 foreach ( $rows as $row ) {
1400 $rowColumns = array_keys( $row );
1401 // VALUES(...) requires a uniform correspondence of (column => value)
1402 if ( $rowColumns !== $tupleColumns ) {
1403 throw new DBLanguageError(
1404 'All rows must specify the same columns in multi-row inserts. Found a row with (' .
1405 implode( ', ', $rowColumns ) . ') ' .
1406 'instead of expected (' . implode( ', ', $tupleColumns ) . ') as in the first row'
1407 );
1408 }
1409 // Make the value tuple that defines this row
1410 $valueTuples[] = '(' . $this->makeList( array_values( $row ), self::LIST_COMMA ) . ')';
1411 }
1412
1413 $magicAliasFields = [];
1414 foreach ( $tupleColumns as $column ) {
1415 $magicAliasFields[] = $aliasPrefix . $column;
1416 }
1417
1418 return [
1419 $this->makeList( $tupleColumns, self::LIST_NAMES ),
1420 implode( ',', $valueTuples ),
1421 $this->makeList( $magicAliasFields, self::LIST_NAMES )
1422 ];
1423 }
1424
1425 public function insertNonConflictingSqlText( $table, array $rows ) {
1426 $encTable = $this->tableName( $table );
1427 [ $sqlColumns, $sqlTuples ] = $this->makeInsertLists( $rows );
1428 [ $sqlVerb, $sqlOpts ] = $this->makeInsertNonConflictingVerbAndOptions();
1429
1430 return [
1431 rtrim( "$sqlVerb $encTable ($sqlColumns) VALUES $sqlTuples $sqlOpts" ),
1432 rtrim( "$sqlVerb $encTable ($sqlColumns) VALUES '?' $sqlOpts" )
1433 ];
1434 }
1435
1441 return [ 'INSERT IGNORE INTO', '' ];
1442 }
1443
1445 $destTable,
1446 $srcTable,
1447 array $varMap,
1448 $conds,
1449 $fname,
1450 array $insertOptions,
1451 array $selectOptions,
1452 $selectJoinConds
1453 ) {
1454 [ $sqlVerb, $sqlOpts ] = $this->isFlagInOptions( 'IGNORE', $insertOptions )
1455 ? $this->makeInsertNonConflictingVerbAndOptions()
1456 : [ 'INSERT INTO', '' ];
1457 $encDstTable = $this->tableName( $destTable );
1458 $sqlDstColumns = implode( ',', array_keys( $varMap ) );
1459 $selectSql = $this->selectSQLText(
1460 $srcTable,
1461 array_values( $varMap ),
1462 $conds,
1463 $fname,
1464 $selectOptions,
1465 $selectJoinConds
1466 );
1467
1468 return rtrim( "$sqlVerb $encDstTable ($sqlDstColumns) $selectSql $sqlOpts" );
1469 }
1470
1477 public function isFlagInOptions( $option, array $options ) {
1478 foreach ( array_keys( $options, $option, true ) as $k ) {
1479 if ( is_int( $k ) ) {
1480 return true;
1481 }
1482 }
1483
1484 return false;
1485 }
1486
1494 public function makeKeyCollisionCondition( array $rows, array $uniqueKey ) {
1495 if ( !$rows ) {
1496 throw new DBLanguageError( "Empty row array" );
1497 } elseif ( !$uniqueKey ) {
1498 throw new DBLanguageError( "Empty unique key array" );
1499 }
1500
1501 if ( count( $uniqueKey ) == 1 ) {
1502 // Use a simple IN(...) clause
1503 $column = reset( $uniqueKey );
1504 $values = array_column( $rows, $column );
1505 if ( count( $values ) !== count( $rows ) ) {
1506 throw new DBLanguageError( "Missing values for unique key ($column)" );
1507 }
1508
1509 return $this->makeList( [ $column => $values ], self::LIST_AND );
1510 }
1511
1512 $nullByUniqueKeyColumn = array_fill_keys( $uniqueKey, null );
1513
1514 $orConds = [];
1515 foreach ( $rows as $row ) {
1516 $rowKeyMap = array_intersect_key( $row, $nullByUniqueKeyColumn );
1517 if ( count( $rowKeyMap ) != count( $uniqueKey ) ) {
1518 throw new DBLanguageError(
1519 "Missing values for unique key (" . implode( ',', $uniqueKey ) . ")"
1520 );
1521 }
1522 $orConds[] = $this->makeList( $rowKeyMap, self::LIST_AND );
1523 }
1524
1525 return count( $orConds ) > 1
1526 ? $this->makeList( $orConds, self::LIST_OR )
1527 : $orConds[0];
1528 }
1529
1530 public function deleteJoinSqlText( $delTable, $joinTable, $delVar, $joinVar, $conds ) {
1531 if ( !$conds ) {
1532 throw new DBLanguageError( __METHOD__ . ' called with empty $conds' );
1533 }
1534
1535 $delTable = $this->tableName( $delTable );
1536 $joinTable = $this->tableName( $joinTable );
1537 $sql = "DELETE FROM $delTable WHERE $delVar IN (SELECT $joinVar FROM $joinTable ";
1538 if ( $conds != '*' ) {
1539 $sql .= 'WHERE ' . $this->makeList( $conds, self::LIST_AND );
1540 }
1541 $sql .= ')';
1542
1543 return $sql;
1544 }
1545
1551 public function deleteSqlText( $table, $conds ) {
1552 $isCondValid = ( is_string( $conds ) || is_array( $conds ) ) && $conds;
1553 if ( !$isCondValid ) {
1554 throw new DBLanguageError( __METHOD__ . ' called with empty conditions' );
1555 }
1556
1557 $encTable = $this->tableName( $table );
1558 $sql = "DELETE FROM $encTable";
1559
1560 $condsSql = '';
1561 $cleanCondsSql = '';
1562 if ( $conds !== self::ALL_ROWS && $conds !== [ self::ALL_ROWS ] ) {
1563 $cleanCondsSql = ' WHERE ' . $this->scrubArray( $conds );
1564 if ( is_array( $conds ) ) {
1565 $conds = $this->makeList( $conds, self::LIST_AND );
1566 }
1567 $condsSql .= ' WHERE ' . $conds;
1568 }
1569 return new Query(
1570 $sql . $condsSql,
1571 self::QUERY_CHANGE_ROWS,
1572 'DELETE',
1573 $table,
1574 $sql . $cleanCondsSql
1575 );
1576 }
1577
1582 private function scrubArray( $array, int $listType = self::LIST_AND ): string {
1583 if ( is_array( $array ) ) {
1584 $scrubbedArray = [];
1585 foreach ( $array as $key => $value ) {
1586 if ( $value instanceof IExpression ) {
1587 $scrubbedArray[$key] = $value->toGeneralizedSql();
1588 } else {
1589 $scrubbedArray[$key] = '?';
1590 }
1591 }
1592 return $this->makeList( $scrubbedArray, $listType );
1593 }
1594 return '?';
1595 }
1596
1597 public function updateSqlText( $table, $set, $conds, $options ) {
1598 $isCondValid = ( is_string( $conds ) || is_array( $conds ) ) && $conds;
1599 if ( !$isCondValid ) {
1600 throw new DBLanguageError( __METHOD__ . ' called with empty conditions' );
1601 }
1602 $encTable = $this->tableName( $table );
1603 $opts = $this->makeUpdateOptions( $options );
1604 $sql = "UPDATE $opts $encTable";
1605 $condsSql = " SET " . $this->makeList( $set, self::LIST_SET );
1606 $cleanCondsSql = " SET " . $this->scrubArray( $set, self::LIST_SET );
1607
1608 if ( $conds && $conds !== self::ALL_ROWS && $conds !== [ self::ALL_ROWS ] ) {
1609 $cleanCondsSql .= ' WHERE ' . $this->scrubArray( $conds );
1610 if ( is_array( $conds ) ) {
1611 $conds = $this->makeList( $conds, self::LIST_AND );
1612 }
1613 $condsSql .= ' WHERE ' . $conds;
1614 }
1615 return new Query(
1616 $sql . $condsSql,
1617 self::QUERY_CHANGE_ROWS,
1618 'UPDATE',
1619 $table,
1620 $sql . $cleanCondsSql
1621 );
1622 }
1623
1630 protected function makeUpdateOptions( $options ) {
1631 $opts = $this->makeUpdateOptionsArray( $options );
1632
1633 return implode( ' ', $opts );
1634 }
1635
1642 protected function makeUpdateOptionsArray( $options ) {
1643 $options = $this->normalizeOptions( $options );
1644
1645 $opts = [];
1646
1647 if ( in_array( 'IGNORE', $options ) ) {
1648 $opts[] = 'IGNORE';
1649 }
1650
1651 return $opts;
1652 }
1653
1659 final public function normalizeOptions( $options ) {
1660 if ( is_array( $options ) ) {
1661 return $options;
1662 } elseif ( is_string( $options ) ) {
1663 return ( $options === '' ) ? [] : [ $options ];
1664 } else {
1665 throw new DBLanguageError( __METHOD__ . ': expected string or array' );
1666 }
1667 }
1668
1669 public function dropTableSqlText( $table ) {
1670 // https://mariadb.com/kb/en/drop-table/
1671 // https://dev.mysql.com/doc/refman/8.0/en/drop-table.html
1672 // https://www.postgresql.org/docs/9.2/sql-truncate.html
1673 return "DROP TABLE " . $this->tableName( $table ) . " CASCADE";
1674 }
1675
1681 public function getQueryVerb( $sql ) {
1682 wfDeprecated( __METHOD__, '1.42' );
1683 return QueryBuilderFromRawSql::buildQuery( $sql, 0 )->getVerb();
1684 }
1685
1698 public function isTransactableQuery( Query $sql ) {
1699 return !in_array(
1700 $sql->getVerb(),
1701 [
1702 'BEGIN',
1703 'ROLLBACK',
1704 'ROLLBACK TO SAVEPOINT',
1705 'COMMIT',
1706 'SET',
1707 'SHOW',
1708 'CREATE',
1709 'ALTER',
1710 'USE',
1711 'SHOW'
1712 ],
1713 true
1714 );
1715 }
1716
1717 public function buildExcludedValue( $column ) {
1718 /* @see Database::upsert() */
1719 // This can be treated like a single value since __VALS is a single row table
1720 return "(SELECT __$column FROM __VALS)";
1721 }
1722
1723 public function savepointSqlText( $identifier ) {
1724 return 'SAVEPOINT ' . $this->addIdentifierQuotes( $identifier );
1725 }
1726
1727 public function releaseSavepointSqlText( $identifier ) {
1728 return 'RELEASE SAVEPOINT ' . $this->addIdentifierQuotes( $identifier );
1729 }
1730
1731 public function rollbackToSavepointSqlText( $identifier ) {
1732 return 'ROLLBACK TO SAVEPOINT ' . $this->addIdentifierQuotes( $identifier );
1733 }
1734
1735 public function rollbackSqlText() {
1736 return 'ROLLBACK';
1737 }
1738
1739 public function dispatchingInsertSqlText( $table, $rows, $options ) {
1740 $rows = $this->normalizeRowArray( $rows );
1741 if ( !$rows ) {
1742 return false;
1743 }
1744
1745 $options = $this->normalizeOptions( $options );
1746 if ( $this->isFlagInOptions( 'IGNORE', $options ) ) {
1747 [ $sql, $cleanSql ] = $this->insertNonConflictingSqlText( $table, $rows );
1748 } else {
1749 [ $sql, $cleanSql ] = $this->insertSqlText( $table, $rows );
1750 }
1751 return new Query( $sql, self::QUERY_CHANGE_ROWS, 'INSERT', $table, $cleanSql );
1752 }
1753
1759 final protected function normalizeRowArray( array $rowOrRows ) {
1760 if ( !$rowOrRows ) {
1761 $rows = [];
1762 } elseif ( isset( $rowOrRows[0] ) ) {
1763 $rows = $rowOrRows;
1764 } else {
1765 $rows = [ $rowOrRows ];
1766 }
1767
1768 foreach ( $rows as $row ) {
1769 if ( !is_array( $row ) ) {
1770 throw new DBLanguageError( "Got non-array in row array" );
1771 } elseif ( !$row ) {
1772 throw new DBLanguageError( "Got empty array in row array" );
1773 }
1774 }
1775
1776 return $rows;
1777 }
1778
1787 final public function normalizeUpsertParams( $uniqueKeys, &$rows ) {
1788 $rows = $this->normalizeRowArray( $rows );
1789 if ( !$uniqueKeys ) {
1790 throw new DBLanguageError( 'No unique key specified for upsert/replace' );
1791 }
1792 $uniqueKey = $this->normalizeUpsertKeys( $uniqueKeys );
1793 $this->assertValidUpsertRowArray( $rows, $uniqueKey );
1794
1795 return $uniqueKey;
1796 }
1797
1804 final public function normalizeConditions( $conds, $fname ) {
1805 if ( $conds === null || $conds === false ) {
1806 $this->logger->warning(
1807 __METHOD__
1808 . ' called from '
1809 . $fname
1810 . ' with incorrect parameters: $conds must be a string or an array',
1811 [ 'db_log_category' => 'sql' ]
1812 );
1813 return [];
1814 } elseif ( $conds === '' ) {
1815 return [];
1816 }
1817
1818 return is_array( $conds ) ? $conds : [ $conds ];
1819 }
1820
1826 private function normalizeUpsertKeys( $uniqueKeys ) {
1827 if ( is_string( $uniqueKeys ) ) {
1828 return [ $uniqueKeys ];
1829 } elseif ( !is_array( $uniqueKeys ) ) {
1830 throw new DBLanguageError( 'Invalid unique key array' );
1831 } else {
1832 if ( count( $uniqueKeys ) !== 1 || !isset( $uniqueKeys[0] ) ) {
1833 throw new DBLanguageError(
1834 "The unique key array should contain a single unique index" );
1835 }
1836
1837 $uniqueKey = $uniqueKeys[0];
1838 if ( is_string( $uniqueKey ) ) {
1839 // Passing a list of strings for single-column unique keys is too
1840 // easily confused with passing the columns of composite unique key
1841 $this->logger->warning( __METHOD__ .
1842 " called with deprecated parameter style: " .
1843 "the unique key array should be a string or array of string arrays",
1844 [
1845 'exception' => new RuntimeException(),
1846 'db_log_category' => 'sql',
1847 ] );
1848 return $uniqueKeys;
1849 } elseif ( is_array( $uniqueKey ) ) {
1850 return $uniqueKey;
1851 } else {
1852 throw new DBLanguageError( 'Invalid unique key array entry' );
1853 }
1854 }
1855 }
1856
1862 final protected function assertValidUpsertRowArray( array $rows, array $uniqueKey ) {
1863 foreach ( $rows as $row ) {
1864 foreach ( $uniqueKey as $column ) {
1865 if ( !isset( $row[$column] ) ) {
1866 throw new DBLanguageError(
1867 "NULL/absent values for unique key (" . implode( ',', $uniqueKey ) . ")"
1868 );
1869 }
1870 }
1871 }
1872 }
1873
1880 final public function assertValidUpsertSetArray(
1881 array $set,
1882 array $uniqueKey,
1883 array $rows
1884 ) {
1885 if ( !$set ) {
1886 throw new DBLanguageError( "Update assignment list can't be empty for upsert" );
1887 }
1888
1889 // Sloppy callers might construct the SET array using the ROW array, leaving redundant
1890 // column definitions for unique key columns. Detect this for backwards compatibility.
1891 $soleRow = ( count( $rows ) == 1 ) ? reset( $rows ) : null;
1892 // Disallow value changes for any columns in the unique key. This avoids additional
1893 // insertion order dependencies that are unwieldy and difficult to implement efficiently
1894 // in PostgreSQL.
1895 foreach ( $set as $k => $v ) {
1896 if ( is_string( $k ) ) {
1897 // Key is a column name and value is a literal (e.g. string, int, null, ...)
1898 if ( in_array( $k, $uniqueKey, true ) ) {
1899 if ( $soleRow && array_key_exists( $k, $soleRow ) && $soleRow[$k] === $v ) {
1900 $this->logger->warning(
1901 __METHOD__ . " called with redundant assignment to column '$k'",
1902 [
1903 'exception' => new RuntimeException(),
1904 'db_log_category' => 'sql',
1905 ]
1906 );
1907 } else {
1908 throw new DBLanguageError(
1909 "Cannot reassign column '$k' since it belongs to the provided unique key"
1910 );
1911 }
1912 }
1913 } elseif ( preg_match( '/^([a-zA-Z0-9_]+)\s*=/', $v, $m ) ) {
1914 // Value is of the form "<unquoted alphanumeric column> = <SQL expression>"
1915 if ( in_array( $m[1], $uniqueKey, true ) ) {
1916 throw new DBLanguageError(
1917 "Cannot reassign column '{$m[1]}' since it belongs to the provided unique key"
1918 );
1919 }
1920 }
1921 }
1922 }
1923
1928 final public function extractSingleFieldFromList( $var ) {
1929 if ( is_array( $var ) ) {
1930 if ( !$var ) {
1931 $column = null;
1932 } elseif ( count( $var ) == 1 ) {
1933 $column = $var[0] ?? reset( $var );
1934 } else {
1935 throw new DBLanguageError( __METHOD__ . ': got multiple columns' );
1936 }
1937 } else {
1938 $column = $var;
1939 }
1940
1941 return $column;
1942 }
1943
1944 public function setSchemaVars( $vars ) {
1945 $this->schemaVars = is_array( $vars ) ? $vars : null;
1946 }
1947
1954 protected function getSchemaVars() {
1955 return $this->schemaVars ?? $this->getDefaultSchemaVars();
1956 }
1957
1966 protected function getDefaultSchemaVars() {
1967 return [];
1968 }
1969
1990 public function replaceVars( $ins ) {
1991 $vars = $this->getSchemaVars();
1992 return preg_replace_callback(
1993 '!
1994 /\* (\$wgDBprefix|[_i]) \*/ (\w*) | # 1-2. tableName, indexName
1995 \'\{\$ (\w+) }\' | # 3. addQuotes
1996 `\{\$ (\w+) }` | # 4. addIdentifierQuotes
1997 /\*\$ (\w+) \*/ # 5. leave unencoded
1998 !x',
1999 function ( $m ) use ( $vars ) {
2000 // Note: Because of <https://bugs.php.net/bug.php?id=51881>,
2001 // check for both nonexistent keys *and* the empty string.
2002 if ( isset( $m[1] ) && $m[1] !== '' ) {
2003 if ( $m[1] === 'i' ) {
2004 return $m[2];
2005 } else {
2006 return $this->tableName( $m[2] );
2007 }
2008 } elseif ( isset( $m[3] ) && $m[3] !== '' && array_key_exists( $m[3], $vars ) ) {
2009 return $this->quoter->addQuotes( $vars[$m[3]] );
2010 } elseif ( isset( $m[4] ) && $m[4] !== '' && array_key_exists( $m[4], $vars ) ) {
2011 return $this->addIdentifierQuotes( $vars[$m[4]] );
2012 } elseif ( isset( $m[5] ) && $m[5] !== '' && array_key_exists( $m[5], $vars ) ) {
2013 return $vars[$m[5]];
2014 } else {
2015 return $m[0];
2016 }
2017 },
2018 $ins
2019 );
2020 }
2021
2022 public function lockSQLText( $lockName, $timeout ) {
2023 throw new RuntimeException( 'locking must be implemented in subclasses' );
2024 }
2025
2026 public function lockIsFreeSQLText( $lockName ) {
2027 throw new RuntimeException( 'locking must be implemented in subclasses' );
2028 }
2029
2030 public function unlockSQLText( $lockName ) {
2031 throw new RuntimeException( 'locking must be implemented in subclasses' );
2032 }
2033}
wfDeprecated( $function, $version=false, $component=false, $callerOffset=2)
Logs a warning that a deprecated feature was used.
if(!defined('MW_SETUP_CALLBACK'))
Definition WebStart.php:82
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...
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()
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.
__construct(DbQuoter $quoter, ?LoggerInterface $logger=null, ?DatabaseDomain $currentDomain=null, $errorLogger=null)
array null $schemaVars
Current variables use for schema element placeholders.
ignoreIndexClause( $index)
IGNORE INDEX clause.
makeOrderBy( $options)
Returns an optional ORDER BY.
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.
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.
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.
Holds information on Query to be executed.
Definition Query.php:31
Raw SQL value to be used in query builders.
Interface for query language.