PostgreSQL学习笔记----PATAP使用

本文链接：https://blog.youkuaiyun.com/qq_39182381/article/details/141131441

简介

pgTAP 是一个基于 TAP（Test Anything Protocol）的测试工具套件，专门用于测试 PostgreSQL 数据库。目前官方最新版本为2.0，开源仓库为2.1。

pgTAP 可以帮助开发人员编写单元测试和集成测试，以确保数据库中的函数、触发器、视图和其他数据库对象的正确性和可靠性。它提供了一套简洁而强大的函数和宏，用于断言和验证预期结果，比较数据、检查异常、执行查询和事务管理等。

参考地址：pgTAP：PostgreSQL 的单元测试
github链接：theory/pgtap: PostgreSQL Unit Testing Suite (github.com)

安装

下载源码包或者 git clone 源码
解压缩进行编译

make
make install
make installcheck

注意：
make 时必须保证 pg_config 命令可用(即配置对应的 $PATH )；
make installcheck 运行预置的测试用例必须启动 postgresql 集群。

pg_prove工具安装

cpan TAP::Parser::SourceHandler::pgTAP

若 cpan 未找到，使用 yum install perl-CPAN 命令进行安装。
若中间报其他错：缺啥装啥；没权限就给权限。

使用

pgTAP 以插件的形式存在，用户安装之后可以在 postgresql 数据库中直接创建该插件。插件创建后，相关函数就会被相应创建。

CREATE EXTENSION IF NOT EXISTS pgtap;

插件创建后，使用 \df 命令即可看到相关函数。具体的函数介绍见参考链接：pgTAP 1.2.0 documentation

编写测试用例

测试用例的编写可以使用参考链接学习 pgtap 的相关函数（因其就是集成的 gtest，所以和 gtest 用法比较相像，类似于 gtest 使用的断言）。

-- test_file.sql

-- Load the pgTAP extension
SET search_path = public, pgtap;
CREATE EXTENSION IF NOT EXISTS pgtap;

-- Create a test table
CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name TEXT
);

-- Test case to check if the table was created
BEGIN;
SELECT plan(1);
SELECT ok(relname = 'test_table', 'Table test_table exists') FROM pg_class WHERE relname = 'test_table';
ROLLBACK;

-- Test case to insert a row and check if it exists
BEGIN;
SELECT plan(2);
INSERT INTO test_table (name) VALUES ('John');
SELECT ok(EXISTS(SELECT 1 FROM test_table WHERE name = 'John'), 'Row with name John exists');
ROLLBACK;

-- Test case to update a row and check the updated value
BEGIN;
SELECT plan(2);
UPDATE test_table SET name = 'Jane' WHERE id = 1;
SELECT ok(EXISTS(SELECT 1 FROM test_table WHERE name = 'Jane'), 'Row with name Jane exists');
ROLLBACK;

-- Test case to delete a row and check if it's deleted
BEGIN;
SELECT plan(2);
DELETE FROM test_table WHERE id = 1;
SELECT ok(NOT EXISTS(SELECT 1 FROM test_table WHERE id = 1), 'Row with id 1 is deleted');
ROLLBACK;

-- Clean up: drop the test table
DROP TABLE test_table;

若 postgresql 集群已经安装 pgtap 插件，那么直接使用 ./psql -d postgres -U postgres -f test_file.sql 运行该用例即可。输出如下：

[uxdb@ux248:~/pginstall/postgresql-12.3/bin]$ ./psql -d postgres -U postgres -f test_file.sql

SET
psql:test_file.sql:5: 注意:  扩展 "pgtap" 已经存在,跳过
CREATE EXTENSION
CREATE TABLE
BEGIN
 plan
------
 1..1
(1 行记录)

               ok
--------------------------------
 ok 1 - Table test_table exists
(1 行记录)

ROLLBACK
BEGIN
 plan
------
 1..2
(1 行记录)

INSERT 0 1
                ok
----------------------------------
 ok 1 - Row with name John exists
(1 行记录)

ROLLBACK
BEGIN
 plan
------
 1..2
(1 行记录)

UPDATE 0
                      ok
----------------------------------------------
 not ok 1 - Row with name Jane exists        +
 # Failed test 1: "Row with name Jane exists"
 (1 行记录)

ROLLBACK
BEGIN
 plan
------
 1..2
(1 行记录)

DELETE 0
               ok
---------------------------------
 ok 1 - Row with id 1 is deleted
(1 行记录)

ROLLBACK
DROP TABLE

若操作系统已安装 pg_prove ，使用 pg_prove -d postgres test_file.sql 也可运行测试用例，将会生成相应的报告。报告如下：

[uxdb@ux248:~/pginstall/postgresql-12.3/bin]$ pg_prove -d postgres test_file.sql
test_file.sql .. psql:test_file.sql:5: 注意:  扩展 "pgtap" 已经存在,跳过
test_file.sql .. 1/1
# Failed test 1: "Row with name Jane exists"
test_file.sql .. All 1 subtests passed

Test Summary Report
-------------------
test_file.sql (Wstat: 0 Tests: 4 Failed: 3)
  Failed tests:  1, 1, 1
  Parse errors: More than one plan found in TAP output
                Tests out of sequence.  Found (1) but expected (2)
                More than one plan found in TAP output
                Tests out of sequence.  Found (1) but expected (3)
                More than one plan found in TAP output
Displayed the first 5 of 7 TAP syntax errors.
Re-run prove with the -p option to see them all.
Files=1, Tests=4,  0 wallclock secs ( 0.02 usr +  0.01 sys =  0.03 CPU)
Result: FAIL

注意：使用 pg_prove 工具时，sql 文件中只能有一个 plan，否则就会如上报错

正常输出如下：

test_file.sql .. psql:test_file.sql:5: 注意:  扩展 "pgtap" 已经存在,跳过
test_file.sql .. ok
All tests successful.
Files=1, Tests=1,  1 wallclock secs ( 0.01 usr +  0.00 sys =  0.01 CPU)
Result: PASS

增加新的用例 sql

只需要在 pgtap 代码目录下增加相应的 .sql 及其 .out 文件，即可使用 make installcheck 运行。

pgtap/test/expected/test_file.out
pgtap/test/sql/test_file.sql

自编写测试用例：

-- my_test.sql

-- Load the pgTAP extension
SET search_path = public, pgtap;
CREATE EXTENSION IF NOT EXISTS pgtap;

-- Create a test table
CREATE TABLE test1 (
    name TEXT
);
insert into test1 values('aaaaaaaa');
insert into test1 values('bbbbbbbb');
insert into test1 values('cccccccc');
checkpoint;

-- Test case to check if the table was created
BEGIN;
SELECT * FROM no_plan();
-- 查看物理文件是否内容是否正确
-- pg_read_binary_file返回的是16进制数据.
-- 字符a的ASCII值为97,转换为16进制对应61
select alike(pg_read_binary_file(pg_relation_filepath('test1'))::text, '%6161616161616161%'::text);
select unalike(pg_read_binary_file(pg_relation_filepath('test1'))::text, '%6161616161616162%'::text);
ROLLBACK;

DROP TABLE test1;

-- Create a test table
CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name TEXT
);

-- Test case to check if the table was created
BEGIN;
SELECT plan(1);
SELECT ok(relname = 'test_table', 'Table test_table exists') FROM pg_class WHERE relname = 'test_table';
ROLLBACK;

-- Test case to insert a row and check if it exists
BEGIN;
SELECT plan(2);
INSERT INTO test_table (name) VALUES ('John');
SELECT ok(EXISTS(SELECT 1 FROM test_table WHERE name = 'John'), 'Row with name John exists');
ROLLBACK;

-- Test case to update a row and check the updated value
BEGIN;
SELECT plan(2);
UPDATE test_table SET name = 'Jane' WHERE id = 1;
SELECT ok(EXISTS(SELECT 1 FROM test_table WHERE name = 'Jane'), 'Row with name Jane exists');
ROLLBACK;

-- Test case to delete a row and check if it's deleted
BEGIN;
SELECT plan(2);
DELETE FROM test_table WHERE id = 1;
SELECT ok(NOT EXISTS(SELECT 1 FROM test_table WHERE id = 1), 'Row with id 1 is deleted');
ROLLBACK;

-- Clean up: drop the test table
DROP TABLE test_table;