1CREATE TABLE thread ( 2 id INTEGER NOT NULL PRIMARY KEY, 3 4 original_poster TEXT NOT NULL, 5 coordinator TEXT NULL, -- NULL - proposal 6 closed_by TEXT NULL, -- who closed or rejected the thread 7 8 9 private BOOLEAN NOT NULL DEFAULT 0, -- 0 - public, 1 - private 10 lock BOOLEAN NOT NULL DEFAULT 0, -- 0 - unlocked, 1 - locked 11 12 type TEXT NOT NULL DEFAULT 'issue', -- issue, project 13 state TEXT NOT NULL DEFAULT 'proposal', -- proposal,opened,done,closed,rejected 14 15 create_date TEXT NOT NULL, -- ISO8601 16 last_activity_date TEXT NOT NULL, -- ISO8601 17 last_modification_date TEXT NOT NULL, -- ISO8601 18 close_date TEXT, -- ISO8601 19 20 title TEXT NOT NULL, 21 content TEXT NOT NULL, 22 content_html TEXT NOT NULL, 23 24 priority INTEGER NOT NULL DEFAULT -1, -- dependent on tasks 25 26 task_count INTEGER NOT NULL DEFAULT 0, 27 task_count_closed INTEGER NOT NULL DEFAULT 0, 28 task_sum_cost REAL 29); 30 31CREATE INDEX thread_ix_last_activity_date 32 ON thread (last_activity_date); -- to speedup order by 33 34CREATE TABLE thread_participant ( 35 thread_id INTEGER NOT NULL REFERENCES thread (id), 36 user_id TEXT NOT NULL, 37 38 original_poster BOOLEAN NOT NULL DEFAULT 0, 39 coordinator BOOLEAN NOT NULL DEFAULT 0, 40 41 commentator BOOLEAN NOT NULL DEFAULT 0, 42 task_assignee BOOLEAN NOT NULL DEFAULT 0, 43 subscribent BOOLEAN NOT NULL DEFAULT 0, 44 45 added_by TEXT NOT NULL, -- user who added the participant. Equals user_id when user subscribed himself 46 added_date TEXT NOT NULL, -- ISO8601 47 48 PRIMARY KEY (thread_id, user_id) 49); 50 51CREATE TABLE thread_comment ( 52 id INTEGER NOT NULL PRIMARY KEY, 53 54 thread_id INTEGER NOT NULL REFERENCES thread (id), 55 56 type TEXT NOT NULL DEFAULT 'comment', -- comment, cause_real, cause_potential -- will be: comment, cause, risk 57 58 author TEXT NOT NULL, 59 create_date TEXT NOT NULL, -- ISO8601 60 last_modification_date TEXT NOT NULL, -- ISO8601 61 62 content TEXT NOT NULL, 63 content_html TEXT NOT NULL, 64 65 task_count INTEGER NOT NULL DEFAULT 0 66); 67 68CREATE INDEX thread_comment_ix_thread_id 69 ON thread_comment (thread_id); 70 71CREATE TABLE label ( 72 id INTEGER NOT NULL PRIMARY KEY, 73 name TEXT UNIQUE NOT NULL, 74 color TEXT NULL, -- color of the label, hex RGB: xxxxxx 75 count INTEGER NOT NULL DEFAULT 0, 76 77 added_by TEXT NOT NULL, -- user who added the label 78 added_date TEXT NOT NULL -- ISO8601 79); 80 81CREATE INDEX label_ix_name 82 ON label (name); 83 84CREATE TABLE thread_label ( 85 thread_id INTEGER NOT NULL, 86 label_id INTEGER NOT NULL, 87 PRIMARY KEY (thread_id, label_id) 88); 89 90CREATE TRIGGER thread_label_tr_insert 91 INSERT 92 ON thread_label 93BEGIN 94 UPDATE label 95 SET count = count + 1 96 WHERE id = new.label_id; 97END; 98 99CREATE TRIGGER thread_label_tr_delete 100 DELETE 101 ON thread_label 102BEGIN 103 UPDATE label 104 SET count = count - 1 105 WHERE id = old.label_id; 106END; 107 108CREATE TRIGGER thread_label_tr_update_label_id 109 UPDATE OF label_id 110 ON thread_label 111BEGIN 112 UPDATE label 113 SET count = count - 1 114 WHERE id = old.label_id; 115 116 UPDATE label 117 SET count = count + 1 118 WHERE id = new.label_id; 119END; 120 121CREATE TABLE task_program ( 122 id INTEGER NOT NULL PRIMARY KEY, 123 name TEXT UNIQUE NOT NULL, 124 count INTEGER NOT NULL DEFAULT 0, 125 126 added_by TEXT NOT NULL, -- user who added the label 127 added_date TEXT NOT NULL -- ISO8601 128); 129 130-- we cannot delete tasks (so not triggers provided) 131CREATE TABLE task ( 132 id INTEGER NOT NULL PRIMARY KEY, 133 134 original_poster TEXT NOT NULL, 135 assignee TEXT NOT NULL, 136 closed_by TEXT NULL, -- who closed the task 137 138 private BOOLEAN NOT NULL DEFAULT 0, -- 0 - public, 1 - private 139 lock BOOLEAN NOT NULL DEFAULT 0, -- 0 - unlocked, 1 - locked 140 141 state TEXT NOT NULL DEFAULT 'opened', -- opened, done 142 type TEXT NOT NULL DEFAULT 'correction', -- correction, corrective, preventive, program 143 144 create_date TEXT NOT NULL, -- ISO8601 145 last_activity_date TEXT NOT NULL, -- -- ISO8601 146 last_modification_date TEXT NOT NULL, -- ISO8601 147 close_date TEXT, -- ISO8601 148 149 cost REAL, 150 plan_date TEXT NOT NULL, -- -- ISO8601 151 all_day_event INTEGER NOT NULL DEFAULT 0, -- 0 - false, 1 - true 152 start_time TEXT NULL, -- HH:MM 153 finish_time TEXT NULL, -- HH:MM 154 155 content TEXT NOT NULL, 156 content_html TEXT NOT NULL, 157 158 thread_id INTEGER REFERENCES thread (id), --may be null 159 thread_comment_id INTEGER REFERENCES thread_comment (id), --may be null 160 task_program_id INTEGER REFERENCES task_program (id) --may be null 161); 162 163CREATE INDEX task_ix_thread_id_thread_comment_id 164 ON task (thread_id, thread_comment_id); 165 166CREATE INDEX task_ix_task_program_id 167 ON task(task_program_id); 168 169CREATE VIEW task_view 170 AS 171 SELECT 172 task.*, 173 task_program.name AS task_program_name, 174 thread.coordinator AS coordinator, 175 CASE WHEN task.state = 'done' THEN NULL 176 WHEN task.plan_date >= date('now', '+1 month') THEN '2' 177 WHEN task.plan_date >= date('now') THEN '1' 178 ELSE '0' END AS priority 179 FROM task 180 LEFT JOIN task_program ON task.task_program_id = task_program.id 181 LEFT JOIN thread ON task.thread_id = thread.id; 182 183CREATE TRIGGER task_tr_insert 184 INSERT 185 ON task 186 WHEN new.thread_id IS NOT NULL 187BEGIN 188 UPDATE thread 189 SET task_count = task_count + 1 190 WHERE id = new.thread_id; 191 UPDATE thread 192 SET task_sum_cost = task_sum_cost + new.cost 193 WHERE id = new.thread_id; 194END; 195 196CREATE TRIGGER task_tr_update_cost_count 197 UPDATE OF thread_id, cost 198 ON task 199BEGIN 200 UPDATE thread 201 SET task_sum_cost = task_sum_cost - old.cost 202 WHERE id = old.thread_id; 203 UPDATE thread 204 SET task_sum_cost = task_sum_cost + new.cost 205 WHERE id = new.thread_id; 206 207 UPDATE thread 208 SET task_count = task_count - 1 209 WHERE id = old.thread_id; 210 UPDATE thread 211 SET task_count = task_count + 1 212 WHERE id = new.thread_id; 213END; 214 215CREATE TRIGGER task_tr_update_state_opened_closed 216 UPDATE OF thread_id, state 217 ON task 218 WHEN old.state = 'opened' AND new.state = 'done' 219BEGIN 220 UPDATE thread 221 SET task_count_closed = task_count_closed + 1 222 WHERE id = new.thread_id; 223END; 224 225CREATE TRIGGER task_tr_update_state_closed_opened 226 UPDATE OF thread_id, state 227 ON task 228 WHEN old.state = 'done' AND new.state = 'opened' 229BEGIN 230 UPDATE thread 231 SET task_count_closed = task_count_closed - 1 232 WHERE id = old.thread_id; 233END; 234 235CREATE TRIGGER task_tr_update_state_closed_closed 236 UPDATE OF thread_id, state 237 ON task 238 WHEN old.state = 'done' AND new.state = 'done' 239BEGIN 240 UPDATE thread 241 SET task_count_closed = task_count_closed - 1 242 WHERE id = old.thread_id; 243 UPDATE thread 244 SET task_count_closed = task_count_closed + 1 245 WHERE id = new.thread_id; 246END; 247 248-- thread_comment triggers 249 250CREATE TRIGGER thread_comment_tr_insert 251 INSERT 252 ON task 253BEGIN 254 UPDATE thread_comment 255 SET task_count = task_count + 1 256 WHERE id = new.thread_comment_id; 257END; 258 259CREATE TRIGGER thread_comment_tr_delete 260 DELETE 261 ON task 262BEGIN 263 UPDATE thread_comment 264 SET task_count = task_count - 1 265 WHERE id = old.thread_comment_id; 266END; 267 268CREATE TRIGGER thread_comment_tr_thread_comment_id 269 UPDATE OF thread_comment_id 270 ON task 271BEGIN 272 UPDATE thread_comment 273 SET task_count = task_count - 1 274 WHERE id = old.thread_comment_id; 275 276 UPDATE thread_comment 277 SET task_count = task_count + 1 278 WHERE id = new.thread_comment_id; 279END; 280 281-- end of thread_comment triggers 282 283-- task_program triggers 284CREATE TRIGGER task_program_tr_insert 285 INSERT 286 ON task 287BEGIN 288 UPDATE task_program 289 SET count = count + 1 290 WHERE id = new.task_program_id; 291END; 292 293CREATE TRIGGER task_program_tr_delete 294 DELETE 295 ON task 296BEGIN 297 UPDATE task_program 298 SET count = count - 1 299 WHERE id = old.task_program_id; 300END; 301 302CREATE TRIGGER task_program_tr_update_task_program_id 303 UPDATE OF task_program_id 304 ON task 305BEGIN 306 UPDATE task_program 307 SET count = count - 1 308 WHERE id = old.task_program_id; 309 310 UPDATE task_program 311 SET count = count + 1 312 WHERE id = new.task_program_id; 313END; 314-- end of task_program triggres 315 316CREATE TABLE task_participant ( 317 task_id INTEGER NOT NULL REFERENCES thread (id), 318 user_id TEXT NOT NULL, 319 320 original_poster BOOLEAN NOT NULL DEFAULT 0, 321 assignee BOOLEAN NOT NULL DEFAULT 0, 322 323 commentator BOOLEAN NOT NULL DEFAULT 0, 324 subscribent BOOLEAN NOT NULL DEFAULT 0, 325 326 added_by TEXT NOT NULL, -- user who added the participant. Equals user_id when user subscribed himself 327 added_date TEXT NOT NULL, -- ISO8601 328 329 PRIMARY KEY (task_id, user_id) 330); 331 332CREATE TABLE task_comment ( 333 id INTEGER NOT NULL PRIMARY KEY, 334 335 task_id INTEGER NOT NULL REFERENCES task (id), 336 337 author TEXT NOT NULL, 338 create_date TEXT NOT NULL, -- ISO8601 339 last_modification_date TEXT NOT NULL, -- ISO8601 340 341 content TEXT NOT NULL, 342 content_html TEXT NOT NULL 343); 344 345CREATE TABLE authentication_token ( 346 page_id TEXT NOT NULL, 347 token TEXT NOT NULL, 348 349 generated_by TEXT NOT NULL, 350 generation_date TEXT NOT NULL, 351 expire_date TEXT, 352 353 PRIMARY KEY (page_id, token) 354);