add LAST_MSG_ID column to conversation table for 3.0 UXsubmit/tizen_3.0/20161226.074309accepted/tizen/3.0/wearable/20161227.010510accepted/tizen/3.0/mobile/20161227.010450accepted/tizen/3.0/ivi/20161227.010530accepted/tizen/3.0/common/20161227.102042

Change-Id: I82faffbba8c9f3698d03c3a860e567ed33dcd3ff
Signed-off-by: Kyeonghun Lee <kh9090.lee@samsung.com>

4 files changed, 56 insertions, 56 deletions

diff --git a/config/msg-service-db.sql b/config/msg-service-db.sql
index f0dc291..05b8eb4 100755
--- a/config/msg-service-db.sql
+++ b/config/msg-service-db.sql
@@ -11,7 +11,8 @@ CREATE TABLE MSG_CONVERSATION_TABLE
 	MSG_DIRECTION INTEGER DEFAULT 0 ,
 	DISPLAY_TIME DATETIME ,
 	DISPLAY_NAME TEXT NOT NULL DEFAULT '' ,
-	MSG_TEXT TEXT NOT NULL DEFAULT ''
+	MSG_TEXT TEXT NOT NULL DEFAULT '' ,
+	LAST_MSG_ID INTEGER DEFAULT 0
 );
 
 CREATE TABLE MSG_ADDRESS_TABLE
diff --git a/framework/storage-handler/MsgStorageManager.cpp b/framework/storage-handler/MsgStorageManager.cpp
index 5734763..0dd43be 100755
--- a/framework/storage-handler/MsgStorageManager.cpp
+++ b/framework/storage-handler/MsgStorageManager.cpp
@@ -115,7 +115,7 @@ void MsgUpdateDBtoVer2()
 	if (err == MSG_SUCCESS)
 		MSG_SEC_DEBUG("SUCCESS : alter %s.", MSGFW_MESSAGE_TABLE_NAME);
 	else
-		MSG_SEC_DEBUG("FAIL : create %s [%d].", MSGFW_MESSAGE_TABLE_NAME, err);
+		MSG_SEC_DEBUG("FAIL : alter %s [%d].", MSGFW_MESSAGE_TABLE_NAME, err);
 
 	memset(sqlQuery, 0x00, sizeof(sqlQuery));
 	snprintf(sqlQuery, sizeof(sqlQuery),
@@ -128,7 +128,20 @@ void MsgUpdateDBtoVer2()
 	if (err == MSG_SUCCESS)
 		MSG_SEC_DEBUG("SUCCESS : alter %s.", MSGFW_CB_MSG_TABLE_NAME);
 	else
-		MSG_SEC_DEBUG("FAIL : create %s [%d].", MSGFW_CB_MSG_TABLE_NAME, err);
+		MSG_SEC_DEBUG("FAIL : alter %s [%d].", MSGFW_CB_MSG_TABLE_NAME, err);
+
+	memset(sqlQuery, 0x00, sizeof(sqlQuery));
+	snprintf(sqlQuery, sizeof(sqlQuery),
+			"ALTER TABLE %s "
+			"ADD COLUMN LAST_MSG_ID INTEGER DEFAULT 0;",
+			MSGFW_CONVERSATION_TABLE_NAME);
+
+	err = dbHandle->execQuery(sqlQuery);
+
+	if (err == MSG_SUCCESS)
+		MSG_SEC_DEBUG("SUCCESS : alter %s.", MSGFW_CONVERSATION_TABLE_NAME);
+	else
+		MSG_SEC_DEBUG("FAIL : alter %s [%d].", MSGFW_CONVERSATION_TABLE_NAME, err);
 }
 
 
diff --git a/framework/storage-handler/MsgStorageMessage.cpp b/framework/storage-handler/MsgStorageMessage.cpp
index a4ff93c..702c581 100755
--- a/framework/storage-handler/MsgStorageMessage.cpp
+++ b/framework/storage-handler/MsgStorageMessage.cpp
@@ -2774,16 +2774,20 @@ msg_error_t MsgStoGetThreadInfo(msg_thread_id_t threadId, MSG_THREAD_VIEW_S *pTh
 
 	snprintf(sqlQuery, sizeof(sqlQuery), "SELECT A.CONV_ID, A.UNREAD_CNT, A.SMS_CNT, A.MMS_CNT, "
 			"A.MAIN_TYPE, A.SUB_TYPE, A.MSG_DIRECTION, A.DISPLAY_TIME, A.DISPLAY_NAME, A.MSG_TEXT, "
-			"(SELECT COUNT(*) FROM %s B WHERE B.CONV_ID = A.CONV_ID AND B.PROTECTED = 1) AS PROTECTED, "
-			"(SELECT COUNT(*) FROM %s B WHERE B.CONV_ID = A.CONV_ID AND B.FOLDER_ID = %d) AS DRAFT, "
-			"(SELECT COUNT(*) FROM %s B WHERE B.CONV_ID = A.CONV_ID AND B.NETWORK_STATUS = %d) AS FAILED, "
-			"(SELECT COUNT(*) FROM %s B WHERE B.CONV_ID = A.CONV_ID AND B.NETWORK_STATUS = %d) AS SENDING "
-			"FROM %s A WHERE A.CONV_ID = %d AND A.SMS_CNT + A.MMS_CNT > 0;",
+			"B.PROTECTED, "
+			"(CASE WHEN B.FOLDER_ID = %d THEN 1 END) AS DRAFT, "
+			"(CASE WHEN B.NETWORK_STATUS = %d THEN 1 END) AS FAILED, "
+			"(CASE WHEN B.NETWORK_STATUS = %d THEN 1 END) AS SENDING "
+			"FROM %s A "
+			"LEFT OUTER JOIN "
+			"%s B ON A.LAST_MSG_ID = B.MSG_ID "
+			"WHERE A.CONV_ID = %d AND A.SMS_CNT + A.MMS_CNT > 0;",
+			MSG_DRAFT_ID,
+			MSG_NETWORK_SEND_FAIL,
+			MSG_NETWORK_SENDING,
+			MSGFW_CONVERSATION_TABLE_NAME,
 			MSGFW_MESSAGE_TABLE_NAME,
-			MSGFW_MESSAGE_TABLE_NAME, MSG_DRAFT_ID,
-			MSGFW_MESSAGE_TABLE_NAME, MSG_NETWORK_SEND_FAIL,
-			MSGFW_MESSAGE_TABLE_NAME, MSG_NETWORK_SENDING,
-			MSGFW_CONVERSATION_TABLE_NAME, threadId);
+			threadId);
 
 	msg_error_t err = dbHandle->getTable(sqlQuery, &rowCnt, &index);
 
@@ -2819,21 +2823,10 @@ msg_error_t MsgStoGetThreadInfo(msg_thread_id_t threadId, MSG_THREAD_VIEW_S *pTh
 		memset(pThreadInfo->threadData, 0x00, sizeof(pThreadInfo->threadData));
 		dbHandle->getColumnToString(index++, MAX_THREAD_DATA_LEN, pThreadInfo->threadData);
 
-		int protectedCnt = dbHandle->getColumnToInt(index++);
-		if (protectedCnt > 0)
-			pThreadInfo->bProtected = true;
-
-		int draftCnt = dbHandle->getColumnToInt(index++);
-		if (draftCnt > 0)
-			pThreadInfo->bDraft = true;
-
-		int failedCnt = dbHandle->getColumnToInt(index++);
-		if (failedCnt > 0)
-			pThreadInfo->bSendFailed = true;
-
-		int sendingCnt = dbHandle->getColumnToInt(index++);
-		if (sendingCnt > 0)
-			pThreadInfo->bSending = true;
+		pThreadInfo->bProtected = dbHandle->getColumnToInt(index++);
+		pThreadInfo->bDraft = dbHandle->getColumnToInt(index++);
+		pThreadInfo->bSendFailed = dbHandle->getColumnToInt(index++);
+		pThreadInfo->bSending = dbHandle->getColumnToInt(index++);
 	}
 
 	dbHandle->freeTable();
diff --git a/utils/MsgUtilStorage.cpp b/utils/MsgUtilStorage.cpp
index b2d5975..ac9342b 100755
--- a/utils/MsgUtilStorage.cpp
+++ b/utils/MsgUtilStorage.cpp
@@ -684,7 +684,7 @@ msg_error_t MsgStoUpdateConversation(MsgDbHandler *pDbHandle, msg_thread_id_t co
 
 		memset(sqlQuery, 0x00, MAX_QUERY_LEN);
 		snprintf(sqlQuery, sizeof(sqlQuery),
-				"SELECT MAIN_TYPE, SUB_TYPE, MSG_DIRECTION, DISPLAY_TIME, LENGTH(SUBJECT), SUBJECT, MSG_TEXT, DPM_RESTRICTED "
+				"SELECT MAIN_TYPE, SUB_TYPE, MSG_DIRECTION, MSG_ID, DISPLAY_TIME, LENGTH(SUBJECT), SUBJECT, MSG_TEXT, DPM_RESTRICTED "
 				"FROM %s "
 				"WHERE CONV_ID = %d AND FOLDER_ID > %d AND FOLDER_ID < %d AND STORAGE_ID = %d AND SCHEDULED_TIME = 0 ORDER BY DISPLAY_TIME DESC;",
 				MSGFW_MESSAGE_TABLE_NAME,
@@ -706,14 +706,15 @@ msg_error_t MsgStoUpdateConversation(MsgDbHandler *pDbHandle, msg_thread_id_t co
 		int main_type = pDbHandle->columnInt(0);
 		int sub_type = pDbHandle->columnInt(1);
 		int msg_direction = pDbHandle->columnInt(2);
-		time_t disp_time = (time_t)pDbHandle->columnInt(3);
-		int subject_length = pDbHandle->columnInt(4);
-		bool dpm_restricted = pDbHandle->columnInt(7);
+		int last_msg_id = pDbHandle->columnInt(3);
+		time_t disp_time = (time_t)pDbHandle->columnInt(4);
+		int subject_length = pDbHandle->columnInt(5);
+		bool dpm_restricted = pDbHandle->columnInt(8);
 		char subject[MAX_SUBJECT_LEN+1] = {0, };
 		char msg_text[MAX_MSG_TEXT_LEN+1] = {0, };
 		if (!dpm_restricted) {
-			snprintf(subject, sizeof(subject), "%s", pDbHandle->columnText(5));
-			snprintf(msg_text, sizeof(msg_text), "%s", pDbHandle->columnText(6));
+			snprintf(subject, sizeof(subject), "%s", pDbHandle->columnText(6));
+			snprintf(msg_text, sizeof(msg_text), "%s", pDbHandle->columnText(7));
 		} else {
 			snprintf(subject, sizeof(subject), "restricted message");
 			snprintf(msg_text, sizeof(msg_text), "restricted message");
@@ -822,8 +823,8 @@ msg_error_t MsgStoUpdateConversation(MsgDbHandler *pDbHandle, msg_thread_id_t co
 #endif
 		tmpSize = strlen(sqlQuery);
 		snprintf(sqlQuery+tmpSize, MAX_QUERY_LEN-tmpSize,
-				"MAIN_TYPE = %d, SUB_TYPE = %d, MSG_DIRECTION = %d, DISPLAY_TIME = %lu ",
-				main_type, sub_type, msg_direction, disp_time);
+				"MAIN_TYPE = %d, SUB_TYPE = %d, MSG_DIRECTION = %d, DISPLAY_TIME = %lu, LAST_MSG_ID = %d ",
+				main_type, sub_type, msg_direction, disp_time, last_msg_id);
 
 		tmpSize = strlen(sqlQuery);
 		snprintf(sqlQuery+tmpSize, MAX_QUERY_LEN-tmpSize,
@@ -1150,7 +1151,7 @@ msg_error_t MsgStoAddConversation(MsgDbHandler *pDbHandle, msg_thread_id_t *pCon
 	}
 	/* Add Conversation */
 	memset(sqlQuery, 0x00, sizeof(sqlQuery));
-	snprintf(sqlQuery, sizeof(sqlQuery), "INSERT INTO %s VALUES (%d, 0, 0, 0, 0, 0, 0, 0, '', '');",
+	snprintf(sqlQuery, sizeof(sqlQuery), "INSERT INTO %s VALUES (%d, 0, 0, 0, 0, 0, 0, 0, '', '', 0);",
 			MSGFW_CONVERSATION_TABLE_NAME, *pConvId);
 
 	if (pDbHandle->execQuery(sqlQuery) != MSG_SUCCESS) {
@@ -1668,11 +1669,14 @@ msg_error_t MsgStoGetThreadViewList(const MSG_SORT_RULE_S *pSortRule, msg_struct
 
 	snprintf(sqlQuery, sizeof(sqlQuery), "SELECT A.CONV_ID, A.UNREAD_CNT, A.SMS_CNT, A.MMS_CNT, A.MAIN_TYPE, A.SUB_TYPE, "
 			"A.MSG_DIRECTION, A.DISPLAY_TIME, A.DISPLAY_NAME, A.MSG_TEXT, "
-			"(COUNT(CASE WHEN B.PROTECTED = 1 THEN 1 END)) AS PROTECTED, "
-			"(COUNT(CASE WHEN B.FOLDER_ID = %d THEN 1 END)) AS DRAFT, "
-			"(COUNT(CASE WHEN B.NETWORK_STATUS = %d THEN 1 END)) AS FAILED, "
-			"(COUNT(CASE WHEN B.NETWORK_STATUS = %d THEN 1 END)) AS SENDING "
-			"FROM %s A, %s B ON A.SMS_CNT + A.MMS_CNT > 0 AND B.CONV_ID = A.CONV_ID "
+			"B.PROTECTED, "
+			"(CASE WHEN B.FOLDER_ID = %d THEN 1 END) AS DRAFT, "
+			"(CASE WHEN B.NETWORK_STATUS = %d THEN 1 END) AS FAILED, "
+			"(CASE WHEN B.NETWORK_STATUS = %d THEN 1 END) AS SENDING "
+			"FROM %s A "
+			"LEFT OUTER JOIN "
+			"%s B ON A.LAST_MSG_ID = B.MSG_ID "
+			"WHERE A.SMS_CNT + A.MMS_CNT > 0 "
 			"GROUP BY A.CONV_ID ORDER BY A.DISPLAY_TIME DESC;",
 			MSG_DRAFT_ID,
 			MSG_NETWORK_SEND_FAIL,
@@ -1734,21 +1738,10 @@ msg_error_t MsgStoGetThreadViewList(const MSG_SORT_RULE_S *pSortRule, msg_struct
 		memset(pTmp->threadData, 0x00, sizeof(pTmp->threadData));
 		dbHandle->getColumnToString(index++, MAX_THREAD_DATA_LEN, pTmp->threadData);
 
-		int protectedCnt = dbHandle->getColumnToInt(index++);
-		if (protectedCnt > 0)
-			pTmp->bProtected = true;
-
-		int draftCnt = dbHandle->getColumnToInt(index++);
-		if (draftCnt > 0)
-			pTmp->bDraft = true;
-
-		int failedCnt = dbHandle->getColumnToInt(index++);
-		if (failedCnt > 0)
-			pTmp->bSendFailed = true;
-
-		int sendingCnt = dbHandle->getColumnToInt(index++);
-		if (sendingCnt > 0)
-			pTmp->bSending = true;
+		pTmp->bProtected = dbHandle->getColumnToInt(index++);
+		pTmp->bDraft = dbHandle->getColumnToInt(index++);
+		pTmp->bSendFailed = dbHandle->getColumnToInt(index++);
+		pTmp->bSending = dbHandle->getColumnToInt(index++);
 	}
 
 	dbHandle->freeTable();