# HG changeset patch # User Goffi # Date 1655468123 -7200 # Node ID 24c1c06c865b84996d4132b89acf8d8bd967652b # Parent 967a8e109cdaf292ae5b3369efe137077b9b6101 core (memory/mapping): add `origin_id` column to History + constraints update: - `origin_id` is added as a column instead of being just in extra, as it an important data to filter on. - Add some constraints. - Add `serialise` method to Message and Subject. diff -r 967a8e109cda -r 24c1c06c865b sat/memory/migration/versions/4b002773cf92_add_origin_id_column_to_history_and_.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sat/memory/migration/versions/4b002773cf92_add_origin_id_column_to_history_and_.py Fri Jun 17 14:15:23 2022 +0200 @@ -0,0 +1,60 @@ +"""add origin_id column to history and adapt constraints + +Revision ID: 4b002773cf92 +Revises: 79e5f3313fa4 +Create Date: 2022-06-13 16:10:39.711634 + +""" +from alembic import op +import sqlalchemy as sa + + +# revision identifiers, used by Alembic. +revision = '4b002773cf92' +down_revision = '79e5f3313fa4' +branch_labels = None +depends_on = None + + +def upgrade(): + with op.batch_alter_table('history', schema=None) as batch_op: + batch_op.add_column(sa.Column('origin_id', sa.Text(), nullable=True)) + batch_op.create_unique_constraint('uq_origin_id', ['profile_id', 'origin_id', 'source']) + + with op.batch_alter_table('message', schema=None) as batch_op: + batch_op.alter_column('history_uid', + existing_type=sa.TEXT(), + nullable=False) + batch_op.alter_column('message', + existing_type=sa.TEXT(), + nullable=False) + + with op.batch_alter_table('subject', schema=None) as batch_op: + batch_op.alter_column('history_uid', + existing_type=sa.TEXT(), + nullable=False) + batch_op.alter_column('subject', + existing_type=sa.TEXT(), + nullable=False) + + +def downgrade(): + with op.batch_alter_table('subject', schema=None) as batch_op: + batch_op.alter_column('subject', + existing_type=sa.TEXT(), + nullable=True) + batch_op.alter_column('history_uid', + existing_type=sa.TEXT(), + nullable=True) + + with op.batch_alter_table('message', schema=None) as batch_op: + batch_op.alter_column('message', + existing_type=sa.TEXT(), + nullable=True) + batch_op.alter_column('history_uid', + existing_type=sa.TEXT(), + nullable=True) + + with op.batch_alter_table('history', schema=None) as batch_op: + batch_op.drop_constraint('uq_origin_id', type_='unique') + batch_op.drop_column('origin_id') diff -r 967a8e109cda -r 24c1c06c865b sat/memory/sqla_mapping.py --- a/sat/memory/sqla_mapping.py Fri Jun 17 14:15:23 2022 +0200 +++ b/sat/memory/sqla_mapping.py Fri Jun 17 14:15:23 2022 +0200 @@ -16,19 +16,32 @@ # You should have received a copy of the GNU Affero General Public License # along with this program. If not, see . -import pickle -import json +from typing import Dict, Any from datetime import datetime +import enum +import json +import pickle import time -import enum + from sqlalchemy import ( - MetaData, Column, Integer, Text, Float, Boolean, DateTime, Enum, JSON, ForeignKey, - UniqueConstraint, Index, DDL, event + Boolean, + Column, + DDL, + DateTime, + Enum, + Float, + ForeignKey, + Index, + Integer, + JSON, + MetaData, + Text, + UniqueConstraint, + event, ) - from sqlalchemy.orm import declarative_base, relationship +from sqlalchemy.sql.functions import now from sqlalchemy.types import TypeDecorator -from sqlalchemy.sql.functions import now from twisted.words.protocols.jabber import jid from wokkel import generic @@ -46,7 +59,7 @@ ) # 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') +NOT_IN_EXTRA = ('origin_id', 'stanza_id', 'received_timestamp', 'update_uid') class SyncState(enum.Enum): @@ -184,6 +197,7 @@ __tablename__ = "history" __table_args__ = ( UniqueConstraint("profile_id", "stanza_id", "source", "dest"), + UniqueConstraint("profile_id", "origin_id", "source", name="uq_origin_id"), Index("history__profile_id_timestamp", "profile_id", "timestamp"), Index( "history__profile_id_received_timestamp", "profile_id", "received_timestamp" @@ -191,6 +205,7 @@ ) uid = Column(Text, primary_key=True) + origin_id = Column(Text) stanza_id = Column(Text) update_uid = Column(Text) profile_id = Column(ForeignKey("profiles.id", ondelete="CASCADE")) @@ -259,6 +274,8 @@ def serialise(self): extra = self.extra + if self.origin_id is not None: + extra["origin_id"] = self.origin_id if self.stanza_id is not None: extra["stanza_id"] = self.stanza_id if self.update_uid is not None: @@ -319,10 +336,18 @@ Integer, primary_key=True, ) - history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE")) - message = Column(Text) + history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE"), nullable=False) + message = Column(Text, nullable=False) language = Column(Text) + def serialise(self) -> Dict[str, Any]: + s = {} + if self.message: + s["message"] = str(self.message) + if self.language: + s["language"] = str(self.language) + return s + def __repr__(self): lang_str = f"[{self.language}]" if self.language else "" msg = f"{self.message[:20]}…" if len(self.message)>20 else self.message @@ -340,10 +365,18 @@ Integer, primary_key=True, ) - history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE")) - subject = Column(Text) + history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE"), nullable=False) + subject = Column(Text, nullable=False) language = Column(Text) + def serialise(self) -> Dict[str, Any]: + s = {} + if self.subject: + s["subject"] = str(self.subject) + if self.language: + s["language"] = str(self.language) + return s + def __repr__(self): lang_str = f"[{self.language}]" if self.language else "" msg = f"{self.subject[:20]}…" if len(self.subject)>20 else self.subject