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