[#_lesson_29_keeping_data] == Lezione 29: Conservare i dati image::learn/29-databases.png[La lezione finita, 480] La dodicesima lezione ha salvato un punteggio record: un numero, sotto un nome, ancora lì la mattina dopo. Per un punteggio record è esattamente la cosa giusta, ed è sbagliata per quasi tutto il resto che un gioco vuole conservare. L'elenco di tutte le partite che qualcuno abbia mai giocato. Il tempo migliore su ognuno di venti tracciati. Quaranta livelli che qualcuno ha progettato in un foglio di calcolo. La domanda «qual è il mio record nello stage difficile?», a cui un mucchio di numeri salvati non sa rispondere, a meno che la risposta non la scriva tu a mano. È a questo che serve un database, e Singe ne ha uno incorporato. Questa lezione prende il test di reazione della ventottesima lezione, gli dà degli stage che vengono distribuiti con il gioco, e gli fa conservare ogni prova giocata da chiunque e rispondere a domande su di esse. [#_what_a_database_is] === Che cos'è un database Un database è un file di *tabelle*. Una tabella è fatta di righe e colonne, come un foglio di calcolo con i nomi delle colonne decisi in anticipo: una tabella di prove potrebbe avere uno stage, un punteggio e un momento per ogni riga. Le righe ce le metti tu, e per tirarle fuori fai delle domande, e le domande si scrivono in *SQL*, un piccolo linguaggio che fa questo lavoro da prima della maggior parte dei linguaggi di programmazione di cui hai sentito parlare. Il database che Singe si porta dietro è *SQLite*. Non è un server da installare né un servizio a cui iscriversi. È una libreria, il database è un normale file, e lo stesso SQLite si trova nel tuo telefono, nel tuo browser e probabilmente nella tua auto. Per seguire questa lezione non devi conoscere SQL: ogni istruzione viene spiegata man mano che passa, e le tre che userai di più -- `CREATE TABLE`, `INSERT` e `SELECT` -- ci sono tutte. [#_opening_one] === Aprirne uno [source,lua] ---- local sqlite3 = require("sqlite3") local runs = sqlite3.open(singeGetDataPath() .. "runs.db") ---- `require("sqlite3")` carica una libreria che viene con Singe e te la restituisce come tabella, quindi tutto ciò che SQLite sa fare è `sqlite3.` qualcosa. `sqlite3.open` apre un file di database e lo crea se non c'è ancora, quindi la prima volta che avvii il gioco ne crea uno vuoto, e ogni volta successiva lo ritrova. Il file va in `singeGetDataPath()`, per lo stesso motivo per cui ci va il salvataggio della dodicesima lezione: la cartella del gioco può stare su un disco, su una cartella condivisa in rete o impacchettata in un unico file, e in nessuno di questi si può scrivere. Tutto ciò che un gioco scrive va nella sua cartella dei dati. [#_making_a_table] === Creare una tabella Un database vuoto non ha tabelle. Ne crei una descrivendola: [source,lua] ---- runs:exec([[ CREATE TABLE IF NOT EXISTS runs ( id INTEGER PRIMARY KEY, stage TEXT NOT NULL, score INTEGER NOT NULL, at INTEGER NOT NULL ) ]]) ---- `exec` esegue SQL che non restituisce niente. Le doppie parentesi quadre sono la stringa lunga di Lua: tutto quello che c'è in mezzo, a capo compresi, è un'unica stringa, ed è così che si tiene leggibile l'SQL. `CREATE TABLE` dà un nome alla tabella e alle sue colonne, ognuna con un tipo: `TEXT` per le parole e `INTEGER` per i numeri interi. `NOT NULL` vuol dire che ogni riga deve avere un valore lì. `INTEGER PRIMARY KEY` è speciale: SQLite lo riempie da solo, contando in avanti, così ogni riga riceve il suo numero senza che tu ci debba pensare. `IF NOT EXISTS` è ciò che rende sicuro eseguirlo ogni volta che il gioco parte. La prima volta crea la tabella. Tutte le volte successive la tabella c'è già e la riga non fa niente. Senza quelle tre parole, la seconda volta che avvii il gioco si fermerebbe con un errore che dice che la tabella esiste già. [#_putting_a_row_in] === Inserire una riga Potresti costruire l'SQL per una nuova riga incollando insieme delle stringhe: [source,lua] ---- runs:exec("INSERT INTO runs (stage, score, at) VALUES ('" .. stage .. "', " .. score .. ", " .. os.time() .. ")") ---- Non farlo. Funziona finché un nome non contiene un apostrofo, e a quel punto l'apice chiude la stringa troppo presto e l'istruzione non ha più senso. In un gioco che lascia scrivere i giocatori va anche peggio: qualunque cosa scrivano diventa parte del tuo SQL. Il modo giusto è scrivere l'istruzione una volta sola, con dei punti interrogativi dove vanno i valori, e consegnare i valori a parte: [source,lua] ---- local addRun = runs:prepare("INSERT INTO runs (stage, score, at) VALUES (?, ?, ?)") local function record(stage, points) addRun:bind_values(stage, points, os.time()) addRun:step() addRun:reset() end ---- `prepare` trasforma l'SQL in un'*istruzione* che si può usare più e più volte. `bind_values` mette un valore in ogni punto interrogativo, in ordine. Qualunque cosa contenga un valore, apostrofi compresi, resta un valore e non diventa mai parte dell'SQL. `step` esegue l'istruzione, e `reset` la prepara per essere eseguita di nuovo. `os.time()` è Lua puro: l'ora attuale come numero di secondi, che è il modo più facile per memorizzare un «quando». Preparare una volta e riusare l'istruzione è anche più veloce, perché SQLite capisce come eseguirla una volta sola. Per un gioco che scrive una riga al minuto non conta. Per un editor di livelli che ne scrive mille, sì. [#_asking_questions] === Fare domande Le righe tornano fuori con `nrows`, che esegue una `SELECT` e ti consegna ogni riga come una tabella con i nomi delle colonne come chiavi: [source,lua] ---- for row in runs:nrows("SELECT stage, score FROM runs ORDER BY score DESC LIMIT 5") do debugPrint(row.stage .. " " .. row.score) end ---- Leggi l'SQL da sinistra a destra. `SELECT stage, score` dice quali colonne vuoi. `FROM runs` dice da quale tabella. `ORDER BY score DESC` ordina le righe per punteggio, dal più alto, e `LIMIT 5` si ferma dopo cinque. Questa è una tabella dei punteggi record, e non ha mai avuto bisogno di codice di ordinamento scritto da te. Le domande possono essere più furbe di quelle che vorresti scrivere a mano: [source,lua] ---- for row in runs:nrows("SELECT stage, MAX(score) AS top, COUNT(*) AS tries FROM runs GROUP BY stage ORDER BY stage") do debugPrint(row.stage .. ": best " .. row.top .. " of " .. row.tries) end ---- `GROUP BY stage` raccoglie le righe in un gruppo per ogni stage, e `MAX(score)` e `COUNT(*)` vengono calcolati per ciascun gruppo: il punteggio migliore in quello stage e quante volte è stato giocato. `AS top` dà un nome alla risposta, ed è la chiave con cui la rileggi. Quando una domanda ha una sola risposta, `urows` restituisce direttamente le colonne invece di una tabella, il che è più ordinato per un conteggio: [source,lua] ---- for count in runs:urows("SELECT COUNT(*) FROM runs") do played = count end ---- [#_ask_when_it_changes] === Chiedi quando cambia Nessuna di queste domande va messa in `onOverlayUpdate`. Una query è veloce, ma sessanta al secondo, per avere risposte che cambiano una volta al minuto, vuol dire che il gioco fa la stessa domanda e riceve la stessa risposta cinquantanove volte su sessanta. Quindi il gioco chiede quando la risposta può essere cambiata -- una volta all'inizio, e di nuovo ogni volta che viene registrata una prova -- e tiene quello che torna indietro in normali tabelle: [source,lua] ---- local function refresh() best = {} for row in runs:nrows("SELECT stage, score FROM runs ORDER BY score DESC, id LIMIT 5") do best[#best + 1] = row end -- e lo stesso per le altre due domande end ---- `onOverlayUpdate` disegna a partire da `best`, il che non costa niente. `record` chiama `refresh` dopo ogni nuova riga, così la tabella sullo schermo non è mai superata. [#_data_that_ships_with_the_game] === Dati che vengono distribuiti con il gioco L'altra metà dei dati di un gioco è la parte che scrivi prima che qualcuno giochi: gli stage, i livelli, gli oggetti, le battute dei dialoghi. Anche quella può essere un database, creato con qualsiasi strumento per SQLite -- il programma `sqlite3` dalla riga di comando, o un programma con le finestre come DB Browser for SQLite -- e messo nella cartella del gioco accanto alla sua grafica. Il kit di grafica ne ha uno, `stages.db`, con un nome e l'attesa più breve e più lunga per ogni stage. Un gioco quello lo apre in modo diverso: [source,lua] ---- local stages = {} local shipped = sqlite3.open(DIR .. "art/stages.db", sqlite3.OPEN_READONLY) for row in shipped:nrows("SELECT name, waitMin, waitMax FROM stages ORDER BY id") do stages[#stages + 1] = row end shipped:close() ---- `DIR` è la cartella del tuo gioco, dove si trova la grafica. `sqlite3.OPEN_READONLY` dice che intendi solo leggerlo, e questo conta il giorno in cui distribuisci il gioco. Quando nella prossima lezione impacchetterai il gioco in un unico file, un database aperto in sola lettura viene letto direttamente da quel file, senza estrarre niente. Uno aperto in scrittura viene copiato, una volta, nella cartella dei dati del gioco, e da quel momento in poi modifichi la copia; il file che hai distribuito non viene mai scritto. Quindi tieni separati i due tipi. Quello che hai creato tu va nella tua cartella e si apre in sola lettura. Quello che crea il gioco va nella cartella dei dati. Qui gli stage vengono letti una volta all'inizio in una normale tabella e il database viene chiuso, perché per un elenco di quattro stage non vale la pena tenere aperto un file. [#_closing_up] === Chiudere tutto Le istruzioni e i database tengono occupata la memoria e aperto un file finché non dici che hai finito, e il posto ordinato per dirlo è `onShutdown`: [source,lua] ---- function onShutdown() addRun:finalize() runs:close() end ---- `finalize` è il `close` di un'istruzione. Un database non si chiude finché è ancora in giro un'istruzione creata da lui, quindi le istruzioni vanno per prime. [#_the_whole_script_4] === Lo script completo Crea una cartella chiamata `runs`, copiaci dentro la cartella `art` del libro per avere `stages.db`, e metti questo in `runs.singe`: [source,lua] ---- dofile("Singe/Framework.singe") local sqlite3 = require("sqlite3") local PERFECT = 1000 -- Quello che viene distribuito con il gioco: gli stage, letti una volta e chiusi. local stages = {} local shipped = sqlite3.open(DIR .. "art/stages.db", sqlite3.OPEN_READONLY) for row in shipped:nrows("SELECT name, waitMin, waitMax FROM stages ORDER BY id") do stages[#stages + 1] = row end shipped:close() -- Quello che scrive il gioco: ogni prova giocata da chiunque, tenuta nella cartella dei dati. local runs = sqlite3.open(singeGetDataPath() .. "runs.db") runs:exec([[ CREATE TABLE IF NOT EXISTS runs ( id INTEGER PRIMARY KEY, stage TEXT NOT NULL, score INTEGER NOT NULL, at INTEGER NOT NULL ) ]]) local addRun = runs:prepare("INSERT INTO runs (stage, score, at) VALUES (?, ?, ?)") local stageAt = 1 local state = "attract" local goAt = 0 local score = 0 local best = {} local perStage = {} local played = 0 -- Le domande, fatte quando si aggiunge una prova invece che a ogni fotogramma. local function refresh() best = {} for row in runs:nrows("SELECT stage, score FROM runs ORDER BY score DESC, id LIMIT 5") do best[#best + 1] = row end perStage = {} for row in runs:nrows("SELECT stage, MAX(score) AS top, COUNT(*) AS tries FROM runs GROUP BY stage ORDER BY stage") do perStage[#perStage + 1] = row end for count in runs:urows("SELECT COUNT(*) FROM runs") do played = count end end local function record(stage, points) addRun:bind_values(stage, points, os.time()) addRun:step() addRun:reset() refresh() end local function play() local stage = stages[stageAt] state = "waiting" goAt = singeGetTicks() + math.random(stage.waitMin, stage.waitMax) score = 0 end function onInputPressed(what) if what == SWITCH_BUTTON2 and state ~= "waiting" and state ~= "go" then stageAt = stageAt % #stages + 1 return end if what ~= SWITCH_BUTTON1 then return end if state == "waiting" then state = "result" score = 0 record(stages[stageAt].name, score) elseif state == "go" then score = math.max(0, PERFECT - (singeGetTicks() - goAt)) state = "result" record(stages[stageAt].name, score) else play() end end function onOverlayUpdate() if state == "waiting" and singeGetTicks() >= goAt then state = "go" end overlayClear() overlayPrint(2, 0, "REACTION TEST: " .. stages[stageAt].name) if state == "attract" then overlayPrint(2, 2, "Button 1 to play, button 2 for another stage.") elseif state == "waiting" then overlayPrint(2, 2, "Wait for it...") elseif state == "go" then overlayPrint(2, 2, "NOW!") else overlayPrint(2, 2, "You scored " .. score .. ". Button 1 plays again.") end overlayPrint(2, 4, "Runs kept: " .. played) overlayPrint(2, 6, "BEST RUNS") for place, row in ipairs(best) do overlayPrint(2, 6 + place, string.format("%d. %-8s %4d", place, row.stage, row.score)) end overlayPrint(24, 6, "BY STAGE") for line, row in ipairs(perStage) do overlayPrint(24, 6 + line, string.format("%-8s best %4d of %d", row.stage, row.top, row.tries)) end return OVERLAY_UPDATED end function onShutdown() addRun:finalize() runs:close() end refresh() ---- Avvialo con `Singe -R runs`. Il pulsante 1 fa partire una partita e la ferma, come nella ventottesima lezione, e il pulsante 2 tra una partita e l'altra passa allo stage successivo. Fanne qualcuna su ognuno. Poi esci e avvialo di nuovo: ogni prova è ancora lì, le cinque migliori a sinistra e il record di ogni stage a destra. [#_what_just_happened_24] === Cos'è appena successo [source,lua] ---- local runs = sqlite3.open(singeGetDataPath() .. "runs.db") ---- In gioco ci sono due file, e non sono uguali. `art/stages.db` è tuo: l'hai scritto tu, viene distribuito con il gioco, e il gioco lo legge soltanto. `runs.db` è del gioco: non esisteva finché il primo avvio non l'ha creato, vive nella cartella dei dati, e nessuno lo distribuisce. [source,lua] ---- if state == "waiting" then state = "result" score = 0 record(stages[stageAt].name, score) ---- Un pulsante premuto troppo presto registra comunque una prova, con zero punti. Il database è il registro di quello che è successo, e una falsa partenza è successa. Se mostrare o no quelle righe è una domanda per la `SELECT`, non qualcosa da nascondere non scrivendole mai. [source,lua] ---- for row in runs:nrows("SELECT stage, score FROM runs ORDER BY score DESC, id LIMIT 5") do ---- `ORDER BY score DESC, id` ordina per punteggio e, dove due punteggi sono uguali, per `id` -- l'ordine in cui sono state giocate. Senza la seconda chiave, due punteggi uguali tornano nell'ordine che fa più comodo a SQLite, che può cambiare da un avvio all'altro. Una tabella che si rimescola da sola senza un motivo visibile sembra rotta anche quando non lo è. [#_try_it_29] === Prova tu . *Mostra la data.* `os.date("%Y-%m-%d", row.at)` trasforma di nuovo un'ora memorizzata in qualcosa che una persona può leggere. Aggiungi la data a ognuna delle prove migliori. . *Ignora le false partenze.* Aggiungi `WHERE score > 0` alla domanda delle prove migliori, tra `FROM runs` e `ORDER BY`. Poi conta le false partenze in ogni stage con una domanda tutta tua. . *Aggiungi uno stage.* Apri `stages.db` in uno strumento per SQLite, aggiungi una riga e avvia il gioco. Nello script non cambia niente. È proprio questo il senso di tenere i dati fuori dallo script. . *Azzera lo storico.* `runs:exec("DELETE FROM runs")` svuota la tabella. Mettilo su un pulsante, chiama `refresh` subito dopo, e guarda tutto tornare a zero. È il «reset dei punteggi record» di un menu di servizio. . *Scrivi mille righe.* Ripeti `record` mille volte in un ciclo e misura il tempo con `singeGetTicks`. Poi metti `runs:exec("BEGIN")` prima del ciclo e `runs:exec("COMMIT")` dopo, e misuralo di nuovo. Una *transazione* fa di molte scritture una sola, e la differenza è abbastanza grande da valere la pena di ricordarsela. [#_break_it_on_purpose_28] === Rompilo apposta Togli `addRun:reset()` da `record` e gioca tre partite su tre stage diversi. Nessun errore. Il gioco funziona perfettamente. Ma la tabella a destra dice che hai giocato tre volte il primo stage, e le prove migliori sono tre copie del tuo primo punteggio. `bind_values` non poteva mettere nuovi valori in un'istruzione già eseguita e non azzerata, quindi ha rinunciato -- in silenzio, restituendo un numero che non stavi controllando -- e `step` ha eseguito di nuovo l'istruzione con i valori che aveva già. Questo è il bug che conta in questa lezione, perché non sembra un bug. Una query che fallisce fa rumore. Una scrittura che scrive la cosa sbagliata è silenziosa, e la scopri una settimana dopo nei dati. Quando le righe di un database non sono quelle che ti aspetti, guarda il codice che ce le ha messe prima del codice che le legge. Rimetti il `reset` e rompilo nel modo rumoroso: cambia `score` in `scroe` nella prima `SELECT` di `refresh`. ---- Error running script: no such column: scroe stack traceback: [C]: in method 'nrows' runs/runs.singe:42: in local 'refresh' runs/runs.singe:132: in main chunk ---- SQLite legge i nomi delle colonne quando prepara la domanda, quindi un nome scritto male ferma subito il gioco, e il messaggio lo indica con precisione. Il traceback si legge dall'interno verso l'esterno: si è lamentata `nrows`, l'ha chiamata `refresh` alla riga 42, e lo script stesso ha chiamato `refresh` alla riga 132, l'ultima, mentre il gioco partiva. Ecco perché dice *running script* invece di nominare una funzione chiamata dal motore: non è mai arrivato fino al primo fotogramma. [#_what_you_learned_29] === Cosa hai imparato * Un database è un file di tabelle; una tabella è fatta di righe con colonne che hanno un nome. * `require("sqlite3")` ti dà SQLite. `sqlite3.open` apre un file e lo crea se manca. * Quello che il gioco scrive va in `singeGetDataPath()`. Quello che distribuisci va nella tua cartella, aperto con `sqlite3.OPEN_READONLY`. * `CREATE TABLE IF NOT EXISTS` si può eseguire senza rischi ogni volta che il gioco parte. * I valori entrano attraverso `prepare`, `bind_values`, `step` e `reset`, mai incollando stringhe dentro l'SQL. * `nrows` restituisce le righe come tabelle; `urows` restituisce direttamente le colonne. * `ORDER BY`, `LIMIT`, `GROUP BY`, `MAX` e `COUNT` rispondono alle domande per cui altrimenti scriveresti dei cicli. * Chiedi quando la risposta può cambiare, non a ogni fotogramma. * In `onShutdown` si finalizzano le istruzioni, poi si chiude il database. [#_next_time_29] === La prossima volta Il tuo gioco ora ha una cartella piena di cose: uno script, la grafica, i suoni e un database o due. La trentesima lezione trasforma quella cartella in un unico file che uno sconosciuto può copiare sulla sua macchina e giocare -- e il tuo database in sola lettura va con lui, e si apre ancora esattamente come adesso.