auth: resources: Attach resource data and list unlinked data.

Load the data that is attached to a particular resource together with the
resource.

List any unlinked data: useful when linking data to resources.

2 files changed, 45 insertions, 22 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
index b54942c..890eeef 100644
--- a/migrations/auth/20230216_01_dgWjv-create-linked-group-data-table.py
+++ b/migrations/auth/20230216_01_dgWjv-create-linked-group-data-table.py
@@ -11,13 +11,15 @@ steps = [
         """
         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,
-          name TEXT NOT NULL,
-          type TEXT NOT NULL,
-          PRIMARY KEY(group_id, dataset_or_trait_id),
+          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 (type IN ('mRNA', 'Genotype', 'Phenotype'))
+          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
index d6dc4a9..b8a57fc 100644
--- 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
@@ -26,20 +26,25 @@ def add_foreign_key_to_mrna_resources(conn):
             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 (dataset_id)
-                REFERENCES linked_group_data(dataset_or_trait_id)
+              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 VALUES (?, ?, ?)",
-                           rows)
+        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):
@@ -61,8 +66,10 @@ def drop_foreign_key_from_mrna_resources(conn):
         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.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):
@@ -75,12 +82,15 @@ def add_foreign_key_to_geno_resources(conn):
             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 (trait_id)
-                REFERENCES linked_group_data(dataset_or_trait_id)
+              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
             """)
@@ -88,8 +98,10 @@ def add_foreign_key_to_geno_resources(conn):
             "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.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):
@@ -112,8 +124,10 @@ def drop_foreign_key_from_geno_resources(conn):
             "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.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):
@@ -126,12 +140,15 @@ def add_foreign_key_to_pheno_resources(conn):
             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 (trait_id)
-                REFERENCES linked_group_data(dataset_or_trait_id)
+              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
             """)
@@ -139,8 +156,10 @@ def add_foreign_key_to_pheno_resources(conn):
             "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.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):
@@ -163,8 +182,10 @@ def drop_foreign_key_from_pheno_resources(conn):
             "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.executemany(
+            "INSERT INTO phenotype_resources(group_id, resource_id, trait_id) "
+            "VALUES (?, ?, ?)",
+            rows)
         cursor.execute("DROP TABLE phenotype_resources_bkp")
 
 from yoyo import step