tests/phpunit/includes/db/DatabaseSQLTest.php

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