Bug 672956 - Add backend for expiring old history entries. r=lucasr
☠☠ backed out by 337cc85e941e ☠ ☠
authorWes Johnston <wjohnston@mozilla.com>
Fri, 19 Oct 2012 17:35:37 -0700
changeset 110971 62207930924a39a638d70aed986d57614b0d7f50
parent 110970 6fb79aaa409276b18f54cf76ae50f89bdb64bf14
child 110972 6931d5b6f05559ccfacbc39c61320afa8d368b03
push id93
push usernmatsakis@mozilla.com
push dateWed, 31 Oct 2012 21:26:57 +0000
reviewerslucasr
bugs672956
milestone19.0a1
Bug 672956 - Add backend for expiring old history entries. r=lucasr
mobile/android/base/db/BrowserContract.java.in
mobile/android/base/db/BrowserDB.java
mobile/android/base/db/BrowserProvider.java.in
mobile/android/base/db/LocalBrowserDB.java
--- a/mobile/android/base/db/BrowserContract.java.in
+++ b/mobile/android/base/db/BrowserContract.java.in
@@ -24,16 +24,22 @@ public class BrowserContract {
     public static final String PARAM_PROFILE = "profile";
     public static final String PARAM_PROFILE_PATH = "profilePath";
     public static final String PARAM_LIMIT = "limit";
     public static final String PARAM_IS_SYNC = "sync";
     public static final String PARAM_SHOW_DELETED = "show_deleted";
     public static final String PARAM_IS_TEST = "test";
     public static final String PARAM_INSERT_IF_NEEDED = "insert_if_needed";
     public static final String PARAM_INCREMENT_VISITS = "increment_visits";
+    public static final String PARAM_EXPIRE_PRIORITY = "priority";
+
+    static public enum ExpirePriority {
+        NORMAL,
+        AGGRESSIVE
+    }
 
     public interface CommonColumns {
         public static final String _ID = "_id";
     }
 
     public interface SyncColumns {
         public static final String GUID = "guid";
         public static final String DATE_CREATED = "created";
@@ -106,16 +112,17 @@ public class BrowserContract {
         public static final String TAGS = "tags";
         public static final String DESCRIPTION = "description";
         public static final String KEYWORD = "keyword";
     }
 
     public static final class History implements CommonColumns, URLColumns, HistoryColumns, ImageColumns, SyncColumns {
         private History() {}
         public static final Uri CONTENT_URI = Uri.withAppendedPath(AUTHORITY_URI, "history");
+        public static final Uri CONTENT_OLD_URI = Uri.withAppendedPath(AUTHORITY_URI, "history/old");
         public static final String CONTENT_TYPE = "vnd.android.cursor.dir/browser-history";
         public static final String CONTENT_ITEM_TYPE = "vnd.android.cursor.item/browser-history";
     }
 
     // Combined bookmarks and history
     public static final class Combined implements CommonColumns, URLColumns, HistoryColumns, ImageColumns  {
         private Combined() {}
         public static final Uri CONTENT_URI = Uri.withAppendedPath(AUTHORITY_URI, "combined");
--- a/mobile/android/base/db/BrowserDB.java
+++ b/mobile/android/base/db/BrowserDB.java
@@ -1,15 +1,17 @@
 /* -*- Mode: Java; c-basic-offset: 4; tab-width: 20; indent-tabs-mode: nil; -*-
  * This Source Code Form is subject to the terms of the Mozilla Public
  * License, v. 2.0. If a copy of the MPL was not distributed with this
  * file, You can obtain one at http://mozilla.org/MPL/2.0/. */
 
 package org.mozilla.gecko.db;
 
+import org.mozilla.gecko.db.BrowserContract.ExpirePriority;
+
 import android.content.ContentResolver;
 import android.database.ContentObserver;
 import android.database.Cursor;
 import android.graphics.drawable.BitmapDrawable;
 
 public class BrowserDB {
     public static String ABOUT_PAGES_URL_FILTER = "about:%";
 
@@ -38,16 +40,18 @@ public class BrowserDB {
 
         public void updateHistoryEntry(ContentResolver cr, String uri, String title,
                                        long date, int visits);
 
         public Cursor getAllVisitedHistory(ContentResolver cr);
 
         public Cursor getRecentHistory(ContentResolver cr, int limit);
 
+        public void expireHistory(ContentResolver cr, ExpirePriority priority);
+
         public void removeHistoryEntry(ContentResolver cr, int id);
 
         public void clearHistory(ContentResolver cr);
 
         public Cursor getBookmarksInFolder(ContentResolver cr, long folderId);
 
         public boolean isBookmark(ContentResolver cr, String uri);
 
@@ -119,16 +123,22 @@ public class BrowserDB {
     public static Cursor getAllVisitedHistory(ContentResolver cr) {
         return sDb.getAllVisitedHistory(cr);
     }
 
     public static Cursor getRecentHistory(ContentResolver cr, int limit) {
         return sDb.getRecentHistory(cr, limit);
     }
 
+    public static void expireHistory(ContentResolver cr, ExpirePriority priority) {
+        if (priority == null)
+            priority = ExpirePriority.NORMAL;
+        sDb.expireHistory(cr, priority);
+    }
+
     public static void removeHistoryEntry(ContentResolver cr, int id) {
         sDb.removeHistoryEntry(cr, id);
     }
 
     public static void clearHistory(ContentResolver cr) {
         sDb.clearHistory(cr);
     }
 
--- a/mobile/android/base/db/BrowserProvider.java.in
+++ b/mobile/android/base/db/BrowserProvider.java.in
@@ -29,16 +29,17 @@ import org.mozilla.gecko.db.BrowserContr
 import org.mozilla.gecko.db.BrowserContract.CommonColumns;
 import org.mozilla.gecko.db.BrowserContract.Control;
 import org.mozilla.gecko.db.BrowserContract.History;
 import org.mozilla.gecko.db.BrowserContract.Images;
 import org.mozilla.gecko.db.BrowserContract.Schema;
 import org.mozilla.gecko.db.BrowserContract.SyncColumns;
 import org.mozilla.gecko.db.BrowserContract.URLColumns;
 import org.mozilla.gecko.db.BrowserContract;
+import org.mozilla.gecko.db.BrowserDB;
 import org.mozilla.gecko.db.DBUtils;
 import org.mozilla.gecko.ProfileMigrator;
 import org.mozilla.gecko.sync.Utils;
 import org.mozilla.gecko.util.GeckoBackgroundThread;
 
 import android.app.SearchManager;
 import android.content.ContentProvider;
 import android.content.ContentUris;
@@ -77,16 +78,23 @@ public class BrowserProvider extends Con
     static final long DELETED_RECORDS_PURGE_LIMIT = 5;
 
     // How many records to reposition in a single query.
     // This should be less than the SQLite maximum number of query variables
     // (currently 999) divided by the number of variables used per positioning
     // query (currently 3).
     static final int MAX_POSITION_UPDATES_PER_QUERY = 100;
 
+    // Minimum number of records to keep when expiring history.
+    static final int DEFAULT_EXPIRY_RETAIN_COUNT = 2000;
+    static final int AGGRESSIVE_EXPIRY_RETAIN_COUNT = 500;
+
+    // Minimum duration to keep when expiring.
+    static final long DEFAULT_EXPIRY_PRESERVE_WINDOW = 1000L * 60L * 60L * 24L * 28L;     // Four weeks.
+
     static final String TABLE_BOOKMARKS = "bookmarks";
     static final String TABLE_HISTORY = "history";
     static final String TABLE_IMAGES = "images";
 
     static final String TABLE_BOOKMARKS_TMP = TABLE_BOOKMARKS + "_tmp";
     static final String TABLE_HISTORY_TMP = TABLE_HISTORY + "_tmp";
     static final String TABLE_IMAGES_TMP = TABLE_IMAGES + "_tmp";
 
@@ -99,16 +107,17 @@ public class BrowserProvider extends Con
     static final int BOOKMARKS_ID = 101;
     static final int BOOKMARKS_FOLDER_ID = 102;
     static final int BOOKMARKS_PARENT = 103;
     static final int BOOKMARKS_POSITIONS = 104;
 
     // History matches
     static final int HISTORY = 200;
     static final int HISTORY_ID = 201;
+    static final int HISTORY_OLD = 202;
 
     // Image matches
     static final int IMAGES = 300;
     static final int IMAGES_ID = 301;
 
     // Schema matches
     static final int SCHEMA = 400;
 
@@ -171,16 +180,17 @@ public class BrowserProvider extends Con
         map.put(Bookmarks.DATE_MODIFIED, Bookmarks.DATE_MODIFIED);
         map.put(Bookmarks.GUID, Bookmarks.GUID);
         map.put(Bookmarks.IS_DELETED, Bookmarks.IS_DELETED);
         BOOKMARKS_PROJECTION_MAP = Collections.unmodifiableMap(map);
 
         // History
         URI_MATCHER.addURI(BrowserContract.AUTHORITY, "history", HISTORY);
         URI_MATCHER.addURI(BrowserContract.AUTHORITY, "history/#", HISTORY_ID);
+        URI_MATCHER.addURI(BrowserContract.AUTHORITY, "history/old", HISTORY_OLD);
 
         map = new HashMap<String, String>();
         map.put(History._ID, History._ID);
         map.put(History.TITLE, History.TITLE);
         map.put(History.URL, History.URL);
         map.put(History.FAVICON, History.FAVICON);
         map.put(History.THUMBNAIL, History.THUMBNAIL);
         map.put(History.VISITS, History.VISITS);
@@ -1319,16 +1329,59 @@ public class BrowserProvider extends Con
                 debug("Removed old deleted item with URI: " + uriWithId);
             }
         } finally {
             if (cursor != null)
                 cursor.close();
         }
     }
 
+    /**
+     * Remove enough history items to bring the database count below <code>retain</code>,
+     * removing no items with a modified time after <code>keepAfter</code>.
+     *
+     * Provide <code>keepAfter</code> less than or equal to zero to skip that check.
+     *
+     * Items will be removed according to an approximate frecency calculation.
+     *
+     * Call this method within a transaction.
+     */
+    public void expireHistory(final SQLiteDatabase db, final int retain, final long keepAfter) {
+        final long rows = DatabaseUtils.queryNumEntries(db, TABLE_HISTORY);
+        if (retain >= rows) {
+            debug("Not expiring history: only have " + rows + " rows.");
+            return;
+        }
+
+        final long toRemove = rows - retain;
+        final long now = System.currentTimeMillis();
+        debug("Expiring at most " + toRemove + " rows earlier than " + keepAfter + ".");
+
+        final String age = "(" + Combined.DATE_LAST_VISITED + " - " + now + ") / 86400000";
+        final String sortOrder = Combined.VISITS + " * MAX(1, 100 * 225 / (" + age + "*" + age + " + 225)) ASC";
+
+        final String sql;
+        if (keepAfter > 0) {
+            // If we don't bind these paramaters dynamically, the WHERE clause here can return null
+            sql = "DELETE FROM " + TABLE_HISTORY + " " + 
+                  "WHERE MAX(" + History.DATE_LAST_VISITED + ", " + History.DATE_MODIFIED +") < " + keepAfter + " " +
+                  " AND " + History._ID + " " + "IN ( SELECT " +
+                    History._ID + " FROM " + TABLE_HISTORY + " " +
+                    "ORDER BY " + sortOrder + " LIMIT " + toRemove +
+                  ")";
+        } else {
+            sql = "DELETE FROM " + TABLE_HISTORY + " WHERE " + History._ID + " " +
+                  "IN ( SELECT " + History._ID + " FROM " + TABLE_HISTORY + " " +
+                  "ORDER BY " + sortOrder + " LIMIT " + toRemove + ")";
+        }
+
+        trace("Deleting using query: " + sql);
+        db.execSQL(sql);
+    }
+
     private boolean isCallerSync(Uri uri) {
         String isSync = uri.getQueryParameter(BrowserContract.PARAM_IS_SYNC);
         return !TextUtils.isEmpty(isSync);
     }
 
     private boolean isTest(Uri uri) {
         String isTest = uri.getQueryParameter(BrowserContract.PARAM_IS_TEST);
         return !TextUtils.isEmpty(isTest);
@@ -1407,34 +1460,34 @@ public class BrowserProvider extends Con
 
         final SQLiteDatabase db = getWritableDatabase(uri);
         int deleted = 0;
 
         if (Build.VERSION.SDK_INT >= 11) {
             trace("Beginning delete transaction: " + uri);
             db.beginTransaction();
             try {
-                deleted = deleteInTransaction(uri, selection, selectionArgs);
+                deleted = deleteInTransaction(db, uri, selection, selectionArgs);
                 db.setTransactionSuccessful();
                 trace("Successful delete transaction: " + uri);
             } finally {
                 db.endTransaction();
             }
         } else {
-            deleted = deleteInTransaction(uri, selection, selectionArgs);
+            deleted = deleteInTransaction(db, uri, selection, selectionArgs);
         }
-        
+
         if (deleted > 0)
             getContext().getContentResolver().notifyChange(uri, null);
 
         return deleted;
     }
 
     @SuppressWarnings("fallthrough")
-    public int deleteInTransaction(Uri uri, String selection, String[] selectionArgs) {
+    public int deleteInTransaction(SQLiteDatabase db, Uri uri, String selection, String[] selectionArgs) {
         trace("Calling delete in transaction on URI: " + uri);
 
         final int match = URI_MATCHER.match(uri);
         int deleted = 0;
 
         switch (match) {
             case BOOKMARKS_ID:
                 trace("Delete on BOOKMARKS_ID: " + uri);
@@ -1459,16 +1512,29 @@ public class BrowserProvider extends Con
                 // fall through
             case HISTORY: {
                 trace("Deleting history: " + uri);
                 deleted = deleteHistory(uri, selection, selectionArgs);
                 deleteUnusedImages(uri);
                 break;
             }
 
+            case HISTORY_OLD: {
+                String priority = uri.getQueryParameter(BrowserContract.PARAM_EXPIRE_PRIORITY);
+                long keepAfter = System.currentTimeMillis() - DEFAULT_EXPIRY_PRESERVE_WINDOW;
+                int retainCount = DEFAULT_EXPIRY_RETAIN_COUNT;
+
+                if (BrowserContract.ExpirePriority.AGGRESSIVE.toString().equals(priority)) {
+                    keepAfter = 0;
+                    retainCount = AGGRESSIVE_EXPIRY_RETAIN_COUNT;
+                }
+                expireHistory(db, retainCount, keepAfter);
+                break;
+            }
+
             case IMAGES_ID:
                 debug("Delete on IMAGES_ID: " + uri);
 
                 selection = DBUtils.concatenateWhere(selection, TABLE_IMAGES + "._id = ?");
                 selectionArgs = DBUtils.appendSelectionArgs(selectionArgs,
                         new String[] { Long.toString(ContentUris.parseId(uri)) });
                 // fall through
             case IMAGES: {
@@ -2068,20 +2134,16 @@ public class BrowserProvider extends Con
     long insertBookmark(Uri uri, ContentValues values) {
         // Generate values if not specified. Don't overwrite
         // if specified by caller.
         long now = System.currentTimeMillis();
         if (!values.containsKey(Bookmarks.DATE_CREATED)) {
             values.put(Bookmarks.DATE_CREATED, now);
         }
 
-        if (!values.containsKey(Bookmarks.DATE_MODIFIED)) {
-            values.put(Bookmarks.DATE_MODIFIED, now);
-        }
-
         if (!values.containsKey(Bookmarks.GUID)) {
             values.put(Bookmarks.GUID, Utils.generateGuid());
         }
 
         if (!values.containsKey(Bookmarks.POSITION)) {
             debug("Inserting bookmark with no position for URI");
             values.put(Bookmarks.POSITION,
                        Long.toString(BrowserContract.Bookmarks.DEFAULT_POSITION));
--- a/mobile/android/base/db/LocalBrowserDB.java
+++ b/mobile/android/base/db/LocalBrowserDB.java
@@ -7,16 +7,17 @@ package org.mozilla.gecko.db;
 
 import org.mozilla.gecko.db.BrowserContract.Bookmarks;
 import org.mozilla.gecko.db.BrowserContract.Combined;
 import org.mozilla.gecko.db.BrowserContract.History;
 import org.mozilla.gecko.db.BrowserContract.ImageColumns;
 import org.mozilla.gecko.db.BrowserContract.Images;
 import org.mozilla.gecko.db.BrowserContract.SyncColumns;
 import org.mozilla.gecko.db.BrowserContract.URLColumns;
+import org.mozilla.gecko.db.BrowserContract.ExpirePriority;
 
 import android.content.ContentProviderOperation;
 import android.content.ContentResolver;
 import android.content.ContentValues;
 import android.database.ContentObserver;
 import android.database.Cursor;
 import android.database.CursorWrapper;
 import android.graphics.Bitmap;
@@ -49,16 +50,17 @@ public class LocalBrowserDB implements B
 
     // Use wrapped Boolean so that we can have a null state
     private Boolean mDesktopBookmarksExist;
     private Boolean mReadingListItemsExist;
 
     private final Uri mBookmarksUriWithProfile;
     private final Uri mParentsUriWithProfile;
     private final Uri mHistoryUriWithProfile;
+    private final Uri mHistoryExpireUriWithProfile;
     private final Uri mImagesUriWithProfile;
     private final Uri mCombinedUriWithProfile;
     private final Uri mDeletedHistoryUriWithProfile;
     private final Uri mUpdateHistoryUriWithProfile;
 
     private static final String[] DEFAULT_BOOKMARK_COLUMNS =
             new String[] { Bookmarks._ID,
                            Bookmarks.GUID,
@@ -73,16 +75,17 @@ public class LocalBrowserDB implements B
         mProfile = profile;
         mFolderIdMap = new HashMap<String, Long>();
         mDesktopBookmarksExist = null;
         mReadingListItemsExist = null;
 
         mBookmarksUriWithProfile = appendProfile(Bookmarks.CONTENT_URI);
         mParentsUriWithProfile = appendProfile(Bookmarks.PARENTS_CONTENT_URI);
         mHistoryUriWithProfile = appendProfile(History.CONTENT_URI);
+        mHistoryExpireUriWithProfile = appendProfile(History.CONTENT_OLD_URI);
         mImagesUriWithProfile = appendProfile(Images.CONTENT_URI);
         mCombinedUriWithProfile = appendProfile(Combined.CONTENT_URI);
 
         mDeletedHistoryUriWithProfile = mHistoryUriWithProfile.buildUpon().
             appendQueryParameter(BrowserContract.PARAM_SHOW_DELETED, "1").build();
 
         mUpdateHistoryUriWithProfile = mHistoryUriWithProfile.buildUpon().
             appendQueryParameter(BrowserContract.PARAM_INCREMENT_VISITS, "true").
@@ -280,16 +283,22 @@ public class LocalBrowserDB implements B
                                            Combined.VISITS },
                             History.DATE_LAST_VISITED + " > 0",
                             null,
                             History.DATE_LAST_VISITED + " DESC");
 
         return new LocalDBCursor(c);
     }
 
+    public void expireHistory(ContentResolver cr, ExpirePriority priority) {
+        Uri url = mHistoryExpireUriWithProfile;
+        url = url.buildUpon().appendQueryParameter(BrowserContract.PARAM_EXPIRE_PRIORITY, priority.toString()).build();
+        cr.delete(url, null, null);
+    }
+
     public void removeHistoryEntry(ContentResolver cr, int id) {
         cr.delete(mHistoryUriWithProfile,
                   History._ID + " = ?",
                   new String[] { String.valueOf(id) });
     }
 
     public void clearHistory(ContentResolver cr) {
         cr.delete(mHistoryUriWithProfile, null, null);