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