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