You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
315 lines
12 KiB
315 lines
12 KiB
"""SQLAlchemy ORM-модели для БД clinic_tests. |
|
|
|
Таблицы: departments, users, tests, test_versions, questions, answer_options, |
|
test_assignments, test_assignment_targets, test_attempts, user_answers. |
|
|
|
Enum-типы (`user_role`, `target_type`, `attempt_status`) соответствуют PostgreSQL-перечислениям |
|
из 001_initial.sql — создаются через `create_constraint=False` (тип уже есть в БД). |
|
""" |
|
from __future__ import annotations |
|
|
|
import uuid |
|
from datetime import date, datetime |
|
from typing import List, Optional |
|
|
|
from sqlalchemy import ( |
|
ARRAY, |
|
Boolean, |
|
Date, |
|
DateTime, |
|
Enum, |
|
ForeignKey, |
|
Index, |
|
Integer, |
|
String, |
|
Text, |
|
UniqueConstraint, |
|
func, |
|
) |
|
from sqlalchemy.dialects.postgresql import UUID |
|
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship |
|
|
|
|
|
# ─── Base ──────────────────────────────────────────────────────────────────── |
|
|
|
class Base(DeclarativeBase): |
|
pass |
|
|
|
|
|
# ─── Enum types ─────────────────────────────────────────────────────────────── |
|
|
|
user_role_enum = Enum( |
|
"hr", "manager", "employee", |
|
name="user_role", |
|
create_constraint=False, # тип уже существует в БД |
|
) |
|
|
|
target_type_enum = Enum( |
|
"department", "user", |
|
name="target_type", |
|
create_constraint=False, |
|
) |
|
|
|
attempt_status_enum = Enum( |
|
"in_progress", "completed", "expired", |
|
name="attempt_status", |
|
create_constraint=False, |
|
) |
|
|
|
|
|
# ─── Models ─────────────────────────────────────────────────────────────────── |
|
|
|
class Department(Base): |
|
__tablename__ = "departments" |
|
|
|
id: Mapped[uuid.UUID] = mapped_column( |
|
UUID(as_uuid=True), primary_key=True, default=uuid.uuid4 |
|
) |
|
name: Mapped[str] = mapped_column(String(255), nullable=False) |
|
created_at: Mapped[Optional[datetime]] = mapped_column( |
|
DateTime, server_default=func.now() |
|
) |
|
updated_at: Mapped[Optional[datetime]] = mapped_column( |
|
DateTime, server_default=func.now(), onupdate=func.now() |
|
) |
|
|
|
users: Mapped[List["User"]] = relationship(back_populates="department") |
|
|
|
|
|
class User(Base): |
|
__tablename__ = "users" |
|
__table_args__ = ( |
|
Index("idx_users_login", "login"), |
|
Index("idx_users_department", "department_id"), |
|
Index( |
|
"idx_users_staff_id", "staff_id", |
|
postgresql_where="staff_id IS NOT NULL", |
|
), |
|
) |
|
|
|
id: Mapped[uuid.UUID] = mapped_column( |
|
UUID(as_uuid=True), primary_key=True, default=uuid.uuid4 |
|
) |
|
login: Mapped[str] = mapped_column(String(100), unique=True, nullable=False) |
|
password_hash: Mapped[str] = mapped_column(String(255), nullable=False) |
|
full_name: Mapped[str] = mapped_column(String(255), nullable=False) |
|
role: Mapped[str] = mapped_column(user_role_enum, nullable=False, server_default="employee") |
|
department_id: Mapped[Optional[uuid.UUID]] = mapped_column( |
|
UUID(as_uuid=True), ForeignKey("departments.id"), nullable=True |
|
) |
|
is_active: Mapped[bool] = mapped_column(Boolean, server_default="true") |
|
created_at: Mapped[Optional[datetime]] = mapped_column( |
|
DateTime, server_default=func.now() |
|
) |
|
updated_at: Mapped[Optional[datetime]] = mapped_column( |
|
DateTime, server_default=func.now(), onupdate=func.now() |
|
) |
|
staff_id: Mapped[Optional[int]] = mapped_column(Integer, unique=True, nullable=True) |
|
|
|
department: Mapped[Optional["Department"]] = relationship(back_populates="users") |
|
tests: Mapped[List["Test"]] = relationship(back_populates="author", foreign_keys="Test.created_by") |
|
attempts: Mapped[List["TestAttempt"]] = relationship(back_populates="user") |
|
|
|
|
|
class Test(Base): |
|
__tablename__ = "tests" |
|
|
|
id: Mapped[uuid.UUID] = mapped_column( |
|
UUID(as_uuid=True), primary_key=True, default=uuid.uuid4 |
|
) |
|
title: Mapped[str] = mapped_column(String(255), nullable=False) |
|
description: Mapped[Optional[str]] = mapped_column(Text) |
|
passing_threshold: Mapped[Optional[int]] = mapped_column(Integer, server_default="70") |
|
time_limit: Mapped[Optional[int]] = mapped_column(Integer) |
|
allow_back: Mapped[bool] = mapped_column(Boolean, server_default="true") |
|
is_active: Mapped[bool] = mapped_column(Boolean, server_default="true") |
|
is_versioned: Mapped[bool] = mapped_column(Boolean, server_default="false") |
|
hints_enabled: Mapped[bool] = mapped_column(Boolean, server_default="false") |
|
result_mode: Mapped[str] = mapped_column(String(16), server_default="end") |
|
created_by: Mapped[Optional[uuid.UUID]] = mapped_column( |
|
UUID(as_uuid=True), ForeignKey("users.id"), nullable=True |
|
) |
|
created_at: Mapped[Optional[datetime]] = mapped_column( |
|
DateTime, server_default=func.now() |
|
) |
|
updated_at: Mapped[Optional[datetime]] = mapped_column( |
|
DateTime, server_default=func.now(), onupdate=func.now() |
|
) |
|
|
|
author: Mapped[Optional["User"]] = relationship( |
|
back_populates="tests", foreign_keys=[created_by] |
|
) |
|
versions: Mapped[List["TestVersion"]] = relationship( |
|
back_populates="test", cascade="all, delete-orphan" |
|
) |
|
|
|
|
|
class TestVersion(Base): |
|
__tablename__ = "test_versions" |
|
__table_args__ = ( |
|
UniqueConstraint("test_id", "version", name="test_versions_test_id_version_key"), |
|
Index("idx_test_versions_parent_id", "parent_id"), |
|
Index( |
|
"uq_test_versions_one_active_per_test", "test_id", |
|
unique=True, |
|
postgresql_where="is_active = true", |
|
), |
|
) |
|
|
|
id: Mapped[uuid.UUID] = mapped_column( |
|
UUID(as_uuid=True), primary_key=True, default=uuid.uuid4 |
|
) |
|
test_id: Mapped[uuid.UUID] = mapped_column( |
|
UUID(as_uuid=True), ForeignKey("tests.id", ondelete="CASCADE"), nullable=False |
|
) |
|
version: Mapped[int] = mapped_column(Integer, nullable=False, server_default="1") |
|
is_active: Mapped[bool] = mapped_column(Boolean, server_default="false") |
|
parent_id: Mapped[Optional[uuid.UUID]] = mapped_column( |
|
UUID(as_uuid=True), |
|
ForeignKey("test_versions.id", ondelete="RESTRICT"), |
|
nullable=True, |
|
) |
|
created_at: Mapped[Optional[datetime]] = mapped_column( |
|
DateTime, server_default=func.now() |
|
) |
|
|
|
test: Mapped["Test"] = relationship(back_populates="versions") |
|
questions: Mapped[List["Question"]] = relationship( |
|
back_populates="version", cascade="all, delete-orphan", order_by="Question.question_order" |
|
) |
|
attempts: Mapped[List["TestAttempt"]] = relationship(back_populates="test_version") |
|
assignments: Mapped[List["TestAssignment"]] = relationship(back_populates="test_version") |
|
parent: Mapped[Optional["TestVersion"]] = relationship( |
|
remote_side="TestVersion.id", foreign_keys=[parent_id] |
|
) |
|
|
|
|
|
class Question(Base): |
|
__tablename__ = "questions" |
|
|
|
id: Mapped[uuid.UUID] = mapped_column( |
|
UUID(as_uuid=True), primary_key=True, default=uuid.uuid4 |
|
) |
|
test_version_id: Mapped[uuid.UUID] = mapped_column( |
|
UUID(as_uuid=True), ForeignKey("test_versions.id", ondelete="CASCADE"), nullable=False |
|
) |
|
text: Mapped[str] = mapped_column(Text, nullable=False) |
|
question_order: Mapped[int] = mapped_column(Integer, nullable=False) |
|
has_multiple_answers: Mapped[bool] = mapped_column(Boolean, server_default="false") |
|
ai_hint: Mapped[Optional[str]] = mapped_column(Text) |
|
|
|
version: Mapped["TestVersion"] = relationship(back_populates="questions") |
|
options: Mapped[List["AnswerOption"]] = relationship( |
|
back_populates="question", cascade="all, delete-orphan", order_by="AnswerOption.option_order" |
|
) |
|
user_answers: Mapped[List["UserAnswer"]] = relationship(back_populates="question") |
|
|
|
|
|
class AnswerOption(Base): |
|
__tablename__ = "answer_options" |
|
|
|
id: Mapped[uuid.UUID] = mapped_column( |
|
UUID(as_uuid=True), primary_key=True, default=uuid.uuid4 |
|
) |
|
question_id: Mapped[uuid.UUID] = mapped_column( |
|
UUID(as_uuid=True), ForeignKey("questions.id", ondelete="CASCADE"), nullable=False |
|
) |
|
text: Mapped[str] = mapped_column(Text, nullable=False) |
|
is_correct: Mapped[bool] = mapped_column(Boolean, server_default="false") |
|
option_order: Mapped[int] = mapped_column(Integer, nullable=False) |
|
|
|
question: Mapped["Question"] = relationship(back_populates="options") |
|
|
|
|
|
class TestAssignment(Base): |
|
__tablename__ = "test_assignments" |
|
|
|
id: Mapped[uuid.UUID] = mapped_column( |
|
UUID(as_uuid=True), primary_key=True, default=uuid.uuid4 |
|
) |
|
test_version_id: Mapped[uuid.UUID] = mapped_column( |
|
UUID(as_uuid=True), ForeignKey("test_versions.id", ondelete="CASCADE"), nullable=False |
|
) |
|
assigned_by: Mapped[Optional[uuid.UUID]] = mapped_column( |
|
UUID(as_uuid=True), ForeignKey("users.id"), nullable=True |
|
) |
|
deadline: Mapped[Optional[date]] = mapped_column(Date) |
|
max_attempts: Mapped[Optional[int]] = mapped_column(Integer, server_default="1") |
|
created_at: Mapped[Optional[datetime]] = mapped_column( |
|
DateTime, server_default=func.now() |
|
) |
|
|
|
test_version: Mapped["TestVersion"] = relationship(back_populates="assignments") |
|
targets: Mapped[List["TestAssignmentTarget"]] = relationship( |
|
back_populates="assignment", cascade="all, delete-orphan" |
|
) |
|
|
|
|
|
class TestAssignmentTarget(Base): |
|
__tablename__ = "test_assignment_targets" |
|
|
|
id: Mapped[uuid.UUID] = mapped_column( |
|
UUID(as_uuid=True), primary_key=True, default=uuid.uuid4 |
|
) |
|
assignment_id: Mapped[uuid.UUID] = mapped_column( |
|
UUID(as_uuid=True), ForeignKey("test_assignments.id", ondelete="CASCADE"), nullable=False |
|
) |
|
target_type: Mapped[str] = mapped_column(target_type_enum, nullable=False) |
|
target_id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), nullable=False) |
|
|
|
assignment: Mapped["TestAssignment"] = relationship(back_populates="targets") |
|
|
|
|
|
class TestAttempt(Base): |
|
__tablename__ = "test_attempts" |
|
__table_args__ = ( |
|
UniqueConstraint( |
|
"test_version_id", "user_id", "attempt_number", |
|
name="test_attempts_test_version_id_user_id_attempt_number_key", |
|
), |
|
) |
|
|
|
id: Mapped[uuid.UUID] = mapped_column( |
|
UUID(as_uuid=True), primary_key=True, default=uuid.uuid4 |
|
) |
|
test_version_id: Mapped[uuid.UUID] = mapped_column( |
|
UUID(as_uuid=True), ForeignKey("test_versions.id", ondelete="CASCADE"), nullable=False |
|
) |
|
user_id: Mapped[uuid.UUID] = mapped_column( |
|
UUID(as_uuid=True), ForeignKey("users.id"), nullable=False |
|
) |
|
attempt_number: Mapped[int] = mapped_column(Integer, server_default="1") |
|
status: Mapped[str] = mapped_column(attempt_status_enum, server_default="in_progress") |
|
started_at: Mapped[Optional[datetime]] = mapped_column( |
|
DateTime, server_default=func.now() |
|
) |
|
completed_at: Mapped[Optional[datetime]] = mapped_column(DateTime) |
|
correct_count: Mapped[Optional[int]] = mapped_column(Integer) |
|
total_questions: Mapped[Optional[int]] = mapped_column(Integer) |
|
passed: Mapped[Optional[bool]] = mapped_column(Boolean) |
|
|
|
test_version: Mapped["TestVersion"] = relationship(back_populates="attempts") |
|
user: Mapped["User"] = relationship(back_populates="attempts") |
|
user_answers: Mapped[List["UserAnswer"]] = relationship( |
|
back_populates="attempt", cascade="all, delete-orphan" |
|
) |
|
|
|
|
|
class UserAnswer(Base): |
|
__tablename__ = "user_answers" |
|
|
|
id: Mapped[uuid.UUID] = mapped_column( |
|
UUID(as_uuid=True), primary_key=True, default=uuid.uuid4 |
|
) |
|
attempt_id: Mapped[uuid.UUID] = mapped_column( |
|
UUID(as_uuid=True), ForeignKey("test_attempts.id", ondelete="CASCADE"), nullable=False |
|
) |
|
question_id: Mapped[uuid.UUID] = mapped_column( |
|
UUID(as_uuid=True), ForeignKey("questions.id", ondelete="CASCADE"), nullable=False |
|
) |
|
selected_options: Mapped[Optional[List[uuid.UUID]]] = mapped_column( |
|
ARRAY(UUID(as_uuid=True)), server_default="{}" |
|
) |
|
|
|
attempt: Mapped["TestAttempt"] = relationship(back_populates="user_answers") |
|
question: Mapped["Question"] = relationship(back_populates="user_answers")
|
|
|