1// Copyright (c) 2011 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 "chrome/browser/webdata/keyword_table.h"
6
7#include "app/sql/statement.h"
8#include "base/logging.h"
9#include "base/string_split.h"
10#include "base/string_util.h"
11#include "base/utf_string_conversions.h"
12#include "chrome/browser/history/history_database.h"
13#include "chrome/browser/search_engines/template_url.h"
14#include "googleurl/src/gurl.h"
15
16using base::Time;
17
18namespace {
19
20// ID of the url column in keywords.
21const int kUrlIdPosition = 16;
22
23// Keys used in the meta table.
24const char* kDefaultSearchProviderKey = "Default Search Provider ID";
25const char* kBuiltinKeywordVersion = "Builtin Keyword Version";
26
27void BindURLToStatement(const TemplateURL& url, sql::Statement* s) {
28  s->BindString(0, UTF16ToUTF8(url.short_name()));
29  s->BindString(1, UTF16ToUTF8(url.keyword()));
30  GURL favicon_url = url.GetFaviconURL();
31  if (!favicon_url.is_valid()) {
32    s->BindString(2, std::string());
33  } else {
34    s->BindString(2, history::HistoryDatabase::GURLToDatabaseURL(
35        url.GetFaviconURL()));
36  }
37  s->BindString(3, url.url() ? url.url()->url() : std::string());
38  s->BindInt(4, url.safe_for_autoreplace() ? 1 : 0);
39  if (!url.originating_url().is_valid()) {
40    s->BindString(5, std::string());
41  } else {
42    s->BindString(5, history::HistoryDatabase::GURLToDatabaseURL(
43        url.originating_url()));
44  }
45  s->BindInt64(6, url.date_created().ToTimeT());
46  s->BindInt(7, url.usage_count());
47  s->BindString(8, JoinString(url.input_encodings(), ';'));
48  s->BindInt(9, url.show_in_default_list() ? 1 : 0);
49  s->BindString(10, url.suggestions_url() ? url.suggestions_url()->url() :
50                std::string());
51  s->BindInt(11, url.prepopulate_id());
52  s->BindInt(12, url.autogenerate_keyword() ? 1 : 0);
53  s->BindInt(13, url.logo_id());
54  s->BindBool(14, url.created_by_policy());
55  s->BindString(15, url.instant_url() ? url.instant_url()->url() :
56                std::string());
57}
58}  // anonymous namespace
59
60KeywordTable::~KeywordTable() {}
61
62bool KeywordTable::Init() {
63  if (!db_->DoesTableExist("keywords")) {
64    if (!db_->Execute("CREATE TABLE keywords ("
65                      "id INTEGER PRIMARY KEY,"
66                      "short_name VARCHAR NOT NULL,"
67                      "keyword VARCHAR NOT NULL,"
68                      "favicon_url VARCHAR NOT NULL,"
69                      "url VARCHAR NOT NULL,"
70                      "show_in_default_list INTEGER,"
71                      "safe_for_autoreplace INTEGER,"
72                      "originating_url VARCHAR,"
73                      "date_created INTEGER DEFAULT 0,"
74                      "usage_count INTEGER DEFAULT 0,"
75                      "input_encodings VARCHAR,"
76                      "suggest_url VARCHAR,"
77                      "prepopulate_id INTEGER DEFAULT 0,"
78                      "autogenerate_keyword INTEGER DEFAULT 0,"
79                      "logo_id INTEGER DEFAULT 0,"
80                      "created_by_policy INTEGER DEFAULT 0,"
81                      "instant_url VARCHAR)")) {
82      NOTREACHED();
83      return false;
84    }
85  }
86  return true;
87}
88
89bool KeywordTable::IsSyncable() {
90  return true;
91}
92
93bool KeywordTable::AddKeyword(const TemplateURL& url) {
94  DCHECK(url.id());
95  // Be sure to change kUrlIdPosition if you add columns
96  sql::Statement s(db_->GetCachedStatement(SQL_FROM_HERE,
97      "INSERT INTO keywords "
98      "(short_name, keyword, favicon_url, url, safe_for_autoreplace, "
99      "originating_url, date_created, usage_count, input_encodings, "
100      "show_in_default_list, suggest_url, prepopulate_id, "
101      "autogenerate_keyword, logo_id, created_by_policy, instant_url, "
102      "id) VALUES "
103      "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"));
104  if (!s) {
105    NOTREACHED() << "Statement prepare failed";
106    return false;
107  }
108  BindURLToStatement(url, &s);
109  s.BindInt64(kUrlIdPosition, url.id());
110  if (!s.Run()) {
111    NOTREACHED();
112    return false;
113  }
114  return true;
115}
116
117bool KeywordTable::RemoveKeyword(TemplateURLID id) {
118  DCHECK(id);
119  sql::Statement s(
120      db_->GetUniqueStatement("DELETE FROM keywords WHERE id = ?"));
121  if (!s) {
122    NOTREACHED() << "Statement prepare failed";
123    return false;
124  }
125  s.BindInt64(0, id);
126  return s.Run();
127}
128
129bool KeywordTable::GetKeywords(std::vector<TemplateURL*>* urls) {
130  sql::Statement s(db_->GetUniqueStatement(
131      "SELECT id, short_name, keyword, favicon_url, url, "
132      "safe_for_autoreplace, originating_url, date_created, "
133      "usage_count, input_encodings, show_in_default_list, "
134      "suggest_url, prepopulate_id, autogenerate_keyword, logo_id, "
135      "created_by_policy, instant_url "
136      "FROM keywords ORDER BY id ASC"));
137  if (!s) {
138    NOTREACHED() << "Statement prepare failed";
139    return false;
140  }
141  while (s.Step()) {
142    TemplateURL* template_url = new TemplateURL();
143    template_url->set_id(s.ColumnInt64(0));
144
145    std::string tmp;
146    tmp = s.ColumnString(1);
147    DCHECK(!tmp.empty());
148    template_url->set_short_name(UTF8ToUTF16(tmp));
149
150    template_url->set_keyword(UTF8ToUTF16(s.ColumnString(2)));
151
152    tmp = s.ColumnString(3);
153    if (!tmp.empty())
154      template_url->SetFaviconURL(GURL(tmp));
155
156    template_url->SetURL(s.ColumnString(4), 0, 0);
157
158    template_url->set_safe_for_autoreplace(s.ColumnInt(5) == 1);
159
160    tmp = s.ColumnString(6);
161    if (!tmp.empty())
162      template_url->set_originating_url(GURL(tmp));
163
164    template_url->set_date_created(Time::FromTimeT(s.ColumnInt64(7)));
165
166    template_url->set_usage_count(s.ColumnInt(8));
167
168    std::vector<std::string> encodings;
169    base::SplitString(s.ColumnString(9), ';', &encodings);
170    template_url->set_input_encodings(encodings);
171
172    template_url->set_show_in_default_list(s.ColumnInt(10) == 1);
173
174    template_url->SetSuggestionsURL(s.ColumnString(11), 0, 0);
175
176    template_url->set_prepopulate_id(s.ColumnInt(12));
177
178    template_url->set_autogenerate_keyword(s.ColumnInt(13) == 1);
179
180    template_url->set_logo_id(s.ColumnInt(14));
181
182    template_url->set_created_by_policy(s.ColumnBool(15));
183
184    template_url->SetInstantURL(s.ColumnString(16), 0, 0);
185
186    urls->push_back(template_url);
187  }
188  return s.Succeeded();
189}
190
191bool KeywordTable::UpdateKeyword(const TemplateURL& url) {
192  DCHECK(url.id());
193  // Be sure to change kUrlIdPosition if you add columns
194  sql::Statement s(db_->GetUniqueStatement(
195      "UPDATE keywords "
196      "SET short_name=?, keyword=?, favicon_url=?, url=?, "
197      "safe_for_autoreplace=?, originating_url=?, date_created=?, "
198      "usage_count=?, input_encodings=?, show_in_default_list=?, "
199      "suggest_url=?, prepopulate_id=?, autogenerate_keyword=?, "
200      "logo_id=?, created_by_policy=?, instant_url=? WHERE id=?"));
201  if (!s) {
202    NOTREACHED() << "Statement prepare failed";
203    return false;
204  }
205  BindURLToStatement(url, &s);
206  s.BindInt64(kUrlIdPosition, url.id());
207  return s.Run();
208}
209
210bool KeywordTable::SetDefaultSearchProviderID(int64 id) {
211  return meta_table_->SetValue(kDefaultSearchProviderKey, id);
212}
213
214int64 KeywordTable::GetDefaulSearchProviderID() {
215  int64 value = 0;
216  meta_table_->GetValue(kDefaultSearchProviderKey, &value);
217  return value;
218}
219
220bool KeywordTable::SetBuitinKeywordVersion(int version) {
221  return meta_table_->SetValue(kBuiltinKeywordVersion, version);
222}
223
224int KeywordTable::GetBuitinKeywordVersion() {
225  int version = 0;
226  meta_table_->GetValue(kBuiltinKeywordVersion, &version);
227  return version;
228}
229
230bool KeywordTable::MigrateToVersion21AutoGenerateKeywordColumn() {
231  return db_->Execute("ALTER TABLE keywords ADD COLUMN autogenerate_keyword "
232                      "INTEGER DEFAULT 0");
233}
234
235bool KeywordTable::MigrateToVersion25AddLogoIDColumn() {
236  return db_->Execute(
237      "ALTER TABLE keywords ADD COLUMN logo_id INTEGER DEFAULT 0");
238}
239
240bool KeywordTable::MigrateToVersion26AddCreatedByPolicyColumn() {
241  return db_->Execute("ALTER TABLE keywords ADD COLUMN created_by_policy "
242                      "INTEGER DEFAULT 0");
243}
244
245bool KeywordTable::MigrateToVersion28SupportsInstantColumn() {
246  return db_->Execute("ALTER TABLE keywords ADD COLUMN supports_instant "
247                      "INTEGER DEFAULT 0");
248}
249
250bool KeywordTable::MigrateToVersion29InstantUrlToSupportsInstant() {
251  if (!db_->Execute("ALTER TABLE keywords ADD COLUMN instant_url VARCHAR"))
252    return false;
253
254  if (!db_->Execute("CREATE TABLE keywords_temp ("
255                    "id INTEGER PRIMARY KEY,"
256                    "short_name VARCHAR NOT NULL,"
257                    "keyword VARCHAR NOT NULL,"
258                    "favicon_url VARCHAR NOT NULL,"
259                    "url VARCHAR NOT NULL,"
260                    "show_in_default_list INTEGER,"
261                    "safe_for_autoreplace INTEGER,"
262                    "originating_url VARCHAR,"
263                    "date_created INTEGER DEFAULT 0,"
264                    "usage_count INTEGER DEFAULT 0,"
265                    "input_encodings VARCHAR,"
266                    "suggest_url VARCHAR,"
267                    "prepopulate_id INTEGER DEFAULT 0,"
268                    "autogenerate_keyword INTEGER DEFAULT 0,"
269                    "logo_id INTEGER DEFAULT 0,"
270                    "created_by_policy INTEGER DEFAULT 0,"
271                    "instant_url VARCHAR)")) {
272    return false;
273  }
274
275  if (!db_->Execute(
276      "INSERT INTO keywords_temp "
277      "SELECT id, short_name, keyword, favicon_url, url, "
278      "show_in_default_list, safe_for_autoreplace, originating_url, "
279      "date_created, usage_count, input_encodings, suggest_url, "
280      "prepopulate_id, autogenerate_keyword, logo_id, created_by_policy, "
281      "instant_url FROM keywords")) {
282    return false;
283  }
284
285  if (!db_->Execute("DROP TABLE keywords"))
286    return false;
287
288  if (!db_->Execute("ALTER TABLE keywords_temp RENAME TO keywords"))
289    return false;
290
291  return true;
292}
293