init.sql (5390B)
1 CREATE TABLE subjects 2 (id INTEGER PRIMARY KEY AUTOINCREMENT, 3 name TEXT NOT NULL); 4 CREATE UNIQUE INDEX i_subjects ON subjects(name); 5 6 CREATE TABLE received 7 (id INTEGER PRIMARY KEY AUTOINCREMENT, 8 timestamp REAL NOT NULL, 9 subject_id INTEGER NOT NULL, 10 data TEXT NOT NULL, 11 reply_subject_id INTEGER, 12 FOREIGN KEY (subject_id) REFERENCES subjects (id), 13 FOREIGN KEY (reply_subject_id) REFERENCES subjects (id)); 14 CREATE INDEX i_rtime ON received(timestamp); 15 CREATE INDEX i_rsubjectid ON received(subject_id); 16 17 CREATE TABLE sent 18 (id INTEGER PRIMARY KEY AUTOINCREMENT, 19 timestamp REAL NOT NULL, 20 subject_id INTEGER NOT NULL, 21 data TEXT NOT NULL, 22 reply_subject_id INTEGER, 23 FOREIGN KEY (subject_id) REFERENCES subjects (id), 24 FOREIGN KEY (reply_subject_id) REFERENCES subjects (id)); 25 CREATE INDEX i_stime ON sent(timestamp); 26 CREATE INDEX i_ssubjectid ON sent(subject_id); 27 28 CREATE VIEW received_view (timestamp,subject,reply_subject,data) 29 AS SELECT datetime(timestamp),subjects.name,rsub.name,data 30 FROM received LEFT OUTER JOIN subjects ON subjects.id = subject_id 31 LEFT OUTER JOIN subjects rsub ON rsub.id = reply_subject_id; 32 33 CREATE VIEW sent_view (timestamp,subject,reply_subject,data) 34 AS SELECT datetime(timestamp),subjects.name,rsub.name,data 35 FROM sent LEFT OUTER JOIN subjects ON subjects.id = subject_id 36 LEFT OUTER JOIN subjects rsub ON rsub.id = reply_subject_id; 37 38 CREATE TRIGGER insert_received INSTEAD OF INSERT ON received_view 39 BEGIN 40 INSERT INTO subjects(name) 41 SELECT NEW.subject WHERE NOT EXISTS 42 (SELECT 1 FROM subjects WHERE name = NEW.subject); 43 INSERT INTO subjects(name) 44 SELECT NEW.reply_subject WHERE NOT EXISTS 45 (SELECT 1 FROM subjects WHERE name = NEW.reply_subject) 46 AND NEW.reply_subject <> ''; 47 INSERT INTO received(timestamp,subject_id,reply_subject_id,data) 48 VALUES (NEW.timestamp, 49 (SELECT id FROM subjects WHERE name = NEW.subject), 50 (SELECT id FROM subjects WHERE name = NEW.reply_subject), 51 NEW.data); 52 END; 53 54 CREATE TRIGGER insert_sent INSTEAD OF INSERT ON sent_view 55 BEGIN 56 INSERT INTO subjects(name) 57 SELECT NEW.subject WHERE NOT EXISTS 58 (SELECT 1 FROM subjects WHERE name = NEW.subject); 59 INSERT INTO subjects(name) 60 SELECT NEW.reply_subject WHERE NOT EXISTS 61 (SELECT 1 FROM subjects WHERE name = NEW.reply_subject) 62 AND NEW.reply_subject <> ''; 63 INSERT INTO sent(timestamp,subject_id,reply_subject_id,data) 64 VALUES (NEW.timestamp, 65 (SELECT id FROM subjects WHERE name = NEW.subject), 66 (SELECT id FROM subjects WHERE name = NEW.reply_subject), 67 NEW.data); 68 END; 69 70 CREATE TABLE header_keys 71 (id INTEGER PRIMARY KEY AUTOINCREMENT, 72 name TEXT NOT NULL); 73 CREATE UNIQUE INDEX i_header_keys ON header_keys(name); 74 75 CREATE TABLE headers 76 (id INTEGER PRIMARY KEY AUTOINCREMENT, 77 key_id INTEGER NOT NULL, 78 value TEXT NOT NULL, 79 FOREIGN KEY (key_id) REFERENCES header_keys (id)); 80 CREATE UNIQUE INDEX i_headers ON headers(key_id, value); 81 82 CREATE VIEW headers_view (id,key,value) 83 AS SELECT headers.id,header_keys.name,value 84 FROM headers LEFT OUTER JOIN header_keys ON header_keys.id = key_id; 85 86 CREATE TRIGGER insert_header INSTEAD OF INSERT ON headers_view 87 BEGIN 88 INSERT INTO header_keys(name) 89 SELECT NEW.key WHERE NOT EXISTS 90 (SELECT 1 FROM header_keys WHERE name = NEW.key); 91 INSERT INTO headers(id,key_id,value) 92 VALUES (NEW.id, 93 (SELECT id FROM header_keys WHERE name = NEW.key), 94 NEW.value); 95 END; 96 97 CREATE TABLE received_headers 98 (msg_id INTEGER NOT NULL, 99 header_id INTEGER NOT NULL, 100 FOREIGN KEY (msg_id) REFERENCES received (id), 101 FOREIGN KEY (header_id) REFERENCES headers (id)); 102 103 CREATE VIEW received_headers_view (msg_id,key,value) 104 AS SELECT msg_id,header_keys.name,headers.value 105 FROM received_headers 106 LEFT OUTER JOIN headers ON headers.id = header_id 107 LEFT OUTER JOIN header_keys ON header_keys.id = headers.key_id; 108 109 CREATE TRIGGER insert_received_header INSTEAD OF INSERT ON received_headers_view 110 BEGIN 111 INSERT INTO headers_view(key,value) 112 SELECT NEW.key,NEW.value WHERE NOT EXISTS 113 (SELECT 1 FROM headers_view WHERE key = NEW.key AND value = NEW.value); 114 INSERT INTO received_headers(msg_id,header_id) 115 VALUES (NEW.msg_id, 116 (SELECT id FROM headers_view WHERE key = NEW.key AND value = NEW.value)); 117 END; 118 119 CREATE TABLE sent_headers 120 (msg_id INTEGER NOT NULL, 121 header_id INTEGER NOT NULL, 122 FOREIGN KEY (msg_id) REFERENCES sent (id), 123 FOREIGN KEY (header_id) REFERENCES headers (id)); 124 125 CREATE VIEW sent_headers_view (msg_id,key,value) 126 AS SELECT msg_id,header_keys.name,headers.value 127 FROM sent_headers 128 LEFT OUTER JOIN headers ON headers.id = header_id 129 LEFT OUTER JOIN header_keys ON header_keys.id = headers.key_id; 130 131 CREATE TRIGGER insert_sent_header INSTEAD OF INSERT ON sent_headers_view 132 BEGIN 133 INSERT INTO headers_view(key,value) 134 SELECT NEW.key,NEW.value WHERE NOT EXISTS 135 (SELECT 1 FROM headers_view WHERE key = NEW.key AND value = NEW.value); 136 INSERT INTO sent_headers(msg_id,header_id) 137 VALUES (NEW.msg_id, 138 (SELECT id FROM headers_view WHERE key = NEW.key AND value = NEW.value)); 139 END; 140 141 PRAGMA user_version = 1;