tests/phpunit/includes/db/DatabaseSQLTest.php

   1 <?php
   2
   3 use Wikimedia\Rdbms\LikeMatch;
   4
   5 /**
   6  * Test the abstract database layer
   7  * This is a non DBMS depending test.
   8  */
   9 class DatabaseSQLTest extends MediaWikiTestCase {
  10         /** @var DatabaseTestHelper */
  11         private $database;
  12
  13         protected function setUp() {
  14                 parent::setUp();
  15                 $this->database = new DatabaseTestHelper( __CLASS__, [ 'cliMode' => true ] );
  16         }
  17
  18         protected function assertLastSql( $sqlText ) {
  19                 $this->assertEquals(
  20                         $this->database->getLastSqls(),
  21                         $sqlText
  22                 );
  23         }
  24
  25         protected function assertLastSqlDb( $sqlText, $db ) {
  26                 $this->assertEquals( $db->getLastSqls(), $sqlText );
  27         }
  28
  29         /**
  30          * @dataProvider provideSelect
  31          * @covers Database::select
  32          */
  33         public function testSelect( $sql, $sqlText ) {
  34                 $this->database->select(
  35                         $sql['tables'],
  36                         $sql['fields'],
  37                         isset( $sql['conds'] ) ? $sql['conds'] : [],
  38                         __METHOD__,
  39                         isset( $sql['options'] ) ? $sql['options'] : [],
  40                         isset( $sql['join_conds'] ) ? $sql['join_conds'] : []
  41                 );
  42                 $this->assertLastSql( $sqlText );
  43         }
  44
  45         public static function provideSelect() {
  46                 return [
  47                         [
  48                                 [
  49                                         'tables' => 'table',
  50                                         'fields' => [ 'field', 'alias' => 'field2' ],
  51                                         'conds' => [ 'alias' => 'text' ],
  52                                 ],
  53                                 "SELECT field,field2 AS alias " .
  54                                         "FROM table " .
  55                                         "WHERE alias = 'text'"
  56                         ],
  57                         [
  58                                 [
  59                                         'tables' => 'table',
  60                                         'fields' => [ 'field', 'alias' => 'field2' ],
  61                                         'conds' => [ 'alias' => 'text' ],
  62                                         'options' => [ 'LIMIT' => 1, 'ORDER BY' => 'field' ],
  63                                 ],
  64                                 "SELECT field,field2 AS alias " .
  65                                         "FROM table " .
  66                                         "WHERE alias = 'text' " .
  67                                         "ORDER BY field " .
  68                                         "LIMIT 1"
  69                         ],
  70                         [
  71                                 [
  72                                         'tables' => [ 'table', 't2' => 'table2' ],
  73                                         'fields' => [ 'tid', 'field', 'alias' => 'field2', 't2.id' ],
  74                                         'conds' => [ 'alias' => 'text' ],
  75                                         'options' => [ 'LIMIT' => 1, 'ORDER BY' => 'field' ],
  76                                         'join_conds' => [ 't2' => [
  77                                                 'LEFT JOIN', 'tid = t2.id'
  78                                         ] ],
  79                                 ],
  80                                 "SELECT tid,field,field2 AS alias,t2.id " .
  81                                         "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
  82                                         "WHERE alias = 'text' " .
  83                                         "ORDER BY field " .
  84                                         "LIMIT 1"
  85                         ],
  86                         [
  87                                 [
  88                                         'tables' => [ 'table', 't2' => 'table2' ],
  89                                         'fields' => [ 'tid', 'field', 'alias' => 'field2', 't2.id' ],
  90                                         'conds' => [ 'alias' => 'text' ],
  91                                         'options' => [ 'LIMIT' => 1, 'GROUP BY' => 'field', 'HAVING' => 'COUNT(*) > 1' ],
  92                                         'join_conds' => [ 't2' => [
  93                                                 'LEFT JOIN', 'tid = t2.id'
  94                                         ] ],
  95                                 ],
  96                                 "SELECT tid,field,field2 AS alias,t2.id " .
  97                                         "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
  98                                         "WHERE alias = 'text' " .
  99                                         "GROUP BY field HAVING COUNT(*) > 1 " .
 100                                         "LIMIT 1"
 101                         ],
 102                         [
 103                                 [
 104                                         'tables' => [ 'table', 't2' => 'table2' ],
 105                                         'fields' => [ 'tid', 'field', 'alias' => 'field2', 't2.id' ],
 106                                         'conds' => [ 'alias' => 'text' ],
 107                                         'options' => [
 108                                                 'LIMIT' => 1,
 109                                                 'GROUP BY' => [ 'field', 'field2' ],
 110                                                 'HAVING' => [ 'COUNT(*) > 1', 'field' => 1 ]
 111                                         ],
 112                                         'join_conds' => [ 't2' => [
 113                                                 'LEFT JOIN', 'tid = t2.id'
 114                                         ] ],
 115                                 ],
 116                                 "SELECT tid,field,field2 AS alias,t2.id " .
 117                                         "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
 118                                         "WHERE alias = 'text' " .
 119                                         "GROUP BY field,field2 HAVING (COUNT(*) > 1) AND field = '1' " .
 120                                         "LIMIT 1"
 121                         ],
 122                         [
 123                                 [
 124                                         'tables' => [ 'table' ],
 125                                         'fields' => [ 'alias' => 'field' ],
 126                                         'conds' => [ 'alias' => [ 1, 2, 3, 4 ] ],
 127                                 ],
 128                                 "SELECT field AS alias " .
 129                                         "FROM table " .
 130                                         "WHERE alias IN ('1','2','3','4')"
 131                         ],
 132                 ];
 133         }
 134
 135         /**
 136          * @dataProvider provideUpdate
 137          * @covers Database::update
 138          */
 139         public function testUpdate( $sql, $sqlText ) {
 140                 $this->database->update(
 141                         $sql['table'],
 142                         $sql['values'],
 143                         $sql['conds'],
 144                         __METHOD__,
 145                         isset( $sql['options'] ) ? $sql['options'] : []
 146                 );
 147                 $this->assertLastSql( $sqlText );
 148         }
 149
 150         public static function provideUpdate() {
 151                 return [
 152                         [
 153                                 [
 154                                         'table' => 'table',
 155                                         'values' => [ 'field' => 'text', 'field2' => 'text2' ],
 156                                         'conds' => [ 'alias' => 'text' ],
 157                                 ],
 158                                 "UPDATE table " .
 159                                         "SET field = 'text'" .
 160                                         ",field2 = 'text2' " .
 161                                         "WHERE alias = 'text'"
 162                         ],
 163                         [
 164                                 [
 165                                         'table' => 'table',
 166                                         'values' => [ 'field = other', 'field2' => 'text2' ],
 167                                         'conds' => [ 'id' => '1' ],
 168                                 ],
 169                                 "UPDATE table " .
 170                                         "SET field = other" .
 171                                         ",field2 = 'text2' " .
 172                                         "WHERE id = '1'"
 173                         ],
 174                         [
 175                                 [
 176                                         'table' => 'table',
 177                                         'values' => [ 'field = other', 'field2' => 'text2' ],
 178                                         'conds' => '*',
 179                                 ],
 180                                 "UPDATE table " .
 181                                         "SET field = other" .
 182                                         ",field2 = 'text2'"
 183                         ],
 184                 ];
 185         }
 186
 187         /**
 188          * @dataProvider provideDelete
 189          * @covers Database::delete
 190          */
 191         public function testDelete( $sql, $sqlText ) {
 192                 $this->database->delete(
 193                         $sql['table'],
 194                         $sql['conds'],
 195                         __METHOD__
 196                 );
 197                 $this->assertLastSql( $sqlText );
 198         }
 199
 200         public static function provideDelete() {
 201                 return [
 202                         [
 203                                 [
 204                                         'table' => 'table',
 205                                         'conds' => [ 'alias' => 'text' ],
 206                                 ],
 207                                 "DELETE FROM table " .
 208                                         "WHERE alias = 'text'"
 209                         ],
 210                         [
 211                                 [
 212                                         'table' => 'table',
 213                                         'conds' => '*',
 214                                 ],
 215                                 "DELETE FROM table"
 216                         ],
 217                 ];
 218         }
 219
 220         /**
 221          * @dataProvider provideUpsert
 222          * @covers Database::upsert
 223          */
 224         public function testUpsert( $sql, $sqlText ) {
 225                 $this->database->upsert(
 226                         $sql['table'],
 227                         $sql['rows'],
 228                         $sql['uniqueIndexes'],
 229                         $sql['set'],
 230                         __METHOD__
 231                 );
 232                 $this->assertLastSql( $sqlText );
 233         }
 234
 235         public static function provideUpsert() {
 236                 return [
 237                         [
 238                                 [
 239                                         'table' => 'upsert_table',
 240                                         'rows' => [ 'field' => 'text', 'field2' => 'text2' ],
 241                                         'uniqueIndexes' => [ 'field' ],
 242                                         'set' => [ 'field' => 'set' ],
 243                                 ],
 244                                 "BEGIN; " .
 245                                         "UPDATE upsert_table " .
 246                                         "SET field = 'set' " .
 247                                         "WHERE ((field = 'text')); " .
 248                                         "INSERT IGNORE INTO upsert_table " .
 249                                         "(field,field2) " .
 250                                         "VALUES ('text','text2'); " .
 251                                         "COMMIT"
 252                         ],
 253                 ];
 254         }
 255
 256         /**
 257          * @dataProvider provideDeleteJoin
 258          * @covers Database::deleteJoin
 259          */
 260         public function testDeleteJoin( $sql, $sqlText ) {
 261                 $this->database->deleteJoin(
 262                         $sql['delTable'],
 263                         $sql['joinTable'],
 264                         $sql['delVar'],
 265                         $sql['joinVar'],
 266                         $sql['conds'],
 267                         __METHOD__
 268                 );
 269                 $this->assertLastSql( $sqlText );
 270         }
 271
 272         public static function provideDeleteJoin() {
 273                 return [
 274                         [
 275                                 [
 276                                         'delTable' => 'table',
 277                                         'joinTable' => 'table_join',
 278                                         'delVar' => 'field',
 279                                         'joinVar' => 'field_join',
 280                                         'conds' => [ 'alias' => 'text' ],
 281                                 ],
 282                                 "DELETE FROM table " .
 283                                         "WHERE field IN (" .
 284                                         "SELECT field_join FROM table_join WHERE alias = 'text'" .
 285                                         ")"
 286                         ],
 287                         [
 288                                 [
 289                                         'delTable' => 'table',
 290                                         'joinTable' => 'table_join',
 291                                         'delVar' => 'field',
 292                                         'joinVar' => 'field_join',
 293                                         'conds' => '*',
 294                                 ],
 295                                 "DELETE FROM table " .
 296                                         "WHERE field IN (" .
 297                                         "SELECT field_join FROM table_join " .
 298                                         ")"
 299                         ],
 300                 ];
 301         }
 302
 303         /**
 304          * @dataProvider provideInsert
 305          * @covers Database::insert
 306          */
 307         public function testInsert( $sql, $sqlText ) {
 308                 $this->database->insert(
 309                         $sql['table'],
 310                         $sql['rows'],
 311                         __METHOD__,
 312                         isset( $sql['options'] ) ? $sql['options'] : []
 313                 );
 314                 $this->assertLastSql( $sqlText );
 315         }
 316
 317         public static function provideInsert() {
 318                 return [
 319                         [
 320                                 [
 321                                         'table' => 'table',
 322                                         'rows' => [ 'field' => 'text', 'field2' => 2 ],
 323                                 ],
 324                                 "INSERT INTO table " .
 325                                         "(field,field2) " .
 326                                         "VALUES ('text','2')"
 327                         ],
 328                         [
 329                                 [
 330                                         'table' => 'table',
 331                                         'rows' => [ 'field' => 'text', 'field2' => 2 ],
 332                                         'options' => 'IGNORE',
 333                                 ],
 334                                 "INSERT IGNORE INTO table " .
 335                                         "(field,field2) " .
 336                                         "VALUES ('text','2')"
 337                         ],
 338                         [
 339                                 [
 340                                         'table' => 'table',
 341                                         'rows' => [
 342                                                 [ 'field' => 'text', 'field2' => 2 ],
 343                                                 [ 'field' => 'multi', 'field2' => 3 ],
 344                                         ],
 345                                         'options' => 'IGNORE',
 346                                 ],
 347                                 "INSERT IGNORE INTO table " .
 348                                         "(field,field2) " .
 349                                         "VALUES " .
 350                                         "('text','2')," .
 351                                         "('multi','3')"
 352                         ],
 353                 ];
 354         }
 355
 356         /**
 357          * @dataProvider provideInsertSelect
 358          * @covers Database::insertSelect
 359          */
 360         public function testInsertSelect( $sql, $sqlTextNative, $sqlSelect, $sqlInsert ) {
 361                 $this->database->insertSelect(
 362                         $sql['destTable'],
 363                         $sql['srcTable'],
 364                         $sql['varMap'],
 365                         $sql['conds'],
 366                         __METHOD__,
 367                         isset( $sql['insertOptions'] ) ? $sql['insertOptions'] : [],
 368                         isset( $sql['selectOptions'] ) ? $sql['selectOptions'] : []
 369                 );
 370                 $this->assertLastSql( $sqlTextNative );
 371
 372                 $dbWeb = new DatabaseTestHelper( __CLASS__, [ 'cliMode' => false ] );
 373                 $dbWeb->forceNextResult( [
 374                         array_flip( array_keys( $sql['varMap'] ) )
 375                 ] );
 376                 $dbWeb->insertSelect(
 377                         $sql['destTable'],
 378                         $sql['srcTable'],
 379                         $sql['varMap'],
 380                         $sql['conds'],
 381                         __METHOD__,
 382                         isset( $sql['insertOptions'] ) ? $sql['insertOptions'] : [],
 383                         isset( $sql['selectOptions'] ) ? $sql['selectOptions'] : []
 384                 );
 385                 $this->assertLastSqlDb( implode( '; ', [ $sqlSelect, $sqlInsert ] ), $dbWeb );
 386         }
 387
 388         public static function provideInsertSelect() {
 389                 return [
 390                         [
 391                                 [
 392                                         'destTable' => 'insert_table',
 393                                         'srcTable' => 'select_table',
 394                                         'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
 395                                         'conds' => '*',
 396                                 ],
 397                                 "INSERT INTO insert_table " .
 398                                         "(field_insert,field) " .
 399                                         "SELECT field_select,field2 " .
 400                                         "FROM select_table",
 401                                 "SELECT field_select AS field_insert,field2 AS field " .
 402                                 "FROM select_table WHERE *   FOR UPDATE",
 403                                 "INSERT INTO insert_table (field_insert,field) VALUES ('0','1')"
 404                         ],
 405                         [
 406                                 [
 407                                         'destTable' => 'insert_table',
 408                                         'srcTable' => 'select_table',
 409                                         'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
 410                                         'conds' => [ 'field' => 2 ],
 411                                 ],
 412                                 "INSERT INTO insert_table " .
 413                                         "(field_insert,field) " .
 414                                         "SELECT field_select,field2 " .
 415                                         "FROM select_table " .
 416                                         "WHERE field = '2'",
 417                                 "SELECT field_select AS field_insert,field2 AS field FROM " .
 418                                 "select_table WHERE field = '2'   FOR UPDATE",
 419                                 "INSERT INTO insert_table (field_insert,field) VALUES ('0','1')"
 420                         ],
 421                         [
 422                                 [
 423                                         'destTable' => 'insert_table',
 424                                         'srcTable' => 'select_table',
 425                                         'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
 426                                         'conds' => [ 'field' => 2 ],
 427                                         'insertOptions' => 'IGNORE',
 428                                         'selectOptions' => [ 'ORDER BY' => 'field' ],
 429                                 ],
 430                                 "INSERT IGNORE INTO insert_table " .
 431                                         "(field_insert,field) " .
 432                                         "SELECT field_select,field2 " .
 433                                         "FROM select_table " .
 434                                         "WHERE field = '2' " .
 435                                         "ORDER BY field",
 436                                 "SELECT field_select AS field_insert,field2 AS field " .
 437                                 "FROM select_table WHERE field = '2' ORDER BY field  FOR UPDATE",
 438                                 "INSERT IGNORE INTO insert_table (field_insert,field) VALUES ('0','1')"
 439                         ],
 440                 ];
 441         }
 442
 443         /**
 444          * @dataProvider provideReplace
 445          * @covers Database::replace
 446          */
 447         public function testReplace( $sql, $sqlText ) {
 448                 $this->database->replace(
 449                         $sql['table'],
 450                         $sql['uniqueIndexes'],
 451                         $sql['rows'],
 452                         __METHOD__
 453                 );
 454                 $this->assertLastSql( $sqlText );
 455         }
 456
 457         public static function provideReplace() {
 458                 return [
 459                         [
 460                                 [
 461                                         'table' => 'replace_table',
 462                                         'uniqueIndexes' => [ 'field' ],
 463                                         'rows' => [ 'field' => 'text', 'field2' => 'text2' ],
 464                                 ],
 465                                 "DELETE FROM replace_table " .
 466                                         "WHERE ( field='text' ); " .
 467                                         "INSERT INTO replace_table " .
 468                                         "(field,field2) " .
 469                                         "VALUES ('text','text2')"
 470                         ],
 471                         [
 472                                 [
 473                                         'table' => 'module_deps',
 474                                         'uniqueIndexes' => [ [ 'md_module', 'md_skin' ] ],
 475                                         'rows' => [
 476                                                 'md_module' => 'module',
 477                                                 'md_skin' => 'skin',
 478                                                 'md_deps' => 'deps',
 479                                         ],
 480                                 ],
 481                                 "DELETE FROM module_deps " .
 482                                         "WHERE ( md_module='module' AND md_skin='skin' ); " .
 483                                         "INSERT INTO module_deps " .
 484                                         "(md_module,md_skin,md_deps) " .
 485                                         "VALUES ('module','skin','deps')"
 486                         ],
 487                         [
 488                                 [
 489                                         'table' => 'module_deps',
 490                                         'uniqueIndexes' => [ [ 'md_module', 'md_skin' ] ],
 491                                         'rows' => [
 492                                                 [
 493                                                         'md_module' => 'module',
 494                                                         'md_skin' => 'skin',
 495                                                         'md_deps' => 'deps',
 496                                                 ], [
 497                                                         'md_module' => 'module2',
 498                                                         'md_skin' => 'skin2',
 499                                                         'md_deps' => 'deps2',
 500                                                 ],
 501                                         ],
 502                                 ],
 503                                 "DELETE FROM module_deps " .
 504                                         "WHERE ( md_module='module' AND md_skin='skin' ); " .
 505                                         "INSERT INTO module_deps " .
 506                                         "(md_module,md_skin,md_deps) " .
 507                                         "VALUES ('module','skin','deps'); " .
 508                                         "DELETE FROM module_deps " .
 509                                         "WHERE ( md_module='module2' AND md_skin='skin2' ); " .
 510                                         "INSERT INTO module_deps " .
 511                                         "(md_module,md_skin,md_deps) " .
 512                                         "VALUES ('module2','skin2','deps2')"
 513                         ],
 514                         [
 515                                 [
 516                                         'table' => 'module_deps',
 517                                         'uniqueIndexes' => [ 'md_module', 'md_skin' ],
 518                                         'rows' => [
 519                                                 [
 520                                                         'md_module' => 'module',
 521                                                         'md_skin' => 'skin',
 522                                                         'md_deps' => 'deps',
 523                                                 ], [
 524                                                         'md_module' => 'module2',
 525                                                         'md_skin' => 'skin2',
 526                                                         'md_deps' => 'deps2',
 527                                                 ],
 528                                         ],
 529                                 ],
 530                                 "DELETE FROM module_deps " .
 531                                         "WHERE ( md_module='module' ) OR ( md_skin='skin' ); " .
 532                                         "INSERT INTO module_deps " .
 533                                         "(md_module,md_skin,md_deps) " .
 534                                         "VALUES ('module','skin','deps'); " .
 535                                         "DELETE FROM module_deps " .
 536                                         "WHERE ( md_module='module2' ) OR ( md_skin='skin2' ); " .
 537                                         "INSERT INTO module_deps " .
 538                                         "(md_module,md_skin,md_deps) " .
 539                                         "VALUES ('module2','skin2','deps2')"
 540                         ],
 541                         [
 542                                 [
 543                                         'table' => 'module_deps',
 544                                         'uniqueIndexes' => [],
 545                                         'rows' => [
 546                                                 'md_module' => 'module',
 547                                                 'md_skin' => 'skin',
 548                                                 'md_deps' => 'deps',
 549                                         ],
 550                                 ],
 551                                 "INSERT INTO module_deps " .
 552                                         "(md_module,md_skin,md_deps) " .
 553                                         "VALUES ('module','skin','deps')"
 554                         ],
 555                 ];
 556         }
 557
 558         /**
 559          * @dataProvider provideNativeReplace
 560          * @covers Database::nativeReplace
 561          */
 562         public function testNativeReplace( $sql, $sqlText ) {
 563                 $this->database->nativeReplace(
 564                         $sql['table'],
 565                         $sql['rows'],
 566                         __METHOD__
 567                 );
 568                 $this->assertLastSql( $sqlText );
 569         }
 570
 571         public static function provideNativeReplace() {
 572                 return [
 573                         [
 574                                 [
 575                                         'table' => 'replace_table',
 576                                         'rows' => [ 'field' => 'text', 'field2' => 'text2' ],
 577                                 ],
 578                                 "REPLACE INTO replace_table " .
 579                                         "(field,field2) " .
 580                                         "VALUES ('text','text2')"
 581                         ],
 582                 ];
 583         }
 584
 585         /**
 586          * @dataProvider provideConditional
 587          * @covers Database::conditional
 588          */
 589         public function testConditional( $sql, $sqlText ) {
 590                 $this->assertEquals( trim( $this->database->conditional(
 591                         $sql['conds'],
 592                         $sql['true'],
 593                         $sql['false']
 594                 ) ), $sqlText );
 595         }
 596
 597         public static function provideConditional() {
 598                 return [
 599                         [
 600                                 [
 601                                         'conds' => [ 'field' => 'text' ],
 602                                         'true' => 1,
 603                                         'false' => 'NULL',
 604                                 ],
 605                                 "(CASE WHEN field = 'text' THEN 1 ELSE NULL END)"
 606                         ],
 607                         [
 608                                 [
 609                                         'conds' => [ 'field' => 'text', 'field2' => 'anothertext' ],
 610                                         'true' => 1,
 611                                         'false' => 'NULL',
 612                                 ],
 613                                 "(CASE WHEN field = 'text' AND field2 = 'anothertext' THEN 1 ELSE NULL END)"
 614                         ],
 615                         [
 616                                 [
 617                                         'conds' => 'field=1',
 618                                         'true' => 1,
 619                                         'false' => 'NULL',
 620                                 ],
 621                                 "(CASE WHEN field=1 THEN 1 ELSE NULL END)"
 622                         ],
 623                 ];
 624         }
 625
 626         /**
 627          * @dataProvider provideBuildConcat
 628          * @covers Database::buildConcat
 629          */
 630         public function testBuildConcat( $stringList, $sqlText ) {
 631                 $this->assertEquals( trim( $this->database->buildConcat(
 632                         $stringList
 633                 ) ), $sqlText );
 634         }
 635
 636         public static function provideBuildConcat() {
 637                 return [
 638                         [
 639                                 [ 'field', 'field2' ],
 640                                 "CONCAT(field,field2)"
 641                         ],
 642                         [
 643                                 [ "'test'", 'field2' ],
 644                                 "CONCAT('test',field2)"
 645                         ],
 646                 ];
 647         }
 648
 649         /**
 650          * @dataProvider provideBuildLike
 651          * @covers Database::buildLike
 652          */
 653         public function testBuildLike( $array, $sqlText ) {
 654                 $this->assertEquals( trim( $this->database->buildLike(
 655                         $array
 656                 ) ), $sqlText );
 657         }
 658
 659         public static function provideBuildLike() {
 660                 return [
 661                         [
 662                                 'text',
 663                                 "LIKE 'text' ESCAPE '`'"
 664                         ],
 665                         [
 666                                 [ 'text', new LikeMatch( '%' ) ],
 667                                 "LIKE 'text%' ESCAPE '`'"
 668                         ],
 669                         [
 670                                 [ 'text', new LikeMatch( '%' ), 'text2' ],
 671                                 "LIKE 'text%text2' ESCAPE '`'"
 672                         ],
 673                         [
 674                                 [ 'text', new LikeMatch( '_' ) ],
 675                                 "LIKE 'text_' ESCAPE '`'"
 676                         ],
 677                         [
 678                                 'more_text',
 679                                 "LIKE 'more`_text' ESCAPE '`'"
 680                         ],
 681                         [
 682                                 [ 'C:\\Windows\\', new LikeMatch( '%' ) ],
 683                                 "LIKE 'C:\\Windows\\%' ESCAPE '`'"
 684                         ],
 685                         [
 686                                 [ 'accent`_test`', new LikeMatch( '%' ) ],
 687                                 "LIKE 'accent```_test``%' ESCAPE '`'"
 688                         ],
 689                 ];
 690         }
 691
 692         /**
 693          * @dataProvider provideUnionQueries
 694          * @covers Database::unionQueries
 695          */
 696         public function testUnionQueries( $sql, $sqlText ) {
 697                 $this->assertEquals( trim( $this->database->unionQueries(
 698                         $sql['sqls'],
 699                         $sql['all']
 700                 ) ), $sqlText );
 701         }
 702
 703         public static function provideUnionQueries() {
 704                 return [
 705                         [
 706                                 [
 707                                         'sqls' => [ 'RAW SQL', 'RAW2SQL' ],
 708                                         'all' => true,
 709                                 ],
 710                                 "(RAW SQL) UNION ALL (RAW2SQL)"
 711                         ],
 712                         [
 713                                 [
 714                                         'sqls' => [ 'RAW SQL', 'RAW2SQL' ],
 715                                         'all' => false,
 716                                 ],
 717                                 "(RAW SQL) UNION (RAW2SQL)"
 718                         ],
 719                         [
 720                                 [
 721                                         'sqls' => [ 'RAW SQL', 'RAW2SQL', 'RAW3SQL' ],
 722                                         'all' => false,
 723                                 ],
 724                                 "(RAW SQL) UNION (RAW2SQL) UNION (RAW3SQL)"
 725                         ],
 726                 ];
 727         }
 728
 729         /**
 730          * @covers Database::commit
 731          */
 732         public function testTransactionCommit() {
 733                 $this->database->begin( __METHOD__ );
 734                 $this->database->commit( __METHOD__ );
 735                 $this->assertLastSql( 'BEGIN; COMMIT' );
 736         }
 737
 738         /**
 739          * @covers Database::rollback
 740          */
 741         public function testTransactionRollback() {
 742                 $this->database->begin( __METHOD__ );
 743                 $this->database->rollback( __METHOD__ );
 744                 $this->assertLastSql( 'BEGIN; ROLLBACK' );
 745         }
 746
 747         /**
 748          * @covers Database::dropTable
 749          */
 750         public function testDropTable() {
 751                 $this->database->setExistingTables( [ 'table' ] );
 752                 $this->database->dropTable( 'table', __METHOD__ );
 753                 $this->assertLastSql( 'DROP TABLE table CASCADE' );
 754         }
 755
 756         /**
 757          * @covers Database::dropTable
 758          */
 759         public function testDropNonExistingTable() {
 760                 $this->assertFalse(
 761                         $this->database->dropTable( 'non_existing', __METHOD__ )
 762                 );
 763         }
 764
 765         /**
 766          * @dataProvider provideMakeList
 767          * @covers Database::makeList
 768          */
 769         public function testMakeList( $list, $mode, $sqlText ) {
 770                 $this->assertEquals( trim( $this->database->makeList(
 771                         $list, $mode
 772                 ) ), $sqlText );
 773         }
 774
 775         public static function provideMakeList() {
 776                 return [
 777                         [
 778                                 [ 'value', 'value2' ],
 779                                 LIST_COMMA,
 780                                 "'value','value2'"
 781                         ],
 782                         [
 783                                 [ 'field', 'field2' ],
 784                                 LIST_NAMES,
 785                                 "field,field2"
 786                         ],
 787                         [
 788                                 [ 'field' => 'value', 'field2' => 'value2' ],
 789                                 LIST_AND,
 790                                 "field = 'value' AND field2 = 'value2'"
 791                         ],
 792                         [
 793                                 [ 'field' => null, "field2 != 'value2'" ],
 794                                 LIST_AND,
 795                                 "field IS NULL AND (field2 != 'value2')"
 796                         ],
 797                         [
 798                                 [ 'field' => [ 'value', null, 'value2' ], 'field2' => 'value2' ],
 799                                 LIST_AND,
 800                                 "(field IN ('value','value2')  OR field IS NULL) AND field2 = 'value2'"
 801                         ],
 802                         [
 803                                 [ 'field' => [ null ], 'field2' => null ],
 804                                 LIST_AND,
 805                                 "field IS NULL AND field2 IS NULL"
 806                         ],
 807                         [
 808                                 [ 'field' => 'value', 'field2' => 'value2' ],
 809                                 LIST_OR,
 810                                 "field = 'value' OR field2 = 'value2'"
 811                         ],
 812                         [
 813                                 [ 'field' => 'value', 'field2' => null ],
 814                                 LIST_OR,
 815                                 "field = 'value' OR field2 IS NULL"
 816                         ],
 817                         [
 818                                 [ 'field' => [ 'value', 'value2' ], 'field2' => [ 'value' ] ],
 819                                 LIST_OR,
 820                                 "field IN ('value','value2')  OR field2 = 'value'"
 821                         ],
 822                         [
 823                                 [ 'field' => [ null, 'value', null, 'value2' ], "field2 != 'value2'" ],
 824                                 LIST_OR,
 825                                 "(field IN ('value','value2')  OR field IS NULL) OR (field2 != 'value2')"
 826                         ],
 827                         [
 828                                 [ 'field' => 'value', 'field2' => 'value2' ],
 829                                 LIST_SET,
 830                                 "field = 'value',field2 = 'value2'"
 831                         ],
 832                         [
 833                                 [ 'field' => 'value', 'field2' => null ],
 834                                 LIST_SET,
 835                                 "field = 'value',field2 = NULL"
 836                         ],
 837                         [
 838                                 [ 'field' => 'value', "field2 != 'value2'" ],
 839                                 LIST_SET,
 840                                 "field = 'value',field2 != 'value2'"
 841                         ],
 842                 ];
 843         }
 844
 845         public function testSessionTempTables() {
 846                 $temp1 = $this->database->tableName( 'tmp_table_1' );
 847                 $temp2 = $this->database->tableName( 'tmp_table_2' );
 848                 $temp3 = $this->database->tableName( 'tmp_table_3' );
 849
 850                 $this->database->query( "CREATE TEMPORARY TABLE $temp1 LIKE orig_tbl", __METHOD__ );
 851                 $this->database->query( "CREATE TEMPORARY TABLE $temp2 LIKE orig_tbl", __METHOD__ );
 852                 $this->database->query( "CREATE TEMPORARY TABLE $temp3 LIKE orig_tbl", __METHOD__ );
 853
 854                 $this->assertTrue( $this->database->tableExists( "tmp_table_1", __METHOD__ ) );
 855                 $this->assertTrue( $this->database->tableExists( "tmp_table_2", __METHOD__ ) );
 856                 $this->assertTrue( $this->database->tableExists( "tmp_table_3", __METHOD__ ) );
 857
 858                 $this->database->dropTable( 'tmp_table_1', __METHOD__ );
 859                 $this->database->dropTable( 'tmp_table_2', __METHOD__ );
 860                 $this->database->dropTable( 'tmp_table_3', __METHOD__ );
 861
 862                 $this->assertFalse( $this->database->tableExists( "tmp_table_1", __METHOD__ ) );
 863                 $this->assertFalse( $this->database->tableExists( "tmp_table_2", __METHOD__ ) );
 864                 $this->assertFalse( $this->database->tableExists( "tmp_table_3", __METHOD__ ) );
 865
 866                 $this->database->query( "CREATE TEMPORARY TABLE tmp_table_1 LIKE orig_tbl", __METHOD__ );
 867                 $this->database->query( "CREATE TEMPORARY TABLE 'tmp_table_2' LIKE orig_tbl", __METHOD__ );
 868                 $this->database->query( "CREATE TEMPORARY TABLE `tmp_table_3` LIKE orig_tbl", __METHOD__ );
 869
 870                 $this->assertTrue( $this->database->tableExists( "tmp_table_1", __METHOD__ ) );
 871                 $this->assertTrue( $this->database->tableExists( "tmp_table_2", __METHOD__ ) );
 872                 $this->assertTrue( $this->database->tableExists( "tmp_table_3", __METHOD__ ) );
 873
 874                 $this->database->query( "DROP TEMPORARY TABLE tmp_table_1 LIKE orig_tbl", __METHOD__ );
 875                 $this->database->query( "DROP TEMPORARY TABLE 'tmp_table_2' LIKE orig_tbl", __METHOD__ );
 876                 $this->database->query( "DROP TABLE `tmp_table_3` LIKE orig_tbl", __METHOD__ );
 877
 878                 $this->assertFalse( $this->database->tableExists( "tmp_table_1", __METHOD__ ) );
 879                 $this->assertFalse( $this->database->tableExists( "tmp_table_2", __METHOD__ ) );
 880                 $this->assertFalse( $this->database->tableExists( "tmp_table_3", __METHOD__ ) );
 881         }
 882 }