auth: migrations: Link to data in main db

Provide the `linked_group_data` table to be used to link to data in the main
database.

Update the `mrna_resources`, `genotype_resources` and `phenotype_resources`
tables to rely on the `linked_group_data` table.

1 files changed, 24 insertions, 0 deletions

diff --git a/migrations/auth/20230216_01_dgWjv-create-linked-group-data-table.py b/migrations/auth/20230216_01_dgWjv-create-linked-group-data-table.py
new file mode 100644
index 0000000..b54942c
--- /dev/null
+++ b/migrations/auth/20230216_01_dgWjv-create-linked-group-data-table.py
@@ -0,0 +1,24 @@
+"""
+Create linked_group_data table
+"""
+
+from yoyo import step
+
+__depends__ = {'20230210_02_lDK14-create-system-admin-role'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS linked_group_data(
+          group_id TEXT NOT NULL,
+          dataset_or_trait_id TEXT NOT NULL,
+          name TEXT NOT NULL,
+          type TEXT NOT NULL,
+          PRIMARY KEY(group_id, dataset_or_trait_id),
+          FOREIGN KEY (group_id) REFERENCES groups(group_id)
+            ON UPDATE CASCADE ON DELETE RESTRICT,
+          CHECK (type IN ('mRNA', 'Genotype', 'Phenotype'))
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS linked_group_data")
+]