ContactsDatabaseHelper.java revision 04b7ce026c73077d9d982742bc662ea4b3ac74e7
1/* 2 * Copyright (C) 2009 The Android Open Source Project 3 * 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License 15 */ 16 17package com.android.providers.contacts; 18 19import com.android.internal.content.SyncStateContentProviderHelper; 20 21import android.content.ContentResolver; 22import android.content.ContentValues; 23import android.content.Context; 24import android.content.pm.ApplicationInfo; 25import android.content.pm.PackageManager; 26import android.content.pm.PackageManager.NameNotFoundException; 27import android.content.res.Resources; 28import android.database.Cursor; 29import android.database.DatabaseUtils; 30import android.database.SQLException; 31import android.database.sqlite.SQLiteDatabase; 32import android.database.sqlite.SQLiteDoneException; 33import android.database.sqlite.SQLiteException; 34import android.database.sqlite.SQLiteOpenHelper; 35import android.database.sqlite.SQLiteQueryBuilder; 36import android.database.sqlite.SQLiteStatement; 37import android.os.Binder; 38import android.os.Bundle; 39import android.provider.BaseColumns; 40import android.provider.ContactsContract; 41import android.provider.CallLog.Calls; 42import android.provider.ContactsContract.AggregationExceptions; 43import android.provider.ContactsContract.Contacts; 44import android.provider.ContactsContract.Data; 45import android.provider.ContactsContract.DisplayNameSources; 46import android.provider.ContactsContract.FullNameStyle; 47import android.provider.ContactsContract.Groups; 48import android.provider.ContactsContract.RawContacts; 49import android.provider.ContactsContract.Settings; 50import android.provider.ContactsContract.StatusUpdates; 51import android.provider.ContactsContract.CommonDataKinds.Email; 52import android.provider.ContactsContract.CommonDataKinds.GroupMembership; 53import android.provider.ContactsContract.CommonDataKinds.Nickname; 54import android.provider.ContactsContract.CommonDataKinds.Organization; 55import android.provider.ContactsContract.CommonDataKinds.Phone; 56import android.provider.ContactsContract.CommonDataKinds.StructuredName; 57import android.provider.SocialContract.Activities; 58import android.telephony.PhoneNumberUtils; 59import android.text.TextUtils; 60import android.text.util.Rfc822Token; 61import android.text.util.Rfc822Tokenizer; 62import android.util.Log; 63 64import java.util.HashMap; 65import java.util.Locale; 66 67/** 68 * Database helper for contacts. Designed as a singleton to make sure that all 69 * {@link android.content.ContentProvider} users get the same reference. 70 * Provides handy methods for maintaining package and mime-type lookup tables. 71 */ 72/* package */ class ContactsDatabaseHelper extends SQLiteOpenHelper { 73 private static final String TAG = "ContactsDatabaseHelper"; 74 75 private static final int DATABASE_VERSION = 303; 76 77 private static final String DATABASE_NAME = "contacts2.db"; 78 private static final String DATABASE_PRESENCE = "presence_db"; 79 80 public interface Tables { 81 public static final String CONTACTS = "contacts"; 82 public static final String RAW_CONTACTS = "raw_contacts"; 83 public static final String PACKAGES = "packages"; 84 public static final String MIMETYPES = "mimetypes"; 85 public static final String PHONE_LOOKUP = "phone_lookup"; 86 public static final String NAME_LOOKUP = "name_lookup"; 87 public static final String AGGREGATION_EXCEPTIONS = "agg_exceptions"; 88 public static final String SETTINGS = "settings"; 89 public static final String DATA = "data"; 90 public static final String GROUPS = "groups"; 91 public static final String PRESENCE = "presence"; 92 public static final String AGGREGATED_PRESENCE = "agg_presence"; 93 public static final String NICKNAME_LOOKUP = "nickname_lookup"; 94 public static final String CALLS = "calls"; 95 public static final String CONTACT_ENTITIES = "contact_entities_view"; 96 public static final String CONTACT_ENTITIES_RESTRICTED = "contact_entities_view_restricted"; 97 public static final String STATUS_UPDATES = "status_updates"; 98 99 public static final String DATA_JOIN_MIMETYPES = "data " 100 + "JOIN mimetypes ON (data.mimetype_id = mimetypes._id)"; 101 102 public static final String DATA_JOIN_RAW_CONTACTS = "data " 103 + "JOIN raw_contacts ON (data.raw_contact_id = raw_contacts._id)"; 104 105 public static final String DATA_JOIN_MIMETYPE_RAW_CONTACTS = "data " 106 + "JOIN mimetypes ON (data.mimetype_id = mimetypes._id) " 107 + "JOIN raw_contacts ON (data.raw_contact_id = raw_contacts._id)"; 108 109 // NOTE: This requires late binding of GroupMembership MIME-type 110 public static final String RAW_CONTACTS_JOIN_SETTINGS_DATA_GROUPS = "raw_contacts " 111 + "LEFT OUTER JOIN settings ON (" 112 + "raw_contacts.account_name = settings.account_name AND " 113 + "raw_contacts.account_type = settings.account_type) " 114 + "LEFT OUTER JOIN data ON (data.mimetype_id=? AND " 115 + "data.raw_contact_id = raw_contacts._id) " 116 + "LEFT OUTER JOIN groups ON (groups._id = data." + GroupMembership.GROUP_ROW_ID 117 + ")"; 118 119 // NOTE: This requires late binding of GroupMembership MIME-type 120 public static final String SETTINGS_JOIN_RAW_CONTACTS_DATA_MIMETYPES_CONTACTS = "settings " 121 + "LEFT OUTER JOIN raw_contacts ON (" 122 + "raw_contacts.account_name = settings.account_name AND " 123 + "raw_contacts.account_type = settings.account_type) " 124 + "LEFT OUTER JOIN data ON (data.mimetype_id=? AND " 125 + "data.raw_contact_id = raw_contacts._id) " 126 + "LEFT OUTER JOIN contacts ON (raw_contacts.contact_id = contacts._id)"; 127 128 public static final String DATA_JOIN_MIMETYPES_RAW_CONTACTS_CONTACTS = "data " 129 + "JOIN mimetypes ON (data.mimetype_id = mimetypes._id) " 130 + "JOIN raw_contacts ON (data.raw_contact_id = raw_contacts._id) " 131 + "LEFT OUTER JOIN contacts ON (raw_contacts.contact_id = contacts._id)"; 132 133 public static final String DATA_JOIN_PACKAGES_MIMETYPES_RAW_CONTACTS_GROUPS = "data " 134 + "JOIN mimetypes ON (data.mimetype_id = mimetypes._id) " 135 + "JOIN raw_contacts ON (data.raw_contact_id = raw_contacts._id) " 136 + "LEFT OUTER JOIN packages ON (data.package_id = packages._id) " 137 + "LEFT OUTER JOIN groups " 138 + " ON (mimetypes.mimetype='" + GroupMembership.CONTENT_ITEM_TYPE + "' " 139 + " AND groups._id = data." + GroupMembership.GROUP_ROW_ID + ") "; 140 141 public static final String GROUPS_JOIN_PACKAGES = "groups " 142 + "LEFT OUTER JOIN packages ON (groups.package_id = packages._id)"; 143 144 public static final String ACTIVITIES = "activities"; 145 146 public static final String ACTIVITIES_JOIN_MIMETYPES = "activities " 147 + "LEFT OUTER JOIN mimetypes ON (activities.mimetype_id = mimetypes._id)"; 148 149 public static final String ACTIVITIES_JOIN_PACKAGES_MIMETYPES_RAW_CONTACTS_CONTACTS = 150 "activities " 151 + "LEFT OUTER JOIN packages ON (activities.package_id = packages._id) " 152 + "LEFT OUTER JOIN mimetypes ON (activities.mimetype_id = mimetypes._id) " 153 + "LEFT OUTER JOIN raw_contacts ON (activities.author_contact_id = " + 154 "raw_contacts._id) " 155 + "LEFT OUTER JOIN contacts ON (raw_contacts.contact_id = contacts._id)"; 156 157 public static final String NAME_LOOKUP_JOIN_RAW_CONTACTS = "name_lookup " 158 + "INNER JOIN raw_contacts ON (name_lookup.raw_contact_id = raw_contacts._id)"; 159 } 160 161 public interface Views { 162 public static final String DATA_ALL = "view_data"; 163 public static final String DATA_RESTRICTED = "view_data_restricted"; 164 165 public static final String RAW_CONTACTS_ALL = "view_raw_contacts"; 166 public static final String RAW_CONTACTS_RESTRICTED = "view_raw_contacts_restricted"; 167 168 public static final String CONTACTS_ALL = "view_contacts"; 169 public static final String CONTACTS_RESTRICTED = "view_contacts_restricted"; 170 171 public static final String GROUPS_ALL = "view_groups"; 172 } 173 174 public interface Clauses { 175 final String MIMETYPE_IS_GROUP_MEMBERSHIP = MimetypesColumns.CONCRETE_MIMETYPE + "='" 176 + GroupMembership.CONTENT_ITEM_TYPE + "'"; 177 178 final String BELONGS_TO_GROUP = DataColumns.CONCRETE_GROUP_ID + "=" 179 + GroupsColumns.CONCRETE_ID; 180 181 final String HAVING_NO_GROUPS = "COUNT(" + DataColumns.CONCRETE_GROUP_ID + ") == 0"; 182 183 final String GROUP_BY_ACCOUNT_CONTACT_ID = SettingsColumns.CONCRETE_ACCOUNT_NAME + "," 184 + SettingsColumns.CONCRETE_ACCOUNT_TYPE + "," + RawContacts.CONTACT_ID; 185 186 final String RAW_CONTACT_IS_LOCAL = RawContactsColumns.CONCRETE_ACCOUNT_NAME 187 + " IS NULL AND " + RawContactsColumns.CONCRETE_ACCOUNT_TYPE + " IS NULL"; 188 189 final String ZERO_GROUP_MEMBERSHIPS = "COUNT(" + GroupsColumns.CONCRETE_ID + ")=0"; 190 191 final String OUTER_RAW_CONTACTS = "outer_raw_contacts"; 192 final String OUTER_RAW_CONTACTS_ID = OUTER_RAW_CONTACTS + "." + RawContacts._ID; 193 194 final String CONTACT_IS_VISIBLE = 195 "SELECT " + 196 "MAX((SELECT (CASE WHEN " + 197 "(CASE" + 198 " WHEN " + RAW_CONTACT_IS_LOCAL + 199 " THEN 1 " + 200 " WHEN " + ZERO_GROUP_MEMBERSHIPS + 201 " THEN " + Settings.UNGROUPED_VISIBLE + 202 " ELSE MAX(" + Groups.GROUP_VISIBLE + ")" + 203 "END)=1 THEN 1 ELSE 0 END)" + 204 " FROM " + Tables.RAW_CONTACTS_JOIN_SETTINGS_DATA_GROUPS + 205 " WHERE " + RawContactsColumns.CONCRETE_ID + "=" + OUTER_RAW_CONTACTS_ID + "))" + 206 " FROM " + Tables.RAW_CONTACTS + " AS " + OUTER_RAW_CONTACTS + 207 " WHERE " + RawContacts.CONTACT_ID + "=" + ContactsColumns.CONCRETE_ID + 208 " GROUP BY " + RawContacts.CONTACT_ID; 209 210 final String GROUP_HAS_ACCOUNT_AND_SOURCE_ID = Groups.SOURCE_ID + "=? AND " 211 + Groups.ACCOUNT_NAME + "=? AND " + Groups.ACCOUNT_TYPE + "=?"; 212 } 213 214 public interface ContactsColumns { 215 /** 216 * This flag is set for a contact if it has only one constituent raw contact and 217 * it is restricted. 218 */ 219 public static final String SINGLE_IS_RESTRICTED = "single_is_restricted"; 220 221 public static final String LAST_STATUS_UPDATE_ID = "status_update_id"; 222 223 public static final String CONCRETE_ID = Tables.CONTACTS + "." + BaseColumns._ID; 224 225 public static final String CONCRETE_TIMES_CONTACTED = Tables.CONTACTS + "." 226 + Contacts.TIMES_CONTACTED; 227 public static final String CONCRETE_LAST_TIME_CONTACTED = Tables.CONTACTS + "." 228 + Contacts.LAST_TIME_CONTACTED; 229 public static final String CONCRETE_STARRED = Tables.CONTACTS + "." + Contacts.STARRED; 230 public static final String CONCRETE_CUSTOM_RINGTONE = Tables.CONTACTS + "." 231 + Contacts.CUSTOM_RINGTONE; 232 public static final String CONCRETE_SEND_TO_VOICEMAIL = Tables.CONTACTS + "." 233 + Contacts.SEND_TO_VOICEMAIL; 234 public static final String CONCRETE_LOOKUP_KEY = Tables.CONTACTS + "." 235 + Contacts.LOOKUP_KEY; 236 } 237 238 public interface RawContactsColumns { 239 public static final String CONCRETE_ID = 240 Tables.RAW_CONTACTS + "." + BaseColumns._ID; 241 public static final String CONCRETE_ACCOUNT_NAME = 242 Tables.RAW_CONTACTS + "." + RawContacts.ACCOUNT_NAME; 243 public static final String CONCRETE_ACCOUNT_TYPE = 244 Tables.RAW_CONTACTS + "." + RawContacts.ACCOUNT_TYPE; 245 public static final String CONCRETE_SOURCE_ID = 246 Tables.RAW_CONTACTS + "." + RawContacts.SOURCE_ID; 247 public static final String CONCRETE_VERSION = 248 Tables.RAW_CONTACTS + "." + RawContacts.VERSION; 249 public static final String CONCRETE_DIRTY = 250 Tables.RAW_CONTACTS + "." + RawContacts.DIRTY; 251 public static final String CONCRETE_DELETED = 252 Tables.RAW_CONTACTS + "." + RawContacts.DELETED; 253 public static final String CONCRETE_SYNC1 = 254 Tables.RAW_CONTACTS + "." + RawContacts.SYNC1; 255 public static final String CONCRETE_SYNC2 = 256 Tables.RAW_CONTACTS + "." + RawContacts.SYNC2; 257 public static final String CONCRETE_SYNC3 = 258 Tables.RAW_CONTACTS + "." + RawContacts.SYNC3; 259 public static final String CONCRETE_SYNC4 = 260 Tables.RAW_CONTACTS + "." + RawContacts.SYNC4; 261 public static final String CONCRETE_STARRED = 262 Tables.RAW_CONTACTS + "." + RawContacts.STARRED; 263 public static final String CONCRETE_IS_RESTRICTED = 264 Tables.RAW_CONTACTS + "." + RawContacts.IS_RESTRICTED; 265 266 public static final String DISPLAY_NAME = RawContacts.DISPLAY_NAME_PRIMARY; 267 public static final String DISPLAY_NAME_SOURCE = RawContacts.DISPLAY_NAME_SOURCE; 268 public static final String AGGREGATION_NEEDED = "aggregation_needed"; 269 public static final String CONTACT_IN_VISIBLE_GROUP = "contact_in_visible_group"; 270 271 public static final String CONCRETE_DISPLAY_NAME = 272 Tables.RAW_CONTACTS + "." + DISPLAY_NAME; 273 public static final String CONCRETE_CONTACT_ID = 274 Tables.RAW_CONTACTS + "." + RawContacts.CONTACT_ID; 275 public static final String CONCRETE_NAME_VERIFIED = 276 Tables.RAW_CONTACTS + "." + RawContacts.NAME_VERIFIED; 277 } 278 279 public interface DataColumns { 280 public static final String PACKAGE_ID = "package_id"; 281 public static final String MIMETYPE_ID = "mimetype_id"; 282 283 public static final String CONCRETE_ID = Tables.DATA + "." + BaseColumns._ID; 284 public static final String CONCRETE_MIMETYPE_ID = Tables.DATA + "." + MIMETYPE_ID; 285 public static final String CONCRETE_RAW_CONTACT_ID = Tables.DATA + "." 286 + Data.RAW_CONTACT_ID; 287 public static final String CONCRETE_GROUP_ID = Tables.DATA + "." 288 + GroupMembership.GROUP_ROW_ID; 289 290 public static final String CONCRETE_DATA1 = Tables.DATA + "." + Data.DATA1; 291 public static final String CONCRETE_DATA2 = Tables.DATA + "." + Data.DATA2; 292 public static final String CONCRETE_DATA3 = Tables.DATA + "." + Data.DATA3; 293 public static final String CONCRETE_DATA4 = Tables.DATA + "." + Data.DATA4; 294 public static final String CONCRETE_DATA5 = Tables.DATA + "." + Data.DATA5; 295 public static final String CONCRETE_DATA6 = Tables.DATA + "." + Data.DATA6; 296 public static final String CONCRETE_DATA7 = Tables.DATA + "." + Data.DATA7; 297 public static final String CONCRETE_DATA8 = Tables.DATA + "." + Data.DATA8; 298 public static final String CONCRETE_DATA9 = Tables.DATA + "." + Data.DATA9; 299 public static final String CONCRETE_DATA10 = Tables.DATA + "." + Data.DATA10; 300 public static final String CONCRETE_DATA11 = Tables.DATA + "." + Data.DATA11; 301 public static final String CONCRETE_DATA12 = Tables.DATA + "." + Data.DATA12; 302 public static final String CONCRETE_DATA13 = Tables.DATA + "." + Data.DATA13; 303 public static final String CONCRETE_DATA14 = Tables.DATA + "." + Data.DATA14; 304 public static final String CONCRETE_DATA15 = Tables.DATA + "." + Data.DATA15; 305 public static final String CONCRETE_IS_PRIMARY = Tables.DATA + "." + Data.IS_PRIMARY; 306 public static final String CONCRETE_PACKAGE_ID = Tables.DATA + "." + PACKAGE_ID; 307 } 308 309 // Used only for legacy API support 310 public interface ExtensionsColumns { 311 public static final String NAME = Data.DATA1; 312 public static final String VALUE = Data.DATA2; 313 } 314 315 public interface GroupMembershipColumns { 316 public static final String RAW_CONTACT_ID = Data.RAW_CONTACT_ID; 317 public static final String GROUP_ROW_ID = GroupMembership.GROUP_ROW_ID; 318 } 319 320 public interface PhoneColumns { 321 public static final String NORMALIZED_NUMBER = Data.DATA4; 322 public static final String CONCRETE_NORMALIZED_NUMBER = DataColumns.CONCRETE_DATA4; 323 } 324 325 public interface GroupsColumns { 326 public static final String PACKAGE_ID = "package_id"; 327 328 public static final String CONCRETE_ID = Tables.GROUPS + "." + BaseColumns._ID; 329 public static final String CONCRETE_SOURCE_ID = Tables.GROUPS + "." + Groups.SOURCE_ID; 330 public static final String CONCRETE_ACCOUNT_NAME = Tables.GROUPS + "." + Groups.ACCOUNT_NAME; 331 public static final String CONCRETE_ACCOUNT_TYPE = Tables.GROUPS + "." + Groups.ACCOUNT_TYPE; 332 } 333 334 public interface ActivitiesColumns { 335 public static final String PACKAGE_ID = "package_id"; 336 public static final String MIMETYPE_ID = "mimetype_id"; 337 } 338 339 public interface PhoneLookupColumns { 340 public static final String _ID = BaseColumns._ID; 341 public static final String DATA_ID = "data_id"; 342 public static final String RAW_CONTACT_ID = "raw_contact_id"; 343 public static final String NORMALIZED_NUMBER = "normalized_number"; 344 public static final String MIN_MATCH = "min_match"; 345 } 346 347 public interface NameLookupColumns { 348 public static final String RAW_CONTACT_ID = "raw_contact_id"; 349 public static final String DATA_ID = "data_id"; 350 public static final String NORMALIZED_NAME = "normalized_name"; 351 public static final String NAME_TYPE = "name_type"; 352 } 353 354 public final static class NameLookupType { 355 public static final int NAME_EXACT = 0; 356 public static final int NAME_VARIANT = 1; 357 public static final int NAME_COLLATION_KEY = 2; 358 public static final int NICKNAME = 3; 359 public static final int EMAIL_BASED_NICKNAME = 4; 360 public static final int ORGANIZATION = 5; 361 public static final int NAME_SHORTHAND = 6; 362 363 // This is the highest name lookup type code plus one 364 public static final int TYPE_COUNT = 7; 365 366 public static boolean isBasedOnStructuredName(int nameLookupType) { 367 return nameLookupType == NameLookupType.NAME_EXACT 368 || nameLookupType == NameLookupType.NAME_VARIANT 369 || nameLookupType == NameLookupType.NAME_COLLATION_KEY; 370 } 371 } 372 373 public interface PackagesColumns { 374 public static final String _ID = BaseColumns._ID; 375 public static final String PACKAGE = "package"; 376 377 public static final String CONCRETE_ID = Tables.PACKAGES + "." + _ID; 378 } 379 380 public interface MimetypesColumns { 381 public static final String _ID = BaseColumns._ID; 382 public static final String MIMETYPE = "mimetype"; 383 384 public static final String CONCRETE_ID = Tables.MIMETYPES + "." + BaseColumns._ID; 385 public static final String CONCRETE_MIMETYPE = Tables.MIMETYPES + "." + MIMETYPE; 386 } 387 388 public interface AggregationExceptionColumns { 389 public static final String _ID = BaseColumns._ID; 390 } 391 392 public interface NicknameLookupColumns { 393 public static final String NAME = "name"; 394 public static final String CLUSTER = "cluster"; 395 } 396 397 public interface SettingsColumns { 398 public static final String CONCRETE_ACCOUNT_NAME = Tables.SETTINGS + "." 399 + Settings.ACCOUNT_NAME; 400 public static final String CONCRETE_ACCOUNT_TYPE = Tables.SETTINGS + "." 401 + Settings.ACCOUNT_TYPE; 402 } 403 404 public interface PresenceColumns { 405 String RAW_CONTACT_ID = "presence_raw_contact_id"; 406 String CONTACT_ID = "presence_contact_id"; 407 } 408 409 public interface AggregatedPresenceColumns { 410 String CONTACT_ID = "presence_contact_id"; 411 412 String CONCRETE_CONTACT_ID = Tables.AGGREGATED_PRESENCE + "." + CONTACT_ID; 413 } 414 415 public interface StatusUpdatesColumns { 416 String DATA_ID = "status_update_data_id"; 417 418 String CONCRETE_DATA_ID = Tables.STATUS_UPDATES + "." + DATA_ID; 419 420 String CONCRETE_PRESENCE = Tables.STATUS_UPDATES + "." + StatusUpdates.PRESENCE; 421 String CONCRETE_STATUS = Tables.STATUS_UPDATES + "." + StatusUpdates.STATUS; 422 String CONCRETE_STATUS_TIMESTAMP = Tables.STATUS_UPDATES + "." 423 + StatusUpdates.STATUS_TIMESTAMP; 424 String CONCRETE_STATUS_RES_PACKAGE = Tables.STATUS_UPDATES + "." 425 + StatusUpdates.STATUS_RES_PACKAGE; 426 String CONCRETE_STATUS_LABEL = Tables.STATUS_UPDATES + "." + StatusUpdates.STATUS_LABEL; 427 String CONCRETE_STATUS_ICON = Tables.STATUS_UPDATES + "." + StatusUpdates.STATUS_ICON; 428 } 429 430 public interface ContactsStatusUpdatesColumns { 431 String ALIAS = "contacts_" + Tables.STATUS_UPDATES; 432 433 String CONCRETE_DATA_ID = ALIAS + "." + StatusUpdatesColumns.DATA_ID; 434 435 String CONCRETE_PRESENCE = ALIAS + "." + StatusUpdates.PRESENCE; 436 String CONCRETE_STATUS = ALIAS + "." + StatusUpdates.STATUS; 437 String CONCRETE_STATUS_TIMESTAMP = ALIAS + "." + StatusUpdates.STATUS_TIMESTAMP; 438 String CONCRETE_STATUS_RES_PACKAGE = ALIAS + "." + StatusUpdates.STATUS_RES_PACKAGE; 439 String CONCRETE_STATUS_LABEL = ALIAS + "." + StatusUpdates.STATUS_LABEL; 440 String CONCRETE_STATUS_ICON = ALIAS + "." + StatusUpdates.STATUS_ICON; 441 } 442 443 /** In-memory cache of previously found MIME-type mappings */ 444 private final HashMap<String, Long> mMimetypeCache = new HashMap<String, Long>(); 445 /** In-memory cache of previously found package name mappings */ 446 private final HashMap<String, Long> mPackageCache = new HashMap<String, Long>(); 447 448 449 /** Compiled statements for querying and inserting mappings */ 450 private SQLiteStatement mMimetypeQuery; 451 private SQLiteStatement mPackageQuery; 452 private SQLiteStatement mContactIdQuery; 453 private SQLiteStatement mAggregationModeQuery; 454 private SQLiteStatement mMimetypeInsert; 455 private SQLiteStatement mPackageInsert; 456 private SQLiteStatement mDataMimetypeQuery; 457 private SQLiteStatement mActivitiesMimetypeQuery; 458 459 private final Context mContext; 460 private final SyncStateContentProviderHelper mSyncState; 461 462 463 /** Compiled statements for updating {@link Contacts#IN_VISIBLE_GROUP}. */ 464 private SQLiteStatement mVisibleSpecificUpdate; 465 private SQLiteStatement mVisibleUpdateRawContacts; 466 private SQLiteStatement mVisibleSpecificUpdateRawContacts; 467 468 private boolean mReopenDatabase = false; 469 470 private static ContactsDatabaseHelper sSingleton = null; 471 472 private boolean mUseStrictPhoneNumberComparison; 473 474 /** 475 * List of package names with access to {@link RawContacts#IS_RESTRICTED} data. 476 */ 477 private String[] mUnrestrictedPackages; 478 479 public static synchronized ContactsDatabaseHelper getInstance(Context context) { 480 if (sSingleton == null) { 481 sSingleton = new ContactsDatabaseHelper(context); 482 } 483 return sSingleton; 484 } 485 486 /** 487 * Private constructor, callers except unit tests should obtain an instance through 488 * {@link #getInstance(android.content.Context)} instead. 489 */ 490 ContactsDatabaseHelper(Context context) { 491 super(context, DATABASE_NAME, null, DATABASE_VERSION); 492 if (false) Log.i(TAG, "Creating OpenHelper"); 493 Resources resources = context.getResources(); 494 495 mContext = context; 496 mSyncState = new SyncStateContentProviderHelper(); 497 mUseStrictPhoneNumberComparison = 498 resources.getBoolean( 499 com.android.internal.R.bool.config_use_strict_phone_number_comparation); 500 int resourceId = resources.getIdentifier("unrestricted_packages", "array", 501 context.getPackageName()); 502 if (resourceId != 0) { 503 mUnrestrictedPackages = resources.getStringArray(resourceId); 504 } else { 505 mUnrestrictedPackages = new String[0]; 506 } 507 } 508 509 @Override 510 public void onOpen(SQLiteDatabase db) { 511 mSyncState.onDatabaseOpened(db); 512 513 // Create compiled statements for package and mimetype lookups 514 mMimetypeQuery = db.compileStatement("SELECT " + MimetypesColumns._ID + " FROM " 515 + Tables.MIMETYPES + " WHERE " + MimetypesColumns.MIMETYPE + "=?"); 516 mPackageQuery = db.compileStatement("SELECT " + PackagesColumns._ID + " FROM " 517 + Tables.PACKAGES + " WHERE " + PackagesColumns.PACKAGE + "=?"); 518 mContactIdQuery = db.compileStatement("SELECT " + RawContacts.CONTACT_ID + " FROM " 519 + Tables.RAW_CONTACTS + " WHERE " + RawContacts._ID + "=?"); 520 mAggregationModeQuery = db.compileStatement("SELECT " + RawContacts.AGGREGATION_MODE 521 + " FROM " + Tables.RAW_CONTACTS + " WHERE " + RawContacts._ID + "=?"); 522 mMimetypeInsert = db.compileStatement("INSERT INTO " + Tables.MIMETYPES + "(" 523 + MimetypesColumns.MIMETYPE + ") VALUES (?)"); 524 mPackageInsert = db.compileStatement("INSERT INTO " + Tables.PACKAGES + "(" 525 + PackagesColumns.PACKAGE + ") VALUES (?)"); 526 527 mDataMimetypeQuery = db.compileStatement("SELECT " + MimetypesColumns.MIMETYPE + " FROM " 528 + Tables.DATA_JOIN_MIMETYPES + " WHERE " + Tables.DATA + "." + Data._ID + "=?"); 529 mActivitiesMimetypeQuery = db.compileStatement("SELECT " + MimetypesColumns.MIMETYPE 530 + " FROM " + Tables.ACTIVITIES_JOIN_MIMETYPES + " WHERE " + Tables.ACTIVITIES + "." 531 + Activities._ID + "=?"); 532 533 // Change visibility of a specific contact 534 mVisibleSpecificUpdate = db.compileStatement( 535 "UPDATE " + Tables.CONTACTS + 536 " SET " + Contacts.IN_VISIBLE_GROUP + "=(" + Clauses.CONTACT_IS_VISIBLE + ")" + 537 " WHERE " + ContactsColumns.CONCRETE_ID + "=?"); 538 539 // Return visibility of the aggregate contact joined with the raw contact 540 String contactVisibility = 541 "SELECT " + Contacts.IN_VISIBLE_GROUP + 542 " FROM " + Tables.CONTACTS + 543 " WHERE " + Contacts._ID + "=" + RawContacts.CONTACT_ID; 544 545 // Set visibility of raw contacts to the visibility of corresponding aggregate contacts 546 mVisibleUpdateRawContacts = db.compileStatement( 547 "UPDATE " + Tables.RAW_CONTACTS + 548 " SET " + RawContactsColumns.CONTACT_IN_VISIBLE_GROUP + "=(" 549 + contactVisibility + ")" + 550 " WHERE " + RawContacts.DELETED + "=0" + 551 " AND " + RawContactsColumns.CONTACT_IN_VISIBLE_GROUP + "!=(" 552 + contactVisibility + ")=1"); 553 554 // Set visibility of a raw contact to the visibility of corresponding aggregate contact 555 mVisibleSpecificUpdateRawContacts = db.compileStatement( 556 "UPDATE " + Tables.RAW_CONTACTS + 557 " SET " + RawContactsColumns.CONTACT_IN_VISIBLE_GROUP + "=(" 558 + contactVisibility + ")" + 559 " WHERE " + RawContacts.DELETED + "=0 AND " + RawContacts.CONTACT_ID + "=?"); 560 561 db.execSQL("ATTACH DATABASE ':memory:' AS " + DATABASE_PRESENCE + ";"); 562 db.execSQL("CREATE TABLE IF NOT EXISTS " + DATABASE_PRESENCE + "." + Tables.PRESENCE + " ("+ 563 StatusUpdates.DATA_ID + " INTEGER PRIMARY KEY REFERENCES data(_id)," + 564 StatusUpdates.PROTOCOL + " INTEGER NOT NULL," + 565 StatusUpdates.CUSTOM_PROTOCOL + " TEXT," + 566 StatusUpdates.IM_HANDLE + " TEXT," + 567 StatusUpdates.IM_ACCOUNT + " TEXT," + 568 PresenceColumns.CONTACT_ID + " INTEGER REFERENCES contacts(_id)," + 569 PresenceColumns.RAW_CONTACT_ID + " INTEGER REFERENCES raw_contacts(_id)," + 570 StatusUpdates.PRESENCE + " INTEGER," + 571 "UNIQUE(" + StatusUpdates.PROTOCOL + ", " + StatusUpdates.CUSTOM_PROTOCOL 572 + ", " + StatusUpdates.IM_HANDLE + ", " + StatusUpdates.IM_ACCOUNT + ")" + 573 ");"); 574 575 db.execSQL("CREATE INDEX IF NOT EXISTS " + DATABASE_PRESENCE + ".presenceIndex" + " ON " 576 + Tables.PRESENCE + " (" + PresenceColumns.RAW_CONTACT_ID + ");"); 577 578 db.execSQL("CREATE TABLE IF NOT EXISTS " 579 + DATABASE_PRESENCE + "." + Tables.AGGREGATED_PRESENCE + " ("+ 580 AggregatedPresenceColumns.CONTACT_ID 581 + " INTEGER PRIMARY KEY REFERENCES contacts(_id)," + 582 StatusUpdates.PRESENCE_STATUS + " INTEGER" + 583 ");"); 584 585 586 db.execSQL("CREATE TRIGGER " + DATABASE_PRESENCE + "." + Tables.PRESENCE + "_deleted" 587 + " BEFORE DELETE ON " + DATABASE_PRESENCE + "." + Tables.PRESENCE 588 + " BEGIN " 589 + " DELETE FROM " + Tables.AGGREGATED_PRESENCE 590 + " WHERE " + AggregatedPresenceColumns.CONTACT_ID + " = " + 591 "(SELECT " + PresenceColumns.CONTACT_ID + 592 " FROM " + Tables.PRESENCE + 593 " WHERE " + PresenceColumns.RAW_CONTACT_ID 594 + "=OLD." + PresenceColumns.RAW_CONTACT_ID + 595 " AND NOT EXISTS" + 596 "(SELECT " + PresenceColumns.RAW_CONTACT_ID + 597 " FROM " + Tables.PRESENCE + 598 " WHERE " + PresenceColumns.CONTACT_ID 599 + "=OLD." + PresenceColumns.CONTACT_ID + 600 " AND " + PresenceColumns.RAW_CONTACT_ID 601 + "!=OLD." + PresenceColumns.RAW_CONTACT_ID + "));" 602 + " END"); 603 604 String replaceAggregatePresenceSql = 605 "INSERT OR REPLACE INTO " + Tables.AGGREGATED_PRESENCE + "(" 606 + AggregatedPresenceColumns.CONTACT_ID + ", " 607 + StatusUpdates.PRESENCE_STATUS + ")" + 608 " SELECT " + PresenceColumns.CONTACT_ID + "," 609 + "MAX(" + StatusUpdates.PRESENCE_STATUS + ")" + 610 " FROM " + Tables.PRESENCE + 611 " WHERE " + PresenceColumns.CONTACT_ID 612 + "=NEW." + PresenceColumns.CONTACT_ID + ";"; 613 614 db.execSQL("CREATE TRIGGER " + DATABASE_PRESENCE + "." + Tables.PRESENCE + "_inserted" 615 + " AFTER INSERT ON " + DATABASE_PRESENCE + "." + Tables.PRESENCE 616 + " BEGIN " 617 + replaceAggregatePresenceSql 618 + " END"); 619 620 db.execSQL("CREATE TRIGGER " + DATABASE_PRESENCE + "." + Tables.PRESENCE + "_updated" 621 + " AFTER UPDATE ON " + DATABASE_PRESENCE + "." + Tables.PRESENCE 622 + " BEGIN " 623 + replaceAggregatePresenceSql 624 + " END"); 625 } 626 627 @Override 628 public void onCreate(SQLiteDatabase db) { 629 Log.i(TAG, "Bootstrapping database"); 630 631 mSyncState.createDatabase(db); 632 633 // One row per group of contacts corresponding to the same person 634 db.execSQL("CREATE TABLE " + Tables.CONTACTS + " (" + 635 BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 636 Contacts.NAME_RAW_CONTACT_ID + " INTEGER REFERENCES raw_contacts(_id)," + 637 Contacts.PHOTO_ID + " INTEGER REFERENCES data(_id)," + 638 Contacts.CUSTOM_RINGTONE + " TEXT," + 639 Contacts.SEND_TO_VOICEMAIL + " INTEGER NOT NULL DEFAULT 0," + 640 Contacts.TIMES_CONTACTED + " INTEGER NOT NULL DEFAULT 0," + 641 Contacts.LAST_TIME_CONTACTED + " INTEGER," + 642 Contacts.STARRED + " INTEGER NOT NULL DEFAULT 0," + 643 Contacts.IN_VISIBLE_GROUP + " INTEGER NOT NULL DEFAULT 1," + 644 Contacts.HAS_PHONE_NUMBER + " INTEGER NOT NULL DEFAULT 0," + 645 Contacts.LOOKUP_KEY + " TEXT," + 646 ContactsColumns.LAST_STATUS_UPDATE_ID + " INTEGER REFERENCES data(_id)," + 647 ContactsColumns.SINGLE_IS_RESTRICTED + " INTEGER NOT NULL DEFAULT 0" + 648 ");"); 649 650 db.execSQL("CREATE INDEX contacts_visible_index ON " + Tables.CONTACTS + " (" + 651 Contacts.IN_VISIBLE_GROUP + 652 ");"); 653 654 db.execSQL("CREATE INDEX contacts_has_phone_index ON " + Tables.CONTACTS + " (" + 655 Contacts.HAS_PHONE_NUMBER + 656 ");"); 657 658 db.execSQL("CREATE INDEX contacts_restricted_index ON " + Tables.CONTACTS + " (" + 659 ContactsColumns.SINGLE_IS_RESTRICTED + 660 ");"); 661 662 db.execSQL("CREATE INDEX contacts_name_raw_contact_id_index ON " + Tables.CONTACTS + " (" + 663 Contacts.NAME_RAW_CONTACT_ID + 664 ");"); 665 666 // Contacts table 667 db.execSQL("CREATE TABLE " + Tables.RAW_CONTACTS + " (" + 668 RawContacts._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 669 RawContacts.IS_RESTRICTED + " INTEGER DEFAULT 0," + 670 RawContacts.ACCOUNT_NAME + " STRING DEFAULT NULL, " + 671 RawContacts.ACCOUNT_TYPE + " STRING DEFAULT NULL, " + 672 RawContacts.SOURCE_ID + " TEXT," + 673 RawContacts.VERSION + " INTEGER NOT NULL DEFAULT 1," + 674 RawContacts.DIRTY + " INTEGER NOT NULL DEFAULT 0," + 675 RawContacts.DELETED + " INTEGER NOT NULL DEFAULT 0," + 676 RawContacts.CONTACT_ID + " INTEGER REFERENCES contacts(_id)," + 677 RawContacts.AGGREGATION_MODE + " INTEGER NOT NULL DEFAULT " + 678 RawContacts.AGGREGATION_MODE_DEFAULT + "," + 679 RawContactsColumns.AGGREGATION_NEEDED + " INTEGER NOT NULL DEFAULT 1," + 680 RawContacts.CUSTOM_RINGTONE + " TEXT," + 681 RawContacts.SEND_TO_VOICEMAIL + " INTEGER NOT NULL DEFAULT 0," + 682 RawContacts.TIMES_CONTACTED + " INTEGER NOT NULL DEFAULT 0," + 683 RawContacts.LAST_TIME_CONTACTED + " INTEGER," + 684 RawContacts.STARRED + " INTEGER NOT NULL DEFAULT 0," + 685 RawContacts.DISPLAY_NAME_PRIMARY + " TEXT," + 686 RawContacts.DISPLAY_NAME_ALTERNATIVE + " TEXT," + 687 RawContacts.DISPLAY_NAME_SOURCE + " INTEGER NOT NULL DEFAULT " + 688 DisplayNameSources.UNDEFINED + "," + 689 RawContacts.PHONETIC_NAME + " TEXT," + 690 RawContacts.PHONETIC_NAME_STYLE + " TEXT," + 691 RawContacts.SORT_KEY_PRIMARY + " TEXT COLLATE LOCALIZED," + 692 RawContacts.SORT_KEY_ALTERNATIVE + " TEXT COLLATE LOCALIZED," + 693 RawContacts.NAME_VERIFIED + " INTEGER NOT NULL DEFAULT 0," + 694 RawContactsColumns.CONTACT_IN_VISIBLE_GROUP + " INTEGER NOT NULL DEFAULT 0," + 695 RawContacts.SYNC1 + " TEXT, " + 696 RawContacts.SYNC2 + " TEXT, " + 697 RawContacts.SYNC3 + " TEXT, " + 698 RawContacts.SYNC4 + " TEXT " + 699 ");"); 700 701 db.execSQL("CREATE INDEX raw_contacts_contact_id_index ON " + Tables.RAW_CONTACTS + " (" + 702 RawContacts.CONTACT_ID + 703 ");"); 704 705 db.execSQL("CREATE INDEX raw_contacts_source_id_index ON " + Tables.RAW_CONTACTS + " (" + 706 RawContacts.SOURCE_ID + ", " + 707 RawContacts.ACCOUNT_TYPE + ", " + 708 RawContacts.ACCOUNT_NAME + 709 ");"); 710 711 // TODO readd the index and investigate a controlled use of it 712// db.execSQL("CREATE INDEX raw_contacts_agg_index ON " + Tables.RAW_CONTACTS + " (" + 713// RawContactsColumns.AGGREGATION_NEEDED + 714// ");"); 715 716 // Package name mapping table 717 db.execSQL("CREATE TABLE " + Tables.PACKAGES + " (" + 718 PackagesColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 719 PackagesColumns.PACKAGE + " TEXT NOT NULL" + 720 ");"); 721 722 // Mimetype mapping table 723 db.execSQL("CREATE TABLE " + Tables.MIMETYPES + " (" + 724 MimetypesColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 725 MimetypesColumns.MIMETYPE + " TEXT NOT NULL" + 726 ");"); 727 728 // Public generic data table 729 db.execSQL("CREATE TABLE " + Tables.DATA + " (" + 730 Data._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 731 DataColumns.PACKAGE_ID + " INTEGER REFERENCES package(_id)," + 732 DataColumns.MIMETYPE_ID + " INTEGER REFERENCES mimetype(_id) NOT NULL," + 733 Data.RAW_CONTACT_ID + " INTEGER REFERENCES raw_contacts(_id) NOT NULL," + 734 Data.IS_PRIMARY + " INTEGER NOT NULL DEFAULT 0," + 735 Data.IS_SUPER_PRIMARY + " INTEGER NOT NULL DEFAULT 0," + 736 Data.DATA_VERSION + " INTEGER NOT NULL DEFAULT 0," + 737 Data.DATA1 + " TEXT," + 738 Data.DATA2 + " TEXT," + 739 Data.DATA3 + " TEXT," + 740 Data.DATA4 + " TEXT," + 741 Data.DATA5 + " TEXT," + 742 Data.DATA6 + " TEXT," + 743 Data.DATA7 + " TEXT," + 744 Data.DATA8 + " TEXT," + 745 Data.DATA9 + " TEXT," + 746 Data.DATA10 + " TEXT," + 747 Data.DATA11 + " TEXT," + 748 Data.DATA12 + " TEXT," + 749 Data.DATA13 + " TEXT," + 750 Data.DATA14 + " TEXT," + 751 Data.DATA15 + " TEXT," + 752 Data.SYNC1 + " TEXT, " + 753 Data.SYNC2 + " TEXT, " + 754 Data.SYNC3 + " TEXT, " + 755 Data.SYNC4 + " TEXT " + 756 ");"); 757 758 db.execSQL("CREATE INDEX data_raw_contact_id ON " + Tables.DATA + " (" + 759 Data.RAW_CONTACT_ID + 760 ");"); 761 762 /** 763 * For email lookup and similar queries. 764 */ 765 db.execSQL("CREATE INDEX data_mimetype_data1_index ON " + Tables.DATA + " (" + 766 DataColumns.MIMETYPE_ID + "," + 767 Data.DATA1 + 768 ");"); 769 770 // Private phone numbers table used for lookup 771 db.execSQL("CREATE TABLE " + Tables.PHONE_LOOKUP + " (" + 772 PhoneLookupColumns.DATA_ID 773 + " INTEGER PRIMARY KEY REFERENCES data(_id) NOT NULL," + 774 PhoneLookupColumns.RAW_CONTACT_ID 775 + " INTEGER REFERENCES raw_contacts(_id) NOT NULL," + 776 PhoneLookupColumns.NORMALIZED_NUMBER + " TEXT NOT NULL," + 777 PhoneLookupColumns.MIN_MATCH + " TEXT NOT NULL" + 778 ");"); 779 780 db.execSQL("CREATE INDEX phone_lookup_index ON " + Tables.PHONE_LOOKUP + " (" + 781 PhoneLookupColumns.NORMALIZED_NUMBER + "," + 782 PhoneLookupColumns.RAW_CONTACT_ID + "," + 783 PhoneLookupColumns.DATA_ID + 784 ");"); 785 786 db.execSQL("CREATE INDEX phone_lookup_min_match_index ON " + Tables.PHONE_LOOKUP + " (" + 787 PhoneLookupColumns.MIN_MATCH + "," + 788 PhoneLookupColumns.RAW_CONTACT_ID + "," + 789 PhoneLookupColumns.DATA_ID + 790 ");"); 791 792 // Private name/nickname table used for lookup 793 db.execSQL("CREATE TABLE " + Tables.NAME_LOOKUP + " (" + 794 NameLookupColumns.DATA_ID 795 + " INTEGER REFERENCES data(_id) NOT NULL," + 796 NameLookupColumns.RAW_CONTACT_ID 797 + " INTEGER REFERENCES raw_contacts(_id) NOT NULL," + 798 NameLookupColumns.NORMALIZED_NAME + " TEXT NOT NULL," + 799 NameLookupColumns.NAME_TYPE + " INTEGER NOT NULL," + 800 "PRIMARY KEY (" 801 + NameLookupColumns.DATA_ID + ", " 802 + NameLookupColumns.NORMALIZED_NAME + ", " 803 + NameLookupColumns.NAME_TYPE + ")" + 804 ");"); 805 806 db.execSQL("CREATE INDEX name_lookup_raw_contact_id_index ON " + Tables.NAME_LOOKUP + " (" + 807 NameLookupColumns.RAW_CONTACT_ID + 808 ");"); 809 810 db.execSQL("CREATE TABLE " + Tables.NICKNAME_LOOKUP + " (" + 811 NicknameLookupColumns.NAME + " TEXT," + 812 NicknameLookupColumns.CLUSTER + " TEXT" + 813 ");"); 814 815 db.execSQL("CREATE UNIQUE INDEX nickname_lookup_index ON " + Tables.NICKNAME_LOOKUP + " (" + 816 NicknameLookupColumns.NAME + ", " + 817 NicknameLookupColumns.CLUSTER + 818 ");"); 819 820 // Groups table 821 db.execSQL("CREATE TABLE " + Tables.GROUPS + " (" + 822 Groups._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 823 GroupsColumns.PACKAGE_ID + " INTEGER REFERENCES package(_id)," + 824 Groups.ACCOUNT_NAME + " STRING DEFAULT NULL, " + 825 Groups.ACCOUNT_TYPE + " STRING DEFAULT NULL, " + 826 Groups.SOURCE_ID + " TEXT," + 827 Groups.VERSION + " INTEGER NOT NULL DEFAULT 1," + 828 Groups.DIRTY + " INTEGER NOT NULL DEFAULT 0," + 829 Groups.TITLE + " TEXT," + 830 Groups.TITLE_RES + " INTEGER," + 831 Groups.NOTES + " TEXT," + 832 Groups.SYSTEM_ID + " TEXT," + 833 Groups.DELETED + " INTEGER NOT NULL DEFAULT 0," + 834 Groups.GROUP_VISIBLE + " INTEGER NOT NULL DEFAULT 0," + 835 Groups.SHOULD_SYNC + " INTEGER NOT NULL DEFAULT 1," + 836 Groups.SYNC1 + " TEXT, " + 837 Groups.SYNC2 + " TEXT, " + 838 Groups.SYNC3 + " TEXT, " + 839 Groups.SYNC4 + " TEXT " + 840 ");"); 841 842 db.execSQL("CREATE INDEX groups_source_id_index ON " + Tables.GROUPS + " (" + 843 Groups.SOURCE_ID + ", " + 844 Groups.ACCOUNT_TYPE + ", " + 845 Groups.ACCOUNT_NAME + 846 ");"); 847 848 db.execSQL("CREATE TABLE IF NOT EXISTS " + Tables.AGGREGATION_EXCEPTIONS + " (" + 849 AggregationExceptionColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 850 AggregationExceptions.TYPE + " INTEGER NOT NULL, " + 851 AggregationExceptions.RAW_CONTACT_ID1 852 + " INTEGER REFERENCES raw_contacts(_id), " + 853 AggregationExceptions.RAW_CONTACT_ID2 854 + " INTEGER REFERENCES raw_contacts(_id)" + 855 ");"); 856 857 db.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS aggregation_exception_index1 ON " + 858 Tables.AGGREGATION_EXCEPTIONS + " (" + 859 AggregationExceptions.RAW_CONTACT_ID1 + ", " + 860 AggregationExceptions.RAW_CONTACT_ID2 + 861 ");"); 862 863 db.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS aggregation_exception_index2 ON " + 864 Tables.AGGREGATION_EXCEPTIONS + " (" + 865 AggregationExceptions.RAW_CONTACT_ID2 + ", " + 866 AggregationExceptions.RAW_CONTACT_ID1 + 867 ");"); 868 869 db.execSQL("CREATE TABLE IF NOT EXISTS " + Tables.SETTINGS + " (" + 870 Settings.ACCOUNT_NAME + " STRING NOT NULL," + 871 Settings.ACCOUNT_TYPE + " STRING NOT NULL," + 872 Settings.UNGROUPED_VISIBLE + " INTEGER NOT NULL DEFAULT 0," + 873 Settings.SHOULD_SYNC + " INTEGER NOT NULL DEFAULT 1, " + 874 "PRIMARY KEY (" + Settings.ACCOUNT_NAME + ", " + 875 Settings.ACCOUNT_TYPE + ") ON CONFLICT REPLACE" + 876 ");"); 877 878 // The table for recent calls is here so we can do table joins 879 // on people, phones, and calls all in one place. 880 db.execSQL("CREATE TABLE " + Tables.CALLS + " (" + 881 Calls._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 882 Calls.NUMBER + " TEXT," + 883 Calls.DATE + " INTEGER," + 884 Calls.DURATION + " INTEGER," + 885 Calls.TYPE + " INTEGER," + 886 Calls.NEW + " INTEGER," + 887 Calls.CACHED_NAME + " TEXT," + 888 Calls.CACHED_NUMBER_TYPE + " INTEGER," + 889 Calls.CACHED_NUMBER_LABEL + " TEXT" + 890 ");"); 891 892 // Activities table 893 db.execSQL("CREATE TABLE " + Tables.ACTIVITIES + " (" + 894 Activities._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 895 ActivitiesColumns.PACKAGE_ID + " INTEGER REFERENCES package(_id)," + 896 ActivitiesColumns.MIMETYPE_ID + " INTEGER REFERENCES mimetype(_id) NOT NULL," + 897 Activities.RAW_ID + " TEXT," + 898 Activities.IN_REPLY_TO + " TEXT," + 899 Activities.AUTHOR_CONTACT_ID + " INTEGER REFERENCES raw_contacts(_id)," + 900 Activities.TARGET_CONTACT_ID + " INTEGER REFERENCES raw_contacts(_id)," + 901 Activities.PUBLISHED + " INTEGER NOT NULL," + 902 Activities.THREAD_PUBLISHED + " INTEGER NOT NULL," + 903 Activities.TITLE + " TEXT NOT NULL," + 904 Activities.SUMMARY + " TEXT," + 905 Activities.LINK + " TEXT, " + 906 Activities.THUMBNAIL + " BLOB" + 907 ");"); 908 909 db.execSQL("CREATE TABLE " + Tables.STATUS_UPDATES + " (" + 910 StatusUpdatesColumns.DATA_ID + " INTEGER PRIMARY KEY REFERENCES data(_id)," + 911 StatusUpdates.STATUS + " TEXT," + 912 StatusUpdates.STATUS_TIMESTAMP + " INTEGER," + 913 StatusUpdates.STATUS_RES_PACKAGE + " TEXT, " + 914 StatusUpdates.STATUS_LABEL + " INTEGER, " + 915 StatusUpdates.STATUS_ICON + " INTEGER" + 916 ");"); 917 918 createContactsViews(db); 919 createGroupsView(db); 920 createContactEntitiesView(db); 921 createContactsTriggers(db); 922 createContactsIndexes(db); 923 924 loadNicknameLookupTable(db); 925 926 // Add the legacy API support views, etc 927 LegacyApiSupport.createDatabase(db); 928 929 // This will create a sqlite_stat1 table that is used for query optimization 930 db.execSQL("ANALYZE;"); 931 932 updateSqliteStats(db); 933 934 // We need to close and reopen the database connection so that the stats are 935 // taken into account. Make a note of it and do the actual reopening in the 936 // getWritableDatabase method. 937 mReopenDatabase = true; 938 939 ContentResolver.requestSync(null /* all accounts */, 940 ContactsContract.AUTHORITY, new Bundle()); 941 } 942 943 private static void createContactsTriggers(SQLiteDatabase db) { 944 945 /* 946 * Automatically delete Data rows when a raw contact is deleted. 947 */ 948 db.execSQL("DROP TRIGGER IF EXISTS " + Tables.RAW_CONTACTS + "_deleted;"); 949 db.execSQL("CREATE TRIGGER " + Tables.RAW_CONTACTS + "_deleted " 950 + " BEFORE DELETE ON " + Tables.RAW_CONTACTS 951 + " BEGIN " 952 + " DELETE FROM " + Tables.DATA 953 + " WHERE " + Data.RAW_CONTACT_ID 954 + "=OLD." + RawContacts._ID + ";" 955 + " DELETE FROM " + Tables.AGGREGATION_EXCEPTIONS 956 + " WHERE " + AggregationExceptions.RAW_CONTACT_ID1 957 + "=OLD." + RawContacts._ID 958 + " OR " + AggregationExceptions.RAW_CONTACT_ID2 959 + "=OLD." + RawContacts._ID + ";" 960 + " DELETE FROM " + Tables.CONTACTS 961 + " WHERE " + Contacts._ID + "=OLD." + RawContacts.CONTACT_ID 962 + " AND (SELECT COUNT(*) FROM " + Tables.RAW_CONTACTS 963 + " WHERE " + RawContacts.CONTACT_ID + "=OLD." + RawContacts.CONTACT_ID 964 + " )=1;" 965 + " END"); 966 967 968 db.execSQL("DROP TRIGGER IF EXISTS contacts_times_contacted;"); 969 db.execSQL("DROP TRIGGER IF EXISTS raw_contacts_times_contacted;"); 970 971 /* 972 * Triggers that update {@link RawContacts#VERSION} when the contact is 973 * marked for deletion or any time a data row is inserted, updated or 974 * deleted. 975 */ 976 db.execSQL("DROP TRIGGER IF EXISTS " + Tables.RAW_CONTACTS + "_marked_deleted;"); 977 db.execSQL("CREATE TRIGGER " + Tables.RAW_CONTACTS + "_marked_deleted " 978 + " AFTER UPDATE ON " + Tables.RAW_CONTACTS 979 + " BEGIN " 980 + " UPDATE " + Tables.RAW_CONTACTS 981 + " SET " 982 + RawContacts.VERSION + "=OLD." + RawContacts.VERSION + "+1 " 983 + " WHERE " + RawContacts._ID + "=OLD." + RawContacts._ID 984 + " AND NEW." + RawContacts.DELETED + "!= OLD." + RawContacts.DELETED + ";" 985 + " END"); 986 987 db.execSQL("DROP TRIGGER IF EXISTS " + Tables.DATA + "_updated;"); 988 db.execSQL("CREATE TRIGGER " + Tables.DATA + "_updated AFTER UPDATE ON " + Tables.DATA 989 + " BEGIN " 990 + " UPDATE " + Tables.DATA 991 + " SET " + Data.DATA_VERSION + "=OLD." + Data.DATA_VERSION + "+1 " 992 + " WHERE " + Data._ID + "=OLD." + Data._ID + ";" 993 + " UPDATE " + Tables.RAW_CONTACTS 994 + " SET " + RawContacts.VERSION + "=" + RawContacts.VERSION + "+1 " 995 + " WHERE " + RawContacts._ID + "=OLD." + Data.RAW_CONTACT_ID + ";" 996 + " END"); 997 998 db.execSQL("DROP TRIGGER IF EXISTS " + Tables.DATA + "_deleted;"); 999 db.execSQL("CREATE TRIGGER " + Tables.DATA + "_deleted BEFORE DELETE ON " + Tables.DATA 1000 + " BEGIN " 1001 + " UPDATE " + Tables.RAW_CONTACTS 1002 + " SET " + RawContacts.VERSION + "=" + RawContacts.VERSION + "+1 " 1003 + " WHERE " + RawContacts._ID + "=OLD." + Data.RAW_CONTACT_ID + ";" 1004 + " DELETE FROM " + Tables.PHONE_LOOKUP 1005 + " WHERE " + PhoneLookupColumns.DATA_ID + "=OLD." + Data._ID + ";" 1006 + " DELETE FROM " + Tables.STATUS_UPDATES 1007 + " WHERE " + StatusUpdatesColumns.DATA_ID + "=OLD." + Data._ID + ";" 1008 + " DELETE FROM " + Tables.NAME_LOOKUP 1009 + " WHERE " + NameLookupColumns.DATA_ID + "=OLD." + Data._ID + ";" 1010 + " END"); 1011 1012 1013 db.execSQL("DROP TRIGGER IF EXISTS " + Tables.GROUPS + "_updated1;"); 1014 db.execSQL("CREATE TRIGGER " + Tables.GROUPS + "_updated1 " 1015 + " AFTER UPDATE ON " + Tables.GROUPS 1016 + " BEGIN " 1017 + " UPDATE " + Tables.GROUPS 1018 + " SET " 1019 + Groups.VERSION + "=OLD." + Groups.VERSION + "+1" 1020 + " WHERE " + Groups._ID + "=OLD." + Groups._ID + ";" 1021 + " END"); 1022 } 1023 1024 private static void createContactsIndexes(SQLiteDatabase db) { 1025 db.execSQL("DROP INDEX IF EXISTS name_lookup_index"); 1026 db.execSQL("CREATE INDEX name_lookup_index ON " + Tables.NAME_LOOKUP + " (" + 1027 NameLookupColumns.NORMALIZED_NAME + "," + 1028 NameLookupColumns.NAME_TYPE + ", " + 1029 NameLookupColumns.RAW_CONTACT_ID + ", " + 1030 NameLookupColumns.DATA_ID + 1031 ");"); 1032 1033 db.execSQL("DROP INDEX IF EXISTS raw_contact_sort_key1_index"); 1034 db.execSQL("CREATE INDEX raw_contact_sort_key1_index ON " + Tables.RAW_CONTACTS + " (" + 1035 RawContactsColumns.CONTACT_IN_VISIBLE_GROUP + "," + 1036 RawContacts.SORT_KEY_PRIMARY + 1037 ");"); 1038 1039 db.execSQL("DROP INDEX IF EXISTS raw_contact_sort_key2_index"); 1040 db.execSQL("CREATE INDEX raw_contact_sort_key2_index ON " + Tables.RAW_CONTACTS + " (" + 1041 RawContactsColumns.CONTACT_IN_VISIBLE_GROUP + "," + 1042 RawContacts.SORT_KEY_ALTERNATIVE + 1043 ");"); 1044 } 1045 1046 private static void createContactsViews(SQLiteDatabase db) { 1047 db.execSQL("DROP VIEW IF EXISTS " + Views.CONTACTS_ALL + ";"); 1048 db.execSQL("DROP VIEW IF EXISTS " + Views.CONTACTS_RESTRICTED + ";"); 1049 db.execSQL("DROP VIEW IF EXISTS " + Views.DATA_ALL + ";"); 1050 db.execSQL("DROP VIEW IF EXISTS " + Views.DATA_RESTRICTED + ";"); 1051 db.execSQL("DROP VIEW IF EXISTS " + Views.RAW_CONTACTS_ALL + ";"); 1052 db.execSQL("DROP VIEW IF EXISTS " + Views.RAW_CONTACTS_RESTRICTED + ";"); 1053 1054 String dataColumns = 1055 Data.IS_PRIMARY + ", " 1056 + Data.IS_SUPER_PRIMARY + ", " 1057 + Data.DATA_VERSION + ", " 1058 + PackagesColumns.PACKAGE + " AS " + Data.RES_PACKAGE + "," 1059 + MimetypesColumns.MIMETYPE + " AS " + Data.MIMETYPE + ", " 1060 + Data.DATA1 + ", " 1061 + Data.DATA2 + ", " 1062 + Data.DATA3 + ", " 1063 + Data.DATA4 + ", " 1064 + Data.DATA5 + ", " 1065 + Data.DATA6 + ", " 1066 + Data.DATA7 + ", " 1067 + Data.DATA8 + ", " 1068 + Data.DATA9 + ", " 1069 + Data.DATA10 + ", " 1070 + Data.DATA11 + ", " 1071 + Data.DATA12 + ", " 1072 + Data.DATA13 + ", " 1073 + Data.DATA14 + ", " 1074 + Data.DATA15 + ", " 1075 + Data.SYNC1 + ", " 1076 + Data.SYNC2 + ", " 1077 + Data.SYNC3 + ", " 1078 + Data.SYNC4; 1079 1080 String syncColumns = 1081 RawContactsColumns.CONCRETE_ACCOUNT_NAME + " AS " + RawContacts.ACCOUNT_NAME + "," 1082 + RawContactsColumns.CONCRETE_ACCOUNT_TYPE + " AS " + RawContacts.ACCOUNT_TYPE + "," 1083 + RawContactsColumns.CONCRETE_SOURCE_ID + " AS " + RawContacts.SOURCE_ID + "," 1084 + RawContactsColumns.CONCRETE_NAME_VERIFIED + " AS " + RawContacts.NAME_VERIFIED + "," 1085 + RawContactsColumns.CONCRETE_VERSION + " AS " + RawContacts.VERSION + "," 1086 + RawContactsColumns.CONCRETE_DIRTY + " AS " + RawContacts.DIRTY + "," 1087 + RawContactsColumns.CONCRETE_SYNC1 + " AS " + RawContacts.SYNC1 + "," 1088 + RawContactsColumns.CONCRETE_SYNC2 + " AS " + RawContacts.SYNC2 + "," 1089 + RawContactsColumns.CONCRETE_SYNC3 + " AS " + RawContacts.SYNC3 + "," 1090 + RawContactsColumns.CONCRETE_SYNC4 + " AS " + RawContacts.SYNC4; 1091 1092 String contactOptionColumns = 1093 ContactsColumns.CONCRETE_CUSTOM_RINGTONE 1094 + " AS " + RawContacts.CUSTOM_RINGTONE + "," 1095 + ContactsColumns.CONCRETE_SEND_TO_VOICEMAIL 1096 + " AS " + RawContacts.SEND_TO_VOICEMAIL + "," 1097 + ContactsColumns.CONCRETE_LAST_TIME_CONTACTED 1098 + " AS " + RawContacts.LAST_TIME_CONTACTED + "," 1099 + ContactsColumns.CONCRETE_TIMES_CONTACTED 1100 + " AS " + RawContacts.TIMES_CONTACTED + "," 1101 + ContactsColumns.CONCRETE_STARRED 1102 + " AS " + RawContacts.STARRED; 1103 1104 String contactNameColumns = 1105 "name_raw_contact." + RawContacts.DISPLAY_NAME_SOURCE 1106 + " AS " + Contacts.DISPLAY_NAME_SOURCE + ", " 1107 + "name_raw_contact." + RawContacts.DISPLAY_NAME_PRIMARY 1108 + " AS " + Contacts.DISPLAY_NAME_PRIMARY + ", " 1109 + "name_raw_contact." + RawContacts.DISPLAY_NAME_ALTERNATIVE 1110 + " AS " + Contacts.DISPLAY_NAME_ALTERNATIVE + ", " 1111 + "name_raw_contact." + RawContacts.PHONETIC_NAME 1112 + " AS " + Contacts.PHONETIC_NAME + ", " 1113 + "name_raw_contact." + RawContacts.PHONETIC_NAME_STYLE 1114 + " AS " + Contacts.PHONETIC_NAME_STYLE + ", " 1115 + "name_raw_contact." + RawContacts.SORT_KEY_PRIMARY 1116 + " AS " + Contacts.SORT_KEY_PRIMARY + ", " 1117 + "name_raw_contact." + RawContacts.SORT_KEY_ALTERNATIVE 1118 + " AS " + Contacts.SORT_KEY_ALTERNATIVE + ", " 1119 + "name_raw_contact." + RawContactsColumns.CONTACT_IN_VISIBLE_GROUP 1120 + " AS " + Contacts.IN_VISIBLE_GROUP; 1121 1122 String dataSelect = "SELECT " 1123 + DataColumns.CONCRETE_ID + " AS " + Data._ID + "," 1124 + Data.RAW_CONTACT_ID + ", " 1125 + RawContactsColumns.CONCRETE_CONTACT_ID + " AS " + RawContacts.CONTACT_ID + ", " 1126 + syncColumns + ", " 1127 + dataColumns + ", " 1128 + contactOptionColumns + ", " 1129 + contactNameColumns + ", " 1130 + Contacts.LOOKUP_KEY + ", " 1131 + Contacts.PHOTO_ID + ", " 1132 + ContactsColumns.LAST_STATUS_UPDATE_ID + ", " 1133 + Tables.GROUPS + "." + Groups.SOURCE_ID + " AS " + GroupMembership.GROUP_SOURCE_ID 1134 + " FROM " + Tables.DATA 1135 + " JOIN " + Tables.MIMETYPES + " ON (" 1136 + DataColumns.CONCRETE_MIMETYPE_ID + "=" + MimetypesColumns.CONCRETE_ID + ")" 1137 + " JOIN " + Tables.RAW_CONTACTS + " ON (" 1138 + DataColumns.CONCRETE_RAW_CONTACT_ID + "=" + RawContactsColumns.CONCRETE_ID + ")" 1139 + " JOIN " + Tables.CONTACTS + " ON (" 1140 + RawContactsColumns.CONCRETE_CONTACT_ID + "=" + ContactsColumns.CONCRETE_ID + ")" 1141 + " JOIN " + Tables.RAW_CONTACTS + " AS name_raw_contact ON(" 1142 + Contacts.NAME_RAW_CONTACT_ID + "=name_raw_contact." + RawContacts._ID + ")" 1143 + " LEFT OUTER JOIN " + Tables.PACKAGES + " ON (" 1144 + DataColumns.CONCRETE_PACKAGE_ID + "=" + PackagesColumns.CONCRETE_ID + ")" 1145 + " LEFT OUTER JOIN " + Tables.GROUPS + " ON (" 1146 + MimetypesColumns.CONCRETE_MIMETYPE + "='" + GroupMembership.CONTENT_ITEM_TYPE 1147 + "' AND " + GroupsColumns.CONCRETE_ID + "=" 1148 + Tables.DATA + "." + GroupMembership.GROUP_ROW_ID + ")"; 1149 1150 db.execSQL("CREATE VIEW " + Views.DATA_ALL + " AS " + dataSelect); 1151 db.execSQL("CREATE VIEW " + Views.DATA_RESTRICTED + " AS " + dataSelect + " WHERE " 1152 + RawContactsColumns.CONCRETE_IS_RESTRICTED + "=0"); 1153 1154 String rawContactOptionColumns = 1155 RawContacts.CUSTOM_RINGTONE + "," 1156 + RawContacts.SEND_TO_VOICEMAIL + "," 1157 + RawContacts.LAST_TIME_CONTACTED + "," 1158 + RawContacts.TIMES_CONTACTED + "," 1159 + RawContacts.STARRED; 1160 1161 String rawContactsSelect = "SELECT " 1162 + RawContactsColumns.CONCRETE_ID + " AS " + RawContacts._ID + "," 1163 + RawContacts.CONTACT_ID + ", " 1164 + RawContacts.AGGREGATION_MODE + ", " 1165 + RawContacts.DELETED + ", " 1166 + RawContacts.DISPLAY_NAME_SOURCE + ", " 1167 + RawContacts.DISPLAY_NAME_PRIMARY + ", " 1168 + RawContacts.DISPLAY_NAME_ALTERNATIVE + ", " 1169 + RawContacts.PHONETIC_NAME + ", " 1170 + RawContacts.PHONETIC_NAME_STYLE + ", " 1171 + RawContacts.SORT_KEY_PRIMARY + ", " 1172 + RawContacts.SORT_KEY_ALTERNATIVE + ", " 1173 + rawContactOptionColumns + ", " 1174 + syncColumns 1175 + " FROM " + Tables.RAW_CONTACTS; 1176 1177 db.execSQL("CREATE VIEW " + Views.RAW_CONTACTS_ALL + " AS " + rawContactsSelect); 1178 db.execSQL("CREATE VIEW " + Views.RAW_CONTACTS_RESTRICTED + " AS " + rawContactsSelect 1179 + " WHERE " + RawContacts.IS_RESTRICTED + "=0"); 1180 1181 String contactsColumns = 1182 ContactsColumns.CONCRETE_CUSTOM_RINGTONE 1183 + " AS " + Contacts.CUSTOM_RINGTONE + ", " 1184 + contactNameColumns + ", " 1185 + Contacts.HAS_PHONE_NUMBER + ", " 1186 + Contacts.LOOKUP_KEY + ", " 1187 + Contacts.PHOTO_ID + ", " 1188 + ContactsColumns.CONCRETE_LAST_TIME_CONTACTED 1189 + " AS " + Contacts.LAST_TIME_CONTACTED + ", " 1190 + ContactsColumns.CONCRETE_SEND_TO_VOICEMAIL 1191 + " AS " + Contacts.SEND_TO_VOICEMAIL + ", " 1192 + ContactsColumns.CONCRETE_STARRED 1193 + " AS " + Contacts.STARRED + ", " 1194 + ContactsColumns.CONCRETE_TIMES_CONTACTED 1195 + " AS " + Contacts.TIMES_CONTACTED + ", " 1196 + ContactsColumns.LAST_STATUS_UPDATE_ID; 1197 1198 String contactsSelect = "SELECT " 1199 + ContactsColumns.CONCRETE_ID + " AS " + Contacts._ID + "," 1200 + contactsColumns 1201 + " FROM " + Tables.CONTACTS 1202 + " JOIN " + Tables.RAW_CONTACTS + " AS name_raw_contact ON(" 1203 + Contacts.NAME_RAW_CONTACT_ID + "=name_raw_contact." + RawContacts._ID + ")"; 1204 1205 db.execSQL("CREATE VIEW " + Views.CONTACTS_ALL + " AS " + contactsSelect); 1206 db.execSQL("CREATE VIEW " + Views.CONTACTS_RESTRICTED + " AS " + contactsSelect 1207 + " WHERE " + ContactsColumns.SINGLE_IS_RESTRICTED + "=0"); 1208 } 1209 1210 private static void createGroupsView(SQLiteDatabase db) { 1211 db.execSQL("DROP VIEW IF EXISTS " + Views.GROUPS_ALL + ";"); 1212 String groupsColumns = 1213 Groups.ACCOUNT_NAME + "," 1214 + Groups.ACCOUNT_TYPE + "," 1215 + Groups.SOURCE_ID + "," 1216 + Groups.VERSION + "," 1217 + Groups.DIRTY + "," 1218 + Groups.TITLE + "," 1219 + Groups.TITLE_RES + "," 1220 + Groups.NOTES + "," 1221 + Groups.SYSTEM_ID + "," 1222 + Groups.DELETED + "," 1223 + Groups.GROUP_VISIBLE + "," 1224 + Groups.SHOULD_SYNC + "," 1225 + Groups.SYNC1 + "," 1226 + Groups.SYNC2 + "," 1227 + Groups.SYNC3 + "," 1228 + Groups.SYNC4 + "," 1229 + PackagesColumns.PACKAGE + " AS " + Groups.RES_PACKAGE; 1230 1231 String groupsSelect = "SELECT " 1232 + GroupsColumns.CONCRETE_ID + " AS " + Groups._ID + "," 1233 + groupsColumns 1234 + " FROM " + Tables.GROUPS_JOIN_PACKAGES; 1235 1236 db.execSQL("CREATE VIEW " + Views.GROUPS_ALL + " AS " + groupsSelect); 1237 } 1238 1239 private static void createContactEntitiesView(SQLiteDatabase db) { 1240 db.execSQL("DROP VIEW IF EXISTS " + Tables.CONTACT_ENTITIES + ";"); 1241 db.execSQL("DROP VIEW IF EXISTS " + Tables.CONTACT_ENTITIES_RESTRICTED + ";"); 1242 1243 String contactEntitiesSelect = "SELECT " 1244 + RawContactsColumns.CONCRETE_ACCOUNT_NAME + " AS " + RawContacts.ACCOUNT_NAME + "," 1245 + RawContactsColumns.CONCRETE_ACCOUNT_TYPE + " AS " + RawContacts.ACCOUNT_TYPE + "," 1246 + RawContactsColumns.CONCRETE_SOURCE_ID + " AS " + RawContacts.SOURCE_ID + "," 1247 + RawContactsColumns.CONCRETE_VERSION + " AS " + RawContacts.VERSION + "," 1248 + RawContactsColumns.CONCRETE_DIRTY + " AS " + RawContacts.DIRTY + "," 1249 + RawContactsColumns.CONCRETE_DELETED + " AS " + RawContacts.DELETED + "," 1250 + RawContactsColumns.CONCRETE_NAME_VERIFIED + " AS " + RawContacts.NAME_VERIFIED + "," 1251 + PackagesColumns.PACKAGE + " AS " + Data.RES_PACKAGE + "," 1252 + RawContacts.CONTACT_ID + ", " 1253 + RawContactsColumns.CONCRETE_SYNC1 + " AS " + RawContacts.SYNC1 + ", " 1254 + RawContactsColumns.CONCRETE_SYNC2 + " AS " + RawContacts.SYNC2 + ", " 1255 + RawContactsColumns.CONCRETE_SYNC3 + " AS " + RawContacts.SYNC3 + ", " 1256 + RawContactsColumns.CONCRETE_SYNC4 + " AS " + RawContacts.SYNC4 + ", " 1257 + Data.MIMETYPE + ", " 1258 + Data.DATA1 + ", " 1259 + Data.DATA2 + ", " 1260 + Data.DATA3 + ", " 1261 + Data.DATA4 + ", " 1262 + Data.DATA5 + ", " 1263 + Data.DATA6 + ", " 1264 + Data.DATA7 + ", " 1265 + Data.DATA8 + ", " 1266 + Data.DATA9 + ", " 1267 + Data.DATA10 + ", " 1268 + Data.DATA11 + ", " 1269 + Data.DATA12 + ", " 1270 + Data.DATA13 + ", " 1271 + Data.DATA14 + ", " 1272 + Data.DATA15 + ", " 1273 + Data.SYNC1 + ", " 1274 + Data.SYNC2 + ", " 1275 + Data.SYNC3 + ", " 1276 + Data.SYNC4 + ", " 1277 + RawContactsColumns.CONCRETE_ID + " AS " + RawContacts._ID + ", " 1278 + Data.IS_PRIMARY + ", " 1279 + Data.IS_SUPER_PRIMARY + ", " 1280 + Data.DATA_VERSION + ", " 1281 + DataColumns.CONCRETE_ID + " AS " + RawContacts.Entity.DATA_ID + "," 1282 + RawContactsColumns.CONCRETE_STARRED + " AS " + RawContacts.STARRED + "," 1283 + RawContactsColumns.CONCRETE_IS_RESTRICTED + " AS " 1284 + RawContacts.IS_RESTRICTED + "," 1285 + Tables.GROUPS + "." + Groups.SOURCE_ID + " AS " + GroupMembership.GROUP_SOURCE_ID 1286 + " FROM " + Tables.RAW_CONTACTS 1287 + " LEFT OUTER JOIN " + Tables.DATA + " ON (" 1288 + DataColumns.CONCRETE_RAW_CONTACT_ID + "=" + RawContactsColumns.CONCRETE_ID + ")" 1289 + " LEFT OUTER JOIN " + Tables.PACKAGES + " ON (" 1290 + DataColumns.CONCRETE_PACKAGE_ID + "=" + PackagesColumns.CONCRETE_ID + ")" 1291 + " LEFT OUTER JOIN " + Tables.MIMETYPES + " ON (" 1292 + DataColumns.CONCRETE_MIMETYPE_ID + "=" + MimetypesColumns.CONCRETE_ID + ")" 1293 + " LEFT OUTER JOIN " + Tables.GROUPS + " ON (" 1294 + MimetypesColumns.CONCRETE_MIMETYPE + "='" + GroupMembership.CONTENT_ITEM_TYPE 1295 + "' AND " + GroupsColumns.CONCRETE_ID + "=" 1296 + Tables.DATA + "." + GroupMembership.GROUP_ROW_ID + ")"; 1297 1298 db.execSQL("CREATE VIEW " + Tables.CONTACT_ENTITIES + " AS " 1299 + contactEntitiesSelect); 1300 db.execSQL("CREATE VIEW " + Tables.CONTACT_ENTITIES_RESTRICTED + " AS " 1301 + contactEntitiesSelect + " WHERE " + RawContacts.IS_RESTRICTED + "=0"); 1302 } 1303 1304 @Override 1305 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 1306 if (oldVersion < 99) { 1307 Log.i(TAG, "Upgrading from version " + oldVersion + " to " + newVersion 1308 + ", data will be lost!"); 1309 1310 db.execSQL("DROP TABLE IF EXISTS " + Tables.CONTACTS + ";"); 1311 db.execSQL("DROP TABLE IF EXISTS " + Tables.RAW_CONTACTS + ";"); 1312 db.execSQL("DROP TABLE IF EXISTS " + Tables.PACKAGES + ";"); 1313 db.execSQL("DROP TABLE IF EXISTS " + Tables.MIMETYPES + ";"); 1314 db.execSQL("DROP TABLE IF EXISTS " + Tables.DATA + ";"); 1315 db.execSQL("DROP TABLE IF EXISTS " + Tables.PHONE_LOOKUP + ";"); 1316 db.execSQL("DROP TABLE IF EXISTS " + Tables.NAME_LOOKUP + ";"); 1317 db.execSQL("DROP TABLE IF EXISTS " + Tables.NICKNAME_LOOKUP + ";"); 1318 db.execSQL("DROP TABLE IF EXISTS " + Tables.GROUPS + ";"); 1319 db.execSQL("DROP TABLE IF EXISTS " + Tables.ACTIVITIES + ";"); 1320 db.execSQL("DROP TABLE IF EXISTS " + Tables.CALLS + ";"); 1321 db.execSQL("DROP TABLE IF EXISTS " + Tables.SETTINGS + ";"); 1322 db.execSQL("DROP TABLE IF EXISTS " + Tables.STATUS_UPDATES + ";"); 1323 1324 // TODO: we should not be dropping agg_exceptions and contact_options. In case that 1325 // table's schema changes, we should try to preserve the data, because it was entered 1326 // by the user and has never been synched to the server. 1327 db.execSQL("DROP TABLE IF EXISTS " + Tables.AGGREGATION_EXCEPTIONS + ";"); 1328 1329 onCreate(db); 1330 return; 1331 } 1332 1333 Log.i(TAG, "Upgrading from version " + oldVersion + " to " + newVersion); 1334 1335 boolean upgradeViewsAndTriggers = false; 1336 boolean upgradeNameLookup = false; 1337 1338 if (oldVersion == 99) { 1339 upgradeViewsAndTriggers = true; 1340 oldVersion++; 1341 } 1342 1343 if (oldVersion == 100) { 1344 db.execSQL("CREATE INDEX IF NOT EXISTS mimetypes_mimetype_index ON " 1345 + Tables.MIMETYPES + " (" 1346 + MimetypesColumns.MIMETYPE + "," 1347 + MimetypesColumns._ID + ");"); 1348 updateIndexStats(db, Tables.MIMETYPES, 1349 "mimetypes_mimetype_index", "50 1 1"); 1350 1351 upgradeViewsAndTriggers = true; 1352 oldVersion++; 1353 } 1354 1355 if (oldVersion == 101) { 1356 upgradeViewsAndTriggers = true; 1357 oldVersion++; 1358 } 1359 1360 if (oldVersion == 102) { 1361 upgradeViewsAndTriggers = true; 1362 oldVersion++; 1363 } 1364 1365 if (oldVersion == 103) { 1366 upgradeViewsAndTriggers = true; 1367 oldVersion++; 1368 } 1369 1370 if (oldVersion == 104 || oldVersion == 201) { 1371 LegacyApiSupport.createSettingsTable(db); 1372 upgradeViewsAndTriggers = true; 1373 oldVersion++; 1374 } 1375 1376 if (oldVersion == 105) { 1377 upgradeToVersion202(db); 1378 upgradeNameLookup = true; 1379 oldVersion = 202; 1380 } 1381 1382 if (oldVersion == 202) { 1383 upgradeToVersion203(db); 1384 upgradeViewsAndTriggers = true; 1385 oldVersion++; 1386 } 1387 1388 if (oldVersion == 203) { 1389 upgradeViewsAndTriggers = true; 1390 oldVersion++; 1391 } 1392 1393 if (oldVersion == 204) { 1394 upgradeToVersion205(db); 1395 upgradeViewsAndTriggers = true; 1396 oldVersion++; 1397 } 1398 1399 if (oldVersion == 205) { 1400 upgrateToVersion206(db); 1401 upgradeViewsAndTriggers = true; 1402 oldVersion++; 1403 } 1404 1405 if (oldVersion == 206) { 1406 upgradeToVersion300(db); 1407 oldVersion = 300; 1408 } 1409 1410 if (oldVersion == 300) { 1411 upgradeViewsAndTriggers = true; 1412 oldVersion = 301; 1413 } 1414 1415 if (oldVersion == 301) { 1416 upgradeViewsAndTriggers = true; 1417 oldVersion = 302; 1418 } 1419 1420 if (oldVersion == 302) { 1421 upgradeEmailToVersion303(db); 1422 upgradeNicknameToVersion303(db); 1423 oldVersion = 303; 1424 } 1425 1426 if (upgradeViewsAndTriggers) { 1427 createContactsViews(db); 1428 createGroupsView(db); 1429 createContactEntitiesView(db); 1430 createContactsTriggers(db); 1431 createContactsIndexes(db); 1432 LegacyApiSupport.createViews(db); 1433 updateSqliteStats(db); 1434 mReopenDatabase = true; 1435 } 1436 1437 if (upgradeNameLookup) { 1438 rebuildNameLookup(db); 1439 } 1440 1441 if (oldVersion != newVersion) { 1442 throw new IllegalStateException( 1443 "error upgrading the database to version " + newVersion); 1444 } 1445 } 1446 1447 private void upgradeToVersion202(SQLiteDatabase db) { 1448 db.execSQL( 1449 "ALTER TABLE " + Tables.PHONE_LOOKUP + 1450 " ADD " + PhoneLookupColumns.MIN_MATCH + " TEXT;"); 1451 1452 db.execSQL("CREATE INDEX phone_lookup_min_match_index ON " + Tables.PHONE_LOOKUP + " (" + 1453 PhoneLookupColumns.MIN_MATCH + "," + 1454 PhoneLookupColumns.RAW_CONTACT_ID + "," + 1455 PhoneLookupColumns.DATA_ID + 1456 ");"); 1457 1458 updateIndexStats(db, Tables.PHONE_LOOKUP, 1459 "phone_lookup_min_match_index", "10000 2 2 1"); 1460 1461 SQLiteStatement update = db.compileStatement( 1462 "UPDATE " + Tables.PHONE_LOOKUP + 1463 " SET " + PhoneLookupColumns.MIN_MATCH + "=?" + 1464 " WHERE " + PhoneLookupColumns.DATA_ID + "=?"); 1465 1466 // Populate the new column 1467 Cursor c = db.query(Tables.PHONE_LOOKUP + " JOIN " + Tables.DATA + 1468 " ON (" + PhoneLookupColumns.DATA_ID + "=" + DataColumns.CONCRETE_ID + ")", 1469 new String[]{Data._ID, Phone.NUMBER}, null, null, null, null, null); 1470 try { 1471 while (c.moveToNext()) { 1472 long dataId = c.getLong(0); 1473 String number = c.getString(1); 1474 if (!TextUtils.isEmpty(number)) { 1475 update.bindString(1, PhoneNumberUtils.toCallerIDMinMatch(number)); 1476 update.bindLong(2, dataId); 1477 update.execute(); 1478 } 1479 } 1480 } finally { 1481 c.close(); 1482 } 1483 } 1484 1485 private void upgradeToVersion203(SQLiteDatabase db) { 1486 db.execSQL( 1487 "ALTER TABLE " + Tables.CONTACTS + 1488 " ADD " + Contacts.NAME_RAW_CONTACT_ID + " INTEGER REFERENCES raw_contacts(_id)"); 1489 db.execSQL( 1490 "ALTER TABLE " + Tables.RAW_CONTACTS + 1491 " ADD " + RawContactsColumns.CONTACT_IN_VISIBLE_GROUP 1492 + " INTEGER NOT NULL DEFAULT 0"); 1493 1494 // For each Contact, find the RawContact that contributed the display name 1495 db.execSQL( 1496 "UPDATE " + Tables.CONTACTS + 1497 " SET " + Contacts.NAME_RAW_CONTACT_ID + "=(" + 1498 " SELECT " + RawContacts._ID + 1499 " FROM " + Tables.RAW_CONTACTS + 1500 " WHERE " + RawContacts.CONTACT_ID + "=" + ContactsColumns.CONCRETE_ID + 1501 " AND " + RawContactsColumns.CONCRETE_DISPLAY_NAME + "=" + 1502 Tables.CONTACTS + "." + Contacts.DISPLAY_NAME + 1503 " ORDER BY " + RawContacts._ID + 1504 " LIMIT 1)" 1505 ); 1506 1507 db.execSQL("CREATE INDEX contacts_name_raw_contact_id_index ON " + Tables.CONTACTS + " (" + 1508 Contacts.NAME_RAW_CONTACT_ID + 1509 ");"); 1510 1511 // If for some unknown reason we missed some names, let's make sure there are 1512 // no contacts without a name, picking a raw contact "at random". 1513 db.execSQL( 1514 "UPDATE " + Tables.CONTACTS + 1515 " SET " + Contacts.NAME_RAW_CONTACT_ID + "=(" + 1516 " SELECT " + RawContacts._ID + 1517 " FROM " + Tables.RAW_CONTACTS + 1518 " WHERE " + RawContacts.CONTACT_ID + "=" + ContactsColumns.CONCRETE_ID + 1519 " ORDER BY " + RawContacts._ID + 1520 " LIMIT 1)" + 1521 " WHERE " + Contacts.NAME_RAW_CONTACT_ID + " IS NULL" 1522 ); 1523 1524 // Wipe out DISPLAY_NAME on the Contacts table as it is no longer in use. 1525 db.execSQL( 1526 "UPDATE " + Tables.CONTACTS + 1527 " SET " + Contacts.DISPLAY_NAME + "=NULL" 1528 ); 1529 1530 // Copy the IN_VISIBLE_GROUP flag down to all raw contacts to allow 1531 // indexing on (display_name, in_visible_group) 1532 db.execSQL( 1533 "UPDATE " + Tables.RAW_CONTACTS + 1534 " SET " + RawContactsColumns.CONTACT_IN_VISIBLE_GROUP + "=(" + 1535 "SELECT " + Contacts.IN_VISIBLE_GROUP + 1536 " FROM " + Tables.CONTACTS + 1537 " WHERE " + Contacts._ID + "=" + RawContacts.CONTACT_ID + ")" + 1538 " WHERE " + RawContacts.CONTACT_ID + " NOT NULL" 1539 ); 1540 1541 db.execSQL("CREATE INDEX raw_contact_sort_key1_index ON " + Tables.RAW_CONTACTS + " (" + 1542 RawContactsColumns.CONTACT_IN_VISIBLE_GROUP + "," + 1543 RawContactsColumns.DISPLAY_NAME + " COLLATE LOCALIZED ASC" + 1544 ");"); 1545 1546 db.execSQL("DROP INDEX contacts_visible_index"); 1547 db.execSQL("CREATE INDEX contacts_visible_index ON " + Tables.CONTACTS + " (" + 1548 Contacts.IN_VISIBLE_GROUP + 1549 ");"); 1550 } 1551 1552 private void upgradeToVersion205(SQLiteDatabase db) { 1553 db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS 1554 + " ADD " + RawContacts.DISPLAY_NAME_ALTERNATIVE + " TEXT;"); 1555 db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS 1556 + " ADD " + RawContacts.PHONETIC_NAME + " TEXT;"); 1557 db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS 1558 + " ADD " + RawContacts.PHONETIC_NAME_STYLE + " INTEGER;"); 1559 db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS 1560 + " ADD " + RawContacts.SORT_KEY_PRIMARY + " TEXT COLLATE LOCALIZED;"); 1561 db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS 1562 + " ADD " + RawContacts.SORT_KEY_ALTERNATIVE + " TEXT COLLATE LOCALIZED;"); 1563 1564 final Locale locale = Locale.getDefault(); 1565 1566 NameSplitter splitter = new NameSplitter( 1567 mContext.getString(com.android.internal.R.string.common_name_prefixes), 1568 mContext.getString(com.android.internal.R.string.common_last_name_prefixes), 1569 mContext.getString(com.android.internal.R.string.common_name_suffixes), 1570 mContext.getString(com.android.internal.R.string.common_name_conjunctions), 1571 locale); 1572 1573 SQLiteStatement rawContactUpdate = db.compileStatement( 1574 "UPDATE " + Tables.RAW_CONTACTS + 1575 " SET " + 1576 RawContacts.DISPLAY_NAME_PRIMARY + "=?," + 1577 RawContacts.DISPLAY_NAME_ALTERNATIVE + "=?," + 1578 RawContacts.PHONETIC_NAME + "=?," + 1579 RawContacts.PHONETIC_NAME_STYLE + "=?," + 1580 RawContacts.SORT_KEY_PRIMARY + "=?," + 1581 RawContacts.SORT_KEY_ALTERNATIVE + "=?" + 1582 " WHERE " + RawContacts._ID + "=?"); 1583 1584 upgradeStructuredNamesToVersion205(db, rawContactUpdate, splitter); 1585 upgradeOrganizationsToVersion205(db, rawContactUpdate, splitter); 1586 1587 db.execSQL("DROP INDEX raw_contact_sort_key1_index"); 1588 db.execSQL("CREATE INDEX raw_contact_sort_key1_index ON " + Tables.RAW_CONTACTS + " (" + 1589 RawContactsColumns.CONTACT_IN_VISIBLE_GROUP + "," + 1590 RawContacts.SORT_KEY_PRIMARY + 1591 ");"); 1592 1593 db.execSQL("CREATE INDEX raw_contact_sort_key2_index ON " + Tables.RAW_CONTACTS + " (" + 1594 RawContactsColumns.CONTACT_IN_VISIBLE_GROUP + "," + 1595 RawContacts.SORT_KEY_ALTERNATIVE + 1596 ");"); 1597 } 1598 1599 private interface StructName205Query { 1600 String TABLE = Tables.DATA_JOIN_RAW_CONTACTS; 1601 1602 String COLUMNS[] = { 1603 DataColumns.CONCRETE_ID, 1604 Data.RAW_CONTACT_ID, 1605 RawContacts.DISPLAY_NAME_SOURCE, 1606 RawContacts.DISPLAY_NAME_PRIMARY, 1607 StructuredName.PREFIX, 1608 StructuredName.GIVEN_NAME, 1609 StructuredName.MIDDLE_NAME, 1610 StructuredName.FAMILY_NAME, 1611 StructuredName.SUFFIX, 1612 StructuredName.PHONETIC_FAMILY_NAME, 1613 StructuredName.PHONETIC_MIDDLE_NAME, 1614 StructuredName.PHONETIC_GIVEN_NAME, 1615 }; 1616 1617 int ID = 0; 1618 int RAW_CONTACT_ID = 1; 1619 int DISPLAY_NAME_SOURCE = 2; 1620 int DISPLAY_NAME = 3; 1621 int PREFIX = 4; 1622 int GIVEN_NAME = 5; 1623 int MIDDLE_NAME = 6; 1624 int FAMILY_NAME = 7; 1625 int SUFFIX = 8; 1626 int PHONETIC_FAMILY_NAME = 9; 1627 int PHONETIC_MIDDLE_NAME = 10; 1628 int PHONETIC_GIVEN_NAME = 11; 1629 } 1630 1631 private void upgradeStructuredNamesToVersion205(SQLiteDatabase db, 1632 SQLiteStatement rawContactUpdate, NameSplitter splitter) { 1633 1634 // Process structured names to detect the style of the full name and phonetic name 1635 1636 long mMimeType; 1637 try { 1638 mMimeType = DatabaseUtils.longForQuery(db, 1639 "SELECT " + MimetypesColumns._ID + 1640 " FROM " + Tables.MIMETYPES + 1641 " WHERE " + MimetypesColumns.MIMETYPE 1642 + "='" + StructuredName.CONTENT_ITEM_TYPE + "'", null); 1643 } catch (SQLiteDoneException e) { 1644 // No structured names in the database 1645 return; 1646 } 1647 1648 SQLiteStatement structuredNameUpdate = db.compileStatement( 1649 "UPDATE " + Tables.DATA + 1650 " SET " + 1651 StructuredName.FULL_NAME_STYLE + "=?," + 1652 StructuredName.DISPLAY_NAME + "=?," + 1653 StructuredName.PHONETIC_NAME_STYLE + "=?" + 1654 " WHERE " + Data._ID + "=?"); 1655 1656 NameSplitter.Name name = new NameSplitter.Name(); 1657 StringBuilder sb = new StringBuilder(); 1658 Cursor cursor = db.query(StructName205Query.TABLE, 1659 StructName205Query.COLUMNS, 1660 DataColumns.MIMETYPE_ID + "=" + mMimeType, null, null, null, null); 1661 try { 1662 while (cursor.moveToNext()) { 1663 long dataId = cursor.getLong(StructName205Query.ID); 1664 long rawContactId = cursor.getLong(StructName205Query.RAW_CONTACT_ID); 1665 int displayNameSource = cursor.getInt(StructName205Query.DISPLAY_NAME_SOURCE); 1666 String displayName = cursor.getString(StructName205Query.DISPLAY_NAME); 1667 1668 name.clear(); 1669 name.prefix = cursor.getString(StructName205Query.PREFIX); 1670 name.givenNames = cursor.getString(StructName205Query.GIVEN_NAME); 1671 name.middleName = cursor.getString(StructName205Query.MIDDLE_NAME); 1672 name.familyName = cursor.getString(StructName205Query.FAMILY_NAME); 1673 name.suffix = cursor.getString(StructName205Query.SUFFIX); 1674 name.phoneticFamilyName = cursor.getString(StructName205Query.PHONETIC_FAMILY_NAME); 1675 name.phoneticMiddleName = cursor.getString(StructName205Query.PHONETIC_MIDDLE_NAME); 1676 name.phoneticGivenName = cursor.getString(StructName205Query.PHONETIC_GIVEN_NAME); 1677 1678 upgradeNameToVersion205(dataId, rawContactId, displayNameSource, displayName, name, 1679 structuredNameUpdate, rawContactUpdate, splitter, sb); 1680 } 1681 } finally { 1682 cursor.close(); 1683 } 1684 } 1685 1686 private void upgradeNameToVersion205(long dataId, long rawContactId, int displayNameSource, 1687 String currentDisplayName, NameSplitter.Name name, 1688 SQLiteStatement structuredNameUpdate, SQLiteStatement rawContactUpdate, 1689 NameSplitter splitter, StringBuilder sb) { 1690 1691 splitter.guessNameStyle(name); 1692 name.fullNameStyle = splitter.getAdjustedFullNameStyle(name.fullNameStyle); 1693 String displayName = splitter.join(name, true); 1694 1695 structuredNameUpdate.bindLong(1, name.fullNameStyle); 1696 DatabaseUtils.bindObjectToProgram(structuredNameUpdate, 2, displayName); 1697 structuredNameUpdate.bindLong(3, name.phoneticNameStyle); 1698 structuredNameUpdate.bindLong(4, dataId); 1699 structuredNameUpdate.execute(); 1700 1701 if (displayNameSource == DisplayNameSources.STRUCTURED_NAME) { 1702 String displayNameAlternative = splitter.join(name, false); 1703 String phoneticName = splitter.joinPhoneticName(name); 1704 String sortKey = null; 1705 String sortKeyAlternative = null; 1706 1707 if (phoneticName != null) { 1708 sortKey = sortKeyAlternative = phoneticName; 1709 } else if (name.fullNameStyle == FullNameStyle.CHINESE) { 1710 sortKey = sortKeyAlternative = splitter.convertHanziToPinyin(displayName); 1711 } 1712 1713 if (sortKey == null) { 1714 sortKey = displayName; 1715 sortKeyAlternative = displayNameAlternative; 1716 } 1717 1718 updateRawContact205(rawContactUpdate, rawContactId, displayName, 1719 displayNameAlternative, name.phoneticNameStyle, phoneticName, sortKey, 1720 sortKeyAlternative); 1721 } 1722 } 1723 1724 private interface Organization205Query { 1725 String TABLE = Tables.DATA_JOIN_RAW_CONTACTS; 1726 1727 String COLUMNS[] = { 1728 DataColumns.CONCRETE_ID, 1729 Data.RAW_CONTACT_ID, 1730 Organization.COMPANY, 1731 Organization.PHONETIC_NAME, 1732 }; 1733 1734 int ID = 0; 1735 int RAW_CONTACT_ID = 1; 1736 int COMPANY = 2; 1737 int PHONETIC_NAME = 3; 1738 } 1739 1740 private void upgradeOrganizationsToVersion205(SQLiteDatabase db, 1741 SQLiteStatement rawContactUpdate, NameSplitter splitter) { 1742 final long mimeType = lookupMimeTypeId(db, Organization.CONTENT_ITEM_TYPE); 1743 1744 SQLiteStatement organizationUpdate = db.compileStatement( 1745 "UPDATE " + Tables.DATA + 1746 " SET " + 1747 Organization.PHONETIC_NAME_STYLE + "=?" + 1748 " WHERE " + Data._ID + "=?"); 1749 1750 Cursor cursor = db.query(Organization205Query.TABLE, Organization205Query.COLUMNS, 1751 DataColumns.MIMETYPE_ID + "=" + mimeType + " AND " 1752 + RawContacts.DISPLAY_NAME_SOURCE + "=" + DisplayNameSources.ORGANIZATION, 1753 null, null, null, null); 1754 try { 1755 while (cursor.moveToNext()) { 1756 long dataId = cursor.getLong(Organization205Query.ID); 1757 long rawContactId = cursor.getLong(Organization205Query.RAW_CONTACT_ID); 1758 String company = cursor.getString(Organization205Query.COMPANY); 1759 String phoneticName = cursor.getString(Organization205Query.PHONETIC_NAME); 1760 1761 int phoneticNameStyle = splitter.guessPhoneticNameStyle(phoneticName); 1762 1763 organizationUpdate.bindLong(1, phoneticNameStyle); 1764 organizationUpdate.bindLong(2, dataId); 1765 organizationUpdate.execute(); 1766 1767 String sortKey = null; 1768 if (phoneticName == null && company != null) { 1769 int nameStyle = splitter.guessFullNameStyle(company); 1770 nameStyle = splitter.getAdjustedFullNameStyle(nameStyle); 1771 if (nameStyle == FullNameStyle.CHINESE) { 1772 sortKey = splitter.convertHanziToPinyin(company); 1773 } 1774 } 1775 1776 if (sortKey == null) { 1777 sortKey = company; 1778 } 1779 1780 updateRawContact205(rawContactUpdate, rawContactId, company, 1781 company, phoneticNameStyle, phoneticName, sortKey, sortKey); 1782 } 1783 } finally { 1784 cursor.close(); 1785 } 1786 } 1787 1788 private void updateRawContact205(SQLiteStatement rawContactUpdate, long rawContactId, 1789 String displayName, String displayNameAlternative, int phoneticNameStyle, 1790 String phoneticName, String sortKeyPrimary, String sortKeyAlternative) { 1791 bindString(rawContactUpdate, 1, displayName); 1792 bindString(rawContactUpdate, 2, displayNameAlternative); 1793 bindString(rawContactUpdate, 3, phoneticName); 1794 rawContactUpdate.bindLong(4, phoneticNameStyle); 1795 bindString(rawContactUpdate, 5, sortKeyPrimary); 1796 bindString(rawContactUpdate, 6, sortKeyAlternative); 1797 rawContactUpdate.bindLong(7, rawContactId); 1798 rawContactUpdate.execute(); 1799 } 1800 1801 private void upgrateToVersion206(SQLiteDatabase db) { 1802 db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS 1803 + " ADD " + RawContacts.NAME_VERIFIED + " INTEGER NOT NULL DEFAULT 0;"); 1804 } 1805 1806 private interface Organization300Query { 1807 String TABLE = Tables.DATA; 1808 1809 String SELECTION = DataColumns.MIMETYPE_ID + "=?"; 1810 1811 String COLUMNS[] = { 1812 Organization._ID, 1813 Organization.RAW_CONTACT_ID, 1814 Organization.COMPANY, 1815 Organization.TITLE 1816 }; 1817 1818 int ID = 0; 1819 int RAW_CONTACT_ID = 1; 1820 int COMPANY = 2; 1821 int TITLE = 3; 1822 } 1823 1824 /** 1825 * Fix for the bug where name lookup records for organizations would get removed by 1826 * unrelated updates of the data rows. 1827 */ 1828 private void upgradeToVersion300(SQLiteDatabase db) { 1829 final long mimeType = lookupMimeTypeId(db, Organization.CONTENT_ITEM_TYPE); 1830 if (mimeType == -1) { 1831 return; 1832 } 1833 1834 ContentValues values = new ContentValues(); 1835 1836 // Find all data rows with the mime type "organization" 1837 Cursor cursor = db.query(Organization300Query.TABLE, Organization300Query.COLUMNS, 1838 Organization300Query.SELECTION, new String[] {String.valueOf(mimeType)}, 1839 null, null, null); 1840 try { 1841 while (cursor.moveToNext()) { 1842 long dataId = cursor.getLong(Organization300Query.ID); 1843 long rawContactId = cursor.getLong(Organization300Query.RAW_CONTACT_ID); 1844 String company = cursor.getString(Organization300Query.COMPANY); 1845 String title = cursor.getString(Organization300Query.TITLE); 1846 1847 // First delete name lookup if there is any (chances are there won't be) 1848 db.delete(Tables.NAME_LOOKUP, NameLookupColumns.DATA_ID + "=?", 1849 new String[]{String.valueOf(dataId)}); 1850 1851 // Now insert two name lookup records: one for company name, one for title 1852 values.put(NameLookupColumns.DATA_ID, dataId); 1853 values.put(NameLookupColumns.RAW_CONTACT_ID, rawContactId); 1854 values.put(NameLookupColumns.NAME_TYPE, NameLookupType.ORGANIZATION); 1855 1856 if (!TextUtils.isEmpty(company)) { 1857 values.put(NameLookupColumns.NORMALIZED_NAME, 1858 NameNormalizer.normalize(company)); 1859 db.insert(Tables.NAME_LOOKUP, null, values); 1860 } 1861 1862 if (!TextUtils.isEmpty(title)) { 1863 values.put(NameLookupColumns.NORMALIZED_NAME, 1864 NameNormalizer.normalize(title)); 1865 db.insert(Tables.NAME_LOOKUP, null, values); 1866 } 1867 } 1868 } finally { 1869 cursor.close(); 1870 } 1871 } 1872 1873 private static final class Upgrade303Query { 1874 public static final String TABLE = Tables.DATA; 1875 1876 public static final String SELECTION = 1877 DataColumns.MIMETYPE_ID + "=?" + 1878 " AND " + Data._ID + " NOT IN " + 1879 "(SELECT " + NameLookupColumns.DATA_ID + " FROM " + Tables.NAME_LOOKUP + ")" + 1880 " AND " + Data.DATA1 + " NOT NULL"; 1881 1882 public static final String COLUMNS[] = { 1883 Data._ID, 1884 Data.RAW_CONTACT_ID, 1885 Data.DATA1, 1886 }; 1887 1888 public static final int ID = 0; 1889 public static final int RAW_CONTACT_ID = 1; 1890 public static final int DATA1 = 2; 1891 } 1892 1893 /** 1894 * The {@link ContactsProvider2#update} method was deleting name lookup for new 1895 * emails during the sync. We need to restore the lost name lookup rows. 1896 */ 1897 private void upgradeEmailToVersion303(SQLiteDatabase db) { 1898 final long mimeTypeId = lookupMimeTypeId(db, Email.CONTENT_ITEM_TYPE); 1899 if (mimeTypeId == -1) { 1900 return; 1901 } 1902 1903 ContentValues values = new ContentValues(); 1904 1905 // Find all data rows with the mime type "email" that are missing name lookup 1906 Cursor cursor = db.query(Upgrade303Query.TABLE, Upgrade303Query.COLUMNS, 1907 Upgrade303Query.SELECTION, new String[] {String.valueOf(mimeTypeId)}, 1908 null, null, null); 1909 try { 1910 while (cursor.moveToNext()) { 1911 long dataId = cursor.getLong(Upgrade303Query.ID); 1912 long rawContactId = cursor.getLong(Upgrade303Query.RAW_CONTACT_ID); 1913 String value = cursor.getString(Upgrade303Query.DATA1); 1914 value = extractHandleFromEmailAddress(value); 1915 1916 if (value != null) { 1917 values.put(NameLookupColumns.DATA_ID, dataId); 1918 values.put(NameLookupColumns.RAW_CONTACT_ID, rawContactId); 1919 values.put(NameLookupColumns.NAME_TYPE, NameLookupType.EMAIL_BASED_NICKNAME); 1920 values.put(NameLookupColumns.NORMALIZED_NAME, NameNormalizer.normalize(value)); 1921 db.insert(Tables.NAME_LOOKUP, null, values); 1922 } 1923 } 1924 } finally { 1925 cursor.close(); 1926 } 1927 } 1928 1929 /** 1930 * The {@link ContactsProvider2#update} method was deleting name lookup for new 1931 * nicknames during the sync. We need to restore the lost name lookup rows. 1932 */ 1933 private void upgradeNicknameToVersion303(SQLiteDatabase db) { 1934 final long mimeTypeId = lookupMimeTypeId(db, Nickname.CONTENT_ITEM_TYPE); 1935 if (mimeTypeId == -1) { 1936 return; 1937 } 1938 1939 ContentValues values = new ContentValues(); 1940 1941 // Find all data rows with the mime type "nickname" that are missing name lookup 1942 Cursor cursor = db.query(Upgrade303Query.TABLE, Upgrade303Query.COLUMNS, 1943 Upgrade303Query.SELECTION, new String[] {String.valueOf(mimeTypeId)}, 1944 null, null, null); 1945 try { 1946 while (cursor.moveToNext()) { 1947 long dataId = cursor.getLong(Upgrade303Query.ID); 1948 long rawContactId = cursor.getLong(Upgrade303Query.RAW_CONTACT_ID); 1949 String value = cursor.getString(Upgrade303Query.DATA1); 1950 1951 values.put(NameLookupColumns.DATA_ID, dataId); 1952 values.put(NameLookupColumns.RAW_CONTACT_ID, rawContactId); 1953 values.put(NameLookupColumns.NAME_TYPE, NameLookupType.NICKNAME); 1954 values.put(NameLookupColumns.NORMALIZED_NAME, NameNormalizer.normalize(value)); 1955 db.insert(Tables.NAME_LOOKUP, null, values); 1956 } 1957 } finally { 1958 cursor.close(); 1959 } 1960 } 1961 1962 /** 1963 * Regenerate all rows in the nickname_lookup and name_lookup tables, 1964 * because sort key generation algorithm has been modified, e.g. as a result of 1965 * locale change. 1966 */ 1967 private void rebuildNameLookup(SQLiteDatabase db) { 1968 db.execSQL("DELETE FROM " + Tables.NICKNAME_LOOKUP); 1969 loadNicknameLookupTable(db); 1970 1971 db.execSQL("DROP INDEX IF EXISTS name_lookup_index"); 1972 db.execSQL("DELETE FROM " + Tables.NAME_LOOKUP); 1973 1974 SQLiteStatement nameLookupInsert = db.compileStatement( 1975 "INSERT OR IGNORE INTO " + Tables.NAME_LOOKUP + "(" 1976 + NameLookupColumns.RAW_CONTACT_ID + "," 1977 + NameLookupColumns.DATA_ID + "," 1978 + NameLookupColumns.NAME_TYPE + "," 1979 + NameLookupColumns.NORMALIZED_NAME + 1980 ") VALUES (?,?,?,?)"); 1981 1982 insertStructuredNameLookup(db, nameLookupInsert); 1983 insertOrganizationLookup(db, nameLookupInsert); 1984 insertEmailLookup(db, nameLookupInsert); 1985 insertNicknameLookup(db, nameLookupInsert); 1986 1987 createContactsIndexes(db); 1988 } 1989 1990 private static final class StructuredNameQuery { 1991 public static final String TABLE = Tables.DATA; 1992 1993 public static final String SELECTION = 1994 DataColumns.MIMETYPE_ID + "=? AND " + Data.DATA1 + " NOT NULL"; 1995 1996 public static final String COLUMNS[] = { 1997 StructuredName._ID, 1998 StructuredName.RAW_CONTACT_ID, 1999 StructuredName.DISPLAY_NAME, 2000 }; 2001 2002 public static final int ID = 0; 2003 public static final int RAW_CONTACT_ID = 1; 2004 public static final int DISPLAY_NAME = 2; 2005 } 2006 2007 private class StructuredNameLookupBuilder extends NameLookupBuilder { 2008 2009 private final SQLiteStatement mNameLookupInsert; 2010 private final CommonNicknameCache mCommonNicknameCache; 2011 2012 public StructuredNameLookupBuilder(NameSplitter splitter, 2013 CommonNicknameCache commonNicknameCache, SQLiteStatement nameLookupInsert) { 2014 super(splitter); 2015 this.mCommonNicknameCache = commonNicknameCache; 2016 this.mNameLookupInsert = nameLookupInsert; 2017 } 2018 2019 @Override 2020 protected void insertNameLookup(long rawContactId, long dataId, int lookupType, 2021 String name) { 2022 if (!TextUtils.isEmpty(name)) { 2023 ContactsDatabaseHelper.this.insertNormalizedNameLookup(mNameLookupInsert, 2024 rawContactId, dataId, lookupType, name); 2025 } 2026 } 2027 2028 @Override 2029 protected String[] getCommonNicknameClusters(String normalizedName) { 2030 return mCommonNicknameCache.getCommonNicknameClusters(normalizedName); 2031 } 2032 } 2033 2034 /** 2035 * Inserts name lookup rows for all structured names in the database. 2036 */ 2037 private void insertStructuredNameLookup(SQLiteDatabase db, SQLiteStatement nameLookupInsert) { 2038 NameLookupBuilder nameLookupBuilder = new StructuredNameLookupBuilder(createNameSplitter(), 2039 new CommonNicknameCache(db), nameLookupInsert); 2040 final long mimeTypeId = lookupMimeTypeId(db, StructuredName.CONTENT_ITEM_TYPE); 2041 Cursor cursor = db.query(StructuredNameQuery.TABLE, StructuredNameQuery.COLUMNS, 2042 StructuredNameQuery.SELECTION, new String[] {String.valueOf(mimeTypeId)}, 2043 null, null, null); 2044 try { 2045 while (cursor.moveToNext()) { 2046 long dataId = cursor.getLong(StructuredNameQuery.ID); 2047 long rawContactId = cursor.getLong(StructuredNameQuery.RAW_CONTACT_ID); 2048 String name = cursor.getString(StructuredNameQuery.DISPLAY_NAME); 2049 nameLookupBuilder.insertNameLookup(rawContactId, dataId, name); 2050 } 2051 } finally { 2052 cursor.close(); 2053 } 2054 } 2055 2056 private static final class OrganizationQuery { 2057 public static final String TABLE = Tables.DATA; 2058 2059 public static final String SELECTION = 2060 DataColumns.MIMETYPE_ID + "=? AND " + Data.DATA1 + " NOT NULL"; 2061 2062 public static final String COLUMNS[] = { 2063 Organization._ID, 2064 Organization.RAW_CONTACT_ID, 2065 Organization.COMPANY, 2066 Organization.TITLE, 2067 }; 2068 2069 public static final int ID = 0; 2070 public static final int RAW_CONTACT_ID = 1; 2071 public static final int COMPANY = 2; 2072 public static final int TITLE = 3; 2073 } 2074 2075 /** 2076 * Inserts name lookup rows for all organizations in the database. 2077 */ 2078 private void insertOrganizationLookup(SQLiteDatabase db, SQLiteStatement nameLookupInsert) { 2079 final long mimeTypeId = lookupMimeTypeId(db, Organization.CONTENT_ITEM_TYPE); 2080 Cursor cursor = db.query(OrganizationQuery.TABLE, OrganizationQuery.COLUMNS, 2081 OrganizationQuery.SELECTION, new String[] {String.valueOf(mimeTypeId)}, 2082 null, null, null); 2083 try { 2084 while (cursor.moveToNext()) { 2085 long dataId = cursor.getLong(OrganizationQuery.ID); 2086 long rawContactId = cursor.getLong(OrganizationQuery.RAW_CONTACT_ID); 2087 String organization = cursor.getString(OrganizationQuery.COMPANY); 2088 String title = cursor.getString(OrganizationQuery.TITLE); 2089 insertNameLookup(nameLookupInsert, rawContactId, dataId, 2090 NameLookupType.ORGANIZATION, organization); 2091 insertNameLookup(nameLookupInsert, rawContactId, dataId, 2092 NameLookupType.ORGANIZATION, title); 2093 } 2094 } finally { 2095 cursor.close(); 2096 } 2097 } 2098 2099 private static final class EmailQuery { 2100 public static final String TABLE = Tables.DATA; 2101 2102 public static final String SELECTION = 2103 DataColumns.MIMETYPE_ID + "=? AND " + Data.DATA1 + " NOT NULL"; 2104 2105 public static final String COLUMNS[] = { 2106 Email._ID, 2107 Email.RAW_CONTACT_ID, 2108 Email.ADDRESS, 2109 }; 2110 2111 public static final int ID = 0; 2112 public static final int RAW_CONTACT_ID = 1; 2113 public static final int ADDRESS = 2; 2114 } 2115 2116 /** 2117 * Inserts name lookup rows for all email addresses in the database. 2118 */ 2119 private void insertEmailLookup(SQLiteDatabase db, SQLiteStatement nameLookupInsert) { 2120 final long mimeTypeId = lookupMimeTypeId(db, Email.CONTENT_ITEM_TYPE); 2121 Cursor cursor = db.query(EmailQuery.TABLE, EmailQuery.COLUMNS, 2122 EmailQuery.SELECTION, new String[] {String.valueOf(mimeTypeId)}, 2123 null, null, null); 2124 try { 2125 while (cursor.moveToNext()) { 2126 long dataId = cursor.getLong(EmailQuery.ID); 2127 long rawContactId = cursor.getLong(EmailQuery.RAW_CONTACT_ID); 2128 String address = cursor.getString(EmailQuery.ADDRESS); 2129 address = extractHandleFromEmailAddress(address); 2130 insertNameLookup(nameLookupInsert, rawContactId, dataId, 2131 NameLookupType.EMAIL_BASED_NICKNAME, address); 2132 } 2133 } finally { 2134 cursor.close(); 2135 } 2136 } 2137 2138 private static final class NicknameQuery { 2139 public static final String TABLE = Tables.DATA; 2140 2141 public static final String SELECTION = 2142 DataColumns.MIMETYPE_ID + "=? AND " + Data.DATA1 + " NOT NULL"; 2143 2144 public static final String COLUMNS[] = { 2145 Nickname._ID, 2146 Nickname.RAW_CONTACT_ID, 2147 Nickname.NAME, 2148 }; 2149 2150 public static final int ID = 0; 2151 public static final int RAW_CONTACT_ID = 1; 2152 public static final int NAME = 2; 2153 } 2154 2155 /** 2156 * Inserts name lookup rows for all nicknames in the database. 2157 */ 2158 private void insertNicknameLookup(SQLiteDatabase db, SQLiteStatement nameLookupInsert) { 2159 final long mimeTypeId = lookupMimeTypeId(db, Nickname.CONTENT_ITEM_TYPE); 2160 Cursor cursor = db.query(NicknameQuery.TABLE, NicknameQuery.COLUMNS, 2161 NicknameQuery.SELECTION, new String[] {String.valueOf(mimeTypeId)}, 2162 null, null, null); 2163 try { 2164 while (cursor.moveToNext()) { 2165 long dataId = cursor.getLong(NicknameQuery.ID); 2166 long rawContactId = cursor.getLong(NicknameQuery.RAW_CONTACT_ID); 2167 String nickname = cursor.getString(NicknameQuery.NAME); 2168 insertNameLookup(nameLookupInsert, rawContactId, dataId, 2169 NameLookupType.NICKNAME, nickname); 2170 } 2171 } finally { 2172 cursor.close(); 2173 } 2174 } 2175 2176 /** 2177 * Inserts a record in the {@link Tables#NAME_LOOKUP} table. 2178 */ 2179 public void insertNameLookup(SQLiteStatement stmt, long rawContactId, long dataId, 2180 int lookupType, String name) { 2181 if (TextUtils.isEmpty(name)) { 2182 return; 2183 } 2184 2185 String normalized = NameNormalizer.normalize(name); 2186 if (TextUtils.isEmpty(normalized)) { 2187 return; 2188 } 2189 2190 insertNormalizedNameLookup(stmt, rawContactId, dataId, lookupType, normalized); 2191 } 2192 2193 private void insertNormalizedNameLookup(SQLiteStatement stmt, long rawContactId, long dataId, 2194 int lookupType, String normalizedName) { 2195 stmt.bindLong(1, rawContactId); 2196 stmt.bindLong(2, dataId); 2197 stmt.bindLong(3, lookupType); 2198 stmt.bindString(4, normalizedName); 2199 stmt.executeInsert(); 2200 } 2201 2202 public String extractHandleFromEmailAddress(String email) { 2203 Rfc822Token[] tokens = Rfc822Tokenizer.tokenize(email); 2204 if (tokens.length == 0) { 2205 return null; 2206 } 2207 2208 String address = tokens[0].getAddress(); 2209 int at = address.indexOf('@'); 2210 if (at != -1) { 2211 return address.substring(0, at); 2212 } 2213 return null; 2214 } 2215 2216 private long lookupMimeTypeId(SQLiteDatabase db, String mimeType) { 2217 try { 2218 return DatabaseUtils.longForQuery(db, 2219 "SELECT " + MimetypesColumns._ID + 2220 " FROM " + Tables.MIMETYPES + 2221 " WHERE " + MimetypesColumns.MIMETYPE 2222 + "='" + mimeType + "'", null); 2223 } catch (SQLiteDoneException e) { 2224 // No rows of this type in the database 2225 return -1; 2226 } 2227 } 2228 2229 private void bindString(SQLiteStatement stmt, int index, String value) { 2230 if (value == null) { 2231 stmt.bindNull(index); 2232 } else { 2233 stmt.bindString(index, value); 2234 } 2235 } 2236 2237 /** 2238 * Adds index stats into the SQLite database to force it to always use the lookup indexes. 2239 */ 2240 private void updateSqliteStats(SQLiteDatabase db) { 2241 2242 // Specific stats strings are based on an actual large database after running ANALYZE 2243 try { 2244 updateIndexStats(db, Tables.CONTACTS, 2245 "contacts_restricted_index", "10000 9000"); 2246 updateIndexStats(db, Tables.CONTACTS, 2247 "contacts_has_phone_index", "10000 500"); 2248 updateIndexStats(db, Tables.CONTACTS, 2249 "contacts_visible_index", "10000 500 1"); 2250 2251 updateIndexStats(db, Tables.RAW_CONTACTS, 2252 "raw_contacts_source_id_index", "10000 1 1 1"); 2253 updateIndexStats(db, Tables.RAW_CONTACTS, 2254 "raw_contacts_contact_id_index", "10000 2"); 2255 2256 updateIndexStats(db, Tables.NAME_LOOKUP, 2257 "name_lookup_raw_contact_id_index", "10000 3"); 2258 updateIndexStats(db, Tables.NAME_LOOKUP, 2259 "name_lookup_index", "10000 3 2 2 1"); 2260 updateIndexStats(db, Tables.NAME_LOOKUP, 2261 "sqlite_autoindex_name_lookup_1", "10000 3 2 1"); 2262 2263 updateIndexStats(db, Tables.PHONE_LOOKUP, 2264 "phone_lookup_index", "10000 2 2 1"); 2265 updateIndexStats(db, Tables.PHONE_LOOKUP, 2266 "phone_lookup_min_match_index", "10000 2 2 1"); 2267 2268 updateIndexStats(db, Tables.DATA, 2269 "data_mimetype_data1_index", "60000 5000 2"); 2270 updateIndexStats(db, Tables.DATA, 2271 "data_raw_contact_id", "60000 10"); 2272 2273 updateIndexStats(db, Tables.GROUPS, 2274 "groups_source_id_index", "50 1 1 1"); 2275 2276 updateIndexStats(db, Tables.NICKNAME_LOOKUP, 2277 "sqlite_autoindex_name_lookup_1", "500 2 1"); 2278 2279 } catch (SQLException e) { 2280 Log.e(TAG, "Could not update index stats", e); 2281 } 2282 } 2283 2284 /** 2285 * Stores statistics for a given index. 2286 * 2287 * @param stats has the following structure: the first index is the expected size of 2288 * the table. The following integer(s) are the expected number of records selected with the 2289 * index. There should be one integer per indexed column. 2290 */ 2291 private void updateIndexStats(SQLiteDatabase db, String table, String index, 2292 String stats) { 2293 db.execSQL("DELETE FROM sqlite_stat1 WHERE tbl='" + table + "' AND idx='" + index + "';"); 2294 db.execSQL("INSERT INTO sqlite_stat1 (tbl,idx,stat)" 2295 + " VALUES ('" + table + "','" + index + "','" + stats + "');"); 2296 } 2297 2298 @Override 2299 public synchronized SQLiteDatabase getWritableDatabase() { 2300 SQLiteDatabase db = super.getWritableDatabase(); 2301 if (mReopenDatabase) { 2302 mReopenDatabase = false; 2303 close(); 2304 db = super.getWritableDatabase(); 2305 } 2306 return db; 2307 } 2308 2309 /** 2310 * Wipes all data except mime type and package lookup tables. 2311 */ 2312 public void wipeData() { 2313 SQLiteDatabase db = getWritableDatabase(); 2314 2315 db.execSQL("DELETE FROM " + Tables.CONTACTS + ";"); 2316 db.execSQL("DELETE FROM " + Tables.RAW_CONTACTS + ";"); 2317 db.execSQL("DELETE FROM " + Tables.DATA + ";"); 2318 db.execSQL("DELETE FROM " + Tables.PHONE_LOOKUP + ";"); 2319 db.execSQL("DELETE FROM " + Tables.NAME_LOOKUP + ";"); 2320 db.execSQL("DELETE FROM " + Tables.GROUPS + ";"); 2321 db.execSQL("DELETE FROM " + Tables.AGGREGATION_EXCEPTIONS + ";"); 2322 db.execSQL("DELETE FROM " + Tables.SETTINGS + ";"); 2323 db.execSQL("DELETE FROM " + Tables.ACTIVITIES + ";"); 2324 db.execSQL("DELETE FROM " + Tables.CALLS + ";"); 2325 2326 // Note: we are not removing reference data from Tables.NICKNAME_LOOKUP 2327 } 2328 2329 public NameSplitter createNameSplitter() { 2330 return new NameSplitter( 2331 mContext.getString(com.android.internal.R.string.common_name_prefixes), 2332 mContext.getString(com.android.internal.R.string.common_last_name_prefixes), 2333 mContext.getString(com.android.internal.R.string.common_name_suffixes), 2334 mContext.getString(com.android.internal.R.string.common_name_conjunctions), 2335 Locale.getDefault()); 2336 } 2337 2338 /** 2339 * Return the {@link ApplicationInfo#uid} for the given package name. 2340 */ 2341 public static int getUidForPackageName(PackageManager pm, String packageName) { 2342 try { 2343 ApplicationInfo clientInfo = pm.getApplicationInfo(packageName, 0 /* no flags */); 2344 return clientInfo.uid; 2345 } catch (NameNotFoundException e) { 2346 throw new RuntimeException(e); 2347 } 2348 } 2349 2350 /** 2351 * Perform an internal string-to-integer lookup using the compiled 2352 * {@link SQLiteStatement} provided, using the in-memory cache to speed up 2353 * lookups. If a mapping isn't found in cache or database, it will be 2354 * created. All new, uncached answers are added to the cache automatically. 2355 * 2356 * @param query Compiled statement used to query for the mapping. 2357 * @param insert Compiled statement used to insert a new mapping when no 2358 * existing one is found in cache or from query. 2359 * @param value Value to find mapping for. 2360 * @param cache In-memory cache of previous answers. 2361 * @return An unique integer mapping for the given value. 2362 */ 2363 private long getCachedId(SQLiteStatement query, SQLiteStatement insert, 2364 String value, HashMap<String, Long> cache) { 2365 // Try an in-memory cache lookup 2366 if (cache.containsKey(value)) { 2367 return cache.get(value); 2368 } 2369 2370 long id = -1; 2371 try { 2372 // Try searching database for mapping 2373 DatabaseUtils.bindObjectToProgram(query, 1, value); 2374 id = query.simpleQueryForLong(); 2375 } catch (SQLiteDoneException e) { 2376 // Nothing found, so try inserting new mapping 2377 DatabaseUtils.bindObjectToProgram(insert, 1, value); 2378 id = insert.executeInsert(); 2379 } 2380 2381 if (id != -1) { 2382 // Cache and return the new answer 2383 cache.put(value, id); 2384 return id; 2385 } else { 2386 // Otherwise throw if no mapping found or created 2387 throw new IllegalStateException("Couldn't find or create internal " 2388 + "lookup table entry for value " + value); 2389 } 2390 } 2391 2392 /** 2393 * Convert a package name into an integer, using {@link Tables#PACKAGES} for 2394 * lookups and possible allocation of new IDs as needed. 2395 */ 2396 public long getPackageId(String packageName) { 2397 // Make sure compiled statements are ready by opening database 2398 getReadableDatabase(); 2399 return getCachedId(mPackageQuery, mPackageInsert, packageName, mPackageCache); 2400 } 2401 2402 /** 2403 * Convert a mimetype into an integer, using {@link Tables#MIMETYPES} for 2404 * lookups and possible allocation of new IDs as needed. 2405 */ 2406 public long getMimeTypeId(String mimetype) { 2407 // Make sure compiled statements are ready by opening database 2408 getReadableDatabase(); 2409 return getMimeTypeIdNoDbCheck(mimetype); 2410 } 2411 2412 private long getMimeTypeIdNoDbCheck(String mimetype) { 2413 return getCachedId(mMimetypeQuery, mMimetypeInsert, mimetype, mMimetypeCache); 2414 } 2415 2416 /** 2417 * Find the mimetype for the given {@link Data#_ID}. 2418 */ 2419 public String getDataMimeType(long dataId) { 2420 // Make sure compiled statements are ready by opening database 2421 getReadableDatabase(); 2422 try { 2423 // Try database query to find mimetype 2424 DatabaseUtils.bindObjectToProgram(mDataMimetypeQuery, 1, dataId); 2425 String mimetype = mDataMimetypeQuery.simpleQueryForString(); 2426 return mimetype; 2427 } catch (SQLiteDoneException e) { 2428 // No valid mapping found, so return null 2429 return null; 2430 } 2431 } 2432 2433 /** 2434 * Find the mime-type for the given {@link Activities#_ID}. 2435 */ 2436 public String getActivityMimeType(long activityId) { 2437 // Make sure compiled statements are ready by opening database 2438 getReadableDatabase(); 2439 try { 2440 // Try database query to find mimetype 2441 DatabaseUtils.bindObjectToProgram(mActivitiesMimetypeQuery, 1, activityId); 2442 String mimetype = mActivitiesMimetypeQuery.simpleQueryForString(); 2443 return mimetype; 2444 } catch (SQLiteDoneException e) { 2445 // No valid mapping found, so return null 2446 return null; 2447 } 2448 } 2449 2450 /** 2451 * Update {@link Contacts#IN_VISIBLE_GROUP} for all contacts. 2452 */ 2453 public void updateAllVisible() { 2454 SQLiteDatabase db = getWritableDatabase(); 2455 final long groupMembershipMimetypeId = getMimeTypeId(GroupMembership.CONTENT_ITEM_TYPE); 2456 String[] selectionArgs = new String[]{String.valueOf(groupMembershipMimetypeId)}; 2457 2458 // There are a couple questions that can be asked regarding the 2459 // following two update statements: 2460 // 2461 // Q: Why do we run these two queries separately? They seem like they could be combined. 2462 // A: This is a result of painstaking experimentation. Turns out that the most 2463 // important optimization is to make sure we never update a value to its current value. 2464 // Changing 0 to 0 is unexpectedly expensive - SQLite actually writes the unchanged 2465 // rows back to disk. The other consideration is that the CONTACT_IS_VISIBLE condition 2466 // is very complex and executing it twice in the same statement ("if contact_visible != 2467 // CONTACT_IS_VISIBLE change it to CONTACT_IS_VISIBLE") is more expensive than running 2468 // two update statements. 2469 // 2470 // Q: How come we are using db.update instead of compiled statements? 2471 // A: This is a limitation of the compiled statement API. It does not return the 2472 // number of rows changed. As you will see later in this method we really need 2473 // to know how many rows have been changed. 2474 2475 // First update contacts that are currently marked as invisible, but need to be visible 2476 ContentValues values = new ContentValues(); 2477 values.put(Contacts.IN_VISIBLE_GROUP, 1); 2478 int countMadeVisible = db.update(Tables.CONTACTS, values, 2479 Contacts.IN_VISIBLE_GROUP + "=0" + " AND (" + Clauses.CONTACT_IS_VISIBLE + ")=1", 2480 selectionArgs); 2481 2482 // Next update contacts that are currently marked as visible, but need to be invisible 2483 values.put(Contacts.IN_VISIBLE_GROUP, 0); 2484 int countMadeInvisible = db.update(Tables.CONTACTS, values, 2485 Contacts.IN_VISIBLE_GROUP + "=1" + " AND (" + Clauses.CONTACT_IS_VISIBLE + ")=0", 2486 selectionArgs); 2487 2488 if (countMadeVisible != 0 || countMadeInvisible != 0) { 2489 // TODO break out the fields (contact_in_visible_group, sort_key, sort_key_alt) into 2490 // a separate table. 2491 // Rationale: The following statement will take a very long time on 2492 // a large database even though we are only changing one field from 0 to 1 or from 2493 // 1 to 0. The reason for the slowness is that SQLite will need to write the whole 2494 // page even when only one bit on it changes. Changing the visibility of a 2495 // significant number of contacts will likely read and write almost the entire 2496 // raw_contacts table. So, the solution is to break out into a separate table 2497 // the changing field along with the sort keys used for index-based sorting. 2498 // That table will occupy a smaller number of pages, so rewriting it would 2499 // not be as expensive. 2500 mVisibleUpdateRawContacts.execute(); 2501 } 2502 } 2503 2504 /** 2505 * Update {@link Contacts#IN_VISIBLE_GROUP} for a specific contact. 2506 */ 2507 public void updateContactVisible(long contactId) { 2508 final long groupMembershipMimetypeId = getMimeTypeId(GroupMembership.CONTENT_ITEM_TYPE); 2509 mVisibleSpecificUpdate.bindLong(1, groupMembershipMimetypeId); 2510 mVisibleSpecificUpdate.bindLong(2, contactId); 2511 mVisibleSpecificUpdate.execute(); 2512 2513 mVisibleSpecificUpdateRawContacts.bindLong(1, contactId); 2514 mVisibleSpecificUpdateRawContacts.execute(); 2515 } 2516 2517 /** 2518 * Returns contact ID for the given contact or zero if it is NULL. 2519 */ 2520 public long getContactId(long rawContactId) { 2521 getReadableDatabase(); 2522 try { 2523 DatabaseUtils.bindObjectToProgram(mContactIdQuery, 1, rawContactId); 2524 return mContactIdQuery.simpleQueryForLong(); 2525 } catch (SQLiteDoneException e) { 2526 // No valid mapping found, so return 0 2527 return 0; 2528 } 2529 } 2530 2531 public int getAggregationMode(long rawContactId) { 2532 getReadableDatabase(); 2533 try { 2534 DatabaseUtils.bindObjectToProgram(mAggregationModeQuery, 1, rawContactId); 2535 return (int)mAggregationModeQuery.simpleQueryForLong(); 2536 } catch (SQLiteDoneException e) { 2537 // No valid row found, so return "disabled" 2538 return RawContacts.AGGREGATION_MODE_DISABLED; 2539 } 2540 } 2541 2542 public void buildPhoneLookupAndRawContactQuery(SQLiteQueryBuilder qb, String number) { 2543 String minMatch = PhoneNumberUtils.toCallerIDMinMatch(number); 2544 qb.setTables(Tables.DATA_JOIN_RAW_CONTACTS + 2545 " JOIN " + Tables.PHONE_LOOKUP 2546 + " ON(" + DataColumns.CONCRETE_ID + "=" + PhoneLookupColumns.DATA_ID + ")"); 2547 2548 StringBuilder sb = new StringBuilder(); 2549 sb.append(PhoneLookupColumns.MIN_MATCH + "='"); 2550 sb.append(minMatch); 2551 sb.append("' AND PHONE_NUMBERS_EQUAL(data." + Phone.NUMBER + ", "); 2552 DatabaseUtils.appendEscapedSQLString(sb, number); 2553 sb.append(mUseStrictPhoneNumberComparison ? ", 1)" : ", 0)"); 2554 2555 qb.appendWhere(sb.toString()); 2556 } 2557 2558 public void buildPhoneLookupAndContactQuery(SQLiteQueryBuilder qb, String number) { 2559 String minMatch = PhoneNumberUtils.toCallerIDMinMatch(number); 2560 StringBuilder sb = new StringBuilder(); 2561 appendPhoneLookupTables(sb, minMatch, true); 2562 qb.setTables(sb.toString()); 2563 2564 sb = new StringBuilder(); 2565 appendPhoneLookupSelection(sb, number); 2566 qb.appendWhere(sb.toString()); 2567 } 2568 2569 public String buildPhoneLookupAsNestedQuery(String number) { 2570 StringBuilder sb = new StringBuilder(); 2571 final String minMatch = PhoneNumberUtils.toCallerIDMinMatch(number); 2572 sb.append("(SELECT DISTINCT raw_contact_id" + " FROM "); 2573 appendPhoneLookupTables(sb, minMatch, false); 2574 sb.append(" WHERE "); 2575 appendPhoneLookupSelection(sb, number); 2576 sb.append(")"); 2577 return sb.toString(); 2578 } 2579 2580 private void appendPhoneLookupTables(StringBuilder sb, final String minMatch, 2581 boolean joinContacts) { 2582 sb.append(Tables.RAW_CONTACTS); 2583 if (joinContacts) { 2584 sb.append(" JOIN " + getContactView() + " contacts_view" 2585 + " ON (contacts_view._id = raw_contacts.contact_id)"); 2586 } 2587 sb.append(", (SELECT data_id FROM phone_lookup " 2588 + "WHERE (" + Tables.PHONE_LOOKUP + "." + PhoneLookupColumns.MIN_MATCH + " = '"); 2589 sb.append(minMatch); 2590 sb.append("')) AS lookup, " + Tables.DATA); 2591 } 2592 2593 private void appendPhoneLookupSelection(StringBuilder sb, String number) { 2594 sb.append("lookup.data_id=data._id AND data.raw_contact_id=raw_contacts._id" 2595 + " AND PHONE_NUMBERS_EQUAL(data." + Phone.NUMBER + ", "); 2596 DatabaseUtils.appendEscapedSQLString(sb, number); 2597 sb.append(mUseStrictPhoneNumberComparison ? ", 1)" : ", 0)"); 2598 } 2599 2600 public String getUseStrictPhoneNumberComparisonParameter() { 2601 return mUseStrictPhoneNumberComparison ? "1" : "0"; 2602 } 2603 2604 /** 2605 * Loads common nickname mappings into the database. 2606 */ 2607 private void loadNicknameLookupTable(SQLiteDatabase db) { 2608 String[] strings = mContext.getResources().getStringArray( 2609 com.android.internal.R.array.common_nicknames); 2610 if (strings == null || strings.length == 0) { 2611 return; 2612 } 2613 2614 SQLiteStatement nicknameLookupInsert = db.compileStatement("INSERT INTO " 2615 + Tables.NICKNAME_LOOKUP + "(" + NicknameLookupColumns.NAME + "," 2616 + NicknameLookupColumns.CLUSTER + ") VALUES (?,?)"); 2617 2618 for (int clusterId = 0; clusterId < strings.length; clusterId++) { 2619 String[] names = strings[clusterId].split(","); 2620 for (int j = 0; j < names.length; j++) { 2621 String name = NameNormalizer.normalize(names[j]); 2622 try { 2623 DatabaseUtils.bindObjectToProgram(nicknameLookupInsert, 1, name); 2624 DatabaseUtils.bindObjectToProgram(nicknameLookupInsert, 2, 2625 String.valueOf(clusterId)); 2626 nicknameLookupInsert.executeInsert(); 2627 } catch (SQLiteException e) { 2628 2629 // Print the exception and keep going - this is not a fatal error 2630 Log.e(TAG, "Cannot insert nickname: " + names[j], e); 2631 } 2632 } 2633 } 2634 } 2635 2636 public static void copyStringValue(ContentValues toValues, String toKey, 2637 ContentValues fromValues, String fromKey) { 2638 if (fromValues.containsKey(fromKey)) { 2639 toValues.put(toKey, fromValues.getAsString(fromKey)); 2640 } 2641 } 2642 2643 public static void copyLongValue(ContentValues toValues, String toKey, 2644 ContentValues fromValues, String fromKey) { 2645 if (fromValues.containsKey(fromKey)) { 2646 long longValue; 2647 Object value = fromValues.get(fromKey); 2648 if (value instanceof Boolean) { 2649 if ((Boolean)value) { 2650 longValue = 1; 2651 } else { 2652 longValue = 0; 2653 } 2654 } else if (value instanceof String) { 2655 longValue = Long.parseLong((String)value); 2656 } else { 2657 longValue = ((Number)value).longValue(); 2658 } 2659 toValues.put(toKey, longValue); 2660 } 2661 } 2662 2663 public SyncStateContentProviderHelper getSyncState() { 2664 return mSyncState; 2665 } 2666 2667 /** 2668 * Delete the aggregate contact if it has no constituent raw contacts other 2669 * than the supplied one. 2670 */ 2671 public void removeContactIfSingleton(long rawContactId) { 2672 SQLiteDatabase db = getWritableDatabase(); 2673 2674 // Obtain contact ID from the supplied raw contact ID 2675 String contactIdFromRawContactId = "(SELECT " + RawContacts.CONTACT_ID + " FROM " 2676 + Tables.RAW_CONTACTS + " WHERE " + RawContacts._ID + "=" + rawContactId + ")"; 2677 2678 // Find other raw contacts in the same aggregate contact 2679 String otherRawContacts = "(SELECT contacts1." + RawContacts._ID + " FROM " 2680 + Tables.RAW_CONTACTS + " contacts1 JOIN " + Tables.RAW_CONTACTS + " contacts2 ON (" 2681 + "contacts1." + RawContacts.CONTACT_ID + "=contacts2." + RawContacts.CONTACT_ID 2682 + ") WHERE contacts1." + RawContacts._ID + "!=" + rawContactId + "" 2683 + " AND contacts2." + RawContacts._ID + "=" + rawContactId + ")"; 2684 2685 db.execSQL("DELETE FROM " + Tables.CONTACTS 2686 + " WHERE " + Contacts._ID + "=" + contactIdFromRawContactId 2687 + " AND NOT EXISTS " + otherRawContacts + ";"); 2688 } 2689 2690 /** 2691 * Check if {@link Binder#getCallingUid()} should be allowed access to 2692 * {@link RawContacts#IS_RESTRICTED} data. 2693 */ 2694 boolean hasAccessToRestrictedData() { 2695 final PackageManager pm = mContext.getPackageManager(); 2696 final String[] callerPackages = pm.getPackagesForUid(Binder.getCallingUid()); 2697 2698 // Has restricted access if caller matches any packages 2699 for (String callerPackage : callerPackages) { 2700 if (hasAccessToRestrictedData(callerPackage)) { 2701 return true; 2702 } 2703 } 2704 return false; 2705 } 2706 2707 /** 2708 * Check if requestingPackage should be allowed access to 2709 * {@link RawContacts#IS_RESTRICTED} data. 2710 */ 2711 boolean hasAccessToRestrictedData(String requestingPackage) { 2712 if (mUnrestrictedPackages != null) { 2713 for (String allowedPackage : mUnrestrictedPackages) { 2714 if (allowedPackage.equals(requestingPackage)) { 2715 return true; 2716 } 2717 } 2718 } 2719 return false; 2720 } 2721 2722 public String getDataView() { 2723 return getDataView(false); 2724 } 2725 2726 public String getDataView(boolean requireRestrictedView) { 2727 return (hasAccessToRestrictedData() && !requireRestrictedView) ? 2728 Views.DATA_ALL : Views.DATA_RESTRICTED; 2729 } 2730 2731 public String getRawContactView() { 2732 return getRawContactView(false); 2733 } 2734 2735 public String getRawContactView(boolean requireRestrictedView) { 2736 return (hasAccessToRestrictedData() && !requireRestrictedView) ? 2737 Views.RAW_CONTACTS_ALL : Views.RAW_CONTACTS_RESTRICTED; 2738 } 2739 2740 public String getContactView() { 2741 return getContactView(false); 2742 } 2743 2744 public String getContactView(boolean requireRestrictedView) { 2745 return (hasAccessToRestrictedData() && !requireRestrictedView) ? 2746 Views.CONTACTS_ALL : Views.CONTACTS_RESTRICTED; 2747 } 2748 2749 public String getGroupView() { 2750 return Views.GROUPS_ALL; 2751 } 2752 2753 public String getContactEntitiesView() { 2754 return getContactEntitiesView(false); 2755 } 2756 2757 public String getContactEntitiesView(boolean requireRestrictedView) { 2758 return (hasAccessToRestrictedData() && !requireRestrictedView) ? 2759 Tables.CONTACT_ENTITIES : Tables.CONTACT_ENTITIES_RESTRICTED; 2760 } 2761 2762 /** 2763 * Test if any of the columns appear in the given projection. 2764 */ 2765 public boolean isInProjection(String[] projection, String... columns) { 2766 if (projection == null) { 2767 return true; 2768 } 2769 2770 // Optimized for a single-column test 2771 if (columns.length == 1) { 2772 String column = columns[0]; 2773 for (String test : projection) { 2774 if (column.equals(test)) { 2775 return true; 2776 } 2777 } 2778 } else { 2779 for (String test : projection) { 2780 for (String column : columns) { 2781 if (column.equals(test)) { 2782 return true; 2783 } 2784 } 2785 } 2786 } 2787 return false; 2788 } 2789} 2790