ROWID in SQLite: chiavi primarie nascoste, alias e WITHOUT ROWID

Ogni tabella ha una colonna segreta

Crea una normale tabella SQLite e hai già una colonna che non hai dichiarato:

CREATE TABLE notes (
    title TEXT,
    body  TEXT
);

INSERT INTO notes (title, body) VALUES ('prima', 'ciao');
INSERT INTO notes (title, body) VALUES ('seconda', 'mondo');

SELECT rowid, title, body FROM notes;

Quella colonna rowid è reale. SQLite ne assegna una a ogni riga di ogni tabella normale, che tu lo chieda o no. È un intero con segno a 64 bit, unico all'interno della tabella, ed è la vera chiave che SQLite usa per trovare le righe nella sua struttura B-tree. Pensala come la spina dorsale della tabella: l'indice che tiene in ordine tutto il resto.

Di solito non la vedi perché SELECT * non la include. Devi chiederla per nome.

Il ROWID ha tre alias

Dato che rowid compare spesso nell'SQL scritto per altri database, SQLite accetta tre nomi per la stessa colonna:

CREATE TABLE notes (title TEXT);
INSERT INTO notes VALUES ('a'), ('b'), ('c');

SELECT rowid, oid, _rowid_, title FROM notes;

rowid, oid e _rowid_ si riferiscono tutti alla stessa colonna nascosta. Se hai dichiarato una colonna vera con uno di questi nomi, vince la tua colonna e l'alias non è più disponibile, ma è l'unico tranello. Nel codice di tutti i giorni scrivi semplicemente rowid.

INTEGER PRIMARY KEY è la formula magica

Ecco la parte che confonde chiunque arrivi da altri database. Se dichiari una colonna esattamente come INTEGER PRIMARY KEY, quella colonna non viene salvata a parte: diventa il rowid:

CREATE TABLE users (
    id   INTEGER PRIMARY KEY,
    name TEXT
);

INSERT INTO users (name) VALUES ('Ada'), ('Boris'), ('Rosa');

SELECT rowid, id, name FROM users;

rowid e id sono la stessa colonna con due nomi. Gli insert che omettono id ricevono un intero scelto in automatico (di solito il rowid massimo + 1). Ecco perché INTEGER PRIMARY KEY è il modo più efficiente per dare a una tabella una chiave con incremento automatico in SQLite: nessuna colonna in più, nessun indice in più, solo il rowid stesso.

La grafia esatta conta. INT PRIMARY KEY non è la stessa cosa: qui INT e INTEGER hanno effetti diversi:

CREATE TABLE a (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE b (id INT PRIMARY KEY, name TEXT);

INSERT INTO a (name) VALUES ('x');
INSERT INTO b (name) VALUES ('y');

SELECT rowid, id, name FROM a;
SELECT rowid, id, name FROM b;

Nella tabella a, id e rowid coincidono. Nella tabella b, id è una colonna normale e rowid è l'intero nascosto separato. Peggio ancora, b.id non si riempie in automatico all'insert: resta NULL finché non lo imposti. Usa INTEGER PRIMARY KEY (la parola intera) quando vuoi il comportamento da alias.

Ottenere il ROWID di un insert

Dopo un INSERT spesso vuoi sapere quale rowid è stato appena assegnato, di solito per collegargli una riga figlia. SQLite ti dà last_insert_rowid():

CREATE TABLE posts (
    id    INTEGER PRIMARY KEY,
    title TEXT
);

INSERT INTO posts (title) VALUES ('primo');
INSERT INTO posts (title) VALUES ('secondo');

SELECT last_insert_rowid();

La funzione restituisce il rowid dell'ultimo insert riuscito sulla connessione corrente. La maggior parte dei driver di database espone lo stesso valore come cursor.lastrowid o simili. La clausola RETURNING (trattata più avanti) è un altro modo per ottenerlo direttamente come parte dell'insert.

I ROWID non sono permanenti

Il rowid di una riga è stabile finché la riga esiste, ma non è un identificatore a vita. Un VACUUM può rinumerare i rowid, e se cancelli una riga il suo numero può essere riutilizzato da un insert futuro:

CREATE TABLE items (name TEXT);
INSERT INTO items VALUES ('a'), ('b'), ('c');

DELETE FROM items WHERE name = 'c';
INSERT INTO items VALUES ('d');

SELECT rowid, name FROM items;

Nota che la nuova riga potrebbe riutilizzare o no il vecchio rowid a seconda della versione e delle circostanze: il punto è che non puoi contare sul fatto che resti unico per sempre. Se ti serve un identificatore che sopravviva a cancellazioni, vacuum ed esportazioni, dichiara una tua colonna INTEGER PRIMARY KEY (che fissa il valore a quella riga) e valuta la parola chiave AUTOINCREMENT se ti servono proprio valori sempre crescenti e mai riutilizzati.

Tabelle WITHOUT ROWID

A volte il rowid è un costo che non vuoi, soprattutto quando la tua vera chiave non è un intero. Una tabella di città con chiave sul nome, per esempio, finisce per avere due strutture: il B-tree del rowid e un indice separato su name per imporre la chiave primaria. WITHOUT ROWID le fonde in una sola:

CREATE TABLE cities (
    name       TEXT PRIMARY KEY,
    population INTEGER
) WITHOUT ROWID;

INSERT INTO cities VALUES ('Lisbon', 545000), ('Paris', 2102000);

SELECT * FROM cities WHERE name = 'Lisbon';
-- SELECT rowid FROM cities;   -- error: no such column: rowid

Ora name è la vera chiave di archiviazione. Le ricerche per name saltano un livello di indirezione e la tabella è più piccola. I compromessi:

• Niente rowid, oid o _rowid_: quelle colonne semplicemente non esistono.
• last_insert_rowid() non si aggiorna per gli insert su questa tabella.
• L'I/O incrementale sui BLOB e alcune funzioni di replica non sono disponibili.
• La tabella deve avere una PRIMARY KEY dichiarata.

WITHOUT ROWID è un'ottimizzazione ponderata, non un'impostazione predefinita. Usala quando la chiave primaria non è intera e la tabella è grande o molto scritta. Per le normali tabelle con chiave intera, la struttura standard con rowid è già ottimale.

Il modello mentale

Ridotto all'essenziale:

• Ogni tabella SQLite normale ha una chiave intera nascosta a 64 bit chiamata rowid.
• INTEGER PRIMARY KEY (con questa grafia esatta) trasforma la tua colonna in un suo alias.
• Usa last_insert_rowid() per leggere il valore appena assegnato.
• I rowid possono essere riutilizzati dopo le cancellazioni e rinumerati da VACUUM.
• Le tabelle WITHOUT ROWID rinunciano alla chiave nascosta e usano direttamente la chiave primaria che hai dichiarato: utili per chiavi non intere, ma rinunciano ad alcune funzioni.

Quasi sempre non pensi affatto al rowid. Dichiari id INTEGER PRIMARY KEY, lasci che SQLite gestisca la numerazione e vai avanti. I meccanismi contano quando ottimizzi lo spazio, leggi schemi esistenti o ti chiedi perché INT PRIMARY KEY si comporta diversamente da INTEGER PRIMARY KEY.

Prossimo passo: NOT NULL e DEFAULT

Ora che l'identità della riga è sistemata, il livello successivo è fare in modo che le altre colonne contengano valori sensati. NOT NULL e DEFAULT sono le due clausole che fanno gran parte di questo lavoro, e sono il prossimo argomento.