diff options
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( |