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
18namespace {
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.
23std::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.
45std::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
51int 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.
58int 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.
68bool 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.
82bool 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
94class 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
123TEST_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.
193TEST_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
233void 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.
266TEST_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.
338TEST_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
426