Alter and simplify user-group related tables (#763)
Change-Id: Ia09a69e7f7a6e922283b6a113dcaaf4a7eac51f8
diff --git a/src/main/resources/db/predefined/V2.1__insert_predefined_roles.sql b/src/main/resources/db/predefined/V2.1__insert_predefined_roles.sql
index 0c307cb..37e5be3 100644
--- a/src/main/resources/db/predefined/V2.1__insert_predefined_roles.sql
+++ b/src/main/resources/db/predefined/V2.1__insert_predefined_roles.sql
@@ -1,28 +1,28 @@
--- roles
-INSERT INTO role(name) VALUES ("USER_GROUP_ADMIN");
-INSERT INTO role(name) VALUES ("USER_GROUP_MEMBER");
-INSERT INTO role(name) VALUES ("VC_ACCESS_ADMIN");
-INSERT INTO role(name) VALUES ("VC_ACCESS_MEMBER");
-INSERT INTO role(name) VALUES ("QUERY_ACCESS_ADMIN");
-INSERT INTO role(name) VALUES ("QUERY_ACCESS_MEMBER");
+---- roles
+--INSERT INTO role(name) VALUES ("USER_GROUP_ADMIN");
+--INSERT INTO role(name) VALUES ("USER_GROUP_MEMBER");
+--INSERT INTO role(name) VALUES ("VC_ACCESS_ADMIN");
+--INSERT INTO role(name) VALUES ("VC_ACCESS_MEMBER");
+--INSERT INTO role(name) VALUES ("QUERY_ACCESS_ADMIN");
+--INSERT INTO role(name) VALUES ("QUERY_ACCESS_MEMBER");
--- privileges
-INSERT INTO privilege(name,role_id)
- VALUES("READ", 1);
-INSERT INTO privilege(name,role_id)
- VALUES("WRITE", 1);
-INSERT INTO privilege(name,role_id)
- VALUES("DELETE", 1);
-
-INSERT INTO privilege(name,role_id)
- VALUES("DELETE",2);
-
-INSERT INTO privilege(name,role_id)
- VALUES("READ",3);
-INSERT INTO privilege(name,role_id)
- VALUES("WRITE",3);
-INSERT INTO privilege(name,role_id)
- VALUES("DELETE",3);
-
-INSERT INTO privilege(name,role_id)
- VALUES("READ",4);
\ No newline at end of file
+---- privileges
+--INSERT INTO privilege(name,role_id)
+-- VALUES("READ", 1);
+--INSERT INTO privilege(name,role_id)
+-- VALUES("WRITE", 1);
+--INSERT INTO privilege(name,role_id)
+-- VALUES("DELETE", 1);
+--
+--INSERT INTO privilege(name,role_id)
+-- VALUES("DELETE",2);
+--
+--INSERT INTO privilege(name,role_id)
+-- VALUES("READ",3);
+--INSERT INTO privilege(name,role_id)
+-- VALUES("WRITE",3);
+--INSERT INTO privilege(name,role_id)
+-- VALUES("DELETE",3);
+--
+--INSERT INTO privilege(name,role_id)
+-- VALUES("READ",4);
\ No newline at end of file
diff --git a/src/main/resources/db/sqlite/V1.13__user_group_alteration.sql b/src/main/resources/db/sqlite/V1.13__user_group_alteration.sql
new file mode 100644
index 0000000..d13dde0
--- /dev/null
+++ b/src/main/resources/db/sqlite/V1.13__user_group_alteration.sql
@@ -0,0 +1,85 @@
+--DROP INDEX IF EXISTS group_member_role_index;
+--DROP INDEX IF EXISTS user_group_member_index;
+DROP INDEX IF EXISTS user_group_member_status_index;
+DROP INDEX IF EXISTS role_index;
+
+-- please commented out the triggers in V1.2__triggers.sql later
+--DROP TRIGGER IF EXISTS insert_member_status;
+--DROP TRIGGER IF EXISTS update_member_status;
+--DROP TRIGGER IF EXISTS delete_member;
+
+--ALTER TABLE user_group
+--DROP COLUMN deleted_by;
+--
+ALTER TABLE user_group
+ADD COLUMN created_date TIMESTAMP;
+--
+--ALTER TABLE user_group_member
+--DROP COLUMN created_by;
+--
+--ALTER TABLE user_group_member
+--DROP COLUMN deleted_by;
+--
+--ALTER TABLE user_group_member
+--DROP COLUMN status;
+--
+--ALTER TABLE user_group_member
+--DROP COLUMN status_date;
+
+
+CREATE TABLE IF NOT EXISTS role_new (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ name VARCHAR(100) NOT NULL,
+ privilege VARCHAR(100) NOT NULL,
+ group_id INTEGER,
+ query_id INTEGER,
+ FOREIGN KEY (group_id)
+ REFERENCES user_group (id)
+ ON DELETE CASCADE
+ FOREIGN KEY (query_id)
+ REFERENCES query (id)
+ ON DELETE CASCADE
+);
+
+INSERT INTO role_new (name, privilege, group_id, query_id)
+ SELECT DISTINCT r.name || '_' || p.name, p.name, ug.id, qa.query_id
+ FROM user_group ug
+ JOIN query_access qa ON ug.id=qa.user_group_id
+ JOIN user_group_member ugm ON ugm.group_id = ug.id
+ JOIN group_member_role gmr ON gmr.group_member_id = ugm.id
+ JOIN role r ON gmr.role_id = r.id
+ JOIN privilege p ON p.role_id = r.id;
+
+--CREATE TABLE IF NOT EXISTS user_role (
+-- id INTEGER PRIMARY KEY AUTOINCREMENT,
+-- user_id VARCHAR(100) NOT NULL,
+-- status VARCHAR(100) NOT NULL,
+-- role_id INTEGER,
+-- FOREIGN KEY (role_id)
+-- REFERENCES role_new (id)
+-- ON DELETE CASCADE
+--);
+--
+--INSERT INTO user_role (user_id,status,role_id)
+--SELECT ugm.user_id, ugm.status, rn.id
+-- FROM query_access qa
+-- JOIN user_group_member ugm ON ugm.group_id = qa.user_group_id
+-- JOIN group_member_role gmr ON gmr.group_member_id = ugm.id
+-- JOIN role r ON gmr.role_id = r.id
+-- JOIN privilege p ON p.role_id = r.id
+-- JOIN role_new rn
+-- where r.name || '_' || p.name = rn.name
+-- and rn.group_id=qa.user_group_id;
+
+DROP INDEX IF EXISTS privilege_index;
+DROP INDEX IF EXISTS virtual_corpus_access_unique_index;
+DROP INDEX IF EXISTS virtual_corpus_status_index;
+
+DROP TABLE role;
+
+ALTER TABLE role_new RENAME TO role;
+
+DROP TABLE privilege;
+--DROP TABLE group_member_role;
+--DROP TABLE query_access;
+--DROP TABLE user_group_member;
\ No newline at end of file
diff --git a/src/main/resources/db/test/V3.1__insert_virtual_corpus.sql b/src/main/resources/db/test/V3.1__insert_virtual_corpus.sql
index d9d2c13..3673798 100644
--- a/src/main/resources/db/test/V3.1__insert_virtual_corpus.sql
+++ b/src/main/resources/db/test/V3.1__insert_virtual_corpus.sql
@@ -1,63 +1,63 @@
-- dummy data only for testing
-- user groups
-INSERT INTO user_group(name,status,created_by)
- VALUES ("marlin-group","ACTIVE","marlin");
+INSERT INTO user_group(name,status,created_by,created_date)
+ VALUES ("marlin-group","ACTIVE","marlin",CURRENT_TIMESTAMP);
-INSERT INTO user_group(name,status,created_by)
- VALUES ("dory-group","ACTIVE","dory");
+--INSERT INTO user_group(name,status,created_by,created_date)
+-- VALUES ("dory-group","ACTIVE","dory",CURRENT_TIMESTAMP);
-INSERT INTO user_group(name,status,created_by)
- VALUES ("auto-group","HIDDEN","system");
+INSERT INTO user_group(name,status,created_by,created_date)
+ VALUES ("auto-group","HIDDEN","system",CURRENT_TIMESTAMP);
--INSERT INTO user_group(name,status,created_by)
-- VALUES ("all users","HIDDEN","system");
-INSERT INTO user_group(name,status,created_by, deleted_by)
- VALUES ("deleted-group","DELETED","dory", "dory");
+--INSERT INTO user_group(name,status,created_by,deleted_by,created_date)
+-- VALUES ("deleted-group","DELETED","dory", "dory",CURRENT_TIMESTAMP);
-- user group members
-INSERT INTO user_group_member(user_id, group_id, status, created_by)
- SELECT "marlin",
- (SELECT id from user_group where name = "marlin-group"),
- "ACTIVE","marlin";
-
-INSERT INTO user_group_member(user_id, group_id, status, created_by)
- SELECT "dory",
- (SELECT id from user_group where name = "marlin-group"),
- "ACTIVE","marlin";
-
-INSERT INTO user_group_member(user_id, group_id, status, created_by)
- SELECT "dory",
- (SELECT id from user_group where name = "dory-group"),
- "ACTIVE","dory";
-
-INSERT INTO user_group_member(user_id, group_id, status, created_by)
- SELECT "nemo",
- (SELECT id from user_group where name = "dory-group"),
- "ACTIVE","dory";
-
-INSERT INTO user_group_member(user_id, group_id, status, created_by)
- SELECT "marlin",
- (SELECT id from user_group where name = "dory-group"),
- "PENDING","dory";
-
-INSERT INTO user_group_member(user_id, group_id, status, created_by, deleted_by)
- SELECT "pearl",
- (SELECT id from user_group where name = "dory-group"),
- "DELETED","dory", "pearl";
-
-INSERT INTO user_group_member(user_id, group_id, status, created_by)
- SELECT "pearl",
- (SELECT id from user_group where name = "auto-group"),
- "ACTIVE","system";
-
-INSERT INTO user_group_member(user_id, group_id, status, created_by)
- SELECT "dory",
- (SELECT id from user_group where name = "deleted-group"),
- "ACTIVE","dory";
+--INSERT INTO user_group_member(user_id, group_id, status, created_by)
+-- SELECT "marlin",
+-- (SELECT id from user_group where name = "marlin-group"),
+-- "ACTIVE","marlin";
+--
+--INSERT INTO user_group_member(user_id, group_id, status, created_by)
+-- SELECT "dory",
+-- (SELECT id from user_group where name = "marlin-group"),
+-- "ACTIVE","marlin";
+--
+--INSERT INTO user_group_member(user_id, group_id, status, created_by)
+-- SELECT "dory",
+-- (SELECT id from user_group where name = "dory-group"),
+-- "ACTIVE","dory";
+--
+--INSERT INTO user_group_member(user_id, group_id, status, created_by)
+-- SELECT "nemo",
+-- (SELECT id from user_group where name = "dory-group"),
+-- "ACTIVE","dory";
+--
+--INSERT INTO user_group_member(user_id, group_id, status, created_by)
+-- SELECT "marlin",
+-- (SELECT id from user_group where name = "dory-group"),
+-- "PENDING","dory";
+--
+--INSERT INTO user_group_member(user_id, group_id, status, created_by, deleted_by)
+-- SELECT "pearl",
+-- (SELECT id from user_group where name = "dory-group"),
+-- "DELETED","dory", "pearl";
+--
+--INSERT INTO user_group_member(user_id, group_id, status, created_by)
+-- SELECT "pearl",
+-- (SELECT id from user_group where name = "auto-group"),
+-- "ACTIVE","system";
+--
+--INSERT INTO user_group_member(user_id, group_id, status, created_by)
+-- SELECT "dory",
+-- (SELECT id from user_group where name = "deleted-group"),
+-- "ACTIVE","dory";
-- virtual corpora
@@ -86,11 +86,11 @@
'{"collection":{"@type":"koral:doc","value":"GOE","match":"match:eq","key":"corpusSigle"}}');
-- virtual corpus access
-INSERT INTO query_access(query_id, user_group_id, status, created_by)
- SELECT
- (SELECT id from query where name = "group-vc"),
- (SELECT id from user_group where name = "dory-group"),
- "ACTIVE", "dory";
+--INSERT INTO query_access(query_id, user_group_id, status, created_by)
+-- SELECT
+-- (SELECT id from query where name = "group-vc"),
+-- (SELECT id from user_group where name = "dory-group"),
+-- "ACTIVE", "dory";
--INSERT INTO query_access(query_id, user_group_id, status, created_by)
-- SELECT
@@ -98,17 +98,17 @@
-- (SELECT id from user_group where name = "all users"),
-- "ACTIVE", "system";
-INSERT INTO query_access(query_id, user_group_id, status, created_by)
- SELECT
- (SELECT id from query where name = "published-vc"),
- (SELECT id from user_group where name = "marlin-group"),
- "ACTIVE", "marlin";
+--INSERT INTO query_access(query_id, user_group_id, status, created_by)
+-- SELECT
+-- (SELECT id from query where name = "published-vc"),
+-- (SELECT id from user_group where name = "marlin-group"),
+-- "ACTIVE", "marlin";
-INSERT INTO query_access(query_id, user_group_id, status, created_by)
- SELECT
- (SELECT id from query where name = "published-vc"),
- (SELECT id from user_group where name = "auto-group"),
- "HIDDEN", "system";
+--INSERT INTO query_access(query_id, user_group_id, status, created_by)
+-- SELECT
+-- (SELECT id from query where name = "published-vc"),
+-- (SELECT id from user_group where name = "auto-group"),
+-- "HIDDEN", "system";
-- Summary user VC Lists
diff --git a/src/main/resources/db/test/V3.3__insert_member_roles.sql b/src/main/resources/db/test/V3.3__insert_member_roles.sql
index effbbcb..b1db4b6 100644
--- a/src/main/resources/db/test/V3.3__insert_member_roles.sql
+++ b/src/main/resources/db/test/V3.3__insert_member_roles.sql
@@ -1,52 +1,52 @@
-- member roles
-- marlin group
-INSERT INTO group_member_role(group_member_id,role_id)
-SELECT
- (SELECT id FROM user_group_member WHERE user_id="marlin" AND group_id=1),
- (SELECT id FROM role WHERE name = "USER_GROUP_ADMIN");
-
-INSERT INTO group_member_role(group_member_id,role_id)
-SELECT
- (SELECT id FROM user_group_member WHERE user_id="marlin" AND group_id=1),
- (SELECT id FROM role WHERE name = "VC_ACCESS_ADMIN");
-
-INSERT INTO group_member_role(group_member_id,role_id)
-SELECT
- (SELECT id FROM user_group_member WHERE user_id="dory" AND group_id=1),
- (SELECT id FROM role WHERE name = "USER_GROUP_ADMIN");
-
-INSERT INTO group_member_role(group_member_id,role_id)
-SELECT
- (SELECT id FROM user_group_member WHERE user_id="dory" AND group_id=1),
- (SELECT id FROM role WHERE name = "VC_ACCESS_ADMIN");
+--INSERT INTO group_member_role(group_member_id,role_id)
+--SELECT
+-- (SELECT id FROM user_group_member WHERE user_id="marlin" AND group_id=1),
+-- (SELECT id FROM role WHERE name = "USER_GROUP_ADMIN");
+--
+--INSERT INTO group_member_role(group_member_id,role_id)
+--SELECT
+-- (SELECT id FROM user_group_member WHERE user_id="marlin" AND group_id=1),
+-- (SELECT id FROM role WHERE name = "VC_ACCESS_ADMIN");
+--
+--INSERT INTO group_member_role(group_member_id,role_id)
+--SELECT
+-- (SELECT id FROM user_group_member WHERE user_id="dory" AND group_id=1),
+-- (SELECT id FROM role WHERE name = "USER_GROUP_ADMIN");
+--
+--INSERT INTO group_member_role(group_member_id,role_id)
+--SELECT
+-- (SELECT id FROM user_group_member WHERE user_id="dory" AND group_id=1),
+-- (SELECT id FROM role WHERE name = "VC_ACCESS_ADMIN");
-- dory group
-INSERT INTO group_member_role(group_member_id,role_id)
-SELECT
- (SELECT id FROM user_group_member WHERE user_id="dory" AND group_id=2),
- (SELECT id FROM role WHERE name = "USER_GROUP_ADMIN");
-
-INSERT INTO group_member_role(group_member_id,role_id)
-SELECT
- (SELECT id FROM user_group_member WHERE user_id="dory" AND group_id=2),
- (SELECT id FROM role WHERE name = "VC_ACCESS_ADMIN");
-
-INSERT INTO group_member_role(group_member_id,role_id)
-SELECT
- (SELECT id FROM user_group_member WHERE user_id="nemo" AND group_id=2),
- (SELECT id FROM role WHERE name = "USER_GROUP_MEMBER");
-
-INSERT INTO group_member_role(group_member_id,role_id)
-SELECT
- (SELECT id FROM user_group_member WHERE user_id="nemo" AND group_id=2),
- (SELECT id FROM role WHERE name = "VC_ACCESS_MEMBER");
+--INSERT INTO group_member_role(group_member_id,role_id)
+--SELECT
+-- (SELECT id FROM user_group_member WHERE user_id="dory" AND group_id=2),
+-- (SELECT id FROM role WHERE name = "USER_GROUP_ADMIN");
+--
+--INSERT INTO group_member_role(group_member_id,role_id)
+--SELECT
+-- (SELECT id FROM user_group_member WHERE user_id="dory" AND group_id=2),
+-- (SELECT id FROM role WHERE name = "VC_ACCESS_ADMIN");
+--
+--INSERT INTO group_member_role(group_member_id,role_id)
+--SELECT
+-- (SELECT id FROM user_group_member WHERE user_id="nemo" AND group_id=2),
+-- (SELECT id FROM role WHERE name = "USER_GROUP_MEMBER");
+--
+--INSERT INTO group_member_role(group_member_id,role_id)
+--SELECT
+-- (SELECT id FROM user_group_member WHERE user_id="nemo" AND group_id=2),
+-- (SELECT id FROM role WHERE name = "VC_ACCESS_MEMBER");
-- auto group
-INSERT INTO group_member_role(group_member_id,role_id)
-SELECT
- (SELECT id FROM user_group_member WHERE user_id="pearl" AND group_id=3),
- (SELECT id FROM role WHERE name = "VC_ACCESS_MEMBER");
+--INSERT INTO group_member_role(group_member_id,role_id)
+--SELECT
+-- (SELECT id FROM user_group_member WHERE user_id="pearl" AND group_id=3),
+-- (SELECT id FROM role WHERE name = "VC_ACCESS_MEMBER");