EmailProvider.java revision 6605115ec4bd02f9adf190e0b390ceed783bde5b
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.email.provider;
18
19import android.accounts.AccountManager;
20import android.content.ContentProvider;
21import android.content.ContentProviderOperation;
22import android.content.ContentProviderResult;
23import android.content.ContentResolver;
24import android.content.ContentUris;
25import android.content.ContentValues;
26import android.content.Context;
27import android.content.Intent;
28import android.content.OperationApplicationException;
29import android.content.UriMatcher;
30import android.database.ContentObserver;
31import android.database.Cursor;
32import android.database.MatrixCursor;
33import android.database.SQLException;
34import android.database.sqlite.SQLiteDatabase;
35import android.database.sqlite.SQLiteException;
36import android.database.sqlite.SQLiteOpenHelper;
37import android.net.Uri;
38import android.provider.BaseColumns;
39import android.provider.ContactsContract;
40import android.text.TextUtils;
41import android.util.Log;
42
43import com.android.common.content.ProjectionMap;
44import com.android.email.Email;
45import com.android.email.Preferences;
46import com.android.email.provider.ContentCache.CacheToken;
47import com.android.email.service.AttachmentDownloadService;
48import com.android.emailcommon.AccountManagerTypes;
49import com.android.emailcommon.CalendarProviderStub;
50import com.android.emailcommon.Logging;
51import com.android.emailcommon.provider.Account;
52import com.android.emailcommon.provider.EmailContent;
53import com.android.emailcommon.provider.EmailContent.AccountColumns;
54import com.android.emailcommon.provider.EmailContent.Attachment;
55import com.android.emailcommon.provider.EmailContent.AttachmentColumns;
56import com.android.emailcommon.provider.EmailContent.Body;
57import com.android.emailcommon.provider.EmailContent.BodyColumns;
58import com.android.emailcommon.provider.EmailContent.HostAuthColumns;
59import com.android.emailcommon.provider.EmailContent.MailboxColumns;
60import com.android.emailcommon.provider.EmailContent.Message;
61import com.android.emailcommon.provider.EmailContent.MessageColumns;
62import com.android.emailcommon.provider.EmailContent.PolicyColumns;
63import com.android.emailcommon.provider.EmailContent.QuickResponseColumns;
64import com.android.emailcommon.provider.EmailContent.SyncColumns;
65import com.android.emailcommon.provider.HostAuth;
66import com.android.emailcommon.provider.Mailbox;
67import com.android.emailcommon.provider.Policy;
68import com.android.emailcommon.provider.QuickResponse;
69import com.android.emailcommon.service.LegacyPolicySet;
70import com.android.mail.providers.UIProvider;
71import com.android.mail.providers.UIProvider.ConversationPriority;
72import com.android.mail.providers.UIProvider.ConversationSendingState;
73import com.google.common.annotations.VisibleForTesting;
74
75import java.io.File;
76import java.util.ArrayList;
77import java.util.Arrays;
78import java.util.Collection;
79import java.util.HashMap;
80import java.util.List;
81import java.util.Map;
82
83public class EmailProvider extends ContentProvider {
84
85    private static final String TAG = "EmailProvider";
86
87    protected static final String DATABASE_NAME = "EmailProvider.db";
88    protected static final String BODY_DATABASE_NAME = "EmailProviderBody.db";
89    protected static final String BACKUP_DATABASE_NAME = "EmailProviderBackup.db";
90
91    public static final String ACTION_ATTACHMENT_UPDATED = "com.android.email.ATTACHMENT_UPDATED";
92    public static final String ATTACHMENT_UPDATED_EXTRA_FLAGS =
93        "com.android.email.ATTACHMENT_UPDATED_FLAGS";
94
95    /**
96     * Notifies that changes happened. Certain UI components, e.g., widgets, can register for this
97     * {@link android.content.Intent} and update accordingly. However, this can be very broad and
98     * is NOT the preferred way of getting notification.
99     */
100    public static final String ACTION_NOTIFY_MESSAGE_LIST_DATASET_CHANGED =
101        "com.android.email.MESSAGE_LIST_DATASET_CHANGED";
102
103    public static final String EMAIL_MESSAGE_MIME_TYPE =
104        "vnd.android.cursor.item/email-message";
105    public static final String EMAIL_ATTACHMENT_MIME_TYPE =
106        "vnd.android.cursor.item/email-attachment";
107
108    public static final Uri INTEGRITY_CHECK_URI =
109        Uri.parse("content://" + EmailContent.AUTHORITY + "/integrityCheck");
110    public static final Uri ACCOUNT_BACKUP_URI =
111        Uri.parse("content://" + EmailContent.AUTHORITY + "/accountBackup");
112
113    /** Appended to the notification URI for delete operations */
114    public static final String NOTIFICATION_OP_DELETE = "delete";
115    /** Appended to the notification URI for insert operations */
116    public static final String NOTIFICATION_OP_INSERT = "insert";
117    /** Appended to the notification URI for update operations */
118    public static final String NOTIFICATION_OP_UPDATE = "update";
119
120    // Definitions for our queries looking for orphaned messages
121    private static final String[] ORPHANS_PROJECTION
122        = new String[] {MessageColumns.ID, MessageColumns.MAILBOX_KEY};
123    private static final int ORPHANS_ID = 0;
124    private static final int ORPHANS_MAILBOX_KEY = 1;
125
126    private static final String WHERE_ID = EmailContent.RECORD_ID + "=?";
127
128    // This is not a hard limit on accounts, per se, but beyond this, we can't guarantee that all
129    // critical mailboxes, host auth's, accounts, and policies are cached
130    private static final int MAX_CACHED_ACCOUNTS = 16;
131    // Inbox, Drafts, Sent, Outbox, Trash, and Search (these boxes are cached when possible)
132    private static final int NUM_ALWAYS_CACHED_MAILBOXES = 6;
133
134    // We'll cache the following four tables; sizes are best estimates of effective values
135    private final ContentCache mCacheAccount =
136        new ContentCache("Account", Account.CONTENT_PROJECTION, MAX_CACHED_ACCOUNTS);
137    private final ContentCache mCacheHostAuth =
138        new ContentCache("HostAuth", HostAuth.CONTENT_PROJECTION, MAX_CACHED_ACCOUNTS * 2);
139    /*package*/ final ContentCache mCacheMailbox =
140        new ContentCache("Mailbox", Mailbox.CONTENT_PROJECTION,
141                MAX_CACHED_ACCOUNTS * (NUM_ALWAYS_CACHED_MAILBOXES + 2));
142    private final ContentCache mCacheMessage =
143        new ContentCache("Message", Message.CONTENT_PROJECTION, 8);
144    private final ContentCache mCachePolicy =
145        new ContentCache("Policy", Policy.CONTENT_PROJECTION, MAX_CACHED_ACCOUNTS);
146
147    // Any changes to the database format *must* include update-in-place code.
148    // Original version: 3
149    // Version 4: Database wipe required; changing AccountManager interface w/Exchange
150    // Version 5: Database wipe required; changing AccountManager interface w/Exchange
151    // Version 6: Adding Message.mServerTimeStamp column
152    // Version 7: Replace the mailbox_delete trigger with a version that removes orphaned messages
153    //            from the Message_Deletes and Message_Updates tables
154    // Version 8: Add security flags column to accounts table
155    // Version 9: Add security sync key and signature to accounts table
156    // Version 10: Add meeting info to message table
157    // Version 11: Add content and flags to attachment table
158    // Version 12: Add content_bytes to attachment table. content is deprecated.
159    // Version 13: Add messageCount to Mailbox table.
160    // Version 14: Add snippet to Message table
161    // Version 15: Fix upgrade problem in version 14.
162    // Version 16: Add accountKey to Attachment table
163    // Version 17: Add parentKey to Mailbox table
164    // Version 18: Copy Mailbox.displayName to Mailbox.serverId for all IMAP & POP3 mailboxes.
165    //             Column Mailbox.serverId is used for the server-side pathname of a mailbox.
166    // Version 19: Add Policy table; add policyKey to Account table and trigger to delete an
167    //             Account's policy when the Account is deleted
168    // Version 20: Add new policies to Policy table
169    // Version 21: Add lastSeenMessageKey column to Mailbox table
170    // Version 22: Upgrade path for IMAP/POP accounts to integrate with AccountManager
171    // Version 23: Add column to mailbox table for time of last access
172    // Version 24: Add column to hostauth table for client cert alias
173    // Version 25: Added QuickResponse table
174    // Version 26: Update IMAP accounts to add FLAG_SUPPORTS_SEARCH flag
175    // Version 27: Add protocolSearchInfo to Message table
176    // Version 28: Add notifiedMessageId and notifiedMessageCount to Account
177    // Version 29: Add protocolPoliciesEnforced and protocolPoliciesUnsupported to Policy
178
179    public static final int DATABASE_VERSION = 29;
180
181    // Any changes to the database format *must* include update-in-place code.
182    // Original version: 2
183    // Version 3: Add "sourceKey" column
184    // Version 4: Database wipe required; changing AccountManager interface w/Exchange
185    // Version 5: Database wipe required; changing AccountManager interface w/Exchange
186    // Version 6: Adding Body.mIntroText column
187    public static final int BODY_DATABASE_VERSION = 6;
188
189    private static final int ACCOUNT_BASE = 0;
190    private static final int ACCOUNT = ACCOUNT_BASE;
191    private static final int ACCOUNT_ID = ACCOUNT_BASE + 1;
192    private static final int ACCOUNT_ID_ADD_TO_FIELD = ACCOUNT_BASE + 2;
193    private static final int ACCOUNT_RESET_NEW_COUNT = ACCOUNT_BASE + 3;
194    private static final int ACCOUNT_RESET_NEW_COUNT_ID = ACCOUNT_BASE + 4;
195    private static final int ACCOUNT_DEFAULT_ID = ACCOUNT_BASE + 5;
196
197    private static final int MAILBOX_BASE = 0x1000;
198    private static final int MAILBOX = MAILBOX_BASE;
199    private static final int MAILBOX_ID = MAILBOX_BASE + 1;
200    private static final int MAILBOX_ID_FROM_ACCOUNT_AND_TYPE = MAILBOX_BASE + 2;
201    private static final int MAILBOX_ID_ADD_TO_FIELD = MAILBOX_BASE + 2;
202
203    private static final int MESSAGE_BASE = 0x2000;
204    private static final int MESSAGE = MESSAGE_BASE;
205    private static final int MESSAGE_ID = MESSAGE_BASE + 1;
206    private static final int SYNCED_MESSAGE_ID = MESSAGE_BASE + 2;
207
208    private static final int ATTACHMENT_BASE = 0x3000;
209    private static final int ATTACHMENT = ATTACHMENT_BASE;
210    private static final int ATTACHMENT_ID = ATTACHMENT_BASE + 1;
211    private static final int ATTACHMENTS_MESSAGE_ID = ATTACHMENT_BASE + 2;
212
213    private static final int HOSTAUTH_BASE = 0x4000;
214    private static final int HOSTAUTH = HOSTAUTH_BASE;
215    private static final int HOSTAUTH_ID = HOSTAUTH_BASE + 1;
216
217    private static final int UPDATED_MESSAGE_BASE = 0x5000;
218    private static final int UPDATED_MESSAGE = UPDATED_MESSAGE_BASE;
219    private static final int UPDATED_MESSAGE_ID = UPDATED_MESSAGE_BASE + 1;
220
221    private static final int DELETED_MESSAGE_BASE = 0x6000;
222    private static final int DELETED_MESSAGE = DELETED_MESSAGE_BASE;
223    private static final int DELETED_MESSAGE_ID = DELETED_MESSAGE_BASE + 1;
224
225    private static final int POLICY_BASE = 0x7000;
226    private static final int POLICY = POLICY_BASE;
227    private static final int POLICY_ID = POLICY_BASE + 1;
228
229    private static final int QUICK_RESPONSE_BASE = 0x8000;
230    private static final int QUICK_RESPONSE = QUICK_RESPONSE_BASE;
231    private static final int QUICK_RESPONSE_ID = QUICK_RESPONSE_BASE + 1;
232    private static final int QUICK_RESPONSE_ACCOUNT_ID = QUICK_RESPONSE_BASE + 2;
233
234    private static final int UI_BASE = 0x9000;
235    private static final int UI_FOLDERS = UI_BASE;
236    private static final int UI_MESSAGES = UI_BASE + 1;
237    private static final int UI_MESSAGE = UI_BASE + 2;
238    private static final int UI_SENDMAIL = UI_BASE + 3;
239
240    // MUST ALWAYS EQUAL THE LAST OF THE PREVIOUS BASE CONSTANTS
241    private static final int LAST_EMAIL_PROVIDER_DB_BASE = UI_BASE;
242
243    // DO NOT CHANGE BODY_BASE!!
244    private static final int BODY_BASE = LAST_EMAIL_PROVIDER_DB_BASE + 0x1000;
245    private static final int BODY = BODY_BASE;
246    private static final int BODY_ID = BODY_BASE + 1;
247
248    private static final int BASE_SHIFT = 12;  // 12 bits to the base type: 0, 0x1000, 0x2000, etc.
249
250    // TABLE_NAMES MUST remain in the order of the BASE constants above (e.g. ACCOUNT_BASE = 0x0000,
251    // MESSAGE_BASE = 0x1000, etc.)
252    private static final String[] TABLE_NAMES = {
253        Account.TABLE_NAME,
254        Mailbox.TABLE_NAME,
255        Message.TABLE_NAME,
256        Attachment.TABLE_NAME,
257        HostAuth.TABLE_NAME,
258        Message.UPDATED_TABLE_NAME,
259        Message.DELETED_TABLE_NAME,
260        Policy.TABLE_NAME,
261        QuickResponse.TABLE_NAME,
262        null,  // UI
263        Body.TABLE_NAME,
264    };
265
266    // CONTENT_CACHES MUST remain in the order of the BASE constants above
267    private final ContentCache[] mContentCaches = {
268        mCacheAccount,
269        mCacheMailbox,
270        mCacheMessage,
271        null, // Attachment
272        mCacheHostAuth,
273        null, // Updated message
274        null, // Deleted message
275        mCachePolicy,
276        null, // Quick response
277        null, // Body
278        null  // UI
279    };
280
281    // CACHE_PROJECTIONS MUST remain in the order of the BASE constants above
282    private static final String[][] CACHE_PROJECTIONS = {
283        Account.CONTENT_PROJECTION,
284        Mailbox.CONTENT_PROJECTION,
285        Message.CONTENT_PROJECTION,
286        null, // Attachment
287        HostAuth.CONTENT_PROJECTION,
288        null, // Updated message
289        null, // Deleted message
290        Policy.CONTENT_PROJECTION,
291        null,  // Quick response
292        null,  // Body
293        null   // UI
294    };
295
296    private static final UriMatcher sURIMatcher = new UriMatcher(UriMatcher.NO_MATCH);
297
298    private static final String MAILBOX_PRE_CACHE_SELECTION = MailboxColumns.TYPE + " IN (" +
299        Mailbox.TYPE_INBOX + "," + Mailbox.TYPE_DRAFTS + "," + Mailbox.TYPE_TRASH + "," +
300        Mailbox.TYPE_SENT + "," + Mailbox.TYPE_SEARCH + "," + Mailbox.TYPE_OUTBOX + ")";
301
302    /**
303     * Let's only generate these SQL strings once, as they are used frequently
304     * Note that this isn't relevant for table creation strings, since they are used only once
305     */
306    private static final String UPDATED_MESSAGE_INSERT = "insert or ignore into " +
307        Message.UPDATED_TABLE_NAME + " select * from " + Message.TABLE_NAME + " where " +
308        EmailContent.RECORD_ID + '=';
309
310    private static final String UPDATED_MESSAGE_DELETE = "delete from " +
311        Message.UPDATED_TABLE_NAME + " where " + EmailContent.RECORD_ID + '=';
312
313    private static final String DELETED_MESSAGE_INSERT = "insert or replace into " +
314        Message.DELETED_TABLE_NAME + " select * from " + Message.TABLE_NAME + " where " +
315        EmailContent.RECORD_ID + '=';
316
317    private static final String DELETE_ORPHAN_BODIES = "delete from " + Body.TABLE_NAME +
318        " where " + BodyColumns.MESSAGE_KEY + " in " + "(select " + BodyColumns.MESSAGE_KEY +
319        " from " + Body.TABLE_NAME + " except select " + EmailContent.RECORD_ID + " from " +
320        Message.TABLE_NAME + ')';
321
322    private static final String DELETE_BODY = "delete from " + Body.TABLE_NAME +
323        " where " + BodyColumns.MESSAGE_KEY + '=';
324
325    private static final String ID_EQUALS = EmailContent.RECORD_ID + "=?";
326
327    private static final String TRIGGER_MAILBOX_DELETE =
328        "create trigger mailbox_delete before delete on " + Mailbox.TABLE_NAME +
329        " begin" +
330        " delete from " + Message.TABLE_NAME +
331        "  where " + MessageColumns.MAILBOX_KEY + "=old." + EmailContent.RECORD_ID +
332        "; delete from " + Message.UPDATED_TABLE_NAME +
333        "  where " + MessageColumns.MAILBOX_KEY + "=old." + EmailContent.RECORD_ID +
334        "; delete from " + Message.DELETED_TABLE_NAME +
335        "  where " + MessageColumns.MAILBOX_KEY + "=old." + EmailContent.RECORD_ID +
336        "; end";
337
338    private static final String TRIGGER_ACCOUNT_DELETE =
339        "create trigger account_delete before delete on " + Account.TABLE_NAME +
340        " begin delete from " + Mailbox.TABLE_NAME +
341        " where " + MailboxColumns.ACCOUNT_KEY + "=old." + EmailContent.RECORD_ID +
342        "; delete from " + HostAuth.TABLE_NAME +
343        " where " + EmailContent.RECORD_ID + "=old." + AccountColumns.HOST_AUTH_KEY_RECV +
344        "; delete from " + HostAuth.TABLE_NAME +
345        " where " + EmailContent.RECORD_ID + "=old." + AccountColumns.HOST_AUTH_KEY_SEND +
346        "; delete from " + Policy.TABLE_NAME +
347        " where " + EmailContent.RECORD_ID + "=old." + AccountColumns.POLICY_KEY +
348        "; end";
349
350    private static final ContentValues CONTENT_VALUES_RESET_NEW_MESSAGE_COUNT;
351
352    public static final String MESSAGE_URI_PARAMETER_MAILBOX_ID = "mailboxId";
353
354    static {
355        // Email URI matching table
356        UriMatcher matcher = sURIMatcher;
357
358        // All accounts
359        matcher.addURI(EmailContent.AUTHORITY, "account", ACCOUNT);
360        // A specific account
361        // insert into this URI causes a mailbox to be added to the account
362        matcher.addURI(EmailContent.AUTHORITY, "account/#", ACCOUNT_ID);
363        matcher.addURI(EmailContent.AUTHORITY, "account/default", ACCOUNT_DEFAULT_ID);
364
365        // Special URI to reset the new message count.  Only update works, and content values
366        // will be ignored.
367        matcher.addURI(EmailContent.AUTHORITY, "resetNewMessageCount",
368                ACCOUNT_RESET_NEW_COUNT);
369        matcher.addURI(EmailContent.AUTHORITY, "resetNewMessageCount/#",
370                ACCOUNT_RESET_NEW_COUNT_ID);
371
372        // All mailboxes
373        matcher.addURI(EmailContent.AUTHORITY, "mailbox", MAILBOX);
374        // A specific mailbox
375        // insert into this URI causes a message to be added to the mailbox
376        // ** NOTE For now, the accountKey must be set manually in the values!
377        matcher.addURI(EmailContent.AUTHORITY, "mailbox/#", MAILBOX_ID);
378        matcher.addURI(EmailContent.AUTHORITY, "mailboxIdFromAccountAndType/#/#",
379                MAILBOX_ID_FROM_ACCOUNT_AND_TYPE);
380        // All messages
381        matcher.addURI(EmailContent.AUTHORITY, "message", MESSAGE);
382        // A specific message
383        // insert into this URI causes an attachment to be added to the message
384        matcher.addURI(EmailContent.AUTHORITY, "message/#", MESSAGE_ID);
385
386        // A specific attachment
387        matcher.addURI(EmailContent.AUTHORITY, "attachment", ATTACHMENT);
388        // A specific attachment (the header information)
389        matcher.addURI(EmailContent.AUTHORITY, "attachment/#", ATTACHMENT_ID);
390        // The attachments of a specific message (query only) (insert & delete TBD)
391        matcher.addURI(EmailContent.AUTHORITY, "attachment/message/#",
392                ATTACHMENTS_MESSAGE_ID);
393
394        // All mail bodies
395        matcher.addURI(EmailContent.AUTHORITY, "body", BODY);
396        // A specific mail body
397        matcher.addURI(EmailContent.AUTHORITY, "body/#", BODY_ID);
398
399        // All hostauth records
400        matcher.addURI(EmailContent.AUTHORITY, "hostauth", HOSTAUTH);
401        // A specific hostauth
402        matcher.addURI(EmailContent.AUTHORITY, "hostauth/#", HOSTAUTH_ID);
403
404        // Atomically a constant value to a particular field of a mailbox/account
405        matcher.addURI(EmailContent.AUTHORITY, "mailboxIdAddToField/#",
406                MAILBOX_ID_ADD_TO_FIELD);
407        matcher.addURI(EmailContent.AUTHORITY, "accountIdAddToField/#",
408                ACCOUNT_ID_ADD_TO_FIELD);
409
410        /**
411         * THIS URI HAS SPECIAL SEMANTICS
412         * ITS USE IS INTENDED FOR THE UI APPLICATION TO MARK CHANGES THAT NEED TO BE SYNCED BACK
413         * TO A SERVER VIA A SYNC ADAPTER
414         */
415        matcher.addURI(EmailContent.AUTHORITY, "syncedMessage/#", SYNCED_MESSAGE_ID);
416
417        /**
418         * THE URIs BELOW THIS POINT ARE INTENDED TO BE USED BY SYNC ADAPTERS ONLY
419         * THEY REFER TO DATA CREATED AND MAINTAINED BY CALLS TO THE SYNCED_MESSAGE_ID URI
420         * BY THE UI APPLICATION
421         */
422        // All deleted messages
423        matcher.addURI(EmailContent.AUTHORITY, "deletedMessage", DELETED_MESSAGE);
424        // A specific deleted message
425        matcher.addURI(EmailContent.AUTHORITY, "deletedMessage/#", DELETED_MESSAGE_ID);
426
427        // All updated messages
428        matcher.addURI(EmailContent.AUTHORITY, "updatedMessage", UPDATED_MESSAGE);
429        // A specific updated message
430        matcher.addURI(EmailContent.AUTHORITY, "updatedMessage/#", UPDATED_MESSAGE_ID);
431
432        CONTENT_VALUES_RESET_NEW_MESSAGE_COUNT = new ContentValues();
433        CONTENT_VALUES_RESET_NEW_MESSAGE_COUNT.put(Account.NEW_MESSAGE_COUNT, 0);
434
435        matcher.addURI(EmailContent.AUTHORITY, "policy", POLICY);
436        matcher.addURI(EmailContent.AUTHORITY, "policy/#", POLICY_ID);
437
438        // All quick responses
439        matcher.addURI(EmailContent.AUTHORITY, "quickresponse", QUICK_RESPONSE);
440        // A specific quick response
441        matcher.addURI(EmailContent.AUTHORITY, "quickresponse/#", QUICK_RESPONSE_ID);
442        // All quick responses associated with a particular account id
443        matcher.addURI(EmailContent.AUTHORITY, "quickresponse/account/#",
444                QUICK_RESPONSE_ACCOUNT_ID);
445
446        matcher.addURI(EmailContent.AUTHORITY, "uifolders/*", UI_FOLDERS);
447        matcher.addURI(EmailContent.AUTHORITY, "uimessages/#", UI_MESSAGES);
448        matcher.addURI(EmailContent.AUTHORITY, "uimessage/#", UI_MESSAGE);
449        matcher.addURI(EmailContent.AUTHORITY, "uisendmail/*", UI_SENDMAIL);
450    }
451
452    /**
453     * Wrap the UriMatcher call so we can throw a runtime exception if an unknown Uri is passed in
454     * @param uri the Uri to match
455     * @return the match value
456     */
457    private static int findMatch(Uri uri, String methodName) {
458        int match = sURIMatcher.match(uri);
459        if (match < 0) {
460            throw new IllegalArgumentException("Unknown uri: " + uri);
461        } else if (Logging.LOGD) {
462            Log.v(TAG, methodName + ": uri=" + uri + ", match is " + match);
463        }
464        return match;
465    }
466
467    /*
468     * Internal helper method for index creation.
469     * Example:
470     * "create index message_" + MessageColumns.FLAG_READ
471     * + " on " + Message.TABLE_NAME + " (" + MessageColumns.FLAG_READ + ");"
472     */
473    /* package */
474    static String createIndex(String tableName, String columnName) {
475        return "create index " + tableName.toLowerCase() + '_' + columnName
476            + " on " + tableName + " (" + columnName + ");";
477    }
478
479    static void createMessageTable(SQLiteDatabase db) {
480        String messageColumns = MessageColumns.DISPLAY_NAME + " text, "
481            + MessageColumns.TIMESTAMP + " integer, "
482            + MessageColumns.SUBJECT + " text, "
483            + MessageColumns.FLAG_READ + " integer, "
484            + MessageColumns.FLAG_LOADED + " integer, "
485            + MessageColumns.FLAG_FAVORITE + " integer, "
486            + MessageColumns.FLAG_ATTACHMENT + " integer, "
487            + MessageColumns.FLAGS + " integer, "
488            + MessageColumns.CLIENT_ID + " integer, "
489            + MessageColumns.MESSAGE_ID + " text, "
490            + MessageColumns.MAILBOX_KEY + " integer, "
491            + MessageColumns.ACCOUNT_KEY + " integer, "
492            + MessageColumns.FROM_LIST + " text, "
493            + MessageColumns.TO_LIST + " text, "
494            + MessageColumns.CC_LIST + " text, "
495            + MessageColumns.BCC_LIST + " text, "
496            + MessageColumns.REPLY_TO_LIST + " text, "
497            + MessageColumns.MEETING_INFO + " text, "
498            + MessageColumns.SNIPPET + " text, "
499            + MessageColumns.PROTOCOL_SEARCH_INFO + " text"
500            + ");";
501
502        // This String and the following String MUST have the same columns, except for the type
503        // of those columns!
504        String createString = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, "
505            + SyncColumns.SERVER_ID + " text, "
506            + SyncColumns.SERVER_TIMESTAMP + " integer, "
507            + messageColumns;
508
509        // For the updated and deleted tables, the id is assigned, but we do want to keep track
510        // of the ORDER of updates using an autoincrement primary key.  We use the DATA column
511        // at this point; it has no other function
512        String altCreateString = " (" + EmailContent.RECORD_ID + " integer unique, "
513            + SyncColumns.SERVER_ID + " text, "
514            + SyncColumns.SERVER_TIMESTAMP + " integer, "
515            + messageColumns;
516
517        // The three tables have the same schema
518        db.execSQL("create table " + Message.TABLE_NAME + createString);
519        db.execSQL("create table " + Message.UPDATED_TABLE_NAME + altCreateString);
520        db.execSQL("create table " + Message.DELETED_TABLE_NAME + altCreateString);
521
522        String indexColumns[] = {
523            MessageColumns.TIMESTAMP,
524            MessageColumns.FLAG_READ,
525            MessageColumns.FLAG_LOADED,
526            MessageColumns.MAILBOX_KEY,
527            SyncColumns.SERVER_ID
528        };
529
530        for (String columnName : indexColumns) {
531            db.execSQL(createIndex(Message.TABLE_NAME, columnName));
532        }
533
534        // Deleting a Message deletes all associated Attachments
535        // Deleting the associated Body cannot be done in a trigger, because the Body is stored
536        // in a separate database, and trigger cannot operate on attached databases.
537        db.execSQL("create trigger message_delete before delete on " + Message.TABLE_NAME +
538                " begin delete from " + Attachment.TABLE_NAME +
539                "  where " + AttachmentColumns.MESSAGE_KEY + "=old." + EmailContent.RECORD_ID +
540                "; end");
541
542        // Add triggers to keep unread count accurate per mailbox
543
544        // NOTE: SQLite's before triggers are not safe when recursive triggers are involved.
545        // Use caution when changing them.
546
547        // Insert a message; if flagRead is zero, add to the unread count of the message's mailbox
548        db.execSQL("create trigger unread_message_insert before insert on " + Message.TABLE_NAME +
549                " when NEW." + MessageColumns.FLAG_READ + "=0" +
550                " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT +
551                '=' + MailboxColumns.UNREAD_COUNT + "+1" +
552                "  where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY +
553                "; end");
554
555        // Delete a message; if flagRead is zero, decrement the unread count of the msg's mailbox
556        db.execSQL("create trigger unread_message_delete before delete on " + Message.TABLE_NAME +
557                " when OLD." + MessageColumns.FLAG_READ + "=0" +
558                " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT +
559                '=' + MailboxColumns.UNREAD_COUNT + "-1" +
560                "  where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY +
561                "; end");
562
563        // Change a message's mailbox
564        db.execSQL("create trigger unread_message_move before update of " +
565                MessageColumns.MAILBOX_KEY + " on " + Message.TABLE_NAME +
566                " when OLD." + MessageColumns.FLAG_READ + "=0" +
567                " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT +
568                '=' + MailboxColumns.UNREAD_COUNT + "-1" +
569                "  where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY +
570                "; update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT +
571                '=' + MailboxColumns.UNREAD_COUNT + "+1" +
572                " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY +
573                "; end");
574
575        // Change a message's read state
576        db.execSQL("create trigger unread_message_read before update of " +
577                MessageColumns.FLAG_READ + " on " + Message.TABLE_NAME +
578                " when OLD." + MessageColumns.FLAG_READ + "!=NEW." + MessageColumns.FLAG_READ +
579                " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT +
580                '=' + MailboxColumns.UNREAD_COUNT + "+ case OLD." + MessageColumns.FLAG_READ +
581                " when 0 then -1 else 1 end" +
582                "  where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY +
583                "; end");
584
585        // Add triggers to update message count per mailbox
586
587        // Insert a message.
588        db.execSQL("create trigger message_count_message_insert after insert on " +
589                Message.TABLE_NAME +
590                " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT +
591                '=' + MailboxColumns.MESSAGE_COUNT + "+1" +
592                "  where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY +
593                "; end");
594
595        // Delete a message; if flagRead is zero, decrement the unread count of the msg's mailbox
596        db.execSQL("create trigger message_count_message_delete after delete on " +
597                Message.TABLE_NAME +
598                " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT +
599                '=' + MailboxColumns.MESSAGE_COUNT + "-1" +
600                "  where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY +
601                "; end");
602
603        // Change a message's mailbox
604        db.execSQL("create trigger message_count_message_move after update of " +
605                MessageColumns.MAILBOX_KEY + " on " + Message.TABLE_NAME +
606                " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT +
607                '=' + MailboxColumns.MESSAGE_COUNT + "-1" +
608                "  where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY +
609                "; update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT +
610                '=' + MailboxColumns.MESSAGE_COUNT + "+1" +
611                " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY +
612                "; end");
613    }
614
615    static void resetMessageTable(SQLiteDatabase db, int oldVersion, int newVersion) {
616        try {
617            db.execSQL("drop table " + Message.TABLE_NAME);
618            db.execSQL("drop table " + Message.UPDATED_TABLE_NAME);
619            db.execSQL("drop table " + Message.DELETED_TABLE_NAME);
620        } catch (SQLException e) {
621        }
622        createMessageTable(db);
623    }
624
625    @SuppressWarnings("deprecation")
626    static void createAccountTable(SQLiteDatabase db) {
627        String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, "
628            + AccountColumns.DISPLAY_NAME + " text, "
629            + AccountColumns.EMAIL_ADDRESS + " text, "
630            + AccountColumns.SYNC_KEY + " text, "
631            + AccountColumns.SYNC_LOOKBACK + " integer, "
632            + AccountColumns.SYNC_INTERVAL + " text, "
633            + AccountColumns.HOST_AUTH_KEY_RECV + " integer, "
634            + AccountColumns.HOST_AUTH_KEY_SEND + " integer, "
635            + AccountColumns.FLAGS + " integer, "
636            + AccountColumns.IS_DEFAULT + " integer, "
637            + AccountColumns.COMPATIBILITY_UUID + " text, "
638            + AccountColumns.SENDER_NAME + " text, "
639            + AccountColumns.RINGTONE_URI + " text, "
640            + AccountColumns.PROTOCOL_VERSION + " text, "
641            + AccountColumns.NEW_MESSAGE_COUNT + " integer, "
642            + AccountColumns.SECURITY_FLAGS + " integer, "
643            + AccountColumns.SECURITY_SYNC_KEY + " text, "
644            + AccountColumns.SIGNATURE + " text, "
645            + AccountColumns.POLICY_KEY + " integer, "
646            + AccountColumns.NOTIFIED_MESSAGE_ID + " integer, "
647            + AccountColumns.NOTIFIED_MESSAGE_COUNT + " integer"
648            + ");";
649        db.execSQL("create table " + Account.TABLE_NAME + s);
650        // Deleting an account deletes associated Mailboxes and HostAuth's
651        db.execSQL(TRIGGER_ACCOUNT_DELETE);
652    }
653
654    static void resetAccountTable(SQLiteDatabase db, int oldVersion, int newVersion) {
655        try {
656            db.execSQL("drop table " +  Account.TABLE_NAME);
657        } catch (SQLException e) {
658        }
659        createAccountTable(db);
660    }
661
662    static void createPolicyTable(SQLiteDatabase db) {
663        String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, "
664            + PolicyColumns.PASSWORD_MODE + " integer, "
665            + PolicyColumns.PASSWORD_MIN_LENGTH + " integer, "
666            + PolicyColumns.PASSWORD_EXPIRATION_DAYS + " integer, "
667            + PolicyColumns.PASSWORD_HISTORY + " integer, "
668            + PolicyColumns.PASSWORD_COMPLEX_CHARS + " integer, "
669            + PolicyColumns.PASSWORD_MAX_FAILS + " integer, "
670            + PolicyColumns.MAX_SCREEN_LOCK_TIME + " integer, "
671            + PolicyColumns.REQUIRE_REMOTE_WIPE + " integer, "
672            + PolicyColumns.REQUIRE_ENCRYPTION + " integer, "
673            + PolicyColumns.REQUIRE_ENCRYPTION_EXTERNAL + " integer, "
674            + PolicyColumns.REQUIRE_MANUAL_SYNC_WHEN_ROAMING + " integer, "
675            + PolicyColumns.DONT_ALLOW_CAMERA + " integer, "
676            + PolicyColumns.DONT_ALLOW_ATTACHMENTS + " integer, "
677            + PolicyColumns.DONT_ALLOW_HTML + " integer, "
678            + PolicyColumns.MAX_ATTACHMENT_SIZE + " integer, "
679            + PolicyColumns.MAX_TEXT_TRUNCATION_SIZE + " integer, "
680            + PolicyColumns.MAX_HTML_TRUNCATION_SIZE + " integer, "
681            + PolicyColumns.MAX_EMAIL_LOOKBACK + " integer, "
682            + PolicyColumns.MAX_CALENDAR_LOOKBACK + " integer, "
683            + PolicyColumns.PASSWORD_RECOVERY_ENABLED + " integer, "
684            + PolicyColumns.PROTOCOL_POLICIES_ENFORCED + " text, "
685            + PolicyColumns.PROTOCOL_POLICIES_UNSUPPORTED + " text"
686            + ");";
687        db.execSQL("create table " + Policy.TABLE_NAME + s);
688    }
689
690    static void createHostAuthTable(SQLiteDatabase db) {
691        String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, "
692            + HostAuthColumns.PROTOCOL + " text, "
693            + HostAuthColumns.ADDRESS + " text, "
694            + HostAuthColumns.PORT + " integer, "
695            + HostAuthColumns.FLAGS + " integer, "
696            + HostAuthColumns.LOGIN + " text, "
697            + HostAuthColumns.PASSWORD + " text, "
698            + HostAuthColumns.DOMAIN + " text, "
699            + HostAuthColumns.ACCOUNT_KEY + " integer,"
700            + HostAuthColumns.CLIENT_CERT_ALIAS + " text"
701            + ");";
702        db.execSQL("create table " + HostAuth.TABLE_NAME + s);
703    }
704
705    static void resetHostAuthTable(SQLiteDatabase db, int oldVersion, int newVersion) {
706        try {
707            db.execSQL("drop table " + HostAuth.TABLE_NAME);
708        } catch (SQLException e) {
709        }
710        createHostAuthTable(db);
711    }
712
713    static void createMailboxTable(SQLiteDatabase db) {
714        String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, "
715            + MailboxColumns.DISPLAY_NAME + " text, "
716            + MailboxColumns.SERVER_ID + " text, "
717            + MailboxColumns.PARENT_SERVER_ID + " text, "
718            + MailboxColumns.PARENT_KEY + " integer, "
719            + MailboxColumns.ACCOUNT_KEY + " integer, "
720            + MailboxColumns.TYPE + " integer, "
721            + MailboxColumns.DELIMITER + " integer, "
722            + MailboxColumns.SYNC_KEY + " text, "
723            + MailboxColumns.SYNC_LOOKBACK + " integer, "
724            + MailboxColumns.SYNC_INTERVAL + " integer, "
725            + MailboxColumns.SYNC_TIME + " integer, "
726            + MailboxColumns.UNREAD_COUNT + " integer, "
727            + MailboxColumns.FLAG_VISIBLE + " integer, "
728            + MailboxColumns.FLAGS + " integer, "
729            + MailboxColumns.VISIBLE_LIMIT + " integer, "
730            + MailboxColumns.SYNC_STATUS + " text, "
731            + MailboxColumns.MESSAGE_COUNT + " integer not null default 0, "
732            + MailboxColumns.LAST_SEEN_MESSAGE_KEY + " integer, "
733            + MailboxColumns.LAST_TOUCHED_TIME + " integer default 0"
734            + ");";
735        db.execSQL("create table " + Mailbox.TABLE_NAME + s);
736        db.execSQL("create index mailbox_" + MailboxColumns.SERVER_ID
737                + " on " + Mailbox.TABLE_NAME + " (" + MailboxColumns.SERVER_ID + ")");
738        db.execSQL("create index mailbox_" + MailboxColumns.ACCOUNT_KEY
739                + " on " + Mailbox.TABLE_NAME + " (" + MailboxColumns.ACCOUNT_KEY + ")");
740        // Deleting a Mailbox deletes associated Messages in all three tables
741        db.execSQL(TRIGGER_MAILBOX_DELETE);
742    }
743
744    static void resetMailboxTable(SQLiteDatabase db, int oldVersion, int newVersion) {
745        try {
746            db.execSQL("drop table " + Mailbox.TABLE_NAME);
747        } catch (SQLException e) {
748        }
749        createMailboxTable(db);
750    }
751
752    static void createAttachmentTable(SQLiteDatabase db) {
753        String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, "
754            + AttachmentColumns.FILENAME + " text, "
755            + AttachmentColumns.MIME_TYPE + " text, "
756            + AttachmentColumns.SIZE + " integer, "
757            + AttachmentColumns.CONTENT_ID + " text, "
758            + AttachmentColumns.CONTENT_URI + " text, "
759            + AttachmentColumns.MESSAGE_KEY + " integer, "
760            + AttachmentColumns.LOCATION + " text, "
761            + AttachmentColumns.ENCODING + " text, "
762            + AttachmentColumns.CONTENT + " text, "
763            + AttachmentColumns.FLAGS + " integer, "
764            + AttachmentColumns.CONTENT_BYTES + " blob, "
765            + AttachmentColumns.ACCOUNT_KEY + " integer"
766            + ");";
767        db.execSQL("create table " + Attachment.TABLE_NAME + s);
768        db.execSQL(createIndex(Attachment.TABLE_NAME, AttachmentColumns.MESSAGE_KEY));
769    }
770
771    static void resetAttachmentTable(SQLiteDatabase db, int oldVersion, int newVersion) {
772        try {
773            db.execSQL("drop table " + Attachment.TABLE_NAME);
774        } catch (SQLException e) {
775        }
776        createAttachmentTable(db);
777    }
778
779    static void createQuickResponseTable(SQLiteDatabase db) {
780        String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, "
781                + QuickResponseColumns.TEXT + " text, "
782                + QuickResponseColumns.ACCOUNT_KEY + " integer"
783                + ");";
784        db.execSQL("create table " + QuickResponse.TABLE_NAME + s);
785    }
786
787    static void createBodyTable(SQLiteDatabase db) {
788        String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, "
789            + BodyColumns.MESSAGE_KEY + " integer, "
790            + BodyColumns.HTML_CONTENT + " text, "
791            + BodyColumns.TEXT_CONTENT + " text, "
792            + BodyColumns.HTML_REPLY + " text, "
793            + BodyColumns.TEXT_REPLY + " text, "
794            + BodyColumns.SOURCE_MESSAGE_KEY + " text, "
795            + BodyColumns.INTRO_TEXT + " text"
796            + ");";
797        db.execSQL("create table " + Body.TABLE_NAME + s);
798        db.execSQL(createIndex(Body.TABLE_NAME, BodyColumns.MESSAGE_KEY));
799    }
800
801    static void upgradeBodyTable(SQLiteDatabase db, int oldVersion, int newVersion) {
802        if (oldVersion < 5) {
803            try {
804                db.execSQL("drop table " + Body.TABLE_NAME);
805                createBodyTable(db);
806            } catch (SQLException e) {
807            }
808        } else if (oldVersion == 5) {
809            try {
810                db.execSQL("alter table " + Body.TABLE_NAME
811                        + " add " + BodyColumns.INTRO_TEXT + " text");
812            } catch (SQLException e) {
813                // Shouldn't be needed unless we're debugging and interrupt the process
814                Log.w(TAG, "Exception upgrading EmailProviderBody.db from v5 to v6", e);
815            }
816            oldVersion = 6;
817        }
818    }
819
820    private SQLiteDatabase mDatabase;
821    private SQLiteDatabase mBodyDatabase;
822
823    /**
824     * Orphan record deletion utility.  Generates a sqlite statement like:
825     *  delete from <table> where <column> not in (select <foreignColumn> from <foreignTable>)
826     * @param db the EmailProvider database
827     * @param table the table whose orphans are to be removed
828     * @param column the column deletion will be based on
829     * @param foreignColumn the column in the foreign table whose absence will trigger the deletion
830     * @param foreignTable the foreign table
831     */
832    @VisibleForTesting
833    void deleteUnlinked(SQLiteDatabase db, String table, String column, String foreignColumn,
834            String foreignTable) {
835        int count = db.delete(table, column + " not in (select " + foreignColumn + " from " +
836                foreignTable + ")", null);
837        if (count > 0) {
838            Log.w(TAG, "Found " + count + " orphaned row(s) in " + table);
839        }
840    }
841
842    @VisibleForTesting
843    synchronized SQLiteDatabase getDatabase(Context context) {
844        // Always return the cached database, if we've got one
845        if (mDatabase != null) {
846            return mDatabase;
847        }
848
849        // Whenever we create or re-cache the databases, make sure that we haven't lost one
850        // to corruption
851        checkDatabases();
852
853        DatabaseHelper helper = new DatabaseHelper(context, DATABASE_NAME);
854        mDatabase = helper.getWritableDatabase();
855        mDatabase.setLockingEnabled(true);
856        BodyDatabaseHelper bodyHelper = new BodyDatabaseHelper(context, BODY_DATABASE_NAME);
857        mBodyDatabase = bodyHelper.getWritableDatabase();
858        if (mBodyDatabase != null) {
859            mBodyDatabase.setLockingEnabled(true);
860            String bodyFileName = mBodyDatabase.getPath();
861            mDatabase.execSQL("attach \"" + bodyFileName + "\" as BodyDatabase");
862        }
863
864        // Restore accounts if the database is corrupted...
865        restoreIfNeeded(context, mDatabase);
866
867        if (Email.DEBUG) {
868            Log.d(TAG, "Deleting orphans...");
869        }
870        // Check for any orphaned Messages in the updated/deleted tables
871        deleteMessageOrphans(mDatabase, Message.UPDATED_TABLE_NAME);
872        deleteMessageOrphans(mDatabase, Message.DELETED_TABLE_NAME);
873        // Delete orphaned mailboxes/messages/policies (account no longer exists)
874        deleteUnlinked(mDatabase, Mailbox.TABLE_NAME, MailboxColumns.ACCOUNT_KEY, AccountColumns.ID,
875                Account.TABLE_NAME);
876        deleteUnlinked(mDatabase, Message.TABLE_NAME, MessageColumns.ACCOUNT_KEY, AccountColumns.ID,
877                Account.TABLE_NAME);
878        deleteUnlinked(mDatabase, Policy.TABLE_NAME, PolicyColumns.ID, AccountColumns.POLICY_KEY,
879                Account.TABLE_NAME);
880
881        if (Email.DEBUG) {
882            Log.d(TAG, "EmailProvider pre-caching...");
883        }
884        preCacheData();
885        if (Email.DEBUG) {
886            Log.d(TAG, "EmailProvider ready.");
887        }
888        return mDatabase;
889    }
890
891    /**
892     * Pre-cache all of the items in a given table meeting the selection criteria
893     * @param tableUri the table uri
894     * @param baseProjection the base projection of that table
895     * @param selection the selection criteria
896     */
897    private void preCacheTable(Uri tableUri, String[] baseProjection, String selection) {
898        Cursor c = query(tableUri, EmailContent.ID_PROJECTION, selection, null, null);
899        try {
900            while (c.moveToNext()) {
901                long id = c.getLong(EmailContent.ID_PROJECTION_COLUMN);
902                Cursor cachedCursor = query(ContentUris.withAppendedId(
903                        tableUri, id), baseProjection, null, null, null);
904                if (cachedCursor != null) {
905                    // For accounts, create a mailbox type map entry (if necessary)
906                    if (tableUri == Account.CONTENT_URI) {
907                        getOrCreateAccountMailboxTypeMap(id);
908                    }
909                    cachedCursor.close();
910                }
911            }
912        } finally {
913            c.close();
914        }
915    }
916
917    private final HashMap<Long, HashMap<Integer, Long>> mMailboxTypeMap =
918        new HashMap<Long, HashMap<Integer, Long>>();
919
920    private HashMap<Integer, Long> getOrCreateAccountMailboxTypeMap(long accountId) {
921        synchronized(mMailboxTypeMap) {
922            HashMap<Integer, Long> accountMailboxTypeMap = mMailboxTypeMap.get(accountId);
923            if (accountMailboxTypeMap == null) {
924                accountMailboxTypeMap = new HashMap<Integer, Long>();
925                mMailboxTypeMap.put(accountId, accountMailboxTypeMap);
926            }
927            return accountMailboxTypeMap;
928        }
929    }
930
931    private void addToMailboxTypeMap(Cursor c) {
932        long accountId = c.getLong(Mailbox.CONTENT_ACCOUNT_KEY_COLUMN);
933        int type = c.getInt(Mailbox.CONTENT_TYPE_COLUMN);
934        synchronized(mMailboxTypeMap) {
935            HashMap<Integer, Long> accountMailboxTypeMap =
936                getOrCreateAccountMailboxTypeMap(accountId);
937            accountMailboxTypeMap.put(type, c.getLong(Mailbox.CONTENT_ID_COLUMN));
938        }
939    }
940
941    private long getMailboxIdFromMailboxTypeMap(long accountId, int type) {
942        synchronized(mMailboxTypeMap) {
943            HashMap<Integer, Long> accountMap = mMailboxTypeMap.get(accountId);
944            Long mailboxId = null;
945            if (accountMap != null) {
946                mailboxId = accountMap.get(type);
947            }
948            if (mailboxId == null) return Mailbox.NO_MAILBOX;
949            return mailboxId;
950        }
951    }
952
953    private void preCacheData() {
954        synchronized(mMailboxTypeMap) {
955            mMailboxTypeMap.clear();
956
957            // Pre-cache accounts, host auth's, policies, and special mailboxes
958            preCacheTable(Account.CONTENT_URI, Account.CONTENT_PROJECTION, null);
959            preCacheTable(HostAuth.CONTENT_URI, HostAuth.CONTENT_PROJECTION, null);
960            preCacheTable(Policy.CONTENT_URI, Policy.CONTENT_PROJECTION, null);
961            preCacheTable(Mailbox.CONTENT_URI, Mailbox.CONTENT_PROJECTION,
962                    MAILBOX_PRE_CACHE_SELECTION);
963
964            // Create a map from account,type to a mailbox
965            Map<String, Cursor> snapshot = mCacheMailbox.getSnapshot();
966            Collection<Cursor> values = snapshot.values();
967            if (values != null) {
968                for (Cursor c: values) {
969                    if (c.moveToFirst()) {
970                        addToMailboxTypeMap(c);
971                    }
972                }
973            }
974        }
975    }
976
977    /*package*/ static SQLiteDatabase getReadableDatabase(Context context) {
978        DatabaseHelper helper = new DatabaseHelper(context, DATABASE_NAME);
979        return helper.getReadableDatabase();
980    }
981
982    /**
983     * Restore user Account and HostAuth data from our backup database
984     */
985    public static void restoreIfNeeded(Context context, SQLiteDatabase mainDatabase) {
986        if (Email.DEBUG) {
987            Log.w(TAG, "restoreIfNeeded...");
988        }
989        // Check for legacy backup
990        String legacyBackup = Preferences.getLegacyBackupPreference(context);
991        // If there's a legacy backup, create a new-style backup and delete the legacy backup
992        // In the 1:1000000000 chance that the user gets an app update just as his database becomes
993        // corrupt, oh well...
994        if (!TextUtils.isEmpty(legacyBackup)) {
995            backupAccounts(context, mainDatabase);
996            Preferences.clearLegacyBackupPreference(context);
997            Log.w(TAG, "Created new EmailProvider backup database");
998            return;
999        }
1000
1001        // If we have accounts, we're done
1002        Cursor c = mainDatabase.query(Account.TABLE_NAME, EmailContent.ID_PROJECTION, null, null,
1003                null, null, null);
1004        if (c.moveToFirst()) {
1005            if (Email.DEBUG) {
1006                Log.w(TAG, "restoreIfNeeded: Account exists.");
1007            }
1008            return; // At least one account exists.
1009        }
1010        restoreAccounts(context, mainDatabase);
1011    }
1012
1013    /** {@inheritDoc} */
1014    @Override
1015    public void shutdown() {
1016        if (mDatabase != null) {
1017            mDatabase.close();
1018            mDatabase = null;
1019        }
1020        if (mBodyDatabase != null) {
1021            mBodyDatabase.close();
1022            mBodyDatabase = null;
1023        }
1024    }
1025
1026    /*package*/ static void deleteMessageOrphans(SQLiteDatabase database, String tableName) {
1027        if (database != null) {
1028            // We'll look at all of the items in the table; there won't be many typically
1029            Cursor c = database.query(tableName, ORPHANS_PROJECTION, null, null, null, null, null);
1030            // Usually, there will be nothing in these tables, so make a quick check
1031            try {
1032                if (c.getCount() == 0) return;
1033                ArrayList<Long> foundMailboxes = new ArrayList<Long>();
1034                ArrayList<Long> notFoundMailboxes = new ArrayList<Long>();
1035                ArrayList<Long> deleteList = new ArrayList<Long>();
1036                String[] bindArray = new String[1];
1037                while (c.moveToNext()) {
1038                    // Get the mailbox key and see if we've already found this mailbox
1039                    // If so, we're fine
1040                    long mailboxId = c.getLong(ORPHANS_MAILBOX_KEY);
1041                    // If we already know this mailbox doesn't exist, mark the message for deletion
1042                    if (notFoundMailboxes.contains(mailboxId)) {
1043                        deleteList.add(c.getLong(ORPHANS_ID));
1044                    // If we don't know about this mailbox, we'll try to find it
1045                    } else if (!foundMailboxes.contains(mailboxId)) {
1046                        bindArray[0] = Long.toString(mailboxId);
1047                        Cursor boxCursor = database.query(Mailbox.TABLE_NAME,
1048                                Mailbox.ID_PROJECTION, WHERE_ID, bindArray, null, null, null);
1049                        try {
1050                            // If it exists, we'll add it to the "found" mailboxes
1051                            if (boxCursor.moveToFirst()) {
1052                                foundMailboxes.add(mailboxId);
1053                            // Otherwise, we'll add to "not found" and mark the message for deletion
1054                            } else {
1055                                notFoundMailboxes.add(mailboxId);
1056                                deleteList.add(c.getLong(ORPHANS_ID));
1057                            }
1058                        } finally {
1059                            boxCursor.close();
1060                        }
1061                    }
1062                }
1063                // Now, delete the orphan messages
1064                for (long messageId: deleteList) {
1065                    bindArray[0] = Long.toString(messageId);
1066                    database.delete(tableName, WHERE_ID, bindArray);
1067                }
1068            } finally {
1069                c.close();
1070            }
1071        }
1072    }
1073
1074    private class BodyDatabaseHelper extends SQLiteOpenHelper {
1075        BodyDatabaseHelper(Context context, String name) {
1076            super(context, name, null, BODY_DATABASE_VERSION);
1077        }
1078
1079        @Override
1080        public void onCreate(SQLiteDatabase db) {
1081            Log.d(TAG, "Creating EmailProviderBody database");
1082            createBodyTable(db);
1083        }
1084
1085        @Override
1086        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
1087            upgradeBodyTable(db, oldVersion, newVersion);
1088        }
1089
1090        @Override
1091        public void onOpen(SQLiteDatabase db) {
1092        }
1093    }
1094
1095    private static class DatabaseHelper extends SQLiteOpenHelper {
1096        Context mContext;
1097
1098        DatabaseHelper(Context context, String name) {
1099            super(context, name, null, DATABASE_VERSION);
1100            mContext = context;
1101        }
1102
1103        @Override
1104        public void onCreate(SQLiteDatabase db) {
1105            Log.d(TAG, "Creating EmailProvider database");
1106            // Create all tables here; each class has its own method
1107            createMessageTable(db);
1108            createAttachmentTable(db);
1109            createMailboxTable(db);
1110            createHostAuthTable(db);
1111            createAccountTable(db);
1112            createPolicyTable(db);
1113            createQuickResponseTable(db);
1114        }
1115
1116        @Override
1117        @SuppressWarnings("deprecation")
1118        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
1119            // For versions prior to 5, delete all data
1120            // Versions >= 5 require that data be preserved!
1121            if (oldVersion < 5) {
1122                android.accounts.Account[] accounts = AccountManager.get(mContext)
1123                        .getAccountsByType(AccountManagerTypes.TYPE_EXCHANGE);
1124                for (android.accounts.Account account: accounts) {
1125                    AccountManager.get(mContext).removeAccount(account, null, null);
1126                }
1127                resetMessageTable(db, oldVersion, newVersion);
1128                resetAttachmentTable(db, oldVersion, newVersion);
1129                resetMailboxTable(db, oldVersion, newVersion);
1130                resetHostAuthTable(db, oldVersion, newVersion);
1131                resetAccountTable(db, oldVersion, newVersion);
1132                return;
1133            }
1134            if (oldVersion == 5) {
1135                // Message Tables: Add SyncColumns.SERVER_TIMESTAMP
1136                try {
1137                    db.execSQL("alter table " + Message.TABLE_NAME
1138                            + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";");
1139                    db.execSQL("alter table " + Message.UPDATED_TABLE_NAME
1140                            + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";");
1141                    db.execSQL("alter table " + Message.DELETED_TABLE_NAME
1142                            + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";");
1143                } catch (SQLException e) {
1144                    // Shouldn't be needed unless we're debugging and interrupt the process
1145                    Log.w(TAG, "Exception upgrading EmailProvider.db from v5 to v6", e);
1146                }
1147                oldVersion = 6;
1148            }
1149            if (oldVersion == 6) {
1150                // Use the newer mailbox_delete trigger
1151                db.execSQL("drop trigger mailbox_delete;");
1152                db.execSQL(TRIGGER_MAILBOX_DELETE);
1153                oldVersion = 7;
1154            }
1155            if (oldVersion == 7) {
1156                // add the security (provisioning) column
1157                try {
1158                    db.execSQL("alter table " + Account.TABLE_NAME
1159                            + " add column " + AccountColumns.SECURITY_FLAGS + " integer" + ";");
1160                } catch (SQLException e) {
1161                    // Shouldn't be needed unless we're debugging and interrupt the process
1162                    Log.w(TAG, "Exception upgrading EmailProvider.db from 7 to 8 " + e);
1163                }
1164                oldVersion = 8;
1165            }
1166            if (oldVersion == 8) {
1167                // accounts: add security sync key & user signature columns
1168                try {
1169                    db.execSQL("alter table " + Account.TABLE_NAME
1170                            + " add column " + AccountColumns.SECURITY_SYNC_KEY + " text" + ";");
1171                    db.execSQL("alter table " + Account.TABLE_NAME
1172                            + " add column " + AccountColumns.SIGNATURE + " text" + ";");
1173                } catch (SQLException e) {
1174                    // Shouldn't be needed unless we're debugging and interrupt the process
1175                    Log.w(TAG, "Exception upgrading EmailProvider.db from 8 to 9 " + e);
1176                }
1177                oldVersion = 9;
1178            }
1179            if (oldVersion == 9) {
1180                // Message: add meeting info column into Message tables
1181                try {
1182                    db.execSQL("alter table " + Message.TABLE_NAME
1183                            + " add column " + MessageColumns.MEETING_INFO + " text" + ";");
1184                    db.execSQL("alter table " + Message.UPDATED_TABLE_NAME
1185                            + " add column " + MessageColumns.MEETING_INFO + " text" + ";");
1186                    db.execSQL("alter table " + Message.DELETED_TABLE_NAME
1187                            + " add column " + MessageColumns.MEETING_INFO + " text" + ";");
1188                } catch (SQLException e) {
1189                    // Shouldn't be needed unless we're debugging and interrupt the process
1190                    Log.w(TAG, "Exception upgrading EmailProvider.db from 9 to 10 " + e);
1191                }
1192                oldVersion = 10;
1193            }
1194            if (oldVersion == 10) {
1195                // Attachment: add content and flags columns
1196                try {
1197                    db.execSQL("alter table " + Attachment.TABLE_NAME
1198                            + " add column " + AttachmentColumns.CONTENT + " text" + ";");
1199                    db.execSQL("alter table " + Attachment.TABLE_NAME
1200                            + " add column " + AttachmentColumns.FLAGS + " integer" + ";");
1201                } catch (SQLException e) {
1202                    // Shouldn't be needed unless we're debugging and interrupt the process
1203                    Log.w(TAG, "Exception upgrading EmailProvider.db from 10 to 11 " + e);
1204                }
1205                oldVersion = 11;
1206            }
1207            if (oldVersion == 11) {
1208                // Attachment: add content_bytes
1209                try {
1210                    db.execSQL("alter table " + Attachment.TABLE_NAME
1211                            + " add column " + AttachmentColumns.CONTENT_BYTES + " blob" + ";");
1212                } catch (SQLException e) {
1213                    // Shouldn't be needed unless we're debugging and interrupt the process
1214                    Log.w(TAG, "Exception upgrading EmailProvider.db from 11 to 12 " + e);
1215                }
1216                oldVersion = 12;
1217            }
1218            if (oldVersion == 12) {
1219                try {
1220                    db.execSQL("alter table " + Mailbox.TABLE_NAME
1221                            + " add column " + Mailbox.MESSAGE_COUNT
1222                                    +" integer not null default 0" + ";");
1223                    recalculateMessageCount(db);
1224                } catch (SQLException e) {
1225                    // Shouldn't be needed unless we're debugging and interrupt the process
1226                    Log.w(TAG, "Exception upgrading EmailProvider.db from 12 to 13 " + e);
1227                }
1228                oldVersion = 13;
1229            }
1230            if (oldVersion == 13) {
1231                try {
1232                    db.execSQL("alter table " + Message.TABLE_NAME
1233                            + " add column " + Message.SNIPPET
1234                                    +" text" + ";");
1235                } catch (SQLException e) {
1236                    // Shouldn't be needed unless we're debugging and interrupt the process
1237                    Log.w(TAG, "Exception upgrading EmailProvider.db from 13 to 14 " + e);
1238                }
1239                oldVersion = 14;
1240            }
1241            if (oldVersion == 14) {
1242                try {
1243                    db.execSQL("alter table " + Message.DELETED_TABLE_NAME
1244                            + " add column " + Message.SNIPPET +" text" + ";");
1245                    db.execSQL("alter table " + Message.UPDATED_TABLE_NAME
1246                            + " add column " + Message.SNIPPET +" text" + ";");
1247                } catch (SQLException e) {
1248                    // Shouldn't be needed unless we're debugging and interrupt the process
1249                    Log.w(TAG, "Exception upgrading EmailProvider.db from 14 to 15 " + e);
1250                }
1251                oldVersion = 15;
1252            }
1253            if (oldVersion == 15) {
1254                try {
1255                    db.execSQL("alter table " + Attachment.TABLE_NAME
1256                            + " add column " + Attachment.ACCOUNT_KEY +" integer" + ";");
1257                    // Update all existing attachments to add the accountKey data
1258                    db.execSQL("update " + Attachment.TABLE_NAME + " set " +
1259                            Attachment.ACCOUNT_KEY + "= (SELECT " + Message.TABLE_NAME + "." +
1260                            Message.ACCOUNT_KEY + " from " + Message.TABLE_NAME + " where " +
1261                            Message.TABLE_NAME + "." + Message.RECORD_ID + " = " +
1262                            Attachment.TABLE_NAME + "." + Attachment.MESSAGE_KEY + ")");
1263                } catch (SQLException e) {
1264                    // Shouldn't be needed unless we're debugging and interrupt the process
1265                    Log.w(TAG, "Exception upgrading EmailProvider.db from 15 to 16 " + e);
1266                }
1267                oldVersion = 16;
1268            }
1269            if (oldVersion == 16) {
1270                try {
1271                    db.execSQL("alter table " + Mailbox.TABLE_NAME
1272                            + " add column " + Mailbox.PARENT_KEY + " integer;");
1273                } catch (SQLException e) {
1274                    // Shouldn't be needed unless we're debugging and interrupt the process
1275                    Log.w(TAG, "Exception upgrading EmailProvider.db from 16 to 17 " + e);
1276                }
1277                oldVersion = 17;
1278            }
1279            if (oldVersion == 17) {
1280                upgradeFromVersion17ToVersion18(db);
1281                oldVersion = 18;
1282            }
1283            if (oldVersion == 18) {
1284                try {
1285                    db.execSQL("alter table " + Account.TABLE_NAME
1286                            + " add column " + Account.POLICY_KEY + " integer;");
1287                    db.execSQL("drop trigger account_delete;");
1288                    db.execSQL(TRIGGER_ACCOUNT_DELETE);
1289                    createPolicyTable(db);
1290                    convertPolicyFlagsToPolicyTable(db);
1291                } catch (SQLException e) {
1292                    // Shouldn't be needed unless we're debugging and interrupt the process
1293                    Log.w(TAG, "Exception upgrading EmailProvider.db from 18 to 19 " + e);
1294                }
1295                oldVersion = 19;
1296            }
1297            if (oldVersion == 19) {
1298                try {
1299                    db.execSQL("alter table " + Policy.TABLE_NAME
1300                            + " add column " + PolicyColumns.REQUIRE_MANUAL_SYNC_WHEN_ROAMING +
1301                            " integer;");
1302                    db.execSQL("alter table " + Policy.TABLE_NAME
1303                            + " add column " + PolicyColumns.DONT_ALLOW_CAMERA + " integer;");
1304                    db.execSQL("alter table " + Policy.TABLE_NAME
1305                            + " add column " + PolicyColumns.DONT_ALLOW_ATTACHMENTS + " integer;");
1306                    db.execSQL("alter table " + Policy.TABLE_NAME
1307                            + " add column " + PolicyColumns.DONT_ALLOW_HTML + " integer;");
1308                    db.execSQL("alter table " + Policy.TABLE_NAME
1309                            + " add column " + PolicyColumns.MAX_ATTACHMENT_SIZE + " integer;");
1310                    db.execSQL("alter table " + Policy.TABLE_NAME
1311                            + " add column " + PolicyColumns.MAX_TEXT_TRUNCATION_SIZE +
1312                            " integer;");
1313                    db.execSQL("alter table " + Policy.TABLE_NAME
1314                            + " add column " + PolicyColumns.MAX_HTML_TRUNCATION_SIZE +
1315                            " integer;");
1316                    db.execSQL("alter table " + Policy.TABLE_NAME
1317                            + " add column " + PolicyColumns.MAX_EMAIL_LOOKBACK + " integer;");
1318                    db.execSQL("alter table " + Policy.TABLE_NAME
1319                            + " add column " + PolicyColumns.MAX_CALENDAR_LOOKBACK + " integer;");
1320                    db.execSQL("alter table " + Policy.TABLE_NAME
1321                            + " add column " + PolicyColumns.PASSWORD_RECOVERY_ENABLED +
1322                            " integer;");
1323                } catch (SQLException e) {
1324                    // Shouldn't be needed unless we're debugging and interrupt the process
1325                    Log.w(TAG, "Exception upgrading EmailProvider.db from 19 to 20 " + e);
1326                }
1327                oldVersion = 20;
1328            }
1329            if (oldVersion == 20) {
1330                upgradeFromVersion20ToVersion21(db);
1331                oldVersion = 21;
1332            }
1333            if (oldVersion == 21) {
1334                upgradeFromVersion21ToVersion22(db, mContext);
1335                oldVersion = 22;
1336            }
1337            if (oldVersion == 22) {
1338                upgradeFromVersion22ToVersion23(db);
1339                oldVersion = 23;
1340            }
1341            if (oldVersion == 23) {
1342                upgradeFromVersion23ToVersion24(db);
1343                oldVersion = 24;
1344            }
1345            if (oldVersion == 24) {
1346                upgradeFromVersion24ToVersion25(db);
1347                oldVersion = 25;
1348            }
1349            if (oldVersion == 25) {
1350                upgradeFromVersion25ToVersion26(db);
1351                oldVersion = 26;
1352            }
1353            if (oldVersion == 26) {
1354                try {
1355                    db.execSQL("alter table " + Message.TABLE_NAME
1356                            + " add column " + Message.PROTOCOL_SEARCH_INFO + " text;");
1357                    db.execSQL("alter table " + Message.DELETED_TABLE_NAME
1358                            + " add column " + Message.PROTOCOL_SEARCH_INFO +" text" + ";");
1359                    db.execSQL("alter table " + Message.UPDATED_TABLE_NAME
1360                            + " add column " + Message.PROTOCOL_SEARCH_INFO +" text" + ";");
1361                } catch (SQLException e) {
1362                    // Shouldn't be needed unless we're debugging and interrupt the process
1363                    Log.w(TAG, "Exception upgrading EmailProvider.db from 26 to 27 " + e);
1364                }
1365                oldVersion = 27;
1366            }
1367            if (oldVersion == 27) {
1368                try {
1369                    db.execSQL("alter table " + Account.TABLE_NAME
1370                            + " add column " + Account.NOTIFIED_MESSAGE_ID + " integer;");
1371                    db.execSQL("alter table " + Account.TABLE_NAME
1372                            + " add column " + Account.NOTIFIED_MESSAGE_COUNT + " integer;");
1373                } catch (SQLException e) {
1374                    // Shouldn't be needed unless we're debugging and interrupt the process
1375                    Log.w(TAG, "Exception upgrading EmailProvider.db from 27 to 28 " + e);
1376                }
1377                oldVersion = 28;
1378            }
1379            if (oldVersion == 28) {
1380                try {
1381                    db.execSQL("alter table " + Policy.TABLE_NAME
1382                            + " add column " + Policy.PROTOCOL_POLICIES_ENFORCED + " text;");
1383                    db.execSQL("alter table " + Policy.TABLE_NAME
1384                            + " add column " + Policy.PROTOCOL_POLICIES_UNSUPPORTED + " text;");
1385                } catch (SQLException e) {
1386                    // Shouldn't be needed unless we're debugging and interrupt the process
1387                    Log.w(TAG, "Exception upgrading EmailProvider.db from 28 to 29 " + e);
1388                }
1389                oldVersion = 29;
1390            }
1391        }
1392
1393        @Override
1394        public void onOpen(SQLiteDatabase db) {
1395        }
1396    }
1397
1398    @Override
1399    public int delete(Uri uri, String selection, String[] selectionArgs) {
1400        final int match = findMatch(uri, "delete");
1401        Context context = getContext();
1402        // Pick the correct database for this operation
1403        // If we're in a transaction already (which would happen during applyBatch), then the
1404        // body database is already attached to the email database and any attempt to use the
1405        // body database directly will result in a SQLiteException (the database is locked)
1406        SQLiteDatabase db = getDatabase(context);
1407        int table = match >> BASE_SHIFT;
1408        String id = "0";
1409        boolean messageDeletion = false;
1410        ContentResolver resolver = context.getContentResolver();
1411
1412        ContentCache cache = mContentCaches[table];
1413        String tableName = TABLE_NAMES[table];
1414        int result = -1;
1415
1416        try {
1417            switch (match) {
1418                // These are cases in which one or more Messages might get deleted, either by
1419                // cascade or explicitly
1420                case MAILBOX_ID:
1421                case MAILBOX:
1422                case ACCOUNT_ID:
1423                case ACCOUNT:
1424                case MESSAGE:
1425                case SYNCED_MESSAGE_ID:
1426                case MESSAGE_ID:
1427                    // Handle lost Body records here, since this cannot be done in a trigger
1428                    // The process is:
1429                    //  1) Begin a transaction, ensuring that both databases are affected atomically
1430                    //  2) Do the requested deletion, with cascading deletions handled in triggers
1431                    //  3) End the transaction, committing all changes atomically
1432                    //
1433                    // Bodies are auto-deleted here;  Attachments are auto-deleted via trigger
1434                    messageDeletion = true;
1435                    db.beginTransaction();
1436                    break;
1437            }
1438            switch (match) {
1439                case BODY_ID:
1440                case DELETED_MESSAGE_ID:
1441                case SYNCED_MESSAGE_ID:
1442                case MESSAGE_ID:
1443                case UPDATED_MESSAGE_ID:
1444                case ATTACHMENT_ID:
1445                case MAILBOX_ID:
1446                case ACCOUNT_ID:
1447                case HOSTAUTH_ID:
1448                case POLICY_ID:
1449                case QUICK_RESPONSE_ID:
1450                    id = uri.getPathSegments().get(1);
1451                    if (match == SYNCED_MESSAGE_ID) {
1452                        // For synced messages, first copy the old message to the deleted table and
1453                        // delete it from the updated table (in case it was updated first)
1454                        // Note that this is all within a transaction, for atomicity
1455                        db.execSQL(DELETED_MESSAGE_INSERT + id);
1456                        db.execSQL(UPDATED_MESSAGE_DELETE + id);
1457                    }
1458                    if (cache != null) {
1459                        cache.lock(id);
1460                    }
1461                    try {
1462                        result = db.delete(tableName, whereWithId(id, selection), selectionArgs);
1463                        if (cache != null) {
1464                            switch(match) {
1465                                case ACCOUNT_ID:
1466                                    // Account deletion will clear all of the caches, as HostAuth's,
1467                                    // Mailboxes, and Messages will be deleted in the process
1468                                    mCacheMailbox.invalidate("Delete", uri, selection);
1469                                    mCacheHostAuth.invalidate("Delete", uri, selection);
1470                                    mCachePolicy.invalidate("Delete", uri, selection);
1471                                    //$FALL-THROUGH$
1472                                case MAILBOX_ID:
1473                                    // Mailbox deletion will clear the Message cache
1474                                    mCacheMessage.invalidate("Delete", uri, selection);
1475                                    //$FALL-THROUGH$
1476                                case SYNCED_MESSAGE_ID:
1477                                case MESSAGE_ID:
1478                                case HOSTAUTH_ID:
1479                                case POLICY_ID:
1480                                    cache.invalidate("Delete", uri, selection);
1481                                    // Make sure all data is properly cached
1482                                    if (match != MESSAGE_ID) {
1483                                        preCacheData();
1484                                    }
1485                                    break;
1486                            }
1487                        }
1488                    } finally {
1489                        if (cache != null) {
1490                            cache.unlock(id);
1491                        }
1492                    }
1493                    break;
1494                case ATTACHMENTS_MESSAGE_ID:
1495                    // All attachments for the given message
1496                    id = uri.getPathSegments().get(2);
1497                    result = db.delete(tableName,
1498                            whereWith(Attachment.MESSAGE_KEY + "=" + id, selection), selectionArgs);
1499                    break;
1500
1501                case BODY:
1502                case MESSAGE:
1503                case DELETED_MESSAGE:
1504                case UPDATED_MESSAGE:
1505                case ATTACHMENT:
1506                case MAILBOX:
1507                case ACCOUNT:
1508                case HOSTAUTH:
1509                case POLICY:
1510                    switch(match) {
1511                        // See the comments above for deletion of ACCOUNT_ID, etc
1512                        case ACCOUNT:
1513                            mCacheMailbox.invalidate("Delete", uri, selection);
1514                            mCacheHostAuth.invalidate("Delete", uri, selection);
1515                            mCachePolicy.invalidate("Delete", uri, selection);
1516                            //$FALL-THROUGH$
1517                        case MAILBOX:
1518                            mCacheMessage.invalidate("Delete", uri, selection);
1519                            //$FALL-THROUGH$
1520                        case MESSAGE:
1521                        case HOSTAUTH:
1522                        case POLICY:
1523                            cache.invalidate("Delete", uri, selection);
1524                            break;
1525                    }
1526                    result = db.delete(tableName, selection, selectionArgs);
1527                    switch(match) {
1528                        case ACCOUNT:
1529                        case MAILBOX:
1530                        case HOSTAUTH:
1531                        case POLICY:
1532                            // Make sure all data is properly cached
1533                            preCacheData();
1534                            break;
1535                    }
1536                    break;
1537
1538                default:
1539                    throw new IllegalArgumentException("Unknown URI " + uri);
1540            }
1541            if (messageDeletion) {
1542                if (match == MESSAGE_ID) {
1543                    // Delete the Body record associated with the deleted message
1544                    db.execSQL(DELETE_BODY + id);
1545                } else {
1546                    // Delete any orphaned Body records
1547                    db.execSQL(DELETE_ORPHAN_BODIES);
1548                }
1549                db.setTransactionSuccessful();
1550            }
1551        } catch (SQLiteException e) {
1552            checkDatabases();
1553            throw e;
1554        } finally {
1555            if (messageDeletion) {
1556                db.endTransaction();
1557            }
1558        }
1559
1560        // Notify all notifier cursors
1561        sendNotifierChange(getBaseNotificationUri(match), NOTIFICATION_OP_DELETE, id);
1562
1563        // Notify all email content cursors
1564        resolver.notifyChange(EmailContent.CONTENT_URI, null);
1565        return result;
1566    }
1567
1568    @Override
1569    // Use the email- prefix because message, mailbox, and account are so generic (e.g. SMS, IM)
1570    public String getType(Uri uri) {
1571        int match = findMatch(uri, "getType");
1572        switch (match) {
1573            case BODY_ID:
1574                return "vnd.android.cursor.item/email-body";
1575            case BODY:
1576                return "vnd.android.cursor.dir/email-body";
1577            case UPDATED_MESSAGE_ID:
1578            case MESSAGE_ID:
1579                // NOTE: According to the framework folks, we're supposed to invent mime types as
1580                // a way of passing information to drag & drop recipients.
1581                // If there's a mailboxId parameter in the url, we respond with a mime type that
1582                // has -n appended, where n is the mailboxId of the message.  The drag & drop code
1583                // uses this information to know not to allow dragging the item to its own mailbox
1584                String mimeType = EMAIL_MESSAGE_MIME_TYPE;
1585                String mailboxId = uri.getQueryParameter(MESSAGE_URI_PARAMETER_MAILBOX_ID);
1586                if (mailboxId != null) {
1587                    mimeType += "-" + mailboxId;
1588                }
1589                return mimeType;
1590            case UPDATED_MESSAGE:
1591            case MESSAGE:
1592                return "vnd.android.cursor.dir/email-message";
1593            case MAILBOX:
1594                return "vnd.android.cursor.dir/email-mailbox";
1595            case MAILBOX_ID:
1596                return "vnd.android.cursor.item/email-mailbox";
1597            case ACCOUNT:
1598                return "vnd.android.cursor.dir/email-account";
1599            case ACCOUNT_ID:
1600                return "vnd.android.cursor.item/email-account";
1601            case ATTACHMENTS_MESSAGE_ID:
1602            case ATTACHMENT:
1603                return "vnd.android.cursor.dir/email-attachment";
1604            case ATTACHMENT_ID:
1605                return EMAIL_ATTACHMENT_MIME_TYPE;
1606            case HOSTAUTH:
1607                return "vnd.android.cursor.dir/email-hostauth";
1608            case HOSTAUTH_ID:
1609                return "vnd.android.cursor.item/email-hostauth";
1610            default:
1611                throw new IllegalArgumentException("Unknown URI " + uri);
1612        }
1613    }
1614
1615    private static final Uri UIPROVIDER_NEW_MESSAGE_NOTIFIER =
1616            Uri.parse("content://" + EmailContent.AUTHORITY + "/uimessages");
1617
1618    @Override
1619    public Uri insert(Uri uri, ContentValues values) {
1620        int match = findMatch(uri, "insert");
1621        Context context = getContext();
1622        ContentResolver resolver = context.getContentResolver();
1623
1624        // See the comment at delete(), above
1625        SQLiteDatabase db = getDatabase(context);
1626        int table = match >> BASE_SHIFT;
1627        String id = "0";
1628        long longId;
1629
1630        // We do NOT allow setting of unreadCount/messageCount via the provider
1631        // These columns are maintained via triggers
1632        if (match == MAILBOX_ID || match == MAILBOX) {
1633            values.put(MailboxColumns.UNREAD_COUNT, 0);
1634            values.put(MailboxColumns.MESSAGE_COUNT, 0);
1635        }
1636
1637        Uri resultUri = null;
1638
1639        try {
1640            switch (match) {
1641                // NOTE: It is NOT legal for production code to insert directly into UPDATED_MESSAGE
1642                // or DELETED_MESSAGE; see the comment below for details
1643                case UPDATED_MESSAGE:
1644                case DELETED_MESSAGE:
1645                case MESSAGE:
1646                case BODY:
1647                case ATTACHMENT:
1648                case MAILBOX:
1649                case ACCOUNT:
1650                case HOSTAUTH:
1651                case POLICY:
1652                case QUICK_RESPONSE:
1653                    longId = db.insert(TABLE_NAMES[table], "foo", values);
1654                    resultUri = ContentUris.withAppendedId(uri, longId);
1655                    switch(match) {
1656                        case MESSAGE:
1657                            resolver.notifyChange(UIPROVIDER_NEW_MESSAGE_NOTIFIER, null);
1658                            break;
1659                        case MAILBOX:
1660                            if (values.containsKey(MailboxColumns.TYPE)) {
1661                                // Only cache special mailbox types
1662                                int type = values.getAsInteger(MailboxColumns.TYPE);
1663                                if (type != Mailbox.TYPE_INBOX && type != Mailbox.TYPE_OUTBOX &&
1664                                        type != Mailbox.TYPE_DRAFTS && type != Mailbox.TYPE_SENT &&
1665                                        type != Mailbox.TYPE_TRASH && type != Mailbox.TYPE_SEARCH) {
1666                                    break;
1667                                }
1668                            }
1669                            //$FALL-THROUGH$
1670                        case ACCOUNT:
1671                        case HOSTAUTH:
1672                        case POLICY:
1673                            // Cache new account, host auth, policy, and some mailbox rows
1674                            Cursor c = query(resultUri, CACHE_PROJECTIONS[table], null, null, null);
1675                            if (c != null) {
1676                                if (match == MAILBOX) {
1677                                    addToMailboxTypeMap(c);
1678                                } else if (match == ACCOUNT) {
1679                                    getOrCreateAccountMailboxTypeMap(longId);
1680                                }
1681                                c.close();
1682                            }
1683                            break;
1684                    }
1685                    // Clients shouldn't normally be adding rows to these tables, as they are
1686                    // maintained by triggers.  However, we need to be able to do this for unit
1687                    // testing, so we allow the insert and then throw the same exception that we
1688                    // would if this weren't allowed.
1689                    if (match == UPDATED_MESSAGE || match == DELETED_MESSAGE) {
1690                        throw new IllegalArgumentException("Unknown URL " + uri);
1691                    }
1692                    if (match == ATTACHMENT) {
1693                        int flags = 0;
1694                        if (values.containsKey(Attachment.FLAGS)) {
1695                            flags = values.getAsInteger(Attachment.FLAGS);
1696                        }
1697                        // Report all new attachments to the download service
1698                        mAttachmentService.attachmentChanged(getContext(), longId, flags);
1699                    }
1700                    break;
1701                case MAILBOX_ID:
1702                    // This implies adding a message to a mailbox
1703                    // Hmm, a problem here is that we can't link the account as well, so it must be
1704                    // already in the values...
1705                    longId = Long.parseLong(uri.getPathSegments().get(1));
1706                    values.put(MessageColumns.MAILBOX_KEY, longId);
1707                    return insert(Message.CONTENT_URI, values); // Recurse
1708                case MESSAGE_ID:
1709                    // This implies adding an attachment to a message.
1710                    id = uri.getPathSegments().get(1);
1711                    longId = Long.parseLong(id);
1712                    values.put(AttachmentColumns.MESSAGE_KEY, longId);
1713                    return insert(Attachment.CONTENT_URI, values); // Recurse
1714                case ACCOUNT_ID:
1715                    // This implies adding a mailbox to an account.
1716                    longId = Long.parseLong(uri.getPathSegments().get(1));
1717                    values.put(MailboxColumns.ACCOUNT_KEY, longId);
1718                    return insert(Mailbox.CONTENT_URI, values); // Recurse
1719                case ATTACHMENTS_MESSAGE_ID:
1720                    longId = db.insert(TABLE_NAMES[table], "foo", values);
1721                    resultUri = ContentUris.withAppendedId(Attachment.CONTENT_URI, longId);
1722                    break;
1723                default:
1724                    throw new IllegalArgumentException("Unknown URL " + uri);
1725            }
1726        } catch (SQLiteException e) {
1727            checkDatabases();
1728            throw e;
1729        }
1730
1731        // Notify all notifier cursors
1732        sendNotifierChange(getBaseNotificationUri(match), NOTIFICATION_OP_INSERT, id);
1733
1734        // Notify all existing cursors.
1735        resolver.notifyChange(EmailContent.CONTENT_URI, null);
1736        return resultUri;
1737    }
1738
1739    @Override
1740    public boolean onCreate() {
1741        checkDatabases();
1742        return false;
1743    }
1744
1745    /**
1746     * The idea here is that the two databases (EmailProvider.db and EmailProviderBody.db must
1747     * always be in sync (i.e. there are two database or NO databases).  This code will delete
1748     * any "orphan" database, so that both will be created together.  Note that an "orphan" database
1749     * will exist after either of the individual databases is deleted due to data corruption.
1750     */
1751    public void checkDatabases() {
1752        // Uncache the databases
1753        if (mDatabase != null) {
1754            mDatabase = null;
1755        }
1756        if (mBodyDatabase != null) {
1757            mBodyDatabase = null;
1758        }
1759        // Look for orphans, and delete as necessary; these must always be in sync
1760        File databaseFile = getContext().getDatabasePath(DATABASE_NAME);
1761        File bodyFile = getContext().getDatabasePath(BODY_DATABASE_NAME);
1762
1763        // TODO Make sure attachments are deleted
1764        if (databaseFile.exists() && !bodyFile.exists()) {
1765            Log.w(TAG, "Deleting orphaned EmailProvider database...");
1766            databaseFile.delete();
1767        } else if (bodyFile.exists() && !databaseFile.exists()) {
1768            Log.w(TAG, "Deleting orphaned EmailProviderBody database...");
1769            bodyFile.delete();
1770        }
1771    }
1772    @Override
1773    public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs,
1774            String sortOrder) {
1775        long time = 0L;
1776        if (Email.DEBUG) {
1777            time = System.nanoTime();
1778        }
1779        Cursor c = null;
1780        int match;
1781        try {
1782            match = findMatch(uri, "query");
1783        } catch (IllegalArgumentException e) {
1784            String uriString = uri.toString();
1785            // If we were passed an illegal uri, see if it ends in /-1
1786            // if so, and if substituting 0 for -1 results in a valid uri, return an empty cursor
1787            if (uriString != null && uriString.endsWith("/-1")) {
1788                uri = Uri.parse(uriString.substring(0, uriString.length() - 2) + "0");
1789                match = findMatch(uri, "query");
1790                switch (match) {
1791                    case BODY_ID:
1792                    case MESSAGE_ID:
1793                    case DELETED_MESSAGE_ID:
1794                    case UPDATED_MESSAGE_ID:
1795                    case ATTACHMENT_ID:
1796                    case MAILBOX_ID:
1797                    case ACCOUNT_ID:
1798                    case HOSTAUTH_ID:
1799                    case POLICY_ID:
1800                        return new MatrixCursor(projection, 0);
1801                }
1802            }
1803            throw e;
1804        }
1805        Context context = getContext();
1806        // See the comment at delete(), above
1807        SQLiteDatabase db = getDatabase(context);
1808        int table = match >> BASE_SHIFT;
1809        String limit = uri.getQueryParameter(EmailContent.PARAMETER_LIMIT);
1810        String id;
1811
1812        // Find the cache for this query's table (if any)
1813        ContentCache cache = null;
1814        String tableName = TABLE_NAMES[table];
1815        // We can only use the cache if there's no selection
1816        if (selection == null) {
1817            cache = mContentCaches[table];
1818        }
1819        if (cache == null) {
1820            ContentCache.notCacheable(uri, selection);
1821        }
1822
1823        try {
1824            switch (match) {
1825                // First, dispatch queries from UnfiedEmail
1826                case UI_FOLDERS:
1827                case UI_MESSAGES:
1828                case UI_MESSAGE:
1829                    // For now, we don't allow selection criteria within these queries
1830                    if (selection != null || selectionArgs != null) {
1831                        throw new IllegalArgumentException("UI queries can't have selection/args");
1832                    }
1833                    c = uiQuery(match, uri, projection);
1834                    return c;
1835                case ACCOUNT_DEFAULT_ID:
1836                    // Start with a snapshot of the cache
1837                    Map<String, Cursor> accountCache = mCacheAccount.getSnapshot();
1838                    long accountId = Account.NO_ACCOUNT;
1839                    // Find the account with "isDefault" set, or the lowest account ID otherwise.
1840                    // Note that the snapshot from the cached isn't guaranteed to be sorted in any
1841                    // way.
1842                    Collection<Cursor> accounts = accountCache.values();
1843                    for (Cursor accountCursor: accounts) {
1844                        // For now, at least, we can have zero count cursors (e.g. if someone looks
1845                        // up a non-existent id); we need to skip these
1846                        if (accountCursor.moveToFirst()) {
1847                            boolean isDefault =
1848                                accountCursor.getInt(Account.CONTENT_IS_DEFAULT_COLUMN) == 1;
1849                            long iterId = accountCursor.getLong(Account.CONTENT_ID_COLUMN);
1850                            // We'll remember this one if it's the default or the first one we see
1851                            if (isDefault) {
1852                                accountId = iterId;
1853                                break;
1854                            } else if ((accountId == Account.NO_ACCOUNT) || (iterId < accountId)) {
1855                                accountId = iterId;
1856                            }
1857                        }
1858                    }
1859                    // Return a cursor with an id projection
1860                    MatrixCursor mc = new MatrixCursor(EmailContent.ID_PROJECTION);
1861                    mc.addRow(new Object[] {accountId});
1862                    c = mc;
1863                    break;
1864                case MAILBOX_ID_FROM_ACCOUNT_AND_TYPE:
1865                    // Get accountId and type and find the mailbox in our map
1866                    List<String> pathSegments = uri.getPathSegments();
1867                    accountId = Long.parseLong(pathSegments.get(1));
1868                    int type = Integer.parseInt(pathSegments.get(2));
1869                    long mailboxId = getMailboxIdFromMailboxTypeMap(accountId, type);
1870                    // Return a cursor with an id projection
1871                    mc = new MatrixCursor(EmailContent.ID_PROJECTION);
1872                    mc.addRow(new Object[] {mailboxId});
1873                    c = mc;
1874                    break;
1875                case BODY:
1876                case MESSAGE:
1877                case UPDATED_MESSAGE:
1878                case DELETED_MESSAGE:
1879                case ATTACHMENT:
1880                case MAILBOX:
1881                case ACCOUNT:
1882                case HOSTAUTH:
1883                case POLICY:
1884                case QUICK_RESPONSE:
1885                    // Special-case "count of accounts"; it's common and we always know it
1886                    if (match == ACCOUNT && Arrays.equals(projection, EmailContent.COUNT_COLUMNS) &&
1887                            selection == null && limit.equals("1")) {
1888                        int accountCount = mMailboxTypeMap.size();
1889                        // In the rare case there are MAX_CACHED_ACCOUNTS or more, we can't do this
1890                        if (accountCount < MAX_CACHED_ACCOUNTS) {
1891                            mc = new MatrixCursor(projection, 1);
1892                            mc.addRow(new Object[] {accountCount});
1893                            c = mc;
1894                            break;
1895                        }
1896                    }
1897                    c = db.query(tableName, projection,
1898                            selection, selectionArgs, null, null, sortOrder, limit);
1899                    break;
1900                case BODY_ID:
1901                case MESSAGE_ID:
1902                case DELETED_MESSAGE_ID:
1903                case UPDATED_MESSAGE_ID:
1904                case ATTACHMENT_ID:
1905                case MAILBOX_ID:
1906                case ACCOUNT_ID:
1907                case HOSTAUTH_ID:
1908                case POLICY_ID:
1909                case QUICK_RESPONSE_ID:
1910                    id = uri.getPathSegments().get(1);
1911                    if (cache != null) {
1912                        c = cache.getCachedCursor(id, projection);
1913                    }
1914                    if (c == null) {
1915                        CacheToken token = null;
1916                        if (cache != null) {
1917                            token = cache.getCacheToken(id);
1918                        }
1919                        c = db.query(tableName, projection, whereWithId(id, selection),
1920                                selectionArgs, null, null, sortOrder, limit);
1921                        if (cache != null) {
1922                            c = cache.putCursor(c, id, projection, token);
1923                        }
1924                    }
1925                    break;
1926                case ATTACHMENTS_MESSAGE_ID:
1927                    // All attachments for the given message
1928                    id = uri.getPathSegments().get(2);
1929                    c = db.query(Attachment.TABLE_NAME, projection,
1930                            whereWith(Attachment.MESSAGE_KEY + "=" + id, selection),
1931                            selectionArgs, null, null, sortOrder, limit);
1932                    break;
1933                case QUICK_RESPONSE_ACCOUNT_ID:
1934                    // All quick responses for the given account
1935                    id = uri.getPathSegments().get(2);
1936                    c = db.query(QuickResponse.TABLE_NAME, projection,
1937                            whereWith(QuickResponse.ACCOUNT_KEY + "=" + id, selection),
1938                            selectionArgs, null, null, sortOrder);
1939                    break;
1940                default:
1941                    throw new IllegalArgumentException("Unknown URI " + uri);
1942            }
1943        } catch (SQLiteException e) {
1944            checkDatabases();
1945            throw e;
1946        } catch (RuntimeException e) {
1947            checkDatabases();
1948            e.printStackTrace();
1949            throw e;
1950        } finally {
1951            if (cache != null && c != null && Email.DEBUG) {
1952                cache.recordQueryTime(c, System.nanoTime() - time);
1953            }
1954            if (c == null) {
1955                // This should never happen, but let's be sure to log it...
1956                Log.e(TAG, "Query returning null for uri: " + uri + ", selection: " + selection);
1957            }
1958        }
1959
1960        if ((c != null) && !isTemporary()) {
1961            c.setNotificationUri(getContext().getContentResolver(), uri);
1962        }
1963        return c;
1964    }
1965
1966    private String whereWithId(String id, String selection) {
1967        StringBuilder sb = new StringBuilder(256);
1968        sb.append("_id=");
1969        sb.append(id);
1970        if (selection != null) {
1971            sb.append(" AND (");
1972            sb.append(selection);
1973            sb.append(')');
1974        }
1975        return sb.toString();
1976    }
1977
1978    /**
1979     * Combine a locally-generated selection with a user-provided selection
1980     *
1981     * This introduces risk that the local selection might insert incorrect chars
1982     * into the SQL, so use caution.
1983     *
1984     * @param where locally-generated selection, must not be null
1985     * @param selection user-provided selection, may be null
1986     * @return a single selection string
1987     */
1988    private String whereWith(String where, String selection) {
1989        if (selection == null) {
1990            return where;
1991        }
1992        StringBuilder sb = new StringBuilder(where);
1993        sb.append(" AND (");
1994        sb.append(selection);
1995        sb.append(')');
1996
1997        return sb.toString();
1998    }
1999
2000    /**
2001     * Restore a HostAuth from a database, given its unique id
2002     * @param db the database
2003     * @param id the unique id (_id) of the row
2004     * @return a fully populated HostAuth or null if the row does not exist
2005     */
2006    private static HostAuth restoreHostAuth(SQLiteDatabase db, long id) {
2007        Cursor c = db.query(HostAuth.TABLE_NAME, HostAuth.CONTENT_PROJECTION,
2008                HostAuth.RECORD_ID + "=?", new String[] {Long.toString(id)}, null, null, null);
2009        try {
2010            if (c.moveToFirst()) {
2011                HostAuth hostAuth = new HostAuth();
2012                hostAuth.restore(c);
2013                return hostAuth;
2014            }
2015            return null;
2016        } finally {
2017            c.close();
2018        }
2019    }
2020
2021    /**
2022     * Copy the Account and HostAuth tables from one database to another
2023     * @param fromDatabase the source database
2024     * @param toDatabase the destination database
2025     * @return the number of accounts copied, or -1 if an error occurred
2026     */
2027    private static int copyAccountTables(SQLiteDatabase fromDatabase, SQLiteDatabase toDatabase) {
2028        if (fromDatabase == null || toDatabase == null) return -1;
2029        int copyCount = 0;
2030        try {
2031            // Lock both databases; for the "from" database, we don't want anyone changing it from
2032            // under us; for the "to" database, we want to make the operation atomic
2033            fromDatabase.beginTransaction();
2034            toDatabase.beginTransaction();
2035            // Delete anything hanging around here
2036            toDatabase.delete(Account.TABLE_NAME, null, null);
2037            toDatabase.delete(HostAuth.TABLE_NAME, null, null);
2038            // Get our account cursor
2039            Cursor c = fromDatabase.query(Account.TABLE_NAME, Account.CONTENT_PROJECTION,
2040                    null, null, null, null, null);
2041            boolean noErrors = true;
2042            try {
2043                // Loop through accounts, copying them and associated host auth's
2044                while (c.moveToNext()) {
2045                    Account account = new Account();
2046                    account.restore(c);
2047
2048                    // Clear security sync key and sync key, as these were specific to the state of
2049                    // the account, and we've reset that...
2050                    // Clear policy key so that we can re-establish policies from the server
2051                    // TODO This is pretty EAS specific, but there's a lot of that around
2052                    account.mSecuritySyncKey = null;
2053                    account.mSyncKey = null;
2054                    account.mPolicyKey = 0;
2055
2056                    // Copy host auth's and update foreign keys
2057                    HostAuth hostAuth = restoreHostAuth(fromDatabase, account.mHostAuthKeyRecv);
2058                    // The account might have gone away, though very unlikely
2059                    if (hostAuth == null) continue;
2060                    account.mHostAuthKeyRecv = toDatabase.insert(HostAuth.TABLE_NAME, null,
2061                            hostAuth.toContentValues());
2062                    // EAS accounts have no send HostAuth
2063                    if (account.mHostAuthKeySend > 0) {
2064                        hostAuth = restoreHostAuth(fromDatabase, account.mHostAuthKeySend);
2065                        // Belt and suspenders; I can't imagine that this is possible, since we
2066                        // checked the validity of the account above, and the database is now locked
2067                        if (hostAuth == null) continue;
2068                        account.mHostAuthKeySend = toDatabase.insert(HostAuth.TABLE_NAME, null,
2069                                hostAuth.toContentValues());
2070                    }
2071                    // Now, create the account in the "to" database
2072                    toDatabase.insert(Account.TABLE_NAME, null, account.toContentValues());
2073                    copyCount++;
2074                }
2075            } catch (SQLiteException e) {
2076                noErrors = false;
2077                copyCount = -1;
2078            } finally {
2079                fromDatabase.endTransaction();
2080                if (noErrors) {
2081                    // Say it's ok to commit
2082                    toDatabase.setTransactionSuccessful();
2083                }
2084                toDatabase.endTransaction();
2085                c.close();
2086            }
2087        } catch (SQLiteException e) {
2088            copyCount = -1;
2089        }
2090        return copyCount;
2091    }
2092
2093    private static SQLiteDatabase getBackupDatabase(Context context) {
2094        DatabaseHelper helper = new DatabaseHelper(context, BACKUP_DATABASE_NAME);
2095        return helper.getWritableDatabase();
2096    }
2097
2098    /**
2099     * Backup account data, returning the number of accounts backed up
2100     */
2101    private static int backupAccounts(Context context, SQLiteDatabase mainDatabase) {
2102        if (Email.DEBUG) {
2103            Log.d(TAG, "backupAccounts...");
2104        }
2105        SQLiteDatabase backupDatabase = getBackupDatabase(context);
2106        try {
2107            int numBackedUp = copyAccountTables(mainDatabase, backupDatabase);
2108            if (numBackedUp < 0) {
2109                Log.e(TAG, "Account backup failed!");
2110            } else if (Email.DEBUG) {
2111                Log.d(TAG, "Backed up " + numBackedUp + " accounts...");
2112            }
2113            return numBackedUp;
2114        } finally {
2115            if (backupDatabase != null) {
2116                backupDatabase.close();
2117            }
2118        }
2119    }
2120
2121    /**
2122     * Restore account data, returning the number of accounts restored
2123     */
2124    private static int restoreAccounts(Context context, SQLiteDatabase mainDatabase) {
2125        if (Email.DEBUG) {
2126            Log.d(TAG, "restoreAccounts...");
2127        }
2128        SQLiteDatabase backupDatabase = getBackupDatabase(context);
2129        try {
2130            int numRecovered = copyAccountTables(backupDatabase, mainDatabase);
2131            if (numRecovered > 0) {
2132                Log.e(TAG, "Recovered " + numRecovered + " accounts!");
2133            } else if (numRecovered < 0) {
2134                Log.e(TAG, "Account recovery failed?");
2135            } else if (Email.DEBUG) {
2136                Log.d(TAG, "No accounts to restore...");
2137            }
2138            return numRecovered;
2139        } finally {
2140            if (backupDatabase != null) {
2141                backupDatabase.close();
2142            }
2143        }
2144    }
2145
2146    @Override
2147    public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) {
2148        // Handle this special case the fastest possible way
2149        if (uri == INTEGRITY_CHECK_URI) {
2150            checkDatabases();
2151            return 0;
2152        } else if (uri == ACCOUNT_BACKUP_URI) {
2153            return backupAccounts(getContext(), getDatabase(getContext()));
2154        }
2155
2156        // Notify all existing cursors, except for ACCOUNT_RESET_NEW_COUNT(_ID)
2157        Uri notificationUri = EmailContent.CONTENT_URI;
2158
2159        int match = findMatch(uri, "update");
2160        Context context = getContext();
2161        ContentResolver resolver = context.getContentResolver();
2162        // See the comment at delete(), above
2163        SQLiteDatabase db = getDatabase(context);
2164        int table = match >> BASE_SHIFT;
2165        int result;
2166
2167        // We do NOT allow setting of unreadCount/messageCount via the provider
2168        // These columns are maintained via triggers
2169        if (match == MAILBOX_ID || match == MAILBOX) {
2170            values.remove(MailboxColumns.UNREAD_COUNT);
2171            values.remove(MailboxColumns.MESSAGE_COUNT);
2172        }
2173
2174        ContentCache cache = mContentCaches[table];
2175        String tableName = TABLE_NAMES[table];
2176        String id = "0";
2177
2178        try {
2179outer:
2180            switch (match) {
2181                case UI_SENDMAIL:
2182                    return uiSendmail(uri, values);
2183                case MAILBOX_ID_ADD_TO_FIELD:
2184                case ACCOUNT_ID_ADD_TO_FIELD:
2185                    id = uri.getPathSegments().get(1);
2186                    String field = values.getAsString(EmailContent.FIELD_COLUMN_NAME);
2187                    Long add = values.getAsLong(EmailContent.ADD_COLUMN_NAME);
2188                    if (field == null || add == null) {
2189                        throw new IllegalArgumentException("No field/add specified " + uri);
2190                    }
2191                    ContentValues actualValues = new ContentValues();
2192                    if (cache != null) {
2193                        cache.lock(id);
2194                    }
2195                    try {
2196                        db.beginTransaction();
2197                        try {
2198                            Cursor c = db.query(tableName,
2199                                    new String[] {EmailContent.RECORD_ID, field},
2200                                    whereWithId(id, selection),
2201                                    selectionArgs, null, null, null);
2202                            try {
2203                                result = 0;
2204                                String[] bind = new String[1];
2205                                if (c.moveToNext()) {
2206                                    bind[0] = c.getString(0); // _id
2207                                    long value = c.getLong(1) + add;
2208                                    actualValues.put(field, value);
2209                                    result = db.update(tableName, actualValues, ID_EQUALS, bind);
2210                                }
2211                                db.setTransactionSuccessful();
2212                            } finally {
2213                                c.close();
2214                            }
2215                        } finally {
2216                            db.endTransaction();
2217                        }
2218                    } finally {
2219                        if (cache != null) {
2220                            cache.unlock(id, actualValues);
2221                        }
2222                    }
2223                    break;
2224                case SYNCED_MESSAGE_ID:
2225                case UPDATED_MESSAGE_ID:
2226                case MESSAGE_ID:
2227                case BODY_ID:
2228                case ATTACHMENT_ID:
2229                case MAILBOX_ID:
2230                case ACCOUNT_ID:
2231                case HOSTAUTH_ID:
2232                case QUICK_RESPONSE_ID:
2233                case POLICY_ID:
2234                    id = uri.getPathSegments().get(1);
2235                    if (cache != null) {
2236                        cache.lock(id);
2237                    }
2238                    try {
2239                        if (match == SYNCED_MESSAGE_ID) {
2240                            // For synced messages, first copy the old message to the updated table
2241                            // Note the insert or ignore semantics, guaranteeing that only the first
2242                            // update will be reflected in the updated message table; therefore this
2243                            // row will always have the "original" data
2244                            db.execSQL(UPDATED_MESSAGE_INSERT + id);
2245                        } else if (match == MESSAGE_ID) {
2246                            db.execSQL(UPDATED_MESSAGE_DELETE + id);
2247                        }
2248                        result = db.update(tableName, values, whereWithId(id, selection),
2249                                selectionArgs);
2250                    } catch (SQLiteException e) {
2251                        // Null out values (so they aren't cached) and re-throw
2252                        values = null;
2253                        throw e;
2254                    } finally {
2255                        if (cache != null) {
2256                            cache.unlock(id, values);
2257                        }
2258                    }
2259                    if (match == ATTACHMENT_ID) {
2260                        if (values.containsKey(Attachment.FLAGS)) {
2261                            int flags = values.getAsInteger(Attachment.FLAGS);
2262                            mAttachmentService.attachmentChanged(getContext(),
2263                                    Integer.parseInt(id), flags);
2264                        }
2265                    }
2266                    break;
2267                case BODY:
2268                case MESSAGE:
2269                case UPDATED_MESSAGE:
2270                case ATTACHMENT:
2271                case MAILBOX:
2272                case ACCOUNT:
2273                case HOSTAUTH:
2274                case POLICY:
2275                    switch(match) {
2276                        // To avoid invalidating the cache on updates, we execute them one at a
2277                        // time using the XXX_ID uri; these are all executed atomically
2278                        case ACCOUNT:
2279                        case MAILBOX:
2280                        case HOSTAUTH:
2281                        case POLICY:
2282                            Cursor c = db.query(tableName, EmailContent.ID_PROJECTION,
2283                                    selection, selectionArgs, null, null, null);
2284                            db.beginTransaction();
2285                            result = 0;
2286                            try {
2287                                while (c.moveToNext()) {
2288                                    update(ContentUris.withAppendedId(
2289                                                uri, c.getLong(EmailContent.ID_PROJECTION_COLUMN)),
2290                                            values, null, null);
2291                                    result++;
2292                                }
2293                                db.setTransactionSuccessful();
2294                            } finally {
2295                                db.endTransaction();
2296                                c.close();
2297                            }
2298                            break outer;
2299                        // Any cached table other than those above should be invalidated here
2300                        case MESSAGE:
2301                            // If we're doing some generic update, the whole cache needs to be
2302                            // invalidated.  This case should be quite rare
2303                            cache.invalidate("Update", uri, selection);
2304                            //$FALL-THROUGH$
2305                        default:
2306                            result = db.update(tableName, values, selection, selectionArgs);
2307                            break outer;
2308                    }
2309                case ACCOUNT_RESET_NEW_COUNT_ID:
2310                    id = uri.getPathSegments().get(1);
2311                    if (cache != null) {
2312                        cache.lock(id);
2313                    }
2314                    ContentValues newMessageCount = CONTENT_VALUES_RESET_NEW_MESSAGE_COUNT;
2315                    if (values != null) {
2316                        Long set = values.getAsLong(EmailContent.SET_COLUMN_NAME);
2317                        if (set != null) {
2318                            newMessageCount = new ContentValues();
2319                            newMessageCount.put(Account.NEW_MESSAGE_COUNT, set);
2320                        }
2321                    }
2322                    try {
2323                        result = db.update(tableName, newMessageCount,
2324                                whereWithId(id, selection), selectionArgs);
2325                    } finally {
2326                        if (cache != null) {
2327                            cache.unlock(id, values);
2328                        }
2329                    }
2330                    notificationUri = Account.CONTENT_URI; // Only notify account cursors.
2331                    break;
2332                case ACCOUNT_RESET_NEW_COUNT:
2333                    result = db.update(tableName, CONTENT_VALUES_RESET_NEW_MESSAGE_COUNT,
2334                            selection, selectionArgs);
2335                    // Affects all accounts.  Just invalidate all account cache.
2336                    cache.invalidate("Reset all new counts", null, null);
2337                    notificationUri = Account.CONTENT_URI; // Only notify account cursors.
2338                    break;
2339                default:
2340                    throw new IllegalArgumentException("Unknown URI " + uri);
2341            }
2342        } catch (SQLiteException e) {
2343            checkDatabases();
2344            throw e;
2345        }
2346
2347        // Notify all notifier cursors
2348        sendNotifierChange(getBaseNotificationUri(match), NOTIFICATION_OP_UPDATE, id);
2349
2350        resolver.notifyChange(notificationUri, null);
2351        return result;
2352    }
2353
2354    /**
2355     * Returns the base notification URI for the given content type.
2356     *
2357     * @param match The type of content that was modified.
2358     */
2359    private Uri getBaseNotificationUri(int match) {
2360        Uri baseUri = null;
2361        switch (match) {
2362            case MESSAGE:
2363            case MESSAGE_ID:
2364            case SYNCED_MESSAGE_ID:
2365                baseUri = Message.NOTIFIER_URI;
2366                break;
2367            case ACCOUNT:
2368            case ACCOUNT_ID:
2369                baseUri = Account.NOTIFIER_URI;
2370                break;
2371        }
2372        return baseUri;
2373    }
2374
2375    /**
2376     * Sends a change notification to any cursors observers of the given base URI. The final
2377     * notification URI is dynamically built to contain the specified information. It will be
2378     * of the format <<baseURI>>/<<op>>/<<id>>; where <<op>> and <<id>> are optional depending
2379     * upon the given values.
2380     * NOTE: If <<op>> is specified, notifications for <<baseURI>>/<<id>> will NOT be invoked.
2381     * If this is necessary, it can be added. However, due to the implementation of
2382     * {@link ContentObserver}, observers of <<baseURI>> will receive multiple notifications.
2383     *
2384     * @param baseUri The base URI to send notifications to. Must be able to take appended IDs.
2385     * @param op Optional operation to be appended to the URI.
2386     * @param id If a positive value, the ID to append to the base URI. Otherwise, no ID will be
2387     *           appended to the base URI.
2388     */
2389    private void sendNotifierChange(Uri baseUri, String op, String id) {
2390        if (baseUri == null) return;
2391
2392        final ContentResolver resolver = getContext().getContentResolver();
2393
2394        // Append the operation, if specified
2395        if (op != null) {
2396            baseUri = baseUri.buildUpon().appendEncodedPath(op).build();
2397        }
2398
2399        long longId = 0L;
2400        try {
2401            longId = Long.valueOf(id);
2402        } catch (NumberFormatException ignore) {}
2403        if (longId > 0) {
2404            resolver.notifyChange(ContentUris.withAppendedId(baseUri, longId), null);
2405        } else {
2406            resolver.notifyChange(baseUri, null);
2407        }
2408
2409        // We want to send the message list changed notification if baseUri is Message.NOTIFIER_URI.
2410        if (baseUri.equals(Message.NOTIFIER_URI)) {
2411            sendMessageListDataChangedNotification();
2412        }
2413    }
2414
2415    private void sendMessageListDataChangedNotification() {
2416        final Context context = getContext();
2417        final Intent intent = new Intent(ACTION_NOTIFY_MESSAGE_LIST_DATASET_CHANGED);
2418        // Ideally this intent would contain information about which account changed, to limit the
2419        // updates to that particular account.  Unfortunately, that information is not available in
2420        // sendNotifierChange().
2421        context.sendBroadcast(intent);
2422    }
2423
2424    @Override
2425    public ContentProviderResult[] applyBatch(ArrayList<ContentProviderOperation> operations)
2426            throws OperationApplicationException {
2427        Context context = getContext();
2428        SQLiteDatabase db = getDatabase(context);
2429        db.beginTransaction();
2430        try {
2431            ContentProviderResult[] results = super.applyBatch(operations);
2432            db.setTransactionSuccessful();
2433            return results;
2434        } finally {
2435            db.endTransaction();
2436        }
2437    }
2438
2439    /** Counts the number of messages in each mailbox, and updates the message count column. */
2440    @VisibleForTesting
2441    static void recalculateMessageCount(SQLiteDatabase db) {
2442        db.execSQL("update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT +
2443                "= (select count(*) from " + Message.TABLE_NAME +
2444                " where " + Message.MAILBOX_KEY + " = " +
2445                    Mailbox.TABLE_NAME + "." + EmailContent.RECORD_ID + ")");
2446    }
2447
2448    @VisibleForTesting
2449    @SuppressWarnings("deprecation")
2450    static void convertPolicyFlagsToPolicyTable(SQLiteDatabase db) {
2451        Cursor c = db.query(Account.TABLE_NAME,
2452                new String[] {EmailContent.RECORD_ID /*0*/, AccountColumns.SECURITY_FLAGS /*1*/},
2453                AccountColumns.SECURITY_FLAGS + ">0", null, null, null, null);
2454        ContentValues cv = new ContentValues();
2455        String[] args = new String[1];
2456        while (c.moveToNext()) {
2457            long securityFlags = c.getLong(1 /*SECURITY_FLAGS*/);
2458            Policy policy = LegacyPolicySet.flagsToPolicy(securityFlags);
2459            long policyId = db.insert(Policy.TABLE_NAME, null, policy.toContentValues());
2460            cv.put(AccountColumns.POLICY_KEY, policyId);
2461            cv.putNull(AccountColumns.SECURITY_FLAGS);
2462            args[0] = Long.toString(c.getLong(0 /*RECORD_ID*/));
2463            db.update(Account.TABLE_NAME, cv, EmailContent.RECORD_ID + "=?", args);
2464        }
2465    }
2466
2467    /** Upgrades the database from v17 to v18 */
2468    @VisibleForTesting
2469    static void upgradeFromVersion17ToVersion18(SQLiteDatabase db) {
2470        // Copy the displayName column to the serverId column. In v18 of the database,
2471        // we use the serverId for IMAP/POP3 mailboxes instead of overloading the
2472        // display name.
2473        //
2474        // For posterity; this is the command we're executing:
2475        //sqlite> UPDATE mailbox SET serverid=displayname WHERE mailbox._id in (
2476        //        ...> SELECT mailbox._id FROM mailbox,account,hostauth WHERE
2477        //        ...> (mailbox.parentkey isnull OR mailbox.parentkey=0) AND
2478        //        ...> mailbox.accountkey=account._id AND
2479        //        ...> account.hostauthkeyrecv=hostauth._id AND
2480        //        ...> (hostauth.protocol='imap' OR hostauth.protocol='pop3'));
2481        try {
2482            db.execSQL(
2483                    "UPDATE " + Mailbox.TABLE_NAME + " SET "
2484                    + MailboxColumns.SERVER_ID + "=" + MailboxColumns.DISPLAY_NAME
2485                    + " WHERE "
2486                    + Mailbox.TABLE_NAME + "." + MailboxColumns.ID + " IN ( SELECT "
2487                    + Mailbox.TABLE_NAME + "." + MailboxColumns.ID + " FROM "
2488                    + Mailbox.TABLE_NAME + "," + Account.TABLE_NAME + ","
2489                    + HostAuth.TABLE_NAME + " WHERE "
2490                    + "("
2491                    + Mailbox.TABLE_NAME + "." + MailboxColumns.PARENT_KEY + " isnull OR "
2492                    + Mailbox.TABLE_NAME + "." + MailboxColumns.PARENT_KEY + "=0 "
2493                    + ") AND "
2494                    + Mailbox.TABLE_NAME + "." + MailboxColumns.ACCOUNT_KEY + "="
2495                    + Account.TABLE_NAME + "." + AccountColumns.ID + " AND "
2496                    + Account.TABLE_NAME + "." + AccountColumns.HOST_AUTH_KEY_RECV + "="
2497                    + HostAuth.TABLE_NAME + "." + HostAuthColumns.ID + " AND ( "
2498                    + HostAuth.TABLE_NAME + "." + HostAuthColumns.PROTOCOL + "='imap' OR "
2499                    + HostAuth.TABLE_NAME + "." + HostAuthColumns.PROTOCOL + "='pop3' ) )");
2500        } catch (SQLException e) {
2501            // Shouldn't be needed unless we're debugging and interrupt the process
2502            Log.w(TAG, "Exception upgrading EmailProvider.db from 17 to 18 " + e);
2503        }
2504        ContentCache.invalidateAllCaches();
2505    }
2506
2507    /** Upgrades the database from v20 to v21 */
2508    private static void upgradeFromVersion20ToVersion21(SQLiteDatabase db) {
2509        try {
2510            db.execSQL("alter table " + Mailbox.TABLE_NAME
2511                    + " add column " + Mailbox.LAST_SEEN_MESSAGE_KEY + " integer;");
2512        } catch (SQLException e) {
2513            // Shouldn't be needed unless we're debugging and interrupt the process
2514            Log.w(TAG, "Exception upgrading EmailProvider.db from 20 to 21 " + e);
2515        }
2516    }
2517
2518    /**
2519     * Upgrade the database from v21 to v22
2520     * This entails creating AccountManager accounts for all pop3 and imap accounts
2521     */
2522
2523    private static final String[] V21_ACCOUNT_PROJECTION =
2524        new String[] {AccountColumns.HOST_AUTH_KEY_RECV, AccountColumns.EMAIL_ADDRESS};
2525    private static final int V21_ACCOUNT_RECV = 0;
2526    private static final int V21_ACCOUNT_EMAIL = 1;
2527
2528    private static final String[] V21_HOSTAUTH_PROJECTION =
2529        new String[] {HostAuthColumns.PROTOCOL, HostAuthColumns.PASSWORD};
2530    private static final int V21_HOSTAUTH_PROTOCOL = 0;
2531    private static final int V21_HOSTAUTH_PASSWORD = 1;
2532
2533    static private void createAccountManagerAccount(Context context, String login,
2534            String password) {
2535        AccountManager accountManager = AccountManager.get(context);
2536        android.accounts.Account amAccount =
2537            new android.accounts.Account(login, AccountManagerTypes.TYPE_POP_IMAP);
2538        accountManager.addAccountExplicitly(amAccount, password, null);
2539        ContentResolver.setIsSyncable(amAccount, EmailContent.AUTHORITY, 1);
2540        ContentResolver.setSyncAutomatically(amAccount, EmailContent.AUTHORITY, true);
2541        ContentResolver.setIsSyncable(amAccount, ContactsContract.AUTHORITY, 0);
2542        ContentResolver.setIsSyncable(amAccount, CalendarProviderStub.AUTHORITY, 0);
2543    }
2544
2545    @VisibleForTesting
2546    static void upgradeFromVersion21ToVersion22(SQLiteDatabase db, Context accountManagerContext) {
2547        try {
2548            // Loop through accounts, looking for pop/imap accounts
2549            Cursor accountCursor = db.query(Account.TABLE_NAME, V21_ACCOUNT_PROJECTION, null,
2550                    null, null, null, null);
2551            try {
2552                String[] hostAuthArgs = new String[1];
2553                while (accountCursor.moveToNext()) {
2554                    hostAuthArgs[0] = accountCursor.getString(V21_ACCOUNT_RECV);
2555                    // Get the "receive" HostAuth for this account
2556                    Cursor hostAuthCursor = db.query(HostAuth.TABLE_NAME,
2557                            V21_HOSTAUTH_PROJECTION, HostAuth.RECORD_ID + "=?", hostAuthArgs,
2558                            null, null, null);
2559                    try {
2560                        if (hostAuthCursor.moveToFirst()) {
2561                            String protocol = hostAuthCursor.getString(V21_HOSTAUTH_PROTOCOL);
2562                            // If this is a pop3 or imap account, create the account manager account
2563                            if (HostAuth.SCHEME_IMAP.equals(protocol) ||
2564                                    HostAuth.SCHEME_POP3.equals(protocol)) {
2565                                if (Email.DEBUG) {
2566                                    Log.d(TAG, "Create AccountManager account for " + protocol +
2567                                            "account: " +
2568                                            accountCursor.getString(V21_ACCOUNT_EMAIL));
2569                                }
2570                                createAccountManagerAccount(accountManagerContext,
2571                                        accountCursor.getString(V21_ACCOUNT_EMAIL),
2572                                        hostAuthCursor.getString(V21_HOSTAUTH_PASSWORD));
2573                            // If an EAS account, make Email sync automatically (equivalent of
2574                            // checking the "Sync Email" box in settings
2575                            } else if (HostAuth.SCHEME_EAS.equals(protocol)) {
2576                                android.accounts.Account amAccount =
2577                                        new android.accounts.Account(
2578                                                accountCursor.getString(V21_ACCOUNT_EMAIL),
2579                                                AccountManagerTypes.TYPE_EXCHANGE);
2580                                ContentResolver.setIsSyncable(amAccount, EmailContent.AUTHORITY, 1);
2581                                ContentResolver.setSyncAutomatically(amAccount,
2582                                        EmailContent.AUTHORITY, true);
2583
2584                            }
2585                        }
2586                    } finally {
2587                        hostAuthCursor.close();
2588                    }
2589                }
2590            } finally {
2591                accountCursor.close();
2592            }
2593        } catch (SQLException e) {
2594            // Shouldn't be needed unless we're debugging and interrupt the process
2595            Log.w(TAG, "Exception upgrading EmailProvider.db from 20 to 21 " + e);
2596        }
2597    }
2598
2599    /** Upgrades the database from v22 to v23 */
2600    private static void upgradeFromVersion22ToVersion23(SQLiteDatabase db) {
2601        try {
2602            db.execSQL("alter table " + Mailbox.TABLE_NAME
2603                    + " add column " + Mailbox.LAST_TOUCHED_TIME + " integer default 0;");
2604        } catch (SQLException e) {
2605            // Shouldn't be needed unless we're debugging and interrupt the process
2606            Log.w(TAG, "Exception upgrading EmailProvider.db from 22 to 23 " + e);
2607        }
2608    }
2609
2610    /** Adds in a column for information about a client certificate to use. */
2611    private static void upgradeFromVersion23ToVersion24(SQLiteDatabase db) {
2612        try {
2613            db.execSQL("alter table " + HostAuth.TABLE_NAME
2614                    + " add column " + HostAuth.CLIENT_CERT_ALIAS + " text;");
2615        } catch (SQLException e) {
2616            // Shouldn't be needed unless we're debugging and interrupt the process
2617            Log.w(TAG, "Exception upgrading EmailProvider.db from 23 to 24 " + e);
2618        }
2619    }
2620
2621    /** Upgrades the database from v24 to v25 by creating table for quick responses */
2622    private static void upgradeFromVersion24ToVersion25(SQLiteDatabase db) {
2623        try {
2624            createQuickResponseTable(db);
2625        } catch (SQLException e) {
2626            // Shouldn't be needed unless we're debugging and interrupt the process
2627            Log.w(TAG, "Exception upgrading EmailProvider.db from 24 to 25 " + e);
2628        }
2629    }
2630
2631    private static final String[] V25_ACCOUNT_PROJECTION =
2632        new String[] {AccountColumns.ID, AccountColumns.FLAGS, AccountColumns.HOST_AUTH_KEY_RECV};
2633    private static final int V25_ACCOUNT_ID = 0;
2634    private static final int V25_ACCOUNT_FLAGS = 1;
2635    private static final int V25_ACCOUNT_RECV = 2;
2636
2637    private static final String[] V25_HOSTAUTH_PROJECTION = new String[] {HostAuthColumns.PROTOCOL};
2638    private static final int V25_HOSTAUTH_PROTOCOL = 0;
2639
2640    /** Upgrades the database from v25 to v26 by adding FLAG_SUPPORTS_SEARCH to IMAP accounts */
2641    private static void upgradeFromVersion25ToVersion26(SQLiteDatabase db) {
2642        try {
2643            // Loop through accounts, looking for imap accounts
2644            Cursor accountCursor = db.query(Account.TABLE_NAME, V25_ACCOUNT_PROJECTION, null,
2645                    null, null, null, null);
2646            ContentValues cv = new ContentValues();
2647            try {
2648                String[] hostAuthArgs = new String[1];
2649                while (accountCursor.moveToNext()) {
2650                    hostAuthArgs[0] = accountCursor.getString(V25_ACCOUNT_RECV);
2651                    // Get the "receive" HostAuth for this account
2652                    Cursor hostAuthCursor = db.query(HostAuth.TABLE_NAME,
2653                            V25_HOSTAUTH_PROJECTION, HostAuth.RECORD_ID + "=?", hostAuthArgs,
2654                            null, null, null);
2655                    try {
2656                        if (hostAuthCursor.moveToFirst()) {
2657                            String protocol = hostAuthCursor.getString(V25_HOSTAUTH_PROTOCOL);
2658                            // If this is an imap account, add the search flag
2659                            if (HostAuth.SCHEME_IMAP.equals(protocol)) {
2660                                String id = accountCursor.getString(V25_ACCOUNT_ID);
2661                                int flags = accountCursor.getInt(V25_ACCOUNT_FLAGS);
2662                                cv.put(AccountColumns.FLAGS, flags | Account.FLAGS_SUPPORTS_SEARCH);
2663                                db.update(Account.TABLE_NAME, cv, Account.RECORD_ID + "=?",
2664                                        new String[] {id});
2665                            }
2666                        }
2667                    } finally {
2668                        hostAuthCursor.close();
2669                    }
2670                }
2671            } finally {
2672                accountCursor.close();
2673            }
2674        } catch (SQLException e) {
2675            // Shouldn't be needed unless we're debugging and interrupt the process
2676            Log.w(TAG, "Exception upgrading EmailProvider.db from 25 to 26 " + e);
2677        }
2678    }
2679
2680    /**
2681     * For testing purposes, check whether a given row is cached
2682     * @param baseUri the base uri of the EmailContent
2683     * @param id the row id of the EmailContent
2684     * @return whether or not the row is currently cached
2685     */
2686    @VisibleForTesting
2687    protected boolean isCached(Uri baseUri, long id) {
2688        int match = findMatch(baseUri, "isCached");
2689        int table = match >> BASE_SHIFT;
2690        ContentCache cache = mContentCaches[table];
2691        if (cache == null) return false;
2692        Cursor cc = cache.get(Long.toString(id));
2693        return (cc != null);
2694    }
2695
2696    public static interface AttachmentService {
2697        /**
2698         * Notify the service that an attachment has changed.
2699         */
2700        void attachmentChanged(Context context, long id, int flags);
2701    }
2702
2703    private final AttachmentService DEFAULT_ATTACHMENT_SERVICE = new AttachmentService() {
2704        @Override
2705        public void attachmentChanged(Context context, long id, int flags) {
2706            // The default implementation delegates to the real service.
2707            AttachmentDownloadService.attachmentChanged(context, id, flags);
2708        }
2709    };
2710    private AttachmentService mAttachmentService = DEFAULT_ATTACHMENT_SERVICE;
2711
2712    /**
2713     * Injects a custom attachment service handler. If null is specified, will reset to the
2714     * default service.
2715     */
2716    public void injectAttachmentService(AttachmentService as) {
2717        mAttachmentService = (as == null) ? DEFAULT_ATTACHMENT_SERVICE : as;
2718    }
2719
2720    /**
2721     * Support for UnifiedEmail below
2722     */
2723
2724    private static final String NOT_A_DRAFT_STRING =
2725        Integer.toString(UIProvider.DraftType.NOT_A_DRAFT);
2726
2727    /**
2728     * Mapping of UIProvider columns to EmailProvider columns for the message list (called the
2729     * conversation list in UnifiedEmail)
2730     */
2731    private static final ProjectionMap sMessageListMap = ProjectionMap.builder()
2732        .add(BaseColumns._ID, MessageColumns.ID)
2733        .add(UIProvider.ConversationColumns.URI, uriWithId("uimessage"))
2734        .add(UIProvider.ConversationColumns.MESSAGE_LIST_URI, uriWithId("uimessage"))
2735        .add(UIProvider.ConversationColumns.SUBJECT, MessageColumns.SUBJECT)
2736        .add(UIProvider.ConversationColumns.SNIPPET, MessageColumns.SNIPPET)
2737        .add(UIProvider.ConversationColumns.SENDER_INFO, MessageColumns.FROM_LIST)
2738        .add(UIProvider.ConversationColumns.DATE_RECEIVED_MS, MessageColumns.TIMESTAMP)
2739        .add(UIProvider.ConversationColumns.HAS_ATTACHMENTS, MessageColumns.FLAG_ATTACHMENT)
2740        .add(UIProvider.ConversationColumns.NUM_MESSAGES, "1")
2741        .add(UIProvider.ConversationColumns.NUM_DRAFTS, "0")
2742        .add(UIProvider.ConversationColumns.SENDING_STATE,
2743                Integer.toString(ConversationSendingState.OTHER))
2744        .add(UIProvider.ConversationColumns.PRIORITY, Integer.toString(ConversationPriority.LOW))
2745        .build();
2746
2747    /**
2748     * Mapping of UIProvider columns to EmailProvider columns for a detailed message view in
2749     * UnifiedEmail
2750     */
2751    private static final ProjectionMap sMessageViewMap = ProjectionMap.builder()
2752        .add(BaseColumns._ID, Message.TABLE_NAME + "." + EmailContent.MessageColumns.ID)
2753        .add(UIProvider.MessageColumns.SERVER_ID, SyncColumns.SERVER_ID)
2754        .add(UIProvider.MessageColumns.URI, uriWithFQId("uimessage", Message.TABLE_NAME))
2755        .add(UIProvider.MessageColumns.CONVERSATION_ID,
2756                uriWithFQId("uimessage", Message.TABLE_NAME))
2757        .add(UIProvider.MessageColumns.SUBJECT, EmailContent.MessageColumns.SUBJECT)
2758        .add(UIProvider.MessageColumns.SNIPPET, EmailContent.MessageColumns.SNIPPET)
2759        .add(UIProvider.MessageColumns.FROM, EmailContent.MessageColumns.FROM_LIST)
2760        .add(UIProvider.MessageColumns.TO, EmailContent.MessageColumns.TO_LIST)
2761        .add(UIProvider.MessageColumns.CC, EmailContent.MessageColumns.CC_LIST)
2762        .add(UIProvider.MessageColumns.BCC, EmailContent.MessageColumns.BCC_LIST)
2763        .add(UIProvider.MessageColumns.REPLY_TO, EmailContent.MessageColumns.REPLY_TO_LIST)
2764        .add(UIProvider.MessageColumns.DATE_RECEIVED_MS, EmailContent.MessageColumns.TIMESTAMP)
2765        .add(UIProvider.MessageColumns.BODY_HTML, Body.HTML_CONTENT)
2766        .add(UIProvider.MessageColumns.BODY_TEXT, Body.TEXT_CONTENT)
2767        .add(UIProvider.MessageColumns.EMBEDS_EXTERNAL_RESOURCES, "0")
2768        .add(UIProvider.MessageColumns.REF_MESSAGE_ID, "0")
2769        .add(UIProvider.MessageColumns.DRAFT_TYPE, NOT_A_DRAFT_STRING)
2770        .add(UIProvider.MessageColumns.INCLUDE_QUOTED_TEXT, "0")
2771        .add(UIProvider.MessageColumns.QUOTE_START_POS, "0")
2772        .add(UIProvider.MessageColumns.CUSTOM_FROM_ADDRESS, "0")
2773        .add(UIProvider.MessageColumns.HAS_ATTACHMENTS, EmailContent.MessageColumns.FLAG_ATTACHMENT)
2774        .add(UIProvider.MessageColumns.INCLUDE_QUOTED_TEXT, "0")
2775        .add(UIProvider.MessageColumns.ATTACHMENT_LIST_URI,
2776                uriWithFQId("uiattachments", Message.TABLE_NAME))
2777        .add(UIProvider.MessageColumns.MESSAGE_FLAGS, "0")
2778        .build();
2779
2780    /**
2781     * Mapping of UIProvider columns to EmailProvider columns for the folder list in UnifiedEmail
2782     */
2783    private static final ProjectionMap sFolderListMap = ProjectionMap.builder()
2784        .add(BaseColumns._ID, MessageColumns.ID)
2785        .add(UIProvider.FolderColumns.URI, uriWithId("uifolder"))
2786        .add(UIProvider.FolderColumns.NAME, "displayName")
2787        .add(UIProvider.FolderColumns.HAS_CHILDREN,
2788                MailboxColumns.FLAGS + "&" + Mailbox.FLAG_HAS_CHILDREN)
2789        .add(UIProvider.FolderColumns.CAPABILITIES, "0")
2790        .add(UIProvider.FolderColumns.SYNC_FREQUENCY, "0")
2791        .add(UIProvider.FolderColumns.SYNC_WINDOW, "3")
2792        .add(UIProvider.FolderColumns.CONVERSATION_LIST_URI, uriWithId("uimessages"))
2793        .add(UIProvider.FolderColumns.CHILD_FOLDERS_LIST_URI, uriWithId("uichildren"))
2794        .add(UIProvider.FolderColumns.UNREAD_COUNT, MailboxColumns.UNREAD_COUNT)
2795        .add(UIProvider.FolderColumns.TOTAL_COUNT, MailboxColumns.MESSAGE_COUNT)
2796        .build();
2797
2798    /**
2799     * The "ORDER BY" clause for top level folders
2800     */
2801    private static final String MAILBOX_ORDER_BY = "CASE " + MailboxColumns.TYPE
2802        + " WHEN " + Mailbox.TYPE_INBOX   + " THEN 0"
2803        + " WHEN " + Mailbox.TYPE_DRAFTS  + " THEN 1"
2804        + " WHEN " + Mailbox.TYPE_OUTBOX  + " THEN 2"
2805        + " WHEN " + Mailbox.TYPE_SENT    + " THEN 3"
2806        + " WHEN " + Mailbox.TYPE_TRASH   + " THEN 4"
2807        + " WHEN " + Mailbox.TYPE_JUNK    + " THEN 5"
2808        // Other mailboxes (i.e. of Mailbox.TYPE_MAIL) are shown in alphabetical order.
2809        + " ELSE 10 END"
2810        + " ," + MailboxColumns.DISPLAY_NAME + " COLLATE LOCALIZED ASC";
2811
2812    /**
2813     * Generate the SELECT clause using a specified mapping and the original UI projection
2814     * @param map the ProjectionMap to use for this projection
2815     * @param projection the projection as sent by UnifiedEmail
2816     * @return a StringBuilder containing the SELECT expression for a SQLite query
2817     */
2818    private StringBuilder genSelect(ProjectionMap map, String[] projection) {
2819        StringBuilder sb = new StringBuilder("SELECT ");
2820        boolean first = true;
2821        for (String column: projection) {
2822            if (first) {
2823                first = false;
2824            } else {
2825                sb.append(',');
2826            }
2827            String val = map.get(column);
2828            // If we don't have the column, be permissive, returning "0 AS <column>", and warn
2829            if (val == null) {
2830                Log.w(TAG, "UIProvider column not found, returning 0: " + column);
2831                val = "0 AS " + column;
2832            }
2833            sb.append(val);
2834        }
2835        return sb;
2836    }
2837
2838    /**
2839     * Convenience method to create a Uri string given the "type" of query; we append the type
2840     * of the query and the id column name (_id)
2841     *
2842     * @param type the "type" of the query, as defined by our UriMatcher definitions
2843     * @return a Uri string
2844     */
2845    private static String uriWithId(String type) {
2846        return "'content://" + EmailContent.AUTHORITY + "/" + type + "/' || _id";
2847    }
2848
2849    /**
2850     * Convenience method to create a Uri string given the "type" of query and the table name to
2851     * which it applies; we append the type of the query and the fully qualified (FQ) id column
2852     * (i.e. including the table name); we need this for join queries where _id would otherwise
2853     * be ambiguous
2854     *
2855     * @param type the "type" of the query, as defined by our UriMatcher definitions
2856     * @param tableName the name of the table whose _id is referred to
2857     * @return a Uri string
2858     */
2859    private static String uriWithFQId(String type, String tableName) {
2860        return "'content://" + EmailContent.AUTHORITY + "/" + type + "/' || " + tableName + "._id";
2861    }
2862
2863    /**
2864     * Generate the "view message" SQLite query, given a projection from UnifiedEmail
2865     *
2866     * @param uiProjection as passed from UnifiedEmail
2867     * @return the SQLite query to be executed on the EmailProvider database
2868     */
2869    private String genQueryViewMessage(String[] uiProjection) {
2870        StringBuilder sb = genSelect(sMessageViewMap, uiProjection);
2871        sb.append(" FROM " + Message.TABLE_NAME + "," + Body.TABLE_NAME + " WHERE " +
2872                Body.MESSAGE_KEY + "=" + Message.TABLE_NAME + "." + Message.RECORD_ID + " AND " +
2873                Message.TABLE_NAME + "." + Message.RECORD_ID + "=?");
2874        return sb.toString();
2875    }
2876
2877    /**
2878     * Generate the "message list" SQLite query, given a projection from UnifiedEmail
2879     *
2880     * @param uiProjection as passed from UnifiedEmail
2881     * @return the SQLite query to be executed on the EmailProvider database
2882     */
2883    private String genQueryMailboxMessages(String[] uiProjection) {
2884        StringBuilder sb = genSelect(sMessageListMap, uiProjection);
2885        // Make constant
2886        sb.append(" FROM " + Message.TABLE_NAME + " WHERE " + Message.MAILBOX_KEY + "=? ORDER BY " +
2887                MessageColumns.TIMESTAMP + " DESC");
2888        return sb.toString();
2889    }
2890
2891    /**
2892     * Generate the "folder list" SQLite query, given a projection from UnifiedEmail
2893     *
2894     * @param uiProjection as passed from UnifiedEmail
2895     * @return the SQLite query to be executed on the EmailProvider database
2896     */
2897    private String genQueryAccountMailboxes(String[] uiProjection) {
2898        StringBuilder sb = genSelect(sFolderListMap, uiProjection);
2899        // Make constant
2900        sb.append(" FROM " + Mailbox.TABLE_NAME + " WHERE " + Mailbox.ACCOUNT_KEY + "=? ORDER BY ");
2901        sb.append(MAILBOX_ORDER_BY);
2902        return sb.toString();
2903    }
2904
2905    /**
2906     * Given the email address of an account, return its account id (the _id row in the Account
2907     * table), or NO_ACCOUNT (-1) if not found
2908     *
2909     * @param email the email address of the account
2910     * @return the account id for this account, or NO_ACCOUNT if not found
2911     */
2912    private long findAccountIdByName(String email) {
2913        Map<String, Cursor> accountCache = mCacheAccount.getSnapshot();
2914        Collection<Cursor> accounts = accountCache.values();
2915        for (Cursor accountCursor: accounts) {
2916            if (accountCursor.getString(Account.CONTENT_EMAIL_ADDRESS_COLUMN).equals(email)) {
2917                return accountCursor.getLong(Account.CONTENT_ID_COLUMN);
2918            }
2919        }
2920        return Account.NO_ACCOUNT;
2921    }
2922
2923    /**
2924     * Handle UnifiedEmail queries here (dispatched from query())
2925     *
2926     * @param match the UriMatcher match for the original uri passed in from UnifiedEmail
2927     * @param uri the original uri passed in from UnifiedEmail
2928     * @param uiProjection the projection passed in from UnifiedEmail
2929     * @return the result Cursor
2930     */
2931    private Cursor uiQuery(int match, Uri uri, String[] uiProjection) {
2932        Context context = getContext();
2933        SQLiteDatabase db = getDatabase(context);
2934        // Should we ever return null, or throw an exception??
2935        Cursor c = null;
2936        switch(match) {
2937            case UI_FOLDERS:
2938                // We are passed the email address (unique account identifier) in the uri; we
2939                // need to turn this into the _id of the Account row in the EmailProvider db
2940                String accountName = uri.getPathSegments().get(1);
2941                long acctId = findAccountIdByName(accountName);
2942                if (acctId == Account.NO_ACCOUNT) return null;
2943                c = db.rawQuery(genQueryAccountMailboxes(uiProjection),
2944                        new String[] {Long.toString(acctId)});
2945                break;
2946            case UI_MESSAGES:
2947                String id = uri.getPathSegments().get(1);
2948                c = db.rawQuery(genQueryMailboxMessages(uiProjection), new String[] {id});
2949                break;
2950            case UI_MESSAGE:
2951                id = uri.getPathSegments().get(1);
2952                c = db.rawQuery(genQueryViewMessage(uiProjection), new String[] {id});
2953                break;
2954        }
2955        if (c != null) {
2956            // Notify UIProvider on changes
2957            c.setNotificationUri(context.getContentResolver(), uri);
2958        }
2959        return c;
2960    }
2961
2962    private int uiSendmail(Uri uri, ContentValues values) {
2963        Context context = getContext();
2964        String accountName = uri.getPathSegments().get(1);
2965        long acctId = findAccountIdByName(accountName);
2966        if (acctId == Account.NO_ACCOUNT) return 0;
2967        Mailbox mailbox = Mailbox.restoreMailboxOfType(context, acctId, Mailbox.TYPE_OUTBOX);
2968        if (mailbox == null) return 0;
2969        Message msg = new Message();
2970        // Fill in the message
2971        msg.mTo = values.getAsString(UIProvider.MessageColumns.TO);
2972        msg.mCc = values.getAsString(UIProvider.MessageColumns.CC);
2973        msg.mBcc = values.getAsString(UIProvider.MessageColumns.BCC);
2974        msg.mSubject = values.getAsString(UIProvider.MessageColumns.SUBJECT);
2975        msg.mText = values.getAsString(UIProvider.MessageColumns.BODY_TEXT);
2976        msg.mHtml = values.getAsString(UIProvider.MessageColumns.BODY_HTML);
2977        msg.mMailboxKey = mailbox.mId;
2978        msg.mAccountKey = mailbox.mAccountKey;
2979        // Save it
2980        msg.save(context);
2981        return 1;
2982    }
2983}
2984