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'] : array(),
36 isset( $sql['options'] ) ?
$sql['options'] : array(),
37 isset( $sql['join_conds'] ) ?
$sql['join_conds'] : array()
39 $this->assertLastSql( $sqlText );
42 public static function provideSelect() {
47 'fields' => array( 'field', 'alias' => 'field2' ),
48 'conds' => array( 'alias' => 'text' ),
50 "SELECT field,field2 AS alias " .
52 "WHERE alias = 'text'"
57 'fields' => array( 'field', 'alias' => 'field2' ),
58 'conds' => array( 'alias' => 'text' ),
59 'options' => array( 'LIMIT' => 1, 'ORDER BY' => 'field' ),
61 "SELECT field,field2 AS alias " .
63 "WHERE alias = 'text' " .
69 'tables' => array( 'table', 't2' => 'table2' ),
70 'fields' => array( 'tid', 'field', 'alias' => 'field2', 't2.id' ),
71 'conds' => array( 'alias' => 'text' ),
72 'options' => array( 'LIMIT' => 1, 'ORDER BY' => 'field' ),
73 'join_conds' => array( 't2' => array(
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' => array( 'table', 't2' => 'table2' ),
86 'fields' => array( 'tid', 'field', 'alias' => 'field2', 't2.id' ),
87 'conds' => array( 'alias' => 'text' ),
88 'options' => array( 'LIMIT' => 1, 'GROUP BY' => 'field', 'HAVING' => 'COUNT(*) > 1' ),
89 'join_conds' => array( 't2' => array(
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' => array( 'table', 't2' => 'table2' ),
102 'fields' => array( 'tid', 'field', 'alias' => 'field2', 't2.id' ),
103 'conds' => array( 'alias' => 'text' ),
104 'options' => array( 'LIMIT' => 1, 'GROUP BY' => array( 'field', 'field2' ), 'HAVING' => array( 'COUNT(*) > 1', 'field' => 1 ) ),
105 'join_conds' => array( 't2' => array(
106 'LEFT JOIN', 'tid = t2.id'
109 "SELECT tid,field,field2 AS alias,t2.id " .
110 "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
111 "WHERE alias = 'text' " .
112 "GROUP BY field,field2 HAVING (COUNT(*) > 1) AND field = '1' " .
117 'tables' => array( 'table' ),
118 'fields' => array( 'alias' => 'field' ),
119 'conds' => array( 'alias' => array( 1, 2, 3, 4 ) ),
121 "SELECT field AS alias " .
123 "WHERE alias IN ('1','2','3','4')"
129 * @dataProvider provideUpdate
130 * @covers DatabaseBase::update
132 public function testUpdate( $sql, $sqlText ) {
133 $this->database
->update(
138 isset( $sql['options'] ) ?
$sql['options'] : array()
140 $this->assertLastSql( $sqlText );
143 public static function provideUpdate() {
148 'values' => array( 'field' => 'text', 'field2' => 'text2' ),
149 'conds' => array( 'alias' => 'text' ),
152 "SET field = 'text'" .
153 ",field2 = 'text2' " .
154 "WHERE alias = 'text'"
159 'values' => array( 'field = other', 'field2' => 'text2' ),
160 'conds' => array( 'id' => '1' ),
163 "SET field = other" .
164 ",field2 = 'text2' " .
170 'values' => array( 'field = other', 'field2' => 'text2' ),
174 "SET field = other" .
181 * @dataProvider provideDelete
182 * @covers DatabaseBase::delete
184 public function testDelete( $sql, $sqlText ) {
185 $this->database
->delete(
190 $this->assertLastSql( $sqlText );
193 public static function provideDelete() {
198 'conds' => array( 'alias' => 'text' ),
200 "DELETE FROM table " .
201 "WHERE alias = 'text'"
214 * @dataProvider provideUpsert
215 * @covers DatabaseBase::upsert
217 public function testUpsert( $sql, $sqlText ) {
218 $this->database
->upsert(
221 $sql['uniqueIndexes'],
225 $this->assertLastSql( $sqlText );
228 public static function provideUpsert() {
232 'table' => 'upsert_table',
233 'rows' => array( 'field' => 'text', 'field2' => 'text2' ),
234 'uniqueIndexes' => array( 'field' ),
235 'set' => array( 'field' => 'set' ),
238 "UPDATE upsert_table " .
239 "SET field = 'set' " .
240 "WHERE ((field = 'text')); " .
241 "INSERT IGNORE INTO upsert_table " .
243 "VALUES ('text','text2'); " .
250 * @dataProvider provideDeleteJoin
251 * @covers DatabaseBase::deleteJoin
253 public function testDeleteJoin( $sql, $sqlText ) {
254 $this->database
->deleteJoin(
262 $this->assertLastSql( $sqlText );
265 public static function provideDeleteJoin() {
269 'delTable' => 'table',
270 'joinTable' => 'table_join',
272 'joinVar' => 'field_join',
273 'conds' => array( 'alias' => 'text' ),
275 "DELETE FROM table " .
277 "SELECT field_join FROM table_join WHERE alias = 'text'" .
282 'delTable' => 'table',
283 'joinTable' => 'table_join',
285 'joinVar' => 'field_join',
288 "DELETE FROM table " .
290 "SELECT field_join FROM table_join " .
297 * @dataProvider provideInsert
298 * @covers DatabaseBase::insert
300 public function testInsert( $sql, $sqlText ) {
301 $this->database
->insert(
305 isset( $sql['options'] ) ?
$sql['options'] : array()
307 $this->assertLastSql( $sqlText );
310 public static function provideInsert() {
315 'rows' => array( 'field' => 'text', 'field2' => 2 ),
317 "INSERT INTO table " .
319 "VALUES ('text','2')"
324 'rows' => array( 'field' => 'text', 'field2' => 2 ),
325 'options' => 'IGNORE',
327 "INSERT IGNORE INTO table " .
329 "VALUES ('text','2')"
335 array( 'field' => 'text', 'field2' => 2 ),
336 array( 'field' => 'multi', 'field2' => 3 ),
338 'options' => 'IGNORE',
340 "INSERT IGNORE INTO table " .
350 * @dataProvider provideInsertSelect
351 * @covers DatabaseBase::insertSelect
353 public function testInsertSelect( $sql, $sqlText ) {
354 $this->database
->insertSelect(
360 isset( $sql['insertOptions'] ) ?
$sql['insertOptions'] : array(),
361 isset( $sql['selectOptions'] ) ?
$sql['selectOptions'] : array()
363 $this->assertLastSql( $sqlText );
366 public static function provideInsertSelect() {
370 'destTable' => 'insert_table',
371 'srcTable' => 'select_table',
372 'varMap' => array( 'field_insert' => 'field_select', 'field' => 'field2' ),
375 "INSERT INTO insert_table " .
376 "(field_insert,field) " .
377 "SELECT field_select,field2 " .
382 'destTable' => 'insert_table',
383 'srcTable' => 'select_table',
384 'varMap' => array( 'field_insert' => 'field_select', 'field' => 'field2' ),
385 'conds' => array( 'field' => 2 ),
387 "INSERT INTO insert_table " .
388 "(field_insert,field) " .
389 "SELECT field_select,field2 " .
390 "FROM select_table " .
395 'destTable' => 'insert_table',
396 'srcTable' => 'select_table',
397 'varMap' => array( 'field_insert' => 'field_select', 'field' => 'field2' ),
398 'conds' => array( 'field' => 2 ),
399 'insertOptions' => 'IGNORE',
400 'selectOptions' => array( 'ORDER BY' => 'field' ),
402 "INSERT IGNORE INTO insert_table " .
403 "(field_insert,field) " .
404 "SELECT field_select,field2 " .
405 "FROM select_table " .
406 "WHERE field = '2' " .
413 * @dataProvider provideReplace
414 * @covers DatabaseBase::replace
416 public function testReplace( $sql, $sqlText ) {
417 $this->database
->replace(
419 $sql['uniqueIndexes'],
423 $this->assertLastSql( $sqlText );
426 public static function provideReplace() {
430 'table' => 'replace_table',
431 'uniqueIndexes' => array( 'field' ),
432 'rows' => array( 'field' => 'text', 'field2' => 'text2' ),
434 "DELETE FROM replace_table " .
435 "WHERE ( field='text' ); " .
436 "INSERT INTO replace_table " .
438 "VALUES ('text','text2')"
442 'table' => 'module_deps',
443 'uniqueIndexes' => array( array( 'md_module', 'md_skin' ) ),
445 'md_module' => 'module',
450 "DELETE FROM module_deps " .
451 "WHERE ( md_module='module' AND md_skin='skin' ); " .
452 "INSERT INTO module_deps " .
453 "(md_module,md_skin,md_deps) " .
454 "VALUES ('module','skin','deps')"
458 'table' => 'module_deps',
459 'uniqueIndexes' => array( array( 'md_module', 'md_skin' ) ),
462 'md_module' => 'module',
466 'md_module' => 'module2',
467 'md_skin' => 'skin2',
468 'md_deps' => 'deps2',
472 "DELETE FROM module_deps " .
473 "WHERE ( md_module='module' AND md_skin='skin' ); " .
474 "INSERT INTO module_deps " .
475 "(md_module,md_skin,md_deps) " .
476 "VALUES ('module','skin','deps'); " .
477 "DELETE FROM module_deps " .
478 "WHERE ( md_module='module2' AND md_skin='skin2' ); " .
479 "INSERT INTO module_deps " .
480 "(md_module,md_skin,md_deps) " .
481 "VALUES ('module2','skin2','deps2')"
485 'table' => 'module_deps',
486 'uniqueIndexes' => array( 'md_module', 'md_skin' ),
489 'md_module' => 'module',
493 'md_module' => 'module2',
494 'md_skin' => 'skin2',
495 'md_deps' => 'deps2',
499 "DELETE FROM module_deps " .
500 "WHERE ( md_module='module' ) OR ( md_skin='skin' ); " .
501 "INSERT INTO module_deps " .
502 "(md_module,md_skin,md_deps) " .
503 "VALUES ('module','skin','deps'); " .
504 "DELETE FROM module_deps " .
505 "WHERE ( md_module='module2' ) OR ( md_skin='skin2' ); " .
506 "INSERT INTO module_deps " .
507 "(md_module,md_skin,md_deps) " .
508 "VALUES ('module2','skin2','deps2')"
512 'table' => 'module_deps',
513 'uniqueIndexes' => array(),
515 'md_module' => 'module',
520 "INSERT INTO module_deps " .
521 "(md_module,md_skin,md_deps) " .
522 "VALUES ('module','skin','deps')"
528 * @dataProvider provideNativeReplace
529 * @covers DatabaseBase::nativeReplace
531 public function testNativeReplace( $sql, $sqlText ) {
532 $this->database
->nativeReplace(
537 $this->assertLastSql( $sqlText );
540 public static function provideNativeReplace() {
544 'table' => 'replace_table',
545 'rows' => array( 'field' => 'text', 'field2' => 'text2' ),
547 "REPLACE INTO replace_table " .
549 "VALUES ('text','text2')"
555 * @dataProvider provideConditional
556 * @covers DatabaseBase::conditional
558 public function testConditional( $sql, $sqlText ) {
559 $this->assertEquals( trim( $this->database
->conditional(
566 public static function provideConditional() {
570 'conds' => array( 'field' => 'text' ),
574 "(CASE WHEN field = 'text' THEN 1 ELSE NULL END)"
578 'conds' => array( 'field' => 'text', 'field2' => 'anothertext' ),
582 "(CASE WHEN field = 'text' AND field2 = 'anothertext' THEN 1 ELSE NULL END)"
586 'conds' => 'field=1',
590 "(CASE WHEN field=1 THEN 1 ELSE NULL END)"
596 * @dataProvider provideBuildConcat
597 * @covers DatabaseBase::buildConcat
599 public function testBuildConcat( $stringList, $sqlText ) {
600 $this->assertEquals( trim( $this->database
->buildConcat(
605 public static function provideBuildConcat() {
608 array( 'field', 'field2' ),
609 "CONCAT(field,field2)"
612 array( "'test'", 'field2' ),
613 "CONCAT('test',field2)"
619 * @dataProvider provideBuildLike
620 * @covers DatabaseBase::buildLike
622 public function testBuildLike( $array, $sqlText ) {
623 $this->assertEquals( trim( $this->database
->buildLike(
628 public static function provideBuildLike() {
635 array( 'text', new LikeMatch( '%' ) ),
639 array( 'text', new LikeMatch( '%' ), 'text2' ),
643 array( 'text', new LikeMatch( '_' ) ),
650 * @dataProvider provideUnionQueries
651 * @covers DatabaseBase::unionQueries
653 public function testUnionQueries( $sql, $sqlText ) {
654 $this->assertEquals( trim( $this->database
->unionQueries(
660 public static function provideUnionQueries() {
664 'sqls' => array( 'RAW SQL', 'RAW2SQL' ),
667 "(RAW SQL) UNION ALL (RAW2SQL)"
671 'sqls' => array( 'RAW SQL', 'RAW2SQL' ),
674 "(RAW SQL) UNION (RAW2SQL)"
678 'sqls' => array( 'RAW SQL', 'RAW2SQL', 'RAW3SQL' ),
681 "(RAW SQL) UNION (RAW2SQL) UNION (RAW3SQL)"
687 * @covers DatabaseBase::commit
689 public function testTransactionCommit() {
690 $this->database
->begin( __METHOD__
);
691 $this->database
->commit( __METHOD__
);
692 $this->assertLastSql( 'BEGIN; COMMIT' );
696 * @covers DatabaseBase::rollback
698 public function testTransactionRollback() {
699 $this->database
->begin( __METHOD__
);
700 $this->database
->rollback( __METHOD__
);
701 $this->assertLastSql( 'BEGIN; ROLLBACK' );
705 * @covers DatabaseBase::dropTable
707 public function testDropTable() {
708 $this->database
->setExistingTables( array( 'table' ) );
709 $this->database
->dropTable( 'table', __METHOD__
);
710 $this->assertLastSql( 'DROP TABLE table' );
714 * @covers DatabaseBase::dropTable
716 public function testDropNonExistingTable() {
718 $this->database
->dropTable( 'non_existing', __METHOD__
)