From 132b27adeef3ab4d305facda7dd035015b00766f Mon Sep 17 00:00:00 2001 From: steckbrief Date: Sat, 5 May 2018 20:28:04 +0200 Subject: introduces new message state model --- .../db/access/MessageDatabaseAccess.java | 143 ++++++++++++++++++++- 1 file changed, 141 insertions(+), 2 deletions(-) (limited to 'src/main/java/de/thedevstack/conversationsplus/persistance/db/access/MessageDatabaseAccess.java') diff --git a/src/main/java/de/thedevstack/conversationsplus/persistance/db/access/MessageDatabaseAccess.java b/src/main/java/de/thedevstack/conversationsplus/persistance/db/access/MessageDatabaseAccess.java index 43ae6ebb..cfef7a53 100644 --- a/src/main/java/de/thedevstack/conversationsplus/persistance/db/access/MessageDatabaseAccess.java +++ b/src/main/java/de/thedevstack/conversationsplus/persistance/db/access/MessageDatabaseAccess.java @@ -3,18 +3,25 @@ package de.thedevstack.conversationsplus.persistance.db.access; import android.content.ContentValues; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; +import android.util.Pair; import de.thedevstack.android.logcat.Logging; +import de.thedevstack.conversationsplus.entities.Account; import de.thedevstack.conversationsplus.entities.FileParams; import de.thedevstack.conversationsplus.entities.Message; import de.thedevstack.conversationsplus.enums.FileStatus; +import de.thedevstack.conversationsplus.enums.MessageDirection; +import de.thedevstack.conversationsplus.enums.MessageStatus; import de.thedevstack.conversationsplus.persistance.db.migrations.FileParamsBodyToDatabaseFieldsMigration; +import de.thedevstack.conversationsplus.persistance.db.migrations.MessageStatusMigration; import de.thedevstack.conversationsplus.utils.CryptoHelper; /** * */ public class MessageDatabaseAccess extends AbstractDatabaseAccess { + + private static final String TABLE_NAME_MESSAGES = "messages"; // since cplus db version 1 public static final String TABLE_NAME_ADDITIONAL_PARAMETERS = "message_parameters"; private static final String COLUMN_NAME_MSG_PARAMS_HTTPUPLOAD = "httpupload"; @@ -32,6 +39,9 @@ public class MessageDatabaseAccess extends AbstractDatabaseAccess { private static final String COLUMN_NAME_MSG_PARAMS_FILE_IV = "file_iv"; private static final String COLUMN_NAME_MSG_PARAMS_FILE_KEY = "file_key"; private static final String COLUMN_NAME_MSG_PARAMS_ORIGINAL_FILE_NAME = "original_file_name"; + // since cplus db version 4 + private static final String COLUMN_NAME_MSG_STATUS = "status"; + private static final String COLUMN_NAME_MSG_DIRECTION = "direction"; private static final String TABLE_ADDITIONAL_PARAMETERS_CREATE_V1 = "CREATE TABLE " + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS + " (" + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID + " TEXT, " @@ -39,7 +49,24 @@ public class MessageDatabaseAccess extends AbstractDatabaseAccess { + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_TREATASDOWNLOADABLE_DECISION + " TEXT DEFAULT 'NOT_DECIDED', " + "FOREIGN KEY(" + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID + ") REFERENCES " + Message.TABLENAME + "(" + Message.UUID + ") ON DELETE CASCADE)"; - private static final String TABLE_ADDITIONAL_PARAMETERS_CREATE = "CREATE TABLE " + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS + " (" + private static final String TABLE_ADDITIONAL_PARAMETERS_CREATE_V3 = "CREATE TABLE " + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS + " (" + + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID + " TEXT, " + + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_HTTPUPLOAD + " INTEGER DEFAULT 0, " + + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_TREATASDOWNLOADABLE_DECISION + " TEXT DEFAULT 'NOT_DECIDED', " + + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_TYPE + " TEXT DEFAULT NULL, " + + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_SIZE + " INTEGER DEFAULT 0, " + + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_HEIGHT + " INTEGER DEFAULT 0, " + + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_WIDTH + " INTEGER DEFAULT 0, " + + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_STATUS + " TEXT DEFAULT 'UNDEFINED', " + + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_NAME + " TEXT DEFAULT NULL, " + + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_ORIGINAL_FILE_NAME + " TEXT DEFAULT NULL, " + + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_PATH + " TEXT DEFAULT NULL, " + + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_URL + " TEXT DEFAULT NULL, " + + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_IV + " TEXT DEFAULT NULL, " + + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_KEY + " TEXT DEFAULT NULL, " + + "FOREIGN KEY(" + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID + ") REFERENCES " + Message.TABLENAME + "(" + Message.UUID + ") ON DELETE CASCADE)"; + + private static final String TABLE_ADDITIONAL_PARAMETERS_CREATE_V4 = "CREATE TABLE " + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS + " (" + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID + " TEXT, " + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_HTTPUPLOAD + " INTEGER DEFAULT 0, " + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_TREATASDOWNLOADABLE_DECISION + " TEXT DEFAULT 'NOT_DECIDED', " @@ -54,6 +81,8 @@ public class MessageDatabaseAccess extends AbstractDatabaseAccess { + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_URL + " TEXT DEFAULT NULL, " + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_IV + " TEXT DEFAULT NULL, " + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_KEY + " TEXT DEFAULT NULL, " + + MessageDatabaseAccess.COLUMN_NAME_MSG_STATUS + " TEXT DEFAULT 'WAITING', " + + MessageDatabaseAccess.COLUMN_NAME_MSG_DIRECTION + " TEXT DEFAULT 'OUT', " + "FOREIGN KEY(" + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID + ") REFERENCES " + Message.TABLENAME + "(" + Message.UUID + ") ON DELETE CASCADE)"; public static void updateMessageParameters(SQLiteDatabase db, Message message, String uuid) { @@ -66,6 +95,8 @@ public class MessageDatabaseAccess extends AbstractDatabaseAccess { additionalParameters.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID, message.getUuid()); additionalParameters.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_HTTPUPLOAD, message.isHttpUploaded() ? 1 : 0); additionalParameters.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_TREATASDOWNLOADABLE_DECISION, message.treatAsDownloadable().name()); + additionalParameters.put(MessageDatabaseAccess.COLUMN_NAME_MSG_DIRECTION, message.getDirection().name()); + additionalParameters.put(MessageDatabaseAccess.COLUMN_NAME_MSG_STATUS, message.getMessageStatus().name()); if (null != message.getFileParams()) { FileParams fileParams = message.getFileParams(); additionalParameters.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_TYPE, fileParams.getMimeType()); @@ -104,6 +135,28 @@ public class MessageDatabaseAccess extends AbstractDatabaseAccess { message.setTreatAsDownloadable(treatAsDownloadable); + String msgStatus = CursorHelper.getString(cursor, COLUMN_NAME_MSG_STATUS); + MessageStatus messageStatus = MessageStatus.WAITING; + try { + messageStatus = MessageStatus.valueOf(msgStatus); + } catch (IllegalArgumentException e) { + // Should only happen if the database is corrupted, but to be on the save side catch it here + Logging.e("db.msg", "Unknown MessageStatus found: '" + msgStatus + "'"); + } + + message.setMessageStatus(messageStatus); + + String msgDirection = CursorHelper.getString(cursor, COLUMN_NAME_MSG_DIRECTION); + MessageDirection messageDirection = MessageDirection.OUT; + try { + messageDirection = MessageDirection.valueOf(msgDirection); + } catch (IllegalArgumentException e) { + // Should only happen if the database is corrupted, but to be on the save side catch it here + Logging.e("db.msg", "Unknown MessageDirection found: '" + msgDirection + "'"); + } + + message.setDirection(messageDirection); + String status = CursorHelper.getString(cursor, COLUMN_NAME_MSG_PARAMS_FILE_STATUS); FileStatus fileStatus = FileStatus.UNDEFINED; if (null != status && !status.isEmpty()) { @@ -209,10 +262,96 @@ public class MessageDatabaseAccess extends AbstractDatabaseAccess { } cursor.close(); } + if (oldVersion < 4 && newVersion >= 4) { + Logging.d("db.upgrade.cplus", "Upgrade " + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS + ": new fields for status and direction"); + String[] columnDefinitions = { + MessageDatabaseAccess.COLUMN_NAME_MSG_STATUS + " TEXT DEFAULT 'WAITING'", + MessageDatabaseAccess.COLUMN_NAME_MSG_DIRECTION + " TEXT DEFAULT 'OUT'" + }; + + addNewColumns(db, MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS, columnDefinitions); + + Logging.d("db.upgrade.cplus", "Migrate integer status from " + Message.TABLENAME + "." + Message.STATUS + " to dedicated status and direction field in " + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS); + Cursor cursor = db.rawQuery("SELECT " + Message.UUID + ", " + Message.STATUS + " FROM " + Message.TABLENAME, null); + while (cursor.moveToNext()) { + String uuid = CursorHelper.getString(cursor, Message.UUID); + int status = CursorHelper.getInt(cursor, Message.STATUS); + + MessageDirection direction = MessageStatusMigration.getMessageDirection(status); + MessageStatus newStatus = MessageStatusMigration.getMessageStatus(status); + + ContentValues parameterValues = new ContentValues(); + parameterValues.put(MessageDatabaseAccess.COLUMN_NAME_MSG_STATUS, newStatus.name()); + parameterValues.put(MessageDatabaseAccess.COLUMN_NAME_MSG_DIRECTION, direction.name()); + + db.update(MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS, parameterValues, MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID + "=?", new String[] {uuid}); + } + cursor.close(); + } + if (oldVersion < 5 && newVersion >= 5) { // ONLY NEEDED FOR DEV + Logging.d("db.upgrade.cplus", "Fixing hick up in first try"); + String alterStatement = "ALTER TABLE " + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS + " RENAME TO old_msg_params"; + db.execSQL(alterStatement); + + db.execSQL(MessageDatabaseAccess.TABLE_ADDITIONAL_PARAMETERS_CREATE_V4); + + db.execSQL("INSERT INTO " + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS + " SELECT message_uuid, httpupload, treatasdownloadable_decision," + + "file_type, file_size, file_height, file_width, file_status, file_name, original_file_name, file_path, file_url, file_iv, file_key, status, 'IN' FROM old_msg_params"); + + db.execSQL("DROP TABLE old_msg_params"); + + Logging.d("db.upgrade.cplus", "Migrate integer status from " + Message.TABLENAME + "." + Message.STATUS + " to dedicated status and direction field in " + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS); + Cursor cursor = db.rawQuery("SELECT " + Message.UUID + ", " + Message.STATUS + " FROM " + Message.TABLENAME, null); + while (cursor.moveToNext()) { + String uuid = CursorHelper.getString(cursor, Message.UUID); + int status = CursorHelper.getInt(cursor, Message.STATUS); + + MessageDirection direction = MessageStatusMigration.getMessageDirection(status); + MessageStatus newStatus = MessageStatusMigration.getMessageStatus(status); + + ContentValues parameterValues = new ContentValues(); + parameterValues.put(MessageDatabaseAccess.COLUMN_NAME_MSG_STATUS, newStatus.name()); + parameterValues.put(MessageDatabaseAccess.COLUMN_NAME_MSG_DIRECTION, direction.name()); + + db.update(MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS, parameterValues, MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID + "=?", new String[] {uuid}); + } + cursor.close(); + } + if (oldVersion < 6 && newVersion >= 6) { + + } } public static void create(SQLiteDatabase db) { // Create Conversations+ related tables - db.execSQL(MessageDatabaseAccess.TABLE_ADDITIONAL_PARAMETERS_CREATE); + db.execSQL(MessageDatabaseAccess.TABLE_ADDITIONAL_PARAMETERS_CREATE_V4); + } + + public static Pair getLastMessageReceived(SQLiteDatabase db, String accountUuid) { + Cursor cursor = null; + try { + String sql = "select messages.timeSent,messages.serverMsgId " + + "from accounts " + + "join conversations on accounts.uuid=conversations.accountUuid " + + "join messages on conversations.uuid=messages.conversationUuid " + + "join " + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS + " on messages.uuid=" + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS + "." + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID + " " + + "where accounts.uuid=? " + + "and (" + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS + "." + MessageDatabaseAccess.COLUMN_NAME_MSG_DIRECTION + "='IN'" + + " or messages.carbon=1 or messages.serverMsgId not null) order by messages.timesent desc limit 1"; + String[] args = {accountUuid}; + cursor = db.rawQuery(sql, args); + if (cursor.getCount() == 0) { + return null; + } else { + cursor.moveToFirst(); + return new Pair<>(cursor.getLong(0), cursor.getString(1)); + } + } catch (Exception e) { + return null; + } finally { + if (cursor != null) { + cursor.close(); + } + } } } -- cgit v1.2.3