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.  Using column [v] to
 332   // force use of the table rather than the index.
 333   const char kDistinctSql[] = "SELECT DISTINCT COUNT (v) FROM x";
 334   EXPECT_EQ("10", ExecuteWithResults(&db(), kDistinctSql, "|", ","));
 335
 336   // Insert id 0 again.  Since it is not in the index, the insert
 337   // succeeds, but results in a duplicate value in the table.
 338   const char kInsertSql[] = "INSERT INTO x (id, v) VALUES (0, 100)";
 339   ASSERT_TRUE(db().Execute(kInsertSql));
 340
 341   // Duplication is visible.
 342   EXPECT_EQ("10", ExecuteWithResults(&db(), kCountSql, "|", ","));
 343   EXPECT_EQ("11", ExecuteWithResults(&db(), kDistinctSql, "|", ","));
 344
 345   // This works before the callback is called.
 346   const char kTrivialSql[] = "SELECT COUNT(*) FROM sqlite_master";
 347   EXPECT_TRUE(db().IsSQLValid(kTrivialSql));
 348
 349   // Call the recovery callback manually.
 350   int error = SQLITE_OK;
 351   RecoveryCallback(&db(), db_path(), &error, SQLITE_CORRUPT, NULL);
 352   EXPECT_EQ(SQLITE_CORRUPT, error);
 353
 354   // Database handle has been poisoned.
 355   EXPECT_FALSE(db().IsSQLValid(kTrivialSql));
 356
 357   ASSERT_TRUE(Reopen());
 358
 359   // The recovered table has consistency between the index and the table.
 360   EXPECT_EQ("10", ExecuteWithResults(&db(), kCountSql, "|", ","));
 361   EXPECT_EQ("10", ExecuteWithResults(&db(), kDistinctSql, "|", ","));
 362
 363   // The expected value was retained.
 364   const char kSelectSql[] = "SELECT v FROM x WHERE id = 0";
 365   EXPECT_EQ("100", ExecuteWithResults(&db(), kSelectSql, "|", ","));
 366 }
 367
 368 TEST_F(SQLRecoveryTest, Meta) {
 369   const int kVersion = 3;
 370   const int kCompatibleVersion = 2;
 371
 372   {
 373     sql::MetaTable meta;
 374     EXPECT_TRUE(meta.Init(&db(), kVersion, kCompatibleVersion));
 375     EXPECT_EQ(kVersion, meta.GetVersionNumber());
 376   }
 377
 378   // Test expected case where everything works.
 379   {
 380     scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path());
 381     EXPECT_TRUE(recovery->SetupMeta());
 382     int version = 0;
 383     EXPECT_TRUE(recovery->GetMetaVersionNumber(&version));
 384     EXPECT_EQ(kVersion, version);
 385
 386     sql::Recovery::Rollback(recovery.Pass());
 387   }
 388   ASSERT_TRUE(Reopen());  // Handle was poisoned.
 389
 390   // Test version row missing.
 391   EXPECT_TRUE(db().Execute("DELETE FROM meta WHERE key = 'version'"));
 392   {
 393     scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path());
 394     EXPECT_TRUE(recovery->SetupMeta());
 395     int version = 0;
 396     EXPECT_FALSE(recovery->GetMetaVersionNumber(&version));
 397     EXPECT_EQ(0, version);
 398
 399     sql::Recovery::Rollback(recovery.Pass());
 400   }
 401   ASSERT_TRUE(Reopen());  // Handle was poisoned.
 402
 403   // Test meta table missing.
 404   EXPECT_TRUE(db().Execute("DROP TABLE meta"));
 405   {
 406     sql::ScopedErrorIgnorer ignore_errors;
 407     ignore_errors.IgnoreError(SQLITE_CORRUPT);  // From virtual table.
 408     scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path());
 409     EXPECT_FALSE(recovery->SetupMeta());
 410     ASSERT_TRUE(ignore_errors.CheckIgnoredErrors());
 411   }
 412 }
 413
 414 // Baseline AutoRecoverTable() test.
 415 TEST_F(SQLRecoveryTest, AutoRecoverTable) {
 416   // BIGINT and VARCHAR to test type affinity.
 417   const char kCreateSql[] = "CREATE TABLE x (id BIGINT, t TEXT, v VARCHAR)";
 418   ASSERT_TRUE(db().Execute(kCreateSql));
 419   ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (11, 'This is', 'a test')"));
 420   ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (5, 'That was', 'a test')"));
 421
 422   // Save aside a copy of the original schema and data.
 423   const std::string orig_schema(GetSchema(&db()));
 424   const char kXSql[] = "SELECT * FROM x ORDER BY 1";
 425   const std::string orig_data(ExecuteWithResults(&db(), kXSql, "|", "\n"));
 426
 427   // Create a lame-duck table which will not be propagated by recovery to
 428   // detect that the recovery code actually ran.
 429   ASSERT_TRUE(db().Execute("CREATE TABLE y (c TEXT)"));
 430   ASSERT_NE(orig_schema, GetSchema(&db()));
 431
 432   {
 433     scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path());
 434     ASSERT_TRUE(recovery->db()->Execute(kCreateSql));
 435
 436     // Save a copy of the temp db's schema before recovering the table.
 437     const char kTempSchemaSql[] = "SELECT name, sql FROM sqlite_temp_master";
 438     const std::string temp_schema(
 439         ExecuteWithResults(recovery->db(), kTempSchemaSql, "|", "\n"));
 440
 441     size_t rows = 0;
 442     EXPECT_TRUE(recovery->AutoRecoverTable("x", 0, &rows));
 443     EXPECT_EQ(2u, rows);
 444
 445     // Test that any additional temp tables were cleaned up.
 446     EXPECT_EQ(temp_schema,
 447               ExecuteWithResults(recovery->db(), kTempSchemaSql, "|", "\n"));
 448
 449     ASSERT_TRUE(sql::Recovery::Recovered(recovery.Pass()));
 450   }
 451
 452   // Since the database was not corrupt, the entire schema and all
 453   // data should be recovered.
 454   ASSERT_TRUE(Reopen());
 455   ASSERT_EQ(orig_schema, GetSchema(&db()));
 456   ASSERT_EQ(orig_data, ExecuteWithResults(&db(), kXSql, "|", "\n"));
 457
 458   // Recovery fails if the target table doesn't exist.
 459   {
 460     scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path());
 461     ASSERT_TRUE(recovery->db()->Execute(kCreateSql));
 462
 463     // TODO(shess): Should this failure implicitly lead to Raze()?
 464     size_t rows = 0;
 465     EXPECT_FALSE(recovery->AutoRecoverTable("y", 0, &rows));
 466
 467     sql::Recovery::Unrecoverable(recovery.Pass());
 468   }
 469 }
 470
 471 // Test that default values correctly replace nulls.  The recovery
 472 // virtual table reads directly from the database, so DEFAULT is not
 473 // interpretted at that level.
 474 TEST_F(SQLRecoveryTest, AutoRecoverTableWithDefault) {
 475   ASSERT_TRUE(db().Execute("CREATE TABLE x (id INTEGER)"));
 476   ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (5)"));
 477   ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (15)"));
 478
 479   // ALTER effectively leaves the new columns NULL in the first two
 480   // rows.  The row with 17 will get the default injected at insert
 481   // time, while the row with 42 will get the actual value provided.
 482   // Embedded "'" to make sure default-handling continues to be quoted
 483   // correctly.
 484   ASSERT_TRUE(db().Execute("ALTER TABLE x ADD COLUMN t TEXT DEFAULT 'a''a'"));
 485   ASSERT_TRUE(db().Execute("ALTER TABLE x ADD COLUMN b BLOB DEFAULT x'AA55'"));
 486   ASSERT_TRUE(db().Execute("ALTER TABLE x ADD COLUMN i INT DEFAULT 93"));
 487   ASSERT_TRUE(db().Execute("INSERT INTO x (id) VALUES (17)"));
 488   ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (42, 'b', x'1234', 12)"));
 489
 490   // Save aside a copy of the original schema and data.
 491   const std::string orig_schema(GetSchema(&db()));
 492   const char kXSql[] = "SELECT * FROM x ORDER BY 1";
 493   const std::string orig_data(ExecuteWithResults(&db(), kXSql, "|", "\n"));
 494
 495   // Create a lame-duck table which will not be propagated by recovery to
 496   // detect that the recovery code actually ran.
 497   ASSERT_TRUE(db().Execute("CREATE TABLE y (c TEXT)"));
 498   ASSERT_NE(orig_schema, GetSchema(&db()));
 499
 500   // Mechanically adjust the stored schema and data to allow detecting
 501   // where the default value is coming from.  The target table is just
 502   // like the original with the default for [t] changed, to signal
 503   // defaults coming from the recovery system.  The two %5 rows should
 504   // get the target-table default for [t], while the others should get
 505   // the source-table default.
 506   std::string final_schema(orig_schema);
 507   std::string final_data(orig_data);
 508   size_t pos;
 509   while ((pos = final_schema.find("'a''a'")) != std::string::npos) {
 510     final_schema.replace(pos, 6, "'c''c'");
 511   }
 512   while ((pos = final_data.find("5|a'a")) != std::string::npos) {
 513     final_data.replace(pos, 5, "5|c'c");
 514   }
 515
 516   {
 517     scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path());
 518     // Different default to detect which table provides the default.
 519     ASSERT_TRUE(recovery->db()->Execute(final_schema.c_str()));
 520
 521     size_t rows = 0;
 522     EXPECT_TRUE(recovery->AutoRecoverTable("x", 0, &rows));
 523     EXPECT_EQ(4u, rows);
 524
 525     ASSERT_TRUE(sql::Recovery::Recovered(recovery.Pass()));
 526   }
 527
 528   // Since the database was not corrupt, the entire schema and all
 529   // data should be recovered.
 530   ASSERT_TRUE(Reopen());
 531   ASSERT_EQ(final_schema, GetSchema(&db()));
 532   ASSERT_EQ(final_data, ExecuteWithResults(&db(), kXSql, "|", "\n"));
 533 }
 534
 535 // Test that rows with NULL in a NOT NULL column are filtered
 536 // correctly.  In the wild, this would probably happen due to
 537 // corruption, but here it is simulated by recovering a table which
 538 // allowed nulls into a table which does not.
 539 TEST_F(SQLRecoveryTest, AutoRecoverTableNullFilter) {
 540   const char kOrigSchema[] = "CREATE TABLE x (id INTEGER, t TEXT)";
 541   const char kFinalSchema[] = "CREATE TABLE x (id INTEGER, t TEXT NOT NULL)";
 542
 543   ASSERT_TRUE(db().Execute(kOrigSchema));
 544   ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (5, null)"));
 545   ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (15, 'this is a test')"));
 546
 547   // Create a lame-duck table which will not be propagated by recovery to
 548   // detect that the recovery code actually ran.
 549   ASSERT_EQ(kOrigSchema, GetSchema(&db()));
 550   ASSERT_TRUE(db().Execute("CREATE TABLE y (c TEXT)"));
 551   ASSERT_NE(kOrigSchema, GetSchema(&db()));
 552
 553   {
 554     scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path());
 555     ASSERT_TRUE(recovery->db()->Execute(kFinalSchema));
 556
 557     size_t rows = 0;
 558     EXPECT_TRUE(recovery->AutoRecoverTable("x", 0, &rows));
 559     EXPECT_EQ(1u, rows);
 560
 561     ASSERT_TRUE(sql::Recovery::Recovered(recovery.Pass()));
 562   }
 563
 564   // The schema should be the same, but only one row of data should
 565   // have been recovered.
 566   ASSERT_TRUE(Reopen());
 567   ASSERT_EQ(kFinalSchema, GetSchema(&db()));
 568   const char kXSql[] = "SELECT * FROM x ORDER BY 1";
 569   ASSERT_EQ("15|this is a test", ExecuteWithResults(&db(), kXSql, "|", "\n"));
 570 }
 571
 572 // Test AutoRecoverTable with a ROWID alias.
 573 TEST_F(SQLRecoveryTest, AutoRecoverTableWithRowid) {
 574   // The rowid alias is almost always the first column, intentionally
 575   // put it later.
 576   const char kCreateSql[] =
 577       "CREATE TABLE x (t TEXT, id INTEGER PRIMARY KEY NOT NULL)";
 578   ASSERT_TRUE(db().Execute(kCreateSql));
 579   ASSERT_TRUE(db().Execute("INSERT INTO x VALUES ('This is a test', null)"));
 580   ASSERT_TRUE(db().Execute("INSERT INTO x VALUES ('That was a test', null)"));
 581
 582   // Save aside a copy of the original schema and data.
 583   const std::string orig_schema(GetSchema(&db()));
 584   const char kXSql[] = "SELECT * FROM x ORDER BY 1";
 585   const std::string orig_data(ExecuteWithResults(&db(), kXSql, "|", "\n"));
 586
 587   // Create a lame-duck table which will not be propagated by recovery to
 588   // detect that the recovery code actually ran.
 589   ASSERT_TRUE(db().Execute("CREATE TABLE y (c TEXT)"));
 590   ASSERT_NE(orig_schema, GetSchema(&db()));
 591
 592   {
 593     scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path());
 594     ASSERT_TRUE(recovery->db()->Execute(kCreateSql));
 595
 596     size_t rows = 0;
 597     EXPECT_TRUE(recovery->AutoRecoverTable("x", 0, &rows));
 598     EXPECT_EQ(2u, rows);
 599
 600     ASSERT_TRUE(sql::Recovery::Recovered(recovery.Pass()));
 601   }
 602
 603   // Since the database was not corrupt, the entire schema and all
 604   // data should be recovered.
 605   ASSERT_TRUE(Reopen());
 606   ASSERT_EQ(orig_schema, GetSchema(&db()));
 607   ASSERT_EQ(orig_data, ExecuteWithResults(&db(), kXSql, "|", "\n"));
 608 }
 609
 610 // Test that a compound primary key doesn't fire the ROWID code.
 611 TEST_F(SQLRecoveryTest, AutoRecoverTableWithCompoundKey) {
 612   const char kCreateSql[] =
 613       "CREATE TABLE x ("
 614       "id INTEGER NOT NULL,"
 615       "id2 TEXT NOT NULL,"
 616       "t TEXT,"
 617       "PRIMARY KEY (id, id2)"
 618       ")";
 619   ASSERT_TRUE(db().Execute(kCreateSql));
 620
 621   // NOTE(shess): Do not accidentally use [id] 1, 2, 3, as those will
 622   // be the ROWID values.
 623   ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (1, 'a', 'This is a test')"));
 624   ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (1, 'b', 'That was a test')"));
 625   ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (2, 'a', 'Another test')"));
 626
 627   // Save aside a copy of the original schema and data.
 628   const std::string orig_schema(GetSchema(&db()));
 629   const char kXSql[] = "SELECT * FROM x ORDER BY 1";
 630   const std::string orig_data(ExecuteWithResults(&db(), kXSql, "|", "\n"));
 631
 632   // Create a lame-duck table which will not be propagated by recovery to
 633   // detect that the recovery code actually ran.
 634   ASSERT_TRUE(db().Execute("CREATE TABLE y (c TEXT)"));
 635   ASSERT_NE(orig_schema, GetSchema(&db()));
 636
 637   {
 638     scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path());
 639     ASSERT_TRUE(recovery->db()->Execute(kCreateSql));
 640
 641     size_t rows = 0;
 642     EXPECT_TRUE(recovery->AutoRecoverTable("x", 0, &rows));
 643     EXPECT_EQ(3u, rows);
 644
 645     ASSERT_TRUE(sql::Recovery::Recovered(recovery.Pass()));
 646   }
 647
 648   // Since the database was not corrupt, the entire schema and all
 649   // data should be recovered.
 650   ASSERT_TRUE(Reopen());
 651   ASSERT_EQ(orig_schema, GetSchema(&db()));
 652   ASSERT_EQ(orig_data, ExecuteWithResults(&db(), kXSql, "|", "\n"));
 653 }
 654
 655 // Test |extend_columns| support.
 656 TEST_F(SQLRecoveryTest, AutoRecoverTableExtendColumns) {
 657   const char kCreateSql[] = "CREATE TABLE x (id INTEGER PRIMARY KEY, t0 TEXT)";
 658   ASSERT_TRUE(db().Execute(kCreateSql));
 659   ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (1, 'This is')"));
 660   ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (2, 'That was')"));
 661
 662   // Save aside a copy of the original schema and data.
 663   const std::string orig_schema(GetSchema(&db()));
 664   const char kXSql[] = "SELECT * FROM x ORDER BY 1";
 665   const std::string orig_data(ExecuteWithResults(&db(), kXSql, "|", "\n"));
 666
 667   // Modify the table to add a column, and add data to that column.
 668   ASSERT_TRUE(db().Execute("ALTER TABLE x ADD COLUMN t1 TEXT"));
 669   ASSERT_TRUE(db().Execute("UPDATE x SET t1 = 'a test'"));
 670   ASSERT_NE(orig_schema, GetSchema(&db()));
 671   ASSERT_NE(orig_data, ExecuteWithResults(&db(), kXSql, "|", "\n"));
 672
 673   {
 674     scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path());
 675     ASSERT_TRUE(recovery->db()->Execute(kCreateSql));
 676     size_t rows = 0;
 677     EXPECT_TRUE(recovery->AutoRecoverTable("x", 1, &rows));
 678     EXPECT_EQ(2u, rows);
 679     ASSERT_TRUE(sql::Recovery::Recovered(recovery.Pass()));
 680   }
 681
 682   // Since the database was not corrupt, the entire schema and all
 683   // data should be recovered.
 684   ASSERT_TRUE(Reopen());
 685   ASSERT_EQ(orig_schema, GetSchema(&db()));
 686   ASSERT_EQ(orig_data, ExecuteWithResults(&db(), kXSql, "|", "\n"));
 687 }
 688
 689 // Recover a golden file where an interior page has been manually modified so
 690 // that the number of cells is greater than will fit on a single page.  This
 691 // case happened in <http://crbug.com/387868>.
 692 TEST_F(SQLRecoveryTest, Bug387868) {
 693   base::FilePath golden_path;
 694   ASSERT_TRUE(PathService::Get(sql::test::DIR_TEST_DATA, &golden_path));
 695   golden_path = golden_path.AppendASCII("recovery_387868");
 696   db().Close();
 697   ASSERT_TRUE(base::CopyFile(golden_path, db_path()));
 698   ASSERT_TRUE(Reopen());
 699
 700   {
 701     scoped_ptr<sql::Recovery> recovery = sql::Recovery::Begin(&db(), db_path());
 702     ASSERT_TRUE(recovery.get());
 703
 704     // Create the new version of the table.
 705     const char kCreateSql[] =
 706         "CREATE TABLE x (id INTEGER PRIMARY KEY, t0 TEXT)";
 707     ASSERT_TRUE(recovery->db()->Execute(kCreateSql));
 708
 709     size_t rows = 0;
 710     EXPECT_TRUE(recovery->AutoRecoverTable("x", 0, &rows));
 711     EXPECT_EQ(43u, rows);
 712
 713     // Successfully recovered.
 714     EXPECT_TRUE(sql::Recovery::Recovered(recovery.Pass()));
 715   }
 716 }
 717 #endif  // !defined(USE_SYSTEM_SQLITE)
 718
 719 }  // namespace