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;