1 CREATE TABLE user_type (
5 INSERT into user_type (type_name) VALUES ('User');
6 INSERT into user_type (type_name) VALUES ('UserPool');
8 ALTER TABLE users ADD COLUMN type_id INT;
9 ALTER TABLE users ALTER COLUMN type_id SET DEFAULT 1;
10 UPDATE users SET type_id=1;
11 ALTER TABLE users ADD CONSTRAINT users_typeid
12 FOREIGN KEY (type_id) REFERENCES user_type(type_id) MATCH FULL;
15 -- Each FRS Package now has public/private flags
17 ALTER TABLE frs_package ADD COLUMN is_public INT;
18 ALTER TABLE frs_package ALTER COLUMN is_public SET DEFAULT 1;
19 UPDATE frs_package SET is_public=1;
22 role_id serial unique,
23 group_id int not null REFERENCES groups(group_id) ON DELETE CASCADE,
26 CREATE UNIQUE INDEX role_groupidroleid ON role(group_id,role_id);
28 INSERT INTO role (group_id,role_name) VALUES (1,'Default');
30 --DROP TABLE role_section;
31 --DROP SEQUENCE role_section_section_id_seq;
32 --DROP TABLE role_value;
33 --DROP VIEW role_section_value_vw;
37 -- This new table will store separate perms for each task manager subproject
40 CREATE TABLE project_perm (
42 group_project_id int not null REFERENCES project_group_list(group_project_id) ON DELETE CASCADE,
43 user_id int not null REFERENCES users(user_id) MATCH FULL,
44 perm_level int not null default 0
46 CREATE UNIQUE INDEX projectperm_groupprojiduserid ON project_perm(group_project_id,user_id);
48 DELETE FROM project_perm;
49 INSERT INTO project_perm (group_project_id,user_id,perm_level)
50 SELECT project_group_list.group_project_id,user_group.user_id,user_group.project_flags
51 FROM user_group,project_group_list
52 WHERE project_group_list.group_id=user_group.group_id
53 AND NOT EXISTS (SELECT user_id FROM project_perm WHERE project_perm.group_project_id=
54 project_group_list.group_project_id);
58 -- This new table will store separate perms for each forum
61 CREATE TABLE forum_perm (
63 group_forum_id int not null REFERENCES forum_group_list(group_forum_id) ON DELETE CASCADE,
64 user_id int not null REFERENCES users(user_id) MATCH FULL,
65 perm_level int not null default 0
67 CREATE UNIQUE INDEX forumperm_groupforumiduserid ON forum_perm(group_forum_id,user_id);
69 DELETE FROM forum_perm;
70 INSERT INTO forum_perm (group_forum_id,user_id,perm_level)
71 SELECT forum_group_list.group_forum_id,user_group.user_id,user_group.forum_flags
72 FROM user_group,forum_group_list
73 WHERE forum_group_list.group_id=user_group.group_id
74 AND NOT EXISTS (SELECT user_id FROM forum_perm WHERE forum_perm.group_forum_id=
75 forum_group_list.group_forum_id);
79 -- Add to all trackers
81 update user_group set artifact_flags=0 where artifact_flags is null;
82 INSERT INTO artifact_perm (group_artifact_id,user_id,perm_level)
83 SELECT artifact_group_list.group_artifact_id,user_group.user_id,user_group.artifact_flags
84 FROM user_group,artifact_group_list
85 WHERE artifact_group_list.group_id=user_group.group_id
86 AND NOT EXISTS (SELECT user_id FROM artifact_perm WHERE artifact_perm.group_artifact_id=
87 artifact_group_list.group_artifact_id);
90 -- This table contains all the settings for this particular role
92 -- example; 1,'docman',$category_id,1
94 CREATE TABLE role_setting (
95 role_id int not null REFERENCES role(role_id) ON DELETE CASCADE,
96 section_name text not null,
97 ref_id int not null, --optional ID for something like artifact_type_id or doc_category_id
98 value varchar(2) not null
100 CREATE INDEX rolesetting_roleidsectionid ON role_setting(role_id,section_name);
102 ALTER TABLE user_group ADD COLUMN role_id INT;
103 ALTER TABLE user_group ALTER COLUMN role_id SET DEFAULT 1;
104 UPDATE user_group SET role_id='1';
105 ALTER TABLE user_group ADD CONSTRAINT usergroup_roleid
106 FOREIGN KEY (role_id) REFERENCES role(role_id) MATCH FULL;