aboutsummaryrefslogtreecommitdiff
path: root/migrations/auth
diff options
context:
space:
mode:
Diffstat (limited to 'migrations/auth')
-rw-r--r--migrations/auth/20231002_01_tzxTf-link-inbredsets-to-auth-system.py84
1 files changed, 84 insertions, 0 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')
+ """)
+]