Clever Geek Handbook

PostgreSQL中的海战



程序员对数据库中存储过程的危害和好处进行了激烈的辩论。今天我们将脱离他们，在不可能的情况下再次做出令人难以置信的事情。



如今，开发人员尝试尽可能避免在数据库中建立业务逻辑。尽管如此，还是有一些发烧友挑战自己并创建例如Exchange Matcher，有时整个公司都将服务器端转移到数据库存储过程。这些项目的作者认为，如果需要，您可以在数据库上执行任何操作。



在这里，我不由自主地回顾了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文件将记录在PostgreSQL中执行的所有SQL查询。该文档在“使用CSV格式的日志输出”部分中，描述了一种在启用旋转的情况下加载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查询，因此我们可以将自己限制为存在查询文本且命令标记为空闲的查询。



不幸的是，PostgreSQL没有按计划运行例程的计划程序。由于问题出在游戏的“服务器”部分，因此可以通过编写外壳脚本来解决，该脚本将每秒调用存储过程以加载日志。



用户输入的不是有效SQL查询的任何字符串现在都将显示在postgres_log表中。尽管此方法需要使用强制分号分隔符，但比发送EOF容易得多。



细心的读者会注意到，在执行存储过程或函数期间，命令行客户端将不会处理命令，并且绝对正确。为了使该解决方案起作用，需要两个客户端：“屏幕”和“键盘”。



屏幕客户端（左）和键盘客户端（右）

要“配对”键盘，屏幕会生成必须在键盘客户端上输入的伪随机字符序列。“屏幕”通过客户端会话的唯一标识符（session_id）识别键盘，然后从日志表中仅选择具有所需会话标识符的行。



很容易看出，客户端键盘的输出没有用，并且客户端屏幕的输入仅限于单个过程调用。为了易于使用，您可以将“屏幕”发送到背景，并熄灭“键盘”的输出： 



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


现在，我们可以将标准输入中的信息输入数据库并使用存储过程。



游戏循环

游戏的活跃部分

游戏有条件地分为以下几个阶段：



  • 屏幕客户端与键盘客户端的接口；
  • 创建一个大厅或连接到现有大厅；
  • 船舶布置；
  • 游戏的活跃部分。


游戏包含五个表：



  • 现场显示，两个表；
  • 船舶清单及其状况，两张表；
  • 游戏中的事件列表。


在大厅创建期间，服务器A播放器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使用信任身份验证策略，即，它允许所有连接而无需密码。完成所有游戏后，别忘了拔下容器的电源！



结论

将专用工具用于其他目的通常会引起专业人员的负面反馈。但是，解决无意义但有趣的任务会训练横向思维，并使您可以从不同的角度探索该工具以寻找合适的解决方案。



今天，我们再次确认，您可以根据需要在SQL中编写任何内容。尽管如此，我们还是建议将工具用于生产目的，并专门进行小型家庭项目之类的乐趣。





More articles:


All Articles