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_thread_comment_id 271 UPDATE OF thread_comment_id 272 ON task 273BEGIN 274 UPDATE thread_comment 275 SET task_count = task_count - 1 276 WHERE id = old.thread_comment_id; 277 278 UPDATE thread_comment 279 SET task_count = task_count + 1 280 WHERE id = new.thread_comment_id; 281END; 282 283-- end of thread_comment triggers 284 285-- task_program triggers 286CREATE TRIGGER task_program_tr_insert 287 INSERT 288 ON task 289BEGIN 290 UPDATE task_program 291 SET count = count + 1 292 WHERE id = new.task_program_id; 293END; 294 295CREATE TRIGGER task_program_tr_delete 296 DELETE 297 ON task 298BEGIN 299 UPDATE task_program 300 SET count = count - 1 301 WHERE id = old.task_program_id; 302END; 303 304CREATE TRIGGER task_program_tr_update_task_program_id 305 UPDATE OF task_program_id 306 ON task 307BEGIN 308 UPDATE task_program 309 SET count = count - 1 310 WHERE id = old.task_program_id; 311 312 UPDATE task_program 313 SET count = count + 1 314 WHERE id = new.task_program_id; 315END; 316-- end of task_program triggres 317 318CREATE TABLE task_participant ( 319 task_id INTEGER NOT NULL REFERENCES thread (id), 320 user_id TEXT NOT NULL, 321 322 original_poster BOOLEAN NOT NULL DEFAULT 0, 323 assignee BOOLEAN NOT NULL DEFAULT 0, 324 325 commentator BOOLEAN NOT NULL DEFAULT 0, 326 subscribent BOOLEAN NOT NULL DEFAULT 0, 327 328 added_by TEXT NOT NULL, -- user who added the participant. Equals user_id when user subscribed himself 329 added_date TEXT NOT NULL, -- ISO8601 330 331 PRIMARY KEY (task_id, user_id) 332); 333 334CREATE TABLE task_comment ( 335 id INTEGER NOT NULL PRIMARY KEY, 336 337 task_id INTEGER NOT NULL REFERENCES task (id), 338 339 author TEXT NOT NULL, 340 create_date TEXT NOT NULL, -- ISO8601 341 last_modification_date TEXT NOT NULL, -- ISO8601 342 343 content TEXT NOT NULL, 344 content_html TEXT NOT NULL 345); 346 347CREATE TABLE authentication_token ( 348 page_id TEXT NOT NULL, 349 token TEXT NOT NULL, 350 351 generated_by TEXT NOT NULL, 352 generation_date TEXT NOT NULL, 353 expire_date TEXT, 354 355 PRIMARY KEY (page_id, token) 356); 357 358CREATE VIEW task_view 359 AS 360 SELECT 361 task.*, 362 task_program.name AS task_program_name, 363 thread.coordinator AS coordinator, 364 CASE WHEN task.state = 'done' THEN NULL 365 WHEN task.plan_date >= date('now', '+1 month') THEN '2' 366 WHEN task.plan_date >= date('now') THEN '1' 367 ELSE '0' END AS priority 368 FROM task 369 LEFT JOIN task_program ON task.task_program_id = task_program.id 370 LEFT JOIN thread ON task.thread_id = thread.id; 371 372CREATE VIEW thread_view 373 AS 374 SELECT thread.id, thread.original_poster, thread.coordinator, thread.closed_by, 375 thread.private, thread.lock, thread.type, 376 thread.create_date, thread.last_activity_date, thread.last_modification_date, thread.close_date, 377 thread.title, thread.content, thread.content_html, 378 thread.task_count, thread.task_count_closed, thread.task_sum_cost, 379 label.id AS label_id, 380 label.name AS label_name, 381 (SELECT MIN(priority) FROM task_view WHERE task_view.thread_id = thread.id) AS priority, 382 CASE WHEN thread.state = 'opened' AND thread.task_count > 0 AND thread.task_count = thread.task_count_closed THEN 'done' 383 ELSE thread.state END AS state 384 FROM thread 385 LEFT JOIN thread_label ON thread.id = thread_label.thread_id 386 LEFT JOIN label ON label.id = thread_label.label_id; 387