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