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