aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorFrederick Muriuki Muriithi2022-12-06 11:01:05 +0300
committerFrederick Muriuki Muriithi2022-12-06 11:01:05 +0300
commit09f4d9c4bec6e3395296a69ce718dea30240ed54 (patch)
tree2db833b92bcc79f9a330226bacfb24b3c24d5e40
parente781996b952bc1ff9d9cd7703cb5e37e2a282162 (diff)
downloadgenenetwork3-09f4d9c4bec6e3395296a69ce718dea30240ed54.tar.gz
migrations: Add `group_user_roles_on_resources` table
* migrations/auth/20221206_01_BbeF9-create-group-user-roles-on-resources-table.py: new migration * tests/unit/auth/test_migrations_create_tables.py: Test creation of table * tests/unit/auth/test_migrations_indexes.py: Test creation of index
-rw-r--r--migrations/auth/20221206_01_BbeF9-create-group-user-roles-on-resources-table.py36
-rw-r--r--tests/unit/auth/test_migrations_create_tables.py4
-rw-r--r--tests/unit/auth/test_migrations_indexes.py5
3 files changed, 43 insertions, 2 deletions
diff --git a/migrations/auth/20221206_01_BbeF9-create-group-user-roles-on-resources-table.py b/migrations/auth/20221206_01_BbeF9-create-group-user-roles-on-resources-table.py
new file mode 100644
index 0000000..b683b03
--- /dev/null
+++ b/migrations/auth/20221206_01_BbeF9-create-group-user-roles-on-resources-table.py
@@ -0,0 +1,36 @@
+"""
+Create 'group_user_roles_on_resources' table
+"""
+
+from yoyo import step
+
+__depends__ = {'20221117_02_fmuZh-create-group-users-table'}
+
+steps = [
+ step(
+ """
+ CREATE TABLE group_user_roles_on_resources (
+ group_id TEXT NOT NULL,
+ user_id TEXT NOT NULL,
+ role_id TEXT NOT NULL,
+ resource_id TEXT NOT NULL,
+ PRIMARY KEY (group_id, user_id, role_id, resource_id),
+ FOREIGN KEY (group_id, user_id)
+ REFERENCES group_users(group_id, user_id),
+ FOREIGN KEY (group_id, role_id)
+ REFERENCES group_roles(group_id, role_id),
+ FOREIGN KEY (group_id, resource_id)
+ REFERENCES resources(group_id, resource_id)
+ ) WITHOUT ROWID
+ """,
+ "DROP TABLE IF EXISTS group_user_roles_on_resources"),
+ step(
+ """
+ CREATE INDEX IF NOT EXISTS
+ idx_tbl_group_user_roles_on_resources_group_user_resource
+ ON group_user_roles_on_resources(group_id, user_id, resource_id)
+ """,
+ """
+ DROP INDEX IF EXISTS
+ idx_tbl_group_user_roles_on_resources_group_user_resource""")
+]
diff --git a/tests/unit/auth/test_migrations_create_tables.py b/tests/unit/auth/test_migrations_create_tables.py
index e51d04b..513a50d 100644
--- a/tests/unit/auth/test_migrations_create_tables.py
+++ b/tests/unit/auth/test_migrations_create_tables.py
@@ -24,7 +24,9 @@ migrations_and_tables = (
"generic_role_privileges"),
("20221114_03_PtWjc-create-group-roles-table.py", "group_roles"),
("20221114_05_hQun6-create-user-roles-table.py", "user_roles"),
- ("20221117_02_fmuZh-create-group-users-table.py", "group_users"))
+ ("20221117_02_fmuZh-create-group-users-table.py", "group_users"),
+ ("20221206_01_BbeF9-create-group-user-roles-on-resources-table.py",
+ "group_user_roles_on_resources"))
@pytest.mark.unit_test
@pytest.mark.parametrize("migration_file,the_table", migrations_and_tables)
diff --git a/tests/unit/auth/test_migrations_indexes.py b/tests/unit/auth/test_migrations_indexes.py
index ea9d7e1..b1f06d9 100644
--- a/tests/unit/auth/test_migrations_indexes.py
+++ b/tests/unit/auth/test_migrations_indexes.py
@@ -22,7 +22,10 @@ migrations_tables_and_indexes = (
("20221114_05_hQun6-create-user-roles-table.py", "user_roles",
"idx_tbl_user_roles_cols_user_id"),
("20221117_02_fmuZh-create-group-users-table.py", "group_users",
- "tbl_group_users_cols_group_id"))
+ "tbl_group_users_cols_group_id"),
+ ("20221206_01_BbeF9-create-group-user-roles-on-resources-table.py",
+ "group_user_roles_on_resources",
+ "idx_tbl_group_user_roles_on_resources_group_user_resource"))
@pytest.mark.unit_test
@pytest.mark.parametrize(