PostgreSQL — View
GÖRSELDEKİ QR KODU KULLANARAK ILETISIM KANALLARIMA ULASABILIRSINIZ
CREATE VIEW
Create View bir sorgunun görünümünü tanımlar. View(Görünüm) fiziksel olarak oluşturulmaz. Bunun yerine view her referans alındığında sorgu tekrar çalıştırılır.
Bu da şu anlama gelir.
Bir view database’deki tablolara benzer bir yapı oluşturur ancak diskte fiziksel olarak bir yer kaplamaz. View, sorgu sonucunda elde edilen sanal bir tablo olarak düşünülebilir.
View tamamlandıktan sonra, database bu sorguyu kaydeder ve her seferinde bu view’a başvurulduğunda tanımlanan sorgu çalıştırılarak sonuçlar dinamik olarak getirilir. Bu verilerin güncel olmasını sağlar. Çünkü her sorguda mevcut duruma göre veriler döndürülür.
Bir view oluşturma örneği;
Öncelikle mockaroo adresine gidip fake data oluşturacağım. Ve bu datayı DataGript’te açtığım PostgreSQL’e insert edeceğim.
CREATE VIEW view_name AS select …statement…;- Create View: Bir view oluşturmak için kullanılan keyword.
- AS: Bu keyword ile view’ımıza bir isim veriyoruz gibi düşünebiliriz.
- STATEMENT: View’ımız hangi SQL query’sini içerecek.?
NOT: Eğer bir View oluşturulurken bir schema belirtilirse, view o schema’da oluşturulur. Aksi halde o anki aktif schema’da oluşturulur.
Örn: Yaşı 45'ten büyük olan ve cinsiyeti erkek olanları içeren bir view oluşturalım.
CREATE VIEW older_male_employees AS
select * FROM mock where age > 45 and gender = 'Male';
Bu sorguyu yazdıktan sonra database tarafında views adında bir sekme oluşturuldu ve bu sekmenin içerisinde tanımlamış olduğum view’lar yer aldı.
Artık gönül rahatlığıyla aşağıdaki sorguyu çalıştırabilirim.
select *
from older_male_employees order by age;Artık older_male_employees benim için sorgulanabilir bir tablo gibi düşünülebilir.
SORU1;
Ana tabloya örneğin insert attığımda ne olur.?
Kendi database’ime bir insert işlemi yapacağım fakat öncesinde view’ımın count’una çok basit bir SQL sorgusuyla bakıyorum.
select count(*)
from older_male_employees;Sonuç 234 çıktı.
Insert işlemini gerçekleştirdikten sonra aynı sorgu sonucu: 235
Yani, ana tabloya yapılan ekleme, silme ya da güncelleme view’ı da etkiler.
SORU2;
Ana tablodaki column adları ile ana tablodan oluşturacağım view’daki column adları farklı olsun istiyorum. Ne yapmam lazım.?
CREATE VIEW younger_female_employees
(id, name, surname, mail, gender, slry, lg, uni, cmp, age) AS
SELECT * FROM mock WHERE age < 30 AND gender = 'FEMALE';Bu şekilde AS ifadesinden önce parantezler arasına column isimlerine denk gelecek şekilde kendi custom column isimlerimizi verebiliriz.
SORU3;
Bir view’a veri ekleyebilir miyim.? Eğer eklersem bu view’a eklenen data asıl tabloya yansır mı.?
CREATE VIEW older_riches_view AS
SELECT * FROM mock WHERE age > 30
AND REPLACE(salary, '$', '')::NUMERIC > 4500;Maaşı 4500 dolardan fazla olan ve 30 yaşından büyük olanlar için bir view oluşturdum.
INSERT INTO older_riches_view(id, first_name, last_name, email, salary,
language, university, company, age)
VALUES (1002, 'Ahmet', 'Kulaksiz', 'ahmet@gmail.com', '$4931.14', 'Turkish',
'Cankaya', 'Google', 40);Bu şekilde bir insert attığımda, bunun hem oluşturmuş olduğum view’a hem de asıl tabloya yansıdığını gördüm.
Ama!!!!!!
ÇOK ÇOK ÖNEMLİ..!
Mesela ben bu view’a yaşı 30 ‘ dan küçük birisini eklemeye çalıştığımda, örneğin aşağıdaki gibi bir sorgu yazdığımda
INSERT INTO older_riches_view(id, first_name, last_name, email, salary,
language, university, company, age)
VALUES (1003, 'Alperen', 'Kulaksiz', 'alperen@gmail.com', '$4950', 'Turkish',
'Cankaya', 'Google', 20);Bu sorguyu oluşturmuş olduğum view’a eklemedi ama (SQL scriptini çalıştırdığımda success almama rağmen ve insert 1 row diye log basmasına rağmen) asıl tabloya bu datayı ekledi.
Burada şöyle bir durum mevcut.
CREATE VIEW older_riches_view AS
SELECT * FROM mock WHERE age > 30 AND
REPLACE(salary, '$', '')::NUMERIC > 4500
WITH CASCADED CHECK OPTION;WITH CASCADED CHECK OPTION: CHECK OPTION aslında bir enum type. Bir view’a yeni data eklemeye veya mevcut dataları değiştirmeye çalıştığınızda, view’ın şartlarına uygun olup olmadığını kontrol eden bir mekanizmadır. Bu, dataların sadece belirlenen kurallar içinde kalmasını sağlar.
Bu şekilde VIEW’i oluşturup tekrardan aynı datayı eklediğimizde,
Şeklinde bir hata aldık.
Sorguyu değiştirip age parametresini 60 yaptığımızı düşünelim. (30'dan büyük olan herhangi bir değer olabilir.)
Bu sefer view’a datamızın eklendiğini gördük.
WITH LOCAL CHECK OPTION: Bu seçeneği kullandığınızda, sadece view’ın kendisine uygulanan şartlar dikkate alınır. Altındaki diğer view’lar veya tablolarda başka kurallar olsa bile, bu kurallar göz önünde bulundurulmaz.
NOT: Bir view, recursive view ise bu view’da check option kullanılamaz.
NOT: Eğer bir view JOIN’lerle ya da daha karmaşık sorgularla hazırlanmışsa bu view’a data eklemek mümkün olmayabilir.
SORU4;
Peki ya güvenlik concernleri.?
Burada bahsedilmesi gereken 2 tane konu var. Birincisi Security Barrier, İkincisi ise Security Invoker.
Security Barrier;
Örneğin aşağıdaki gibi bir view oluşturduğumuzu düşünelim.
CREATE VIEW my_secure_view AS
SELECT * FROM employees
WHERE department = 'Finance'
WITH (security_barrier = true);Bu security_barrier = true ayarı, view üzerinden sorgu yapan bir kullanıcının sadece view’da belirtilen filtreleme kurallarına uygun satırları görmesini sağlar. Bu özellik sayesinde, saldırganlar ya da yetkisiz kullanıcılar view’ı farklı yollarla manipüle edip, tablodaki diğer satırları görüntüleyemezler.
Örneğin, bir kullanıcı department != 'Finance' gibi bir sorgu yazarak veya JOIN'lerle başka bir tablo üzerinden dolaylı olarak bu görünümü sorgulamaya çalışsa bile, security_barrier sayesinde sadece Finance departmanındaki çalışanları görebileceklerdir.
Security Invoker;
CREATE VIEW user_view AS
SELECT * FROM employees
WITH (security_invoker = true);Bu parametre, view’ın sorgulandığı kullanıcının yetkileriyle mi yoksa görünümü oluşturan kişinin yetkileriyle mi çalışacağını belirtir. security_invoker = true olarak ayarlanırsa, sorgu view’ı kullanan kişinin yetkileriyle çalışır. Eğer view üzerinde sorgulama yapan kişinin yetkilerinin temel alınması isteniyorsa, bu ayar kullanılır.
TEMPORARY VIEW
Temporary keywordü ile geçici view’lar oluşturulabilir. Bu şekilde oluşturulan view’lar, yalnızca mevcut oturum süresince var olacak ve oturum kapandığında otomatik olarak silinecektir.
Aslında bu bir kolaylıktır. Temporary View oluşturmak Write Ahead Log(WAL) ile kaydedilmez. Bu önemsiz gibi görünse de bu loglar zamanla birikir ve performans problemleri ortaya çıkabilir.
Ufak bir Reklam arası.
WAL Nedir.?
Bir işlem, ya da bir process, adına ne dersek diyelim, birden fazla database işlemi gerçekleştirebilir. Çok klasik bir şekilde, bir para transferi söz konusu olduğunda bir hesaptan para eksilirken başka bir hesabın bakiyesi artar. Aslında transaction’un basit anlamda da mantığı budur. Fakat database bu süreci sağlıklı bir şekilde yapmakla mükellef. Eğer sistem beklenmedik bir şekilde çökerse (örneğin, elektrik kesintisi veya sunucu arızası), yarım kalmış bir işlemden dolayı veritabanı bozulabilir. İşte WAL bu sorunu önlemek için geliştirilmiş bir yöntemdir.
Tekrardan temporary view’a geri dönelim.
Temporary View oluşturmak aslında normal view oluşturmakla aynı syntaxa sahip. Tek fark “TEMPORARY” anahtar kelimesini kullanıyoruz.
Bir tane products adında tablo oluşturdum ve içerisine bazı mock datalar kaydettim. Günün sonunda temporary view formülü şu şekilde olacak.
#TEMPOARY VIEW OLUŞTURMA SYNTAXKI
CREATE TEMP/TEMPORARY VIEW view_name AS expression;
#BİR TEMPORARY VIEW ÖRNEĞİ
CREATE TEMP VIEW temp_view AS select * from products where price < 400;Ama datagript’te ya da kendi kullandığınz ide’de oluşturduğunuz temporary view’ı göremeyebilirsiniz.
Yani bir temporary view oluşturduğunuzda bu normal view oluşturduğunuz zamandaki gibi view adında bir klasör oluşturulup da içerisine oluşturduğunuz bu temporary view atılmaz.
Bir temporary view’ı görmek için aşağıdaki gibi küçük bir sql scripti yazabilirsiniz.
select * from information_schema.tables where table_name = 'temp_view'Peki information_schema nedir ve ne işe yarar.?
Ufak bir reklam
Information Schema nedir.?
INFORMATION_SCHEMA, SQL standardında tanımlanan ve çoğu ilişkisel veritabanı yönetim sistemi tarafından desteklenen bir sanal veritabanı şemasıdır. PostgreSQL'de de bu şema, veritabanında bulunan tüm tablolar, kolonlar, veri tipleri, kısıtlamalar ve diğer veritabanı nesneleri hakkında metadata sağlar.
INFORMATION_SCHEMA'yı kullanarak veritabanında hangi tabloların ve kolonların bulunduğunu, veri tiplerini, kısıtlamaları ve daha fazlasını sorgulayabiliriz. Veritabanı yöneticileri ve geliştiriciler, veritabanı yapısını anlamak ve yönetmek için bu şemayı kullanırlar.
Bu schema ile nelere erişebiliriz diye bir soru sorarsak kendimize, aslında aşağıdaki bilgilere erişebiliriz.
- Tablo ve kolon bilgileri
Örneğin;
-- Bir veritabanındaki public schemada olan tüm databaseleri almak için
-- aşağıdaki script çalıştırılabilir.
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';-- Bir veritabanındaki bir tabloya ait column bilgilerini almak için
-- aşağıdaki script çalıştırılabilir.
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'your_table_name';- Constraintns’ler ve ilişki bilgileri
- View’lar ve Fonksiyonlar
- Bir Tabloya Ait Tüm indexlerin Listesi
- Veritabanındaki Tüm View’lar
- Kullanıcı Yetkileri ve Erişim Hakları
- …
Reklam Bitti.
RECURSIVE VIEW
Bir view’ın kendisini tekrar eden bir işlemle verileri getirdiği bir işlemdir. Yani, bir veri parçası başka bir veri parçasına bağlıysa, bu bağı çözmek için recursive bir işlem yapar.
Örneğin, çalışanlar ve yöneticiler düşünelim. Her çalışanın bir yöneticisi var. Yöneticilerin de başka yöneticileri olabilir. En tepede ise bir CEO var. Eğer tüm bu hiyerarşiyi (kim kimin yöneticisi) görmel istersek recursive bir yapıya ihtiyacımız olur.
Böyle bir yapıda, recursive bir yaklaşımla bu tablodaki kim kimin yöneticisi görebiliriz. Ve bu işlem her çalışanın yöneticisi bulunana kadar ya da yöneticisi olmayan kişiye kadar recursive bir şekilde devam eder.
Reklam 1
Recursive Keyword’üne Genel bir bakış
RECURSIVE keyword'ü, genellikle "recursive common table expression" (CTE) veya "recursive query" olarak adlandırılan sorgular oluşturmak için kullanılır. Bu tür sorgular, kendi kendine başvuran, yani bir tablonun veya ifadenin kendi satırlarına veya verisine dayanan verileri döndürebilir.
RECURSIVE Kullanım Kuralları
WITH RECURSIVEifadesi ile başlar.- İlk SELECT ifadesi başlangıç durumu, ikinci SELECT ifadesi ise döngü durumu olarak kullanılır.
UNIONveyaUNION ALLile bu iki durum birleştirilir.- Döngü, bir duruma veya şart sağlanana kadar devam eder.
Yani özet olarak, Recursive keyword’ü WITH ifadesini sadece bir syntax kolaylığı olmaktan çıkararak, standart SQL ile mümkün olmayan şeyleri gerçekleştiren bir özelliğe dönüştürür.
Gerçek hayattan bir örnek
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
employee_name VARCHAR(100),
manager_id INT
);
INSERT INTO employees (employee_name, manager_id) VALUES
('Ali', NULL), -- Ali en üst düzey yönetici
('Ayşe', 1), -- Ayşe, Ali'ye bağlı
('Mehmet', 1), -- Mehmet, Ali'ye bağlı
('Fatma', 2), -- Fatma, Ayşe'ye bağlı
('Ahmet', 2); -- Ahmet, Ayşe'ye bağlıBu tabloda:
Ali, en üst yönetici olduğu içinmanager_iddeğeriNULL.AyşeveMehmetdoğrudanAli'ye bağlı.FatmaveAhmetiseAyşe'ye bağlı.
WITH RECURSIVE employee_hierarchy AS (
-- Başlangıç Durumu: En üst düzey yöneticiyi bulur (Ali)
SELECT
employee_id,
employee_name,
manager_id,
1 AS level
FROM
employees
WHERE
manager_id IS NULL
UNION ALL
-- Döngü Durumu: Bir önceki adımda bulunan çalışanın altındaki çalışanları bulur
SELECT
e.employee_id,
e.employee_name,
e.manager_id,
eh.level + 1
FROM
employees e
INNER JOIN
employee_hierarchy eh ON e.manager_id = eh.employee_id
)
-- Sonuç: Hiyerarşik yapıyı gösterir
SELECT
employee_id,
employee_name,
manager_id,
level
FROM
employee_hierarchy
ORDER BY
level, employee_id;Reklam 2
Common Table Expressions (CTE)
SQL’de karmaşık ve tekrar eden sorguları daha okunabilir ve yönetilebilir hale getirmek için kullanılan bir yapıdır. Bir CTE, geçici bir sonuç kümesi oluşturur ve bu sonuç kümesi, sorgunun geri kalanında kullanılabilir.
CTE’ler özellikle uzun ve karmaşık sorguları bölmek, aynı sorguyu tekrar tekrar yazmaktan kaçınmak ve daha temiz kod yazmak için olduka faydalıdır.
Ufak bir google’lama ile CTE diye aratırsak karşımıza WITH anahtar kelimesi çıkar. WITH daha büyük sorgularda kullanılmak üzere yardımcı ifadeler yazmanın bir yolunu sağlar.
Yani WITH ifadesi, büyük ve karmaşık sorguların bir parçası olarak kullanılmak üzere geçici yardımcı sorgular (CTE’ler) tanımlanmasını sağlar. Bu yardımcı sorgular, sorguların diğer kısımlarında tabloymuş gibi kullanılabilir.
CTE’ler bir sorgu boyunca geçici olarak var olan ve sadece o sorgunun sonunda kaybolan bir yapıdadır. WITH ifadesi sadece bir sorgu boyunca geçerlidir. Tanımlanan bu yardımcı sorgular, normal bir tablo gibi kullanılabilir. Ama sorgu tamamlandığında yok olurlar.
-- CTE SYNTAX
WITH cte_name AS (
SELECT ... -- CTE'nin içindeki subquery
)
SELECT ... -- Ana sorgu
FROM cte_name;Örneğin,
Diyelim ki bir sales tablomuz var ve bu tabloda satış miktarlarını bölgelere göre toplamak istiyoruz.
CREATE TABLE sales (
id SERIAL PRIMARY KEY,
region VARCHAR(50),
sales_amount NUMERIC
);
INSERT INTO sales (region, sales_amount) VALUES
('North', 1000),
('North', 1500),
('South', 800),
('South', 900),
('East', 2000),
('West', 1800);-- Her bölgenin toplam satışını hesaplayan bir CTE
WITH total_sales_by_region AS (
SELECT
region,
SUM(sales_amount) AS total_sales
FROM
sales
GROUP BY
region
)
SELECT
region,
total_sales
FROM
total_sales_by_region;Daha karmaşık bir CTE örneği olması açısından;
Örnek olarak, hem öğrenci bilgilerini hem de sınav puanlarını içeren iki tablo olduğunu düşünelim:
CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
student_name VARCHAR(100)
);
CREATE TABLE exam_results (
exam_id SERIAL PRIMARY KEY,
student_id INT,
score NUMERIC,
FOREIGN KEY (student_id) REFERENCES students(student_id)
);
INSERT INTO students (student_name) VALUES ('Ali'), ('Ayşe'), ('Mehmet');
INSERT INTO exam_results (student_id, score) VALUES
(1, 85),
(1, 90),
(2, 78),
(3, 88),
(3, 92);Şimdi her öğrencinin sınav puanlarının ortalamasını hesaplayalım ve bu ortalama puanı kullanarak öğrencilerin adını ve not ortalamasını listeleyelim
WITH student_averages AS (
SELECT
student_id,
AVG(score) AS average_score
FROM
exam_results
GROUP BY
student_id
)
SELECT
s.student_name,
sa.average_score
FROM
students s
JOIN
student_averages sa ON s.student_id = sa.student_id;Reklamlar bitti.
Tekardan, Recursive View’lara dönecek olursak;
Aşağıdaki syntax ile recursive view oluşturabiliriz.
CREATE VIEW view_name AS
WITH RECURSIVE cte_name AS (
-- Başlangıç durumu
SELECT ...
FROM ...
WHERE ...
UNION ALL
-- Döngü durumu
SELECT ...
FROM ...
JOIN cte_name ON ...
)
SELECT ...
FROM cte_name;