Type Affinity SQLite: Cara Tipe Kolom Sebenarnya Berperilaku

Affinity Adalah Preferensi, Bukan Aturan

Tipe data di SQLite bersifat dinamis. Sebuah nilai membawa storage class-nya sendiri (NULL, INTEGER, REAL, TEXT, BLOB), dan tipe yang dideklarasikan pada kolom tidak benar-benar membatasi apa yang bisa kamu masukkan ke dalamnya. Yang dilakukan tipe yang dideklarasikan adalah memberi kolom sebuah affinity, yaitu storage class pilihan yang menjadi tujuan konversi nilai yang masuk.

Lihat apa yang terjadi saat affinity tidak cukup untuk mencegah ketidakcocokan:

CREATE TABLE items (
    id INTEGER,
    name TEXT
);

INSERT INTO items VALUES (1, 'apple');
INSERT INTO items VALUES ('two', 'banana');

SELECT id, typeof(id), name FROM items;

Baris kedua menyimpan string 'two' di kolom INTEGER. SQLite mencoba mengonversi 'two' menjadi angka, tidak bisa (itu bukan angka), dan tetap menyimpannya sebagai TEXT. typeof() memperlihatkan storage class sebenarnya dari setiap nilai, dan hasilnya tidak selalu sesuai dengan deklarasi kolom.

Ini mengejutkan orang yang datang dari Postgres atau MySQL. Tapi memang begitulah rancangannya.

Lima Affinity

Setiap kolom di tabel non-STRICT mendapat tepat satu dari berikut ini:

• TEXT: mengutamakan string.
• NUMERIC: mengutamakan angka, tapi menerima teks jika tidak bisa dikonversi.
• INTEGER: seperti NUMERIC, tapi menyimpan nilai tanpa bagian pecahan sebagai integer.
• REAL: mengutamakan bilangan floating-point.
• BLOB: tanpa preferensi, menyimpan apa pun yang kamu berikan.

Affinity BLOB juga disebut "tanpa affinity": itulah yang kamu dapat saat sama sekali tidak mendeklarasikan tipe.

CREATE TABLE samples (
    a,                 -- BLOB affinity (no type declared)
    b TEXT,            -- TEXT
    c INTEGER,         -- INTEGER
    d REAL,            -- REAL
    e NUMERIC          -- NUMERIC
);

INSERT INTO samples VALUES ('42', '42', '42', '42', '42');
SELECT typeof(a), typeof(b), typeof(c), typeof(d), typeof(e) FROM samples;

Input yang sama, yaitu string '42', menghasilkan lima tipe tersimpan yang berbeda. Setiap kolom mengonversi (atau tidak) sesuai affinity-nya.

Cara SQLite Memilih Affinity dari Deklarasimu

Inilah bagian yang sering menjebak: SQLite tidak punya daftar tetap tipe yang "valid". Kamu bisa menulis hampir apa saja setelah nama kolom, dan SQLite menentukan affinity-nya dengan memindai teks itu untuk mencari potongan teks, dengan urutan berikut:

1. Mengandung INT → INTEGER
2. Mengandung CHAR, CLOB, atau TEXT → TEXT
3. Mengandung BLOB, atau tanpa tipe sama sekali → BLOB
4. Mengandung REAL, FLOA, atau DOUB → REAL
5. Selain itu → NUMERIC

Itulah seluruh algoritmanya. Algoritma ini menjelaskan banyak keanehan:

CREATE TABLE quirks (
    a VARCHAR(255),       -- TEXT  (contains CHAR)
    b BIGINT,             -- INTEGER (contains INT)
    c DOUBLE PRECISION,   -- REAL (contains DOUB)
    d DECIMAL(10,2),      -- NUMERIC (matches none of the above)
    e BOOLEAN,            -- NUMERIC (matches none of the above)
    f DATETIME,           -- NUMERIC (matches none of the above)
    g FLOATING_POINTS,    -- INTEGER (contains INT - surprise!)
    h GIBBERISH           -- NUMERIC (matches none of the above)
);

SELECT name, type FROM pragma_table_info('quirks');

FLOATING_POINTS menjadi INTEGER karena potongan teks INT muncul di POINTS. Aturan pertama yang cocok yang menang, dari atas ke bawah. Itulah sebabnya menyalin tipe dari database lain begitu saja bisa memberimu hasil yang berbeda dari dugaanmu.

Affinity dalam Praktik: Konversi Saat Insert

Affinity paling berpengaruh saat SQLite memutuskan apakah nilaimu dikonversi atau disimpan apa adanya. Aturannya:

• Affinity TEXT: angka dan BLOB dikonversi menjadi teks.
• Affinity NUMERIC, INTEGER, REAL: teks yang terlihat seperti angka dikonversi; teks yang tidak terlihat seperti angka tetap teks.
• Affinity BLOB: tidak ada yang dikonversi.

CREATE TABLE conv (
    n NUMERIC,
    t TEXT,
    b BLOB
);

INSERT INTO conv VALUES ('123', 456, 789);
INSERT INTO conv VALUES ('12.5', 7.5, 'hello');
INSERT INTO conv VALUES ('hello', 'world', 'world');

SELECT n, typeof(n), t, typeof(t), b, typeof(b) FROM conv;

Baris demi baris:

• '123' di kolom NUMERIC menjadi integer 123. Konversi teks ke angka berhasil tanpa kehilangan informasi.
• '12.5' menjadi real 12.5.
• 'hello' di NUMERIC tetap sebagai teks, karena tidak ada angka yang bisa dihasilkan.
• Kolom TEXT mengonversi angka menjadi bentuk string-nya.
• Kolom BLOB menyimpan semuanya persis seperti yang diberikan, termasuk tipenya.

Keunikan INTEGER vs REAL

Affinity INTEGER berperilaku hampir sama dengan NUMERIC, dengan satu perbedaan: nilai seperti 3.0 yang tidak punya bagian pecahan sungguhan disimpan sebagai integer 3 untuk menghemat ruang.

CREATE TABLE nums (
    i INTEGER,
    n NUMERIC
);

INSERT INTO nums VALUES (3.0, 3.0);
INSERT INTO nums VALUES (3.5, 3.5);

SELECT i, typeof(i), n, typeof(n) FROM nums;

3.0 tersimpan sebagai INTEGER di kedua kolom, karena optimasi ini juga terjadi untuk NUMERIC. 3.5 mempertahankan bagian pecahannya dan tetap REAL. Pelajarannya: jangan mengandalkan typeof() untuk mengetahui apakah sebuah kolom dideklarasikan INTEGER atau REAL. Fungsi itu memberi tahu apa yang benar-benar tersimpan, dan itu bisa berbeda dari baris ke baris.

Saat Affinity Menjadi Masalah

Fleksibilitas ini nyaman, sampai suatu saat tidak lagi. Ada dua bentuk kegagalan yang muncul di kode sungguhan:

1. Data buruk menyelinap masuk. Jika aplikasimu punya bug yang mengirim 'N/A' ke kolom INTEGER, SQLite tetap menyimpannya. Query berikutnya yang melakukan aritmetika pada kolom itu mengembalikan hasil aneh atau NULL. Tanpa error, tanpa peringatan, hanya kerusakan data yang diam-diam.

2. Perbandingan jadi aneh. Pengurutan dan pemeriksaan kesamaan memperlakukan nilai dengan storage class berbeda secara berbeda pula:

CREATE TABLE mixed (val INTEGER);
INSERT INTO mixed VALUES (10), (2), ('100'), ('20'), (3);

SELECT val, typeof(val) FROM mixed ORDER BY val;

Integer diurutkan secara numerik, lalu nilai teks diurutkan secara leksikografis, dan semuanya diletakkan setelah semua angka. Jadi kamu mendapat 2, 3, 10 (integer dalam urutan numerik), lalu '20', '100' (string dalam urutan abjad). Bukan itu yang diinginkan kebanyakan orang.

Jika kamu mengendalikan insert dan memvalidasinya dengan cermat, tabel biasa tidak masalah. Jika tidak, atau kamu memang ingin database yang menegakkan tipe untukmu, ada pilihan yang lebih baik.

Berikutnya: Tabel STRICT

SQLite 3.37 menambahkan tabel STRICT, yang mematikan affinity dan menolak nilai yang tidak cocok dengan tipe yang dideklarasikan. Dengan begitu kamu mendapat perilaku tipe data dinamis secara default saat kamu menginginkannya, dan penegakan ala Postgres saat kamu tidak menginginkannya. Itu halaman berikutnya.