SQLite - PRAGMA: Una Guida Completa per Principianti

Ciao there, appassionati di database in erba! Oggi ci imbarcheremo in un viaggio emozionante nel mondo delle PRAGMAs di SQLite. Non preoccuparti se non hai mai sentito parlare delle PRAGMAs prima - alla fine di questo tutorial, le userai come un professionista!

Cos'è una PRAGMA?

Prima di immergerci, iniziiamo con le basi. In SQLite, una PRAGMA è un comando speciale che ci permette di modificare il comportamento della libreria SQLite o di interrogarne lo stato interno. Pensate alle PRAGMAs come al pannello di controllo del vostro database SQLite - vi permettono di regolare le impostazioni e ottenere informazioni su come funziona il vostro database.

Ora, esploriamo alcune delle PRAGMAs più utilizzate in SQLite.

PRAGMA auto_vacuum

La PRAGMA auto_vacuum è come avere un coinquilino ordinaio per il vostro database. Pulisce e organizza automaticamente il file del database man mano che cancellate dati.

PRAGMA auto_vacuum = FULL;

Questo comando imposta la modalità auto_vacuum su FULL, il che significa che SQLite farà del suo meglio per mantenere il file del database il più piccolo possibile.

PRAGMA cache_size

La PRAGMA cache_size è come dare al vostro database un cervello più grande. Determina quante pagine SQLite tiene in memoria per un accesso più rapido.

PRAGMA cache_size = 10000;

Questo imposta la dimensione della cache a 10.000 pagine. Più cache significa prestazioni più veloci, ma anche un utilizzo maggiore della memoria.

PRAGMA case_sensitive_like

Questa PRAGMA determina se l'operatore LIKE è sensibile alla maiuscola o meno. È come insegnare al vostro database a leggere con o senza gli occhiali.

PRAGMA case_sensitive_like = TRUE;

Con questa impostazione, 'Apple' LIKE 'app%' sarà falso, mentre 'Apple' LIKE 'App%' sarà vero.

PRAGMA count_changes

La PRAGMA count_changes è come un cronometrista per le operazioni del vostro database. Vi dice quante righe sono state modificate da una dichiarazione INSERT, UPDATE o DELETE.

PRAGMA count_changes = ON;

Dopo aver impostato questo, quando eseguite una dichiarazione UPDATE, per esempio, SQLite vi dirà quante righe sono state interessate.

PRAGMA database_list

Questa PRAGMA è come un elenco telefonico per il vostro database. Elenca tutti i database collegati, inclusi il database principale e qualsiasi altro che avete collegato.

PRAGMA database_list;

Questo vi mostrerà un elenco di tutti i database collegati, i loro percorsi di file e i loro nomi interni.

PRAGMA encoding

La PRAGMA encoding imposta o interroga la codifica di testo utilizzata dal database. È come scegliere quale lingua parli il vostro database.

PRAGMA encoding = "UTF-8";

Questo imposta la codifica del database su UTF-8, che è una scelta comune per supportare più lingue.

PRAGMA freelist_count

Questa PRAGMA vi dice quante pagine inutilizzate ci sono nel file del database. È come controllare quanto spazio libero avete nel vostro magazzino.

PRAGMA freelist_count;

Questo restituirà il numero di pagine inutilizzate nel file del database.

PRAGMA index_info e PRAGMA index_list

Queste PRAGMAs sono come avere una bibliotecaria per gli indici del vostro database. Forniscono informazioni sulla struttura e il contenuto dei vostri indici.

PRAGMA index_list(table_name);
PRAGMA index_info(index_name);

Il primo comando elenca tutti gli indici su una tabella, mentre il secondo fornisce informazioni dettagliate su un indice specifico.

PRAGMA journal_mode

La PRAGMA journal_mode controlla come SQLite tiene traccia delle transazioni. È come scegliere tra diversi tipi di sistemi di backup.

PRAGMA journal_mode = WAL;

Questo imposta la modalità journal su Write-Ahead Logging (WAL), che può migliorare le prestazioni in molti casi.

PRAGMA max_page_count e PRAGMA page_count

Queste PRAGMAs si occupano del numero di pagine nel file del database. Sono come impostare e controllare il numero massimo di pagine in un libro.

PRAGMA max_page_count = 1000000;
PRAGMA page_count;

Il primo comando imposta il numero massimo di pagine, mentre il secondo vi dice quante pagine sono attualmente in uso.

PRAGMA page_size

Questa PRAGMA imposta o interroga la dimensione della pagina del database. È come decidere quanto grande dovrebbe essere ogni pagina del vostro libro.

PRAGMA page_size = 4096;

Questo imposta la dimensione della pagina a 4096 byte, che è una scelta comune.

PRAGMA parser_trace e PRAGMA sql_trace

Queste PRAGMAs sono come attivare la modalità di debug per il vostro database. Forniscono informazioni dettagliate su come SQLite sta elaborando i vostri comandi.

PRAGMA parser_trace = ON;
PRAGMA sql_trace = ON;

Queste possono essere molto utili quando cercate di capire perché una query specifica non funziona come previsto.

PRAGMA recursive_triggers

Questa PRAGMA controlla se i trigger possono innescare altri trigger in modo ricorsivo. È come permettere effetti a catena nel vostro database.

PRAGMA recursive_triggers = ON;

Con questa impostazione, un trigger può causare l'attivazione di un altro trigger, che a sua volta può causare l'attivazione di un altro trigger, e così via.

PRAGMA schema_version e PRAGMA user_version

Queste PRAGMAs vi permettono di impostare e interrogare i numeri di versione per lo schema del vostro database. Sono come tag di versione per la struttura del database.

PRAGMA schema_version;
PRAGMA user_version = 1;

Il primo comando interroga la versione corrente dello schema, mentre il secondo imposta la versione utente a 1.

PRAGMA secure_delete

Questa PRAGMA controlla quanto attentamente SQLite sovrascrive i dati cancellati. È come scegliere tra triturare i vostri documenti o gettarli semplicemente nella spazzatura.

PRAGMA secure_delete = ON;

Con questa impostazione, SQLite sovrascriverà i dati cancellati con zeri, rendendo più difficile il loro recupero.

PRAGMA synchronous

La PRAGMA synchronous controlla quanto attentamente SQLite scrive i dati su disco. È un equilibrio tra sicurezza e velocità.

PRAGMA synchronous = NORMAL;

Questo imposta un livello moderato di sicurezza, bilanciando tra la massima sicurezza (FULL) e la massima velocità (OFF).

PRAGMA temp_store e PRAGMA temp_store_directory

Queste PRAGMAs controllano dove e come SQLite memorizza i dati temporanei. È come scegliere dove mettere il vostro foglio di appunti mentre lavorate.

PRAGMA temp_store = MEMORY;
PRAGMA temp_store_directory = '/path/to/directory';

Il primo comando dice a SQLite di memorizzare i dati temporanei in memoria, mentre il secondo specifica una directory per i file temporanei.

PRAGMA writable_schema

Questa PRAGMA controlla se potete modificare direttamente la tabella sqlite_master. È come avere una chiave maestra per la struttura del vostro database.

PRAGMA writable_schema = ON;

Siate molto cauti con questa! Permette di modificare la struttura fondamentale del vostro database, il che può essere pericoloso se non fatto correttamente.

Ecco una tabella che riassume tutte le PRAGMAs discusse:

PRAGMA	Descrizione
auto_vacuum	Controlla la pulizia automatica del database
cache_size	Imposta il numero di pagine da mantenere in memoria
case_sensitive_like	Controlla la sensibilità alla maiuscola dell'operatore LIKE
count_changes	Segnala il numero di righe modificate dalle operazioni
database_list	Elenca i database collegati
encoding	Imposta o interroga la codifica del testo del database
freelist_count	Segnala il numero di pagine inutilizzate nel database
index_info	Fornisce informazioni su un indice specifico
index_list	Elenca tutti gli indici su una tabella
journal_mode	Controlla come le transazioni sono tracciate
max_page_count	Imposta il numero massimo di pagine nel database
page_count	Segnala il numero corrente di pagine nel database
page_size	Imposta o interroga la dimensione della pagina del database
parser_trace	Abilita informazioni dettagliate di parsing
recursive_triggers	Permette ai trigger di innescare altri trigger ricorsivamente
schema_version	Imposta o interroga la versione dello schema
secure_delete	Controlla come i dati cancellati vengono sovrascritti
sql_trace	Abilita informazioni dettagliate di esecuzione SQL
synchronous	Controlla come i dati sono scritti su disco
temp_store	Controlla dove i dati temporanei sono memorizzati
temp_store_directory	Imposta la directory per i file temporanei
user_version	Imposta o interroga la versione definita dall'utente
writable_schema	Permette la modifica diretta della tabella sqlite_master

Ecco tutto! Ora sei ben equipaggiato per iniziare a sperimentare con le PRAGMAs di SQLite. Ricorda, questi strumenti potenti possono influenzare significativamente il comportamento e le prestazioni del tuo database, quindi sempre testare a fondo prima di utilizzarli in un ambiente di produzione. Buon codice!

Credits: Image by storyset