Home | History | Annotate | Download | only in history 
      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 
     19 TopSitesDatabase::TopSitesDatabase() : may_need_history_migration_(false) {
     20 }
     21 
     22 TopSitesDatabase::~TopSitesDatabase() {
     23 }
     24 
     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 
     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 
     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
    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
    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 
    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 
    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 
    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 
    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.
    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 
    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 
    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 
    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.
    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 
    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