some optimizations and indexes for the main key list query

1 files changed, 16 insertions, 6 deletions

diff --git a/OpenKeychain/src/main/java/org/sufficientlysecure/keychain/provider/KeychainDatabase.java b/OpenKeychain/src/main/java/org/sufficientlysecure/keychain/provider/KeychainDatabase.java
index 0f90f8141..ff5e64bb6 100644
--- a/OpenKeychain/src/main/java/org/sufficientlysecure/keychain/provider/KeychainDatabase.java
+++ b/OpenKeychain/src/main/java/org/sufficientlysecure/keychain/provider/KeychainDatabase.java
@@ -54,7 +54,7 @@ import java.io.IOException;
  */
 public class KeychainDatabase extends SQLiteOpenHelper {
     private static final String DATABASE_NAME = "openkeychain.db";
-    private static final int DATABASE_VERSION = 13;
+    private static final int DATABASE_VERSION = 14;
     static Boolean apgHack = false;
     private Context mContext;
 
@@ -74,12 +74,14 @@ public class KeychainDatabase extends SQLiteOpenHelper {
             "CREATE TABLE IF NOT EXISTS keyrings_public ("
                 + KeyRingsColumns.MASTER_KEY_ID + " INTEGER PRIMARY KEY,"
                 + KeyRingsColumns.KEY_RING_DATA + " BLOB"
+                    + "PRIMARY KEY(" + KeyRingsColumns.MASTER_KEY_ID + "),"
             + ")";
 
     private static final String CREATE_KEYRINGS_SECRET =
             "CREATE TABLE IF NOT EXISTS keyrings_secret ("
                     + KeyRingsColumns.MASTER_KEY_ID + " INTEGER PRIMARY KEY,"
                     + KeyRingsColumns.KEY_RING_DATA + " BLOB,"
+                    + "PRIMARY KEY(" + KeyRingsColumns.MASTER_KEY_ID + "),"
                     + "FOREIGN KEY(" + KeyRingsColumns.MASTER_KEY_ID + ") "
                         + "REFERENCES keyrings_public(" + KeyRingsColumns.MASTER_KEY_ID + ") ON DELETE CASCADE"
             + ")";
@@ -220,6 +222,13 @@ public class KeychainDatabase extends SQLiteOpenHelper {
         db.execSQL(CREATE_API_APPS);
         db.execSQL(CREATE_API_APPS_ACCOUNTS);
         db.execSQL(CREATE_API_APPS_ALLOWED_KEYS);
+
+        db.execSQL("CREATE INDEX keys_by_rank ON keys (" + KeysColumns.RANK + ");");
+        db.execSQL("CREATE INDEX uids_by_rank ON user_packets (" + UserPacketsColumns.RANK + ", "
+                + UserPacketsColumns.USER_ID + ", " + UserPacketsColumns.MASTER_KEY_ID + ");");
+        db.execSQL("CREATE INDEX verified_certs ON certs ("
+                + CertsColumns.VERIFIED + ", " + CertsColumns.MASTER_KEY_ID + ");");
+
     }
 
     @Override
@@ -291,13 +300,14 @@ public class KeychainDatabase extends SQLiteOpenHelper {
                 db.execSQL("DELETE FROM api_accounts WHERE key_id BETWEEN 0 AND 3");
             case 12:
                 db.execSQL(CREATE_UPDATE_KEYS);
-                if (oldVersion == 10) {
-                    // no consolidate if we are updating from 10, we're just here for
-                    // the api_accounts fix and the new update keys table
-                    return;
-                }
             case 13:
                 // do nothing here, just consolidate
+            case 14:
+                db.execSQL("CREATE INDEX keys_by_rank ON keys (" + KeysColumns.RANK + ");");
+                db.execSQL("CREATE INDEX uids_by_rank ON user_packets (" + UserPacketsColumns.RANK + ", "
+                        + UserPacketsColumns.USER_ID + ", " + UserPacketsColumns.MASTER_KEY_ID + ");");
+                db.execSQL("CREATE INDEX verified_certs ON certs ("
+                        + CertsColumns.VERIFIED + ", " + CertsColumns.MASTER_KEY_ID + ");");
 
         }