diff options
8 files changed, 3 insertions, 312 deletions
diff --git a/migrations/auth/20221110_02_z1dWf-create-mrna-resources-table.py b/migrations/auth/20221110_02_z1dWf-create-mrna-resources-table.py deleted file mode 100644 index d3f97ac..0000000 --- a/migrations/auth/20221110_02_z1dWf-create-mrna-resources-table.py +++ /dev/null @@ -1,25 +0,0 @@ -""" -Create 'mrna_resources' table - -NOTE: One "mRNA dataset" should only ever be linked to one and only one resource object. - One "resource object" should only ever be linked to one and only one "mRNA dataset". -""" - -from yoyo import step - -__depends__ = {'20221110_01_WtZ1I-create-resources-table'} - -steps = [ - step( - """ - 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 - """, - "DROP TABLE IF EXISTS mrna_resources") -] diff --git a/migrations/auth/20221110_03_ka3W0-create-phenotype-resources-table.py b/migrations/auth/20221110_03_ka3W0-create-phenotype-resources-table.py deleted file mode 100644 index 7dde67e..0000000 --- a/migrations/auth/20221110_03_ka3W0-create-phenotype-resources-table.py +++ /dev/null @@ -1,26 +0,0 @@ -""" -Create 'phenotype_resources' table - -NOTE: A "phenotype resource" can only ever be linked to one and only one "resource object" - A "resource object" can be linked to one or more "phenotype resource". -""" - -from yoyo import step - -__depends__ = {'20221110_02_z1dWf-create-mrna-resources-table'} - -steps = [ - step( - """ - CREATE TABLE IF NOT EXISTS phenotype_resources( - group_id TEXT NOT NULL, - resource_id TEXT NOT NULL, - trait_id TEXT NOT NULL UNIQUE, - PRIMARY KEY(resource_id, trait_id), - FOREIGN KEY(group_id, resource_id) - REFERENCES resources(group_id, resource_id) - ON UPDATE CASCADE ON DELETE RESTRICT - ) WITHOUT ROWID - """, - "DROP TABLE IF EXISTS phenotype_resources") -] diff --git a/migrations/auth/20221110_04_6PRFQ-create-genotype-resources-table.py b/migrations/auth/20221110_04_6PRFQ-create-genotype-resources-table.py deleted file mode 100644 index ed6e87d..0000000 --- a/migrations/auth/20221110_04_6PRFQ-create-genotype-resources-table.py +++ /dev/null @@ -1,26 +0,0 @@ -""" -Create 'genotype_resources' table - -NOTE: A "genotype resource" can only ever be linked to one and only one "resource object" - A "resource object" can be linked to one or more "genotype resource". -""" - -from yoyo import step - -__depends__ = {'20221110_03_ka3W0-create-phenotype-resources-table'} - -steps = [ - step( - """ - CREATE TABLE IF NOT EXISTS genotype_resources( - group_id TEXT NOT NULL, - resource_id TEXT NOT NULL, - trait_id TEXT NOT NULL UNIQUE, - PRIMARY KEY(resource_id, trait_id), - FOREIGN KEY(group_id, resource_id) - REFERENCES resources(group_id, resource_id) - ON UPDATE CASCADE ON DELETE RESTRICT - ) WITHOUT ROWID - """, - "DROP TABLE IF EXISTS genotype_resources") -] diff --git a/migrations/auth/20221110_05_BaNtL-create-roles-table.py b/migrations/auth/20221110_05_BaNtL-create-roles-table.py index 18499a6..51e19e8 100644 --- a/migrations/auth/20221110_05_BaNtL-create-roles-table.py +++ b/migrations/auth/20221110_05_BaNtL-create-roles-table.py @@ -4,7 +4,7 @@ Create 'roles' table from yoyo import step -__depends__ = {'20221110_04_6PRFQ-create-genotype-resources-table'} +__depends__ = {'20221110_01_WtZ1I-create-resources-table'} steps = [ step( 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 deleted file mode 100644 index 890eeef..0000000 --- a/migrations/auth/20230216_01_dgWjv-create-linked-group-data-table.py +++ /dev/null @@ -1,26 +0,0 @@ -""" -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_type TEXT NOT NULL, - dataset_or_trait_id TEXT NOT NULL, - dataset_name TEXT NOT NULL, - dataset_fullname TEXT NOT NULL, - accession_id TEXT DEFAULT NULL, - PRIMARY KEY(group_id, dataset_type, dataset_or_trait_id), - FOREIGN KEY (group_id) REFERENCES groups(group_id) - ON UPDATE CASCADE ON DELETE RESTRICT, - CHECK (dataset_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 deleted file mode 100644 index b8a57fc..0000000 --- a/migrations/auth/20230216_02_0ZHSl-make-dataset-id-and-trait-id-foreign-keys-in-tables.py +++ /dev/null @@ -1,202 +0,0 @@ -""" -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_type TEXT NOT NULL DEFAULT "mRNA" - CHECK (dataset_type="mRNA"), - 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 (group_id, dataset_type, dataset_id) - REFERENCES - linked_group_data(group_id, dataset_type, 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(group_id, resource_id, dataset_id) " - "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(group_id, resource_id, dataset_id) " - "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, - dataset_type TEXT NOT NULL DEFAULT "Genotype" - CHECK (dataset_type="Genotype"), - 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 (group_id, dataset_type, trait_id) - REFERENCES - linked_group_data(group_id, dataset_type, 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(group_id, resource_id, trait_id) " - "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(group_id, resource_id, trait_id) " - "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, - dataset_type TEXT NOT NULL DEFAULT "Phenotype" - CHECK (dataset_type="Phenotype"), - 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 (group_id, dataset_type, trait_id) - REFERENCES - linked_group_data(group_id, dataset_type, 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(group_id, resource_id, trait_id) " - "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(group_id, resource_id, trait_id) " - "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/migrations/auth/20230306_01_pRfxl-add-system-user-list-privilege.py b/migrations/auth/20230306_01_pRfxl-add-system-user-list-privilege.py index 17db17c..84bbd49 100644 --- a/migrations/auth/20230306_01_pRfxl-add-system-user-list-privilege.py +++ b/migrations/auth/20230306_01_pRfxl-add-system-user-list-privilege.py @@ -5,7 +5,7 @@ import contextlib from yoyo import step -__depends__ = {'20230216_02_0ZHSl-make-dataset-id-and-trait-id-foreign-keys-in-tables'} +__depends__ = {'20230210_02_lDK14-create-system-admin-role'} def insert_users_list_priv(conn): """Create a new 'system:user:list' privilege.""" diff --git a/tests/unit/auth/test_migrations_create_tables.py b/tests/unit/auth/test_migrations_create_tables.py index ef192f4..65f3896 100644 --- a/tests/unit/auth/test_migrations_create_tables.py +++ b/tests/unit/auth/test_migrations_create_tables.py @@ -14,9 +14,6 @@ migrations_and_tables = ( ("20221108_02_wxTr9-create-privileges-table.py", "privileges"), ("20221108_03_Pbhb1-create-resource-categories-table.py", "resource_categories"), ("20221110_01_WtZ1I-create-resources-table.py", "resources"), - ("20221110_02_z1dWf-create-mrna-resources-table.py", "mrna_resources"), - ("20221110_03_ka3W0-create-phenotype-resources-table.py", "phenotype_resources"), - ("20221110_04_6PRFQ-create-genotype-resources-table.py", "genotype_resources"), ("20221110_05_BaNtL-create-roles-table.py", "roles"), ("20221110_06_Pq2kT-create-generic-roles-table.py", "generic_roles"), ("20221110_07_7WGa1-create-role-privileges-table.py", "role_privileges"), @@ -32,8 +29,7 @@ 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"), - ("20230216_01_dgWjv-create-linked-group-data-table.py", "linked_group_data")) + "group_join_requests")) @pytest.mark.unit_test @pytest.mark.parametrize("migration_file,the_table", migrations_and_tables) |