Updatable view — unga yuborilgan INSERT, UPDATE yoki DELETE amallarini database aniq base table operatsiyasiga tarjima qila oladigan SQL view’dir. U applicationga cheklangan ustun va satr interface’i beradi, ammo data odatda viewning o‘zida emas, base table’da saqlanadi.
Avtomatik yangilanadigan view
Bitta base table’dan oddiy column mapping va filter bilan tuzilgan view ko‘pincha avtomatik updatable bo‘ladi:
CREATE VIEW active_accounts AS
SELECT id, owner_id, display_name
FROM accounts
WHERE disabled_at IS NULL;
UPDATE active_accounts SET display_name = ... WHERE id = ... base accounts satrini o‘zgartiradi. Primary key viewda ko‘rinmasa ham engine row identityni ichki aniqlashi mumkin, lekin platforma qoidasi tekshiriladi.
Yangilanishni cheklovchi operatorlar
Aggregation, GROUP BY, DISTINCT, set operation va window function natija satrini bitta base rowga bog‘lashni qiyinlashtiradi. Join view’da qaysi table update qilinishi noaniq bo‘lishi mumkin. Shu sabab bunday viewlar odatda read-only.
Computed columnni bevosita yozib bo‘lmaydi. first_name || ' ' || last_name qiymatini qayta ikki fieldga ajratishning yagona qoidasi yo‘q. Base columnlar alohida viewga chiqarilishi yoki trigger explicit conversion qilishi kerak.
CHECK OPTION
View filteri orqali ko‘rinadigan satr update’dan keyin filterdan chiqib ketishi mumkin. WITH CHECK OPTION insert va update natijasi view predicate’iga mos qolishini talab qiladi.
CREATE VIEW open_tickets AS
SELECT id, title, status
FROM tickets
WHERE status = 'open'
WITH CHECK OPTION;
Bu view orqali statusni closedga o‘zgartirish rad etilishi mumkin. Local va cascaded check option nested viewlar predicate’ini qanchalik tekshirishini belgilaydi.
INSTEAD OF trigger
Murakkab view write’ini INSTEAD OF trigger yoki rule qayta ishlashi mumkin. Trigger incoming pseudo-rowni olib, kerakli base tablelarga transaction ichida yozadi. Masalan, join view inserti customer va address tablelarini muvofiqlashtiradi.
Bu imkoniyat kuchli, lekin affected row count, generated key, constraint error va concurrency semantikasini aniq belgilash kerak. Triggerning yashirin side effectlari application uchun tushunarsiz bo‘lishi mumkin. Bulk statement’da trigger row yoki statement darajasida ishlashi platformaga bog‘liq.
Xavfsizlik interfeysi
View faqat user tahrirlashi mumkin bo‘lgan ustunlarni chiqaradi. Base table permission berilmay, viewga DML huquqi berilishi mumkin. Sensitive owner yoki tenant fieldi viewdan yashiriladi va session identitydan hosil qilinadi.
Security definer, invoker va row-level security o‘zaro ta’siri tekshiriladi. View ownerning keng huquqi userga filter bypass imkonini bermasligi kerak. Function va trigger search_path kabi resolution contextini xavfsiz belgilaydi.
Concurrency
View orqali update base table lock va MVCC qoidalariga bo‘ysunadi. Optimistic concurrency uchun version column viewda chiqarilib, WHERE id=? AND version=? bilan tekshiriladi. Affected row nol bo‘lsa stale update aniqlanadi.
View querysida derived filter bo‘lsa parallel transaction satrni ko‘rinish doirasidan chiqarishi mumkin. Isolation darajasi va recheck semantikasi enginega bog‘liq. Business invariant base table constraint bilan himoyalanadi.
Migration
Updatable view eski application contractini yangi base schema ustida vaqtincha saqlashi mumkin. Rename qilingan column alias bilan ko‘rsatiladi, write trigger yangi struktura bo‘yicha yozadi. Barcha eski instance o‘chgach compatibility layer olib tashlanadi.
View ta’rifi migration kodi bilan versionlanadi. CREATE OR REPLACE column order va type’ni har doim o‘zgartira olmasligi mumkin; dependency va permission qayta qo‘llanishi tekshiriladi.
Bog‘liq tushunchalar
SQL view, Base table, CHECK OPTION, INSTEAD OF trigger, Row-level security, MVCC, Schema migration