[Thumbnails DB] Allow setting last_requested time when accessing favicons.

components/history/core/browser/thumbnail_database.cc

 // Copyright (c) 2012 The Chromium Authors. All rights reserved.
 // Use of this source code is governed by a BSD-style license that can be
 // found in the LICENSE file.
 
 #include "components/history/core/browser/thumbnail_database.h"
 
 #include <stddef.h>
 #include <stdint.h>
 #include <algorithm>
 #include <string>
@@ skipping 56 matching lines @@
 //                    redownloaded from the web.
 //   image_data       PNG encoded data of the favicon.
 //   width            Pixel width of |image_data|.
 //   height           Pixel height of |image_data|.
 //   last_requested   The time at which this bitmap was last requested. This is
 //                    used to determine the priority with which the bitmap
 //                    should be retained on cleanup.
 
 namespace {
 
+const int kFaviconUpdateLastRequestedAfterDays = 14;
+
 // For this database, schema migrations are deprecated after two
 // years.  This means that the oldest non-deprecated version should be
 // two years old or greater (thus the migrations to get there are
 // older).  Databases containing deprecated versions will be cleared
 // at startup.  Since this database is a cache, losing old data is not
 // fatal (in fact, very old data may be expired immediately at startup
 // anyhow).
 
 // Version 8: 982ef2c1/r323176 by rogerm@chromium.org on 2015-03-31
 // Version 7: 911a634d/r209424 by qsr@chromium.org on 2013-07-01
@@ skipping 173 matching lines @@
     // test-expectation framework that the error was handled.
     ignore_result(sql::Connection::IsExpectedSqliteError(extended_error));
     return;
   }
 
   // The default handling is to assert on debug and to ignore on release.
   if (!sql::Connection::IsExpectedSqliteError(extended_error))
     DLOG(FATAL) << db->GetErrorMessage();
 }
 
+bool SetFaviconBitmapLastRequestedTime(sql::Connection* db,
+                                       FaviconBitmapID bitmap_id,
+                                       base::Time access_time) {
+  DCHECK(bitmap_id);
+  sql::Statement statement(db->GetCachedStatement(
+      SQL_FROM_HERE, "UPDATE favicon_bitmaps SET last_requested=? WHERE id=?"));
+  statement.BindInt64(0, access_time.ToInternalValue());
+  statement.BindInt64(1, bitmap_id);
+  return statement.Run();
+}
+
+void DeleteOrphanagedFavicons(sql::Connection* db) {
+  sql::Statement favicons(db->GetCachedStatement(
+      SQL_FROM_HERE,
+      "DELETE FROM favicons WHERE NOT EXISTS (SELECT id FROM favicon_bitmaps "
+      "WHERE favicon_bitmaps.icon_id = favicons.id)"));
+  favicons.Run();
+}
+
+void DeleteOrphanagedMappings(sql::Connection* db) {
+  sql::Statement mappings(db->GetCachedStatement(
+      SQL_FROM_HERE,
+      "DELETE FROM icon_mapping WHERE NOT EXISTS (SELECT id FROM favicons "
+      "WHERE favicons.id = icon_mapping.icon_id)"));
+  mappings.Run();
+}
+
 }  // namespace
 
 ThumbnailDatabase::IconMappingEnumerator::IconMappingEnumerator() {
 }
 
 ThumbnailDatabase::IconMappingEnumerator::~IconMappingEnumerator() {
 }
 
 bool ThumbnailDatabase::IconMappingEnumerator::GetNextIconMapping(
     IconMapping* icon_mapping) {
@@ skipping 121 matching lines @@
 void ThumbnailDatabase::Vacuum() {
   DCHECK(db_.transaction_nesting() == 0) <<
       "Can not have a transaction when vacuuming.";
   ignore_result(db_.Execute("VACUUM"));
 }
 
 void ThumbnailDatabase::TrimMemory(bool aggressively) {
   db_.TrimMemory(aggressively);
 }
 
+void ThumbnailDatabase::CleanUnusedFavicons(base::Time expiration_threshold) {
+  // Delete bitmaps that have not been accessed for a while.
+  sql::Statement bitmaps(db_.GetCachedStatement(
+      SQL_FROM_HERE, "DELETE FROM favicon_bitmaps WHERE last_requested<?"));
+  bitmaps.BindInt64(0, expiration_threshold.ToInternalValue());
+  bitmaps.Run();
+
+  DeleteOrphanagedFavicons(&db_);
+  DeleteOrphanagedMappings(&db_);

[pkotwicz, 2017/05/04 18:34:19]

Drive by: Is it possible that this will end up deleting bookmark favicons on
Android? In particular, this might end up deleting the nice large bookmark
favicons and keep the version which was stored in the database via
MergeFavicon()

[jkrcal, 2017/05/05 16:36:46]

Huh, correct! I've forgotten about that. I will address this problem in the next
patch set. Thanks for spotting this!

[jkrcal, 2017/05/15 14:26:57]

I've reimplemented the code in order to filter out bookmarks. The favicons are
now deleted one-by-one. I am wondering how to improve worst-case performance for
deleting a set of favicons by id. Do you think that
 - setting up a transaction or
 - deleting in batches by "WHERE id IN (?,?,?,?,?,?,?,..,?)" with e.g. up to 100
ids per query
would be worth it?
 

+}
+
 bool ThumbnailDatabase::GetFaviconBitmapIDSizes(
     favicon_base::FaviconID icon_id,
     std::vector<FaviconBitmapIDSize>* bitmap_id_sizes) {
   DCHECK(icon_id);
   sql::Statement statement(db_.GetCachedStatement(SQL_FROM_HERE,
       "SELECT id, width, height FROM favicon_bitmaps WHERE icon_id=?"));
   statement.BindInt64(0, icon_id);
 
   bool result = false;
   while (statement.Step()) {
@@ skipping 39 matching lines @@
                                           statement.ColumnInt(4));
     favicon_bitmap.last_requested =
         base::Time::FromInternalValue(statement.ColumnInt64(5));
     favicon_bitmaps->push_back(favicon_bitmap);
   }
   return result;
 }
 
 bool ThumbnailDatabase::GetFaviconBitmap(
     FaviconBitmapID bitmap_id,
+    base::Time access_time,
     base::Time* last_updated,
-    base::Time* last_requested,
     scoped_refptr<base::RefCountedMemory>* png_icon_data,
     gfx::Size* pixel_size) {
   DCHECK(bitmap_id);
   sql::Statement statement(db_.GetCachedStatement(SQL_FROM_HERE,
       "SELECT last_updated, image_data, width, height, last_requested "
       "FROM favicon_bitmaps WHERE id=?"));
   statement.BindInt64(0, bitmap_id);
 
   if (!statement.Step())
     return false;
 
   if (last_updated)
     *last_updated = base::Time::FromInternalValue(statement.ColumnInt64(0));
 
   if (png_icon_data && statement.ColumnByteLength(1) > 0) {
     scoped_refptr<base::RefCountedBytes> data(new base::RefCountedBytes());
     statement.ColumnBlobAsVector(1, &data->data());
     *png_icon_data = data;
   }
 
   if (pixel_size) {
     *pixel_size = gfx::Size(statement.ColumnInt(2),
                             statement.ColumnInt(3));
   }
 
-  if (last_requested)
-    *last_requested = base::Time::FromInternalValue(statement.ColumnInt64(4));
+  base::Time last_requested =
+      base::Time::FromInternalValue(statement.ColumnInt64(4));
+  if (access_time - last_requested >
+      base::TimeDelta::FromDays(kFaviconUpdateLastRequestedAfterDays)) {
+    SetFaviconBitmapLastRequestedTime(&db_, bitmap_id, access_time);
+  }
 
   return true;
 }
 
 FaviconBitmapID ThumbnailDatabase::AddFaviconBitmap(
     favicon_base::FaviconID icon_id,
     const scoped_refptr<base::RefCountedMemory>& icon_data,
     base::Time time,
     const gfx::Size& pixel_size) {
   DCHECK(icon_id);
@@ skipping 39 matching lines @@
     FaviconBitmapID bitmap_id,
     base::Time time) {
   DCHECK(bitmap_id);
   sql::Statement statement(db_.GetCachedStatement(SQL_FROM_HERE,
       "UPDATE favicon_bitmaps SET last_updated=? WHERE id=?"));
   statement.BindInt64(0, time.ToInternalValue());
   statement.BindInt64(1, bitmap_id);
   return statement.Run();
 }
 
-bool ThumbnailDatabase::SetFaviconBitmapLastRequestedTime(
-    FaviconBitmapID bitmap_id,
-    base::Time time) {
-  DCHECK(bitmap_id);
-  sql::Statement statement(db_.GetCachedStatement(SQL_FROM_HERE,
-      "UPDATE favicon_bitmaps SET last_requested=? WHERE id=?"));
-  statement.BindInt64(0, time.ToInternalValue());
-  statement.BindInt64(1, bitmap_id);
-  return statement.Run();
-}
-
 bool ThumbnailDatabase::DeleteFaviconBitmap(FaviconBitmapID bitmap_id) {
   sql::Statement statement(db_.GetCachedStatement(SQL_FROM_HERE,
       "DELETE FROM favicon_bitmaps WHERE id=?"));
   statement.BindInt64(0, bitmap_id);
   return statement.Run();
 }
 
 bool ThumbnailDatabase::SetFaviconOutOfDate(favicon_base::FaviconID icon_id) {
   sql::Statement statement(db_.GetCachedStatement(SQL_FROM_HERE,
       "UPDATE favicon_bitmaps SET last_updated=? WHERE icon_id=?"));
@@ skipping 488 matching lines @@
   meta_table_.SetVersionNumber(8);
   meta_table_.SetCompatibleVersionNumber(std::min(8, kCompatibleVersionNumber));
   return true;
 }
 
 bool ThumbnailDatabase::IsFaviconDBStructureIncorrect() {
   return !db_.IsSQLValid("SELECT id, url, icon_type FROM favicons");
 }
 
 }  // namespace history