Batalha marítima em PostgreSQL

Os programadores têm um debate acirrado sobre os perigos e benefícios dos procedimentos armazenados em bancos de dados. Hoje vamos nos desviar deles e novamente fazer o incrível em condições impossíveis.



Hoje, os desenvolvedores tentam evitar a construção de lógica de negócios em bancos de dados, sempre que possível. No entanto, existem entusiastas que se desafiam e criam, por exemplo, um matcher de troca , e às vezes empresas inteiras transferem o lado do servidor para procedimentos armazenados em banco de dados. Os autores de tais projetos argumentam que você pode fazer qualquer coisa nos bancos de dados, se quiser.



Aqui, involuntariamente, recordo a "batalha naval" sobre o BGP . É possível fazer este jogo em SQL? Para responder a esta pergunta, usaremos os serviços PostgreSQL 12, bem como o PLpgSQL. Para aqueles que não podem esperar para olhar "sob o capô", um link para o repositório .



O jogo de batalha marítima requer informações constantes do usuário ao longo do jogo. A maneira mais fácil de interagir com um usuário de banco de dados é um cliente de linha de comando.

Entrada de dados

Obter dados do usuário é a tarefa mais difícil neste projeto. A maneira mais fácil do ponto de vista do desenvolvimento é pedir ao usuário para escrever consultas SQL corretas para inserir as informações necessárias em uma tabela especialmente preparada. Este método é relativamente lento e exige que o usuário repita a solicitação indefinidamente. Eu gostaria de poder recuperar dados sem escrever uma consulta SQL.



O PostgreSQL sugere o uso de COPY… FROM STDIN para salvar os dados da entrada padrão em uma tabela. Mas essa solução tem duas desvantagens.



Primeiro, o operador COPY não pode ser limitado pela quantidade de informações carregadas. A instrução COPY termina apenas quando recebe um sinal de fim de arquivo. Assim, o usuário também terá que inserir EOF para indicar a conclusão da entrada de informações.



Em segundo lugar, não há arquivos stdin e stdout em procedimentos e funções armazenados. Os fluxos de entrada e saída padrão estão disponíveis ao executar consultas SQL regulares por meio do cliente, mas os loops não estão disponíveis lá. Portanto, você não pode executar o jogo em um comando SQL. Isso poderia ter sido o fim da história, mas uma solução astuta foi encontrada.



PostgreSQL tem a capacidade de registrartodos os pedidos, incluindo os incorretos. Além disso, o registro pode estar no formato CSV, e o operador COPY pode trabalhar com esse formato. Vamos configurar o registro no arquivo de configuração postgresql.conf:

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

O arquivo postgresql.csv agora registrará todas as consultas SQL que são executadas no PostgreSQL. A documentação, na seção Usando saída de log em formato CSV , descreve uma maneira de carregar csv-logs com rotação habilitada. Estamos interessados ​​em carregar logs com um intervalo de um segundo.



Como é impraticável girar os logs a cada segundo, carregaremos o arquivo de log repetidamente, adicionando à tabela os logs. Uma solução direta de um operador COPY funcionará apenas na primeira vez e, em seguida, exibirá um erro devido a conflitos de chave primária. Esse problema é resolvido usando uma tabela de teste e a cláusula ON CONFLICT DO NOTHING .





Você também pode adicionar um filtro ao migrar dados de uma tabela temporária para postgres_log, reduzindo a quantidade de informações desnecessárias na tabela de log. Como não planejamos receber consultas SQL corretas do usuário, podemos nos restringir a consultas em que haja um texto de consulta e a tag de comando esteja ociosa.



Infelizmente, o PostgreSQL não possui um agendador que executa uma rotina em um agendamento. Como o problema está localizado na parte "servidor" do jogo, ele pode ser resolvido escrevendo um script de shell que chamará o procedimento armazenado para carregar logs a cada segundo.



Qualquer string inserida pelo usuário que não seja uma consulta SQL válida agora aparecerá na tabela postgres_log. Embora esse método exija o separador de ponto-e-vírgula obrigatório, é muito mais fácil do que enviar EOF.



O leitor atento notará que durante a execução de um procedimento armazenado ou função, o cliente de linha de comando não processará comandos e estará absolutamente correto. Para que esta solução funcione, são necessários dois clientes: uma "tela" e um "teclado".



^{Cliente de tela (esquerda) e cliente de teclado (direita)}

Para "emparelhar" o teclado, a tela gera uma seqüência pseudo-aleatória de caracteres que devem ser inseridos no cliente de teclado. "Screen" identifica o teclado pelo identificador único da sessão do cliente (session_id) e então seleciona da tabela de log apenas as linhas com o identificador de sessão necessário.



É fácil ver que a saída do teclado do cliente não é útil e que a entrada para a tela do cliente é limitada a uma única chamada de procedimento. Para facilidade de uso, você pode enviar a "tela" para o fundo, e extinguir a saída do "teclado":

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

Agora temos a capacidade de inserir informações de entrada padrão no banco de dados e usar procedimentos armazenados.

Loop de jogo

^{A parte ativa do jogo}

O jogo é condicionalmente dividido nas seguintes fases:

• interface do cliente de tela com o cliente de teclado;
• criar um lobby ou conectar-se a um existente;
• colocação de navios;
• a parte ativa do jogo.

O jogo consiste em cinco mesas:

• exibição visual do campo, duas mesas;
• lista de navios e seu estado, duas tabelas;
• lista de eventos no jogo.

Durante a criação do lobby, o jogador A, o servidor, cria todas as tabelas e as preenche com os valores iniciais. Para que seja possível jogar vários jogos em paralelo, todas as tabelas do título têm um identificador de lobby de dez dígitos, que é gerado pseudo-aleatoriamente no início do jogo.



O desenvolvimento da lógica do jogo é geralmente muito semelhante ao desenvolvimento em linguagens de programação tradicionais e difere principalmente na sintaxe e na falta de uma biblioteca para uma formatação adequada. Para a saída, o operador RAISE é usado, que para psql exibe uma mensagem com um prefixo de nível de registro. Você não conseguirá se livrar dele, mas isso não interfere no jogo.



Existem diferenças de design também, e elas fazem o cérebro ferver.

Tempo de compromisso

Toda a lógica do jogo é iniciada pela tela do cliente, ou seja, um procedimento é executado do início ao fim. Além disso, para uma transação, se o operador COMMIT não for especificado explicitamente.



Isso significa que novas tabelas e novos dados em tabelas existentes não serão alterados para o segundo jogador até que a transação seja concluída. Além disso, ao trabalhar com a hora, é importante lembrar que a função now () retorna a hora atual no momento em que a transação foi iniciada .



Fazer um commit não é tão fácil quanto parece. Eles só são permitidos em procedimentos . Uma tentativa de confirmar uma transação em uma função resultará em um erro, uma vez que opera dentro de uma transação externa à função.

Executando o jogo

^{Iniciando o jogo}

Não recomendamos executar esse tipo de jogo em um ambiente real. Felizmente, é possível implantar de forma rápida e fácil um banco de dados com um jogo. No repositório, você pode encontrar um Dockerfile que construirá uma imagem com PostgreSQL 12.4 e a configuração necessária. Crie e execute a imagem:

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

Conectando-se ao banco de dados na imagem:

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

Observe que o PostgreSQL no container utiliza a política de autenticação trust, ou seja, permite todas as conexões sem senha. Não se esqueça de desconectar o contêiner após completar todos os jogos!

Conclusão

O uso de ferramentas especiais para outros fins frequentemente causa feedback negativo dos profissionais. No entanto, resolver tarefas sem sentido, mas interessantes, treina o pensamento lateral e permite que você explore a ferramenta de diferentes pontos de vista em busca de uma solução adequada.



Hoje, mais uma vez confirmamos que você pode escrever o que quiser em SQL, se quiser. No entanto, recomendamos usar as ferramentas em produção para os fins a que se destinam, e se divertir exclusivamente como pequenos projetos domésticos.