Bug 721731 - Create combined bookmarks/history view for top sites/awesomebar queries. r=mfinkle
authorMargaret Leibovic <margaret.leibovic@gmail.com>
Fri, 23 Mar 2012 15:52:42 -0700
changeset 90666 074de1a022db13b52ca309e36b3a59afa10f0cf2
parent 90665 c5016d59add6fd5d4786fbe868902b63889cb044
child 90667 a2ba1894d61bb3f0365c34d95b071e8cacc2108e
push id1069
push usermfinkle@mozilla.com
push dateWed, 04 Apr 2012 14:22:33 +0000
treeherdermozilla-aurora@883536e3b5da [default view] [failures only]
perfherder[talos] [build metrics] [platform microbench] (compared to previous push)
reviewersmfinkle
bugs721731
milestone13.0a2
Bug 721731 - Create combined bookmarks/history view for top sites/awesomebar queries. r=mfinkle
mobile/android/base/db/BrowserContract.java.in
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
@@ -77,16 +77,21 @@ public class BrowserContract {
         public static final String TITLE = "title";
     }
 
     public interface ImageColumns {
         public static final String FAVICON = "favicon";
         public static final String THUMBNAIL = "thumbnail";
     }
 
+    public interface HistoryColumns {
+        public static final String DATE_LAST_VISITED = "date";
+        public static final String VISITS = "visits";
+    }
+
     public interface DeletedColumns {
         public static final String ID = "id";
         public static final String GUID = "guid";
         public static final String TIME_DELETED = "timeDeleted";
     }
 
     public static final class Images implements CommonColumns, ImageColumns, SyncColumns {
         private Images() {}
@@ -124,24 +129,30 @@ public class BrowserContract {
         public static final String TYPE = "type";
         public static final String PARENT = "parent";
         public static final String POSITION = "position";
         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, ImageColumns, SyncColumns {
+    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 String CONTENT_TYPE = "vnd.android.cursor.dir/browser-history";
         public static final String CONTENT_ITEM_TYPE = "vnd.android.cursor.item/browser-history";
+    }
 
-        public static final String DATE_LAST_VISITED = "date";
-        public static final String VISITS = "visits";
+    // 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");
+
+        public static final String BOOKMARK_ID = "bookmark_id";
+        public static final String HISTORY_ID = "history_id";
     }
 
     public static final class Schema {
         private Schema() {}
         public static final Uri CONTENT_URI = Uri.withAppendedPath(AUTHORITY_URI, "schema");
 
         public static final String VERSION = "version";
     }
--- a/mobile/android/base/db/BrowserProvider.java.in
+++ b/mobile/android/base/db/BrowserProvider.java.in
@@ -14,16 +14,17 @@ import java.util.HashMap;
 import java.util.List;
 import java.util.Map;
 import java.util.Random;
 
 import org.mozilla.gecko.GeckoBackgroundThread;
 import org.mozilla.gecko.GeckoProfile;
 import org.mozilla.gecko.R;
 import org.mozilla.gecko.db.BrowserContract.Bookmarks;
+import org.mozilla.gecko.db.BrowserContract.Combined;
 import org.mozilla.gecko.db.BrowserContract.CommonColumns;
 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.DBUtils;
@@ -50,32 +51,33 @@ import android.text.TextUtils;
 import android.util.Log;
 
 public class BrowserProvider extends ContentProvider {
     private static final String LOGTAG = "GeckoBrowserProvider";
     private Context mContext;
 
     static final String DATABASE_NAME = "browser.db";
 
-    static final int DATABASE_VERSION = 4;
+    static final int DATABASE_VERSION = 5;
 
     // Maximum age of deleted records to be cleaned up (20 days in ms)
     static final long MAX_AGE_OF_DELETED_RECORDS = 86400000 * 20;
 
     // Number of records marked as deleted to be removed
     static final long DELETED_RECORDS_PURGE_LIMIT = 5;
 
     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 VIEW_BOOKMARKS_WITH_IMAGES = "bookmarks_with_images";
     static final String VIEW_HISTORY_WITH_IMAGES = "history_with_images";
+    static final String VIEW_COMBINED_WITH_IMAGES = "combined_with_images";
 
     // Bookmark matches
     static final int BOOKMARKS = 100;
     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;
 
@@ -85,16 +87,19 @@ public class BrowserProvider extends Con
 
     // Image matches
     static final int IMAGES = 300;
     static final int IMAGES_ID = 301;
 
     // Schema matches
     static final int SCHEMA = 400;
 
+    // Combined bookmarks and history matches
+    static final int COMBINED = 500;
+
     static final String DEFAULT_BOOKMARKS_SORT_ORDER = Bookmarks.TYPE
             + " ASC, " + Bookmarks.POSITION + " ASC, " + Bookmarks._ID
             + " ASC";
 
     static final String DEFAULT_HISTORY_SORT_ORDER = History.DATE_LAST_VISITED + " DESC";
 
     static final String TABLE_BOOKMARKS_JOIN_IMAGES = TABLE_BOOKMARKS + " LEFT OUTER JOIN " +
             TABLE_IMAGES + " ON " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " = " +
@@ -104,16 +109,17 @@ public class BrowserProvider extends Con
             TABLE_IMAGES + " ON " + qualifyColumn(TABLE_HISTORY, History.URL) + " = " +
             qualifyColumn(TABLE_IMAGES, Images.URL);
 
     static final UriMatcher URI_MATCHER = new UriMatcher(UriMatcher.NO_MATCH);
 
     static final Map<String, String> BOOKMARKS_PROJECTION_MAP;
     static final Map<String, String> HISTORY_PROJECTION_MAP;
     static final Map<String, String> IMAGES_PROJECTION_MAP;
+    static final Map<String, String> COMBINED_PROJECTION_MAP;
     static final Map<String, String> SCHEMA_PROJECTION_MAP;
 
     static {
         // We will reuse this.
         HashMap<String, String> map;
 
         // Bookmarks
         URI_MATCHER.addURI(BrowserContract.AUTHORITY, "bookmarks", BOOKMARKS);
@@ -169,16 +175,31 @@ public class BrowserProvider extends Con
         map.put(Images.FAVICON_URL, Images.FAVICON_URL);
         map.put(Images.THUMBNAIL, Images.THUMBNAIL);
         map.put(Images.DATE_CREATED, Images.DATE_CREATED);
         map.put(Images.DATE_MODIFIED, Images.DATE_MODIFIED);
         map.put(Images.GUID, Images.GUID);
         map.put(Images.IS_DELETED, Images.IS_DELETED);
         IMAGES_PROJECTION_MAP = Collections.unmodifiableMap(map);
 
+        // Combined bookmarks and history
+        URI_MATCHER.addURI(BrowserContract.AUTHORITY, "combined", COMBINED);
+
+        map = new HashMap<String, String>();
+        map.put(Combined._ID, Combined._ID);
+        map.put(Combined.BOOKMARK_ID, Combined.BOOKMARK_ID);
+        map.put(Combined.HISTORY_ID, Combined.HISTORY_ID);
+        map.put(Combined.URL, Combined.URL);
+        map.put(Combined.TITLE, Combined.TITLE);
+        map.put(Combined.VISITS, Combined.VISITS);
+        map.put(Combined.DATE_LAST_VISITED, Combined.DATE_LAST_VISITED);
+        map.put(Combined.FAVICON, Combined.FAVICON);
+        map.put(Combined.THUMBNAIL, Combined.THUMBNAIL);
+        COMBINED_PROJECTION_MAP = Collections.unmodifiableMap(map);
+
         // Schema
         URI_MATCHER.addURI(BrowserContract.AUTHORITY, "schema", SCHEMA);
 
         map = new HashMap<String, String>();
         map.put(Schema.VERSION, Schema.VERSION);
         SCHEMA_PROJECTION_MAP = Collections.unmodifiableMap(map);
     }
 
@@ -332,26 +353,76 @@ public class BrowserProvider extends Con
             debug("Creating " + VIEW_HISTORY_WITH_IMAGES + " view");
 
             db.execSQL("CREATE VIEW IF NOT EXISTS " + VIEW_HISTORY_WITH_IMAGES + " AS " +
                     "SELECT " + qualifyColumn(TABLE_HISTORY, "*") +
                     ", " + Images.FAVICON + ", " + Images.THUMBNAIL + " FROM " +
                     TABLE_HISTORY_JOIN_IMAGES);
         }
 
+        private void createCombinedWithImagesView(SQLiteDatabase db) {
+            debug("Creating " + VIEW_COMBINED_WITH_IMAGES + " view");
+
+            db.execSQL("CREATE VIEW IF NOT EXISTS " + VIEW_COMBINED_WITH_IMAGES + " AS" +
+                    " SELECT " + Combined.BOOKMARK_ID + ", " +
+                                 Combined.HISTORY_ID + ", " +
+                                 // We need to return an _id column because CursorAdapter requires it for its
+                                 // default implementation for the getItemId() method. However, since
+                                 // we're not using this feature in the parts of the UI using this view,
+                                 // we can just use 0 for all rows.
+                                 "0 AS " + Combined._ID + ", " +
+                                 Combined.URL + ", " +
+                                 Combined.TITLE + ", " +
+                                 Combined.VISITS + ", " +
+                                 Combined.DATE_LAST_VISITED + ", " +
+                                 qualifyColumn(TABLE_IMAGES, Images.FAVICON) + " AS " + Combined.FAVICON + ", " +
+                                 qualifyColumn(TABLE_IMAGES, Images.THUMBNAIL) + " AS " + Combined.THUMBNAIL +
+                    " FROM (" +
+                        // Bookmarks without history.
+                        " SELECT " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " AS " + Combined.BOOKMARK_ID + ", " +
+                                     qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " AS " + Combined.URL + ", " +
+                                     qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + " AS " + Combined.TITLE + ", " +
+                                     "-1 AS " + Combined.HISTORY_ID + ", " +
+                                     "-1 AS " + Combined.VISITS + ", " +
+                                     "-1 AS " + Combined.DATE_LAST_VISITED +
+                        " FROM " + TABLE_BOOKMARKS +
+                        " WHERE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE)  + " = " + Bookmarks.TYPE_BOOKMARK + " AND " +
+                                    qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) +
+                                        " NOT IN (SELECT " + History.URL + " FROM " + TABLE_HISTORY + ")" +
+                        " UNION ALL" +                
+                        // History with and without bookmark.
+                        " SELECT " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " AS " + Combined.BOOKMARK_ID + ", " +
+                                     qualifyColumn(TABLE_HISTORY, History.URL) + " AS " + Combined.URL + ", " +
+                                     // Prioritze bookmark titles over history titles, since the user may have
+                                     // customized the title for a bookmark.
+                                     "COALESCE(" + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + ", " +
+                                                   qualifyColumn(TABLE_HISTORY, History.TITLE) +")" + " AS " + Combined.TITLE + ", " +
+                                     qualifyColumn(TABLE_HISTORY, History._ID) + " AS " + Combined.HISTORY_ID + ", " +
+                                     qualifyColumn(TABLE_HISTORY, History.VISITS) + " AS " + Combined.VISITS + ", " +
+                                     qualifyColumn(TABLE_HISTORY, History.DATE_LAST_VISITED) + " AS " + Combined.DATE_LAST_VISITED +
+                        " FROM " + TABLE_HISTORY + " LEFT OUTER JOIN " + TABLE_BOOKMARKS +
+                            " ON " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " = " + qualifyColumn(TABLE_HISTORY, History.URL) +
+                        " WHERE " + qualifyColumn(TABLE_HISTORY, History.URL) + " IS NOT NULL AND (" +
+                                        qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " IS NULL OR " +
+                                        qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE)  + " = " + Bookmarks.TYPE_BOOKMARK + ")" +
+                    ") LEFT OUTER JOIN " + TABLE_IMAGES +
+                        " ON " + Combined.URL + " = " + qualifyColumn(TABLE_IMAGES, Images.URL));
+        }
+
         @Override
         public void onCreate(SQLiteDatabase db) {
             debug("Creating browser.db: " + db.getPath());
 
             createBookmarksTable(db);
             createHistoryTable(db);
             createImagesTable(db);
 
             createBookmarksWithImagesView(db);
             createHistoryWithImagesView(db);
+            createCombinedWithImagesView(db);
 
             createOrUpdateSpecialFolder(db, Bookmarks.PLACES_FOLDER_GUID,
                 R.string.bookmarks_folder_places, 0);
 
             createOrUpdateAllSpecialFolders(db);
 
             // FIXME: Create default bookmarks here (bug 728224)
         }
@@ -553,16 +624,20 @@ public class BrowserProvider extends Con
 
             createHistoryWithImagesView(db);
         }
 
         private void upgradeDatabaseFrom3to4(SQLiteDatabase db) {
             migrateBookmarksTable(db, new BookmarkMigrator3to4());
         }
 
+        private void upgradeDatabaseFrom4to5(SQLiteDatabase db) {
+            createCombinedWithImagesView(db);
+        }
+
         @Override
         public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
             debug("Upgrading browser.db: " + db.getPath() + " from " +
                     oldVersion + " to " + newVersion);
 
             db.beginTransaction();
 
             // We have to do incremental upgrades until we reach the current
@@ -575,16 +650,20 @@ public class BrowserProvider extends Con
 
                     case 3:
                         upgradeDatabaseFrom2to3(db);
                         break;
 
                     case 4:
                         upgradeDatabaseFrom3to4(db);
                         break;
+
+                    case 5:
+                        upgradeDatabaseFrom4to5(db);
+                        break;
                  }
              }
 
              db.setTransactionSuccessful();
              db.endTransaction();
         }
 
         @Override
@@ -1246,16 +1325,28 @@ public class BrowserProvider extends Con
             case SCHEMA: {
                 debug("Query is on schema.");
                 MatrixCursor schemaCursor = new MatrixCursor(new String[] { Schema.VERSION });
                 schemaCursor.newRow().add(DATABASE_VERSION);
 
                 return schemaCursor;
             }
 
+            case COMBINED: {
+                debug("Query is on combined: " + uri);
+
+                if (TextUtils.isEmpty(sortOrder))
+                    sortOrder = DEFAULT_HISTORY_SORT_ORDER;
+
+                qb.setProjectionMap(COMBINED_PROJECTION_MAP);
+                qb.setTables(VIEW_COMBINED_WITH_IMAGES);
+
+                break;
+            }
+
             default:
                 throw new UnsupportedOperationException("Unknown query URI " + uri);
         }
 
         trace("Running built query.");
         Cursor cursor = qb.query(db, projection, selection, selectionArgs, null,
                 null, sortOrder, limit);
         cursor.setNotificationUri(getContext().getContentResolver(),
--- a/mobile/android/base/db/LocalBrowserDB.java
+++ b/mobile/android/base/db/LocalBrowserDB.java
@@ -40,16 +40,17 @@
 package org.mozilla.gecko.db;
 
 import java.io.ByteArrayOutputStream;
 
 import org.mozilla.gecko.db.BrowserContract.Bookmarks;
 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.Combined;
 import org.mozilla.gecko.db.BrowserContract.URLColumns;
 import org.mozilla.gecko.db.DBUtils;
 
 import android.content.ContentResolver;
 import android.content.ContentUris;
 import android.content.ContentValues;
 import android.database.ContentObserver;
 import android.database.Cursor;
@@ -83,16 +84,17 @@ public class LocalBrowserDB implements B
 
     // Use wrapped Boolean so that we can have a null state
     private Boolean mDesktopBookmarksExist;
 
     private final Uri mBookmarksUriWithProfile;
     private final Uri mParentsUriWithProfile;
     private final Uri mHistoryUriWithProfile;
     private final Uri mImagesUriWithProfile;
+    private final Uri mCombinedUriWithProfile;
     private final Uri mDeletedHistoryUriWithProfile;
 
     private static final String[] DEFAULT_BOOKMARK_COLUMNS =
             new String[] { Bookmarks._ID,
                            Bookmarks.GUID,
                            Bookmarks.URL,
                            Bookmarks.TITLE,
                            Bookmarks.TYPE,
@@ -104,16 +106,17 @@ public class LocalBrowserDB implements B
         mProfile = profile;
         mMobileFolderId = -1;
         mDesktopBookmarksExist = null;
 
         mBookmarksUriWithProfile = appendProfile(Bookmarks.CONTENT_URI);
         mParentsUriWithProfile = appendProfile(Bookmarks.PARENTS_CONTENT_URI);
         mHistoryUriWithProfile = appendProfile(History.CONTENT_URI);
         mImagesUriWithProfile = appendProfile(Images.CONTENT_URI);
+        mCombinedUriWithProfile = appendProfile(Combined.CONTENT_URI);
 
         mDeletedHistoryUriWithProfile = mHistoryUriWithProfile.buildUpon().
             appendQueryParameter(BrowserContract.PARAM_SHOW_DELETED, "1").build();
     }
 
     // Invalidate cached data
     public void invalidateCachedState() {
         mDesktopBookmarksExist = null;
@@ -124,70 +127,75 @@ public class LocalBrowserDB implements B
                                                                        String.valueOf(limit)).build();
     }
 
     private Uri bookmarksUriWithLimit(int limit) {
         return mBookmarksUriWithProfile.buildUpon().appendQueryParameter(BrowserContract.PARAM_LIMIT,
                                                                          String.valueOf(limit)).build();
     }
 
+    private Uri combinedUriWithLimit(int limit) {
+        return mCombinedUriWithProfile.buildUpon().appendQueryParameter(BrowserContract.PARAM_LIMIT,
+                String.valueOf(limit)).build();
+    }
+
     private Uri appendProfile(Uri uri) {
         return uri.buildUpon().appendQueryParameter(BrowserContract.PARAM_PROFILE, mProfile).build();
     }
 
     private Cursor filterAllSites(ContentResolver cr, String[] projection, CharSequence constraint,
             int limit, CharSequence urlFilter) {
-        // The history selection queries for sites with a url or title
+        // The combined history/bookmarks selection queries for sites with a url or title
         // containing the constraint string
-        String selection = "(" + History.URL + " LIKE ? OR " +
-                                 History.TITLE + " LIKE ?)";
+        String selection = "(" + Combined.URL + " LIKE ? OR " +
+                                 Combined.TITLE + " LIKE ?)";
 
         final String historySelectionArg = "%" + constraint.toString() + "%";
         String[] selectionArgs = new String[] { historySelectionArg, historySelectionArg };
 
         if (urlFilter != null) {
-            selection = DBUtils.concatenateWhere(selection, "(" + History.URL + " NOT LIKE ?)");
+            selection = DBUtils.concatenateWhere(selection, "(" + Combined.URL + " NOT LIKE ?)");
             selectionArgs = DBUtils.appendSelectionArgs(selectionArgs, new String[] { urlFilter.toString() });
         }
 
         // Our version of frecency is computed by scaling the number of visits by a multiplier
         // that approximates Gaussian decay, based on how long ago the entry was last visited.
         // Since we're limited by the math we can do with sqlite, we're calculating this
         // approximation using the Cauchy distribution: multiplier = 15^2 / (age^2 + 15^2).
         // Using 15 as our scale parameter, we get a constant 15^2 = 225. Following this math,
         // frecencyScore = numVisits * max(1, 100 * 225 / (age*age + 225)). (See bug 704977)
-        final String age = "(" + History.DATE_LAST_VISITED + " - " + System.currentTimeMillis() + ") / 86400000";
-        final String sortOrder = History.VISITS + " * MAX(1, 100 * 225 / (" + age + "*" + age + " + 225)) DESC";
+        final String age = "(" + Combined.DATE_LAST_VISITED + " - " + System.currentTimeMillis() + ") / 86400000";
+        final String sortOrder = Combined.VISITS + " * MAX(1, 100 * 225 / (" + age + "*" + age + " + 225)) DESC";
 
-        Cursor c = cr.query(historyUriWithLimit(limit),
+        Cursor c = cr.query(combinedUriWithLimit(limit),
                             projection,
                             selection,
                             selectionArgs,
                             sortOrder);
 
         return new LocalDBCursor(c);
     }
 
     public Cursor filter(ContentResolver cr, CharSequence constraint, int limit) {
         return filterAllSites(cr,
-                              new String[] { History._ID,
-                                             History.URL,
-                                             History.TITLE,
-                                             History.FAVICON },
+                              new String[] { Combined._ID,
+                                             Combined.URL,
+                                             Combined.TITLE,
+                                             Combined.FAVICON },
                               constraint,
                               limit,
                               null);
     }
 
     public Cursor getTopSites(ContentResolver cr, int limit) {
         return filterAllSites(cr,
-                              new String[] { History._ID,
-                                             History.URL,
-                                             History.TITLE,
-                                             History.THUMBNAIL },
+                              new String[] { Combined._ID,
+                                             Combined.URL,
+                                             Combined.TITLE,
+                                             Combined.THUMBNAIL },
                               "",
                               limit,
                               BrowserDB.ABOUT_PAGES_URL_FILTER);
     }
 
     private void truncateHistory(ContentResolver cr) {
         Cursor cursor = null;