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