4 * Test the abstract database layer
5 * This is a non DBMS depending test.
7 class DatabaseSQLTest
extends MediaWikiTestCase
{
10 * @var DatabaseTestHelper
14 protected function setUp() {
16 $this->database
= new DatabaseTestHelper( __CLASS__
);
19 protected function assertLastSql( $sqlText ) {
21 $this->database
->getLastSqls(),
27 * @dataProvider provideSelect
28 * @covers DatabaseBase::select
30 public function testSelect( $sql, $sqlText ) {
31 $this->database
->select(
34 isset( $sql['conds'] ) ?
$sql['conds'] : [],
36 isset( $sql['options'] ) ?
$sql['options'] : [],
37 isset( $sql['join_conds'] ) ?
$sql['join_conds'] : []
39 $this->assertLastSql( $sqlText );
42 public static function provideSelect() {
47 'fields' => [ 'field', 'alias' => 'field2' ],
48 'conds' => [ 'alias' => 'text' ],
50 "SELECT field,field2 AS alias " .
52 "WHERE alias = 'text'"
57 'fields' => [ 'field', 'alias' => 'field2' ],
58 'conds' => [ 'alias' => 'text' ],
59 'options' => [ 'LIMIT' => 1, 'ORDER BY' => 'field' ],
61 "SELECT field,field2 AS alias " .
63 "WHERE alias = 'text' " .
69 'tables' => [ 'table', 't2' => 'table2' ],
70 'fields' => [ 'tid', 'field', 'alias' => 'field2', 't2.id' ],
71 'conds' => [ 'alias' => 'text' ],
72 'options' => [ 'LIMIT' => 1, 'ORDER BY' => 'field' ],
73 'join_conds' => [ 't2' => [
74 'LEFT JOIN', 'tid = t2.id'
77 "SELECT tid,field,field2 AS alias,t2.id " .
78 "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
79 "WHERE alias = 'text' " .
85 'tables' => [ 'table', 't2' => 'table2' ],
86 'fields' => [ 'tid', 'field', 'alias' => 'field2', 't2.id' ],
87 'conds' => [ 'alias' => 'text' ],
88 'options' => [ 'LIMIT' => 1, 'GROUP BY' => 'field', 'HAVING' => 'COUNT(*) > 1' ],
89 'join_conds' => [ 't2' => [
90 'LEFT JOIN', 'tid = t2.id'
93 "SELECT tid,field,field2 AS alias,t2.id " .
94 "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
95 "WHERE alias = 'text' " .
96 "GROUP BY field HAVING COUNT(*) > 1 " .
101 'tables' => [ 'table', 't2' => 'table2' ],
102 'fields' => [ 'tid', 'field', 'alias' => 'field2', 't2.id' ],
103 'conds' => [ 'alias' => 'text' ],
106 'GROUP BY' => [ 'field', 'field2' ],
107 'HAVING' => [ 'COUNT(*) > 1', 'field' => 1 ]
109 'join_conds' => [ 't2' => [
110 'LEFT JOIN', 'tid = t2.id'
113 "SELECT tid,field,field2 AS alias,t2.id " .
114 "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
115 "WHERE alias = 'text' " .
116 "GROUP BY field,field2 HAVING (COUNT(*) > 1) AND field = '1' " .
121 'tables' => [ 'table' ],
122 'fields' => [ 'alias' => 'field' ],
123 'conds' => [ 'alias' => [ 1, 2, 3, 4 ] ],
125 "SELECT field AS alias " .
127 "WHERE alias IN ('1','2','3','4')"
133 * @dataProvider provideUpdate
134 * @covers DatabaseBase::update
136 public function testUpdate( $sql, $sqlText ) {
137 $this->database
->update(
142 isset( $sql['options'] ) ?
$sql['options'] : []
144 $this->assertLastSql( $sqlText );
147 public static function provideUpdate() {
152 'values' => [ 'field' => 'text', 'field2' => 'text2' ],
153 'conds' => [ 'alias' => 'text' ],
156 "SET field = 'text'" .
157 ",field2 = 'text2' " .
158 "WHERE alias = 'text'"
163 'values' => [ 'field = other', 'field2' => 'text2' ],
164 'conds' => [ 'id' => '1' ],
167 "SET field = other" .
168 ",field2 = 'text2' " .
174 'values' => [ 'field = other', 'field2' => 'text2' ],
178 "SET field = other" .
185 * @dataProvider provideDelete
186 * @covers DatabaseBase::delete
188 public function testDelete( $sql, $sqlText ) {
189 $this->database
->delete(
194 $this->assertLastSql( $sqlText );
197 public static function provideDelete() {
202 'conds' => [ 'alias' => 'text' ],
204 "DELETE FROM table " .
205 "WHERE alias = 'text'"
218 * @dataProvider provideUpsert
219 * @covers DatabaseBase::upsert
221 public function testUpsert( $sql, $sqlText ) {
222 $this->database
->upsert(
225 $sql['uniqueIndexes'],
229 $this->assertLastSql( $sqlText );
232 public static function provideUpsert() {
236 'table' => 'upsert_table',
237 'rows' => [ 'field' => 'text', 'field2' => 'text2' ],
238 'uniqueIndexes' => [ 'field' ],
239 'set' => [ 'field' => 'set' ],
242 "UPDATE upsert_table " .
243 "SET field = 'set' " .
244 "WHERE ((field = 'text')); " .
245 "INSERT IGNORE INTO upsert_table " .
247 "VALUES ('text','text2'); " .
254 * @dataProvider provideDeleteJoin
255 * @covers DatabaseBase::deleteJoin
257 public function testDeleteJoin( $sql, $sqlText ) {
258 $this->database
->deleteJoin(
266 $this->assertLastSql( $sqlText );
269 public static function provideDeleteJoin() {
273 'delTable' => 'table',
274 'joinTable' => 'table_join',
276 'joinVar' => 'field_join',
277 'conds' => [ 'alias' => 'text' ],
279 "DELETE FROM table " .
281 "SELECT field_join FROM table_join WHERE alias = 'text'" .
286 'delTable' => 'table',
287 'joinTable' => 'table_join',
289 'joinVar' => 'field_join',
292 "DELETE FROM table " .
294 "SELECT field_join FROM table_join " .
301 * @dataProvider provideInsert
302 * @covers DatabaseBase::insert
304 public function testInsert( $sql, $sqlText ) {
305 $this->database
->insert(
309 isset( $sql['options'] ) ?
$sql['options'] : []
311 $this->assertLastSql( $sqlText );
314 public static function provideInsert() {
319 'rows' => [ 'field' => 'text', 'field2' => 2 ],
321 "INSERT INTO table " .
323 "VALUES ('text','2')"
328 'rows' => [ 'field' => 'text', 'field2' => 2 ],
329 'options' => 'IGNORE',
331 "INSERT IGNORE INTO table " .
333 "VALUES ('text','2')"
339 [ 'field' => 'text', 'field2' => 2 ],
340 [ 'field' => 'multi', 'field2' => 3 ],
342 'options' => 'IGNORE',
344 "INSERT IGNORE INTO table " .
354 * @dataProvider provideInsertSelect
355 * @covers DatabaseBase::insertSelect
357 public function testInsertSelect( $sql, $sqlText ) {
358 $this->database
->insertSelect(
364 isset( $sql['insertOptions'] ) ?
$sql['insertOptions'] : [],
365 isset( $sql['selectOptions'] ) ?
$sql['selectOptions'] : []
367 $this->assertLastSql( $sqlText );
370 public static function provideInsertSelect() {
374 'destTable' => 'insert_table',
375 'srcTable' => 'select_table',
376 'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
379 "INSERT INTO insert_table " .
380 "(field_insert,field) " .
381 "SELECT field_select,field2 " .
386 'destTable' => 'insert_table',
387 'srcTable' => 'select_table',
388 'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
389 'conds' => [ 'field' => 2 ],
391 "INSERT INTO insert_table " .
392 "(field_insert,field) " .
393 "SELECT field_select,field2 " .
394 "FROM select_table " .
399 'destTable' => 'insert_table',
400 'srcTable' => 'select_table',
401 'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
402 'conds' => [ 'field' => 2 ],
403 'insertOptions' => 'IGNORE',
404 'selectOptions' => [ 'ORDER BY' => 'field' ],
406 "INSERT IGNORE INTO insert_table " .
407 "(field_insert,field) " .
408 "SELECT field_select,field2 " .
409 "FROM select_table " .
410 "WHERE field = '2' " .
417 * @dataProvider provideReplace
418 * @covers DatabaseBase::replace
420 public function testReplace( $sql, $sqlText ) {
421 $this->database
->replace(
423 $sql['uniqueIndexes'],
427 $this->assertLastSql( $sqlText );
430 public static function provideReplace() {
434 'table' => 'replace_table',
435 'uniqueIndexes' => [ 'field' ],
436 'rows' => [ 'field' => 'text', 'field2' => 'text2' ],
438 "DELETE FROM replace_table " .
439 "WHERE ( field='text' ); " .
440 "INSERT INTO replace_table " .
442 "VALUES ('text','text2')"
446 'table' => 'module_deps',
447 'uniqueIndexes' => [ [ 'md_module', 'md_skin' ] ],
449 'md_module' => 'module',
454 "DELETE FROM module_deps " .
455 "WHERE ( md_module='module' AND md_skin='skin' ); " .
456 "INSERT INTO module_deps " .
457 "(md_module,md_skin,md_deps) " .
458 "VALUES ('module','skin','deps')"
462 'table' => 'module_deps',
463 'uniqueIndexes' => [ [ 'md_module', 'md_skin' ] ],
466 'md_module' => 'module',
470 'md_module' => 'module2',
471 'md_skin' => 'skin2',
472 'md_deps' => 'deps2',
476 "DELETE FROM module_deps " .
477 "WHERE ( md_module='module' AND md_skin='skin' ); " .
478 "INSERT INTO module_deps " .
479 "(md_module,md_skin,md_deps) " .
480 "VALUES ('module','skin','deps'); " .
481 "DELETE FROM module_deps " .
482 "WHERE ( md_module='module2' AND md_skin='skin2' ); " .
483 "INSERT INTO module_deps " .
484 "(md_module,md_skin,md_deps) " .
485 "VALUES ('module2','skin2','deps2')"
489 'table' => 'module_deps',
490 'uniqueIndexes' => [ 'md_module', 'md_skin' ],
493 'md_module' => 'module',
497 'md_module' => 'module2',
498 'md_skin' => 'skin2',
499 'md_deps' => 'deps2',
503 "DELETE FROM module_deps " .
504 "WHERE ( md_module='module' ) OR ( md_skin='skin' ); " .
505 "INSERT INTO module_deps " .
506 "(md_module,md_skin,md_deps) " .
507 "VALUES ('module','skin','deps'); " .
508 "DELETE FROM module_deps " .
509 "WHERE ( md_module='module2' ) OR ( md_skin='skin2' ); " .
510 "INSERT INTO module_deps " .
511 "(md_module,md_skin,md_deps) " .
512 "VALUES ('module2','skin2','deps2')"
516 'table' => 'module_deps',
517 'uniqueIndexes' => [],
519 'md_module' => 'module',
524 "INSERT INTO module_deps " .
525 "(md_module,md_skin,md_deps) " .
526 "VALUES ('module','skin','deps')"
532 * @dataProvider provideNativeReplace
533 * @covers DatabaseBase::nativeReplace
535 public function testNativeReplace( $sql, $sqlText ) {
536 $this->database
->nativeReplace(
541 $this->assertLastSql( $sqlText );
544 public static function provideNativeReplace() {
548 'table' => 'replace_table',
549 'rows' => [ 'field' => 'text', 'field2' => 'text2' ],
551 "REPLACE INTO replace_table " .
553 "VALUES ('text','text2')"
559 * @dataProvider provideConditional
560 * @covers DatabaseBase::conditional
562 public function testConditional( $sql, $sqlText ) {
563 $this->assertEquals( trim( $this->database
->conditional(
570 public static function provideConditional() {
574 'conds' => [ 'field' => 'text' ],
578 "(CASE WHEN field = 'text' THEN 1 ELSE NULL END)"
582 'conds' => [ 'field' => 'text', 'field2' => 'anothertext' ],
586 "(CASE WHEN field = 'text' AND field2 = 'anothertext' THEN 1 ELSE NULL END)"
590 'conds' => 'field=1',
594 "(CASE WHEN field=1 THEN 1 ELSE NULL END)"
600 * @dataProvider provideBuildConcat
601 * @covers DatabaseBase::buildConcat
603 public function testBuildConcat( $stringList, $sqlText ) {
604 $this->assertEquals( trim( $this->database
->buildConcat(
609 public static function provideBuildConcat() {
612 [ 'field', 'field2' ],
613 "CONCAT(field,field2)"
616 [ "'test'", 'field2' ],
617 "CONCAT('test',field2)"
623 * @dataProvider provideBuildLike
624 * @covers DatabaseBase::buildLike
626 public function testBuildLike( $array, $sqlText ) {
627 $this->assertEquals( trim( $this->database
->buildLike(
632 public static function provideBuildLike() {
639 [ 'text', new LikeMatch( '%' ) ],
643 [ 'text', new LikeMatch( '%' ), 'text2' ],
647 [ 'text', new LikeMatch( '_' ) ],
654 * @dataProvider provideUnionQueries
655 * @covers DatabaseBase::unionQueries
657 public function testUnionQueries( $sql, $sqlText ) {
658 $this->assertEquals( trim( $this->database
->unionQueries(
664 public static function provideUnionQueries() {
668 'sqls' => [ 'RAW SQL', 'RAW2SQL' ],
671 "(RAW SQL) UNION ALL (RAW2SQL)"
675 'sqls' => [ 'RAW SQL', 'RAW2SQL' ],
678 "(RAW SQL) UNION (RAW2SQL)"
682 'sqls' => [ 'RAW SQL', 'RAW2SQL', 'RAW3SQL' ],
685 "(RAW SQL) UNION (RAW2SQL) UNION (RAW3SQL)"
691 * @covers DatabaseBase::commit
693 public function testTransactionCommit() {
694 $this->database
->begin( __METHOD__
);
695 $this->database
->commit( __METHOD__
);
696 $this->assertLastSql( 'BEGIN; COMMIT' );
700 * @covers DatabaseBase::rollback
702 public function testTransactionRollback() {
703 $this->database
->begin( __METHOD__
);
704 $this->database
->rollback( __METHOD__
);
705 $this->assertLastSql( 'BEGIN; ROLLBACK' );
709 * @covers DatabaseBase::dropTable
711 public function testDropTable() {
712 $this->database
->setExistingTables( [ 'table' ] );
713 $this->database
->dropTable( 'table', __METHOD__
);
714 $this->assertLastSql( 'DROP TABLE table' );
718 * @covers DatabaseBase::dropTable
720 public function testDropNonExistingTable() {
722 $this->database
->dropTable( 'non_existing', __METHOD__
)
727 * @dataProvider provideMakeList
728 * @covers DatabaseBase::makeList
730 public function testMakeList( $list, $mode, $sqlText ) {
731 $this->assertEquals( trim( $this->database
->makeList(
736 public static function provideMakeList() {
739 [ 'value', 'value2' ],
744 [ 'field', 'field2' ],
749 [ 'field' => 'value', 'field2' => 'value2' ],
751 "field = 'value' AND field2 = 'value2'"
754 [ 'field' => null, "field2 != 'value2'" ],
756 "field IS NULL AND (field2 != 'value2')"
759 [ 'field' => [ 'value', null, 'value2' ], 'field2' => 'value2' ],
761 "(field IN ('value','value2') OR field IS NULL) AND field2 = 'value2'"
764 [ 'field' => [ null ], 'field2' => null ],
766 "field IS NULL AND field2 IS NULL"
769 [ 'field' => 'value', 'field2' => 'value2' ],
771 "field = 'value' OR field2 = 'value2'"
774 [ 'field' => 'value', 'field2' => null ],
776 "field = 'value' OR field2 IS NULL"
779 [ 'field' => [ 'value', 'value2' ], 'field2' => [ 'value' ] ],
781 "field IN ('value','value2') OR field2 = 'value'"
784 [ 'field' => [ null, 'value', null, 'value2' ], "field2 != 'value2'" ],
786 "(field IN ('value','value2') OR field IS NULL) OR (field2 != 'value2')"
789 [ 'field' => 'value', 'field2' => 'value2' ],
791 "field = 'value',field2 = 'value2'"
794 [ 'field' => 'value', 'field2' => null ],
796 "field = 'value',field2 = NULL"
799 [ 'field' => 'value', "field2 != 'value2'" ],
801 "field = 'value',field2 != 'value2'"