MediaWiki
REL1_22
|
00001 <?php 00002 00003 class MockDatabaseSqlite extends DatabaseSqliteStandalone { 00004 var $lastQuery; 00005 00006 function __construct() { 00007 parent::__construct( ':memory:' ); 00008 } 00009 00010 function query( $sql, $fname = '', $tempIgnore = false ) { 00011 $this->lastQuery = $sql; 00012 00013 return true; 00014 } 00015 00019 public function replaceVars( $s ) { 00020 return parent::replaceVars( $s ); 00021 } 00022 } 00023 00029 class DatabaseSqliteTest extends MediaWikiTestCase { 00030 00034 var $db; 00035 00036 protected function setUp() { 00037 parent::setUp(); 00038 00039 if ( !Sqlite::isPresent() ) { 00040 $this->markTestSkipped( 'No SQLite support detected' ); 00041 } 00042 $this->db = new MockDatabaseSqlite(); 00043 if ( version_compare( $this->db->getServerVersion(), '3.6.0', '<' ) ) { 00044 $this->markTestSkipped( "SQLite at least 3.6 required, {$this->db->getServerVersion()} found" ); 00045 } 00046 } 00047 00048 private function replaceVars( $sql ) { 00049 // normalize spacing to hide implementation details 00050 return preg_replace( '/\s+/', ' ', $this->db->replaceVars( $sql ) ); 00051 } 00052 00053 private function assertResultIs( $expected, $res ) { 00054 $this->assertNotNull( $res ); 00055 $i = 0; 00056 foreach ( $res as $row ) { 00057 foreach ( $expected[$i] as $key => $value ) { 00058 $this->assertTrue( isset( $row->$key ) ); 00059 $this->assertEquals( $value, $row->$key ); 00060 } 00061 $i++; 00062 } 00063 $this->assertEquals( count( $expected ), $i, 'Unexpected number of rows' ); 00064 } 00065 00066 public static function provideAddQuotes() { 00067 return array( 00068 array( // #0: empty 00069 '', "''" 00070 ), 00071 array( // #1: simple 00072 'foo bar', "'foo bar'" 00073 ), 00074 array( // #2: including quote 00075 'foo\'bar', "'foo''bar'" 00076 ), 00077 array( // #3: including \0 (must be represented as hex, per https://bugs.php.net/bug.php?id=63419) 00078 "x\0y", 00079 "x'780079'", 00080 ), 00081 array( // #4: blob object (must be represented as hex) 00082 new Blob( "hello" ), 00083 "x'68656c6c6f'", 00084 ), 00085 ); 00086 } 00087 00092 public function testAddQuotes( $value, $expected ) { 00093 // check quoting 00094 $db = new DatabaseSqliteStandalone( ':memory:' ); 00095 $this->assertEquals( $expected, $db->addQuotes( $value ), 'string not quoted as expected' ); 00096 00097 // ok, quoting works as expected, now try a round trip. 00098 $re = $db->query( 'select ' . $db->addQuotes( $value ) ); 00099 00100 $this->assertTrue( $re !== false, 'query failed' ); 00101 00102 if ( $row = $re->fetchRow() ) { 00103 if ( $value instanceof Blob ) { 00104 $value = $value->fetch(); 00105 } 00106 00107 $this->assertEquals( $value, $row[0], 'string mangled by the database' ); 00108 } else { 00109 $this->fail( 'query returned no result' ); 00110 } 00111 } 00112 00116 public function testReplaceVars() { 00117 $this->assertEquals( 'foo', $this->replaceVars( 'foo' ), "Don't break anything accidentally" ); 00118 00119 $this->assertEquals( "CREATE TABLE /**/foo (foo_key INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " 00120 . "foo_bar TEXT, foo_name TEXT NOT NULL DEFAULT '', foo_int INTEGER, foo_int2 INTEGER );", 00121 $this->replaceVars( "CREATE TABLE /**/foo (foo_key int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, 00122 foo_bar char(13), foo_name varchar(255) binary NOT NULL DEFAULT '', foo_int tinyint ( 8 ), foo_int2 int(16) ) ENGINE=MyISAM;" ) 00123 ); 00124 00125 $this->assertEquals( "CREATE TABLE foo ( foo1 REAL, foo2 REAL, foo3 REAL );", 00126 $this->replaceVars( "CREATE TABLE foo ( foo1 FLOAT, foo2 DOUBLE( 1,10), foo3 DOUBLE PRECISION );" ) 00127 ); 00128 00129 $this->assertEquals( "CREATE TABLE foo ( foo_binary1 BLOB, foo_binary2 BLOB );", 00130 $this->replaceVars( "CREATE TABLE foo ( foo_binary1 binary(16), foo_binary2 varbinary(32) );" ) 00131 ); 00132 00133 $this->assertEquals( "CREATE TABLE text ( text_foo TEXT );", 00134 $this->replaceVars( "CREATE TABLE text ( text_foo tinytext );" ), 00135 'Table name changed' 00136 ); 00137 00138 $this->assertEquals( "CREATE TABLE foo ( foobar INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL );", 00139 $this->replaceVars( "CREATE TABLE foo ( foobar INT PRIMARY KEY NOT NULL AUTO_INCREMENT );" ) 00140 ); 00141 $this->assertEquals( "CREATE TABLE foo ( foobar INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL );", 00142 $this->replaceVars( "CREATE TABLE foo ( foobar INT PRIMARY KEY AUTO_INCREMENT NOT NULL );" ) 00143 ); 00144 00145 $this->assertEquals( "CREATE TABLE enums( enum1 TEXT, myenum TEXT)", 00146 $this->replaceVars( "CREATE TABLE enums( enum1 ENUM('A', 'B'), myenum ENUM ('X', 'Y'))" ) 00147 ); 00148 00149 $this->assertEquals( "ALTER TABLE foo ADD COLUMN foo_bar INTEGER DEFAULT 42", 00150 $this->replaceVars( "ALTER TABLE foo\nADD COLUMN foo_bar int(10) unsigned DEFAULT 42" ) 00151 ); 00152 } 00153 00157 public function testTableName() { 00158 // @todo Moar! 00159 $db = new DatabaseSqliteStandalone( ':memory:' ); 00160 $this->assertEquals( 'foo', $db->tableName( 'foo' ) ); 00161 $this->assertEquals( 'sqlite_master', $db->tableName( 'sqlite_master' ) ); 00162 $db->tablePrefix( 'foo' ); 00163 $this->assertEquals( 'sqlite_master', $db->tableName( 'sqlite_master' ) ); 00164 $this->assertEquals( 'foobar', $db->tableName( 'bar' ) ); 00165 } 00166 00170 public function testDuplicateTableStructure() { 00171 $db = new DatabaseSqliteStandalone( ':memory:' ); 00172 $db->query( 'CREATE TABLE foo(foo, barfoo)' ); 00173 00174 $db->duplicateTableStructure( 'foo', 'bar' ); 00175 $this->assertEquals( 'CREATE TABLE "bar"(foo, barfoo)', 00176 $db->selectField( 'sqlite_master', 'sql', array( 'name' => 'bar' ) ), 00177 'Normal table duplication' 00178 ); 00179 00180 $db->duplicateTableStructure( 'foo', 'baz', true ); 00181 $this->assertEquals( 'CREATE TABLE "baz"(foo, barfoo)', 00182 $db->selectField( 'sqlite_temp_master', 'sql', array( 'name' => 'baz' ) ), 00183 'Creation of temporary duplicate' 00184 ); 00185 $this->assertEquals( 0, 00186 $db->selectField( 'sqlite_master', 'COUNT(*)', array( 'name' => 'baz' ) ), 00187 'Create a temporary duplicate only' 00188 ); 00189 } 00190 00194 public function testDuplicateTableStructureVirtual() { 00195 $db = new DatabaseSqliteStandalone( ':memory:' ); 00196 if ( $db->getFulltextSearchModule() != 'FTS3' ) { 00197 $this->markTestSkipped( 'FTS3 not supported, cannot create virtual tables' ); 00198 } 00199 $db->query( 'CREATE VIRTUAL TABLE "foo" USING FTS3(foobar)' ); 00200 00201 $db->duplicateTableStructure( 'foo', 'bar' ); 00202 $this->assertEquals( 'CREATE VIRTUAL TABLE "bar" USING FTS3(foobar)', 00203 $db->selectField( 'sqlite_master', 'sql', array( 'name' => 'bar' ) ), 00204 'Duplication of virtual tables' 00205 ); 00206 00207 $db->duplicateTableStructure( 'foo', 'baz', true ); 00208 $this->assertEquals( 'CREATE VIRTUAL TABLE "baz" USING FTS3(foobar)', 00209 $db->selectField( 'sqlite_master', 'sql', array( 'name' => 'baz' ) ), 00210 "Can't create temporary virtual tables, should fall back to non-temporary duplication" 00211 ); 00212 } 00213 00217 public function testDeleteJoin() { 00218 $db = new DatabaseSqliteStandalone( ':memory:' ); 00219 $db->query( 'CREATE TABLE a (a_1)', __METHOD__ ); 00220 $db->query( 'CREATE TABLE b (b_1, b_2)', __METHOD__ ); 00221 $db->insert( 'a', array( 00222 array( 'a_1' => 1 ), 00223 array( 'a_1' => 2 ), 00224 array( 'a_1' => 3 ), 00225 ), 00226 __METHOD__ 00227 ); 00228 $db->insert( 'b', array( 00229 array( 'b_1' => 2, 'b_2' => 'a' ), 00230 array( 'b_1' => 3, 'b_2' => 'b' ), 00231 ), 00232 __METHOD__ 00233 ); 00234 $db->deleteJoin( 'a', 'b', 'a_1', 'b_1', array( 'b_2' => 'a' ), __METHOD__ ); 00235 $res = $db->query( "SELECT * FROM a", __METHOD__ ); 00236 $this->assertResultIs( array( 00237 array( 'a_1' => 1 ), 00238 array( 'a_1' => 3 ), 00239 ), 00240 $res 00241 ); 00242 } 00243 00244 public function testEntireSchema() { 00245 global $IP; 00246 00247 $result = Sqlite::checkSqlSyntax( "$IP/maintenance/tables.sql" ); 00248 if ( $result !== true ) { 00249 $this->fail( $result ); 00250 } 00251 $this->assertTrue( true ); // avoid test being marked as incomplete due to lack of assertions 00252 } 00253 00258 public function testUpgrades() { 00259 global $IP, $wgVersion, $wgProfileToDatabase; 00260 00261 // Versions tested 00262 $versions = array( 00263 //'1.13', disabled for now, was totally screwed up 00264 // SQLite wasn't included in 1.14 00265 '1.15', 00266 '1.16', 00267 '1.17', 00268 '1.18', 00269 ); 00270 00271 // Mismatches for these columns we can safely ignore 00272 $ignoredColumns = array( 00273 'user_newtalk.user_last_timestamp', // r84185 00274 ); 00275 00276 $currentDB = new DatabaseSqliteStandalone( ':memory:' ); 00277 $currentDB->sourceFile( "$IP/maintenance/tables.sql" ); 00278 if ( $wgProfileToDatabase ) { 00279 $currentDB->sourceFile( "$IP/maintenance/sqlite/archives/patch-profiling.sql" ); 00280 } 00281 $currentTables = $this->getTables( $currentDB ); 00282 sort( $currentTables ); 00283 00284 foreach ( $versions as $version ) { 00285 $versions = "upgrading from $version to $wgVersion"; 00286 $db = $this->prepareDB( $version ); 00287 $tables = $this->getTables( $db ); 00288 $this->assertEquals( $currentTables, $tables, "Different tables $versions" ); 00289 foreach ( $tables as $table ) { 00290 $currentCols = $this->getColumns( $currentDB, $table ); 00291 $cols = $this->getColumns( $db, $table ); 00292 $this->assertEquals( 00293 array_keys( $currentCols ), 00294 array_keys( $cols ), 00295 "Mismatching columns for table \"$table\" $versions" 00296 ); 00297 foreach ( $currentCols as $name => $column ) { 00298 $fullName = "$table.$name"; 00299 $this->assertEquals( 00300 (bool)$column->pk, 00301 (bool)$cols[$name]->pk, 00302 "PRIMARY KEY status does not match for column $fullName $versions" 00303 ); 00304 if ( !in_array( $fullName, $ignoredColumns ) ) { 00305 $this->assertEquals( 00306 (bool)$column->notnull, 00307 (bool)$cols[$name]->notnull, 00308 "NOT NULL status does not match for column $fullName $versions" 00309 ); 00310 $this->assertEquals( 00311 $column->dflt_value, 00312 $cols[$name]->dflt_value, 00313 "Default values does not match for column $fullName $versions" 00314 ); 00315 } 00316 } 00317 $currentIndexes = $this->getIndexes( $currentDB, $table ); 00318 $indexes = $this->getIndexes( $db, $table ); 00319 $this->assertEquals( 00320 array_keys( $currentIndexes ), 00321 array_keys( $indexes ), 00322 "mismatching indexes for table \"$table\" $versions" 00323 ); 00324 } 00325 $db->close(); 00326 } 00327 } 00328 00332 public function testInsertIdType() { 00333 $db = new DatabaseSqliteStandalone( ':memory:' ); 00334 00335 $databaseCreation = $db->query( 'CREATE TABLE a ( a_1 )', __METHOD__ ); 00336 $this->assertInstanceOf( 'ResultWrapper', $databaseCreation, "Database creation" ); 00337 00338 $insertion = $db->insert( 'a', array( 'a_1' => 10 ), __METHOD__ ); 00339 $this->assertTrue( $insertion, "Insertion worked" ); 00340 00341 $this->assertInternalType( 'integer', $db->insertId(), "Actual typecheck" ); 00342 $this->assertTrue( $db->close(), "closing database" ); 00343 } 00344 00345 private function prepareDB( $version ) { 00346 static $maint = null; 00347 if ( $maint === null ) { 00348 $maint = new FakeMaintenance(); 00349 $maint->loadParamsAndArgs( null, array( 'quiet' => 1 ) ); 00350 } 00351 00352 global $IP; 00353 $db = new DatabaseSqliteStandalone( ':memory:' ); 00354 $db->sourceFile( "$IP/tests/phpunit/data/db/sqlite/tables-$version.sql" ); 00355 $updater = DatabaseUpdater::newForDB( $db, false, $maint ); 00356 $updater->doUpdates( array( 'core' ) ); 00357 00358 return $db; 00359 } 00360 00361 private function getTables( $db ) { 00362 $list = array_flip( $db->listTables() ); 00363 $excluded = array( 00364 'external_user', // removed from core in 1.22 00365 'math', // moved out of core in 1.18 00366 'trackbacks', // removed from core in 1.19 00367 'searchindex', 00368 'searchindex_content', 00369 'searchindex_segments', 00370 'searchindex_segdir', 00371 // FTS4 ready!!1 00372 'searchindex_docsize', 00373 'searchindex_stat', 00374 ); 00375 foreach ( $excluded as $t ) { 00376 unset( $list[$t] ); 00377 } 00378 $list = array_flip( $list ); 00379 sort( $list ); 00380 00381 return $list; 00382 } 00383 00384 private function getColumns( $db, $table ) { 00385 $cols = array(); 00386 $res = $db->query( "PRAGMA table_info($table)" ); 00387 $this->assertNotNull( $res ); 00388 foreach ( $res as $col ) { 00389 $cols[$col->name] = $col; 00390 } 00391 ksort( $cols ); 00392 00393 return $cols; 00394 } 00395 00396 private function getIndexes( $db, $table ) { 00397 $indexes = array(); 00398 $res = $db->query( "PRAGMA index_list($table)" ); 00399 $this->assertNotNull( $res ); 00400 foreach ( $res as $index ) { 00401 $res2 = $db->query( "PRAGMA index_info({$index->name})" ); 00402 $this->assertNotNull( $res2 ); 00403 $index->columns = array(); 00404 foreach ( $res2 as $col ) { 00405 $index->columns[] = $col; 00406 } 00407 $indexes[$index->name] = $index; 00408 } 00409 ksort( $indexes ); 00410 00411 return $indexes; 00412 } 00413 00414 public function testCaseInsensitiveLike() { 00415 // TODO: Test this for all databases 00416 $db = new DatabaseSqliteStandalone( ':memory:' ); 00417 $res = $db->query( 'SELECT "a" LIKE "A" AS a' ); 00418 $row = $res->fetchRow(); 00419 $this->assertFalse( (bool)$row['a'] ); 00420 } 00421 }