آوند — داده: SQL، مدل‌سازی و تحلیل برای تصمیم

فصل ۶ از ۹

پیشرفت ترم
۰٪

در این فصل چه یاد می‌گیری#

ترمِ ۱ سه عیب در این داده کاشت و از آن روز تا حالا دست‌نخورده مانده‌اند: ۲۳۱ سطر بدونِ امتیاز، ۳۷ مقدارِ متنی در ستونی که INTEGER اعلام شده، و ۱۹ سطر که نامِ نویسنده‌شان با نویسهٔ عربی وارد شده.

این فصل برای هر سه یک قید می‌نویسد و می‌شمارد که هرکدام دقیقاً چند سطر از دادهٔ موجود را رد می‌کند. بعد کلِ فهرست را داخلِ جدولِ سخت‌گیر می‌ریزد: ۷۸۸ سطر قبول می‌شود و ۴۱۲ سطر رد.

و دو چیزِ غیرمنتظره پیدا می‌کنیم. اول اینکه جمعِ تک‌تکِ قیدها ۴۶۲ است، نه ۴۱۲ — و اختلافش یک درسِ کامل است. دوم اینکه یکی از آن قیدها اصلاً نباید نوشته می‌شد، و همان قید تنهایی مسئولِ نصفِ سطرهای ردشده است.

یک غربالِ چندطبقه که هر طبقه سوراخ‌های شکل‌دارِ متفاوتی دارد و در هر طبقه چند قطعه پشتِ آن مانده

آخر این فصل می‌توانی:

  • NOT NULL و DEFAULT و CHECK و UNIQUE را در یک اسکیمای واقعی بنویسی
  • قبل از اعمالِ هر قید، تعدادِ سطرهایی را که رد می‌کند بشماری
  • تشخیص بدهی قیدی که خیلی سطر رد می‌کند، سخت‌گیر است یا غلط
  • بگویی کدام قید را می‌شود خاموش کرد و کدام را نمی‌شود

قبل از شروع#

از فصلِ ۲ و ۳: PRIMARY KEY و UNIQUE و FOREIGN KEY و آن پرچمِ خاموش. از ترمِ ۱ فصلِ ۵ و ۶: typeof و CAST و اینکه در SQLite نوع صفتِ مقدار است نه صفتِ ستون.

قاعدهٔ سختِ این ترم، در نهایی‌ترین شکلش: هر قانونی که می‌شود در اسکیما نوشت، در اسکیما نوشته می‌شود. کدی که فراموش می‌کند اجرا شود همیشه وجود دارد؛ قیدی که پایگاه داده اجرا می‌کند فراموش نمی‌شود.

📓 نوت‌بوک: نوت‌بوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و به‌ترتیب داخلش هست.

جدول تعدادِ سطر زمانِ ساخت
catalog_raw ۱٬۲۰۰ کمتر از یک ثانیه
members_raw ۳۰۰ آنی
loan_events ۲۶٬۱۱۹ حدودِ یک ثانیه

۱. قانونی که در کد است، فراموش می‌شود#

فرض کن قاعده این باشد: «تعدادِ صفحه‌های یک کتاب نمی‌تواند صفر یا منفی باشد». دو راه برای اجرایش هست.

con.execute("DROP TABLE IF EXISTS soft_rule")
con.execute("DROP TABLE IF EXISTS hard_rule")
con.execute("CREATE TABLE soft_rule (id INTEGER PRIMARY KEY, pages INTEGER NOT NULL)")
con.execute("""
    CREATE TABLE hard_rule (
        id    INTEGER PRIMARY KEY,
        pages INTEGER NOT NULL CONSTRAINT pages_positive CHECK (pages > 0)
    )
""")
con.commit()


def add_book(table, book_id, pages):
    """راهِ اول: قانون در کد."""
    if pages <= 0:
        print("  کد جلویش را گرفت")
        return
    # نام جدول به متن پرسش چسبیده، نه مقدار — همان مرز ترم ۱ فصل ۷.
    con.execute(f"INSERT INTO {table} VALUES (?, ?)", (book_id, pages))


print("از راه تابع، با صفحهٔ منفی:")
add_book("soft_rule", 1, -10)
print("و از راهی که یادش رفته از تابع رد شود:")
con.execute("INSERT INTO soft_rule VALUES (?, ?)", (2, -10))
con.commit()
print(q("SELECT * FROM soft_rule"))
print()
print("همان کار روی جدولی که قانون را در اسکیما دارد:")
try:
    con.execute("INSERT INTO hard_rule VALUES (?, ?)", (2, -10))
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
از راه تابع، با صفحهٔ منفی:
  کد جلویش را گرفت
و از راهی که یادش رفته از تابع رد شود:
   id  pages
0   2    -10

همان کار روی جدولی که قانون را در اسکیما دارد:
sqlite3.IntegrityError
CHECK constraint failed: pages_positive

تابع کارش را درست انجام داد. مشکل این بود که یک نفر از تابع رد نشد.

و این «یک نفر» در کارِ واقعی همیشه وجود دارد: اسکریپتِ واردکردنِ داده، پنلِ ادمین، ابزارِ ترمیم که یک بار نوشته شد، یا خودت وقتی داری در کنسول عیب‌یابی می‌کنی. قیدِ اسکیما زیرِ همهٔ این‌ها می‌نشیند و کسی تصادفی از کنارش رد نمی‌شود.

نامِ قید (CONSTRAINT pages_positive) اختیاری است و نوشتنش از اینجا به بعد اجباری‌مان می‌شود — چون بدونِ نام، پیامِ خطا به‌جای اسمِ قاعده، متنِ خودِ شرط را تکرار می‌کند. بخشِ آخر نشان می‌دهد چرا این گران است.

۲. قبل از اعمالِ قید، بشمار چند سطر را رد می‌کند#

بدترین کاری که می‌شود کرد این است که قید را بنویسی و ببینی چه می‌شود. کارِ درست این است که اول همان شرط را به‌شکلِ یک SELECT بشماری.

RULES = [
    ("rating_not_null",   "rating IS NULL"),
    ("edition_is_number", "typeof(edition) <> 'integer'"),
    ("author_is_persian", "author LIKE '%ي%' OR author LIKE '%ك%'"),
    ("price_positive",    "CAST(price_text AS INTEGER) <= 0"),
    ("pages_positive",    "pages <= 0"),
    ("year_in_range",     "year NOT BETWEEN 1500 AND 2100"),
]
total = 0
for name, breaks_it in RULES:
    # شرط، متنِ پرسش است نه مقدار؛ هیچ‌کدام از این‌ها از بیرون نمی‌آیند.
    n = int(q(f"SELECT COUNT(*) AS n FROM catalog_raw WHERE {breaks_it}").iloc[0, 0])
    total += n
    print(f"  {name:<18} {n:>5} سطر را رد می‌کند")
duplicates = int(q("""
    SELECT COUNT(*) - COUNT(DISTINCT title || '|' || author || '|' || branch) AS n
    FROM catalog_raw
""").iloc[0, 0])
total += duplicates
print(f"  {'unique_title_etc':<18} {duplicates:>5} سطر را رد می‌کند")
print()
print("جمع تک‌تک قیدها:", total, "| سطرهای جدول: 1200")
  rating_not_null      231 سطر را رد می‌کند
  edition_is_number     37 سطر را رد می‌کند
  author_is_persian     19 سطر را رد می‌کند
  price_positive       169 سطر را رد می‌کند
  pages_positive         0 سطر را رد می‌کند
  year_in_range          0 سطر را رد می‌کند
  unique_title_etc       6 سطر را رد می‌کند

جمع تک‌تک قیدها: 462 | سطرهای جدول: 1200

هفت عدد، و دوتایشان صفرند — که همان‌قدر خبر است.

pages_positive و year_in_range هیچ سطری را رد نمی‌کنند. این یک نتیجهٔ منفی است و باید نوشته شود: یعنی نوشتنشان امروز هیچ چیزی را تغییر نمی‌دهد، و دقیقاً به همین دلیل ارزان‌ترین قیدهای این فهرست‌اند. قیدی که امروز صفر سطر رد می‌کند، فردا اولین سطرِ خرابِ آن دسته را می‌گیرد — و هیچ هزینه‌ای هم ندارد.

و حالا مهم‌ترین قضاوتِ این فصل: rating_not_null با ۲۳۱ سطر، تنهایی نصفِ ماجراست — و قیدِ غلطی است.

کتابی که هیچ‌کس امتیازش نداده، دادهٔ خرابی نیست؛ کتابی است که هنوز امتیاز نخورده. ترمِ ۱ فصلِ ۶ نشان داد آن ۲۳۱ سطر دقیقاً همان کتاب‌های کم‌امانت‌اند. اگر NOT NULL رویش بگذاری، یا باید ۲۳۱ کتابِ واقعی را دور بریزی، یا برایشان عددی از خودت بسازی — و هر دو بدتر از نگه داشتنِ NULL هستند.

قاعده‌ای که از اینجا بیرون می‌آید: عددِ بزرگ به‌تنهایی نمی‌گوید داده خراب است. می‌گوید یکی از این دو خراب است — داده یا قید — و تصمیمش با توست. ۳۷ و ۱۹ نشانهٔ خرابیِ داده‌اند؛ ۲۳۱ نشانهٔ خرابیِ قید.

۳. حالا واقعاً بارگذاری کن#

con.executescript("""
    DROP TABLE IF EXISTS books_strict;
    CREATE TABLE books_strict (
        id      INTEGER PRIMARY KEY,
        title   TEXT    NOT NULL,
        author  TEXT    NOT NULL CONSTRAINT author_is_persian
                        CHECK (author NOT LIKE '%ي%' AND author NOT LIKE '%ك%'),
        branch  TEXT    NOT NULL,
        pages   INTEGER NOT NULL CONSTRAINT pages_positive CHECK (pages > 0),
        year    INTEGER NOT NULL CONSTRAINT year_in_range
                        CHECK (year BETWEEN 1500 AND 2100),
        edition INTEGER NOT NULL CONSTRAINT edition_is_number
                        CHECK (typeof(edition) = 'integer'),
        rating  REAL    NOT NULL CONSTRAINT rating_in_range
                        CHECK (rating BETWEEN 0 AND 5),
        price   INTEGER NOT NULL CONSTRAINT price_positive CHECK (price > 0),
        UNIQUE (title, author, branch)
    );
""")
con.commit()

SOURCE = """
    SELECT id, title, author, branch, pages, year, edition, rating,
           CAST(price_text AS INTEGER)
    FROM catalog_raw
    ORDER BY id
"""
try:
    con.execute("INSERT INTO books_strict " + SOURCE)
except Exception as error:
    print("بارگذاری یک‌جا:")
    print(" ", type(error).__module__ + "." + type(error).__name__, "|", error)
con.rollback()
بارگذاری یک‌جا:
  sqlite3.IntegrityError | NOT NULL constraint failed: books_strict.rating

یک INSERT برای ۱٬۲۰۰ سطر، و کلش با اولین سطرِ خراب برگشت. این رفتار درست است — یک تراکنش یا کامل انجام می‌شود یا اصلاً — ولی برای فهمیدنِ وضعیتِ داده بی‌فایده است: فقط اسمِ اولین قیدِ شکسته را می‌گوید.

پس سطر به سطر واردشان می‌کنیم و هر ردشدن را ثبت می‌کنیم:

from collections import Counter

rejected = Counter()
accepted = 0
for row in con.execute(SOURCE).fetchall():
    try:
        con.execute("INSERT INTO books_strict VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", row)
        accepted += 1
    except Exception as error:
        rejected[str(error)] += 1
con.commit()

print("قبول شد:", accepted, "| رد شد:", sum(rejected.values()),
      "| جمع:", accepted + sum(rejected.values()))
print()
for message, count in sorted(rejected.items(), key=lambda pair: -pair[1]):
    print(f"  {count:>5}  {message}")
قبول شد: 788 | رد شد: 412 | جمع: 1200

    231  NOT NULL constraint failed: books_strict.rating
    136  CHECK constraint failed: price_positive
     27  CHECK constraint failed: edition_is_number
     16  CHECK constraint failed: author_is_persian
      2  UNIQUE constraint failed: books_strict.title, books_strict.author, books_strict.branch

هفتصد و هشتاد و هشت قبول، ۴۱۲ رد، و جمعشان دقیقاً ۱٬۲۰۰ — پس هیچ سطری بی‌سرنوشت نماند.

و این خطاها مزاحمت نیستند، اولین فهرستِ صادقانهٔ عیب‌های این داده‌اند. تا امروز آن ۴۱۲ سطر همان‌طور در جدول نشسته بودند و هر پرسشی که رویشان می‌زدی جوابِ آلوده می‌داد. حالا اسم دارند، شمرده شده‌اند، و می‌شود دربارهٔ هرکدام تصمیم گرفت.

۴. چرا جمعِ اجزا از کل بیشتر است#

بخشِ ۲ جمعِ ۴۶۲ داد و بخشِ ۳ رقمِ ۴۱۲. پنجاه واحد اختلاف، و دلیلش هیچ ربطی به اشتباهِ محاسبه ندارد.

برای دیدنش، به‌جای شمردنِ سطر برای هر قید، برای هر سطر می‌شماریم چند قید را می‌شکند:

spread = q("""
    SELECT broken, COUNT(*) AS rows
    FROM (
        SELECT (rating IS NULL)
             + (typeof(edition) <> 'integer')
             + (author LIKE '%ي%' OR author LIKE '%ك%')
             + (CAST(price_text AS INTEGER) <= 0) AS broken
        FROM catalog_raw
    )
    GROUP BY broken
    ORDER BY broken
""")
print(spread)
print()
print("جمع «قید ضربدر سطر»           :", int((spread["broken"] * spread["rows"]).sum()))
print("سطرهایی که دست‌کم یک قید را می‌شکنند:",
      int(spread.loc[spread["broken"] > 0, "rows"].sum()))
print()
print(q("""
    SELECT id, author, edition, rating, price_text
    FROM catalog_raw
    WHERE rating IS NULL AND CAST(price_text AS INTEGER) <= 0
    ORDER BY id
    LIMIT 3
"""))
   broken  rows
0       0   790
1       1   365
2       2    44
3       3     1

جمع «قید ضربدر سطر»           : 456
سطرهایی که دست‌کم یک قید را می‌شکنند: 410

    id       author  edition rating price_text
0   33   زهرا دهقان        3   None
1  100   آرش نیکزاد        6   None     نامشخص
2  102  پروانه بیات        4   None     ۱۹۷۰۰۰

یک سطر می‌تواند چند قانون را با هم بشکند، و SQLite فقط اولی را گزارش می‌کند.

چهار قیدِ مقداری روی هم ۴۵۶ بار شکسته شدند، ولی فقط ۴۱۰ سطر مسئولِ آن‌ها هستند: ۴۴ سطر دو قید را با هم شکستند و یک سطر سه‌تا. ۴۵۶ منهای ۴۱۰ می‌شود ۴۶، و چهار واحدِ باقی‌ماندهٔ آن اختلافِ ۵۰ مالِ UNIQUE است: از شش سطرِ تکراری، چهارتایشان قبلاً سرِ یک قیدِ مقداری رد شده بودند و هرگز به UNIQUE نرسیدند — به همین دلیل جدولِ بخشِ ۳ فقط دو ردشدنِ UNIQUE نشان داد.

سه سطرِ نمونهٔ بالا هم امتیاز ندارند و هم قیمتشان قابلِ خواندن نیست. در بخشِ ۲ هر کدامشان دو بار شمرده شدند؛ در بخشِ ۳ فقط یک بار رد شدند.

پس دو عدد دو چیزِ متفاوت را می‌گویند و هر دو لازم‌اند:

  • ۴۶۲ می‌گوید «هر قید چقدر کار می‌کند» — برای تصمیم گرفتن دربارهٔ خودِ قیدها.
  • ۴۱۲ می‌گوید «چند سطر از داده وارد نمی‌شود» — برای تصمیم گرفتن دربارهٔ داده.

و همین توضیح می‌دهد چرا جدولِ بخشِ ۳ برای edition_is_number عددِ ۲۷ نشان داد نه ۳۷: از ده‌تای باقی‌مانده، نه‌تا قبلاً سرِ rating رد شده بودند و یکی سرِ author_is_persian.

✅ چک کن: accepted + sum(rejected.values()) باید دقیقاً ۱٬۲۰۰ باشد. اگر کمتر بود، حلقه‌ات یک try بدونِ except دارد و سطری بی‌صدا گم شده — که بدترین شکلِ ممکن است، چون نه در قبول‌شده‌هاست و نه در ردشده‌ها.

۵. همان اسکیما، با یک قیدِ اصلاح‌شده#

حالا نتیجهٔ قضاوتِ بخشِ ۲ را اعمال می‌کنیم: rating می‌تواند خالی باشد، ولی اگر مقدار داشت باید معتبر باشد.

con.executescript("""
    DROP TABLE IF EXISTS books_sane;
    CREATE TABLE books_sane (
        id      INTEGER PRIMARY KEY,
        title   TEXT    NOT NULL,
        author  TEXT    NOT NULL CONSTRAINT author_is_persian
                        CHECK (author NOT LIKE '%ي%' AND author NOT LIKE '%ك%'),
        branch  TEXT    NOT NULL,
        pages   INTEGER NOT NULL CONSTRAINT pages_positive CHECK (pages > 0),
        year    INTEGER NOT NULL CONSTRAINT year_in_range
                        CHECK (year BETWEEN 1500 AND 2100),
        edition INTEGER NOT NULL CONSTRAINT edition_is_number
                        CHECK (typeof(edition) = 'integer'),
        rating  REAL             CONSTRAINT rating_in_range
                        CHECK (rating IS NULL OR rating BETWEEN 0 AND 5),
        price   INTEGER NOT NULL CONSTRAINT price_positive CHECK (price > 0),
        status  TEXT    NOT NULL DEFAULT 'shelf',
        UNIQUE (title, author, branch)
    );
""")
con.commit()

sane_rejected = Counter()
sane_accepted = 0
for row in con.execute(SOURCE).fetchall():
    try:
        con.execute("""
            INSERT INTO books_sane
                   (id, title, author, branch, pages, year, edition, rating, price)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, row)
        sane_accepted += 1
    except Exception as error:
        sane_rejected[str(error)] += 1
con.commit()

print("قبول شد:", sane_accepted, "| رد شد:", sum(sane_rejected.values()))
for message, count in sorted(sane_rejected.items(), key=lambda pair: -pair[1]):
    print(f"  {count:>5}  {message}")
print()
print("ستون status که اصلاً مقدارش را ندادیم:")
print(q("SELECT status, COUNT(*) AS rows FROM books_sane GROUP BY status"))
قبول شد: 978 | رد شد: 222
    164  CHECK constraint failed: price_positive
     36  CHECK constraint failed: edition_is_number
     19  CHECK constraint failed: author_is_persian
      3  UNIQUE constraint failed: books_sane.title, books_sane.author, books_sane.branch

ستون status که اصلاً مقدارش را ندادیم:
  status  rows
0  shelf   978

۷۸۸ شد ۹۷۸ — یعنی یک تصمیمِ درست دربارهٔ یک قید، ۱۹۰ سطرِ سالم را نجات داد.

و توجه کن که rating هنوز محافظت دارد: مقدارِ خالی مجاز است، مقدارِ نامعتبر نه. این ترکیب — یک ستونِ اختیاری با یک CHECK که NULL را استثنا می‌کند — پرکاربردترین الگوی این فصل است.

و دقت کن که عددهای هر چهار قیدِ دیگر بالا رفتند: price_positive از ۱۳۶ به ۱۶۴، edition_is_number از ۲۷ به ۳۶ و author_is_persian از ۱۶ به ۱۹. سطرهایی که قبلاً سرِ rating رد می‌شدند، حالا زنده به این قیدها می‌رسند. همان درسِ بخشِ ۴، این بار از جهتِ عکس.

DEFAULT هم کارِ خودش را کرد: ستونِ status را در INSERT اصلاً نام نبردیم و هر ۹۷۸ سطر مقدارِ پیش‌فرض گرفتند. DEFAULT و NOT NULL دو نیمهٔ یک تصمیم‌اند: NOT NULL می‌گوید این ستون باید مقدار داشته باشد، DEFAULT می‌گوید اگر کسی ندهد چه باشد. بدونِ دومی، اولی هر INSERT قدیمی را می‌شکند.

۶. قیدی که خاموش‌شدنی نیست#

فصلِ ۳ نشان داد کلیدِ خارجی یک کلیدِ خاموش‌وروشن دارد و پیش‌فرض خاموش است. طبیعی است که بپرسی بقیهٔ قیدها هم همین‌طورند یا نه.

import sqlite3

plain = sqlite3.connect(DB_PATH)
print("PRAGMA foreign_keys روی این اتصال:",
      plain.execute("PRAGMA foreign_keys").fetchone()[0])
print()
for label, values in [
    ("CHECK", (9001, "کتاب آزمایشی", "نام آزمایشی", "مرکزی", -5, 2020, 1, 4.0, 1000)),
    ("NOT NULL", (9002, None, "نام آزمایشی", "مرکزی", 100, 2020, 1, 4.0, 1000)),
]:
    try:
        plain.execute("""
            INSERT INTO books_sane
                   (id, title, author, branch, pages, year, edition, rating, price)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, values)
        print(f"  {label:<9} → قبول شد")
    except Exception as error:
        print(f"  {label:<9} → {type(error).__name__} | {error}")
plain.rollback()
PRAGMA foreign_keys روی این اتصال: 0

  CHECK     → IntegrityError | CHECK constraint failed: pages_positive
  NOT NULL  → IntegrityError | NOT NULL constraint failed: books_sane.title

اتصالی که کلیدِ خارجی رویش خاموش است، CHECK و NOT NULL را همچنان اجرا می‌کند.

این تفاوت را جدی بگیر: FOREIGN KEY تنها قیدی است که در SQLite پیش‌فرض خاموش است. NOT NULL و UNIQUE و PRIMARY KEY همیشه و از هر اتصالی اجرا می‌شوند و کلیدی ندارند. CHECK پیش‌فرض روشن است، ولی آن هم یک کلیدِ کمتر شناخته‌شده دارد:

print("ignore_check_constraints پیش‌فرض:",
      plain.execute("PRAGMA ignore_check_constraints").fetchone()[0])
plain.execute("PRAGMA ignore_check_constraints = ON")
plain.execute("""
    INSERT INTO books_sane
           (id, title, author, branch, pages, year, edition, rating, price)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
""", (9003, "کتاب آزمایشی", "نام آزمایشی", "مرکزی", -5, 2020, 1, 4.0, 1000))
print("با کلید روشن، صفحهٔ منفی:",
      plain.execute("SELECT pages FROM books_sane WHERE id = ?", (9003,)).fetchone()[0])
plain.rollback()
plain.execute("PRAGMA ignore_check_constraints = OFF")
ignore_check_constraints پیش‌فرض: 0
با کلید روشن، صفحهٔ منفی: -5

نتیجهٔ عملی‌اش برای طراحی: هر قانونی که می‌توانی به‌شکلِ CHECK بنویسی، از هر قانونِ معادلی که به کلیدِ خارجی تکیه می‌کند مطمئن‌تر است — نه چون دور زدنی نیست، بلکه چون برای اجرا شدنش کسی نباید کاری بکند. کلیدِ خارجی را باید روی هر اتصال روشن کرد و فراموش کردنش کافی است. CHECK را باید کسی آگاهانه خاموش کند.

۷. با ۲۲۲ سطرِ ردشده چه کنیم#

سطرهای ردشده را دور نریز. جدولِ قرنطینه بسازشان: همان ستون‌ها، بدونِ هیچ قیدی، به‌علاوهٔ یک ستون که می‌گوید چرا رد شده.

con.executescript("""
    DROP TABLE IF EXISTS books_quarantine;
    CREATE TABLE books_quarantine (
        id     INTEGER PRIMARY KEY,
        title  TEXT,
        author TEXT,
        reason TEXT NOT NULL
    );
""")
con.commit()

for row in con.execute(SOURCE).fetchall():
    if int(q("SELECT COUNT(*) AS n FROM books_sane WHERE id = ?",
             (row[0],)).iloc[0, 0]):
        continue
    reasons = []
    if not isinstance(row[6], int):
        reasons.append("edition_is_number")
    if "ي" in row[2] or "ك" in row[2]:
        reasons.append("author_is_persian")
    if not row[8]:
        reasons.append("price_positive")
    con.execute("INSERT INTO books_quarantine VALUES (?, ?, ?, ?)",
                (row[0], row[1], row[2], "، ".join(reasons) or "unique_title_etc"))
con.commit()

print("سطرهای قرنطینه:", int(q("SELECT COUNT(*) AS n FROM books_quarantine").iloc[0, 0]))
print(q("""
    SELECT reason, COUNT(*) AS rows
    FROM books_quarantine
    GROUP BY reason
    ORDER BY rows DESC, reason
"""))
print()
print("قبول‌شده + قرنطینه:",
      int(q("SELECT COUNT(*) AS n FROM books_sane").iloc[0, 0])
      + int(q("SELECT COUNT(*) AS n FROM books_quarantine").iloc[0, 0]))
سطرهای قرنطینه: 222
                                 reason  rows
0                        price_positive   164
1                     edition_is_number    33
2                     author_is_persian    16
3     edition_is_number، price_positive     3
4                      unique_title_etc     3
5     author_is_persian، price_positive     2
6  edition_is_number، author_is_persian     1

قبول‌شده + قرنطینه: 1200

جمعِ دو جدول دقیقاً ۱٬۲۰۰ است — هیچ سطری دور ریخته نشد.

و حالا هر سطرِ قرنطینه یک کارِ مشخص دارد: آن‌هایی که فقط author_is_persian خورده‌اند با یک replace درست می‌شوند (ترمِ ۱ فصلِ ۵)، آن‌هایی که edition_is_number خورده‌اند با تبدیلِ رقمِ فارسی، و آن‌هایی که price_positive خورده‌اند باید یک‌به‌یک نگاه شوند چون بعضی‌شان واقعاً قیمت ندارند.

🔧 اگر کار نکرد: اگر CHECK را بدونِ نام بنویسی، پیامِ خطا دیگر نمی‌گوید کدام قاعده شکست:

con.execute("DROP TABLE IF EXISTS unnamed_rules")
con.execute("""
    CREATE TABLE unnamed_rules (
        id    INTEGER PRIMARY KEY,
        pages INTEGER CHECK (pages > 0),
        year  INTEGER CHECK (year BETWEEN 1500 AND 2100)
    )
""")
con.commit()
try:
    con.execute("INSERT INTO unnamed_rules VALUES (?, ?, ?)", (1, 100, 3000))
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
sqlite3.IntegrityError
CHECK constraint failed: year BETWEEN 1500 AND 2100

پیام به‌جای نام، متنِ خودِ شرط را تکرار می‌کند.

برای یک شرطِ کوتاه بد نیست. برای شرطی که سه خط است، خروجیِ خطا خواندنی نمی‌ماند — و مهم‌تر از خوانایی این است: هیچ نامِ ثابتی وجود ندارد که کدِ برنامه بتواند رویش تصمیم بگیرد. اگر بخواهی به کاربر پیامِ فارسیِ مناسبی نشان بدهی، مجبوری متنِ شرط را در کدت تکرار کنی و اولین باری که شرط را عوض کنی، پیام دروغ می‌شود. به همین دلیل در تمامِ این فصل هر قید نام داشت.

📏 اندازه بگیر: یک سطر یعنی چه؟ در جدولِ ردشده‌های بخشِ ۳ یک سطر یعنی یک پیامِ خطای متمایز، نه یک قید — و به همین دلیل عددهایش با بخشِ ۲ فرق دارند. با کدام شمارشِ دوم سنجیدی؟ با سه‌تا: accepted + rejected که ۱٬۲۰۰ شد، جمعِ تک‌تکِ قیدها (۴۶۲) در برابرِ سطرهای واقعاً ردشده (۴۱۲) که اختلافشان با شمارشِ مستقیمِ هم‌پوشانی توضیح داده شد، و جمعِ books_sane و books_quarantine که باز ۱٬۲۰۰ شد. چه کسی از قلم افتاد؟ ۲۲۲ سطر که در جدولِ سالم نیستند — و اگر جدولِ قرنطینه را نساخته بودیم، هیچ‌جا نبودند؛ نه در گزارش‌ها و نه در فهرستِ کارها.

🤖 از دستیارت بپرس: «فرقِ CHECK با اعتبارسنجی در کدِ برنامه چیست؟» بعد این را بپرس: «اگر قیدی ۲۰ درصدِ سطرهای موجود را رد کند، یعنی داده خراب است یا قید؟» — جوابِ درست می‌گوید هیچ‌کدام را نمی‌شود از روی عدد فهمید و باید به معنیِ ستون نگاه کرد. اگر جواب قاطعانه گفت «داده خراب است»، با مثالِ ۲۳۱ سطرِ بی‌امتیاز ردش کن.

واژه‌های تازهٔ این فصل#

کلمه تلفظ به حروف فارسی یعنی چه
constraint کانسترینت قید؛ قانونی که در اسکیما نوشته می‌شود و پایگاه داده اجرایش می‌کند
NOT NULL نات نال این ستون حتماً باید مقدار داشته باشد
CHECK چک شرطی که هر سطر باید برآورده‌اش کند
DEFAULT دیفالت مقداری که اگر کسی ندهد، خودکار گذاشته می‌شود
named constraint نیمد کانسترینت قیدِ نام‌دار، تا پیامِ خطا بگوید کدام قاعده شکست
quarantine table کوارانتین تیبل جدولِ قرنطینه؛ جایی برای سطرهایی که قید ردشان کرد

تمرین‌ها

اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.

در فصل بعد#

اسکیمای ما حالا از قوانینِ خودش محافظت می‌کند. ولی یک ستون هست که در تمامِ این ترم بی‌سروصدا کنارمان بوده و هیچ قیدی رویش نگذاشتیم: زمان.

فصلِ بعد سراغش می‌رود و با یک عددِ ساده شروع می‌کند: پرسشِ «امانت‌های تا آخرِ ژوئن» با <= روی تاریخ ۱۳٬۰۰۴ جواب می‌دهد و با بازهٔ درست ۱۳٬۴۱۵. چهارصد و یازده رویدادِ روزِ آخر، بی‌صدا، از هر گزارشی که این‌طور نوشته شده حذف می‌شوند.

به آخر این فصل رسیدی!

اگر ساختی و جواب داد، این دکمه مال توست.