Kolom yang Dihasilkan

Generated column VIRTUAL dan STORED untuk nilai turunan yang selalu segar.

Daftar isi

Kolom yang dihasilkan (generated column) adalah kolom yang nilainya dihitung dari ekspresi atas kolom lain pada baris yang sama, sehingga nilai turunan seperti luas, nama lengkap, atau huruf besar untuk pencarian tidak pernah menyimpang dari sumbernya. Topik ini membahas definisi kolom dihasilkan dengan GENERATED ALWAYS AS, perbedaan mode VIRTUAL dengan STORED, batas ekspresi yang diizinkan, perilaku INSERT dan UPDATE terhadap kolom semacam ini, serta pengindeksannya. Seluruh contoh memakai ranah perpustakaan dan setiap blok SQL berdiri sendiri sehingga keluarannya dapat direproduksi ulang.

Definisi kolom dihasilkan#

Kolom dihasilkan dideklarasikan pada CREATE TABLE dengan klausa GENERATED ALWAYS AS yang diikuti ekspresi dalam tanda kurung, dan INSERT hanya mengisi kolom sumbernya saja. Nilai kolom hasil perhitungan baru terwujud saat dibaca atau saat ditulis, tergantung mode yang dipilih pada bagian berikutnya. Blok berikut mendefinisikan tabel rak dengan kolom luas yang dihitung dari panjang dikali lebar, lalu mengisi dua rak tanpa menyebut kolom luas sama sekali.

sql Kolom luas yang dihasilkan dari panjang dan lebar

CREATE TABLE rak (
  id INTEGER PRIMARY KEY,
  kode TEXT,
  panjang REAL,
  lebar REAL,
  luas REAL GENERATED ALWAYS AS (panjang * lebar)
);
INSERT INTO rak (kode, panjang, lebar) VALUES ('A1', 2.0, 1.5), ('A2', 3.0, 2.0);

SELECT kode, panjang, lebar, luas FROM rak;

text Keluaran

A1|2.0|1.5|3.0
A2|3.0|2.0|6.0

INSERT yang mengabaikan kolom luas tetap berhasil dan SELECT menampilkan nilainya seolah-olah tersimpan biasa, karena ekspresi dihitung pada saat query membaca baris. Kolom dihasilkan juga boleh merujuk kolom dihasilkan lain, dan rantai semacam itu berguna untuk menyusun turunan bertingkat. Blok berikut membangun tabel pengarang yang nama_lengkapnya menggabungkan dua kolom sumber, kemudian kata_kunci menurunkannya menjadi huruf besar dari nama_lengkap tersebut.

sql Rantai nama lengkap dan kata kunci huruf besar

CREATE TABLE pengarang (
  id INTEGER PRIMARY KEY,
  nama_depan TEXT,
  nama_belakang TEXT,
  nama_lengkap TEXT GENERATED ALWAYS AS (TRIM(nama_depan || ' ' || nama_belakang)),
  kata_kunci TEXT GENERATED ALWAYS AS (UPPER(nama_lengkap))
);
INSERT INTO pengarang (id, nama_depan, nama_belakang) VALUES
  (1, 'Dewi', 'Sartika'), (2, 'Ali', 'Murtadho');

SELECT nama_lengkap, kata_kunci FROM pengarang ORDER BY id;

text Keluaran

Dewi Sartika|DEWI SARTIKA
Ali Murtadho|ALI MURTADHO

Kedua kolom turunan muncul lengkap tanpa satu pun nilai yang ditulis manual, sehingga perbaikan pada nama_belakang otomatis merambat ke nama_lengkap dan kata_kunci. Pola kata_kunci yang berupa huruf besar menjawab kebutuhan pencarian yang tidak ingin bergantung pada cara penulisan pengguna. Sintaksnya juga dapat dipendekkan menjadi nama AS (ekspresi) tanpa kata GENERATED ALWAYS, tetapi bentuk lengkapnya lebih eksplisit dan lebih mudah dikenali saat schema dibaca ulang.

VIRTUAL dan STORED#

Kolom dihasilkan punya dua mode, yaitu VIRTUAL yang dihitung setiap kali dibaca dan STORED yang dihitung ketika baris ditulis lalu disimpan pada berkas basis data. VIRTUAL merupakan bawaan, sehingga definisi tanpa kata mode seperti kolom luas pada bagian sebelumnya adalah kolom virtual. Kata mode ditulis di akhir definisi kolom, tepat setelah ekspresinya. Blok berikut mendefinisikan dua tabel kembar dengan mode yang berbeda, kemudian membaca teks definisi yang tersimpan pada sqlite_schema untuk membandingkannya.

sql Definisi VIRTUAL dan STORED pada sqlite_schema

CREATE TABLE rak_v (
  id INTEGER PRIMARY KEY,
  panjang REAL,
  lebar REAL,
  luas REAL GENERATED ALWAYS AS (panjang * lebar) VIRTUAL
);
CREATE TABLE rak_s (
  id INTEGER PRIMARY KEY,
  panjang REAL,
  lebar REAL,
  luas REAL GENERATED ALWAYS AS (panjang * lebar) STORED
);

SELECT name, sql FROM sqlite_schema WHERE name IN ('rak_v', 'rak_s');

text Keluaran

rak_v|CREATE TABLE rak_v (
  id INTEGER PRIMARY KEY,
  panjang REAL,
  lebar REAL,
  luas REAL GENERATED ALWAYS AS (panjang * lebar) VIRTUAL
)
rak_s|CREATE TABLE rak_s (
  id INTEGER PRIMARY KEY,
  panjang REAL,
  lebar REAL,
  luas REAL GENERATED ALWAYS AS (panjang * lebar) STORED
)

Keluaran memperlihatkan kata mode yang tersimpan apa adanya pada definisi, dan tabel rak_v memakai VIRTUAL eksplisit sementara rak_s memakai STORED. Perbedaan bentuknya hanya kata akhir itu, sedangkan pemakaiannya dalam query tidak berbeda sedikit pun. Pertimbangan pemilihannya berupa tukar-menukar ruang dengan waktu, karena STORED menambah ukuran berkas tetapi tidak menghitung ulang ekspresi pada setiap pembacaan, sedangkan VIRTUAL hemat ruang tetapi membayar biaya perhitungan pada setiap SELECT. Keduanya boleh dijadikan sumber indeks, dan nilai yang terindeks ikut tersimpan pada indeks sehingga pencarian lewat indeks tidak menghitung ulang ekspresinya.

Batas ekspresi kolom dihasilkan#

Ekspresi pada kolom dihasilkan hanya boleh membaca kolom pada baris yang sama dan harus deterministik, sehingga subquery maupun fungsi yang hasilnya berubah-ubah ditolak sejak CREATE TABLE. Kolom semacam ini juga tidak boleh menjadi bagian dari PRIMARY KEY karena nilai turunannya tidak dimiliki sendiri oleh baris. Blok berikut menumpuk tiga penolakan tersebut dan menampilkan pesan error aslinya satu per satu.

sql Tiga batas yang ditolak pada CREATE TABLE

CREATE TABLE uji (a INTEGER, b GENERATED ALWAYS AS (random()));
CREATE TABLE uji2 (a INTEGER, b GENERATED ALWAYS AS ((SELECT 1)));
CREATE TABLE uji3 (a INTEGER, b INTEGER GENERATED ALWAYS AS (a * 2) PRIMARY KEY);

text Keluaran

Parse error near line 1: non-deterministic functions prohibited in generated columns
  EATE TABLE uji (a INTEGER, b GENERATED ALWAYS AS (random()));
                                      error here ---^
Parse error near line 2: subqueries prohibited in generated columns
Parse error near line 3: generated columns cannot be part of the PRIMARY KEY

Pesan pertama menolak random() karena fungsi tanpa hasil yang tetap membuat kolom tidak dapat dihitung ulang secara konsisten, dan potongan pernyataan pada keluaran menunjuk posisi pelanggarannya. Pesan kedua menolak subquery yang membaca baris lain, sedangkan pesan ketiga menolak PRIMARY KEY pada kolom dihasilkan. Larangan DEFAULT juga berlaku, dan pemakaian DEFAULT pada kolom dihasilkan ditolak dengan pesan cannot use DEFAULT on a generated column. Batas deterministik ini menjamin kolom selalu dapat dihitung ulang dengan hasil yang sama, syarat yang menjaga indeks atas kolom tersebut tetap sah. Fungsi deterministik seperti UPPER dan TRIM tetap diterima, sebagaimana dipakai pada blok nama lengkap.

Menulis dan mengindeks kolom dihasilkan#

Kolom dihasilkan tidak pernah menerima nilai dari luar, sehingga penyebutannya pada daftar kolom INSERT maupun target UPDATE langsung ditolak. Penolakan ini melindungi kekonsistenan nilai turunan, sebab nilai yang ditulis paksa akan bertentangan dengan ekspresinya. Blok berikut mencoba keduanya pada tabel rak dan menampilkan kedua pesan errornya.

sql INSERT dan UPDATE ke kolom dihasilkan

CREATE TABLE rak (
  id INTEGER PRIMARY KEY,
  kode TEXT,
  panjang REAL,
  lebar REAL,
  luas REAL GENERATED ALWAYS AS (panjang * lebar)
);
INSERT INTO rak (id, kode, panjang, lebar, luas) VALUES (1, 'A1', 2.0, 1.5, 3.0);
UPDATE rak SET luas = 9 WHERE id = 1;

text Keluaran

Parse error near line 8: cannot INSERT into generated column "luas"
Parse error near line 9: cannot UPDATE generated column "luas"

Blok berikut memperlihatkan sisi yang positif, yaitu kolom dihasilkan tetap dapat diindeks dan index benar-benar dipakai pada pencarian nilainya. Indeks dibuat atas kolom luas yang bertipe STORED, kemudian EXPLAIN QUERY PLAN memastikan rencana pencariannya memakai index tersebut.

sql Indeks pada kolom dihasilkan dan rencana query

CREATE TABLE rak (
  id INTEGER PRIMARY KEY,
  kode TEXT,
  panjang REAL,
  lebar REAL,
  luas REAL GENERATED ALWAYS AS (panjang * lebar) STORED
);
INSERT INTO rak (kode, panjang, lebar) VALUES ('A1', 2.0, 1.5), ('A2', 3.0, 2.0);
CREATE INDEX i_luas ON rak(luas);
EXPLAIN QUERY PLAN SELECT kode FROM rak WHERE luas = 4.0;

text Keluaran

QUERY PLAN
`--SEARCH rak USING INDEX i_luas (luas=?)

Rencana query berbunyi SEARCH rak USING INDEX i_luas, yang berarti pencarian luas 4.0 tidak memindai tabel sama sekali. Pola ini menjadikan kolom dihasilkan pasangan yang alami bagi ekspresi yang sering dicari, karena ekspresi ditulis sekali pada schema dan indeksnya dirawat otomatis pada setiap tulisan. Perubahan definisi ekspresi setelahnya tetap memerlukan pembuatan ulang tabel, sehingga pemilihan ekspresi sebaiknya matang sejak awal.

Kesalahan umum#

Mencoba mengisi atau memperbarui kolom dihasilkan secara langsung. Penolakannya berbunyi cannot INSERT into generated column atau cannot UPDATE generated column sesuai perintahnya, dan perbaikannya adalah menulis kolom sumber saja lalu membiarkan ekspresi menghitung sisanya. Daftar kolom pada INSERT aplikasi perlu ditinjau ulang setiap kali kolom dihasilkan ditambahkan. Kedua pesan errornya menyebut nama kolom yang bermasalah, sehingga letak kesalahannya mudah ditemukan.

Menaruh DEFAULT atau fungsi waktu pada kolom dihasilkan. Nilai bawaan ditolak dengan pesan cannot use DEFAULT on a generated column, sedangkan fungsi seperti datetime(’now’) termasuk nondeterministik dan ditolak sejak CREATE TABLE. Nilai turunan semacam itu ditaruh pada kolom biasa yang diisi aplikasi, bukan pada kolom yang dihasilkan.

Mengira nilai VIRTUAL tersimpan dan menggunakannya sebagai kunci tabel. Kolom virtual tidak menempati berkas basis data, dan PRIMARY KEY atas kolom dihasilkan ditolak dengan pesan generated columns cannot be part of the PRIMARY KEY. Jadikan kolom sumber sebagai kuncinya, lalu biarkan kolom dihasilkan menurunkan nilai pembantu dari kunci tersebut.

Ringkasan#

GENERATED ALWAYS AS (ekspresi) mendefinisikan kolom yang nilainya turun dari kolom lain pada baris yang sama, dan INSERT yang mengabaikannya tetap berhasil.
VIRTUAL dihitung setiap kali dibaca dan tidak menempati berkas, sedangkan STORED dihitung saat ditulis dan tersimpan, dan keduanya dapat diindeks.
Ekspresinya harus deterministik, tidak boleh berupa subquery, tidak boleh ber-DEFAULT, dan tidak boleh menjadi bagian dari PRIMARY KEY.
Penulisan langsung ke kolom dihasilkan ditolak dengan pesan error, sehingga satu-satunya jalur pembaruan adalah kolom sumbernya.

Referensi#

Generated Columns — SQLite — Dokumentasi resmi kolom dihasilkan, perbandingan VIRTUAL dengan STORED, dan batas ekspresinya.
SQLite Query Language — SQLite — Indeks pernyataan SQL SQLite yang memuat sintaks CREATE TABLE dan CREATE INDEX pada contoh.