Bug 723841 - Add a foreign key to bookmarks table and sanitize special folders (r=rnewman)
authorLucas Rocha <lucasr@mozilla.com>
Mon, 20 Feb 2012 19:28:27 +0000
changeset 87244 14f791dbb5796d0b3566aab3ad7225128c1b67de
parent 87243 0ffb3b233fcedae835ffcbd2a4f696852255c0fd
child 87245 489a84d4a5028d9fcd653d0859d79a83d9541696
push id22103
push userbmo@edmorley.co.uk
push dateTue, 21 Feb 2012 12:01:45 +0000
treeherdermozilla-central@4038ffaa5d82 [default view] [failures only]
perfherder[talos] [build metrics] [platform microbench] (compared to previous push)
reviewersrnewman
bugs723841
milestone13.0a1
first release with
nightly linux32
nightly linux64
nightly mac
nightly win32
nightly win64
last release without
nightly linux32
nightly linux64
nightly mac
nightly win32
nightly win64
Bug 723841 - Add a foreign key to bookmarks table and sanitize special folders (r=rnewman)
mobile/android/base/db/BrowserProvider.java.in
--- a/mobile/android/base/db/BrowserProvider.java.in
+++ b/mobile/android/base/db/BrowserProvider.java.in
@@ -37,17 +37,19 @@
  *
  * ***** END LICENSE BLOCK ***** */
 
 #filter substitution
 package @ANDROID_PACKAGE_NAME@.db;
 
 import java.io.File;
 import java.io.IOException;
+import java.util.ArrayList;
 import java.util.HashMap;
+import java.util.List;
 import java.util.Random;
 
 import org.mozilla.gecko.GeckoAppShell;
 import org.mozilla.gecko.GeckoDirProvider;
 import org.mozilla.gecko.R;
 import org.mozilla.gecko.db.BrowserContract.Bookmarks;
 import org.mozilla.gecko.db.BrowserContract.CommonColumns;
 import org.mozilla.gecko.db.BrowserContract.History;
@@ -60,43 +62,46 @@ import org.mozilla.gecko.db.DBUtils;
 import org.mozilla.gecko.sync.Utils;
 
 import android.content.ContentProvider;
 import android.content.ContentUris;
 import android.content.ContentValues;
 import android.content.Context;
 import android.content.UriMatcher;
 import android.database.Cursor;
+import android.database.DatabaseUtils;
 import android.database.MatrixCursor;
 import android.database.sqlite.SQLiteDatabase;
 import android.database.sqlite.SQLiteOpenHelper;
 import android.database.sqlite.SQLiteQueryBuilder;
 import android.net.Uri;
 import android.os.Build;
 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 = 1;
+    static final int DATABASE_VERSION = 2;
 
     // 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";
 
     // 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;
@@ -241,30 +246,40 @@ public class BrowserProvider extends Con
 
     final class DatabaseHelper extends SQLiteOpenHelper {
         public DatabaseHelper(Context context, String databasePath) {
             super(context, databasePath, null, DATABASE_VERSION);
         }
 
         private void createBookmarksTable(SQLiteDatabase db) {
             debug("Creating " + TABLE_BOOKMARKS + " table");
+
+            // Android versions older than Froyo ship with an sqlite
+            // that doesn't support foreign keys.
+            String foreignKeyOnParent = null;
+            if (Build.VERSION.SDK_INT >= 8) {
+                foreignKeyOnParent = ", FOREIGN KEY (" + Bookmarks.PARENT +
+                    ") REFERENCES " + TABLE_BOOKMARKS + "(" + Bookmarks._ID + ")";
+            }
+
             db.execSQL("CREATE TABLE " + TABLE_BOOKMARKS + "(" +
                     Bookmarks._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                     Bookmarks.TITLE + " TEXT," +
                     Bookmarks.URL + " TEXT," +
                     Bookmarks.IS_FOLDER + " INTEGER NOT NULL DEFAULT 0," +
                     Bookmarks.PARENT + " INTEGER," +
                     Bookmarks.POSITION + " INTEGER NOT NULL," +
                     Bookmarks.KEYWORD + " TEXT," +
                     Bookmarks.DESCRIPTION + " TEXT," +
                     Bookmarks.TAGS + " TEXT," +
                     Bookmarks.DATE_CREATED + " INTEGER," +
                     Bookmarks.DATE_MODIFIED + " INTEGER," +
                     Bookmarks.GUID + " TEXT," +
                     Bookmarks.IS_DELETED + " INTEGER NOT NULL DEFAULT 0" +
+                    (foreignKeyOnParent != null ? foreignKeyOnParent : "") +
                     ");");
 
             db.execSQL("CREATE INDEX bookmarks_url_index ON " + TABLE_BOOKMARKS + "("
                     + Bookmarks.URL + ")");
             db.execSQL("CREATE UNIQUE INDEX bookmarks_guid_index ON " + TABLE_BOOKMARKS + "("
                     + Bookmarks.GUID + ")");
             db.execSQL("CREATE INDEX bookmarks_modified_index ON " + TABLE_BOOKMARKS + "("
                     + Bookmarks.DATE_MODIFIED + ")");
@@ -336,53 +351,216 @@ public class BrowserProvider extends Con
 
             createBookmarksTable(db);
             createHistoryTable(db);
             createImagesTable(db);
 
             createBookmarksWithImagesView(db);
             createHistoryWithImagesView(db);
 
+            createOrUpdateSpecialFolder(db, Bookmarks.PLACES_FOLDER_GUID,
+                R.string.bookmarks_folder_places, 0);
+
+            createOrUpdateAllSpecialFolders(db);
+
+            // FIXME: Create default bookmarks here (bug 728224)
+        }
+
+        private void createOrUpdateAllSpecialFolders(SQLiteDatabase db) {
             createOrUpdateSpecialFolder(db, Bookmarks.MOBILE_FOLDER_GUID,
                 R.string.bookmarks_folder_mobile, 0);
-
-            // FIXME: Create default bookmarks here
+            createOrUpdateSpecialFolder(db, Bookmarks.TOOLBAR_FOLDER_GUID,
+                R.string.bookmarks_folder_toolbar, 1);
+            createOrUpdateSpecialFolder(db, Bookmarks.MENU_FOLDER_GUID,
+                R.string.bookmarks_folder_menu, 2);
+            createOrUpdateSpecialFolder(db, Bookmarks.TAGS_FOLDER_GUID,
+                R.string.bookmarks_folder_tags, 3);
+            createOrUpdateSpecialFolder(db, Bookmarks.UNFILED_FOLDER_GUID,
+                R.string.bookmarks_folder_unfiled, 4);
         }
 
         private void createOrUpdateSpecialFolder(SQLiteDatabase db,
                 String guid, int titleId, int position) {
             ContentValues values = new ContentValues();
             values.put(Bookmarks.GUID, guid);
             values.put(Bookmarks.IS_FOLDER, 1);
             values.put(Bookmarks.POSITION, position);
 
+            if (guid.equals(Bookmarks.PLACES_FOLDER_GUID))
+                values.put(Bookmarks._ID, Bookmarks.FIXED_ROOT_ID);
+
             // Set the parent to 0, which sync assumes is the root
             values.put(Bookmarks.PARENT, Bookmarks.FIXED_ROOT_ID);
 
             String title = mContext.getResources().getString(titleId);
             values.put(Bookmarks.TITLE, title);
 
             long now = System.currentTimeMillis();
             values.put(Bookmarks.DATE_CREATED, now);
             values.put(Bookmarks.DATE_MODIFIED, now);
 
             int updated = db.update(TABLE_BOOKMARKS, values,
                                     Bookmarks.GUID + " = ?",
                                     new String[] { guid });
 
-            if (updated == 0)
+            if (updated == 0) {
                 db.insert(TABLE_BOOKMARKS, Bookmarks.GUID, values);
+                debug("Inserted special folder: " + guid);
+            } else {
+                debug("Updated special folder: " + guid);
+            }
+        }
+
+        private boolean isSpecialFolder(ContentValues values) {
+            String guid = values.getAsString(Bookmarks.GUID);
+            if (guid == null)
+                return false;
+
+            return guid.equals(Bookmarks.MOBILE_FOLDER_GUID) ||
+                   guid.equals(Bookmarks.MENU_FOLDER_GUID) ||
+                   guid.equals(Bookmarks.TOOLBAR_FOLDER_GUID) ||
+                   guid.equals(Bookmarks.UNFILED_FOLDER_GUID) ||
+                   guid.equals(Bookmarks.TAGS_FOLDER_GUID);
+        }
+
+        private void migrateBookmarkFolder(SQLiteDatabase db, int folderId) {
+            Cursor c = null;
+
+            debug("Migrating bookmark folder with id = " + folderId);
+
+            String selection = Bookmarks.PARENT + " = " + folderId;
+            String[] selectionArgs = null;
+
+            boolean isRootFolder = (folderId == Bookmarks.FIXED_ROOT_ID);
+
+            // If we're loading the root folder, we have to account for
+            // any previously created special folder that was created without
+            // setting a parent id (e.g. mobile folder) and making sure we're
+            // not adding any infinite recursion as root's parent is root itself.
+            if (isRootFolder) {
+                selection = Bookmarks.GUID + " != ?" + " AND (" +
+                            selection + " OR " + Bookmarks.PARENT + " = NULL)";
+                selectionArgs = new String[] { Bookmarks.PLACES_FOLDER_GUID };
+            }
+
+            List<Integer> subFolders = new ArrayList<Integer>();
+            List<ContentValues> invalidSpecialEntries = new ArrayList<ContentValues>();
+
+            try {
+                c = db.query(TABLE_BOOKMARKS_TMP,
+                             null,
+                             selection,
+                             selectionArgs,
+                             null, null, null);
+
+                // The key point here is that bookmarks should be added in
+                // parent order to avoid any problems with the foreign key
+                // in Bookmarks.PARENT.
+                while (c.moveToNext()) {
+                    ContentValues values = new ContentValues();
+
+                    // We're using a null projection in the query which
+                    // means we're getting all columns from the table.
+                    // It's safe to simply transform the row into the
+                    // values to be inserted on the new table.
+                    DatabaseUtils.cursorRowToContentValues(c, values);
+
+                    boolean isSpecialFolder = isSpecialFolder(values);
+
+                    // The mobile folder used to be created with PARENT = NULL.
+                    // We want fix that here.
+                    if (values.getAsLong(Bookmarks.PARENT) == null && isSpecialFolder)
+                        values.put(Bookmarks.PARENT, Bookmarks.FIXED_ROOT_ID);
+
+                    if (isRootFolder && !isSpecialFolder) {
+                        invalidSpecialEntries.add(values);
+                        continue;
+                    }
+
+                    debug("Migrating bookmark: " + values.getAsString(Bookmarks.TITLE));
+                    db.insert(TABLE_BOOKMARKS, Bookmarks.URL, values);
+
+                    Integer isFolder = values.getAsInteger(Bookmarks.IS_FOLDER);
+                    if (isFolder != null && isFolder == 1)
+                        subFolders.add(values.getAsInteger(Bookmarks._ID));
+                }
+            } finally {
+                if (c != null)
+                    c.close();
+            }
+
+            // At this point is safe to assume that the mobile folder is
+            // in the new table given that we've always created it on
+            // database creation time.
+            final int nInvalidSpecialEntries = invalidSpecialEntries.size();
+            if (nInvalidSpecialEntries > 0) {
+                Long mobileFolderId = guidToID(db, Bookmarks.MOBILE_FOLDER_GUID);
+
+                debug("Found " + nInvalidSpecialEntries + " invalid special folder entries");
+                for (int i = 0; i < nInvalidSpecialEntries; i++) {
+                    ContentValues values = invalidSpecialEntries.get(i);
+                    values.put(Bookmarks.PARENT, mobileFolderId);
+
+                    db.insert(TABLE_BOOKMARKS, Bookmarks.URL, values);
+                }
+            }
+
+            final int nSubFolders = subFolders.size();
+            for (int i = 0; i < nSubFolders; i++) {
+                int subFolderId = subFolders.get(i);
+                migrateBookmarkFolder(db, subFolderId);
+            }
+        }
+
+        private void upgradeDatabaseFrom1to2(SQLiteDatabase db) {
+            debug("Renaming bookmarks table to " + TABLE_BOOKMARKS_TMP);
+            db.execSQL("ALTER TABLE " + TABLE_BOOKMARKS +
+                       " RENAME TO " + TABLE_BOOKMARKS_TMP);
+
+            debug("Dropping views and indexes related to " + TABLE_BOOKMARKS);
+            db.execSQL("DROP VIEW IF EXISTS " + VIEW_BOOKMARKS_WITH_IMAGES);
+
+            db.execSQL("DROP INDEX IF EXISTS bookmarks_url_index");
+            db.execSQL("DROP INDEX IF EXISTS bookmarks_guid_index");
+            db.execSQL("DROP INDEX IF EXISTS bookmarks_modified_index");
+
+            createBookmarksTable(db);
+            createBookmarksWithImagesView(db);
+
+            createOrUpdateSpecialFolder(db, Bookmarks.PLACES_FOLDER_GUID,
+                R.string.bookmarks_folder_places, 0);
+
+            migrateBookmarkFolder(db, Bookmarks.FIXED_ROOT_ID);
+
+            // 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);
         }
 
         @Override
         public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
             debug("Upgrading browser.db: " + db.getPath() + " from " +
                     oldVersion + " to " + newVersion);
 
-            // Do nothing for now
+            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;
+                 }
+             }
+
+             db.endTransaction();
         }
 
         @Override
         public void onOpen(SQLiteDatabase db) {
             debug("Opening browser.db: " + db.getPath());
 
             // From Honeycomb on, it's possible to run several db
             // commands in parallel using multiple connections.
@@ -403,16 +581,36 @@ public class BrowserProvider extends Con
                 } finally {
                     if (cursor != null)
                         cursor.close();
                 }
             }
         }
     }
 
+    private Long guidToID(SQLiteDatabase db, String guid) {
+        Cursor c = null;
+
+        try {
+            c = db.query(TABLE_BOOKMARKS,
+                         new String[] { Bookmarks._ID },
+                         Bookmarks.GUID + " = ?",
+                         new String[] { guid },
+                         null, null, null);
+
+            if (c == null || !c.moveToFirst())
+                return null;
+
+            return c.getLong(c.getColumnIndex(Bookmarks._ID));
+        } finally {
+            if (c != null)
+                c.close();
+        }
+    }
+
     private DatabaseHelper getDatabaseHelperForProfile(String profile) {
         // Each profile has a separate browser.db database. The target
         // profile is provided using a URI query argument in each request
         // to our content provider.
 
         // Always fallback to default profile if none has been provided.
         if (TextUtils.isEmpty(profile)) {
             profile = BrowserContract.DEFAULT_PROFILE;