Los programadores mantienen un intenso debate sobre los peligros y beneficios de los procedimientos almacenados en las bases de datos. Hoy nos apartaremos de ellos y volveremos a hacer lo increíble en condiciones imposibles.
Hoy en día, los desarrolladores intentan evitar la creación de lógica empresarial en bases de datos siempre que sea posible. Sin embargo, hay entusiastas que se desafían a sí mismos y crean, por ejemplo, un comparador de intercambio y, a veces, empresas enteras transfieren el lado del servidor a los procedimientos almacenados de la base de datos. Los autores de tales proyectos argumentan que puede hacer cualquier cosa en las bases de datos si lo desea.
Aquí recuerdo involuntariamente la "batalla naval" sobre BGP . ¿Es posible hacer este juego en SQL? Para responder a esta pregunta, usaremos los servicios PostgreSQL 12, así como PLpgSQL. Para aquellos que no pueden esperar para mirar "bajo el capó", un enlace al repositorio .
El juego de batalla naval requiere una participación constante del usuario durante todo el juego. La forma más sencilla de interactuar con un usuario de una base de datos es un cliente de línea de comandos.
Entrada de datos
Obtener datos del usuario es la tarea más difícil de este proyecto. La forma más fácil desde el punto de vista del desarrollo es pedirle al usuario que escriba consultas SQL correctas para insertar la información necesaria en una tabla especialmente preparada. Este método es relativamente lento y requiere que el usuario repita la solicitud una y otra vez. Me gustaría poder recuperar datos sin escribir una consulta SQL.
PostgreSQL sugiere usar COPY ... FROM STDIN para guardar datos de la entrada estándar en una tabla. Pero esta solución tiene dos inconvenientes.
Primero, el operador COPY no puede estar limitado por la cantidad de información cargada. La instrucción COPY termina solo cuando recibe un signo de fin de archivo. Por lo tanto, el usuario adicionalmente tendrá que ingresar EOF para indicar la finalización de la entrada de información.
En segundo lugar, no hay archivos stdin y stdout en los procedimientos y funciones almacenados. Los flujos de entrada y salida estándar están disponibles cuando se ejecutan consultas SQL regulares a través del cliente, pero los bucles no están disponibles allí. Por lo tanto, no puede ejecutar el juego en un comando SQL. Este podría haber sido el final de la historia, pero se encontró una solución astuta.
PostgreSQL tiene la capacidad de registrartodas las solicitudes, incluidas las incorrectas. Además, el registro puede estar en formato CSV y el operador COPY puede trabajar con este formato. Configuremos el registro en el archivo de configuración postgresql.conf:
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql.log'
log_min_error_statement = error
log_statement = 'all'El archivo postgresql.csv ahora registrará todas las consultas SQL que se ejecuten en PostgreSQL. La documentación, en la sección Usar salida de registro en formato CSV , describe una forma de cargar registros csv con la rotación habilitada. Estamos interesados en cargar registros con un intervalo de un segundo.
Dado que no es práctico rotar los registros cada segundo, cargaremos el archivo de registro una y otra vez, agregando a la tabla con los registros. Una solución sencilla de un operador de COPY funcionará solo la primera vez y luego mostrará un error debido a conflictos de claves primarias. Este problema se resuelve utilizando una tabla de preparación y la cláusula EN CONFLICTO NO HACER NADA .
Cargar registros en una mesa
CREATE TEMP TABLE tmp_table ON COMMIT DROP
AS SELECT * FROM postgres_log WITH NO DATA;
COPY tmp_table FROM '/var/lib/postgresql/data/pg_log/postgresql.csv' WITH csv;
INSERT INTO postgres_log
SELECT * FROM tmp_table WHERE query is not null AND command_tag = 'idle' ON CONFLICT DO NOTHING;También puede agregar un filtro al migrar datos de una tabla temporal a postgres_log, reduciendo la cantidad de información innecesaria en la tabla de registro. Dado que no planeamos recibir consultas SQL correctas del usuario, podemos restringirnos a consultas donde hay un texto de consulta y la etiqueta de comando está inactiva.
Desafortunadamente, PostgreSQL no tiene un programador que ejecute una rutina en un horario. Dado que el problema está en la parte del "servidor" del juego, se puede resolver escribiendo un script de shell que llamará al procedimiento almacenado para cargar registros cada segundo.
Cualquier cadena ingresada por el usuario que no sea una consulta SQL válida ahora aparecerá en la tabla postgres_log. Aunque este método requiere un separador de punto y coma obligatorio, es mucho más fácil que enviar EOF.
El lector atento notará que durante la ejecución de un procedimiento o función almacenada, el cliente de línea de comando no procesará comandos y será absolutamente correcto. Para que esta solución funcione, se requieren dos clientes: una "pantalla" y un "teclado".
Para "emparejar" el teclado, la pantalla genera una secuencia pseudoaleatoria de caracteres que se deben ingresar en el teclado del cliente. La "pantalla" identifica el teclado por el identificador único de la sesión del cliente (session_id) y luego selecciona de la tabla de registro solo las filas con el identificador de sesión requerido.
Es fácil ver que la salida del teclado del cliente no es útil y que la entrada a la pantalla del cliente se limita a una única llamada a procedimiento. Para facilitar su uso, puede enviar la "pantalla" al fondo y apagar la salida del "teclado":
psql <<<'select keyboard_init()' & psql >/dev/null 2>&1Ahora tenemos la capacidad de ingresar información de la entrada estándar en la base de datos y utilizar procedimientos almacenados.
Bucle de juego
El juego se divide condicionalmente en las siguientes fases:
- interfaz del cliente de pantalla con el cliente de teclado;
- crear un vestíbulo o conectarse a uno existente;
- colocación de barcos;
- la parte activa del juego.
El juego consta de cinco mesas:
- exhibición visual del campo, dos tablas;
- lista de buques y su estado, dos tablas;
- lista de eventos en el juego.
Durante la creación del lobby, el jugador A, el servidor, crea todas las mesas y las llena con valores iniciales. Para que sea posible jugar varios juegos en paralelo, todas las mesas del título tienen un identificador de lobby de diez dígitos, que se genera de forma pseudoaleatoria al comienzo del juego.
El desarrollo de la lógica del juego es generalmente muy similar al desarrollo en los lenguajes de programación tradicionales y se diferencia principalmente en la sintaxis y la falta de una biblioteca para un buen formato. Para la salida, se utiliza el operador RAISE, que para psql muestra un mensaje con un prefijo de nivel de registro. No podrás deshacerte de él, pero esto no interfiere con el juego.
También hay diferencias de diseño y hacen que el cerebro hierva.
Comprometer tiempo
Toda la lógica del juego es lanzada por la pantalla del cliente, es decir, se ejecuta un procedimiento de principio a fin. Además, para una transacción, si el operador COMMIT no se especifica explícitamente.
Esto significa que las nuevas tablas y los nuevos datos en las tablas existentes no cambiarán para el segundo jugador hasta que se complete la transacción. Además, cuando se trabaja con tiempo, es importante recordar que la función ahora () devuelve la hora actual en el momento en que se inició la transacción .
Hacer un compromiso no es tan fácil como parece. Solo se permiten en procedimientos . Un intento de confirmar una transacción en una función resultará en un error, ya que opera dentro de una transacción externa a la función.
Ejecutando el juego
No recomendamos ejecutar un juego de este tipo en un entorno real. Afortunadamente, es posible implementar rápida y fácilmente una base de datos con un juego. En el repositorio, puede encontrar un Dockerfile que construirá una imagen con PostgreSQL 12.4 y la configuración necesaria. Construye y ejecuta la imagen:
docker build -t sql-battleships .
docker run -p 5432:5432 sql-battleshipsConectando a la base de datos en la imagen:
psql -U postgres <<<'call screen_loop()' & psql -U postgresTenga en cuenta que PostgreSQL en el contenedor utiliza la política de autenticación de confianza, es decir, permite todas las conexiones sin contraseña. ¡No olvide desenchufar el contenedor después de completar todos los juegos!
Conclusión
El uso de herramientas especiales para otros fines a menudo provoca comentarios negativos de los profesionales. Sin embargo, la resolución de tareas sin sentido pero interesantes entrena el pensamiento lateral y permite explorar la herramienta desde diferentes puntos de vista en busca de una solución adecuada.
Hoy hemos confirmado una vez más que puedes escribir lo que quieras en SQL si quieres. Sin embargo, recomendamos utilizar las herramientas en producción para el propósito para el que fueron diseñadas, y divertirse exclusivamente como pequeños proyectos domésticos.
