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.

3 files changed, 207 insertions, 1 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")
+]
diff --git a/migrations/auth/20230216_02_0ZHSl-make-dataset-id-and-trait-id-foreign-keys-in-tables.py b/migrations/auth/20230216_02_0ZHSl-make-dataset-id-and-trait-id-foreign-keys-in-tables.py
new file mode 100644
index 0000000..d6dc4a9
--- /dev/null
+++ b/migrations/auth/20230216_02_0ZHSl-make-dataset-id-and-trait-id-foreign-keys-in-tables.py
@@ -0,0 +1,181 @@
+"""
+Make dataset_id and trait_id foreign keys in tables
+
+This migration makes dataset_id and trait_id columns FOREIGN KEYS in the tables:
+
+* mrna_resources
+* genotype_resources
+* phenotype_resources
+
+At this point, there really should be no data in the table, so it should not
+cause issues, but since this will be run by humans, there is a chance that
+unexpected actions might be taken, so this code takes a somewhat deliberate
+extra step to ensure the integrity of data is maintained.
+"""
+from contextlib import closing
+
+from yoyo import step
+
+def add_foreign_key_to_mrna_resources(conn):
+    """Make `dataset_id` a foreign key in mrna_resources."""
+    with closing(conn.cursor()) as cursor:
+        cursor.execute(
+            "ALTER TABLE mrna_resources RENAME TO mrna_resources_bkp")
+        cursor.execute(
+            """
+            CREATE TABLE IF NOT EXISTS mrna_resources(
+              group_id TEXT NOT NULL,
+              resource_id TEXT PRIMARY KEY,
+              dataset_id TEXT NOT NULL UNIQUE,
+              FOREIGN KEY(group_id, resource_id)
+                REFERENCES resources(group_id, resource_id)
+                ON UPDATE CASCADE ON DELETE RESTRICT,
+              FOREIGN KEY (dataset_id)
+                REFERENCES linked_group_data(dataset_or_trait_id)
+                ON UPDATE CASCADE ON DELETE CASCADE
+              ) WITHOUT ROWID
+            """)
+        cursor.execute(
+            "SELECT group_id, resource_id, dataset_id FROM mrna_resources_bkp")
+        rows = ((row[0], row[1], row[2]) for row in cursor.fetchall())
+        cursor.executemany("INSERT INTO mrna_resources VALUES (?, ?, ?)",
+                           rows)
+        cursor.execute("DROP TABLE mrna_resources_bkp")
+
+def drop_foreign_key_from_mrna_resources(conn):
+    """Undo `add_foreign_key_to_mrna_resources` above."""
+    with closing(conn.cursor()) as cursor:
+        cursor.execute(
+            "ALTER TABLE mrna_resources RENAME TO mrna_resources_bkp")
+        cursor.execute(
+        """
+        CREATE TABLE IF NOT EXISTS mrna_resources(
+          group_id TEXT NOT NULL,
+          resource_id TEXT PRIMARY KEY,
+          dataset_id TEXT NOT NULL UNIQUE,
+          FOREIGN KEY(group_id, resource_id)
+            REFERENCES resources(group_id, resource_id)
+            ON UPDATE CASCADE ON DELETE RESTRICT
+        ) WITHOUT ROWID
+        """)
+        cursor.execute(
+            "SELECT group_id, resource_id, dataset_id FROM mrna_resources_bkp")
+        rows = ((row[0], row[1], row[2]) for row in cursor.fetchall())
+        cursor.executemany("INSERT INTO mrna_resources VALUES (?, ?, ?)",
+                           rows)
+        cursor.execute("DROP TABLE mrna_resources_bkp")
+
+def add_foreign_key_to_geno_resources(conn):
+    """Make `trait_id` a foreign key in genotype_resources."""
+    with closing(conn.cursor()) as cursor:
+        cursor.execute(
+            "ALTER TABLE genotype_resources RENAME TO genotype_resources_bkp")
+        cursor.execute(
+            """
+            CREATE TABLE IF NOT EXISTS genotype_resources(
+              group_id TEXT NOT NULL,
+              resource_id TEXT PRIMARY KEY,
+              trait_id TEXT NOT NULL UNIQUE,
+              FOREIGN KEY(group_id, resource_id)
+                REFERENCES resources(group_id, resource_id)
+                ON UPDATE CASCADE ON DELETE RESTRICT,
+              FOREIGN KEY (trait_id)
+                REFERENCES linked_group_data(dataset_or_trait_id)
+                ON UPDATE CASCADE ON DELETE CASCADE
+              ) WITHOUT ROWID
+            """)
+        cursor.execute(
+            "SELECT group_id, resource_id, trait_id "
+            "FROM genotype_resources_bkp")
+        rows = ((row[0], row[1], row[2]) for row in cursor.fetchall())
+        cursor.executemany("INSERT INTO genotype_resources VALUES (?, ?, ?)",
+                           rows)
+        cursor.execute("DROP TABLE genotype_resources_bkp")
+
+def drop_foreign_key_from_geno_resources(conn):
+    """Undo `add_foreign_key_to_geno_resources` above."""
+    with closing(conn.cursor()) as cursor:
+        cursor.execute(
+            "ALTER TABLE genotype_resources RENAME TO genotype_resources_bkp")
+        cursor.execute(
+        """
+        CREATE TABLE IF NOT EXISTS genotype_resources(
+          group_id TEXT NOT NULL,
+          resource_id TEXT PRIMARY KEY,
+          trait_id TEXT NOT NULL UNIQUE,
+          FOREIGN KEY(group_id, resource_id)
+            REFERENCES resources(group_id, resource_id)
+            ON UPDATE CASCADE ON DELETE RESTRICT
+        ) WITHOUT ROWID
+        """)
+        cursor.execute(
+            "SELECT group_id, resource_id, trait_id "
+            "FROM genotype_resources_bkp")
+        rows = ((row[0], row[1], row[2]) for row in cursor.fetchall())
+        cursor.executemany("INSERT INTO genotype_resources VALUES (?, ?, ?)",
+                           rows)
+        cursor.execute("DROP TABLE genotype_resources_bkp")
+
+def add_foreign_key_to_pheno_resources(conn):
+    """Make `trait_id` a foreign key in phenotype_resources."""
+    with closing(conn.cursor()) as cursor:
+        cursor.execute(
+            "ALTER TABLE phenotype_resources RENAME TO phenotype_resources_bkp")
+        cursor.execute(
+            """
+            CREATE TABLE IF NOT EXISTS phenotype_resources(
+              group_id TEXT NOT NULL,
+              resource_id TEXT PRIMARY KEY,
+              trait_id TEXT NOT NULL UNIQUE,
+              FOREIGN KEY(group_id, resource_id)
+                REFERENCES resources(group_id, resource_id)
+                ON UPDATE CASCADE ON DELETE RESTRICT,
+              FOREIGN KEY (trait_id)
+                REFERENCES linked_group_data(dataset_or_trait_id)
+                ON UPDATE CASCADE ON DELETE CASCADE
+              ) WITHOUT ROWID
+            """)
+        cursor.execute(
+            "SELECT group_id, resource_id, trait_id "
+            "FROM phenotype_resources_bkp")
+        rows = ((row[0], row[1], row[2]) for row in cursor.fetchall())
+        cursor.executemany("INSERT INTO phenotype_resources VALUES (?, ?, ?)",
+                           rows)
+        cursor.execute("DROP TABLE phenotype_resources_bkp")
+
+def drop_foreign_key_from_pheno_resources(conn):
+    """Undo `add_foreign_key_to_pheno_resources` above."""
+    with closing(conn.cursor()) as cursor:
+        cursor.execute(
+            "ALTER TABLE phenotype_resources RENAME TO phenotype_resources_bkp")
+        cursor.execute(
+        """
+        CREATE TABLE IF NOT EXISTS phenotype_resources(
+          group_id TEXT NOT NULL,
+          resource_id TEXT PRIMARY KEY,
+          trait_id TEXT NOT NULL UNIQUE,
+          FOREIGN KEY(group_id, resource_id)
+            REFERENCES resources(group_id, resource_id)
+            ON UPDATE CASCADE ON DELETE RESTRICT
+        ) WITHOUT ROWID
+        """)
+        cursor.execute(
+            "SELECT group_id, resource_id, trait_id "
+            "FROM phenotype_resources_bkp")
+        rows = ((row[0], row[1], row[2]) for row in cursor.fetchall())
+        cursor.executemany("INSERT INTO phenotype_resources VALUES (?, ?, ?)",
+                           rows)
+        cursor.execute("DROP TABLE phenotype_resources_bkp")
+
+from yoyo import step
+
+__depends__ = {'20230216_01_dgWjv-create-linked-group-data-table'}
+
+steps = [
+    step(add_foreign_key_to_mrna_resources,
+         drop_foreign_key_from_mrna_resources),
+    step(add_foreign_key_to_geno_resources,
+         drop_foreign_key_from_geno_resources),
+    step(add_foreign_key_to_pheno_resources,
+         drop_foreign_key_from_pheno_resources)
+]
diff --git a/tests/unit/auth/test_migrations_create_tables.py b/tests/unit/auth/test_migrations_create_tables.py
index 98022eb..ef192f4 100644
--- a/tests/unit/auth/test_migrations_create_tables.py
+++ b/tests/unit/auth/test_migrations_create_tables.py
@@ -32,7 +32,8 @@ migrations_and_tables = (
     ("20221219_03_PcTrb-create-authorisation-code-table.py",
      "authorisation_code"),
     ("20230207_01_r0bkZ-create-group-join-requests-table.py",
-     "group_join_requests"))
+     "group_join_requests"),
+    ("20230216_01_dgWjv-create-linked-group-data-table.py", "linked_group_data"))
 
 @pytest.mark.unit_test
 @pytest.mark.parametrize("migration_file,the_table", migrations_and_tables)