1 // Copyright (c) 2012 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.
5 #include "base/file_util.h"
6 #include "base/memory/ref_counted.h"
7 #include "base/strings/string_split.h"
8 #include "base/strings/string_util.h"
9 #include "chrome/browser/history/history_types.h"
10 #include "chrome/browser/history/top_sites.h"
11 #include "chrome/browser/history/top_sites_database.h"
12 #include "chrome/common/thumbnail_score.h"
13 #include "sql/connection.h"
14 #include "sql/transaction.h"
16 // Description of database table:
19 // url URL of the sites for which we have a thumbnail.
20 // url_rank Index of the URL in that thumbnail, 0-based. The thumbnail
21 // with the highest rank will be the next one evicted. Forced
22 // thumbnails have a rank of -1.
23 // title The title to display under that thumbnail.
24 // redirects A space separated list of URLs that are known to redirect
26 // boring_score How "boring" that thumbnail is. See ThumbnailScore.
27 // good_clipping True if the thumbnail was clipped from the bottom, keeping
28 // the entire width of the window. See ThumbnailScore.
29 // at_top True if the thumbnail was captured at the top of the
31 // last_updated The time at which this thumbnail was last updated.
32 // load_completed True if the thumbnail was captured after the page load was
34 // last_forced If this is a forced thumbnail, records the last time it
35 // was forced. If it's not a forced thumbnail, 0.
39 // TODO(beaudoin): Fill revision/date details of Version 3 after landing.
40 // Version 3: by beaudoin@chromium.org
41 // Version 2: eb0b24e6/r87284 by satorux@chromium.org on 2011-05-31
42 // Version 1: 809cc4d8/r64072 by sky@chromium.org on 2010-10-27
44 // From the version 1 to 2, one column was added. Old versions of Chrome
45 // should be able to read version 2 files just fine. Same thing for version 2
47 // NOTE(shess): When changing the version, add a new golden file for
48 // the new version and a test to verify that Init() works with it.
49 static const int kVersionNumber = 3;
51 TopSitesDatabase::TopSitesDatabase() {
54 TopSitesDatabase::~TopSitesDatabase() {
57 bool TopSitesDatabase::Init(const base::FilePath& db_name) {
58 bool file_existed = base::PathExists(db_name);
60 db_.reset(CreateDB(db_name));
64 bool does_meta_exist = sql::MetaTable::DoesTableExist(db_.get());
65 if (!does_meta_exist && file_existed) {
66 // If the meta file doesn't exist, this version is old. We could remove all
67 // the entries as they are no longer applicable, but it's safest to just
68 // remove the file and start over.
70 if (!sql::Connection::Delete(db_name)) {
71 LOG(ERROR) << "unable to delete old TopSites file";
74 db_.reset(CreateDB(db_name));
79 // Scope initialization in a transaction so we can't be partially
81 sql::Transaction transaction(db_.get());
84 if (!meta_table_.Init(db_.get(), kVersionNumber, kVersionNumber))
87 if (!InitThumbnailTable())
90 if (meta_table_.GetVersionNumber() == 1) {
91 if (!UpgradeToVersion2()) {
92 LOG(WARNING) << "Unable to upgrade top sites database to version 2.";
97 if (meta_table_.GetVersionNumber() == 2) {
98 if (!UpgradeToVersion3()) {
99 LOG(WARNING) << "Unable to upgrade top sites database to version 3.";
105 if (meta_table_.GetVersionNumber() != kVersionNumber)
108 // Initialization is complete.
109 if (!transaction.Commit())
115 bool TopSitesDatabase::InitThumbnailTable() {
116 if (!db_->DoesTableExist("thumbnails")) {
117 if (!db_->Execute("CREATE TABLE thumbnails ("
118 "url LONGVARCHAR PRIMARY KEY,"
122 "redirects LONGVARCHAR,"
123 "boring_score DOUBLE DEFAULT 1.0,"
124 "good_clipping INTEGER DEFAULT 0,"
125 "at_top INTEGER DEFAULT 0,"
126 "last_updated INTEGER DEFAULT 0,"
127 "load_completed INTEGER DEFAULT 0,"
128 "last_forced INTEGER DEFAULT 0)")) {
129 LOG(WARNING) << db_->GetErrorMessage();
136 bool TopSitesDatabase::UpgradeToVersion2() {
137 // Add 'load_completed' column.
139 "ALTER TABLE thumbnails ADD load_completed INTEGER DEFAULT 0")) {
143 meta_table_.SetVersionNumber(2);
147 bool TopSitesDatabase::UpgradeToVersion3() {
148 // Add 'last_forced' column.
150 "ALTER TABLE thumbnails ADD last_forced INTEGER DEFAULT 0")) {
154 meta_table_.SetVersionNumber(3);
158 void TopSitesDatabase::GetPageThumbnails(MostVisitedURLList* urls,
159 URLToImagesMap* thumbnails) {
160 sql::Statement statement(db_->GetCachedStatement(
162 "SELECT url, url_rank, title, thumbnail, redirects, "
163 "boring_score, good_clipping, at_top, last_updated, load_completed, "
164 "last_forced FROM thumbnails ORDER BY url_rank, last_forced"));
166 if (!statement.is_valid()) {
167 LOG(WARNING) << db_->GetErrorMessage();
174 while (statement.Step()) {
175 // Results are sorted by url_rank. For forced thumbnails with url_rank = -1,
176 // thumbnails are sorted by last_forced.
178 GURL gurl(statement.ColumnString(0));
180 url.title = statement.ColumnString16(2);
181 url.last_forced_time =
182 base::Time::FromInternalValue(statement.ColumnInt64(10));
183 std::string redirects = statement.ColumnString(4);
184 SetRedirects(redirects, &url);
185 urls->push_back(url);
187 std::vector<unsigned char> data;
188 statement.ColumnBlobAsVector(3, &data);
191 thumbnail.thumbnail = base::RefCountedBytes::TakeVector(&data);
192 thumbnail.thumbnail_score.boring_score = statement.ColumnDouble(5);
193 thumbnail.thumbnail_score.good_clipping = statement.ColumnBool(6);
194 thumbnail.thumbnail_score.at_top = statement.ColumnBool(7);
195 thumbnail.thumbnail_score.time_at_snapshot =
196 base::Time::FromInternalValue(statement.ColumnInt64(8));
197 thumbnail.thumbnail_score.load_completed = statement.ColumnBool(9);
198 (*thumbnails)[gurl] = thumbnail;
203 std::string TopSitesDatabase::GetRedirects(const MostVisitedURL& url) {
204 std::vector<std::string> redirects;
205 for (size_t i = 0; i < url.redirects.size(); i++)
206 redirects.push_back(url.redirects[i].spec());
207 return JoinString(redirects, ' ');
211 void TopSitesDatabase::SetRedirects(const std::string& redirects,
212 MostVisitedURL* url) {
213 std::vector<std::string> redirects_vector;
214 base::SplitStringAlongWhitespace(redirects, &redirects_vector);
215 for (size_t i = 0; i < redirects_vector.size(); ++i)
216 url->redirects.push_back(GURL(redirects_vector[i]));
219 void TopSitesDatabase::SetPageThumbnail(const MostVisitedURL& url,
221 const Images& thumbnail) {
222 sql::Transaction transaction(db_.get());
225 int rank = GetURLRank(url);
226 if (rank == kRankOfNonExistingURL) {
227 AddPageThumbnail(url, new_rank, thumbnail);
229 UpdatePageRankNoTransaction(url, new_rank);
230 UpdatePageThumbnail(url, thumbnail);
233 transaction.Commit();
236 bool TopSitesDatabase::UpdatePageThumbnail(
237 const MostVisitedURL& url, const Images& thumbnail) {
238 sql::Statement statement(db_->GetCachedStatement(
240 "UPDATE thumbnails SET "
241 "title = ?, thumbnail = ?, redirects = ?, "
242 "boring_score = ?, good_clipping = ?, at_top = ?, last_updated = ?, "
243 "load_completed = ?, last_forced = ?"
245 statement.BindString16(0, url.title);
246 if (thumbnail.thumbnail.get() && thumbnail.thumbnail->front()) {
247 statement.BindBlob(1, thumbnail.thumbnail->front(),
248 static_cast<int>(thumbnail.thumbnail->size()));
250 statement.BindString(2, GetRedirects(url));
251 const ThumbnailScore& score = thumbnail.thumbnail_score;
252 statement.BindDouble(3, score.boring_score);
253 statement.BindBool(4, score.good_clipping);
254 statement.BindBool(5, score.at_top);
255 statement.BindInt64(6, score.time_at_snapshot.ToInternalValue());
256 statement.BindBool(7, score.load_completed);
257 statement.BindInt64(8, url.last_forced_time.ToInternalValue());
258 statement.BindString(9, url.url.spec());
260 return statement.Run();
263 void TopSitesDatabase::AddPageThumbnail(const MostVisitedURL& url,
265 const Images& thumbnail) {
266 sql::Statement statement(db_->GetCachedStatement(
268 "INSERT OR REPLACE INTO thumbnails "
269 "(url, url_rank, title, thumbnail, redirects, "
270 "boring_score, good_clipping, at_top, last_updated, load_completed, "
271 "last_forced) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"));
272 statement.BindString(0, url.url.spec());
273 statement.BindInt(1, kRankOfForcedURL); // Fist make it a forced thumbnail.
274 statement.BindString16(2, url.title);
275 if (thumbnail.thumbnail.get() && thumbnail.thumbnail->front()) {
276 statement.BindBlob(3, thumbnail.thumbnail->front(),
277 static_cast<int>(thumbnail.thumbnail->size()));
279 statement.BindString(4, GetRedirects(url));
280 const ThumbnailScore& score = thumbnail.thumbnail_score;
281 statement.BindDouble(5, score.boring_score);
282 statement.BindBool(6, score.good_clipping);
283 statement.BindBool(7, score.at_top);
284 statement.BindInt64(8, score.time_at_snapshot.ToInternalValue());
285 statement.BindBool(9, score.load_completed);
286 int64 last_forced = url.last_forced_time.ToInternalValue();
287 DCHECK((last_forced == 0) == (new_rank != kRankOfForcedURL))
288 << "Thumbnail without a forced time stamp has a forced rank, or the "
290 statement.BindInt64(10, last_forced);
291 if (!statement.Run())
294 // Update rank if this is not a forced thumbnail.
295 if (new_rank != kRankOfForcedURL)
296 UpdatePageRankNoTransaction(url, new_rank);
299 void TopSitesDatabase::UpdatePageRank(const MostVisitedURL& url,
301 DCHECK((url.last_forced_time.ToInternalValue() == 0) ==
302 (new_rank != kRankOfForcedURL))
303 << "Thumbnail without a forced time stamp has a forced rank, or the "
305 sql::Transaction transaction(db_.get());
307 UpdatePageRankNoTransaction(url, new_rank);
308 transaction.Commit();
311 // Caller should have a transaction open.
312 void TopSitesDatabase::UpdatePageRankNoTransaction(
313 const MostVisitedURL& url, int new_rank) {
314 DCHECK_GT(db_->transaction_nesting(), 0);
316 int prev_rank = GetURLRank(url);
317 if (prev_rank == kRankOfNonExistingURL) {
318 LOG(WARNING) << "Updating rank of an unknown URL: " << url.url.spec();
323 if (prev_rank > new_rank) {
324 if (new_rank == kRankOfForcedURL) {
325 // From non-forced to forced, shift down.
327 // -1, -1, -1, 0, 1, [2 -> -1], [3 -> 2], [4 -> 3]
328 sql::Statement shift_statement(db_->GetCachedStatement(
331 "SET url_rank = url_rank - 1 "
332 "WHERE url_rank > ?"));
333 shift_statement.BindInt(0, prev_rank);
334 shift_statement.Run();
336 // From non-forced to non-forced, shift up.
338 // -1, -1, -1, 0, [1 -> 2], [2 -> 3], [3 -> 1], 4
339 sql::Statement shift_statement(db_->GetCachedStatement(
342 "SET url_rank = url_rank + 1 "
343 "WHERE url_rank >= ? AND url_rank < ?"));
344 shift_statement.BindInt(0, new_rank);
345 shift_statement.BindInt(1, prev_rank);
346 shift_statement.Run();
348 } else if (prev_rank < new_rank) {
349 if (prev_rank == kRankOfForcedURL) {
350 // From non-forced to forced, shift up.
352 // -1, [-1 -> 2], -1, 0, 1, [2 -> 3], [3 -> 4], [4 -> 5]
353 sql::Statement shift_statement(db_->GetCachedStatement(
356 "SET url_rank = url_rank + 1 "
357 "WHERE url_rank >= ?"));
358 shift_statement.BindInt(0, new_rank);
359 shift_statement.Run();
361 // From non-forced to non-forced, shift down.
363 // -1, -1, -1, 0, [1 -> 3], [2 -> 1], [3 -> 2], 4
364 sql::Statement shift_statement(db_->GetCachedStatement(
367 "SET url_rank = url_rank - 1 "
368 "WHERE url_rank > ? AND url_rank <= ?"));
369 shift_statement.BindInt(0, prev_rank);
370 shift_statement.BindInt(1, new_rank);
371 shift_statement.Run();
375 // Set the url's rank and last_forced, since the latter changes when a URL
376 // goes from forced to non-forced and vice-versa.
377 sql::Statement set_statement(db_->GetCachedStatement(
380 "SET url_rank = ?, last_forced = ? "
382 set_statement.BindInt(0, new_rank);
383 set_statement.BindInt64(1, url.last_forced_time.ToInternalValue());
384 set_statement.BindString(2, url.url.spec());
388 bool TopSitesDatabase::GetPageThumbnail(const GURL& url,
390 sql::Statement statement(db_->GetCachedStatement(
392 "SELECT thumbnail, boring_score, good_clipping, at_top, last_updated "
393 "FROM thumbnails WHERE url=?"));
394 statement.BindString(0, url.spec());
395 if (!statement.Step())
398 std::vector<unsigned char> data;
399 statement.ColumnBlobAsVector(0, &data);
400 thumbnail->thumbnail = base::RefCountedBytes::TakeVector(&data);
401 thumbnail->thumbnail_score.boring_score = statement.ColumnDouble(1);
402 thumbnail->thumbnail_score.good_clipping = statement.ColumnBool(2);
403 thumbnail->thumbnail_score.at_top = statement.ColumnBool(3);
404 thumbnail->thumbnail_score.time_at_snapshot =
405 base::Time::FromInternalValue(statement.ColumnInt64(4));
409 int TopSitesDatabase::GetURLRank(const MostVisitedURL& url) {
410 sql::Statement select_statement(db_->GetCachedStatement(
413 "FROM thumbnails WHERE url=?"));
414 select_statement.BindString(0, url.url.spec());
415 if (select_statement.Step())
416 return select_statement.ColumnInt(0);
418 return kRankOfNonExistingURL;
421 // Remove the record for this URL. Returns true iff removed successfully.
422 bool TopSitesDatabase::RemoveURL(const MostVisitedURL& url) {
423 int old_rank = GetURLRank(url);
424 if (old_rank == kRankOfNonExistingURL)
427 sql::Transaction transaction(db_.get());
429 if (old_rank != kRankOfForcedURL) {
430 // Decrement all following ranks.
431 sql::Statement shift_statement(db_->GetCachedStatement(
434 "SET url_rank = url_rank - 1 "
435 "WHERE url_rank > ?"));
436 shift_statement.BindInt(0, old_rank);
438 if (!shift_statement.Run())
442 sql::Statement delete_statement(
443 db_->GetCachedStatement(SQL_FROM_HERE,
444 "DELETE FROM thumbnails WHERE url = ?"));
445 delete_statement.BindString(0, url.url.spec());
447 if (!delete_statement.Run())
450 return transaction.Commit();
453 sql::Connection* TopSitesDatabase::CreateDB(const base::FilePath& db_name) {
454 scoped_ptr<sql::Connection> db(new sql::Connection());
455 // Settings copied from ThumbnailDatabase.
456 db->set_histogram_tag("TopSites");
457 db->set_page_size(4096);
458 db->set_cache_size(32);
460 if (!db->Open(db_name)) {
461 LOG(ERROR) << db->GetErrorMessage();
468 } // namespace history