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