SQLite CREATE VIEW: Görünümler, Sütun Adları ve INSTEAD OF

Görünüm oluşturma ve sütun adları

CREATE VIEW söz diziminin temel parçaları şunlardır: isteğe bağlı TEMP anahtar kelimesi, isteğe bağlı IF NOT EXISTS, isteğe bağlı şema adı, görünümün adı, parantez içinde isteğe bağlı sütun adları listesi ve AS ile başlayan SELECT ifadesi. Aşağıdaki görünüm her müşterinin sipariş sayısını ve toplam tutarını tek bir sanal tabloda topluyor:

CREATE TABLE musteri(id INTEGER PRIMARY KEY, ad TEXT, sehir TEXT);
CREATE TABLE siparis(id INTEGER PRIMARY KEY, musteri_id INTEGER, tutar INTEGER);
INSERT INTO musteri VALUES (1, 'Ayşe', 'İzmir'), (2, 'Mehmet', 'Ankara'), (3, 'Zeynep', 'İzmir');
INSERT INTO siparis(musteri_id, tutar) VALUES (1, 250), (1, 100), (2, 400);

CREATE VIEW musteri_ozet(musteri, sehir, siparis_sayisi, toplam) AS
  SELECT m.ad, m.sehir, count(s.id), coalesce(sum(s.tutar), 0)
  FROM musteri m LEFT JOIN siparis s ON s.musteri_id = m.id
  GROUP BY m.id;

SELECT * FROM musteri_ozet ORDER BY toplam DESC;
-- çıktı:
-- Mehmet|Ankara|1|400
-- Ayşe|İzmir|2|350
-- Zeynep|İzmir|0|0

SELECT musteri FROM musteri_ozet WHERE sehir = 'İzmir' AND toplam > 0;
-- çıktı: Ayşe

Görünüm adından hemen sonraki (musteri, sehir, siparis_sayisi, toplam) listesi, sonuç sütunlarının adlarını belirler. Bu liste SQLite 3.9.0 sürümünde eklendi ve belgeler kullanılmasını önerir. Listeyi yazdığınızda SELECT içindeki ifadelerin nasıl yazıldığı önemini yitirir; dışarıdan görünen adlar sabit kalır. Listedeki ad sayısı SELECT'in döndürdüğü sütun sayısıyla aynı olmalıdır. Denememizde sayılar uyuşmadığında CREATE VIEW hata vermedi; hata ancak görünüm ilk sorgulandığında "expected 1 columns for 'v' but got 2" biçiminde çıktı. Bu yüzden yeni bir görünümü oluşturduktan hemen sonra en az bir kez sorgulamak iyi bir alışkanlıktır.

Görünüm, ikinci sorguda olduğu gibi kendi üzerine yeni koşullar, sıralama ve birleştirmeler eklemeye izin verir. Böylece karmaşık bir birleştirme ve gruplamayı bir kez yazıp uygulamanın farklı yerlerinden kısa sorgularla kullanabilirsiniz. Raporlama ekranları, yönetim paneli listeleri ve dışa aktarma sorguları görünümlerin en sık kullanıldığı yerlerdir.

Sütun adı vermezseniz ne olur

Sütun listesini yazmazsanız SQLite, adları SELECT ifadesinin sonuç sütunlarından türetir. Sade bir sütun adı için bu sorun çıkarmaz; ancak ifadelerde ad, ifadenin metni olabilir:

.headers on
CREATE TABLE urun(id INTEGER PRIMARY KEY, ad TEXT, fiyat INTEGER, kdv INTEGER);
INSERT INTO urun VALUES (1, 'Kalem', 100, 20);

CREATE VIEW v_adsiz AS SELECT ad, fiyat * (100 + kdv) / 100, upper(ad) FROM urun;
SELECT * FROM v_adsiz;
-- çıktı:
-- ad|fiyat * (100 + kdv) / 100|upper(ad)
-- Kalem|120|KALEM

CREATE VIEW v_adli AS
  SELECT ad, fiyat * (100 + kdv) / 100 AS kdvli, upper(ad) AS buyuk FROM urun;
SELECT * FROM v_adli;
-- çıktı:
-- ad|kdvli|buyuk
-- Kalem|120|KALEM
.headers off

İlk görünümde ikinci ve üçüncü sütunların adı, yazdığımız ifadenin kendisi oldu. Belgeler bu otomatik adlandırma kurallarının arayüzün tanımlı bir parçası olmadığını ve gelecekteki sürümlerde değişebileceğini açıkça belirtir. Uygulama kodunuz sonuç sütunlarına adla erişiyorsa, görünümün sütunlarını ya CREATE VIEW içindeki listeyle ya da her ifadeye AS takma_ad ekleyerek kendiniz adlandırın. İkinci görünümde olduğu gibi adlar artık tahmin edilebilir.

Görünüm veri saklamaz

Görünüm yalnızca bir sorgu metnidir. sqlite_schema tablosunda type değeri view olan bir satır olarak durur; bu satırın rootpage değeri 0'dır, çünkü görünüme ait bir B-ağacı sayfası yoktur. tbl_name sütununda da görünümün kendi adı bulunur, kullandığı tabloların adı değil.

SELECT type, name, tbl_name, rootpage FROM sqlite_schema WHERE type = 'view';
-- çıktı: view|musteri_ozet|musteri_ozet|0

SELECT name, type FROM pragma_table_info('musteri_ozet');
-- çıktı:
-- musteri|TEXT
-- sehir|TEXT
-- siparis_sayisi|
-- toplam|

pragma_table_info görünümler için de çalışır ve sütun listesini verir. Doğrudan bir tablo sütunundan gelen sütunlar o sütunun bildirilen tipini taşırken, count() ve sum() gibi ifadelerden gelen sütunların tipi boştur. Şema tablosunu sorgulamanın diğer yolları için SQLite tabloları listeleme yazısına, tip ve eğilim kuralları için SQLite veri tipleri yazısına bakın.

Verinin saklanmaması iki sonuç doğurur. İyi tarafı, görünüm her zaman alttaki tabloların güncel hâlini gösterir; eşitlemeniz gereken ayrı bir kopya yoktur. Dikkat edilecek tarafı ise performanstır: görünüm üzerinde çalışan her sorgu, görünümün SELECT'ini de yeniden çalıştırır. SQLite'ın sorgu iyileştiricisi görünümün her kullanımını bir alt sorgu olarak ele alır ve mümkün olduğunda bu alt sorguyu dıştaki sorguyla birleştirir (query flattening) ya da dıştaki koşulu görünümün içine iter. Bu yüzden alttaki tablolardaki indeksler görünüm üzerinden de kullanılabilir:

CREATE TABLE siparis(id INTEGER PRIMARY KEY, musteri_id INTEGER, durum TEXT, tutar INTEGER);
CREATE INDEX siparis_musteri ON siparis(musteri_id);

CREATE VIEW acik_siparis AS
  SELECT id, musteri_id, tutar FROM siparis WHERE durum = 'acik';
EXPLAIN QUERY PLAN SELECT * FROM acik_siparis WHERE musteri_id = 7;
-- çıktı:
-- QUERY PLAN
-- `--SEARCH siparis USING INDEX siparis_musteri (musteri_id=?)

CREATE VIEW musteri_toplam AS
  SELECT musteri_id, sum(tutar) AS toplam FROM siparis GROUP BY musteri_id;
EXPLAIN QUERY PLAN SELECT * FROM musteri_toplam WHERE musteri_id = 7;
-- çıktı:
-- QUERY PLAN
-- |--CO-ROUTINE musteri_toplam
-- |  `--SEARCH siparis USING INDEX siparis_musteri (musteri_id=?)
-- `--SCAN musteri_toplam

İlk sorguda görünüm tamamen düzleştirildi ve doğrudan tablo üzerinde indeksli arama yapıldı. Gruplama içeren ikinci görünüm bir eş yordam (co-routine) olarak ayrı çalıştı, ama musteri_id = 7 koşulu görünümün içine itildiği için yine indeks kullanıldı. Hangi durumda hangi iyileştirmenin uygulanacağı karmaşık kurallara bağlıdır; emin olmak için sorgunuzu EXPLAIN QUERY PLAN ile kontrol edin. Plan çıktısının biçimi sürümden sürüme küçük farklar gösterebilir.

Görünümler salt okunurdur

SQLite'ta bir görünüme doğrudan veri yazamazsınız. Görünüm tek bir tablodan birkaç sütun seçen en basit sorgu bile olsa, INSERT, UPDATE ve DELETE aynı hatayla reddedilir:

INSERT INTO musteri_ozet VALUES ('Ali', 'Bursa', 0, 0);
-- hata: cannot modify musteri_ozet because it is a view
UPDATE musteri_ozet SET sehir = 'Bursa';
-- hata: cannot modify musteri_ozet because it is a view
DELETE FROM musteri_ozet;
-- hata: cannot modify musteri_ozet because it is a view

Bu hata, görünüm üzerinde ilgili işlem için bir INSTEAD OF tetikleyicisi olmadığında ortaya çıkar. Çözüm ya işlemi doğrudan alttaki tabloya yapmak ya da görünüme tetikleyici eklemektir.

INSTEAD OF tetikleyicilerle yazılabilir görünüm

INSTEAD OF tetikleyicileri yalnızca görünümler üzerinde tanımlanabilir; BEFORE ve AFTER tetikleyicileri ise yalnızca sıradan tablolarda çalışır. Görünüm üzerinde INSTEAD OF INSERT tetikleyicisi varsa, o görünüme INSERT yazdığınızda gerçek bir ekleme olmaz; onun yerine tetikleyicinin gövdesindeki ifadeler çalışır. UPDATE ve DELETE için de aynı mantık geçerlidir. Görünüme yazılmak istenen değerler, tetikleyici gövdesinde NEW adlı özel satır üzerinden, değiştirilen ya da silinen satırın eski değerleri OLD üzerinden okunur.

Aşağıdaki örnek, uygulamalarda sık görülen "yumuşak silme" düzenini kuruyor. Uygulama yalnızca aktif_musteri görünümüyle çalışıyor; bu görünümden silinen bir müşteri aslında tablodan silinmiyor, silindi işareti 1 yapılıyor:

CREATE TABLE musteri(
  id INTEGER PRIMARY KEY, ad TEXT NOT NULL, eposta TEXT,
  silindi INTEGER NOT NULL DEFAULT 0
);
INSERT INTO musteri(ad, eposta) VALUES ('Ayşe', 'ayse@ornek.com'), ('Mehmet', 'mehmet@ornek.com');

CREATE VIEW aktif_musteri(id, ad, eposta) AS
  SELECT id, ad, eposta FROM musteri WHERE silindi = 0;

CREATE TRIGGER aktif_musteri_ekle INSTEAD OF INSERT ON aktif_musteri
BEGIN
  INSERT INTO musteri(ad, eposta) VALUES (NEW.ad, NEW.eposta);
END;

CREATE TRIGGER aktif_musteri_guncelle INSTEAD OF UPDATE OF eposta ON aktif_musteri
BEGIN
  UPDATE musteri SET eposta = NEW.eposta WHERE id = OLD.id;
END;

CREATE TRIGGER aktif_musteri_sil INSTEAD OF DELETE ON aktif_musteri
BEGIN
  UPDATE musteri SET silindi = 1 WHERE id = OLD.id;
END;

INSERT INTO aktif_musteri(ad, eposta) VALUES ('Zeynep', 'zeynep@ornek.com');
UPDATE aktif_musteri SET eposta = 'ayse.y@ornek.com' WHERE ad = 'Ayşe';
DELETE FROM aktif_musteri WHERE ad = 'Mehmet';

SELECT * FROM aktif_musteri;
-- çıktı:
-- 1|Ayşe|ayse.y@ornek.com
-- 3|Zeynep|zeynep@ornek.com
SELECT id, ad, silindi FROM musteri;
-- çıktı:
-- 1|Ayşe|0
-- 2|Mehmet|1
-- 3|Zeynep|0

Mehmet görünümden kayboldu ama tabloda silindi = 1 olarak duruyor; Zeynep görünüme yazılan INSERT ile gerçek tabloya eklendi. Güncelleme tetikleyicisi UPDATE OF eposta biçiminde yazıldığı için yalnızca SET kısmında eposta sütunu geçen güncellemeleri karşılar. Başka bir sütunu güncellemeye çalıştığınızda, o sütun için tetikleyici olmadığından yine görünüm hatası alırsınız:

UPDATE aktif_musteri SET ad = 'Ali' WHERE id = 1;
-- hata: cannot modify aktif_musteri because it is a view

Bu davranış, görünüm üzerinden hangi sütunların değiştirilebileceğini bilinçli olarak sınırlamak için kullanılabilir. Bir ayrıntıyı daha bilmekte fayda var: belgelere göre C arayüzündeki sqlite3_changes() ve sqlite3_total_changes() INSTEAD OF tetikleyicisi çalışmalarını saymaz. Denememizde görünüme yapılan DELETE sonrasında changes() fonksiyonu 0 döndürdü. Uygulamanız "kaç satır etkilendi" bilgisine göre karar veriyorsa, görünüm üzerinden yazarken bu sayıya güvenmeyin. Tetikleyicilerin geri kalan kuralları için SQLite trigger yazısına bakın.

TEMP görünümler ve şema adı

CREATE ile VIEW arasına TEMP veya TEMPORARY yazarsanız görünüm yalnızca onu oluşturan veritabanı bağlantısında görünür ve bağlantı kapandığında kendiliğinden silinir. Bu tür görünümler ana veritabanı dosyasına yazılmaz; temp.sqlite_schema içinde listelenir. Bir oturum boyunca tekrar tekrar kullanacağınız uzun bir sorguyu kalıcı şemayı kirletmeden kısaltmak için kullanışlıdır.

CREATE TEMP VIEW gecici AS SELECT ad FROM urun;
SELECT type, name FROM temp.sqlite_schema;
-- çıktı: view|gecici

CREATE TEMP VIEW main.v2 AS SELECT 1;
-- hata: temporary table name must be unqualified

CREATE VIEW IF NOT EXISTS v_adli AS SELECT 1;   -- hata yok, hiçbir şey yapmaz
CREATE VIEW v_adli AS SELECT 1;
-- hata: view v_adli already exists

Şema adı ile TEMP anahtar kelimesini birlikte kullanmak, şema adı temp olmadığı sürece hatadır. Şema adı da TEMP de verilmezse görünüm main veritabanında oluşturulur. Aynı adla ikinci bir görünüm oluşturmak hata verir; IF NOT EXISTS eklendiğinde komut sessizce hiçbir şey yapmaz. Dikkat: IF NOT EXISTS mevcut görünümün tanımını güncellemez. Bir görünümün SELECT'ini değiştirmek için önce DROP VIEW, sonra yeni CREATE VIEW çalıştırmanız gerekir; SQLite'ta görünümü yerinde değiştiren bir ALTER VIEW komutu yoktur.

Bağımlı tablo silinir veya yeniden adlandırılırsa

Bir görünümün kullandığı tablo silindiğinde SQLite bunu engellemez. Görünüm şemada kalır, ancak kullanılmaya çalışıldığında hata verir. DROP VIEW ise yalnızca görünüm tanımını şemadan kaldırır, alttaki tablolardaki veriye dokunmaz.

DROP TABLE urun;          -- görünüm varken bile tablo silinebilir
SELECT * FROM v_adli;
-- hata: no such table: main.urun

DROP VIEW v_yok;
-- hata: no such view: v_yok
DROP VIEW IF EXISTS v_yok;  -- hata yok

Tablo yeniden adlandırıldığında durum farklıdır. SQLite 3.25.0 sürümünden beri ALTER TABLE RENAME, görünüm tanımlarındaki tablo adlarını da günceller; sütun yeniden adlandırma (RENAME COLUMN) da sütunu kullanan görünümleri düzenler. PRAGMA legacy_alter_table=ON ayarı bu güncellemeyi kapatır. Şema değişikliği sırasında görünümlerin nasıl korunacağı için SQLite şema göçü ve ALTER TABLE yazılarına bakın.

Görünüm, CTE ve CREATE TABLE AS karşılaştırması

Bir sorguyu adlandırmanın SQLite'ta üç yaygın yolu vardır ve her biri farklı bir ihtiyaca cevap verir:

CTE kullanımı için SQLite WITH ve recursive CTE, tablo oluşturma seçenekleri için SQLite CREATE TABLE yazılarına bakın.

Sık yapılan hatalar

• Görünüm sütunlarını adlandırmadan bırakıp uygulamada otomatik oluşan adlara güvenmek.
• Görünümün sonucu önbelleğe aldığını sanmak; her sorgu alttaki SELECT'i yeniden çalıştırır.
• Görünüme BEFORE veya AFTER tetikleyicisi eklemeye çalışmak; SQLite bunu "cannot create BEFORE trigger on view" hatasıyla reddeder.
• INSTEAD OF UPDATE OF ile yalnızca bazı sütunları karşılayıp diğer sütunların güncellenebileceğini varsaymak.
• Görünümün tanımını değiştirmek için CREATE VIEW IF NOT EXISTS kullanmak; mevcut tanım olduğu gibi kalır.
• Görünümün kullandığı tabloyu silip görünümü bırakmak; hata ancak görünüm kullanıldığında ortaya çıkar.

Sık Sorulan Sorular

SQLite materialized view destekliyor mu?

Hayır, SQLite görünümleri her zaman sorgu anında hesaplanır. Sonucun kalıcı bir kopyasına ihtiyacınız varsa CREATE TABLE ... AS SELECT ile bir tablo oluşturabilir ve bu tabloyu tetikleyicilerle ya da zamanlanmış bir işle kendiniz güncelleyebilirsiniz.

Görünüm üzerinde indeks oluşturabilir miyim?

Hayır, CREATE INDEX yalnızca tablolar üzerinde çalışır. Görünüm üzerinden yapılan sorgular alttaki tabloların indekslerini kullanır; bu yüzden indeksleri görünümün WHERE ve JOIN koşullarında geçen tablo sütunlarına göre planlayın. Ayrıntılar için SQLite index oluşturma yazısına bakın.

Bir görünüm başka bir görünümü kullanabilir mi?

Evet. Görünüm, FROM kısmında tablo adının yerine kullanılabildiği için başka bir görünümün SELECT ifadesinde de kullanılabilir. Denememizde v1 görünümünün satırlarını sayan v2 görünümü sorunsuz çalıştı. Zincir uzadıkça hangi tablonun nerede kullanıldığını izlemek zorlaşır; alttaki bir tabloyu silmeden veya yeniden adlandırmadan önce sqlite_schema içinde sql sütununda tablo adını aratarak bağımlı görünümleri bulun.

Görünümün tanımını nasıl görürüm?

SELECT sql FROM sqlite_schema WHERE type = 'view' AND name = 'görünüm_adı' sorgusu tanımın metnini verir. Komut satırında .schema görünüm_adı da aynı metni gösterir.

Görünümde ORDER BY kullanmalı mıyım?

Söz dizimi buna izin verir, ancak sonuç sırasının önemli olduğu yerde ORDER BY ifadesini görünümü kullanan dış sorguya yazmak daha açık bir yoldur. Dış sorgu kendi sıralamasını belirttiğinde görünümün içindeki sıralama sonucu belirlemez.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: SQLite öğrenme rehberi · SQLite trigger · SQLite tabloları listeleme · SQLite WITH ve CTE · SQLite EXPLAIN QUERY PLAN