Afinitas Tipe

Banyak basis data relasional memaksa setiap nilai dalam satu kolom bertipe sama, sedangkan SQLite mengambil jalan yang lebih lentur. Aturan afinitas tipe (type affinity) inilah yang menentukan kelas penyimpanan setiap nilai yang masuk ke sebuah kolom. Topik ini menjelaskan alasan kelenturan tersebut, merinci aturan penentuan afinitas dari nama tipe, memeragakan perilakunya dengan fungsi typeof(), dan menutup celah data tidak konsisten menggunakan tabel strict.

Mengapa tipe SQLite lentur#

SQLite menyimpan setiap nilai dalam salah satu dari lima storage class, yaitu NULL, INTEGER, REAL, TEXT, dan BLOB. Kelas penyimpanan melekat pada nilai, bukan pada kolomnya, sehingga satu kolom dapat memuat nilai dari beberapa kelas sekaligus. Pendekatan ini disebut manifest typing, yaitu tipe merupakan sifat nilai itu sendiri, sedangkan tipe yang ditulis pada definisi kolom hanya menjadi masukan bagi aturan afinitas.

Afinitas adalah kecenderungan yang diberikan SQLite pada sebuah kolom untuk mengonversi nilai sebelum menyimpannya. Apabila nilai yang masuk cocok dengan afinitas kolom, nilai itu disimpan apa adanya. Apabila tidak cocok, SQLite berusaha mengonversinya, misalnya teks 123 pada kolom berafinitas NUMERIC diubah menjadi bilangan bulat. Konversi hanya dilakukan bila prosesnya dapat dilakukan tanpa kehilangan informasi, sedangkan nilai yang tidak dapat dikonversi tetap disimpan dalam kelas asalnya.

Kelenturan ini lahir dari tujuan praktis. Program yang berbeda mengirim tipe yang berbeda untuk data yang sama, dan SQLite memilih menerima semuanya daripada menolak baris yang sudah dikirim. Konsekuensinya muncul pada sisi lain, yaitu kolom angka pada tabel biasa dapat berisi teks apa pun tanpa peringatan, dan masalah tersebut diselesaikan pada bagian akhir topik ini.

Aturan penentuan afinitas#

Afinitas sebuah kolom ditentukan sekali saat tabel dibuat, murni dari kata yang terkandung dalam nama tipenya, menurut lima aturan berurutan berikut.

Aturan dibaca dari atas ke bawah, dan aturan pertama yang cocok menentukan afinitas. Dengan urutan tersebut, VARCHAR(80) mengandung CHAR sehingga berafinitas TEXT, BIGINT mengandung INT sehingga berafinitas INTEGER, dan DOUBLE PRECISION mengandung DOUB sehingga berafinitas REAL. Nama tipe seperti DECIMAL(10,2) tidak memuat kata kunci mana pun pada tabel di atas, sehingga jatuh ke afinitas NUMERIC. Kolom yang ditulis tanpa tipe sama sekali berafinitas BLOB, yang praktisnya berarti nilai disimpan apa adanya.

Banyak nama tipe yang lazim di basis data lain tetap dikenali SQLite hanya karena aturan ini. Tidak ada VARCHAR maupun DOUBLE sebagai tipe tersendiri di SQLite, tetapi nama-nama tersebut tetap dapat ditulis dan afinitasnya dipetakan secara deterministik. Walaupun demikian, menggunakan kelima nama tipe bawaan SQLite tetap menjadi pilihan yang paling jelas bagi pembaca schema.

Membaca kelas penyimpanan dengan typeof()#

Fungsi typeof() mengembalikan nama kelas penyimpanan sebuah nilai, sehingga perilaku afinitas dapat diperiksa langsung. Blok berikut menyisipkan bilangan 123 ke lima kolom dengan afinitas berbeda, ditambah satu blob singkat pada kolom BLOB.

CREATE TABLE contoh (
    kolom_angka    INTEGER,
    kolom_teks     TEXT,
    kolom_blob     BLOB,
    kolom_real     REAL,
    kolom_numerik  NUMERIC
);

INSERT INTO contoh VALUES (123, 123, x'414243', 123, 123);

SELECT typeof(kolom_angka),
       typeof(kolom_teks),
       typeof(kolom_blob),
       typeof(kolom_real),
       typeof(kolom_numerik)
FROM contoh;

integer|text|blob|real|integer

Satu nilai yang sama menghasilkan lima kelas penyimpanan yang berbeda. Kolom kolom_angka berafinitas INTEGER sehingga 123 tersimpan sebagai bilangan bulat. Kolom kolom_teks berafinitas TEXT sehingga bilangan yang sama dikonversi menjadi teks 123. Kolom kolom_blob menyimpan blob apa adanya, sedangkan kolom kolom_real berafinitas REAL sehingga 123 disimpan sebagai 123.0. Kolom kolom_numerik berafinitas NUMERIC, yang menyimpan bilangan bulat sebagai bilangan bulat dan baru berpindah kelas bila perlu.

Kolom berafinitas NUMERIC paling sering mengejutkan karena konversinya berlaku dua arah. Teks yang berupa literal angka yang utuh dikonversi menjadi bilangan, sedangkan teks lain tetap disimpan sebagai teks.

CREATE TABLE konversi (nilai NUMERIC);

INSERT INTO konversi VALUES ('123'), ('12.5'), ('007'), ('abc');

SELECT quote(nilai), typeof(nilai) FROM konversi;

123|integer
12.5|real
7|integer
'abc'|text

Fungsi quote() menampilkan nilai sebagaimana tersimpan. Teks 123 menjadi bilangan bulat 123, teks 12.5 menjadi bilangan pecahan, dan teks 007 menjadi bilangan bulat 7 karena konversi dihitung dari nilai angkanya, bukan dari digit yang tertulis. Hanya teks abc yang tidak dapat dikonversi, sehingga tetap disimpan sebagai teks dan tampak dengan tanda kutip pada keluaran.

Tabel strict sebagai penutup celah#

Kelenturan afinitas membuka celah kualitas data, dan SQLite menutupnya dengan tabel strict sejak versi 3.37.0 (2021). Tabel yang dibuat dengan klausa STRICT hanya menerima enam nama tipe, yaitu INT, INTEGER, REAL, TEXT, BLOB, dan ANY. Untuk lima tipe pertama, nilai yang dimasukkan wajib berada pada kelas penyimpanan yang sesuai setelah konversi afinitas dilakukan, sedangkan tipe ANY menerima nilai dari kelas mana pun.

Konversi afinitas tetap bekerja pada tabel strict, sehingga teks yang menyerupai angka masih diterima kolom INTEGER. Yang ditolak adalah nilai yang tidak dapat dikonversi sama sekali, sebagaimana diperagakan blok berikut.

CREATE TABLE buku_strict (
    id    INTEGER PRIMARY KEY,
    judul TEXT NOT NULL,
    tahun INTEGER
) STRICT;

INSERT INTO buku_strict (judul, tahun) VALUES ('Dasar Algoritma', '2019');

INSERT INTO buku_strict (judul, tahun) VALUES ('Peta Langit', 'baru-baru ini');

INSERT INTO buku_strict (judul, tahun) VALUES ('Kisah Kota Lama', 2021);

SELECT id, judul, tahun, typeof(tahun) FROM buku_strict;

Error near line 9: cannot store TEXT value in INTEGER column buku_strict.tahun
1|Dasar Algoritma|2019|integer
2|Kisah Kota Lama|2021|integer

Pesan error menyebut kolom dan nilai yang bermasalah secara eksplisit, sedangkan dua baris lain tetap tersimpan. Baris pertama terlihat pada keluaran dengan tahun bertipe integer, karena teks 2019 dikonversi otomatis sebelum pemeriksaan tipe dijalankan. Nama tipe di luar daftar juga ditolak, sebagaimana VARCHAR(100) pada blok berikut.

CREATE TABLE salah_strict (
    nama VARCHAR(100)
) STRICT;

Parse error near line 1: unknown datatype for salah_strict.nama: "VARCHAR(100)"

Pemeriksaan berlaku pada semua kelas, termasuk teks. Blok terakhir memasukkan blob ke kolom bertipe TEXT pada tabel strict dan langsung ditolak.

CREATE TABLE label_strict (
    nama TEXT
) STRICT;

INSERT INTO label_strict (nama) VALUES (x'414243');

SELECT quote(nama), typeof(nama) FROM label_strict;

Error near line 5: cannot store BLOB value in TEXT column label_strict.nama

Ringkasan#

• Afinitas kolom ditentukan dari nama tipenya pada saat CREATE TABLE dan menentukan konversi kelas penyimpanan yang dilakukan SQLite.
• Fungsi typeof() menampilkan kelas penyimpanan aktual sebuah nilai, sehingga perilaku afinitas dapat diverifikasi langsung.
• Tabel STRICT membatasi tipe pada INT, INTEGER, REAL, TEXT, BLOB, dan ANY, lalu menolak nilai yang kelasnya tidak cocok setelah konversi afinitas.

Referensi#

• Datatypes In SQLite — SQLite — Aturan penentuan afinitas dan konversi antarkelas penyimpanan.
• STRICT Tables — SQLite — Dokumentasi tabel strict beserta daftar tipe yang diperbolehkan.
• CREATE TABLE — SQLite — Sintaks lengkap CREATE TABLE termasuk klausa STRICT.