دیتابیس و SQLدیتابیس در کار واقعیدرس ۱۰ از ۱۰

پروژه: دیتابیس فروشگاه

پروژه‌ی پایانی دوره‌ی SQL: یک دیتابیس فروشگاه کامل از صفر، با فایل ساختار (schema.sql)، داده‌ی آزمایشی تکرارپذیر با generate_series، ثبت سفارش اتمیک با تراکنش و قفل سطر، گزارش‌ها به‌صورت VIEW، نقش‌های جدا برای برنامه و گزارش، بررسی سرعت با EXPLAIN، و بکاپ خودکار با بررسی بازیابی.

حدود ۲۰ دقیقه مطالعه
۳ تمرین با جواب
مقدماتی تا متوسط
به‌روزرسانی: ۲۹ مهر ۱۴۰۵

توی این پروژه همه‌ی چیزهایی را که در دوره یاد گرفتی کنار هم می‌گذاری و یک دیتابیس فروشگاه قابل‌اعتماد از صفر می‌سازی: ساختار را در یک فایل نگه می‌داری که هر بار دقیقاً همان دیتابیس را بسازد، ۳۰۰۰ سفارش آزمایشی تولید می‌کنی، ثبت سفارش را طوری می‌نویسی که هیچ‌وقت موجودی منفی یا سفارش بی‌قلم نسازد، گزارش‌های مدیریتی را به‌صورت VIEW آماده می‌کنی، برای برنامه و گزارش نقش جدا با کمترین دسترسی می‌سازی، سرعت را با EXPLAIN بررسی می‌کنی و در آخر بکاپ خودکار با بررسی بازیابی راه می‌اندازی.

مسئله

تا اینجا هر درس یک مهارت جدا بود: یک JOIN اینجا، یک ایندکس آنجا. در کار واقعی این‌ها با هم می‌آیند و سؤال‌ها این شکلی‌اند: «اگر فردا سرور را از دست بدهیم، دیتابیس را دقیقاً همین‌طور دوباره می‌سازیم؟»، «اگر دو نفر هم‌زمان آخرین سوئیچ را بخرند چه می‌شود؟»، «تیم مالی چطور گزارش ماهانه می‌گیرد بدون اینکه به اطلاعات شخصی مشتری‌ها دسترسی داشته باشد؟»، «بکاپ دیشب واقعاً برمی‌گردد؟». این پروژه به همه‌ی این سؤال‌ها جواب عملی می‌دهد.

نقشه‌ی پروژه

همه‌ی فایل‌ها در پوشه‌ی ~/store:

هر فایل یک مسئولیت دارد؛ ترتیب اجرا: schema، seed، reports، بعد roles و grants.

mkdir -p ~/store
cd ~/store
createdb store

قدم ۱: ساختار، `schema.sql`

-- schema.sql: ساختار دیتابیس فروشگاه
BEGIN;

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    city TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    price INTEGER NOT NULL CHECK (price > 0),
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers (id),
    status TEXT NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'cancelled')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    order_id INTEGER NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products (id),
    qty INTEGER NOT NULL CHECK (qty > 0),
    unit_price INTEGER NOT NULL CHECK (unit_price > 0),
    PRIMARY KEY (order_id, product_id)
);

CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX orders_created_at_idx ON orders (created_at);
CREATE INDEX order_items_product_id_idx ON order_items (product_id);

COMMIT;

تصمیم‌های طراحی، هر کدام از یک درس:

قیدها قانون‌های کسب‌وکارند (درس طراحی): ایمیل یکتا، قیمت مثبت، موجودی هرگز منفی (CHECK (stock >= 0)؛ قدم ۳ به آن تکیه می‌کند)، وضعیت فقط سه مقدار.
sku (کد کالا) یکتا و مستقل از id: برنامه‌ها و انبار با کد کار می‌کنند، نه با شماره‌ی داخلی دیتابیس.
TIMESTAMPTZ برای زمان‌ها، unit_price به‌عنوان عکس لحظه‌ای قیمت، ON DELETE CASCADE فقط برای اقلام سفارش.
ایندکس روی کلیدهای خارجی و زمان سفارش (درس ایندکس): «سفارش‌های یک مشتری» و «سفارش‌های یک ماه» پرتکرارترین کوئری‌ها هستند.
همه داخل BEGIN/COMMIT: اگر یک خط خطا بدهد، نیمی از جدول‌ها ساخته نمی‌شوند.

psql store -f schema.sql

BEGIN
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
COMMIT

قدم ۲: داده‌ی آزمایشی، `seed.sql`

-- seed.sql: داده‌ی آزمایشی تکرارپذیر
SELECT setseed(0.7);

INSERT INTO products (sku, name, category, price, stock) VALUES
    ('BK-PY', 'کتاب پایتون', 'کتاب', 320000, 40),
    ('BK-LX', 'کتاب لینوکس', 'کتاب', 280000, 35),
    ('BK-DK', 'کتاب داکر', 'کتاب', 350000, 20),
    ('AC-MS', 'ماوس بی‌سیم', 'لوازم جانبی', 450000, 60),
    ('AC-KB', 'کیبورد مکانیکی', 'لوازم جانبی', 1890000, 15),
    ('AC-HP', 'هدفون', 'لوازم جانبی', 1250000, 12),
    ('ST-HD', 'هارد اکسترنال', 'ذخیره‌سازی', 3200000, 8),
    ('ST-FL', 'فلش 64 گیگ', 'ذخیره‌سازی', 290000, 100),
    ('ST-SS', 'SSD 512 گیگ', 'ذخیره‌سازی', 2750000, 10),
    ('NW-CB', 'کابل شبکه', 'شبکه', 95000, 300),
    ('NW-RT', 'روتر وای‌فای', 'شبکه', 2400000, 6),
    ('NW-SW', 'سوئیچ 8 پورت', 'شبکه', 1650000, 4);

INSERT INTO customers (name, email, city, created_at)
SELECT 'مشتری ' || n,
       'user' || n || '@example.com',
       (ARRAY['تهران', 'تهران', 'تهران', 'اصفهان', 'شیراز', 'تبریز', 'مشهد'])[1 + floor(random() * 7)::int],
       '2026-01-01'::timestamptz + random() * interval '240 days'
FROM generate_series(1, 200) AS n;

INSERT INTO orders (customer_id, status, created_at)
SELECT 1 + floor(random() * 200)::int,
       (ARRAY['paid', 'paid', 'paid', 'paid', 'pending', 'cancelled'])[1 + floor(random() * 6)::int],
       '2026-05-01'::timestamptz + random() * interval '170 days'
FROM generate_series(1, 3000);

-- هر سفارش یک قلم اصلی؛ سفارش‌های زوج یک قلم دوم هم دارند
INSERT INTO order_items (order_id, product_id, qty, unit_price)
SELECT o.id, p.id, 1 + o.id % 3, p.price
FROM orders o
JOIN products p ON p.id = 1 + (o.id * 7) % 12;

INSERT INTO order_items (order_id, product_id, qty, unit_price)
SELECT o.id, p.id, 1, p.price
FROM orders o
JOIN products p ON p.id = 1 + (o.id * 5) % 12
WHERE o.id % 2 = 0 AND (o.id * 5) % 12 <> (o.id * 7) % 12;

ANALYZE customers, products, orders, order_items;

همان تکنیک‌های درس ایندکس: generate_series برای ساختن سطر، random() با setseed تا عددهای تو با درس یکی باشد، و آرایه‌ای که «تهران» را سه بار دارد تا مشتری‌های تهرانی بیشتر باشند (مثل واقعیت). floor(random() * 7) عددی صحیح از ۰ تا ۶ می‌دهد که همه‌ی خانه‌ها شانس برابر داشته باشند. قلم‌های سفارش با یک فرمول ثابت ((o.id * 7) % 12، باقی‌مانده‌ی تقسیم) ساخته می‌شوند تا بدون تصادف، محصول‌ها پخش شوند؛ شرط آخر جلوی قلم تکراری در یک سفارش را می‌گیرد (کلید اصلی دوتایی اجازه نمی‌دهد).

psql store -f seed.sql
psql store -c "SELECT (SELECT count(*) FROM customers) AS customers, (SELECT count(*) FROM orders) AS orders, (SELECT count(*) FROM order_items) AS items"
psql store -c "SELECT * FROM orders ORDER BY id LIMIT 3"

 setseed
---------

(1 row)

INSERT 0 12
INSERT 0 200
INSERT 0 3000
INSERT 0 3000
INSERT 0 1000
ANALYZE
 customers | orders | items
-----------+--------+-------
       200 |   3000 |  4000
(1 row)

 id | customer_id | status |            created_at
----+-------------+--------+----------------------------------
  1 |         115 | paid   | 2026-06-06 12:18:20.136875+03:30
  2 |           4 | paid   | 2026-05-30 19:49:59.199745+03:30
  3 |         131 | paid   | 2026-09-19 19:18:20.161544+03:30
(3 rows)

(اگر منطقه‌ی زمانی سیستمت تهران نیست، ساعت‌ها و +03:30 فرق می‌کنند؛ لحظه‌ها همان‌اند.) حالا هر وقت خواستی از صفر شروع کنی: dropdb store && createdb store && psql store -f schema.sql && psql store -f seed.sql.

قدم ۳: ثبت سفارش، `place-order.sql`

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

-- place-order.sql: ثبت یک سفارش تک‌قلمی؛ یا همه‌ی قدم‌ها یا هیچ‌کدام
-- اجرا: psql store -v customer=7 -v sku=NW-SW -v qty=2 -f place-order.sql
\set ON_ERROR_STOP on

BEGIN;

-- ۱) محصول را پیدا و قفل کن تا سفارش هم‌زمانِ دیگری موجودی را عوض نکند
SELECT id AS product_id, price
FROM products
WHERE sku = :'sku'
FOR UPDATE \gset

-- ۲) از موجودی کم کن (قید CHECK اجازه‌ی منفی شدن نمی‌دهد)
UPDATE products SET stock = stock - :'qty' WHERE id = :product_id;

-- ۳) سفارش و قلمش را ثبت کن
INSERT INTO orders (customer_id, status) VALUES (:'customer', 'paid')
RETURNING id AS order_id \gset

INSERT INTO order_items (order_id, product_id, qty, unit_price)
VALUES (:order_id, :product_id, :'qty', :price);

COMMIT;

\echo 'سفارش' :order_id 'ثبت شد'

سه چیز تازه:

\set ON_ERROR_STOP on: psql با اولین خطا متوقف می‌شود (درس join دیدی که پیش‌فرضش ادامه دادن است). چون COMMIT هرگز اجرا نمی‌شود، اتصال بسته و تراکنش برگردانده می‌شود.
\gset به‌جای ;: نتیجه‌ی یک SELECT تک‌سطری را در متغیرهای psql هم‌نام ستون‌ها می‌ریزد (:product_id، :price، :order_id). اگر سطری برنگردد، خطاست.
FOR UPDATE: سطر محصول را تا آخر تراکنش قفل می‌کند. اگر مشتری دیگری هم‌زمان همین کالا را بخرد، تراکنش او روی همین SELECT منتظر می‌ماند تا این یکی تمام شود و بعد موجودی جدید را می‌بیند.

ورودی‌ها با :'...' (پارامتر امن، درس امنیت) وارد می‌شوند. امتحان کن. سوئیچ ۴ عدد موجودی دارد:

psql store -v customer=7 -v sku=NW-SW -v qty=2 -f place-order.sql; echo "exit=$?"

BEGIN
UPDATE 1
INSERT 0 1
INSERT 0 1
COMMIT
سفارش 3001 ثبت شد
exit=0

حالا سه حالت خطا: موجودی کافی نیست، کد کالا اشتباه است، مشتری وجود ندارد:

psql store -v customer=7 -v sku=NW-SW -v qty=5 -f place-order.sql; echo "exit=$?"
psql store -v customer=7 -v sku=XX-00 -v qty=1 -f place-order.sql; echo "exit=$?"
psql store -v customer=999 -v sku=NW-SW -v qty=1 -f place-order.sql; echo "exit=$?"

BEGIN
psql:place-order.sql:14: ERROR:  new row for relation "products" violates check constraint "products_stock_check"
DETAIL:  Failing row contains (12, NW-SW, سوئیچ 8 پورت, شبکه, 1650000, -3).
exit=3
BEGIN
psql:place-order.sql:11: error: no rows returned for \gset
exit=3
BEGIN
UPDATE 1
psql:place-order.sql:18: ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(999) is not present in table "customers".
exit=3

حالت سوم مهم است: موجودی کم شد (UPDATE 1)، بعد سفارش رد شد. آیا سوئیچ‌ها گم شدند؟

psql store -c "SELECT sku, stock FROM products WHERE sku = 'NW-SW'" \
           -c "SELECT id, customer_id, status FROM orders WHERE id > 2999 ORDER BY id"

  sku  | stock
-------+-------
 NW-SW |     2
(1 row)

  id  | customer_id | status
------+-------------+--------
 3000 |          41 | paid
 3001 |           7 | paid
(2 rows)

موجودی دقیقاً ۲ است (۴ منهای سفارش موفق) و هیچ سفارش نیمه‌کاره‌ای نیست. کد خروج ۳ هم یعنی «اسکریپت با خطا متوقف شد»، پس برنامه یا اسکریپت bash ای که این را صدا می‌زند می‌فهمد شکست خورده.

⚡ بررسی سریع

اگر FOR UPDATE را حذف کنی، دو سفارش هم‌زمان برای آخرین سوئیچ چه می‌شوند؟

جواب درست: یکی موفق؛ دیگری با قید CHECK رد می‌شود چون UPDATE روی همان سطر منتظر اولی می‌ماند

UPDATE هم خودش سطر را قفل می‌کند و قید CHECK آخرین خط دفاع است؛ FOR UPDATE تضمین می‌کند قیمت و موجودی‌ای که خواندی تا آخر تراکنش عوض نشود.

قدم ۴: گزارش‌ها، `reports.sql`

گزارش‌ها کوئری‌های طولانی‌اند که هر کسی نباید هر بار دوباره بنویسد. VIEW یک کوئری ذخیره‌شده با اسم است که مثل جدول از آن SELECT می‌گیری؛ داده‌ای نگه نمی‌دارد و هر بار از جدول‌های اصلی حساب می‌شود.

-- reports.sql: گزارش‌ها به‌صورت VIEW
CREATE OR REPLACE VIEW monthly_sales AS
SELECT to_char(o.created_at, 'YYYY-MM') AS month,
       count(DISTINCT o.id) AS orders,
       sum(oi.qty * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'paid'
GROUP BY month;

CREATE OR REPLACE VIEW top_customers AS
SELECT c.id, c.name, c.city,
       count(DISTINCT o.id) AS orders,
       sum(oi.qty * oi.unit_price) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.id, c.name, c.city;

CREATE OR REPLACE VIEW low_stock AS
SELECT p.sku, p.name, p.stock,
       coalesce(sum(oi.qty) FILTER (WHERE o.status = 'paid'), 0) AS sold
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
LEFT JOIN orders o ON o.id = oi.order_id
GROUP BY p.id, p.sku, p.name, p.stock
HAVING p.stock < 10;

دقت کن: count(DISTINCT o.id) نه count(*)، چون بعد از JOIN با اقلام، هر سفارش دوقلمی دو سطر دارد (اشتباه رایج ۵ درس join). در low_stock، LEFT JOIN محصول‌های هرگز فروخته‌نشده را هم نگه می‌دارد و FILTER فقط فروش‌های پرداخت‌شده را می‌شمارد.

psql store -f reports.sql
psql store -c "SELECT * FROM monthly_sales ORDER BY month"
psql store -c "SELECT name, city, orders, spent FROM top_customers ORDER BY spent DESC LIMIT 5"
psql store -c "SELECT * FROM low_stock ORDER BY stock"

CREATE VIEW
CREATE VIEW
CREATE VIEW
  month  | orders |  revenue
---------+--------+------------
 2026-05 |    364 | 1166125000
 2026-06 |    330 | 1019570000
 2026-07 |    404 | 1381825000
 2026-08 |    372 | 1246185000
 2026-09 |    338 | 1046815000
 2026-10 |    187 |  566895000
(6 rows)

   name    | city  | orders |  spent
-----------+-------+--------+----------
 مشتری 21  | تهران |     20 | 70875000
 مشتری 131 | تهران |     13 | 68615000
 مشتری 99  | تهران |     17 | 62815000
 مشتری 39  | تبریز |     15 | 62600000
 مشتری 87  | تبریز |     16 | 62415000
(5 rows)

  sku  |     name      | stock | sold
-------+---------------+-------+------
 NW-SW | سوئیچ 8 پورت  |     2 |  494
 NW-RT | روتر وای‌فای   |     6 |  482
 ST-HD | هارد اکسترنال |     8 |  172
(3 rows)

سفارش ۳۰۰۱ (دو سوئیچ) همین حالا در گزارش اکتبر و در sold سوئیچ دیده می‌شود: VIEW همیشه داده‌ی زنده را نشان می‌دهد. \dv فهرست VIEWها را می‌دهد.

قدم ۵: نقش‌ها و دسترسی

برنامه‌ی فروشگاه (store_app) سفارش ثبت می‌کند ولی چیزی حذف نمی‌کند؛ تیم گزارش (store_report) فقط VIEWها را می‌بیند، نه جدول مشتری‌ها با ایمیل‌هایشان.

-- roles.sql: با sudo -u postgres psql -f roles.sql اجرا کن
CREATE ROLE store_app LOGIN PASSWORD 'Store-App-2026!';
CREATE ROLE store_report LOGIN PASSWORD 'Store-Report-2026!';

-- grants.sql: با صاحب جدول‌ها اجرا کن (بعد از schema.sql و reports.sql)
GRANT SELECT, UPDATE ON products TO store_app;
GRANT SELECT ON customers TO store_app;
GRANT SELECT, INSERT ON orders, order_items TO store_app;
GRANT USAGE ON SEQUENCE orders_id_seq TO store_app;

GRANT SELECT ON monthly_sales, top_customers, low_stock TO store_report;

roles.sql را postgres اجرا می‌کند ولی فایل در پوشه‌ی توست و postgres اجازه‌ی خواندن پوشه‌ی خانه‌ی تو را ندارد؛ برای همین فایل را با < (ورودی استاندارد، که شِل تو باز می‌کند) به او بده:

sudo -u postgres psql < roles.sql
psql store -f grants.sql

CREATE ROLE
CREATE ROLE
GRANT
GRANT
GRANT
GRANT
GRANT

امتحان با هر نقش (رمز را وقتی پرسید وارد کن، یا در ‎~/.pgpass بگذار):

psql -h localhost -U store_app store -v customer=12 -v sku=BK-PY -v qty=1 -f place-order.sql
psql -h localhost -U store_app store -c "DELETE FROM orders WHERE id = 1"
psql -h localhost -U store_report store -c "SELECT * FROM monthly_sales ORDER BY month DESC LIMIT 2" \
                                        -c "SELECT count(*) FROM customers"

BEGIN
UPDATE 1
INSERT 0 1
INSERT 0 1
COMMIT
سفارش 3003 ثبت شد
ERROR:  permission denied for table orders
  month  | orders |  revenue
---------+--------+------------
 2026-10 |    188 |  567215000
 2026-09 |    338 | 1046815000
(2 rows)

ERROR:  permission denied for table customers

برنامه سفارش ثبت کرد ولی نمی‌تواند حذف کند. گزارش‌گیر VIEW را می‌خواند، با اینکه به جدول‌های زیرش دسترسی ندارد: VIEW با دسترسی صاحبش اجرا می‌شود. VIEW اینجا یک مرز امنیتی هم هست: دقیقاً همان ستون‌ها و سطرهایی که در آن نوشته‌ای، و نه بیشتر.

و چرا ۳۰۰۳، نه ۳۰۰۲؟ سفارش ناموفق مشتری ۹۹۹ در قدم ۳ یک عدد از sequence گرفته بود و با ROLLBACK آن عدد برنمی‌گردد. شناسه‌ها یکتااند، نه پشت‌سرهم (اشتباه رایج ۵ درس insert-update-delete).

قدم ۶: سرعت

پرتکرارترین کوئری برنامه: «۵ سفارش آخر یک مشتری» (صفحه‌ی «سفارش‌های من»):

psql store -c "EXPLAIN ANALYZE SELECT id, status, created_at FROM orders WHERE customer_id = 21 ORDER BY created_at DESC LIMIT 5"

                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=26.31..26.32 rows=5 width=18) (actual time=0.070..0.071 rows=5 loops=1)
   ->  Sort  (cost=26.31..26.37 rows=26 width=18) (actual time=0.069..0.069 rows=5 loops=1)
         Sort Key: created_at DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Bitmap Heap Scan on orders  (cost=4.48..25.87 rows=26 width=18) (actual time=0.019..0.043 rows=26 loops=1)
               Recheck Cond: (customer_id = 21)
               Heap Blocks: exact=14
               ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.48 rows=26 width=0) (actual time=0.012..0.012 rows=26 loops=1)
                     Index Cond: (customer_id = 21)
 Planning Time: 0.444 ms
 Execution Time: 0.110 ms
(11 rows)

ایندکس orders_customer_id_idx از schema.sql استفاده شد و ۲۶ سفارش این مشتری در ۰٫۱ میلی‌ثانیه پیدا و مرتب شدند. روی ۳۰۰۰ سفارش این کاملاً کافی است. اگر یک مشتری هزاران سفارش داشت، Sort گران می‌شد و ایندکس (customer_id, created_at) (تمرین متوسط درس ایندکس) آن را حذف می‌کرد. ایندکس را وقتی اضافه کن که اندازه‌گیری بگوید، نه قبلش.

قدم ۷: بکاپ و بررسی بازیابی

اسکریپت backup-db.sh درس بکاپ اسم دیتابیس را به‌عنوان ورودی می‌گیرد، پس همان را استفاده کن. برای verify-backup.sh نسخه‌ای کلی‌تر بنویس که برای هر دیتابیسی کار کند (تمرین سخت پایین)، و بعد:

~/bin/backup-db.sh store && ~/bin/verify-backup.sh store

2026-10-21 20:36:26 ok /home/ali/backups/store-2026-10-21_2036.dump 72K
ok: /home/ali/backups/store-2026-10-21_2036.dump matches (4 tables)

و در crontab -e:

30 2 * * * $HOME/bin/backup-db.sh store >> $HOME/backups/backup.log 2>&1
0 4 * * 0  $HOME/bin/verify-backup.sh store >> $HOME/backups/backup.log 2>&1

بکاپ هر شب، بررسی بازیابی هر یکشنبه ساعت ۴ صبح. یک قدم بیشتر: اگر verify-backup.sh شکست خورد، ایمیل یا پیام بفرست (cron خروجی را به ایمیل کاربر می‌فرستد اگر MAILTO تنظیم شده باشد) و فایل‌های بکاپ را با rsync به سرور دیگری کپی کن (قانون ۳-۲-۱).

مسیر کامل پروژه: از فایل‌های SQL در گیت تا بکاپ بررسی‌شده.

چک‌لیست نهایی

✓ چک‌لیست پروژه‌ی دیتابیس فروشگاه

schema.sql داخل یک تراکنش، با قیدهای کسب‌وکار و ایندکس کلیدهای خارجی
seed.sql تکرارپذیر با setseed؛ دیتابیس از صفر در یک دقیقه ساخته می‌شود
place-order.sql با ON_ERROR_STOP، تراکنش و FOR UPDATE؛ هر سه حالت خطا امتحان شده
گزارش‌ها به‌صورت VIEW با count(DISTINCT) درست
نقش جدا برای برنامه و گزارش؛ DELETE و دسترسی به customers برای گزارش ممنوع
کوئری پرتکرار با EXPLAIN ANALYZE بررسی شده
بکاپ شبانه و بررسی بازیابی هفتگی در cron
فایل‌ها در گیت، بدون رمز واقعی

اشتباهات رایج

۱) ساختن دیتابیس با دست

دستورهایی که مستقیم در psql تایپ شده‌اند، جایی ثبت نشده‌اند و روز حادثه کسی یادش نیست. راه‌حل: هر تغییر ساختار در یک فایل SQL و در گیت.

۲) ثبت سفارش در چند دستور جدا بدون تراکنش

خطا وسط کار = موجودی کم‌شده بدون سفارش. راه‌حل: BEGIN/COMMIT و ON_ERROR_STOP، مثل قدم ۳.

۳) چک کردن موجودی فقط در برنامه

«اول SELECT stock، اگر کافی بود UPDATE» بدون قفل: دو درخواست هم‌زمان هر دو «کافی» می‌بینند. راه‌حل: قید CHECK (stock >= 0) در دیتابیس به‌عنوان آخرین خط دفاع، و FOR UPDATE.

۴) `count(*)` در گزارش بعد از `JOIN` با اقلام

تعداد سفارش‌ها بیشتر از واقع. راه‌حل: count(DISTINCT o.id).

۵) دسترسی گزارش به جدول‌های خام

تیم گزارش با SELECT * ایمیل و اسم همه‌ی مشتری‌ها را در فایل اکسل می‌برد. راه‌حل: فقط VIEW، فقط ستون‌های لازم.

تمرین

تمرینآسان

یک VIEW به اسم city_sales اضافه کن که برای هر شهر تعداد سفارش‌های پرداخت‌شده و درآمد را نشان دهد، و به store_report اجازه‌ی خواندنش را بده. شهرها را به ترتیب درآمد ببین.

دیدن جواب

به reports.sql اضافه کن:

CREATE OR REPLACE VIEW city_sales AS
SELECT c.city,
       count(DISTINCT o.id) AS orders,
       sum(oi.qty * oi.unit_price) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.city;

و به grants.sql: GRANT SELECT ON city_sales TO store_report;

psql store -f reports.sql
psql store -c "GRANT SELECT ON city_sales TO store_report"
psql -h localhost -U store_report store -c "SELECT * FROM city_sales ORDER BY revenue DESC"

  city  | orders |  revenue
--------+--------+------------
 تهران  |    895 | 2850040000
 شیراز  |    352 | 1115955000
 اصفهان |    282 |  957855000
 تبریز  |    265 |  875270000
 مشهد   |    202 |  628615000
(5 rows)

CREATE OR REPLACE اجازه می‌دهد reports.sql را بارها اجرا کنی. تهران تقریباً ۴۵٪ سفارش‌هاست؛ همان وزنی که در seed.sql به آن دادیم.

تمرینمتوسط

اسکریپت cancel-order.sql بنویس که یک سفارش پرداخت‌شده را لغو کند و موجودی همه‌ی قلم‌هایش را برگرداند، همه در یک تراکنش. سفارش ۳۰۰۱ را لغو کن و موجودی سوئیچ را قبل و بعد ببین. اجرای دوباره روی همان سفارش باید خطا بدهد.

دیدن جواب

-- cancel-order.sql: لغو یک سفارش پرداخت‌شده و برگرداندن موجودی
-- اجرا: psql store -v order=3001 -f cancel-order.sql
\set ON_ERROR_STOP on

BEGIN;

SELECT id FROM orders
WHERE id = :'order' AND status = 'paid'
FOR UPDATE \gset

UPDATE products p
SET stock = p.stock + oi.qty
FROM order_items oi
WHERE oi.order_id = :id AND p.id = oi.product_id;

UPDATE orders SET status = 'cancelled' WHERE id = :id;

COMMIT;

\echo 'سفارش' :id 'لغو شد'

psql store -v order=3001 -f cancel-order.sql; echo "exit=$?"
psql store -v order=3001 -f cancel-order.sql; echo "exit=$?"
psql store -c "SELECT sku, stock FROM products WHERE sku = 'NW-SW'"

BEGIN
UPDATE 1
UPDATE 1
COMMIT
سفارش 3001 لغو شد
exit=0
BEGIN
psql:cancel-order.sql:9: error: no rows returned for \gset
exit=3
  sku  | stock
-------+-------
 NW-SW |     4
(1 row)

شرط status = 'paid' در SELECT اول جلوی لغو دوباره را گرفت (وگرنه موجودی دو بار برمی‌گشت). UPDATE ... FROM (درس بکاپ) موجودی را برای همه‌ی قلم‌ها با یک دستور برگرداند.

تمرینسخت

verify-backup.sh درس قبل اسم دیتابیس و جدول‌ها را ثابت نوشته بود. نسخه‌ای بنویس که اسم دیتابیس را به‌عنوان ورودی بگیرد و فهرست جدول‌ها را خودش از دیتابیس بخواند، طوری که برای shop و store و هر دیتابیس آینده‌ای کار کند. بدون ورودی، پیام راهنما بدهد.

دیدن جواب

#!/usr/bin/env bash
# بررسی آخرین بکاپ یک دیتابیس: بازیابی در دیتابیس موقت و مقایسه‌ی تعداد سطرها
set -euo pipefail

DB="${1:?usage: verify-backup.sh DBNAME}"
LATEST=$(ls -t "$HOME"/backups/"$DB"-*.dump | head -1)
TMPDB="verify_$(date +%s)"
trap 'dropdb --if-exists "$TMPDB"' EXIT

createdb "$TMPDB"
pg_restore -d "$TMPDB" "$LATEST"

# برای هر جدول یک SELECT count(*) بساز و با UNION ALL به هم بچسبان
COUNT_SQL=$(psql -At "$DB" -c "
    SELECT string_agg(format('SELECT %L, count(*) FROM %I', tablename, tablename),
                      ' UNION ALL ' ORDER BY tablename)
    FROM pg_tables WHERE schemaname = 'public'")

if diff <(psql -At "$DB" -c "$COUNT_SQL") <(psql -At "$TMPDB" -c "$COUNT_SQL"); then
    echo "ok: $LATEST matches ($(psql -At "$DB" -c "$COUNT_SQL" | wc -l) tables)"
else
    echo "MISMATCH: $LATEST" >&2
    exit 1
fi

~/bin/verify-backup.sh store
~/bin/verify-backup.sh shop
~/bin/verify-backup.sh; echo "exit=$?"

ok: /home/ali/backups/store-2026-10-21_2036.dump matches (4 tables)
ok: /home/ali/backups/shop-2026-10-21_2034.dump matches (4 tables)
/home/ali/bin/verify-backup.sh: line 5: 1: usage: verify-backup.sh DBNAME
exit=1

(برای shop یک بکاپ تازه لازم است، وگرنه تغییرهای بعد از بکاپ را فرق گزارش می‌کند.) نکته‌ها: pg_tables یکی از جدول‌های سیستمی است که فهرست جدول‌ها را دارد. format('... %L ... %I', ...) مثل printf است ولی امن برای SQL: %L مقدار را به‌صورت متن نقل‌قول‌شده ('orders') و %I به‌صورت اسم (orders، و اگر لازم باشد "Order Items") می‌گذارد؛ یعنی حتی اسم جدول عجیب هم کوئری را نمی‌شکند (همان فکر پارامتر در درس امنیت). ${1:?پیام} در bash یعنی «اگر ورودی اول نیست، با این پیام خطا بده». نتیجه: یک کوئری که خودش کوئری می‌سازد.

آزمونک

چرا schema.sql داخل BEGIN و COMMIT است؟
\gset در psql چه می‌کند؟
SELECT ... FOR UPDATE برای چیست؟
VIEW چیست؟
چرا store_report به VIEW دسترسی دارد ولی customers را نمی‌تواند بخواند؟
بعد از سفارش ناموفق، سفارش بعدی شماره‌ی ۳۰۰۳ گرفت نه ۳۰۰۲. چرا؟

جمع‌بندی

دیتابیس قابل‌اعتماد از فایل ساخته می‌شود: ساختار، داده‌ی آزمایشی، گزارش‌ها و دسترسی‌ها، همه در گیت.
قیدها (CHECK، UNIQUE، REFERENCES) قانون‌های کسب‌وکارند و آخرین خط دفاع.
عملیات چندمرحله‌ای (ثبت و لغو سفارش) در یک تراکنش، با ON_ERROR_STOP و FOR UPDATE.
گزارش‌ها به‌صورت VIEW: یک بار نوشته، همیشه زنده، و مرز امنیتی برای تیم گزارش.
نقش جدا با کمترین دسترسی؛ ایندکس بر اساس اندازه‌گیری؛ بکاپ شبانه با بررسی بازیابی.
قدم بعدی: همین دیتابیس را به برنامه‌ی Flask دوره‌ی پایتون وصل کن (با psycopg و کاربر store_app) یا با داکر بالا بیاور.

برگه‌ی تقلب این درس

دستور	کاری که می‌کند
`\set ON_ERROR_STOP on`	توقف با اولین خطا
`SELECT a, b FROM t ... \gset`	نتیجه در متغیر psql
`psql db -v x=1 -f f.sql → :'x'`	پارامتر به اسکریپت
`SELECT ... FOR UPDATE`	قفل سطر
`CREATE OR REPLACE VIEW v AS SELECT ...`	گزارش ذخیره‌شده
`GRANT SELECT ON v TO role;`	دسترسی فقط به VIEW
`UPDATE t SET ... FROM t2 WHERE ...`	به‌روزرسانی با JOIN
`format('%I', name)`	اسم امن در SQL ساختگی

مسئله

نقشه‌ی پروژه

قدم ۱: ساختار، schema.sql

قدم ۲: داده‌ی آزمایشی، seed.sql

قدم ۳: ثبت سفارش، place-order.sql

قدم ۴: گزارش‌ها، reports.sql

قدم ۵: نقش‌ها و دسترسی

قدم ۶: سرعت

قدم ۷: بکاپ و بررسی بازیابی

چک‌لیست نهایی

اشتباهات رایج

۱) ساختن دیتابیس با دست

۲) ثبت سفارش در چند دستور جدا بدون تراکنش

۳) چک کردن موجودی فقط در برنامه

۴) count(*) در گزارش بعد از JOIN با اقلام

۵) دسترسی گزارش به جدول‌های خام

تمرین

آزمونک

جمع‌بندی

قدم ۱: ساختار، `schema.sql`

قدم ۲: داده‌ی آزمایشی، `seed.sql`

قدم ۳: ثبت سفارش، `place-order.sql`

قدم ۴: گزارش‌ها، `reports.sql`

۴) `count(*)` در گزارش بعد از `JOIN` با اقلام