xref: /sqlite-3.40.0/test/csv01.test (revision dfe4e6bb) 
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
14set testdir [file dirname $argv0]
15source $testdir/tester.tcl
16set testprefix csv01
17
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
41do_execsql_test 2.0 {
42  DROP TABLE t1;
43  CREATE VIRTUAL TABLE temp.t2 USING csv(
44    data=
45'1,2,3,4
465,6,7,8
479,10,11,12
4813,14,15,16
49',
50    columns=4,
51    schema='CREATE TABLE t2(a INT, b TEXT, c REAL, d BLOB)'
52  );
53  SELECT * FROM t2 WHERE a=9;
54} {9 10 11 12}
55do_execsql_test 2.1 {
56  SELECT * FROM t2 WHERE b=10;
57} {9 10 11 12}
58do_execsql_test 2.2 {
59  SELECT * FROM t2 WHERE c=11;
60} {9 10 11 12}
61do_execsql_test 2.3 {
62  SELECT * FROM t2 WHERE d=12;
63} {}
64do_execsql_test 2.4 {
65  SELECT * FROM t2 WHERE d='12';
66} {9 10 11 12}
67do_execsql_test 2.5 {
68  SELECT * FROM t2 WHERE a='9';
69} {9 10 11 12}
70
71do_execsql_test 3.0 {
72  DROP TABLE t2;
73  CREATE VIRTUAL TABLE temp.t3 USING csv(
74    data=
75'1,2,3,4
765,6,7,8
779,10,11,12
7813,14,15,16
79',
80    columns=4,
81    schema=
82      'CREATE TABLE t3(a PRIMARY KEY,b TEXT,c TEXT,d TEXT) WITHOUT ROWID',
83    testflags=1
84  );
85  SELECT a FROM t3 WHERE b=6 OR c=7 OR d=12 ORDER BY +a;
86} {5 9}
87do_execsql_test 3.1 {
88  SELECT a FROM t3 WHERE +b=6 OR c=7 OR d=12 ORDER BY +a;
89} {5 9}
90
91# The rowid column is not visible on a WITHOUT ROWID virtual table
92do_catchsql_test 3.2 {
93  SELECT rowid, a FROM t3;
94} {1 {no such column: rowid}}
95
96do_catchsql_test 4.0 {
97  DROP TABLE t3;
98  CREATE VIRTUAL TABLE temp.t4 USING csv_wr(
99    data=
100'1,2,3,4
1015,6,7,8
1029,10,11,12
10313,14,15,16
104',
105    columns=4,
106    schema=
107      'CREATE TABLE t3(a PRIMARY KEY,b TEXT,c TEXT,d TEXT) WITHOUT ROWID',
108    testflags=1
109  );
110} {1 {vtable constructor failed: t4}}
111
112finish_test
113