در این فصل چه یاد میگیری#
هیچ ابزارِ تازهای. یک فایلِ صادراتیِ ۱٬۲۰۰ سطری با ۱۴ ستون، و یک اسکیمای کامل در آن سرِ دیگر — با دانهبندیِ اعلامشده، کلید، ارجاعِ اجراشونده، قید، و زمانِ درست. بهعلاوهٔ یک مهاجرتِ واقعی روی همان.
و در راه، چهار عدد که هر کدامشان یک تصمیماند: ۱۲۳ املای نویسنده به ۱۲۰ نفر تبدیل میشود، ۶۶ قیمتی که یک CAST ساده صفرشان میکرد نجات پیدا میکنند، ۶ ثبتِ تکراری رد میشوند، و ۲۰۷ رویداد اصلاً وارد نمیشوند — که ۵۵تایش یتیمهای فصلِ ۳ است و بقیهاش نتیجهٔ همان تصمیمی که خودمان دربارهٔ آن شش ثبت گرفتیم.

آخر این فصل میتوانی:
- یک فایلِ خام را به اسکیمایی تبدیل کنی که پایگاه داده خودش از قوانینش محافظت میکند
- در هر مرحله ثابت کنی که چیزی گم یا تکثیر نشده
- سطرهایی را که قید ردشان میکند نگه داری و دربارهٔ هرکدام تصمیم بگیری
- یک مهاجرت بنویسی که اجرای دوبارهاش بیخطر است
قبل از شروع#
همهٔ هشت فصلِ قبل. هیچ چیزِ تازهای اینجا نیست.
دانهبندیِ فایلِ ورودی: یک سطر یعنی یک ثبت در فهرست — یک عنوان که در یکی از شش شعبه ثبت شده. نه یک کتابِ فیزیکی، نه یک عنوانِ متمایز. همین یک جمله تصمیمِ کلیدِ بخشِ ۳ را تعیین میکند.
📓 نوتبوک: نوتبوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و بهترتیب داخلش هست.
| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
catalog_export.csv (روی دیسک) |
۱٬۲۰۰ | آنی |
loan_events |
۲۶٬۱۱۹ | حدودِ یک ثانیه |
members_raw |
۳۰۰ | آنی |
۱. قدمِ صفر: فایل را ببین، عیبها را بشمار#
import csv
with CSV_PATH.open(encoding="utf-8") as fh:
incoming = list(csv.DictReader(fh))
DIGITS = str.maketrans("۰۱۲۳۴۵۶۷۸۹", "0123456789")
def to_int(text):
"""رقم فارسی را برمیگرداند؛ اگر عدد نبود None میدهد، نه صفر."""
cleaned = (text or "").translate(DIGITS)
return int(cleaned) if cleaned.isdigit() else None
def to_persian_letters(text):
return text.replace("ي", "ی").replace("ك", "ک")
print("سطرهای فایل :", len(incoming))
print("ستونها :", len(incoming[0]))
print()
print("املاهای متمایز نویسنده :", len({r["author"] for r in incoming}))
print("بعد از یکسان کردن نویسهها :",
len({to_persian_letters(r["author"]) for r in incoming}))
print("قیمتهایی که با رقم فارسی نوشته شدهاند:",
sum(1 for r in incoming
if not r["price_text"].isascii() and to_int(r["price_text"]) is not None))
print("قیمتهایی که واقعاً عدد نیستند :",
sum(1 for r in incoming if to_int(r["price_text"]) is None))
print("چاپهایی که رقم فارسی دارند :",
sum(1 for r in incoming if not r["edition"].isascii()))
print("سطرهای بدون امتیاز :",
sum(1 for r in incoming if r["rating"] == ""))
سطرهای فایل : 1200
ستونها : 14
املاهای متمایز نویسنده : 123
بعد از یکسان کردن نویسهها : 120
قیمتهایی که با رقم فارسی نوشته شدهاند: 66
قیمتهایی که واقعاً عدد نیستند : 103
چاپهایی که رقم فارسی دارند : 37
سطرهای بدون امتیاز : 231
شش عدد، قبل از اینکه یک جدول ساخته شود.
و مهمترینشان همان دو خطِ قیمت است: ۶۶ قیمت رقمِ فارسی دارند و کاملاً معتبرند، و ۱۰۳تا واقعاً عدد نیستند. اگر مثلِ ترمِ ۱ فقط CAST میزدیم، هر ۱۶۹تا صفر میشدند و هیچ راهی برای جدا کردنِ این دو دسته نمیماند. تابعِ to_int بالا برای مقدارِ غیرعددی None میدهد، نه صفر — و این تفاوت، کلِ درسِ ترمِ ۱ فصلِ ۶ است.
۲. اسکیما#
con.executescript("""
DROP TABLE IF EXISTS p_book_tags; DROP TABLE IF EXISTS p_events;
DROP TABLE IF EXISTS p_books; DROP TABLE IF EXISTS p_tags;
DROP TABLE IF EXISTS p_authors; DROP TABLE IF EXISTS p_branches;
DROP TABLE IF EXISTS p_rejected;
CREATE TABLE p_branches (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
CREATE TABLE p_authors (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE
CONSTRAINT author_is_persian
CHECK (name NOT LIKE '%ي%' AND name NOT LIKE '%ك%')
);
CREATE TABLE p_tags (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
CREATE TABLE p_books (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author_id INTEGER NOT NULL REFERENCES p_authors(id),
branch_id INTEGER NOT NULL REFERENCES p_branches(id),
category 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 CONSTRAINT price_positive
CHECK (price IS NULL OR price > 0),
added_at TEXT NOT NULL CONSTRAINT added_at_is_iso
CHECK (added_at LIKE '____-__-__'),
status TEXT NOT NULL DEFAULT 'shelf',
UNIQUE (title, author_id, branch_id)
);
CREATE TABLE p_book_tags (
book_id INTEGER NOT NULL REFERENCES p_books(id),
tag_id INTEGER NOT NULL REFERENCES p_tags(id),
PRIMARY KEY (book_id, tag_id)
);
CREATE TABLE p_events (
id INTEGER PRIMARY KEY,
book_id INTEGER NOT NULL REFERENCES p_books(id),
member_id INTEGER NOT NULL REFERENCES members_raw(id),
happened_at TEXT NOT NULL CONSTRAINT happened_at_is_iso
CHECK (happened_at LIKE '____-__-__ __:__:__'),
kind TEXT NOT NULL CONSTRAINT kind_is_known
CHECK (kind IN ('out', 'in'))
);
CREATE TABLE p_rejected (
source_id INTEGER PRIMARY KEY,
title TEXT,
reason TEXT NOT NULL
);
""")
con.commit()
print("جدولهای ساختهشده:")
print(q("SELECT name FROM sqlite_master WHERE type = 'table' AND name LIKE 'p_%' ORDER BY name"))
جدولهای ساختهشده:
name
0 p_authors
1 p_book_tags
2 p_books
3 p_branches
4 p_events
5 p_rejected
6 p_tags
هفت جدول، و هر تصمیمی که در این هشت فصل گرفتیم دقیقاً یک جای مشخص در این متن دارد: دانهبندی در نامِ جدولها، کلیدِ جانشین در id، کلیدِ طبیعی در UNIQUE (title, author_id, branch_id)، ارجاع در REFERENCES، قید در CHECKهای نامدار، رابطهٔ چندبهچند در p_book_tags، و زمان در دو قیدِ قالب.
و price عمداً NULL میپذیرد ولی صفر نه — چون «نمیدانیم چند است» با «رایگان است» فرق دارد.
۳. بارگذاری، با شمارشِ کنترلی در هر مرحله#
branches = sorted({r["branch"] for r in incoming})
authors = sorted({to_persian_letters(r["author"]) for r in incoming})
tags = sorted({t for r in incoming for t in r["tags"].split("، ")})
con.executemany("INSERT INTO p_branches (name) VALUES (?)", [(b,) for b in branches])
con.executemany("INSERT INTO p_authors (name) VALUES (?)", [(a,) for a in authors])
con.executemany("INSERT INTO p_tags (name) VALUES (?)", [(t,) for t in tags])
con.commit()
BRANCH_ID = dict(con.execute("SELECT name, id FROM p_branches").fetchall())
AUTHOR_ID = dict(con.execute("SELECT name, id FROM p_authors").fetchall())
TAG_ID = dict(con.execute("SELECT name, id FROM p_tags").fetchall())
print("شعبهها:", len(BRANCH_ID), "| نویسندهها:", len(AUTHOR_ID), "| برچسبها:", len(TAG_ID))
شعبهها: 6 | نویسندهها: 120 | برچسبها: 10
۱۲۰ نویسنده، نه ۱۲۳. یکسان کردنِ نویسهها همینجا انجام شد — یک بار، در لحظهٔ ورود — و قیدِ author_is_persian تضمین میکند که از این به بعد هیچ املای عربیای وارد نشود. این همان چیزی است که ترمِ ۱ فصلِ ۵ گفت «وصله است نه درمان»؛ اینجا درمان است.
حالا خودِ کتابها:
accepted = 0
for row in incoming:
values = (
int(row["id"]), row["title"],
AUTHOR_ID[to_persian_letters(row["author"])], BRANCH_ID[row["branch"]],
row["category"], to_int(row["pages"]), to_int(row["year"]),
to_int(row["edition"]),
float(row["rating"]) if row["rating"] else None,
to_int(row["price_text"]), row["added_at"],
)
try:
con.execute("""
INSERT INTO p_books (id, title, author_id, branch_id, category,
pages, year, edition, rating, price, added_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""", values)
accepted += 1
except Exception as error:
con.execute("INSERT INTO p_rejected VALUES (?, ?, ?)",
(int(row["id"]), row["title"], str(error)))
con.commit()
print("کتابهای پذیرفتهشده:", accepted)
print("کتابهای ردشده :", int(q("SELECT COUNT(*) AS n FROM p_rejected").iloc[0, 0]))
print("جمع :", accepted + int(q("SELECT COUNT(*) AS n FROM p_rejected").iloc[0, 0]))
print()
print(q("SELECT reason, COUNT(*) AS rows FROM p_rejected GROUP BY reason"))
کتابهای پذیرفتهشده: 1194
کتابهای ردشده : 6
جمع : 1200
reason rows
0 UNIQUE constraint failed: p_books.title, p_boo... 6
۱٬۱۹۴ قبول، ۶ رد، و جمعشان ۱٬۲۰۰.
آن شش سطر همان شش جفتی هستند که فصلِ ۲ پیدایشان کرد: عنوان و نویسنده و شعبهٔ یکسان. و توجه کن که این بار حذف نشدند، ثبت شدند — با شناسه و دلیل، در جدولی که میشود رویش تصمیم گرفت.
۴. برچسبها و رویدادها#
kept = {row[0] for row in con.execute("SELECT id FROM p_books").fetchall()}
links = [(int(r["id"]), TAG_ID[t])
for r in incoming if int(r["id"]) in kept
for t in r["tags"].split("، ")]
con.executemany("INSERT INTO p_book_tags VALUES (?, ?)", links)
con.commit()
loaded = rejected_events = 0
for event in con.execute("""
SELECT id, catalog_id, member_id, happened_at, kind FROM loan_events ORDER BY id
""").fetchall():
try:
con.execute("INSERT INTO p_events VALUES (?, ?, ?, ?, ?)", event)
loaded += 1
except Exception:
rejected_events += 1
con.commit()
orphans = int(q("""
SELECT COUNT(*) AS n FROM loan_events AS e
WHERE NOT EXISTS (SELECT 1 FROM catalog_raw AS c WHERE c.id = e.catalog_id)
OR NOT EXISTS (SELECT 1 FROM members_raw AS m WHERE m.id = e.member_id)
""").iloc[0, 0])
print("پیوندهای برچسب:", len(links))
print()
print("رویدادهای وارد شده:", loaded)
print("رویدادهای رد شده :", rejected_events)
print(" از اینها، یتیمهای فصل ۳ :", orphans)
print(" و رویدادهای شش کتابِ ردشده :", rejected_events - orphans)
print("جمع :", loaded + rejected_events, "| سطرهای loan_events: 26119")
پیوندهای برچسب: 2175
رویدادهای وارد شده: 25912
رویدادهای رد شده : 207
از اینها، یتیمهای فصل ۳ : 55
و رویدادهای شش کتابِ ردشده : 152
جمع : 26119 | سطرهای loan_events: 26119
دویست و هفت رویداد وارد نشدند، و دو دلیلِ کاملاً متفاوت دارند.
پنجاهوپنجتایشان همان یتیمهای فصلِ ۳ هستند — شش کتاب و دو عضوی که وجود ندارند. ۱۵۲تای دیگر رویدادهای همان شش ثبتیاند که خودمان در بخشِ ۳ ردشان کردیم. و آن تصمیم قطعی نیست: تمرینِ ستارهدار نشان میدهد آن شش ثبت احتمالاً اصلاً تکراری نیستند. یعنی یک تصمیم در جدولِ کتابها، سطرهایی را در جدولِ رویدادها هم انداخت — و این دقیقاً همان چیزی است که یک کلیدِ خارجیِ اجراشونده باید بکند: نگذارد داده به چیزی اشاره کند که تصمیم گرفتهای نداشته باشی.
۵. اثبات: نه گم شد، نه تکثیر#
rejected_ids = {row[0] for row in con.execute("SELECT source_id FROM p_rejected").fetchall()}
csv_links = sum(len(r["tags"].split("، ")) for r in incoming)
lost_links = sum(len(r["tags"].split("، ")) for r in incoming
if int(r["id"]) in rejected_ids)
checks = [
("کتابها + ردشدهها",
accepted + int(q("SELECT COUNT(*) AS n FROM p_rejected").iloc[0, 0]), 1200),
("رویدادها + ردشدهها", loaded + rejected_events, 26119),
("پیوندهای برچسب",
int(q("SELECT COUNT(*) AS n FROM p_book_tags").iloc[0, 0]),
csv_links - lost_links),
("نویسندههای یکتا",
int(q("SELECT COUNT(*) AS n FROM p_authors").iloc[0, 0]), 120),
("نقضهای کلید خارجی",
len(pd.read_sql_query("PRAGMA foreign_key_check", con)), 0),
]
for label, got, want in checks:
print(f" {label:<22} {got:>7} انتظار {want:>7} {'✔' if got == want else '✘'}")
print()
print(q("""
SELECT t.name, COUNT(*) AS books
FROM p_book_tags AS bt
JOIN p_tags AS t ON t.id = bt.tag_id
GROUP BY t.id, t.name
ORDER BY books DESC, t.name
LIMIT 3
"""))
کتابها + ردشدهها 1200 انتظار 1200 ✔
رویدادها + ردشدهها 26119 انتظار 26119 ✔
پیوندهای برچسب 2175 انتظار 2175 ✔
نویسندههای یکتا 120 انتظار 120 ✔
نقضهای کلید خارجی 0 انتظار 0 ✔
name books
0 ادبیات کودک 249
1 تاریخ 231
2 چاپ نایاب 226
پنج آزمون، هر پنج پاس.
✅ چک کن: هر پنج خط باید علامتِ درست بگیرند. اگر «نقضهای کلید خارجی» صفر نشد، جایی داده را با پرچمِ خاموش وارد کردهای — و هیچکدام از چهار عددِ دیگر دیگر معنایی ندارند.
۶. یک مهاجرتِ واقعی#
فردا کسی میگوید باید بدانیم هر کتاب کِی آخرین بار امانت رفته. قاعدهٔ فقط-افزودنی: یک ستون اضافه میشود و هیچچیزی برداشته نمیشود.
MIGRATIONS = [
(1, "ALTER TABLE p_books ADD COLUMN last_loan_at TEXT"),
(2, """UPDATE p_books
SET last_loan_at = (SELECT MAX(e.happened_at) FROM p_events AS e
WHERE e.book_id = p_books.id AND e.kind = 'out')"""),
(3, "CREATE INDEX IF NOT EXISTS idx_events_book ON p_events(book_id)"),
]
def migrate(connection):
version = connection.execute("PRAGMA user_version").fetchone()[0]
applied = []
for number, statement in MIGRATIONS:
if number <= version:
continue
connection.execute(statement)
connection.execute(f"PRAGMA user_version = {number}")
applied.append(number)
connection.commit()
return applied
con.execute("PRAGMA user_version = 0")
con.commit()
print("اجرای اول:", migrate(con), "| نسخه:", con.execute("PRAGMA user_version").fetchone()[0])
print("اجرای دوم:", migrate(con), "| نسخه:", con.execute("PRAGMA user_version").fetchone()[0])
print()
print("کتابهایی که ستون تازهشان پر شد:",
int(q("SELECT COUNT(last_loan_at) AS n FROM p_books").iloc[0, 0]))
print("کتابهایی که هرگز امانت نرفتهاند :",
int(q("SELECT COUNT(*) AS n FROM p_books WHERE last_loan_at IS NULL").iloc[0, 0]))
print()
print(q("""
SELECT b.title, b.last_loan_at
FROM p_books AS b
WHERE b.last_loan_at IS NOT NULL
ORDER BY b.last_loan_at DESC, b.id
LIMIT 3
"""))
اجرای اول: [1, 2, 3] | نسخه: 3
اجرای دوم: [] | نسخه: 3
کتابهایی که ستون تازهشان پر شد: 994
کتابهایی که هرگز امانت نرفتهاند : 200
title last_loan_at
0 کویر و برف 2025-06-30 22:48:03
1 بازگشت به خاک 2025-06-30 21:29:35
2 روزهای چراغ 2025-06-30 20:57:20
اجرای دوم هیچ کاری نکرد، و ستونِ تازه برای کتابِ بیامانت NULL ماند — نه یک تاریخِ ساختگی.
و توجه کن که این ستون همان معاملهٔ فصلِ ۵ است: یک واقعیتِ محاسبهشده که کنارِ کتاب نشسته و از این به بعد باید سازگار نگه داشته شود. پس کنارِ این مهاجرت یک آزمونِ سازگاری هم لازم است که هر شب اجرا شود و جوابش صفر بماند. آن آزمون خودش مهاجرت نیست، چون مهاجرت دقیقاً یک بار اجرا میشود.
🔧 اگر کار نکرد: اگر قیدِ قالبِ تاریخ را جدی نگیری، اولین سطری که با قالبِ دیگری بیاید همانجا رد میشود — و این خبرِ خوبی است:
try:
con.execute("""
INSERT INTO p_books (id, title, author_id, branch_id, category,
pages, year, edition, added_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
""", (9001, "کتاب آزمایشی", 1, 1, "علم", 100, 2020, 1, "30/06/2025"))
except Exception as error:
print(type(error).__module__ + "." + type(error).__name__)
print(error)
con.rollback()
sqlite3.IntegrityError
CHECK constraint failed: added_at_is_iso
۷. گزارشِ نهایی#
سه جمله که میشود رویشان تصمیم گرفت، و هر جمله عددش را با خودش میآورد:
- ۱٬۱۹۴ ثبت از ۱٬۲۰۰ ثبتِ فایل وارد اسکیمای تازه شد — ششتایش رد شد چون عنوان و نویسنده و شعبهشان تکراری بود، و هر شش در
p_rejectedبا دلیلشان ثبتاند. اینها حذف نشدهاند؛ منتظرِ تصمیماند. - ۱۲۳ املا به ۱۲۰ نویسنده تبدیل شد و ۶۶ قیمت نجات پیدا کرد — و از این به بعد هیچ املای عربی و هیچ قیمتِ صفرِ ساختگی وارد نمیشود، چون قیدش در اسکیماست نه در کد.
- رویدادهایی که به کتاب یا عضوِ ناموجود اشاره میکنند دیگر وارد نمیشوند — و همانطور که فصلِ ۳ گفت، این جلوی خونریزیِ تازه را میگیرد، نه اینکه گذشته را پاک کند.
و آنچه نمیدانیم، بخشی از همین گزارش است: ۱۰۳ ثبت قیمتشان NULL است و در هیچ میانگینِ قیمتی نیستند؛ ۲۳۱ ثبتِ فایل امتیاز ندارند و همانطور که ترمِ ۱ نشان داد این جاافتادگی تصادفی نیست؛ و مدلِ ما هنوز نسخهٔ فیزیکی ندارد، پس نمیتواند بگوید یک عنوانِ دوجلدی همین حالا کجاست. دو ستونِ فایل هم عمداً منتقل نشدند: audio_minutes که در هر ۱٬۲۰۰ سطر خالی است، و borrowed که شمارندهای است با منبعِ نامعلوم و p_events جایش را میگیرد.
📏 اندازه بگیر: یک سطر یعنی چه؟ در
p_booksیک ثبت در فهرست — همان دانهبندیِ فایلِ ورودی، دستنخورده. درp_book_tagsیک جفتِ «کتاب و برچسب»، و درp_eventsیک رویداد. با کدام شمارشِ دوم سنجیدی؟ با پنج آزمونِ بخشِ ۵، که هر کدامشان یک جمعِ مستقل را با یک عددِ از پیش معلوم میسنجد. چه کسی از قلم افتاد؟ شش ثبتِ تکراری و رویدادهایشان، ۵۵ رویدادِ یتیم، و ۴۷ عضوی که از فصلِ ۱ میدانیم هرگز کتابی نبردهاند و درp_eventsهیچ سطری ندارند.
🤖 از دستیارت بپرس: «برای تبدیلِ یک
CSVکثیف به یک اسکیمای رابطهای، ترتیبِ کارها چه باید باشد؟» بعد این را بپرس: «چرا سطرهایی را که قید رد میکند باید در جدولِ جدا نگه داشت بهجای اینکه دور ریخته شوند؟» — جوابِ درست میگوید ردشدن یعنی «هنوز تصمیم نگرفتهایم»، نه «بیارزش است». اگر جواب فقط گفت «برای اشکالیابی»، نصفِ ماجرا را گفته.
واژههای تازهٔ این فصل#
| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| schema design | اسکیما دیزاین | طراحیِ اسکیما؛ تصمیم دربارهٔ جدولها و ستونها و قیدها |
| ingestion | اینجستیون | مرحلهٔ ورودِ دادهٔ خام به پایگاه داده |
| lookup table | لوکآپ تیبل | جدولِ کوچکی مثلِ p_tags که فقط نام و شناسه دارد |
| reconciliation | ریکانسیلیایشن | تطبیق؛ سنجیدنِ جمعِ هر مرحله با یک شمارشِ مستقل |
تمرینها
اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.
در فصل بعد#
ترمِ ۴ تمام شد. حالا میتوانی از یک فایلِ خام به اسکیمایی برسی که خودش از قوانینش محافظت میکند، و فردا عوضش کنی بدونِ اینکه چیزی بشکند.
ولی در تمامِ این ترم یک چیز را نادیده گرفتیم: سرعت. ترمیمِ ستونِ خلاصه در فصلِ ۵ همین ترم دو ثانیه طول کشید و ما فقط رد شدیم. ترمِ ۵ سراغِ همان میرود — با دادهٔ بزرگتر، با EXPLAIN QUERY PLAN بهعنوان ذرهبین، و با قاعدهای که هیچ استثنا ندارد: هیچ ادعای سرعتی بدونِ زمانِ قبل، زمانِ بعد، و نقشهٔ اجرا پذیرفته نیست.
به آخر این فصل رسیدی!
اگر ساختی و جواب داد، این دکمه مال توست.