diff options
| author | Frederick Muriuki Muriithi | 2023-04-21 05:24:21 +0300 |
|---|---|---|
| committer | Frederick Muriuki Muriithi | 2023-04-21 05:24:21 +0300 |
| commit | 2f6f54e215d7618ab9efa4ed17e09c633db07eab (patch) | |
| tree | 1bb1eda425e276b6c4cdf9550572dd948b8d5443 /migrations | |
| parent | 95f067a542424b76022595a74d660a7e84158f38 (diff) | |
| download | genenetwork3-2f6f54e215d7618ab9efa4ed17e09c633db07eab.tar.gz | |
auth: Attach linked data to specific resources.
Diffstat (limited to 'migrations')
3 files changed, 14 insertions, 2 deletions
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 |