1 // Copyright 2013 The Chromium Authors. All rights reserved. 2 // Use of this source code is governed by a BSD-style license that can be 3 // found in the LICENSE file. 4 5 #include <string> 6 7 #include "base/bind.h" 8 #include "base/files/file_path.h" 9 #include "base/files/file_util.h" 10 #include "base/files/scoped_temp_dir.h" 11 #include "base/path_service.h" 12 #include "base/strings/string_number_conversions.h" 13 #include "sql/connection.h" 14 #include "sql/meta_table.h" 15 #include "sql/recovery.h" 16 #include "sql/statement.h" 17 #include "sql/test/paths.h" 18 #include "sql/test/scoped_error_ignorer.h" 19 #include "sql/test/test_helpers.h" 20 #include "testing/gtest/include/gtest/gtest.h" 21 #include "third_party/sqlite/sqlite3.h" 22 23 namespace { 24 25 // Execute |sql|, and stringify the results with |column_sep| between 26 // columns and |row_sep| between rows. 27 // TODO(shess): Promote this to a central testing helper. 28 std::string ExecuteWithResults(sql::Connection* db, 29 const char* sql, 30 const char* column_sep, 31 const char* row_sep) { 32 sql::Statement s(db->GetUniqueStatement(sql)); 33 std::string ret; 34 while (s.Step()) { 35 if (!ret.empty()) 36 ret += row_sep; 37 for (int i = 0; i < s.ColumnCount(); ++i) { 38 if (i > 0) 39 ret += column_sep; 40 if (s.ColumnType(i) == sql::COLUMN_TYPE_NULL) { 41 ret += "<null>"; 42 } else if (s.ColumnType(i) == sql::COLUMN_TYPE_BLOB) { 43 ret += "<x'"; 44 ret += base::HexEncode(s.ColumnBlob(i), s.ColumnByteLength(i)); 45 ret += "'>"; 46 } else { 47 ret += s.ColumnString(i); 48 } 49 } 50 } 51 return ret; 52 } 53 54 // Dump consistent human-readable representation of the database 55 // schema. For tables or indices, this will contain the sql command 56 // to create the table or index. For certain automatic SQLite 57 // structures with no sql, the name is used. 58 std::string GetSchema(sql::Connection* db) { 59 const char kSql[] = 60 "SELECT COALESCE(sql, name) FROM sqlite_master ORDER BY 1"; 61 return ExecuteWithResults(db, kSql, "|", "\n"); 62 } 63 64 class SQLRecoveryTest : public testing::Test { 65 public: 66 SQLRecoveryTest() {} 67 68 virtual void SetUp() { 69 ASSERT_TRUE(temp_dir_.CreateUniqueTempDir()); 70 ASSERT_TRUE(db_.Open(db_path())); 71 } 72 73 virtual void TearDown() { 74 db_.Close(); 75 } 76 77 sql::Connection& db() { return db_; } 78 79 base::FilePath db_path() { 80 return temp_dir_.path().AppendASCII("SQLRecoveryTest.db"); 81 } 82 83 bool Reopen() { 84 db_.Close(); 85 return db_.Open(db_path()); 86 } 87 88 private: 89 base::ScopedTempDir temp_dir_; 90 sql::Connection db_; 91 }; 92 93 TEST_F(SQLRecoveryTest, RecoverBasic) { 94 const char kCreateSql[] = "CREATE TABLE x (t TEXT)"; 95 const char kInsertSql[] = "INSERT INTO x VALUES ('This is a test')"; 96 ASSERT_TRUE(db().Execute(kCreateSql)); 97 ASSERT_TRUE(db().Execute(kInsertSql)); 98 ASSERT_EQ("CREATE TABLE x (t TEXT)", GetSchema(&db())); 99 100 // If the Recovery handle goes out of scope without being 101 // Recovered(), the database is razed. 102 { 103 scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path()); 104 ASSERT_TRUE(recovery.get()); 105 } 106 EXPECT_FALSE(db().is_open()); 107 ASSERT_TRUE(Reopen()); 108 EXPECT_TRUE(db().is_open()); 109 ASSERT_EQ("", GetSchema(&db())); 110 111 // Recreate the database. 112 ASSERT_TRUE(db().Execute(kCreateSql)); 113 ASSERT_TRUE(db().Execute(kInsertSql)); 114 ASSERT_EQ("CREATE TABLE x (t TEXT)", GetSchema(&db())); 115 116 // Unrecoverable() also razes. 117 { 118 scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path()); 119 ASSERT_TRUE(recovery.get()); 120 sql::Recovery::Unrecoverable(recovery.Pass()); 121 122 // TODO(shess): Test that calls to recover.db() start failing. 123 } 124 EXPECT_FALSE(db().is_open()); 125 ASSERT_TRUE(Reopen()); 126 EXPECT_TRUE(db().is_open()); 127 ASSERT_EQ("", GetSchema(&db())); 128 129 // Recreate the database. 130 ASSERT_TRUE(db().Execute(kCreateSql)); 131 ASSERT_TRUE(db().Execute(kInsertSql)); 132 ASSERT_EQ("CREATE TABLE x (t TEXT)", GetSchema(&db())); 133 134 // Recovered() replaces the original with the "recovered" version. 135 { 136 scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path()); 137 ASSERT_TRUE(recovery.get()); 138 139 // Create the new version of the table. 140 ASSERT_TRUE(recovery->db()->Execute(kCreateSql)); 141 142 // Insert different data to distinguish from original database. 143 const char kAltInsertSql[] = "INSERT INTO x VALUES ('That was a test')"; 144 ASSERT_TRUE(recovery->db()->Execute(kAltInsertSql)); 145 146 // Successfully recovered. 147 ASSERT_TRUE(sql::Recovery::Recovered(recovery.Pass())); 148 } 149 EXPECT_FALSE(db().is_open()); 150 ASSERT_TRUE(Reopen()); 151 EXPECT_TRUE(db().is_open()); 152 ASSERT_EQ("CREATE TABLE x (t TEXT)", GetSchema(&db())); 153 154 const char* kXSql = "SELECT * FROM x ORDER BY 1"; 155 ASSERT_EQ("That was a test", 156 ExecuteWithResults(&db(), kXSql, "|", "\n")); 157 } 158 159 // The recovery virtual table is only supported for Chromium's SQLite. 160 #if !defined(USE_SYSTEM_SQLITE) 161 162 // Run recovery through its paces on a valid database. 163 TEST_F(SQLRecoveryTest, VirtualTable) { 164 const char kCreateSql[] = "CREATE TABLE x (t TEXT)"; 165 ASSERT_TRUE(db().Execute(kCreateSql)); 166 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES ('This is a test')")); 167 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES ('That was a test')")); 168 169 // Successfully recover the database. 170 { 171 scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path()); 172 173 // Tables to recover original DB, now at [corrupt]. 174 const char kRecoveryCreateSql[] = 175 "CREATE VIRTUAL TABLE temp.recover_x using recover(" 176 " corrupt.x," 177 " t TEXT STRICT" 178 ")"; 179 ASSERT_TRUE(recovery->db()->Execute(kRecoveryCreateSql)); 180 181 // Re-create the original schema. 182 ASSERT_TRUE(recovery->db()->Execute(kCreateSql)); 183 184 // Copy the data from the recovery tables to the new database. 185 const char kRecoveryCopySql[] = 186 "INSERT INTO x SELECT t FROM recover_x"; 187 ASSERT_TRUE(recovery->db()->Execute(kRecoveryCopySql)); 188 189 // Successfully recovered. 190 ASSERT_TRUE(sql::Recovery::Recovered(recovery.Pass())); 191 } 192 193 // Since the database was not corrupt, the entire schema and all 194 // data should be recovered. 195 ASSERT_TRUE(Reopen()); 196 ASSERT_EQ("CREATE TABLE x (t TEXT)", GetSchema(&db())); 197 198 const char* kXSql = "SELECT * FROM x ORDER BY 1"; 199 ASSERT_EQ("That was a test\nThis is a test", 200 ExecuteWithResults(&db(), kXSql, "|", "\n")); 201 } 202 203 void RecoveryCallback(sql::Connection* db, const base::FilePath& db_path, 204 int* record_error, int error, sql::Statement* stmt) { 205 *record_error = error; 206 207 // Clear the error callback to prevent reentrancy. 208 db->reset_error_callback(); 209 210 scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(db, db_path); 211 ASSERT_TRUE(recovery.get()); 212 213 const char kRecoveryCreateSql[] = 214 "CREATE VIRTUAL TABLE temp.recover_x using recover(" 215 " corrupt.x," 216 " id INTEGER STRICT," 217 " v INTEGER STRICT" 218 ")"; 219 const char kCreateTable[] = "CREATE TABLE x (id INTEGER, v INTEGER)"; 220 const char kCreateIndex[] = "CREATE UNIQUE INDEX x_id ON x (id)"; 221 222 // Replicate data over. 223 const char kRecoveryCopySql[] = 224 "INSERT OR REPLACE INTO x SELECT id, v FROM recover_x"; 225 226 ASSERT_TRUE(recovery->db()->Execute(kRecoveryCreateSql)); 227 ASSERT_TRUE(recovery->db()->Execute(kCreateTable)); 228 ASSERT_TRUE(recovery->db()->Execute(kCreateIndex)); 229 ASSERT_TRUE(recovery->db()->Execute(kRecoveryCopySql)); 230 231 ASSERT_TRUE(sql::Recovery::Recovered(recovery.Pass())); 232 } 233 234 // Build a database, corrupt it by making an index reference to 235 // deleted row, then recover when a query selects that row. 236 TEST_F(SQLRecoveryTest, RecoverCorruptIndex) { 237 const char kCreateTable[] = "CREATE TABLE x (id INTEGER, v INTEGER)"; 238 const char kCreateIndex[] = "CREATE UNIQUE INDEX x_id ON x (id)"; 239 ASSERT_TRUE(db().Execute(kCreateTable)); 240 ASSERT_TRUE(db().Execute(kCreateIndex)); 241 242 // Insert a bit of data. 243 { 244 ASSERT_TRUE(db().BeginTransaction()); 245 246 const char kInsertSql[] = "INSERT INTO x (id, v) VALUES (?, ?)"; 247 sql::Statement s(db().GetUniqueStatement(kInsertSql)); 248 for (int i = 0; i < 10; ++i) { 249 s.Reset(true); 250 s.BindInt(0, i); 251 s.BindInt(1, i); 252 EXPECT_FALSE(s.Step()); 253 EXPECT_TRUE(s.Succeeded()); 254 } 255 256 ASSERT_TRUE(db().CommitTransaction()); 257 } 258 db().Close(); 259 260 // Delete a row from the table, while leaving the index entry which 261 // references it. 262 const char kDeleteSql[] = "DELETE FROM x WHERE id = 0"; 263 ASSERT_TRUE(sql::test::CorruptTableOrIndex(db_path(), "x_id", kDeleteSql)); 264 265 ASSERT_TRUE(Reopen()); 266 267 int error = SQLITE_OK; 268 db().set_error_callback(base::Bind(&RecoveryCallback, 269 &db(), db_path(), &error)); 270 271 // This works before the callback is called. 272 const char kTrivialSql[] = "SELECT COUNT(*) FROM sqlite_master"; 273 EXPECT_TRUE(db().IsSQLValid(kTrivialSql)); 274 275 // TODO(shess): Could this be delete? Anything which fails should work. 276 const char kSelectSql[] = "SELECT v FROM x WHERE id = 0"; 277 ASSERT_FALSE(db().Execute(kSelectSql)); 278 EXPECT_EQ(SQLITE_CORRUPT, error); 279 280 // Database handle has been poisoned. 281 EXPECT_FALSE(db().IsSQLValid(kTrivialSql)); 282 283 ASSERT_TRUE(Reopen()); 284 285 // The recovered table should reflect the deletion. 286 const char kSelectAllSql[] = "SELECT v FROM x ORDER BY id"; 287 EXPECT_EQ("1,2,3,4,5,6,7,8,9", 288 ExecuteWithResults(&db(), kSelectAllSql, "|", ",")); 289 290 // The failing statement should now succeed, with no results. 291 EXPECT_EQ("", ExecuteWithResults(&db(), kSelectSql, "|", ",")); 292 } 293 294 // Build a database, corrupt it by making a table contain a row not 295 // referenced by the index, then recover the database. 296 TEST_F(SQLRecoveryTest, RecoverCorruptTable) { 297 const char kCreateTable[] = "CREATE TABLE x (id INTEGER, v INTEGER)"; 298 const char kCreateIndex[] = "CREATE UNIQUE INDEX x_id ON x (id)"; 299 ASSERT_TRUE(db().Execute(kCreateTable)); 300 ASSERT_TRUE(db().Execute(kCreateIndex)); 301 302 // Insert a bit of data. 303 { 304 ASSERT_TRUE(db().BeginTransaction()); 305 306 const char kInsertSql[] = "INSERT INTO x (id, v) VALUES (?, ?)"; 307 sql::Statement s(db().GetUniqueStatement(kInsertSql)); 308 for (int i = 0; i < 10; ++i) { 309 s.Reset(true); 310 s.BindInt(0, i); 311 s.BindInt(1, i); 312 EXPECT_FALSE(s.Step()); 313 EXPECT_TRUE(s.Succeeded()); 314 } 315 316 ASSERT_TRUE(db().CommitTransaction()); 317 } 318 db().Close(); 319 320 // Delete a row from the index while leaving a table entry. 321 const char kDeleteSql[] = "DELETE FROM x WHERE id = 0"; 322 ASSERT_TRUE(sql::test::CorruptTableOrIndex(db_path(), "x", kDeleteSql)); 323 324 // TODO(shess): Figure out a query which causes SQLite to notice 325 // this organically. Meanwhile, just handle it manually. 326 327 ASSERT_TRUE(Reopen()); 328 329 // Index shows one less than originally inserted. 330 const char kCountSql[] = "SELECT COUNT (*) FROM x"; 331 EXPECT_EQ("9", ExecuteWithResults(&db(), kCountSql, "|", ",")); 332 333 // A full table scan shows all of the original data. 334 const char kDistinctSql[] = "SELECT DISTINCT COUNT (id) FROM x"; 335 EXPECT_EQ("10", ExecuteWithResults(&db(), kDistinctSql, "|", ",")); 336 337 // Insert id 0 again. Since it is not in the index, the insert 338 // succeeds, but results in a duplicate value in the table. 339 const char kInsertSql[] = "INSERT INTO x (id, v) VALUES (0, 100)"; 340 ASSERT_TRUE(db().Execute(kInsertSql)); 341 342 // Duplication is visible. 343 EXPECT_EQ("10", ExecuteWithResults(&db(), kCountSql, "|", ",")); 344 EXPECT_EQ("11", ExecuteWithResults(&db(), kDistinctSql, "|", ",")); 345 346 // This works before the callback is called. 347 const char kTrivialSql[] = "SELECT COUNT(*) FROM sqlite_master"; 348 EXPECT_TRUE(db().IsSQLValid(kTrivialSql)); 349 350 // Call the recovery callback manually. 351 int error = SQLITE_OK; 352 RecoveryCallback(&db(), db_path(), &error, SQLITE_CORRUPT, NULL); 353 EXPECT_EQ(SQLITE_CORRUPT, error); 354 355 // Database handle has been poisoned. 356 EXPECT_FALSE(db().IsSQLValid(kTrivialSql)); 357 358 ASSERT_TRUE(Reopen()); 359 360 // The recovered table has consistency between the index and the table. 361 EXPECT_EQ("10", ExecuteWithResults(&db(), kCountSql, "|", ",")); 362 EXPECT_EQ("10", ExecuteWithResults(&db(), kDistinctSql, "|", ",")); 363 364 // The expected value was retained. 365 const char kSelectSql[] = "SELECT v FROM x WHERE id = 0"; 366 EXPECT_EQ("100", ExecuteWithResults(&db(), kSelectSql, "|", ",")); 367 } 368 369 TEST_F(SQLRecoveryTest, Meta) { 370 const int kVersion = 3; 371 const int kCompatibleVersion = 2; 372 373 { 374 sql::MetaTable meta; 375 EXPECT_TRUE(meta.Init(&db(), kVersion, kCompatibleVersion)); 376 EXPECT_EQ(kVersion, meta.GetVersionNumber()); 377 } 378 379 // Test expected case where everything works. 380 { 381 scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path()); 382 EXPECT_TRUE(recovery->SetupMeta()); 383 int version = 0; 384 EXPECT_TRUE(recovery->GetMetaVersionNumber(&version)); 385 EXPECT_EQ(kVersion, version); 386 387 sql::Recovery::Rollback(recovery.Pass()); 388 } 389 ASSERT_TRUE(Reopen()); // Handle was poisoned. 390 391 // Test version row missing. 392 EXPECT_TRUE(db().Execute("DELETE FROM meta WHERE key = 'version'")); 393 { 394 scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path()); 395 EXPECT_TRUE(recovery->SetupMeta()); 396 int version = 0; 397 EXPECT_FALSE(recovery->GetMetaVersionNumber(&version)); 398 EXPECT_EQ(0, version); 399 400 sql::Recovery::Rollback(recovery.Pass()); 401 } 402 ASSERT_TRUE(Reopen()); // Handle was poisoned. 403 404 // Test meta table missing. 405 EXPECT_TRUE(db().Execute("DROP TABLE meta")); 406 { 407 sql::ScopedErrorIgnorer ignore_errors; 408 ignore_errors.IgnoreError(SQLITE_CORRUPT); // From virtual table. 409 scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path()); 410 EXPECT_FALSE(recovery->SetupMeta()); 411 ASSERT_TRUE(ignore_errors.CheckIgnoredErrors()); 412 } 413 } 414 415 // Baseline AutoRecoverTable() test. 416 TEST_F(SQLRecoveryTest, AutoRecoverTable) { 417 // BIGINT and VARCHAR to test type affinity. 418 const char kCreateSql[] = "CREATE TABLE x (id BIGINT, t TEXT, v VARCHAR)"; 419 ASSERT_TRUE(db().Execute(kCreateSql)); 420 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (11, 'This is', 'a test')")); 421 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (5, 'That was', 'a test')")); 422 423 // Save aside a copy of the original schema and data. 424 const std::string orig_schema(GetSchema(&db())); 425 const char kXSql[] = "SELECT * FROM x ORDER BY 1"; 426 const std::string orig_data(ExecuteWithResults(&db(), kXSql, "|", "\n")); 427 428 // Create a lame-duck table which will not be propagated by recovery to 429 // detect that the recovery code actually ran. 430 ASSERT_TRUE(db().Execute("CREATE TABLE y (c TEXT)")); 431 ASSERT_NE(orig_schema, GetSchema(&db())); 432 433 { 434 scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path()); 435 ASSERT_TRUE(recovery->db()->Execute(kCreateSql)); 436 437 // Save a copy of the temp db's schema before recovering the table. 438 const char kTempSchemaSql[] = "SELECT name, sql FROM sqlite_temp_master"; 439 const std::string temp_schema( 440 ExecuteWithResults(recovery->db(), kTempSchemaSql, "|", "\n")); 441 442 size_t rows = 0; 443 EXPECT_TRUE(recovery->AutoRecoverTable("x", 0, &rows)); 444 EXPECT_EQ(2u, rows); 445 446 // Test that any additional temp tables were cleaned up. 447 EXPECT_EQ(temp_schema, 448 ExecuteWithResults(recovery->db(), kTempSchemaSql, "|", "\n")); 449 450 ASSERT_TRUE(sql::Recovery::Recovered(recovery.Pass())); 451 } 452 453 // Since the database was not corrupt, the entire schema and all 454 // data should be recovered. 455 ASSERT_TRUE(Reopen()); 456 ASSERT_EQ(orig_schema, GetSchema(&db())); 457 ASSERT_EQ(orig_data, ExecuteWithResults(&db(), kXSql, "|", "\n")); 458 459 // Recovery fails if the target table doesn't exist. 460 { 461 scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path()); 462 ASSERT_TRUE(recovery->db()->Execute(kCreateSql)); 463 464 // TODO(shess): Should this failure implicitly lead to Raze()? 465 size_t rows = 0; 466 EXPECT_FALSE(recovery->AutoRecoverTable("y", 0, &rows)); 467 468 sql::Recovery::Unrecoverable(recovery.Pass()); 469 } 470 } 471 472 // Test that default values correctly replace nulls. The recovery 473 // virtual table reads directly from the database, so DEFAULT is not 474 // interpretted at that level. 475 TEST_F(SQLRecoveryTest, AutoRecoverTableWithDefault) { 476 ASSERT_TRUE(db().Execute("CREATE TABLE x (id INTEGER)")); 477 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (5)")); 478 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (15)")); 479 480 // ALTER effectively leaves the new columns NULL in the first two 481 // rows. The row with 17 will get the default injected at insert 482 // time, while the row with 42 will get the actual value provided. 483 // Embedded "'" to make sure default-handling continues to be quoted 484 // correctly. 485 ASSERT_TRUE(db().Execute("ALTER TABLE x ADD COLUMN t TEXT DEFAULT 'a''a'")); 486 ASSERT_TRUE(db().Execute("ALTER TABLE x ADD COLUMN b BLOB DEFAULT x'AA55'")); 487 ASSERT_TRUE(db().Execute("ALTER TABLE x ADD COLUMN i INT DEFAULT 93")); 488 ASSERT_TRUE(db().Execute("INSERT INTO x (id) VALUES (17)")); 489 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (42, 'b', x'1234', 12)")); 490 491 // Save aside a copy of the original schema and data. 492 const std::string orig_schema(GetSchema(&db())); 493 const char kXSql[] = "SELECT * FROM x ORDER BY 1"; 494 const std::string orig_data(ExecuteWithResults(&db(), kXSql, "|", "\n")); 495 496 // Create a lame-duck table which will not be propagated by recovery to 497 // detect that the recovery code actually ran. 498 ASSERT_TRUE(db().Execute("CREATE TABLE y (c TEXT)")); 499 ASSERT_NE(orig_schema, GetSchema(&db())); 500 501 // Mechanically adjust the stored schema and data to allow detecting 502 // where the default value is coming from. The target table is just 503 // like the original with the default for [t] changed, to signal 504 // defaults coming from the recovery system. The two %5 rows should 505 // get the target-table default for [t], while the others should get 506 // the source-table default. 507 std::string final_schema(orig_schema); 508 std::string final_data(orig_data); 509 size_t pos; 510 while ((pos = final_schema.find("'a''a'")) != std::string::npos) { 511 final_schema.replace(pos, 6, "'c''c'"); 512 } 513 while ((pos = final_data.find("5|a'a")) != std::string::npos) { 514 final_data.replace(pos, 5, "5|c'c"); 515 } 516 517 { 518 scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path()); 519 // Different default to detect which table provides the default. 520 ASSERT_TRUE(recovery->db()->Execute(final_schema.c_str())); 521 522 size_t rows = 0; 523 EXPECT_TRUE(recovery->AutoRecoverTable("x", 0, &rows)); 524 EXPECT_EQ(4u, rows); 525 526 ASSERT_TRUE(sql::Recovery::Recovered(recovery.Pass())); 527 } 528 529 // Since the database was not corrupt, the entire schema and all 530 // data should be recovered. 531 ASSERT_TRUE(Reopen()); 532 ASSERT_EQ(final_schema, GetSchema(&db())); 533 ASSERT_EQ(final_data, ExecuteWithResults(&db(), kXSql, "|", "\n")); 534 } 535 536 // Test that rows with NULL in a NOT NULL column are filtered 537 // correctly. In the wild, this would probably happen due to 538 // corruption, but here it is simulated by recovering a table which 539 // allowed nulls into a table which does not. 540 TEST_F(SQLRecoveryTest, AutoRecoverTableNullFilter) { 541 const char kOrigSchema[] = "CREATE TABLE x (id INTEGER, t TEXT)"; 542 const char kFinalSchema[] = "CREATE TABLE x (id INTEGER, t TEXT NOT NULL)"; 543 544 ASSERT_TRUE(db().Execute(kOrigSchema)); 545 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (5, null)")); 546 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (15, 'this is a test')")); 547 548 // Create a lame-duck table which will not be propagated by recovery to 549 // detect that the recovery code actually ran. 550 ASSERT_EQ(kOrigSchema, GetSchema(&db())); 551 ASSERT_TRUE(db().Execute("CREATE TABLE y (c TEXT)")); 552 ASSERT_NE(kOrigSchema, GetSchema(&db())); 553 554 { 555 scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path()); 556 ASSERT_TRUE(recovery->db()->Execute(kFinalSchema)); 557 558 size_t rows = 0; 559 EXPECT_TRUE(recovery->AutoRecoverTable("x", 0, &rows)); 560 EXPECT_EQ(1u, rows); 561 562 ASSERT_TRUE(sql::Recovery::Recovered(recovery.Pass())); 563 } 564 565 // The schema should be the same, but only one row of data should 566 // have been recovered. 567 ASSERT_TRUE(Reopen()); 568 ASSERT_EQ(kFinalSchema, GetSchema(&db())); 569 const char kXSql[] = "SELECT * FROM x ORDER BY 1"; 570 ASSERT_EQ("15|this is a test", ExecuteWithResults(&db(), kXSql, "|", "\n")); 571 } 572 573 // Test AutoRecoverTable with a ROWID alias. 574 TEST_F(SQLRecoveryTest, AutoRecoverTableWithRowid) { 575 // The rowid alias is almost always the first column, intentionally 576 // put it later. 577 const char kCreateSql[] = 578 "CREATE TABLE x (t TEXT, id INTEGER PRIMARY KEY NOT NULL)"; 579 ASSERT_TRUE(db().Execute(kCreateSql)); 580 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES ('This is a test', null)")); 581 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES ('That was a test', null)")); 582 583 // Save aside a copy of the original schema and data. 584 const std::string orig_schema(GetSchema(&db())); 585 const char kXSql[] = "SELECT * FROM x ORDER BY 1"; 586 const std::string orig_data(ExecuteWithResults(&db(), kXSql, "|", "\n")); 587 588 // Create a lame-duck table which will not be propagated by recovery to 589 // detect that the recovery code actually ran. 590 ASSERT_TRUE(db().Execute("CREATE TABLE y (c TEXT)")); 591 ASSERT_NE(orig_schema, GetSchema(&db())); 592 593 { 594 scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path()); 595 ASSERT_TRUE(recovery->db()->Execute(kCreateSql)); 596 597 size_t rows = 0; 598 EXPECT_TRUE(recovery->AutoRecoverTable("x", 0, &rows)); 599 EXPECT_EQ(2u, rows); 600 601 ASSERT_TRUE(sql::Recovery::Recovered(recovery.Pass())); 602 } 603 604 // Since the database was not corrupt, the entire schema and all 605 // data should be recovered. 606 ASSERT_TRUE(Reopen()); 607 ASSERT_EQ(orig_schema, GetSchema(&db())); 608 ASSERT_EQ(orig_data, ExecuteWithResults(&db(), kXSql, "|", "\n")); 609 } 610 611 // Test that a compound primary key doesn't fire the ROWID code. 612 TEST_F(SQLRecoveryTest, AutoRecoverTableWithCompoundKey) { 613 const char kCreateSql[] = 614 "CREATE TABLE x (" 615 "id INTEGER NOT NULL," 616 "id2 TEXT NOT NULL," 617 "t TEXT," 618 "PRIMARY KEY (id, id2)" 619 ")"; 620 ASSERT_TRUE(db().Execute(kCreateSql)); 621 622 // NOTE(shess): Do not accidentally use [id] 1, 2, 3, as those will 623 // be the ROWID values. 624 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (1, 'a', 'This is a test')")); 625 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (1, 'b', 'That was a test')")); 626 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (2, 'a', 'Another test')")); 627 628 // Save aside a copy of the original schema and data. 629 const std::string orig_schema(GetSchema(&db())); 630 const char kXSql[] = "SELECT * FROM x ORDER BY 1"; 631 const std::string orig_data(ExecuteWithResults(&db(), kXSql, "|", "\n")); 632 633 // Create a lame-duck table which will not be propagated by recovery to 634 // detect that the recovery code actually ran. 635 ASSERT_TRUE(db().Execute("CREATE TABLE y (c TEXT)")); 636 ASSERT_NE(orig_schema, GetSchema(&db())); 637 638 { 639 scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path()); 640 ASSERT_TRUE(recovery->db()->Execute(kCreateSql)); 641 642 size_t rows = 0; 643 EXPECT_TRUE(recovery->AutoRecoverTable("x", 0, &rows)); 644 EXPECT_EQ(3u, rows); 645 646 ASSERT_TRUE(sql::Recovery::Recovered(recovery.Pass())); 647 } 648 649 // Since the database was not corrupt, the entire schema and all 650 // data should be recovered. 651 ASSERT_TRUE(Reopen()); 652 ASSERT_EQ(orig_schema, GetSchema(&db())); 653 ASSERT_EQ(orig_data, ExecuteWithResults(&db(), kXSql, "|", "\n")); 654 } 655 656 // Test |extend_columns| support. 657 TEST_F(SQLRecoveryTest, AutoRecoverTableExtendColumns) { 658 const char kCreateSql[] = "CREATE TABLE x (id INTEGER PRIMARY KEY, t0 TEXT)"; 659 ASSERT_TRUE(db().Execute(kCreateSql)); 660 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (1, 'This is')")); 661 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (2, 'That was')")); 662 663 // Save aside a copy of the original schema and data. 664 const std::string orig_schema(GetSchema(&db())); 665 const char kXSql[] = "SELECT * FROM x ORDER BY 1"; 666 const std::string orig_data(ExecuteWithResults(&db(), kXSql, "|", "\n")); 667 668 // Modify the table to add a column, and add data to that column. 669 ASSERT_TRUE(db().Execute("ALTER TABLE x ADD COLUMN t1 TEXT")); 670 ASSERT_TRUE(db().Execute("UPDATE x SET t1 = 'a test'")); 671 ASSERT_NE(orig_schema, GetSchema(&db())); 672 ASSERT_NE(orig_data, ExecuteWithResults(&db(), kXSql, "|", "\n")); 673 674 { 675 scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path()); 676 ASSERT_TRUE(recovery->db()->Execute(kCreateSql)); 677 size_t rows = 0; 678 EXPECT_TRUE(recovery->AutoRecoverTable("x", 1, &rows)); 679 EXPECT_EQ(2u, rows); 680 ASSERT_TRUE(sql::Recovery::Recovered(recovery.Pass())); 681 } 682 683 // Since the database was not corrupt, the entire schema and all 684 // data should be recovered. 685 ASSERT_TRUE(Reopen()); 686 ASSERT_EQ(orig_schema, GetSchema(&db())); 687 ASSERT_EQ(orig_data, ExecuteWithResults(&db(), kXSql, "|", "\n")); 688 } 689 690 // Recover a golden file where an interior page has been manually modified so 691 // that the number of cells is greater than will fit on a single page. This 692 // case happened in <http://crbug.com/387868>. 693 TEST_F(SQLRecoveryTest, Bug387868) { 694 base::FilePath golden_path; 695 ASSERT_TRUE(PathService::Get(sql::test::DIR_TEST_DATA, &golden_path)); 696 golden_path = golden_path.AppendASCII("recovery_387868"); 697 db().Close(); 698 ASSERT_TRUE(base::CopyFile(golden_path, db_path())); 699 ASSERT_TRUE(Reopen()); 700 701 { 702 scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path()); 703 ASSERT_TRUE(recovery.get()); 704 705 // Create the new version of the table. 706 const char kCreateSql[] = 707 "CREATE TABLE x (id INTEGER PRIMARY KEY, t0 TEXT)"; 708 ASSERT_TRUE(recovery->db()->Execute(kCreateSql)); 709 710 size_t rows = 0; 711 EXPECT_TRUE(recovery->AutoRecoverTable("x", 0, &rows)); 712 EXPECT_EQ(43u, rows); 713 714 // Successfully recovered. 715 EXPECT_TRUE(sql::Recovery::Recovered(recovery.Pass())); 716 } 717 } 718 #endif // !defined(USE_SYSTEM_SQLITE) 719 720 } // namespace 721