gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/csv01.test (about) 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 finish_test