Cos'è un database?

Quando accedi a un sito web, il sito deve trovare il tuo account tra milioni di altri, controllare la password e caricare le tue impostazioni, tutto in pochi millisecondi. Non apre un file di testo leggendolo riga per riga. Invia una richiesta come SELECT * FROM users WHERE email = 'ana@example.com' a un database, e il database salta direttamente alla riga giusta usando un indice, un po' come l'indice analitico in fondo a un libro.

Come funziona un database

Due pezzi lavorano insieme. Il database sono i dati, organizzati in una struttura fissa. Il sistema di gestione di basi di dati (DBMS) è il programma che salva quei dati in file, li mantiene coerenti e risponde alle richieste. PostgreSQL, MySQL e SQLite sono DBMS; il tuo elenco di clienti è il database.

In un database relazionale, i dati vivono in tabelle. Ogni tabella ha colonne (nome, paese, punti) e righe (una per utente). Una richiesta passa per queste fasi:

1. Il tuo programma invia una query, di solito scritta in SQL (Structured Query Language).
2. Il DBMS analizza la query e pianifica come rispondere: quali tabelle leggere e se un indice può saltare la maggior parte delle righe.
3. Legge i dati dal disco in pagine di dimensione fissa (4.096 byte per impostazione predefinita in SQLite, 8 KB in PostgreSQL), tenendo in memoria le pagine usate più spesso.
4. Filtra, ordina e combina le righe, poi restituisce il risultato.

Python include SQLite, quindi puoi usare un vero database proprio qui:

import sqlite3

db = sqlite3.connect(":memory:")   # a database that lives in RAM
db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, country TEXT, points INTEGER)")

rows = [("Ana", "BR", 120), ("Kenji", "JP", 340), ("Lea", "DE", 90), ("Omar", "EG", 340)]
db.executemany("INSERT INTO users (name, country, points) VALUES (?, ?, ?)", rows)

# Ask a question in SQL: who has more than 100 points, highest first?
query = "SELECT name, points FROM users WHERE points > 100 ORDER BY points DESC, name"
for name, points in db.execute(query):
    print(name, points)

total = db.execute("SELECT COUNT(*), AVG(points) FROM users").fetchone()
print("users:", total[0], "average points:", total[1])

Kenji 340
Omar 340
Ana 120
users: 4 average points: 222.5

Il programma non dice mai come cercare. Descrive il risultato che vuole, e il DBMS decide come ottenerlo. È questa la differenza principale tra interrogare un database e scrivere un ciclo su una lista.

Tipi di database

Tutto ciò che non è relazionale viene spesso raggruppato sotto il nome NoSQL. I database relazionali restano la scelta predefinita per la maggior parte delle applicazioni, perché tabelle, vincoli e SQL coprono bene un'ampia gamma di problemi. Molti prodotti combinano anche più tipi: PostgreSQL salva documenti JSON, e pgvector gli aggiunge la ricerca vettoriale.

Transazioni: tutto o niente

Spostare denaro da Ana a Ben richiede due modifiche: aggiungere a Ben, togliere ad Ana. Se il programma si blocca tra le due, compare denaro dal nulla. Una transazione raggruppa le modifiche in modo che vengano salvate tutte o nessuna. Qui la seconda modifica viola una regola (un saldo non può scendere sotto zero), quindi il database annulla anche la prima:

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER CHECK (balance >= 0))")
db.executemany("INSERT INTO accounts VALUES (?, ?)", [("ana", 50), ("ben", 0)])
db.commit()

def transfer(amount):
    try:
        with db:  # one transaction: commit on success, roll back on error
            db.execute("UPDATE accounts SET balance = balance + ? WHERE name = 'ben'", (amount,))
            db.execute("UPDATE accounts SET balance = balance - ? WHERE name = 'ana'", (amount,))
        print("transfer", amount, "done")
    except sqlite3.IntegrityError as err:
        print("transfer", amount, "failed:", err)
    print(dict(db.execute("SELECT name, balance FROM accounts")))

transfer(30)
transfer(40)

transfer 30 done
{'ana': 20, 'ben': 30}
transfer 40 failed: CHECK constraint failed: balance >= 0
{'ana': 20, 'ben': 30}

I 40 di Ben sono stati aggiunti e poi ripresi, quindi i totali tornano. I database relazionali garantiscono quattro proprietà per le transazioni, note come ACID: atomicità (tutto o niente), coerenza (regole come CHECK valgono sempre), isolamento (due utenti che eseguono transazioni nello stesso momento non vedono il lavoro a metà dell'altro) e durabilità (una volta salvata, una modifica sopravvive a un crash o a un'interruzione di corrente).

Database, foglio di calcolo o file

Un foglio di calcolo è un buon strumento per una persona che guarda qualche migliaio di righe. Quando più programmi o utenti scrivono dati nello stesso momento, o i dati devono rispettare delle regole, lo strumento giusto è un database.

Come i database proteggono i dati

Un DBMS scrive ogni modifica in un log prima di toccare i file dei dati (il write-ahead log), così dopo un crash può ripetere o annullare il lavoro non finito. PostgreSQL può salvare un checksum su ogni pagina da 8 KB e segnalare un errore di checksum quando un disco guasto restituisce dati danneggiati. Utenti e permessi decidono chi può leggere o modificare quali tabelle, e la replica mantiene copie su altri server. I programmi devono anche passare l'input degli utenti come parametri, come i segnaposto ? qui sopra, mai incollandolo nel testo SQL, che è il modo in cui avvengono gli attacchi di SQL injection.

Falsi miti comuni

• "SQL è un database." SQL è il linguaggio. PostgreSQL, MySQL e SQLite sono database che lo capiscono.
• "NoSQL significa niente SQL." Il termine di solito si legge come "not only SQL", non solo SQL, e diversi database NoSQL hanno linguaggi di query simili a SQL, come il CQL di Cassandra.
• "Un database è sempre un grande server." SQLite è un singolo file che un programma apre direttamente, senza alcun server. Gira dentro ogni telefono Android e iOS.
• "I dati in un database sono al sicuro per sempre." Un database protegge dai crash, non da qualcuno che esegue DELETE per sbaglio. I backup servono comunque.

Cosa leggere dopo

Il corso SQL insegna le query a partire dal primo SELECT. La documentazione di SQLite copre i passi successivi: cos'è SQLite, creare una tabella, gli indici e prevenire la SQL injection. Per vedere come sono disposti i dati sul disco, leggi cos'è un byte, e checksum spiega come vengono scoperte le pagine danneggiate.