Bug 1046709 - (WIP) Schema changes. draft
authorMichael Comella <michael.l.comella@gmail.com>
Thu, 12 Nov 2015 17:44:58 -0800
changeset 308537 0808ef6f04986ae27caf3e116d5804d6e75e739a
parent 307749 6e1399c05bd0a982a0a176bff2e0f9180fa767bb
child 511186 f8f126effb0b86902f20aae054461575ab73a618
push id7496
push usermichael.l.comella@gmail.com
push dateFri, 13 Nov 2015 01:46:11 +0000
bugs1046709
milestone45.0a1
Bug 1046709 - (WIP) Schema changes. This is just to check we all agree on the schema - the migration and removal of the old fields still needs to happen. The schema closely mirrors iOS': History: https://github.com/mozilla/firefox-ios/blob/e077523cc0581c0b3939350243158a03a721e8a3/Storage/SQL/BrowserTable.swift#L141 Visits: https://github.com/mozilla/firefox-ios/blob/e077523cc0581c0b3939350243158a03a721e8a3/Storage/SQL/BrowserTable.swift#L266
mobile/android/base/db/BrowserContract.java
mobile/android/base/db/BrowserDatabaseHelper.java
mobile/android/base/sync/repositories/android/BrowserContractHelpers.java
--- a/mobile/android/base/db/BrowserContract.java
+++ b/mobile/android/base/db/BrowserContract.java
@@ -68,17 +68,20 @@ public class BrowserContract {
     @RobocopTarget
     public interface CommonColumns {
         public static final String _ID = "_id";
     }
 
     @RobocopTarget
     public interface DateSyncColumns {
         public static final String DATE_CREATED = "created";
-        public static final String DATE_MODIFIED = "modified";
+        public static final String DATE_MODIFIED = "modified"; // TODO: Remove?
+        // TODO: Correct interface? Is this synced?
+        String SERVER_MODIFIED = "server_modified";
+        String LOCAL_MODIFIED = "local_modified";
     }
 
     @RobocopTarget
     public interface SyncColumns extends DateSyncColumns {
         public static final String GUID = "guid";
         public static final String IS_DELETED = "deleted";
     }
 
@@ -90,22 +93,31 @@ public class BrowserContract {
 
     @RobocopTarget
     public interface FaviconColumns {
         public static final String FAVICON = "favicon";
         public static final String FAVICON_ID = "favicon_id";
         public static final String FAVICON_URL = "favicon_url";
     }
 
+    // TODO: Remove me!
     @RobocopTarget
     public interface HistoryColumns {
         public static final String DATE_LAST_VISITED = "date";
         public static final String VISITS = "visits";
     }
 
+    @RobocopTarget
+    public interface VisitsColumns {
+        String SITE_ID = "site_id";
+        String DATE = "date";
+        String TYPE = "type";
+        String IS_LOCAL = "is_local";
+    }
+
     public interface DeletedColumns {
         public static final String ID = "id";
         public static final String GUID = "guid";
         public static final String TIME_DELETED = "timeDeleted";
     }
 
     @RobocopTarget
     public static final class Favicons implements CommonColumns, DateSyncColumns {
@@ -191,16 +203,23 @@ public class BrowserContract {
         public static final String VIEW_WITH_FAVICONS = "history_with_favicons";
 
         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";
     }
 
+    @RobocopTarget
+    public static final class Visits implements CommonColumns, VisitsColumns {
+        private Visits() {}
+
+        public static final String TABLE_NAME = "visits";
+    }
+
     // Combined bookmarks and history
     @RobocopTarget
     public static final class Combined implements CommonColumns, URLColumns, HistoryColumns, FaviconColumns  {
         private Combined() {}
 
         public static final String VIEW_NAME = "combined";
 
         public static final String VIEW_WITH_FAVICONS = "combined_with_favicons";
--- a/mobile/android/base/db/BrowserDatabaseHelper.java
+++ b/mobile/android/base/db/BrowserDatabaseHelper.java
@@ -14,16 +14,17 @@ 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.Favicons;
 import org.mozilla.gecko.db.BrowserContract.History;
 import org.mozilla.gecko.db.BrowserContract.ReadingListItems;
 import org.mozilla.gecko.db.BrowserContract.SearchHistory;
 import org.mozilla.gecko.db.BrowserContract.Thumbnails;
+import org.mozilla.gecko.db.BrowserContract.Visits;
 import org.mozilla.gecko.util.FileUtils;
 
 import static org.mozilla.gecko.db.DBUtils.qualifyColumn;
 
 import android.content.ContentValues;
 import android.content.Context;
 import android.database.Cursor;
 import android.database.DatabaseUtils;
@@ -41,16 +42,17 @@ final class BrowserDatabaseHelper extend
 
     public static final int DATABASE_VERSION = 25;
     public static final String DATABASE_NAME = "browser.db";
 
     final protected Context mContext;
 
     static final String TABLE_BOOKMARKS = Bookmarks.TABLE_NAME;
     static final String TABLE_HISTORY = History.TABLE_NAME;
+    static final String TABLE_VISITS = History.TABLE_NAME;
     static final String TABLE_FAVICONS = Favicons.TABLE_NAME;
     static final String TABLE_THUMBNAILS = Thumbnails.TABLE_NAME;
     static final String TABLE_READING_LIST = ReadingListItems.TABLE_NAME;
     static final String TABLE_TABS = TabsProvider.TABLE_TABS;
     static final String TABLE_CLIENTS = TabsProvider.TABLE_CLIENTS;
 
     static final String VIEW_COMBINED = Combined.VIEW_NAME;
     static final String VIEW_BOOKMARKS_WITH_FAVICONS = Bookmarks.VIEW_WITH_FAVICONS;
@@ -109,35 +111,53 @@ final class BrowserDatabaseHelper extend
     }
 
     private void createHistoryTable(SQLiteDatabase db) {
         debug("Creating " + TABLE_HISTORY + " table");
         db.execSQL("CREATE TABLE " + TABLE_HISTORY + "(" +
                 History._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                 History.TITLE + " TEXT," +
                 History.URL + " TEXT NOT NULL," +
-                History.VISITS + " INTEGER NOT NULL DEFAULT 0," +
+                //History.VISITS + " INTEGER NOT NULL DEFAULT 0," +
                 History.FAVICON_ID + " INTEGER," +
-                History.DATE_LAST_VISITED + " INTEGER," +
+                //History.DATE_LAST_VISITED + " INTEGER," +
                 History.DATE_CREATED + " INTEGER," +
-                History.DATE_MODIFIED + " INTEGER," +
-                History.GUID + " TEXT NOT NULL," +
+                //History.DATE_MODIFIED + " INTEGER," +
+                History.SERVER_MODIFIED + " INTEGER," +
+                History.LOCAL_MODIFIED + " INTEGER," +
+                History.GUID + " TEXT NOT NULL," + // TODO: Should be unique. Scope creep? Might break.
                 History.IS_DELETED + " INTEGER NOT NULL DEFAULT 0" +
                 ");");
 
+        // TODO: check
         db.execSQL("CREATE INDEX history_url_index ON " + TABLE_HISTORY + '('
                 + History.URL + ')');
         db.execSQL("CREATE UNIQUE INDEX history_guid_index ON " + TABLE_HISTORY + '('
                 + History.GUID + ')');
         db.execSQL("CREATE INDEX history_modified_index ON " + TABLE_HISTORY + '('
                 + History.DATE_MODIFIED + ')');
         db.execSQL("CREATE INDEX history_visited_index ON " + TABLE_HISTORY + '('
                 + History.DATE_LAST_VISITED + ')');
     }
 
+    private void createVisitsTable(final SQLiteDatabase db) {
+        debug("Creating " + TABLE_VISITS + " table");
+        db.execSQL("CREATE TABLE " + TABLE_VISITS + "(" +
+                Visits._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
+                // TODO: Make sure this shorthand is correct (seems unlisted on the statement graphs)
+                Visits.SITE_ID + " INTEGER NOT NULL REFERENCES " + TABLE_HISTORY + " ON DELETE CASCADE, " +
+                // TODO: iOS uses a real here but since we use longs and nano?, I'm not sure that's necessary or microseconds is true.
+                Visits.DATE + " INTEGER NOT NULL, " + // Microseconds since epoch.
+                Visits.TYPE + " INTEGER NOT NULL, " +
+                Visits.IS_LOCAL + " TINYINT NOT NULL, " + // Some visits are local, some are remote: this boolean flag is the split.
+                ");");
+
+        // TODO: Indices & stuff?
+    }
+
     private void createFaviconsTable(SQLiteDatabase db) {
         debug("Creating " + TABLE_FAVICONS + " table");
         db.execSQL("CREATE TABLE " + TABLE_FAVICONS + " (" +
                 Favicons._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                 Favicons.URL + " TEXT UNIQUE," +
                 Favicons.DATA + " BLOB," +
                 Favicons.DATE_CREATED + " INTEGER," +
                 Favicons.DATE_MODIFIED + " INTEGER" +
@@ -339,16 +359,17 @@ final class BrowserDatabaseHelper extend
         debug("Creating browser.db: " + db.getPath());
 
         for (Table table : BrowserProvider.sTables) {
             table.onCreate(db);
         }
 
         createBookmarksTable(db);
         createHistoryTable(db);
+        createVisitsTable(db);
         createFaviconsTable(db);
         createThumbnailsTable(db);
         createClientsTable(db);
         createLocalClient(db);
         createTabsTable(db, TABLE_TABS);
         didCreateTabsTable = true;
         createTabsTableIndices(db, TABLE_TABS);
 
@@ -1027,16 +1048,20 @@ final class BrowserDatabaseHelper extend
 
         // Now switch these tables over and recreate the indices.
         db.execSQL("DROP TABLE " + TABLE_TABS);
         db.execSQL("ALTER TABLE tmp_tabs RENAME TO " + TABLE_TABS);
         createTabsTableIndices(db, TABLE_TABS);
         didCreateTabsTable =true;
     }
 
+    private void upgradeDatabaseFrom25to26(final SQLiteDatabase db) {
+
+    }
+
     private void createV19CombinedView(SQLiteDatabase db) {
         db.execSQL("DROP VIEW IF EXISTS " + VIEW_COMBINED);
         db.execSQL("DROP VIEW IF EXISTS " + VIEW_COMBINED_WITH_FAVICONS);
 
         createCombinedViewOn19(db);
     }
 
     @Override
@@ -1106,16 +1131,20 @@ final class BrowserDatabaseHelper extend
 
                 case 24:
                     upgradeDatabaseFrom23to24(db);
                     break;
 
                 case 25:
                     upgradeDatabaseFrom24to25(db);
                     break;
+
+                case 26:
+                    upgradeDatabaseFrom25to26(db);
+                    break;
             }
         }
 
         for (Table table : BrowserProvider.sTables) {
             table.onUpgrade(db, oldVersion, newVersion);
         }
 
         // Delete the obsolete favicon database after all other upgrades complete.
--- a/mobile/android/base/sync/repositories/android/BrowserContractHelpers.java
+++ b/mobile/android/base/sync/repositories/android/BrowserContractHelpers.java
@@ -53,16 +53,17 @@ public class BrowserContractHelpers exte
     Passwords.ENC_TYPE,
     Passwords.TIME_CREATED,
     Passwords.TIME_LAST_USED,
     Passwords.TIME_PASSWORD_CHANGED,
     Passwords.TIMES_USED,
     Passwords.GUID
   };
 
+  // TODO: Update me!
   public static final String[] HistoryColumns = new String[] {
     CommonColumns._ID,
     SyncColumns.GUID,
     SyncColumns.DATE_CREATED,
     SyncColumns.DATE_MODIFIED,
     SyncColumns.IS_DELETED,
     History.TITLE,
     History.URL,