1 DROP VIEW IF EXISTS users_with_cvsflags_vw;
2 DROP VIEW IF EXISTS groups_with_svn_vw;
4 DROP SEQUENCE user_group_pk_seq;
5 DROP TABLE role_setting ;
8 DROP VIEW artifact_group_list_vw;
9 CREATE VIEW artifact_group_list_vw AS
10 SELECT agl.group_artifact_id, agl.group_id, agl.name, agl.description,
11 agl.email_all_updates, agl.email_address,
12 agl.due_period, agl.submit_instructions, agl.browse_instructions,
13 agl.browse_list, agl.datatype, agl.status_timeout, agl.custom_status_field,
14 agl.custom_renderer, aca.count, aca.open_count
15 FROM artifact_group_list agl
16 LEFT JOIN artifact_counts_agg aca USING (group_artifact_id);
18 CREATE OR REPLACE FUNCTION tmp_migrate_tracker_allow_anon_to_rbac () RETURNS integer AS $$
20 agl artifact_group_list%ROWTYPE ;
23 UPDATE pfo_role_setting SET perm_val = perm_val|8 WHERE perm_val > 0 AND role_id != 1 AND (section_name = 'tracker' OR section_name = 'new_tracker');
24 FOR agl IN SELECT * FROM artifact_group_list WHERE allow_anon = 1
26 tid = agl.group_artifact_id ;
27 UPDATE pfo_role_setting SET perm_val = perm_val|8 WHERE perm_val > 0 AND role_id = 1 AND section_name = 'tracker' AND ref_id = tid;
33 SELECT tmp_migrate_tracker_allow_anon_to_rbac ();
35 DROP FUNCTION tmp_migrate_tracker_allow_anon_to_rbac () ;
37 ALTER TABLE artifact_group_list DROP COLUMN is_public;
38 ALTER TABLE artifact_group_list DROP COLUMN allow_anon;
40 DROP VIEW forum_group_list_vw;
41 CREATE VIEW forum_group_list_vw AS
42 SELECT forum_group_list.group_forum_id, forum_group_list.group_id, forum_group_list.forum_name, forum_group_list.description, forum_group_list.send_all_posts_to, forum_agg_msg_count.count AS total, (SELECT max(forum.post_date) AS recent FROM forum WHERE (forum.group_forum_id = forum_group_list.group_forum_id)) AS recent, (SELECT count(*) AS count FROM (SELECT forum.thread_id FROM forum WHERE (forum.group_forum_id = forum_group_list.group_forum_id) GROUP BY forum.thread_id) tmp) AS threads FROM (forum_group_list LEFT JOIN forum_agg_msg_count USING (group_forum_id));
44 DROP TRIGGER IF EXISTS fmsg_agg_trig ON forum;
45 DROP TRIGGER IF EXISTS fora_agg_trig ON forum_group_list;
47 CREATE OR REPLACE FUNCTION project_sums () RETURNS TRIGGER AS $$
54 --- Get number of things this group has now
56 IF TG_ARGV[0]='surv' THEN
57 IF TG_OP='DELETE' THEN
58 SELECT INTO num count(*) FROM surveys WHERE OLD.group_id=group_id AND is_active=1;
59 curr_group := OLD.group_id;
61 SELECT INTO num count(*) FROM surveys WHERE NEW.group_id=group_id AND is_active=1;
62 curr_group := NEW.group_id;
65 IF TG_ARGV[0]='mail' THEN
66 IF TG_OP='DELETE' THEN
67 SELECT INTO num count(*) FROM mail_group_list WHERE OLD.group_id=group_id AND is_public=1;
68 curr_group := OLD.group_id;
70 SELECT INTO num count(*) FROM mail_group_list WHERE NEW.group_id=group_id AND is_public=1;
71 curr_group := NEW.group_id;
75 --- See if this group already has a row in project_sums_agg for these things
77 SELECT INTO found count(group_id) FROM project_sums_agg WHERE curr_group=group_id AND type=TG_ARGV[0];
81 --- Create row for this group
83 INSERT INTO project_sums_agg
84 VALUES (curr_group, TG_ARGV[0], num);
89 UPDATE project_sums_agg SET count=num
90 WHERE curr_group=group_id AND type=TG_ARGV[0];
93 IF TG_OP='DELETE' THEN
100 DELETE FROM project_sums_agg WHERE type='fora' OR type='fmsg';
102 ALTER TABLE forum_group_list DROP COLUMN is_public;
103 ALTER TABLE forum_group_list DROP COLUMN allow_anonymous;
104 ALTER TABLE forum_group_list DROP COLUMN moderation_level;
106 DROP VIEW project_group_list_vw;
107 ALTER TABLE project_group_list DROP COLUMN is_public;
108 CREATE VIEW project_group_list_vw AS SELECT * FROM project_group_list NATURAL JOIN project_counts_agg;
110 ALTER TABLE groups DROP COLUMN is_public;
111 ALTER TABLE groups DROP COLUMN enable_anonscm;
112 -- ALTER TABLE groups DROP COLUMN enable_pserver;
114 DROP SEQUENCE foundry_news_pk_seq;