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