Gestione e utilizzo di database SQLite 3

SQLite 3 è un sistema di gestione di database relazionale embedded molto diffuso. Grazie alla sua leggerezza, all'assenza di un processo server separato e alla memorizzazione dei dati in un singolo file, rappresenta la scelta ideale per applicazioni mobili, sistemi embedded e piccoli software desktop (così come per ambienti di sviluppo locale). SQLite 3 è facile da gestire e utilizzare. Questa guida dettagliata è pensata per gli utenti ServBay e spiega come gestire e utilizzare in modo efficiente database SQLite 3 in ambiente ServBay, coprendo verifica dell'installazione, connessione, operazioni di base, backup e ripristino, ottimizzazione delle prestazioni e aspetti relativi alla sicurezza.

Panoramica

ServBay, ambiente completo per lo sviluppo web locale, include già SQLite 3 e le relative librerie di sviluppo, rendendo possibile l'accesso e la gestione del database sia da linea di comando che tramite i principali linguaggi di programmazione supportati (come PHP, Node.js, Python, Go, ecc.) senza ulteriori installazioni. Questo semplifica notevolmente l’utilizzo di SQLite 3 nello sviluppo locale.

Installazione e configurazione di SQLite 3

ServBay integra SQLite 3 di default: non sono necessari ulteriori passaggi di installazione. L’eseguibile e le librerie di SQLite 3 sono già presenti nel pacchetto software di ServBay.

Verifica dell’installazione di SQLite 3

È possibile verificare rapidamente la disponibilità di SQLite 3 fornita da ServBay digitando il seguente comando nel terminale e visualizzando le informazioni sulla versione disponibile:

sqlite3 --version

Se il comando restituisce il numero di versione di SQLite 3 (ad esempio 3.41.2 2023-03-26 11:52:19 ...), significa che l’integrazione è avvenuta correttamente e SQLite 3 è pronto all’uso.

Creazione e connessione a un database SQLite 3

Un database SQLite 3 corrisponde semplicemente a un file. In modo predefinito, ServBay consiglia di salvare i file dei database nella directory /Applications/ServBay/db/sqlite, ma questa non è una regola fissa: puoi scegliere la posizione più adatta alle esigenze del progetto. La creazione o la connessione a un database si effettua tramite lo strumento a riga di comando sqlite3 o tramite le librerie specifiche dei linguaggi di programmazione.

Connessione via riga di comando

Lo strumento a riga di comando sqlite3 è il modo più diretto per gestire un database SQLite 3.

1. Creazione o connessione a un database: Esegui questo comando nel terminale. Se il file database indicato non esiste, il comando sqlite3 lo creerà; se invece esiste, si connetterà ad esso.

   sqlite3 /Applications/ServBay/db/sqlite/servbay.db

   (In questo esempio il database si chiama servbay.db per richiamare il brand ServBay)

   Apparirà il prompt sqlite>, dove potrai eseguire comandi SQL o .commands.

2. Uscita da SQLite 3: Dal prompt sqlite>, digita il comando .exit per uscire dallo strumento.

   .exit

Connessione tramite linguaggi di programmazione

Tutti i principali linguaggi supportati da ServBay offrono librerie per la gestione dei database SQLite 3. Ecco alcuni esempi per PHP e Node.js.

Esempio in PHP

ServBay integra normalmente l’estensione PHP per SQLite 3 (php-sqlite3). Si può usare la classe SQLite3 per connettersi al database.

<?php
// Specifica il percorso del file database, preferibilmente nella cartella db/sqlite di ServBay
$database_file = '/Applications/ServBay/db/sqlite/servbay.db';

// Connessione al database SQLite 3
// Se il file non esiste, il costruttore di SQLite3 lo creerà automaticamente
$db = new SQLite3($database_file);

if (!$db) {
    die("Impossibile connettersi al database SQLite 3: " . $db->lastErrorMsg());
}

echo "Connessione riuscita al database SQLite 3: " . $database_file . "\n";

// Creazione tabella (se non esiste)
$create_table_sql = "CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE
)";
$db->exec($create_table_sql);
echo "Tabella 'users' controllata o creata.\n";

// Inserimento dati
$name = 'ServBay Demo User';
$email = '[email protected]';
$insert_sql = $db->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');
$insert_sql->bindValue(':name', $name, SQLITE3_TEXT);
$insert_sql->bindValue(':email', $email, SQLITE3_TEXT);

if ($insert_sql->execute()) {
    echo "Inserimento dati riuscito.\n";
} else {
    echo "Inserimento dati fallito: " . $db->lastErrorMsg() . "\n";
}


// Query dei dati
$query_sql = "SELECT id, name, email FROM users";
$result = $db->query($query_sql);

if ($result) {
    echo "Risultati della query:\n";
    while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
        echo "ID: " . $row['id'] . ", Nome: " . $row['name'] . ", Email: " . $row['email'] . "\n";
    }
} else {
     echo "Query fallita: " . $db->lastErrorMsg() . "\n";
}


// Chiusura della connessione
$db->close();
unset($db); // Libera le risorse
echo "Connessione al database chiusa.\n";

?>

Esempio in Node.js

Per utilizzare SQLite 3 in Node.js è necessario installare il pacchetto npm sqlite3. Apri il terminale, vai nella directory del progetto ed esegui:

npm install sqlite3

Poi potrai utilizzare la libreria per connetterti e operare sul database:

const sqlite3 = require('sqlite3').verbose();
const path = require('path');

// Specifica il percorso del file database usando path.join per la compatibilità cross-platform
const dbPath = path.join('/Applications/ServBay/db/sqlite', 'servbay.db');

// Connessione al database SQLite 3
// Se il file non esiste, sqlite3.Database lo creerà automaticamente
let db = new sqlite3.Database(dbPath, sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE, (err) => {
  if (err) {
    console.error('Impossibile connettersi al database SQLite 3:', err.message);
  } else {
    console.log('Connessione riuscita al database SQLite.');
  }
});

// Esecuzione serializzata dei comandi, per garantire l’ordine delle operazioni
db.serialize(() => {
  // Creazione tabella (se non esiste)
  db.run(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE
  )`, (err) => {
    if (err) {
      console.error('Creazione tabella fallita:', err.message);
    } else {
      console.log('Tabella "users" controllata o creata.');
    }
  });

  // Inserimento dati
  const name = 'ServBay Demo User';
  const email = '[email protected]';
  db.run(`INSERT INTO users (name, email) VALUES (?, ?)`, [name, email], function(err) {
    if (err) {
      // SQLITE_CONSTRAINT è il codice di errore per violazione di vincolo UNIQUE
      if (err.errno === 19) { // SQLITE_CONSTRAINT
         console.warn(`L'utente '${name}' (${email}) esiste già, inserimento saltato.`);
      } else {
         console.error('Inserimento dati fallito:', err.message);
      }
    } else {
      console.log(`Riga inserita con successo, ID: ${this.lastID}`);
    }
  });

  // Query dei dati
  db.all(`SELECT id, name, email FROM users`, [], (err, rows) => {
    if (err) {
      throw err;
    }
    console.log('Risultati della query:');
    rows.forEach((row) => {
      console.log(`ID: ${row.id}, Nome: ${row.name}, Email: ${row.email}`);
    });
  });
});

// Chiusura della connessione al database
db.close((err) => {
  if (err) {
    console.error('Chiusura della connessione fallita:', err.message);
  } else {
    console.log('Connessione al database chiusa.');
  }
});

Nota: In applicazioni reali, assicurati di rendere sicuro il percorso del file database e di gestire in modo corretto errori e accessi concorrenti.

Gestione del database

Operazioni SQL di base

Una volta connesso a un database SQLite 3, è possibile eseguire normali comandi SQL per gestire i dati. Ecco alcune operazioni tipiche:

1. Creare una tabella: per definire la struttura dei dati.

   CREATE TABLE products (
       product_id INTEGER PRIMARY KEY AUTOINCREMENT,
       product_name TEXT NOT NULL,
       price REAL DEFAULT 0.00
   );

2. Inserire dati: per aggiungere nuovi record.

   INSERT INTO products (product_name, price) VALUES ('ServBay T-Shirt', 19.99);
   INSERT INTO products (product_name, price) VALUES ('ServBay Sticker Pack', 4.99);

3. Interrogare i dati: per recuperare record dalla tabella.

   SELECT * FROM products;
   SELECT product_name, price FROM products WHERE price > 10.00;

4. Aggiornare i dati: per modificare record esistenti.

   UPDATE products SET price = 24.99 WHERE product_name = 'ServBay T-Shirt';

5. Cancellare i dati: per eliminare uno o più record.

   DELETE FROM products WHERE product_id = 1;
   DELETE FROM products; -- Cancella tutti i dati dalla tabella

6. Eliminare una tabella: per rimuovere completamente la tabella e i dati.

   DROP TABLE products;

Questi comandi possono essere eseguiti direttamente attraverso lo strumento sqlite3 o usando le funzioni delle librerie SQLite dei singoli linguaggi.

Backup e ripristino

Effettuare backup e il ripristino di un database SQLite 3 è semplice, dato che il database è costituito da un singolo file.

Backup del database

Il modo più semplice per eseguire un backup è copiare il file del database. ServBay consiglia di conservare i backup nella cartella /Applications/ServBay/backup/sqlite, per una gestione centralizzata.

Esempio di backup tramite riga di comando:

# Crea la cartella dei backup (se non già presente)
mkdir -p /Applications/ServBay/backup/sqlite

# Copia il file database per il backup
# Si consiglia di includere data e ora nel nome file per distinguere le versioni
cp /Applications/ServBay/db/sqlite/servbay.db /Applications/ServBay/backup/sqlite/servbay_$(date +%Y%m%d_%H%M%S).db

È anche possibile usare il comando .backup da linea di comando sqlite3 per un backup "a caldo" (cioè mentre il database è aperto), ma per lo sviluppo locale copiare semplicemente il file è solitamente più che sufficiente.

Ripristino del database

Per ripristinare il database, basta copiare il file di backup sulla posizione originale. Si raccomanda fortemente di arrestare tutte le applicazioni o servizi ServBay che potrebbero accedere al database prima del ripristino, per evitare problemi di lock o dati incoerenti.

# Supponiamo di voler ripristinare il backup più recente
# Trova il file di backup più recente, es: servbay_20231027_103000.db
LATEST_BACKUP=$(ls -t /Applications/ServBay/backup/sqlite/servbay_*.db | head -n 1)

# Verifica che il file di backup sia stato individuato
if [ -z "$LATEST_BACKUP" ]; then
  echo "Errore: Nessun file di backup SQLite trovato."
  exit 1
fi

echo "Backup da ripristinare: $LATEST_BACKUP"

# Arresta applicazioni/servizi coinvolti... (secondo la tua configurazione ServBay)
# Ad esempio, assicurati che nessun processo PHP stia accedendo al database

# Copia il backup sopra il file database corrente
cp "$LATEST_BACKUP" /Applications/ServBay/db/sqlite/servbay.db

echo "Ripristino del database completato."

# Riavvia eventuali servizi/applicazioni necessari...

Avviso importante: La copia diretta del file può corrompere i dati se il database è aperto in scrittura: per un ripristino sicuro assicurati che nessun servizio acceda al file, o utilizza le API di backup avanzate offerte da SQLite nei casi di database aperto.

Ottimizzazione delle prestazioni

SQLite 3 fornisce diversi comandi PRAGMA e strategie di indicizzazione per migliorare le performance del database.

Ottimizzazione degli indici

Creare indici sulle colonne utilizzate frequentemente in filtri (WHERE), join (JOIN) o ordinamenti (ORDER BY) può velocizzare in modo significativo le interrogazioni.

-- Crea un indice unico sulla colonna email della tabella users
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Crea un indice semplice sulla colonna product_name della tabella products
CREATE INDEX idx_products_name ON products(product_name);

Ottimizzazione delle query

Con il comando EXPLAIN QUERY PLAN puoi capire come SQLite esegue una query SQL, utile per individuare eventuali colli di bottiglia.

EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = '[email protected]';
EXPLAIN QUERY PLAN SELECT product_name FROM products ORDER BY price DESC;

Analizza il piano di esecuzione per verificare l’uso degli indici o la presenza di scansioni complete delle tabelle (SCAN TABLE).

Ottimizzazioni tramite PRAGMA

I comandi PRAGMA servono a controllare la configurazione runtime di SQLite 3. Alcune delle opzioni di ottimizzazione più comuni sono:

• PRAGMA journal_mode; / PRAGMA journal_mode = mode;: Definisce la modalità di journaling. Il valore WAL (Write-Ahead Logging) di solito offre migliori prestazioni in caso di letture e scritture concorrenti. La modalità predefinita può essere DELETE o TRUNCATE. Impostare WAL può ridurre la frequenza dei blocchi in scrittura.

  PRAGMA journal_mode = WAL;

• PRAGMA synchronous; / PRAGMA synchronous = livello;: Controlla quanto rigorosamente SQLite sincronizza i dati col filesystem. Impostando OFF (o 0) si velocizzano molto le scritture (a scapito di una possibile perdita dati in caso di crash), mentre livelli FULL (o 1) e NORMAL (o 2) garantiscono maggiore sicurezza dei dati (ma riducono la velocità). Per lo sviluppo locale di solito va bene impostare OFF.

  PRAGMA synchronous = OFF;

• PRAGMA cache_size; / PRAGMA cache_size = pagine;: Imposta la quantità di pagine del database da mantenere in cache in memoria. Aumentare questa dimensione riduce l’I/O su disco migliorando le prestazioni, ma aumenta l’uso della RAM.

  PRAGMA cache_size = -20000; -- Imposta la cache a 20MB (valori negativi = KB)

Nota: Le opzioni PRAGMA in genere agiscono solo sulla connessione attiva. Per renderle persistenti, applicale a ogni nuova connessione o all’avvio dell’applicazione.

Gestione della sicurezza

Anche se SQLite 3 è un database file-based, è importante seguire alcune precauzioni di sicurezza nell’ambiente di sviluppo locale.

Permessi sui file

Assicurati che i permessi del file del database consentano la lettura e scrittura solo all’utente (o processo) che esegue ServBay, per evitare accessi non autorizzati.

# Esempio: imposta il proprietario e i permessi per consentire lettura/scrittura solo all’utente corrente
# Adatta il comando all’utente effettivo che utilizza ServBay
chmod 600 /Applications/ServBay/db/sqlite/servbay.db

Crittografia dei dati

SQLite 3 non offre cifratura integrata. Se nel tuo ambiente di sviluppo locale gestisci dati sensibili e necessiti di proteggere il file database, puoi valutare estensioni come SQLCipher, che richiedono librerie aggiuntive e l’uso di API dedicate per accedere al database cifrato.

Domande frequenti e soluzioni comuni

Impossibile connettersi al database SQLite 3

1. Verifica del percorso del file database: Accertati che il percorso del file database sia corretto e che il file esista davvero.
2. Controllo dei permessi: Verifica che l’utente che esegue ServBay (o il terminale) abbia i permessi necessari. Usa ls -l /Applications/ServBay/db/sqlite/servbay.db e i comandi chmod / chown per eventuali modifiche.
3. File database corrotto? Prova a connetterti mediante il comando sqlite3 da terminale. Se fallisce o restituisce errori, potrebbe essere necessario recuperarlo da un backup.

Problemi di database bloccato ("Database is locked")

Quando SQLite 3 esegue operazioni di scrittura, blocca tutto il file database. Se altri processi provano a scrivere o, in certe condizioni, anche solo a leggere, potresti ricevere errori di "database bloccato".

1. Verifica dell’accesso concorrente: Assicurati che nessun’altra applicazione, script o processo stia scrivendo contemporaneamente sullo stesso database. Nell’ambiente di sviluppo ciò succede spesso se più server o script accedono alla stessa risorsa.

2. Uso del journal in modalità WAL: Abilitare PRAGMA journal_mode = WAL; migliora le performance in lettura/scrittura contemporanee, riducendo i conflitti. È buona prassi impostare questo PRAGMA subito dopo la connessione.

3. Gestione degli errori di lock: In fase di sviluppo è preferibile implementare meccanismi di ritentativo automatico: al primo errore, attendi e riprova. Le librerie di SQLite spesso permettono di impostare un "busy timeout" per tentativi automatici.

   Ad esempio, nella libreria Node.js sqlite3 si può impostare:

   db.configure("busyTimeout", 5000); // Imposta timeout di 5 secondi in caso di lock

   Nella classe PHP SQLite3 tale metodo non esiste, quindi occorre implementare logicamente un ciclo di ritentativi oppure valutare funzioni PHP più avanzate come sqlite_busy_timeout (se disponibile).

Conclusioni

ServBay, pensato appositamente per gli sviluppatori web, integra nativamente un database SQLite 3 potente e facile da usare. Dopo aver letto questa guida dovresti essere in grado di verificare l’installazione, collegarti al database, gestirlo, effettuare backup/ripristino, ottimizzare le performance e garantire la sicurezza dei dati su ServBay. Grazie alla sua leggerezza e praticità, SQLite 3 si conferma la scelta migliore per sviluppo locale, test e piccoli progetti. Integrato con la toolchain di ServBay (PHP, Node.js, Python, Go, diversi server web e database), puoi realizzare e collaudare applicazioni anche complesse in completa autonomia.