使用 pgTap 扩展为 PostgreSQL 创建单元测试框架

在数据库开发领域，确保代码的可靠性和正确性至关重要。单元测试起着至关重要的作用，它允许开发人员验证数据库逻辑的各个组件，包括存储过程、函数和触发器。

PgTap（PostgreSQL 测试任何协议）是一个单元测试框架，它使开发人员能够直接在数据库中编写和运行测试。它提供原生数据库测试功能，支持对数据库功能和查询的验证，并在没有外部依赖关系的情况下提供即时反馈，从而确保代码按预期运行。

在这篇文章中，我们将探讨如何利用 PgTap 扩展在适用于 PostgreSQL 的 Amazon RDS 和兼容 Amazon Aurora PostgreSQL 的版本数据库上进行单元测试，以帮助您构建强大而可靠的数据库应用程序。

pgTap 概述

pgtap 是一个开源单元测试框架，由一套用 PL/pgSQL 编写的数据库函数组成。它提供了一组强大的工具和实用程序，用于编写和运行针对数据库代码的测试。受流行的 XUnit 测试框架的启发，PgTap 遵循类似的惯例，允许您为表、函数、触发器等数据库对象编写全面的测试。

其他测试框架和工具可用于 PostgreSQL，例如 PgUnit 和 pytest-postgresql。这些工具还提供在 PostgreSQL 环境中测试数据库功能、查询和数据完整性的功能。将 PgTap 与这些工具进行比较时，其突出特点之一是其全面的内置断言函数。

Pgtap 为 PostgreSQL 环境提供了强大的功能，简化了测试的编写和执行。通过与 PostgreSQL 的无缝集成、内置断言和强大的可扩展性，使用 PgTap 的好处包括全面的测试覆盖范围、原生 PostgreSQL 集成和数据库组件的自动验证，使其成为确保数据库代码可靠性的有效工具。在活跃社区的支持下，它可以帮助开发人员维护强大而可靠的数据库。在本指南中，我们将探讨 pgTap 如何增强您的测试工作流程。

在 Amazon RDS for PostgreSQL 或兼容 Amazon Aurora PostgreSQL 的 Amazon RDS 中使用 pgTap 进行单元测试的好处是：

通过验证表格的结构和内容来确保数据的完整性
在开发初期检测回归并识别潜在问题
提供可重复的自动化测试流程，节省时间和精力
通过持续集成和持续部署 (CI/CD) 管道实现集成
使用标准化测试框架促进开发人员之间的协作

解决方案概述

在以下各节中，我们将演示如何安装 pgTap、创建示例数据库和其他对象以及测试 PgTap 框架。

先决条件

使用适用于 PostgreSQL 11 或更高版本的 Amazon RDS 和适用于 PostgreSQL 13 或更高版本的 Amazon Aurora 创建活动数据库连接。
以具有rds_superuser权限的用户身份连接到上面创建的数据库来安装 pgTap，然后运行以下代码：
```
-- Create the "pgtap" extension
CREATE EXTENSION IF NOT EXISTS pgtap;
```

创建示例数据库并测试 PgTap 框架。

使用命令行工具或其他方法（例如 pgAdmin 或 psql 实用程序）连接到安装 pgTap 扩展的数据库实例，然后运行以下命令来创建测试 PgTap 框架所需的示例对象：

创建测试表：

CREATE TABLE users (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    fname VARCHAR(100),
    age INTEGER,
    email TEXT
);

CREATE TABLE products (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    fname VARCHAR(100),
    price DECIMAL(10, 2),
    category VARCHAR(50)
);

CREATE TABLE price_changes (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    product_id INTEGER,
    old_price DECIMAL(10, 2),
    new_price DECIMAL(10, 2),
    change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

使用 INSERT 语句填充表：

-- Bulk Insert for 'users' table 
INSERT INTO users (fname, age, email) VALUES ('John Doe', 30, 'john.doe@example.com'), ('Jane Smith', 28, 'jane.smith@example.com'), ('Bob Johnson', 35, 'bob.johnson@example.com'); 

-- Bulk Insert for 'products' table 
INSERT INTO products (fname, price, category) VALUES ('Widget A', 9.99, 'Electronics'), ('Widget B', 14.99, 'Electronics'), ('Gadget C', 24.99, 'Gadgets'); 
COMMIT;

创建示例函数：
```
CREATE FUNCTION calculate_total_price (quantity INTEGER, unit_price DECIMAL(10, 2))
RETURNS DECIMAL(10, 2) AS $$
BEGIN
    RETURN quantity * unit_price;
END;
$$ LANGUAGE plpgsql;
```
此函数可用于根据产品的数量和单价计算订单或采购的总成本。它通过将数量乘以单位价格来计算总价格。
```
CREATE FUNCTION validate_email(email TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    -- Simple email validation logic
    RETURN (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
END;
$$ LANGUAGE plpgsql;
```
此函数检查电子邮件地址是否有效，可以在将电子邮件地址存储到数据库之前调用此函数。该函数返回一个布尔值：如果电子邮件地址有效，则返回 TRUE，否则返回 FALSE。我们可以使用 pgTap 测试框架验证上述功能的功能。

创建示例触发器：

CREATE or replace FUNCTION log_product_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.price <> OLD.price THEN
        INSERT INTO price_changes (product_id, old_price, new_price)
        VALUES (NEW.id, OLD.price, NEW.price);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

此触发器可用于跟踪和记录产品的价格变化，可用于审计或历史目的。

CREATE or replace TRIGGER product_price_change
AFTER UPDATE OF price ON products
FOR EACH ROW
WHEN (NEW.price IS DISTINCT FROM OLD.price)
EXECUTE FUNCTION log_product_changes();

此触发器将在 price_changes 表中插入一个新行，每当新价格发生变化时，都会记录产品编号、旧价格和新价格。

我们可以使用 pgTap 测试框架验证上述触发器的功能。

使用 pgTap 测试用例

PgTap 扩展为表空间、架构、表、列、视图、序列、索引、触发器、函数、策略、用户、语言、规则、类、运算符和扩展提供了广泛的测试方法。我们可以通过两种不同的方式使用 PgTap：

1. pg_prove

我们也可以使用 pg_prove 来测试 pgTap，它是一个命令行工具，允许你使用单个命令批量运行多个 PgTap 测试。使用pg_prove，您可以自动执行pg_tap测试。您可以将测试脚本编写为 SQL 脚本并将其存储在目录中。pg_prove然后可以运行该目录中的所有测试。

测试的输出由 TAP:: Harness 收集和处理，以汇总测试结果。

要使用pg_prove，请按照以下步骤操作：

导航到包含测试脚本的文件夹，运行以下命令，将[database name]和[hostname]替换为数据库名称和主机名：

pg_prove -h host.rds.amazonaws.com -p 5432 -d databaseName -U postgres -v path/to/test/directory/*.sql  > results.txt

此命令针对提供的数据库执行指定目录中存在的所有测试脚本。

通过使用pg_prove，您可以简化运行多个 pgTap 测试的过程，确保您的数据库功能经过全面测试和验证。

2. 命令行工具或客户端实用程序

我们可以使用 pgAdmin 或 psql 等一些工具来运行测试用例，确保您从托管数据库的同一亚马逊云科技区域进行连接。

浏览几个 PgTap 测试用例

1. 表存在性测试

此测试用例验证数据库中是否存在特定的表、用户是否拥有该表的SELECT权限，以及该函数和触发器是否存在。

在当前的测试场景中，我们将使用该pg_prove实用程序。我们将在本地计算机上或存储值（例如 Amazon EBS）上创建一个测试 SQL 文件，从中可以访问和连接数据库。

以下是我们的 test.sql 文件，可以使用任何文本编辑器或文档编辑工具创建：

\unset ECHO
\set QUIET 1
-- Turn off echo and keep things quiet.

-- Format the output for nice TAP.
\pset format unaligned
\pset tuples_only true
\pset pager off

-- Revert all changes on failure.
\set ON_ERROR_ROLLBACK 1
\set ON_ERROR_STOP true

BEGIN;

-- PLAN  the tests.
SELECT PLAN (4);

-- Run the tests.

SELECT HAS_TABLE_PRIVILEGE('my_schema.users', 'SELECT') AS privilege;

SELECT has_table('my_schema'::name,'users'::name) AS my_table_exists;

select HAS_FUNCTION('my_schema','log_product_changes','Check if function exists!');
select has_trigger('products','product_price_change','Check if trigger "product_price_change" exists');

-- Finish the tests and clean up.
SELECT * FROM FINISH();
ROLLBACK;

前面的代码片段包括特定于使用 PgTap 运行测试用例的其他指令和设置。让我们仔细阅读每行并详细解释其用途。

代码解释：

提供的脚本在 PostgreSQL 中设置并执行 PgTap 测试用例，重点是配置测试环境和控制输出。\unset ECHO和\set QUIET 1之类的指令用于抑制 SQL 命令的回显并最大限度地减少不必要的输出，从而确保测试结果保持干净和集中。

指\pset令（\pset format unaligned、\pset tuples_only true和\pset pager off）对输出进行格式化以与 TAP（测试任何协议）格式保持一致，从而删除了表格格式和分页。此外，还配置了诸如\set ON_ERROR_ROLLBACK 1和\set ON_ERROR_STOP true之类的错误处理设置，以确保测试期间的任何错误都会导致更改立即回滚，使数据库状态保持不变。

该脚本使用BEGIN;开始新的事务，使用SELECT PLAN (4);指定测试计划，并运行测试查询以检查表权限、表是否存在、函数存在和触发器是否存在。这些测试验证用户是否具有 my_schema.users 表的 SELECT 权限、该表是否存在、是否定义了 log_product_changes 函数，以及是否存在 product_price_change 触发器。测试完成后，使用SELECT * FROM FINISH();显示结果，并ROLLBACK;回滚事务，以确保测试不会影响任何数据。这种设置可确保正确控制测试环境，处理错误，并且数据库不受测试操作的影响。

现在，我们将使用上述命令运行 test.sql 文件
运行以下命令，将[database name]和[hostname]替换为您的数据库名称和主机名：pg_prove

pg_prove -h host.rds.amazonaws.com -p 5432 -d databaseName -U postgres -v path/to/test/directory/*.sql  > results.txt

输出解释：

输出：

1..4
ok 1 - privilege
ok 2 - my_table_exists
ok 3 - Check if function exists!
ok 4 - Check if trigger "product_price_change" exists
All tests successful.

输出表明总共执行了 4 次测试，所有测试均成功通过。测试检查了各个方面，例如，第一次测试确认用户在相关表上拥有所需的 SELECT 权限，第二次测试验证了 my_schema.users 表的存在，第三次测试确保了 log_product_changes 函数存在，第四次测试检查了 product_price_change 触发器的存在。最后显示 "所有测试成功" 消息，确认所有检查均已完成，没有任何问题，这表明数据库架构已正确配置为必要的权限、表、函数和触发器。

2. 数据完整性测试

该测试用例通过检查特定条件来验证表中数据的完整性，确保该表存在且正好包含三行。

在这种情况下，我们使用命令行工具或客户端实用程序（例如 pgAdmin 或 psql）在用户界面中执行测试。

BEGIN;

SELECT PLAN (2);

SELECT results_eq('SELECT COUNT(*)::int  AS no_duplicate_emails  FROM (SELECT email, COUNT(*) as count FROM users  GROUP BY email  HAVING COUNT(*) > 1) AS duplicates',ARRAY[0], 'no_duplicate_emails');
      
SELECT results_eq('SELECT COUNT(*)::int  AS users_table_has_3_rows FROM users; ',ARRAY[3],'row count matched');
            

-- Finish the tests and clean up.
SELECT * FROM FINISH();
ROLLBACK;

输出：

1..2
ok 1 - no_duplicate_emails
ok 2 - row count matched

在这里，我们在第一个查询中检查测试表用户中的电子邮件重复情况，在第二个查询中，我们匹配了预期的表数。

3. 功能测试

这个测试用例验证了函数输出的正确性。测试使用参数 5 和 10.99 调用该calculate_total_price函数，并检查返回值是否等于 54.95：

\unset ECHO
\set QUIET 1
-- Turn off echo and keep things quiet.

-- Format the output for nice TAP.
\pset format unaligned
\pset tuples_only true
\pset pager off

-- Revert all changes on failure.
\set ON_ERROR_ROLLBACK 1
\set ON_ERROR_STOP true

BEGIN;

SELECT PLAN (1);

SELECT IS(CALCULATE_TOTAL_PRICE(5, 10.99), 54.95, 'correct_total_price');

-- Finish the tests and clean up.
SELECT * FROM FINISH();
ROLLBACK;

输出：

1..1
ok 1 - correct_total_price

4. 触发器测试

此测试用例检查在满足特定条件时触发器是否执行预期的操作。测试在products表中插入一个新行，调用product_price_change触发器。然后，它通过验证所插入产品的price_changes表中是否存在相应的行来检查触发器是否已运行。

始终使用set session_replication_role = 'origin'，通过设置此设置，PostgreSQL 将触发器，否则在运行pg_tap测试时不会触发触发器。

\unset ECHO
\set QUIET 1
-- Turn off echo and keep things quiet.

-- Format the output for nice TAP.
\pset format unaligned
\pset tuples_only true
\pset pager off

-- Revert all changes on failure.
\set ON_ERROR_ROLLBACK 1
\set ON_ERROR_STOP true

BEGIN;

SELECT PLAN (2);

set session_replication_role = 'origin';

-- update an old  product with a different price to trigger the price change trigger

update products  set price = 24.99 where fname = 'Widget A';

SELECT has_trigger('my_schema','products', 'product_price_change', 'trigger exists on product table and insertions on price_changes' );

   select results_eq('SELECT COUNT(*)::int   AS price_change_logged FROM price_changes WHERE product_id = (SELECT id FROM products WHERE fname= ''Widget A'') AND old_price = 9.99    AND new_price = 24.99;',ARRAY[1],'trigger executed for new insertion row');

 
-- Finish the tests and clean up.
SELECT * FROM FINISH();
ROLLBACK;

输出：

1..2
ok 1 - trigger exists on product table and insertions on price_changes
ok 2 - trigger executed for new insertion row

断言函数列表

下表列出了一些更广泛使用的断言函数。

PGTAP 功能	描述
has_table 和 hasnt_table	检查指定的表是否存在。
has_view	检查指定的视图是否存在。
has_materialized_view	检查指定的实例化视图是否存在。
有_索引	检查表是否有指定的索引。
has_relation	检查指定的关系是否存在，可以是表、索引或序列。
函数_返回	检查特定函数是否返回特定的数据类型。
is_definer	检查函数是否是安全定义器。
col_is_pk	检查某列是否是表的主键列。
col_isnt_pk	检查列是否不是表的主键。
col_isnt_fk	检查列是否不是表的外键。
has_column	检查表中是否包含一列。
hasnt_column	检查表中是否不包含列。
policy_cmd_is	检查指定的行级安全策略是否存在。
policy_roles_are	检查行级安全策略是否适用于所有用户。如果指定了行级安全策略所应用的所有用户，则该函数返回 TRUE。
policies_are	检查表是否包含行级安全策略。

有关所有断言函数的完整列表，请参阅 pgTap 文档。

结论

在这篇文章中，我们探讨了 PostgreSQL 中用户定义函数的各种单元测试选项，并演示了如何使用 PgTap 框架创建示例测试。通过整合 pgTap，您可以显著增强数据库的可靠性、稳定性和安全性。pgTap 有助于提高代码质量，同时简化测试流程。有了它，开发人员可以使用 psql 脚本或 Xunit 风格的函数编写测试，涵盖函数、触发器和索引列等关键方面。

有关使用 PgTap 的更多信息，请参阅 PgTap 文档。如果您有任何意见或疑问，请在评论部分留言。