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