Initial checkin of AOSP Messaging app.

b/23110861

Change-Id: I9aa980d7569247d6b2ca78f5dcb4502e1eaadb8a

1 files changed, 813 insertions, 0 deletions

diff --git a/src/com/android/messaging/datamodel/DatabaseHelper.java b/src/com/android/messaging/datamodel/DatabaseHelper.java
new file mode 100644
index 0000000..f16bb3c
--- /dev/null
+++ b/src/com/android/messaging/datamodel/DatabaseHelper.java
@@ -0,0 +1,813 @@
+/*
+ * Copyright (C) 2015 The Android Open Source Project
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *      http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.android.messaging.datamodel;
+
+import android.content.Context;
+import android.database.Cursor;
+import android.database.SQLException;
+import android.database.sqlite.SQLiteDatabase;
+import android.database.sqlite.SQLiteOpenHelper;
+import android.provider.BaseColumns;
+
+import com.android.messaging.BugleApplication;
+import com.android.messaging.R;
+import com.android.messaging.datamodel.data.ConversationListItemData;
+import com.android.messaging.datamodel.data.MessageData;
+import com.android.messaging.datamodel.data.ParticipantData;
+import com.android.messaging.util.Assert;
+import com.android.messaging.util.Assert.DoesNotRunOnMainThread;
+import com.android.messaging.util.LogUtil;
+import com.google.common.annotations.VisibleForTesting;
+
+/**
+ * TODO: Open Issues:
+ * - Should we be storing the draft messages in the regular messages table or should we have a
+ *   separate table for drafts to keep the normal messages query as simple as possible?
+ */
+
+/**
+ * Allows access to the SQL database.  This is package private.
+ */
+public class DatabaseHelper extends SQLiteOpenHelper {
+    public static final String DATABASE_NAME = "bugle_db";
+
+    private static final int getDatabaseVersion(final Context context) {
+        return Integer.parseInt(context.getResources().getString(R.string.database_version));
+    }
+
+    /** Table containing names of all other tables and views */
+    private static final String MASTER_TABLE = "sqlite_master";
+    /** Column containing the name of the tables and views */
+    private static final String[] MASTER_COLUMNS = new String[] { "name", };
+
+    // Table names
+    public static final String CONVERSATIONS_TABLE = "conversations";
+    public static final String MESSAGES_TABLE = "messages";
+    public static final String PARTS_TABLE = "parts";
+    public static final String PARTICIPANTS_TABLE = "participants";
+    public static final String CONVERSATION_PARTICIPANTS_TABLE = "conversation_participants";
+
+    // Views
+    static final String DRAFT_PARTS_VIEW = "draft_parts_view";
+
+    // Conversations table schema
+    public static class ConversationColumns implements BaseColumns {
+        /* SMS/MMS Thread ID from the system provider */
+        public static final String SMS_THREAD_ID = "sms_thread_id";
+
+        /* Display name for the conversation */
+        public static final String NAME = "name";
+
+        /* Latest Message ID for the read status to display in conversation list */
+        public static final String LATEST_MESSAGE_ID = "latest_message_id";
+
+        /* Latest text snippet for display in conversation list */
+        public static final String SNIPPET_TEXT = "snippet_text";
+
+        /* Latest text subject for display in conversation list, empty string if none exists */
+        public static final String SUBJECT_TEXT = "subject_text";
+
+        /* Preview Uri */
+        public static final String PREVIEW_URI = "preview_uri";
+
+        /* The preview uri's content type */
+        public static final String PREVIEW_CONTENT_TYPE = "preview_content_type";
+
+        /* If we should display the current draft snippet/preview pair or snippet/preview pair */
+        public static final String SHOW_DRAFT = "show_draft";
+
+        /* Latest draft text subject for display in conversation list, empty string if none exists*/
+        public static final String DRAFT_SUBJECT_TEXT = "draft_subject_text";
+
+        /* Latest draft text snippet for display, empty string if none exists */
+        public static final String DRAFT_SNIPPET_TEXT = "draft_snippet_text";
+
+        /* Draft Preview Uri, empty string if none exists */
+        public static final String DRAFT_PREVIEW_URI = "draft_preview_uri";
+
+        /* The preview uri's content type */
+        public static final String DRAFT_PREVIEW_CONTENT_TYPE = "draft_preview_content_type";
+
+        /* If this conversation is archived */
+        public static final String ARCHIVE_STATUS = "archive_status";
+
+        /* Timestamp for sorting purposes */
+        public static final String SORT_TIMESTAMP = "sort_timestamp";
+
+        /* Last read message timestamp */
+        public static final String LAST_READ_TIMESTAMP = "last_read_timestamp";
+
+        /* Avatar for the conversation. Could be for group of individual */
+        public static final String ICON = "icon";
+
+        /* Participant contact ID if this conversation has a single participant. -1 otherwise */
+        public static final String PARTICIPANT_CONTACT_ID = "participant_contact_id";
+
+        /* Participant lookup key if this conversation has a single participant. null otherwise */
+        public static final String PARTICIPANT_LOOKUP_KEY = "participant_lookup_key";
+
+        /*
+         * Participant's normalized destination if this conversation has a single participant.
+         * null otherwise.
+         */
+        public static final String OTHER_PARTICIPANT_NORMALIZED_DESTINATION =
+                "participant_normalized_destination";
+
+        /* Default self participant for the conversation */
+        public static final String CURRENT_SELF_ID = "current_self_id";
+
+        /* Participant count not including self (so will be 1 for 1:1 or bigger for group) */
+        public static final String PARTICIPANT_COUNT = "participant_count";
+
+        /* Should notifications be enabled for this conversation? */
+        public static final String NOTIFICATION_ENABLED = "notification_enabled";
+
+        /* Notification sound used for the conversation */
+        public static final String NOTIFICATION_SOUND_URI = "notification_sound_uri";
+
+        /* Should vibrations be enabled for the conversation's notification? */
+        public static final String NOTIFICATION_VIBRATION = "notification_vibration";
+
+        /* Conversation recipients include email address */
+        public static final String INCLUDE_EMAIL_ADDRESS = "include_email_addr";
+
+        // Record the last received sms's service center info if it indicates that the reply path
+        // is present (TP-Reply-Path), so that we could use it for the subsequent message to send.
+        // Refer to TS 23.040 D.6 and SmsMessageSender.java in Android Messaging app.
+        public static final String SMS_SERVICE_CENTER = "sms_service_center";
+    }
+
+    // Conversation table SQL
+    private static final String CREATE_CONVERSATIONS_TABLE_SQL =
+            "CREATE TABLE " + CONVERSATIONS_TABLE + "("
+                    + ConversationColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+                    // TODO : Int? Required not default?
+                    + ConversationColumns.SMS_THREAD_ID + " INT DEFAULT(0), "
+                    + ConversationColumns.NAME + " TEXT, "
+                    + ConversationColumns.LATEST_MESSAGE_ID + " INT, "
+                    + ConversationColumns.SNIPPET_TEXT + " TEXT, "
+                    + ConversationColumns.SUBJECT_TEXT + " TEXT, "
+                    + ConversationColumns.PREVIEW_URI + " TEXT, "
+                    + ConversationColumns.PREVIEW_CONTENT_TYPE + " TEXT, "
+                    + ConversationColumns.SHOW_DRAFT + " INT DEFAULT(0), "
+                    + ConversationColumns.DRAFT_SNIPPET_TEXT + " TEXT, "
+                    + ConversationColumns.DRAFT_SUBJECT_TEXT + " TEXT, "
+                    + ConversationColumns.DRAFT_PREVIEW_URI + " TEXT, "
+                    + ConversationColumns.DRAFT_PREVIEW_CONTENT_TYPE + " TEXT, "
+                    + ConversationColumns.ARCHIVE_STATUS + " INT DEFAULT(0), "
+                    + ConversationColumns.SORT_TIMESTAMP + " INT DEFAULT(0), "
+                    + ConversationColumns.LAST_READ_TIMESTAMP + " INT DEFAULT(0), "
+                    + ConversationColumns.ICON + " TEXT, "
+                    + ConversationColumns.PARTICIPANT_CONTACT_ID + " INT DEFAULT ( "
+                            + ParticipantData.PARTICIPANT_CONTACT_ID_NOT_RESOLVED + "), "
+                    + ConversationColumns.PARTICIPANT_LOOKUP_KEY + " TEXT, "
+                    + ConversationColumns.OTHER_PARTICIPANT_NORMALIZED_DESTINATION + " TEXT, "
+                    + ConversationColumns.CURRENT_SELF_ID + " TEXT, "
+                    + ConversationColumns.PARTICIPANT_COUNT + " INT DEFAULT(0), "
+                    + ConversationColumns.NOTIFICATION_ENABLED + " INT DEFAULT(1), "
+                    + ConversationColumns.NOTIFICATION_SOUND_URI + " TEXT, "
+                    + ConversationColumns.NOTIFICATION_VIBRATION + " INT DEFAULT(1), "
+                    + ConversationColumns.INCLUDE_EMAIL_ADDRESS + " INT DEFAULT(0), "
+                    + ConversationColumns.SMS_SERVICE_CENTER + " TEXT "
+                    + ");";
+
+    private static final String CONVERSATIONS_TABLE_SMS_THREAD_ID_INDEX_SQL =
+            "CREATE INDEX index_" + CONVERSATIONS_TABLE + "_" + ConversationColumns.SMS_THREAD_ID
+            + " ON " +  CONVERSATIONS_TABLE
+            + "(" + ConversationColumns.SMS_THREAD_ID + ")";
+
+    private static final String CONVERSATIONS_TABLE_ARCHIVE_STATUS_INDEX_SQL =
+            "CREATE INDEX index_" + CONVERSATIONS_TABLE + "_" + ConversationColumns.ARCHIVE_STATUS
+            + " ON " +  CONVERSATIONS_TABLE
+            + "(" + ConversationColumns.ARCHIVE_STATUS + ")";
+
+    private static final String CONVERSATIONS_TABLE_SORT_TIMESTAMP_INDEX_SQL =
+            "CREATE INDEX index_" + CONVERSATIONS_TABLE + "_" + ConversationColumns.SORT_TIMESTAMP
+            + " ON " +  CONVERSATIONS_TABLE
+            + "(" + ConversationColumns.SORT_TIMESTAMP + ")";
+
+    // Messages table schema
+    public static class MessageColumns implements BaseColumns {
+        /* conversation id that this message belongs to */
+        public static final String CONVERSATION_ID = "conversation_id";
+
+        /* participant which send this message */
+        public static final String SENDER_PARTICIPANT_ID = "sender_id";
+
+        /* This is bugle's internal status for the message */
+        public static final String STATUS = "message_status";
+
+        /* Type of message: SMS, MMS or MMS notification */
+        public static final String PROTOCOL = "message_protocol";
+
+        /* This is the time that the sender sent the message */
+        public static final String SENT_TIMESTAMP = "sent_timestamp";
+
+        /* Time that we received the message on this device */
+        public static final String RECEIVED_TIMESTAMP = "received_timestamp";
+
+        /* When the message has been seen by a user in a notification */
+        public static final String SEEN = "seen";
+
+        /* When the message has been read by a user */
+        public static final String READ = "read";
+
+        /* participant representing the sim which processed this message */
+        public static final String SELF_PARTICIPANT_ID = "self_id";
+
+        /*
+         * Time when a retry is initiated. This is used to compute the retry window
+         * when we retry sending/downloading a message.
+         */
+        public static final String RETRY_START_TIMESTAMP = "retry_start_timestamp";
+
+        // Columns which map to the SMS provider
+
+        /* Message ID from the platform provider */
+        public static final String SMS_MESSAGE_URI = "sms_message_uri";
+
+        /* The message priority for MMS message */
+        public static final String SMS_PRIORITY = "sms_priority";
+
+        /* The message size for MMS message */
+        public static final String SMS_MESSAGE_SIZE = "sms_message_size";
+
+        /* The subject for MMS message */
+        public static final String MMS_SUBJECT = "mms_subject";
+
+        /* Transaction id for MMS notificaiton */
+        public static final String MMS_TRANSACTION_ID = "mms_transaction_id";
+
+        /* Content location for MMS notificaiton */
+        public static final String MMS_CONTENT_LOCATION = "mms_content_location";
+
+        /* The expiry time (ms) for MMS message */
+        public static final String MMS_EXPIRY = "mms_expiry";
+
+        /* The detailed status (RESPONSE_STATUS or RETRIEVE_STATUS) for MMS message */
+        public static final String RAW_TELEPHONY_STATUS = "raw_status";
+    }
+
+    // Messages table SQL
+    private static final String CREATE_MESSAGES_TABLE_SQL =
+            "CREATE TABLE " + MESSAGES_TABLE + " ("
+                    + MessageColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+                    + MessageColumns.CONVERSATION_ID + " INT, "
+                    + MessageColumns.SENDER_PARTICIPANT_ID + " INT, "
+                    + MessageColumns.SENT_TIMESTAMP + " INT DEFAULT(0), "
+                    + MessageColumns.RECEIVED_TIMESTAMP + " INT DEFAULT(0), "
+                    + MessageColumns.PROTOCOL + " INT DEFAULT(0), "
+                    + MessageColumns.STATUS + " INT DEFAULT(0), "
+                    + MessageColumns.SEEN + " INT DEFAULT(0), "
+                    + MessageColumns.READ + " INT DEFAULT(0), "
+                    + MessageColumns.SMS_MESSAGE_URI + " TEXT, "
+                    + MessageColumns.SMS_PRIORITY + " INT DEFAULT(0), "
+                    + MessageColumns.SMS_MESSAGE_SIZE + " INT DEFAULT(0), "
+                    + MessageColumns.MMS_SUBJECT + " TEXT, "
+                    + MessageColumns.MMS_TRANSACTION_ID + " TEXT, "
+                    + MessageColumns.MMS_CONTENT_LOCATION + " TEXT, "
+                    + MessageColumns.MMS_EXPIRY + " INT DEFAULT(0), "
+                    + MessageColumns.RAW_TELEPHONY_STATUS + " INT DEFAULT(0), "
+                    + MessageColumns.SELF_PARTICIPANT_ID + " INT, "
+                    + MessageColumns.RETRY_START_TIMESTAMP + " INT DEFAULT(0), "
+                    + "FOREIGN KEY (" + MessageColumns.CONVERSATION_ID + ") REFERENCES "
+                    + CONVERSATIONS_TABLE + "(" + ConversationColumns._ID + ") ON DELETE CASCADE "
+                    + "FOREIGN KEY (" + MessageColumns.SENDER_PARTICIPANT_ID + ") REFERENCES "
+                    + PARTICIPANTS_TABLE + "(" + ParticipantColumns._ID + ") ON DELETE SET NULL "
+                    + "FOREIGN KEY (" + MessageColumns.SELF_PARTICIPANT_ID + ") REFERENCES "
+                    + PARTICIPANTS_TABLE + "(" + ParticipantColumns._ID + ") ON DELETE SET NULL "
+                    + ");";
+
+    // Primary sort index for messages table : by conversation id, status, received timestamp.
+    private static final String MESSAGES_TABLE_SORT_INDEX_SQL =
+            "CREATE INDEX index_" + MESSAGES_TABLE + "_sort ON " +  MESSAGES_TABLE + "("
+                    + MessageColumns.CONVERSATION_ID + ", "
+                    + MessageColumns.STATUS + ", "
+                    + MessageColumns.RECEIVED_TIMESTAMP + ")";
+
+    private static final String MESSAGES_TABLE_STATUS_SEEN_INDEX_SQL =
+            "CREATE INDEX index_" + MESSAGES_TABLE + "_status_seen ON " +  MESSAGES_TABLE + "("
+                    + MessageColumns.STATUS + ", "
+                    + MessageColumns.SEEN + ")";
+
+    // Parts table schema
+    // A part may contain text or a media url, but not both.
+    public static class PartColumns implements BaseColumns {
+        /* message id that this part belongs to */
+        public static final String MESSAGE_ID = "message_id";
+
+        /* conversation id that this part belongs to */
+        public static final String CONVERSATION_ID = "conversation_id";
+
+        /* text for this part */
+        public static final String TEXT = "text";
+
+        /* content uri for this part */
+        public static final String CONTENT_URI = "uri";
+
+        /* content type for this part */
+        public static final String CONTENT_TYPE = "content_type";
+
+        /* cached width for this part (for layout while loading) */
+        public static final String WIDTH = "width";
+
+        /* cached height for this part (for layout while loading) */
+        public static final String HEIGHT = "height";
+
+        /* de-normalized copy of timestamp from the messages table.  This is populated
+         * via an insert trigger on the parts table.
+         */
+        public static final String TIMESTAMP = "timestamp";
+    }
+
+    // Message part table SQL
+    private static final String CREATE_PARTS_TABLE_SQL =
+            "CREATE TABLE " + PARTS_TABLE + "("
+                    + PartColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+                    + PartColumns.MESSAGE_ID + " INT,"
+                    + PartColumns.TEXT + " TEXT,"
+                    + PartColumns.CONTENT_URI + " TEXT,"
+                    + PartColumns.CONTENT_TYPE + " TEXT,"
+                    + PartColumns.WIDTH + " INT DEFAULT("
+                    + MessagingContentProvider.UNSPECIFIED_SIZE + "),"
+                    + PartColumns.HEIGHT + " INT DEFAULT("
+                    + MessagingContentProvider.UNSPECIFIED_SIZE + "),"
+                    + PartColumns.TIMESTAMP + " INT, "
+                    + PartColumns.CONVERSATION_ID + " INT NOT NULL,"
+                    + "FOREIGN KEY (" + PartColumns.MESSAGE_ID + ") REFERENCES "
+                    + MESSAGES_TABLE + "(" + MessageColumns._ID + ") ON DELETE CASCADE "
+                    + "FOREIGN KEY (" + PartColumns.CONVERSATION_ID + ") REFERENCES "
+                    + CONVERSATIONS_TABLE + "(" + ConversationColumns._ID + ") ON DELETE CASCADE "
+                    + ");";
+
+    public static final String CREATE_PARTS_TRIGGER_SQL =
+            "CREATE TRIGGER " + PARTS_TABLE + "_TRIGGER" + " AFTER INSERT ON " + PARTS_TABLE
+            + " FOR EACH ROW "
+            + " BEGIN UPDATE " + PARTS_TABLE
+            + " SET " + PartColumns.TIMESTAMP + "="
+            + " (SELECT received_timestamp FROM " + MESSAGES_TABLE + " WHERE " + MESSAGES_TABLE
+            + "." + MessageColumns._ID + "=" + "NEW." + PartColumns.MESSAGE_ID + ")"
+            + " WHERE " + PARTS_TABLE + "." + PartColumns._ID + "=" + "NEW." + PartColumns._ID
+            + "; END";
+
+    public static final String CREATE_MESSAGES_TRIGGER_SQL =
+            "CREATE TRIGGER " + MESSAGES_TABLE + "_TRIGGER" + " AFTER UPDATE OF "
+            + MessageColumns.RECEIVED_TIMESTAMP + " ON " + MESSAGES_TABLE
+            + " FOR EACH ROW BEGIN UPDATE " + PARTS_TABLE + " SET " + PartColumns.TIMESTAMP
+            + " = NEW." + MessageColumns.RECEIVED_TIMESTAMP + " WHERE " + PARTS_TABLE + "."
+            + PartColumns.MESSAGE_ID + " = NEW." + MessageColumns._ID
+            + "; END;";
+
+    // Primary sort index for parts table : by message_id
+    private static final String PARTS_TABLE_MESSAGE_INDEX_SQL =
+            "CREATE INDEX index_" + PARTS_TABLE + "_message_id ON " + PARTS_TABLE + "("
+                    + PartColumns.MESSAGE_ID + ")";
+
+    // Participants table schema
+    public static class ParticipantColumns implements BaseColumns {
+        /* The subscription id for the sim associated with this self participant.
+         * Introduced in L. For earlier versions will always be default_sub_id (-1).
+         * For multi sim devices (or cases where the sim was changed) single device
+         * may have several different sub_id values */
+        public static final String SUB_ID = "sub_id";
+
+        /* The slot of the active SIM (inserted in the device) for this self-participant. If the
+         * self-participant doesn't correspond to any active SIM, this will be
+         * {@link android.telephony.SubscriptionManager#INVALID_SLOT_ID}.
+         * The column is ignored for all non-self participants.
+         */
+        public static final String SIM_SLOT_ID = "sim_slot_id";
+
+        /* The phone number stored in a standard E164 format if possible.  This is unique for a
+         * given participant.  We can't handle multiple participants with the same phone number
+         * since we don't know which of them a message comes from. This can also be an email
+         * address, in which case this is the same as the displayed address */
+        public static final String NORMALIZED_DESTINATION = "normalized_destination";
+
+        /* The phone number as originally supplied and used for dialing. Not necessarily in E164
+         * format or unique */
+        public static final String SEND_DESTINATION = "send_destination";
+
+        /* The user-friendly formatting of the phone number according to the region setting of
+         * the device when the row was added. */
+        public static final String DISPLAY_DESTINATION = "display_destination";
+
+        /* A string with this participant's full name or a pretty printed phone number */
+        public static final String FULL_NAME = "full_name";
+
+        /* A string with just this participant's first name */
+        public static final String FIRST_NAME = "first_name";
+
+        /* A local URI to an asset for the icon for this participant */
+        public static final String PROFILE_PHOTO_URI = "profile_photo_uri";
+
+        /* Contact id for matching local contact for this participant */
+        public static final String CONTACT_ID = "contact_id";
+
+        /* String that contains hints on how to find contact information in a contact lookup */
+        public static final String LOOKUP_KEY = "lookup_key";
+
+        /* If this participant is blocked */
+        public static final String BLOCKED = "blocked";
+
+        /* The color of the subscription (FOR SELF PARTICIPANTS ONLY) */
+        public static final String SUBSCRIPTION_COLOR = "subscription_color";
+
+        /* The name of the subscription (FOR SELF PARTICIPANTS ONLY) */
+        public static final String SUBSCRIPTION_NAME = "subscription_name";
+
+        /* The exact destination stored in Contacts for this participant */
+        public static final String CONTACT_DESTINATION = "contact_destination";
+    }
+
+    // Participants table SQL
+    private static final String CREATE_PARTICIPANTS_TABLE_SQL =
+            "CREATE TABLE " + PARTICIPANTS_TABLE + "("
+                    + ParticipantColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+                    + ParticipantColumns.SUB_ID + " INT DEFAULT("
+                    + ParticipantData.OTHER_THAN_SELF_SUB_ID + "),"
+                    + ParticipantColumns.SIM_SLOT_ID + " INT DEFAULT("
+                    + ParticipantData.INVALID_SLOT_ID + "),"
+                    + ParticipantColumns.NORMALIZED_DESTINATION + " TEXT,"
+                    + ParticipantColumns.SEND_DESTINATION + " TEXT,"
+                    + ParticipantColumns.DISPLAY_DESTINATION + " TEXT,"
+                    + ParticipantColumns.FULL_NAME + " TEXT,"
+                    + ParticipantColumns.FIRST_NAME + " TEXT,"
+                    + ParticipantColumns.PROFILE_PHOTO_URI + " TEXT, "
+                    + ParticipantColumns.CONTACT_ID + " INT DEFAULT( "
+                    + ParticipantData.PARTICIPANT_CONTACT_ID_NOT_RESOLVED + "), "
+                    + ParticipantColumns.LOOKUP_KEY + " STRING, "
+                    + ParticipantColumns.BLOCKED + " INT DEFAULT(0), "
+                    + ParticipantColumns.SUBSCRIPTION_NAME + " TEXT, "
+                    + ParticipantColumns.SUBSCRIPTION_COLOR + " INT DEFAULT(0), "
+                    + ParticipantColumns.CONTACT_DESTINATION + " TEXT, "
+                    + "UNIQUE (" + ParticipantColumns.NORMALIZED_DESTINATION + ", "
+                    + ParticipantColumns.SUB_ID + ") ON CONFLICT FAIL" + ");";
+
+    private static final String CREATE_SELF_PARTICIPANT_SQL =
+            "INSERT INTO " + PARTICIPANTS_TABLE
+            + " ( " +  ParticipantColumns.SUB_ID + " ) VALUES ( %s )";
+
+    static String getCreateSelfParticipantSql(int subId) {
+        return String.format(CREATE_SELF_PARTICIPANT_SQL, subId);
+    }
+
+    // Conversation Participants table schema - contains a list of participants excluding the user
+    // in a given conversation.
+    public static class ConversationParticipantsColumns implements BaseColumns {
+        /* participant id of someone in this conversation */
+        public static final String PARTICIPANT_ID = "participant_id";
+
+        /* conversation id that this participant belongs to */
+        public static final String CONVERSATION_ID = "conversation_id";
+    }
+
+    // Conversation Participants table SQL
+    private static final String CREATE_CONVERSATION_PARTICIPANTS_TABLE_SQL =
+            "CREATE TABLE " + CONVERSATION_PARTICIPANTS_TABLE + "("
+                    + ConversationParticipantsColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+                    + ConversationParticipantsColumns.CONVERSATION_ID + " INT,"
+                    + ConversationParticipantsColumns.PARTICIPANT_ID + " INT,"
+                    + "UNIQUE (" + ConversationParticipantsColumns.CONVERSATION_ID + ","
+                    + ConversationParticipantsColumns.PARTICIPANT_ID + ") ON CONFLICT FAIL, "
+                    + "FOREIGN KEY (" + ConversationParticipantsColumns.CONVERSATION_ID + ") "
+                    + "REFERENCES " + CONVERSATIONS_TABLE + "(" + ConversationColumns._ID + ")"
+                    + " ON DELETE CASCADE "
+                    + "FOREIGN KEY (" + ConversationParticipantsColumns.PARTICIPANT_ID + ")"
+                    + " REFERENCES " + PARTICIPANTS_TABLE + "(" + ParticipantColumns._ID + "));";
+
+    // Primary access pattern for conversation participants is to look them up for a specific
+    // conversation.
+    private static final String CONVERSATION_PARTICIPANTS_TABLE_CONVERSATION_ID_INDEX_SQL =
+            "CREATE INDEX index_" + CONVERSATION_PARTICIPANTS_TABLE + "_"
+                    + ConversationParticipantsColumns.CONVERSATION_ID
+                    + " ON " +  CONVERSATION_PARTICIPANTS_TABLE
+                    + "(" + ConversationParticipantsColumns.CONVERSATION_ID + ")";
+
+    // View for getting parts which are for draft messages.
+    static final String DRAFT_PARTS_VIEW_SQL = "CREATE VIEW " +
+            DRAFT_PARTS_VIEW + " AS SELECT "
+            + PARTS_TABLE + '.' + PartColumns._ID
+            + " as " + PartColumns._ID + ", "
+            + PARTS_TABLE + '.' + PartColumns.MESSAGE_ID
+            + " as " + PartColumns.MESSAGE_ID + ", "
+            + PARTS_TABLE + '.' + PartColumns.TEXT
+            + " as " + PartColumns.TEXT + ", "
+            + PARTS_TABLE + '.' + PartColumns.CONTENT_URI
+            + " as " + PartColumns.CONTENT_URI + ", "
+            + PARTS_TABLE + '.' + PartColumns.CONTENT_TYPE
+            + " as " + PartColumns.CONTENT_TYPE + ", "
+            + PARTS_TABLE + '.' + PartColumns.WIDTH
+            + " as " + PartColumns.WIDTH + ", "
+            + PARTS_TABLE + '.' + PartColumns.HEIGHT
+            + " as " + PartColumns.HEIGHT + ", "
+            + MESSAGES_TABLE + '.' + MessageColumns.CONVERSATION_ID
+            + " as " + MessageColumns.CONVERSATION_ID + " "
+            + " FROM " + MESSAGES_TABLE + " LEFT JOIN " + PARTS_TABLE + " ON ("
+            + MESSAGES_TABLE + "." + MessageColumns._ID
+            + "=" + PARTS_TABLE + "." + PartColumns.MESSAGE_ID + ")"
+            // Exclude draft messages from main view
+            + " WHERE " + MESSAGES_TABLE + "." + MessageColumns.STATUS
+            + " = " + MessageData.BUGLE_STATUS_OUTGOING_DRAFT;
+
+    // List of all our SQL tables
+    private static final String[] CREATE_TABLE_SQLS = new String[] {
+        CREATE_CONVERSATIONS_TABLE_SQL,
+        CREATE_MESSAGES_TABLE_SQL,
+        CREATE_PARTS_TABLE_SQL,
+        CREATE_PARTICIPANTS_TABLE_SQL,
+        CREATE_CONVERSATION_PARTICIPANTS_TABLE_SQL,
+    };
+
+    // List of all our indices
+    private static final String[] CREATE_INDEX_SQLS = new String[] {
+        CONVERSATIONS_TABLE_SMS_THREAD_ID_INDEX_SQL,
+        CONVERSATIONS_TABLE_ARCHIVE_STATUS_INDEX_SQL,
+        CONVERSATIONS_TABLE_SORT_TIMESTAMP_INDEX_SQL,
+        MESSAGES_TABLE_SORT_INDEX_SQL,
+        MESSAGES_TABLE_STATUS_SEEN_INDEX_SQL,
+        PARTS_TABLE_MESSAGE_INDEX_SQL,
+        CONVERSATION_PARTICIPANTS_TABLE_CONVERSATION_ID_INDEX_SQL,
+    };
+
+    // List of all our SQL triggers
+    private static final String[] CREATE_TRIGGER_SQLS = new String[] {
+            CREATE_PARTS_TRIGGER_SQL,
+            CREATE_MESSAGES_TRIGGER_SQL,
+    };
+
+    // List of all our views
+    private static final String[] CREATE_VIEW_SQLS = new String[] {
+        ConversationListItemData.getConversationListViewSql(),
+        ConversationImagePartsView.getCreateSql(),
+        DRAFT_PARTS_VIEW_SQL,
+    };
+
+    private static final Object sLock = new Object();
+    private final Context mApplicationContext;
+    private static DatabaseHelper sHelperInstance;      // Protected by sLock.
+
+    private final Object mDatabaseWrapperLock = new Object();
+    private DatabaseWrapper mDatabaseWrapper;           // Protected by mDatabaseWrapperLock.
+    private final DatabaseUpgradeHelper mUpgradeHelper = new DatabaseUpgradeHelper();
+
+    /**
+     * Get a (singleton) instance of {@link DatabaseHelper}, creating one if there isn't one yet.
+     * This is the only public method for getting a new instance of the class.
+     * @param context Should be the application context (or something that will live for the
+     * lifetime of the application).
+     * @return The current (or a new) DatabaseHelper instance.
+     */
+    public static DatabaseHelper getInstance(final Context context) {
+        synchronized (sLock) {
+            if (sHelperInstance == null) {
+                sHelperInstance = new DatabaseHelper(context);
+            }
+            return sHelperInstance;
+        }
+    }
+
+    /**
+     * Private constructor, used from {@link #getInstance()}.
+     * @param context Should be the application context (or something that will live for the
+     * lifetime of the application).
+     */
+    private DatabaseHelper(final Context context) {
+        super(context, DATABASE_NAME, null, getDatabaseVersion(context), null);
+        mApplicationContext = context;
+    }
+
+    /**
+     * Test method that always instantiates a new DatabaseHelper instance. This should
+     * be used ONLY by the tests and never by the real application.
+     * @param context Test context.
+     * @return Brand new DatabaseHelper instance.
+     */
+    @VisibleForTesting
+    static DatabaseHelper getNewInstanceForTest(final Context context) {
+        Assert.isEngBuild();
+        Assert.isTrue(BugleApplication.isRunningTests());
+        return new DatabaseHelper(context);
+    }
+
+    /**
+     * Get the (singleton) instance of @{link DatabaseWrapper}.
+     * <p>The database is always opened as a writeable database.
+     * @return The current (or a new) DatabaseWrapper instance.
+     */
+    @DoesNotRunOnMainThread
+    DatabaseWrapper getDatabase() {
+        // We prevent the main UI thread from accessing the database here since we have to allow
+        // public access to this class to enable sub-packages to access data.
+        Assert.isNotMainThread();
+
+        synchronized (mDatabaseWrapperLock) {
+            if (mDatabaseWrapper == null) {
+                mDatabaseWrapper = new DatabaseWrapper(mApplicationContext, getWritableDatabase());
+            }
+            return mDatabaseWrapper;
+        }
+    }
+
+    @Override
+    public void onDowngrade(final SQLiteDatabase db, final int oldVersion, final int newVersion) {
+        mUpgradeHelper.onDowngrade(db, oldVersion, newVersion);
+    }
+
+    /**
+     * Drops and recreates all tables.
+     */
+    public static void rebuildTables(final SQLiteDatabase db) {
+        // Drop tables first, then views, and indices.
+        dropAllTables(db);
+        dropAllViews(db);
+        dropAllIndexes(db);
+        dropAllTriggers(db);
+
+        // Recreate the whole database.
+        createDatabase(db);
+    }
+
+    /**
+     * Drop and rebuild a given view.
+     */
+    static void rebuildView(final SQLiteDatabase db, final String viewName,
+            final String createViewSql) {
+        dropView(db, viewName, true /* throwOnFailure */);
+        db.execSQL(createViewSql);
+    }
+
+    private static void dropView(final SQLiteDatabase db, final String viewName,
+            final boolean throwOnFailure) {
+        final String dropPrefix = "DROP VIEW IF EXISTS ";
+        try {
+            db.execSQL(dropPrefix + viewName);
+        } catch (final SQLException ex) {
+            if (LogUtil.isLoggable(LogUtil.BUGLE_TAG, LogUtil.DEBUG)) {
+                LogUtil.d(LogUtil.BUGLE_TAG, "unable to drop view " + viewName + " "
+                        + ex);
+            }
+
+            if (throwOnFailure) {
+                throw ex;
+            }
+        }
+    }
+
+    /**
+     * Drops all user-defined tables from the given database.
+     */
+    private static void dropAllTables(final SQLiteDatabase db) {
+        final Cursor tableCursor =
+                db.query(MASTER_TABLE, MASTER_COLUMNS, "type='table'", null, null, null, null);
+        if (tableCursor != null) {
+            try {
+                final String dropPrefix = "DROP TABLE IF EXISTS ";
+                while (tableCursor.moveToNext()) {
+                    final String tableName = tableCursor.getString(0);
+
+                    // Skip special tables
+                    if (tableName.startsWith("android_") || tableName.startsWith("sqlite_")) {
+                        continue;
+                    }
+                    try {
+                        db.execSQL(dropPrefix + tableName);
+                    } catch (final SQLException ex) {
+                        if (LogUtil.isLoggable(LogUtil.BUGLE_TAG, LogUtil.DEBUG)) {
+                            LogUtil.d(LogUtil.BUGLE_TAG, "unable to drop table " + tableName + " "
+                                    + ex);
+                        }
+                    }
+                }
+            } finally {
+                tableCursor.close();
+            }
+        }
+    }
+
+    /**
+     * Drops all user-defined triggers from the given database.
+     */
+    private static void dropAllTriggers(final SQLiteDatabase db) {
+        final Cursor triggerCursor =
+                db.query(MASTER_TABLE, MASTER_COLUMNS, "type='trigger'", null, null, null, null);
+        if (triggerCursor != null) {
+            try {
+                final String dropPrefix = "DROP TRIGGER IF EXISTS ";
+                while (triggerCursor.moveToNext()) {
+                    final String triggerName = triggerCursor.getString(0);
+
+                    // Skip special tables
+                    if (triggerName.startsWith("android_") || triggerName.startsWith("sqlite_")) {
+                        continue;
+                    }
+                    try {
+                        db.execSQL(dropPrefix + triggerName);
+                    } catch (final SQLException ex) {
+                        if (LogUtil.isLoggable(LogUtil.BUGLE_TAG, LogUtil.DEBUG)) {
+                            LogUtil.d(LogUtil.BUGLE_TAG, "unable to drop trigger " + triggerName +
+                                    " " + ex);
+                        }
+                    }
+                }
+            } finally {
+                triggerCursor.close();
+            }
+        }
+    }
+
+    /**
+     * Drops all user-defined views from the given database.
+     */
+    private static void dropAllViews(final SQLiteDatabase db) {
+        final Cursor viewCursor =
+                db.query(MASTER_TABLE, MASTER_COLUMNS, "type='view'", null, null, null, null);
+        if (viewCursor != null) {
+            try {
+                while (viewCursor.moveToNext()) {
+                    final String viewName = viewCursor.getString(0);
+                    dropView(db, viewName, false /* throwOnFailure */);
+                }
+            } finally {
+                viewCursor.close();
+            }
+        }
+    }
+
+    /**
+     * Drops all user-defined views from the given database.
+     */
+    private static void dropAllIndexes(final SQLiteDatabase db) {
+        final Cursor indexCursor =
+                db.query(MASTER_TABLE, MASTER_COLUMNS, "type='index'", null, null, null, null);
+        if (indexCursor != null) {
+            try {
+                final String dropPrefix = "DROP INDEX IF EXISTS ";
+                while (indexCursor.moveToNext()) {
+                    final String indexName = indexCursor.getString(0);
+                    try {
+                        db.execSQL(dropPrefix + indexName);
+                    } catch (final SQLException ex) {
+                        if (LogUtil.isLoggable(LogUtil.BUGLE_TAG, LogUtil.DEBUG)) {
+                            LogUtil.d(LogUtil.BUGLE_TAG, "unable to drop index " + indexName + " "
+                                    + ex);
+                        }
+                    }
+                }
+            } finally {
+                indexCursor.close();
+            }
+        }
+    }
+
+    private static void createDatabase(final SQLiteDatabase db) {
+        for (final String sql : CREATE_TABLE_SQLS) {
+            db.execSQL(sql);
+        }
+
+        for (final String sql : CREATE_INDEX_SQLS) {
+            db.execSQL(sql);
+        }
+
+        for (final String sql : CREATE_VIEW_SQLS) {
+            db.execSQL(sql);
+        }
+
+        for (final String sql : CREATE_TRIGGER_SQLS) {
+            db.execSQL(sql);
+        }
+
+        // Enable foreign key constraints
+        db.execSQL("PRAGMA foreign_keys=ON;");
+
+        // Add the default self participant. The default self will be assigned a proper slot id
+        // during participant refresh.
+        db.execSQL(getCreateSelfParticipantSql(ParticipantData.DEFAULT_SELF_SUB_ID));
+
+        DataModel.get().onCreateTables(db);
+    }
+
+    @Override
+    public void onCreate(SQLiteDatabase db) {
+        createDatabase(db);
+    }
+
+    @Override
+    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
+        mUpgradeHelper.doOnUpgrade(db, oldVersion, newVersion);
+    }
+}