[#_lesson_29_keeping_data] == Lektion 29: Daten aufbewahren image::learn/29-databases.png[Die fertige Lektion, 480] Lektion zwölf hat einen Highscore gespeichert: eine Zahl, unter einem Namen, am nächsten Morgen noch da. Für einen Highscore ist das genau richtig, und für fast alles andere, was ein Spiel aufbewahren will, ist es falsch. Eine Liste jeder Runde, die irgendwer gespielt hat. Die beste Zeit auf jeder von zwanzig Strecken. Vierzig Level, die jemand in einer Tabellenkalkulation entworfen hat. Die Frage „Was ist mein Bestwert auf der schweren Stufe?“, die ein Haufen gespeicherter Zahlen nicht beantworten kann, ohne dass du die Antwort von Hand schreibst. Genau dafür ist eine Datenbank da, und Singe hat eine eingebaut. Diese Lektion nimmt den Reaktionstest aus Lektion achtundzwanzig, gibt ihm Stufen, die mit dem Spiel ausgeliefert werden, und lässt ihn jeden Durchgang aufbewahren, den irgendwer spielt, und Fragen dazu beantworten. [#_what_a_database_is] === Was eine Datenbank ist Eine Datenbank ist eine Datei voller *Tabellen*. Eine Tabelle besteht aus Zeilen und Spalten, wie eine Tabellenkalkulation, bei der die Spaltennamen vorher feststehen: Eine Tabelle mit Durchgängen könnte in jeder Zeile eine Stufe, eine Punktzahl und eine Zeit haben. Du legst Zeilen hinein und stellst Fragen, um Zeilen herauszubekommen, und die Fragen werden in *SQL* geschrieben, einer kleinen Sprache, die diese Arbeit schon gemacht hat, bevor es die meisten Programmiersprachen gab, von denen du je gehört hast. Die Datenbank, die Singe mitbringt, ist *SQLite*. Sie ist kein Server, den du installierst, und kein Dienst, bei dem du dich registrierst. Sie ist eine Bibliothek, die Datenbank ist eine gewöhnliche Datei, und dasselbe SQLite steckt in deinem Handy, in deinem Browser und wahrscheinlich in deinem Auto. Du musst kein SQL können, um dieser Lektion zu folgen; jede Anweisung darin wird erklärt, wenn sie vorbeikommt, und die drei, die du am häufigsten brauchen wirst -- `CREATE TABLE`, `INSERT` und `SELECT` -- sind alle dabei. [#_opening_one] === Eine öffnen [source,lua] ---- local sqlite3 = require("sqlite3") local runs = sqlite3.open(singeGetDataPath() .. "runs.db") ---- `require("sqlite3")` lädt eine Bibliothek, die mit Singe kommt, und gibt sie dir als Tabelle zurück, also ist alles, was SQLite kann, `sqlite3.` irgendwas. `sqlite3.open` öffnet eine Datenbankdatei und legt sie an, wenn es sie noch nicht gibt, also macht der erste Start deines Spiels eine leere, und jeder Start danach findet sie wieder. Die Datei kommt nach `singeGetDataPath()`, aus demselben Grund wie der Spielstand aus Lektion zwölf: Der eigene Ordner eines Spiels kann auf einer Disc liegen, auf einer Netzwerkfreigabe oder in eine einzige Datei gepackt sein, und in nichts davon kann man schreiben. Alles, was ein Spiel schreibt, kommt in seinen Datenordner. [#_making_a_table] === Eine Tabelle anlegen Eine leere Datenbank hat keine Tabellen. Du legst eine an, indem du sie beschreibst: [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` führt SQL aus, das nichts zurückgibt. Die doppelten eckigen Klammern sind Luas langer String: Alles zwischen ihnen, Zeilenumbrüche eingeschlossen, ist ein einziger String, und so hältst du SQL lesbar. `CREATE TABLE` nennt die Tabelle und ihre Spalten, jede mit einem Typ: `TEXT` für Wörter und `INTEGER` für ganze Zahlen. `NOT NULL` heißt, dass jede Zeile dort einen Wert haben muss. `INTEGER PRIMARY KEY` ist etwas Besonderes: SQLite füllt es selbst aus und zählt dabei hoch, also bekommt jede Zeile ihre eigene Nummer, ohne dass du darüber nachdenken musst. `IF NOT EXISTS` sorgt dafür, dass das bei jedem Spielstart gefahrlos laufen kann. Beim ersten Mal legt es die Tabelle an. Bei jedem Mal danach ist die Tabelle schon da, und die Zeile tut nichts. Ohne diese drei Wörter würde der zweite Start deines Spiels mit einem Fehler anhalten, der sagt, dass es die Tabelle schon gibt. [#_putting_a_row_in] === Eine Zeile hineinlegen Du könntest das SQL für eine neue Zeile bauen, indem du Strings zusammenklebst: [source,lua] ---- runs:exec("INSERT INTO runs (stage, score, at) VALUES ('" .. stage .. "', " .. score .. ", " .. os.time() .. ")") ---- Lass das. Es funktioniert, bis ein Name einen Apostroph enthält, und dann beendet dieses Anführungszeichen den String zu früh, und die Anweisung ist Unsinn. In einem Spiel, in dem Spieler etwas tippen dürfen, wird es schlimmer: Was immer sie tippen, wird Teil deines SQL. Der richtige Weg ist, die Anweisung einmal zu schreiben, mit Fragezeichen dort, wo die Werte hinkommen, und die Werte getrennt zu übergeben: [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` macht aus dem SQL eine *Anweisung*, die du immer wieder benutzen kannst. `bind_values` setzt der Reihe nach einen Wert in jedes Fragezeichen. Was immer ein Wert enthält, Apostrophe eingeschlossen, er ist ein Wert und nie Teil des SQL. `step` führt die Anweisung aus, und `reset` macht sie bereit, wieder zu laufen. `os.time()` ist gewöhnliches Lua: die aktuelle Zeit als Anzahl von Sekunden, und das ist der einfachste Weg, „wann“ zu speichern. Einmal vorbereiten und die Anweisung wiederverwenden ist außerdem schneller, weil SQLite nur einmal ausrechnet, wie es sie ausführt. Für ein Spiel, das eine Zeile pro Minute schreibt, spielt das keine Rolle. Für einen Level-Editor, der tausend schreibt, schon. [#_asking_questions] === Fragen stellen Zeilen kommen mit `nrows` wieder heraus, das ein `SELECT` ausführt und dir jede Zeile als Tabelle gibt, mit den Spaltennamen als Schlüsseln: [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 ---- Lies das SQL von links nach rechts. `SELECT stage, score` sagt, welche Spalten du willst. `FROM runs` sagt, welche Tabelle. `ORDER BY score DESC` sortiert die Zeilen nach Punktzahl, die höchste zuerst, und `LIMIT 5` hört nach fünf auf. Das ist eine Highscore-Tabelle, und sie hat nie Sortiercode von dir gebraucht. Die Fragen können klüger sein als die, die du von Hand schreiben wollen würdest: [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` fasst die Zeilen zu einer Gruppe pro Stufe zusammen, und `MAX(score)` und `COUNT(*)` werden für jede Gruppe ausgerechnet: die beste Punktzahl auf dieser Stufe und wie oft sie gespielt wurde. `AS top` gibt der Antwort einen Namen, und das ist der Schlüssel, mit dem du sie wieder ausliest. Wenn eine Frage nur eine Antwort hat, gibt `urows` die Spalten selbst zurück statt einer Tabelle, und das ist für eine Anzahl ordentlicher: [source,lua] ---- for count in runs:urows("SELECT COUNT(*) FROM runs") do played = count end ---- [#_ask_when_it_changes] === Frag, wenn es sich ändert Keine dieser Fragen gehört in `onOverlayUpdate`. Eine Abfrage ist schnell, aber sechzig davon pro Sekunde, für Antworten, die sich einmal pro Minute ändern, heißt, dass das Spiel neunundfünfzig von sechzig Malen dieselbe Frage stellt und dieselbe Antwort bekommt. Also fragt das Spiel dann, wenn sich die Antwort geändert haben kann -- einmal beim Start und dann jedes Mal, wenn ein Durchgang aufgezeichnet wird -- und behält, was zurückkam, in gewöhnlichen Tabellen: [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 -- und dasselbe für die anderen beiden Fragen end ---- `onOverlayUpdate` zeichnet aus `best`, und das kostet nichts. `record` ruft nach jeder neuen Zeile `refresh` auf, also ist die Tabelle auf dem Bildschirm nie veraltet. [#_data_that_ships_with_the_game] === Daten, die mit dem Spiel ausgeliefert werden Die andere Hälfte der Daten eines Spiels ist der Teil, den du schreibst, bevor irgendwer spielt: die Stufen, die Level, die Gegenstände, die Dialogzeilen. Das kann auch eine Datenbank sein, gemacht mit irgendeinem SQLite-Werkzeug -- dem Programm `sqlite3` auf der Befehlszeile oder einem Programm mit Fenstern wie DB Browser for SQLite -- und in den Ordner deines Spiels neben seine Grafiken gelegt. Das Grafik-Kit hat eine, `stages.db`, mit einem Namen und der kürzesten und längsten Wartezeit für jede Stufe. Ein Spiel öffnet diese anders: [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` ist der Ordner deines Spiels, in dem die Grafiken liegen. `sqlite3.OPEN_READONLY` sagt, dass du nur lesen willst, und das zählt an dem Tag, an dem du auslieferst. Wenn du dein Spiel in der nächsten Lektion in eine einzige Datei packst, wird eine schreibgeschützt geöffnete Datenbank direkt aus dieser Datei gelesen, ohne dass irgendetwas entpackt wird. Eine, die zum Schreiben geöffnet wird, wird einmal in den Datenordner des Spiels kopiert, und von da an änderst du die Kopie; die Datei, die du ausgeliefert hast, wird nie beschrieben. Halte die beiden Arten also auseinander. Was du gemacht hast, kommt in deinen Ordner und wird schreibgeschützt geöffnet. Was das Spiel macht, kommt in den Datenordner. Die Stufen werden hier einmal beim Start in eine gewöhnliche Tabelle gelesen, und die Datenbank wird geschlossen, denn für eine Liste mit vier Stufen lohnt es sich nicht, eine Datei offen zu halten. [#_closing_up] === Zum Schluss schließen Anweisungen und Datenbanken belegen Speicher und halten eine Datei offen, bis du sagst, dass du fertig bist, und der ordentliche Ort, das zu sagen, ist `onShutdown`: [source,lua] ---- function onShutdown() addRun:finalize() runs:close() end ---- `finalize` ist `close` für eine Anweisung. Eine Datenbank lässt sich nicht schließen, solange eine Anweisung, die aus ihr gemacht wurde, noch da ist, also kommen die Anweisungen zuerst dran. [#_the_whole_script_4] === Das ganze Skript Leg einen Ordner namens `runs` an, kopier den Ordner `art` des Buchs hinein, wegen `stages.db`, und schreib das hier in `runs.singe`: [source,lua] ---- dofile("Singe/Framework.singe") local sqlite3 = require("sqlite3") local PERFECT = 1000 -- Was mit dem Spiel ausgeliefert wird: die Stufen, einmal gelesen und geschlossen. 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() -- Was das Spiel schreibt: jeder Durchgang, den irgendwer spielt, aufbewahrt im Datenordner. 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 -- Die Fragen, gestellt, wenn ein Durchgang dazukommt, statt in jedem Frame. 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() ---- Starte es mit `Singe -R runs`. Knopf 1 startet eine Runde und hält sie an, wie in Lektion achtundzwanzig, und Knopf 2 wechselt zwischen den Runden zur nächsten Stufe. Spiel auf jeder ein paar. Dann beende es und starte es noch einmal: Jeder Durchgang ist noch da, die besten fünf links und der Bestwert jeder Stufe rechts. [#_what_just_happened_24] === Was gerade passiert ist [source,lua] ---- local runs = sqlite3.open(singeGetDataPath() .. "runs.db") ---- Hier sind zwei Dateien im Spiel, und sie sind nicht gleich. `art/stages.db` gehört dir: Du hast sie geschrieben, sie wird mit dem Spiel ausgeliefert, und das Spiel liest sie nur. `runs.db` gehört dem Spiel: Es gab sie nicht, bis der erste Start sie angelegt hat, sie wohnt im Datenordner, und niemand liefert sie aus. [source,lua] ---- if state == "waiting" then state = "result" score = 0 record(stages[stageAt].name, score) ---- Ein zu früh gedrückter Knopf zeichnet trotzdem einen Durchgang auf, mit einer Punktzahl von nichts. Die Datenbank ist eine Aufzeichnung dessen, was passiert ist, und ein Frühstart ist passiert. Ob man diese Zeilen zeigt, ist eine Frage für das `SELECT`, nicht etwas, das man versteckt, indem man sie nie schreibt. [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` sortiert nach Punktzahl und, wo zwei Punktzahlen gleich sind, nach `id` -- der Reihenfolge, in der sie gespielt wurden. Ohne den zweiten Schlüssel kommen zwei gleiche Punktzahlen in der Reihenfolge zurück, die SQLite gerade bequem ist, und die kann sich von einem Start zum nächsten ändern. Eine Tabelle, die sich ohne sichtbaren Grund neu mischt, sieht kaputt aus, auch wenn sie es nicht ist. [#_try_it_29] === Probier es aus . *Zeig das Datum.* `os.date("%Y-%m-%d", row.at)` macht aus einer gespeicherten Zeit wieder etwas, das ein Mensch lesen kann. Füg jedem der besten Durchgänge das Datum hinzu. . *Ignorier die Frühstarts.* Füg `WHERE score > 0` in die Frage nach den besten Durchgängen ein, zwischen `FROM runs` und `ORDER BY`. Dann zähl die Frühstarts auf jeder Stufe mit einer eigenen Frage. . *Füg eine Stufe hinzu.* Öffne `stages.db` in einem SQLite-Werkzeug, füg eine Zeile hinzu und starte das Spiel. Am Skript ändert sich nichts. Genau darum hält man Daten aus dem Skript heraus. . *Lösch den Verlauf.* `runs:exec("DELETE FROM runs")` leert die Tabelle. Leg es auf einen Knopf, ruf danach `refresh` auf und sieh zu, wie alles auf nichts geht. Das ist das „Highscores zurücksetzen“ in einem Service-Menü. . *Schreib tausend Zeilen.* Ruf `record` in einer Schleife tausendmal auf und miss die Zeit mit `singeGetTicks`. Dann setz `runs:exec("BEGIN")` vor die Schleife und `runs:exec("COMMIT")` dahinter und miss noch einmal. Eine *Transaktion* macht aus vielen Schreibvorgängen einen, und der Unterschied ist groß genug, dass es sich lohnt, ihn zu behalten. [#_break_it_on_purpose_28] === Mach es absichtlich kaputt Nimm `addRun:reset()` aus `record` heraus und spiel drei Runden auf drei verschiedenen Stufen. Kein Fehler. Das Spiel läuft einwandfrei. Aber die Tabelle rechts sagt, dass du die erste Stufe dreimal gespielt hast, und die besten Durchgänge sind drei Kopien deiner ersten Punktzahl. `bind_values` konnte keine neuen Werte in eine Anweisung setzen, die schon gelaufen und nicht zurückgesetzt worden war, also hat es aufgegeben -- leise, mit einer Zahl als Rückgabe, die du nicht geprüft hast -- und `step` hat die Anweisung noch einmal mit den Werten ausgeführt, die sie schon hatte. Das ist der Fehler, auf den es in dieser Lektion ankommt, weil er nicht wie einer aussieht. Eine Abfrage, die scheitert, ist laut. Ein Schreibvorgang, der das Falsche schreibt, ist still, und du findest ihn eine Woche später in den Daten. Wenn die Zeilen in einer Datenbank nicht das sind, was du erwartest, sieh dir den Code an, der sie hineingelegt hat, bevor du dir den Code ansiehst, der sie liest. Setz `reset` wieder ein und mach es auf die laute Art kaputt: Ändere `score` im ersten `SELECT` in `refresh` in `scroe`. ---- 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 liest die Spaltennamen, wenn es die Frage vorbereitet, also hält ein falsch geschriebener das Spiel sofort an, und die Meldung nennt ihn genau. Den Traceback liest man von innen nach außen: `nrows` hat sich beschwert, `refresh` hat es in Zeile 42 aufgerufen, und das Skript selbst hat `refresh` in Zeile 132 aufgerufen, der letzten Zeile, als das Spiel startete. Darum steht da *running script*, statt dass eine Funktion genannt wird, die die Engine aufgerufen hat: Das Spiel ist nie bis zum ersten Frame gekommen. [#_what_you_learned_29] === Was du gelernt hast * Eine Datenbank ist eine Datei voller Tabellen; eine Tabelle besteht aus Zeilen mit benannten Spalten. * `require("sqlite3")` gibt dir SQLite. `sqlite3.open` öffnet eine Datei und legt sie an, wenn sie fehlt. * Was das Spiel schreibt, kommt nach `singeGetDataPath()`. Was du auslieferst, kommt in deinen Ordner und wird mit `sqlite3.OPEN_READONLY` geöffnet. * `CREATE TABLE IF NOT EXISTS` kann gefahrlos bei jedem Spielstart laufen. * Werte kommen über `prepare`, `bind_values`, `step` und `reset` hinein, nie, indem man Strings ins SQL klebt. * `nrows` gibt Zeilen als Tabellen zurück; `urows` gibt die Spalten selbst zurück. * `ORDER BY`, `LIMIT`, `GROUP BY`, `MAX` und `COUNT` beantworten die Fragen, für die du sonst Schleifen schreiben würdest. * Frag, wenn sich die Antwort ändern kann, nicht in jedem Frame. * In `onShutdown` werden erst die Anweisungen finalisiert, dann wird die Datenbank geschlossen. [#_next_time_29] === Beim nächsten Mal Dein Spiel hat jetzt einen Ordner voller Dinge: ein Skript, Grafiken, Sounds und eine oder zwei Datenbanken. Lektion dreißig macht aus diesem Ordner eine einzige Datei, die ein Fremder auf seinen eigenen Rechner kopieren und spielen kann -- und deine schreibgeschützte Datenbank geht mit und öffnet sich noch genau so wie jetzt.