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, closing_comment 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 TRIGGER task_tr_insert 170 INSERT 171 ON task 172 WHEN new.thread_id IS NOT NULL 173BEGIN 174 UPDATE thread 175 SET task_count = task_count + 1 176 WHERE id = new.thread_id; 177 UPDATE thread 178 SET task_sum_cost = task_sum_cost + new.cost 179 WHERE id = new.thread_id; 180END; 181 182CREATE TRIGGER task_tr_update_cost_count 183 UPDATE OF thread_id, cost 184 ON task 185BEGIN 186 UPDATE thread 187 SET task_sum_cost = task_sum_cost - old.cost 188 WHERE id = old.thread_id; 189 UPDATE thread 190 SET task_sum_cost = task_sum_cost + new.cost 191 WHERE id = new.thread_id; 192 193 UPDATE thread 194 SET task_count = task_count - 1 195 WHERE id = old.thread_id; 196 UPDATE thread 197 SET task_count = task_count + 1 198 WHERE id = new.thread_id; 199END; 200 201CREATE TRIGGER task_tr_update_state_opened_closed 202 UPDATE OF thread_id, state 203 ON task 204 WHEN old.state = 'opened' AND new.state = 'done' 205BEGIN 206 UPDATE thread 207 SET task_count_closed = task_count_closed + 1 208 WHERE id = new.thread_id; 209END; 210 211CREATE TRIGGER task_tr_update_state_closed_opened 212 UPDATE OF thread_id, state 213 ON task 214 WHEN old.state = 'done' AND new.state = 'opened' 215BEGIN 216 UPDATE thread 217 SET task_count_closed = task_count_closed - 1 218 WHERE id = old.thread_id; 219END; 220 221CREATE TRIGGER task_tr_update_state_closed_closed 222 UPDATE OF thread_id, state 223 ON task 224 WHEN old.state = 'done' AND new.state = 'done' 225BEGIN 226 UPDATE thread 227 SET task_count_closed = task_count_closed - 1 228 WHERE id = old.thread_id; 229 UPDATE thread 230 SET task_count_closed = task_count_closed + 1 231 WHERE id = new.thread_id; 232END; 233 234-- thread_comment triggers 235 236CREATE TRIGGER thread_comment_tr_insert 237 INSERT 238 ON task 239BEGIN 240 UPDATE thread_comment 241 SET task_count = task_count + 1 242 WHERE id = new.thread_comment_id; 243END; 244 245CREATE TRIGGER thread_comment_tr_delete 246 DELETE 247 ON task 248BEGIN 249 UPDATE thread_comment 250 SET task_count = task_count - 1 251 WHERE id = old.thread_comment_id; 252END; 253 254CREATE TRIGGER thread_comment_tr_thread_comment_id 255 UPDATE OF thread_comment_id 256 ON task 257BEGIN 258 UPDATE thread_comment 259 SET task_count = task_count - 1 260 WHERE id = old.thread_comment_id; 261 262 UPDATE thread_comment 263 SET task_count = task_count + 1 264 WHERE id = new.thread_comment_id; 265END; 266 267-- end of thread_comment triggers 268 269-- task_program triggers 270CREATE TRIGGER task_program_tr_insert 271 INSERT 272 ON task 273BEGIN 274 UPDATE task_program 275 SET count = count + 1 276 WHERE id = new.task_program_id; 277END; 278 279CREATE TRIGGER task_program_tr_delete 280 DELETE 281 ON task 282BEGIN 283 UPDATE task_program 284 SET count = count - 1 285 WHERE id = old.task_program_id; 286END; 287 288CREATE TRIGGER task_program_tr_update_task_program_id 289 UPDATE OF task_program_id 290 ON task 291BEGIN 292 UPDATE task_program 293 SET count = count - 1 294 WHERE id = old.task_program_id; 295 296 UPDATE task_program 297 SET count = count + 1 298 WHERE id = new.task_program_id; 299END; 300-- end of task_program triggres 301 302CREATE TABLE task_participant ( 303 task_id INTEGER NOT NULL REFERENCES thread (id), 304 user_id TEXT NOT NULL, 305 306 original_poster BOOLEAN NOT NULL DEFAULT 0, 307 assignee BOOLEAN NOT NULL DEFAULT 0, 308 309 commentator BOOLEAN NOT NULL DEFAULT 0, 310 subscribent BOOLEAN NOT NULL DEFAULT 0, 311 312 added_by TEXT NOT NULL, -- user who added the participant. Equals user_id when user subscribed himself 313 added_date TEXT NOT NULL, -- ISO8601 314 315 PRIMARY KEY (task_id, user_id) 316); 317 318CREATE TABLE task_comment ( 319 id INTEGER NOT NULL PRIMARY KEY, 320 321 task_id INTEGER NOT NULL REFERENCES task (id), 322 323 author TEXT NOT NULL, 324 create_date TEXT NOT NULL, -- ISO8601 325 last_modification_date TEXT NOT NULL, -- ISO8601 326 327 content TEXT NOT NULL, 328 content_html TEXT NOT NULL 329); 330 331CREATE TABLE authentication_token ( 332 page_id TEXT NOT NULL, 333 token TEXT NOT NULL, 334 335 generated_by TEXT NOT NULL, 336 generation_date TEXT NOT NULL, 337 expire_date TEXT, 338 339 PRIMARY KEY (page_id, token) 340);