migrations: New migration - InbredSet Resources

Provide a new migration to create tables to handle the InbredSet
resources. The migration also sets up the resource category and the
related privileges.

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]