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