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