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