Mercurial > libervia-backend
diff sat/memory/sqla_mapping.py @ 3583:16ade4ad63f3 sqlalchemy
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 |
line wrap: on
line diff
--- a/sat/memory/sqla_mapping.py Fri Jun 25 17:55:23 2021 +0200 +++ b/sat/memory/sqla_mapping.py Sun Jun 27 00:15:40 2021 +0200 @@ -19,7 +19,7 @@ import pickle import json from sqlalchemy import ( - Column, Integer, Text, Float, Enum, ForeignKey, UniqueConstraint, Index, + MetaData, Column, Integer, Text, Float, Enum, ForeignKey, UniqueConstraint, Index, ) from sqlalchemy.orm import declarative_base, relationship @@ -28,7 +28,17 @@ from datetime import datetime -Base = declarative_base() +Base = declarative_base( + metadata=MetaData( + naming_convention={ + "ix": 'ix_%(column_0_label)s', + "uq": "uq_%(table_name)s_%(column_0_name)s", + "ck": "ck_%(table_name)s_%(constraint_name)s", + "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", + "pk": "pk_%(table_name)s" + } + ) +) # keys which are in message data extra but not stored in extra field this is # because those values are stored in separate fields NOT_IN_EXTRA = ('stanza_id', 'received_timestamp', 'update_uid') @@ -137,12 +147,6 @@ profile = relationship("Profile") -class MessageType(Base): - __tablename__ = "message_types" - - type = Column(Text, primary_key=True, nullable=True) - - class History(Base): __tablename__ = "history" __table_args__ = ( @@ -153,7 +157,7 @@ ) ) - uid = Column(Text, primary_key=True, nullable=True) + uid = Column(Text, primary_key=True) stanza_id = Column(Text) update_uid = Column(Text) profile_id = Column(ForeignKey("profiles.id", ondelete="CASCADE")) @@ -163,11 +167,24 @@ dest_res = Column(Text) timestamp = Column(Float, nullable=False) received_timestamp = Column(Float) - type = Column(ForeignKey("message_types.type")) + type = Column( + Enum( + "chat", + "error", + "groupchat", + "headline", + "normal", + # info is not XMPP standard, but used to keep track of info like join/leave + # in a MUC + "info", + name="message_type", + create_constraint=True, + ), + nullable=False, + ) extra = Column(LegacyPickle) profile = relationship("Profile") - message_type = relationship("MessageType") messages = relationship("Message", backref="history", passive_deletes=True) subjects = relationship("Subject", backref="history", passive_deletes=True) thread = relationship( @@ -268,7 +285,6 @@ id = Column( Integer, primary_key=True, - nullable=True, # cf. note for Profile.id autoincrement=False ) @@ -292,7 +308,6 @@ id = Column( Integer, primary_key=True, - nullable=True, # cf. note for Profile.id autoincrement=False, ) @@ -316,7 +331,6 @@ id = Column( Integer, primary_key=True, - nullable=True, # cf. note for Profile.id autoincrement=False, ) @@ -333,18 +347,18 @@ class ParamGen(Base): __tablename__ = "param_gen" - category = Column(Text, primary_key=True, nullable=True) - name = Column(Text, primary_key=True, nullable=True) + category = Column(Text, primary_key=True) + name = Column(Text, primary_key=True) value = Column(Text) class ParamInd(Base): __tablename__ = "param_ind" - category = Column(Text, primary_key=True, nullable=True) - name = Column(Text, primary_key=True, nullable=True) + category = Column(Text, primary_key=True) + name = Column(Text, primary_key=True) profile_id = Column( - ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True, nullable=True + ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True ) value = Column(Text) @@ -354,18 +368,18 @@ class PrivateGen(Base): __tablename__ = "private_gen" - namespace = Column(Text, primary_key=True, nullable=True) - key = Column(Text, primary_key=True, nullable=True) + namespace = Column(Text, primary_key=True) + key = Column(Text, primary_key=True) value = Column(Text) class PrivateInd(Base): __tablename__ = "private_ind" - namespace = Column(Text, primary_key=True, nullable=True) - key = Column(Text, primary_key=True, nullable=True) + namespace = Column(Text, primary_key=True) + key = Column(Text, primary_key=True) profile_id = Column( - ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True, nullable=True + ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True ) value = Column(Text) @@ -375,18 +389,18 @@ class PrivateGenBin(Base): __tablename__ = "private_gen_bin" - namespace = Column(Text, primary_key=True, nullable=True) - key = Column(Text, primary_key=True, nullable=True) + namespace = Column(Text, primary_key=True) + key = Column(Text, primary_key=True) value = Column(LegacyPickle) class PrivateIndBin(Base): __tablename__ = "private_ind_bin" - namespace = Column(Text, primary_key=True, nullable=True) - key = Column(Text, primary_key=True, nullable=True) + namespace = Column(Text, primary_key=True) + key = Column(Text, primary_key=True) profile_id = Column( - ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True, nullable=True + ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True ) value = Column(LegacyPickle) @@ -406,15 +420,18 @@ ) ) - id = Column(Text, primary_key=True, nullable=False) + id = Column(Text, primary_key=True) public_id = Column(Text, unique=True) - version = Column(Text, primary_key=True, nullable=False) + version = Column(Text, primary_key=True) parent = Column(Text, nullable=False) type = Column( - Enum("file", "directory", create_constraint=True), + Enum( + "file", "directory", + name="file_type", + create_constraint=True + ), nullable=False, server_default="file", - # name="file_type", ) file_hash = Column(Text) hash_algo = Column(Text)