/* * 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. * TODO(rtenneti): Fix the following special SQLLite table name when SQLLite changes. */ private static final String PRIMARY_TABLE = "sqlite_master"; /** Column containing the name of the tables and views */ private static final String[] PRIMARY_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"; // A conversation is enterprise if one of the participant is a enterprise contact. public static final String IS_ENTERPRISE = "IS_ENTERPRISE"; } // 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 ," + ConversationColumns.IS_ENTERPRISE + " INT DEFAULT(0)" + ");"; 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.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; } } } public static void rebuildAllViews(final DatabaseWrapper db) { for (final String sql : DatabaseHelper.CREATE_VIEW_SQLS) { db.execSQL(sql); } } /** * Drops all user-defined tables from the given database. */ private static void dropAllTables(final SQLiteDatabase db) { final Cursor tableCursor = db.query(PRIMARY_TABLE, PRIMARY_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(PRIMARY_TABLE, PRIMARY_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. */ public static void dropAllViews(final SQLiteDatabase db) { final Cursor viewCursor = db.query(PRIMARY_TABLE, PRIMARY_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(PRIMARY_TABLE, PRIMARY_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); } }