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