blob: 9ba04206b8bf5fad21ae7dcdaa6d1c2535975dc0 [file] [log] [blame]
drh35db31b2016-06-02 23:13:21 +00001# 2016-06-02
2#
3# The author disclaims copyright to this source code. In place of
4# a legal notice, here is a blessing:
5#
6# May you do good and not evil.
7# May you find forgiveness for yourself and forgive others.
8# May you share freely, never taking more than you give.
9#
10#***********************************************************************
11#
12# Test cases for CSV virtual table.
13
14set testdir [file dirname $argv0]
15source $testdir/tester.tcl
mistachkina4c07342016-07-22 21:35:38 +000016set testprefix csv01
drh35db31b2016-06-02 23:13:21 +000017
18ifcapable !vtab||!cte { finish_test ; return }
19
20load_static_extension db csv
21
22do_execsql_test 1.0 {
23 CREATE VIRTUAL TABLE temp.t1 USING csv(
24 data=
25'1,2,3,4
265,6,7,8
279,10,11,12
2813,14,15,16
29',
30 columns=4
31 );
32 SELECT * FROM t1 WHERE c1=10;
33} {9 10 11 12}
34do_execsql_test 1.1 {
35 SELECT * FROM t1 WHERE c1='10';
36} {9 10 11 12}
37do_execsql_test 1.2 {
38 SELECT rowid FROM t1;
39} {1 2 3 4}
40
drh6f147c52018-11-16 01:42:26 +000041do_execsql_test 1.3 {
42 DROP TABLE temp.t1;
43 CREATE VIRTUAL TABLE temp.t1 USING csv(
44 data=
45'a,b,"mix-bloom-eel","soft opinion"
461,2,3,4
475,6,7,8
489,10,11,12
4913,14,15,16
50',
51 header=1
52 );
53 SELECT * FROM t1 WHERE "soft opinion"=12;
54} {9 10 11 12}
55do_execsql_test 1.4 {
56 SELECT name FROM pragma_table_xinfo('t1');
57} {a b mix-bloom-eel {soft opinion}}
58
59do_execsql_test 1.5 {
60 DROP TABLE temp.t1;
61 CREATE VIRTUAL TABLE temp.t1 USING csv(
62 data=
63'a,b,"mix-bloom-eel","soft opinion"
641,2,3,4
655,6,7,8
669,10,11,12
6713,14,15,16
68',
69 header=false
70 );
71 SELECT * FROM t1 WHERE c1='b';
72} {a b mix-bloom-eel {soft opinion}}
73do_execsql_test 1.6 {
74 SELECT name FROM pragma_table_xinfo('t1');
75} {c0 c1 c2 c3}
76
77do_execsql_test 1.7 {
78 DROP TABLE temp.t1;
79 CREATE VIRTUAL TABLE temp.t1 USING csv(
80 data=
81'a,b,"mix-bloom-eel","soft opinion"
821,2,3,4
835,6,7,8
849,10,11,12
8513,14,15,16
86',
87 header,
88 schema='CREATE TABLE x(x0,x1,x2,x3,x4)',
89 columns=5
90 );
91 SELECT * FROM t1 WHERE x1='6';
92} {5 6 7 8 {}}
93do_execsql_test 1.8 {
94 SELECT name FROM pragma_table_xinfo('t1');
95} {x0 x1 x2 x3 x4}
96
97
drh35db31b2016-06-02 23:13:21 +000098do_execsql_test 2.0 {
99 DROP TABLE t1;
100 CREATE VIRTUAL TABLE temp.t2 USING csv(
101 data=
102'1,2,3,4
1035,6,7,8
1049,10,11,12
10513,14,15,16
106',
107 columns=4,
108 schema='CREATE TABLE t2(a INT, b TEXT, c REAL, d BLOB)'
109 );
110 SELECT * FROM t2 WHERE a=9;
111} {9 10 11 12}
112do_execsql_test 2.1 {
113 SELECT * FROM t2 WHERE b=10;
114} {9 10 11 12}
115do_execsql_test 2.2 {
116 SELECT * FROM t2 WHERE c=11;
117} {9 10 11 12}
118do_execsql_test 2.3 {
119 SELECT * FROM t2 WHERE d=12;
120} {}
121do_execsql_test 2.4 {
122 SELECT * FROM t2 WHERE d='12';
123} {9 10 11 12}
124do_execsql_test 2.5 {
125 SELECT * FROM t2 WHERE a='9';
126} {9 10 11 12}
127
128do_execsql_test 3.0 {
129 DROP TABLE t2;
130 CREATE VIRTUAL TABLE temp.t3 USING csv(
131 data=
132'1,2,3,4
1335,6,7,8
1349,10,11,12
13513,14,15,16
136',
137 columns=4,
138 schema=
139 'CREATE TABLE t3(a PRIMARY KEY,b TEXT,c TEXT,d TEXT) WITHOUT ROWID',
140 testflags=1
141 );
142 SELECT a FROM t3 WHERE b=6 OR c=7 OR d=12 ORDER BY +a;
143} {5 9}
144do_execsql_test 3.1 {
145 SELECT a FROM t3 WHERE +b=6 OR c=7 OR d=12 ORDER BY +a;
146} {5 9}
147
drh7edcf622016-06-03 17:27:14 +0000148# The rowid column is not visible on a WITHOUT ROWID virtual table
149do_catchsql_test 3.2 {
150 SELECT rowid, a FROM t3;
151} {1 {no such column: rowid}}
152
drhe3740f22017-08-10 15:19:39 +0000153# Multi-column WITHOUT ROWID virtual tables may not be writable.
drhac9c3d22016-06-03 01:01:57 +0000154do_catchsql_test 4.0 {
155 DROP TABLE t3;
156 CREATE VIRTUAL TABLE temp.t4 USING csv_wr(
157 data=
158'1,2,3,4
1595,6,7,8
1609,10,11,12
drhe3740f22017-08-10 15:19:39 +000016113,14,15,16',
drhac9c3d22016-06-03 01:01:57 +0000162 columns=4,
163 schema=
drhe3740f22017-08-10 15:19:39 +0000164 'CREATE TABLE t3(a,b,c,d,PRIMARY KEY(a,b)) WITHOUT ROWID',
drhac9c3d22016-06-03 01:01:57 +0000165 testflags=1
166 );
drh6f147c52018-11-16 01:42:26 +0000167} {1 {bad schema: 'CREATE TABLE t3(a,b,c,d,PRIMARY KEY(a,b)) WITHOUT ROWID' - not an error}}
drhac9c3d22016-06-03 01:01:57 +0000168
drhe3740f22017-08-10 15:19:39 +0000169# WITHOUT ROWID tables with a single-column PRIMARY KEY may be writable.
170do_catchsql_test 4.1 {
171 DROP TABLE IF EXISTS t4;
172 CREATE VIRTUAL TABLE temp.t4 USING csv_wr(
173 data=
174'1,2,3,4
1755,6,7,8
1769,10,11,12
17713,14,15,16',
178 columns=4,
179 schema=
180 'CREATE TABLE t3(a,b,c,d,PRIMARY KEY(b)) WITHOUT ROWID',
181 testflags=1
182 );
183} {0 {}}
184
dan09fc79b2017-08-10 19:10:23 +0000185do_catchsql_test 4.2 {
186 DROP TABLE IF EXISTS t5;
187 CREATE VIRTUAL TABLE temp.t5 USING csv_wr(
188 data=
189 '1,2,3,4
190 5,6,7,8
191 9,10,11,12
192 13,14,15,16',
193 columns=4,
194 schema=
195 'CREATE TABLE t3(a,b,c,d) WITHOUT ROWID',
196 testflags=1
197 );
drh6f147c52018-11-16 01:42:26 +0000198} {1 {bad schema: 'CREATE TABLE t3(a,b,c,d) WITHOUT ROWID' - PRIMARY KEY missing on table t3}}
dan09fc79b2017-08-10 19:10:23 +0000199
drh4d3e6142018-04-24 10:57:10 +0000200# 2018-04-24
201# Memory leak reported on the sqlite-users mailing list by Ralf Junker.
202#
203do_catchsql_test 4.3 {
204 CREATE VIRTUAL TABLE IF NOT EXISTS temp.t1
205 USING csv(filename='FileDoesNotExist.csv');
206} {1 {cannot open 'FileDoesNotExist.csv' for reading}}
dan09fc79b2017-08-10 19:10:23 +0000207
drhe893e2e2018-06-02 12:05:18 +0000208# 2018-06-02
209# Problem with single-column CSV support reported on the mailing list
210# by Trent W. Buck.
211#
212do_execsql_test 4.4 {
213 CREATE VIRTUAL TABLE temp.trent USING csv(data='1');
214 SELECT * FROM trent;
215} {1}
216
drh86252212018-12-26 12:50:47 +0000217# 2018-12-26
218# Bug report on the mailing list
219#
220forcedelete csv01.csv
drh1baae572019-01-03 16:03:48 +0000221set fd [open csv01.csv wb]
drh86252212018-12-26 12:50:47 +0000222puts $fd "a,b,c,d\r\n1,2,3,4\r\none,two,three,four\r\n5,6,7,8"
223close $fd
224do_execsql_test 5.1 {
225 CREATE VIRTUAL TABLE t5_1 USING csv(filename='csv01.csv');
226 SELECT name FROM temp.pragma_table_info('t5_1');
227} {c0 c1 c2 c3}
228do_execsql_test 5.2 {
229 SELECT *, '|' FROM t5_1;
230} {a b c d | 1 2 3 4 | one two three four | 5 6 7 8 |}
231do_execsql_test 5.3 {
232 DROP TABLE t5_1;
233 CREATE VIRTUAL TABLE t5_1 USING csv(filename='csv01.csv', header);
234 SELECT name FROM temp.pragma_table_info('t5_1');
235} {a b c d}
236do_execsql_test 5.4 {
237 SELECT *, '|' FROM t5_1;
238} {1 2 3 4 | one two three four | 5 6 7 8 |}
239
drh35db31b2016-06-02 23:13:21 +0000240finish_test