aboutsummaryrefslogtreecommitdiff
path: root/migrations
diff options
context:
space:
mode:
Diffstat (limited to 'migrations')
-rw-r--r--migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py5
-rw-r--r--migrations/auth/20230404_02_la33P-create-genotype-resources-table.py3
-rw-r--r--migrations/auth/20230410_01_8mwaf-create-linked-mrna-data-table.py30
-rw-r--r--migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py24
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")
+]