Bug 900289: Enable foreign key pragma on FHR database. r=rnewman
authorMichael Comella <michael.l.comella@gmail.com>
Thu, 08 Aug 2013 20:54:36 -0700
changeset 154758 e881258c3ac5c56a02c89fcecdd15fcb5559678d
parent 154757 eab4756f594dc8d33f61990ca8466adc1443e5a0
child 154759 e33c2011643e323aa29739ac67c28c588197cbb1
push id2961
push userlsblakk@mozilla.com
push dateMon, 28 Oct 2013 21:59:28 +0000
treeherdermozilla-beta@73ef4f13486f [default view] [failures only]
perfherder[talos] [build metrics] [platform microbench] (compared to previous push)
reviewersrnewman
bugs900289
milestone26.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 900289: Enable foreign key pragma on FHR database. r=rnewman
mobile/android/base/background/healthreport/HealthReportDatabaseStorage.java
--- a/mobile/android/base/background/healthreport/HealthReportDatabaseStorage.java
+++ b/mobile/android/base/background/healthreport/HealthReportDatabaseStorage.java
@@ -15,16 +15,17 @@ import org.json.JSONObject;
 import org.mozilla.gecko.background.common.log.Logger;
 import org.mozilla.gecko.background.healthreport.HealthReportStorage.MeasurementFields.FieldSpec;
 
 import android.content.ContentValues;
 import android.content.Context;
 import android.content.ContextWrapper;
 import android.database.Cursor;
 import android.database.SQLException;
+import android.database.sqlite.SQLiteConstraintException;
 import android.database.sqlite.SQLiteDatabase;
 import android.database.sqlite.SQLiteOpenHelper;
 import android.os.Build;
 import android.util.SparseArray;
 
 /**
  * <code>HealthReportDatabaseStorage</code> provides an interface on top of
  * SQLite storage for Health Report data. It exposes methods for management of
@@ -181,17 +182,17 @@ public class HealthReportDatabaseStorage
     this.fields.clear();
     this.envs.clear();
     this.measurementVersions.clear();
   }
 
   protected final HealthReportSQLiteOpenHelper helper;
 
   public static class HealthReportSQLiteOpenHelper extends SQLiteOpenHelper {
-    public static final int CURRENT_VERSION = 4;
+    public static final int CURRENT_VERSION = 5;
     public static final String LOG_TAG = "HealthReportSQL";
 
     /**
      * A little helper to avoid SQLiteOpenHelper misbehaving on Android 2.1.
      * Partly cribbed from
      * <http://stackoverflow.com/questions/5332328/sqliteopenhelper-problem-with-fully-qualified-db-path-name>.
      */
     public static class AbsolutePathContext extends ContextWrapper {
@@ -222,21 +223,26 @@ public class HealthReportDatabaseStorage
     }
 
     public static String getAbsolutePath(File parent, String name) {
       return parent.getAbsolutePath() + File.separator + name;
     }
 
     public static boolean CAN_USE_ABSOLUTE_DB_PATH = (Build.VERSION.SDK_INT >= Build.VERSION_CODES.FROYO);
     public HealthReportSQLiteOpenHelper(Context context, File profileDirectory, String name) {
+      this(context, profileDirectory, name, CURRENT_VERSION);
+    }
+
+    // For testing DBs of different versions.
+    public HealthReportSQLiteOpenHelper(Context context, File profileDirectory, String name, int version) {
       super(
           (CAN_USE_ABSOLUTE_DB_PATH ? context : new AbsolutePathContext(context, profileDirectory)),
           (CAN_USE_ABSOLUTE_DB_PATH ? getAbsolutePath(profileDirectory, name) : name),
           null,
-          CURRENT_VERSION);
+          version);
 
       if (CAN_USE_ABSOLUTE_DB_PATH) {
         Logger.pii(LOG_TAG, "Opening: " + getAbsolutePath(profileDirectory, name));
       }
     }
 
     @Override
     public void onCreate(SQLiteDatabase db) {
@@ -342,16 +348,23 @@ public class HealthReportDatabaseStorage
         createAddonsEnvironmentsView(db);
 
         db.setTransactionSuccessful();
       } finally {
         db.endTransaction();
       }
     }
 
+    @Override
+    public void onOpen(SQLiteDatabase db) {
+      if (!db.isReadOnly()) {
+        db.execSQL("PRAGMA foreign_keys=ON;");
+      }
+    }
+
     private void createAddonsEnvironmentsView(SQLiteDatabase db) {
       db.execSQL("CREATE VIEW environments_with_addons AS " +
           "SELECT e.id AS id, " +
           "       e.hash AS hash, " +
           "       e.profileCreation AS profileCreation, " +
           "       e.cpuCount AS cpuCount, " +
           "       e.memoryMB AS memoryMB, " +
           "       e.isBlocklistEnabled AS isBlocklistEnabled, " +
@@ -389,30 +402,48 @@ public class HealthReportDatabaseStorage
     }
 
     private void upgradeDatabaseFrom3To4(SQLiteDatabase db) {
       // Update search measurements to use a different type.
       db.execSQL("UPDATE OR IGNORE fields SET flags = " + Field.TYPE_COUNTED_STRING_DISCRETE +
                  " WHERE measurement IN (SELECT id FROM measurements WHERE name = 'org.mozilla.searches.counts')");
     }
 
+    private void upgradeDatabaseFrom4to5(SQLiteDatabase db) {
+      // Delete NULL in addons.body, which appeared as a result of Bug 886156. Note that the
+      // foreign key constraint, "ON DELETE RESTRICT", may be violated, but since onOpen() is
+      // called after this method, foreign keys are not yet enabled and constraints can be broken.
+      db.delete("addons", "body IS NULL", null);
+
+      // Purge any data inconsistent with foreign key references (which may have appeared before
+      // foreign keys were enabled in Bug 900289).
+      db.delete("fields", "measurement NOT IN (SELECT id FROM measurements)", null);
+      db.delete("environments", "addonsID NOT IN (SELECT id from addons)", null);
+      db.delete(EVENTS_INTEGER, "env NOT IN (SELECT id FROM environments)", null);
+      db.delete(EVENTS_TEXTUAL, "env NOT IN (SELECT id FROM environments)", null);
+      db.delete(EVENTS_INTEGER, "field NOT IN (SELECT id FROM fields)", null);
+      db.delete(EVENTS_TEXTUAL, "field NOT IN (SELECT id FROM fields)", null);
+    }
+
     @Override
     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
       if (oldVersion >= newVersion) {
         return;
       }
 
       Logger.info(LOG_TAG, "onUpgrade: from " + oldVersion + " to " + newVersion + ".");
       try {
         db.beginTransaction();
         switch (oldVersion) {
         case 2:
           upgradeDatabaseFrom2To3(db);
         case 3:
           upgradeDatabaseFrom3To4(db);
+        case 4:
+          upgradeDatabaseFrom4to5(db);
         }
         db.setTransactionSuccessful();
       } catch (Exception e) {
         Logger.error(LOG_TAG, "Failure in onUpgrade.", e);
         throw new RuntimeException(e);
       } finally {
         db.endTransaction();
       }
@@ -1026,17 +1057,21 @@ public class HealthReportDatabaseStorage
     // UNIQUE(env, field, day) constraint for daily-last values, then we could
     // use INSERT OR REPLACE.
     final int updated = db.update(table, v, WHERE_DATE_AND_ENV_AND_FIELD,
                                   new String[] {dayString, envString, fieldIDString});
     if (0 == updated) {
       v.put("env", env);
       v.put("field", field);
       v.put("date", day);
-      db.insert(table, null, v);
+      try {
+        db.insertOrThrow(table, null, v);
+      } catch (SQLiteConstraintException e) {
+        throw new IllegalStateException("Event did not reference existing an environment or field.", e);
+      }
     }
   }
 
   @Override
   public void recordDailyLast(int env, int day, int field, JSONObject value) {
     this.recordDailyLast(env, day, field, value == null ? "null" : value.toString(), EVENTS_TEXTUAL);
   }
 
@@ -1058,17 +1093,21 @@ public class HealthReportDatabaseStorage
 
     final ContentValues v = new ContentValues();
     v.put("env", env);
     v.put("field", field);
     v.put("date", day);
 
     final SQLiteDatabase db = this.helper.getWritableDatabase();
     putValue(v, value);
-    db.insert(table, null, v);
+    try {
+      db.insertOrThrow(table, null, v);
+    } catch (SQLiteConstraintException e) {
+      throw new IllegalStateException("Event did not reference existing an environment or field.", e);
+    }
   }
 
   @Override
   public void recordDailyDiscrete(int env, int day, int field, JSONObject value) {
     this.recordDailyDiscrete(env, day, field, value == null ? "null" : value.toString(), EVENTS_TEXTUAL);
   }
 
   @Override
@@ -1128,17 +1167,21 @@ public class HealthReportDatabaseStorage
                  WHERE_DATE_AND_ENV_AND_FIELD,
                  args);
     } else {
       final ContentValues v = new ContentValues();
       v.put("env", env);
       v.put("value", by);
       v.put("field", field);
       v.put("date", day);
-      db.insert(EVENTS_INTEGER, null, v);
+      try {
+        db.insertOrThrow(EVENTS_INTEGER, null, v);
+      } catch (SQLiteConstraintException e) {
+        throw new IllegalStateException("Event did not reference existing an environment or field.", e);
+      }
     }
   }
 
   @Override
   public void incrementDailyCount(int env, int day, int field) {
     this.incrementDailyCount(env, day, field, 1);
   }