3 use Wikimedia\Rdbms\LikeMatch
;
6 * Test the parts of the Database abstract class that deal
7 * with creating SQL text.
9 class DatabaseSQLTest
extends PHPUnit\Framework\TestCase
{
11 use MediaWikiCoversValidator
;
13 /** @var DatabaseTestHelper */
16 protected function setUp() {
18 $this->database
= new DatabaseTestHelper( __CLASS__
, [ 'cliMode' => true ] );
21 protected function assertLastSql( $sqlText ) {
24 $this->database
->getLastSqls()
28 protected function assertLastSqlDb( $sqlText, DatabaseTestHelper
$db ) {
29 $this->assertEquals( $sqlText, $db->getLastSqls() );
33 * @dataProvider provideSelect
34 * @covers Wikimedia\Rdbms\Database::select
35 * @covers Wikimedia\Rdbms\Database::selectSQLText
36 * @covers Wikimedia\Rdbms\Database::tableNamesWithIndexClauseOrJOIN
37 * @covers Wikimedia\Rdbms\Database::useIndexClause
38 * @covers Wikimedia\Rdbms\Database::ignoreIndexClause
39 * @covers Wikimedia\Rdbms\Database::makeSelectOptions
40 * @covers Wikimedia\Rdbms\Database::makeOrderBy
41 * @covers Wikimedia\Rdbms\Database::makeGroupByWithHaving
43 public function testSelect( $sql, $sqlText ) {
44 $this->database
->select(
47 isset( $sql['conds'] ) ?
$sql['conds'] : [],
49 isset( $sql['options'] ) ?
$sql['options'] : [],
50 isset( $sql['join_conds'] ) ?
$sql['join_conds'] : []
52 $this->assertLastSql( $sqlText );
55 public static function provideSelect() {
60 'fields' => [ 'field', 'alias' => 'field2' ],
61 'conds' => [ 'alias' => 'text' ],
63 "SELECT field,field2 AS alias " .
65 "WHERE alias = 'text'"
69 // 'tables' with space prepended indicates pre-escaped table name
70 'tables' => ' table LEFT JOIN table2',
71 'fields' => [ 'field' ],
72 'conds' => [ 'field' => 'text' ],
74 "SELECT field FROM table LEFT JOIN table2 WHERE field = 'text'"
78 // Empty 'tables' is allowed
80 'fields' => [ 'SPECIAL_QUERY()' ],
82 "SELECT SPECIAL_QUERY()"
87 'fields' => [ 'field', 'alias' => 'field2' ],
88 'conds' => [ 'alias' => 'text' ],
89 'options' => [ 'LIMIT' => 1, 'ORDER BY' => 'field' ],
91 "SELECT field,field2 AS alias " .
93 "WHERE alias = 'text' " .
99 'tables' => [ 'table', 't2' => 'table2' ],
100 'fields' => [ 'tid', 'field', 'alias' => 'field2', 't2.id' ],
101 'conds' => [ 'alias' => 'text' ],
102 'options' => [ 'LIMIT' => 1, 'ORDER BY' => 'field' ],
103 'join_conds' => [ 't2' => [
104 'LEFT JOIN', 'tid = t2.id'
107 "SELECT tid,field,field2 AS alias,t2.id " .
108 "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
109 "WHERE alias = 'text' " .
115 'tables' => [ 'table', 't2' => 'table2' ],
116 'fields' => [ 'tid', 'field', 'alias' => 'field2', 't2.id' ],
117 'conds' => [ 'alias' => 'text' ],
118 'options' => [ 'LIMIT' => 1, 'GROUP BY' => 'field', 'HAVING' => 'COUNT(*) > 1' ],
119 'join_conds' => [ 't2' => [
120 'LEFT JOIN', 'tid = t2.id'
123 "SELECT tid,field,field2 AS alias,t2.id " .
124 "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
125 "WHERE alias = 'text' " .
126 "GROUP BY field HAVING COUNT(*) > 1 " .
131 'tables' => [ 'table', 't2' => 'table2' ],
132 'fields' => [ 'tid', 'field', 'alias' => 'field2', 't2.id' ],
133 'conds' => [ 'alias' => 'text' ],
136 'GROUP BY' => [ 'field', 'field2' ],
137 'HAVING' => [ 'COUNT(*) > 1', 'field' => 1 ]
139 'join_conds' => [ 't2' => [
140 'LEFT JOIN', 'tid = t2.id'
143 "SELECT tid,field,field2 AS alias,t2.id " .
144 "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
145 "WHERE alias = 'text' " .
146 "GROUP BY field,field2 HAVING (COUNT(*) > 1) AND field = '1' " .
151 'tables' => [ 'table' ],
152 'fields' => [ 'alias' => 'field' ],
153 'conds' => [ 'alias' => [ 1, 2, 3, 4 ] ],
155 "SELECT field AS alias " .
157 "WHERE alias IN ('1','2','3','4')"
162 'fields' => [ 'field' ],
163 'options' => [ 'USE INDEX' => [ 'table' => 'X' ] ],
166 "SELECT field FROM table"
171 'fields' => [ 'field' ],
172 'options' => [ 'IGNORE INDEX' => [ 'table' => 'X' ] ],
175 "SELECT field FROM table"
180 'fields' => [ 'field' ],
181 'options' => [ 'DISTINCT', 'LOCK IN SHARE MODE' ],
183 "SELECT DISTINCT field FROM table LOCK IN SHARE MODE"
188 'fields' => [ 'field' ],
189 'options' => [ 'EXPLAIN' => true ],
191 'EXPLAIN SELECT field FROM table'
196 'fields' => [ 'field' ],
197 'options' => [ 'FOR UPDATE' ],
199 "SELECT field FROM table FOR UPDATE"
205 * @dataProvider provideUpdate
206 * @covers Wikimedia\Rdbms\Database::update
207 * @covers Wikimedia\Rdbms\Database::makeUpdateOptions
208 * @covers Wikimedia\Rdbms\Database::makeUpdateOptionsArray
210 public function testUpdate( $sql, $sqlText ) {
211 $this->database
->update(
216 isset( $sql['options'] ) ?
$sql['options'] : []
218 $this->assertLastSql( $sqlText );
221 public static function provideUpdate() {
226 'values' => [ 'field' => 'text', 'field2' => 'text2' ],
227 'conds' => [ 'alias' => 'text' ],
230 "SET field = 'text'" .
231 ",field2 = 'text2' " .
232 "WHERE alias = 'text'"
237 'values' => [ 'field = other', 'field2' => 'text2' ],
238 'conds' => [ 'id' => '1' ],
241 "SET field = other" .
242 ",field2 = 'text2' " .
248 'values' => [ 'field = other', 'field2' => 'text2' ],
252 "SET field = other" .
259 * @dataProvider provideDelete
260 * @covers Wikimedia\Rdbms\Database::delete
262 public function testDelete( $sql, $sqlText ) {
263 $this->database
->delete(
268 $this->assertLastSql( $sqlText );
271 public static function provideDelete() {
276 'conds' => [ 'alias' => 'text' ],
278 "DELETE FROM table " .
279 "WHERE alias = 'text'"
292 * @dataProvider provideUpsert
293 * @covers Wikimedia\Rdbms\Database::upsert
295 public function testUpsert( $sql, $sqlText ) {
296 $this->database
->upsert(
299 $sql['uniqueIndexes'],
303 $this->assertLastSql( $sqlText );
306 public static function provideUpsert() {
310 'table' => 'upsert_table',
311 'rows' => [ 'field' => 'text', 'field2' => 'text2' ],
312 'uniqueIndexes' => [ 'field' ],
313 'set' => [ 'field' => 'set' ],
316 "UPDATE upsert_table " .
317 "SET field = 'set' " .
318 "WHERE ((field = 'text')); " .
319 "INSERT IGNORE INTO upsert_table " .
321 "VALUES ('text','text2'); " .
328 * @dataProvider provideDeleteJoin
329 * @covers Wikimedia\Rdbms\Database::deleteJoin
331 public function testDeleteJoin( $sql, $sqlText ) {
332 $this->database
->deleteJoin(
340 $this->assertLastSql( $sqlText );
343 public static function provideDeleteJoin() {
347 'delTable' => 'table',
348 'joinTable' => 'table_join',
350 'joinVar' => 'field_join',
351 'conds' => [ 'alias' => 'text' ],
353 "DELETE FROM table " .
355 "SELECT field_join FROM table_join WHERE alias = 'text'" .
360 'delTable' => 'table',
361 'joinTable' => 'table_join',
363 'joinVar' => 'field_join',
366 "DELETE FROM table " .
368 "SELECT field_join FROM table_join " .
375 * @dataProvider provideInsert
376 * @covers Wikimedia\Rdbms\Database::insert
377 * @covers Wikimedia\Rdbms\Database::makeInsertOptions
379 public function testInsert( $sql, $sqlText ) {
380 $this->database
->insert(
384 isset( $sql['options'] ) ?
$sql['options'] : []
386 $this->assertLastSql( $sqlText );
389 public static function provideInsert() {
394 'rows' => [ 'field' => 'text', 'field2' => 2 ],
396 "INSERT INTO table " .
398 "VALUES ('text','2')"
403 'rows' => [ 'field' => 'text', 'field2' => 2 ],
404 'options' => 'IGNORE',
406 "INSERT IGNORE INTO table " .
408 "VALUES ('text','2')"
414 [ 'field' => 'text', 'field2' => 2 ],
415 [ 'field' => 'multi', 'field2' => 3 ],
417 'options' => 'IGNORE',
419 "INSERT IGNORE INTO table " .
429 * @dataProvider provideInsertSelect
430 * @covers Wikimedia\Rdbms\Database::insertSelect
431 * @covers Wikimedia\Rdbms\Database::nativeInsertSelect
433 public function testInsertSelect( $sql, $sqlTextNative, $sqlSelect, $sqlInsert ) {
434 $this->database
->insertSelect(
440 isset( $sql['insertOptions'] ) ?
$sql['insertOptions'] : [],
441 isset( $sql['selectOptions'] ) ?
$sql['selectOptions'] : [],
442 isset( $sql['selectJoinConds'] ) ?
$sql['selectJoinConds'] : []
444 $this->assertLastSql( $sqlTextNative );
446 $dbWeb = new DatabaseTestHelper( __CLASS__
, [ 'cliMode' => false ] );
447 $dbWeb->forceNextResult( [
448 array_flip( array_keys( $sql['varMap'] ) )
450 $dbWeb->insertSelect(
456 isset( $sql['insertOptions'] ) ?
$sql['insertOptions'] : [],
457 isset( $sql['selectOptions'] ) ?
$sql['selectOptions'] : [],
458 isset( $sql['selectJoinConds'] ) ?
$sql['selectJoinConds'] : []
460 $this->assertLastSqlDb( implode( '; ', [ $sqlSelect, 'BEGIN', $sqlInsert, 'COMMIT' ] ), $dbWeb );
463 public static function provideInsertSelect() {
467 'destTable' => 'insert_table',
468 'srcTable' => 'select_table',
469 'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
472 "INSERT INTO insert_table " .
473 "(field_insert,field) " .
474 "SELECT field_select,field2 " .
475 "FROM select_table WHERE *",
476 "SELECT field_select AS field_insert,field2 AS field " .
477 "FROM select_table WHERE * FOR UPDATE",
478 "INSERT INTO insert_table (field_insert,field) VALUES ('0','1')"
482 'destTable' => 'insert_table',
483 'srcTable' => 'select_table',
484 'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
485 'conds' => [ 'field' => 2 ],
487 "INSERT INTO insert_table " .
488 "(field_insert,field) " .
489 "SELECT field_select,field2 " .
490 "FROM select_table " .
492 "SELECT field_select AS field_insert,field2 AS field FROM " .
493 "select_table WHERE field = '2' FOR UPDATE",
494 "INSERT INTO insert_table (field_insert,field) VALUES ('0','1')"
498 'destTable' => 'insert_table',
499 'srcTable' => 'select_table',
500 'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
501 'conds' => [ 'field' => 2 ],
502 'insertOptions' => 'IGNORE',
503 'selectOptions' => [ 'ORDER BY' => 'field' ],
505 "INSERT IGNORE INTO insert_table " .
506 "(field_insert,field) " .
507 "SELECT field_select,field2 " .
508 "FROM select_table " .
509 "WHERE field = '2' " .
511 "SELECT field_select AS field_insert,field2 AS field " .
512 "FROM select_table WHERE field = '2' ORDER BY field FOR UPDATE",
513 "INSERT IGNORE INTO insert_table (field_insert,field) VALUES ('0','1')"
517 'destTable' => 'insert_table',
518 'srcTable' => [ 'select_table1', 'select_table2' ],
519 'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
520 'conds' => [ 'field' => 2 ],
521 'selectOptions' => [ 'ORDER BY' => 'field', 'FORCE INDEX' => [ 'select_table1' => 'index1' ] ],
522 'selectJoinConds' => [
523 'select_table2' => [ 'LEFT JOIN', [ 'select_table1.foo = select_table2.bar' ] ],
526 "INSERT INTO insert_table " .
527 "(field_insert,field) " .
528 "SELECT field_select,field2 " .
529 "FROM select_table1 LEFT JOIN select_table2 ON ((select_table1.foo = select_table2.bar)) " .
530 "WHERE field = '2' " .
532 "SELECT field_select AS field_insert,field2 AS field " .
533 "FROM select_table1 LEFT JOIN select_table2 ON ((select_table1.foo = select_table2.bar)) " .
534 "WHERE field = '2' ORDER BY field FOR UPDATE",
535 "INSERT INTO insert_table (field_insert,field) VALUES ('0','1')"
540 public function testInsertSelectBatching() {
541 $dbWeb = new DatabaseTestHelper( __CLASS__
, [ 'cliMode' => false ] );
543 for ( $i = 0; $i <= 25000; $i++
) {
544 $rows[] = [ 'field' => $i ];
546 $dbWeb->forceNextResult( $rows );
547 $dbWeb->insertSelect(
550 [ 'field' => 'field2' ],
554 $this->assertLastSqlDb( implode( '; ', [
555 'SELECT field2 AS field FROM select_table WHERE * FOR UPDATE',
557 "INSERT INTO insert_table (field) VALUES ('" . implode( "'),('", range( 0, 9999 ) ) . "')",
558 "INSERT INTO insert_table (field) VALUES ('" . implode( "'),('", range( 10000, 19999 ) ) . "')",
559 "INSERT INTO insert_table (field) VALUES ('" . implode( "'),('", range( 20000, 25000 ) ) . "')",
565 * @dataProvider provideReplace
566 * @covers Wikimedia\Rdbms\Database::replace
568 public function testReplace( $sql, $sqlText ) {
569 $this->database
->replace(
571 $sql['uniqueIndexes'],
575 $this->assertLastSql( $sqlText );
578 public static function provideReplace() {
582 'table' => 'replace_table',
583 'uniqueIndexes' => [ 'field' ],
584 'rows' => [ 'field' => 'text', 'field2' => 'text2' ],
586 "BEGIN; DELETE FROM replace_table " .
587 "WHERE (field = 'text'); " .
588 "INSERT INTO replace_table " .
590 "VALUES ('text','text2'); COMMIT"
594 'table' => 'module_deps',
595 'uniqueIndexes' => [ [ 'md_module', 'md_skin' ] ],
597 'md_module' => 'module',
602 "BEGIN; DELETE FROM module_deps " .
603 "WHERE (md_module = 'module' AND md_skin = 'skin'); " .
604 "INSERT INTO module_deps " .
605 "(md_module,md_skin,md_deps) " .
606 "VALUES ('module','skin','deps'); COMMIT"
610 'table' => 'module_deps',
611 'uniqueIndexes' => [ [ 'md_module', 'md_skin' ] ],
614 'md_module' => 'module',
618 'md_module' => 'module2',
619 'md_skin' => 'skin2',
620 'md_deps' => 'deps2',
624 "BEGIN; DELETE FROM module_deps " .
625 "WHERE (md_module = 'module' AND md_skin = 'skin'); " .
626 "INSERT INTO module_deps " .
627 "(md_module,md_skin,md_deps) " .
628 "VALUES ('module','skin','deps'); " .
629 "DELETE FROM module_deps " .
630 "WHERE (md_module = 'module2' AND md_skin = 'skin2'); " .
631 "INSERT INTO module_deps " .
632 "(md_module,md_skin,md_deps) " .
633 "VALUES ('module2','skin2','deps2'); COMMIT"
637 'table' => 'module_deps',
638 'uniqueIndexes' => [ 'md_module', 'md_skin' ],
641 'md_module' => 'module',
645 'md_module' => 'module2',
646 'md_skin' => 'skin2',
647 'md_deps' => 'deps2',
651 "BEGIN; DELETE FROM module_deps " .
652 "WHERE (md_module = 'module') OR (md_skin = 'skin'); " .
653 "INSERT INTO module_deps " .
654 "(md_module,md_skin,md_deps) " .
655 "VALUES ('module','skin','deps'); " .
656 "DELETE FROM module_deps " .
657 "WHERE (md_module = 'module2') OR (md_skin = 'skin2'); " .
658 "INSERT INTO module_deps " .
659 "(md_module,md_skin,md_deps) " .
660 "VALUES ('module2','skin2','deps2'); COMMIT"
664 'table' => 'module_deps',
665 'uniqueIndexes' => [],
667 'md_module' => 'module',
672 "BEGIN; INSERT INTO module_deps " .
673 "(md_module,md_skin,md_deps) " .
674 "VALUES ('module','skin','deps'); COMMIT"
680 * @dataProvider provideNativeReplace
681 * @covers Wikimedia\Rdbms\Database::nativeReplace
683 public function testNativeReplace( $sql, $sqlText ) {
684 $this->database
->nativeReplace(
689 $this->assertLastSql( $sqlText );
692 public static function provideNativeReplace() {
696 'table' => 'replace_table',
697 'rows' => [ 'field' => 'text', 'field2' => 'text2' ],
699 "REPLACE INTO replace_table " .
701 "VALUES ('text','text2')"
707 * @dataProvider provideConditional
708 * @covers Wikimedia\Rdbms\Database::conditional
710 public function testConditional( $sql, $sqlText ) {
711 $this->assertEquals( trim( $this->database
->conditional(
718 public static function provideConditional() {
722 'conds' => [ 'field' => 'text' ],
726 "(CASE WHEN field = 'text' THEN 1 ELSE NULL END)"
730 'conds' => [ 'field' => 'text', 'field2' => 'anothertext' ],
734 "(CASE WHEN field = 'text' AND field2 = 'anothertext' THEN 1 ELSE NULL END)"
738 'conds' => 'field=1',
742 "(CASE WHEN field=1 THEN 1 ELSE NULL END)"
748 * @dataProvider provideBuildConcat
749 * @covers Wikimedia\Rdbms\Database::buildConcat
751 public function testBuildConcat( $stringList, $sqlText ) {
752 $this->assertEquals( trim( $this->database
->buildConcat(
757 public static function provideBuildConcat() {
760 [ 'field', 'field2' ],
761 "CONCAT(field,field2)"
764 [ "'test'", 'field2' ],
765 "CONCAT('test',field2)"
771 * @dataProvider provideBuildLike
772 * @covers Wikimedia\Rdbms\Database::buildLike
773 * @covers Wikimedia\Rdbms\Database::escapeLikeInternal
775 public function testBuildLike( $array, $sqlText ) {
776 $this->assertEquals( trim( $this->database
->buildLike(
781 public static function provideBuildLike() {
785 "LIKE 'text' ESCAPE '`'"
788 [ 'text', new LikeMatch( '%' ) ],
789 "LIKE 'text%' ESCAPE '`'"
792 [ 'text', new LikeMatch( '%' ), 'text2' ],
793 "LIKE 'text%text2' ESCAPE '`'"
796 [ 'text', new LikeMatch( '_' ) ],
797 "LIKE 'text_' ESCAPE '`'"
801 "LIKE 'more`_text' ESCAPE '`'"
804 [ 'C:\\Windows\\', new LikeMatch( '%' ) ],
805 "LIKE 'C:\\Windows\\%' ESCAPE '`'"
808 [ 'accent`_test`', new LikeMatch( '%' ) ],
809 "LIKE 'accent```_test``%' ESCAPE '`'"
815 * @dataProvider provideUnionQueries
816 * @covers Wikimedia\Rdbms\Database::unionQueries
818 public function testUnionQueries( $sql, $sqlText ) {
819 $this->assertEquals( trim( $this->database
->unionQueries(
825 public static function provideUnionQueries() {
829 'sqls' => [ 'RAW SQL', 'RAW2SQL' ],
832 "(RAW SQL) UNION ALL (RAW2SQL)"
836 'sqls' => [ 'RAW SQL', 'RAW2SQL' ],
839 "(RAW SQL) UNION (RAW2SQL)"
843 'sqls' => [ 'RAW SQL', 'RAW2SQL', 'RAW3SQL' ],
846 "(RAW SQL) UNION (RAW2SQL) UNION (RAW3SQL)"
852 * @dataProvider provideUnionConditionPermutations
853 * @covers Wikimedia\Rdbms\Database::unionConditionPermutations
855 public function testUnionConditionPermutations( $params, $expect ) {
856 if ( isset( $params['unionSupportsOrderAndLimit'] ) ) {
857 $this->database
->setUnionSupportsOrderAndLimit( $params['unionSupportsOrderAndLimit'] );
860 $sql = trim( $this->database
->unionConditionPermutations(
863 $params['permute_conds'],
864 isset( $params['extra_conds'] ) ?
$params['extra_conds'] : '',
866 isset( $params['options'] ) ?
$params['options'] : [],
867 isset( $params['join_conds'] ) ?
$params['join_conds'] : []
869 $this->assertEquals( $expect, $sql );
872 public static function provideUnionConditionPermutations() {
873 // phpcs:disable Generic.Files.LineLength
877 'table' => [ 'table1', 'table2' ],
878 'vars' => [ 'field1', 'alias' => 'field2' ],
880 'field3' => [ 1, 2, 3 ],
881 'duplicates' => [ 4, 5, 4 ],
885 'extra_conds' => 'table2.bar > 23',
887 'ORDER BY' => [ 'field1', 'alias' ],
888 'INNER ORDER BY' => [ 'field1', 'field2' ],
892 'table2' => [ 'JOIN', 'table1.foo_id = table2.foo_id' ],
895 "(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 " .
896 "(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 " .
897 "(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 " .
898 "(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 " .
899 "(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 " .
900 "(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 ) " .
901 "ORDER BY field1,alias LIMIT 100"
906 'vars' => [ 'foo_id' ],
908 'bar' => [ 1, 2, 3 ],
910 'extra_conds' => [ 'baz' => null ],
913 'ORDER BY' => [ 'foo_id' ],
917 "(SELECT foo_id FROM foo WHERE bar = '1' AND baz IS NULL ORDER BY foo_id LIMIT 25 ) UNION " .
918 "(SELECT foo_id FROM foo WHERE bar = '2' AND baz IS NULL ORDER BY foo_id LIMIT 25 ) UNION " .
919 "(SELECT foo_id FROM foo WHERE bar = '3' AND baz IS NULL ORDER BY foo_id LIMIT 25 ) " .
920 "ORDER BY foo_id LIMIT 25"
925 'vars' => [ 'foo_id' ],
927 'bar' => [ 1, 2, 3 ],
929 'extra_conds' => [ 'baz' => null ],
932 'ORDER BY' => [ 'foo_id' ],
935 'unionSupportsOrderAndLimit' => false,
937 "(SELECT foo_id FROM foo WHERE bar = '1' AND baz IS NULL ) UNION " .
938 "(SELECT foo_id FROM foo WHERE bar = '2' AND baz IS NULL ) UNION " .
939 "(SELECT foo_id FROM foo WHERE bar = '3' AND baz IS NULL ) " .
940 "ORDER BY foo_id LIMIT 25"
945 'vars' => [ 'foo_id' ],
946 'permute_conds' => [],
947 'extra_conds' => [ 'baz' => null ],
949 'ORDER BY' => [ 'foo_id' ],
953 "SELECT foo_id FROM foo WHERE baz IS NULL ORDER BY foo_id LIMIT 25"
958 'vars' => [ 'foo_id' ],
962 'extra_conds' => [ 'baz' => null ],
964 'ORDER BY' => [ 'foo_id' ],
968 "SELECT foo_id FROM foo WHERE baz IS NULL ORDER BY foo_id LIMIT 25"
973 'vars' => [ 'foo_id' ],
978 'ORDER BY' => [ 'foo_id' ],
983 "SELECT foo_id FROM foo WHERE bar = '1' ORDER BY foo_id LIMIT 150,25"
988 'vars' => [ 'foo_id' ],
989 'permute_conds' => [],
990 'extra_conds' => [ 'baz' => null ],
992 'ORDER BY' => [ 'foo_id' ],
995 'INNER ORDER BY' => [ 'bar_id' ],
998 "(SELECT foo_id FROM foo WHERE baz IS NULL ORDER BY bar_id LIMIT 175 ) ORDER BY foo_id LIMIT 150,25"
1003 'vars' => [ 'foo_id' ],
1004 'permute_conds' => [],
1005 'extra_conds' => [ 'baz' => null ],
1007 'ORDER BY' => [ 'foo_id' ],
1010 'INNER ORDER BY' => [ 'bar_id' ],
1012 'unionSupportsOrderAndLimit' => false,
1014 "SELECT foo_id FROM foo WHERE baz IS NULL ORDER BY foo_id LIMIT 150,25"
1021 * @covers Wikimedia\Rdbms\Database::commit
1022 * @covers Wikimedia\Rdbms\Database::doCommit
1024 public function testTransactionCommit() {
1025 $this->database
->begin( __METHOD__
);
1026 $this->database
->commit( __METHOD__
);
1027 $this->assertLastSql( 'BEGIN; COMMIT' );
1031 * @covers Wikimedia\Rdbms\Database::rollback
1032 * @covers Wikimedia\Rdbms\Database::doRollback
1034 public function testTransactionRollback() {
1035 $this->database
->begin( __METHOD__
);
1036 $this->database
->rollback( __METHOD__
);
1037 $this->assertLastSql( 'BEGIN; ROLLBACK' );
1041 * @covers Wikimedia\Rdbms\Database::dropTable
1043 public function testDropTable() {
1044 $this->database
->setExistingTables( [ 'table' ] );
1045 $this->database
->dropTable( 'table', __METHOD__
);
1046 $this->assertLastSql( 'DROP TABLE table CASCADE' );
1050 * @covers Wikimedia\Rdbms\Database::dropTable
1052 public function testDropNonExistingTable() {
1054 $this->database
->dropTable( 'non_existing', __METHOD__
)
1059 * @dataProvider provideMakeList
1060 * @covers Wikimedia\Rdbms\Database::makeList
1062 public function testMakeList( $list, $mode, $sqlText ) {
1063 $this->assertEquals( trim( $this->database
->makeList(
1068 public static function provideMakeList() {
1071 [ 'value', 'value2' ],
1076 [ 'field', 'field2' ],
1081 [ 'field' => 'value', 'field2' => 'value2' ],
1083 "field = 'value' AND field2 = 'value2'"
1086 [ 'field' => null, "field2 != 'value2'" ],
1088 "field IS NULL AND (field2 != 'value2')"
1091 [ 'field' => [ 'value', null, 'value2' ], 'field2' => 'value2' ],
1093 "(field IN ('value','value2') OR field IS NULL) AND field2 = 'value2'"
1096 [ 'field' => [ null ], 'field2' => null ],
1098 "field IS NULL AND field2 IS NULL"
1101 [ 'field' => 'value', 'field2' => 'value2' ],
1103 "field = 'value' OR field2 = 'value2'"
1106 [ 'field' => 'value', 'field2' => null ],
1108 "field = 'value' OR field2 IS NULL"
1111 [ 'field' => [ 'value', 'value2' ], 'field2' => [ 'value' ] ],
1113 "field IN ('value','value2') OR field2 = 'value'"
1116 [ 'field' => [ null, 'value', null, 'value2' ], "field2 != 'value2'" ],
1118 "(field IN ('value','value2') OR field IS NULL) OR (field2 != 'value2')"
1121 [ 'field' => 'value', 'field2' => 'value2' ],
1123 "field = 'value',field2 = 'value2'"
1126 [ 'field' => 'value', 'field2' => null ],
1128 "field = 'value',field2 = NULL"
1131 [ 'field' => 'value', "field2 != 'value2'" ],
1133 "field = 'value',field2 != 'value2'"
1139 * @covers Wikimedia\Rdbms\Database::registerTempTableOperation
1141 public function testSessionTempTables() {
1142 $temp1 = $this->database
->tableName( 'tmp_table_1' );
1143 $temp2 = $this->database
->tableName( 'tmp_table_2' );
1144 $temp3 = $this->database
->tableName( 'tmp_table_3' );
1146 $this->database
->query( "CREATE TEMPORARY TABLE $temp1 LIKE orig_tbl", __METHOD__
);
1147 $this->database
->query( "CREATE TEMPORARY TABLE $temp2 LIKE orig_tbl", __METHOD__
);
1148 $this->database
->query( "CREATE TEMPORARY TABLE $temp3 LIKE orig_tbl", __METHOD__
);
1150 $this->assertTrue( $this->database
->tableExists( "tmp_table_1", __METHOD__
) );
1151 $this->assertTrue( $this->database
->tableExists( "tmp_table_2", __METHOD__
) );
1152 $this->assertTrue( $this->database
->tableExists( "tmp_table_3", __METHOD__
) );
1154 $this->database
->dropTable( 'tmp_table_1', __METHOD__
);
1155 $this->database
->dropTable( 'tmp_table_2', __METHOD__
);
1156 $this->database
->dropTable( 'tmp_table_3', __METHOD__
);
1158 $this->assertFalse( $this->database
->tableExists( "tmp_table_1", __METHOD__
) );
1159 $this->assertFalse( $this->database
->tableExists( "tmp_table_2", __METHOD__
) );
1160 $this->assertFalse( $this->database
->tableExists( "tmp_table_3", __METHOD__
) );
1162 $this->database
->query( "CREATE TEMPORARY TABLE tmp_table_1 LIKE orig_tbl", __METHOD__
);
1163 $this->database
->query( "CREATE TEMPORARY TABLE 'tmp_table_2' LIKE orig_tbl", __METHOD__
);
1164 $this->database
->query( "CREATE TEMPORARY TABLE `tmp_table_3` LIKE orig_tbl", __METHOD__
);
1166 $this->assertTrue( $this->database
->tableExists( "tmp_table_1", __METHOD__
) );
1167 $this->assertTrue( $this->database
->tableExists( "tmp_table_2", __METHOD__
) );
1168 $this->assertTrue( $this->database
->tableExists( "tmp_table_3", __METHOD__
) );
1170 $this->database
->query( "DROP TEMPORARY TABLE tmp_table_1 LIKE orig_tbl", __METHOD__
);
1171 $this->database
->query( "DROP TEMPORARY TABLE 'tmp_table_2' LIKE orig_tbl", __METHOD__
);
1172 $this->database
->query( "DROP TABLE `tmp_table_3` LIKE orig_tbl", __METHOD__
);
1174 $this->assertFalse( $this->database
->tableExists( "tmp_table_1", __METHOD__
) );
1175 $this->assertFalse( $this->database
->tableExists( "tmp_table_2", __METHOD__
) );
1176 $this->assertFalse( $this->database
->tableExists( "tmp_table_3", __METHOD__
) );