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 96# Multi-column WITHOUT ROWID virtual tables may not be writable. 97do_catchsql_test 4.0 { 98 DROP TABLE t3; 99 CREATE VIRTUAL TABLE temp.t4 USING csv_wr( 100 data= 101'1,2,3,4 1025,6,7,8 1039,10,11,12 10413,14,15,16', 105 columns=4, 106 schema= 107 'CREATE TABLE t3(a,b,c,d,PRIMARY KEY(a,b)) WITHOUT ROWID', 108 testflags=1 109 ); 110} {1 {vtable constructor failed: t4}} 111 112# WITHOUT ROWID tables with a single-column PRIMARY KEY may be writable. 113do_catchsql_test 4.1 { 114 DROP TABLE IF EXISTS t4; 115 CREATE VIRTUAL TABLE temp.t4 USING csv_wr( 116 data= 117'1,2,3,4 1185,6,7,8 1199,10,11,12 12013,14,15,16', 121 columns=4, 122 schema= 123 'CREATE TABLE t3(a,b,c,d,PRIMARY KEY(b)) WITHOUT ROWID', 124 testflags=1 125 ); 126} {0 {}} 127 128do_catchsql_test 4.2 { 129 DROP TABLE IF EXISTS t5; 130 CREATE VIRTUAL TABLE temp.t5 USING csv_wr( 131 data= 132 '1,2,3,4 133 5,6,7,8 134 9,10,11,12 135 13,14,15,16', 136 columns=4, 137 schema= 138 'CREATE TABLE t3(a,b,c,d) WITHOUT ROWID', 139 testflags=1 140 ); 141} {1 {vtable constructor failed: t5}} 142 143# 2018-04-24 144# Memory leak reported on the sqlite-users mailing list by Ralf Junker. 145# 146do_catchsql_test 4.3 { 147 CREATE VIRTUAL TABLE IF NOT EXISTS temp.t1 148 USING csv(filename='FileDoesNotExist.csv'); 149} {1 {cannot open 'FileDoesNotExist.csv' for reading}} 150 151# 2018-06-02 152# Problem with single-column CSV support reported on the mailing list 153# by Trent W. Buck. 154# 155do_execsql_test 4.4 { 156 CREATE VIRTUAL TABLE temp.trent USING csv(data='1'); 157 SELECT * FROM trent; 158} {1} 159 160finish_test 161