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