আসল স্কিমা ডিজাইন, আর নিরাপদে তা বদলানো

STUDENTS ||--o{ ENROLMENTS    : has
COURSES  ||--o{ ENROLMENTS    : has
COURSES  ||--|{ LESSONS       : "made of"
ENROLMENTS ||--o{ QUIZ_ATTEMPTS : "allows"
LESSONS  ||--o{ QUIZ_ATTEMPTS : "quizzed in"

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    email      VARCHAR(100) NOT NULL UNIQUE,
    name       VARCHAR(100) NOT NULL,
    joined_on  DATE NOT NULL
);

CREATE TABLE courses (
    course_id INTEGER PRIMARY KEY,
    code      VARCHAR(20)  NOT NULL UNIQUE,
    title     VARCHAR(200) NOT NULL,
    level     INTEGER NOT NULL CHECK (level BETWEEN 0 AND 3)
);

CREATE TABLE lessons (
    lesson_id INTEGER PRIMARY KEY,
    course_id INTEGER NOT NULL,
    position  INTEGER NOT NULL CHECK (position > 0),
    title     VARCHAR(200) NOT NULL,
    has_quiz  INTEGER NOT NULL DEFAULT 0 CHECK (has_quiz IN (0, 1)),
    UNIQUE (course_id, position),
    FOREIGN KEY (course_id) REFERENCES courses (course_id)
);

CREATE TABLE enrolments (
    student_id  INTEGER NOT NULL,
    course_id   INTEGER NOT NULL,
    enrolled_on DATE NOT NULL,
    status      VARCHAR(10) NOT NULL DEFAULT 'active'
                CHECK (status IN ('active', 'completed', 'dropped')),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students (student_id),
    FOREIGN KEY (course_id) REFERENCES courses (course_id)
);

CREATE TABLE quiz_attempts (
    attempt_id   INTEGER PRIMARY KEY,
    student_id   INTEGER NOT NULL,
    course_id    INTEGER NOT NULL,
    lesson_id    INTEGER NOT NULL,
    score        INTEGER NOT NULL CHECK (score BETWEEN 0 AND 100),
    attempted_at TEXT NOT NULL,
    FOREIGN KEY (lesson_id) REFERENCES lessons (lesson_id),
    FOREIGN KEY (student_id, course_id) REFERENCES enrolments (student_id, course_id)
);

INSERT INTO students VALUES
    (1, 'nadia@example.com', 'Nadia Rahman', '2026-01-05'),
    (2, 'rafiq@example.com', 'Rafiq Islam',  '2026-01-20'),
    (3, 'mitu@example.com',  'Mitu Das',     '2026-02-02');
INSERT INTO courses VALUES (1, 'SQL-101', 'SQL for AI', 0), (2, 'ML-201', 'First ML Models', 1);
INSERT INTO lessons VALUES
    (1, 1, 1, 'SELECT basics', 1), (2, 1, 2, 'Joins', 1), (3, 1, 3, 'Window functions', 1),
    (4, 2, 1, 'Train/test split', 1), (5, 2, 2, 'Linear regression', 0);
INSERT INTO enrolments VALUES
    (1, 1, '2026-01-06', 'completed'), (1, 2, '2026-03-01', 'active'),
    (2, 1, '2026-01-21', 'dropped'),   (3, 1, '2026-02-03', 'active');
INSERT INTO quiz_attempts VALUES
    (1, 1, 1, 1, 60, '2026-01-07 10:00'), (2, 1, 1, 1, 90, '2026-01-08 09:30'),
    (3, 1, 1, 2, 85, '2026-01-12 20:15'), (4, 1, 1, 3, 70, '2026-01-20 21:00'),
    (5, 2, 1, 1, 40, '2026-01-22 18:00'), (6, 3, 1, 1, 95, '2026-02-04 08:45'),
    (7, 3, 1, 2, 55, '2026-02-10 19:10'), (8, 1, 2, 4, 80, '2026-03-02 11:00');

-- Rafiq কখনো ML-201-এ ভর্তি হননি
INSERT INTO quiz_attempts VALUES (9, 2, 2, 4, 75, '2026-03-05 10:00');

Error: FOREIGN KEY constraint failed

-- SQL-101-এ দ্বিতীয় একটা লেসন ২
INSERT INTO lessons VALUES (6, 1, 2, 'Joins again', 0);

Error: UNIQUE constraint failed: lessons.course_id, lessons.position

-- সীমার বাইরের স্কোর
INSERT INTO quiz_attempts VALUES (9, 3, 1, 3, 120, '2026-02-12 19:00');

Error: CHECK constraint failed: score BETWEEN 0 AND 100

SELECT s.name, c.code, e.status,
       COUNT(DISTINCT qa.lesson_id) AS quizzes_tried,
       (SELECT COUNT(*) FROM lessons AS l
        WHERE l.course_id = c.course_id AND l.has_quiz = 1) AS quizzes_total,
       COUNT(qa.attempt_id) AS attempts,
       ROUND(AVG(qa.score), 1) AS avg_score
FROM enrolments AS e
JOIN students AS s ON s.student_id = e.student_id
JOIN courses  AS c ON c.course_id  = e.course_id
LEFT JOIN quiz_attempts AS qa
       ON qa.student_id = e.student_id AND qa.course_id = e.course_id
GROUP BY s.student_id, s.name, c.course_id, c.code, e.status
ORDER BY s.student_id, c.code;

+--------------+---------+-----------+---------------+---------------+----------+-----------+
| name         | code    | status    | quizzes_tried | quizzes_total | attempts | avg_score |
+--------------+---------+-----------+---------------+---------------+----------+-----------+
| Nadia Rahman | ML-201  | active    |             1 |             1 |        1 |      80.0 |
| Nadia Rahman | SQL-101 | completed |             3 |             3 |        4 |      76.3 |
| Rafiq Islam  | SQL-101 | dropped   |             1 |             3 |        1 |      40.0 |
| Mitu Das     | SQL-101 | active    |             2 |             3 |        2 |      75.0 |
+--------------+---------+-----------+---------------+---------------+----------+-----------+

migrations/
  0001_initial.sql
  0002_add_country.sql
  0003_backfill_country.sql
  0004_country_not_null.sql

import sqlite3
from pathlib import Path

def migrate(db_path, folder="migrations"):
    con = sqlite3.connect(db_path)
    con.execute("CREATE TABLE IF NOT EXISTS schema_migrations ("
                "version TEXT PRIMARY KEY, applied_at TEXT DEFAULT CURRENT_TIMESTAMP)")
    con.commit()
    done = {row[0] for row in con.execute("SELECT version FROM schema_migrations")}
    applied = 0
    for path in sorted(Path(folder).glob("*.sql")):
        if path.stem in done:
            continue
        try:
            con.executescript("BEGIN;\n" + path.read_text(encoding="utf-8"))
            con.execute("INSERT INTO schema_migrations (version) VALUES (?)", (path.stem,))
            con.commit()
            print("applied", path.stem)
            applied += 1
        except sqlite3.Error as exc:
            con.rollback()
            print(f"FAILED {path.stem}: {exc} -- rolled back")
            break
    con.close()
    print(f"{applied} migration(s) applied")

Path("migrations").mkdir(exist_ok=True)
Path("migrations/0001_initial.sql").write_text("""
CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    email      TEXT NOT NULL UNIQUE,
    name       TEXT NOT NULL
);
INSERT INTO students (student_id, email, name) VALUES
    (1, 'nadia@example.com', 'Nadia Rahman'),
    (2, 'rafiq@example.com', 'Rafiq Islam');
""", encoding="utf-8")

migrate("platform.db")
migrate("platform.db")

applied 0001_initial
1 migration(s) applied
0 migration(s) applied

-- PostgreSQL: রাজি হয় না, কারণ আগের সারিগুলো NULL হয়ে যেত
ALTER TABLE orders ADD COLUMN total_amount INTEGER NOT NULL;

Error: column "total_amount" of relation "orders" contains null values

-- MySQL: সফল হয়, আর চুপচাপ প্রতিটা সারিতে 0 বসিয়ে দেয়
ALTER TABLE orders ADD COLUMN total_amount INTEGER NOT NULL;
SELECT order_id, total_amount FROM orders WHERE order_id <= 3 ORDER BY order_id;

+----------+--------------+
| order_id | total_amount |
+----------+--------------+
|        1 |            0 |
|        2 |            0 |
|        3 |            0 |
+----------+--------------+

-- PostgreSQL: ১. NULL-যোগ্য কলাম যোগ  ২. আসল ডেটা থেকে ব্যাকফিল  ৩. কনস্ট্রেইন্ট
ALTER TABLE orders ADD COLUMN total_amount INTEGER;
UPDATE orders AS o
SET total_amount = (SELECT COALESCE(SUM(oi.quantity * oi.unit_price), 0)
                    FROM order_items AS oi WHERE oi.order_id = o.order_id)
WHERE total_amount IS NULL;
ALTER TABLE orders ALTER COLUMN total_amount SET NOT NULL;
SELECT order_id, total_amount FROM orders WHERE order_id <= 3 ORDER BY order_id;

+----------+--------------+
| order_id | total_amount |
+----------+--------------+
|        1 |         2100 |
|        2 |         4500 |
|        3 |         3000 |
+----------+--------------+

Path("migrations/0002_add_country.sql").write_text(
    "ALTER TABLE students ADD COLUMN country TEXT;", encoding="utf-8")
Path("migrations/0003_backfill_country.sql").write_text(
    "UPDATE students SET country = 'BD' WHERE country IS NULL;", encoding="utf-8")
Path("migrations/0004_country_not_null.sql").write_text("""
CREATE TABLE students_new (
    student_id INTEGER PRIMARY KEY,
    email      TEXT NOT NULL UNIQUE,
    name       TEXT NOT NULL,
    country    TEXT NOT NULL DEFAULT 'BD'
);
INSERT INTO students_new (student_id, email, name, country)
    SELECT student_id, email, name, country FROM students;
DROP TABLE students;
ALTER TABLE students_new RENAME TO students;
""", encoding="utf-8")
Path("migrations/0005_unique_name.sql").write_text("""
INSERT INTO students (student_id, email, name) VALUES (3, 'nadia2@example.com', 'Nadia Rahman');
CREATE UNIQUE INDEX students_name ON students (name);
""", encoding="utf-8")

migrate("platform.db")

con = sqlite3.connect("platform.db")
print(con.execute("SELECT group_concat(version, ', ') FROM schema_migrations").fetchone()[0])
print(con.execute("SELECT * FROM students ORDER BY student_id").fetchall())
con.close()

applied 0002_add_country
applied 0003_backfill_country
applied 0004_country_not_null
FAILED 0005_unique_name: UNIQUE constraint failed: students.name -- rolled back
3 migration(s) applied
0001_initial, 0002_add_country, 0003_backfill_country, 0004_country_not_null
[(1, 'nadia@example.com', 'Nadia Rahman', 'BD'), (2, 'rafiq@example.com', 'Rafiq Islam', 'BD')]

alembic revision --autogenerate -m "add country to students"   # একটা মাইগ্রেশনের খসড়া
alembic upgrade head                                           # বাকি সব প্রয়োগ
flyway migrate                                                 # বাকি V*.sql ফাইল প্রয়োগ
npx prisma migrate dev --name add_country                      # বানানো আর প্রয়োগ (ডেভেলপমেন্ট)
npx prisma migrate deploy                                      # বাকিগুলো প্রয়োগ (প্রোডাকশন)

-- সহজ
SELECT s.name, l.title, MAX(qa.score) AS best_score
FROM quiz_attempts AS qa
JOIN students AS s ON s.student_id = qa.student_id
JOIN lessons  AS l ON l.lesson_id  = qa.lesson_id
WHERE qa.course_id = 1
GROUP BY s.student_id, s.name, l.lesson_id, l.position, l.title
ORDER BY s.student_id, l.position;

# মাঝারি
Path("migrations/0005_unique_name.sql").write_text(
    "CREATE INDEX students_name ON students (name);", encoding="utf-8")
migrate("platform.db")
con = sqlite3.connect("platform.db")
print(con.execute("SELECT group_concat(version, ', ') FROM schema_migrations").fetchone()[0])
con.close()

-- কঠিন: (lesson_id, course_id)-কে ফরেন কি-র লক্ষ্য হওয়ার যোগ্য করুন, তারপর দুটো কলাম একসাথে রেফার করুন
CREATE TABLE lessons_v2 (
    lesson_id INTEGER PRIMARY KEY,
    course_id INTEGER NOT NULL,
    position  INTEGER NOT NULL CHECK (position > 0),
    title     VARCHAR(200) NOT NULL,
    UNIQUE (course_id, position),
    UNIQUE (lesson_id, course_id),
    FOREIGN KEY (course_id) REFERENCES courses (course_id)
);
CREATE TABLE quiz_attempts_v2 (
    attempt_id   INTEGER PRIMARY KEY,
    student_id   INTEGER NOT NULL,
    course_id    INTEGER NOT NULL,
    lesson_id    INTEGER NOT NULL,
    score        INTEGER NOT NULL CHECK (score BETWEEN 0 AND 100),
    attempted_at TEXT NOT NULL,
    FOREIGN KEY (student_id, course_id) REFERENCES enrolments (student_id, course_id),
    FOREIGN KEY (lesson_id, course_id)  REFERENCES lessons_v2 (lesson_id, course_id)
);