2 -- drop index downloads_idx;
3 -- create index frsdlstatsgroupagg_day_dls on frs_dlstats_group_agg (day,downloads);
4 -- create index projectweeklymetric_ranking on project_weekly_metric(ranking);
5 -- create index users_status on users(status);
6 -- drop index news_date;
7 -- create index support_groupid_assignedto_status on support(group_id,assigned_to,support_status_id);
8 -- create index support_groupid_assignedto on support(group_id,assigned_to);
9 -- create index support_groupid_status on support(group_id,support_status_id);
10 -- create index patch_groupid_assignedto_status on patch(group_id,assigned_to,patch_status_id);
11 -- create index patch_groupid_assignedto on patch(group_id,assigned_to);
12 -- create index patch_groupid_status on patch(group_id,patch_status_id);
13 -- create index projecttask_projid_status on project_task(group_project_id,status_id);
15 CREATE INDEX forummonitoredforums_user ON forum_monitored_forums(user_id);
16 CREATE INDEX filemodulemonitor_userid ON filemodule_monitor(user_id);
17 CREATE INDEX support_status_assignedto ON support(support_status_id,assigned_to);
18 CREATE INDEX bug_status_assignedto ON bug(status_id,assigned_to);
21 -- alter table filemodule_monitor add column id int not null default 0 primary key auto_increment first;
22 -- alter table frs_dlstats_filetotal_agg change column file_id file_id int not null default 0 primary key;
23 -- alter table group_cvs_history add column id int not null default 0 primary key auto_increment first;
24 -- DROP TABLE system_news;
25 -- DROP TABLE system_history;
26 -- DROP TABLE system_status;
27 -- DROP TABLE system_services;
28 -- DROP TABLE system_machines;
29 -- create index foundrynews_foundry_date_approved on foundry_news(foundry_id,approve_date,is_approved);
30 -- create index news_group_date on news_bytes(group_id,date);
31 -- create index news_approved_date on news_bytes(is_approved,date);
34 ALTER TABLE patch ADD COLUMN details text;
35 INSERT INTO themes (dirname, fullname) VALUES ('ultralite','Ultra Lite');
38 CREATE TABLE project_sums_agg (
39 group_id int NOT NULL DEFAULT 0,
41 count int NOT NULL DEFAULT 0
43 CREATE INDEX projectsumsagg_groupid ON project_sums_agg (group_id);
46 ALTER TABLE groups ADD COLUMN bug_due_period int ;
47 ALTER TABLE groups ALTER COLUMN bug_due_period SET DEFAULT 2592000;
48 UPDATE groups SET bug_due_period = 2592000 ;
49 ALTER TABLE groups ADD COLUMN patch_due_period int ;
50 ALTER TABLE groups ALTER COLUMN patch_due_period SET DEFAULT 5184000;
51 UPDATE groups SET patch_due_period = 5184000;
52 ALTER TABLE groups ADD COLUMN support_due_period int ;
53 ALTER TABLE groups ALTER COLUMN support_due_period SET DEFAULT 1296000;
54 UPDATE groups SET support_due_period = 1296000;
57 CREATE SEQUENCE "prdb_dbs_dbid_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
59 CREATE TABLE "prdb_dbs" (
60 "dbid" integer DEFAULT nextval('"prdb_dbs_dbid_seq"'::text) NOT NULL,
61 "group_id" integer NOT NULL,
62 "dbname" text NOT NULL,
63 "dbusername" text NOT NULL,
64 "dbuserpass" text NOT NULL,
65 "requestdate" integer NOT NULL,
66 "dbtype" integer NOT NULL,
67 "created_by" integer NOT NULL,
68 "state" integer NOT NULL,
69 Constraint "prdb_dbs_pkey" Primary Key ("dbid")
72 CREATE TABLE prdb_states (
76 CREATE UNIQUE INDEX idx_prdb_dbname ON prdb_dbs (dbname);
77 INSERT INTO prdb_states VALUES ('1', 'Active');
78 INSERT INTO prdb_states VALUES ('2', 'Pending Create');
79 INSERT INTO prdb_states VALUES ('3', 'Pending Delete');
80 INSERT INTO prdb_states VALUES ('4', 'Pending Update');
81 INSERT INTO prdb_states VALUES ('5', 'Failed Create');
82 INSERT INTO prdb_states VALUES ('6', 'Failed Delete');
83 INSERT INTO prdb_states VALUES ('7', 'Failed Update');
84 CREATE TABLE prdb_types (
85 dbtypeid INT PRIMARY KEY,
86 dbservername TEXT NOT NULL,
87 dbsoftware TEXT NOT NULL
89 INSERT INTO prdb_types VALUES ('1','pr-db1','mysql');
90 CREATE SEQUENCE "prweb_vhost_vhostid_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
91 CREATE TABLE "prweb_vhost" (
92 "vhostid" integer DEFAULT nextval('"prweb_vhost_vhostid_seq"'::text) NOT NULL,
96 "group_id" integer NOT NULL,
97 Constraint "prweb_vhost_pkey" Primary Key ("vhostid")
99 CREATE INDEX idx_vhost_groups ON prweb_vhost (group_id);
100 CREATE UNIQUE INDEX idx_vhost_hostnames ON prweb_vhost(vhost_name);
103 ALTER TABLE db_images ADD COLUMN upload_date int ;
104 ALTER TABLE db_images ALTER COLUMN upload_date SET DEFAULT '0' ;
105 ALTER TABLE db_images ADD COLUMN version int ;
106 ALTER TABLE db_images ALTER COLUMN version SET DEFAULT '0' ;
107 CREATE UNIQUE INDEX usergroup_uniq_groupid_userid ON user_group(group_id,user_id);
111 -- CREATE TABLE "kernel_traffic" (
112 -- "kt_id" serial primary key,
114 -- CONSTRAINT "kernel_traffic_pkey" PRIMARY KEY ("kt_id")
120 ALTER TABLE user_preferences RENAME COLUMN preference_value TO dead1;
121 ALTER TABLE user_preferences ADD COLUMN preference_value TEXT;
122 UPDATE user_preferences SET preference_value=dead1;
123 UPDATE user_preferences SET dead1='';
124 ALTER TABLE user_group ADD COLUMN artifact_flags INT ;
125 ALTER TABLE user_group ALTER COLUMN artifact_flags SET DEFAULT '0';
126 UPDATE user_group SET artifact_flags=0;
128 CREATE SEQUENCE "artifact_grou_group_artifac_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
131 -- TOC Entry ID 256 (OID 22552)
133 -- Name: artifact_group_list Type: TABLE Owner: tperdue
136 CREATE TABLE "artifact_group_list" (
137 "group_artifact_id" integer DEFAULT nextval('"artifact_grou_group_artifac_seq"'::text) NOT NULL,
138 "group_id" integer NOT NULL,
141 "is_public" integer DEFAULT 0 NOT NULL,
142 "allow_anon" integer DEFAULT 0 NOT NULL,
143 "email_all_updates" integer DEFAULT 0 NOT NULL,
144 "email_address" text NOT NULL,
145 "due_period" integer DEFAULT 2592000 NOT NULL,
146 "use_resolution" integer DEFAULT 0 NOT NULL,
147 "submit_instructions" text,
148 "browse_instructions" text,
149 "datatype" integer DEFAULT 0 NOT NULL,
150 Constraint "artifact_group_list_pkey" Primary Key ("group_artifact_id")
152 CREATE INDEX artgrouplist_groupid on artifact_group_list (group_id);
153 CREATE INDEX artgrouplist_groupid_public on artifact_group_list (group_id,is_public);
154 CREATE SEQUENCE "artifact_resolution_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
155 CREATE TABLE "artifact_resolution" (
156 "id" integer DEFAULT nextval('"artifact_resolution_id_seq"'::text) NOT NULL,
157 "resolution_name" text,
158 Constraint "artifact_resolution_pkey" Primary Key ("id")
160 INSERT INTO artifact_resolution SELECT * FROM bug_resolution;
161 CREATE SEQUENCE "artifact_perm_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
162 CREATE TABLE "artifact_perm" (
163 "id" integer DEFAULT nextval('"artifact_perm_id_seq"'::text) NOT NULL,
164 "group_artifact_id" integer NOT NULL,
165 "user_id" integer NOT NULL,
166 "perm_level" integer DEFAULT 0 NOT NULL,
167 Constraint "artifact_perm_pkey" Primary Key ("id")
169 CREATE INDEX artperm_groupartifactid on artifact_perm (group_artifact_id);
170 CREATE UNIQUE INDEX artperm_groupartifactid_userid on artifact_perm (group_artifact_id,user_id);
171 CREATE VIEW artifactperm_artgrouplist_vw AS
172 SELECT agl.group_artifact_id,agl.name,agl.description,agl.group_id,ap.user_id, ap.perm_level
173 FROM artifact_perm ap, artifact_group_list agl
174 WHERE ap.group_artifact_id=agl.group_artifact_id;
175 CREATE SEQUENCE "artifact_category_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
176 CREATE TABLE "artifact_category" (
177 "id" integer DEFAULT nextval('"artifact_category_id_seq"'::text) NOT NULL,
178 "group_artifact_id" integer NOT NULL,
179 "category_name" text NOT NULL,
180 "auto_assign_to" integer DEFAULT 100 NOT NULL,
181 Constraint "artifact_category_pkey" Primary Key ("id")
183 CREATE INDEX artcategory_groupartifactid on artifact_category (group_artifact_id);
184 CREATE SEQUENCE "artifact_group_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
185 CREATE TABLE "artifact_group" (
186 "id" integer DEFAULT nextval('"artifact_group_id_seq"'::text) NOT NULL,
187 "group_artifact_id" integer NOT NULL,
188 "group_name" text NOT NULL,
189 Constraint "artifact_group_pkey" Primary Key ("id")
191 CREATE INDEX artgroup_groupartifactid on artifact_group (group_artifact_id);
192 CREATE SEQUENCE "artifact_status_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
193 CREATE TABLE "artifact_status" (
194 "id" integer DEFAULT nextval('"artifact_status_id_seq"'::text) NOT NULL,
195 "status_name" text NOT NULL,
196 Constraint "artifact_status_pkey" Primary Key ("id")
198 CREATE SEQUENCE "artifact_artifact_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
199 CREATE TABLE "artifact" (
200 "artifact_id" integer DEFAULT nextval('"artifact_artifact_id_seq"'::text) NOT NULL,
201 "group_artifact_id" integer NOT NULL,
202 "status_id" integer DEFAULT '1' NOT NULL,
203 "category_id" integer DEFAULT '100' NOT NULL,
204 "artifact_group_id" integer DEFAULT '0' NOT NULL,
205 "resolution_id" integer DEFAULT '100' NOT NULL,
206 "priority" integer DEFAULT '5' NOT NULL,
207 "submitted_by" integer DEFAULT '100' NOT NULL,
208 "assigned_to" integer DEFAULT '100' NOT NULL,
209 "open_date" integer DEFAULT '0' NOT NULL,
210 "close_date" integer DEFAULT '0' NOT NULL,
211 "summary" text NOT NULL,
212 "details" text NOT NULL,
213 Constraint "artifact_pkey" Primary Key ("artifact_id")
215 CREATE INDEX art_groupartid ON artifact (group_artifact_id);
216 CREATE INDEX art_groupartid_statusid ON artifact (group_artifact_id,status_id);
217 CREATE INDEX art_groupartid_assign ON artifact (group_artifact_id,assigned_to);
218 CREATE INDEX art_groupartid_submit ON artifact (group_artifact_id,submitted_by);
219 CREATE INDEX art_submit_status ON artifact(submitted_by,status_id);
220 CREATE INDEX art_assign_status ON artifact(assigned_to,status_id);
221 CREATE INDEX art_groupartid_artifactid ON artifact (group_artifact_id,artifact_id);
222 CREATE SEQUENCE "artifact_history_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
223 CREATE TABLE "artifact_history" (
224 "id" integer DEFAULT nextval('"artifact_history_id_seq"'::text) NOT NULL,
225 "artifact_id" integer DEFAULT '0' NOT NULL,
226 "field_name" text DEFAULT '' NOT NULL,
227 "old_value" text DEFAULT '' NOT NULL,
228 "mod_by" integer DEFAULT '0' NOT NULL,
229 "entrydate" integer DEFAULT '0' NOT NULL,
230 Constraint "artifact_history_pkey" Primary Key ("id")
232 CREATE INDEX arthistory_artid on artifact_history(artifact_id);
233 CREATE INDEX arthistory_artid_entrydate on artifact_history(artifact_id,entrydate);
234 CREATE SEQUENCE "artifact_file_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
235 CREATE TABLE "artifact_file" (
236 "id" integer DEFAULT nextval('"artifact_file_id_seq"'::text) NOT NULL,
237 "artifact_id" integer NOT NULL,
238 "description" text NOT NULL,
239 "bin_data" text NOT NULL,
240 "filename" text NOT NULL,
241 "filesize" integer NOT NULL,
242 "filetype" text NOT NULL,
243 "adddate" integer DEFAULT '0' NOT NULL,
244 "submitted_by" integer NOT NULL,
245 Constraint "artifact_file_pkey" Primary Key ("id")
247 CREATE INDEX artfile_artid on artifact_file(artifact_id);
248 CREATE INDEX artfile_artid_adddate on artifact_file(artifact_id,adddate);
249 CREATE SEQUENCE "artifact_message_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
250 CREATE TABLE "artifact_message" (
251 "id" integer DEFAULT nextval('"artifact_message_id_seq"'::text) NOT NULL,
252 "artifact_id" integer NOT NULL,
253 "submitted_by" integer NOT NULL,
254 "from_email" text NOT NULL,
255 "adddate" integer DEFAULT '0' NOT NULL,
256 "body" text NOT NULL,
257 Constraint "artifact_message_pkey" Primary Key ("id")
259 CREATE INDEX artmessage_artid on artifact_message(artifact_id);
260 CREATE INDEX artmessage_artid_adddate on artifact_message(artifact_id,adddate);
261 CREATE SEQUENCE "artifact_monitor_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
262 CREATE TABLE "artifact_monitor" (
263 "id" integer DEFAULT nextval('"artifact_monitor_id_seq"'::text) NOT NULL,
264 "artifact_id" integer NOT NULL,
265 "user_id" integer NOT NULL,
267 Constraint "artifact_monitor_pkey" Primary Key ("id")
269 CREATE INDEX artmonitor_artifactid on artifact_monitor(artifact_id);
271 INSERT INTO artifact_group_list VALUES (100,1,'Default','Default Data - Dont Edit',3,0,0,'0',0);
272 INSERT INTO artifact_category VALUES (100,100,'None',100);
273 INSERT INTO artifact_group VALUES (100,100,'None');
274 INSERT INTO artifact_status VALUES (1,'Open');
275 INSERT INTO artifact_status VALUES (2,'Closed');
276 INSERT INTO artifact_status VALUES (3,'Deleted');
277 CREATE SEQUENCE "artifact_canned_response_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
278 CREATE TABLE "artifact_canned_responses" (
279 "id" integer DEFAULT nextval('"artifact_canned_response_id_seq"'::text) NOT NULL,
280 "group_artifact_id" integer NOT NULL,
281 "title" text NOT NULL,
282 "body" text NOT NULL,
283 Constraint "artifact_canned_responses_pkey" Primary Key ("id")
286 CREATE INDEX artifactcannedresponses_groupid ON artifact_canned_responses (group_artifact_id);
288 CREATE TABLE artifact_counts_agg (
289 group_artifact_id int not null,
292 CREATE INDEX artifactcountsagg_groupartid ON artifact_counts_agg(group_artifact_id);
294 -- Re-enable this when the "stats" account exists
298 -- artifact_group_list
301 -- artifact-conversion
302 UPDATE groups SET bug_due_period='2592000' WHERE bug_due_period is null;
303 INSERT INTO artifact_group_list
304 (group_artifact_id,group_id,name,description,is_public,
305 allow_anon,email_all_updates,email_address,due_period,use_resolution,datatype)
306 SELECT group_id+100000,group_id,'Bugs','Bug Tracking System',use_bugs,
307 1,send_all_bugs,new_bug_address,bug_due_period,1,1
309 WHERE status != 'I' AND status != 'P'
310 ORDER BY group_id ASC;
311 INSERT INTO artifact_perm
312 (group_artifact_id,user_id,perm_level)
313 SELECT group_id+100000,user_id,bug_flags
315 INSERT INTO artifact_group (id,group_artifact_id,group_name)
316 SELECT bug_group_id+100000,group_id+100000,group_name FROM bug_group;
317 INSERT INTO artifact_category (id,group_artifact_id,category_name,auto_assign_to)
318 SELECT bug_category_id+100000,group_id+100000,category_name,100 FROM bug_category;
319 UPDATE bug SET status_id=1 WHERE status_id=100;
320 INSERT INTO bug_status (status_id,status_name) VALUES (2,'Open');
321 UPDATE bug SET status_id=2 WHERE status_id=3;
322 DELETE FROM bug_status WHERE status_id=3;
323 UPDATE bug SET close_date=0 WHERE close_date is NULL;
325 (artifact_id,group_artifact_id,status_id,category_id,artifact_group_id,priority,
326 submitted_by,assigned_to,open_date,close_date,summary,details,resolution_id)
328 bug_id+100000,group_id+100000,status_id,category_id+100000,bug_group_id+100000,priority,
329 submitted_by,assigned_to,date,close_date,summary,details,resolution_id
330 FROM bug WHERE summary is not null
331 ORDER BY group_id ASC;
332 -- UPDATE bug_history SET old_value=1 WHERE old_value='100' AND field_name='status_id';
333 UPDATE bug_history SET old_value=2 WHERE old_value='3' AND field_name='status_id';
334 INSERT INTO artifact_history
335 (artifact_id,field_name,old_value,mod_by,entrydate)
337 bug_id+100000,field_name,old_value,mod_by,date
339 WHERE field_name IN ('summary','resolution_id','priority','group_id','close_date','assigned_to','status_id');
340 INSERT INTO artifact_history
341 (artifact_id,field_name,old_value,mod_by,entrydate)
343 bug_id+100000,'artifact_group_id',(old_value::int)+100000,mod_by,date
345 WHERE field_name='bug_group_id';
346 INSERT INTO artifact_history
347 (artifact_id,field_name,old_value,mod_by,entrydate)
349 bug_id+100000,field_name,(old_value::int)+100000,mod_by,date
351 WHERE field_name='category_id';
352 INSERT INTO artifact_message
353 (artifact_id,submitted_by,from_email,adddate,body)
355 bh.bug_id+100000,bh.mod_by,users.email,bh.date,bh.old_value
356 FROM bug_history bh, users
357 WHERE bh.mod_by=users.user_id
358 AND bh.field_name='details';
359 delete from bug_canned_responses where title is null;
360 INSERT INTO artifact_canned_responses
361 (group_artifact_id,title,body)
363 group_id+100000,title,body
364 FROM bug_canned_responses
366 UPDATE groups SET support_due_period='2592000' WHERE support_due_period is null;
367 INSERT INTO artifact_group_list
368 (group_artifact_id,group_id,name,description,is_public,
369 allow_anon,email_all_updates,email_address,due_period,use_resolution,datatype)
370 SELECT group_id+200000,group_id,'Support Requests','Tech Support Tracking System',use_support,
371 1,send_all_support,new_support_address,support_due_period,0,2
373 WHERE status != 'I' AND status != 'P'
374 ORDER BY group_id ASC;
375 INSERT INTO artifact_perm
376 (group_artifact_id,user_id,perm_level)
377 SELECT group_id+200000,user_id,support_flags
379 INSERT INTO artifact_category (id,group_artifact_id,category_name,auto_assign_to)
380 SELECT support_category_id+200000,group_id+200000,category_name,100 FROM support_category;
381 DELETE FROM support WHERE NOT EXISTS
382 (SELECT group_id FROM groups WHERE support.group_id=groups.group_id);
384 (artifact_id,group_artifact_id,status_id,category_id,artifact_group_id,priority,
385 submitted_by,assigned_to,open_date,close_date,summary,details,resolution_id)
387 support_id+200000,group_id+200000,support_status_id,support_category_id+200000,100,priority,
388 submitted_by,assigned_to,open_date,close_date,summary,'',100
390 ORDER BY group_id ASC;
391 DELETE FROM support_history WHERE support_id=0;
392 INSERT INTO artifact_history
393 (artifact_id,field_name,old_value,mod_by,entrydate)
395 support_id+200000,field_name,old_value,mod_by,date
398 field_name IN ('summary','priority','close_date','assigned_to');
399 INSERT INTO artifact_history
400 (artifact_id,field_name,old_value,mod_by,entrydate)
402 support_id+200000,'category_id',(old_value::int)+200000,mod_by,date
405 field_name='support_category_id';
406 INSERT INTO artifact_history
407 (artifact_id,field_name,old_value,mod_by,entrydate)
409 support_id+200000,'status_id',old_value,mod_by,date
412 field_name='support_status_id';
413 DELETE FROM support_messages WHERE NOT EXISTS
414 (SELECT support_id FROM support WHERE support.support_id=support_messages.support_id);
415 INSERT INTO artifact_message
416 (artifact_id,submitted_by,from_email,adddate,body)
418 support_id+200000,100,from_email,date,body
419 FROM support_messages;
420 INSERT INTO artifact_canned_responses
421 (group_artifact_id,title,body)
423 group_id+200000,title,body
424 FROM support_canned_responses
426 UPDATE groups SET patch_due_period='2592000' WHERE patch_due_period is null;
427 INSERT INTO artifact_group_list
428 (group_artifact_id,group_id,name,description,is_public,
429 allow_anon,email_all_updates,email_address,due_period,use_resolution,datatype)
430 SELECT group_id+300000,group_id,'Patches','Patch Tracking System',use_patch,
431 1,send_all_patches,new_patch_address,patch_due_period,1,3
433 WHERE status != 'I' AND status != 'P'
434 ORDER BY group_id ASC;
435 INSERT INTO artifact_perm
436 (group_artifact_id,user_id,perm_level)
437 SELECT group_id+300000,user_id,patch_flags
439 INSERT INTO artifact_category (id,group_artifact_id,category_name,auto_assign_to)
440 SELECT patch_category_id+300000,group_id+300000,category_name,100 FROM patch_category;
441 ALTER TABLE patch ADD COLUMN resolution_id INT ;
442 ALTER TABLE patch ALTER COLUMN resolution_id SET DEFAULT 100;
443 UPDATE patch SET resolution_id=patch_status_id;
444 -- vacuum analyze patch;
445 update patch set patch_status_id=2 where patch_status_id > 3;
446 update patch set resolution_id=100 WHERE resolution_id < 4;
447 INSERT INTO artifact_resolution VALUES (102,'Accepted');
448 INSERT INTO artifact_resolution VALUES (103,'Out of Date');
449 INSERT INTO artifact_resolution VALUES (104,'Postponed');
450 INSERT INTO artifact_resolution VALUES (105,'Rejected');
451 update patch set resolution_id=104 WHERE resolution_id=4;
452 update patch set resolution_id=105 WHERE resolution_id=101;
453 delete from patch where patch_id=100000;
454 UPDATE patch SET details=' ' WHERE details is null;
457 (artifact_id,group_artifact_id,status_id,category_id,artifact_group_id,priority,
458 submitted_by,assigned_to,open_date,close_date,summary,details,resolution_id)
460 patch_id+300000,group_id+300000,patch_status_id,patch_category_id+300000,100,5,
461 submitted_by,assigned_to,open_date,close_date,summary,details,resolution_id
463 WHERE summary is not null
464 ORDER BY group_id ASC;
465 INSERT INTO artifact_history
466 (artifact_id,field_name,old_value,mod_by,entrydate)
468 patch_id+300000,field_name,old_value,mod_by,date
470 WHERE field_name IN ('summary','close_date','assigned_to','Patch Code');
472 INSERT INTO artifact_history
473 (artifact_id,field_name,old_value,mod_by,entrydate)
475 patch_id+300000,'status_id',old_value,mod_by,date
477 WHERE field_name='patch_status_id';
479 INSERT INTO artifact_history
480 (artifact_id,field_name,old_value,mod_by,entrydate)
482 patch_id+300000,'category_id',(old_value::int)+300000,mod_by,date
484 WHERE field_name='patch_category_id';
485 INSERT INTO artifact_message
486 (artifact_id,submitted_by,from_email,adddate,body)
488 ph.patch_id+300000,ph.mod_by,users.email,ph.date,ph.old_value
489 FROM patch_history ph, users
490 WHERE ph.mod_by=users.user_id
491 AND ph.field_name='details';
493 INSERT INTO artifact_file
494 (artifact_id,description,bin_data,filename,filesize,filetype,adddate,submitted_by)
495 SELECT patch_id+300000,'None',code,'None',length(code),'text/plain',open_date,submitted_by
497 WHERE code IS NOT NULL;
499 INSERT INTO artifact_counts_agg
500 SELECT group_artifact_id,count(*)
503 GROUP BY group_artifact_id;
505 INSERT INTO artifact_group_list
506 (group_artifact_id,group_id,name,description,is_public,
507 allow_anon,email_all_updates,email_address,due_period,use_resolution,datatype)
508 SELECT group_id+350000,group_id,'Feature Requests','Feature Request Tracking System',1,
509 1,0,'',45*24*60*60,0,4
511 WHERE status != 'I' AND status != 'P'
512 ORDER BY group_id ASC;
515 -- Roland Mas 20020307 and 20020308
516 -- Drop and recreate the groups and users tables.
518 -- 1. Remove the dead columns (in groups)
519 -- 2. Get rid of the undeleteable foreign key constraints with old tables
521 ALTER TABLE groups RENAME TO old_groups ;
522 DROP INDEX groups_type ;
523 DROP INDEX groups_public ;
524 DROP INDEX groups_status ;
526 CREATE TABLE "groups" (
527 "group_id" integer DEFAULT nextval('groups_pk_seq'::text) NOT NULL,
528 "group_name" character varying(40),
529 "homepage" character varying(128),
530 "is_public" integer DEFAULT '0' NOT NULL,
531 "status" character(1) DEFAULT 'A' NOT NULL,
532 "unix_group_name" character varying(30) DEFAULT '' NOT NULL,
533 "unix_box" character varying(20) DEFAULT 'shell' NOT NULL,
534 "http_domain" character varying(80),
535 "short_description" character varying(255),
536 "cvs_box" character varying(20) DEFAULT 'cvs' NOT NULL,
537 "license" character varying(16),
538 "register_purpose" text,
539 "license_other" text,
540 "register_time" integer DEFAULT '0' NOT NULL,
542 "use_mail" integer DEFAULT '1' NOT NULL,
543 "use_survey" integer DEFAULT '1' NOT NULL,
544 "use_forum" integer DEFAULT '1' NOT NULL,
545 "use_pm" integer DEFAULT '1' NOT NULL,
546 "use_cvs" integer DEFAULT '1' NOT NULL,
547 "use_news" integer DEFAULT '1' NOT NULL,
548 "type" integer DEFAULT '1' NOT NULL,
549 "use_docman" integer DEFAULT '1' NOT NULL,
550 "new_task_address" text DEFAULT '' NOT NULL,
551 "send_all_tasks" integer DEFAULT '0' NOT NULL,
552 "use_pm_depend_box" integer DEFAULT '1' NOT NULL,
553 CONSTRAINT "groups_pkey" PRIMARY KEY ("group_id")
557 SELECT group_id, group_name, homepage, is_public, status, unix_group_name,
558 unix_box, http_domain, short_description, cvs_box, license,
559 register_purpose, license_other, register_time, rand_hash, use_mail,
560 use_survey, use_forum, use_pm, use_cvs, use_news, type, use_docman,
561 new_task_address, send_all_tasks, use_pm_depend_box
564 DROP TABLE old_groups ;
566 ALTER TABLE artifact_group_list ADD CONSTRAINT artifactgroup_groupid_fk FOREIGN KEY (group_id) REFERENCES groups(group_id) MATCH FULL ;
568 CREATE UNIQUE INDEX group_unix_uniq ON groups USING BTREE (unix_group_name varchar_ops);
569 CREATE INDEX groups_type ON groups USING BTREE (type int4_ops);
570 CREATE INDEX groups_public ON groups USING BTREE (is_public int4_ops);
571 CREATE INDEX groups_status ON groups USING BTREE (status bpchar_ops);
573 ALTER TABLE users RENAME TO old_users ;
574 DROP INDEX users_status ;
575 DROP INDEX user_user ;
576 DROP INDEX idx_users_username ;
577 DROP INDEX users_user_pw ;
579 CREATE TABLE "users" (
580 "user_id" integer DEFAULT nextval('users_pk_seq'::text) NOT NULL,
581 "user_name" text DEFAULT '' NOT NULL,
582 "email" text DEFAULT '' NOT NULL,
583 "user_pw" character varying(32) DEFAULT '' NOT NULL,
584 "realname" character varying(32) DEFAULT '' NOT NULL,
585 "status" character(1) DEFAULT 'A' NOT NULL,
586 "shell" character varying(20) DEFAULT '/bin/bash' NOT NULL,
587 "unix_pw" character varying(40) DEFAULT '' NOT NULL,
588 "unix_status" character(1) DEFAULT 'N' NOT NULL,
589 "unix_uid" integer DEFAULT '0' NOT NULL,
590 "unix_box" character varying(10) DEFAULT 'shell' NOT NULL,
591 "add_date" integer DEFAULT '0' NOT NULL,
592 "confirm_hash" character varying(32),
593 "mail_siteupdates" integer DEFAULT '0' NOT NULL,
594 "mail_va" integer DEFAULT '0' NOT NULL,
595 "authorized_keys" text,
597 "people_view_skills" integer DEFAULT '0' NOT NULL,
598 "people_resume" text DEFAULT '' NOT NULL,
599 "timezone" character varying(64) DEFAULT 'GMT',
600 "language" integer DEFAULT '1' NOT NULL,
601 CONSTRAINT "users_pkey" PRIMARY KEY ("user_id")
603 CREATE VIEW artifactperm_user_vw AS
604 SELECT ap.id, ap.group_artifact_id, ap.user_id, ap.perm_level, users.user_name, users.realname
605 FROM artifact_perm ap, users
606 WHERE users.user_id=ap.user_id;
607 CREATE VIEW artifact_vw AS
610 u.user_name AS assigned_unixname,
611 u.realname AS assigned_realname,
612 u.email AS assigned_email,
613 u2.user_name AS submitted_unixname,
614 u2.realname AS submitted_realname,
615 u2.email AS submitted_email,
616 artifact_status.status_name,
617 artifact_category.category_name,
618 artifact_group.group_name,
619 artifact_resolution.resolution_name
621 users u, users u2, artifact, artifact_status, artifact_category, artifact_group, artifact_resolution
623 artifact.assigned_to=u.user_id
624 AND artifact.submitted_by=u2.user_id
625 AND artifact.status_id=artifact_status.id
626 AND artifact.category_id=artifact_category.id
627 AND artifact.artifact_group_id=artifact_group.id
628 AND artifact.resolution_id=artifact_resolution.id;
629 CREATE VIEW artifact_history_user_vw AS
630 SELECT ah.id, ah.artifact_id, ah.field_name, ah.old_value, ah.entrydate, users.user_name
631 FROM artifact_history ah, users
632 WHERE ah.mod_by=users.user_id;
633 CREATE VIEW artifact_file_user_vw AS
634 SELECT af.id, af.artifact_id, af.description, af.bin_data, af.filename, af.filesize, af.filetype,
635 af.adddate, af.submitted_by, users.user_name, users.realname
636 FROM artifact_file af,users
637 WHERE af.submitted_by=users.user_id;
638 CREATE VIEW artifact_message_user_vw AS
639 SELECT am.id, am.artifact_id, am.from_email, am.body, am.adddate,
640 users.user_id, users.email, users.user_name, users.realname
641 FROM artifact_message am,users
642 WHERE am.submitted_by=users.user_id;
645 SELECT user_id, user_name, email, user_pw, realname, status, shell,
646 unix_pw, unix_status, unix_uid, unix_box, add_date, confirm_hash,
647 mail_siteupdates, mail_va, authorized_keys, email_new,
648 people_view_skills, people_resume, timezone, language
651 DROP TABLE old_users ;
653 ALTER TABLE user_group ADD CONSTRAINT user_group_user_id_fk
654 FOREIGN KEY (user_id) REFERENCES users(user_id) MATCH FULL ;
655 -- ALTER TABLE forum ADD CONSTRAINT forum_posted_by_fk
656 -- FOREIGN KEY (posted_by) REFERENCES users(user_id) MATCH FULL ;
657 -- ALTER TABLE forum_group_list ADD CONSTRAINT forum_group_list_group_id_fk
658 -- FOREIGN KEY (group_id) REFERENCES groups(group_id) MATCH FULL ;
659 -- ALTER TABLE project_task ADD CONSTRAINT project_task_created_by_fk
660 -- FOREIGN KEY (created_by) REFERENCES users(user_id) MATCH FULL ;
661 ALTER TABLE users ADD CONSTRAINT users_languageid_fk
662 FOREIGN KEY (language) REFERENCES supported_languages(language_id) MATCH FULL ;
664 CREATE INDEX users_status ON users USING BTREE (status bpchar_ops);
665 CREATE INDEX idx_users_username ON users USING BTREE (user_name text_ops);
666 CREATE INDEX users_user_pw ON users USING BTREE (user_pw varchar_ops);
668 -- End of Roland Mas 20020307 and 20020308
672 DELETE from artifact_perm
673 where not exists (select group_artifact_id
674 from artifact_group_list
675 where artifact_perm.group_artifact_id=artifact_group_list.group_artifact_id);
676 ALTER TABLE artifact_perm ADD CONSTRAINT artifactperm_userid_fk
677 FOREIGN KEY (user_id) REFERENCES users(user_id) MATCH FULL;
678 ALTER TABLE artifact_perm ADD CONSTRAINT artifactperm_groupartifactid_fk
679 FOREIGN KEY (group_artifact_id) REFERENCES artifact_group_list(group_artifact_id) MATCH FULL;
681 ALTER TABLE artifact_category ADD CONSTRAINT artifactcategory_autoassignto_fk
682 FOREIGN KEY (auto_assign_to) REFERENCES users(user_id) MATCH FULL;
683 ALTER TABLE artifact_category ADD CONSTRAINT artifactcategory_groupartifactid_fk
684 FOREIGN KEY (group_artifact_id) REFERENCES artifact_group_list(group_artifact_id) MATCH FULL;
686 ALTER TABLE artifact_group ADD CONSTRAINT artifactgroup_groupartifactid_fk
687 FOREIGN KEY (group_artifact_id) REFERENCES artifact_group_list(group_artifact_id) MATCH FULL;
689 ALTER TABLE artifact ADD CONSTRAINT artifact_artifactgroupid_fk
690 FOREIGN KEY (artifact_group_id) REFERENCES artifact_group(id) MATCH FULL;
691 ALTER TABLE artifact ADD CONSTRAINT artifact_assignedto_fk
692 FOREIGN KEY (assigned_to) REFERENCES users(user_id) MATCH FULL;
693 ALTER TABLE artifact ADD CONSTRAINT artifact_categoryid_fk
694 FOREIGN KEY (category_id) REFERENCES artifact_category(id) MATCH FULL;
695 ALTER TABLE artifact ADD CONSTRAINT artifact_groupartifactid_fk
696 FOREIGN KEY (group_artifact_id) REFERENCES artifact_group_list(group_artifact_id) MATCH FULL;
697 ALTER TABLE artifact ADD CONSTRAINT artifact_resolutionid_fk
698 FOREIGN KEY (resolution_id) REFERENCES artifact_resolution(id) MATCH FULL;
699 ALTER TABLE artifact ADD CONSTRAINT artifact_statusid_fk
700 FOREIGN KEY (status_id) REFERENCES artifact_status(id) MATCH FULL;
701 ALTER TABLE artifact ADD CONSTRAINT artifact_submittedby_fk
702 FOREIGN KEY (submitted_by) REFERENCES users(user_id) MATCH FULL;
704 DELETE FROM artifact_history WHERE NOT EXISTS
705 (SELECT artifact_id FROM artifact WHERE artifact.artifact_id=artifact_history.artifact_id);
706 ALTER TABLE artifact_history ADD CONSTRAINT artifacthistory_artifactid_fk
707 FOREIGN KEY (artifact_id) REFERENCES artifact(artifact_id) MATCH FULL;
708 ALTER TABLE artifact_history ADD CONSTRAINT artifacthistory_modby_fk
709 FOREIGN KEY (mod_by) REFERENCES users(user_id) MATCH FULL;
711 ALTER TABLE artifact_file ADD CONSTRAINT artifactfile_artifactid_fk
712 FOREIGN KEY (artifact_id) REFERENCES artifact(artifact_id) MATCH FULL;
713 ALTER TABLE artifact_file ADD CONSTRAINT artifactfile_submittedby_fk
714 FOREIGN KEY (submitted_by) REFERENCES users(user_id) MATCH FULL;
716 ALTER TABLE artifact_message ADD CONSTRAINT artifactmessage_artifactid_fk
717 FOREIGN KEY (artifact_id) REFERENCES artifact(artifact_id) MATCH FULL;
718 ALTER TABLE artifact_message ADD CONSTRAINT artifactmessage_submittedby_fk
719 FOREIGN KEY (submitted_by) REFERENCES users(user_id) MATCH FULL;
721 ALTER TABLE artifact_monitor ADD CONSTRAINT artifactmonitor_artifactid_fk
722 FOREIGN KEY (artifact_id) REFERENCES artifact(artifact_id) MATCH FULL;
724 SELECT setval('artifact_grou_group_artifac_seq',(SELECT max(group_artifact_id) FROM artifact_group_list));
725 --SELECT setval('artifact_perm_id_seq',(SELECT max(id) FROM artifact_perm));
726 SELECT setval('artifact_category_id_seq',(SELECT max(id) FROM artifact_category));
727 SELECT setval('artifact_group_id_seq',(SELECT max(id) FROM artifact_group));
728 --SELECT setval('artifact_status_id_seq',(SELECT max(id) FROM artifact_status));
729 SELECT setval('artifact_artifact_id_seq',(SELECT max(artifact_id) FROM artifact));
730 --SELECT setval('artifact_history_id_seq',(SELECT max(id) FROM artifact_history));
731 --SELECT setval('artifact_file_id_seq',(SELECT max(id) FROM artifact_file));
732 --SELECT setval('artifact_message_id_seq',(SELECT max(id) FROM artifact_message));
733 --SELECT setval('artifact_monitor_id_seq',(SELECT max(id) FROM artifact_monitor));
737 -- Re-enable the grants once we are sure the "backend" account exists
739 -- CREATE USER backend WITH PASSWORD 'xxxxx' NOCREATEDB NOCREATEUSER;
740 -- GRANT SELECT ON prweb_vhost TO backend;
741 -- GRANT SELECT,UPDATE ON prdb_dbs TO backend;
743 DROP SEQUENCE bug_bug_dependencies_pk_seq;
744 DROP SEQUENCE bug_canned_responses_pk_seq;
745 DROP SEQUENCE bug_category_pk_seq ;
746 DROP SEQUENCE bug_filter_pk_seq ;
747 DROP SEQUENCE bug_group_pk_seq ;
748 DROP SEQUENCE bug_history_pk_seq ;
749 DROP SEQUENCE bug_pk_seq ;
750 DROP SEQUENCE bug_resolution_pk_seq ;
751 DROP SEQUENCE bug_status_pk_seq ;
752 DROP SEQUENCE bug_task_dependencies_pk_seq ;
753 DROP SEQUENCE patch_category_pk_seq ;
754 DROP SEQUENCE patch_history_pk_seq ;
755 DROP SEQUENCE patch_pk_seq ;
756 DROP SEQUENCE patch_status_pk_seq ;
757 DROP SEQUENCE support_canned_responses_pk_seq;
758 DROP SEQUENCE support_category_pk_seq ;
759 DROP SEQUENCE support_history_pk_seq ;
760 DROP SEQUENCE support_messages_pk_seq ;
761 DROP SEQUENCE support_pk_seq ;
762 DROP SEQUENCE support_status_pk_seq ;
765 DROP TABLE bug_bug_dependencies ;
766 DROP TABLE bug_canned_responses ;
767 DROP TABLE bug_category ;
768 DROP TABLE bug_filter ;
769 DROP TABLE bug_group ;
770 DROP TABLE bug_history ;
771 DROP TABLE bug_resolution ;
772 DROP TABLE bug_status ;
773 DROP TABLE bug_task_dependencies ;
775 DROP TABLE patch_category ;
776 DROP TABLE patch_history ;
777 DROP TABLE patch_status ;
779 DROP TABLE support_canned_responses ;
780 DROP TABLE support_category ;
781 DROP TABLE support_history ;
782 DROP TABLE support_messages ;
783 DROP TABLE support_status ;
786 create unique index users_namename_uniq on users(user_name);
787 -- CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';
788 -- CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL';
789 CREATE FUNCTION forumgrouplist_insert_agg () RETURNS OPAQUE AS '
791 INSERT INTO forum_agg_msg_count (group_forum_id,count) \
792 VALUES (NEW.group_forum_id,0);
795 ' LANGUAGE 'plpgsql';
796 CREATE TRIGGER forumgrouplist_insert_trig AFTER INSERT ON forum_group_list
797 FOR EACH ROW EXECUTE PROCEDURE forumgrouplist_insert_agg();
798 CREATE RULE forum_insert_agg AS
800 DO UPDATE forum_agg_msg_count SET count=count+1
801 WHERE group_forum_id=new.group_forum_id;
802 CREATE RULE forum_delete_agg AS
804 DO UPDATE forum_agg_msg_count SET count=count-1
805 WHERE group_forum_id=old.group_forum_id;
806 ALTER TABLE artifact_counts_agg ADD COLUMN open_count int;
807 CREATE FUNCTION artifactgrouplist_insert_agg () RETURNS OPAQUE AS '
809 INSERT INTO artifact_counts_agg (group_artifact_id,count,open_count) \
810 VALUES (NEW.group_artifact_id,0,0);
813 ' LANGUAGE 'plpgsql';
815 CREATE TRIGGER artifactgrouplist_insert_trig AFTER INSERT ON artifact_group_list
816 FOR EACH ROW EXECUTE PROCEDURE artifactgrouplist_insert_agg();
817 CREATE RULE artifact_insert_agg AS
818 ON INSERT TO artifact
819 DO UPDATE artifact_counts_agg SET count=count+1,open_count=open_count+1
820 WHERE group_artifact_id=new.group_artifact_id;
821 -- drop TRIGGER artifactgroup_update_trig ON artifact;
822 -- drop function artifactgroup_update_agg();
824 CREATE FUNCTION artifactgroup_update_agg () RETURNS OPAQUE AS '
827 -- see if they are moving to a new artifacttype
828 -- if so, its a more complex operation
830 IF NEW.group_artifact_id <> OLD.group_artifact_id THEN
832 -- transferred artifacts always have a status of 1
833 -- so we will increment the new artifacttypes sums
835 UPDATE artifact_counts_agg SET count=count+1, open_count=open_count+1 \
836 WHERE group_artifact_id=NEW.group_artifact_id;
839 -- now see how to increment/decrement the old types sums
841 IF NEW.status_id <> OLD.status_id THEN
842 IF OLD.status_id = 2 THEN
843 UPDATE artifact_counts_agg SET count=count-1 \
844 WHERE group_artifact_id=OLD.group_artifact_id;
846 -- no need to do anything if it was in deleted status
851 -- Was already in open status before
853 UPDATE artifact_counts_agg SET count=count-1, open_count=open_count-1 \
854 WHERE group_artifact_id=OLD.group_artifact_id;
858 -- just need to evaluate the status flag and
859 -- increment/decrement the counter as necessary
861 IF NEW.status_id <> OLD.status_id THEN
862 IF new.status_id = 1 THEN
863 UPDATE artifact_counts_agg SET open_count=open_count+1 \
864 WHERE group_artifact_id=new.group_artifact_id;
866 IF new.status_id = 2 THEN
867 UPDATE artifact_counts_agg SET open_count=open_count-1 \
868 WHERE group_artifact_id=new.group_artifact_id;
870 IF new.status_id = 3 THEN
871 UPDATE artifact_counts_agg SET open_count=open_count-1,count=count-1 \
872 WHERE group_artifact_id=new.group_artifact_id;
880 ' LANGUAGE 'plpgsql';
881 CREATE TRIGGER artifactgroup_update_trig AFTER UPDATE ON artifact
882 FOR EACH ROW EXECUTE PROCEDURE artifactgroup_update_agg();
885 CREATE SEQUENCE "massmail_queue_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
887 CREATE TABLE "massmail_queue" (
888 "id" integer DEFAULT nextval('"massmail_queue_id_seq"'::text) NOT NULL,
889 "type" character varying(8) NOT NULL,
890 "subject" text NOT NULL,
891 "message" text NOT NULL,
892 "queued_date" integer NOT NULL,
893 "last_userid" integer DEFAULT 0 NOT NULL,
894 "failed_date" integer DEFAULT 0 NOT NULL,
895 "finished_date" integer DEFAULT 0 NOT NULL,
896 Constraint "massmail_queue_pkey" Primary Key ("id")
900 -- drop table stats_project_build_tmp;
901 drop table tmp_projs_releases_tmp;
902 delete from stats_project where day is null or week is null;
903 drop table stats_project_tmp;
904 -- drop table topproj_admins;
905 DROP TABLE frs_dlstats_agg;
906 DROP TABLE frs_dlstats_filetotal_agg_old;
907 DROP TABLE stats_agg_pages_by_browser;
908 DROP TABLE stats_agg_pages_by_day_old;
909 DROP TABLE stats_agr_filerelease;
910 DROP TABLE stats_agr_project;
911 DROP TABLE group_cvs_history;
912 CREATE TABLE frs_dlstats_file_agg_tmp AS
914 substring(day::text from 1 for 6)::int AS month,
915 substring(day::text from 7 for 2)::int AS day,
918 from frs_dlstats_file_agg;
920 DROP TABLE frs_dlstats_file_agg;
921 ALTER TABLE frs_dlstats_file_agg_tmp RENAME TO frs_dlstats_file_agg;
922 CREATE UNIQUE INDEX frsdlfileagg_month_day_file ON frs_dlstats_file_agg(month,day,file_id);
923 drop index httpdl_fid;
924 drop index httpdl_group_id;
925 create index statshttpdl_day_fileid ON stats_http_downloads(day,filerelease_id);
926 drop index ftpdl_fid;
927 drop index ftpdl_group_id;
928 create index statsftpdl_day_fileid ON stats_ftp_downloads(day,filerelease_id);
929 CREATE TABLE stats_project_metric (
930 month int not null default 0,
931 day int not null default 0,
932 ranking int not null default 0,
933 percentile float not null default 0,
934 group_id int not null default 0
936 -- copy stats_project_metric from '/tmp/stats_project_metric.dump';
937 CREATE UNIQUE INDEX statsprojectmetric_month_day_group ON stats_project_metric(month,day,group_id);
938 CREATE TABLE stats_agg_site_by_group_tmp AS
940 substring(day::text from 1 for 6)::int AS month,
941 substring(day::text from 7 for 2)::int AS day,
944 from stats_agg_site_by_group ;
946 DROP TABLE stats_agg_site_by_group;
947 ALTER TABLE stats_agg_site_by_group_tmp RENAME TO stats_agg_site_by_group;
949 DROP TABLE stats_agg_site_by_day;
951 CREATE UNIQUE INDEX statssitebygroup_month_day_group ON stats_agg_site_by_group(month,day,group_id);
952 CREATE TABLE stats_agg_logo_by_group_tmp AS
954 substring(day::text from 1 for 6)::int AS month,
955 substring(day::text from 7 for 2)::int AS day,
958 from stats_agg_logo_by_group ;
960 DROP TABLE stats_agg_logo_by_group;
961 ALTER TABLE stats_agg_logo_by_group_tmp RENAME TO stats_agg_logo_by_group;
963 CREATE UNIQUE INDEX statslogobygroup_month_day_group ON stats_agg_logo_by_group(month,day,group_id);
964 create table stats_subd_pages (
965 month INT NOT NULL DEFAULT 0,
966 day INT NOT NULL DEFAULT 0,
967 group_id INT NOT NULL DEFAULT 0,
968 pages INT NOT NULL DEFAULT 0
970 INSERT INTO stats_subd_pages
971 SELECT month,day,group_id,subdomain_views
972 FROM stats_project WHERE subdomain_views > 0;
974 CREATE UNIQUE INDEX statssubdpages_month_day_group ON stats_subd_pages(month,day,group_id);
977 create table stats_cvs_user (
978 month INT NOT NULL DEFAULT 0,
979 day INT NOT NULL DEFAULT 0,
980 group_id INT NOT NULL DEFAULT 0,
981 user_id INT NOT NULL DEFAULT 0,
982 checkouts INT NOT NULL DEFAULT 0,
983 commits INT NOT NULL DEFAULT 0,
984 adds INT NOT NULL DEFAULT 0
986 create table stats_cvs_group (
987 month INT NOT NULL DEFAULT 0,
988 day INT NOT NULL DEFAULT 0,
989 group_id INT NOT NULL DEFAULT 0,
990 checkouts INT NOT NULL DEFAULT 0,
991 commits INT NOT NULL DEFAULT 0,
992 adds INT NOT NULL DEFAULT 0
994 INSERT INTO stats_cvs_group
995 SELECT month,day,group_id,cvs_checkouts,cvs_commits,cvs_adds
997 WHERE cvs_checkouts > 0
1001 CREATE UNIQUE INDEX statscvsgroup_month_day_group ON stats_cvs_group(month,day,group_id);
1002 DROP INDEX archive_project_day;
1003 DROP INDEX archive_project_month;
1004 DROP INDEX archive_project_monthday;
1005 DROP INDEX archive_project_week;
1006 DROP INDEX project_log_group;
1007 create table stats_project_developers (
1008 month INT NOT NULL DEFAULT 0,
1009 day INT NOT NULL DEFAULT 0,
1010 group_id INT NOT NULL DEFAULT 0,
1011 developers INT NOT NULL DEFAULT 0
1013 -- COPY stats_project_developers from '/tmp/stats_project_developers';
1014 CREATE UNIQUE INDEX statsprojectdev_month_day_group ON stats_project_developers(month,day,group_id);
1015 DROP TABLE stats_project;
1017 create table stats_project (
1018 month INT NOT NULL DEFAULT 0,
1019 day INT NOT NULL DEFAULT 0,
1020 group_id INT NOT NULL DEFAULT 0,
1021 file_releases INT DEFAULT 0,
1022 msg_posted INT DEFAULT 0,
1023 msg_uniq_auth INT DEFAULT 0,
1024 bugs_opened INT DEFAULT 0,
1025 bugs_closed INT DEFAULT 0,
1026 support_opened INT DEFAULT 0,
1027 support_closed INT DEFAULT 0,
1028 patches_opened INT DEFAULT 0,
1029 patches_closed INT DEFAULT 0,
1030 artifacts_opened INT DEFAULT 0,
1031 artifacts_closed INT DEFAULT 0,
1032 tasks_opened INT DEFAULT 0,
1033 tasks_closed INT DEFAULT 0,
1034 help_requests INT DEFAULT 0
1036 -- copy stats_project from '/tmp/stats_project.dump';
1037 CREATE UNIQUE INDEX statsproject_month_day_group ON stats_project(month,day,group_id);
1038 CREATE TABLE stats_site_tmp AS
1039 SELECT month,day,uniq_users,sessions,total_users,new_users,new_projects
1042 DROP TABLE stats_site;
1043 ALTER TABLE stats_site_tmp RENAME TO stats_site;
1045 CREATE UNIQUE INDEX statssite_month_day on stats_site(month,day);
1047 -- Re-enable this once we are sure the "stats" account exists
1049 -- GRANT ALL ON stats_cvs_group TO stats;
1050 -- GRANT ALL ON stats_project TO stats;
1051 -- GRANT ALL ON stats_subd_pages TO stats;
1054 ALTER TABLE users ADD COLUMN block_ratings int ;
1055 ALTER TABLE users ALTER COLUMN block_ratings SET DEFAULT 0;
1058 INSERT INTO frs_filetype VALUES (100,'None');
1059 INSERT INTO frs_processor VALUES (100,'None');
1061 DELETE FROM frs_file
1065 WHERE frs_file.release_id=frs_release.release_id
1073 WHERE frs_file.type_id=frs_filetype.type_id
1078 SET processor_id=100
1082 WHERE frs_file.processor_id=frs_processor.processor_id
1086 ALTER TABLE frs_file ADD CONSTRAINT frsfile_processorid_fk
1087 FOREIGN KEY (processor_id) REFERENCES frs_processor(processor_id) MATCH FULL;
1088 ALTER TABLE frs_file ADD CONSTRAINT frsfile_releaseid_fk
1089 FOREIGN KEY (release_id) REFERENCES frs_release(release_id) MATCH FULL;
1090 ALTER TABLE frs_file ADD CONSTRAINT frsfile_typeid_fk
1091 FOREIGN KEY (type_id) REFERENCES frs_filetype(type_id) MATCH FULL;
1093 ALTER TABLE frs_package ADD CONSTRAINT frspackage_statusid_fk
1094 FOREIGN KEY (status_id) REFERENCES frs_status(status_id) MATCH FULL;
1095 ALTER TABLE frs_package ADD CONSTRAINT frspackage_groupid_fk
1096 FOREIGN KEY (group_id) REFERENCES groups(group_id) MATCH FULL ;
1098 ALTER TABLE frs_release ADD CONSTRAINT frsrelease_packageid_fk
1099 FOREIGN KEY (package_id) REFERENCES frs_package(package_id) MATCH FULL;
1100 ALTER TABLE frs_release ADD CONSTRAINT frsrelease_releasedby_fk
1101 FOREIGN KEY (released_by) REFERENCES users(user_id) MATCH FULL;
1102 ALTER TABLE frs_release ADD CONSTRAINT frsrelease_statusid_fk
1103 FOREIGN KEY (status_id) REFERENCES frs_status(status_id) MATCH FULL;
1105 ALTER TABLE artifact_group_list ADD COLUMN status_timeout integer;
1106 UPDATE artifact_group_list SET status_timeout='1209600' WHERE status_timeout is NULL;
1108 INSERT INTO artifact_status VALUES('4','Pending');
1111 CREATE TABLE user_metric_history(
1114 user_id int not null,
1115 ranking int not null,
1116 metric float not null);
1118 ---- From now on, everything comes from Debian-SF
1120 -- Get rid of another dead column
1121 ALTER TABLE user_preferences RENAME TO old_user_preferences ;
1122 DROP INDEX user_pref_user_id ;
1124 CREATE TABLE "user_preferences" (
1125 "user_id" integer DEFAULT '0' NOT NULL,
1126 "preference_name" character varying(20),
1127 "preference_value" text,
1128 "set_date" integer DEFAULT '0' NOT NULL
1131 INSERT INTO user_preferences
1132 SELECT user_id, preference_name, preference_value, set_date
1133 FROM old_user_preferences ;
1135 DROP TABLE old_user_preferences ;
1137 CREATE INDEX "user_pref_user_id" on "user_preferences" using btree ( "user_id" "int4_ops" );
1139 -- Fix some hostnames
1140 UPDATE groups SET unix_box = 'shell', cvs_box = 'cvs' ;
1141 UPDATE users SET unix_box = 'shell' ;
1143 -- Drop a few indexes
1144 DROP INDEX frs_dlstats_group_agg_day ;
1145 DROP INDEX frs_file_name ;
1146 DROP INDEX frs_file_processor ;
1147 DROP INDEX frs_file_release_id ;
1148 DROP INDEX frs_file_type ;
1149 DROP INDEX frs_release_by ;
1150 DROP INDEX frs_release_date ;
1151 DROP INDEX frs_release_package ;
1152 DROP INDEX frsdlstatsgroupagg_day_dls ;
1153 DROP INDEX ftpdl_day ;
1154 DROP INDEX group_id_idx ;
1155 DROP INDEX httpdl_day ;
1156 DROP INDEX idx_users_username ;
1157 DROP INDEX stats_agr_tmp_fid ;
1158 DROP INDEX stats_agr_tmp_gid ;
1160 -- Add a few missing tables
1161 CREATE TABLE "cache_store" (
1162 "name" character varying(255) NOT NULL,
1164 "indate" integer DEFAULT 0 NOT NULL,
1165 Constraint "cache_store_pkey" Primary Key ("name")
1168 CREATE TABLE "foundry_project_downloads_agg" (
1169 "foundry_id" integer,
1170 "downloads" integer,
1172 "group_name" character varying(40),
1173 "unix_group_name" character varying(30)
1176 CREATE TABLE "foundry_project_rankings_agg" (
1177 "foundry_id" integer,
1179 "group_name" character varying(40),
1180 "unix_group_name" character varying(30),
1182 "percentile" double precision
1185 CREATE TABLE "stats_project_all" (
1187 "developers" integer,
1188 "group_ranking" integer,
1189 "group_metric" double precision,
1190 "logo_showings" integer,
1191 "downloads" integer,
1192 "site_views" integer,
1193 "subdomain_views" integer,
1194 "page_views" integer,
1195 "msg_posted" integer,
1196 "msg_uniq_auth" integer,
1197 "bugs_opened" integer,
1198 "bugs_closed" integer,
1199 "support_opened" integer,
1200 "support_closed" integer,
1201 "patches_opened" integer,
1202 "patches_closed" integer,
1203 "artifacts_opened" integer,
1204 "artifacts_closed" integer,
1205 "tasks_opened" integer,
1206 "tasks_closed" integer,
1207 "help_requests" integer,
1208 "cvs_checkouts" integer,
1209 "cvs_commits" integer,
1213 CREATE TABLE "stats_project_developers_last30" (
1217 "developers" integer
1220 CREATE TABLE "stats_project_last_30" (
1224 "developers" integer,
1225 "group_ranking" integer,
1226 "group_metric" double precision,
1227 "logo_showings" integer,
1228 "downloads" integer,
1229 "site_views" integer,
1230 "subdomain_views" integer,
1231 "page_views" integer,
1232 "filereleases" integer,
1233 "msg_posted" integer,
1234 "msg_uniq_auth" integer,
1235 "bugs_opened" integer,
1236 "bugs_closed" integer,
1237 "support_opened" integer,
1238 "support_closed" integer,
1239 "patches_opened" integer,
1240 "patches_closed" integer,
1241 "artifacts_opened" integer,
1242 "artifacts_closed" integer,
1243 "tasks_opened" integer,
1244 "tasks_closed" integer,
1245 "help_requests" integer,
1246 "cvs_checkouts" integer,
1247 "cvs_commits" integer,
1251 CREATE TABLE "stats_project_months" (
1254 "developers" integer,
1255 "group_ranking" integer,
1256 "group_metric" double precision,
1257 "logo_showings" integer,
1258 "downloads" integer,
1259 "site_views" integer,
1260 "subdomain_views" integer,
1261 "page_views" integer,
1262 "file_releases" integer,
1263 "msg_posted" integer,
1264 "msg_uniq_auth" integer,
1265 "bugs_opened" integer,
1266 "bugs_closed" integer,
1267 "support_opened" integer,
1268 "support_closed" integer,
1269 "patches_opened" integer,
1270 "patches_closed" integer,
1271 "artifacts_opened" integer,
1272 "artifacts_closed" integer,
1273 "tasks_opened" integer,
1274 "tasks_closed" integer,
1275 "help_requests" integer,
1276 "cvs_checkouts" integer,
1277 "cvs_commits" integer,
1281 CREATE TABLE "stats_site_all" (
1282 "site_page_views" integer,
1283 "downloads" integer,
1284 "subdomain_views" integer,
1285 "msg_posted" integer,
1286 "bugs_opened" integer,
1287 "bugs_closed" integer,
1288 "support_opened" integer,
1289 "support_closed" integer,
1290 "patches_opened" integer,
1291 "patches_closed" integer,
1292 "artifacts_opened" integer,
1293 "artifacts_closed" integer,
1294 "tasks_opened" integer,
1295 "tasks_closed" integer,
1296 "help_requests" integer,
1297 "cvs_checkouts" integer,
1298 "cvs_commits" integer,
1302 CREATE TABLE "stats_site_last_30" (
1305 "site_page_views" integer,
1306 "downloads" integer,
1307 "subdomain_views" integer,
1308 "msg_posted" integer,
1309 "bugs_opened" integer,
1310 "bugs_closed" integer,
1311 "support_opened" integer,
1312 "support_closed" integer,
1313 "patches_opened" integer,
1314 "patches_closed" integer,
1315 "artifacts_opened" integer,
1316 "artifacts_closed" integer,
1317 "tasks_opened" integer,
1318 "tasks_closed" integer,
1319 "help_requests" integer,
1320 "cvs_checkouts" integer,
1321 "cvs_commits" integer,
1325 CREATE TABLE "stats_site_months" (
1327 "site_page_views" integer,
1328 "downloads" integer,
1329 "subdomain_views" integer,
1330 "msg_posted" integer,
1331 "bugs_opened" integer,
1332 "bugs_closed" integer,
1333 "support_opened" integer,
1334 "support_closed" integer,
1335 "patches_opened" integer,
1336 "patches_closed" integer,
1337 "artifacts_opened" integer,
1338 "artifacts_closed" integer,
1339 "tasks_opened" integer,
1340 "tasks_closed" integer,
1341 "help_requests" integer,
1342 "cvs_checkouts" integer,
1343 "cvs_commits" integer,
1347 CREATE TABLE "stats_site_pages_by_day" (
1350 "site_page_views" integer
1353 CREATE TABLE "stats_site_pages_by_month" (
1355 "site_page_views" integer
1358 -- Add/alter a few columns
1359 ALTER TABLE frs_dlstats_group_agg ADD COLUMN month integer;
1360 ALTER TABLE frs_dlstats_group_agg ALTER COLUMN month SET DEFAULT '0';
1361 ALTER TABLE project_weekly_metric ALTER COLUMN group_id SET DEFAULT '0';
1363 -- Drop an unused table
1364 DROP TABLE intel_agreement ;
1366 -- (Re-)create indexes
1367 CREATE INDEX frs_file_release_id ON frs_file USING btree (release_id);
1368 CREATE INDEX frs_release_package ON frs_release USING btree (package_id);
1369 CREATE INDEX frsdlfiletotal_fileid ON frs_dlstats_filetotal_agg USING btree (file_id);
1370 CREATE INDEX frsdlgroup_groupid ON frs_dlstats_group_agg USING btree (group_id);
1371 CREATE INDEX frsdlgroup_month_day_groupid ON frs_dlstats_group_agg USING btree ("month", "day", group_id);
1372 CREATE INDEX frsdlgrouptotal_groupid ON frs_dlstats_grouptotal_agg USING btree (group_id);
1373 -- CREATE INDEX project_metric_group ON project_metric USING btree (group_id);
1374 -- CREATE INDEX project_metric_weekly_group ON project_weekly_metric USING btree (group_id);
1375 -- CREATE INDEX projectweeklymetric_ranking ON project_weekly_metric USING btree (ranking);
1376 CREATE INDEX statsproject30_groupid ON stats_project_last_30 USING btree (group_id);
1377 CREATE INDEX statsprojectall_groupid ON stats_project_all USING btree (group_id);
1378 CREATE INDEX statsprojectmonths_groupid ON stats_project_months USING btree (group_id);
1379 CREATE INDEX statsprojectmonths_groupid_mont ON stats_project_months USING btree (group_id, "month");
1380 CREATE INDEX statssitelast30_month_day ON stats_site_last_30 USING btree ("month", "day");
1381 CREATE INDEX statssitemonths_month ON stats_site_months USING btree ("month");
1382 CREATE INDEX statssitepagesbyday_month_day ON stats_site_pages_by_day USING btree ("month", "day");
1383 CREATE INDEX troveagg_trovecatid_ranking ON trove_agg USING btree (trove_cat_id, ranking);
1384 CREATE INDEX user_metric_history_date_userid ON user_metric_history USING btree ("month", "day", user_id);
1386 CREATE INDEX "foundryprojdlsagg_foundryid_dls" on "foundry_project_downloads_agg" using btree ( "foundry_id" "int4_ops", "downloads" "int4_ops" );
1387 CREATE INDEX "foundryprojectrankingsagg_found" on "foundry_project_rankings_agg" using btree ( "foundry_id" "int4_ops", "ranking" "int4_ops" );
1389 CREATE UNIQUE INDEX frsdlfileagg_oid ON frs_dlstats_file_agg USING btree (oid);
1390 CREATE UNIQUE INDEX statsagglogobygrp_oid ON stats_agg_logo_by_group USING btree (oid);
1391 CREATE UNIQUE INDEX statsaggsitebygrp_oid ON stats_agg_site_by_group USING btree (oid);
1392 CREATE UNIQUE INDEX statscvsgrp_oid ON stats_cvs_group USING btree (oid);
1393 CREATE UNIQUE INDEX statsproject_oid ON stats_project USING btree (oid);
1394 CREATE UNIQUE INDEX statsprojectdevelop_oid ON stats_project_developers USING btree (oid);
1395 CREATE UNIQUE INDEX statsprojectmetric_oid ON stats_project_metric USING btree (oid);
1396 CREATE UNIQUE INDEX statssite_oid ON stats_site USING btree (oid);
1397 CREATE UNIQUE INDEX statssitepgsbyday_oid ON stats_site_pages_by_day USING btree (oid);
1398 CREATE UNIQUE INDEX statssubdpages_oid ON stats_subd_pages USING btree (oid);
1400 -- Add two new themes
1401 INSERT INTO themes (dirname, fullname) VALUES ('debian', 'Debian') ;
1402 INSERT INTO themes (dirname, fullname) VALUES ('savannah', 'Savannah') ;
1405 ALTER TABLE project_group_list ADD CONSTRAINT project_group_list_group_id_fk
1406 FOREIGN KEY (group_id) REFERENCES groups(group_id) MATCH FULL ;
1407 ALTER TABLE user_group ADD CONSTRAINT user_group_group_id_fk
1408 FOREIGN KEY (group_id) REFERENCES groups(group_id) MATCH FULL ;