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