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