3 use Wikimedia\Rdbms\IDatabase
;
4 use Wikimedia\Rdbms\LikeMatch
;
5 use Wikimedia\Rdbms\Database
;
8 * Test the parts of the Database abstract class that deal
9 * with creating SQL text.
11 class DatabaseSQLTest
extends PHPUnit\Framework\TestCase
{
13 use MediaWikiCoversValidator
;
15 /** @var DatabaseTestHelper|Database */
18 protected function setUp() {
20 $this->database
= new DatabaseTestHelper( __CLASS__
, [ 'cliMode' => true ] );
23 protected function assertLastSql( $sqlText ) {
26 $this->database
->getLastSqls()
30 protected function assertLastSqlDb( $sqlText, DatabaseTestHelper
$db ) {
31 $this->assertEquals( $sqlText, $db->getLastSqls() );
35 * @dataProvider provideSelect
36 * @covers Wikimedia\Rdbms\Database::select
37 * @covers Wikimedia\Rdbms\Database::selectSQLText
38 * @covers Wikimedia\Rdbms\Database::tableNamesWithIndexClauseOrJOIN
39 * @covers Wikimedia\Rdbms\Database::useIndexClause
40 * @covers Wikimedia\Rdbms\Database::ignoreIndexClause
41 * @covers Wikimedia\Rdbms\Database::makeSelectOptions
42 * @covers Wikimedia\Rdbms\Database::makeOrderBy
43 * @covers Wikimedia\Rdbms\Database::makeGroupByWithHaving
45 public function testSelect( $sql, $sqlText ) {
46 $this->database
->select(
49 isset( $sql['conds'] ) ?
$sql['conds'] : [],
51 isset( $sql['options'] ) ?
$sql['options'] : [],
52 isset( $sql['join_conds'] ) ?
$sql['join_conds'] : []
54 $this->assertLastSql( $sqlText );
57 public static function provideSelect() {
62 'fields' => [ 'field', 'alias' => 'field2' ],
63 'conds' => [ 'alias' => 'text' ],
65 "SELECT field,field2 AS alias " .
67 "WHERE alias = 'text'"
72 'fields' => [ 'field', 'alias' => 'field2' ],
73 'conds' => 'alias = \'text\'',
75 "SELECT field,field2 AS alias " .
77 "WHERE alias = 'text'"
82 'fields' => [ 'field', 'alias' => 'field2' ],
85 "SELECT field,field2 AS alias " .
91 'fields' => [ 'field', 'alias' => 'field2' ],
94 "SELECT field,field2 AS alias " .
100 'fields' => [ 'field', 'alias' => 'field2' ],
101 'conds' => '0', // T188314
103 "SELECT field,field2 AS alias " .
109 // 'tables' with space prepended indicates pre-escaped table name
110 'tables' => ' table LEFT JOIN table2',
111 'fields' => [ 'field' ],
112 'conds' => [ 'field' => 'text' ],
114 "SELECT field FROM table LEFT JOIN table2 WHERE field = 'text'"
118 // Empty 'tables' is allowed
120 'fields' => [ 'SPECIAL_QUERY()' ],
122 "SELECT SPECIAL_QUERY()"
127 'fields' => [ 'field', 'alias' => 'field2' ],
128 'conds' => [ 'alias' => 'text' ],
129 'options' => [ 'LIMIT' => 1, 'ORDER BY' => 'field' ],
131 "SELECT field,field2 AS alias " .
133 "WHERE alias = 'text' " .
139 'tables' => [ 'table', 't2' => 'table2' ],
140 'fields' => [ 'tid', 'field', 'alias' => 'field2', 't2.id' ],
141 'conds' => [ 'alias' => 'text' ],
142 'options' => [ 'LIMIT' => 1, 'ORDER BY' => 'field' ],
143 'join_conds' => [ 't2' => [
144 'LEFT JOIN', 'tid = t2.id'
147 "SELECT tid,field,field2 AS alias,t2.id " .
148 "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
149 "WHERE alias = 'text' " .
155 'tables' => [ 'table', 't2' => 'table2' ],
156 'fields' => [ 'tid', 'field', 'alias' => 'field2', 't2.id' ],
157 'conds' => [ 'alias' => 'text' ],
158 'options' => [ 'LIMIT' => 1, 'GROUP BY' => 'field', 'HAVING' => 'COUNT(*) > 1' ],
159 'join_conds' => [ 't2' => [
160 'LEFT JOIN', 'tid = t2.id'
163 "SELECT tid,field,field2 AS alias,t2.id " .
164 "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
165 "WHERE alias = 'text' " .
166 "GROUP BY field HAVING COUNT(*) > 1 " .
171 'tables' => [ 'table', 't2' => 'table2' ],
172 'fields' => [ 'tid', 'field', 'alias' => 'field2', 't2.id' ],
173 'conds' => [ 'alias' => 'text' ],
176 'GROUP BY' => [ 'field', 'field2' ],
177 'HAVING' => [ 'COUNT(*) > 1', 'field' => 1 ]
179 'join_conds' => [ 't2' => [
180 'LEFT JOIN', 'tid = t2.id'
183 "SELECT tid,field,field2 AS alias,t2.id " .
184 "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
185 "WHERE alias = 'text' " .
186 "GROUP BY field,field2 HAVING (COUNT(*) > 1) AND field = '1' " .
191 'tables' => [ 'table' ],
192 'fields' => [ 'alias' => 'field' ],
193 'conds' => [ 'alias' => [ 1, 2, 3, 4 ] ],
195 "SELECT field AS alias " .
197 "WHERE alias IN ('1','2','3','4')"
202 'fields' => [ 'field' ],
203 'options' => [ 'USE INDEX' => [ 'table' => 'X' ] ],
206 "SELECT field FROM table"
211 'fields' => [ 'field' ],
212 'options' => [ 'IGNORE INDEX' => [ 'table' => 'X' ] ],
215 "SELECT field FROM table"
220 'fields' => [ 'field' ],
221 'options' => [ 'DISTINCT', 'LOCK IN SHARE MODE' ],
223 "SELECT DISTINCT field FROM table LOCK IN SHARE MODE"
228 'fields' => [ 'field' ],
229 'options' => [ 'EXPLAIN' => true ],
231 'EXPLAIN SELECT field FROM table'
236 'fields' => [ 'field' ],
237 'options' => [ 'FOR UPDATE' ],
239 "SELECT field FROM table FOR UPDATE"
245 * @covers Wikimedia\Rdbms\Subquery
246 * @dataProvider provideSelectRowCount
250 public function testSelectRowCount( $sql, $sqlText ) {
251 $this->database
->selectRowCount(
254 isset( $sql['conds'] ) ?
$sql['conds'] : [],
256 isset( $sql['options'] ) ?
$sql['options'] : [],
257 isset( $sql['join_conds'] ) ?
$sql['join_conds'] : []
259 $this->assertLastSql( $sqlText );
262 public static function provideSelectRowCount() {
268 'conds' => [ 'field' => 'text' ],
270 "SELECT COUNT(*) AS rowcount FROM " .
271 "(SELECT 1 FROM table WHERE field = 'text' ) tmp_count"
276 'field' => [ 'column' ],
277 'conds' => [ 'field' => 'text' ],
279 "SELECT COUNT(*) AS rowcount FROM " .
280 "(SELECT 1 FROM table WHERE field = 'text' AND (column IS NOT NULL) ) tmp_count"
285 'field' => [ 'alias' => 'column' ],
286 'conds' => [ 'field' => 'text' ],
288 "SELECT COUNT(*) AS rowcount FROM " .
289 "(SELECT 1 FROM table WHERE field = 'text' AND (column IS NOT NULL) ) tmp_count"
294 'field' => [ 'alias' => 'column' ],
297 "SELECT COUNT(*) AS rowcount FROM " .
298 "(SELECT 1 FROM table WHERE (column IS NOT NULL) ) tmp_count"
303 'field' => [ 'alias' => 'column' ],
306 "SELECT COUNT(*) AS rowcount FROM " .
307 "(SELECT 1 FROM table WHERE (column IS NOT NULL) ) tmp_count"
312 'field' => [ 'alias' => 'column' ],
315 "SELECT COUNT(*) AS rowcount FROM " .
316 "(SELECT 1 FROM table WHERE (column IS NOT NULL) ) tmp_count"
321 'field' => [ 'alias' => 'column' ],
324 "SELECT COUNT(*) AS rowcount FROM " .
325 "(SELECT 1 FROM table WHERE (1) AND (column IS NOT NULL) ) tmp_count"
330 'field' => [ 'alias' => 'column' ],
333 "SELECT COUNT(*) AS rowcount FROM " .
334 "(SELECT 1 FROM table WHERE (0) AND (column IS NOT NULL) ) tmp_count"
340 * @dataProvider provideUpdate
341 * @covers Wikimedia\Rdbms\Database::update
342 * @covers Wikimedia\Rdbms\Database::makeUpdateOptions
343 * @covers Wikimedia\Rdbms\Database::makeUpdateOptionsArray
345 public function testUpdate( $sql, $sqlText ) {
346 $this->database
->update(
351 isset( $sql['options'] ) ?
$sql['options'] : []
353 $this->assertLastSql( $sqlText );
356 public static function provideUpdate() {
361 'values' => [ 'field' => 'text', 'field2' => 'text2' ],
362 'conds' => [ 'alias' => 'text' ],
365 "SET field = 'text'" .
366 ",field2 = 'text2' " .
367 "WHERE alias = 'text'"
372 'values' => [ 'field = other', 'field2' => 'text2' ],
373 'conds' => [ 'id' => '1' ],
376 "SET field = other" .
377 ",field2 = 'text2' " .
383 'values' => [ 'field = other', 'field2' => 'text2' ],
387 "SET field = other" .
394 * @dataProvider provideDelete
395 * @covers Wikimedia\Rdbms\Database::delete
397 public function testDelete( $sql, $sqlText ) {
398 $this->database
->delete(
403 $this->assertLastSql( $sqlText );
406 public static function provideDelete() {
411 'conds' => [ 'alias' => 'text' ],
413 "DELETE FROM table " .
414 "WHERE alias = 'text'"
427 * @dataProvider provideUpsert
428 * @covers Wikimedia\Rdbms\Database::upsert
430 public function testUpsert( $sql, $sqlText ) {
431 $this->database
->upsert(
434 $sql['uniqueIndexes'],
438 $this->assertLastSql( $sqlText );
441 public static function provideUpsert() {
445 'table' => 'upsert_table',
446 'rows' => [ 'field' => 'text', 'field2' => 'text2' ],
447 'uniqueIndexes' => [ 'field' ],
448 'set' => [ 'field' => 'set' ],
451 "UPDATE upsert_table " .
452 "SET field = 'set' " .
453 "WHERE ((field = 'text')); " .
454 "INSERT IGNORE INTO upsert_table " .
456 "VALUES ('text','text2'); " .
463 * @dataProvider provideDeleteJoin
464 * @covers Wikimedia\Rdbms\Database::deleteJoin
466 public function testDeleteJoin( $sql, $sqlText ) {
467 $this->database
->deleteJoin(
475 $this->assertLastSql( $sqlText );
478 public static function provideDeleteJoin() {
482 'delTable' => 'table',
483 'joinTable' => 'table_join',
485 'joinVar' => 'field_join',
486 'conds' => [ 'alias' => 'text' ],
488 "DELETE FROM table " .
490 "SELECT field_join FROM table_join WHERE alias = 'text'" .
495 'delTable' => 'table',
496 'joinTable' => 'table_join',
498 'joinVar' => 'field_join',
501 "DELETE FROM table " .
503 "SELECT field_join FROM table_join " .
510 * @dataProvider provideInsert
511 * @covers Wikimedia\Rdbms\Database::insert
512 * @covers Wikimedia\Rdbms\Database::makeInsertOptions
514 public function testInsert( $sql, $sqlText ) {
515 $this->database
->insert(
519 isset( $sql['options'] ) ?
$sql['options'] : []
521 $this->assertLastSql( $sqlText );
524 public static function provideInsert() {
529 'rows' => [ 'field' => 'text', 'field2' => 2 ],
531 "INSERT INTO table " .
533 "VALUES ('text','2')"
538 'rows' => [ 'field' => 'text', 'field2' => 2 ],
539 'options' => 'IGNORE',
541 "INSERT IGNORE INTO table " .
543 "VALUES ('text','2')"
549 [ 'field' => 'text', 'field2' => 2 ],
550 [ 'field' => 'multi', 'field2' => 3 ],
552 'options' => 'IGNORE',
554 "INSERT IGNORE INTO table " .
564 * @dataProvider provideInsertSelect
565 * @covers Wikimedia\Rdbms\Database::insertSelect
566 * @covers Wikimedia\Rdbms\Database::nativeInsertSelect
568 public function testInsertSelect( $sql, $sqlTextNative, $sqlSelect, $sqlInsert ) {
569 $this->database
->insertSelect(
575 isset( $sql['insertOptions'] ) ?
$sql['insertOptions'] : [],
576 isset( $sql['selectOptions'] ) ?
$sql['selectOptions'] : [],
577 isset( $sql['selectJoinConds'] ) ?
$sql['selectJoinConds'] : []
579 $this->assertLastSql( $sqlTextNative );
581 $dbWeb = new DatabaseTestHelper( __CLASS__
, [ 'cliMode' => false ] );
582 $dbWeb->forceNextResult( [
583 array_flip( array_keys( $sql['varMap'] ) )
585 $dbWeb->insertSelect(
591 isset( $sql['insertOptions'] ) ?
$sql['insertOptions'] : [],
592 isset( $sql['selectOptions'] ) ?
$sql['selectOptions'] : [],
593 isset( $sql['selectJoinConds'] ) ?
$sql['selectJoinConds'] : []
595 $this->assertLastSqlDb( implode( '; ', [ $sqlSelect, 'BEGIN', $sqlInsert, 'COMMIT' ] ), $dbWeb );
598 public static function provideInsertSelect() {
602 'destTable' => 'insert_table',
603 'srcTable' => 'select_table',
604 'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
607 "INSERT INTO insert_table " .
608 "(field_insert,field) " .
609 "SELECT field_select,field2 " .
610 "FROM select_table WHERE *",
611 "SELECT field_select AS field_insert,field2 AS field " .
612 "FROM select_table WHERE * FOR UPDATE",
613 "INSERT INTO insert_table (field_insert,field) VALUES ('0','1')"
617 'destTable' => 'insert_table',
618 'srcTable' => 'select_table',
619 'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
620 'conds' => [ 'field' => 2 ],
622 "INSERT INTO insert_table " .
623 "(field_insert,field) " .
624 "SELECT field_select,field2 " .
625 "FROM select_table " .
627 "SELECT field_select AS field_insert,field2 AS field FROM " .
628 "select_table WHERE field = '2' FOR UPDATE",
629 "INSERT INTO insert_table (field_insert,field) VALUES ('0','1')"
633 'destTable' => 'insert_table',
634 'srcTable' => 'select_table',
635 'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
636 'conds' => [ 'field' => 2 ],
637 'insertOptions' => 'IGNORE',
638 'selectOptions' => [ 'ORDER BY' => 'field' ],
640 "INSERT IGNORE INTO insert_table " .
641 "(field_insert,field) " .
642 "SELECT field_select,field2 " .
643 "FROM select_table " .
644 "WHERE field = '2' " .
646 "SELECT field_select AS field_insert,field2 AS field " .
647 "FROM select_table WHERE field = '2' ORDER BY field FOR UPDATE",
648 "INSERT IGNORE INTO insert_table (field_insert,field) VALUES ('0','1')"
652 'destTable' => 'insert_table',
653 'srcTable' => [ 'select_table1', 'select_table2' ],
654 'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
655 'conds' => [ 'field' => 2 ],
656 'insertOptions' => [ 'NO_AUTO_COLUMNS' ],
657 'selectOptions' => [ 'ORDER BY' => 'field', 'FORCE INDEX' => [ 'select_table1' => 'index1' ] ],
658 'selectJoinConds' => [
659 'select_table2' => [ 'LEFT JOIN', [ 'select_table1.foo = select_table2.bar' ] ],
662 "INSERT INTO insert_table " .
663 "(field_insert,field) " .
664 "SELECT field_select,field2 " .
665 "FROM select_table1 LEFT JOIN select_table2 ON ((select_table1.foo = select_table2.bar)) " .
666 "WHERE field = '2' " .
668 "SELECT field_select AS field_insert,field2 AS field " .
669 "FROM select_table1 LEFT JOIN select_table2 ON ((select_table1.foo = select_table2.bar)) " .
670 "WHERE field = '2' ORDER BY field FOR UPDATE",
671 "INSERT INTO insert_table (field_insert,field) VALUES ('0','1')"
676 public function testInsertSelectBatching() {
677 $dbWeb = new DatabaseTestHelper( __CLASS__
, [ 'cliMode' => false ] );
679 for ( $i = 0; $i <= 25000; $i++
) {
680 $rows[] = [ 'field' => $i ];
682 $dbWeb->forceNextResult( $rows );
683 $dbWeb->insertSelect(
686 [ 'field' => 'field2' ],
690 $this->assertLastSqlDb( implode( '; ', [
691 'SELECT field2 AS field FROM select_table WHERE * FOR UPDATE',
693 "INSERT INTO insert_table (field) VALUES ('" . implode( "'),('", range( 0, 9999 ) ) . "')",
694 "INSERT INTO insert_table (field) VALUES ('" . implode( "'),('", range( 10000, 19999 ) ) . "')",
695 "INSERT INTO insert_table (field) VALUES ('" . implode( "'),('", range( 20000, 25000 ) ) . "')",
701 * @dataProvider provideReplace
702 * @covers Wikimedia\Rdbms\Database::replace
704 public function testReplace( $sql, $sqlText ) {
705 $this->database
->replace(
707 $sql['uniqueIndexes'],
711 $this->assertLastSql( $sqlText );
714 public static function provideReplace() {
718 'table' => 'replace_table',
719 'uniqueIndexes' => [ 'field' ],
720 'rows' => [ 'field' => 'text', 'field2' => 'text2' ],
722 "BEGIN; DELETE FROM replace_table " .
723 "WHERE (field = 'text'); " .
724 "INSERT INTO replace_table " .
726 "VALUES ('text','text2'); COMMIT"
730 'table' => 'module_deps',
731 'uniqueIndexes' => [ [ 'md_module', 'md_skin' ] ],
733 'md_module' => 'module',
738 "BEGIN; DELETE FROM module_deps " .
739 "WHERE (md_module = 'module' AND md_skin = 'skin'); " .
740 "INSERT INTO module_deps " .
741 "(md_module,md_skin,md_deps) " .
742 "VALUES ('module','skin','deps'); COMMIT"
746 'table' => 'module_deps',
747 'uniqueIndexes' => [ [ 'md_module', 'md_skin' ] ],
750 'md_module' => 'module',
754 'md_module' => 'module2',
755 'md_skin' => 'skin2',
756 'md_deps' => 'deps2',
760 "BEGIN; DELETE FROM module_deps " .
761 "WHERE (md_module = 'module' AND md_skin = 'skin'); " .
762 "INSERT INTO module_deps " .
763 "(md_module,md_skin,md_deps) " .
764 "VALUES ('module','skin','deps'); " .
765 "DELETE FROM module_deps " .
766 "WHERE (md_module = 'module2' AND md_skin = 'skin2'); " .
767 "INSERT INTO module_deps " .
768 "(md_module,md_skin,md_deps) " .
769 "VALUES ('module2','skin2','deps2'); COMMIT"
773 'table' => 'module_deps',
774 'uniqueIndexes' => [ 'md_module', 'md_skin' ],
777 'md_module' => 'module',
781 'md_module' => 'module2',
782 'md_skin' => 'skin2',
783 'md_deps' => 'deps2',
787 "BEGIN; DELETE FROM module_deps " .
788 "WHERE (md_module = 'module') OR (md_skin = 'skin'); " .
789 "INSERT INTO module_deps " .
790 "(md_module,md_skin,md_deps) " .
791 "VALUES ('module','skin','deps'); " .
792 "DELETE FROM module_deps " .
793 "WHERE (md_module = 'module2') OR (md_skin = 'skin2'); " .
794 "INSERT INTO module_deps " .
795 "(md_module,md_skin,md_deps) " .
796 "VALUES ('module2','skin2','deps2'); COMMIT"
800 'table' => 'module_deps',
801 'uniqueIndexes' => [],
803 'md_module' => 'module',
808 "BEGIN; INSERT INTO module_deps " .
809 "(md_module,md_skin,md_deps) " .
810 "VALUES ('module','skin','deps'); COMMIT"
816 * @dataProvider provideNativeReplace
817 * @covers Wikimedia\Rdbms\Database::nativeReplace
819 public function testNativeReplace( $sql, $sqlText ) {
820 $this->database
->nativeReplace(
825 $this->assertLastSql( $sqlText );
828 public static function provideNativeReplace() {
832 'table' => 'replace_table',
833 'rows' => [ 'field' => 'text', 'field2' => 'text2' ],
835 "REPLACE INTO replace_table " .
837 "VALUES ('text','text2')"
843 * @dataProvider provideConditional
844 * @covers Wikimedia\Rdbms\Database::conditional
846 public function testConditional( $sql, $sqlText ) {
847 $this->assertEquals( trim( $this->database
->conditional(
854 public static function provideConditional() {
858 'conds' => [ 'field' => 'text' ],
862 "(CASE WHEN field = 'text' THEN 1 ELSE NULL END)"
866 'conds' => [ 'field' => 'text', 'field2' => 'anothertext' ],
870 "(CASE WHEN field = 'text' AND field2 = 'anothertext' THEN 1 ELSE NULL END)"
874 'conds' => 'field=1',
878 "(CASE WHEN field=1 THEN 1 ELSE NULL END)"
884 * @dataProvider provideBuildConcat
885 * @covers Wikimedia\Rdbms\Database::buildConcat
887 public function testBuildConcat( $stringList, $sqlText ) {
888 $this->assertEquals( trim( $this->database
->buildConcat(
893 public static function provideBuildConcat() {
896 [ 'field', 'field2' ],
897 "CONCAT(field,field2)"
900 [ "'test'", 'field2' ],
901 "CONCAT('test',field2)"
907 * @dataProvider provideBuildLike
908 * @covers Wikimedia\Rdbms\Database::buildLike
909 * @covers Wikimedia\Rdbms\Database::escapeLikeInternal
911 public function testBuildLike( $array, $sqlText ) {
912 $this->assertEquals( trim( $this->database
->buildLike(
917 public static function provideBuildLike() {
921 "LIKE 'text' ESCAPE '`'"
924 [ 'text', new LikeMatch( '%' ) ],
925 "LIKE 'text%' ESCAPE '`'"
928 [ 'text', new LikeMatch( '%' ), 'text2' ],
929 "LIKE 'text%text2' ESCAPE '`'"
932 [ 'text', new LikeMatch( '_' ) ],
933 "LIKE 'text_' ESCAPE '`'"
937 "LIKE 'more`_text' ESCAPE '`'"
940 [ 'C:\\Windows\\', new LikeMatch( '%' ) ],
941 "LIKE 'C:\\Windows\\%' ESCAPE '`'"
944 [ 'accent`_test`', new LikeMatch( '%' ) ],
945 "LIKE 'accent```_test``%' ESCAPE '`'"
951 * @dataProvider provideUnionQueries
952 * @covers Wikimedia\Rdbms\Database::unionQueries
954 public function testUnionQueries( $sql, $sqlText ) {
955 $this->assertEquals( trim( $this->database
->unionQueries(
961 public static function provideUnionQueries() {
965 'sqls' => [ 'RAW SQL', 'RAW2SQL' ],
968 "(RAW SQL) UNION ALL (RAW2SQL)"
972 'sqls' => [ 'RAW SQL', 'RAW2SQL' ],
975 "(RAW SQL) UNION (RAW2SQL)"
979 'sqls' => [ 'RAW SQL', 'RAW2SQL', 'RAW3SQL' ],
982 "(RAW SQL) UNION (RAW2SQL) UNION (RAW3SQL)"
988 * @dataProvider provideUnionConditionPermutations
989 * @covers Wikimedia\Rdbms\Database::unionConditionPermutations
991 public function testUnionConditionPermutations( $params, $expect ) {
992 if ( isset( $params['unionSupportsOrderAndLimit'] ) ) {
993 $this->database
->setUnionSupportsOrderAndLimit( $params['unionSupportsOrderAndLimit'] );
996 $sql = trim( $this->database
->unionConditionPermutations(
999 $params['permute_conds'],
1000 isset( $params['extra_conds'] ) ?
$params['extra_conds'] : '',
1002 isset( $params['options'] ) ?
$params['options'] : [],
1003 isset( $params['join_conds'] ) ?
$params['join_conds'] : []
1005 $this->assertEquals( $expect, $sql );
1008 public static function provideUnionConditionPermutations() {
1009 // phpcs:disable Generic.Files.LineLength
1013 'table' => [ 'table1', 'table2' ],
1014 'vars' => [ 'field1', 'alias' => 'field2' ],
1015 'permute_conds' => [
1016 'field3' => [ 1, 2, 3 ],
1017 'duplicates' => [ 4, 5, 4 ],
1021 'extra_conds' => 'table2.bar > 23',
1023 'ORDER BY' => [ 'field1', 'alias' ],
1024 'INNER ORDER BY' => [ 'field1', 'field2' ],
1028 'table2' => [ 'JOIN', 'table1.foo_id = table2.foo_id' ],
1031 "(SELECT field1,field2 AS alias FROM table1 JOIN table2 ON ((table1.foo_id = table2.foo_id)) WHERE field3 = '1' AND duplicates = '4' AND single = '0' AND (table2.bar > 23) ORDER BY field1,field2 LIMIT 100 ) UNION ALL " .
1032 "(SELECT field1,field2 AS alias FROM table1 JOIN table2 ON ((table1.foo_id = table2.foo_id)) WHERE field3 = '1' AND duplicates = '5' AND single = '0' AND (table2.bar > 23) ORDER BY field1,field2 LIMIT 100 ) UNION ALL " .
1033 "(SELECT field1,field2 AS alias FROM table1 JOIN table2 ON ((table1.foo_id = table2.foo_id)) WHERE field3 = '2' AND duplicates = '4' AND single = '0' AND (table2.bar > 23) ORDER BY field1,field2 LIMIT 100 ) UNION ALL " .
1034 "(SELECT field1,field2 AS alias FROM table1 JOIN table2 ON ((table1.foo_id = table2.foo_id)) WHERE field3 = '2' AND duplicates = '5' AND single = '0' AND (table2.bar > 23) ORDER BY field1,field2 LIMIT 100 ) UNION ALL " .
1035 "(SELECT field1,field2 AS alias FROM table1 JOIN table2 ON ((table1.foo_id = table2.foo_id)) WHERE field3 = '3' AND duplicates = '4' AND single = '0' AND (table2.bar > 23) ORDER BY field1,field2 LIMIT 100 ) UNION ALL " .
1036 "(SELECT field1,field2 AS alias FROM table1 JOIN table2 ON ((table1.foo_id = table2.foo_id)) WHERE field3 = '3' AND duplicates = '5' AND single = '0' AND (table2.bar > 23) ORDER BY field1,field2 LIMIT 100 ) " .
1037 "ORDER BY field1,alias LIMIT 100"
1042 'vars' => [ 'foo_id' ],
1043 'permute_conds' => [
1044 'bar' => [ 1, 2, 3 ],
1046 'extra_conds' => [ 'baz' => null ],
1049 'ORDER BY' => [ 'foo_id' ],
1053 "(SELECT foo_id FROM foo WHERE bar = '1' AND baz IS NULL ORDER BY foo_id LIMIT 25 ) UNION " .
1054 "(SELECT foo_id FROM foo WHERE bar = '2' AND baz IS NULL ORDER BY foo_id LIMIT 25 ) UNION " .
1055 "(SELECT foo_id FROM foo WHERE bar = '3' AND baz IS NULL ORDER BY foo_id LIMIT 25 ) " .
1056 "ORDER BY foo_id LIMIT 25"
1061 'vars' => [ 'foo_id' ],
1062 'permute_conds' => [
1063 'bar' => [ 1, 2, 3 ],
1065 'extra_conds' => [ 'baz' => null ],
1068 'ORDER BY' => [ 'foo_id' ],
1071 'unionSupportsOrderAndLimit' => false,
1073 "(SELECT foo_id FROM foo WHERE bar = '1' AND baz IS NULL ) UNION " .
1074 "(SELECT foo_id FROM foo WHERE bar = '2' AND baz IS NULL ) UNION " .
1075 "(SELECT foo_id FROM foo WHERE bar = '3' AND baz IS NULL ) " .
1076 "ORDER BY foo_id LIMIT 25"
1081 'vars' => [ 'foo_id' ],
1082 'permute_conds' => [],
1083 'extra_conds' => [ 'baz' => null ],
1085 'ORDER BY' => [ 'foo_id' ],
1089 "SELECT foo_id FROM foo WHERE baz IS NULL ORDER BY foo_id LIMIT 25"
1094 'vars' => [ 'foo_id' ],
1095 'permute_conds' => [
1098 'extra_conds' => [ 'baz' => null ],
1100 'ORDER BY' => [ 'foo_id' ],
1104 "SELECT foo_id FROM foo WHERE baz IS NULL ORDER BY foo_id LIMIT 25"
1109 'vars' => [ 'foo_id' ],
1110 'permute_conds' => [
1114 'ORDER BY' => [ 'foo_id' ],
1119 "SELECT foo_id FROM foo WHERE bar = '1' ORDER BY foo_id LIMIT 150,25"
1124 'vars' => [ 'foo_id' ],
1125 'permute_conds' => [],
1126 'extra_conds' => [ 'baz' => null ],
1128 'ORDER BY' => [ 'foo_id' ],
1131 'INNER ORDER BY' => [ 'bar_id' ],
1134 "(SELECT foo_id FROM foo WHERE baz IS NULL ORDER BY bar_id LIMIT 175 ) ORDER BY foo_id LIMIT 150,25"
1139 'vars' => [ 'foo_id' ],
1140 'permute_conds' => [],
1141 'extra_conds' => [ 'baz' => null ],
1143 'ORDER BY' => [ 'foo_id' ],
1146 'INNER ORDER BY' => [ 'bar_id' ],
1148 'unionSupportsOrderAndLimit' => false,
1150 "SELECT foo_id FROM foo WHERE baz IS NULL ORDER BY foo_id LIMIT 150,25"
1157 * @covers Wikimedia\Rdbms\Database::commit
1158 * @covers Wikimedia\Rdbms\Database::doCommit
1160 public function testTransactionCommit() {
1161 $this->database
->begin( __METHOD__
);
1162 $this->database
->commit( __METHOD__
);
1163 $this->assertLastSql( 'BEGIN; COMMIT' );
1167 * @covers Wikimedia\Rdbms\Database::rollback
1168 * @covers Wikimedia\Rdbms\Database::doRollback
1170 public function testTransactionRollback() {
1171 $this->database
->begin( __METHOD__
);
1172 $this->database
->rollback( __METHOD__
);
1173 $this->assertLastSql( 'BEGIN; ROLLBACK' );
1177 * @covers Wikimedia\Rdbms\Database::dropTable
1179 public function testDropTable() {
1180 $this->database
->setExistingTables( [ 'table' ] );
1181 $this->database
->dropTable( 'table', __METHOD__
);
1182 $this->assertLastSql( 'DROP TABLE table CASCADE' );
1186 * @covers Wikimedia\Rdbms\Database::dropTable
1188 public function testDropNonExistingTable() {
1190 $this->database
->dropTable( 'non_existing', __METHOD__
)
1195 * @dataProvider provideMakeList
1196 * @covers Wikimedia\Rdbms\Database::makeList
1198 public function testMakeList( $list, $mode, $sqlText ) {
1199 $this->assertEquals( trim( $this->database
->makeList(
1204 public static function provideMakeList() {
1207 [ 'value', 'value2' ],
1212 [ 'field', 'field2' ],
1217 [ 'field' => 'value', 'field2' => 'value2' ],
1219 "field = 'value' AND field2 = 'value2'"
1222 [ 'field' => null, "field2 != 'value2'" ],
1224 "field IS NULL AND (field2 != 'value2')"
1227 [ 'field' => [ 'value', null, 'value2' ], 'field2' => 'value2' ],
1229 "(field IN ('value','value2') OR field IS NULL) AND field2 = 'value2'"
1232 [ 'field' => [ null ], 'field2' => null ],
1234 "field IS NULL AND field2 IS NULL"
1237 [ 'field' => 'value', 'field2' => 'value2' ],
1239 "field = 'value' OR field2 = 'value2'"
1242 [ 'field' => 'value', 'field2' => null ],
1244 "field = 'value' OR field2 IS NULL"
1247 [ 'field' => [ 'value', 'value2' ], 'field2' => [ 'value' ] ],
1249 "field IN ('value','value2') OR field2 = 'value'"
1252 [ 'field' => [ null, 'value', null, 'value2' ], "field2 != 'value2'" ],
1254 "(field IN ('value','value2') OR field IS NULL) OR (field2 != 'value2')"
1257 [ 'field' => 'value', 'field2' => 'value2' ],
1259 "field = 'value',field2 = 'value2'"
1262 [ 'field' => 'value', 'field2' => null ],
1264 "field = 'value',field2 = NULL"
1267 [ 'field' => 'value', "field2 != 'value2'" ],
1269 "field = 'value',field2 != 'value2'"
1275 * @covers Wikimedia\Rdbms\Database::registerTempTableOperation
1277 public function testSessionTempTables() {
1278 $temp1 = $this->database
->tableName( 'tmp_table_1' );
1279 $temp2 = $this->database
->tableName( 'tmp_table_2' );
1280 $temp3 = $this->database
->tableName( 'tmp_table_3' );
1282 $this->database
->query( "CREATE TEMPORARY TABLE $temp1 LIKE orig_tbl", __METHOD__
);
1283 $this->database
->query( "CREATE TEMPORARY TABLE $temp2 LIKE orig_tbl", __METHOD__
);
1284 $this->database
->query( "CREATE TEMPORARY TABLE $temp3 LIKE orig_tbl", __METHOD__
);
1286 $this->assertTrue( $this->database
->tableExists( "tmp_table_1", __METHOD__
) );
1287 $this->assertTrue( $this->database
->tableExists( "tmp_table_2", __METHOD__
) );
1288 $this->assertTrue( $this->database
->tableExists( "tmp_table_3", __METHOD__
) );
1290 $this->database
->dropTable( 'tmp_table_1', __METHOD__
);
1291 $this->database
->dropTable( 'tmp_table_2', __METHOD__
);
1292 $this->database
->dropTable( 'tmp_table_3', __METHOD__
);
1294 $this->assertFalse( $this->database
->tableExists( "tmp_table_1", __METHOD__
) );
1295 $this->assertFalse( $this->database
->tableExists( "tmp_table_2", __METHOD__
) );
1296 $this->assertFalse( $this->database
->tableExists( "tmp_table_3", __METHOD__
) );
1298 $this->database
->query( "CREATE TEMPORARY TABLE tmp_table_1 LIKE orig_tbl", __METHOD__
);
1299 $this->database
->query( "CREATE TEMPORARY TABLE 'tmp_table_2' LIKE orig_tbl", __METHOD__
);
1300 $this->database
->query( "CREATE TEMPORARY TABLE `tmp_table_3` LIKE orig_tbl", __METHOD__
);
1302 $this->assertTrue( $this->database
->tableExists( "tmp_table_1", __METHOD__
) );
1303 $this->assertTrue( $this->database
->tableExists( "tmp_table_2", __METHOD__
) );
1304 $this->assertTrue( $this->database
->tableExists( "tmp_table_3", __METHOD__
) );
1306 $this->database
->query( "DROP TEMPORARY TABLE tmp_table_1 LIKE orig_tbl", __METHOD__
);
1307 $this->database
->query( "DROP TEMPORARY TABLE 'tmp_table_2' LIKE orig_tbl", __METHOD__
);
1308 $this->database
->query( "DROP TABLE `tmp_table_3` LIKE orig_tbl", __METHOD__
);
1310 $this->assertFalse( $this->database
->tableExists( "tmp_table_1", __METHOD__
) );
1311 $this->assertFalse( $this->database
->tableExists( "tmp_table_2", __METHOD__
) );
1312 $this->assertFalse( $this->database
->tableExists( "tmp_table_3", __METHOD__
) );
1315 public function provideBuildSubstring() {
1316 yield
[ 'someField', 1, 2, 'SUBSTRING(someField FROM 1 FOR 2)' ];
1317 yield
[ 'someField', 1, null, 'SUBSTRING(someField FROM 1)' ];
1321 * @covers Wikimedia\Rdbms\Database::buildSubstring
1322 * @dataProvider provideBuildSubstring
1324 public function testBuildSubstring( $input, $start, $length, $expected ) {
1325 $output = $this->database
->buildSubstring( $input, $start, $length );
1326 $this->assertSame( $expected, $output );
1329 public function provideBuildSubstring_invalidParams() {
1339 * @covers Wikimedia\Rdbms\Database::buildSubstring
1340 * @covers Wikimedia\Rdbms\Database::assertBuildSubstringParams
1341 * @dataProvider provideBuildSubstring_invalidParams
1343 public function testBuildSubstring_invalidParams( $start, $length ) {
1344 $this->setExpectedException( InvalidArgumentException
::class );
1345 $this->database
->buildSubstring( 'foo', $start, $length );
1349 * @covers \Wikimedia\Rdbms\Database::buildIntegerCast
1351 public function testBuildIntegerCast() {
1352 $output = $this->database
->buildIntegerCast( 'fieldName' );
1353 $this->assertSame( 'CAST( fieldName AS INTEGER )', $output );
1357 * @covers \Wikimedia\Rdbms\Database::doSavepoint
1358 * @covers \Wikimedia\Rdbms\Database::doReleaseSavepoint
1359 * @covers \Wikimedia\Rdbms\Database::doRollbackToSavepoint
1360 * @covers \Wikimedia\Rdbms\Database::startAtomic
1361 * @covers \Wikimedia\Rdbms\Database::endAtomic
1362 * @covers \Wikimedia\Rdbms\Database::cancelAtomic
1363 * @covers \Wikimedia\Rdbms\Database::doAtomicSection
1365 public function testAtomicSections() {
1366 $this->database
->startAtomic( __METHOD__
);
1367 $this->database
->endAtomic( __METHOD__
);
1368 $this->assertLastSql( 'BEGIN; COMMIT' );
1370 $this->database
->startAtomic( __METHOD__
, IDatabase
::ATOMIC_CANCELABLE
);
1371 $this->database
->cancelAtomic( __METHOD__
);
1372 $this->assertLastSql( 'BEGIN; ROLLBACK' );
1374 $this->database
->begin( __METHOD__
);
1375 $this->database
->startAtomic( __METHOD__
);
1376 $this->database
->endAtomic( __METHOD__
);
1377 $this->database
->commit( __METHOD__
);
1378 $this->assertLastSql( 'BEGIN; COMMIT' );
1380 $this->database
->begin( __METHOD__
);
1381 $this->database
->startAtomic( __METHOD__
, IDatabase
::ATOMIC_CANCELABLE
);
1382 $this->database
->endAtomic( __METHOD__
);
1383 $this->database
->commit( __METHOD__
);
1384 // phpcs:ignore Generic.Files.LineLength
1385 $this->assertLastSql( 'BEGIN; SAVEPOINT wikimedia_rdbms_atomic1; RELEASE SAVEPOINT wikimedia_rdbms_atomic1; COMMIT' );
1387 $this->database
->begin( __METHOD__
);
1388 $this->database
->startAtomic( __METHOD__
, IDatabase
::ATOMIC_CANCELABLE
);
1389 $this->database
->cancelAtomic( __METHOD__
);
1390 $this->database
->commit( __METHOD__
);
1391 // phpcs:ignore Generic.Files.LineLength
1392 $this->assertLastSql( 'BEGIN; SAVEPOINT wikimedia_rdbms_atomic1; ROLLBACK TO SAVEPOINT wikimedia_rdbms_atomic1; COMMIT' );
1394 $this->database
->startAtomic( __METHOD__
, IDatabase
::ATOMIC_CANCELABLE
);
1395 $this->database
->startAtomic( __METHOD__
, IDatabase
::ATOMIC_CANCELABLE
);
1396 $this->database
->cancelAtomic( __METHOD__
);
1397 $this->database
->endAtomic( __METHOD__
);
1398 // phpcs:ignore Generic.Files.LineLength
1399 $this->assertLastSql( 'BEGIN; SAVEPOINT wikimedia_rdbms_atomic1; ROLLBACK TO SAVEPOINT wikimedia_rdbms_atomic1; COMMIT' );
1401 $this->database
->doAtomicSection( __METHOD__
, function () {
1403 $this->assertLastSql( 'BEGIN; COMMIT' );
1405 $this->database
->begin( __METHOD__
);
1406 $this->database
->doAtomicSection( __METHOD__
, function () {
1408 $this->database
->rollback( __METHOD__
);
1409 // phpcs:ignore Generic.Files.LineLength
1410 $this->assertLastSql( 'BEGIN; SAVEPOINT wikimedia_rdbms_atomic1; RELEASE SAVEPOINT wikimedia_rdbms_atomic1; ROLLBACK' );
1412 $this->database
->begin( __METHOD__
);
1414 $this->database
->doAtomicSection( __METHOD__
, function () {
1415 throw new RuntimeException( 'Test exception' );
1417 $this->fail( 'Expected exception not thrown' );
1418 } catch ( RuntimeException
$ex ) {
1419 $this->assertSame( 'Test exception', $ex->getMessage() );
1421 $this->database
->commit( __METHOD__
);
1422 // phpcs:ignore Generic.Files.LineLength
1423 $this->assertLastSql( 'BEGIN; SAVEPOINT wikimedia_rdbms_atomic1; ROLLBACK TO SAVEPOINT wikimedia_rdbms_atomic1; COMMIT' );
1426 public static function provideAtomicSectionMethodsForErrors() {
1434 * @dataProvider provideAtomicSectionMethodsForErrors
1435 * @covers \Wikimedia\Rdbms\Database::endAtomic
1436 * @covers \Wikimedia\Rdbms\Database::cancelAtomic
1438 public function testNoAtomicSection( $method ) {
1440 $this->database
->$method( __METHOD__
);
1441 $this->fail( 'Expected exception not thrown' );
1442 } catch ( DBUnexpectedError
$ex ) {
1444 'No atomic transaction is open (got ' . __METHOD__
. ').',
1451 * @dataProvider provideAtomicSectionMethodsForErrors
1452 * @covers \Wikimedia\Rdbms\Database::endAtomic
1453 * @covers \Wikimedia\Rdbms\Database::cancelAtomic
1455 public function testInvalidAtomicSectionEnded( $method ) {
1456 $this->database
->startAtomic( __METHOD__
. 'X' );
1458 $this->database
->$method( __METHOD__
);
1459 $this->fail( 'Expected exception not thrown' );
1460 } catch ( DBUnexpectedError
$ex ) {
1462 'Invalid atomic section ended (got ' . __METHOD__
. ').',
1469 * @covers \Wikimedia\Rdbms\Database::cancelAtomic
1471 public function testUncancellableAtomicSection() {
1472 $this->database
->startAtomic( __METHOD__
);
1474 $this->database
->cancelAtomic( __METHOD__
);
1475 $this->fail( 'Expected exception not thrown' );
1476 } catch ( DBUnexpectedError
$ex ) {
1478 'Uncancelable atomic section canceled (got ' . __METHOD__
. ').',