From 302f4b0ce106b93a454dc0da022e3f4bcbb08bf5 Mon Sep 17 00:00:00 2001 From: umherirrender Date: Sat, 13 Apr 2013 16:40:04 +0200 Subject: [PATCH] Add non DBMS depending SQL tests for DatabaseBase Created a DatabaseTestHelper class, which extends DatabaseBase and implements STUBs for the interface methods and abstract methods Change-Id: I9965b3604e78b2722077a35a7b4ce62a5bcb370e --- tests/TestsAutoLoader.php | 1 + tests/phpunit/includes/db/DatabaseSQLTest.php | 568 +++++++++++++++++- .../includes/db/DatabaseTestHelper.php | 164 +++++ 3 files changed, 704 insertions(+), 29 deletions(-) create mode 100644 tests/phpunit/includes/db/DatabaseTestHelper.php diff --git a/tests/TestsAutoLoader.php b/tests/TestsAutoLoader.php index 264ba695c6..1561f8f4f6 100644 --- a/tests/TestsAutoLoader.php +++ b/tests/TestsAutoLoader.php @@ -47,6 +47,7 @@ $wgAutoloadClasses += array( //db 'ORMTableTest' => "$testDir/phpunit/includes/db/ORMTableTest.php", + 'DatabaseTestHelper' => "$testDir/phpunit/includes/db/DatabaseTestHelper.php", //Selenium 'SeleniumTestConstants' => "$testDir/selenium/SeleniumTestConstants.php", diff --git a/tests/phpunit/includes/db/DatabaseSQLTest.php b/tests/phpunit/includes/db/DatabaseSQLTest.php index 097924380e..caa3368ab6 100644 --- a/tests/phpunit/includes/db/DatabaseSQLTest.php +++ b/tests/phpunit/includes/db/DatabaseSQLTest.php @@ -2,35 +2,40 @@ /** * Test the abstract database layer - * Using Mysql for the sql at the moment TODO - * - * @group Database + * This is a non DBMS depending test. */ class DatabaseSQLTest extends MediaWikiTestCase { + private $database; + protected function setUp() { parent::setUp(); - // TODO support other DBMS or find another way to do it - if ( $this->db->getType() !== 'mysql' ) { - $this->markTestSkipped( 'No mysql database' ); - } + $this->database = new DatabaseTestHelper( __CLASS__ ); + } + + protected function assertLastSql( $sqlText ) { + $this->assertEquals( + $this->database->getLastSqls(), + $sqlText + ); } /** - * @dataProvider provideSelectSQLText + * @dataProvider provideSelect */ - function testSelectSQLText( $sql, $sqlText ) { - $this->assertEquals( trim( $this->db->selectSQLText( - isset( $sql['tables'] ) ? $sql['tables'] : array(), - isset( $sql['fields'] ) ? $sql['fields'] : array(), + function testSelect( $sql, $sqlText ) { + $this->database->select( + $sql['tables'], + $sql['fields'], isset( $sql['conds'] ) ? $sql['conds'] : array(), __METHOD__, isset( $sql['options'] ) ? $sql['options'] : array(), isset( $sql['join_conds'] ) ? $sql['join_conds'] : array() - ) ), $sqlText ); + ); + $this->assertLastSql( $sqlText ); } - public static function provideSelectSQLText() { + public static function provideSelect() { return array( array( array( @@ -38,8 +43,8 @@ class DatabaseSQLTest extends MediaWikiTestCase { 'fields' => array( 'field', 'alias' => 'field2' ), 'conds' => array( 'alias' => 'text' ), ), - "SELECT field,field2 AS alias " . - "FROM `unittest_table` " . + "SELECT field,field2 AS alias " . + "FROM table " . "WHERE alias = 'text'" ), array( @@ -49,9 +54,9 @@ class DatabaseSQLTest extends MediaWikiTestCase { 'conds' => array( 'alias' => 'text' ), 'options' => array( 'LIMIT' => 1, 'ORDER BY' => 'field' ), ), - "SELECT field,field2 AS alias " . - "FROM `unittest_table` " . - "WHERE alias = 'text' " . + "SELECT field,field2 AS alias " . + "FROM table " . + "WHERE alias = 'text' " . "ORDER BY field " . "LIMIT 1" ), @@ -65,9 +70,9 @@ class DatabaseSQLTest extends MediaWikiTestCase { 'LEFT JOIN', 'tid = t2.id' ) ), ), - "SELECT tid,field,field2 AS alias,t2.id " . - "FROM `unittest_table` LEFT JOIN `unittest_table2` `t2` ON ((tid = t2.id)) " . - "WHERE alias = 'text' " . + "SELECT tid,field,field2 AS alias,t2.id " . + "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " . + "WHERE alias = 'text' " . "ORDER BY field " . "LIMIT 1" ), @@ -81,9 +86,9 @@ class DatabaseSQLTest extends MediaWikiTestCase { 'LEFT JOIN', 'tid = t2.id' ) ), ), - "SELECT tid,field,field2 AS alias,t2.id " . - "FROM `unittest_table` LEFT JOIN `unittest_table2` `t2` ON ((tid = t2.id)) " . - "WHERE alias = 'text' " . + "SELECT tid,field,field2 AS alias,t2.id " . + "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " . + "WHERE alias = 'text' " . "GROUP BY field HAVING COUNT(*) > 1 " . "LIMIT 1" ), @@ -97,12 +102,405 @@ class DatabaseSQLTest extends MediaWikiTestCase { 'LEFT JOIN', 'tid = t2.id' ) ), ), - "SELECT tid,field,field2 AS alias,t2.id " . - "FROM `unittest_table` LEFT JOIN `unittest_table2` `t2` ON ((tid = t2.id)) " . - "WHERE alias = 'text' " . + "SELECT tid,field,field2 AS alias,t2.id " . + "FROM table LEFT JOIN table2 t2 ON ((tid = t2.id)) " . + "WHERE alias = 'text' " . "GROUP BY field,field2 HAVING (COUNT(*) > 1) AND field = '1' " . "LIMIT 1" ), + array( + array( + 'tables' => array( 'table' ), + 'fields' => array( 'alias' => 'field' ), + 'conds' => array( 'alias' => array( 1, 2, 3, 4 ) ), + ), + "SELECT field AS alias " . + "FROM table " . + "WHERE alias IN ('1','2','3','4')" + ), + ); + } + + /** + * @dataProvider provideUpdate + */ + function testUpdate( $sql, $sqlText ) { + $this->database->update( + $sql['table'], + $sql['values'], + $sql['conds'], + __METHOD__, + isset( $sql['options'] ) ? $sql['options'] : array() + ); + $this->assertLastSql( $sqlText ); + } + + public static function provideUpdate() { + return array( + array( + array( + 'table' => 'table', + 'values' => array( 'field' => 'text', 'field2' => 'text2' ), + 'conds' => array( 'alias' => 'text' ), + ), + "UPDATE table " . + "SET field = 'text'" . + ",field2 = 'text2' " . + "WHERE alias = 'text'" + ), + array( + array( + 'table' => 'table', + 'values' => array( 'field = other', 'field2' => 'text2' ), + 'conds' => array( 'id' => '1' ), + ), + "UPDATE table " . + "SET field = other" . + ",field2 = 'text2' " . + "WHERE id = '1'" + ), + array( + array( + 'table' => 'table', + 'values' => array( 'field = other', 'field2' => 'text2' ), + 'conds' => '*', + ), + "UPDATE table " . + "SET field = other" . + ",field2 = 'text2'" + ), + ); + } + + /** + * @dataProvider provideDelete + */ + function testDelete( $sql, $sqlText ) { + $this->database->delete( + $sql['table'], + $sql['conds'], + __METHOD__ + ); + $this->assertLastSql( $sqlText ); + } + + public static function provideDelete() { + return array( + array( + array( + 'table' => 'table', + 'conds' => array( 'alias' => 'text' ), + ), + "DELETE FROM table " . + "WHERE alias = 'text'" + ), + array( + array( + 'table' => 'table', + 'conds' => '*', + ), + "DELETE FROM table" + ), + ); + } + + /** + * @dataProvider provideDeleteJoin + */ + function testDeleteJoin( $sql, $sqlText ) { + $this->database->deleteJoin( + $sql['delTable'], + $sql['joinTable'], + $sql['delVar'], + $sql['joinVar'], + $sql['conds'], + __METHOD__ + ); + $this->assertLastSql( $sqlText ); + } + + public static function provideDeleteJoin() { + return array( + array( + array( + 'delTable' => 'table', + 'joinTable' => 'table_join', + 'delVar' => 'field', + 'joinVar' => 'field_join', + 'conds' => array( 'alias' => 'text' ), + ), + "DELETE FROM table " . + "WHERE field IN (" . + "SELECT field_join FROM table_join WHERE alias = 'text'" . + ")" + ), + array( + array( + 'delTable' => 'table', + 'joinTable' => 'table_join', + 'delVar' => 'field', + 'joinVar' => 'field_join', + 'conds' => '*', + ), + "DELETE FROM table " . + "WHERE field IN (" . + "SELECT field_join FROM table_join " . + ")" + ), + ); + } + + /** + * @dataProvider provideInsert + */ + function testInsert( $sql, $sqlText ) { + $this->database->insert( + $sql['table'], + $sql['rows'], + __METHOD__, + isset( $sql['options'] ) ? $sql['options'] : array() + ); + $this->assertLastSql( $sqlText ); + } + + public static function provideInsert() { + return array( + array( + array( + 'table' => 'table', + 'rows' => array( 'field' => 'text', 'field2' => 2 ), + ), + "INSERT INTO table " . + "(field,field2) " . + "VALUES ('text','2')" + ), + array( + array( + 'table' => 'table', + 'rows' => array( 'field' => 'text', 'field2' => 2 ), + 'options' => 'IGNORE', + ), + "INSERT IGNORE INTO table " . + "(field,field2) " . + "VALUES ('text','2')" + ), + array( + array( + 'table' => 'table', + 'rows' => array( + array( 'field' => 'text', 'field2' => 2 ), + array( 'field' => 'multi', 'field2' => 3 ), + ), + 'options' => 'IGNORE', + ), + "INSERT IGNORE INTO table " . + "(field,field2) " . + "VALUES " . + "('text','2')," . + "('multi','3')" + ), + ); + } + + /** + * @dataProvider provideInsertSelect + */ + function testInsertSelect( $sql, $sqlText ) { + $this->database->insertSelect( + $sql['destTable'], + $sql['srcTable'], + $sql['varMap'], + $sql['conds'], + __METHOD__, + isset( $sql['insertOptions'] ) ? $sql['insertOptions'] : array(), + isset( $sql['selectOptions'] ) ? $sql['selectOptions'] : array() + ); + $this->assertLastSql( $sqlText ); + } + + public static function provideInsertSelect() { + return array( + array( + array( + 'destTable' => 'insert_table', + 'srcTable' => 'select_table', + 'varMap' => array( 'field_insert' => 'field_select', 'field' => 'field2' ), + 'conds' => '*', + ), + "INSERT INTO insert_table " . + "(field_insert,field) " . + "SELECT field_select,field2 " . + "FROM select_table" + ), + array( + array( + 'destTable' => 'insert_table', + 'srcTable' => 'select_table', + 'varMap' => array( 'field_insert' => 'field_select', 'field' => 'field2' ), + 'conds' => array( 'field' => 2 ), + ), + "INSERT INTO insert_table " . + "(field_insert,field) " . + "SELECT field_select,field2 " . + "FROM select_table " . + "WHERE field = '2'" + ), + array( + array( + 'destTable' => 'insert_table', + 'srcTable' => 'select_table', + 'varMap' => array( 'field_insert' => 'field_select', 'field' => 'field2' ), + 'conds' => array( 'field' => 2 ), + 'insertOptions' => 'IGNORE', + 'selectOptions' => array( 'ORDER BY' => 'field' ), + ), + "INSERT IGNORE INTO insert_table " . + "(field_insert,field) " . + "SELECT field_select,field2 " . + "FROM select_table " . + "WHERE field = '2' " . + "ORDER BY field" + ), + ); + } + + /** + * @dataProvider provideReplace + */ + function testReplace( $sql, $sqlText ) { + $this->database->replace( + $sql['table'], + $sql['uniqueIndexes'], + $sql['rows'], + __METHOD__ + ); + $this->assertLastSql( $sqlText ); + } + + public static function provideReplace() { + return array( + array( + array( + 'table' => 'replace_table', + 'uniqueIndexes' => array( 'field' ), + 'rows' => array( 'field' => 'text', 'field2' => 'text2' ), + ), + "DELETE FROM replace_table " . + "WHERE ( field='text' ); " . + "INSERT INTO replace_table " . + "(field,field2) " . + "VALUES ('text','text2')" + ), + array( + array( + 'table' => 'module_deps', + 'uniqueIndexes' => array( array( 'md_module', 'md_skin' ) ), + 'rows' => array( + 'md_module' => 'module', + 'md_skin' => 'skin', + 'md_deps' => 'deps', + ), + ), + "DELETE FROM module_deps " . + "WHERE ( md_module='module' AND md_skin='skin' ); " . + "INSERT INTO module_deps " . + "(md_module,md_skin,md_deps) " . + "VALUES ('module','skin','deps')" + ), + array( + array( + 'table' => 'module_deps', + 'uniqueIndexes' => array( array( 'md_module', 'md_skin' ) ), + 'rows' => array( + array( + 'md_module' => 'module', + 'md_skin' => 'skin', + 'md_deps' => 'deps', + ), array( + 'md_module' => 'module2', + 'md_skin' => 'skin2', + 'md_deps' => 'deps2', + ), + ), + ), + "DELETE FROM module_deps " . + "WHERE ( md_module='module' AND md_skin='skin' ); " . + "INSERT INTO module_deps " . + "(md_module,md_skin,md_deps) " . + "VALUES ('module','skin','deps'); " . + "DELETE FROM module_deps " . + "WHERE ( md_module='module2' AND md_skin='skin2' ); " . + "INSERT INTO module_deps " . + "(md_module,md_skin,md_deps) " . + "VALUES ('module2','skin2','deps2')" + ), + array( + array( + 'table' => 'module_deps', + 'uniqueIndexes' => array( 'md_module', 'md_skin' ), + 'rows' => array( + array( + 'md_module' => 'module', + 'md_skin' => 'skin', + 'md_deps' => 'deps', + ), array( + 'md_module' => 'module2', + 'md_skin' => 'skin2', + 'md_deps' => 'deps2', + ), + ), + ), + "DELETE FROM module_deps " . + "WHERE ( md_module='module' ) OR ( md_skin='skin' ); " . + "INSERT INTO module_deps " . + "(md_module,md_skin,md_deps) " . + "VALUES ('module','skin','deps'); " . + "DELETE FROM module_deps " . + "WHERE ( md_module='module2' ) OR ( md_skin='skin2' ); " . + "INSERT INTO module_deps " . + "(md_module,md_skin,md_deps) " . + "VALUES ('module2','skin2','deps2')" + ), + array( + array( + 'table' => 'module_deps', + 'uniqueIndexes' => array(), + 'rows' => array( + 'md_module' => 'module', + 'md_skin' => 'skin', + 'md_deps' => 'deps', + ), + ), + "INSERT INTO module_deps " . + "(md_module,md_skin,md_deps) " . + "VALUES ('module','skin','deps')" + ), + ); + } + + /** + * @dataProvider provideNativeReplace + */ + function testNativeReplace( $sql, $sqlText ) { + $this->database->nativeReplace( + $sql['table'], + $sql['rows'], + __METHOD__ + ); + $this->assertLastSql( $sqlText ); + } + + public static function provideNativeReplace() { + return array( + array( + array( + 'table' => 'replace_table', + 'rows' => array( 'field' => 'text', 'field2' => 'text2' ), + ), + "REPLACE INTO replace_table " . + "(field,field2) " . + "VALUES ('text','text2')" + ), ); } @@ -110,7 +508,7 @@ class DatabaseSQLTest extends MediaWikiTestCase { * @dataProvider provideConditional */ function testConditional( $sql, $sqlText ) { - $this->assertEquals( trim( $this->db->conditional( + $this->assertEquals( trim( $this->database->conditional( $sql['conds'], $sql['true'], $sql['false'] @@ -145,4 +543,116 @@ class DatabaseSQLTest extends MediaWikiTestCase { ), ); } + + /** + * @dataProvider provideBuildConcat + */ + function testBuildConcat( $stringList, $sqlText ) { + $this->assertEquals( trim( $this->database->buildConcat( + $stringList + ) ), $sqlText ); + } + + public static function provideBuildConcat() { + return array( + array( + array( 'field', 'field2' ), + "CONCAT(field,field2)" + ), + array( + array( "'test'", 'field2' ), + "CONCAT('test',field2)" + ), + ); + } + + /** + * @dataProvider provideBuildLike + */ + function testBuildLike( $array, $sqlText ) { + $this->assertEquals( trim( $this->database->buildLike( + $array + ) ), $sqlText ); + } + + public static function provideBuildLike() { + return array( + array( + 'text', + "LIKE 'text'" + ), + array( + array( 'text', new LikeMatch( '%' ) ), + "LIKE 'text%'" + ), + array( + array( 'text', new LikeMatch( '%' ), 'text2' ), + "LIKE 'text%text2'" + ), + array( + array( 'text', new LikeMatch( '_' ) ), + "LIKE 'text_'" + ), + ); + } + + /** + * @dataProvider provideUnionQueries + */ + function testUnionQueries( $sql, $sqlText ) { + $this->assertEquals( trim( $this->database->unionQueries( + $sql['sqls'], + $sql['all'] + ) ), $sqlText ); + } + + public static function provideUnionQueries() { + return array( + array( + array( + 'sqls' => array( 'RAW SQL', 'RAW2SQL' ), + 'all' => true, + ), + "(RAW SQL) UNION ALL (RAW2SQL)" + ), + array( + array( + 'sqls' => array( 'RAW SQL', 'RAW2SQL' ), + 'all' => false, + ), + "(RAW SQL) UNION (RAW2SQL)" + ), + array( + array( + 'sqls' => array( 'RAW SQL', 'RAW2SQL', 'RAW3SQL' ), + 'all' => false, + ), + "(RAW SQL) UNION (RAW2SQL) UNION (RAW3SQL)" + ), + ); + } + + function testTransactionCommit() { + $this->database->begin( __METHOD__ ); + $this->database->commit( __METHOD__ ); + $this->assertLastSql( 'BEGIN; COMMIT' ); + } + + function testTransactionRollback() { + $this->database->begin( __METHOD__ ); + $this->database->rollback( __METHOD__ ); + $this->assertLastSql( 'BEGIN; ROLLBACK' ); + } + + function testDropTable() { + $this->database->setExistingTables( array( 'table' ) ); + $this->database->dropTable( 'table', __METHOD__ ); + $this->assertLastSql( 'DROP TABLE table' ); + } + + function testDropNonExistingTable() { + $this->assertFalse( + $this->database->dropTable( 'non_existing', __METHOD__ ) + ); + } } diff --git a/tests/phpunit/includes/db/DatabaseTestHelper.php b/tests/phpunit/includes/db/DatabaseTestHelper.php new file mode 100644 index 0000000000..1efa279b58 --- /dev/null +++ b/tests/phpunit/includes/db/DatabaseTestHelper.php @@ -0,0 +1,164 @@ +testName = $testName; + } + + /** + * Returns SQL queries grouped by '; ' + * Clear the list of queries that have been done so far. + */ + public function getLastSqls() { + $lastSqls = implode( '; ', $this->lastSqls ); + $this->lastSqls = array(); + return $lastSqls; + } + + public function setExistingTables( $tablesExists ) { + $this->tablesExists = (array)$tablesExists; + } + + protected function addSql( $sql ) { + // clean up spaces before and after some words and the whole string + $this->lastSqls[] = trim( preg_replace( + '/\s{2,}(?=FROM|WHERE|GROUP BY|ORDER BY|LIMIT)|(?<=SELECT|INSERT|UPDATE)\s{2,}/', + ' ', $sql + ) ); + } + + protected function checkFunctionName( $fname ) { + if ( substr( $fname, 0, strlen( $this->testName ) ) !== $this->testName ) { + throw new MWException( 'function name does not start with test class. ' . + $fname . ' vs. ' . $this->testName . '. ' . + 'Please provide __METHOD__ to database methods.' ); + } + } + + function strencode( $s ) { + // Choose apos to avoid handling of escaping double quotes in quoted text + return str_replace( "'", "\'", $s ); + } + + public function addIdentifierQuotes( $s ) { + // no escaping to avoid handling of double quotes in quoted text + return $s; + } + + public function query( $sql, $fname = '', $tempIgnore = false ) { + $this->checkFunctionName( $fname ); + $this->addSql( $sql ); + + return parent::query( $sql, $fname, $tempIgnore ); + } + + public function tableExists( $table, $fname = __METHOD__ ) { + $this->checkFunctionName( $fname ); + return in_array( $table, (array)$this->tablesExists ); + } + + // Redeclare parent method to make it public + public function nativeReplace( $table, $rows, $fname ) { + return parent::nativeReplace( $table, $rows, $fname ); + } + + function getType() { + return 'test'; + } + + function open( $server, $user, $password, $dbName ) { + return false; + } + + function fetchObject( $res ) { + return false; + } + + function fetchRow( $res ) { + return false; + } + + function numRows( $res ) { + return -1; + } + + function numFields( $res ) { + return -1; + } + + function fieldName( $res, $n ) { + return 'test'; + } + + function insertId() { + return -1; + } + + function dataSeek( $res, $row ) { + /* nop */ + } + + function lastErrno() { + return -1; + } + + function lastError() { + return 'test'; + } + + function fieldInfo( $table, $field ) { + return false; + } + + function indexInfo( $table, $index, $fname = 'Database::indexInfo' ) { + return false; + } + + function affectedRows() { + return -1; + } + + static function getSoftwareLink() { + return 'test'; + } + + function getServerVersion() { + return 'test'; + } + + function getServerInfo() { + return 'test'; + } + + protected function closeConnection() { + return false; + } + + protected function doQuery( $sql ) { + return array(); + } +} -- 2.20.1