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