187 lines
9.4 KiB
Python
187 lines
9.4 KiB
Python
"""Initial TryMe schema
|
|
|
|
Revision ID: 0001_initial_schema
|
|
Revises:
|
|
Create Date: 2026-11-11 13:45:00
|
|
|
|
"""
|
|
from collections.abc import Sequence
|
|
|
|
import sqlalchemy as sa
|
|
from alembic import op
|
|
|
|
revision: str = "0001_initial_schema"
|
|
down_revision: str | None = None
|
|
branch_labels: str | Sequence[str] | None = None
|
|
depends_on: str | Sequence[str] | None = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
op.create_table(
|
|
"users",
|
|
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
|
|
sa.Column("auth_sub", sa.String(length=190), nullable=False),
|
|
sa.Column("email", sa.String(length=255), nullable=True),
|
|
sa.Column("nickname", sa.String(length=48), nullable=False),
|
|
sa.Column("first_name", sa.String(length=80), nullable=False, server_default=""),
|
|
sa.Column("last_name", sa.String(length=80), nullable=False, server_default=""),
|
|
sa.Column("age", sa.Integer(), nullable=True),
|
|
sa.Column(
|
|
"gender", sa.String(length=40), nullable=False, server_default="prefer_not_to_say"
|
|
),
|
|
sa.Column("gender_custom", sa.String(length=80), nullable=True),
|
|
sa.Column("avatar_animal", sa.String(length=32), nullable=False, server_default="fox"),
|
|
sa.Column("avatar_color", sa.String(length=32), nullable=False, server_default="avatar-1"),
|
|
sa.Column(
|
|
"public_website_consent", sa.Boolean(), nullable=False, server_default=sa.text("false")
|
|
),
|
|
sa.Column("consent_at", sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column("onboarded", sa.Boolean(), nullable=False, server_default=sa.text("false")),
|
|
sa.Column("is_admin", sa.Boolean(), nullable=False, server_default=sa.text("false")),
|
|
sa.Column("is_banned", sa.Boolean(), nullable=False, server_default=sa.text("false")),
|
|
sa.Column("ban_reason", sa.String(length=255), nullable=True),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
|
|
sa.PrimaryKeyConstraint("id"),
|
|
sa.UniqueConstraint("auth_sub"),
|
|
sa.UniqueConstraint("nickname"),
|
|
)
|
|
op.create_index(op.f("ix_users_auth_sub"), "users", ["auth_sub"], unique=True)
|
|
op.create_index(op.f("ix_users_email"), "users", ["email"], unique=False)
|
|
op.create_index(op.f("ix_users_nickname"), "users", ["nickname"], unique=True)
|
|
|
|
op.create_table(
|
|
"api_keys",
|
|
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
|
|
sa.Column("user_id", sa.Integer(), nullable=False),
|
|
sa.Column("name", sa.String(length=80), nullable=False),
|
|
sa.Column("key_prefix", sa.String(length=24), nullable=False),
|
|
sa.Column("key_hash", sa.String(length=64), nullable=False),
|
|
sa.Column("expires_at", sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column("revoked_at", sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column("last_used_at", sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
|
|
sa.ForeignKeyConstraint(["user_id"], ["users.id"], ondelete="CASCADE"),
|
|
sa.PrimaryKeyConstraint("id"),
|
|
sa.UniqueConstraint("key_hash"),
|
|
)
|
|
op.create_index(op.f("ix_api_keys_key_hash"), "api_keys", ["key_hash"], unique=True)
|
|
op.create_index(op.f("ix_api_keys_user_id"), "api_keys", ["user_id"], unique=False)
|
|
|
|
op.create_table(
|
|
"quizzes",
|
|
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
|
|
sa.Column("share_slug", sa.String(length=40), nullable=False),
|
|
sa.Column("author_id", sa.Integer(), nullable=False),
|
|
sa.Column("title", sa.String(length=140), nullable=False),
|
|
sa.Column("description", sa.Text(), nullable=False, server_default=""),
|
|
sa.Column("is_public", sa.Boolean(), nullable=False, server_default=sa.text("true")),
|
|
sa.Column("is_removed", sa.Boolean(), nullable=False, server_default=sa.text("false")),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
|
|
sa.ForeignKeyConstraint(["author_id"], ["users.id"], ondelete="CASCADE"),
|
|
sa.PrimaryKeyConstraint("id"),
|
|
sa.UniqueConstraint("share_slug"),
|
|
)
|
|
op.create_index(op.f("ix_quizzes_author_id"), "quizzes", ["author_id"], unique=False)
|
|
op.create_index(op.f("ix_quizzes_is_public"), "quizzes", ["is_public"], unique=False)
|
|
op.create_index(op.f("ix_quizzes_is_removed"), "quizzes", ["is_removed"], unique=False)
|
|
op.create_index(op.f("ix_quizzes_share_slug"), "quizzes", ["share_slug"], unique=True)
|
|
|
|
op.create_table(
|
|
"questions",
|
|
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
|
|
sa.Column("quiz_id", sa.Integer(), nullable=False),
|
|
sa.Column("position", sa.Integer(), nullable=False, server_default="0"),
|
|
sa.Column("question_type", sa.String(length=24), nullable=False),
|
|
sa.Column("prompt", sa.Text(), nullable=False),
|
|
sa.Column("options", sa.JSON(), nullable=False),
|
|
sa.Column("correct_answer", sa.String(length=500), nullable=False),
|
|
sa.Column("accepted_answers", sa.JSON(), nullable=False),
|
|
sa.ForeignKeyConstraint(["quiz_id"], ["quizzes.id"], ondelete="CASCADE"),
|
|
sa.PrimaryKeyConstraint("id"),
|
|
)
|
|
op.create_index(op.f("ix_questions_quiz_id"), "questions", ["quiz_id"], unique=False)
|
|
|
|
op.create_table(
|
|
"quiz_attempts",
|
|
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
|
|
sa.Column("quiz_id", sa.Integer(), nullable=False),
|
|
sa.Column("user_id", sa.Integer(), nullable=False),
|
|
sa.Column("score_count", sa.Integer(), nullable=False),
|
|
sa.Column("total_questions", sa.Integer(), nullable=False),
|
|
sa.Column("score_percent", sa.Float(), nullable=False),
|
|
sa.Column("answers_payload", sa.JSON(), nullable=False),
|
|
sa.Column("completed_at", sa.DateTime(timezone=True), nullable=False),
|
|
sa.ForeignKeyConstraint(["quiz_id"], ["quizzes.id"], ondelete="CASCADE"),
|
|
sa.ForeignKeyConstraint(["user_id"], ["users.id"], ondelete="CASCADE"),
|
|
sa.PrimaryKeyConstraint("id"),
|
|
)
|
|
op.create_index(
|
|
"ix_quiz_attempts_quiz_score",
|
|
"quiz_attempts",
|
|
["quiz_id", "score_percent", "completed_at"],
|
|
unique=False,
|
|
)
|
|
op.create_index(
|
|
"ix_quiz_attempts_user_quiz",
|
|
"quiz_attempts",
|
|
["user_id", "quiz_id", "completed_at"],
|
|
unique=False,
|
|
)
|
|
|
|
op.create_table(
|
|
"quiz_votes",
|
|
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
|
|
sa.Column("quiz_id", sa.Integer(), nullable=False),
|
|
sa.Column("user_id", sa.Integer(), nullable=False),
|
|
sa.Column("value", sa.Integer(), nullable=False),
|
|
sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
|
|
sa.ForeignKeyConstraint(["quiz_id"], ["quizzes.id"], ondelete="CASCADE"),
|
|
sa.ForeignKeyConstraint(["user_id"], ["users.id"], ondelete="CASCADE"),
|
|
sa.PrimaryKeyConstraint("id"),
|
|
sa.UniqueConstraint("quiz_id", "user_id", name="uq_quiz_vote_user"),
|
|
)
|
|
op.create_index(op.f("ix_quiz_votes_quiz_id"), "quiz_votes", ["quiz_id"], unique=False)
|
|
op.create_index(op.f("ix_quiz_votes_user_id"), "quiz_votes", ["user_id"], unique=False)
|
|
|
|
op.create_table(
|
|
"favourites",
|
|
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
|
|
sa.Column("user_id", sa.Integer(), nullable=False),
|
|
sa.Column("quiz_id", sa.Integer(), nullable=False),
|
|
sa.Column("position", sa.Integer(), nullable=False, server_default="0"),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
|
|
sa.ForeignKeyConstraint(["quiz_id"], ["quizzes.id"], ondelete="CASCADE"),
|
|
sa.ForeignKeyConstraint(["user_id"], ["users.id"], ondelete="CASCADE"),
|
|
sa.PrimaryKeyConstraint("id"),
|
|
sa.UniqueConstraint("user_id", "quiz_id", name="uq_favourite_user_quiz"),
|
|
)
|
|
op.create_index(op.f("ix_favourites_quiz_id"), "favourites", ["quiz_id"], unique=False)
|
|
op.create_index(op.f("ix_favourites_user_id"), "favourites", ["user_id"], unique=False)
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.drop_index(op.f("ix_favourites_user_id"), table_name="favourites")
|
|
op.drop_index(op.f("ix_favourites_quiz_id"), table_name="favourites")
|
|
op.drop_table("favourites")
|
|
op.drop_index(op.f("ix_quiz_votes_user_id"), table_name="quiz_votes")
|
|
op.drop_index(op.f("ix_quiz_votes_quiz_id"), table_name="quiz_votes")
|
|
op.drop_table("quiz_votes")
|
|
op.drop_index("ix_quiz_attempts_user_quiz", table_name="quiz_attempts")
|
|
op.drop_index("ix_quiz_attempts_quiz_score", table_name="quiz_attempts")
|
|
op.drop_table("quiz_attempts")
|
|
op.drop_index(op.f("ix_questions_quiz_id"), table_name="questions")
|
|
op.drop_table("questions")
|
|
op.drop_index(op.f("ix_quizzes_share_slug"), table_name="quizzes")
|
|
op.drop_index(op.f("ix_quizzes_is_removed"), table_name="quizzes")
|
|
op.drop_index(op.f("ix_quizzes_is_public"), table_name="quizzes")
|
|
op.drop_index(op.f("ix_quizzes_author_id"), table_name="quizzes")
|
|
op.drop_table("quizzes")
|
|
op.drop_index(op.f("ix_api_keys_user_id"), table_name="api_keys")
|
|
op.drop_index(op.f("ix_api_keys_key_hash"), table_name="api_keys")
|
|
op.drop_table("api_keys")
|
|
op.drop_index(op.f("ix_users_nickname"), table_name="users")
|
|
op.drop_index(op.f("ix_users_email"), table_name="users")
|
|
op.drop_index(op.f("ix_users_auth_sub"), table_name="users")
|
|
op.drop_table("users")
|