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 color TEXT NULL, -- color of the label, hex RGB: xxxxxx 68 count INTEGER NOT NULL DEFAULT 0, 69 70 added_by TEXT NOT NULL, -- user who added the label 71 added_date TEXT NOT NULL -- ISO8601 72); 73 74CREATE INDEX label_ix_name 75 ON label (name); 76 77CREATE TABLE thread_label ( 78 thread_id INTEGER NOT NULL, 79 label_id INTEGER NOT NULL, 80 PRIMARY KEY (thread_id, label_id) 81); 82 83CREATE TRIGGER thread_label_tr_insert 84 INSERT 85 ON thread_label 86BEGIN 87 UPDATE label 88 SET count = count + 1 89 WHERE id = new.label_id; 90END; 91 92CREATE TRIGGER thread_label_tr_delete 93 DELETE 94 ON thread_label 95BEGIN 96 UPDATE label 97 SET count = count - 1 98 WHERE id = old.label_id; 99END; 100 101CREATE TRIGGER thread_label_tr_update_label_id 102 UPDATE OF label_id 103 ON thread_label 104BEGIN 105 UPDATE label 106 SET count = count - 1 107 WHERE id = old.label_id; 108 109 UPDATE label 110 SET count = count + 1 111 WHERE id = new.label_id; 112END; 113 114-- we cannot delete tasks (so not triggers provided) 115CREATE TABLE task ( 116 id INTEGER NOT NULL PRIMARY KEY, 117 118 original_poster TEXT NOT NULL, 119 assignee TEXT NOT NULL, 120 121 private BOOLEAN NOT NULL DEFAULT 0, -- 0 - public, 1 - private 122 lock BOOLEAN NOT NULL DEFAULT 0, -- 0 - unlocked, 1 - locked 123 124 state TEXT NOT NULL DEFAULT 'opened', -- opened, closed, rejected 125 126 create_date TEXT NOT NULL, -- ISO8601 127 last_activity_date TEXT NOT NULL, -- -- ISO8601 128 last_modification_date TEXT NOT NULL, -- ISO8601 129 close_date TEXT, -- ISO8601 130 131 cost REAL, 132 plan_date TEXT NOT NULL, -- -- ISO8601 133 134 content TEXT NOT NULL, 135 content_html TEXT NOT NULL, 136 137 thread_id INTEGER REFERENCES thread (id), --may be null 138 cause_id INTEGER REFERENCES thread_cause (id) --may be null 139); 140 141CREATE TRIGGER task_tr_insert 142 INSERT 143 ON task 144 WHEN new.thread_id IS NOT NULL 145BEGIN 146 UPDATE thread 147 SET task_count = task_count + 1 148 WHERE id = new.thread_id; 149 UPDATE thread 150 SET task_sum_cost = task_sum_cost + new.cost 151 WHERE id = new.thread_id; 152END; 153 154CREATE TRIGGER task_tr_update_cost_count 155 UPDATE OF thread_id, cost 156 ON task 157BEGIN 158 UPDATE thread 159 SET task_sum_cost = task_sum_cost - old.cost 160 WHERE id = old.thread_id; 161 UPDATE thread 162 SET task_sum_cost = task_sum_cost + new.cost 163 WHERE id = new.thread_id; 164 165 UPDATE thread 166 SET task_count = task_count - 1 167 WHERE id = old.thread_id; 168 UPDATE thread 169 SET task_count = task_count + 1 170 WHERE id = new.thread_id; 171END; 172 173CREATE TRIGGER task_tr_update_state_opened_closed 174 UPDATE OF thread_id, state 175 ON task 176 WHEN old.state = 'opened' AND new.state IN ('closed', 'rejected') 177BEGIN 178 UPDATE thread 179 SET task_count_closed = task_count_closed + 1 180 WHERE id = new.thread_id; 181END; 182 183CREATE TRIGGER task_tr_update_state_closed_opened 184 UPDATE OF thread_id, state 185 ON task 186 WHEN old.state IN ('closed', 'rejected') AND new.state = 'opened' 187BEGIN 188 UPDATE thread 189 SET task_count_closed = task_count_closed - 1 190 WHERE id = old.thread_id; 191END; 192 193CREATE TRIGGER task_tr_update_state_closed_closed 194 UPDATE OF thread_id, state 195 ON task 196 WHEN old.state IN ('closed', 'rejected') AND new.state IN ('closed', 'rejected') 197BEGIN 198 UPDATE thread 199 SET task_count_closed = task_count_closed - 1 200 WHERE id = old.thread_id; 201 UPDATE thread 202 SET task_count_closed = task_count_closed + 1 203 WHERE id = new.thread_id; 204END; 205 206CREATE TABLE task_participant ( 207 thread_id INTEGER NOT NULL REFERENCES thread (id), 208 user_id TEXT NOT NULL, 209 210 original_poster BOOLEAN NOT NULL DEFAULT 0, 211 assignee BOOLEAN NOT NULL DEFAULT 0, 212 213 commentator BOOLEAN NOT NULL DEFAULT 0, 214 subscribent BOOLEAN NOT NULL DEFAULT 0, 215 216 added_by TEXT NOT NULL, -- user who added the participant. Equals user_id when user subscribed himself 217 added_date TEXT NOT NULL, -- ISO8601 218 219 PRIMARY KEY (thread_id, user_id) 220); 221 222CREATE TABLE task_comment ( 223 id INTEGER NOT NULL PRIMARY KEY, 224 225 task_id INTEGER NOT NULL REFERENCES task (id), 226 227 type INTEGER NOT NULL DEFAULT 0, -- 0 -comment, 1 - closing comment 228 229 author TEXT NOT NULL, 230 create_date TEXT NOT NULL, -- ISO8601 231 last_modification_date TEXT NOT NULL, -- ISO8601 232 233 content TEXT NOT NULL, 234 content_html TEXT NOT NULL 235); 236 237CREATE TABLE authentication_token ( 238 page_id TEXT PRIMARY KEY, 239 token TEXT NOT NULL, 240 241 generated_by TEXT NOT NULL, 242 generation_date TEXT NOT NULL, 243 expire_date TEXT 244);