1 /* 2 * Copyright (C) 2015 The Android Open Source Project 3 * 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License. 15 */ 16 17 package com.android.messaging.datamodel; 18 19 import android.content.Context; 20 import android.database.Cursor; 21 import android.database.SQLException; 22 import android.database.sqlite.SQLiteDatabase; 23 import android.database.sqlite.SQLiteOpenHelper; 24 import android.provider.BaseColumns; 25 26 import com.android.messaging.BugleApplication; 27 import com.android.messaging.R; 28 import com.android.messaging.datamodel.data.ConversationListItemData; 29 import com.android.messaging.datamodel.data.MessageData; 30 import com.android.messaging.datamodel.data.ParticipantData; 31 import com.android.messaging.util.Assert; 32 import com.android.messaging.util.Assert.DoesNotRunOnMainThread; 33 import com.android.messaging.util.LogUtil; 34 import com.google.common.annotations.VisibleForTesting; 35 36 /** 37 * TODO: Open Issues: 38 * - Should we be storing the draft messages in the regular messages table or should we have a 39 * separate table for drafts to keep the normal messages query as simple as possible? 40 */ 41 42 /** 43 * Allows access to the SQL database. This is package private. 44 */ 45 public class DatabaseHelper extends SQLiteOpenHelper { 46 public static final String DATABASE_NAME = "bugle_db"; 47 getDatabaseVersion(final Context context)48 private static final int getDatabaseVersion(final Context context) { 49 return Integer.parseInt(context.getResources().getString(R.string.database_version)); 50 } 51 52 /** Table containing names of all other tables and views */ 53 private static final String MASTER_TABLE = "sqlite_master"; 54 /** Column containing the name of the tables and views */ 55 private static final String[] MASTER_COLUMNS = new String[] { "name", }; 56 57 // Table names 58 public static final String CONVERSATIONS_TABLE = "conversations"; 59 public static final String MESSAGES_TABLE = "messages"; 60 public static final String PARTS_TABLE = "parts"; 61 public static final String PARTICIPANTS_TABLE = "participants"; 62 public static final String CONVERSATION_PARTICIPANTS_TABLE = "conversation_participants"; 63 64 // Views 65 static final String DRAFT_PARTS_VIEW = "draft_parts_view"; 66 67 // Conversations table schema 68 public static class ConversationColumns implements BaseColumns { 69 /* SMS/MMS Thread ID from the system provider */ 70 public static final String SMS_THREAD_ID = "sms_thread_id"; 71 72 /* Display name for the conversation */ 73 public static final String NAME = "name"; 74 75 /* Latest Message ID for the read status to display in conversation list */ 76 public static final String LATEST_MESSAGE_ID = "latest_message_id"; 77 78 /* Latest text snippet for display in conversation list */ 79 public static final String SNIPPET_TEXT = "snippet_text"; 80 81 /* Latest text subject for display in conversation list, empty string if none exists */ 82 public static final String SUBJECT_TEXT = "subject_text"; 83 84 /* Preview Uri */ 85 public static final String PREVIEW_URI = "preview_uri"; 86 87 /* The preview uri's content type */ 88 public static final String PREVIEW_CONTENT_TYPE = "preview_content_type"; 89 90 /* If we should display the current draft snippet/preview pair or snippet/preview pair */ 91 public static final String SHOW_DRAFT = "show_draft"; 92 93 /* Latest draft text subject for display in conversation list, empty string if none exists*/ 94 public static final String DRAFT_SUBJECT_TEXT = "draft_subject_text"; 95 96 /* Latest draft text snippet for display, empty string if none exists */ 97 public static final String DRAFT_SNIPPET_TEXT = "draft_snippet_text"; 98 99 /* Draft Preview Uri, empty string if none exists */ 100 public static final String DRAFT_PREVIEW_URI = "draft_preview_uri"; 101 102 /* The preview uri's content type */ 103 public static final String DRAFT_PREVIEW_CONTENT_TYPE = "draft_preview_content_type"; 104 105 /* If this conversation is archived */ 106 public static final String ARCHIVE_STATUS = "archive_status"; 107 108 /* Timestamp for sorting purposes */ 109 public static final String SORT_TIMESTAMP = "sort_timestamp"; 110 111 /* Last read message timestamp */ 112 public static final String LAST_READ_TIMESTAMP = "last_read_timestamp"; 113 114 /* Avatar for the conversation. Could be for group of individual */ 115 public static final String ICON = "icon"; 116 117 /* Participant contact ID if this conversation has a single participant. -1 otherwise */ 118 public static final String PARTICIPANT_CONTACT_ID = "participant_contact_id"; 119 120 /* Participant lookup key if this conversation has a single participant. null otherwise */ 121 public static final String PARTICIPANT_LOOKUP_KEY = "participant_lookup_key"; 122 123 /* 124 * Participant's normalized destination if this conversation has a single participant. 125 * null otherwise. 126 */ 127 public static final String OTHER_PARTICIPANT_NORMALIZED_DESTINATION = 128 "participant_normalized_destination"; 129 130 /* Default self participant for the conversation */ 131 public static final String CURRENT_SELF_ID = "current_self_id"; 132 133 /* Participant count not including self (so will be 1 for 1:1 or bigger for group) */ 134 public static final String PARTICIPANT_COUNT = "participant_count"; 135 136 /* Should notifications be enabled for this conversation? */ 137 public static final String NOTIFICATION_ENABLED = "notification_enabled"; 138 139 /* Notification sound used for the conversation */ 140 public static final String NOTIFICATION_SOUND_URI = "notification_sound_uri"; 141 142 /* Should vibrations be enabled for the conversation's notification? */ 143 public static final String NOTIFICATION_VIBRATION = "notification_vibration"; 144 145 /* Conversation recipients include email address */ 146 public static final String INCLUDE_EMAIL_ADDRESS = "include_email_addr"; 147 148 // Record the last received sms's service center info if it indicates that the reply path 149 // is present (TP-Reply-Path), so that we could use it for the subsequent message to send. 150 // Refer to TS 23.040 D.6 and SmsMessageSender.java in Android Messaging app. 151 public static final String SMS_SERVICE_CENTER = "sms_service_center"; 152 } 153 154 // Conversation table SQL 155 private static final String CREATE_CONVERSATIONS_TABLE_SQL = 156 "CREATE TABLE " + CONVERSATIONS_TABLE + "(" 157 + ConversationColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " 158 // TODO : Int? Required not default? 159 + ConversationColumns.SMS_THREAD_ID + " INT DEFAULT(0), " 160 + ConversationColumns.NAME + " TEXT, " 161 + ConversationColumns.LATEST_MESSAGE_ID + " INT, " 162 + ConversationColumns.SNIPPET_TEXT + " TEXT, " 163 + ConversationColumns.SUBJECT_TEXT + " TEXT, " 164 + ConversationColumns.PREVIEW_URI + " TEXT, " 165 + ConversationColumns.PREVIEW_CONTENT_TYPE + " TEXT, " 166 + ConversationColumns.SHOW_DRAFT + " INT DEFAULT(0), " 167 + ConversationColumns.DRAFT_SNIPPET_TEXT + " TEXT, " 168 + ConversationColumns.DRAFT_SUBJECT_TEXT + " TEXT, " 169 + ConversationColumns.DRAFT_PREVIEW_URI + " TEXT, " 170 + ConversationColumns.DRAFT_PREVIEW_CONTENT_TYPE + " TEXT, " 171 + ConversationColumns.ARCHIVE_STATUS + " INT DEFAULT(0), " 172 + ConversationColumns.SORT_TIMESTAMP + " INT DEFAULT(0), " 173 + ConversationColumns.LAST_READ_TIMESTAMP + " INT DEFAULT(0), " 174 + ConversationColumns.ICON + " TEXT, " 175 + ConversationColumns.PARTICIPANT_CONTACT_ID + " INT DEFAULT ( " 176 + ParticipantData.PARTICIPANT_CONTACT_ID_NOT_RESOLVED + "), " 177 + ConversationColumns.PARTICIPANT_LOOKUP_KEY + " TEXT, " 178 + ConversationColumns.OTHER_PARTICIPANT_NORMALIZED_DESTINATION + " TEXT, " 179 + ConversationColumns.CURRENT_SELF_ID + " TEXT, " 180 + ConversationColumns.PARTICIPANT_COUNT + " INT DEFAULT(0), " 181 + ConversationColumns.NOTIFICATION_ENABLED + " INT DEFAULT(1), " 182 + ConversationColumns.NOTIFICATION_SOUND_URI + " TEXT, " 183 + ConversationColumns.NOTIFICATION_VIBRATION + " INT DEFAULT(1), " 184 + ConversationColumns.INCLUDE_EMAIL_ADDRESS + " INT DEFAULT(0), " 185 + ConversationColumns.SMS_SERVICE_CENTER + " TEXT " 186 + ");"; 187 188 private static final String CONVERSATIONS_TABLE_SMS_THREAD_ID_INDEX_SQL = 189 "CREATE INDEX index_" + CONVERSATIONS_TABLE + "_" + ConversationColumns.SMS_THREAD_ID 190 + " ON " + CONVERSATIONS_TABLE 191 + "(" + ConversationColumns.SMS_THREAD_ID + ")"; 192 193 private static final String CONVERSATIONS_TABLE_ARCHIVE_STATUS_INDEX_SQL = 194 "CREATE INDEX index_" + CONVERSATIONS_TABLE + "_" + ConversationColumns.ARCHIVE_STATUS 195 + " ON " + CONVERSATIONS_TABLE 196 + "(" + ConversationColumns.ARCHIVE_STATUS + ")"; 197 198 private static final String CONVERSATIONS_TABLE_SORT_TIMESTAMP_INDEX_SQL = 199 "CREATE INDEX index_" + CONVERSATIONS_TABLE + "_" + ConversationColumns.SORT_TIMESTAMP 200 + " ON " + CONVERSATIONS_TABLE 201 + "(" + ConversationColumns.SORT_TIMESTAMP + ")"; 202 203 // Messages table schema 204 public static class MessageColumns implements BaseColumns { 205 /* conversation id that this message belongs to */ 206 public static final String CONVERSATION_ID = "conversation_id"; 207 208 /* participant which send this message */ 209 public static final String SENDER_PARTICIPANT_ID = "sender_id"; 210 211 /* This is bugle's internal status for the message */ 212 public static final String STATUS = "message_status"; 213 214 /* Type of message: SMS, MMS or MMS notification */ 215 public static final String PROTOCOL = "message_protocol"; 216 217 /* This is the time that the sender sent the message */ 218 public static final String SENT_TIMESTAMP = "sent_timestamp"; 219 220 /* Time that we received the message on this device */ 221 public static final String RECEIVED_TIMESTAMP = "received_timestamp"; 222 223 /* When the message has been seen by a user in a notification */ 224 public static final String SEEN = "seen"; 225 226 /* When the message has been read by a user */ 227 public static final String READ = "read"; 228 229 /* participant representing the sim which processed this message */ 230 public static final String SELF_PARTICIPANT_ID = "self_id"; 231 232 /* 233 * Time when a retry is initiated. This is used to compute the retry window 234 * when we retry sending/downloading a message. 235 */ 236 public static final String RETRY_START_TIMESTAMP = "retry_start_timestamp"; 237 238 // Columns which map to the SMS provider 239 240 /* Message ID from the platform provider */ 241 public static final String SMS_MESSAGE_URI = "sms_message_uri"; 242 243 /* The message priority for MMS message */ 244 public static final String SMS_PRIORITY = "sms_priority"; 245 246 /* The message size for MMS message */ 247 public static final String SMS_MESSAGE_SIZE = "sms_message_size"; 248 249 /* The subject for MMS message */ 250 public static final String MMS_SUBJECT = "mms_subject"; 251 252 /* Transaction id for MMS notificaiton */ 253 public static final String MMS_TRANSACTION_ID = "mms_transaction_id"; 254 255 /* Content location for MMS notificaiton */ 256 public static final String MMS_CONTENT_LOCATION = "mms_content_location"; 257 258 /* The expiry time (ms) for MMS message */ 259 public static final String MMS_EXPIRY = "mms_expiry"; 260 261 /* The detailed status (RESPONSE_STATUS or RETRIEVE_STATUS) for MMS message */ 262 public static final String RAW_TELEPHONY_STATUS = "raw_status"; 263 } 264 265 // Messages table SQL 266 private static final String CREATE_MESSAGES_TABLE_SQL = 267 "CREATE TABLE " + MESSAGES_TABLE + " (" 268 + MessageColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " 269 + MessageColumns.CONVERSATION_ID + " INT, " 270 + MessageColumns.SENDER_PARTICIPANT_ID + " INT, " 271 + MessageColumns.SENT_TIMESTAMP + " INT DEFAULT(0), " 272 + MessageColumns.RECEIVED_TIMESTAMP + " INT DEFAULT(0), " 273 + MessageColumns.PROTOCOL + " INT DEFAULT(0), " 274 + MessageColumns.STATUS + " INT DEFAULT(0), " 275 + MessageColumns.SEEN + " INT DEFAULT(0), " 276 + MessageColumns.READ + " INT DEFAULT(0), " 277 + MessageColumns.SMS_MESSAGE_URI + " TEXT, " 278 + MessageColumns.SMS_PRIORITY + " INT DEFAULT(0), " 279 + MessageColumns.SMS_MESSAGE_SIZE + " INT DEFAULT(0), " 280 + MessageColumns.MMS_SUBJECT + " TEXT, " 281 + MessageColumns.MMS_TRANSACTION_ID + " TEXT, " 282 + MessageColumns.MMS_CONTENT_LOCATION + " TEXT, " 283 + MessageColumns.MMS_EXPIRY + " INT DEFAULT(0), " 284 + MessageColumns.RAW_TELEPHONY_STATUS + " INT DEFAULT(0), " 285 + MessageColumns.SELF_PARTICIPANT_ID + " INT, " 286 + MessageColumns.RETRY_START_TIMESTAMP + " INT DEFAULT(0), " 287 + "FOREIGN KEY (" + MessageColumns.CONVERSATION_ID + ") REFERENCES " 288 + CONVERSATIONS_TABLE + "(" + ConversationColumns._ID + ") ON DELETE CASCADE " 289 + "FOREIGN KEY (" + MessageColumns.SENDER_PARTICIPANT_ID + ") REFERENCES " 290 + PARTICIPANTS_TABLE + "(" + ParticipantColumns._ID + ") ON DELETE SET NULL " 291 + "FOREIGN KEY (" + MessageColumns.SELF_PARTICIPANT_ID + ") REFERENCES " 292 + PARTICIPANTS_TABLE + "(" + ParticipantColumns._ID + ") ON DELETE SET NULL " 293 + ");"; 294 295 // Primary sort index for messages table : by conversation id, status, received timestamp. 296 private static final String MESSAGES_TABLE_SORT_INDEX_SQL = 297 "CREATE INDEX index_" + MESSAGES_TABLE + "_sort ON " + MESSAGES_TABLE + "(" 298 + MessageColumns.CONVERSATION_ID + ", " 299 + MessageColumns.STATUS + ", " 300 + MessageColumns.RECEIVED_TIMESTAMP + ")"; 301 302 private static final String MESSAGES_TABLE_STATUS_SEEN_INDEX_SQL = 303 "CREATE INDEX index_" + MESSAGES_TABLE + "_status_seen ON " + MESSAGES_TABLE + "(" 304 + MessageColumns.STATUS + ", " 305 + MessageColumns.SEEN + ")"; 306 307 // Parts table schema 308 // A part may contain text or a media url, but not both. 309 public static class PartColumns implements BaseColumns { 310 /* message id that this part belongs to */ 311 public static final String MESSAGE_ID = "message_id"; 312 313 /* conversation id that this part belongs to */ 314 public static final String CONVERSATION_ID = "conversation_id"; 315 316 /* text for this part */ 317 public static final String TEXT = "text"; 318 319 /* content uri for this part */ 320 public static final String CONTENT_URI = "uri"; 321 322 /* content type for this part */ 323 public static final String CONTENT_TYPE = "content_type"; 324 325 /* cached width for this part (for layout while loading) */ 326 public static final String WIDTH = "width"; 327 328 /* cached height for this part (for layout while loading) */ 329 public static final String HEIGHT = "height"; 330 331 /* de-normalized copy of timestamp from the messages table. This is populated 332 * via an insert trigger on the parts table. 333 */ 334 public static final String TIMESTAMP = "timestamp"; 335 } 336 337 // Message part table SQL 338 private static final String CREATE_PARTS_TABLE_SQL = 339 "CREATE TABLE " + PARTS_TABLE + "(" 340 + PartColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," 341 + PartColumns.MESSAGE_ID + " INT," 342 + PartColumns.TEXT + " TEXT," 343 + PartColumns.CONTENT_URI + " TEXT," 344 + PartColumns.CONTENT_TYPE + " TEXT," 345 + PartColumns.WIDTH + " INT DEFAULT(" 346 + MessagingContentProvider.UNSPECIFIED_SIZE + ")," 347 + PartColumns.HEIGHT + " INT DEFAULT(" 348 + MessagingContentProvider.UNSPECIFIED_SIZE + ")," 349 + PartColumns.TIMESTAMP + " INT, " 350 + PartColumns.CONVERSATION_ID + " INT NOT NULL," 351 + "FOREIGN KEY (" + PartColumns.MESSAGE_ID + ") REFERENCES " 352 + MESSAGES_TABLE + "(" + MessageColumns._ID + ") ON DELETE CASCADE " 353 + "FOREIGN KEY (" + PartColumns.CONVERSATION_ID + ") REFERENCES " 354 + CONVERSATIONS_TABLE + "(" + ConversationColumns._ID + ") ON DELETE CASCADE " 355 + ");"; 356 357 public static final String CREATE_PARTS_TRIGGER_SQL = 358 "CREATE TRIGGER " + PARTS_TABLE + "_TRIGGER" + " AFTER INSERT ON " + PARTS_TABLE 359 + " FOR EACH ROW " 360 + " BEGIN UPDATE " + PARTS_TABLE 361 + " SET " + PartColumns.TIMESTAMP + "=" 362 + " (SELECT received_timestamp FROM " + MESSAGES_TABLE + " WHERE " + MESSAGES_TABLE 363 + "." + MessageColumns._ID + "=" + "NEW." + PartColumns.MESSAGE_ID + ")" 364 + " WHERE " + PARTS_TABLE + "." + PartColumns._ID + "=" + "NEW." + PartColumns._ID 365 + "; END"; 366 367 public static final String CREATE_MESSAGES_TRIGGER_SQL = 368 "CREATE TRIGGER " + MESSAGES_TABLE + "_TRIGGER" + " AFTER UPDATE OF " 369 + MessageColumns.RECEIVED_TIMESTAMP + " ON " + MESSAGES_TABLE 370 + " FOR EACH ROW BEGIN UPDATE " + PARTS_TABLE + " SET " + PartColumns.TIMESTAMP 371 + " = NEW." + MessageColumns.RECEIVED_TIMESTAMP + " WHERE " + PARTS_TABLE + "." 372 + PartColumns.MESSAGE_ID + " = NEW." + MessageColumns._ID 373 + "; END;"; 374 375 // Primary sort index for parts table : by message_id 376 private static final String PARTS_TABLE_MESSAGE_INDEX_SQL = 377 "CREATE INDEX index_" + PARTS_TABLE + "_message_id ON " + PARTS_TABLE + "(" 378 + PartColumns.MESSAGE_ID + ")"; 379 380 // Participants table schema 381 public static class ParticipantColumns implements BaseColumns { 382 /* The subscription id for the sim associated with this self participant. 383 * Introduced in L. For earlier versions will always be default_sub_id (-1). 384 * For multi sim devices (or cases where the sim was changed) single device 385 * may have several different sub_id values */ 386 public static final String SUB_ID = "sub_id"; 387 388 /* The slot of the active SIM (inserted in the device) for this self-participant. If the 389 * self-participant doesn't correspond to any active SIM, this will be 390 * {@link android.telephony.SubscriptionManager#INVALID_SLOT_ID}. 391 * The column is ignored for all non-self participants. 392 */ 393 public static final String SIM_SLOT_ID = "sim_slot_id"; 394 395 /* The phone number stored in a standard E164 format if possible. This is unique for a 396 * given participant. We can't handle multiple participants with the same phone number 397 * since we don't know which of them a message comes from. This can also be an email 398 * address, in which case this is the same as the displayed address */ 399 public static final String NORMALIZED_DESTINATION = "normalized_destination"; 400 401 /* The phone number as originally supplied and used for dialing. Not necessarily in E164 402 * format or unique */ 403 public static final String SEND_DESTINATION = "send_destination"; 404 405 /* The user-friendly formatting of the phone number according to the region setting of 406 * the device when the row was added. */ 407 public static final String DISPLAY_DESTINATION = "display_destination"; 408 409 /* A string with this participant's full name or a pretty printed phone number */ 410 public static final String FULL_NAME = "full_name"; 411 412 /* A string with just this participant's first name */ 413 public static final String FIRST_NAME = "first_name"; 414 415 /* A local URI to an asset for the icon for this participant */ 416 public static final String PROFILE_PHOTO_URI = "profile_photo_uri"; 417 418 /* Contact id for matching local contact for this participant */ 419 public static final String CONTACT_ID = "contact_id"; 420 421 /* String that contains hints on how to find contact information in a contact lookup */ 422 public static final String LOOKUP_KEY = "lookup_key"; 423 424 /* If this participant is blocked */ 425 public static final String BLOCKED = "blocked"; 426 427 /* The color of the subscription (FOR SELF PARTICIPANTS ONLY) */ 428 public static final String SUBSCRIPTION_COLOR = "subscription_color"; 429 430 /* The name of the subscription (FOR SELF PARTICIPANTS ONLY) */ 431 public static final String SUBSCRIPTION_NAME = "subscription_name"; 432 433 /* The exact destination stored in Contacts for this participant */ 434 public static final String CONTACT_DESTINATION = "contact_destination"; 435 } 436 437 // Participants table SQL 438 private static final String CREATE_PARTICIPANTS_TABLE_SQL = 439 "CREATE TABLE " + PARTICIPANTS_TABLE + "(" 440 + ParticipantColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," 441 + ParticipantColumns.SUB_ID + " INT DEFAULT(" 442 + ParticipantData.OTHER_THAN_SELF_SUB_ID + ")," 443 + ParticipantColumns.SIM_SLOT_ID + " INT DEFAULT(" 444 + ParticipantData.INVALID_SLOT_ID + ")," 445 + ParticipantColumns.NORMALIZED_DESTINATION + " TEXT," 446 + ParticipantColumns.SEND_DESTINATION + " TEXT," 447 + ParticipantColumns.DISPLAY_DESTINATION + " TEXT," 448 + ParticipantColumns.FULL_NAME + " TEXT," 449 + ParticipantColumns.FIRST_NAME + " TEXT," 450 + ParticipantColumns.PROFILE_PHOTO_URI + " TEXT, " 451 + ParticipantColumns.CONTACT_ID + " INT DEFAULT( " 452 + ParticipantData.PARTICIPANT_CONTACT_ID_NOT_RESOLVED + "), " 453 + ParticipantColumns.LOOKUP_KEY + " STRING, " 454 + ParticipantColumns.BLOCKED + " INT DEFAULT(0), " 455 + ParticipantColumns.SUBSCRIPTION_NAME + " TEXT, " 456 + ParticipantColumns.SUBSCRIPTION_COLOR + " INT DEFAULT(0), " 457 + ParticipantColumns.CONTACT_DESTINATION + " TEXT, " 458 + "UNIQUE (" + ParticipantColumns.NORMALIZED_DESTINATION + ", " 459 + ParticipantColumns.SUB_ID + ") ON CONFLICT FAIL" + ");"; 460 461 private static final String CREATE_SELF_PARTICIPANT_SQL = 462 "INSERT INTO " + PARTICIPANTS_TABLE 463 + " ( " + ParticipantColumns.SUB_ID + " ) VALUES ( %s )"; 464 getCreateSelfParticipantSql(int subId)465 static String getCreateSelfParticipantSql(int subId) { 466 return String.format(CREATE_SELF_PARTICIPANT_SQL, subId); 467 } 468 469 // Conversation Participants table schema - contains a list of participants excluding the user 470 // in a given conversation. 471 public static class ConversationParticipantsColumns implements BaseColumns { 472 /* participant id of someone in this conversation */ 473 public static final String PARTICIPANT_ID = "participant_id"; 474 475 /* conversation id that this participant belongs to */ 476 public static final String CONVERSATION_ID = "conversation_id"; 477 } 478 479 // Conversation Participants table SQL 480 private static final String CREATE_CONVERSATION_PARTICIPANTS_TABLE_SQL = 481 "CREATE TABLE " + CONVERSATION_PARTICIPANTS_TABLE + "(" 482 + ConversationParticipantsColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," 483 + ConversationParticipantsColumns.CONVERSATION_ID + " INT," 484 + ConversationParticipantsColumns.PARTICIPANT_ID + " INT," 485 + "UNIQUE (" + ConversationParticipantsColumns.CONVERSATION_ID + "," 486 + ConversationParticipantsColumns.PARTICIPANT_ID + ") ON CONFLICT FAIL, " 487 + "FOREIGN KEY (" + ConversationParticipantsColumns.CONVERSATION_ID + ") " 488 + "REFERENCES " + CONVERSATIONS_TABLE + "(" + ConversationColumns._ID + ")" 489 + " ON DELETE CASCADE " 490 + "FOREIGN KEY (" + ConversationParticipantsColumns.PARTICIPANT_ID + ")" 491 + " REFERENCES " + PARTICIPANTS_TABLE + "(" + ParticipantColumns._ID + "));"; 492 493 // Primary access pattern for conversation participants is to look them up for a specific 494 // conversation. 495 private static final String CONVERSATION_PARTICIPANTS_TABLE_CONVERSATION_ID_INDEX_SQL = 496 "CREATE INDEX index_" + CONVERSATION_PARTICIPANTS_TABLE + "_" 497 + ConversationParticipantsColumns.CONVERSATION_ID 498 + " ON " + CONVERSATION_PARTICIPANTS_TABLE 499 + "(" + ConversationParticipantsColumns.CONVERSATION_ID + ")"; 500 501 // View for getting parts which are for draft messages. 502 static final String DRAFT_PARTS_VIEW_SQL = "CREATE VIEW " + 503 DRAFT_PARTS_VIEW + " AS SELECT " 504 + PARTS_TABLE + '.' + PartColumns._ID 505 + " as " + PartColumns._ID + ", " 506 + PARTS_TABLE + '.' + PartColumns.MESSAGE_ID 507 + " as " + PartColumns.MESSAGE_ID + ", " 508 + PARTS_TABLE + '.' + PartColumns.TEXT 509 + " as " + PartColumns.TEXT + ", " 510 + PARTS_TABLE + '.' + PartColumns.CONTENT_URI 511 + " as " + PartColumns.CONTENT_URI + ", " 512 + PARTS_TABLE + '.' + PartColumns.CONTENT_TYPE 513 + " as " + PartColumns.CONTENT_TYPE + ", " 514 + PARTS_TABLE + '.' + PartColumns.WIDTH 515 + " as " + PartColumns.WIDTH + ", " 516 + PARTS_TABLE + '.' + PartColumns.HEIGHT 517 + " as " + PartColumns.HEIGHT + ", " 518 + MESSAGES_TABLE + '.' + MessageColumns.CONVERSATION_ID 519 + " as " + MessageColumns.CONVERSATION_ID + " " 520 + " FROM " + MESSAGES_TABLE + " LEFT JOIN " + PARTS_TABLE + " ON (" 521 + MESSAGES_TABLE + "." + MessageColumns._ID 522 + "=" + PARTS_TABLE + "." + PartColumns.MESSAGE_ID + ")" 523 // Exclude draft messages from main view 524 + " WHERE " + MESSAGES_TABLE + "." + MessageColumns.STATUS 525 + " = " + MessageData.BUGLE_STATUS_OUTGOING_DRAFT; 526 527 // List of all our SQL tables 528 private static final String[] CREATE_TABLE_SQLS = new String[] { 529 CREATE_CONVERSATIONS_TABLE_SQL, 530 CREATE_MESSAGES_TABLE_SQL, 531 CREATE_PARTS_TABLE_SQL, 532 CREATE_PARTICIPANTS_TABLE_SQL, 533 CREATE_CONVERSATION_PARTICIPANTS_TABLE_SQL, 534 }; 535 536 // List of all our indices 537 private static final String[] CREATE_INDEX_SQLS = new String[] { 538 CONVERSATIONS_TABLE_SMS_THREAD_ID_INDEX_SQL, 539 CONVERSATIONS_TABLE_ARCHIVE_STATUS_INDEX_SQL, 540 CONVERSATIONS_TABLE_SORT_TIMESTAMP_INDEX_SQL, 541 MESSAGES_TABLE_SORT_INDEX_SQL, 542 MESSAGES_TABLE_STATUS_SEEN_INDEX_SQL, 543 PARTS_TABLE_MESSAGE_INDEX_SQL, 544 CONVERSATION_PARTICIPANTS_TABLE_CONVERSATION_ID_INDEX_SQL, 545 }; 546 547 // List of all our SQL triggers 548 private static final String[] CREATE_TRIGGER_SQLS = new String[] { 549 CREATE_PARTS_TRIGGER_SQL, 550 CREATE_MESSAGES_TRIGGER_SQL, 551 }; 552 553 // List of all our views 554 private static final String[] CREATE_VIEW_SQLS = new String[] { 555 ConversationListItemData.getConversationListViewSql(), 556 ConversationImagePartsView.getCreateSql(), 557 DRAFT_PARTS_VIEW_SQL, 558 }; 559 560 private static final Object sLock = new Object(); 561 private final Context mApplicationContext; 562 private static DatabaseHelper sHelperInstance; // Protected by sLock. 563 564 private final Object mDatabaseWrapperLock = new Object(); 565 private DatabaseWrapper mDatabaseWrapper; // Protected by mDatabaseWrapperLock. 566 private final DatabaseUpgradeHelper mUpgradeHelper = new DatabaseUpgradeHelper(); 567 568 /** 569 * Get a (singleton) instance of {@link DatabaseHelper}, creating one if there isn't one yet. 570 * This is the only public method for getting a new instance of the class. 571 * @param context Should be the application context (or something that will live for the 572 * lifetime of the application). 573 * @return The current (or a new) DatabaseHelper instance. 574 */ getInstance(final Context context)575 public static DatabaseHelper getInstance(final Context context) { 576 synchronized (sLock) { 577 if (sHelperInstance == null) { 578 sHelperInstance = new DatabaseHelper(context); 579 } 580 return sHelperInstance; 581 } 582 } 583 584 /** 585 * Private constructor, used from {@link #getInstance()}. 586 * @param context Should be the application context (or something that will live for the 587 * lifetime of the application). 588 */ DatabaseHelper(final Context context)589 private DatabaseHelper(final Context context) { 590 super(context, DATABASE_NAME, null, getDatabaseVersion(context), null); 591 mApplicationContext = context; 592 } 593 594 /** 595 * Test method that always instantiates a new DatabaseHelper instance. This should 596 * be used ONLY by the tests and never by the real application. 597 * @param context Test context. 598 * @return Brand new DatabaseHelper instance. 599 */ 600 @VisibleForTesting getNewInstanceForTest(final Context context)601 static DatabaseHelper getNewInstanceForTest(final Context context) { 602 Assert.isEngBuild(); 603 Assert.isTrue(BugleApplication.isRunningTests()); 604 return new DatabaseHelper(context); 605 } 606 607 /** 608 * Get the (singleton) instance of @{link DatabaseWrapper}. 609 * <p>The database is always opened as a writeable database. 610 * @return The current (or a new) DatabaseWrapper instance. 611 */ 612 @DoesNotRunOnMainThread getDatabase()613 DatabaseWrapper getDatabase() { 614 // We prevent the main UI thread from accessing the database here since we have to allow 615 // public access to this class to enable sub-packages to access data. 616 Assert.isNotMainThread(); 617 618 synchronized (mDatabaseWrapperLock) { 619 if (mDatabaseWrapper == null) { 620 mDatabaseWrapper = new DatabaseWrapper(mApplicationContext, getWritableDatabase()); 621 } 622 return mDatabaseWrapper; 623 } 624 } 625 626 @Override onDowngrade(final SQLiteDatabase db, final int oldVersion, final int newVersion)627 public void onDowngrade(final SQLiteDatabase db, final int oldVersion, final int newVersion) { 628 mUpgradeHelper.onDowngrade(db, oldVersion, newVersion); 629 } 630 631 /** 632 * Drops and recreates all tables. 633 */ rebuildTables(final SQLiteDatabase db)634 public static void rebuildTables(final SQLiteDatabase db) { 635 // Drop tables first, then views, and indices. 636 dropAllTables(db); 637 dropAllViews(db); 638 dropAllIndexes(db); 639 dropAllTriggers(db); 640 641 // Recreate the whole database. 642 createDatabase(db); 643 } 644 645 /** 646 * Drop and rebuild a given view. 647 */ rebuildView(final SQLiteDatabase db, final String viewName, final String createViewSql)648 static void rebuildView(final SQLiteDatabase db, final String viewName, 649 final String createViewSql) { 650 dropView(db, viewName, true /* throwOnFailure */); 651 db.execSQL(createViewSql); 652 } 653 dropView(final SQLiteDatabase db, final String viewName, final boolean throwOnFailure)654 private static void dropView(final SQLiteDatabase db, final String viewName, 655 final boolean throwOnFailure) { 656 final String dropPrefix = "DROP VIEW IF EXISTS "; 657 try { 658 db.execSQL(dropPrefix + viewName); 659 } catch (final SQLException ex) { 660 if (LogUtil.isLoggable(LogUtil.BUGLE_TAG, LogUtil.DEBUG)) { 661 LogUtil.d(LogUtil.BUGLE_TAG, "unable to drop view " + viewName + " " 662 + ex); 663 } 664 665 if (throwOnFailure) { 666 throw ex; 667 } 668 } 669 } 670 671 /** 672 * Drops all user-defined tables from the given database. 673 */ dropAllTables(final SQLiteDatabase db)674 private static void dropAllTables(final SQLiteDatabase db) { 675 final Cursor tableCursor = 676 db.query(MASTER_TABLE, MASTER_COLUMNS, "type='table'", null, null, null, null); 677 if (tableCursor != null) { 678 try { 679 final String dropPrefix = "DROP TABLE IF EXISTS "; 680 while (tableCursor.moveToNext()) { 681 final String tableName = tableCursor.getString(0); 682 683 // Skip special tables 684 if (tableName.startsWith("android_") || tableName.startsWith("sqlite_")) { 685 continue; 686 } 687 try { 688 db.execSQL(dropPrefix + tableName); 689 } catch (final SQLException ex) { 690 if (LogUtil.isLoggable(LogUtil.BUGLE_TAG, LogUtil.DEBUG)) { 691 LogUtil.d(LogUtil.BUGLE_TAG, "unable to drop table " + tableName + " " 692 + ex); 693 } 694 } 695 } 696 } finally { 697 tableCursor.close(); 698 } 699 } 700 } 701 702 /** 703 * Drops all user-defined triggers from the given database. 704 */ dropAllTriggers(final SQLiteDatabase db)705 private static void dropAllTriggers(final SQLiteDatabase db) { 706 final Cursor triggerCursor = 707 db.query(MASTER_TABLE, MASTER_COLUMNS, "type='trigger'", null, null, null, null); 708 if (triggerCursor != null) { 709 try { 710 final String dropPrefix = "DROP TRIGGER IF EXISTS "; 711 while (triggerCursor.moveToNext()) { 712 final String triggerName = triggerCursor.getString(0); 713 714 // Skip special tables 715 if (triggerName.startsWith("android_") || triggerName.startsWith("sqlite_")) { 716 continue; 717 } 718 try { 719 db.execSQL(dropPrefix + triggerName); 720 } catch (final SQLException ex) { 721 if (LogUtil.isLoggable(LogUtil.BUGLE_TAG, LogUtil.DEBUG)) { 722 LogUtil.d(LogUtil.BUGLE_TAG, "unable to drop trigger " + triggerName + 723 " " + ex); 724 } 725 } 726 } 727 } finally { 728 triggerCursor.close(); 729 } 730 } 731 } 732 733 /** 734 * Drops all user-defined views from the given database. 735 */ dropAllViews(final SQLiteDatabase db)736 private static void dropAllViews(final SQLiteDatabase db) { 737 final Cursor viewCursor = 738 db.query(MASTER_TABLE, MASTER_COLUMNS, "type='view'", null, null, null, null); 739 if (viewCursor != null) { 740 try { 741 while (viewCursor.moveToNext()) { 742 final String viewName = viewCursor.getString(0); 743 dropView(db, viewName, false /* throwOnFailure */); 744 } 745 } finally { 746 viewCursor.close(); 747 } 748 } 749 } 750 751 /** 752 * Drops all user-defined views from the given database. 753 */ dropAllIndexes(final SQLiteDatabase db)754 private static void dropAllIndexes(final SQLiteDatabase db) { 755 final Cursor indexCursor = 756 db.query(MASTER_TABLE, MASTER_COLUMNS, "type='index'", null, null, null, null); 757 if (indexCursor != null) { 758 try { 759 final String dropPrefix = "DROP INDEX IF EXISTS "; 760 while (indexCursor.moveToNext()) { 761 final String indexName = indexCursor.getString(0); 762 try { 763 db.execSQL(dropPrefix + indexName); 764 } catch (final SQLException ex) { 765 if (LogUtil.isLoggable(LogUtil.BUGLE_TAG, LogUtil.DEBUG)) { 766 LogUtil.d(LogUtil.BUGLE_TAG, "unable to drop index " + indexName + " " 767 + ex); 768 } 769 } 770 } 771 } finally { 772 indexCursor.close(); 773 } 774 } 775 } 776 createDatabase(final SQLiteDatabase db)777 private static void createDatabase(final SQLiteDatabase db) { 778 for (final String sql : CREATE_TABLE_SQLS) { 779 db.execSQL(sql); 780 } 781 782 for (final String sql : CREATE_INDEX_SQLS) { 783 db.execSQL(sql); 784 } 785 786 for (final String sql : CREATE_VIEW_SQLS) { 787 db.execSQL(sql); 788 } 789 790 for (final String sql : CREATE_TRIGGER_SQLS) { 791 db.execSQL(sql); 792 } 793 794 // Enable foreign key constraints 795 db.execSQL("PRAGMA foreign_keys=ON;"); 796 797 // Add the default self participant. The default self will be assigned a proper slot id 798 // during participant refresh. 799 db.execSQL(getCreateSelfParticipantSql(ParticipantData.DEFAULT_SELF_SUB_ID)); 800 801 DataModel.get().onCreateTables(db); 802 } 803 804 @Override onCreate(SQLiteDatabase db)805 public void onCreate(SQLiteDatabase db) { 806 createDatabase(db); 807 } 808 809 @Override onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)810 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 811 mUpgradeHelper.doOnUpgrade(db, oldVersion, newVersion); 812 } 813 } 814