Debian Codegraph @

test/csv01.test

raw
1 # 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
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 set testprefix csv01
17
18 ifcapable !vtab||!cte { finish_test ; return }
19
20 load_static_extension db csv
21
22 do_execsql_test 1.0 {
23 CREATE VIRTUAL TABLE temp.t1 USING csv(
24 data=
25 '1,2,3,4
26 5,6,7,8
27 9,10,11,12
28 13,14,15,16
29 ',
30 columns=4
31 );
32 SELECT * FROM t1 WHERE c1=10;
33 } {9 10 11 12}
34 do_execsql_test 1.1 {
35 SELECT * FROM t1 WHERE c1='10';
36 } {9 10 11 12}
37 do_execsql_test 1.2 {
38 SELECT rowid FROM t1;
39 } {1 2 3 4}
40
41 do_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"
46 1,2,3,4
47 5,6,7,8
48 9,10,11,12
49 13,14,15,16
50 ',
51 header=1
52 );
53 SELECT * FROM t1 WHERE "soft opinion"=12;
54 } {9 10 11 12}
55 do_execsql_test 1.4 {
56 SELECT name FROM pragma_table_xinfo('t1');
57 } {a b mix-bloom-eel {soft opinion}}
58
59 do_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"
64 1,2,3,4
65 5,6,7,8
66 9,10,11,12
67 13,14,15,16
68 ',
69 header=false
70 );
71 SELECT * FROM t1 WHERE c1='b';
72 } {a b mix-bloom-eel {soft opinion}}
73 do_execsql_test 1.6 {
74 SELECT name FROM pragma_table_xinfo('t1');
75 } {c0 c1 c2 c3}
76
77 do_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"
82 1,2,3,4
83 5,6,7,8
84 9,10,11,12
85 13,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 {}}
93 do_execsql_test 1.8 {
94 SELECT name FROM pragma_table_xinfo('t1');
95 } {x0 x1 x2 x3 x4}
96
97
98 do_execsql_test 2.0 {
99 DROP TABLE t1;
100 CREATE VIRTUAL TABLE temp.t2 USING csv(
101 data=
102 '1,2,3,4
103 5,6,7,8
104 9,10,11,12
105 13,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}
112 do_execsql_test 2.1 {
113 SELECT * FROM t2 WHERE b=10;
114 } {9 10 11 12}
115 do_execsql_test 2.2 {
116 SELECT * FROM t2 WHERE c=11;
117 } {9 10 11 12}
118 do_execsql_test 2.3 {
119 SELECT * FROM t2 WHERE d=12;
120 } {}
121 do_execsql_test 2.4 {
122 SELECT * FROM t2 WHERE d='12';
123 } {9 10 11 12}
124 do_execsql_test 2.5 {
125 SELECT * FROM t2 WHERE a='9';
126 } {9 10 11 12}
127
128 do_execsql_test 3.0 {
129 DROP TABLE t2;
130 CREATE VIRTUAL TABLE temp.t3 USING csv(
131 data=
132 '1,2,3,4
133 5,6,7,8
134 9,10,11,12
135 13,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}
144 do_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
148 # The rowid column is not visible on a WITHOUT ROWID virtual table
149 do_catchsql_test 3.2 {
150 SELECT rowid, a FROM t3;
151 } {1 {no such column: rowid}}
152
153 # Multi-column WITHOUT ROWID virtual tables may not be writable.
154 do_catchsql_test 4.0 {
155 DROP TABLE t3;
156 CREATE VIRTUAL TABLE temp.t4 USING csv_wr(
157 data=
158 '1,2,3,4
159 5,6,7,8
160 9,10,11,12
161 13,14,15,16',
162 columns=4,
163 schema=
164 'CREATE TABLE t3(a,b,c,d,PRIMARY KEY(a,b)) WITHOUT ROWID',
165 testflags=1
166 );
167 } {1 {bad schema: 'CREATE TABLE t3(a,b,c,d,PRIMARY KEY(a,b)) WITHOUT ROWID' - not an error}}
168
169 # WITHOUT ROWID tables with a single-column PRIMARY KEY may be writable.
170 do_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
175 5,6,7,8
176 9,10,11,12
177 13,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
185 do_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 );
198 } {1 {bad schema: 'CREATE TABLE t3(a,b,c,d) WITHOUT ROWID' - PRIMARY KEY missing on table t3}}
199
200 # 2018-04-24
201 # Memory leak reported on the sqlite-users mailing list by Ralf Junker.
202 #
203 do_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}}
207
208 # 2018-06-02
209 # Problem with single-column CSV support reported on the mailing list
210 # by Trent W. Buck.
211 #
212 do_execsql_test 4.4 {
213 CREATE VIRTUAL TABLE temp.trent USING csv(data='1');
214 SELECT * FROM trent;
215 } {1}
216
217 # 2018-12-26
218 # Bug report on the mailing list
219 #
220 forcedelete csv01.csv
221 set fd [open csv01.csv wb]
222 puts $fd "a,b,c,d\r\n1,2,3,4\r\none,two,three,four\r\n5,6,7,8"
223 close $fd
224 do_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}
228 do_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 |}
231 do_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}
236 do_execsql_test 5.4 {
237 SELECT *, '|' FROM t5_1;
238 } {1 2 3 4 | one two three four | 5 6 7 8 |}
239
240 #-------------------------------------------------------------------------
241
242 proc randomtext {n} {
243 string range [db one {SELECT hex(randomblob($n))}] 1 $n
244 }
245
246 for {set ii 0} {$ii < 200} {incr ii} {
247 reset_db
248 load_static_extension db csv
249 set fd [open csv.data w]
250 puts $fd "a,b"
251 puts $fd "[randomtext $ii],abcd"
252 close $fd
253 do_execsql_test 6.$ii.1 {
254 CREATE VIRTUAL TABLE abc USING csv(filename='csv.data', header=true);
255 }
256 do_execsql_test 6.$ii.2 {
257 SELECT count(*) FROM abc
258 } 1
259 }
260
261 for {set ii 0} {$ii < 20} {incr ii} {
262 reset_db
263 load_static_extension db csv
264 set T [randomtext $ii]
265 set fd [open csv.data w]
266 puts $fd "a,b"
267 puts -nonewline $fd "abcd,$T"
268 close $fd
269 do_execsql_test 7.$ii.1 {
270 CREATE VIRTUAL TABLE abc USING csv(filename='csv.data', header=true);
271 }
272 breakpoint
273 do_execsql_test 7.$ii.2 {
274 SELECT * FROM abc
275 } [list abcd $T]
276 }
277
278
279 finish_test