· 7 years ago · Jan 02, 2019, 10:20 PM
1CREATE TABLE android_metadata (locale TEXT);
2CREATE TABLE pdu (_id INTEGER PRIMARY KEY AUTOINCREMENT,thread_id INTEGER,date INTEGER,date_sent INTEGER DEFAULT 0,msg_box INTEGER,read INTEGER DEFAULT 0,m_id TEXT,sub TEXT,sub_cs INTEGER,ct_t TEXT,ct_l TEXT,exp INTEGER,m_cls TEXT,m_type INTEGER,v INTEGER,m_size INTEGER,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,sub_id INTEGER DEFAULT -1, seen INTEGER DEFAULT 0,creator TEXT,text_only INTEGER DEFAULT 0);
3CREATE TABLE sqlite_sequence(name,seq);
4CREATE TABLE addr (_id INTEGER PRIMARY KEY,msg_id INTEGER,contact_id INTEGER,address TEXT,type INTEGER,charset INTEGER);
5CREATE TABLE part (_id INTEGER PRIMARY KEY AUTOINCREMENT,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);
6CREATE TABLE rate (sent_time INTEGER);
7CREATE TABLE drm (_id INTEGER PRIMARY KEY,_data TEXT);
8CREATE VIEW pdu_restricted AS SELECT * FROM pdu WHERE (msg_box=1 OR msg_box=2) AND (m_type!=130)
9/* pdu_restricted(_id,thread_id,date,date_sent,msg_box,read,m_id,sub,sub_cs,ct_t,ct_l,exp,m_cls,m_type,v,m_size,pri,rr,rpt_a,resp_st,st,tr_id,retr_st,retr_txt,retr_txt_cs,read_status,ct_cls,resp_txt,d_tm,d_rpt,locked,sub_id,seen,creator,text_only) */;
10CREATE TABLE sms (_id INTEGER PRIMARY KEY,thread_id INTEGER,address TEXT,person INTEGER,date INTEGER,date_sent INTEGER DEFAULT 0,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,sub_id INTEGER DEFAULT -1, error_code INTEGER DEFAULT 0,creator TEXT,seen INTEGER DEFAULT 0);
11CREATE TABLE raw (_id INTEGER PRIMARY KEY,date INTEGER,reference_number INTEGER,count INTEGER,sequence INTEGER,destination_port INTEGER,address TEXT,sub_id INTEGER DEFAULT -1, pdu TEXT,deleted INTEGER DEFAULT 0,message_body TEXT,display_originating_addr TEXT);
12CREATE TABLE attachments (sms_id INTEGER,content_url TEXT,offset INTEGER);
13CREATE TABLE sr_pending (reference_number INTEGER,action TEXT,data TEXT);
14CREATE VIEW sms_restricted AS SELECT * FROM sms WHERE type=1 OR type=2
15/* sms_restricted(_id,thread_id,address,person,date,date_sent,protocol,read,status,type,reply_path_present,subject,body,service_center,locked,sub_id,error_code,creator,seen) */;
16CREATE TABLE canonical_addresses (_id INTEGER PRIMARY KEY AUTOINCREMENT,address TEXT);
17CREATE TABLE threads (_id INTEGER PRIMARY KEY AUTOINCREMENT,date INTEGER DEFAULT 0,message_count INTEGER DEFAULT 0,recipient_ids TEXT,snippet TEXT,snippet_cs INTEGER DEFAULT 0,read INTEGER DEFAULT 1,archived INTEGER DEFAULT 0,type INTEGER DEFAULT 0,error INTEGER DEFAULT 0,has_attachment INTEGER DEFAULT 0);
18CREATE 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,pending_sub_id INTEGER DEFAULT -1, last_try INTEGER);
19CREATE 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;
20CREATE 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;
21CREATE 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;
22CREATE 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;
23CREATE 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;
24CREATE TRIGGER part_cleanup DELETE ON pdu BEGIN DELETE FROM part WHERE mid=old._id;END;
25CREATE TRIGGER addr_cleanup DELETE ON pdu BEGIN DELETE FROM addr WHERE msg_id=old._id;END;
26CREATE 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;
27CREATE 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;
28CREATE 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;
29CREATE 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;
30CREATE 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;
31CREATE TRIGGER delete_mms_pending_on_delete AFTER DELETE ON pdu BEGIN DELETE FROM pending_msgs WHERE msg_id=old._id; END;
32CREATE 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;
33CREATE 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;
34CREATE 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;
35CREATE 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;
36CREATE TRIGGER mms_words_delete AFTER DELETE ON part BEGIN DELETE FROM words WHERE source_id = OLD._id AND table_to_use = 2; END;
37CREATE 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;
38CREATE 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;
39CREATE 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;
40CREATE 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;
41CREATE 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;
42CREATE 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;
43CREATE VIRTUAL TABLE words USING FTS3 (_id INTEGER PRIMARY KEY, index_text TEXT, source_id INTEGER, table_to_use INTEGER)
44/* words(_id,index_text,source_id,table_to_use) */;
45CREATE TABLE IF NOT EXISTS 'words_content'(docid INTEGER PRIMARY KEY, 'c0_id', 'c1index_text', 'c2source_id', 'c3table_to_use');
46CREATE TABLE IF NOT EXISTS 'words_segments'(blockid INTEGER PRIMARY KEY, block BLOB);
47CREATE TABLE IF NOT EXISTS 'words_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx));
48CREATE 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;
49CREATE TRIGGER sms_words_delete AFTER DELETE ON sms BEGIN DELETE FROM words WHERE source_id = OLD._id AND table_to_use = 1; END;
50CREATE INDEX typeThreadIdIndex ON sms (type, thread_id);
51CREATE INDEX threadIdDateIndex ON sms (thread_id, date);
52CREATE INDEX partMidIndex ON part (mid);
53CREATE INDEX addrMsgIdIndex ON addr (msg_id);