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