diff --git a/migrations/versions/storage/9662e80c6ac2_initial_migration.py b/migrations/versions/storage/9662e80c6ac2_initial_migration.py index e63a8cf..f4e9c35 100644 --- a/migrations/versions/storage/9662e80c6ac2_initial_migration.py +++ b/migrations/versions/storage/9662e80c6ac2_initial_migration.py @@ -1,59 +1,56 @@ """initial_migration Revision ID: 9662e80c6ac2 Revises: Create Date: 2025-03-11 16:45:10.460029 """ import sqlalchemy as sa import sqlmodel from alembic import op # revision identifiers, used by Alembic. revision = '9662e80c6ac2' down_revision = None branch_labels = None depends_on = None def upgrade() -> None: # ### commands auto generated by Alembic - please adjust! ### op.create_table('watchers', sa.Column('id', sa.Integer(), nullable=False), sa.Column('presentity_uri', sqlmodel.sql.sqltypes.AutoString(length=255), nullable=False), sa.Column('watcher_username', sqlmodel.sql.sqltypes.AutoString(length=64), nullable=False), sa.Column('watcher_domain', sqlmodel.sql.sqltypes.AutoString(length=64), nullable=False), sa.Column('event', sqlmodel.sql.sqltypes.AutoString(length=64), nullable=False), sa.Column('status', sa.Integer(), nullable=False), sa.Column('reason', sqlmodel.sql.sqltypes.AutoString(length=64), nullable=True), sa.Column('inserted_time', sa.Integer(), nullable=False), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('presentity_uri', 'watcher_username', 'watcher_domain', 'event', name='watcher_idx') ) op.create_table('xcap', sa.Column('id', sa.Integer(), nullable=False), - sa.Column('subscriber_id', sa.Integer(), nullable=True), sa.Column('username', sqlmodel.sql.sqltypes.AutoString(length=64), nullable=False), sa.Column('domain', sqlmodel.sql.sqltypes.AutoString(length=64), nullable=False), sa.Column('doc', sa.LargeBinary(), nullable=False), sa.Column('doc_type', sa.Integer(), nullable=False), sa.Column('etag', sqlmodel.sql.sqltypes.AutoString(length=64), nullable=False), sa.Column('source', sa.Integer(), nullable=False), sa.Column('doc_uri', sqlmodel.sql.sqltypes.AutoString(length=255), nullable=False), sa.Column('port', sa.Integer(), nullable=False), - sa.ForeignKeyConstraint(['subscriber_id'], ['subscriber.id'], ondelete='CASCADE'), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('username', 'domain', 'doc_type', 'doc_uri', name='account_doc_type_idx') ) op.create_index('source_idx', 'xcap', ['source'], unique=False) - op.create_index('xcap_subscriber_id_exists', 'xcap', ['subscriber_id'], unique=False) # ### end Alembic commands ### def downgrade() -> None: # ### commands auto generated by Alembic - please adjust! ### op.drop_index('xcap_subscriber_id_exists', table_name='xcap') op.drop_index('source_idx', table_name='xcap') op.drop_table('xcap') op.drop_table('watchers') # ### end Alembic commands ### diff --git a/xcap/db/models/storage_db.py b/xcap/db/models/storage_db.py index 967cb6f..2c23612 100644 --- a/xcap/db/models/storage_db.py +++ b/xcap/db/models/storage_db.py @@ -1,49 +1,47 @@ from datetime import datetime from typing import Optional from sqlmodel import (Column, Field, ForeignKey, Index, Integer, Relationship, SQLModel, UniqueConstraint) from xcap.configuration import DatabaseConfig class XCAP(SQLModel, table=True): __tablename__ = DatabaseConfig.xcap_table __database__ = 'storage_db' id: Optional[int] = Field(default=None, primary_key=True) - subscriber_id: Optional[int] = Field(default=None, sa_column=Column(Integer, ForeignKey("subscriber.id", ondelete="CASCADE"))) username: str = Field(max_length=64) domain: str = Field(max_length=64) doc: bytes # Representing longblob as bytes doc_type: int etag: str = Field(max_length=64) source: int = Field(default=0) doc_uri: str = Field(max_length=255) port: int = Field(default=0) __table_args__ = ( UniqueConstraint("username", "domain", "doc_type", "doc_uri", name="account_doc_type_idx"), - Index("xcap_subscriber_id_exists", "subscriber_id"), Index("source_idx", "source"), ) # subscriber: Optional["Subscriber"] = Relationship(back_populates="none", cascade="all, delete-orphan") class Watcher(SQLModel, table=True): __tablename__ = 'watchers' __database__ = 'storage_db' id: int = Field(default=None, primary_key=True) presentity_uri: str = Field(max_length=255) watcher_username: str = Field(max_length=64) watcher_domain: str = Field(max_length=64) event: str = Field(default="presence", max_length=64) status: int reason: Optional[str] = Field(default=None, max_length=64) inserted_time: int # Unique constraint for multiple columns __table_args__ = ( UniqueConstraint('presentity_uri', 'watcher_username', 'watcher_domain', 'event', name='watcher_idx'), )