Tabelle STRICT in SQLite: far rispettare i tipi delle colonne

Perché esistono le tabelle STRICT

Il comportamento predefinito di SQLite con i tipi è notoriamente rilassato. Dichiari una colonna INTEGER, inserisci la stringa "hello" e SQLite alza le spalle e salva la stringa. Questa flessibilità è stata una scelta di progetto deliberata negli anni '90, ma sorprende chi arriva da Postgres o MySQL, e nasconde i bug.

Le tabelle STRICT, introdotte in SQLite 3.37, risolvono il problema. Le attivi tabella per tabella, e da quel momento i tipi delle colonne significano quello che dicono.

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;

La parola chiave STRICT va dopo la parentesi di chiusura. Tutto il resto è un normale CREATE TABLE. La differenza si vede appena provi a mettere in una colonna un valore del tipo sbagliato.

Cosa impone davvero STRICT

In una tabella normale, l'affinità di tipo prova a convertire un valore nel tipo dichiarato e, se non ci riesce, lo salva così com'è. In una tabella STRICT, una mancata corrispondenza è un errore.

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' si converte senza problemi
INSERT INTO accounts (balance) VALUES ('oops');  -- errore

Prova lo stesso con una tabella non STRICT e il terzo insert va a buon fine: SQLite salva allegramente la stringa 'oops' in una colonna che avevi dichiarato INTEGER. Mesi dopo, una query di aggregazione restituisce risultati senza senso e passi un pomeriggio a cercare il motivo. STRICT fa emergere l'errore al momento dell'insert, dove puoi correggerlo.

L'errore che vedrai:

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

Chiaro, immediato, difficile da ignorare.

I cinque tipi ammessi

Le tabelle STRICT accettano solo cinque nomi di tipo:

• INTEGER: numeri interi.
• REAL: numeri in virgola mobile.
• TEXT: stringhe.
• BLOB: byte grezzi.
• ANY: qualsiasi tipo, senza conversione.

Tutto qui. Gli alias informali che SQLite di solito accetta, come VARCHAR(255), DOUBLE, BOOLEAN, DATETIME e INT, generano tutti un errore dentro una tabella STRICT:

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

L'errore:

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

La soluzione è usare uno dei cinque nomi canonici. VARCHAR(255) diventa TEXT, DATETIME diventa TEXT (SQLite salva comunque le date come stringhe ISO), BOOLEAN diventa INTEGER (con 0 e 1).

La via di fuga ANY

ANY è l'unico tipo che permette a una colonna STRICT di contenere valori eterogenei: utile per esempio per una colonna generica value in una tabella chiave/valore:

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;

Dentro le tabelle STRICT ANY è speciale: salva i valori senza la conversione di tipo che la stessa parola implicherebbe altrove. Una stringa '100' resta una stringa; un intero 100 resta un intero. Le chiamate a typeof() nella query qui sopra lo dimostrano.

In una tabella non STRICT, una colonna con affinità ANY convertirebbe in numeri le stringhe dall'aspetto numerico. STRICT conserva esattamente il tipo originale.

STRICT e PRIMARY KEY

Una differenza sottile: in una tabella normale, INTEGER PRIMARY KEY è speciale, perché diventa un alias del rowid e accetta solo interi. Le altre dichiarazioni di chiave primaria sono più permissive.

In una tabella STRICT, il tipo della colonna viene imposto indipendentemente dal fatto che sia la chiave primaria:

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

INSERT INTO products VALUES ('A-001', 'Aggeggio', 9.99);
INSERT INTO products VALUES (42, 'Arnese', 14.99);  -- 42 diventa '42'? No: errore.

SELECT * FROM products;

Il secondo insert fallisce. In una tabella non STRICT, 42 verrebbe salvato in silenzio nella colonna di chiave primaria TEXT. Qui, invece, te lo dice.

Mescolare tabelle STRICT e non STRICT

STRICT vale per tabella, non per database. Puoi avere una tabella users rigida e una tabella events rilassata nello stesso file. Le chiavi esterne funzionano tra le due come farebbero comunque.

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   -- nessun tipo dichiarato; valgono le regole di affinità
);

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;

La tabella events non ha STRICT né un tipo dichiarato su payload, quindi accetta qualsiasi cosa tu le passi. A volte è utile, come impostazione predefinita è rischioso. Riserva l'archiviazione senza tipo ai casi in cui ti serve davvero una colonna tuttofare.

Quando usare STRICT

Per i nuovi schemi la risposta è "quasi sempre". Il costo è minimo: una parola chiave per tabella e ricordarsi i cinque nomi di tipo canonici. Il vantaggio è che i bug che di solito si nasconderebbero nei tuoi dati emergono proprio all'insert che li ha causati.

Lascia perdere STRICT quando:

• Stai mantenendo un vecchio database SQLite il cui schema esistente si basa sulla tipizzazione rilassata.
• Il tuo obiettivo è una versione di SQLite precedente alla 3.37 (ottobre 2021): lì la parola chiave non esiste.
• Vuoi davvero che una colonna contenga tipi misti; anche in quel caso, preferisci STRICT più una colonna ANY a una tabella non STRICT, perché tutto il resto continua a essere controllato.

Una breve checklist per convertire una tabella normale in STRICT:

• Sostituisci VARCHAR, CHAR, NVARCHAR con TEXT.
• Sostituisci DOUBLE, FLOAT, NUMERIC con REAL.
• Sostituisci BOOLEAN, BIT, TINYINT con INTEGER.
• Sostituisci DATETIME, TIMESTAMP, DATE con TEXT (o INTEGER se salvi timestamp unix).
• Aggiungi STRICT dopo la parentesi di chiusura.

Prossimo passo: le chiavi primarie

Le tabelle STRICT rendono più rigoroso il modo in cui le colonne salvano i dati. La prossima cosa da rendere più rigorosa è quale colonna identifica ogni riga, e le chiavi primarie di SQLite hanno un paio di stranezze (soprattutto su INTEGER PRIMARY KEY e rowid) che vale la pena conoscere prima di progettare uno schema reale.