1// Copyright (c) 2010 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/history/url_database.h"
6
7#include <algorithm>
8#include <limits>
9#include <string>
10#include <vector>
11
12#include "app/sql/statement.h"
13#include "base/utf_string_conversions.h"
14#include "chrome/common/url_constants.h"
15#include "googleurl/src/gurl.h"
16#include "ui/base/l10n/l10n_util.h"
17
18namespace history {
19
20const char URLDatabase::kURLRowFields[] = HISTORY_URL_ROW_FIELDS;
21const int URLDatabase::kNumURLRowFields = 9;
22
23URLDatabase::URLEnumeratorBase::URLEnumeratorBase()
24    : initialized_(false) {
25}
26
27URLDatabase::URLEnumeratorBase::~URLEnumeratorBase() {
28}
29
30URLDatabase::URLEnumerator::URLEnumerator() {
31}
32
33URLDatabase::IconMappingEnumerator::IconMappingEnumerator() {
34}
35
36bool URLDatabase::URLEnumerator::GetNextURL(URLRow* r) {
37  if (statement_.Step()) {
38    FillURLRow(statement_, r);
39    return true;
40  }
41  return false;
42}
43
44bool URLDatabase::IconMappingEnumerator::GetNextIconMapping(IconMapping* r) {
45  if (!statement_.Step())
46    return false;
47
48  r->page_url = GURL(statement_.ColumnString(0));
49  r->icon_id =  statement_.ColumnInt64(1);
50  return true;
51}
52
53URLDatabase::URLDatabase()
54    : has_keyword_search_terms_(false) {
55}
56
57URLDatabase::~URLDatabase() {
58}
59
60// static
61std::string URLDatabase::GURLToDatabaseURL(const GURL& gurl) {
62  // TODO(brettw): do something fancy here with encoding, etc.
63
64  // Strip username and password from URL before sending to DB.
65  GURL::Replacements replacements;
66  replacements.ClearUsername();
67  replacements.ClearPassword();
68
69  return (gurl.ReplaceComponents(replacements)).spec();
70}
71
72// Convenience to fill a history::URLRow. Must be in sync with the fields in
73// kURLRowFields.
74void URLDatabase::FillURLRow(sql::Statement& s, history::URLRow* i) {
75  DCHECK(i);
76  i->id_ = s.ColumnInt64(0);
77  i->url_ = GURL(s.ColumnString(1));
78  i->title_ = s.ColumnString16(2);
79  i->visit_count_ = s.ColumnInt(3);
80  i->typed_count_ = s.ColumnInt(4);
81  i->last_visit_ = base::Time::FromInternalValue(s.ColumnInt64(5));
82  i->hidden_ = s.ColumnInt(6) != 0;
83}
84
85bool URLDatabase::GetURLRow(URLID url_id, URLRow* info) {
86  // TODO(brettw) We need check for empty URLs to handle the case where
87  // there are old URLs in the database that are empty that got in before
88  // we added any checks. We should eventually be able to remove it
89  // when all inputs are using GURL (which prohibit empty input).
90  sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
91      "SELECT" HISTORY_URL_ROW_FIELDS "FROM urls WHERE id=?"));
92  if (!statement)
93    return false;
94
95  statement.BindInt64(0, url_id);
96  if (statement.Step()) {
97    FillURLRow(statement, info);
98    return true;
99  }
100  return false;
101}
102
103bool URLDatabase::GetAllTypedUrls(std::vector<history::URLRow>* urls) {
104  sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
105      "SELECT" HISTORY_URL_ROW_FIELDS "FROM urls WHERE typed_count > 0"));
106  if (!statement)
107    return false;
108
109  while (statement.Step()) {
110    URLRow info;
111    FillURLRow(statement, &info);
112    urls->push_back(info);
113  }
114  return true;
115}
116
117URLID URLDatabase::GetRowForURL(const GURL& url, history::URLRow* info) {
118  sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
119      "SELECT" HISTORY_URL_ROW_FIELDS "FROM urls WHERE url=?"));
120  if (!statement)
121    return 0;
122
123  std::string url_string = GURLToDatabaseURL(url);
124  statement.BindString(0, url_string);
125  if (!statement.Step())
126    return 0;  // no data
127
128  if (info)
129    FillURLRow(statement, info);
130  return statement.ColumnInt64(0);
131}
132
133bool URLDatabase::UpdateURLRow(URLID url_id,
134                               const history::URLRow& info) {
135  sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
136      "UPDATE urls SET title=?,visit_count=?,typed_count=?,last_visit_time=?,"
137        "hidden=?"
138      "WHERE id=?"));
139  if (!statement)
140    return false;
141
142  statement.BindString16(0, info.title());
143  statement.BindInt(1, info.visit_count());
144  statement.BindInt(2, info.typed_count());
145  statement.BindInt64(3, info.last_visit().ToInternalValue());
146  statement.BindInt(4, info.hidden() ? 1 : 0);
147  statement.BindInt64(5, url_id);
148  return statement.Run();
149}
150
151URLID URLDatabase::AddURLInternal(const history::URLRow& info,
152                                  bool is_temporary) {
153  // This function is used to insert into two different tables, so we have to
154  // do some shuffling. Unfortinately, we can't use the macro
155  // HISTORY_URL_ROW_FIELDS because that specifies the table name which is
156  // invalid in the insert syntax.
157  #define ADDURL_COMMON_SUFFIX \
158      " (url, title, visit_count, typed_count, "\
159      "last_visit_time, hidden) "\
160      "VALUES (?,?,?,?,?,?)"
161  const char* statement_name;
162  const char* statement_sql;
163  if (is_temporary) {
164    statement_name = "AddURLTemporary";
165    statement_sql = "INSERT INTO temp_urls" ADDURL_COMMON_SUFFIX;
166  } else {
167    statement_name = "AddURL";
168    statement_sql = "INSERT INTO urls" ADDURL_COMMON_SUFFIX;
169  }
170  #undef ADDURL_COMMON_SUFFIX
171
172  sql::Statement statement(GetDB().GetCachedStatement(
173      sql::StatementID(statement_name), statement_sql));
174  if (!statement) {
175    NOTREACHED() << GetDB().GetErrorMessage();
176    return 0;
177  }
178
179  statement.BindString(0, GURLToDatabaseURL(info.url()));
180  statement.BindString16(1, info.title());
181  statement.BindInt(2, info.visit_count());
182  statement.BindInt(3, info.typed_count());
183  statement.BindInt64(4, info.last_visit().ToInternalValue());
184  statement.BindInt(5, info.hidden() ? 1 : 0);
185
186  if (!statement.Run()) {
187    VLOG(0) << "Failed to add url " << info.url().possibly_invalid_spec()
188            << " to table history.urls.";
189    return 0;
190  }
191  return GetDB().GetLastInsertRowId();
192}
193
194bool URLDatabase::DeleteURLRow(URLID id) {
195  sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
196      "DELETE FROM urls WHERE id = ?"));
197  if (!statement)
198    return false;
199
200  statement.BindInt64(0, id);
201  if (!statement.Run())
202    return false;
203
204  // And delete any keyword visits.
205  if (!has_keyword_search_terms_)
206    return true;
207
208  sql::Statement del_keyword_visit(GetDB().GetCachedStatement(SQL_FROM_HERE,
209                          "DELETE FROM keyword_search_terms WHERE url_id=?"));
210  if (!del_keyword_visit)
211    return false;
212  del_keyword_visit.BindInt64(0, id);
213  return del_keyword_visit.Run();
214}
215
216bool URLDatabase::CreateTemporaryURLTable() {
217  return CreateURLTable(true);
218}
219
220bool URLDatabase::CommitTemporaryURLTable() {
221  // See the comments in the header file as well as
222  // HistoryBackend::DeleteAllHistory() for more information on how this works
223  // and why it does what it does.
224  //
225  // Note that the main database overrides this to additionally create the
226  // supplimentary indices that the archived database doesn't need.
227
228  // Swap the url table out and replace it with the temporary one.
229  if (!GetDB().Execute("DROP TABLE urls")) {
230    NOTREACHED() << GetDB().GetErrorMessage();
231    return false;
232  }
233  if (!GetDB().Execute("ALTER TABLE temp_urls RENAME TO urls")) {
234    NOTREACHED() << GetDB().GetErrorMessage();
235    return false;
236  }
237
238  // Create the index over URLs. This is needed for the main, in-memory, and
239  // archived databases, so we always do it. The supplimentary indices used by
240  // the main database are not created here. When deleting all history, they
241  // are created by HistoryDatabase::RecreateAllButStarAndURLTables().
242  CreateMainURLIndex();
243
244  return true;
245}
246
247bool URLDatabase::InitURLEnumeratorForEverything(URLEnumerator* enumerator) {
248  DCHECK(!enumerator->initialized_);
249  std::string sql("SELECT ");
250  sql.append(kURLRowFields);
251  sql.append(" FROM urls");
252  enumerator->statement_.Assign(GetDB().GetUniqueStatement(sql.c_str()));
253  if (!enumerator->statement_) {
254    NOTREACHED() << GetDB().GetErrorMessage();
255    return false;
256  }
257  enumerator->initialized_ = true;
258  return true;
259}
260
261bool URLDatabase::InitURLEnumeratorForSignificant(URLEnumerator* enumerator) {
262  DCHECK(!enumerator->initialized_);
263  std::string sql("SELECT ");
264  sql.append(kURLRowFields);
265  sql.append(" FROM urls WHERE last_visit_time >= ? OR visit_count > ? OR "
266             "typed_count > ?");
267  enumerator->statement_.Assign(GetDB().GetUniqueStatement(sql.c_str()));
268  if (!enumerator->statement_) {
269    NOTREACHED() << GetDB().GetErrorMessage();
270    return false;
271  }
272  enumerator->statement_.BindInt64(
273      0, AutocompleteAgeThreshold().ToInternalValue());
274  enumerator->statement_.BindInt(1, kLowQualityMatchVisitLimit);
275  enumerator->statement_.BindInt(2, kLowQualityMatchTypedLimit);
276  enumerator->initialized_ = true;
277  return true;
278}
279
280bool URLDatabase::InitIconMappingEnumeratorForEverything(
281    IconMappingEnumerator* enumerator) {
282  DCHECK(!enumerator->initialized_);
283  enumerator->statement_.Assign(GetDB().GetUniqueStatement(
284      "SELECT url, favicon_id FROM urls WHERE favicon_id <> 0"));
285  if (!enumerator->statement_) {
286    NOTREACHED() << GetDB().GetErrorMessage();
287    return false;
288  }
289  enumerator->initialized_ = true;
290  return true;
291}
292
293void URLDatabase::AutocompleteForPrefix(const string16& prefix,
294                                        size_t max_results,
295                                        bool typed_only,
296                                        std::vector<history::URLRow>* results) {
297  // NOTE: this query originally sorted by starred as the second parameter. But
298  // as bookmarks is no longer part of the db we no longer include the order
299  // by clause.
300  results->clear();
301  const char* sql;
302  int line;
303  if (typed_only) {
304    sql = "SELECT" HISTORY_URL_ROW_FIELDS "FROM urls "
305        "WHERE url >= ? AND url < ? AND hidden = 0 AND typed_count > 0 "
306        "ORDER BY typed_count DESC, visit_count DESC, last_visit_time DESC "
307        "LIMIT ?";
308    line = __LINE__;
309  } else {
310    sql = "SELECT" HISTORY_URL_ROW_FIELDS "FROM urls "
311        "WHERE url >= ? AND url < ? AND hidden = 0 "
312        "ORDER BY typed_count DESC, visit_count DESC, last_visit_time DESC "
313        "LIMIT ?";
314    line = __LINE__;
315  }
316  sql::Statement statement(
317      GetDB().GetCachedStatement(sql::StatementID(__FILE__, line), sql));
318  if (!statement)
319    return;
320
321  // We will find all strings between "prefix" and this string, which is prefix
322  // followed by the maximum character size. Use 8-bit strings for everything
323  // so we can be sure sqlite is comparing everything in 8-bit mode. Otherwise,
324  // it will have to convert strings either to UTF-8 or UTF-16 for comparison.
325  std::string prefix_utf8(UTF16ToUTF8(prefix));
326  std::string end_query(prefix_utf8);
327  end_query.push_back(std::numeric_limits<unsigned char>::max());
328
329  statement.BindString(0, prefix_utf8);
330  statement.BindString(1, end_query);
331  statement.BindInt(2, static_cast<int>(max_results));
332
333  while (statement.Step()) {
334    history::URLRow info;
335    FillURLRow(statement, &info);
336    if (info.url().is_valid())
337      results->push_back(info);
338  }
339}
340
341bool URLDatabase::FindShortestURLFromBase(const std::string& base,
342                                          const std::string& url,
343                                          int min_visits,
344                                          int min_typed,
345                                          bool allow_base,
346                                          history::URLRow* info) {
347  // Select URLs that start with |base| and are prefixes of |url|.  All parts
348  // of this query except the substr() call can be done using the index.  We
349  // could do this query with a couple of LIKE or GLOB statements as well, but
350  // those wouldn't use the index, and would run into problems with "wildcard"
351  // characters that appear in URLs (% for LIKE, or *, ? for GLOB).
352  std::string sql("SELECT ");
353  sql.append(kURLRowFields);
354  sql.append(" FROM urls WHERE url ");
355  sql.append(allow_base ? ">=" : ">");
356  sql.append(" ? AND url < :end AND url = substr(:end, 1, length(url)) "
357             "AND hidden = 0 AND visit_count >= ? AND typed_count >= ? "
358             "ORDER BY url LIMIT 1");
359  sql::Statement statement(GetDB().GetUniqueStatement(sql.c_str()));
360  if (!statement) {
361    NOTREACHED() << GetDB().GetErrorMessage();
362    return false;
363  }
364
365  statement.BindString(0, base);
366  statement.BindString(1, url);   // :end
367  statement.BindInt(2, min_visits);
368  statement.BindInt(3, min_typed);
369
370  if (!statement.Step())
371    return false;
372
373  DCHECK(info);
374  FillURLRow(statement, info);
375  return true;
376}
377
378bool URLDatabase::InitKeywordSearchTermsTable() {
379  has_keyword_search_terms_ = true;
380  if (!GetDB().DoesTableExist("keyword_search_terms")) {
381    if (!GetDB().Execute("CREATE TABLE keyword_search_terms ("
382        "keyword_id INTEGER NOT NULL,"      // ID of the TemplateURL.
383        "url_id INTEGER NOT NULL,"          // ID of the url.
384        "lower_term LONGVARCHAR NOT NULL,"  // The search term, in lower case.
385        "term LONGVARCHAR NOT NULL)"))      // The actual search term.
386      return false;
387  }
388  return true;
389}
390
391void URLDatabase::CreateKeywordSearchTermsIndices() {
392  // For searching.
393  GetDB().Execute("CREATE INDEX keyword_search_terms_index1 ON "
394                  "keyword_search_terms (keyword_id, lower_term)");
395
396  // For deletion.
397  GetDB().Execute("CREATE INDEX keyword_search_terms_index2 ON "
398                  "keyword_search_terms (url_id)");
399}
400
401bool URLDatabase::DropKeywordSearchTermsTable() {
402  // This will implicitly delete the indices over the table.
403  return GetDB().Execute("DROP TABLE keyword_search_terms");
404}
405
406bool URLDatabase::SetKeywordSearchTermsForURL(URLID url_id,
407                                              TemplateURLID keyword_id,
408                                              const string16& term) {
409  DCHECK(url_id && keyword_id && !term.empty());
410
411  sql::Statement exist_statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
412      "SELECT term FROM keyword_search_terms "
413      "WHERE keyword_id = ? AND url_id = ?"));
414  if (!exist_statement)
415    return false;
416  exist_statement.BindInt64(0, keyword_id);
417  exist_statement.BindInt64(1, url_id);
418  if (exist_statement.Step())
419    return true;  // Term already exists, no need to add it.
420
421  sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
422      "INSERT INTO keyword_search_terms (keyword_id, url_id, lower_term, term) "
423      "VALUES (?,?,?,?)"));
424  if (!statement)
425    return false;
426
427  statement.BindInt64(0, keyword_id);
428  statement.BindInt64(1, url_id);
429  statement.BindString16(2, l10n_util::ToLower(term));
430  statement.BindString16(3, term);
431  return statement.Run();
432}
433
434bool URLDatabase::GetKeywordSearchTermRow(URLID url_id,
435                                          KeywordSearchTermRow* row) {
436  DCHECK(url_id);
437  sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
438      "SELECT keyword_id, term FROM keyword_search_terms WHERE url_id=?"));
439  if (!statement)
440    return false;
441
442  statement.BindInt64(0, url_id);
443  if (!statement.Step())
444    return false;
445
446  if (row) {
447    row->url_id = url_id;
448    row->keyword_id = statement.ColumnInt64(0);
449    row->term = statement.ColumnString16(1);
450  }
451  return true;
452}
453
454void URLDatabase::DeleteAllSearchTermsForKeyword(
455    TemplateURLID keyword_id) {
456  DCHECK(keyword_id);
457  sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
458      "DELETE FROM keyword_search_terms WHERE keyword_id=?"));
459  if (!statement)
460    return;
461
462  statement.BindInt64(0, keyword_id);
463  statement.Run();
464}
465
466void URLDatabase::GetMostRecentKeywordSearchTerms(
467    TemplateURLID keyword_id,
468    const string16& prefix,
469    int max_count,
470    std::vector<KeywordSearchTermVisit>* matches) {
471  // NOTE: the keyword_id can be zero if on first run the user does a query
472  // before the TemplateURLModel has finished loading. As the chances of this
473  // occurring are small, we ignore it.
474  if (!keyword_id)
475    return;
476
477  DCHECK(!prefix.empty());
478  sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
479      "SELECT DISTINCT kv.term, u.last_visit_time "
480      "FROM keyword_search_terms kv "
481      "JOIN urls u ON kv.url_id = u.id "
482      "WHERE kv.keyword_id = ? AND kv.lower_term >= ? AND kv.lower_term < ? "
483      "ORDER BY u.last_visit_time DESC LIMIT ?"));
484  if (!statement)
485    return;
486
487  // NOTE: Keep this ToLower() call in sync with search_provider.cc.
488  string16 lower_prefix = l10n_util::ToLower(prefix);
489  // This magic gives us a prefix search.
490  string16 next_prefix = lower_prefix;
491  next_prefix[next_prefix.size() - 1] =
492      next_prefix[next_prefix.size() - 1] + 1;
493  statement.BindInt64(0, keyword_id);
494  statement.BindString16(1, lower_prefix);
495  statement.BindString16(2, next_prefix);
496  statement.BindInt(3, max_count);
497
498  KeywordSearchTermVisit visit;
499  while (statement.Step()) {
500    visit.term = statement.ColumnString16(0);
501    visit.time = base::Time::FromInternalValue(statement.ColumnInt64(1));
502    matches->push_back(visit);
503  }
504}
505
506bool URLDatabase::DropStarredIDFromURLs() {
507  if (!GetDB().DoesColumnExist("urls", "starred_id"))
508    return true;  // urls is already updated, no need to continue.
509
510  // Create a temporary table to contain the new URLs table.
511  if (!CreateTemporaryURLTable()) {
512    NOTREACHED();
513    return false;
514  }
515
516  // Copy the contents.
517  if (!GetDB().Execute(
518      "INSERT INTO temp_urls (id, url, title, visit_count, typed_count, "
519      "last_visit_time, hidden, favicon_id) "
520      "SELECT id, url, title, visit_count, typed_count, last_visit_time, "
521      "hidden, favicon_id FROM urls")) {
522    NOTREACHED() << GetDB().GetErrorMessage();
523    return false;
524  }
525
526  // Rename/commit the tmp table.
527  CommitTemporaryURLTable();
528
529  return true;
530}
531
532bool URLDatabase::CreateURLTable(bool is_temporary) {
533  const char* name = is_temporary ? "temp_urls" : "urls";
534  if (GetDB().DoesTableExist(name))
535    return true;
536
537  std::string sql;
538  sql.append("CREATE TABLE ");
539  sql.append(name);
540  sql.append("("
541      "id INTEGER PRIMARY KEY,"
542      "url LONGVARCHAR,"
543      "title LONGVARCHAR,"
544      "visit_count INTEGER DEFAULT 0 NOT NULL,"
545      "typed_count INTEGER DEFAULT 0 NOT NULL,"
546      "last_visit_time INTEGER NOT NULL,"
547      "hidden INTEGER DEFAULT 0 NOT NULL,"
548      "favicon_id INTEGER DEFAULT 0 NOT NULL)"); // favicon_id is not used now.
549
550  return GetDB().Execute(sql.c_str());
551}
552
553void URLDatabase::CreateMainURLIndex() {
554  // Index over URLs so we can quickly look up based on URL.  Ignore errors as
555  // this likely already exists (and the same below).
556  GetDB().Execute("CREATE INDEX urls_url_index ON urls (url)");
557}
558
559}  // namespace history
560