components/search_engines/keyword_table.cc

   1 // Copyright 2014 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 "components/search_engines/keyword_table.h"
   6
   7 #include <set>
   8
   9 #include "base/json/json_reader.h"
  10 #include "base/json/json_writer.h"
  11 #include "base/logging.h"
  12 #include "base/memory/scoped_ptr.h"
  13 #include "base/strings/string_number_conversions.h"
  14 #include "base/strings/string_split.h"
  15 #include "base/strings/string_util.h"
  16 #include "base/strings/utf_string_conversions.h"
  17 #include "base/values.h"
  18 #include "components/history/core/browser/url_database.h"
  19 #include "components/search_engines/search_terms_data.h"
  20 #include "components/search_engines/template_url.h"
  21 #include "components/webdata/common/web_database.h"
  22 #include "sql/statement.h"
  23 #include "sql/transaction.h"
  24 #include "url/gurl.h"
  25
  26 using base::Time;
  27
  28 // static
  29 const char KeywordTable::kDefaultSearchProviderKey[] =
  30     "Default Search Provider ID";
  31
  32 namespace {
  33
  34 // Keys used in the meta table.
  35 const char kBuiltinKeywordVersion[] = "Builtin Keyword Version";
  36
  37 const std::string ColumnsForVersion(int version, bool concatenated) {
  38   std::vector<std::string> columns;
  39
  40   columns.push_back("id");
  41   columns.push_back("short_name");
  42   columns.push_back("keyword");
  43   columns.push_back("favicon_url");
  44   columns.push_back("url");
  45   columns.push_back("safe_for_autoreplace");
  46   columns.push_back("originating_url");
  47   columns.push_back("date_created");
  48   columns.push_back("usage_count");
  49   columns.push_back("input_encodings");
  50   columns.push_back("show_in_default_list");
  51   columns.push_back("suggest_url");
  52   columns.push_back("prepopulate_id");
  53   if (version <= 44) {
  54     // Columns removed after version 44.
  55     columns.push_back("autogenerate_keyword");
  56     columns.push_back("logo_id");
  57   }
  58   columns.push_back("created_by_policy");
  59   columns.push_back("instant_url");
  60   columns.push_back("last_modified");
  61   columns.push_back("sync_guid");
  62   if (version >= 47) {
  63     // Column added in version 47.
  64     columns.push_back("alternate_urls");
  65   }
  66   if (version >= 49) {
  67     // Column added in version 49.
  68     columns.push_back("search_terms_replacement_key");
  69   }
  70   if (version >= 52) {
  71     // Column added in version 52.
  72     columns.push_back("image_url");
  73     columns.push_back("search_url_post_params");
  74     columns.push_back("suggest_url_post_params");
  75     columns.push_back("instant_url_post_params");
  76     columns.push_back("image_url_post_params");
  77   }
  78   if (version >= 53) {
  79     // Column added in version 53.
  80     columns.push_back("new_tab_url");
  81   }
  82
  83   return base::JoinString(columns, std::string(concatenated ? " || " : ", "));
  84 }
  85
  86
  87 // Inserts the data from |data| into |s|.  |s| is assumed to have slots for all
  88 // the columns in the keyword table.  |id_column| is the slot number to bind
  89 // |data|'s |id| to; |starting_column| is the slot number of the first of a
  90 // contiguous set of slots to bind all the other fields to.
  91 void BindURLToStatement(const TemplateURLData& data,
  92                         sql::Statement* s,
  93                         int id_column,
  94                         int starting_column) {
  95   // Serialize |alternate_urls| to JSON.
  96   // TODO(beaudoin): Check what it would take to use a new table to store
  97   // alternate_urls while keeping backups and table signature in a good state.
  98   // See: crbug.com/153520
  99   base::ListValue alternate_urls_value;
 100   for (size_t i = 0; i < data.alternate_urls.size(); ++i)
 101     alternate_urls_value.AppendString(data.alternate_urls[i]);
 102   std::string alternate_urls;
 103   base::JSONWriter::Write(alternate_urls_value, &alternate_urls);
 104
 105   s->BindInt64(id_column, data.id);
 106   s->BindString16(starting_column, data.short_name());
 107   s->BindString16(starting_column + 1, data.keyword());
 108   s->BindString(starting_column + 2, data.favicon_url.is_valid() ?
 109       history::URLDatabase::GURLToDatabaseURL(data.favicon_url) :
 110       std::string());
 111   s->BindString(starting_column + 3, data.url());
 112   s->BindBool(starting_column + 4, data.safe_for_autoreplace);
 113   s->BindString(starting_column + 5, data.originating_url.is_valid() ?
 114       history::URLDatabase::GURLToDatabaseURL(data.originating_url) :
 115       std::string());
 116   s->BindInt64(starting_column + 6, data.date_created.ToTimeT());
 117   s->BindInt(starting_column + 7, data.usage_count);
 118   s->BindString(starting_column + 8,
 119                 base::JoinString(data.input_encodings, ";"));
 120   s->BindBool(starting_column + 9, data.show_in_default_list);
 121   s->BindString(starting_column + 10, data.suggestions_url);
 122   s->BindInt(starting_column + 11, data.prepopulate_id);
 123   s->BindBool(starting_column + 12, data.created_by_policy);
 124   s->BindString(starting_column + 13, data.instant_url);
 125   s->BindInt64(starting_column + 14, data.last_modified.ToTimeT());
 126   s->BindString(starting_column + 15, data.sync_guid);
 127   s->BindString(starting_column + 16, alternate_urls);
 128   s->BindString(starting_column + 17, data.search_terms_replacement_key);
 129   s->BindString(starting_column + 18, data.image_url);
 130   s->BindString(starting_column + 19, data.search_url_post_params);
 131   s->BindString(starting_column + 20, data.suggestions_url_post_params);
 132   s->BindString(starting_column + 21, data.instant_url_post_params);
 133   s->BindString(starting_column + 22, data.image_url_post_params);
 134   s->BindString(starting_column + 23, data.new_tab_url);
 135 }
 136
 137 WebDatabaseTable::TypeKey GetKey() {
 138   // We just need a unique constant. Use the address of a static that
 139   // COMDAT folding won't touch in an optimizing linker.
 140   static int table_key = 0;
 141   return reinterpret_cast<void*>(&table_key);
 142 }
 143
 144 }  // namespace
 145
 146 KeywordTable::KeywordTable() {
 147 }
 148
 149 KeywordTable::~KeywordTable() {}
 150
 151 KeywordTable* KeywordTable::FromWebDatabase(WebDatabase* db) {
 152   return static_cast<KeywordTable*>(db->GetTable(GetKey()));
 153 }
 154
 155 WebDatabaseTable::TypeKey KeywordTable::GetTypeKey() const {
 156   return GetKey();
 157 }
 158
 159 bool KeywordTable::CreateTablesIfNecessary() {
 160   return db_->DoesTableExist("keywords") ||
 161       db_->Execute("CREATE TABLE keywords ("
 162                    "id INTEGER PRIMARY KEY,"
 163                    "short_name VARCHAR NOT NULL,"
 164                    "keyword VARCHAR NOT NULL,"
 165                    "favicon_url VARCHAR NOT NULL,"
 166                    "url VARCHAR NOT NULL,"
 167                    "safe_for_autoreplace INTEGER,"
 168                    "originating_url VARCHAR,"
 169                    "date_created INTEGER DEFAULT 0,"
 170                    "usage_count INTEGER DEFAULT 0,"
 171                    "input_encodings VARCHAR,"
 172                    "show_in_default_list INTEGER,"
 173                    "suggest_url VARCHAR,"
 174                    "prepopulate_id INTEGER DEFAULT 0,"
 175                    "created_by_policy INTEGER DEFAULT 0,"
 176                    "instant_url VARCHAR,"
 177                    "last_modified INTEGER DEFAULT 0,"
 178                    "sync_guid VARCHAR,"
 179                    "alternate_urls VARCHAR,"
 180                    "search_terms_replacement_key VARCHAR,"
 181                    "image_url VARCHAR,"
 182                    "search_url_post_params VARCHAR,"
 183                    "suggest_url_post_params VARCHAR,"
 184                    "instant_url_post_params VARCHAR,"
 185                    "image_url_post_params VARCHAR,"
 186                    "new_tab_url VARCHAR)");
 187 }
 188
 189 bool KeywordTable::IsSyncable() {
 190   return true;
 191 }
 192
 193 bool KeywordTable::MigrateToVersion(int version,
 194                                     bool* update_compatible_version) {
 195   // Migrate if necessary.
 196   switch (version) {
 197     case 53:
 198       *update_compatible_version = true;
 199       return MigrateToVersion53AddNewTabURLColumn();
 200     case 59:
 201       *update_compatible_version = true;
 202       return MigrateToVersion59RemoveExtensionKeywords();
 203   }
 204
 205   return true;
 206 }
 207
 208 bool KeywordTable::PerformOperations(const Operations& operations) {
 209   sql::Transaction transaction(db_);
 210   if (!transaction.Begin())
 211     return false;
 212
 213   for (Operations::const_iterator i(operations.begin()); i != operations.end();
 214        ++i) {
 215     switch (i->first) {
 216       case ADD:
 217         if (!AddKeyword(i->second))
 218           return false;
 219         break;
 220
 221       case REMOVE:
 222         if (!RemoveKeyword(i->second.id))
 223           return false;
 224         break;
 225
 226       case UPDATE:
 227         if (!UpdateKeyword(i->second))
 228           return false;
 229         break;
 230     }
 231   }
 232
 233   return transaction.Commit();
 234 }
 235
 236 bool KeywordTable::GetKeywords(Keywords* keywords) {
 237   std::string query("SELECT " + GetKeywordColumns() +
 238                     " FROM keywords ORDER BY id ASC");
 239   sql::Statement s(db_->GetUniqueStatement(query.c_str()));
 240
 241   std::set<TemplateURLID> bad_entries;
 242   while (s.Step()) {
 243     keywords->push_back(TemplateURLData());
 244     if (!GetKeywordDataFromStatement(s, &keywords->back())) {
 245       bad_entries.insert(s.ColumnInt64(0));
 246       keywords->pop_back();
 247     }
 248   }
 249   bool succeeded = s.Succeeded();
 250   for (std::set<TemplateURLID>::const_iterator i(bad_entries.begin());
 251        i != bad_entries.end(); ++i)
 252     succeeded &= RemoveKeyword(*i);
 253   return succeeded;
 254 }
 255
 256 bool KeywordTable::SetDefaultSearchProviderID(int64 id) {
 257   return meta_table_->SetValue(kDefaultSearchProviderKey, id);
 258 }
 259
 260 int64 KeywordTable::GetDefaultSearchProviderID() {
 261   int64 value = kInvalidTemplateURLID;
 262   meta_table_->GetValue(kDefaultSearchProviderKey, &value);
 263   return value;
 264 }
 265
 266 bool KeywordTable::SetBuiltinKeywordVersion(int version) {
 267   return meta_table_->SetValue(kBuiltinKeywordVersion, version);
 268 }
 269
 270 int KeywordTable::GetBuiltinKeywordVersion() {
 271   int version = 0;
 272   return meta_table_->GetValue(kBuiltinKeywordVersion, &version) ? version : 0;
 273 }
 274
 275 // static
 276 std::string KeywordTable::GetKeywordColumns() {
 277   return ColumnsForVersion(WebDatabase::kCurrentVersionNumber, false);
 278 }
 279
 280 bool KeywordTable::MigrateToVersion53AddNewTabURLColumn() {
 281   return db_->Execute("ALTER TABLE keywords ADD COLUMN new_tab_url "
 282                       "VARCHAR DEFAULT ''");
 283 }
 284
 285 bool KeywordTable::MigrateToVersion59RemoveExtensionKeywords() {
 286   return db_->Execute("DELETE FROM keywords "
 287                       "WHERE url LIKE 'chrome-extension://%'");
 288 }
 289
 290 // static
 291 bool KeywordTable::GetKeywordDataFromStatement(const sql::Statement& s,
 292                                                TemplateURLData* data) {
 293   DCHECK(data);
 294
 295   data->SetShortName(s.ColumnString16(1));
 296   data->SetKeyword(s.ColumnString16(2));
 297   // Due to past bugs, we might have persisted entries with empty URLs.  Avoid
 298   // reading these out.  (GetKeywords() will delete these entries on return.)
 299   // NOTE: This code should only be needed as long as we might be reading such
 300   // potentially-old data and can be removed afterward.
 301   if (s.ColumnString(4).empty())
 302     return false;
 303   data->SetURL(s.ColumnString(4));
 304   data->suggestions_url = s.ColumnString(11);
 305   data->instant_url = s.ColumnString(14);
 306   data->image_url = s.ColumnString(19);
 307   data->new_tab_url = s.ColumnString(24);
 308   data->search_url_post_params = s.ColumnString(20);
 309   data->suggestions_url_post_params = s.ColumnString(21);
 310   data->instant_url_post_params = s.ColumnString(22);
 311   data->image_url_post_params = s.ColumnString(23);
 312   data->favicon_url = GURL(s.ColumnString(3));
 313   data->originating_url = GURL(s.ColumnString(6));
 314   data->show_in_default_list = s.ColumnBool(10);
 315   data->safe_for_autoreplace = s.ColumnBool(5);
 316   data->input_encodings = base::SplitString(
 317       s.ColumnString(9), ";", base::TRIM_WHITESPACE, base::SPLIT_WANT_ALL);
 318   data->id = s.ColumnInt64(0);
 319   data->date_created = Time::FromTimeT(s.ColumnInt64(7));
 320   data->last_modified = Time::FromTimeT(s.ColumnInt64(15));
 321   data->created_by_policy = s.ColumnBool(13);
 322   data->usage_count = s.ColumnInt(8);
 323   data->prepopulate_id = s.ColumnInt(12);
 324   data->sync_guid = s.ColumnString(16);
 325
 326   data->alternate_urls.clear();
 327   base::JSONReader json_reader;
 328   scoped_ptr<base::Value> value(json_reader.ReadToValue(s.ColumnString(17)));
 329   base::ListValue* alternate_urls_value;
 330   if (value.get() && value->GetAsList(&alternate_urls_value)) {
 331     std::string alternate_url;
 332     for (size_t i = 0; i < alternate_urls_value->GetSize(); ++i) {
 333       if (alternate_urls_value->GetString(i, &alternate_url))
 334         data->alternate_urls.push_back(alternate_url);
 335     }
 336   }
 337
 338   data->search_terms_replacement_key = s.ColumnString(18);
 339
 340   return true;
 341 }
 342
 343 bool KeywordTable::AddKeyword(const TemplateURLData& data) {
 344   DCHECK(data.id);
 345   std::string query("INSERT INTO keywords (" + GetKeywordColumns() + ") "
 346                     "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
 347                     "        ?)");
 348   sql::Statement s(db_->GetCachedStatement(SQL_FROM_HERE, query.c_str()));
 349   BindURLToStatement(data, &s, 0, 1);
 350
 351   return s.Run();
 352 }
 353
 354 bool KeywordTable::RemoveKeyword(TemplateURLID id) {
 355   DCHECK(id);
 356   sql::Statement s(db_->GetCachedStatement(
 357       SQL_FROM_HERE, "DELETE FROM keywords WHERE id = ?"));
 358   s.BindInt64(0, id);
 359
 360   return s.Run();
 361 }
 362
 363 bool KeywordTable::UpdateKeyword(const TemplateURLData& data) {
 364   DCHECK(data.id);
 365   sql::Statement s(db_->GetCachedStatement(
 366       SQL_FROM_HERE,
 367       "UPDATE keywords SET short_name=?, keyword=?, favicon_url=?, url=?, "
 368       "safe_for_autoreplace=?, originating_url=?, date_created=?, "
 369       "usage_count=?, input_encodings=?, show_in_default_list=?, "
 370       "suggest_url=?, prepopulate_id=?, created_by_policy=?, instant_url=?, "
 371       "last_modified=?, sync_guid=?, alternate_urls=?, "
 372       "search_terms_replacement_key=?, image_url=?, search_url_post_params=?, "
 373       "suggest_url_post_params=?, instant_url_post_params=?, "
 374       "image_url_post_params=?, new_tab_url=? WHERE id=?"));
 375   BindURLToStatement(data, &s, 24, 0);  // "24" binds id() as the last item.
 376
 377   return s.Run();
 378 }
 379
 380 bool KeywordTable::GetKeywordAsString(TemplateURLID id,
 381                                       const std::string& table_name,
 382                                       std::string* result) {
 383   std::string query("SELECT " +
 384       ColumnsForVersion(WebDatabase::kCurrentVersionNumber, true) +
 385       " FROM " + table_name + " WHERE id=?");
 386   sql::Statement s(db_->GetUniqueStatement(query.c_str()));
 387   s.BindInt64(0, id);
 388
 389   if (!s.Step()) {
 390     LOG_IF(WARNING, s.Succeeded()) << "No keyword with id: " << id
 391                                    << ", ignoring.";
 392     return true;
 393   }
 394
 395   if (!s.Succeeded())
 396     return false;
 397
 398   *result = s.ColumnString(0);
 399   return true;
 400 }
 401
 402 bool KeywordTable::MigrateKeywordsTableForVersion45(const std::string& name) {
 403   // Create a new table without the columns we're dropping.
 404   if (!db_->Execute("CREATE TABLE keywords_temp ("
 405                     "id INTEGER PRIMARY KEY,"
 406                     "short_name VARCHAR NOT NULL,"
 407                     "keyword VARCHAR NOT NULL,"
 408                     "favicon_url VARCHAR NOT NULL,"
 409                     "url VARCHAR NOT NULL,"
 410                     "safe_for_autoreplace INTEGER,"
 411                     "originating_url VARCHAR,"
 412                     "date_created INTEGER DEFAULT 0,"
 413                     "usage_count INTEGER DEFAULT 0,"
 414                     "input_encodings VARCHAR,"
 415                     "show_in_default_list INTEGER,"
 416                     "suggest_url VARCHAR,"
 417                     "prepopulate_id INTEGER DEFAULT 0,"
 418                     "created_by_policy INTEGER DEFAULT 0,"
 419                     "instant_url VARCHAR,"
 420                     "last_modified INTEGER DEFAULT 0,"
 421                     "sync_guid VARCHAR)"))
 422     return false;
 423   std::string sql("INSERT INTO keywords_temp SELECT " +
 424                   ColumnsForVersion(46, false) + " FROM " + name);
 425   if (!db_->Execute(sql.c_str()))
 426     return false;
 427
 428   // NOTE: The ORDER BY here ensures that the uniquing process for keywords will
 429   // happen identically on both the normal and backup tables.
 430   sql = "SELECT id, keyword, url, autogenerate_keyword FROM " + name +
 431       " ORDER BY id ASC";
 432   sql::Statement s(db_->GetUniqueStatement(sql.c_str()));
 433   base::string16 placeholder_keyword(base::ASCIIToUTF16("dummy"));
 434   std::set<base::string16> keywords;
 435   while (s.Step()) {
 436     base::string16 keyword(s.ColumnString16(1));
 437     bool generate_keyword = keyword.empty() || s.ColumnBool(3);
 438     if (generate_keyword)
 439       keyword = placeholder_keyword;
 440     TemplateURLData data;
 441     data.SetKeyword(keyword);
 442     data.SetURL(s.ColumnString(2));
 443     TemplateURL turl(data);
 444     // Don't persist extension keywords to disk.  These will get added to the
 445     // TemplateURLService as the extensions are loaded.
 446     bool delete_entry = turl.GetType() == TemplateURL::OMNIBOX_API_EXTENSION;
 447     if (!delete_entry && generate_keyword) {
 448       // Explicitly generate keywords for all rows with the autogenerate bit set
 449       // or where the keyword is empty.
 450       SearchTermsData terms_data;
 451       GURL url(turl.GenerateSearchURL(terms_data));
 452       if (!url.is_valid()) {
 453         delete_entry = true;
 454       } else {
 455         // Ensure autogenerated keywords are unique.
 456         keyword = TemplateURL::GenerateKeyword(
 457             url, terms_data.GetAcceptLanguages());
 458         while (keywords.count(keyword))
 459           keyword.append(base::ASCIIToUTF16("_"));
 460         sql::Statement u(db_->GetUniqueStatement(
 461             "UPDATE keywords_temp SET keyword=? WHERE id=?"));
 462         u.BindString16(0, keyword);
 463         u.BindInt64(1, s.ColumnInt64(0));
 464         if (!u.Run())
 465           return false;
 466       }
 467     }
 468     if (delete_entry) {
 469       sql::Statement u(db_->GetUniqueStatement(
 470           "DELETE FROM keywords_temp WHERE id=?"));
 471       u.BindInt64(0, s.ColumnInt64(0));
 472       if (!u.Run())
 473         return false;
 474     } else {
 475       keywords.insert(keyword);
 476     }
 477   }
 478
 479   // Replace the old table with the new one.
 480   sql = "DROP TABLE " + name;
 481   if (!db_->Execute(sql.c_str()))
 482     return false;
 483   sql = "ALTER TABLE keywords_temp RENAME TO " + name;
 484   return db_->Execute(sql.c_str());
 485 }