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