[sql] Use recover virtual table in sql::Recovery.

sql/recovery_unittest.cc

 // Copyright 2013 The Chromium Authors. All rights reserved.
 // Use of this source code is governed by a BSD-style license that can be
 // found in the LICENSE file.
 
+#include "base/bind.h"
 #include "base/file_util.h"
 #include "base/files/scoped_temp_dir.h"
 #include "base/logging.h"
 #include "base/strings/stringprintf.h"
 #include "sql/connection.h"
 #include "sql/meta_table.h"
 #include "sql/recovery.h"
 #include "sql/statement.h"
 #include "sql/test/scoped_error_ignorer.h"
 #include "testing/gtest/include/gtest/gtest.h"
@@ skipping 116 matching lines @@
 
     // Successfully recovered.
     ASSERT_TRUE(sql::Recovery::Recovered(recovery.Pass()));
   }
   EXPECT_FALSE(db().is_open());
   ASSERT_TRUE(Reopen());
   EXPECT_TRUE(db().is_open());
   ASSERT_EQ("CREATE TABLE x (t TEXT)", GetSchema(&db()));
 
   const char* kXSql = "SELECT * FROM x ORDER BY 1";
-  ASSERT_EQ(ExecuteWithResults(&db(), kXSql, "|", "\n"),
-            "That was a test");
-}
+  ASSERT_EQ("That was a test",
+            ExecuteWithResults(&db(), kXSql, "|", "\n"));

[Scott Hess - ex-Googler, 2013/07/23 22:32:05]

Noticed that the expectation was in the wrong place when auditing expectations
in the following code, so changed it.  Otherwise not really related.

+}
+
+// The recovery virtual table is only supported for our SQLite.
+#if !defined(USE_SYSTEM_SQLITE)
+
+// Run recovery through its paces on a valid database.
+TEST_F(SQLRecoveryTest, VirtualTable) {
+  const char kCreateSql[] = "CREATE TABLE x (t TEXT)";
+  ASSERT_TRUE(db().Execute(kCreateSql));
+  ASSERT_TRUE(db().Execute("INSERT INTO x VALUES ('This is a test')"));
+  ASSERT_TRUE(db().Execute("INSERT INTO x VALUES ('That was a test')"));
+
+  // Successfully recover the database.
+  {
+    scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path());
+
+    // Tables to recover original DB, now at [corrupt].
+    const char kRecoveryCreateSql[] =
+        "CREATE VIRTUAL TABLE temp.recover_x using recover("
+        "  corrupt.x,"
+        "  t TEXT STRICT"
+        ")";
+    ASSERT_TRUE(recovery->db()->Execute(kRecoveryCreateSql));
+
+    // Re-create the original schema.
+    ASSERT_TRUE(recovery->db()->Execute(kCreateSql));
+
+    // Copy the data from the recovery tables to the new database.
+    const char kRecoveryCopySql[] =
+        "INSERT INTO x SELECT t FROM recover_x";
+    ASSERT_TRUE(recovery->db()->Execute(kRecoveryCopySql));
+
+    // Successfully recovered.
+    ASSERT_TRUE(sql::Recovery::Recovered(recovery.Pass()));
+  }
+
+  // Since the database was not corrupt, the entire schema and all
+  // data should be recovered.
+  ASSERT_TRUE(Reopen());
+  ASSERT_EQ("CREATE TABLE x (t TEXT)", GetSchema(&db()));
+
+  const char* kXSql = "SELECT * FROM x ORDER BY 1";
+  ASSERT_EQ("That was a test\nThis is a test",
+            ExecuteWithResults(&db(), kXSql, "|", "\n"));
+}
+
+void RecoveryCallback(sql::Connection* db, const base::FilePath& db_path,
+                      int* record_error, int error, sql::Statement* stmt) {
+  *record_error = error;
+
+  // Clear the error callback to prevent reentrancy.
+  db->reset_error_callback();
+
+  scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(db, db_path);
+  ASSERT_TRUE(recovery.get());
+
+  const char kCreateRecoveryTable[] =
+      "CREATE VIRTUAL TABLE temp.recover_x using recover("
+      "  corrupt.x,"
+      "  id INTEGER STRICT,"
+      "  v INTEGER STRICT"
+      ")";
+  const char kCreateTable[] = "CREATE TABLE x (id INTEGER, v INTEGER)";
+  const char kCreateIndex[] = "CREATE UNIQUE INDEX x_id ON x (id)";
+
+  // Replicate data over.
+  const char kInsertSql[] =
+      "INSERT OR REPLACE INTO x SELECT id, v FROM recover_x";
+
+  ASSERT_TRUE(recovery->db()->Execute(kCreateRecoveryTable));
+  ASSERT_TRUE(recovery->db()->Execute(kCreateTable));
+  ASSERT_TRUE(recovery->db()->Execute(kCreateIndex));
+  ASSERT_TRUE(recovery->db()->Execute(kInsertSql));
+
+  ASSERT_TRUE(sql::Recovery::Recovered(recovery.Pass()));
+}
+
+// Build a databases, corrupt it by making an index contain a
+// reference to a deleted row, then recover it in response to a query
+// which hits that row.
+TEST_F(SQLRecoveryTest, RecoverCorruptIndex) {
+  const char kCreateTable[] = "CREATE TABLE x (id INTEGER, v INTEGER)";
+  const char kCreateIndex[] = "CREATE UNIQUE INDEX x_id ON x (id)";
+  ASSERT_TRUE(db().Execute(kCreateTable));
+  ASSERT_TRUE(db().Execute(kCreateIndex));
+
+  // Insert a bit of data.
+  {
+    ASSERT_TRUE(db().BeginTransaction());
+
+    const char kInsertSql[] = "INSERT INTO x (id, v) VALUES (?, ?)";
+    sql::Statement s(db().GetUniqueStatement(kInsertSql));
+    for (int i = 0; i < 10; ++i) {
+      s.Reset(true);
+      s.BindInt(0, i);
+      s.BindInt(1, i);
+      EXPECT_FALSE(s.Step());
+      EXPECT_TRUE(s.Succeeded());
+    }
+
+    ASSERT_TRUE(db().CommitTransaction());
+  }
+
+  int page_size = 0;
+  {
+    sql::Statement s(db().GetUniqueStatement("PRAGMA page_size"));
+    ASSERT_TRUE(s.Step());
+    page_size = s.ColumnInt(0);
+  }
+
+  // Find the page the index is stored on.
+  int index_page = 0;
+  {
+    const char kPageSql[] = "SELECT rootpage FROM sqlite_master WHERE name = ?";
+    sql::Statement s(db().GetUniqueStatement(kPageSql));
+    s.BindString(0, "x_id");
+    EXPECT_TRUE(s.Step());
+    index_page = s.ColumnInt(0);
+  }
+
+  // Capture the index page into |buf|.
+  scoped_ptr<char[]> buf(new char[page_size]);
+  {
+    file_util::ScopedFILE file(file_util::OpenFile(db_path(), "rb"));
+    ASSERT_TRUE(file.get() != NULL);
+    ASSERT_EQ(0, fseek(file.get(), (index_page-1)*page_size, SEEK_SET));
+    EXPECT_EQ(1u, fread(buf.get(), page_size, 1, file.get()));
+  }
+
+  // Delete the row from the table and index.
+  ASSERT_TRUE(db().Execute("DELETE FROM x WHERE id = 0"));
+
+  // Close to clear and cached data.
+  db().Close();
+
+  // Put the stale index page back.  This corrupts the database.
+  {
+    file_util::ScopedFILE file(file_util::OpenFile(db_path(), "rb+"));
+    ASSERT_TRUE(file.get() != NULL);
+    ASSERT_EQ(0, fseek(file.get(), (index_page-1)*page_size, SEEK_SET));
+    EXPECT_EQ(1u, fwrite(buf.get(), page_size, 1, file.get()));
+  }
+
+  ASSERT_TRUE(Reopen());
+
+  int error = SQLITE_OK;
+  db().set_error_callback(base::Bind(&RecoveryCallback,
+                                     &db(), db_path(), &error));
+
+  // This works before the callback is called.
+  const char kTrivialSql[] = "SELECT COUNT(*) FROM sqlite_master";
+  EXPECT_TRUE(db().IsSQLValid(kTrivialSql));
+
+  // TODO(shess): Could this be delete?  Anything which fails should work.
+  const char kSelectSql[] = "SELECT v FROM x WHERE id = 0";
+  ASSERT_FALSE(db().Execute(kSelectSql));
+  EXPECT_EQ(SQLITE_CORRUPT, error);
+
+  // Database handle has been poisoned.
+  EXPECT_FALSE(db().IsSQLValid(kTrivialSql));
+
+  ASSERT_TRUE(Reopen());
+
+  // The recovered table should reflect the deletion.
+  const char kSelectAllSql[] = "SELECT v FROM x ORDER BY id";
+  EXPECT_EQ("1,2,3,4,5,6,7,8,9",
+            ExecuteWithResults(&db(), kSelectAllSql, "|", ","));
+
+  // The earlier statement should now execute successfully, with no results.
+  EXPECT_EQ("", ExecuteWithResults(&db(), kSelectSql, "|", ","));
+}
+
+// Build a databases, corrupt it by making an table containing a row
+// which is not referenced by the index, then recover it.
+TEST_F(SQLRecoveryTest, RecoverCorruptTable) {
+  const char kCreateTable[] = "CREATE TABLE x (id INTEGER, v INTEGER)";
+  const char kCreateIndex[] = "CREATE UNIQUE INDEX x_id ON x (id)";
+  ASSERT_TRUE(db().Execute(kCreateTable));
+  ASSERT_TRUE(db().Execute(kCreateIndex));
+
+  // Insert a bit of data.
+  {
+    ASSERT_TRUE(db().BeginTransaction());
+
+    const char kInsertSql[] = "INSERT INTO x (id, v) VALUES (?, ?)";
+    sql::Statement s(db().GetUniqueStatement(kInsertSql));
+    for (int i = 0; i < 10; ++i) {
+      s.Reset(true);
+      s.BindInt(0, i);
+      s.BindInt(1, i);
+      EXPECT_FALSE(s.Step());
+      EXPECT_TRUE(s.Succeeded());
+    }
+
+    ASSERT_TRUE(db().CommitTransaction());
+  }
+
+  // Find the page the table is stored on.
+  int table_page = 0;
+  {
+    const char kPageSql[] = "SELECT rootpage FROM sqlite_master WHERE name = ?";
+    sql::Statement s(db().GetUniqueStatement(kPageSql));
+    s.BindString(0, "x");
+    EXPECT_TRUE(s.Step());
+    table_page = s.ColumnInt(0);
+  }
+
+  int page_size = 0;
+  {
+    sql::Statement s(db().GetUniqueStatement("PRAGMA page_size"));
+    ASSERT_TRUE(s.Step());
+    page_size = s.ColumnInt(0);
+  }
+
+  // Capture the table page into |buf|.
+  scoped_ptr<char[]> buf(new char[page_size]);
+  {
+    file_util::ScopedFILE file(file_util::OpenFile(db_path(), "rb"));
+    ASSERT_TRUE(file.get() != NULL);
+    ASSERT_EQ(0, fseek(file.get(), (table_page-1)*page_size, SEEK_SET));
+    EXPECT_EQ(1u, fread(buf.get(), page_size, 1, file.get()));
+  }
+
+  // Delete the row from the table and index.
+  ASSERT_TRUE(db().Execute("DELETE FROM x WHERE id = 0"));
+
+  // Close to clear and cached data.
+  db().Close();
+
+  // Put the stale table page back.
+  {
+    file_util::ScopedFILE file(file_util::OpenFile(db_path(), "rb+"));
+    ASSERT_TRUE(file.get() != NULL);
+    ASSERT_EQ(0, fseek(file.get(), (table_page-1)*page_size, SEEK_SET));
+    EXPECT_EQ(1u, fwrite(buf.get(), page_size, 1, file.get()));
+  }
+
+  // At this point, the table contains a value not referenced by the
+  // index.
+  // TODO(shess): Figure out a query which causes SQLite to notice
+  // this organically.  Meanwhile, just handle it manually.
+
+  ASSERT_TRUE(Reopen());
+
+  // Index shows one less than originally inserted.
+  const char kCountSql[] = "SELECT COUNT (*) FROM x";
+  EXPECT_EQ("9", ExecuteWithResults(&db(), kCountSql, "|", ","));
+
+  // But a table scan shows all of the original data.
+  const char kDistinctSql[] = "SELECT DISTINCT COUNT (id) FROM x";
+  EXPECT_EQ("10", ExecuteWithResults(&db(), kDistinctSql, "|", ","));
+
+  // Insert id 0 again.  Since it is not in the index, the insert
+  // succeeds, but results in a duplicate value in the table.
+  const char kInsertSql[] = "INSERT INTO x (id, v) VALUES (0, 100)";
+  ASSERT_TRUE(db().Execute(kInsertSql));
+
+  // Values are in expected places.
+  EXPECT_EQ("10", ExecuteWithResults(&db(), kCountSql, "|", ","));
+  EXPECT_EQ("11", ExecuteWithResults(&db(), kDistinctSql, "|", ","));
+
+  // This works before the callback is called.
+  const char kTrivialSql[] = "SELECT COUNT(*) FROM sqlite_master";
+  EXPECT_TRUE(db().IsSQLValid(kTrivialSql));
+
+  // Call the recovery callback manually.
+  int error = SQLITE_OK;
+  RecoveryCallback(&db(), db_path(), &error, SQLITE_CORRUPT, NULL);
+  EXPECT_EQ(SQLITE_CORRUPT, error);
+
+  // Database handle has been poisoned.
+  EXPECT_FALSE(db().IsSQLValid(kTrivialSql));
+
+  ASSERT_TRUE(Reopen());
+
+  // The recovered table has consistency between the index and the table.
+  EXPECT_EQ("10", ExecuteWithResults(&db(), kCountSql, "|", ","));
+  EXPECT_EQ("10", ExecuteWithResults(&db(), kDistinctSql, "|", ","));
+
+  // The expected value was retained.
+  const char kSelectSql[] = "SELECT v FROM x WHERE id = 0";
+  EXPECT_EQ("100", ExecuteWithResults(&db(), kSelectSql, "|", ","));
+}
+#endif  // !defined(USE_SYSTEM_SQLITE)
 
 }  // namespace