Introduces a search term extraction mechanism working for arbitrary search providers.

chrome/browser/webdata/keyword_table.cc

 // Copyright (c) 2012 The Chromium Authors. All rights reserved.
 // Use of this source code is governed by a BSD-style license that can be
 // found in the LICENSE file.
 
 #include "chrome/browser/webdata/keyword_table.h"
 
 #include <set>
 
+#include "base/json/json_reader.h"
+#include "base/json/json_writer.h"
 #include "base/logging.h"
 #include "base/memory/scoped_ptr.h"
 #include "base/metrics/histogram.h"
 #include "base/metrics/stats_counters.h"
 #include "base/string_number_conversions.h"
 #include "base/string_split.h"
 #include "base/string_util.h"
 #include "base/stringprintf.h"
 #include "base/utf_string_conversions.h"
+#include "base/values.h"
 #include "chrome/browser/history/history_database.h"
 #include "chrome/browser/protector/histograms.h"
 #include "chrome/browser/protector/protector_utils.h"
 #include "chrome/browser/search_engines/search_terms_data.h"
 #include "chrome/browser/search_engines/template_url.h"
 #include "chrome/browser/search_engines/template_url_service.h"
 #include "chrome/browser/webdata/web_database.h"
 #include "googleurl/src/gurl.h"
 #include "sql/statement.h"
 #include "sql/transaction.h"
 
 using base::Time;
 
 // static
 const char KeywordTable::kDefaultSearchProviderKey[] =
     "Default Search Provider ID";
 const char KeywordTable::kDefaultSearchIDBackupKey[] =
     "Default Search Provider ID Backup";
 const char KeywordTable::kBackupSignatureKey[] =
     "Default Search Provider ID Backup Signature";
 const char KeywordTable::kKeywordColumns[] = "id, short_name, keyword, "
     "favicon_url, url, safe_for_autoreplace, originating_url, date_created, "
     "usage_count, input_encodings, show_in_default_list, suggest_url, "
-    "prepopulate_id, created_by_policy, instant_url, last_modified, sync_guid";
+    "prepopulate_id, created_by_policy, instant_url, last_modified, sync_guid, "
+    "alternate_urls";
 
 namespace {
 
 // Keys used in the meta table.
 const char kBuiltinKeywordVersion[] = "Builtin Keyword Version";
 
 // The set of columns up through version 44.  (There were different columns
 // below version 29 but none of the code below needs to worry about that case.)
 const char kKeywordColumnsVersion44Concatenated[] = "id || short_name || "
     "keyword || favicon_url || url || safe_for_autoreplace || "
     "originating_url || date_created || usage_count || input_encodings || "
     "show_in_default_list || suggest_url || prepopulate_id || "
     "autogenerate_keyword || logo_id || created_by_policy || instant_url || "
     "last_modified || sync_guid";
 const char kKeywordColumnsVersion44[] = "id, short_name, keyword, favicon_url, "
     "url, safe_for_autoreplace, originating_url, date_created, usage_count, "
     "input_encodings, show_in_default_list, suggest_url, prepopulate_id, "
     "autogenerate_keyword, logo_id, created_by_policy, instant_url, "
     "last_modified, sync_guid";
-// NOTE: Remember to change what |kKeywordColumnsVersion45| says if the column
-// set in |kKeywordColumns| changes, and update any code that needs to switch
-// column sets based on a version number!
-const char* const kKeywordColumnsVersion45 = KeywordTable::kKeywordColumns;
+// The set of columns from version 45 through version 46 (inclusively).
+const char kKeywordColumnsVersion46Concatenated[] = "id || short_name || "
+    "keyword || favicon_url || url || safe_for_autoreplace || "
+    "originating_url || date_created || usage_count || input_encodings || "
+    "show_in_default_list || suggest_url || prepopulate_id || "
+    "created_by_policy || instant_url || last_modified || sync_guid";
+const char kKeywordColumnsVersion46[] = "id, short_name, keyword, "
+    "favicon_url, url, safe_for_autoreplace, originating_url, date_created, "
+    "usage_count, input_encodings, show_in_default_list, suggest_url, "
+    "prepopulate_id, created_by_policy, instant_url, last_modified, sync_guid";
 
 // The current columns.
-const char kKeywordColumnsConcatenated[] = "id || short_name || keyword || "

[Peter Kasting, 2012/10/02 21:47:59]

Nit: Why rewrap this value (and all the subsequent lines)?

[beaudoin, 2012/10/03 22:46:52]

Done.

-    "favicon_url || url || safe_for_autoreplace || originating_url || "
-    "date_created || usage_count || input_encodings || show_in_default_list || "
-    "suggest_url || prepopulate_id || created_by_policy || instant_url || "
-    "last_modified || sync_guid";
+const char kKeywordColumnsConcatenated[] = "id || short_name || "
+    "keyword || favicon_url || url || safe_for_autoreplace || "
+    "originating_url || date_created || usage_count || input_encodings || "
+    "show_in_default_list || suggest_url || prepopulate_id || "
+    "created_by_policy || instant_url || last_modified || sync_guid || "
+    "alternate_urls";
 
 // Inserts the data from |data| into |s|.  |s| is assumed to have slots for all
 // the columns in the keyword table.  |id_column| is the slot number to bind
 // |data|'s |id| to; |starting_column| is the slot number of the first of a
 // contiguous set of slots to bind all the other fields to.
 void BindURLToStatement(const TemplateURLData& data,
                         sql::Statement* s,
                         int id_column,
                         int starting_column) {
+  // Serialize |alternate_urls| to JSON.
+  // TODO(beaudoin): Check what it would take to use a new table to store
+  // alternate_urls while keeping backups and table signature in a good state.
+  // See: crbug.com/153520
+  ListValue alternate_urls_value;
+  for (size_t i = 0; i < data.alternate_urls.size(); ++i)
+    alternate_urls_value.AppendString(data.alternate_urls[i]);
+  std::string alternate_urls;
+  base::JSONWriter::Write(&alternate_urls_value, &alternate_urls);
+
   s->BindInt64(id_column, data.id);
   s->BindString16(starting_column, data.short_name);
   s->BindString16(starting_column + 1, data.keyword());
   s->BindString(starting_column + 2, data.favicon_url.is_valid() ?
       history::HistoryDatabase::GURLToDatabaseURL(data.favicon_url) :
       std::string());
   s->BindString(starting_column + 3, data.url());
   s->BindBool(starting_column + 4, data.safe_for_autoreplace);
   s->BindString(starting_column + 5, data.originating_url.is_valid() ?
       history::HistoryDatabase::GURLToDatabaseURL(data.originating_url) :
       std::string());
   s->BindInt64(starting_column + 6, data.date_created.ToTimeT());
   s->BindInt(starting_column + 7, data.usage_count);
   s->BindString(starting_column + 8, JoinString(data.input_encodings, ';'));
   s->BindBool(starting_column + 9, data.show_in_default_list);
   s->BindString(starting_column + 10, data.suggestions_url);
   s->BindInt(starting_column + 11, data.prepopulate_id);
   s->BindBool(starting_column + 12, data.created_by_policy);
   s->BindString(starting_column + 13, data.instant_url);
   s->BindInt64(starting_column + 14, data.last_modified.ToTimeT());
   s->BindString(starting_column + 15, data.sync_guid);
+  s->BindString(starting_column + 16, alternate_urls);
 }
 
 }  // anonymous namespace
 
 KeywordTable::KeywordTable(sql::Connection* db, sql::MetaTable* meta_table)
     : WebDatabaseTable(db, meta_table),
       backup_overwritten_(false) {
 }
 
 KeywordTable::~KeywordTable() {}
@@ skipping 10 matching lines @@
                     "originating_url VARCHAR,"
                     "date_created INTEGER DEFAULT 0,"
                     "usage_count INTEGER DEFAULT 0,"
                     "input_encodings VARCHAR,"
                     "show_in_default_list INTEGER,"
                     "suggest_url VARCHAR,"
                     "prepopulate_id INTEGER DEFAULT 0,"
                     "created_by_policy INTEGER DEFAULT 0,"
                     "instant_url VARCHAR,"
                     "last_modified INTEGER DEFAULT 0,"
-                    "sync_guid VARCHAR)") &&
+                    "sync_guid VARCHAR,"
+                    "alternate_urls VARCHAR)") &&
        UpdateBackupSignature(WebDatabase::kCurrentVersionNumber));
 }
 
 bool KeywordTable::IsSyncable() {
   return true;
 }
 
 bool KeywordTable::AddKeyword(const TemplateURLData& data) {
   DCHECK(data.id);
   std::string query("INSERT INTO keywords (" + std::string(kKeywordColumns) +
-                    ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
+                    ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
   sql::Statement s(db_->GetUniqueStatement(query.c_str()));
   BindURLToStatement(data, &s, 0, 1);
 
   return s.Run() && UpdateBackupSignature(WebDatabase::kCurrentVersionNumber);
 }
 
 bool KeywordTable::RemoveKeyword(TemplateURLID id) {
   DCHECK(id);
   sql::Statement s(
       db_->GetUniqueStatement("DELETE FROM keywords WHERE id = ?"));
@@ skipping 21 matching lines @@
     succeeded &= RemoveKeyword(*i);
   return succeeded;
 }
 
 bool KeywordTable::UpdateKeyword(const TemplateURLData& data) {
   DCHECK(data.id);
   sql::Statement s(db_->GetUniqueStatement("UPDATE keywords SET short_name=?, "
       "keyword=?, favicon_url=?, url=?, safe_for_autoreplace=?, "
       "originating_url=?, date_created=?, usage_count=?, input_encodings=?, "
       "show_in_default_list=?, suggest_url=?, prepopulate_id=?, "
-      "created_by_policy=?, instant_url=?, last_modified=?, sync_guid=? WHERE "
-      "id=?"));
-  BindURLToStatement(data, &s, 16, 0);  // "16" binds id() as the last item.
+      "created_by_policy=?, instant_url=?, last_modified=?, sync_guid=?, "
+      "alternate_urls=? WHERE id=?"));
+  BindURLToStatement(data, &s, 17, 0);  // "17" binds id() as the last item.
 
   return s.Run() && UpdateBackupSignature(WebDatabase::kCurrentVersionNumber);
 }
 
 bool KeywordTable::SetDefaultSearchProviderID(int64 id) {
   // Added for http://crbug.com/116952.
   UMA_HISTOGRAM_COUNTS_100("Search.DefaultSearchProviderID",
                            static_cast<int32>(id));
   return meta_table_->SetValue(kDefaultSearchProviderKey, id) &&
       UpdateBackupSignature(WebDatabase::kCurrentVersionNumber);
@@ skipping 104 matching lines @@
 bool KeywordTable::MigrateToVersion26AddCreatedByPolicyColumn() {
   return db_->Execute("ALTER TABLE keywords ADD COLUMN created_by_policy "
                       "INTEGER DEFAULT 0");
 }
 
 bool KeywordTable::MigrateToVersion28SupportsInstantColumn() {
   return db_->Execute("ALTER TABLE keywords ADD COLUMN supports_instant "
                       "INTEGER DEFAULT 0");
 }
 
-bool KeywordTable::MigrateToVersion29InstantUrlToSupportsInstant() {
+bool KeywordTable::MigrateToVersion29InstantURLToSupportsInstant() {
   sql::Transaction transaction(db_);
   return transaction.Begin() &&
       db_->Execute("ALTER TABLE keywords ADD COLUMN instant_url VARCHAR") &&
       db_->Execute("CREATE TABLE keywords_temp ("
                    "id INTEGER PRIMARY KEY,"
                    "short_name VARCHAR NOT NULL,"
                    "keyword VARCHAR NOT NULL,"
                    "favicon_url VARCHAR NOT NULL,"
                    "url VARCHAR NOT NULL,"
                    "safe_for_autoreplace INTEGER,"
@@ skipping 56 matching lines @@
     // Old backup was invalid; drop the table entirely, which will trigger the
     // protector code to prompt the user and recreate the table.
     if (db_->DoesTableExist("keywords_backup") &&
         !db_->Execute("DROP TABLE keywords_backup"))
       return false;
   }
 
   return transaction.Commit();
 }
 
+bool KeywordTable::MigrateToVersion47AddAlternateURLsColumn() {
+  sql::Transaction transaction(db_);
+
+  // Fill the |alternate_urls| column with empty strings, otherwise it breaks
+  // code relying on |kKeywordColumnsConcatenated|.

[Peter Kasting, 2012/10/02 21:47:59]

Why?  We have other VARCHAR columns that don't default to the empty string.

[beaudoin, 2012/10/03 22:46:52]

It's needed for the migration. When adding the column to every existing entry in
the table, if there is no default then NULL will be in there and it breaks the
table signing code. (Took me forever to debug this one!)

We do not need the default after the migration because we will always put a JSON
list in there (potentially empty).

[Peter Kasting, 2012/10/03 22:59:33]

So doesn't that mean that all our other VARCHARs should really be DEFAULT ''
too?  Since if we were to ever insert a row without specifying one, the embedded
NUL would break things?  This will also establish a pattern so that if others
add more string columns, they'll be less likely to trip on this.

Alternately, can we write extraction code such that embedded NULs are handled
correctly?  Maybe that's not possible, or maybe we could simply strip all \0s
from the string?

[beaudoin, 2012/10/04 00:03:58]

I think the fact that any update to the table (save for ALTER TABLE like this)
goes through BindURLToStatement is a good enough guarantee that we wont add a
NUL in there.

I tried to play with the alternate solution however the way we generate the
string for the entire table, in GetTableContents(), is by using a SELECT query
with all the columns concatenated by " || ". When you iterate through the
results of that query, however, it appears that s.ColumnString(0) will return an
empty string if one of the concatenated column had NUL. I did not dig deeper in
sql/statement.cc to see if we can change that behavior.

+  if (!transaction.Begin() ||
+      !db_->Execute("ALTER TABLE keywords ADD COLUMN "
+                    "alternate_urls VARCHAR DEFAULT ''"))
+    return false;
+
+  if (db_->DoesTableExist("keywords_backup")) {
+    if (!db_->Execute("ALTER TABLE keywords_backup ADD COLUMN "
+                      "alternate_urls VARCHAR DEFAULT ''") ||
+        !SignBackup(47))

[Peter Kasting, 2012/10/02 21:47:59]

We should only migrate and sign the backup if it was valid to begin with;
otherwise we should drop it.  See the version 45 migration code above.

[beaudoin, 2012/10/03 22:46:52]

Done.

+      return false;
+  }
+
+  return transaction.Commit();
+}
+
 // static
 bool KeywordTable::GetKeywordDataFromStatement(const sql::Statement& s,
                                                TemplateURLData* data) {
   DCHECK(data);
+
   data->short_name = s.ColumnString16(1);
   data->SetKeyword(s.ColumnString16(2));
   // Due to past bugs, we might have persisted entries with empty URLs.  Avoid
   // reading these out.  (GetKeywords() will delete these entries on return.)
   // NOTE: This code should only be needed as long as we might be reading such
   // potentially-old data and can be removed afterward.
   if (s.ColumnString(4).empty())
     return false;
   data->SetURL(s.ColumnString(4));
   data->suggestions_url = s.ColumnString(11);
   data->instant_url = s.ColumnString(14);
   data->favicon_url = GURL(s.ColumnString(3));
   data->originating_url = GURL(s.ColumnString(6));
   data->show_in_default_list = s.ColumnBool(10);
   data->safe_for_autoreplace = s.ColumnBool(5);
   base::SplitString(s.ColumnString(9), ';', &data->input_encodings);
   data->id = s.ColumnInt64(0);
   data->date_created = Time::FromTimeT(s.ColumnInt64(7));
   data->last_modified = Time::FromTimeT(s.ColumnInt64(15));
   data->created_by_policy = s.ColumnBool(13);
   data->usage_count = s.ColumnInt(8);
   data->prepopulate_id = s.ColumnInt(12);
   data->sync_guid = s.ColumnString(16);
+
+  data->alternate_urls.clear();
+  base::JSONReader json_reader;
+  scoped_ptr<Value> value(json_reader.ReadToValue(s.ColumnString(17)));
+  ListValue* alternate_urls_value;
+  if (value.get() && value->GetAsList(&alternate_urls_value)) {
+    std::string alternate_url;
+    for (size_t i = 0; i < alternate_urls_value->GetSize(); ++i) {
+      if (alternate_urls_value->GetString(i, &alternate_url))
+        data->alternate_urls.push_back(alternate_url);
+    }
+  }
+
   return true;
 }
 
 bool KeywordTable::GetSignatureData(int table_version, std::string* backup) {
   DCHECK(backup);
 
   int64 backup_value = kInvalidTemplateURLID;
   if (!meta_table_->GetValue(kDefaultSearchIDBackupKey, &backup_value)) {
     LOG(ERROR) << "No backup id for signing.";
     return false;
@@ skipping 11 matching lines @@
 
 bool KeywordTable::GetTableContents(const char* table_name,
                                     int table_version,
                                     std::string* contents) {
   DCHECK(contents);
 
   if (!db_->DoesTableExist(table_name))
     return false;
 
   contents->clear();
-  std::string query("SELECT " +
-      std::string((table_version <= 44) ?
-          kKeywordColumnsVersion44Concatenated : kKeywordColumnsConcatenated) +
+  const char* keywords_columns_concatenated = kKeywordColumnsConcatenated;

[Peter Kasting, 2012/10/02 21:47:59]

Nit: Probably makes sense to have helper functions to get the column strings for
the current version.  (2 places)

[beaudoin, 2012/10/03 22:46:52]

Refactored this in a major way, got rid of all the error prone cut-and-paste of
column names.

Done.

+  if (table_version <= 44)
+    keywords_columns_concatenated = kKeywordColumnsVersion44Concatenated;
+  else if (table_version <= 46)
+    keywords_columns_concatenated = kKeywordColumnsVersion46Concatenated;
+  std::string query("SELECT " + std::string(keywords_columns_concatenated) +
       " FROM " + std::string(table_name) + " ORDER BY id ASC");
   sql::Statement s((table_version == WebDatabase::kCurrentVersionNumber) ?
       db_->GetCachedStatement(sql::StatementID(table_name), query.c_str()) :
       db_->GetUniqueStatement(query.c_str()));
   while (s.Step())
     *contents += s.ColumnString(0);
   return s.Succeeded();
 }
 
 bool KeywordTable::UpdateBackupSignature(int table_version) {
   sql::Transaction transaction(db_);
   if (!transaction.Begin())
     return false;
 
   int64 id = kInvalidTemplateURLID;
   if (!UpdateDefaultSearchProviderIDBackup(&id)) {
     LOG(ERROR) << "Failed to update default search id backup.";
     return false;
   }
 
   // Backup of all keywords.
   if (db_->DoesTableExist("keywords_backup") &&
       !db_->Execute("DROP TABLE keywords_backup"))
     return false;
 
+  const char* keywords_columns = kKeywordColumns;
+  if (table_version <= 44)
+    keywords_columns = kKeywordColumnsVersion44;
+  else if (table_version <= 46)
+    keywords_columns = kKeywordColumnsVersion46;
   std::string query("CREATE TABLE keywords_backup AS SELECT " +
-      std::string((table_version <= 44) ?
-          kKeywordColumnsVersion44 : kKeywordColumns) +
-      " FROM keywords ORDER BY id ASC");
+      std::string(keywords_columns) + " FROM keywords ORDER BY id ASC");
   if (!db_->Execute(query.c_str())) {
     LOG(ERROR) << "Failed to create keywords_backup table.";
     return false;
   }
 
   return SignBackup(table_version) && transaction.Commit();
 }
 
 bool KeywordTable::SignBackup(int table_version) {
   std::string data_to_sign;
@@ skipping 68 matching lines @@
                     "input_encodings VARCHAR,"
                     "show_in_default_list INTEGER,"
                     "suggest_url VARCHAR,"
                     "prepopulate_id INTEGER DEFAULT 0,"
                     "created_by_policy INTEGER DEFAULT 0,"
                     "instant_url VARCHAR,"
                     "last_modified INTEGER DEFAULT 0,"
                     "sync_guid VARCHAR)"))
     return false;
   std::string sql("INSERT INTO keywords_temp SELECT " +
-                  std::string(kKeywordColumnsVersion45) + " FROM " + name);
+                  std::string(kKeywordColumnsVersion46) + " FROM " + name);
   if (!db_->Execute(sql.c_str()))
     return false;
 
   // NOTE: The ORDER BY here ensures that the uniquing process for keywords will
   // happen identically on both the normal and backup tables.
   sql = "SELECT id, keyword, url, autogenerate_keyword FROM " + name +
       " ORDER BY id ASC";
   sql::Statement s(db_->GetUniqueStatement(sql.c_str()));
   string16 placeholder_keyword(ASCIIToUTF16("dummy"));
   std::set<string16> keywords;
@@ skipping 41 matching lines @@
     }
   }
 
   // Replace the old table with the new one.
   sql = "DROP TABLE " + name;
   if (!db_->Execute(sql.c_str()))
     return false;
   sql = "ALTER TABLE keywords_temp RENAME TO " + name;
   return db_->Execute(sql.c_str());
 }