From 5bd5cf75e1d86baa49fb65836d69f294714d7066 Mon Sep 17 00:00:00 2001 From: Chris Thornton Date: Sun, 16 Oct 2016 16:23:29 -0700 Subject: [PATCH] Update primary key in VIS sound model database. The public API for getting at KeyphraseSoundModels is based on the user, locale, and keyphrase ID. However, the sound model database would allow multiple models to be registered using the same values, as it has a primary key on the model UUID. This can potentially lead to a scenario where multiple models are enrolled for the same user, and the data that is returned when querying models is arbitrary. V6 of the sound model database now enforces a primary key over the user ID, locale and keyphrase ID. When inserting a new model with the same primary key, the old model will be removed and the new model will be used instead. When upgrading from V5 to V6, if there are any models that would violate the primary key constraint, they are all dropped. Bug: 32174118 Change-Id: I9597c6a994f01a426625c3be2c53e826f26f5156 --- .../server/voiceinteraction/DatabaseHelper.java | 121 ++++++++++++++++++++- 1 file changed, 117 insertions(+), 4 deletions(-) diff --git a/services/voiceinteraction/java/com/android/server/voiceinteraction/DatabaseHelper.java b/services/voiceinteraction/java/com/android/server/voiceinteraction/DatabaseHelper.java index 0dcd1521deac..610f9bcdab4c 100644 --- a/services/voiceinteraction/java/com/android/server/voiceinteraction/DatabaseHelper.java +++ b/services/voiceinteraction/java/com/android/server/voiceinteraction/DatabaseHelper.java @@ -29,6 +29,8 @@ import android.util.Slog; import java.util.Locale; import java.util.UUID; +import java.util.List; +import java.util.ArrayList; /** * Helper to manage the database of the sound models that have been registered on the device. @@ -40,7 +42,7 @@ public class DatabaseHelper extends SQLiteOpenHelper { static final boolean DBG = false; private static final String NAME = "sound_model.db"; - private static final int VERSION = 5; + private static final int VERSION = 6; public static interface SoundModelContract { public static final String TABLE = "sound_model"; @@ -58,15 +60,19 @@ public class DatabaseHelper extends SQLiteOpenHelper { // Table Create Statement private static final String CREATE_TABLE_SOUND_MODEL = "CREATE TABLE " + SoundModelContract.TABLE + "(" - + SoundModelContract.KEY_MODEL_UUID + " TEXT PRIMARY KEY," - + SoundModelContract.KEY_VENDOR_UUID + " TEXT, " + + SoundModelContract.KEY_MODEL_UUID + " TEXT," + + SoundModelContract.KEY_VENDOR_UUID + " TEXT," + SoundModelContract.KEY_KEYPHRASE_ID + " INTEGER," + SoundModelContract.KEY_TYPE + " INTEGER," + SoundModelContract.KEY_DATA + " BLOB," + SoundModelContract.KEY_RECOGNITION_MODES + " INTEGER," + SoundModelContract.KEY_LOCALE + " TEXT," + SoundModelContract.KEY_HINT_TEXT + " TEXT," - + SoundModelContract.KEY_USERS + " TEXT" + ")"; + + SoundModelContract.KEY_USERS + " TEXT," + + "PRIMARY KEY (" + SoundModelContract.KEY_KEYPHRASE_ID + "," + + SoundModelContract.KEY_LOCALE + "," + + SoundModelContract.KEY_USERS + ")" + + ")"; public DatabaseHelper(Context context) { super(context, NAME, null, VERSION); @@ -93,6 +99,44 @@ public class DatabaseHelper extends SQLiteOpenHelper { oldVersion++; } } + if (oldVersion == 5) { + // We need to enforce the new primary key constraint that the + // keyphrase id, locale, and users are unique. We have to first pull + // everything out of the database, remove duplicates, create the new + // table, then push everything back in. + String selectQuery = "SELECT * FROM " + SoundModelContract.TABLE; + Cursor c = db.rawQuery(selectQuery, null); + List old_records = new ArrayList(); + try { + if (c.moveToFirst()) { + do { + try { + old_records.add(new SoundModelRecord(5, c)); + } catch (Exception e) { + Slog.e(TAG, "Failed to extract V5 record", e); + } + } while (c.moveToNext()); + } + } finally { + c.close(); + } + db.execSQL("DROP TABLE IF EXISTS " + SoundModelContract.TABLE); + onCreate(db); + for (SoundModelRecord record : old_records) { + if (!record.violatesV6PrimaryKeyConstraint(old_records)) { + try { + long return_value = record.writeToDatabase(6, db); + if (return_value == -1) { + Slog.e(TAG, "Database write failed " + record.modelUuid + ": " + + return_value); + } + } catch (Exception e) { + Slog.e(TAG, "Failed to update V6 record " + record.modelUuid, e); + } + } + } + oldVersion++; + } } /** @@ -279,4 +323,73 @@ public class DatabaseHelper extends SQLiteOpenHelper { } return users; } + + private static class SoundModelRecord { + public final String modelUuid; + public final String vendorUuid; + public final int keyphraseId; + public final int type; + public final byte[] data; + public final int recognitionModes; + public final String locale; + public final String hintText; + public final String users; + + public SoundModelRecord(int version, Cursor c) { + modelUuid = c.getString(c.getColumnIndex(SoundModelContract.KEY_MODEL_UUID)); + if (version >= 5) { + vendorUuid = c.getString(c.getColumnIndex(SoundModelContract.KEY_VENDOR_UUID)); + } else { + vendorUuid = null; + } + keyphraseId = c.getInt(c.getColumnIndex(SoundModelContract.KEY_KEYPHRASE_ID)); + type = c.getInt(c.getColumnIndex(SoundModelContract.KEY_TYPE)); + data = c.getBlob(c.getColumnIndex(SoundModelContract.KEY_DATA)); + recognitionModes = c.getInt(c.getColumnIndex(SoundModelContract.KEY_RECOGNITION_MODES)); + locale = c.getString(c.getColumnIndex(SoundModelContract.KEY_LOCALE)); + hintText = c.getString(c.getColumnIndex(SoundModelContract.KEY_HINT_TEXT)); + users = c.getString(c.getColumnIndex(SoundModelContract.KEY_USERS)); + } + + // Check to see if this record conflicts with some other record in the list of records. + public boolean violatesV6PrimaryKeyConstraint(List records) { + for (SoundModelRecord record : records) { + if (this == record) { + continue; + } + if (keyphraseId == record.keyphraseId + && stringComparisonHelper(locale, record.locale) + && stringComparisonHelper(users, record.users)) { + return true; + } + } + return false; + } + + public long writeToDatabase(int version, SQLiteDatabase db) { + ContentValues values = new ContentValues(); + values.put(SoundModelContract.KEY_MODEL_UUID, modelUuid); + if (version >= 5) { + values.put(SoundModelContract.KEY_VENDOR_UUID, vendorUuid); + } + values.put(SoundModelContract.KEY_KEYPHRASE_ID, keyphraseId); + values.put(SoundModelContract.KEY_TYPE, type); + values.put(SoundModelContract.KEY_DATA, data); + values.put(SoundModelContract.KEY_RECOGNITION_MODES, recognitionModes); + values.put(SoundModelContract.KEY_LOCALE, locale); + values.put(SoundModelContract.KEY_HINT_TEXT, hintText); + values.put(SoundModelContract.KEY_USERS, users); + + return db.insertWithOnConflict( + SoundModelContract.TABLE, null, values, SQLiteDatabase.CONFLICT_REPLACE); + } + + // Helper for checking string equality - including the case when they are null. + static private boolean stringComparisonHelper(String a, String b) { + if (a != null) { + return a.equals(b); + } + return a == b; + } + } } -- 2.11.0