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