forked from mtusi2026/mtusi2026lab4
66 lines
2.8 KiB
Python
66 lines
2.8 KiB
Python
"""Private S3 attachment metadata and ordered message links."""
|
|
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
revision = "007"
|
|
down_revision = "006"
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def upgrade():
|
|
op.create_table(
|
|
"attachments",
|
|
sa.Column("id", sa.Uuid(), primary_key=True),
|
|
sa.Column("chat_id", sa.Uuid(), sa.ForeignKey("chats.id"), nullable=False),
|
|
sa.Column("owner_id", sa.Uuid(), sa.ForeignKey("users.id"), nullable=False),
|
|
sa.Column("filename", sa.String(255), nullable=False),
|
|
sa.Column("object_key", sa.Text(), nullable=False, unique=True),
|
|
sa.Column("content_type", sa.String(32), nullable=False),
|
|
sa.Column("size_bytes", sa.BigInteger(), nullable=False),
|
|
sa.Column("sha256", sa.String(64), nullable=False),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
|
|
sa.UniqueConstraint("chat_id", "id"),
|
|
sa.CheckConstraint("size_bytes BETWEEN 1 AND 10485760"),
|
|
sa.CheckConstraint("content_type IN ('image/png', 'image/jpeg')"),
|
|
)
|
|
op.create_index("attachments_chat_idx", "attachments", ["chat_id"])
|
|
op.create_unique_constraint("messages_chat_id_id_key", "messages", ["chat_id", "id"])
|
|
op.create_table(
|
|
"message_attachments",
|
|
sa.Column("message_id", sa.Uuid(), primary_key=True),
|
|
sa.Column("attachment_id", sa.Uuid(), primary_key=True),
|
|
sa.Column("chat_id", sa.Uuid(), nullable=False),
|
|
sa.Column("position", sa.Integer(), nullable=False),
|
|
sa.ForeignKeyConstraint(
|
|
["chat_id", "message_id"], ["messages.chat_id", "messages.id"], ondelete="CASCADE"
|
|
),
|
|
sa.ForeignKeyConstraint(
|
|
["chat_id", "attachment_id"], ["attachments.chat_id", "attachments.id"]
|
|
),
|
|
sa.UniqueConstraint("message_id", "position"),
|
|
sa.CheckConstraint("position BETWEEN 0 AND 9"),
|
|
)
|
|
op.add_column("message_requests", sa.Column(
|
|
"request_attachment_ids", sa.Text(), nullable=False, server_default=""
|
|
))
|
|
op.drop_constraint("messages_text_bytes_check", "messages", type_="check")
|
|
op.create_check_constraint(
|
|
"messages_text_bytes_check", "messages", "octet_length(text) BETWEEN 0 AND 16000"
|
|
)
|
|
|
|
|
|
def downgrade():
|
|
# Refuse downgrade if attachment-only messages cannot satisfy the old schema.
|
|
op.drop_constraint("messages_text_bytes_check", "messages", type_="check")
|
|
op.create_check_constraint(
|
|
"messages_text_bytes_check", "messages", "octet_length(text) BETWEEN 1 AND 16000"
|
|
)
|
|
op.drop_column("message_requests", "request_attachment_ids")
|
|
op.drop_table("message_attachments")
|
|
op.drop_constraint("messages_chat_id_id_key", "messages", type_="unique")
|
|
op.drop_index("attachments_chat_idx", "attachments")
|
|
op.drop_table("attachments")
|