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