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 17 package com.android.providers.contacts; 18 19 import android.content.ContentResolver; 20 import android.content.ContentValues; 21 import android.content.Context; 22 import android.content.Intent; 23 import android.content.pm.ApplicationInfo; 24 import android.content.pm.PackageManager; 25 import android.content.pm.PackageManager.NameNotFoundException; 26 import android.content.res.Resources; 27 import android.database.CharArrayBuffer; 28 import android.database.Cursor; 29 import android.database.DatabaseUtils; 30 import android.database.SQLException; 31 import android.database.sqlite.SQLiteConstraintException; 32 import android.database.sqlite.SQLiteDatabase; 33 import android.database.sqlite.SQLiteDoneException; 34 import android.database.sqlite.SQLiteException; 35 import android.database.sqlite.SQLiteOpenHelper; 36 import android.database.sqlite.SQLiteQueryBuilder; 37 import android.database.sqlite.SQLiteStatement; 38 import android.location.CountryDetector; 39 import android.net.Uri; 40 import android.os.Binder; 41 import android.os.Bundle; 42 import android.os.SystemClock; 43 import android.provider.BaseColumns; 44 import android.provider.CallLog.Calls; 45 import android.provider.ContactsContract; 46 import android.provider.ContactsContract.AggregationExceptions; 47 import android.provider.ContactsContract.CommonDataKinds.Email; 48 import android.provider.ContactsContract.CommonDataKinds.GroupMembership; 49 import android.provider.ContactsContract.CommonDataKinds.Im; 50 import android.provider.ContactsContract.CommonDataKinds.Nickname; 51 import android.provider.ContactsContract.CommonDataKinds.Organization; 52 import android.provider.ContactsContract.CommonDataKinds.Phone; 53 import android.provider.ContactsContract.CommonDataKinds.SipAddress; 54 import android.provider.ContactsContract.CommonDataKinds.StructuredName; 55 import android.provider.ContactsContract.CommonDataKinds.StructuredPostal; 56 import android.provider.ContactsContract.Contacts; 57 import android.provider.ContactsContract.Contacts.Photo; 58 import android.provider.ContactsContract.Data; 59 import android.provider.ContactsContract.Directory; 60 import android.provider.ContactsContract.DisplayNameSources; 61 import android.provider.ContactsContract.DisplayPhoto; 62 import android.provider.ContactsContract.FullNameStyle; 63 import android.provider.ContactsContract.Groups; 64 import android.provider.ContactsContract.PhoneticNameStyle; 65 import android.provider.ContactsContract.PhotoFiles; 66 import android.provider.ContactsContract.RawContacts; 67 import android.provider.ContactsContract.Settings; 68 import android.provider.ContactsContract.StatusUpdates; 69 import android.provider.ContactsContract.StreamItemPhotos; 70 import android.provider.ContactsContract.StreamItems; 71 import android.provider.VoicemailContract; 72 import android.provider.VoicemailContract.Voicemails; 73 import android.telephony.PhoneNumberUtils; 74 import android.text.TextUtils; 75 import android.text.util.Rfc822Token; 76 import android.text.util.Rfc822Tokenizer; 77 import android.util.Log; 78 79 import com.android.common.content.SyncStateContentProviderHelper; 80 import com.android.providers.contacts.aggregation.util.CommonNicknameCache; 81 import com.android.providers.contacts.database.ContactsTableUtil; 82 import com.android.providers.contacts.database.DeletedContactsTableUtil; 83 import com.android.providers.contacts.database.MoreDatabaseUtils; 84 import com.android.providers.contacts.util.NeededForTesting; 85 import com.google.android.collect.Sets; 86 87 import java.util.HashMap; 88 import java.util.Locale; 89 import java.util.Set; 90 91 import libcore.icu.ICU; 92 93 /** 94 * Database helper for contacts. Designed as a singleton to make sure that all 95 * {@link android.content.ContentProvider} users get the same reference. 96 * Provides handy methods for maintaining package and mime-type lookup tables. 97 */ 98 public class ContactsDatabaseHelper extends SQLiteOpenHelper { 99 private static final String TAG = "ContactsDatabaseHelper"; 100 101 /** 102 * Contacts DB version ranges: 103 * <pre> 104 * 0-98 Cupcake/Donut 105 * 100-199 Eclair 106 * 200-299 Eclair-MR1 107 * 300-349 Froyo 108 * 350-399 Gingerbread 109 * 400-499 Honeycomb 110 * 500-549 Honeycomb-MR1 111 * 550-599 Honeycomb-MR2 112 * 600-699 Ice Cream Sandwich 113 * 700-799 Jelly Bean 114 * </pre> 115 */ 116 static final int DATABASE_VERSION = 710; 117 118 private static final String DATABASE_NAME = "contacts2.db"; 119 private static final String DATABASE_PRESENCE = "presence_db"; 120 121 public interface Tables { 122 public static final String CONTACTS = "contacts"; 123 public static final String DELETED_CONTACTS = "deleted_contacts"; 124 public static final String RAW_CONTACTS = "raw_contacts"; 125 public static final String STREAM_ITEMS = "stream_items"; 126 public static final String STREAM_ITEM_PHOTOS = "stream_item_photos"; 127 public static final String PHOTO_FILES = "photo_files"; 128 public static final String PACKAGES = "packages"; 129 public static final String MIMETYPES = "mimetypes"; 130 public static final String PHONE_LOOKUP = "phone_lookup"; 131 public static final String NAME_LOOKUP = "name_lookup"; 132 public static final String AGGREGATION_EXCEPTIONS = "agg_exceptions"; 133 public static final String SETTINGS = "settings"; 134 public static final String DATA = "data"; 135 public static final String GROUPS = "groups"; 136 public static final String PRESENCE = "presence"; 137 public static final String AGGREGATED_PRESENCE = "agg_presence"; 138 public static final String NICKNAME_LOOKUP = "nickname_lookup"; 139 public static final String CALLS = "calls"; 140 public static final String STATUS_UPDATES = "status_updates"; 141 public static final String PROPERTIES = "properties"; 142 public static final String ACCOUNTS = "accounts"; 143 public static final String VISIBLE_CONTACTS = "visible_contacts"; 144 public static final String DIRECTORIES = "directories"; 145 public static final String DEFAULT_DIRECTORY = "default_directory"; 146 public static final String SEARCH_INDEX = "search_index"; 147 public static final String VOICEMAIL_STATUS = "voicemail_status"; 148 149 // This list of tables contains auto-incremented sequences. 150 public static final String[] SEQUENCE_TABLES = new String[] { 151 CONTACTS, 152 RAW_CONTACTS, 153 STREAM_ITEMS, 154 STREAM_ITEM_PHOTOS, 155 PHOTO_FILES, 156 DATA, 157 GROUPS, 158 CALLS, 159 DIRECTORIES 160 }; 161 162 /** 163 * For {@link ContactsContract.DataUsageFeedback}. The table structure itself 164 * is not exposed outside. 165 */ 166 public static final String DATA_USAGE_STAT = "data_usage_stat"; 167 168 public static final String DATA_JOIN_MIMETYPES = "data " 169 + "JOIN mimetypes ON (data.mimetype_id = mimetypes._id)"; 170 171 public static final String DATA_JOIN_RAW_CONTACTS = "data " 172 + "JOIN raw_contacts ON (data.raw_contact_id = raw_contacts._id)"; 173 174 // NOTE: If you want to refer to account name/type/data_set, AccountsColumns.CONCRETE_XXX 175 // MUST be used, as upgraded raw_contacts may have the account info columns too. 176 public static final String DATA_JOIN_MIMETYPE_RAW_CONTACTS = "data " 177 + "JOIN mimetypes ON (data.mimetype_id = mimetypes._id) " 178 + "JOIN raw_contacts ON (data.raw_contact_id = raw_contacts._id)" 179 + " JOIN " + Tables.ACCOUNTS + " ON (" 180 + RawContactsColumns.CONCRETE_ACCOUNT_ID + "=" + AccountsColumns.CONCRETE_ID 181 + ")"; 182 183 // NOTE: This requires late binding of GroupMembership MIME-type 184 // TODO Consolidate settings and accounts 185 public static final String RAW_CONTACTS_JOIN_SETTINGS_DATA_GROUPS = Tables.RAW_CONTACTS 186 + " JOIN " + Tables.ACCOUNTS + " ON (" 187 + RawContactsColumns.CONCRETE_ACCOUNT_ID + "=" + AccountsColumns.CONCRETE_ID 188 + ")" 189 + "LEFT OUTER JOIN " + Tables.SETTINGS + " ON (" 190 + AccountsColumns.CONCRETE_ACCOUNT_NAME + "=" 191 + SettingsColumns.CONCRETE_ACCOUNT_NAME + " AND " 192 + AccountsColumns.CONCRETE_ACCOUNT_TYPE + "=" 193 + SettingsColumns.CONCRETE_ACCOUNT_TYPE + " AND " 194 + "((" + AccountsColumns.CONCRETE_DATA_SET + " IS NULL AND " 195 + SettingsColumns.CONCRETE_DATA_SET + " IS NULL) OR (" 196 + AccountsColumns.CONCRETE_DATA_SET + "=" 197 + SettingsColumns.CONCRETE_DATA_SET + "))) " 198 + "LEFT OUTER JOIN data ON (data.mimetype_id=? AND " 199 + "data.raw_contact_id = raw_contacts._id) " 200 + "LEFT OUTER JOIN groups ON (groups._id = data." + GroupMembership.GROUP_ROW_ID 201 + ")"; 202 203 // NOTE: This requires late binding of GroupMembership MIME-type 204 // TODO Add missing DATA_SET join -- or just consolidate settings and accounts 205 public static final String SETTINGS_JOIN_RAW_CONTACTS_DATA_MIMETYPES_CONTACTS = "settings " 206 + "LEFT OUTER JOIN raw_contacts ON (" 207 + RawContactsColumns.CONCRETE_ACCOUNT_ID + "=(SELECT " 208 + AccountsColumns.CONCRETE_ID 209 + " FROM " + Tables.ACCOUNTS 210 + " WHERE " 211 + "(" + AccountsColumns.CONCRETE_ACCOUNT_NAME 212 + "=" + SettingsColumns.CONCRETE_ACCOUNT_NAME + ") AND " 213 + "(" + AccountsColumns.CONCRETE_ACCOUNT_TYPE 214 + "=" + SettingsColumns.CONCRETE_ACCOUNT_TYPE + ")))" 215 + "LEFT OUTER JOIN data ON (data.mimetype_id=? AND " 216 + "data.raw_contact_id = raw_contacts._id) " 217 + "LEFT OUTER JOIN contacts ON (raw_contacts.contact_id = contacts._id)"; 218 219 public static final String CONTACTS_JOIN_RAW_CONTACTS_DATA_FILTERED_BY_GROUPMEMBERSHIP = 220 Tables.CONTACTS 221 + " INNER JOIN " + Tables.RAW_CONTACTS 222 + " ON (" + RawContactsColumns.CONCRETE_CONTACT_ID + "=" 223 + ContactsColumns.CONCRETE_ID 224 + ")" 225 + " INNER JOIN " + Tables.DATA 226 + " ON (" + DataColumns.CONCRETE_DATA1 + "=" + GroupsColumns.CONCRETE_ID 227 + " AND " 228 + DataColumns.CONCRETE_RAW_CONTACT_ID + "=" + RawContactsColumns.CONCRETE_ID 229 + " AND " 230 + DataColumns.CONCRETE_MIMETYPE_ID + "=" 231 + "(SELECT " + MimetypesColumns._ID 232 + " FROM " + Tables.MIMETYPES 233 + " WHERE " 234 + MimetypesColumns.CONCRETE_MIMETYPE + "=" 235 + "'" + GroupMembership.CONTENT_ITEM_TYPE + "'" 236 + ")" 237 + ")"; 238 239 // NOTE: If you want to refer to account name/type/data_set, AccountsColumns.CONCRETE_XXX 240 // MUST be used, as upgraded raw_contacts may have the account info columns too. 241 public static final String DATA_JOIN_PACKAGES_MIMETYPES_RAW_CONTACTS_GROUPS = "data " 242 + "JOIN mimetypes ON (data.mimetype_id = mimetypes._id) " 243 + "JOIN raw_contacts ON (data.raw_contact_id = raw_contacts._id) " 244 + " JOIN " + Tables.ACCOUNTS + " ON (" 245 + RawContactsColumns.CONCRETE_ACCOUNT_ID + "=" + AccountsColumns.CONCRETE_ID 246 + ")" 247 + "LEFT OUTER JOIN packages ON (data.package_id = packages._id) " 248 + "LEFT OUTER JOIN groups " 249 + " ON (mimetypes.mimetype='" + GroupMembership.CONTENT_ITEM_TYPE + "' " 250 + " AND groups._id = data." + GroupMembership.GROUP_ROW_ID + ") "; 251 252 public static final String ACTIVITIES_JOIN_MIMETYPES = "activities " 253 + "LEFT OUTER JOIN mimetypes ON (activities.mimetype_id = mimetypes._id)"; 254 255 public static final String ACTIVITIES_JOIN_PACKAGES_MIMETYPES_RAW_CONTACTS_CONTACTS = 256 "activities " 257 + "LEFT OUTER JOIN packages ON (activities.package_id = packages._id) " 258 + "LEFT OUTER JOIN mimetypes ON (activities.mimetype_id = mimetypes._id) " 259 + "LEFT OUTER JOIN raw_contacts ON (activities.author_contact_id = " + 260 "raw_contacts._id) " 261 + "LEFT OUTER JOIN contacts ON (raw_contacts.contact_id = contacts._id)"; 262 263 public static final String NAME_LOOKUP_JOIN_RAW_CONTACTS = "name_lookup " 264 + "INNER JOIN view_raw_contacts ON (name_lookup.raw_contact_id = " 265 + "view_raw_contacts._id)"; 266 267 public static final String RAW_CONTACTS_JOIN_ACCOUNTS = Tables.RAW_CONTACTS 268 + " JOIN " + Tables.ACCOUNTS + " ON (" 269 + AccountsColumns.CONCRETE_ID + "=" + RawContactsColumns.CONCRETE_ACCOUNT_ID 270 + ")"; 271 } 272 273 public interface Joins { 274 /** 275 * Join string intended to be used with the GROUPS table/view. The main table must be named 276 * as "groups". 277 * 278 * Adds the "group_member_count column" to the query, which will be null if a group has 279 * no members. Use ifnull(group_member_count, 0) if 0 is needed instead. 280 */ 281 public static final String GROUP_MEMBER_COUNT = 282 " LEFT OUTER JOIN (SELECT " 283 + "data.data1 AS member_count_group_id, " 284 + "COUNT(data.raw_contact_id) AS group_member_count " 285 + "FROM data " 286 + "WHERE " 287 + "data.mimetype_id = (SELECT _id FROM mimetypes WHERE " 288 + "mimetypes.mimetype = '" + GroupMembership.CONTENT_ITEM_TYPE + "')" 289 + "GROUP BY member_count_group_id) AS member_count_table" // End of inner query 290 + " ON (groups._id = member_count_table.member_count_group_id)"; 291 } 292 293 public interface Views { 294 public static final String DATA = "view_data"; 295 public static final String RAW_CONTACTS = "view_raw_contacts"; 296 public static final String CONTACTS = "view_contacts"; 297 public static final String ENTITIES = "view_entities"; 298 public static final String RAW_ENTITIES = "view_raw_entities"; 299 public static final String GROUPS = "view_groups"; 300 public static final String DATA_USAGE_STAT = "view_data_usage_stat"; 301 public static final String STREAM_ITEMS = "view_stream_items"; 302 } 303 304 public interface Clauses { 305 final String HAVING_NO_GROUPS = "COUNT(" + DataColumns.CONCRETE_GROUP_ID + ") == 0"; 306 307 final String GROUP_BY_ACCOUNT_CONTACT_ID = SettingsColumns.CONCRETE_ACCOUNT_NAME + "," 308 + SettingsColumns.CONCRETE_ACCOUNT_TYPE + "," + RawContacts.CONTACT_ID; 309 310 String LOCAL_ACCOUNT_ID = 311 "(SELECT " + AccountsColumns._ID + 312 " FROM " + Tables.ACCOUNTS + 313 " WHERE " + 314 AccountsColumns.ACCOUNT_NAME + " IS NULL AND " + 315 AccountsColumns.ACCOUNT_TYPE + " IS NULL AND " + 316 AccountsColumns.DATA_SET + " IS NULL)"; 317 318 final String RAW_CONTACT_IS_LOCAL = RawContactsColumns.CONCRETE_ACCOUNT_ID 319 + "=" + LOCAL_ACCOUNT_ID; 320 321 final String ZERO_GROUP_MEMBERSHIPS = "COUNT(" + GroupsColumns.CONCRETE_ID + ")=0"; 322 323 final String OUTER_RAW_CONTACTS = "outer_raw_contacts"; 324 final String OUTER_RAW_CONTACTS_ID = OUTER_RAW_CONTACTS + "." + RawContacts._ID; 325 326 final String CONTACT_IS_VISIBLE = 327 "SELECT " + 328 "MAX((SELECT (CASE WHEN " + 329 "(CASE" + 330 " WHEN " + RAW_CONTACT_IS_LOCAL + 331 " THEN 1 " + 332 " WHEN " + ZERO_GROUP_MEMBERSHIPS + 333 " THEN " + Settings.UNGROUPED_VISIBLE + 334 " ELSE MAX(" + Groups.GROUP_VISIBLE + ")" + 335 "END)=1 THEN 1 ELSE 0 END)" + 336 " FROM " + Tables.RAW_CONTACTS_JOIN_SETTINGS_DATA_GROUPS + 337 " WHERE " + RawContactsColumns.CONCRETE_ID + "=" + OUTER_RAW_CONTACTS_ID + "))" + 338 " FROM " + Tables.RAW_CONTACTS + " AS " + OUTER_RAW_CONTACTS + 339 " WHERE " + RawContacts.CONTACT_ID + "=" + ContactsColumns.CONCRETE_ID + 340 " GROUP BY " + RawContacts.CONTACT_ID; 341 342 final String GROUP_HAS_ACCOUNT_AND_SOURCE_ID = Groups.SOURCE_ID + "=? AND " 343 + GroupsColumns.ACCOUNT_ID + "=?"; 344 345 public static final String CONTACT_VISIBLE = 346 "EXISTS (SELECT _id FROM " + Tables.VISIBLE_CONTACTS 347 + " WHERE " + Tables.CONTACTS +"." + Contacts._ID 348 + "=" + Tables.VISIBLE_CONTACTS +"." + Contacts._ID + ")"; 349 } 350 351 public interface ContactsColumns { 352 public static final String LAST_STATUS_UPDATE_ID = "status_update_id"; 353 354 public static final String CONCRETE_ID = Tables.CONTACTS + "." + BaseColumns._ID; 355 356 public static final String CONCRETE_PHOTO_FILE_ID = Tables.CONTACTS + "." 357 + Contacts.PHOTO_FILE_ID; 358 public static final String CONCRETE_TIMES_CONTACTED = Tables.CONTACTS + "." 359 + Contacts.TIMES_CONTACTED; 360 public static final String CONCRETE_LAST_TIME_CONTACTED = Tables.CONTACTS + "." 361 + Contacts.LAST_TIME_CONTACTED; 362 public static final String CONCRETE_STARRED = Tables.CONTACTS + "." + Contacts.STARRED; 363 public static final String CONCRETE_CUSTOM_RINGTONE = Tables.CONTACTS + "." 364 + Contacts.CUSTOM_RINGTONE; 365 public static final String CONCRETE_SEND_TO_VOICEMAIL = Tables.CONTACTS + "." 366 + Contacts.SEND_TO_VOICEMAIL; 367 public static final String CONCRETE_LOOKUP_KEY = Tables.CONTACTS + "." 368 + Contacts.LOOKUP_KEY; 369 public static final String CONCRETE_CONTACT_LAST_UPDATED_TIMESTAMP = Tables.CONTACTS + "." 370 + Contacts.CONTACT_LAST_UPDATED_TIMESTAMP; 371 public static final String PHONEBOOK_LABEL_PRIMARY = "phonebook_label"; 372 public static final String PHONEBOOK_BUCKET_PRIMARY = "phonebook_bucket"; 373 public static final String PHONEBOOK_LABEL_ALTERNATIVE = "phonebook_label_alt"; 374 public static final String PHONEBOOK_BUCKET_ALTERNATIVE = "phonebook_bucket_alt"; 375 } 376 377 public interface RawContactsColumns { 378 public static final String CONCRETE_ID = 379 Tables.RAW_CONTACTS + "." + BaseColumns._ID; 380 381 public static final String ACCOUNT_ID = "account_id"; 382 public static final String CONCRETE_ACCOUNT_ID = Tables.RAW_CONTACTS + "." + ACCOUNT_ID; 383 public static final String CONCRETE_SOURCE_ID = 384 Tables.RAW_CONTACTS + "." + RawContacts.SOURCE_ID; 385 public static final String CONCRETE_VERSION = 386 Tables.RAW_CONTACTS + "." + RawContacts.VERSION; 387 public static final String CONCRETE_DIRTY = 388 Tables.RAW_CONTACTS + "." + RawContacts.DIRTY; 389 public static final String CONCRETE_DELETED = 390 Tables.RAW_CONTACTS + "." + RawContacts.DELETED; 391 public static final String CONCRETE_SYNC1 = 392 Tables.RAW_CONTACTS + "." + RawContacts.SYNC1; 393 public static final String CONCRETE_SYNC2 = 394 Tables.RAW_CONTACTS + "." + RawContacts.SYNC2; 395 public static final String CONCRETE_SYNC3 = 396 Tables.RAW_CONTACTS + "." + RawContacts.SYNC3; 397 public static final String CONCRETE_SYNC4 = 398 Tables.RAW_CONTACTS + "." + RawContacts.SYNC4; 399 public static final String CONCRETE_CUSTOM_RINGTONE = 400 Tables.RAW_CONTACTS + "." + RawContacts.CUSTOM_RINGTONE; 401 public static final String CONCRETE_SEND_TO_VOICEMAIL = 402 Tables.RAW_CONTACTS + "." + RawContacts.SEND_TO_VOICEMAIL; 403 public static final String CONCRETE_LAST_TIME_CONTACTED = 404 Tables.RAW_CONTACTS + "." + RawContacts.LAST_TIME_CONTACTED; 405 public static final String CONCRETE_TIMES_CONTACTED = 406 Tables.RAW_CONTACTS + "." + RawContacts.TIMES_CONTACTED; 407 public static final String CONCRETE_STARRED = 408 Tables.RAW_CONTACTS + "." + RawContacts.STARRED; 409 410 public static final String DISPLAY_NAME = RawContacts.DISPLAY_NAME_PRIMARY; 411 public static final String DISPLAY_NAME_SOURCE = RawContacts.DISPLAY_NAME_SOURCE; 412 public static final String AGGREGATION_NEEDED = "aggregation_needed"; 413 414 public static final String CONCRETE_DISPLAY_NAME = 415 Tables.RAW_CONTACTS + "." + DISPLAY_NAME; 416 public static final String CONCRETE_CONTACT_ID = 417 Tables.RAW_CONTACTS + "." + RawContacts.CONTACT_ID; 418 public static final String CONCRETE_NAME_VERIFIED = 419 Tables.RAW_CONTACTS + "." + RawContacts.NAME_VERIFIED; 420 public static final String PHONEBOOK_LABEL_PRIMARY = 421 ContactsColumns.PHONEBOOK_LABEL_PRIMARY; 422 public static final String PHONEBOOK_BUCKET_PRIMARY = 423 ContactsColumns.PHONEBOOK_BUCKET_PRIMARY; 424 public static final String PHONEBOOK_LABEL_ALTERNATIVE = 425 ContactsColumns.PHONEBOOK_LABEL_ALTERNATIVE; 426 public static final String PHONEBOOK_BUCKET_ALTERNATIVE = 427 ContactsColumns.PHONEBOOK_BUCKET_ALTERNATIVE; 428 } 429 430 public interface ViewRawContactsColumns { 431 String CONCRETE_ACCOUNT_NAME = Views.RAW_CONTACTS + "." + RawContacts.ACCOUNT_NAME; 432 String CONCRETE_ACCOUNT_TYPE = Views.RAW_CONTACTS + "." + RawContacts.ACCOUNT_TYPE; 433 String CONCRETE_DATA_SET = Views.RAW_CONTACTS + "." + RawContacts.DATA_SET; 434 } 435 436 public interface DataColumns { 437 public static final String PACKAGE_ID = "package_id"; 438 public static final String MIMETYPE_ID = "mimetype_id"; 439 440 public static final String CONCRETE_ID = Tables.DATA + "." + BaseColumns._ID; 441 public static final String CONCRETE_MIMETYPE_ID = Tables.DATA + "." + MIMETYPE_ID; 442 public static final String CONCRETE_RAW_CONTACT_ID = Tables.DATA + "." 443 + Data.RAW_CONTACT_ID; 444 public static final String CONCRETE_GROUP_ID = Tables.DATA + "." 445 + GroupMembership.GROUP_ROW_ID; 446 447 public static final String CONCRETE_DATA1 = Tables.DATA + "." + Data.DATA1; 448 public static final String CONCRETE_DATA2 = Tables.DATA + "." + Data.DATA2; 449 public static final String CONCRETE_DATA3 = Tables.DATA + "." + Data.DATA3; 450 public static final String CONCRETE_DATA4 = Tables.DATA + "." + Data.DATA4; 451 public static final String CONCRETE_DATA5 = Tables.DATA + "." + Data.DATA5; 452 public static final String CONCRETE_DATA6 = Tables.DATA + "." + Data.DATA6; 453 public static final String CONCRETE_DATA7 = Tables.DATA + "." + Data.DATA7; 454 public static final String CONCRETE_DATA8 = Tables.DATA + "." + Data.DATA8; 455 public static final String CONCRETE_DATA9 = Tables.DATA + "." + Data.DATA9; 456 public static final String CONCRETE_DATA10 = Tables.DATA + "." + Data.DATA10; 457 public static final String CONCRETE_DATA11 = Tables.DATA + "." + Data.DATA11; 458 public static final String CONCRETE_DATA12 = Tables.DATA + "." + Data.DATA12; 459 public static final String CONCRETE_DATA13 = Tables.DATA + "." + Data.DATA13; 460 public static final String CONCRETE_DATA14 = Tables.DATA + "." + Data.DATA14; 461 public static final String CONCRETE_DATA15 = Tables.DATA + "." + Data.DATA15; 462 public static final String CONCRETE_IS_PRIMARY = Tables.DATA + "." + Data.IS_PRIMARY; 463 public static final String CONCRETE_PACKAGE_ID = Tables.DATA + "." + PACKAGE_ID; 464 } 465 466 // Used only for legacy API support 467 public interface ExtensionsColumns { 468 public static final String NAME = Data.DATA1; 469 public static final String VALUE = Data.DATA2; 470 } 471 472 public interface GroupMembershipColumns { 473 public static final String RAW_CONTACT_ID = Data.RAW_CONTACT_ID; 474 public static final String GROUP_ROW_ID = GroupMembership.GROUP_ROW_ID; 475 } 476 477 public interface GroupsColumns { 478 public static final String PACKAGE_ID = "package_id"; 479 public static final String CONCRETE_PACKAGE_ID = Tables.GROUPS + "." + PACKAGE_ID; 480 481 public static final String CONCRETE_ID = Tables.GROUPS + "." + BaseColumns._ID; 482 public static final String CONCRETE_SOURCE_ID = Tables.GROUPS + "." + Groups.SOURCE_ID; 483 484 public static final String ACCOUNT_ID = "account_id"; 485 public static final String CONCRETE_ACCOUNT_ID = Tables.GROUPS + "." + ACCOUNT_ID; 486 } 487 488 public interface ViewGroupsColumns { 489 String CONCRETE_ACCOUNT_NAME = Views.GROUPS + "." + Groups.ACCOUNT_NAME; 490 String CONCRETE_ACCOUNT_TYPE = Views.GROUPS + "." + Groups.ACCOUNT_TYPE; 491 String CONCRETE_DATA_SET = Views.GROUPS + "." + Groups.DATA_SET; 492 } 493 494 public interface ActivitiesColumns { 495 public static final String PACKAGE_ID = "package_id"; 496 public static final String MIMETYPE_ID = "mimetype_id"; 497 } 498 499 public interface PhoneLookupColumns { 500 public static final String _ID = BaseColumns._ID; 501 public static final String DATA_ID = "data_id"; 502 public static final String RAW_CONTACT_ID = "raw_contact_id"; 503 public static final String NORMALIZED_NUMBER = "normalized_number"; 504 public static final String MIN_MATCH = "min_match"; 505 } 506 507 public interface NameLookupColumns { 508 public static final String RAW_CONTACT_ID = "raw_contact_id"; 509 public static final String DATA_ID = "data_id"; 510 public static final String NORMALIZED_NAME = "normalized_name"; 511 public static final String NAME_TYPE = "name_type"; 512 } 513 514 public final static class NameLookupType { 515 public static final int NAME_EXACT = 0; 516 public static final int NAME_VARIANT = 1; 517 public static final int NAME_COLLATION_KEY = 2; 518 public static final int NICKNAME = 3; 519 public static final int EMAIL_BASED_NICKNAME = 4; 520 521 // This is the highest name lookup type code plus one 522 public static final int TYPE_COUNT = 5; 523 isBasedOnStructuredName(int nameLookupType)524 public static boolean isBasedOnStructuredName(int nameLookupType) { 525 return nameLookupType == NameLookupType.NAME_EXACT 526 || nameLookupType == NameLookupType.NAME_VARIANT 527 || nameLookupType == NameLookupType.NAME_COLLATION_KEY; 528 } 529 } 530 531 public interface PackagesColumns { 532 public static final String _ID = BaseColumns._ID; 533 public static final String PACKAGE = "package"; 534 535 public static final String CONCRETE_ID = Tables.PACKAGES + "." + _ID; 536 } 537 538 public interface MimetypesColumns { 539 public static final String _ID = BaseColumns._ID; 540 public static final String MIMETYPE = "mimetype"; 541 542 public static final String CONCRETE_ID = Tables.MIMETYPES + "." + BaseColumns._ID; 543 public static final String CONCRETE_MIMETYPE = Tables.MIMETYPES + "." + MIMETYPE; 544 } 545 546 public interface AggregationExceptionColumns { 547 public static final String _ID = BaseColumns._ID; 548 } 549 550 public interface NicknameLookupColumns { 551 public static final String NAME = "name"; 552 public static final String CLUSTER = "cluster"; 553 } 554 555 public interface SettingsColumns { 556 public static final String CONCRETE_ACCOUNT_NAME = Tables.SETTINGS + "." 557 + Settings.ACCOUNT_NAME; 558 public static final String CONCRETE_ACCOUNT_TYPE = Tables.SETTINGS + "." 559 + Settings.ACCOUNT_TYPE; 560 public static final String CONCRETE_DATA_SET = Tables.SETTINGS + "." 561 + Settings.DATA_SET; 562 } 563 564 public interface PresenceColumns { 565 String RAW_CONTACT_ID = "presence_raw_contact_id"; 566 String CONTACT_ID = "presence_contact_id"; 567 } 568 569 public interface AggregatedPresenceColumns { 570 String CONTACT_ID = "presence_contact_id"; 571 572 String CONCRETE_CONTACT_ID = Tables.AGGREGATED_PRESENCE + "." + CONTACT_ID; 573 } 574 575 public interface StatusUpdatesColumns { 576 String DATA_ID = "status_update_data_id"; 577 578 String CONCRETE_DATA_ID = Tables.STATUS_UPDATES + "." + DATA_ID; 579 580 String CONCRETE_PRESENCE = Tables.STATUS_UPDATES + "." + StatusUpdates.PRESENCE; 581 String CONCRETE_STATUS = Tables.STATUS_UPDATES + "." + StatusUpdates.STATUS; 582 String CONCRETE_STATUS_TIMESTAMP = Tables.STATUS_UPDATES + "." 583 + StatusUpdates.STATUS_TIMESTAMP; 584 String CONCRETE_STATUS_RES_PACKAGE = Tables.STATUS_UPDATES + "." 585 + StatusUpdates.STATUS_RES_PACKAGE; 586 String CONCRETE_STATUS_LABEL = Tables.STATUS_UPDATES + "." + StatusUpdates.STATUS_LABEL; 587 String CONCRETE_STATUS_ICON = Tables.STATUS_UPDATES + "." + StatusUpdates.STATUS_ICON; 588 } 589 590 public interface ContactsStatusUpdatesColumns { 591 String ALIAS = "contacts_" + Tables.STATUS_UPDATES; 592 593 String CONCRETE_DATA_ID = ALIAS + "." + StatusUpdatesColumns.DATA_ID; 594 595 String CONCRETE_PRESENCE = ALIAS + "." + StatusUpdates.PRESENCE; 596 String CONCRETE_STATUS = ALIAS + "." + StatusUpdates.STATUS; 597 String CONCRETE_STATUS_TIMESTAMP = ALIAS + "." + StatusUpdates.STATUS_TIMESTAMP; 598 String CONCRETE_STATUS_RES_PACKAGE = ALIAS + "." + StatusUpdates.STATUS_RES_PACKAGE; 599 String CONCRETE_STATUS_LABEL = ALIAS + "." + StatusUpdates.STATUS_LABEL; 600 String CONCRETE_STATUS_ICON = ALIAS + "." + StatusUpdates.STATUS_ICON; 601 } 602 603 public interface StreamItemsColumns { 604 final String CONCRETE_ID = Tables.STREAM_ITEMS + "." + BaseColumns._ID; 605 final String CONCRETE_RAW_CONTACT_ID = 606 Tables.STREAM_ITEMS + "." + StreamItems.RAW_CONTACT_ID; 607 final String CONCRETE_PACKAGE = Tables.STREAM_ITEMS + "." + StreamItems.RES_PACKAGE; 608 final String CONCRETE_ICON = Tables.STREAM_ITEMS + "." + StreamItems.RES_ICON; 609 final String CONCRETE_LABEL = Tables.STREAM_ITEMS + "." + StreamItems.RES_LABEL; 610 final String CONCRETE_TEXT = Tables.STREAM_ITEMS + "." + StreamItems.TEXT; 611 final String CONCRETE_TIMESTAMP = Tables.STREAM_ITEMS + "." + StreamItems.TIMESTAMP; 612 final String CONCRETE_COMMENTS = Tables.STREAM_ITEMS + "." + StreamItems.COMMENTS; 613 final String CONCRETE_SYNC1 = Tables.STREAM_ITEMS + "." + StreamItems.SYNC1; 614 final String CONCRETE_SYNC2 = Tables.STREAM_ITEMS + "." + StreamItems.SYNC2; 615 final String CONCRETE_SYNC3 = Tables.STREAM_ITEMS + "." + StreamItems.SYNC3; 616 final String CONCRETE_SYNC4 = Tables.STREAM_ITEMS + "." + StreamItems.SYNC4; 617 } 618 619 public interface StreamItemPhotosColumns { 620 final String CONCRETE_ID = Tables.STREAM_ITEM_PHOTOS + "." + BaseColumns._ID; 621 final String CONCRETE_STREAM_ITEM_ID = Tables.STREAM_ITEM_PHOTOS + "." 622 + StreamItemPhotos.STREAM_ITEM_ID; 623 final String CONCRETE_SORT_INDEX = 624 Tables.STREAM_ITEM_PHOTOS + "." + StreamItemPhotos.SORT_INDEX; 625 final String CONCRETE_PHOTO_FILE_ID = Tables.STREAM_ITEM_PHOTOS + "." 626 + StreamItemPhotos.PHOTO_FILE_ID; 627 final String CONCRETE_SYNC1 = Tables.STREAM_ITEM_PHOTOS + "." + StreamItemPhotos.SYNC1; 628 final String CONCRETE_SYNC2 = Tables.STREAM_ITEM_PHOTOS + "." + StreamItemPhotos.SYNC2; 629 final String CONCRETE_SYNC3 = Tables.STREAM_ITEM_PHOTOS + "." + StreamItemPhotos.SYNC3; 630 final String CONCRETE_SYNC4 = Tables.STREAM_ITEM_PHOTOS + "." + StreamItemPhotos.SYNC4; 631 } 632 633 public interface PhotoFilesColumns { 634 String CONCRETE_ID = Tables.PHOTO_FILES + "." + BaseColumns._ID; 635 String CONCRETE_HEIGHT = Tables.PHOTO_FILES + "." + PhotoFiles.HEIGHT; 636 String CONCRETE_WIDTH = Tables.PHOTO_FILES + "." + PhotoFiles.WIDTH; 637 String CONCRETE_FILESIZE = Tables.PHOTO_FILES + "." + PhotoFiles.FILESIZE; 638 } 639 640 public interface PropertiesColumns { 641 String PROPERTY_KEY = "property_key"; 642 String PROPERTY_VALUE = "property_value"; 643 } 644 645 public interface AccountsColumns extends BaseColumns { 646 String CONCRETE_ID = Tables.ACCOUNTS + "." + BaseColumns._ID; 647 648 String ACCOUNT_NAME = RawContacts.ACCOUNT_NAME; 649 String ACCOUNT_TYPE = RawContacts.ACCOUNT_TYPE; 650 String DATA_SET = RawContacts.DATA_SET; 651 652 String CONCRETE_ACCOUNT_NAME = Tables.ACCOUNTS + "." + ACCOUNT_NAME; 653 String CONCRETE_ACCOUNT_TYPE = Tables.ACCOUNTS + "." + ACCOUNT_TYPE; 654 String CONCRETE_DATA_SET = Tables.ACCOUNTS + "." + DATA_SET; 655 } 656 657 public static final class DirectoryColumns { 658 public static final String TYPE_RESOURCE_NAME = "typeResourceName"; 659 } 660 661 public static final class SearchIndexColumns { 662 public static final String CONTACT_ID = "contact_id"; 663 public static final String CONTENT = "content"; 664 public static final String NAME = "name"; 665 public static final String TOKENS = "tokens"; 666 } 667 668 /** 669 * Private table for calculating per-contact-method ranking. 670 */ 671 public static final class DataUsageStatColumns { 672 /** type: INTEGER (long) */ 673 public static final String _ID = "stat_id"; 674 public static final String CONCRETE_ID = Tables.DATA_USAGE_STAT + "." + _ID; 675 676 /** type: INTEGER (long) */ 677 public static final String DATA_ID = "data_id"; 678 public static final String CONCRETE_DATA_ID = Tables.DATA_USAGE_STAT + "." + DATA_ID; 679 680 /** type: INTEGER (long) */ 681 public static final String LAST_TIME_USED = "last_time_used"; 682 public static final String CONCRETE_LAST_TIME_USED = 683 Tables.DATA_USAGE_STAT + "." + LAST_TIME_USED; 684 685 /** type: INTEGER */ 686 public static final String TIMES_USED = "times_used"; 687 public static final String CONCRETE_TIMES_USED = 688 Tables.DATA_USAGE_STAT + "." + TIMES_USED; 689 690 /** type: INTEGER */ 691 public static final String USAGE_TYPE_INT = "usage_type"; 692 public static final String CONCRETE_USAGE_TYPE = 693 Tables.DATA_USAGE_STAT + "." + USAGE_TYPE_INT; 694 695 /** 696 * Integer values for USAGE_TYPE. 697 * 698 * @see ContactsContract.DataUsageFeedback#USAGE_TYPE 699 */ 700 public static final int USAGE_TYPE_INT_CALL = 0; 701 public static final int USAGE_TYPE_INT_LONG_TEXT = 1; 702 public static final int USAGE_TYPE_INT_SHORT_TEXT = 2; 703 } 704 705 public interface Projections { 706 String[] ID = new String[] {BaseColumns._ID}; 707 String[] LITERAL_ONE = new String[] {"1"}; 708 } 709 710 /** 711 * Property names for {@link ContactsDatabaseHelper#getProperty} and 712 * {@link ContactsDatabaseHelper#setProperty}. 713 */ 714 public interface DbProperties { 715 String DIRECTORY_SCAN_COMPLETE = "directoryScanComplete"; 716 String AGGREGATION_ALGORITHM = "aggregation_v2"; 717 String KNOWN_ACCOUNTS = "known_accounts"; 718 String ICU_VERSION = "icu_version"; 719 String LOCALE = "locale"; 720 } 721 722 /** In-memory cache of previously found MIME-type mappings */ 723 private final HashMap<String, Long> mMimetypeCache = new HashMap<String, Long>(); 724 725 /** In-memory cache the packages table */ 726 private final HashMap<String, Long> mPackageCache = new HashMap<String, Long>(); 727 728 private long mMimeTypeIdEmail; 729 private long mMimeTypeIdIm; 730 private long mMimeTypeIdNickname; 731 private long mMimeTypeIdOrganization; 732 private long mMimeTypeIdPhone; 733 private long mMimeTypeIdSip; 734 private long mMimeTypeIdStructuredName; 735 private long mMimeTypeIdStructuredPostal; 736 737 /** Compiled statements for querying and inserting mappings */ 738 private SQLiteStatement mContactIdQuery; 739 private SQLiteStatement mAggregationModeQuery; 740 private SQLiteStatement mDataMimetypeQuery; 741 742 /** Precompiled sql statement for setting a data record to the primary. */ 743 private SQLiteStatement mSetPrimaryStatement; 744 /** Precompiled sql statement for setting a data record to the super primary. */ 745 private SQLiteStatement mSetSuperPrimaryStatement; 746 /** Precompiled sql statement for clearing super primary of a single record. */ 747 private SQLiteStatement mClearSuperPrimaryStatement; 748 /** Precompiled sql statement for updating a contact display name */ 749 private SQLiteStatement mRawContactDisplayNameUpdate; 750 751 private SQLiteStatement mNameLookupInsert; 752 private SQLiteStatement mNameLookupDelete; 753 private SQLiteStatement mStatusUpdateAutoTimestamp; 754 private SQLiteStatement mStatusUpdateInsert; 755 private SQLiteStatement mStatusUpdateReplace; 756 private SQLiteStatement mStatusAttributionUpdate; 757 private SQLiteStatement mStatusUpdateDelete; 758 private SQLiteStatement mResetNameVerifiedForOtherRawContacts; 759 private SQLiteStatement mContactInDefaultDirectoryQuery; 760 761 private final Context mContext; 762 private final boolean mDatabaseOptimizationEnabled; 763 private final SyncStateContentProviderHelper mSyncState; 764 private final CountryMonitor mCountryMonitor; 765 private StringBuilder mSb = new StringBuilder(); 766 767 private static ContactsDatabaseHelper sSingleton = null; 768 769 private boolean mUseStrictPhoneNumberComparison; 770 771 private String[] mSelectionArgs1 = new String[1]; 772 private NameSplitter.Name mName = new NameSplitter.Name(); 773 private CharArrayBuffer mCharArrayBuffer = new CharArrayBuffer(128); 774 private NameSplitter mNameSplitter; 775 getInstance(Context context)776 public static synchronized ContactsDatabaseHelper getInstance(Context context) { 777 if (sSingleton == null) { 778 sSingleton = new ContactsDatabaseHelper(context, DATABASE_NAME, true); 779 } 780 return sSingleton; 781 } 782 783 /** 784 * Returns a new instance for unit tests. 785 */ 786 @NeededForTesting getNewInstanceForTest(Context context)787 static ContactsDatabaseHelper getNewInstanceForTest(Context context) { 788 return new ContactsDatabaseHelper(context, null, false); 789 } 790 ContactsDatabaseHelper( Context context, String databaseName, boolean optimizationEnabled)791 protected ContactsDatabaseHelper( 792 Context context, String databaseName, boolean optimizationEnabled) { 793 super(context, databaseName, null, DATABASE_VERSION); 794 mDatabaseOptimizationEnabled = optimizationEnabled; 795 Resources resources = context.getResources(); 796 797 mContext = context; 798 mSyncState = new SyncStateContentProviderHelper(); 799 mCountryMonitor = new CountryMonitor(context); 800 mUseStrictPhoneNumberComparison = 801 resources.getBoolean( 802 com.android.internal.R.bool.config_use_strict_phone_number_comparation); 803 } 804 getDatabase(boolean writable)805 public SQLiteDatabase getDatabase(boolean writable) { 806 return writable ? getWritableDatabase() : getReadableDatabase(); 807 } 808 809 /** 810 * Clear all the cached database information and re-initialize it. 811 * 812 * @param db target database 813 */ refreshDatabaseCaches(SQLiteDatabase db)814 private void refreshDatabaseCaches(SQLiteDatabase db) { 815 mStatusUpdateDelete = null; 816 mStatusUpdateReplace = null; 817 mStatusUpdateInsert = null; 818 mStatusUpdateAutoTimestamp = null; 819 mStatusAttributionUpdate = null; 820 mResetNameVerifiedForOtherRawContacts = null; 821 mRawContactDisplayNameUpdate = null; 822 mSetPrimaryStatement = null; 823 mClearSuperPrimaryStatement = null; 824 mSetSuperPrimaryStatement = null; 825 mNameLookupInsert = null; 826 mNameLookupDelete = null; 827 mDataMimetypeQuery = null; 828 mContactIdQuery = null; 829 mAggregationModeQuery = null; 830 mContactInDefaultDirectoryQuery = null; 831 832 initializeCache(db); 833 } 834 835 /** 836 * (Re-)initialize the cached database information. 837 * 838 * @param db target database 839 */ initializeCache(SQLiteDatabase db)840 private void initializeCache(SQLiteDatabase db) { 841 mMimetypeCache.clear(); 842 mPackageCache.clear(); 843 844 // TODO: This could be optimized into one query instead of 7 845 // Also: We shouldn't have those fields in the first place. This should just be 846 // in the cache 847 mMimeTypeIdEmail = lookupMimeTypeId(Email.CONTENT_ITEM_TYPE, db); 848 mMimeTypeIdIm = lookupMimeTypeId(Im.CONTENT_ITEM_TYPE, db); 849 mMimeTypeIdNickname = lookupMimeTypeId(Nickname.CONTENT_ITEM_TYPE, db); 850 mMimeTypeIdOrganization = lookupMimeTypeId(Organization.CONTENT_ITEM_TYPE, db); 851 mMimeTypeIdPhone = lookupMimeTypeId(Phone.CONTENT_ITEM_TYPE, db); 852 mMimeTypeIdSip = lookupMimeTypeId(SipAddress.CONTENT_ITEM_TYPE, db); 853 mMimeTypeIdStructuredName = lookupMimeTypeId(StructuredName.CONTENT_ITEM_TYPE, db); 854 mMimeTypeIdStructuredPostal = lookupMimeTypeId(StructuredPostal.CONTENT_ITEM_TYPE, db); 855 } 856 857 @Override onOpen(SQLiteDatabase db)858 public void onOpen(SQLiteDatabase db) { 859 refreshDatabaseCaches(db); 860 861 mSyncState.onDatabaseOpened(db); 862 863 db.execSQL("ATTACH DATABASE ':memory:' AS " + DATABASE_PRESENCE + ";"); 864 db.execSQL("CREATE TABLE IF NOT EXISTS " + DATABASE_PRESENCE + "." + Tables.PRESENCE + " ("+ 865 StatusUpdates.DATA_ID + " INTEGER PRIMARY KEY REFERENCES data(_id)," + 866 StatusUpdates.PROTOCOL + " INTEGER NOT NULL," + 867 StatusUpdates.CUSTOM_PROTOCOL + " TEXT," + 868 StatusUpdates.IM_HANDLE + " TEXT," + 869 StatusUpdates.IM_ACCOUNT + " TEXT," + 870 PresenceColumns.CONTACT_ID + " INTEGER REFERENCES contacts(_id)," + 871 PresenceColumns.RAW_CONTACT_ID + " INTEGER REFERENCES raw_contacts(_id)," + 872 StatusUpdates.PRESENCE + " INTEGER," + 873 StatusUpdates.CHAT_CAPABILITY + " INTEGER NOT NULL DEFAULT 0," + 874 "UNIQUE(" + StatusUpdates.PROTOCOL + ", " + StatusUpdates.CUSTOM_PROTOCOL 875 + ", " + StatusUpdates.IM_HANDLE + ", " + StatusUpdates.IM_ACCOUNT + ")" + 876 ");"); 877 878 db.execSQL("CREATE INDEX IF NOT EXISTS " + DATABASE_PRESENCE + ".presenceIndex" + " ON " 879 + Tables.PRESENCE + " (" + PresenceColumns.RAW_CONTACT_ID + ");"); 880 db.execSQL("CREATE INDEX IF NOT EXISTS " + DATABASE_PRESENCE + ".presenceIndex2" + " ON " 881 + Tables.PRESENCE + " (" + PresenceColumns.CONTACT_ID + ");"); 882 883 db.execSQL("CREATE TABLE IF NOT EXISTS " 884 + DATABASE_PRESENCE + "." + Tables.AGGREGATED_PRESENCE + " ("+ 885 AggregatedPresenceColumns.CONTACT_ID 886 + " INTEGER PRIMARY KEY REFERENCES contacts(_id)," + 887 StatusUpdates.PRESENCE + " INTEGER," + 888 StatusUpdates.CHAT_CAPABILITY + " INTEGER NOT NULL DEFAULT 0" + 889 ");"); 890 891 892 db.execSQL("CREATE TRIGGER " + DATABASE_PRESENCE + "." + Tables.PRESENCE + "_deleted" 893 + " BEFORE DELETE ON " + DATABASE_PRESENCE + "." + Tables.PRESENCE 894 + " BEGIN " 895 + " DELETE FROM " + Tables.AGGREGATED_PRESENCE 896 + " WHERE " + AggregatedPresenceColumns.CONTACT_ID + " = " + 897 "(SELECT " + PresenceColumns.CONTACT_ID + 898 " FROM " + Tables.PRESENCE + 899 " WHERE " + PresenceColumns.RAW_CONTACT_ID 900 + "=OLD." + PresenceColumns.RAW_CONTACT_ID + 901 " AND NOT EXISTS" + 902 "(SELECT " + PresenceColumns.RAW_CONTACT_ID + 903 " FROM " + Tables.PRESENCE + 904 " WHERE " + PresenceColumns.CONTACT_ID 905 + "=OLD." + PresenceColumns.CONTACT_ID + 906 " AND " + PresenceColumns.RAW_CONTACT_ID 907 + "!=OLD." + PresenceColumns.RAW_CONTACT_ID + "));" 908 + " END"); 909 910 final String replaceAggregatePresenceSql = 911 "INSERT OR REPLACE INTO " + Tables.AGGREGATED_PRESENCE + "(" 912 + AggregatedPresenceColumns.CONTACT_ID + ", " 913 + StatusUpdates.PRESENCE + ", " 914 + StatusUpdates.CHAT_CAPABILITY + ")" 915 + " SELECT " 916 + PresenceColumns.CONTACT_ID + "," 917 + StatusUpdates.PRESENCE + "," 918 + StatusUpdates.CHAT_CAPABILITY 919 + " FROM " + Tables.PRESENCE 920 + " WHERE " 921 + " (ifnull(" + StatusUpdates.PRESENCE + ",0) * 10 " 922 + "+ ifnull(" + StatusUpdates.CHAT_CAPABILITY + ", 0))" 923 + " = (SELECT " 924 + "MAX (ifnull(" + StatusUpdates.PRESENCE + ",0) * 10 " 925 + "+ ifnull(" + StatusUpdates.CHAT_CAPABILITY + ", 0))" 926 + " FROM " + Tables.PRESENCE 927 + " WHERE " + PresenceColumns.CONTACT_ID 928 + "=NEW." + PresenceColumns.CONTACT_ID 929 + ")" 930 + " AND " + PresenceColumns.CONTACT_ID + "=NEW." + PresenceColumns.CONTACT_ID + ";"; 931 932 db.execSQL("CREATE TRIGGER " + DATABASE_PRESENCE + "." + Tables.PRESENCE + "_inserted" 933 + " AFTER INSERT ON " + DATABASE_PRESENCE + "." + Tables.PRESENCE 934 + " BEGIN " 935 + replaceAggregatePresenceSql 936 + " END"); 937 938 db.execSQL("CREATE TRIGGER " + DATABASE_PRESENCE + "." + Tables.PRESENCE + "_updated" 939 + " AFTER UPDATE ON " + DATABASE_PRESENCE + "." + Tables.PRESENCE 940 + " BEGIN " 941 + replaceAggregatePresenceSql 942 + " END"); 943 } 944 945 @Override onCreate(SQLiteDatabase db)946 public void onCreate(SQLiteDatabase db) { 947 Log.i(TAG, "Bootstrapping database version: " + DATABASE_VERSION); 948 949 mSyncState.createDatabase(db); 950 951 db.execSQL("CREATE TABLE " + Tables.ACCOUNTS + " (" + 952 AccountsColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 953 AccountsColumns.ACCOUNT_NAME + " TEXT, " + 954 AccountsColumns.ACCOUNT_TYPE + " TEXT, " + 955 AccountsColumns.DATA_SET + " TEXT" + 956 ");"); 957 958 // One row per group of contacts corresponding to the same person 959 db.execSQL("CREATE TABLE " + Tables.CONTACTS + " (" + 960 BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 961 Contacts.NAME_RAW_CONTACT_ID + " INTEGER REFERENCES raw_contacts(_id)," + 962 Contacts.PHOTO_ID + " INTEGER REFERENCES data(_id)," + 963 Contacts.PHOTO_FILE_ID + " INTEGER REFERENCES photo_files(_id)," + 964 Contacts.CUSTOM_RINGTONE + " TEXT," + 965 Contacts.SEND_TO_VOICEMAIL + " INTEGER NOT NULL DEFAULT 0," + 966 Contacts.TIMES_CONTACTED + " INTEGER NOT NULL DEFAULT 0," + 967 Contacts.LAST_TIME_CONTACTED + " INTEGER," + 968 Contacts.STARRED + " INTEGER NOT NULL DEFAULT 0," + 969 Contacts.HAS_PHONE_NUMBER + " INTEGER NOT NULL DEFAULT 0," + 970 Contacts.LOOKUP_KEY + " TEXT," + 971 ContactsColumns.LAST_STATUS_UPDATE_ID + " INTEGER REFERENCES data(_id)," + 972 Contacts.CONTACT_LAST_UPDATED_TIMESTAMP + " INTEGER" + 973 ");"); 974 975 ContactsTableUtil.createIndexes(db); 976 977 // deleted_contacts table 978 DeletedContactsTableUtil.create(db); 979 980 // Raw_contacts table 981 db.execSQL("CREATE TABLE " + Tables.RAW_CONTACTS + " (" + 982 RawContacts._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 983 RawContactsColumns.ACCOUNT_ID + " INTEGER REFERENCES " + 984 Tables.ACCOUNTS + "(" + AccountsColumns._ID + ")," + 985 RawContacts.SOURCE_ID + " TEXT," + 986 RawContacts.RAW_CONTACT_IS_READ_ONLY + " INTEGER NOT NULL DEFAULT 0," + 987 RawContacts.VERSION + " INTEGER NOT NULL DEFAULT 1," + 988 RawContacts.DIRTY + " INTEGER NOT NULL DEFAULT 0," + 989 RawContacts.DELETED + " INTEGER NOT NULL DEFAULT 0," + 990 RawContacts.CONTACT_ID + " INTEGER REFERENCES contacts(_id)," + 991 RawContacts.AGGREGATION_MODE + " INTEGER NOT NULL DEFAULT " + 992 RawContacts.AGGREGATION_MODE_DEFAULT + "," + 993 RawContactsColumns.AGGREGATION_NEEDED + " INTEGER NOT NULL DEFAULT 1," + 994 RawContacts.CUSTOM_RINGTONE + " TEXT," + 995 RawContacts.SEND_TO_VOICEMAIL + " INTEGER NOT NULL DEFAULT 0," + 996 RawContacts.TIMES_CONTACTED + " INTEGER NOT NULL DEFAULT 0," + 997 RawContacts.LAST_TIME_CONTACTED + " INTEGER," + 998 RawContacts.STARRED + " INTEGER NOT NULL DEFAULT 0," + 999 RawContacts.DISPLAY_NAME_PRIMARY + " TEXT," + 1000 RawContacts.DISPLAY_NAME_ALTERNATIVE + " TEXT," + 1001 RawContacts.DISPLAY_NAME_SOURCE + " INTEGER NOT NULL DEFAULT " + 1002 DisplayNameSources.UNDEFINED + "," + 1003 RawContacts.PHONETIC_NAME + " TEXT," + 1004 // TODO: PHONETIC_NAME_STYLE should be INTEGER. There is a 1005 // mismatch between how the column is created here (TEXT) and 1006 // how it is created in upgradeToVersion205 (INTEGER). 1007 RawContacts.PHONETIC_NAME_STYLE + " TEXT," + 1008 RawContacts.SORT_KEY_PRIMARY + " TEXT COLLATE " + 1009 ContactsProvider2.PHONEBOOK_COLLATOR_NAME + "," + 1010 RawContactsColumns.PHONEBOOK_LABEL_PRIMARY + " TEXT," + 1011 RawContactsColumns.PHONEBOOK_BUCKET_PRIMARY + " INTEGER," + 1012 RawContacts.SORT_KEY_ALTERNATIVE + " TEXT COLLATE " + 1013 ContactsProvider2.PHONEBOOK_COLLATOR_NAME + "," + 1014 RawContactsColumns.PHONEBOOK_LABEL_ALTERNATIVE + " TEXT," + 1015 RawContactsColumns.PHONEBOOK_BUCKET_ALTERNATIVE + " INTEGER," + 1016 RawContacts.NAME_VERIFIED + " INTEGER NOT NULL DEFAULT 0," + 1017 RawContacts.SYNC1 + " TEXT, " + 1018 RawContacts.SYNC2 + " TEXT, " + 1019 RawContacts.SYNC3 + " TEXT, " + 1020 RawContacts.SYNC4 + " TEXT " + 1021 ");"); 1022 1023 db.execSQL("CREATE INDEX raw_contacts_contact_id_index ON " + Tables.RAW_CONTACTS + " (" + 1024 RawContacts.CONTACT_ID + 1025 ");"); 1026 1027 db.execSQL("CREATE INDEX raw_contacts_source_id_account_id_index ON " + 1028 Tables.RAW_CONTACTS + " (" + 1029 RawContacts.SOURCE_ID + ", " + 1030 RawContactsColumns.ACCOUNT_ID + 1031 ");"); 1032 1033 db.execSQL("CREATE TABLE " + Tables.STREAM_ITEMS + " (" + 1034 StreamItems._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 1035 StreamItems.RAW_CONTACT_ID + " INTEGER NOT NULL, " + 1036 StreamItems.RES_PACKAGE + " TEXT, " + 1037 StreamItems.RES_ICON + " TEXT, " + 1038 StreamItems.RES_LABEL + " TEXT, " + 1039 StreamItems.TEXT + " TEXT, " + 1040 StreamItems.TIMESTAMP + " INTEGER NOT NULL, " + 1041 StreamItems.COMMENTS + " TEXT, " + 1042 StreamItems.SYNC1 + " TEXT, " + 1043 StreamItems.SYNC2 + " TEXT, " + 1044 StreamItems.SYNC3 + " TEXT, " + 1045 StreamItems.SYNC4 + " TEXT, " + 1046 "FOREIGN KEY(" + StreamItems.RAW_CONTACT_ID + ") REFERENCES " + 1047 Tables.RAW_CONTACTS + "(" + RawContacts._ID + "));"); 1048 1049 db.execSQL("CREATE TABLE " + Tables.STREAM_ITEM_PHOTOS + " (" + 1050 StreamItemPhotos._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 1051 StreamItemPhotos.STREAM_ITEM_ID + " INTEGER NOT NULL, " + 1052 StreamItemPhotos.SORT_INDEX + " INTEGER, " + 1053 StreamItemPhotos.PHOTO_FILE_ID + " INTEGER NOT NULL, " + 1054 StreamItemPhotos.SYNC1 + " TEXT, " + 1055 StreamItemPhotos.SYNC2 + " TEXT, " + 1056 StreamItemPhotos.SYNC3 + " TEXT, " + 1057 StreamItemPhotos.SYNC4 + " TEXT, " + 1058 "FOREIGN KEY(" + StreamItemPhotos.STREAM_ITEM_ID + ") REFERENCES " + 1059 Tables.STREAM_ITEMS + "(" + StreamItems._ID + "));"); 1060 1061 db.execSQL("CREATE TABLE " + Tables.PHOTO_FILES + " (" + 1062 PhotoFiles._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 1063 PhotoFiles.HEIGHT + " INTEGER NOT NULL, " + 1064 PhotoFiles.WIDTH + " INTEGER NOT NULL, " + 1065 PhotoFiles.FILESIZE + " INTEGER NOT NULL);"); 1066 1067 // TODO readd the index and investigate a controlled use of it 1068 // db.execSQL("CREATE INDEX raw_contacts_agg_index ON " + Tables.RAW_CONTACTS + " (" + 1069 // RawContactsColumns.AGGREGATION_NEEDED + 1070 // ");"); 1071 1072 // Package name mapping table 1073 db.execSQL("CREATE TABLE " + Tables.PACKAGES + " (" + 1074 PackagesColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 1075 PackagesColumns.PACKAGE + " TEXT NOT NULL" + 1076 ");"); 1077 1078 // Mimetype mapping table 1079 db.execSQL("CREATE TABLE " + Tables.MIMETYPES + " (" + 1080 MimetypesColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 1081 MimetypesColumns.MIMETYPE + " TEXT NOT NULL" + 1082 ");"); 1083 1084 // Mimetype table requires an index on mime type 1085 db.execSQL("CREATE UNIQUE INDEX mime_type ON " + Tables.MIMETYPES + " (" + 1086 MimetypesColumns.MIMETYPE + 1087 ");"); 1088 1089 // Public generic data table 1090 db.execSQL("CREATE TABLE " + Tables.DATA + " (" + 1091 Data._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 1092 DataColumns.PACKAGE_ID + " INTEGER REFERENCES package(_id)," + 1093 DataColumns.MIMETYPE_ID + " INTEGER REFERENCES mimetype(_id) NOT NULL," + 1094 Data.RAW_CONTACT_ID + " INTEGER REFERENCES raw_contacts(_id) NOT NULL," + 1095 Data.IS_READ_ONLY + " INTEGER NOT NULL DEFAULT 0," + 1096 Data.IS_PRIMARY + " INTEGER NOT NULL DEFAULT 0," + 1097 Data.IS_SUPER_PRIMARY + " INTEGER NOT NULL DEFAULT 0," + 1098 Data.DATA_VERSION + " INTEGER NOT NULL DEFAULT 0," + 1099 Data.DATA1 + " TEXT," + 1100 Data.DATA2 + " TEXT," + 1101 Data.DATA3 + " TEXT," + 1102 Data.DATA4 + " TEXT," + 1103 Data.DATA5 + " TEXT," + 1104 Data.DATA6 + " TEXT," + 1105 Data.DATA7 + " TEXT," + 1106 Data.DATA8 + " TEXT," + 1107 Data.DATA9 + " TEXT," + 1108 Data.DATA10 + " TEXT," + 1109 Data.DATA11 + " TEXT," + 1110 Data.DATA12 + " TEXT," + 1111 Data.DATA13 + " TEXT," + 1112 Data.DATA14 + " TEXT," + 1113 Data.DATA15 + " TEXT," + 1114 Data.SYNC1 + " TEXT, " + 1115 Data.SYNC2 + " TEXT, " + 1116 Data.SYNC3 + " TEXT, " + 1117 Data.SYNC4 + " TEXT " + 1118 ");"); 1119 1120 db.execSQL("CREATE INDEX data_raw_contact_id ON " + Tables.DATA + " (" + 1121 Data.RAW_CONTACT_ID + 1122 ");"); 1123 1124 /** 1125 * For email lookup and similar queries. 1126 */ 1127 db.execSQL("CREATE INDEX data_mimetype_data1_index ON " + Tables.DATA + " (" + 1128 DataColumns.MIMETYPE_ID + "," + 1129 Data.DATA1 + 1130 ");"); 1131 1132 // Private phone numbers table used for lookup 1133 db.execSQL("CREATE TABLE " + Tables.PHONE_LOOKUP + " (" + 1134 PhoneLookupColumns.DATA_ID 1135 + " INTEGER REFERENCES data(_id) NOT NULL," + 1136 PhoneLookupColumns.RAW_CONTACT_ID 1137 + " INTEGER REFERENCES raw_contacts(_id) NOT NULL," + 1138 PhoneLookupColumns.NORMALIZED_NUMBER + " TEXT NOT NULL," + 1139 PhoneLookupColumns.MIN_MATCH + " TEXT NOT NULL" + 1140 ");"); 1141 1142 db.execSQL("CREATE INDEX phone_lookup_index ON " + Tables.PHONE_LOOKUP + " (" + 1143 PhoneLookupColumns.NORMALIZED_NUMBER + "," + 1144 PhoneLookupColumns.RAW_CONTACT_ID + "," + 1145 PhoneLookupColumns.DATA_ID + 1146 ");"); 1147 1148 db.execSQL("CREATE INDEX phone_lookup_min_match_index ON " + Tables.PHONE_LOOKUP + " (" + 1149 PhoneLookupColumns.MIN_MATCH + "," + 1150 PhoneLookupColumns.RAW_CONTACT_ID + "," + 1151 PhoneLookupColumns.DATA_ID + 1152 ");"); 1153 1154 db.execSQL("CREATE INDEX phone_lookup_data_id_min_match_index ON " + Tables.PHONE_LOOKUP + 1155 " (" + PhoneLookupColumns.DATA_ID + ", " + PhoneLookupColumns.MIN_MATCH + ");"); 1156 1157 // Private name/nickname table used for lookup 1158 db.execSQL("CREATE TABLE " + Tables.NAME_LOOKUP + " (" + 1159 NameLookupColumns.DATA_ID 1160 + " INTEGER REFERENCES data(_id) NOT NULL," + 1161 NameLookupColumns.RAW_CONTACT_ID 1162 + " INTEGER REFERENCES raw_contacts(_id) NOT NULL," + 1163 NameLookupColumns.NORMALIZED_NAME + " TEXT NOT NULL," + 1164 NameLookupColumns.NAME_TYPE + " INTEGER NOT NULL," + 1165 "PRIMARY KEY (" 1166 + NameLookupColumns.DATA_ID + ", " 1167 + NameLookupColumns.NORMALIZED_NAME + ", " 1168 + NameLookupColumns.NAME_TYPE + ")" + 1169 ");"); 1170 1171 db.execSQL("CREATE INDEX name_lookup_raw_contact_id_index ON " + Tables.NAME_LOOKUP + " (" + 1172 NameLookupColumns.RAW_CONTACT_ID + 1173 ");"); 1174 1175 db.execSQL("CREATE TABLE " + Tables.NICKNAME_LOOKUP + " (" + 1176 NicknameLookupColumns.NAME + " TEXT," + 1177 NicknameLookupColumns.CLUSTER + " TEXT" + 1178 ");"); 1179 1180 db.execSQL("CREATE UNIQUE INDEX nickname_lookup_index ON " + Tables.NICKNAME_LOOKUP + " (" + 1181 NicknameLookupColumns.NAME + ", " + 1182 NicknameLookupColumns.CLUSTER + 1183 ");"); 1184 1185 // Groups table 1186 db.execSQL("CREATE TABLE " + Tables.GROUPS + " (" + 1187 Groups._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 1188 GroupsColumns.PACKAGE_ID + " INTEGER REFERENCES package(_id)," + 1189 GroupsColumns.ACCOUNT_ID + " INTEGER REFERENCES " + 1190 Tables.ACCOUNTS + "(" + AccountsColumns._ID + ")," + 1191 Groups.SOURCE_ID + " TEXT," + 1192 Groups.VERSION + " INTEGER NOT NULL DEFAULT 1," + 1193 Groups.DIRTY + " INTEGER NOT NULL DEFAULT 0," + 1194 Groups.TITLE + " TEXT," + 1195 Groups.TITLE_RES + " INTEGER," + 1196 Groups.NOTES + " TEXT," + 1197 Groups.SYSTEM_ID + " TEXT," + 1198 Groups.DELETED + " INTEGER NOT NULL DEFAULT 0," + 1199 Groups.GROUP_VISIBLE + " INTEGER NOT NULL DEFAULT 0," + 1200 Groups.SHOULD_SYNC + " INTEGER NOT NULL DEFAULT 1," + 1201 Groups.AUTO_ADD + " INTEGER NOT NULL DEFAULT 0," + 1202 Groups.FAVORITES + " INTEGER NOT NULL DEFAULT 0," + 1203 Groups.GROUP_IS_READ_ONLY + " INTEGER NOT NULL DEFAULT 0," + 1204 Groups.SYNC1 + " TEXT, " + 1205 Groups.SYNC2 + " TEXT, " + 1206 Groups.SYNC3 + " TEXT, " + 1207 Groups.SYNC4 + " TEXT " + 1208 ");"); 1209 1210 db.execSQL("CREATE INDEX groups_source_id_account_id_index ON " + Tables.GROUPS + " (" + 1211 Groups.SOURCE_ID + ", " + 1212 GroupsColumns.ACCOUNT_ID + 1213 ");"); 1214 1215 db.execSQL("CREATE TABLE IF NOT EXISTS " + Tables.AGGREGATION_EXCEPTIONS + " (" + 1216 AggregationExceptionColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 1217 AggregationExceptions.TYPE + " INTEGER NOT NULL, " + 1218 AggregationExceptions.RAW_CONTACT_ID1 1219 + " INTEGER REFERENCES raw_contacts(_id), " + 1220 AggregationExceptions.RAW_CONTACT_ID2 1221 + " INTEGER REFERENCES raw_contacts(_id)" + 1222 ");"); 1223 1224 db.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS aggregation_exception_index1 ON " + 1225 Tables.AGGREGATION_EXCEPTIONS + " (" + 1226 AggregationExceptions.RAW_CONTACT_ID1 + ", " + 1227 AggregationExceptions.RAW_CONTACT_ID2 + 1228 ");"); 1229 1230 db.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS aggregation_exception_index2 ON " + 1231 Tables.AGGREGATION_EXCEPTIONS + " (" + 1232 AggregationExceptions.RAW_CONTACT_ID2 + ", " + 1233 AggregationExceptions.RAW_CONTACT_ID1 + 1234 ");"); 1235 1236 db.execSQL("CREATE TABLE IF NOT EXISTS " + Tables.SETTINGS + " (" + 1237 Settings.ACCOUNT_NAME + " STRING NOT NULL," + 1238 Settings.ACCOUNT_TYPE + " STRING NOT NULL," + 1239 Settings.DATA_SET + " STRING," + 1240 Settings.UNGROUPED_VISIBLE + " INTEGER NOT NULL DEFAULT 0," + 1241 Settings.SHOULD_SYNC + " INTEGER NOT NULL DEFAULT 1" + 1242 ");"); 1243 1244 db.execSQL("CREATE TABLE " + Tables.VISIBLE_CONTACTS + " (" + 1245 Contacts._ID + " INTEGER PRIMARY KEY" + 1246 ");"); 1247 1248 db.execSQL("CREATE TABLE " + Tables.DEFAULT_DIRECTORY + " (" + 1249 Contacts._ID + " INTEGER PRIMARY KEY" + 1250 ");"); 1251 1252 // The table for recent calls is here so we can do table joins 1253 // on people, phones, and calls all in one place. 1254 db.execSQL("CREATE TABLE " + Tables.CALLS + " (" + 1255 Calls._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 1256 Calls.NUMBER + " TEXT," + 1257 Calls.DATE + " INTEGER," + 1258 Calls.DURATION + " INTEGER," + 1259 Calls.TYPE + " INTEGER," + 1260 Calls.NEW + " INTEGER," + 1261 Calls.CACHED_NAME + " TEXT," + 1262 Calls.CACHED_NUMBER_TYPE + " INTEGER," + 1263 Calls.CACHED_NUMBER_LABEL + " TEXT," + 1264 Calls.COUNTRY_ISO + " TEXT," + 1265 Calls.VOICEMAIL_URI + " TEXT," + 1266 Calls.IS_READ + " INTEGER," + 1267 Calls.GEOCODED_LOCATION + " TEXT," + 1268 Calls.CACHED_LOOKUP_URI + " TEXT," + 1269 Calls.CACHED_MATCHED_NUMBER + " TEXT," + 1270 Calls.CACHED_NORMALIZED_NUMBER + " TEXT," + 1271 Calls.CACHED_PHOTO_ID + " INTEGER NOT NULL DEFAULT 0," + 1272 Calls.CACHED_FORMATTED_NUMBER + " TEXT," + 1273 Voicemails._DATA + " TEXT," + 1274 Voicemails.HAS_CONTENT + " INTEGER," + 1275 Voicemails.MIME_TYPE + " TEXT," + 1276 Voicemails.SOURCE_DATA + " TEXT," + 1277 Voicemails.SOURCE_PACKAGE + " TEXT," + 1278 Voicemails.STATE + " INTEGER" + 1279 ");"); 1280 1281 // Voicemail source status table. 1282 db.execSQL("CREATE TABLE " + Tables.VOICEMAIL_STATUS + " (" + 1283 VoicemailContract.Status._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 1284 VoicemailContract.Status.SOURCE_PACKAGE + " TEXT UNIQUE NOT NULL," + 1285 VoicemailContract.Status.SETTINGS_URI + " TEXT," + 1286 VoicemailContract.Status.VOICEMAIL_ACCESS_URI + " TEXT," + 1287 VoicemailContract.Status.CONFIGURATION_STATE + " INTEGER," + 1288 VoicemailContract.Status.DATA_CHANNEL_STATE + " INTEGER," + 1289 VoicemailContract.Status.NOTIFICATION_CHANNEL_STATE + " INTEGER" + 1290 ");"); 1291 1292 db.execSQL("CREATE TABLE " + Tables.STATUS_UPDATES + " (" + 1293 StatusUpdatesColumns.DATA_ID + " INTEGER PRIMARY KEY REFERENCES data(_id)," + 1294 StatusUpdates.STATUS + " TEXT," + 1295 StatusUpdates.STATUS_TIMESTAMP + " INTEGER," + 1296 StatusUpdates.STATUS_RES_PACKAGE + " TEXT, " + 1297 StatusUpdates.STATUS_LABEL + " INTEGER, " + 1298 StatusUpdates.STATUS_ICON + " INTEGER" + 1299 ");"); 1300 1301 db.execSQL("CREATE TABLE " + Tables.PROPERTIES + " (" + 1302 PropertiesColumns.PROPERTY_KEY + " TEXT PRIMARY KEY, " + 1303 PropertiesColumns.PROPERTY_VALUE + " TEXT " + 1304 ");"); 1305 1306 createDirectoriesTable(db); 1307 createSearchIndexTable(db, false /* we build stats table later */); 1308 1309 db.execSQL("CREATE TABLE " + Tables.DATA_USAGE_STAT + "(" + 1310 DataUsageStatColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 1311 DataUsageStatColumns.DATA_ID + " INTEGER NOT NULL, " + 1312 DataUsageStatColumns.USAGE_TYPE_INT + " INTEGER NOT NULL DEFAULT 0, " + 1313 DataUsageStatColumns.TIMES_USED + " INTEGER NOT NULL DEFAULT 0, " + 1314 DataUsageStatColumns.LAST_TIME_USED + " INTERGER NOT NULL DEFAULT 0, " + 1315 "FOREIGN KEY(" + DataUsageStatColumns.DATA_ID + ") REFERENCES " 1316 + Tables.DATA + "(" + Data._ID + ")" + 1317 ");"); 1318 db.execSQL("CREATE UNIQUE INDEX data_usage_stat_index ON " + 1319 Tables.DATA_USAGE_STAT + " (" + 1320 DataUsageStatColumns.DATA_ID + ", " + 1321 DataUsageStatColumns.USAGE_TYPE_INT + 1322 ");"); 1323 1324 // When adding new tables, be sure to also add size-estimates in updateSqliteStats 1325 createContactsViews(db); 1326 createGroupsView(db); 1327 createContactsTriggers(db); 1328 createContactsIndexes(db, false /* we build stats table later */); 1329 1330 loadNicknameLookupTable(db); 1331 1332 // Set sequence starts. 1333 initializeAutoIncrementSequences(db); 1334 1335 // Add the legacy API support views, etc 1336 LegacyApiSupport.createDatabase(db); 1337 1338 if (mDatabaseOptimizationEnabled) { 1339 // This will create a sqlite_stat1 table that is used for query optimization 1340 db.execSQL("ANALYZE;"); 1341 1342 updateSqliteStats(db); 1343 } 1344 1345 ContentResolver.requestSync(null /* all accounts */, 1346 ContactsContract.AUTHORITY, new Bundle()); 1347 1348 // Only send broadcasts for regular contacts db. 1349 if (dbForProfile() == 0) { 1350 mContext.sendBroadcast(new Intent(ContactsContract.Intents.CONTACTS_DATABASE_CREATED), 1351 android.Manifest.permission.READ_CONTACTS); 1352 } 1353 } 1354 initializeAutoIncrementSequences(SQLiteDatabase db)1355 protected void initializeAutoIncrementSequences(SQLiteDatabase db) { 1356 // Default implementation does nothing. 1357 } 1358 createDirectoriesTable(SQLiteDatabase db)1359 private void createDirectoriesTable(SQLiteDatabase db) { 1360 db.execSQL("CREATE TABLE " + Tables.DIRECTORIES + "(" + 1361 Directory._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 1362 Directory.PACKAGE_NAME + " TEXT NOT NULL," + 1363 Directory.DIRECTORY_AUTHORITY + " TEXT NOT NULL," + 1364 Directory.TYPE_RESOURCE_ID + " INTEGER," + 1365 DirectoryColumns.TYPE_RESOURCE_NAME + " TEXT," + 1366 Directory.ACCOUNT_TYPE + " TEXT," + 1367 Directory.ACCOUNT_NAME + " TEXT," + 1368 Directory.DISPLAY_NAME + " TEXT, " + 1369 Directory.EXPORT_SUPPORT + " INTEGER NOT NULL" + 1370 " DEFAULT " + Directory.EXPORT_SUPPORT_NONE + "," + 1371 Directory.SHORTCUT_SUPPORT + " INTEGER NOT NULL" + 1372 " DEFAULT " + Directory.SHORTCUT_SUPPORT_NONE + "," + 1373 Directory.PHOTO_SUPPORT + " INTEGER NOT NULL" + 1374 " DEFAULT " + Directory.PHOTO_SUPPORT_NONE + 1375 ");"); 1376 1377 // Trigger a full scan of directories in the system 1378 setProperty(db, DbProperties.DIRECTORY_SCAN_COMPLETE, "0"); 1379 } 1380 createSearchIndexTable(SQLiteDatabase db, boolean rebuildSqliteStats)1381 public void createSearchIndexTable(SQLiteDatabase db, boolean rebuildSqliteStats) { 1382 db.execSQL("DROP TABLE IF EXISTS " + Tables.SEARCH_INDEX); 1383 db.execSQL("CREATE VIRTUAL TABLE " + Tables.SEARCH_INDEX 1384 + " USING FTS4 (" 1385 + SearchIndexColumns.CONTACT_ID + " INTEGER REFERENCES contacts(_id) NOT NULL," 1386 + SearchIndexColumns.CONTENT + " TEXT, " 1387 + SearchIndexColumns.NAME + " TEXT, " 1388 + SearchIndexColumns.TOKENS + " TEXT" 1389 + ")"); 1390 if (rebuildSqliteStats) { 1391 updateSqliteStats(db); 1392 } 1393 } 1394 createContactsTriggers(SQLiteDatabase db)1395 private void createContactsTriggers(SQLiteDatabase db) { 1396 1397 /* 1398 * Automatically delete Data rows when a raw contact is deleted. 1399 */ 1400 db.execSQL("DROP TRIGGER IF EXISTS " + Tables.RAW_CONTACTS + "_deleted;"); 1401 db.execSQL("CREATE TRIGGER " + Tables.RAW_CONTACTS + "_deleted " 1402 + " BEFORE DELETE ON " + Tables.RAW_CONTACTS 1403 + " BEGIN " 1404 + " DELETE FROM " + Tables.DATA 1405 + " WHERE " + Data.RAW_CONTACT_ID 1406 + "=OLD." + RawContacts._ID + ";" 1407 + " DELETE FROM " + Tables.AGGREGATION_EXCEPTIONS 1408 + " WHERE " + AggregationExceptions.RAW_CONTACT_ID1 1409 + "=OLD." + RawContacts._ID 1410 + " OR " + AggregationExceptions.RAW_CONTACT_ID2 1411 + "=OLD." + RawContacts._ID + ";" 1412 + " DELETE FROM " + Tables.VISIBLE_CONTACTS 1413 + " WHERE " + Contacts._ID + "=OLD." + RawContacts.CONTACT_ID 1414 + " AND (SELECT COUNT(*) FROM " + Tables.RAW_CONTACTS 1415 + " WHERE " + RawContacts.CONTACT_ID + "=OLD." + RawContacts.CONTACT_ID 1416 + " )=1;" 1417 + " DELETE FROM " + Tables.DEFAULT_DIRECTORY 1418 + " WHERE " + Contacts._ID + "=OLD." + RawContacts.CONTACT_ID 1419 + " AND (SELECT COUNT(*) FROM " + Tables.RAW_CONTACTS 1420 + " WHERE " + RawContacts.CONTACT_ID + "=OLD." + RawContacts.CONTACT_ID 1421 + " )=1;" 1422 + " DELETE FROM " + Tables.CONTACTS 1423 + " WHERE " + Contacts._ID + "=OLD." + RawContacts.CONTACT_ID 1424 + " AND (SELECT COUNT(*) FROM " + Tables.RAW_CONTACTS 1425 + " WHERE " + RawContacts.CONTACT_ID + "=OLD." + RawContacts.CONTACT_ID 1426 + " )=1;" 1427 + " END"); 1428 1429 1430 db.execSQL("DROP TRIGGER IF EXISTS contacts_times_contacted;"); 1431 db.execSQL("DROP TRIGGER IF EXISTS raw_contacts_times_contacted;"); 1432 1433 /* 1434 * Triggers that update {@link RawContacts#VERSION} when the contact is 1435 * marked for deletion or any time a data row is inserted, updated or 1436 * deleted. 1437 */ 1438 db.execSQL("DROP TRIGGER IF EXISTS " + Tables.RAW_CONTACTS + "_marked_deleted;"); 1439 db.execSQL("CREATE TRIGGER " + Tables.RAW_CONTACTS + "_marked_deleted " 1440 + " AFTER UPDATE ON " + Tables.RAW_CONTACTS 1441 + " BEGIN " 1442 + " UPDATE " + Tables.RAW_CONTACTS 1443 + " SET " 1444 + RawContacts.VERSION + "=OLD." + RawContacts.VERSION + "+1 " 1445 + " WHERE " + RawContacts._ID + "=OLD." + RawContacts._ID 1446 + " AND NEW." + RawContacts.DELETED + "!= OLD." + RawContacts.DELETED + ";" 1447 + " END"); 1448 1449 db.execSQL("DROP TRIGGER IF EXISTS " + Tables.DATA + "_updated;"); 1450 db.execSQL("CREATE TRIGGER " + Tables.DATA + "_updated AFTER UPDATE ON " + Tables.DATA 1451 + " BEGIN " 1452 + " UPDATE " + Tables.DATA 1453 + " SET " + Data.DATA_VERSION + "=OLD." + Data.DATA_VERSION + "+1 " 1454 + " WHERE " + Data._ID + "=OLD." + Data._ID + ";" 1455 + " UPDATE " + Tables.RAW_CONTACTS 1456 + " SET " + RawContacts.VERSION + "=" + RawContacts.VERSION + "+1 " 1457 + " WHERE " + RawContacts._ID + "=OLD." + Data.RAW_CONTACT_ID + ";" 1458 + " END"); 1459 1460 db.execSQL("DROP TRIGGER IF EXISTS " + Tables.DATA + "_deleted;"); 1461 db.execSQL("CREATE TRIGGER " + Tables.DATA + "_deleted BEFORE DELETE ON " + Tables.DATA 1462 + " BEGIN " 1463 + " UPDATE " + Tables.RAW_CONTACTS 1464 + " SET " + RawContacts.VERSION + "=" + RawContacts.VERSION + "+1 " 1465 + " WHERE " + RawContacts._ID + "=OLD." + Data.RAW_CONTACT_ID + ";" 1466 + " DELETE FROM " + Tables.PHONE_LOOKUP 1467 + " WHERE " + PhoneLookupColumns.DATA_ID + "=OLD." + Data._ID + ";" 1468 + " DELETE FROM " + Tables.STATUS_UPDATES 1469 + " WHERE " + StatusUpdatesColumns.DATA_ID + "=OLD." + Data._ID + ";" 1470 + " DELETE FROM " + Tables.NAME_LOOKUP 1471 + " WHERE " + NameLookupColumns.DATA_ID + "=OLD." + Data._ID + ";" 1472 + " END"); 1473 1474 1475 db.execSQL("DROP TRIGGER IF EXISTS " + Tables.GROUPS + "_updated1;"); 1476 db.execSQL("CREATE TRIGGER " + Tables.GROUPS + "_updated1 " 1477 + " AFTER UPDATE ON " + Tables.GROUPS 1478 + " BEGIN " 1479 + " UPDATE " + Tables.GROUPS 1480 + " SET " 1481 + Groups.VERSION + "=OLD." + Groups.VERSION + "+1" 1482 + " WHERE " + Groups._ID + "=OLD." + Groups._ID + ";" 1483 + " END"); 1484 1485 // Update DEFAULT_FILTER table per AUTO_ADD column update. 1486 // See also upgradeToVersion411(). 1487 final String insertContactsWithoutAccount = ( 1488 " INSERT OR IGNORE INTO " + Tables.DEFAULT_DIRECTORY + 1489 " SELECT " + RawContacts.CONTACT_ID + 1490 " FROM " + Tables.RAW_CONTACTS + 1491 " WHERE " + RawContactsColumns.CONCRETE_ACCOUNT_ID + 1492 "=" + Clauses.LOCAL_ACCOUNT_ID + ";"); 1493 final String insertContactsWithAccountNoDefaultGroup = ( 1494 " INSERT OR IGNORE INTO " + Tables.DEFAULT_DIRECTORY + 1495 " SELECT " + RawContacts.CONTACT_ID + 1496 " FROM " + Tables.RAW_CONTACTS + 1497 " WHERE NOT EXISTS" + 1498 " (SELECT " + Groups._ID + 1499 " FROM " + Tables.GROUPS + 1500 " WHERE " + RawContactsColumns.CONCRETE_ACCOUNT_ID + " = " + 1501 GroupsColumns.CONCRETE_ACCOUNT_ID + 1502 " AND " + Groups.AUTO_ADD + " != 0" + ");"); 1503 final String insertContactsWithAccountDefaultGroup = ( 1504 " INSERT OR IGNORE INTO " + Tables.DEFAULT_DIRECTORY + 1505 " SELECT " + RawContacts.CONTACT_ID + 1506 " FROM " + Tables.RAW_CONTACTS + 1507 " JOIN " + Tables.DATA + 1508 " ON (" + RawContactsColumns.CONCRETE_ID + "=" + 1509 Data.RAW_CONTACT_ID + ")" + 1510 " WHERE " + DataColumns.MIMETYPE_ID + "=" + 1511 "(SELECT " + MimetypesColumns._ID + " FROM " + Tables.MIMETYPES + 1512 " WHERE " + MimetypesColumns.MIMETYPE + 1513 "='" + GroupMembership.CONTENT_ITEM_TYPE + "')" + 1514 " AND EXISTS" + 1515 " (SELECT " + Groups._ID + 1516 " FROM " + Tables.GROUPS + 1517 " WHERE " + RawContactsColumns.CONCRETE_ACCOUNT_ID + " = " + 1518 GroupsColumns.CONCRETE_ACCOUNT_ID + 1519 " AND " + Groups.AUTO_ADD + " != 0" + ");"); 1520 1521 db.execSQL("DROP TRIGGER IF EXISTS " + Tables.GROUPS + "_auto_add_updated1;"); 1522 db.execSQL("CREATE TRIGGER " + Tables.GROUPS + "_auto_add_updated1 " 1523 + " AFTER UPDATE OF " + Groups.AUTO_ADD + " ON " + Tables.GROUPS 1524 + " BEGIN " 1525 + " DELETE FROM " + Tables.DEFAULT_DIRECTORY + ";" 1526 + insertContactsWithoutAccount 1527 + insertContactsWithAccountNoDefaultGroup 1528 + insertContactsWithAccountDefaultGroup 1529 + " END"); 1530 } 1531 createContactsIndexes(SQLiteDatabase db, boolean rebuildSqliteStats)1532 private void createContactsIndexes(SQLiteDatabase db, boolean rebuildSqliteStats) { 1533 db.execSQL("DROP INDEX IF EXISTS name_lookup_index"); 1534 db.execSQL("CREATE INDEX name_lookup_index ON " + Tables.NAME_LOOKUP + " (" + 1535 NameLookupColumns.NORMALIZED_NAME + "," + 1536 NameLookupColumns.NAME_TYPE + ", " + 1537 NameLookupColumns.RAW_CONTACT_ID + ", " + 1538 NameLookupColumns.DATA_ID + 1539 ");"); 1540 1541 db.execSQL("DROP INDEX IF EXISTS raw_contact_sort_key1_index"); 1542 db.execSQL("CREATE INDEX raw_contact_sort_key1_index ON " + Tables.RAW_CONTACTS + " (" + 1543 RawContacts.SORT_KEY_PRIMARY + 1544 ");"); 1545 1546 db.execSQL("DROP INDEX IF EXISTS raw_contact_sort_key2_index"); 1547 db.execSQL("CREATE INDEX raw_contact_sort_key2_index ON " + Tables.RAW_CONTACTS + " (" + 1548 RawContacts.SORT_KEY_ALTERNATIVE + 1549 ");"); 1550 1551 if (rebuildSqliteStats) { 1552 updateSqliteStats(db); 1553 } 1554 } 1555 createContactsViews(SQLiteDatabase db)1556 private void createContactsViews(SQLiteDatabase db) { 1557 db.execSQL("DROP VIEW IF EXISTS " + Views.CONTACTS + ";"); 1558 db.execSQL("DROP VIEW IF EXISTS " + Views.DATA + ";"); 1559 db.execSQL("DROP VIEW IF EXISTS " + Views.RAW_CONTACTS + ";"); 1560 db.execSQL("DROP VIEW IF EXISTS " + Views.RAW_ENTITIES + ";"); 1561 db.execSQL("DROP VIEW IF EXISTS " + Views.ENTITIES + ";"); 1562 db.execSQL("DROP VIEW IF EXISTS " + Views.DATA_USAGE_STAT + ";"); 1563 db.execSQL("DROP VIEW IF EXISTS " + Views.STREAM_ITEMS + ";"); 1564 1565 String dataColumns = 1566 Data.IS_PRIMARY + ", " 1567 + Data.IS_SUPER_PRIMARY + ", " 1568 + Data.DATA_VERSION + ", " 1569 + DataColumns.CONCRETE_PACKAGE_ID + "," 1570 + PackagesColumns.PACKAGE + " AS " + Data.RES_PACKAGE + "," 1571 + DataColumns.CONCRETE_MIMETYPE_ID + "," 1572 + MimetypesColumns.MIMETYPE + " AS " + Data.MIMETYPE + ", " 1573 + Data.IS_READ_ONLY + ", " 1574 + Data.DATA1 + ", " 1575 + Data.DATA2 + ", " 1576 + Data.DATA3 + ", " 1577 + Data.DATA4 + ", " 1578 + Data.DATA5 + ", " 1579 + Data.DATA6 + ", " 1580 + Data.DATA7 + ", " 1581 + Data.DATA8 + ", " 1582 + Data.DATA9 + ", " 1583 + Data.DATA10 + ", " 1584 + Data.DATA11 + ", " 1585 + Data.DATA12 + ", " 1586 + Data.DATA13 + ", " 1587 + Data.DATA14 + ", " 1588 + Data.DATA15 + ", " 1589 + Data.SYNC1 + ", " 1590 + Data.SYNC2 + ", " 1591 + Data.SYNC3 + ", " 1592 + Data.SYNC4; 1593 1594 String syncColumns = 1595 RawContactsColumns.CONCRETE_ACCOUNT_ID + "," 1596 + AccountsColumns.CONCRETE_ACCOUNT_NAME + " AS " + RawContacts.ACCOUNT_NAME + "," 1597 + AccountsColumns.CONCRETE_ACCOUNT_TYPE + " AS " + RawContacts.ACCOUNT_TYPE + "," 1598 + AccountsColumns.CONCRETE_DATA_SET + " AS " + RawContacts.DATA_SET + "," 1599 + "(CASE WHEN " + AccountsColumns.CONCRETE_DATA_SET + " IS NULL THEN " 1600 + AccountsColumns.CONCRETE_ACCOUNT_TYPE 1601 + " ELSE " + AccountsColumns.CONCRETE_ACCOUNT_TYPE + "||'/'||" 1602 + AccountsColumns.CONCRETE_DATA_SET + " END) AS " 1603 + RawContacts.ACCOUNT_TYPE_AND_DATA_SET + "," 1604 + RawContactsColumns.CONCRETE_SOURCE_ID + " AS " + RawContacts.SOURCE_ID + "," 1605 + RawContactsColumns.CONCRETE_NAME_VERIFIED + " AS " 1606 + RawContacts.NAME_VERIFIED + "," 1607 + RawContactsColumns.CONCRETE_VERSION + " AS " + RawContacts.VERSION + "," 1608 + RawContactsColumns.CONCRETE_DIRTY + " AS " + RawContacts.DIRTY + "," 1609 + RawContactsColumns.CONCRETE_SYNC1 + " AS " + RawContacts.SYNC1 + "," 1610 + RawContactsColumns.CONCRETE_SYNC2 + " AS " + RawContacts.SYNC2 + "," 1611 + RawContactsColumns.CONCRETE_SYNC3 + " AS " + RawContacts.SYNC3 + "," 1612 + RawContactsColumns.CONCRETE_SYNC4 + " AS " + RawContacts.SYNC4; 1613 1614 String baseContactColumns = 1615 Contacts.HAS_PHONE_NUMBER + ", " 1616 + Contacts.NAME_RAW_CONTACT_ID + ", " 1617 + Contacts.LOOKUP_KEY + ", " 1618 + Contacts.PHOTO_ID + ", " 1619 + Contacts.PHOTO_FILE_ID + ", " 1620 + "CAST(" + Clauses.CONTACT_VISIBLE + " AS INTEGER) AS " 1621 + Contacts.IN_VISIBLE_GROUP + ", " 1622 + ContactsColumns.LAST_STATUS_UPDATE_ID + ", " 1623 + ContactsColumns.CONCRETE_CONTACT_LAST_UPDATED_TIMESTAMP; 1624 1625 String contactOptionColumns = 1626 ContactsColumns.CONCRETE_CUSTOM_RINGTONE 1627 + " AS " + RawContacts.CUSTOM_RINGTONE + "," 1628 + ContactsColumns.CONCRETE_SEND_TO_VOICEMAIL 1629 + " AS " + RawContacts.SEND_TO_VOICEMAIL + "," 1630 + ContactsColumns.CONCRETE_LAST_TIME_CONTACTED 1631 + " AS " + RawContacts.LAST_TIME_CONTACTED + "," 1632 + ContactsColumns.CONCRETE_TIMES_CONTACTED 1633 + " AS " + RawContacts.TIMES_CONTACTED + "," 1634 + ContactsColumns.CONCRETE_STARRED 1635 + " AS " + RawContacts.STARRED; 1636 1637 String contactNameColumns = 1638 "name_raw_contact." + RawContacts.DISPLAY_NAME_SOURCE 1639 + " AS " + Contacts.DISPLAY_NAME_SOURCE + ", " 1640 + "name_raw_contact." + RawContacts.DISPLAY_NAME_PRIMARY 1641 + " AS " + Contacts.DISPLAY_NAME_PRIMARY + ", " 1642 + "name_raw_contact." + RawContacts.DISPLAY_NAME_ALTERNATIVE 1643 + " AS " + Contacts.DISPLAY_NAME_ALTERNATIVE + ", " 1644 + "name_raw_contact." + RawContacts.PHONETIC_NAME 1645 + " AS " + Contacts.PHONETIC_NAME + ", " 1646 + "name_raw_contact." + RawContacts.PHONETIC_NAME_STYLE 1647 + " AS " + Contacts.PHONETIC_NAME_STYLE + ", " 1648 + "name_raw_contact." + RawContacts.SORT_KEY_PRIMARY 1649 + " AS " + Contacts.SORT_KEY_PRIMARY + ", " 1650 + "name_raw_contact." + RawContactsColumns.PHONEBOOK_LABEL_PRIMARY 1651 + " AS " + ContactsColumns.PHONEBOOK_LABEL_PRIMARY + ", " 1652 + "name_raw_contact." + RawContactsColumns.PHONEBOOK_BUCKET_PRIMARY 1653 + " AS " + ContactsColumns.PHONEBOOK_BUCKET_PRIMARY + ", " 1654 + "name_raw_contact." + RawContacts.SORT_KEY_ALTERNATIVE 1655 + " AS " + Contacts.SORT_KEY_ALTERNATIVE + ", " 1656 + "name_raw_contact." + RawContactsColumns.PHONEBOOK_LABEL_ALTERNATIVE 1657 + " AS " + ContactsColumns.PHONEBOOK_LABEL_ALTERNATIVE + ", " 1658 + "name_raw_contact." + RawContactsColumns.PHONEBOOK_BUCKET_ALTERNATIVE 1659 + " AS " + ContactsColumns.PHONEBOOK_BUCKET_ALTERNATIVE; 1660 1661 String dataSelect = "SELECT " 1662 + DataColumns.CONCRETE_ID + " AS " + Data._ID + "," 1663 + Data.RAW_CONTACT_ID + ", " 1664 + RawContactsColumns.CONCRETE_CONTACT_ID + " AS " + RawContacts.CONTACT_ID + ", " 1665 + syncColumns + ", " 1666 + dataColumns + ", " 1667 + contactOptionColumns + ", " 1668 + contactNameColumns + ", " 1669 + baseContactColumns + ", " 1670 + buildDisplayPhotoUriAlias(RawContactsColumns.CONCRETE_CONTACT_ID, 1671 Contacts.PHOTO_URI) + ", " 1672 + buildThumbnailPhotoUriAlias(RawContactsColumns.CONCRETE_CONTACT_ID, 1673 Contacts.PHOTO_THUMBNAIL_URI) + ", " 1674 + dbForProfile() + " AS " + RawContacts.RAW_CONTACT_IS_USER_PROFILE + ", " 1675 + Tables.GROUPS + "." + Groups.SOURCE_ID + " AS " + GroupMembership.GROUP_SOURCE_ID 1676 + " FROM " + Tables.DATA 1677 + " JOIN " + Tables.MIMETYPES + " ON (" 1678 + DataColumns.CONCRETE_MIMETYPE_ID + "=" + MimetypesColumns.CONCRETE_ID + ")" 1679 + " JOIN " + Tables.RAW_CONTACTS + " ON (" 1680 + DataColumns.CONCRETE_RAW_CONTACT_ID + "=" + RawContactsColumns.CONCRETE_ID + ")" 1681 + " JOIN " + Tables.ACCOUNTS + " ON (" 1682 + RawContactsColumns.CONCRETE_ACCOUNT_ID + "=" + AccountsColumns.CONCRETE_ID 1683 + ")" 1684 + " JOIN " + Tables.CONTACTS + " ON (" 1685 + RawContactsColumns.CONCRETE_CONTACT_ID + "=" + ContactsColumns.CONCRETE_ID + ")" 1686 + " JOIN " + Tables.RAW_CONTACTS + " AS name_raw_contact ON(" 1687 + Contacts.NAME_RAW_CONTACT_ID + "=name_raw_contact." + RawContacts._ID + ")" 1688 + " LEFT OUTER JOIN " + Tables.PACKAGES + " ON (" 1689 + DataColumns.CONCRETE_PACKAGE_ID + "=" + PackagesColumns.CONCRETE_ID + ")" 1690 + " LEFT OUTER JOIN " + Tables.GROUPS + " ON (" 1691 + MimetypesColumns.CONCRETE_MIMETYPE + "='" + GroupMembership.CONTENT_ITEM_TYPE 1692 + "' AND " + GroupsColumns.CONCRETE_ID + "=" 1693 + Tables.DATA + "." + GroupMembership.GROUP_ROW_ID + ")"; 1694 1695 db.execSQL("CREATE VIEW " + Views.DATA + " AS " + dataSelect); 1696 1697 String rawContactOptionColumns = 1698 RawContacts.CUSTOM_RINGTONE + "," 1699 + RawContacts.SEND_TO_VOICEMAIL + "," 1700 + RawContacts.LAST_TIME_CONTACTED + "," 1701 + RawContacts.TIMES_CONTACTED + "," 1702 + RawContacts.STARRED; 1703 1704 String rawContactsSelect = "SELECT " 1705 + RawContactsColumns.CONCRETE_ID + " AS " + RawContacts._ID + "," 1706 + RawContacts.CONTACT_ID + ", " 1707 + RawContacts.AGGREGATION_MODE + ", " 1708 + RawContacts.RAW_CONTACT_IS_READ_ONLY + ", " 1709 + RawContacts.DELETED + ", " 1710 + RawContacts.DISPLAY_NAME_SOURCE + ", " 1711 + RawContacts.DISPLAY_NAME_PRIMARY + ", " 1712 + RawContacts.DISPLAY_NAME_ALTERNATIVE + ", " 1713 + RawContacts.PHONETIC_NAME + ", " 1714 + RawContacts.PHONETIC_NAME_STYLE + ", " 1715 + RawContacts.SORT_KEY_PRIMARY + ", " 1716 + RawContactsColumns.PHONEBOOK_LABEL_PRIMARY + ", " 1717 + RawContactsColumns.PHONEBOOK_BUCKET_PRIMARY + ", " 1718 + RawContacts.SORT_KEY_ALTERNATIVE + ", " 1719 + RawContactsColumns.PHONEBOOK_LABEL_ALTERNATIVE + ", " 1720 + RawContactsColumns.PHONEBOOK_BUCKET_ALTERNATIVE + ", " 1721 + dbForProfile() + " AS " + RawContacts.RAW_CONTACT_IS_USER_PROFILE + ", " 1722 + rawContactOptionColumns + ", " 1723 + syncColumns 1724 + " FROM " + Tables.RAW_CONTACTS 1725 + " JOIN " + Tables.ACCOUNTS + " ON (" 1726 + RawContactsColumns.CONCRETE_ACCOUNT_ID + "=" + AccountsColumns.CONCRETE_ID 1727 + ")"; 1728 1729 db.execSQL("CREATE VIEW " + Views.RAW_CONTACTS + " AS " + rawContactsSelect); 1730 1731 String contactsColumns = 1732 ContactsColumns.CONCRETE_CUSTOM_RINGTONE 1733 + " AS " + Contacts.CUSTOM_RINGTONE + ", " 1734 + contactNameColumns + ", " 1735 + baseContactColumns + ", " 1736 + ContactsColumns.CONCRETE_LAST_TIME_CONTACTED 1737 + " AS " + Contacts.LAST_TIME_CONTACTED + ", " 1738 + ContactsColumns.CONCRETE_SEND_TO_VOICEMAIL 1739 + " AS " + Contacts.SEND_TO_VOICEMAIL + ", " 1740 + ContactsColumns.CONCRETE_STARRED 1741 + " AS " + Contacts.STARRED + ", " 1742 + ContactsColumns.CONCRETE_TIMES_CONTACTED 1743 + " AS " + Contacts.TIMES_CONTACTED; 1744 1745 String contactsSelect = "SELECT " 1746 + ContactsColumns.CONCRETE_ID + " AS " + Contacts._ID + "," 1747 + contactsColumns + ", " 1748 + buildDisplayPhotoUriAlias(ContactsColumns.CONCRETE_ID, Contacts.PHOTO_URI) + ", " 1749 + buildThumbnailPhotoUriAlias(ContactsColumns.CONCRETE_ID, 1750 Contacts.PHOTO_THUMBNAIL_URI) + ", " 1751 + dbForProfile() + " AS " + Contacts.IS_USER_PROFILE 1752 + " FROM " + Tables.CONTACTS 1753 + " JOIN " + Tables.RAW_CONTACTS + " AS name_raw_contact ON(" 1754 + Contacts.NAME_RAW_CONTACT_ID + "=name_raw_contact." + RawContacts._ID + ")"; 1755 1756 db.execSQL("CREATE VIEW " + Views.CONTACTS + " AS " + contactsSelect); 1757 1758 String rawEntitiesSelect = "SELECT " 1759 + RawContacts.CONTACT_ID + ", " 1760 + RawContactsColumns.CONCRETE_DELETED + " AS " + RawContacts.DELETED + "," 1761 + dataColumns + ", " 1762 + syncColumns + ", " 1763 + Data.SYNC1 + ", " 1764 + Data.SYNC2 + ", " 1765 + Data.SYNC3 + ", " 1766 + Data.SYNC4 + ", " 1767 + RawContactsColumns.CONCRETE_ID + " AS " + RawContacts._ID + ", " 1768 + DataColumns.CONCRETE_ID + " AS " + RawContacts.Entity.DATA_ID + "," 1769 + RawContactsColumns.CONCRETE_STARRED + " AS " + RawContacts.STARRED + "," 1770 + dbForProfile() + " AS " + RawContacts.RAW_CONTACT_IS_USER_PROFILE + "," 1771 + Tables.GROUPS + "." + Groups.SOURCE_ID + " AS " + GroupMembership.GROUP_SOURCE_ID 1772 + " FROM " + Tables.RAW_CONTACTS 1773 + " JOIN " + Tables.ACCOUNTS + " ON (" 1774 + RawContactsColumns.CONCRETE_ACCOUNT_ID + "=" + AccountsColumns.CONCRETE_ID 1775 + ")" 1776 + " LEFT OUTER JOIN " + Tables.DATA + " ON (" 1777 + DataColumns.CONCRETE_RAW_CONTACT_ID + "=" + RawContactsColumns.CONCRETE_ID + ")" 1778 + " LEFT OUTER JOIN " + Tables.PACKAGES + " ON (" 1779 + DataColumns.CONCRETE_PACKAGE_ID + "=" + PackagesColumns.CONCRETE_ID + ")" 1780 + " LEFT OUTER JOIN " + Tables.MIMETYPES + " ON (" 1781 + DataColumns.CONCRETE_MIMETYPE_ID + "=" + MimetypesColumns.CONCRETE_ID + ")" 1782 + " LEFT OUTER JOIN " + Tables.GROUPS + " ON (" 1783 + MimetypesColumns.CONCRETE_MIMETYPE + "='" + GroupMembership.CONTENT_ITEM_TYPE 1784 + "' AND " + GroupsColumns.CONCRETE_ID + "=" 1785 + Tables.DATA + "." + GroupMembership.GROUP_ROW_ID + ")"; 1786 1787 db.execSQL("CREATE VIEW " + Views.RAW_ENTITIES + " AS " 1788 + rawEntitiesSelect); 1789 1790 String entitiesSelect = "SELECT " 1791 + RawContactsColumns.CONCRETE_CONTACT_ID + " AS " + Contacts._ID + ", " 1792 + RawContactsColumns.CONCRETE_CONTACT_ID + " AS " + RawContacts.CONTACT_ID + ", " 1793 + RawContactsColumns.CONCRETE_DELETED + " AS " + RawContacts.DELETED + "," 1794 + dataColumns + ", " 1795 + syncColumns + ", " 1796 + contactsColumns + ", " 1797 + buildDisplayPhotoUriAlias(RawContactsColumns.CONCRETE_CONTACT_ID, 1798 Contacts.PHOTO_URI) + ", " 1799 + buildThumbnailPhotoUriAlias(RawContactsColumns.CONCRETE_CONTACT_ID, 1800 Contacts.PHOTO_THUMBNAIL_URI) + ", " 1801 + dbForProfile() + " AS " + Contacts.IS_USER_PROFILE + ", " 1802 + Data.SYNC1 + ", " 1803 + Data.SYNC2 + ", " 1804 + Data.SYNC3 + ", " 1805 + Data.SYNC4 + ", " 1806 + RawContactsColumns.CONCRETE_ID + " AS " + Contacts.Entity.RAW_CONTACT_ID + ", " 1807 + DataColumns.CONCRETE_ID + " AS " + Contacts.Entity.DATA_ID + "," 1808 + Tables.GROUPS + "." + Groups.SOURCE_ID + " AS " + GroupMembership.GROUP_SOURCE_ID 1809 + " FROM " + Tables.RAW_CONTACTS 1810 + " JOIN " + Tables.ACCOUNTS + " ON (" 1811 + RawContactsColumns.CONCRETE_ACCOUNT_ID + "=" + AccountsColumns.CONCRETE_ID 1812 + ")" 1813 + " JOIN " + Tables.CONTACTS + " ON (" 1814 + RawContactsColumns.CONCRETE_CONTACT_ID + "=" + ContactsColumns.CONCRETE_ID + ")" 1815 + " JOIN " + Tables.RAW_CONTACTS + " AS name_raw_contact ON(" 1816 + Contacts.NAME_RAW_CONTACT_ID + "=name_raw_contact." + RawContacts._ID + ")" 1817 + " LEFT OUTER JOIN " + Tables.DATA + " ON (" 1818 + DataColumns.CONCRETE_RAW_CONTACT_ID + "=" + RawContactsColumns.CONCRETE_ID + ")" 1819 + " LEFT OUTER JOIN " + Tables.PACKAGES + " ON (" 1820 + DataColumns.CONCRETE_PACKAGE_ID + "=" + PackagesColumns.CONCRETE_ID + ")" 1821 + " LEFT OUTER JOIN " + Tables.MIMETYPES + " ON (" 1822 + DataColumns.CONCRETE_MIMETYPE_ID + "=" + MimetypesColumns.CONCRETE_ID + ")" 1823 + " LEFT OUTER JOIN " + Tables.GROUPS + " ON (" 1824 + MimetypesColumns.CONCRETE_MIMETYPE + "='" + GroupMembership.CONTENT_ITEM_TYPE 1825 + "' AND " + GroupsColumns.CONCRETE_ID + "=" 1826 + Tables.DATA + "." + GroupMembership.GROUP_ROW_ID + ")"; 1827 1828 db.execSQL("CREATE VIEW " + Views.ENTITIES + " AS " 1829 + entitiesSelect); 1830 1831 String dataUsageStatSelect = "SELECT " 1832 + DataUsageStatColumns.CONCRETE_ID + " AS " + DataUsageStatColumns._ID + ", " 1833 + DataUsageStatColumns.DATA_ID + ", " 1834 + RawContactsColumns.CONCRETE_CONTACT_ID + " AS " + RawContacts.CONTACT_ID + ", " 1835 + MimetypesColumns.CONCRETE_MIMETYPE + " AS " + Data.MIMETYPE + ", " 1836 + DataUsageStatColumns.USAGE_TYPE_INT + ", " 1837 + DataUsageStatColumns.TIMES_USED + ", " 1838 + DataUsageStatColumns.LAST_TIME_USED 1839 + " FROM " + Tables.DATA_USAGE_STAT 1840 + " JOIN " + Tables.DATA + " ON (" 1841 + DataColumns.CONCRETE_ID + "=" + DataUsageStatColumns.CONCRETE_DATA_ID + ")" 1842 + " JOIN " + Tables.RAW_CONTACTS + " ON (" 1843 + RawContactsColumns.CONCRETE_ID + "=" + DataColumns.CONCRETE_RAW_CONTACT_ID 1844 + " )" 1845 + " JOIN " + Tables.MIMETYPES + " ON (" 1846 + MimetypesColumns.CONCRETE_ID + "=" + DataColumns.CONCRETE_MIMETYPE_ID + ")"; 1847 1848 db.execSQL("CREATE VIEW " + Views.DATA_USAGE_STAT + " AS " + dataUsageStatSelect); 1849 1850 String streamItemSelect = "SELECT " + 1851 StreamItemsColumns.CONCRETE_ID + ", " + 1852 ContactsColumns.CONCRETE_ID + " AS " + StreamItems.CONTACT_ID + ", " + 1853 ContactsColumns.CONCRETE_LOOKUP_KEY + 1854 " AS " + StreamItems.CONTACT_LOOKUP_KEY + ", " + 1855 AccountsColumns.CONCRETE_ACCOUNT_NAME + ", " + 1856 AccountsColumns.CONCRETE_ACCOUNT_TYPE + ", " + 1857 AccountsColumns.CONCRETE_DATA_SET + ", " + 1858 StreamItemsColumns.CONCRETE_RAW_CONTACT_ID + 1859 " as " + StreamItems.RAW_CONTACT_ID + ", " + 1860 RawContactsColumns.CONCRETE_SOURCE_ID + 1861 " as " + StreamItems.RAW_CONTACT_SOURCE_ID + ", " + 1862 StreamItemsColumns.CONCRETE_PACKAGE + ", " + 1863 StreamItemsColumns.CONCRETE_ICON + ", " + 1864 StreamItemsColumns.CONCRETE_LABEL + ", " + 1865 StreamItemsColumns.CONCRETE_TEXT + ", " + 1866 StreamItemsColumns.CONCRETE_TIMESTAMP + ", " + 1867 StreamItemsColumns.CONCRETE_COMMENTS + ", " + 1868 StreamItemsColumns.CONCRETE_SYNC1 + ", " + 1869 StreamItemsColumns.CONCRETE_SYNC2 + ", " + 1870 StreamItemsColumns.CONCRETE_SYNC3 + ", " + 1871 StreamItemsColumns.CONCRETE_SYNC4 + 1872 " FROM " + Tables.STREAM_ITEMS 1873 + " JOIN " + Tables.RAW_CONTACTS + " ON (" 1874 + StreamItemsColumns.CONCRETE_RAW_CONTACT_ID + "=" + RawContactsColumns.CONCRETE_ID 1875 + ")" 1876 + " JOIN " + Tables.ACCOUNTS + " ON (" 1877 + RawContactsColumns.CONCRETE_ACCOUNT_ID + "=" + AccountsColumns.CONCRETE_ID 1878 + ")" 1879 + " JOIN " + Tables.CONTACTS + " ON (" 1880 + RawContactsColumns.CONCRETE_CONTACT_ID + "=" + ContactsColumns.CONCRETE_ID + ")"; 1881 1882 db.execSQL("CREATE VIEW " + Views.STREAM_ITEMS + " AS " + streamItemSelect); 1883 } 1884 buildDisplayPhotoUriAlias(String contactIdColumn, String alias)1885 private static String buildDisplayPhotoUriAlias(String contactIdColumn, String alias) { 1886 return "(CASE WHEN " + Contacts.PHOTO_FILE_ID + " IS NULL THEN (CASE WHEN " 1887 + Contacts.PHOTO_ID + " IS NULL" 1888 + " OR " + Contacts.PHOTO_ID + "=0" 1889 + " THEN NULL" 1890 + " ELSE '" + Contacts.CONTENT_URI + "/'||" 1891 + contactIdColumn + "|| '/" + Photo.CONTENT_DIRECTORY + "'" 1892 + " END) ELSE '" + DisplayPhoto.CONTENT_URI + "/'||" 1893 + Contacts.PHOTO_FILE_ID + " END)" 1894 + " AS " + alias; 1895 } 1896 buildThumbnailPhotoUriAlias(String contactIdColumn, String alias)1897 private static String buildThumbnailPhotoUriAlias(String contactIdColumn, String alias) { 1898 return "(CASE WHEN " 1899 + Contacts.PHOTO_ID + " IS NULL" 1900 + " OR " + Contacts.PHOTO_ID + "=0" 1901 + " THEN NULL" 1902 + " ELSE '" + Contacts.CONTENT_URI + "/'||" 1903 + contactIdColumn + "|| '/" + Photo.CONTENT_DIRECTORY + "'" 1904 + " END)" 1905 + " AS " + alias; 1906 } 1907 1908 /** 1909 * Returns the value to be returned when querying the column indicating that the contact 1910 * or raw contact belongs to the user's personal profile. Overridden in the profile 1911 * DB helper subclass. 1912 */ dbForProfile()1913 protected int dbForProfile() { 1914 return 0; 1915 } 1916 createGroupsView(SQLiteDatabase db)1917 private void createGroupsView(SQLiteDatabase db) { 1918 db.execSQL("DROP VIEW IF EXISTS " + Views.GROUPS + ";"); 1919 1920 String groupsColumns = 1921 GroupsColumns.CONCRETE_ACCOUNT_ID + " AS " + GroupsColumns.ACCOUNT_ID + "," 1922 + AccountsColumns.CONCRETE_ACCOUNT_NAME + " AS " + Groups.ACCOUNT_NAME + "," 1923 + AccountsColumns.CONCRETE_ACCOUNT_TYPE + " AS " + Groups.ACCOUNT_TYPE + "," 1924 + AccountsColumns.CONCRETE_DATA_SET + " AS " + Groups.DATA_SET + "," 1925 + "(CASE WHEN " + AccountsColumns.CONCRETE_DATA_SET 1926 + " IS NULL THEN " + AccountsColumns.CONCRETE_ACCOUNT_TYPE 1927 + " ELSE " + AccountsColumns.CONCRETE_ACCOUNT_TYPE 1928 + "||'/'||" + AccountsColumns.CONCRETE_DATA_SET + " END) AS " 1929 + Groups.ACCOUNT_TYPE_AND_DATA_SET + "," 1930 + Groups.SOURCE_ID + "," 1931 + Groups.VERSION + "," 1932 + Groups.DIRTY + "," 1933 + Groups.TITLE + "," 1934 + Groups.TITLE_RES + "," 1935 + Groups.NOTES + "," 1936 + Groups.SYSTEM_ID + "," 1937 + Groups.DELETED + "," 1938 + Groups.GROUP_VISIBLE + "," 1939 + Groups.SHOULD_SYNC + "," 1940 + Groups.AUTO_ADD + "," 1941 + Groups.FAVORITES + "," 1942 + Groups.GROUP_IS_READ_ONLY + "," 1943 + Groups.SYNC1 + "," 1944 + Groups.SYNC2 + "," 1945 + Groups.SYNC3 + "," 1946 + Groups.SYNC4 + "," 1947 + PackagesColumns.PACKAGE + " AS " + Groups.RES_PACKAGE; 1948 1949 String groupsSelect = "SELECT " 1950 + GroupsColumns.CONCRETE_ID + " AS " + Groups._ID + "," 1951 + groupsColumns 1952 + " FROM " + Tables.GROUPS 1953 + " JOIN " + Tables.ACCOUNTS + " ON (" 1954 + GroupsColumns.CONCRETE_ACCOUNT_ID + "=" + AccountsColumns.CONCRETE_ID + ")" 1955 + " LEFT OUTER JOIN " + Tables.PACKAGES + " ON (" 1956 + GroupsColumns.CONCRETE_PACKAGE_ID + "=" + PackagesColumns.CONCRETE_ID + ")"; 1957 1958 db.execSQL("CREATE VIEW " + Views.GROUPS + " AS " + groupsSelect); 1959 } 1960 1961 @Override onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion)1962 public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) { 1963 Log.i(TAG, "ContactsProvider cannot proceed because downgrading your database is not " + 1964 "supported. To continue, please either re-upgrade to your previous Android " + 1965 "version, or clear all application data in Contacts Storage (this will result " + 1966 "in the loss of all local contacts that are not synced). To avoid data loss, " + 1967 "your contacts database will not be wiped automatically."); 1968 super.onDowngrade(db, oldVersion, newVersion); 1969 } 1970 1971 @Override onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)1972 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 1973 if (oldVersion < 99) { 1974 Log.i(TAG, "Upgrading from version " + oldVersion + " to " + newVersion 1975 + ", data will be lost!"); 1976 1977 db.execSQL("DROP TABLE IF EXISTS " + Tables.CONTACTS + ";"); 1978 db.execSQL("DROP TABLE IF EXISTS " + Tables.RAW_CONTACTS + ";"); 1979 db.execSQL("DROP TABLE IF EXISTS " + Tables.PACKAGES + ";"); 1980 db.execSQL("DROP TABLE IF EXISTS " + Tables.MIMETYPES + ";"); 1981 db.execSQL("DROP TABLE IF EXISTS " + Tables.DATA + ";"); 1982 db.execSQL("DROP TABLE IF EXISTS " + Tables.PHONE_LOOKUP + ";"); 1983 db.execSQL("DROP TABLE IF EXISTS " + Tables.NAME_LOOKUP + ";"); 1984 db.execSQL("DROP TABLE IF EXISTS " + Tables.NICKNAME_LOOKUP + ";"); 1985 db.execSQL("DROP TABLE IF EXISTS " + Tables.GROUPS + ";"); 1986 db.execSQL("DROP TABLE IF EXISTS activities;"); 1987 db.execSQL("DROP TABLE IF EXISTS " + Tables.CALLS + ";"); 1988 db.execSQL("DROP TABLE IF EXISTS " + Tables.SETTINGS + ";"); 1989 db.execSQL("DROP TABLE IF EXISTS " + Tables.STATUS_UPDATES + ";"); 1990 1991 // TODO: we should not be dropping agg_exceptions and contact_options. In case that 1992 // table's schema changes, we should try to preserve the data, because it was entered 1993 // by the user and has never been synched to the server. 1994 db.execSQL("DROP TABLE IF EXISTS " + Tables.AGGREGATION_EXCEPTIONS + ";"); 1995 1996 onCreate(db); 1997 return; 1998 } 1999 2000 Log.i(TAG, "Upgrading from version " + oldVersion + " to " + newVersion); 2001 2002 boolean upgradeViewsAndTriggers = false; 2003 boolean upgradeNameLookup = false; 2004 boolean upgradeLegacyApiSupport = false; 2005 boolean upgradeSearchIndex = false; 2006 boolean rescanDirectories = false; 2007 boolean rebuildSqliteStats = false; 2008 boolean upgradeLocaleSpecificData = false; 2009 2010 if (oldVersion == 99) { 2011 upgradeViewsAndTriggers = true; 2012 oldVersion++; 2013 } 2014 2015 if (oldVersion == 100) { 2016 db.execSQL("CREATE INDEX IF NOT EXISTS mimetypes_mimetype_index ON " 2017 + Tables.MIMETYPES + " (" 2018 + MimetypesColumns.MIMETYPE + "," 2019 + MimetypesColumns._ID + ");"); 2020 updateIndexStats(db, Tables.MIMETYPES, 2021 "mimetypes_mimetype_index", "50 1 1"); 2022 2023 upgradeViewsAndTriggers = true; 2024 oldVersion++; 2025 } 2026 2027 if (oldVersion == 101) { 2028 upgradeViewsAndTriggers = true; 2029 oldVersion++; 2030 } 2031 2032 if (oldVersion == 102) { 2033 upgradeViewsAndTriggers = true; 2034 oldVersion++; 2035 } 2036 2037 if (oldVersion == 103) { 2038 upgradeViewsAndTriggers = true; 2039 oldVersion++; 2040 } 2041 2042 if (oldVersion == 104 || oldVersion == 201) { 2043 LegacyApiSupport.createSettingsTable(db); 2044 upgradeViewsAndTriggers = true; 2045 oldVersion++; 2046 } 2047 2048 if (oldVersion == 105) { 2049 upgradeToVersion202(db); 2050 upgradeNameLookup = true; 2051 oldVersion = 202; 2052 } 2053 2054 if (oldVersion == 202) { 2055 upgradeToVersion203(db); 2056 upgradeViewsAndTriggers = true; 2057 oldVersion++; 2058 } 2059 2060 if (oldVersion == 203) { 2061 upgradeViewsAndTriggers = true; 2062 oldVersion++; 2063 } 2064 2065 if (oldVersion == 204) { 2066 upgradeToVersion205(db); 2067 upgradeViewsAndTriggers = true; 2068 oldVersion++; 2069 } 2070 2071 if (oldVersion == 205) { 2072 upgrateToVersion206(db); 2073 upgradeViewsAndTriggers = true; 2074 oldVersion++; 2075 } 2076 2077 if (oldVersion == 206) { 2078 upgradeToVersion300(db); 2079 oldVersion = 300; 2080 } 2081 2082 if (oldVersion == 300) { 2083 upgradeViewsAndTriggers = true; 2084 oldVersion = 301; 2085 } 2086 2087 if (oldVersion == 301) { 2088 upgradeViewsAndTriggers = true; 2089 oldVersion = 302; 2090 } 2091 2092 if (oldVersion == 302) { 2093 upgradeEmailToVersion303(db); 2094 upgradeNicknameToVersion303(db); 2095 oldVersion = 303; 2096 } 2097 2098 if (oldVersion == 303) { 2099 upgradeToVersion304(db); 2100 oldVersion = 304; 2101 } 2102 2103 if (oldVersion == 304) { 2104 upgradeNameLookup = true; 2105 oldVersion = 305; 2106 } 2107 2108 if (oldVersion == 305) { 2109 upgradeToVersion306(db); 2110 oldVersion = 306; 2111 } 2112 2113 if (oldVersion == 306) { 2114 upgradeToVersion307(db); 2115 oldVersion = 307; 2116 } 2117 2118 if (oldVersion == 307) { 2119 upgradeToVersion308(db); 2120 oldVersion = 308; 2121 } 2122 2123 // Gingerbread upgrades 2124 if (oldVersion < 350) { 2125 upgradeViewsAndTriggers = true; 2126 oldVersion = 351; 2127 } 2128 2129 if (oldVersion == 351) { 2130 upgradeNameLookup = true; 2131 oldVersion = 352; 2132 } 2133 2134 if (oldVersion == 352) { 2135 upgradeToVersion353(db); 2136 oldVersion = 353; 2137 } 2138 2139 // Honeycomb upgrades 2140 if (oldVersion < 400) { 2141 upgradeViewsAndTriggers = true; 2142 upgradeToVersion400(db); 2143 oldVersion = 400; 2144 } 2145 2146 if (oldVersion == 400) { 2147 upgradeViewsAndTriggers = true; 2148 upgradeToVersion401(db); 2149 oldVersion = 401; 2150 } 2151 2152 if (oldVersion == 401) { 2153 upgradeToVersion402(db); 2154 oldVersion = 402; 2155 } 2156 2157 if (oldVersion == 402) { 2158 upgradeViewsAndTriggers = true; 2159 upgradeToVersion403(db); 2160 oldVersion = 403; 2161 } 2162 2163 if (oldVersion == 403) { 2164 upgradeViewsAndTriggers = true; 2165 oldVersion = 404; 2166 } 2167 2168 if (oldVersion == 404) { 2169 upgradeViewsAndTriggers = true; 2170 upgradeToVersion405(db); 2171 oldVersion = 405; 2172 } 2173 2174 if (oldVersion == 405) { 2175 upgradeViewsAndTriggers = true; 2176 upgradeToVersion406(db); 2177 oldVersion = 406; 2178 } 2179 2180 if (oldVersion == 406) { 2181 upgradeViewsAndTriggers = true; 2182 oldVersion = 407; 2183 } 2184 2185 if (oldVersion == 407) { 2186 // Obsolete 2187 oldVersion = 408; 2188 } 2189 2190 if (oldVersion == 408) { 2191 upgradeViewsAndTriggers = true; 2192 upgradeToVersion409(db); 2193 oldVersion = 409; 2194 } 2195 2196 if (oldVersion == 409) { 2197 upgradeViewsAndTriggers = true; 2198 oldVersion = 410; 2199 } 2200 2201 if (oldVersion == 410) { 2202 upgradeToVersion411(db); 2203 oldVersion = 411; 2204 } 2205 2206 if (oldVersion == 411) { 2207 // Same upgrade as 353, only on Honeycomb devices 2208 upgradeToVersion353(db); 2209 oldVersion = 412; 2210 } 2211 2212 if (oldVersion == 412) { 2213 upgradeToVersion413(db); 2214 oldVersion = 413; 2215 } 2216 2217 if (oldVersion == 413) { 2218 upgradeNameLookup = true; 2219 oldVersion = 414; 2220 } 2221 2222 if (oldVersion == 414) { 2223 upgradeToVersion415(db); 2224 upgradeViewsAndTriggers = true; 2225 oldVersion = 415; 2226 } 2227 2228 if (oldVersion == 415) { 2229 upgradeToVersion416(db); 2230 oldVersion = 416; 2231 } 2232 2233 if (oldVersion == 416) { 2234 upgradeLegacyApiSupport = true; 2235 oldVersion = 417; 2236 } 2237 2238 // Honeycomb-MR1 upgrades 2239 if (oldVersion < 500) { 2240 upgradeSearchIndex = true; 2241 } 2242 2243 if (oldVersion < 501) { 2244 upgradeSearchIndex = true; 2245 upgradeToVersion501(db); 2246 oldVersion = 501; 2247 } 2248 2249 if (oldVersion < 502) { 2250 upgradeSearchIndex = true; 2251 upgradeToVersion502(db); 2252 oldVersion = 502; 2253 } 2254 2255 if (oldVersion < 503) { 2256 upgradeSearchIndex = true; 2257 oldVersion = 503; 2258 } 2259 2260 if (oldVersion < 504) { 2261 upgradeToVersion504(db); 2262 oldVersion = 504; 2263 } 2264 2265 if (oldVersion < 600) { 2266 upgradeToVersion600(db); 2267 upgradeViewsAndTriggers = true; 2268 oldVersion = 600; 2269 } 2270 2271 if (oldVersion < 601) { 2272 upgradeToVersion601(db); 2273 oldVersion = 601; 2274 } 2275 2276 if (oldVersion < 602) { 2277 upgradeToVersion602(db); 2278 oldVersion = 602; 2279 } 2280 2281 if (oldVersion < 603) { 2282 upgradeViewsAndTriggers = true; 2283 oldVersion = 603; 2284 } 2285 2286 if (oldVersion < 604) { 2287 upgradeToVersion604(db); 2288 oldVersion = 604; 2289 } 2290 2291 if (oldVersion < 605) { 2292 upgradeViewsAndTriggers = true; 2293 upgradeToVersion605(db); 2294 oldVersion = 605; 2295 } 2296 2297 if (oldVersion < 606) { 2298 upgradeViewsAndTriggers = true; 2299 upgradeLegacyApiSupport = true; 2300 upgradeToVersion606(db); 2301 oldVersion = 606; 2302 } 2303 2304 if (oldVersion < 607) { 2305 upgradeViewsAndTriggers = true; 2306 upgradeToVersion607(db); 2307 oldVersion = 607; 2308 } 2309 2310 if (oldVersion < 608) { 2311 upgradeViewsAndTriggers = true; 2312 upgradeToVersion608(db); 2313 oldVersion = 608; 2314 } 2315 2316 if (oldVersion < 609) { 2317 upgradeToVersion609(db); 2318 oldVersion = 609; 2319 } 2320 2321 if (oldVersion < 610) { 2322 upgradeToVersion610(db); 2323 oldVersion = 610; 2324 } 2325 2326 if (oldVersion < 611) { 2327 upgradeViewsAndTriggers = true; 2328 upgradeToVersion611(db); 2329 oldVersion = 611; 2330 } 2331 2332 if (oldVersion < 612) { 2333 upgradeViewsAndTriggers = true; 2334 upgradeToVersion612(db); 2335 oldVersion = 612; 2336 } 2337 2338 if (oldVersion < 613) { 2339 upgradeToVersion613(db); 2340 oldVersion = 613; 2341 } 2342 2343 if (oldVersion < 614) { 2344 // this creates the view "view_stream_items" 2345 upgradeViewsAndTriggers = true; 2346 oldVersion = 614; 2347 } 2348 2349 if (oldVersion < 615) { 2350 upgradeToVersion615(db); 2351 oldVersion = 615; 2352 } 2353 2354 if (oldVersion < 616) { 2355 // this updates the "view_stream_items" view 2356 upgradeViewsAndTriggers = true; 2357 oldVersion = 616; 2358 } 2359 2360 if (oldVersion < 617) { 2361 // This version upgrade obsoleted the profile_raw_contact_id field of the Accounts 2362 // table, but we aren't removing the column because it is very little data (and not 2363 // referenced anymore). We do need to upgrade the views to handle the simplified 2364 // per-database "is profile" columns. 2365 upgradeViewsAndTriggers = true; 2366 oldVersion = 617; 2367 } 2368 2369 if (oldVersion < 618) { 2370 upgradeToVersion618(db); 2371 oldVersion = 618; 2372 } 2373 2374 if (oldVersion < 619) { 2375 upgradeViewsAndTriggers = true; 2376 oldVersion = 619; 2377 } 2378 2379 if (oldVersion < 620) { 2380 upgradeViewsAndTriggers = true; 2381 oldVersion = 620; 2382 } 2383 2384 if (oldVersion < 621) { 2385 upgradeSearchIndex = true; 2386 oldVersion = 621; 2387 } 2388 2389 if (oldVersion < 622) { 2390 upgradeToVersion622(db); 2391 oldVersion = 622; 2392 } 2393 2394 if (oldVersion < 623) { 2395 // change FTS to normalize names using collation key 2396 upgradeSearchIndex = true; 2397 oldVersion = 623; 2398 } 2399 2400 if (oldVersion < 624) { 2401 // Upgraded the sqlite index stats 2402 upgradeViewsAndTriggers = true; 2403 oldVersion = 624; 2404 } 2405 2406 if (oldVersion < 625) { 2407 // Fix for search for hyphenated names 2408 upgradeSearchIndex = true; 2409 oldVersion = 625; 2410 } 2411 2412 if (oldVersion < 626) { 2413 upgradeToVersion626(db); 2414 upgradeViewsAndTriggers = true; 2415 oldVersion = 626; 2416 } 2417 2418 if (oldVersion < 700) { 2419 rescanDirectories = true; 2420 oldVersion = 700; 2421 } 2422 2423 if (oldVersion < 701) { 2424 upgradeToVersion701(db); 2425 oldVersion = 701; 2426 } 2427 2428 if (oldVersion < 702) { 2429 upgradeToVersion702(db); 2430 oldVersion = 702; 2431 } 2432 2433 if (oldVersion < 703) { 2434 // Now names like "L'Image" will be searchable. 2435 upgradeSearchIndex = true; 2436 oldVersion = 703; 2437 } 2438 2439 if (oldVersion < 704) { 2440 db.execSQL("DROP TABLE IF EXISTS activities;"); 2441 oldVersion = 704; 2442 } 2443 2444 if (oldVersion < 705) { 2445 // Before this version, we didn't rebuild the search index on locale changes, so 2446 // if the locale has changed after sync, the index contains gets stale. 2447 // To correct the issue we have to rebuild the index here. 2448 upgradeSearchIndex = true; 2449 oldVersion = 705; 2450 } 2451 2452 if (oldVersion < 706) { 2453 // Prior to this version, we didn't rebuild the stats table after drop operations, 2454 // which resulted in losing some of the rows from the stats table. 2455 rebuildSqliteStats = true; 2456 oldVersion = 706; 2457 } 2458 2459 if (oldVersion < 707) { 2460 upgradeToVersion707(db); 2461 upgradeViewsAndTriggers = true; 2462 oldVersion = 707; 2463 } 2464 2465 if (oldVersion < 708) { 2466 // Sort keys, phonebook labels and buckets, and search keys have 2467 // changed so force a rebuild. 2468 upgradeLocaleSpecificData = true; 2469 oldVersion = 708; 2470 } 2471 if (oldVersion < 709) { 2472 // Added secondary locale phonebook labels; changed Japanese 2473 // and Chinese sort keys. 2474 upgradeLocaleSpecificData = true; 2475 oldVersion = 709; 2476 } 2477 2478 if (oldVersion < 710) { 2479 upgradeToVersion710(db); 2480 upgradeViewsAndTriggers = true; 2481 oldVersion = 710; 2482 } 2483 2484 if (upgradeViewsAndTriggers) { 2485 createContactsViews(db); 2486 createGroupsView(db); 2487 createContactsTriggers(db); 2488 createContactsIndexes(db, false /* we build stats table later */); 2489 upgradeLegacyApiSupport = true; 2490 rebuildSqliteStats = true; 2491 } 2492 2493 if (upgradeLegacyApiSupport) { 2494 LegacyApiSupport.createViews(db); 2495 } 2496 2497 if (upgradeLocaleSpecificData) { 2498 upgradeLocaleData(db, false /* we build stats table later */); 2499 // Name lookups are rebuilt as part of the full locale rebuild 2500 upgradeNameLookup = false; 2501 upgradeSearchIndex = true; 2502 rebuildSqliteStats = true; 2503 } 2504 2505 if (upgradeNameLookup) { 2506 rebuildNameLookup(db, false /* we build stats table later */); 2507 rebuildSqliteStats = true; 2508 } 2509 2510 if (upgradeSearchIndex) { 2511 rebuildSearchIndex(db, false /* we build stats table later */); 2512 rebuildSqliteStats = true; 2513 } 2514 2515 if (rescanDirectories) { 2516 // Force the next ContactDirectoryManager.scanAllPackages() to rescan all packages. 2517 // (It's called from the BACKGROUND_TASK_UPDATE_ACCOUNTS background task.) 2518 setProperty(db, DbProperties.DIRECTORY_SCAN_COMPLETE, "0"); 2519 } 2520 2521 if (rebuildSqliteStats) { 2522 updateSqliteStats(db); 2523 } 2524 2525 if (oldVersion != newVersion) { 2526 throw new IllegalStateException( 2527 "error upgrading the database to version " + newVersion); 2528 } 2529 } 2530 upgradeToVersion202(SQLiteDatabase db)2531 private void upgradeToVersion202(SQLiteDatabase db) { 2532 db.execSQL( 2533 "ALTER TABLE " + Tables.PHONE_LOOKUP + 2534 " ADD " + PhoneLookupColumns.MIN_MATCH + " TEXT;"); 2535 2536 db.execSQL("CREATE INDEX phone_lookup_min_match_index ON " + Tables.PHONE_LOOKUP + " (" + 2537 PhoneLookupColumns.MIN_MATCH + "," + 2538 PhoneLookupColumns.RAW_CONTACT_ID + "," + 2539 PhoneLookupColumns.DATA_ID + 2540 ");"); 2541 2542 updateIndexStats(db, Tables.PHONE_LOOKUP, 2543 "phone_lookup_min_match_index", "10000 2 2 1"); 2544 2545 SQLiteStatement update = db.compileStatement( 2546 "UPDATE " + Tables.PHONE_LOOKUP + 2547 " SET " + PhoneLookupColumns.MIN_MATCH + "=?" + 2548 " WHERE " + PhoneLookupColumns.DATA_ID + "=?"); 2549 2550 // Populate the new column 2551 Cursor c = db.query(Tables.PHONE_LOOKUP + " JOIN " + Tables.DATA + 2552 " ON (" + PhoneLookupColumns.DATA_ID + "=" + DataColumns.CONCRETE_ID + ")", 2553 new String[]{Data._ID, Phone.NUMBER}, null, null, null, null, null); 2554 try { 2555 while (c.moveToNext()) { 2556 long dataId = c.getLong(0); 2557 String number = c.getString(1); 2558 if (!TextUtils.isEmpty(number)) { 2559 update.bindString(1, PhoneNumberUtils.toCallerIDMinMatch(number)); 2560 update.bindLong(2, dataId); 2561 update.execute(); 2562 } 2563 } 2564 } finally { 2565 c.close(); 2566 } 2567 } 2568 upgradeToVersion203(SQLiteDatabase db)2569 private void upgradeToVersion203(SQLiteDatabase db) { 2570 // Garbage-collect first. A bug in Eclair was sometimes leaving 2571 // raw_contacts in the database that no longer had contacts associated 2572 // with them. To avoid failures during this database upgrade, drop 2573 // the orphaned raw_contacts. 2574 db.execSQL( 2575 "DELETE FROM raw_contacts" + 2576 " WHERE contact_id NOT NULL" + 2577 " AND contact_id NOT IN (SELECT _id FROM contacts)"); 2578 2579 db.execSQL( 2580 "ALTER TABLE " + Tables.CONTACTS + 2581 " ADD " + Contacts.NAME_RAW_CONTACT_ID + " INTEGER REFERENCES raw_contacts(_id)"); 2582 db.execSQL( 2583 "ALTER TABLE " + Tables.RAW_CONTACTS + 2584 " ADD contact_in_visible_group INTEGER NOT NULL DEFAULT 0"); 2585 2586 // For each Contact, find the RawContact that contributed the display name 2587 db.execSQL( 2588 "UPDATE " + Tables.CONTACTS + 2589 " SET " + Contacts.NAME_RAW_CONTACT_ID + "=(" + 2590 " SELECT " + RawContacts._ID + 2591 " FROM " + Tables.RAW_CONTACTS + 2592 " WHERE " + RawContacts.CONTACT_ID + "=" + ContactsColumns.CONCRETE_ID + 2593 " AND " + RawContactsColumns.CONCRETE_DISPLAY_NAME + "=" + 2594 Tables.CONTACTS + "." + Contacts.DISPLAY_NAME + 2595 " ORDER BY " + RawContacts._ID + 2596 " LIMIT 1)" 2597 ); 2598 2599 db.execSQL("CREATE INDEX contacts_name_raw_contact_id_index ON " + Tables.CONTACTS + " (" + 2600 Contacts.NAME_RAW_CONTACT_ID + 2601 ");"); 2602 2603 // If for some unknown reason we missed some names, let's make sure there are 2604 // no contacts without a name, picking a raw contact "at random". 2605 db.execSQL( 2606 "UPDATE " + Tables.CONTACTS + 2607 " SET " + Contacts.NAME_RAW_CONTACT_ID + "=(" + 2608 " SELECT " + RawContacts._ID + 2609 " FROM " + Tables.RAW_CONTACTS + 2610 " WHERE " + RawContacts.CONTACT_ID + "=" + ContactsColumns.CONCRETE_ID + 2611 " ORDER BY " + RawContacts._ID + 2612 " LIMIT 1)" + 2613 " WHERE " + Contacts.NAME_RAW_CONTACT_ID + " IS NULL" 2614 ); 2615 2616 // Wipe out DISPLAY_NAME on the Contacts table as it is no longer in use. 2617 db.execSQL( 2618 "UPDATE " + Tables.CONTACTS + 2619 " SET " + Contacts.DISPLAY_NAME + "=NULL" 2620 ); 2621 2622 // Copy the IN_VISIBLE_GROUP flag down to all raw contacts to allow 2623 // indexing on (display_name, in_visible_group) 2624 db.execSQL( 2625 "UPDATE " + Tables.RAW_CONTACTS + 2626 " SET contact_in_visible_group=(" + 2627 "SELECT " + Contacts.IN_VISIBLE_GROUP + 2628 " FROM " + Tables.CONTACTS + 2629 " WHERE " + Contacts._ID + "=" + RawContacts.CONTACT_ID + ")" + 2630 " WHERE " + RawContacts.CONTACT_ID + " NOT NULL" 2631 ); 2632 2633 db.execSQL("CREATE INDEX raw_contact_sort_key1_index ON " + Tables.RAW_CONTACTS + " (" + 2634 "contact_in_visible_group" + "," + 2635 RawContactsColumns.DISPLAY_NAME + " COLLATE LOCALIZED ASC" + 2636 ");"); 2637 2638 db.execSQL("DROP INDEX contacts_visible_index"); 2639 db.execSQL("CREATE INDEX contacts_visible_index ON " + Tables.CONTACTS + " (" + 2640 Contacts.IN_VISIBLE_GROUP + 2641 ");"); 2642 } 2643 upgradeToVersion205(SQLiteDatabase db)2644 private void upgradeToVersion205(SQLiteDatabase db) { 2645 db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS 2646 + " ADD " + RawContacts.DISPLAY_NAME_ALTERNATIVE + " TEXT;"); 2647 db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS 2648 + " ADD " + RawContacts.PHONETIC_NAME + " TEXT;"); 2649 db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS 2650 + " ADD " + RawContacts.PHONETIC_NAME_STYLE + " INTEGER;"); 2651 db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS 2652 + " ADD " + RawContacts.SORT_KEY_PRIMARY 2653 + " TEXT COLLATE " + ContactsProvider2.PHONEBOOK_COLLATOR_NAME + ";"); 2654 db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS 2655 + " ADD " + RawContacts.SORT_KEY_ALTERNATIVE 2656 + " TEXT COLLATE " + ContactsProvider2.PHONEBOOK_COLLATOR_NAME + ";"); 2657 2658 NameSplitter splitter = createNameSplitter(); 2659 2660 SQLiteStatement rawContactUpdate = db.compileStatement( 2661 "UPDATE " + Tables.RAW_CONTACTS + 2662 " SET " + 2663 RawContacts.DISPLAY_NAME_PRIMARY + "=?," + 2664 RawContacts.DISPLAY_NAME_ALTERNATIVE + "=?," + 2665 RawContacts.PHONETIC_NAME + "=?," + 2666 RawContacts.PHONETIC_NAME_STYLE + "=?," + 2667 RawContacts.SORT_KEY_PRIMARY + "=?," + 2668 RawContacts.SORT_KEY_ALTERNATIVE + "=?" + 2669 " WHERE " + RawContacts._ID + "=?"); 2670 2671 upgradeStructuredNamesToVersion205(db, rawContactUpdate, splitter); 2672 upgradeOrganizationsToVersion205(db, rawContactUpdate, splitter); 2673 2674 db.execSQL("DROP INDEX raw_contact_sort_key1_index"); 2675 db.execSQL("CREATE INDEX raw_contact_sort_key1_index ON " + Tables.RAW_CONTACTS + " (" + 2676 "contact_in_visible_group" + "," + 2677 RawContacts.SORT_KEY_PRIMARY + 2678 ");"); 2679 2680 db.execSQL("CREATE INDEX raw_contact_sort_key2_index ON " + Tables.RAW_CONTACTS + " (" + 2681 "contact_in_visible_group" + "," + 2682 RawContacts.SORT_KEY_ALTERNATIVE + 2683 ");"); 2684 } 2685 2686 private interface StructName205Query { 2687 String TABLE = Tables.DATA_JOIN_RAW_CONTACTS; 2688 2689 String COLUMNS[] = { 2690 DataColumns.CONCRETE_ID, 2691 Data.RAW_CONTACT_ID, 2692 RawContacts.DISPLAY_NAME_SOURCE, 2693 RawContacts.DISPLAY_NAME_PRIMARY, 2694 StructuredName.PREFIX, 2695 StructuredName.GIVEN_NAME, 2696 StructuredName.MIDDLE_NAME, 2697 StructuredName.FAMILY_NAME, 2698 StructuredName.SUFFIX, 2699 StructuredName.PHONETIC_FAMILY_NAME, 2700 StructuredName.PHONETIC_MIDDLE_NAME, 2701 StructuredName.PHONETIC_GIVEN_NAME, 2702 }; 2703 2704 int ID = 0; 2705 int RAW_CONTACT_ID = 1; 2706 int DISPLAY_NAME_SOURCE = 2; 2707 int DISPLAY_NAME = 3; 2708 int PREFIX = 4; 2709 int GIVEN_NAME = 5; 2710 int MIDDLE_NAME = 6; 2711 int FAMILY_NAME = 7; 2712 int SUFFIX = 8; 2713 int PHONETIC_FAMILY_NAME = 9; 2714 int PHONETIC_MIDDLE_NAME = 10; 2715 int PHONETIC_GIVEN_NAME = 11; 2716 } 2717 upgradeStructuredNamesToVersion205(SQLiteDatabase db, SQLiteStatement rawContactUpdate, NameSplitter splitter)2718 private void upgradeStructuredNamesToVersion205(SQLiteDatabase db, 2719 SQLiteStatement rawContactUpdate, NameSplitter splitter) { 2720 2721 // Process structured names to detect the style of the full name and phonetic name 2722 2723 long mMimeType; 2724 try { 2725 mMimeType = DatabaseUtils.longForQuery(db, 2726 "SELECT " + MimetypesColumns._ID + 2727 " FROM " + Tables.MIMETYPES + 2728 " WHERE " + MimetypesColumns.MIMETYPE 2729 + "='" + StructuredName.CONTENT_ITEM_TYPE + "'", null); 2730 } catch (SQLiteDoneException e) { 2731 // No structured names in the database 2732 return; 2733 } 2734 2735 SQLiteStatement structuredNameUpdate = db.compileStatement( 2736 "UPDATE " + Tables.DATA + 2737 " SET " + 2738 StructuredName.FULL_NAME_STYLE + "=?," + 2739 StructuredName.DISPLAY_NAME + "=?," + 2740 StructuredName.PHONETIC_NAME_STYLE + "=?" + 2741 " WHERE " + Data._ID + "=?"); 2742 2743 NameSplitter.Name name = new NameSplitter.Name(); 2744 StringBuilder sb = new StringBuilder(); 2745 Cursor cursor = db.query(StructName205Query.TABLE, 2746 StructName205Query.COLUMNS, 2747 DataColumns.MIMETYPE_ID + "=" + mMimeType, null, null, null, null); 2748 try { 2749 while (cursor.moveToNext()) { 2750 long dataId = cursor.getLong(StructName205Query.ID); 2751 long rawContactId = cursor.getLong(StructName205Query.RAW_CONTACT_ID); 2752 int displayNameSource = cursor.getInt(StructName205Query.DISPLAY_NAME_SOURCE); 2753 String displayName = cursor.getString(StructName205Query.DISPLAY_NAME); 2754 2755 name.clear(); 2756 name.prefix = cursor.getString(StructName205Query.PREFIX); 2757 name.givenNames = cursor.getString(StructName205Query.GIVEN_NAME); 2758 name.middleName = cursor.getString(StructName205Query.MIDDLE_NAME); 2759 name.familyName = cursor.getString(StructName205Query.FAMILY_NAME); 2760 name.suffix = cursor.getString(StructName205Query.SUFFIX); 2761 name.phoneticFamilyName = cursor.getString(StructName205Query.PHONETIC_FAMILY_NAME); 2762 name.phoneticMiddleName = cursor.getString(StructName205Query.PHONETIC_MIDDLE_NAME); 2763 name.phoneticGivenName = cursor.getString(StructName205Query.PHONETIC_GIVEN_NAME); 2764 2765 upgradeNameToVersion205(dataId, rawContactId, displayNameSource, displayName, name, 2766 structuredNameUpdate, rawContactUpdate, splitter, sb); 2767 } 2768 } finally { 2769 cursor.close(); 2770 } 2771 } 2772 upgradeNameToVersion205(long dataId, long rawContactId, int displayNameSource, String currentDisplayName, NameSplitter.Name name, SQLiteStatement structuredNameUpdate, SQLiteStatement rawContactUpdate, NameSplitter splitter, StringBuilder sb)2773 private void upgradeNameToVersion205(long dataId, long rawContactId, int displayNameSource, 2774 String currentDisplayName, NameSplitter.Name name, 2775 SQLiteStatement structuredNameUpdate, SQLiteStatement rawContactUpdate, 2776 NameSplitter splitter, StringBuilder sb) { 2777 2778 splitter.guessNameStyle(name); 2779 int unadjustedFullNameStyle = name.fullNameStyle; 2780 name.fullNameStyle = splitter.getAdjustedFullNameStyle(name.fullNameStyle); 2781 String displayName = splitter.join(name, true, true); 2782 2783 // Don't update database with the adjusted fullNameStyle as it is locale 2784 // related 2785 structuredNameUpdate.bindLong(1, unadjustedFullNameStyle); 2786 DatabaseUtils.bindObjectToProgram(structuredNameUpdate, 2, displayName); 2787 structuredNameUpdate.bindLong(3, name.phoneticNameStyle); 2788 structuredNameUpdate.bindLong(4, dataId); 2789 structuredNameUpdate.execute(); 2790 2791 if (displayNameSource == DisplayNameSources.STRUCTURED_NAME) { 2792 String displayNameAlternative = splitter.join(name, false, false); 2793 String phoneticName = splitter.joinPhoneticName(name); 2794 String sortKey = null; 2795 String sortKeyAlternative = null; 2796 2797 if (phoneticName != null) { 2798 sortKey = sortKeyAlternative = phoneticName; 2799 } else if (name.fullNameStyle == FullNameStyle.CHINESE || 2800 name.fullNameStyle == FullNameStyle.CJK) { 2801 sortKey = sortKeyAlternative = displayName; 2802 } 2803 2804 if (sortKey == null) { 2805 sortKey = displayName; 2806 sortKeyAlternative = displayNameAlternative; 2807 } 2808 2809 updateRawContact205(rawContactUpdate, rawContactId, displayName, 2810 displayNameAlternative, name.phoneticNameStyle, phoneticName, sortKey, 2811 sortKeyAlternative); 2812 } 2813 } 2814 2815 private interface Organization205Query { 2816 String TABLE = Tables.DATA_JOIN_RAW_CONTACTS; 2817 2818 String COLUMNS[] = { 2819 DataColumns.CONCRETE_ID, 2820 Data.RAW_CONTACT_ID, 2821 Organization.COMPANY, 2822 Organization.PHONETIC_NAME, 2823 }; 2824 2825 int ID = 0; 2826 int RAW_CONTACT_ID = 1; 2827 int COMPANY = 2; 2828 int PHONETIC_NAME = 3; 2829 } 2830 upgradeOrganizationsToVersion205(SQLiteDatabase db, SQLiteStatement rawContactUpdate, NameSplitter splitter)2831 private void upgradeOrganizationsToVersion205(SQLiteDatabase db, 2832 SQLiteStatement rawContactUpdate, NameSplitter splitter) { 2833 final long mimeType = lookupMimeTypeId(db, Organization.CONTENT_ITEM_TYPE); 2834 2835 SQLiteStatement organizationUpdate = db.compileStatement( 2836 "UPDATE " + Tables.DATA + 2837 " SET " + 2838 Organization.PHONETIC_NAME_STYLE + "=?" + 2839 " WHERE " + Data._ID + "=?"); 2840 2841 Cursor cursor = db.query(Organization205Query.TABLE, Organization205Query.COLUMNS, 2842 DataColumns.MIMETYPE_ID + "=" + mimeType + " AND " 2843 + RawContacts.DISPLAY_NAME_SOURCE + "=" + DisplayNameSources.ORGANIZATION, 2844 null, null, null, null); 2845 try { 2846 while (cursor.moveToNext()) { 2847 long dataId = cursor.getLong(Organization205Query.ID); 2848 long rawContactId = cursor.getLong(Organization205Query.RAW_CONTACT_ID); 2849 String company = cursor.getString(Organization205Query.COMPANY); 2850 String phoneticName = cursor.getString(Organization205Query.PHONETIC_NAME); 2851 2852 int phoneticNameStyle = splitter.guessPhoneticNameStyle(phoneticName); 2853 2854 organizationUpdate.bindLong(1, phoneticNameStyle); 2855 organizationUpdate.bindLong(2, dataId); 2856 organizationUpdate.execute(); 2857 2858 String sortKey = company; 2859 2860 updateRawContact205(rawContactUpdate, rawContactId, company, 2861 company, phoneticNameStyle, phoneticName, sortKey, sortKey); 2862 } 2863 } finally { 2864 cursor.close(); 2865 } 2866 } 2867 updateRawContact205(SQLiteStatement rawContactUpdate, long rawContactId, String displayName, String displayNameAlternative, int phoneticNameStyle, String phoneticName, String sortKeyPrimary, String sortKeyAlternative)2868 private void updateRawContact205(SQLiteStatement rawContactUpdate, long rawContactId, 2869 String displayName, String displayNameAlternative, int phoneticNameStyle, 2870 String phoneticName, String sortKeyPrimary, String sortKeyAlternative) { 2871 bindString(rawContactUpdate, 1, displayName); 2872 bindString(rawContactUpdate, 2, displayNameAlternative); 2873 bindString(rawContactUpdate, 3, phoneticName); 2874 rawContactUpdate.bindLong(4, phoneticNameStyle); 2875 bindString(rawContactUpdate, 5, sortKeyPrimary); 2876 bindString(rawContactUpdate, 6, sortKeyAlternative); 2877 rawContactUpdate.bindLong(7, rawContactId); 2878 rawContactUpdate.execute(); 2879 } 2880 upgrateToVersion206(SQLiteDatabase db)2881 private void upgrateToVersion206(SQLiteDatabase db) { 2882 db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS 2883 + " ADD " + RawContacts.NAME_VERIFIED + " INTEGER NOT NULL DEFAULT 0;"); 2884 } 2885 2886 /** 2887 * Fix for the bug where name lookup records for organizations would get removed by 2888 * unrelated updates of the data rows. 2889 */ upgradeToVersion300(SQLiteDatabase db)2890 private void upgradeToVersion300(SQLiteDatabase db) { 2891 // No longer needed 2892 } 2893 2894 private static final class Upgrade303Query { 2895 public static final String TABLE = Tables.DATA; 2896 2897 public static final String SELECTION = 2898 DataColumns.MIMETYPE_ID + "=?" + 2899 " AND " + Data._ID + " NOT IN " + 2900 "(SELECT " + NameLookupColumns.DATA_ID + " FROM " + Tables.NAME_LOOKUP + ")" + 2901 " AND " + Data.DATA1 + " NOT NULL"; 2902 2903 public static final String COLUMNS[] = { 2904 Data._ID, 2905 Data.RAW_CONTACT_ID, 2906 Data.DATA1, 2907 }; 2908 2909 public static final int ID = 0; 2910 public static final int RAW_CONTACT_ID = 1; 2911 public static final int DATA1 = 2; 2912 } 2913 2914 /** 2915 * The {@link ContactsProvider2#update} method was deleting name lookup for new 2916 * emails during the sync. We need to restore the lost name lookup rows. 2917 */ upgradeEmailToVersion303(SQLiteDatabase db)2918 private void upgradeEmailToVersion303(SQLiteDatabase db) { 2919 final long mimeTypeId = lookupMimeTypeId(db, Email.CONTENT_ITEM_TYPE); 2920 if (mimeTypeId == -1) { 2921 return; 2922 } 2923 2924 ContentValues values = new ContentValues(); 2925 2926 // Find all data rows with the mime type "email" that are missing name lookup 2927 Cursor cursor = db.query(Upgrade303Query.TABLE, Upgrade303Query.COLUMNS, 2928 Upgrade303Query.SELECTION, new String[] {String.valueOf(mimeTypeId)}, 2929 null, null, null); 2930 try { 2931 while (cursor.moveToNext()) { 2932 long dataId = cursor.getLong(Upgrade303Query.ID); 2933 long rawContactId = cursor.getLong(Upgrade303Query.RAW_CONTACT_ID); 2934 String value = cursor.getString(Upgrade303Query.DATA1); 2935 value = extractHandleFromEmailAddress(value); 2936 2937 if (value != null) { 2938 values.put(NameLookupColumns.DATA_ID, dataId); 2939 values.put(NameLookupColumns.RAW_CONTACT_ID, rawContactId); 2940 values.put(NameLookupColumns.NAME_TYPE, NameLookupType.EMAIL_BASED_NICKNAME); 2941 values.put(NameLookupColumns.NORMALIZED_NAME, NameNormalizer.normalize(value)); 2942 db.insert(Tables.NAME_LOOKUP, null, values); 2943 } 2944 } 2945 } finally { 2946 cursor.close(); 2947 } 2948 } 2949 2950 /** 2951 * The {@link ContactsProvider2#update} method was deleting name lookup for new 2952 * nicknames during the sync. We need to restore the lost name lookup rows. 2953 */ upgradeNicknameToVersion303(SQLiteDatabase db)2954 private void upgradeNicknameToVersion303(SQLiteDatabase db) { 2955 final long mimeTypeId = lookupMimeTypeId(db, Nickname.CONTENT_ITEM_TYPE); 2956 if (mimeTypeId == -1) { 2957 return; 2958 } 2959 2960 ContentValues values = new ContentValues(); 2961 2962 // Find all data rows with the mime type "nickname" that are missing name lookup 2963 Cursor cursor = db.query(Upgrade303Query.TABLE, Upgrade303Query.COLUMNS, 2964 Upgrade303Query.SELECTION, new String[] {String.valueOf(mimeTypeId)}, 2965 null, null, null); 2966 try { 2967 while (cursor.moveToNext()) { 2968 long dataId = cursor.getLong(Upgrade303Query.ID); 2969 long rawContactId = cursor.getLong(Upgrade303Query.RAW_CONTACT_ID); 2970 String value = cursor.getString(Upgrade303Query.DATA1); 2971 2972 values.put(NameLookupColumns.DATA_ID, dataId); 2973 values.put(NameLookupColumns.RAW_CONTACT_ID, rawContactId); 2974 values.put(NameLookupColumns.NAME_TYPE, NameLookupType.NICKNAME); 2975 values.put(NameLookupColumns.NORMALIZED_NAME, NameNormalizer.normalize(value)); 2976 db.insert(Tables.NAME_LOOKUP, null, values); 2977 } 2978 } finally { 2979 cursor.close(); 2980 } 2981 } 2982 upgradeToVersion304(SQLiteDatabase db)2983 private void upgradeToVersion304(SQLiteDatabase db) { 2984 // Mimetype table requires an index on mime type 2985 db.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS mime_type ON " + Tables.MIMETYPES + " (" + 2986 MimetypesColumns.MIMETYPE + 2987 ");"); 2988 } 2989 upgradeToVersion306(SQLiteDatabase db)2990 private void upgradeToVersion306(SQLiteDatabase db) { 2991 // Fix invalid lookup that was used for Exchange contacts (it was not escaped) 2992 // It happened when a new contact was created AND synchronized 2993 final StringBuilder lookupKeyBuilder = new StringBuilder(); 2994 final SQLiteStatement updateStatement = db.compileStatement( 2995 "UPDATE contacts " + 2996 "SET lookup=? " + 2997 "WHERE _id=?"); 2998 final Cursor contactIdCursor = db.rawQuery( 2999 "SELECT DISTINCT contact_id " + 3000 "FROM raw_contacts " + 3001 "WHERE deleted=0 AND account_type='com.android.exchange'", 3002 null); 3003 try { 3004 while (contactIdCursor.moveToNext()) { 3005 final long contactId = contactIdCursor.getLong(0); 3006 lookupKeyBuilder.setLength(0); 3007 final Cursor c = db.rawQuery( 3008 "SELECT account_type, account_name, _id, sourceid, display_name " + 3009 "FROM raw_contacts " + 3010 "WHERE contact_id=? " + 3011 "ORDER BY _id", 3012 new String[] { String.valueOf(contactId) }); 3013 try { 3014 while (c.moveToNext()) { 3015 ContactLookupKey.appendToLookupKey(lookupKeyBuilder, 3016 c.getString(0), 3017 c.getString(1), 3018 c.getLong(2), 3019 c.getString(3), 3020 c.getString(4)); 3021 } 3022 } finally { 3023 c.close(); 3024 } 3025 3026 if (lookupKeyBuilder.length() == 0) { 3027 updateStatement.bindNull(1); 3028 } else { 3029 updateStatement.bindString(1, Uri.encode(lookupKeyBuilder.toString())); 3030 } 3031 updateStatement.bindLong(2, contactId); 3032 3033 updateStatement.execute(); 3034 } 3035 } finally { 3036 updateStatement.close(); 3037 contactIdCursor.close(); 3038 } 3039 } 3040 upgradeToVersion307(SQLiteDatabase db)3041 private void upgradeToVersion307(SQLiteDatabase db) { 3042 db.execSQL("CREATE TABLE properties (" + 3043 "property_key TEXT PRIMARY_KEY, " + 3044 "property_value TEXT" + 3045 ");"); 3046 } 3047 upgradeToVersion308(SQLiteDatabase db)3048 private void upgradeToVersion308(SQLiteDatabase db) { 3049 db.execSQL("CREATE TABLE accounts (" + 3050 "account_name TEXT, " + 3051 "account_type TEXT " + 3052 ");"); 3053 3054 db.execSQL("INSERT INTO accounts " + 3055 "SELECT DISTINCT account_name, account_type FROM raw_contacts"); 3056 } 3057 upgradeToVersion400(SQLiteDatabase db)3058 private void upgradeToVersion400(SQLiteDatabase db) { 3059 db.execSQL("ALTER TABLE " + Tables.GROUPS 3060 + " ADD " + Groups.FAVORITES + " INTEGER NOT NULL DEFAULT 0;"); 3061 db.execSQL("ALTER TABLE " + Tables.GROUPS 3062 + " ADD " + Groups.AUTO_ADD + " INTEGER NOT NULL DEFAULT 0;"); 3063 } 3064 upgradeToVersion353(SQLiteDatabase db)3065 private void upgradeToVersion353(SQLiteDatabase db) { 3066 db.execSQL("DELETE FROM contacts " + 3067 "WHERE NOT EXISTS (SELECT 1 FROM raw_contacts WHERE contact_id=contacts._id)"); 3068 } 3069 rebuildNameLookup(SQLiteDatabase db, boolean rebuildSqliteStats)3070 private void rebuildNameLookup(SQLiteDatabase db, boolean rebuildSqliteStats) { 3071 db.execSQL("DROP INDEX IF EXISTS name_lookup_index"); 3072 insertNameLookup(db); 3073 createContactsIndexes(db, rebuildSqliteStats); 3074 } 3075 rebuildSearchIndex()3076 protected void rebuildSearchIndex() { 3077 rebuildSearchIndex(getWritableDatabase(), true); 3078 } 3079 rebuildSearchIndex(SQLiteDatabase db, boolean rebuildSqliteStats)3080 private void rebuildSearchIndex(SQLiteDatabase db, boolean rebuildSqliteStats) { 3081 createSearchIndexTable(db, rebuildSqliteStats); 3082 setProperty(db, SearchIndexManager.PROPERTY_SEARCH_INDEX_VERSION, "0"); 3083 } 3084 3085 /** 3086 * Checks whether the current ICU code version matches that used to build 3087 * the locale specific data in the ContactsDB. 3088 */ needsToUpdateLocaleData(Locale locale)3089 public boolean needsToUpdateLocaleData(Locale locale) { 3090 final String dbLocale = getProperty(DbProperties.LOCALE, ""); 3091 if (!dbLocale.equals(locale.toString())) { 3092 return true; 3093 } 3094 final String curICUVersion = ICU.getIcuVersion(); 3095 final String dbICUVersion = getProperty(DbProperties.ICU_VERSION, 3096 "(unknown)"); 3097 if (!curICUVersion.equals(dbICUVersion)) { 3098 Log.i(TAG, "ICU version has changed. Current version is " 3099 + curICUVersion + "; DB was built with " + dbICUVersion); 3100 return true; 3101 } 3102 return false; 3103 } 3104 upgradeLocaleData(SQLiteDatabase db, boolean rebuildSqliteStats)3105 private void upgradeLocaleData(SQLiteDatabase db, boolean rebuildSqliteStats) { 3106 final Locale locale = Locale.getDefault(); 3107 Log.i(TAG, "Upgrading locale data for " + locale 3108 + " (ICU v" + ICU.getIcuVersion() + ")"); 3109 final long start = SystemClock.elapsedRealtime(); 3110 initializeCache(db); 3111 rebuildLocaleData(db, locale, rebuildSqliteStats); 3112 Log.i(TAG, "Locale update completed in " + (SystemClock.elapsedRealtime() - start) + "ms"); 3113 } 3114 rebuildLocaleData(SQLiteDatabase db, Locale locale, boolean rebuildSqliteStats)3115 private void rebuildLocaleData(SQLiteDatabase db, Locale locale, 3116 boolean rebuildSqliteStats) { 3117 db.execSQL("DROP INDEX raw_contact_sort_key1_index"); 3118 db.execSQL("DROP INDEX raw_contact_sort_key2_index"); 3119 db.execSQL("DROP INDEX IF EXISTS name_lookup_index"); 3120 3121 loadNicknameLookupTable(db); 3122 insertNameLookup(db); 3123 rebuildSortKeys(db); 3124 createContactsIndexes(db, rebuildSqliteStats); 3125 3126 FastScrollingIndexCache.getInstance(mContext).invalidate(); 3127 // Update the ICU version used to generate the locale derived data 3128 // so we can tell when we need to rebuild with new ICU versions. 3129 setProperty(db, DbProperties.ICU_VERSION, ICU.getIcuVersion()); 3130 setProperty(db, DbProperties.LOCALE, locale.toString()); 3131 } 3132 3133 /** 3134 * Regenerates all locale-sensitive data if needed: 3135 * nickname_lookup, name_lookup and sort keys. Invalidates the fast 3136 * scrolling index cache. 3137 */ setLocale(Locale locale)3138 public void setLocale(Locale locale) { 3139 if (!needsToUpdateLocaleData(locale)) { 3140 return; 3141 } 3142 Log.i(TAG, "Switching to locale " + locale 3143 + " (ICU v" + ICU.getIcuVersion() + ")"); 3144 3145 final long start = SystemClock.elapsedRealtime(); 3146 SQLiteDatabase db = getWritableDatabase(); 3147 db.setLocale(locale); 3148 db.beginTransaction(); 3149 try { 3150 rebuildLocaleData(db, locale, true); 3151 db.setTransactionSuccessful(); 3152 } finally { 3153 db.endTransaction(); 3154 } 3155 3156 Log.i(TAG, "Locale change completed in " + (SystemClock.elapsedRealtime() - start) + "ms"); 3157 } 3158 3159 /** 3160 * Regenerates sort keys for all contacts. 3161 */ rebuildSortKeys(SQLiteDatabase db)3162 private void rebuildSortKeys(SQLiteDatabase db) { 3163 Cursor cursor = db.query(Tables.RAW_CONTACTS, new String[]{RawContacts._ID}, 3164 null, null, null, null, null); 3165 try { 3166 while (cursor.moveToNext()) { 3167 long rawContactId = cursor.getLong(0); 3168 updateRawContactDisplayName(db, rawContactId); 3169 } 3170 } finally { 3171 cursor.close(); 3172 } 3173 } 3174 insertNameLookup(SQLiteDatabase db)3175 private void insertNameLookup(SQLiteDatabase db) { 3176 db.execSQL("DELETE FROM " + Tables.NAME_LOOKUP); 3177 3178 SQLiteStatement nameLookupInsert = db.compileStatement( 3179 "INSERT OR IGNORE INTO " + Tables.NAME_LOOKUP + "(" 3180 + NameLookupColumns.RAW_CONTACT_ID + "," 3181 + NameLookupColumns.DATA_ID + "," 3182 + NameLookupColumns.NAME_TYPE + "," 3183 + NameLookupColumns.NORMALIZED_NAME + 3184 ") VALUES (?,?,?,?)"); 3185 3186 try { 3187 insertStructuredNameLookup(db, nameLookupInsert); 3188 insertEmailLookup(db, nameLookupInsert); 3189 insertNicknameLookup(db, nameLookupInsert); 3190 } finally { 3191 nameLookupInsert.close(); 3192 } 3193 } 3194 3195 private static final class StructuredNameQuery { 3196 public static final String TABLE = Tables.DATA; 3197 3198 public static final String SELECTION = 3199 DataColumns.MIMETYPE_ID + "=? AND " + Data.DATA1 + " NOT NULL"; 3200 3201 public static final String COLUMNS[] = { 3202 StructuredName._ID, 3203 StructuredName.RAW_CONTACT_ID, 3204 StructuredName.DISPLAY_NAME, 3205 }; 3206 3207 public static final int ID = 0; 3208 public static final int RAW_CONTACT_ID = 1; 3209 public static final int DISPLAY_NAME = 2; 3210 } 3211 3212 private class StructuredNameLookupBuilder extends NameLookupBuilder { 3213 3214 private final SQLiteStatement mNameLookupInsert; 3215 private final CommonNicknameCache mCommonNicknameCache; 3216 StructuredNameLookupBuilder(NameSplitter splitter, CommonNicknameCache commonNicknameCache, SQLiteStatement nameLookupInsert)3217 public StructuredNameLookupBuilder(NameSplitter splitter, 3218 CommonNicknameCache commonNicknameCache, SQLiteStatement nameLookupInsert) { 3219 super(splitter); 3220 this.mCommonNicknameCache = commonNicknameCache; 3221 this.mNameLookupInsert = nameLookupInsert; 3222 } 3223 3224 @Override insertNameLookup(long rawContactId, long dataId, int lookupType, String name)3225 protected void insertNameLookup(long rawContactId, long dataId, int lookupType, 3226 String name) { 3227 if (!TextUtils.isEmpty(name)) { 3228 ContactsDatabaseHelper.this.insertNormalizedNameLookup(mNameLookupInsert, 3229 rawContactId, dataId, lookupType, name); 3230 } 3231 } 3232 3233 @Override getCommonNicknameClusters(String normalizedName)3234 protected String[] getCommonNicknameClusters(String normalizedName) { 3235 return mCommonNicknameCache.getCommonNicknameClusters(normalizedName); 3236 } 3237 } 3238 3239 /** 3240 * Inserts name lookup rows for all structured names in the database. 3241 */ insertStructuredNameLookup(SQLiteDatabase db, SQLiteStatement nameLookupInsert)3242 private void insertStructuredNameLookup(SQLiteDatabase db, SQLiteStatement nameLookupInsert) { 3243 NameSplitter nameSplitter = createNameSplitter(); 3244 NameLookupBuilder nameLookupBuilder = new StructuredNameLookupBuilder(nameSplitter, 3245 new CommonNicknameCache(db), nameLookupInsert); 3246 final long mimeTypeId = lookupMimeTypeId(db, StructuredName.CONTENT_ITEM_TYPE); 3247 Cursor cursor = db.query(StructuredNameQuery.TABLE, StructuredNameQuery.COLUMNS, 3248 StructuredNameQuery.SELECTION, new String[] {String.valueOf(mimeTypeId)}, 3249 null, null, null); 3250 try { 3251 while (cursor.moveToNext()) { 3252 long dataId = cursor.getLong(StructuredNameQuery.ID); 3253 long rawContactId = cursor.getLong(StructuredNameQuery.RAW_CONTACT_ID); 3254 String name = cursor.getString(StructuredNameQuery.DISPLAY_NAME); 3255 int fullNameStyle = nameSplitter.guessFullNameStyle(name); 3256 fullNameStyle = nameSplitter.getAdjustedFullNameStyle(fullNameStyle); 3257 nameLookupBuilder.insertNameLookup(rawContactId, dataId, name, fullNameStyle); 3258 } 3259 } finally { 3260 cursor.close(); 3261 } 3262 } 3263 3264 private static final class OrganizationQuery { 3265 public static final String TABLE = Tables.DATA; 3266 3267 public static final String SELECTION = 3268 DataColumns.MIMETYPE_ID + "=? AND " + Data.DATA1 + " NOT NULL"; 3269 3270 public static final String COLUMNS[] = { 3271 Organization._ID, 3272 Organization.RAW_CONTACT_ID, 3273 Organization.COMPANY, 3274 Organization.TITLE, 3275 }; 3276 3277 public static final int ID = 0; 3278 public static final int RAW_CONTACT_ID = 1; 3279 public static final int COMPANY = 2; 3280 public static final int TITLE = 3; 3281 } 3282 3283 private static final class EmailQuery { 3284 public static final String TABLE = Tables.DATA; 3285 3286 public static final String SELECTION = 3287 DataColumns.MIMETYPE_ID + "=? AND " + Data.DATA1 + " NOT NULL"; 3288 3289 public static final String COLUMNS[] = { 3290 Email._ID, 3291 Email.RAW_CONTACT_ID, 3292 Email.ADDRESS, 3293 }; 3294 3295 public static final int ID = 0; 3296 public static final int RAW_CONTACT_ID = 1; 3297 public static final int ADDRESS = 2; 3298 } 3299 3300 /** 3301 * Inserts name lookup rows for all email addresses in the database. 3302 */ insertEmailLookup(SQLiteDatabase db, SQLiteStatement nameLookupInsert)3303 private void insertEmailLookup(SQLiteDatabase db, SQLiteStatement nameLookupInsert) { 3304 final long mimeTypeId = lookupMimeTypeId(db, Email.CONTENT_ITEM_TYPE); 3305 Cursor cursor = db.query(EmailQuery.TABLE, EmailQuery.COLUMNS, 3306 EmailQuery.SELECTION, new String[] {String.valueOf(mimeTypeId)}, 3307 null, null, null); 3308 try { 3309 while (cursor.moveToNext()) { 3310 long dataId = cursor.getLong(EmailQuery.ID); 3311 long rawContactId = cursor.getLong(EmailQuery.RAW_CONTACT_ID); 3312 String address = cursor.getString(EmailQuery.ADDRESS); 3313 address = extractHandleFromEmailAddress(address); 3314 insertNameLookup(nameLookupInsert, rawContactId, dataId, 3315 NameLookupType.EMAIL_BASED_NICKNAME, address); 3316 } 3317 } finally { 3318 cursor.close(); 3319 } 3320 } 3321 3322 private static final class NicknameQuery { 3323 public static final String TABLE = Tables.DATA; 3324 3325 public static final String SELECTION = 3326 DataColumns.MIMETYPE_ID + "=? AND " + Data.DATA1 + " NOT NULL"; 3327 3328 public static final String COLUMNS[] = { 3329 Nickname._ID, 3330 Nickname.RAW_CONTACT_ID, 3331 Nickname.NAME, 3332 }; 3333 3334 public static final int ID = 0; 3335 public static final int RAW_CONTACT_ID = 1; 3336 public static final int NAME = 2; 3337 } 3338 3339 /** 3340 * Inserts name lookup rows for all nicknames in the database. 3341 */ insertNicknameLookup(SQLiteDatabase db, SQLiteStatement nameLookupInsert)3342 private void insertNicknameLookup(SQLiteDatabase db, SQLiteStatement nameLookupInsert) { 3343 final long mimeTypeId = lookupMimeTypeId(db, Nickname.CONTENT_ITEM_TYPE); 3344 Cursor cursor = db.query(NicknameQuery.TABLE, NicknameQuery.COLUMNS, 3345 NicknameQuery.SELECTION, new String[] {String.valueOf(mimeTypeId)}, 3346 null, null, null); 3347 try { 3348 while (cursor.moveToNext()) { 3349 long dataId = cursor.getLong(NicknameQuery.ID); 3350 long rawContactId = cursor.getLong(NicknameQuery.RAW_CONTACT_ID); 3351 String nickname = cursor.getString(NicknameQuery.NAME); 3352 insertNameLookup(nameLookupInsert, rawContactId, dataId, 3353 NameLookupType.NICKNAME, nickname); 3354 } 3355 } finally { 3356 cursor.close(); 3357 } 3358 } 3359 3360 /** 3361 * Inserts a record in the {@link Tables#NAME_LOOKUP} table. 3362 */ insertNameLookup(SQLiteStatement stmt, long rawContactId, long dataId, int lookupType, String name)3363 public void insertNameLookup(SQLiteStatement stmt, long rawContactId, long dataId, 3364 int lookupType, String name) { 3365 if (TextUtils.isEmpty(name)) { 3366 return; 3367 } 3368 3369 String normalized = NameNormalizer.normalize(name); 3370 if (TextUtils.isEmpty(normalized)) { 3371 return; 3372 } 3373 3374 insertNormalizedNameLookup(stmt, rawContactId, dataId, lookupType, normalized); 3375 } 3376 insertNormalizedNameLookup(SQLiteStatement stmt, long rawContactId, long dataId, int lookupType, String normalizedName)3377 private void insertNormalizedNameLookup(SQLiteStatement stmt, long rawContactId, long dataId, 3378 int lookupType, String normalizedName) { 3379 stmt.bindLong(1, rawContactId); 3380 stmt.bindLong(2, dataId); 3381 stmt.bindLong(3, lookupType); 3382 stmt.bindString(4, normalizedName); 3383 stmt.executeInsert(); 3384 } 3385 3386 /** 3387 * Changing the VISIBLE bit from a field on both RawContacts and Contacts to a separate table. 3388 */ upgradeToVersion401(SQLiteDatabase db)3389 private void upgradeToVersion401(SQLiteDatabase db) { 3390 db.execSQL("CREATE TABLE " + Tables.VISIBLE_CONTACTS + " (" + 3391 Contacts._ID + " INTEGER PRIMARY KEY" + 3392 ");"); 3393 db.execSQL("INSERT INTO " + Tables.VISIBLE_CONTACTS + 3394 " SELECT " + Contacts._ID + 3395 " FROM " + Tables.CONTACTS + 3396 " WHERE " + Contacts.IN_VISIBLE_GROUP + "!=0"); 3397 db.execSQL("DROP INDEX contacts_visible_index"); 3398 } 3399 3400 /** 3401 * Introducing a new table: directories. 3402 */ upgradeToVersion402(SQLiteDatabase db)3403 private void upgradeToVersion402(SQLiteDatabase db) { 3404 createDirectoriesTable(db); 3405 } 3406 upgradeToVersion403(SQLiteDatabase db)3407 private void upgradeToVersion403(SQLiteDatabase db) { 3408 db.execSQL("DROP TABLE IF EXISTS directories;"); 3409 createDirectoriesTable(db); 3410 3411 db.execSQL("ALTER TABLE raw_contacts" 3412 + " ADD raw_contact_is_read_only INTEGER NOT NULL DEFAULT 0;"); 3413 3414 db.execSQL("ALTER TABLE data" 3415 + " ADD is_read_only INTEGER NOT NULL DEFAULT 0;"); 3416 } 3417 upgradeToVersion405(SQLiteDatabase db)3418 private void upgradeToVersion405(SQLiteDatabase db) { 3419 db.execSQL("DROP TABLE IF EXISTS phone_lookup;"); 3420 // Private phone numbers table used for lookup 3421 db.execSQL("CREATE TABLE " + Tables.PHONE_LOOKUP + " (" + 3422 PhoneLookupColumns.DATA_ID 3423 + " INTEGER REFERENCES data(_id) NOT NULL," + 3424 PhoneLookupColumns.RAW_CONTACT_ID 3425 + " INTEGER REFERENCES raw_contacts(_id) NOT NULL," + 3426 PhoneLookupColumns.NORMALIZED_NUMBER + " TEXT NOT NULL," + 3427 PhoneLookupColumns.MIN_MATCH + " TEXT NOT NULL" + 3428 ");"); 3429 3430 db.execSQL("CREATE INDEX phone_lookup_index ON " + Tables.PHONE_LOOKUP + " (" + 3431 PhoneLookupColumns.NORMALIZED_NUMBER + "," + 3432 PhoneLookupColumns.RAW_CONTACT_ID + "," + 3433 PhoneLookupColumns.DATA_ID + 3434 ");"); 3435 3436 db.execSQL("CREATE INDEX phone_lookup_min_match_index ON " + Tables.PHONE_LOOKUP + " (" + 3437 PhoneLookupColumns.MIN_MATCH + "," + 3438 PhoneLookupColumns.RAW_CONTACT_ID + "," + 3439 PhoneLookupColumns.DATA_ID + 3440 ");"); 3441 3442 final long mimeTypeId = lookupMimeTypeId(db, Phone.CONTENT_ITEM_TYPE); 3443 if (mimeTypeId == -1) { 3444 return; 3445 } 3446 3447 Cursor cursor = db.rawQuery( 3448 "SELECT _id, " + Phone.RAW_CONTACT_ID + ", " + Phone.NUMBER + 3449 " FROM " + Tables.DATA + 3450 " WHERE " + DataColumns.MIMETYPE_ID + "=" + mimeTypeId 3451 + " AND " + Phone.NUMBER + " NOT NULL", null); 3452 3453 ContentValues phoneValues = new ContentValues(); 3454 try { 3455 while (cursor.moveToNext()) { 3456 long dataID = cursor.getLong(0); 3457 long rawContactID = cursor.getLong(1); 3458 String number = cursor.getString(2); 3459 String normalizedNumber = PhoneNumberUtils.normalizeNumber(number); 3460 if (!TextUtils.isEmpty(normalizedNumber)) { 3461 phoneValues.clear(); 3462 phoneValues.put(PhoneLookupColumns.RAW_CONTACT_ID, rawContactID); 3463 phoneValues.put(PhoneLookupColumns.DATA_ID, dataID); 3464 phoneValues.put(PhoneLookupColumns.NORMALIZED_NUMBER, normalizedNumber); 3465 phoneValues.put(PhoneLookupColumns.MIN_MATCH, 3466 PhoneNumberUtils.toCallerIDMinMatch(normalizedNumber)); 3467 db.insert(Tables.PHONE_LOOKUP, null, phoneValues); 3468 } 3469 } 3470 } finally { 3471 cursor.close(); 3472 } 3473 } 3474 upgradeToVersion406(SQLiteDatabase db)3475 private void upgradeToVersion406(SQLiteDatabase db) { 3476 db.execSQL("ALTER TABLE calls ADD countryiso TEXT;"); 3477 } 3478 upgradeToVersion409(SQLiteDatabase db)3479 private void upgradeToVersion409(SQLiteDatabase db) { 3480 db.execSQL("DROP TABLE IF EXISTS directories;"); 3481 createDirectoriesTable(db); 3482 } 3483 3484 /** 3485 * Adding DEFAULT_DIRECTORY table. 3486 * DEFAULT_DIRECTORY should contain every contact which should be shown to users in default. 3487 * - if a contact doesn't belong to any account (local contact), it should be in 3488 * default_directory 3489 * - if a contact belongs to an account that doesn't have a "default" group, it should be in 3490 * default_directory 3491 * - if a contact belongs to an account that has a "default" group (like Google directory, 3492 * which has "My contacts" group as default), it should be in default_directory. 3493 * 3494 * This logic assumes that accounts with the "default" group should have at least one 3495 * group with AUTO_ADD (implying it is the default group) flag in the groups table. 3496 */ upgradeToVersion411(SQLiteDatabase db)3497 private void upgradeToVersion411(SQLiteDatabase db) { 3498 db.execSQL("DROP TABLE IF EXISTS " + Tables.DEFAULT_DIRECTORY); 3499 db.execSQL("CREATE TABLE default_directory (_id INTEGER PRIMARY KEY);"); 3500 3501 // Process contacts without an account 3502 db.execSQL("INSERT OR IGNORE INTO default_directory " + 3503 " SELECT contact_id " + 3504 " FROM raw_contacts " + 3505 " WHERE raw_contacts.account_name IS NULL " + 3506 " AND raw_contacts.account_type IS NULL "); 3507 3508 // Process accounts that don't have a default group (e.g. Exchange). 3509 db.execSQL("INSERT OR IGNORE INTO default_directory " + 3510 " SELECT contact_id " + 3511 " FROM raw_contacts " + 3512 " WHERE NOT EXISTS" + 3513 " (SELECT _id " + 3514 " FROM groups " + 3515 " WHERE raw_contacts.account_name = groups.account_name" + 3516 " AND raw_contacts.account_type = groups.account_type" + 3517 " AND groups.auto_add != 0)"); 3518 3519 final long mimetype = lookupMimeTypeId(db, GroupMembership.CONTENT_ITEM_TYPE); 3520 3521 // Process accounts that do have a default group (e.g. Google) 3522 db.execSQL("INSERT OR IGNORE INTO default_directory " + 3523 " SELECT contact_id " + 3524 " FROM raw_contacts " + 3525 " JOIN data " + 3526 " ON (raw_contacts._id=raw_contact_id)" + 3527 " WHERE mimetype_id=" + mimetype + 3528 " AND EXISTS" + 3529 " (SELECT _id" + 3530 " FROM groups" + 3531 " WHERE raw_contacts.account_name = groups.account_name" + 3532 " AND raw_contacts.account_type = groups.account_type" + 3533 " AND groups.auto_add != 0)"); 3534 } 3535 upgradeToVersion413(SQLiteDatabase db)3536 private void upgradeToVersion413(SQLiteDatabase db) { 3537 db.execSQL("DROP TABLE IF EXISTS directories;"); 3538 createDirectoriesTable(db); 3539 } 3540 upgradeToVersion415(SQLiteDatabase db)3541 private void upgradeToVersion415(SQLiteDatabase db) { 3542 db.execSQL( 3543 "ALTER TABLE " + Tables.GROUPS + 3544 " ADD " + Groups.GROUP_IS_READ_ONLY + " INTEGER NOT NULL DEFAULT 0"); 3545 db.execSQL( 3546 "UPDATE " + Tables.GROUPS + 3547 " SET " + Groups.GROUP_IS_READ_ONLY + "=1" + 3548 " WHERE " + Groups.SYSTEM_ID + " NOT NULL"); 3549 } 3550 upgradeToVersion416(SQLiteDatabase db)3551 private void upgradeToVersion416(SQLiteDatabase db) { 3552 db.execSQL("CREATE INDEX phone_lookup_data_id_min_match_index ON " + Tables.PHONE_LOOKUP + 3553 " (" + PhoneLookupColumns.DATA_ID + ", " + PhoneLookupColumns.MIN_MATCH + ");"); 3554 } 3555 upgradeToVersion501(SQLiteDatabase db)3556 private void upgradeToVersion501(SQLiteDatabase db) { 3557 // Remove organization rows from the name lookup, we now use search index for that 3558 db.execSQL("DELETE FROM name_lookup WHERE name_type=5"); 3559 } 3560 upgradeToVersion502(SQLiteDatabase db)3561 private void upgradeToVersion502(SQLiteDatabase db) { 3562 // Remove Chinese and Korean name lookup - this data is now in the search index 3563 db.execSQL("DELETE FROM name_lookup WHERE name_type IN (6, 7)"); 3564 } 3565 upgradeToVersion504(SQLiteDatabase db)3566 private void upgradeToVersion504(SQLiteDatabase db) { 3567 initializeCache(db); 3568 3569 // Find all names with prefixes and recreate display name 3570 Cursor cursor = db.rawQuery( 3571 "SELECT " + StructuredName.RAW_CONTACT_ID + 3572 " FROM " + Tables.DATA + 3573 " WHERE " + DataColumns.MIMETYPE_ID + "=?" 3574 + " AND " + StructuredName.PREFIX + " NOT NULL", 3575 new String[]{ String.valueOf(mMimeTypeIdStructuredName) }); 3576 3577 try { 3578 while(cursor.moveToNext()) { 3579 long rawContactId = cursor.getLong(0); 3580 updateRawContactDisplayName(db, rawContactId); 3581 } 3582 3583 } finally { 3584 cursor.close(); 3585 } 3586 } 3587 upgradeToVersion600(SQLiteDatabase db)3588 private void upgradeToVersion600(SQLiteDatabase db) { 3589 // This change used to add the profile raw contact ID to the Accounts table. That 3590 // column is no longer needed (as of version 614) since the profile records are stored in 3591 // a separate copy of the database for security reasons. So this change is now a no-op. 3592 } 3593 upgradeToVersion601(SQLiteDatabase db)3594 private void upgradeToVersion601(SQLiteDatabase db) { 3595 db.execSQL("CREATE TABLE data_usage_stat(" + 3596 "stat_id INTEGER PRIMARY KEY AUTOINCREMENT, " + 3597 "data_id INTEGER NOT NULL, " + 3598 "usage_type INTEGER NOT NULL DEFAULT 0, " + 3599 "times_used INTEGER NOT NULL DEFAULT 0, " + 3600 "last_time_used INTERGER NOT NULL DEFAULT 0, " + 3601 "FOREIGN KEY(data_id) REFERENCES data(_id));"); 3602 db.execSQL("CREATE UNIQUE INDEX data_usage_stat_index ON " + 3603 "data_usage_stat (data_id, usage_type)"); 3604 } 3605 upgradeToVersion602(SQLiteDatabase db)3606 private void upgradeToVersion602(SQLiteDatabase db) { 3607 db.execSQL("ALTER TABLE calls ADD voicemail_uri TEXT;"); 3608 db.execSQL("ALTER TABLE calls ADD _data TEXT;"); 3609 db.execSQL("ALTER TABLE calls ADD has_content INTEGER;"); 3610 db.execSQL("ALTER TABLE calls ADD mime_type TEXT;"); 3611 db.execSQL("ALTER TABLE calls ADD source_data TEXT;"); 3612 db.execSQL("ALTER TABLE calls ADD source_package TEXT;"); 3613 db.execSQL("ALTER TABLE calls ADD state INTEGER;"); 3614 } 3615 upgradeToVersion604(SQLiteDatabase db)3616 private void upgradeToVersion604(SQLiteDatabase db) { 3617 db.execSQL("CREATE TABLE voicemail_status (" + 3618 "_id INTEGER PRIMARY KEY AUTOINCREMENT," + 3619 "source_package TEXT UNIQUE NOT NULL," + 3620 "settings_uri TEXT," + 3621 "voicemail_access_uri TEXT," + 3622 "configuration_state INTEGER," + 3623 "data_channel_state INTEGER," + 3624 "notification_channel_state INTEGER" + 3625 ");"); 3626 } 3627 upgradeToVersion605(SQLiteDatabase db)3628 private void upgradeToVersion605(SQLiteDatabase db) { 3629 // This version used to create the stream item and stream item photos tables, but a newer 3630 // version of those tables is created in version 609 below. So omitting the creation in 3631 // this upgrade step to avoid a create->drop->create. 3632 } 3633 upgradeToVersion606(SQLiteDatabase db)3634 private void upgradeToVersion606(SQLiteDatabase db) { 3635 db.execSQL("DROP VIEW IF EXISTS view_contacts_restricted;"); 3636 db.execSQL("DROP VIEW IF EXISTS view_data_restricted;"); 3637 db.execSQL("DROP VIEW IF EXISTS view_raw_contacts_restricted;"); 3638 db.execSQL("DROP VIEW IF EXISTS view_raw_entities_restricted;"); 3639 db.execSQL("DROP VIEW IF EXISTS view_entities_restricted;"); 3640 db.execSQL("DROP VIEW IF EXISTS view_data_usage_stat_restricted;"); 3641 db.execSQL("DROP INDEX IF EXISTS contacts_restricted_index"); 3642 3643 // We should remove the restricted columns here as well, but unfortunately SQLite doesn't 3644 // provide ALTER TABLE DROP COLUMN. As they have DEFAULT 0, we can keep but ignore them 3645 } 3646 upgradeToVersion607(SQLiteDatabase db)3647 private void upgradeToVersion607(SQLiteDatabase db) { 3648 // We added "action" and "action_uri" to groups here, but realized this was not a smart 3649 // move. This upgrade step has been removed (all dogfood phones that executed this step 3650 // will have those columns, but that shouldn't hurt. Unfortunately, SQLite makes it hard 3651 // to remove columns) 3652 } 3653 upgradeToVersion608(SQLiteDatabase db)3654 private void upgradeToVersion608(SQLiteDatabase db) { 3655 db.execSQL("ALTER TABLE contacts ADD photo_file_id INTEGER REFERENCES photo_files(_id);"); 3656 3657 db.execSQL("CREATE TABLE photo_files(" + 3658 "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + 3659 "height INTEGER NOT NULL, " + 3660 "width INTEGER NOT NULL, " + 3661 "filesize INTEGER NOT NULL);"); 3662 } 3663 upgradeToVersion609(SQLiteDatabase db)3664 private void upgradeToVersion609(SQLiteDatabase db) { 3665 // This version used to create the stream item and stream item photos tables, but a newer 3666 // version of those tables is created in version 613 below. So omitting the creation in 3667 // this upgrade step to avoid a create->drop->create. 3668 } 3669 upgradeToVersion610(SQLiteDatabase db)3670 private void upgradeToVersion610(SQLiteDatabase db) { 3671 db.execSQL("ALTER TABLE calls ADD is_read INTEGER;"); 3672 } 3673 upgradeToVersion611(SQLiteDatabase db)3674 private void upgradeToVersion611(SQLiteDatabase db) { 3675 db.execSQL("ALTER TABLE raw_contacts ADD data_set TEXT DEFAULT NULL;"); 3676 db.execSQL("ALTER TABLE groups ADD data_set TEXT DEFAULT NULL;"); 3677 db.execSQL("ALTER TABLE accounts ADD data_set TEXT DEFAULT NULL;"); 3678 3679 db.execSQL("CREATE INDEX raw_contacts_source_id_data_set_index ON raw_contacts " + 3680 "(sourceid, account_type, account_name, data_set);"); 3681 3682 db.execSQL("CREATE INDEX groups_source_id_data_set_index ON groups " + 3683 "(sourceid, account_type, account_name, data_set);"); 3684 } 3685 upgradeToVersion612(SQLiteDatabase db)3686 private void upgradeToVersion612(SQLiteDatabase db) { 3687 db.execSQL("ALTER TABLE calls ADD geocoded_location TEXT DEFAULT NULL;"); 3688 // Old calls will not have a geocoded location; new calls will get it when inserted. 3689 } 3690 upgradeToVersion613(SQLiteDatabase db)3691 private void upgradeToVersion613(SQLiteDatabase db) { 3692 // The stream item and stream item photos APIs were not in-use by anyone in the time 3693 // between their initial creation (in v609) and this update. So we're just dropping 3694 // and re-creating them to get appropriate columns. The delta is as follows: 3695 // - In stream_items, package_id was replaced by res_package. 3696 // - In stream_item_photos, picture was replaced by photo_file_id. 3697 // - Instead of resource ids for icon and label, we use resource name strings now 3698 // - Added sync columns 3699 // - Removed action and action_uri 3700 // - Text and comments are now nullable 3701 3702 db.execSQL("DROP TABLE IF EXISTS stream_items"); 3703 db.execSQL("DROP TABLE IF EXISTS stream_item_photos"); 3704 3705 db.execSQL("CREATE TABLE stream_items(" + 3706 "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + 3707 "raw_contact_id INTEGER NOT NULL, " + 3708 "res_package TEXT, " + 3709 "icon TEXT, " + 3710 "label TEXT, " + 3711 "text TEXT, " + 3712 "timestamp INTEGER NOT NULL, " + 3713 "comments TEXT, " + 3714 "stream_item_sync1 TEXT, " + 3715 "stream_item_sync2 TEXT, " + 3716 "stream_item_sync3 TEXT, " + 3717 "stream_item_sync4 TEXT, " + 3718 "FOREIGN KEY(raw_contact_id) REFERENCES raw_contacts(_id));"); 3719 3720 db.execSQL("CREATE TABLE stream_item_photos(" + 3721 "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + 3722 "stream_item_id INTEGER NOT NULL, " + 3723 "sort_index INTEGER, " + 3724 "photo_file_id INTEGER NOT NULL, " + 3725 "stream_item_photo_sync1 TEXT, " + 3726 "stream_item_photo_sync2 TEXT, " + 3727 "stream_item_photo_sync3 TEXT, " + 3728 "stream_item_photo_sync4 TEXT, " + 3729 "FOREIGN KEY(stream_item_id) REFERENCES stream_items(_id));"); 3730 } 3731 upgradeToVersion615(SQLiteDatabase db)3732 private void upgradeToVersion615(SQLiteDatabase db) { 3733 // Old calls will not have up to date values for these columns, they will be filled in 3734 // as needed. 3735 db.execSQL("ALTER TABLE calls ADD lookup_uri TEXT DEFAULT NULL;"); 3736 db.execSQL("ALTER TABLE calls ADD matched_number TEXT DEFAULT NULL;"); 3737 db.execSQL("ALTER TABLE calls ADD normalized_number TEXT DEFAULT NULL;"); 3738 db.execSQL("ALTER TABLE calls ADD photo_id INTEGER NOT NULL DEFAULT 0;"); 3739 } 3740 upgradeToVersion618(SQLiteDatabase db)3741 private void upgradeToVersion618(SQLiteDatabase db) { 3742 // The Settings table needs a data_set column which technically should be part of the 3743 // primary key but can't be because it may be null. Since SQLite doesn't support nuking 3744 // the primary key, we'll drop the old table, re-create it, and copy the settings back in. 3745 db.execSQL("CREATE TEMPORARY TABLE settings_backup(" + 3746 "account_name STRING NOT NULL," + 3747 "account_type STRING NOT NULL," + 3748 "ungrouped_visible INTEGER NOT NULL DEFAULT 0," + 3749 "should_sync INTEGER NOT NULL DEFAULT 1" + 3750 ");"); 3751 db.execSQL("INSERT INTO settings_backup " + 3752 "SELECT account_name, account_type, ungrouped_visible, should_sync" + 3753 " FROM settings"); 3754 db.execSQL("DROP TABLE settings"); 3755 db.execSQL("CREATE TABLE settings (" + 3756 "account_name STRING NOT NULL," + 3757 "account_type STRING NOT NULL," + 3758 "data_set STRING," + 3759 "ungrouped_visible INTEGER NOT NULL DEFAULT 0," + 3760 "should_sync INTEGER NOT NULL DEFAULT 1" + 3761 ");"); 3762 db.execSQL("INSERT INTO settings " + 3763 "SELECT account_name, account_type, NULL, ungrouped_visible, should_sync " + 3764 "FROM settings_backup"); 3765 db.execSQL("DROP TABLE settings_backup"); 3766 } 3767 upgradeToVersion622(SQLiteDatabase db)3768 private void upgradeToVersion622(SQLiteDatabase db) { 3769 db.execSQL("ALTER TABLE calls ADD formatted_number TEXT DEFAULT NULL;"); 3770 } 3771 upgradeToVersion626(SQLiteDatabase db)3772 private void upgradeToVersion626(SQLiteDatabase db) { 3773 db.execSQL("DROP TABLE IF EXISTS accounts"); 3774 3775 db.execSQL("CREATE TABLE accounts (" + 3776 "_id INTEGER PRIMARY KEY AUTOINCREMENT," + 3777 "account_name TEXT, " + 3778 "account_type TEXT, " + 3779 "data_set TEXT" + 3780 ");"); 3781 3782 // Add "account_id" column to groups and raw_contacts 3783 db.execSQL("ALTER TABLE raw_contacts ADD " + 3784 "account_id INTEGER REFERENCES accounts(_id)"); 3785 db.execSQL("ALTER TABLE groups ADD " + 3786 "account_id INTEGER REFERENCES accounts(_id)"); 3787 3788 // Update indexes. 3789 db.execSQL("DROP INDEX IF EXISTS raw_contacts_source_id_index"); 3790 db.execSQL("DROP INDEX IF EXISTS raw_contacts_source_id_data_set_index"); 3791 db.execSQL("DROP INDEX IF EXISTS groups_source_id_index"); 3792 db.execSQL("DROP INDEX IF EXISTS groups_source_id_data_set_index"); 3793 3794 db.execSQL("CREATE INDEX raw_contacts_source_id_account_id_index ON raw_contacts (" 3795 + "sourceid, account_id);"); 3796 db.execSQL("CREATE INDEX groups_source_id_account_id_index ON groups (" 3797 + "sourceid, account_id);"); 3798 3799 // Migrate account_name/account_type/data_set to accounts table 3800 3801 final Set<AccountWithDataSet> accountsWithDataSets = Sets.newHashSet(); 3802 upgradeToVersion626_findAccountsWithDataSets(accountsWithDataSets, db, "raw_contacts"); 3803 upgradeToVersion626_findAccountsWithDataSets(accountsWithDataSets, db, "groups"); 3804 3805 for (AccountWithDataSet accountWithDataSet : accountsWithDataSets) { 3806 db.execSQL("INSERT INTO accounts (account_name,account_type,data_set)VALUES(?, ?, ?)", 3807 new String[] { 3808 accountWithDataSet.getAccountName(), 3809 accountWithDataSet.getAccountType(), 3810 accountWithDataSet.getDataSet() 3811 }); 3812 } 3813 upgradeToVersion626_fillAccountId(db, "raw_contacts"); 3814 upgradeToVersion626_fillAccountId(db, "groups"); 3815 } 3816 upgradeToVersion626_findAccountsWithDataSets( Set<AccountWithDataSet> result, SQLiteDatabase db, String table)3817 private static void upgradeToVersion626_findAccountsWithDataSets( 3818 Set<AccountWithDataSet> result, SQLiteDatabase db, String table) { 3819 Cursor c = db.rawQuery( 3820 "SELECT DISTINCT account_name, account_type, data_set FROM " + table, null); 3821 try { 3822 while (c.moveToNext()) { 3823 result.add(AccountWithDataSet.get(c.getString(0), c.getString(1), c.getString(2))); 3824 } 3825 } finally { 3826 c.close(); 3827 } 3828 } 3829 upgradeToVersion626_fillAccountId(SQLiteDatabase db, String table)3830 private static void upgradeToVersion626_fillAccountId(SQLiteDatabase db, String table) { 3831 StringBuilder sb = new StringBuilder(); 3832 3833 // Set account_id and null out account_name, account_type and data_set 3834 3835 sb.append("UPDATE " + table + " SET account_id = (SELECT _id FROM accounts WHERE "); 3836 3837 addJoinExpressionAllowingNull(sb, table + ".account_name", "accounts.account_name"); 3838 sb.append("AND"); 3839 addJoinExpressionAllowingNull(sb, table + ".account_type", "accounts.account_type"); 3840 sb.append("AND"); 3841 addJoinExpressionAllowingNull(sb, table + ".data_set", "accounts.data_set"); 3842 3843 sb.append("), account_name = null, account_type = null, data_set = null"); 3844 db.execSQL(sb.toString()); 3845 } 3846 upgradeToVersion701(SQLiteDatabase db)3847 private void upgradeToVersion701(SQLiteDatabase db) { 3848 db.execSQL("UPDATE raw_contacts SET last_time_contacted =" + 3849 " max(ifnull(last_time_contacted, 0), " + 3850 " ifnull((SELECT max(last_time_used) " + 3851 " FROM data JOIN data_usage_stat ON (data._id = data_usage_stat.data_id)" + 3852 " WHERE data.raw_contact_id = raw_contacts._id), 0))"); 3853 // Replace 0 with null. This isn't really necessary, but we do this anyway for consistency. 3854 db.execSQL("UPDATE raw_contacts SET last_time_contacted = null" + 3855 " where last_time_contacted = 0"); 3856 } 3857 3858 /** 3859 * Pre-HC devices don't have correct "NORMALIZED_NUMBERS". Clear them up. 3860 */ upgradeToVersion702(SQLiteDatabase db)3861 private void upgradeToVersion702(SQLiteDatabase db) { 3862 // All the "correct" Phone.NORMALIZED_NUMBERS should begin with "+". The upgraded data 3863 // don't. Find all Phone.NORMALIZED_NUMBERS that don't begin with "+". 3864 final int count; 3865 final long[] dataIds; 3866 final long[] rawContactIds; 3867 final String[] phoneNumbers; 3868 final StringBuilder sbDataIds; 3869 final Cursor c = db.rawQuery( 3870 "SELECT _id, raw_contact_id, data1 FROM data " + 3871 " WHERE mimetype_id=" + 3872 "(SELECT _id FROM mimetypes" + 3873 " WHERE mimetype='vnd.android.cursor.item/phone_v2')" + 3874 " AND data4 not like '+%'", // "Not like" will exclude nulls too. 3875 null); 3876 try { 3877 count = c.getCount(); 3878 if (count == 0) { 3879 return; 3880 } 3881 dataIds = new long[count]; 3882 rawContactIds = new long[count]; 3883 phoneNumbers = new String[count]; 3884 sbDataIds = new StringBuilder(); 3885 3886 c.moveToPosition(-1); 3887 while (c.moveToNext()) { 3888 final int i = c.getPosition(); 3889 dataIds[i] = c.getLong(0); 3890 rawContactIds[i] = c.getLong(1); 3891 phoneNumbers[i] = c.getString(2); 3892 3893 if (sbDataIds.length() > 0) { 3894 sbDataIds.append(","); 3895 } 3896 sbDataIds.append(dataIds[i]); 3897 } 3898 } finally { 3899 c.close(); 3900 } 3901 3902 final String dataIdList = sbDataIds.toString(); 3903 3904 // Then, update the Data and PhoneLookup tables. 3905 3906 // First, just null out all Phone.NORMALIZED_NUMBERS for those. 3907 db.execSQL("UPDATE data SET data4 = null" + 3908 " WHERE _id IN (" + dataIdList + ")"); 3909 3910 // Then, re-create phone_lookup for them. 3911 db.execSQL("DELETE FROM phone_lookup" + 3912 " WHERE data_id IN (" + dataIdList + ")"); 3913 3914 for (int i = 0; i < count; i++) { 3915 // Mimic how DataRowHandlerForPhoneNumber.insert() works when it can't normalize 3916 // numbers. 3917 final String phoneNumber = phoneNumbers[i]; 3918 if (TextUtils.isEmpty(phoneNumber)) continue; 3919 3920 final String normalized = PhoneNumberUtils.normalizeNumber(phoneNumber); 3921 if (TextUtils.isEmpty(normalized)) continue; 3922 3923 db.execSQL("INSERT INTO phone_lookup" + 3924 "(data_id, raw_contact_id, normalized_number, min_match)" + 3925 " VALUES(?,?,?,?)", 3926 new String[] { 3927 String.valueOf(dataIds[i]), 3928 String.valueOf(rawContactIds[i]), 3929 normalized, 3930 PhoneNumberUtils.toCallerIDMinMatch(normalized) 3931 }); 3932 } 3933 } 3934 upgradeToVersion707(SQLiteDatabase db)3935 private void upgradeToVersion707(SQLiteDatabase db) { 3936 db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS 3937 + " ADD " + RawContactsColumns.PHONEBOOK_LABEL_PRIMARY + " TEXT;"); 3938 db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS 3939 + " ADD " + RawContactsColumns.PHONEBOOK_BUCKET_PRIMARY + " INTEGER;"); 3940 db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS 3941 + " ADD " + RawContactsColumns.PHONEBOOK_LABEL_ALTERNATIVE + " TEXT;"); 3942 db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS 3943 + " ADD " + RawContactsColumns.PHONEBOOK_BUCKET_ALTERNATIVE + " INTEGER;"); 3944 } 3945 upgradeToVersion710(SQLiteDatabase db)3946 private void upgradeToVersion710(SQLiteDatabase db) { 3947 3948 // Adding timestamp to contacts table. 3949 db.execSQL("ALTER TABLE contacts" 3950 + " ADD contact_last_updated_timestamp INTEGER;"); 3951 3952 db.execSQL("UPDATE contacts" 3953 + " SET contact_last_updated_timestamp" 3954 + " = " + System.currentTimeMillis()); 3955 3956 db.execSQL("CREATE INDEX contacts_contact_last_updated_timestamp_index " 3957 + "ON contacts(contact_last_updated_timestamp)"); 3958 3959 // New deleted contacts table. 3960 db.execSQL("CREATE TABLE deleted_contacts (" + 3961 "contact_id INTEGER PRIMARY KEY," + 3962 "contact_deleted_timestamp INTEGER NOT NULL default 0" 3963 + ");"); 3964 3965 db.execSQL("CREATE INDEX deleted_contacts_contact_deleted_timestamp_index " 3966 + "ON deleted_contacts(contact_deleted_timestamp)"); 3967 } 3968 extractHandleFromEmailAddress(String email)3969 public String extractHandleFromEmailAddress(String email) { 3970 Rfc822Token[] tokens = Rfc822Tokenizer.tokenize(email); 3971 if (tokens.length == 0) { 3972 return null; 3973 } 3974 3975 String address = tokens[0].getAddress(); 3976 int at = address.indexOf('@'); 3977 if (at != -1) { 3978 return address.substring(0, at); 3979 } 3980 return null; 3981 } 3982 extractAddressFromEmailAddress(String email)3983 public String extractAddressFromEmailAddress(String email) { 3984 Rfc822Token[] tokens = Rfc822Tokenizer.tokenize(email); 3985 if (tokens.length == 0) { 3986 return null; 3987 } 3988 3989 return tokens[0].getAddress().trim(); 3990 } 3991 lookupMimeTypeId(SQLiteDatabase db, String mimeType)3992 private static long lookupMimeTypeId(SQLiteDatabase db, String mimeType) { 3993 try { 3994 return DatabaseUtils.longForQuery(db, 3995 "SELECT " + MimetypesColumns._ID + 3996 " FROM " + Tables.MIMETYPES + 3997 " WHERE " + MimetypesColumns.MIMETYPE 3998 + "='" + mimeType + "'", null); 3999 } catch (SQLiteDoneException e) { 4000 // No rows of this type in the database 4001 return -1; 4002 } 4003 } 4004 bindString(SQLiteStatement stmt, int index, String value)4005 private void bindString(SQLiteStatement stmt, int index, String value) { 4006 if (value == null) { 4007 stmt.bindNull(index); 4008 } else { 4009 stmt.bindString(index, value); 4010 } 4011 } 4012 bindLong(SQLiteStatement stmt, int index, Number value)4013 private void bindLong(SQLiteStatement stmt, int index, Number value) { 4014 if (value == null) { 4015 stmt.bindNull(index); 4016 } else { 4017 stmt.bindLong(index, value.longValue()); 4018 } 4019 } 4020 4021 /** 4022 * Add a string like "(((column1) = (column2)) OR ((column1) IS NULL AND (column2) IS NULL))" 4023 */ addJoinExpressionAllowingNull(StringBuilder sb, String column1, String column2)4024 private static StringBuilder addJoinExpressionAllowingNull(StringBuilder sb, 4025 String column1, String column2) { 4026 sb.append("(((").append(column1).append(")=(").append(column2); 4027 sb.append("))OR(("); 4028 sb.append(column1).append(") IS NULL AND (").append(column2).append(") IS NULL))"); 4029 return sb; 4030 } 4031 4032 /** 4033 * Adds index stats into the SQLite database to force it to always use the lookup indexes. 4034 * 4035 * Note if you drop a table or an index, the corresponding row will be removed from this table. 4036 * Make sure to call this method after such operations. 4037 */ updateSqliteStats(SQLiteDatabase db)4038 private void updateSqliteStats(SQLiteDatabase db) { 4039 if (!mDatabaseOptimizationEnabled) { 4040 return; // We don't use sqlite_stat1 during tests. 4041 } 4042 4043 // Specific stats strings are based on an actual large database after running ANALYZE 4044 // Important here are relative sizes. Raw-Contacts is slightly bigger than Contacts 4045 // Warning: Missing tables in here will make SQLite assume to contain 1000000 rows, 4046 // which can lead to catastrophic query plans for small tables 4047 4048 // What these numbers mean is described in this file. 4049 // http://www.sqlite.org/cgi/src/finfo?name=src/analyze.c 4050 4051 // Excerpt: 4052 /* 4053 ** Format of sqlite_stat1: 4054 ** 4055 ** There is normally one row per index, with the index identified by the 4056 ** name in the idx column. The tbl column is the name of the table to 4057 ** which the index belongs. In each such row, the stat column will be 4058 ** a string consisting of a list of integers. The first integer in this 4059 ** list is the number of rows in the index and in the table. The second 4060 ** integer is the average number of rows in the index that have the same 4061 ** value in the first column of the index. The third integer is the average 4062 ** number of rows in the index that have the same value for the first two 4063 ** columns. The N-th integer (for N>1) is the average number of rows in 4064 ** the index which have the same value for the first N-1 columns. For 4065 ** a K-column index, there will be K+1 integers in the stat column. If 4066 ** the index is unique, then the last integer will be 1. 4067 ** 4068 ** The list of integers in the stat column can optionally be followed 4069 ** by the keyword "unordered". The "unordered" keyword, if it is present, 4070 ** must be separated from the last integer by a single space. If the 4071 ** "unordered" keyword is present, then the query planner assumes that 4072 ** the index is unordered and will not use the index for a range query. 4073 ** 4074 ** If the sqlite_stat1.idx column is NULL, then the sqlite_stat1.stat 4075 ** column contains a single integer which is the (estimated) number of 4076 ** rows in the table identified by sqlite_stat1.tbl. 4077 */ 4078 4079 try { 4080 db.execSQL("DELETE FROM sqlite_stat1"); 4081 updateIndexStats(db, Tables.CONTACTS, 4082 "contacts_has_phone_index", "9000 500"); 4083 updateIndexStats(db, Tables.CONTACTS, 4084 "contacts_name_raw_contact_id_index", "9000 1"); 4085 updateIndexStats(db, Tables.CONTACTS, MoreDatabaseUtils.buildIndexName(Tables.CONTACTS, 4086 Contacts.CONTACT_LAST_UPDATED_TIMESTAMP), "9000 10"); 4087 4088 updateIndexStats(db, Tables.RAW_CONTACTS, 4089 "raw_contacts_contact_id_index", "10000 2"); 4090 updateIndexStats(db, Tables.RAW_CONTACTS, 4091 "raw_contact_sort_key2_index", "10000 2"); 4092 updateIndexStats(db, Tables.RAW_CONTACTS, 4093 "raw_contact_sort_key1_index", "10000 2"); 4094 updateIndexStats(db, Tables.RAW_CONTACTS, 4095 "raw_contacts_source_id_account_id_index", "10000 1 1 1 1"); 4096 4097 updateIndexStats(db, Tables.NAME_LOOKUP, 4098 "name_lookup_raw_contact_id_index", "35000 4"); 4099 updateIndexStats(db, Tables.NAME_LOOKUP, 4100 "name_lookup_index", "35000 2 2 2 1"); 4101 updateIndexStats(db, Tables.NAME_LOOKUP, 4102 "sqlite_autoindex_name_lookup_1", "35000 3 2 1"); 4103 4104 updateIndexStats(db, Tables.PHONE_LOOKUP, 4105 "phone_lookup_index", "3500 3 2 1"); 4106 updateIndexStats(db, Tables.PHONE_LOOKUP, 4107 "phone_lookup_min_match_index", "3500 3 2 2"); 4108 updateIndexStats(db, Tables.PHONE_LOOKUP, 4109 "phone_lookup_data_id_min_match_index", "3500 2 2"); 4110 4111 updateIndexStats(db, Tables.DATA, 4112 "data_mimetype_data1_index", "60000 5000 2"); 4113 updateIndexStats(db, Tables.DATA, 4114 "data_raw_contact_id", "60000 10"); 4115 4116 updateIndexStats(db, Tables.GROUPS, 4117 "groups_source_id_account_id_index", "50 2 2 1 1"); 4118 4119 updateIndexStats(db, Tables.NICKNAME_LOOKUP, 4120 "nickname_lookup_index", "500 2 1"); 4121 4122 updateIndexStats(db, Tables.CALLS, 4123 null, "250"); 4124 4125 updateIndexStats(db, Tables.STATUS_UPDATES, 4126 null, "100"); 4127 4128 updateIndexStats(db, Tables.STREAM_ITEMS, 4129 null, "500"); 4130 updateIndexStats(db, Tables.STREAM_ITEM_PHOTOS, 4131 null, "50"); 4132 4133 updateIndexStats(db, Tables.VOICEMAIL_STATUS, 4134 null, "5"); 4135 4136 updateIndexStats(db, Tables.ACCOUNTS, 4137 null, "3"); 4138 4139 updateIndexStats(db, Tables.VISIBLE_CONTACTS, 4140 null, "2000"); 4141 4142 updateIndexStats(db, Tables.PHOTO_FILES, 4143 null, "50"); 4144 4145 updateIndexStats(db, Tables.DEFAULT_DIRECTORY, 4146 null, "1500"); 4147 4148 updateIndexStats(db, Tables.MIMETYPES, 4149 "mime_type", "18 1"); 4150 4151 updateIndexStats(db, Tables.DATA_USAGE_STAT, 4152 "data_usage_stat_index", "20 2 1"); 4153 4154 // Tiny tables 4155 updateIndexStats(db, Tables.AGGREGATION_EXCEPTIONS, 4156 null, "10"); 4157 updateIndexStats(db, Tables.SETTINGS, 4158 null, "10"); 4159 updateIndexStats(db, Tables.PACKAGES, 4160 null, "0"); 4161 updateIndexStats(db, Tables.DIRECTORIES, 4162 null, "3"); 4163 updateIndexStats(db, LegacyApiSupport.LegacyTables.SETTINGS, 4164 null, "0"); 4165 updateIndexStats(db, "android_metadata", 4166 null, "1"); 4167 updateIndexStats(db, "_sync_state", 4168 "sqlite_autoindex__sync_state_1", "2 1 1"); 4169 updateIndexStats(db, "_sync_state_metadata", 4170 null, "1"); 4171 updateIndexStats(db, "properties", 4172 "sqlite_autoindex_properties_1", "4 1"); 4173 4174 // Search index 4175 updateIndexStats(db, "search_index_docsize", 4176 null, "9000"); 4177 updateIndexStats(db, "search_index_content", 4178 null, "9000"); 4179 updateIndexStats(db, "search_index_stat", 4180 null, "1"); 4181 updateIndexStats(db, "search_index_segments", 4182 null, "450"); 4183 updateIndexStats(db, "search_index_segdir", 4184 "sqlite_autoindex_search_index_segdir_1", "9 5 1"); 4185 4186 // Force sqlite to reload sqlite_stat1. 4187 db.execSQL("ANALYZE sqlite_master;"); 4188 } catch (SQLException e) { 4189 Log.e(TAG, "Could not update index stats", e); 4190 } 4191 } 4192 4193 /** 4194 * Stores statistics for a given index. 4195 * 4196 * @param stats has the following structure: the first index is the expected size of 4197 * the table. The following integer(s) are the expected number of records selected with the 4198 * index. There should be one integer per indexed column. 4199 */ updateIndexStats(SQLiteDatabase db, String table, String index, String stats)4200 private void updateIndexStats(SQLiteDatabase db, String table, String index, 4201 String stats) { 4202 if (index == null) { 4203 db.execSQL("DELETE FROM sqlite_stat1 WHERE tbl=? AND idx IS NULL", 4204 new String[] { table }); 4205 } else { 4206 db.execSQL("DELETE FROM sqlite_stat1 WHERE tbl=? AND idx=?", 4207 new String[] { table, index }); 4208 } 4209 db.execSQL("INSERT INTO sqlite_stat1 (tbl,idx,stat) VALUES (?,?,?)", 4210 new String[] { table, index, stats }); 4211 } 4212 4213 /** 4214 * Wipes all data except mime type and package lookup tables. 4215 */ wipeData()4216 public void wipeData() { 4217 SQLiteDatabase db = getWritableDatabase(); 4218 4219 db.execSQL("DELETE FROM " + Tables.ACCOUNTS + ";"); 4220 db.execSQL("DELETE FROM " + Tables.CONTACTS + ";"); 4221 db.execSQL("DELETE FROM " + Tables.RAW_CONTACTS + ";"); 4222 db.execSQL("DELETE FROM " + Tables.STREAM_ITEMS + ";"); 4223 db.execSQL("DELETE FROM " + Tables.STREAM_ITEM_PHOTOS + ";"); 4224 db.execSQL("DELETE FROM " + Tables.PHOTO_FILES + ";"); 4225 db.execSQL("DELETE FROM " + Tables.DATA + ";"); 4226 db.execSQL("DELETE FROM " + Tables.PHONE_LOOKUP + ";"); 4227 db.execSQL("DELETE FROM " + Tables.NAME_LOOKUP + ";"); 4228 db.execSQL("DELETE FROM " + Tables.GROUPS + ";"); 4229 db.execSQL("DELETE FROM " + Tables.AGGREGATION_EXCEPTIONS + ";"); 4230 db.execSQL("DELETE FROM " + Tables.SETTINGS + ";"); 4231 db.execSQL("DELETE FROM " + Tables.CALLS + ";"); 4232 db.execSQL("DELETE FROM " + Tables.DIRECTORIES + ";"); 4233 db.execSQL("DELETE FROM " + Tables.SEARCH_INDEX + ";"); 4234 db.execSQL("DELETE FROM " + Tables.DELETED_CONTACTS + ";"); 4235 4236 initializeCache(db); 4237 4238 // Note: we are not removing reference data from Tables.NICKNAME_LOOKUP 4239 } 4240 createNameSplitter()4241 public NameSplitter createNameSplitter() { 4242 mNameSplitter = new NameSplitter( 4243 mContext.getString(com.android.internal.R.string.common_name_prefixes), 4244 mContext.getString(com.android.internal.R.string.common_last_name_prefixes), 4245 mContext.getString(com.android.internal.R.string.common_name_suffixes), 4246 mContext.getString(com.android.internal.R.string.common_name_conjunctions), 4247 Locale.getDefault()); 4248 return mNameSplitter; 4249 } 4250 4251 /** 4252 * Return the {@link ApplicationInfo#uid} for the given package name. 4253 */ getUidForPackageName(PackageManager pm, String packageName)4254 public static int getUidForPackageName(PackageManager pm, String packageName) { 4255 try { 4256 ApplicationInfo clientInfo = pm.getApplicationInfo(packageName, 0 /* no flags */); 4257 return clientInfo.uid; 4258 } catch (NameNotFoundException e) { 4259 throw new RuntimeException(e); 4260 } 4261 } 4262 4263 /** 4264 * Perform an internal string-to-integer lookup using the compiled 4265 * {@link SQLiteStatement} provided. If a mapping isn't found in database, it will be 4266 * created. All new, uncached answers are added to the cache automatically. 4267 * 4268 * @param query Compiled statement used to query for the mapping. 4269 * @param insert Compiled statement used to insert a new mapping when no 4270 * existing one is found in cache or from query. 4271 * @param value Value to find mapping for. 4272 * @param cache In-memory cache of previous answers. 4273 * @return An unique integer mapping for the given value. 4274 */ lookupAndCacheId(SQLiteStatement query, SQLiteStatement insert, String value, HashMap<String, Long> cache)4275 private long lookupAndCacheId(SQLiteStatement query, SQLiteStatement insert, 4276 String value, HashMap<String, Long> cache) { 4277 long id = -1; 4278 try { 4279 // Try searching database for mapping 4280 DatabaseUtils.bindObjectToProgram(query, 1, value); 4281 id = query.simpleQueryForLong(); 4282 } catch (SQLiteDoneException e) { 4283 // Nothing found, so try inserting new mapping 4284 DatabaseUtils.bindObjectToProgram(insert, 1, value); 4285 id = insert.executeInsert(); 4286 } 4287 if (id != -1) { 4288 // Cache and return the new answer 4289 cache.put(value, id); 4290 return id; 4291 } else { 4292 // Otherwise throw if no mapping found or created 4293 throw new IllegalStateException("Couldn't find or create internal " 4294 + "lookup table entry for value " + value); 4295 } 4296 } 4297 4298 /** 4299 * Convert a package name into an integer, using {@link Tables#PACKAGES} for 4300 * lookups and possible allocation of new IDs as needed. 4301 */ getPackageId(String packageName)4302 public long getPackageId(String packageName) { 4303 // Try an in-memory cache lookup 4304 if (mPackageCache.containsKey(packageName)) return mPackageCache.get(packageName); 4305 4306 final SQLiteStatement packageQuery = getWritableDatabase().compileStatement( 4307 "SELECT " + PackagesColumns._ID + 4308 " FROM " + Tables.PACKAGES + 4309 " WHERE " + PackagesColumns.PACKAGE + "=?"); 4310 4311 final SQLiteStatement packageInsert = getWritableDatabase().compileStatement( 4312 "INSERT INTO " + Tables.PACKAGES + "(" 4313 + PackagesColumns.PACKAGE + 4314 ") VALUES (?)"); 4315 try { 4316 return lookupAndCacheId(packageQuery, packageInsert, packageName, mPackageCache); 4317 } finally { 4318 packageQuery.close(); 4319 packageInsert.close(); 4320 } 4321 } 4322 4323 /** 4324 * Convert a mimetype into an integer, using {@link Tables#MIMETYPES} for 4325 * lookups and possible allocation of new IDs as needed. 4326 */ getMimeTypeId(String mimetype)4327 public long getMimeTypeId(String mimetype) { 4328 // Try an in-memory cache lookup 4329 if (mMimetypeCache.containsKey(mimetype)) return mMimetypeCache.get(mimetype); 4330 4331 return lookupMimeTypeId(mimetype, getWritableDatabase()); 4332 } 4333 lookupMimeTypeId(String mimetype, SQLiteDatabase db)4334 private long lookupMimeTypeId(String mimetype, SQLiteDatabase db) { 4335 final SQLiteStatement mimetypeQuery = db.compileStatement( 4336 "SELECT " + MimetypesColumns._ID + 4337 " FROM " + Tables.MIMETYPES + 4338 " WHERE " + MimetypesColumns.MIMETYPE + "=?"); 4339 4340 final SQLiteStatement mimetypeInsert = db.compileStatement( 4341 "INSERT INTO " + Tables.MIMETYPES + "(" 4342 + MimetypesColumns.MIMETYPE + 4343 ") VALUES (?)"); 4344 4345 try { 4346 return lookupAndCacheId(mimetypeQuery, mimetypeInsert, mimetype, mMimetypeCache); 4347 } finally { 4348 mimetypeQuery.close(); 4349 mimetypeInsert.close(); 4350 } 4351 } 4352 getMimeTypeIdForStructuredName()4353 public long getMimeTypeIdForStructuredName() { 4354 return mMimeTypeIdStructuredName; 4355 } 4356 getMimeTypeIdForStructuredPostal()4357 public long getMimeTypeIdForStructuredPostal() { 4358 return mMimeTypeIdStructuredPostal; 4359 } 4360 getMimeTypeIdForOrganization()4361 public long getMimeTypeIdForOrganization() { 4362 return mMimeTypeIdOrganization; 4363 } 4364 getMimeTypeIdForIm()4365 public long getMimeTypeIdForIm() { 4366 return mMimeTypeIdIm; 4367 } 4368 getMimeTypeIdForEmail()4369 public long getMimeTypeIdForEmail() { 4370 return mMimeTypeIdEmail; 4371 } 4372 getMimeTypeIdForPhone()4373 public long getMimeTypeIdForPhone() { 4374 return mMimeTypeIdPhone; 4375 } 4376 getMimeTypeIdForSip()4377 public long getMimeTypeIdForSip() { 4378 return mMimeTypeIdSip; 4379 } 4380 getDisplayNameSourceForMimeTypeId(int mimeTypeId)4381 public int getDisplayNameSourceForMimeTypeId(int mimeTypeId) { 4382 if (mimeTypeId == mMimeTypeIdStructuredName) { 4383 return DisplayNameSources.STRUCTURED_NAME; 4384 } else if (mimeTypeId == mMimeTypeIdEmail) { 4385 return DisplayNameSources.EMAIL; 4386 } else if (mimeTypeId == mMimeTypeIdPhone) { 4387 return DisplayNameSources.PHONE; 4388 } else if (mimeTypeId == mMimeTypeIdOrganization) { 4389 return DisplayNameSources.ORGANIZATION; 4390 } else if (mimeTypeId == mMimeTypeIdNickname) { 4391 return DisplayNameSources.NICKNAME; 4392 } else { 4393 return DisplayNameSources.UNDEFINED; 4394 } 4395 } 4396 4397 /** 4398 * Find the mimetype for the given {@link Data#_ID}. 4399 */ getDataMimeType(long dataId)4400 public String getDataMimeType(long dataId) { 4401 if (mDataMimetypeQuery == null) { 4402 mDataMimetypeQuery = getWritableDatabase().compileStatement( 4403 "SELECT " + MimetypesColumns.MIMETYPE + 4404 " FROM " + Tables.DATA_JOIN_MIMETYPES + 4405 " WHERE " + Tables.DATA + "." + Data._ID + "=?"); 4406 } 4407 try { 4408 // Try database query to find mimetype 4409 DatabaseUtils.bindObjectToProgram(mDataMimetypeQuery, 1, dataId); 4410 String mimetype = mDataMimetypeQuery.simpleQueryForString(); 4411 return mimetype; 4412 } catch (SQLiteDoneException e) { 4413 // No valid mapping found, so return null 4414 return null; 4415 } 4416 } 4417 invalidateAllCache()4418 public void invalidateAllCache() { 4419 Log.w(TAG, "invalidateAllCache: [" + getClass().getSimpleName() + "]"); 4420 4421 mMimetypeCache.clear(); 4422 mPackageCache.clear(); 4423 } 4424 4425 /** 4426 * Gets all accounts in the accounts table. 4427 */ getAllAccountsWithDataSets()4428 public Set<AccountWithDataSet> getAllAccountsWithDataSets() { 4429 final Set<AccountWithDataSet> result = Sets.newHashSet(); 4430 Cursor c = getReadableDatabase().rawQuery( 4431 "SELECT DISTINCT " + AccountsColumns._ID + "," + AccountsColumns.ACCOUNT_NAME + 4432 "," + AccountsColumns.ACCOUNT_TYPE + "," + AccountsColumns.DATA_SET + 4433 " FROM " + Tables.ACCOUNTS, null); 4434 try { 4435 while (c.moveToNext()) { 4436 result.add(AccountWithDataSet.get(c.getString(1), c.getString(2), c.getString(3))); 4437 } 4438 } finally { 4439 c.close(); 4440 } 4441 return result; 4442 } 4443 4444 /** 4445 * @return ID of the specified account, or null if the account doesn't exist. 4446 */ getAccountIdOrNull(AccountWithDataSet accountWithDataSet)4447 public Long getAccountIdOrNull(AccountWithDataSet accountWithDataSet) { 4448 if (accountWithDataSet == null) { 4449 accountWithDataSet = AccountWithDataSet.LOCAL; 4450 } 4451 final SQLiteStatement select = getWritableDatabase().compileStatement( 4452 "SELECT " + AccountsColumns._ID + 4453 " FROM " + Tables.ACCOUNTS + 4454 " WHERE " + 4455 "((?1 IS NULL AND " + AccountsColumns.ACCOUNT_NAME + " IS NULL) OR " + 4456 "(" + AccountsColumns.ACCOUNT_NAME + "=?1)) AND " + 4457 "((?2 IS NULL AND " + AccountsColumns.ACCOUNT_TYPE + " IS NULL) OR " + 4458 "(" + AccountsColumns.ACCOUNT_TYPE + "=?2)) AND " + 4459 "((?3 IS NULL AND " + AccountsColumns.DATA_SET + " IS NULL) OR " + 4460 "(" + AccountsColumns.DATA_SET + "=?3))"); 4461 try { 4462 DatabaseUtils.bindObjectToProgram(select, 1, accountWithDataSet.getAccountName()); 4463 DatabaseUtils.bindObjectToProgram(select, 2, accountWithDataSet.getAccountType()); 4464 DatabaseUtils.bindObjectToProgram(select, 3, accountWithDataSet.getDataSet()); 4465 try { 4466 return select.simpleQueryForLong(); 4467 } catch (SQLiteDoneException notFound) { 4468 return null; 4469 } 4470 } finally { 4471 select.close(); 4472 } 4473 } 4474 4475 /** 4476 * @return ID of the specified account. This method will create a record in the accounts table 4477 * if the account doesn't exist in the accounts table. 4478 * 4479 * This must be used in a transaction, so there's no need for synchronization. 4480 */ getOrCreateAccountIdInTransaction(AccountWithDataSet accountWithDataSet)4481 public long getOrCreateAccountIdInTransaction(AccountWithDataSet accountWithDataSet) { 4482 if (accountWithDataSet == null) { 4483 accountWithDataSet = AccountWithDataSet.LOCAL; 4484 } 4485 Long id = getAccountIdOrNull(accountWithDataSet); 4486 if (id != null) { 4487 return id; 4488 } 4489 final SQLiteStatement insert = getWritableDatabase().compileStatement( 4490 "INSERT INTO " + Tables.ACCOUNTS + 4491 " (" + AccountsColumns.ACCOUNT_NAME + ", " + 4492 AccountsColumns.ACCOUNT_TYPE + ", " + 4493 AccountsColumns.DATA_SET + ") VALUES (?, ?, ?)"); 4494 try { 4495 DatabaseUtils.bindObjectToProgram(insert, 1, accountWithDataSet.getAccountName()); 4496 DatabaseUtils.bindObjectToProgram(insert, 2, accountWithDataSet.getAccountType()); 4497 DatabaseUtils.bindObjectToProgram(insert, 3, accountWithDataSet.getDataSet()); 4498 id = insert.executeInsert(); 4499 } finally { 4500 insert.close(); 4501 } 4502 4503 return id; 4504 } 4505 4506 /** 4507 * Update {@link Contacts#IN_VISIBLE_GROUP} for all contacts. 4508 */ updateAllVisible()4509 public void updateAllVisible() { 4510 updateCustomContactVisibility(getWritableDatabase(), -1); 4511 } 4512 4513 /** 4514 * Updates contact visibility and return true iff the visibility was actually changed. 4515 */ updateContactVisibleOnlyIfChanged(TransactionContext txContext, long contactId)4516 public boolean updateContactVisibleOnlyIfChanged(TransactionContext txContext, long contactId) { 4517 return updateContactVisible(txContext, contactId, true); 4518 } 4519 4520 /** 4521 * Update {@link Contacts#IN_VISIBLE_GROUP} and 4522 * {@link Tables#DEFAULT_DIRECTORY} for a specific contact. 4523 */ updateContactVisible(TransactionContext txContext, long contactId)4524 public void updateContactVisible(TransactionContext txContext, long contactId) { 4525 updateContactVisible(txContext, contactId, false); 4526 } 4527 updateContactVisible( TransactionContext txContext, long contactId, boolean onlyIfChanged)4528 public boolean updateContactVisible( 4529 TransactionContext txContext, long contactId, boolean onlyIfChanged) { 4530 SQLiteDatabase db = getWritableDatabase(); 4531 updateCustomContactVisibility(db, contactId); 4532 4533 String contactIdAsString = String.valueOf(contactId); 4534 long mimetype = getMimeTypeId(GroupMembership.CONTENT_ITEM_TYPE); 4535 4536 // The contact will be included in the default directory if contains 4537 // a raw contact that is in any group or in an account that 4538 // does not have any AUTO_ADD groups. 4539 boolean newVisibility = DatabaseUtils.longForQuery(db, 4540 "SELECT EXISTS (" + 4541 "SELECT " + RawContacts.CONTACT_ID + 4542 " FROM " + Tables.RAW_CONTACTS + 4543 " JOIN " + Tables.DATA + 4544 " ON (" + RawContactsColumns.CONCRETE_ID + "=" 4545 + Data.RAW_CONTACT_ID + ")" + 4546 " WHERE " + RawContacts.CONTACT_ID + "=?1" + 4547 " AND " + DataColumns.MIMETYPE_ID + "=?2" + 4548 ") OR EXISTS (" + 4549 "SELECT " + RawContacts._ID + 4550 " FROM " + Tables.RAW_CONTACTS + 4551 " WHERE " + RawContacts.CONTACT_ID + "=?1" + 4552 " AND NOT EXISTS" + 4553 " (SELECT " + Groups._ID + 4554 " FROM " + Tables.GROUPS + 4555 " WHERE " + RawContactsColumns.CONCRETE_ACCOUNT_ID + " = " 4556 + GroupsColumns.CONCRETE_ACCOUNT_ID + 4557 " AND " + Groups.AUTO_ADD + " != 0" + 4558 ")" + 4559 ") OR EXISTS (" + 4560 "SELECT " + RawContacts._ID + 4561 " FROM " + Tables.RAW_CONTACTS + 4562 " WHERE " + RawContacts.CONTACT_ID + "=?1" + 4563 " AND " + RawContactsColumns.CONCRETE_ACCOUNT_ID + "=" + 4564 Clauses.LOCAL_ACCOUNT_ID + 4565 ")", 4566 new String[] { 4567 contactIdAsString, 4568 String.valueOf(mimetype) 4569 }) != 0; 4570 4571 if (onlyIfChanged) { 4572 boolean oldVisibility = isContactInDefaultDirectory(db, contactId); 4573 if (oldVisibility == newVisibility) { 4574 return false; 4575 } 4576 } 4577 4578 if (newVisibility) { 4579 db.execSQL("INSERT OR IGNORE INTO " + Tables.DEFAULT_DIRECTORY + " VALUES(?)", 4580 new String[] { contactIdAsString }); 4581 txContext.invalidateSearchIndexForContact(contactId); 4582 } else { 4583 db.execSQL("DELETE FROM " + Tables.DEFAULT_DIRECTORY + 4584 " WHERE " + Contacts._ID + "=?", 4585 new String[] { contactIdAsString }); 4586 db.execSQL("DELETE FROM " + Tables.SEARCH_INDEX + 4587 " WHERE " + SearchIndexColumns.CONTACT_ID + "=CAST(? AS int)", 4588 new String[] { contactIdAsString }); 4589 } 4590 return true; 4591 } 4592 isContactInDefaultDirectory(SQLiteDatabase db, long contactId)4593 public boolean isContactInDefaultDirectory(SQLiteDatabase db, long contactId) { 4594 if (mContactInDefaultDirectoryQuery == null) { 4595 mContactInDefaultDirectoryQuery = db.compileStatement( 4596 "SELECT EXISTS (" + 4597 "SELECT 1 FROM " + Tables.DEFAULT_DIRECTORY + 4598 " WHERE " + Contacts._ID + "=?)"); 4599 } 4600 mContactInDefaultDirectoryQuery.bindLong(1, contactId); 4601 return mContactInDefaultDirectoryQuery.simpleQueryForLong() != 0; 4602 } 4603 4604 /** 4605 * Update the visible_contacts table according to the current visibility of contacts, which 4606 * is defined by {@link Clauses#CONTACT_IS_VISIBLE}. 4607 * 4608 * If {@code optionalContactId} is non-negative, it'll update only for the specified contact. 4609 */ updateCustomContactVisibility(SQLiteDatabase db, long optionalContactId)4610 private void updateCustomContactVisibility(SQLiteDatabase db, long optionalContactId) { 4611 final long groupMembershipMimetypeId = getMimeTypeId(GroupMembership.CONTENT_ITEM_TYPE); 4612 String[] selectionArgs = new String[]{String.valueOf(groupMembershipMimetypeId)}; 4613 4614 final String contactIdSelect = (optionalContactId < 0) ? "" : 4615 (Contacts._ID + "=" + optionalContactId + " AND "); 4616 4617 // First delete what needs to be deleted, then insert what needs to be added. 4618 // Since flash writes are very expensive, this approach is much better than 4619 // delete-all-insert-all. 4620 db.execSQL( 4621 "DELETE FROM " + Tables.VISIBLE_CONTACTS + 4622 " WHERE " + Contacts._ID + " IN" + 4623 "(SELECT " + Contacts._ID + 4624 " FROM " + Tables.CONTACTS + 4625 " WHERE " + contactIdSelect + "(" + Clauses.CONTACT_IS_VISIBLE + ")=0) ", 4626 selectionArgs); 4627 4628 db.execSQL( 4629 "INSERT INTO " + Tables.VISIBLE_CONTACTS + 4630 " SELECT " + Contacts._ID + 4631 " FROM " + Tables.CONTACTS + 4632 " WHERE " + 4633 contactIdSelect + 4634 Contacts._ID + " NOT IN " + Tables.VISIBLE_CONTACTS + 4635 " AND (" + Clauses.CONTACT_IS_VISIBLE + ")=1 ", 4636 selectionArgs); 4637 } 4638 4639 /** 4640 * Returns contact ID for the given contact or zero if it is NULL. 4641 */ getContactId(long rawContactId)4642 public long getContactId(long rawContactId) { 4643 if (mContactIdQuery == null) { 4644 mContactIdQuery = getWritableDatabase().compileStatement( 4645 "SELECT " + RawContacts.CONTACT_ID + 4646 " FROM " + Tables.RAW_CONTACTS + 4647 " WHERE " + RawContacts._ID + "=?"); 4648 } 4649 try { 4650 DatabaseUtils.bindObjectToProgram(mContactIdQuery, 1, rawContactId); 4651 return mContactIdQuery.simpleQueryForLong(); 4652 } catch (SQLiteDoneException e) { 4653 // No valid mapping found, so return 0 4654 return 0; 4655 } 4656 } 4657 getAggregationMode(long rawContactId)4658 public int getAggregationMode(long rawContactId) { 4659 if (mAggregationModeQuery == null) { 4660 mAggregationModeQuery = getWritableDatabase().compileStatement( 4661 "SELECT " + RawContacts.AGGREGATION_MODE + 4662 " FROM " + Tables.RAW_CONTACTS + 4663 " WHERE " + RawContacts._ID + "=?"); 4664 } 4665 try { 4666 DatabaseUtils.bindObjectToProgram(mAggregationModeQuery, 1, rawContactId); 4667 return (int)mAggregationModeQuery.simpleQueryForLong(); 4668 } catch (SQLiteDoneException e) { 4669 // No valid row found, so return "disabled" 4670 return RawContacts.AGGREGATION_MODE_DISABLED; 4671 } 4672 } 4673 buildPhoneLookupAndContactQuery( SQLiteQueryBuilder qb, String normalizedNumber, String numberE164)4674 public void buildPhoneLookupAndContactQuery( 4675 SQLiteQueryBuilder qb, String normalizedNumber, String numberE164) { 4676 String minMatch = PhoneNumberUtils.toCallerIDMinMatch(normalizedNumber); 4677 StringBuilder sb = new StringBuilder(); 4678 appendPhoneLookupTables(sb, minMatch, true); 4679 qb.setTables(sb.toString()); 4680 4681 sb = new StringBuilder(); 4682 appendPhoneLookupSelection(sb, normalizedNumber, numberE164); 4683 qb.appendWhere(sb.toString()); 4684 } 4685 4686 /** 4687 * Phone lookup method that uses the custom SQLite function phone_number_compare_loose 4688 * that serves as a fallback in case the regular lookup does not return any results. 4689 * @param qb The query builder. 4690 * @param number The phone number to search for. 4691 */ buildFallbackPhoneLookupAndContactQuery(SQLiteQueryBuilder qb, String number)4692 public void buildFallbackPhoneLookupAndContactQuery(SQLiteQueryBuilder qb, String number) { 4693 final String minMatch = PhoneNumberUtils.toCallerIDMinMatch(number); 4694 final StringBuilder sb = new StringBuilder(); 4695 //append lookup tables 4696 sb.append(Tables.RAW_CONTACTS); 4697 sb.append(" JOIN " + Views.CONTACTS + " as contacts_view" 4698 + " ON (contacts_view._id = " + Tables.RAW_CONTACTS 4699 + "." + RawContacts.CONTACT_ID + ")" + 4700 " JOIN (SELECT " + PhoneLookupColumns.DATA_ID + "," + 4701 PhoneLookupColumns.NORMALIZED_NUMBER + " FROM "+ Tables.PHONE_LOOKUP + " " 4702 + "WHERE (" + Tables.PHONE_LOOKUP + "." + PhoneLookupColumns.MIN_MATCH + " = '"); 4703 sb.append(minMatch); 4704 sb.append("')) AS lookup " + 4705 "ON lookup." + PhoneLookupColumns.DATA_ID + "=" + Tables.DATA + "." + Data._ID 4706 + " JOIN " + Tables.DATA + " " 4707 + "ON " + Tables.DATA + "." + Data.RAW_CONTACT_ID + "=" + Tables.RAW_CONTACTS + "." 4708 + RawContacts._ID); 4709 4710 qb.setTables(sb.toString()); 4711 4712 sb.setLength(0); 4713 sb.append("PHONE_NUMBERS_EQUAL(" + Tables.DATA + "." + Phone.NUMBER + ", "); 4714 DatabaseUtils.appendEscapedSQLString(sb, number); 4715 sb.append(mUseStrictPhoneNumberComparison ? ", 1)" : ", 0)"); 4716 qb.appendWhere(sb.toString()); 4717 } 4718 4719 /** 4720 * Adds query for selecting the contact with the given {@code sipAddress} to the given 4721 * {@link StringBuilder}. 4722 * 4723 * @return the query arguments to be passed in with the query 4724 */ buildSipContactQuery(StringBuilder sb, String sipAddress)4725 public String[] buildSipContactQuery(StringBuilder sb, String sipAddress) { 4726 sb.append("upper("); 4727 sb.append(Data.DATA1); 4728 sb.append(")=upper(?) AND "); 4729 sb.append(DataColumns.MIMETYPE_ID); 4730 sb.append("="); 4731 sb.append(Long.toString(getMimeTypeIdForSip())); 4732 // Return the arguments to be passed to the query. 4733 return new String[]{ sipAddress }; 4734 } 4735 buildPhoneLookupAsNestedQuery(String number)4736 public String buildPhoneLookupAsNestedQuery(String number) { 4737 StringBuilder sb = new StringBuilder(); 4738 final String minMatch = PhoneNumberUtils.toCallerIDMinMatch(number); 4739 sb.append("(SELECT DISTINCT raw_contact_id" + " FROM "); 4740 appendPhoneLookupTables(sb, minMatch, false); 4741 sb.append(" WHERE "); 4742 appendPhoneLookupSelection(sb, number, null); 4743 sb.append(")"); 4744 return sb.toString(); 4745 } 4746 appendPhoneLookupTables(StringBuilder sb, final String minMatch, boolean joinContacts)4747 private void appendPhoneLookupTables(StringBuilder sb, final String minMatch, 4748 boolean joinContacts) { 4749 sb.append(Tables.RAW_CONTACTS); 4750 if (joinContacts) { 4751 sb.append(" JOIN " + Views.CONTACTS + " contacts_view" 4752 + " ON (contacts_view._id = raw_contacts.contact_id)"); 4753 } 4754 sb.append(", (SELECT data_id, normalized_number, length(normalized_number) as len " 4755 + " FROM phone_lookup " + " WHERE (" + Tables.PHONE_LOOKUP + "." 4756 + PhoneLookupColumns.MIN_MATCH + " = '"); 4757 sb.append(minMatch); 4758 sb.append("')) AS lookup, " + Tables.DATA); 4759 } 4760 appendPhoneLookupSelection(StringBuilder sb, String number, String numberE164)4761 private void appendPhoneLookupSelection(StringBuilder sb, String number, String numberE164) { 4762 sb.append("lookup.data_id=data._id AND data.raw_contact_id=raw_contacts._id"); 4763 boolean hasNumberE164 = !TextUtils.isEmpty(numberE164); 4764 boolean hasNumber = !TextUtils.isEmpty(number); 4765 if (hasNumberE164 || hasNumber) { 4766 sb.append(" AND ( "); 4767 if (hasNumberE164) { 4768 sb.append(" lookup.normalized_number = "); 4769 DatabaseUtils.appendEscapedSQLString(sb, numberE164); 4770 } 4771 if (hasNumberE164 && hasNumber) { 4772 sb.append(" OR "); 4773 } 4774 if (hasNumber) { 4775 // skip the suffix match entirely if we are using strict number comparison 4776 if (!mUseStrictPhoneNumberComparison) { 4777 int numberLen = number.length(); 4778 sb.append(" lookup.len <= "); 4779 sb.append(numberLen); 4780 sb.append(" AND substr("); 4781 DatabaseUtils.appendEscapedSQLString(sb, number); 4782 sb.append(','); 4783 sb.append(numberLen); 4784 sb.append(" - lookup.len + 1) = lookup.normalized_number"); 4785 4786 // Some countries (e.g. Brazil) can have incoming calls which contain only the local 4787 // number (no country calling code and no area code). This case is handled below. 4788 // Details see b/5197612. 4789 // This also handles a Gingerbread -> ICS upgrade issue; see b/5638376. 4790 sb.append(" OR ("); 4791 sb.append(" lookup.len > "); 4792 sb.append(numberLen); 4793 sb.append(" AND substr(lookup.normalized_number,"); 4794 sb.append("lookup.len + 1 - "); 4795 sb.append(numberLen); 4796 sb.append(") = "); 4797 DatabaseUtils.appendEscapedSQLString(sb, number); 4798 sb.append(")"); 4799 } else { 4800 sb.append("0"); 4801 } 4802 } 4803 sb.append(')'); 4804 } 4805 } 4806 getUseStrictPhoneNumberComparisonParameter()4807 public String getUseStrictPhoneNumberComparisonParameter() { 4808 return mUseStrictPhoneNumberComparison ? "1" : "0"; 4809 } 4810 4811 /** 4812 * Loads common nickname mappings into the database. 4813 */ loadNicknameLookupTable(SQLiteDatabase db)4814 private void loadNicknameLookupTable(SQLiteDatabase db) { 4815 db.execSQL("DELETE FROM " + Tables.NICKNAME_LOOKUP); 4816 4817 String[] strings = mContext.getResources().getStringArray( 4818 com.android.internal.R.array.common_nicknames); 4819 if (strings == null || strings.length == 0) { 4820 return; 4821 } 4822 4823 SQLiteStatement nicknameLookupInsert = db.compileStatement("INSERT INTO " 4824 + Tables.NICKNAME_LOOKUP + "(" + NicknameLookupColumns.NAME + "," 4825 + NicknameLookupColumns.CLUSTER + ") VALUES (?,?)"); 4826 4827 try { 4828 for (int clusterId = 0; clusterId < strings.length; clusterId++) { 4829 String[] names = strings[clusterId].split(","); 4830 for (int j = 0; j < names.length; j++) { 4831 String name = NameNormalizer.normalize(names[j]); 4832 try { 4833 DatabaseUtils.bindObjectToProgram(nicknameLookupInsert, 1, name); 4834 DatabaseUtils.bindObjectToProgram(nicknameLookupInsert, 2, 4835 String.valueOf(clusterId)); 4836 nicknameLookupInsert.executeInsert(); 4837 } catch (SQLiteException e) { 4838 4839 // Print the exception and keep going - this is not a fatal error 4840 Log.e(TAG, "Cannot insert nickname: " + names[j], e); 4841 } 4842 } 4843 } 4844 } finally { 4845 nicknameLookupInsert.close(); 4846 } 4847 } 4848 copyStringValue(ContentValues toValues, String toKey, ContentValues fromValues, String fromKey)4849 public static void copyStringValue(ContentValues toValues, String toKey, 4850 ContentValues fromValues, String fromKey) { 4851 if (fromValues.containsKey(fromKey)) { 4852 toValues.put(toKey, fromValues.getAsString(fromKey)); 4853 } 4854 } 4855 copyLongValue(ContentValues toValues, String toKey, ContentValues fromValues, String fromKey)4856 public static void copyLongValue(ContentValues toValues, String toKey, 4857 ContentValues fromValues, String fromKey) { 4858 if (fromValues.containsKey(fromKey)) { 4859 long longValue; 4860 Object value = fromValues.get(fromKey); 4861 if (value instanceof Boolean) { 4862 if ((Boolean)value) { 4863 longValue = 1; 4864 } else { 4865 longValue = 0; 4866 } 4867 } else if (value instanceof String) { 4868 longValue = Long.parseLong((String)value); 4869 } else { 4870 longValue = ((Number)value).longValue(); 4871 } 4872 toValues.put(toKey, longValue); 4873 } 4874 } 4875 getSyncState()4876 public SyncStateContentProviderHelper getSyncState() { 4877 return mSyncState; 4878 } 4879 4880 /** 4881 * Returns the value from the {@link Tables#PROPERTIES} table. 4882 */ getProperty(String key, String defaultValue)4883 public String getProperty(String key, String defaultValue) { 4884 return getProperty(getReadableDatabase(), key, defaultValue); 4885 } 4886 getProperty(SQLiteDatabase db, String key, String defaultValue)4887 public String getProperty(SQLiteDatabase db, String key, String defaultValue) { 4888 Cursor cursor = db.query(Tables.PROPERTIES, 4889 new String[]{PropertiesColumns.PROPERTY_VALUE}, 4890 PropertiesColumns.PROPERTY_KEY + "=?", 4891 new String[]{key}, null, null, null); 4892 String value = null; 4893 try { 4894 if (cursor.moveToFirst()) { 4895 value = cursor.getString(0); 4896 } 4897 } finally { 4898 cursor.close(); 4899 } 4900 4901 return value != null ? value : defaultValue; 4902 } 4903 4904 /** 4905 * Stores a key-value pair in the {@link Tables#PROPERTIES} table. 4906 */ setProperty(String key, String value)4907 public void setProperty(String key, String value) { 4908 setProperty(getWritableDatabase(), key, value); 4909 } 4910 setProperty(SQLiteDatabase db, String key, String value)4911 private void setProperty(SQLiteDatabase db, String key, String value) { 4912 ContentValues values = new ContentValues(); 4913 values.put(PropertiesColumns.PROPERTY_KEY, key); 4914 values.put(PropertiesColumns.PROPERTY_VALUE, value); 4915 db.replace(Tables.PROPERTIES, null, values); 4916 } 4917 4918 /** 4919 * Test if the given column appears in the given projection. 4920 */ isInProjection(String[] projection, String column)4921 public static boolean isInProjection(String[] projection, String column) { 4922 if (projection == null) { 4923 return true; // Null means "all columns". We can't really tell if it's in there... 4924 } 4925 for (String test : projection) { 4926 if (column.equals(test)) { 4927 return true; 4928 } 4929 } 4930 return false; 4931 } 4932 4933 /** 4934 * Test if any of the columns appear in the given projection. 4935 */ isInProjection(String[] projection, String... columns)4936 public static boolean isInProjection(String[] projection, String... columns) { 4937 if (projection == null) { 4938 return true; 4939 } 4940 4941 // Optimized for a single-column test 4942 if (columns.length == 1) { 4943 return isInProjection(projection, columns[0]); 4944 } else { 4945 for (String test : projection) { 4946 for (String column : columns) { 4947 if (column.equals(test)) { 4948 return true; 4949 } 4950 } 4951 } 4952 } 4953 return false; 4954 } 4955 4956 /** 4957 * Returns a detailed exception message for the supplied URI. It includes the calling 4958 * user and calling package(s). 4959 */ exceptionMessage(Uri uri)4960 public String exceptionMessage(Uri uri) { 4961 return exceptionMessage(null, uri); 4962 } 4963 4964 /** 4965 * Returns a detailed exception message for the supplied URI. It includes the calling 4966 * user and calling package(s). 4967 */ exceptionMessage(String message, Uri uri)4968 public String exceptionMessage(String message, Uri uri) { 4969 StringBuilder sb = new StringBuilder(); 4970 if (message != null) { 4971 sb.append(message).append("; "); 4972 } 4973 sb.append("URI: ").append(uri); 4974 final PackageManager pm = mContext.getPackageManager(); 4975 int callingUid = Binder.getCallingUid(); 4976 sb.append(", calling user: "); 4977 String userName = pm.getNameForUid(callingUid); 4978 if (userName != null) { 4979 sb.append(userName); 4980 } else { 4981 sb.append(callingUid); 4982 } 4983 4984 final String[] callerPackages = pm.getPackagesForUid(callingUid); 4985 if (callerPackages != null && callerPackages.length > 0) { 4986 if (callerPackages.length == 1) { 4987 sb.append(", calling package:"); 4988 sb.append(callerPackages[0]); 4989 } else { 4990 sb.append(", calling package is one of: ["); 4991 for (int i = 0; i < callerPackages.length; i++) { 4992 if (i != 0) { 4993 sb.append(", "); 4994 } 4995 sb.append(callerPackages[i]); 4996 } 4997 sb.append("]"); 4998 } 4999 } 5000 5001 return sb.toString(); 5002 } 5003 getCountryIso()5004 protected String getCountryIso() { 5005 CountryDetector detector = 5006 (CountryDetector) mContext.getSystemService(Context.COUNTRY_DETECTOR); 5007 return detector.detectCountry().getCountryIso(); 5008 } 5009 deleteStatusUpdate(long dataId)5010 public void deleteStatusUpdate(long dataId) { 5011 if (mStatusUpdateDelete == null) { 5012 mStatusUpdateDelete = getWritableDatabase().compileStatement( 5013 "DELETE FROM " + Tables.STATUS_UPDATES + 5014 " WHERE " + StatusUpdatesColumns.DATA_ID + "=?"); 5015 } 5016 mStatusUpdateDelete.bindLong(1, dataId); 5017 mStatusUpdateDelete.execute(); 5018 } 5019 replaceStatusUpdate(Long dataId, long timestamp, String status, String resPackage, Integer iconResource, Integer labelResource)5020 public void replaceStatusUpdate(Long dataId, long timestamp, String status, String resPackage, 5021 Integer iconResource, Integer labelResource) { 5022 if (mStatusUpdateReplace == null) { 5023 mStatusUpdateReplace = getWritableDatabase().compileStatement( 5024 "INSERT OR REPLACE INTO " + Tables.STATUS_UPDATES + "(" 5025 + StatusUpdatesColumns.DATA_ID + ", " 5026 + StatusUpdates.STATUS_TIMESTAMP + "," 5027 + StatusUpdates.STATUS + "," 5028 + StatusUpdates.STATUS_RES_PACKAGE + "," 5029 + StatusUpdates.STATUS_ICON + "," 5030 + StatusUpdates.STATUS_LABEL + ")" + 5031 " VALUES (?,?,?,?,?,?)"); 5032 } 5033 mStatusUpdateReplace.bindLong(1, dataId); 5034 mStatusUpdateReplace.bindLong(2, timestamp); 5035 bindString(mStatusUpdateReplace, 3, status); 5036 bindString(mStatusUpdateReplace, 4, resPackage); 5037 bindLong(mStatusUpdateReplace, 5, iconResource); 5038 bindLong(mStatusUpdateReplace, 6, labelResource); 5039 mStatusUpdateReplace.execute(); 5040 } 5041 insertStatusUpdate(Long dataId, String status, String resPackage, Integer iconResource, Integer labelResource)5042 public void insertStatusUpdate(Long dataId, String status, String resPackage, 5043 Integer iconResource, Integer labelResource) { 5044 if (mStatusUpdateInsert == null) { 5045 mStatusUpdateInsert = getWritableDatabase().compileStatement( 5046 "INSERT INTO " + Tables.STATUS_UPDATES + "(" 5047 + StatusUpdatesColumns.DATA_ID + ", " 5048 + StatusUpdates.STATUS + "," 5049 + StatusUpdates.STATUS_RES_PACKAGE + "," 5050 + StatusUpdates.STATUS_ICON + "," 5051 + StatusUpdates.STATUS_LABEL + ")" + 5052 " VALUES (?,?,?,?,?)"); 5053 } 5054 try { 5055 mStatusUpdateInsert.bindLong(1, dataId); 5056 bindString(mStatusUpdateInsert, 2, status); 5057 bindString(mStatusUpdateInsert, 3, resPackage); 5058 bindLong(mStatusUpdateInsert, 4, iconResource); 5059 bindLong(mStatusUpdateInsert, 5, labelResource); 5060 mStatusUpdateInsert.executeInsert(); 5061 } catch (SQLiteConstraintException e) { 5062 // The row already exists - update it 5063 if (mStatusUpdateAutoTimestamp == null) { 5064 mStatusUpdateAutoTimestamp = getWritableDatabase().compileStatement( 5065 "UPDATE " + Tables.STATUS_UPDATES + 5066 " SET " + StatusUpdates.STATUS_TIMESTAMP + "=?," 5067 + StatusUpdates.STATUS + "=?" + 5068 " WHERE " + StatusUpdatesColumns.DATA_ID + "=?" 5069 + " AND " + StatusUpdates.STATUS + "!=?"); 5070 } 5071 5072 long timestamp = System.currentTimeMillis(); 5073 mStatusUpdateAutoTimestamp.bindLong(1, timestamp); 5074 bindString(mStatusUpdateAutoTimestamp, 2, status); 5075 mStatusUpdateAutoTimestamp.bindLong(3, dataId); 5076 bindString(mStatusUpdateAutoTimestamp, 4, status); 5077 mStatusUpdateAutoTimestamp.execute(); 5078 5079 if (mStatusAttributionUpdate == null) { 5080 mStatusAttributionUpdate = getWritableDatabase().compileStatement( 5081 "UPDATE " + Tables.STATUS_UPDATES + 5082 " SET " + StatusUpdates.STATUS_RES_PACKAGE + "=?," 5083 + StatusUpdates.STATUS_ICON + "=?," 5084 + StatusUpdates.STATUS_LABEL + "=?" + 5085 " WHERE " + StatusUpdatesColumns.DATA_ID + "=?"); 5086 } 5087 bindString(mStatusAttributionUpdate, 1, resPackage); 5088 bindLong(mStatusAttributionUpdate, 2, iconResource); 5089 bindLong(mStatusAttributionUpdate, 3, labelResource); 5090 mStatusAttributionUpdate.bindLong(4, dataId); 5091 mStatusAttributionUpdate.execute(); 5092 } 5093 } 5094 5095 /** 5096 * Resets the {@link RawContacts#NAME_VERIFIED} flag to 0 on all other raw 5097 * contacts in the same aggregate 5098 */ resetNameVerifiedForOtherRawContacts(long rawContactId)5099 public void resetNameVerifiedForOtherRawContacts(long rawContactId) { 5100 if (mResetNameVerifiedForOtherRawContacts == null) { 5101 mResetNameVerifiedForOtherRawContacts = getWritableDatabase().compileStatement( 5102 "UPDATE " + Tables.RAW_CONTACTS + 5103 " SET " + RawContacts.NAME_VERIFIED + "=0" + 5104 " WHERE " + RawContacts.CONTACT_ID + "=(" + 5105 "SELECT " + RawContacts.CONTACT_ID + 5106 " FROM " + Tables.RAW_CONTACTS + 5107 " WHERE " + RawContacts._ID + "=?)" + 5108 " AND " + RawContacts._ID + "!=?"); 5109 } 5110 mResetNameVerifiedForOtherRawContacts.bindLong(1, rawContactId); 5111 mResetNameVerifiedForOtherRawContacts.bindLong(2, rawContactId); 5112 mResetNameVerifiedForOtherRawContacts.execute(); 5113 } 5114 5115 private interface RawContactNameQuery { 5116 public static final String RAW_SQL = 5117 "SELECT " 5118 + DataColumns.MIMETYPE_ID + "," 5119 + Data.IS_PRIMARY + "," 5120 + Data.DATA1 + "," 5121 + Data.DATA2 + "," 5122 + Data.DATA3 + "," 5123 + Data.DATA4 + "," 5124 + Data.DATA5 + "," 5125 + Data.DATA6 + "," 5126 + Data.DATA7 + "," 5127 + Data.DATA8 + "," 5128 + Data.DATA9 + "," 5129 + Data.DATA10 + "," 5130 + Data.DATA11 + 5131 " FROM " + Tables.DATA + 5132 " WHERE " + Data.RAW_CONTACT_ID + "=?" + 5133 " AND (" + Data.DATA1 + " NOT NULL OR " + 5134 Data.DATA8 + " NOT NULL OR " + 5135 Data.DATA9 + " NOT NULL OR " + 5136 Data.DATA10 + " NOT NULL OR " + // Phonetic name not empty 5137 Organization.TITLE + " NOT NULL)"; 5138 5139 public static final int MIMETYPE = 0; 5140 public static final int IS_PRIMARY = 1; 5141 public static final int DATA1 = 2; 5142 public static final int GIVEN_NAME = 3; // data2 5143 public static final int FAMILY_NAME = 4; // data3 5144 public static final int PREFIX = 5; // data4 5145 public static final int TITLE = 5; // data4 5146 public static final int MIDDLE_NAME = 6; // data5 5147 public static final int SUFFIX = 7; // data6 5148 public static final int PHONETIC_GIVEN_NAME = 8; // data7 5149 public static final int PHONETIC_MIDDLE_NAME = 9; // data8 5150 public static final int ORGANIZATION_PHONETIC_NAME = 9; // data8 5151 public static final int PHONETIC_FAMILY_NAME = 10; // data9 5152 public static final int FULL_NAME_STYLE = 11; // data10 5153 public static final int ORGANIZATION_PHONETIC_NAME_STYLE = 11; // data10 5154 public static final int PHONETIC_NAME_STYLE = 12; // data11 5155 } 5156 5157 /** 5158 * Updates a raw contact display name based on data rows, e.g. structured name, 5159 * organization, email etc. 5160 */ updateRawContactDisplayName(SQLiteDatabase db, long rawContactId)5161 public void updateRawContactDisplayName(SQLiteDatabase db, long rawContactId) { 5162 if (mNameSplitter == null) { 5163 createNameSplitter(); 5164 } 5165 5166 int bestDisplayNameSource = DisplayNameSources.UNDEFINED; 5167 NameSplitter.Name bestName = null; 5168 String bestDisplayName = null; 5169 String bestPhoneticName = null; 5170 int bestPhoneticNameStyle = PhoneticNameStyle.UNDEFINED; 5171 5172 mSelectionArgs1[0] = String.valueOf(rawContactId); 5173 Cursor c = db.rawQuery(RawContactNameQuery.RAW_SQL, mSelectionArgs1); 5174 try { 5175 while (c.moveToNext()) { 5176 int mimeType = c.getInt(RawContactNameQuery.MIMETYPE); 5177 int source = getDisplayNameSourceForMimeTypeId(mimeType); 5178 if (source < bestDisplayNameSource || source == DisplayNameSources.UNDEFINED) { 5179 continue; 5180 } 5181 5182 if (source == bestDisplayNameSource 5183 && c.getInt(RawContactNameQuery.IS_PRIMARY) == 0) { 5184 continue; 5185 } 5186 5187 if (mimeType == getMimeTypeIdForStructuredName()) { 5188 NameSplitter.Name name; 5189 if (bestName != null) { 5190 name = new NameSplitter.Name(); 5191 } else { 5192 name = mName; 5193 name.clear(); 5194 } 5195 name.prefix = c.getString(RawContactNameQuery.PREFIX); 5196 name.givenNames = c.getString(RawContactNameQuery.GIVEN_NAME); 5197 name.middleName = c.getString(RawContactNameQuery.MIDDLE_NAME); 5198 name.familyName = c.getString(RawContactNameQuery.FAMILY_NAME); 5199 name.suffix = c.getString(RawContactNameQuery.SUFFIX); 5200 name.fullNameStyle = c.isNull(RawContactNameQuery.FULL_NAME_STYLE) 5201 ? FullNameStyle.UNDEFINED 5202 : c.getInt(RawContactNameQuery.FULL_NAME_STYLE); 5203 name.phoneticFamilyName = c.getString(RawContactNameQuery.PHONETIC_FAMILY_NAME); 5204 name.phoneticMiddleName = c.getString(RawContactNameQuery.PHONETIC_MIDDLE_NAME); 5205 name.phoneticGivenName = c.getString(RawContactNameQuery.PHONETIC_GIVEN_NAME); 5206 name.phoneticNameStyle = c.isNull(RawContactNameQuery.PHONETIC_NAME_STYLE) 5207 ? PhoneticNameStyle.UNDEFINED 5208 : c.getInt(RawContactNameQuery.PHONETIC_NAME_STYLE); 5209 if (!name.isEmpty()) { 5210 bestDisplayNameSource = source; 5211 bestName = name; 5212 } 5213 } else if (mimeType == getMimeTypeIdForOrganization()) { 5214 mCharArrayBuffer.sizeCopied = 0; 5215 c.copyStringToBuffer(RawContactNameQuery.DATA1, mCharArrayBuffer); 5216 if (mCharArrayBuffer.sizeCopied != 0) { 5217 bestDisplayNameSource = source; 5218 bestDisplayName = new String(mCharArrayBuffer.data, 0, 5219 mCharArrayBuffer.sizeCopied); 5220 bestPhoneticName = c.getString( 5221 RawContactNameQuery.ORGANIZATION_PHONETIC_NAME); 5222 bestPhoneticNameStyle = 5223 c.isNull(RawContactNameQuery.ORGANIZATION_PHONETIC_NAME_STYLE) 5224 ? PhoneticNameStyle.UNDEFINED 5225 : c.getInt(RawContactNameQuery.ORGANIZATION_PHONETIC_NAME_STYLE); 5226 } else { 5227 c.copyStringToBuffer(RawContactNameQuery.TITLE, mCharArrayBuffer); 5228 if (mCharArrayBuffer.sizeCopied != 0) { 5229 bestDisplayNameSource = source; 5230 bestDisplayName = new String(mCharArrayBuffer.data, 0, 5231 mCharArrayBuffer.sizeCopied); 5232 bestPhoneticName = null; 5233 bestPhoneticNameStyle = PhoneticNameStyle.UNDEFINED; 5234 } 5235 } 5236 } else { 5237 // Display name is at DATA1 in all other types. 5238 // This is ensured in the constructor. 5239 5240 mCharArrayBuffer.sizeCopied = 0; 5241 c.copyStringToBuffer(RawContactNameQuery.DATA1, mCharArrayBuffer); 5242 if (mCharArrayBuffer.sizeCopied != 0) { 5243 bestDisplayNameSource = source; 5244 bestDisplayName = new String(mCharArrayBuffer.data, 0, 5245 mCharArrayBuffer.sizeCopied); 5246 bestPhoneticName = null; 5247 bestPhoneticNameStyle = PhoneticNameStyle.UNDEFINED; 5248 } 5249 } 5250 } 5251 5252 } finally { 5253 c.close(); 5254 } 5255 5256 String displayNamePrimary; 5257 String displayNameAlternative; 5258 String sortNamePrimary; 5259 String sortNameAlternative; 5260 String sortKeyPrimary = null; 5261 String sortKeyAlternative = null; 5262 int displayNameStyle = FullNameStyle.UNDEFINED; 5263 5264 if (bestDisplayNameSource == DisplayNameSources.STRUCTURED_NAME) { 5265 displayNameStyle = bestName.fullNameStyle; 5266 if (displayNameStyle == FullNameStyle.CJK 5267 || displayNameStyle == FullNameStyle.UNDEFINED) { 5268 displayNameStyle = mNameSplitter.getAdjustedFullNameStyle(displayNameStyle); 5269 bestName.fullNameStyle = displayNameStyle; 5270 } 5271 5272 displayNamePrimary = mNameSplitter.join(bestName, true, true); 5273 displayNameAlternative = mNameSplitter.join(bestName, false, true); 5274 5275 if (TextUtils.isEmpty(bestName.prefix)) { 5276 sortNamePrimary = displayNamePrimary; 5277 sortNameAlternative = displayNameAlternative; 5278 } else { 5279 sortNamePrimary = mNameSplitter.join(bestName, true, false); 5280 sortNameAlternative = mNameSplitter.join(bestName, false, false); 5281 } 5282 5283 bestPhoneticName = mNameSplitter.joinPhoneticName(bestName); 5284 bestPhoneticNameStyle = bestName.phoneticNameStyle; 5285 } else { 5286 displayNamePrimary = displayNameAlternative = bestDisplayName; 5287 sortNamePrimary = sortNameAlternative = bestDisplayName; 5288 } 5289 5290 if (bestPhoneticName != null) { 5291 if (displayNamePrimary == null) { 5292 displayNamePrimary = bestPhoneticName; 5293 } 5294 if (displayNameAlternative == null) { 5295 displayNameAlternative = bestPhoneticName; 5296 } 5297 // Phonetic names disregard name order so displayNamePrimary and displayNameAlternative 5298 // are the same. 5299 sortKeyPrimary = sortKeyAlternative = bestPhoneticName; 5300 if (bestPhoneticNameStyle == PhoneticNameStyle.UNDEFINED) { 5301 bestPhoneticNameStyle = mNameSplitter.guessPhoneticNameStyle(bestPhoneticName); 5302 } 5303 } else { 5304 bestPhoneticNameStyle = PhoneticNameStyle.UNDEFINED; 5305 if (displayNameStyle == FullNameStyle.UNDEFINED) { 5306 displayNameStyle = mNameSplitter.guessFullNameStyle(bestDisplayName); 5307 if (displayNameStyle == FullNameStyle.UNDEFINED 5308 || displayNameStyle == FullNameStyle.CJK) { 5309 displayNameStyle = mNameSplitter.getAdjustedNameStyleBasedOnPhoneticNameStyle( 5310 displayNameStyle, bestPhoneticNameStyle); 5311 } 5312 displayNameStyle = mNameSplitter.getAdjustedFullNameStyle(displayNameStyle); 5313 } 5314 if (displayNameStyle == FullNameStyle.CHINESE || 5315 displayNameStyle == FullNameStyle.CJK) { 5316 sortKeyPrimary = sortKeyAlternative = sortNamePrimary; 5317 } 5318 } 5319 5320 if (sortKeyPrimary == null) { 5321 sortKeyPrimary = sortNamePrimary; 5322 sortKeyAlternative = sortNameAlternative; 5323 } 5324 5325 String phonebookLabelPrimary = ""; 5326 String phonebookLabelAlternative = ""; 5327 int phonebookBucketPrimary = 0; 5328 int phonebookBucketAlternative = 0; 5329 ContactLocaleUtils localeUtils = ContactLocaleUtils.getInstance(); 5330 5331 if (sortKeyPrimary != null) { 5332 phonebookBucketPrimary = localeUtils.getBucketIndex(sortKeyPrimary); 5333 phonebookLabelPrimary = localeUtils.getBucketLabel(phonebookBucketPrimary); 5334 } 5335 if (sortKeyAlternative != null) { 5336 phonebookBucketAlternative = localeUtils.getBucketIndex(sortKeyAlternative); 5337 phonebookLabelAlternative = localeUtils.getBucketLabel(phonebookBucketAlternative); 5338 } 5339 5340 if (mRawContactDisplayNameUpdate == null) { 5341 mRawContactDisplayNameUpdate = db.compileStatement( 5342 "UPDATE " + Tables.RAW_CONTACTS + 5343 " SET " + 5344 RawContacts.DISPLAY_NAME_SOURCE + "=?," + 5345 RawContacts.DISPLAY_NAME_PRIMARY + "=?," + 5346 RawContacts.DISPLAY_NAME_ALTERNATIVE + "=?," + 5347 RawContacts.PHONETIC_NAME + "=?," + 5348 RawContacts.PHONETIC_NAME_STYLE + "=?," + 5349 RawContacts.SORT_KEY_PRIMARY + "=?," + 5350 RawContactsColumns.PHONEBOOK_LABEL_PRIMARY + "=?," + 5351 RawContactsColumns.PHONEBOOK_BUCKET_PRIMARY + "=?," + 5352 RawContacts.SORT_KEY_ALTERNATIVE + "=?," + 5353 RawContactsColumns.PHONEBOOK_LABEL_ALTERNATIVE + "=?," + 5354 RawContactsColumns.PHONEBOOK_BUCKET_ALTERNATIVE + "=?" + 5355 " WHERE " + RawContacts._ID + "=?"); 5356 } 5357 5358 mRawContactDisplayNameUpdate.bindLong(1, bestDisplayNameSource); 5359 bindString(mRawContactDisplayNameUpdate, 2, displayNamePrimary); 5360 bindString(mRawContactDisplayNameUpdate, 3, displayNameAlternative); 5361 bindString(mRawContactDisplayNameUpdate, 4, bestPhoneticName); 5362 mRawContactDisplayNameUpdate.bindLong(5, bestPhoneticNameStyle); 5363 bindString(mRawContactDisplayNameUpdate, 6, sortKeyPrimary); 5364 bindString(mRawContactDisplayNameUpdate, 7, phonebookLabelPrimary); 5365 mRawContactDisplayNameUpdate.bindLong(8, phonebookBucketPrimary); 5366 bindString(mRawContactDisplayNameUpdate, 9, sortKeyAlternative); 5367 bindString(mRawContactDisplayNameUpdate, 10, phonebookLabelAlternative); 5368 mRawContactDisplayNameUpdate.bindLong(11, phonebookBucketAlternative); 5369 mRawContactDisplayNameUpdate.bindLong(12, rawContactId); 5370 mRawContactDisplayNameUpdate.execute(); 5371 } 5372 5373 /* 5374 * Sets the given dataId record in the "data" table to primary, and resets all data records of 5375 * the same mimetype and under the same contact to not be primary. 5376 * 5377 * @param dataId the id of the data record to be set to primary. Pass -1 to clear the primary 5378 * flag of all data items of this raw contacts 5379 */ setIsPrimary(long rawContactId, long dataId, long mimeTypeId)5380 public void setIsPrimary(long rawContactId, long dataId, long mimeTypeId) { 5381 if (mSetPrimaryStatement == null) { 5382 mSetPrimaryStatement = getWritableDatabase().compileStatement( 5383 "UPDATE " + Tables.DATA + 5384 " SET " + Data.IS_PRIMARY + "=(_id=?)" + 5385 " WHERE " + DataColumns.MIMETYPE_ID + "=?" + 5386 " AND " + Data.RAW_CONTACT_ID + "=?"); 5387 } 5388 mSetPrimaryStatement.bindLong(1, dataId); 5389 mSetPrimaryStatement.bindLong(2, mimeTypeId); 5390 mSetPrimaryStatement.bindLong(3, rawContactId); 5391 mSetPrimaryStatement.execute(); 5392 } 5393 5394 /* 5395 * Clears the super primary of all data items of the given raw contact. does not touch 5396 * other raw contacts of the same joined aggregate 5397 */ clearSuperPrimary(long rawContactId, long mimeTypeId)5398 public void clearSuperPrimary(long rawContactId, long mimeTypeId) { 5399 if (mClearSuperPrimaryStatement == null) { 5400 mClearSuperPrimaryStatement = getWritableDatabase().compileStatement( 5401 "UPDATE " + Tables.DATA + 5402 " SET " + Data.IS_SUPER_PRIMARY + "=0" + 5403 " WHERE " + DataColumns.MIMETYPE_ID + "=?" + 5404 " AND " + Data.RAW_CONTACT_ID + "=?"); 5405 } 5406 mClearSuperPrimaryStatement.bindLong(1, mimeTypeId); 5407 mClearSuperPrimaryStatement.bindLong(2, rawContactId); 5408 mClearSuperPrimaryStatement.execute(); 5409 } 5410 5411 /* 5412 * Sets the given dataId record in the "data" table to "super primary", and resets all data 5413 * records of the same mimetype and under the same aggregate to not be "super primary". 5414 * 5415 * @param dataId the id of the data record to be set to primary. 5416 */ setIsSuperPrimary(long rawContactId, long dataId, long mimeTypeId)5417 public void setIsSuperPrimary(long rawContactId, long dataId, long mimeTypeId) { 5418 if (mSetSuperPrimaryStatement == null) { 5419 mSetSuperPrimaryStatement = getWritableDatabase().compileStatement( 5420 "UPDATE " + Tables.DATA + 5421 " SET " + Data.IS_SUPER_PRIMARY + "=(" + Data._ID + "=?)" + 5422 " WHERE " + DataColumns.MIMETYPE_ID + "=?" + 5423 " AND " + Data.RAW_CONTACT_ID + " IN (" + 5424 "SELECT " + RawContacts._ID + 5425 " FROM " + Tables.RAW_CONTACTS + 5426 " WHERE " + RawContacts.CONTACT_ID + " =(" + 5427 "SELECT " + RawContacts.CONTACT_ID + 5428 " FROM " + Tables.RAW_CONTACTS + 5429 " WHERE " + RawContacts._ID + "=?))"); 5430 } 5431 mSetSuperPrimaryStatement.bindLong(1, dataId); 5432 mSetSuperPrimaryStatement.bindLong(2, mimeTypeId); 5433 mSetSuperPrimaryStatement.bindLong(3, rawContactId); 5434 mSetSuperPrimaryStatement.execute(); 5435 } 5436 5437 /** 5438 * Inserts a record in the {@link Tables#NAME_LOOKUP} table. 5439 */ insertNameLookup(long rawContactId, long dataId, int lookupType, String name)5440 public void insertNameLookup(long rawContactId, long dataId, int lookupType, String name) { 5441 if (TextUtils.isEmpty(name)) { 5442 return; 5443 } 5444 5445 if (mNameLookupInsert == null) { 5446 mNameLookupInsert = getWritableDatabase().compileStatement( 5447 "INSERT OR IGNORE INTO " + Tables.NAME_LOOKUP + "(" 5448 + NameLookupColumns.RAW_CONTACT_ID + "," 5449 + NameLookupColumns.DATA_ID + "," 5450 + NameLookupColumns.NAME_TYPE + "," 5451 + NameLookupColumns.NORMALIZED_NAME 5452 + ") VALUES (?,?,?,?)"); 5453 } 5454 mNameLookupInsert.bindLong(1, rawContactId); 5455 mNameLookupInsert.bindLong(2, dataId); 5456 mNameLookupInsert.bindLong(3, lookupType); 5457 bindString(mNameLookupInsert, 4, name); 5458 mNameLookupInsert.executeInsert(); 5459 } 5460 5461 /** 5462 * Deletes all {@link Tables#NAME_LOOKUP} table rows associated with the specified data element. 5463 */ deleteNameLookup(long dataId)5464 public void deleteNameLookup(long dataId) { 5465 if (mNameLookupDelete == null) { 5466 mNameLookupDelete = getWritableDatabase().compileStatement( 5467 "DELETE FROM " + Tables.NAME_LOOKUP + 5468 " WHERE " + NameLookupColumns.DATA_ID + "=?"); 5469 } 5470 mNameLookupDelete.bindLong(1, dataId); 5471 mNameLookupDelete.execute(); 5472 } 5473 insertNameLookupForEmail(long rawContactId, long dataId, String email)5474 public String insertNameLookupForEmail(long rawContactId, long dataId, String email) { 5475 if (TextUtils.isEmpty(email)) { 5476 return null; 5477 } 5478 5479 String address = extractHandleFromEmailAddress(email); 5480 if (address == null) { 5481 return null; 5482 } 5483 5484 insertNameLookup(rawContactId, dataId, 5485 NameLookupType.EMAIL_BASED_NICKNAME, NameNormalizer.normalize(address)); 5486 return address; 5487 } 5488 5489 /** 5490 * Normalizes the nickname and inserts it in the name lookup table. 5491 */ insertNameLookupForNickname(long rawContactId, long dataId, String nickname)5492 public void insertNameLookupForNickname(long rawContactId, long dataId, String nickname) { 5493 if (TextUtils.isEmpty(nickname)) { 5494 return; 5495 } 5496 5497 insertNameLookup(rawContactId, dataId, 5498 NameLookupType.NICKNAME, NameNormalizer.normalize(nickname)); 5499 } 5500 insertNameLookupForPhoneticName(long rawContactId, long dataId, String familyName, String middleName, String givenName)5501 public void insertNameLookupForPhoneticName(long rawContactId, long dataId, String familyName, 5502 String middleName, String givenName) { 5503 mSb.setLength(0); 5504 if (familyName != null) { 5505 mSb.append(familyName.trim()); 5506 } 5507 if (middleName != null) { 5508 mSb.append(middleName.trim()); 5509 } 5510 if (givenName != null) { 5511 mSb.append(givenName.trim()); 5512 } 5513 5514 if (mSb.length() > 0) { 5515 insertNameLookup(rawContactId, dataId, NameLookupType.NAME_COLLATION_KEY, 5516 NameNormalizer.normalize(mSb.toString())); 5517 } 5518 } 5519 5520 /** 5521 * Performs a query and returns true if any Data item of the raw contact with the given 5522 * id and mimetype is marked as super-primary 5523 */ rawContactHasSuperPrimary(long rawContactId, long mimeTypeId)5524 public boolean rawContactHasSuperPrimary(long rawContactId, long mimeTypeId) { 5525 final Cursor existsCursor = getReadableDatabase().rawQuery( 5526 "SELECT EXISTS(SELECT 1 FROM " + Tables.DATA + 5527 " WHERE " + Data.RAW_CONTACT_ID + "=?" + 5528 " AND " + DataColumns.MIMETYPE_ID + "=?" + 5529 " AND " + Data.IS_SUPER_PRIMARY + "<>0)", 5530 new String[] { String.valueOf(rawContactId), String.valueOf(mimeTypeId) }); 5531 try { 5532 if (!existsCursor.moveToFirst()) throw new IllegalStateException(); 5533 return existsCursor.getInt(0) != 0; 5534 } finally { 5535 existsCursor.close(); 5536 } 5537 } 5538 getCurrentCountryIso()5539 public String getCurrentCountryIso() { 5540 return mCountryMonitor.getCountryIso(); 5541 } 5542 5543 @NeededForTesting setUseStrictPhoneNumberComparisonForTest(boolean useStrict)5544 /* package */ void setUseStrictPhoneNumberComparisonForTest(boolean useStrict) { 5545 mUseStrictPhoneNumberComparison = useStrict; 5546 } 5547 5548 @NeededForTesting getUseStrictPhoneNumberComparisonForTest()5549 /* package */ boolean getUseStrictPhoneNumberComparisonForTest() { 5550 return mUseStrictPhoneNumberComparison; 5551 } 5552 5553 @NeededForTesting querySearchIndexContentForTest(long contactId)5554 /* package */ String querySearchIndexContentForTest(long contactId) { 5555 return DatabaseUtils.stringForQuery(getReadableDatabase(), 5556 "SELECT " + SearchIndexColumns.CONTENT + 5557 " FROM " + Tables.SEARCH_INDEX + 5558 " WHERE " + SearchIndexColumns.CONTACT_ID + "=CAST(? AS int)", 5559 new String[] { String.valueOf(contactId) }); 5560 } 5561 5562 @NeededForTesting querySearchIndexTokensForTest(long contactId)5563 /* package */ String querySearchIndexTokensForTest(long contactId) { 5564 return DatabaseUtils.stringForQuery(getReadableDatabase(), 5565 "SELECT " + SearchIndexColumns.TOKENS + 5566 " FROM " + Tables.SEARCH_INDEX + 5567 " WHERE " + SearchIndexColumns.CONTACT_ID + "=CAST(? AS int)", 5568 new String[] { String.valueOf(contactId) }); 5569 } 5570 } 5571