Dasturlash kitobi/6-QISM — Database18 daqiqa

6.8-bob: Stored procedures, functions, triggers, views

6-QISM — Ma'lumotlar bazasi (Database) · 8-mavzu

1. Kirish va motivatsiya

SQL so'rovlarini ekspert darajada (JOIN, CTE, window — 6.7) bildik. Endi DB'ning yana bir kuchli, lekin ko'pincha noma'lum qirrasiga o'tamiz: DB ichida mantiq saqlash. Hozirgacha biz so'rovlarni ilovadan (Node.js — 6.5, 6.6) yubordik. Lekin SQL DB'lar o'z ichida ham mantiq saqlay oladi: stored procedures (saqlangan protseduralar), functions (funksiyalar), triggers (avtomatik ishga tushuvchi kod), va views (virtual jadvallar). Bular DB'ni shunchaki "ombor"dan "aqlli tizim"ga aylantiradi.

Bu mavzu — ikki tomonlama. Bir tomondan, u kuchli: ba'zi mantiqni DB'ga yaqin saqlash tezroq (ma'lumot DB'da, mantiq ham DB'da — tarmoq sayohati kam), va ba'zi narsalar (avtomatik audit, hisoblangan ustun) DB darajasida tabiiyroq. Boshqa tomondan, bahsli: ko'p mantiqni DB'ga solish — kodni "yashiradi" (versiya nazorati, test qiyin — 4, 15). Zamonaviy yondashuv: ko'p mantiq ilovada (Node.js), lekin ba'zi narsalar (view, trigger — audit) DB'da. Ikkalasini ham bilish — to'g'ri qaror uchun zarur.

Eng amaliy va keng ishlatiladigani — VIEW (murakkab so'rovni "nomlangan virtual jadval" qilish — o'qiladigan, qayta ishlatiladigan) va TRIGGER (avtomatik audit, updated_at yangilash). Stored procedure/function — kamroq (lekin maxsus holatlarda kuchli). Bu bobda hammasini, qachon ishlatish/ishlatmaslik bilan o'rganamiz.

O'xshatish: VIEW — restoran menyusidagi "tayyor taom nomi" (murakkab retsept ortida, lekin siz faqat nomini aytasiz — "Osh" orqada 10 ta ingredient JOIN'i). FUNCTION — kalkulyator (qiymat berasiz, hisoblab qaytaradi). PROCEDURE — ko'p qadamli ish (bajaradi, lekin qiymat qaytarmasligi mumkin). TRIGGER — avtomatik datchik ("ma'lumot o'zgardi avtomatik log yoz" — siz chaqirmaysiz, o'zi ishga tushadi).

Nega muhim?

VIEW — murakkab so'rovni soddalashtirish, maxfiy ma'lumotni yashirish (14).
TRIGGER — avtomatik audit, updated_at, yaxlitlik (DB darajasida).
Function/Procedure — DB'ga yaqin mantiq (maxsus holatlarda tez).
Tushunish — mavjud loyihalarda uchraydi; to'g'ri qaror (ilova vs DB mantiq).

2. Nazariya — chuqur tushuntirish

2.1. DB'da mantiq saqlash (umumiy g'oya)

text

  Klassik (mantiq ilovada — 6.5, 6.6):
  Node.js  SQL so'rov  DB  natija  Node.js mantiqni bajaradi

  DB'da mantiq:
  Node.js  "protsedurani chaqir"  DB ichida mantiq bajariladi  natija
  yoki: ma'lumot o'zgardi  TRIGGER avtomatik ishga tushadi (ilova bilmaydi)

Trade-off: DB'da mantiq — tez (ma'lumotga yaqin), markazlashgan; lekin yashirin (versiya nazorat/test qiyin — 15), DB'ga bog'liq. Zamonaviy: ko'p mantiq ilovada; view/trigger DB'da 2.13-bob.

2.2. VIEW — virtual jadval

VIEW — saqlangan so'rov, jadval kabi ishlatiladigan (medium/rkalaichitra):

sql

CREATE VIEW faol_mijozlar AS
SELECT u.id, u.ism, u.email, COUNT(o.id) AS buyurtma_soni, SUM(o.summa) AS jami
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.holat = 'tugallandi'
GROUP BY u.id, u.ism, u.email;

-- Endi view'ni JADVAL kabi ishlatasiz:
SELECT * FROM faol_mijozlar WHERE jami > 100000;

VIEW — virtual: u ma'lumot saqlamaydi (faqat so'rovni). SELECT * FROM view — har safar asosiy so'rov qaytadan ishlaydi (oneuptime). Foydasi: murakkab so'rovni bir marta yozib, ko'p joyda oddiy nom bilan ishlatish (DRY, o'qiladigan).

2.3. VIEW foydalari va kamchiligi

text

   Foyda:
  - Murakkab so'rovni soddalashtirish (5-jadval JOIN  oddiy SELECT)
  - Qayta ishlatish (DRY); takror so'rov xatosini kamaytirish
  - Xavfsizlik: maxfiy ustunni yashirib, view'ga ruxsat (14)
  - Mantiq markazlashgan (so'rov o'zgarsa, bir joyda)

   Kamchilik:
  - Natijani KESHLAMAYDI (har safar asosiy so'rov ishlaydi — 2.2)
  - Murakkab view JOIN'i baribir sekin (oneuptime)
  - Ko'p ichma-ich view — chalkash

VIEW keshlamasligi (oneuptime): "view tezlashtiradi" degan tushuncha noto'g'ri. View — faqat so'rovni "yashiradi" (soddalashtiradi); 5-jadval JOIN'i view orqali ham, to'g'ridan ham bir xil tez/sekin. Tezlik kerak bo'lsa — materialized view 2.4-bob yoki kesh 5.21-bob.

2.4. Materialized View (keshlangan)

Materialized view — natijani saqlaydigan view (PostgreSQL):

sql

CREATE MATERIALIZED VIEW oylik_hisobot AS
SELECT DATE_TRUNC('month', created_at) AS oy, SUM(summa) AS daromad
FROM orders GROUP BY 1;

REFRESH MATERIALIZED VIEW oylik_hisobot;   -- ma'lumotni yangilash (qo'lda yoki cron — 5.22)

Materialized view — oddiy view'dan farqli, natijani diskda saqlaydi (tez o'qiladi). Lekin ma'lumot eskiradi (REFRESH kerak — cron bilan — 5.22). Og'ir, kam o'zgaradigan hisobot uchun (oylik statistika). Oddiy view — har doim yangi (lekin har safar hisoblaydi).

2.5. FUNCTION — qiymat qaytaruvchi

Function — kirish oladi, mantiq bajaradi, qiymat qaytaradi (EDB):

sql

-- PostgreSQL function (PL/pgSQL)
CREATE FUNCTION yoshni_hisobla(tugilgan DATE) RETURNS INTEGER AS $$
BEGIN
  RETURN DATE_PART('year', AGE(tugilgan));   -- yoshni hisoblash
END;
$$ LANGUAGE plpgsql;

-- Ishlatish (SELECT/WHERE ichida)
SELECT ism, yoshni_hisobla(tugilgan_sana) AS yosh FROM users;

Function — qiymat qaytaradi (oneuptime): SELECT, WHERE, hisoblangan ustunda ishlatiladi. Foydali: takroriy hisoblash (yosh, masofa, formatlash), trigger mantig'i 2.8-bob. IMMUTABLE belgilangan funksiya keshlanadi (bir xil kirishga qayta hisoblamaydi — tez).

2.6. PROCEDURE — amal bajaruvchi

Procedure — mantiq bajaradi, lekin qiymat qaytarmasligi mumkin; tranzaksiya boshqaradi (oneuptime):

sql

CREATE PROCEDURE pul_otkaz(from_id INT, to_id INT, summa DECIMAL) AS $$
BEGIN
  UPDATE accounts SET balans = balans - summa WHERE id = from_id;
  UPDATE accounts SET balans = balans + summa WHERE id = to_id;
  COMMIT;                                     -- procedure tranzaksiya boshqaradi (2.6)
END;
$$ LANGUAGE plpgsql;

CALL pul_otkaz(1, 2, 100);                    -- chaqirish (CALL)

Function vs Procedure (oneuptime): function — qiymat qaytaradi (SELECT'da); procedure — amal bajaradi (CALL bilan), tranzaksiya (COMMIT/ROLLBACK) boshqara oladi. Murakkab, ko'p qadamli amal (pul o'tkazish) — procedure. Lekin buni ilovada (6.5: 2.9) ham qilsa bo'ladi (zamonaviy yondashuv — 2.13).

Parametr yo'nalishi (IN/OUT): procedure/function parametrlari yo'nalishga ega — IN (kirish, standart — yozilmasa ham shu), OUT (natijani qaytaradi), INOUT (ikkalasi). Masalan CREATE PROCEDURE hisobla(IN a INT, OUT jami INT) — a kiradi, jami chiqadi. IN — eng ko'p; OUT — procedure'dan qiymat qaytarish kerak bo'lganda (function RETURNS o'rniga).

2.7. TRIGGER — avtomatik ishga tushuvchi

Trigger — ma'lumot o'zgarganda (INSERT/UPDATE/DELETE) avtomatik ishga tushuvchi kod (severalnines):

text

  Trigger qachon ishga tushadi:
  BEFORE/AFTER  +  INSERT/UPDATE/DELETE

  BEFORE INSERT — qo'shishdan oldin (ma'lumotni o'zgartirish/tekshirish)
  AFTER UPDATE  — yangilangach (log yozish, boshqa jadvalni yangilash)
  Siz CHAQIRMAYSIZ — DB O'ZI ishga tushiradi (avtomatik)

Trigger — avtomatik: ilova "bilmaydi" — ma'lumot o'zgarishi bilan trigger o'zi ishlaydi. Foydali: audit (kim/qachon o'zgartirdi — avtomatik log), updated_at yangilash, hisoblangan maydon, yaxlitlik. Lekin "yashirin" (ilova ko'rmaydi — debug qiyin — 2.12).

2.8. Trigger qanday yoziladi (PostgreSQL)

PostgreSQL'da trigger ikki qism: trigger function + trigger (postgresql docs):

sql

-- 1. Trigger function (return type: trigger)
CREATE FUNCTION updated_at_yangila() RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();                     -- yangilanayotgan qatorning vaqtini o'rnat
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2. Trigger (qaysi jadval, qachon)
CREATE TRIGGER users_updated_at
BEFORE UPDATE ON users                        -- har UPDATE'dan oldin
FOR EACH ROW
EXECUTE FUNCTION updated_at_yangila();
-- Endi har users UPDATE  updated_at avtomatik yangilanadi (ilova qilmaydi)

NEW va OLD: trigger ichida NEW (yangi qiymat), OLD (eski qiymat) — o'zgarishni ko'rish/o'zgartirish. BEFORE — qiymatni o'zgartirish (NEW); AFTER — log/boshqa amal. Bu — eng keng tarqalgan trigger (auto updated_at).

2.9. Trigger use-case'lar

text

   updated_at avtomatik 2.8-bob — eng keng tarqalgan
   Audit log (kim/qachon/nima o'zgartirdi — avtomatik tarix)
   Hisoblangan/denormalizatsiya maydon (buyurtma soni avtomatik)
   Yaxlitlik tekshiruvi (constraint yetmaganda)
   Murakkab biznes mantiq (ilovada afzal — 2.13)

2.10. View — xavfsizlik (maxfiy ma'lumotni yashirish — 14)

View'ning muhim ishlatilishi — maxfiy ustunlarni yashirish (14):

sql

-- users'da parol bor, lekin view'da yo'q
CREATE VIEW users_public AS
SELECT id, ism, email, created_at FROM users;   -- parol YO'Q (14)

-- Foydalanuvchiga/rolga faqat view'ga ruxsat (asosiy jadvalga emas)
GRANT SELECT ON users_public TO oddiy_rol;

Xavfsizlik view'i (14): maxfiy ustun (parol, balans) bo'lgan jadvalga to'g'ridan ruxsat berish o'rniga — view (faqat ochiq ustunlar) ga ruxsat. DB darajasidagi RBAC (5.17 ruhida). Hisobot/o'qish foydalanuvchilari uchun.

2.11. Node.js'dan chaqirish

// View — oddiy SELECT (6.5, 6.6)
const { rows } = await pool.query("SELECT * FROM faol_mijozlar WHERE jami > $1", [100000]);

// Function — SELECT ichida
const { rows: r } = await pool.query("SELECT ism, yoshni_hisobla(tugilgan_sana) AS yosh FROM users");

// Procedure — CALL
await pool.query("CALL pul_otkaz($1, $2, $3)", [1, 2, 100]);

// Trigger — chaqirilmaydi (avtomatik — 2.7)
await pool.query("UPDATE users SET ism = $1 WHERE id = $2", [ism, id]);   // updated_at o'zi yangilanadi

View — SELECT (jadval kabi); function — SELECT/WHEREda; procedure — CALL; trigger — chaqirilmaydi (avtomatik). Ilova nuqtai nazaridan view eng qulay (oddiy SELECT).

2.12. Kamchiliklar va ehtiyot (bahsli tomon)

text

   DB'da mantiqning kamchiliklari 2.1-bob:
  - Versiya nazorat qiyin (git'da emas — kod DB ichida — 4)
  - Test qiyin (Jest/unit — 8.11 — DB mantig'ini test qilish murakkab)
  - Debug qiyin (trigger yashirin — kutilmagan xatti-harakat)
  - DB'ga bog'liq (PostgreSQL function MySQL'da ishlamaydi)
  - Bilim talab (PL/pgSQL — alohida til)

Trigger ehtiyot: "yashirin" mantiq — ilova bir narsa qiladi, trigger boshqa narsani avtomatik qiladi kutilmagan natija, qiyin debug. Trigger'ni kam, oddiy, hujjatlangan ishlating (audit, updated_at). Murakkab mantiq — ilovada.

2.13. Zamonaviy yondashuv (qachon DB, qachon ilova)

text

  DB'da (view/trigger):
   VIEW — murakkab so'rovni soddalashtirish, xavfsizlik (2.2, 2.10)
   TRIGGER — updated_at, audit (oddiy, avtomatik — 2.9)
   Constraint — yaxlitlik (FK, CHECK — 6.4)

  Ilovada (Node.js — afzal):
   Biznes mantiq (buyurtma jarayoni, hisob-kitob)
   Tranzaksiya (6.5: 2.9 — kodda boshqarish)
   Murakkab logika (test/versiya nazorat oson — 15)

Zamonaviy konsensus: view va oddiy trigger (updated_at, audit) — DB'da yaxshi. Murakkab biznes mantiq — ilovada (Node.js — test, versiya, moslashuvchanlik — 15). Stored procedure'lar kamroq (lekin maxsus tezlik holatlarida kuchli). Mavjud (legacy) loyihalarda ko'p uchraydi — tushunish kerak.

2.14. ORM va bular (6.14 ko'prik)

text

  ORM'lar (Sequelize/Prisma/TypeORM — 6.14):
  - View — odatda raw query yoki model sifatida
  - Trigger — migration'da (raw SQL)
  - Function/procedure — raw query (.query())
   ORM'lar bularni to'liq qamramaydi; ko'pincha xom SQL (migration — 6.16)

2.15. Best practices (14)

text

   VIEW — murakkab so'rov soddalashtirish + xavfsizlik (2.2, 2.10)
   Materialized view — og'ir, kam-o'zgaradigan hisobot (REFRESH cron — 2.4)
   TRIGGER — oddiy, hujjatlangan (updated_at, audit — 2.9, 2.12)
   Murakkab mantiq ilovada (test/versiya — 2.13)
   Function IMMUTABLE bo'lsa belgilash (kesh — 2.5)
   DB'dagi kodni ham migration/git'da saqlang (versiya nazorat — 6.16)
   View bilan maxfiy ustunni yashir (14, 2.10)

3. Sintaksis — tez ma'lumotnoma

sql

-- VIEW (2.2)
CREATE VIEW nom AS SELECT ...;
SELECT * FROM nom;                              -- jadval kabi
CREATE MATERIALIZED VIEW nom AS ...; REFRESH MATERIALIZED VIEW nom;   -- keshlangan (2.4)

-- FUNCTION 2.5-bob — qiymat qaytaradi
CREATE FUNCTION nom(param TYPE) RETURNS TYPE AS $$ BEGIN RETURN ...; END; $$ LANGUAGE plpgsql;

-- PROCEDURE 2.6-bob — amal; CALL bilan
CREATE PROCEDURE nom(...) AS $$ BEGIN ...; COMMIT; END; $$ LANGUAGE plpgsql;  CALL nom(...);

-- TRIGGER 2.8-bob — avtomatik
CREATE FUNCTION fn() RETURNS TRIGGER AS $$ BEGIN NEW.x = ...; RETURN NEW; END; $$ LANGUAGE plpgsql;
CREATE TRIGGER trg BEFORE UPDATE ON jadval FOR EACH ROW EXECUTE FUNCTION fn();

4. Batafsil kod namunalari

Misol 1 — VIEW (murakkab so'rovni soddalashtirish — 2.2)

sql

-- Mahsulot to'liq ma'lumoti (kategoriya nomi bilan — JOIN yashirilgan)
CREATE VIEW mahsulot_toliq AS
SELECT
  p.id, p.nom, p.narx, p.zaxira,
  c.nom AS kategoriya_nomi,
  CASE WHEN p.zaxira > 0 THEN 'mavjud' ELSE 'tugagan' END AS holat   -- hisoblangan
FROM products p
JOIN categories c ON p.category_id = c.id;

-- Endi oddiy SELECT (JOIN/CASE yashirin — 2.2)
SELECT * FROM mahsulot_toliq WHERE holat = 'mavjud' AND narx < 1000000;

Misol 2 — Xavfsizlik view'i (maxfiy yashirish — 2.10)

sql

-- users'da parol/balans bor; view'da yo'q (14)
CREATE VIEW users_ochiq AS
SELECT id, ism, email, rol, created_at FROM users;   -- parol, balans YO'Q

-- Hisobot rolida faqat view'ga ruxsat (asosiy jadvalga emas — 2.10)
GRANT SELECT ON users_ochiq TO hisobot_rol;
REVOKE ALL ON users FROM hisobot_rol;                -- asosiy jadval yopiq

Misol 3 — Materialized view (og'ir hisobot — 2.4)

sql

-- Oylik daromad (og'ir hisoblash — keshlangan — 2.4)
CREATE MATERIALIZED VIEW oylik_daromad AS
SELECT
  DATE_TRUNC('month', created_at) AS oy,
  COUNT(*) AS buyurtmalar,
  SUM(summa) AS daromad,
  AVG(summa) AS ortacha_chek
FROM orders
WHERE holat = 'tugallandi'
GROUP BY 1
ORDER BY 1;

-- Indeks (materialized view'ga ham mumkin)
CREATE INDEX idx_oylik_oy ON oylik_daromad (oy);

-- Yangilash (har kuni cron bilan — 5.22)
REFRESH MATERIALIZED VIEW CONCURRENTLY oylik_daromad;   -- bloklab qo'ymasdan

Misol 4 — FUNCTION (qayta ishlatiladigan hisoblash — 2.5)

sql

-- Chegirmali narxni hisoblash funksiyasi (2.5)
CREATE FUNCTION chegirmali_narx(narx DECIMAL, foiz INT) RETURNS DECIMAL AS $$
BEGIN
  RETURN ROUND(narx * (1 - foiz / 100.0), 2);   -- chegirma hisoblash
END;
$$ LANGUAGE plpgsql IMMUTABLE;                   -- IMMUTABLE — keshlanadi (2.5)

-- SELECT ichida ishlatish
SELECT nom, narx, chegirmali_narx(narx, 20) AS yangi_narx FROM products;

Misol 5 — TRIGGER: updated_at avtomatik (2.8)

sql

-- 1. Trigger function (2.8)
CREATE FUNCTION set_updated_at() RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();                        -- yangi qiymat (2.8)
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2. Trigger'ni jadvallarga ulash (har UPDATE'dan oldin)
CREATE TRIGGER users_set_updated
BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER orders_set_updated
BEFORE UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- Endi: UPDATE users SET ism = 'X' WHERE id = 1;   updated_at AVTOMATIK yangilanadi

Misol 6 — TRIGGER: audit log (2.9)

sql

-- Audit jadvali
CREATE TABLE audit_log (
  id SERIAL PRIMARY KEY,
  jadval VARCHAR(50), amal VARCHAR(10),
  eski_qiymat JSONB, yangi_qiymat JSONB,        -- JSONB (6.6: 2.10)
  vaqt TIMESTAMPTZ DEFAULT NOW()
);

-- Audit trigger function
CREATE FUNCTION audit_yoz() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (jadval, amal, eski_qiymat, yangi_qiymat)
  VALUES (
    TG_TABLE_NAME, TG_OP,                        -- jadval nomi, amal (INSERT/UPDATE/DELETE)
    to_jsonb(OLD), to_jsonb(NEW)                 -- eski/yangi qiymat (JSON)
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger (har o'zgarishda audit — 2.9)
CREATE TRIGGER orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_yoz();
-- Endi har buyurtma o'zgarishi avtomatik audit_log'ga yoziladi (ilova bilmaydi — 2.7)

Misol 7 — Denormalizatsiya trigger (hisoblangan maydon — 2.9)

sql

-- users.buyurtma_soni'ni avtomatik yangilash (denormalizatsiya — 6.1: 2.10)
CREATE FUNCTION buyurtma_sonini_yangila() RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE users SET buyurtma_soni = buyurtma_soni + 1 WHERE id = NEW.user_id;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE users SET buyurtma_soni = buyurtma_soni - 1 WHERE id = OLD.user_id;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_count
AFTER INSERT OR DELETE ON orders FOR EACH ROW EXECUTE FUNCTION buyurtma_sonini_yangila();
-- Buyurtma qo'shilsa/o'chsa — users.buyurtma_soni avtomatik (COUNT'siz tez o'qish)

Misol 8 — Node.js integratsiya (2.11)

// View — oddiy SELECT (6.6)
export async function faolMijozlar(minJami) {
  const { rows } = await pool.query(
    "SELECT * FROM faol_mijozlar WHERE jami > $1 ORDER BY jami DESC", [minJami]
  );
  return rows;
}

// Function — SELECT ichida
export async function chegirmalimahsulotlar(foiz) {
  const { rows } = await pool.query(
    "SELECT nom, narx, chegirmali_narx(narx, $1) AS yangi FROM products", [foiz]
  );
  return rows;
}

// updated_at trigger — chaqirilmaydi, avtomatik (2.7)
await pool.query("UPDATE users SET ism = $1 WHERE id = $2", [ism, id]);
//  updated_at o'zi yangilandi (trigger — Misol 5)

// Materialized view'ni yangilash (cron — 5.22)
export const hisobotniYangila = () => pool.query("REFRESH MATERIALIZED VIEW oylik_daromad");

5. To'g'ri va noto'g'ri holatlar

1) View tezlashtiradi deb o'ylash

text

 "murakkab so'rovni view qilsam, tez bo'ladi" (keshlamaydi — 2.3)
 tezlik kerak  materialized view 2.4-bob yoki kesh (5.21)

2) Murakkab biznes mantiqni trigger'ga

text

 butun buyurtma jarayoni trigger'da (yashirin, debug qiyin — 2.12)
 biznes mantiq ilovada; trigger faqat oddiy (updated_at, audit — 2.13)

3) updated_at'ni qo'lda har joyda

//  har UPDATE'da qo'lda (unutiladi, takror)
await q("UPDATE users SET ism = $1, updated_at = NOW() WHERE id = $2", [ism, id]);

//  trigger (avtomatik — Misol 5)
await q("UPDATE users SET ism = $1 WHERE id = $2", [ism, id]);

4) Maxfiy ustunli jadvalga to'g'ridan ruxsat

sql

--  parol bor jadvalga hisobot rolini qo'yish (14)
GRANT SELECT ON users TO hisobot_rol;

--  view orqali (maxfiy yashirin — 2.10)
GRANT SELECT ON users_ochiq TO hisobot_rol;

5) DB kodini git'siz qoldirish

text

 trigger/function faqat DB'da (versiya nazorat yo'q — 2.12)
 migration fayllarda (git'da — 6.16)

6. Keng tarqalgan xatolar va yechimlari

Xato 1 — View eskirgan ma'lumot (materialized)

Sababi: materialized view REFRESH qilinmagan 2.4-bob. Yechimi: REFRESH (cron — 5.22); yoki oddiy view (har doim yangi).

Xato 2 — Trigger kutilmagan xatti-harakat

Sababi: yashirin trigger ilova bilmagan narsani qiladi 2.12-bob. Yechimi: trigger'ni hujjatlang; kam ishlating; debug'da trigger'larni tekshiring.

Xato 3 — function ... does not exist

Sababi: function yaratilmagan yoki parametr turi mos emas. Yechimi: funksiyani yarating; argument turlarini moslang.

Xato 4 — Trigger cheksiz sikl

Sababi: trigger o'zi yangilanayotgan jadvalni yangilaydi yana trigger (loop). Yechimi: BEFORE'da NEW o'zgartirish (UPDATE emas); shart bilan to'xtatish.

Xato 5 — View'ga INSERT/UPDATE ishlamaydi

Sababi: murakkab view (JOIN/GROUP BY) — yangilab bo'lmaydi. Yechimi: oddiy view yangilanadi; murakkab — INSTEAD OF trigger yoki asosiy jadval.

Xato 6 — Procedure CALL xatosi

Sababi: function'ni CALL, yoki procedure'ni SELECT 2.6-bob. Yechimi: function SELECT; procedure CALL.

7. Integratsiya — bu mavzu stack'ning qayerida uchraydi

SQL chuqur 6.7-bob: view — JOIN/CTE'ni saqlash.
PostgreSQL 6.6-bob: PL/pgSQL, trigger, JSONB (audit).
Tranzaksiya 6.11-bob: procedure tranzaksiya boshqaradi.
Cron 5.22-bob: materialized view REFRESH.
Xavfsizlik (14): view bilan maxfiy yashirish; RBAC 5.17-bob.
Migration 6.16-bob: trigger/function/view — migration'da (git).
ORM 6.14-bob: raw query bilan; trigger migration'da.
Kesh 5.21-bob: materialized view muqobili.
Clean code (15): mantiq ilovada vs DB (test/versiya).

8. Eng yaxshi amaliyotlar (best practices)

VIEW — murakkab so'rov soddalashtirish + xavfsizlik (maxfiy yashirish — 2.2, 2.10, 14).
View keshlamaydi — tezlik uchun materialized view (REFRESH cron) yoki Redis (2.3, 2.4).
TRIGGER — oddiy, hujjatlangan (updated_at, audit — 2.9); murakkab mantiq emas 2.12-bob.
Murakkab biznes mantiq — ilovada (test/versiya nazorat — 2.13).
updated_at — trigger bilan (qo'lda har joyda emas — Misol 5).
Function IMMUTABLE bo'lsa belgilang (kesh — 2.5).
DB kodini migration/git'da saqlang (versiya nazorat — 2.12, 6.16).
Trigger siklidan saqlaning (Xato 4); kam, ehtiyot bilan.
Audit — trigger bilan avtomatik (Misol 6); denormalizatsiya — trigger (Misol 7).
function SELECT; procedure CALL (to'g'ri chaqirish — 2.6).

9. Amaliy loyiha: "DB Mantiq Qatlami (View + Trigger)"

DB ichidagi mantiqni mustahkamlash.

Maqsad

View, materialized view, function va trigger'lar bilan DB mantiq qatlamini qurish: soddalashtirish, avtomatik audit/updated_at, xavfsizlik.

Talablar (requirements)

VIEW: murakkab JOIN'ni soddalashtirish (mahsulot+kategoriya — Misol 1, 2.2).
Xavfsizlik view: maxfiy ustunni yashirish; rolga ruxsat (Misol 2, 2.10).
Materialized view: og'ir hisobot (oylik daromad); REFRESH (Misol 3, 2.4).
FUNCTION: qayta ishlatiladigan hisoblash (chegirma/yosh — Misol 4, 2.5).
updated_at trigger: avtomatik (Misol 5, 2.8).
Audit trigger: o'zgarishlarni JSONB'ga log (Misol 6, 2.9).
Denormalizatsiya trigger: hisoblangan maydon avtomatik (Misol 7).
Node.js integratsiya: view SELECT, function, REFRESH (Misol 8, 2.11).
Migration: DB kodini fayllarda (git — 2.12, 6.16).
Qaror tahlili: qaysi mantiq DB'da, qaysi ilovada — asosla 2.13-bob.

Maslahatlar (hint)

View keshlamaydi og'ir hisobot materialized (2.3, 1-xato).
updated_at: BEFORE UPDATE trigger (Misol 5, 3-holat).
Audit: AFTER + to_jsonb(OLD/NEW) (Misol 6).
Trigger siklidan saqlaning (Xato 4).
function SELECT; procedure CALL (2.6, 6-xato).
DB kodi migration'da (git — 2.12).

"Tayyor" mezonlari (acceptance criteria)

VIEW murakkab so'rovni soddalashtiradi.
Xavfsizlik view (maxfiy yashirin).
Materialized view + REFRESH.
FUNCTION (qayta ishlatiladigan hisoblash).
updated_at trigger avtomatik.
Audit trigger (JSONB log).
Denormalizatsiya trigger.
Node.js'dan ishlatiladi.
DB kodi migration/git'da.
DB vs ilova mantiq qarori asoslangan.

Yechim kodi ataylab berilmagan — bu loyihani o'zingiz yozib ko'ring.

10. Xulosa va keyingi bobga ko'prik

Bu bobda DB ichidagi mantiqni o'rgandik:

DB'da mantiq (g'oya, trade-off — 2.1, 2.13); VIEW (virtual jadval — soddalashtirish/xavfsizlik — 2.2, 2.10) — keshlamaydi 2.3-bob; materialized view (keshlangan — REFRESH — 2.4).
FUNCTION (qiymat qaytaradi — SELECT'da — 2.5) vs PROCEDURE (amal, tranzaksiya — CALL — 2.6).
TRIGGER (avtomatik — BEFORE/AFTER, NEW/OLD — 2.7, 2.8); use-case'lar (updated_at, audit, denormalizatsiya — 2.9).
Zamonaviy yondashuv (view/oddiy trigger DB'da; murakkab mantiq ilovada — 2.13); kamchiliklar (yashirin, test — 2.12).

Keyingi bob — 6.9-bob: Normalizatsiya (1NF–3NF), indekslar, tranzaksiyalar (ACID), izolyatsiya darajalari — wait, kitob rejasiga ko'ra navbatdagi 6.9 (Normalizatsiya 1NF-3NF) va 6.10/6.11 (indekslar/ACID). Keyingi bobda normalizatsiya (ma'lumotni to'g'ri tashkil qilish — 1NF, 2NF, 3NF), va tranzaksiyalar (ACID) — izolyatsiya darajalarini chuqur o'rganamiz. Bu — DB dizaynining ilmiy asoslari.

Foydalanilgan rasmiy/ishonchli manbalar

PostgreSQL docs — Trigger Functions, CREATE VIEW, CREATE FUNCTION/PROCEDURE
OneUptime — PostgreSQL stored procedures best practices 2026; EDB — procedures vs functions
Severalnines — PostgreSQL triggers; Medium — Views/Functions/Procedures qachon ishlatish

Izohlar (0)

Izoh yozish uchun kiring.

Hozircha izoh yo'q. Birinchi bo'ling!