ContactsDatabaseHelper.java revision b650982af7aeb2800efdcea587b8ce153259cf1c
1b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey/* 2b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * Copyright (C) 2009 The Android Open Source Project 3b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * 4b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * Licensed under the Apache License, Version 2.0 (the "License"); 5b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * you may not use this file except in compliance with the License. 6b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * You may obtain a copy of the License at 7b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * 8b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * http://www.apache.org/licenses/LICENSE-2.0 9b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * 10b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * Unless required by applicable law or agreed to in writing, software 11b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * distributed under the License is distributed on an "AS IS" BASIS, 12b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * See the License for the specific language governing permissions and 14b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * limitations under the License 15b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey */ 16b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 17b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkeypackage com.android.providers.contacts2; 18b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 19b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkeyimport com.android.providers.contacts2.SocialContract.Activities; 20b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkeyimport com.android.providers.contacts2.ContactsContract.Aggregates; 21b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkeyimport com.android.providers.contacts2.ContactsContract.Contacts; 22b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkeyimport com.android.providers.contacts2.ContactsContract.Data; 23b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 24b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkeyimport android.content.Context; 25b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkeyimport android.database.DatabaseUtils; 26b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkeyimport android.database.sqlite.SQLiteDatabase; 27b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkeyimport android.database.sqlite.SQLiteDoneException; 28b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkeyimport android.database.sqlite.SQLiteOpenHelper; 29b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkeyimport android.database.sqlite.SQLiteStatement; 30b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkeyimport android.provider.BaseColumns; 31b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkeyimport android.util.Log; 32b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 33b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkeyimport java.util.HashMap; 34b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 35b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey/** 36b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * Database open helper for contacts and social activity data. Designed as a 37b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * singleton to make sure that all {@link ContentProvider} users get the same 38b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * reference. Provides handy methods for maintaining package and mime-type 39b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * lookup tables. 40b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey */ 41b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey/* package */ class OpenHelper extends SQLiteOpenHelper { 42b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey private static final String TAG = "OpenHelper"; 43b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 44b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey private static final int DATABASE_VERSION = 14; 45b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey private static final String DATABASE_NAME = "contacts2.db"; 46b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 47b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public interface Tables { 48b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String AGGREGATES = "aggregates"; 49b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String CONTACTS = "contacts"; 50b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String PACKAGE = "package"; 51b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String MIMETYPE = "mimetype"; 52b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String PHONE_LOOKUP = "phone_lookup"; 53b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 54b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String DATA = "data"; 55b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 56b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String DATA_JOIN_MIMETYPE = "data " 57b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey + "LEFT OUTER JOIN mimetype ON (data.mimetype_id = mimetype._id)"; 58b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 59b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String DATA_JOIN_PACKAGE_MIMETYPE = "data " 60b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey + "LEFT OUTER JOIN package ON (data.package_id = package._id)" 61b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey + "LEFT OUTER JOIN mimetype ON (data.mimetype_id = mimetype._id)"; 62b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 63b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String DATA_JOIN_AGGREGATES_PACKAGE_MIMETYPE = "data " 64b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey + "LEFT OUTER JOIN package ON (data.package_id = package._id)" 65b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey + "LEFT OUTER JOIN mimetype ON (data.mimetype_id = mimetype._id)" 66b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey + "LEFT JOIN contacts ON (data.contact_id = contacts._id) " 67b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey + "LEFT JOIN aggregates ON (contacts.aggregate_id = aggregates._id)"; 68b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 69b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String ACTIVITIES = "activities"; 70b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 71b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String ACTIVITIES_JOIN_MIMETYPE = "activities " 72b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey + "LEFT OUTER JOIN mimetype ON (activities.mimetype_id = mimetype._id)"; 73b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 74b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String ACTIVITIES_JOIN_PACKAGE_MIMETYPE = "activities " 75b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey + "LEFT OUTER JOIN package ON (activities.package_id = package._id)" 76b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey + "LEFT OUTER JOIN mimetype ON (activities.mimetype_id = mimetype._id)"; 77b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 78b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 79b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 80b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public interface ContactsColumns { 81b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String AGGREGATION_NEEDED = "aggregation_needed"; 82b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 83b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 84b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public interface DataColumns { 85b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String PACKAGE_ID = "package_id"; 86b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String MIMETYPE_ID = "mimetype_id"; 87b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 88b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 89b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public interface ActivitiesColumns { 90b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String PACKAGE_ID = "package_id"; 91b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String MIMETYPE_ID = "mimetype_id"; 92b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 93b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 94b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public interface PhoneLookupColumns { 95b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String _ID = BaseColumns._ID; 96b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String DATA_ID = "data_id"; 97b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String CONTACT_ID = "contact_id"; 98b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String NORMALIZED_NUMBER = "normalized_number"; 99b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 100b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 101b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey private interface PackageColumns { 102b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String _ID = BaseColumns._ID; 103b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String PACKAGE = "package"; 104b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 105b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 106b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey private interface MimetypeColumns { 107b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String _ID = BaseColumns._ID; 108b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static final String MIMETYPE = "mimetype"; 109b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 110b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 111b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 112b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 113b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey /** In-memory cache of previously found mimetype mappings */ 114b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey private HashMap<String, Long> mMimetypeCache = new HashMap<String, Long>(); 115b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey /** In-memory cache of previously found package name mappings */ 116b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey private HashMap<String, Long> mPackageCache = new HashMap<String, Long>(); 117b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 118b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 119b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey /** Compiled statements for querying and inserting mappings */ 120b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey private SQLiteStatement mMimetypeQuery; 121b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey private SQLiteStatement mPackageQuery; 122b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey private SQLiteStatement mMimetypeInsert; 123b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey private SQLiteStatement mPackageInsert; 124b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 125b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey private SQLiteStatement mDataMimetypeQuery; 126b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey private SQLiteStatement mActivitiesMimetypeQuery; 127b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 128b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey private static OpenHelper sSingleton = null; 129b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 130b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public static synchronized OpenHelper getInstance(Context context) { 131b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey if (sSingleton == null) { 132b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey sSingleton = new OpenHelper(context); 133b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 134b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey return sSingleton; 135b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 136b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 137b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey private OpenHelper(Context context) { 138b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey super(context, DATABASE_NAME, null, DATABASE_VERSION); 139b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Log.i(TAG, "Creating OpenHelper"); 140b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 141b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 142b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 143b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 144b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey @Override 145b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public void onOpen(SQLiteDatabase db) { 146b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey // Create compiled statements for package and mimetype lookups 147b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey mMimetypeQuery = db.compileStatement("SELECT " + MimetypeColumns._ID + " FROM " 148b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey + Tables.MIMETYPE + " WHERE " + MimetypeColumns.MIMETYPE + "=?"); 149b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey mPackageQuery = db.compileStatement("SELECT " + PackageColumns._ID + " FROM " 150b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey + Tables.PACKAGE + " WHERE " + PackageColumns.PACKAGE + "=?"); 151b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey mMimetypeInsert = db.compileStatement("INSERT INTO " + Tables.MIMETYPE + "(" 152b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey + MimetypeColumns.MIMETYPE + ") VALUES (?)"); 153b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey mPackageInsert = db.compileStatement("INSERT INTO " + Tables.PACKAGE + "(" 154b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey + PackageColumns.PACKAGE + ") VALUES (?)"); 155b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 156b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey mDataMimetypeQuery = db.compileStatement("SELECT " + MimetypeColumns.MIMETYPE + " FROM " 157b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey + Tables.DATA_JOIN_MIMETYPE + " WHERE " + Tables.DATA + "." + Data._ID + "=?"); 158b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey mActivitiesMimetypeQuery = db.compileStatement("SELECT " + MimetypeColumns.MIMETYPE 159b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey + " FROM " + Tables.ACTIVITIES_JOIN_MIMETYPE + " WHERE " + Tables.ACTIVITIES + "." 160b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey + Activities._ID + "=?"); 161b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 162b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 163b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey @Override 164b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public void onCreate(SQLiteDatabase db) { 165b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Log.i(TAG, "Bootstrapping database"); 166b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 167b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey // One row per group of contacts corresponding to the same person 168b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey db.execSQL("CREATE TABLE " + Tables.AGGREGATES + " (" + 169b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 170b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Aggregates.DISPLAY_NAME + " TEXT," + 171b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Aggregates.TIMES_CONTACTED + " INTEGER," + 172b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Aggregates.LAST_TIME_CONTACTED + " INTEGER," + 173b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Aggregates.STARRED + " INTEGER" + 174b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey ");"); 175b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 176b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey // Contacts table 177b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey db.execSQL("CREATE TABLE " + Tables.CONTACTS + " (" + 178b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Contacts._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 179b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Contacts.AGGREGATE_ID + " INTEGER, " + 180b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey ContactsColumns.AGGREGATION_NEEDED + " INTEGER," + 181b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Contacts.CUSTOM_RINGTONE + " TEXT," + 182b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Contacts.SEND_TO_VOICEMAIL + " INTEGER" + 183b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey ");"); 184b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 185b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey // Package name mapping table 186b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey db.execSQL("CREATE TABLE " + Tables.PACKAGE + " (" + 187b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey PackageColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 188b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey PackageColumns.PACKAGE + " TEXT" + 189b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey ");"); 190b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 191b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey // Mime-type mapping table 192b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey db.execSQL("CREATE TABLE " + Tables.MIMETYPE + " (" + 193b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey MimetypeColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 194b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey MimetypeColumns.MIMETYPE + " TEXT" + 195b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey ");"); 196b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 197b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey // Public generic data table 198b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey db.execSQL("CREATE TABLE " + Tables.DATA + " (" + 199b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Data._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 200b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey DataColumns.PACKAGE_ID + " INTEGER REFERENCES package(_id) NOT NULL," + 201b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey DataColumns.MIMETYPE_ID + " INTEGER REFERENCES mimetype(_id) NOT NULL," + 202b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Data.CONTACT_ID + " INTEGER NOT NULL," + 203b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Data.DATA1 + " NUMERIC," + 204b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Data.DATA2 + " NUMERIC," + 205b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Data.DATA3 + " NUMERIC," + 206b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Data.DATA4 + " NUMERIC," + 207b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Data.DATA5 + " NUMERIC," + 208b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Data.DATA6 + " NUMERIC," + 209b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Data.DATA7 + " NUMERIC," + 210b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Data.DATA8 + " NUMERIC," + 211b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Data.DATA9 + " NUMERIC," + 212b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Data.DATA10 + " NUMERIC" + 213b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey ");"); 214b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 215b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey // Private phone numbers table used for lookup 216b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey db.execSQL("CREATE TABLE " + Tables.PHONE_LOOKUP + " (" + 217b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey PhoneLookupColumns._ID + " INTEGER PRIMARY KEY," + 218b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey PhoneLookupColumns.DATA_ID + " INTEGER REFERENCES data(_id) NOT NULL," + 219b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey PhoneLookupColumns.CONTACT_ID + " INTEGER REFERENCES contacts(_id) NOT NULL," + 220b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey PhoneLookupColumns.NORMALIZED_NUMBER + " TEXT NOT NULL" + 221b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey ");"); 222b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 223b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey db.execSQL("CREATE INDEX phone_lookup_index ON " + Tables.PHONE_LOOKUP + " (" + 224b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey PhoneLookupColumns.NORMALIZED_NUMBER + " ASC, " + 225b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey PhoneLookupColumns.CONTACT_ID + ", " + 226b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey PhoneLookupColumns.DATA_ID + 227b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey ");"); 228b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 229b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey // Activities table 230b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey db.execSQL("CREATE TABLE " + Tables.ACTIVITIES + " (" + 231b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Activities._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 232b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey ActivitiesColumns.PACKAGE_ID + " INTEGER REFERENCES package(_id) NOT NULL," + 233b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey ActivitiesColumns.MIMETYPE_ID + " INTEGER REFERENCES mimetype(_id) NOT NULL," + 234b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Activities.RAW_ID + " TEXT," + 235b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Activities.IN_REPLY_TO + " INTEGER REFERENCES activities(_id)," + 236b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Activities.AUTHOR_CONTACT_ID + " INTEGER REFERENCES contacts(_id)," + 237b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Activities.TARGET_CONTACT_ID + " INTEGER REFERENCES contacts(_id)," + 238b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Activities.PUBLISHED + " INTEGER NOT NULL," + 239b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Activities.TITLE + " TEXT NOT NULL," + 240b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Activities.SUMMARY + " TEXT," + 241b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Activities.THUMBNAIL + " BLOB" + 242b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey ");"); 243b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 244b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 245b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 246b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey @Override 247b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 248b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey Log.i(TAG, "Upgraing from version " + oldVersion + " to " + newVersion 249b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey + ", data will be lost!"); 250b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 251b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey db.execSQL("DROP TABLE IF EXISTS " + Tables.AGGREGATES + ";"); 252b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey db.execSQL("DROP TABLE IF EXISTS " + Tables.CONTACTS + ";"); 253b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey db.execSQL("DROP TABLE IF EXISTS " + Tables.PACKAGE + ";"); 254b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey db.execSQL("DROP TABLE IF EXISTS " + Tables.MIMETYPE + ";"); 255b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey db.execSQL("DROP TABLE IF EXISTS " + Tables.DATA + ";"); 256b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey db.execSQL("DROP TABLE IF EXISTS " + Tables.PHONE_LOOKUP + ";"); 257b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey db.execSQL("DROP TABLE IF EXISTS " + Tables.ACTIVITIES + ";"); 258b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 259b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey onCreate(db); 260b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 261b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 262b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 263b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey /** 264b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * Perform an internal string-to-integer lookup using the compiled 265b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * {@link SQLiteStatement} provided, using the in-memory cache to speed up 266b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * lookups. If a mapping isn't found in cache or database, it will be 267b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * created. All new, uncached answers are added to the cache automatically. 268b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * 269b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * @param query Compiled statement used to query for the mapping. 270b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * @param insert Compiled statement used to insert a new mapping when no 271b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * existing one is found in cache or from query. 272b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * @param value Value to find mapping for. 273b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * @param cache In-memory cache of previous answers. 274b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * @return An unique integer mapping for the given value. 275b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey */ 276b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey private synchronized long getCachedId(SQLiteStatement query, SQLiteStatement insert, 277b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey String value, HashMap<String, Long> cache) { 278b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey // Try an in-memory cache lookup 279b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey if (cache.containsKey(value)) { 280b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey return cache.get(value); 281b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 282b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 283b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey long id = -1; 284b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey try { 285b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey // Try searching database for mapping 286b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey DatabaseUtils.bindObjectToProgram(query, 1, value); 287b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey id = query.simpleQueryForLong(); 288b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } catch (SQLiteDoneException e) { 289b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey // Nothing found, so try inserting new mapping 290b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey DatabaseUtils.bindObjectToProgram(insert, 1, value); 291b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey id = insert.executeInsert(); 292b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 293b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 294b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey if (id != -1) { 295b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey // Cache and return the new answer 296b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey cache.put(value, id); 297b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey return id; 298b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } else { 299b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey // Otherwise throw if no mapping found or created 300b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey throw new IllegalStateException("Couldn't find or create internal " 301b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey + "lookup table entry for value " + value); 302b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 303b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 304b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 305b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey /** 306b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * Convert a package name into an integer, using {@link Tables#PACKAGE} for 307b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * lookups and possible allocation of new IDs as needed. 308b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey */ 309b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public long getPackageId(String packageName) { 310b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey // Make sure compiled statements are ready by opening database 311b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey getReadableDatabase(); 312b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey return getCachedId(mPackageQuery, mPackageInsert, packageName, mPackageCache); 313b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 314b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 315b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey /** 316b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * Convert a mime-type into an integer, using {@link Tables#MIMETYPE} for 317b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * lookups and possible allocation of new IDs as needed. 318b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey */ 319b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public long getMimeTypeId(String mimetype) { 320b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey // Make sure compiled statements are ready by opening database 321b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey getReadableDatabase(); 322b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey return getCachedId(mMimetypeQuery, mMimetypeInsert, mimetype, mMimetypeCache); 323b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 324b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 325b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey /** 326b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * Find the mime-type for the given {@link Data#_ID}. 327b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey */ 328b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public String getDataMimeType(long dataId) { 329b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey // Make sure compiled statements are ready by opening database 330b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey getReadableDatabase(); 331b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey try { 332b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey // Try database query to find mimetype 333b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey DatabaseUtils.bindObjectToProgram(mDataMimetypeQuery, 1, dataId); 334b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey String mimetype = mDataMimetypeQuery.simpleQueryForString(); 335b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey return mimetype; 336b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } catch (SQLiteDoneException e) { 337b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey // No valid mapping found, so return null 338b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey return null; 339b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 340b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 341b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey 342b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey /** 343b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey * Find the mime-type for the given {@link Activities#_ID}. 344b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey */ 345b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey public String getActivityMimeType(long activityId) { 346b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey // Make sure compiled statements are ready by opening database 347b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey getReadableDatabase(); 348b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey try { 349b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey // Try database query to find mimetype 350b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey DatabaseUtils.bindObjectToProgram(mActivitiesMimetypeQuery, 1, activityId); 351b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey String mimetype = mActivitiesMimetypeQuery.simpleQueryForString(); 352b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey return mimetype; 353b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } catch (SQLiteDoneException e) { 354b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey // No valid mapping found, so return null 355b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey return null; 356b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 357b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey } 358b650982af7aeb2800efdcea587b8ce153259cf1cJeff Sharkey} 359