Guida SQLite interattiva: Colonne generate

Q: Che differenza c'è tra colonne generate VIRTUAL e STORED?

Una colonna VIRTUAL viene calcolata a ogni lettura e non occupa spazio su disco: è il valore predefinito. Una colonna STORED viene calcolata una volta al momento della scrittura e salvata nel file del database, così le letture costano meno e le scritture un po' di più. Entrambe si possono indicizzare, ma STORED è di solito la scelta giusta quando l'espressione è pesante o la colonna viene letta molto più spesso di quanto venga scritta.

Q: Si può indicizzare una colonna generata in SQLite?

Sì. CREATE INDEX funziona sulle colonne generate, sia VIRTUAL sia STORED. È il motivo principale per usarle: puoi indicizzare un valore derivato (come lower(email) o un campo JSON estratto con ->>) e lasciare che il query planner usi quell'indice senza riscrivere ogni query.

Q: Si può aggiungere una colonna generata con ALTER TABLE?

Sì, ma solo per le colonne VIRTUAL. ALTER TABLE ... ADD COLUMN ... GENERATED ALWAYS AS (...) VIRTUAL funziona senza problemi. Aggiungere una colonna generata STORED con ALTER TABLE non è supportato: dovresti ricostruire la tabella. Pianifica in anticipo se vuoi colonne salvate su tabelle esistenti.

Una colonna generata è una colonna calcolata

Una colonna generata è una colonna il cui valore arriva da un'espressione, non da un INSERT. Dichiari la formula una volta sola in CREATE TABLE e SQLite si occupa del resto. Non ci scrivi mai: provarci genera un errore.

L'esempio più breve possibile:

CREATE TABLE products (
    id      INTEGER PRIMARY KEY,
    price   REAL NOT NULL,
    tax     REAL NOT NULL,
    total   REAL GENERATED ALWAYS AS (price + tax)
);

INSERT INTO products (price, tax) VALUES (100.0, 8.5);
INSERT INTO products (price, tax) VALUES (49.99, 4.25);

SELECT id, price, tax, total FROM products;

total non è mai stata inserita, ma compare in ogni riga. SQLite la ricalcola da price + tax ogni volta che leggi la riga. Aggiorna una delle due colonne e total si adegua.

La frase chiave GENERATED ALWAYS AS è obbligatoria. ALWAYS è una formalità dello standard SQL: in SQLite non esiste un'altra opzione.

VIRTUAL e STORED

Ogni colonna generata è di uno dei due tipi. Il predefinito è VIRTUAL:

CREATE TABLE orders (
    id        INTEGER PRIMARY KEY,
    qty       INTEGER NOT NULL,
    unit      REAL NOT NULL,
    -- ricalcolata a ogni SELECT
    subtotal  REAL GENERATED ALWAYS AS (qty * unit) VIRTUAL,
    -- calcolata una volta e salvata su disco
    tax       REAL GENERATED ALWAYS AS (qty * unit * 0.08) STORED
);

INSERT INTO orders (qty, unit) VALUES (3, 19.99);
SELECT * FROM orders;

Il modello mentale:

VIRTUAL: zero byte su disco, un costo di CPU a ogni lettura. Economica da aggiungere, economica da cambiare in seguito.
STORED: occupa spazio su disco, non costa nulla in più in lettura. Conviene quando l'espressione è costosa o la colonna viene letta molto più spesso di quanto venga scritta.

Se non scrivi nessuna parola chiave, ottieni VIRTUAL. È quasi sempre il valore predefinito giusto.

Perché usarle? Valori derivati indicizzabili

La funzionalità decisiva è che puoi mettere un indice su una colonna generata. Così ottieni ricerche veloci su valori derivati senza riscrivere ogni query.

Supponiamo che tu voglia cercare le email senza distinguere maiuscole e minuscole:

CREATE TABLE users (
    id           INTEGER PRIMARY KEY,
    email        TEXT NOT NULL,
    email_lower  TEXT GENERATED ALWAYS AS (lower(email)) VIRTUAL
);

CREATE INDEX users_email_lower ON users(email_lower);

INSERT INTO users (email) VALUES ('Ada@Example.com'), ('boris@example.com');

SELECT id, email FROM users WHERE email_lower = 'ada@example.com';

L'indice copre la forma in minuscolo. Una query che filtra su email_lower usa direttamente l'indice. SQLite ha anche gli indici su espressione (CREATE INDEX ... ON users(lower(email))), ma una colonna generata rende il valore derivato visibile come una colonna vera, che puoi usare in una SELECT, citare nelle viste e riutilizzare dal codice dell'applicazione.

Estrarre valori da JSON

Le colonne generate danno il meglio sopra il JSON. Il supporto JSON di SQLite ti offre ->> per estrarre uno scalare; avvolgilo in una colonna generata e ottieni un campo tipizzato e indicizzabile sopra un blob flessibile.

CREATE TABLE events (
    id       INTEGER PRIMARY KEY,
    payload  TEXT NOT NULL,
    user_id  INTEGER GENERATED ALWAYS AS (payload ->> '$.user_id') VIRTUAL,
    kind     TEXT    GENERATED ALWAYS AS (payload ->> '$.kind') VIRTUAL
);

CREATE INDEX events_user_id ON events(user_id);

INSERT INTO events (payload) VALUES
    ('{"user_id": 1, "kind": "login"}'),
    ('{"user_id": 2, "kind": "purchase"}'),
    ('{"user_id": 1, "kind": "logout"}');

SELECT id, kind FROM events WHERE user_id = 1;

Per le tue query user_id e kind sembrano colonne normali, ma i dati stanno in payload. Cambia il JSON e le colonne si aggiornano. L'indice su user_id rende la ricerca veloce.

Regole e vincoli

Alcune cose che SQLite impone, da conoscere prima di sbatterci contro:

L'espressione deve essere deterministica. random(), datetime('now') e le altre funzioni non deterministiche non sono ammesse. Il valore deve essere riproducibile a partire dalla riga.
L'espressione può fare riferimento solo a colonne della stessa riga. Niente subquery, niente aggregati, niente altre tabelle.
Non puoi fare INSERT o UPDATE direttamente su una colonna generata. INSERT INTO products (total) VALUES (5) è un errore.
Le colonne STORED non si possono aggiungere con ALTER TABLE ... ADD COLUMN. Solo le VIRTUAL si possono aggiungere in un secondo momento.
Le colonne generate possono avere vincoli NOT NULL, CHECK, UNIQUE e persino FOREIGN KEY. Da questo punto di vista si comportano come qualsiasi altra colonna.

Una breve dimostrazione della regola sulla scrittura:

sqlite> INSERT INTO products (price, tax, total) VALUES (10, 1, 999);
Runtime error: cannot INSERT into generated column "total"

La soluzione è togliere la colonna generata dall'elenco dell'INSERT e lasciare che SQLite la calcoli.

Scegliere tra VIRTUAL e STORED

Di solito la decisione dipende dal rapporto tra letture e scritture e dal costo dell'espressione:

CREATE TABLE measurements (
    id          INTEGER PRIMARY KEY,
    celsius     REAL NOT NULL,
    -- Aritmetica economica, si può ricalcolare a ogni lettura.
    fahrenheit  REAL GENERATED ALWAYS AS (celsius * 9.0 / 5.0 + 32) VIRTUAL,
    -- Espressione più pesante, letta spesso, scritta di rado → salvala.
    bucket      TEXT GENERATED ALWAYS AS (
        CASE
            WHEN celsius < 0  THEN 'freezing'
            WHEN celsius < 15 THEN 'cool'
            WHEN celsius < 25 THEN 'mild'
            ELSE 'hot'
        END
    ) STORED
);

INSERT INTO measurements (celsius) VALUES (-3), (10), (22), (31);
SELECT celsius, fahrenheit, bucket FROM measurements;

Regole pratiche:

Parti da VIRTUAL. Non costa nulla in scrittura e va bene per quasi tutto.
Passa a STORED quando indicizzi la colonna su una tabella con molte scritture (l'indice ha comunque bisogno del valore salvato), o quando l'espressione è davvero costosa.
Non tormentarti. Il tipo fa parte dello schema, ma puoi eliminare e ricreare la colonna se cambi idea, almeno per le VIRTUAL.

Colonne generate e viste

C'è una sovrapposizione con le viste: entrambe espongono valori calcolati senza salvarli (beh, a volte). Di solito la divisione è questa:

Una colonna generata appartiene a una riga e a una tabella. Usala per i calcoli per riga: formattare un'email, estrarre un campo JSON, calcolare un totale.
Una vista è una query salvata. Usala quando il calcolo richiede join, aggregazioni o filtri su più righe.

Puoi combinarle. Una vista può fare SELECT da una tabella con colonne generate e aggiungere contesto con una join. Le colonne generate stanno al livello dello storage; le viste al livello delle query.

Prossimo passo: ATTACH DATABASE

Le colonne generate permettono a una tabella di calcolare i propri valori. La prossima pagina va nella direzione opposta: collegare più database SQLite contemporaneamente con ATTACH DATABASE, così una sola query può lavorare su più file.

Domande frequenti

Cos'è una colonna generata in SQLite?

Una colonna generata è una colonna il cui valore viene calcolato da un'espressione che usa altre colonne della stessa riga. La dichiari con GENERATED ALWAYS AS (expression) in CREATE TABLE. Non ci scrivi mai direttamente: SQLite la calcola per te ogni volta che la riga viene letta o salvata.

Che differenza c'è tra colonne generate VIRTUAL e STORED?

Una colonna VIRTUAL viene calcolata a ogni lettura e non occupa spazio su disco: è il valore predefinito. Una colonna STORED viene calcolata una volta al momento della scrittura e salvata nel file del database, così le letture costano meno e le scritture un po' di più. Entrambe si possono indicizzare, ma STORED è di solito la scelta giusta quando l'espressione è pesante o la colonna viene letta molto più spesso di quanto venga scritta.

Si può indicizzare una colonna generata in SQLite?

Sì. CREATE INDEX funziona sulle colonne generate, sia VIRTUAL sia STORED. È il motivo principale per usarle: puoi indicizzare un valore derivato (come lower(email) o un campo JSON estratto con ->>) e lasciare che il query planner usi quell'indice senza riscrivere ogni query.

Si può aggiungere una colonna generata con ALTER TABLE?

Sì, ma solo per le colonne VIRTUAL. ALTER TABLE ... ADD COLUMN ... GENERATED ALWAYS AS (...) VIRTUAL funziona senza problemi. Aggiungere una colonna generata STORED con ALTER TABLE non è supportato: dovresti ricostruire la tabella. Pianifica in anticipo se vuoi colonne salvate su tabelle esistenti.

Concetti correlati