/*
* Copyright (C) 2008 The Android Open Source Project
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package cn.edu.tsinghua.hpc.tmms.provider;
import static cn.edu.tsinghua.hpc.google.tmms.pdu.PduHeaders.MESSAGE_TYPE_DELIVERY_IND;
import static cn.edu.tsinghua.hpc.google.tmms.pdu.PduHeaders.MESSAGE_TYPE_NOTIFICATION_IND;
import static cn.edu.tsinghua.hpc.google.tmms.pdu.PduHeaders.MESSAGE_TYPE_READ_ORIG_IND;
import static cn.edu.tsinghua.hpc.google.tmms.pdu.PduHeaders.MESSAGE_TYPE_READ_REC_IND;
import static cn.edu.tsinghua.hpc.google.tmms.pdu.PduHeaders.MESSAGE_TYPE_RETRIEVE_CONF;
import static cn.edu.tsinghua.hpc.google.tmms.pdu.PduHeaders.MESSAGE_TYPE_SEND_REQ;
import java.io.IOException;
import java.io.InputStream;
import java.io.FileInputStream;
import java.io.File;
import java.lang.reflect.Method;
import java.util.ArrayList;
import cn.edu.tsinghua.hpc.google.tmms.pdu.EncodedStringValue;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.provider.Telephony.Mms;
import android.provider.Telephony.MmsSms;
import android.provider.Telephony.Sms;
import android.provider.Telephony.Threads;
import android.provider.Telephony.Mms.Addr;
import android.provider.Telephony.Mms.Part;
import android.provider.Telephony.Mms.Rate;
import android.provider.Telephony.MmsSms.PendingMessages;
import android.util.Log;
public class MmsSmsDatabaseHelper extends SQLiteOpenHelper {
private static final String TAG = "MmsSmsDatabaseHelper";
private static final String SMS_UPDATE_THREAD_READ_BODY =
" UPDATE threads SET read = " +
" CASE (SELECT COUNT(*)" +
" FROM sms" +
" WHERE " + Sms.READ + " = 0" +
" AND " + Sms.THREAD_ID + " = threads._id)" +
" WHEN 0 THEN 1" +
" ELSE 0" +
" END" +
" WHERE threads._id = new." + Sms.THREAD_ID + "; ";
private static final String UPDATE_THREAD_COUNT_ON_NEW =
" UPDATE threads SET message_count = " +
" (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads " +
" ON threads._id = " + Sms.THREAD_ID +
" WHERE " + Sms.THREAD_ID + " = new.thread_id" +
// " AND sms." + Sms.TYPE + " != 3) + " +
// add by chenqiang
" AND sms." + Sms.TYPE + " != 3 AND (sms.sync_state = 'present' or sms.sync_state='recover')) + " +
" (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads " +
" ON threads._id = " + Mms.THREAD_ID +
" WHERE " + Mms.THREAD_ID + " = new.thread_id" +
" AND (m_type=132 OR m_type=130 OR m_type=128)" +
// " AND " + Mms.MESSAGE_BOX + " != 3) " +
//add by chenqiang
" AND " + Mms.MESSAGE_BOX + " != 3 AND (pdu.sync_state = 'present' or pdu.sync_state='recover')) " +
" WHERE threads._id = new.thread_id; ";
private static final String UPDATE_THREAD_COUNT_ON_OLD =
" UPDATE threads SET message_count = " +
" (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads " +
" ON threads._id = " + Sms.THREAD_ID +
" WHERE " + Sms.THREAD_ID + " = old.thread_id" +
// " AND sms." + Sms.TYPE + " != 3) + " +
// add by chenqiang
" AND sms." + Sms.TYPE + " != 3 AND (sms.sync_state = 'present' or sms.sync_state='recover')) + " +
" (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads " +
" ON threads._id = " + Mms.THREAD_ID +
" WHERE " + Mms.THREAD_ID + " = old.thread_id" +
" AND (m_type=132 OR m_type=130 OR m_type=128)" +
// " AND " + Mms.MESSAGE_BOX + " != 3) " +
//add by chenqiang
" AND " + Mms.MESSAGE_BOX + " != 3 AND (pdu.sync_state = 'present' or pdu.sync_state='recover')) " +
" WHERE threads._id = old.thread_id; ";
private static final String SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE =
"BEGIN" +
" UPDATE threads SET" +
" date = (strftime('%s','now') * 1000), " +
" snippet = new." + Sms.BODY + ", " +
" snippet_cs = 0" +
" WHERE threads._id = new." + Sms.THREAD_ID + "; " +
UPDATE_THREAD_COUNT_ON_NEW +
SMS_UPDATE_THREAD_READ_BODY +
"END;";
private static final String PDU_UPDATE_THREAD_CONSTRAINTS =
" WHEN new." + Mms.MESSAGE_TYPE + "=" + MESSAGE_TYPE_RETRIEVE_CONF +
" OR new." + Mms.MESSAGE_TYPE + "=" + MESSAGE_TYPE_NOTIFICATION_IND +
" OR new." + Mms.MESSAGE_TYPE + "=" + MESSAGE_TYPE_SEND_REQ + " ";
private static final String PDU_UPDATE_THREAD_READ_BODY =
" UPDATE threads SET read = " +
" CASE (SELECT COUNT(*)" +
" FROM " + MmsProvider.TABLE_PDU +
" WHERE " + Mms.READ + " = 0" +
" AND " + Mms.THREAD_ID + " = threads._id)" +
" WHEN 0 THEN 1" +
" ELSE 0" +
" END" +
" WHERE threads._id = new." + Mms.THREAD_ID + "; ";
private static final String PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE =
"BEGIN" +
" UPDATE threads SET" +
" date = (strftime('%s','now') * 1000), " +
" snippet = new." + Mms.SUBJECT + ", " +
" snippet_cs = new." + Mms.SUBJECT_CHARSET +
" WHERE threads._id = new." + Mms.THREAD_ID + "; " +
UPDATE_THREAD_COUNT_ON_NEW +
PDU_UPDATE_THREAD_READ_BODY +
"END;";
private static final String UPDATE_THREAD_SNIPPET_SNIPPET_CS_ON_DELETE =
" UPDATE threads SET snippet = " +
" (SELECT snippet FROM" +
" (SELECT date * 1000 AS date, sub AS snippet, thread_id FROM pdu" +
" UNION SELECT date, body AS snippet, thread_id FROM sms)" +
" WHERE thread_id = OLD.thread_id ORDER BY date DESC LIMIT 1) " +
" WHERE threads._id = OLD.thread_id; " +
" UPDATE threads SET snippet_cs = " +
" (SELECT snippet_cs FROM" +
" (SELECT date * 1000 AS date, sub_cs AS snippet_cs, thread_id FROM pdu" +
" UNION SELECT date, 0 AS snippet_cs, thread_id FROM sms)" +
" WHERE thread_id = OLD.thread_id ORDER BY date DESC LIMIT 1) " +
" WHERE threads._id = OLD.thread_id; ";
// When a part is inserted, if it is not text/plain or application/smil
// (which both can exist with text-only MMSes), then there is an attachment.
// Set has_attachment=1 in the threads table for the thread in question.
private static final String PART_UPDATE_THREADS_ON_INSERT_TRIGGER =
"CREATE TRIGGER update_threads_on_insert_part " +
" AFTER INSERT ON part " +
" WHEN new.ct != 'text/plain' AND new.ct != 'application/smil' " +
" BEGIN " +
" UPDATE threads SET has_attachment=1 WHERE _id IN " +
" (SELECT pdu.thread_id FROM part JOIN pdu ON pdu._id=part.mid " +
" WHERE part._id=new._id LIMIT 1); " +
" END";
// When the 'mid' column in the part table is updated, we need to run the trigger to update
// the threads table's has_attachment column, if the part is an attachment.
private static final String PART_UPDATE_THREADS_ON_UPDATE_TRIGGER =
"CREATE TRIGGER update_threads_on_update_part " +
" AFTER UPDATE of " + Part.MSG_ID + " ON part " +
" WHEN new.ct != 'text/plain' AND new.ct != 'application/smil' " +
" BEGIN " +
" UPDATE threads SET has_attachment=1 WHERE _id IN " +
" (SELECT pdu.thread_id FROM part JOIN pdu ON pdu._id=part.mid " +
" WHERE part._id=new._id LIMIT 1); " +
" END";
// When a part is deleted (with the same non-text/SMIL constraint as when
// we set has_attachment), update the threads table for all threads.
// Unfortunately we cannot update only the thread that the part was
// attached to, as it is possible that the part has been orphaned and
// the message it was attached to is already gone.
private static final String PART_UPDATE_THREADS_ON_DELETE_TRIGGER =
"CREATE TRIGGER update_threads_on_delete_part " +
" AFTER DELETE ON part " +
" WHEN old.ct != 'text/plain' AND old.ct != 'application/smil' " +
" BEGIN " +
" UPDATE threads SET has_attachment = " +
" CASE " +
" (SELECT COUNT(*) FROM part JOIN pdu ON pdu._id=part.mid " +
" WHERE pdu.thread_id = threads._id " +
" AND part.ct != 'text/plain' AND part.ct != 'application/smil')" +
" WHEN 0 THEN 0 " +
" ELSE 1 " +
" END; " +
" END";
// When the 'thread_id' column in the pdu table is updated, we need to run the trigger to update
// the threads table's has_attachment column, if the message has an attachment in 'part' table
private static final String PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER =
"CREATE TRIGGER update_threads_on_update_pdu " +
" AFTER UPDATE of thread_id ON pdu " +
" BEGIN " +
" UPDATE threads SET has_attachment=1 WHERE _id IN " +
" (SELECT pdu.thread_id FROM part JOIN pdu ON new._id=part.mid " +
" WHERE part.ct != 'text/plain' AND part.ct != 'application/smil'); " +
" END";
private static MmsSmsDatabaseHelper mInstance = null;
static final String DATABASE_NAME = "mmssms.db";
static final int DATABASE_VERSION = 47;
private MmsSmsDatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
/**
* Return a singleton helper for the combined MMS and SMS
* database.
*/
/* package */ public static synchronized MmsSmsDatabaseHelper getInstance(Context context) {
if (mInstance == null) {
mInstance = new MmsSmsDatabaseHelper(context);
}
return mInstance;
}
public static void updateThread(SQLiteDatabase db, long thread_id) {
if (thread_id < 0) {
updateAllThreads(db, null, null);
return;
}
// Delete the row for this thread in the threads table if
// there are no more messages attached to it in either
// the sms or pdu tables.
int rows = db.delete("threads",
"_id = ? AND _id NOT IN" +
" (SELECT thread_id FROM sms " +
" UNION SELECT thread_id FROM pdu)",
new String[] { String.valueOf(thread_id) });
if (rows > 0) {
// If this deleted a row, we have no more work to do.
return;
}
// Update the message count in the threads table as the sum
// of all messages in both the sms and pdu tables.
db.execSQL(
" UPDATE threads SET message_count = " +
" (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads " +
" ON threads._id = " + Sms.THREAD_ID +
" WHERE " + Sms.THREAD_ID + " = " + thread_id +
// " AND sms." + Sms.TYPE + " != 3) + " +
//add by chenqiang
" AND sms." + Sms.TYPE + " != 3 AND (sms.sync_state = 'present' or sms.sync_state='recover')) + " +
" (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads " +
" ON threads._id = " + Mms.THREAD_ID +
" WHERE " + Mms.THREAD_ID + " = " + thread_id +
" AND (m_type=132 OR m_type=130 OR m_type=128)" +
// " AND " + Mms.MESSAGE_BOX + " != 3) " +
// //add by chenqiang
" AND " + Mms.MESSAGE_BOX + " != 3 AND (pdu.sync_state = 'present' or pdu.sync_state='recover')) " +
" WHERE threads._id = " + thread_id + ";");
// Update the date and the snippet (and its character set) in
// the threads table to be that of the most recent message in
// the thread.
db.execSQL(
" UPDATE threads" +
" SET" +
" date =" +
" (SELECT date FROM" +
" (SELECT date * 1000 AS date, thread_id FROM pdu" +
" UNION SELECT date, thread_id FROM sms)" +
" WHERE thread_id = " + thread_id + " ORDER BY date DESC LIMIT 1)," +
" snippet =" +
" (SELECT snippet FROM" +
" (SELECT date * 1000 AS date, sub AS snippet, thread_id FROM pdu" +
" UNION SELECT date, body AS snippet, thread_id FROM sms)" +
" WHERE thread_id = " + thread_id + " ORDER BY date DESC LIMIT 1)," +
" snippet_cs =" +
" (SELECT snippet_cs FROM" +
" (SELECT date * 1000 AS date, sub_cs AS snippet_cs, thread_id FROM pdu" +
" UNION SELECT date, 0 AS snippet_cs, thread_id FROM sms)" +
" WHERE thread_id = " + thread_id + " ORDER BY date DESC LIMIT 1)" +
" WHERE threads._id = " + thread_id + ";");
// Update the error column of the thread to indicate if there
// are any messages in it that have failed to send.
db.execSQL(
"UPDATE threads SET error =" +
" (SELECT COUNT(*) FROM sms WHERE type=5" +
" AND thread_id = " + thread_id + " LIMIT 1)" +
" WHERE threads._id = " + thread_id + ";");
}
public static void updateAllThreads(SQLiteDatabase db, String where, String[] whereArgs) {
if (where == null) {
where = "";
} else {
where = "WHERE (" + where + ")";
}
String query = "SELECT _id FROM threads WHERE _id IN " +
"(SELECT DISTINCT thread_id FROM sms " + where + ")";
Cursor c = db.rawQuery(query, whereArgs);
if (c != null) {
while (c.moveToNext()) {
updateThread(db, c.getInt(0));
}
c.close();
}
// remove orphaned threads
db.delete("threads",
"_id NOT IN (SELECT DISTINCT thread_id FROM sms " +
"UNION SELECT DISTINCT thread_id FROM pdu)", null);
}
public static int deleteOneSms(SQLiteDatabase db, int message_id) {
int thread_id = -1;
// Find the thread ID that the specified SMS belongs to.
Cursor c = db.query("sms", new String[] { "thread_id" },
"_id=" + message_id, null, null, null, null);
if (c != null) {
if (c.moveToFirst()) {
thread_id = c.getInt(0);
}
c.close();
}
// Delete the specified message.
int rows = db.delete("sms", "_id=" + message_id, null);
if (thread_id > 0) {
// Update its thread.
updateThread(db, thread_id);
}
return rows;
}
@Override
public void onCreate(SQLiteDatabase db) {
Log.d("Mms","create db");
createMmsTables(db);
createSmsTables(db);
createCommonTables(db);
createCommonTriggers(db);
createMmsTriggers(db);
}
private void createMmsTables(SQLiteDatabase db) {
// N.B.: Whenever the columns here are changed, the columns in
// {@ref MmsSmsProvider} must be changed to match.
db.execSQL("CREATE TABLE " + MmsProvider.TABLE_PDU + " (" +
Mms._ID + " INTEGER PRIMARY KEY," +
/***
*Add by Yangyang Zhao
*/
// "guid INTEGER ," +
//add by chenqiang
"guid INTEGER DEFAULT -1," +
"sync_state TEXT DEFAULT 'present',"+
"sync_dirty INTEGER DEFAULT 0," +
"sync_enable INTERGER DEFAULT 1,"+
Mms.THREAD_ID + " INTEGER," +
Mms.DATE + " INTEGER," +
Mms.MESSAGE_BOX + " INTEGER," +
Mms.READ + " INTEGER DEFAULT 0," +
Mms.MESSAGE_ID + " TEXT," +
Mms.SUBJECT + " TEXT," +
Mms.SUBJECT_CHARSET + " INTEGER," +
Mms.CONTENT_TYPE + " TEXT," +
Mms.CONTENT_LOCATION + " TEXT," +
Mms.EXPIRY + " INTEGER," +
Mms.MESSAGE_CLASS + " TEXT," +
Mms.MESSAGE_TYPE + " INTEGER," +
Mms.MMS_VERSION + " INTEGER," +
Mms.MESSAGE_SIZE + " INTEGER," +
Mms.PRIORITY + " INTEGER," +
Mms.READ_REPORT + " INTEGER," +
Mms.REPORT_ALLOWED + " INTEGER," +
Mms.RESPONSE_STATUS + " INTEGER," +
Mms.STATUS + " INTEGER," +
Mms.TRANSACTION_ID + " TEXT," +
Mms.RETRIEVE_STATUS + " INTEGER," +
Mms.RETRIEVE_TEXT + " TEXT," +
Mms.RETRIEVE_TEXT_CHARSET + " INTEGER," +
Mms.READ_STATUS + " INTEGER," +
Mms.CONTENT_CLASS + " INTEGER," +
Mms.RESPONSE_TEXT + " TEXT," +
Mms.DELIVERY_TIME + " INTEGER," +
Mms.DELIVERY_REPORT + " INTEGER," +
Mms.LOCKED + " INTEGER DEFAULT 0" +
");");
db.execSQL("CREATE TABLE " + MmsProvider.TABLE_ADDR + " (" +
Addr._ID + " INTEGER PRIMARY KEY," +
Addr.MSG_ID + " INTEGER," +
Addr.CONTACT_ID + " INTEGER," +
Addr.ADDRESS + " TEXT," +
Addr.TYPE + " INTEGER," +
Addr.CHARSET + " INTEGER);");
db.execSQL("CREATE TABLE " + MmsProvider.TABLE_PART + " (" +
Part._ID + " INTEGER PRIMARY KEY," +
Part.MSG_ID + " INTEGER," +
Part.SEQ + " INTEGER DEFAULT 0," +
Part.CONTENT_TYPE + " TEXT," +
Part.NAME + " TEXT," +
Part.CHARSET + " INTEGER," +
Part.CONTENT_DISPOSITION + " TEXT," +
Part.FILENAME + " TEXT," +
Part.CONTENT_ID + " TEXT," +
Part.CONTENT_LOCATION + " TEXT," +
Part.CT_START + " INTEGER," +
Part.CT_TYPE + " TEXT," +
Part._DATA + " TEXT," +
Part.TEXT + " TEXT);");
db.execSQL("CREATE TABLE " + MmsProvider.TABLE_RATE + " (" +
Rate.SENT_TIME + " INTEGER);");
db.execSQL("CREATE TABLE " + MmsProvider.TABLE_DRM + " (" +
BaseColumns._ID + " INTEGER PRIMARY KEY," +
"_data TEXT);");
/**
* Add by Yangyang Zhao
*/
db.execSQL("CREATE TABLE " + "mms_data" + " (" + BaseColumns._ID
+ " INTEGER PRIMARY KEY," + "mms_id INTEGER DEFAULT -1,"
+ "data TEXT);");
}
private void createMmsTriggers(SQLiteDatabase db) {
// Cleans up parts when a MM is deleted.
db.execSQL("CREATE TRIGGER part_cleanup DELETE ON " + MmsProvider.TABLE_PDU + " " +
"BEGIN " +
" DELETE FROM " + MmsProvider.TABLE_PART +
" WHERE " + Part.MSG_ID + "=old._id;" +
"END;");
// Cleans up address info when a MM is deleted.
db.execSQL("CREATE TRIGGER addr_cleanup DELETE ON " + MmsProvider.TABLE_PDU + " " +
"BEGIN " +
" DELETE FROM " + MmsProvider.TABLE_ADDR +
" WHERE " + Addr.MSG_ID + "=old._id;" +
"END;");
// Delete obsolete delivery-report, read-report while deleting their
// associated Send.req.
db.execSQL("CREATE TRIGGER cleanup_delivery_and_read_report " +
"AFTER DELETE ON " + MmsProvider.TABLE_PDU + " " +
"WHEN old." + Mms.MESSAGE_TYPE + "=" + MESSAGE_TYPE_SEND_REQ + " " +
"BEGIN " +
" DELETE FROM " + MmsProvider.TABLE_PDU +
" WHERE (" + Mms.MESSAGE_TYPE + "=" + MESSAGE_TYPE_DELIVERY_IND +
" OR " + Mms.MESSAGE_TYPE + "=" + MESSAGE_TYPE_READ_ORIG_IND + ")" +
" AND " + Mms.MESSAGE_ID + "=old." + Mms.MESSAGE_ID + "; " +
"END;");
// Update threads table to indicate whether attachments exist when
// parts are inserted or deleted.
db.execSQL(PART_UPDATE_THREADS_ON_INSERT_TRIGGER);
db.execSQL(PART_UPDATE_THREADS_ON_UPDATE_TRIGGER);
db.execSQL(PART_UPDATE_THREADS_ON_DELETE_TRIGGER);
db.execSQL(PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER);
}
private void createSmsTables(SQLiteDatabase db) {
// N.B.: Whenever the columns here are changed, the columns in
// {@ref MmsSmsProvider} must be changed to match.
db.execSQL("CREATE TABLE sms (" +
"_id INTEGER PRIMARY KEY," +
/***
* Add by Yangyang Zhao
*/
// "guid INTEGER," +
//add by chenqiang
"guid INTEGER DEFAULT -1," +
"sync_state TEXT DEFAULT 'present'," +
"sync_dirty INTEGER DEFAULT 0," +
"sync_enable INTERGER DEFAULT 1,"+
"thread_id INTEGER," +
"address TEXT," +
"person INTEGER," +
"date INTEGER," +
"protocol INTEGER," +
"read INTEGER DEFAULT 0," +
"status INTEGER DEFAULT -1," + // a TP-Status value
// or -1 if it
// status hasn't
// been received
"type INTEGER," +
"reply_path_present INTEGER," +
"subject TEXT," +
"body TEXT," +
"service_center TEXT," +
"locked INTEGER DEFAULT 0" +
");");
/**
* This table is used by the SMS dispatcher to hold
* incomplete partial messages until all the parts arrive.
*/
db.execSQL("CREATE TABLE raw (" +
"_id INTEGER PRIMARY KEY," +
"date INTEGER," +
"reference_number INTEGER," + // one per full message
"count INTEGER," + // the number of parts
"sequence INTEGER," + // the part number of this message
"destination_port INTEGER," +
"address TEXT," +
"pdu TEXT);"); // the raw PDU for this part
db.execSQL("CREATE TABLE attachments (" +
"sms_id INTEGER," +
"content_url TEXT," +
"offset INTEGER);");
/**
* This table is used by the SMS dispatcher to hold pending
* delivery status report intents.
*/
db.execSQL("CREATE TABLE sr_pending (" +
"reference_number INTEGER," +
"action TEXT," +
"data TEXT);");
}
private void createCommonTables(SQLiteDatabase db) {
// TODO Ensure that each entry is removed when the last use of
// any address equivalent to its address is removed.
/**
* This table maps the first instance seen of any particular
* MMS/SMS address to an ID, which is then used as its
* canonical representation. If the same address or an
* equivalent address (as determined by our Sqlite
* PHONE_NUMBERS_EQUAL extension) is seen later, this same ID
* will be used.
*/
db.execSQL("CREATE TABLE canonical_addresses (" +
"_id INTEGER PRIMARY KEY," +
"address TEXT);");
/**
* This table stores the infomation for TSync
*/
db.execSQL("CREATE TABLE transparent_sync (" +
// "_id INTERGER PRIMARY KEY," +
//add by chenqiang
BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
"key TEXT,"+
"value TEXT);");
/**
* This table maps the subject and an ordered set of recipient
* IDs, separated by spaces, to a unique thread ID. The IDs
* come from the canonical_addresses table. This works
* because messages are considered to be part of the same
* thread if they have the same subject (or a null subject)
* and the same set of recipients.
*/
db.execSQL("CREATE TABLE threads (" +
Threads._ID + " INTEGER PRIMARY KEY," +
/**
* Add by Yangyang Zhao
*/
"sync_state TEXT DEFAULT 'present'," +
"sync_enable INTERGER DEFAULT 1," +
Threads.DATE + " INTEGER DEFAULT 0," +
Threads.MESSAGE_COUNT + " INTEGER DEFAULT 0," +
Threads.RECIPIENT_IDS + " TEXT," +
Threads.SNIPPET + " TEXT," +
Threads.SNIPPET_CHARSET + " INTEGER DEFAULT 0," +
Threads.READ + " INTEGER DEFAULT 1," +
Threads.TYPE + " INTEGER DEFAULT 0," +
Threads.ERROR + " INTEGER DEFAULT 0," +
Threads.HAS_ATTACHMENT + " INTEGER DEFAULT 0);");
/**
* This table stores the queue of messages to be sent/downloaded.
*/
db.execSQL("CREATE TABLE " + MmsSmsProvider.TABLE_PENDING_MSG +" (" +
PendingMessages._ID + " INTEGER PRIMARY KEY," +
PendingMessages.PROTO_TYPE + " INTEGER," +
PendingMessages.MSG_ID + " INTEGER," +
PendingMessages.MSG_TYPE + " INTEGER," +
PendingMessages.ERROR_TYPE + " INTEGER," +
PendingMessages.ERROR_CODE + " INTEGER," +
PendingMessages.RETRY_INDEX + " INTEGER NOT NULL DEFAULT 0," +
PendingMessages.DUE_TIME + " INTEGER," +
PendingMessages.LAST_TRY + " INTEGER);");
}
// TODO Check the query plans for these triggers.
private void createCommonTriggers(SQLiteDatabase db) {
// Updates threads table whenever a message is added to pdu.
db.execSQL("CREATE TRIGGER pdu_update_thread_on_insert AFTER INSERT ON " +
MmsProvider.TABLE_PDU + " " +
PDU_UPDATE_THREAD_CONSTRAINTS +
PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE);
// Updates threads table whenever a message is added to sms.
db.execSQL("CREATE TRIGGER sms_update_thread_on_insert AFTER INSERT ON sms " +
SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE);
// Updates threads table whenever a message in pdu is updated.
db.execSQL("CREATE TRIGGER pdu_update_thread_date_subject_on_update AFTER" +
// " UPDATE OF " + Mms.DATE + ", " + Mms.SUBJECT + ", " + Mms.MESSAGE_BOX +
//add by chenqiang
" UPDATE OF " + Mms.DATE + ", " + Mms.SUBJECT + ", " + Mms.MESSAGE_BOX + ", " + "sync_state" +
" ON " + MmsProvider.TABLE_PDU + " " +
PDU_UPDATE_THREAD_CONSTRAINTS +
PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE);
// Updates threads table whenever a message in sms is updated.
db.execSQL("CREATE TRIGGER sms_update_thread_date_subject_on_update AFTER" +
// " UPDATE OF " + Sms.DATE + ", " + Sms.BODY + ", " + Sms.TYPE +
//add by chenqiang
" UPDATE OF " + Sms.DATE + ", " + Sms.BODY + ", " + Sms.TYPE +", " + "sync_state" +
" ON sms " +
SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE);
// Updates threads table whenever a message in pdu is updated.
db.execSQL("CREATE TRIGGER pdu_update_thread_read_on_update AFTER" +
" UPDATE OF " + Mms.READ +
" ON " + MmsProvider.TABLE_PDU + " " +
PDU_UPDATE_THREAD_CONSTRAINTS +
"BEGIN " +
PDU_UPDATE_THREAD_READ_BODY +
"END;");
// Updates threads table whenever a message in sms is updated.
db.execSQL("CREATE TRIGGER sms_update_thread_read_on_update AFTER" +
" UPDATE OF " + Sms.READ +
" ON sms " +
"BEGIN " +
SMS_UPDATE_THREAD_READ_BODY +
"END;");
// Update threads table whenever a message in pdu is deleted
db.execSQL("CREATE TRIGGER pdu_update_thread_on_delete " +
"AFTER DELETE ON pdu " +
"BEGIN " +
" UPDATE threads SET " +
" date = (strftime('%s','now') * 1000)" +
" WHERE threads._id = old." + Mms.THREAD_ID + "; " +
UPDATE_THREAD_COUNT_ON_OLD +
UPDATE_THREAD_SNIPPET_SNIPPET_CS_ON_DELETE +
"END;");
// When the last message in a thread is deleted, these
// triggers ensure that the entry for its thread ID is removed
// from the threads table.
db.execSQL("CREATE TRIGGER delete_obsolete_threads_pdu " +
"AFTER DELETE ON pdu " +
"BEGIN " +
" DELETE FROM threads " +
" WHERE " +
" _id = old.thread_id " +
" AND _id NOT IN " +
" (SELECT thread_id FROM sms " +
" UNION SELECT thread_id from pdu); " +
"END;");
db.execSQL("CREATE TRIGGER delete_obsolete_threads_when_update_pdu " +
"AFTER UPDATE OF " + Mms.THREAD_ID + " ON pdu " +
"WHEN old." + Mms.THREAD_ID + " != new." + Mms.THREAD_ID + " " +
"BEGIN " +
" DELETE FROM threads " +
" WHERE " +
" _id = old.thread_id " +
" AND _id NOT IN " +
" (SELECT thread_id FROM sms " +
" UNION SELECT thread_id from pdu); " +
"END;");
// Insert pending status for M-Notification.ind or M-ReadRec.ind
// when they are inserted into Inbox/Outbox.
db.execSQL("CREATE TRIGGER insert_mms_pending_on_insert " +
"AFTER INSERT ON pdu " +
"WHEN new." + Mms.MESSAGE_TYPE + "=" + MESSAGE_TYPE_NOTIFICATION_IND +
" OR new." + Mms.MESSAGE_TYPE + "=" + MESSAGE_TYPE_READ_REC_IND + " " +
"BEGIN " +
" INSERT INTO " + MmsSmsProvider.TABLE_PENDING_MSG +
" (" + PendingMessages.PROTO_TYPE + "," +
" " + PendingMessages.MSG_ID + "," +
" " + PendingMessages.MSG_TYPE + "," +
" " + PendingMessages.ERROR_TYPE + "," +
" " + PendingMessages.ERROR_CODE + "," +
" " + PendingMessages.RETRY_INDEX + "," +
" " + PendingMessages.DUE_TIME + ") " +
" VALUES " +
" (" + MmsSms.MMS_PROTO + "," +
" new." + BaseColumns._ID + "," +
" new." + Mms.MESSAGE_TYPE + ",0,0,0,0);" +
"END;");
// Insert pending status for M-Send.req when it is moved into Outbox.
db.execSQL("CREATE TRIGGER insert_mms_pending_on_update " +
"AFTER UPDATE ON pdu " +
"WHEN new." + Mms.MESSAGE_TYPE + "=" + MESSAGE_TYPE_SEND_REQ +
" AND new." + Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_OUTBOX +
" AND old." + Mms.MESSAGE_BOX + "!=" + Mms.MESSAGE_BOX_OUTBOX + " " +
"BEGIN " +
" INSERT INTO " + MmsSmsProvider.TABLE_PENDING_MSG +
" (" + PendingMessages.PROTO_TYPE + "," +
" " + PendingMessages.MSG_ID + "," +
" " + PendingMessages.MSG_TYPE + "," +
" " + PendingMessages.ERROR_TYPE + "," +
" " + PendingMessages.ERROR_CODE + "," +
" " + PendingMessages.RETRY_INDEX + "," +
" " + PendingMessages.DUE_TIME + ") " +
" VALUES " +
" (" + MmsSms.MMS_PROTO + "," +
" new." + BaseColumns._ID + "," +
" new." + Mms.MESSAGE_TYPE + ",0,0,0,0);" +
"END;");
// When a message is moved out of Outbox, delete its pending status.
db.execSQL("CREATE TRIGGER delete_mms_pending_on_update " +
"AFTER UPDATE ON " + MmsProvider.TABLE_PDU + " " +
"WHEN old." + Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_OUTBOX +
" AND new." + Mms.MESSAGE_BOX + "!=" + Mms.MESSAGE_BOX_OUTBOX + " " +
"BEGIN " +
" DELETE FROM " + MmsSmsProvider.TABLE_PENDING_MSG +
" WHERE " + PendingMessages.MSG_ID + "=new._id; " +
"END;");
// Delete pending status for a message when it is deleted.
db.execSQL("CREATE TRIGGER delete_mms_pending_on_delete " +
"AFTER DELETE ON " + MmsProvider.TABLE_PDU + " " +
"BEGIN " +
" DELETE FROM " + MmsSmsProvider.TABLE_PENDING_MSG +
" WHERE " + PendingMessages.MSG_ID + "=old._id; " +
"END;");
// TODO Add triggers for SMS retry-status management.
// Update the error flag of threads when the error type of
// a pending MM is updated.
db.execSQL("CREATE TRIGGER update_threads_error_on_update_mms " +
" AFTER UPDATE OF err_type ON pending_msgs " +
" WHEN (OLD.err_type < 10 AND NEW.err_type >= 10)" +
" OR (OLD.err_type >= 10 AND NEW.err_type < 10) " +
"BEGIN" +
" UPDATE threads SET error = " +
" CASE" +
" WHEN NEW.err_type >= 10 THEN error + 1" +
" ELSE error - 1" +
" END " +
" WHERE _id =" +
" (SELECT DISTINCT thread_id" +
" FROM pdu" +
" WHERE _id = NEW.msg_id); " +
"END;");
// Update the error flag of threads when delete pending message.
db.execSQL("CREATE TRIGGER update_threads_error_on_delete_mms " +
" BEFORE DELETE ON pdu" +
" WHEN OLD._id IN (SELECT DISTINCT msg_id" +
" FROM pending_msgs" +
" WHERE err_type >= 10) " +
"BEGIN " +
" UPDATE threads SET error = error - 1" +
" WHERE _id = OLD.thread_id; " +
"END;");
// Update the error flag of threads while moving an MM out of Outbox,
// which was failed to be sent permanently.
db.execSQL("CREATE TRIGGER update_threads_error_on_move_mms " +
" BEFORE UPDATE OF msg_box ON pdu " +
" WHEN (OLD.msg_box = 4 AND NEW.msg_box != 4) " +
" AND (OLD._id IN (SELECT DISTINCT msg_id" +
" FROM pending_msgs" +
" WHERE err_type >= 10)) " +
"BEGIN " +
" UPDATE threads SET error = error - 1" +
" WHERE _id = OLD.thread_id; " +
"END;");
// Update the error flag of threads after a text message was
// failed to send/receive.
db.execSQL("CREATE TRIGGER update_threads_error_on_update_sms " +
" AFTER UPDATE OF type ON sms" +
" WHEN (OLD.type != 5 AND NEW.type = 5)" +
" OR (OLD.type = 5 AND NEW.type != 5) " +
"BEGIN " +
" UPDATE threads SET error = " +
" CASE" +
" WHEN NEW.type = 5 THEN error + 1" +
" ELSE error - 1" +
" END " +
" WHERE _id = NEW.thread_id; " +
"END;");
// //add by chenqiang ����sync_state�仯����Ӧthread�ı仯
// db.execSQL("CREATE TRIGGER sms_update_thread_sync_state "+
// "AFTER UPDATE OF sync_state "+
// "ON sms "+
// "BEGIN "+
// "update threads set message_count = "+
// "case when new.sync_state='present' then message_count+1 "+
// "when new.sync_state='deleted' and message_count>0 then message_count-1 "+
// "else 0 "+
// "end "+
// "where _id = new.thread_id; "+
// "update threads set sync_state = "+
// "case when message_count<=0 then 'deleted' "+
// "else 'present' "+
// "end "+
// "where _id = new.thread_id; "+
// "END;");
// //add by chenqiang �Ựsync_state�仯����Ӧ���ŵı仯
// db.execSQL("CREATE TRIGGER thread_update_sms_sync_state "+
// "AFTER UPDATE OF sync_state "+
// "ON threads "+
// "FOR EACH ROW "+
// "BEGIN "+
// "update sms set sync_state = "+
// "case when new.sync_state='deleted' then 'deleted' " +
// "else sync_state "+
// "end "+
// "where thread_id = new._id; "+
// "END;");
}
/**
* ��ձ�������
*/
public void wipeData() {
SQLiteDatabase db = getWritableDatabase();
db.execSQL("DELETE FROM canonical_addresses");
db.execSQL("DELETE FROM threads");
db.execSQL("DELETE FROM " + MmsSmsProvider.TABLE_PENDING_MSG);
db.execSQL("DELETE FROM sms");
db.execSQL("DELETE FROM mms_data");
db.execSQL("DELETE FROM raw");
db.execSQL("DELETE FROM attachments");
// db.execSQL("DELETE FROM thread_ids");
db.execSQL("DELETE FROM sr_pending");
db.execSQL("DELETE FROM " + MmsProvider.TABLE_PDU + ";");
db.execSQL("DELETE FROM " + MmsProvider.TABLE_ADDR + ";");
db.execSQL("DELETE FROM " + MmsProvider.TABLE_PART + ";");
db.execSQL("DELETE FROM " + MmsProvider.TABLE_RATE + ";");
db.execSQL("DELETE FROM " + MmsProvider.TABLE_DRM + ";");
db.execSQL("VACUUM;");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int currentVersion) {
Log.w(TAG, "Upgrading database from version " + oldVersion
+ " to " + currentVersion + ".");
switch (oldVersion) {
case 40:
if (currentVersion <= 40) {
return;
}
db.beginTransaction();
try {
upgradeDatabaseToVersion41(db);
db.setTransactionSuccessful();
} catch (Throwable ex) {
Log.e(TAG, ex.getMessage(), ex);
break;
} finally {
db.endTransaction();
}
// fall through
case 41:
if (currentVersion <= 41) {
return;
}
db.beginTransaction();
try {
upgradeDatabaseToVersion42(db);
db.setTransactionSuccessful();
} catch (Throwable ex) {
Log.e(TAG, ex.getMessage(), ex);
break;
} finally {
db.endTransaction();
}
// fall through
case 42:
if (currentVersion <= 42) {
return;
}
db.beginTransaction();
try {
upgradeDatabaseToVersion43(db);
db.setTransactionSuccessful();
} catch (Throwable ex) {
Log.e(TAG, ex.getMessage(), ex);
break;
} finally {
db.endTransaction();
}
// fall through
case 43:
if (currentVersion <= 43) {
return;
}
db.beginTransaction();
try {
upgradeDatabaseToVersion44(db);
db.setTransactionSuccessful();
} catch (Throwable ex) {
Log.e(TAG, ex.getMessage(), ex);
break;
} finally {
db.endTransaction();
}
// fall through
case 44:
if (currentVersion <= 44) {
return;
}
db.beginTransaction();
try {
upgradeDatabaseToVersion45(db);
db.setTransactionSuccessful();
} catch (Throwable ex) {
Log.e(TAG, ex.getMessage(), ex);
break;
} finally {
db.endTransaction();
}
// fall through
case 45:
if (currentVersion <= 45) {
return;
}
db.beginTransaction();
try {
upgradeDatabaseToVersion46(db);
db.setTransactionSuccessful();
} catch (Throwable ex) {
Log.e(TAG, ex.getMessage(), ex);
break;
} finally {
db.endTransaction();
}
// fall through
case 46:
if (currentVersion <= 46) {
return;
}
db.beginTransaction();
try {
upgradeDatabaseToVersion47(db);
db.setTransactionSuccessful();
} catch (Throwable ex) {
Log.e(TAG, ex.getMessage(), ex);
break;
} finally {
db.endTransaction();
}
return;
}
Log.e(TAG, "Destroying all old data.");
dropAll(db);
onCreate(db);
}
private void dropAll(SQLiteDatabase db) {
// Clean the database out in order to start over from scratch.
// We don't need to drop our triggers here because SQLite automatically
// drops a trigger when its attached database is dropped.
db.execSQL("DROP TABLE IF EXISTS canonical_addresses");
db.execSQL("DROP TABLE IF EXISTS threads");
db.execSQL("DROP TABLE IF EXISTS " + MmsSmsProvider.TABLE_PENDING_MSG);
db.execSQL("DROP TABLE IF EXISTS sms");
db.execSQL("DROP TABLE IF EXISTS raw");
db.execSQL("DROP TABLE IF EXISTS attachments");
db.execSQL("DROP TABLE IF EXISTS thread_ids");
db.execSQL("DROP TABLE IF EXISTS sr_pending");
db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_PDU + ";");
db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_ADDR + ";");
db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_PART + ";");
db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_RATE + ";");
db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_DRM + ";");
}
private void upgradeDatabaseToVersion41(SQLiteDatabase db) {
db.execSQL("DROP TRIGGER IF EXISTS update_threads_error_on_move_mms");
db.execSQL("CREATE TRIGGER update_threads_error_on_move_mms " +
" BEFORE UPDATE OF msg_box ON pdu " +
" WHEN (OLD.msg_box = 4 AND NEW.msg_box != 4) " +
" AND (OLD._id IN (SELECT DISTINCT msg_id" +
" FROM pending_msgs" +
" WHERE err_type >= 10)) " +
"BEGIN " +
" UPDATE threads SET error = error - 1" +
" WHERE _id = OLD.thread_id; " +
"END;");
}
private void upgradeDatabaseToVersion42(SQLiteDatabase db) {
db.execSQL("DROP TRIGGER IF EXISTS sms_update_thread_on_delete");
db.execSQL("DROP TRIGGER IF EXISTS delete_obsolete_threads_sms");
db.execSQL("DROP TRIGGER IF EXISTS update_threads_error_on_delete_sms");
}
private void upgradeDatabaseToVersion43(SQLiteDatabase db) {
// Add 'has_attachment' column to threads table.
db.execSQL("ALTER TABLE threads ADD COLUMN has_attachment INTEGER DEFAULT 0");
updateThreadsAttachmentColumn(db);
// Add insert and delete triggers for keeping it up to date.
db.execSQL(PART_UPDATE_THREADS_ON_INSERT_TRIGGER);
db.execSQL(PART_UPDATE_THREADS_ON_DELETE_TRIGGER);
}
private void upgradeDatabaseToVersion44(SQLiteDatabase db) {
updateThreadsAttachmentColumn(db);
// add the update trigger for keeping the threads up to date.
db.execSQL(PART_UPDATE_THREADS_ON_UPDATE_TRIGGER);
}
private void upgradeDatabaseToVersion45(SQLiteDatabase db) {
// Add 'locked' column to sms table.
db.execSQL("ALTER TABLE sms ADD COLUMN locked INTEGER DEFAULT 0");
// Add 'locked' column to pdu table.
db.execSQL("ALTER TABLE pdu ADD COLUMN " + Mms.LOCKED + " INTEGER DEFAULT 0");
}
private void upgradeDatabaseToVersion46(SQLiteDatabase db) {
// add the "text" column for caching inline text (e.g. strings) instead of
// putting them in an external file
db.execSQL("ALTER TABLE part ADD COLUMN " + Part.TEXT + " TEXT");
Cursor textRows = db.query(
"part",
new String[] { Part._ID, Part._DATA, Part.TEXT},
"ct = 'text/plain' OR ct == 'application/smil'",
null,
null,
null,
null);
ArrayList<String> filesToDelete = new ArrayList<String>();
try {
if (textRows != null) {
int partIdColumn = textRows.getColumnIndex(Part._ID);
int partDataColumn = textRows.getColumnIndex(Part._DATA);
int partTextColumn = textRows.getColumnIndex(Part.TEXT);
// This code is imperfect in that we can't guarantee that all the
// backing files get deleted. For example if the system aborts after
// the database is updated but before we complete the process of
// deleting files.
while (textRows.moveToNext()) {
String path = textRows.getString(partDataColumn);
if (path != null) {
try {
InputStream is = new FileInputStream(path);
byte[] data = new byte[is.available()];
is.read(data);
EncodedStringValue v = new EncodedStringValue(data);
Method updateStringMethod = Class.forName(
"android.database.Cursor").getMethod(
"updateString",
new Class[] { int.class, String.class });
updateStringMethod.setAccessible(true);
updateStringMethod.invoke(textRows, new Object[] {
partTextColumn, v.getString() });
Method updateToNullMethod = Class.forName(
"android.database.Cursor").getMethod(
"updateToNull", new Class[] { int.class });
updateToNullMethod.setAccessible(true);
updateToNullMethod.invoke(textRows,
new Object[] { partDataColumn });
// textRows.updateString(partTextColumn,
// v.getString());
// textRows.updateToNull(partDataColumn);
is.close();
filesToDelete.add(path);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
Log.d(TAG, e.getMessage());
}
}
}
}
} finally {
Method commitUpdatesMethod;
try {
commitUpdatesMethod = Class.forName("android.database.Cursor")
.getMethod("updateString", new Class[] {});
commitUpdatesMethod.setAccessible(true);
commitUpdatesMethod.invoke(textRows, new Object[] {});
} catch (Exception e) {
Log.d(TAG, e.getMessage());
}
//textRows.commitUpdates();
for (String pathToDelete : filesToDelete) {
try {
(new File(pathToDelete)).delete();
} catch (SecurityException ex) {
Log.e(TAG, "unable to clean up old mms file for " + pathToDelete, ex);
}
}
if (textRows != null) {
textRows.close();
}
}
}
private void upgradeDatabaseToVersion47(SQLiteDatabase db) {
updateThreadsAttachmentColumn(db);
// add the update trigger for keeping the threads up to date.
db.execSQL(PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER);
}
private void updateThreadsAttachmentColumn(SQLiteDatabase db) {
// Set the values of that column correctly based on the current
// contents of the database.
db.execSQL("UPDATE threads SET has_attachment=1 WHERE _id IN " +
" (SELECT DISTINCT pdu.thread_id FROM part " +
" JOIN pdu ON pdu._id=part.mid " +
" WHERE part.ct != 'text/plain' AND part.ct != 'application/smil')");
}
}