1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
|
"""
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)
]
|
