| # 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 |
| set CLI [test_cli_invocation] |
| db close |
| forcedelete test.db test.db-journal test.db-wal |
| |
| #---------------------------------------------------------------------------- |
| # 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: missing FILE argument.*/} |
| do_test shell5-1.1.2 { |
| catchcmd "test.db" ".import FOO" |
| } {/1 .ERROR: missing TABLE argument.*/} |
| do_test shell5-1.1.3 { |
| # too many arguments |
| catchcmd "test.db" ".import FOO BAR BAD" |
| } {/1 .ERROR: extra argument.*/} |
| |
| # .separator STRING Change separator used by output mode and .import |
| do_test shell5-1.2.1 { |
| catchcmd "test.db" ".separator" |
| } {1 {Usage: .separator COL ?ROW?}} |
| do_test shell5-1.2.2 { |
| catchcmd "test.db" ".separator ONE" |
| } {0 {}} |
| 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 ONE TWO THREE" |
| } {1 {Usage: .separator COL ?ROW?}} |
| |
| # column separator should default to "|" |
| do_test shell5-1.3.1.1 { |
| set res [catchcmd "test.db" ".show"] |
| list [regexp {colseparator: \"\|\"} $res] |
| } {1} |
| |
| # row separator should default to "\n" |
| do_test shell5-1.3.1.2 { |
| set res [catchcmd "test.db" ".show"] |
| list [regexp {rowseparator: \"\\n\"} $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 { |
| 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 { |
| forcedelete shell5.csv |
| set in [open shell5.csv w] |
| close $in |
| set res [catchcmd ":memory:" {ATTACH 'test.db' AS test; |
| .import -schema test shell5.csv t1 |
| SELECT COUNT(*) FROM test.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 ":memory:" {ATTACH 'test.db' AS test; |
| .import -schema test shell5.csv t1}] |
| } {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 { |
| set in [open shell5.csv w] |
| puts $in "1|2|3" |
| close $in |
| set res [catchcmd ":memory:" {ATTACH 'test.db' AS test; |
| .import --schema test shell5.csv t1}] |
| } {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" {DELETE FROM t1; |
| .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 ":memory:" {ATTACH 'test.db' AS test; |
| .import -schema test shell5.csv t1 |
| SELECT COUNT(*) FROM test.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 ":memory:" {ATTACH 'test.db' AS test; |
| .separator , |
| .import --schema test shell5.csv t1 |
| SELECT COUNT(*) FROM test.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.}} |
| |
| # import file with 2 rows, 2 columns and an initial BOM |
| # |
| do_test shell5-1.4.11 { |
| set in [open shell5.csv wb] |
| puts -nonewline $in "\xef\xbb\xbf" |
| puts $in "2|3" |
| puts $in "4|5" |
| close $in |
| set res [catchcmd "test.db" {CREATE TABLE t2(x INT, y INT); |
| .import shell5.csv t2 |
| .mode quote |
| .header on |
| SELECT * FROM t2;}] |
| string map {\n | \n\r |} $res |
| } {0 {'x','y'|2,3|4,5}} |
| |
| # import file with 2 rows, 2 columns or text with an initial BOM |
| # |
| do_test shell5-1.4.12 { |
| set in [open shell5.csv wb] |
| puts $in "\xef\xbb\xbf\"two\"|3" |
| puts $in "4|5" |
| close $in |
| set res [catchcmd "test.db" {DELETE FROM t2; |
| .import shell5.csv t2 |
| .mode quote |
| .header on |
| SELECT * FROM t2;}] |
| string map {\n | \n\r |} $res |
| } {0 {'x','y'|'two',3|4,5}} |
| |
| # 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 data {} |
| for {set i 1} {$i<$cols} {incr i} { |
| append data "c$i|" |
| } |
| append data "c$cols\n"; |
| for {set i 1} {$i<$cols} {incr i} { |
| append data "$i|" |
| } |
| append data "$cols" |
| set in [open shell5.csv w] |
| puts $in $data |
| close $in |
| set res [catchcmd "test.db" {DROP TABLE IF EXISTS t2; |
| .import shell5.csv t2 |
| SELECT COUNT(*) FROM t2;}] |
| } {0 1} |
| |
| # try importing a large number of rows |
| 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" {.mode csv |
| .import shell5.csv t3 |
| SELECT COUNT(*) FROM t3;}] |
| } [list 0 $rows] |
| |
| # Import 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 {} {}} |
| |
| #---------------------------------------------------------------------------- |
| # Tests for the shell "ascii" import/export mode. |
| # |
| do_test shell5-3.1 { |
| set fd [open shell5.csv w] |
| fconfigure $fd -encoding binary -translation binary |
| puts -nonewline $fd "\"test 1\"\x1F,test 2\r\n\x1E" |
| puts -nonewline $fd "test 3\x1Ftest 4\n" |
| close $fd |
| catchcmd test.db { |
| .mode ascii |
| CREATE TABLE t5(a, b); |
| .import shell5.csv t5 |
| } |
| db eval { SELECT * FROM t5 } |
| } "\{\"test 1\"} \{,test 2\r\n\} \{test 3\} \{test 4\n\}" |
| |
| do_test shell5-3.2 { |
| set x [catchcmd test.db { |
| .mode ascii |
| SELECT * FROM t5; |
| }] |
| # Handle platform end-of-line differences |
| regsub -all {[\n\r]?\n} $x <EOL> x |
| set x |
| } "0 \{\"test 1\"\x1F,test 2<EOL>\x1Etest 3\x1Ftest 4<EOL>\x1E\}" |
| |
| do_test shell5-4.1 { |
| forcedelete shell5.csv |
| set fd [open shell5.csv w] |
| puts $fd "1,2,3" |
| puts $fd "4,5" |
| puts $fd "6,7,8" |
| close $fd |
| catchcmd test.db [string trim { |
| .mode csv |
| CREATE TABLE t6(a, b, c); |
| .import shell5.csv t6 |
| }] |
| db eval { SELECT * FROM t6 ORDER BY a } |
| } {1 2 3 4 5 {} 6 7 8} |
| |
| do_test shell5-4.2 { |
| forcedelete shell5.csv |
| set fd [open shell5.csv w] |
| puts $fd "1,2,3" |
| puts $fd "4,5" |
| puts $fd "6,7,8,9" |
| close $fd |
| catchcmd test.db [string trim { |
| .mode csv |
| CREATE TABLE t7(a, b, c); |
| .import shell5.csv t7 |
| }] |
| db eval { SELECT * FROM t7 ORDER BY a } |
| } {1 2 3 4 5 {} 6 7 8} |
| |
| do_test shell5-4.3 { |
| forcedelete shell5.csv |
| set fd [open shell5.csv w] |
| puts $fd ",," |
| puts $fd "1,2,3" |
| close $fd |
| catchcmd test.db [string trim { |
| .mode csv |
| CREATE TABLE t8(a, b, c); |
| .import -skip 1 shell5.csv t8 |
| .nullvalue # |
| }] |
| db eval { SELECT * FROM t8 } |
| } {1 2 3} |
| |
| do_test shell5-4.4 { |
| forcedelete shell5.csv |
| set fd [open shell5.csv w] |
| puts $fd "1,2,3" |
| close $fd |
| catchcmd test.db [string trim { |
| .mode csv |
| CREATE TEMP TABLE t8(a, b, c); |
| .import shell5.csv t8 |
| .nullvalue # |
| SELECT * FROM temp.t8 |
| }] |
| } {0 1,2,3} |
| |
| #---------------------------------------------------------------------------- |
| # Tests for the shell automatic column rename. |
| # |
| db close |
| |
| # Import columns containing duplicates |
| do_test shell5-5.1 { |
| set out [open shell5.csv w] |
| fconfigure $out -translation lf |
| puts $out {"","x","x","y","z","z_0","z_5","z"} |
| puts $out {0,"x2","x3","y4","z5","z6","z7","z8"} |
| close $out |
| forcedelete test.db |
| catchcmd test.db {.import -csv shell5.csv t1 |
| .mode line |
| SELECT * FROM t1;} |
| } {1 { ? = 0 |
| x_02 = x2 |
| x_03 = x3 |
| y = y4 |
| z_05 = z5 |
| z_0 = z6 |
| z_5 = z7 |
| z_08 = z8 |
| Columns renamed during .import shell5.csv due to duplicates: |
| "x" to "x_02", |
| "x" to "x_03", |
| "z" to "z_05", |
| "z" to "z_08"}} |
| |
| do_test shell5-5.1 { |
| set out [open shell5.csv w] |
| fconfigure $out -translation lf |
| puts $out {"COW","cow","CoW","cOw"} |
| puts $out {"uuu","lll","ulu","lul"} |
| close $out |
| forcedelete test.db |
| catchcmd test.db {.import -csv shell5.csv t1 |
| .mode line |
| SELECT * FROM t1;} |
| } {1 {COW_1 = uuu |
| cow_2 = lll |
| CoW_3 = ulu |
| cOw_4 = lul |
| Columns renamed during .import shell5.csv due to duplicates: |
| "COW" to "COW_1", |
| "cow" to "cow_2", |
| "CoW" to "CoW_3", |
| "cOw" to "cOw_4"}} |
| |
| #---------------------------------------------------------------------------- |
| # Tests for preserving utf-8 that is not also ASCII. |
| # |
| |
| do_test shell5-6.1 { |
| set out [open shell5.csv w] |
| fconfigure $out -translation lf |
| puts $out {あい,うえお} |
| puts $out {1,2} |
| close $out |
| forcedelete test.db |
| catchcmd test.db {.import -csv shell5.csv t1 |
| .mode line |
| SELECT * FROM t1;} |
| } {0 { あい = 1 |
| うえお = 2}} |
| |
| do_test shell5-6.2 { |
| set out [open shell5.csv w] |
| fconfigure $out -translation lf |
| puts $out {1,2} |
| puts $out {あい,うえお} |
| close $out |
| forcedelete test.db |
| catchcmd test.db {.import -csv shell5.csv t1 |
| .mode line |
| SELECT * FROM t1;} |
| } {0 { 1 = あい |
| 2 = うえお}} |
| |
| finish_test |