#!/usr/local/bin/perl
# Test row insertion and retrieval
use strict;
use warnings;
use Test::More;
BEGIN { use_ok ("DBI") }
do "t/lib.pl";
my @tbl_def = (
[ "id", "INTEGER", 4, 0 ],
[ "name", "CHAR", 64, 0 ],
[ "val", "INTEGER", 4, 0 ],
[ "txt", "CHAR", 64, 0 ],
);
ok (my $dbh = Connect (), "connect");
ok (my $tbl = FindNewTable ($dbh), "find new test table");
$tbl ||= "tmp99";
eval {
local $SIG{__WARN__} = sub {};
$dbh->do ("drop table $tbl");
};
like (my $def = TableDefinition ($tbl, @tbl_def),
qr{^create table $tbl}i, "table definition");
my $sz = 0;
ok ($dbh->do ($def), "create table");
my $tbl_file = DbFile ($tbl);
ok ($sz = -s $tbl_file, "file exists");
ok ($dbh->do ("insert into $tbl values ".
"(1, 'Alligator Descartes', 1111, 'Some Text')"), "insert");
ok ($sz < -s $tbl_file, "file grew");
$sz = -s $tbl_file;
ok ($dbh->do ("insert into $tbl (id, name, val, txt) values ".
"(2, 'Crocodile Dundee', 2222, 'Down Under')"), "insert with field names");
ok ($sz < -s $tbl_file, "file grew");
ok (my $sth = $dbh->prepare ("select * from $tbl where id = 1"), "prepare");
is (ref $sth, "DBI::st", "handle type");
ok ($sth->execute, "execute");
ok (my $row = $sth->fetch, "fetch");
is (ref $row, "ARRAY", "returned a list");
is ($sth->errstr, undef, "no error");
is_deeply ($row, [ 1, "Alligator Descartes", 1111, "Some Text" ], "content");
ok ($sth->finish, "finish");
undef $sth;
# Try some other capitilization
ok ($dbh->do ("DELETE FROM $tbl WHERE id = 1"), "delete");
# Now, try SELECT'ing the row out. This should fail.
ok ($sth = $dbh->prepare ("select * from $tbl where id = 1"), "prepare");
is (ref $sth, "DBI::st", "handle type");
ok ($sth->execute, "execute");
is ($sth->fetch, undef, "fetch");
is ($sth->errstr, undef, "error"); # ???
ok ($sth->finish, "finish");
undef $sth;
ok ($sth = $dbh->prepare ("insert into $tbl values (?, ?, ?, ?)"), "prepare insert");
ok ($sth->execute (3, "Babar", 3333, "Elephant"), "insert prepared");
ok ($sth->finish, "finish");
undef $sth;
ok ($sth = $dbh->prepare ("insert into $tbl (id, name, val, txt) values (?, ?, ?, ?)"), "prepare insert with field names");
ok ($sth->execute (4, "Vischje", 33, "in het riet"), "insert prepared");
ok ($sth->finish, "finish");
undef $sth;
ok ($dbh->do ("delete from $tbl"), "delete all");
ok ($dbh->do ("insert into $tbl (id) values (0)"), "insert just one field");
{ local (@ARGV) = DbFile ($tbl);
my @csv = <>;
s/\r?\n\Z// for @csv;
is (scalar @csv, 2, "Just two lines");
is ($csv[0], "id,name,val,txt", "header");
is ($csv[1], "0,,,", "data");
}
ok ($dbh->do ("drop table $tbl"), "drop");
ok ($dbh->disconnect, "disconnect");
done_testing ();