forked from mtusi2026/mtusi2026lab5
61 lines
2.5 KiB
Python
61 lines
2.5 KiB
Python
"""Пользователи, чаты, участники, сообщения и сессии."""
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
revision = '001'
|
|
down_revision = None
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def upgrade():
|
|
op.create_table(
|
|
'users',
|
|
sa.Column('id', sa.Uuid(), primary_key=True),
|
|
sa.Column('username', sa.String(32), nullable=False, unique=True),
|
|
sa.Column('display_name', sa.String(100), nullable=False),
|
|
sa.Column('password_hash', sa.Text(), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.CheckConstraint("username ~ '^[a-z0-9_]{3,32}$'"),
|
|
sa.CheckConstraint('length(trim(display_name)) > 0'),
|
|
)
|
|
op.create_table(
|
|
'chats',
|
|
sa.Column('id', sa.Uuid(), primary_key=True),
|
|
sa.Column('title', sa.String(100), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.CheckConstraint('length(trim(title)) > 0'),
|
|
)
|
|
op.create_table(
|
|
'chat_members',
|
|
sa.Column('chat_id', sa.Uuid(), sa.ForeignKey('chats.id', ondelete='CASCADE'), primary_key=True),
|
|
sa.Column('user_id', sa.Uuid(), sa.ForeignKey('users.id'), primary_key=True),
|
|
)
|
|
op.create_index('chat_members_user_idx', 'chat_members', ['user_id', 'chat_id'])
|
|
op.create_table(
|
|
'messages',
|
|
sa.Column('id', sa.Uuid(), primary_key=True),
|
|
sa.Column('chat_id', sa.Uuid(), sa.ForeignKey('chats.id'), nullable=False),
|
|
sa.Column('sender_id', sa.Uuid(), sa.ForeignKey('users.id'), nullable=False),
|
|
sa.Column('text', sa.String(4000), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.ForeignKeyConstraint(['chat_id', 'sender_id'], ['chat_members.chat_id', 'chat_members.user_id']),
|
|
sa.CheckConstraint('length(trim(text)) > 0'),
|
|
)
|
|
op.create_index('messages_history_idx', 'messages', ['chat_id', 'created_at', 'id'])
|
|
op.create_table(
|
|
'sessions',
|
|
sa.Column('token_hash', sa.Text(), primary_key=True),
|
|
sa.Column('user_id', sa.Uuid(), sa.ForeignKey('users.id', ondelete='CASCADE'), nullable=False),
|
|
sa.Column('expires_at', sa.DateTime(timezone=True), nullable=False),
|
|
)
|
|
op.create_index('sessions_expiry_idx', 'sessions', ['expires_at'])
|
|
|
|
|
|
def downgrade():
|
|
op.drop_table('sessions')
|
|
op.drop_table('messages')
|
|
op.drop_table('chat_members')
|
|
op.drop_table('chats')
|
|
op.drop_table('users')
|