AryaWu/sqlite

1# 2016-06-022#3# The author disclaims copyright to this source code.  In place of4# 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.tcl16set testprefix csv0117 18ifcapable !vtab||!cte { finish_test ; return }19 20load_static_extension db csv21 22do_execsql_test 1.0 {23  CREATE VIRTUAL TABLE temp.t1 USING csv(24    data=25'1,2,3,4265,6,7,8279,10,11,122813,14,15,1629',30    columns=431  );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 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,4475,6,7,8489,10,11,124913,14,15,1650',51    header=152  );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,4655,6,7,8669,10,11,126713,14,15,1668',69    header=false70  );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,4835,6,7,8849,10,11,128513,14,15,1686',87    header,88    schema='CREATE TABLE x(x0,x1,x2,x3,x4)',89    columns=590  );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 98do_execsql_test 2.0 {99  DROP TABLE t1;100  CREATE VIRTUAL TABLE temp.t2 USING csv(101    data=102'1,2,3,41035,6,7,81049,10,11,1210513,14,15,16106',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,41335,6,7,81349,10,11,1213513,14,15,16136',137    columns=4,138    schema=139      'CREATE TABLE t3(a PRIMARY KEY,b TEXT,c TEXT,d TEXT) WITHOUT ROWID',140    testflags=1141  );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 148# The rowid column is not visible on a WITHOUT ROWID virtual table149do_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.154do_catchsql_test 4.0 {155  DROP TABLE t3;156  CREATE VIRTUAL TABLE temp.t4 USING csv_wr(157    data=158'1,2,3,41595,6,7,81609,10,11,1216113,14,15,16',162    columns=4,163    schema=164      'CREATE TABLE t3(a,b,c,d,PRIMARY KEY(a,b)) WITHOUT ROWID',165    testflags=1166  );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.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,41755,6,7,81769,10,11,1217713,14,15,16',178    columns=4,179    schema=180      'CREATE TABLE t3(a,b,c,d,PRIMARY KEY(b)) WITHOUT ROWID',181    testflags=1182  );183} {0 {}}184 185do_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,4190      5,6,7,8191      9,10,11,12192      13,14,15,16',193      columns=4,194      schema=195      'CREATE TABLE t3(a,b,c,d) WITHOUT ROWID',196      testflags=1197      );198} {1 {bad schema: 'CREATE TABLE t3(a,b,c,d) WITHOUT ROWID' - PRIMARY KEY missing on table t3}}199 200# 2018-04-24201# 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.t1205  USING csv(filename='FileDoesNotExist.csv');206} {1 {cannot open 'FileDoesNotExist.csv' for reading}}207 208# 2018-06-02209# Problem with single-column CSV support reported on the mailing list210# 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 217# 2018-12-26218# Bug report on the mailing list219#220forcedelete csv01.csv221set fd [open csv01.csv wb]222puts $fd "a,b,c,d\r\n1,2,3,4\r\none,two,three,four\r\n5,6,7,8"223close $fd224do_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 240#-------------------------------------------------------------------------241 242proc randomtext {n} {243  string range [db one {SELECT hex(randomblob($n))}] 1 $n244}245 246for {set ii 0} {$ii < 200} {incr ii} {247  reset_db248  load_static_extension db csv249  set fd [open csv.data w]250  puts $fd "a,b"251  puts $fd "[randomtext $ii],abcd"252  close $fd253  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 abc258  } 1259}260 261for {set ii 0} {$ii < 20} {incr ii} {262  reset_db263  load_static_extension db csv264  set T [randomtext $ii]265  set fd [open csv.data w]266  puts $fd "a,b"267  puts -nonewline $fd "abcd,$T"268  close $fd269  do_execsql_test 7.$ii.1 {270    CREATE VIRTUAL TABLE abc USING csv(filename='csv.data', header=true);271  }272  breakpoint273  do_execsql_test 7.$ii.2 {274    SELECT * FROM abc275  } [list abcd $T]276}277 278 279finish_test280