aboutsummaryrefslogtreecommitdiff
path: root/migrations
diff options
context:
space:
mode:
Diffstat (limited to 'migrations')
-rw-r--r--migrations/auth/20230216_01_dgWjv-create-linked-group-data-table.py24
-rw-r--r--migrations/auth/20230216_02_0ZHSl-make-dataset-id-and-trait-id-foreign-keys-in-tables.py181
2 files changed, 205 insertions, 0 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
new file mode 100644
index 0000000..b54942c
--- /dev/null
+++ b/migrations/auth/20230216_01_dgWjv-create-linked-group-data-table.py
@@ -0,0 +1,24 @@
+"""
+Create linked_group_data table
+"""
+
+from yoyo import step
+
+__depends__ = {'20230210_02_lDK14-create-system-admin-role'}
+
+steps = [
+ step(
+ """
+ CREATE TABLE IF NOT EXISTS linked_group_data(
+ group_id 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),
+ FOREIGN KEY (group_id) REFERENCES groups(group_id)
+ ON UPDATE CASCADE ON DELETE RESTRICT,
+ CHECK (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
new file mode 100644
index 0000000..d6dc4a9
--- /dev/null
+++ b/migrations/auth/20230216_02_0ZHSl-make-dataset-id-and-trait-id-foreign-keys-in-tables.py
@@ -0,0 +1,181 @@
+"""
+Make dataset_id and trait_id foreign keys in tables
+
+This migration makes dataset_id and trait_id columns FOREIGN KEYS in the tables:
+
+* mrna_resources
+* genotype_resources
+* phenotype_resources
+
+At this point, there really should be no data in the table, so it should not
+cause issues, but since this will be run by humans, there is a chance that
+unexpected actions might be taken, so this code takes a somewhat deliberate
+extra step to ensure the integrity of data is maintained.
+"""
+from contextlib import closing
+
+from yoyo import step
+
+def add_foreign_key_to_mrna_resources(conn):
+ """Make `dataset_id` a foreign key in mrna_resources."""
+ with closing(conn.cursor()) as cursor:
+ cursor.execute(
+ "ALTER TABLE mrna_resources RENAME TO mrna_resources_bkp")
+ cursor.execute(
+ """
+ CREATE TABLE IF NOT EXISTS mrna_resources(
+ group_id TEXT NOT NULL,
+ resource_id TEXT PRIMARY KEY,
+ 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)
+ 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.execute("DROP TABLE mrna_resources_bkp")
+
+def drop_foreign_key_from_mrna_resources(conn):
+ """Undo `add_foreign_key_to_mrna_resources` above."""
+ with closing(conn.cursor()) as cursor:
+ cursor.execute(
+ "ALTER TABLE mrna_resources RENAME TO mrna_resources_bkp")
+ cursor.execute(
+ """
+ CREATE TABLE IF NOT EXISTS mrna_resources(
+ group_id TEXT NOT NULL,
+ resource_id TEXT PRIMARY KEY,
+ dataset_id TEXT NOT NULL UNIQUE,
+ FOREIGN KEY(group_id, resource_id)
+ REFERENCES resources(group_id, resource_id)
+ ON UPDATE CASCADE ON DELETE RESTRICT
+ ) 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.execute("DROP TABLE mrna_resources_bkp")
+
+def add_foreign_key_to_geno_resources(conn):
+ """Make `trait_id` a foreign key in genotype_resources."""
+ with closing(conn.cursor()) as cursor:
+ cursor.execute(
+ "ALTER TABLE genotype_resources RENAME TO genotype_resources_bkp")
+ cursor.execute(
+ """
+ CREATE TABLE IF NOT EXISTS genotype_resources(
+ group_id TEXT NOT NULL,
+ resource_id TEXT PRIMARY KEY,
+ 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)
+ ON UPDATE CASCADE ON DELETE CASCADE
+ ) WITHOUT ROWID
+ """)
+ cursor.execute(
+ "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.execute("DROP TABLE genotype_resources_bkp")
+
+def drop_foreign_key_from_geno_resources(conn):
+ """Undo `add_foreign_key_to_geno_resources` above."""
+ with closing(conn.cursor()) as cursor:
+ cursor.execute(
+ "ALTER TABLE genotype_resources RENAME TO genotype_resources_bkp")
+ cursor.execute(
+ """
+ CREATE TABLE IF NOT EXISTS genotype_resources(
+ group_id TEXT NOT NULL,
+ resource_id TEXT PRIMARY KEY,
+ trait_id TEXT NOT NULL UNIQUE,
+ FOREIGN KEY(group_id, resource_id)
+ REFERENCES resources(group_id, resource_id)
+ ON UPDATE CASCADE ON DELETE RESTRICT
+ ) WITHOUT ROWID
+ """)
+ cursor.execute(
+ "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.execute("DROP TABLE genotype_resources_bkp")
+
+def add_foreign_key_to_pheno_resources(conn):
+ """Make `trait_id` a foreign key in phenotype_resources."""
+ with closing(conn.cursor()) as cursor:
+ cursor.execute(
+ "ALTER TABLE phenotype_resources RENAME TO phenotype_resources_bkp")
+ cursor.execute(
+ """
+ CREATE TABLE IF NOT EXISTS phenotype_resources(
+ group_id TEXT NOT NULL,
+ resource_id TEXT PRIMARY KEY,
+ 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)
+ ON UPDATE CASCADE ON DELETE CASCADE
+ ) WITHOUT ROWID
+ """)
+ cursor.execute(
+ "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.execute("DROP TABLE phenotype_resources_bkp")
+
+def drop_foreign_key_from_pheno_resources(conn):
+ """Undo `add_foreign_key_to_pheno_resources` above."""
+ with closing(conn.cursor()) as cursor:
+ cursor.execute(
+ "ALTER TABLE phenotype_resources RENAME TO phenotype_resources_bkp")
+ cursor.execute(
+ """
+ CREATE TABLE IF NOT EXISTS phenotype_resources(
+ group_id TEXT NOT NULL,
+ resource_id TEXT PRIMARY KEY,
+ trait_id TEXT NOT NULL UNIQUE,
+ FOREIGN KEY(group_id, resource_id)
+ REFERENCES resources(group_id, resource_id)
+ ON UPDATE CASCADE ON DELETE RESTRICT
+ ) WITHOUT ROWID
+ """)
+ cursor.execute(
+ "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.execute("DROP TABLE phenotype_resources_bkp")
+
+from yoyo import step
+
+__depends__ = {'20230216_01_dgWjv-create-linked-group-data-table'}
+
+steps = [
+ step(add_foreign_key_to_mrna_resources,
+ drop_foreign_key_from_mrna_resources),
+ step(add_foreign_key_to_geno_resources,
+ drop_foreign_key_from_geno_resources),
+ step(add_foreign_key_to_pheno_resources,
+ drop_foreign_key_from_pheno_resources)
+]
generated by cgit v1.2.3 (git 2.44.0) at 2024-11-22 06:55:30 +0000