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
26using base::Time;
27
28// static
29const char KeywordTable::kDefaultSearchProviderKey[] =
30    "Default Search Provider ID";
31
32namespace {
33
34// Keys used in the meta table.
35const char kBuiltinKeywordVersion[] = "Builtin Keyword Version";
36
37const 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.
91void 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
136WebDatabaseTable::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
145KeywordTable::KeywordTable() {
146}
147
148KeywordTable::~KeywordTable() {}
149
150KeywordTable* KeywordTable::FromWebDatabase(WebDatabase* db) {
151  return static_cast<KeywordTable*>(db->GetTable(GetKey()));
152}
153
154WebDatabaseTable::TypeKey KeywordTable::GetTypeKey() const {
155  return GetKey();
156}
157
158bool 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
188bool KeywordTable::IsSyncable() {
189  return true;
190}
191
192bool KeywordTable::MigrateToVersion(int version,
193                                    bool* update_compatible_version) {
194  // Migrate if necessary.
195  switch (version) {
196    case 21:
197      *update_compatible_version = true;
198      return MigrateToVersion21AutoGenerateKeywordColumn();
199    case 25:
200      *update_compatible_version = true;
201      return MigrateToVersion25AddLogoIDColumn();
202    case 26:
203      *update_compatible_version = true;
204      return MigrateToVersion26AddCreatedByPolicyColumn();
205    case 28:
206      *update_compatible_version = true;
207      return MigrateToVersion28SupportsInstantColumn();
208    case 29:
209      *update_compatible_version = true;
210      return MigrateToVersion29InstantURLToSupportsInstant();
211    case 38:
212      *update_compatible_version = true;
213      return MigrateToVersion38AddLastModifiedColumn();
214    case 39:
215      *update_compatible_version = true;
216      return MigrateToVersion39AddSyncGUIDColumn();
217    case 44:
218      *update_compatible_version = true;
219      return MigrateToVersion44AddDefaultSearchProviderBackup();
220    case 45:
221      *update_compatible_version = true;
222      return MigrateToVersion45RemoveLogoIDAndAutogenerateColumns();
223    case 47:
224      *update_compatible_version = true;
225      return MigrateToVersion47AddAlternateURLsColumn();
226    case 48:
227      *update_compatible_version = true;
228      return MigrateToVersion48RemoveKeywordsBackup();
229    case 49:
230      *update_compatible_version = true;
231      return MigrateToVersion49AddSearchTermsReplacementKeyColumn();
232    case 52:
233      *update_compatible_version = true;
234      return MigrateToVersion52AddImageSearchAndPOSTSupport();
235    case 53:
236      *update_compatible_version = true;
237      return MigrateToVersion53AddNewTabURLColumn();
238  }
239
240  return true;
241}
242
243bool KeywordTable::PerformOperations(const Operations& operations) {
244  sql::Transaction transaction(db_);
245  if (!transaction.Begin())
246    return false;
247
248  for (Operations::const_iterator i(operations.begin()); i != operations.end();
249       ++i) {
250    switch (i->first) {
251      case ADD:
252        if (!AddKeyword(i->second))
253          return false;
254        break;
255
256      case REMOVE:
257        if (!RemoveKeyword(i->second.id))
258          return false;
259        break;
260
261      case UPDATE:
262        if (!UpdateKeyword(i->second))
263          return false;
264        break;
265    }
266  }
267
268  return transaction.Commit();
269}
270
271bool KeywordTable::GetKeywords(Keywords* keywords) {
272  std::string query("SELECT " + GetKeywordColumns() +
273                    " FROM keywords ORDER BY id ASC");
274  sql::Statement s(db_->GetUniqueStatement(query.c_str()));
275
276  std::set<TemplateURLID> bad_entries;
277  while (s.Step()) {
278    keywords->push_back(TemplateURLData());
279    if (!GetKeywordDataFromStatement(s, &keywords->back())) {
280      bad_entries.insert(s.ColumnInt64(0));
281      keywords->pop_back();
282    }
283  }
284  bool succeeded = s.Succeeded();
285  for (std::set<TemplateURLID>::const_iterator i(bad_entries.begin());
286       i != bad_entries.end(); ++i)
287    succeeded &= RemoveKeyword(*i);
288  return succeeded;
289}
290
291bool KeywordTable::SetDefaultSearchProviderID(int64 id) {
292  return meta_table_->SetValue(kDefaultSearchProviderKey, id);
293}
294
295int64 KeywordTable::GetDefaultSearchProviderID() {
296  int64 value = kInvalidTemplateURLID;
297  meta_table_->GetValue(kDefaultSearchProviderKey, &value);
298  return value;
299}
300
301bool KeywordTable::SetBuiltinKeywordVersion(int version) {
302  return meta_table_->SetValue(kBuiltinKeywordVersion, version);
303}
304
305int KeywordTable::GetBuiltinKeywordVersion() {
306  int version = 0;
307  return meta_table_->GetValue(kBuiltinKeywordVersion, &version) ? version : 0;
308}
309
310// static
311std::string KeywordTable::GetKeywordColumns() {
312  return ColumnsForVersion(WebDatabase::kCurrentVersionNumber, false);
313}
314
315bool KeywordTable::MigrateToVersion21AutoGenerateKeywordColumn() {
316  return db_->Execute("ALTER TABLE keywords ADD COLUMN autogenerate_keyword "
317                      "INTEGER DEFAULT 0");
318}
319
320bool KeywordTable::MigrateToVersion25AddLogoIDColumn() {
321  return db_->Execute(
322      "ALTER TABLE keywords ADD COLUMN logo_id INTEGER DEFAULT 0");
323}
324
325bool KeywordTable::MigrateToVersion26AddCreatedByPolicyColumn() {
326  return db_->Execute("ALTER TABLE keywords ADD COLUMN created_by_policy "
327                      "INTEGER DEFAULT 0");
328}
329
330bool KeywordTable::MigrateToVersion28SupportsInstantColumn() {
331  return db_->Execute("ALTER TABLE keywords ADD COLUMN supports_instant "
332                      "INTEGER DEFAULT 0");
333}
334
335bool KeywordTable::MigrateToVersion29InstantURLToSupportsInstant() {
336  sql::Transaction transaction(db_);
337  return transaction.Begin() &&
338      db_->Execute("ALTER TABLE keywords ADD COLUMN instant_url VARCHAR") &&
339      db_->Execute("CREATE TABLE keywords_temp ("
340                   "id INTEGER PRIMARY KEY,"
341                   "short_name VARCHAR NOT NULL,"
342                   "keyword VARCHAR NOT NULL,"
343                   "favicon_url VARCHAR NOT NULL,"
344                   "url VARCHAR NOT NULL,"
345                   "safe_for_autoreplace INTEGER,"
346                   "originating_url VARCHAR,"
347                   "date_created INTEGER DEFAULT 0,"
348                   "usage_count INTEGER DEFAULT 0,"
349                   "input_encodings VARCHAR,"
350                   "show_in_default_list INTEGER,"
351                   "suggest_url VARCHAR,"
352                   "prepopulate_id INTEGER DEFAULT 0,"
353                   "autogenerate_keyword INTEGER DEFAULT 0,"
354                   "logo_id INTEGER DEFAULT 0,"
355                   "created_by_policy INTEGER DEFAULT 0,"
356                   "instant_url VARCHAR)") &&
357      db_->Execute("INSERT INTO keywords_temp SELECT id, short_name, keyword, "
358                   "favicon_url, url, safe_for_autoreplace, originating_url, "
359                   "date_created, usage_count, input_encodings, "
360                   "show_in_default_list, suggest_url, prepopulate_id, "
361                   "autogenerate_keyword, logo_id, created_by_policy, "
362                   "instant_url FROM keywords") &&
363      db_->Execute("DROP TABLE keywords") &&
364      db_->Execute("ALTER TABLE keywords_temp RENAME TO keywords") &&
365      transaction.Commit();
366}
367
368bool KeywordTable::MigrateToVersion38AddLastModifiedColumn() {
369  return db_->Execute(
370      "ALTER TABLE keywords ADD COLUMN last_modified INTEGER DEFAULT 0");
371}
372
373bool KeywordTable::MigrateToVersion39AddSyncGUIDColumn() {
374  return db_->Execute("ALTER TABLE keywords ADD COLUMN sync_guid VARCHAR");
375}
376
377bool KeywordTable::MigrateToVersion44AddDefaultSearchProviderBackup() {
378  std::string query("CREATE TABLE keywords_backup AS SELECT " +
379      ColumnsForVersion(44, false) + " FROM keywords ORDER BY id ASC");
380  sql::Transaction transaction(db_);
381  return transaction.Begin() &&
382      meta_table_->SetValue("Default Search Provider ID Backup",
383                            GetDefaultSearchProviderID()) &&
384      (!db_->DoesTableExist("keywords_backup") ||
385       db_->Execute("DROP TABLE keywords_backup")) &&
386      db_->Execute(query.c_str()) &&
387      transaction.Commit();
388}
389
390bool KeywordTable::MigrateToVersion45RemoveLogoIDAndAutogenerateColumns() {
391  sql::Transaction transaction(db_);
392  if (!transaction.Begin())
393    return false;
394
395  // The version 43 migration should have been written to do this, but since it
396  // wasn't, we'll do it now.  Unfortunately a previous change deleted this for
397  // some users, so we can't be sure this will succeed (so don't bail on error).
398  meta_table_->DeleteKey("Default Search Provider Backup");
399
400  return MigrateKeywordsTableForVersion45("keywords") &&
401      MigrateKeywordsTableForVersion45("keywords_backup") &&
402      meta_table_->SetValue("Default Search Provider ID Backup Signature",
403                            std::string()) &&
404      transaction.Commit();
405}
406
407bool KeywordTable::MigrateToVersion47AddAlternateURLsColumn() {
408  sql::Transaction transaction(db_);
409  return transaction.Begin() &&
410      db_->Execute("ALTER TABLE keywords ADD COLUMN "
411                   "alternate_urls VARCHAR DEFAULT ''") &&
412      db_->Execute("ALTER TABLE keywords_backup ADD COLUMN "
413                   "alternate_urls VARCHAR DEFAULT ''") &&
414      meta_table_->SetValue("Default Search Provider ID Backup Signature",
415                            std::string()) &&
416      transaction.Commit();
417}
418
419bool KeywordTable::MigrateToVersion48RemoveKeywordsBackup() {
420  sql::Transaction transaction(db_);
421  return transaction.Begin() &&
422      meta_table_->DeleteKey("Default Search Provider ID Backup") &&
423      meta_table_->DeleteKey("Default Search Provider ID Backup Signature") &&
424      db_->Execute("DROP TABLE keywords_backup") &&
425      transaction.Commit();
426}
427
428bool KeywordTable::MigrateToVersion49AddSearchTermsReplacementKeyColumn() {
429  return db_->Execute("ALTER TABLE keywords ADD COLUMN "
430                      "search_terms_replacement_key VARCHAR DEFAULT ''");
431}
432
433bool KeywordTable::MigrateToVersion52AddImageSearchAndPOSTSupport() {
434  sql::Transaction transaction(db_);
435  return transaction.Begin() &&
436      db_->Execute("ALTER TABLE keywords ADD COLUMN image_url "
437                   "VARCHAR DEFAULT ''") &&
438      db_->Execute("ALTER TABLE keywords ADD COLUMN search_url_post_params "
439                   "VARCHAR DEFAULT ''") &&
440      db_->Execute("ALTER TABLE keywords ADD COLUMN suggest_url_post_params "
441                   "VARCHAR DEFAULT ''") &&
442      db_->Execute("ALTER TABLE keywords ADD COLUMN instant_url_post_params "
443                   "VARCHAR DEFAULT ''") &&
444      db_->Execute("ALTER TABLE keywords ADD COLUMN image_url_post_params "
445                   "VARCHAR DEFAULT ''") &&
446      transaction.Commit();
447}
448
449bool KeywordTable::MigrateToVersion53AddNewTabURLColumn() {
450  return db_->Execute("ALTER TABLE keywords ADD COLUMN new_tab_url "
451                      "VARCHAR DEFAULT ''");
452}
453
454// static
455bool KeywordTable::GetKeywordDataFromStatement(const sql::Statement& s,
456                                               TemplateURLData* data) {
457  DCHECK(data);
458
459  data->short_name = s.ColumnString16(1);
460  data->SetKeyword(s.ColumnString16(2));
461  // Due to past bugs, we might have persisted entries with empty URLs.  Avoid
462  // reading these out.  (GetKeywords() will delete these entries on return.)
463  // NOTE: This code should only be needed as long as we might be reading such
464  // potentially-old data and can be removed afterward.
465  if (s.ColumnString(4).empty())
466    return false;
467  data->SetURL(s.ColumnString(4));
468  data->suggestions_url = s.ColumnString(11);
469  data->instant_url = s.ColumnString(14);
470  data->image_url = s.ColumnString(19);
471  data->new_tab_url = s.ColumnString(24);
472  data->search_url_post_params = s.ColumnString(20);
473  data->suggestions_url_post_params = s.ColumnString(21);
474  data->instant_url_post_params = s.ColumnString(22);
475  data->image_url_post_params = s.ColumnString(23);
476  data->favicon_url = GURL(s.ColumnString(3));
477  data->originating_url = GURL(s.ColumnString(6));
478  data->show_in_default_list = s.ColumnBool(10);
479  data->safe_for_autoreplace = s.ColumnBool(5);
480  base::SplitString(s.ColumnString(9), ';', &data->input_encodings);
481  data->id = s.ColumnInt64(0);
482  data->date_created = Time::FromTimeT(s.ColumnInt64(7));
483  data->last_modified = Time::FromTimeT(s.ColumnInt64(15));
484  data->created_by_policy = s.ColumnBool(13);
485  data->usage_count = s.ColumnInt(8);
486  data->prepopulate_id = s.ColumnInt(12);
487  data->sync_guid = s.ColumnString(16);
488
489  data->alternate_urls.clear();
490  base::JSONReader json_reader;
491  scoped_ptr<base::Value> value(json_reader.ReadToValue(s.ColumnString(17)));
492  base::ListValue* alternate_urls_value;
493  if (value.get() && value->GetAsList(&alternate_urls_value)) {
494    std::string alternate_url;
495    for (size_t i = 0; i < alternate_urls_value->GetSize(); ++i) {
496      if (alternate_urls_value->GetString(i, &alternate_url))
497        data->alternate_urls.push_back(alternate_url);
498    }
499  }
500
501  data->search_terms_replacement_key = s.ColumnString(18);
502
503  return true;
504}
505
506bool KeywordTable::AddKeyword(const TemplateURLData& data) {
507  DCHECK(data.id);
508  std::string query("INSERT INTO keywords (" + GetKeywordColumns() + ") "
509                    "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
510                    "        ?)");
511  sql::Statement s(db_->GetCachedStatement(SQL_FROM_HERE, query.c_str()));
512  BindURLToStatement(data, &s, 0, 1);
513
514  return s.Run();
515}
516
517bool KeywordTable::RemoveKeyword(TemplateURLID id) {
518  DCHECK(id);
519  sql::Statement s(db_->GetCachedStatement(
520      SQL_FROM_HERE, "DELETE FROM keywords WHERE id = ?"));
521  s.BindInt64(0, id);
522
523  return s.Run();
524}
525
526bool KeywordTable::UpdateKeyword(const TemplateURLData& data) {
527  DCHECK(data.id);
528  sql::Statement s(db_->GetCachedStatement(
529      SQL_FROM_HERE,
530      "UPDATE keywords SET short_name=?, keyword=?, favicon_url=?, url=?, "
531      "safe_for_autoreplace=?, originating_url=?, date_created=?, "
532      "usage_count=?, input_encodings=?, show_in_default_list=?, "
533      "suggest_url=?, prepopulate_id=?, created_by_policy=?, instant_url=?, "
534      "last_modified=?, sync_guid=?, alternate_urls=?, "
535      "search_terms_replacement_key=?, image_url=?, search_url_post_params=?, "
536      "suggest_url_post_params=?, instant_url_post_params=?, "
537      "image_url_post_params=?, new_tab_url=? WHERE id=?"));
538  BindURLToStatement(data, &s, 24, 0);  // "24" binds id() as the last item.
539
540  return s.Run();
541}
542
543bool KeywordTable::GetKeywordAsString(TemplateURLID id,
544                                      const std::string& table_name,
545                                      std::string* result) {
546  std::string query("SELECT " +
547      ColumnsForVersion(WebDatabase::kCurrentVersionNumber, true) +
548      " FROM " + table_name + " WHERE id=?");
549  sql::Statement s(db_->GetUniqueStatement(query.c_str()));
550  s.BindInt64(0, id);
551
552  if (!s.Step()) {
553    LOG_IF(WARNING, s.Succeeded()) << "No keyword with id: " << id
554                                   << ", ignoring.";
555    return true;
556  }
557
558  if (!s.Succeeded())
559    return false;
560
561  *result = s.ColumnString(0);
562  return true;
563}
564
565bool KeywordTable::MigrateKeywordsTableForVersion45(const std::string& name) {
566  // Create a new table without the columns we're dropping.
567  if (!db_->Execute("CREATE TABLE keywords_temp ("
568                    "id INTEGER PRIMARY KEY,"
569                    "short_name VARCHAR NOT NULL,"
570                    "keyword VARCHAR NOT NULL,"
571                    "favicon_url VARCHAR NOT NULL,"
572                    "url VARCHAR NOT NULL,"
573                    "safe_for_autoreplace INTEGER,"
574                    "originating_url VARCHAR,"
575                    "date_created INTEGER DEFAULT 0,"
576                    "usage_count INTEGER DEFAULT 0,"
577                    "input_encodings VARCHAR,"
578                    "show_in_default_list INTEGER,"
579                    "suggest_url VARCHAR,"
580                    "prepopulate_id INTEGER DEFAULT 0,"
581                    "created_by_policy INTEGER DEFAULT 0,"
582                    "instant_url VARCHAR,"
583                    "last_modified INTEGER DEFAULT 0,"
584                    "sync_guid VARCHAR)"))
585    return false;
586  std::string sql("INSERT INTO keywords_temp SELECT " +
587                  ColumnsForVersion(46, false) + " FROM " + name);
588  if (!db_->Execute(sql.c_str()))
589    return false;
590
591  // NOTE: The ORDER BY here ensures that the uniquing process for keywords will
592  // happen identically on both the normal and backup tables.
593  sql = "SELECT id, keyword, url, autogenerate_keyword FROM " + name +
594      " ORDER BY id ASC";
595  sql::Statement s(db_->GetUniqueStatement(sql.c_str()));
596  base::string16 placeholder_keyword(base::ASCIIToUTF16("dummy"));
597  std::set<base::string16> keywords;
598  while (s.Step()) {
599    base::string16 keyword(s.ColumnString16(1));
600    bool generate_keyword = keyword.empty() || s.ColumnBool(3);
601    if (generate_keyword)
602      keyword = placeholder_keyword;
603    TemplateURLData data;
604    data.SetKeyword(keyword);
605    data.SetURL(s.ColumnString(2));
606    TemplateURL turl(data);
607    // Don't persist extension keywords to disk.  These will get added to the
608    // TemplateURLService as the extensions are loaded.
609    bool delete_entry = turl.GetType() == TemplateURL::OMNIBOX_API_EXTENSION;
610    if (!delete_entry && generate_keyword) {
611      // Explicitly generate keywords for all rows with the autogenerate bit set
612      // or where the keyword is empty.
613      SearchTermsData terms_data;
614      GURL url(turl.GenerateSearchURL(terms_data));
615      if (!url.is_valid()) {
616        delete_entry = true;
617      } else {
618        // Ensure autogenerated keywords are unique.
619        keyword = TemplateURL::GenerateKeyword(url);
620        while (keywords.count(keyword))
621          keyword.append(base::ASCIIToUTF16("_"));
622        sql::Statement u(db_->GetUniqueStatement(
623            "UPDATE keywords_temp SET keyword=? WHERE id=?"));
624        u.BindString16(0, keyword);
625        u.BindInt64(1, s.ColumnInt64(0));
626        if (!u.Run())
627          return false;
628      }
629    }
630    if (delete_entry) {
631      sql::Statement u(db_->GetUniqueStatement(
632          "DELETE FROM keywords_temp WHERE id=?"));
633      u.BindInt64(0, s.ColumnInt64(0));
634      if (!u.Run())
635        return false;
636    } else {
637      keywords.insert(keyword);
638    }
639  }
640
641  // Replace the old table with the new one.
642  sql = "DROP TABLE " + name;
643  if (!db_->Execute(sql.c_str()))
644    return false;
645  sql = "ALTER TABLE keywords_temp RENAME TO " + name;
646  return db_->Execute(sql.c_str());
647}
648