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