diff options
3 files changed, 207 insertions, 1 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) +] diff --git a/tests/unit/auth/test_migrations_create_tables.py b/tests/unit/auth/test_migrations_create_tables.py index 98022eb..ef192f4 100644 --- a/tests/unit/auth/test_migrations_create_tables.py +++ b/tests/unit/auth/test_migrations_create_tables.py @@ -32,7 +32,8 @@ migrations_and_tables = ( ("20221219_03_PcTrb-create-authorisation-code-table.py", "authorisation_code"), ("20230207_01_r0bkZ-create-group-join-requests-table.py", - "group_join_requests")) + "group_join_requests"), + ("20230216_01_dgWjv-create-linked-group-data-table.py", "linked_group_data")) @pytest.mark.unit_test @pytest.mark.parametrize("migration_file,the_table", migrations_and_tables) |