top_sites_database.cc revision 513209b27ff55e2841eac0e4120199c23acce758 
1// Copyright (c) 2009 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 "app/sql/connection.h"
6#include "app/sql/transaction.h"
7#include "base/file_util.h"
8#include "base/string_util.h"
9#include "chrome/browser/diagnostics/sqlite_diagnostics.h"
10#include "chrome/browser/history/history_types.h"
11#include "chrome/browser/history/top_sites.h"
12#include "chrome/browser/history/top_sites_database.h"
13
14namespace history {
15
16static const int kVersionNumber = 1;
17
18TopSitesDatabase::TopSitesDatabase() : may_need_history_migration_(false) {
19}
20
21TopSitesDatabase::~TopSitesDatabase() {
22}
23
24bool TopSitesDatabase::Init(const FilePath& db_name) {
25  bool file_existed = file_util::PathExists(db_name);
26
27  if (!file_existed)
28    may_need_history_migration_ = true;
29
30  db_.reset(CreateDB(db_name));
31  if (!db_.get())
32    return false;
33
34  bool does_meta_exist = sql::MetaTable::DoesTableExist(db_.get());
35  if (!does_meta_exist && file_existed) {
36    may_need_history_migration_ = true;
37
38    // If the meta file doesn't exist, this version is old. We could remove all
39    // the entries as they are no longer applicable, but it's safest to just
40    // remove the file and start over.
41    db_.reset(NULL);
42    if (!file_util::Delete(db_name, false) &&
43        !file_util::Delete(db_name, false)) {
44      // Try to delete twice. If we can't, fail.
45      LOG(ERROR) << "unable to delete old TopSites file";
46      return false;
47    }
48    db_.reset(CreateDB(db_name));
49    if (!db_.get())
50      return false;
51  }
52
53  if (!meta_table_.Init(db_.get(), kVersionNumber, kVersionNumber))
54    return false;
55
56  if (!InitThumbnailTable())
57    return false;
58
59  // Version check.
60  if (meta_table_.GetVersionNumber() != kVersionNumber)
61    return false;
62
63  return true;
64}
65
66bool TopSitesDatabase::InitThumbnailTable() {
67  if (!db_->DoesTableExist("thumbnails")) {
68    if (!db_->Execute("CREATE TABLE thumbnails ("
69                      "url LONGVARCHAR PRIMARY KEY,"
70                      "url_rank INTEGER ,"
71                      "title LONGVARCHAR,"
72                      "thumbnail BLOB,"
73                      "redirects LONGVARCHAR,"
74                      "boring_score DOUBLE DEFAULT 1.0, "
75                      "good_clipping INTEGER DEFAULT 0, "
76                      "at_top INTEGER DEFAULT 0, "
77                      "last_updated INTEGER DEFAULT 0) ")) {
78      LOG(WARNING) << db_->GetErrorMessage();
79      return false;
80    }
81  }
82  return true;
83}
84
85void TopSitesDatabase::GetPageThumbnails(MostVisitedURLList* urls,
86                                             URLToImagesMap* thumbnails) {
87  sql::Statement statement(db_->GetCachedStatement(
88      SQL_FROM_HERE,
89      "SELECT url, url_rank, title, thumbnail, redirects, "
90      "boring_score, good_clipping, at_top, last_updated "
91      "FROM thumbnails ORDER BY url_rank "));
92
93  if (!statement) {
94    LOG(WARNING) << db_->GetErrorMessage();
95    return;
96  }
97
98  urls->clear();
99  thumbnails->clear();
100
101  while (statement.Step()) {
102    // Results are sorted by url_rank.
103    MostVisitedURL url;
104    GURL gurl(statement.ColumnString(0));
105    url.url = gurl;
106    url.title = statement.ColumnString16(2);
107    std::string redirects = statement.ColumnString(4);
108    SetRedirects(redirects, &url);
109    urls->push_back(url);
110
111    std::vector<unsigned char> data;
112    statement.ColumnBlobAsVector(3, &data);
113    Images thumbnail;
114    thumbnail.thumbnail = RefCountedBytes::TakeVector(&data);
115    thumbnail.thumbnail_score.boring_score = statement.ColumnDouble(5);
116    thumbnail.thumbnail_score.good_clipping = statement.ColumnBool(6);
117    thumbnail.thumbnail_score.at_top = statement.ColumnBool(7);
118    thumbnail.thumbnail_score.time_at_snapshot =
119        base::Time::FromInternalValue(statement.ColumnInt64(8));
120
121    (*thumbnails)[gurl] = thumbnail;
122  }
123}
124
125// static
126std::string TopSitesDatabase::GetRedirects(const MostVisitedURL& url) {
127  std::vector<std::string> redirects;
128  for (size_t i = 0; i < url.redirects.size(); i++)
129    redirects.push_back(url.redirects[i].spec());
130  return JoinString(redirects, ' ');
131}
132
133// static
134void TopSitesDatabase::SetRedirects(const std::string& redirects,
135                                    MostVisitedURL* url) {
136  std::vector<std::string> redirects_vector;
137  SplitStringAlongWhitespace(redirects, &redirects_vector);
138  for (size_t i = 0; i < redirects_vector.size(); i++)
139    url->redirects.push_back(GURL(redirects_vector[i]));
140}
141
142void TopSitesDatabase::SetPageThumbnail(const MostVisitedURL& url,
143                                            int new_rank,
144                                            const Images& thumbnail) {
145  sql::Transaction transaction(db_.get());
146  transaction.Begin();
147
148  int rank = GetURLRank(url);
149  if (rank == -1) {
150    AddPageThumbnail(url, new_rank, thumbnail);
151  } else {
152    UpdatePageRankNoTransaction(url, new_rank);
153    UpdatePageThumbnail(url, thumbnail);
154  }
155
156  transaction.Commit();
157}
158
159void TopSitesDatabase::UpdatePageThumbnail(
160    const MostVisitedURL& url, const Images& thumbnail) {
161  sql::Statement statement(db_->GetCachedStatement(
162      SQL_FROM_HERE,
163      "UPDATE thumbnails SET "
164      "title = ?, thumbnail = ?, redirects = ?, "
165      "boring_score = ?, good_clipping = ?, at_top = ?, last_updated = ? "
166      "WHERE url = ? "));
167  if (!statement)
168    return;
169
170  statement.BindString16(0, url.title);
171  if (thumbnail.thumbnail.get() && thumbnail.thumbnail->front()) {
172    statement.BindBlob(1, thumbnail.thumbnail->front(),
173                       static_cast<int>(thumbnail.thumbnail->size()));
174  }
175  statement.BindString(2, GetRedirects(url));
176  const ThumbnailScore& score = thumbnail.thumbnail_score;
177  statement.BindDouble(3, score.boring_score);
178  statement.BindBool(4, score.good_clipping);
179  statement.BindBool(5, score.at_top);
180  statement.BindInt64(6, score.time_at_snapshot.ToInternalValue());
181  statement.BindString(7, url.url.spec());
182  if (!statement.Run())
183    NOTREACHED() << db_->GetErrorMessage();
184}
185
186void TopSitesDatabase::AddPageThumbnail(const MostVisitedURL& url,
187                                            int new_rank,
188                                            const Images& thumbnail) {
189  int count = GetRowCount();
190
191  sql::Statement statement(db_->GetCachedStatement(
192      SQL_FROM_HERE,
193      "INSERT OR REPLACE INTO thumbnails "
194      "(url, url_rank, title, thumbnail, redirects, "
195      "boring_score, good_clipping, at_top, last_updated) "
196      "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"));
197  if (!statement)
198    return;
199
200  statement.BindString(0, url.url.spec());
201  statement.BindInt(1, count);  // Make it the last url.
202  statement.BindString16(2, url.title);
203  if (thumbnail.thumbnail.get() && thumbnail.thumbnail->front()) {
204    statement.BindBlob(3, thumbnail.thumbnail->front(),
205                       static_cast<int>(thumbnail.thumbnail->size()));
206  }
207  statement.BindString(4, GetRedirects(url));
208  const ThumbnailScore& score = thumbnail.thumbnail_score;
209  statement.BindDouble(5, score.boring_score);
210  statement.BindBool(6, score.good_clipping);
211  statement.BindBool(7, score.at_top);
212  statement.BindInt64(8, score.time_at_snapshot.ToInternalValue());
213  if (!statement.Run())
214    NOTREACHED() << db_->GetErrorMessage();
215
216  UpdatePageRankNoTransaction(url, new_rank);
217}
218
219void TopSitesDatabase::UpdatePageRank(const MostVisitedURL& url,
220                                          int new_rank) {
221  sql::Transaction transaction(db_.get());
222  transaction.Begin();
223  UpdatePageRankNoTransaction(url, new_rank);
224  transaction.Commit();
225}
226
227// Caller should have a transaction open.
228void TopSitesDatabase::UpdatePageRankNoTransaction(
229    const MostVisitedURL& url, int new_rank) {
230  int prev_rank = GetURLRank(url);
231  if (prev_rank == -1) {
232    LOG(WARNING) << "Updating rank of an unknown URL: " << url.url.spec();
233    return;
234  }
235
236  // Shift the ranks.
237  if (prev_rank > new_rank) {
238    // Shift up
239    sql::Statement shift_statement(db_->GetCachedStatement(
240        SQL_FROM_HERE,
241        "UPDATE thumbnails "
242        "SET url_rank = url_rank + 1 "
243        "WHERE url_rank >= ? AND url_rank < ?"));
244    shift_statement.BindInt(0, new_rank);
245    shift_statement.BindInt(1, prev_rank);
246    if (shift_statement)
247      shift_statement.Run();
248  } else if (prev_rank < new_rank) {
249    // Shift down
250    sql::Statement shift_statement(db_->GetCachedStatement(
251        SQL_FROM_HERE,
252        "UPDATE thumbnails "
253        "SET url_rank = url_rank - 1 "
254        "WHERE url_rank > ? AND url_rank <= ?"));
255    shift_statement.BindInt(0, prev_rank);
256    shift_statement.BindInt(1, new_rank);
257    if (shift_statement)
258      shift_statement.Run();
259  }
260
261  // Set the url's rank.
262  sql::Statement set_statement(db_->GetCachedStatement(
263      SQL_FROM_HERE,
264      "UPDATE thumbnails "
265      "SET url_rank = ? "
266      "WHERE url == ?"));
267  set_statement.BindInt(0, new_rank);
268  set_statement.BindString(1, url.url.spec());
269  if (set_statement)
270    set_statement.Run();
271}
272
273bool TopSitesDatabase::GetPageThumbnail(const GURL& url,
274                                            Images* thumbnail) {
275  sql::Statement statement(db_->GetCachedStatement(
276      SQL_FROM_HERE,
277      "SELECT thumbnail, boring_score, good_clipping, at_top, last_updated "
278      "FROM thumbnails WHERE url=?"));
279
280  if (!statement) {
281    LOG(WARNING) << db_->GetErrorMessage();
282    return false;
283  }
284
285  statement.BindString(0, url.spec());
286  if (!statement.Step())
287    return false;
288
289  std::vector<unsigned char> data;
290  statement.ColumnBlobAsVector(0, &data);
291  thumbnail->thumbnail = RefCountedBytes::TakeVector(&data);
292  thumbnail->thumbnail_score.boring_score = statement.ColumnDouble(1);
293  thumbnail->thumbnail_score.good_clipping = statement.ColumnBool(2);
294  thumbnail->thumbnail_score.at_top = statement.ColumnBool(3);
295  thumbnail->thumbnail_score.time_at_snapshot =
296      base::Time::FromInternalValue(statement.ColumnInt64(4));
297  return true;
298}
299
300int TopSitesDatabase::GetRowCount() {
301  int result = 0;
302  sql::Statement select_statement(db_->GetCachedStatement(
303      SQL_FROM_HERE,
304      "SELECT COUNT (url) FROM thumbnails"));
305  if (!select_statement) {
306    LOG(WARNING) << db_->GetErrorMessage();
307    return result;
308  }
309
310  if (select_statement.Step())
311    result = select_statement.ColumnInt(0);
312
313  return result;
314}
315
316int TopSitesDatabase::GetURLRank(const MostVisitedURL& url) {
317  int result = -1;
318  sql::Statement select_statement(db_->GetCachedStatement(
319      SQL_FROM_HERE,
320      "SELECT url_rank "
321      "FROM thumbnails WHERE url=?"));
322  if (!select_statement) {
323    LOG(WARNING) << db_->GetErrorMessage();
324    return result;
325  }
326
327  select_statement.BindString(0, url.url.spec());
328  if (select_statement.Step())
329    result = select_statement.ColumnInt(0);
330
331  return result;
332}
333
334// Remove the record for this URL. Returns true iff removed successfully.
335bool TopSitesDatabase::RemoveURL(const MostVisitedURL& url) {
336  int old_rank = GetURLRank(url);
337  if (old_rank < 0)
338    return false;
339
340  sql::Transaction transaction(db_.get());
341  transaction.Begin();
342  // Decrement all following ranks.
343  sql::Statement shift_statement(db_->GetCachedStatement(
344      SQL_FROM_HERE,
345      "UPDATE thumbnails "
346      "SET url_rank = url_rank - 1 "
347      "WHERE url_rank > ?"));
348  if (!shift_statement)
349    return false;
350  shift_statement.BindInt(0, old_rank);
351  shift_statement.Run();
352
353  sql::Statement delete_statement(
354      db_->GetCachedStatement(SQL_FROM_HERE,
355                              "DELETE FROM thumbnails WHERE url = ?"));
356  if (!delete_statement)
357    return false;
358  delete_statement.BindString(0, url.url.spec());
359  delete_statement.Run();
360
361  return transaction.Commit();
362}
363
364sql::Connection* TopSitesDatabase::CreateDB(const FilePath& db_name) {
365  scoped_ptr<sql::Connection> db(new sql::Connection());
366  // Settings copied from ThumbnailDatabase.
367  db->set_error_delegate(GetErrorHandlerForThumbnailDb());
368  db->set_page_size(4096);
369  db->set_cache_size(32);
370
371  if (!db->Open(db_name)) {
372    LOG(ERROR) << db->GetErrorMessage();
373    return NULL;
374  }
375
376  return db.release();
377}
378
379}  // namespace history
380