tests/phpunit/includes/db/DatabaseSQLTest.php

   1 <?php
   2
   3 /**
   4  * Test the abstract database layer
   5  * This is a non DBMS depending test.
   6  */
   7 class DatabaseSQLTest extends MediaWikiTestCase {
   8
   9         private $database;
  10
  11         protected function setUp() {
  12                 parent::setUp();
  13                 $this->database = new DatabaseTestHelper( __CLASS__ );
  14         }
  15
  16         protected function assertLastSql( $sqlText ) {
  17                 $this->assertEquals(
  18                         $this->database->getLastSqls(),
  19                         $sqlText
  20                 );
  21         }
  22
  23         /**
  24          * @dataProvider provideSelect
  25          */
  26         function testSelect( $sql, $sqlText ) {
  27                 $this->database->select(
  28                         $sql['tables'],
  29                         $sql['fields'],
  30                         isset( $sql['conds'] ) ? $sql['conds'] : array(),
  31                         __METHOD__,
  32                         isset( $sql['options'] ) ? $sql['options'] : array(),
  33                         isset( $sql['join_conds'] ) ? $sql['join_conds'] : array()
  34                 );
  35                 $this->assertLastSql( $sqlText );
  36         }
  37
  38         public static function provideSelect() {
  39                 return array(
  40                         array(
  41                                 array(
  42                                         'tables' => 'table',
  43                                         'fields' => array( 'field', 'alias' => 'field2' ),
  44                                         'conds' => array( 'alias' => 'text' ),
  45                                 ),
  46                                 "SELECT field,field2 AS alias " .
  47                                         "FROM table " .
  48                                         "WHERE alias = 'text'"
  49                         ),
  50                         array(
  51                                 array(
  52                                         'tables' => 'table',
  53                                         'fields' => array( 'field', 'alias' => 'field2' ),
  54                                         'conds' => array( 'alias' => 'text' ),
  55                                         'options' => array( 'LIMIT' => 1, 'ORDER BY' => 'field' ),
  56                                 ),
  57                                 "SELECT field,field2 AS alias " .
  58                                         "FROM table " .
  59                                         "WHERE alias = 'text' " .
  60                                         "ORDER BY field " .
  61                                         "LIMIT 1"
  62                         ),
  63                         array(
  64                                 array(
  65                                         'tables' => array( 'table', 't2' => 'table2' ),
  66                                         'fields' => array( 'tid', 'field', 'alias' => 'field2', 't2.id' ),
  67                                         'conds' => array( 'alias' => 'text' ),
  68                                         'options' => array( 'LIMIT' => 1, 'ORDER BY' => 'field' ),
  69                                         'join_conds' => array( 't2' => array(
  70                                                 'LEFT JOIN', 'tid = t2.id'
  71                                         ) ),
  72                                 ),
  73                                 "SELECT tid,field,field2 AS alias,t2.id " .
  74                                         "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
  75                                         "WHERE alias = 'text' " .
  76                                         "ORDER BY field " .
  77                                         "LIMIT 1"
  78                         ),
  79                         array(
  80                                 array(
  81                                         'tables' => array( 'table', 't2' => 'table2' ),
  82                                         'fields' => array( 'tid', 'field', 'alias' => 'field2', 't2.id' ),
  83                                         'conds' => array( 'alias' => 'text' ),
  84                                         'options' => array( 'LIMIT' => 1, 'GROUP BY' => 'field', 'HAVING' => 'COUNT(*) > 1' ),
  85                                         'join_conds' => array( 't2' => array(
  86                                                 'LEFT JOIN', 'tid = t2.id'
  87                                         ) ),
  88                                 ),
  89                                 "SELECT tid,field,field2 AS alias,t2.id " .
  90                                         "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
  91                                         "WHERE alias = 'text' " .
  92                                         "GROUP BY field HAVING COUNT(*) > 1 " .
  93                                         "LIMIT 1"
  94                         ),
  95                         array(
  96                                 array(
  97                                         'tables' => array( 'table', 't2' => 'table2' ),
  98                                         'fields' => array( 'tid', 'field', 'alias' => 'field2', 't2.id' ),
  99                                         'conds' => array( 'alias' => 'text' ),
 100                                         'options' => array( 'LIMIT' => 1, 'GROUP BY' => array( 'field', 'field2' ), 'HAVING' => array( 'COUNT(*) > 1', 'field' => 1 ) ),
 101                                         'join_conds' => array( 't2' => array(
 102                                                 'LEFT JOIN', 'tid = t2.id'
 103                                         ) ),
 104                                 ),
 105                                 "SELECT tid,field,field2 AS alias,t2.id " .
 106                                         "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
 107                                         "WHERE alias = 'text' " .
 108                                         "GROUP BY field,field2 HAVING (COUNT(*) > 1) AND field = '1' " .
 109                                         "LIMIT 1"
 110                         ),
 111                         array(
 112                                 array(
 113                                         'tables' => array( 'table' ),
 114                                         'fields' => array( 'alias' => 'field' ),
 115                                         'conds' => array( 'alias' => array( 1, 2, 3, 4 ) ),
 116                                 ),
 117                                 "SELECT field AS alias " .
 118                                         "FROM table " .
 119                                         "WHERE alias IN ('1','2','3','4')"
 120                         ),
 121                 );
 122         }
 123
 124         /**
 125          * @dataProvider provideUpdate
 126          */
 127         function testUpdate( $sql, $sqlText ) {
 128                 $this->database->update(
 129                         $sql['table'],
 130                         $sql['values'],
 131                         $sql['conds'],
 132                         __METHOD__,
 133                         isset( $sql['options'] ) ? $sql['options'] : array()
 134                 );
 135                 $this->assertLastSql( $sqlText );
 136         }
 137
 138         public static function provideUpdate() {
 139                 return array(
 140                         array(
 141                                 array(
 142                                         'table' => 'table',
 143                                         'values' => array( 'field' => 'text', 'field2' => 'text2' ),
 144                                         'conds' => array( 'alias' => 'text' ),
 145                                 ),
 146                                 "UPDATE table " .
 147                                         "SET field = 'text'" .
 148                                         ",field2 = 'text2' " .
 149                                         "WHERE alias = 'text'"
 150                         ),
 151                         array(
 152                                 array(
 153                                         'table' => 'table',
 154                                         'values' => array( 'field = other', 'field2' => 'text2' ),
 155                                         'conds' => array( 'id' => '1' ),
 156                                 ),
 157                                 "UPDATE table " .
 158                                         "SET field = other" .
 159                                         ",field2 = 'text2' " .
 160                                         "WHERE id = '1'"
 161                         ),
 162                         array(
 163                                 array(
 164                                         'table' => 'table',
 165                                         'values' => array( 'field = other', 'field2' => 'text2' ),
 166                                         'conds' => '*',
 167                                 ),
 168                                 "UPDATE table " .
 169                                         "SET field = other" .
 170                                         ",field2 = 'text2'"
 171                         ),
 172                 );
 173         }
 174
 175         /**
 176          * @dataProvider provideDelete
 177          */
 178         function testDelete( $sql, $sqlText ) {
 179                 $this->database->delete(
 180                         $sql['table'],
 181                         $sql['conds'],
 182                         __METHOD__
 183                 );
 184                 $this->assertLastSql( $sqlText );
 185         }
 186
 187         public static function provideDelete() {
 188                 return array(
 189                         array(
 190                                 array(
 191                                         'table' => 'table',
 192                                         'conds' => array( 'alias' => 'text' ),
 193                                 ),
 194                                 "DELETE FROM table " .
 195                                         "WHERE alias = 'text'"
 196                         ),
 197                         array(
 198                                 array(
 199                                         'table' => 'table',
 200                                         'conds' => '*',
 201                                 ),
 202                                 "DELETE FROM table"
 203                         ),
 204                 );
 205         }
 206
 207         /**
 208          * @dataProvider provideDeleteJoin
 209          */
 210         function testDeleteJoin( $sql, $sqlText ) {
 211                 $this->database->deleteJoin(
 212                         $sql['delTable'],
 213                         $sql['joinTable'],
 214                         $sql['delVar'],
 215                         $sql['joinVar'],
 216                         $sql['conds'],
 217                         __METHOD__
 218                 );
 219                 $this->assertLastSql( $sqlText );
 220         }
 221
 222         public static function provideDeleteJoin() {
 223                 return array(
 224                         array(
 225                                 array(
 226                                         'delTable' => 'table',
 227                                         'joinTable' => 'table_join',
 228                                         'delVar' => 'field',
 229                                         'joinVar' => 'field_join',
 230                                         'conds' => array( 'alias' => 'text' ),
 231                                 ),
 232                                 "DELETE FROM table " .
 233                                         "WHERE field IN (" .
 234                                         "SELECT field_join FROM table_join WHERE alias = 'text'" .
 235                                         ")"
 236                         ),
 237                         array(
 238                                 array(
 239                                         'delTable' => 'table',
 240                                         'joinTable' => 'table_join',
 241                                         'delVar' => 'field',
 242                                         'joinVar' => 'field_join',
 243                                         'conds' => '*',
 244                                 ),
 245                                 "DELETE FROM table " .
 246                                         "WHERE field IN (" .
 247                                         "SELECT field_join FROM table_join " .
 248                                         ")"
 249                         ),
 250                 );
 251         }
 252
 253         /**
 254          * @dataProvider provideInsert
 255          */
 256         function testInsert( $sql, $sqlText ) {
 257                 $this->database->insert(
 258                         $sql['table'],
 259                         $sql['rows'],
 260                         __METHOD__,
 261                         isset( $sql['options'] ) ? $sql['options'] : array()
 262                 );
 263                 $this->assertLastSql( $sqlText );
 264         }
 265
 266         public static function provideInsert() {
 267                 return array(
 268                         array(
 269                                 array(
 270                                         'table' => 'table',
 271                                         'rows' => array( 'field' => 'text', 'field2' => 2 ),
 272                                 ),
 273                                 "INSERT INTO table " .
 274                                         "(field,field2) " .
 275                                         "VALUES ('text','2')"
 276                         ),
 277                         array(
 278                                 array(
 279                                         'table' => 'table',
 280                                         'rows' => array( 'field' => 'text', 'field2' => 2 ),
 281                                         'options' => 'IGNORE',
 282                                 ),
 283                                 "INSERT IGNORE INTO table " .
 284                                         "(field,field2) " .
 285                                         "VALUES ('text','2')"
 286                         ),
 287                         array(
 288                                 array(
 289                                         'table' => 'table',
 290                                         'rows' => array(
 291                                                 array( 'field' => 'text', 'field2' => 2 ),
 292                                                 array( 'field' => 'multi', 'field2' => 3 ),
 293                                         ),
 294                                         'options' => 'IGNORE',
 295                                 ),
 296                                 "INSERT IGNORE INTO table " .
 297                                         "(field,field2) " .
 298                                         "VALUES " .
 299                                         "('text','2')," .
 300                                         "('multi','3')"
 301                         ),
 302                 );
 303         }
 304
 305         /**
 306          * @dataProvider provideInsertSelect
 307          */
 308         function testInsertSelect( $sql, $sqlText ) {
 309                 $this->database->insertSelect(
 310                         $sql['destTable'],
 311                         $sql['srcTable'],
 312                         $sql['varMap'],
 313                         $sql['conds'],
 314                         __METHOD__,
 315                         isset( $sql['insertOptions'] ) ? $sql['insertOptions'] : array(),
 316                         isset( $sql['selectOptions'] ) ? $sql['selectOptions'] : array()
 317                 );
 318                 $this->assertLastSql( $sqlText );
 319         }
 320
 321         public static function provideInsertSelect() {
 322                 return array(
 323                         array(
 324                                 array(
 325                                         'destTable' => 'insert_table',
 326                                         'srcTable' => 'select_table',
 327                                         'varMap' => array( 'field_insert' => 'field_select', 'field' => 'field2' ),
 328                                         'conds' => '*',
 329                                 ),
 330                                 "INSERT INTO insert_table " .
 331                                         "(field_insert,field) " .
 332                                         "SELECT field_select,field2 " .
 333                                         "FROM select_table"
 334                         ),
 335                         array(
 336                                 array(
 337                                         'destTable' => 'insert_table',
 338                                         'srcTable' => 'select_table',
 339                                         'varMap' => array( 'field_insert' => 'field_select', 'field' => 'field2' ),
 340                                         'conds' => array( 'field' => 2 ),
 341                                 ),
 342                                 "INSERT INTO insert_table " .
 343                                         "(field_insert,field) " .
 344                                         "SELECT field_select,field2 " .
 345                                         "FROM select_table " .
 346                                         "WHERE field = '2'"
 347                         ),
 348                         array(
 349                                 array(
 350                                         'destTable' => 'insert_table',
 351                                         'srcTable' => 'select_table',
 352                                         'varMap' => array( 'field_insert' => 'field_select', 'field' => 'field2' ),
 353                                         'conds' => array( 'field' => 2 ),
 354                                         'insertOptions' => 'IGNORE',
 355                                         'selectOptions' => array( 'ORDER BY' => 'field' ),
 356                                 ),
 357                                 "INSERT IGNORE INTO insert_table " .
 358                                         "(field_insert,field) " .
 359                                         "SELECT field_select,field2 " .
 360                                         "FROM select_table " .
 361                                         "WHERE field = '2' " .
 362                                         "ORDER BY field"
 363                         ),
 364                 );
 365         }
 366
 367         /**
 368          * @dataProvider provideReplace
 369          */
 370         function testReplace( $sql, $sqlText ) {
 371                 $this->database->replace(
 372                         $sql['table'],
 373                         $sql['uniqueIndexes'],
 374                         $sql['rows'],
 375                         __METHOD__
 376                 );
 377                 $this->assertLastSql( $sqlText );
 378         }
 379
 380         public static function provideReplace() {
 381                 return array(
 382                         array(
 383                                 array(
 384                                         'table' => 'replace_table',
 385                                         'uniqueIndexes' => array( 'field' ),
 386                                         'rows' => array( 'field' => 'text', 'field2' => 'text2' ),
 387                                 ),
 388                                 "DELETE FROM replace_table " .
 389                                         "WHERE ( field='text' ); " .
 390                                         "INSERT INTO replace_table " .
 391                                         "(field,field2) " .
 392                                         "VALUES ('text','text2')"
 393                         ),
 394                         array(
 395                                 array(
 396                                         'table' => 'module_deps',
 397                                         'uniqueIndexes' => array( array( 'md_module', 'md_skin' ) ),
 398                                         'rows' => array(
 399                                                 'md_module' => 'module',
 400                                                 'md_skin' => 'skin',
 401                                                 'md_deps' => 'deps',
 402                                         ),
 403                                 ),
 404                                 "DELETE FROM module_deps " .
 405                                         "WHERE ( md_module='module' AND md_skin='skin' ); " .
 406                                         "INSERT INTO module_deps " .
 407                                         "(md_module,md_skin,md_deps) " .
 408                                         "VALUES ('module','skin','deps')"
 409                         ),
 410                         array(
 411                                 array(
 412                                         'table' => 'module_deps',
 413                                         'uniqueIndexes' => array( array( 'md_module', 'md_skin' ) ),
 414                                         'rows' => array(
 415                                                 array(
 416                                                         'md_module' => 'module',
 417                                                         'md_skin' => 'skin',
 418                                                         'md_deps' => 'deps',
 419                                                 ), array(
 420                                                         'md_module' => 'module2',
 421                                                         'md_skin' => 'skin2',
 422                                                         'md_deps' => 'deps2',
 423                                                 ),
 424                                         ),
 425                                 ),
 426                                 "DELETE FROM module_deps " .
 427                                         "WHERE ( md_module='module' AND md_skin='skin' ); " .
 428                                         "INSERT INTO module_deps " .
 429                                         "(md_module,md_skin,md_deps) " .
 430                                         "VALUES ('module','skin','deps'); " .
 431                                         "DELETE FROM module_deps " .
 432                                         "WHERE ( md_module='module2' AND md_skin='skin2' ); " .
 433                                         "INSERT INTO module_deps " .
 434                                         "(md_module,md_skin,md_deps) " .
 435                                         "VALUES ('module2','skin2','deps2')"
 436                         ),
 437                         array(
 438                                 array(
 439                                         'table' => 'module_deps',
 440                                         'uniqueIndexes' => array( 'md_module', 'md_skin' ),
 441                                         'rows' => array(
 442                                                 array(
 443                                                         'md_module' => 'module',
 444                                                         'md_skin' => 'skin',
 445                                                         'md_deps' => 'deps',
 446                                                 ), array(
 447                                                         'md_module' => 'module2',
 448                                                         'md_skin' => 'skin2',
 449                                                         'md_deps' => 'deps2',
 450                                                 ),
 451                                         ),
 452                                 ),
 453                                 "DELETE FROM module_deps " .
 454                                         "WHERE ( md_module='module' ) OR ( md_skin='skin' ); " .
 455                                         "INSERT INTO module_deps " .
 456                                         "(md_module,md_skin,md_deps) " .
 457                                         "VALUES ('module','skin','deps'); " .
 458                                         "DELETE FROM module_deps " .
 459                                         "WHERE ( md_module='module2' ) OR ( md_skin='skin2' ); " .
 460                                         "INSERT INTO module_deps " .
 461                                         "(md_module,md_skin,md_deps) " .
 462                                         "VALUES ('module2','skin2','deps2')"
 463                         ),
 464                         array(
 465                                 array(
 466                                         'table' => 'module_deps',
 467                                         'uniqueIndexes' => array(),
 468                                         'rows' => array(
 469                                                 'md_module' => 'module',
 470                                                 'md_skin' => 'skin',
 471                                                 'md_deps' => 'deps',
 472                                         ),
 473                                 ),
 474                                 "INSERT INTO module_deps " .
 475                                         "(md_module,md_skin,md_deps) " .
 476                                         "VALUES ('module','skin','deps')"
 477                         ),
 478                 );
 479         }
 480
 481         /**
 482          * @dataProvider provideNativeReplace
 483          */
 484         function testNativeReplace( $sql, $sqlText ) {
 485                 $this->database->nativeReplace(
 486                         $sql['table'],
 487                         $sql['rows'],
 488                         __METHOD__
 489                 );
 490                 $this->assertLastSql( $sqlText );
 491         }
 492
 493         public static function provideNativeReplace() {
 494                 return array(
 495                         array(
 496                                 array(
 497                                         'table' => 'replace_table',
 498                                         'rows' => array( 'field' => 'text', 'field2' => 'text2' ),
 499                                 ),
 500                                 "REPLACE INTO replace_table " .
 501                                         "(field,field2) " .
 502                                         "VALUES ('text','text2')"
 503                         ),
 504                 );
 505         }
 506
 507         /**
 508          * @dataProvider provideConditional
 509          */
 510         function testConditional( $sql, $sqlText ) {
 511                 $this->assertEquals( trim( $this->database->conditional(
 512                         $sql['conds'],
 513                         $sql['true'],
 514                         $sql['false']
 515                 ) ), $sqlText );
 516         }
 517
 518         public static function provideConditional() {
 519                 return array(
 520                         array(
 521                                 array(
 522                                         'conds' => array( 'field' => 'text' ),
 523                                         'true' => 1,
 524                                         'false' => 'NULL',
 525                                 ),
 526                                 "(CASE WHEN field = 'text' THEN 1 ELSE NULL END)"
 527                         ),
 528                         array(
 529                                 array(
 530                                         'conds' => array( 'field' => 'text', 'field2' => 'anothertext' ),
 531                                         'true' => 1,
 532                                         'false' => 'NULL',
 533                                 ),
 534                                 "(CASE WHEN field = 'text' AND field2 = 'anothertext' THEN 1 ELSE NULL END)"
 535                         ),
 536                         array(
 537                                 array(
 538                                         'conds' => 'field=1',
 539                                         'true' => 1,
 540                                         'false' => 'NULL',
 541                                 ),
 542                                 "(CASE WHEN field=1 THEN 1 ELSE NULL END)"
 543                         ),
 544                 );
 545         }
 546
 547         /**
 548          * @dataProvider provideBuildConcat
 549          */
 550         function testBuildConcat( $stringList, $sqlText ) {
 551                 $this->assertEquals( trim( $this->database->buildConcat(
 552                         $stringList
 553                 ) ), $sqlText );
 554         }
 555
 556         public static function provideBuildConcat() {
 557                 return array(
 558                         array(
 559                                 array( 'field', 'field2' ),
 560                                 "CONCAT(field,field2)"
 561                         ),
 562                         array(
 563                                 array( "'test'", 'field2' ),
 564                                 "CONCAT('test',field2)"
 565                         ),
 566                 );
 567         }
 568
 569         /**
 570          * @dataProvider provideBuildLike
 571          */
 572         function testBuildLike( $array, $sqlText ) {
 573                 $this->assertEquals( trim( $this->database->buildLike(
 574                         $array
 575                 ) ), $sqlText );
 576         }
 577
 578         public static function provideBuildLike() {
 579                 return array(
 580                         array(
 581                                 'text',
 582                                 "LIKE 'text'"
 583                         ),
 584                         array(
 585                                 array( 'text', new LikeMatch( '%' ) ),
 586                                 "LIKE 'text%'"
 587                         ),
 588                         array(
 589                                 array( 'text', new LikeMatch( '%' ), 'text2' ),
 590                                 "LIKE 'text%text2'"
 591                         ),
 592                         array(
 593                                 array( 'text', new LikeMatch( '_' ) ),
 594                                 "LIKE 'text_'"
 595                         ),
 596                 );
 597         }
 598
 599         /**
 600          * @dataProvider provideUnionQueries
 601          */
 602         function testUnionQueries( $sql, $sqlText ) {
 603                 $this->assertEquals( trim( $this->database->unionQueries(
 604                         $sql['sqls'],
 605                         $sql['all']
 606                 ) ), $sqlText );
 607         }
 608
 609         public static function provideUnionQueries() {
 610                 return array(
 611                         array(
 612                                 array(
 613                                         'sqls' => array( 'RAW SQL', 'RAW2SQL' ),
 614                                         'all' => true,
 615                                 ),
 616                                 "(RAW SQL) UNION ALL (RAW2SQL)"
 617                         ),
 618                         array(
 619                                 array(
 620                                         'sqls' => array( 'RAW SQL', 'RAW2SQL' ),
 621                                         'all' => false,
 622                                 ),
 623                                 "(RAW SQL) UNION (RAW2SQL)"
 624                         ),
 625                         array(
 626                                 array(
 627                                         'sqls' => array( 'RAW SQL', 'RAW2SQL', 'RAW3SQL' ),
 628                                         'all' => false,
 629                                 ),
 630                                 "(RAW SQL) UNION (RAW2SQL) UNION (RAW3SQL)"
 631                         ),
 632                 );
 633         }
 634
 635         function testTransactionCommit() {
 636                 $this->database->begin( __METHOD__ );
 637                 $this->database->commit( __METHOD__ );
 638                 $this->assertLastSql( 'BEGIN; COMMIT' );
 639         }
 640
 641         function testTransactionRollback() {
 642                 $this->database->begin( __METHOD__ );
 643                 $this->database->rollback( __METHOD__ );
 644                 $this->assertLastSql( 'BEGIN; ROLLBACK' );
 645         }
 646
 647         function testDropTable() {
 648                 $this->database->setExistingTables( array( 'table' ) );
 649                 $this->database->dropTable( 'table', __METHOD__ );
 650                 $this->assertLastSql( 'DROP TABLE table' );
 651         }
 652
 653         function testDropNonExistingTable() {
 654                 $this->assertFalse(
 655                         $this->database->dropTable( 'non_existing', __METHOD__ )
 656                 );
 657         }
 658 }