1 files changed, 139 insertions, 0 deletions
diff --git a/admin/upgrades/roles_upgrade.sql b/admin/upgrades/roles_upgrade.sql
new file mode 100755
index 0000000..d53d397
--- /dev/null
+++ b/admin/upgrades/roles_upgrade.sql
@@ -0,0 +1,139 @@
+CREATE GENERATOR USERS_ROLES_ID_SEQ;
+SET GENERATOR USERS_ROLES_ID_SEQ TO 3;
+DROP GENERATOR USERS_GROUPS_ID_SEQ;
+
+CREATE TABLE LIBERTY_CONTENT_ROLE_MAP
+(
+  CONTENT_ID Integer NOT NULL,
+  ROLE_ID Integer NOT NULL,
+  PRIMARY KEY (CONTENT_ID,ROLE_ID)
+);
+
+COMMIT;
+
+INSERT INTO LIBERTY_CONTENT_ROLE_MAP ( CONTENT_ID, ROLE_ID )
+SELECT lg.CONTENT_ID, lg.GROUP_ID AS ROLE_ID FROM LIBERTY_CONTENT_GROUP_MAP lg;
+
+DROP TABLE LIBERTY_CONTENT_GROUP_MAP;
+
+ALTER TABLE LIBERTY_CONTENT_PERMISSIONS DROP CONSTRAINT LIBERTY_CONTENT_PERM_GROUP_REF;
+ALTER TABLE LIBERTY_CONTENT_PERMISSIONS DROP CONSTRAINT INTEG_83;
+ALTER TABLE LIBERTY_CONTENT_PERMISSIONS ALTER GROUP_ID TO ROLE_ID;
+ALTER TABLE LIBERTY_CONTENT_PERMISSIONS
+ADD CONSTRAINT PK_LIBERTY_CONTENT_PERMISSIONS
+PRIMARY KEY (ROLE_ID, PERM_NAME, CONTENT_ID);
+
+ALTER TABLE THEMES_LAYOUTS ALTER GROUPS TO ROLES;
+
+CREATE TABLE USERS_ROLES
+(
+  ROLE_ID Integer NOT NULL,
+  USER_ID Integer NOT NULL,
+  ROLE_NAME Varchar(30),
+  IS_DEFAULT Varchar(1),
+  ROLE_DESC Varchar(255),
+  ROLE_HOME Varchar(255),
+  IS_PUBLIC Varchar(1),
+  AFTER_REGISTRATION_PAGE Varchar(255),
+  PRIMARY KEY (ROLE_ID)
+);
+
+CREATE TABLE USERS_ROLES_MAP
+(
+  USER_ID Integer NOT NULL,
+  ROLE_ID Integer NOT NULL,
+  PRIMARY KEY (USER_ID,ROLE_ID)
+);
+
+CREATE TABLE USERS_ROLE_PERMISSIONS
+(
+  ROLE_ID Integer NOT NULL,
+  PERM_NAME Varchar(30) NOT NULL,
+  PERM_VALUE Varchar(1) DEFAULT '',
+  PRIMARY KEY (ROLE_ID,PERM_NAME)
+);
+
+ALTER TABLE USERS_USERS ALTER DEFAULT_GROUP_ID TO DEFAULT_ROLE_ID;
+
+ALTER TABLE LIBERTY_CONTENT_ROLE_MAP ADD CONSTRAINT PROTECTOR_CONTENT_REF
+  FOREIGN KEY (CONTENT_ID) REFERENCES LIBERTY_CONTENT (CONTENT_ID);
+ALTER TABLE LIBERTY_CONTENT_ROLE_MAP ADD CONSTRAINT PROTECTOR_ROLE_REF
+  FOREIGN KEY (ROLE_ID) REFERENCES USERS_ROLES (ROLE_ID);
+  
+CREATE INDEX LIBERTY_CONTENT_PERM_ROLE_IDX ON LIBERTY_CONTENT_PERMISSIONS (ROLE_ID);
+
+ALTER TABLE LIBERTY_CONTENT_PERMISSIONS ADD CONSTRAINT LIBERTY_CONTENT_PERM_ROLE_REF
+  FOREIGN KEY (ROLE_ID) REFERENCES USERS_ROLES (ROLE_ID);
+
+ALTER TABLE USERS_ROLES ADD CONSTRAINT USERS_ROLES_USER_REF
+  FOREIGN KEY (USER_ID) REFERENCES USERS_USERS (USER_ID);
+CREATE INDEX USERS_ROLES_USER_IDX ON USERS_ROLES (USER_ID);
+CREATE UNIQUE INDEX USERS_ROLES_USER_NAME_IDX ON USERS_ROLES (USER_ID,ROLE_NAME);
+ALTER TABLE USERS_ROLES_MAP ADD CONSTRAINT USERS_ROLES_MAP_ROLE_REF
+  FOREIGN KEY (ROLE_ID) REFERENCES USERS_ROLES (ROLE_ID);
+ALTER TABLE USERS_ROLES_MAP ADD CONSTRAINT USERS_ROLES_MAP_USER_REF
+  FOREIGN KEY (USER_ID) REFERENCES USERS_USERS (USER_ID);
+CREATE INDEX USERS_ROLES_MAP_ROLE_IDX ON USERS_ROLES_MAP (ROLE_ID);
+CREATE INDEX USERS_ROLES_MAP_USER_IDX ON USERS_ROLES_MAP (USER_ID);
+ALTER TABLE USERS_ROLE_PERMISSIONS ADD CONSTRAINT USERS_ROLE_PERM_PERM_REF
+  FOREIGN KEY (PERM_NAME) REFERENCES USERS_PERMISSIONS (PERM_NAME);
+ALTER TABLE USERS_ROLE_PERMISSIONS ADD CONSTRAINT USERS_ROLE_PERM_ROLE_REF
+  FOREIGN KEY (ROLE_ID) REFERENCES USERS_ROLES (ROLE_ID);
+CREATE INDEX USERS_ROLE_PERM_PERM_IDX ON USERS_ROLE_PERMISSIONS (PERM_NAME);
+CREATE INDEX USERS_ROLE_PERM_ROLE_IDX ON USERS_ROLE_PERMISSIONS (ROLE_ID);
+
+GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
+ ON LIBERTY_CONTENT_ROLE_MAP TO  SYSDBA WITH GRANT OPTION;
+ 
+GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
+ ON USERS_PERMISSIONS TO  SYSDBA WITH GRANT OPTION;
+
+GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
+ ON USERS_ROLES TO  SYSDBA WITH GRANT OPTION;
+
+GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
+ ON USERS_ROLES_MAP TO  SYSDBA WITH GRANT OPTION;
+
+GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
+ ON USERS_ROLE_PERMISSIONS TO  SYSDBA WITH GRANT OPTION;
+
+COMMIT;
+
+INSERT INTO USERS_ROLES
+SELECT * FROM USERS_GROUPS;
+
+INSERT INTO USERS_ROLES_MAP
+SELECT * FROM USERS_GROUPS_MAP;
+
+INSERT INTO USERS_ROLE_PERMISSIONS
+SELECT * FROM USERS_GROUP_PERMISSIONS;
+
+DROP TABLE USERS_GROUP_PERMISSIONS;
+DROP TABLE USERS_GROUPS_MAP;
+DROP TABLE USERS_GROUPS;
+DROP INDEX LIBERTY_CONTENT_PERM_GROUP_IDX;
+
+INSERT INTO USERS_PERMISSIONS (PERM_NAME, PERM_DESC, PERM_LEVEL, PACKAGE) VALUES ('p_users_assign_role_perms', 'Can assign permissions to personal roles', 'editors', 'users');
+INSERT INTO USERS_PERMISSIONS (PERM_NAME, PERM_DESC, PERM_LEVEL, PACKAGE) VALUES ('p_users_assign_role_members', 'Can assign users to personal roles', 'registered', 'users');
+INSERT INTO USERS_PERMISSIONS (PERM_NAME, PERM_DESC, PERM_LEVEL, PACKAGE) VALUES ('p_users_role_subroles', 'Can include other roles in roles', 'editors', 'users');
+INSERT INTO USERS_PERMISSIONS (PERM_NAME, PERM_DESC, PERM_LEVEL, PACKAGE) VALUES ('p_users_create_personal_roles', 'Can create personal user roles', 'editors', 'users');
+
+UPDATE USERS_ROLE_PERMISSIONS SET PERM_NAME = 'p_users_assign_role_members' WHERE PERM_NAME = 'p_users_assign_group_members';
+UPDATE USERS_ROLE_PERMISSIONS SET PERM_NAME = 'p_users_assign_role_perms' WHERE PERM_NAME = 'p_users_assign_group_perms';
+UPDATE USERS_ROLE_PERMISSIONS SET PERM_NAME = 'p_users_role_subroles' WHERE PERM_NAME = 'p_users_group_subgroups';
+UPDATE USERS_ROLE_PERMISSIONS SET PERM_NAME = 'p_users_create_personal_roles' WHERE PERM_NAME = 'p_users_create_personal_groups';
+UPDATE LIBERTY_CONTENT_PERMISSIONS SET PERM_NAME = 'p_users_assign_role_members' WHERE PERM_NAME = 'p_users_assign_group_members';
+UPDATE LIBERTY_CONTENT_PERMISSIONS SET PERM_NAME = 'p_users_assign_role_perms' WHERE PERM_NAME = 'p_users_assign_group_perms';
+UPDATE LIBERTY_CONTENT_PERMISSIONS SET PERM_NAME = 'p_users_role_subroles' WHERE PERM_NAME = 'p_users_group_subgroups';
+UPDATE LIBERTY_CONTENT_PERMISSIONS SET PERM_NAME = 'p_users_create_personal_roles' WHERE PERM_NAME = 'p_users_create_personal_groups';
+
+DELETE FROM USERS_PERMISSIONS WHERE PERM_NAME = 'p_users_assign_group_members';
+DELETE FROM USERS_PERMISSIONS WHERE PERM_NAME = 'p_users_assign_group_perms';
+DELETE FROM USERS_PERMISSIONS WHERE PERM_NAME = 'p_users_group_subgroups';
+DELETE FROM USERS_PERMISSIONS WHERE PERM_NAME = 'p_users_create_personal_groups';
+
+UPDATE KERNEL_CONFIG SET 
+CONFIG_NAME = 'protector_single_role', 
+PACKAGE = 'protector', 
+CONFIG_VALUE = 'y'
+WHERE CONFIG_NAME = 'protector_single_group';
+\ No newline at end of file