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 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, JoinString(data.input_encodings, ';'));
 119   s->BindBool(starting_column + 9, data.show_in_default_list);
 120   s->BindString(starting_column + 10, data.suggestions_url);
 121   s->BindInt(starting_column + 11, data.prepopulate_id);
 122   s->BindBool(starting_column + 12, data.created_by_policy);
 123   s->BindString(starting_column + 13, data.instant_url);
 124   s->BindInt64(starting_column + 14, data.last_modified.ToTimeT());
 125   s->BindString(starting_column + 15, data.sync_guid);
 126   s->BindString(starting_column + 16, alternate_urls);
 127   s->BindString(starting_column + 17, data.search_terms_replacement_key);
 128   s->BindString(starting_column + 18, data.image_url);
 129   s->BindString(starting_column + 19, data.search_url_post_params);
 130   s->BindString(starting_column + 20, data.suggestions_url_post_params);
 131   s->BindString(starting_column + 21, data.instant_url_post_params);
 132   s->BindString(starting_column + 22, data.image_url_post_params);
 133   s->BindString(starting_column + 23, data.new_tab_url);
 134 }
 135
 136 WebDatabaseTable::TypeKey GetKey() {
 137   // We just need a unique constant. Use the address of a static that
 138   // COMDAT folding won't touch in an optimizing linker.
 139   static int table_key = 0;
 140   return reinterpret_cast<void*>(&table_key);
 141 }
 142
 143 }  // namespace
 144
 145 KeywordTable::KeywordTable() {
 146 }
 147
 148 KeywordTable::~KeywordTable() {}
 149
 150 KeywordTable* KeywordTable::FromWebDatabase(WebDatabase* db) {
 151   return static_cast<KeywordTable*>(db->GetTable(GetKey()));
 152 }
 153
 154 WebDatabaseTable::TypeKey KeywordTable::GetTypeKey() const {
 155   return GetKey();
 156 }
 157
 158 bool KeywordTable::CreateTablesIfNecessary() {
 159   return db_->DoesTableExist("keywords") ||
 160       db_->Execute("CREATE TABLE keywords ("
 161                    "id INTEGER PRIMARY KEY,"
 162                    "short_name VARCHAR NOT NULL,"
 163                    "keyword VARCHAR NOT NULL,"
 164                    "favicon_url VARCHAR NOT NULL,"
 165                    "url VARCHAR NOT NULL,"
 166                    "safe_for_autoreplace INTEGER,"
 167                    "originating_url VARCHAR,"
 168                    "date_created INTEGER DEFAULT 0,"
 169                    "usage_count INTEGER DEFAULT 0,"
 170                    "input_encodings VARCHAR,"
 171                    "show_in_default_list INTEGER,"
 172                    "suggest_url VARCHAR,"
 173                    "prepopulate_id INTEGER DEFAULT 0,"
 174                    "created_by_policy INTEGER DEFAULT 0,"
 175                    "instant_url VARCHAR,"
 176                    "last_modified INTEGER DEFAULT 0,"
 177                    "sync_guid VARCHAR,"
 178                    "alternate_urls VARCHAR,"
 179                    "search_terms_replacement_key VARCHAR,"
 180                    "image_url VARCHAR,"
 181                    "search_url_post_params VARCHAR,"
 182                    "suggest_url_post_params VARCHAR,"
 183                    "instant_url_post_params VARCHAR,"
 184                    "image_url_post_params VARCHAR,"
 185                    "new_tab_url VARCHAR)");
 186 }
 187
 188 bool KeywordTable::IsSyncable() {
 189   return true;
 190 }
 191
 192 bool KeywordTable::MigrateToVersion(int version,
 193                                     bool* update_compatible_version) {
 194   // Migrate if necessary.
 195   switch (version) {
 196     case 53:
 197       *update_compatible_version = true;
 198       return MigrateToVersion53AddNewTabURLColumn();
 199     case 59:
 200       *update_compatible_version = true;
 201       return MigrateToVersion59RemoveExtensionKeywords();
 202   }
 203
 204   return true;
 205 }
 206
 207 bool KeywordTable::PerformOperations(const Operations& operations) {
 208   sql::Transaction transaction(db_);
 209   if (!transaction.Begin())
 210     return false;
 211
 212   for (Operations::const_iterator i(operations.begin()); i != operations.end();
 213        ++i) {
 214     switch (i->first) {
 215       case ADD:
 216         if (!AddKeyword(i->second))
 217           return false;
 218         break;
 219
 220       case REMOVE:
 221         if (!RemoveKeyword(i->second.id))
 222           return false;
 223         break;
 224
 225       case UPDATE:
 226         if (!UpdateKeyword(i->second))
 227           return false;
 228         break;
 229     }
 230   }
 231
 232   return transaction.Commit();
 233 }
 234
 235 bool KeywordTable::GetKeywords(Keywords* keywords) {
 236   std::string query("SELECT " + GetKeywordColumns() +
 237                     " FROM keywords ORDER BY id ASC");
 238   sql::Statement s(db_->GetUniqueStatement(query.c_str()));
 239
 240   std::set<TemplateURLID> bad_entries;
 241   while (s.Step()) {
 242     keywords->push_back(TemplateURLData());
 243     if (!GetKeywordDataFromStatement(s, &keywords->back())) {
 244       bad_entries.insert(s.ColumnInt64(0));
 245       keywords->pop_back();
 246     }
 247   }
 248   bool succeeded = s.Succeeded();
 249   for (std::set<TemplateURLID>::const_iterator i(bad_entries.begin());
 250        i != bad_entries.end(); ++i)
 251     succeeded &= RemoveKeyword(*i);
 252   return succeeded;
 253 }
 254
 255 bool KeywordTable::SetDefaultSearchProviderID(int64 id) {
 256   return meta_table_->SetValue(kDefaultSearchProviderKey, id);
 257 }
 258
 259 int64 KeywordTable::GetDefaultSearchProviderID() {
 260   int64 value = kInvalidTemplateURLID;
 261   meta_table_->GetValue(kDefaultSearchProviderKey, &value);
 262   return value;
 263 }
 264
 265 bool KeywordTable::SetBuiltinKeywordVersion(int version) {
 266   return meta_table_->SetValue(kBuiltinKeywordVersion, version);
 267 }
 268
 269 int KeywordTable::GetBuiltinKeywordVersion() {
 270   int version = 0;
 271   return meta_table_->GetValue(kBuiltinKeywordVersion, &version) ? version : 0;
 272 }
 273
 274 // static
 275 std::string KeywordTable::GetKeywordColumns() {
 276   return ColumnsForVersion(WebDatabase::kCurrentVersionNumber, false);
 277 }
 278
 279 bool KeywordTable::MigrateToVersion53AddNewTabURLColumn() {
 280   return db_->Execute("ALTER TABLE keywords ADD COLUMN new_tab_url "
 281                       "VARCHAR DEFAULT ''");
 282 }
 283
 284 bool KeywordTable::MigrateToVersion59RemoveExtensionKeywords() {
 285   return db_->Execute("DELETE FROM keywords "
 286                       "WHERE url LIKE 'chrome-extension://%'");
 287 }
 288
 289 // static
 290 bool KeywordTable::GetKeywordDataFromStatement(const sql::Statement& s,
 291                                                TemplateURLData* data) {
 292   DCHECK(data);
 293
 294   data->short_name = s.ColumnString16(1);
 295   data->SetKeyword(s.ColumnString16(2));
 296   // Due to past bugs, we might have persisted entries with empty URLs.  Avoid
 297   // reading these out.  (GetKeywords() will delete these entries on return.)
 298   // NOTE: This code should only be needed as long as we might be reading such
 299   // potentially-old data and can be removed afterward.
 300   if (s.ColumnString(4).empty())
 301     return false;
 302   data->SetURL(s.ColumnString(4));
 303   data->suggestions_url = s.ColumnString(11);
 304   data->instant_url = s.ColumnString(14);
 305   data->image_url = s.ColumnString(19);
 306   data->new_tab_url = s.ColumnString(24);
 307   data->search_url_post_params = s.ColumnString(20);
 308   data->suggestions_url_post_params = s.ColumnString(21);
 309   data->instant_url_post_params = s.ColumnString(22);
 310   data->image_url_post_params = s.ColumnString(23);
 311   data->favicon_url = GURL(s.ColumnString(3));
 312   data->originating_url = GURL(s.ColumnString(6));
 313   data->show_in_default_list = s.ColumnBool(10);
 314   data->safe_for_autoreplace = s.ColumnBool(5);
 315   base::SplitString(s.ColumnString(9), ';', &data->input_encodings);
 316   data->id = s.ColumnInt64(0);
 317   data->date_created = Time::FromTimeT(s.ColumnInt64(7));
 318   data->last_modified = Time::FromTimeT(s.ColumnInt64(15));
 319   data->created_by_policy = s.ColumnBool(13);
 320   data->usage_count = s.ColumnInt(8);
 321   data->prepopulate_id = s.ColumnInt(12);
 322   data->sync_guid = s.ColumnString(16);
 323
 324   data->alternate_urls.clear();
 325   base::JSONReader json_reader;
 326   scoped_ptr<base::Value> value(json_reader.ReadToValue(s.ColumnString(17)));
 327   base::ListValue* alternate_urls_value;
 328   if (value.get() && value->GetAsList(&alternate_urls_value)) {
 329     std::string alternate_url;
 330     for (size_t i = 0; i < alternate_urls_value->GetSize(); ++i) {
 331       if (alternate_urls_value->GetString(i, &alternate_url))
 332         data->alternate_urls.push_back(alternate_url);
 333     }
 334   }
 335
 336   data->search_terms_replacement_key = s.ColumnString(18);
 337
 338   return true;
 339 }
 340
 341 bool KeywordTable::AddKeyword(const TemplateURLData& data) {
 342   DCHECK(data.id);
 343   std::string query("INSERT INTO keywords (" + GetKeywordColumns() + ") "
 344                     "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
 345                     "        ?)");
 346   sql::Statement s(db_->GetCachedStatement(SQL_FROM_HERE, query.c_str()));
 347   BindURLToStatement(data, &s, 0, 1);
 348
 349   return s.Run();
 350 }
 351
 352 bool KeywordTable::RemoveKeyword(TemplateURLID id) {
 353   DCHECK(id);
 354   sql::Statement s(db_->GetCachedStatement(
 355       SQL_FROM_HERE, "DELETE FROM keywords WHERE id = ?"));
 356   s.BindInt64(0, id);
 357
 358   return s.Run();
 359 }
 360
 361 bool KeywordTable::UpdateKeyword(const TemplateURLData& data) {
 362   DCHECK(data.id);
 363   sql::Statement s(db_->GetCachedStatement(
 364       SQL_FROM_HERE,
 365       "UPDATE keywords SET short_name=?, keyword=?, favicon_url=?, url=?, "
 366       "safe_for_autoreplace=?, originating_url=?, date_created=?, "
 367       "usage_count=?, input_encodings=?, show_in_default_list=?, "
 368       "suggest_url=?, prepopulate_id=?, created_by_policy=?, instant_url=?, "
 369       "last_modified=?, sync_guid=?, alternate_urls=?, "
 370       "search_terms_replacement_key=?, image_url=?, search_url_post_params=?, "
 371       "suggest_url_post_params=?, instant_url_post_params=?, "
 372       "image_url_post_params=?, new_tab_url=? WHERE id=?"));
 373   BindURLToStatement(data, &s, 24, 0);  // "24" binds id() as the last item.
 374
 375   return s.Run();
 376 }
 377
 378 bool KeywordTable::GetKeywordAsString(TemplateURLID id,
 379                                       const std::string& table_name,
 380                                       std::string* result) {
 381   std::string query("SELECT " +
 382       ColumnsForVersion(WebDatabase::kCurrentVersionNumber, true) +
 383       " FROM " + table_name + " WHERE id=?");
 384   sql::Statement s(db_->GetUniqueStatement(query.c_str()));
 385   s.BindInt64(0, id);
 386
 387   if (!s.Step()) {
 388     LOG_IF(WARNING, s.Succeeded()) << "No keyword with id: " << id
 389                                    << ", ignoring.";
 390     return true;
 391   }
 392
 393   if (!s.Succeeded())
 394     return false;
 395
 396   *result = s.ColumnString(0);
 397   return true;
 398 }
 399
 400 bool KeywordTable::MigrateKeywordsTableForVersion45(const std::string& name) {
 401   // Create a new table without the columns we're dropping.
 402   if (!db_->Execute("CREATE TABLE keywords_temp ("
 403                     "id INTEGER PRIMARY KEY,"
 404                     "short_name VARCHAR NOT NULL,"
 405                     "keyword VARCHAR NOT NULL,"
 406                     "favicon_url VARCHAR NOT NULL,"
 407                     "url VARCHAR NOT NULL,"
 408                     "safe_for_autoreplace INTEGER,"
 409                     "originating_url VARCHAR,"
 410                     "date_created INTEGER DEFAULT 0,"
 411                     "usage_count INTEGER DEFAULT 0,"
 412                     "input_encodings VARCHAR,"
 413                     "show_in_default_list INTEGER,"
 414                     "suggest_url VARCHAR,"
 415                     "prepopulate_id INTEGER DEFAULT 0,"
 416                     "created_by_policy INTEGER DEFAULT 0,"
 417                     "instant_url VARCHAR,"
 418                     "last_modified INTEGER DEFAULT 0,"
 419                     "sync_guid VARCHAR)"))
 420     return false;
 421   std::string sql("INSERT INTO keywords_temp SELECT " +
 422                   ColumnsForVersion(46, false) + " FROM " + name);
 423   if (!db_->Execute(sql.c_str()))
 424     return false;
 425
 426   // NOTE: The ORDER BY here ensures that the uniquing process for keywords will
 427   // happen identically on both the normal and backup tables.
 428   sql = "SELECT id, keyword, url, autogenerate_keyword FROM " + name +
 429       " ORDER BY id ASC";
 430   sql::Statement s(db_->GetUniqueStatement(sql.c_str()));
 431   base::string16 placeholder_keyword(base::ASCIIToUTF16("dummy"));
 432   std::set<base::string16> keywords;
 433   while (s.Step()) {
 434     base::string16 keyword(s.ColumnString16(1));
 435     bool generate_keyword = keyword.empty() || s.ColumnBool(3);
 436     if (generate_keyword)
 437       keyword = placeholder_keyword;
 438     TemplateURLData data;
 439     data.SetKeyword(keyword);
 440     data.SetURL(s.ColumnString(2));
 441     TemplateURL turl(data);
 442     // Don't persist extension keywords to disk.  These will get added to the
 443     // TemplateURLService as the extensions are loaded.
 444     bool delete_entry = turl.GetType() == TemplateURL::OMNIBOX_API_EXTENSION;
 445     if (!delete_entry && generate_keyword) {
 446       // Explicitly generate keywords for all rows with the autogenerate bit set
 447       // or where the keyword is empty.
 448       SearchTermsData terms_data;
 449       GURL url(turl.GenerateSearchURL(terms_data));
 450       if (!url.is_valid()) {
 451         delete_entry = true;
 452       } else {
 453         // Ensure autogenerated keywords are unique.
 454         keyword = TemplateURL::GenerateKeyword(url);
 455         while (keywords.count(keyword))
 456           keyword.append(base::ASCIIToUTF16("_"));
 457         sql::Statement u(db_->GetUniqueStatement(
 458             "UPDATE keywords_temp SET keyword=? WHERE id=?"));
 459         u.BindString16(0, keyword);
 460         u.BindInt64(1, s.ColumnInt64(0));
 461         if (!u.Run())
 462           return false;
 463       }
 464     }
 465     if (delete_entry) {
 466       sql::Statement u(db_->GetUniqueStatement(
 467           "DELETE FROM keywords_temp WHERE id=?"));
 468       u.BindInt64(0, s.ColumnInt64(0));
 469       if (!u.Run())
 470         return false;
 471     } else {
 472       keywords.insert(keyword);
 473     }
 474   }
 475
 476   // Replace the old table with the new one.
 477   sql = "DROP TABLE " + name;
 478   if (!db_->Execute(sql.c_str()))
 479     return false;
 480   sql = "ALTER TABLE keywords_temp RENAME TO " + name;
 481   return db_->Execute(sql.c_str());
 482 }