1CREATE TABLE thread ( 2 id INTEGER NOT NULL PRIMARY KEY, 3 4 original_poster TEXT NOT NULL, 5 coordinator TEXT NULL, -- NULL - proposal 6 7 private BOOLEAN NOT NULL DEFAULT 0, -- 0 - public, 1 - private 8 lock BOOLEAN NOT NULL DEFAULT 0, -- 0 - unlocked, 1 - locked 9 10 type INTEGER NOT NULL DEFAULT 0, -- 0 - project, 1 - issue 11 state TEXT NOT NULL DEFAULT 'proposal', -- proposal,opened,done,closed,rejected 12 13 create_date TEXT NOT NULL, -- ISO8601 14 last_activity_date TEXT NOT NULL, -- ISO8601 15 last_modification_date TEXT NOT NULL, -- ISO8601 16 close_date TEXT, -- ISO8601 17 18 title TEXT NOT NULL, 19 content TEXT NOT NULL, 20 content_html TEXT NOT NULL, 21 22 priority INTEGER NOT NULL DEFAULT -1, -- dependent on tasks 23 24 task_count INTEGER NOT NULL DEFAULT 0, 25 task_count_closed INTEGER NOT NULL DEFAULT 0, 26 task_sum_cost REAL 27); 28 29CREATE INDEX thread_ix_last_activity_date 30 ON thread (last_activity_date); -- to speedup order by 31 32CREATE TABLE thread_participant ( 33 thread_id INTEGER NOT NULL REFERENCES thread (id), 34 user_id TEXT NOT NULL, 35 36 original_poster BOOLEAN NOT NULL DEFAULT 0, 37 coordinator BOOLEAN NOT NULL DEFAULT 0, 38 39 commentator BOOLEAN NOT NULL DEFAULT 0, 40 task_assignee BOOLEAN NOT NULL DEFAULT 0, 41 subscribent BOOLEAN NOT NULL DEFAULT 0, 42 43 added_by TEXT NOT NULL, -- user who added the participant. Equals user_id when user subscribed himself 44 added_date TEXT NOT NULL, -- ISO8601 45 46 PRIMARY KEY (thread_id, user_id) 47); 48 49CREATE TABLE thread_comment ( 50 id INTEGER NOT NULL PRIMARY KEY, 51 52 thread_id INTEGER NOT NULL REFERENCES thread (id), 53 54 type INTEGER NOT NULL DEFAULT 0, -- 0 -comment, 1 - real cause, 2 - potential cause, 10 - closing comment 55 56 author TEXT NOT NULL, 57 create_date TEXT NOT NULL, -- ISO8601 58 last_modification_date TEXT NOT NULL, -- ISO8601 59 60 content TEXT NOT NULL, 61 content_html TEXT NOT NULL 62); 63 64CREATE TABLE label ( 65 id INTEGER NOT NULL PRIMARY KEY, 66 name TEXT UNIQUE NOT NULL, 67 count INTEGER NOT NULL DEFAULT 0, 68 69 added_by TEXT NOT NULL, -- user who added the label 70 added_date TEXT NOT NULL -- ISO8601 71); 72 73CREATE INDEX label_ix_name 74 ON label (name); 75 76CREATE TABLE thread_label ( 77 thread_id INTEGER NOT NULL, 78 label_id INTEGER NOT NULL, 79 PRIMARY KEY (thread_id, label_id) 80); 81 82CREATE TRIGGER thread_label_tr_insert 83 INSERT 84 ON thread_label 85BEGIN 86 UPDATE label 87 SET count = count + 1 88 WHERE id = new.label_id; 89END; 90 91CREATE TRIGGER thread_label_tr_delete 92 DELETE 93 ON thread_label 94BEGIN 95 UPDATE label 96 SET count = count - 1 97 WHERE id = old.label_id; 98END; 99 100CREATE TRIGGER thread_label_tr_update_label_id 101 UPDATE OF label_id 102 ON thread_label 103BEGIN 104 UPDATE label 105 SET count = count - 1 106 WHERE id = old.label_id; 107 108 UPDATE label 109 SET count = count + 1 110 WHERE id = new.label_id; 111END; 112 113-- we cannot delete tasks (so not triggers provided) 114CREATE TABLE task ( 115 id INTEGER NOT NULL PRIMARY KEY, 116 117 original_poster TEXT NOT NULL, 118 assignee TEXT NOT NULL, 119 120 private BOOLEAN NOT NULL DEFAULT 0, -- 0 - public, 1 - private 121 lock BOOLEAN NOT NULL DEFAULT 0, -- 0 - unlocked, 1 - locked 122 123 state TEXT NOT NULL DEFAULT 'opened', -- opened, closed, rejected 124 125 create_date TEXT NOT NULL, -- ISO8601 126 last_activity_date TEXT NOT NULL, -- -- ISO8601 127 last_modification_date TEXT NOT NULL, -- ISO8601 128 close_date TEXT, -- ISO8601 129 130 cost REAL, 131 plan_date TEXT NOT NULL, -- -- ISO8601 132 133 content TEXT NOT NULL, 134 content_html TEXT NOT NULL, 135 136 thread_id INTEGER REFERENCES thread (id), --may be null 137 cause_id INTEGER REFERENCES thread_cause (id) --may be null 138); 139 140CREATE TRIGGER task_tr_insert 141 INSERT 142 ON task 143 WHEN new.thread_id IS NOT NULL 144BEGIN 145 UPDATE thread 146 SET task_count = task_count + 1 147 WHERE id = new.thread_id; 148 UPDATE thread 149 SET task_sum_cost = task_sum_cost + new.cost 150 WHERE id = new.thread_id; 151END; 152 153CREATE TRIGGER task_tr_update_cost_count 154 UPDATE OF thread_id, cost 155 ON task 156BEGIN 157 UPDATE thread 158 SET task_sum_cost = task_sum_cost - old.cost 159 WHERE id = old.thread_id; 160 UPDATE thread 161 SET task_sum_cost = task_sum_cost + new.cost 162 WHERE id = new.thread_id; 163 164 UPDATE thread 165 SET task_count = task_count - 1 166 WHERE id = old.thread_id; 167 UPDATE thread 168 SET task_count = task_count + 1 169 WHERE id = new.thread_id; 170END; 171 172CREATE TRIGGER task_tr_update_state_opened_closed 173 UPDATE OF thread_id, state 174 ON task 175 WHEN old.state = 'opened' AND new.state IN ('closed', 'rejected') 176BEGIN 177 UPDATE thread 178 SET task_count_closed = task_count_closed + 1 179 WHERE id = new.thread_id; 180END; 181 182CREATE TRIGGER task_tr_update_state_closed_opened 183 UPDATE OF thread_id, state 184 ON task 185 WHEN old.state IN ('closed', 'rejected') AND new.state = 'opened' 186BEGIN 187 UPDATE thread 188 SET task_count_closed = task_count_closed - 1 189 WHERE id = old.thread_id; 190END; 191 192CREATE TRIGGER task_tr_update_state_closed_closed 193 UPDATE OF thread_id, state 194 ON task 195 WHEN old.state IN ('closed', 'rejected') AND new.state IN ('closed', 'rejected') 196BEGIN 197 UPDATE thread 198 SET task_count_closed = task_count_closed - 1 199 WHERE id = old.thread_id; 200 UPDATE thread 201 SET task_count_closed = task_count_closed + 1 202 WHERE id = new.thread_id; 203END; 204 205CREATE TABLE task_participant ( 206 thread_id INTEGER NOT NULL REFERENCES thread (id), 207 user_id TEXT NOT NULL, 208 209 original_poster BOOLEAN NOT NULL DEFAULT 0, 210 assignee BOOLEAN NOT NULL DEFAULT 0, 211 212 commentator BOOLEAN NOT NULL DEFAULT 0, 213 subscribent BOOLEAN NOT NULL DEFAULT 0, 214 215 added_by TEXT NOT NULL, -- user who added the participant. Equals user_id when user subscribed himself 216 added_date TEXT NOT NULL, -- ISO8601 217 218 PRIMARY KEY (thread_id, user_id) 219); 220 221CREATE TABLE task_comment ( 222 id INTEGER NOT NULL PRIMARY KEY, 223 224 task_id INTEGER NOT NULL REFERENCES task (id), 225 226 type INTEGER NOT NULL DEFAULT 0, -- 0 -comment, 1 - closing comment 227 228 author TEXT NOT NULL, 229 create_date TEXT NOT NULL, -- ISO8601 230 last_modification_date TEXT NOT NULL, -- ISO8601 231 232 content TEXT NOT NULL, 233 content_html TEXT NOT NULL 234); 235 236CREATE TABLE authentication_token ( 237 page_id TEXT PRIMARY KEY, 238 token TEXT NOT NULL, 239 240 generated_by TEXT NOT NULL, 241 generation_date TEXT NOT NULL, 242 expire_date TEXT 243);