Generated Column di SQLite: VIRTUAL vs STORED dengan Contoh

Generated Column Adalah Kolom Hasil Perhitungan

Generated column adalah kolom yang nilainya berasal dari sebuah ekspresi, bukan dari INSERT. Kamu mendeklarasikan rumusnya sekali di CREATE TABLE, dan SQLite mengurus sisanya. Kamu tidak pernah menulis ke kolom itu; mencobanya akan menghasilkan error.

Contoh sesingkat mungkin:

CREATE TABLE products (
    id      INTEGER PRIMARY KEY,
    price   REAL NOT NULL,
    tax     REAL NOT NULL,
    total   REAL GENERATED ALWAYS AS (price + tax)
);

INSERT INTO products (price, tax) VALUES (100.0, 8.5);
INSERT INTO products (price, tax) VALUES (49.99, 4.25);

SELECT id, price, tax, total FROM products;

total tidak pernah di-insert, tapi muncul di setiap baris. SQLite menghitung ulang nilainya dari price + tax setiap kali kamu membaca baris itu. Perbarui salah satu kolom, dan total ikut berubah.

Frasa kata kunci GENERATED ALWAYS AS wajib ditulis. ALWAYS hanyalah formalitas standar SQL, karena tidak ada pilihan lain di SQLite.

VIRTUAL vs STORED

Setiap generated column termasuk salah satu dari dua jenis. Bawaannya adalah VIRTUAL:

CREATE TABLE orders (
    id        INTEGER PRIMARY KEY,
    qty       INTEGER NOT NULL,
    unit      REAL NOT NULL,
    -- recomputed on every SELECT
    subtotal  REAL GENERATED ALWAYS AS (qty * unit) VIRTUAL,
    -- computed once and saved to disk
    tax       REAL GENERATED ALWAYS AS (qty * unit * 0.08) STORED
);

INSERT INTO orders (qty, unit) VALUES (3, 19.99);
SELECT * FROM orders;

Gambaran sederhananya:

• VIRTUAL: memakan nol byte di disk, memakan CPU setiap kali dibaca. Murah untuk ditambahkan, murah untuk diubah nanti.
• STORED: memakan ruang disk, tidak ada biaya tambahan saat dibaca. Sepadan kalau ekspresinya mahal atau kolomnya jauh lebih sering dibaca daripada ditulis.

Kalau kamu tidak menulis kata kunci, kamu mendapat VIRTUAL. Itu hampir selalu pilihan bawaan yang tepat.

Kenapa Repot? Nilai Turunan yang Bisa Di-index

Fitur andalannya adalah kamu bisa memasang index pada generated column. Dengan begitu kamu mendapat pencarian yang cepat pada nilai turunan tanpa menulis ulang setiap query.

Misalnya kamu ingin mencari email tanpa membedakan huruf besar kecil:

CREATE TABLE users (
    id           INTEGER PRIMARY KEY,
    email        TEXT NOT NULL,
    email_lower  TEXT GENERATED ALWAYS AS (lower(email)) VIRTUAL
);

CREATE INDEX users_email_lower ON users(email_lower);

INSERT INTO users (email) VALUES ('Ada@Example.com'), ('boris@example.com');

SELECT id, email FROM users WHERE email_lower = 'ada@example.com';

Index-nya mencakup bentuk huruf kecil. Query yang memfilter email_lower langsung memakai index itu. SQLite sebenarnya juga punya expression index (CREATE INDEX ... ON users(lower(email))), tapi generated column membuat nilai turunan itu terlihat sebagai kolom sungguhan yang bisa kamu SELECT, rujuk di view, dan pakai ulang dari kode aplikasi.

Mengambil Nilai dari JSON

Generated column bersinar di atas JSON. Dukungan JSON di SQLite menyediakan ->> untuk mengambil nilai skalar; bungkus itu dalam generated column, dan kamu mendapat field bertipe yang bisa di-index di atas blob yang fleksibel.

CREATE TABLE events (
    id       INTEGER PRIMARY KEY,
    payload  TEXT NOT NULL,
    user_id  INTEGER GENERATED ALWAYS AS (payload ->> '$.user_id') VIRTUAL,
    kind     TEXT    GENERATED ALWAYS AS (payload ->> '$.kind') VIRTUAL
);

CREATE INDEX events_user_id ON events(user_id);

INSERT INTO events (payload) VALUES
    ('{"user_id": 1, "kind": "login"}'),
    ('{"user_id": 2, "kind": "purchase"}'),
    ('{"user_id": 1, "kind": "logout"}');

SELECT id, kind FROM events WHERE user_id = 1;

user_id dan kind terlihat seperti kolom biasa bagi query-mu, tapi datanya tersimpan di payload. Ubah JSON-nya, dan kolom-kolom itu ikut diperbarui. Index pada user_id membuat pencariannya cepat.

Aturan dan Batasan

Beberapa hal yang ditegakkan SQLite, dan sebaiknya kamu tahu sebelum menabraknya:

• Ekspresinya harus deterministik. random(), datetime('now'), dan fungsi non-deterministik lainnya tidak diizinkan. Nilainya harus bisa dihasilkan ulang dari baris itu.
• Ekspresinya hanya boleh merujuk kolom di baris yang sama. Tidak boleh ada subquery, agregat, atau tabel lain.
• Kamu tidak bisa melakukan INSERT atau UPDATE langsung ke generated column. INSERT INTO products (total) VALUES (5) adalah error.
• Kolom STORED tidak bisa ditambahkan dengan ALTER TABLE ... ADD COLUMN. Hanya VIRTUAL yang bisa ditambahkan belakangan.
• Generated column boleh punya constraint NOT NULL, CHECK, UNIQUE, bahkan FOREIGN KEY. Untuk keperluan itu, perilakunya sama seperti kolom lain.

Demo singkat aturan penulisannya:

sqlite> INSERT INTO products (price, tax, total) VALUES (10, 1, 999);
Runtime error: cannot INSERT into generated column "total"

Perbaikannya adalah mengeluarkan generated column dari daftar kolom INSERT dan membiarkan SQLite yang menghitungnya.

Memilih VIRTUAL atau STORED

Keputusannya biasanya bergantung pada perbandingan baca dan tulis serta biaya ekspresinya:

CREATE TABLE measurements (
    id          INTEGER PRIMARY KEY,
    celsius     REAL NOT NULL,
    -- Cheap arithmetic, fine to recompute every read.
    fahrenheit  REAL GENERATED ALWAYS AS (celsius * 9.0 / 5.0 + 32) VIRTUAL,
    -- Heavier expression, queried often, written rarely → store it.
    bucket      TEXT GENERATED ALWAYS AS (
        CASE
            WHEN celsius < 0  THEN 'freezing'
            WHEN celsius < 15 THEN 'cool'
            WHEN celsius < 25 THEN 'mild'
            ELSE 'hot'
        END
    ) STORED
);

INSERT INTO measurements (celsius) VALUES (-3), (10), (22), (31);
SELECT celsius, fahrenheit, bucket FROM measurements;

Patokan praktisnya:

• Pakai VIRTUAL sebagai bawaan. Gratis saat penulisan dan cocok untuk hampir semua hal.
• Ganti ke STORED kalau kamu memasang index pada kolom itu di tabel yang sering ditulisi (index-nya toh butuh nilai yang tersimpan), atau kalau ekspresinya memang mahal.
• Jangan terlalu dipusingkan. Jenisnya memang bagian dari skema, tapi kamu bisa menghapus dan membuat ulang kolomnya kalau berubah pikiran, setidaknya untuk VIRTUAL.

Generated Column vs View

Ada tumpang tindih dengan view: keduanya menampilkan nilai hasil perhitungan tanpa menyimpannya (yah, kadang-kadang). Pembagiannya biasanya seperti ini:

• Generated column milik satu baris dan satu tabel. Pakai untuk turunan per baris: memformat email, mengambil field JSON, menghitung total.
• View adalah query yang disimpan. Pakai kalau perhitungannya melibatkan join, agregasi, atau penyaringan lintas baris.

Kamu bisa menggabungkan keduanya. Sebuah view bisa melakukan SELECT dari tabel yang punya generated column lalu menambahkan konteks lewat join. Generated column berada di lapisan penyimpanan; view berada di lapisan query.

Berikutnya: ATTACH DATABASE

Generated column memungkinkan satu tabel menghitung nilainya sendiri. Halaman berikutnya bergerak ke arah sebaliknya: menghubungkan beberapa database SQLite sekaligus dengan ATTACH DATABASE, sehingga satu query bisa menjangkau beberapa file.