5 -- The general idea is to move data from the 2
16 -- set up bug ArtifactTypes for each group
19 UPDATE groups SET bug_due_period='2592000' WHERE bug_due_period is null;
20 INSERT INTO artifact_group_list
21 (group_artifact_id,group_id,name,description,is_public,
22 allow_anon,email_all_updates,email_address,due_period,use_resolution,datatype)
23 SELECT group_id+100000,group_id,'Bugs','Bug Tracking System',use_bugs,
24 1,send_all_bugs,new_bug_address,bug_due_period,1,1
26 WHERE status != 'I' AND status != 'P'
27 ORDER BY group_id ASC;
32 INSERT INTO artifact_perm
33 (group_artifact_id,user_id,perm_level)
34 SELECT group_id+100000,user_id,bug_flags
38 -- bug groups conversion
40 INSERT INTO artifact_group (id,group_artifact_id,group_name)
41 SELECT bug_group_id+100000,group_id+100000,group_name FROM bug_group;
44 -- bug category conversion
46 INSERT INTO artifact_category (id,group_artifact_id,category_name,auto_assign_to)
47 SELECT bug_category_id+100000,group_id+100000,category_name,100 FROM bug_category;
52 -- bug tracker had status_id of 100 (None) and status_id=3 (closed)
54 UPDATE bug SET status_id=1 WHERE status_id=100;
55 INSERT INTO bug_status (status_id,status_name) VALUES (2,'Open');
56 UPDATE bug SET status_id=2 WHERE status_id=3;
57 DELETE FROM bug_status WHERE status_id=3;
59 UPDATE bug SET close_date=0 WHERE close_date is NULL;
60 UPDATE bug SET summary=0 WHERE summary is NULL;
61 UPDATE bug SET details='' WHERE details is NULL;
64 (artifact_id,group_artifact_id,status_id,category_id,artifact_group_id,priority,
65 submitted_by,assigned_to,open_date,close_date,summary,details,resolution_id)
67 bug_id+100000,group_id+100000,status_id,category_id+100000,bug_group_id+100000,priority,
68 submitted_by,assigned_to,date,close_date,summary,details,resolution_id
70 ORDER BY group_id ASC;
75 --UPDATE bug_history SET old_value=1 WHERE old_value='100' AND field_name='status_id';
76 UPDATE bug_history SET old_value=2 WHERE old_value='3' AND field_name='status_id';
79 --SELECT * from bug_history
80 --WHERE NOT EXISTS (select bug_id FROM bug
81 --where bug.bug_id=bug_history.bug_id);
84 --DELETE FROM bug_history WHERE bug_id=0;
86 --DELETE FROM bug_history
87 --WHERE bug_id+100000 NOT IN (SELECT artifact_id FROM artifact);
89 INSERT INTO artifact_history
90 (artifact_id,field_name,old_value,mod_by,entrydate)
92 bug_id+100000,field_name,old_value,mod_by,date
94 WHERE field_name IN ('summary','resolution_id','priority','group_id','close_date','assigned_to','status_id');
96 INSERT INTO artifact_history
97 (artifact_id,field_name,old_value,mod_by,entrydate)
99 bug_id+100000,'artifact_group_id',(old_value::int)+100000,mod_by,date
101 WHERE field_name='bug_group_id';
103 INSERT INTO artifact_history
104 (artifact_id,field_name,old_value,mod_by,entrydate)
106 bug_id+100000,field_name,(old_value::int)+100000,mod_by,date
108 WHERE field_name='category_id';
113 INSERT INTO artifact_message
114 (artifact_id,submitted_by,from_email,adddate,body)
116 bh.bug_id+100000,bh.mod_by,users.email,bh.date,bh.old_value
117 FROM bug_history bh, users
118 WHERE bh.mod_by=users.user_id
119 AND bh.field_name='details';
122 -- bug canned responses
124 delete from bug_canned_responses where title is null;
126 INSERT INTO artifact_canned_responses
127 (group_artifact_id,title,body)
129 group_id+100000,title,body
130 FROM bug_canned_responses
142 -- set up support ArtifactTypes for each group
146 UPDATE groups SET support_due_period='2592000' WHERE support_due_period is null;
148 INSERT INTO artifact_group_list
149 (group_artifact_id,group_id,name,description,is_public,
150 allow_anon,email_all_updates,email_address,due_period,use_resolution,datatype)
151 SELECT group_id+200000,group_id,'Support Requests','Tech Support Tracking System',use_support,
152 1,send_all_support,new_support_address,support_due_period,0,2
154 WHERE status != 'I' AND status != 'P'
155 ORDER BY group_id ASC;
160 INSERT INTO artifact_perm
161 (group_artifact_id,user_id,perm_level)
162 SELECT group_id+200000,user_id,support_flags
166 -- support category conversion
168 INSERT INTO artifact_category (id,group_artifact_id,category_name,auto_assign_to)
169 SELECT support_category_id+200000,group_id+200000,category_name,100 FROM support_category;
174 DELETE FROM support WHERE NOT EXISTS
175 (SELECT group_id FROM groups WHERE support.group_id=groups.group_id);
177 UPDATE patch SET summary=0 WHERE summary is NULL;
178 UPDATE patch SET details='' WHERE details is NULL;
181 (artifact_id,group_artifact_id,status_id,category_id,artifact_group_id,priority,
182 submitted_by,assigned_to,open_date,close_date,summary,details,resolution_id)
184 support_id+200000,group_id+200000,support_status_id,support_category_id+200000,100,priority,
185 submitted_by,assigned_to,open_date,close_date,summary,'',100
187 ORDER BY group_id ASC;
192 DELETE FROM support_history WHERE support_id=0;
194 INSERT INTO artifact_history
195 (artifact_id,field_name,old_value,mod_by,entrydate)
197 support_id+200000,field_name,old_value,mod_by,date
200 field_name IN ('summary','priority','close_date','assigned_to');
202 INSERT INTO artifact_history
203 (artifact_id,field_name,old_value,mod_by,entrydate)
205 support_id+200000,'category_id',(old_value::int)+200000,mod_by,date
208 field_name='support_category_id';
210 INSERT INTO artifact_history
211 (artifact_id,field_name,old_value,mod_by,entrydate)
213 support_id+200000,'status_id',old_value,mod_by,date
216 field_name='support_status_id';
221 DELETE FROM support_messages WHERE NOT EXISTS
222 (SELECT support_id FROM support WHERE support.support_id=support_messages.support_id);
224 INSERT INTO artifact_message
225 (artifact_id,submitted_by,from_email,adddate,body)
227 support_id+200000,100,from_email,date,body
228 FROM support_messages;
233 INSERT INTO artifact_canned_responses
234 (group_artifact_id,title,body)
236 group_id+200000,title,body
237 FROM support_canned_responses
251 -- set up patch ArtifactTypes for each group
253 UPDATE groups SET patch_due_period='2592000' WHERE patch_due_period is null;
255 INSERT INTO artifact_group_list
256 (group_artifact_id,group_id,name,description,is_public,
257 allow_anon,email_all_updates,email_address,due_period,use_resolution,datatype)
258 SELECT group_id+300000,group_id,'Patches','Patch Tracking System',use_patch,
259 1,send_all_patches,new_patch_address,patch_due_period,1,3
261 WHERE status != 'I' AND status != 'P'
262 ORDER BY group_id ASC;
267 INSERT INTO artifact_perm
268 (group_artifact_id,user_id,perm_level)
269 SELECT group_id+300000,user_id,patch_flags
273 -- patch category conversion
275 INSERT INTO artifact_category (id,group_artifact_id,category_name,auto_assign_to)
276 SELECT patch_category_id+300000,group_id+300000,category_name,100 FROM patch_category;
281 -- moving the odd patch statuses to resolutions
283 ALTER TABLE patch ADD COLUMN resolution_id INT;
284 UPDATE patch SET resolution_id = 0;
285 ALTER TABLE patch ALTER COLUMN resolution_id SET NOT NULL;
286 ALTER TABLE patch ALTER COLUMN resolution_id SET DEFAULT 100;
288 UPDATE patch SET resolution_id=patch_status_id;
289 vacuum analyze patch;
290 update patch set patch_status_id=2 where patch_status_id > 3;
291 update patch set resolution_id=100 WHERE resolution_id < 4;
292 INSERT INTO artifact_resolution VALUES (102,'Accepted');
293 INSERT INTO artifact_resolution VALUES (103,'Out of Date');
294 INSERT INTO artifact_resolution VALUES (104,'Postponed');
295 INSERT INTO artifact_resolution VALUES (105,'Rejected');
296 update patch set resolution_id=104 WHERE resolution_id=4;
297 update patch set resolution_id=105 WHERE resolution_id=101;
299 delete from patch where patch_id=100000;
300 UPDATE patch SET details=' ' WHERE details is null;
303 (artifact_id,group_artifact_id,status_id,category_id,artifact_group_id,priority,
304 submitted_by,assigned_to,open_date,close_date,summary,details,resolution_id)
306 patch_id+300000,group_id+300000,patch_status_id,patch_category_id+300000,100,5,
307 submitted_by,assigned_to,open_date,close_date,summary,details,resolution_id
309 ORDER BY group_id ASC;
314 INSERT INTO artifact_history
315 (artifact_id,field_name,old_value,mod_by,entrydate)
317 patch_id+300000,field_name,old_value,mod_by,date
319 WHERE field_name IN ('summary','close_date','assigned_to','Patch Code');
321 INSERT INTO artifact_history
322 (artifact_id,field_name,old_value,mod_by,entrydate)
324 patch_id+300000,'status_id',old_value,mod_by,date
326 WHERE field_name='patch_status_id';
328 INSERT INTO artifact_history
329 (artifact_id,field_name,old_value,mod_by,entrydate)
331 patch_id+300000,'category_id',(old_value::int)+300000,mod_by,date
333 WHERE field_name='patch_category_id';
338 INSERT INTO artifact_message
339 (artifact_id,submitted_by,from_email,adddate,body)
341 ph.patch_id+300000,ph.mod_by,users.email,ph.date,ph.old_value
342 FROM patch_history ph, users
343 WHERE ph.mod_by=users.user_id
344 AND ph.field_name='details';
349 INSERT INTO artifact_file
350 (artifact_id,description,bin_data,filename,filesize,filetype,adddate,submitted_by)
351 SELECT patch_id+300000,'None',code,'None',length(code),'text/plain',open_date,submitted_by
353 WHERE code IS NOT NULL;
357 INSERT INTO artifact_counts_agg
358 SELECT group_artifact_id,count(*)
361 GROUP BY group_artifact_id;
368 INSERT INTO artifact_group_list
369 (group_artifact_id,group_id,name,description,is_public,
370 allow_anon,email_all_updates,email_address,due_period,use_resolution,datatype)
371 SELECT group_id+350000,group_id,'Feature Requests','Feature Request Tracking System',1,
372 1,0,'',45*24*60*60,0,4
374 WHERE status != 'I' AND status != 'P'
375 ORDER BY group_id ASC;
377 vacuum analyze artifact_perm;
378 vacuum analyze artifact_group_list;
379 vacuum analyze artifact;
380 vacuum analyze artifact_history;
381 vacuum analyze artifact_category;
382 vacuum analyze artifact_group;
383 vacuum analyze artifact_file;
384 vacuum analyze artifact_message;