diff options
Diffstat (limited to 'migrations/auth')
5 files changed, 15 insertions, 4 deletions
diff --git a/migrations/auth/20221110_01_WtZ1I-create-resources-table.py b/migrations/auth/20221110_01_WtZ1I-create-resources-table.py index f99d482..09ed891 100644 --- a/migrations/auth/20221110_01_WtZ1I-create-resources-table.py +++ b/migrations/auth/20221110_01_WtZ1I-create-resources-table.py @@ -11,9 +11,10 @@ steps = [ """ CREATE TABLE IF NOT EXISTS resources( group_id TEXT NOT NULL, - resource_id TEXT PRIMARY KEY, + resource_id TEXT NOT NULL, resource_name TEXT NOT NULL, resource_category_id TEXT NOT NULL, + PRIMARY KEY(group_id, resource_id), FOREIGN KEY(group_id) REFERENCES groups(group_id), FOREIGN KEY(resource_category_id) REFERENCES resource_categories(resource_category_id) ) WITHOUT ROWID diff --git a/migrations/auth/20221110_02_z1dWf-create-mrna-resources-table.py b/migrations/auth/20221110_02_z1dWf-create-mrna-resources-table.py index 0959116..d3f97ac 100644 --- a/migrations/auth/20221110_02_z1dWf-create-mrna-resources-table.py +++ b/migrations/auth/20221110_02_z1dWf-create-mrna-resources-table.py @@ -13,9 +13,12 @@ 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(resource_id) REFERENCES resources(resource_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 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 index d241f77..7dde67e 100644 --- a/migrations/auth/20221110_03_ka3W0-create-phenotype-resources-table.py +++ b/migrations/auth/20221110_03_ka3W0-create-phenotype-resources-table.py @@ -13,10 +13,13 @@ 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(resource_id) REFERENCES resources(resource_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 index 9200774..ed6e87d 100644 --- a/migrations/auth/20221110_04_6PRFQ-create-genotype-resources-table.py +++ b/migrations/auth/20221110_04_6PRFQ-create-genotype-resources-table.py @@ -13,10 +13,13 @@ 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(resource_id) REFERENCES resources(resource_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/20221117_01_RDlfx-modify-group-roles-add-group-role-id.py b/migrations/auth/20221117_01_RDlfx-modify-group-roles-add-group-role-id.py index 7115bc3..2deff10 100644 --- a/migrations/auth/20221117_01_RDlfx-modify-group-roles-add-group-role-id.py +++ b/migrations/auth/20221117_01_RDlfx-modify-group-roles-add-group-role-id.py @@ -33,6 +33,7 @@ steps = [ group_role_id TEXT PRIMARY KEY, group_id TEXT NOT NULL, role_id TEXT NOT NULL, + UNIQUE (group_id, role_id), FOREIGN KEY(group_id) REFERENCES groups(group_id), FOREIGN KEY(role_id) REFERENCES roles(role_id) ) WITHOUT ROWID |