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.
5 #include "sql/recovery.h"
13 #include "base/bind.h"
14 #include "base/files/file_path.h"
15 #include "base/files/file_util.h"
16 #include "base/files/scoped_temp_dir.h"
17 #include "base/path_service.h"
18 #include "base/strings/string_number_conversions.h"
19 #include "base/test/metrics/histogram_tester.h"
20 #include "sql/database.h"
21 #include "sql/meta_table.h"
22 #include "sql/statement.h"
23 #include "sql/test/paths.h"
24 #include "sql/test/scoped_error_expecter.h"
25 #include "sql/test/sql_test_base.h"
26 #include "sql/test/test_helpers.h"
27 #include "testing/gtest/include/gtest/gtest.h"
28 #include "third_party/sqlite/sqlite3.h"
32 using sql::test::ExecuteWithResults;
33 using sql::test::ExecuteWithResult;
35 // Dump consistent human-readable representation of the database
36 // schema. For tables or indices, this will contain the sql command
37 // to create the table or index. For certain automatic SQLite
38 // structures with no sql, the name is used.
39 std::string GetSchema(sql::Database* db) {
40 static const char kSql[] =
41 "SELECT COALESCE(sql, name) FROM sqlite_master ORDER BY 1";
42 return ExecuteWithResults(db, kSql, "|", "\n");
45 using SQLRecoveryTest = sql::SQLTestBase;
47 // Baseline sql::Recovery test covering the different ways to dispose of the
48 // scoped pointer received from sql::Recovery::Begin().
49 TEST_F(SQLRecoveryTest, RecoverBasic) {
50 static const char kCreateSql[] = "CREATE TABLE x (t TEXT)";
51 static const char kInsertSql[] = "INSERT INTO x VALUES ('This is a test')";
52 static const char kAltInsertSql[] =
53 "INSERT INTO x VALUES ('That was a test')";
54 ASSERT_TRUE(db().Execute(kCreateSql));
55 ASSERT_TRUE(db().Execute(kInsertSql));
56 ASSERT_EQ("CREATE TABLE x (t TEXT)", GetSchema(&db()));
58 // If the Recovery handle goes out of scope without being
59 // Recovered(), the database is razed.
61 std::unique_ptr<sql::Recovery> recovery =
62 sql::Recovery::Begin(&db(), db_path());
63 ASSERT_TRUE(recovery.get());
65 EXPECT_FALSE(db().is_open());
66 ASSERT_TRUE(Reopen());
67 EXPECT_TRUE(db().is_open());
68 ASSERT_EQ("", GetSchema(&db()));
70 // Recreate the database.
71 ASSERT_TRUE(db().Execute(kCreateSql));
72 ASSERT_TRUE(db().Execute(kInsertSql));
73 ASSERT_EQ("CREATE TABLE x (t TEXT)", GetSchema(&db()));
75 // Unrecoverable() also razes.
77 std::unique_ptr<sql::Recovery> recovery =
78 sql::Recovery::Begin(&db(), db_path());
79 ASSERT_TRUE(recovery.get());
80 sql::Recovery::Unrecoverable(std::move(recovery));
82 // TODO(shess): Test that calls to recover.db() start failing.
84 EXPECT_FALSE(db().is_open());
85 ASSERT_TRUE(Reopen());
86 EXPECT_TRUE(db().is_open());
87 ASSERT_EQ("", GetSchema(&db()));
89 // Attempting to recover a previously-recovered handle fails early.
91 std::unique_ptr<sql::Recovery> recovery =
92 sql::Recovery::Begin(&db(), db_path());
93 ASSERT_TRUE(recovery.get());
96 recovery = sql::Recovery::Begin(&db(), db_path());
97 ASSERT_FALSE(recovery.get());
99 ASSERT_TRUE(Reopen());
101 // Recreate the database.
102 ASSERT_TRUE(db().Execute(kCreateSql));
103 ASSERT_TRUE(db().Execute(kInsertSql));
104 ASSERT_EQ("CREATE TABLE x (t TEXT)", GetSchema(&db()));
106 // Unrecovered table to distinguish from recovered database.
107 ASSERT_TRUE(db().Execute("CREATE TABLE y (c INTEGER)"));
108 ASSERT_NE("CREATE TABLE x (t TEXT)", GetSchema(&db()));
110 // Recovered() replaces the original with the "recovered" version.
112 std::unique_ptr<sql::Recovery> recovery =
113 sql::Recovery::Begin(&db(), db_path());
114 ASSERT_TRUE(recovery.get());
116 // Create the new version of the table.
117 ASSERT_TRUE(recovery->db()->Execute(kCreateSql));
119 // Insert different data to distinguish from original database.
120 ASSERT_TRUE(recovery->db()->Execute(kAltInsertSql));
122 // Successfully recovered.
123 ASSERT_TRUE(sql::Recovery::Recovered(std::move(recovery)));
125 EXPECT_FALSE(db().is_open());
126 ASSERT_TRUE(Reopen());
127 EXPECT_TRUE(db().is_open());
128 ASSERT_EQ("CREATE TABLE x (t TEXT)", GetSchema(&db()));
130 const char* kXSql = "SELECT * FROM x ORDER BY 1";
131 ASSERT_EQ("That was a test", ExecuteWithResult(&db(), kXSql));
133 // Reset the database contents.
134 ASSERT_TRUE(db().Execute("DELETE FROM x"));
135 ASSERT_TRUE(db().Execute(kInsertSql));
137 // Rollback() discards recovery progress and leaves the database as it was.
139 std::unique_ptr<sql::Recovery> recovery =
140 sql::Recovery::Begin(&db(), db_path());
141 ASSERT_TRUE(recovery.get());
143 ASSERT_TRUE(recovery->db()->Execute(kCreateSql));
144 ASSERT_TRUE(recovery->db()->Execute(kAltInsertSql));
146 sql::Recovery::Rollback(std::move(recovery));
148 EXPECT_FALSE(db().is_open());
149 ASSERT_TRUE(Reopen());
150 EXPECT_TRUE(db().is_open());
151 ASSERT_EQ("CREATE TABLE x (t TEXT)", GetSchema(&db()));
153 ASSERT_EQ("This is a test", ExecuteWithResult(&db(), kXSql));
156 // Test operation of the virtual table used by sql::Recovery.
157 TEST_F(SQLRecoveryTest, VirtualTable) {
158 static const char kCreateSql[] = "CREATE TABLE x (t TEXT)";
159 ASSERT_TRUE(db().Execute(kCreateSql));
160 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES ('This is a test')"));
161 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES ('That was a test')"));
163 // Successfully recover the database.
165 std::unique_ptr<sql::Recovery> recovery =
166 sql::Recovery::Begin(&db(), db_path());
168 // Tables to recover original DB, now at [corrupt].
169 static const char kRecoveryCreateSql[] =
170 "CREATE VIRTUAL TABLE temp.recover_x using recover("
174 ASSERT_TRUE(recovery->db()->Execute(kRecoveryCreateSql));
176 // Re-create the original schema.
177 ASSERT_TRUE(recovery->db()->Execute(kCreateSql));
179 // Copy the data from the recovery tables to the new database.
180 static const char kRecoveryCopySql[] =
181 "INSERT INTO x SELECT t FROM recover_x";
182 ASSERT_TRUE(recovery->db()->Execute(kRecoveryCopySql));
184 // Successfully recovered.
185 ASSERT_TRUE(sql::Recovery::Recovered(std::move(recovery)));
188 // Since the database was not corrupt, the entire schema and all
189 // data should be recovered.
190 ASSERT_TRUE(Reopen());
191 ASSERT_EQ("CREATE TABLE x (t TEXT)", GetSchema(&db()));
193 static const char* kXSql = "SELECT * FROM x ORDER BY 1";
194 ASSERT_EQ("That was a test\nThis is a test",
195 ExecuteWithResults(&db(), kXSql, "|", "\n"));
198 void RecoveryCallback(sql::Database* db,
199 const base::FilePath& db_path,
200 const char* create_table,
201 const char* create_index,
204 sql::Statement* stmt) {
205 *record_error = error;
207 // Clear the error callback to prevent reentrancy.
208 db->reset_error_callback();
210 std::unique_ptr<sql::Recovery> recovery = sql::Recovery::Begin(db, db_path);
211 ASSERT_TRUE(recovery.get());
213 ASSERT_TRUE(recovery->db()->Execute(create_table));
214 ASSERT_TRUE(recovery->db()->Execute(create_index));
217 ASSERT_TRUE(recovery->AutoRecoverTable("x", &rows));
219 ASSERT_TRUE(sql::Recovery::Recovered(std::move(recovery)));
222 // Build a database, corrupt it by making an index reference to
223 // deleted row, then recover when a query selects that row.
224 TEST_F(SQLRecoveryTest, RecoverCorruptIndex) {
225 static const char kCreateTable[] = "CREATE TABLE x (id INTEGER, v INTEGER)";
226 static const char kCreateIndex[] = "CREATE UNIQUE INDEX x_id ON x (id)";
227 ASSERT_TRUE(db().Execute(kCreateTable));
228 ASSERT_TRUE(db().Execute(kCreateIndex));
230 // Insert a bit of data.
232 ASSERT_TRUE(db().BeginTransaction());
234 static const char kInsertSql[] = "INSERT INTO x (id, v) VALUES (?, ?)";
235 sql::Statement s(db().GetUniqueStatement(kInsertSql));
236 for (int i = 0; i < 10; ++i) {
240 EXPECT_FALSE(s.Step());
241 EXPECT_TRUE(s.Succeeded());
244 ASSERT_TRUE(db().CommitTransaction());
248 // Delete a row from the table, while leaving the index entry which
250 static const char kDeleteSql[] = "DELETE FROM x WHERE id = 0";
251 ASSERT_TRUE(sql::test::CorruptTableOrIndex(db_path(), "x_id", kDeleteSql));
253 ASSERT_TRUE(Reopen());
255 int error = SQLITE_OK;
256 db().set_error_callback(base::BindRepeating(
257 &RecoveryCallback, &db(), db_path(), kCreateTable, kCreateIndex, &error));
259 // This works before the callback is called.
260 static const char kTrivialSql[] = "SELECT COUNT(*) FROM sqlite_master";
261 EXPECT_TRUE(db().IsSQLValid(kTrivialSql));
263 // TODO(shess): Could this be delete? Anything which fails should work.
264 static const char kSelectSql[] = "SELECT v FROM x WHERE id = 0";
265 ASSERT_FALSE(db().Execute(kSelectSql));
266 EXPECT_EQ(SQLITE_CORRUPT, error);
268 // Database handle has been poisoned.
269 EXPECT_FALSE(db().IsSQLValid(kTrivialSql));
271 ASSERT_TRUE(Reopen());
273 // The recovered table should reflect the deletion.
274 static const char kSelectAllSql[] = "SELECT v FROM x ORDER BY id";
275 EXPECT_EQ("1,2,3,4,5,6,7,8,9",
276 ExecuteWithResults(&db(), kSelectAllSql, "|", ","));
278 // The failing statement should now succeed, with no results.
279 EXPECT_EQ("", ExecuteWithResults(&db(), kSelectSql, "|", ","));
282 // Build a database, corrupt it by making a table contain a row not
283 // referenced by the index, then recover the database.
284 TEST_F(SQLRecoveryTest, RecoverCorruptTable) {
285 static const char kCreateTable[] = "CREATE TABLE x (id INTEGER, v INTEGER)";
286 static const char kCreateIndex[] = "CREATE UNIQUE INDEX x_id ON x (id)";
287 ASSERT_TRUE(db().Execute(kCreateTable));
288 ASSERT_TRUE(db().Execute(kCreateIndex));
290 // Insert a bit of data.
292 ASSERT_TRUE(db().BeginTransaction());
294 static const char kInsertSql[] = "INSERT INTO x (id, v) VALUES (?, ?)";
295 sql::Statement s(db().GetUniqueStatement(kInsertSql));
296 for (int i = 0; i < 10; ++i) {
300 EXPECT_FALSE(s.Step());
301 EXPECT_TRUE(s.Succeeded());
304 ASSERT_TRUE(db().CommitTransaction());
308 // Delete a row from the index while leaving a table entry.
309 static const char kDeleteSql[] = "DELETE FROM x WHERE id = 0";
310 ASSERT_TRUE(sql::test::CorruptTableOrIndex(db_path(), "x", kDeleteSql));
312 ASSERT_TRUE(Reopen());
314 int error = SQLITE_OK;
315 db().set_error_callback(base::BindRepeating(
316 &RecoveryCallback, &db(), db_path(), kCreateTable, kCreateIndex, &error));
318 // Index shows one less than originally inserted.
319 static const char kCountSql[] = "SELECT COUNT (*) FROM x";
320 EXPECT_EQ("9", ExecuteWithResult(&db(), kCountSql));
322 // A full table scan shows all of the original data. Using column [v] to
323 // force use of the table rather than the index.
324 static const char kDistinctSql[] = "SELECT DISTINCT COUNT (v) FROM x";
325 EXPECT_EQ("10", ExecuteWithResult(&db(), kDistinctSql));
327 // Insert id 0 again. Since it is not in the index, the insert
328 // succeeds, but results in a duplicate value in the table.
329 static const char kInsertSql[] = "INSERT INTO x (id, v) VALUES (0, 100)";
330 ASSERT_TRUE(db().Execute(kInsertSql));
332 // Duplication is visible.
333 EXPECT_EQ("10", ExecuteWithResult(&db(), kCountSql));
334 EXPECT_EQ("11", ExecuteWithResult(&db(), kDistinctSql));
336 // This works before the callback is called.
337 static const char kTrivialSql[] = "SELECT COUNT(*) FROM sqlite_master";
338 EXPECT_TRUE(db().IsSQLValid(kTrivialSql));
340 // TODO(shess): Figure out a statement which causes SQLite to notice the
341 // corruption. SELECT doesn't see errors because missing index values aren't
342 // visible. UPDATE or DELETE against v=0 don't see errors, even though the
343 // index item is missing. I suspect SQLite only deletes the key in these
344 // cases, but doesn't verify that one or more keys were deleted.
345 ASSERT_FALSE(db().Execute("INSERT INTO x (id, v) VALUES (0, 101)"));
346 EXPECT_EQ(SQLITE_CONSTRAINT_UNIQUE, error);
348 // Database handle has been poisoned.
349 EXPECT_FALSE(db().IsSQLValid(kTrivialSql));
351 ASSERT_TRUE(Reopen());
353 // The recovered table has consistency between the index and the table.
354 EXPECT_EQ("10", ExecuteWithResult(&db(), kCountSql));
355 EXPECT_EQ("10", ExecuteWithResult(&db(), kDistinctSql));
357 // Only one of the values is retained.
358 static const char kSelectSql[] = "SELECT v FROM x WHERE id = 0";
359 const std::string results = ExecuteWithResult(&db(), kSelectSql);
360 EXPECT_TRUE(results=="100" || results=="0") << "Actual results: " << results;
363 TEST_F(SQLRecoveryTest, Meta) {
364 const int kVersion = 3;
365 const int kCompatibleVersion = 2;
369 EXPECT_TRUE(meta.Init(&db(), kVersion, kCompatibleVersion));
370 EXPECT_EQ(kVersion, meta.GetVersionNumber());
373 // Test expected case where everything works.
375 std::unique_ptr<sql::Recovery> recovery =
376 sql::Recovery::Begin(&db(), db_path());
377 EXPECT_TRUE(recovery->SetupMeta());
379 EXPECT_TRUE(recovery->GetMetaVersionNumber(&version));
380 EXPECT_EQ(kVersion, version);
382 sql::Recovery::Rollback(std::move(recovery));
384 ASSERT_TRUE(Reopen()); // Handle was poisoned.
386 // Test version row missing.
387 EXPECT_TRUE(db().Execute("DELETE FROM meta WHERE key = 'version'"));
389 std::unique_ptr<sql::Recovery> recovery =
390 sql::Recovery::Begin(&db(), db_path());
391 EXPECT_TRUE(recovery->SetupMeta());
393 EXPECT_FALSE(recovery->GetMetaVersionNumber(&version));
394 EXPECT_EQ(0, version);
396 sql::Recovery::Rollback(std::move(recovery));
398 ASSERT_TRUE(Reopen()); // Handle was poisoned.
400 // Test meta table missing.
401 EXPECT_TRUE(db().Execute("DROP TABLE meta"));
403 sql::test::ScopedErrorExpecter expecter;
404 expecter.ExpectError(SQLITE_CORRUPT); // From virtual table.
405 std::unique_ptr<sql::Recovery> recovery =
406 sql::Recovery::Begin(&db(), db_path());
407 EXPECT_FALSE(recovery->SetupMeta());
408 ASSERT_TRUE(expecter.SawExpectedErrors());
412 // Baseline AutoRecoverTable() test.
413 TEST_F(SQLRecoveryTest, AutoRecoverTable) {
414 // BIGINT and VARCHAR to test type affinity.
415 static const char kCreateSql[] =
416 "CREATE TABLE x (id BIGINT, t TEXT, v VARCHAR)";
417 ASSERT_TRUE(db().Execute(kCreateSql));
418 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (11, 'This is', 'a test')"));
419 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (5, 'That was', 'a test')"));
421 // Save aside a copy of the original schema and data.
422 const std::string orig_schema(GetSchema(&db()));
423 static const char kXSql[] = "SELECT * FROM x ORDER BY 1";
424 const std::string orig_data(ExecuteWithResults(&db(), kXSql, "|", "\n"));
426 // Create a lame-duck table which will not be propagated by recovery to
427 // detect that the recovery code actually ran.
428 ASSERT_TRUE(db().Execute("CREATE TABLE y (c TEXT)"));
429 ASSERT_NE(orig_schema, GetSchema(&db()));
432 std::unique_ptr<sql::Recovery> recovery =
433 sql::Recovery::Begin(&db(), db_path());
434 ASSERT_TRUE(recovery->db()->Execute(kCreateSql));
436 // Save a copy of the temp db's schema before recovering the table.
437 static const char kTempSchemaSql[] =
438 "SELECT name, sql FROM sqlite_temp_master";
439 const std::string temp_schema(
440 ExecuteWithResults(recovery->db(), kTempSchemaSql, "|", "\n"));
443 EXPECT_TRUE(recovery->AutoRecoverTable("x", &rows));
446 // Test that any additional temp tables were cleaned up.
447 EXPECT_EQ(temp_schema,
448 ExecuteWithResults(recovery->db(), kTempSchemaSql, "|", "\n"));
450 ASSERT_TRUE(sql::Recovery::Recovered(std::move(recovery)));
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"));
459 // Recovery fails if the target table doesn't exist.
461 std::unique_ptr<sql::Recovery> recovery =
462 sql::Recovery::Begin(&db(), db_path());
463 ASSERT_TRUE(recovery->db()->Execute(kCreateSql));
465 // TODO(shess): Should this failure implicitly lead to Raze()?
467 EXPECT_FALSE(recovery->AutoRecoverTable("y", &rows));
469 sql::Recovery::Unrecoverable(std::move(recovery));
473 // Test that default values correctly replace nulls. The recovery
474 // virtual table reads directly from the database, so DEFAULT is not
475 // interpretted at that level.
476 TEST_F(SQLRecoveryTest, AutoRecoverTableWithDefault) {
477 ASSERT_TRUE(db().Execute("CREATE TABLE x (id INTEGER)"));
478 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (5)"));
479 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (15)"));
481 // ALTER effectively leaves the new columns NULL in the first two
482 // rows. The row with 17 will get the default injected at insert
483 // time, while the row with 42 will get the actual value provided.
484 // Embedded "'" to make sure default-handling continues to be quoted
486 ASSERT_TRUE(db().Execute("ALTER TABLE x ADD COLUMN t TEXT DEFAULT 'a''a'"));
487 ASSERT_TRUE(db().Execute("ALTER TABLE x ADD COLUMN b BLOB DEFAULT x'AA55'"));
488 ASSERT_TRUE(db().Execute("ALTER TABLE x ADD COLUMN i INT DEFAULT 93"));
489 ASSERT_TRUE(db().Execute("INSERT INTO x (id) VALUES (17)"));
490 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (42, 'b', x'1234', 12)"));
492 // Save aside a copy of the original schema and data.
493 const std::string orig_schema(GetSchema(&db()));
494 static const char kXSql[] = "SELECT * FROM x ORDER BY 1";
495 const std::string orig_data(ExecuteWithResults(&db(), kXSql, "|", "\n"));
497 // Create a lame-duck table which will not be propagated by recovery to
498 // detect that the recovery code actually ran.
499 ASSERT_TRUE(db().Execute("CREATE TABLE y (c TEXT)"));
500 ASSERT_NE(orig_schema, GetSchema(&db()));
502 // Mechanically adjust the stored schema and data to allow detecting
503 // where the default value is coming from. The target table is just
504 // like the original with the default for [t] changed, to signal
505 // defaults coming from the recovery system. The two %5 rows should
506 // get the target-table default for [t], while the others should get
507 // the source-table default.
508 std::string final_schema(orig_schema);
509 std::string final_data(orig_data);
511 while ((pos = final_schema.find("'a''a'")) != std::string::npos) {
512 final_schema.replace(pos, 6, "'c''c'");
514 while ((pos = final_data.find("5|a'a")) != std::string::npos) {
515 final_data.replace(pos, 5, "5|c'c");
519 std::unique_ptr<sql::Recovery> recovery =
520 sql::Recovery::Begin(&db(), db_path());
521 // Different default to detect which table provides the default.
522 ASSERT_TRUE(recovery->db()->Execute(final_schema.c_str()));
525 EXPECT_TRUE(recovery->AutoRecoverTable("x", &rows));
528 ASSERT_TRUE(sql::Recovery::Recovered(std::move(recovery)));
531 // Since the database was not corrupt, the entire schema and all
532 // data should be recovered.
533 ASSERT_TRUE(Reopen());
534 ASSERT_EQ(final_schema, GetSchema(&db()));
535 ASSERT_EQ(final_data, ExecuteWithResults(&db(), kXSql, "|", "\n"));
538 // Test that rows with NULL in a NOT NULL column are filtered
539 // correctly. In the wild, this would probably happen due to
540 // corruption, but here it is simulated by recovering a table which
541 // allowed nulls into a table which does not.
542 TEST_F(SQLRecoveryTest, AutoRecoverTableNullFilter) {
543 static const char kOrigSchema[] = "CREATE TABLE x (id INTEGER, t TEXT)";
544 static const char kFinalSchema[] =
545 "CREATE TABLE x (id INTEGER, t TEXT NOT NULL)";
547 ASSERT_TRUE(db().Execute(kOrigSchema));
548 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (5, NULL)"));
549 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (15, 'this is a test')"));
551 // Create a lame-duck table which will not be propagated by recovery to
552 // detect that the recovery code actually ran.
553 ASSERT_EQ(kOrigSchema, GetSchema(&db()));
554 ASSERT_TRUE(db().Execute("CREATE TABLE y (c TEXT)"));
555 ASSERT_NE(kOrigSchema, GetSchema(&db()));
558 std::unique_ptr<sql::Recovery> recovery =
559 sql::Recovery::Begin(&db(), db_path());
560 ASSERT_TRUE(recovery->db()->Execute(kFinalSchema));
563 EXPECT_TRUE(recovery->AutoRecoverTable("x", &rows));
566 ASSERT_TRUE(sql::Recovery::Recovered(std::move(recovery)));
569 // The schema should be the same, but only one row of data should
570 // have been recovered.
571 ASSERT_TRUE(Reopen());
572 ASSERT_EQ(kFinalSchema, GetSchema(&db()));
573 static const char kXSql[] = "SELECT * FROM x ORDER BY 1";
574 ASSERT_EQ("15|this is a test", ExecuteWithResults(&db(), kXSql, "|", "\n"));
577 // Test AutoRecoverTable with a ROWID alias.
578 TEST_F(SQLRecoveryTest, AutoRecoverTableWithRowid) {
579 // The rowid alias is almost always the first column, intentionally
581 static const char kCreateSql[] =
582 "CREATE TABLE x (t TEXT, id INTEGER PRIMARY KEY NOT NULL)";
583 ASSERT_TRUE(db().Execute(kCreateSql));
584 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES ('This is a test', NULL)"));
585 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES ('That was a test', NULL)"));
587 // Save aside a copy of the original schema and data.
588 const std::string orig_schema(GetSchema(&db()));
589 static const char kXSql[] = "SELECT * FROM x ORDER BY 1";
590 const std::string orig_data(ExecuteWithResults(&db(), kXSql, "|", "\n"));
592 // Create a lame-duck table which will not be propagated by recovery to
593 // detect that the recovery code actually ran.
594 ASSERT_TRUE(db().Execute("CREATE TABLE y (c TEXT)"));
595 ASSERT_NE(orig_schema, GetSchema(&db()));
598 std::unique_ptr<sql::Recovery> recovery =
599 sql::Recovery::Begin(&db(), db_path());
600 ASSERT_TRUE(recovery->db()->Execute(kCreateSql));
603 EXPECT_TRUE(recovery->AutoRecoverTable("x", &rows));
606 ASSERT_TRUE(sql::Recovery::Recovered(std::move(recovery)));
609 // Since the database was not corrupt, the entire schema and all
610 // data should be recovered.
611 ASSERT_TRUE(Reopen());
612 ASSERT_EQ(orig_schema, GetSchema(&db()));
613 ASSERT_EQ(orig_data, ExecuteWithResults(&db(), kXSql, "|", "\n"));
616 // Test that a compound primary key doesn't fire the ROWID code.
617 TEST_F(SQLRecoveryTest, AutoRecoverTableWithCompoundKey) {
618 static const char kCreateSql[] =
620 "id INTEGER NOT NULL,"
623 "PRIMARY KEY (id, id2)"
625 ASSERT_TRUE(db().Execute(kCreateSql));
627 // NOTE(shess): Do not accidentally use [id] 1, 2, 3, as those will
628 // be the ROWID values.
629 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (1, 'a', 'This is a test')"));
630 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (1, 'b', 'That was a test')"));
631 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (2, 'a', 'Another test')"));
633 // Save aside a copy of the original schema and data.
634 const std::string orig_schema(GetSchema(&db()));
635 static const char kXSql[] = "SELECT * FROM x ORDER BY 1";
636 const std::string orig_data(ExecuteWithResults(&db(), kXSql, "|", "\n"));
638 // Create a lame-duck table which will not be propagated by recovery to
639 // detect that the recovery code actually ran.
640 ASSERT_TRUE(db().Execute("CREATE TABLE y (c TEXT)"));
641 ASSERT_NE(orig_schema, GetSchema(&db()));
644 std::unique_ptr<sql::Recovery> recovery =
645 sql::Recovery::Begin(&db(), db_path());
646 ASSERT_TRUE(recovery->db()->Execute(kCreateSql));
649 EXPECT_TRUE(recovery->AutoRecoverTable("x", &rows));
652 ASSERT_TRUE(sql::Recovery::Recovered(std::move(recovery)));
655 // Since the database was not corrupt, the entire schema and all
656 // data should be recovered.
657 ASSERT_TRUE(Reopen());
658 ASSERT_EQ(orig_schema, GetSchema(&db()));
659 ASSERT_EQ(orig_data, ExecuteWithResults(&db(), kXSql, "|", "\n"));
662 // Test recovering from a table with fewer columns than the target.
663 TEST_F(SQLRecoveryTest, AutoRecoverTableMissingColumns) {
664 static const char kCreateSql[] =
665 "CREATE TABLE x (id INTEGER PRIMARY KEY, t0 TEXT)";
666 static const char kAlterSql[] =
667 "ALTER TABLE x ADD COLUMN t1 TEXT DEFAULT 't'";
668 ASSERT_TRUE(db().Execute(kCreateSql));
669 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (1, 'This is')"));
670 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES (2, 'That was')"));
672 // Generate the expected info by faking a table to match what recovery will
674 const std::string orig_schema(GetSchema(&db()));
675 static const char kXSql[] = "SELECT * FROM x ORDER BY 1";
676 std::string expected_schema;
677 std::string expected_data;
679 ASSERT_TRUE(db().BeginTransaction());
680 ASSERT_TRUE(db().Execute(kAlterSql));
682 expected_schema = GetSchema(&db());
683 expected_data = ExecuteWithResults(&db(), kXSql, "|", "\n");
685 db().RollbackTransaction();
688 // Following tests are pointless if the rollback didn't work.
689 ASSERT_EQ(orig_schema, GetSchema(&db()));
691 // Recover the previous version of the table into the altered version.
693 std::unique_ptr<sql::Recovery> recovery =
694 sql::Recovery::Begin(&db(), db_path());
695 ASSERT_TRUE(recovery->db()->Execute(kCreateSql));
696 ASSERT_TRUE(recovery->db()->Execute(kAlterSql));
698 EXPECT_TRUE(recovery->AutoRecoverTable("x", &rows));
700 ASSERT_TRUE(sql::Recovery::Recovered(std::move(recovery)));
703 // Since the database was not corrupt, the entire schema and all
704 // data should be recovered.
705 ASSERT_TRUE(Reopen());
706 ASSERT_EQ(expected_schema, GetSchema(&db()));
707 ASSERT_EQ(expected_data, ExecuteWithResults(&db(), kXSql, "|", "\n"));
710 // Recover a golden file where an interior page has been manually modified so
711 // that the number of cells is greater than will fit on a single page. This
712 // case happened in <http://crbug.com/387868>.
713 TEST_F(SQLRecoveryTest, Bug387868) {
714 base::FilePath golden_path;
715 ASSERT_TRUE(base::PathService::Get(sql::test::DIR_TEST_DATA, &golden_path));
716 golden_path = golden_path.AppendASCII("recovery_387868");
718 ASSERT_TRUE(base::CopyFile(golden_path, db_path()));
719 ASSERT_TRUE(Reopen());
722 std::unique_ptr<sql::Recovery> recovery =
723 sql::Recovery::Begin(&db(), db_path());
724 ASSERT_TRUE(recovery.get());
726 // Create the new version of the table.
727 static const char kCreateSql[] =
728 "CREATE TABLE x (id INTEGER PRIMARY KEY, t0 TEXT)";
729 ASSERT_TRUE(recovery->db()->Execute(kCreateSql));
732 EXPECT_TRUE(recovery->AutoRecoverTable("x", &rows));
733 EXPECT_EQ(43u, rows);
735 // Successfully recovered.
736 EXPECT_TRUE(sql::Recovery::Recovered(std::move(recovery)));
740 // Memory-mapped I/O interacts poorly with I/O errors. Make sure the recovery
741 // database doesn't accidentally enable it.
742 TEST_F(SQLRecoveryTest, NoMmap) {
743 std::unique_ptr<sql::Recovery> recovery =
744 sql::Recovery::Begin(&db(), db_path());
745 ASSERT_TRUE(recovery.get());
747 // In the current implementation, the PRAGMA successfully runs with no result
748 // rows. Running with a single result of |0| is also acceptable.
749 sql::Statement s(recovery->db()->GetUniqueStatement("PRAGMA mmap_size"));
750 EXPECT_TRUE(!s.Step() || !s.ColumnInt64(0));
753 TEST_F(SQLRecoveryTest, RecoverDatabase) {
754 // As a side effect, AUTOINCREMENT creates the sqlite_sequence table for
755 // RecoverDatabase() to handle.
756 ASSERT_TRUE(db().Execute(
757 "CREATE TABLE x (id INTEGER PRIMARY KEY AUTOINCREMENT, v TEXT)"));
758 EXPECT_TRUE(db().Execute("INSERT INTO x (v) VALUES ('turtle')"));
759 EXPECT_TRUE(db().Execute("INSERT INTO x (v) VALUES ('truck')"));
760 EXPECT_TRUE(db().Execute("INSERT INTO x (v) VALUES ('trailer')"));
762 // This table needs index and a unique index to work.
763 ASSERT_TRUE(db().Execute("CREATE TABLE y (name TEXT, v TEXT)"));
764 ASSERT_TRUE(db().Execute("CREATE UNIQUE INDEX y_name ON y(name)"));
765 ASSERT_TRUE(db().Execute("CREATE INDEX y_v ON y(v)"));
766 EXPECT_TRUE(db().Execute("INSERT INTO y VALUES ('jim', 'telephone')"));
767 EXPECT_TRUE(db().Execute("INSERT INTO y VALUES ('bob', 'truck')"));
768 EXPECT_TRUE(db().Execute("INSERT INTO y VALUES ('dean', 'trailer')"));
770 // View which is the intersection of [x.v] and [y.v].
771 ASSERT_TRUE(db().Execute(
772 "CREATE VIEW v AS SELECT x.v FROM x, y WHERE x.v = y.v"));
774 // When an element is deleted from [x], trigger a delete on [y]. Between the
775 // BEGIN and END, [old] stands for the deleted rows from [x].
776 ASSERT_TRUE(db().Execute("CREATE TRIGGER t AFTER DELETE ON x "
777 "BEGIN DELETE FROM y WHERE y.v = old.v; END"));
779 // Save aside a copy of the original schema, verifying that it has the created
780 // items plus the sqlite_sequence table.
781 const std::string orig_schema(GetSchema(&db()));
782 ASSERT_EQ(6, std::count(orig_schema.begin(), orig_schema.end(), '\n'));
784 static const char kXSql[] = "SELECT * FROM x ORDER BY 1";
785 static const char kYSql[] = "SELECT * FROM y ORDER BY 1";
786 static const char kVSql[] = "SELECT * FROM v ORDER BY 1";
787 EXPECT_EQ("1|turtle\n2|truck\n3|trailer",
788 ExecuteWithResults(&db(), kXSql, "|", "\n"));
789 EXPECT_EQ("bob|truck\ndean|trailer\njim|telephone",
790 ExecuteWithResults(&db(), kYSql, "|", "\n"));
791 EXPECT_EQ("trailer\ntruck", ExecuteWithResults(&db(), kVSql, "|", "\n"));
793 // Database handle is valid before recovery, poisoned after.
794 static const char kTrivialSql[] = "SELECT COUNT(*) FROM sqlite_master";
795 EXPECT_TRUE(db().IsSQLValid(kTrivialSql));
796 sql::Recovery::RecoverDatabase(&db(), db_path());
797 EXPECT_FALSE(db().IsSQLValid(kTrivialSql));
799 // Since the database was not corrupt, the entire schema and all
800 // data should be recovered.
801 ASSERT_TRUE(Reopen());
802 ASSERT_EQ(orig_schema, GetSchema(&db()));
803 EXPECT_EQ("1|turtle\n2|truck\n3|trailer",
804 ExecuteWithResults(&db(), kXSql, "|", "\n"));
805 EXPECT_EQ("bob|truck\ndean|trailer\njim|telephone",
806 ExecuteWithResults(&db(), kYSql, "|", "\n"));
807 EXPECT_EQ("trailer\ntruck", ExecuteWithResults(&db(), kVSql, "|", "\n"));
809 // Test that the trigger works.
810 ASSERT_TRUE(db().Execute("DELETE FROM x WHERE v = 'truck'"));
811 EXPECT_EQ("1|turtle\n3|trailer",
812 ExecuteWithResults(&db(), kXSql, "|", "\n"));
813 EXPECT_EQ("dean|trailer\njim|telephone",
814 ExecuteWithResults(&db(), kYSql, "|", "\n"));
815 EXPECT_EQ("trailer", ExecuteWithResults(&db(), kVSql, "|", "\n"));
818 // When RecoverDatabase() encounters SQLITE_NOTADB, the database is deleted.
819 TEST_F(SQLRecoveryTest, RecoverDatabaseDelete) {
820 // Create a valid database, then write junk over the header. This should lead
821 // to SQLITE_NOTADB, which will cause ATTACH to fail.
822 ASSERT_TRUE(db().Execute("CREATE TABLE x (t TEXT)"));
823 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES ('This is a test')"));
825 WriteJunkToDatabase(SQLTestBase::TYPE_OVERWRITE);
828 sql::test::ScopedErrorExpecter expecter;
829 expecter.ExpectError(SQLITE_NOTADB);
831 // Reopen() here because it will see SQLITE_NOTADB.
832 ASSERT_TRUE(Reopen());
834 // This should "recover" the database by making it valid, but empty.
835 sql::Recovery::RecoverDatabase(&db(), db_path());
837 ASSERT_TRUE(expecter.SawExpectedErrors());
840 // Recovery poisoned the handle, must re-open.
842 ASSERT_TRUE(Reopen());
844 EXPECT_EQ("", GetSchema(&db()));
847 // Allow callers to validate the database between recovery and commit.
848 TEST_F(SQLRecoveryTest, BeginRecoverDatabase) {
849 // Create a table with a broken index.
850 ASSERT_TRUE(db().Execute("CREATE TABLE t (id INTEGER PRIMARY KEY, c TEXT)"));
851 ASSERT_TRUE(db().Execute("CREATE UNIQUE INDEX t_id ON t (id)"));
852 ASSERT_TRUE(db().Execute("INSERT INTO t VALUES (1, 'hello world')"));
853 ASSERT_TRUE(db().Execute("INSERT INTO t VALUES (2, 'testing')"));
854 ASSERT_TRUE(db().Execute("INSERT INTO t VALUES (3, 'nope')"));
856 // Inject corruption into the index.
858 static const char kDeleteSql[] = "DELETE FROM t WHERE id = 3";
859 ASSERT_TRUE(sql::test::CorruptTableOrIndex(db_path(), "t_id", kDeleteSql));
860 ASSERT_TRUE(Reopen());
862 // id as read from index.
863 static const char kSelectIndexIdSql[] = "SELECT id FROM t INDEXED BY t_id";
864 EXPECT_EQ("1,2,3", ExecuteWithResults(&db(), kSelectIndexIdSql, "|", ","));
866 // id as read from table.
867 static const char kSelectTableIdSql[] = "SELECT id FROM t NOT INDEXED";
868 EXPECT_EQ("1,2", ExecuteWithResults(&db(), kSelectTableIdSql, "|", ","));
870 // Run recovery code, then rollback. Database remains the same.
872 std::unique_ptr<sql::Recovery> recovery =
873 sql::Recovery::BeginRecoverDatabase(&db(), db_path());
874 ASSERT_TRUE(recovery);
875 sql::Recovery::Rollback(std::move(recovery));
878 ASSERT_TRUE(Reopen());
879 EXPECT_EQ("1,2,3", ExecuteWithResults(&db(), kSelectIndexIdSql, "|", ","));
880 EXPECT_EQ("1,2", ExecuteWithResults(&db(), kSelectTableIdSql, "|", ","));
882 // Run recovery code, then commit. The failing row is dropped.
884 std::unique_ptr<sql::Recovery> recovery =
885 sql::Recovery::BeginRecoverDatabase(&db(), db_path());
886 ASSERT_TRUE(recovery);
887 ASSERT_TRUE(sql::Recovery::Recovered(std::move(recovery)));
890 ASSERT_TRUE(Reopen());
891 EXPECT_EQ("1,2", ExecuteWithResults(&db(), kSelectIndexIdSql, "|", ","));
892 EXPECT_EQ("1,2", ExecuteWithResults(&db(), kSelectTableIdSql, "|", ","));
895 // Test histograms recorded when the invalid database cannot be attached.
896 TEST_F(SQLRecoveryTest, AttachFailure) {
897 // Create a valid database, then write junk over the header. This should lead
898 // to SQLITE_NOTADB, which will cause ATTACH to fail.
899 ASSERT_TRUE(db().Execute("CREATE TABLE x (t TEXT)"));
900 ASSERT_TRUE(db().Execute("INSERT INTO x VALUES ('This is a test')"));
902 WriteJunkToDatabase(SQLTestBase::TYPE_OVERWRITE);
904 static const char kEventHistogramName[] = "Sqlite.RecoveryEvents";
905 const int kEventEnum = 5; // RECOVERY_FAILED_ATTACH
906 static const char kErrorHistogramName[] = "Sqlite.RecoveryAttachError";
907 base::HistogramTester tester;
910 sql::test::ScopedErrorExpecter expecter;
911 expecter.ExpectError(SQLITE_NOTADB);
913 // Reopen() here because it will see SQLITE_NOTADB.
914 ASSERT_TRUE(Reopen());
916 // Begin() should fail.
917 std::unique_ptr<sql::Recovery>
918 recovery = sql::Recovery::Begin(&db(), db_path());
919 ASSERT_FALSE(recovery.get());
921 ASSERT_TRUE(expecter.SawExpectedErrors());
924 // Verify that the failure was in the right place with the expected code.
925 tester.ExpectBucketCount(kEventHistogramName, kEventEnum, 1);
926 tester.ExpectBucketCount(kErrorHistogramName, SQLITE_NOTADB, 1);
929 // Helper for SQLRecoveryTest.PageSize. Creates a fresh db based on db_prefix,
930 // with the given initial page size, and verifies it against the expected size.
931 // Then changes to the final page size and recovers, verifying that the
932 // recovered database ends up with the expected final page size.
933 void TestPageSize(const base::FilePath& db_prefix,
934 int initial_page_size,
935 const std::string& expected_initial_page_size,
937 const std::string& expected_final_page_size) {
938 static const char kCreateSql[] = "CREATE TABLE x (t TEXT)";
939 static const char kInsertSql1[] = "INSERT INTO x VALUES ('This is a test')";
940 static const char kInsertSql2[] = "INSERT INTO x VALUES ('That was a test')";
941 static const char kSelectSql[] = "SELECT * FROM x ORDER BY t";
943 const base::FilePath db_path = db_prefix.InsertBeforeExtensionASCII(
944 base::IntToString(initial_page_size));
945 sql::Database::Delete(db_path);
947 db.set_page_size(initial_page_size);
948 ASSERT_TRUE(db.Open(db_path));
949 ASSERT_TRUE(db.Execute(kCreateSql));
950 ASSERT_TRUE(db.Execute(kInsertSql1));
951 ASSERT_TRUE(db.Execute(kInsertSql2));
952 ASSERT_EQ(expected_initial_page_size,
953 ExecuteWithResult(&db, "PRAGMA page_size"));
955 // Recovery will use the page size set in the connection object, which may not
956 // match the file's page size.
957 db.set_page_size(final_page_size);
958 sql::Recovery::RecoverDatabase(&db, db_path);
960 // Recovery poisoned the handle, must re-open.
963 // Make sure the page size is read from the file.
964 db.set_page_size(sql::Database::kDefaultPageSize);
965 ASSERT_TRUE(db.Open(db_path));
966 ASSERT_EQ(expected_final_page_size,
967 ExecuteWithResult(&db, "PRAGMA page_size"));
968 EXPECT_EQ("That was a test\nThis is a test",
969 ExecuteWithResults(&db, kSelectSql, "|", "\n"));
972 // Verify that sql::Recovery maintains the page size, and the virtual table
973 // works with page sizes other than SQLite's default. Also verify the case
974 // where the default page size has changed.
975 TEST_F(SQLRecoveryTest, PageSize) {
976 const std::string default_page_size =
977 ExecuteWithResult(&db(), "PRAGMA page_size");
979 // Check the default page size first.
980 EXPECT_NO_FATAL_FAILURE(TestPageSize(
981 db_path(), sql::Database::kDefaultPageSize, default_page_size,
982 sql::Database::kDefaultPageSize, default_page_size));
984 // Sync uses 32k pages.
985 EXPECT_NO_FATAL_FAILURE(
986 TestPageSize(db_path(), 32768, "32768", 32768, "32768"));
988 // Many clients use 4k pages. This is the SQLite default after 3.12.0.
989 EXPECT_NO_FATAL_FAILURE(TestPageSize(db_path(), 4096, "4096", 4096, "4096"));
991 // 1k is the default page size before 3.12.0.
992 EXPECT_NO_FATAL_FAILURE(TestPageSize(db_path(), 1024, "1024", 1024, "1024"));
994 // Databases with no page size specified should recover with the new default
995 // page size. 2k has never been the default page size.
996 ASSERT_NE("2048", default_page_size);
997 EXPECT_NO_FATAL_FAILURE(TestPageSize(db_path(), 2048, "2048",
998 sql::Database::kDefaultPageSize,