#!/usr/bin/env qore

# database test script

# databases users must be able to create and destroy tables and procedures, etc

# in order to execute all tests

%requires qore >= 0.9.4

%requires QUnit

%prepend-module-path "${SCRIPT_DIR}/../build"

%requires pgsql

%modern

%exec-class PgsqlTest

class PgsqlTest inherits QUnit::Test {

public {

}

private {

hash o;

int errors;

int test_count;

string connstr;

const object_map = (

"pgsql": (

"tables": pgsql_tables,

),

);

const pgsql_tables = (

"family" : "create table family (

family_id numeric(14) not null,

name varchar(80) not null )",

"people" : "create table people (

person_id int not null,

family_id int not null,

name varchar(250) not null,

dob date not null )",

"attributes" : "create table attributes (

person_id int not null,

attribute varchar(80) not null,

value varchar(160) not null)",

"data_test" : "create table data_test (

int2_f smallint not null,

int4_f integer not null,

int8_f int8 not null,

bool_f boolean not null,

float4_f real not null,

float8_f double precision not null,

number_f numeric(16,3) not null,

number2_f numeric not null,

number3_f numeric not null,

number4_f numeric not null,

number5_f numeric not null,

number6_f numeric not null,

number7_f numeric not null,

number8_f numeric not null,

money_f money not null,

text_f text not null,

varchar_f varchar(40) not null,

char_f char(40) not null,

name_f name not null,

date_f date not null,

interval_f interval not null,

time_f time not null,

timetz_f time with time zone not null,

timestamp_f timestamp not null,

timestamptz_f timestamp with time zone not null,

bytea_f bytea not null,

xml xml not null,

json json not null,

jsonb jsonb not null

)",

"geometric_test" : "create table geometric_test (

id serial primary key,

point_f point,

line_f line,

lseg_f lseg,

box_f box,

path_f path,

polygon_f polygon,

circle_f circle

)",

"network_test" : "create table network_test (

id serial primary key,

macaddr_f macaddr,

inet_f inet,

cidr_f cidr

)",

"array_test" : "create table array_test (

id serial primary key,

int_arr integer[],

text_arr text[],

float_arr double precision[],

bool_arr boolean[],

timestamp_arr timestamp with time zone[]

)",

"corner_cases" : "create table corner_cases (

id serial primary key,

nullable_text text,

nullable_int integer,

nullable_float double precision,

nullable_bool boolean,

nullable_bytea bytea,

nullable_timestamp timestamp with time zone

)",

"boundary_test" : "create table boundary_test (

id serial primary key,

int2_val smallint,

int4_val integer,

int8_val bigint,

numeric_val numeric,

float4_val real,

float8_val double precision

)",

"multidim_array_test" : "create table multidim_array_test (

id serial primary key,

int2d integer[][],

text2d text[][]

)",

"bulk_test" : "create table bulk_test (

id serial primary key,

int_col bigint not null,

text_col text not null,

float_col double precision,

bool_col boolean,

ts_col timestamp with time zone,

bin_col bytea

)",

);

const family_hash = (

"Jones" : (

"people" : (

"John" : (

"dob" : 1995-03-23,

"eyes" : "brown",

"hair" : "brown" ),

"Alan" : (

"dob" : 1992-06-04,

"eyes" : "blue",

"hair" : "black" ) ) ),

"Smith" : (

"people" : (

"Arnie" : (

"dob" : 1983-05-13,

"eyes" : "hazel",

"hair" : "blond" ),

"Carol" : (

"dob" : 2003-07-23,

"eyes" : "grey",

"hair" : "brown" ),

"Isaac" : (

"dob" : 2000-04-04,

"eyes" : "green",

"hair" : "red" ),

"Bernard" : (

"dob" : 1960-02-27,

"eyes" : "brown",

"hair" : "brown" ),

"Sylvia" : (

"dob" : 1994-11-10,

"eyes" : "blue",

"hair" : "blond",

),

),

),

);

const Args = {

"int2_f" : 258,

"int4_f" : 233932,

"int8_f" : 239392939458,

"bool_f" : True,

"float4_f" : 21.3444,

"float8_f" : 49394.23423491,

"number_f" : 7235634215.3250n,

"number2_f" : 999999999999999999999999999999n,

"number3_f" : MAXINT,

"number4_f" : MAXINT.toNumber() + 1,

"number5_f" : MININT,

"number6_f" : MININT.toNumber() - 1,

"number7_f" : 0.000042n,

"number8_f" : 0.015643n,

"money_f" : pgsql_bind(PG_TYPE_CASH, "400.56"),

"text_f" : 'some text ',

"varchar_f" : 'varchar ',

"char_f" : 'char text',

"name_f" : 'name',

"date_f" : 2040-01-05,

"interval_f" : 6M + 3D + 2h + 45m + 15s,

"time_f" : 11:35:00,

"timetz_f" : 11:38:21-06,

"timestamp_f" : 2005-04-01T11:35:26,

"timestamptz_f" : 2005-04-01T11:35:26.259400+03,

"bytea_f" : <bead>,

"xml" : pgsql_bind(PG_TYPE_XML, "<a>1</a>"),

"json" : pgsql_bind(PG_TYPE_JSON, "{\"a\": 1}"),

"jsonb" : pgsql_bind(PG_TYPE_JSONB, "{\"a\": 1}"),

};

const OptionColumn = 20;

const MyOpts = Opts + (

"keep": "k,keep",

);

}

constructor() : Test("PgsqlTest", "1.0", \ARGV, MyOpts) {

*string cs = ENV.QORE_DB_CONNSTR_PGSQL ?? shift ARGV;

if (!cs) {

stderr.printf("QORE_DB_CONNSTR_PGSQL environment variable not set; cannot run tests\n");

return;

}

connstr = cs;

Datasource db(connstr);

if (db.getDriverName() != "pgsql") {

stderr.printf("DB is %y; can only test \"pgsql\" with this script\n", db.getDriverName());

return;

}

createDataModel(db);

addTestCase("context test case", \contextTests());

addTestCase("transaction test case", \transactionTests());

addTestCase("pgsql test case", \pgsqlTests());

addTestCase("select row test", \selectRowTest());

addTestCase("alterr exceptions", \alterrExceptionTest());

addTestCase("geometric types test", \geometricTypesTest());

addTestCase("network types test", \networkTypesTest());

addTestCase("array binding test", \arrayBindingTest());

addTestCase("prepared statement test", \preparedStatementTest());

addTestCase("corner cases test", \cornerCasesTest());

addTestCase("negative tests", \negativeTests());

addTestCase("multi-dimensional array test", \multiDimArrayTest());

addTestCase("boundary values test", \boundaryValuesTest());

addTestCase("array bind (NT_LIST) test", \arrayBindListTest());

addTestCase("COPY protocol test", \copyProtocolTest());

addTestCase("pgvector type test", \pgvectorTest());

set_return_value(main());

}

createDataModel(Datasource db) {

purgeTestData(db);

string driver = db.getDriverName();

# create tables

hash tables = object_map{driver}.tables;

foreach string table in (keys tables) {

db.exec(tables{table});

}

# create procedures if any

foreach string proc in (keys object_map{driver}.procs) {

db.exec(object_map{driver}.procs{proc});

}

# create functions if any

foreach string func in (keys object_map{driver}.funcs) {

db.exec(object_map{driver}.funcs{func});

}

db.exec("insert into family values ( 1, 'Smith' )");

db.exec("insert into family values ( 2, 'Jones' )");

# we insert the dates here using binding by value so we don't have

# to worry about each database's specific date format

db.exec("insert into people values ( 1, 1, 'Arnie', %v)", 1983-05-13);

db.exec("insert into people values ( 2, 1, 'Sylvia', %v)", 1994-11-10);

db.exec("insert into people values ( 3, 1, 'Carol', %v)", 2003-07-23);

db.exec("insert into people values ( 4, 1, 'Bernard', %v)", 1960-02-27);

db.exec("insert into people values ( 5, 1, 'Isaac', %v)", 2000-04-04);

db.exec("insert into people values ( 6, 2, 'Alan', %v)", 1992-06-04);

db.exec("insert into people values ( 7, 2, 'John', %v)", 1995-03-23);

db.exec("insert into attributes values ( 1, 'hair', 'blond' )");

db.exec("insert into attributes values ( 1, 'eyes', 'hazel' )");

db.exec("insert into attributes values ( 2, 'hair', 'blond' )");

db.exec("insert into attributes values ( 2, 'eyes', 'blue' )");

db.exec("insert into attributes values ( 3, 'hair', 'brown' )");

db.exec("insert into attributes values ( 3, 'eyes', 'grey')");

db.exec("insert into attributes values ( 4, 'hair', 'brown' )");

db.exec("insert into attributes values ( 4, 'eyes', 'brown' )");

db.exec("insert into attributes values ( 5, 'hair', 'red' )");

db.exec("insert into attributes values ( 5, 'eyes', 'green' )");

db.exec("insert into attributes values ( 6, 'hair', 'black' )");

db.exec("insert into attributes values ( 6, 'eyes', 'blue' )");

db.exec("insert into attributes values ( 7, 'hair', 'brown' )");

db.exec("insert into attributes values ( 7, 'eyes', 'brown' )");

db.commit();

}

globalTearDown() {

if (m_options.keep) {

if (m_options.verbose)

printf("not deleting test DB data\n");

}

else

purgeTestData();

}

private usageIntern() {

TestReporter::usageIntern(OptionColumn);

printOption("-k,--keep","do not delete test data in DB", OptionColumn);

}

private purgeTestData(*Datasource db) {

if (!db)

db = new Datasource(connstr);

string driver = db.getDriverName();

# drop the tables and ignore exceptions

foreach string table in (keys object_map{driver}.tables) {

try {

db.exec("drop table " + table);

db.commit();

} catch () {

db.commit();

}

}

# drop procedures and ignore exceptions

foreach string proc in (keys object_map{driver}.procs) {

*string cmd = object_map{driver}.drop_proc_cmd;

if (!exists cmd)

cmd = "drop procedure";

try {

db.exec(cmd + " " + proc);

db.commit();

} catch () {

db.commit();

}

}

# drop functions and ignore exceptions

foreach string func in (keys object_map{driver}.funcs) {

*string cmd = object_map{driver}.drop_func_cmd;

if (!cmd)

cmd = "drop function";

try {

db.exec(cmd + " " + func);

db.commit();

} catch () {

db.commit();

}

}

}

contextTests() {

Datasource db(connstr);

# first we select all the data from the tables and then use

# context statements to order the output hierarchically

# context statements are most useful when a set of queries can be executed once

# and the results processed many times by creating "views" with context statements

hash people = db.select("select * from people");

hash attributes = db.select("select * from attributes");

# in this test, we create a big hash structure out of the queries executed above

# and compare it at the end to the expected result

# display each family sorted by family name

hash fl;

context family (db.select("select * from family")) sortBy (%name) {

hash pl;

# display people, sorted by eye color, descending

context people (people)

sortDescendingBy (find %value in attributes

where (%attribute == "eyes"

&& %person_id == %people:person_id))

where (%family_id == %family:family_id) {

hash al;

context (attributes) sortBy (%attribute) where (%person_id == %people:person_id) {

al.%attribute = %value;

}

# leave out the ID fields and name from hash under name; subtracting a

# string from a hash removes that key from the result

# this is "doing it the hard way", there is only one key left,

# "dob", then attributes are added directly into the person hash

pl.%name = %% - "family_id" - "person_id" - "name" + al;

}

# leave out family_id and name fields (leaving an empty hash)

fl.%name = %% - "family_id" - "name" + ( "people" : pl );

}

# test context ordering

assertEq(("Jones", "Smith"), fl.keys());

assertEq(("Arnie", "Carol", "Isaac", "Bernard", "Sylvia"), fl.Smith.people.keys());

# test entire context value

assertEq(family_hash, fl);

# issue #4249

SQLStatement stmt = db.getSQLStatement();

on_exit stmt.rollback();

stmt.prepare("select * from family where family_id = 1");

assertTrue(stmt.next());

hash<auto> row = stmt.fetchRow();

assertEq("Smith", row.name);

assertEq(1, row.family_id);

row = stmt.fetchRow();

assertEq("Smith", row.name);

assertEq(1, row.family_id);

}

testTimeout(Datasource db, Counter c) {

db.setTransactionLockTimeout(1ms);

try {

# this should cause a TRANSACTION-LOCK-TIMEOUT exception to be thrown

db.exec("insert into family values (3, 'Test')\n");

assertEq(True, False);

db.exec("delete from family where name = 'Test'");

} catch (hash ex) {

assertEq(True, True);

}

# signal parent thread to continue

c.dec();

}

transactionTests() {

Datasource db(connstr);

Datasource ndb(connstr);

# first, we insert a new row into "family" but do not commit it

int rows = db.exec("insert into family values (3, 'Test')\n");

assertEq(1, rows);

*string r = ndb.selectRow("select name from family where family_id = 3").name;

assertEq(NOTHING, r);

# now we verify that the new row is visible to the inserting datasource

r = db.selectRow("select name from family where family_id = 3").name;

assertEq("Test", r);

# test datasource timeout

# this Counter variable will allow the parent thread to sleep

# until the child thread times out

Counter c(1);

background testTimeout(db, c);

# wait for child thread to time out

c.waitForZero();

# now, we commit the transaction

db.commit();

# now we verify that the new row is visible in the other datasource

r = ndb.selectRow("select name from family where family_id = 3").name;

assertEq("Test", r);

# now we delete the row we inserted (so we can repeat the test)

rows = ndb.exec("delete from family where family_id = 3");

assertEq(1, rows);

ndb.commit();

}

pgsqlTests() {

Datasource db(connstr);

string vstr = foldl $1 + ", " + $2, (map "%v", xrange(Args.size()));

{

string sql = sprintf("insert into data_test values (%s)", vstr);

on_error printf("sql: %y args: (%d) %y\n", sql, Args.size(), Args);

db.vexec(sql, Args.values());

}

hash q = db.selectRow("select * from data_test");

hash args = Args;

# fix values where we know the return type is different

args.money_f = 400.56;

args.number_f = 7235634215.325n;

args.interval_f = 6M + 3D + 9915s;

args.xml = Args.xml."^value^";

args.json = Args.json."^value^";

args.jsonb = Args.jsonb."^value^";

# rounding errors can happen in float4

q.float4_f = round(q.float4_f);

args.float4_f = round(args.float4_f);

# remove values where we know they won't match

# timestamptz is converted to GMT by the server

delete q.timestamptz_f;

# issue 269:

delete q.money_f;

# compare each value

map assertEq(args.($1.key), $1.value), q.pairIterator();

# make sure we can select with a bound timestamp value

hash<auto> row = db.selectRow("select * from data_test where timestamptz_f = %v", Args.timestamptz_f);

assertEq(Args.timestamptz_f, row.timestamptz_f);

db.commit();

}

selectRowTest() {

Datasource db(connstr);

on_exit

db.rollback();

string vstr = foldl $1 + ", " + $2, (map "%v", xrange(Args.size()));

db.vexec("insert into data_test values (" + vstr + ")", Args.values());

assertThrows("DBI-SELECT-ROW-ERROR", \db.selectRow(), "select * from data_test");

}

private alterrExceptionTest() {

Datasource ds(connstr);

on_exit ds.rollback();

try {

ds.selectRow("select * from dalhlhwqsadcnfhe"); # table does not exist

} catch (hash ex) {

assertEq(True, ex.hasKey("arg"));

hash arg = ex.arg;

assertEq(True, arg.hasKey("alterr"));

assertEq(True, arg.hasKey("alterr_diag"));

assertEq("42P01", arg.alterr);

assertEq(True, arg.alterr_diag =~ /dalhlhwqsadcnfhe/);

}

}

private geometricTypesTest() {

Datasource db(connstr);

on_exit db.rollback();

# Test point type

db.exec("insert into geometric_test (point_f) values ('(1.5,2.5)')");

hash<auto> row = db.selectRow("select * from geometric_test where id = currval('geometric_test_id_seq')");

assertEq("1.5,2.5", row.point_f);

# Test line segment type

db.exec("insert into geometric_test (lseg_f) values ('[(0,0),(1,1)]')");

row = db.selectRow("select lseg_f from geometric_test where lseg_f is not null");

assertEq(True, row.lseg_f =~ /\(0,0\).*\(1,1\)/);

# Test box type

db.exec("insert into geometric_test (box_f) values ('((0,0),(2,2))')");

row = db.selectRow("select box_f from geometric_test where box_f is not null");

assertEq(True, row.box_f =~ /\(2,2\).*\(0,0\)/);

# Test path type (open)

db.exec("insert into geometric_test (path_f) values ('[(0,0),(1,1),(2,0)]')");

row = db.selectRow("select path_f from geometric_test where path_f is not null");

assertEq(True, row.path_f =~ /^\[/); # open path starts with [

# Test polygon type

db.exec("insert into geometric_test (polygon_f) values ('((0,0),(1,1),(1,0))')");

row = db.selectRow("select polygon_f from geometric_test where polygon_f is not null");

assertEq(True, row.polygon_f =~ /^\(/);

# Test circle type

db.exec("insert into geometric_test (circle_f) values ('<(1,1),5>')");

row = db.selectRow("select circle_f from geometric_test where circle_f is not null");

assertEq(True, row.circle_f =~ /<\(1,1\),5>/);

}

private networkTypesTest() {

Datasource db(connstr);

on_exit db.rollback();

# Test MAC address type

db.exec("insert into network_test (macaddr_f) values ('08:00:2b:01:02:03')");

hash<auto> row = db.selectRow("select * from network_test where macaddr_f is not null");

assertEq("08:00:2b:01:02:03", row.macaddr_f);

# Test inet type (IPv4)

db.exec("insert into network_test (inet_f) values ('192.168.1.100/24')");

row = db.selectRow("select inet_f from network_test where inet_f is not null");

assertEq("192.168.1.100/24", row.inet_f);

# Test cidr type (IPv4)

db.exec("insert into network_test (cidr_f) values ('192.168.1.0/24')");

row = db.selectRow("select cidr_f from network_test where cidr_f is not null");

assertEq("192.168.1.0/24", row.cidr_f);

# Test inet type (IPv6) - PostgreSQL expands :: notation

db.exec("insert into network_test (inet_f) values ('::1/128')");

row = db.selectRow("select inet_f from network_test where inet_f = '::1/128'::inet");

assertEq("0:0:0:0:0:0:0:1/128", row.inet_f);

}

private arrayBindingTest() {

Datasource db(connstr);

on_exit db.rollback();

# Test integer array binding

list<int> intArr = (1, 2, 3, 4, 5);

db.exec("insert into array_test (int_arr) values (%v)", pgsql_bind_array(intArr));

hash<auto> row = db.selectRow("select int_arr from array_test where int_arr is not null");

assertEq(intArr, row.int_arr);

# Test text array binding

list<string> textArr = ("hello", "world", "test");

db.exec("insert into array_test (text_arr) values (%v)", pgsql_bind_array(textArr));

row = db.selectRow("select text_arr from array_test where text_arr is not null order by id desc limit 1");

assertEq(textArr, row.text_arr);

# Test float array binding

list<float> floatArr = (1.1, 2.2, 3.3);

db.exec("insert into array_test (float_arr) values (%v)", pgsql_bind_array(floatArr));

row = db.selectRow("select float_arr from array_test where float_arr is not null order by id desc limit 1");

assertEq(3, row.float_arr.size());

# Test boolean array binding

list<bool> boolArr = (True, False, True);

db.exec("insert into array_test (bool_arr) values (%v)", pgsql_bind_array(boolArr));

row = db.selectRow("select bool_arr from array_test where bool_arr is not null order by id desc limit 1");

assertEq(boolArr, row.bool_arr);

# Test timestamp array binding

list<date> tsArr = (2023-01-01T10:00:00Z, 2023-06-15T15:30:00Z);

db.exec("insert into array_test (timestamp_arr) values (%v)", pgsql_bind_array(tsArr));

row = db.selectRow("select timestamp_arr from array_test where timestamp_arr is not null order by id desc limit 1");

assertEq(2, row.timestamp_arr.size());

}

private list<auto> asList(auto value) {

return value.type() == Type::List ? value : value.toList();

}

private preparedStatementTest() {

Datasource db(connstr);

on_exit db.rollback();

# Test basic prepared statement

SQLStatement stmt = db.getSQLStatement();

on_exit stmt.rollback();

stmt.prepare("select * from family where family_id = %v");

stmt.bind(1);

assertTrue(stmt.next());

hash<auto> row = stmt.fetchRow();

assertEq("Smith", row.name);

# Test describe() method

stmt.prepare("select family_id, name from family");

stmt.exec();

hash<auto> desc = stmt.describe();

assertEq(True, desc.hasKey("family_id"));

assertEq(True, desc.hasKey("name"));

# family_id is numeric(14), so type is NT_NUMBER

assertEq(NT_NUMBER, desc.family_id.type);

assertEq(NT_STRING, desc.name.type);

# Test fetchRows() method

stmt.prepare("select * from family order by family_id");

stmt.exec();

list<auto> rows = stmt.fetchRows(-1);

assertEq(2, rows.size());

assertEq("Smith", rows[0].name);

assertEq("Jones", rows[1].name);

# Test fetchColumns() method

stmt.prepare("select * from family order by family_id");

stmt.exec();

hash<auto> cols = stmt.fetchColumns(-1);

assertEq(2, cols.family_id.size());

assertEq(2, cols.name.size());

assertEq((1, 2), cols.family_id);

assertEq(("Smith", "Jones"), cols.name);

ColumnarResult dense_direct = db.selectColumnar("

select 1::bigint as id, 1.5::double precision as score, true as active, 'Ada'::text as name

union all

select 2::bigint as id, 2.25::double precision as score, false as active, 'Ben'::text as name

order by id");

assertEq("buffer<int64>", dense_direct.getColumn("id").fullType());

assertEq("buffer<float64>", dense_direct.getColumn("score").fullType());

assertEq("buffer<bool>", dense_direct.getColumn("active").fullType());

assertEq("buffer<string>", dense_direct.getColumn("name").fullType());

assertEq((1, 2), asList(dense_direct.getColumn("id")));

assertEq((1.5, 2.25), asList(dense_direct.getColumn("score")));

assertEq((True, False), asList(dense_direct.getColumn("active")));

assertEq(("Ada", "Ben"), asList(dense_direct.getColumn("name")));

ColumnarResult nullable_strings = db.selectColumnar("

select null::text as name

union all select 'Ada'::text as name

order by name nulls first");

assertEq("buffer<*string>", nullable_strings.getColumn("name").fullType());

assertEq((NOTHING, "Ada"), asList(nullable_strings.getColumn("name")));

ColumnarResult logical_dense = db.selectColumnar("

select cast(1.23 as numeric(12,2)) as amount,

timestamp with time zone '2023-01-01 00:00:00+00' as created_at,

interval '2 seconds' as elapsed

union all

select cast(null as numeric(12,2)) as amount,

null::timestamp with time zone as created_at,

null::interval as elapsed");

list<hash<auto>> logical_schema = logical_dense.schemaV2();

assertEq("buffer<*decimal128>", logical_dense.getColumn("amount").fullType());

assertEq("buffer<*int64>", logical_dense.getColumn("created_at").fullType());

assertEq("buffer<*int64>", logical_dense.getColumn("elapsed").fullType());

assertEq((1.23n, NOTHING), asList(logical_dense.getColumn("amount")));

assertEq("decimal128", logical_schema[0].kind);

assertEq("numeric(12,2)", logical_schema[0].native_type);

assertEq(12, logical_schema[0].precision);

assertEq(2, logical_schema[0].scale);

assertEq("timestamp", logical_schema[1].kind);

assertEq("duration", logical_schema[2].kind);

# Test fetchColumnar() method, including -1 after a partial fetch

stmt.prepare("select * from family order by family_id");

stmt.exec();

ColumnarResult first_block = stmt.fetchColumnar(1);

hash<auto> first_cols = first_block.toColumnHash();

assertEq("buffer<int64>", first_block.getColumn("family_id").fullType());

assertEq(1, first_cols.family_id.size());

assertEq((1,), asList(first_cols.family_id));

ColumnarResult remaining_block = stmt.fetchColumnar(-1);

hash<auto> remaining_cols = remaining_block.toColumnHash();

assertEq("buffer<int64>", remaining_block.getColumn("family_id").fullType());

assertEq(1, remaining_cols.family_id.size());

assertEq((2,), asList(remaining_cols.family_id));

stmt.prepare("

select 1::bigint as id, 1.5::double precision as score, true as active, 'Ada'::text as name

union all

select 2::bigint as id, 2.25::double precision as score, false as active, 'Ben'::text as name

order by id");

stmt.exec();

ColumnarResult dense_first = stmt.fetchColumnar(1);

ColumnarResult dense_rest = stmt.fetchColumnar(-1);

assertEq("buffer<int64>", dense_first.getColumn("id").fullType());

assertEq("buffer<string>", dense_first.getColumn("name").fullType());

assertEq("buffer<int64>", dense_rest.getColumn("id").fullType());

assertEq("buffer<string>", dense_rest.getColumn("name").fullType());

assertEq((1,), asList(dense_first.getColumn("id")));

assertEq(("Ada",), asList(dense_first.getColumn("name")));

assertEq((2,), asList(dense_rest.getColumn("id")));

assertEq(("Ben",), asList(dense_rest.getColumn("name")));

# Test re-execution with different parameters

stmt.prepare("select name from family where family_id = %v");

stmt.bind(1);

assertTrue(stmt.next());

row = stmt.fetchRow();

assertEq("Smith", row.name);

# Re-execute with different parameter

stmt.bind(2);

stmt.exec();

assertTrue(stmt.next());

row = stmt.fetchRow();

assertEq("Jones", row.name);

# Test affected rows

SQLStatement stmt2 = db.getSQLStatement();

on_exit stmt2.rollback();

stmt2.prepare("insert into family values (99, 'TestFamily')");

stmt2.exec();

assertEq(1, stmt2.affectedRows());

}

private cornerCasesTest() {

Datasource db(connstr);

on_exit db.rollback();

# Test NULL values for all types

db.exec("insert into corner_cases (nullable_text, nullable_int, nullable_float, nullable_bool, nullable_bytea, nullable_timestamp) values (null, null, null, null, null, null)");

hash<auto> row = db.selectRow("select * from corner_cases where id = currval('corner_cases_id_seq')");

# NULL values from PostgreSQL - check they have no value (NULL or NOTHING)

assertFalse(boolean(row.nullable_text));

assertFalse(boolean(row.nullable_int));

assertFalse(boolean(row.nullable_float));

assertFalse(boolean(row.nullable_bool));

assertFalse(boolean(row.nullable_bytea));

assertFalse(boolean(row.nullable_timestamp));

# Test empty string

db.exec("insert into corner_cases (nullable_text) values ('')");

row = db.selectRow("select nullable_text from corner_cases where nullable_text = ''");

assertEq("", row.nullable_text);

# Test special characters in text

string specialChars = "Hello\tWorld\nLine2\r\nLine3";

db.exec("insert into corner_cases (nullable_text) values (%v)", specialChars);

row = db.selectRow("select nullable_text from corner_cases where nullable_text like 'Hello%World%'");

assertEq(specialChars, row.nullable_text);

# Test short string parameter binding

list<string> shortTexts = ("Alice", "Bob", "Carol");

foreach string text in (shortTexts) {

db.exec("insert into corner_cases (nullable_text) values (%v)", text);

}

hash<auto> shortRows = db.select("select nullable_text from corner_cases "

+ "where nullable_text in ('Alice', 'Bob', 'Carol') order by id");

assertEq(shortTexts, shortRows.nullable_text);

# Test multiple short string arguments in one statement. This catches

# dangling libpq parameter buffers when short strings are stored inline.

row = db.selectRow("select %v::text as a, %v::text as b, %v::text as c, %v::text as d, "

+ "%v::text as e, %v::text as f", "aa", "bb", "cc", "dd", "ee", "-");

assertEq("aa", row.a);

assertEq("bb", row.b);

assertEq("cc", row.c);

assertEq("dd", row.d);

assertEq("ee", row.e);

assertEq("-", row.f);

row = db.selectRow("select %v as a, %v as b", pgsql_bind(PG_TYPE_TEXT, "x"), pgsql_bind(PG_TYPE_TEXT, "-"));

assertEq("x", row.a);

assertEq("-", row.b);

# Test Unicode characters

string unicodeText = "日本語テスト emoji: 🎉";

db.exec("insert into corner_cases (nullable_text) values (%v)", unicodeText);

row = db.selectRow("select nullable_text from corner_cases where nullable_text like '%日本語%'");

assertEq(unicodeText, row.nullable_text);

# Test zero values

db.exec("insert into corner_cases (nullable_int, nullable_float) values (0, 0.0)");

row = db.selectRow("select nullable_int, nullable_float from corner_cases where nullable_int = 0");

assertEq(0, row.nullable_int);

assertEq(0.0, row.nullable_float);

# Test empty binary

db.exec("insert into corner_cases (nullable_bytea) values (%v)", <00>);

row = db.selectRow("select nullable_bytea from corner_cases where length(nullable_bytea) = 1 order by id desc limit 1");

assertEq(1, row.nullable_bytea.size());

# Test very long text

string longText = strmul("x", 10000);

db.exec("insert into corner_cases (nullable_text) values (%v)", longText);

row = db.selectRow("select nullable_text from corner_cases where length(nullable_text) = 10000");

assertEq(10000, row.nullable_text.size());

# Test epoch timestamp

date epochDate = 1970-01-01T00:00:00Z;

db.exec("insert into corner_cases (nullable_timestamp) values (%v)", epochDate);

row = db.selectRow("select nullable_timestamp from corner_cases where nullable_timestamp = '1970-01-01 00:00:00+00'::timestamptz");

assertEq(1970, get_years(row.nullable_timestamp));

}

private negativeTests() {

# Test invalid pgsql_bind() with zero type (no DB needed)

assertThrows("PGSQL-BIND-ERROR", \pgsql_bind(), (0, "test"));

{

Datasource db(connstr);

on_exit db.rollback();

# Test non-existent table

assertThrows("DBI:PGSQL:ERROR", \db.selectRow(), "select * from nonexistent_table_xyz");

}

{

Datasource db(connstr);

on_exit db.rollback();

# Test non-existent column

assertThrows("DBI:PGSQL:ERROR", \db.selectRow(), "select nonexistent_column from family");

}

{

Datasource db(connstr);

on_exit db.rollback();

# Test invalid SQL syntax

assertThrows("DBI:PGSQL:ERROR", \db.exec(), "SELEKT * FROM family");

}

{

Datasource db(connstr);

on_exit db.rollback();

# Test type mismatch - trying to insert string into integer column

assertThrows("DBI:PGSQL:ERROR", \db.exec(), "insert into boundary_test (int4_val) values ('not_a_number')");

}

{

Datasource db(connstr);

on_exit db.rollback();

# Test constraint violation - duplicate primary key

db.exec("insert into corner_cases (id) values (99999)");

assertThrows("DBI:PGSQL:ERROR", \db.exec(), "insert into corner_cases (id) values (99999)");

}

{

Datasource db(connstr);

on_exit db.rollback();

# Test division by zero

assertThrows("DBI:PGSQL:ERROR", \db.selectRow(), "select 1/0");

}

{

Datasource db(connstr);

on_exit db.rollback();

# Test selectRow with multiple rows should throw error

db.exec("insert into corner_cases (nullable_text) values ('neg_row1')");

db.exec("insert into corner_cases (nullable_text) values ('neg_row2')");

assertThrows("DBI-SELECT-ROW-ERROR", \db.selectRow(), "select * from corner_cases where nullable_text in ('neg_row1', 'neg_row2')");

}

}

private multiDimArrayTest() {

Datasource db(connstr);

on_exit db.rollback();

# Test retrieving 2D integer array (binding multi-dim arrays requires SQL literals)

db.exec("insert into multidim_array_test (int2d) values ('{{1,2,3},{4,5,6}}')");

hash<auto> row = db.selectRow("select int2d from multidim_array_test where int2d is not null order by id desc limit 1");

assertEq(2, row.int2d.size());

assertEq(3, row.int2d[0].size());

assertEq(1, row.int2d[0][0]);

assertEq(6, row.int2d[1][2]);

# Test retrieving 2D text array

db.exec("insert into multidim_array_test (text2d) values ('{{\"a\",\"b\"},{\"c\",\"d\"}}')");

row = db.selectRow("select text2d from multidim_array_test where text2d is not null order by id desc limit 1");

assertEq(2, row.text2d.size());

assertEq("a", row.text2d[0][0]);

assertEq("d", row.text2d[1][1]);

# Test 3x3 array

db.exec("insert into multidim_array_test (int2d) values ('{{10,20,30},{40,50,60},{70,80,90}}')");

row = db.selectRow("select int2d from multidim_array_test where int2d[1][1] = 10");

assertEq(3, row.int2d.size());

assertEq(3, row.int2d[0].size());

assertEq(10, row.int2d[0][0]);

assertEq(90, row.int2d[2][2]);

}

private boundaryValuesTest() {

Datasource db(connstr);

on_exit db.rollback();

# Test smallint - typical range values

db.exec("insert into boundary_test (int2_val) values (%v)", -1000);

hash<auto> row = db.selectRow("select int2_val from boundary_test where int2_val = -1000");

assertEq(-1000, row.int2_val);

db.exec("insert into boundary_test (int2_val) values (%v)", 1000);

row = db.selectRow("select int2_val from boundary_test where int2_val = 1000");

assertEq(1000, row.int2_val);

# Test integer - typical and boundary values

db.exec("insert into boundary_test (int4_val) values (%v)", -1000000);

row = db.selectRow("select int4_val from boundary_test where int4_val = -1000000");

assertEq(-1000000, row.int4_val);

db.exec("insert into boundary_test (int4_val) values (%v)", 2147483647);

row = db.selectRow("select int4_val from boundary_test where int4_val = 2147483647");

assertEq(2147483647, row.int4_val);

# Test bigint - large values

db.exec("insert into boundary_test (int8_val) values (%v)", -1000000000000);

row = db.selectRow("select int8_val from boundary_test where int8_val = -1000000000000");

assertEq(-1000000000000, row.int8_val);

db.exec("insert into boundary_test (int8_val) values (%v)", 9000000000000000000);

row = db.selectRow("select int8_val from boundary_test where int8_val = 9000000000000000000");

assertEq(9000000000000000000, row.int8_val);

# Test very large numeric

number largeNum = 12345678901234567890123456789012345678901234567890n;

db.exec("insert into boundary_test (numeric_val) values (%v)", largeNum);

row = db.selectRow("select numeric_val from boundary_test where numeric_val > 10000000000000000000000000000000000000000000000000");

assertEq(largeNum, row.numeric_val);

# Test very small numeric (close to zero but within typical precision)

number smallNum = 0.00000001n;

db.exec("insert into boundary_test (numeric_val) values (%v)", smallNum);

row = db.selectRow("select numeric_val from boundary_test where numeric_val = %v", smallNum);

assertEq(smallNum, row.numeric_val);

# Test negative zero for float

db.exec("insert into boundary_test (float8_val) values (-0.0)");

row = db.selectRow("select float8_val from boundary_test where float8_val = 0 order by id desc limit 1");

assertEq(0.0, row.float8_val);

# Test float special values - infinity

db.exec("insert into boundary_test (float8_val) values ('Infinity'::double precision)");

row = db.selectRow("select float8_val from boundary_test where float8_val = 'Infinity'::double precision");

assertTrue(row.float8_val > 1e308);

# Test float special values - negative infinity

db.exec("insert into boundary_test (float8_val) values ('-Infinity'::double precision)");

row = db.selectRow("select float8_val from boundary_test where float8_val = '-Infinity'::double precision");

assertTrue(row.float8_val < -1e308);

}