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