Someone got Doom in an SQL database

有人把《毁灭战士》（Doom）塞进了 SQL 数据库里

“Rendering Doom in a database is obviously a bad idea,” Lukas Vogel writes in a lengthy blog post explaining how exactly he managed to render Doom using an SQL database. OK, that’s not entirely accurate. The SQLDoom project uses a small Python client to handle input and output, drive the game’s timing, and display each frame to the screen. Behind that, a series of CedarDB tables tracks the game geometry and state, while about 1,300 lines of SQL queries spread across 89 common table expressions implement the game logic and generate 35 bitmap framebuffers per second.

“在数据库里渲染《毁灭战士》显然是个馊主意，” Lukas Vogel 在一篇详尽的博客文章中写道，并解释了他究竟是如何通过 SQL 数据库成功运行这款游戏的。好吧，这种说法并不完全准确。SQLDoom 项目使用了一个小型 Python 客户端来处理输入输出、驱动游戏时序，并将每一帧画面显示到屏幕上。而在后台，一系列 CedarDB 数据表负责追踪游戏几何结构和状态，同时约 1,300 行分布在 89 个公用表表达式（CTE）中的 SQL 查询实现了游戏逻辑，并以每秒 35 帧的速度生成位图帧缓冲区。

In this, SQLDoom is a major improvement over Vogel’s previous DoomQL project, which last year set out to build “a multiplayer Doom-like shooter entirely in SQL.” Unfortunately, that effort ended up with raycasting-based, grayscale ASCII graphics that were more akin to the simplistic 90-degree-angled maps of Wolfenstein 3D. The newer SQLDoom, on the other hand, generates full-color 640×480 frames that look like they could have come from the original Doom executable.

在这方面，SQLDoom 比 Vogel 之前的 DoomQL 项目有了重大改进。去年，DoomQL 旨在“完全用 SQL 构建一款类似《毁灭战士》的多人射击游戏”。遗憾的是，那次尝试最终只实现了基于光线投射的灰度 ASCII 图形，看起来更像是《德军总部 3D》那种简单的 90 度角地图。而较新的 SQLDoom 则能生成 640×480 的全彩画面，看起来就像是直接从《毁灭战士》原版可执行文件中运行出来的一样。

It’s all just data, man. Converting Doom‘s classic WAD files to a relational database was relatively simple and straightforward, Vogel writes, because of the way the original game broke levels down into vertices, lines, sectors, and so on. Even Doom‘s famous binary-space partition trees can be broken down into SQL using a sort_key for objects that’s pre-computed for each position at load time. With this set in your table, a simple “ORDER BY” statement can determine every frame which parts of walls to display and which to ignore, vastly improving performance.

这不过就是数据而已。Vogel 写道，将《毁灭战士》经典的 WAD 文件转换为关系型数据库的过程相对简单直接，因为原版游戏本身就是将关卡拆解为顶点、线条、扇区等要素的。甚至连《毁灭战士》著名的二叉空间分割树（BSP trees）也可以拆解为 SQL，只需在加载时为每个位置的对象预先计算一个排序键（sort_key）。在表中设置好这些后，一个简单的“ORDER BY”语句就能在每一帧确定哪些墙壁部分需要显示，哪些需要忽略，从而极大地提升了性能。

There were a few complications in going from SQL table to rendered first-person frames, however. Chief among these was the algorithm needed to render floors and ceilings, which can’t really make use of the elegant “visplanes” and state mutations that handle this rendering on a column-by-column basis in the original game. For SQLDoom, Vogel uses what he calls a “pretty hacky” replacement involving iterating over an ordered list of panels. Despite the substantial additional overhead of reading and writing to SQL tables for everything, Vogel said he was able to get DoomSQL running at about 60 fps on a Ryzen 7-powered laptop, with occasional dips down to 35 fps for busy scenes.

然而，从 SQL 表到渲染出第一人称画面仍存在一些复杂问题。其中最主要的是渲染地板和天花板所需的算法，它无法像原版游戏那样利用优雅的“可见平面”（visplanes）和状态变更来逐列处理渲染。对于 SQLDoom，Vogel 使用了一种他称之为“相当取巧”的替代方案，即遍历一个有序的面板列表。尽管对所有内容进行 SQL 表读写会带来巨大的额外开销，但 Vogel 表示，他还是能在搭载 Ryzen 7 处理器的笔记本电脑上以约 60 帧的速度运行 DoomSQL，在复杂场景下偶尔会降至 35 帧。

And despite the hassles of translating Doom to SQL, Vogel points out that the database’s steady “reference snapshot” of the game state, along with inherent concurrency and access handling features, offers significant benefits for running a multiplayer server. With a database, there’s “no partially applied updates, physics bugs, or disagreements over whether the rocket actually hit,” Vogel says. If you want to run a Doomtabase on your own local machine, you can do so simply with the GitHub code, a copy of CedarDB, and a Doom WAD file. Or you can jump into a free online hosted demo match to test it out without all the setup.

尽管将《毁灭战士》转换为 SQL 过程繁琐，但 Vogel 指出，数据库对游戏状态稳定的“参考快照”，以及其固有的并发和访问处理特性，为运行多人服务器提供了显著优势。Vogel 说，有了数据库，“就不会出现部分更新、物理错误，或者关于火箭是否击中目标的争议”。如果你想在自己的本地机器上运行“Doomtabase”，只需使用 GitHub 上的代码、一份 CedarDB 和一个《毁灭战士》的 WAD 文件即可。或者，你也可以直接进入免费的在线托管演示比赛进行测试，无需进行任何繁琐的设置。