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