From 461a34b466cb4b13dbbc2ec6330b31e217b2ac4e Mon Sep 17 00:00:00 2001 From: Mike Dodd Date: Tue, 11 Aug 2015 11:16:59 -0700 Subject: Initial checkin of AOSP Messaging app. b/23110861 Change-Id: I9aa980d7569247d6b2ca78f5dcb4502e1eaadb8a --- .../messaging/datamodel/DatabaseHelper.java | 813 +++++++++++++++++++++ 1 file changed, 813 insertions(+) create mode 100644 src/com/android/messaging/datamodel/DatabaseHelper.java (limited to 'src/com/android/messaging/datamodel/DatabaseHelper.java') 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}. + *

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); + } +} -- cgit v1.2.3