1
|
/* operations are placed in similar order than replicant database after installation */
|
2
|
|
3
|
/* user_version is used to decide if db is useable by android */
|
4
|
PRAGMA user_version = 57;
|
5
|
|
6
|
/* TABLE pdu to convert */
|
7
|
BEGIN TRANSACTION;
|
8
|
CREATE TEMPORARY TABLE pdu_backup (_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,seen INTEGER DEFAULT 0,deletable INTEGER DEFAULT 0,hidden INTEGER DEFAULT 0,app_id INTEGER DEFAULT 0,msg_id INTEGER DEFAULT 0,callback_set INTEGER DEFAULT 0,reserved INTEGER DEFAULT 0);
|
9
|
INSERT INTO pdu_backup SELECT _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,seen,deletable,hidden,app_id,msg_id,callback_set,reserved FROM pdu;
|
10
|
DROP TABLE pdu;
|
11
|
CREATE 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,seen INTEGER DEFAULT 0,text_only INTEGER DEFAULT 0);
|
12
|
INSERT INTO pdu SELECT _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,seen,0 FROM pdu_backup;
|
13
|
DROP TABLE pdu_backup;
|
14
|
COMMIT;
|
15
|
|
16
|
/* TABLE part to convert */
|
17
|
BEGIN TRANSACTION;
|
18
|
CREATE TEMPORARY TABLE part_backup (_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);
|
19
|
INSERT INTO part_backup SELECT _id,mid,seq,ct,name,chset,cd,fn,cid,cl,ctt_s,ctt_t,_data,text FROM part;
|
20
|
DROP TABLE part;
|
21
|
CREATE 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);
|
22
|
INSERT INTO part SELECT _id,mid,seq,ct,name,chset,cd,fn,cid,cl,ctt_s,ctt_t,_data,text FROM part_backup;
|
23
|
DROP TABLE part_backup;
|
24
|
COMMIT;
|
25
|
|
26
|
/* TABLE sms to convert */
|
27
|
BEGIN TRANSACTION;
|
28
|
CREATE TEMPORARY TABLE sms_backup (_id INTEGER PRIMARY KEY AUTOINCREMENT,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,error_code INTEGER DEFAULT 0,seen INTEGER DEFAULT 0,deletable INTEGER DEFAULT 0,hidden INTEGER DEFAULT 0,group_id INTEGER,group_type INTEGER,delivery_date INTEGER,app_id INTEGER DEFAULT 0,msg_id INTEGER DEFAULT 0,callback_number TEXT,reserved INTEGER DEFAULT 0,pri INTEGER DEFAULT 0,teleservice_id INTEGER DEFAULT 0,link_url TEXT);
|
29
|
INSERT INTO sms_backup SELECT _id,thread_id,address,person,date,date_sent,protocol,read,status,type,reply_path_present,subject,body,service_center,locked,error_code,seen,deletable,hidden,group_id,group_type,delivery_date,app_id,msg_id,callback_number,reserved,pri,teleservice_id,link_url FROM sms;
|
30
|
DROP TABLE sms;
|
31
|
CREATE 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,error_code INTEGER DEFAULT 0,seen INTEGER DEFAULT 0);
|
32
|
INSERT INTO sms SELECT _id,thread_id,address,person,date,date_sent,protocol,read,status,type,reply_path_present,subject,body,service_center,locked,error_code,seen FROM sms_backup;
|
33
|
DROP TABLE sms_backup;
|
34
|
COMMIT;
|
35
|
|
36
|
/* TABLE threads to convert */
|
37
|
BEGIN TRANSACTION;
|
38
|
CREATE TEMPORARY TABLE threads_backup (_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,type INTEGER DEFAULT 0,error INTEGER DEFAULT 0,has_attachment INTEGER DEFAULT 0,unread_count INTEGER DEFAULT 0,alert_expired INTEGER DEFAULT 1,reply_all INTEGER DEFAULT -1,group_snippet TEXT,message_type INTEGER DEFAULT 0);
|
39
|
INSERT INTO threads_backup SELECT _id,date,message_count,recipient_ids,snippet,snippet_cs,read,type,error,has_attachment,unread_count,alert_expired,reply_all,group_snippet,message_type FROM threads;
|
40
|
DROP TABLE threads;
|
41
|
CREATE 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,type INTEGER DEFAULT 0,error INTEGER DEFAULT 0,has_attachment INTEGER DEFAULT 0);
|
42
|
INSERT INTO threads SELECT _id,date,message_count,recipient_ids,snippet,snippet_cs,read,type,error,has_attachment FROM threads_backup;
|
43
|
DROP TABLE threads_backup;
|
44
|
COMMIT;
|
45
|
|
46
|
/* TRIGGER to create */
|
47
|
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;
|
48
|
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;
|
49
|
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;
|
50
|
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;
|
51
|
|
52
|
CREATE TRIGGER part_cleanup DELETE ON pdu BEGIN DELETE FROM part WHERE mid=old._id;END;
|
53
|
CREATE TRIGGER addr_cleanup DELETE ON pdu BEGIN DELETE FROM addr WHERE msg_id=old._id;END;
|
54
|
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;
|
55
|
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;
|
56
|
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;
|
57
|
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;
|
58
|
|
59
|
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;
|
60
|
CREATE TRIGGER delete_mms_pending_on_delete AFTER DELETE ON pdu BEGIN DELETE FROM pending_msgs WHERE msg_id=old._id; END;
|
61
|
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;
|
62
|
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;
|
63
|
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;
|
64
|
|
65
|
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;
|
66
|
CREATE TRIGGER mms_words_delete AFTER DELETE ON part BEGIN DELETE FROM words WHERE source_id = OLD._id AND table_to_use = 2; END;
|
67
|
|
68
|
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;
|
69
|
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;
|
70
|
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;
|
71
|
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;
|
72
|
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;
|
73
|
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;
|
74
|
|
75
|
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;
|
76
|
CREATE TRIGGER sms_words_delete AFTER DELETE ON sms BEGIN DELETE FROM words WHERE source_id = OLD._id AND table_to_use = 1; END;
|
77
|
|
78
|
|
79
|
/* INDEX to create/drop */
|
80
|
CREATE INDEX typeThreadIdIndex ON sms (type, thread_id);
|
81
|
|
82
|
DROP INDEX IF EXISTS pduIndex1;
|
83
|
DROP INDEX IF EXISTS addrIndex1;
|
84
|
DROP INDEX IF EXISTS partIndex1;
|
85
|
|
86
|
DROP TABLE IF EXISTS cmas;
|
87
|
DROP TABLE IF EXISTS wpm;
|
88
|
|
89
|
DROP INDEX IF EXISTS threadsIndex1;
|
90
|
DROP INDEX IF EXISTS pending_msgsIndex1;
|
91
|
|
92
|
DROP TABLE IF EXISTS mychannels;
|
93
|
|
94
|
DROP TRIGGER IF EXISTS pdu_update_thread_date_on_update;
|
95
|
DROP TRIGGER IF EXISTS pdu_update_thread_type_on_update;
|
96
|
DROP TRIGGER IF EXISTS sms_update_thread_type_on_update;
|
97
|
DROP TRIGGER IF EXISTS sms_update_thread_date_on_update;
|
98
|
DROP TRIGGER IF EXISTS delete_obsolete_threads_pdu;
|
99
|
DROP TRIGGER IF EXISTS delete_obsolete_threads_when_update_pdu;
|
100
|
DROP TRIGGER IF EXISTS update_threads_message_type_on_update_sms;
|
101
|
DROP TRIGGER IF EXISTS update_threads_message_type_on_update_pdu;
|
102
|
DROP TRIGGER IF EXISTS update_threads_message_type_on_update_pending_msgs;
|
103
|
DROP TRIGGER IF EXISTS update_threads_message_type_on_insert_sms;
|
104
|
DROP TRIGGER IF EXISTS update_threads_message_type_on_insert_pdu;
|
105
|
DROP TRIGGER IF EXISTS update_threads_message_type_on_insert_pending_msgs;
|
106
|
DROP TRIGGER IF EXISTS update_threads_message_type_on_delete_sms;
|
107
|
DROP TRIGGER IF EXISTS update_threads_message_type_on_delete_pdu;
|
108
|
DROP TRIGGER IF EXISTS wpm_update_thread_on_insert;
|
109
|
DROP TRIGGER IF EXISTS wpm_update_thread_date_on_update;
|
110
|
DROP TRIGGER IF EXISTS wpm_update_thread_type_on_update;
|
111
|
DROP TRIGGER IF EXISTS wpm_update_thread_read_on_update;
|
112
|
DROP TRIGGER IF EXISTS cmas_update_thread_alert_expired_on_update;
|
113
|
DROP TRIGGER IF EXISTS Cmas_cleanup;
|
114
|
|
115
|
DROP TABLE IF EXISTS spam_pdu;
|
116
|
|
117
|
DROP INDEX IF EXISTS spam_pduIndex1;
|
118
|
|
119
|
DROP TABLE IF EXISTS spam_addr;
|
120
|
DROP INDEX IF EXISTS spam_addrIndex1;
|
121
|
|
122
|
DROP TABLE IF EXISTS spam_part;
|
123
|
|
124
|
DROP INDEX IF EXISTS spam_partIndex1;
|
125
|
|
126
|
DROP TABLE IF EXISTS spam_rate;
|
127
|
DROP TABLE IF EXISTS spam_drm;
|
128
|
DROP TABLE IF EXISTS spam_sms;
|
129
|
|
130
|
DROP TRIGGER IF EXISTS spam_part_cleanup;
|
131
|
DROP TRIGGER IF EXISTS spam_addr_cleanup;
|
132
|
DROP TRIGGER IF EXISTS spam_cleanup_delivery_and_read_report;
|
133
|
|
134
|
DROP TABLE IF EXISTS spam_filter;
|