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