?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
rdbms: Rename "memCache" to "memStash" in LBFactory
[mediawiki.git] / tests / phpunit / includes / libs / rdbms / database / DatabaseSQLTest.php
blob7b8411729fc0c2b4c480ce3fcc9e3e2a7b0a1f6f
1 <?php
2
3 use Wikimedia\Rdbms\LikeMatch;
4
5 /**
6 * Test the parts of the Database abstract class that deal
7 * with creating SQL text.
8 */
9 class DatabaseSQLTest extends PHPUnit_Framework_TestCase {
10 /** @var DatabaseTestHelper */
11 private $database;
12
13 protected function setUp() {
14 parent::setUp();
15 $this->database = new DatabaseTestHelper( __CLASS__, [ 'cliMode' => true ] );
16 }
17
18 protected function assertLastSql( $sqlText ) {
19 $this->assertEquals(
20 $sqlText,
21 $this->database->getLastSqls()
22 );
23 }
24
25 protected function assertLastSqlDb( $sqlText, DatabaseTestHelper $db ) {
26 $this->assertEquals( $sqlText, $db->getLastSqls() );
27 }
28
29 /**
30 * @dataProvider provideSelect
31 * @covers Wikimedia\Rdbms\Database::select
32 * @covers Wikimedia\Rdbms\Database::selectSQLText
33 * @covers Wikimedia\Rdbms\Database::tableNamesWithIndexClauseOrJOIN
34 * @covers Wikimedia\Rdbms\Database::useIndexClause
35 * @covers Wikimedia\Rdbms\Database::ignoreIndexClause
36 * @covers Wikimedia\Rdbms\Database::makeSelectOptions
37 * @covers Wikimedia\Rdbms\Database::makeOrderBy
38 * @covers Wikimedia\Rdbms\Database::makeGroupByWithHaving
39 */
40 public function testSelect( $sql, $sqlText ) {
41 $this->database->select(
42 $sql['tables'],
43 $sql['fields'],
44 isset( $sql['conds'] ) ? $sql['conds'] : [],
45 __METHOD__,
46 isset( $sql['options'] ) ? $sql['options'] : [],
47 isset( $sql['join_conds'] ) ? $sql['join_conds'] : []
48 );
49 $this->assertLastSql( $sqlText );
50 }
51
52 public static function provideSelect() {
53 return [
54 [
55 [
56 'tables' => 'table',
57 'fields' => [ 'field', 'alias' => 'field2' ],
58 'conds' => [ 'alias' => 'text' ],
59 ],
60 "SELECT field,field2 AS alias " .
61 "FROM table " .
62 "WHERE alias = 'text'"
63 ],
64 [
65 [
66 // 'tables' with space prepended indicates pre-escaped table name
67 'tables' => ' table LEFT JOIN table2',
68 'fields' => [ 'field' ],
69 'conds' => [ 'field' => 'text' ],
70 ],
71 "SELECT field FROM table LEFT JOIN table2 WHERE field = 'text'"
72 ],
73 [
74 [
75 // Empty 'tables' is allowed
76 'tables' => '',
77 'fields' => [ 'SPECIAL_QUERY()' ],
78 ],
79 "SELECT SPECIAL_QUERY()"
80 ],
81 [
82 [
83 'tables' => 'table',
84 'fields' => [ 'field', 'alias' => 'field2' ],
85 'conds' => [ 'alias' => 'text' ],
86 'options' => [ 'LIMIT' => 1, 'ORDER BY' => 'field' ],
87 ],
88 "SELECT field,field2 AS alias " .
89 "FROM table " .
90 "WHERE alias = 'text' " .
91 "ORDER BY field " .
92 "LIMIT 1"
93 ],
94 [
95 [
96 'tables' => [ 'table', 't2' => 'table2' ],
97 'fields' => [ 'tid', 'field', 'alias' => 'field2', 't2.id' ],
98 'conds' => [ 'alias' => 'text' ],
99 'options' => [ 'LIMIT' => 1, 'ORDER BY' => 'field' ],
100 'join_conds' => [ 't2' => [
101 'LEFT JOIN', 'tid = t2.id'
102 ] ],
103 ],
104 "SELECT tid,field,field2 AS alias,t2.id " .
105 "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
106 "WHERE alias = 'text' " .
107 "ORDER BY field " .
108 "LIMIT 1"
109 ],
110 [
111 [
112 'tables' => [ 'table', 't2' => 'table2' ],
113 'fields' => [ 'tid', 'field', 'alias' => 'field2', 't2.id' ],
114 'conds' => [ 'alias' => 'text' ],
115 'options' => [ 'LIMIT' => 1, 'GROUP BY' => 'field', 'HAVING' => 'COUNT(*) > 1' ],
116 'join_conds' => [ 't2' => [
117 'LEFT JOIN', 'tid = t2.id'
118 ] ],
119 ],
120 "SELECT tid,field,field2 AS alias,t2.id " .
121 "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
122 "WHERE alias = 'text' " .
123 "GROUP BY field HAVING COUNT(*) > 1 " .
124 "LIMIT 1"
125 ],
126 [
127 [
128 'tables' => [ 'table', 't2' => 'table2' ],
129 'fields' => [ 'tid', 'field', 'alias' => 'field2', 't2.id' ],
130 'conds' => [ 'alias' => 'text' ],
131 'options' => [
132 'LIMIT' => 1,
133 'GROUP BY' => [ 'field', 'field2' ],
134 'HAVING' => [ 'COUNT(*) > 1', 'field' => 1 ]
135 ],
136 'join_conds' => [ 't2' => [
137 'LEFT JOIN', 'tid = t2.id'
138 ] ],
139 ],
140 "SELECT tid,field,field2 AS alias,t2.id " .
141 "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
142 "WHERE alias = 'text' " .
143 "GROUP BY field,field2 HAVING (COUNT(*) > 1) AND field = '1' " .
144 "LIMIT 1"
145 ],
146 [
147 [
148 'tables' => [ 'table' ],
149 'fields' => [ 'alias' => 'field' ],
150 'conds' => [ 'alias' => [ 1, 2, 3, 4 ] ],
151 ],
152 "SELECT field AS alias " .
153 "FROM table " .
154 "WHERE alias IN ('1','2','3','4')"
155 ],
156 [
157 [
158 'tables' => 'table',
159 'fields' => [ 'field' ],
160 'options' => [ 'USE INDEX' => [ 'table' => 'X' ] ],
161 ],
162 // No-op by default
163 "SELECT field FROM table"
164 ],
165 [
166 [
167 'tables' => 'table',
168 'fields' => [ 'field' ],
169 'options' => [ 'IGNORE INDEX' => [ 'table' => 'X' ] ],
170 ],
171 // No-op by default
172 "SELECT field FROM table"
173 ],
174 [
175 [
176 'tables' => 'table',
177 'fields' => [ 'field' ],
178 'options' => [ 'DISTINCT', 'LOCK IN SHARE MODE' ],
179 ],
180 "SELECT DISTINCT field FROM table LOCK IN SHARE MODE"
181 ],
182 [
183 [
184 'tables' => 'table',
185 'fields' => [ 'field' ],
186 'options' => [ 'EXPLAIN' => true ],
187 ],
188 'EXPLAIN SELECT field FROM table'
189 ],
190 [
191 [
192 'tables' => 'table',
193 'fields' => [ 'field' ],
194 'options' => [ 'FOR UPDATE' ],
195 ],
196 "SELECT field FROM table FOR UPDATE"
197 ],
198 ];
199 }
200
201 /**
202 * @dataProvider provideUpdate
203 * @covers Wikimedia\Rdbms\Database::update
204 * @covers Wikimedia\Rdbms\Database::makeUpdateOptions
205 * @covers Wikimedia\Rdbms\Database::makeUpdateOptionsArray
206 */
207 public function testUpdate( $sql, $sqlText ) {
208 $this->database->update(
209 $sql['table'],
210 $sql['values'],
211 $sql['conds'],
212 __METHOD__,
213 isset( $sql['options'] ) ? $sql['options'] : []
214 );
215 $this->assertLastSql( $sqlText );
216 }
217
218 public static function provideUpdate() {
219 return [
220 [
221 [
222 'table' => 'table',
223 'values' => [ 'field' => 'text', 'field2' => 'text2' ],
224 'conds' => [ 'alias' => 'text' ],
225 ],
226 "UPDATE table " .
227 "SET field = 'text'" .
228 ",field2 = 'text2' " .
229 "WHERE alias = 'text'"
230 ],
231 [
232 [
233 'table' => 'table',
234 'values' => [ 'field = other', 'field2' => 'text2' ],
235 'conds' => [ 'id' => '1' ],
236 ],
237 "UPDATE table " .
238 "SET field = other" .
239 ",field2 = 'text2' " .
240 "WHERE id = '1'"
241 ],
242 [
243 [
244 'table' => 'table',
245 'values' => [ 'field = other', 'field2' => 'text2' ],
246 'conds' => '*',
247 ],
248 "UPDATE table " .
249 "SET field = other" .
250 ",field2 = 'text2'"
251 ],
252 ];
253 }
254
255 /**
256 * @dataProvider provideDelete
257 * @covers Wikimedia\Rdbms\Database::delete
258 */
259 public function testDelete( $sql, $sqlText ) {
260 $this->database->delete(
261 $sql['table'],
262 $sql['conds'],
263 __METHOD__
264 );
265 $this->assertLastSql( $sqlText );
266 }
267
268 public static function provideDelete() {
269 return [
270 [
271 [
272 'table' => 'table',
273 'conds' => [ 'alias' => 'text' ],
274 ],
275 "DELETE FROM table " .
276 "WHERE alias = 'text'"
277 ],
278 [
279 [
280 'table' => 'table',
281 'conds' => '*',
282 ],
283 "DELETE FROM table"
284 ],
285 ];
286 }
287
288 /**
289 * @dataProvider provideUpsert
290 * @covers Wikimedia\Rdbms\Database::upsert
291 */
292 public function testUpsert( $sql, $sqlText ) {
293 $this->database->upsert(
294 $sql['table'],
295 $sql['rows'],
296 $sql['uniqueIndexes'],
297 $sql['set'],
298 __METHOD__
299 );
300 $this->assertLastSql( $sqlText );
301 }
302
303 public static function provideUpsert() {
304 return [
305 [
306 [
307 'table' => 'upsert_table',
308 'rows' => [ 'field' => 'text', 'field2' => 'text2' ],
309 'uniqueIndexes' => [ 'field' ],
310 'set' => [ 'field' => 'set' ],
311 ],
312 "BEGIN; " .
313 "UPDATE upsert_table " .
314 "SET field = 'set' " .
315 "WHERE ((field = 'text')); " .
316 "INSERT IGNORE INTO upsert_table " .
317 "(field,field2) " .
318 "VALUES ('text','text2'); " .
319 "COMMIT"
320 ],
321 ];
322 }
323
324 /**
325 * @dataProvider provideDeleteJoin
326 * @covers Wikimedia\Rdbms\Database::deleteJoin
327 */
328 public function testDeleteJoin( $sql, $sqlText ) {
329 $this->database->deleteJoin(
330 $sql['delTable'],
331 $sql['joinTable'],
332 $sql['delVar'],
333 $sql['joinVar'],
334 $sql['conds'],
335 __METHOD__
336 );
337 $this->assertLastSql( $sqlText );
338 }
339
340 public static function provideDeleteJoin() {
341 return [
342 [
343 [
344 'delTable' => 'table',
345 'joinTable' => 'table_join',
346 'delVar' => 'field',
347 'joinVar' => 'field_join',
348 'conds' => [ 'alias' => 'text' ],
349 ],
350 "DELETE FROM table " .
351 "WHERE field IN (" .
352 "SELECT field_join FROM table_join WHERE alias = 'text'" .
353 ")"
354 ],
355 [
356 [
357 'delTable' => 'table',
358 'joinTable' => 'table_join',
359 'delVar' => 'field',
360 'joinVar' => 'field_join',
361 'conds' => '*',
362 ],
363 "DELETE FROM table " .
364 "WHERE field IN (" .
365 "SELECT field_join FROM table_join " .
366 ")"
367 ],
368 ];
369 }
370
371 /**
372 * @dataProvider provideInsert
373 * @covers Wikimedia\Rdbms\Database::insert
374 * @covers Wikimedia\Rdbms\Database::makeInsertOptions
375 */
376 public function testInsert( $sql, $sqlText ) {
377 $this->database->insert(
378 $sql['table'],
379 $sql['rows'],
380 __METHOD__,
381 isset( $sql['options'] ) ? $sql['options'] : []
382 );
383 $this->assertLastSql( $sqlText );
384 }
385
386 public static function provideInsert() {
387 return [
388 [
389 [
390 'table' => 'table',
391 'rows' => [ 'field' => 'text', 'field2' => 2 ],
392 ],
393 "INSERT INTO table " .
394 "(field,field2) " .
395 "VALUES ('text','2')"
396 ],
397 [
398 [
399 'table' => 'table',
400 'rows' => [ 'field' => 'text', 'field2' => 2 ],
401 'options' => 'IGNORE',
402 ],
403 "INSERT IGNORE INTO table " .
404 "(field,field2) " .
405 "VALUES ('text','2')"
406 ],
407 [
408 [
409 'table' => 'table',
410 'rows' => [
411 [ 'field' => 'text', 'field2' => 2 ],
412 [ 'field' => 'multi', 'field2' => 3 ],
413 ],
414 'options' => 'IGNORE',
415 ],
416 "INSERT IGNORE INTO table " .
417 "(field,field2) " .
418 "VALUES " .
419 "('text','2')," .
420 "('multi','3')"
421 ],
422 ];
423 }
424
425 /**
426 * @dataProvider provideInsertSelect
427 * @covers Wikimedia\Rdbms\Database::insertSelect
428 * @covers Wikimedia\Rdbms\Database::nativeInsertSelect
429 */
430 public function testInsertSelect( $sql, $sqlTextNative, $sqlSelect, $sqlInsert ) {
431 $this->database->insertSelect(
432 $sql['destTable'],
433 $sql['srcTable'],
434 $sql['varMap'],
435 $sql['conds'],
436 __METHOD__,
437 isset( $sql['insertOptions'] ) ? $sql['insertOptions'] : [],
438 isset( $sql['selectOptions'] ) ? $sql['selectOptions'] : [],
439 isset( $sql['selectJoinConds'] ) ? $sql['selectJoinConds'] : []
440 );
441 $this->assertLastSql( $sqlTextNative );
442
443 $dbWeb = new DatabaseTestHelper( __CLASS__, [ 'cliMode' => false ] );
444 $dbWeb->forceNextResult( [
445 array_flip( array_keys( $sql['varMap'] ) )
446 ] );
447 $dbWeb->insertSelect(
448 $sql['destTable'],
449 $sql['srcTable'],
450 $sql['varMap'],
451 $sql['conds'],
452 __METHOD__,
453 isset( $sql['insertOptions'] ) ? $sql['insertOptions'] : [],
454 isset( $sql['selectOptions'] ) ? $sql['selectOptions'] : [],
455 isset( $sql['selectJoinConds'] ) ? $sql['selectJoinConds'] : []
456 );
457 $this->assertLastSqlDb( implode( '; ', [ $sqlSelect, $sqlInsert ] ), $dbWeb );
458 }
459
460 public static function provideInsertSelect() {
461 return [
462 [
463 [
464 'destTable' => 'insert_table',
465 'srcTable' => 'select_table',
466 'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
467 'conds' => '*',
468 ],
469 "INSERT INTO insert_table " .
470 "(field_insert,field) " .
471 "SELECT field_select,field2 " .
472 "FROM select_table WHERE *",
473 "SELECT field_select AS field_insert,field2 AS field " .
474 "FROM select_table WHERE * FOR UPDATE",
475 "INSERT INTO insert_table (field_insert,field) VALUES ('0','1')"
476 ],
477 [
478 [
479 'destTable' => 'insert_table',
480 'srcTable' => 'select_table',
481 'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
482 'conds' => [ 'field' => 2 ],
483 ],
484 "INSERT INTO insert_table " .
485 "(field_insert,field) " .
486 "SELECT field_select,field2 " .
487 "FROM select_table " .
488 "WHERE field = '2'",
489 "SELECT field_select AS field_insert,field2 AS field FROM " .
490 "select_table WHERE field = '2' FOR UPDATE",
491 "INSERT INTO insert_table (field_insert,field) VALUES ('0','1')"
492 ],
493 [
494 [
495 'destTable' => 'insert_table',
496 'srcTable' => 'select_table',
497 'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
498 'conds' => [ 'field' => 2 ],
499 'insertOptions' => 'IGNORE',
500 'selectOptions' => [ 'ORDER BY' => 'field' ],
501 ],
502 "INSERT IGNORE INTO insert_table " .
503 "(field_insert,field) " .
504 "SELECT field_select,field2 " .
505 "FROM select_table " .
506 "WHERE field = '2' " .
507 "ORDER BY field",
508 "SELECT field_select AS field_insert,field2 AS field " .
509 "FROM select_table WHERE field = '2' ORDER BY field FOR UPDATE",
510 "INSERT IGNORE INTO insert_table (field_insert,field) VALUES ('0','1')"
511 ],
512 [
513 [
514 'destTable' => 'insert_table',
515 'srcTable' => [ 'select_table1', 'select_table2' ],
516 'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
517 'conds' => [ 'field' => 2 ],
518 'selectOptions' => [ 'ORDER BY' => 'field', 'FORCE INDEX' => [ 'select_table1' => 'index1' ] ],
519 'selectJoinConds' => [
520 'select_table2' => [ 'LEFT JOIN', [ 'select_table1.foo = select_table2.bar' ] ],
521 ],
522 ],
523 "INSERT INTO insert_table " .
524 "(field_insert,field) " .
525 "SELECT field_select,field2 " .
526 "FROM select_table1 LEFT JOIN select_table2 ON ((select_table1.foo = select_table2.bar)) " .
527 "WHERE field = '2' " .
528 "ORDER BY field",
529 "SELECT field_select AS field_insert,field2 AS field " .
530 "FROM select_table1 LEFT JOIN select_table2 ON ((select_table1.foo = select_table2.bar)) " .
531 "WHERE field = '2' ORDER BY field FOR UPDATE",
532 "INSERT INTO insert_table (field_insert,field) VALUES ('0','1')"
533 ],
534 ];
535 }
536
537 /**
538 * @dataProvider provideReplace
539 * @covers Wikimedia\Rdbms\Database::replace
540 */
541 public function testReplace( $sql, $sqlText ) {
542 $this->database->replace(
543 $sql['table'],
544 $sql['uniqueIndexes'],
545 $sql['rows'],
546 __METHOD__
547 );
548 $this->assertLastSql( $sqlText );
549 }
550
551 public static function provideReplace() {
552 return [
553 [
554 [
555 'table' => 'replace_table',
556 'uniqueIndexes' => [ 'field' ],
557 'rows' => [ 'field' => 'text', 'field2' => 'text2' ],
558 ],
559 "DELETE FROM replace_table " .
560 "WHERE ( field='text' ); " .
561 "INSERT INTO replace_table " .
562 "(field,field2) " .
563 "VALUES ('text','text2')"
564 ],
565 [
566 [
567 'table' => 'module_deps',
568 'uniqueIndexes' => [ [ 'md_module', 'md_skin' ] ],
569 'rows' => [
570 'md_module' => 'module',
571 'md_skin' => 'skin',
572 'md_deps' => 'deps',
573 ],
574 ],
575 "DELETE FROM module_deps " .
576 "WHERE ( md_module='module' AND md_skin='skin' ); " .
577 "INSERT INTO module_deps " .
578 "(md_module,md_skin,md_deps) " .
579 "VALUES ('module','skin','deps')"
580 ],
581 [
582 [
583 'table' => 'module_deps',
584 'uniqueIndexes' => [ [ 'md_module', 'md_skin' ] ],
585 'rows' => [
586 [
587 'md_module' => 'module',
588 'md_skin' => 'skin',
589 'md_deps' => 'deps',
590 ], [
591 'md_module' => 'module2',
592 'md_skin' => 'skin2',
593 'md_deps' => 'deps2',
594 ],
595 ],
596 ],
597 "DELETE FROM module_deps " .
598 "WHERE ( md_module='module' AND md_skin='skin' ); " .
599 "INSERT INTO module_deps " .
600 "(md_module,md_skin,md_deps) " .
601 "VALUES ('module','skin','deps'); " .
602 "DELETE FROM module_deps " .
603 "WHERE ( md_module='module2' AND md_skin='skin2' ); " .
604 "INSERT INTO module_deps " .
605 "(md_module,md_skin,md_deps) " .
606 "VALUES ('module2','skin2','deps2')"
607 ],
608 [
609 [
610 'table' => 'module_deps',
611 'uniqueIndexes' => [ 'md_module', 'md_skin' ],
612 'rows' => [
613 [
614 'md_module' => 'module',
615 'md_skin' => 'skin',
616 'md_deps' => 'deps',
617 ], [
618 'md_module' => 'module2',
619 'md_skin' => 'skin2',
620 'md_deps' => 'deps2',
621 ],
622 ],
623 ],
624 "DELETE FROM module_deps " .
625 "WHERE ( md_module='module' ) OR ( 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' ) OR ( md_skin='skin2' ); " .
631 "INSERT INTO module_deps " .
632 "(md_module,md_skin,md_deps) " .
633 "VALUES ('module2','skin2','deps2')"
634 ],
635 [
636 [
637 'table' => 'module_deps',
638 'uniqueIndexes' => [],
639 'rows' => [
640 'md_module' => 'module',
641 'md_skin' => 'skin',
642 'md_deps' => 'deps',
643 ],
644 ],
645 "INSERT INTO module_deps " .
646 "(md_module,md_skin,md_deps) " .
647 "VALUES ('module','skin','deps')"
648 ],
649 ];
650 }
651
652 /**
653 * @dataProvider provideNativeReplace
654 * @covers Wikimedia\Rdbms\Database::nativeReplace
655 */
656 public function testNativeReplace( $sql, $sqlText ) {
657 $this->database->nativeReplace(
658 $sql['table'],
659 $sql['rows'],
660 __METHOD__
661 );
662 $this->assertLastSql( $sqlText );
663 }
664
665 public static function provideNativeReplace() {
666 return [
667 [
668 [
669 'table' => 'replace_table',
670 'rows' => [ 'field' => 'text', 'field2' => 'text2' ],
671 ],
672 "REPLACE INTO replace_table " .
673 "(field,field2) " .
674 "VALUES ('text','text2')"
675 ],
676 ];
677 }
678
679 /**
680 * @dataProvider provideConditional
681 * @covers Wikimedia\Rdbms\Database::conditional
682 */
683 public function testConditional( $sql, $sqlText ) {
684 $this->assertEquals( trim( $this->database->conditional(
685 $sql['conds'],
686 $sql['true'],
687 $sql['false']
688 ) ), $sqlText );
689 }
690
691 public static function provideConditional() {
692 return [
693 [
694 [
695 'conds' => [ 'field' => 'text' ],
696 'true' => 1,
697 'false' => 'NULL',
698 ],
699 "(CASE WHEN field = 'text' THEN 1 ELSE NULL END)"
700 ],
701 [
702 [
703 'conds' => [ 'field' => 'text', 'field2' => 'anothertext' ],
704 'true' => 1,
705 'false' => 'NULL',
706 ],
707 "(CASE WHEN field = 'text' AND field2 = 'anothertext' THEN 1 ELSE NULL END)"
708 ],
709 [
710 [
711 'conds' => 'field=1',
712 'true' => 1,
713 'false' => 'NULL',
714 ],
715 "(CASE WHEN field=1 THEN 1 ELSE NULL END)"
716 ],
717 ];
718 }
719
720 /**
721 * @dataProvider provideBuildConcat
722 * @covers Wikimedia\Rdbms\Database::buildConcat
723 */
724 public function testBuildConcat( $stringList, $sqlText ) {
725 $this->assertEquals( trim( $this->database->buildConcat(
726 $stringList
727 ) ), $sqlText );
728 }
729
730 public static function provideBuildConcat() {
731 return [
732 [
733 [ 'field', 'field2' ],
734 "CONCAT(field,field2)"
735 ],
736 [
737 [ "'test'", 'field2' ],
738 "CONCAT('test',field2)"
739 ],
740 ];
741 }
742
743 /**
744 * @dataProvider provideBuildLike
745 * @covers Wikimedia\Rdbms\Database::buildLike
746 * @covers Wikimedia\Rdbms\Database::escapeLikeInternal
747 */
748 public function testBuildLike( $array, $sqlText ) {
749 $this->assertEquals( trim( $this->database->buildLike(
750 $array
751 ) ), $sqlText );
752 }
753
754 public static function provideBuildLike() {
755 return [
756 [
757 'text',
758 "LIKE 'text' ESCAPE '`'"
759 ],
760 [
761 [ 'text', new LikeMatch( '%' ) ],
762 "LIKE 'text%' ESCAPE '`'"
763 ],
764 [
765 [ 'text', new LikeMatch( '%' ), 'text2' ],
766 "LIKE 'text%text2' ESCAPE '`'"
767 ],
768 [
769 [ 'text', new LikeMatch( '_' ) ],
770 "LIKE 'text_' ESCAPE '`'"
771 ],
772 [
773 'more_text',
774 "LIKE 'more`_text' ESCAPE '`'"
775 ],
776 [
777 [ 'C:\\Windows\\', new LikeMatch( '%' ) ],
778 "LIKE 'C:\\Windows\\%' ESCAPE '`'"
779 ],
780 [
781 [ 'accent`_test`', new LikeMatch( '%' ) ],
782 "LIKE 'accent```_test``%' ESCAPE '`'"
783 ],
784 ];
785 }
786
787 /**
788 * @dataProvider provideUnionQueries
789 * @covers Wikimedia\Rdbms\Database::unionQueries
790 */
791 public function testUnionQueries( $sql, $sqlText ) {
792 $this->assertEquals( trim( $this->database->unionQueries(
793 $sql['sqls'],
794 $sql['all']
795 ) ), $sqlText );
796 }
797
798 public static function provideUnionQueries() {
799 return [
800 [
801 [
802 'sqls' => [ 'RAW SQL', 'RAW2SQL' ],
803 'all' => true,
804 ],
805 "(RAW SQL) UNION ALL (RAW2SQL)"
806 ],
807 [
808 [
809 'sqls' => [ 'RAW SQL', 'RAW2SQL' ],
810 'all' => false,
811 ],
812 "(RAW SQL) UNION (RAW2SQL)"
813 ],
814 [
815 [
816 'sqls' => [ 'RAW SQL', 'RAW2SQL', 'RAW3SQL' ],
817 'all' => false,
818 ],
819 "(RAW SQL) UNION (RAW2SQL) UNION (RAW3SQL)"
820 ],
821 ];
822 }
823
824 /**
825 * @dataProvider provideUnionConditionPermutations
826 * @covers Wikimedia\Rdbms\Database::unionConditionPermutations
827 */
828 public function testUnionConditionPermutations( $params, $expect ) {
829 if ( isset( $params['unionSupportsOrderAndLimit'] ) ) {
830 $this->database->setUnionSupportsOrderAndLimit( $params['unionSupportsOrderAndLimit'] );
831 }
832
833 $sql = trim( $this->database->unionConditionPermutations(
834 $params['table'],
835 $params['vars'],
836 $params['permute_conds'],
837 isset( $params['extra_conds'] ) ? $params['extra_conds'] : '',
838 'FNAME',
839 isset( $params['options'] ) ? $params['options'] : [],
840 isset( $params['join_conds'] ) ? $params['join_conds'] : []
841 ) );
842 $this->assertEquals( $expect, $sql );
843 }
844
845 public static function provideUnionConditionPermutations() {
846 return [
847 // @codingStandardsIgnoreStart Generic.Files.LineLength.TooLong
848 [
849 [
850 'table' => [ 'table1', 'table2' ],
851 'vars' => [ 'field1', 'alias' => 'field2' ],
852 'permute_conds' => [
853 'field3' => [ 1, 2, 3 ],
854 'duplicates' => [ 4, 5, 4 ],
855 'empty' => [],
856 'single' => [ 0 ],
857 ],
858 'extra_conds' => 'table2.bar > 23',
859 'options' => [
860 'ORDER BY' => [ 'field1', 'alias' ],
861 'INNER ORDER BY' => [ 'field1', 'field2' ],
862 'LIMIT' => 100,
863 ],
864 'join_conds' => [
865 'table2' => [ 'JOIN', 'table1.foo_id = table2.foo_id' ],
866 ],
867 ],
868 "(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 " .
869 "(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 " .
870 "(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 " .
871 "(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 " .
872 "(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 " .
873 "(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 ) " .
874 "ORDER BY field1,alias LIMIT 100"
875 ],
876 [
877 [
878 'table' => 'foo',
879 'vars' => [ 'foo_id' ],
880 'permute_conds' => [
881 'bar' => [ 1, 2, 3 ],
882 ],
883 'extra_conds' => [ 'baz' => null ],
884 'options' => [
885 'NOTALL',
886 'ORDER BY' => [ 'foo_id' ],
887 'LIMIT' => 25,
888 ],
889 ],
890 "(SELECT foo_id FROM foo WHERE bar = '1' AND baz IS NULL ORDER BY foo_id LIMIT 25 ) UNION " .
891 "(SELECT foo_id FROM foo WHERE bar = '2' AND baz IS NULL ORDER BY foo_id LIMIT 25 ) UNION " .
892 "(SELECT foo_id FROM foo WHERE bar = '3' AND baz IS NULL ORDER BY foo_id LIMIT 25 ) " .
893 "ORDER BY foo_id LIMIT 25"
894 ],
895 [
896 [
897 'table' => 'foo',
898 'vars' => [ 'foo_id' ],
899 'permute_conds' => [
900 'bar' => [ 1, 2, 3 ],
901 ],
902 'extra_conds' => [ 'baz' => null ],
903 'options' => [
904 'NOTALL' => true,
905 'ORDER BY' => [ 'foo_id' ],
906 'LIMIT' => 25,
907 ],
908 'unionSupportsOrderAndLimit' => false,
909 ],
910 "(SELECT foo_id FROM foo WHERE bar = '1' AND baz IS NULL ) UNION " .
911 "(SELECT foo_id FROM foo WHERE bar = '2' AND baz IS NULL ) UNION " .
912 "(SELECT foo_id FROM foo WHERE bar = '3' AND baz IS NULL ) " .
913 "ORDER BY foo_id LIMIT 25"
914 ],
915 [
916 [
917 'table' => 'foo',
918 'vars' => [ 'foo_id' ],
919 'permute_conds' => [],
920 'extra_conds' => [ 'baz' => null ],
921 'options' => [
922 'ORDER BY' => [ 'foo_id' ],
923 'LIMIT' => 25,
924 ],
925 ],
926 "SELECT foo_id FROM foo WHERE baz IS NULL ORDER BY foo_id LIMIT 25"
927 ],
928 [
929 [
930 'table' => 'foo',
931 'vars' => [ 'foo_id' ],
932 'permute_conds' => [
933 'bar' => [],
934 ],
935 'extra_conds' => [ 'baz' => null ],
936 'options' => [
937 'ORDER BY' => [ 'foo_id' ],
938 'LIMIT' => 25,
939 ],
940 ],
941 "SELECT foo_id FROM foo WHERE baz IS NULL ORDER BY foo_id LIMIT 25"
942 ],
943 [
944 [
945 'table' => 'foo',
946 'vars' => [ 'foo_id' ],
947 'permute_conds' => [
948 'bar' => [ 1 ],
949 ],
950 'options' => [
951 'ORDER BY' => [ 'foo_id' ],
952 'LIMIT' => 25,
953 'OFFSET' => 150,
954 ],
955 ],
956 "SELECT foo_id FROM foo WHERE bar = '1' ORDER BY foo_id LIMIT 150,25"
957 ],
958 [
959 [
960 'table' => 'foo',
961 'vars' => [ 'foo_id' ],
962 'permute_conds' => [],
963 'extra_conds' => [ 'baz' => null ],
964 'options' => [
965 'ORDER BY' => [ 'foo_id' ],
966 'LIMIT' => 25,
967 'OFFSET' => 150,
968 'INNER ORDER BY' => [ 'bar_id' ],
969 ],
970 ],
971 "(SELECT foo_id FROM foo WHERE baz IS NULL ORDER BY bar_id LIMIT 175 ) ORDER BY foo_id LIMIT 150,25"
972 ],
973 [
974 [
975 'table' => 'foo',
976 'vars' => [ 'foo_id' ],
977 'permute_conds' => [],
978 'extra_conds' => [ 'baz' => null ],
979 'options' => [
980 'ORDER BY' => [ 'foo_id' ],
981 'LIMIT' => 25,
982 'OFFSET' => 150,
983 'INNER ORDER BY' => [ 'bar_id' ],
984 ],
985 'unionSupportsOrderAndLimit' => false,
986 ],
987 "SELECT foo_id FROM foo WHERE baz IS NULL ORDER BY foo_id LIMIT 150,25"
988 ],
989 // @codingStandardsIgnoreEnd
990 ];
991 }
992
993 /**
994 * @covers Wikimedia\Rdbms\Database::commit
995 * @covers Wikimedia\Rdbms\Database::doCommit
996 */
997 public function testTransactionCommit() {
998 $this->database->begin( __METHOD__ );
999 $this->database->commit( __METHOD__ );
1000 $this->assertLastSql( 'BEGIN; COMMIT' );
1001 }
1002
1003 /**
1004 * @covers Wikimedia\Rdbms\Database::rollback
1005 * @covers Wikimedia\Rdbms\Database::doRollback
1006 */
1007 public function testTransactionRollback() {
1008 $this->database->begin( __METHOD__ );
1009 $this->database->rollback( __METHOD__ );
1010 $this->assertLastSql( 'BEGIN; ROLLBACK' );
1011 }
1012
1013 /**
1014 * @covers Wikimedia\Rdbms\Database::dropTable
1015 */
1016 public function testDropTable() {
1017 $this->database->setExistingTables( [ 'table' ] );
1018 $this->database->dropTable( 'table', __METHOD__ );
1019 $this->assertLastSql( 'DROP TABLE table CASCADE' );
1020 }
1021
1022 /**
1023 * @covers Wikimedia\Rdbms\Database::dropTable
1024 */
1025 public function testDropNonExistingTable() {
1026 $this->assertFalse(
1027 $this->database->dropTable( 'non_existing', __METHOD__ )
1028 );
1029 }
1030
1031 /**
1032 * @dataProvider provideMakeList
1033 * @covers Wikimedia\Rdbms\Database::makeList
1034 */
1035 public function testMakeList( $list, $mode, $sqlText ) {
1036 $this->assertEquals( trim( $this->database->makeList(
1037 $list, $mode
1038 ) ), $sqlText );
1039 }
1040
1041 public static function provideMakeList() {
1042 return [
1043 [
1044 [ 'value', 'value2' ],
1045 LIST_COMMA,
1046 "'value','value2'"
1047 ],
1048 [
1049 [ 'field', 'field2' ],
1050 LIST_NAMES,
1051 "field,field2"
1052 ],
1053 [
1054 [ 'field' => 'value', 'field2' => 'value2' ],
1055 LIST_AND,
1056 "field = 'value' AND field2 = 'value2'"
1057 ],
1058 [
1059 [ 'field' => null, "field2 != 'value2'" ],
1060 LIST_AND,
1061 "field IS NULL AND (field2 != 'value2')"
1062 ],
1063 [
1064 [ 'field' => [ 'value', null, 'value2' ], 'field2' => 'value2' ],
1065 LIST_AND,
1066 "(field IN ('value','value2') OR field IS NULL) AND field2 = 'value2'"
1067 ],
1068 [
1069 [ 'field' => [ null ], 'field2' => null ],
1070 LIST_AND,
1071 "field IS NULL AND field2 IS NULL"
1072 ],
1073 [
1074 [ 'field' => 'value', 'field2' => 'value2' ],
1075 LIST_OR,
1076 "field = 'value' OR field2 = 'value2'"
1077 ],
1078 [
1079 [ 'field' => 'value', 'field2' => null ],
1080 LIST_OR,
1081 "field = 'value' OR field2 IS NULL"
1082 ],
1083 [
1084 [ 'field' => [ 'value', 'value2' ], 'field2' => [ 'value' ] ],
1085 LIST_OR,
1086 "field IN ('value','value2') OR field2 = 'value'"
1087 ],
1088 [
1089 [ 'field' => [ null, 'value', null, 'value2' ], "field2 != 'value2'" ],
1090 LIST_OR,
1091 "(field IN ('value','value2') OR field IS NULL) OR (field2 != 'value2')"
1092 ],
1093 [
1094 [ 'field' => 'value', 'field2' => 'value2' ],
1095 LIST_SET,
1096 "field = 'value',field2 = 'value2'"
1097 ],
1098 [
1099 [ 'field' => 'value', 'field2' => null ],
1100 LIST_SET,
1101 "field = 'value',field2 = NULL"
1102 ],
1103 [
1104 [ 'field' => 'value', "field2 != 'value2'" ],
1105 LIST_SET,
1106 "field = 'value',field2 != 'value2'"
1107 ],
1108 ];
1109 }
1110
1111 /**
1112 * @covers Wikimedia\Rdbms\Database::registerTempTableOperation
1113 */
1114 public function testSessionTempTables() {
1115 $temp1 = $this->database->tableName( 'tmp_table_1' );
1116 $temp2 = $this->database->tableName( 'tmp_table_2' );
1117 $temp3 = $this->database->tableName( 'tmp_table_3' );
1118
1119 $this->database->query( "CREATE TEMPORARY TABLE $temp1 LIKE orig_tbl", __METHOD__ );
1120 $this->database->query( "CREATE TEMPORARY TABLE $temp2 LIKE orig_tbl", __METHOD__ );
1121 $this->database->query( "CREATE TEMPORARY TABLE $temp3 LIKE orig_tbl", __METHOD__ );
1122
1123 $this->assertTrue( $this->database->tableExists( "tmp_table_1", __METHOD__ ) );
1124 $this->assertTrue( $this->database->tableExists( "tmp_table_2", __METHOD__ ) );
1125 $this->assertTrue( $this->database->tableExists( "tmp_table_3", __METHOD__ ) );
1126
1127 $this->database->dropTable( 'tmp_table_1', __METHOD__ );
1128 $this->database->dropTable( 'tmp_table_2', __METHOD__ );
1129 $this->database->dropTable( 'tmp_table_3', __METHOD__ );
1130
1131 $this->assertFalse( $this->database->tableExists( "tmp_table_1", __METHOD__ ) );
1132 $this->assertFalse( $this->database->tableExists( "tmp_table_2", __METHOD__ ) );
1133 $this->assertFalse( $this->database->tableExists( "tmp_table_3", __METHOD__ ) );
1134
1135 $this->database->query( "CREATE TEMPORARY TABLE tmp_table_1 LIKE orig_tbl", __METHOD__ );
1136 $this->database->query( "CREATE TEMPORARY TABLE 'tmp_table_2' LIKE orig_tbl", __METHOD__ );
1137 $this->database->query( "CREATE TEMPORARY TABLE `tmp_table_3` LIKE orig_tbl", __METHOD__ );
1138
1139 $this->assertTrue( $this->database->tableExists( "tmp_table_1", __METHOD__ ) );
1140 $this->assertTrue( $this->database->tableExists( "tmp_table_2", __METHOD__ ) );
1141 $this->assertTrue( $this->database->tableExists( "tmp_table_3", __METHOD__ ) );
1142
1143 $this->database->query( "DROP TEMPORARY TABLE tmp_table_1 LIKE orig_tbl", __METHOD__ );
1144 $this->database->query( "DROP TEMPORARY TABLE 'tmp_table_2' LIKE orig_tbl", __METHOD__ );
1145 $this->database->query( "DROP TABLE `tmp_table_3` LIKE orig_tbl", __METHOD__ );
1146
1147 $this->assertFalse( $this->database->tableExists( "tmp_table_1", __METHOD__ ) );
1148 $this->assertFalse( $this->database->tableExists( "tmp_table_2", __METHOD__ ) );
1149 $this->assertFalse( $this->database->tableExists( "tmp_table_3", __METHOD__ ) );
1150 }
1151 }
MediaWiki Core