1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
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')
""")
]
|
