Tabel STRICT SQLite: Menegakkan Tipe Kolom dengan Benar

Kenapa Tabel STRICT Ada

Perilaku default SQLite soal tipe data terkenal longgar. Deklarasikan kolom INTEGER, masukkan string "hello", dan SQLite santai saja menyimpan string itu. Fleksibilitas ini adalah pilihan desain yang disengaja di tahun 90-an, tapi sering mengejutkan orang yang datang dari Postgres atau MySQL, dan bisa menyembunyikan bug.

Tabel STRICT, yang ditambahkan di SQLite 3.37, memperbaikinya. Kamu mengaktifkannya per tabel, dan sejak itu tipe kolom benar-benar berarti seperti yang tertulis.

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    age   INTEGER
) STRICT;

INSERT INTO users (name, age) VALUES ('Ada', 30);
SELECT * FROM users;

Kata kunci STRICT diletakkan setelah tanda kurung penutup. Selebihnya terlihat seperti CREATE TABLE biasa. Bedanya langsung terlihat begitu kamu mencoba memasukkan jenis nilai yang salah ke sebuah kolom.

Apa yang Sebenarnya Ditegakkan STRICT

Di tabel biasa, type affinity mencoba mengonversi nilai ke tipe yang dideklarasikan, lalu menyimpannya apa adanya jika tidak bisa. Di tabel STRICT, ketidakcocokan tipe adalah error.

CREATE TABLE accounts (
    id      INTEGER PRIMARY KEY,
    balance INTEGER
) STRICT;

INSERT INTO accounts (balance) VALUES (100);     -- ok
INSERT INTO accounts (balance) VALUES ('100');   -- ok, '100' converts cleanly
INSERT INTO accounts (balance) VALUES ('oops');  -- error

Coba hal yang sama pada tabel non-STRICT, dan insert ketiga berhasil: SQLite dengan senang hati menyimpan string 'oops' di kolom yang kamu nyatakan bertipe INTEGER. Berbulan-bulan kemudian, sebuah query agregat mengembalikan hasil ngawur dan kamu menghabiskan satu sore untuk melacaknya. STRICT membuat kegagalan itu terjadi saat insert, di mana kamu bisa memperbaikinya.

Error yang akan kamu lihat:

Runtime error: cannot store TEXT value in INTEGER column accounts.balance

Jelas, langsung, dan sulit diabaikan.

Lima Tipe yang Diizinkan

Tabel STRICT hanya menerima lima nama tipe:

• INTEGER: bilangan bulat.
• REAL: bilangan floating-point.
• TEXT: string.
• BLOB: byte mentah.
• ANY: tipe apa pun, tanpa konversi.

Hanya itu. Alias santai yang biasanya diterima SQLite, seperti VARCHAR(255), DOUBLE, BOOLEAN, DATETIME, INT, semuanya memunculkan error di dalam tabel STRICT:

CREATE TABLE bad (
    id   INTEGER PRIMARY KEY,
    name VARCHAR(255)
) STRICT;

Error-nya:

Parse error: unknown datatype for bad.name: "VARCHAR(255)"

Solusinya adalah memakai salah satu dari lima nama baku tersebut. VARCHAR(255) menjadi TEXT, DATETIME menjadi TEXT (SQLite memang menyimpan tanggal sebagai string ISO), BOOLEAN menjadi INTEGER (dengan 0 dan 1).

Jalan Keluar ANY

ANY adalah satu-satunya tipe yang membuat kolom STRICT bisa menampung nilai dengan tipe campuran. Ini berguna untuk hal seperti kolom value generik di tabel key/value:

CREATE TABLE settings (
    key   TEXT PRIMARY KEY,
    value ANY
) STRICT;

INSERT INTO settings VALUES ('max_users', 100);
INSERT INTO settings VALUES ('site_name', 'Coddy');
INSERT INTO settings VALUES ('ratio', 1.5);

SELECT key, value, typeof(value) FROM settings;

ANY istimewa di dalam tabel STRICT: ia menyimpan nilai tanpa konversi tipe yang biasanya tersirat dari kata yang sama di tempat lain. String '100' tetap string, integer 100 tetap integer. Pemanggilan typeof() pada query di atas membuktikannya.

Di tabel non-STRICT, kolom dengan affinity ANY akan mengonversi string yang terlihat seperti angka menjadi angka. STRICT mempertahankan tipe aslinya dengan tepat.

STRICT dan PRIMARY KEY

Ada satu perbedaan halus: di tabel biasa, INTEGER PRIMARY KEY itu istimewa, karena ia menjadi alias untuk rowid dan hanya menerima integer. Deklarasi primary key lainnya lebih longgar.

Di tabel STRICT, tipe kolom ditegakkan, entah kolom itu primary key atau bukan:

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL
) STRICT;

INSERT INTO products VALUES ('A-001', 'Widget', 9.99);
INSERT INTO products VALUES (42, 'Gizmo', 14.99);  -- 42 converts to '42'? No - error.

SELECT * FROM products;

Insert kedua gagal. Di tabel non-STRICT, 42 akan diam-diam disimpan di kolom primary key bertipe TEXT. Di sini, kamu diberi tahu.

Mencampur Tabel STRICT dan Non-STRICT

STRICT berlaku per tabel, bukan per database. Kamu bisa punya tabel users yang ketat dan tabel events yang longgar di file yang sama. Foreign key tetap berfungsi di antara keduanya seperti biasa.

CREATE TABLE users (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
) STRICT;

CREATE TABLE events (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    payload   -- no type declared; affinity rules apply
);

INSERT INTO users (name) VALUES ('Boris');
INSERT INTO events (user_id, payload) VALUES (1, 'login');
INSERT INTO events (user_id, payload) VALUES (1, 42);

SELECT id, payload, typeof(payload) FROM events;

Tabel events tidak memakai STRICT dan tidak punya tipe yang dideklarasikan pada payload, jadi ia menerima apa pun yang kamu masukkan. Kadang berguna, tapi berisiko jika dijadikan default. Simpan penyimpanan tanpa tipe untuk kasus ketika kamu benar-benar butuh kolom serba guna.

Kapan Memakai STRICT

Untuk skema baru, jawabannya "hampir selalu." Biayanya kecil: satu kata kunci per tabel, dan mengingat lima nama tipe baku. Manfaatnya, bug yang biasanya bersembunyi di datamu akan muncul tepat pada insert yang menyebabkannya.

Lewati STRICT saat:

• Kamu memelihara database SQLite lama yang skemanya bergantung pada tipe data yang longgar.
• Kamu menargetkan SQLite yang lebih lama dari 3.37 (Oktober 2021), karena kata kunci ini belum ada di sana.
• Kamu memang ingin sebuah kolom menampung tipe campuran. Bahkan dalam kasus ini, lebih baik pakai STRICT ditambah kolom ANY daripada tabel non-STRICT, karena semua kolom lainnya tetap ditegakkan.

Daftar periksa singkat saat mengubah tabel biasa menjadi STRICT:

• Ganti VARCHAR, CHAR, NVARCHAR dengan TEXT.
• Ganti DOUBLE, FLOAT, NUMERIC dengan REAL.
• Ganti BOOLEAN, BIT, TINYINT dengan INTEGER.
• Ganti DATETIME, TIMESTAMP, DATE dengan TEXT (atau INTEGER jika kamu menyimpan unix timestamp).
• Tambahkan STRICT setelah tanda kurung penutup.

Berikutnya: Primary Key

Tabel STRICT memperketat cara kolom menyimpan datanya. Hal berikutnya yang layak diperketat adalah kolom mana yang mengidentifikasi setiap baris, dan primary key di SQLite punya beberapa keanehan (terutama seputar INTEGER PRIMARY KEY dan rowid) yang perlu kamu ketahui sebelum merancang skema sungguhan.