Морской бой в PostgreSQL
Программисты ведут ожесточенные споры о вреде и пользе хранимых процедур в базах данных. Сегодня мы отвлечемся от них и снова сделаем невероятное в невозможных условиях.
Сегодня разработчики по возможности стараются не выстраивать бизнес-логику в базах данных. Тем не менее, находятся энтузиасты, которые бросают себе вызов и создают, например, матчер биржи, а иногда целые компании переводят серверную часть на хранимые процедуры БД. Авторы таких проектов утверждают, что на базах данных можно сделать все, что угодно, если захотеть.
Тут невольно вспоминается «морской бой» по BGP. Возможно ли сделать эту игру на SQL? Для ответа на этот вопрос мы воспользуемся услугами PostgreSQL 12, а также языком PLpgSQL. Для тех, кому не терпится посмотреть «под капот», ссылка на репозиторий.
Игра «морской бой» требует постоянного ввода информации от пользователя на протяжении всей игры. Самый простой способ взаимодействия базы данных с пользователем — клиент командной строки.
Ввод данных
Получение данных от пользователя — наиболее сложная задача в данном проекте. Самый простой с точки зрения разработки способ — просить пользователя писать корректные SQL-запросы для вставки необходимой информации в специально подготовленную таблицу. Данный способ относительно медленный и требует от пользователя повторения запроса раз за разом. Хочется иметь возможность забирать данные без написания SQL-запроса.
PostgreSQL предлагает использовать COPY… FROM STDIN для сохранения данных из стандартного потока ввода в таблицу. Но у этого решения есть два недостатка.
Во-первых, оператор COPY нельзя ограничить по количеству загружаемой информации. Оператор COPY завершает свое выполнение только при получении признака конца файла. Таким образом, пользователю дополнительно придется вводить EOF для обозначения завершения ввода информации.
Во-вторых, в хранимых процедурах и функциях нет файлов stdin и stdout. Стандартные потоки ввода и вывода доступы при выполнении обычных SQL-запросов через клиент, но там недоступны циклы. Таким образом, нельзя запустить игру в одну SQL-команду. Это могло бы стать концом истории, однако нашлось хитрое решение.
В PostgreSQL есть возможность логировать все запросы, в том числе некорректные. Более того, логирование может быть в формате CSV, а оператор COPY умеет работать с этим форматом. Настроим логирование в файле конфигурации postgresql.conf:
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql.log'
log_min_error_statement = error
log_statement = 'all'
Теперь в файле postgresql.csv будут записаны все SQL-запросы, которые выполняются в PostgreSQL. В документации, в разделе Using CSV-Format Log Output, описан способ подгрузки csv-логов при включенной ротации. Нас же интересует подгрузка логов с интервалом в одну секунду.
Так как проводить ротацию логов каждую секунду нецелесообразно, то будем раз за разом загружать файл логов, дополняя таблицу с логами. Решение «в лоб» из одного оператора COPY сработает только первый раз, а далее будет выводить ошибку из-за конфликтов первичных ключей. Данная проблема решается использованием промежуточной таблицы и предложения ON CONFLICT DO NOTHING.
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;
Можно также добавить фильтр при переносе данных из временной таблицы в postgres_log, сократив количество ненужной информации в таблице логов. Так как мы не планируем получать от пользователя корректные SQL-запросы, то можем ограничиться запросами, где есть текст запроса и тег команды равен idle.
К сожалению, в PostgreSQL нет планировщика, который бы запускал процедуру по расписанию. Так как проблема находится в «серверной» части игры, то ее можно решить написанием shell-скрипта, который каждую секунду будет вызывать хранимую процедуру загрузки логов.
Теперь любая введенная пользователем строка, не являющаяся корректным SQL-запросом, появится в таблице postgres_log. Хотя этот способ требует обязательного введения разделителя — точки с запятой, это гораздо проще, чем отправка EOF.
Внимательный читатель отметит, что во время выполнения хранимой процедуры или функции клиент командной строки не будет обрабатывать команды и будет абсолютно прав. Для работы подобного решения требуются два клиента: «экран» и «клавиатура».
Клиент-экран (слева) и клиент-клавиатура (справа)
Для «сопряжения» клавиатуры экран генерирует псевдослучайную последовательность символов, которую необходимо ввести на клиенте-клавиатуре. «Экран» идентифицирует клавиатуру по уникальному идентификатору сессии клиента (session_id) и далее выбирает из таблицы логов только строки с нужным идентификатором сессии.
Легко заметить, что вывод клиента-клавиатуры не несет пользы, а ввод в клиент-экран ограничивается вызовом одной процедуры. Для удобства эксплуатации можно отправить «экран» в фон, а вывод «клавиатуры» погасить:
psql <<<'select keyboard_init()' & psql >/dev/null 2>&1
Теперь у нас есть возможность вводить в базу данных информацию со стандартного потока ввода и использовать хранимые процедуры.
Игровой цикл
Активная часть игры
Игра условно разделена на следующие фазы:
- сопряжение клиента-экрана с клиентом-клавиатурой;
- создание лобби или подключение к существующему;
- расстановка кораблей;
- активная часть игры.
Игра состоит из пяти таблиц:
- визуальное отображение поля, две таблицы;
- список кораблей и их состояние, две таблицы;
- список событий в игре.
Во время создания лобби игрок A, сервер, создает все таблицы и заполняет их начальными значениями. Для возможности проведения нескольких игр параллельно все таблицы в названии имеют десятизначный идентификатор лобби, который генерируется псевдослучайным образом при старте игры.
Разработка логики игры в целом очень похожа на разработку на традиционных языках программирования и отличается по большей части синтаксисом и отсутствием библиотеки для красивого форматирования. Для вывода используется оператор RAISE, который для psql выводит сообщение с префиксом уровня логирования. Избавиться от него не получится, но это не мешает игре.
Различия в разработке тоже есть, и они заставляют мозг закипать.
Время коммитов
Вся логика игры запускается клиентом-экраном, то есть от начала до завершения выполняется одна процедура. Причем за одну транзакцию, если явно не указан оператор COMMIT.
Это означает, что новые таблицы и новые данные в существующих таблицах не изменятся для второго игрока до тех пор, пока транзакция не будет завершена. Более того, при работе со временем важно помнить, что функция now () возвращает текущее время на момент начала транзакции.
Сделать коммит не так просто, как кажется. Они допустимы только в процедурах. Попытка зафиксировать транзакцию в функции приведет к ошибке, так как она работает в рамках внешней по отношению к функции транзакции.
Запуск игры
Начало игры
Запускать такую игру в реальном окружении мы не рекомендуем. К счастью, есть возможность быстро и без сложностей развернуть базу данных с игрой. В репозитории можно найти Dockerfile, который соберет образ с PostgreSQL 12.4 и необходимой конфигурацией. Сборка и запуск образа:
docker build -t sql-battleships .
docker run -p 5432:5432 sql-battleships
Подключение к БД в образе:
psql -U postgres <<<'call screen_loop()' & psql -U postgres
Обратите внимание, что PostgreSQL в контейнере использует политику аутентификации trust, то есть разрешает все подключения без пароля. Не забывайте отключать контейнер после завершения всех партий!
Заключение
Использование специальных инструментов не по назначению часто вызывает негатив со стороны профессионалов. Однако решение бессмысленных, но интересных задач тренирует нестандартное мышление и позволяет изучить инструмент с разных точек зрения в поиске подходящего решения.
Сегодня мы лишний раз подтвердили, что на SQL при желании можно написать все, что угодно. Тем не менее, мы рекомендуем в продакшене использовать инструменты по назначению, а такие забавы делать исключительно как маленькие домашние проекты.
