tests/phpunit/includes/libs/rdbms/database/DatabaseSQLTest.php

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