Tabele STRICT w SQLite: prawdziwe egzekwowanie typów kolumn

Po co istnieją tabele STRICT

Domyślne podejście SQLite do typów jest słynne ze swojej swobody. Zadeklaruj kolumnę INTEGER, wstaw tekst "hello", a SQLite wzruszy ramionami i zapisze tekst. Ta elastyczność była świadomą decyzją projektową z lat 90., ale zaskakuje osoby przychodzące z Postgres czy MySQL i ukrywa błędy.

Tabele STRICT, dodane w SQLite 3.37, to naprawiają. Włączasz je dla konkretnej tabeli i od tej chwili typy kolumn znaczą to, co mówią.

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;

Słowo kluczowe STRICT stoi po nawiasie zamykającym. Cała reszta wygląda jak zwykłe CREATE TABLE. Różnica wychodzi na jaw w chwili, gdy spróbujesz wstawić do kolumny wartość złego rodzaju.

Co właściwie egzekwuje STRICT

W zwykłej tabeli powinowactwo typów próbuje skonwertować wartość do zadeklarowanego typu, a gdy się nie da, zapisuje ją bez zmian. W tabeli STRICT niezgodność to błąd.

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' da się czysto skonwertować
INSERT INTO accounts (balance) VALUES ('oops');  -- błąd

Zrób to samo na tabeli bez STRICT, a trzecie wstawienie się uda: SQLite radośnie zapisze tekst 'oops' w kolumnie, którą oznaczono jako INTEGER. Po miesiącach zapytanie agregujące zwróci bzdury, a ty spędzisz popołudnie na szukaniu przyczyny. STRICT sprawia, że błąd pojawia się już przy wstawianiu, gdzie możesz go naprawić.

Błąd, który zobaczysz:

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

Jasny, natychmiastowy i trudny do zignorowania.

Pięć dozwolonych typów

Tabele STRICT akceptują tylko pięć nazw typów:

• INTEGER: liczby całkowite.
• REAL: liczby zmiennoprzecinkowe.
• TEXT: teksty.
• BLOB: surowe bajty.
• ANY: dowolny typ, bez konwersji.

To wszystko. Swobodne aliasy, które SQLite normalnie akceptuje (VARCHAR(255), DOUBLE, BOOLEAN, DATETIME, INT), w tabeli STRICT zgłaszają błąd:

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

Błąd:

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

Rozwiązanie to użycie jednej z pięciu kanonicznych nazw. VARCHAR(255) zmienia się w TEXT, DATETIME w TEXT (SQLite i tak przechowuje daty jako teksty ISO), a BOOLEAN w INTEGER (z 0 i 1).

Furtka ANY

ANY to jedyny typ, który pozwala kolumnie STRICT przechowywać wartości różnych typów. Przydaje się na przykład w ogólnej kolumnie value w tabeli klucz-wartość:

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 jest w tabelach STRICT wyjątkowy: przechowuje wartości bez wymuszania typu, które to samo słowo oznaczałoby gdzie indziej. Tekst '100' zostaje tekstem, a liczba całkowita 100 zostaje liczbą całkowitą. Dowodzą tego wywołania typeof() w zapytaniu powyżej.

W tabeli bez STRICT kolumna z powinowactwem ANY zamieniałaby teksty wyglądające na liczby w liczby. STRICT dokładnie zachowuje pierwotny typ.

STRICT i PRIMARY KEY

Jedna subtelna różnica: w zwykłej tabeli INTEGER PRIMARY KEY jest wyjątkowy, bo staje się aliasem rowid i przyjmuje tylko liczby całkowite. Inne deklaracje klucza głównego są luźniejsze.

W tabeli STRICT typ kolumny jest egzekwowany niezależnie od tego, czy jest ona kluczem głównym:

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

INSERT INTO products VALUES ('A-001', 'Widżet', 9.99);
INSERT INTO products VALUES (42, 'Gadżet', 14.99);  -- 42 zamieni się w '42'? Nie, błąd.

SELECT * FROM products;

Drugie wstawienie się nie udaje. W tabeli bez STRICT 42 zostałoby po cichu zapisane w kolumnie klucza głównego typu TEXT. Tutaj dostajesz komunikat.

Łączenie tabel STRICT i zwykłych

STRICT działa na poziomie tabeli, a nie bazy. W jednym pliku możesz mieć ścisłą tabelę users i swobodną tabelę events. Klucze obce działają między nimi tak samo jak zawsze.

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   -- bez zadeklarowanego typu; obowiązują reguły powinowactwa
);

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;

Tabela events nie ma STRICT ani zadeklarowanego typu dla payload, więc przyjmuje wszystko, co do niej wrzucisz. Czasem przydatne, ale ryzykowne jako ustawienie domyślne. Zostaw nietypowane przechowywanie na przypadki, w których naprawdę potrzebujesz kolumny na wszystko.

Kiedy używać STRICT

Przy nowych schematach odpowiedź brzmi: "prawie zawsze". Koszt jest mały: jedno słowo kluczowe na tabelę i zapamiętanie pięciu kanonicznych nazw typów. Korzyść jest taka, że błędy, które normalnie czaiłyby się w danych, ujawniają się przy wstawieniu, które je spowodowało.

Pomiń STRICT, gdy:

• Utrzymujesz starą bazę SQLite, której istniejący schemat opiera się na swobodnym typowaniu.
• Celujesz w SQLite starsze niż 3.37 (październik 2021): tam to słowo kluczowe nie istnieje.
• Naprawdę chcesz, żeby kolumna przechowywała mieszane typy. Nawet wtedy lepiej wybrać STRICT z kolumną ANY niż tabelę bez STRICT, bo cała reszta pozostaje egzekwowana.

Krótka lista kontrolna przy zamianie zwykłej tabeli na STRICT:

• Zamień VARCHAR, CHAR, NVARCHAR na TEXT.
• Zamień DOUBLE, FLOAT, NUMERIC na REAL.
• Zamień BOOLEAN, BIT, TINYINT na INTEGER.
• Zamień DATETIME, TIMESTAMP, DATE na TEXT (albo INTEGER, jeśli przechowujesz uniksowe znaczniki czasu).
• Dopisz STRICT po nawiasie zamykającym.

Dalej: klucze główne

Tabele STRICT zaostrzają sposób, w jaki kolumny przechowują dane. Następna rzecz, którą warto zaostrzyć, to która kolumna identyfikuje każdy wiersz. Klucze główne w SQLite mają kilka dziwactw (zwłaszcza wokół INTEGER PRIMARY KEY i rowid), które warto znać, zanim zaprojektujesz prawdziwy schemat.