4 * Test the abstract database layer
5 * This is a non DBMS depending test.
7 class DatabaseSQLTest
extends MediaWikiTestCase
{
11 protected function setUp() {
13 $this->database
= new DatabaseTestHelper( __CLASS__
);
16 protected function assertLastSql( $sqlText ) {
18 $this->database
->getLastSqls(),
24 * @dataProvider provideSelect
26 function testSelect( $sql, $sqlText ) {
27 $this->database
->select(
30 isset( $sql['conds'] ) ?
$sql['conds'] : array(),
32 isset( $sql['options'] ) ?
$sql['options'] : array(),
33 isset( $sql['join_conds'] ) ?
$sql['join_conds'] : array()
35 $this->assertLastSql( $sqlText );
38 public static function provideSelect() {
43 'fields' => array( 'field', 'alias' => 'field2' ),
44 'conds' => array( 'alias' => 'text' ),
46 "SELECT field,field2 AS alias " .
48 "WHERE alias = 'text'"
53 'fields' => array( 'field', 'alias' => 'field2' ),
54 'conds' => array( 'alias' => 'text' ),
55 'options' => array( 'LIMIT' => 1, 'ORDER BY' => 'field' ),
57 "SELECT field,field2 AS alias " .
59 "WHERE alias = 'text' " .
65 'tables' => array( 'table', 't2' => 'table2' ),
66 'fields' => array( 'tid', 'field', 'alias' => 'field2', 't2.id' ),
67 'conds' => array( 'alias' => 'text' ),
68 'options' => array( 'LIMIT' => 1, 'ORDER BY' => 'field' ),
69 'join_conds' => array( 't2' => array(
70 'LEFT JOIN', 'tid = t2.id'
73 "SELECT tid,field,field2 AS alias,t2.id " .
74 "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
75 "WHERE alias = 'text' " .
81 'tables' => array( 'table', 't2' => 'table2' ),
82 'fields' => array( 'tid', 'field', 'alias' => 'field2', 't2.id' ),
83 'conds' => array( 'alias' => 'text' ),
84 'options' => array( 'LIMIT' => 1, 'GROUP BY' => 'field', 'HAVING' => 'COUNT(*) > 1' ),
85 'join_conds' => array( 't2' => array(
86 'LEFT JOIN', 'tid = t2.id'
89 "SELECT tid,field,field2 AS alias,t2.id " .
90 "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
91 "WHERE alias = 'text' " .
92 "GROUP BY field HAVING COUNT(*) > 1 " .
97 'tables' => array( 'table', 't2' => 'table2' ),
98 'fields' => array( 'tid', 'field', 'alias' => 'field2', 't2.id' ),
99 'conds' => array( 'alias' => 'text' ),
100 'options' => array( 'LIMIT' => 1, 'GROUP BY' => array( 'field', 'field2' ), 'HAVING' => array( 'COUNT(*) > 1', 'field' => 1 ) ),
101 'join_conds' => array( 't2' => array(
102 'LEFT JOIN', 'tid = t2.id'
105 "SELECT tid,field,field2 AS alias,t2.id " .
106 "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
107 "WHERE alias = 'text' " .
108 "GROUP BY field,field2 HAVING (COUNT(*) > 1) AND field = '1' " .
113 'tables' => array( 'table' ),
114 'fields' => array( 'alias' => 'field' ),
115 'conds' => array( 'alias' => array( 1, 2, 3, 4 ) ),
117 "SELECT field AS alias " .
119 "WHERE alias IN ('1','2','3','4')"
125 * @dataProvider provideUpdate
127 function testUpdate( $sql, $sqlText ) {
128 $this->database
->update(
133 isset( $sql['options'] ) ?
$sql['options'] : array()
135 $this->assertLastSql( $sqlText );
138 public static function provideUpdate() {
143 'values' => array( 'field' => 'text', 'field2' => 'text2' ),
144 'conds' => array( 'alias' => 'text' ),
147 "SET field = 'text'" .
148 ",field2 = 'text2' " .
149 "WHERE alias = 'text'"
154 'values' => array( 'field = other', 'field2' => 'text2' ),
155 'conds' => array( 'id' => '1' ),
158 "SET field = other" .
159 ",field2 = 'text2' " .
165 'values' => array( 'field = other', 'field2' => 'text2' ),
169 "SET field = other" .
176 * @dataProvider provideDelete
178 function testDelete( $sql, $sqlText ) {
179 $this->database
->delete(
184 $this->assertLastSql( $sqlText );
187 public static function provideDelete() {
192 'conds' => array( 'alias' => 'text' ),
194 "DELETE FROM table " .
195 "WHERE alias = 'text'"
208 * @dataProvider provideDeleteJoin
210 function testDeleteJoin( $sql, $sqlText ) {
211 $this->database
->deleteJoin(
219 $this->assertLastSql( $sqlText );
222 public static function provideDeleteJoin() {
226 'delTable' => 'table',
227 'joinTable' => 'table_join',
229 'joinVar' => 'field_join',
230 'conds' => array( 'alias' => 'text' ),
232 "DELETE FROM table " .
234 "SELECT field_join FROM table_join WHERE alias = 'text'" .
239 'delTable' => 'table',
240 'joinTable' => 'table_join',
242 'joinVar' => 'field_join',
245 "DELETE FROM table " .
247 "SELECT field_join FROM table_join " .
254 * @dataProvider provideInsert
256 function testInsert( $sql, $sqlText ) {
257 $this->database
->insert(
261 isset( $sql['options'] ) ?
$sql['options'] : array()
263 $this->assertLastSql( $sqlText );
266 public static function provideInsert() {
271 'rows' => array( 'field' => 'text', 'field2' => 2 ),
273 "INSERT INTO table " .
275 "VALUES ('text','2')"
280 'rows' => array( 'field' => 'text', 'field2' => 2 ),
281 'options' => 'IGNORE',
283 "INSERT IGNORE INTO table " .
285 "VALUES ('text','2')"
291 array( 'field' => 'text', 'field2' => 2 ),
292 array( 'field' => 'multi', 'field2' => 3 ),
294 'options' => 'IGNORE',
296 "INSERT IGNORE INTO table " .
306 * @dataProvider provideInsertSelect
308 function testInsertSelect( $sql, $sqlText ) {
309 $this->database
->insertSelect(
315 isset( $sql['insertOptions'] ) ?
$sql['insertOptions'] : array(),
316 isset( $sql['selectOptions'] ) ?
$sql['selectOptions'] : array()
318 $this->assertLastSql( $sqlText );
321 public static function provideInsertSelect() {
325 'destTable' => 'insert_table',
326 'srcTable' => 'select_table',
327 'varMap' => array( 'field_insert' => 'field_select', 'field' => 'field2' ),
330 "INSERT INTO insert_table " .
331 "(field_insert,field) " .
332 "SELECT field_select,field2 " .
337 'destTable' => 'insert_table',
338 'srcTable' => 'select_table',
339 'varMap' => array( 'field_insert' => 'field_select', 'field' => 'field2' ),
340 'conds' => array( 'field' => 2 ),
342 "INSERT INTO insert_table " .
343 "(field_insert,field) " .
344 "SELECT field_select,field2 " .
345 "FROM select_table " .
350 'destTable' => 'insert_table',
351 'srcTable' => 'select_table',
352 'varMap' => array( 'field_insert' => 'field_select', 'field' => 'field2' ),
353 'conds' => array( 'field' => 2 ),
354 'insertOptions' => 'IGNORE',
355 'selectOptions' => array( 'ORDER BY' => 'field' ),
357 "INSERT IGNORE INTO insert_table " .
358 "(field_insert,field) " .
359 "SELECT field_select,field2 " .
360 "FROM select_table " .
361 "WHERE field = '2' " .
368 * @dataProvider provideReplace
370 function testReplace( $sql, $sqlText ) {
371 $this->database
->replace(
373 $sql['uniqueIndexes'],
377 $this->assertLastSql( $sqlText );
380 public static function provideReplace() {
384 'table' => 'replace_table',
385 'uniqueIndexes' => array( 'field' ),
386 'rows' => array( 'field' => 'text', 'field2' => 'text2' ),
388 "DELETE FROM replace_table " .
389 "WHERE ( field='text' ); " .
390 "INSERT INTO replace_table " .
392 "VALUES ('text','text2')"
396 'table' => 'module_deps',
397 'uniqueIndexes' => array( array( 'md_module', 'md_skin' ) ),
399 'md_module' => 'module',
404 "DELETE FROM module_deps " .
405 "WHERE ( md_module='module' AND md_skin='skin' ); " .
406 "INSERT INTO module_deps " .
407 "(md_module,md_skin,md_deps) " .
408 "VALUES ('module','skin','deps')"
412 'table' => 'module_deps',
413 'uniqueIndexes' => array( array( 'md_module', 'md_skin' ) ),
416 'md_module' => 'module',
420 'md_module' => 'module2',
421 'md_skin' => 'skin2',
422 'md_deps' => 'deps2',
426 "DELETE FROM module_deps " .
427 "WHERE ( md_module='module' AND md_skin='skin' ); " .
428 "INSERT INTO module_deps " .
429 "(md_module,md_skin,md_deps) " .
430 "VALUES ('module','skin','deps'); " .
431 "DELETE FROM module_deps " .
432 "WHERE ( md_module='module2' AND md_skin='skin2' ); " .
433 "INSERT INTO module_deps " .
434 "(md_module,md_skin,md_deps) " .
435 "VALUES ('module2','skin2','deps2')"
439 'table' => 'module_deps',
440 'uniqueIndexes' => array( 'md_module', 'md_skin' ),
443 'md_module' => 'module',
447 'md_module' => 'module2',
448 'md_skin' => 'skin2',
449 'md_deps' => 'deps2',
453 "DELETE FROM module_deps " .
454 "WHERE ( md_module='module' ) OR ( md_skin='skin' ); " .
455 "INSERT INTO module_deps " .
456 "(md_module,md_skin,md_deps) " .
457 "VALUES ('module','skin','deps'); " .
458 "DELETE FROM module_deps " .
459 "WHERE ( md_module='module2' ) OR ( md_skin='skin2' ); " .
460 "INSERT INTO module_deps " .
461 "(md_module,md_skin,md_deps) " .
462 "VALUES ('module2','skin2','deps2')"
466 'table' => 'module_deps',
467 'uniqueIndexes' => array(),
469 'md_module' => 'module',
474 "INSERT INTO module_deps " .
475 "(md_module,md_skin,md_deps) " .
476 "VALUES ('module','skin','deps')"
482 * @dataProvider provideNativeReplace
484 function testNativeReplace( $sql, $sqlText ) {
485 $this->database
->nativeReplace(
490 $this->assertLastSql( $sqlText );
493 public static function provideNativeReplace() {
497 'table' => 'replace_table',
498 'rows' => array( 'field' => 'text', 'field2' => 'text2' ),
500 "REPLACE INTO replace_table " .
502 "VALUES ('text','text2')"
508 * @dataProvider provideConditional
510 function testConditional( $sql, $sqlText ) {
511 $this->assertEquals( trim( $this->database
->conditional(
518 public static function provideConditional() {
522 'conds' => array( 'field' => 'text' ),
526 "(CASE WHEN field = 'text' THEN 1 ELSE NULL END)"
530 'conds' => array( 'field' => 'text', 'field2' => 'anothertext' ),
534 "(CASE WHEN field = 'text' AND field2 = 'anothertext' THEN 1 ELSE NULL END)"
538 'conds' => 'field=1',
542 "(CASE WHEN field=1 THEN 1 ELSE NULL END)"
548 * @dataProvider provideBuildConcat
550 function testBuildConcat( $stringList, $sqlText ) {
551 $this->assertEquals( trim( $this->database
->buildConcat(
556 public static function provideBuildConcat() {
559 array( 'field', 'field2' ),
560 "CONCAT(field,field2)"
563 array( "'test'", 'field2' ),
564 "CONCAT('test',field2)"
570 * @dataProvider provideBuildLike
572 function testBuildLike( $array, $sqlText ) {
573 $this->assertEquals( trim( $this->database
->buildLike(
578 public static function provideBuildLike() {
585 array( 'text', new LikeMatch( '%' ) ),
589 array( 'text', new LikeMatch( '%' ), 'text2' ),
593 array( 'text', new LikeMatch( '_' ) ),
600 * @dataProvider provideUnionQueries
602 function testUnionQueries( $sql, $sqlText ) {
603 $this->assertEquals( trim( $this->database
->unionQueries(
609 public static function provideUnionQueries() {
613 'sqls' => array( 'RAW SQL', 'RAW2SQL' ),
616 "(RAW SQL) UNION ALL (RAW2SQL)"
620 'sqls' => array( 'RAW SQL', 'RAW2SQL' ),
623 "(RAW SQL) UNION (RAW2SQL)"
627 'sqls' => array( 'RAW SQL', 'RAW2SQL', 'RAW3SQL' ),
630 "(RAW SQL) UNION (RAW2SQL) UNION (RAW3SQL)"
635 function testTransactionCommit() {
636 $this->database
->begin( __METHOD__
);
637 $this->database
->commit( __METHOD__
);
638 $this->assertLastSql( 'BEGIN; COMMIT' );
641 function testTransactionRollback() {
642 $this->database
->begin( __METHOD__
);
643 $this->database
->rollback( __METHOD__
);
644 $this->assertLastSql( 'BEGIN; ROLLBACK' );
647 function testDropTable() {
648 $this->database
->setExistingTables( array( 'table' ) );
649 $this->database
->dropTable( 'table', __METHOD__
);
650 $this->assertLastSql( 'DROP TABLE table' );
653 function testDropNonExistingTable() {
655 $this->database
->dropTable( 'non_existing', __METHOD__
)