diff options
3 files changed, 105 insertions, 2 deletions
diff --git a/migrations/auth/20231002_01_tzxTf-link-inbredsets-to-auth-system.py b/migrations/auth/20231002_01_tzxTf-link-inbredsets-to-auth-system.py new file mode 100644 index 0000000..402e9a5 --- /dev/null +++ b/migrations/auth/20231002_01_tzxTf-link-inbredsets-to-auth-system.py @@ -0,0 +1,84 @@ +""" +link InbredSets to auth system +""" + +from yoyo import step + +__depends__ = {'20230925_01_TWJuR-add-new-public-view-role', '__init__'} + +steps = [ + step( + """ + INSERT INTO resource_categories + ( + resource_category_id, + resource_category_key, + resource_category_description, + resource_meta + ) + VALUES + ( + 'b3654600-4ab0-4745-8292-5849b34173a7', + 'inbredset-group', + 'A resource that controls access to a particular InbredSet group', + '{"default-access-level":"public-read"}' + ) + """, + """ + DELETE FROM resource_categories WHERE + resource_category_id = 'b3654600-4ab0-4745-8292-5849b34173a7' + """ + ), + step( + """ + CREATE TABLE IF NOT EXISTS linked_inbredset_groups + -- Link InbredSet groups in MariaDB to auth system + ( + data_link_id TEXT NOT NULL PRIMARY KEY, -- A new ID for the auth system + SpeciesId TEXT NOT NULL, -- Species ID in MariaDB + InbredSetId TEXT NOT NULL, -- The InbredSet ID in MariaDB + InbredSetName TEXT NOT NULL, -- The InbredSet group's name in MariaDB + InbredSetFullName TEXT NOT NULL, -- The InbredSet group's full name in MariaDB + UNIQUE(SpeciesId, InbredSetId) + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS linked_inbredset_groups"), + step( + """ + CREATE TABLE IF NOT EXISTS inbredset_group_resources + -- Link the InbredSet data to a specific resource + ( + resource_id TEXT NOT NULL, -- Linked resource: one-to-one + data_link_id TEXT NOT NULL, + PRIMARY KEY(resource_id, data_link_id), + UNIQUE(resource_id), -- resource is linked to only one InbredSet + UNIQUE(data_link_id), -- InbredSet is linked to only one resource + FOREIGN KEY(resource_id) + REFERENCES resources(resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(data_link_id) + REFERENCES linked_inbredset_groups(data_link_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS inbredset_group_resources"), + step( + """ + INSERT INTO privileges(privilege_id, privilege_description) VALUES + ('system:inbredset:create-case-attribute', 'Create a new case attribute for an InbredSet group.'), + ('system:inbredset:delete-case-attribute', 'Delete an existing case-attribute from an InbredSet group'), + ('system:inbredset:edit-case-attribute', 'Edit the values of case-attributes of an InbredSet group'), + ('system:inbredset:view-case-attribute', 'View the case-attributes of an InbredSet group'), + ('system:inbredset:apply-case-attribute-edit', 'Apply an edit to case-attributes performed by another user for an InbredSet group'), + ('system:inbredset:reject-case-attribute-edit', 'Reject an edit to case-attributes performed by another user for an InbredSet group') + """, + """ + DELETE FROM privileges WHERE privilege_id IN ( + 'system:inbredset:create-case-attribute', + 'system:inbredset:delete-case-attribute', + 'system:inbredset:edit-case-attribute', + 'system:inbredset:view-case-attribute', + 'system:inbredset:apply-case-attribute-edit', + 'system:inbredset:reject-case-attribute-edit') + """) +] diff --git a/tests/unit/auth/test_migrations_add_data_to_table.py b/tests/unit/auth/test_migrations_add_data_to_table.py index b686f25..d9e2ca4 100644 --- a/tests/unit/auth/test_migrations_add_data_to_table.py +++ b/tests/unit/auth/test_migrations_add_data_to_table.py @@ -22,7 +22,21 @@ test_params = ( ("a0e67630-d502-4b9f-b23f-6805d0f30e30", "5103cc68-96f8-4ebb-83a4-a31692402c9b"), ("a0e67630-d502-4b9f-b23f-6805d0f30e30", - "1c59eff5-9336-4ed2-a166-8f70d4cb012e"))),) + "1c59eff5-9336-4ed2-a166-8f70d4cb012e"))), + ("20231002_01_tzxTf-link-inbredsets-to-auth-system.py", + "SELECT privilege_id FROM privileges WHERE privilege_id IN (?, ?, ?, ?, ?, ?)", + ("system:inbredset:create-case-attribute", + "system:inbredset:delete-case-attribute", + "system:inbredset:edit-case-attribute", + "system:inbredset:view-case-attribute", + "system:inbredset:apply-case-attribute-edit", + "system:inbredset:reject-case-attribute-edit"), + (("system:inbredset:create-case-attribute",), + ("system:inbredset:delete-case-attribute",), + ("system:inbredset:edit-case-attribute",), + ("system:inbredset:view-case-attribute",), + ("system:inbredset:apply-case-attribute-edit",), + ("system:inbredset:reject-case-attribute-edit",)))) @pytest.mark.unit_test @pytest.mark.parametrize("migration_file,query,query_params,data", test_params) diff --git a/tests/unit/auth/test_migrations_create_tables.py b/tests/unit/auth/test_migrations_create_tables.py index 8b0f36e..90c3fb8 100644 --- a/tests/unit/auth/test_migrations_create_tables.py +++ b/tests/unit/auth/test_migrations_create_tables.py @@ -43,7 +43,11 @@ migrations_and_tables = ( ("20230907_01_pjnxz-refactor-add-resource-ownership-table.py", "resource_ownership"), ("20230907_04_3LnrG-refactor-create-group-resources-table.py", - "group_resources")) + "group_resources"), + ("20231002_01_tzxTf-link-inbredsets-to-auth-system.py", + "linked_inbredset_groups"), + ("20231002_01_tzxTf-link-inbredsets-to-auth-system.py", + "inbredset_group_resources")) @pytest.mark.unit_test @pytest.mark.parametrize("migration_file,the_table", migrations_and_tables) @@ -64,6 +68,7 @@ def test_create_table( apply_single_migration(backend, get_migration(migration_path)) cursor.execute("SELECT name FROM sqlite_schema WHERE type='table'") result_after_migration = cursor.fetchall() + rollback_single_migration(backend, get_migration(migration_path)) rollback_migrations(backend, older_migrations) assert the_table not in [row[0] for row in result_before_migration] |