ContactsDatabaseHelper.java revision 5df7e46835c4f103b05407660b4769edd515760f
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.common.content.SyncStateContentProviderHelper; 20import com.android.providers.contacts.ContactsDatabaseHelper.NameLookupType; 21 22import android.content.ContentResolver; 23import android.content.ContentValues; 24import android.content.Context; 25import android.content.pm.ApplicationInfo; 26import android.content.pm.PackageManager; 27import android.content.pm.PackageManager.NameNotFoundException; 28import android.content.res.Resources; 29import android.database.Cursor; 30import android.database.DatabaseUtils; 31import android.database.SQLException; 32import android.database.sqlite.SQLiteConstraintException; 33import android.database.sqlite.SQLiteDatabase; 34import android.database.sqlite.SQLiteDoneException; 35import android.database.sqlite.SQLiteException; 36import android.database.sqlite.SQLiteOpenHelper; 37import android.database.sqlite.SQLiteQueryBuilder; 38import android.database.sqlite.SQLiteStatement; 39import android.location.CountryDetector; 40import android.net.Uri; 41import android.os.Binder; 42import android.os.Bundle; 43import android.os.SystemClock; 44import android.provider.BaseColumns; 45import android.provider.CallLog.Calls; 46import android.provider.ContactsContract; 47import android.provider.ContactsContract.AggregationExceptions; 48import android.provider.ContactsContract.CommonDataKinds.Email; 49import android.provider.ContactsContract.CommonDataKinds.GroupMembership; 50import android.provider.ContactsContract.CommonDataKinds.Im; 51import android.provider.ContactsContract.CommonDataKinds.Nickname; 52import android.provider.ContactsContract.CommonDataKinds.Organization; 53import android.provider.ContactsContract.CommonDataKinds.Phone; 54import android.provider.ContactsContract.CommonDataKinds.StructuredName; 55import android.provider.ContactsContract.Contacts; 56import android.provider.ContactsContract.Contacts.Photo; 57import android.provider.ContactsContract.Data; 58import android.provider.ContactsContract.Directory; 59import android.provider.ContactsContract.DisplayNameSources; 60import android.provider.ContactsContract.FullNameStyle; 61import android.provider.ContactsContract.Groups; 62import android.provider.ContactsContract.RawContacts; 63import android.provider.ContactsContract.Settings; 64import android.provider.ContactsContract.StatusUpdates; 65import android.provider.SocialContract.Activities; 66import android.telephony.PhoneNumberUtils; 67import android.text.TextUtils; 68import android.text.util.Rfc822Token; 69import android.text.util.Rfc822Tokenizer; 70import android.util.Log; 71 72import java.util.HashMap; 73import java.util.Locale; 74 75/** 76 * Database helper for contacts. Designed as a singleton to make sure that all 77 * {@link android.content.ContentProvider} users get the same reference. 78 * Provides handy methods for maintaining package and mime-type lookup tables. 79 */ 80/* package */ class ContactsDatabaseHelper extends SQLiteOpenHelper { 81 private static final String TAG = "ContactsDatabaseHelper"; 82 83 /** 84 * Contacts DB version ranges: 85 * <pre> 86 * 0-98 Cupcake/Donut 87 * 100-199 Eclair 88 * 200-299 Eclair-MR1 89 * 300-349 Froyo 90 * 350-399 Gingerbread 91 * 400-499 Honeycomb 92 * </pre> 93 */ 94 static final int DATABASE_VERSION = 415; 95 96 private static final String DATABASE_NAME = "contacts2.db"; 97 private static final String DATABASE_PRESENCE = "presence_db"; 98 99 public interface Tables { 100 public static final String CONTACTS = "contacts"; 101 public static final String RAW_CONTACTS = "raw_contacts"; 102 public static final String PACKAGES = "packages"; 103 public static final String MIMETYPES = "mimetypes"; 104 public static final String PHONE_LOOKUP = "phone_lookup"; 105 public static final String NAME_LOOKUP = "name_lookup"; 106 public static final String AGGREGATION_EXCEPTIONS = "agg_exceptions"; 107 public static final String SETTINGS = "settings"; 108 public static final String DATA = "data"; 109 public static final String GROUPS = "groups"; 110 public static final String PRESENCE = "presence"; 111 public static final String AGGREGATED_PRESENCE = "agg_presence"; 112 public static final String NICKNAME_LOOKUP = "nickname_lookup"; 113 public static final String CALLS = "calls"; 114 public static final String STATUS_UPDATES = "status_updates"; 115 public static final String PROPERTIES = "properties"; 116 public static final String ACCOUNTS = "accounts"; 117 public static final String VISIBLE_CONTACTS = "visible_contacts"; 118 public static final String DIRECTORIES = "directories"; 119 public static final String DEFAULT_DIRECTORY = "default_directory"; 120 121 public static final String DATA_JOIN_MIMETYPES = "data " 122 + "JOIN mimetypes ON (data.mimetype_id = mimetypes._id)"; 123 124 public static final String DATA_JOIN_RAW_CONTACTS = "data " 125 + "JOIN raw_contacts ON (data.raw_contact_id = raw_contacts._id)"; 126 127 public static final String DATA_JOIN_MIMETYPE_RAW_CONTACTS = "data " 128 + "JOIN mimetypes ON (data.mimetype_id = mimetypes._id) " 129 + "JOIN raw_contacts ON (data.raw_contact_id = raw_contacts._id)"; 130 131 // NOTE: This requires late binding of GroupMembership MIME-type 132 public static final String RAW_CONTACTS_JOIN_SETTINGS_DATA_GROUPS = "raw_contacts " 133 + "LEFT OUTER JOIN settings ON (" 134 + "raw_contacts.account_name = settings.account_name AND " 135 + "raw_contacts.account_type = settings.account_type) " 136 + "LEFT OUTER JOIN data ON (data.mimetype_id=? AND " 137 + "data.raw_contact_id = raw_contacts._id) " 138 + "LEFT OUTER JOIN groups ON (groups._id = data." + GroupMembership.GROUP_ROW_ID 139 + ")"; 140 141 // NOTE: This requires late binding of GroupMembership MIME-type 142 public static final String SETTINGS_JOIN_RAW_CONTACTS_DATA_MIMETYPES_CONTACTS = "settings " 143 + "LEFT OUTER JOIN raw_contacts ON (" 144 + "raw_contacts.account_name = settings.account_name AND " 145 + "raw_contacts.account_type = settings.account_type) " 146 + "LEFT OUTER JOIN data ON (data.mimetype_id=? AND " 147 + "data.raw_contact_id = raw_contacts._id) " 148 + "LEFT OUTER JOIN contacts ON (raw_contacts.contact_id = contacts._id)"; 149 150 public static final String DATA_JOIN_MIMETYPES_RAW_CONTACTS_CONTACTS = "data " 151 + "JOIN mimetypes ON (data.mimetype_id = mimetypes._id) " 152 + "JOIN raw_contacts ON (data.raw_contact_id = raw_contacts._id) " 153 + "LEFT OUTER JOIN contacts ON (raw_contacts.contact_id = contacts._id)"; 154 155 public static final String DATA_JOIN_PACKAGES_MIMETYPES_RAW_CONTACTS_GROUPS = "data " 156 + "JOIN mimetypes ON (data.mimetype_id = mimetypes._id) " 157 + "JOIN raw_contacts ON (data.raw_contact_id = raw_contacts._id) " 158 + "LEFT OUTER JOIN packages ON (data.package_id = packages._id) " 159 + "LEFT OUTER JOIN groups " 160 + " ON (mimetypes.mimetype='" + GroupMembership.CONTENT_ITEM_TYPE + "' " 161 + " AND groups._id = data." + GroupMembership.GROUP_ROW_ID + ") "; 162 163 public static final String GROUPS_JOIN_PACKAGES = "groups " 164 + "LEFT OUTER JOIN packages ON (groups.package_id = packages._id)"; 165 166 167 public static final String ACTIVITIES = "activities"; 168 169 public static final String ACTIVITIES_JOIN_MIMETYPES = "activities " 170 + "LEFT OUTER JOIN mimetypes ON (activities.mimetype_id = mimetypes._id)"; 171 172 public static final String ACTIVITIES_JOIN_PACKAGES_MIMETYPES_RAW_CONTACTS_CONTACTS = 173 "activities " 174 + "LEFT OUTER JOIN packages ON (activities.package_id = packages._id) " 175 + "LEFT OUTER JOIN mimetypes ON (activities.mimetype_id = mimetypes._id) " 176 + "LEFT OUTER JOIN raw_contacts ON (activities.author_contact_id = " + 177 "raw_contacts._id) " 178 + "LEFT OUTER JOIN contacts ON (raw_contacts.contact_id = contacts._id)"; 179 180 public static final String NAME_LOOKUP_JOIN_RAW_CONTACTS = "name_lookup " 181 + "INNER JOIN raw_contacts ON (name_lookup.raw_contact_id = raw_contacts._id)"; 182 } 183 184 public interface Views { 185 public static final String DATA_ALL = "view_data"; 186 public static final String DATA_RESTRICTED = "view_data_restricted"; 187 188 public static final String RAW_CONTACTS_ALL = "view_raw_contacts"; 189 public static final String RAW_CONTACTS_RESTRICTED = "view_raw_contacts_restricted"; 190 191 public static final String CONTACTS_ALL = "view_contacts"; 192 public static final String CONTACTS_RESTRICTED = "view_contacts_restricted"; 193 194 public static final String ENTITIES = "view_entities"; 195 public static final String ENTITIES_RESTRICTED = "view_entities_restricted"; 196 197 public static final String RAW_ENTITIES = "view_raw_entities"; 198 public static final String RAW_ENTITIES_RESTRICTED = "view_raw_entities_restricted"; 199 200 public static final String GROUPS_ALL = "view_groups"; 201 } 202 203 public interface Clauses { 204 final String MIMETYPE_IS_GROUP_MEMBERSHIP = MimetypesColumns.CONCRETE_MIMETYPE + "='" 205 + GroupMembership.CONTENT_ITEM_TYPE + "'"; 206 207 final String BELONGS_TO_GROUP = DataColumns.CONCRETE_GROUP_ID + "=" 208 + GroupsColumns.CONCRETE_ID; 209 210 final String HAVING_NO_GROUPS = "COUNT(" + DataColumns.CONCRETE_GROUP_ID + ") == 0"; 211 212 final String GROUP_BY_ACCOUNT_CONTACT_ID = SettingsColumns.CONCRETE_ACCOUNT_NAME + "," 213 + SettingsColumns.CONCRETE_ACCOUNT_TYPE + "," + RawContacts.CONTACT_ID; 214 215 final String RAW_CONTACT_IS_LOCAL = RawContactsColumns.CONCRETE_ACCOUNT_NAME 216 + " IS NULL AND " + RawContactsColumns.CONCRETE_ACCOUNT_TYPE + " IS NULL"; 217 218 final String ZERO_GROUP_MEMBERSHIPS = "COUNT(" + GroupsColumns.CONCRETE_ID + ")=0"; 219 220 final String OUTER_RAW_CONTACTS = "outer_raw_contacts"; 221 final String OUTER_RAW_CONTACTS_ID = OUTER_RAW_CONTACTS + "." + RawContacts._ID; 222 223 final String CONTACT_IS_VISIBLE = 224 "SELECT " + 225 "MAX((SELECT (CASE WHEN " + 226 "(CASE" + 227 " WHEN " + RAW_CONTACT_IS_LOCAL + 228 " THEN 1 " + 229 " WHEN " + ZERO_GROUP_MEMBERSHIPS + 230 " THEN " + Settings.UNGROUPED_VISIBLE + 231 " ELSE MAX(" + Groups.GROUP_VISIBLE + ")" + 232 "END)=1 THEN 1 ELSE 0 END)" + 233 " FROM " + Tables.RAW_CONTACTS_JOIN_SETTINGS_DATA_GROUPS + 234 " WHERE " + RawContactsColumns.CONCRETE_ID + "=" + OUTER_RAW_CONTACTS_ID + "))" + 235 " FROM " + Tables.RAW_CONTACTS + " AS " + OUTER_RAW_CONTACTS + 236 " WHERE " + RawContacts.CONTACT_ID + "=" + ContactsColumns.CONCRETE_ID + 237 " GROUP BY " + RawContacts.CONTACT_ID; 238 239 final String GROUP_HAS_ACCOUNT_AND_SOURCE_ID = Groups.SOURCE_ID + "=? AND " 240 + Groups.ACCOUNT_NAME + "=? AND " + Groups.ACCOUNT_TYPE + "=?"; 241 242 public static final String CONTACT_VISIBLE = 243 "EXISTS (SELECT _id FROM " + Tables.VISIBLE_CONTACTS 244 + " WHERE " + Tables.CONTACTS +"." + Contacts._ID 245 + "=" + Tables.VISIBLE_CONTACTS +"." + Contacts._ID + ")"; 246 } 247 248 public interface ContactsColumns { 249 /** 250 * This flag is set for a contact if it has only one constituent raw contact and 251 * it is restricted. 252 */ 253 public static final String SINGLE_IS_RESTRICTED = "single_is_restricted"; 254 255 public static final String LAST_STATUS_UPDATE_ID = "status_update_id"; 256 257 public static final String CONCRETE_ID = Tables.CONTACTS + "." + BaseColumns._ID; 258 259 public static final String CONCRETE_TIMES_CONTACTED = Tables.CONTACTS + "." 260 + Contacts.TIMES_CONTACTED; 261 public static final String CONCRETE_LAST_TIME_CONTACTED = Tables.CONTACTS + "." 262 + Contacts.LAST_TIME_CONTACTED; 263 public static final String CONCRETE_STARRED = Tables.CONTACTS + "." + Contacts.STARRED; 264 public static final String CONCRETE_CUSTOM_RINGTONE = Tables.CONTACTS + "." 265 + Contacts.CUSTOM_RINGTONE; 266 public static final String CONCRETE_SEND_TO_VOICEMAIL = Tables.CONTACTS + "." 267 + Contacts.SEND_TO_VOICEMAIL; 268 public static final String CONCRETE_LOOKUP_KEY = Tables.CONTACTS + "." 269 + Contacts.LOOKUP_KEY; 270 } 271 272 public interface RawContactsColumns { 273 public static final String CONCRETE_ID = 274 Tables.RAW_CONTACTS + "." + BaseColumns._ID; 275 public static final String CONCRETE_ACCOUNT_NAME = 276 Tables.RAW_CONTACTS + "." + RawContacts.ACCOUNT_NAME; 277 public static final String CONCRETE_ACCOUNT_TYPE = 278 Tables.RAW_CONTACTS + "." + RawContacts.ACCOUNT_TYPE; 279 public static final String CONCRETE_SOURCE_ID = 280 Tables.RAW_CONTACTS + "." + RawContacts.SOURCE_ID; 281 public static final String CONCRETE_VERSION = 282 Tables.RAW_CONTACTS + "." + RawContacts.VERSION; 283 public static final String CONCRETE_DIRTY = 284 Tables.RAW_CONTACTS + "." + RawContacts.DIRTY; 285 public static final String CONCRETE_DELETED = 286 Tables.RAW_CONTACTS + "." + RawContacts.DELETED; 287 public static final String CONCRETE_SYNC1 = 288 Tables.RAW_CONTACTS + "." + RawContacts.SYNC1; 289 public static final String CONCRETE_SYNC2 = 290 Tables.RAW_CONTACTS + "." + RawContacts.SYNC2; 291 public static final String CONCRETE_SYNC3 = 292 Tables.RAW_CONTACTS + "." + RawContacts.SYNC3; 293 public static final String CONCRETE_SYNC4 = 294 Tables.RAW_CONTACTS + "." + RawContacts.SYNC4; 295 public static final String CONCRETE_STARRED = 296 Tables.RAW_CONTACTS + "." + RawContacts.STARRED; 297 public static final String CONCRETE_IS_RESTRICTED = 298 Tables.RAW_CONTACTS + "." + RawContacts.IS_RESTRICTED; 299 300 public static final String DISPLAY_NAME = RawContacts.DISPLAY_NAME_PRIMARY; 301 public static final String DISPLAY_NAME_SOURCE = RawContacts.DISPLAY_NAME_SOURCE; 302 public static final String AGGREGATION_NEEDED = "aggregation_needed"; 303 304 public static final String CONCRETE_DISPLAY_NAME = 305 Tables.RAW_CONTACTS + "." + DISPLAY_NAME; 306 public static final String CONCRETE_CONTACT_ID = 307 Tables.RAW_CONTACTS + "." + RawContacts.CONTACT_ID; 308 public static final String CONCRETE_NAME_VERIFIED = 309 Tables.RAW_CONTACTS + "." + RawContacts.NAME_VERIFIED; 310 } 311 312 public interface DataColumns { 313 public static final String PACKAGE_ID = "package_id"; 314 public static final String MIMETYPE_ID = "mimetype_id"; 315 316 public static final String CONCRETE_ID = Tables.DATA + "." + BaseColumns._ID; 317 public static final String CONCRETE_MIMETYPE_ID = Tables.DATA + "." + MIMETYPE_ID; 318 public static final String CONCRETE_RAW_CONTACT_ID = Tables.DATA + "." 319 + Data.RAW_CONTACT_ID; 320 public static final String CONCRETE_GROUP_ID = Tables.DATA + "." 321 + GroupMembership.GROUP_ROW_ID; 322 323 public static final String CONCRETE_DATA1 = Tables.DATA + "." + Data.DATA1; 324 public static final String CONCRETE_DATA2 = Tables.DATA + "." + Data.DATA2; 325 public static final String CONCRETE_DATA3 = Tables.DATA + "." + Data.DATA3; 326 public static final String CONCRETE_DATA4 = Tables.DATA + "." + Data.DATA4; 327 public static final String CONCRETE_DATA5 = Tables.DATA + "." + Data.DATA5; 328 public static final String CONCRETE_DATA6 = Tables.DATA + "." + Data.DATA6; 329 public static final String CONCRETE_DATA7 = Tables.DATA + "." + Data.DATA7; 330 public static final String CONCRETE_DATA8 = Tables.DATA + "." + Data.DATA8; 331 public static final String CONCRETE_DATA9 = Tables.DATA + "." + Data.DATA9; 332 public static final String CONCRETE_DATA10 = Tables.DATA + "." + Data.DATA10; 333 public static final String CONCRETE_DATA11 = Tables.DATA + "." + Data.DATA11; 334 public static final String CONCRETE_DATA12 = Tables.DATA + "." + Data.DATA12; 335 public static final String CONCRETE_DATA13 = Tables.DATA + "." + Data.DATA13; 336 public static final String CONCRETE_DATA14 = Tables.DATA + "." + Data.DATA14; 337 public static final String CONCRETE_DATA15 = Tables.DATA + "." + Data.DATA15; 338 public static final String CONCRETE_IS_PRIMARY = Tables.DATA + "." + Data.IS_PRIMARY; 339 public static final String CONCRETE_PACKAGE_ID = Tables.DATA + "." + PACKAGE_ID; 340 } 341 342 // Used only for legacy API support 343 public interface ExtensionsColumns { 344 public static final String NAME = Data.DATA1; 345 public static final String VALUE = Data.DATA2; 346 } 347 348 public interface GroupMembershipColumns { 349 public static final String RAW_CONTACT_ID = Data.RAW_CONTACT_ID; 350 public static final String GROUP_ROW_ID = GroupMembership.GROUP_ROW_ID; 351 } 352 353 public interface PhoneColumns { 354 public static final String NORMALIZED_NUMBER = Data.DATA4; 355 public static final String CONCRETE_NORMALIZED_NUMBER = DataColumns.CONCRETE_DATA4; 356 } 357 358 public interface GroupsColumns { 359 public static final String PACKAGE_ID = "package_id"; 360 361 public static final String CONCRETE_ID = Tables.GROUPS + "." + BaseColumns._ID; 362 public static final String CONCRETE_SOURCE_ID = Tables.GROUPS + "." + Groups.SOURCE_ID; 363 public static final String CONCRETE_ACCOUNT_NAME = Tables.GROUPS + "." + Groups.ACCOUNT_NAME; 364 public static final String CONCRETE_ACCOUNT_TYPE = Tables.GROUPS + "." + Groups.ACCOUNT_TYPE; 365 } 366 367 public interface ActivitiesColumns { 368 public static final String PACKAGE_ID = "package_id"; 369 public static final String MIMETYPE_ID = "mimetype_id"; 370 } 371 372 public interface PhoneLookupColumns { 373 public static final String _ID = BaseColumns._ID; 374 public static final String DATA_ID = "data_id"; 375 public static final String RAW_CONTACT_ID = "raw_contact_id"; 376 public static final String NORMALIZED_NUMBER = "normalized_number"; 377 public static final String MIN_MATCH = "min_match"; 378 } 379 380 public interface NameLookupColumns { 381 public static final String RAW_CONTACT_ID = "raw_contact_id"; 382 public static final String DATA_ID = "data_id"; 383 public static final String NORMALIZED_NAME = "normalized_name"; 384 public static final String NAME_TYPE = "name_type"; 385 } 386 387 public final static class NameLookupType { 388 public static final int NAME_EXACT = 0; 389 public static final int NAME_VARIANT = 1; 390 public static final int NAME_COLLATION_KEY = 2; 391 public static final int NICKNAME = 3; 392 public static final int EMAIL_BASED_NICKNAME = 4; 393 public static final int ORGANIZATION = 5; 394 public static final int NAME_SHORTHAND = 6; 395 public static final int NAME_CONSONANTS = 7; 396 397 // This is the highest name lookup type code plus one 398 public static final int TYPE_COUNT = 8; 399 400 public static boolean isBasedOnStructuredName(int nameLookupType) { 401 return nameLookupType == NameLookupType.NAME_EXACT 402 || nameLookupType == NameLookupType.NAME_VARIANT 403 || nameLookupType == NameLookupType.NAME_COLLATION_KEY; 404 } 405 } 406 407 public interface PackagesColumns { 408 public static final String _ID = BaseColumns._ID; 409 public static final String PACKAGE = "package"; 410 411 public static final String CONCRETE_ID = Tables.PACKAGES + "." + _ID; 412 } 413 414 public interface MimetypesColumns { 415 public static final String _ID = BaseColumns._ID; 416 public static final String MIMETYPE = "mimetype"; 417 418 public static final String CONCRETE_ID = Tables.MIMETYPES + "." + BaseColumns._ID; 419 public static final String CONCRETE_MIMETYPE = Tables.MIMETYPES + "." + MIMETYPE; 420 } 421 422 public interface AggregationExceptionColumns { 423 public static final String _ID = BaseColumns._ID; 424 } 425 426 public interface NicknameLookupColumns { 427 public static final String NAME = "name"; 428 public static final String CLUSTER = "cluster"; 429 } 430 431 public interface SettingsColumns { 432 public static final String CONCRETE_ACCOUNT_NAME = Tables.SETTINGS + "." 433 + Settings.ACCOUNT_NAME; 434 public static final String CONCRETE_ACCOUNT_TYPE = Tables.SETTINGS + "." 435 + Settings.ACCOUNT_TYPE; 436 } 437 438 public interface PresenceColumns { 439 String RAW_CONTACT_ID = "presence_raw_contact_id"; 440 String CONTACT_ID = "presence_contact_id"; 441 } 442 443 public interface AggregatedPresenceColumns { 444 String CONTACT_ID = "presence_contact_id"; 445 446 String CONCRETE_CONTACT_ID = Tables.AGGREGATED_PRESENCE + "." + CONTACT_ID; 447 } 448 449 public interface StatusUpdatesColumns { 450 String DATA_ID = "status_update_data_id"; 451 452 String CONCRETE_DATA_ID = Tables.STATUS_UPDATES + "." + DATA_ID; 453 454 String CONCRETE_PRESENCE = Tables.STATUS_UPDATES + "." + StatusUpdates.PRESENCE; 455 String CONCRETE_STATUS = Tables.STATUS_UPDATES + "." + StatusUpdates.STATUS; 456 String CONCRETE_STATUS_TIMESTAMP = Tables.STATUS_UPDATES + "." 457 + StatusUpdates.STATUS_TIMESTAMP; 458 String CONCRETE_STATUS_RES_PACKAGE = Tables.STATUS_UPDATES + "." 459 + StatusUpdates.STATUS_RES_PACKAGE; 460 String CONCRETE_STATUS_LABEL = Tables.STATUS_UPDATES + "." + StatusUpdates.STATUS_LABEL; 461 String CONCRETE_STATUS_ICON = Tables.STATUS_UPDATES + "." + StatusUpdates.STATUS_ICON; 462 } 463 464 public interface ContactsStatusUpdatesColumns { 465 String ALIAS = "contacts_" + Tables.STATUS_UPDATES; 466 467 String CONCRETE_DATA_ID = ALIAS + "." + StatusUpdatesColumns.DATA_ID; 468 469 String CONCRETE_PRESENCE = ALIAS + "." + StatusUpdates.PRESENCE; 470 String CONCRETE_STATUS = ALIAS + "." + StatusUpdates.STATUS; 471 String CONCRETE_STATUS_TIMESTAMP = ALIAS + "." + StatusUpdates.STATUS_TIMESTAMP; 472 String CONCRETE_STATUS_RES_PACKAGE = ALIAS + "." + StatusUpdates.STATUS_RES_PACKAGE; 473 String CONCRETE_STATUS_LABEL = ALIAS + "." + StatusUpdates.STATUS_LABEL; 474 String CONCRETE_STATUS_ICON = ALIAS + "." + StatusUpdates.STATUS_ICON; 475 } 476 477 public interface PropertiesColumns { 478 String PROPERTY_KEY = "property_key"; 479 String PROPERTY_VALUE = "property_value"; 480 } 481 482 public static final class DirectoryColumns { 483 public static final String TYPE_RESOURCE_NAME = "typeResourceName"; 484 } 485 486 /** In-memory cache of previously found MIME-type mappings */ 487 private final HashMap<String, Long> mMimetypeCache = new HashMap<String, Long>(); 488 /** In-memory cache of previously found package name mappings */ 489 private final HashMap<String, Long> mPackageCache = new HashMap<String, Long>(); 490 491 private long mMimeTypeIdEmail; 492 private long mMimeTypeIdIm; 493 private long mMimeTypeIdStructuredName; 494 private long mMimeTypeIdOrganization; 495 private long mMimeTypeIdNickname; 496 private long mMimeTypeIdPhone; 497 498 /** Compiled statements for querying and inserting mappings */ 499 private SQLiteStatement mMimetypeQuery; 500 private SQLiteStatement mPackageQuery; 501 private SQLiteStatement mContactIdQuery; 502 private SQLiteStatement mAggregationModeQuery; 503 private SQLiteStatement mMimetypeInsert; 504 private SQLiteStatement mPackageInsert; 505 private SQLiteStatement mDataMimetypeQuery; 506 private SQLiteStatement mActivitiesMimetypeQuery; 507 508 /** Precompiled sql statement for setting a data record to the primary. */ 509 private SQLiteStatement mSetPrimaryStatement; 510 /** Precompiled sql statement for setting a data record to the super primary. */ 511 private SQLiteStatement mSetSuperPrimaryStatement; 512 /** Precompiled sql statement for clearing super primary of a single record. */ 513 private SQLiteStatement mClearSuperPrimaryStatement; 514 /** Precompiled sql statement for updating a contact display name */ 515 private SQLiteStatement mRawContactDisplayNameUpdate; 516 517 private SQLiteStatement mNameLookupInsert; 518 private SQLiteStatement mNameLookupDelete; 519 private SQLiteStatement mStatusUpdateAutoTimestamp; 520 private SQLiteStatement mStatusUpdateInsert; 521 private SQLiteStatement mStatusUpdateReplace; 522 private SQLiteStatement mStatusAttributionUpdate; 523 private SQLiteStatement mStatusUpdateDelete; 524 private SQLiteStatement mResetNameVerifiedForOtherRawContacts; 525 526 private final Context mContext; 527 private final boolean mDatabaseOptimizationEnabled; 528 private final SyncStateContentProviderHelper mSyncState; 529 private final CountryMonitor mCountryMonitor; 530 private StringBuilder mSb = new StringBuilder(); 531 532 private boolean mReopenDatabase = false; 533 534 private static ContactsDatabaseHelper sSingleton = null; 535 536 private boolean mUseStrictPhoneNumberComparison; 537 538 /** 539 * List of package names with access to {@link RawContacts#IS_RESTRICTED} data. 540 */ 541 private String[] mUnrestrictedPackages; 542 543 public static synchronized ContactsDatabaseHelper getInstance(Context context) { 544 if (sSingleton == null) { 545 sSingleton = new ContactsDatabaseHelper(context, DATABASE_NAME, true); 546 } 547 return sSingleton; 548 } 549 550 /** 551 * Private constructor, callers except unit tests should obtain an instance through 552 * {@link #getInstance(android.content.Context)} instead. 553 */ 554 ContactsDatabaseHelper(Context context) { 555 this(context, null, false); 556 } 557 558 private ContactsDatabaseHelper( 559 Context context, String databaseName, boolean optimizationEnabled) { 560 super(context, databaseName, null, DATABASE_VERSION); 561 mDatabaseOptimizationEnabled = optimizationEnabled; 562 Resources resources = context.getResources(); 563 564 mContext = context; 565 mSyncState = new SyncStateContentProviderHelper(); 566 mCountryMonitor = new CountryMonitor(context); 567 mUseStrictPhoneNumberComparison = 568 resources.getBoolean( 569 com.android.internal.R.bool.config_use_strict_phone_number_comparation); 570 int resourceId = resources.getIdentifier("unrestricted_packages", "array", 571 context.getPackageName()); 572 if (resourceId != 0) { 573 mUnrestrictedPackages = resources.getStringArray(resourceId); 574 } else { 575 mUnrestrictedPackages = new String[0]; 576 } 577 } 578 579 private void refreshDatabaseCaches(SQLiteDatabase db) { 580 mStatusUpdateDelete = null; 581 mStatusUpdateReplace = null; 582 mStatusUpdateInsert = null; 583 mStatusUpdateAutoTimestamp = null; 584 mStatusAttributionUpdate = null; 585 mResetNameVerifiedForOtherRawContacts = null; 586 mRawContactDisplayNameUpdate = null; 587 mSetPrimaryStatement = null; 588 mClearSuperPrimaryStatement = null; 589 mSetSuperPrimaryStatement = null; 590 mNameLookupInsert = null; 591 mNameLookupDelete = null; 592 mPackageQuery = null; 593 mPackageInsert = null; 594 mDataMimetypeQuery = null; 595 mActivitiesMimetypeQuery = null; 596 mContactIdQuery = null; 597 mAggregationModeQuery = null; 598 599 mMimetypeCache.clear(); 600 mPackageCache.clear(); 601 602 mMimetypeQuery = db.compileStatement( 603 "SELECT " + MimetypesColumns._ID + 604 " FROM " + Tables.MIMETYPES + 605 " WHERE " + MimetypesColumns.MIMETYPE + "=?"); 606 607 mMimetypeInsert = db.compileStatement( 608 "INSERT INTO " + Tables.MIMETYPES + "(" 609 + MimetypesColumns.MIMETYPE + 610 ") VALUES (?)"); 611 612 mMimeTypeIdEmail = getMimeTypeId(Email.CONTENT_ITEM_TYPE); 613 mMimeTypeIdIm = getMimeTypeId(Im.CONTENT_ITEM_TYPE); 614 mMimeTypeIdStructuredName = getMimeTypeId(StructuredName.CONTENT_ITEM_TYPE); 615 mMimeTypeIdOrganization = getMimeTypeId(Organization.CONTENT_ITEM_TYPE); 616 mMimeTypeIdNickname = getMimeTypeId(Nickname.CONTENT_ITEM_TYPE); 617 mMimeTypeIdPhone = getMimeTypeId(Phone.CONTENT_ITEM_TYPE); 618 } 619 620 @Override 621 public void onOpen(SQLiteDatabase db) { 622 refreshDatabaseCaches(db); 623 624 mSyncState.onDatabaseOpened(db); 625 626 db.execSQL("ATTACH DATABASE ':memory:' AS " + DATABASE_PRESENCE + ";"); 627 db.execSQL("CREATE TABLE IF NOT EXISTS " + DATABASE_PRESENCE + "." + Tables.PRESENCE + " ("+ 628 StatusUpdates.DATA_ID + " INTEGER PRIMARY KEY REFERENCES data(_id)," + 629 StatusUpdates.PROTOCOL + " INTEGER NOT NULL," + 630 StatusUpdates.CUSTOM_PROTOCOL + " TEXT," + 631 StatusUpdates.IM_HANDLE + " TEXT," + 632 StatusUpdates.IM_ACCOUNT + " TEXT," + 633 PresenceColumns.CONTACT_ID + " INTEGER REFERENCES contacts(_id)," + 634 PresenceColumns.RAW_CONTACT_ID + " INTEGER REFERENCES raw_contacts(_id)," + 635 StatusUpdates.PRESENCE + " INTEGER," + 636 StatusUpdates.CHAT_CAPABILITY + " INTEGER NOT NULL DEFAULT 0," + 637 "UNIQUE(" + StatusUpdates.PROTOCOL + ", " + StatusUpdates.CUSTOM_PROTOCOL 638 + ", " + StatusUpdates.IM_HANDLE + ", " + StatusUpdates.IM_ACCOUNT + ")" + 639 ");"); 640 641 db.execSQL("CREATE INDEX IF NOT EXISTS " + DATABASE_PRESENCE + ".presenceIndex" + " ON " 642 + Tables.PRESENCE + " (" + PresenceColumns.RAW_CONTACT_ID + ");"); 643 db.execSQL("CREATE INDEX IF NOT EXISTS " + DATABASE_PRESENCE + ".presenceIndex2" + " ON " 644 + Tables.PRESENCE + " (" + PresenceColumns.CONTACT_ID + ");"); 645 646 db.execSQL("CREATE TABLE IF NOT EXISTS " 647 + DATABASE_PRESENCE + "." + Tables.AGGREGATED_PRESENCE + " ("+ 648 AggregatedPresenceColumns.CONTACT_ID 649 + " INTEGER PRIMARY KEY REFERENCES contacts(_id)," + 650 StatusUpdates.PRESENCE + " INTEGER," + 651 StatusUpdates.CHAT_CAPABILITY + " INTEGER NOT NULL DEFAULT 0" + 652 ");"); 653 654 655 db.execSQL("CREATE TRIGGER " + DATABASE_PRESENCE + "." + Tables.PRESENCE + "_deleted" 656 + " BEFORE DELETE ON " + DATABASE_PRESENCE + "." + Tables.PRESENCE 657 + " BEGIN " 658 + " DELETE FROM " + Tables.AGGREGATED_PRESENCE 659 + " WHERE " + AggregatedPresenceColumns.CONTACT_ID + " = " + 660 "(SELECT " + PresenceColumns.CONTACT_ID + 661 " FROM " + Tables.PRESENCE + 662 " WHERE " + PresenceColumns.RAW_CONTACT_ID 663 + "=OLD." + PresenceColumns.RAW_CONTACT_ID + 664 " AND NOT EXISTS" + 665 "(SELECT " + PresenceColumns.RAW_CONTACT_ID + 666 " FROM " + Tables.PRESENCE + 667 " WHERE " + PresenceColumns.CONTACT_ID 668 + "=OLD." + PresenceColumns.CONTACT_ID + 669 " AND " + PresenceColumns.RAW_CONTACT_ID 670 + "!=OLD." + PresenceColumns.RAW_CONTACT_ID + "));" 671 + " END"); 672 673 final String replaceAggregatePresenceSql = 674 "INSERT OR REPLACE INTO " + Tables.AGGREGATED_PRESENCE + "(" 675 + AggregatedPresenceColumns.CONTACT_ID + ", " 676 + StatusUpdates.PRESENCE + ", " 677 + StatusUpdates.CHAT_CAPABILITY + ")" 678 + " SELECT " 679 + PresenceColumns.CONTACT_ID + "," 680 + StatusUpdates.PRESENCE + "," 681 + StatusUpdates.CHAT_CAPABILITY 682 + " FROM " + Tables.PRESENCE 683 + " WHERE " 684 + " (ifnull(" + StatusUpdates.PRESENCE + ",0) * 10 " 685 + "+ ifnull(" + StatusUpdates.CHAT_CAPABILITY + ", 0))" 686 + " = (SELECT " 687 + "MAX (ifnull(" + StatusUpdates.PRESENCE + ",0) * 10 " 688 + "+ ifnull(" + StatusUpdates.CHAT_CAPABILITY + ", 0))" 689 + " FROM " + Tables.PRESENCE 690 + " WHERE " + PresenceColumns.CONTACT_ID 691 + "=NEW." + PresenceColumns.CONTACT_ID 692 + ")" 693 + " AND " + PresenceColumns.CONTACT_ID + "=NEW." + PresenceColumns.CONTACT_ID + ";"; 694 695 db.execSQL("CREATE TRIGGER " + DATABASE_PRESENCE + "." + Tables.PRESENCE + "_inserted" 696 + " AFTER INSERT ON " + DATABASE_PRESENCE + "." + Tables.PRESENCE 697 + " BEGIN " 698 + replaceAggregatePresenceSql 699 + " END"); 700 701 db.execSQL("CREATE TRIGGER " + DATABASE_PRESENCE + "." + Tables.PRESENCE + "_updated" 702 + " AFTER UPDATE ON " + DATABASE_PRESENCE + "." + Tables.PRESENCE 703 + " BEGIN " 704 + replaceAggregatePresenceSql 705 + " END"); 706 } 707 708 @Override 709 public void onCreate(SQLiteDatabase db) { 710 Log.i(TAG, "Bootstrapping database"); 711 712 mSyncState.createDatabase(db); 713 714 // One row per group of contacts corresponding to the same person 715 db.execSQL("CREATE TABLE " + Tables.CONTACTS + " (" + 716 BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 717 Contacts.NAME_RAW_CONTACT_ID + " INTEGER REFERENCES raw_contacts(_id)," + 718 Contacts.PHOTO_ID + " INTEGER REFERENCES data(_id)," + 719 Contacts.CUSTOM_RINGTONE + " TEXT," + 720 Contacts.SEND_TO_VOICEMAIL + " INTEGER NOT NULL DEFAULT 0," + 721 Contacts.TIMES_CONTACTED + " INTEGER NOT NULL DEFAULT 0," + 722 Contacts.LAST_TIME_CONTACTED + " INTEGER," + 723 Contacts.STARRED + " INTEGER NOT NULL DEFAULT 0," + 724 Contacts.HAS_PHONE_NUMBER + " INTEGER NOT NULL DEFAULT 0," + 725 Contacts.LOOKUP_KEY + " TEXT," + 726 ContactsColumns.LAST_STATUS_UPDATE_ID + " INTEGER REFERENCES data(_id)," + 727 ContactsColumns.SINGLE_IS_RESTRICTED + " INTEGER NOT NULL DEFAULT 0" + 728 ");"); 729 730 db.execSQL("CREATE INDEX contacts_has_phone_index ON " + Tables.CONTACTS + " (" + 731 Contacts.HAS_PHONE_NUMBER + 732 ");"); 733 734 db.execSQL("CREATE INDEX contacts_restricted_index ON " + Tables.CONTACTS + " (" + 735 ContactsColumns.SINGLE_IS_RESTRICTED + 736 ");"); 737 738 db.execSQL("CREATE INDEX contacts_name_raw_contact_id_index ON " + Tables.CONTACTS + " (" + 739 Contacts.NAME_RAW_CONTACT_ID + 740 ");"); 741 742 // Contacts table 743 db.execSQL("CREATE TABLE " + Tables.RAW_CONTACTS + " (" + 744 RawContacts._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 745 RawContacts.IS_RESTRICTED + " INTEGER DEFAULT 0," + 746 RawContacts.ACCOUNT_NAME + " STRING DEFAULT NULL, " + 747 RawContacts.ACCOUNT_TYPE + " STRING DEFAULT NULL, " + 748 RawContacts.SOURCE_ID + " TEXT," + 749 RawContacts.RAW_CONTACT_IS_READ_ONLY + " INTEGER NOT NULL DEFAULT 0," + 750 RawContacts.VERSION + " INTEGER NOT NULL DEFAULT 1," + 751 RawContacts.DIRTY + " INTEGER NOT NULL DEFAULT 0," + 752 RawContacts.DELETED + " INTEGER NOT NULL DEFAULT 0," + 753 RawContacts.CONTACT_ID + " INTEGER REFERENCES contacts(_id)," + 754 RawContacts.AGGREGATION_MODE + " INTEGER NOT NULL DEFAULT " + 755 RawContacts.AGGREGATION_MODE_DEFAULT + "," + 756 RawContactsColumns.AGGREGATION_NEEDED + " INTEGER NOT NULL DEFAULT 1," + 757 RawContacts.CUSTOM_RINGTONE + " TEXT," + 758 RawContacts.SEND_TO_VOICEMAIL + " INTEGER NOT NULL DEFAULT 0," + 759 RawContacts.TIMES_CONTACTED + " INTEGER NOT NULL DEFAULT 0," + 760 RawContacts.LAST_TIME_CONTACTED + " INTEGER," + 761 RawContacts.STARRED + " INTEGER NOT NULL DEFAULT 0," + 762 RawContacts.DISPLAY_NAME_PRIMARY + " TEXT," + 763 RawContacts.DISPLAY_NAME_ALTERNATIVE + " TEXT," + 764 RawContacts.DISPLAY_NAME_SOURCE + " INTEGER NOT NULL DEFAULT " + 765 DisplayNameSources.UNDEFINED + "," + 766 RawContacts.PHONETIC_NAME + " TEXT," + 767 RawContacts.PHONETIC_NAME_STYLE + " TEXT," + 768 RawContacts.SORT_KEY_PRIMARY + " TEXT COLLATE " + 769 ContactsProvider2.PHONEBOOK_COLLATOR_NAME + "," + 770 RawContacts.SORT_KEY_ALTERNATIVE + " TEXT COLLATE " + 771 ContactsProvider2.PHONEBOOK_COLLATOR_NAME + "," + 772 RawContacts.NAME_VERIFIED + " INTEGER NOT NULL DEFAULT 0," + 773 RawContacts.SYNC1 + " TEXT, " + 774 RawContacts.SYNC2 + " TEXT, " + 775 RawContacts.SYNC3 + " TEXT, " + 776 RawContacts.SYNC4 + " TEXT " + 777 ");"); 778 779 db.execSQL("CREATE INDEX raw_contacts_contact_id_index ON " + Tables.RAW_CONTACTS + " (" + 780 RawContacts.CONTACT_ID + 781 ");"); 782 783 db.execSQL("CREATE INDEX raw_contacts_source_id_index ON " + Tables.RAW_CONTACTS + " (" + 784 RawContacts.SOURCE_ID + ", " + 785 RawContacts.ACCOUNT_TYPE + ", " + 786 RawContacts.ACCOUNT_NAME + 787 ");"); 788 789 // TODO readd the index and investigate a controlled use of it 790// db.execSQL("CREATE INDEX raw_contacts_agg_index ON " + Tables.RAW_CONTACTS + " (" + 791// RawContactsColumns.AGGREGATION_NEEDED + 792// ");"); 793 794 // Package name mapping table 795 db.execSQL("CREATE TABLE " + Tables.PACKAGES + " (" + 796 PackagesColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 797 PackagesColumns.PACKAGE + " TEXT NOT NULL" + 798 ");"); 799 800 // Mimetype mapping table 801 db.execSQL("CREATE TABLE " + Tables.MIMETYPES + " (" + 802 MimetypesColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 803 MimetypesColumns.MIMETYPE + " TEXT NOT NULL" + 804 ");"); 805 806 // Mimetype table requires an index on mime type 807 db.execSQL("CREATE UNIQUE INDEX mime_type ON " + Tables.MIMETYPES + " (" + 808 MimetypesColumns.MIMETYPE + 809 ");"); 810 811 // Public generic data table 812 db.execSQL("CREATE TABLE " + Tables.DATA + " (" + 813 Data._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 814 DataColumns.PACKAGE_ID + " INTEGER REFERENCES package(_id)," + 815 DataColumns.MIMETYPE_ID + " INTEGER REFERENCES mimetype(_id) NOT NULL," + 816 Data.RAW_CONTACT_ID + " INTEGER REFERENCES raw_contacts(_id) NOT NULL," + 817 Data.IS_READ_ONLY + " INTEGER NOT NULL DEFAULT 0," + 818 Data.IS_PRIMARY + " INTEGER NOT NULL DEFAULT 0," + 819 Data.IS_SUPER_PRIMARY + " INTEGER NOT NULL DEFAULT 0," + 820 Data.DATA_VERSION + " INTEGER NOT NULL DEFAULT 0," + 821 Data.DATA1 + " TEXT," + 822 Data.DATA2 + " TEXT," + 823 Data.DATA3 + " TEXT," + 824 Data.DATA4 + " TEXT," + 825 Data.DATA5 + " TEXT," + 826 Data.DATA6 + " TEXT," + 827 Data.DATA7 + " TEXT," + 828 Data.DATA8 + " TEXT," + 829 Data.DATA9 + " TEXT," + 830 Data.DATA10 + " TEXT," + 831 Data.DATA11 + " TEXT," + 832 Data.DATA12 + " TEXT," + 833 Data.DATA13 + " TEXT," + 834 Data.DATA14 + " TEXT," + 835 Data.DATA15 + " TEXT," + 836 Data.SYNC1 + " TEXT, " + 837 Data.SYNC2 + " TEXT, " + 838 Data.SYNC3 + " TEXT, " + 839 Data.SYNC4 + " TEXT " + 840 ");"); 841 842 db.execSQL("CREATE INDEX data_raw_contact_id ON " + Tables.DATA + " (" + 843 Data.RAW_CONTACT_ID + 844 ");"); 845 846 /** 847 * For email lookup and similar queries. 848 */ 849 db.execSQL("CREATE INDEX data_mimetype_data1_index ON " + Tables.DATA + " (" + 850 DataColumns.MIMETYPE_ID + "," + 851 Data.DATA1 + 852 ");"); 853 854 // Private phone numbers table used for lookup 855 db.execSQL("CREATE TABLE " + Tables.PHONE_LOOKUP + " (" + 856 PhoneLookupColumns.DATA_ID 857 + " INTEGER REFERENCES data(_id) NOT NULL," + 858 PhoneLookupColumns.RAW_CONTACT_ID 859 + " INTEGER REFERENCES raw_contacts(_id) NOT NULL," + 860 PhoneLookupColumns.NORMALIZED_NUMBER + " TEXT NOT NULL," + 861 PhoneLookupColumns.MIN_MATCH + " TEXT NOT NULL" + 862 ");"); 863 864 db.execSQL("CREATE INDEX phone_lookup_index ON " + Tables.PHONE_LOOKUP + " (" + 865 PhoneLookupColumns.NORMALIZED_NUMBER + "," + 866 PhoneLookupColumns.RAW_CONTACT_ID + "," + 867 PhoneLookupColumns.DATA_ID + 868 ");"); 869 870 db.execSQL("CREATE INDEX phone_lookup_min_match_index ON " + Tables.PHONE_LOOKUP + " (" + 871 PhoneLookupColumns.MIN_MATCH + "," + 872 PhoneLookupColumns.RAW_CONTACT_ID + "," + 873 PhoneLookupColumns.DATA_ID + 874 ");"); 875 876 // Private name/nickname table used for lookup 877 db.execSQL("CREATE TABLE " + Tables.NAME_LOOKUP + " (" + 878 NameLookupColumns.DATA_ID 879 + " INTEGER REFERENCES data(_id) NOT NULL," + 880 NameLookupColumns.RAW_CONTACT_ID 881 + " INTEGER REFERENCES raw_contacts(_id) NOT NULL," + 882 NameLookupColumns.NORMALIZED_NAME + " TEXT NOT NULL," + 883 NameLookupColumns.NAME_TYPE + " INTEGER NOT NULL," + 884 "PRIMARY KEY (" 885 + NameLookupColumns.DATA_ID + ", " 886 + NameLookupColumns.NORMALIZED_NAME + ", " 887 + NameLookupColumns.NAME_TYPE + ")" + 888 ");"); 889 890 db.execSQL("CREATE INDEX name_lookup_raw_contact_id_index ON " + Tables.NAME_LOOKUP + " (" + 891 NameLookupColumns.RAW_CONTACT_ID + 892 ");"); 893 894 db.execSQL("CREATE TABLE " + Tables.NICKNAME_LOOKUP + " (" + 895 NicknameLookupColumns.NAME + " TEXT," + 896 NicknameLookupColumns.CLUSTER + " TEXT" + 897 ");"); 898 899 db.execSQL("CREATE UNIQUE INDEX nickname_lookup_index ON " + Tables.NICKNAME_LOOKUP + " (" + 900 NicknameLookupColumns.NAME + ", " + 901 NicknameLookupColumns.CLUSTER + 902 ");"); 903 904 // Groups table 905 db.execSQL("CREATE TABLE " + Tables.GROUPS + " (" + 906 Groups._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 907 GroupsColumns.PACKAGE_ID + " INTEGER REFERENCES package(_id)," + 908 Groups.ACCOUNT_NAME + " STRING DEFAULT NULL, " + 909 Groups.ACCOUNT_TYPE + " STRING DEFAULT NULL, " + 910 Groups.SOURCE_ID + " TEXT," + 911 Groups.VERSION + " INTEGER NOT NULL DEFAULT 1," + 912 Groups.DIRTY + " INTEGER NOT NULL DEFAULT 0," + 913 Groups.TITLE + " TEXT," + 914 Groups.TITLE_RES + " INTEGER," + 915 Groups.NOTES + " TEXT," + 916 Groups.SYSTEM_ID + " TEXT," + 917 Groups.DELETED + " INTEGER NOT NULL DEFAULT 0," + 918 Groups.GROUP_VISIBLE + " INTEGER NOT NULL DEFAULT 0," + 919 Groups.SHOULD_SYNC + " INTEGER NOT NULL DEFAULT 1," + 920 Groups.AUTO_ADD + " INTEGER NOT NULL DEFAULT 0," + 921 Groups.FAVORITES + " INTEGER NOT NULL DEFAULT 0," + 922 Groups.GROUP_IS_READ_ONLY + " INTEGER NOT NULL DEFAULT 0," + 923 Groups.SYNC1 + " TEXT, " + 924 Groups.SYNC2 + " TEXT, " + 925 Groups.SYNC3 + " TEXT, " + 926 Groups.SYNC4 + " TEXT " + 927 ");"); 928 929 db.execSQL("CREATE INDEX groups_source_id_index ON " + Tables.GROUPS + " (" + 930 Groups.SOURCE_ID + ", " + 931 Groups.ACCOUNT_TYPE + ", " + 932 Groups.ACCOUNT_NAME + 933 ");"); 934 935 db.execSQL("CREATE TABLE IF NOT EXISTS " + Tables.AGGREGATION_EXCEPTIONS + " (" + 936 AggregationExceptionColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 937 AggregationExceptions.TYPE + " INTEGER NOT NULL, " + 938 AggregationExceptions.RAW_CONTACT_ID1 939 + " INTEGER REFERENCES raw_contacts(_id), " + 940 AggregationExceptions.RAW_CONTACT_ID2 941 + " INTEGER REFERENCES raw_contacts(_id)" + 942 ");"); 943 944 db.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS aggregation_exception_index1 ON " + 945 Tables.AGGREGATION_EXCEPTIONS + " (" + 946 AggregationExceptions.RAW_CONTACT_ID1 + ", " + 947 AggregationExceptions.RAW_CONTACT_ID2 + 948 ");"); 949 950 db.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS aggregation_exception_index2 ON " + 951 Tables.AGGREGATION_EXCEPTIONS + " (" + 952 AggregationExceptions.RAW_CONTACT_ID2 + ", " + 953 AggregationExceptions.RAW_CONTACT_ID1 + 954 ");"); 955 956 db.execSQL("CREATE TABLE IF NOT EXISTS " + Tables.SETTINGS + " (" + 957 Settings.ACCOUNT_NAME + " STRING NOT NULL," + 958 Settings.ACCOUNT_TYPE + " STRING NOT NULL," + 959 Settings.UNGROUPED_VISIBLE + " INTEGER NOT NULL DEFAULT 0," + 960 Settings.SHOULD_SYNC + " INTEGER NOT NULL DEFAULT 1, " + 961 "PRIMARY KEY (" + Settings.ACCOUNT_NAME + ", " + 962 Settings.ACCOUNT_TYPE + ") ON CONFLICT REPLACE" + 963 ");"); 964 965 db.execSQL("CREATE TABLE " + Tables.VISIBLE_CONTACTS + " (" + 966 Contacts._ID + " INTEGER PRIMARY KEY" + 967 ");"); 968 969 db.execSQL("CREATE TABLE " + Tables.DEFAULT_DIRECTORY + " (" + 970 Contacts._ID + " INTEGER PRIMARY KEY" + 971 ");"); 972 973 // The table for recent calls is here so we can do table joins 974 // on people, phones, and calls all in one place. 975 db.execSQL("CREATE TABLE " + Tables.CALLS + " (" + 976 Calls._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 977 Calls.NUMBER + " TEXT," + 978 Calls.DATE + " INTEGER," + 979 Calls.DURATION + " INTEGER," + 980 Calls.TYPE + " INTEGER," + 981 Calls.NEW + " INTEGER," + 982 Calls.CACHED_NAME + " TEXT," + 983 Calls.CACHED_NUMBER_TYPE + " INTEGER," + 984 Calls.CACHED_NUMBER_LABEL + " TEXT," + 985 Calls.COUNTRY_ISO + " TEXT" + ");"); 986 987 // Activities table 988 db.execSQL("CREATE TABLE " + Tables.ACTIVITIES + " (" + 989 Activities._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 990 ActivitiesColumns.PACKAGE_ID + " INTEGER REFERENCES package(_id)," + 991 ActivitiesColumns.MIMETYPE_ID + " INTEGER REFERENCES mimetype(_id) NOT NULL," + 992 Activities.RAW_ID + " TEXT," + 993 Activities.IN_REPLY_TO + " TEXT," + 994 Activities.AUTHOR_CONTACT_ID + " INTEGER REFERENCES raw_contacts(_id)," + 995 Activities.TARGET_CONTACT_ID + " INTEGER REFERENCES raw_contacts(_id)," + 996 Activities.PUBLISHED + " INTEGER NOT NULL," + 997 Activities.THREAD_PUBLISHED + " INTEGER NOT NULL," + 998 Activities.TITLE + " TEXT NOT NULL," + 999 Activities.SUMMARY + " TEXT," + 1000 Activities.LINK + " TEXT, " + 1001 Activities.THUMBNAIL + " BLOB" + 1002 ");"); 1003 1004 db.execSQL("CREATE TABLE " + Tables.STATUS_UPDATES + " (" + 1005 StatusUpdatesColumns.DATA_ID + " INTEGER PRIMARY KEY REFERENCES data(_id)," + 1006 StatusUpdates.STATUS + " TEXT," + 1007 StatusUpdates.STATUS_TIMESTAMP + " INTEGER," + 1008 StatusUpdates.STATUS_RES_PACKAGE + " TEXT, " + 1009 StatusUpdates.STATUS_LABEL + " INTEGER, " + 1010 StatusUpdates.STATUS_ICON + " INTEGER" + 1011 ");"); 1012 1013 db.execSQL("CREATE TABLE " + Tables.PROPERTIES + " (" + 1014 PropertiesColumns.PROPERTY_KEY + " TEXT PRIMARY KEY, " + 1015 PropertiesColumns.PROPERTY_VALUE + " TEXT " + 1016 ");"); 1017 1018 db.execSQL("CREATE TABLE " + Tables.ACCOUNTS + " (" + 1019 RawContacts.ACCOUNT_NAME + " TEXT, " + 1020 RawContacts.ACCOUNT_TYPE + " TEXT " + 1021 ");"); 1022 1023 // Allow contacts without any account to be created for now. Achieve that 1024 // by inserting a fake account with both type and name as NULL. 1025 // This "account" should be eliminated as soon as the first real writable account 1026 // is added to the phone. 1027 db.execSQL("INSERT INTO accounts VALUES(NULL, NULL)"); 1028 1029 createDirectoriesTable(db); 1030 1031 createContactsViews(db); 1032 createGroupsView(db); 1033 createContactsTriggers(db); 1034 createContactsIndexes(db); 1035 1036 loadNicknameLookupTable(db); 1037 1038 // Add the legacy API support views, etc 1039 LegacyApiSupport.createDatabase(db); 1040 1041 if (mDatabaseOptimizationEnabled) { 1042 // This will create a sqlite_stat1 table that is used for query optimization 1043 db.execSQL("ANALYZE;"); 1044 1045 updateSqliteStats(db); 1046 1047 // We need to close and reopen the database connection so that the stats are 1048 // taken into account. Make a note of it and do the actual reopening in the 1049 // getWritableDatabase method. 1050 mReopenDatabase = true; 1051 } 1052 1053 ContentResolver.requestSync(null /* all accounts */, 1054 ContactsContract.AUTHORITY, new Bundle()); 1055 } 1056 1057 private void createDirectoriesTable(SQLiteDatabase db) { 1058 db.execSQL("CREATE TABLE " + Tables.DIRECTORIES + "(" + 1059 Directory._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 1060 Directory.PACKAGE_NAME + " TEXT NOT NULL," + 1061 Directory.DIRECTORY_AUTHORITY + " TEXT NOT NULL," + 1062 Directory.TYPE_RESOURCE_ID + " INTEGER," + 1063 DirectoryColumns.TYPE_RESOURCE_NAME + " TEXT," + 1064 Directory.ACCOUNT_TYPE + " TEXT," + 1065 Directory.ACCOUNT_NAME + " TEXT," + 1066 Directory.DISPLAY_NAME + " TEXT, " + 1067 Directory.EXPORT_SUPPORT + " INTEGER NOT NULL" + 1068 " DEFAULT " + Directory.EXPORT_SUPPORT_NONE + "," + 1069 Directory.SHORTCUT_SUPPORT + " INTEGER NOT NULL" + 1070 " DEFAULT " + Directory.SHORTCUT_SUPPORT_NONE + "," + 1071 Directory.PHOTO_SUPPORT + " INTEGER NOT NULL" + 1072 " DEFAULT " + Directory.PHOTO_SUPPORT_NONE + 1073 ");"); 1074 1075 // Trigger a full scan of directories in the system 1076 setProperty(db, ContactDirectoryManager.PROPERTY_DIRECTORY_SCAN_COMPLETE, "0"); 1077 } 1078 1079 private static void createContactsTriggers(SQLiteDatabase db) { 1080 1081 /* 1082 * Automatically delete Data rows when a raw contact is deleted. 1083 */ 1084 db.execSQL("DROP TRIGGER IF EXISTS " + Tables.RAW_CONTACTS + "_deleted;"); 1085 db.execSQL("CREATE TRIGGER " + Tables.RAW_CONTACTS + "_deleted " 1086 + " BEFORE DELETE ON " + Tables.RAW_CONTACTS 1087 + " BEGIN " 1088 + " DELETE FROM " + Tables.DATA 1089 + " WHERE " + Data.RAW_CONTACT_ID 1090 + "=OLD." + RawContacts._ID + ";" 1091 + " DELETE FROM " + Tables.AGGREGATION_EXCEPTIONS 1092 + " WHERE " + AggregationExceptions.RAW_CONTACT_ID1 1093 + "=OLD." + RawContacts._ID 1094 + " OR " + AggregationExceptions.RAW_CONTACT_ID2 1095 + "=OLD." + RawContacts._ID + ";" 1096 + " DELETE FROM " + Tables.VISIBLE_CONTACTS 1097 + " WHERE " + Contacts._ID + "=OLD." + RawContacts.CONTACT_ID 1098 + " AND (SELECT COUNT(*) FROM " + Tables.RAW_CONTACTS 1099 + " WHERE " + RawContacts.CONTACT_ID + "=OLD." + RawContacts.CONTACT_ID 1100 + " )=1;" 1101 + " DELETE FROM " + Tables.DEFAULT_DIRECTORY 1102 + " WHERE " + Contacts._ID + "=OLD." + RawContacts.CONTACT_ID 1103 + " AND (SELECT COUNT(*) FROM " + Tables.RAW_CONTACTS 1104 + " WHERE " + RawContacts.CONTACT_ID + "=OLD." + RawContacts.CONTACT_ID 1105 + " )=1;" 1106 + " DELETE FROM " + Tables.CONTACTS 1107 + " WHERE " + Contacts._ID + "=OLD." + RawContacts.CONTACT_ID 1108 + " AND (SELECT COUNT(*) FROM " + Tables.RAW_CONTACTS 1109 + " WHERE " + RawContacts.CONTACT_ID + "=OLD." + RawContacts.CONTACT_ID 1110 + " )=1;" 1111 + " END"); 1112 1113 1114 db.execSQL("DROP TRIGGER IF EXISTS contacts_times_contacted;"); 1115 db.execSQL("DROP TRIGGER IF EXISTS raw_contacts_times_contacted;"); 1116 1117 /* 1118 * Triggers that update {@link RawContacts#VERSION} when the contact is 1119 * marked for deletion or any time a data row is inserted, updated or 1120 * deleted. 1121 */ 1122 db.execSQL("DROP TRIGGER IF EXISTS " + Tables.RAW_CONTACTS + "_marked_deleted;"); 1123 db.execSQL("CREATE TRIGGER " + Tables.RAW_CONTACTS + "_marked_deleted " 1124 + " AFTER UPDATE ON " + Tables.RAW_CONTACTS 1125 + " BEGIN " 1126 + " UPDATE " + Tables.RAW_CONTACTS 1127 + " SET " 1128 + RawContacts.VERSION + "=OLD." + RawContacts.VERSION + "+1 " 1129 + " WHERE " + RawContacts._ID + "=OLD." + RawContacts._ID 1130 + " AND NEW." + RawContacts.DELETED + "!= OLD." + RawContacts.DELETED + ";" 1131 + " END"); 1132 1133 db.execSQL("DROP TRIGGER IF EXISTS " + Tables.DATA + "_updated;"); 1134 db.execSQL("CREATE TRIGGER " + Tables.DATA + "_updated AFTER UPDATE ON " + Tables.DATA 1135 + " BEGIN " 1136 + " UPDATE " + Tables.DATA 1137 + " SET " + Data.DATA_VERSION + "=OLD." + Data.DATA_VERSION + "+1 " 1138 + " WHERE " + Data._ID + "=OLD." + Data._ID + ";" 1139 + " UPDATE " + Tables.RAW_CONTACTS 1140 + " SET " + RawContacts.VERSION + "=" + RawContacts.VERSION + "+1 " 1141 + " WHERE " + RawContacts._ID + "=OLD." + Data.RAW_CONTACT_ID + ";" 1142 + " END"); 1143 1144 db.execSQL("DROP TRIGGER IF EXISTS " + Tables.DATA + "_deleted;"); 1145 db.execSQL("CREATE TRIGGER " + Tables.DATA + "_deleted BEFORE DELETE ON " + Tables.DATA 1146 + " BEGIN " 1147 + " UPDATE " + Tables.RAW_CONTACTS 1148 + " SET " + RawContacts.VERSION + "=" + RawContacts.VERSION + "+1 " 1149 + " WHERE " + RawContacts._ID + "=OLD." + Data.RAW_CONTACT_ID + ";" 1150 + " DELETE FROM " + Tables.PHONE_LOOKUP 1151 + " WHERE " + PhoneLookupColumns.DATA_ID + "=OLD." + Data._ID + ";" 1152 + " DELETE FROM " + Tables.STATUS_UPDATES 1153 + " WHERE " + StatusUpdatesColumns.DATA_ID + "=OLD." + Data._ID + ";" 1154 + " DELETE FROM " + Tables.NAME_LOOKUP 1155 + " WHERE " + NameLookupColumns.DATA_ID + "=OLD." + Data._ID + ";" 1156 + " END"); 1157 1158 1159 db.execSQL("DROP TRIGGER IF EXISTS " + Tables.GROUPS + "_updated1;"); 1160 db.execSQL("CREATE TRIGGER " + Tables.GROUPS + "_updated1 " 1161 + " AFTER UPDATE ON " + Tables.GROUPS 1162 + " BEGIN " 1163 + " UPDATE " + Tables.GROUPS 1164 + " SET " 1165 + Groups.VERSION + "=OLD." + Groups.VERSION + "+1" 1166 + " WHERE " + Groups._ID + "=OLD." + Groups._ID + ";" 1167 + " END"); 1168 } 1169 1170 private static void createContactsIndexes(SQLiteDatabase db) { 1171 db.execSQL("DROP INDEX IF EXISTS name_lookup_index"); 1172 db.execSQL("CREATE INDEX name_lookup_index ON " + Tables.NAME_LOOKUP + " (" + 1173 NameLookupColumns.NORMALIZED_NAME + "," + 1174 NameLookupColumns.NAME_TYPE + ", " + 1175 NameLookupColumns.RAW_CONTACT_ID + ", " + 1176 NameLookupColumns.DATA_ID + 1177 ");"); 1178 1179 db.execSQL("DROP INDEX IF EXISTS raw_contact_sort_key1_index"); 1180 db.execSQL("CREATE INDEX raw_contact_sort_key1_index ON " + Tables.RAW_CONTACTS + " (" + 1181 RawContacts.SORT_KEY_PRIMARY + 1182 ");"); 1183 1184 db.execSQL("DROP INDEX IF EXISTS raw_contact_sort_key2_index"); 1185 db.execSQL("CREATE INDEX raw_contact_sort_key2_index ON " + Tables.RAW_CONTACTS + " (" + 1186 RawContacts.SORT_KEY_ALTERNATIVE + 1187 ");"); 1188 } 1189 1190 private static void createContactsViews(SQLiteDatabase db) { 1191 db.execSQL("DROP VIEW IF EXISTS " + Views.CONTACTS_ALL + ";"); 1192 db.execSQL("DROP VIEW IF EXISTS " + Views.CONTACTS_RESTRICTED + ";"); 1193 db.execSQL("DROP VIEW IF EXISTS " + Views.DATA_ALL + ";"); 1194 db.execSQL("DROP VIEW IF EXISTS " + Views.DATA_RESTRICTED + ";"); 1195 db.execSQL("DROP VIEW IF EXISTS " + Views.RAW_CONTACTS_ALL + ";"); 1196 db.execSQL("DROP VIEW IF EXISTS " + Views.RAW_CONTACTS_RESTRICTED + ";"); 1197 db.execSQL("DROP VIEW IF EXISTS " + Views.RAW_ENTITIES + ";"); 1198 db.execSQL("DROP VIEW IF EXISTS " + Views.RAW_ENTITIES_RESTRICTED + ";"); 1199 db.execSQL("DROP VIEW IF EXISTS " + Views.ENTITIES + ";"); 1200 db.execSQL("DROP VIEW IF EXISTS " + Views.ENTITIES_RESTRICTED + ";"); 1201 1202 String dataColumns = 1203 Data.IS_PRIMARY + ", " 1204 + Data.IS_SUPER_PRIMARY + ", " 1205 + Data.DATA_VERSION + ", " 1206 + PackagesColumns.PACKAGE + " AS " + Data.RES_PACKAGE + "," 1207 + MimetypesColumns.MIMETYPE + " AS " + Data.MIMETYPE + ", " 1208 + Data.IS_READ_ONLY + ", " 1209 + Data.DATA1 + ", " 1210 + Data.DATA2 + ", " 1211 + Data.DATA3 + ", " 1212 + Data.DATA4 + ", " 1213 + Data.DATA5 + ", " 1214 + Data.DATA6 + ", " 1215 + Data.DATA7 + ", " 1216 + Data.DATA8 + ", " 1217 + Data.DATA9 + ", " 1218 + Data.DATA10 + ", " 1219 + Data.DATA11 + ", " 1220 + Data.DATA12 + ", " 1221 + Data.DATA13 + ", " 1222 + Data.DATA14 + ", " 1223 + Data.DATA15 + ", " 1224 + Data.SYNC1 + ", " 1225 + Data.SYNC2 + ", " 1226 + Data.SYNC3 + ", " 1227 + Data.SYNC4; 1228 1229 String syncColumns = 1230 RawContactsColumns.CONCRETE_ACCOUNT_NAME + " AS " + RawContacts.ACCOUNT_NAME + "," 1231 + RawContactsColumns.CONCRETE_ACCOUNT_TYPE + " AS " + RawContacts.ACCOUNT_TYPE + "," 1232 + RawContactsColumns.CONCRETE_SOURCE_ID + " AS " + RawContacts.SOURCE_ID + "," 1233 + RawContactsColumns.CONCRETE_NAME_VERIFIED + " AS " + RawContacts.NAME_VERIFIED + "," 1234 + RawContactsColumns.CONCRETE_VERSION + " AS " + RawContacts.VERSION + "," 1235 + RawContactsColumns.CONCRETE_DIRTY + " AS " + RawContacts.DIRTY + "," 1236 + RawContactsColumns.CONCRETE_SYNC1 + " AS " + RawContacts.SYNC1 + "," 1237 + RawContactsColumns.CONCRETE_SYNC2 + " AS " + RawContacts.SYNC2 + "," 1238 + RawContactsColumns.CONCRETE_SYNC3 + " AS " + RawContacts.SYNC3 + "," 1239 + RawContactsColumns.CONCRETE_SYNC4 + " AS " + RawContacts.SYNC4; 1240 1241 String baseContactColumns = 1242 Contacts.HAS_PHONE_NUMBER + ", " 1243 + Contacts.NAME_RAW_CONTACT_ID + ", " 1244 + Contacts.LOOKUP_KEY + ", " 1245 + Contacts.PHOTO_ID + ", " 1246 + Clauses.CONTACT_VISIBLE + " AS " + Contacts.IN_VISIBLE_GROUP + ", " 1247 + ContactsColumns.LAST_STATUS_UPDATE_ID; 1248 1249 String contactOptionColumns = 1250 ContactsColumns.CONCRETE_CUSTOM_RINGTONE 1251 + " AS " + RawContacts.CUSTOM_RINGTONE + "," 1252 + ContactsColumns.CONCRETE_SEND_TO_VOICEMAIL 1253 + " AS " + RawContacts.SEND_TO_VOICEMAIL + "," 1254 + ContactsColumns.CONCRETE_LAST_TIME_CONTACTED 1255 + " AS " + RawContacts.LAST_TIME_CONTACTED + "," 1256 + ContactsColumns.CONCRETE_TIMES_CONTACTED 1257 + " AS " + RawContacts.TIMES_CONTACTED + "," 1258 + ContactsColumns.CONCRETE_STARRED 1259 + " AS " + RawContacts.STARRED; 1260 1261 String contactNameColumns = 1262 "name_raw_contact." + RawContacts.DISPLAY_NAME_SOURCE 1263 + " AS " + Contacts.DISPLAY_NAME_SOURCE + ", " 1264 + "name_raw_contact." + RawContacts.DISPLAY_NAME_PRIMARY 1265 + " AS " + Contacts.DISPLAY_NAME_PRIMARY + ", " 1266 + "name_raw_contact." + RawContacts.DISPLAY_NAME_ALTERNATIVE 1267 + " AS " + Contacts.DISPLAY_NAME_ALTERNATIVE + ", " 1268 + "name_raw_contact." + RawContacts.PHONETIC_NAME 1269 + " AS " + Contacts.PHONETIC_NAME + ", " 1270 + "name_raw_contact." + RawContacts.PHONETIC_NAME_STYLE 1271 + " AS " + Contacts.PHONETIC_NAME_STYLE + ", " 1272 + "name_raw_contact." + RawContacts.SORT_KEY_PRIMARY 1273 + " AS " + Contacts.SORT_KEY_PRIMARY + ", " 1274 + "name_raw_contact." + RawContacts.SORT_KEY_ALTERNATIVE 1275 + " AS " + Contacts.SORT_KEY_ALTERNATIVE; 1276 1277 String dataSelect = "SELECT " 1278 + DataColumns.CONCRETE_ID + " AS " + Data._ID + "," 1279 + Data.RAW_CONTACT_ID + ", " 1280 + RawContactsColumns.CONCRETE_CONTACT_ID + " AS " + RawContacts.CONTACT_ID + ", " 1281 + syncColumns + ", " 1282 + dataColumns + ", " 1283 + contactOptionColumns + ", " 1284 + contactNameColumns + ", " 1285 + baseContactColumns + ", " 1286 + buildPhotoUriAlias(RawContactsColumns.CONCRETE_CONTACT_ID, 1287 Contacts.PHOTO_URI) + ", " 1288 + buildPhotoUriAlias(RawContactsColumns.CONCRETE_CONTACT_ID, 1289 Contacts.PHOTO_THUMBNAIL_URI) + ", " 1290 + Tables.GROUPS + "." + Groups.SOURCE_ID + " AS " + GroupMembership.GROUP_SOURCE_ID 1291 + " FROM " + Tables.DATA 1292 + " JOIN " + Tables.MIMETYPES + " ON (" 1293 + DataColumns.CONCRETE_MIMETYPE_ID + "=" + MimetypesColumns.CONCRETE_ID + ")" 1294 + " JOIN " + Tables.RAW_CONTACTS + " ON (" 1295 + DataColumns.CONCRETE_RAW_CONTACT_ID + "=" + RawContactsColumns.CONCRETE_ID + ")" 1296 + " JOIN " + Tables.CONTACTS + " ON (" 1297 + RawContactsColumns.CONCRETE_CONTACT_ID + "=" + ContactsColumns.CONCRETE_ID + ")" 1298 + " JOIN " + Tables.RAW_CONTACTS + " AS name_raw_contact ON(" 1299 + Contacts.NAME_RAW_CONTACT_ID + "=name_raw_contact." + RawContacts._ID + ")" 1300 + " LEFT OUTER JOIN " + Tables.PACKAGES + " ON (" 1301 + DataColumns.CONCRETE_PACKAGE_ID + "=" + PackagesColumns.CONCRETE_ID + ")" 1302 + " LEFT OUTER JOIN " + Tables.GROUPS + " ON (" 1303 + MimetypesColumns.CONCRETE_MIMETYPE + "='" + GroupMembership.CONTENT_ITEM_TYPE 1304 + "' AND " + GroupsColumns.CONCRETE_ID + "=" 1305 + Tables.DATA + "." + GroupMembership.GROUP_ROW_ID + ")"; 1306 1307 db.execSQL("CREATE VIEW " + Views.DATA_ALL + " AS " + dataSelect); 1308 db.execSQL("CREATE VIEW " + Views.DATA_RESTRICTED + " AS " + dataSelect + " WHERE " 1309 + RawContactsColumns.CONCRETE_IS_RESTRICTED + "=0"); 1310 1311 String rawContactOptionColumns = 1312 RawContacts.CUSTOM_RINGTONE + "," 1313 + RawContacts.SEND_TO_VOICEMAIL + "," 1314 + RawContacts.LAST_TIME_CONTACTED + "," 1315 + RawContacts.TIMES_CONTACTED + "," 1316 + RawContacts.STARRED; 1317 1318 String rawContactsSelect = "SELECT " 1319 + RawContactsColumns.CONCRETE_ID + " AS " + RawContacts._ID + "," 1320 + RawContacts.CONTACT_ID + ", " 1321 + RawContacts.AGGREGATION_MODE + ", " 1322 + RawContacts.RAW_CONTACT_IS_READ_ONLY + ", " 1323 + RawContacts.DELETED + ", " 1324 + RawContacts.DISPLAY_NAME_SOURCE + ", " 1325 + RawContacts.DISPLAY_NAME_PRIMARY + ", " 1326 + RawContacts.DISPLAY_NAME_ALTERNATIVE + ", " 1327 + RawContacts.PHONETIC_NAME + ", " 1328 + RawContacts.PHONETIC_NAME_STYLE + ", " 1329 + RawContacts.SORT_KEY_PRIMARY + ", " 1330 + RawContacts.SORT_KEY_ALTERNATIVE + ", " 1331 + rawContactOptionColumns + ", " 1332 + syncColumns 1333 + " FROM " + Tables.RAW_CONTACTS; 1334 1335 db.execSQL("CREATE VIEW " + Views.RAW_CONTACTS_ALL + " AS " + rawContactsSelect); 1336 db.execSQL("CREATE VIEW " + Views.RAW_CONTACTS_RESTRICTED + " AS " + rawContactsSelect 1337 + " WHERE " + RawContacts.IS_RESTRICTED + "=0"); 1338 1339 String contactsColumns = 1340 ContactsColumns.CONCRETE_CUSTOM_RINGTONE 1341 + " AS " + Contacts.CUSTOM_RINGTONE + ", " 1342 + contactNameColumns + ", " 1343 + baseContactColumns + ", " 1344 + ContactsColumns.CONCRETE_LAST_TIME_CONTACTED 1345 + " AS " + Contacts.LAST_TIME_CONTACTED + ", " 1346 + ContactsColumns.CONCRETE_SEND_TO_VOICEMAIL 1347 + " AS " + Contacts.SEND_TO_VOICEMAIL + ", " 1348 + ContactsColumns.CONCRETE_STARRED 1349 + " AS " + Contacts.STARRED + ", " 1350 + ContactsColumns.CONCRETE_TIMES_CONTACTED 1351 + " AS " + Contacts.TIMES_CONTACTED; 1352 1353 String contactsSelect = "SELECT " 1354 + ContactsColumns.CONCRETE_ID + " AS " + Contacts._ID + "," 1355 + contactsColumns + ", " 1356 + buildPhotoUriAlias(ContactsColumns.CONCRETE_ID, Contacts.PHOTO_URI) + ", " 1357 + buildPhotoUriAlias(ContactsColumns.CONCRETE_ID, Contacts.PHOTO_THUMBNAIL_URI) 1358 + " FROM " + Tables.CONTACTS 1359 + " JOIN " + Tables.RAW_CONTACTS + " AS name_raw_contact ON(" 1360 + Contacts.NAME_RAW_CONTACT_ID + "=name_raw_contact." + RawContacts._ID + ")"; 1361 1362 db.execSQL("CREATE VIEW " + Views.CONTACTS_ALL + " AS " + contactsSelect); 1363 db.execSQL("CREATE VIEW " + Views.CONTACTS_RESTRICTED + " AS " + contactsSelect 1364 + " WHERE " + ContactsColumns.SINGLE_IS_RESTRICTED + "=0"); 1365 1366 String rawEntitiesSelect = "SELECT " 1367 + RawContacts.CONTACT_ID + ", " 1368 + RawContactsColumns.CONCRETE_DELETED + " AS " + RawContacts.DELETED + "," 1369 + dataColumns + ", " 1370 + syncColumns + ", " 1371 + Data.SYNC1 + ", " 1372 + Data.SYNC2 + ", " 1373 + Data.SYNC3 + ", " 1374 + Data.SYNC4 + ", " 1375 + RawContactsColumns.CONCRETE_ID + " AS " + RawContacts._ID + ", " 1376 + DataColumns.CONCRETE_ID + " AS " + RawContacts.Entity.DATA_ID + "," 1377 + RawContactsColumns.CONCRETE_STARRED + " AS " + RawContacts.STARRED + "," 1378 + RawContactsColumns.CONCRETE_IS_RESTRICTED + " AS " 1379 + RawContacts.IS_RESTRICTED + "," 1380 + Tables.GROUPS + "." + Groups.SOURCE_ID + " AS " + GroupMembership.GROUP_SOURCE_ID 1381 + " FROM " + Tables.RAW_CONTACTS 1382 + " LEFT OUTER JOIN " + Tables.DATA + " ON (" 1383 + DataColumns.CONCRETE_RAW_CONTACT_ID + "=" + RawContactsColumns.CONCRETE_ID + ")" 1384 + " LEFT OUTER JOIN " + Tables.PACKAGES + " ON (" 1385 + DataColumns.CONCRETE_PACKAGE_ID + "=" + PackagesColumns.CONCRETE_ID + ")" 1386 + " LEFT OUTER JOIN " + Tables.MIMETYPES + " ON (" 1387 + DataColumns.CONCRETE_MIMETYPE_ID + "=" + MimetypesColumns.CONCRETE_ID + ")" 1388 + " LEFT OUTER JOIN " + Tables.GROUPS + " ON (" 1389 + MimetypesColumns.CONCRETE_MIMETYPE + "='" + GroupMembership.CONTENT_ITEM_TYPE 1390 + "' AND " + GroupsColumns.CONCRETE_ID + "=" 1391 + Tables.DATA + "." + GroupMembership.GROUP_ROW_ID + ")"; 1392 1393 db.execSQL("CREATE VIEW " + Views.RAW_ENTITIES + " AS " 1394 + rawEntitiesSelect); 1395 db.execSQL("CREATE VIEW " + Views.RAW_ENTITIES_RESTRICTED + " AS " 1396 + rawEntitiesSelect + " WHERE " + RawContacts.IS_RESTRICTED + "=0"); 1397 1398 String entitiesSelect = "SELECT " 1399 + RawContactsColumns.CONCRETE_CONTACT_ID + " AS " + Contacts._ID + ", " 1400 + RawContactsColumns.CONCRETE_CONTACT_ID + " AS " + RawContacts.CONTACT_ID + ", " 1401 + RawContactsColumns.CONCRETE_DELETED + " AS " + RawContacts.DELETED + "," 1402 + RawContactsColumns.CONCRETE_IS_RESTRICTED 1403 + " AS " + RawContacts.IS_RESTRICTED + "," 1404 + dataColumns + ", " 1405 + syncColumns + ", " 1406 + contactsColumns + ", " 1407 + buildPhotoUriAlias(RawContactsColumns.CONCRETE_CONTACT_ID, 1408 Contacts.PHOTO_URI) + ", " 1409 + buildPhotoUriAlias(RawContactsColumns.CONCRETE_CONTACT_ID, 1410 Contacts.PHOTO_THUMBNAIL_URI) + ", " 1411 + Data.SYNC1 + ", " 1412 + Data.SYNC2 + ", " 1413 + Data.SYNC3 + ", " 1414 + Data.SYNC4 + ", " 1415 + RawContactsColumns.CONCRETE_ID + " AS " + Contacts.Entity.RAW_CONTACT_ID + ", " 1416 + DataColumns.CONCRETE_ID + " AS " + Contacts.Entity.DATA_ID + "," 1417 + Tables.GROUPS + "." + Groups.SOURCE_ID + " AS " + GroupMembership.GROUP_SOURCE_ID 1418 + " FROM " + Tables.RAW_CONTACTS 1419 + " JOIN " + Tables.CONTACTS + " ON (" 1420 + RawContactsColumns.CONCRETE_CONTACT_ID + "=" + ContactsColumns.CONCRETE_ID + ")" 1421 + " JOIN " + Tables.RAW_CONTACTS + " AS name_raw_contact ON(" 1422 + Contacts.NAME_RAW_CONTACT_ID + "=name_raw_contact." + RawContacts._ID + ")" 1423 + " LEFT OUTER JOIN " + Tables.DATA + " ON (" 1424 + DataColumns.CONCRETE_RAW_CONTACT_ID + "=" + RawContactsColumns.CONCRETE_ID + ")" 1425 + " LEFT OUTER JOIN " + Tables.PACKAGES + " ON (" 1426 + DataColumns.CONCRETE_PACKAGE_ID + "=" + PackagesColumns.CONCRETE_ID + ")" 1427 + " LEFT OUTER JOIN " + Tables.MIMETYPES + " ON (" 1428 + DataColumns.CONCRETE_MIMETYPE_ID + "=" + MimetypesColumns.CONCRETE_ID + ")" 1429 + " LEFT OUTER JOIN " + Tables.GROUPS + " ON (" 1430 + MimetypesColumns.CONCRETE_MIMETYPE + "='" + GroupMembership.CONTENT_ITEM_TYPE 1431 + "' AND " + GroupsColumns.CONCRETE_ID + "=" 1432 + Tables.DATA + "." + GroupMembership.GROUP_ROW_ID + ")"; 1433 1434 db.execSQL("CREATE VIEW " + Views.ENTITIES + " AS " 1435 + entitiesSelect); 1436 db.execSQL("CREATE VIEW " + Views.ENTITIES_RESTRICTED + " AS " 1437 + entitiesSelect + " WHERE " + RawContactsColumns.CONCRETE_IS_RESTRICTED + "=0"); 1438 } 1439 1440 private static String buildPhotoUriAlias(String contactIdColumn, String alias) { 1441 return "(CASE WHEN " + Contacts.PHOTO_ID + " IS NULL" 1442 + " OR " + Contacts.PHOTO_ID + "=0" 1443 + " THEN NULL" 1444 + " ELSE " + "'" + Contacts.CONTENT_URI + "/'||" 1445 + contactIdColumn + "|| '/" + Photo.CONTENT_DIRECTORY + "'" 1446 + " END)" 1447 + " AS " + alias; 1448 } 1449 1450 private static void createGroupsView(SQLiteDatabase db) { 1451 db.execSQL("DROP VIEW IF EXISTS " + Views.GROUPS_ALL + ";"); 1452 String groupsColumns = 1453 Groups.ACCOUNT_NAME + "," 1454 + Groups.ACCOUNT_TYPE + "," 1455 + Groups.SOURCE_ID + "," 1456 + Groups.VERSION + "," 1457 + Groups.DIRTY + "," 1458 + Groups.TITLE + "," 1459 + Groups.TITLE_RES + "," 1460 + Groups.NOTES + "," 1461 + Groups.SYSTEM_ID + "," 1462 + Groups.DELETED + "," 1463 + Groups.GROUP_VISIBLE + "," 1464 + Groups.SHOULD_SYNC + "," 1465 + Groups.AUTO_ADD + "," 1466 + Groups.FAVORITES + "," 1467 + Groups.GROUP_IS_READ_ONLY + "," 1468 + Groups.SYNC1 + "," 1469 + Groups.SYNC2 + "," 1470 + Groups.SYNC3 + "," 1471 + Groups.SYNC4 + "," 1472 + PackagesColumns.PACKAGE + " AS " + Groups.RES_PACKAGE; 1473 1474 String groupsSelect = "SELECT " 1475 + GroupsColumns.CONCRETE_ID + " AS " + Groups._ID + "," 1476 + groupsColumns 1477 + " FROM " + Tables.GROUPS_JOIN_PACKAGES; 1478 1479 db.execSQL("CREATE VIEW " + Views.GROUPS_ALL + " AS " + groupsSelect); 1480 } 1481 1482 @Override 1483 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 1484 if (oldVersion < 99) { 1485 Log.i(TAG, "Upgrading from version " + oldVersion + " to " + newVersion 1486 + ", data will be lost!"); 1487 1488 db.execSQL("DROP TABLE IF EXISTS " + Tables.CONTACTS + ";"); 1489 db.execSQL("DROP TABLE IF EXISTS " + Tables.RAW_CONTACTS + ";"); 1490 db.execSQL("DROP TABLE IF EXISTS " + Tables.PACKAGES + ";"); 1491 db.execSQL("DROP TABLE IF EXISTS " + Tables.MIMETYPES + ";"); 1492 db.execSQL("DROP TABLE IF EXISTS " + Tables.DATA + ";"); 1493 db.execSQL("DROP TABLE IF EXISTS " + Tables.PHONE_LOOKUP + ";"); 1494 db.execSQL("DROP TABLE IF EXISTS " + Tables.NAME_LOOKUP + ";"); 1495 db.execSQL("DROP TABLE IF EXISTS " + Tables.NICKNAME_LOOKUP + ";"); 1496 db.execSQL("DROP TABLE IF EXISTS " + Tables.GROUPS + ";"); 1497 db.execSQL("DROP TABLE IF EXISTS " + Tables.ACTIVITIES + ";"); 1498 db.execSQL("DROP TABLE IF EXISTS " + Tables.CALLS + ";"); 1499 db.execSQL("DROP TABLE IF EXISTS " + Tables.SETTINGS + ";"); 1500 db.execSQL("DROP TABLE IF EXISTS " + Tables.STATUS_UPDATES + ";"); 1501 1502 // TODO: we should not be dropping agg_exceptions and contact_options. In case that 1503 // table's schema changes, we should try to preserve the data, because it was entered 1504 // by the user and has never been synched to the server. 1505 db.execSQL("DROP TABLE IF EXISTS " + Tables.AGGREGATION_EXCEPTIONS + ";"); 1506 1507 onCreate(db); 1508 return; 1509 } 1510 1511 Log.i(TAG, "Upgrading from version " + oldVersion + " to " + newVersion); 1512 1513 boolean upgradeViewsAndTriggers = false; 1514 boolean upgradeNameLookup = false; 1515 1516 if (oldVersion == 99) { 1517 upgradeViewsAndTriggers = true; 1518 oldVersion++; 1519 } 1520 1521 if (oldVersion == 100) { 1522 db.execSQL("CREATE INDEX IF NOT EXISTS mimetypes_mimetype_index ON " 1523 + Tables.MIMETYPES + " (" 1524 + MimetypesColumns.MIMETYPE + "," 1525 + MimetypesColumns._ID + ");"); 1526 updateIndexStats(db, Tables.MIMETYPES, 1527 "mimetypes_mimetype_index", "50 1 1"); 1528 1529 upgradeViewsAndTriggers = true; 1530 oldVersion++; 1531 } 1532 1533 if (oldVersion == 101) { 1534 upgradeViewsAndTriggers = true; 1535 oldVersion++; 1536 } 1537 1538 if (oldVersion == 102) { 1539 upgradeViewsAndTriggers = true; 1540 oldVersion++; 1541 } 1542 1543 if (oldVersion == 103) { 1544 upgradeViewsAndTriggers = true; 1545 oldVersion++; 1546 } 1547 1548 if (oldVersion == 104 || oldVersion == 201) { 1549 LegacyApiSupport.createSettingsTable(db); 1550 upgradeViewsAndTriggers = true; 1551 oldVersion++; 1552 } 1553 1554 if (oldVersion == 105) { 1555 upgradeToVersion202(db); 1556 upgradeNameLookup = true; 1557 oldVersion = 202; 1558 } 1559 1560 if (oldVersion == 202) { 1561 upgradeToVersion203(db); 1562 upgradeViewsAndTriggers = true; 1563 oldVersion++; 1564 } 1565 1566 if (oldVersion == 203) { 1567 upgradeViewsAndTriggers = true; 1568 oldVersion++; 1569 } 1570 1571 if (oldVersion == 204) { 1572 upgradeToVersion205(db); 1573 upgradeViewsAndTriggers = true; 1574 oldVersion++; 1575 } 1576 1577 if (oldVersion == 205) { 1578 upgrateToVersion206(db); 1579 upgradeViewsAndTriggers = true; 1580 oldVersion++; 1581 } 1582 1583 if (oldVersion == 206) { 1584 upgradeToVersion300(db); 1585 oldVersion = 300; 1586 } 1587 1588 if (oldVersion == 300) { 1589 upgradeViewsAndTriggers = true; 1590 oldVersion = 301; 1591 } 1592 1593 if (oldVersion == 301) { 1594 upgradeViewsAndTriggers = true; 1595 oldVersion = 302; 1596 } 1597 1598 if (oldVersion == 302) { 1599 upgradeEmailToVersion303(db); 1600 upgradeNicknameToVersion303(db); 1601 oldVersion = 303; 1602 } 1603 1604 if (oldVersion == 303) { 1605 upgradeToVersion304(db); 1606 oldVersion = 304; 1607 } 1608 1609 if (oldVersion == 304) { 1610 upgradeNameLookup = true; 1611 oldVersion = 305; 1612 } 1613 1614 if (oldVersion == 305) { 1615 upgradeToVersion306(db); 1616 oldVersion = 306; 1617 } 1618 1619 if (oldVersion == 306) { 1620 upgradeToVersion307(db); 1621 oldVersion = 307; 1622 } 1623 1624 if (oldVersion == 307) { 1625 upgradeToVersion308(db); 1626 oldVersion = 308; 1627 } 1628 1629 // Gingerbread upgrades 1630 if (oldVersion < 350) { 1631 upgradeViewsAndTriggers = true; 1632 oldVersion = 351; 1633 } 1634 1635 if (oldVersion == 351) { 1636 upgradeNameLookup = true; 1637 oldVersion = 352; 1638 } 1639 1640 if (oldVersion == 352) { 1641 upgradeToVersion353(db); 1642 oldVersion = 353; 1643 } 1644 1645 // Honeycomb upgrades 1646 if (oldVersion < 400) { 1647 upgradeViewsAndTriggers = true; 1648 upgradeToVersion400(db); 1649 oldVersion = 400; 1650 } 1651 1652 if (oldVersion == 400) { 1653 upgradeViewsAndTriggers = true; 1654 upgradeToVersion401(db); 1655 oldVersion = 401; 1656 } 1657 1658 if (oldVersion == 401) { 1659 upgradeToVersion402(db); 1660 oldVersion = 402; 1661 } 1662 1663 if (oldVersion == 402) { 1664 upgradeViewsAndTriggers = true; 1665 upgradeToVersion403(db); 1666 oldVersion = 403; 1667 } 1668 1669 if (oldVersion == 403) { 1670 upgradeViewsAndTriggers = true; 1671 oldVersion = 404; 1672 } 1673 1674 if (oldVersion == 404) { 1675 upgradeViewsAndTriggers = true; 1676 upgradeToVersion405(db); 1677 oldVersion = 405; 1678 } 1679 1680 if (oldVersion == 405) { 1681 upgradeViewsAndTriggers = true; 1682 upgradeToVersion406(db); 1683 oldVersion = 406; 1684 } 1685 1686 if (oldVersion == 406) { 1687 upgradeViewsAndTriggers = true; 1688 oldVersion = 407; 1689 } 1690 1691 if (oldVersion == 407) { 1692 // Obsolete 1693 oldVersion = 408; 1694 } 1695 1696 if (oldVersion == 408) { 1697 upgradeViewsAndTriggers = true; 1698 upgradeToVersion409(db); 1699 oldVersion = 409; 1700 } 1701 1702 if (oldVersion == 409) { 1703 upgradeViewsAndTriggers = true; 1704 oldVersion = 410; 1705 } 1706 1707 if (oldVersion == 410) { 1708 upgradeToVersion411(db); 1709 oldVersion = 411; 1710 } 1711 1712 if (oldVersion == 411) { 1713 // Same upgrade as 353, only on Honeycomb devices 1714 upgradeToVersion353(db); 1715 oldVersion = 412; 1716 } 1717 1718 if (oldVersion == 412) { 1719 upgradeToVersion413(db); 1720 oldVersion = 413; 1721 } 1722 1723 if (oldVersion == 413) { 1724 upgradeNameLookup = true; 1725 oldVersion = 414; 1726 } 1727 1728 if (oldVersion == 414) { 1729 upgradeToVersion415(db); 1730 upgradeViewsAndTriggers = true; 1731 oldVersion = 415; 1732 } 1733 1734 if (upgradeViewsAndTriggers) { 1735 createContactsViews(db); 1736 createGroupsView(db); 1737 createContactsTriggers(db); 1738 createContactsIndexes(db); 1739 LegacyApiSupport.createViews(db); 1740 updateSqliteStats(db); 1741 mReopenDatabase = true; 1742 } 1743 1744 if (upgradeNameLookup) { 1745 rebuildNameLookup(db); 1746 } 1747 1748 if (oldVersion != newVersion) { 1749 throw new IllegalStateException( 1750 "error upgrading the database to version " + newVersion); 1751 } 1752 } 1753 1754 private void upgradeToVersion202(SQLiteDatabase db) { 1755 db.execSQL( 1756 "ALTER TABLE " + Tables.PHONE_LOOKUP + 1757 " ADD " + PhoneLookupColumns.MIN_MATCH + " TEXT;"); 1758 1759 db.execSQL("CREATE INDEX phone_lookup_min_match_index ON " + Tables.PHONE_LOOKUP + " (" + 1760 PhoneLookupColumns.MIN_MATCH + "," + 1761 PhoneLookupColumns.RAW_CONTACT_ID + "," + 1762 PhoneLookupColumns.DATA_ID + 1763 ");"); 1764 1765 updateIndexStats(db, Tables.PHONE_LOOKUP, 1766 "phone_lookup_min_match_index", "10000 2 2 1"); 1767 1768 SQLiteStatement update = db.compileStatement( 1769 "UPDATE " + Tables.PHONE_LOOKUP + 1770 " SET " + PhoneLookupColumns.MIN_MATCH + "=?" + 1771 " WHERE " + PhoneLookupColumns.DATA_ID + "=?"); 1772 1773 // Populate the new column 1774 Cursor c = db.query(Tables.PHONE_LOOKUP + " JOIN " + Tables.DATA + 1775 " ON (" + PhoneLookupColumns.DATA_ID + "=" + DataColumns.CONCRETE_ID + ")", 1776 new String[]{Data._ID, Phone.NUMBER}, null, null, null, null, null); 1777 try { 1778 while (c.moveToNext()) { 1779 long dataId = c.getLong(0); 1780 String number = c.getString(1); 1781 if (!TextUtils.isEmpty(number)) { 1782 update.bindString(1, PhoneNumberUtils.toCallerIDMinMatch(number)); 1783 update.bindLong(2, dataId); 1784 update.execute(); 1785 } 1786 } 1787 } finally { 1788 c.close(); 1789 } 1790 } 1791 1792 private void upgradeToVersion203(SQLiteDatabase db) { 1793 // Garbage-collect first. A bug in Eclair was sometimes leaving 1794 // raw_contacts in the database that no longer had contacts associated 1795 // with them. To avoid failures during this database upgrade, drop 1796 // the orphaned raw_contacts. 1797 db.execSQL( 1798 "DELETE FROM raw_contacts" + 1799 " WHERE contact_id NOT NULL" + 1800 " AND contact_id NOT IN (SELECT _id FROM contacts)"); 1801 1802 db.execSQL( 1803 "ALTER TABLE " + Tables.CONTACTS + 1804 " ADD " + Contacts.NAME_RAW_CONTACT_ID + " INTEGER REFERENCES raw_contacts(_id)"); 1805 db.execSQL( 1806 "ALTER TABLE " + Tables.RAW_CONTACTS + 1807 " ADD contact_in_visible_group INTEGER NOT NULL DEFAULT 0"); 1808 1809 // For each Contact, find the RawContact that contributed the display name 1810 db.execSQL( 1811 "UPDATE " + Tables.CONTACTS + 1812 " SET " + Contacts.NAME_RAW_CONTACT_ID + "=(" + 1813 " SELECT " + RawContacts._ID + 1814 " FROM " + Tables.RAW_CONTACTS + 1815 " WHERE " + RawContacts.CONTACT_ID + "=" + ContactsColumns.CONCRETE_ID + 1816 " AND " + RawContactsColumns.CONCRETE_DISPLAY_NAME + "=" + 1817 Tables.CONTACTS + "." + Contacts.DISPLAY_NAME + 1818 " ORDER BY " + RawContacts._ID + 1819 " LIMIT 1)" 1820 ); 1821 1822 db.execSQL("CREATE INDEX contacts_name_raw_contact_id_index ON " + Tables.CONTACTS + " (" + 1823 Contacts.NAME_RAW_CONTACT_ID + 1824 ");"); 1825 1826 // If for some unknown reason we missed some names, let's make sure there are 1827 // no contacts without a name, picking a raw contact "at random". 1828 db.execSQL( 1829 "UPDATE " + Tables.CONTACTS + 1830 " SET " + Contacts.NAME_RAW_CONTACT_ID + "=(" + 1831 " SELECT " + RawContacts._ID + 1832 " FROM " + Tables.RAW_CONTACTS + 1833 " WHERE " + RawContacts.CONTACT_ID + "=" + ContactsColumns.CONCRETE_ID + 1834 " ORDER BY " + RawContacts._ID + 1835 " LIMIT 1)" + 1836 " WHERE " + Contacts.NAME_RAW_CONTACT_ID + " IS NULL" 1837 ); 1838 1839 // Wipe out DISPLAY_NAME on the Contacts table as it is no longer in use. 1840 db.execSQL( 1841 "UPDATE " + Tables.CONTACTS + 1842 " SET " + Contacts.DISPLAY_NAME + "=NULL" 1843 ); 1844 1845 // Copy the IN_VISIBLE_GROUP flag down to all raw contacts to allow 1846 // indexing on (display_name, in_visible_group) 1847 db.execSQL( 1848 "UPDATE " + Tables.RAW_CONTACTS + 1849 " SET contact_in_visible_group=(" + 1850 "SELECT " + Contacts.IN_VISIBLE_GROUP + 1851 " FROM " + Tables.CONTACTS + 1852 " WHERE " + Contacts._ID + "=" + RawContacts.CONTACT_ID + ")" + 1853 " WHERE " + RawContacts.CONTACT_ID + " NOT NULL" 1854 ); 1855 1856 db.execSQL("CREATE INDEX raw_contact_sort_key1_index ON " + Tables.RAW_CONTACTS + " (" + 1857 "contact_in_visible_group" + "," + 1858 RawContactsColumns.DISPLAY_NAME + " COLLATE LOCALIZED ASC" + 1859 ");"); 1860 1861 db.execSQL("DROP INDEX contacts_visible_index"); 1862 db.execSQL("CREATE INDEX contacts_visible_index ON " + Tables.CONTACTS + " (" + 1863 Contacts.IN_VISIBLE_GROUP + 1864 ");"); 1865 } 1866 1867 private void upgradeToVersion205(SQLiteDatabase db) { 1868 db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS 1869 + " ADD " + RawContacts.DISPLAY_NAME_ALTERNATIVE + " TEXT;"); 1870 db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS 1871 + " ADD " + RawContacts.PHONETIC_NAME + " TEXT;"); 1872 db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS 1873 + " ADD " + RawContacts.PHONETIC_NAME_STYLE + " INTEGER;"); 1874 db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS 1875 + " ADD " + RawContacts.SORT_KEY_PRIMARY 1876 + " TEXT COLLATE " + ContactsProvider2.PHONEBOOK_COLLATOR_NAME + ";"); 1877 db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS 1878 + " ADD " + RawContacts.SORT_KEY_ALTERNATIVE 1879 + " TEXT COLLATE " + ContactsProvider2.PHONEBOOK_COLLATOR_NAME + ";"); 1880 1881 final Locale locale = Locale.getDefault(); 1882 1883 NameSplitter splitter = createNameSplitter(); 1884 1885 SQLiteStatement rawContactUpdate = db.compileStatement( 1886 "UPDATE " + Tables.RAW_CONTACTS + 1887 " SET " + 1888 RawContacts.DISPLAY_NAME_PRIMARY + "=?," + 1889 RawContacts.DISPLAY_NAME_ALTERNATIVE + "=?," + 1890 RawContacts.PHONETIC_NAME + "=?," + 1891 RawContacts.PHONETIC_NAME_STYLE + "=?," + 1892 RawContacts.SORT_KEY_PRIMARY + "=?," + 1893 RawContacts.SORT_KEY_ALTERNATIVE + "=?" + 1894 " WHERE " + RawContacts._ID + "=?"); 1895 1896 upgradeStructuredNamesToVersion205(db, rawContactUpdate, splitter); 1897 upgradeOrganizationsToVersion205(db, rawContactUpdate, splitter); 1898 1899 db.execSQL("DROP INDEX raw_contact_sort_key1_index"); 1900 db.execSQL("CREATE INDEX raw_contact_sort_key1_index ON " + Tables.RAW_CONTACTS + " (" + 1901 "contact_in_visible_group" + "," + 1902 RawContacts.SORT_KEY_PRIMARY + 1903 ");"); 1904 1905 db.execSQL("CREATE INDEX raw_contact_sort_key2_index ON " + Tables.RAW_CONTACTS + " (" + 1906 "contact_in_visible_group" + "," + 1907 RawContacts.SORT_KEY_ALTERNATIVE + 1908 ");"); 1909 } 1910 1911 private interface StructName205Query { 1912 String TABLE = Tables.DATA_JOIN_RAW_CONTACTS; 1913 1914 String COLUMNS[] = { 1915 DataColumns.CONCRETE_ID, 1916 Data.RAW_CONTACT_ID, 1917 RawContacts.DISPLAY_NAME_SOURCE, 1918 RawContacts.DISPLAY_NAME_PRIMARY, 1919 StructuredName.PREFIX, 1920 StructuredName.GIVEN_NAME, 1921 StructuredName.MIDDLE_NAME, 1922 StructuredName.FAMILY_NAME, 1923 StructuredName.SUFFIX, 1924 StructuredName.PHONETIC_FAMILY_NAME, 1925 StructuredName.PHONETIC_MIDDLE_NAME, 1926 StructuredName.PHONETIC_GIVEN_NAME, 1927 }; 1928 1929 int ID = 0; 1930 int RAW_CONTACT_ID = 1; 1931 int DISPLAY_NAME_SOURCE = 2; 1932 int DISPLAY_NAME = 3; 1933 int PREFIX = 4; 1934 int GIVEN_NAME = 5; 1935 int MIDDLE_NAME = 6; 1936 int FAMILY_NAME = 7; 1937 int SUFFIX = 8; 1938 int PHONETIC_FAMILY_NAME = 9; 1939 int PHONETIC_MIDDLE_NAME = 10; 1940 int PHONETIC_GIVEN_NAME = 11; 1941 } 1942 1943 private void upgradeStructuredNamesToVersion205(SQLiteDatabase db, 1944 SQLiteStatement rawContactUpdate, NameSplitter splitter) { 1945 1946 // Process structured names to detect the style of the full name and phonetic name 1947 1948 long mMimeType; 1949 try { 1950 mMimeType = DatabaseUtils.longForQuery(db, 1951 "SELECT " + MimetypesColumns._ID + 1952 " FROM " + Tables.MIMETYPES + 1953 " WHERE " + MimetypesColumns.MIMETYPE 1954 + "='" + StructuredName.CONTENT_ITEM_TYPE + "'", null); 1955 } catch (SQLiteDoneException e) { 1956 // No structured names in the database 1957 return; 1958 } 1959 1960 SQLiteStatement structuredNameUpdate = db.compileStatement( 1961 "UPDATE " + Tables.DATA + 1962 " SET " + 1963 StructuredName.FULL_NAME_STYLE + "=?," + 1964 StructuredName.DISPLAY_NAME + "=?," + 1965 StructuredName.PHONETIC_NAME_STYLE + "=?" + 1966 " WHERE " + Data._ID + "=?"); 1967 1968 NameSplitter.Name name = new NameSplitter.Name(); 1969 StringBuilder sb = new StringBuilder(); 1970 Cursor cursor = db.query(StructName205Query.TABLE, 1971 StructName205Query.COLUMNS, 1972 DataColumns.MIMETYPE_ID + "=" + mMimeType, null, null, null, null); 1973 try { 1974 while (cursor.moveToNext()) { 1975 long dataId = cursor.getLong(StructName205Query.ID); 1976 long rawContactId = cursor.getLong(StructName205Query.RAW_CONTACT_ID); 1977 int displayNameSource = cursor.getInt(StructName205Query.DISPLAY_NAME_SOURCE); 1978 String displayName = cursor.getString(StructName205Query.DISPLAY_NAME); 1979 1980 name.clear(); 1981 name.prefix = cursor.getString(StructName205Query.PREFIX); 1982 name.givenNames = cursor.getString(StructName205Query.GIVEN_NAME); 1983 name.middleName = cursor.getString(StructName205Query.MIDDLE_NAME); 1984 name.familyName = cursor.getString(StructName205Query.FAMILY_NAME); 1985 name.suffix = cursor.getString(StructName205Query.SUFFIX); 1986 name.phoneticFamilyName = cursor.getString(StructName205Query.PHONETIC_FAMILY_NAME); 1987 name.phoneticMiddleName = cursor.getString(StructName205Query.PHONETIC_MIDDLE_NAME); 1988 name.phoneticGivenName = cursor.getString(StructName205Query.PHONETIC_GIVEN_NAME); 1989 1990 upgradeNameToVersion205(dataId, rawContactId, displayNameSource, displayName, name, 1991 structuredNameUpdate, rawContactUpdate, splitter, sb); 1992 } 1993 } finally { 1994 cursor.close(); 1995 } 1996 } 1997 1998 private void upgradeNameToVersion205(long dataId, long rawContactId, int displayNameSource, 1999 String currentDisplayName, NameSplitter.Name name, 2000 SQLiteStatement structuredNameUpdate, SQLiteStatement rawContactUpdate, 2001 NameSplitter splitter, StringBuilder sb) { 2002 2003 splitter.guessNameStyle(name); 2004 int unadjustedFullNameStyle = name.fullNameStyle; 2005 name.fullNameStyle = splitter.getAdjustedFullNameStyle(name.fullNameStyle); 2006 String displayName = splitter.join(name, true); 2007 2008 // Don't update database with the adjusted fullNameStyle as it is locale 2009 // related 2010 structuredNameUpdate.bindLong(1, unadjustedFullNameStyle); 2011 DatabaseUtils.bindObjectToProgram(structuredNameUpdate, 2, displayName); 2012 structuredNameUpdate.bindLong(3, name.phoneticNameStyle); 2013 structuredNameUpdate.bindLong(4, dataId); 2014 structuredNameUpdate.execute(); 2015 2016 if (displayNameSource == DisplayNameSources.STRUCTURED_NAME) { 2017 String displayNameAlternative = splitter.join(name, false); 2018 String phoneticName = splitter.joinPhoneticName(name); 2019 String sortKey = null; 2020 String sortKeyAlternative = null; 2021 2022 if (phoneticName != null) { 2023 sortKey = sortKeyAlternative = phoneticName; 2024 } else if (name.fullNameStyle == FullNameStyle.CHINESE || 2025 name.fullNameStyle == FullNameStyle.CJK) { 2026 sortKey = sortKeyAlternative = ContactLocaleUtils.getIntance() 2027 .getSortKey(displayName, name.fullNameStyle); 2028 } 2029 2030 if (sortKey == null) { 2031 sortKey = displayName; 2032 sortKeyAlternative = displayNameAlternative; 2033 } 2034 2035 updateRawContact205(rawContactUpdate, rawContactId, displayName, 2036 displayNameAlternative, name.phoneticNameStyle, phoneticName, sortKey, 2037 sortKeyAlternative); 2038 } 2039 } 2040 2041 private interface Organization205Query { 2042 String TABLE = Tables.DATA_JOIN_RAW_CONTACTS; 2043 2044 String COLUMNS[] = { 2045 DataColumns.CONCRETE_ID, 2046 Data.RAW_CONTACT_ID, 2047 Organization.COMPANY, 2048 Organization.PHONETIC_NAME, 2049 }; 2050 2051 int ID = 0; 2052 int RAW_CONTACT_ID = 1; 2053 int COMPANY = 2; 2054 int PHONETIC_NAME = 3; 2055 } 2056 2057 private void upgradeOrganizationsToVersion205(SQLiteDatabase db, 2058 SQLiteStatement rawContactUpdate, NameSplitter splitter) { 2059 final long mimeType = lookupMimeTypeId(db, Organization.CONTENT_ITEM_TYPE); 2060 2061 SQLiteStatement organizationUpdate = db.compileStatement( 2062 "UPDATE " + Tables.DATA + 2063 " SET " + 2064 Organization.PHONETIC_NAME_STYLE + "=?" + 2065 " WHERE " + Data._ID + "=?"); 2066 2067 Cursor cursor = db.query(Organization205Query.TABLE, Organization205Query.COLUMNS, 2068 DataColumns.MIMETYPE_ID + "=" + mimeType + " AND " 2069 + RawContacts.DISPLAY_NAME_SOURCE + "=" + DisplayNameSources.ORGANIZATION, 2070 null, null, null, null); 2071 try { 2072 while (cursor.moveToNext()) { 2073 long dataId = cursor.getLong(Organization205Query.ID); 2074 long rawContactId = cursor.getLong(Organization205Query.RAW_CONTACT_ID); 2075 String company = cursor.getString(Organization205Query.COMPANY); 2076 String phoneticName = cursor.getString(Organization205Query.PHONETIC_NAME); 2077 2078 int phoneticNameStyle = splitter.guessPhoneticNameStyle(phoneticName); 2079 2080 organizationUpdate.bindLong(1, phoneticNameStyle); 2081 organizationUpdate.bindLong(2, dataId); 2082 organizationUpdate.execute(); 2083 2084 String sortKey = null; 2085 if (phoneticName == null && company != null) { 2086 int nameStyle = splitter.guessFullNameStyle(company); 2087 nameStyle = splitter.getAdjustedFullNameStyle(nameStyle); 2088 if (nameStyle == FullNameStyle.CHINESE || 2089 nameStyle == FullNameStyle.CJK ) { 2090 sortKey = ContactLocaleUtils.getIntance() 2091 .getSortKey(company, nameStyle); 2092 } 2093 } 2094 2095 if (sortKey == null) { 2096 sortKey = company; 2097 } 2098 2099 updateRawContact205(rawContactUpdate, rawContactId, company, 2100 company, phoneticNameStyle, phoneticName, sortKey, sortKey); 2101 } 2102 } finally { 2103 cursor.close(); 2104 } 2105 } 2106 2107 private void updateRawContact205(SQLiteStatement rawContactUpdate, long rawContactId, 2108 String displayName, String displayNameAlternative, int phoneticNameStyle, 2109 String phoneticName, String sortKeyPrimary, String sortKeyAlternative) { 2110 bindString(rawContactUpdate, 1, displayName); 2111 bindString(rawContactUpdate, 2, displayNameAlternative); 2112 bindString(rawContactUpdate, 3, phoneticName); 2113 rawContactUpdate.bindLong(4, phoneticNameStyle); 2114 bindString(rawContactUpdate, 5, sortKeyPrimary); 2115 bindString(rawContactUpdate, 6, sortKeyAlternative); 2116 rawContactUpdate.bindLong(7, rawContactId); 2117 rawContactUpdate.execute(); 2118 } 2119 2120 private void upgrateToVersion206(SQLiteDatabase db) { 2121 db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS 2122 + " ADD " + RawContacts.NAME_VERIFIED + " INTEGER NOT NULL DEFAULT 0;"); 2123 } 2124 2125 private interface Organization300Query { 2126 String TABLE = Tables.DATA; 2127 2128 String SELECTION = DataColumns.MIMETYPE_ID + "=?"; 2129 2130 String COLUMNS[] = { 2131 Organization._ID, 2132 Organization.RAW_CONTACT_ID, 2133 Organization.COMPANY, 2134 Organization.TITLE 2135 }; 2136 2137 int ID = 0; 2138 int RAW_CONTACT_ID = 1; 2139 int COMPANY = 2; 2140 int TITLE = 3; 2141 } 2142 2143 /** 2144 * Fix for the bug where name lookup records for organizations would get removed by 2145 * unrelated updates of the data rows. 2146 */ 2147 private void upgradeToVersion300(SQLiteDatabase db) { 2148 final long mimeType = lookupMimeTypeId(db, Organization.CONTENT_ITEM_TYPE); 2149 if (mimeType == -1) { 2150 return; 2151 } 2152 2153 ContentValues values = new ContentValues(); 2154 2155 // Find all data rows with the mime type "organization" 2156 Cursor cursor = db.query(Organization300Query.TABLE, Organization300Query.COLUMNS, 2157 Organization300Query.SELECTION, new String[] {String.valueOf(mimeType)}, 2158 null, null, null); 2159 try { 2160 while (cursor.moveToNext()) { 2161 long dataId = cursor.getLong(Organization300Query.ID); 2162 long rawContactId = cursor.getLong(Organization300Query.RAW_CONTACT_ID); 2163 String company = cursor.getString(Organization300Query.COMPANY); 2164 String title = cursor.getString(Organization300Query.TITLE); 2165 2166 // First delete name lookup if there is any (chances are there won't be) 2167 db.delete(Tables.NAME_LOOKUP, NameLookupColumns.DATA_ID + "=?", 2168 new String[]{String.valueOf(dataId)}); 2169 2170 // Now insert two name lookup records: one for company name, one for title 2171 values.put(NameLookupColumns.DATA_ID, dataId); 2172 values.put(NameLookupColumns.RAW_CONTACT_ID, rawContactId); 2173 values.put(NameLookupColumns.NAME_TYPE, NameLookupType.ORGANIZATION); 2174 2175 if (!TextUtils.isEmpty(company)) { 2176 values.put(NameLookupColumns.NORMALIZED_NAME, 2177 NameNormalizer.normalize(company)); 2178 db.insert(Tables.NAME_LOOKUP, null, values); 2179 } 2180 2181 if (!TextUtils.isEmpty(title)) { 2182 values.put(NameLookupColumns.NORMALIZED_NAME, 2183 NameNormalizer.normalize(title)); 2184 db.insert(Tables.NAME_LOOKUP, null, values); 2185 } 2186 } 2187 } finally { 2188 cursor.close(); 2189 } 2190 } 2191 2192 private static final class Upgrade303Query { 2193 public static final String TABLE = Tables.DATA; 2194 2195 public static final String SELECTION = 2196 DataColumns.MIMETYPE_ID + "=?" + 2197 " AND " + Data._ID + " NOT IN " + 2198 "(SELECT " + NameLookupColumns.DATA_ID + " FROM " + Tables.NAME_LOOKUP + ")" + 2199 " AND " + Data.DATA1 + " NOT NULL"; 2200 2201 public static final String COLUMNS[] = { 2202 Data._ID, 2203 Data.RAW_CONTACT_ID, 2204 Data.DATA1, 2205 }; 2206 2207 public static final int ID = 0; 2208 public static final int RAW_CONTACT_ID = 1; 2209 public static final int DATA1 = 2; 2210 } 2211 2212 /** 2213 * The {@link ContactsProvider2#update} method was deleting name lookup for new 2214 * emails during the sync. We need to restore the lost name lookup rows. 2215 */ 2216 private void upgradeEmailToVersion303(SQLiteDatabase db) { 2217 final long mimeTypeId = lookupMimeTypeId(db, Email.CONTENT_ITEM_TYPE); 2218 if (mimeTypeId == -1) { 2219 return; 2220 } 2221 2222 ContentValues values = new ContentValues(); 2223 2224 // Find all data rows with the mime type "email" that are missing name lookup 2225 Cursor cursor = db.query(Upgrade303Query.TABLE, Upgrade303Query.COLUMNS, 2226 Upgrade303Query.SELECTION, new String[] {String.valueOf(mimeTypeId)}, 2227 null, null, null); 2228 try { 2229 while (cursor.moveToNext()) { 2230 long dataId = cursor.getLong(Upgrade303Query.ID); 2231 long rawContactId = cursor.getLong(Upgrade303Query.RAW_CONTACT_ID); 2232 String value = cursor.getString(Upgrade303Query.DATA1); 2233 value = extractHandleFromEmailAddress(value); 2234 2235 if (value != null) { 2236 values.put(NameLookupColumns.DATA_ID, dataId); 2237 values.put(NameLookupColumns.RAW_CONTACT_ID, rawContactId); 2238 values.put(NameLookupColumns.NAME_TYPE, NameLookupType.EMAIL_BASED_NICKNAME); 2239 values.put(NameLookupColumns.NORMALIZED_NAME, NameNormalizer.normalize(value)); 2240 db.insert(Tables.NAME_LOOKUP, null, values); 2241 } 2242 } 2243 } finally { 2244 cursor.close(); 2245 } 2246 } 2247 2248 /** 2249 * The {@link ContactsProvider2#update} method was deleting name lookup for new 2250 * nicknames during the sync. We need to restore the lost name lookup rows. 2251 */ 2252 private void upgradeNicknameToVersion303(SQLiteDatabase db) { 2253 final long mimeTypeId = lookupMimeTypeId(db, Nickname.CONTENT_ITEM_TYPE); 2254 if (mimeTypeId == -1) { 2255 return; 2256 } 2257 2258 ContentValues values = new ContentValues(); 2259 2260 // Find all data rows with the mime type "nickname" that are missing name lookup 2261 Cursor cursor = db.query(Upgrade303Query.TABLE, Upgrade303Query.COLUMNS, 2262 Upgrade303Query.SELECTION, new String[] {String.valueOf(mimeTypeId)}, 2263 null, null, null); 2264 try { 2265 while (cursor.moveToNext()) { 2266 long dataId = cursor.getLong(Upgrade303Query.ID); 2267 long rawContactId = cursor.getLong(Upgrade303Query.RAW_CONTACT_ID); 2268 String value = cursor.getString(Upgrade303Query.DATA1); 2269 2270 values.put(NameLookupColumns.DATA_ID, dataId); 2271 values.put(NameLookupColumns.RAW_CONTACT_ID, rawContactId); 2272 values.put(NameLookupColumns.NAME_TYPE, NameLookupType.NICKNAME); 2273 values.put(NameLookupColumns.NORMALIZED_NAME, NameNormalizer.normalize(value)); 2274 db.insert(Tables.NAME_LOOKUP, null, values); 2275 } 2276 } finally { 2277 cursor.close(); 2278 } 2279 } 2280 2281 private void upgradeToVersion304(SQLiteDatabase db) { 2282 // Mimetype table requires an index on mime type 2283 db.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS mime_type ON " + Tables.MIMETYPES + " (" + 2284 MimetypesColumns.MIMETYPE + 2285 ");"); 2286 } 2287 2288 private void upgradeToVersion306(SQLiteDatabase db) { 2289 // Fix invalid lookup that was used for Exchange contacts (it was not escaped) 2290 // It happened when a new contact was created AND synchronized 2291 final StringBuilder lookupKeyBuilder = new StringBuilder(); 2292 final SQLiteStatement updateStatement = db.compileStatement( 2293 "UPDATE contacts " + 2294 "SET lookup=? " + 2295 "WHERE _id=?"); 2296 final Cursor contactIdCursor = db.rawQuery( 2297 "SELECT DISTINCT contact_id " + 2298 "FROM raw_contacts " + 2299 "WHERE deleted=0 AND account_type='com.android.exchange'", 2300 null); 2301 try { 2302 while (contactIdCursor.moveToNext()) { 2303 final long contactId = contactIdCursor.getLong(0); 2304 lookupKeyBuilder.setLength(0); 2305 final Cursor c = db.rawQuery( 2306 "SELECT account_type, account_name, _id, sourceid, display_name " + 2307 "FROM raw_contacts " + 2308 "WHERE contact_id=? " + 2309 "ORDER BY _id", 2310 new String[] { String.valueOf(contactId) }); 2311 try { 2312 while (c.moveToNext()) { 2313 ContactLookupKey.appendToLookupKey(lookupKeyBuilder, 2314 c.getString(0), 2315 c.getString(1), 2316 c.getLong(2), 2317 c.getString(3), 2318 c.getString(4)); 2319 } 2320 } finally { 2321 c.close(); 2322 } 2323 2324 if (lookupKeyBuilder.length() == 0) { 2325 updateStatement.bindNull(1); 2326 } else { 2327 updateStatement.bindString(1, Uri.encode(lookupKeyBuilder.toString())); 2328 } 2329 updateStatement.bindLong(2, contactId); 2330 2331 updateStatement.execute(); 2332 } 2333 } finally { 2334 updateStatement.close(); 2335 contactIdCursor.close(); 2336 } 2337 } 2338 2339 private void upgradeToVersion307(SQLiteDatabase db) { 2340 db.execSQL("CREATE TABLE properties (" + 2341 "property_key TEXT PRIMARY_KEY, " + 2342 "property_value TEXT" + 2343 ");"); 2344 } 2345 2346 private void upgradeToVersion308(SQLiteDatabase db) { 2347 db.execSQL("CREATE TABLE accounts (" + 2348 "account_name TEXT, " + 2349 "account_type TEXT " + 2350 ");"); 2351 2352 db.execSQL("INSERT INTO accounts " + 2353 "SELECT DISTINCT account_name, account_type FROM raw_contacts"); 2354 } 2355 2356 private void upgradeToVersion400(SQLiteDatabase db) { 2357 db.execSQL("ALTER TABLE " + Tables.GROUPS 2358 + " ADD " + Groups.FAVORITES + " INTEGER NOT NULL DEFAULT 0;"); 2359 db.execSQL("ALTER TABLE " + Tables.GROUPS 2360 + " ADD " + Groups.AUTO_ADD + " INTEGER NOT NULL DEFAULT 0;"); 2361 } 2362 2363 private void upgradeToVersion353(SQLiteDatabase db) { 2364 db.execSQL("DELETE FROM contacts " + 2365 "WHERE NOT EXISTS (SELECT 1 FROM raw_contacts WHERE contact_id=contacts._id)"); 2366 } 2367 2368 private void rebuildNameLookup(SQLiteDatabase db) { 2369 db.execSQL("DROP INDEX IF EXISTS name_lookup_index"); 2370 insertNameLookup(db); 2371 createContactsIndexes(db); 2372 } 2373 2374 /** 2375 * Regenerates all locale-sensitive data: nickname_lookup, name_lookup and sort keys. 2376 */ 2377 public void setLocale(ContactsProvider2 provider, Locale locale) { 2378 Log.i(TAG, "Switching to locale " + locale); 2379 2380 long start = SystemClock.uptimeMillis(); 2381 SQLiteDatabase db = getWritableDatabase(); 2382 db.setLocale(locale); 2383 db.beginTransaction(); 2384 try { 2385 db.execSQL("DROP INDEX raw_contact_sort_key1_index"); 2386 db.execSQL("DROP INDEX raw_contact_sort_key2_index"); 2387 db.execSQL("DROP INDEX IF EXISTS name_lookup_index"); 2388 2389 loadNicknameLookupTable(db); 2390 insertNameLookup(db); 2391 rebuildSortKeys(db, provider); 2392 createContactsIndexes(db); 2393 db.setTransactionSuccessful(); 2394 } finally { 2395 db.endTransaction(); 2396 } 2397 2398 Log.i(TAG, "Locale change completed in " + (SystemClock.uptimeMillis() - start) + "ms"); 2399 } 2400 2401 /** 2402 * Regenerates sort keys for all contacts. 2403 */ 2404 private void rebuildSortKeys(SQLiteDatabase db, ContactsProvider2 provider) { 2405 Cursor cursor = db.query(Tables.RAW_CONTACTS, new String[]{RawContacts._ID}, 2406 null, null, null, null, null); 2407 try { 2408 while (cursor.moveToNext()) { 2409 long rawContactId = cursor.getLong(0); 2410 provider.updateRawContactDisplayName(db, rawContactId); 2411 } 2412 } finally { 2413 cursor.close(); 2414 } 2415 } 2416 2417 private void insertNameLookup(SQLiteDatabase db) { 2418 db.execSQL("DELETE FROM " + Tables.NAME_LOOKUP); 2419 2420 SQLiteStatement nameLookupInsert = db.compileStatement( 2421 "INSERT OR IGNORE INTO " + Tables.NAME_LOOKUP + "(" 2422 + NameLookupColumns.RAW_CONTACT_ID + "," 2423 + NameLookupColumns.DATA_ID + "," 2424 + NameLookupColumns.NAME_TYPE + "," 2425 + NameLookupColumns.NORMALIZED_NAME + 2426 ") VALUES (?,?,?,?)"); 2427 2428 try { 2429 insertStructuredNameLookup(db, nameLookupInsert); 2430 insertOrganizationLookup(db, nameLookupInsert); 2431 insertEmailLookup(db, nameLookupInsert); 2432 insertNicknameLookup(db, nameLookupInsert); 2433 } finally { 2434 nameLookupInsert.close(); 2435 } 2436 } 2437 2438 private static final class StructuredNameQuery { 2439 public static final String TABLE = Tables.DATA; 2440 2441 public static final String SELECTION = 2442 DataColumns.MIMETYPE_ID + "=? AND " + Data.DATA1 + " NOT NULL"; 2443 2444 public static final String COLUMNS[] = { 2445 StructuredName._ID, 2446 StructuredName.RAW_CONTACT_ID, 2447 StructuredName.DISPLAY_NAME, 2448 }; 2449 2450 public static final int ID = 0; 2451 public static final int RAW_CONTACT_ID = 1; 2452 public static final int DISPLAY_NAME = 2; 2453 } 2454 2455 private class StructuredNameLookupBuilder extends NameLookupBuilder { 2456 2457 private final SQLiteStatement mNameLookupInsert; 2458 private final CommonNicknameCache mCommonNicknameCache; 2459 2460 public StructuredNameLookupBuilder(NameSplitter splitter, 2461 CommonNicknameCache commonNicknameCache, SQLiteStatement nameLookupInsert) { 2462 super(splitter); 2463 this.mCommonNicknameCache = commonNicknameCache; 2464 this.mNameLookupInsert = nameLookupInsert; 2465 } 2466 2467 @Override 2468 protected void insertNameLookup(long rawContactId, long dataId, int lookupType, 2469 String name) { 2470 if (!TextUtils.isEmpty(name)) { 2471 ContactsDatabaseHelper.this.insertNormalizedNameLookup(mNameLookupInsert, 2472 rawContactId, dataId, lookupType, name); 2473 } 2474 } 2475 2476 @Override 2477 protected String[] getCommonNicknameClusters(String normalizedName) { 2478 return mCommonNicknameCache.getCommonNicknameClusters(normalizedName); 2479 } 2480 } 2481 2482 /** 2483 * Inserts name lookup rows for all structured names in the database. 2484 */ 2485 private void insertStructuredNameLookup(SQLiteDatabase db, SQLiteStatement nameLookupInsert) { 2486 NameSplitter nameSplitter = createNameSplitter(); 2487 NameLookupBuilder nameLookupBuilder = new StructuredNameLookupBuilder(nameSplitter, 2488 new CommonNicknameCache(db), nameLookupInsert); 2489 final long mimeTypeId = lookupMimeTypeId(db, StructuredName.CONTENT_ITEM_TYPE); 2490 Cursor cursor = db.query(StructuredNameQuery.TABLE, StructuredNameQuery.COLUMNS, 2491 StructuredNameQuery.SELECTION, new String[] {String.valueOf(mimeTypeId)}, 2492 null, null, null); 2493 try { 2494 while (cursor.moveToNext()) { 2495 long dataId = cursor.getLong(StructuredNameQuery.ID); 2496 long rawContactId = cursor.getLong(StructuredNameQuery.RAW_CONTACT_ID); 2497 String name = cursor.getString(StructuredNameQuery.DISPLAY_NAME); 2498 int fullNameStyle = nameSplitter.guessFullNameStyle(name); 2499 fullNameStyle = nameSplitter.getAdjustedFullNameStyle(fullNameStyle); 2500 nameLookupBuilder.insertNameLookup(rawContactId, dataId, name, fullNameStyle); 2501 } 2502 } finally { 2503 cursor.close(); 2504 } 2505 } 2506 2507 private static final class OrganizationQuery { 2508 public static final String TABLE = Tables.DATA; 2509 2510 public static final String SELECTION = 2511 DataColumns.MIMETYPE_ID + "=? AND " + Data.DATA1 + " NOT NULL"; 2512 2513 public static final String COLUMNS[] = { 2514 Organization._ID, 2515 Organization.RAW_CONTACT_ID, 2516 Organization.COMPANY, 2517 Organization.TITLE, 2518 }; 2519 2520 public static final int ID = 0; 2521 public static final int RAW_CONTACT_ID = 1; 2522 public static final int COMPANY = 2; 2523 public static final int TITLE = 3; 2524 } 2525 2526 /** 2527 * Inserts name lookup rows for all organizations in the database. 2528 */ 2529 private void insertOrganizationLookup(SQLiteDatabase db, SQLiteStatement nameLookupInsert) { 2530 final long mimeTypeId = lookupMimeTypeId(db, Organization.CONTENT_ITEM_TYPE); 2531 Cursor cursor = db.query(OrganizationQuery.TABLE, OrganizationQuery.COLUMNS, 2532 OrganizationQuery.SELECTION, new String[] {String.valueOf(mimeTypeId)}, 2533 null, null, null); 2534 try { 2535 while (cursor.moveToNext()) { 2536 long dataId = cursor.getLong(OrganizationQuery.ID); 2537 long rawContactId = cursor.getLong(OrganizationQuery.RAW_CONTACT_ID); 2538 String organization = cursor.getString(OrganizationQuery.COMPANY); 2539 String title = cursor.getString(OrganizationQuery.TITLE); 2540 insertNameLookup(nameLookupInsert, rawContactId, dataId, 2541 NameLookupType.ORGANIZATION, organization); 2542 insertNameLookup(nameLookupInsert, rawContactId, dataId, 2543 NameLookupType.ORGANIZATION, title); 2544 } 2545 } finally { 2546 cursor.close(); 2547 } 2548 } 2549 2550 private static final class EmailQuery { 2551 public static final String TABLE = Tables.DATA; 2552 2553 public static final String SELECTION = 2554 DataColumns.MIMETYPE_ID + "=? AND " + Data.DATA1 + " NOT NULL"; 2555 2556 public static final String COLUMNS[] = { 2557 Email._ID, 2558 Email.RAW_CONTACT_ID, 2559 Email.ADDRESS, 2560 }; 2561 2562 public static final int ID = 0; 2563 public static final int RAW_CONTACT_ID = 1; 2564 public static final int ADDRESS = 2; 2565 } 2566 2567 /** 2568 * Inserts name lookup rows for all email addresses in the database. 2569 */ 2570 private void insertEmailLookup(SQLiteDatabase db, SQLiteStatement nameLookupInsert) { 2571 final long mimeTypeId = lookupMimeTypeId(db, Email.CONTENT_ITEM_TYPE); 2572 Cursor cursor = db.query(EmailQuery.TABLE, EmailQuery.COLUMNS, 2573 EmailQuery.SELECTION, new String[] {String.valueOf(mimeTypeId)}, 2574 null, null, null); 2575 try { 2576 while (cursor.moveToNext()) { 2577 long dataId = cursor.getLong(EmailQuery.ID); 2578 long rawContactId = cursor.getLong(EmailQuery.RAW_CONTACT_ID); 2579 String address = cursor.getString(EmailQuery.ADDRESS); 2580 address = extractHandleFromEmailAddress(address); 2581 insertNameLookup(nameLookupInsert, rawContactId, dataId, 2582 NameLookupType.EMAIL_BASED_NICKNAME, address); 2583 } 2584 } finally { 2585 cursor.close(); 2586 } 2587 } 2588 2589 private static final class NicknameQuery { 2590 public static final String TABLE = Tables.DATA; 2591 2592 public static final String SELECTION = 2593 DataColumns.MIMETYPE_ID + "=? AND " + Data.DATA1 + " NOT NULL"; 2594 2595 public static final String COLUMNS[] = { 2596 Nickname._ID, 2597 Nickname.RAW_CONTACT_ID, 2598 Nickname.NAME, 2599 }; 2600 2601 public static final int ID = 0; 2602 public static final int RAW_CONTACT_ID = 1; 2603 public static final int NAME = 2; 2604 } 2605 2606 /** 2607 * Inserts name lookup rows for all nicknames in the database. 2608 */ 2609 private void insertNicknameLookup(SQLiteDatabase db, SQLiteStatement nameLookupInsert) { 2610 final long mimeTypeId = lookupMimeTypeId(db, Nickname.CONTENT_ITEM_TYPE); 2611 Cursor cursor = db.query(NicknameQuery.TABLE, NicknameQuery.COLUMNS, 2612 NicknameQuery.SELECTION, new String[] {String.valueOf(mimeTypeId)}, 2613 null, null, null); 2614 try { 2615 while (cursor.moveToNext()) { 2616 long dataId = cursor.getLong(NicknameQuery.ID); 2617 long rawContactId = cursor.getLong(NicknameQuery.RAW_CONTACT_ID); 2618 String nickname = cursor.getString(NicknameQuery.NAME); 2619 insertNameLookup(nameLookupInsert, rawContactId, dataId, 2620 NameLookupType.NICKNAME, nickname); 2621 } 2622 } finally { 2623 cursor.close(); 2624 } 2625 } 2626 2627 /** 2628 * Inserts a record in the {@link Tables#NAME_LOOKUP} table. 2629 */ 2630 public void insertNameLookup(SQLiteStatement stmt, long rawContactId, long dataId, 2631 int lookupType, String name) { 2632 if (TextUtils.isEmpty(name)) { 2633 return; 2634 } 2635 2636 String normalized = NameNormalizer.normalize(name); 2637 if (TextUtils.isEmpty(normalized)) { 2638 return; 2639 } 2640 2641 insertNormalizedNameLookup(stmt, rawContactId, dataId, lookupType, normalized); 2642 } 2643 2644 private void insertNormalizedNameLookup(SQLiteStatement stmt, long rawContactId, long dataId, 2645 int lookupType, String normalizedName) { 2646 stmt.bindLong(1, rawContactId); 2647 stmt.bindLong(2, dataId); 2648 stmt.bindLong(3, lookupType); 2649 stmt.bindString(4, normalizedName); 2650 stmt.executeInsert(); 2651 } 2652 2653 /** 2654 * Changing the VISIBLE bit from a field on both RawContacts and Contacts to a separate table. 2655 */ 2656 private void upgradeToVersion401(SQLiteDatabase db) { 2657 db.execSQL("CREATE TABLE " + Tables.VISIBLE_CONTACTS + " (" + 2658 Contacts._ID + " INTEGER PRIMARY KEY" + 2659 ");"); 2660 db.execSQL("INSERT INTO " + Tables.VISIBLE_CONTACTS + 2661 " SELECT " + Contacts._ID + 2662 " FROM " + Tables.CONTACTS + 2663 " WHERE " + Contacts.IN_VISIBLE_GROUP + "!=0"); 2664 db.execSQL("DROP INDEX contacts_visible_index"); 2665 } 2666 2667 /** 2668 * Introducing a new table: directories. 2669 */ 2670 private void upgradeToVersion402(SQLiteDatabase db) { 2671 createDirectoriesTable(db); 2672 } 2673 2674 private void upgradeToVersion403(SQLiteDatabase db) { 2675 db.execSQL("DROP TABLE IF EXISTS directories;"); 2676 createDirectoriesTable(db); 2677 2678 db.execSQL("ALTER TABLE raw_contacts" 2679 + " ADD raw_contact_is_read_only INTEGER NOT NULL DEFAULT 0;"); 2680 2681 db.execSQL("ALTER TABLE data" 2682 + " ADD is_read_only INTEGER NOT NULL DEFAULT 0;"); 2683 } 2684 2685 private void upgradeToVersion405(SQLiteDatabase db) { 2686 db.execSQL("DROP TABLE IF EXISTS phone_lookup;"); 2687 // Private phone numbers table used for lookup 2688 db.execSQL("CREATE TABLE " + Tables.PHONE_LOOKUP + " (" + 2689 PhoneLookupColumns.DATA_ID 2690 + " INTEGER REFERENCES data(_id) NOT NULL," + 2691 PhoneLookupColumns.RAW_CONTACT_ID 2692 + " INTEGER REFERENCES raw_contacts(_id) NOT NULL," + 2693 PhoneLookupColumns.NORMALIZED_NUMBER + " TEXT NOT NULL," + 2694 PhoneLookupColumns.MIN_MATCH + " TEXT NOT NULL" + 2695 ");"); 2696 2697 db.execSQL("CREATE INDEX phone_lookup_index ON " + Tables.PHONE_LOOKUP + " (" + 2698 PhoneLookupColumns.NORMALIZED_NUMBER + "," + 2699 PhoneLookupColumns.RAW_CONTACT_ID + "," + 2700 PhoneLookupColumns.DATA_ID + 2701 ");"); 2702 2703 db.execSQL("CREATE INDEX phone_lookup_min_match_index ON " + Tables.PHONE_LOOKUP + " (" + 2704 PhoneLookupColumns.MIN_MATCH + "," + 2705 PhoneLookupColumns.RAW_CONTACT_ID + "," + 2706 PhoneLookupColumns.DATA_ID + 2707 ");"); 2708 2709 final long mimeTypeId = lookupMimeTypeId(db, Phone.CONTENT_ITEM_TYPE); 2710 if (mimeTypeId == -1) { 2711 return; 2712 } 2713 2714 String mCountryIso = getCountryIso(); 2715 Cursor cursor = db.rawQuery( 2716 "SELECT _id, " + Phone.RAW_CONTACT_ID + ", " + Phone.NUMBER + 2717 " FROM " + Tables.DATA + 2718 " WHERE " + DataColumns.MIMETYPE_ID + "=" + mimeTypeId 2719 + " AND " + Phone.NUMBER + " NOT NULL", null); 2720 2721 ContentValues phoneValues = new ContentValues(); 2722 try { 2723 while (cursor.moveToNext()) { 2724 long dataID = cursor.getLong(0); 2725 long rawContactID = cursor.getLong(1); 2726 String number = cursor.getString(2); 2727 String numberE164 = PhoneNumberUtils.formatNumberToE164(number, mCountryIso); 2728 String normalizedNumber = PhoneNumberUtils.normalizeNumber(number); 2729 if (!TextUtils.isEmpty(normalizedNumber)) { 2730 phoneValues.clear(); 2731 phoneValues.put(PhoneLookupColumns.RAW_CONTACT_ID, rawContactID); 2732 phoneValues.put(PhoneLookupColumns.DATA_ID, dataID); 2733 phoneValues.put(PhoneLookupColumns.NORMALIZED_NUMBER, normalizedNumber); 2734 phoneValues.put(PhoneLookupColumns.MIN_MATCH, 2735 PhoneNumberUtils.toCallerIDMinMatch(normalizedNumber)); 2736 db.insert(Tables.PHONE_LOOKUP, null, phoneValues); 2737 2738 if (numberE164 != null && !numberE164.equals(normalizedNumber)) { 2739 phoneValues.put(PhoneLookupColumns.NORMALIZED_NUMBER, numberE164); 2740 phoneValues.put(PhoneLookupColumns.MIN_MATCH, 2741 PhoneNumberUtils.toCallerIDMinMatch(numberE164)); 2742 db.insert(Tables.PHONE_LOOKUP, null, phoneValues); 2743 } 2744 } 2745 } 2746 } finally { 2747 cursor.close(); 2748 } 2749 } 2750 2751 private void upgradeToVersion406(SQLiteDatabase db) { 2752 db.execSQL("ALTER TABLE calls ADD countryiso TEXT;"); 2753 } 2754 2755 private void upgradeToVersion409(SQLiteDatabase db) { 2756 db.execSQL("DROP TABLE IF EXISTS directories;"); 2757 createDirectoriesTable(db); 2758 } 2759 2760 /** 2761 * Adding DEFAULT_DIRECTORY table. 2762 */ 2763 private void upgradeToVersion411(SQLiteDatabase db) { 2764 db.execSQL("DROP TABLE IF EXISTS " + Tables.DEFAULT_DIRECTORY); 2765 db.execSQL("CREATE TABLE " + Tables.DEFAULT_DIRECTORY + " (" + 2766 Contacts._ID + " INTEGER PRIMARY KEY" + 2767 ");"); 2768 2769 // Process contacts without an account 2770 db.execSQL("INSERT OR IGNORE INTO " + Tables.DEFAULT_DIRECTORY + 2771 " SELECT " + RawContacts.CONTACT_ID + 2772 " FROM " + Tables.RAW_CONTACTS + 2773 " WHERE " + RawContactsColumns.CONCRETE_ACCOUNT_NAME + " IS NULL " + 2774 " AND " + RawContactsColumns.CONCRETE_ACCOUNT_TYPE + " IS NULL "); 2775 2776 // Process accounts that don't have a default group (e.g. Exchange) 2777 db.execSQL("INSERT OR IGNORE INTO " + Tables.DEFAULT_DIRECTORY + 2778 " SELECT " + RawContacts.CONTACT_ID + 2779 " FROM " + Tables.RAW_CONTACTS + 2780 " WHERE NOT EXISTS" + 2781 " (SELECT " + Groups._ID + 2782 " FROM " + Tables.GROUPS + 2783 " WHERE " + RawContactsColumns.CONCRETE_ACCOUNT_NAME + " = " 2784 + GroupsColumns.CONCRETE_ACCOUNT_NAME + 2785 " AND " + RawContactsColumns.CONCRETE_ACCOUNT_TYPE + " = " 2786 + GroupsColumns.CONCRETE_ACCOUNT_TYPE + 2787 " AND " + Groups.AUTO_ADD + " != 0" + 2788 ")"); 2789 2790 long mimetype = lookupMimeTypeId(db, GroupMembership.CONTENT_ITEM_TYPE); 2791 2792 // Process accounts that do have a default group (e.g. Google) 2793 db.execSQL("INSERT OR IGNORE INTO " + Tables.DEFAULT_DIRECTORY + 2794 " SELECT " + RawContacts.CONTACT_ID + 2795 " FROM " + Tables.RAW_CONTACTS + 2796 " JOIN " + Tables.DATA + 2797 " ON (" + RawContactsColumns.CONCRETE_ID + "=" + Data.RAW_CONTACT_ID + ")" + 2798 " WHERE " + DataColumns.MIMETYPE_ID + "=" + mimetype + 2799 " AND EXISTS" + 2800 " (SELECT " + Groups._ID + 2801 " FROM " + Tables.GROUPS + 2802 " WHERE " + RawContactsColumns.CONCRETE_ACCOUNT_NAME + " = " 2803 + GroupsColumns.CONCRETE_ACCOUNT_NAME + 2804 " AND " + RawContactsColumns.CONCRETE_ACCOUNT_TYPE + " = " 2805 + GroupsColumns.CONCRETE_ACCOUNT_TYPE + 2806 " AND " + Groups.AUTO_ADD + " != 0" + 2807 ")"); 2808 } 2809 2810 private void upgradeToVersion413(SQLiteDatabase db) { 2811 db.execSQL("DROP TABLE IF EXISTS directories;"); 2812 createDirectoriesTable(db); 2813 } 2814 2815 private void upgradeToVersion415(SQLiteDatabase db) { 2816 db.execSQL( 2817 "ALTER TABLE " + Tables.GROUPS + 2818 " ADD " + Groups.GROUP_IS_READ_ONLY + " INTEGER NOT NULL DEFAULT 0"); 2819 db.execSQL( 2820 "UPDATE " + Tables.GROUPS + 2821 " SET " + Groups.GROUP_IS_READ_ONLY + "=1" + 2822 " WHERE " + Groups.SYSTEM_ID + " NOT NULL"); 2823 } 2824 2825 public String extractHandleFromEmailAddress(String email) { 2826 Rfc822Token[] tokens = Rfc822Tokenizer.tokenize(email); 2827 if (tokens.length == 0) { 2828 return null; 2829 } 2830 2831 String address = tokens[0].getAddress(); 2832 int at = address.indexOf('@'); 2833 if (at != -1) { 2834 return address.substring(0, at); 2835 } 2836 return null; 2837 } 2838 2839 public String extractAddressFromEmailAddress(String email) { 2840 Rfc822Token[] tokens = Rfc822Tokenizer.tokenize(email); 2841 if (tokens.length == 0) { 2842 return null; 2843 } 2844 2845 return tokens[0].getAddress(); 2846 } 2847 2848 private long lookupMimeTypeId(SQLiteDatabase db, String mimeType) { 2849 try { 2850 return DatabaseUtils.longForQuery(db, 2851 "SELECT " + MimetypesColumns._ID + 2852 " FROM " + Tables.MIMETYPES + 2853 " WHERE " + MimetypesColumns.MIMETYPE 2854 + "='" + mimeType + "'", null); 2855 } catch (SQLiteDoneException e) { 2856 // No rows of this type in the database 2857 return -1; 2858 } 2859 } 2860 2861 private void bindString(SQLiteStatement stmt, int index, String value) { 2862 if (value == null) { 2863 stmt.bindNull(index); 2864 } else { 2865 stmt.bindString(index, value); 2866 } 2867 } 2868 2869 private void bindLong(SQLiteStatement stmt, int index, Number value) { 2870 if (value == null) { 2871 stmt.bindNull(index); 2872 } else { 2873 stmt.bindLong(index, value.longValue()); 2874 } 2875 } 2876 2877 /** 2878 * Adds index stats into the SQLite database to force it to always use the lookup indexes. 2879 */ 2880 private void updateSqliteStats(SQLiteDatabase db) { 2881 2882 // Specific stats strings are based on an actual large database after running ANALYZE 2883 try { 2884 updateIndexStats(db, Tables.CONTACTS, 2885 "contacts_restricted_index", "10000 9000"); 2886 updateIndexStats(db, Tables.CONTACTS, 2887 "contacts_has_phone_index", "10000 500"); 2888 2889 updateIndexStats(db, Tables.RAW_CONTACTS, 2890 "raw_contacts_source_id_index", "10000 1 1 1"); 2891 updateIndexStats(db, Tables.RAW_CONTACTS, 2892 "raw_contacts_contact_id_index", "10000 2"); 2893 2894 updateIndexStats(db, Tables.NAME_LOOKUP, 2895 "name_lookup_raw_contact_id_index", "10000 3"); 2896 updateIndexStats(db, Tables.NAME_LOOKUP, 2897 "name_lookup_index", "10000 3 2 2 1"); 2898 updateIndexStats(db, Tables.NAME_LOOKUP, 2899 "sqlite_autoindex_name_lookup_1", "10000 3 2 1"); 2900 2901 updateIndexStats(db, Tables.PHONE_LOOKUP, 2902 "phone_lookup_index", "10000 2 2 1"); 2903 updateIndexStats(db, Tables.PHONE_LOOKUP, 2904 "phone_lookup_min_match_index", "10000 2 2 1"); 2905 2906 updateIndexStats(db, Tables.DATA, 2907 "data_mimetype_data1_index", "60000 5000 2"); 2908 updateIndexStats(db, Tables.DATA, 2909 "data_raw_contact_id", "60000 10"); 2910 2911 updateIndexStats(db, Tables.GROUPS, 2912 "groups_source_id_index", "50 1 1 1"); 2913 2914 updateIndexStats(db, Tables.NICKNAME_LOOKUP, 2915 "sqlite_autoindex_name_lookup_1", "500 2 1"); 2916 2917 } catch (SQLException e) { 2918 Log.e(TAG, "Could not update index stats", e); 2919 } 2920 } 2921 2922 /** 2923 * Stores statistics for a given index. 2924 * 2925 * @param stats has the following structure: the first index is the expected size of 2926 * the table. The following integer(s) are the expected number of records selected with the 2927 * index. There should be one integer per indexed column. 2928 */ 2929 private void updateIndexStats(SQLiteDatabase db, String table, String index, 2930 String stats) { 2931 db.execSQL("DELETE FROM sqlite_stat1 WHERE tbl='" + table + "' AND idx='" + index + "';"); 2932 db.execSQL("INSERT INTO sqlite_stat1 (tbl,idx,stat)" 2933 + " VALUES ('" + table + "','" + index + "','" + stats + "');"); 2934 } 2935 2936 @Override 2937 public synchronized SQLiteDatabase getWritableDatabase() { 2938 SQLiteDatabase db = super.getWritableDatabase(); 2939 if (mReopenDatabase) { 2940 mReopenDatabase = false; 2941 close(); 2942 db = super.getWritableDatabase(); 2943 } 2944 return db; 2945 } 2946 2947 /** 2948 * Wipes all data except mime type and package lookup tables. 2949 */ 2950 public void wipeData() { 2951 SQLiteDatabase db = getWritableDatabase(); 2952 2953 db.execSQL("DELETE FROM " + Tables.ACCOUNTS + ";"); 2954 db.execSQL("INSERT INTO " + Tables.ACCOUNTS + " VALUES(NULL, NULL)"); 2955 2956 db.execSQL("DELETE FROM " + Tables.CONTACTS + ";"); 2957 db.execSQL("DELETE FROM " + Tables.RAW_CONTACTS + ";"); 2958 db.execSQL("DELETE FROM " + Tables.DATA + ";"); 2959 db.execSQL("DELETE FROM " + Tables.PHONE_LOOKUP + ";"); 2960 db.execSQL("DELETE FROM " + Tables.NAME_LOOKUP + ";"); 2961 db.execSQL("DELETE FROM " + Tables.GROUPS + ";"); 2962 db.execSQL("DELETE FROM " + Tables.AGGREGATION_EXCEPTIONS + ";"); 2963 db.execSQL("DELETE FROM " + Tables.SETTINGS + ";"); 2964 db.execSQL("DELETE FROM " + Tables.ACTIVITIES + ";"); 2965 db.execSQL("DELETE FROM " + Tables.CALLS + ";"); 2966 db.execSQL("DELETE FROM " + Tables.DIRECTORIES + ";"); 2967 2968 // Note: we are not removing reference data from Tables.NICKNAME_LOOKUP 2969 } 2970 2971 public NameSplitter createNameSplitter() { 2972 return new NameSplitter( 2973 mContext.getString(com.android.internal.R.string.common_name_prefixes), 2974 mContext.getString(com.android.internal.R.string.common_last_name_prefixes), 2975 mContext.getString(com.android.internal.R.string.common_name_suffixes), 2976 mContext.getString(com.android.internal.R.string.common_name_conjunctions), 2977 Locale.getDefault()); 2978 } 2979 2980 /** 2981 * Return the {@link ApplicationInfo#uid} for the given package name. 2982 */ 2983 public static int getUidForPackageName(PackageManager pm, String packageName) { 2984 try { 2985 ApplicationInfo clientInfo = pm.getApplicationInfo(packageName, 0 /* no flags */); 2986 return clientInfo.uid; 2987 } catch (NameNotFoundException e) { 2988 throw new RuntimeException(e); 2989 } 2990 } 2991 2992 /** 2993 * Perform an internal string-to-integer lookup using the compiled 2994 * {@link SQLiteStatement} provided, using the in-memory cache to speed up 2995 * lookups. If a mapping isn't found in cache or database, it will be 2996 * created. All new, uncached answers are added to the cache automatically. 2997 * 2998 * @param query Compiled statement used to query for the mapping. 2999 * @param insert Compiled statement used to insert a new mapping when no 3000 * existing one is found in cache or from query. 3001 * @param value Value to find mapping for. 3002 * @param cache In-memory cache of previous answers. 3003 * @return An unique integer mapping for the given value. 3004 */ 3005 private long getCachedId(SQLiteStatement query, SQLiteStatement insert, 3006 String value, HashMap<String, Long> cache) { 3007 // Try an in-memory cache lookup 3008 if (cache.containsKey(value)) { 3009 return cache.get(value); 3010 } 3011 3012 long id = -1; 3013 try { 3014 // Try searching database for mapping 3015 DatabaseUtils.bindObjectToProgram(query, 1, value); 3016 id = query.simpleQueryForLong(); 3017 } catch (SQLiteDoneException e) { 3018 // Nothing found, so try inserting new mapping 3019 DatabaseUtils.bindObjectToProgram(insert, 1, value); 3020 id = insert.executeInsert(); 3021 } 3022 3023 if (id != -1) { 3024 // Cache and return the new answer 3025 cache.put(value, id); 3026 return id; 3027 } else { 3028 // Otherwise throw if no mapping found or created 3029 throw new IllegalStateException("Couldn't find or create internal " 3030 + "lookup table entry for value " + value); 3031 } 3032 } 3033 3034 /** 3035 * Convert a package name into an integer, using {@link Tables#PACKAGES} for 3036 * lookups and possible allocation of new IDs as needed. 3037 */ 3038 public long getPackageId(String packageName) { 3039 if (mPackageQuery == null) { 3040 mPackageQuery = getWritableDatabase().compileStatement( 3041 "SELECT " + PackagesColumns._ID + 3042 " FROM " + Tables.PACKAGES + 3043 " WHERE " + PackagesColumns.PACKAGE + "=?"); 3044 3045 } 3046 if (mPackageInsert == null) { 3047 mPackageInsert = getWritableDatabase().compileStatement( 3048 "INSERT INTO " + Tables.PACKAGES + "(" 3049 + PackagesColumns.PACKAGE + 3050 ") VALUES (?)"); 3051 } 3052 return getCachedId(mPackageQuery, mPackageInsert, packageName, mPackageCache); 3053 } 3054 3055 /** 3056 * Convert a mimetype into an integer, using {@link Tables#MIMETYPES} for 3057 * lookups and possible allocation of new IDs as needed. 3058 */ 3059 public long getMimeTypeId(String mimetype) { 3060 return getCachedId(mMimetypeQuery, mMimetypeInsert, mimetype, mMimetypeCache); 3061 } 3062 3063 public long getMimeTypeIdForStructuredName() { 3064 return mMimeTypeIdStructuredName; 3065 } 3066 3067 public long getMimeTypeIdForOrganization() { 3068 return mMimeTypeIdOrganization; 3069 } 3070 3071 public long getMimeTypeIdForIm() { 3072 return mMimeTypeIdIm; 3073 } 3074 3075 public long getMimeTypeIdForEmail() { 3076 return mMimeTypeIdEmail; 3077 } 3078 3079 public int getDisplayNameSourceForMimeTypeId(int mimeTypeId) { 3080 if (mimeTypeId == mMimeTypeIdStructuredName) { 3081 return DisplayNameSources.STRUCTURED_NAME; 3082 } else if (mimeTypeId == mMimeTypeIdEmail) { 3083 return DisplayNameSources.EMAIL; 3084 } else if (mimeTypeId == mMimeTypeIdPhone) { 3085 return DisplayNameSources.PHONE; 3086 } else if (mimeTypeId == mMimeTypeIdOrganization) { 3087 return DisplayNameSources.ORGANIZATION; 3088 } else if (mimeTypeId == mMimeTypeIdNickname) { 3089 return DisplayNameSources.NICKNAME; 3090 } else { 3091 return DisplayNameSources.UNDEFINED; 3092 } 3093 } 3094 3095 /** 3096 * Find the mimetype for the given {@link Data#_ID}. 3097 */ 3098 public String getDataMimeType(long dataId) { 3099 if (mDataMimetypeQuery == null) { 3100 mDataMimetypeQuery = getWritableDatabase().compileStatement( 3101 "SELECT " + MimetypesColumns.MIMETYPE + 3102 " FROM " + Tables.DATA_JOIN_MIMETYPES + 3103 " WHERE " + Tables.DATA + "." + Data._ID + "=?"); 3104 } 3105 try { 3106 // Try database query to find mimetype 3107 DatabaseUtils.bindObjectToProgram(mDataMimetypeQuery, 1, dataId); 3108 String mimetype = mDataMimetypeQuery.simpleQueryForString(); 3109 return mimetype; 3110 } catch (SQLiteDoneException e) { 3111 // No valid mapping found, so return null 3112 return null; 3113 } 3114 } 3115 3116 /** 3117 * Find the mime-type for the given {@link Activities#_ID}. 3118 */ 3119 public String getActivityMimeType(long activityId) { 3120 if (mActivitiesMimetypeQuery == null) { 3121 mActivitiesMimetypeQuery = getWritableDatabase().compileStatement( 3122 "SELECT " + MimetypesColumns.MIMETYPE + 3123 " FROM " + Tables.ACTIVITIES_JOIN_MIMETYPES + 3124 " WHERE " + Tables.ACTIVITIES + "." + Activities._ID + "=?"); 3125 } 3126 try { 3127 // Try database query to find mimetype 3128 DatabaseUtils.bindObjectToProgram(mActivitiesMimetypeQuery, 1, activityId); 3129 String mimetype = mActivitiesMimetypeQuery.simpleQueryForString(); 3130 return mimetype; 3131 } catch (SQLiteDoneException e) { 3132 // No valid mapping found, so return null 3133 return null; 3134 } 3135 } 3136 3137 /** 3138 * Update {@link Contacts#IN_VISIBLE_GROUP} for all contacts. 3139 */ 3140 public void updateAllVisible() { 3141 updateCustomContactVisibility(getWritableDatabase(), ""); 3142 } 3143 3144 /** 3145 * Update {@link Contacts#IN_VISIBLE_GROUP} and 3146 * {@link Tables#DEFAULT_DIRECTORY} for a specific contact. 3147 */ 3148 public void updateContactVisible(long contactId) { 3149 SQLiteDatabase db = getWritableDatabase(); 3150 updateCustomContactVisibility(getWritableDatabase(), 3151 " AND " + Contacts._ID + "=" + contactId); 3152 3153 String contactIdAsString = String.valueOf(contactId); 3154 long mimetype = getMimeTypeId(GroupMembership.CONTENT_ITEM_TYPE); 3155 3156 // The contact will be included in the default directory if contains 3157 // a raw contact that is in any group or in an account that 3158 // does not have any AUTO_ADD groups. 3159 long visibleRawContact = DatabaseUtils.longForQuery(db, 3160 "SELECT EXISTS (" + 3161 "SELECT " + RawContacts.CONTACT_ID + 3162 " FROM " + Tables.RAW_CONTACTS + 3163 " JOIN " + Tables.DATA + 3164 " ON (" + RawContactsColumns.CONCRETE_ID + "=" 3165 + Data.RAW_CONTACT_ID + ")" + 3166 " WHERE " + RawContacts.CONTACT_ID + "=?" + 3167 " AND " + DataColumns.MIMETYPE_ID + "=?" + 3168 ") OR EXISTS (" + 3169 "SELECT " + RawContacts._ID + 3170 " FROM " + Tables.RAW_CONTACTS + 3171 " WHERE " + RawContacts.CONTACT_ID + "=?" + 3172 " AND NOT EXISTS" + 3173 " (SELECT " + Groups._ID + 3174 " FROM " + Tables.GROUPS + 3175 " WHERE " + RawContactsColumns.CONCRETE_ACCOUNT_NAME + " = " 3176 + GroupsColumns.CONCRETE_ACCOUNT_NAME + 3177 " AND " + RawContactsColumns.CONCRETE_ACCOUNT_TYPE + " = " 3178 + GroupsColumns.CONCRETE_ACCOUNT_TYPE + 3179 " AND " + Groups.AUTO_ADD + " != 0" + 3180 ")" + 3181 ") OR EXISTS (" + 3182 "SELECT " + RawContacts._ID + 3183 " FROM " + Tables.RAW_CONTACTS + 3184 " WHERE " + RawContacts.CONTACT_ID + "=?" + 3185 " AND " + RawContactsColumns.CONCRETE_ACCOUNT_NAME + " IS NULL " + 3186 " AND " + RawContactsColumns.CONCRETE_ACCOUNT_TYPE + " IS NULL" + 3187 ")", 3188 new String[] { 3189 contactIdAsString, 3190 String.valueOf(mimetype), 3191 contactIdAsString, 3192 contactIdAsString 3193 }); 3194 3195 if (visibleRawContact != 0) { 3196 db.execSQL("INSERT OR IGNORE INTO " + Tables.DEFAULT_DIRECTORY + " VALUES(?)", 3197 new String[] { contactIdAsString }); 3198 } else { 3199 db.execSQL("DELETE FROM " + Tables.DEFAULT_DIRECTORY + " WHERE " + Contacts._ID + "=?", 3200 new String[] { contactIdAsString }); 3201 } 3202 } 3203 3204 private void updateCustomContactVisibility(SQLiteDatabase db, String selection) { 3205 final long groupMembershipMimetypeId = getMimeTypeId(GroupMembership.CONTENT_ITEM_TYPE); 3206 String[] selectionArgs = new String[]{String.valueOf(groupMembershipMimetypeId)}; 3207 3208 // First delete what needs to be deleted, then insert what needs to be added. 3209 // Since flash writes are very expensive, this approach is much better than 3210 // delete-all-insert-all. 3211 db.execSQL("DELETE FROM " + Tables.VISIBLE_CONTACTS + 3212 " WHERE " + "_id NOT IN" + 3213 "(SELECT " + Contacts._ID + 3214 " FROM " + Tables.CONTACTS + 3215 " WHERE (" + Clauses.CONTACT_IS_VISIBLE + ")=1) " + selection, 3216 selectionArgs); 3217 3218 db.execSQL("INSERT INTO " + Tables.VISIBLE_CONTACTS + 3219 " SELECT " + Contacts._ID + 3220 " FROM " + Tables.CONTACTS + 3221 " WHERE " + Contacts._ID + 3222 " NOT IN " + Tables.VISIBLE_CONTACTS + 3223 " AND (" + Clauses.CONTACT_IS_VISIBLE + ")=1 " + selection, 3224 selectionArgs); 3225 } 3226 3227 /** 3228 * Returns contact ID for the given contact or zero if it is NULL. 3229 */ 3230 public long getContactId(long rawContactId) { 3231 if (mContactIdQuery == null) { 3232 mContactIdQuery = getWritableDatabase().compileStatement( 3233 "SELECT " + RawContacts.CONTACT_ID + 3234 " FROM " + Tables.RAW_CONTACTS + 3235 " WHERE " + RawContacts._ID + "=?"); 3236 } 3237 try { 3238 DatabaseUtils.bindObjectToProgram(mContactIdQuery, 1, rawContactId); 3239 return mContactIdQuery.simpleQueryForLong(); 3240 } catch (SQLiteDoneException e) { 3241 // No valid mapping found, so return 0 3242 return 0; 3243 } 3244 } 3245 3246 public int getAggregationMode(long rawContactId) { 3247 if (mAggregationModeQuery == null) { 3248 mAggregationModeQuery = getWritableDatabase().compileStatement( 3249 "SELECT " + RawContacts.AGGREGATION_MODE + 3250 " FROM " + Tables.RAW_CONTACTS + 3251 " WHERE " + RawContacts._ID + "=?"); 3252 } 3253 try { 3254 DatabaseUtils.bindObjectToProgram(mAggregationModeQuery, 1, rawContactId); 3255 return (int)mAggregationModeQuery.simpleQueryForLong(); 3256 } catch (SQLiteDoneException e) { 3257 // No valid row found, so return "disabled" 3258 return RawContacts.AGGREGATION_MODE_DISABLED; 3259 } 3260 } 3261 3262 public void buildPhoneLookupAndContactQuery( 3263 SQLiteQueryBuilder qb, String normalizedNumber, String numberE164) { 3264 String minMatch = PhoneNumberUtils.toCallerIDMinMatch(normalizedNumber); 3265 StringBuilder sb = new StringBuilder(); 3266 appendPhoneLookupTables(sb, minMatch, true); 3267 qb.setTables(sb.toString()); 3268 3269 sb = new StringBuilder(); 3270 appendPhoneLookupSelection(sb, normalizedNumber, numberE164); 3271 qb.appendWhere(sb.toString()); 3272 } 3273 3274 public String buildPhoneLookupAsNestedQuery(String number) { 3275 StringBuilder sb = new StringBuilder(); 3276 final String minMatch = PhoneNumberUtils.toCallerIDMinMatch(number); 3277 sb.append("(SELECT DISTINCT raw_contact_id" + " FROM "); 3278 appendPhoneLookupTables(sb, minMatch, false); 3279 sb.append(" WHERE "); 3280 appendPhoneLookupSelection(sb, number, null); 3281 sb.append(")"); 3282 return sb.toString(); 3283 } 3284 3285 private void appendPhoneLookupTables(StringBuilder sb, final String minMatch, 3286 boolean joinContacts) { 3287 sb.append(Tables.RAW_CONTACTS); 3288 if (joinContacts) { 3289 sb.append(" JOIN " + getContactView() + " contacts_view" 3290 + " ON (contacts_view._id = raw_contacts.contact_id)"); 3291 } 3292 sb.append(", (SELECT data_id, normalized_number, length(normalized_number) as len " 3293 + " FROM phone_lookup " + " WHERE (" + Tables.PHONE_LOOKUP + "." 3294 + PhoneLookupColumns.MIN_MATCH + " = '"); 3295 sb.append(minMatch); 3296 sb.append("')) AS lookup, " + Tables.DATA); 3297 } 3298 3299 private void appendPhoneLookupSelection(StringBuilder sb, String number, String numberE164) { 3300 sb.append("lookup.data_id=data._id AND data.raw_contact_id=raw_contacts._id"); 3301 boolean hasNumberE164 = !TextUtils.isEmpty(numberE164); 3302 boolean hasNumber = !TextUtils.isEmpty(number); 3303 if (hasNumberE164 || hasNumber) { 3304 sb.append(" AND ( "); 3305 if (hasNumberE164) { 3306 sb.append(" lookup.normalized_number = "); 3307 DatabaseUtils.appendEscapedSQLString(sb, numberE164); 3308 } 3309 if (hasNumberE164 && hasNumber) { 3310 sb.append(" OR "); 3311 } 3312 if (hasNumber) { 3313 int numberLen = number.length(); 3314 sb.append(" lookup.len <= "); 3315 sb.append(numberLen); 3316 sb.append(" AND substr("); 3317 DatabaseUtils.appendEscapedSQLString(sb, number); 3318 sb.append(','); 3319 sb.append(numberLen); 3320 sb.append(" - lookup.len + 1) = lookup.normalized_number"); 3321 } 3322 sb.append(')'); 3323 } 3324 } 3325 3326 public String getUseStrictPhoneNumberComparisonParameter() { 3327 return mUseStrictPhoneNumberComparison ? "1" : "0"; 3328 } 3329 3330 /** 3331 * Loads common nickname mappings into the database. 3332 */ 3333 private void loadNicknameLookupTable(SQLiteDatabase db) { 3334 db.execSQL("DELETE FROM " + Tables.NICKNAME_LOOKUP); 3335 3336 String[] strings = mContext.getResources().getStringArray( 3337 com.android.internal.R.array.common_nicknames); 3338 if (strings == null || strings.length == 0) { 3339 return; 3340 } 3341 3342 SQLiteStatement nicknameLookupInsert = db.compileStatement("INSERT INTO " 3343 + Tables.NICKNAME_LOOKUP + "(" + NicknameLookupColumns.NAME + "," 3344 + NicknameLookupColumns.CLUSTER + ") VALUES (?,?)"); 3345 3346 try { 3347 for (int clusterId = 0; clusterId < strings.length; clusterId++) { 3348 String[] names = strings[clusterId].split(","); 3349 for (int j = 0; j < names.length; j++) { 3350 String name = NameNormalizer.normalize(names[j]); 3351 try { 3352 DatabaseUtils.bindObjectToProgram(nicknameLookupInsert, 1, name); 3353 DatabaseUtils.bindObjectToProgram(nicknameLookupInsert, 2, 3354 String.valueOf(clusterId)); 3355 nicknameLookupInsert.executeInsert(); 3356 } catch (SQLiteException e) { 3357 3358 // Print the exception and keep going - this is not a fatal error 3359 Log.e(TAG, "Cannot insert nickname: " + names[j], e); 3360 } 3361 } 3362 } 3363 } finally { 3364 nicknameLookupInsert.close(); 3365 } 3366 } 3367 3368 public static void copyStringValue(ContentValues toValues, String toKey, 3369 ContentValues fromValues, String fromKey) { 3370 if (fromValues.containsKey(fromKey)) { 3371 toValues.put(toKey, fromValues.getAsString(fromKey)); 3372 } 3373 } 3374 3375 public static void copyLongValue(ContentValues toValues, String toKey, 3376 ContentValues fromValues, String fromKey) { 3377 if (fromValues.containsKey(fromKey)) { 3378 long longValue; 3379 Object value = fromValues.get(fromKey); 3380 if (value instanceof Boolean) { 3381 if ((Boolean)value) { 3382 longValue = 1; 3383 } else { 3384 longValue = 0; 3385 } 3386 } else if (value instanceof String) { 3387 longValue = Long.parseLong((String)value); 3388 } else { 3389 longValue = ((Number)value).longValue(); 3390 } 3391 toValues.put(toKey, longValue); 3392 } 3393 } 3394 3395 public SyncStateContentProviderHelper getSyncState() { 3396 return mSyncState; 3397 } 3398 3399 /** 3400 * Delete the aggregate contact if it has no constituent raw contacts other 3401 * than the supplied one. 3402 */ 3403 public void removeContactIfSingleton(long rawContactId) { 3404 SQLiteDatabase db = getWritableDatabase(); 3405 3406 // Obtain contact ID from the supplied raw contact ID 3407 String contactIdFromRawContactId = "(SELECT " + RawContacts.CONTACT_ID + " FROM " 3408 + Tables.RAW_CONTACTS + " WHERE " + RawContacts._ID + "=" + rawContactId + ")"; 3409 3410 // Find other raw contacts in the same aggregate contact 3411 String otherRawContacts = "(SELECT contacts1." + RawContacts._ID + " FROM " 3412 + Tables.RAW_CONTACTS + " contacts1 JOIN " + Tables.RAW_CONTACTS + " contacts2 ON (" 3413 + "contacts1." + RawContacts.CONTACT_ID + "=contacts2." + RawContacts.CONTACT_ID 3414 + ") WHERE contacts1." + RawContacts._ID + "!=" + rawContactId + "" 3415 + " AND contacts2." + RawContacts._ID + "=" + rawContactId + ")"; 3416 3417 db.execSQL("DELETE FROM " + Tables.CONTACTS 3418 + " WHERE " + Contacts._ID + "=" + contactIdFromRawContactId 3419 + " AND NOT EXISTS " + otherRawContacts + ";"); 3420 } 3421 3422 /** 3423 * Returns the value from the {@link Tables#PROPERTIES} table. 3424 */ 3425 public String getProperty(String key, String defaultValue) { 3426 Cursor cursor = getReadableDatabase().query(Tables.PROPERTIES, 3427 new String[]{PropertiesColumns.PROPERTY_VALUE}, 3428 PropertiesColumns.PROPERTY_KEY + "=?", 3429 new String[]{key}, null, null, null); 3430 String value = null; 3431 try { 3432 if (cursor.moveToFirst()) { 3433 value = cursor.getString(0); 3434 } 3435 } finally { 3436 cursor.close(); 3437 } 3438 3439 return value != null ? value : defaultValue; 3440 } 3441 3442 /** 3443 * Stores a key-value pair in the {@link Tables#PROPERTIES} table. 3444 */ 3445 public void setProperty(String key, String value) { 3446 setProperty(getWritableDatabase(), key, value); 3447 } 3448 3449 private void setProperty(SQLiteDatabase db, String key, String value) { 3450 ContentValues values = new ContentValues(); 3451 values.put(PropertiesColumns.PROPERTY_KEY, key); 3452 values.put(PropertiesColumns.PROPERTY_VALUE, value); 3453 db.replace(Tables.PROPERTIES, null, values); 3454 } 3455 3456 /** 3457 * Check if {@link Binder#getCallingUid()} should be allowed access to 3458 * {@link RawContacts#IS_RESTRICTED} data. 3459 */ 3460 boolean hasAccessToRestrictedData() { 3461 final PackageManager pm = mContext.getPackageManager(); 3462 int caller = Binder.getCallingUid(); 3463 if (caller == 0) return true; // root can do anything 3464 final String[] callerPackages = pm.getPackagesForUid(caller); 3465 3466 // Has restricted access if caller matches any packages 3467 for (String callerPackage : callerPackages) { 3468 if (hasAccessToRestrictedData(callerPackage)) { 3469 return true; 3470 } 3471 } 3472 return false; 3473 } 3474 3475 /** 3476 * Check if requestingPackage should be allowed access to 3477 * {@link RawContacts#IS_RESTRICTED} data. 3478 */ 3479 boolean hasAccessToRestrictedData(String requestingPackage) { 3480 if (mUnrestrictedPackages != null) { 3481 for (String allowedPackage : mUnrestrictedPackages) { 3482 if (allowedPackage.equals(requestingPackage)) { 3483 return true; 3484 } 3485 } 3486 } 3487 return false; 3488 } 3489 3490 public String getDataView() { 3491 return getDataView(false); 3492 } 3493 3494 public String getDataView(boolean requireRestrictedView) { 3495 return (hasAccessToRestrictedData() && !requireRestrictedView) ? 3496 Views.DATA_ALL : Views.DATA_RESTRICTED; 3497 } 3498 3499 public String getRawContactView() { 3500 return getRawContactView(false); 3501 } 3502 3503 public String getRawContactView(boolean requireRestrictedView) { 3504 return (hasAccessToRestrictedData() && !requireRestrictedView) ? 3505 Views.RAW_CONTACTS_ALL : Views.RAW_CONTACTS_RESTRICTED; 3506 } 3507 3508 public String getContactView() { 3509 return getContactView(false); 3510 } 3511 3512 public String getContactView(boolean requireRestrictedView) { 3513 return (hasAccessToRestrictedData() && !requireRestrictedView) ? 3514 Views.CONTACTS_ALL : Views.CONTACTS_RESTRICTED; 3515 } 3516 3517 public String getGroupView() { 3518 return Views.GROUPS_ALL; 3519 } 3520 3521 public String getRawEntitiesView() { 3522 return getRawEntitiesView(false); 3523 } 3524 3525 public String getRawEntitiesView(boolean requireRestrictedView) { 3526 return (hasAccessToRestrictedData() && !requireRestrictedView) ? 3527 Views.RAW_ENTITIES : Views.RAW_ENTITIES_RESTRICTED; 3528 } 3529 3530 public String getEntitiesView() { 3531 return getEntitiesView(false); 3532 } 3533 3534 public String getEntitiesView(boolean requireRestrictedView) { 3535 return (hasAccessToRestrictedData() && !requireRestrictedView) ? 3536 Views.ENTITIES : Views.ENTITIES_RESTRICTED; 3537 } 3538 3539 /** 3540 * Test if any of the columns appear in the given projection. 3541 */ 3542 public boolean isInProjection(String[] projection, String... columns) { 3543 if (projection == null) { 3544 return true; 3545 } 3546 3547 // Optimized for a single-column test 3548 if (columns.length == 1) { 3549 String column = columns[0]; 3550 for (String test : projection) { 3551 if (column.equals(test)) { 3552 return true; 3553 } 3554 } 3555 } else { 3556 for (String test : projection) { 3557 for (String column : columns) { 3558 if (column.equals(test)) { 3559 return true; 3560 } 3561 } 3562 } 3563 } 3564 return false; 3565 } 3566 3567 /** 3568 * Returns a detailed exception message for the supplied URI. It includes the calling 3569 * user and calling package(s). 3570 */ 3571 public String exceptionMessage(Uri uri) { 3572 return exceptionMessage(null, uri); 3573 } 3574 3575 /** 3576 * Returns a detailed exception message for the supplied URI. It includes the calling 3577 * user and calling package(s). 3578 */ 3579 public String exceptionMessage(String message, Uri uri) { 3580 StringBuilder sb = new StringBuilder(); 3581 if (message != null) { 3582 sb.append(message).append("; "); 3583 } 3584 sb.append("URI: ").append(uri); 3585 final PackageManager pm = mContext.getPackageManager(); 3586 int callingUid = Binder.getCallingUid(); 3587 sb.append(", calling user: "); 3588 String userName = pm.getNameForUid(callingUid); 3589 if (userName != null) { 3590 sb.append(userName); 3591 } else { 3592 sb.append(callingUid); 3593 } 3594 3595 final String[] callerPackages = pm.getPackagesForUid(callingUid); 3596 if (callerPackages != null && callerPackages.length > 0) { 3597 if (callerPackages.length == 1) { 3598 sb.append(", calling package:"); 3599 sb.append(callerPackages[0]); 3600 } else { 3601 sb.append(", calling package is one of: ["); 3602 for (int i = 0; i < callerPackages.length; i++) { 3603 if (i != 0) { 3604 sb.append(", "); 3605 } 3606 sb.append(callerPackages[i]); 3607 } 3608 sb.append("]"); 3609 } 3610 } 3611 3612 return sb.toString(); 3613 } 3614 3615 protected String getCountryIso() { 3616 CountryDetector detector = 3617 (CountryDetector) mContext.getSystemService(Context.COUNTRY_DETECTOR); 3618 return detector.detectCountry().getCountryIso(); 3619 } 3620 3621 public void deleteStatusUpdate(long dataId) { 3622 if (mStatusUpdateDelete == null) { 3623 mStatusUpdateDelete = getWritableDatabase().compileStatement( 3624 "DELETE FROM " + Tables.STATUS_UPDATES + 3625 " WHERE " + StatusUpdatesColumns.DATA_ID + "=?"); 3626 } 3627 mStatusUpdateDelete.bindLong(1, dataId); 3628 mStatusUpdateDelete.execute(); 3629 } 3630 3631 public void replaceStatusUpdate(Long dataId, long timestamp, String status, String resPackage, 3632 Long iconResource, Integer labelResource) { 3633 if (mStatusUpdateReplace == null) { 3634 mStatusUpdateReplace = getWritableDatabase().compileStatement( 3635 "INSERT OR REPLACE INTO " + Tables.STATUS_UPDATES + "(" 3636 + StatusUpdatesColumns.DATA_ID + ", " 3637 + StatusUpdates.STATUS_TIMESTAMP + "," 3638 + StatusUpdates.STATUS + "," 3639 + StatusUpdates.STATUS_RES_PACKAGE + "," 3640 + StatusUpdates.STATUS_ICON + "," 3641 + StatusUpdates.STATUS_LABEL + ")" + 3642 " VALUES (?,?,?,?,?,?)"); 3643 } 3644 mStatusUpdateReplace.bindLong(1, dataId); 3645 mStatusUpdateReplace.bindLong(2, timestamp); 3646 bindString(mStatusUpdateReplace, 3, status); 3647 bindString(mStatusUpdateReplace, 4, resPackage); 3648 bindLong(mStatusUpdateReplace, 5, iconResource); 3649 bindLong(mStatusUpdateReplace, 6, labelResource); 3650 mStatusUpdateReplace.execute(); 3651 } 3652 3653 public void insertStatusUpdate(Long dataId, String status, String resPackage, Long iconResource, 3654 Integer labelResource) { 3655 if (mStatusUpdateInsert == null) { 3656 mStatusUpdateInsert = getWritableDatabase().compileStatement( 3657 "INSERT INTO " + Tables.STATUS_UPDATES + "(" 3658 + StatusUpdatesColumns.DATA_ID + ", " 3659 + StatusUpdates.STATUS + "," 3660 + StatusUpdates.STATUS_RES_PACKAGE + "," 3661 + StatusUpdates.STATUS_ICON + "," 3662 + StatusUpdates.STATUS_LABEL + ")" + 3663 " VALUES (?,?,?,?,?)"); 3664 } 3665 try { 3666 mStatusUpdateInsert.bindLong(1, dataId); 3667 bindString(mStatusUpdateInsert, 2, status); 3668 bindString(mStatusUpdateInsert, 3, resPackage); 3669 bindLong(mStatusUpdateInsert, 4, iconResource); 3670 bindLong(mStatusUpdateInsert, 5, labelResource); 3671 mStatusUpdateInsert.executeInsert(); 3672 } catch (SQLiteConstraintException e) { 3673 // The row already exists - update it 3674 if (mStatusUpdateAutoTimestamp == null) { 3675 mStatusUpdateAutoTimestamp = getWritableDatabase().compileStatement( 3676 "UPDATE " + Tables.STATUS_UPDATES + 3677 " SET " + StatusUpdates.STATUS_TIMESTAMP + "=?," 3678 + StatusUpdates.STATUS + "=?" + 3679 " WHERE " + StatusUpdatesColumns.DATA_ID + "=?" 3680 + " AND " + StatusUpdates.STATUS + "!=?"); 3681 } 3682 3683 long timestamp = System.currentTimeMillis(); 3684 mStatusUpdateAutoTimestamp.bindLong(1, timestamp); 3685 bindString(mStatusUpdateAutoTimestamp, 2, status); 3686 mStatusUpdateAutoTimestamp.bindLong(3, dataId); 3687 bindString(mStatusUpdateAutoTimestamp, 4, status); 3688 mStatusUpdateAutoTimestamp.execute(); 3689 3690 if (mStatusAttributionUpdate == null) { 3691 mStatusAttributionUpdate = getWritableDatabase().compileStatement( 3692 "UPDATE " + Tables.STATUS_UPDATES + 3693 " SET " + StatusUpdates.STATUS_RES_PACKAGE + "=?," 3694 + StatusUpdates.STATUS_ICON + "=?," 3695 + StatusUpdates.STATUS_LABEL + "=?" + 3696 " WHERE " + StatusUpdatesColumns.DATA_ID + "=?"); 3697 } 3698 bindString(mStatusAttributionUpdate, 1, resPackage); 3699 bindLong(mStatusAttributionUpdate, 2, iconResource); 3700 bindLong(mStatusAttributionUpdate, 3, labelResource); 3701 mStatusAttributionUpdate.bindLong(4, dataId); 3702 mStatusAttributionUpdate.execute(); 3703 } 3704 } 3705 3706 /** 3707 * Resets the {@link RawContacts#NAME_VERIFIED} flag to 0 on all other raw 3708 * contacts in the same aggregate 3709 */ 3710 public void resetNameVerifiedForOtherRawContacts(long rawContactId) { 3711 if (mResetNameVerifiedForOtherRawContacts == null) { 3712 mResetNameVerifiedForOtherRawContacts = getWritableDatabase().compileStatement( 3713 "UPDATE " + Tables.RAW_CONTACTS + 3714 " SET " + RawContacts.NAME_VERIFIED + "=0" + 3715 " WHERE " + RawContacts.CONTACT_ID + "=(" + 3716 "SELECT " + RawContacts.CONTACT_ID + 3717 " FROM " + Tables.RAW_CONTACTS + 3718 " WHERE " + RawContacts._ID + "=?)" + 3719 " AND " + RawContacts._ID + "!=?"); 3720 } 3721 mResetNameVerifiedForOtherRawContacts.bindLong(1, rawContactId); 3722 mResetNameVerifiedForOtherRawContacts.bindLong(2, rawContactId); 3723 mResetNameVerifiedForOtherRawContacts.execute(); 3724 } 3725 3726 public void setDisplayName(long rawContactId, int displayNameSource, 3727 String displayNamePrimary, String displayNameAlternative, String phoneticName, 3728 int phoneticNameStyle, String sortKeyPrimary, String sortKeyAlternative) { 3729 if (mRawContactDisplayNameUpdate == null) { 3730 mRawContactDisplayNameUpdate = getWritableDatabase().compileStatement( 3731 "UPDATE " + Tables.RAW_CONTACTS + 3732 " SET " + 3733 RawContacts.DISPLAY_NAME_SOURCE + "=?," + 3734 RawContacts.DISPLAY_NAME_PRIMARY + "=?," + 3735 RawContacts.DISPLAY_NAME_ALTERNATIVE + "=?," + 3736 RawContacts.PHONETIC_NAME + "=?," + 3737 RawContacts.PHONETIC_NAME_STYLE + "=?," + 3738 RawContacts.SORT_KEY_PRIMARY + "=?," + 3739 RawContacts.SORT_KEY_ALTERNATIVE + "=?" + 3740 " WHERE " + RawContacts._ID + "=?"); 3741 } 3742 mRawContactDisplayNameUpdate.bindLong(1, displayNameSource); 3743 bindString(mRawContactDisplayNameUpdate, 2, displayNamePrimary); 3744 bindString(mRawContactDisplayNameUpdate, 3, displayNameAlternative); 3745 bindString(mRawContactDisplayNameUpdate, 4, phoneticName); 3746 mRawContactDisplayNameUpdate.bindLong(5, phoneticNameStyle); 3747 bindString(mRawContactDisplayNameUpdate, 6, sortKeyPrimary); 3748 bindString(mRawContactDisplayNameUpdate, 7, sortKeyAlternative); 3749 mRawContactDisplayNameUpdate.bindLong(8, rawContactId); 3750 mRawContactDisplayNameUpdate.execute(); 3751 } 3752 3753 /* 3754 * Sets the given dataId record in the "data" table to primary, and resets all data records of 3755 * the same mimetype and under the same contact to not be primary. 3756 * 3757 * @param dataId the id of the data record to be set to primary. Pass -1 to clear the primary 3758 * flag of all data items of this raw contacts 3759 */ 3760 public void setIsPrimary(long rawContactId, long dataId, long mimeTypeId) { 3761 if (mSetPrimaryStatement == null) { 3762 mSetPrimaryStatement = getWritableDatabase().compileStatement( 3763 "UPDATE " + Tables.DATA + 3764 " SET " + Data.IS_PRIMARY + "=(_id=?)" + 3765 " WHERE " + DataColumns.MIMETYPE_ID + "=?" + 3766 " AND " + Data.RAW_CONTACT_ID + "=?"); 3767 } 3768 mSetPrimaryStatement.bindLong(1, dataId); 3769 mSetPrimaryStatement.bindLong(2, mimeTypeId); 3770 mSetPrimaryStatement.bindLong(3, rawContactId); 3771 mSetPrimaryStatement.execute(); 3772 } 3773 3774 /* 3775 * Clears the super primary of all data items of the given raw contact. does not touch 3776 * other raw contacts of the same joined aggregate 3777 */ 3778 public void clearSuperPrimary(long rawContactId, long mimeTypeId) { 3779 if (mClearSuperPrimaryStatement == null) { 3780 mClearSuperPrimaryStatement = getWritableDatabase().compileStatement( 3781 "UPDATE " + Tables.DATA + 3782 " SET " + Data.IS_SUPER_PRIMARY + "=0" + 3783 " WHERE " + DataColumns.MIMETYPE_ID + "=?" + 3784 " AND " + Data.RAW_CONTACT_ID + "=?"); 3785 } 3786 mClearSuperPrimaryStatement.bindLong(1, mimeTypeId); 3787 mClearSuperPrimaryStatement.bindLong(2, rawContactId); 3788 mClearSuperPrimaryStatement.execute(); 3789 } 3790 3791 /* 3792 * Sets the given dataId record in the "data" table to "super primary", and resets all data 3793 * records of the same mimetype and under the same aggregate to not be "super primary". 3794 * 3795 * @param dataId the id of the data record to be set to primary. 3796 */ 3797 public void setIsSuperPrimary(long rawContactId, long dataId, long mimeTypeId) { 3798 if (mSetSuperPrimaryStatement == null) { 3799 mSetSuperPrimaryStatement = getWritableDatabase().compileStatement( 3800 "UPDATE " + Tables.DATA + 3801 " SET " + Data.IS_SUPER_PRIMARY + "=(" + Data._ID + "=?)" + 3802 " WHERE " + DataColumns.MIMETYPE_ID + "=?" + 3803 " AND " + Data.RAW_CONTACT_ID + " IN (" + 3804 "SELECT " + RawContacts._ID + 3805 " FROM " + Tables.RAW_CONTACTS + 3806 " WHERE " + RawContacts.CONTACT_ID + " =(" + 3807 "SELECT " + RawContacts.CONTACT_ID + 3808 " FROM " + Tables.RAW_CONTACTS + 3809 " WHERE " + RawContacts._ID + "=?))"); 3810 } 3811 mSetSuperPrimaryStatement.bindLong(1, dataId); 3812 mSetSuperPrimaryStatement.bindLong(2, mimeTypeId); 3813 mSetSuperPrimaryStatement.bindLong(3, rawContactId); 3814 mSetSuperPrimaryStatement.execute(); 3815 } 3816 3817 /** 3818 * Inserts a record in the {@link Tables#NAME_LOOKUP} table. 3819 */ 3820 public void insertNameLookup(long rawContactId, long dataId, int lookupType, String name) { 3821 if (TextUtils.isEmpty(name)) { 3822 return; 3823 } 3824 3825 if (mNameLookupInsert == null) { 3826 mNameLookupInsert = getWritableDatabase().compileStatement( 3827 "INSERT OR IGNORE INTO " + Tables.NAME_LOOKUP + "(" 3828 + NameLookupColumns.RAW_CONTACT_ID + "," 3829 + NameLookupColumns.DATA_ID + "," 3830 + NameLookupColumns.NAME_TYPE + "," 3831 + NameLookupColumns.NORMALIZED_NAME 3832 + ") VALUES (?,?,?,?)"); 3833 } 3834 mNameLookupInsert.bindLong(1, rawContactId); 3835 mNameLookupInsert.bindLong(2, dataId); 3836 mNameLookupInsert.bindLong(3, lookupType); 3837 bindString(mNameLookupInsert, 4, name); 3838 mNameLookupInsert.executeInsert(); 3839 } 3840 3841 /** 3842 * Deletes all {@link Tables#NAME_LOOKUP} table rows associated with the specified data element. 3843 */ 3844 public void deleteNameLookup(long dataId) { 3845 if (mNameLookupDelete == null) { 3846 mNameLookupDelete = getWritableDatabase().compileStatement( 3847 "DELETE FROM " + Tables.NAME_LOOKUP + 3848 " WHERE " + NameLookupColumns.DATA_ID + "=?"); 3849 } 3850 mNameLookupDelete.bindLong(1, dataId); 3851 mNameLookupDelete.execute(); 3852 } 3853 3854 public void insertNameLookupForOrganization(long rawContactId, long dataId, String company, 3855 String title) { 3856 if (!TextUtils.isEmpty(company)) { 3857 insertNameLookup(rawContactId, dataId, 3858 NameLookupType.ORGANIZATION, NameNormalizer.normalize(company)); 3859 } 3860 if (!TextUtils.isEmpty(title)) { 3861 insertNameLookup(rawContactId, dataId, 3862 NameLookupType.ORGANIZATION, NameNormalizer.normalize(title)); 3863 } 3864 } 3865 3866 public String insertNameLookupForEmail(long rawContactId, long dataId, String email) { 3867 if (TextUtils.isEmpty(email)) { 3868 return null; 3869 } 3870 3871 String address = extractHandleFromEmailAddress(email); 3872 if (address == null) { 3873 return null; 3874 } 3875 3876 insertNameLookup(rawContactId, dataId, 3877 NameLookupType.EMAIL_BASED_NICKNAME, NameNormalizer.normalize(address)); 3878 return address; 3879 } 3880 3881 /** 3882 * Normalizes the nickname and inserts it in the name lookup table. 3883 */ 3884 public void insertNameLookupForNickname(long rawContactId, long dataId, String nickname) { 3885 if (TextUtils.isEmpty(nickname)) { 3886 return; 3887 } 3888 3889 insertNameLookup(rawContactId, dataId, 3890 NameLookupType.NICKNAME, NameNormalizer.normalize(nickname)); 3891 } 3892 3893 public void insertNameLookupForPhoneticName(long rawContactId, long dataId, String familyName, 3894 String middleName, String givenName) { 3895 mSb.setLength(0); 3896 if (familyName != null) { 3897 mSb.append(familyName.trim()); 3898 } 3899 if (middleName != null) { 3900 mSb.append(middleName.trim()); 3901 } 3902 if (givenName != null) { 3903 mSb.append(givenName.trim()); 3904 } 3905 3906 if (mSb.length() > 0) { 3907 insertNameLookup(rawContactId, dataId, NameLookupType.NAME_COLLATION_KEY, 3908 NameNormalizer.normalize(mSb.toString())); 3909 } 3910 3911 if (givenName != null) { 3912 // We want the phonetic given name to be used for search, but not for aggregation, 3913 // which is why we are using NAME_SHORTHAND rather than NAME_COLLATION_KEY 3914 insertNameLookup(rawContactId, dataId, NameLookupType.NAME_SHORTHAND, 3915 NameNormalizer.normalize(givenName.trim())); 3916 } 3917 } 3918 3919 /** 3920 * Performs a query and returns true if any Data item of the raw contact with the given 3921 * id and mimetype is marked as super-primary 3922 */ 3923 public boolean rawContactHasSuperPrimary(long rawContactId, long mimeTypeId) { 3924 final Cursor existsCursor = getReadableDatabase().rawQuery( 3925 "SELECT EXISTS(SELECT 1 FROM " + Tables.DATA + 3926 " WHERE " + Data.RAW_CONTACT_ID + "=?" + 3927 " AND " + DataColumns.MIMETYPE_ID + "=?" + 3928 " AND " + Data.IS_SUPER_PRIMARY + "<>0)", 3929 new String[] { String.valueOf(rawContactId), String.valueOf(mimeTypeId) }); 3930 try { 3931 if (!existsCursor.moveToFirst()) throw new IllegalStateException(); 3932 return existsCursor.getInt(0) != 0; 3933 } finally { 3934 existsCursor.close(); 3935 } 3936 } 3937 3938 public String getCurrentCountryIso() { 3939 return mCountryMonitor.getCountryIso(); 3940 } 3941} 3942