From 2f6f54e215d7618ab9efa4ed17e09c633db07eab Mon Sep 17 00:00:00 2001
From: Frederick Muriuki Muriithi
Date: Fri, 21 Apr 2023 05:24:21 +0300
Subject: auth: Attach linked data to specific resources.

---
 .../auth/20230322_02_Ll854-create-phenotype-resources-table.py      | 6 +++++-
 .../auth/20230404_02_la33P-create-genotype-resources-table.py       | 6 +++++-
 migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py    | 4 ++++
 3 files changed, 14 insertions(+), 2 deletions(-)

(limited to 'migrations')

diff --git a/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py b/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py
index 19ee885..7c9e986 100644
--- a/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py
+++ b/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py
@@ -12,10 +12,14 @@ steps = [
         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(resource_id, data_link_id),
+          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
diff --git a/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py b/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py
index e967ce6..1a865e0 100644
--- a/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py
+++ b/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py
@@ -12,10 +12,14 @@ steps = [
         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 (resource_id, data_link_id),
+          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
diff --git a/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py b/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py
index de348c0..2ad1056 100644
--- a/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py
+++ b/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py
@@ -12,10 +12,14 @@ steps = [
         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
-- 
cgit v1.2.3