MediaWiki  master
SelectQueryBuilder.php
Go to the documentation of this file.
1 <?php
2 
3 namespace Wikimedia\Rdbms;
4 
25 
27  public const SORT_ASC = 'ASC';
28 
30  public const SORT_DESC = 'DESC';
31 
35  private $fields = [];
36 
40  private $conds = [];
41 
45  private $caller = __CLASS__;
46 
50  protected $options = [];
51 
55  private $nextAutoAlias = 1;
56 
60  private $isCallerOverridden = false;
61 
64 
71  public function __construct( IReadableDatabase $db ) {
72  $this->db = $db;
73  }
74 
82  public function connection( IReadableDatabase $db ) {
83  if ( $this->db->getType() !== $db->getType() ) {
84  throw new \InvalidArgumentException( __METHOD__ .
85  ' cannot switch to a database of a different type.' );
86  }
87  $this->db = $db;
88  return $this;
89  }
90 
111  public function queryInfo( $info ) {
112  if ( isset( $info['tables'] ) ) {
113  $this->rawTables( $info['tables'] );
114  }
115  if ( isset( $info['fields'] ) ) {
116  $this->fields( $info['fields'] );
117  }
118  if ( isset( $info['conds'] ) ) {
119  $this->where( $info['conds'] );
120  }
121  if ( isset( $info['options'] ) ) {
122  $this->options( (array)$info['options'] );
123  }
124  if ( isset( $info['join_conds'] ) ) {
125  $this->joinConds( (array)$info['join_conds'] );
126  }
127  if ( isset( $info['joins'] ) ) {
128  $this->joinConds( (array)$info['joins'] );
129  }
130  if ( isset( $info['caller'] ) ) {
131  $this->caller( $info['caller'] );
132  }
133  return $this;
134  }
135 
148  public function rawTables( $tables ) {
149  if ( is_array( $tables ) ) {
150  $this->tables = array_merge( $this->tables, $tables );
151  } elseif ( is_string( $tables ) ) {
152  $this->tables[] = $tables;
153  } else {
154  throw new \InvalidArgumentException( __METHOD__ .
155  ': $tables must be a string or array' );
156  }
157  return $this;
158  }
159 
168  public function merge( SelectQueryBuilder $builder ) {
169  $this->rawTables( $builder->tables );
170  $this->fields( $builder->fields );
171  $this->where( $builder->conds );
172  $this->options( $builder->options );
173  $this->joinConds( $builder->joinConds );
174  if ( $builder->isCallerOverridden ) {
175  $this->caller( $builder->caller );
176  }
177  return $this;
178  }
179 
185  public function newSubquery() {
186  return new self( $this->db );
187  }
188 
198  public function from( $table, $alias = null ) {
199  return $this->table( $table, $alias );
200  }
201 
209  public function tables( $tables ) {
210  foreach ( $tables as $alias => $table ) {
211  if ( is_string( $alias ) ) {
212  $this->table( $table, $alias );
213  } else {
214  $this->table( $table );
215  }
216  }
217  return $this;
218  }
219 
231  public function fields( $fields ) {
232  if ( is_array( $fields ) ) {
233  $this->fields = array_merge( $this->fields, $fields );
234  } else {
235  $this->fields[] = $fields;
236  }
237  return $this;
238  }
239 
247  public function select( $fields ) {
248  return $this->fields( $fields );
249  }
250 
261  public function field( $field, $alias = null ) {
262  if ( $alias === null ) {
263  $this->fields[] = $field;
264  } else {
265  $this->fields[$alias] = $field;
266  }
267  return $this;
268  }
269 
275  public function clearFields() {
276  $this->fields = [];
277  return $this;
278  }
279 
317  public function where( $conds ) {
318  if ( is_array( $conds ) ) {
319  foreach ( $conds as $key => $cond ) {
320  if ( is_int( $key ) ) {
321  $this->conds[] = $cond;
322  } elseif ( isset( $this->conds[$key] ) ) {
323  // @phan-suppress-previous-line PhanTypeMismatchDimFetch
324  // T288882
325  $this->conds[] = $this->db->makeList(
326  [ $key => $cond ], IReadableDatabase::LIST_AND );
327  } else {
328  $this->conds[$key] = $cond;
329  }
330  }
331  } else {
332  $this->conds[] = $conds;
333  }
334  return $this;
335  }
336 
344  public function andWhere( $conds ) {
345  return $this->where( $conds );
346  }
347 
355  public function conds( $conds ) {
356  return $this->where( $conds );
357  }
358 
367  public function joinConds( array $joinConds ) {
368  $this->joinConds = array_merge( $this->joinConds, $joinConds );
369  return $this;
370  }
371 
377  protected function getAutoAlias() {
378  return 'sqb' . ( $this->nextAutoAlias++ );
379  }
380 
387  public function newJoinGroup() {
388  return new JoinGroup( $this->getAutoAlias() );
389  }
390 
401  public function offset( $offset ) {
402  $this->options['OFFSET'] = $offset;
403  return $this;
404  }
405 
416  public function limit( $limit ) {
417  $this->options['LIMIT'] = $limit;
418  return $this;
419  }
420 
428  public function lockInShareMode() {
429  $this->options[] = 'LOCK IN SHARE MODE';
430  return $this;
431  }
432 
440  public function forUpdate() {
441  $this->options[] = 'FOR UPDATE';
442  return $this;
443  }
444 
450  public function distinct() {
451  $this->options[] = 'DISTINCT';
452  return $this;
453  }
454 
461  public function setMaxExecutionTime( int $time ) {
462  $this->options['MAX_EXECUTION_TIME'] = $time;
463  return $this;
464  }
465 
475  public function groupBy( $group ) {
476  $this->mergeOption( 'GROUP BY', $group );
477  return $this;
478  }
479 
491  public function having( $having ) {
492  $this->mergeOption( 'HAVING', $having );
493  return $this;
494  }
495 
506  public function orderBy( $fields, $direction = null ) {
507  if ( $direction === null ) {
508  $this->mergeOption( 'ORDER BY', $fields );
509  } elseif ( is_array( $fields ) ) {
510  $fieldsWithDirection = [];
511  foreach ( $fields as $field ) {
512  $fieldsWithDirection[] = "$field $direction";
513  }
514  $this->mergeOption( 'ORDER BY', $fieldsWithDirection );
515  } else {
516  $this->mergeOption( 'ORDER BY', "$fields $direction" );
517  }
518  return $this;
519  }
520 
527  private function mergeOption( $name, $newArrayOrValue ) {
528  $value = isset( $this->options[$name] )
529  ? (array)$this->options[$name] : [];
530  if ( is_array( $newArrayOrValue ) ) {
531  $value = array_merge( $value, $newArrayOrValue );
532  } else {
533  $value[] = $newArrayOrValue;
534  }
535  $this->options[$name] = $value;
536  }
537 
550  public function useIndex( $index ) {
551  $this->setIndexHint( 'USE INDEX', $index );
552  return $this;
553  }
554 
567  public function ignoreIndex( $index ) {
568  $this->setIndexHint( 'IGNORE INDEX', $index );
569  return $this;
570  }
571 
578  private function setIndexHint( $type, $value ) {
579  if ( !isset( $this->options[$type] ) ) {
580  $this->options[$type] = [];
581  } elseif ( !is_array( $this->options[$type] ) ) {
582  throw new \UnexpectedValueException(
583  __METHOD__ . ": The $type option cannot be appended to " .
584  'because it is not an array. This may have been caused by a prior ' .
585  'call to option() or options().' );
586  }
587  if ( is_array( $value ) ) {
588  $this->options[$type] = array_merge( $this->options[$type], $value );
589  } elseif ( $this->lastAlias === null ) {
590  throw new \UnexpectedValueException(
591  __METHOD__ . ': Cannot append index value since there is no' .
592  'prior table' );
593  } else {
594  $this->options[$type][$this->lastAlias] = $value;
595  }
596  }
597 
603  public function explain() {
604  $this->options['EXPLAIN'] = true;
605  return $this;
606  }
607 
613  public function straightJoinOption() {
614  $this->options[] = 'STRAIGHT_JOIN';
615  return $this;
616  }
617 
623  public function bigResult() {
624  $this->options[] = 'SQL_BIG_RESULT';
625  return $this;
626  }
627 
633  public function bufferResult() {
634  $this->options[] = 'SQL_BUFFER_RESULT';
635  return $this;
636  }
637 
643  public function smallResult() {
644  $this->options[] = 'SQL_SMALL_RESULT';
645  return $this;
646  }
647 
653  public function calcFoundRows() {
654  $this->options[] = 'SQL_CALC_FOUND_ROWS';
655  return $this;
656  }
657 
666  public function option( $name, $value = null ) {
667  if ( $value === null ) {
668  $this->options[] = $name;
669  } else {
670  $this->options[$name] = $value;
671  }
672  return $this;
673  }
674 
682  public function options( array $options ) {
683  $this->options = array_merge( $this->options, $options );
684  return $this;
685  }
686 
694  public function caller( $fname ) {
695  $this->caller = $fname;
696  $this->isCallerOverridden = true;
697  return $this;
698  }
699 
706  public function fetchResultSet() {
707  return $this->db->select( $this->tables, $this->fields, $this->conds, $this->caller,
708  $this->options, $this->joinConds );
709  }
710 
719  public function fetchField() {
720  if ( count( $this->fields ) !== 1 ) {
721  throw new \UnexpectedValueException(
722  __METHOD__ . ' expects the query to have only one field' );
723  }
724  $field = reset( $this->fields );
725  return $this->db->selectField( $this->tables, $field, $this->conds, $this->caller,
726  $this->options, $this->joinConds );
727  }
728 
737  public function fetchFieldValues() {
738  if ( count( $this->fields ) !== 1 ) {
739  throw new \UnexpectedValueException(
740  __METHOD__ . ' expects the query to have only one field' );
741  }
742  $field = reset( $this->fields );
743  return $this->db->selectFieldValues( $this->tables, $field, $this->conds, $this->caller,
744  $this->options, $this->joinConds );
745  }
746 
754  public function fetchRow() {
755  return $this->db->selectRow( $this->tables, $this->fields, $this->conds, $this->caller,
756  $this->options, $this->joinConds );
757  }
758 
771  public function fetchRowCount() {
772  return $this->db->selectRowCount( $this->tables, $this->getRowCountVar(), $this->conds,
773  $this->caller, $this->options, $this->joinConds );
774  }
775 
786  public function estimateRowCount() {
787  return $this->db->estimateRowCount( $this->tables, $this->getRowCountVar(), $this->conds,
788  $this->caller, $this->options, $this->joinConds );
789  }
790 
797  private function getRowCountVar() {
798  if ( count( $this->fields ) === 0 ) {
799  return '*';
800  } elseif ( count( $this->fields ) === 1 ) {
801  return reset( $this->fields );
802  } else {
803  throw new \UnexpectedValueException(
804  __METHOD__ . ' expects the query to have at most one field' );
805  }
806  }
807 
819  public function buildGroupConcatField( $delim ) {
820  if ( count( $this->fields ) !== 1 ) {
821  throw new \UnexpectedValueException(
822  __METHOD__ . ' expects the query to have only one field' );
823  }
824  $field = reset( $this->fields );
825  return $this->db->buildGroupConcatField( $delim, $this->tables, $field,
826  $this->conds, $this->joinConds );
827  }
828 
834  public function getSQL() {
835  return $this->db->selectSQLText( $this->tables, $this->fields, $this->conds, $this->caller,
836  $this->options, $this->joinConds );
837  }
838 
854  public function getQueryInfo( $joinsName = 'join_conds' ) {
855  $info = [
856  'tables' => $this->tables,
857  'fields' => $this->fields,
858  'conds' => $this->conds,
859  'options' => $this->options,
860  ];
861  if ( $this->caller !== __CLASS__ ) {
862  $info['caller'] = $this->caller;
863  }
864  $info[ $joinsName ] = $this->joinConds;
865  return $info;
866  }
867 
882  public function acquireRowLocks(): void {
883  if ( !array_intersect( $this->options, [ 'FOR UPDATE', 'LOCK IN SHARE MODE' ] ) ) {
884  throw new \UnexpectedValueException( __METHOD__ . ' can only be called ' .
885  'after forUpdate() or lockInShareMode()' );
886  }
887  $fields = $this->fields ?: '1';
888  $this->db->select( $this->tables, $fields, $this->conds, $this->caller,
889  $this->options, $this->joinConds );
890  }
891 }
const LIST_AND
Definition: Defines.php:43
if(!defined('MW_SETUP_CALLBACK'))
Definition: WebStart.php:88
Shared code between SelectQueryBuilder and JoinGroup to represent tables and join conditions.
table( $table, $alias=null)
Add a single table or a single parenthesized group.
Parenthesized group of table names and their join types and conditions.
Definition: JoinGroup.php:10
Build SELECT queries with a fluent interface.
estimateRowCount()
Estimate the number of rows in dataset.
getQueryInfo( $joinsName='join_conds')
Get an associative array describing the query in terms of its raw parameters to Database::select().
__construct(IReadableDatabase $db)
Only for use in subclasses.
distinct()
Enable the DISTINCT option.
straightJoinOption()
Enable the STRAIGHT_JOIN query option.
groupBy( $group)
Add a GROUP BY clause.
newSubquery()
Get an empty SelectQueryBuilder which can be used to build a subquery of this query.
useIndex( $index)
Set a USE INDEX option.
rawTables( $tables)
Given a table or table array as might be passed to Database::select(), append it to the existing tabl...
conds( $conds)
Add conditions to the query.
limit( $limit)
Set the query limit.
clearFields()
Remove all fields from the query.
andWhere( $conds)
Add conditions to the query.
fetchField()
Run the constructed SELECT query, and return a single field extracted from the first result row.
array $options
The options to be passed to IReadableDatabase::select()
fetchFieldValues()
Run the constructed SELECT query, and extract a single field from each result row,...
calcFoundRows()
Enable the SQL_CALC_FOUND_ROWS option.
newJoinGroup()
Create a parenthesized group of joins which can be added to the object like a table.
fetchResultSet()
Run the constructed SELECT query and return all results.
lockInShareMode()
Enable the LOCK IN SHARE MODE option.
bufferResult()
Enable the SQL_BUFFER_RESULT option.
merge(SelectQueryBuilder $builder)
Merge another query builder with this one.
queryInfo( $info)
Set the query parameters to the given values, appending to the values which were already set.
options(array $options)
Manually set multiple options in the $options array to be passed to IReadableDatabase::select().
forUpdate()
Enable the FOR UPDATE option.
option( $name, $value=null)
Manually set an option in the $options array to be passed to IReadableDatabase::select()
buildGroupConcatField( $delim)
Build a GROUP_CONCAT or equivalent statement for a query.
IReadableDatabase IReadableDatabase $db
select( $fields)
Add a field or an array of fields to the query.
caller( $fname)
Set the method name to be included in an SQL comment.
tables( $tables)
Add multiple tables.
acquireRowLocks()
Execute the query, but throw away the results.
ignoreIndex( $index)
Set the IGNORE INDEX option.
having( $having)
Add a HAVING clause.
from( $table, $alias=null)
Add a single table to the SELECT query.
setMaxExecutionTime(int $time)
Set MAX_EXECUTION_TIME for queries.
field( $field, $alias=null)
Add a single field to the query, optionally with an alias.
orderBy( $fields, $direction=null)
Set the ORDER BY clause.
connection(IReadableDatabase $db)
Change the IReadableDatabase object the query builder is bound to.
smallResult()
Enable the SQL_SMALL_RESULT option.
explain()
Make the query be an EXPLAIN SELECT query instead of a SELECT query.
bigResult()
Enable the SQL_BIG_RESULT option.
fetchRowCount()
Run the SELECT query, and return the number of results.
fetchRow()
Run the constructed SELECT query, and return the first result row.
joinConds(array $joinConds)
Manually append to the $join_conds array which will be passed to IReadableDatabase::select().
fields( $fields)
Add a field or an array of fields to the query.
getAutoAlias()
Get a table alias which is unique to this SelectQueryBuilder.
where( $conds)
Add conditions to the query.
getSQL()
Get the SQL query string which would be used by fetchResultSet().
A database connection without write operations.
getType()
Get the RDBMS type of the server (e.g.