Mercurial > libervia-backend
comparison libervia/backend/memory/migration/versions/602caf848068_drop_message_types_table_fix_nullable.py @ 4071:4b842c1fb686
refactoring: renamed `sat` package to `libervia.backend`
author | Goffi <goffi@goffi.org> |
---|---|
date | Fri, 02 Jun 2023 11:49:51 +0200 |
parents | sat/memory/migration/versions/602caf848068_drop_message_types_table_fix_nullable.py@16ade4ad63f3 |
children | 0d7bb4df2343 |
comparison
equal
deleted
inserted
replaced
4070:d10748475025 | 4071:4b842c1fb686 |
---|---|
1 """drop message_types table + fix nullable + rename constraints | |
2 | |
3 Revision ID: 602caf848068 | |
4 Revises: | |
5 Create Date: 2021-06-26 12:42:54.148313 | |
6 | |
7 """ | |
8 from alembic import op | |
9 from sqlalchemy import ( | |
10 Table, | |
11 Column, | |
12 MetaData, | |
13 TEXT, | |
14 INTEGER, | |
15 Text, | |
16 Integer, | |
17 Float, | |
18 Enum, | |
19 ForeignKey, | |
20 Index, | |
21 PrimaryKeyConstraint, | |
22 ) | |
23 from sqlalchemy.sql import table, column | |
24 | |
25 | |
26 # revision identifiers, used by Alembic. | |
27 revision = "602caf848068" | |
28 down_revision = None | |
29 branch_labels = None | |
30 depends_on = None | |
31 | |
32 | |
33 def upgrade(): | |
34 # we have to recreate former tables for batch_alter_table's reflexion, otherwise the | |
35 # database will be used, and this will keep unammed UNIQUE constraints in addition | |
36 # to the named ones that we create | |
37 metadata = MetaData( | |
38 naming_convention={ | |
39 "ix": "ix_%(column_0_label)s", | |
40 "uq": "uq_%(table_name)s_%(column_0_name)s", | |
41 "ck": "ck_%(table_name)s_%(constraint_name)s", | |
42 "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", | |
43 "pk": "pk_%(table_name)s", | |
44 }, | |
45 ) | |
46 | |
47 old_profiles_table = Table( | |
48 "profiles", | |
49 metadata, | |
50 Column("id", Integer, primary_key=True, nullable=True, autoincrement=False), | |
51 Column("name", Text, unique=True), | |
52 ) | |
53 | |
54 old_components_table = Table( | |
55 "components", | |
56 metadata, | |
57 Column( | |
58 "profile_id", | |
59 ForeignKey("profiles.id", ondelete="CASCADE"), | |
60 nullable=True, | |
61 primary_key=True, | |
62 ), | |
63 Column("entry_point", Text, nullable=False), | |
64 ) | |
65 | |
66 old_message_table = Table( | |
67 "message", | |
68 metadata, | |
69 Column("id", Integer, primary_key=True, nullable=True, autoincrement=False), | |
70 Column("history_uid", ForeignKey("history.uid", ondelete="CASCADE")), | |
71 Column("message", Text), | |
72 Column("language", Text), | |
73 Index("message__history_uid", "history_uid"), | |
74 ) | |
75 | |
76 old_subject_table = Table( | |
77 "subject", | |
78 metadata, | |
79 Column("id", Integer, primary_key=True, nullable=True, autoincrement=False), | |
80 Column("history_uid", ForeignKey("history.uid", ondelete="CASCADE")), | |
81 Column("subject", Text), | |
82 Column("language", Text), | |
83 Index("subject__history_uid", "history_uid"), | |
84 ) | |
85 | |
86 old_thread_table = Table( | |
87 "thread", | |
88 metadata, | |
89 Column("id", Integer, primary_key=True, nullable=True, autoincrement=False), | |
90 Column("history_uid", ForeignKey("history.uid", ondelete="CASCADE")), | |
91 Column("thread_id", Text), | |
92 Column("parent_id", Text), | |
93 Index("thread__history_uid", "history_uid"), | |
94 ) | |
95 | |
96 old_history_table = Table( | |
97 "history", | |
98 metadata, | |
99 Column("uid", Text, primary_key=True, nullable=True), | |
100 Column("stanza_id", Text), | |
101 Column("update_uid", Text), | |
102 Column("profile_id", Integer, ForeignKey("profiles.id", ondelete="CASCADE")), | |
103 Column("source", Text), | |
104 Column("dest", Text), | |
105 Column("source_res", Text), | |
106 Column("dest_res", Text), | |
107 Column("timestamp", Float, nullable=False), | |
108 Column("received_timestamp", Float), | |
109 Column("type", Text, ForeignKey("message_types.type")), | |
110 Column("extra", Text), | |
111 Index("history__profile_id_timestamp", "profile_id", "timestamp"), | |
112 Index( | |
113 "history__profile_id_received_timestamp", "profile_id", "received_timestamp" | |
114 ), | |
115 ) | |
116 | |
117 old_param_gen_table = Table( | |
118 "param_gen", | |
119 metadata, | |
120 Column("category", Text, primary_key=True), | |
121 Column("name", Text, primary_key=True), | |
122 Column("value", Text), | |
123 ) | |
124 | |
125 old_param_ind_table = Table( | |
126 "param_ind", | |
127 metadata, | |
128 Column("category", Text, primary_key=True), | |
129 Column("name", Text, primary_key=True), | |
130 Column( | |
131 "profile_id", ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True | |
132 ), | |
133 Column("value", Text), | |
134 ) | |
135 | |
136 old_private_gen_table = Table( | |
137 "private_gen", | |
138 metadata, | |
139 Column("namespace", Text, primary_key=True), | |
140 Column("key", Text, primary_key=True), | |
141 Column("value", Text), | |
142 ) | |
143 | |
144 old_private_ind_table = Table( | |
145 "private_ind", | |
146 metadata, | |
147 Column("namespace", Text, primary_key=True), | |
148 Column("key", Text, primary_key=True), | |
149 Column( | |
150 "profile_id", ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True | |
151 ), | |
152 Column("value", Text), | |
153 ) | |
154 | |
155 old_private_gen_bin_table = Table( | |
156 "private_gen_bin", | |
157 metadata, | |
158 Column("namespace", Text, primary_key=True), | |
159 Column("key", Text, primary_key=True), | |
160 Column("value", Text), | |
161 ) | |
162 | |
163 old_private_ind_bin_table = Table( | |
164 "private_ind_bin", | |
165 metadata, | |
166 Column("namespace", Text, primary_key=True), | |
167 Column("key", Text, primary_key=True), | |
168 Column( | |
169 "profile_id", ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True | |
170 ), | |
171 Column("value", Text), | |
172 ) | |
173 | |
174 old_files_table = Table( | |
175 "files", | |
176 metadata, | |
177 Column("id", Text, primary_key=True), | |
178 Column("public_id", Text, unique=True), | |
179 Column("version", Text, primary_key=True), | |
180 Column("parent", Text, nullable=False), | |
181 Column( | |
182 "type", | |
183 Enum("file", "directory", name="file_type", create_constraint=True), | |
184 nullable=False, | |
185 server_default="file", | |
186 ), | |
187 Column("file_hash", Text), | |
188 Column("hash_algo", Text), | |
189 Column("name", Text, nullable=False), | |
190 Column("size", Integer), | |
191 Column("namespace", Text), | |
192 Column("media_type", Text), | |
193 Column("media_subtype", Text), | |
194 Column("created", Float, nullable=False), | |
195 Column("modified", Float), | |
196 Column("owner", Text), | |
197 Column("access", Text), | |
198 Column("extra", Text), | |
199 Column("profile_id", ForeignKey("profiles.id", ondelete="CASCADE")), | |
200 Index("files__profile_id_owner_parent", "profile_id", "owner", "parent"), | |
201 Index( | |
202 "files__profile_id_owner_media_type_media_subtype", | |
203 "profile_id", | |
204 "owner", | |
205 "media_type", | |
206 "media_subtype", | |
207 ), | |
208 ) | |
209 | |
210 op.drop_table("message_types") | |
211 | |
212 with op.batch_alter_table( | |
213 "profiles", copy_from=old_profiles_table, schema=None | |
214 ) as batch_op: | |
215 batch_op.create_unique_constraint(batch_op.f("uq_profiles_name"), ["name"]) | |
216 | |
217 with op.batch_alter_table( | |
218 "components", | |
219 copy_from=old_components_table, | |
220 naming_convention={ | |
221 "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", | |
222 }, | |
223 schema=None, | |
224 ) as batch_op: | |
225 batch_op.create_unique_constraint(batch_op.f("uq_profiles_name"), ["name"]) | |
226 | |
227 with op.batch_alter_table( | |
228 "history", | |
229 copy_from=old_history_table, | |
230 naming_convention={ | |
231 "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", | |
232 }, | |
233 schema=None, | |
234 ) as batch_op: | |
235 batch_op.alter_column("uid", existing_type=TEXT(), nullable=False) | |
236 batch_op.alter_column( | |
237 "type", | |
238 type_=Enum( | |
239 "chat", | |
240 "error", | |
241 "groupchat", | |
242 "headline", | |
243 "normal", | |
244 "info", | |
245 name="message_type", | |
246 create_constraint=True, | |
247 ), | |
248 existing_type=TEXT(), | |
249 nullable=False, | |
250 ) | |
251 batch_op.create_unique_constraint( | |
252 batch_op.f("uq_history_profile_id"), | |
253 ["profile_id", "stanza_id", "source", "dest"], | |
254 ) | |
255 batch_op.drop_constraint("fk_history_type_message_types", type_="foreignkey") | |
256 | |
257 with op.batch_alter_table( | |
258 "message", copy_from=old_message_table, schema=None | |
259 ) as batch_op: | |
260 batch_op.alter_column( | |
261 "id", existing_type=INTEGER(), nullable=False, autoincrement=False | |
262 ) | |
263 | |
264 with op.batch_alter_table( | |
265 "subject", copy_from=old_subject_table, schema=None | |
266 ) as batch_op: | |
267 batch_op.alter_column( | |
268 "id", existing_type=INTEGER(), nullable=False, autoincrement=False | |
269 ) | |
270 | |
271 with op.batch_alter_table( | |
272 "thread", copy_from=old_thread_table, schema=None | |
273 ) as batch_op: | |
274 batch_op.alter_column( | |
275 "id", existing_type=INTEGER(), nullable=False, autoincrement=False | |
276 ) | |
277 | |
278 with op.batch_alter_table( | |
279 "param_gen", copy_from=old_param_gen_table, schema=None | |
280 ) as batch_op: | |
281 batch_op.alter_column("category", existing_type=TEXT(), nullable=False) | |
282 batch_op.alter_column("name", existing_type=TEXT(), nullable=False) | |
283 | |
284 with op.batch_alter_table( | |
285 "param_ind", copy_from=old_param_ind_table, schema=None | |
286 ) as batch_op: | |
287 batch_op.alter_column("category", existing_type=TEXT(), nullable=False) | |
288 batch_op.alter_column("name", existing_type=TEXT(), nullable=False) | |
289 batch_op.alter_column("profile_id", existing_type=INTEGER(), nullable=False) | |
290 | |
291 with op.batch_alter_table( | |
292 "private_gen", copy_from=old_private_gen_table, schema=None | |
293 ) as batch_op: | |
294 batch_op.alter_column("namespace", existing_type=TEXT(), nullable=False) | |
295 batch_op.alter_column("key", existing_type=TEXT(), nullable=False) | |
296 | |
297 with op.batch_alter_table( | |
298 "private_ind", copy_from=old_private_ind_table, schema=None | |
299 ) as batch_op: | |
300 batch_op.alter_column("namespace", existing_type=TEXT(), nullable=False) | |
301 batch_op.alter_column("key", existing_type=TEXT(), nullable=False) | |
302 batch_op.alter_column("profile_id", existing_type=INTEGER(), nullable=False) | |
303 | |
304 with op.batch_alter_table( | |
305 "private_gen_bin", copy_from=old_private_gen_bin_table, schema=None | |
306 ) as batch_op: | |
307 batch_op.alter_column("namespace", existing_type=TEXT(), nullable=False) | |
308 batch_op.alter_column("key", existing_type=TEXT(), nullable=False) | |
309 | |
310 # found some invalid rows in local database, maybe old values made during development, | |
311 # but in doubt we have to delete them | |
312 op.execute("DELETE FROM private_ind_bin WHERE namespace IS NULL") | |
313 | |
314 with op.batch_alter_table( | |
315 "private_ind_bin", copy_from=old_private_ind_bin_table, schema=None | |
316 ) as batch_op: | |
317 batch_op.alter_column("namespace", existing_type=TEXT(), nullable=False) | |
318 batch_op.alter_column("key", existing_type=TEXT(), nullable=False) | |
319 batch_op.alter_column("profile_id", existing_type=INTEGER(), nullable=False) | |
320 | |
321 with op.batch_alter_table( | |
322 "files", copy_from=old_files_table, schema=None | |
323 ) as batch_op: | |
324 batch_op.create_unique_constraint(batch_op.f("uq_files_public_id"), ["public_id"]) | |
325 batch_op.alter_column( | |
326 "type", | |
327 type_=Enum("file", "directory", name="file_type", create_constraint=True), | |
328 existing_type=Text(), | |
329 nullable=False, | |
330 ) | |
331 | |
332 | |
333 def downgrade(): | |
334 # downgrade doesn't restore the exact same state as before upgrade, as it | |
335 # would be useless and waste of resource to restore broken things such as | |
336 # anonymous constraints | |
337 with op.batch_alter_table("thread", schema=None) as batch_op: | |
338 batch_op.alter_column( | |
339 "id", existing_type=INTEGER(), nullable=True, autoincrement=False | |
340 ) | |
341 | |
342 with op.batch_alter_table("subject", schema=None) as batch_op: | |
343 batch_op.alter_column( | |
344 "id", existing_type=INTEGER(), nullable=True, autoincrement=False | |
345 ) | |
346 | |
347 with op.batch_alter_table("private_ind_bin", schema=None) as batch_op: | |
348 batch_op.alter_column("profile_id", existing_type=INTEGER(), nullable=True) | |
349 batch_op.alter_column("key", existing_type=TEXT(), nullable=True) | |
350 batch_op.alter_column("namespace", existing_type=TEXT(), nullable=True) | |
351 | |
352 with op.batch_alter_table("private_ind", schema=None) as batch_op: | |
353 batch_op.alter_column("profile_id", existing_type=INTEGER(), nullable=True) | |
354 batch_op.alter_column("key", existing_type=TEXT(), nullable=True) | |
355 batch_op.alter_column("namespace", existing_type=TEXT(), nullable=True) | |
356 | |
357 with op.batch_alter_table("private_gen_bin", schema=None) as batch_op: | |
358 batch_op.alter_column("key", existing_type=TEXT(), nullable=True) | |
359 batch_op.alter_column("namespace", existing_type=TEXT(), nullable=True) | |
360 | |
361 with op.batch_alter_table("private_gen", schema=None) as batch_op: | |
362 batch_op.alter_column("key", existing_type=TEXT(), nullable=True) | |
363 batch_op.alter_column("namespace", existing_type=TEXT(), nullable=True) | |
364 | |
365 with op.batch_alter_table("param_ind", schema=None) as batch_op: | |
366 batch_op.alter_column("profile_id", existing_type=INTEGER(), nullable=True) | |
367 batch_op.alter_column("name", existing_type=TEXT(), nullable=True) | |
368 batch_op.alter_column("category", existing_type=TEXT(), nullable=True) | |
369 | |
370 with op.batch_alter_table("param_gen", schema=None) as batch_op: | |
371 batch_op.alter_column("name", existing_type=TEXT(), nullable=True) | |
372 batch_op.alter_column("category", existing_type=TEXT(), nullable=True) | |
373 | |
374 with op.batch_alter_table("message", schema=None) as batch_op: | |
375 batch_op.alter_column( | |
376 "id", existing_type=INTEGER(), nullable=True, autoincrement=False | |
377 ) | |
378 | |
379 op.create_table( | |
380 "message_types", | |
381 Column("type", TEXT(), nullable=True), | |
382 PrimaryKeyConstraint("type"), | |
383 ) | |
384 message_types_table = table("message_types", column("type", TEXT())) | |
385 op.bulk_insert( | |
386 message_types_table, | |
387 [ | |
388 {"type": "chat"}, | |
389 {"type": "error"}, | |
390 {"type": "groupchat"}, | |
391 {"type": "headline"}, | |
392 {"type": "normal"}, | |
393 {"type": "info"}, | |
394 ], | |
395 ) | |
396 | |
397 with op.batch_alter_table("history", schema=None) as batch_op: | |
398 batch_op.alter_column( | |
399 "type", | |
400 type_=TEXT(), | |
401 existing_type=TEXT(), | |
402 nullable=True, | |
403 ) | |
404 batch_op.create_foreign_key( | |
405 batch_op.f("fk_history_type_message_types"), | |
406 "message_types", | |
407 ["type"], | |
408 ["type"], | |
409 ) | |
410 batch_op.alter_column("uid", existing_type=TEXT(), nullable=True) |