use Test::More;
use DBI;
use PGObject;
plan skip_all => 'Not set up for db tests' unless $ENV{DB_TESTING};
# Initial setup
my $dbh1 = DBI->connect('dbi:Pg:', 'postgres');
plan skip_all => 'Needs superuser connection for this test script' unless $dbh1;
plan tests => 17;
$dbh1->do('CREATE DATABASE pgobject_test_db');
my $dbh = DBI->connect('dbi:Pg:dbname=pgobject_test_db', 'postgres');
# Function to test:
$dbh->do(q|
CREATE OR REPLACE FUNCTION pgobject_order_test()
RETURNS TABLE(col1 int, col2 text, col3 int)
language sql as
$$
SELECT 1, 'group1', 1
union
select 2, 'group2', 2
union
select 3, 'group1', 2
union
select 4, 'group2', 1
$$;
|);
my @resultset = PGObject->call_procedure( # no order
funcname => 'pgobject_order_test',
dbh => $dbh,
);
is(scalar @resultset, 4, 'Unordered call successful, returned 4 rows');
@resultset = PGObject->call_procedure( # ordered by col1
funcname => 'pgobject_order_test',
dbh => $dbh,
orderby => ['col1'],
);
for my $num (1 .. 4){
is($resultset[$num - 1]->{col1}, $num, "simple ordering, correct result for item $num");
}
@resultset = PGObject->call_procedure( # ordered by col1
funcname => 'pgobject_order_test',
dbh => $dbh,
orderby => ['col1 asc'],
);
for my $num (1 .. 4){
is($resultset[$num - 1]->{col1}, $num, "simple explicit ordering, correct result for item $num");
}
@resultset = PGObject->call_procedure( # Reverse simple order
funcname => 'pgobject_order_test',
dbh => $dbh,
orderby => ['col1 desc'],
);
for my $num (0 .. 3){
is($resultset[$num]->{col1}, 4 - $num, "simple reverse ordering, correct result for item $num");
}
@resultset = PGObject->call_procedure( # Compound, complex ordering
funcname => 'pgobject_order_test',
dbh => $dbh,
orderby => ['col2 desc', 'col3 asc'],
);
my @expected = (4, 2, 1, 3);
for my $num (0 .. 3){
is($resultset[$num]->{col1}, $expected[$num], "simple reverse ordering, correct result for item $num");
}
# Teardown connections
$dbh->disconnect;
$dbh1->do('DROP DATABASE pgobject_test_db');
$dbh1->disconnect;