[Issue 2183494] Deleting status updates when a contacts account is removed

Change-Id: Ia9e86786ed11b3fca60b274edc6495b77838bc46

1 files changed, 95 insertions, 80 deletions

diff --git a/src/com/android/providers/contacts/ContactsDatabaseHelper.java b/src/com/android/providers/contacts/ContactsDatabaseHelper.java
index ecdd8cab..504e604b 100644
--- a/src/com/android/providers/contacts/ContactsDatabaseHelper.java
+++ b/src/com/android/providers/contacts/ContactsDatabaseHelper.java
@@ -61,7 +61,7 @@ import java.util.HashMap;
 /* package */ class ContactsDatabaseHelper extends SQLiteOpenHelper {
     private static final String TAG = "ContactsDatabaseHelper";
 
-    private static final int DATABASE_VERSION = 101;
+    private static final int DATABASE_VERSION = 102;
 
     private static final String DATABASE_NAME = "contacts2.db";
     private static final String DATABASE_PRESENCE = "presence_db";
@@ -657,15 +657,6 @@ import java.util.HashMap;
                 ContactsColumns.SINGLE_IS_RESTRICTED + " INTEGER NOT NULL DEFAULT 0" +
         ");");
 
-        db.execSQL("CREATE TRIGGER contacts_times_contacted UPDATE OF " +
-                Contacts.LAST_TIME_CONTACTED + " ON " + Tables.CONTACTS + " " +
-            "BEGIN " +
-                "UPDATE " + Tables.CONTACTS + " SET "
-                    + Contacts.TIMES_CONTACTED + " = " + "" +
-                            "(new." + Contacts.TIMES_CONTACTED + " + 1)"
-                    + " WHERE _id = new._id;" +
-            "END");
-
         db.execSQL("CREATE INDEX contacts_visible_index ON " + Tables.CONTACTS + " (" +
                 Contacts.IN_VISIBLE_GROUP + "," +
                 Contacts.DISPLAY_NAME + " COLLATE LOCALIZED" +
@@ -785,67 +776,6 @@ import java.util.HashMap;
                 Data.DATA1 +
         ");");
 
-        /**
-         * Automatically delete Data rows when a raw contact is deleted.
-         */
-        db.execSQL("CREATE TRIGGER " + Tables.RAW_CONTACTS + "_deleted "
-                + "   BEFORE DELETE ON " + Tables.RAW_CONTACTS
-                + " BEGIN "
-                + "   DELETE FROM " + Tables.DATA
-                + "     WHERE " + Data.RAW_CONTACT_ID
-                                + "=OLD." + RawContacts._ID + ";"
-                + "   DELETE FROM " + Tables.PHONE_LOOKUP
-                + "     WHERE " + PhoneLookupColumns.RAW_CONTACT_ID
-                                + "=OLD." + RawContacts._ID + ";"
-                + "   DELETE FROM " + Tables.NAME_LOOKUP
-                + "     WHERE " + NameLookupColumns.RAW_CONTACT_ID
-                                + "=OLD." + RawContacts._ID + ";"
-                + "   DELETE FROM " + Tables.AGGREGATION_EXCEPTIONS
-                + "     WHERE " + AggregationExceptions.RAW_CONTACT_ID1
-                                + "=OLD." + RawContacts._ID
-                + "        OR " + AggregationExceptions.RAW_CONTACT_ID2
-                                + "=OLD." + RawContacts._ID + ";"
-                + "   DELETE FROM " + Tables.CONTACTS
-                + "     WHERE " + Contacts._ID + "=OLD." + RawContacts.CONTACT_ID
-                + "       AND (SELECT COUNT(*) FROM " + Tables.RAW_CONTACTS
-                + "            WHERE " + RawContacts.CONTACT_ID + "=OLD." + RawContacts.CONTACT_ID
-                + "           )=1;"
-                + " END");
-
-        /**
-         * Triggers that update {@link RawContacts#VERSION} when the contact is
-         * marked for deletion or any time a data row is inserted, updated or
-         * deleted.
-         */
-        db.execSQL("CREATE TRIGGER " + Tables.RAW_CONTACTS + "_marked_deleted "
-                + "   BEFORE UPDATE ON " + Tables.RAW_CONTACTS
-                + " BEGIN "
-                + "   UPDATE " + Tables.RAW_CONTACTS
-                + "     SET "
-                +         RawContacts.VERSION + "=OLD." + RawContacts.VERSION + "+1 "
-                + "     WHERE " + RawContacts._ID + "=OLD." + RawContacts._ID
-                + "       AND NEW." + RawContacts.DELETED + "!= OLD." + RawContacts.DELETED + ";"
-                + " END");
-
-        db.execSQL("CREATE TRIGGER " + Tables.DATA + "_updated BEFORE UPDATE ON " + Tables.DATA
-                + " BEGIN "
-                + "   UPDATE " + Tables.DATA
-                + "     SET " + Data.DATA_VERSION + "=OLD." + Data.DATA_VERSION + "+1 "
-                + "     WHERE " + Data._ID + "=OLD." + Data._ID + ";"
-                + "   UPDATE " + Tables.RAW_CONTACTS
-                + "     SET " +	RawContacts.VERSION + "=" + RawContacts.VERSION + "+1 "
-                + "     WHERE " + RawContacts._ID + "=OLD." + Data.RAW_CONTACT_ID + ";"
-                + " END");
-
-        db.execSQL("CREATE TRIGGER " + Tables.DATA + "_deleted BEFORE DELETE ON " + Tables.DATA
-                + " BEGIN "
-                + "   UPDATE " + Tables.RAW_CONTACTS
-                + "     SET " + RawContacts.VERSION + "=" + RawContacts.VERSION + "+1 "
-                + "     WHERE " + RawContacts._ID + "=OLD." + Data.RAW_CONTACT_ID + ";"
-                + "   DELETE FROM " + Tables.PHONE_LOOKUP
-                + "     WHERE " + PhoneLookupColumns.DATA_ID + "=OLD." + Data._ID + ";"
-                + " END");
-
         // Private phone numbers table used for lookup
         db.execSQL("CREATE TABLE " + Tables.PHONE_LOOKUP + " (" +
                 PhoneLookupColumns.DATA_ID
@@ -923,15 +853,6 @@ import java.util.HashMap;
                 Groups.ACCOUNT_NAME +
         ");");
 
-        db.execSQL("CREATE TRIGGER " + Tables.GROUPS + "_updated1 "
-                + "   BEFORE UPDATE ON " + Tables.GROUPS
-                + " BEGIN "
-                + "   UPDATE " + Tables.GROUPS
-                + "     SET "
-                +         Groups.VERSION + "=OLD." + Groups.VERSION + "+1"
-                + "     WHERE " + Groups._ID + "=OLD." + Groups._ID + ";"
-                + " END");
-
         db.execSQL("CREATE TABLE IF NOT EXISTS " + Tables.AGGREGATION_EXCEPTIONS + " (" +
                 AggregationExceptionColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                 AggregationExceptions.TYPE + " INTEGER NOT NULL, " +
@@ -1005,6 +926,7 @@ import java.util.HashMap;
         createContactsViews(db);
         createGroupsView(db);
         createContactEntitiesView(db);
+        createContactsTriggers(db);
 
         loadNicknameLookupTable(db);
 
@@ -1025,6 +947,94 @@ import java.util.HashMap;
                 ContactsContract.AUTHORITY, new Bundle());
     }
 
+    private void createContactsTriggers(SQLiteDatabase db) {
+
+        /*
+         * Automatically delete Data rows when a raw contact is deleted.
+         */
+        db.execSQL("DROP TRIGGER IF EXISTS " + Tables.RAW_CONTACTS + "_deleted;");
+        db.execSQL("CREATE TRIGGER " + Tables.RAW_CONTACTS + "_deleted "
+                + "   BEFORE DELETE ON " + Tables.RAW_CONTACTS
+                + " BEGIN "
+                + "   DELETE FROM " + Tables.DATA
+                + "     WHERE " + Data.RAW_CONTACT_ID
+                                + "=OLD." + RawContacts._ID + ";"
+                + "   DELETE FROM " + Tables.AGGREGATION_EXCEPTIONS
+                + "     WHERE " + AggregationExceptions.RAW_CONTACT_ID1
+                                + "=OLD." + RawContacts._ID
+                + "        OR " + AggregationExceptions.RAW_CONTACT_ID2
+                                + "=OLD." + RawContacts._ID + ";"
+                + "   DELETE FROM " + Tables.CONTACTS
+                + "     WHERE " + Contacts._ID + "=OLD." + RawContacts.CONTACT_ID
+                + "       AND (SELECT COUNT(*) FROM " + Tables.RAW_CONTACTS
+                + "            WHERE " + RawContacts.CONTACT_ID + "=OLD." + RawContacts.CONTACT_ID
+                + "           )=1;"
+                + " END");
+
+
+        db.execSQL("DROP TRIGGER IF EXISTS contacts_times_contacted;");
+        db.execSQL("CREATE TRIGGER contacts_times_contacted UPDATE OF " +
+                Contacts.LAST_TIME_CONTACTED + " ON " + Tables.CONTACTS + " " +
+            "BEGIN " +
+                "UPDATE " + Tables.CONTACTS + " SET "
+                    + Contacts.TIMES_CONTACTED + " = " + "" +
+                            "(new." + Contacts.TIMES_CONTACTED + " + 1)"
+                    + " WHERE _id = new._id;" +
+            "END");
+
+        /*
+         * Triggers that update {@link RawContacts#VERSION} when the contact is
+         * marked for deletion or any time a data row is inserted, updated or
+         * deleted.
+         */
+        db.execSQL("DROP TRIGGER IF EXISTS " + Tables.RAW_CONTACTS + "_marked_deleted;");
+        db.execSQL("CREATE TRIGGER " + Tables.RAW_CONTACTS + "_marked_deleted "
+                + "   BEFORE UPDATE ON " + Tables.RAW_CONTACTS
+                + " BEGIN "
+                + "   UPDATE " + Tables.RAW_CONTACTS
+                + "     SET "
+                +         RawContacts.VERSION + "=OLD." + RawContacts.VERSION + "+1 "
+                + "     WHERE " + RawContacts._ID + "=OLD." + RawContacts._ID
+                + "       AND NEW." + RawContacts.DELETED + "!= OLD." + RawContacts.DELETED + ";"
+                + " END");
+
+        db.execSQL("DROP TRIGGER IF EXISTS " + Tables.DATA + "_updated;");
+        db.execSQL("CREATE TRIGGER " + Tables.DATA + "_updated BEFORE UPDATE ON " + Tables.DATA
+                + " BEGIN "
+                + "   UPDATE " + Tables.DATA
+                + "     SET " + Data.DATA_VERSION + "=OLD." + Data.DATA_VERSION + "+1 "
+                + "     WHERE " + Data._ID + "=OLD." + Data._ID + ";"
+                + "   UPDATE " + Tables.RAW_CONTACTS
+                + "     SET " + RawContacts.VERSION + "=" + RawContacts.VERSION + "+1 "
+                + "     WHERE " + RawContacts._ID + "=OLD." + Data.RAW_CONTACT_ID + ";"
+                + " END");
+
+        db.execSQL("DROP TRIGGER IF EXISTS " + Tables.DATA + "_deleted;");
+        db.execSQL("CREATE TRIGGER " + Tables.DATA + "_deleted BEFORE DELETE ON " + Tables.DATA
+                + " BEGIN "
+                + "   UPDATE " + Tables.RAW_CONTACTS
+                + "     SET " + RawContacts.VERSION + "=" + RawContacts.VERSION + "+1 "
+                + "     WHERE " + RawContacts._ID + "=OLD." + Data.RAW_CONTACT_ID + ";"
+                + "   DELETE FROM " + Tables.PHONE_LOOKUP
+                + "     WHERE " + PhoneLookupColumns.DATA_ID + "=OLD." + Data._ID + ";"
+                + "   DELETE FROM " + Tables.STATUS_UPDATES
+                + "     WHERE " + StatusUpdatesColumns.DATA_ID + "=OLD." + Data._ID + ";"
+                + "   DELETE FROM " + Tables.NAME_LOOKUP
+                + "     WHERE " + NameLookupColumns.DATA_ID + "=OLD." + Data._ID + ";"
+                + " END");
+
+
+        db.execSQL("DROP TRIGGER IF EXISTS " + Tables.GROUPS + "_updated1;");
+        db.execSQL("CREATE TRIGGER " + Tables.GROUPS + "_updated1 "
+                + "   BEFORE UPDATE ON " + Tables.GROUPS
+                + " BEGIN "
+                + "   UPDATE " + Tables.GROUPS
+                + "     SET "
+                +         Groups.VERSION + "=OLD." + Groups.VERSION + "+1"
+                + "     WHERE " + Groups._ID + "=OLD." + Groups._ID + ";"
+                + " END");
+    }
+
     private static void createContactsViews(SQLiteDatabase db) {
         db.execSQL("DROP VIEW IF EXISTS " + Views.CONTACTS_ALL + ";");
         db.execSQL("DROP VIEW IF EXISTS " + Views.CONTACTS_RESTRICTED + ";");
@@ -1307,6 +1317,11 @@ import java.util.HashMap;
             oldVersion++;
         }
 
+        if (oldVersion == 101) {
+            createContactsTriggers(db);
+            oldVersion++;
+        }
+
         if (oldVersion != newVersion) {
             throw new IllegalStateException(
                     "error upgrading the database to version " + newVersion);