From ce5e5f36c56fe14cb4a90a912eece07e4cb92285 Mon Sep 17 00:00:00 2001 From: Vincent Breitmoser Date: Wed, 16 Mar 2016 00:33:09 +0100 Subject: update null-check for duplicates, and add matching indexes to name and email columns --- .../keychain/provider/KeychainDatabase.java | 9 ++++++++- .../keychain/provider/KeychainProvider.java | 11 +++++------ 2 files changed, 13 insertions(+), 7 deletions(-) (limited to 'OpenKeychain/src/main/java/org/sufficientlysecure/keychain/provider') 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 c3697997f..04c14491b 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 = 15; + private static final int DATABASE_VERSION = 16; static Boolean apgHack = false; private Context mContext; @@ -311,6 +311,13 @@ public class KeychainDatabase extends SQLiteOpenHelper { db.execSQL("ALTER TABLE user_packets ADD COLUMN name TEXT"); db.execSQL("ALTER TABLE user_packets ADD COLUMN email TEXT"); db.execSQL("ALTER TABLE user_packets ADD COLUMN comment TEXT"); + case 15: + db.execSQL("CREATE INDEX uids_by_name ON user_packets (name COLLATE NOCASE)"); + db.execSQL("CREATE INDEX uids_by_email ON user_packets (email COLLATE NOCASE)"); + if (oldVersion == 14) { + // no consolidate necessary + return; + } } // always do consolidate after upgrade diff --git a/OpenKeychain/src/main/java/org/sufficientlysecure/keychain/provider/KeychainProvider.java b/OpenKeychain/src/main/java/org/sufficientlysecure/keychain/provider/KeychainProvider.java index 75a5e89b8..9c5d0c054 100644 --- a/OpenKeychain/src/main/java/org/sufficientlysecure/keychain/provider/KeychainProvider.java +++ b/OpenKeychain/src/main/java/org/sufficientlysecure/keychain/provider/KeychainProvider.java @@ -312,15 +312,14 @@ public class KeychainProvider extends ContentProvider { projectionMap.put(KeyRings.EMAIL, Tables.USER_PACKETS + "." + UserPackets.EMAIL); projectionMap.put(KeyRings.COMMENT, Tables.USER_PACKETS + "." + UserPackets.COMMENT); projectionMap.put(KeyRings.HAS_DUPLICATE_USER_ID, - "(EXISTS (SELECT * FROM " + Tables.USER_PACKETS + " AS dups" + "(EXISTS (SELECT * FROM " + Tables.USER_PACKETS + " AS dups" + " WHERE dups." + UserPackets.MASTER_KEY_ID + " != " + Tables.KEYS + "." + Keys.MASTER_KEY_ID + " AND dups." + UserPackets.RANK + " = 0" - + " AND (dups." + UserPackets.USER_ID + " = " + Tables.USER_PACKETS + "." + UserPackets.USER_ID + " COLLATE NOCASE" - + " OR (dups." + UserPackets.NAME + " = " + Tables.USER_PACKETS + "." + UserPackets.NAME + " COLLATE NOCASE" - + " AND dups." + UserPackets.EMAIL + " = " + Tables.USER_PACKETS + "." + UserPackets.EMAIL + " COLLATE NOCASE" - + ")" - + ")" + + " AND dups." + UserPackets.NAME + + " = " + Tables.USER_PACKETS + "." + UserPackets.NAME + " COLLATE NOCASE" + + " AND dups." + UserPackets.EMAIL + + " = " + Tables.USER_PACKETS + "." + UserPackets.EMAIL + " COLLATE NOCASE" + ")) AS " + KeyRings.HAS_DUPLICATE_USER_ID); projectionMap.put(KeyRings.VERIFIED, Tables.CERTS + "." + Certs.VERIFIED); projectionMap.put(KeyRings.PUBKEY_DATA, -- cgit v1.2.3