# 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 CLI "./sqlite3" proc do_test {name cmd expected} { puts -nonewline "$name ..." set res [uplevel $cmd] if {$res eq $expected} { puts Ok } else { puts Error puts " Got: $res" puts " Expected: $expected" exit } } proc catchcmd {db {cmd ""}} { global CLI set out [open cmds.txt w] puts $out $cmd close $out set line "exec $CLI $db < cmds.txt" set rc [catch { eval $line } msg] list $rc $msg } file delete -force test.db test.db.journal #---------------------------------------------------------------------------- # 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] puts "CLI tests completed successfully"