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