sql/recovery_unittest.cc

   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 "base/bind.h"
   6 #include "base/file_util.h"
   7 #include "base/files/scoped_temp_dir.h"
   8 #include "base/logging.h"
   9 #include "base/strings/stringprintf.h"
  10 #include "sql/connection.h"
  11 #include "sql/meta_table.h"
  12 #include "sql/recovery.h"
  13 #include "sql/statement.h"
  14 #include "sql/test/scoped_error_ignorer.h"
  15 #include "testing/gtest/include/gtest/gtest.h"
  16 #include "third_party/sqlite/sqlite3.h"
  17
  18 namespace {
  19
  20 // Execute |sql|, and stringify the results with |column_sep| between
  21 // columns and |row_sep| between rows.
  22 // TODO(shess): Promote this to a central testing helper.
  23 std::string ExecuteWithResults(sql::Connection* db,
  24                                const char* sql,
  25                                const char* column_sep,
  26                                const char* row_sep) {
  27   sql::Statement s(db->GetUniqueStatement(sql));
  28   std::string ret;
  29   while (s.Step()) {
  30     if (!ret.empty())
  31       ret += row_sep;
  32     for (int i = 0; i < s.ColumnCount(); ++i) {
  33       if (i > 0)
  34         ret += column_sep;
  35       ret += s.ColumnString(i);
  36     }
  37   }
  38   return ret;
  39 }
  40
  41 // Dump consistent human-readable representation of the database
  42 // schema.  For tables or indices, this will contain the sql command
  43 // to create the table or index.  For certain automatic SQLite
  44 // structures with no sql, the name is used.
  45 std::string GetSchema(sql::Connection* db) {
  46   const char kSql[] =
  47       "SELECT COALESCE(sql, name) FROM sqlite_master ORDER BY 1";
  48   return ExecuteWithResults(db, kSql, "|", "\n");
  49 }
  50
  51 #if !defined(USE_SYSTEM_SQLITE)
  52 int GetPageSize(sql::Connection* db) {
  53   sql::Statement s(db->GetUniqueStatement("PRAGMA page_size"));
  54   EXPECT_TRUE(s.Step());
  55   return s.ColumnInt(0);
  56 }
  57
  58 // Get |name|'s root page number in the database.
  59 int GetRootPage(sql::Connection* db, const char* name) {
  60   const char kPageSql[] = "SELECT rootpage FROM sqlite_master WHERE name = ?";
  61   sql::Statement s(db->GetUniqueStatement(kPageSql));
  62   s.BindString(0, name);
  63   EXPECT_TRUE(s.Step());
  64   return s.ColumnInt(0);
  65 }
  66
  67 // Helper to read a SQLite page into a buffer.  |page_no| is 1-based
  68 // per SQLite usage.
  69 bool ReadPage(const base::FilePath& path, size_t page_no,
  70               char* buf, size_t page_size) {
  71   file_util::ScopedFILE file(file_util::OpenFile(path, "rb"));
  72   if (!file.get())
  73     return false;
  74   if (0 != fseek(file.get(), (page_no - 1) * page_size, SEEK_SET))
  75     return false;
  76   if (1u != fread(buf, page_size, 1, file.get()))
  77     return false;
  78   return true;
  79 }
  80
  81 // Helper to write a SQLite page into a buffer.  |page_no| is 1-based
  82 // per SQLite usage.
  83 bool WritePage(const base::FilePath& path, size_t page_no,
  84                const char* buf, size_t page_size) {
  85   file_util::ScopedFILE file(file_util::OpenFile(path, "rb+"));
  86   if (!file.get())
  87     return false;
  88   if (0 != fseek(file.get(), (page_no - 1) * page_size, SEEK_SET))
  89     return false;
  90   if (1u != fwrite(buf, page_size, 1, file.get()))
  91     return false;
  92   return true;
  93 }
  94 #endif  // !defined(USE_SYSTEM_SQLITE)
  95
  96 class SQLRecoveryTest : public testing::Test {
  97  public:
  98   SQLRecoveryTest() {}
  99
 100   virtual void SetUp() {
 101     ASSERT_TRUE(temp_dir_.CreateUniqueTempDir());
 102     ASSERT_TRUE(db_.Open(db_path()));
 103   }
 104
 105   virtual void TearDown() {
 106     db_.Close();
 107   }
 108
 109   sql::Connection& db() { return db_; }
 110
 111   base::FilePath db_path() {
 112     return temp_dir_.path().AppendASCII("SQLRecoveryTest.db");
 113   }
 114
 115   bool Reopen() {
 116     db_.Close();
 117     return db_.Open(db_path());
 118   }
 119
 120  private:
 121   base::ScopedTempDir temp_dir_;
 122   sql::Connection db_;
 123 };
 124
 125 TEST_F(SQLRecoveryTest, RecoverBasic) {
 126   const char kCreateSql[] = "CREATE TABLE x (t TEXT)";
 127   const char kInsertSql[] = "INSERT INTO x VALUES ('This is a test')";
 128   ASSERT_TRUE(db().Execute(kCreateSql));
 129   ASSERT_TRUE(db().Execute(kInsertSql));
 130   ASSERT_EQ("CREATE TABLE x (t TEXT)", GetSchema(&db()));
 131
 132   // If the Recovery handle goes out of scope without being
 133   // Recovered(), the database is razed.
 134   {
 135     scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path());
 136     ASSERT_TRUE(recovery.get());
 137   }
 138   EXPECT_FALSE(db().is_open());
 139   ASSERT_TRUE(Reopen());
 140   EXPECT_TRUE(db().is_open());
 141   ASSERT_EQ("", GetSchema(&db()));
 142
 143   // Recreate the database.
 144   ASSERT_TRUE(db().Execute(kCreateSql));
 145   ASSERT_TRUE(db().Execute(kInsertSql));
 146   ASSERT_EQ("CREATE TABLE x (t TEXT)", GetSchema(&db()));
 147
 148   // Unrecoverable() also razes.
 149   {
 150     scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path());
 151     ASSERT_TRUE(recovery.get());
 152     sql::Recovery::Unrecoverable(recovery.Pass());
 153
 154     // TODO(shess): Test that calls to recover.db() start failing.
 155   }
 156   EXPECT_FALSE(db().is_open());
 157   ASSERT_TRUE(Reopen());
 158   EXPECT_TRUE(db().is_open());
 159   ASSERT_EQ("", GetSchema(&db()));
 160
 161   // Recreate the database.
 162   ASSERT_TRUE(db().Execute(kCreateSql));
 163   ASSERT_TRUE(db().Execute(kInsertSql));
 164   ASSERT_EQ("CREATE TABLE x (t TEXT)", GetSchema(&db()));
 165
 166   // Recovered() replaces the original with the "recovered" version.
 167   {
 168     scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path());
 169     ASSERT_TRUE(recovery.get());
 170
 171     // Create the new version of the table.
 172     ASSERT_TRUE(recovery->db()->Execute(kCreateSql));
 173
 174     // Insert different data to distinguish from original database.
 175     const char kAltInsertSql[] = "INSERT INTO x VALUES ('That was a test')";
 176     ASSERT_TRUE(recovery->db()->Execute(kAltInsertSql));
 177
 178     // Successfully recovered.
 179     ASSERT_TRUE(sql::Recovery::Recovered(recovery.Pass()));
 180   }
 181   EXPECT_FALSE(db().is_open());
 182   ASSERT_TRUE(Reopen());
 183   EXPECT_TRUE(db().is_open());
 184   ASSERT_EQ("CREATE TABLE x (t TEXT)", GetSchema(&db()));
 185
 186   const char* kXSql = "SELECT * FROM x ORDER BY 1";
 187   ASSERT_EQ("That was a test",
 188             ExecuteWithResults(&db(), kXSql, "|", "\n"));
 189 }
 190
 191 // The recovery virtual table is only supported for Chromium's SQLite.
 192 #if !defined(USE_SYSTEM_SQLITE)
 193
 194 // Run recovery through its paces on a valid database.
 195 TEST_F(SQLRecoveryTest, VirtualTable) {
 196   const char kCreateSql[] = "CREATE TABLE x (t TEXT)";
 197   ASSERT_TRUE(db().Execute(kCreateSql));
 198   ASSERT_TRUE(db().Execute("INSERT INTO x VALUES ('This is a test')"));
 199   ASSERT_TRUE(db().Execute("INSERT INTO x VALUES ('That was a test')"));
 200
 201   // Successfully recover the database.
 202   {
 203     scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path());
 204
 205     // Tables to recover original DB, now at [corrupt].
 206     const char kRecoveryCreateSql[] =
 207         "CREATE VIRTUAL TABLE temp.recover_x using recover("
 208         "  corrupt.x,"
 209         "  t TEXT STRICT"
 210         ")";
 211     ASSERT_TRUE(recovery->db()->Execute(kRecoveryCreateSql));
 212
 213     // Re-create the original schema.
 214     ASSERT_TRUE(recovery->db()->Execute(kCreateSql));
 215
 216     // Copy the data from the recovery tables to the new database.
 217     const char kRecoveryCopySql[] =
 218         "INSERT INTO x SELECT t FROM recover_x";
 219     ASSERT_TRUE(recovery->db()->Execute(kRecoveryCopySql));
 220
 221     // Successfully recovered.
 222     ASSERT_TRUE(sql::Recovery::Recovered(recovery.Pass()));
 223   }
 224
 225   // Since the database was not corrupt, the entire schema and all
 226   // data should be recovered.
 227   ASSERT_TRUE(Reopen());
 228   ASSERT_EQ("CREATE TABLE x (t TEXT)", GetSchema(&db()));
 229
 230   const char* kXSql = "SELECT * FROM x ORDER BY 1";
 231   ASSERT_EQ("That was a test\nThis is a test",
 232             ExecuteWithResults(&db(), kXSql, "|", "\n"));
 233 }
 234
 235 void RecoveryCallback(sql::Connection* db, const base::FilePath& db_path,
 236                       int* record_error, int error, sql::Statement* stmt) {
 237   *record_error = error;
 238
 239   // Clear the error callback to prevent reentrancy.
 240   db->reset_error_callback();
 241
 242   scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(db, db_path);
 243   ASSERT_TRUE(recovery.get());
 244
 245   const char kRecoveryCreateSql[] =
 246       "CREATE VIRTUAL TABLE temp.recover_x using recover("
 247       "  corrupt.x,"
 248       "  id INTEGER STRICT,"
 249       "  v INTEGER STRICT"
 250       ")";
 251   const char kCreateTable[] = "CREATE TABLE x (id INTEGER, v INTEGER)";
 252   const char kCreateIndex[] = "CREATE UNIQUE INDEX x_id ON x (id)";
 253
 254   // Replicate data over.
 255   const char kRecoveryCopySql[] =
 256       "INSERT OR REPLACE INTO x SELECT id, v FROM recover_x";
 257
 258   ASSERT_TRUE(recovery->db()->Execute(kRecoveryCreateSql));
 259   ASSERT_TRUE(recovery->db()->Execute(kCreateTable));
 260   ASSERT_TRUE(recovery->db()->Execute(kCreateIndex));
 261   ASSERT_TRUE(recovery->db()->Execute(kRecoveryCopySql));
 262
 263   ASSERT_TRUE(sql::Recovery::Recovered(recovery.Pass()));
 264 }
 265
 266 // Build a database, corrupt it by making an index reference to
 267 // deleted row, then recover when a query selects that row.
 268 TEST_F(SQLRecoveryTest, RecoverCorruptIndex) {
 269   const char kCreateTable[] = "CREATE TABLE x (id INTEGER, v INTEGER)";
 270   const char kCreateIndex[] = "CREATE UNIQUE INDEX x_id ON x (id)";
 271   ASSERT_TRUE(db().Execute(kCreateTable));
 272   ASSERT_TRUE(db().Execute(kCreateIndex));
 273
 274   // Insert a bit of data.
 275   {
 276     ASSERT_TRUE(db().BeginTransaction());
 277
 278     const char kInsertSql[] = "INSERT INTO x (id, v) VALUES (?, ?)";
 279     sql::Statement s(db().GetUniqueStatement(kInsertSql));
 280     for (int i = 0; i < 10; ++i) {
 281       s.Reset(true);
 282       s.BindInt(0, i);
 283       s.BindInt(1, i);
 284       EXPECT_FALSE(s.Step());
 285       EXPECT_TRUE(s.Succeeded());
 286     }
 287
 288     ASSERT_TRUE(db().CommitTransaction());
 289   }
 290
 291
 292   // Capture the index's root page into |buf|.
 293   int index_page = GetRootPage(&db(), "x_id");
 294   int page_size = GetPageSize(&db());
 295   scoped_ptr<char[]> buf(new char[page_size]);
 296   ASSERT_TRUE(ReadPage(db_path(), index_page, buf.get(), page_size));
 297
 298   // Delete the row from the table and index.
 299   ASSERT_TRUE(db().Execute("DELETE FROM x WHERE id = 0"));
 300
 301   // Close to clear any cached data.
 302   db().Close();
 303
 304   // Put the stale index page back.
 305   ASSERT_TRUE(WritePage(db_path(), index_page, buf.get(), page_size));
 306
 307   // At this point, the index references a value not in the table.
 308
 309   ASSERT_TRUE(Reopen());
 310
 311   int error = SQLITE_OK;
 312   db().set_error_callback(base::Bind(&RecoveryCallback,
 313                                      &db(), db_path(), &error));
 314
 315   // This works before the callback is called.
 316   const char kTrivialSql[] = "SELECT COUNT(*) FROM sqlite_master";
 317   EXPECT_TRUE(db().IsSQLValid(kTrivialSql));
 318
 319   // TODO(shess): Could this be delete?  Anything which fails should work.
 320   const char kSelectSql[] = "SELECT v FROM x WHERE id = 0";
 321   ASSERT_FALSE(db().Execute(kSelectSql));
 322   EXPECT_EQ(SQLITE_CORRUPT, error);
 323
 324   // Database handle has been poisoned.
 325   EXPECT_FALSE(db().IsSQLValid(kTrivialSql));
 326
 327   ASSERT_TRUE(Reopen());
 328
 329   // The recovered table should reflect the deletion.
 330   const char kSelectAllSql[] = "SELECT v FROM x ORDER BY id";
 331   EXPECT_EQ("1,2,3,4,5,6,7,8,9",
 332             ExecuteWithResults(&db(), kSelectAllSql, "|", ","));
 333
 334   // The failing statement should now succeed, with no results.
 335   EXPECT_EQ("", ExecuteWithResults(&db(), kSelectSql, "|", ","));
 336 }
 337
 338 // Build a database, corrupt it by making a table contain a row not
 339 // referenced by the index, then recover the database.
 340 TEST_F(SQLRecoveryTest, RecoverCorruptTable) {
 341   const char kCreateTable[] = "CREATE TABLE x (id INTEGER, v INTEGER)";
 342   const char kCreateIndex[] = "CREATE UNIQUE INDEX x_id ON x (id)";
 343   ASSERT_TRUE(db().Execute(kCreateTable));
 344   ASSERT_TRUE(db().Execute(kCreateIndex));
 345
 346   // Insert a bit of data.
 347   {
 348     ASSERT_TRUE(db().BeginTransaction());
 349
 350     const char kInsertSql[] = "INSERT INTO x (id, v) VALUES (?, ?)";
 351     sql::Statement s(db().GetUniqueStatement(kInsertSql));
 352     for (int i = 0; i < 10; ++i) {
 353       s.Reset(true);
 354       s.BindInt(0, i);
 355       s.BindInt(1, i);
 356       EXPECT_FALSE(s.Step());
 357       EXPECT_TRUE(s.Succeeded());
 358     }
 359
 360     ASSERT_TRUE(db().CommitTransaction());
 361   }
 362
 363   // Capture the table's root page into |buf|.
 364   // Find the page the table is stored on.
 365   const int table_page = GetRootPage(&db(), "x");
 366   const int page_size = GetPageSize(&db());
 367   scoped_ptr<char[]> buf(new char[page_size]);
 368   ASSERT_TRUE(ReadPage(db_path(), table_page, buf.get(), page_size));
 369
 370   // Delete the row from the table and index.
 371   ASSERT_TRUE(db().Execute("DELETE FROM x WHERE id = 0"));
 372
 373   // Close to clear any cached data.
 374   db().Close();
 375
 376   // Put the stale table page back.
 377   ASSERT_TRUE(WritePage(db_path(), table_page, buf.get(), page_size));
 378
 379   // At this point, the table contains a value not referenced by the
 380   // index.
 381   // TODO(shess): Figure out a query which causes SQLite to notice
 382   // this organically.  Meanwhile, just handle it manually.
 383
 384   ASSERT_TRUE(Reopen());
 385
 386   // Index shows one less than originally inserted.
 387   const char kCountSql[] = "SELECT COUNT (*) FROM x";
 388   EXPECT_EQ("9", ExecuteWithResults(&db(), kCountSql, "|", ","));
 389
 390   // A full table scan shows all of the original data.
 391   const char kDistinctSql[] = "SELECT DISTINCT COUNT (id) FROM x";
 392   EXPECT_EQ("10", ExecuteWithResults(&db(), kDistinctSql, "|", ","));
 393
 394   // Insert id 0 again.  Since it is not in the index, the insert
 395   // succeeds, but results in a duplicate value in the table.
 396   const char kInsertSql[] = "INSERT INTO x (id, v) VALUES (0, 100)";
 397   ASSERT_TRUE(db().Execute(kInsertSql));
 398
 399   // Duplication is visible.
 400   EXPECT_EQ("10", ExecuteWithResults(&db(), kCountSql, "|", ","));
 401   EXPECT_EQ("11", ExecuteWithResults(&db(), kDistinctSql, "|", ","));
 402
 403   // This works before the callback is called.
 404   const char kTrivialSql[] = "SELECT COUNT(*) FROM sqlite_master";
 405   EXPECT_TRUE(db().IsSQLValid(kTrivialSql));
 406
 407   // Call the recovery callback manually.
 408   int error = SQLITE_OK;
 409   RecoveryCallback(&db(), db_path(), &error, SQLITE_CORRUPT, NULL);
 410   EXPECT_EQ(SQLITE_CORRUPT, error);
 411
 412   // Database handle has been poisoned.
 413   EXPECT_FALSE(db().IsSQLValid(kTrivialSql));
 414
 415   ASSERT_TRUE(Reopen());
 416
 417   // The recovered table has consistency between the index and the table.
 418   EXPECT_EQ("10", ExecuteWithResults(&db(), kCountSql, "|", ","));
 419   EXPECT_EQ("10", ExecuteWithResults(&db(), kDistinctSql, "|", ","));
 420
 421   // The expected value was retained.
 422   const char kSelectSql[] = "SELECT v FROM x WHERE id = 0";
 423   EXPECT_EQ("100", ExecuteWithResults(&db(), kSelectSql, "|", ","));
 424 }
 425 #endif  // !defined(USE_SYSTEM_SQLITE)
 426
 427 }  // namespace