auth: Remove the tables used for linking data to resources

The way data is linked to the resources needs to be reworked. This commit
removes all the existing migration scripts that created the tables formerly
used for linking data in preparation for reworking the system.

8 files changed, 3 insertions, 312 deletions

diff --git a/migrations/auth/20221110_02_z1dWf-create-mrna-resources-table.py b/migrations/auth/20221110_02_z1dWf-create-mrna-resources-table.py
deleted file mode 100644
index d3f97ac..0000000
--- a/migrations/auth/20221110_02_z1dWf-create-mrna-resources-table.py
+++ /dev/null
@@ -1,25 +0,0 @@
-"""
-Create 'mrna_resources' table
-
-NOTE: One "mRNA dataset" should only ever be linked to one and only one resource object.
-      One "resource object" should only ever be linked to one and only one "mRNA dataset".
-"""
-
-from yoyo import step
-
-__depends__ = {'20221110_01_WtZ1I-create-resources-table'}
-
-steps = [
-    step(
-        """
-        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
-        """,
-        "DROP TABLE IF EXISTS mrna_resources")
-]
diff --git a/migrations/auth/20221110_03_ka3W0-create-phenotype-resources-table.py b/migrations/auth/20221110_03_ka3W0-create-phenotype-resources-table.py
deleted file mode 100644
index 7dde67e..0000000
--- a/migrations/auth/20221110_03_ka3W0-create-phenotype-resources-table.py
+++ /dev/null
@@ -1,26 +0,0 @@
-"""
-Create 'phenotype_resources' table
-
-NOTE: A "phenotype resource" can only ever be linked to one and only one "resource object"
-      A "resource object" can be linked to one or more "phenotype resource".
-"""
-
-from yoyo import step
-
-__depends__ = {'20221110_02_z1dWf-create-mrna-resources-table'}
-
-steps = [
-    step(
-        """
-        CREATE TABLE IF NOT EXISTS phenotype_resources(
-            group_id TEXT NOT NULL,
-            resource_id TEXT NOT NULL,
-            trait_id TEXT NOT NULL UNIQUE,
-            PRIMARY KEY(resource_id, trait_id),
-            FOREIGN KEY(group_id, resource_id)
-              REFERENCES resources(group_id, resource_id)
-              ON UPDATE CASCADE ON DELETE RESTRICT
-        ) WITHOUT ROWID
-        """,
-        "DROP TABLE IF EXISTS phenotype_resources")
-]
diff --git a/migrations/auth/20221110_04_6PRFQ-create-genotype-resources-table.py b/migrations/auth/20221110_04_6PRFQ-create-genotype-resources-table.py
deleted file mode 100644
index ed6e87d..0000000
--- a/migrations/auth/20221110_04_6PRFQ-create-genotype-resources-table.py
+++ /dev/null
@@ -1,26 +0,0 @@
-"""
-Create 'genotype_resources' table
-
-NOTE: A "genotype resource" can only ever be linked to one and only one "resource object"
-      A "resource object" can be linked to one or more "genotype resource".
-"""
-
-from yoyo import step
-
-__depends__ = {'20221110_03_ka3W0-create-phenotype-resources-table'}
-
-steps = [
-    step(
-        """
-        CREATE TABLE IF NOT EXISTS genotype_resources(
-            group_id TEXT NOT NULL,
-            resource_id TEXT NOT NULL,
-            trait_id TEXT NOT NULL UNIQUE,
-            PRIMARY KEY(resource_id, trait_id),
-            FOREIGN KEY(group_id, resource_id)
-              REFERENCES resources(group_id, resource_id)
-              ON UPDATE CASCADE ON DELETE RESTRICT
-        ) WITHOUT ROWID
-        """,
-        "DROP TABLE IF EXISTS genotype_resources")
-]
diff --git a/migrations/auth/20221110_05_BaNtL-create-roles-table.py b/migrations/auth/20221110_05_BaNtL-create-roles-table.py
index 18499a6..51e19e8 100644
--- a/migrations/auth/20221110_05_BaNtL-create-roles-table.py
+++ b/migrations/auth/20221110_05_BaNtL-create-roles-table.py
@@ -4,7 +4,7 @@ Create 'roles' table
 
 from yoyo import step
 
-__depends__ = {'20221110_04_6PRFQ-create-genotype-resources-table'}
+__depends__ = {'20221110_01_WtZ1I-create-resources-table'}
 
 steps = [
     step(
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
deleted file mode 100644
index 890eeef..0000000
--- a/migrations/auth/20230216_01_dgWjv-create-linked-group-data-table.py
+++ /dev/null
@@ -1,26 +0,0 @@
-"""
-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_type TEXT NOT NULL,
-          dataset_or_trait_id TEXT NOT NULL,
-          dataset_name TEXT NOT NULL,
-          dataset_fullname TEXT NOT NULL,
-          accession_id TEXT DEFAULT NULL,
-          PRIMARY KEY(group_id, dataset_type, dataset_or_trait_id),
-          FOREIGN KEY (group_id) REFERENCES groups(group_id)
-            ON UPDATE CASCADE ON DELETE RESTRICT,
-          CHECK (dataset_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
deleted file mode 100644
index b8a57fc..0000000
--- a/migrations/auth/20230216_02_0ZHSl-make-dataset-id-and-trait-id-foreign-keys-in-tables.py
+++ /dev/null
@@ -1,202 +0,0 @@
-"""
-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_type TEXT NOT NULL DEFAULT "mRNA"
-                CHECK (dataset_type="mRNA"),
-              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 (group_id, dataset_type, dataset_id)
-                REFERENCES
-                  linked_group_data(group_id, dataset_type, 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(group_id, resource_id, dataset_id) "
-            "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(group_id, resource_id, dataset_id) "
-            "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,
-              dataset_type TEXT NOT NULL DEFAULT "Genotype"
-                CHECK (dataset_type="Genotype"),
-              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 (group_id, dataset_type, trait_id)
-                REFERENCES
-                  linked_group_data(group_id, dataset_type, 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(group_id, resource_id, trait_id) "
-            "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(group_id, resource_id, trait_id) "
-            "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,
-              dataset_type TEXT NOT NULL DEFAULT "Phenotype"
-                CHECK (dataset_type="Phenotype"),
-              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 (group_id, dataset_type, trait_id)
-                REFERENCES
-                  linked_group_data(group_id, dataset_type, 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(group_id, resource_id, trait_id) "
-            "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(group_id, resource_id, trait_id) "
-            "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/migrations/auth/20230306_01_pRfxl-add-system-user-list-privilege.py b/migrations/auth/20230306_01_pRfxl-add-system-user-list-privilege.py
index 17db17c..84bbd49 100644
--- a/migrations/auth/20230306_01_pRfxl-add-system-user-list-privilege.py
+++ b/migrations/auth/20230306_01_pRfxl-add-system-user-list-privilege.py
@@ -5,7 +5,7 @@ import contextlib
 
 from yoyo import step
 
-__depends__ = {'20230216_02_0ZHSl-make-dataset-id-and-trait-id-foreign-keys-in-tables'}
+__depends__ = {'20230210_02_lDK14-create-system-admin-role'}
 
 def insert_users_list_priv(conn):
     """Create a new 'system:user:list' privilege."""
diff --git a/tests/unit/auth/test_migrations_create_tables.py b/tests/unit/auth/test_migrations_create_tables.py
index ef192f4..65f3896 100644
--- a/tests/unit/auth/test_migrations_create_tables.py
+++ b/tests/unit/auth/test_migrations_create_tables.py
@@ -14,9 +14,6 @@ migrations_and_tables = (
     ("20221108_02_wxTr9-create-privileges-table.py", "privileges"),
     ("20221108_03_Pbhb1-create-resource-categories-table.py", "resource_categories"),
     ("20221110_01_WtZ1I-create-resources-table.py", "resources"),
-    ("20221110_02_z1dWf-create-mrna-resources-table.py", "mrna_resources"),
-    ("20221110_03_ka3W0-create-phenotype-resources-table.py", "phenotype_resources"),
-    ("20221110_04_6PRFQ-create-genotype-resources-table.py", "genotype_resources"),
     ("20221110_05_BaNtL-create-roles-table.py", "roles"),
     ("20221110_06_Pq2kT-create-generic-roles-table.py", "generic_roles"),
     ("20221110_07_7WGa1-create-role-privileges-table.py", "role_privileges"),
@@ -32,8 +29,7 @@ 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"),
-    ("20230216_01_dgWjv-create-linked-group-data-table.py", "linked_group_data"))
+     "group_join_requests"))
 
 @pytest.mark.unit_test
 @pytest.mark.parametrize("migration_file,the_table", migrations_and_tables)