ContactsDatabaseHelper.java revision ae32283e7fc5b749df96523d8bb343b9068b65ba 
1/*
2 * Copyright (C) 2009 The Android Open Source Project
3 *
4 * Licensed under the Apache License, Version 2.0 (the "License");
5 * you may not use this file except in compliance with the License.
6 * You may obtain a copy of the License at
7 *
8 *      http://www.apache.org/licenses/LICENSE-2.0
9 *
10 * Unless required by applicable law or agreed to in writing, software
11 * distributed under the License is distributed on an "AS IS" BASIS,
12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 * See the License for the specific language governing permissions and
14 * limitations under the License
15 */
16
17package com.android.providers.contacts;
18
19import com.android.common.content.SyncStateContentProviderHelper;
20import com.android.providers.contacts.aggregation.util.CommonNicknameCache;
21import com.android.providers.contacts.util.NeededForTesting;
22import com.google.android.collect.Sets;
23
24import android.content.ContentResolver;
25import android.content.ContentValues;
26import android.content.Context;
27import android.content.pm.ApplicationInfo;
28import android.content.pm.PackageManager;
29import android.content.pm.PackageManager.NameNotFoundException;
30import android.content.res.Resources;
31import android.database.CharArrayBuffer;
32import android.database.Cursor;
33import android.database.DatabaseUtils;
34import android.database.SQLException;
35import android.database.sqlite.SQLiteConstraintException;
36import android.database.sqlite.SQLiteDatabase;
37import android.database.sqlite.SQLiteDoneException;
38import android.database.sqlite.SQLiteException;
39import android.database.sqlite.SQLiteOpenHelper;
40import android.database.sqlite.SQLiteQueryBuilder;
41import android.database.sqlite.SQLiteStatement;
42import android.location.CountryDetector;
43import android.net.Uri;
44import android.os.Binder;
45import android.os.Bundle;
46import android.os.SystemClock;
47import android.provider.BaseColumns;
48import android.provider.CallLog.Calls;
49import android.provider.ContactsContract;
50import android.provider.ContactsContract.AggregationExceptions;
51import android.provider.ContactsContract.CommonDataKinds.Email;
52import android.provider.ContactsContract.CommonDataKinds.GroupMembership;
53import android.provider.ContactsContract.CommonDataKinds.Im;
54import android.provider.ContactsContract.CommonDataKinds.Nickname;
55import android.provider.ContactsContract.CommonDataKinds.Organization;
56import android.provider.ContactsContract.CommonDataKinds.Phone;
57import android.provider.ContactsContract.CommonDataKinds.SipAddress;
58import android.provider.ContactsContract.CommonDataKinds.StructuredName;
59import android.provider.ContactsContract.CommonDataKinds.StructuredPostal;
60import android.provider.ContactsContract.Contacts;
61import android.provider.ContactsContract.Contacts.Photo;
62import android.provider.ContactsContract.Data;
63import android.provider.ContactsContract.Directory;
64import android.provider.ContactsContract.DisplayNameSources;
65import android.provider.ContactsContract.DisplayPhoto;
66import android.provider.ContactsContract.FullNameStyle;
67import android.provider.ContactsContract.Groups;
68import android.provider.ContactsContract.PhoneticNameStyle;
69import android.provider.ContactsContract.PhotoFiles;
70import android.provider.ContactsContract.RawContacts;
71import android.provider.ContactsContract.Settings;
72import android.provider.ContactsContract.StatusUpdates;
73import android.provider.ContactsContract.StreamItemPhotos;
74import android.provider.ContactsContract.StreamItems;
75import android.provider.VoicemailContract;
76import android.provider.VoicemailContract.Voicemails;
77import android.telephony.PhoneNumberUtils;
78import android.text.TextUtils;
79import android.text.util.Rfc822Token;
80import android.text.util.Rfc822Tokenizer;
81import android.util.Log;
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 = 704;
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 (upgradeViewsAndTriggers) {
2379            createContactsViews(db);
2380            createGroupsView(db);
2381            createContactsTriggers(db);
2382            createContactsIndexes(db);
2383            updateSqliteStats(db);
2384            upgradeLegacyApiSupport = true;
2385        }
2386
2387        if (upgradeLegacyApiSupport) {
2388            LegacyApiSupport.createViews(db);
2389        }
2390
2391        if (upgradeNameLookup) {
2392            rebuildNameLookup(db);
2393        }
2394
2395        if (upgradeSearchIndex) {
2396            createSearchIndexTable(db);
2397            setProperty(db, SearchIndexManager.PROPERTY_SEARCH_INDEX_VERSION, "0");
2398        }
2399
2400        if (rescanDirectories) {
2401            // Force the next ContactDirectoryManager.scanAllPackages() to rescan all packages.
2402            // (It's called from the BACKGROUND_TASK_UPDATE_ACCOUNTS background task.)
2403            setProperty(db, DbProperties.DIRECTORY_SCAN_COMPLETE, "0");
2404        }
2405
2406        if (oldVersion != newVersion) {
2407            throw new IllegalStateException(
2408                    "error upgrading the database to version " + newVersion);
2409        }
2410    }
2411
2412    private void upgradeToVersion202(SQLiteDatabase db) {
2413        db.execSQL(
2414                "ALTER TABLE " + Tables.PHONE_LOOKUP +
2415                " ADD " + PhoneLookupColumns.MIN_MATCH + " TEXT;");
2416
2417        db.execSQL("CREATE INDEX phone_lookup_min_match_index ON " + Tables.PHONE_LOOKUP + " (" +
2418                PhoneLookupColumns.MIN_MATCH + "," +
2419                PhoneLookupColumns.RAW_CONTACT_ID + "," +
2420                PhoneLookupColumns.DATA_ID +
2421        ");");
2422
2423        updateIndexStats(db, Tables.PHONE_LOOKUP,
2424                "phone_lookup_min_match_index", "10000 2 2 1");
2425
2426        SQLiteStatement update = db.compileStatement(
2427                "UPDATE " + Tables.PHONE_LOOKUP +
2428                " SET " + PhoneLookupColumns.MIN_MATCH + "=?" +
2429                " WHERE " + PhoneLookupColumns.DATA_ID + "=?");
2430
2431        // Populate the new column
2432        Cursor c = db.query(Tables.PHONE_LOOKUP + " JOIN " + Tables.DATA +
2433                " ON (" + PhoneLookupColumns.DATA_ID + "=" + DataColumns.CONCRETE_ID + ")",
2434                new String[]{Data._ID, Phone.NUMBER}, null, null, null, null, null);
2435        try {
2436            while (c.moveToNext()) {
2437                long dataId = c.getLong(0);
2438                String number = c.getString(1);
2439                if (!TextUtils.isEmpty(number)) {
2440                    update.bindString(1, PhoneNumberUtils.toCallerIDMinMatch(number));
2441                    update.bindLong(2, dataId);
2442                    update.execute();
2443                }
2444            }
2445        } finally {
2446            c.close();
2447        }
2448    }
2449
2450    private void upgradeToVersion203(SQLiteDatabase db) {
2451        // Garbage-collect first. A bug in Eclair was sometimes leaving
2452        // raw_contacts in the database that no longer had contacts associated
2453        // with them.  To avoid failures during this database upgrade, drop
2454        // the orphaned raw_contacts.
2455        db.execSQL(
2456                "DELETE FROM raw_contacts" +
2457                " WHERE contact_id NOT NULL" +
2458                " AND contact_id NOT IN (SELECT _id FROM contacts)");
2459
2460        db.execSQL(
2461                "ALTER TABLE " + Tables.CONTACTS +
2462                " ADD " + Contacts.NAME_RAW_CONTACT_ID + " INTEGER REFERENCES raw_contacts(_id)");
2463        db.execSQL(
2464                "ALTER TABLE " + Tables.RAW_CONTACTS +
2465                " ADD contact_in_visible_group INTEGER NOT NULL DEFAULT 0");
2466
2467        // For each Contact, find the RawContact that contributed the display name
2468        db.execSQL(
2469                "UPDATE " + Tables.CONTACTS +
2470                " SET " + Contacts.NAME_RAW_CONTACT_ID + "=(" +
2471                        " SELECT " + RawContacts._ID +
2472                        " FROM " + Tables.RAW_CONTACTS +
2473                        " WHERE " + RawContacts.CONTACT_ID + "=" + ContactsColumns.CONCRETE_ID +
2474                        " AND " + RawContactsColumns.CONCRETE_DISPLAY_NAME + "=" +
2475                                Tables.CONTACTS + "." + Contacts.DISPLAY_NAME +
2476                        " ORDER BY " + RawContacts._ID +
2477                        " LIMIT 1)"
2478        );
2479
2480        db.execSQL("CREATE INDEX contacts_name_raw_contact_id_index ON " + Tables.CONTACTS + " (" +
2481                Contacts.NAME_RAW_CONTACT_ID +
2482        ");");
2483
2484        // If for some unknown reason we missed some names, let's make sure there are
2485        // no contacts without a name, picking a raw contact "at random".
2486        db.execSQL(
2487                "UPDATE " + Tables.CONTACTS +
2488                " SET " + Contacts.NAME_RAW_CONTACT_ID + "=(" +
2489                        " SELECT " + RawContacts._ID +
2490                        " FROM " + Tables.RAW_CONTACTS +
2491                        " WHERE " + RawContacts.CONTACT_ID + "=" + ContactsColumns.CONCRETE_ID +
2492                        " ORDER BY " + RawContacts._ID +
2493                        " LIMIT 1)" +
2494                " WHERE " + Contacts.NAME_RAW_CONTACT_ID + " IS NULL"
2495        );
2496
2497        // Wipe out DISPLAY_NAME on the Contacts table as it is no longer in use.
2498        db.execSQL(
2499                "UPDATE " + Tables.CONTACTS +
2500                " SET " + Contacts.DISPLAY_NAME + "=NULL"
2501        );
2502
2503        // Copy the IN_VISIBLE_GROUP flag down to all raw contacts to allow
2504        // indexing on (display_name, in_visible_group)
2505        db.execSQL(
2506                "UPDATE " + Tables.RAW_CONTACTS +
2507                " SET contact_in_visible_group=(" +
2508                        "SELECT " + Contacts.IN_VISIBLE_GROUP +
2509                        " FROM " + Tables.CONTACTS +
2510                        " WHERE " + Contacts._ID + "=" + RawContacts.CONTACT_ID + ")" +
2511                " WHERE " + RawContacts.CONTACT_ID + " NOT NULL"
2512        );
2513
2514        db.execSQL("CREATE INDEX raw_contact_sort_key1_index ON " + Tables.RAW_CONTACTS + " (" +
2515                "contact_in_visible_group" + "," +
2516                RawContactsColumns.DISPLAY_NAME + " COLLATE LOCALIZED ASC" +
2517        ");");
2518
2519        db.execSQL("DROP INDEX contacts_visible_index");
2520        db.execSQL("CREATE INDEX contacts_visible_index ON " + Tables.CONTACTS + " (" +
2521                Contacts.IN_VISIBLE_GROUP +
2522        ");");
2523    }
2524
2525    private void upgradeToVersion205(SQLiteDatabase db) {
2526        db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS
2527                + " ADD " + RawContacts.DISPLAY_NAME_ALTERNATIVE + " TEXT;");
2528        db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS
2529                + " ADD " + RawContacts.PHONETIC_NAME + " TEXT;");
2530        db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS
2531                + " ADD " + RawContacts.PHONETIC_NAME_STYLE + " INTEGER;");
2532        db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS
2533                + " ADD " + RawContacts.SORT_KEY_PRIMARY
2534                + " TEXT COLLATE " + ContactsProvider2.PHONEBOOK_COLLATOR_NAME + ";");
2535        db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS
2536                + " ADD " + RawContacts.SORT_KEY_ALTERNATIVE
2537                + " TEXT COLLATE " + ContactsProvider2.PHONEBOOK_COLLATOR_NAME + ";");
2538
2539        final Locale locale = Locale.getDefault();
2540
2541        NameSplitter splitter = createNameSplitter();
2542
2543        SQLiteStatement rawContactUpdate = db.compileStatement(
2544                "UPDATE " + Tables.RAW_CONTACTS +
2545                " SET " +
2546                        RawContacts.DISPLAY_NAME_PRIMARY + "=?," +
2547                        RawContacts.DISPLAY_NAME_ALTERNATIVE + "=?," +
2548                        RawContacts.PHONETIC_NAME + "=?," +
2549                        RawContacts.PHONETIC_NAME_STYLE + "=?," +
2550                        RawContacts.SORT_KEY_PRIMARY + "=?," +
2551                        RawContacts.SORT_KEY_ALTERNATIVE + "=?" +
2552                " WHERE " + RawContacts._ID + "=?");
2553
2554        upgradeStructuredNamesToVersion205(db, rawContactUpdate, splitter);
2555        upgradeOrganizationsToVersion205(db, rawContactUpdate, splitter);
2556
2557        db.execSQL("DROP INDEX raw_contact_sort_key1_index");
2558        db.execSQL("CREATE INDEX raw_contact_sort_key1_index ON " + Tables.RAW_CONTACTS + " (" +
2559                "contact_in_visible_group" + "," +
2560                RawContacts.SORT_KEY_PRIMARY +
2561        ");");
2562
2563        db.execSQL("CREATE INDEX raw_contact_sort_key2_index ON " + Tables.RAW_CONTACTS + " (" +
2564                "contact_in_visible_group" + "," +
2565                RawContacts.SORT_KEY_ALTERNATIVE +
2566        ");");
2567    }
2568
2569    private interface StructName205Query {
2570        String TABLE = Tables.DATA_JOIN_RAW_CONTACTS;
2571
2572        String COLUMNS[] = {
2573                DataColumns.CONCRETE_ID,
2574                Data.RAW_CONTACT_ID,
2575                RawContacts.DISPLAY_NAME_SOURCE,
2576                RawContacts.DISPLAY_NAME_PRIMARY,
2577                StructuredName.PREFIX,
2578                StructuredName.GIVEN_NAME,
2579                StructuredName.MIDDLE_NAME,
2580                StructuredName.FAMILY_NAME,
2581                StructuredName.SUFFIX,
2582                StructuredName.PHONETIC_FAMILY_NAME,
2583                StructuredName.PHONETIC_MIDDLE_NAME,
2584                StructuredName.PHONETIC_GIVEN_NAME,
2585        };
2586
2587        int ID = 0;
2588        int RAW_CONTACT_ID = 1;
2589        int DISPLAY_NAME_SOURCE = 2;
2590        int DISPLAY_NAME = 3;
2591        int PREFIX = 4;
2592        int GIVEN_NAME = 5;
2593        int MIDDLE_NAME = 6;
2594        int FAMILY_NAME = 7;
2595        int SUFFIX = 8;
2596        int PHONETIC_FAMILY_NAME = 9;
2597        int PHONETIC_MIDDLE_NAME = 10;
2598        int PHONETIC_GIVEN_NAME = 11;
2599    }
2600
2601    private void upgradeStructuredNamesToVersion205(SQLiteDatabase db,
2602            SQLiteStatement rawContactUpdate, NameSplitter splitter) {
2603
2604        // Process structured names to detect the style of the full name and phonetic name
2605
2606        long mMimeType;
2607        try {
2608            mMimeType = DatabaseUtils.longForQuery(db,
2609                    "SELECT " + MimetypesColumns._ID +
2610                    " FROM " + Tables.MIMETYPES +
2611                    " WHERE " + MimetypesColumns.MIMETYPE
2612                            + "='" + StructuredName.CONTENT_ITEM_TYPE + "'", null);
2613        } catch (SQLiteDoneException e) {
2614            // No structured names in the database
2615            return;
2616        }
2617
2618        SQLiteStatement structuredNameUpdate = db.compileStatement(
2619                "UPDATE " + Tables.DATA +
2620                " SET " +
2621                        StructuredName.FULL_NAME_STYLE + "=?," +
2622                        StructuredName.DISPLAY_NAME + "=?," +
2623                        StructuredName.PHONETIC_NAME_STYLE + "=?" +
2624                " WHERE " + Data._ID + "=?");
2625
2626        NameSplitter.Name name = new NameSplitter.Name();
2627        StringBuilder sb = new StringBuilder();
2628        Cursor cursor = db.query(StructName205Query.TABLE,
2629                StructName205Query.COLUMNS,
2630                DataColumns.MIMETYPE_ID + "=" + mMimeType, null, null, null, null);
2631        try {
2632            while (cursor.moveToNext()) {
2633                long dataId = cursor.getLong(StructName205Query.ID);
2634                long rawContactId = cursor.getLong(StructName205Query.RAW_CONTACT_ID);
2635                int displayNameSource = cursor.getInt(StructName205Query.DISPLAY_NAME_SOURCE);
2636                String displayName = cursor.getString(StructName205Query.DISPLAY_NAME);
2637
2638                name.clear();
2639                name.prefix = cursor.getString(StructName205Query.PREFIX);
2640                name.givenNames = cursor.getString(StructName205Query.GIVEN_NAME);
2641                name.middleName = cursor.getString(StructName205Query.MIDDLE_NAME);
2642                name.familyName = cursor.getString(StructName205Query.FAMILY_NAME);
2643                name.suffix = cursor.getString(StructName205Query.SUFFIX);
2644                name.phoneticFamilyName = cursor.getString(StructName205Query.PHONETIC_FAMILY_NAME);
2645                name.phoneticMiddleName = cursor.getString(StructName205Query.PHONETIC_MIDDLE_NAME);
2646                name.phoneticGivenName = cursor.getString(StructName205Query.PHONETIC_GIVEN_NAME);
2647
2648                upgradeNameToVersion205(dataId, rawContactId, displayNameSource, displayName, name,
2649                        structuredNameUpdate, rawContactUpdate, splitter, sb);
2650            }
2651        } finally {
2652            cursor.close();
2653        }
2654    }
2655
2656    private void upgradeNameToVersion205(long dataId, long rawContactId, int displayNameSource,
2657            String currentDisplayName, NameSplitter.Name name,
2658            SQLiteStatement structuredNameUpdate, SQLiteStatement rawContactUpdate,
2659            NameSplitter splitter, StringBuilder sb) {
2660
2661        splitter.guessNameStyle(name);
2662        int unadjustedFullNameStyle = name.fullNameStyle;
2663        name.fullNameStyle = splitter.getAdjustedFullNameStyle(name.fullNameStyle);
2664        String displayName = splitter.join(name, true, true);
2665
2666        // Don't update database with the adjusted fullNameStyle as it is locale
2667        // related
2668        structuredNameUpdate.bindLong(1, unadjustedFullNameStyle);
2669        DatabaseUtils.bindObjectToProgram(structuredNameUpdate, 2, displayName);
2670        structuredNameUpdate.bindLong(3, name.phoneticNameStyle);
2671        structuredNameUpdate.bindLong(4, dataId);
2672        structuredNameUpdate.execute();
2673
2674        if (displayNameSource == DisplayNameSources.STRUCTURED_NAME) {
2675            String displayNameAlternative = splitter.join(name, false, false);
2676            String phoneticName = splitter.joinPhoneticName(name);
2677            String sortKey = null;
2678            String sortKeyAlternative = null;
2679
2680            if (phoneticName != null) {
2681                sortKey = sortKeyAlternative = phoneticName;
2682            } else if (name.fullNameStyle == FullNameStyle.CHINESE ||
2683                    name.fullNameStyle == FullNameStyle.CJK) {
2684                sortKey = sortKeyAlternative = ContactLocaleUtils.getIntance()
2685                        .getSortKey(displayName, name.fullNameStyle);
2686            }
2687
2688            if (sortKey == null) {
2689                sortKey = displayName;
2690                sortKeyAlternative = displayNameAlternative;
2691            }
2692
2693            updateRawContact205(rawContactUpdate, rawContactId, displayName,
2694                    displayNameAlternative, name.phoneticNameStyle, phoneticName, sortKey,
2695                    sortKeyAlternative);
2696        }
2697    }
2698
2699    private interface Organization205Query {
2700        String TABLE = Tables.DATA_JOIN_RAW_CONTACTS;
2701
2702        String COLUMNS[] = {
2703                DataColumns.CONCRETE_ID,
2704                Data.RAW_CONTACT_ID,
2705                Organization.COMPANY,
2706                Organization.PHONETIC_NAME,
2707        };
2708
2709        int ID = 0;
2710        int RAW_CONTACT_ID = 1;
2711        int COMPANY = 2;
2712        int PHONETIC_NAME = 3;
2713    }
2714
2715    private void upgradeOrganizationsToVersion205(SQLiteDatabase db,
2716            SQLiteStatement rawContactUpdate, NameSplitter splitter) {
2717        final long mimeType = lookupMimeTypeId(db, Organization.CONTENT_ITEM_TYPE);
2718
2719        SQLiteStatement organizationUpdate = db.compileStatement(
2720                "UPDATE " + Tables.DATA +
2721                " SET " +
2722                        Organization.PHONETIC_NAME_STYLE + "=?" +
2723                " WHERE " + Data._ID + "=?");
2724
2725        Cursor cursor = db.query(Organization205Query.TABLE, Organization205Query.COLUMNS,
2726                DataColumns.MIMETYPE_ID + "=" + mimeType + " AND "
2727                        + RawContacts.DISPLAY_NAME_SOURCE + "=" + DisplayNameSources.ORGANIZATION,
2728                null, null, null, null);
2729        try {
2730            while (cursor.moveToNext()) {
2731                long dataId = cursor.getLong(Organization205Query.ID);
2732                long rawContactId = cursor.getLong(Organization205Query.RAW_CONTACT_ID);
2733                String company = cursor.getString(Organization205Query.COMPANY);
2734                String phoneticName = cursor.getString(Organization205Query.PHONETIC_NAME);
2735
2736                int phoneticNameStyle = splitter.guessPhoneticNameStyle(phoneticName);
2737
2738                organizationUpdate.bindLong(1, phoneticNameStyle);
2739                organizationUpdate.bindLong(2, dataId);
2740                organizationUpdate.execute();
2741
2742                String sortKey = null;
2743                if (phoneticName == null && company != null) {
2744                    int nameStyle = splitter.guessFullNameStyle(company);
2745                    nameStyle = splitter.getAdjustedFullNameStyle(nameStyle);
2746                    if (nameStyle == FullNameStyle.CHINESE ||
2747                            nameStyle == FullNameStyle.CJK ) {
2748                        sortKey = ContactLocaleUtils.getIntance()
2749                                .getSortKey(company, nameStyle);
2750                    }
2751                }
2752
2753                if (sortKey == null) {
2754                    sortKey = company;
2755                }
2756
2757                updateRawContact205(rawContactUpdate, rawContactId, company,
2758                        company, phoneticNameStyle, phoneticName, sortKey, sortKey);
2759            }
2760        } finally {
2761            cursor.close();
2762        }
2763    }
2764
2765    private void updateRawContact205(SQLiteStatement rawContactUpdate, long rawContactId,
2766            String displayName, String displayNameAlternative, int phoneticNameStyle,
2767            String phoneticName, String sortKeyPrimary, String sortKeyAlternative) {
2768        bindString(rawContactUpdate, 1, displayName);
2769        bindString(rawContactUpdate, 2, displayNameAlternative);
2770        bindString(rawContactUpdate, 3, phoneticName);
2771        rawContactUpdate.bindLong(4, phoneticNameStyle);
2772        bindString(rawContactUpdate, 5, sortKeyPrimary);
2773        bindString(rawContactUpdate, 6, sortKeyAlternative);
2774        rawContactUpdate.bindLong(7, rawContactId);
2775        rawContactUpdate.execute();
2776    }
2777
2778    private void upgrateToVersion206(SQLiteDatabase db) {
2779        db.execSQL("ALTER TABLE " + Tables.RAW_CONTACTS
2780                + " ADD " + RawContacts.NAME_VERIFIED + " INTEGER NOT NULL DEFAULT 0;");
2781    }
2782
2783    /**
2784     * Fix for the bug where name lookup records for organizations would get removed by
2785     * unrelated updates of the data rows.
2786     */
2787    private void upgradeToVersion300(SQLiteDatabase db) {
2788        // No longer needed
2789    }
2790
2791    private static final class Upgrade303Query {
2792        public static final String TABLE = Tables.DATA;
2793
2794        public static final String SELECTION =
2795                DataColumns.MIMETYPE_ID + "=?" +
2796                    " AND " + Data._ID + " NOT IN " +
2797                    "(SELECT " + NameLookupColumns.DATA_ID + " FROM " + Tables.NAME_LOOKUP + ")" +
2798                    " AND " + Data.DATA1 + " NOT NULL";
2799
2800        public static final String COLUMNS[] = {
2801                Data._ID,
2802                Data.RAW_CONTACT_ID,
2803                Data.DATA1,
2804        };
2805
2806        public static final int ID = 0;
2807        public static final int RAW_CONTACT_ID = 1;
2808        public static final int DATA1 = 2;
2809    }
2810
2811    /**
2812     * The {@link ContactsProvider2#update} method was deleting name lookup for new
2813     * emails during the sync.  We need to restore the lost name lookup rows.
2814     */
2815    private void upgradeEmailToVersion303(SQLiteDatabase db) {
2816        final long mimeTypeId = lookupMimeTypeId(db, Email.CONTENT_ITEM_TYPE);
2817        if (mimeTypeId == -1) {
2818            return;
2819        }
2820
2821        ContentValues values = new ContentValues();
2822
2823        // Find all data rows with the mime type "email" that are missing name lookup
2824        Cursor cursor = db.query(Upgrade303Query.TABLE, Upgrade303Query.COLUMNS,
2825                Upgrade303Query.SELECTION, new String[] {String.valueOf(mimeTypeId)},
2826                null, null, null);
2827        try {
2828            while (cursor.moveToNext()) {
2829                long dataId = cursor.getLong(Upgrade303Query.ID);
2830                long rawContactId = cursor.getLong(Upgrade303Query.RAW_CONTACT_ID);
2831                String value = cursor.getString(Upgrade303Query.DATA1);
2832                value = extractHandleFromEmailAddress(value);
2833
2834                if (value != null) {
2835                    values.put(NameLookupColumns.DATA_ID, dataId);
2836                    values.put(NameLookupColumns.RAW_CONTACT_ID, rawContactId);
2837                    values.put(NameLookupColumns.NAME_TYPE, NameLookupType.EMAIL_BASED_NICKNAME);
2838                    values.put(NameLookupColumns.NORMALIZED_NAME, NameNormalizer.normalize(value));
2839                    db.insert(Tables.NAME_LOOKUP, null, values);
2840                }
2841            }
2842        } finally {
2843            cursor.close();
2844        }
2845    }
2846
2847    /**
2848     * The {@link ContactsProvider2#update} method was deleting name lookup for new
2849     * nicknames during the sync.  We need to restore the lost name lookup rows.
2850     */
2851    private void upgradeNicknameToVersion303(SQLiteDatabase db) {
2852        final long mimeTypeId = lookupMimeTypeId(db, Nickname.CONTENT_ITEM_TYPE);
2853        if (mimeTypeId == -1) {
2854            return;
2855        }
2856
2857        ContentValues values = new ContentValues();
2858
2859        // Find all data rows with the mime type "nickname" that are missing name lookup
2860        Cursor cursor = db.query(Upgrade303Query.TABLE, Upgrade303Query.COLUMNS,
2861                Upgrade303Query.SELECTION, new String[] {String.valueOf(mimeTypeId)},
2862                null, null, null);
2863        try {
2864            while (cursor.moveToNext()) {
2865                long dataId = cursor.getLong(Upgrade303Query.ID);
2866                long rawContactId = cursor.getLong(Upgrade303Query.RAW_CONTACT_ID);
2867                String value = cursor.getString(Upgrade303Query.DATA1);
2868
2869                values.put(NameLookupColumns.DATA_ID, dataId);
2870                values.put(NameLookupColumns.RAW_CONTACT_ID, rawContactId);
2871                values.put(NameLookupColumns.NAME_TYPE, NameLookupType.NICKNAME);
2872                values.put(NameLookupColumns.NORMALIZED_NAME, NameNormalizer.normalize(value));
2873                db.insert(Tables.NAME_LOOKUP, null, values);
2874            }
2875        } finally {
2876            cursor.close();
2877        }
2878    }
2879
2880    private void upgradeToVersion304(SQLiteDatabase db) {
2881        // Mimetype table requires an index on mime type
2882        db.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS mime_type ON " + Tables.MIMETYPES + " (" +
2883                MimetypesColumns.MIMETYPE +
2884        ");");
2885    }
2886
2887    private void upgradeToVersion306(SQLiteDatabase db) {
2888        // Fix invalid lookup that was used for Exchange contacts (it was not escaped)
2889        // It happened when a new contact was created AND synchronized
2890        final StringBuilder lookupKeyBuilder = new StringBuilder();
2891        final SQLiteStatement updateStatement = db.compileStatement(
2892                "UPDATE contacts " +
2893                "SET lookup=? " +
2894                "WHERE _id=?");
2895        final Cursor contactIdCursor = db.rawQuery(
2896                "SELECT DISTINCT contact_id " +
2897                "FROM raw_contacts " +
2898                "WHERE deleted=0 AND account_type='com.android.exchange'",
2899                null);
2900        try {
2901            while (contactIdCursor.moveToNext()) {
2902                final long contactId = contactIdCursor.getLong(0);
2903                lookupKeyBuilder.setLength(0);
2904                final Cursor c = db.rawQuery(
2905                        "SELECT account_type, account_name, _id, sourceid, display_name " +
2906                        "FROM raw_contacts " +
2907                        "WHERE contact_id=? " +
2908                        "ORDER BY _id",
2909                        new String[] { String.valueOf(contactId) });
2910                try {
2911                    while (c.moveToNext()) {
2912                        ContactLookupKey.appendToLookupKey(lookupKeyBuilder,
2913                                c.getString(0),
2914                                c.getString(1),
2915                                c.getLong(2),
2916                                c.getString(3),
2917                                c.getString(4));
2918                    }
2919                } finally {
2920                    c.close();
2921                }
2922
2923                if (lookupKeyBuilder.length() == 0) {
2924                    updateStatement.bindNull(1);
2925                } else {
2926                    updateStatement.bindString(1, Uri.encode(lookupKeyBuilder.toString()));
2927                }
2928                updateStatement.bindLong(2, contactId);
2929
2930                updateStatement.execute();
2931            }
2932        } finally {
2933            updateStatement.close();
2934            contactIdCursor.close();
2935        }
2936    }
2937
2938    private void upgradeToVersion307(SQLiteDatabase db) {
2939        db.execSQL("CREATE TABLE properties (" +
2940                "property_key TEXT PRIMARY_KEY, " +
2941                "property_value TEXT" +
2942        ");");
2943    }
2944
2945    private void upgradeToVersion308(SQLiteDatabase db) {
2946        db.execSQL("CREATE TABLE accounts (" +
2947                "account_name TEXT, " +
2948                "account_type TEXT " +
2949        ");");
2950
2951        db.execSQL("INSERT INTO accounts " +
2952                "SELECT DISTINCT account_name, account_type FROM raw_contacts");
2953    }
2954
2955    private void upgradeToVersion400(SQLiteDatabase db) {
2956        db.execSQL("ALTER TABLE " + Tables.GROUPS
2957                + " ADD " + Groups.FAVORITES + " INTEGER NOT NULL DEFAULT 0;");
2958        db.execSQL("ALTER TABLE " + Tables.GROUPS
2959                + " ADD " + Groups.AUTO_ADD + " INTEGER NOT NULL DEFAULT 0;");
2960    }
2961
2962    private void upgradeToVersion353(SQLiteDatabase db) {
2963        db.execSQL("DELETE FROM contacts " +
2964                "WHERE NOT EXISTS (SELECT 1 FROM raw_contacts WHERE contact_id=contacts._id)");
2965    }
2966
2967    private void rebuildNameLookup(SQLiteDatabase db) {
2968        db.execSQL("DROP INDEX IF EXISTS name_lookup_index");
2969        insertNameLookup(db);
2970        createContactsIndexes(db);
2971    }
2972
2973    /**
2974     * Regenerates all locale-sensitive data: nickname_lookup, name_lookup and sort keys.
2975     */
2976    public void setLocale(ContactsProvider2 provider, Locale locale) {
2977        Log.i(TAG, "Switching to locale " + locale);
2978
2979        final long start = SystemClock.elapsedRealtime();
2980        SQLiteDatabase db = getWritableDatabase();
2981        db.setLocale(locale);
2982        db.beginTransaction();
2983        try {
2984            db.execSQL("DROP INDEX raw_contact_sort_key1_index");
2985            db.execSQL("DROP INDEX raw_contact_sort_key2_index");
2986            db.execSQL("DROP INDEX IF EXISTS name_lookup_index");
2987
2988            loadNicknameLookupTable(db);
2989            insertNameLookup(db);
2990            rebuildSortKeys(db, provider);
2991            createContactsIndexes(db);
2992            db.setTransactionSuccessful();
2993        } finally {
2994            db.endTransaction();
2995        }
2996
2997        Log.i(TAG, "Locale change completed in " + (SystemClock.elapsedRealtime() - start) + "ms");
2998    }
2999
3000    /**
3001     * Regenerates sort keys for all contacts.
3002     */
3003    private void rebuildSortKeys(SQLiteDatabase db, ContactsProvider2 provider) {
3004        Cursor cursor = db.query(Tables.RAW_CONTACTS, new String[]{RawContacts._ID},
3005                null, null, null, null, null);
3006        try {
3007            while (cursor.moveToNext()) {
3008                long rawContactId = cursor.getLong(0);
3009                updateRawContactDisplayName(db, rawContactId);
3010            }
3011        } finally {
3012            cursor.close();
3013        }
3014    }
3015
3016    private void insertNameLookup(SQLiteDatabase db) {
3017        db.execSQL("DELETE FROM " + Tables.NAME_LOOKUP);
3018
3019        SQLiteStatement nameLookupInsert = db.compileStatement(
3020                "INSERT OR IGNORE INTO " + Tables.NAME_LOOKUP + "("
3021                        + NameLookupColumns.RAW_CONTACT_ID + ","
3022                        + NameLookupColumns.DATA_ID + ","
3023                        + NameLookupColumns.NAME_TYPE + ","
3024                        + NameLookupColumns.NORMALIZED_NAME +
3025                ") VALUES (?,?,?,?)");
3026
3027        try {
3028            insertStructuredNameLookup(db, nameLookupInsert);
3029            insertEmailLookup(db, nameLookupInsert);
3030            insertNicknameLookup(db, nameLookupInsert);
3031        } finally {
3032            nameLookupInsert.close();
3033        }
3034    }
3035
3036    private static final class StructuredNameQuery {
3037        public static final String TABLE = Tables.DATA;
3038
3039        public static final String SELECTION =
3040                DataColumns.MIMETYPE_ID + "=? AND " + Data.DATA1 + " NOT NULL";
3041
3042        public static final String COLUMNS[] = {
3043                StructuredName._ID,
3044                StructuredName.RAW_CONTACT_ID,
3045                StructuredName.DISPLAY_NAME,
3046        };
3047
3048        public static final int ID = 0;
3049        public static final int RAW_CONTACT_ID = 1;
3050        public static final int DISPLAY_NAME = 2;
3051    }
3052
3053    private class StructuredNameLookupBuilder extends NameLookupBuilder {
3054
3055        private final SQLiteStatement mNameLookupInsert;
3056        private final CommonNicknameCache mCommonNicknameCache;
3057
3058        public StructuredNameLookupBuilder(NameSplitter splitter,
3059                CommonNicknameCache commonNicknameCache, SQLiteStatement nameLookupInsert) {
3060            super(splitter);
3061            this.mCommonNicknameCache = commonNicknameCache;
3062            this.mNameLookupInsert = nameLookupInsert;
3063        }
3064
3065        @Override
3066        protected void insertNameLookup(long rawContactId, long dataId, int lookupType,
3067                String name) {
3068            if (!TextUtils.isEmpty(name)) {
3069                ContactsDatabaseHelper.this.insertNormalizedNameLookup(mNameLookupInsert,
3070                        rawContactId, dataId, lookupType, name);
3071            }
3072        }
3073
3074        @Override
3075        protected String[] getCommonNicknameClusters(String normalizedName) {
3076            return mCommonNicknameCache.getCommonNicknameClusters(normalizedName);
3077        }
3078    }
3079
3080    /**
3081     * Inserts name lookup rows for all structured names in the database.
3082     */
3083    private void insertStructuredNameLookup(SQLiteDatabase db, SQLiteStatement nameLookupInsert) {
3084        NameSplitter nameSplitter = createNameSplitter();
3085        NameLookupBuilder nameLookupBuilder = new StructuredNameLookupBuilder(nameSplitter,
3086                new CommonNicknameCache(db), nameLookupInsert);
3087        final long mimeTypeId = lookupMimeTypeId(db, StructuredName.CONTENT_ITEM_TYPE);
3088        Cursor cursor = db.query(StructuredNameQuery.TABLE, StructuredNameQuery.COLUMNS,
3089                StructuredNameQuery.SELECTION, new String[] {String.valueOf(mimeTypeId)},
3090                null, null, null);
3091        try {
3092            while (cursor.moveToNext()) {
3093                long dataId = cursor.getLong(StructuredNameQuery.ID);
3094                long rawContactId = cursor.getLong(StructuredNameQuery.RAW_CONTACT_ID);
3095                String name = cursor.getString(StructuredNameQuery.DISPLAY_NAME);
3096                int fullNameStyle = nameSplitter.guessFullNameStyle(name);
3097                fullNameStyle = nameSplitter.getAdjustedFullNameStyle(fullNameStyle);
3098                nameLookupBuilder.insertNameLookup(rawContactId, dataId, name, fullNameStyle);
3099            }
3100        } finally {
3101            cursor.close();
3102        }
3103    }
3104
3105    private static final class OrganizationQuery {
3106        public static final String TABLE = Tables.DATA;
3107
3108        public static final String SELECTION =
3109                DataColumns.MIMETYPE_ID + "=? AND " + Data.DATA1 + " NOT NULL";
3110
3111        public static final String COLUMNS[] = {
3112                Organization._ID,
3113                Organization.RAW_CONTACT_ID,
3114                Organization.COMPANY,
3115                Organization.TITLE,
3116        };
3117
3118        public static final int ID = 0;
3119        public static final int RAW_CONTACT_ID = 1;
3120        public static final int COMPANY = 2;
3121        public static final int TITLE = 3;
3122    }
3123
3124    private static final class EmailQuery {
3125        public static final String TABLE = Tables.DATA;
3126
3127        public static final String SELECTION =
3128                DataColumns.MIMETYPE_ID + "=? AND " + Data.DATA1 + " NOT NULL";
3129
3130        public static final String COLUMNS[] = {
3131                Email._ID,
3132                Email.RAW_CONTACT_ID,
3133                Email.ADDRESS,
3134        };
3135
3136        public static final int ID = 0;
3137        public static final int RAW_CONTACT_ID = 1;
3138        public static final int ADDRESS = 2;
3139    }
3140
3141    /**
3142     * Inserts name lookup rows for all email addresses in the database.
3143     */
3144    private void insertEmailLookup(SQLiteDatabase db, SQLiteStatement nameLookupInsert) {
3145        final long mimeTypeId = lookupMimeTypeId(db, Email.CONTENT_ITEM_TYPE);
3146        Cursor cursor = db.query(EmailQuery.TABLE, EmailQuery.COLUMNS,
3147                EmailQuery.SELECTION, new String[] {String.valueOf(mimeTypeId)},
3148                null, null, null);
3149        try {
3150            while (cursor.moveToNext()) {
3151                long dataId = cursor.getLong(EmailQuery.ID);
3152                long rawContactId = cursor.getLong(EmailQuery.RAW_CONTACT_ID);
3153                String address = cursor.getString(EmailQuery.ADDRESS);
3154                address = extractHandleFromEmailAddress(address);
3155                insertNameLookup(nameLookupInsert, rawContactId, dataId,
3156                        NameLookupType.EMAIL_BASED_NICKNAME, address);
3157            }
3158        } finally {
3159            cursor.close();
3160        }
3161    }
3162
3163    private static final class NicknameQuery {
3164        public static final String TABLE = Tables.DATA;
3165
3166        public static final String SELECTION =
3167                DataColumns.MIMETYPE_ID + "=? AND " + Data.DATA1 + " NOT NULL";
3168
3169        public static final String COLUMNS[] = {
3170                Nickname._ID,
3171                Nickname.RAW_CONTACT_ID,
3172                Nickname.NAME,
3173        };
3174
3175        public static final int ID = 0;
3176        public static final int RAW_CONTACT_ID = 1;
3177        public static final int NAME = 2;
3178    }
3179
3180    /**
3181     * Inserts name lookup rows for all nicknames in the database.
3182     */
3183    private void insertNicknameLookup(SQLiteDatabase db, SQLiteStatement nameLookupInsert) {
3184        final long mimeTypeId = lookupMimeTypeId(db, Nickname.CONTENT_ITEM_TYPE);
3185        Cursor cursor = db.query(NicknameQuery.TABLE, NicknameQuery.COLUMNS,
3186                NicknameQuery.SELECTION, new String[] {String.valueOf(mimeTypeId)},
3187                null, null, null);
3188        try {
3189            while (cursor.moveToNext()) {
3190                long dataId = cursor.getLong(NicknameQuery.ID);
3191                long rawContactId = cursor.getLong(NicknameQuery.RAW_CONTACT_ID);
3192                String nickname = cursor.getString(NicknameQuery.NAME);
3193                insertNameLookup(nameLookupInsert, rawContactId, dataId,
3194                        NameLookupType.NICKNAME, nickname);
3195            }
3196        } finally {
3197            cursor.close();
3198        }
3199    }
3200
3201    /**
3202     * Inserts a record in the {@link Tables#NAME_LOOKUP} table.
3203     */
3204    public void insertNameLookup(SQLiteStatement stmt, long rawContactId, long dataId,
3205            int lookupType, String name) {
3206        if (TextUtils.isEmpty(name)) {
3207            return;
3208        }
3209
3210        String normalized = NameNormalizer.normalize(name);
3211        if (TextUtils.isEmpty(normalized)) {
3212            return;
3213        }
3214
3215        insertNormalizedNameLookup(stmt, rawContactId, dataId, lookupType, normalized);
3216    }
3217
3218    private void insertNormalizedNameLookup(SQLiteStatement stmt, long rawContactId, long dataId,
3219            int lookupType, String normalizedName) {
3220        stmt.bindLong(1, rawContactId);
3221        stmt.bindLong(2, dataId);
3222        stmt.bindLong(3, lookupType);
3223        stmt.bindString(4, normalizedName);
3224        stmt.executeInsert();
3225    }
3226
3227    /**
3228     * Changing the VISIBLE bit from a field on both RawContacts and Contacts to a separate table.
3229     */
3230    private void upgradeToVersion401(SQLiteDatabase db) {
3231        db.execSQL("CREATE TABLE " + Tables.VISIBLE_CONTACTS + " (" +
3232                Contacts._ID + " INTEGER PRIMARY KEY" +
3233        ");");
3234        db.execSQL("INSERT INTO " + Tables.VISIBLE_CONTACTS +
3235                " SELECT " + Contacts._ID +
3236                " FROM " + Tables.CONTACTS +
3237                " WHERE " + Contacts.IN_VISIBLE_GROUP + "!=0");
3238        db.execSQL("DROP INDEX contacts_visible_index");
3239    }
3240
3241    /**
3242     * Introducing a new table: directories.
3243     */
3244    private void upgradeToVersion402(SQLiteDatabase db) {
3245        createDirectoriesTable(db);
3246    }
3247
3248    private void upgradeToVersion403(SQLiteDatabase db) {
3249        db.execSQL("DROP TABLE IF EXISTS directories;");
3250        createDirectoriesTable(db);
3251
3252        db.execSQL("ALTER TABLE raw_contacts"
3253                + " ADD raw_contact_is_read_only INTEGER NOT NULL DEFAULT 0;");
3254
3255        db.execSQL("ALTER TABLE data"
3256                + " ADD is_read_only INTEGER NOT NULL DEFAULT 0;");
3257    }
3258
3259    private void upgradeToVersion405(SQLiteDatabase db) {
3260        db.execSQL("DROP TABLE IF EXISTS phone_lookup;");
3261        // Private phone numbers table used for lookup
3262        db.execSQL("CREATE TABLE " + Tables.PHONE_LOOKUP + " (" +
3263                PhoneLookupColumns.DATA_ID
3264                + " INTEGER REFERENCES data(_id) NOT NULL," +
3265                PhoneLookupColumns.RAW_CONTACT_ID
3266                + " INTEGER REFERENCES raw_contacts(_id) NOT NULL," +
3267                PhoneLookupColumns.NORMALIZED_NUMBER + " TEXT NOT NULL," +
3268                PhoneLookupColumns.MIN_MATCH + " TEXT NOT NULL" +
3269        ");");
3270
3271        db.execSQL("CREATE INDEX phone_lookup_index ON " + Tables.PHONE_LOOKUP + " (" +
3272                PhoneLookupColumns.NORMALIZED_NUMBER + "," +
3273                PhoneLookupColumns.RAW_CONTACT_ID + "," +
3274                PhoneLookupColumns.DATA_ID +
3275        ");");
3276
3277        db.execSQL("CREATE INDEX phone_lookup_min_match_index ON " + Tables.PHONE_LOOKUP + " (" +
3278                PhoneLookupColumns.MIN_MATCH + "," +
3279                PhoneLookupColumns.RAW_CONTACT_ID + "," +
3280                PhoneLookupColumns.DATA_ID +
3281        ");");
3282
3283        final long mimeTypeId = lookupMimeTypeId(db, Phone.CONTENT_ITEM_TYPE);
3284        if (mimeTypeId == -1) {
3285            return;
3286        }
3287
3288        Cursor cursor = db.rawQuery(
3289                    "SELECT _id, " + Phone.RAW_CONTACT_ID + ", " + Phone.NUMBER +
3290                    " FROM " + Tables.DATA +
3291                    " WHERE " + DataColumns.MIMETYPE_ID + "=" + mimeTypeId
3292                            + " AND " + Phone.NUMBER + " NOT NULL", null);
3293
3294        ContentValues phoneValues = new ContentValues();
3295        try {
3296            while (cursor.moveToNext()) {
3297                long dataID = cursor.getLong(0);
3298                long rawContactID = cursor.getLong(1);
3299                String number = cursor.getString(2);
3300                String normalizedNumber = PhoneNumberUtils.normalizeNumber(number);
3301                if (!TextUtils.isEmpty(normalizedNumber)) {
3302                    phoneValues.clear();
3303                    phoneValues.put(PhoneLookupColumns.RAW_CONTACT_ID, rawContactID);
3304                    phoneValues.put(PhoneLookupColumns.DATA_ID, dataID);
3305                    phoneValues.put(PhoneLookupColumns.NORMALIZED_NUMBER, normalizedNumber);
3306                    phoneValues.put(PhoneLookupColumns.MIN_MATCH,
3307                            PhoneNumberUtils.toCallerIDMinMatch(normalizedNumber));
3308                    db.insert(Tables.PHONE_LOOKUP, null, phoneValues);
3309                }
3310            }
3311        } finally {
3312            cursor.close();
3313        }
3314    }
3315
3316    private void upgradeToVersion406(SQLiteDatabase db) {
3317        db.execSQL("ALTER TABLE calls ADD countryiso TEXT;");
3318    }
3319
3320    private void upgradeToVersion409(SQLiteDatabase db) {
3321        db.execSQL("DROP TABLE IF EXISTS directories;");
3322        createDirectoriesTable(db);
3323    }
3324
3325    /**
3326     * Adding DEFAULT_DIRECTORY table.
3327     * DEFAULT_DIRECTORY should contain every contact which should be shown to users in default.
3328     * - if a contact doesn't belong to any account (local contact), it should be in
3329     *   default_directory
3330     * - if a contact belongs to an account that doesn't have a "default" group, it should be in
3331     *   default_directory
3332     * - if a contact belongs to an account that has a "default" group (like Google directory,
3333     *   which has "My contacts" group as default), it should be in default_directory.
3334     *
3335     * This logic assumes that accounts with the "default" group should have at least one
3336     * group with AUTO_ADD (implying it is the default group) flag in the groups table.
3337     */
3338    private void upgradeToVersion411(SQLiteDatabase db) {
3339        db.execSQL("DROP TABLE IF EXISTS " + Tables.DEFAULT_DIRECTORY);
3340        db.execSQL("CREATE TABLE default_directory (_id INTEGER PRIMARY KEY);");
3341
3342        // Process contacts without an account
3343        db.execSQL("INSERT OR IGNORE INTO default_directory " +
3344                " SELECT contact_id " +
3345                " FROM raw_contacts " +
3346                " WHERE raw_contacts.account_name IS NULL " +
3347                "   AND raw_contacts.account_type IS NULL ");
3348
3349        // Process accounts that don't have a default group (e.g. Exchange).
3350        db.execSQL("INSERT OR IGNORE INTO default_directory " +
3351                " SELECT contact_id " +
3352                " FROM raw_contacts " +
3353                " WHERE NOT EXISTS" +
3354                " (SELECT _id " +
3355                "  FROM groups " +
3356                "  WHERE raw_contacts.account_name = groups.account_name" +
3357                "    AND raw_contacts.account_type = groups.account_type" +
3358                "    AND groups.auto_add != 0)");
3359
3360        final long mimetype = lookupMimeTypeId(db, GroupMembership.CONTENT_ITEM_TYPE);
3361
3362        // Process accounts that do have a default group (e.g. Google)
3363        db.execSQL("INSERT OR IGNORE INTO default_directory " +
3364                " SELECT contact_id " +
3365                " FROM raw_contacts " +
3366                " JOIN data " +
3367                "   ON (raw_contacts._id=raw_contact_id)" +
3368                " WHERE mimetype_id=" + mimetype +
3369                " AND EXISTS" +
3370                " (SELECT _id" +
3371                "  FROM groups" +
3372                "  WHERE raw_contacts.account_name = groups.account_name" +
3373                "    AND raw_contacts.account_type = groups.account_type" +
3374                "    AND groups.auto_add != 0)");
3375    }
3376
3377    private void upgradeToVersion413(SQLiteDatabase db) {
3378        db.execSQL("DROP TABLE IF EXISTS directories;");
3379        createDirectoriesTable(db);
3380    }
3381
3382    private void upgradeToVersion415(SQLiteDatabase db) {
3383        db.execSQL(
3384                "ALTER TABLE " + Tables.GROUPS +
3385                " ADD " + Groups.GROUP_IS_READ_ONLY + " INTEGER NOT NULL DEFAULT 0");
3386        db.execSQL(
3387                "UPDATE " + Tables.GROUPS +
3388                "   SET " + Groups.GROUP_IS_READ_ONLY + "=1" +
3389                " WHERE " + Groups.SYSTEM_ID + " NOT NULL");
3390    }
3391
3392    private void upgradeToVersion416(SQLiteDatabase db) {
3393        db.execSQL("CREATE INDEX phone_lookup_data_id_min_match_index ON " + Tables.PHONE_LOOKUP +
3394                " (" + PhoneLookupColumns.DATA_ID + ", " + PhoneLookupColumns.MIN_MATCH + ");");
3395    }
3396
3397    private void upgradeToVersion501(SQLiteDatabase db) {
3398        // Remove organization rows from the name lookup, we now use search index for that
3399        db.execSQL("DELETE FROM name_lookup WHERE name_type=5");
3400    }
3401
3402    private void upgradeToVersion502(SQLiteDatabase db) {
3403        // Remove Chinese and Korean name lookup - this data is now in the search index
3404        db.execSQL("DELETE FROM name_lookup WHERE name_type IN (6, 7)");
3405    }
3406
3407    private void upgradeToVersion504(SQLiteDatabase db) {
3408        initializeCache(db);
3409
3410        // Find all names with prefixes and recreate display name
3411        Cursor cursor = db.rawQuery(
3412                "SELECT " + StructuredName.RAW_CONTACT_ID +
3413                " FROM " + Tables.DATA +
3414                " WHERE " + DataColumns.MIMETYPE_ID + "=?"
3415                        + " AND " + StructuredName.PREFIX + " NOT NULL",
3416                new String[]{ String.valueOf(mMimeTypeIdStructuredName) });
3417
3418        try {
3419            while(cursor.moveToNext()) {
3420                long rawContactId = cursor.getLong(0);
3421                updateRawContactDisplayName(db, rawContactId);
3422            }
3423
3424        } finally {
3425            cursor.close();
3426        }
3427    }
3428
3429    private void upgradeToVersion600(SQLiteDatabase db) {
3430        // This change used to add the profile raw contact ID to the Accounts table.  That
3431        // column is no longer needed (as of version 614) since the profile records are stored in
3432        // a separate copy of the database for security reasons.  So this change is now a no-op.
3433    }
3434
3435    private void upgradeToVersion601(SQLiteDatabase db) {
3436        db.execSQL("CREATE TABLE data_usage_stat(" +
3437                "stat_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
3438                "data_id INTEGER NOT NULL, " +
3439                "usage_type INTEGER NOT NULL DEFAULT 0, " +
3440                "times_used INTEGER NOT NULL DEFAULT 0, " +
3441                "last_time_used INTERGER NOT NULL DEFAULT 0, " +
3442                "FOREIGN KEY(data_id) REFERENCES data(_id));");
3443        db.execSQL("CREATE UNIQUE INDEX data_usage_stat_index ON " +
3444                "data_usage_stat (data_id, usage_type)");
3445    }
3446
3447    private void upgradeToVersion602(SQLiteDatabase db) {
3448        db.execSQL("ALTER TABLE calls ADD voicemail_uri TEXT;");
3449        db.execSQL("ALTER TABLE calls ADD _data TEXT;");
3450        db.execSQL("ALTER TABLE calls ADD has_content INTEGER;");
3451        db.execSQL("ALTER TABLE calls ADD mime_type TEXT;");
3452        db.execSQL("ALTER TABLE calls ADD source_data TEXT;");
3453        db.execSQL("ALTER TABLE calls ADD source_package TEXT;");
3454        db.execSQL("ALTER TABLE calls ADD state INTEGER;");
3455    }
3456
3457    private void upgradeToVersion604(SQLiteDatabase db) {
3458        db.execSQL("CREATE TABLE voicemail_status (" +
3459                "_id INTEGER PRIMARY KEY AUTOINCREMENT," +
3460                "source_package TEXT UNIQUE NOT NULL," +
3461                "settings_uri TEXT," +
3462                "voicemail_access_uri TEXT," +
3463                "configuration_state INTEGER," +
3464                "data_channel_state INTEGER," +
3465                "notification_channel_state INTEGER" +
3466        ");");
3467    }
3468
3469    private void upgradeToVersion605(SQLiteDatabase db) {
3470        // This version used to create the stream item and stream item photos tables, but a newer
3471        // version of those tables is created in version 609 below.  So omitting the creation in
3472        // this upgrade step to avoid a create->drop->create.
3473    }
3474
3475    private void upgradeToVersion606(SQLiteDatabase db) {
3476        db.execSQL("DROP VIEW IF EXISTS view_contacts_restricted;");
3477        db.execSQL("DROP VIEW IF EXISTS view_data_restricted;");
3478        db.execSQL("DROP VIEW IF EXISTS view_raw_contacts_restricted;");
3479        db.execSQL("DROP VIEW IF EXISTS view_raw_entities_restricted;");
3480        db.execSQL("DROP VIEW IF EXISTS view_entities_restricted;");
3481        db.execSQL("DROP VIEW IF EXISTS view_data_usage_stat_restricted;");
3482        db.execSQL("DROP INDEX IF EXISTS contacts_restricted_index");
3483
3484        // We should remove the restricted columns here as well, but unfortunately SQLite doesn't
3485        // provide ALTER TABLE DROP COLUMN. As they have DEFAULT 0, we can keep but ignore them
3486    }
3487
3488    private void upgradeToVersion607(SQLiteDatabase db) {
3489        // We added "action" and "action_uri" to groups here, but realized this was not a smart
3490        // move. This upgrade step has been removed (all dogfood phones that executed this step
3491        // will have those columns, but that shouldn't hurt. Unfortunately, SQLite makes it hard
3492        // to remove columns)
3493    }
3494
3495    private void upgradeToVersion608(SQLiteDatabase db) {
3496        db.execSQL("ALTER TABLE contacts ADD photo_file_id INTEGER REFERENCES photo_files(_id);");
3497
3498        db.execSQL("CREATE TABLE photo_files(" +
3499                "_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
3500                "height INTEGER NOT NULL, " +
3501                "width INTEGER NOT NULL, " +
3502                "filesize INTEGER NOT NULL);");
3503    }
3504
3505    private void upgradeToVersion609(SQLiteDatabase db) {
3506        // This version used to create the stream item and stream item photos tables, but a newer
3507        // version of those tables is created in version 613 below.  So omitting the creation in
3508        // this upgrade step to avoid a create->drop->create.
3509    }
3510
3511    private void upgradeToVersion610(SQLiteDatabase db) {
3512        db.execSQL("ALTER TABLE calls ADD is_read INTEGER;");
3513    }
3514
3515    private void upgradeToVersion611(SQLiteDatabase db) {
3516        db.execSQL("ALTER TABLE raw_contacts ADD data_set TEXT DEFAULT NULL;");
3517        db.execSQL("ALTER TABLE groups ADD data_set TEXT DEFAULT NULL;");
3518        db.execSQL("ALTER TABLE accounts ADD data_set TEXT DEFAULT NULL;");
3519
3520        db.execSQL("CREATE INDEX raw_contacts_source_id_data_set_index ON raw_contacts " +
3521                "(sourceid, account_type, account_name, data_set);");
3522
3523        db.execSQL("CREATE INDEX groups_source_id_data_set_index ON groups " +
3524                "(sourceid, account_type, account_name, data_set);");
3525    }
3526
3527    private void upgradeToVersion612(SQLiteDatabase db) {
3528        db.execSQL("ALTER TABLE calls ADD geocoded_location TEXT DEFAULT NULL;");
3529        // Old calls will not have a geocoded location; new calls will get it when inserted.
3530    }
3531
3532    private void upgradeToVersion613(SQLiteDatabase db) {
3533        // The stream item and stream item photos APIs were not in-use by anyone in the time
3534        // between their initial creation (in v609) and this update.  So we're just dropping
3535        // and re-creating them to get appropriate columns.  The delta is as follows:
3536        // - In stream_items, package_id was replaced by res_package.
3537        // - In stream_item_photos, picture was replaced by photo_file_id.
3538        // - Instead of resource ids for icon and label, we use resource name strings now
3539        // - Added sync columns
3540        // - Removed action and action_uri
3541        // - Text and comments are now nullable
3542
3543        db.execSQL("DROP TABLE IF EXISTS stream_items");
3544        db.execSQL("DROP TABLE IF EXISTS stream_item_photos");
3545
3546        db.execSQL("CREATE TABLE stream_items(" +
3547                "_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
3548                "raw_contact_id INTEGER NOT NULL, " +
3549                "res_package TEXT, " +
3550                "icon TEXT, " +
3551                "label TEXT, " +
3552                "text TEXT, " +
3553                "timestamp INTEGER NOT NULL, " +
3554                "comments TEXT, " +
3555                "stream_item_sync1 TEXT, " +
3556                "stream_item_sync2 TEXT, " +
3557                "stream_item_sync3 TEXT, " +
3558                "stream_item_sync4 TEXT, " +
3559                "FOREIGN KEY(raw_contact_id) REFERENCES raw_contacts(_id));");
3560
3561        db.execSQL("CREATE TABLE stream_item_photos(" +
3562                "_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
3563                "stream_item_id INTEGER NOT NULL, " +
3564                "sort_index INTEGER, " +
3565                "photo_file_id INTEGER NOT NULL, " +
3566                "stream_item_photo_sync1 TEXT, " +
3567                "stream_item_photo_sync2 TEXT, " +
3568                "stream_item_photo_sync3 TEXT, " +
3569                "stream_item_photo_sync4 TEXT, " +
3570                "FOREIGN KEY(stream_item_id) REFERENCES stream_items(_id));");
3571    }
3572
3573    private void upgradeToVersion615(SQLiteDatabase db) {
3574        // Old calls will not have up to date values for these columns, they will be filled in
3575        // as needed.
3576        db.execSQL("ALTER TABLE calls ADD lookup_uri TEXT DEFAULT NULL;");
3577        db.execSQL("ALTER TABLE calls ADD matched_number TEXT DEFAULT NULL;");
3578        db.execSQL("ALTER TABLE calls ADD normalized_number TEXT DEFAULT NULL;");
3579        db.execSQL("ALTER TABLE calls ADD photo_id INTEGER NOT NULL DEFAULT 0;");
3580    }
3581
3582    private void upgradeToVersion618(SQLiteDatabase db) {
3583        // The Settings table needs a data_set column which technically should be part of the
3584        // primary key but can't be because it may be null.  Since SQLite doesn't support nuking
3585        // the primary key, we'll drop the old table, re-create it, and copy the settings back in.
3586        db.execSQL("CREATE TEMPORARY TABLE settings_backup(" +
3587                "account_name STRING NOT NULL," +
3588                "account_type STRING NOT NULL," +
3589                "ungrouped_visible INTEGER NOT NULL DEFAULT 0," +
3590                "should_sync INTEGER NOT NULL DEFAULT 1" +
3591        ");");
3592        db.execSQL("INSERT INTO settings_backup " +
3593                "SELECT account_name, account_type, ungrouped_visible, should_sync" +
3594                " FROM settings");
3595        db.execSQL("DROP TABLE settings");
3596        db.execSQL("CREATE TABLE settings (" +
3597                "account_name STRING NOT NULL," +
3598                "account_type STRING NOT NULL," +
3599                "data_set STRING," +
3600                "ungrouped_visible INTEGER NOT NULL DEFAULT 0," +
3601                "should_sync INTEGER NOT NULL DEFAULT 1" +
3602        ");");
3603        db.execSQL("INSERT INTO settings " +
3604                "SELECT account_name, account_type, NULL, ungrouped_visible, should_sync " +
3605                "FROM settings_backup");
3606        db.execSQL("DROP TABLE settings_backup");
3607    }
3608
3609    private void upgradeToVersion622(SQLiteDatabase db) {
3610        db.execSQL("ALTER TABLE calls ADD formatted_number TEXT DEFAULT NULL;");
3611    }
3612
3613    private void upgradeToVersion626(SQLiteDatabase db) {
3614        db.execSQL("DROP TABLE IF EXISTS accounts");
3615
3616        db.execSQL("CREATE TABLE accounts (" +
3617                "_id INTEGER PRIMARY KEY AUTOINCREMENT," +
3618                "account_name TEXT, " +
3619                "account_type TEXT, " +
3620                "data_set TEXT" +
3621        ");");
3622
3623        // Add "account_id" column to groups and raw_contacts
3624        db.execSQL("ALTER TABLE raw_contacts ADD " +
3625                "account_id INTEGER REFERENCES accounts(_id)");
3626        db.execSQL("ALTER TABLE groups ADD " +
3627                "account_id INTEGER REFERENCES accounts(_id)");
3628
3629        // Update indexes.
3630        db.execSQL("DROP INDEX IF EXISTS raw_contacts_source_id_index");
3631        db.execSQL("DROP INDEX IF EXISTS raw_contacts_source_id_data_set_index");
3632        db.execSQL("DROP INDEX IF EXISTS groups_source_id_index");
3633        db.execSQL("DROP INDEX IF EXISTS groups_source_id_data_set_index");
3634
3635        db.execSQL("CREATE INDEX raw_contacts_source_id_account_id_index ON raw_contacts ("
3636                + "sourceid, account_id);");
3637        db.execSQL("CREATE INDEX groups_source_id_account_id_index ON groups ("
3638                + "sourceid, account_id);");
3639
3640        // Migrate account_name/account_type/data_set to accounts table
3641
3642        final Set<AccountWithDataSet> accountsWithDataSets = Sets.newHashSet();
3643        upgradeToVersion626_findAccountsWithDataSets(accountsWithDataSets, db, "raw_contacts");
3644        upgradeToVersion626_findAccountsWithDataSets(accountsWithDataSets, db, "groups");
3645
3646        for (AccountWithDataSet accountWithDataSet : accountsWithDataSets) {
3647            db.execSQL("INSERT INTO accounts (account_name,account_type,data_set)VALUES(?, ?, ?)",
3648                    new String[] {
3649                            accountWithDataSet.getAccountName(),
3650                            accountWithDataSet.getAccountType(),
3651                            accountWithDataSet.getDataSet()
3652                    });
3653        }
3654        upgradeToVersion626_fillAccountId(db, "raw_contacts");
3655        upgradeToVersion626_fillAccountId(db, "groups");
3656    }
3657
3658    private static void upgradeToVersion626_findAccountsWithDataSets(
3659            Set<AccountWithDataSet> result, SQLiteDatabase db, String table) {
3660        Cursor c = db.rawQuery(
3661                "SELECT DISTINCT account_name, account_type, data_set FROM " + table, null);
3662        try {
3663            while (c.moveToNext()) {
3664                result.add(AccountWithDataSet.get(c.getString(0), c.getString(1), c.getString(2)));
3665            }
3666        } finally {
3667            c.close();
3668        }
3669    }
3670
3671    private static void upgradeToVersion626_fillAccountId(SQLiteDatabase db, String table) {
3672        StringBuilder sb = new StringBuilder();
3673
3674        // Set account_id and null out account_name, account_type and data_set
3675
3676        sb.append("UPDATE " + table + " SET account_id = (SELECT _id FROM accounts WHERE ");
3677
3678        addJoinExpressionAllowingNull(sb, table + ".account_name", "accounts.account_name");
3679        sb.append("AND");
3680        addJoinExpressionAllowingNull(sb, table + ".account_type", "accounts.account_type");
3681        sb.append("AND");
3682        addJoinExpressionAllowingNull(sb, table + ".data_set", "accounts.data_set");
3683
3684        sb.append("), account_name = null, account_type = null, data_set = null");
3685        db.execSQL(sb.toString());
3686    }
3687
3688    private void upgradeToVersion701(SQLiteDatabase db) {
3689        db.execSQL("UPDATE raw_contacts SET last_time_contacted =" +
3690                " max(ifnull(last_time_contacted, 0), " +
3691                " ifnull((SELECT max(last_time_used) " +
3692                    " FROM data JOIN data_usage_stat ON (data._id = data_usage_stat.data_id)" +
3693                    " WHERE data.raw_contact_id = raw_contacts._id), 0))");
3694        // Replace 0 with null.  This isn't really necessary, but we do this anyway for consistency.
3695        db.execSQL("UPDATE raw_contacts SET last_time_contacted = null" +
3696                " where last_time_contacted = 0");
3697    }
3698
3699    /**
3700     * Pre-HC devices don't have correct "NORMALIZED_NUMBERS".  Clear them up.
3701     */
3702    private void upgradeToVersion702(SQLiteDatabase db) {
3703        // All the "correct" Phone.NORMALIZED_NUMBERS should begin with "+".  The upgraded data
3704        // don't.  Find all Phone.NORMALIZED_NUMBERS that don't begin with "+".
3705        final int count;
3706        final long[] dataIds;
3707        final long[] rawContactIds;
3708        final String[] phoneNumbers;
3709        final StringBuilder sbDataIds;
3710        final Cursor c = db.rawQuery(
3711                "SELECT _id, raw_contact_id, data1 FROM data " +
3712                " WHERE mimetype_id=" +
3713                    "(SELECT _id FROM mimetypes" +
3714                    " WHERE mimetype='vnd.android.cursor.item/phone_v2')" +
3715                " AND data4 not like '+%'", // "Not like" will exclude nulls too.
3716                null);
3717        try {
3718            count = c.getCount();
3719            if (count == 0) {
3720                return;
3721            }
3722            dataIds = new long[count];
3723            rawContactIds = new long[count];
3724            phoneNumbers = new String[count];
3725            sbDataIds = new StringBuilder();
3726
3727            c.moveToPosition(-1);
3728            while (c.moveToNext()) {
3729                final int i = c.getPosition();
3730                dataIds[i] = c.getLong(0);
3731                rawContactIds[i] = c.getLong(1);
3732                phoneNumbers[i] = c.getString(2);
3733
3734                if (sbDataIds.length() > 0) {
3735                    sbDataIds.append(",");
3736                }
3737                sbDataIds.append(dataIds[i]);
3738            }
3739        } finally {
3740            c.close();
3741        }
3742
3743        final String dataIdList = sbDataIds.toString();
3744
3745        // Then, update the Data and PhoneLookup tables.
3746
3747        // First, just null out all Phone.NORMALIZED_NUMBERS for those.
3748        db.execSQL("UPDATE data SET data4 = null" +
3749                " WHERE _id IN (" + dataIdList + ")");
3750
3751        // Then, re-create phone_lookup for them.
3752        db.execSQL("DELETE FROM phone_lookup" +
3753                " WHERE data_id IN (" + dataIdList + ")");
3754
3755        for (int i = 0; i < count; i++) {
3756            // Mimic how DataRowHandlerForPhoneNumber.insert() works when it can't normalize
3757            // numbers.
3758            final String phoneNumber = phoneNumbers[i];
3759            if (TextUtils.isEmpty(phoneNumber)) continue;
3760
3761            final String normalized = PhoneNumberUtils.normalizeNumber(phoneNumber);
3762            if (TextUtils.isEmpty(normalized)) continue;
3763
3764            db.execSQL("INSERT INTO phone_lookup" +
3765                    "(data_id, raw_contact_id, normalized_number, min_match)" +
3766                    " VALUES(?,?,?,?)",
3767                    new String[] {
3768                        String.valueOf(dataIds[i]),
3769                        String.valueOf(rawContactIds[i]),
3770                        normalized,
3771                        PhoneNumberUtils.toCallerIDMinMatch(normalized)
3772                    });
3773        }
3774    }
3775
3776    public String extractHandleFromEmailAddress(String email) {
3777        Rfc822Token[] tokens = Rfc822Tokenizer.tokenize(email);
3778        if (tokens.length == 0) {
3779            return null;
3780        }
3781
3782        String address = tokens[0].getAddress();
3783        int at = address.indexOf('@');
3784        if (at != -1) {
3785            return address.substring(0, at);
3786        }
3787        return null;
3788    }
3789
3790    public String extractAddressFromEmailAddress(String email) {
3791        Rfc822Token[] tokens = Rfc822Tokenizer.tokenize(email);
3792        if (tokens.length == 0) {
3793            return null;
3794        }
3795
3796        return tokens[0].getAddress().trim();
3797    }
3798
3799    private static long lookupMimeTypeId(SQLiteDatabase db, String mimeType) {
3800        try {
3801            return DatabaseUtils.longForQuery(db,
3802                    "SELECT " + MimetypesColumns._ID +
3803                    " FROM " + Tables.MIMETYPES +
3804                    " WHERE " + MimetypesColumns.MIMETYPE
3805                            + "='" + mimeType + "'", null);
3806        } catch (SQLiteDoneException e) {
3807            // No rows of this type in the database
3808            return -1;
3809        }
3810    }
3811
3812    private void bindString(SQLiteStatement stmt, int index, String value) {
3813        if (value == null) {
3814            stmt.bindNull(index);
3815        } else {
3816            stmt.bindString(index, value);
3817        }
3818    }
3819
3820    private void bindLong(SQLiteStatement stmt, int index, Number value) {
3821        if (value == null) {
3822            stmt.bindNull(index);
3823        } else {
3824            stmt.bindLong(index, value.longValue());
3825        }
3826    }
3827
3828    /**
3829     * Add a string like "(((column1) = (column2)) OR ((column1) IS NULL AND (column2) IS NULL))"
3830     */
3831    private static StringBuilder addJoinExpressionAllowingNull(StringBuilder sb,
3832            String column1, String column2) {
3833        sb.append("(((").append(column1).append(")=(").append(column2);
3834        sb.append("))OR((");
3835        sb.append(column1).append(") IS NULL AND (").append(column2).append(") IS NULL))");
3836        return sb;
3837    }
3838
3839    /**
3840     * Adds index stats into the SQLite database to force it to always use the lookup indexes.
3841     */
3842    private void updateSqliteStats(SQLiteDatabase db) {
3843
3844        // Specific stats strings are based on an actual large database after running ANALYZE
3845        // Important here are relative sizes. Raw-Contacts is slightly bigger than Contacts
3846        // Warning: Missing tables in here will make SQLite assume to contain 1000000 rows,
3847        // which can lead to catastrophic query plans for small tables
3848
3849        // See the latest of version of http://www.sqlite.org/cgi/src/finfo?name=src/analyze.c
3850        // for what these numbers mean.
3851        try {
3852            db.execSQL("DELETE FROM sqlite_stat1");
3853            updateIndexStats(db, Tables.CONTACTS,
3854                    "contacts_has_phone_index", "9000 500");
3855            updateIndexStats(db, Tables.CONTACTS,
3856                    "contacts_name_raw_contact_id_index", "9000 1");
3857
3858            updateIndexStats(db, Tables.RAW_CONTACTS,
3859                    "raw_contacts_contact_id_index", "10000 2");
3860            updateIndexStats(db, Tables.RAW_CONTACTS,
3861                    "raw_contact_sort_key2_index", "10000 2");
3862            updateIndexStats(db, Tables.RAW_CONTACTS,
3863                    "raw_contact_sort_key1_index", "10000 2");
3864            updateIndexStats(db, Tables.RAW_CONTACTS,
3865                    "raw_contacts_source_id_account_id_index", "10000 1 1 1 1");
3866
3867            updateIndexStats(db, Tables.NAME_LOOKUP,
3868                    "name_lookup_raw_contact_id_index", "35000 4");
3869            updateIndexStats(db, Tables.NAME_LOOKUP,
3870                    "name_lookup_index", "35000 2 2 2 1");
3871            updateIndexStats(db, Tables.NAME_LOOKUP,
3872                    "sqlite_autoindex_name_lookup_1", "35000 3 2 1");
3873
3874            updateIndexStats(db, Tables.PHONE_LOOKUP,
3875                    "phone_lookup_index", "3500 3 2 1");
3876            updateIndexStats(db, Tables.PHONE_LOOKUP,
3877                    "phone_lookup_min_match_index", "3500 3 2 2");
3878            updateIndexStats(db, Tables.PHONE_LOOKUP,
3879                    "phone_lookup_data_id_min_match_index", "3500 2 2");
3880
3881            updateIndexStats(db, Tables.DATA,
3882                    "data_mimetype_data1_index", "60000 5000 2");
3883            updateIndexStats(db, Tables.DATA,
3884                    "data_raw_contact_id", "60000 10");
3885
3886            updateIndexStats(db, Tables.GROUPS,
3887                    "groups_source_id_account_id_index", "50 2 2 1 1");
3888
3889            updateIndexStats(db, Tables.NICKNAME_LOOKUP,
3890                    "nickname_lookup_index", "500 2 1");
3891
3892            updateIndexStats(db, Tables.CALLS,
3893                    null, "250");
3894
3895            updateIndexStats(db, Tables.STATUS_UPDATES,
3896                    null, "100");
3897
3898            updateIndexStats(db, Tables.STREAM_ITEMS,
3899                    null, "500");
3900            updateIndexStats(db, Tables.STREAM_ITEM_PHOTOS,
3901                    null, "50");
3902
3903            updateIndexStats(db, Tables.VOICEMAIL_STATUS,
3904                    null, "5");
3905
3906            updateIndexStats(db, Tables.ACCOUNTS,
3907                    null, "3");
3908
3909            updateIndexStats(db, Tables.VISIBLE_CONTACTS,
3910                    null, "2000");
3911
3912            updateIndexStats(db, Tables.PHOTO_FILES,
3913                    null, "50");
3914
3915            updateIndexStats(db, Tables.DEFAULT_DIRECTORY,
3916                    null, "1500");
3917
3918            updateIndexStats(db, Tables.MIMETYPES,
3919                    "mime_type", "18 1");
3920
3921            updateIndexStats(db, Tables.DATA_USAGE_STAT,
3922                    "data_usage_stat_index", "20 2 1");
3923
3924            // Tiny tables
3925            updateIndexStats(db, Tables.AGGREGATION_EXCEPTIONS,
3926                    null, "10");
3927            updateIndexStats(db, Tables.SETTINGS,
3928                    null, "10");
3929            updateIndexStats(db, Tables.PACKAGES,
3930                    null, "0");
3931            updateIndexStats(db, Tables.DIRECTORIES,
3932                    null, "3");
3933            updateIndexStats(db, LegacyApiSupport.LegacyTables.SETTINGS,
3934                    null, "0");
3935            updateIndexStats(db, "android_metadata",
3936                    null, "1");
3937            updateIndexStats(db, "_sync_state",
3938                    "sqlite_autoindex__sync_state_1", "2 1 1");
3939            updateIndexStats(db, "_sync_state_metadata",
3940                    null, "1");
3941            updateIndexStats(db, "properties",
3942                    "sqlite_autoindex_properties_1", "4 1");
3943
3944            // Search index
3945            updateIndexStats(db, "search_index_docsize",
3946                    null, "9000");
3947            updateIndexStats(db, "search_index_content",
3948                    null, "9000");
3949            updateIndexStats(db, "search_index_stat",
3950                    null, "1");
3951            updateIndexStats(db, "search_index_segments",
3952                    null, "450");
3953            updateIndexStats(db, "search_index_segdir",
3954                    "sqlite_autoindex_search_index_segdir_1", "9 5 1");
3955
3956            // Force sqlite to reload sqlite_stat1.
3957            db.execSQL("ANALYZE sqlite_master;");
3958        } catch (SQLException e) {
3959            Log.e(TAG, "Could not update index stats", e);
3960        }
3961    }
3962
3963    /**
3964     * Stores statistics for a given index.
3965     *
3966     * @param stats has the following structure: the first index is the expected size of
3967     * the table.  The following integer(s) are the expected number of records selected with the
3968     * index.  There should be one integer per indexed column.
3969     */
3970    private void updateIndexStats(SQLiteDatabase db, String table, String index,
3971            String stats) {
3972        if (index == null) {
3973            db.execSQL("DELETE FROM sqlite_stat1 WHERE tbl=? AND idx IS NULL",
3974                    new String[] { table });
3975        } else {
3976            db.execSQL("DELETE FROM sqlite_stat1 WHERE tbl=? AND idx=?",
3977                    new String[] { table, index });
3978        }
3979        db.execSQL("INSERT INTO sqlite_stat1 (tbl,idx,stat) VALUES (?,?,?)",
3980                new String[] { table, index, stats });
3981    }
3982
3983    /**
3984     * Wipes all data except mime type and package lookup tables.
3985     */
3986    public void wipeData() {
3987        SQLiteDatabase db = getWritableDatabase();
3988
3989        db.execSQL("DELETE FROM " + Tables.ACCOUNTS + ";");
3990        db.execSQL("DELETE FROM " + Tables.CONTACTS + ";");
3991        db.execSQL("DELETE FROM " + Tables.RAW_CONTACTS + ";");
3992        db.execSQL("DELETE FROM " + Tables.STREAM_ITEMS + ";");
3993        db.execSQL("DELETE FROM " + Tables.STREAM_ITEM_PHOTOS + ";");
3994        db.execSQL("DELETE FROM " + Tables.PHOTO_FILES + ";");
3995        db.execSQL("DELETE FROM " + Tables.DATA + ";");
3996        db.execSQL("DELETE FROM " + Tables.PHONE_LOOKUP + ";");
3997        db.execSQL("DELETE FROM " + Tables.NAME_LOOKUP + ";");
3998        db.execSQL("DELETE FROM " + Tables.GROUPS + ";");
3999        db.execSQL("DELETE FROM " + Tables.AGGREGATION_EXCEPTIONS + ";");
4000        db.execSQL("DELETE FROM " + Tables.SETTINGS + ";");
4001        db.execSQL("DELETE FROM " + Tables.CALLS + ";");
4002        db.execSQL("DELETE FROM " + Tables.DIRECTORIES + ";");
4003        db.execSQL("DELETE FROM " + Tables.SEARCH_INDEX + ";");
4004
4005        initializeCache(db);
4006
4007        // Note: we are not removing reference data from Tables.NICKNAME_LOOKUP
4008    }
4009
4010    public NameSplitter createNameSplitter() {
4011        mNameSplitter = new NameSplitter(
4012                mContext.getString(com.android.internal.R.string.common_name_prefixes),
4013                mContext.getString(com.android.internal.R.string.common_last_name_prefixes),
4014                mContext.getString(com.android.internal.R.string.common_name_suffixes),
4015                mContext.getString(com.android.internal.R.string.common_name_conjunctions),
4016                Locale.getDefault());
4017        return mNameSplitter;
4018    }
4019
4020    /**
4021     * Return the {@link ApplicationInfo#uid} for the given package name.
4022     */
4023    public static int getUidForPackageName(PackageManager pm, String packageName) {
4024        try {
4025            ApplicationInfo clientInfo = pm.getApplicationInfo(packageName, 0 /* no flags */);
4026            return clientInfo.uid;
4027        } catch (NameNotFoundException e) {
4028            throw new RuntimeException(e);
4029        }
4030    }
4031
4032    /**
4033     * Perform an internal string-to-integer lookup using the compiled
4034     * {@link SQLiteStatement} provided. If a mapping isn't found in database, it will be
4035     * created. All new, uncached answers are added to the cache automatically.
4036     *
4037     * @param query Compiled statement used to query for the mapping.
4038     * @param insert Compiled statement used to insert a new mapping when no
4039     *            existing one is found in cache or from query.
4040     * @param value Value to find mapping for.
4041     * @param cache In-memory cache of previous answers.
4042     * @return An unique integer mapping for the given value.
4043     */
4044    private long lookupAndCacheId(SQLiteStatement query, SQLiteStatement insert,
4045            String value, HashMap<String, Long> cache) {
4046        long id = -1;
4047        try {
4048            // Try searching database for mapping
4049            DatabaseUtils.bindObjectToProgram(query, 1, value);
4050            id = query.simpleQueryForLong();
4051        } catch (SQLiteDoneException e) {
4052            // Nothing found, so try inserting new mapping
4053            DatabaseUtils.bindObjectToProgram(insert, 1, value);
4054            id = insert.executeInsert();
4055        }
4056        if (id != -1) {
4057            // Cache and return the new answer
4058            cache.put(value, id);
4059            return id;
4060        } else {
4061            // Otherwise throw if no mapping found or created
4062            throw new IllegalStateException("Couldn't find or create internal "
4063                    + "lookup table entry for value " + value);
4064        }
4065    }
4066
4067    /**
4068     * Convert a package name into an integer, using {@link Tables#PACKAGES} for
4069     * lookups and possible allocation of new IDs as needed.
4070     */
4071    public long getPackageId(String packageName) {
4072        // Try an in-memory cache lookup
4073        if (mPackageCache.containsKey(packageName)) return mPackageCache.get(packageName);
4074
4075        final SQLiteStatement packageQuery = getWritableDatabase().compileStatement(
4076                "SELECT " + PackagesColumns._ID +
4077                " FROM " + Tables.PACKAGES +
4078                " WHERE " + PackagesColumns.PACKAGE + "=?");
4079
4080        final SQLiteStatement packageInsert = getWritableDatabase().compileStatement(
4081                "INSERT INTO " + Tables.PACKAGES + "("
4082                        + PackagesColumns.PACKAGE +
4083                ") VALUES (?)");
4084        try {
4085            return lookupAndCacheId(packageQuery, packageInsert, packageName, mPackageCache);
4086        } finally {
4087            packageQuery.close();
4088            packageInsert.close();
4089        }
4090    }
4091
4092    /**
4093     * Convert a mimetype into an integer, using {@link Tables#MIMETYPES} for
4094     * lookups and possible allocation of new IDs as needed.
4095     */
4096    public long getMimeTypeId(String mimetype) {
4097        // Try an in-memory cache lookup
4098        if (mMimetypeCache.containsKey(mimetype)) return mMimetypeCache.get(mimetype);
4099
4100        return lookupMimeTypeId(mimetype, getWritableDatabase());
4101    }
4102
4103    private long lookupMimeTypeId(String mimetype, SQLiteDatabase db) {
4104        final SQLiteStatement mimetypeQuery = db.compileStatement(
4105                "SELECT " + MimetypesColumns._ID +
4106                " FROM " + Tables.MIMETYPES +
4107                " WHERE " + MimetypesColumns.MIMETYPE + "=?");
4108
4109        final SQLiteStatement mimetypeInsert = db.compileStatement(
4110                "INSERT INTO " + Tables.MIMETYPES + "("
4111                        + MimetypesColumns.MIMETYPE +
4112                ") VALUES (?)");
4113
4114        try {
4115            return lookupAndCacheId(mimetypeQuery, mimetypeInsert, mimetype, mMimetypeCache);
4116        } finally {
4117            mimetypeQuery.close();
4118            mimetypeInsert.close();
4119        }
4120    }
4121
4122    public long getMimeTypeIdForStructuredName() {
4123        return mMimeTypeIdStructuredName;
4124    }
4125
4126    public long getMimeTypeIdForStructuredPostal() {
4127        return mMimeTypeIdStructuredPostal;
4128    }
4129
4130    public long getMimeTypeIdForOrganization() {
4131        return mMimeTypeIdOrganization;
4132    }
4133
4134    public long getMimeTypeIdForIm() {
4135        return mMimeTypeIdIm;
4136    }
4137
4138    public long getMimeTypeIdForEmail() {
4139        return mMimeTypeIdEmail;
4140    }
4141
4142    public long getMimeTypeIdForPhone() {
4143        return mMimeTypeIdPhone;
4144    }
4145
4146    public long getMimeTypeIdForSip() {
4147        return mMimeTypeIdSip;
4148    }
4149
4150    public int getDisplayNameSourceForMimeTypeId(int mimeTypeId) {
4151        if (mimeTypeId == mMimeTypeIdStructuredName) {
4152            return DisplayNameSources.STRUCTURED_NAME;
4153        } else if (mimeTypeId == mMimeTypeIdEmail) {
4154            return DisplayNameSources.EMAIL;
4155        } else if (mimeTypeId == mMimeTypeIdPhone) {
4156            return DisplayNameSources.PHONE;
4157        } else if (mimeTypeId == mMimeTypeIdOrganization) {
4158            return DisplayNameSources.ORGANIZATION;
4159        } else if (mimeTypeId == mMimeTypeIdNickname) {
4160            return DisplayNameSources.NICKNAME;
4161        } else {
4162            return DisplayNameSources.UNDEFINED;
4163        }
4164    }
4165
4166    /**
4167     * Find the mimetype for the given {@link Data#_ID}.
4168     */
4169    public String getDataMimeType(long dataId) {
4170        if (mDataMimetypeQuery == null) {
4171            mDataMimetypeQuery = getWritableDatabase().compileStatement(
4172                    "SELECT " + MimetypesColumns.MIMETYPE +
4173                    " FROM " + Tables.DATA_JOIN_MIMETYPES +
4174                    " WHERE " + Tables.DATA + "." + Data._ID + "=?");
4175        }
4176        try {
4177            // Try database query to find mimetype
4178            DatabaseUtils.bindObjectToProgram(mDataMimetypeQuery, 1, dataId);
4179            String mimetype = mDataMimetypeQuery.simpleQueryForString();
4180            return mimetype;
4181        } catch (SQLiteDoneException e) {
4182            // No valid mapping found, so return null
4183            return null;
4184        }
4185    }
4186
4187    public void invalidateAllCache() {
4188        Log.w(TAG, "invalidateAllCache: [" + getClass().getSimpleName() + "]");
4189
4190        mMimetypeCache.clear();
4191        mPackageCache.clear();
4192    }
4193
4194    /**
4195     * Gets all accounts in the accounts table.
4196     */
4197    public Set<AccountWithDataSet> getAllAccountsWithDataSets() {
4198        final Set<AccountWithDataSet> result = Sets.newHashSet();
4199        Cursor c = getReadableDatabase().rawQuery(
4200                "SELECT DISTINCT " +  AccountsColumns._ID + "," + AccountsColumns.ACCOUNT_NAME +
4201                "," + AccountsColumns.ACCOUNT_TYPE + "," + AccountsColumns.DATA_SET +
4202                " FROM " + Tables.ACCOUNTS, null);
4203        try {
4204            while (c.moveToNext()) {
4205                result.add(AccountWithDataSet.get(c.getString(1), c.getString(2), c.getString(3)));
4206            }
4207        } finally {
4208            c.close();
4209        }
4210        return result;
4211    }
4212
4213    /**
4214     * @return ID of the specified account, or null if the account doesn't exist.
4215     */
4216    public Long getAccountIdOrNull(AccountWithDataSet accountWithDataSet) {
4217        if (accountWithDataSet == null) {
4218            accountWithDataSet = AccountWithDataSet.LOCAL;
4219        }
4220        final SQLiteStatement select = getWritableDatabase().compileStatement(
4221                "SELECT " + AccountsColumns._ID +
4222                " FROM " + Tables.ACCOUNTS +
4223                " WHERE " +
4224                "((?1 IS NULL AND " + AccountsColumns.ACCOUNT_NAME + " IS NULL) OR " +
4225                "(" + AccountsColumns.ACCOUNT_NAME + "=?1)) AND " +
4226                "((?2 IS NULL AND " + AccountsColumns.ACCOUNT_TYPE + " IS NULL) OR " +
4227                "(" + AccountsColumns.ACCOUNT_TYPE + "=?2)) AND " +
4228                "((?3 IS NULL AND " + AccountsColumns.DATA_SET + " IS NULL) OR " +
4229                "(" + AccountsColumns.DATA_SET + "=?3))");
4230        try {
4231            DatabaseUtils.bindObjectToProgram(select, 1, accountWithDataSet.getAccountName());
4232            DatabaseUtils.bindObjectToProgram(select, 2, accountWithDataSet.getAccountType());
4233            DatabaseUtils.bindObjectToProgram(select, 3, accountWithDataSet.getDataSet());
4234            try {
4235                return select.simpleQueryForLong();
4236            } catch (SQLiteDoneException notFound) {
4237                return null;
4238            }
4239        } finally {
4240            select.close();
4241        }
4242    }
4243
4244    /**
4245     * @return ID of the specified account.  This method will create a record in the accounts table
4246     *     if the account doesn't exist in the accounts table.
4247     *
4248     * This must be used in a transaction, so there's no need for synchronization.
4249     */
4250    public long getOrCreateAccountIdInTransaction(AccountWithDataSet accountWithDataSet) {
4251        if (accountWithDataSet == null) {
4252            accountWithDataSet = AccountWithDataSet.LOCAL;
4253        }
4254        Long id = getAccountIdOrNull(accountWithDataSet);
4255        if (id != null) {
4256            return id;
4257        }
4258        final SQLiteStatement insert = getWritableDatabase().compileStatement(
4259                "INSERT INTO " + Tables.ACCOUNTS +
4260                " (" + AccountsColumns.ACCOUNT_NAME + ", " +
4261                AccountsColumns.ACCOUNT_TYPE + ", " +
4262                AccountsColumns.DATA_SET + ") VALUES (?, ?, ?)");
4263        try {
4264            DatabaseUtils.bindObjectToProgram(insert, 1, accountWithDataSet.getAccountName());
4265            DatabaseUtils.bindObjectToProgram(insert, 2, accountWithDataSet.getAccountType());
4266            DatabaseUtils.bindObjectToProgram(insert, 3, accountWithDataSet.getDataSet());
4267            id = insert.executeInsert();
4268        } finally {
4269            insert.close();
4270        }
4271
4272        return id;
4273    }
4274
4275    /**
4276     * Update {@link Contacts#IN_VISIBLE_GROUP} for all contacts.
4277     */
4278    public void updateAllVisible() {
4279        updateCustomContactVisibility(getWritableDatabase(), -1);
4280    }
4281
4282    /**
4283     * Updates contact visibility and return true iff the visibility was actually changed.
4284     */
4285    public boolean updateContactVisibleOnlyIfChanged(TransactionContext txContext, long contactId) {
4286        return updateContactVisible(txContext, contactId, true);
4287    }
4288
4289    /**
4290     * Update {@link Contacts#IN_VISIBLE_GROUP} and
4291     * {@link Tables#DEFAULT_DIRECTORY} for a specific contact.
4292     */
4293    public void updateContactVisible(TransactionContext txContext, long contactId) {
4294        updateContactVisible(txContext, contactId, false);
4295    }
4296
4297    public boolean updateContactVisible(
4298            TransactionContext txContext, long contactId, boolean onlyIfChanged) {
4299        SQLiteDatabase db = getWritableDatabase();
4300        updateCustomContactVisibility(db, contactId);
4301
4302        String contactIdAsString = String.valueOf(contactId);
4303        long mimetype = getMimeTypeId(GroupMembership.CONTENT_ITEM_TYPE);
4304
4305        // The contact will be included in the default directory if contains
4306        // a raw contact that is in any group or in an account that
4307        // does not have any AUTO_ADD groups.
4308        boolean newVisibility = DatabaseUtils.longForQuery(db,
4309                "SELECT EXISTS (" +
4310                    "SELECT " + RawContacts.CONTACT_ID +
4311                    " FROM " + Tables.RAW_CONTACTS +
4312                    " JOIN " + Tables.DATA +
4313                    "   ON (" + RawContactsColumns.CONCRETE_ID + "="
4314                            + Data.RAW_CONTACT_ID + ")" +
4315                    " WHERE " + RawContacts.CONTACT_ID + "=?1" +
4316                    "   AND " + DataColumns.MIMETYPE_ID + "=?2" +
4317                ") OR EXISTS (" +
4318                    "SELECT " + RawContacts._ID +
4319                    " FROM " + Tables.RAW_CONTACTS +
4320                    " WHERE " + RawContacts.CONTACT_ID + "=?1" +
4321                    "   AND NOT EXISTS" +
4322                        " (SELECT " + Groups._ID +
4323                        "  FROM " + Tables.GROUPS +
4324                        "  WHERE " + RawContactsColumns.CONCRETE_ACCOUNT_ID + " = "
4325                                + GroupsColumns.CONCRETE_ACCOUNT_ID +
4326                        "  AND " + Groups.AUTO_ADD + " != 0" +
4327                        ")" +
4328                ") OR EXISTS (" +
4329                    "SELECT " + RawContacts._ID +
4330                    " FROM " + Tables.RAW_CONTACTS +
4331                    " WHERE " + RawContacts.CONTACT_ID + "=?1" +
4332                    "   AND " + RawContactsColumns.CONCRETE_ACCOUNT_ID + "=" +
4333                        Clauses.LOCAL_ACCOUNT_ID +
4334                ")",
4335                new String[] {
4336                    contactIdAsString,
4337                    String.valueOf(mimetype)
4338                }) != 0;
4339
4340        if (onlyIfChanged) {
4341            boolean oldVisibility = isContactInDefaultDirectory(db, contactId);
4342            if (oldVisibility == newVisibility) {
4343                return false;
4344            }
4345        }
4346
4347        if (newVisibility) {
4348            db.execSQL("INSERT OR IGNORE INTO " + Tables.DEFAULT_DIRECTORY + " VALUES(?)",
4349                    new String[] { contactIdAsString });
4350            txContext.invalidateSearchIndexForContact(contactId);
4351        } else {
4352            db.execSQL("DELETE FROM " + Tables.DEFAULT_DIRECTORY +
4353                        " WHERE " + Contacts._ID + "=?",
4354                    new String[] { contactIdAsString });
4355            db.execSQL("DELETE FROM " + Tables.SEARCH_INDEX +
4356                        " WHERE " + SearchIndexColumns.CONTACT_ID + "=CAST(? AS int)",
4357                    new String[] { contactIdAsString });
4358        }
4359        return true;
4360    }
4361
4362    public boolean isContactInDefaultDirectory(SQLiteDatabase db, long contactId) {
4363        if (mContactInDefaultDirectoryQuery == null) {
4364            mContactInDefaultDirectoryQuery = db.compileStatement(
4365                    "SELECT EXISTS (" +
4366                            "SELECT 1 FROM " + Tables.DEFAULT_DIRECTORY +
4367                            " WHERE " + Contacts._ID + "=?)");
4368        }
4369        mContactInDefaultDirectoryQuery.bindLong(1, contactId);
4370        return mContactInDefaultDirectoryQuery.simpleQueryForLong() != 0;
4371    }
4372
4373    /**
4374     * Update the visible_contacts table according to the current visibility of contacts, which
4375     * is defined by {@link Clauses#CONTACT_IS_VISIBLE}.
4376     *
4377     * If {@code optionalContactId} is non-negative, it'll update only for the specified contact.
4378     */
4379    private void updateCustomContactVisibility(SQLiteDatabase db, long optionalContactId) {
4380        final long groupMembershipMimetypeId = getMimeTypeId(GroupMembership.CONTENT_ITEM_TYPE);
4381        String[] selectionArgs = new String[]{String.valueOf(groupMembershipMimetypeId)};
4382
4383        final String contactIdSelect = (optionalContactId < 0) ? "" :
4384                (Contacts._ID + "=" + optionalContactId + " AND ");
4385
4386        // First delete what needs to be deleted, then insert what needs to be added.
4387        // Since flash writes are very expensive, this approach is much better than
4388        // delete-all-insert-all.
4389        db.execSQL(
4390                "DELETE FROM " + Tables.VISIBLE_CONTACTS +
4391                " WHERE " + Contacts._ID + " IN" +
4392                    "(SELECT " + Contacts._ID +
4393                    " FROM " + Tables.CONTACTS +
4394                    " WHERE " + contactIdSelect + "(" + Clauses.CONTACT_IS_VISIBLE + ")=0) ",
4395                selectionArgs);
4396
4397        db.execSQL(
4398                "INSERT INTO " + Tables.VISIBLE_CONTACTS +
4399                " SELECT " + Contacts._ID +
4400                " FROM " + Tables.CONTACTS +
4401                " WHERE " +
4402                    contactIdSelect +
4403                    Contacts._ID + " NOT IN " + Tables.VISIBLE_CONTACTS +
4404                    " AND (" + Clauses.CONTACT_IS_VISIBLE + ")=1 ",
4405                selectionArgs);
4406    }
4407
4408    /**
4409     * Returns contact ID for the given contact or zero if it is NULL.
4410     */
4411    public long getContactId(long rawContactId) {
4412        if (mContactIdQuery == null) {
4413            mContactIdQuery = getWritableDatabase().compileStatement(
4414                    "SELECT " + RawContacts.CONTACT_ID +
4415                    " FROM " + Tables.RAW_CONTACTS +
4416                    " WHERE " + RawContacts._ID + "=?");
4417        }
4418        try {
4419            DatabaseUtils.bindObjectToProgram(mContactIdQuery, 1, rawContactId);
4420            return mContactIdQuery.simpleQueryForLong();
4421        } catch (SQLiteDoneException e) {
4422            // No valid mapping found, so return 0
4423            return 0;
4424        }
4425    }
4426
4427    public int getAggregationMode(long rawContactId) {
4428        if (mAggregationModeQuery == null) {
4429            mAggregationModeQuery = getWritableDatabase().compileStatement(
4430                    "SELECT " + RawContacts.AGGREGATION_MODE +
4431                    " FROM " + Tables.RAW_CONTACTS +
4432                    " WHERE " + RawContacts._ID + "=?");
4433        }
4434        try {
4435            DatabaseUtils.bindObjectToProgram(mAggregationModeQuery, 1, rawContactId);
4436            return (int)mAggregationModeQuery.simpleQueryForLong();
4437        } catch (SQLiteDoneException e) {
4438            // No valid row found, so return "disabled"
4439            return RawContacts.AGGREGATION_MODE_DISABLED;
4440        }
4441    }
4442
4443    public void buildPhoneLookupAndContactQuery(
4444            SQLiteQueryBuilder qb, String normalizedNumber, String numberE164) {
4445        String minMatch = PhoneNumberUtils.toCallerIDMinMatch(normalizedNumber);
4446        StringBuilder sb = new StringBuilder();
4447        appendPhoneLookupTables(sb, minMatch, true);
4448        qb.setTables(sb.toString());
4449
4450        sb = new StringBuilder();
4451        appendPhoneLookupSelection(sb, normalizedNumber, numberE164);
4452        qb.appendWhere(sb.toString());
4453    }
4454
4455    /**
4456     * As opposed to {@link #buildPhoneLookupAndContactQuery}, this phone lookup will only do
4457     * a comparison based on the last seven digits of the given phone number.  This is only intended
4458     * to be used as a fallback, in case the regular lookup does not return any results.
4459     * @param qb The query builder.
4460     * @param number The phone number to search for.
4461     */
4462    public void buildMinimalPhoneLookupAndContactQuery(SQLiteQueryBuilder qb, String number) {
4463        String minMatch = PhoneNumberUtils.toCallerIDMinMatch(number);
4464        StringBuilder sb = new StringBuilder();
4465        appendPhoneLookupTables(sb, minMatch, true);
4466        qb.setTables(sb.toString());
4467
4468        sb = new StringBuilder();
4469        appendPhoneLookupSelection(sb, null, null);
4470        qb.appendWhere(sb.toString());
4471    }
4472
4473    /**
4474     * Adds query for selecting the contact with the given {@code sipAddress} to the given
4475     * {@link StringBuilder}.
4476     *
4477     * @return the query arguments to be passed in with the query
4478     */
4479    public String[] buildSipContactQuery(StringBuilder sb, String sipAddress) {
4480        sb.append("upper(");
4481        sb.append(Data.DATA1);
4482        sb.append(")=upper(?) AND ");
4483        sb.append(DataColumns.MIMETYPE_ID);
4484        sb.append("=");
4485        sb.append(Long.toString(getMimeTypeIdForSip()));
4486        // Return the arguments to be passed to the query.
4487        return new String[]{ sipAddress };
4488    }
4489
4490    public String buildPhoneLookupAsNestedQuery(String number) {
4491        StringBuilder sb = new StringBuilder();
4492        final String minMatch = PhoneNumberUtils.toCallerIDMinMatch(number);
4493        sb.append("(SELECT DISTINCT raw_contact_id" + " FROM ");
4494        appendPhoneLookupTables(sb, minMatch, false);
4495        sb.append(" WHERE ");
4496        appendPhoneLookupSelection(sb, number, null);
4497        sb.append(")");
4498        return sb.toString();
4499    }
4500
4501    private void appendPhoneLookupTables(StringBuilder sb, final String minMatch,
4502            boolean joinContacts) {
4503        sb.append(Tables.RAW_CONTACTS);
4504        if (joinContacts) {
4505            sb.append(" JOIN " + Views.CONTACTS + " contacts_view"
4506                    + " ON (contacts_view._id = raw_contacts.contact_id)");
4507        }
4508        sb.append(", (SELECT data_id, normalized_number, length(normalized_number) as len "
4509                + " FROM phone_lookup " + " WHERE (" + Tables.PHONE_LOOKUP + "."
4510                + PhoneLookupColumns.MIN_MATCH + " = '");
4511        sb.append(minMatch);
4512        sb.append("')) AS lookup, " + Tables.DATA);
4513    }
4514
4515    private void appendPhoneLookupSelection(StringBuilder sb, String number, String numberE164) {
4516        sb.append("lookup.data_id=data._id AND data.raw_contact_id=raw_contacts._id");
4517        boolean hasNumberE164 = !TextUtils.isEmpty(numberE164);
4518        boolean hasNumber = !TextUtils.isEmpty(number);
4519        if (hasNumberE164 || hasNumber) {
4520            sb.append(" AND ( ");
4521            if (hasNumberE164) {
4522                sb.append(" lookup.normalized_number = ");
4523                DatabaseUtils.appendEscapedSQLString(sb, numberE164);
4524            }
4525            if (hasNumberE164 && hasNumber) {
4526                sb.append(" OR ");
4527            }
4528            if (hasNumber) {
4529                int numberLen = number.length();
4530                sb.append(" lookup.len <= ");
4531                sb.append(numberLen);
4532                sb.append(" AND substr(");
4533                DatabaseUtils.appendEscapedSQLString(sb, number);
4534                sb.append(',');
4535                sb.append(numberLen);
4536                sb.append(" - lookup.len + 1) = lookup.normalized_number");
4537
4538                // Some countries (e.g. Brazil) can have incoming calls which contain only the local
4539                // number (no country calling code and no area code). This case is handled below.
4540                // Details see b/5197612.
4541                // This also handles a Gingerbread -> ICS upgrade issue; see b/5638376.
4542                sb.append(" OR (");
4543                sb.append(" lookup.len > ");
4544                sb.append(numberLen);
4545                sb.append(" AND substr(lookup.normalized_number,");
4546                sb.append("lookup.len + 1 - ");
4547                sb.append(numberLen);
4548                sb.append(") = ");
4549                DatabaseUtils.appendEscapedSQLString(sb, number);
4550                sb.append(")");
4551            }
4552            sb.append(')');
4553        }
4554    }
4555
4556    public String getUseStrictPhoneNumberComparisonParameter() {
4557        return mUseStrictPhoneNumberComparison ? "1" : "0";
4558    }
4559
4560    /**
4561     * Loads common nickname mappings into the database.
4562     */
4563    private void loadNicknameLookupTable(SQLiteDatabase db) {
4564        db.execSQL("DELETE FROM " + Tables.NICKNAME_LOOKUP);
4565
4566        String[] strings = mContext.getResources().getStringArray(
4567                com.android.internal.R.array.common_nicknames);
4568        if (strings == null || strings.length == 0) {
4569            return;
4570        }
4571
4572        SQLiteStatement nicknameLookupInsert = db.compileStatement("INSERT INTO "
4573                + Tables.NICKNAME_LOOKUP + "(" + NicknameLookupColumns.NAME + ","
4574                + NicknameLookupColumns.CLUSTER + ") VALUES (?,?)");
4575
4576        try {
4577            for (int clusterId = 0; clusterId < strings.length; clusterId++) {
4578                String[] names = strings[clusterId].split(",");
4579                for (int j = 0; j < names.length; j++) {
4580                    String name = NameNormalizer.normalize(names[j]);
4581                    try {
4582                        DatabaseUtils.bindObjectToProgram(nicknameLookupInsert, 1, name);
4583                        DatabaseUtils.bindObjectToProgram(nicknameLookupInsert, 2,
4584                                String.valueOf(clusterId));
4585                        nicknameLookupInsert.executeInsert();
4586                    } catch (SQLiteException e) {
4587
4588                        // Print the exception and keep going - this is not a fatal error
4589                        Log.e(TAG, "Cannot insert nickname: " + names[j], e);
4590                    }
4591                }
4592            }
4593        } finally {
4594            nicknameLookupInsert.close();
4595        }
4596    }
4597
4598    public static void copyStringValue(ContentValues toValues, String toKey,
4599            ContentValues fromValues, String fromKey) {
4600        if (fromValues.containsKey(fromKey)) {
4601            toValues.put(toKey, fromValues.getAsString(fromKey));
4602        }
4603    }
4604
4605    public static void copyLongValue(ContentValues toValues, String toKey,
4606            ContentValues fromValues, String fromKey) {
4607        if (fromValues.containsKey(fromKey)) {
4608            long longValue;
4609            Object value = fromValues.get(fromKey);
4610            if (value instanceof Boolean) {
4611                if ((Boolean)value) {
4612                    longValue = 1;
4613                } else {
4614                    longValue = 0;
4615                }
4616            } else if (value instanceof String) {
4617                longValue = Long.parseLong((String)value);
4618            } else {
4619                longValue = ((Number)value).longValue();
4620            }
4621            toValues.put(toKey, longValue);
4622        }
4623    }
4624
4625    public SyncStateContentProviderHelper getSyncState() {
4626        return mSyncState;
4627    }
4628
4629    /**
4630     * Delete the aggregate contact if it has no constituent raw contacts other
4631     * than the supplied one.
4632     */
4633    public void removeContactIfSingleton(long rawContactId) {
4634        SQLiteDatabase db = getWritableDatabase();
4635
4636        // Obtain contact ID from the supplied raw contact ID
4637        String contactIdFromRawContactId = "(SELECT " + RawContacts.CONTACT_ID + " FROM "
4638                + Tables.RAW_CONTACTS + " WHERE " + RawContacts._ID + "=" + rawContactId + ")";
4639
4640        // Find other raw contacts in the same aggregate contact
4641        String otherRawContacts = "(SELECT contacts1." + RawContacts._ID + " FROM "
4642                + Tables.RAW_CONTACTS + " contacts1 JOIN " + Tables.RAW_CONTACTS + " contacts2 ON ("
4643                + "contacts1." + RawContacts.CONTACT_ID + "=contacts2." + RawContacts.CONTACT_ID
4644                + ") WHERE contacts1." + RawContacts._ID + "!=" + rawContactId + ""
4645                + " AND contacts2." + RawContacts._ID + "=" + rawContactId + ")";
4646
4647        db.execSQL("DELETE FROM " + Tables.CONTACTS
4648                + " WHERE " + Contacts._ID + "=" + contactIdFromRawContactId
4649                + " AND NOT EXISTS " + otherRawContacts + ";");
4650    }
4651
4652    /**
4653     * Returns the value from the {@link Tables#PROPERTIES} table.
4654     */
4655    public String getProperty(String key, String defaultValue) {
4656        Cursor cursor = getReadableDatabase().query(Tables.PROPERTIES,
4657                new String[]{PropertiesColumns.PROPERTY_VALUE},
4658                PropertiesColumns.PROPERTY_KEY + "=?",
4659                new String[]{key}, null, null, null);
4660        String value = null;
4661        try {
4662            if (cursor.moveToFirst()) {
4663                value = cursor.getString(0);
4664            }
4665        } finally {
4666            cursor.close();
4667        }
4668
4669        return value != null ? value : defaultValue;
4670    }
4671
4672    /**
4673     * Stores a key-value pair in the {@link Tables#PROPERTIES} table.
4674     */
4675    public void setProperty(String key, String value) {
4676        setProperty(getWritableDatabase(), key, value);
4677    }
4678
4679    private void setProperty(SQLiteDatabase db, String key, String value) {
4680        ContentValues values = new ContentValues();
4681        values.put(PropertiesColumns.PROPERTY_KEY, key);
4682        values.put(PropertiesColumns.PROPERTY_VALUE, value);
4683        db.replace(Tables.PROPERTIES, null, values);
4684    }
4685
4686    /**
4687     * Test if the given column appears in the given projection.
4688     */
4689    public static boolean isInProjection(String[] projection, String column) {
4690        if (projection == null) {
4691            return true; // Null means "all columns".  We can't really tell if it's in there...
4692        }
4693        for (String test : projection) {
4694            if (column.equals(test)) {
4695                return true;
4696            }
4697        }
4698        return false;
4699    }
4700
4701    /**
4702     * Test if any of the columns appear in the given projection.
4703     */
4704    public static boolean isInProjection(String[] projection, String... columns) {
4705        if (projection == null) {
4706            return true;
4707        }
4708
4709        // Optimized for a single-column test
4710        if (columns.length == 1) {
4711            return isInProjection(projection, columns[0]);
4712        } else {
4713            for (String test : projection) {
4714                for (String column : columns) {
4715                    if (column.equals(test)) {
4716                        return true;
4717                    }
4718                }
4719            }
4720        }
4721        return false;
4722    }
4723
4724    /**
4725     * Returns a detailed exception message for the supplied URI.  It includes the calling
4726     * user and calling package(s).
4727     */
4728    public String exceptionMessage(Uri uri) {
4729        return exceptionMessage(null, uri);
4730    }
4731
4732    /**
4733     * Returns a detailed exception message for the supplied URI.  It includes the calling
4734     * user and calling package(s).
4735     */
4736    public String exceptionMessage(String message, Uri uri) {
4737        StringBuilder sb = new StringBuilder();
4738        if (message != null) {
4739            sb.append(message).append("; ");
4740        }
4741        sb.append("URI: ").append(uri);
4742        final PackageManager pm = mContext.getPackageManager();
4743        int callingUid = Binder.getCallingUid();
4744        sb.append(", calling user: ");
4745        String userName = pm.getNameForUid(callingUid);
4746        if (userName != null) {
4747            sb.append(userName);
4748        } else {
4749            sb.append(callingUid);
4750        }
4751
4752        final String[] callerPackages = pm.getPackagesForUid(callingUid);
4753        if (callerPackages != null && callerPackages.length > 0) {
4754            if (callerPackages.length == 1) {
4755                sb.append(", calling package:");
4756                sb.append(callerPackages[0]);
4757            } else {
4758                sb.append(", calling package is one of: [");
4759                for (int i = 0; i < callerPackages.length; i++) {
4760                    if (i != 0) {
4761                        sb.append(", ");
4762                    }
4763                    sb.append(callerPackages[i]);
4764                }
4765                sb.append("]");
4766            }
4767        }
4768
4769        return sb.toString();
4770    }
4771
4772    protected String getCountryIso() {
4773        CountryDetector detector =
4774            (CountryDetector) mContext.getSystemService(Context.COUNTRY_DETECTOR);
4775        return detector.detectCountry().getCountryIso();
4776    }
4777
4778    public void deleteStatusUpdate(long dataId) {
4779        if (mStatusUpdateDelete == null) {
4780            mStatusUpdateDelete = getWritableDatabase().compileStatement(
4781                    "DELETE FROM " + Tables.STATUS_UPDATES +
4782                    " WHERE " + StatusUpdatesColumns.DATA_ID + "=?");
4783        }
4784        mStatusUpdateDelete.bindLong(1, dataId);
4785        mStatusUpdateDelete.execute();
4786    }
4787
4788    public void replaceStatusUpdate(Long dataId, long timestamp, String status, String resPackage,
4789            Integer iconResource, Integer labelResource) {
4790        if (mStatusUpdateReplace == null) {
4791            mStatusUpdateReplace = getWritableDatabase().compileStatement(
4792                    "INSERT OR REPLACE INTO " + Tables.STATUS_UPDATES + "("
4793                            + StatusUpdatesColumns.DATA_ID + ", "
4794                            + StatusUpdates.STATUS_TIMESTAMP + ","
4795                            + StatusUpdates.STATUS + ","
4796                            + StatusUpdates.STATUS_RES_PACKAGE + ","
4797                            + StatusUpdates.STATUS_ICON + ","
4798                            + StatusUpdates.STATUS_LABEL + ")" +
4799                    " VALUES (?,?,?,?,?,?)");
4800        }
4801        mStatusUpdateReplace.bindLong(1, dataId);
4802        mStatusUpdateReplace.bindLong(2, timestamp);
4803        bindString(mStatusUpdateReplace, 3, status);
4804        bindString(mStatusUpdateReplace, 4, resPackage);
4805        bindLong(mStatusUpdateReplace, 5, iconResource);
4806        bindLong(mStatusUpdateReplace, 6, labelResource);
4807        mStatusUpdateReplace.execute();
4808    }
4809
4810    public void insertStatusUpdate(Long dataId, String status, String resPackage,
4811            Integer iconResource, Integer labelResource) {
4812        if (mStatusUpdateInsert == null) {
4813            mStatusUpdateInsert = getWritableDatabase().compileStatement(
4814                    "INSERT INTO " + Tables.STATUS_UPDATES + "("
4815                            + StatusUpdatesColumns.DATA_ID + ", "
4816                            + StatusUpdates.STATUS + ","
4817                            + StatusUpdates.STATUS_RES_PACKAGE + ","
4818                            + StatusUpdates.STATUS_ICON + ","
4819                            + StatusUpdates.STATUS_LABEL + ")" +
4820                    " VALUES (?,?,?,?,?)");
4821        }
4822        try {
4823            mStatusUpdateInsert.bindLong(1, dataId);
4824            bindString(mStatusUpdateInsert, 2, status);
4825            bindString(mStatusUpdateInsert, 3, resPackage);
4826            bindLong(mStatusUpdateInsert, 4, iconResource);
4827            bindLong(mStatusUpdateInsert, 5, labelResource);
4828            mStatusUpdateInsert.executeInsert();
4829        } catch (SQLiteConstraintException e) {
4830            // The row already exists - update it
4831            if (mStatusUpdateAutoTimestamp == null) {
4832                mStatusUpdateAutoTimestamp = getWritableDatabase().compileStatement(
4833                        "UPDATE " + Tables.STATUS_UPDATES +
4834                        " SET " + StatusUpdates.STATUS_TIMESTAMP + "=?,"
4835                                + StatusUpdates.STATUS + "=?" +
4836                        " WHERE " + StatusUpdatesColumns.DATA_ID + "=?"
4837                                + " AND " + StatusUpdates.STATUS + "!=?");
4838            }
4839
4840            long timestamp = System.currentTimeMillis();
4841            mStatusUpdateAutoTimestamp.bindLong(1, timestamp);
4842            bindString(mStatusUpdateAutoTimestamp, 2, status);
4843            mStatusUpdateAutoTimestamp.bindLong(3, dataId);
4844            bindString(mStatusUpdateAutoTimestamp, 4, status);
4845            mStatusUpdateAutoTimestamp.execute();
4846
4847            if (mStatusAttributionUpdate == null) {
4848                mStatusAttributionUpdate = getWritableDatabase().compileStatement(
4849                        "UPDATE " + Tables.STATUS_UPDATES +
4850                        " SET " + StatusUpdates.STATUS_RES_PACKAGE + "=?,"
4851                                + StatusUpdates.STATUS_ICON + "=?,"
4852                                + StatusUpdates.STATUS_LABEL + "=?" +
4853                        " WHERE " + StatusUpdatesColumns.DATA_ID + "=?");
4854            }
4855            bindString(mStatusAttributionUpdate, 1, resPackage);
4856            bindLong(mStatusAttributionUpdate, 2, iconResource);
4857            bindLong(mStatusAttributionUpdate, 3, labelResource);
4858            mStatusAttributionUpdate.bindLong(4, dataId);
4859            mStatusAttributionUpdate.execute();
4860        }
4861    }
4862
4863    /**
4864     * Resets the {@link RawContacts#NAME_VERIFIED} flag to 0 on all other raw
4865     * contacts in the same aggregate
4866     */
4867    public void resetNameVerifiedForOtherRawContacts(long rawContactId) {
4868        if (mResetNameVerifiedForOtherRawContacts == null) {
4869            mResetNameVerifiedForOtherRawContacts = getWritableDatabase().compileStatement(
4870                    "UPDATE " + Tables.RAW_CONTACTS +
4871                    " SET " + RawContacts.NAME_VERIFIED + "=0" +
4872                    " WHERE " + RawContacts.CONTACT_ID + "=(" +
4873                            "SELECT " + RawContacts.CONTACT_ID +
4874                            " FROM " + Tables.RAW_CONTACTS +
4875                            " WHERE " + RawContacts._ID + "=?)" +
4876                    " AND " + RawContacts._ID + "!=?");
4877        }
4878        mResetNameVerifiedForOtherRawContacts.bindLong(1, rawContactId);
4879        mResetNameVerifiedForOtherRawContacts.bindLong(2, rawContactId);
4880        mResetNameVerifiedForOtherRawContacts.execute();
4881    }
4882
4883    private interface RawContactNameQuery {
4884        public static final String RAW_SQL =
4885                "SELECT "
4886                        + DataColumns.MIMETYPE_ID + ","
4887                        + Data.IS_PRIMARY + ","
4888                        + Data.DATA1 + ","
4889                        + Data.DATA2 + ","
4890                        + Data.DATA3 + ","
4891                        + Data.DATA4 + ","
4892                        + Data.DATA5 + ","
4893                        + Data.DATA6 + ","
4894                        + Data.DATA7 + ","
4895                        + Data.DATA8 + ","
4896                        + Data.DATA9 + ","
4897                        + Data.DATA10 + ","
4898                        + Data.DATA11 +
4899                " FROM " + Tables.DATA +
4900                " WHERE " + Data.RAW_CONTACT_ID + "=?" +
4901                        " AND (" + Data.DATA1 + " NOT NULL OR " +
4902                                Organization.TITLE + " NOT NULL)";
4903
4904        public static final int MIMETYPE = 0;
4905        public static final int IS_PRIMARY = 1;
4906        public static final int DATA1 = 2;
4907        public static final int GIVEN_NAME = 3;                         // data2
4908        public static final int FAMILY_NAME = 4;                        // data3
4909        public static final int PREFIX = 5;                             // data4
4910        public static final int TITLE = 5;                              // data4
4911        public static final int MIDDLE_NAME = 6;                        // data5
4912        public static final int SUFFIX = 7;                             // data6
4913        public static final int PHONETIC_GIVEN_NAME = 8;                // data7
4914        public static final int PHONETIC_MIDDLE_NAME = 9;               // data8
4915        public static final int ORGANIZATION_PHONETIC_NAME = 9;         // data8
4916        public static final int PHONETIC_FAMILY_NAME = 10;              // data9
4917        public static final int FULL_NAME_STYLE = 11;                   // data10
4918        public static final int ORGANIZATION_PHONETIC_NAME_STYLE = 11;  // data10
4919        public static final int PHONETIC_NAME_STYLE = 12;               // data11
4920    }
4921
4922    /**
4923     * Updates a raw contact display name based on data rows, e.g. structured name,
4924     * organization, email etc.
4925     */
4926    public void updateRawContactDisplayName(SQLiteDatabase db, long rawContactId) {
4927        if (mNameSplitter == null) {
4928            createNameSplitter();
4929        }
4930
4931        int bestDisplayNameSource = DisplayNameSources.UNDEFINED;
4932        NameSplitter.Name bestName = null;
4933        String bestDisplayName = null;
4934        String bestPhoneticName = null;
4935        int bestPhoneticNameStyle = PhoneticNameStyle.UNDEFINED;
4936
4937        mSelectionArgs1[0] = String.valueOf(rawContactId);
4938        Cursor c = db.rawQuery(RawContactNameQuery.RAW_SQL, mSelectionArgs1);
4939        try {
4940            while (c.moveToNext()) {
4941                int mimeType = c.getInt(RawContactNameQuery.MIMETYPE);
4942                int source = getDisplayNameSourceForMimeTypeId(mimeType);
4943                if (source < bestDisplayNameSource || source == DisplayNameSources.UNDEFINED) {
4944                    continue;
4945                }
4946
4947                if (source == bestDisplayNameSource
4948                        && c.getInt(RawContactNameQuery.IS_PRIMARY) == 0) {
4949                    continue;
4950                }
4951
4952                if (mimeType == getMimeTypeIdForStructuredName()) {
4953                    NameSplitter.Name name;
4954                    if (bestName != null) {
4955                        name = new NameSplitter.Name();
4956                    } else {
4957                        name = mName;
4958                        name.clear();
4959                    }
4960                    name.prefix = c.getString(RawContactNameQuery.PREFIX);
4961                    name.givenNames = c.getString(RawContactNameQuery.GIVEN_NAME);
4962                    name.middleName = c.getString(RawContactNameQuery.MIDDLE_NAME);
4963                    name.familyName = c.getString(RawContactNameQuery.FAMILY_NAME);
4964                    name.suffix = c.getString(RawContactNameQuery.SUFFIX);
4965                    name.fullNameStyle = c.isNull(RawContactNameQuery.FULL_NAME_STYLE)
4966                            ? FullNameStyle.UNDEFINED
4967                            : c.getInt(RawContactNameQuery.FULL_NAME_STYLE);
4968                    name.phoneticFamilyName = c.getString(RawContactNameQuery.PHONETIC_FAMILY_NAME);
4969                    name.phoneticMiddleName = c.getString(RawContactNameQuery.PHONETIC_MIDDLE_NAME);
4970                    name.phoneticGivenName = c.getString(RawContactNameQuery.PHONETIC_GIVEN_NAME);
4971                    name.phoneticNameStyle = c.isNull(RawContactNameQuery.PHONETIC_NAME_STYLE)
4972                            ? PhoneticNameStyle.UNDEFINED
4973                            : c.getInt(RawContactNameQuery.PHONETIC_NAME_STYLE);
4974                    if (!name.isEmpty()) {
4975                        bestDisplayNameSource = source;
4976                        bestName = name;
4977                    }
4978                } else if (mimeType == getMimeTypeIdForOrganization()) {
4979                    mCharArrayBuffer.sizeCopied = 0;
4980                    c.copyStringToBuffer(RawContactNameQuery.DATA1, mCharArrayBuffer);
4981                    if (mCharArrayBuffer.sizeCopied != 0) {
4982                        bestDisplayNameSource = source;
4983                        bestDisplayName = new String(mCharArrayBuffer.data, 0,
4984                                mCharArrayBuffer.sizeCopied);
4985                        bestPhoneticName = c.getString(
4986                                RawContactNameQuery.ORGANIZATION_PHONETIC_NAME);
4987                        bestPhoneticNameStyle =
4988                                c.isNull(RawContactNameQuery.ORGANIZATION_PHONETIC_NAME_STYLE)
4989                                   ? PhoneticNameStyle.UNDEFINED
4990                                   : c.getInt(RawContactNameQuery.ORGANIZATION_PHONETIC_NAME_STYLE);
4991                    } else {
4992                        c.copyStringToBuffer(RawContactNameQuery.TITLE, mCharArrayBuffer);
4993                        if (mCharArrayBuffer.sizeCopied != 0) {
4994                            bestDisplayNameSource = source;
4995                            bestDisplayName = new String(mCharArrayBuffer.data, 0,
4996                                    mCharArrayBuffer.sizeCopied);
4997                            bestPhoneticName = null;
4998                            bestPhoneticNameStyle = PhoneticNameStyle.UNDEFINED;
4999                        }
5000                    }
5001                } else {
5002                    // Display name is at DATA1 in all other types.
5003                    // This is ensured in the constructor.
5004
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 = null;
5012                        bestPhoneticNameStyle = PhoneticNameStyle.UNDEFINED;
5013                    }
5014                }
5015            }
5016
5017        } finally {
5018            c.close();
5019        }
5020
5021        String displayNamePrimary;
5022        String displayNameAlternative;
5023        String sortNamePrimary;
5024        String sortNameAlternative;
5025        String sortKeyPrimary = null;
5026        String sortKeyAlternative = null;
5027        int displayNameStyle = FullNameStyle.UNDEFINED;
5028
5029        if (bestDisplayNameSource == DisplayNameSources.STRUCTURED_NAME) {
5030            displayNameStyle = bestName.fullNameStyle;
5031            if (displayNameStyle == FullNameStyle.CJK
5032                    || displayNameStyle == FullNameStyle.UNDEFINED) {
5033                displayNameStyle = mNameSplitter.getAdjustedFullNameStyle(displayNameStyle);
5034                bestName.fullNameStyle = displayNameStyle;
5035            }
5036
5037            displayNamePrimary = mNameSplitter.join(bestName, true, true);
5038            displayNameAlternative = mNameSplitter.join(bestName, false, true);
5039
5040            if (TextUtils.isEmpty(bestName.prefix)) {
5041                sortNamePrimary = displayNamePrimary;
5042                sortNameAlternative = displayNameAlternative;
5043            } else {
5044                sortNamePrimary = mNameSplitter.join(bestName, true, false);
5045                sortNameAlternative = mNameSplitter.join(bestName, false, false);
5046            }
5047
5048            bestPhoneticName = mNameSplitter.joinPhoneticName(bestName);
5049            bestPhoneticNameStyle = bestName.phoneticNameStyle;
5050        } else {
5051            displayNamePrimary = displayNameAlternative = bestDisplayName;
5052            sortNamePrimary = sortNameAlternative = bestDisplayName;
5053        }
5054
5055        if (bestPhoneticName != null) {
5056            sortKeyPrimary = sortKeyAlternative = bestPhoneticName;
5057            if (bestPhoneticNameStyle == PhoneticNameStyle.UNDEFINED) {
5058                bestPhoneticNameStyle = mNameSplitter.guessPhoneticNameStyle(bestPhoneticName);
5059            }
5060        } else {
5061            if (displayNameStyle == FullNameStyle.UNDEFINED) {
5062                displayNameStyle = mNameSplitter.guessFullNameStyle(bestDisplayName);
5063                if (displayNameStyle == FullNameStyle.UNDEFINED
5064                        || displayNameStyle == FullNameStyle.CJK) {
5065                    displayNameStyle = mNameSplitter.getAdjustedNameStyleBasedOnPhoneticNameStyle(
5066                            displayNameStyle, bestPhoneticNameStyle);
5067                }
5068                displayNameStyle = mNameSplitter.getAdjustedFullNameStyle(displayNameStyle);
5069            }
5070            if (displayNameStyle == FullNameStyle.CHINESE ||
5071                    displayNameStyle == FullNameStyle.CJK) {
5072                sortKeyPrimary = sortKeyAlternative =
5073                        ContactLocaleUtils.getIntance().getSortKey(
5074                                sortNamePrimary, displayNameStyle);
5075            }
5076        }
5077
5078        if (sortKeyPrimary == null) {
5079            sortKeyPrimary = sortNamePrimary;
5080            sortKeyAlternative = sortNameAlternative;
5081        }
5082
5083        if (mRawContactDisplayNameUpdate == null) {
5084            mRawContactDisplayNameUpdate = db.compileStatement(
5085                    "UPDATE " + Tables.RAW_CONTACTS +
5086                    " SET " +
5087                            RawContacts.DISPLAY_NAME_SOURCE + "=?," +
5088                            RawContacts.DISPLAY_NAME_PRIMARY + "=?," +
5089                            RawContacts.DISPLAY_NAME_ALTERNATIVE + "=?," +
5090                            RawContacts.PHONETIC_NAME + "=?," +
5091                            RawContacts.PHONETIC_NAME_STYLE + "=?," +
5092                            RawContacts.SORT_KEY_PRIMARY + "=?," +
5093                            RawContacts.SORT_KEY_ALTERNATIVE + "=?" +
5094                    " WHERE " + RawContacts._ID + "=?");
5095        }
5096
5097        mRawContactDisplayNameUpdate.bindLong(1, bestDisplayNameSource);
5098        bindString(mRawContactDisplayNameUpdate, 2, displayNamePrimary);
5099        bindString(mRawContactDisplayNameUpdate, 3, displayNameAlternative);
5100        bindString(mRawContactDisplayNameUpdate, 4, bestPhoneticName);
5101        mRawContactDisplayNameUpdate.bindLong(5, bestPhoneticNameStyle);
5102        bindString(mRawContactDisplayNameUpdate, 6, sortKeyPrimary);
5103        bindString(mRawContactDisplayNameUpdate, 7, sortKeyAlternative);
5104        mRawContactDisplayNameUpdate.bindLong(8, rawContactId);
5105        mRawContactDisplayNameUpdate.execute();
5106    }
5107
5108    /*
5109     * Sets the given dataId record in the "data" table to primary, and resets all data records of
5110     * the same mimetype and under the same contact to not be primary.
5111     *
5112     * @param dataId the id of the data record to be set to primary. Pass -1 to clear the primary
5113     * flag of all data items of this raw contacts
5114     */
5115    public void setIsPrimary(long rawContactId, long dataId, long mimeTypeId) {
5116        if (mSetPrimaryStatement == null) {
5117            mSetPrimaryStatement = getWritableDatabase().compileStatement(
5118                    "UPDATE " + Tables.DATA +
5119                    " SET " + Data.IS_PRIMARY + "=(_id=?)" +
5120                    " WHERE " + DataColumns.MIMETYPE_ID + "=?" +
5121                    "   AND " + Data.RAW_CONTACT_ID + "=?");
5122        }
5123        mSetPrimaryStatement.bindLong(1, dataId);
5124        mSetPrimaryStatement.bindLong(2, mimeTypeId);
5125        mSetPrimaryStatement.bindLong(3, rawContactId);
5126        mSetPrimaryStatement.execute();
5127    }
5128
5129    /*
5130     * Clears the super primary of all data items of the given raw contact. does not touch
5131     * other raw contacts of the same joined aggregate
5132     */
5133    public void clearSuperPrimary(long rawContactId, long mimeTypeId) {
5134        if (mClearSuperPrimaryStatement == null) {
5135            mClearSuperPrimaryStatement = getWritableDatabase().compileStatement(
5136                    "UPDATE " + Tables.DATA +
5137                    " SET " + Data.IS_SUPER_PRIMARY + "=0" +
5138                    " WHERE " + DataColumns.MIMETYPE_ID + "=?" +
5139                    "   AND " + Data.RAW_CONTACT_ID + "=?");
5140        }
5141        mClearSuperPrimaryStatement.bindLong(1, mimeTypeId);
5142        mClearSuperPrimaryStatement.bindLong(2, rawContactId);
5143        mClearSuperPrimaryStatement.execute();
5144    }
5145
5146    /*
5147     * Sets the given dataId record in the "data" table to "super primary", and resets all data
5148     * records of the same mimetype and under the same aggregate to not be "super primary".
5149     *
5150     * @param dataId the id of the data record to be set to primary.
5151     */
5152    public void setIsSuperPrimary(long rawContactId, long dataId, long mimeTypeId) {
5153        if (mSetSuperPrimaryStatement == null) {
5154            mSetSuperPrimaryStatement = getWritableDatabase().compileStatement(
5155                    "UPDATE " + Tables.DATA +
5156                    " SET " + Data.IS_SUPER_PRIMARY + "=(" + Data._ID + "=?)" +
5157                    " WHERE " + DataColumns.MIMETYPE_ID + "=?" +
5158                    "   AND " + Data.RAW_CONTACT_ID + " IN (" +
5159                            "SELECT " + RawContacts._ID +
5160                            " FROM " + Tables.RAW_CONTACTS +
5161                            " WHERE " + RawContacts.CONTACT_ID + " =(" +
5162                                    "SELECT " + RawContacts.CONTACT_ID +
5163                                    " FROM " + Tables.RAW_CONTACTS +
5164                                    " WHERE " + RawContacts._ID + "=?))");
5165        }
5166        mSetSuperPrimaryStatement.bindLong(1, dataId);
5167        mSetSuperPrimaryStatement.bindLong(2, mimeTypeId);
5168        mSetSuperPrimaryStatement.bindLong(3, rawContactId);
5169        mSetSuperPrimaryStatement.execute();
5170    }
5171
5172    /**
5173     * Inserts a record in the {@link Tables#NAME_LOOKUP} table.
5174     */
5175    public void insertNameLookup(long rawContactId, long dataId, int lookupType, String name) {
5176        if (TextUtils.isEmpty(name)) {
5177            return;
5178        }
5179
5180        if (mNameLookupInsert == null) {
5181            mNameLookupInsert = getWritableDatabase().compileStatement(
5182                    "INSERT OR IGNORE INTO " + Tables.NAME_LOOKUP + "("
5183                            + NameLookupColumns.RAW_CONTACT_ID + ","
5184                            + NameLookupColumns.DATA_ID + ","
5185                            + NameLookupColumns.NAME_TYPE + ","
5186                            + NameLookupColumns.NORMALIZED_NAME
5187                    + ") VALUES (?,?,?,?)");
5188        }
5189        mNameLookupInsert.bindLong(1, rawContactId);
5190        mNameLookupInsert.bindLong(2, dataId);
5191        mNameLookupInsert.bindLong(3, lookupType);
5192        bindString(mNameLookupInsert, 4, name);
5193        mNameLookupInsert.executeInsert();
5194    }
5195
5196    /**
5197     * Deletes all {@link Tables#NAME_LOOKUP} table rows associated with the specified data element.
5198     */
5199    public void deleteNameLookup(long dataId) {
5200        if (mNameLookupDelete == null) {
5201            mNameLookupDelete = getWritableDatabase().compileStatement(
5202                    "DELETE FROM " + Tables.NAME_LOOKUP +
5203                    " WHERE " + NameLookupColumns.DATA_ID + "=?");
5204        }
5205        mNameLookupDelete.bindLong(1, dataId);
5206        mNameLookupDelete.execute();
5207    }
5208
5209    public String insertNameLookupForEmail(long rawContactId, long dataId, String email) {
5210        if (TextUtils.isEmpty(email)) {
5211            return null;
5212        }
5213
5214        String address = extractHandleFromEmailAddress(email);
5215        if (address == null) {
5216            return null;
5217        }
5218
5219        insertNameLookup(rawContactId, dataId,
5220                NameLookupType.EMAIL_BASED_NICKNAME, NameNormalizer.normalize(address));
5221        return address;
5222    }
5223
5224    /**
5225     * Normalizes the nickname and inserts it in the name lookup table.
5226     */
5227    public void insertNameLookupForNickname(long rawContactId, long dataId, String nickname) {
5228        if (TextUtils.isEmpty(nickname)) {
5229            return;
5230        }
5231
5232        insertNameLookup(rawContactId, dataId,
5233                NameLookupType.NICKNAME, NameNormalizer.normalize(nickname));
5234    }
5235
5236    public void insertNameLookupForPhoneticName(long rawContactId, long dataId, String familyName,
5237            String middleName, String givenName) {
5238        mSb.setLength(0);
5239        if (familyName != null) {
5240            mSb.append(familyName.trim());
5241        }
5242        if (middleName != null) {
5243            mSb.append(middleName.trim());
5244        }
5245        if (givenName != null) {
5246            mSb.append(givenName.trim());
5247        }
5248
5249        if (mSb.length() > 0) {
5250            insertNameLookup(rawContactId, dataId, NameLookupType.NAME_COLLATION_KEY,
5251                    NameNormalizer.normalize(mSb.toString()));
5252        }
5253    }
5254
5255    /**
5256     * Performs a query and returns true if any Data item of the raw contact with the given
5257     * id and mimetype is marked as super-primary
5258     */
5259    public boolean rawContactHasSuperPrimary(long rawContactId, long mimeTypeId) {
5260        final Cursor existsCursor = getReadableDatabase().rawQuery(
5261                "SELECT EXISTS(SELECT 1 FROM " + Tables.DATA +
5262                " WHERE " + Data.RAW_CONTACT_ID + "=?" +
5263                " AND " + DataColumns.MIMETYPE_ID + "=?" +
5264                " AND " + Data.IS_SUPER_PRIMARY + "<>0)",
5265                new String[] { String.valueOf(rawContactId), String.valueOf(mimeTypeId) });
5266        try {
5267            if (!existsCursor.moveToFirst()) throw new IllegalStateException();
5268            return existsCursor.getInt(0) != 0;
5269        } finally {
5270            existsCursor.close();
5271        }
5272    }
5273
5274    public String getCurrentCountryIso() {
5275        return mCountryMonitor.getCountryIso();
5276    }
5277
5278    @NeededForTesting
5279    /* package */ String querySearchIndexContentForTest(long contactId) {
5280        return DatabaseUtils.stringForQuery(getReadableDatabase(),
5281                "SELECT " + SearchIndexColumns.CONTENT +
5282                " FROM " + Tables.SEARCH_INDEX +
5283                " WHERE " + SearchIndexColumns.CONTACT_ID + "=CAST(? AS int)",
5284                new String[] { String.valueOf(contactId) });
5285    }
5286
5287    @NeededForTesting
5288    /* package */ String querySearchIndexTokensForTest(long contactId) {
5289        return DatabaseUtils.stringForQuery(getReadableDatabase(),
5290                "SELECT " + SearchIndexColumns.TOKENS +
5291                " FROM " + Tables.SEARCH_INDEX +
5292                " WHERE " + SearchIndexColumns.CONTACT_ID + "=CAST(? AS int)",
5293                new String[] { String.valueOf(contactId) });
5294    }
5295}
5296