数据库也需要测试
我们的一位客户需要向他们的 PostgreSQL 数据库添加安全策略，以限制某些新用户的数据可见性。这很快就会变得复杂和棘手，确保规则对受影响的用户正常工作，同时让其他用户不受干扰。
这让我觉得这是一个创建单元测试的绝好机会，而不是缺少添加单元测试的好机会！这不仅仅是因为它有助于证明这些安全策略确实可以正常工作，还因为（忏悔时间）我最近在没有单元测试帮助的情况下为不同的数据库做了一个类似的项目，而且它并不好玩。
所以这似乎是使用pgTAP的好时机，这是一组旨在允许在数据库本身内编写单元测试的数据库函数。它们产生“测试任何协议”（TAP）输出，这是一个简单的协议，可以在易于理解的报告中显示单元测试结果。
测试什么？
编写单元测试的第一步是决定要测试的东西。就我而言，我认为我应该确保为我感兴趣的表打开行级安全策略，这可以从视图中的rowsecurity字段中获得pg_tables：
select ok(rowsecurity, tablename || ' has row security enabled') from pg_tables
where schemaname = 'public'
and (
tablename in (
-- ... some hard coded table names
) or tablename like 'some_other_tables_%'
);
该ok()函数来自 pgTAP。每次调用它都算作一次测试；当第一个参数为真时测试通过，当参数为其他参数时测试失败。第二个参数是描述测试内容的可选注释。遵循一个非常常见的与 TAP 相关的命名约定，我将它放在一个名为的文件中，该文件00-test.sql位于我的项目根目录下的一个目录中，简称为t.
一组更复杂的测试可能包括几个不同的文件，其中名称的数字部分有助于按所需的运行顺序对测试进行排序，文件名的其余部分描述了其中的测试主题。但这只是开始。我可以pg_prove使用 pgTAP 包中包含的 运行它：
pg_prove -d mydatabase t/00-test.sql
迭代改进
这失败了，有几个原因。
首先，我还没有在我的数据库中安装 pgTAP 扩展，使用CREATE EXTENSION pgtap.
我实际上也没有在我的测试中做任何事情来运行我正在测试的代码。这个项目中的实际代码包含一些数据库函数，我们需要运行这些函数来创建数据库安全策略，而我还没有运行它们。
最后，pgTAP 要求我首先“计划”我的测试，或者换句话说，我需要在运行之前通知 pgTAP 我计划运行多少测试。调用它也很好，finish()因此 pgTAP 可以自行清理。
我在我的数据库中安装了 pgTAP 扩展，并将测试修改如下：
begin;
\i create_policy.sql
select plan(1); -- plan for a single test
select ok(rowsecurity, tablename || ' has row security enabled') from pg_tables
where schemaname = 'public'
and (
tablename in (
-- ... some hard coded table names
) or tablename like 'some_other_tables_%'
);
select finish();
rollback;
这将我的测试包装在一个事务中，这样我就可以回滚所有内容，以基本上按照我找到的方式离开数据库。它还调用我正在测试的实际代码 increate_policy.sql并计划一次测试。它给了我这个新的失败：
t/m.sql .. All 1 subtests passed
Test Summary Report
-------------------
t/m.sql (Wstat: 0 Tests: 226 Failed: 225)
Failed tests: 2-226
Parse errors: Bad plan. You planned 1 tests but ran 226.
Files=1, Tests=226, 1 wallclock secs ( 0.04 usr 0.00 sys + 0.03 cusr 0.01 csys = 0.08 CPU)
Result: FAIL
这里的问题是每次调用都ok()算作一个测试，而我的测试显然找到了 226 个表来检查行级安全性。我可以像这样改进计划：
select plan(count(*)::integer)
from pg_tables where schemaname = 'public'
and (
tablename in (
-- ... some hard coded table names
) or tablename like 'some_other_tables_%'
);
count()返回 a bigint，并plan()期望integer，所以这需要一个类型转换，但在其他方面非常简单。现在我的测试通过了：
josh@here:~dw$ pg_prove -d nedss t/00-test.sql
t/00-test.sql .. ok
All tests successful.
Files=1, Tests=226, 1 wallclock secs ( 0.03 usr 0.01 sys + 0.03 cusr 0.01 csys = 0.08 CPU)
Result: PASS
回顾过去
可以说 pgTAP 包含许多类似于 的函数ok()，用于测试数据库的各个方面、其结构和行为。
在其完成状态下，我的测试套件包括几个测试，以确保各种所需的初步准备就绪，一些像上面那样检查必要的特定于表的设置的测试，确保创建了受影响角色的其他测试，最后一些创建了一些样本数据并用于SET ROLE直接测试应用了各种策略的角色的数据可见性。
老实说，我对这个完整的测试套件带给我的安全感感到惊讶。正如我所提到的，我以前做过类似的工作，并且知道虽然我在编写代码时对代码充满信心，但这种信心只有通过相当广泛的手动测试才能获得。我非常了解位腐烂的挣扎，并且我知道在一两年后的某个时候手动重复该测试方案至少会同样困难。
我还认识到，如果我需要再次设置类似的策略，我可以将这些测试本身用作参考，因为它们准确地显示了如何运行相关代码。当然，我也将这些信息包含在项目的相关README文件中……对吗？
让我们知道您是否使用过 pgTAP，以及它对您的数据库开发有何影响。
原文标题：Formatting SQL code with pgFormatter within Vim
原文作者：Josh Tolley
原文链接：https://www.endpointdev.com/blog/2022/03/using-pgtap-automate-database-testing/
