Android短彩信数据库信息整理

查询Android短彩信数据库通过CMD命令行的adb shell

数据库名:mmssms.db

数据库位置:data/data/com.android.providers.telephony/databases

数据库信息:(从我的记事本上copy过来，格式全变形了.....大家将就着看吧....)

/*
整个数据库共有
---------------------------------------------------------
addr pdu threads
android_metadata pending_msgs words
attachments rate words_content
canonical_addresses raw words_segdir
drm sms words_segments
part sr_pending
---------------------------------------------------------
17张表，28个触发器

总的来看，android_metadata表内存有当前语言的属性信息，比如en_US或者zh_CN之类的信息
rate,raw,drm,sr_pending表格内容未知
words相关的四张表存储有关文字信息，但具体使用方法未知，应为后期扩展使用
attachments表内好像存有相关短信中的URL链接

-----------------------------------------------------------------------------------------------------------------------------------------

目前观察到其中关于SMS的表格有
---------------------------------------------------------
threads words words_content
canonical_addresses words_segdir sms
---------------------------------------------------------
6张表格

其中sms内存有Android message的几乎所有信息
sms表根据type,thread_id两个键值进行索引

sms表中thread_id与threads表中_id关联
sms表中_id与words表中source_id关联
threads表中recipient_ids与canonical_addresses表中_id关联

与这些表相关的数据库触发器为：

sms_update_thread_on_insert
sms_update_thread_date_subject_on_update
sms_update_thread_read_on_update
update_threads_error_on_update_sms
sms_words_update
sms_words_delete

共6个触发器维护数据库信息

因为如果不通过adb shell中sqlite3的方式操作数据库的话，只能通过Android定义的content://sms协议操作短信数据库，
故大部分操作只能对sms表进行操作，所以Android只定义了其中对sms表操作进行的触发
也就是说如果通过adb shell中sqlite3的方式直接变更threads表或canonical_addresses表或words表内的数据必定会引起数据库的混乱而导致非bug错误

而且说实话6个触发器不能照顾到各个方面的通过协议对数据库的操作，
例如对空数据库内的sms表进行insert操作时，必须事先getOrCreateThreadId
而且在使用smsManager类内的方法发送短信时，sms不会保存发送的短信数据，必须人工插入等等

--------------------------------------------------------------------------------------------------------------------------------------------

目前观察到其中关于MMS的表格有
---------------------------------------------------------
addr pdu threads
pending_msgs words words_content
canonical_addresses words_segdir part
---------------------------------------------------------
9张表格 22个触发器

其中pdu内存有Android MMS的几乎所有信息，通过thread_id外键与threads表内的_id关联，一条mms在pdu中只有一条数据
addr存有MMS的Address信息和其他一些信息（具体未知），通过msg_id外键与pdu表内的_id关联，一条mms可能对应多条addr中的数据
threads和canonical_addresses表内存有的信息与sms相同
pending_msgs内存有mms的信息，通过msg_id与pdu中的_id关联，与mms的关系也是1对1
words，words_content，words_segdir记录了关于mms内文字信息，之中的source_id与part表内的ct属性为text/plain的数据条的_id关联
part表内存有mms附件的内容信息，包括附件存在的位置，文件的类型，大小，具体信息，通过mid外键与pdu表内的_id关联

手机端删除彩信时，删除除canonical_addresses外的所有表中关于这条mms的数据
对pdu进行insert，delete，update时，trigger对pending_msgs进行自动维护
**备份还原mms数据库信息时，只需备份还原pdu，addr，part表的内容以及附件文件
附件文件存储在 data/data/com.android.providers.telephony/app_parts内，包括.jpg.txt.xml等等附件文件

to be continue...

*/

CREATE TABLE android_metadata (locale TEXT);
INSERT INTO "android_metadata" VALUES('en_US');

CREATE TABLE pdu (
_id INTEGER PRIMARY KEY,
thread_id INTEGER,
date INTEGER, //发送时间
msg_box INTEGER, //相当于sms的type
read INTEGER DEFAULT 0,
m_id TEXT,
sub TEXT,
sub_cs INTEGER,
ct_t TEXT,
ct_l TEXT,
exp INTEGER, //收信时间
m_cls TEXT, //默认为personal?
m_type INTEGER,
v INTEGER,
m_size INTEGER, //包含附件的整个mms的大小
pri INTEGER,
rr INTEGER,
rpt_a INTEGER,
resp_st INTEGER,
st INTEGER,
tr_id TEXT,
retr_st INTEGER,
retr_txt TEXT,
retr_txt_cs INTEGER,
read_status INTEGER,
ct_cls INTEGER,
resp_txt TEXT,
d_tm INTEGER,
d_rpt INTEGER,
locked INTEGER DEFAULT 0,
seen INTEGER DEFAULT 0);

CREATE TABLE addr (
_id INTEGER PRIMARY KEY,
msg_id INTEGER,
contact_id INTEGER,
address TEXT,
type INTEGER,
charset INTEGER);

CREATE TABLE part (
_id INTEGER PRIMARY KEY,
mid INTEGER,
seq INTEGER DEFAULT 0,
ct TEXT,
name TEXT,
chset INTEGER,
cd TEXT,
fn TEXT,
cid TEXT,
cl TEXT,
ctt_s INTEGER,
ctt_t TEXT,
_data TEXT,
text TEXT);

CREATE TABLE rate (sent_time INTEGER);

CREATE TABLE drm (_id INTEGER PRIMARY KEY,_data TEXT);

CREATE TABLE sms (
_id INTEGER PRIMARY KEY,
thread_id INTEGER,
address TEXT,
person INTEGER,
date INTEGER,
protocol INTEGER,
read INTEGER DEFAULT 0,
status INTEGER DEFAULT -1,
type INTEGER,
reply_path_present INTEGER,
subject TEXT,
body TEXT,
service_center TEXT,
locked INTEGER DEFAULT 0,
error_code INTEGER DEFAULT 0,
seen INTEGER DEFAULT 0);

CREATE TABLE raw (
_id INTEGER PRIMARY KEY,
date INTEGER,
reference_number INTEGER,
count INTEGER,
sequence INTEGER,
destination_port INTEGER,
address TEXT,
pdu TEXT);

CREATE TABLE attachments (
sms_id INTEGER,
content_url TEXT,
offset INTEGER);

CREATE TABLE sr_pending (
reference_number INTEGER,
action TEXT,
data TEXT);

CREATE TABLE canonical_addresses (
_id INTEGER PRIMARY KEY,
address TEXT);

CREATE TABLE threads (
_id INTEGER PRIMARY KEY,
date INTEGER DEFAULT 0,
message_count INTEGER DEFAULT 0,
recipient_ids TEXT,
snippet TEXT,
snippet_cs INTEGER DEFAULT 0,
read INTEGER DEFAULT 1,
type INTEGER DEFAULT 0,
error INTEGER DEFAULT 0,
has_attachment INTEGER DEFAULT 0);

CREATE TABLE pending_msgs (
_id INTEGER PRIMARY KEY,
proto_type INTEGER,
msg_id INTEGER,
msg_type INTEGER,
err_type INTEGER,
err_code INTEGER,
retry_index INTEGER NOT NULL DEFAULT 0,
due_time INTEGER,
last_try INTEGER);
PRAGMA writable_schema=ON;

INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)
VALUES('table',
'words',
'words',
0,
'CREATE VIRTUAL TABLE words USING FTS3 (_id INTEGER PRIMARY KEY,
index_text TEXT,
source_id INTEGER,
table_to_use INTEGER)');

CREATE TABLE 'words_content'(
docid INTEGER PRIMARY KEY,
'c0_id',
'c1index_text',
'c2source_id',
'c3table_to_use');

CREATE TABLE 'words_segments'(
blockid INTEGER PRIMARY KEY,
block BLOB);

CREATE TABLE 'words_segdir'(
level INTEGER,
idx INTEGER,
start_block INTEGER,
leaves_end_block INTEGER,
end_block INTEGER,
root BLOB,
PRIMARY KEY(level, idx));

=============================================================================================================================================================

CREATE TRIGGER pdu_update_thread_on_insert AFTER INSERT ON pdu
WHEN new.m_type=132 OR new.m_type=130 OR new.m_type=128

BEGIN

UPDATE threads SET date = (strftime('%s','now') * 1000),
snippet = new.sub,
snippet_cs = new.sub_cs
WHERE threads._id = new.thread_id;

UPDATE threads SET message_count = (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads
ON threads._id = thread_id
WHERE thread_id = new.thread_id AND sms.type != 3)
+ (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads
ON threads._id = thread_id
WHERE thread_id = new.thread_id AND (m_type=132 OR m_type=130 OR m_type=128) AND msg_box != 3)
WHERE threads._id = new.thread_id;

UPDATE threads SET read = CASE (SELECT COUNT(*) FROM pdu
WHERE read = 0 AND thread_id = threads._id AND (m_type=132 OR m_type=130 OR m_type=128))
WHEN 0 THEN 1 ELSE 0 END
WHERE threads._id= new.thread_id;

END;

=============================================================================================================================================================

CREATE TRIGGER sms_update_thread_on_insert AFTER INSERT ON sms

BEGIN

UPDATE threads SET date = (strftime('%s','now') * 1000),
snippet = new.body,
snippet_cs = 0
WHERE threads._id = new.thread_id;

UPDATE threads SET message_count = (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads
ON threads._id = thread_id
WHERE thread_id = new.thread_id AND sms.type != 3)
+ (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads
ON threads._id = thread_id
WHERE thread_id = new.thread_id AND (m_type=132 OR m_type=130 OR m_type=128) AND msg_box != 3)
WHERE threads._id = new.thread_id;

UPDATE threads SET read = CASE (SELECT COUNT(*) FROM sms
WHERE read = 0 AND thread_id = threads._id)
WHEN 0 THEN 1 ELSE 0 END
WHERE threads._id = new.thread_id;

END;

=============================================================================================================================================================

CREATE TRIGGER pdu_update_thread_date_subject_on_update AFTER UPDATE OF date, sub, msg_box ON pdu
WHEN new.m_type=132 OR new.m_type=130 OR new.m_type=128
BEGIN

UPDATE threads SET date = (strftime('%s','now') * 1000),
snippet = new.sub,
snippet_cs = new.sub_cs
WHERE threads._id = new.thread_id;

UPDATE threads SET message_count = (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads
ON threads._id = thread_id
WHERE thread_id = new.thread_id AND sms.type != 3)
+ (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads
ON threads._id = thread_id
WHERE thread_id = new.thread_id AND (m_type=132 OR m_type=130 OR m_type=128) AND msg_box != 3)
WHERE threads._id = new.thread_id;

UPDATE threads SET read = CASE (SELECT COUNT(*) FROM pdu
WHERE read = 0 AND thread_id = threads._id AND (m_type=132 OR m_type=130 OR m_type=128))
WHEN 0 THEN 1 ELSE 0 END
WHERE threads._id = new.thread_id;

END;

=============================================================================================================================================================

CREATE TRIGGER sms_update_thread_date_subject_on_update AFTER UPDATE OF date, body, type ON sms

BEGIN

UPDATE threads SET date = (strftime('%s','now') * 1000),
snippet = new.body,
snippet_cs = 0
WHERE threads._id = new.thread_id;
UPDATE threads SET message_count = (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads
ON threads._id = thread_id
WHERE thread_id = new.thread_id AND sms.type != 3)
+ (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads
ON threads._id = thread_id
WHERE thread_id = new.thread_id AND (m_type=132 OR m_type=130 OR m_type=128) AND msg_box != 3)
WHERE threads._id = new.thread_id;

UPDATE threads SET read = CASE (SELECT COUNT(*) FROM sms
WHERE read = 0 AND thread_id = threads._id)
WHEN 0 THEN 1 ELSE 0 END
WHERE threads._id = new.thread_id;

END;

=============================================================================================================================================================

CREATE TRIGGER pdu_update_thread_read_on_update AFTER UPDATE OF read ON pdu
WHEN new.m_type=132 OR new.m_type=130 OR new.m_type=128

BEGIN

UPDATE threads SET read = CASE (SELECT COUNT(*) FROM pdu
WHERE read= 0 AND thread_id = threads._id AND (m_type=132 OR m_type=130 OR m_type=128))
WHEN 0 THEN 1 ELSE 0 END
WHERE threads._id = new.thread_id;

END;

=============================================================================================================================================================

CREATE TRIGGER sms_update_thread_read_on_update AFTER UPDATE OF read ON sms

BEGIN

UPDATE threads SET read = CASE (SELECT COUNT(*) FROM sms
WHERE read = 0 AND thread_id = threads._id)
WHEN 0 THEN 1 ELSE 0 END
WHERE threads._id = new.thread_id;

END;

=============================================================================================================================================================

CREATE TRIGGER pdu_update_thread_on_delete AFTER DELETE ON pdu

BEGIN

UPDATE threads SET date = (strftime('%s','now') * 1000)
WHERE threads._id = old.thread_id;

UPDATE threads SET message_count = (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads
ON threads._id = thread_id
WHERE thread_id = old.thread_id AND sms.type != 3)
+ (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads
ON threads._id = thread_id
WHERE thread_id = old.thread_id AND (m_type=132 OR m_type=130 OR m_type=128) AND msg_box != 3)
WHERE threads._id = old.thread_id;

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;

END;

=============================================================================================================================================================

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;

=============================================================================================================================================================

CREATE TRIGGER delete_obsolete_threads_when_update_pdu AFTER UPDATE OF thread_id ON pdu
WHEN old.thread_id != new.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;

=============================================================================================================================================================

CREATE TRIGGER insert_mms_pending_on_insert AFTER INSERT ON pdu
WHEN new.m_type=130 OR new.m_type=135

BEGIN

INSERT INTO pending_msgs (proto_type, msg_id, msg_type, err_type, err_code, retry_index, due_time)
VALUES (1, new._id, new.m_type,0,0,0,0);

END;

=============================================================================================================================================================

CREATE TRIGGER insert_mms_pending_on_update AFTER UPDATE ON pdu
WHEN new.m_type=128 AND new.msg_box=4 AND old.msg_box!=4

BEGIN

INSERT INTO pending_msgs (proto_type, msg_id, msg_type, err_type, err_code, retry_index, due_time)
VALUES (1, new._id, new.m_type,0,0,0,0);

END;

=============================================================================================================================================================

CREATE TRIGGER delete_mms_pending_on_update AFTER UPDATE ON pdu
WHEN old.msg_box=4 AND new.msg_box!=4

BEGIN

DELETE FROM pending_msgs WHERE msg_id=new._id;

END;

=============================================================================================================================================================

CREATE TRIGGER delete_mms_pending_on_delete AFTER DELETE ON pdu

BEGIN

DELETE FROM pending_msgs WHERE msg_id=old._id;

END;

=============================================================================================================================================================

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;

=============================================================================================================================================================

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;

=============================================================================================================================================================

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;

=============================================================================================================================================================

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;

=============================================================================================================================================================

CREATE TRIGGER part_cleanup DELETE ON pdu

BEGIN

DELETE FROM part WHERE mid=old._id;

END;

=============================================================================================================================================================

CREATE TRIGGER addr_cleanup DELETE ON pdu

BEGIN

DELETE FROM addr WHERE msg_id=old._id;

END;

=============================================================================================================================================================

CREATE TRIGGER cleanup_delivery_and_read_report AFTER DELETE ON pdu
WHEN old.m_type=128

BEGIN

DELETE FROM pdu WHERE (m_type=134 OR m_type=136) AND m_id=old.m_id;

END;

=============================================================================================================================================================

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;

=============================================================================================================================================================

CREATE TRIGGER update_threads_on_update_part AFTER UPDATE of mid 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;

=============================================================================================================================================================

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
WHERE pdu.thread_id = threads._id
AND part.ct != 'text/plain'
AND part.ct != 'application/smil'
AND part.mid = pdu._id)
WHEN 0 THEN 0 ELSE 1 END;

END;

=============================================================================================================================================================

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
WHERE part.ct != 'text/plain' AND part.ct != 'application/smil' AND part.mid = pdu._id);

END;

=============================================================================================================================================================

CREATE TRIGGER sms_words_update AFTER UPDATE ON sms

BEGIN

UPDATE words SET index_text = NEW.body
WHERE (source_id=NEW._id AND table_to_use=1);

END;

=============================================================================================================================================================

CREATE TRIGGER sms_words_delete AFTER DELETE ON sms

BEGIN

DELETE FROM words
WHERE source_id = OLD._id AND table_to_use = 1;

END;

=============================================================================================================================================================

CREATE TRIGGER mms_words_update AFTER UPDATE ON part

BEGIN

UPDATE words SET index_text = NEW.text WHERE (source_id=NEW._id AND table_to_use=2);

END;

=============================================================================================================================================================

CREATE TRIGGER mms_words_delete AFTER DELETE ON part

BEGIN

DELETE FROM words WHERE source_id = OLD._id AND table_to_use = 2;

END;

=============================================================================================================================================================

CREATE INDEX typeThreadIdIndex ON sms (type, thread_id);

更多相关文章

随机推荐