◈ STORAGE CLASS

SQLite usa un sistema di tipi dinamico. Il tipo appartiene al valore, non alla colonna. Esistono 5 storage class native.

LE 5 CLASSI
NULL     -- valore assente / sconosciuto
INTEGER  -- intero con segno, 1-8 byte
         -- a seconda del valore
REAL     -- float IEEE 754 a 8 byte
TEXT     -- stringa UTF-8 / UTF-16
BLOB     -- dati binari raw

-- SQLite NON ha tipi BOOLEAN o DATE
-- BOOLEAN → INTEGER (0/1)
-- DATE    → TEXT o INTEGER o REAL
ORDINE DI CONFRONTO
-- NULL < INTEGER < REAL < TEXT < BLOB
SELECT NULL < 0;          → NULL
SELECT NULL IS NULL;      → 1
SELECT 1 < 'a';            → 1 (integer < text)
SELECT 'a' < x'61';        → 1 (text < blob)
TYPEOF() — VERIFICARE IL TIPO
typeof(42)          → 'integer'
typeof(3.14)        → 'real'
typeof('ciao')      → 'text'
typeof(x'DEADBEEF') → 'blob'
typeof(NULL)        → 'null'

-- attenzione:
typeof(1.0)         → 'real' (non integer!)
typeof('3.14')      → 'text' (non real!)
typeof('')           → 'text' (stringa vuota)

-- filtro per tipo in WHERE
SELECT * FROM t
WHERE typeof(col) = 'integer';
BLOB LITERAL
-- notazione esadecimale: x'...'
SELECT x'AABB';            → blob 2 byte
SELECT length(x'AABB');   → 2
SELECT hex(x'41');         → '41'
SELECT hex('A');            → '41'
RANGE DEI VALORI
-- INTEGER: da -9223372036854775808 a 9223372036854775807 (64 bit con segno)
-- REAL:    IEEE 754, min positivo ~5e-324, max ~1.8e+308
-- TEXT:    dimensione limitata solo da SQLITE_MAX_LENGTH (default 1 GB)
-- BLOB:    idem, max 1 GB
SQLite NON supporta Inf e NaN. La divisione per zero restituisce NULL, non un errore.
◈ TYPE AFFINITY

La type affinity determina come SQLite tenta di convertire i valori inseriti in una colonna. Il tipo dichiarato nella CREATE TABLE viene mappato a una delle 5 affinita secondo queste regole (in ordine di priorita):

Affinita Regola sul nome del tipo dichiarato Esempi di dichiarazione
INTEGER Contiene "INT" (qualsiasi posizione) INTEGER, INT, TINYINT, BIGINT, INT8
TEXT Contiene "CHAR", "CLOB" o "TEXT" TEXT, VARCHAR(255), NCHAR, CLOB
BLOB / NONE Tipo vuoto o non corrisponde a nessuna regola (nessun tipo), BLOB
REAL Contiene "REAL", "FLOA" o "DOUB" REAL, FLOAT, DOUBLE, DOUBLE PRECISION
NUMERIC Tutti gli altri casi NUMERIC, DECIMAL(10,5), BOOLEAN, DATE
Le regole si applicano in ordine: se il tipo contiene "INT", vince INTEGER anche se contiene anche "CHAR" (es. BIGINT CHARACTER → INTEGER).
COMPORTAMENTO PER INSERIMENTO
-- Affinita INTEGER: converte testo → intero se possibile
col_int  = '42'    → memorizzato come INTEGER 42
col_int  = 3.0    → memorizzato come INTEGER 3
col_int  = 3.14   → memorizzato come REAL 3.14 (non intero esatto)

-- Affinita TEXT: converte tutto in testo
col_text = 3.14   → memorizzato come TEXT '3.14'

-- Affinita NUMERIC: testo → numero se possibile,
-- poi REAL → INTEGER se senza perdita
col_num  = '3.0'  → memorizzato come INTEGER 3
col_num  = '3.14' → memorizzato come REAL 3.14
col_num  = 'abc'  → memorizzato come TEXT 'abc'

-- Affinita REAL: converte INTEGER in REAL
col_real = 42     → memorizzato come REAL 42.0

-- Affinita NONE (BLOB): nessuna conversione
col_blob = 'ciao' → memorizzato come TEXT 'ciao' (invariato)
CONVERSIONI IMPLICITE NEI CONFRONTI
-- Senza affinita di colonna, SQLite non converte
SELECT 1   = '1';   → 0 (tipi diversi!)
SELECT 1   = 1.0;  → 1 (integer ≈ real)
SELECT '1' = '1';  → 1

-- Con affinita di colonna INTEGER:
-- il valore '42' viene convertito in 42
-- poi il confronto avviene tra interi
WHERE col_integer = '42';  → funziona!
◈ CAST & CONVERSIONI
CAST DI BASE
CAST('42'    AS INTEGER)  → 42
CAST('3.14'  AS REAL)     → 3.14
CAST(42      AS TEXT)     → '42'
CAST(3.99    AS INTEGER)  → 3 (troncato!)
CAST(NULL    AS INTEGER)  → NULL
CAST('abc'   AS INTEGER)  → 0
CAST('3abc'  AS INTEGER)  → 3 (legge fino a non-cifra)
CAST(''      AS INTEGER)  → 0
CAST VERSO BLOB
CAST('hello' AS BLOB)
→ blob con byte UTF-8 di 'hello'

CAST(x'68656C6C6F' AS TEXT)
→ 'hello'

CAST(65 AS BLOB)
→ 8 byte (rappresentazione interna)

-- per convertire numero → hex ASCII:
SELECT hex(65);   → '41'
◈ NULL

SQLite implementa la logica a tre valori (TRUE, FALSE, NULL). NULL non e uguale a niente, nemmeno a se stesso.

LOGICA A TRE VALORI
NULL = NULL       → NULL (non TRUE!)
NULL != NULL      → NULL
NULL > 0          → NULL

-- operatori corretti per NULL
col IS NULL       → TRUE se NULL
col IS NOT NULL   → TRUE se non NULL

-- AND con NULL
TRUE  AND NULL    → NULL
FALSE AND NULL    → FALSE (short-circuit)

-- OR con NULL
TRUE  OR  NULL    → TRUE (short-circuit)
FALSE OR  NULL    → NULL
NULL NELLE FUNZIONI
-- la maggior parte delle funzioni
-- restituisce NULL se l'input e NULL
upper(NULL)        → NULL
length(NULL)       → NULL
abs(NULL)          → NULL
42 + NULL          → NULL

-- COUNT(*) conta tutte le righe
-- COUNT(col) ignora le righe con NULL
COUNT(*)           → conta tutto
COUNT(col)         → esclude NULL
SUM(col)           → ignora NULL
AVG(col)           → ignora NULL
GESTIRE NULL
COALESCE(a, b, c, 'default')   → primo valore non-NULL
IFNULL(col, 0)                 → valore alternativo se NULL
NULLIF(a, b)                   → NULL se a = b, altrimenti a
IIF(col IS NULL, 'vuoto', col) → ternario (≥ 3.32)

-- NULL nell'ordinamento
ORDER BY col ASC NULLS LAST    -- ≥ 3.30
ORDER BY col DESC NULLS FIRST
◈ BOOLEANI & DATE
BOOLEANI
-- SQLite NON ha tipo BOOLEAN nativo
-- usa INTEGER: 0 = false, qualsiasi ≠ 0 = true

attivo INTEGER  -- convenzione 0/1

WHERE attivo          → attivo ≠ 0
WHERE attivo = 1      → esplicito
WHERE attivo = TRUE   → TRUE = 1
WHERE NOT attivo      → attivo = 0

-- TRUE/FALSE sono alias di 1/0 (≥ 3.23)
SELECT TRUE;   → 1
SELECT FALSE;  → 0
DATE E ORE
-- SQLite NON ha tipo DATE nativo
-- si usa TEXT, REAL o INTEGER

-- TEXT (ISO 8601) — raccomandato
'2025-01-15'
'2025-01-15 14:30:00'
'2025-01-15T14:30:00Z'

-- REAL (Julian Day Number)
julianday('2025-01-15')  → 2460690.5

-- INTEGER (Unix timestamp)
strftime('%s', 'now')   → secondi da epoch

-- confronto date in TEXT funziona!
WHERE data BETWEEN '2025-01-01'
             AND     '2025-12-31'
◈ DDL — DEFINIZIONE DELLA STRUTTURA
CREATE TABLE
CREATE TABLE utenti (
  id      INTEGER PRIMARY KEY AUTOINCREMENT,
  nome    TEXT    NOT NULL,
  email   TEXT    UNIQUE,
  eta     INTEGER DEFAULT 0,
  creato  TEXT    DEFAULT (datetime('now'))
);

-- se non esiste gia
CREATE TABLE IF NOT EXISTS log (
  id  INTEGER PRIMARY KEY,
  msg TEXT
);
AUTOINCREMENT garantisce ID sempre crescenti, ma e leggermente piu lento perche usa la tabella interna sqlite_sequence. Senza AUTOINCREMENT, SQLite riutilizza i rowid eliminati. Nella maggior parte dei casi, INTEGER PRIMARY KEY senza AUTOINCREMENT e sufficiente.
ALTER TABLE
-- aggiunge colonna
ALTER TABLE utenti ADD COLUMN telefono TEXT;

-- rinomina colonna (≥ 3.25)
ALTER TABLE utenti
  RENAME COLUMN eta TO anni;

-- rinomina tabella
ALTER TABLE utenti RENAME TO clienti;

-- drop colonna (≥ 3.35)
ALTER TABLE utenti DROP COLUMN telefono;
SQLite non supporta ALTER TABLE ... MODIFY COLUMN ne ALTER TABLE ... ALTER COLUMN. Per cambiare il tipo o i vincoli di una colonna si deve ricreare la tabella.
DROP
DROP TABLE IF EXISTS vecchie_righe;
DROP VIEW  IF EXISTS v_attivi;
DROP INDEX IF EXISTS idx_email;
CREATE VIEW
CREATE VIEW v_attivi AS
  SELECT id, nome, email
  FROM  utenti
  WHERE attivo = 1;
CREATE TRIGGER
CREATE TRIGGER trg_log_delete
AFTER DELETE ON utenti
BEGIN
  INSERT INTO log (msg)
  VALUES ('Eliminato: ' || OLD.nome);
END;

CREATE TRIGGER trg_validate_email
BEFORE INSERT ON utenti
WHEN NEW.email NOT LIKE '%@%.%'
BEGIN
  SELECT RAISE(ABORT, 'Email non valida');
END;
◈ INDICI & VINCOLI
INDICI
-- indice semplice
CREATE INDEX idx_nome
  ON utenti(nome);

-- indice univoco
CREATE UNIQUE INDEX idx_email
  ON utenti(email);

-- indice composto
CREATE INDEX idx_citta_nome
  ON utenti(citta, nome);

-- indice parziale
CREATE INDEX idx_attivi
  ON utenti(email)
  WHERE attivo = 1;

-- indice su espressione
CREATE INDEX idx_nome_lower
  ON utenti(lower(nome));

DROP INDEX IF EXISTS idx_nome;
VINCOLI
CREATE TABLE prodotti (
  id      INTEGER PRIMARY KEY,
  sku     TEXT    UNIQUE NOT NULL,
  prezzo  REAL    NOT NULL CHECK(prezzo > 0),
  cat_id  INTEGER REFERENCES categorie(id)
                  ON DELETE CASCADE
                  ON UPDATE SET NULL,
  stock   INTEGER DEFAULT 0
                  CHECK(stock >= 0)
);

-- azioni foreign key:
-- CASCADE, SET NULL, SET DEFAULT,
-- RESTRICT, NO ACTION
FOREIGN KEY
-- !! disabilitato di default !!
PRAGMA foreign_keys = ON;

FOREIGN KEY(col)
  REFERENCES altra_tabella(col)
  ON DELETE CASCADE
  ON UPDATE RESTRICT
EXPLAIN QUERY PLAN
-- visualizza piano di esecuzione
EXPLAIN QUERY PLAN
  SELECT * FROM utenti
  WHERE email = 'a@b.it';

-- bytecode della query
EXPLAIN
  SELECT * FROM utenti;
◈ DML — MANIPOLAZIONE DEI DATI
INSERT
INSERT INTO utenti (nome, email)
VALUES ('Mario', 'mario@x.it');

-- multi-riga
INSERT INTO utenti (nome, email) VALUES
  ('Luigi', 'luigi@x.it'),
  ('Anna',  'anna@x.it');

-- da SELECT
INSERT INTO archivio
  SELECT * FROM utenti WHERE anni > 60;
INSERT OR ... / UPSERT
-- ignora conflitti
INSERT OR IGNORE INTO utenti (nome)
VALUES ('Mario');

-- sovrascrive (delete + insert)
INSERT OR REPLACE INTO utenti
  (id, nome) VALUES (1, 'Mario Rossi');

-- upsert con ON CONFLICT (≥ 3.24)
INSERT INTO utenti (id, nome)
VALUES (1, 'Mario')
ON CONFLICT(id) DO UPDATE SET
  nome = excluded.nome;
UPDATE
UPDATE utenti
SET   nome = 'Mario Bianchi',
      anni = anni + 1
WHERE id = 1;

-- con subquery
UPDATE ordini
SET   totale = (
  SELECT SUM(prezzo)
  FROM  voci WHERE voci.ordine_id = ordini.id
);

-- UPDATE con LIMIT (≥ 3.33, compilato con SQLITE_ENABLE_UPDATE_DELETE_LIMIT)
UPDATE utenti SET attivo = 0
WHERE ultimo_accesso < '2024-01-01'
LIMIT 100;
DELETE
DELETE FROM utenti WHERE id = 1;

-- svuota tabella (mantiene struttura)
DELETE FROM utenti;

-- per resettare anche l'auto-increment:
DELETE FROM utenti;
DELETE FROM sqlite_sequence
  WHERE name = 'utenti';
RETURNING (≥ 3.35)
-- restituisce le righe modificate
INSERT INTO utenti (nome) VALUES ('Mario')
  RETURNING id, nome;

UPDATE utenti SET anni = anni + 1
  WHERE id = 1
  RETURNING *;

DELETE FROM utenti WHERE attivo = 0
  RETURNING id, nome;
◈ SELECT & JOIN
SELECT BASE
SELECT id, nome, anni
FROM  utenti
WHERE anni BETWEEN 18 AND 65
  AND email IS NOT NULL
ORDER BY nome ASC
LIMIT 10 OFFSET 20;
JOIN
-- INNER JOIN
SELECT u.nome, o.totale
FROM  utenti u
JOIN  ordini o ON o.utente_id = u.id;

-- LEFT JOIN
SELECT u.nome, o.id
FROM  utenti u
LEFT JOIN ordini o ON o.utente_id = u.id;

-- RIGHT JOIN (≥ 3.39) / FULL OUTER JOIN (≥ 3.39)
SELECT u.nome, o.id
FROM  utenti u
FULL JOIN ordini o ON o.utente_id = u.id;

-- CROSS JOIN (prodotto cartesiano)
SELECT * FROM colori CROSS JOIN taglie;
CASE
SELECT nome,
  CASE
    WHEN anni < 18 THEN 'minore'
    WHEN anni < 65 THEN 'adulto'
    ELSE               'senior'
  END AS fascia
FROM utenti;
◈ GROUP BY & AGGREGAZIONE
GROUP BY / HAVING
SELECT   citta, COUNT(*) AS n,
         AVG(anni) AS eta_media
FROM     utenti
GROUP BY citta
HAVING   COUNT(*) > 5
ORDER BY n DESC;
FUNZIONI DI AGGREGAZIONE
COUNT(*) / COUNT(col)    -- * = tutte le righe, col = esclusi NULL
SUM(col) / AVG(col)
MAX(col) / MIN(col)
GROUP_CONCAT(col, sep)    -- concatena valori (default sep = ',')
TOTAL(col)                -- come SUM ma restituisce 0.0 invece di NULL
◈ SUBQUERY, CTE & WINDOW FUNCTIONS
CTE — COMMON TABLE EXPRESSION
WITH top_clienti AS (
  SELECT utente_id, SUM(totale) spesa
  FROM  ordini
  GROUP BY utente_id
)
SELECT u.nome, t.spesa
FROM  top_clienti t
JOIN  utenti u ON u.id = t.utente_id
ORDER BY t.spesa DESC;
CTE RICORSIVA
-- serie numerica
WITH RECURSIVE nums(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 10
)
SELECT * FROM nums;

-- generare date consecutive
WITH RECURSIVE cal(d) AS (
  SELECT '2025-01-01'
  UNION ALL
  SELECT date(d, '+1 day')
  FROM cal WHERE d < '2025-01-31'
)
SELECT * FROM cal;
SUBQUERY SCALARI & EXISTS
-- subquery scalare (restituisce 1 valore)
SELECT nome,
  (SELECT COUNT(*) FROM ordini
   WHERE ordini.uid = utenti.id) n_ordini
FROM utenti;

-- EXISTS
SELECT nome FROM utenti u
WHERE EXISTS (
  SELECT 1 FROM ordini o
  WHERE o.uid = u.id
    AND o.totale > 1000
);
WINDOW FUNCTIONS (≥ 3.25)
SELECT nome,
  ROW_NUMBER() OVER (ORDER BY spesa DESC) rn,
  RANK()       OVER (ORDER BY spesa DESC) rk,
  DENSE_RANK() OVER (ORDER BY spesa DESC) dr,
  SUM(spesa)   OVER () totale_globale,
  SUM(spesa)   OVER (ORDER BY id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) running_total,
  LAG(spesa)   OVER (ORDER BY id) spesa_prec,
  LEAD(spesa)  OVER (ORDER BY id) spesa_succ
FROM clienti;

-- con PARTITION BY
SELECT citta, nome,
  ROW_NUMBER() OVER (
    PARTITION BY citta
    ORDER BY spesa DESC
  ) pos_in_citta
FROM clienti;
◈ UNION / INTERSECT / EXCEPT
SELECT email FROM utenti
UNION              -- unione senza duplicati
SELECT email FROM invitati;

SELECT email FROM utenti
UNION ALL          -- unione con duplicati (piu veloce)
SELECT email FROM invitati;

SELECT email FROM utenti
INTERSECT          -- comuni ad entrambi
SELECT email FROM newsletter;

SELECT email FROM utenti
EXCEPT             -- presenti solo nel primo
SELECT email FROM ban;
◈ OPERATORI
ARITMETICI
+   addizione
-   sottrazione
*   moltiplicazione
/   divisione (intera se entrambi INTEGER)
%   modulo (resto)

SELECT 7 / 2;      → 3   (divisione intera!)
SELECT 7.0 / 2;    → 3.5 (almeno un REAL)
SELECT 7 % 2;      → 1
SELECT -7 % 2;     → -1
CONFRONTO
=  ==           uguale
!=  <>          diverso
<  <=  >  >=   confronto
IS              uguale (gestisce NULL)
IS NOT          diverso (gestisce NULL)
IS NULL / IS NOT NULL
BETWEEN a AND b inclusivo
IN (lista)
NOT IN (lista)
LOGICI
AND   -- entrambi veri
OR    -- almeno uno vero
NOT   -- negazione

-- EXISTS
WHERE EXISTS (SELECT 1 FROM ...)
WHERE NOT EXISTS (...)
BITWISE & CONCATENAZIONE
-- bitwise (solo su INTEGER)
&    AND bit a bit
|    OR  bit a bit
~    NOT bit a bit (unario)
<<   shift sinistro
>>   shift destro

SELECT 5 & 3;    → 1  (101 & 011)
SELECT 5 | 3;    → 7  (101 | 011)
SELECT 5 << 1;   → 10

-- concatenazione stringa
||   concatena
SELECT 'ciao' || ' ' || 'mondo';
     → 'ciao mondo'
ESPRESSIONI UTILI
-- clamp (limita tra min e max)
MAX(0, MIN(100, valore))

-- arrotondamento finanziario
ROUND(prezzo * 1.22, 2)

-- divisione sicura (evita div/0 → NULL)
CASE WHEN denom = 0 THEN NULL
     ELSE num * 1.0 / denom
END
◈ LIKE, GLOB & COLLATION
LIKE (CASE-INSENSITIVE ASCII)
WHERE nome LIKE 'Mar%'    -- inizia con Mar
WHERE nome LIKE '_ario'   -- Mario, Dario...
WHERE nome LIKE '%ciao%'  -- contiene 'ciao'

-- escape per carattere letterale
WHERE col LIKE '50\%' ESCAPE '\'
→ cerca letteralmente '50%'

-- LIKE e case-insensitive solo per ASCII
-- per Unicode serve ICU extension
GLOB (CASE-SENSITIVE, STILE UNIX)
WHERE path GLOB '*.txt'   -- finisce con .txt
WHERE nome GLOB '[A-Z]*'  -- inizia con maiuscola
WHERE nome GLOB 'foto_???' -- foto_ + 3 char
WHERE id   IN (1, 2, 3)
WHERE id   NOT IN (SELECT id FROM ban)
COLLATION (ORDINAMENTO TESTO)
-- collation disponibili
BINARY    -- default, byte per byte
NOCASE    -- A-Z insensitive (solo ASCII)
RTRIM     -- ignora spazi a destra

-- su colonna
nome TEXT COLLATE NOCASE

-- su query
ORDER BY nome COLLATE NOCASE
WHERE  nome = 'mario' COLLATE NOCASE

-- su indice
CREATE INDEX idx_nome_ci
  ON utenti(nome COLLATE NOCASE);
REGEXP
-- REGEXP richiede un'estensione
-- (non e built-in in SQLite)
-- con estensione caricata:
WHERE email REGEXP '^[a-z]+@.+\..+$'
◈ FUNZIONI — TESTO & NUMERI
TESTO
length(s)              → n. caratteri
upper(s) / lower(s)
substr(s, start, len)  → sottostringa (1-based)
replace(s, da, a)
trim(s) / ltrim(s) / rtrim(s)
instr(s, pattern)      → posizione (0 se assente)
printf('%05d', n)     → formattazione
hex(blob)              → rappresentazione hex
quote(val)             → escape SQL
unicode(s)             → codepoint primo char
char(n1, n2, ...)      → stringa da codepoint
NUMERI
abs(n)
round(n, decimali)
ceil(n) / floor(n)     -- ≥ 3.35
max(a, b) / min(a, b) -- scalari, non aggregati
random()               → intero 64 bit casuale
randomblob(n)          → n byte casuali
zeroblob(n)            → n byte zero
sign(n)                → -1, 0, 1 (≥ 3.39)
TIPO & INFORMAZIONI
typeof(val)            → 'integer','text'...
sqlite_version()       → es. '3.45.1'
last_insert_rowid()
changes()              → righe modificate
total_changes()        → totale dalla connessione
◈ FUNZIONI — DATA E ORA
FUNZIONI BASE
date('now')            → '2025-01-15'
time('now')            → '14:30:00'
datetime('now')        → '2025-01-15 14:30:00'
julianday('now')       → 2460690.1...
unixepoch('now')       → intero (≥ 3.38)
strftime('%Y-%m', 'now')
MODIFICATORI
date('now', '+7 days')
date('now', '-1 month')
date('now', 'start of month')
date('now', 'start of year')
date('now', 'weekday 1')  → prossimo lun
datetime('now', 'localtime')
datetime('now', '+2 hours', '-30 minutes')

-- si possono concatenare piu modificatori
date('now', 'start of month', '+1 month', '-1 day')
→ ultimo giorno del mese corrente
FORMATO STRFTIME
strftime('%Y', 'now')  → '2025' (anno)
strftime('%m', 'now')  → '01'   (mese)
strftime('%d', 'now')  → '15'   (giorno)
strftime('%H', 'now')  → '14'   (ore)
strftime('%M', 'now')  → '30'   (minuti)
strftime('%S', 'now')  → '00'   (secondi)
strftime('%w', 'now')  → '3'    (giorno sett. 0=dom)
strftime('%j', 'now')  → '015'  (giorno anno)
strftime('%s', 'now')  → unix timestamp
strftime('%W', 'now')  → '03'   (settimana anno)
CALCOLI TRA DATE
-- differenza in giorni
SELECT julianday('2025-12-31')
     - julianday('2025-01-01');  → 364.0

-- eta in anni
SELECT (strftime('%Y', 'now')
      - strftime('%Y', data_nascita))
     - (strftime('%m-%d', 'now')
      < strftime('%m-%d', data_nascita))
  AS eta;
◈ FUNZIONI — NULL & CONDIZIONI
COALESCE(a, b, c)          → primo valore non-NULL
IFNULL(val, default)        → val se non NULL, altrimenti default
NULLIF(a, b)                → NULL se a = b, altrimenti a
IIF(cond, val_true, val_false) → ternario (≥ 3.32)

-- CASE come funzione
CASE col
  WHEN 'a' THEN 1
  WHEN 'b' THEN 2
  ELSE 0
END

-- CASE con condizioni
CASE
  WHEN prezzo > 100 THEN 'caro'
  WHEN prezzo > 10  THEN 'medio'
  ELSE                    'economico'
END
◈ FUNZIONI — JSON

Le funzioni JSON sono disponibili da SQLite 3.9.0. Gli operatori -> e ->> richiedono ≥ 3.38.

LETTURA & VALIDAZIONE
json('{"a":1}')            → valida/normalizza
json_extract(col, '$.nome')
json_extract(col, '$.tag[0]')
json_type(col, '$.x')       → tipo del valore
json_valid(col)             → 0 o 1

-- operatori (≥ 3.38)
col -> '$.nome'              → valore JSON
col ->> '$.nome'             → valore SQL nativo
CREAZIONE & MODIFICA
json_array(1, 2, 3)       → '[1,2,3]'
json_object('k', 'v')      → '{"k":"v"}'
json_set(col, '$.x', 42)   → aggiunge/modifica
json_insert(col, '$.x', 42) → solo se non esiste
json_replace(col, '$.x', 42)→ solo se esiste
json_remove(col, '$.x')     → rimuove chiave
json_patch(base, patch)     → RFC 7396 merge

-- iterare su array JSON
SELECT value FROM
  json_each('[1,2,3]');
→ 1, 2, 3 (una riga per elemento)
◈ TRANSAZIONI
TIPI DI BEGIN
BEGIN;           -- o BEGIN DEFERRED (default)
BEGIN IMMEDIATE; -- lock in scrittura subito
BEGIN EXCLUSIVE; -- lock totale sul database

COMMIT;          -- o END
ROLLBACK;
ESEMPIO TRANSAZIONE
BEGIN;
  UPDATE conti SET saldo = saldo - 100
    WHERE id = 1;
  UPDATE conti SET saldo = saldo + 100
    WHERE id = 2;
COMMIT;
SAVEPOINT
SAVEPOINT sp1;
  INSERT INTO ...
  SAVEPOINT sp2;
    UPDATE ...
  ROLLBACK TO sp2; -- annulla solo sp2
  RELEASE sp2;     -- rilascia savepoint
RELEASE sp1;       -- equivale a COMMIT
DEFERRED: lock acquisito alla prima lettura/scrittura.
IMMEDIATE: lock scrittura acquisito subito — previene deadlock in scenari concorrenti.
EXCLUSIVE: nessun altro puo leggere o scrivere.
◈ PRAGMA
PERFORMANCE
PRAGMA journal_mode = WAL;    → Write-Ahead Log
PRAGMA synchronous  = NORMAL;  → bilancia sicurezza/I/O
PRAGMA cache_size   = -64000;  → ~64 MB di cache
PRAGMA temp_store   = MEMORY;
PRAGMA mmap_size    = 268435456;→ 256 MB mmap
INTEGRITA
PRAGMA foreign_keys     = ON;
PRAGMA integrity_check;         → verifica DB completa
PRAGMA quick_check;             → verifica veloce
PRAGMA foreign_key_check;
PRAGMA wal_checkpoint(FULL);
INFORMAZIONI
PRAGMA table_info(utenti);    → schema colonne
PRAGMA table_xinfo(utenti);   → include colonne nascoste
PRAGMA index_list(utenti);    → lista indici
PRAGMA index_info(idx_nome);
PRAGMA database_list;         → db allegati
PRAGMA compile_options;       → opzioni compilazione
PRAGMA page_count;
PRAGMA page_size;
PRAGMA freelist_count;
MANUTENZIONE
VACUUM;                → compatta il file
VACUUM INTO 'backup.db'; → copia compatta
ANALYZE;               → aggiorna statistiche
REINDEX;               → ricostruisce indici
WAL mode e consigliato per la maggior parte degli scenari: permette letture concorrenti durante la scrittura e migliora le performance. Impostare journal_mode = WAL e synchronous = NORMAL all'apertura della connessione.
◈ SHELL CLI (.sqlite3)
META-COMANDI
.open file.db       -- apri/crea database
.tables             -- lista tabelle
.schema utenti      -- DDL della tabella
.schema             -- tutto lo schema
.indexes utenti     -- lista indici
.fullschema         -- schema + statistiche
.dbinfo             -- info sul database
.quit / .exit
OUTPUT
.mode column        -- colonne allineate
.mode table         -- tabella ASCII
.mode csv
.mode json
.mode markdown
.headers on
.width 20 10 30
.output risultati.csv
.once risultati.txt  -- solo prossima query
.output stdout
IMPORT / EXPORT
-- importa CSV
.mode csv
.import dati.csv nome_tabella

-- esporta dump SQL
.output dump.sql
.dump
.output stdout

-- esegui script
.read script.sql
DA TERMINALE
# apri e interroga
sqlite3 file.db "SELECT * FROM utenti;"

# esegui script
sqlite3 file.db < script.sql

# backup veloce
sqlite3 orig.db ".backup backup.db"

# export CSV
sqlite3 -header -csv file.db \
  "SELECT * FROM utenti;" > out.csv

# ATTACH: collegare piu database
sqlite3 main.db
ATTACH 'altro.db' AS db2;
SELECT * FROM db2.tabella;