#!/usr/bin/perl
use strict;
use warnings;
use Test::More;
use DBI qw(:sql_types);
do "t/lib.pl";
my $cnt = join "" => <DATA>;
my $tbl;
my $expect = [
[ 1, "Knut", "white" ],
[ 2, "Inge", "black" ],
[ 3, "Beowulf", "CCEE00" ],
];
{ my $dbh = Connect ();
ok ($tbl = FindNewTable ($dbh), "find new test table");
}
TODO: {
local $TODO = "Streaming support";
if ($DBD::File::VERSION gt "0.44") {
note ("ScalarIO - no col_names");
my $dbh = Connect ();
open my $data, "<", \$cnt;
$dbh->{csv_tables}->{data} = {
f_file => $data,
skip_rows => 4,
};
my $sth = $dbh->prepare ("SELECT * FROM data");
$sth->execute ();
my $rows = $sth->fetchall_arrayref ();
is_deeply ($rows, $expect, "all rows found - mem-io w/o col_names");
}
if ($DBD::File::VERSION gt "0.44") {
note ("ScalarIO - with col_names");
my $dbh = Connect ();
open my $data, "<", \$cnt;
$dbh->{csv_tables}->{data} = {
f_file => $data,
skip_rows => 4,
col_names => [qw(id name color)],
};
my $sth = $dbh->prepare ("SELECT * FROM data");
$sth->execute ();
my $rows = $sth->fetchall_arrayref ();
is_deeply ($rows, $expect, "all rows found - mem-io w col_names");
}
}
my $fn = File::Spec->rel2abs (DbFile ($tbl));
open my $fh, ">", $fn or die "Can't open $fn for writing: $!";
print $fh $cnt;
close $fh;
note ("File handle - no col_names");
{ open my $data, "<", $fn;
my $dbh = Connect ();
$dbh->{csv_tables}->{data} = {
f_file => $data,
skip_rows => 4,
};
my $sth = $dbh->prepare ("SELECT * FROM data");
$sth->execute ();
my $rows = $sth->fetchall_arrayref ();
is_deeply ($rows, $expect, "all rows found - file-handle w/o col_names");
is_deeply ($sth->{NAME_lc}, [qw(id name color)],
"column names - file-handle w/o col_names");
}
note ("File handle - with col_names");
{ open my $data, "<", $fn;
my $dbh = Connect ();
$dbh->{csv_tables}->{data} = {
f_file => $data,
skip_rows => 4,
col_names => [qw(foo bar baz)],
};
my $sth = $dbh->prepare ("SELECT * FROM data");
$sth->execute ();
my $rows = $sth->fetchall_arrayref ();
is_deeply ($rows, $expect, "all rows found - file-handle w col_names");
is_deeply ($sth->{NAME_lc}, [qw(foo bar baz)], "column names - file-handle w col_names");
}
note ("File name - no col_names");
{ my $dbh = Connect ();
$dbh->{csv_tables}->{data} = {
f_file => $fn,
skip_rows => 4,
};
my $sth = $dbh->prepare ("SELECT * FROM data");
$sth->execute ();
my $rows = $sth->fetchall_arrayref ();
is_deeply ($rows, $expect, "all rows found - file-name w/o col_names");
is_deeply ($sth->{NAME_lc}, [qw(id name color)],
"column names - file-name w/o col_names");
}
note ("File name - with col_names");
{ my $dbh = Connect ({ RaiseError => 1 });
$dbh->{csv_tables}->{data} = {
f_file => $fn,
skip_rows => 4,
col_names => [qw(foo bar baz)],
};
my $sth = $dbh->prepare ("SELECT * FROM data");
$sth->execute ();
my $rows = $sth->fetchall_arrayref ();
is_deeply ($rows, $expect, "all rows found - file-name w col_names" );
is_deeply ($sth->{NAME_lc}, [qw(foo bar baz)],
"column names - file-name w col_names" );
# TODO: Next test will hang in open_tables ()
# 'Cannot obtain exclusive lock on .../output12660/testaa: Interrupted system call'
#ok ($dbh->do ("drop table data"), "Drop the table");
}
unlink $fn;
done_testing ();
__END__
id,name,color
stupid content
only for skipping
followed by column names
1,Knut,white
2,Inge,black
3,Beowulf,"CCEE00"