diff options
Diffstat (limited to 'migrations')
4 files changed, 61 insertions, 1 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 d0ff541..19ee885 100644 --- a/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py +++ b/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py @@ -15,7 +15,10 @@ steps = [ 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), - UNIQUE (data_link_id) -- ensure data is linked to only one resource + UNIQUE (data_link_id), -- ensure data is linked to only one resource + FOREIGN KEY (data_link_id) + REFERENCES linked_phenotype_data(data_link_id) + ON UPDATE CASCADE ON DELETE RESTRICT ) WITHOUT ROWID """, "DROP TABLE IF EXISTS phenotype_resources") 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 2d3a3bc..e967ce6 100644 --- a/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py +++ b/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py @@ -16,6 +16,9 @@ steps = [ 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 (data_link_id) + REFERENCES linked_genotype_data(data_link_id) + ON UPDATE CASCADE ON DELETE RESTRICT ) WITHOUT ROWID """, "DROP TABLE IF EXISTS genotype_resources") diff --git a/migrations/auth/20230410_01_8mwaf-create-linked-mrna-data-table.py b/migrations/auth/20230410_01_8mwaf-create-linked-mrna-data-table.py new file mode 100644 index 0000000..db9a6bf --- /dev/null +++ b/migrations/auth/20230410_01_8mwaf-create-linked-mrna-data-table.py @@ -0,0 +1,30 @@ +""" +Create linked mrna data table +""" + +from yoyo import step + +__depends__ = {'20230404_02_la33P-create-genotype-resources-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS linked_mrna_data + -- Link mRNA Assay data in MariaDB to user groups in auth system + ( + data_link_id TEXT NOT NULL PRIMARY KEY, -- A new ID for the auth system + group_id TEXT NOT NULL, -- The user group the data is linked to + SpeciesId TEXT NOT NULL, -- The species in MariaDB + InbredSetId TEXT NOT NULL, -- The traits group in MariaDB + ProbeFreezeId TEXT NOT NULL, -- The study ID in MariaDB + ProbeSetFreezeId TEXT NOT NULL, -- The dataset Id in MariaDB + dataset_name TEXT, -- dataset Name in MariaDB + dataset_fullname, -- dataset FullName in MariaDB + dataset_shortname, -- dataset ShortName in MariaDB + FOREIGN KEY (group_id) + REFERENCES groups(group_id) ON UPDATE CASCADE ON DELETE RESTRICT + UNIQUE (SpeciesId, InbredSetId, ProbeFreezeId, ProbeSetFreezeId) + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS linked_mrna_data") +] diff --git a/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py b/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py new file mode 100644 index 0000000..de348c0 --- /dev/null +++ b/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py @@ -0,0 +1,24 @@ +""" +Create mRNA resources table +""" + +from yoyo import step + +__depends__ = {'20230410_01_8mwaf-create-linked-mrna-data-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS mrna_resources + -- Link mRNA data to specific resource + ( + 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 (data_link_id) REFERENCES linked_mrna_data(data_link_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS mrna_resources") +] |