Mercurial > libervia-backend

comparison sat/memory/sqla_mapping.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 84ea57a8d6b3
children cb8d0e8b917f
comparison
equal deleted inserted replaced
3582:71516731d0aa 3583:16ade4ad63f3
17 # along with this program. If not, see <http://www.gnu.org/licenses/>. 17 # along with this program. If not, see <http://www.gnu.org/licenses/>.
18 18
19 import pickle 19 import pickle
20 import json 20 import json
21 from sqlalchemy import ( 21 from sqlalchemy import (
22 Column, Integer, Text, Float, Enum, ForeignKey, UniqueConstraint, Index, 22 MetaData, Column, Integer, Text, Float, Enum, ForeignKey, UniqueConstraint, Index,
23 ) 23 )
24 24
25 from sqlalchemy.orm import declarative_base, relationship 25 from sqlalchemy.orm import declarative_base, relationship
26 from sqlalchemy.types import TypeDecorator 26 from sqlalchemy.types import TypeDecorator
27 from twisted.words.protocols.jabber import jid 27 from twisted.words.protocols.jabber import jid
28 from datetime import datetime 28 from datetime import datetime
29 29
30 30
31 Base = declarative_base() 31 Base = declarative_base(
32 metadata=MetaData(
33 naming_convention={
34 "ix": 'ix_%(column_0_label)s',
35 "uq": "uq_%(table_name)s_%(column_0_name)s",
36 "ck": "ck_%(table_name)s_%(constraint_name)s",
37 "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
38 "pk": "pk_%(table_name)s"
39 }
40 )
41 )
32 # keys which are in message data extra but not stored in extra field this is 42 # keys which are in message data extra but not stored in extra field this is
33 # because those values are stored in separate fields 43 # because those values are stored in separate fields
34 NOT_IN_EXTRA = ('stanza_id', 'received_timestamp', 'update_uid') 44 NOT_IN_EXTRA = ('stanza_id', 'received_timestamp', 'update_uid')
35 45
36 46
135 ) 145 )
136 entry_point = Column(Text, nullable=False) 146 entry_point = Column(Text, nullable=False)
137 profile = relationship("Profile") 147 profile = relationship("Profile")
138 148
139 149
140 class MessageType(Base):
141 __tablename__ = "message_types"
142
143 type = Column(Text, primary_key=True, nullable=True)
144
145
146 class History(Base): 150 class History(Base):
147 __tablename__ = "history" 151 __tablename__ = "history"
148 __table_args__ = ( 152 __table_args__ = (
149 UniqueConstraint("profile_id", "stanza_id", "source", "dest"), 153 UniqueConstraint("profile_id", "stanza_id", "source", "dest"),
150 Index("history__profile_id_timestamp", "profile_id", "timestamp"), 154 Index("history__profile_id_timestamp", "profile_id", "timestamp"),
151 Index( 155 Index(
152 "history__profile_id_received_timestamp", "profile_id", "received_timestamp" 156 "history__profile_id_received_timestamp", "profile_id", "received_timestamp"
153 ) 157 )
154 ) 158 )
155 159
156 uid = Column(Text, primary_key=True, nullable=True) 160 uid = Column(Text, primary_key=True)
157 stanza_id = Column(Text) 161 stanza_id = Column(Text)
158 update_uid = Column(Text) 162 update_uid = Column(Text)
159 profile_id = Column(ForeignKey("profiles.id", ondelete="CASCADE")) 163 profile_id = Column(ForeignKey("profiles.id", ondelete="CASCADE"))
160 source = Column(Text) 164 source = Column(Text)
161 dest = Column(Text) 165 dest = Column(Text)
162 source_res = Column(Text) 166 source_res = Column(Text)
163 dest_res = Column(Text) 167 dest_res = Column(Text)
164 timestamp = Column(Float, nullable=False) 168 timestamp = Column(Float, nullable=False)
165 received_timestamp = Column(Float) 169 received_timestamp = Column(Float)
166 type = Column(ForeignKey("message_types.type")) 170 type = Column(
171 Enum(
172 "chat",
173 "error",
174 "groupchat",
175 "headline",
176 "normal",
177 # info is not XMPP standard, but used to keep track of info like join/leave
178 # in a MUC
179 "info",
180 name="message_type",
181 create_constraint=True,
182 ),
183 nullable=False,
184 )
167 extra = Column(LegacyPickle) 185 extra = Column(LegacyPickle)
168 186
169 profile = relationship("Profile") 187 profile = relationship("Profile")
170 message_type = relationship("MessageType")
171 messages = relationship("Message", backref="history", passive_deletes=True) 188 messages = relationship("Message", backref="history", passive_deletes=True)
172 subjects = relationship("Subject", backref="history", passive_deletes=True) 189 subjects = relationship("Subject", backref="history", passive_deletes=True)
173 thread = relationship( 190 thread = relationship(
174 "Thread", uselist=False, back_populates="history", passive_deletes=True 191 "Thread", uselist=False, back_populates="history", passive_deletes=True
175 ) 192 )
266 ) 283 )
267 284
268 id = Column( 285 id = Column(
269 Integer, 286 Integer,
270 primary_key=True, 287 primary_key=True,
271 nullable=True,
272 # cf. note for Profile.id 288 # cf. note for Profile.id
273 autoincrement=False 289 autoincrement=False
274 ) 290 )
275 history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE")) 291 history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE"))
276 message = Column(Text) 292 message = Column(Text)
290 ) 306 )
291 307
292 id = Column( 308 id = Column(
293 Integer, 309 Integer,
294 primary_key=True, 310 primary_key=True,
295 nullable=True,
296 # cf. note for Profile.id 311 # cf. note for Profile.id
297 autoincrement=False, 312 autoincrement=False,
298 ) 313 )
299 history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE")) 314 history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE"))
300 subject = Column(Text) 315 subject = Column(Text)
314 ) 329 )
315 330
316 id = Column( 331 id = Column(
317 Integer, 332 Integer,
318 primary_key=True, 333 primary_key=True,
319 nullable=True,
320 # cf. note for Profile.id 334 # cf. note for Profile.id
321 autoincrement=False, 335 autoincrement=False,
322 ) 336 )
323 history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE")) 337 history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE"))
324 thread_id = Column(Text) 338 thread_id = Column(Text)
331 345
332 346
333 class ParamGen(Base): 347 class ParamGen(Base):
334 __tablename__ = "param_gen" 348 __tablename__ = "param_gen"
335 349
336 category = Column(Text, primary_key=True, nullable=True) 350 category = Column(Text, primary_key=True)
337 name = Column(Text, primary_key=True, nullable=True) 351 name = Column(Text, primary_key=True)
338 value = Column(Text) 352 value = Column(Text)
339 353
340 354
341 class ParamInd(Base): 355 class ParamInd(Base):
342 __tablename__ = "param_ind" 356 __tablename__ = "param_ind"
343 357
344 category = Column(Text, primary_key=True, nullable=True) 358 category = Column(Text, primary_key=True)
345 name = Column(Text, primary_key=True, nullable=True) 359 name = Column(Text, primary_key=True)
346 profile_id = Column( 360 profile_id = Column(
347 ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True, nullable=True 361 ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True
348 ) 362 )
349 value = Column(Text) 363 value = Column(Text)
350 364
351 profile = relationship("Profile", back_populates="params") 365 profile = relationship("Profile", back_populates="params")
352 366
353 367
354 class PrivateGen(Base): 368 class PrivateGen(Base):
355 __tablename__ = "private_gen" 369 __tablename__ = "private_gen"
356 370
357 namespace = Column(Text, primary_key=True, nullable=True) 371 namespace = Column(Text, primary_key=True)
358 key = Column(Text, primary_key=True, nullable=True) 372 key = Column(Text, primary_key=True)
359 value = Column(Text) 373 value = Column(Text)
360 374
361 375
362 class PrivateInd(Base): 376 class PrivateInd(Base):
363 __tablename__ = "private_ind" 377 __tablename__ = "private_ind"
364 378
365 namespace = Column(Text, primary_key=True, nullable=True) 379 namespace = Column(Text, primary_key=True)
366 key = Column(Text, primary_key=True, nullable=True) 380 key = Column(Text, primary_key=True)
367 profile_id = Column( 381 profile_id = Column(
368 ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True, nullable=True 382 ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True
369 ) 383 )
370 value = Column(Text) 384 value = Column(Text)
371 385
372 profile = relationship("Profile", back_populates="private_data") 386 profile = relationship("Profile", back_populates="private_data")
373 387
374 388
375 class PrivateGenBin(Base): 389 class PrivateGenBin(Base):
376 __tablename__ = "private_gen_bin" 390 __tablename__ = "private_gen_bin"
377 391
378 namespace = Column(Text, primary_key=True, nullable=True) 392 namespace = Column(Text, primary_key=True)
379 key = Column(Text, primary_key=True, nullable=True) 393 key = Column(Text, primary_key=True)
380 value = Column(LegacyPickle) 394 value = Column(LegacyPickle)
381 395
382 396
383 class PrivateIndBin(Base): 397 class PrivateIndBin(Base):
384 __tablename__ = "private_ind_bin" 398 __tablename__ = "private_ind_bin"
385 399
386 namespace = Column(Text, primary_key=True, nullable=True) 400 namespace = Column(Text, primary_key=True)
387 key = Column(Text, primary_key=True, nullable=True) 401 key = Column(Text, primary_key=True)
388 profile_id = Column( 402 profile_id = Column(
389 ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True, nullable=True 403 ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True
390 ) 404 )
391 value = Column(LegacyPickle) 405 value = Column(LegacyPickle)
392 406
393 profile = relationship("Profile", back_populates="private_bin_data") 407 profile = relationship("Profile", back_populates="private_bin_data")
394 408
404 "media_type", 418 "media_type",
405 "media_subtype" 419 "media_subtype"
406 ) 420 )
407 ) 421 )
408 422
409 id = Column(Text, primary_key=True, nullable=False) 423 id = Column(Text, primary_key=True)
410 public_id = Column(Text, unique=True) 424 public_id = Column(Text, unique=True)
411 version = Column(Text, primary_key=True, nullable=False) 425 version = Column(Text, primary_key=True)
412 parent = Column(Text, nullable=False) 426 parent = Column(Text, nullable=False)
413 type = Column( 427 type = Column(
414 Enum("file", "directory", create_constraint=True), 428 Enum(
429 "file", "directory",
430 name="file_type",
431 create_constraint=True
432 ),
415 nullable=False, 433 nullable=False,
416 server_default="file", 434 server_default="file",
417 # name="file_type",
418 ) 435 )
419 file_hash = Column(Text) 436 file_hash = Column(Text)
420 hash_algo = Column(Text) 437 hash_algo = Column(Text)
421 name = Column(Text, nullable=False) 438 name = Column(Text, nullable=False)
422 size = Column(Integer) 439 size = Column(Integer)