1 // Copyright 2014 The Chromium Authors
2 // Use of this source code is governed by a BSD-style license that can be
3 // found in the LICENSE file.
5 #include "components/search_engines/keyword_table.h"
13 #include "base/json/json_reader.h"
14 #include "base/json/json_writer.h"
15 #include "base/logging.h"
16 #include "base/strings/string_number_conversions.h"
17 #include "base/strings/string_piece.h"
18 #include "base/strings/string_split.h"
19 #include "base/strings/string_util.h"
20 #include "base/strings/utf_string_conversions.h"
21 #include "base/time/time.h"
22 #include "base/values.h"
23 #include "components/database_utils/url_converter.h"
24 #include "components/search_engines/search_terms_data.h"
25 #include "components/search_engines/template_url.h"
26 #include "components/webdata/common/web_database.h"
27 #include "sql/statement.h"
28 #include "sql/transaction.h"
34 const char KeywordTable::kDefaultSearchProviderKey[] =
35 "Default Search Provider ID";
39 // Keys used in the meta table.
40 const char kBuiltinKeywordVersion[] = "Builtin Keyword Version";
41 const char kStarterPackKeywordVersion[] = "Starter Pack Keyword Version";
43 const std::string ColumnsForVersion(int version, bool concatenated) {
44 std::vector<base::StringPiece> columns;
46 columns.push_back("id");
47 columns.push_back("short_name");
48 columns.push_back("keyword");
49 columns.push_back("favicon_url");
50 columns.push_back("url");
51 columns.push_back("safe_for_autoreplace");
52 columns.push_back("originating_url");
53 columns.push_back("date_created");
54 columns.push_back("usage_count");
55 columns.push_back("input_encodings");
57 // Column removed after version 67.
58 columns.push_back("show_in_default_list");
60 columns.push_back("suggest_url");
61 columns.push_back("prepopulate_id");
63 // Columns removed after version 44.
64 columns.push_back("autogenerate_keyword");
65 columns.push_back("logo_id");
67 columns.push_back("created_by_policy");
69 // Column removed after version 75.
70 columns.push_back("instant_url");
72 columns.push_back("last_modified");
73 columns.push_back("sync_guid");
75 // Column added in version 47.
76 columns.push_back("alternate_urls");
78 if (version >= 49 && version <= 75) {
79 // Column added in version 49 and removed after version 75.
80 columns.push_back("search_terms_replacement_key");
83 // Column added in version 52.
84 columns.push_back("image_url");
85 columns.push_back("search_url_post_params");
86 columns.push_back("suggest_url_post_params");
88 if (version >= 52 && version <= 75) {
89 // Column added in version 52 and removed after version 75.
90 columns.push_back("instant_url_post_params");
93 // Column added in version 52.
94 columns.push_back("image_url_post_params");
97 // Column added in version 53.
98 columns.push_back("new_tab_url");
101 // Column added in version 69.
102 columns.push_back("last_visited");
105 // Column added in version 82.
106 columns.push_back("created_from_play_api");
109 // Column added in version 97.
110 columns.push_back("is_active");
112 if (version >= 103) {
113 // Column added in version 103.
114 columns.push_back("starter_pack_id");
116 if (version >= 112) {
117 // Column added in version 112.
118 columns.push_back("enforced_by_policy");
121 return base::JoinString(columns, std::string(concatenated ? " || " : ", "));
124 // Inserts the data from |data| into |s|. |s| is assumed to have slots for all
125 // the columns in the keyword table. |id_column| is the slot number to bind
126 // |data|'s |id| to; |starting_column| is the slot number of the first of a
127 // contiguous set of slots to bind all the other fields to.
128 void BindURLToStatement(const TemplateURLData& data,
131 int starting_column) {
132 // Serialize |alternate_urls| to JSON.
133 // TODO(beaudoin): Check what it would take to use a new table to store
134 // alternate_urls while keeping backups and table signature in a good state.
135 // See: crbug.com/153520
136 base::Value::List alternate_urls_value;
137 for (size_t i = 0; i < data.alternate_urls.size(); ++i)
138 alternate_urls_value.Append(data.alternate_urls[i]);
139 std::string alternate_urls;
140 base::JSONWriter::Write(alternate_urls_value, &alternate_urls);
142 s->BindInt64(id_column, data.id);
143 s->BindString16(starting_column, data.short_name());
144 s->BindString16(starting_column + 1, data.keyword());
145 s->BindString(starting_column + 2,
146 data.favicon_url.is_valid()
147 ? database_utils::GurlToDatabaseUrl(data.favicon_url)
149 s->BindString(starting_column + 3, data.url());
150 s->BindBool(starting_column + 4, data.safe_for_autoreplace);
151 s->BindString(starting_column + 5,
152 data.originating_url.is_valid()
153 ? database_utils::GurlToDatabaseUrl(data.originating_url)
155 s->BindTime(starting_column + 6, data.date_created);
156 s->BindInt(starting_column + 7, data.usage_count);
157 s->BindString(starting_column + 8,
158 base::JoinString(data.input_encodings, ";"));
159 s->BindString(starting_column + 9, data.suggestions_url);
160 s->BindInt(starting_column + 10, data.prepopulate_id);
161 s->BindBool(starting_column + 11, data.created_by_policy);
162 s->BindTime(starting_column + 12, data.last_modified);
163 s->BindString(starting_column + 13, data.sync_guid);
164 s->BindString(starting_column + 14, alternate_urls);
165 s->BindString(starting_column + 15, data.image_url);
166 s->BindString(starting_column + 16, data.search_url_post_params);
167 s->BindString(starting_column + 17, data.suggestions_url_post_params);
168 s->BindString(starting_column + 18, data.image_url_post_params);
169 s->BindString(starting_column + 19, data.new_tab_url);
170 s->BindTime(starting_column + 20, data.last_visited);
171 s->BindBool(starting_column + 21, data.created_from_play_api);
172 s->BindInt(starting_column + 22, static_cast<int>(data.is_active));
173 s->BindInt(starting_column + 23, data.starter_pack_id);
174 s->BindBool(starting_column + 24, data.enforced_by_policy);
177 WebDatabaseTable::TypeKey GetKey() {
178 // We just need a unique constant. Use the address of a static that
179 // COMDAT folding won't touch in an optimizing linker.
180 static int table_key = 0;
181 return reinterpret_cast<void*>(&table_key);
186 KeywordTable::KeywordTable() {
189 KeywordTable::~KeywordTable() {}
191 KeywordTable* KeywordTable::FromWebDatabase(WebDatabase* db) {
192 return static_cast<KeywordTable*>(db->GetTable(GetKey()));
195 WebDatabaseTable::TypeKey KeywordTable::GetTypeKey() const {
199 bool KeywordTable::CreateTablesIfNecessary() {
200 return db_->DoesTableExist("keywords") ||
202 "CREATE TABLE keywords ("
203 "id INTEGER PRIMARY KEY,"
204 "short_name VARCHAR NOT NULL,"
205 "keyword VARCHAR NOT NULL,"
206 "favicon_url VARCHAR NOT NULL,"
207 "url VARCHAR NOT NULL,"
208 "safe_for_autoreplace INTEGER,"
209 "originating_url VARCHAR,"
210 "date_created INTEGER DEFAULT 0,"
211 "usage_count INTEGER DEFAULT 0,"
212 "input_encodings VARCHAR,"
213 "suggest_url VARCHAR,"
214 "prepopulate_id INTEGER DEFAULT 0,"
215 "created_by_policy INTEGER DEFAULT 0,"
216 "last_modified INTEGER DEFAULT 0,"
218 "alternate_urls VARCHAR,"
220 "search_url_post_params VARCHAR,"
221 "suggest_url_post_params VARCHAR,"
222 "image_url_post_params VARCHAR,"
223 "new_tab_url VARCHAR,"
224 "last_visited INTEGER DEFAULT 0, "
225 "created_from_play_api INTEGER DEFAULT 0, "
226 "is_active INTEGER DEFAULT 0, "
227 "starter_pack_id INTEGER DEFAULT 0, "
228 "enforced_by_policy INTEGER DEFAULT 0)");
231 bool KeywordTable::MigrateToVersion(int version,
232 bool* update_compatible_version) {
233 // Migrate if necessary.
236 *update_compatible_version = true;
237 return MigrateToVersion53AddNewTabURLColumn();
239 *update_compatible_version = true;
240 return MigrateToVersion59RemoveExtensionKeywords();
242 *update_compatible_version = true;
243 return MigrateToVersion68RemoveShowInDefaultListColumn();
245 return MigrateToVersion69AddLastVisitedColumn();
247 *update_compatible_version = true;
248 return MigrateToVersion76RemoveInstantColumns();
250 *update_compatible_version = true;
251 return MigrateToVersion77IncreaseTimePrecision();
253 return MigrateToVersion82AddCreatedFromPlayApiColumn();
255 return MigrateToVersion97AddIsActiveColumn();
257 return MigrateToVersion103AddStarterPackIdColumn();
259 return MigrateToVersion112AddEnforcedByPolicyColumn();
265 bool KeywordTable::PerformOperations(const Operations& operations) {
266 sql::Transaction transaction(db_);
267 if (!transaction.Begin())
270 for (auto i(operations.begin()); i != operations.end(); ++i) {
273 if (!AddKeyword(i->second))
278 if (!RemoveKeyword(i->second.id))
283 if (!UpdateKeyword(i->second))
289 return transaction.Commit();
292 bool KeywordTable::GetKeywords(Keywords* keywords) {
293 std::string query("SELECT " + GetKeywordColumns() +
294 " FROM keywords ORDER BY id ASC");
295 sql::Statement s(db_->GetUniqueStatement(query.c_str()));
297 std::set<TemplateURLID> bad_entries;
299 keywords->push_back(TemplateURLData());
300 if (!GetKeywordDataFromStatement(s, &keywords->back())) {
301 bad_entries.insert(s.ColumnInt64(0));
302 keywords->pop_back();
305 bool succeeded = s.Succeeded();
306 for (auto i(bad_entries.begin()); i != bad_entries.end(); ++i)
307 succeeded &= RemoveKeyword(*i);
311 bool KeywordTable::SetDefaultSearchProviderID(int64_t id) {
312 return meta_table_->SetValue(kDefaultSearchProviderKey, id);
315 int64_t KeywordTable::GetDefaultSearchProviderID() {
316 int64_t value = kInvalidTemplateURLID;
317 meta_table_->GetValue(kDefaultSearchProviderKey, &value);
321 bool KeywordTable::SetBuiltinKeywordVersion(int version) {
322 return meta_table_->SetValue(kBuiltinKeywordVersion, version);
325 int KeywordTable::GetBuiltinKeywordVersion() {
327 return meta_table_->GetValue(kBuiltinKeywordVersion, &version) ? version : 0;
330 bool KeywordTable::SetStarterPackKeywordVersion(int version) {
331 return meta_table_->SetValue(kStarterPackKeywordVersion, version);
334 int KeywordTable::GetStarterPackKeywordVersion() {
336 return meta_table_->GetValue(kStarterPackKeywordVersion, &version) ? version
341 std::string KeywordTable::GetKeywordColumns() {
342 return ColumnsForVersion(WebDatabase::kCurrentVersionNumber, false);
345 bool KeywordTable::MigrateToVersion53AddNewTabURLColumn() {
346 return db_->Execute("ALTER TABLE keywords ADD COLUMN new_tab_url "
347 "VARCHAR DEFAULT ''");
350 bool KeywordTable::MigrateToVersion59RemoveExtensionKeywords() {
351 return db_->Execute("DELETE FROM keywords "
352 "WHERE url LIKE 'chrome-extension://%'");
355 // SQLite does not support DROP COLUMN operation. So A new table is created
356 // without the show_in_default_list column. Data from all but the dropped column
357 // of the old table is copied into it. After that, the old table is dropped and
358 // the new table is renamed to it.
359 bool KeywordTable::MigrateToVersion68RemoveShowInDefaultListColumn() {
360 sql::Transaction transaction(db_);
361 std::string query_str =
362 std::string("INSERT INTO temp_keywords SELECT " +
363 ColumnsForVersion(68, false) + " FROM keywords");
364 const char* clone_query = query_str.c_str();
365 return transaction.Begin() &&
367 "CREATE TABLE temp_keywords ("
368 "id INTEGER PRIMARY KEY,"
369 "short_name VARCHAR NOT NULL,"
370 "keyword VARCHAR NOT NULL,"
371 "favicon_url VARCHAR NOT NULL,"
372 "url VARCHAR NOT NULL,"
373 "safe_for_autoreplace INTEGER,"
374 "originating_url VARCHAR,"
375 "date_created INTEGER DEFAULT 0,"
376 "usage_count INTEGER DEFAULT 0,"
377 "input_encodings VARCHAR,"
378 "suggest_url VARCHAR,"
379 "prepopulate_id INTEGER DEFAULT 0,"
380 "created_by_policy INTEGER DEFAULT 0,"
381 "instant_url VARCHAR,"
382 "last_modified INTEGER DEFAULT 0,"
384 "alternate_urls VARCHAR,"
385 "search_terms_replacement_key VARCHAR,"
387 "search_url_post_params VARCHAR,"
388 "suggest_url_post_params VARCHAR,"
389 "instant_url_post_params VARCHAR,"
390 "image_url_post_params VARCHAR,"
391 "new_tab_url VARCHAR)") &&
392 db_->Execute(clone_query) && db_->Execute("DROP TABLE keywords") &&
393 db_->Execute("ALTER TABLE temp_keywords RENAME TO keywords") &&
394 transaction.Commit();
397 bool KeywordTable::MigrateToVersion69AddLastVisitedColumn() {
398 return db_->Execute("ALTER TABLE keywords ADD COLUMN last_visited "
399 "INTEGER DEFAULT 0");
402 // SQLite does not support DROP COLUMN operation. So a new table is created
403 // without the removed columns. Data from all but the dropped columns of the old
404 // table is copied into it. After that, the old table is dropped and the new
405 // table is renamed to it.
406 bool KeywordTable::MigrateToVersion76RemoveInstantColumns() {
407 sql::Transaction transaction(db_);
408 std::string query_str =
409 std::string("INSERT INTO temp_keywords SELECT " +
410 ColumnsForVersion(76, false) + " FROM keywords");
411 const char* clone_query = query_str.c_str();
412 return transaction.Begin() &&
414 "CREATE TABLE temp_keywords ("
415 "id INTEGER PRIMARY KEY,"
416 "short_name VARCHAR NOT NULL,"
417 "keyword VARCHAR NOT NULL,"
418 "favicon_url VARCHAR NOT NULL,"
419 "url VARCHAR NOT NULL,"
420 "safe_for_autoreplace INTEGER,"
421 "originating_url VARCHAR,"
422 "date_created INTEGER DEFAULT 0,"
423 "usage_count INTEGER DEFAULT 0,"
424 "input_encodings VARCHAR,"
425 "suggest_url VARCHAR,"
426 "prepopulate_id INTEGER DEFAULT 0,"
427 "created_by_policy INTEGER DEFAULT 0,"
428 "last_modified INTEGER DEFAULT 0,"
430 "alternate_urls VARCHAR,"
432 "search_url_post_params VARCHAR,"
433 "suggest_url_post_params VARCHAR,"
434 "image_url_post_params VARCHAR,"
435 "new_tab_url VARCHAR,"
436 "last_visited INTEGER DEFAULT 0)") &&
437 db_->Execute(clone_query) && db_->Execute("DROP TABLE keywords") &&
438 db_->Execute("ALTER TABLE temp_keywords RENAME TO keywords") &&
439 transaction.Commit();
442 bool KeywordTable::MigrateToVersion77IncreaseTimePrecision() {
443 sql::Transaction transaction(db_);
444 if (!transaction.Begin())
448 "SELECT id, date_created, last_modified, last_visited FROM keywords");
449 sql::Statement s(db_->GetUniqueStatement(query.c_str()));
450 std::vector<std::tuple<TemplateURLID, Time, Time, Time>> updates;
452 updates.emplace_back(std::make_tuple(s.ColumnInt64(0), s.ColumnTime(1),
453 s.ColumnTime(2), s.ColumnTime(3)));
458 for (auto tuple : updates) {
459 sql::Statement update_statement(db_->GetCachedStatement(
461 "UPDATE keywords SET date_created = ?, last_modified = ?, last_visited "
462 "= ? WHERE id = ? "));
463 update_statement.BindTime(0, std::get<1>(tuple));
464 update_statement.BindTime(1, std::get<2>(tuple));
465 update_statement.BindTime(2, std::get<3>(tuple));
466 update_statement.BindInt64(3, std::get<0>(tuple));
467 if (!update_statement.Run()) {
471 return transaction.Commit();
474 bool KeywordTable::MigrateToVersion82AddCreatedFromPlayApiColumn() {
476 "ALTER TABLE keywords ADD COLUMN created_from_play_api INTEGER DEFAULT "
480 bool KeywordTable::MigrateToVersion97AddIsActiveColumn() {
482 "ALTER TABLE keywords ADD COLUMN is_active INTEGER DEFAULT 0");
485 bool KeywordTable::MigrateToVersion103AddStarterPackIdColumn() {
487 "ALTER TABLE keywords ADD COLUMN starter_pack_id INTEGER DEFAULT 0");
490 bool KeywordTable::MigrateToVersion112AddEnforcedByPolicyColumn() {
492 "ALTER TABLE keywords ADD COLUMN enforced_by_policy INTEGER DEFAULT 0");
496 bool KeywordTable::GetKeywordDataFromStatement(sql::Statement& s,
497 TemplateURLData* data) {
500 data->SetShortName(s.ColumnString16(1));
501 data->SetKeyword(s.ColumnString16(2));
502 // Due to past bugs, we might have persisted entries with empty URLs. Avoid
503 // reading these out. (GetKeywords() will delete these entries on return.)
504 // NOTE: This code should only be needed as long as we might be reading such
505 // potentially-old data and can be removed afterward.
506 if (s.ColumnString(4).empty())
508 data->SetURL(s.ColumnString(4));
509 data->suggestions_url = s.ColumnString(10);
510 data->image_url = s.ColumnString(16);
511 data->new_tab_url = s.ColumnString(20);
512 data->search_url_post_params = s.ColumnString(17);
513 data->suggestions_url_post_params = s.ColumnString(18);
514 data->image_url_post_params = s.ColumnString(19);
515 data->favicon_url = GURL(s.ColumnString(3));
516 data->originating_url = GURL(s.ColumnString(6));
517 data->safe_for_autoreplace = s.ColumnBool(5);
518 data->input_encodings = base::SplitString(
519 s.ColumnString(9), ";", base::TRIM_WHITESPACE, base::SPLIT_WANT_ALL);
520 data->id = s.ColumnInt64(0);
521 data->date_created = s.ColumnTime(7);
522 data->last_modified = s.ColumnTime(13);
523 data->created_by_policy = s.ColumnBool(12);
524 data->created_from_play_api = s.ColumnBool(22);
525 data->usage_count = s.ColumnInt(8);
526 data->prepopulate_id = s.ColumnInt(11);
527 data->sync_guid = s.ColumnString(14);
528 data->is_active = static_cast<TemplateURLData::ActiveStatus>(s.ColumnInt(23));
529 data->starter_pack_id = s.ColumnInt(24);
530 data->enforced_by_policy = s.ColumnBool(25);
532 data->alternate_urls.clear();
533 absl::optional<base::Value> value(base::JSONReader::Read(s.ColumnString(15)));
534 if (value && value->is_list()) {
535 for (const base::Value& alternate_url : value->GetList()) {
536 if (alternate_url.is_string()) {
537 data->alternate_urls.push_back(alternate_url.GetString());
542 data->last_visited = s.ColumnTime(21);
547 bool KeywordTable::AddKeyword(const TemplateURLData& data) {
550 "INSERT INTO keywords (" + GetKeywordColumns() +
552 "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
553 sql::Statement s(db_->GetCachedStatement(SQL_FROM_HERE, query.c_str()));
554 BindURLToStatement(data, &s, 0, 1);
559 bool KeywordTable::RemoveKeyword(TemplateURLID id) {
561 sql::Statement s(db_->GetCachedStatement(
562 SQL_FROM_HERE, "DELETE FROM keywords WHERE id = ?"));
568 bool KeywordTable::UpdateKeyword(const TemplateURLData& data) {
570 sql::Statement s(db_->GetCachedStatement(
572 "UPDATE keywords SET short_name=?, keyword=?, favicon_url=?, url=?, "
573 "safe_for_autoreplace=?, originating_url=?, date_created=?, "
574 "usage_count=?, input_encodings=?, suggest_url=?, prepopulate_id=?, "
575 "created_by_policy=?, last_modified=?, sync_guid=?, alternate_urls=?, "
576 "image_url=?, search_url_post_params=?, suggest_url_post_params=?, "
577 "image_url_post_params=?, new_tab_url=?, last_visited=?, "
578 "created_from_play_api=?, is_active=?, starter_pack_id=?, "
579 "enforced_by_policy=? WHERE id=?"));
580 BindURLToStatement(data, &s, 25, 0); // "25" binds id() as the last item.
585 bool KeywordTable::GetKeywordAsString(TemplateURLID id,
586 const std::string& table_name,
587 std::string* result) {
588 std::string query("SELECT " +
589 ColumnsForVersion(WebDatabase::kCurrentVersionNumber, true) +
590 " FROM " + table_name + " WHERE id=?");
591 sql::Statement s(db_->GetUniqueStatement(query.c_str()));
595 LOG_IF(WARNING, s.Succeeded()) << "No keyword with id: " << id
603 *result = s.ColumnString(0);