MediaWiki  REL1_22
DatabaseSQLTest.php
Go to the documentation of this file.
00001 <?php
00002 
00007 class DatabaseSQLTest extends MediaWikiTestCase {
00008 
00012     private $database;
00013 
00014     protected function setUp() {
00015         parent::setUp();
00016         $this->database = new DatabaseTestHelper( __CLASS__ );
00017     }
00018 
00019     protected function assertLastSql( $sqlText ) {
00020         $this->assertEquals(
00021             $this->database->getLastSqls(),
00022             $sqlText
00023         );
00024     }
00025 
00030     public function testSelect( $sql, $sqlText ) {
00031         $this->database->select(
00032             $sql['tables'],
00033             $sql['fields'],
00034             isset( $sql['conds'] ) ? $sql['conds'] : array(),
00035             __METHOD__,
00036             isset( $sql['options'] ) ? $sql['options'] : array(),
00037             isset( $sql['join_conds'] ) ? $sql['join_conds'] : array()
00038         );
00039         $this->assertLastSql( $sqlText );
00040     }
00041 
00042     public static function provideSelect() {
00043         return array(
00044             array(
00045                 array(
00046                     'tables' => 'table',
00047                     'fields' => array( 'field', 'alias' => 'field2' ),
00048                     'conds' => array( 'alias' => 'text' ),
00049                 ),
00050                 "SELECT field,field2 AS alias " .
00051                     "FROM table " .
00052                     "WHERE alias = 'text'"
00053             ),
00054             array(
00055                 array(
00056                     'tables' => 'table',
00057                     'fields' => array( 'field', 'alias' => 'field2' ),
00058                     'conds' => array( 'alias' => 'text' ),
00059                     'options' => array( 'LIMIT' => 1, 'ORDER BY' => 'field' ),
00060                 ),
00061                 "SELECT field,field2 AS alias " .
00062                     "FROM table " .
00063                     "WHERE alias = 'text' " .
00064                     "ORDER BY field " .
00065                     "LIMIT 1"
00066             ),
00067             array(
00068                 array(
00069                     'tables' => array( 'table', 't2' => 'table2' ),
00070                     'fields' => array( 'tid', 'field', 'alias' => 'field2', 't2.id' ),
00071                     'conds' => array( 'alias' => 'text' ),
00072                     'options' => array( 'LIMIT' => 1, 'ORDER BY' => 'field' ),
00073                     'join_conds' => array( 't2' => array(
00074                         'LEFT JOIN', 'tid = t2.id'
00075                     ) ),
00076                 ),
00077                 "SELECT tid,field,field2 AS alias,t2.id " .
00078                     "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
00079                     "WHERE alias = 'text' " .
00080                     "ORDER BY field " .
00081                     "LIMIT 1"
00082             ),
00083             array(
00084                 array(
00085                     'tables' => array( 'table', 't2' => 'table2' ),
00086                     'fields' => array( 'tid', 'field', 'alias' => 'field2', 't2.id' ),
00087                     'conds' => array( 'alias' => 'text' ),
00088                     'options' => array( 'LIMIT' => 1, 'GROUP BY' => 'field', 'HAVING' => 'COUNT(*) > 1' ),
00089                     'join_conds' => array( 't2' => array(
00090                         'LEFT JOIN', 'tid = t2.id'
00091                     ) ),
00092                 ),
00093                 "SELECT tid,field,field2 AS alias,t2.id " .
00094                     "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
00095                     "WHERE alias = 'text' " .
00096                     "GROUP BY field HAVING COUNT(*) > 1 " .
00097                     "LIMIT 1"
00098             ),
00099             array(
00100                 array(
00101                     'tables' => array( 'table', 't2' => 'table2' ),
00102                     'fields' => array( 'tid', 'field', 'alias' => 'field2', 't2.id' ),
00103                     'conds' => array( 'alias' => 'text' ),
00104                     'options' => array( 'LIMIT' => 1, 'GROUP BY' => array( 'field', 'field2' ), 'HAVING' => array( 'COUNT(*) > 1', 'field' => 1 ) ),
00105                     'join_conds' => array( 't2' => array(
00106                         'LEFT JOIN', 'tid = t2.id'
00107                     ) ),
00108                 ),
00109                 "SELECT tid,field,field2 AS alias,t2.id " .
00110                     "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " .
00111                     "WHERE alias = 'text' " .
00112                     "GROUP BY field,field2 HAVING (COUNT(*) > 1) AND field = '1' " .
00113                     "LIMIT 1"
00114             ),
00115             array(
00116                 array(
00117                     'tables' => array( 'table' ),
00118                     'fields' => array( 'alias' => 'field' ),
00119                     'conds' => array( 'alias' => array( 1, 2, 3, 4 ) ),
00120                 ),
00121                 "SELECT field AS alias " .
00122                     "FROM table " .
00123                     "WHERE alias IN ('1','2','3','4')"
00124             ),
00125         );
00126     }
00127 
00132     public function testUpdate( $sql, $sqlText ) {
00133         $this->database->update(
00134             $sql['table'],
00135             $sql['values'],
00136             $sql['conds'],
00137             __METHOD__,
00138             isset( $sql['options'] ) ? $sql['options'] : array()
00139         );
00140         $this->assertLastSql( $sqlText );
00141     }
00142 
00143     public static function provideUpdate() {
00144         return array(
00145             array(
00146                 array(
00147                     'table' => 'table',
00148                     'values' => array( 'field' => 'text', 'field2' => 'text2' ),
00149                     'conds' => array( 'alias' => 'text' ),
00150                 ),
00151                 "UPDATE table " .
00152                     "SET field = 'text'" .
00153                     ",field2 = 'text2' " .
00154                     "WHERE alias = 'text'"
00155             ),
00156             array(
00157                 array(
00158                     'table' => 'table',
00159                     'values' => array( 'field = other', 'field2' => 'text2' ),
00160                     'conds' => array( 'id' => '1' ),
00161                 ),
00162                 "UPDATE table " .
00163                     "SET field = other" .
00164                     ",field2 = 'text2' " .
00165                     "WHERE id = '1'"
00166             ),
00167             array(
00168                 array(
00169                     'table' => 'table',
00170                     'values' => array( 'field = other', 'field2' => 'text2' ),
00171                     'conds' => '*',
00172                 ),
00173                 "UPDATE table " .
00174                     "SET field = other" .
00175                     ",field2 = 'text2'"
00176             ),
00177         );
00178     }
00179 
00184     public function testDelete( $sql, $sqlText ) {
00185         $this->database->delete(
00186             $sql['table'],
00187             $sql['conds'],
00188             __METHOD__
00189         );
00190         $this->assertLastSql( $sqlText );
00191     }
00192 
00193     public static function provideDelete() {
00194         return array(
00195             array(
00196                 array(
00197                     'table' => 'table',
00198                     'conds' => array( 'alias' => 'text' ),
00199                 ),
00200                 "DELETE FROM table " .
00201                     "WHERE alias = 'text'"
00202             ),
00203             array(
00204                 array(
00205                     'table' => 'table',
00206                     'conds' => '*',
00207                 ),
00208                 "DELETE FROM table"
00209             ),
00210         );
00211     }
00212 
00217     public function testUpsert( $sql, $sqlText ) {
00218         $this->database->upsert(
00219             $sql['table'],
00220             $sql['rows'],
00221             $sql['uniqueIndexes'],
00222             $sql['set'],
00223             __METHOD__
00224         );
00225         $this->assertLastSql( $sqlText );
00226     }
00227 
00228     public static function provideUpsert() {
00229         return array(
00230             array(
00231                 array(
00232                     'table' => 'upsert_table',
00233                     'rows' => array( 'field' => 'text', 'field2' => 'text2' ),
00234                     'uniqueIndexes' => array( 'field' ),
00235                     'set' => array( 'field' => 'set' ),
00236                 ),
00237                 "BEGIN; " .
00238                     "UPDATE upsert_table " .
00239                     "SET field = 'set' " .
00240                     "WHERE ((field = 'text')); " .
00241                     "INSERT IGNORE INTO upsert_table " .
00242                     "(field,field2) " .
00243                     "VALUES ('text','text2'); " .
00244                     "COMMIT"
00245             ),
00246         );
00247     }
00248 
00253     public function testDeleteJoin( $sql, $sqlText ) {
00254         $this->database->deleteJoin(
00255             $sql['delTable'],
00256             $sql['joinTable'],
00257             $sql['delVar'],
00258             $sql['joinVar'],
00259             $sql['conds'],
00260             __METHOD__
00261         );
00262         $this->assertLastSql( $sqlText );
00263     }
00264 
00265     public static function provideDeleteJoin() {
00266         return array(
00267             array(
00268                 array(
00269                     'delTable' => 'table',
00270                     'joinTable' => 'table_join',
00271                     'delVar' => 'field',
00272                     'joinVar' => 'field_join',
00273                     'conds' => array( 'alias' => 'text' ),
00274                 ),
00275                 "DELETE FROM table " .
00276                     "WHERE field IN (" .
00277                     "SELECT field_join FROM table_join WHERE alias = 'text'" .
00278                     ")"
00279             ),
00280             array(
00281                 array(
00282                     'delTable' => 'table',
00283                     'joinTable' => 'table_join',
00284                     'delVar' => 'field',
00285                     'joinVar' => 'field_join',
00286                     'conds' => '*',
00287                 ),
00288                 "DELETE FROM table " .
00289                     "WHERE field IN (" .
00290                     "SELECT field_join FROM table_join " .
00291                     ")"
00292             ),
00293         );
00294     }
00295 
00300     public function testInsert( $sql, $sqlText ) {
00301         $this->database->insert(
00302             $sql['table'],
00303             $sql['rows'],
00304             __METHOD__,
00305             isset( $sql['options'] ) ? $sql['options'] : array()
00306         );
00307         $this->assertLastSql( $sqlText );
00308     }
00309 
00310     public static function provideInsert() {
00311         return array(
00312             array(
00313                 array(
00314                     'table' => 'table',
00315                     'rows' => array( 'field' => 'text', 'field2' => 2 ),
00316                 ),
00317                 "INSERT INTO table " .
00318                     "(field,field2) " .
00319                     "VALUES ('text','2')"
00320             ),
00321             array(
00322                 array(
00323                     'table' => 'table',
00324                     'rows' => array( 'field' => 'text', 'field2' => 2 ),
00325                     'options' => 'IGNORE',
00326                 ),
00327                 "INSERT IGNORE INTO table " .
00328                     "(field,field2) " .
00329                     "VALUES ('text','2')"
00330             ),
00331             array(
00332                 array(
00333                     'table' => 'table',
00334                     'rows' => array(
00335                         array( 'field' => 'text', 'field2' => 2 ),
00336                         array( 'field' => 'multi', 'field2' => 3 ),
00337                     ),
00338                     'options' => 'IGNORE',
00339                 ),
00340                 "INSERT IGNORE INTO table " .
00341                     "(field,field2) " .
00342                     "VALUES " .
00343                     "('text','2')," .
00344                     "('multi','3')"
00345             ),
00346         );
00347     }
00348 
00353     public function testInsertSelect( $sql, $sqlText ) {
00354         $this->database->insertSelect(
00355             $sql['destTable'],
00356             $sql['srcTable'],
00357             $sql['varMap'],
00358             $sql['conds'],
00359             __METHOD__,
00360             isset( $sql['insertOptions'] ) ? $sql['insertOptions'] : array(),
00361             isset( $sql['selectOptions'] ) ? $sql['selectOptions'] : array()
00362         );
00363         $this->assertLastSql( $sqlText );
00364     }
00365 
00366     public static function provideInsertSelect() {
00367         return array(
00368             array(
00369                 array(
00370                     'destTable' => 'insert_table',
00371                     'srcTable' => 'select_table',
00372                     'varMap' => array( 'field_insert' => 'field_select', 'field' => 'field2' ),
00373                     'conds' => '*',
00374                 ),
00375                 "INSERT INTO insert_table " .
00376                     "(field_insert,field) " .
00377                     "SELECT field_select,field2 " .
00378                     "FROM select_table"
00379             ),
00380             array(
00381                 array(
00382                     'destTable' => 'insert_table',
00383                     'srcTable' => 'select_table',
00384                     'varMap' => array( 'field_insert' => 'field_select', 'field' => 'field2' ),
00385                     'conds' => array( 'field' => 2 ),
00386                 ),
00387                 "INSERT INTO insert_table " .
00388                     "(field_insert,field) " .
00389                     "SELECT field_select,field2 " .
00390                     "FROM select_table " .
00391                     "WHERE field = '2'"
00392             ),
00393             array(
00394                 array(
00395                     'destTable' => 'insert_table',
00396                     'srcTable' => 'select_table',
00397                     'varMap' => array( 'field_insert' => 'field_select', 'field' => 'field2' ),
00398                     'conds' => array( 'field' => 2 ),
00399                     'insertOptions' => 'IGNORE',
00400                     'selectOptions' => array( 'ORDER BY' => 'field' ),
00401                 ),
00402                 "INSERT IGNORE INTO insert_table " .
00403                     "(field_insert,field) " .
00404                     "SELECT field_select,field2 " .
00405                     "FROM select_table " .
00406                     "WHERE field = '2' " .
00407                     "ORDER BY field"
00408             ),
00409         );
00410     }
00411 
00416     public function testReplace( $sql, $sqlText ) {
00417         $this->database->replace(
00418             $sql['table'],
00419             $sql['uniqueIndexes'],
00420             $sql['rows'],
00421             __METHOD__
00422         );
00423         $this->assertLastSql( $sqlText );
00424     }
00425 
00426     public static function provideReplace() {
00427         return array(
00428             array(
00429                 array(
00430                     'table' => 'replace_table',
00431                     'uniqueIndexes' => array( 'field' ),
00432                     'rows' => array( 'field' => 'text', 'field2' => 'text2' ),
00433                 ),
00434                 "DELETE FROM replace_table " .
00435                     "WHERE ( field='text' ); " .
00436                     "INSERT INTO replace_table " .
00437                     "(field,field2) " .
00438                     "VALUES ('text','text2')"
00439             ),
00440             array(
00441                 array(
00442                     'table' => 'module_deps',
00443                     'uniqueIndexes' => array( array( 'md_module', 'md_skin' ) ),
00444                     'rows' => array(
00445                         'md_module' => 'module',
00446                         'md_skin' => 'skin',
00447                         'md_deps' => 'deps',
00448                     ),
00449                 ),
00450                 "DELETE FROM module_deps " .
00451                     "WHERE ( md_module='module' AND md_skin='skin' ); " .
00452                     "INSERT INTO module_deps " .
00453                     "(md_module,md_skin,md_deps) " .
00454                     "VALUES ('module','skin','deps')"
00455             ),
00456             array(
00457                 array(
00458                     'table' => 'module_deps',
00459                     'uniqueIndexes' => array( array( 'md_module', 'md_skin' ) ),
00460                     'rows' => array(
00461                         array(
00462                             'md_module' => 'module',
00463                             'md_skin' => 'skin',
00464                             'md_deps' => 'deps',
00465                         ), array(
00466                             'md_module' => 'module2',
00467                             'md_skin' => 'skin2',
00468                             'md_deps' => 'deps2',
00469                         ),
00470                     ),
00471                 ),
00472                 "DELETE FROM module_deps " .
00473                     "WHERE ( md_module='module' AND md_skin='skin' ); " .
00474                     "INSERT INTO module_deps " .
00475                     "(md_module,md_skin,md_deps) " .
00476                     "VALUES ('module','skin','deps'); " .
00477                     "DELETE FROM module_deps " .
00478                     "WHERE ( md_module='module2' AND md_skin='skin2' ); " .
00479                     "INSERT INTO module_deps " .
00480                     "(md_module,md_skin,md_deps) " .
00481                     "VALUES ('module2','skin2','deps2')"
00482             ),
00483             array(
00484                 array(
00485                     'table' => 'module_deps',
00486                     'uniqueIndexes' => array( 'md_module', 'md_skin' ),
00487                     'rows' => array(
00488                         array(
00489                             'md_module' => 'module',
00490                             'md_skin' => 'skin',
00491                             'md_deps' => 'deps',
00492                         ), array(
00493                             'md_module' => 'module2',
00494                             'md_skin' => 'skin2',
00495                             'md_deps' => 'deps2',
00496                         ),
00497                     ),
00498                 ),
00499                 "DELETE FROM module_deps " .
00500                     "WHERE ( md_module='module' ) OR ( md_skin='skin' ); " .
00501                     "INSERT INTO module_deps " .
00502                     "(md_module,md_skin,md_deps) " .
00503                     "VALUES ('module','skin','deps'); " .
00504                     "DELETE FROM module_deps " .
00505                     "WHERE ( md_module='module2' ) OR ( md_skin='skin2' ); " .
00506                     "INSERT INTO module_deps " .
00507                     "(md_module,md_skin,md_deps) " .
00508                     "VALUES ('module2','skin2','deps2')"
00509             ),
00510             array(
00511                 array(
00512                     'table' => 'module_deps',
00513                     'uniqueIndexes' => array(),
00514                     'rows' => array(
00515                         'md_module' => 'module',
00516                         'md_skin' => 'skin',
00517                         'md_deps' => 'deps',
00518                     ),
00519                 ),
00520                 "INSERT INTO module_deps " .
00521                     "(md_module,md_skin,md_deps) " .
00522                     "VALUES ('module','skin','deps')"
00523             ),
00524         );
00525     }
00526 
00531     public function testNativeReplace( $sql, $sqlText ) {
00532         $this->database->nativeReplace(
00533             $sql['table'],
00534             $sql['rows'],
00535             __METHOD__
00536         );
00537         $this->assertLastSql( $sqlText );
00538     }
00539 
00540     public static function provideNativeReplace() {
00541         return array(
00542             array(
00543                 array(
00544                     'table' => 'replace_table',
00545                     'rows' => array( 'field' => 'text', 'field2' => 'text2' ),
00546                 ),
00547                 "REPLACE INTO replace_table " .
00548                     "(field,field2) " .
00549                     "VALUES ('text','text2')"
00550             ),
00551         );
00552     }
00553 
00558     public function testConditional( $sql, $sqlText ) {
00559         $this->assertEquals( trim( $this->database->conditional(
00560             $sql['conds'],
00561             $sql['true'],
00562             $sql['false']
00563         ) ), $sqlText );
00564     }
00565 
00566     public static function provideConditional() {
00567         return array(
00568             array(
00569                 array(
00570                     'conds' => array( 'field' => 'text' ),
00571                     'true' => 1,
00572                     'false' => 'NULL',
00573                 ),
00574                 "(CASE WHEN field = 'text' THEN 1 ELSE NULL END)"
00575             ),
00576             array(
00577                 array(
00578                     'conds' => array( 'field' => 'text', 'field2' => 'anothertext' ),
00579                     'true' => 1,
00580                     'false' => 'NULL',
00581                 ),
00582                 "(CASE WHEN field = 'text' AND field2 = 'anothertext' THEN 1 ELSE NULL END)"
00583             ),
00584             array(
00585                 array(
00586                     'conds' => 'field=1',
00587                     'true' => 1,
00588                     'false' => 'NULL',
00589                 ),
00590                 "(CASE WHEN field=1 THEN 1 ELSE NULL END)"
00591             ),
00592         );
00593     }
00594 
00599     public function testBuildConcat( $stringList, $sqlText ) {
00600         $this->assertEquals( trim( $this->database->buildConcat(
00601             $stringList
00602         ) ), $sqlText );
00603     }
00604 
00605     public static function provideBuildConcat() {
00606         return array(
00607             array(
00608                 array( 'field', 'field2' ),
00609                 "CONCAT(field,field2)"
00610             ),
00611             array(
00612                 array( "'test'", 'field2' ),
00613                 "CONCAT('test',field2)"
00614             ),
00615         );
00616     }
00617 
00622     public function testBuildLike( $array, $sqlText ) {
00623         $this->assertEquals( trim( $this->database->buildLike(
00624             $array
00625         ) ), $sqlText );
00626     }
00627 
00628     public static function provideBuildLike() {
00629         return array(
00630             array(
00631                 'text',
00632                 "LIKE 'text'"
00633             ),
00634             array(
00635                 array( 'text', new LikeMatch( '%' ) ),
00636                 "LIKE 'text%'"
00637             ),
00638             array(
00639                 array( 'text', new LikeMatch( '%' ), 'text2' ),
00640                 "LIKE 'text%text2'"
00641             ),
00642             array(
00643                 array( 'text', new LikeMatch( '_' ) ),
00644                 "LIKE 'text_'"
00645             ),
00646         );
00647     }
00648 
00653     public function testUnionQueries( $sql, $sqlText ) {
00654         $this->assertEquals( trim( $this->database->unionQueries(
00655             $sql['sqls'],
00656             $sql['all']
00657         ) ), $sqlText );
00658     }
00659 
00660     public static function provideUnionQueries() {
00661         return array(
00662             array(
00663                 array(
00664                     'sqls' => array( 'RAW SQL', 'RAW2SQL' ),
00665                     'all' => true,
00666                 ),
00667                 "(RAW SQL) UNION ALL (RAW2SQL)"
00668             ),
00669             array(
00670                 array(
00671                     'sqls' => array( 'RAW SQL', 'RAW2SQL' ),
00672                     'all' => false,
00673                 ),
00674                 "(RAW SQL) UNION (RAW2SQL)"
00675             ),
00676             array(
00677                 array(
00678                     'sqls' => array( 'RAW SQL', 'RAW2SQL', 'RAW3SQL' ),
00679                     'all' => false,
00680                 ),
00681                 "(RAW SQL) UNION (RAW2SQL) UNION (RAW3SQL)"
00682             ),
00683         );
00684     }
00685 
00689     public function testTransactionCommit() {
00690         $this->database->begin( __METHOD__ );
00691         $this->database->commit( __METHOD__ );
00692         $this->assertLastSql( 'BEGIN; COMMIT' );
00693     }
00694 
00698     public function testTransactionRollback() {
00699         $this->database->begin( __METHOD__ );
00700         $this->database->rollback( __METHOD__ );
00701         $this->assertLastSql( 'BEGIN; ROLLBACK' );
00702     }
00703 
00707     public function testDropTable() {
00708         $this->database->setExistingTables( array( 'table' ) );
00709         $this->database->dropTable( 'table', __METHOD__ );
00710         $this->assertLastSql( 'DROP TABLE table' );
00711     }
00712 
00716     public function testDropNonExistingTable() {
00717         $this->assertFalse(
00718             $this->database->dropTable( 'non_existing', __METHOD__ )
00719         );
00720     }
00721 }