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                         $sqlText,
  21                         $this->database->getLastSqls()
  22                 );
  23         }
  24
  25         protected function assertLastSqlDb( $sqlText, $db ) {
  26                 $this->assertEquals( $sqlText, $db->getLastSqls() );
  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                         isset( $sql['selectJoinConds'] ) ? $sql['selectJoinConds'] : []
 370                 );
 371                 $this->assertLastSql( $sqlTextNative );
 372
 373                 $dbWeb = new DatabaseTestHelper( __CLASS__, [ 'cliMode' => false ] );
 374                 $dbWeb->forceNextResult( [
 375                         array_flip( array_keys( $sql['varMap'] ) )
 376                 ] );
 377                 $dbWeb->insertSelect(
 378                         $sql['destTable'],
 379                         $sql['srcTable'],
 380                         $sql['varMap'],
 381                         $sql['conds'],
 382                         __METHOD__,
 383                         isset( $sql['insertOptions'] ) ? $sql['insertOptions'] : [],
 384                         isset( $sql['selectOptions'] ) ? $sql['selectOptions'] : [],
 385                         isset( $sql['selectJoinConds'] ) ? $sql['selectJoinConds'] : []
 386                 );
 387                 $this->assertLastSqlDb( implode( '; ', [ $sqlSelect, $sqlInsert ] ), $dbWeb );
 388         }
 389
 390         public static function provideInsertSelect() {
 391                 return [
 392                         [
 393                                 [
 394                                         'destTable' => 'insert_table',
 395                                         'srcTable' => 'select_table',
 396                                         'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
 397                                         'conds' => '*',
 398                                 ],
 399                                 "INSERT INTO insert_table " .
 400                                         "(field_insert,field) " .
 401                                         "SELECT field_select,field2 " .
 402                                         "FROM select_table WHERE *",
 403                                 "SELECT field_select AS field_insert,field2 AS field " .
 404                                 "FROM select_table WHERE *   FOR UPDATE",
 405                                 "INSERT INTO insert_table (field_insert,field) VALUES ('0','1')"
 406                         ],
 407                         [
 408                                 [
 409                                         'destTable' => 'insert_table',
 410                                         'srcTable' => 'select_table',
 411                                         'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
 412                                         'conds' => [ 'field' => 2 ],
 413                                 ],
 414                                 "INSERT INTO insert_table " .
 415                                         "(field_insert,field) " .
 416                                         "SELECT field_select,field2 " .
 417                                         "FROM select_table " .
 418                                         "WHERE field = '2'",
 419                                 "SELECT field_select AS field_insert,field2 AS field FROM " .
 420                                 "select_table WHERE field = '2'   FOR UPDATE",
 421                                 "INSERT INTO insert_table (field_insert,field) VALUES ('0','1')"
 422                         ],
 423                         [
 424                                 [
 425                                         'destTable' => 'insert_table',
 426                                         'srcTable' => 'select_table',
 427                                         'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
 428                                         'conds' => [ 'field' => 2 ],
 429                                         'insertOptions' => 'IGNORE',
 430                                         'selectOptions' => [ 'ORDER BY' => 'field' ],
 431                                 ],
 432                                 "INSERT IGNORE INTO insert_table " .
 433                                         "(field_insert,field) " .
 434                                         "SELECT field_select,field2 " .
 435                                         "FROM select_table " .
 436                                         "WHERE field = '2' " .
 437                                         "ORDER BY field",
 438                                 "SELECT field_select AS field_insert,field2 AS field " .
 439                                 "FROM select_table WHERE field = '2' ORDER BY field  FOR UPDATE",
 440                                 "INSERT IGNORE INTO insert_table (field_insert,field) VALUES ('0','1')"
 441                         ],
 442                         [
 443                                 [
 444                                         'destTable' => 'insert_table',
 445                                         'srcTable' => [ 'select_table1', 'select_table2' ],
 446                                         'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
 447                                         'conds' => [ 'field' => 2 ],
 448                                         'selectOptions' => [ 'ORDER BY' => 'field', 'FORCE INDEX' => [ 'select_table1' => 'index1' ] ],
 449                                         'selectJoinConds' => [
 450                                                 'select_table2' => [ 'LEFT JOIN', [ 'select_table1.foo = select_table2.bar' ] ],
 451                                         ],
 452                                 ],
 453                                 "INSERT INTO insert_table " .
 454                                         "(field_insert,field) " .
 455                                         "SELECT field_select,field2 " .
 456                                         "FROM select_table1 LEFT JOIN select_table2 ON ((select_table1.foo = select_table2.bar)) " .
 457                                         "WHERE field = '2' " .
 458                                         "ORDER BY field",
 459                                 "SELECT field_select AS field_insert,field2 AS field " .
 460                                 "FROM select_table1 LEFT JOIN select_table2 ON ((select_table1.foo = select_table2.bar)) " .
 461                                 "WHERE field = '2' ORDER BY field  FOR UPDATE",
 462                                 "INSERT INTO insert_table (field_insert,field) VALUES ('0','1')"
 463                         ],
 464                 ];
 465         }
 466
 467         /**
 468          * @dataProvider provideReplace
 469          * @covers Database::replace
 470          */
 471         public function testReplace( $sql, $sqlText ) {
 472                 $this->database->replace(
 473                         $sql['table'],
 474                         $sql['uniqueIndexes'],
 475                         $sql['rows'],
 476                         __METHOD__
 477                 );
 478                 $this->assertLastSql( $sqlText );
 479         }
 480
 481         public static function provideReplace() {
 482                 return [
 483                         [
 484                                 [
 485                                         'table' => 'replace_table',
 486                                         'uniqueIndexes' => [ 'field' ],
 487                                         'rows' => [ 'field' => 'text', 'field2' => 'text2' ],
 488                                 ],
 489                                 "DELETE FROM replace_table " .
 490                                         "WHERE ( field='text' ); " .
 491                                         "INSERT INTO replace_table " .
 492                                         "(field,field2) " .
 493                                         "VALUES ('text','text2')"
 494                         ],
 495                         [
 496                                 [
 497                                         'table' => 'module_deps',
 498                                         'uniqueIndexes' => [ [ 'md_module', 'md_skin' ] ],
 499                                         'rows' => [
 500                                                 'md_module' => 'module',
 501                                                 'md_skin' => 'skin',
 502                                                 'md_deps' => 'deps',
 503                                         ],
 504                                 ],
 505                                 "DELETE FROM module_deps " .
 506                                         "WHERE ( md_module='module' AND md_skin='skin' ); " .
 507                                         "INSERT INTO module_deps " .
 508                                         "(md_module,md_skin,md_deps) " .
 509                                         "VALUES ('module','skin','deps')"
 510                         ],
 511                         [
 512                                 [
 513                                         'table' => 'module_deps',
 514                                         'uniqueIndexes' => [ [ 'md_module', 'md_skin' ] ],
 515                                         'rows' => [
 516                                                 [
 517                                                         'md_module' => 'module',
 518                                                         'md_skin' => 'skin',
 519                                                         'md_deps' => 'deps',
 520                                                 ], [
 521                                                         'md_module' => 'module2',
 522                                                         'md_skin' => 'skin2',
 523                                                         'md_deps' => 'deps2',
 524                                                 ],
 525                                         ],
 526                                 ],
 527                                 "DELETE FROM module_deps " .
 528                                         "WHERE ( md_module='module' AND md_skin='skin' ); " .
 529                                         "INSERT INTO module_deps " .
 530                                         "(md_module,md_skin,md_deps) " .
 531                                         "VALUES ('module','skin','deps'); " .
 532                                         "DELETE FROM module_deps " .
 533                                         "WHERE ( md_module='module2' AND md_skin='skin2' ); " .
 534                                         "INSERT INTO module_deps " .
 535                                         "(md_module,md_skin,md_deps) " .
 536                                         "VALUES ('module2','skin2','deps2')"
 537                         ],
 538                         [
 539                                 [
 540                                         'table' => 'module_deps',
 541                                         'uniqueIndexes' => [ 'md_module', 'md_skin' ],
 542                                         'rows' => [
 543                                                 [
 544                                                         'md_module' => 'module',
 545                                                         'md_skin' => 'skin',
 546                                                         'md_deps' => 'deps',
 547                                                 ], [
 548                                                         'md_module' => 'module2',
 549                                                         'md_skin' => 'skin2',
 550                                                         'md_deps' => 'deps2',
 551                                                 ],
 552                                         ],
 553                                 ],
 554                                 "DELETE FROM module_deps " .
 555                                         "WHERE ( md_module='module' ) OR ( md_skin='skin' ); " .
 556                                         "INSERT INTO module_deps " .
 557                                         "(md_module,md_skin,md_deps) " .
 558                                         "VALUES ('module','skin','deps'); " .
 559                                         "DELETE FROM module_deps " .
 560                                         "WHERE ( md_module='module2' ) OR ( md_skin='skin2' ); " .
 561                                         "INSERT INTO module_deps " .
 562                                         "(md_module,md_skin,md_deps) " .
 563                                         "VALUES ('module2','skin2','deps2')"
 564                         ],
 565                         [
 566                                 [
 567                                         'table' => 'module_deps',
 568                                         'uniqueIndexes' => [],
 569                                         'rows' => [
 570                                                 'md_module' => 'module',
 571                                                 'md_skin' => 'skin',
 572                                                 'md_deps' => 'deps',
 573                                         ],
 574                                 ],
 575                                 "INSERT INTO module_deps " .
 576                                         "(md_module,md_skin,md_deps) " .
 577                                         "VALUES ('module','skin','deps')"
 578                         ],
 579                 ];
 580         }
 581
 582         /**
 583          * @dataProvider provideNativeReplace
 584          * @covers Database::nativeReplace
 585          */
 586         public function testNativeReplace( $sql, $sqlText ) {
 587                 $this->database->nativeReplace(
 588                         $sql['table'],
 589                         $sql['rows'],
 590                         __METHOD__
 591                 );
 592                 $this->assertLastSql( $sqlText );
 593         }
 594
 595         public static function provideNativeReplace() {
 596                 return [
 597                         [
 598                                 [
 599                                         'table' => 'replace_table',
 600                                         'rows' => [ 'field' => 'text', 'field2' => 'text2' ],
 601                                 ],
 602                                 "REPLACE INTO replace_table " .
 603                                         "(field,field2) " .
 604                                         "VALUES ('text','text2')"
 605                         ],
 606                 ];
 607         }
 608
 609         /**
 610          * @dataProvider provideConditional
 611          * @covers Database::conditional
 612          */
 613         public function testConditional( $sql, $sqlText ) {
 614                 $this->assertEquals( trim( $this->database->conditional(
 615                         $sql['conds'],
 616                         $sql['true'],
 617                         $sql['false']
 618                 ) ), $sqlText );
 619         }
 620
 621         public static function provideConditional() {
 622                 return [
 623                         [
 624                                 [
 625                                         'conds' => [ 'field' => 'text' ],
 626                                         'true' => 1,
 627                                         'false' => 'NULL',
 628                                 ],
 629                                 "(CASE WHEN field = 'text' THEN 1 ELSE NULL END)"
 630                         ],
 631                         [
 632                                 [
 633                                         'conds' => [ 'field' => 'text', 'field2' => 'anothertext' ],
 634                                         'true' => 1,
 635                                         'false' => 'NULL',
 636                                 ],
 637                                 "(CASE WHEN field = 'text' AND field2 = 'anothertext' THEN 1 ELSE NULL END)"
 638                         ],
 639                         [
 640                                 [
 641                                         'conds' => 'field=1',
 642                                         'true' => 1,
 643                                         'false' => 'NULL',
 644                                 ],
 645                                 "(CASE WHEN field=1 THEN 1 ELSE NULL END)"
 646                         ],
 647                 ];
 648         }
 649
 650         /**
 651          * @dataProvider provideBuildConcat
 652          * @covers Database::buildConcat
 653          */
 654         public function testBuildConcat( $stringList, $sqlText ) {
 655                 $this->assertEquals( trim( $this->database->buildConcat(
 656                         $stringList
 657                 ) ), $sqlText );
 658         }
 659
 660         public static function provideBuildConcat() {
 661                 return [
 662                         [
 663                                 [ 'field', 'field2' ],
 664                                 "CONCAT(field,field2)"
 665                         ],
 666                         [
 667                                 [ "'test'", 'field2' ],
 668                                 "CONCAT('test',field2)"
 669                         ],
 670                 ];
 671         }
 672
 673         /**
 674          * @dataProvider provideBuildLike
 675          * @covers Database::buildLike
 676          */
 677         public function testBuildLike( $array, $sqlText ) {
 678                 $this->assertEquals( trim( $this->database->buildLike(
 679                         $array
 680                 ) ), $sqlText );
 681         }
 682
 683         public static function provideBuildLike() {
 684                 return [
 685                         [
 686                                 'text',
 687                                 "LIKE 'text' ESCAPE '`'"
 688                         ],
 689                         [
 690                                 [ 'text', new LikeMatch( '%' ) ],
 691                                 "LIKE 'text%' ESCAPE '`'"
 692                         ],
 693                         [
 694                                 [ 'text', new LikeMatch( '%' ), 'text2' ],
 695                                 "LIKE 'text%text2' ESCAPE '`'"
 696                         ],
 697                         [
 698                                 [ 'text', new LikeMatch( '_' ) ],
 699                                 "LIKE 'text_' ESCAPE '`'"
 700                         ],
 701                         [
 702                                 'more_text',
 703                                 "LIKE 'more`_text' ESCAPE '`'"
 704                         ],
 705                         [
 706                                 [ 'C:\\Windows\\', new LikeMatch( '%' ) ],
 707                                 "LIKE 'C:\\Windows\\%' ESCAPE '`'"
 708                         ],
 709                         [
 710                                 [ 'accent`_test`', new LikeMatch( '%' ) ],
 711                                 "LIKE 'accent```_test``%' ESCAPE '`'"
 712                         ],
 713                 ];
 714         }
 715
 716         /**
 717          * @dataProvider provideUnionQueries
 718          * @covers Database::unionQueries
 719          */
 720         public function testUnionQueries( $sql, $sqlText ) {
 721                 $this->assertEquals( trim( $this->database->unionQueries(
 722                         $sql['sqls'],
 723                         $sql['all']
 724                 ) ), $sqlText );
 725         }
 726
 727         public static function provideUnionQueries() {
 728                 return [
 729                         [
 730                                 [
 731                                         'sqls' => [ 'RAW SQL', 'RAW2SQL' ],
 732                                         'all' => true,
 733                                 ],
 734                                 "(RAW SQL) UNION ALL (RAW2SQL)"
 735                         ],
 736                         [
 737                                 [
 738                                         'sqls' => [ 'RAW SQL', 'RAW2SQL' ],
 739                                         'all' => false,
 740                                 ],
 741                                 "(RAW SQL) UNION (RAW2SQL)"
 742                         ],
 743                         [
 744                                 [
 745                                         'sqls' => [ 'RAW SQL', 'RAW2SQL', 'RAW3SQL' ],
 746                                         'all' => false,
 747                                 ],
 748                                 "(RAW SQL) UNION (RAW2SQL) UNION (RAW3SQL)"
 749                         ],
 750                 ];
 751         }
 752
 753         /**
 754          * @dataProvider provideUnionConditionPermutations
 755          * @covers Database::unionConditionPermutations
 756          */
 757         public function testUnionConditionPermutations( $params, $expect ) {
 758                 if ( isset( $params['unionSupportsOrderAndLimit'] ) ) {
 759                         $this->database->setUnionSupportsOrderAndLimit( $params['unionSupportsOrderAndLimit'] );
 760                 }
 761
 762                 $sql = trim( $this->database->unionConditionPermutations(
 763                         $params['table'],
 764                         $params['vars'],
 765                         $params['permute_conds'],
 766                         isset( $params['extra_conds'] ) ? $params['extra_conds'] : '',
 767                         'FNAME',
 768                         isset( $params['options'] ) ? $params['options'] : [],
 769                         isset( $params['join_conds'] ) ? $params['join_conds'] : []
 770                 ) );
 771                 $this->assertEquals( $expect, $sql );
 772         }
 773
 774         public static function provideUnionConditionPermutations() {
 775                 return [
 776                         // @codingStandardsIgnoreStart Generic.Files.LineLength.TooLong
 777                         [
 778                                 [
 779                                         'table' => [ 'table1', 'table2' ],
 780                                         'vars' => [ 'field1', 'alias' => 'field2' ],
 781                                         'permute_conds' => [
 782                                                 'field3' => [ 1, 2, 3 ],
 783                                                 'duplicates' => [ 4, 5, 4 ],
 784                                                 'empty' => [],
 785                                                 'single' => [ 0 ],
 786                                         ],
 787                                         'extra_conds' => 'table2.bar > 23',
 788                                         'options' => [
 789                                                 'ORDER BY' => [ 'field1', 'alias' ],
 790                                                 'INNER ORDER BY' => [ 'field1', 'field2' ],
 791                                                 'LIMIT' => 100,
 792                                         ],
 793                                         'join_conds' => [
 794                                                 'table2' => [ 'JOIN', 'table1.foo_id = table2.foo_id' ],
 795                                         ],
 796                                 ],
 797                                 "(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 " .
 798                                 "(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 " .
 799                                 "(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 " .
 800                                 "(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 " .
 801                                 "(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 " .
 802                                 "(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  ) " .
 803                                 "ORDER BY field1,alias LIMIT 100"
 804                         ],
 805                         [
 806                                 [
 807                                         'table' => 'foo',
 808                                         'vars' => [ 'foo_id' ],
 809                                         'permute_conds' => [
 810                                                 'bar' => [ 1, 2, 3 ],
 811                                         ],
 812                                         'extra_conds' => [ 'baz' => null ],
 813                                         'options' => [
 814                                                 'NOTALL',
 815                                                 'ORDER BY' => [ 'foo_id' ],
 816                                                 'LIMIT' => 25,
 817                                         ],
 818                                 ],
 819                                 "(SELECT  foo_id  FROM foo    WHERE bar = '1' AND baz IS NULL  ORDER BY foo_id LIMIT 25  ) UNION " .
 820                                 "(SELECT  foo_id  FROM foo    WHERE bar = '2' AND baz IS NULL  ORDER BY foo_id LIMIT 25  ) UNION " .
 821                                 "(SELECT  foo_id  FROM foo    WHERE bar = '3' AND baz IS NULL  ORDER BY foo_id LIMIT 25  ) " .
 822                                 "ORDER BY foo_id LIMIT 25"
 823                         ],
 824                         [
 825                                 [
 826                                         'table' => 'foo',
 827                                         'vars' => [ 'foo_id' ],
 828                                         'permute_conds' => [
 829                                                 'bar' => [ 1, 2, 3 ],
 830                                         ],
 831                                         'extra_conds' => [ 'baz' => null ],
 832                                         'options' => [
 833                                                 'NOTALL' => true,
 834                                                 'ORDER BY' => [ 'foo_id' ],
 835                                                 'LIMIT' => 25,
 836                                         ],
 837                                         'unionSupportsOrderAndLimit' => false,
 838                                 ],
 839                                 "(SELECT  foo_id  FROM foo    WHERE bar = '1' AND baz IS NULL  ) UNION " .
 840                                 "(SELECT  foo_id  FROM foo    WHERE bar = '2' AND baz IS NULL  ) UNION " .
 841                                 "(SELECT  foo_id  FROM foo    WHERE bar = '3' AND baz IS NULL  ) " .
 842                                 "ORDER BY foo_id LIMIT 25"
 843                         ],
 844                         [
 845                                 [
 846                                         'table' => 'foo',
 847                                         'vars' => [ 'foo_id' ],
 848                                         'permute_conds' => [],
 849                                         'extra_conds' => [ 'baz' => null ],
 850                                         'options' => [
 851                                                 'ORDER BY' => [ 'foo_id' ],
 852                                                 'LIMIT' => 25,
 853                                         ],
 854                                 ],
 855                                 "SELECT  foo_id  FROM foo    WHERE baz IS NULL  ORDER BY foo_id LIMIT 25"
 856                         ],
 857                         [
 858                                 [
 859                                         'table' => 'foo',
 860                                         'vars' => [ 'foo_id' ],
 861                                         'permute_conds' => [
 862                                                 'bar' => [],
 863                                         ],
 864                                         'extra_conds' => [ 'baz' => null ],
 865                                         'options' => [
 866                                                 'ORDER BY' => [ 'foo_id' ],
 867                                                 'LIMIT' => 25,
 868                                         ],
 869                                 ],
 870                                 "SELECT  foo_id  FROM foo    WHERE baz IS NULL  ORDER BY foo_id LIMIT 25"
 871                         ],
 872                         [
 873                                 [
 874                                         'table' => 'foo',
 875                                         'vars' => [ 'foo_id' ],
 876                                         'permute_conds' => [
 877                                                 'bar' => [ 1 ],
 878                                         ],
 879                                         'options' => [
 880                                                 'ORDER BY' => [ 'foo_id' ],
 881                                                 'LIMIT' => 25,
 882                                                 'OFFSET' => 150,
 883                                         ],
 884                                 ],
 885                                 "SELECT  foo_id  FROM foo    WHERE bar = '1'  ORDER BY foo_id LIMIT 150,25"
 886                         ],
 887                         [
 888                                 [
 889                                         'table' => 'foo',
 890                                         'vars' => [ 'foo_id' ],
 891                                         'permute_conds' => [],
 892                                         'extra_conds' => [ 'baz' => null ],
 893                                         'options' => [
 894                                                 'ORDER BY' => [ 'foo_id' ],
 895                                                 'LIMIT' => 25,
 896                                                 'OFFSET' => 150,
 897                                                 'INNER ORDER BY' => [ 'bar_id' ],
 898                                         ],
 899                                 ],
 900                                 "(SELECT  foo_id  FROM foo    WHERE baz IS NULL  ORDER BY bar_id LIMIT 175  ) ORDER BY foo_id LIMIT 150,25"
 901                         ],
 902                         [
 903                                 [
 904                                         'table' => 'foo',
 905                                         'vars' => [ 'foo_id' ],
 906                                         'permute_conds' => [],
 907                                         'extra_conds' => [ 'baz' => null ],
 908                                         'options' => [
 909                                                 'ORDER BY' => [ 'foo_id' ],
 910                                                 'LIMIT' => 25,
 911                                                 'OFFSET' => 150,
 912                                                 'INNER ORDER BY' => [ 'bar_id' ],
 913                                         ],
 914                                         'unionSupportsOrderAndLimit' => false,
 915                                 ],
 916                                 "SELECT  foo_id  FROM foo    WHERE baz IS NULL  ORDER BY foo_id LIMIT 150,25"
 917                         ],
 918                         // @codingStandardsIgnoreEnd
 919                 ];
 920         }
 921
 922         /**
 923          * @covers Database::commit
 924          */
 925         public function testTransactionCommit() {
 926                 $this->database->begin( __METHOD__ );
 927                 $this->database->commit( __METHOD__ );
 928                 $this->assertLastSql( 'BEGIN; COMMIT' );
 929         }
 930
 931         /**
 932          * @covers Database::rollback
 933          */
 934         public function testTransactionRollback() {
 935                 $this->database->begin( __METHOD__ );
 936                 $this->database->rollback( __METHOD__ );
 937                 $this->assertLastSql( 'BEGIN; ROLLBACK' );
 938         }
 939
 940         /**
 941          * @covers Database::dropTable
 942          */
 943         public function testDropTable() {
 944                 $this->database->setExistingTables( [ 'table' ] );
 945                 $this->database->dropTable( 'table', __METHOD__ );
 946                 $this->assertLastSql( 'DROP TABLE table CASCADE' );
 947         }
 948
 949         /**
 950          * @covers Database::dropTable
 951          */
 952         public function testDropNonExistingTable() {
 953                 $this->assertFalse(
 954                         $this->database->dropTable( 'non_existing', __METHOD__ )
 955                 );
 956         }
 957
 958         /**
 959          * @dataProvider provideMakeList
 960          * @covers Database::makeList
 961          */
 962         public function testMakeList( $list, $mode, $sqlText ) {
 963                 $this->assertEquals( trim( $this->database->makeList(
 964                         $list, $mode
 965                 ) ), $sqlText );
 966         }
 967
 968         public static function provideMakeList() {
 969                 return [
 970                         [
 971                                 [ 'value', 'value2' ],
 972                                 LIST_COMMA,
 973                                 "'value','value2'"
 974                         ],
 975                         [
 976                                 [ 'field', 'field2' ],
 977                                 LIST_NAMES,
 978                                 "field,field2"
 979                         ],
 980                         [
 981                                 [ 'field' => 'value', 'field2' => 'value2' ],
 982                                 LIST_AND,
 983                                 "field = 'value' AND field2 = 'value2'"
 984                         ],
 985                         [
 986                                 [ 'field' => null, "field2 != 'value2'" ],
 987                                 LIST_AND,
 988                                 "field IS NULL AND (field2 != 'value2')"
 989                         ],
 990                         [
 991                                 [ 'field' => [ 'value', null, 'value2' ], 'field2' => 'value2' ],
 992                                 LIST_AND,
 993                                 "(field IN ('value','value2')  OR field IS NULL) AND field2 = 'value2'"
 994                         ],
 995                         [
 996                                 [ 'field' => [ null ], 'field2' => null ],
 997                                 LIST_AND,
 998                                 "field IS NULL AND field2 IS NULL"
 999                         ],
1000                         [
1001                                 [ 'field' => 'value', 'field2' => 'value2' ],
1002                                 LIST_OR,
1003                                 "field = 'value' OR field2 = 'value2'"
1004                         ],
1005                         [
1006                                 [ 'field' => 'value', 'field2' => null ],
1007                                 LIST_OR,
1008                                 "field = 'value' OR field2 IS NULL"
1009                         ],
1010                         [
1011                                 [ 'field' => [ 'value', 'value2' ], 'field2' => [ 'value' ] ],
1012                                 LIST_OR,
1013                                 "field IN ('value','value2')  OR field2 = 'value'"
1014                         ],
1015                         [
1016                                 [ 'field' => [ null, 'value', null, 'value2' ], "field2 != 'value2'" ],
1017                                 LIST_OR,
1018                                 "(field IN ('value','value2')  OR field IS NULL) OR (field2 != 'value2')"
1019                         ],
1020                         [
1021                                 [ 'field' => 'value', 'field2' => 'value2' ],
1022                                 LIST_SET,
1023                                 "field = 'value',field2 = 'value2'"
1024                         ],
1025                         [
1026                                 [ 'field' => 'value', 'field2' => null ],
1027                                 LIST_SET,
1028                                 "field = 'value',field2 = NULL"
1029                         ],
1030                         [
1031                                 [ 'field' => 'value', "field2 != 'value2'" ],
1032                                 LIST_SET,
1033                                 "field = 'value',field2 != 'value2'"
1034                         ],
1035                 ];
1036         }
1037
1038         public function testSessionTempTables() {
1039                 $temp1 = $this->database->tableName( 'tmp_table_1' );
1040                 $temp2 = $this->database->tableName( 'tmp_table_2' );
1041                 $temp3 = $this->database->tableName( 'tmp_table_3' );
1042
1043                 $this->database->query( "CREATE TEMPORARY TABLE $temp1 LIKE orig_tbl", __METHOD__ );
1044                 $this->database->query( "CREATE TEMPORARY TABLE $temp2 LIKE orig_tbl", __METHOD__ );
1045                 $this->database->query( "CREATE TEMPORARY TABLE $temp3 LIKE orig_tbl", __METHOD__ );
1046
1047                 $this->assertTrue( $this->database->tableExists( "tmp_table_1", __METHOD__ ) );
1048                 $this->assertTrue( $this->database->tableExists( "tmp_table_2", __METHOD__ ) );
1049                 $this->assertTrue( $this->database->tableExists( "tmp_table_3", __METHOD__ ) );
1050
1051                 $this->database->dropTable( 'tmp_table_1', __METHOD__ );
1052                 $this->database->dropTable( 'tmp_table_2', __METHOD__ );
1053                 $this->database->dropTable( 'tmp_table_3', __METHOD__ );
1054
1055                 $this->assertFalse( $this->database->tableExists( "tmp_table_1", __METHOD__ ) );
1056                 $this->assertFalse( $this->database->tableExists( "tmp_table_2", __METHOD__ ) );
1057                 $this->assertFalse( $this->database->tableExists( "tmp_table_3", __METHOD__ ) );
1058
1059                 $this->database->query( "CREATE TEMPORARY TABLE tmp_table_1 LIKE orig_tbl", __METHOD__ );
1060                 $this->database->query( "CREATE TEMPORARY TABLE 'tmp_table_2' LIKE orig_tbl", __METHOD__ );
1061                 $this->database->query( "CREATE TEMPORARY TABLE `tmp_table_3` LIKE orig_tbl", __METHOD__ );
1062
1063                 $this->assertTrue( $this->database->tableExists( "tmp_table_1", __METHOD__ ) );
1064                 $this->assertTrue( $this->database->tableExists( "tmp_table_2", __METHOD__ ) );
1065                 $this->assertTrue( $this->database->tableExists( "tmp_table_3", __METHOD__ ) );
1066
1067                 $this->database->query( "DROP TEMPORARY TABLE tmp_table_1 LIKE orig_tbl", __METHOD__ );
1068                 $this->database->query( "DROP TEMPORARY TABLE 'tmp_table_2' LIKE orig_tbl", __METHOD__ );
1069                 $this->database->query( "DROP TABLE `tmp_table_3` LIKE orig_tbl", __METHOD__ );
1070
1071                 $this->assertFalse( $this->database->tableExists( "tmp_table_1", __METHOD__ ) );
1072                 $this->assertFalse( $this->database->tableExists( "tmp_table_2", __METHOD__ ) );
1073                 $this->assertFalse( $this->database->tableExists( "tmp_table_3", __METHOD__ ) );
1074         }
1075 }