Seeschlacht in PostgreSQL

Programmierer haben eine heftige Debatte über die Gefahren und Vorteile gespeicherter Prozeduren in Datenbanken. Heute werden wir von ihnen abschweifen und wieder das Unglaubliche unter unmöglichen Bedingungen tun.



Heutzutage versuchen Entwickler zu vermeiden, Geschäftslogik in Datenbanken zu erstellen, wann immer dies möglich ist. Trotzdem gibt es Enthusiasten, die sich selbst herausfordern und beispielsweise einen Exchange Matcher erstellen, und manchmal übertragen ganze Unternehmen die Serverseite auf gespeicherte Datenbankprozeduren. Die Autoren solcher Projekte argumentieren, dass Sie alles in Datenbanken tun können, wenn Sie wollen.



Hier erinnere ich mich unwillkürlich an die "Seeschlacht" um BGP . Ist es möglich, dieses Spiel in SQL zu machen? Um diese Frage zu beantworten, verwenden wir PostgreSQL 12-Dienste sowie PLpgSQL. Für diejenigen, die es kaum erwarten können, "unter die Haube" zu schauen, ein Link zum Repository .



Das Seeschlachtspiel erfordert während des gesamten Spiels ständige Eingaben des Benutzers. Der einfachste Weg, mit einem Datenbankbenutzer zu interagieren, ist ein Befehlszeilenclient.

Dateneingabe

Das Abrufen von Daten vom Benutzer ist die schwierigste Aufgabe in diesem Projekt. Aus entwicklungspolitischer Sicht ist es am einfachsten, den Benutzer zu bitten, korrekte SQL-Abfragen zu schreiben, um die erforderlichen Informationen in eine speziell vorbereitete Tabelle einzufügen. Diese Methode ist relativ langsam und erfordert, dass der Benutzer die Anforderung immer wieder wiederholt. Ich möchte Daten abrufen können, ohne eine SQL-Abfrage zu schreiben.



PostgreSQL schlägt vor, COPY… FROM STDIN zu verwenden, um Daten von der Standardeingabe in eine Tabelle zu speichern. Diese Lösung hat jedoch zwei Nachteile.



Erstens kann der COPY-Operator nicht durch die Menge der hochgeladenen Informationen eingeschränkt werden. Die COPY-Anweisung wird nur beendet, wenn sie ein Dateiende-Zeichen erhält. Somit muss der Benutzer zusätzlich EOF eingeben, um den Abschluss der Informationseingabe anzuzeigen.



Zweitens gibt es in gespeicherten Prozeduren und Funktionen keine stdin- und stdout-Dateien. Standard-Eingabe- und Ausgabestreams sind verfügbar, wenn reguläre SQL-Abfragen über den Client ausgeführt werden. Dort sind jedoch keine Schleifen verfügbar. Daher können Sie das Spiel nicht in einem SQL-Befehl ausführen. Dies hätte das Ende der Geschichte sein können, aber es wurde eine listige Lösung gefunden.



PostgreSQL kann protokollierenalle Anfragen, auch falsche. Darüber hinaus kann die Protokollierung im CSV-Format erfolgen, und der COPY-Operator kann mit diesem Format arbeiten. Konfigurieren Sie die Protokollierung in der Konfigurationsdatei postgresql.conf:

log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql.log'
log_min_error_statement = error
log_statement = 'all'

Die Datei postgresql.csv zeichnet jetzt alle SQL-Abfragen auf, die in PostgreSQL ausgeführt werden. In der Dokumentation im Abschnitt Verwenden der Protokollausgabe im CSV-Format wird eine Möglichkeit zum Laden von CSV -Protokollen mit aktivierter Rotation beschrieben. Wir sind daran interessiert, Protokolle im Abstand von einer Sekunde zu laden.



Da es unpraktisch ist, die Protokolle jede Sekunde zu drehen, laden wir die Protokolldatei immer wieder und fügen sie der Tabelle mit Protokollen hinzu. Eine einfache Lösung von einem COPY-Operator funktioniert nur beim ersten Mal und zeigt dann einen Fehler aufgrund von Primärschlüsselkonflikten an. Dieses Problem wird mithilfe einer Staging-Tabelle und der ON CONFLICT DO NOTHING-Klausel gelöst .





Sie können auch einen Filter hinzufügen, wenn Sie Daten aus einer temporären Tabelle nach postgres_log migrieren, um die Menge unnötiger Informationen in der Protokolltabelle zu verringern. Da wir nicht vorhaben, korrekte SQL-Abfragen vom Benutzer zu erhalten, können wir uns auf Abfragen beschränken, bei denen ein Abfragetext vorhanden ist und das Befehlstag nicht verwendet wird.



Leider verfügt PostgreSQL nicht über einen Scheduler, der eine Routine nach einem Zeitplan ausführt. Da sich das Problem im "Server" -Teil des Spiels befindet, kann es durch Schreiben eines Shell-Skripts gelöst werden, das die gespeicherte Prozedur zum Laden von Protokollen jede Sekunde aufruft.



Alle vom Benutzer eingegebenen Zeichenfolgen, die keine gültige SQL-Abfrage sind, werden jetzt in der Tabelle postgres_log angezeigt. Obwohl für diese Methode das obligatorische Semikolon-Trennzeichen erforderlich ist, ist es viel einfacher als das Senden von EOF.



Der aufmerksame Leser wird feststellen, dass der Befehlszeilenclient während der Ausführung einer gespeicherten Prozedur oder Funktion keine Befehle verarbeitet und absolut korrekt ist. Damit diese Lösung funktioniert, sind zwei Clients erforderlich: ein "Bildschirm" und eine "Tastatur".



^{Bildschirm-Client (links) und Tastatur-Client (rechts)}

Um die Tastatur zu "koppeln", generiert der Bildschirm eine pseudozufällige Folge von Zeichen, die auf der Client-Tastatur eingegeben werden müssen. "Bildschirm" identifiziert die Tastatur anhand der eindeutigen Kennung der Sitzung des Clients (Sitzungs-ID) und wählt dann nur Zeilen mit der erforderlichen Sitzungskennung aus der Protokolltabelle aus.



Es ist leicht zu erkennen, dass die Client-Tastatur-Ausgabe nicht nützlich ist und die Eingabe in den Client-Bildschirm auf einen einzelnen Prozeduraufruf beschränkt ist. Zur Vereinfachung der Verwendung können Sie den "Bildschirm" in den Hintergrund senden und die Ausgabe der "Tastatur" löschen:

psql <<<'select keyboard_init()' & psql >/dev/null 2>&1

Wir haben jetzt die Möglichkeit, Informationen aus Standardeingaben in die Datenbank einzugeben und gespeicherte Prozeduren zu verwenden.

Spielschleife

^{Der aktive Teil des Spiels}

Das Spiel ist bedingt in folgende Phasen unterteilt:

• Schnittstelle des Bildschirmclients mit dem Tastaturclient;
• Erstellen einer Lobby oder Herstellen einer Verbindung zu einer vorhandenen;
• Platzierung von Schiffen;
• der aktive Teil des Spiels.

Das Spiel besteht aus fünf Tischen:

• visuelle Anzeige des Feldes, zwei Tabellen;
• Liste der Schiffe und ihres Zustands, zwei Tabellen;
• Liste der Ereignisse im Spiel.

Während der Lobbyerstellung erstellt Player A, der Server, alle Tabellen und füllt sie mit Anfangswerten. Um mehrere Spiele gleichzeitig spielen zu können, haben alle Tabellen im Titel eine zehnstellige Lobby-ID, die zu Beginn des Spiels pseudozufällig generiert wird.



Die Entwicklung der Spielelogik ist im Allgemeinen der Entwicklung in traditionellen Programmiersprachen sehr ähnlich und unterscheidet sich hauptsächlich in der Syntax und dem Fehlen einer Bibliothek für eine gute Formatierung. Für die Ausgabe wird der RAISE-Operator verwendet, der für psql eine Nachricht mit einem Präfix auf Protokollebene anzeigt. Sie werden ihn nicht loswerden können, aber das stört das Spiel nicht.



Es gibt auch Designunterschiede, die das Gehirn zum Kochen bringen.

Zeit festschreiben

Die gesamte Logik des Spiels wird vom Client-Bildschirm gestartet, dh eine Prozedur wird von Anfang bis Ende ausgeführt. Darüber hinaus für eine Transaktion, wenn der COMMIT-Operator nicht explizit angegeben ist.



Dies bedeutet, dass sich neue Tabellen und neue Daten in vorhandenen Tabellen für den zweiten Spieler erst ändern, wenn die Transaktion abgeschlossen ist. Wenn Sie mit der Zeit arbeiten, ist es außerdem wichtig zu beachten, dass die Funktion now () die aktuelle Zeit zum Zeitpunkt des Starts der Transaktion zurückgibt .



Ein Commit zu machen ist nicht so einfach, wie es sich anhört. Sie sind nur in Verfahren erlaubt . Der Versuch, eine Transaktion in einer Funktion festzuschreiben, führt zu einem Fehler, da sie innerhalb einer Transaktion außerhalb der Funktion ausgeführt wird.

Das Spiel ausführen

^{Starten des Spiels}

Wir empfehlen, ein solches Spiel nicht in einer realen Umgebung auszuführen. Glücklicherweise ist es möglich, eine Datenbank mit einem Spiel schnell und einfach bereitzustellen. Im Repository finden Sie eine Docker-Datei, die ein Image mit PostgreSQL 12.4 und der erforderlichen Konfiguration erstellt. Erstellen Sie das Image und führen Sie es aus:

docker build -t sql-battleships .
docker run -p 5432:5432 sql-battleships

Herstellen einer Verbindung zur Datenbank im Bild:

psql -U postgres <<<'call screen_loop()' & psql -U postgres

Beachten Sie, dass PostgreSQL im Container die Vertrauensauthentifizierungsrichtlinie verwendet, dh alle Verbindungen ohne Kennwort zulässt. Vergiss nicht, den Container nach Abschluss aller Spiele vom Stromnetz zu trennen!

Fazit

Die Verwendung von Spezialwerkzeugen für andere Zwecke führt häufig zu negativen Rückmeldungen von Fachleuten. Das Lösen bedeutungsloser, aber interessanter Aufgaben trainiert jedoch das Querdenken und ermöglicht es Ihnen, das Werkzeug aus verschiedenen Blickwinkeln auf der Suche nach einer geeigneten Lösung zu erkunden.



Heute haben wir erneut bestätigt, dass Sie alles, was Sie wollen, in SQL schreiben können, wenn Sie wollen. Trotzdem empfehlen wir, die Werkzeuge in der Produktion für den vorgesehenen Zweck zu verwenden und ausschließlich Spaß als kleine Heimprojekte zu machen.