diff options
Diffstat (limited to 'test/shell5.test')
| -rw-r--r-- | test/shell5.test | 197 |
1 files changed, 172 insertions, 25 deletions
diff --git a/test/shell5.test b/test/shell5.test index d90cedf..8d740cb 100644 --- a/test/shell5.test +++ b/test/shell5.test @@ -32,7 +32,6 @@ if {![file executable $CLI]} { } db close forcedelete test.db test.db-journal test.db-wal -sqlite3 db test.db #---------------------------------------------------------------------------- # Test cases shell5-1.*: Basic handling of the .import and .separator commands. @@ -41,29 +40,32 @@ sqlite3 db test.db # .import FILE TABLE Import data from FILE into TABLE do_test shell5-1.1.1 { catchcmd "test.db" ".import" -} {1 {Error: unknown command or invalid arguments: "import". Enter ".help" for help}} +} {1 {Usage: .import FILE TABLE}} do_test shell5-1.1.2 { catchcmd "test.db" ".import FOO" -} {1 {Error: unknown command or invalid arguments: "import". Enter ".help" for help}} -do_test shell5-1.1.2 { - catchcmd "test.db" ".import FOO BAR" -} {1 {Error: no such table: BAR}} +} {1 {Usage: .import FILE TABLE}} +#do_test shell5-1.1.2 { +# catchcmd "test.db" ".import FOO BAR" +#} {1 {Error: no such table: BAR}} do_test shell5-1.1.3 { # too many arguments catchcmd "test.db" ".import FOO BAR BAD" -} {1 {Error: unknown command or invalid arguments: "import". Enter ".help" for help}} +} {1 {Usage: .import FILE TABLE}} # .separator STRING Change separator used by output mode and .import -do_test shell1-1.2.1 { +do_test shell5-1.2.1 { catchcmd "test.db" ".separator" -} {1 {Error: unknown command or invalid arguments: "separator". Enter ".help" for help}} -do_test shell1-1.2.2 { - catchcmd "test.db" ".separator FOO" +} {1 {Usage: .separator SEPARATOR ?NEWLINE?}} +do_test shell5-1.2.2 { + catchcmd "test.db" ".separator ONE" } {0 {}} -do_test shell1-1.2.3 { +do_test shell5-1.2.3 { + catchcmd "test.db" ".separator ONE TWO" +} {0 {}} +do_test shell5-1.2.4 { # too many arguments - catchcmd "test.db" ".separator FOO BAD" -} {1 {Error: unknown command or invalid arguments: "separator". Enter ".help" for help}} + catchcmd "test.db" ".separator ONE TWO THREE" +} {1 {Usage: .separator SEPARATOR ?NEWLINE?}} # separator should default to "|" do_test shell5-1.3.1 { @@ -81,14 +83,14 @@ do_test shell5-1.3.2 { # import file doesn't exist do_test shell5-1.4.1 { - file delete -force FOO + forcedelete FOO set res [catchcmd "test.db" {CREATE TABLE t1(a, b); .import FOO t1}] } {1 {Error: cannot open "FOO"}} # empty import file do_test shell5-1.4.2 { - file delete -force shell5.csv + forcedelete shell5.csv set in [open shell5.csv w] close $in set res [catchcmd "test.db" {.import shell5.csv t1 @@ -101,7 +103,7 @@ do_test shell5-1.4.3 { puts $in "1" close $in set res [catchcmd "test.db" {.import shell5.csv t1}] -} {1 {Error: shell5.csv line 1: expected 2 columns of data but found 1}} +} {1 {shell5.csv:1: expected 2 columns but found 1 - filling the rest with NULL}} # import file with 1 row, 3 columns (expecting 2 cols) do_test shell5-1.4.4 { @@ -109,14 +111,15 @@ do_test shell5-1.4.4 { puts $in "1|2|3" close $in set res [catchcmd "test.db" {.import shell5.csv t1}] -} {1 {Error: shell5.csv line 1: expected 2 columns of data but found 3}} +} {1 {shell5.csv:1: expected 2 columns but found 3 - extras ignored}} # import file with 1 row, 2 columns do_test shell5-1.4.5 { set in [open shell5.csv w] puts $in "1|2" close $in - set res [catchcmd "test.db" {.import shell5.csv t1 + set res [catchcmd "test.db" {DELETE FROM t1; +.import shell5.csv t1 SELECT COUNT(*) FROM t1;}] } {0 1} @@ -197,15 +200,15 @@ SELECT length(b) FROM t1 WHERE a='8';}] # This is limited by SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999. set cols 999 do_test shell5-1.6.1 { - set sql {CREATE TABLE t2(} set data {} for {set i 1} {$i<$cols} {incr i} { - append sql "c$i," + append data "c$i|" + } + append data "c$cols\n"; + for {set i 1} {$i<$cols} {incr i} { append data "$i|" } - append sql "c$cols);" append data "$cols" - catchcmd "test.db" $sql set in [open shell5.csv w] puts $in $data close $in @@ -214,16 +217,160 @@ SELECT COUNT(*) FROM t2;}] } {0 1} # try importing a large number of rows -set rows 999999 +set rows 9999 do_test shell5-1.7.1 { set in [open shell5.csv w] + puts $in a for {set i 1} {$i<=$rows} {incr i} { puts $in $i } close $in - set res [catchcmd "test.db" {CREATE TABLE t3(a); + set res [catchcmd "test.db" {.mode csv .import shell5.csv t3 SELECT COUNT(*) FROM t3;}] } [list 0 $rows] +# Inport from a pipe. (Unix only, as it requires "awk") +if {$tcl_platform(platform)=="unix"} { + do_test shell5-1.8 { + forcedelete test.db + catchcmd test.db {.mode csv +.import "|awk 'END{print \"x,y\";for(i=1;i<=5;i++){print i \",this is \" i}}'" t1 +SELECT * FROM t1;} + } {0 {1,"this is 1" +2,"this is 2" +3,"this is 3" +4,"this is 4" +5,"this is 5"}} +} + +# Import columns containing quoted strings +do_test shell5-1.9 { + set out [open shell5.csv w] + fconfigure $out -translation lf + puts $out {1,"",11} + puts $out {2,"x",22} + puts $out {3,"""",33} + puts $out {4,"hello",44} + puts $out "5,55,\"\"\r" + puts $out {6,66,"x"} + puts $out {7,77,""""} + puts $out {8,88,"hello"} + puts $out {"",9,99} + puts $out {"x",10,110} + puts $out {"""",11,121} + puts $out {"hello",12,132} + close $out + forcedelete test.db + catchcmd test.db {.mode csv + CREATE TABLE t1(a,b,c); +.import shell5.csv t1 + } + sqlite3 db test.db + db eval {SELECT *, '|' FROM t1 ORDER BY rowid} +} {1 {} 11 | 2 x 22 | 3 {"} 33 | 4 hello 44 | 5 55 {} | 6 66 x | 7 77 {"} | 8 88 hello | {} 9 99 | x 10 110 | {"} 11 121 | hello 12 132 |} +db close + +# Import columns containing quoted strings +do_test shell5-1.10 { + set out [open shell5.csv w] + fconfigure $out -translation lf + puts $out {column1,column2,column3,column4} + puts $out "field1,field2,\"x3 \"\"\r\ndata\"\" 3\",field4" + puts $out "x1,x2,\"x3 \"\"\ndata\"\" 3\",x4" + close $out + forcedelete test.db + catchcmd test.db {.mode csv + CREATE TABLE t1(a,b,c,d); +.import shell5.csv t1 + } + sqlite3 db test.db + db eval {SELECT hex(c) FROM t1 ORDER BY rowid} +} {636F6C756D6E33 783320220D0A64617461222033 783320220A64617461222033} + +# Blank last column with \r\n line endings. +do_test shell5-1.11 { + set out [open shell5.csv w] + fconfigure $out -translation binary + puts $out "column1,column2,column3\r" + puts $out "a,b, \r" + puts $out "x,y,\r" + puts $out "p,q,r\r" + close $out + catch {db close} + forcedelete test.db + catchcmd test.db {.mode csv +.import shell5.csv t1 + } + sqlite3 db test.db + db eval {SELECT *, '|' FROM t1} +} {a b { } | x y {} | p q r |} +db close + +#---------------------------------------------------------------------------- +# +reset_db +sqlite3 db test.db +do_test shell5-2.1 { + set fd [open shell5.csv w] + puts $fd ",hello" + close $fd + catchcmd test.db [string trim { +.mode csv +CREATE TABLE t1(a, b); +.import shell5.csv t1 + }] + db eval { SELECT * FROM t1 } +} {{} hello} + +do_test shell5-2.2 { + set fd [open shell5.csv w] + puts $fd {"",hello} + close $fd + catchcmd test.db [string trim { +.mode csv +CREATE TABLE t2(a, b); +.import shell5.csv t2 + }] + db eval { SELECT * FROM t2 } +} {{} hello} + +do_test shell5-2.3 { + set fd [open shell5.csv w] + puts $fd {"x""y",hello} + close $fd + catchcmd test.db [string trim { +.mode csv +CREATE TABLE t3(a, b); +.import shell5.csv t3 + }] + db eval { SELECT * FROM t3 } +} {x\"y hello} + +do_test shell5-2.4 { + set fd [open shell5.csv w] + puts $fd {"xy""",hello} + close $fd + catchcmd test.db [string trim { +.mode csv +CREATE TABLE t4(a, b); +.import shell5.csv t4 + }] + db eval { SELECT * FROM t4 } +} {xy\" hello} + +do_test shell5-2.5 { + set fd [open shell5.csv w] + puts $fd {"one","2"} + puts $fd {} + close $fd + catchcmd test.db [string trim { +.mode csv +CREATE TABLE t4(a, b); +.import shell5.csv t4 + }] + db eval { SELECT * FROM t4 } +} {xy\" hello one 2 {} {}} + + finish_test |