diff options
4 files changed, 34 insertions, 14 deletions
diff --git a/migrations/auth/20221110_05_BaNtL-create-roles-table.py b/migrations/auth/20221110_05_BaNtL-create-roles-table.py index bf238f6..edf3ea4 100644 --- a/migrations/auth/20221110_05_BaNtL-create-roles-table.py +++ b/migrations/auth/20221110_05_BaNtL-create-roles-table.py @@ -10,17 +10,9 @@ steps = [ step( """ CREATE TABLE IF NOT EXISTS roles( - group_id TEXT NOT NULL, role_id TEXT NOT NULL PRIMARY KEY, - role_name TEXT NOT NULL, - FOREIGN KEY(group_id) REFERENCES groups(group_id) + role_name TEXT NOT NULL ) WITHOUT ROWID """, - "DROP TABLE IF EXISTS roles"), - step( - """ - CREATE INDEX IF NOT EXISTS idx_tbl_roles_cols_group_id - ON roles(group_id) - """, - "DROP INDEX IF EXISTS idx_tbl_roles_cols_group_id") + "DROP TABLE IF EXISTS roles") ] diff --git a/migrations/auth/20221114_03_PtWjc-create-group-roles-table.py b/migrations/auth/20221114_03_PtWjc-create-group-roles-table.py new file mode 100644 index 0000000..f314000 --- /dev/null +++ b/migrations/auth/20221114_03_PtWjc-create-group-roles-table.py @@ -0,0 +1,27 @@ +""" +Create 'group_roles' table +""" + +from yoyo import step + +__depends__ = {'20221114_02_DKKjn-drop-generic-role-tables'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS group_roles( + group_id TEXT NOT NULL, + role_id TEXT NOT NULL, + PRIMARY KEY(group_id, role_id), + FOREIGN KEY(group_id) REFERENCES groups(group_id), + FOREIGN KEY(role_id) REFERENCES roles(role_id) + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS group_roles"), + step( + """ + CREATE INDEX IF NOT EXISTS idx_tbl_group_roles_cols_group_id + ON group_roles(group_id) + """, + "DROP INDEX IF EXISTS idx_tbl_group_roles_cols_group_id") +] diff --git a/tests/unit/auth/test_migrations_create_tables.py b/tests/unit/auth/test_migrations_create_tables.py index ce02f62..314ade5 100644 --- a/tests/unit/auth/test_migrations_create_tables.py +++ b/tests/unit/auth/test_migrations_create_tables.py @@ -23,7 +23,8 @@ migrations_and_tables = ( ("20221110_06_Pq2kT-create-generic-roles-table.py", "generic_roles"), ("20221110_07_7WGa1-create-role-privileges-table.py", "role_privileges"), ("20221114_01_n8gsF-create-generic-role-privileges-table.py", - "generic_role_privileges")) + "generic_role_privileges"), + ("20221114_03_PtWjc-create-group-roles-table.py", "group_roles")) @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 87a624f..b83b374 100644 --- a/tests/unit/auth/test_migrations_indexes.py +++ b/tests/unit/auth/test_migrations_indexes.py @@ -15,13 +15,13 @@ AND name= ? """ migrations_tables_and_indexes = ( - ("20221110_05_BaNtL-create-roles-table.py", "roles", - "idx_tbl_roles_cols_group_id"), ("20221110_07_7WGa1-create-role-privileges-table.py", "role_privileges", "idx_tbl_role_privileges_cols_role_id"), ("20221114_01_n8gsF-create-generic-role-privileges-table.py", "generic_role_privileges", - "idx_tbl_generic_role_privileges_cols_generic_role_id")) + "idx_tbl_generic_role_privileges_cols_generic_role_id"), + ("20221114_03_PtWjc-create-group-roles-table.py", "group_roles", + "idx_tbl_group_roles_cols_group_id")) @pytest.mark.unit_test @pytest.mark.parametrize( |