Type affinity in SQLite: come si comportano davvero i tipi

L'affinità è una preferenza, non una regola

SQLite ha una tipizzazione dinamica. Un valore porta con sé la propria classe di memorizzazione (NULL, INTEGER, REAL, TEXT, BLOB) e il tipo dichiarato di una colonna non limita in modo rigido cosa puoi metterci dentro. Quello che il tipo dichiarato fa è dare alla colonna un'affinità: una classe di memorizzazione preferita in cui SQLite prova a convertire i valori in arrivo.

Guarda cosa succede quando l'affinità non basta a fermare un valore non compatibile:

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;

La seconda riga memorizza la stringa 'two' in una colonna INTEGER. SQLite ha provato a convertire 'two' in un numero, non ci è riuscito (non è numerico) e l'ha memorizzata comunque come TEXT. typeof() rivela la classe di memorizzazione effettiva di ogni valore, e non sempre è quella che la dichiarazione della colonna lascia intendere.

Chi arriva da Postgres o MySQL resta sorpreso. È una scelta di progetto.

Le cinque affinità

Ogni colonna di una tabella non STRICT riceve esattamente una di queste:

• TEXT: preferisce le stringhe.
• NUMERIC: preferisce i numeri, ma accetta il testo se non riesce a convertirlo.
• INTEGER: come NUMERIC, ma memorizza come interi i valori senza parte frazionaria.
• REAL: preferisce i numeri in virgola mobile.
• BLOB: nessuna preferenza, memorizza qualsiasi cosa gli passi.

L'affinità BLOB si chiama anche "nessuna affinità": è quella che ottieni quando non dichiari affatto un tipo.

CREATE TABLE samples (
    a,                 -- Affinità BLOB (nessun tipo dichiarato)
    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;

Stesso input, la stringa '42', e cinque tipi memorizzati diversi. Ogni colonna ha convertito (o no) in base alla propria affinità.

Come SQLite sceglie un'affinità dalla tua dichiarazione

Ecco la parte che fa inciampare: SQLite non ha un elenco fisso di tipi "validi". Puoi scrivere quasi qualsiasi cosa dopo il nome di una colonna, e SQLite ricava l'affinità cercando delle sottostringhe nel testo, in quest'ordine:

1. Contiene INT → INTEGER
2. Contiene CHAR, CLOB o TEXT → TEXT
3. Contiene BLOB, oppure nessun tipo → BLOB
4. Contiene REAL, FLOA o DOUB → REAL
5. Qualsiasi altra cosa → NUMERIC

Questo è tutto l'algoritmo. E spiega parecchie stranezze:

CREATE TABLE quirks (
    a VARCHAR(255),       -- TEXT (contiene CHAR)
    b BIGINT,             -- INTEGER (contiene INT)
    c DOUBLE PRECISION,   -- REAL (contiene DOUB)
    d DECIMAL(10,2),      -- NUMERIC (non corrisponde a nessuna delle precedenti)
    e BOOLEAN,            -- NUMERIC (non corrisponde a nessuna delle precedenti)
    f DATETIME,           -- NUMERIC (non corrisponde a nessuna delle precedenti)
    g FLOATING_POINTS,    -- INTEGER (contiene INT: sorpresa!)
    h GIBBERISH           -- NUMERIC (non corrisponde a nessuna delle precedenti)
);

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

FLOATING_POINTS diventa INTEGER perché la sottostringa INT compare in POINTS. Vince la prima regola che corrisponde, dall'alto verso il basso. Ecco perché copiare alla cieca i tipi da un altro database può darti qualcosa di diverso da quello che ti aspettavi.

L'affinità in azione: conversioni all'inserimento

L'affinità conta soprattutto quando SQLite decide se convertire il tuo valore o memorizzarlo così com'è. Le regole:

• Affinità TEXT: i numeri e i BLOB vengono convertiti in testo.
• Affinità NUMERIC, INTEGER, REAL: il testo che sembra un numero viene convertito; quello che non lo sembra resta testo.
• Affinità BLOB: non viene convertito nulla.

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;

Riga per riga:

• '123' in una colonna NUMERIC diventa l'intero 123. La conversione da testo a numero è riuscita senza perdite.
• '12.5' diventa il reale 12.5.
• 'hello' in NUMERIC resta testo: non c'è un numero in cui convertirlo.
• La colonna TEXT converte i numeri nella loro forma di stringa.
• La colonna BLOB memorizza tutto esattamente come gliel'hai passato, tipo compreso.

La sfumatura tra INTEGER e REAL

L'affinità INTEGER si comporta quasi come NUMERIC, con una differenza: un valore come 3.0, che non ha una vera parte frazionaria, viene memorizzato come intero 3 per risparmiare spazio.

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 finisce come INTEGER in entrambe le colonne: l'ottimizzazione avviene anche per NUMERIC. 3.5 mantiene la parte frazionaria e resta REAL. La lezione: non affidarti a typeof() per sapere se una colonna è stata dichiarata INTEGER o REAL. Ti dice cosa è memorizzato davvero, e questo può cambiare da riga a riga.

Quando l'affinità ti morde

La flessibilità è comoda finché non lo è più. Nel codice reale si vedono due tipi di problema:

1. Entrano dati sbagliati. Se la tua applicazione ha un bug che manda 'N/A' a una colonna INTEGER, SQLite lo memorizza. Le query successive che fanno calcoli sulla colonna restituiscono risultati strani o NULL. Nessun errore, nessun avviso: solo dati corrotti in silenzio.

2. I confronti diventano strani. L'ordinamento e i controlli di uguaglianza trattano in modo diverso i valori con classi di memorizzazione diverse:

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

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

Gli interi vengono ordinati numericamente, poi i valori di testo in ordine lessicografico, e finiscono dopo tutti i numeri. Così ottieni 2, 3, 10 (gli interi in ordine numerico), poi '20', '100' (le stringhe in ordine alfabetico). Non è quello che vuole la maggior parte delle persone.

Se controlli tu gli inserimenti e li validi con cura, le tabelle normali vanno bene. Se non è così, o se vuoi semplicemente che sia il database a imporre i tipi, c'è un'opzione migliore.

Prossimo passo: tabelle STRICT

SQLite 3.37 ha introdotto le tabelle STRICT, che disattivano l'affinità e rifiutano i valori che non corrispondono al tipo dichiarato. Ti danno la tipizzazione dinamica predefinita quando la vuoi e un controllo in stile Postgres quando non la vuoi. È la prossima pagina.