1#!/usr/bin/perl 2 3use strict; 4use warnings; 5 6use Test::More; 7use DBI qw(:sql_types); 8do "./t/lib.pl"; 9 10my $cnt = join "" => <DATA>; 11my $tbl; 12 13my $expect = [ 14 [ 1, "Knut", "white" ], 15 [ 2, "Inge", "black" ], 16 [ 3, "Beowulf", "CCEE00" ], 17 ]; 18 19{ my $dbh = Connect (); 20 ok ($tbl = FindNewTable ($dbh), "find new test table"); 21 } 22 23TODO: { 24 local $TODO = "Streaming support"; 25 26 if ($DBD::File::VERSION gt "0.44") { 27 note ("ScalarIO - no col_names"); 28 my $dbh = Connect ({ RaiseError => 0, PrintError => 0 }); 29 30 open my $data, "<", \$cnt; 31 $dbh->{csv_tables}->{data} = { 32 f_file => $data, 33 skip_rows => 4, 34 }; 35 36 if (my $sth = $dbh->prepare ("SELECT * FROM data")) { 37 $sth->execute (); 38 my $rows = $sth->fetchall_arrayref (); 39 is_deeply ($rows, $expect, "all rows found - mem-io w/o col_names"); 40 } 41 } 42 43 if ($DBD::File::VERSION gt "0.44") { 44 note ("ScalarIO - with col_names"); 45 my $dbh = Connect ({ RaiseError => 0, PrintError => 0 }); 46 47 open my $data, "<", \$cnt; 48 $dbh->{csv_tables}->{data} = { 49 f_file => $data, 50 skip_rows => 4, 51 col_names => [qw(id name color)], 52 }; 53 if (my $sth = $dbh->prepare ("SELECT * FROM data")) { 54 $sth->execute (); 55 my $rows = $sth->fetchall_arrayref (); 56 is_deeply ($rows, $expect, "all rows found - mem-io w col_names"); 57 } 58 } 59 } 60 61my $fn = File::Spec->rel2abs (DbFile ($tbl)); 62open my $fh, ">", $fn or die "Can't open $fn for writing: $!"; 63print $fh $cnt; 64close $fh; 65 66note ("File handle - no col_names"); 67{ open my $data, "<", $fn; 68 my $dbh = Connect (); 69 $dbh->{csv_tables}->{data} = { 70 f_file => $data, 71 skip_rows => 4, 72 }; 73 my $sth = $dbh->prepare ("SELECT * FROM data"); 74 $sth->execute (); 75 my $rows = $sth->fetchall_arrayref (); 76 is_deeply ($rows, $expect, "all rows found - file-handle w/o col_names"); 77 is_deeply ($sth->{NAME_lc}, [qw(id name color)], 78 "column names - file-handle w/o col_names"); 79 } 80 81note ("File handle - with col_names"); 82{ open my $data, "<", $fn; 83 my $dbh = Connect (); 84 $dbh->{csv_tables}->{data} = { 85 f_file => $data, 86 skip_rows => 4, 87 col_names => [qw(foo bar baz)], 88 }; 89 my $sth = $dbh->prepare ("SELECT * FROM data"); 90 $sth->execute (); 91 my $rows = $sth->fetchall_arrayref (); 92 is_deeply ($rows, $expect, "all rows found - file-handle w col_names"); 93 is_deeply ($sth->{NAME_lc}, [qw(foo bar baz)], "column names - file-handle w col_names"); 94 } 95 96note ("File name - no col_names"); 97{ my $dbh = Connect (); 98 $dbh->{csv_tables}->{data} = { 99 f_file => $fn, 100 skip_rows => 4, 101 }; 102 my $sth = $dbh->prepare ("SELECT * FROM data"); 103 $sth->execute (); 104 my $rows = $sth->fetchall_arrayref (); 105 is_deeply ($rows, $expect, "all rows found - file-name w/o col_names"); 106 is_deeply ($sth->{NAME_lc}, [qw(id name color)], 107 "column names - file-name w/o col_names"); 108 } 109 110note ("File name - with col_names"); 111{ my $dbh = Connect ({ RaiseError => 1 }); 112 $dbh->{csv_tables}->{data} = { 113 f_file => $fn, 114 skip_rows => 4, 115 col_names => [qw(foo bar baz)], 116 }; 117 my $sth = $dbh->prepare ("SELECT * FROM data"); 118 $sth->execute (); 119 my $rows = $sth->fetchall_arrayref (); 120 is_deeply ($rows, $expect, "all rows found - file-name w col_names" ); 121 is_deeply ($sth->{NAME_lc}, [qw(foo bar baz)], 122 "column names - file-name w col_names" ); 123 124 # TODO: Next test will hang in open_tables () 125 # 'Cannot obtain exclusive lock on .../output12660/testaa: Interrupted system call' 126 #ok ($dbh->do ("drop table data"), "Drop the table"); 127 } 128 129unlink $fn; 130 131note ("Attribute prefixes"); 132$fn = "test.csv"; 133foreach my $x (0, 1) { 134 my ($fpfx, $cpfx) = $x ? ("f_", "csv_") : ("", ""); 135 my $dbh = DBI->connect ("dbi:CSV:", undef, undef, { 136 "${fpfx}schema" => undef, # schema / f_schema 137 "${fpfx}dir" => "files", # .. f_dir 138 "${fpfx}ext" => ".csv/r", # .. f_ext 139 140 "${cpfx}eol" => "\n", # eol / csv_eol 141 "${cpfx}always_quote" => 1, # .. csv_always_quote 142 "${cpfx}sep_char" => ";", # .. csv_sep_char 143 144 RaiseError => 1, 145 PrintError => 1, 146 }) or die "$DBI::errstr\n" || $DBI::errstr; 147 148 my $ffn = "files/$fn"; 149 unlink $ffn; 150 $dbh->{csv_tables}{tst} = { 151 "${fpfx}file" => $fn, # file / f_file 152 col_names => [qw( c_tst s_tst )], 153 }; 154 155 is_deeply ( 156 [ sort $dbh->tables (undef, undef, undef, undef) ], 157 [qw( fruit tools )], "Tables"); 158 is_deeply ( 159 [ sort keys %{$dbh->{csv_tables}} ], 160 [qw( fruit tools tst )], "Mixed tables"); 161 162 $dbh->{csv_tables}{fruit}{sep_char} = ","; # should work 163 164 is_deeply ($dbh->selectall_arrayref ("select * from tools order by c_tool"), 165 [ [ 1, "Hammer" ], 166 [ 2, "Screwdriver" ], 167 [ 3, "Drill" ], 168 [ 4, "Saw" ], 169 [ 5, "Router" ], 170 [ 6, "Hobbyknife" ], 171 ], "Sorted tools"); 172 is_deeply ($dbh->selectall_arrayref ("select * from fruit order by c_fruit"), 173 [ [ 1, "Apple" ], 174 [ 2, "Blueberry" ], 175 [ 3, "Orange" ], 176 [ 4, "Melon" ], 177 ], "Sorted fruit"); 178 179 # TODO: Ideally, insert should create the file if empty or non-existent 180 # and insert "c_tst";"s_tst" as header line 181 open my $fh, ">", $ffn; close $fh; 182 183 $dbh->do ("insert into tst values (42, 'Test')"); # "42";"Test" 184 $dbh->do ("update tst set s_tst = 'Done' where c_tst = 42"); # "42";"Done" 185 186 $dbh->disconnect; 187 188 open $fh, "<", $ffn or die "$ffn: $!\n"; 189 my @dta = <$fh>; 190 close $fh; 191 is ($dta[-1], qq{"42";"Done"\n}, "Table tst written to $fn"); 192 unlink $ffn; 193 } 194 195done_testing (); 196 197__END__ 198id,name,color 199stupid content 200only for skipping 201followed by column names 2021,Knut,white 2032,Inge,black 2043,Beowulf,"CCEE00" 205