87 lines
2.9 KiB
Python
87 lines
2.9 KiB
Python
"""student_progress composite score columns
|
|
|
|
Revision ID: 0005
|
|
Revises: 0004
|
|
Create Date: 2026-08-28
|
|
|
|
Menambah kolom composite evaluation ke student_progress:
|
|
- exercise_passed (Boolean)
|
|
- quiz_score_earned (Integer)
|
|
- quiz_score_total (Integer)
|
|
- composite_percent (Float)
|
|
|
|
Backfill data legacy:
|
|
- state='completed' → exercise_passed=true
|
|
- state='scored' → quiz_score_earned=score_earned, quiz_score_total=score_total
|
|
Legacy 'completed'/'scored' diubah ke 'in_progress' (akan di-recompute jadi
|
|
'done' oleh logika composite di backend). Constraint ck_student_progress_state
|
|
diperluas dari 3-state menjadi 5-state ('not_started','in_progress',
|
|
'completed','scored','done').
|
|
"""
|
|
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
revision = "0005"
|
|
down_revision = "0004"
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
op.add_column(
|
|
"student_progress",
|
|
sa.Column("exercise_passed", sa.Boolean(), nullable=True),
|
|
)
|
|
op.add_column(
|
|
"student_progress",
|
|
sa.Column("quiz_score_earned", sa.Integer(), nullable=True),
|
|
)
|
|
op.add_column(
|
|
"student_progress",
|
|
sa.Column("quiz_score_total", sa.Integer(), nullable=True),
|
|
)
|
|
op.add_column(
|
|
"student_progress",
|
|
sa.Column("composite_percent", sa.Float(), nullable=True),
|
|
)
|
|
|
|
# Perluas constraint state menjadi 5-state SEBELUM backfill update,
|
|
# agar nilai 'in_progress' diizinkan saat UPDATE di bawah.
|
|
op.drop_constraint(
|
|
"ck_student_progress_state", "student_progress", type_="check"
|
|
)
|
|
op.create_check_constraint(
|
|
"ck_student_progress_state",
|
|
"student_progress",
|
|
"state IN ('not_started','in_progress','completed','scored','done')",
|
|
)
|
|
|
|
# Backfill: completed → exercise lulus & composite 100%; scored → ambil skor kuis lama & hitung composite.
|
|
op.execute(
|
|
"UPDATE student_progress SET exercise_passed = true, composite_percent = 100.0, state = 'done' "
|
|
"WHERE state = 'completed'"
|
|
)
|
|
op.execute(
|
|
"UPDATE student_progress "
|
|
"SET quiz_score_earned = score_earned, quiz_score_total = score_total, "
|
|
" composite_percent = CASE WHEN score_total > 0 THEN ROUND((score_earned::numeric / score_total) * 100, 2) ELSE NULL END, "
|
|
" state = CASE WHEN score_total > 0 AND (score_earned::numeric / score_total) * 100 >= 75.0 THEN 'done' ELSE 'in_progress' END "
|
|
"WHERE state = 'scored'"
|
|
)
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.drop_constraint(
|
|
"ck_student_progress_state", "student_progress", type_="check"
|
|
)
|
|
op.create_check_constraint(
|
|
"ck_student_progress_state",
|
|
"student_progress",
|
|
"state IN ('not_started','completed','scored')",
|
|
)
|
|
op.drop_column("student_progress", "composite_percent")
|
|
op.drop_column("student_progress", "quiz_score_total")
|
|
op.drop_column("student_progress", "quiz_score_earned")
|
|
op.drop_column("student_progress", "exercise_passed")
|