diff options
Diffstat (limited to 'test/shell5.test')
-rw-r--r-- | test/shell5.test | 229 |
1 files changed, 229 insertions, 0 deletions
diff --git a/test/shell5.test b/test/shell5.test new file mode 100644 index 0000000..d90cedf --- /dev/null +++ b/test/shell5.test @@ -0,0 +1,229 @@ +# 2010 August 4 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# +# The focus of this file is testing the CLI shell tool. +# These tests are specific to the .import command. +# +# $Id: shell5.test,v 1.7 2009/07/17 16:54:48 shaneh Exp $ +# + +# Test plan: +# +# shell5-1.*: Basic tests specific to the ".import" command. +# +set testdir [file dirname $argv0] +source $testdir/tester.tcl +if {$tcl_platform(platform)=="windows"} { + set CLI "sqlite3.exe" +} else { + set CLI "./sqlite3" +} +if {![file executable $CLI]} { + finish_test + return +} +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. +# + +# .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}} +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}} +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}} + +# .separator STRING Change separator used by output mode and .import +do_test shell1-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" +} {0 {}} +do_test shell1-1.2.3 { + # too many arguments + catchcmd "test.db" ".separator FOO BAD" +} {1 {Error: unknown command or invalid arguments: "separator". Enter ".help" for help}} + +# separator should default to "|" +do_test shell5-1.3.1 { + set res [catchcmd "test.db" ".show"] + list [regexp {separator: \"\|\"} $res] +} {1} + +# set separator to different value. +# check that .show reports new value +do_test shell5-1.3.2 { + set res [catchcmd "test.db" {.separator , +.show}] + list [regexp {separator: \",\"} $res] +} {1} + +# import file doesn't exist +do_test shell5-1.4.1 { + file delete -force 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 + set in [open shell5.csv w] + close $in + set res [catchcmd "test.db" {.import shell5.csv t1 +SELECT COUNT(*) FROM t1;}] +} {0 0} + +# import file with 1 row, 1 column (expecting 2 cols) +do_test shell5-1.4.3 { + set in [open shell5.csv w] + 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}} + +# import file with 1 row, 3 columns (expecting 2 cols) +do_test shell5-1.4.4 { + set in [open shell5.csv w] + 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}} + +# 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 +SELECT COUNT(*) FROM t1;}] +} {0 1} + +# import file with 2 rows, 2 columns +# note we end up with 3 rows because of the 1 row +# imported above. +do_test shell5-1.4.6 { + set in [open shell5.csv w] + puts $in "2|3" + puts $in "3|4" + close $in + set res [catchcmd "test.db" {.import shell5.csv t1 +SELECT COUNT(*) FROM t1;}] +} {0 3} + +# import file with 1 row, 2 columns, using a comma +do_test shell5-1.4.7 { + set in [open shell5.csv w] + puts $in "4,5" + close $in + set res [catchcmd "test.db" {.separator , +.import shell5.csv t1 +SELECT COUNT(*) FROM t1;}] +} {0 4} + +# import file with 1 row, 2 columns, text data +do_test shell5-1.4.8.1 { + set in [open shell5.csv w] + puts $in "5|Now is the time for all good men to come to the aid of their country." + close $in + set res [catchcmd "test.db" {.import shell5.csv t1 +SELECT COUNT(*) FROM t1;}] +} {0 5} + +do_test shell5-1.4.8.2 { + catchcmd "test.db" {SELECT b FROM t1 WHERE a='5';} +} {0 {Now is the time for all good men to come to the aid of their country.}} + +# import file with 1 row, 2 columns, quoted text data +# note that currently sqlite doesn't support quoted fields, and +# imports the entire field, quotes and all. +do_test shell5-1.4.9.1 { + set in [open shell5.csv w] + puts $in "6|'Now is the time for all good men to come to the aid of their country.'" + close $in + set res [catchcmd "test.db" {.import shell5.csv t1 +SELECT COUNT(*) FROM t1;}] +} {0 6} + +do_test shell5-1.4.9.2 { + catchcmd "test.db" {SELECT b FROM t1 WHERE a='6';} +} {0 {'Now is the time for all good men to come to the aid of their country.'}} + +# import file with 1 row, 2 columns, quoted text data +do_test shell5-1.4.10.1 { + set in [open shell5.csv w] + puts $in "7|\"Now is the time for all good men to come to the aid of their country.\"" + close $in + set res [catchcmd "test.db" {.import shell5.csv t1 +SELECT COUNT(*) FROM t1;}] +} {0 7} + +do_test shell5-1.4.10.2 { + catchcmd "test.db" {SELECT b FROM t1 WHERE a='7';} +} {0 {Now is the time for all good men to come to the aid of their country.}} + +# check importing very long field +do_test shell5-1.5.1 { + set str [string repeat X 999] + set in [open shell5.csv w] + puts $in "8|$str" + close $in + set res [catchcmd "test.db" {.import shell5.csv t1 +SELECT length(b) FROM t1 WHERE a='8';}] +} {0 999} + +# try importing into a table with a large number of columns. +# 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 "$i|" + } + append sql "c$cols);" + append data "$cols" + catchcmd "test.db" $sql + set in [open shell5.csv w] + puts $in $data + close $in + set res [catchcmd "test.db" {.import shell5.csv t2 +SELECT COUNT(*) FROM t2;}] +} {0 1} + +# try importing a large number of rows +set rows 999999 +do_test shell5-1.7.1 { + set in [open shell5.csv w] + for {set i 1} {$i<=$rows} {incr i} { + puts $in $i + } + close $in + set res [catchcmd "test.db" {CREATE TABLE t3(a); +.import shell5.csv t3 +SELECT COUNT(*) FROM t3;}] +} [list 0 $rows] + +finish_test |