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