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