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 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 TEXT NOT NULL DEFAULT 'comment', -- comment, cause_real, cause_potential -- will be: comment, cause, risk 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 task_count INTEGER NOT NULL DEFAULT 0 64); 65 66CREATE VIEW thread_comment_view 67 AS 68 SELECT thread_comment.*, 69 thread.coordinator AS coordinator 70 FROM thread_comment 71 JOIN thread ON thread_comment.thread_id = thread.id; 72 73CREATE INDEX thread_comment_ix_thread_id 74 ON thread_comment (thread_id); 75 76CREATE TABLE label ( 77 id INTEGER NOT NULL PRIMARY KEY, 78 name TEXT UNIQUE NOT NULL, 79 color TEXT NULL, -- color of the label, hex RGB: xxxxxx 80 count INTEGER NOT NULL DEFAULT 0, 81 82 added_by TEXT NOT NULL, -- user who added the label 83 added_date TEXT NOT NULL -- ISO8601 84); 85 86CREATE INDEX label_ix_name 87 ON label (name); 88 89CREATE TABLE thread_label ( 90 thread_id INTEGER NOT NULL, 91 label_id INTEGER NOT NULL, 92 PRIMARY KEY (thread_id, label_id) 93); 94 95CREATE TRIGGER thread_label_tr_insert 96 INSERT 97 ON thread_label 98BEGIN 99 UPDATE label 100 SET count = count + 1 101 WHERE id = new.label_id; 102END; 103 104CREATE TRIGGER thread_label_tr_delete 105 DELETE 106 ON thread_label 107BEGIN 108 UPDATE label 109 SET count = count - 1 110 WHERE id = old.label_id; 111END; 112 113CREATE TRIGGER thread_label_tr_update_label_id 114 UPDATE OF label_id 115 ON thread_label 116BEGIN 117 UPDATE label 118 SET count = count - 1 119 WHERE id = old.label_id; 120 121 UPDATE label 122 SET count = count + 1 123 WHERE id = new.label_id; 124END; 125 126CREATE TABLE task_program ( 127 id INTEGER NOT NULL PRIMARY KEY, 128 name TEXT UNIQUE NOT NULL, 129 count INTEGER NOT NULL DEFAULT 0, 130 131 added_by TEXT NOT NULL, -- user who added the label 132 added_date TEXT NOT NULL -- ISO8601 133); 134 135-- we cannot delete tasks (so not triggers provided) 136CREATE TABLE task ( 137 id INTEGER NOT NULL PRIMARY KEY, 138 139 original_poster TEXT NOT NULL, 140 assignee TEXT NOT NULL, 141 closed_by TEXT NULL, -- who closed the task 142 143 private BOOLEAN NOT NULL DEFAULT 0, -- 0 - public, 1 - private 144 lock BOOLEAN NOT NULL DEFAULT 0, -- 0 - unlocked, 1 - locked 145 146 state TEXT NOT NULL DEFAULT 'opened', -- opened, done 147 type TEXT NOT NULL DEFAULT 'correction', -- correction, corrective, preventive, program 148 149 create_date TEXT NOT NULL, -- ISO8601 150 last_activity_date TEXT NOT NULL, -- -- ISO8601 151 last_modification_date TEXT NOT NULL, -- ISO8601 152 close_date TEXT, -- ISO8601 153 154 cost REAL, 155 plan_date TEXT NOT NULL, -- -- ISO8601 156 all_day_event INTEGER NOT NULL DEFAULT 0, -- 0 - false, 1 - true 157 start_time TEXT NULL, -- HH:MM 158 finish_time TEXT NULL, -- HH:MM 159 160 content TEXT NOT NULL, 161 content_html TEXT NOT NULL, 162 163 thread_id INTEGER REFERENCES thread (id), --may be null 164 thread_comment_id INTEGER REFERENCES thread_comment (id), --may be null 165 task_program_id INTEGER REFERENCES task_program (id) --may be null 166); 167 168CREATE INDEX task_ix_thread_id_thread_comment_id 169 ON task (thread_id, thread_comment_id); 170 171CREATE INDEX task_ix_task_program_id 172 ON task(task_program_id); 173 174CREATE TRIGGER task_tr_insert_task_count 175 INSERT 176 ON task 177 WHEN new.thread_id IS NOT NULL 178BEGIN 179 UPDATE thread 180 SET task_count = task_count + 1 181 WHERE id = new.thread_id; 182END; 183 184CREATE TRIGGER task_tr_insert_task_sum_cost 185 INSERT 186 ON task 187 WHEN new.thread_id IS NOT NULL AND new.cost IS NOT NULL 188BEGIN 189 UPDATE thread 190 SET task_sum_cost = coalesce(task_sum_cost, 0) + new.cost 191 WHERE id = new.thread_id; 192END; 193 194CREATE TRIGGER task_tr_update_task_count 195 UPDATE OF thread_id 196 ON task 197BEGIN 198 UPDATE thread 199 SET task_count = task_count - 1 200 WHERE id = old.thread_id; 201 UPDATE thread 202 SET task_count = task_count + 1 203 WHERE id = new.thread_id; 204END; 205 206CREATE TRIGGER task_tr_update_task_sum_cost_old_cost_not_null 207 UPDATE OF thread_id, cost 208 ON task 209 WHEN old.cost IS NOT NULL 210BEGIN 211 UPDATE thread 212 SET task_sum_cost = task_sum_cost - old.cost 213 WHERE id = old.thread_id; 214END; 215 216CREATE TRIGGER task_tr_update_task_sum_cost_new_cost_not_null 217 UPDATE OF thread_id, cost 218 ON task 219 WHEN new.cost IS NOT NULL 220BEGIN 221 UPDATE thread 222 SET task_sum_cost = coalesce(task_sum_cost, 0) + new.cost 223 WHERE id = new.thread_id; 224END; 225 226CREATE TRIGGER task_tr_update_state_opened_closed 227 UPDATE OF thread_id, state 228 ON task 229 WHEN old.state = 'opened' AND new.state = 'done' 230BEGIN 231 UPDATE thread 232 SET task_count_closed = task_count_closed + 1 233 WHERE id = new.thread_id; 234END; 235 236CREATE TRIGGER task_tr_update_state_closed_opened 237 UPDATE OF thread_id, state 238 ON task 239 WHEN old.state = 'done' AND new.state = 'opened' 240BEGIN 241 UPDATE thread 242 SET task_count_closed = task_count_closed - 1 243 WHERE id = old.thread_id; 244END; 245 246CREATE TRIGGER task_tr_update_state_closed_closed 247 UPDATE OF thread_id, state 248 ON task 249 WHEN old.state = 'done' AND new.state = 'done' 250BEGIN 251 UPDATE thread 252 SET task_count_closed = task_count_closed - 1 253 WHERE id = old.thread_id; 254 UPDATE thread 255 SET task_count_closed = task_count_closed + 1 256 WHERE id = new.thread_id; 257END; 258 259-- thread_comment triggers 260 261CREATE TRIGGER thread_comment_tr_insert 262 INSERT 263 ON task 264BEGIN 265 UPDATE thread_comment 266 SET task_count = task_count + 1 267 WHERE id = new.thread_comment_id; 268END; 269 270CREATE TRIGGER thread_comment_tr_delete 271 DELETE 272 ON task 273BEGIN 274 UPDATE thread_comment 275 SET task_count = task_count - 1 276 WHERE id = old.thread_comment_id; 277END; 278 279CREATE TRIGGER thread_comment_tr_thread_comment_id 280 UPDATE OF thread_comment_id 281 ON task 282BEGIN 283 UPDATE thread_comment 284 SET task_count = task_count - 1 285 WHERE id = old.thread_comment_id; 286 287 UPDATE thread_comment 288 SET task_count = task_count + 1 289 WHERE id = new.thread_comment_id; 290END; 291 292-- end of thread_comment triggers 293 294-- task_program triggers 295CREATE TRIGGER task_program_tr_insert 296 INSERT 297 ON task 298BEGIN 299 UPDATE task_program 300 SET count = count + 1 301 WHERE id = new.task_program_id; 302END; 303 304CREATE TRIGGER task_program_tr_delete 305 DELETE 306 ON task 307BEGIN 308 UPDATE task_program 309 SET count = count - 1 310 WHERE id = old.task_program_id; 311END; 312 313CREATE TRIGGER task_program_tr_update_task_program_id 314 UPDATE OF task_program_id 315 ON task 316BEGIN 317 UPDATE task_program 318 SET count = count - 1 319 WHERE id = old.task_program_id; 320 321 UPDATE task_program 322 SET count = count + 1 323 WHERE id = new.task_program_id; 324END; 325-- end of task_program triggres 326 327CREATE TABLE task_participant ( 328 task_id INTEGER NOT NULL REFERENCES thread (id), 329 user_id TEXT NOT NULL, 330 331 original_poster BOOLEAN NOT NULL DEFAULT 0, 332 assignee BOOLEAN NOT NULL DEFAULT 0, 333 334 commentator BOOLEAN NOT NULL DEFAULT 0, 335 subscribent BOOLEAN NOT NULL DEFAULT 0, 336 337 added_by TEXT NOT NULL, -- user who added the participant. Equals user_id when user subscribed himself 338 added_date TEXT NOT NULL, -- ISO8601 339 340 PRIMARY KEY (task_id, user_id) 341); 342 343CREATE TABLE task_comment ( 344 id INTEGER NOT NULL PRIMARY KEY, 345 346 task_id INTEGER NOT NULL REFERENCES task (id), 347 348 author TEXT NOT NULL, 349 create_date TEXT NOT NULL, -- ISO8601 350 last_modification_date TEXT NOT NULL, -- ISO8601 351 352 content TEXT NOT NULL, 353 content_html TEXT NOT NULL 354); 355 356CREATE TABLE authentication_token ( 357 page_id TEXT NOT NULL, 358 token TEXT NOT NULL, 359 360 generated_by TEXT NOT NULL, 361 generation_date TEXT NOT NULL, 362 expire_date TEXT, 363 364 PRIMARY KEY (page_id, token) 365); 366 367CREATE VIEW task_view 368 AS 369 SELECT 370 task.*, 371 task_program.name AS task_program_name, 372 thread.coordinator AS coordinator, 373 CASE WHEN task.state = 'done' THEN NULL 374 WHEN task.plan_date >= date('now', '+1 month') THEN '2' 375 WHEN task.plan_date >= date('now') THEN '1' 376 ELSE '0' END AS priority 377 FROM task 378 LEFT JOIN task_program ON task.task_program_id = task_program.id 379 LEFT JOIN thread ON task.thread_id = thread.id; 380 381CREATE VIEW thread_view 382 AS 383 SELECT thread.id, thread.original_poster, thread.coordinator, thread.closed_by, 384 thread.private, thread.lock, thread.type, 385 thread.create_date, thread.last_activity_date, thread.last_modification_date, thread.close_date, 386 thread.title, thread.content, thread.content_html, 387 thread.task_count, thread.task_count_closed, thread.task_sum_cost, 388 label.id AS label_id, 389 label.name AS label_name, 390 (SELECT MIN(priority) FROM task_view WHERE task_view.thread_id = thread.id) AS priority, 391 CASE WHEN thread.state = 'opened' AND thread.task_count > 0 AND thread.task_count = thread.task_count_closed THEN 'done' 392 ELSE thread.state END AS state 393 FROM thread 394 LEFT JOIN thread_label ON thread.id = thread_label.thread_id 395 LEFT JOIN label ON label.id = thread_label.label_id; 396