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