Mercurial > libervia-backend

comparison sat/memory/migration/versions/602caf848068_drop_message_types_table_fix_nullable.py @ 3583:16ade4ad63f3 sqlalchemy

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
core (memory/sqla_mapping): fix some technical debt: a first Alembic revision file is joined to migrate existing databases to new mapping. This patch: - set `nullable=False` in various places - drop legacy `message_types` table, and use `Enum` instead - use naming convention, and get rid of anonymous constraint (this is the main reason why the revision file is so long)
author Goffi <goffi@goffi.org>
date Sun, 27 Jun 2021 00:15:40 +0200
parents
children
comparison
equal deleted inserted replaced
3582:71516731d0aa 3583:16ade4ad63f3
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)