短信和会话界面的数据库实现（sms and threads）

 目标：实现短信（或者类似微信、QQ之类的聊天工具）的会话界面（threads，左图）和短信界面（sms，右图）的数据库搭建。

图1 左为threads界面，右为sms界面

   两者界面实现都是通过“cursorAdapter+ListView”实现的，这里不再赘述，这里仅讨论数据库的构建。

1.    Android短信数据库的分析

   根据查看手机短信的数据库和构建数据库的android源码（参看附录），发现以下注意点：

1）短信内容存储在一张表上，即sms表；

2）会话内容存储于一张表上，即threads表；

3）thread表的自动更新（即sms表插入、删除、更新导致threads表更新）是通过构建触发器（trigger）机制。

2.    我的实现思路

   根据android源码的分析，我的思路很明显。仅设计两张表：sms表存储所有短信内容，threads表存储会话内容，threads表根据sms的变动自动更新。

1)      表结构

表1，短信表sms

字段

类型

备注

_id

integer

thread_id

integer

外键，会话id，一个联系人的会话一个id，与threads表内的_id关联

address

text

对方号码

read

integer

是否阅读           integer       default 0             0：未读， 1：已读  

type

integer

类型，1是接收到的，2是发出去的

body               

text

短报文内容 

date

日期

表2，会话表：threads

字段

类型

备注

_id

integer

address

text

对方号码

date

message_count

integer

信息条数

snippet

text

信息摘要

read

integer

是否阅读           integer       default 0             0：未读， 1：已读  

    建表过程（SQL语句，这里bdssms表即前面说的sms表，我做的是北斗短报文，所以改成了bdsms）：

CREATE TABLE bdssms (_id INTEGER PRIMARY KEY,thread_id INTEGER,address TEXT,read INTEGER DEFAULT 0,type INTEGER,body TEXT,date INTEGER);
CREATE TABLE threads(_id INTEGER PRIMARY KEY AUTOINCREMENT,address TEXT,snippet TEXT,message_count INTEGER DEFAULT 0,read INTEGER DEFAULT 0,date INTEGER);

2)      threads表如何自动更新

    这里涉及到3个触发器，模拟过程如下：

    建表后sms、threads都为空

第一条短信:

    sms收到第一条短信，取出该短信的发件人号码110，查看当前threads表中是否有该号码的会话。这时是没有。

  threads表插入一条空信息，这条空信息有个_id字段了（即1），取出后作为sms表该条信息的thread_id=1，然后插入短信到sms表，触发器会更新threads表所有字段（触发器1）

第二条短信：

  sms收到第二条短信，取出该短信的发件人号码110，查看当前threads表中是否有该号码的会话。这时是有。

  取出后作为sms表该条信息的thread_id=1，直接插入短信到sms表，触发器会更新threads表的所有字段（触发器1）

第三条短信：

  sms收到第三条短信，取出该短信的发件人号码999，查看当前threads表中是否有该号码的会话。这时是没有。

  threads表插入一条空信息，这条空信息有个_id字段了（即递增至2），取出后作为sms表该条信息的thread_id=2，然后插入短信到sms表，触发器会更新threads表所有字段（触发器1）。

 

…….

 

这时用户查看第一个会话（发件人110），由于接收的短信默认read都是0表示未读，cursorAdapter装载数据进入ListView,装载过程用于将read置为1,表示已读。read一旦更新为，则 触发器会更新threads表的read字段(触发器2)

用户删除该会话 （发件人110）中的某条短信，分为三种情况：如果该会话仅一条短信，则连同threads表中的该回话一同删除；多于一条短信，如果不是最后一条短信，则触发器会更新threads表的message_count字段(触发器3) ；多于一条短信，如果是最后一条短信，则删除最后一条，取出倒数第二条，然后删除倒数第二条，再插入倒数第二条，触发器会更新threads表所有字段（触发器1）。

**********************************************************************************************************************************

触发器1——sms插入数据后更新thread表的所有字段（SQL语句，这里bdssms表即前面说的sms表）

CREATE TRIGGER bdssms_update_threads_on_insert AFTER INSERT ON bdssmsBEGINUPDATE threads SETaddress=new.address,date=new.date,snippet=new.bodyWHERE _id=new.thread_id;UPDATE threads SET read =CASE (SELECT COUNT(*)FROM bdssmsWHERE bdssms.read= 0AND bdssms.thread_id= threads._id)WHEN 0 THEN 1ELSE 0ENDWHERE threads._id = new.thread_id;UPDATE threads SET message_count=(SELECT COUNT(bdssms._id) FROM bdssms LEFT JOIN threads ON threads._id =bdssms.thread_idWHERE bdssms.thread_id=new.thread_id)WHERE threads._id = new.thread_id;END;

触发器2——sms表接收的短信read默认为0表示未读，sms表中read更新后自动更新threads表的read字段（SQL语句，这里bdssms表即前面说的sms表）

CREATE TRIGGER bdssms_update_threads_on_update_read AFTER UPDATE OF read ON bdssmsBEGINUPDATE threads SET read =CASE (SELECT COUNT(*)FROM bdssmsWHERE bdssms.read= 0AND bdssms.thread_id= threads._id)WHEN 0 THEN 1ELSE 0ENDWHERE threads._id = new.thread_id;END;

触发器3——某会话条数多余1条，删除的短信不是最后一条，则更新threads表的message_count字段（SQL语句，这里bdssms表即前面说的sms表）

CREATE TRIGGER bdssms_update_threads_on_delete AFTER DELETE ON bdssmsBEGINUPDATE threads SET message_count=(SELECT COUNT(bdssms._id) FROM bdssms LEFT JOIN threads ON threads._id =bdssms.thread_idWHERE bdssms.thread_id=old.thread_id)WHERE threads._id = old.thread_id; END;

3.    具体实现（仅涉及关键代码）

1)      数据库构建（2张表3个trigger）

/** * @author Yanfeng Cao * */public class BDSsmsDBHelper extends SQLiteOpenHelper {    public static final String BDSsmsDBName = "bdssms.db3";    public static final String SMSTableName = "sms";    public static final String THREADSTableName = "threads";    public static final String BDSSMS_UPDATE_THREADS_ON_INSERT_TRIGGER = "bdssms_update_thread_on_insert";    public static final String BDSSMS_UPDATE_THREADS_ON_UPDATE_READ_TRIGGER = "bdssms_update_thread_on_update_read";    public static final String BDSSMS_UPDATE_THREADS_ON_DELETE_TRIGGER = "bdssms_update_threads_on_delete";     private static final String CREATE_SMS_TABLE = "CREATE TABLE "           + SMSTableName           + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, thread_id INTEGER NOT NULL, address TEXT NOT NULL, read INTEGER DEFAULT 0, type INTEGER, body TEXT,date TEXT);";    private static final String CREATE_THREADS_TABLE = "CREATE TABLE "           + THREADSTableName           + " (_id INTEGER PRIMARY KEY AUTOINCREMENT,address TEXT,snippet TEXT,message_count INTEGER DEFAULT 0,read INTEGER DEFAULT 0,date TEXT);";    private static final String BDSSMS_UPDATE_THREADS_ON_INSERT = "CREATE TRIGGER "           + BDSSMS_UPDATE_THREADS_ON_INSERT_TRIGGER           + " AFTER INSERT ON "           + SMSTableName           + " BEGIN "           + "UPDATE "           + THREADSTableName           + " SET address=new.address,date=new.date,snippet=new.body WHERE _id=new.thread_id;"           + "UPDATE "           + THREADSTableName           + " SET read=CASE (SELECT COUNT(*) FROM "           + SMSTableName           + " WHERE "           + SMSTableName           + ".read=0 AND "           + SMSTableName           + ".thread_id="           + THREADSTableName           + "._id) "           + "WHEN 0 THEN 1 "           + "ELSE 0"           + " END "           + "WHERE threads._id = new.thread_id;"           + "UPDATE "           + THREADSTableName           + " SET message_count=(SELECT COUNT( "           + SMSTableName           + "._id) FROM "           + SMSTableName           + " LEFT JOIN "           + THREADSTableName           + " ON "           + THREADSTableName           + "._id="           + SMSTableName           + ".thread_id"           + " WHERE "           + SMSTableName           + ".thread_id=new.thread_id)"           + "WHERE "           + THREADSTableName + "._id = new.thread_id;END;";     private static final String BDSSMS_UPDATE_THREADS_ON_UPDATE_READ = "CREATE TRIGGER "           + BDSSMS_UPDATE_THREADS_ON_UPDATE_READ_TRIGGER           + " AFTER UPDATE OF read ON "           + SMSTableName           + " BEGIN "           + "UPDATE "           + THREADSTableName           + " SET read=CASE (SELECT COUNT(*) FROM "           + SMSTableName           + " WHERE "           + SMSTableName           + ".read=0 AND "           + SMSTableName           + ".thread_id="           + THREADSTableName           + "._id) "           + "WHEN 0 THEN 1 "           + "ELSE 0" + " END " + "WHERE threads._id = new.thread_id;END;";     private static final String BDSSMS_UPDATE_THREADS_ON_DELETE = "CREATE TRIGGER "           + BDSSMS_UPDATE_THREADS_ON_DELETE_TRIGGER           + " AFTER DELETE ON "           + SMSTableName           + " BEGIN UPDATE "           + THREADSTableName           + " SET message_count=(SELECT COUNT("+SMSTableName+"._id) FROM "           + SMSTableName           + " LEFT JOIN "           + THREADSTableName           + " ON "           + THREADSTableName + "._id" + "=" + SMSTableName + ".thread_id"           + " WHERE "           + SMSTableName           + ".thread_id=old.thread_id)"           + " WHERE "           + THREADSTableName + "._id = old.thread_id;END;";      public BDSsmsDBHelper(Context context) {       super(context, BDSsmsDBName, null, 2);       // TODO Auto-generated constructor stub    }     /*     * (non-Javadoc)     *     * @see     * android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite     * .SQLiteDatabase)     */    @Override    public void onCreate(SQLiteDatabase db) {       // TODO Auto-generated method stub       db.execSQL(CREATE_SMS_TABLE);       db.execSQL(CREATE_THREADS_TABLE);       db.execSQL(BDSSMS_UPDATE_THREADS_ON_INSERT);       db.execSQL(BDSSMS_UPDATE_THREADS_ON_UPDATE_READ);       db.execSQL(BDSSMS_UPDATE_THREADS_ON_DELETE);    }    /*     * (non-Javadoc)     *     * @see     * android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite     * .SQLiteDatabase, int, int)     */    @Override    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {       // TODO Auto-generated method stub     } }

2)      收发到短信

private ContentValues contentValues;    @SuppressLint("SimpleDateFormat")    public void imitateMessage() {       String address = addressEditText.getText().toString();       String body = bodyEditText.getText().toString();       /*        * 查找threads表中是否已经有该address的会话，如果有的话获取该address的thread_id，        * 如果没有则插入一条空的记录并获取这条空记录的thread_id        */       String sql = "SELECT _id FROM threads WHERE address=" + address + ";";        Cursor cursor = db.rawQuery(sql, null);       int thread_id;       if (cursor.moveToFirst() == false) {           db.execSQL("insert into " + BDSsmsDBHelper.THREADSTableName                  + " ( snippet ) values (''); ");           Cursor c = db.rawQuery("SELECT _id FROM "+BDSsmsDBHelper.THREADSTableName, null);           c.moveToLast();           thread_id = c.getInt(0);//获取最后一行的thread_id       } else {           thread_id = cursor.getInt(0);       }       contentValues = new ContentValues();       contentValues.put("thread_id", thread_id);       contentValues.put("address", address);       contentValues.put("body", body);       String date = String.valueOf(System.currentTimeMillis());       contentValues.put("date", date);    }

3)      收到的短信更新read

 cursorAdapter一旦装载短信体，表明已读。

 @Override    public void bindView(View view, Context arg1, Cursor cursor) {       // TODO Auto-generated method stub       int _id=cursor.getInt(0);       db.execSQL("UPDATE "+BDSsmsDBHelper.SMSTableName+" SET read =1 WHERE _id ="+_id);    }

4)      删除短信更新threads

/*     * 删除分三种情况： 1）仅仅1条信息，删除本条同时将threads表中对应的会话删除 2）多余1条信息，本条不是最后一条，删除本条即可（     * 触发器bdssms_update_threads_on_delete会自动更新Threads表的message_count）     * 3）多余1条信息，本条是最后一条，暂存上一条信息，删除本条和上一条信息，然后将上一条信息插入     * （会激发插入类型的trigger来更新threads表） 注意：position是从0开始,跟cursor一致     */    private class ListActivityOnLongClickListener implements           AdapterView.OnItemLongClickListener {        @Override       public boolean onItemLongClick(AdapterView<?> parent, View view,              final int position, long id) {           // TODO Auto-generated method stub           Builder builder = new AlertDialog.Builder(BDSSMSActivity.this);           builder.setMessage("取消  or 删除?");           builder.setPositiveButton("删除", new OnClickListener() {               @SuppressWarnings("deprecation")              @Override              public void onClick(DialogInterface dialog, int which) {                  // TODO Auto-generated method stub                  int number = smsTableCursor.getCount();                  smsTableCursor.moveToPosition(position);                  int _id = smsTableCursor.getInt(0);// 对应smsTableCursor获取的两列                  Log.v("--onListItemClick--", "总数：" + number + ";当前要删除："                         + (position + 1));                  db.execSQL("DELETE FROM " + BDSsmsDBHelper.SMSTableName                         + " WHERE _id =" + _id);                   if (number == 1) {                     Log.v("--onListItemClick--", "总数为1");                     db.execSQL("DELETE FROM "                            + BDSsmsDBHelper.THREADSTableName                            + " WHERE address =" + address);                  } else if (number != 1 && number != position + 1) {                     Log.v("--onListItemClick--", "总数>1，本条不是最后一条！");                  } else {                     Log.v("--onListItemClick--", "总数>1，本条是最后一条！");                     smsTableCursor.moveToPrevious();                     int pre_id = smsTableCursor.getInt(0);                     int pre_thread_id = smsTableCursor.getInt(1);                     String pre_address = address;                     int pre_read = 1;                     int pre_type = smsTableCursor.getInt(4);                     String pre_body = smsTableCursor.getString(5);                     String pre_date = smsTableCursor.getString(6);                     db.execSQL("DELETE FROM " + BDSsmsDBHelper.SMSTableName                            + " WHERE _id =" + pre_id);                     ContentValues contentValues = new ContentValues();                     contentValues = new ContentValues();                     contentValues.put("_id", pre_id);                     contentValues.put("thread_id", pre_thread_id);                     contentValues.put("address", pre_address);                     contentValues.put("read", pre_read);                     contentValues.put("type", pre_type);                     contentValues.put("body", pre_body);                     contentValues.put("date", pre_date);                     db.insert(BDSsmsDBHelper.SMSTableName, null,                            contentValues);                  }                  smsTableCursor.requery();              }           });           builder.create().show();            return true;       }     }}

附录：Android短信数据库的源码MmsSmsDatabaseHelper

 GitSign inandroid / platform/packages/providers/TelephonyProvider / jb-release / . / src / com / android / providers /telephony / MmsSmsDatabaseHelper.javablob: d0d410e5b0b80a9178edf819a312381f464e485c [file] [log] [blame]1./*2.* Copyright (C) 2008 The Android Open Source Project3.*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 at7.*8.* http://www.apache.org/licenses/LICENSE-2.09.*10.* Unless required by applicable law or agreed to in writing, software11.* 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 and14.* limitations under the License.15.*/16.17.package com.android.providers.telephony;18.19.import java.io.IOException;20.import java.io.InputStream;21.import java.io.FileInputStream;22.import java.io.File;23.import java.util.ArrayList;24.import java.util.HashSet;25.import java.util.Iterator;26.27.import android.content.BroadcastReceiver;28.import android.content.ContentValues;29.import android.content.Context;30.import android.content.Intent;31.import android.content.IntentFilter;32.import android.database.Cursor;33.import android.database.sqlite.SQLiteDatabase;34.import android.database.sqlite.SQLiteOpenHelper;35.import android.provider.BaseColumns;36.import android.provider.Telephony;37.import android.provider.Telephony.Mms;38.import android.provider.Telephony.MmsSms;39.import android.provider.Telephony.Sms;40.import android.provider.Telephony.Threads;41.import android.provider.Telephony.Mms.Addr;42.import android.provider.Telephony.Mms.Part;43.import android.provider.Telephony.Mms.Rate;44.import android.provider.Telephony.MmsSms.PendingMessages;45.import android.util.Log;46.47.import com.google.android.mms.pdu.EncodedStringValue;48.import com.google.android.mms.pdu.PduHeaders;49.50.public class MmsSmsDatabaseHelper extends SQLiteOpenHelper {51.private static final String TAG = "MmsSmsDatabaseHelper";52.53.private static final String SMS_UPDATE_THREAD_READ_BODY =54." UPDATE threads SET read = " +55." CASE (SELECT COUNT(*)" +56." FROM sms" +57." WHERE " + Sms.READ + " = 0" +58." AND " + Sms.THREAD_ID + " = threads._id)" +59." WHEN 0 THEN 1" +60." ELSE 0" +61." END" +62." WHERE threads._id = new." + Sms.THREAD_ID + "; ";63.64.private static final String UPDATE_THREAD_COUNT_ON_NEW =65." UPDATE threads SET message_count = " +66." (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads " +67." ON threads._id = " + Sms.THREAD_ID +68." WHERE " + Sms.THREAD_ID + " = new.thread_id" +69." AND sms." + Sms.TYPE + " != 3) + " +70." (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads " +71." ON threads._id = " + Mms.THREAD_ID +72." WHERE " + Mms.THREAD_ID + " = new.thread_id" +73." AND (m_type=132 OR m_type=130 OR m_type=128)" +74." AND " + Mms.MESSAGE_BOX + " != 3) " +75." WHERE threads._id = new.thread_id; ";76.77.private static final String UPDATE_THREAD_COUNT_ON_OLD =78." UPDATE threads SET message_count = " +79." (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads " +80." ON threads._id = " + Sms.THREAD_ID +81." WHERE " + Sms.THREAD_ID + " = old.thread_id" +82." AND sms." + Sms.TYPE + " != 3) + " +83." (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads " +84." ON threads._id = " + Mms.THREAD_ID +85." WHERE " + Mms.THREAD_ID + " = old.thread_id" +86." AND (m_type=132 OR m_type=130 OR m_type=128)" +87." AND " + Mms.MESSAGE_BOX + " != 3) " +88." WHERE threads._id = old.thread_id; ";89.90.private static final String SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE =91."BEGIN" +92." UPDATE threads SET" +93." date = (strftime('%s','now') * 1000), " +94." snippet = new." + Sms.BODY + ", " +95." snippet_cs = 0" +96." WHERE threads._id = new." + Sms.THREAD_ID + "; " +97.UPDATE_THREAD_COUNT_ON_NEW +98.SMS_UPDATE_THREAD_READ_BODY +99."END;";100.101.private static final String PDU_UPDATE_THREAD_CONSTRAINTS =102." WHEN new." + Mms.MESSAGE_TYPE + "=" +103.PduHeaders.MESSAGE_TYPE_RETRIEVE_CONF +104." OR new." + Mms.MESSAGE_TYPE + "=" +105.PduHeaders.MESSAGE_TYPE_NOTIFICATION_IND +106." OR new." + Mms.MESSAGE_TYPE + "=" +107.PduHeaders.MESSAGE_TYPE_SEND_REQ + " ";108.109.// When looking in the pdu table for unread messages, only count messages that110.// are displayed to the user. The constants are defined in PduHeaders and could be used111.// here, but the string "(m_type=132 OR m_type=130 OR m_type=128)" is used throughout this112.// file and so it is used here to be consistent.113.// m_type=128 = MESSAGE_TYPE_SEND_REQ114.// m_type=130 = MESSAGE_TYPE_NOTIFICATION_IND115.// m_type=132 = MESSAGE_TYPE_RETRIEVE_CONF116.private static final String PDU_UPDATE_THREAD_READ_BODY =117." UPDATE threads SET read = " +118." CASE (SELECT COUNT(*)" +119." FROM " + MmsProvider.TABLE_PDU +120." WHERE " + Mms.READ + " = 0" +121." AND " + Mms.THREAD_ID + " = threads._id " +122." AND (m_type=132 OR m_type=130 OR m_type=128)) " +123." WHEN 0 THEN 1" +124." ELSE 0" +125." END" +126." WHERE threads._id = new." + Mms.THREAD_ID + "; ";127.128.private static final String PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE =129."BEGIN" +130." UPDATE threads SET" +131." date = (strftime('%s','now') * 1000), " +132." snippet = new." + Mms.SUBJECT + ", " +133." snippet_cs = new." + Mms.SUBJECT_CHARSET +134." WHERE threads._id = new." + Mms.THREAD_ID + "; " +135.UPDATE_THREAD_COUNT_ON_NEW +136.PDU_UPDATE_THREAD_READ_BODY +137."END;";138.139.private static final String UPDATE_THREAD_SNIPPET_SNIPPET_CS_ON_DELETE =140." UPDATE threads SET snippet = " +141." (SELECT snippet FROM" +142." (SELECT date * 1000 AS date, sub AS snippet, thread_id FROM pdu" +143." UNION SELECT date, body AS snippet, thread_id FROM sms)" +144." WHERE thread_id = OLD.thread_id ORDER BY date DESC LIMIT 1) " +145." WHERE threads._id = OLD.thread_id; " +146." UPDATE threads SET snippet_cs = " +147." (SELECT snippet_cs FROM" +148." (SELECT date * 1000 AS date, sub_cs AS snippet_cs, thread_id FROM pdu" +149." UNION SELECT date, 0 AS snippet_cs, thread_id FROM sms)" +150." WHERE thread_id = OLD.thread_id ORDER BY date DESC LIMIT 1) " +151." WHERE threads._id = OLD.thread_id; ";152.153.154.// When a part is inserted, if it is not text/plain or application/smil155.// (which both can exist with text-only MMSes), then there is an attachment.156.// Set has_attachment=1 in the threads table for the thread in question.157.private static final String PART_UPDATE_THREADS_ON_INSERT_TRIGGER =158."CREATE TRIGGER update_threads_on_insert_part " +159." AFTER INSERT ON part " +160." WHEN new.ct != 'text/plain' AND new.ct != 'application/smil' " +161." BEGIN " +162." UPDATE threads SET has_attachment=1 WHERE _id IN " +163." (SELECT pdu.thread_id FROM part JOIN pdu ON pdu._id=part.mid " +164." WHERE part._id=new._id LIMIT 1); " +165." END";166.167.// When the 'mid' column in the part table is updated, we need to run the trigger to update168.// the threads table's has_attachment column, if the part is an attachment.169.private static final String PART_UPDATE_THREADS_ON_UPDATE_TRIGGER =170."CREATE TRIGGER update_threads_on_update_part " +171." AFTER UPDATE of " + Part.MSG_ID + " ON part " +172." WHEN new.ct != 'text/plain' AND new.ct != 'application/smil' " +173." BEGIN " +174." UPDATE threads SET has_attachment=1 WHERE _id IN " +175." (SELECT pdu.thread_id FROM part JOIN pdu ON pdu._id=part.mid " +176." WHERE part._id=new._id LIMIT 1); " +177." END";178.179.180.// When a part is deleted (with the same non-text/SMIL constraint as when181.// we set has_attachment), update the threads table for all threads.182.// Unfortunately we cannot update only the thread that the part was183.// attached to, as it is possible that the part has been orphaned and184.// the message it was attached to is already gone.185.private static final String PART_UPDATE_THREADS_ON_DELETE_TRIGGER =186."CREATE TRIGGER update_threads_on_delete_part " +187." AFTER DELETE ON part " +188." WHEN old.ct != 'text/plain' AND old.ct != 'application/smil' " +189." BEGIN " +190." UPDATE threads SET has_attachment = " +191." CASE " +192." (SELECT COUNT(*) FROM part JOIN pdu " +193." WHERE pdu.thread_id = threads._id " +194." AND part.ct != 'text/plain' AND part.ct != 'application/smil' " +195." AND part.mid = pdu._id)" +196." WHEN 0 THEN 0 " +197." ELSE 1 " +198." END; " +199." END";200.201.// When the 'thread_id' column in the pdu table is updated, we need to run the trigger to update202.// the threads table's has_attachment column, if the message has an attachment in 'part' table203.private static final String PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER =204."CREATE TRIGGER update_threads_on_update_pdu " +205." AFTER UPDATE of thread_id ON pdu " +206." BEGIN " +207." UPDATE threads SET has_attachment=1 WHERE _id IN " +208." (SELECT pdu.thread_id FROM part JOIN pdu " +209." WHERE part.ct != 'text/plain' AND part.ct != 'application/smil' " +210." AND part.mid = pdu._id);" +211." END";212.213.private static MmsSmsDatabaseHelper sInstance = null;214.private static boolean sTriedAutoIncrement = false;215.private static boolean sFakeLowStorageTest = false; // for testing only216.217.static final String DATABASE_NAME = "mmssms.db";218.static final int DATABASE_VERSION = 55;219.private final Context mContext;220.private LowStorageMonitor mLowStorageMonitor;221.222.223.private MmsSmsDatabaseHelper(Context context) {224.super(context, DATABASE_NAME, null, DATABASE_VERSION);225.226.mContext = context;227.}228.229./**230.* Return a singleton helper for the combined MMS and SMS231.* database.232.*/233./* package */ static synchronized MmsSmsDatabaseHelper getInstance(Context context) {234.if (sInstance == null) {235.sInstance = new MmsSmsDatabaseHelper(context);236.}237.return sInstance;238.}239.240./**241.* Look through all the recipientIds referenced by the threads and then delete any242.* unreferenced rows from the canonical_addresses table.243.*/244.private static void removeUnferencedCanonicalAddresses(SQLiteDatabase db) {245.Cursor c = db.query("threads", new String[] { "recipient_ids" },246.null, null, null, null, null);247.if (c != null) {248.try {249.if (c.getCount() == 0) {250.// no threads, delete all addresses251.int rows = db.delete("canonical_addresses", null, null);252.} else {253.// Find all the referenced recipient_ids from the threads. recipientIds is254.// a space-separated list of recipient ids: "1 14 21"255.HashSet<Integer> recipientIds = new HashSet<Integer>();256.while (c.moveToNext()) {257.String[] recips = c.getString(0).split(" ");258.for (String recip : recips) {259.try {260.int recipientId = Integer.parseInt(recip);261.recipientIds.add(recipientId);262.} catch (Exception e) {263.}264.}265.}266.// Now build a selection string of all the unique recipient ids267.StringBuilder sb = new StringBuilder();268.Iterator<Integer> iter = recipientIds.iterator();269.while (iter.hasNext()) {270.sb.append("_id != " + iter.next());271.if (iter.hasNext()) {272.sb.append(" AND ");273.}274.}275.if (sb.length() > 0) {276.int rows = db.delete("canonical_addresses", sb.toString(), null);277.}278.}279.} finally {280.c.close();281.}282.}283.}284.285.public static void updateThread(SQLiteDatabase db, long thread_id) {286.if (thread_id < 0) {287.updateAllThreads(db, null, null);288.return;289.}290.291.// Delete the row for this thread in the threads table if292.// there are no more messages attached to it in either293.// the sms or pdu tables.294.int rows = db.delete("threads",295."_id = ? AND _id NOT IN" +296." (SELECT thread_id FROM sms " +297." UNION SELECT thread_id FROM pdu)",298.new String[] { String.valueOf(thread_id) });299.if (rows > 0) {300.// If this deleted a row, let's remove orphaned canonical_addresses and get outta here301.removeUnferencedCanonicalAddresses(db);302.return;303.}304.// Update the message count in the threads table as the sum305.// of all messages in both the sms and pdu tables.306.db.execSQL(307." UPDATE threads SET message_count = " +308." (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads " +309." ON threads._id = " + Sms.THREAD_ID +310." WHERE " + Sms.THREAD_ID + " = " + thread_id +311." AND sms." + Sms.TYPE + " != 3) + " +312." (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads " +313." ON threads._id = " + Mms.THREAD_ID +314." WHERE " + Mms.THREAD_ID + " = " + thread_id +315." AND (m_type=132 OR m_type=130 OR m_type=128)" +316." AND " + Mms.MESSAGE_BOX + " != 3) " +317." WHERE threads._id = " + thread_id + ";");318.319.// Update the date and the snippet (and its character set) in320.// the threads table to be that of the most recent message in321.// the thread.322.db.execSQL(323." UPDATE threads" +324." SET" +325." date =" +326." (SELECT date FROM" +327." (SELECT date * 1000 AS date, thread_id FROM pdu" +328." UNION SELECT date, thread_id FROM sms)" +329." WHERE thread_id = " + thread_id + " ORDER BY date DESC LIMIT 1)," +330." snippet =" +331." (SELECT snippet FROM" +332." (SELECT date * 1000 AS date, sub AS snippet, thread_id FROM pdu" +333." UNION SELECT date, body AS snippet, thread_id FROM sms)" +334." WHERE thread_id = " + thread_id + " ORDER BY date DESC LIMIT 1)," +335." snippet_cs =" +336." (SELECT snippet_cs FROM" +337." (SELECT date * 1000 AS date, sub_cs AS snippet_cs, thread_id FROM pdu" +338." UNION SELECT date, 0 AS snippet_cs, thread_id FROM sms)" +339." WHERE thread_id = " + thread_id + " ORDER BY date DESC LIMIT 1)" +340." WHERE threads._id = " + thread_id + ";");341.342.// Update the error column of the thread to indicate if there343.// are any messages in it that have failed to send.344.// First check to see if there are any messages with errors in this thread.345.String query = "SELECT thread_id FROM sms WHERE type=" +346.Telephony.TextBasedSmsColumns.MESSAGE_TYPE_FAILED +347." AND thread_id = " + thread_id +348." LIMIT 1";349.int setError = 0;350.Cursor c = db.rawQuery(query, null);351.if (c != null) {352.try {353.setError = c.getCount(); // Because of the LIMIT 1, count will be 1 or 0.354.} finally {355.c.close();356.}357.}358.// What's the current state of the error flag in the threads table?359.String errorQuery = "SELECT error FROM threads WHERE _id = " + thread_id;360.c = db.rawQuery(errorQuery, null);361.if (c != null) {362.try {363.if (c.moveToNext()) {364.int curError = c.getInt(0);365.if (curError != setError) {366.// The current thread error column differs, update it.367.db.execSQL("UPDATE threads SET error=" + setError +368." WHERE _id = " + thread_id);369.}370.}371.} finally {372.c.close();373.}374.}375.}376.377.public static void updateAllThreads(SQLiteDatabase db, String where, String[] whereArgs) {378.if (where == null) {379.where = "";380.} else {381.where = "WHERE (" + where + ")";382.}383.String query = "SELECT _id FROM threads WHERE _id IN " +384."(SELECT DISTINCT thread_id FROM sms " + where + ")";385.Cursor c = db.rawQuery(query, whereArgs);386.if (c != null) {387.try {388.while (c.moveToNext()) {389.updateThread(db, c.getInt(0));390.}391.} finally {392.c.close();393.}394.}395.// TODO: there are several db operations in this function. Lets wrap them in a396.// transaction to make it faster.397.// remove orphaned threads398.db.delete("threads",399."_id NOT IN (SELECT DISTINCT thread_id FROM sms where thread_id NOT NULL " +400."UNION SELECT DISTINCT thread_id FROM pdu where thread_id NOT NULL)", null);401.402.// remove orphaned canonical_addresses403.removeUnferencedCanonicalAddresses(db);404.}405.406.public static int deleteOneSms(SQLiteDatabase db, int message_id) {407.int thread_id = -1;408.// Find the thread ID that the specified SMS belongs to.409.Cursor c = db.query("sms", new String[] { "thread_id" },410."_id=" + message_id, null, null, null, null);411.if (c != null) {412.if (c.moveToFirst()) {413.thread_id = c.getInt(0);414.}415.c.close();416.}417.418.// Delete the specified message.419.int rows = db.delete("sms", "_id=" + message_id, null);420.if (thread_id > 0) {421.// Update its thread.422.updateThread(db, thread_id);423.}424.return rows;425.}[e-mail] void onCreate(SQLiteDatabase db) {429.createMmsTables(db);430.createSmsTables(db);431.createCommonTables(db);432.createCommonTriggers(db);433.createMmsTriggers(db);434.createWordsTables(db);435.createIndices(db);436.}437.438.// When upgrading the database we need to populate the words439.// table with the rows out of sms and part.440.private void populateWordsTable(SQLiteDatabase db) {441.final String TABLE_WORDS = "words";442.{443.Cursor smsRows = db.query(444."sms",445.new String[] { Sms._ID, Sms.BODY },446.null,447.null,448.null,449.null,450.null);451.try {452.if (smsRows != null) {453.smsRows.moveToPosition(-1);454.ContentValues cv = new ContentValues();455.while (smsRows.moveToNext()) {456.cv.clear();457.458.long id = smsRows.getLong(0); // 0 for Sms._ID459.String body = smsRows.getString(1); // 1 for Sms.BODY460.461.cv.put(Telephony.MmsSms.WordsTable.ID, id);462.cv.put(Telephony.MmsSms.WordsTable.INDEXED_TEXT, body);463.cv.put(Telephony.MmsSms.WordsTable.SOURCE_ROW_ID, id);464.cv.put(Telephony.MmsSms.WordsTable.TABLE_ID, 1);465.db.insert(TABLE_WORDS, Telephony.MmsSms.WordsTable.INDEXED_TEXT, cv);466.}467.}468.} finally {469.if (smsRows != null) {470.smsRows.close();471.}472.}473.}474.475.{476.Cursor mmsRows = db.query(477."part",478.new String[] { Part._ID, Part.TEXT },479."ct = 'text/plain'",480.null,481.null,482.null,483.null);484.try {485.if (mmsRows != null) {486.mmsRows.moveToPosition(-1);487.ContentValues cv = new ContentValues();488.while (mmsRows.moveToNext()) {489.cv.clear();490.491.long id = mmsRows.getLong(0); // 0 for Part._ID492.String body = mmsRows.getString(1); // 1 for Part.TEXT493.494.cv.put(Telephony.MmsSms.WordsTable.ID, id);495.cv.put(Telephony.MmsSms.WordsTable.INDEXED_TEXT, body);496.cv.put(Telephony.MmsSms.WordsTable.SOURCE_ROW_ID, id);497.cv.put(Telephony.MmsSms.WordsTable.TABLE_ID, 1);498.db.insert(TABLE_WORDS, Telephony.MmsSms.WordsTable.INDEXED_TEXT, cv);499.}500.}501.} finally {502.if (mmsRows != null) {503.mmsRows.close();504.}505.}506.}507.}508.509.private void createWordsTables(SQLiteDatabase db) {510.try {511.db.execSQL("CREATE VIRTUAL TABLE words USING FTS3 (_id INTEGER PRIMARY KEY, index_text TEXT, source_id INTEGER, table_to_use INTEGER);");512.513.// monitor the sms table514.// NOTE don't handle inserts using a trigger because it has an unwanted515.// side effect: the value returned for the last row ends up being the516.// id of one of the trigger insert not the original row insert.517.// Handle inserts manually in the provider.518.db.execSQL("CREATE TRIGGER sms_words_update AFTER UPDATE ON sms BEGIN UPDATE words " +519." SET index_text = NEW.body WHERE (source_id=NEW._id AND table_to_use=1); " +520." END;");521.db.execSQL("CREATE TRIGGER sms_words_delete AFTER DELETE ON sms BEGIN DELETE FROM " +522." words WHERE source_id = OLD._id AND table_to_use = 1; END;");523.524.// monitor the mms table525.db.execSQL("CREATE TRIGGER mms_words_update AFTER UPDATE ON part BEGIN UPDATE words " +526." SET index_text = NEW.text WHERE (source_id=NEW._id AND table_to_use=2); " +527." END;");528.db.execSQL("CREATE TRIGGER mms_words_delete AFTER DELETE ON part BEGIN DELETE FROM " +529." words WHERE source_id = OLD._id AND table_to_use = 2; END;");530.531.populateWordsTable(db);532.} catch (Exception ex) {533.Log.e(TAG, "got exception creating words table: " + ex.toString());534.}535.}536.537.private void createIndices(SQLiteDatabase db) {538.createThreadIdIndex(db);539.}540.541.private void createThreadIdIndex(SQLiteDatabase db) {542.try {543.db.execSQL("CREATE INDEX IF NOT EXISTS typeThreadIdIndex ON sms" +544." (type, thread_id);");545.} catch (Exception ex) {546.Log.e(TAG, "got exception creating indices: " + ex.toString());547.}548.}549.550.private void createMmsTables(SQLiteDatabase db) {551.// N.B.: Whenever the columns here are changed, the columns in552.// {@ref MmsSmsProvider} must be changed to match.553.db.execSQL("CREATE TABLE " + MmsProvider.TABLE_PDU + " (" +554.Mms._ID + " INTEGER PRIMARY KEY," +555.Mms.THREAD_ID + " INTEGER," +556.Mms.DATE + " INTEGER," +557.Mms.DATE_SENT + " INTEGER DEFAULT 0," +558.Mms.MESSAGE_BOX + " INTEGER," +559.Mms.READ + " INTEGER DEFAULT 0," +560.Mms.MESSAGE_ID + " TEXT," +561.Mms.SUBJECT + " TEXT," +562.Mms.SUBJECT_CHARSET + " INTEGER," +563.Mms.CONTENT_TYPE + " TEXT," +564.Mms.CONTENT_LOCATION + " TEXT," +565.Mms.EXPIRY + " INTEGER," +566.Mms.MESSAGE_CLASS + " TEXT," +567.Mms.MESSAGE_TYPE + " INTEGER," +568.Mms.MMS_VERSION + " INTEGER," +569.Mms.MESSAGE_SIZE + " INTEGER," +570.Mms.PRIORITY + " INTEGER," +571.Mms.READ_REPORT + " INTEGER," +572.Mms.REPORT_ALLOWED + " INTEGER," +573.Mms.RESPONSE_STATUS + " INTEGER," +574.Mms.STATUS + " INTEGER," +575.Mms.TRANSACTION_ID + " TEXT," +576.Mms.RETRIEVE_STATUS + " INTEGER," +577.Mms.RETRIEVE_TEXT + " TEXT," +578.Mms.RETRIEVE_TEXT_CHARSET + " INTEGER," +579.Mms.READ_STATUS + " INTEGER," +580.Mms.CONTENT_CLASS + " INTEGER," +581.Mms.RESPONSE_TEXT + " TEXT," +582.Mms.DELIVERY_TIME + " INTEGER," +583.Mms.DELIVERY_REPORT + " INTEGER," +584.Mms.LOCKED + " INTEGER DEFAULT 0," +585.Mms.SEEN + " INTEGER DEFAULT 0" +586.");");587.588.db.execSQL("CREATE TABLE " + MmsProvider.TABLE_ADDR + " (" +589.Addr._ID + " INTEGER PRIMARY KEY," +590.Addr.MSG_ID + " INTEGER," +591.Addr.CONTACT_ID + " INTEGER," +592.Addr.ADDRESS + " TEXT," +593.Addr.TYPE + " INTEGER," +594.Addr.CHARSET + " INTEGER);");595.596.db.execSQL("CREATE TABLE " + MmsProvider.TABLE_PART + " (" +597.Part._ID + " INTEGER PRIMARY KEY," +598.Part.MSG_ID + " INTEGER," +599.Part.SEQ + " INTEGER DEFAULT 0," +600.Part.CONTENT_TYPE + " TEXT," +601.Part.NAME + " TEXT," +602.Part.CHARSET + " INTEGER," +603.Part.CONTENT_DISPOSITION + " TEXT," +604.Part.FILENAME + " TEXT," +605.Part.CONTENT_ID + " TEXT," +606.Part.CONTENT_LOCATION + " TEXT," +607.Part.CT_START + " INTEGER," +608.Part.CT_TYPE + " TEXT," +609.Part._DATA + " TEXT," +610.Part.TEXT + " TEXT);");611.612.db.execSQL("CREATE TABLE " + MmsProvider.TABLE_RATE + " (" +613.Rate.SENT_TIME + " INTEGER);");614.615.db.execSQL("CREATE TABLE " + MmsProvider.TABLE_DRM + " (" +616.BaseColumns._ID + " INTEGER PRIMARY KEY," +617."_data TEXT);");618.}619.620.private void createMmsTriggers(SQLiteDatabase db) {621.// Cleans up parts when a MM is deleted.622.db.execSQL("CREATE TRIGGER part_cleanup DELETE ON " + MmsProvider.TABLE_PDU + " " +623."BEGIN " +624." DELETE FROM " + MmsProvider.TABLE_PART +625." WHERE " + Part.MSG_ID + "=old._id;" +626."END;");627.628.// Cleans up address info when a MM is deleted.629.db.execSQL("CREATE TRIGGER addr_cleanup DELETE ON " + MmsProvider.TABLE_PDU + " " +630."BEGIN " +631." DELETE FROM " + MmsProvider.TABLE_ADDR +632." WHERE " + Addr.MSG_ID + "=old._id;" +633."END;");634.635.// Delete obsolete delivery-report, read-report while deleting their636.// associated Send.req.637.db.execSQL("CREATE TRIGGER cleanup_delivery_and_read_report " +638."AFTER DELETE ON " + MmsProvider.TABLE_PDU + " " +639."WHEN old." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_SEND_REQ + " " +640."BEGIN " +641." DELETE FROM " + MmsProvider.TABLE_PDU +642." WHERE (" + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_DELIVERY_IND +643." OR " + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_READ_ORIG_IND +644.")" +645." AND " + Mms.MESSAGE_ID + "=old." + Mms.MESSAGE_ID + "; " +646."END;");647.648.// Update threads table to indicate whether attachments exist when649.// parts are inserted or deleted.650.db.execSQL(PART_UPDATE_THREADS_ON_INSERT_TRIGGER);651.db.execSQL(PART_UPDATE_THREADS_ON_UPDATE_TRIGGER);652.db.execSQL(PART_UPDATE_THREADS_ON_DELETE_TRIGGER);653.db.execSQL(PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER);654.}655.656.private void createSmsTables(SQLiteDatabase db) {657.// N.B.: Whenever the columns here are changed, the columns in658.// {@ref MmsSmsProvider} must be changed to match.659.db.execSQL("CREATE TABLE sms (" +660."_id INTEGER PRIMARY KEY," +661."thread_id INTEGER," +662."address TEXT," +663."person INTEGER," +664."date INTEGER," +665."date_sent INTEGER DEFAULT 0," +666."protocol INTEGER," +667."read INTEGER DEFAULT 0," +668."status INTEGER DEFAULT -1," + // a TP-Status value669.// or -1 if it670.// status hasn't671.// been received672."type INTEGER," +673."reply_path_present INTEGER," +674."subject TEXT," +675."body TEXT," +676."service_center TEXT," +677."locked INTEGER DEFAULT 0," +678."error_code INTEGER DEFAULT 0," +679."seen INTEGER DEFAULT 0" +680.");");681.682./**683.* This table is used by the SMS dispatcher to hold684.* incomplete partial messages until all the parts arrive.685.*/686.db.execSQL("CREATE TABLE raw (" +687."_id INTEGER PRIMARY KEY," +688."date INTEGER," +689."reference_number INTEGER," + // one per full message690."count INTEGER," + // the number of parts691."sequence INTEGER," + // the part number of this message692."destination_port INTEGER," +693."address TEXT," +694."pdu TEXT);"); // the raw PDU for this part695.696.db.execSQL("CREATE TABLE attachments (" +697."sms_id INTEGER," +698."content_url TEXT," +699."offset INTEGER);");700.701./**702.* This table is used by the SMS dispatcher to hold pending703.* delivery status report intents.704.*/705.db.execSQL("CREATE TABLE sr_pending (" +706."reference_number INTEGER," +707."action TEXT," +708."data TEXT);");709.}710.711.private void createCommonTables(SQLiteDatabase db) {712.// TODO Ensure that each entry is removed when the last use of713.// any address equivalent to its address is removed.714.715./**716.* This table maps the first instance seen of any particular717.* MMS/SMS address to an ID, which is then used as its718.* canonical representation. If the same address or an719.* equivalent address (as determined by our Sqlite720.* PHONE_NUMBERS_EQUAL extension) is seen later, this same ID721.* will be used. The _id is created with AUTOINCREMENT so it722.* will never be reused again if a recipient is deleted.723.*/724.db.execSQL("CREATE TABLE canonical_addresses (" +725."_id INTEGER PRIMARY KEY AUTOINCREMENT," +726."address TEXT);");727.728./**729.* This table maps the subject and an ordered set of recipient730.* IDs, separated by spaces, to a unique thread ID. The IDs731.* come from the canonical_addresses table. This works732.* because messages are considered to be part of the same733.* thread if they have the same subject (or a null subject)734.* and the same set of recipients.735.*/736.db.execSQL("CREATE TABLE threads (" +737.Threads._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +738.Threads.DATE + " INTEGER DEFAULT 0," +739.Threads.MESSAGE_COUNT + " INTEGER DEFAULT 0," +740.Threads.RECIPIENT_IDS + " TEXT," +741.Threads.SNIPPET + " TEXT," +742.Threads.SNIPPET_CHARSET + " INTEGER DEFAULT 0," +743.Threads.READ + " INTEGER DEFAULT 1," +744.Threads.TYPE + " INTEGER DEFAULT 0," +745.Threads.ERROR + " INTEGER DEFAULT 0," +746.Threads.HAS_ATTACHMENT + " INTEGER DEFAULT 0);");747.748./**749.* This table stores the queue of messages to be sent/downloaded.750.*/751.db.execSQL("CREATE TABLE " + MmsSmsProvider.TABLE_PENDING_MSG +" (" +752.PendingMessages._ID + " INTEGER PRIMARY KEY," +753.PendingMessages.PROTO_TYPE + " INTEGER," +754.PendingMessages.MSG_ID + " INTEGER," +755.PendingMessages.MSG_TYPE + " INTEGER," +756.PendingMessages.ERROR_TYPE + " INTEGER," +757.PendingMessages.ERROR_CODE + " INTEGER," +758.PendingMessages.RETRY_INDEX + " INTEGER NOT NULL DEFAULT 0," +759.PendingMessages.DUE_TIME + " INTEGER," +760.PendingMessages.LAST_TRY + " INTEGER);");761.762.}763.764.// TODO Check the query plans for these triggers.765.private void createCommonTriggers(SQLiteDatabase db) {766.// Updates threads table whenever a message is added to pdu.767.db.execSQL("CREATE TRIGGER pdu_update_thread_on_insert AFTER INSERT ON " +768.MmsProvider.TABLE_PDU + " " +769.PDU_UPDATE_THREAD_CONSTRAINTS +770.PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE);771.772.// Updates threads table whenever a message is added to sms.773.db.execSQL("CREATE TRIGGER sms_update_thread_on_insert AFTER INSERT ON sms " +774.SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE);775.776.// Updates threads table whenever a message in pdu is updated.777.db.execSQL("CREATE TRIGGER pdu_update_thread_date_subject_on_update AFTER" +778." UPDATE OF " + Mms.DATE + ", " + Mms.SUBJECT + ", " + Mms.MESSAGE_BOX +779." ON " + MmsProvider.TABLE_PDU + " " +780.PDU_UPDATE_THREAD_CONSTRAINTS +781.PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE);782.783.// Updates threads table whenever a message in sms is updated.784.db.execSQL("CREATE TRIGGER sms_update_thread_date_subject_on_update AFTER" +785." UPDATE OF " + Sms.DATE + ", " + Sms.BODY + ", " + Sms.TYPE +786." ON sms " +787.SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE);788.789.// Updates threads table whenever a message in pdu is updated.790.db.execSQL("CREATE TRIGGER pdu_update_thread_read_on_update AFTER" +791." UPDATE OF " + Mms.READ +792." ON " + MmsProvider.TABLE_PDU + " " +793.PDU_UPDATE_THREAD_CONSTRAINTS +794."BEGIN " +795.PDU_UPDATE_THREAD_READ_BODY +796."END;");797.798.// Updates threads table whenever a message in sms is updated.799.db.execSQL("CREATE TRIGGER sms_update_thread_read_on_update AFTER" +800." UPDATE OF " + Sms.READ +801." ON sms " +802."BEGIN " +803.SMS_UPDATE_THREAD_READ_BODY +804."END;");805.806.// Update threads table whenever a message in pdu is deleted807.db.execSQL("CREATE TRIGGER pdu_update_thread_on_delete " +808."AFTER DELETE ON pdu " +809."BEGIN " +810." UPDATE threads SET " +811." date = (strftime('%s','now') * 1000)" +812." WHERE threads._id = old." + Mms.THREAD_ID + "; " +813.UPDATE_THREAD_COUNT_ON_OLD +814.UPDATE_THREAD_SNIPPET_SNIPPET_CS_ON_DELETE +815."END;");816.817.// As of DATABASE_VERSION 55, we've removed these triggers that delete empty threads.818.// These triggers interfere with saving drafts on brand new threads. Instead of819.// triggers cleaning up empty threads, the empty threads should be cleaned up by820.// an explicit call to delete with Threads.OBSOLETE_THREADS_URI.821.822.// // When the last message in a thread is deleted, these823.// // triggers ensure that the entry for its thread ID is removed824.// // from the threads table.825.// db.execSQL("CREATE TRIGGER delete_obsolete_threads_pdu " +826.// "AFTER DELETE ON pdu " +827.// "BEGIN " +828.// " DELETE FROM threads " +829.// " WHERE " +830.// " _id = old.thread_id " +831.// " AND _id NOT IN " +832.// " (SELECT thread_id FROM sms " +833.// " UNION SELECT thread_id from pdu); " +834.// "END;");835.//836.// db.execSQL("CREATE TRIGGER delete_obsolete_threads_when_update_pdu " +837.// "AFTER UPDATE OF " + Mms.THREAD_ID + " ON pdu " +838.// "WHEN old." + Mms.THREAD_ID + " != new." + Mms.THREAD_ID + " " +839.// "BEGIN " +840.// " DELETE FROM threads " +841.// " WHERE " +842.// " _id = old.thread_id " +843.// " AND _id NOT IN " +844.// " (SELECT thread_id FROM sms " +845.// " UNION SELECT thread_id from pdu); " +846.// "END;");847.848.// Insert pending status for M-Notification.ind or M-ReadRec.ind849.// when they are inserted into Inbox/Outbox.850.db.execSQL("CREATE TRIGGER insert_mms_pending_on_insert " +851."AFTER INSERT ON pdu " +852."WHEN new." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_NOTIFICATION_IND +853." OR new." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_READ_REC_IND +854." " +855."BEGIN " +856." INSERT INTO " + MmsSmsProvider.TABLE_PENDING_MSG +857." (" + PendingMessages.PROTO_TYPE + "," +858." " + PendingMessages.MSG_ID + "," +859." " + PendingMessages.MSG_TYPE + "," +860." " + PendingMessages.ERROR_TYPE + "," +861." " + PendingMessages.ERROR_CODE + "," +862." " + PendingMessages.RETRY_INDEX + "," +863." " + PendingMessages.DUE_TIME + ") " +864." VALUES " +865." (" + MmsSms.MMS_PROTO + "," +866." new." + BaseColumns._ID + "," +867." new." + Mms.MESSAGE_TYPE + ",0,0,0,0);" +868."END;");869.870.// Insert pending status for M-Send.req when it is moved into Outbox.871.db.execSQL("CREATE TRIGGER insert_mms_pending_on_update " +872."AFTER UPDATE ON pdu " +873."WHEN new." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_SEND_REQ +874." AND new." + Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_OUTBOX +875." AND old." + Mms.MESSAGE_BOX + "!=" + Mms.MESSAGE_BOX_OUTBOX + " " +876."BEGIN " +877." INSERT INTO " + MmsSmsProvider.TABLE_PENDING_MSG +878." (" + PendingMessages.PROTO_TYPE + "," +879." " + PendingMessages.MSG_ID + "," +880." " + PendingMessages.MSG_TYPE + "," +881." " + PendingMessages.ERROR_TYPE + "," +882." " + PendingMessages.ERROR_CODE + "," +883." " + PendingMessages.RETRY_INDEX + "," +884." " + PendingMessages.DUE_TIME + ") " +885." VALUES " +886." (" + MmsSms.MMS_PROTO + "," +887." new." + BaseColumns._ID + "," +888." new." + Mms.MESSAGE_TYPE + ",0,0,0,0);" +889."END;");890.891.// When a message is moved out of Outbox, delete its pending status.892.db.execSQL("CREATE TRIGGER delete_mms_pending_on_update " +893."AFTER UPDATE ON " + MmsProvider.TABLE_PDU + " " +894."WHEN old." + Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_OUTBOX +895." AND new." + Mms.MESSAGE_BOX + "!=" + Mms.MESSAGE_BOX_OUTBOX + " " +896."BEGIN " +897." DELETE FROM " + MmsSmsProvider.TABLE_PENDING_MSG +898." WHERE " + PendingMessages.MSG_ID + "=new._id; " +899."END;");900.901.// Delete pending status for a message when it is deleted.902.db.execSQL("CREATE TRIGGER delete_mms_pending_on_delete " +903."AFTER DELETE ON " + MmsProvider.TABLE_PDU + " " +904."BEGIN " +905." DELETE FROM " + MmsSmsProvider.TABLE_PENDING_MSG +906." WHERE " + PendingMessages.MSG_ID + "=old._id; " +907."END;");908.909.// TODO Add triggers for SMS retry-status management.910.911.// Update the error flag of threads when the error type of912.// a pending MM is updated.913.db.execSQL("CREATE TRIGGER update_threads_error_on_update_mms " +914." AFTER UPDATE OF err_type ON pending_msgs " +915." WHEN (OLD.err_type < 10 AND NEW.err_type >= 10)" +916." OR (OLD.err_type >= 10 AND NEW.err_type < 10) " +917."BEGIN" +918." UPDATE threads SET error = " +919." CASE" +920." WHEN NEW.err_type >= 10 THEN error + 1" +921." ELSE error - 1" +922." END " +923." WHERE _id =" +924." (SELECT DISTINCT thread_id" +925." FROM pdu" +926." WHERE _id = NEW.msg_id); " +927."END;");928.929.// Update the error flag of threads when delete pending message.930.db.execSQL("CREATE TRIGGER update_threads_error_on_delete_mms " +931." BEFORE DELETE ON pdu" +932." WHEN OLD._id IN (SELECT DISTINCT msg_id" +933." FROM pending_msgs" +934." WHERE err_type >= 10) " +935."BEGIN " +936." UPDATE threads SET error = error - 1" +937." WHERE _id = OLD.thread_id; " +938."END;");939.940.// Update the error flag of threads while moving an MM out of Outbox,941.// which was failed to be sent permanently.942.db.execSQL("CREATE TRIGGER update_threads_error_on_move_mms " +943." BEFORE UPDATE OF msg_box ON pdu " +944." WHEN (OLD.msg_box = 4 AND NEW.msg_box != 4) " +945." AND (OLD._id IN (SELECT DISTINCT msg_id" +946." FROM pending_msgs" +947." WHERE err_type >= 10)) " +948."BEGIN " +949." UPDATE threads SET error = error - 1" +950." WHERE _id = OLD.thread_id; " +951."END;");952.953.// Update the error flag of threads after a text message was954.// failed to send/receive.955.db.execSQL("CREATE TRIGGER update_threads_error_on_update_sms " +956." AFTER UPDATE OF type ON sms" +957." WHEN (OLD.type != 5 AND NEW.type = 5)" +958." OR (OLD.type = 5 AND NEW.type != 5) " +959."BEGIN " +960." UPDATE threads SET error = " +961." CASE" +962." WHEN NEW.type = 5 THEN error + 1" +963." ELSE error - 1" +964." END " +965." WHERE _id = NEW.thread_id; " +966."END;");967.}[e-mail] void onUpgrade(SQLiteDatabase db, int oldVersion, int currentVersion) {971.Log.w(TAG, "Upgrading database from version " + oldVersion972.+ " to " + currentVersion + ".");973.974.switch (oldVersion) {975.case 40:976.if (currentVersion <= 40) {977.return;978.}979.980.db.beginTransaction();981.try {982.upgradeDatabaseToVersion41(db);983.db.setTransactionSuccessful();984.} catch (Throwable ex) {985.Log.e(TAG, ex.getMessage(), ex);986.break;987.} finally {988.db.endTransaction();989.}990.// fall through991.case 41:992.if (currentVersion <= 41) {993.return;994.}995.996.db.beginTransaction();997.try {998.upgradeDatabaseToVersion42(db);999.db.setTransactionSuccessful();1000.} catch (Throwable ex) {1001.Log.e(TAG, ex.getMessage(), ex);1002.break;1003.} finally {1004.db.endTransaction();1005.}1006.// fall through1007.case 42:1008.if (currentVersion <= 42) {1009.return;1010.}1011.1012.db.beginTransaction();1013.try {1014.upgradeDatabaseToVersion43(db);1015.db.setTransactionSuccessful();1016.} catch (Throwable ex) {1017.Log.e(TAG, ex.getMessage(), ex);1018.break;1019.} finally {1020.db.endTransaction();1021.}1022.// fall through1023.case 43:1024.if (currentVersion <= 43) {1025.return;1026.}1027.1028.db.beginTransaction();1029.try {1030.upgradeDatabaseToVersion44(db);1031.db.setTransactionSuccessful();1032.} catch (Throwable ex) {1033.Log.e(TAG, ex.getMessage(), ex);1034.break;1035.} finally {1036.db.endTransaction();1037.}1038.// fall through1039.case 44:1040.if (currentVersion <= 44) {1041.return;1042.}1043.1044.db.beginTransaction();1045.try {1046.upgradeDatabaseToVersion45(db);1047.db.setTransactionSuccessful();1048.} catch (Throwable ex) {1049.Log.e(TAG, ex.getMessage(), ex);1050.break;1051.} finally {1052.db.endTransaction();1053.}1054.// fall through1055.case 45:1056.if (currentVersion <= 45) {1057.return;1058.}1059.db.beginTransaction();1060.try {1061.upgradeDatabaseToVersion46(db);1062.db.setTransactionSuccessful();1063.} catch (Throwable ex) {1064.Log.e(TAG, ex.getMessage(), ex);1065.break;1066.} finally {1067.db.endTransaction();1068.}1069.// fall through1070.case 46:1071.if (currentVersion <= 46) {1072.return;1073.}1074.1075.db.beginTransaction();1076.try {1077.upgradeDatabaseToVersion47(db);1078.db.setTransactionSuccessful();1079.} catch (Throwable ex) {1080.Log.e(TAG, ex.getMessage(), ex);1081.break;1082.} finally {1083.db.endTransaction();1084.}1085.// fall through1086.case 47:1087.if (currentVersion <= 47) {1088.return;1089.}1090.1091.db.beginTransaction();1092.try {1093.upgradeDatabaseToVersion48(db);1094.db.setTransactionSuccessful();1095.} catch (Throwable ex) {1096.Log.e(TAG, ex.getMessage(), ex);1097.break;1098.} finally {1099.db.endTransaction();1100.}1101.// fall through1102.case 48:1103.if (currentVersion <= 48) {1104.return;1105.}1106.1107.db.beginTransaction();1108.try {1109.createWordsTables(db);1110.db.setTransactionSuccessful();1111.} catch (Throwable ex) {1112.Log.e(TAG, ex.getMessage(), ex);1113.break;1114.} finally {1115.db.endTransaction();1116.}1117.// fall through1118.case 49:1119.if (currentVersion <= 49) {1120.return;1121.}1122.db.beginTransaction();1123.try {1124.createThreadIdIndex(db);1125.db.setTransactionSuccessful();1126.} catch (Throwable ex) {1127.Log.e(TAG, ex.getMessage(), ex);1128.break; // force to destroy all old data;1129.} finally {1130.db.endTransaction();1131.}1132.// fall through1133.case 50:1134.if (currentVersion <= 50) {1135.return;1136.}1137.1138.db.beginTransaction();1139.try {1140.upgradeDatabaseToVersion51(db);1141.db.setTransactionSuccessful();1142.} catch (Throwable ex) {1143.Log.e(TAG, ex.getMessage(), ex);1144.break;1145.} finally {1146.db.endTransaction();1147.}1148.// fall through1149.case 51:1150.if (currentVersion <= 51) {1151.return;1152.}1153.// 52 was adding a new meta_data column, but that was removed.1154.// fall through1155.case 52:1156.if (currentVersion <= 52) {1157.return;1158.}1159.1160.db.beginTransaction();1161.try {1162.upgradeDatabaseToVersion53(db);1163.db.setTransactionSuccessful();1164.} catch (Throwable ex) {1165.Log.e(TAG, ex.getMessage(), ex);1166.break;1167.} finally {1168.db.endTransaction();1169.}1170.// fall through1171.case 53:1172.if (currentVersion <= 53) {1173.return;1174.}1175.1176.db.beginTransaction();1177.try {1178.upgradeDatabaseToVersion54(db);1179.db.setTransactionSuccessful();1180.} catch (Throwable ex) {1181.Log.e(TAG, ex.getMessage(), ex);1182.break;1183.} finally {1184.db.endTransaction();1185.}1186.// fall through1187.case 54:1188.if (currentVersion <= 54) {1189.return;1190.}1191.1192.db.beginTransaction();1193.try {1194.upgradeDatabaseToVersion55(db);1195.db.setTransactionSuccessful();1196.} catch (Throwable ex) {1197.Log.e(TAG, ex.getMessage(), ex);1198.break;1199.} finally {1200.db.endTransaction();1201.}1202.return;1203.}1204.1205.Log.e(TAG, "Destroying all old data.");1206.dropAll(db);1207.onCreate(db);1208.}1209.1210.private void dropAll(SQLiteDatabase db) {1211.// Clean the database out in order to start over from scratch.1212.// We don't need to drop our triggers here because SQLite automatically1213.// drops a trigger when its attached database is dropped.1214.db.execSQL("DROP TABLE IF EXISTS canonical_addresses");1215.db.execSQL("DROP TABLE IF EXISTS threads");1216.db.execSQL("DROP TABLE IF EXISTS " + MmsSmsProvider.TABLE_PENDING_MSG);1217.db.execSQL("DROP TABLE IF EXISTS sms");1218.db.execSQL("DROP TABLE IF EXISTS raw");1219.db.execSQL("DROP TABLE IF EXISTS attachments");1220.db.execSQL("DROP TABLE IF EXISTS thread_ids");1221.db.execSQL("DROP TABLE IF EXISTS sr_pending");1222.db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_PDU + ";");1223.db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_ADDR + ";");1224.db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_PART + ";");1225.db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_RATE + ";");1226.db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_DRM + ";");1227.}1228.1229.private void upgradeDatabaseToVersion41(SQLiteDatabase db) {1230.db.execSQL("DROP TRIGGER IF EXISTS update_threads_error_on_move_mms");1231.db.execSQL("CREATE TRIGGER update_threads_error_on_move_mms " +1232." BEFORE UPDATE OF msg_box ON pdu " +1233." WHEN (OLD.msg_box = 4 AND NEW.msg_box != 4) " +1234." AND (OLD._id IN (SELECT DISTINCT msg_id" +1235." FROM pending_msgs" +1236." WHERE err_type >= 10)) " +1237."BEGIN " +1238." UPDATE threads SET error = error - 1" +1239." WHERE _id = OLD.thread_id; " +1240."END;");1241.}1242.1243.private void upgradeDatabaseToVersion42(SQLiteDatabase db) {1244.db.execSQL("DROP TRIGGER IF EXISTS sms_update_thread_on_delete");1245.db.execSQL("DROP TRIGGER IF EXISTS delete_obsolete_threads_sms");1246.db.execSQL("DROP TRIGGER IF EXISTS update_threads_error_on_delete_sms");1247.}1248.1249.private void upgradeDatabaseToVersion43(SQLiteDatabase db) {1250.// Add 'has_attachment' column to threads table.1251.db.execSQL("ALTER TABLE threads ADD COLUMN has_attachment INTEGER DEFAULT 0");1252.1253.updateThreadsAttachmentColumn(db);1254.1255.// Add insert and delete triggers for keeping it up to date.1256.db.execSQL(PART_UPDATE_THREADS_ON_INSERT_TRIGGER);1257.db.execSQL(PART_UPDATE_THREADS_ON_DELETE_TRIGGER);1258.}1259.1260.private void upgradeDatabaseToVersion44(SQLiteDatabase db) {1261.updateThreadsAttachmentColumn(db);1262.1263.// add the update trigger for keeping the threads up to date.1264.db.execSQL(PART_UPDATE_THREADS_ON_UPDATE_TRIGGER);1265.}1266.1267.private void upgradeDatabaseToVersion45(SQLiteDatabase db) {1268.// Add 'locked' column to sms table.1269.db.execSQL("ALTER TABLE sms ADD COLUMN " + Sms.LOCKED + " INTEGER DEFAULT 0");1270.1271.// Add 'locked' column to pdu table.1272.db.execSQL("ALTER TABLE pdu ADD COLUMN " + Mms.LOCKED + " INTEGER DEFAULT 0");1273.}1274.1275.private void upgradeDatabaseToVersion46(SQLiteDatabase db) {1276.// add the "text" column for caching inline text (e.g. strings) instead of1277.// putting them in an external file1278.db.execSQL("ALTER TABLE part ADD COLUMN " + Part.TEXT + " TEXT");1279.1280.Cursor textRows = db.query(1281."part",1282.new String[] { Part._ID, Part._DATA, Part.TEXT},1283."ct = 'text/plain' OR ct == 'application/smil'",1284.null,1285.null,1286.null,1287.null);1288.ArrayList<String> filesToDelete = new ArrayList<String>();1289.try {1290.db.beginTransaction();1291.if (textRows != null) {1292.int partDataColumn = textRows.getColumnIndex(Part._DATA);1293.1294.// This code is imperfect in that we can't guarantee that all the1295.// backing files get deleted. For example if the system aborts after1296.// the database is updated but before we complete the process of1297.// deleting files.1298.while (textRows.moveToNext()) {1299.String path = textRows.getString(partDataColumn);1300.if (path != null) {1301.try {1302.InputStream is = new FileInputStream(path);1303.byte [] data = new byte[is.available()];1304.is.read(data);1305.EncodedStringValue v = new EncodedStringValue(data);1306.db.execSQL("UPDATE part SET " + Part._DATA + " = NULL, " +1307.Part.TEXT + " = ?", new String[] { v.getString() });1308.is.close();1309.filesToDelete.add(path);1310.} catch (IOException e) {1311.// TODO Auto-generated catch block1312.e.printStackTrace();1313.}1314.}1315.}1316.}1317.db.setTransactionSuccessful();1318.} finally {1319.db.endTransaction();1320.for (String pathToDelete : filesToDelete) {1321.try {1322.(new File(pathToDelete)).delete();1323.} catch (SecurityException ex) {1324.Log.e(TAG, "unable to clean up old mms file for " + pathToDelete, ex);1325.}1326.}1327.if (textRows != null) {1328.textRows.close();1329.}1330.}1331.}1332.1333.private void upgradeDatabaseToVersion47(SQLiteDatabase db) {1334.updateThreadsAttachmentColumn(db);1335.1336.// add the update trigger for keeping the threads up to date.1337.db.execSQL(PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER);1338.}1339.1340.private void upgradeDatabaseToVersion48(SQLiteDatabase db) {1341.// Add 'error_code' column to sms table.1342.db.execSQL("ALTER TABLE sms ADD COLUMN error_code INTEGER DEFAULT 0");1343.}1344.1345.private void upgradeDatabaseToVersion51(SQLiteDatabase db) {1346.db.execSQL("ALTER TABLE sms add COLUMN seen INTEGER DEFAULT 0");1347.db.execSQL("ALTER TABLE pdu add COLUMN seen INTEGER DEFAULT 0");1348.1349.try {1350.// update the existing sms and pdu tables so the new "seen" column is the same as1351.// the "read" column for each row.1352.ContentValues contentValues = new ContentValues();1353.contentValues.put("seen", 1);1354.int count = db.update("sms", contentValues, "read=1", null);1355.Log.d(TAG, "[MmsSmsDb] upgradeDatabaseToVersion51: updated " + count +1356." rows in sms table to have READ=1");1357.count = db.update("pdu", contentValues, "read=1", null);1358.Log.d(TAG, "[MmsSmsDb] upgradeDatabaseToVersion51: updated " + count +1359." rows in pdu table to have READ=1");1360.} catch (Exception ex) {1361.Log.e(TAG, "[MmsSmsDb] upgradeDatabaseToVersion51 caught ", ex);1362.}1363.}1364.1365.private void upgradeDatabaseToVersion53(SQLiteDatabase db) {1366.db.execSQL("DROP TRIGGER IF EXISTS pdu_update_thread_read_on_update");1367.1368.// Updates threads table whenever a message in pdu is updated.1369.db.execSQL("CREATE TRIGGER pdu_update_thread_read_on_update AFTER" +1370." UPDATE OF " + Mms.READ +1371." ON " + MmsProvider.TABLE_PDU + " " +1372.PDU_UPDATE_THREAD_CONSTRAINTS +1373."BEGIN " +1374.PDU_UPDATE_THREAD_READ_BODY +1375."END;");1376.}1377.1378.private void upgradeDatabaseToVersion54(SQLiteDatabase db) {1379.// Add 'date_sent' column to sms table.1380.db.execSQL("ALTER TABLE sms ADD COLUMN " + Sms.DATE_SENT + " INTEGER DEFAULT 0");1381.1382.// Add 'date_sent' column to pdu table.1383.db.execSQL("ALTER TABLE pdu ADD COLUMN " + Mms.DATE_SENT + " INTEGER DEFAULT 0");1384.}1385.1386.private void upgradeDatabaseToVersion55(SQLiteDatabase db) {1387.// Drop removed triggers1388.db.execSQL("DROP TRIGGER IF EXISTS delete_obsolete_threads_pdu");1389.db.execSQL("DROP TRIGGER IF EXISTS delete_obsolete_threads_when_update_pdu");1390.}[e-mail] synchronized SQLiteDatabase getWritableDatabase() {1394.SQLiteDatabase db = super.getWritableDatabase();1395.1396.if (!sTriedAutoIncrement) {1397.sTriedAutoIncrement = true;1398.boolean hasAutoIncrementThreads = hasAutoIncrement(db, "threads");1399.boolean hasAutoIncrementAddresses = hasAutoIncrement(db, "canonical_addresses");1400.Log.d(TAG, "[getWritableDatabase] hasAutoIncrementThreads: " + hasAutoIncrementThreads +1401." hasAutoIncrementAddresses: " + hasAutoIncrementAddresses);1402.boolean autoIncrementThreadsSuccess = true;1403.boolean autoIncrementAddressesSuccess = true;1404.if (!hasAutoIncrementThreads) {1405.db.beginTransaction();1406.try {1407.if (false && sFakeLowStorageTest) {1408.Log.d(TAG, "[getWritableDatabase] mFakeLowStorageTest is true " +1409." - fake exception");1410.throw new Exception("FakeLowStorageTest");1411.}1412.upgradeThreadsTableToAutoIncrement(db); // a no-op if already upgraded1413.db.setTransactionSuccessful();1414.} catch (Throwable ex) {1415.Log.e(TAG, "Failed to add autoIncrement to threads;: " + ex.getMessage(), ex);1416.autoIncrementThreadsSuccess = false;1417.} finally {1418.db.endTransaction();1419.}1420.}1421.if (!hasAutoIncrementAddresses) {1422.db.beginTransaction();1423.try {1424.if (false && sFakeLowStorageTest) {1425.Log.d(TAG, "[getWritableDatabase] mFakeLowStorageTest is true " +1426." - fake exception");1427.throw new Exception("FakeLowStorageTest");1428.}1429.upgradeAddressTableToAutoIncrement(db); // a no-op if already upgraded1430.db.setTransactionSuccessful();1431.} catch (Throwable ex) {1432.Log.e(TAG, "Failed to add autoIncrement to canonical_addresses: " +1433.ex.getMessage(), ex);1434.autoIncrementAddressesSuccess = false;1435.} finally {1436.db.endTransaction();1437.}1438.}1439.if (autoIncrementThreadsSuccess && autoIncrementAddressesSuccess) {1440.if (mLowStorageMonitor != null) {1441.// We've already updated the database. This receiver is no longer necessary.1442.Log.d(TAG, "Unregistering mLowStorageMonitor - we've upgraded");1443.mContext.unregisterReceiver(mLowStorageMonitor);1444.mLowStorageMonitor = null;1445.}1446.} else {1447.if (sFakeLowStorageTest) {1448.sFakeLowStorageTest = false;1449.}1450.1451.// We failed, perhaps because of low storage. Turn on a receiver to watch for1452.// storage space.1453.if (mLowStorageMonitor == null) {1454.Log.d(TAG, "[getWritableDatabase] turning on storage monitor");1455.mLowStorageMonitor = new LowStorageMonitor();1456.IntentFilter intentFilter = new IntentFilter();1457.intentFilter.addAction(Intent.ACTION_DEVICE_STORAGE_LOW);1458.intentFilter.addAction(Intent.ACTION_DEVICE_STORAGE_OK);1459.mContext.registerReceiver(mLowStorageMonitor, intentFilter);1460.}1461.}1462.}1463.return db;1464.}1465.1466.// Determine whether a particular table has AUTOINCREMENT in its schema.1467.private boolean hasAutoIncrement(SQLiteDatabase db, String tableName) {1468.boolean result = false;1469.String query = "SELECT sql FROM sqlite_master WHERE type='table' AND name='" +1470.tableName + "'";1471.Cursor c = db.rawQuery(query, null);1472.if (c != null) {1473.try {1474.if (c.moveToFirst()) {1475.String schema = c.getString(0);1476.result = schema != null ? schema.contains("AUTOINCREMENT") : false;1477.Log.d(TAG, "[MmsSmsDb] tableName: " + tableName + " hasAutoIncrement: " +1478.schema + " result: " + result);1479.}1480.} finally {1481.c.close();1482.}1483.}1484.return result;1485.}1486.1487.// upgradeThreadsTableToAutoIncrement() is called to add the AUTOINCREMENT keyword to1488.// the threads table. This could fail if the user has a lot of conversations and not enough1489.// storage to make a copy of the threads table. That's ok. This upgrade is optional. It'll1490.// be called again next time the device is rebooted.1491.private void upgradeThreadsTableToAutoIncrement(SQLiteDatabase db) {1492.if (hasAutoIncrement(db, "threads")) {1493.Log.d(TAG, "[MmsSmsDb] upgradeThreadsTableToAutoIncrement: already upgraded");1494.return;1495.}1496.Log.d(TAG, "[MmsSmsDb] upgradeThreadsTableToAutoIncrement: upgrading");1497.1498.// Make the _id of the threads table autoincrement so we never re-use thread ids1499.// Have to create a new temp threads table. Copy all the info from the old table.1500.// Drop the old table and rename the new table to that of the old.1501.db.execSQL("CREATE TABLE threads_temp (" +1502.Threads._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +1503.Threads.DATE + " INTEGER DEFAULT 0," +1504.Threads.MESSAGE_COUNT + " INTEGER DEFAULT 0," +1505.Threads.RECIPIENT_IDS + " TEXT," +1506.Threads.SNIPPET + " TEXT," +1507.Threads.SNIPPET_CHARSET + " INTEGER DEFAULT 0," +1508.Threads.READ + " INTEGER DEFAULT 1," +1509.Threads.TYPE + " INTEGER DEFAULT 0," +1510.Threads.ERROR + " INTEGER DEFAULT 0," +1511.Threads.HAS_ATTACHMENT + " INTEGER DEFAULT 0);");1512.1513.db.execSQL("INSERT INTO threads_temp SELECT * from threads;");1514.db.execSQL("DROP TABLE threads;");1515.db.execSQL("ALTER TABLE threads_temp RENAME TO threads;");1516.}1517.1518.// upgradeAddressTableToAutoIncrement() is called to add the AUTOINCREMENT keyword to1519.// the canonical_addresses table. This could fail if the user has a lot of people they've1520.// messaged with and not enough storage to make a copy of the canonical_addresses table.1521.// That's ok. This upgrade is optional. It'll be called again next time the device is rebooted.1522.private void upgradeAddressTableToAutoIncrement(SQLiteDatabase db) {1523.if (hasAutoIncrement(db, "canonical_addresses")) {1524.Log.d(TAG, "[MmsSmsDb] upgradeAddressTableToAutoIncrement: already upgraded");1525.return;1526.}1527.Log.d(TAG, "[MmsSmsDb] upgradeAddressTableToAutoIncrement: upgrading");1528.1529.// Make the _id of the canonical_addresses table autoincrement so we never re-use ids1530.// Have to create a new temp canonical_addresses table. Copy all the info from the old1531.// table. Drop the old table and rename the new table to that of the old.1532.db.execSQL("CREATE TABLE canonical_addresses_temp (_id INTEGER PRIMARY KEY AUTOINCREMENT," +1533."address TEXT);");1534.1535.db.execSQL("INSERT INTO canonical_addresses_temp SELECT * from canonical_addresses;");1536.db.execSQL("DROP TABLE canonical_addresses;");1537.db.execSQL("ALTER TABLE canonical_addresses_temp RENAME TO canonical_addresses;");1538.}1539.1540.private class LowStorageMonitor extends BroadcastReceiver {1541.1542.public LowStorageMonitor() {1543.}1544.1545.public void onReceive(Context context, Intent intent) {1546.String action = intent.getAction();1547.1548.Log.d(TAG, "[LowStorageMonitor] onReceive intent " + action);1549.1550.if (Intent.ACTION_DEVICE_STORAGE_OK.equals(action)) {1551.sTriedAutoIncrement = false; // try to upgrade on the next getWriteableDatabase1552.}1553.}1554.}1555.1556.private void updateThreadsAttachmentColumn(SQLiteDatabase db) {1557.// Set the values of that column correctly based on the current1558.// contents of the database.1559.db.execSQL("UPDATE threads SET has_attachment=1 WHERE _id IN " +1560." (SELECT DISTINCT pdu.thread_id FROM part " +1561." JOIN pdu ON pdu._id=part.mid " +1562." WHERE part.ct != 'text/plain' AND part.ct != 'application/smil')");1563.}1564.}Powered by Gitiles