SQLite Generated Columns (Hesaplanan Sütunlar)

Söz dizimi ve ilk örnek

Bir sipariş satırında adet ve birim fiyattan toplamı, toplamdan da vergili tutarı türetelim. Para değerlerini ondalık sayı hatalarından kaçınmak için kuruş cinsinden tamsayı olarak tutuyoruz. GENERATED ALWAYS kelimeleri ve sondaki VIRTUAL ya da STORED isteğe bağlıdır; zorunlu olan yalnızca AS ve parantez içindeki ifadedir:

CREATE TABLE satir(
  id      INTEGER PRIMARY KEY,
  adet    INTEGER NOT NULL,
  birim   INTEGER NOT NULL,                              -- kuruş cinsinden
  toplam  INTEGER GENERATED ALWAYS AS (adet * birim) VIRTUAL,
  kdvli   INTEGER AS (toplam * 120 / 100) STORED
);

INSERT INTO satir(adet, birim) VALUES (3, 250), (1, 1999);
SELECT id, adet, birim, toplam, kdvli FROM satir;
-- çıktı:
-- 1|3|250|750|900
-- 2|1|1999|1999|2398

UPDATE satir SET adet = 4 WHERE id = 1;
SELECT id, toplam, kdvli FROM satir WHERE id = 1;
-- çıktı: 1|1000|1200

adet değiştiğinde iki hesaplanan sütun da kendiliğinden güncellendi. Bir hesaplanan sütunun değerini değiştirmenin tek yolu, ifadede kullanılan sıradan sütunları değiştirmektir. Örnekteki kdvli sütunu başka bir hesaplanan sütuna, toplam sütununa dayanıyor; bu serbesttir, yeter ki bir sütun doğrudan veya dolaylı olarak kendine dayanmasın. Hesaplanan sütunlar sütun listesinin herhangi bir yerinde durabilir, sona konmaları gerekmez. Ondalık sayılarla çalışmanın inceliklerini matematik fonksiyonları yazısında bulabilirsiniz.

Hesaplanan sütuna yazılamaz

INSERT veya UPDATE ile hesaplanan sütuna değer vermeye çalışmak, ifade hazırlanırken hata verir. Sütun listesi vermeden yapılan bir INSERT ise yalnızca sıradan sütunları sayar; yani üç sıradan sütunu olan bu tabloya üç değer vermeniz yeterlidir. SELECT * ise hesaplanan sütunları da döndürür:

INSERT INTO satir(adet, birim, toplam) VALUES (1, 1, 1);
-- hata: cannot INSERT into generated column "toplam"
UPDATE satir SET toplam = 0;
-- hata: cannot UPDATE generated column "toplam"

INSERT INTO satir VALUES (9, 2, 5);     -- yalnızca sıradan sütunlar sayılır
SELECT * FROM satir WHERE id = 9;
-- çıktı: 9|2|5|10|12

VIRTUAL mı STORED mı

SQL açısından iki tür neredeyse aynıdır; sorgular aynı sonucu verir. Fark kaynak kullanımındadır: STORED sütunlar dosyada yer kaplar ama okuma sırasında hesap gerektirmez; VIRTUAL sütunlar yer kaplamaz, her okunuşta yeniden hesaplanır. Belgelerin belirttiği tek işlevsel fark, ALTER TABLE ADD COLUMN ile yalnızca VIRTUAL sütun eklenebilmesidir.

Kabaca şöyle düşünebilirsiniz: ifade ucuzsa ve sütun sık okunmuyorsa VIRTUAL yeterlidir. İfade pahalıysa, örneğin büyük bir JSON belgesinden değer çıkarıyorsa ve sık okunuyorsa STORED mantıklı olabilir. Her iki durumda da sütuna indeks koyarsanız değer indekste saklanır ve indeksli aramalar hesap yapmadan çalışır. Karar vermeden önce kendi verinizle ölçmek en doğrusudur.

Sütunları listelemek: table_info ve table_xinfo

Hesaplanan sütunlar PRAGMA table_info çıktısında görünmez; bu, şemayı okuyan araçlarda sık karşılaşılan bir şaşkınlık nedenidir. Daha yeni olan PRAGMA table_xinfo bütün sütunları döndürür ve hidden sütununda türü belirtir: sıradan sütun için 0, VIRTUAL için 2, STORED için 3. Şemayı sorgulamanın diğer yolları için tabloları listeleme yazısına bakın.

SELECT cid, name, type FROM pragma_table_info('satir');
-- çıktı:
-- 0|id|INTEGER
-- 1|adet|INTEGER
-- 2|birim|INTEGER

SELECT cid, name, type, hidden FROM pragma_table_xinfo('satir');
-- çıktı:
-- 0|id|INTEGER|0
-- 1|adet|INTEGER|0
-- 2|birim|INTEGER|0
-- 3|toplam|INTEGER|2
-- 4|kdvli|INTEGER|3

Sınırlamalar

Hesaplanan sütun ifadesi aynı satırdaki sütunlara ve sabitlere başvurabilir, ancak yalnızca deterministik skaler fonksiyonlar kullanabilir. Alt sorgu, toplama fonksiyonu, pencere fonksiyonu ve tablo değerli fonksiyon yasaktır. Ayrıca hesaplanan sütun DEFAULT alamaz, birincil anahtarın parçası olamaz ve rowid'e doğrudan başvuramaz; INTEGER PRIMARY KEY sütununa ise başvurabilir. Her tabloda en az bir sıradan sütun bulunmalıdır. Bu kuralların hepsini tek seferde deneyelim:

CREATE TABLE e1(a INT, b INT AS (a * 2) DEFAULT 0);
-- hata: cannot use DEFAULT on a generated column
CREATE TABLE e2(a INT, b INT AS (a * 2) PRIMARY KEY);
-- hata: generated columns cannot be part of the PRIMARY KEY
CREATE TABLE e3(a INT, b REAL AS (random()));
-- hata: non-deterministic functions prohibited in generated columns
CREATE TABLE e4(a INT, b INT AS ((SELECT 1)));
-- hata: subqueries prohibited in generated columns
CREATE TABLE e5(a INT, b INT AS (rowid + 1));
-- hata: no such column: rowid
CREATE TABLE e6(a INT, b INT AS (c + 1), c INT AS (b + 1));
-- hata: generated column loop on "c"
CREATE TABLE e7(b INT AS (1));
-- hata: must have at least one non-generated column

Tarih ve saat fonksiyonları özel bir durumdur. Belgelere göre bu fonksiyonlar normalde deterministik sayılır, ancak 'now' değeri ya da localtime ve utc değiştiricileriyle çağrıldıklarında deterministik olmaktan çıkar. Argümanlar çalışma anında belli olduğu için hata da tablo oluşturulurken değil, satır yazılırken alınır:

CREATE TABLE e8(a INT, b TEXT AS (datetime('now')));   -- tablo oluşur
INSERT INTO e8(a) VALUES (1);
-- hata: non-deterministic use of datetime() in a generated column

"Kayıt zamanı" gibi bir değer istiyorsanız hesaplanan sütun yerine DEFAULT CURRENT_TIMESTAMP kullanan sıradan bir sütun tanımlayın. Tarih fonksiyonları için tarih ve saat fonksiyonları yazısına bakın.

Tip ve sıralama kuralı nereden gelir

Hesaplanan sütunun tipi ve sıralama kuralı yalnızca sütun tanımından belirlenir; ifadenin kendi tipi veya sıralama kuralı bir etki yapmaz. SQLite ifadenin sonucunu, sıradan sütunlarda olduğu gibi tanımlı tipin eğilimine dönüştürmeye çalışır. Aşağıda toplama sonucu metne, birleştirme sonucu tamsayıya çevrildi:

CREATE TABLE af(a INT, b INT, metin TEXT AS (a + b), sayi INT AS (a || b));
INSERT INTO af(a, b) VALUES (2, 3);

SELECT metin, typeof(metin), sayi, typeof(sayi) FROM af;
-- çıktı: 5|text|23|integer

Aynı kural sıralama için de geçerlidir. Tam adı büyük küçük harf duyarsız karşılaştırmak için COLLATE NOCASE sütun tanımına yazılır. NOCASE yalnızca ASCII harflerini eşler; Türkçe harflerdeki sınırları COLLATE ve NOCASE yazısında ayrıntılı anlattık.

CREATE TABLE ad(
  ad    TEXT,
  soyad TEXT,
  tam   TEXT COLLATE NOCASE AS (ad || ' ' || soyad)
);
INSERT INTO ad(ad, soyad) VALUES ('Deniz', 'Kaya');
SELECT tam FROM ad WHERE tam = 'DENIZ KAYA';
-- çıktı: Deniz Kaya

Kısıtlar: NOT NULL, CHECK, UNIQUE, FOREIGN KEY

Hesaplanan sütunlara sıradan sütunlar gibi NOT NULL, CHECK, UNIQUE ve yabancı anahtar kısıtları konabilir. Bu, ifade sonucunun belirli bir kurala uymasını şart koşmanın pratik bir yoludur. Örneğin iki sütunun birlikte belirlediği bir değerin benzersiz olmasını isteyebilirsiniz:

CREATE TABLE ch(a INT, b INT AS (a * 2) CHECK (b < 10));
INSERT INTO ch(a) VALUES (3);
INSERT INTO ch(a) VALUES (7);
-- hata: CHECK constraint failed: b < 10

CREATE TABLE u(a INT, b INT AS (a % 3) UNIQUE);
INSERT INTO u(a) VALUES (1), (2);
INSERT INTO u(a) VALUES (4);          -- 4 % 3 = 1, zaten var
-- hata: UNIQUE constraint failed: u.b

Mini örnek: e-posta adreslerini normalleştirerek benzersiz tutmak

Kullanıcıların e-posta adreslerini yazdıkları gibi saklamak, ama aynı adresin büyük harfle veya başında boşlukla ikinci kez kaydedilmesini engellemek istediğinizi düşünün. Normalleştirilmiş biçimi hesaplanan bir sütunda tutup ona UNIQUE koymak bu işi tek satırda çözer. UNIQUE kısıtı otomatik bir indeks de oluşturduğu için aramalar bu sütun üzerinden hızlı yapılır. Örnekteki adresler yalnızca ASCII harf içerdiği için lower() burada güvenle çalışır; Türkçe harflerde bu fonksiyonun sınırları için metin fonksiyonları yazısına bakın.

CREATE TABLE uye(
  id       INTEGER PRIMARY KEY,
  eposta   TEXT NOT NULL,
  eposta_k TEXT AS (lower(trim(eposta))) UNIQUE
);
INSERT INTO uye(eposta) VALUES ('Ada@Example.com');
INSERT INTO uye(eposta) VALUES (' ada@example.com');
-- hata: UNIQUE constraint failed: uye.eposta_k

SELECT id, eposta, eposta_k FROM uye WHERE eposta_k = 'ada@example.com';
-- çıktı: 1|Ada@Example.com|ada@example.com

EXPLAIN QUERY PLAN SELECT id FROM uye WHERE eposta_k = 'ada@example.com';
-- çıktı:
-- QUERY PLAN
-- `--SEARCH uye USING INDEX sqlite_autoindex_uye_1 (eposta_k=?)

Kullanıcıya gösterilen değer özgün yazım olarak kalırken karşılaştırmalar normalleştirilmiş sütunla yapılır. Aynı kalıbı telefon numaralarındaki boşlukları temizlemek veya ürün kodlarını tek bir biçime getirmek için de kullanabilirsiniz.

Yabancı anahtarın nasıl denetlendiği foreign key yazısında anlatılıyor. Hesaplanan sütunlar STRICT tablolarda da aynı biçimde çalışır.

İndeksleme ve JSON alanları

Hesaplanan sütunlar indekslere sıradan sütunlar gibi katılabilir. STORED sütun üzerindeki indeks sıradan bir indekstir; bir veya daha fazla VIRTUAL sütun içeren indeks ise belgelere göre bir ifade indeksidir. Bunun en yaygın kullanımı, JSON metni içindeki bir alanı sütun gibi sorgulanabilir ve indekslenebilir hâle getirmektir. Aşağıdaki örnekte ->> işleci JSON içinden SQL değeri çıkarıyor; işlecin ayrıntıları JSON fonksiyonları yazısında.

CREATE TABLE olay(
  id           INTEGER PRIMARY KEY,
  veri         TEXT NOT NULL,
  tur          TEXT AS (veri ->> '$.tur'),
  kullanici_id INTEGER AS (veri ->> '$.kullanici') STORED
);
INSERT INTO olay(veri) VALUES
  ('{"tur":"giris","kullanici":7}'),
  ('{"tur":"satis","kullanici":7,"tutar":120}'),
  ('{"tur":"giris","kullanici":9}');
CREATE INDEX olay_tur ON olay(tur);
CREATE INDEX olay_kullanici ON olay(kullanici_id);

SELECT id, tur, kullanici_id, typeof(kullanici_id) FROM olay;
-- çıktı:
-- 1|giris|7|integer
-- 2|satis|7|integer
-- 3|giris|9|integer

EXPLAIN QUERY PLAN SELECT id FROM olay WHERE tur = 'giris';
-- çıktı:
-- QUERY PLAN
-- `--SEARCH olay USING INDEX olay_tur (tur=?)

EXPLAIN QUERY PLAN SELECT id FROM olay WHERE kullanici_id = 7;
-- çıktı:
-- QUERY PLAN
-- `--SEARCH olay USING INDEX olay_kullanici (kullanici_id=?)

Her iki sorgu da ilgili indeksi kullandı. Hesaplanan sütunun avantajı, sorguyu yazan kişinin ifadeyi tam olarak tekrarlamak zorunda kalmamasıdır. Aynı ifadeyi doğrudan WHERE içine yazdığımızda ise planlayıcı tabloyu baştan sona taradı:

EXPLAIN QUERY PLAN SELECT id FROM olay WHERE veri ->> '$.tur' = 'giris';
-- çıktı:
-- QUERY PLAN
-- `--SCAN olay

Burada iki ayrıntıya dikkat edin. Birincisi, ifade indeksleri yalnızca sorgudaki ifade indeksteki ifadeyle aynı yazıldığında kullanılır; belgelerin ifadesiyle planlayıcı cebir yapmaz. Hesaplanan sütun bu eşleştirme derdini ortadan kaldırır, çünkü sorguda yalnızca sütun adını yazarsınız. İkincisi, kullanici_id sütununu INTEGER tanımladığımız için JSON'dan gelen değer tamsayı eğilimi aldı. İfade indeksleri ve kısmi indeksler için partial index ve ifade indeksi yazısına, planı okumak için EXPLAIN QUERY PLAN yazısına bakabilirsiniz.

ALTER TABLE ile hesaplanan sütun eklemek

Var olan bir tabloya ALTER TABLE ADD COLUMN ile yalnızca VIRTUAL hesaplanan sütun ekleyebilirsiniz; STORED sütun eklemek için tabloyu yeniden oluşturmanız gerekir. NOT NULL kısıtlı bir hesaplanan sütun eklediğinizde, SQLite 3.37.0 sürümünden beri kısıt mevcut bütün satırlar için denetlenir ve bir satır bile uymazsa ekleme yapılmaz. Bu denetim tablodaki veri miktarıyla orantılı zaman alır:

CREATE TABLE k(id INTEGER PRIMARY KEY, ad TEXT);
INSERT INTO k(ad) VALUES ('Ali'), ('Veli');

ALTER TABLE k ADD COLUMN buyuk TEXT AS (upper(ad)) STORED;
-- hata: cannot add a STORED column
ALTER TABLE k ADD COLUMN uzunluk INT AS (length(ad));      -- VIRTUAL: geçerli
SELECT id, ad, uzunluk FROM k;
-- çıktı:
-- 1|Ali|3
-- 2|Veli|4

ALTER TABLE k ADD COLUMN zorunlu INT AS (NULLIF(id, 2)) NOT NULL;
-- hata: NOT NULL constraint failed
ALTER TABLE k ADD COLUMN zorunlu INT AS (id * 10) NOT NULL;
SELECT id, zorunlu FROM k;
-- çıktı:
-- 1|10
-- 2|20

Bu kuralların geri kalanı ve STORED sütun eklemek için tabloyu güvenle yeniden oluşturma adımları ALTER TABLE yazısında anlatılıyor.

Bir tuhaflık: INSERT INTO ... SELECT *

Aynı şemaya sahip iki tablo arasında veri aktarırken INSERT INTO hedef SELECT * FROM kaynak yazmak teknik olarak hatalıdır; çünkü SELECT * hesaplanan sütunları da döndürür, INSERT ise yalnızca sıradan sütunlar için değer bekler. Belgeler SQLite'ın bu kullanıma yine de izin verdiğini ve içeriği doğru aktardığını belirtir:

CREATE TABLE kaynak(a INT, b INT AS (a * 2) STORED);
CREATE TABLE hedef(a INT, b INT AS (a * 2) STORED);
INSERT INTO kaynak(a) VALUES (1), (5);

INSERT INTO hedef SELECT * FROM kaynak;     -- teknik olarak yanlış, ama izin verilir
SELECT a, b FROM hedef;
-- çıktı:
-- 1|2
-- 5|10

Bu davranış, iki tablonun şemasının birebir aynı olduğu durum için tanınmış bir istisnadır. Taşınabilir ve okunabilir kod için sütun listesini açıkça yazmak daha güvenlidir.

Hesaplanan sütun, görünüm ve tetikleyici

Türetilmiş bir değere ihtiyaç duyduğunuzda üç araçtan birini seçebilirsiniz. Hesaplanan sütun, değeri tablonun bir parçası yapar: her sorguda aynı adla erişilir, kısıt ve indeks alabilir, ama yalnızca aynı satırdaki verilerle ve deterministik fonksiyonlarla çalışır. Görünüm, birden fazla tabloyu birleştiren ya da toplama yapan türetmeler için uygundur; değer her sorguda yeniden hesaplanır ve kendi başına bir kısıt taşımaz. Tetikleyici ise başka bir tabloya bakarak sıradan bir sütuna değer yazabilir; bunun karşılığında mantık şemada daha dağınık durur ve değer sıradan bir sütunda durduğu için doğrudan bir UPDATE ile kaynak verilerden bağımsız olarak değiştirilebilir. Kural basittir: hesap tek satırla sınırlıysa önce hesaplanan sütunu düşünün, değilse görünüme ya da tetikleyiciye geçin.

Uyumluluk

Hesaplanan sütunlar SQLite 3.31.0 ile geldi. Bu tür bir sütun içeren veritabanını daha eski bir sürümle açmaya çalışmak şemanın bozuk olduğu yönünde bir hata verir. Hesaplanan sütun içermeyen veritabanları ise yeni sürümlerle oluşturulmuş olsa da eski sürümlerce okunabilir. Uygulamanızın kullandığı kütüphane sürümünü kontrol edin; dil bağlayıcıları çoğu zaman kendi SQLite kopyasını taşır.

Sık yapılan hatalar

• Hesaplanan sütuna değer yazmaya çalışmak: INSERT ve UPDATE hata verir; kaynak sütunları değiştirin.
• Şema okuyan kodda table_info kullanmak: Hesaplanan sütunlar görünmez; table_xinfo kullanın.
• Zaman damgası için datetime('now') kullanmak: Yazma sırasında hata alırsınız; DEFAULT CURRENT_TIMESTAMP kullanın.
• ALTER TABLE ile STORED eklemeye çalışmak: Yalnızca VIRTUAL eklenebilir.
• Sütun tipini yazmamak: Tip yazılmazsa ifade sonucu BLOB eğilimiyle olduğu gibi kalır; beklenen tipi açıkça yazın.

Sık Sorulan Sorular

Hesaplanan sütun birincil anahtar olabilir mi?

Hayır. Belgelere göre hesaplanan sütunlar birincil anahtarın parçası olamaz; ileride STORED sütunlar için bu kısıtın gevşetilebileceği belirtiliyor.

Hesaplanan sütun başka bir tabloya bakabilir mi?

Hayır. İfade yalnızca aynı satırdaki sütunlara ve sabitlere başvurabilir; alt sorgu yasaktır. Başka tablolardan değer gerekiyorsa görünüm veya tetikleyici kullanın: CREATE VIEW, trigger.

VIRTUAL sütun indekslenince yine her okumada hesaplanır mı?

İndeks, sütunun değerini kendi B-ağacında saklar; indeksi kullanan bir aramada karşılaştırma bu saklanmış değerle yapılır. Belgelere göre VIRTUAL sütunun değeri ise tablodan okunduğu her seferde hesaplanır; örneğin tabloyu tarayan bir sorguda bu hesap her satır için tekrarlanır.

Hesaplanan sütun yerine ifade indeksi kullanabilir miyim?

Evet. CREATE INDEX ... ON tablo(ifade) aynı işi görür, ancak sorgularda ifadeyi indeksteki gibi aynen yazmanız gerekir. Hesaplanan sütun bu ifadeye bir ad verir ve sorguları sadeleştirir.

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 · JSON fonksiyonları · Partial index ve ifade indeksi · Index oluşturma · ALTER TABLE · CREATE TABLE ve kısıtlamalar