1 2create table thread ( 3 id INTEGER NOT NULL PRIMARY KEY, 4 5 original_poster TEXT NOT NULL, 6 coordinator TEXT NULL, -- NULL - proposal 7 8 private BOOLEAN NOT NULL DEFAULT 0, -- 0 - public, 1 - private 9 lock BOOLEAN NOT NULL DEFAULT 0, -- 0 - unlocked, 1 - locked 10 11 type INTEGER NOT NULL DEFAULT 0, -- 0 - project, 1 - issue 12 state INTEGER TEXT NOT NULL DEFAULT 'proposal', -- proposal,opened,done,closed,rejected 13 14 create_date TEXT NOT NULL, -- ISO8601 15 last_activity_date TEXT NOT NULL, -- ISO8601 16 close_date TEXT, -- ISO8601 17 18 title TEXT NOT NULL, 19 content TEXT NOT NULL, 20 content_html TEXT NOT NULL, 21 22 priority INTEGER NOT NULL DEFAULT -1, -- dependent on tasks 23 24 task_count INTEGER NOT NULL DEFAULT 0, 25 task_count_open INTEGER NOT NULL DEFAULT 0, 26 task_sum_cost REAL 27); 28 29create table thread_participant ( 30 thread_id INTEGER NOT NULL REFERENCES thread (id), 31 user_id TEXT NOT NULL, 32 33 original_poster BOOLEAN NOT NULL DEFAULT 0, 34 coordinator BOOLEAN NOT NULL DEFAULT 0, 35 36 commentator BOOLEAN NOT NULL DEFAULT 0, 37 task_assignee BOOLEAN NOT NULL DEFAULT 0, 38 subscribent BOOLEAN NOT NULL DEFAULT 0, 39 40 added_by TEXT NOT NULL, -- user who added the participant. Equals user_id when user subscribed himself 41 added_date TEXT NOT NULL, -- ISO8601 42 43 PRIMARY KEY (thread_id, user_id) 44); 45 46create table thread_comment ( 47 id INTEGER NOT NULL PRIMARY KEY, 48 49 thread_id INTEGER NOT NULL REFERENCES thread (id), 50 51 type INTEGER NOT NULL DEFAULT 0, -- 0 -comment, 1 - closing comment, 2 - real cause, 3 - potential cause 52 53 author TEXT NOT NULL, 54 create_date TEXT NOT NULL, -- ISO8601 55 56 content TEXT NOT NULL, 57 content_html TEXT NOT NULL 58); 59 60create table label ( 61 id INTEGER NOT NULL PRIMARY KEY, 62 name TEXT UNIQUE NOT NULL, 63 count INTEGER NOT NULL DEFAULT 0 64); 65 66create index label_ix_name ON label (name); 67 68create table thread_label ( 69 thread_id INTEGER NOT NULL, 70 label_id INTEGER NOT NULL, 71 PRIMARY KEY (thread_id, label_id) 72); 73 74create table task ( 75 id INTEGER NOT NULL PRIMARY KEY, 76 77 original_poster TEXT NOT NULL, 78 assignee TEXT NOT NULL, 79 80 private BOOLEAN NOT NULL DEFAULT 0, -- 0 - public, 1 - private 81 lock BOOLEAN NOT NULL DEFAULT 0, -- 0 - unlocked, 1 - locked 82 83 state INTEGER NOT NULL DEFAULT 0, -- 0 - opened, 5 - done, 10 - closed, 15 - rejected 84 85 create_date TEXT NOT NULL, -- ISO8601 86 last_activity_date TEXT NOT NULL, -- -- ISO8601 87 close_date TEXT, -- ISO8601 88 89 content TEXT NOT NULL, 90 content_html TEXT NOT NULL, 91 92 cause_id INTEGER REFERENCES thread_cause (id) --may be null 93); 94 95create table task_participant ( 96 thread_id INTEGER NOT NULL REFERENCES thread (id), 97 user_id TEXT NOT NULL, 98 99 original_poster BOOLEAN NOT NULL DEFAULT 0, 100 assignee BOOLEAN NOT NULL DEFAULT 0, 101 102 commentator BOOLEAN NOT NULL DEFAULT 0, 103 subscribent BOOLEAN NOT NULL DEFAULT 0, 104 105 added_by TEXT NOT NULL, -- user who added the participant. Equals user_id when user subscribed himself 106 added_date TEXT NOT NULL, -- ISO8601 107 108 PRIMARY KEY (thread_id, user_id) 109); 110 111create table task_comment ( 112 id INTEGER NOT NULL PRIMARY KEY, 113 114 task_id INTEGER NOT NULL REFERENCES task (id), 115 116 type INTEGER NOT NULL DEFAULT 0, -- 0 -comment, 1 - closing comment 117 118 author TEXT NOT NULL, 119 create_date TEXT NOT NULL, -- ISO8601 120 121 content TEXT NOT NULL, 122 content_html TEXT NOT NULL 123);