Bataille navale dans PostgreSQL

Il y a un débat féroce parmi les programmeurs sur les dangers et les avantages des procédures stockées dans les bases de données. Aujourd'hui, nous allons nous en éloigner et refaire l'incroyable dans des conditions impossibles.



Aujourd'hui, les développeurs essaient d'éviter de créer une logique métier dans les bases de données autant que possible. Néanmoins, il existe des passionnés qui se lancent des défis et créent, par exemple, un matcher d'échange , et parfois des entreprises entières transfèrent le côté serveur vers des procédures stockées dans la base de données. Les auteurs de tels projets affirment que vous pouvez tout faire sur les bases de données si vous le souhaitez.



Ici, je me souviens involontairement de la "bataille navale" sur BGP . Est-il possible de faire ce jeu en SQL? Pour répondre à cette question, nous utiliserons les services PostgreSQL 12, ainsi que PLpgSQL. Pour ceux qui ont hâte de regarder "sous le capot", un lien vers le référentiel .



Le jeu de bataille navale nécessite une contribution constante de l'utilisateur tout au long du jeu. Le moyen le plus simple d'interagir avec un utilisateur de base de données est un client de ligne de commande.

Entrée de données

Obtenir les données de l'utilisateur est la tâche la plus difficile de ce projet. Le moyen le plus simple du point de vue du développement est de demander à l'utilisateur d'écrire des requêtes SQL correctes pour insérer les informations nécessaires dans une table spécialement préparée. Cette méthode est relativement lente et oblige l'utilisateur à répéter la demande encore et encore. Je voudrais pouvoir récupérer des données sans écrire une requête SQL.



PostgreSQL suggère d'utiliser COPY… FROM STDIN pour enregistrer les données d'une entrée standard dans une table. Mais cette solution présente deux inconvénients.



Premièrement, l'opérateur COPY ne peut pas être limité par la quantité d'informations téléchargées. L'instruction COPY se termine uniquement lorsqu'elle reçoit un signe de fin de fichier. Ainsi, l'utilisateur devra en outre entrer EOF pour indiquer la fin de la saisie des informations.



Deuxièmement, il n'y a pas de fichiers stdin et stdout dans les procédures et fonctions stockées. Les flux d'entrée et de sortie standard sont disponibles lors de l'exécution de requêtes SQL régulières via le client, mais les boucles n'y sont pas disponibles. Ainsi, vous ne pouvez pas exécuter le jeu en une seule commande SQL. Cela aurait pu être la fin de l'histoire, mais une solution astucieuse a été trouvée.



PostgreSQL a la capacité de se connectertoutes les demandes, y compris les demandes incorrectes. De plus, la journalisation peut être au format CSV et l'opérateur COPY peut travailler avec ce format. Configurons la journalisation dans le fichier de configuration postgresql.conf:

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

Le fichier postgresql.csv enregistrera désormais toutes les requêtes SQL exécutées dans PostgreSQL. La documentation, dans la section Utilisation de la sortie de journal au format CSV , décrit un moyen de charger les journaux csv avec la rotation activée. Nous sommes intéressés par le chargement des journaux avec un intervalle d'une seconde.



Comme il n'est pas pratique de faire pivoter les journaux toutes les secondes, nous chargerons le fichier journal encore et encore, en ajoutant à la table des journaux. Une solution simple d'un opérateur COPY ne fonctionnera que la première fois, puis affichera une erreur due à des conflits de clé primaire. Ce problème est résolu en utilisant une table intermédiaire et la clause ON CONFLICT DO NOTHING .





Vous pouvez également ajouter un filtre lors de la migration des données d'une table temporaire vers postgres_log, ce qui réduit la quantité d'informations inutiles dans la table du journal. Puisque nous ne prévoyons pas de recevoir des requêtes SQL correctes de l'utilisateur, nous pouvons nous limiter aux requêtes contenant un texte de requête et la balise de commande est inactive.



Malheureusement, PostgreSQL n'a pas de planificateur qui exécute une routine selon un calendrier. Puisque le problème se situe dans la partie "serveur" du jeu, il peut être résolu en écrivant un script shell qui appellera la procédure stockée pour charger les journaux toutes les secondes.



Toute chaîne saisie par l'utilisateur qui n'est pas une requête SQL valide apparaîtra désormais dans la table postgres_log. Bien que cette méthode nécessite le séparateur de point-virgule obligatoire, elle est beaucoup plus facile que l'envoi d'EOF.



Le lecteur attentif notera que lors de l'exécution d'une procédure ou d'une fonction stockée, le client en ligne de commande ne traitera pas les commandes et sera absolument correct. Pour qu'une telle solution fonctionne, deux clients sont nécessaires: un "écran" et un "clavier".



^{Client écran (gauche) et client clavier (droite)}

Pour "coupler" le clavier, l'écran génère une séquence pseudo-aléatoire de caractères qui doivent être saisis sur le clavier client. "Screen" identifie le clavier par l'identifiant unique de la session du client (session_id) et sélectionne ensuite dans la table du journal uniquement les lignes avec l'identifiant de session requis.



Il est facile de voir que la sortie du clavier client n'est pas utile et que l'entrée sur l'écran client est limitée à un seul appel de procédure. Pour faciliter l'utilisation, vous pouvez envoyer le "screen" en arrière-plan, et éteindre la sortie du "clavier":

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

Nous avons maintenant la possibilité d'entrer des informations à partir de l'entrée standard dans la base de données et d'utiliser des procédures stockées.

Boucle de jeu

^{La partie active du jeu}

Le jeu est conditionnellement divisé en les phases suivantes:

• interface du client écran avec le client clavier;
• créer un lobby ou se connecter à un lobby existant;
• placement des navires;
• la partie active du jeu.

Le jeu se compose de cinq tables:

• affichage visuel du terrain, deux tableaux;
• liste des navires et leur état, deux tableaux;
• liste des événements du jeu.

Lors de la création du lobby, le joueur A, le serveur, crée toutes les tables et les remplit avec les valeurs initiales. Pour permettre de jouer à plusieurs jeux en parallèle, toutes les tables du titre ont un identifiant de lobby à dix chiffres, qui est généré de manière pseudo-aléatoire au début de la partie.



Le développement de la logique de jeu est généralement très similaire au développement dans les langages de programmation traditionnels, et diffère principalement par la syntaxe et le manque de bibliothèque pour un formatage agréable. Pour la sortie, l'opérateur RAISE est utilisé, qui pour psql affiche un message avec un préfixe de niveau de journal. Vous ne pourrez pas vous débarrasser de lui, mais cela n'interfère pas avec le jeu.



Il y a aussi des différences de conception et elles font bouillir le cerveau.

Temps de validation

Toute la logique du jeu est lancée par l'écran client, c'est-à-dire qu'une procédure est exécutée du début à la fin. De plus, pour une transaction, si l'opérateur COMMIT n'est pas explicitement spécifié.



Cela signifie que les nouvelles tables et les nouvelles données dans les tables existantes ne changeront pas pour le deuxième joueur tant que la transaction ne sera pas terminée. De plus, lorsque vous travaillez avec le temps, il est important de se rappeler que la fonction now () renvoie l' heure actuelle au moment où la transaction a commencé .



Faire un commit n'est pas aussi simple qu'il y paraît. Ils ne sont autorisés que dans les procédures . Une tentative de validation d'une transaction dans une fonction entraînera une erreur, car elle opère dans une transaction externe à la fonction.

Lancer le jeu

^{Démarrer le jeu}

Nous ne recommandons pas d'exécuter un tel jeu dans un environnement réel. Heureusement, il est possible de déployer rapidement et facilement une base de données avec un jeu. Dans le référentiel, vous pouvez trouver un Dockerfile qui construira une image avec PostgreSQL 12.4 et la configuration nécessaire. Créez et exécutez l'image:

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

Connexion à la base de données dans l'image:

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

Notez que PostgreSQL dans le conteneur utilise la politique d'authentification de confiance, c'est-à-dire qu'il autorise toutes les connexions sans mot de passe. N'oubliez pas de débrancher le conteneur après avoir terminé tous les jeux!

Conclusion

L'utilisation d'outils spéciaux à d'autres fins entraîne souvent des commentaires négatifs de la part des professionnels. Cependant, résoudre des tâches insignifiantes mais intéressantes entraîne une réflexion latérale et vous permet d'explorer l'outil de différents points de vue à la recherche d'une solution appropriée.



Aujourd'hui, nous avons une fois de plus confirmé que vous pouvez écrire tout ce que vous voulez en SQL si vous le souhaitez. Néanmoins, nous vous recommandons d'utiliser les outils de production aux fins pour lesquelles ils ont été conçus et de faire du plaisir exclusivement comme de petits projets domestiques.