[sql] Recover Favicons v5 databases, with more recovery automation.

sql/recovery.cc

Index: sql/recovery.cc
diff --git a/sql/recovery.cc b/sql/recovery.cc
index c750fd01c3f36730527e05fa4246e2c15bc3a18b..89d4c347e61c70ce3a9273e00944e23c5aa0ef28 100644
--- a/sql/recovery.cc
+++ b/sql/recovery.cc
@@ -5,9 +5,13 @@
 #include "sql/recovery.h"
 
 #include "base/files/file_path.h"
+#include "base/format_macros.h"
 #include "base/logging.h"
 #include "base/metrics/sparse_histogram.h"
+#include "base/strings/string_util.h"
+#include "base/strings/stringprintf.h"
 #include "sql/connection.h"
+#include "sql/statement.h"
 #include "third_party/sqlite/sqlite3.h"
 
 namespace sql {
@@ -233,4 +237,170 @@ void Recovery::Shutdown(Recovery::Disposition raze) {
   db_ = NULL;
 }
 
+bool Recovery::AutoRecoverTable(const char* table_name, size_t extend_columns) {
+  // Query the info for the recovered table in database [main].
+  std::string query(
+      base::StringPrintf("PRAGMA main.table_info(%s)", table_name));
+  Statement s(db()->GetUniqueStatement(query.c_str()));
+
+  // The columns of the recover virtual table.
+  std::vector<std::string> create_column_decls;
+
+  // The columns to select from the recover virtual table when copying
+  // to the recovered table.
+  std::vector<std::string> insert_columns;
+
+  // If PRIMARY KEY is a single INTEGER column, then it is an alias
+  // for ROWID.  The primary key can be compound, so this can only be
+  // determined after processing all column data and tracking what is
+  // seen.  |pk_column_count| counts the columns in the primary key.
+  // |rowid_decl| stores the ROWID version of the last INTEGER column
+  // seen, which is at |rowid_ofs| in |create_column_decls|.
+  size_t pk_column_count = 0;
+  size_t rowid_ofs;  // Only valid if rowid_decl is set.
+  std::string rowid_decl;  // ROWID version of column |rowid_ofs|.
+
+  while (s.Step()) {
+    const std::string column_name(s.ColumnString(1));
+    const std::string column_type(s.ColumnString(2));
+    const bool not_null = s.ColumnBool(3);
+    const int default_type = s.ColumnType(4);
+    const bool default_is_null = (default_type == COLUMN_TYPE_NULL);
+    const int pk_column = s.ColumnInt(5);
+
+    if (pk_column > 0) {
+      // TODO(shess): http://www.sqlite.org/pragma.html#pragma_table_info
+      // documents column 5 as the index of the column in the primary key
+      // (zero for not in primary key).  I find that it is always 1 for
+      // columns in the primary key.  Since this code is very dependent on
+      // that pragma, review if the implementation changes.
+      DCHECK_EQ(pk_column, 1);
+      ++pk_column_count;
+    }
+
+    // Construct column declaration as "name type [optional constraint]".
+    std::string column_decl = column_name;
+
+    // SQLite's affinity detection is documented at:
+    // http://www.sqlite.org/datatype3.html#affname
+    // The gist of it is that CHAR, TEXT, and INT use substring matches.
+    if (column_type.find("INT") != std::string::npos) {
+      if (pk_column == 1) {
+        rowid_ofs = create_column_decls.size();
+        rowid_decl = column_name + " ROWID";
+      }
+      column_decl += " INTEGER";
+    } else if (column_type.find("CHAR") != std::string::npos ||
+               column_type.find("TEXT") != std::string::npos) {
+      column_decl += " TEXT";
+    } else if (column_type == "BLOB") {
+      column_decl += " BLOB";
+    } else {
+      // TODO(shess): AFAICT, there remain:
+      // - contains("CLOB") -> TEXT
+      // - contains("REAL") -> REAL
+      // - contains("FLOA") -> REAL
+      // - contains("DOUB") -> REAL
+      // - other -> "NUMERIC"
+      // Just code those in as they come up.
+      NOTREACHED() << " Unsupported type " << column_type;
+      return false;
+    }
+
+    // If column has constraint "NOT NULL", then inserting NULL into
+    // that column will fail.  If the column has a non-NULL DEFAULT
+    // specified, the INSERT will handle it (see below).  If the
+    // DEFAULT is also NULL, the row must be filtered out.
+    // TODO(shess): The above scenario applies to INSERT OR REPLACE,
+    // whereas INSERT OR IGNORE drops such rows.
+    // http://www.sqlite.org/lang_conflict.html
+    if (not_null && default_is_null)
+      column_decl += " NOT NULL";
+
+    create_column_decls.push_back(column_decl);
+
+    // Per the NOTE in the header file, convert NULL values to the
+    // DEFAULT.  All columns could be IFNULL(column_name,default), but
+    // the NULL case would require special handling either way.
+    if (default_is_null) {
+      insert_columns.push_back(column_name);
+    } else {
+      // The default value appears to be pre-quoted, as if it is
+      // literally from the sqlite_master CREATE statement.
+      std::string default_value = s.ColumnString(4);
+      insert_columns.push_back(base::StringPrintf(
+          "IFNULL(%s,%s)", column_name.c_str(), default_value.c_str()));
+    }
+  }
+
+  // Receiving no column information implies that the table doesn't exist.
+  if (create_column_decls.empty())
+    return false;
+
+  // If the PRIMARY KEY was a single INTEGER column, convert it to ROWID.
+  if (pk_column_count == 1 && !rowid_decl.empty())
+    create_column_decls[rowid_ofs] = rowid_decl;
+
+  // Additional columns accept anything.
+  // TODO(shess): ignoreN isn't well namespaced.  But it will fail to
+  // execute in case of conflicts.
+  for (size_t i = 0; i < extend_columns; ++i) {
+    create_column_decls.push_back(
+        base::StringPrintf(", ignore%" PRIuS " ANY", i));

[pkotwicz, 2013/11/04 02:38:31]

Is the comma at the beginning of the statement desired?

[Scott Hess - ex-Googler, 2013/11/04 21:41:44]

Hmm, leftover from a previous version which appended everything rather than
building a vector.  Surprised the unit test didn't fubar... well, it accepts the
empty segment, must be a bug in the virtual table implementation.  Thanks for
catching this!

[Scott Hess - ex-Googler, 2013/11/12 20:25:24]

Weird - virtual table doesn't even see the empty column decl.  I guess maybe
it's a sqlite bug.

+  }
+
+  std::string recover_create(base::StringPrintf(
+      "CREATE VIRTUAL TABLE temp.recover_%s USING recover(corrupt.%s, %s)",
+      table_name,
+      table_name,
+      JoinString(create_column_decls, ',').c_str()));
+
+  std::string recover_insert(base::StringPrintf(
+      "INSERT OR REPLACE INTO main.%s SELECT %s FROM temp.recover_%s",
+      table_name,
+      JoinString(insert_columns, ',').c_str(),
+      table_name));
+
+  std::string recover_drop(base::StringPrintf(
+      "DROP TABLE temp.recover_%s", table_name));
+
+  if (!db()->Execute(recover_create.c_str()))
+    return false;
+
+  if (!db()->Execute(recover_insert.c_str())) {
+    ignore_result(db()->Execute(recover_drop.c_str()));
+    return false;
+  }
+
+  // TODO(shess): Is leaving the recover table around a breaker?
+  return db()->Execute(recover_drop.c_str());
+}
+
+bool Recovery::SetupMeta() {
+  const char kCreateSql[] =
+      "CREATE VIRTUAL TABLE temp.recover_meta USING recover"
+      "("
+      "corrupt.meta,"
+      "key TEXT NOT NULL,"
+      "value ANY"  // Whatever is stored.
+      ")";
+  return db()->Execute(kCreateSql);
+}
+
+bool Recovery::GetMetaVersionNumber(int* version) {
+  DCHECK(version);
+  // TODO(shess): DCHECK(db()->DoesTableExist("temp.recover_meta"));
+  // Unfortunately, DoesTableExist() queries sqlite_master, not
+  // sqlite_temp_master.
+
+  const char kVersionSql[] =
+      "SELECT value FROM temp.recover_meta WHERE key = 'version'";
+  sql::Statement recovery_version(db()->GetUniqueStatement(kVersionSql));
+  if (!recovery_version.Step())
+    return false;
+
+  *version = recovery_version.ColumnInt(0);
+  return true;
+}
+
 }  // namespace sql