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