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
15 namespace history {
16
17 static const int kVersionNumber = 1;
18
TopSitesDatabase()19 TopSitesDatabase::TopSitesDatabase() : may_need_history_migration_(false) {
20 }
21
~TopSitesDatabase()22 TopSitesDatabase::~TopSitesDatabase() {
23 }
24
Init(const FilePath & db_name)25 bool 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
InitThumbnailTable()67 bool 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
GetPageThumbnails(MostVisitedURLList * urls,URLToImagesMap * thumbnails)86 void 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
GetRedirects(const MostVisitedURL & url)127 std::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
SetRedirects(const std::string & redirects,MostVisitedURL * url)135 void 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
SetPageThumbnail(const MostVisitedURL & url,int new_rank,const Images & thumbnail)143 void 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
UpdatePageThumbnail(const MostVisitedURL & url,const Images & thumbnail)160 void 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
AddPageThumbnail(const MostVisitedURL & url,int new_rank,const Images & thumbnail)187 void 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
UpdatePageRank(const MostVisitedURL & url,int new_rank)220 void 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.
UpdatePageRankNoTransaction(const MostVisitedURL & url,int new_rank)229 void 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
GetPageThumbnail(const GURL & url,Images * thumbnail)274 bool 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
GetRowCount()301 int 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
GetURLRank(const MostVisitedURL & url)317 int 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.
RemoveURL(const MostVisitedURL & url)336 bool 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
CreateDB(const FilePath & db_name)365 sql::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