aboutsummaryrefslogtreecommitdiff
path: root/migrations/auth
diff options
context:
space:
mode:
authorFrederick Muriuki Muriithi2023-08-04 10:10:28 +0300
committerFrederick Muriuki Muriithi2023-08-04 10:20:09 +0300
commit8b7c598407a5fea9a3d78473e72df87606998cd4 (patch)
tree8526433a17eca6b511feb082a0574f9b15cb9469 /migrations/auth
parentf7fcbbcc014686ac597b783a8dcb38b43024b9d6 (diff)
downloadgn-auth-8b7c598407a5fea9a3d78473e72df87606998cd4.tar.gz
Copy over files from GN3 repository.
Diffstat (limited to 'migrations/auth')
-rw-r--r--migrations/auth/20221103_01_js9ub-initialise-the-auth-entic-oris-ation-database.py19
-rw-r--r--migrations/auth/20221103_02_sGrIs-create-user-credentials-table.py20
-rw-r--r--migrations/auth/20221108_01_CoxYh-create-the-groups-table.py19
-rw-r--r--migrations/auth/20221108_02_wxTr9-create-privileges-table.py18
-rw-r--r--migrations/auth/20221108_03_Pbhb1-create-resource-categories-table.py19
-rw-r--r--migrations/auth/20221108_04_CKcSL-init-data-in-resource-categories-table.py25
-rw-r--r--migrations/auth/20221109_01_HbD5F-add-resource-meta-field-to-resource-categories-field.py17
-rw-r--r--migrations/auth/20221110_01_WtZ1I-create-resources-table.py26
-rw-r--r--migrations/auth/20221110_05_BaNtL-create-roles-table.py19
-rw-r--r--migrations/auth/20221110_06_Pq2kT-create-generic-roles-table.py24
-rw-r--r--migrations/auth/20221110_07_7WGa1-create-role-privileges-table.py29
-rw-r--r--migrations/auth/20221110_08_23psB-add-privilege-category-and-privilege-description-columns-to-privileges-table.py22
-rw-r--r--migrations/auth/20221113_01_7M0hv-enumerate-initial-privileges.py66
-rw-r--r--migrations/auth/20221114_01_n8gsF-create-generic-role-privileges-table.py35
-rw-r--r--migrations/auth/20221114_02_DKKjn-drop-generic-role-tables.py41
-rw-r--r--migrations/auth/20221114_03_PtWjc-create-group-roles-table.py29
-rw-r--r--migrations/auth/20221114_04_tLUzB-initialise-basic-roles.py56
-rw-r--r--migrations/auth/20221114_05_hQun6-create-user-roles-table.py29
-rw-r--r--migrations/auth/20221116_01_nKUmX-add-privileges-to-group-leader-role.py35
-rw-r--r--migrations/auth/20221117_01_RDlfx-modify-group-roles-add-group-role-id.py52
-rw-r--r--migrations/auth/20221117_02_fmuZh-create-group-users-table.py25
-rw-r--r--migrations/auth/20221206_01_BbeF9-create-group-user-roles-on-resources-table.py39
-rw-r--r--migrations/auth/20221208_01_sSdHz-add-public-column-to-resources-table.py16
-rw-r--r--migrations/auth/20221219_01_CI3tN-create-oauth2-clients-table.py25
-rw-r--r--migrations/auth/20221219_02_buSEU-create-oauth2-tokens-table.py31
-rw-r--r--migrations/auth/20221219_03_PcTrb-create-authorisation-code-table.py31
-rw-r--r--migrations/auth/20230111_01_Wd6IZ-remove-create-group-privilege-from-group-leader.py40
-rw-r--r--migrations/auth/20230116_01_KwuJ3-rework-privileges-schema.py111
-rw-r--r--migrations/auth/20230207_01_r0bkZ-create-group-join-requests-table.py29
-rw-r--r--migrations/auth/20230210_01_8xMa1-system-admin-privileges-for-data-distribution.py22
-rw-r--r--migrations/auth/20230210_02_lDK14-create-system-admin-role.py38
-rw-r--r--migrations/auth/20230306_01_pRfxl-add-system-user-list-privilege.py26
-rw-r--r--migrations/auth/20230306_02_7GnRY-add-system-user-list-privilege-to-system-administrator-and-group-leader-roles.py42
-rw-r--r--migrations/auth/20230322_01_0dDZR-create-linked-phenotype-data-table.py30
-rw-r--r--migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py29
-rw-r--r--migrations/auth/20230404_01_VKxXg-create-linked-genotype-data-table.py29
-rw-r--r--migrations/auth/20230404_02_la33P-create-genotype-resources-table.py29
-rw-r--r--migrations/auth/20230410_01_8mwaf-create-linked-mrna-data-table.py30
-rw-r--r--migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py28
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")
+]