Bug 725914 - Remove sub-select from the table join for bookmark and history (r=mfinkle)
authorLucas Rocha <lucasr@mozilla.com>
Wed, 22 Feb 2012 11:47:13 +0000
changeset 88950 23fef78981b5fdedd45e9c6c35cb1376e3b6184f
parent 88949 6f1953bd8f4623eba7c14ee9cc1e8385d7dfcabe
child 88951 e606267898cf176e1f51e3d4a3682a47cb88f7d9
push idunknown
push userunknown
push dateunknown
reviewersmfinkle
bugs725914
milestone13.0a1
Bug 725914 - Remove sub-select from the table join for bookmark and history (r=mfinkle)
mobile/android/base/db/BrowserProvider.java.in
--- a/mobile/android/base/db/BrowserProvider.java.in
+++ b/mobile/android/base/db/BrowserProvider.java.in
@@ -119,30 +119,22 @@ public class BrowserProvider extends Con
 
     static final String DEFAULT_BOOKMARKS_SORT_ORDER = Bookmarks.IS_FOLDER
             + " DESC, " + 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 " +
-            "(SELECT " + Images.URL + ", " + Images.FAVICON + ", " + Images.THUMBNAIL + " FROM " +
-            TABLE_IMAGES + ", " + TABLE_BOOKMARKS + " WHERE " +
-            qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " = " +
-            qualifyColumn(TABLE_IMAGES, Images.URL) + ") AS bookmark_images ON " +
-            qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " = " +
-            qualifyColumn("bookmark_images", Images.URL);
+            TABLE_IMAGES + " ON " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " = " +
+            qualifyColumn(TABLE_IMAGES, Images.URL);
 
     static final String TABLE_HISTORY_JOIN_IMAGES = TABLE_HISTORY + " LEFT OUTER JOIN " +
-            "(SELECT " + Images.URL + ", " + Images.FAVICON + ", " + Images.THUMBNAIL + " FROM " +
-            TABLE_IMAGES + ", " + TABLE_HISTORY + " WHERE " +
-            qualifyColumn(TABLE_HISTORY, History.URL) + " = " +
-            qualifyColumn(TABLE_IMAGES, Images.URL) + ") AS history_images ON " +
-            qualifyColumn(TABLE_HISTORY, History.URL) + " = " +
-            qualifyColumn("history_images", Images.URL);
+            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 HashMap<String, String> BOOKMARKS_PROJECTION_MAP = new HashMap<String, String>();
     static final HashMap<String, String> HISTORY_PROJECTION_MAP = new HashMap<String, String>();
     static final HashMap<String, String> IMAGES_PROJECTION_MAP = new HashMap<String, String>();
     static final HashMap<String, String> SCHEMA_PROJECTION_MAP = new HashMap<String, String>();
 
@@ -327,23 +319,27 @@ public class BrowserProvider extends Con
                     + Images.URL + ")");
             db.execSQL("CREATE UNIQUE INDEX images_guid_index ON " + TABLE_IMAGES + "("
                     + Images.GUID + ")");
             db.execSQL("CREATE INDEX images_modified_index ON " + TABLE_IMAGES + "("
                     + Images.DATE_MODIFIED + ")");
         }
 
         private void createBookmarksWithImagesView(SQLiteDatabase db) {
+            debug("Creating " + VIEW_BOOKMARKS_WITH_IMAGES + " view");
+
             db.execSQL("CREATE VIEW IF NOT EXISTS " + VIEW_BOOKMARKS_WITH_IMAGES + " AS " +
                     "SELECT " + qualifyColumn(TABLE_BOOKMARKS, "*") +
                     ", " + Images.FAVICON + ", " + Images.THUMBNAIL + " FROM " +
                     TABLE_BOOKMARKS_JOIN_IMAGES);
         }
 
         private void createHistoryWithImagesView(SQLiteDatabase db) {
+            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);
         }
 
         @Override
         public void onCreate(SQLiteDatabase db) {
@@ -533,30 +529,46 @@ public class BrowserProvider extends Con
             // Ensure all special folders exist and have the
             // right folder hierarchy.
             createOrUpdateAllSpecialFolders(db);
 
             debug("Dropping bookmarks temporary table");
             db.execSQL("DROP TABLE IF EXISTS " + TABLE_BOOKMARKS_TMP);
         }
 
+        private void upgradeDatabaseFrom2to3(SQLiteDatabase db) {
+            debug("Dropping view: " + VIEW_BOOKMARKS_WITH_IMAGES);
+            db.execSQL("DROP VIEW IF EXISTS " + VIEW_BOOKMARKS_WITH_IMAGES);
+
+            createBookmarksWithImagesView(db);
+
+            debug("Dropping view: " + VIEW_HISTORY_WITH_IMAGES);
+            db.execSQL("DROP VIEW IF EXISTS " + VIEW_HISTORY_WITH_IMAGES);
+
+            createHistoryWithImagesView(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
             // database schema version.
             for (int v = oldVersion + 1; v <= newVersion; v++) {
                 switch(v) {
                     case 2:
                         upgradeDatabaseFrom1to2(db);
                         break;
+
+                    case 3:
+                        upgradeDatabaseFrom2to3(db);
+                        break;
                  }
              }
 
              db.endTransaction();
         }
 
         @Override
         public void onOpen(SQLiteDatabase db) {