diff options
Diffstat (limited to 'migrations')
39 files changed, 1250 insertions, 0 deletions
diff --git a/migrations/auth/20221103_01_js9ub-initialise-the-auth-entic-oris-ation-database.py b/migrations/auth/20221103_01_js9ub-initialise-the-auth-entic-oris-ation-database.py new file mode 100644 index 0000000..d511f5d --- /dev/null +++ b/migrations/auth/20221103_01_js9ub-initialise-the-auth-entic-oris-ation-database.py @@ -0,0 +1,19 @@ +""" +Initialise the auth(entic|oris)ation database. +""" + +from yoyo import step + +__depends__ = {} # type: ignore[var-annotated] + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS users( + user_id TEXT PRIMARY KEY NOT NULL, + email TEXT UNIQUE NOT NULL, + name TEXT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS users") +] diff --git a/migrations/auth/20221103_02_sGrIs-create-user-credentials-table.py b/migrations/auth/20221103_02_sGrIs-create-user-credentials-table.py new file mode 100644 index 0000000..48bd663 --- /dev/null +++ b/migrations/auth/20221103_02_sGrIs-create-user-credentials-table.py @@ -0,0 +1,20 @@ +""" +create user_credentials table +""" + +from yoyo import step + +__depends__ = {'20221103_01_js9ub-initialise-the-auth-entic-oris-ation-database'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS user_credentials( + user_id TEXT PRIMARY KEY, + password TEXT NOT NULL, + FOREIGN KEY(user_id) REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS user_credentials") +] diff --git a/migrations/auth/20221108_01_CoxYh-create-the-groups-table.py b/migrations/auth/20221108_01_CoxYh-create-the-groups-table.py new file mode 100644 index 0000000..29f92d4 --- /dev/null +++ b/migrations/auth/20221108_01_CoxYh-create-the-groups-table.py @@ -0,0 +1,19 @@ +""" +Create the groups table +""" + +from yoyo import step + +__depends__ = {'20221103_02_sGrIs-create-user-credentials-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS groups( + group_id TEXT PRIMARY KEY NOT NULL, + group_name TEXT NOT NULL, + group_metadata TEXT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS groups") +] diff --git a/migrations/auth/20221108_02_wxTr9-create-privileges-table.py b/migrations/auth/20221108_02_wxTr9-create-privileges-table.py new file mode 100644 index 0000000..67720b2 --- /dev/null +++ b/migrations/auth/20221108_02_wxTr9-create-privileges-table.py @@ -0,0 +1,18 @@ +""" +Create privileges table +""" + +from yoyo import step + +__depends__ = {'20221108_01_CoxYh-create-the-groups-table'} + +steps = [ + step( + """ + CREATE TABLE privileges( + privilege_id TEXT PRIMARY KEY, + privilege_name TEXT NOT NULL + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS privileges") +] diff --git a/migrations/auth/20221108_03_Pbhb1-create-resource-categories-table.py b/migrations/auth/20221108_03_Pbhb1-create-resource-categories-table.py new file mode 100644 index 0000000..ce752ef --- /dev/null +++ b/migrations/auth/20221108_03_Pbhb1-create-resource-categories-table.py @@ -0,0 +1,19 @@ +""" +Create resource_categories table +""" + +from yoyo import step + +__depends__ = {'20221108_02_wxTr9-create-privileges-table'} + +steps = [ + step( + """ + CREATE TABLE resource_categories( + resource_category_id TEXT PRIMARY KEY, + resource_category_key TEXT NOT NULL, + resource_category_description TEXT NOT NULL + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS resource_categories") +] diff --git a/migrations/auth/20221108_04_CKcSL-init-data-in-resource-categories-table.py b/migrations/auth/20221108_04_CKcSL-init-data-in-resource-categories-table.py new file mode 100644 index 0000000..76ffbef --- /dev/null +++ b/migrations/auth/20221108_04_CKcSL-init-data-in-resource-categories-table.py @@ -0,0 +1,25 @@ +""" +Init data in resource_categories table +""" + +from yoyo import step + +__depends__ = {'20221108_03_Pbhb1-create-resource-categories-table'} + +steps = [ + step( + """ + INSERT INTO resource_categories VALUES + ('fad071a3-2fc8-40b8-992b-cdefe7dcac79', 'mrna', 'mRNA Dataset'), + ('548d684b-d4d1-46fb-a6d3-51a56b7da1b3', 'phenotype', 'Phenotype (Publish) Dataset'), + ('48056f84-a2a6-41ac-8319-0e1e212cba2a', 'genotype', 'Genotype Dataset') + """, + """ + DELETE FROM resource_categories WHERE resource_category_id IN + ( + 'fad071a3-2fc8-40b8-992b-cdefe7dcac79', + '548d684b-d4d1-46fb-a6d3-51a56b7da1b3', + '48056f84-a2a6-41ac-8319-0e1e212cba2a' + ) + """) +] diff --git a/migrations/auth/20221109_01_HbD5F-add-resource-meta-field-to-resource-categories-field.py b/migrations/auth/20221109_01_HbD5F-add-resource-meta-field-to-resource-categories-field.py new file mode 100644 index 0000000..6c829b1 --- /dev/null +++ b/migrations/auth/20221109_01_HbD5F-add-resource-meta-field-to-resource-categories-field.py @@ -0,0 +1,17 @@ +""" +Add 'resource_meta' field to 'resource_categories' field. +""" + +from yoyo import step + +__depends__ = {'20221108_04_CKcSL-init-data-in-resource-categories-table'} + +steps = [ + step( + """ + ALTER TABLE resource_categories + ADD COLUMN + resource_meta TEXT NOT NULL DEFAULT '[]' + """, + "ALTER TABLE resource_categories DROP COLUMN resource_meta") +] diff --git a/migrations/auth/20221110_01_WtZ1I-create-resources-table.py b/migrations/auth/20221110_01_WtZ1I-create-resources-table.py new file mode 100644 index 0000000..abc8895 --- /dev/null +++ b/migrations/auth/20221110_01_WtZ1I-create-resources-table.py @@ -0,0 +1,26 @@ +""" +Create 'resources' table +""" + +from yoyo import step + +__depends__ = {'20221109_01_HbD5F-add-resource-meta-field-to-resource-categories-field'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS resources( + group_id TEXT NOT NULL, + resource_id TEXT NOT NULL, + resource_name TEXT NOT NULL UNIQUE, + resource_category_id TEXT NOT NULL, + PRIMARY KEY(group_id, resource_id), + FOREIGN KEY(group_id) REFERENCES groups(group_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(resource_category_id) + REFERENCES resource_categories(resource_category_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS resources") +] diff --git a/migrations/auth/20221110_05_BaNtL-create-roles-table.py b/migrations/auth/20221110_05_BaNtL-create-roles-table.py new file mode 100644 index 0000000..51e19e8 --- /dev/null +++ b/migrations/auth/20221110_05_BaNtL-create-roles-table.py @@ -0,0 +1,19 @@ +""" +Create 'roles' table +""" + +from yoyo import step + +__depends__ = {'20221110_01_WtZ1I-create-resources-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS roles( + role_id TEXT NOT NULL PRIMARY KEY, + role_name TEXT NOT NULL, + user_editable INTEGER NOT NULL DEFAULT 1 CHECK (user_editable=0 or user_editable=1) + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS roles") +] diff --git a/migrations/auth/20221110_06_Pq2kT-create-generic-roles-table.py b/migrations/auth/20221110_06_Pq2kT-create-generic-roles-table.py new file mode 100644 index 0000000..2b55c2b --- /dev/null +++ b/migrations/auth/20221110_06_Pq2kT-create-generic-roles-table.py @@ -0,0 +1,24 @@ +""" +Create 'generic_roles' table + +The roles in this table will be template roles, defining some common roles that +can be used within the groups. + +They could also be used to define system-level roles, though those will not be +provided to the "common" users. +""" + +from yoyo import step + +__depends__ = {'20221110_05_BaNtL-create-roles-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS generic_roles( + role_id TEXT PRIMARY KEY, + role_name TEXT NOT NULL + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS generic_roles") +] diff --git a/migrations/auth/20221110_07_7WGa1-create-role-privileges-table.py b/migrations/auth/20221110_07_7WGa1-create-role-privileges-table.py new file mode 100644 index 0000000..0d0eeb9 --- /dev/null +++ b/migrations/auth/20221110_07_7WGa1-create-role-privileges-table.py @@ -0,0 +1,29 @@ +""" +Create 'role_privileges' table +""" + +from yoyo import step + +__depends__ = {'20221110_06_Pq2kT-create-generic-roles-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS role_privileges( + role_id TEXT NOT NULL, + privilege_id TEXT NOT NULL, + PRIMARY KEY(role_id, privilege_id), + FOREIGN KEY(role_id) REFERENCES roles(role_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(privilege_id) REFERENCES privileges(privilege_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS role_privileges"), + step( + """ + CREATE INDEX IF NOT EXISTS idx_tbl_role_privileges_cols_role_id + ON role_privileges(role_id) + """, + "DROP INDEX IF EXISTS idx_tbl_role_privileges_cols_role_id") +] diff --git a/migrations/auth/20221110_08_23psB-add-privilege-category-and-privilege-description-columns-to-privileges-table.py b/migrations/auth/20221110_08_23psB-add-privilege-category-and-privilege-description-columns-to-privileges-table.py new file mode 100644 index 0000000..077182b --- /dev/null +++ b/migrations/auth/20221110_08_23psB-add-privilege-category-and-privilege-description-columns-to-privileges-table.py @@ -0,0 +1,22 @@ +""" +Add 'privilege_category' and 'privilege_description' columns to 'privileges' table +""" + +from yoyo import step + +__depends__ = {'20221110_07_7WGa1-create-role-privileges-table'} + +steps = [ + step( + """ + ALTER TABLE privileges ADD COLUMN + privilege_category TEXT NOT NULL DEFAULT 'common' + """, + "ALTER TABLE privileges DROP COLUMN privilege_category"), + step( + """ + ALTER TABLE privileges ADD COLUMN + privilege_description TEXT + """, + "ALTER TABLE privileges DROP COLUMN privilege_description") +] diff --git a/migrations/auth/20221113_01_7M0hv-enumerate-initial-privileges.py b/migrations/auth/20221113_01_7M0hv-enumerate-initial-privileges.py new file mode 100644 index 0000000..072f226 --- /dev/null +++ b/migrations/auth/20221113_01_7M0hv-enumerate-initial-privileges.py @@ -0,0 +1,66 @@ +""" +Enumerate initial privileges +""" + +from yoyo import step + +__depends__ = {'20221110_08_23psB-add-privilege-category-and-privilege-description-columns-to-privileges-table'} + +steps = [ + step( + """ + INSERT INTO + privileges(privilege_id, privilege_name, privilege_category, + privilege_description) + VALUES + -- group-management privileges + ('4842e2aa-38b9-4349-805e-0a99a9cf8bff', 'create-group', + 'group-management', 'Create a group'), + ('3ebfe79c-d159-4629-8b38-772cf4bc2261', 'view-group', + 'group-management', 'View the details of a group'), + ('52576370-b3c7-4e6a-9f7e-90e9dbe24d8f', 'edit-group', + 'group-management', 'Edit the details of a group'), + ('13ec2a94-4f1a-442d-aad2-936ad6dd5c57', 'delete-group', + 'group-management', 'Delete a group'), + ('ae4add8c-789a-4d11-a6e9-a306470d83d9', 'add-group-member', + 'group-management', 'Add a user to a group'), + ('f1bd3f42-567e-4965-9643-6d1a52ddee64', 'remove-group-member', + 'group-management', 'Remove a user from a group'), + ('80f11285-5079-4ec0-907c-06509f88a364', 'assign-group-leader', + 'group-management', 'Assign user group-leader privileges'), + ('d4afe2b3-4ca0-4edd-b37d-966535b5e5bd', + 'transfer-group-leadership', 'group-management', + 'Transfer leadership of the group to some other member'), + + -- resource-management privileges + ('aa25b32a-bff2-418d-b0a2-e26b4a8f089b', 'create-resource', + 'resource-management', 'Create a resource object'), + ('7f261757-3211-4f28-a43f-a09b800b164d', 'view-resource', + 'resource-management', 'view a resource and use it in computations'), + ('2f980855-959b-4339-b80e-25d1ec286e21', 'edit-resource', + 'resource-management', 'edit/update a resource'), + ('d2a070fd-e031-42fb-ba41-d60cf19e5d6d', 'delete-resource', + 'resource-management', 'Delete a resource'), + + -- role-management privileges + ('221660b1-df05-4be1-b639-f010269dbda9', 'create-role', + 'role-management', 'Create a new role'), + ('7bcca363-cba9-4169-9e31-26bdc6179b28', 'edit-role', + 'role-management', 'edit/update an existing role'), + ('5103cc68-96f8-4ebb-83a4-a31692402c9b', 'assign-role', + 'role-management', 'Assign a role to an existing user'), + ('1c59eff5-9336-4ed2-a166-8f70d4cb012e', 'delete-role', + 'role-management', 'Delete an existing role'), + + -- user-management privileges + ('e7252301-6ee0-43ba-93ef-73b607cf06f6', 'reset-any-password', + 'user-management', 'Reset the password for any user'), + ('1fe61370-cae9-4983-bd6c-ce61050c510f', 'delete-any-user', + 'user-management', 'Delete any user from the system'), + + -- sytem-admin privileges + ('519db546-d44e-4fdc-9e4e-25aa67548ab3', 'masquerade', + 'system-admin', 'Masquerade as some other user') + """, + "DELETE FROM privileges") +] diff --git a/migrations/auth/20221114_01_n8gsF-create-generic-role-privileges-table.py b/migrations/auth/20221114_01_n8gsF-create-generic-role-privileges-table.py new file mode 100644 index 0000000..2048f4a --- /dev/null +++ b/migrations/auth/20221114_01_n8gsF-create-generic-role-privileges-table.py @@ -0,0 +1,35 @@ +""" +Create 'generic_role_privileges' table + +This table links the generic_roles to the privileges they provide +""" + +from yoyo import step + +__depends__ = {'20221113_01_7M0hv-enumerate-initial-privileges'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS generic_role_privileges( + generic_role_id TEXT NOT NULL, + privilege_id TEXT NOT NULL, + PRIMARY KEY(generic_role_id, privilege_id), + FOREIGN KEY(generic_role_id) REFERENCES generic_roles(role_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(privilege_id) REFERENCES privileges(privilege_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS generic_role_privileges"), + step( + """ + CREATE INDEX IF NOT EXISTS + idx_tbl_generic_role_privileges_cols_generic_role_id + ON generic_role_privileges(generic_role_id) + """, + """ + DROP INDEX IF EXISTS + idx_tbl_generic_role_privileges_cols_generic_role_id + """) +] diff --git a/migrations/auth/20221114_02_DKKjn-drop-generic-role-tables.py b/migrations/auth/20221114_02_DKKjn-drop-generic-role-tables.py new file mode 100644 index 0000000..6bd101b --- /dev/null +++ b/migrations/auth/20221114_02_DKKjn-drop-generic-role-tables.py @@ -0,0 +1,41 @@ +""" +Drop 'generic_role*' tables +""" + +from yoyo import step + +__depends__ = {'20221114_01_n8gsF-create-generic-role-privileges-table'} + +steps = [ + step( + """ + DROP INDEX IF EXISTS + idx_tbl_generic_role_privileges_cols_generic_role_id + """, + """ + CREATE INDEX IF NOT EXISTS + idx_tbl_generic_role_privileges_cols_generic_role_id + ON generic_role_privileges(generic_role_id) + """), + step( + "DROP TABLE IF EXISTS generic_role_privileges", + """ + CREATE TABLE IF NOT EXISTS generic_role_privileges( + generic_role_id TEXT NOT NULL, + privilege_id TEXT NOT NULL, + PRIMARY KEY(generic_role_id, privilege_id), + FOREIGN KEY(generic_role_id) REFERENCES generic_roles(role_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(privilege_id) REFERENCES privileges(privilege_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """), + step( + "DROP TABLE IF EXISTS generic_roles", + """ + CREATE TABLE IF NOT EXISTS generic_roles( + role_id TEXT PRIMARY KEY, + role_name TEXT NOT NULL + ) WITHOUT ROWID + """) +] 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..a7e7b45 --- /dev/null +++ b/migrations/auth/20221114_03_PtWjc-create-group-roles-table.py @@ -0,0 +1,29 @@ +""" +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) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(role_id) REFERENCES roles(role_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) 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/migrations/auth/20221114_04_tLUzB-initialise-basic-roles.py b/migrations/auth/20221114_04_tLUzB-initialise-basic-roles.py new file mode 100644 index 0000000..386f481 --- /dev/null +++ b/migrations/auth/20221114_04_tLUzB-initialise-basic-roles.py @@ -0,0 +1,56 @@ +""" +Initialise basic roles +""" + +from yoyo import step + +__depends__ = {'20221114_03_PtWjc-create-group-roles-table'} + +steps = [ + step( + """ + INSERT INTO roles(role_id, role_name, user_editable) VALUES + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', 'group-leader', '0'), + ('522e4d40-aefc-4a64-b7e0-768b8be517ee', 'resource-owner', '0') + """, + "DELETE FROM roles"), + step( + """ + INSERT INTO role_privileges(role_id, privilege_id) + VALUES + -- group-management + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + '4842e2aa-38b9-4349-805e-0a99a9cf8bff'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + '3ebfe79c-d159-4629-8b38-772cf4bc2261'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + '52576370-b3c7-4e6a-9f7e-90e9dbe24d8f'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + '13ec2a94-4f1a-442d-aad2-936ad6dd5c57'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + 'ae4add8c-789a-4d11-a6e9-a306470d83d9'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + 'f1bd3f42-567e-4965-9643-6d1a52ddee64'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + 'd4afe2b3-4ca0-4edd-b37d-966535b5e5bd'), + + -- resource-management + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + 'aa25b32a-bff2-418d-b0a2-e26b4a8f089b'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + '7f261757-3211-4f28-a43f-a09b800b164d'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + '2f980855-959b-4339-b80e-25d1ec286e21'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + 'd2a070fd-e031-42fb-ba41-d60cf19e5d6d'), + ('522e4d40-aefc-4a64-b7e0-768b8be517ee', + 'aa25b32a-bff2-418d-b0a2-e26b4a8f089b'), + ('522e4d40-aefc-4a64-b7e0-768b8be517ee', + '7f261757-3211-4f28-a43f-a09b800b164d'), + ('522e4d40-aefc-4a64-b7e0-768b8be517ee', + '2f980855-959b-4339-b80e-25d1ec286e21'), + ('522e4d40-aefc-4a64-b7e0-768b8be517ee', + 'd2a070fd-e031-42fb-ba41-d60cf19e5d6d') + """, + "DELETE FROM role_privileges") +] diff --git a/migrations/auth/20221114_05_hQun6-create-user-roles-table.py b/migrations/auth/20221114_05_hQun6-create-user-roles-table.py new file mode 100644 index 0000000..e0de751 --- /dev/null +++ b/migrations/auth/20221114_05_hQun6-create-user-roles-table.py @@ -0,0 +1,29 @@ +""" +Create 'user_roles' table. +""" + +from yoyo import step + +__depends__ = {'20221114_04_tLUzB-initialise-basic-roles'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS user_roles( + user_id TEXT NOT NULL, + role_id TEXT NOT NULL, + PRIMARY KEY(user_id, role_id), + FOREIGN KEY(user_id) REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(role_id) REFERENCES roles(role_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS user_roles"), + step( + """ + CREATE INDEX IF NOT EXISTS idx_tbl_user_roles_cols_user_id + ON user_roles(user_id) + """, + "DROP INDEX IF EXISTS idx_tbl_user_roles_cols_user_id") +] diff --git a/migrations/auth/20221116_01_nKUmX-add-privileges-to-group-leader-role.py b/migrations/auth/20221116_01_nKUmX-add-privileges-to-group-leader-role.py new file mode 100644 index 0000000..2e4ae28 --- /dev/null +++ b/migrations/auth/20221116_01_nKUmX-add-privileges-to-group-leader-role.py @@ -0,0 +1,35 @@ +""" +Add privileges to 'group-leader' role. +""" + +from yoyo import step + +__depends__ = {'20221114_05_hQun6-create-user-roles-table'} + +steps = [ + step( + """ + INSERT INTO role_privileges(role_id, privilege_id) + VALUES + -- role management + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + '221660b1-df05-4be1-b639-f010269dbda9'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + '7bcca363-cba9-4169-9e31-26bdc6179b28'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + '5103cc68-96f8-4ebb-83a4-a31692402c9b'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + '1c59eff5-9336-4ed2-a166-8f70d4cb012e') + """, + """ + DELETE FROM role_privileges + WHERE + role_id='a0e67630-d502-4b9f-b23f-6805d0f30e30' + AND privilege_id IN ( + '221660b1-df05-4be1-b639-f010269dbda9', + '7bcca363-cba9-4169-9e31-26bdc6179b28', + '5103cc68-96f8-4ebb-83a4-a31692402c9b', + '1c59eff5-9336-4ed2-a166-8f70d4cb012e' + ) + """) +] diff --git a/migrations/auth/20221117_01_RDlfx-modify-group-roles-add-group-role-id.py b/migrations/auth/20221117_01_RDlfx-modify-group-roles-add-group-role-id.py new file mode 100644 index 0000000..a4d7806 --- /dev/null +++ b/migrations/auth/20221117_01_RDlfx-modify-group-roles-add-group-role-id.py @@ -0,0 +1,52 @@ +""" +Modify 'group_roles': add 'group_role_id' + +At this point, there is no data in the `group_roles` table and therefore, it +should be safe to simply recreate it. +""" + +from yoyo import step + +__depends__ = {'20221116_01_nKUmX-add-privileges-to-group-leader-role'} + +steps = [ + step( + "DROP INDEX IF EXISTS idx_tbl_group_roles_cols_group_id", + """ + CREATE INDEX IF NOT EXISTS idx_tbl_group_roles_cols_group_id + ON group_roles(group_id) + """), + step( + "DROP TABLE IF EXISTS group_roles", + """ + 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) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(role_id) REFERENCES roles(role_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """), + step( + """ + CREATE TABLE IF NOT EXISTS group_roles( + group_role_id TEXT PRIMARY KEY, + group_id TEXT NOT NULL, + role_id TEXT NOT NULL, + UNIQUE (group_id, role_id), + FOREIGN KEY(group_id) REFERENCES groups(group_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(role_id) REFERENCES roles(role_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) 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/migrations/auth/20221117_02_fmuZh-create-group-users-table.py b/migrations/auth/20221117_02_fmuZh-create-group-users-table.py new file mode 100644 index 0000000..92885ef --- /dev/null +++ b/migrations/auth/20221117_02_fmuZh-create-group-users-table.py @@ -0,0 +1,25 @@ +""" +Create 'group_users' table. +""" + +from yoyo import step + +__depends__ = {'20221117_01_RDlfx-modify-group-roles-add-group-role-id'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS group_users( + group_id TEXT NOT NULL, + user_id TEXT NOT NULL UNIQUE, -- user can only be in one group + PRIMARY KEY(group_id, user_id) + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS group_users"), + step( + """ + CREATE INDEX IF NOT EXISTS tbl_group_users_cols_group_id + ON group_users(group_id) + """, + "DROP INDEX IF EXISTS tbl_group_users_cols_group_id") +] diff --git a/migrations/auth/20221206_01_BbeF9-create-group-user-roles-on-resources-table.py b/migrations/auth/20221206_01_BbeF9-create-group-user-roles-on-resources-table.py new file mode 100644 index 0000000..9aa3667 --- /dev/null +++ b/migrations/auth/20221206_01_BbeF9-create-group-user-roles-on-resources-table.py @@ -0,0 +1,39 @@ +""" +Create 'group_user_roles_on_resources' table +""" + +from yoyo import step + +__depends__ = {'20221117_02_fmuZh-create-group-users-table'} + +steps = [ + step( + """ + CREATE TABLE group_user_roles_on_resources ( + group_id TEXT NOT NULL, + user_id TEXT NOT NULL, + role_id TEXT NOT NULL, + resource_id TEXT NOT NULL, + PRIMARY KEY (group_id, user_id, role_id, resource_id), + FOREIGN KEY (user_id) + REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (group_id, role_id) + REFERENCES group_roles(group_id, role_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (group_id, resource_id) + REFERENCES resources(group_id, resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS group_user_roles_on_resources"), + step( + """ + CREATE INDEX IF NOT EXISTS + idx_tbl_group_user_roles_on_resources_group_user_resource + ON group_user_roles_on_resources(group_id, user_id, resource_id) + """, + """ + DROP INDEX IF EXISTS + idx_tbl_group_user_roles_on_resources_group_user_resource""") +] diff --git a/migrations/auth/20221208_01_sSdHz-add-public-column-to-resources-table.py b/migrations/auth/20221208_01_sSdHz-add-public-column-to-resources-table.py new file mode 100644 index 0000000..2238069 --- /dev/null +++ b/migrations/auth/20221208_01_sSdHz-add-public-column-to-resources-table.py @@ -0,0 +1,16 @@ +""" +Add 'public' column to 'resources' table +""" + +from yoyo import step + +__depends__ = {'20221206_01_BbeF9-create-group-user-roles-on-resources-table'} + +steps = [ + step( + """ + ALTER TABLE resources ADD COLUMN + public INTEGER NOT NULL DEFAULT 0 CHECK (public=0 or public=1) + """, + "ALTER TABLE resources DROP COLUMN public") +] diff --git a/migrations/auth/20221219_01_CI3tN-create-oauth2-clients-table.py b/migrations/auth/20221219_01_CI3tN-create-oauth2-clients-table.py new file mode 100644 index 0000000..475be01 --- /dev/null +++ b/migrations/auth/20221219_01_CI3tN-create-oauth2-clients-table.py @@ -0,0 +1,25 @@ +""" +create oauth2_clients table +""" + +from yoyo import step + +__depends__ = {'20221208_01_sSdHz-add-public-column-to-resources-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS oauth2_clients( + client_id TEXT NOT NULL, + client_secret TEXT NOT NULL, + client_id_issued_at INTEGER NOT NULL, + client_secret_expires_at INTEGER NOT NULL, + client_metadata TEXT, + user_id TEXT NOT NULL, + PRIMARY KEY(client_id), + FOREIGN KEY(user_id) REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS oauth2_clients") +] diff --git a/migrations/auth/20221219_02_buSEU-create-oauth2-tokens-table.py b/migrations/auth/20221219_02_buSEU-create-oauth2-tokens-table.py new file mode 100644 index 0000000..778282b --- /dev/null +++ b/migrations/auth/20221219_02_buSEU-create-oauth2-tokens-table.py @@ -0,0 +1,31 @@ +""" +create oauth2_tokens table +""" + +from yoyo import step + +__depends__ = {'20221219_01_CI3tN-create-oauth2-clients-table'} + +steps = [ + step( + """ + CREATE TABLE oauth2_tokens( + token_id TEXT NOT NULL, + client_id TEXT NOT NULL, + token_type TEXT NOT NULL, + access_token TEXT UNIQUE NOT NULL, + refresh_token TEXT, + scope TEXT, + revoked INTEGER CHECK (revoked = 0 or revoked = 1), + issued_at INTEGER NOT NULL, + expires_in INTEGER NOT NULL, + user_id TEXT NOT NULL, + PRIMARY KEY(token_id), + FOREIGN KEY (client_id) REFERENCES oauth2_clients(client_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (user_id) REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS oauth2_tokens") +] diff --git a/migrations/auth/20221219_03_PcTrb-create-authorisation-code-table.py b/migrations/auth/20221219_03_PcTrb-create-authorisation-code-table.py new file mode 100644 index 0000000..1683f87 --- /dev/null +++ b/migrations/auth/20221219_03_PcTrb-create-authorisation-code-table.py @@ -0,0 +1,31 @@ +""" +create authorisation_code table +""" + +from yoyo import step + +__depends__ = {'20221219_02_buSEU-create-oauth2-tokens-table'} + +steps = [ + step( + """ + CREATE TABLE authorisation_code ( + code_id TEXT NOT NULL, + code TEXT UNIQUE NOT NULL, + client_id NOT NULL, + redirect_uri TEXT, + scope TEXT, + nonce TEXT, + auth_time INTEGER NOT NULL, + code_challenge TEXT, + code_challenge_method TEXT, + user_id TEXT NOT NULL, + PRIMARY KEY (code_id), + FOREIGN KEY (client_id) REFERENCES oauth2_clients(client_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (user_id) REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS authorisation_code") +] diff --git a/migrations/auth/20230111_01_Wd6IZ-remove-create-group-privilege-from-group-leader.py b/migrations/auth/20230111_01_Wd6IZ-remove-create-group-privilege-from-group-leader.py new file mode 100644 index 0000000..7e7fda2 --- /dev/null +++ b/migrations/auth/20230111_01_Wd6IZ-remove-create-group-privilege-from-group-leader.py @@ -0,0 +1,40 @@ +""" +remove 'create-group' privilege from group-leader. +""" + +from yoyo import step + +__depends__ = {'20221219_03_PcTrb-create-authorisation-code-table'} + +steps = [ + step( + """ + DELETE FROM role_privileges + WHERE role_id='a0e67630-d502-4b9f-b23f-6805d0f30e30' + AND privilege_id='4842e2aa-38b9-4349-805e-0a99a9cf8bff' + """, + """ + INSERT INTO role_privileges VALUES + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + '4842e2aa-38b9-4349-805e-0a99a9cf8bff') + """), + step( + """ + INSERT INTO roles(role_id, role_name, user_editable) VALUES + ('ade7e6b0-ba9c-4b51-87d0-2af7fe39a347', 'group-creator', '0') + """, + """ + DELETE FROM roles WHERE role_id='ade7e6b0-ba9c-4b51-87d0-2af7fe39a347' + """), + step( + """ + INSERT INTO role_privileges VALUES + ('ade7e6b0-ba9c-4b51-87d0-2af7fe39a347', + '4842e2aa-38b9-4349-805e-0a99a9cf8bff') + """, + """ + DELETE FROM role_privileges + WHERE role_id='ade7e6b0-ba9c-4b51-87d0-2af7fe39a347' + AND privilege_id='4842e2aa-38b9-4349-805e-0a99a9cf8bff' + """) +] diff --git a/migrations/auth/20230116_01_KwuJ3-rework-privileges-schema.py b/migrations/auth/20230116_01_KwuJ3-rework-privileges-schema.py new file mode 100644 index 0000000..1ef5ab0 --- /dev/null +++ b/migrations/auth/20230116_01_KwuJ3-rework-privileges-schema.py @@ -0,0 +1,111 @@ +""" +rework privileges schema +""" +import contextlib + +from yoyo import step + +__depends__ = {'20230111_01_Wd6IZ-remove-create-group-privilege-from-group-leader'} + +privileges = ( # format: (original_id, original_name, new_id, category) + ("13ec2a94-4f1a-442d-aad2-936ad6dd5c57", "delete-group", + "system:group:delete-group", "group-management"), + ("1c59eff5-9336-4ed2-a166-8f70d4cb012e", "delete-role", + "group:role:delete-role", "role-management"), + ("1fe61370-cae9-4983-bd6c-ce61050c510f", "delete-any-user", + "system:user:delete-user", "user-management"), + ("221660b1-df05-4be1-b639-f010269dbda9", "create-role", + "group:role:create-role", "role-management"), + ("2f980855-959b-4339-b80e-25d1ec286e21", "edit-resource", + "group:resource:edit-resource", "resource-management"), + ("3ebfe79c-d159-4629-8b38-772cf4bc2261", "view-group", + "system:group:view-group", "group-management"), + ("4842e2aa-38b9-4349-805e-0a99a9cf8bff", "create-group", + "system:group:create-group", "group-management"), + ("5103cc68-96f8-4ebb-83a4-a31692402c9b", "assign-role", + "group:user:assign-role", "role-management"), + ("519db546-d44e-4fdc-9e4e-25aa67548ab3", "masquerade", + "system:user:masquerade", "system-admin"), + ("52576370-b3c7-4e6a-9f7e-90e9dbe24d8f", "edit-group", + "system:group:edit-group", "group-management"), + ("7bcca363-cba9-4169-9e31-26bdc6179b28", "edit-role", + "group:role:edit-role", "role-management"), + ("7f261757-3211-4f28-a43f-a09b800b164d", "view-resource", + "group:resource:view-resource", "resource-management"), + ("80f11285-5079-4ec0-907c-06509f88a364", "assign-group-leader", + "system:user:assign-group-leader", "group-management"), + ("aa25b32a-bff2-418d-b0a2-e26b4a8f089b", "create-resource", + "group:resource:create-resource", "resource-management"), + ("ae4add8c-789a-4d11-a6e9-a306470d83d9", "add-group-member", + "group:user:add-group-member", "group-management"), + ("d2a070fd-e031-42fb-ba41-d60cf19e5d6d", "delete-resource", + "group:resource:delete-resource", "resource-management"), + ("d4afe2b3-4ca0-4edd-b37d-966535b5e5bd", "transfer-group-leadership", + "system:group:transfer-group-leader", "group-management"), + ("e7252301-6ee0-43ba-93ef-73b607cf06f6", "reset-any-password", + "system:user:reset-password", "user-management"), + ("f1bd3f42-567e-4965-9643-6d1a52ddee64", "remove-group-member", + "group:user:remove-group-member", "group-management")) + +def rework_privileges_table(cursor): + "rework the schema" + cursor.executemany( + ("UPDATE privileges SET privilege_id=:id " + "WHERE privilege_id=:old_id"), + ({"id": row[2], "old_id": row[0]} for row in privileges)) + cursor.execute("ALTER TABLE privileges DROP COLUMN privilege_category") + cursor.execute("ALTER TABLE privileges DROP COLUMN privilege_name") + +def restore_privileges_table(cursor): + "restore the schema" + cursor.execute(( + "CREATE TABLE privileges_restore (" + " privilege_id TEXT PRIMARY KEY," + " privilege_name TEXT NOT NULL," + " privilege_category TEXT NOT NULL DEFAULT 'common'," + " privilege_description TEXT" + ")")) + id_dict = {row[2]: {"id": row[0], "name": row[1], "cat": row[3]} + for row in privileges} + cursor.execute( + "SELECT privilege_id, privilege_description FROM privileges") + params = ({**id_dict[row[0]], "desc": row[1]} for row in cursor.fetchall()) + cursor.executemany( + "INSERT INTO privileges_restore VALUES (:id, :name, :cat, :desc)", + params) + cursor.execute("DROP TABLE privileges") + cursor.execute("ALTER TABLE privileges_restore RENAME TO privileges") + +def update_privilege_ids_in_role_privileges(cursor): + """Update the ids to new form.""" + cursor.executemany( + ("UPDATE role_privileges SET privilege_id=:new_id " + "WHERE privilege_id=:old_id"), + ({"new_id": row[2], "old_id": row[0]} for row in privileges)) + +def restore_privilege_ids_in_role_privileges(cursor): + """Restore original ids""" + cursor.executemany( + ("UPDATE role_privileges SET privilege_id=:old_id " + "WHERE privilege_id=:new_id"), + ({"new_id": row[2], "old_id": row[0]} for row in privileges)) + +def change_schema(conn): + """Change the privileges schema and IDs""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute("PRAGMA foreign_keys=OFF") + rework_privileges_table(cursor) + update_privilege_ids_in_role_privileges(cursor) + cursor.execute("PRAGMA foreign_keys=ON") + +def restore_schema(conn): + """Change the privileges schema and IDs""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute("PRAGMA foreign_keys=OFF") + restore_privilege_ids_in_role_privileges(cursor) + restore_privileges_table(cursor) + cursor.execute("PRAGMA foreign_keys=ON") + +steps = [ + step(change_schema, restore_schema) +] diff --git a/migrations/auth/20230207_01_r0bkZ-create-group-join-requests-table.py b/migrations/auth/20230207_01_r0bkZ-create-group-join-requests-table.py new file mode 100644 index 0000000..ceae5ea --- /dev/null +++ b/migrations/auth/20230207_01_r0bkZ-create-group-join-requests-table.py @@ -0,0 +1,29 @@ +""" +Create group_requests table +""" + +from yoyo import step + +__depends__ = {'20230116_01_KwuJ3-rework-privileges-schema'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS group_join_requests( + request_id TEXT NOT NULL, + group_id TEXT NOT NULL, + requester_id TEXT NOT NULL, + timestamp REAL NOT NULL, + status TEXT NOT NULL DEFAULT 'PENDING', + message TEXT, + PRIMARY KEY(request_id, group_id), + FOREIGN KEY(group_id) REFERENCES groups(group_id) + ON UPDATE CASCADE ON DELETE CASCADE, + FOREIGN KEY (requester_id) REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE CASCADE, + UNIQUE(group_id, requester_id), + CHECK (status IN ('PENDING', 'ACCEPTED', 'REJECTED')) + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS group_join_requests") +] diff --git a/migrations/auth/20230210_01_8xMa1-system-admin-privileges-for-data-distribution.py b/migrations/auth/20230210_01_8xMa1-system-admin-privileges-for-data-distribution.py new file mode 100644 index 0000000..8b406a6 --- /dev/null +++ b/migrations/auth/20230210_01_8xMa1-system-admin-privileges-for-data-distribution.py @@ -0,0 +1,22 @@ +""" +System admin privileges for data distribution + +These privileges are focussed on allowing the system administrator to link the +datasets and traits in the main database to specific groups in the auth system. +""" + +from yoyo import step + +__depends__ = {'20230207_01_r0bkZ-create-group-join-requests-table'} + +steps = [ + step( + """ + INSERT INTO privileges VALUES + ('system:data:link-to-group', 'Link a dataset or trait to a group.') + """, + """ + DELETE FROM privileges WHERE privilege_id IN + ('system:data:link-to-group') + """) +] diff --git a/migrations/auth/20230210_02_lDK14-create-system-admin-role.py b/migrations/auth/20230210_02_lDK14-create-system-admin-role.py new file mode 100644 index 0000000..9b3fc2b --- /dev/null +++ b/migrations/auth/20230210_02_lDK14-create-system-admin-role.py @@ -0,0 +1,38 @@ +""" +Create system-admin role +""" +import uuid +from contextlib import closing + +from yoyo import step + +__depends__ = {'20230210_01_8xMa1-system-admin-privileges-for-data-distribution'} + +def create_sys_admin_role(conn): + with closing(conn.cursor()) as cursor: + role_id = uuid.uuid4() + cursor.execute( + "INSERT INTO roles VALUES (?, 'system-administrator', '0')", + (str(role_id),)) + + cursor.executemany( + "INSERT INTO role_privileges VALUES (:role_id, :privilege_id)", + ({"role_id": f"{role_id}", "privilege_id": priv} + for priv in ( + "system:data:link-to-group", + "system:group:create-group", + "system:group:delete-group", + "system:group:edit-group", + "system:group:transfer-group-leader", + "system:group:view-group", + "system:user:assign-group-leader", + "system:user:delete-user", + "system:user:masquerade", + "system:user:reset-password"))) + +def drop_sys_admin_role(conn): + pass + +steps = [ + step(create_sys_admin_role, drop_sys_admin_role) +] diff --git a/migrations/auth/20230306_01_pRfxl-add-system-user-list-privilege.py b/migrations/auth/20230306_01_pRfxl-add-system-user-list-privilege.py new file mode 100644 index 0000000..84bbd49 --- /dev/null +++ b/migrations/auth/20230306_01_pRfxl-add-system-user-list-privilege.py @@ -0,0 +1,26 @@ +""" +Add system:user:list privilege +""" +import contextlib + +from yoyo import step + +__depends__ = {'20230210_02_lDK14-create-system-admin-role'} + +def insert_users_list_priv(conn): + """Create a new 'system:user:list' privilege.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute( + "INSERT INTO privileges(privilege_id, privilege_description) " + "VALUES('system:user:list', 'List users in the system') " + "ON CONFLICT (privilege_id) DO NOTHING") + +def delete_users_list_priv(conn): + """Delete the new 'system:user:list' privilege.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute( + "DELETE FROM privileges WHERE privilege_id='system:user:list'") + +steps = [ + step(insert_users_list_priv, delete_users_list_priv) +] diff --git a/migrations/auth/20230306_02_7GnRY-add-system-user-list-privilege-to-system-administrator-and-group-leader-roles.py b/migrations/auth/20230306_02_7GnRY-add-system-user-list-privilege-to-system-administrator-and-group-leader-roles.py new file mode 100644 index 0000000..3caad55 --- /dev/null +++ b/migrations/auth/20230306_02_7GnRY-add-system-user-list-privilege-to-system-administrator-and-group-leader-roles.py @@ -0,0 +1,42 @@ +""" +Add system:user:list privilege to system-administrator and group-leader roles. +""" +import uuid +import contextlib + +from yoyo import step + +__depends__ = {'20230306_01_pRfxl-add-system-user-list-privilege'} + +def role_ids(cursor): + """Get role ids from names""" + cursor.execute( + "SELECT * FROM roles WHERE role_name IN " + "('system-administrator', 'group-leader')") + return (uuid.UUID(row[0]) for row in cursor.fetchall()) + +def add_privilege_to_roles(conn): + """ + Add 'system:user:list' privilege to 'system-administrator' and + 'group-leader' roles.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.executemany( + "INSERT INTO role_privileges(role_id,privilege_id) " + "VALUES(?, ?)", + tuple((str(role_id), "system:user:list") + for role_id in role_ids(cursor))) + +def del_privilege_from_roles(conn): + """ + Delete 'system:user:list' privilege to 'system-administrator' and + 'group-leader' roles. + """ + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute( + "DELETE FROM role_privileges WHERE " + "role_id IN (?, ?) AND privilege_id='system:user:list'", + tuple(str(role_id) for role_id in role_ids(cursor))) + +steps = [ + step(add_privilege_to_roles, del_privilege_from_roles) +] diff --git a/migrations/auth/20230322_01_0dDZR-create-linked-phenotype-data-table.py b/migrations/auth/20230322_01_0dDZR-create-linked-phenotype-data-table.py new file mode 100644 index 0000000..647325f --- /dev/null +++ b/migrations/auth/20230322_01_0dDZR-create-linked-phenotype-data-table.py @@ -0,0 +1,30 @@ +""" +Create linked-phenotype-data table +""" + +from yoyo import step + +__depends__ = {'20230306_02_7GnRY-add-system-user-list-privilege-to-system-administrator-and-group-leader-roles'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS linked_phenotype_data + -- Link the data in MariaDB to user groups in the auth system + ( + data_link_id TEXT NOT NULL PRIMARY KEY, -- A new ID for the auth system + group_id TEXT NOT NULL, -- The user group the data is linked to + SpeciesId TEXT NOT NULL, -- The species in MariaDB + InbredSetId TEXT NOT NULL, -- The traits group in MariaDB + PublishFreezeId TEXT NOT NULL, -- The dataset Id in MariaDB + dataset_name TEXT, -- dataset Name in MariaDB + dataset_fullname, -- dataset FullName in MariaDB + dataset_shortname, -- dataset ShortName in MariaDB + PublishXRefId TEXT NOT NULL, -- The trait's ID in MariaDB + FOREIGN KEY (group_id) + REFERENCES groups(group_id) ON UPDATE CASCADE ON DELETE RESTRICT + UNIQUE (SpeciesId, InbredSetId, PublishFreezeId, PublishXRefId) + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS linked_phenotype_data") +] diff --git a/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py b/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py new file mode 100644 index 0000000..7c9e986 --- /dev/null +++ b/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py @@ -0,0 +1,29 @@ +""" +Create phenotype_resources table +""" + +from yoyo import step + +__depends__ = {'20230322_01_0dDZR-create-linked-phenotype-data-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS phenotype_resources + -- Link phenotype data to specific resources + ( + group_id TEXT NOT NULL, + resource_id TEXT NOT NULL, -- A resource can have multiple data items + data_link_id TEXT NOT NULL, + PRIMARY KEY(group_id, resource_id, data_link_id), + UNIQUE (data_link_id), -- ensure data is linked to only one resource + FOREIGN KEY (group_id, resource_id) + REFERENCES resources(group_id, resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (data_link_id) + REFERENCES linked_phenotype_data(data_link_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS phenotype_resources") +] diff --git a/migrations/auth/20230404_01_VKxXg-create-linked-genotype-data-table.py b/migrations/auth/20230404_01_VKxXg-create-linked-genotype-data-table.py new file mode 100644 index 0000000..02e8718 --- /dev/null +++ b/migrations/auth/20230404_01_VKxXg-create-linked-genotype-data-table.py @@ -0,0 +1,29 @@ +""" +Create linked genotype data table +""" + +from yoyo import step + +__depends__ = {'20230322_02_Ll854-create-phenotype-resources-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS linked_genotype_data + -- Link genotype data in MariaDB to user groups in auth system + ( + data_link_id TEXT NOT NULL PRIMARY KEY, -- A new ID for the auth system + group_id TEXT NOT NULL, -- The user group the data is linked to + SpeciesId TEXT NOT NULL, -- The species in MariaDB + InbredSetId TEXT NOT NULL, -- The traits group in MariaDB + GenoFreezeId TEXT NOT NULL, -- The dataset Id in MariaDB + dataset_name TEXT, -- dataset Name in MariaDB + dataset_fullname, -- dataset FullName in MariaDB + dataset_shortname, -- dataset ShortName in MariaDB + FOREIGN KEY (group_id) + REFERENCES groups(group_id) ON UPDATE CASCADE ON DELETE RESTRICT + UNIQUE (SpeciesId, InbredSetId, GenoFreezeId) + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS linked_genotype_data") +] diff --git a/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py b/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py new file mode 100644 index 0000000..1a865e0 --- /dev/null +++ b/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py @@ -0,0 +1,29 @@ +""" +Create genotype resources table +""" + +from yoyo import step + +__depends__ = {'20230404_01_VKxXg-create-linked-genotype-data-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS genotype_resources + -- Link genotype data to specific resource + ( + group_id TEXT NOT NULL, + resource_id TEXT NOT NULL, -- A resource can have multiple items + data_link_id TEXT NOT NULL, + PRIMARY KEY (group_id, resource_id, data_link_id), + UNIQUE (data_link_id) -- ensure data is linked to single resource + FOREIGN KEY (group_id, resource_id) + REFERENCES resources(group_id, resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (data_link_id) + REFERENCES linked_genotype_data(data_link_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS genotype_resources") +] diff --git a/migrations/auth/20230410_01_8mwaf-create-linked-mrna-data-table.py b/migrations/auth/20230410_01_8mwaf-create-linked-mrna-data-table.py new file mode 100644 index 0000000..db9a6bf --- /dev/null +++ b/migrations/auth/20230410_01_8mwaf-create-linked-mrna-data-table.py @@ -0,0 +1,30 @@ +""" +Create linked mrna data table +""" + +from yoyo import step + +__depends__ = {'20230404_02_la33P-create-genotype-resources-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS linked_mrna_data + -- Link mRNA Assay data in MariaDB to user groups in auth system + ( + data_link_id TEXT NOT NULL PRIMARY KEY, -- A new ID for the auth system + group_id TEXT NOT NULL, -- The user group the data is linked to + SpeciesId TEXT NOT NULL, -- The species in MariaDB + InbredSetId TEXT NOT NULL, -- The traits group in MariaDB + ProbeFreezeId TEXT NOT NULL, -- The study ID in MariaDB + ProbeSetFreezeId TEXT NOT NULL, -- The dataset Id in MariaDB + dataset_name TEXT, -- dataset Name in MariaDB + dataset_fullname, -- dataset FullName in MariaDB + dataset_shortname, -- dataset ShortName in MariaDB + FOREIGN KEY (group_id) + REFERENCES groups(group_id) ON UPDATE CASCADE ON DELETE RESTRICT + UNIQUE (SpeciesId, InbredSetId, ProbeFreezeId, ProbeSetFreezeId) + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS linked_mrna_data") +] diff --git a/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py b/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py new file mode 100644 index 0000000..2ad1056 --- /dev/null +++ b/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py @@ -0,0 +1,28 @@ +""" +Create mRNA resources table +""" + +from yoyo import step + +__depends__ = {'20230410_01_8mwaf-create-linked-mrna-data-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS mrna_resources + -- Link mRNA data to specific resource + ( + group_id TEXT NOT NULL, + resource_id TEXT NOT NULL, -- A resource can have multiple items + data_link_id TEXT NOT NULL, + PRIMARY KEY (resource_id, data_link_id), + UNIQUE (data_link_id) -- ensure data is linked to single resource + FOREIGN KEY (group_id, resource_id) + REFERENCES resources(group_id, resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (data_link_id) REFERENCES linked_mrna_data(data_link_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS mrna_resources") +] |