2 -- need to widen the preference column in user_preferences
3 -- postgres isn't as slick as MySQL in this respect
6 ALTER TABLE user_preferences RENAME COLUMN preference_value TO dead1;
7 ALTER TABLE user_preferences ADD COLUMN preference_value TEXT;
8 UPDATE user_preferences SET preference_value=dead1;
9 UPDATE user_preferences SET dead1='';
12 -- user_group permissions flag
14 ALTER TABLE user_group ADD COLUMN artifact_flags INT;
15 ALTER TABLE user_group ALTER COLUMN artifact_flags SET NOT NULL;
16 ALTER TABLE user_group ALTER COLUMN artifact_flags SET DEFAULT 0;
18 UPDATE user_group SET artifact_flags=0;
21 -- each group can have multiple artifact types
23 create table artifact_group_list (
24 group_artifact_id serial primary key,
25 group_id int not null,
28 is_public int not null default 0,
29 allow_anon int not null default 0,
30 email_all_updates int not null default 0,
31 email_address text not null,
32 due_period int not null default 2592000,
33 use_resolution int not null default 0,
34 submit_instructions text,
35 browse_instructions text,
36 datatype int not null default 0
39 CREATE INDEX artgrouplist_groupid on artifact_group_list (group_id);
40 CREATE INDEX artgrouplist_groupid_public on artifact_group_list (group_id,is_public);
42 create table artifact_resolution (
43 id serial primary key,
47 INSERT INTO artifact_resolution SELECT * FROM bug_resolution;
50 -- new permissions model required
52 create table artifact_perm (
53 id serial primary key,
54 group_artifact_id int not null,
56 perm_level int not null DEFAULT 0
59 CREATE INDEX artperm_groupartifactid on artifact_perm (group_artifact_id);
60 CREATE UNIQUE INDEX artperm_groupartifactid_userid on artifact_perm (group_artifact_id,user_id);
63 -- create a view to make selecting all perms for a user_id/group_id easier
66 CREATE VIEW artifactperm_user_vw AS
67 SELECT ap.id, ap.group_artifact_id, ap.user_id, ap.perm_level, users.user_name, users.realname
68 FROM artifact_perm ap, users
69 WHERE users.user_id=ap.user_id;
71 CREATE VIEW artifactperm_artgrouplist_vw AS
72 SELECT agl.group_artifact_id,agl.name,agl.description,agl.group_id,ap.user_id, ap.perm_level
73 FROM artifact_perm ap, artifact_group_list agl
74 WHERE ap.group_artifact_id=agl.group_artifact_id;
77 -- similar to bug_category
79 CREATE TABLE artifact_category (
80 id serial primary key,
81 group_artifact_id int NOT NULL,
82 category_name text NOT NULL,
83 auto_assign_to int not null DEFAULT 100
86 CREATE INDEX artcategory_groupartifactid on artifact_category (group_artifact_id);
89 -- similar to bug_group
91 CREATE TABLE artifact_group (
92 id serial primary key,
93 group_artifact_id int NOT NULL,
94 group_name text NOT NULL
97 CREATE INDEX artgroup_groupartifactid on artifact_group (group_artifact_id);
100 -- similar to bug_status
102 CREATE TABLE artifact_status (
103 id serial primary key,
104 status_name text NOT NULL
108 -- similar to bug table
110 CREATE TABLE artifact (
111 artifact_id serial primary key,
112 group_artifact_id int NOT NULL,
113 status_id int DEFAULT '1' NOT NULL,
114 category_id int DEFAULT '100' NOT NULL,
115 artifact_group_id int DEFAULT '0' NOT NULL,
116 resolution_id int not null default '100',
117 priority int DEFAULT '5' NOT NULL,
118 submitted_by int DEFAULT '100' NOT NULL,
119 assigned_to int DEFAULT '100' NOT NULL,
120 open_date int DEFAULT '0' NOT NULL,
121 close_date int DEFAULT '0' NOT NULL,
122 summary text NOT NULL,
123 details text NOT NULL
126 CREATE INDEX art_groupartid ON artifact (group_artifact_id);
127 CREATE INDEX art_groupartid_statusid ON artifact (group_artifact_id,status_id);
128 CREATE INDEX art_groupartid_assign ON artifact (group_artifact_id,assigned_to);
129 CREATE INDEX art_groupartid_submit ON artifact (group_artifact_id,submitted_by);
130 create index art_submit_status ON artifact(submitted_by,status_id);
131 create index art_assign_status ON artifact(assigned_to,status_id);
132 create index art_groupartid_artifactid on artifact (group_artifact_id,artifact_id);
136 CREATE VIEW artifact_vw AS
139 u.user_name AS assigned_unixname,
140 u.realname AS assigned_realname,
141 u.email AS assigned_email,
142 u2.user_name AS submitted_unixname,
143 u2.realname AS submitted_realname,
144 u2.email AS submitted_email,
145 artifact_status.status_name,
146 artifact_category.category_name,
147 artifact_group.group_name,
148 artifact_resolution.resolution_name
150 users u, users u2, artifact, artifact_status, artifact_category, artifact_group, artifact_resolution
152 artifact.assigned_to=u.user_id
153 AND artifact.submitted_by=u2.user_id
154 AND artifact.status_id=artifact_status.id
155 AND artifact.category_id=artifact_category.id
156 AND artifact.artifact_group_id=artifact_group.id
157 AND artifact.resolution_id=artifact_resolution.id;
163 CREATE TABLE artifact_history (
164 id serial primary key,
165 artifact_id int DEFAULT '0' NOT NULL,
166 field_name text DEFAULT '' NOT NULL,
167 old_value text DEFAULT '' NOT NULL,
168 mod_by int DEFAULT '0' NOT NULL,
169 entrydate int DEFAULT '0' NOT NULL
172 CREATE INDEX arthistory_artid on artifact_history(artifact_id);
173 CREATE INDEX arthistory_artid_entrydate on artifact_history(artifact_id,entrydate);
176 -- create a view from the audit trail which joins the user table and history table
178 CREATE VIEW artifact_history_user_vw AS
179 SELECT ah.id, ah.artifact_id, ah.field_name, ah.old_value, ah.entrydate, users.user_name
180 FROM artifact_history ah, users
181 WHERE ah.mod_by=users.user_id;
184 -- files attached to a given artifact
186 CREATE TABLE artifact_file (
187 id serial primary key,
188 artifact_id int NOT NULL,
189 description text NOT NULL,
190 bin_data text NOT NULL,
191 filename text NOT NULL,
192 filesize int NOT NULL,
193 filetype text NOT NULL,
194 adddate int not null DEFAULT '0',
195 submitted_by int not null
198 CREATE INDEX artfile_artid on artifact_file(artifact_id);
199 CREATE INDEX artfile_artid_adddate on artifact_file(artifact_id,adddate);
202 -- create a view from the files which joins the user table and files table
204 CREATE VIEW artifact_file_user_vw AS
205 SELECT af.id, af.artifact_id, af.description, af.bin_data, af.filename, af.filesize, af.filetype,
206 af.adddate, af.submitted_by, users.user_name, users.realname
207 FROM artifact_file af,users
208 WHERE af.submitted_by=users.user_id;
211 -- messages and comments attached to an artifact
213 CREATE TABLE artifact_message (
214 id serial primary key,
215 artifact_id int NOT NULL,
216 submitted_by int not null,
217 from_email text NOT NULL,
218 adddate int DEFAULT '0' NOT NULL,
222 CREATE INDEX artmessage_artid on artifact_message(artifact_id);
223 CREATE INDEX artmessage_artid_adddate on artifact_message(artifact_id,adddate);
226 -- create a view from the messages which joins the user table and messages table
228 CREATE VIEW artifact_message_user_vw AS
229 SELECT am.id, am.artifact_id, am.from_email, am.body, am.adddate,
230 users.user_id, users.email, users.user_name, users.realname
231 FROM artifact_message am,users
232 WHERE am.submitted_by=users.user_id;
235 -- table containing list of people monitoring each artifact
237 CREATE TABLE artifact_monitor (
238 id serial primary key,
239 artifact_id int NOT NULL,
240 user_id int not null,
244 CREATE INDEX artmonitor_artifactid on artifact_monitor(artifact_id);
246 ALTER TABLE artifact_monitor ADD CONSTRAINT artifactmonitor_artifactid_fk
247 FOREIGN KEY (artifact_id) REFERENCES artifact(artifact_id) MATCH FULL;
249 INSERT INTO artifact_group_list VALUES (100,1,'Default','Default Data - Dont Edit',3,0,0,'0',0);
250 INSERT INTO artifact_category VALUES (100,100,'None',100);
251 INSERT INTO artifact_group VALUES (100,100,'None');
252 INSERT INTO artifact_status VALUES (1,'Open');
253 INSERT INTO artifact_status VALUES (2,'Closed');
254 INSERT INTO artifact_status VALUES (3,'Deleted');
256 CREATE TABLE artifact_canned_responses (
257 id serial primary key,
258 group_artifact_id int NOT NULL,
263 CREATE INDEX artifactcannedresponses_groupid ON artifact_canned_responses (group_artifact_id);
265 CREATE TABLE artifact_counts_agg (
266 group_artifact_id int not null,
269 CREATE INDEX artifactcountsagg_groupartid ON artifact_counts_agg(group_artifact_id);