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