AryaWu/sqlite
0
1# 2010 August 42#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# TESTRUNNER: shell12#13# The focus of this file is testing the CLI shell tool.14# These tests are specific to the .import command.15#16# $Id: shell5.test,v 1.7 2009/07/17 16:54:48 shaneh Exp $17#18 19# Test plan:20#21# shell5-1.*: Basic tests specific to the ".import" command.22#23set testdir [file dirname $argv0]24source $testdir/tester.tcl25set CLI [test_cli_invocation]26db close27forcedelete test.db test.db-journal test.db-wal28 29#----------------------------------------------------------------------------30# Test cases shell5-1.*: Basic handling of the .import and .separator commands.31#32 33# .import FILE TABLE Import data from FILE into TABLE34do_test shell5-1.1.1 {35 catchcmd "test.db" ".import"36} {/1 .ERROR: missing FILE argument.*/}37do_test shell5-1.1.2 {38 catchcmd "test.db" ".import FOO"39} {/1 .ERROR: missing TABLE argument.*/}40do_test shell5-1.1.3 {41 # too many arguments42 catchcmd "test.db" ".import FOO BAR BAD"43} {1 {line 1: .import FOO BAR BAD44line 1: ^--- unknown argument}}45 46# .separator STRING Change separator used by output mode and .import47do_test shell5-1.2.1 {48 catchcmd "test.db" ".separator"49} {1 {Usage: .separator COL ?ROW?}}50do_test shell5-1.2.2 {51 catchcmd "test.db" ".separator ONE"52} {0 {}}53do_test shell5-1.2.3 {54 catchcmd "test.db" ".separator ONE TWO"55} {0 {}}56do_test shell5-1.2.4 {57 # too many arguments58 catchcmd "test.db" ".separator ONE TWO THREE"59} {1 {Usage: .separator COL ?ROW?}}60 61# column separator should default to "|"62do_test shell5-1.3.1.1 {63 set res [catchcmd "test.db" ".mode list\n.show"]64 list [regexp {colseparator: \"\|\"} $res]65} {1}66 67# row separator should default to "\n"68do_test shell5-1.3.1.2 {69 set res [catchcmd "test.db" ".mode list\n.show"]70 list [regexp {rowseparator: \"\\n\"} $res]71} {1}72 73# set separator to different value.74# check that .show reports new value75do_test shell5-1.3.2 {76 set res [catchcmd "test.db" {.separator ,77.show}]78 list [regexp {separator: \",\"} $res]79} {1}80 81# import file doesn't exist82do_test shell5-1.4.1 {83 forcedelete FOO84 set res [catchcmd "test.db" {CREATE TABLE t1(a, b);85.import FOO t1}]86} {1 {Error: cannot open "FOO"}}87 88# the remainder of these test cases require virtual tables.89#90ifcapable !vtab {91 puts "Skipping subsequent tests due to SQLITE_OMIT_VIRTUALTABLE"92 finish_test93 return94}95 96# empty import file97do_test shell5-1.4.2 {98 forcedelete shell5.csv99 set in [open shell5.csv w]100 close $in101 set res [catchcmd ":memory:" {102.mode list103ATTACH 'test.db' AS test;104.import -schema test shell5.csv t1105SELECT COUNT(*) FROM test.t1;}]106} {0 0}107 108# import file with 1 row, 1 column (expecting 2 cols)109do_test shell5-1.4.3 {110 set in [open shell5.csv w]111 puts $in "1"112 close $in113 set res [catchcmd ":memory:" {ATTACH 'test.db' AS test;114.import -schema test shell5.csv t1}]115} {1 {shell5.csv:1: expected 2 columns but found 1 - filling the rest with NULL}}116 117# import file with 1 row, 3 columns (expecting 2 cols)118do_test shell5-1.4.4 {119 set in [open shell5.csv w]120 puts $in "1|2|3"121 close $in122 set res [catchcmd ":memory: --list" {ATTACH 'test.db' AS test;123.import --schema test shell5.csv t1}]124} {1 {shell5.csv:1: expected 2 columns but found 3 - extras ignored}}125 126# import file with 1 row, 2 columns127do_test shell5-1.4.5 {128 set in [open shell5.csv w]129 puts $in "1|2"130 close $in131 set res [catchcmd "test.db -list" {DELETE FROM t1;132.import shell5.csv t1133SELECT COUNT(*) FROM t1;}]134} {0 1}135 136# import file with 2 rows, 2 columns137# note we end up with 3 rows because of the 1 row 138# imported above.139do_test shell5-1.4.6 {140 set in [open shell5.csv w]141 puts $in "2|3"142 puts $in "3|4"143 close $in144 set res [catchcmd ":memory:" {145.mode list146ATTACH 'test.db' AS test;147.import -schema test shell5.csv t1148SELECT COUNT(*) FROM test.t1;}]149} {0 3}150 151# import file with 1 row, 2 columns, using a comma152do_test shell5-1.4.7 {153 set in [open shell5.csv w]154 puts $in "4,5"155 close $in156 set res [catchcmd ":memory:" {157.mode list158ATTACH 'test.db' AS test;159.separator ,160.import --schema test shell5.csv t1161SELECT COUNT(*) FROM test.t1;}]162} {0 4}163 164# import file with 1 row, 2 columns, text data165do_test shell5-1.4.8.1 {166 set in [open shell5.csv w]167 puts $in "5|Now is the time for all good men to come to the aid of their country."168 close $in169 set res [catchcmd "test.db" {.mode list170.import shell5.csv t1171SELECT COUNT(*) FROM t1;}]172} {0 5}173 174do_test shell5-1.4.8.2 {175 catchcmd "test.db -list" {SELECT b FROM t1 WHERE a='5';}176} {0 {Now is the time for all good men to come to the aid of their country.}}177 178# import file with 1 row, 2 columns, quoted text data179# note that currently sqlite doesn't support quoted fields, and180# imports the entire field, quotes and all.181do_test shell5-1.4.9.1 {182 set in [open shell5.csv w]183 puts $in "6|'Now is the time for all good men to come to the aid of their country.'"184 close $in185 set res [catchcmd "test.db -list" {.import shell5.csv t1186SELECT COUNT(*) FROM t1;}]187} {0 6}188 189do_test shell5-1.4.9.2 {190 catchcmd "test.db -list" {SELECT b FROM t1 WHERE a='6';}191} {0 {'Now is the time for all good men to come to the aid of their country.'}}192 193# import file with 1 row, 2 columns, quoted text data194do_test shell5-1.4.10.1 {195 set in [open shell5.csv w]196 puts $in "7|\"Now is the time for all good men to come to the aid of their country.\""197 close $in198 set res [catchcmd "test.db -list" {.import shell5.csv t1199SELECT COUNT(*) FROM t1;}]200} {0 7}201 202do_test shell5-1.4.10.2 {203 catchcmd "test.db -list" {SELECT b FROM t1 WHERE a='7';}204} {0 {Now is the time for all good men to come to the aid of their country.}}205 206# import file with 2 rows, 2 columns and an initial BOM207#208do_test shell5-1.4.11 {209 set in [open shell5.csv wb]210 puts -nonewline $in "\xef\xbb\xbf"211 puts $in "2|3"212 puts $in "4|5"213 close $in214 set res [catchcmd "test.db -list" {CREATE TABLE t2(x INT, y INT);215.import shell5.csv t2216.mode quote217.header on218SELECT * FROM t2;}]219 string map {\n | \n\r |} $res220} {0 {'x','y'|2,3|4,5}}221 222# import file with 2 rows, 2 columns or text with an initial BOM223#224do_test shell5-1.4.12 {225 set in [open shell5.csv wb]226 puts $in "\xef\xbb\xbf\"two\"|3"227 puts $in "4|5"228 close $in229 set res [catchcmd "test.db -list" {DELETE FROM t2;230.import shell5.csv t2231.mode quote232.header on233SELECT * FROM t2;}]234 string map {\n | \n\r |} $res235} {0 {'x','y'|'two',3|4,5}}236 237# check importing very long field238do_test shell5-1.5.1 {239 set str [string repeat X 999]240 set in [open shell5.csv w]241 puts $in "8|$str"242 close $in243 set res [catchcmd "test.db -list" {.import shell5.csv t1244SELECT length(b) FROM t1 WHERE a='8';}]245} {0 999}246 247# try importing into a table with a large number of columns.248# This is limited by SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999.249set cols 999250do_test shell5-1.6.1 {251 set data {}252 for {set i 1} {$i<$cols} {incr i} {253 append data "c$i|"254 }255 append data "c$cols\n";256 for {set i 1} {$i<$cols} {incr i} {257 append data "$i|"258 }259 append data "$cols"260 set in [open shell5.csv w]261 puts $in $data262 close $in263 set res [catchcmd "test.db -list" {DROP TABLE IF EXISTS t2;264.import shell5.csv t2265SELECT COUNT(*) FROM t2;}]266} {0 1}267 268# try importing a large number of rows269set rows 9999270do_test shell5-1.7.1 {271 set in [open shell5.csv w]272 puts $in a273 for {set i 1} {$i<=$rows} {incr i} {274 puts $in $i275 }276 close $in277 set res [catchcmd "test.db" {.mode csv278.import shell5.csv t3279SELECT COUNT(*) FROM t3;}]280} [list 0 $rows]281 282# Import from a pipe. (Unix only, as it requires "awk")283if {$tcl_platform(platform)=="unix"} {284 do_test shell5-1.8 {285 forcedelete test.db286 catchcmd test.db {.mode csv287.import "|awk 'END{print \"x,y\";for(i=1;i<=5;i++){print i \",this is \" i}}'" t1288SELECT * FROM t1;}289 } {0 {1,"this is 1"2902,"this is 2"2913,"this is 3"2924,"this is 4"2935,"this is 5"}}294}295 296# Import columns containing quoted strings297do_test shell5-1.9 {298 set out [open shell5.csv w]299 fconfigure $out -translation lf300 puts $out {1,"",11}301 puts $out {2,"x",22}302 puts $out {3,"""",33}303 puts $out {4,"hello",44}304 puts $out "5,55,\"\"\r"305 puts $out {6,66,"x"}306 puts $out {7,77,""""}307 puts $out {8,88,"hello"}308 puts $out {"",9,99}309 puts $out {"x",10,110}310 puts $out {"""",11,121}311 puts $out {"hello",12,132}312 close $out313 forcedelete test.db314 catchcmd test.db {.mode csv315 CREATE TABLE t1(a,b,c);316.import shell5.csv t1317 }318 sqlite3 db test.db319 db eval {SELECT *, '|' FROM t1 ORDER BY rowid}320} {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 |}321db close322 323# Import columns containing quoted strings324do_test shell5-1.10 {325 set out [open shell5.csv w]326 fconfigure $out -translation lf327 puts $out {column1,column2,column3,column4}328 puts $out "field1,field2,\"x3 \"\"\r\ndata\"\" 3\",field4"329 puts $out "x1,x2,\"x3 \"\"\ndata\"\" 3\",x4"330 close $out331 forcedelete test.db332 catchcmd test.db {.mode csv333 CREATE TABLE t1(a,b,c,d);334.import shell5.csv t1335 }336 sqlite3 db test.db337 db eval {SELECT hex(c) FROM t1 ORDER BY rowid}338} {636F6C756D6E33 783320220D0A64617461222033 783320220A64617461222033}339 340# Blank last column with \r\n line endings.341do_test shell5-1.11 {342 set out [open shell5.csv w]343 fconfigure $out -translation binary344 puts $out "column1,column2,column3\r"345 puts $out "a,b, \r"346 puts $out "x,y,\r"347 puts $out "p,q,r\r"348 close $out349 catch {db close}350 forcedelete test.db351 catchcmd test.db {.mode csv352.import shell5.csv t1353 }354 sqlite3 db test.db355 db eval {SELECT *, '|' FROM t1}356} {a b { } | x y {} | p q r |}357db close358 359#----------------------------------------------------------------------------360# 361reset_db362sqlite3 db test.db363do_test shell5-2.1 {364 set fd [open shell5.csv w]365 puts $fd ",hello"366 close $fd367 catchcmd test.db [string trim {368.mode csv369CREATE TABLE t1(a, b);370.import shell5.csv t1371 }]372 db eval { SELECT * FROM t1 }373} {{} hello}374 375do_test shell5-2.2 {376 set fd [open shell5.csv w]377 puts $fd {"",hello}378 close $fd379 catchcmd test.db [string trim {380.mode csv381CREATE TABLE t2(a, b);382.import shell5.csv t2383 }]384 db eval { SELECT * FROM t2 }385} {{} hello}386 387do_test shell5-2.3 {388 set fd [open shell5.csv w]389 puts $fd {"x""y",hello}390 close $fd391 catchcmd test.db [string trim {392.mode csv393CREATE TABLE t3(a, b);394.import shell5.csv t3395 }]396 db eval { SELECT * FROM t3 }397} {x\"y hello}398 399do_test shell5-2.4 {400 set fd [open shell5.csv w]401 puts $fd {"xy""",hello}402 close $fd403 catchcmd test.db [string trim {404.mode csv405CREATE TABLE t4(a, b);406.import shell5.csv t4407 }]408 db eval { SELECT * FROM t4 }409} {xy\" hello}410 411do_test shell5-2.5 {412 set fd [open shell5.csv w]413 puts $fd {"one","2"}414 puts $fd {}415 close $fd416 catchcmd test.db [string trim {417.mode csv418CREATE TABLE t4(a, b);419.import shell5.csv t4420 }]421 db eval { SELECT * FROM t4 }422} {xy\" hello one 2 {} {}}423 424#----------------------------------------------------------------------------425# Tests for the shell "ascii" import/export mode.426#427do_test shell5-3.1 {428 set fd [open shell5.csv w]429 fconfigure $fd -translation binary430 puts -nonewline $fd "\"test 1\"\x1F,test 2\r\n\x1E"431 puts -nonewline $fd "test 3\x1Ftest 4\n"432 close $fd433 catchcmd test.db {434.mode ascii435CREATE TABLE t5(a, b);436.import shell5.csv t5437 }438 db eval { SELECT * FROM t5 }439} "\{\"test 1\"} \{,test 2\r\n\} \{test 3\} \{test 4\n\}"440 441do_test shell5-3.2 {442 set x [catchcmd test.db {443.mode ascii444SELECT * FROM t5;445 }]446 # Handle platform end-of-line differences447 regsub -all {[\n\r]?\n} $x <EOL> x448 set x449} "0 \{\"test 1\"\x1F,test 2<EOL>\x1Etest 3\x1Ftest 4<EOL>\x1E\}"450 451do_test shell5-4.1 {452 forcedelete shell5.csv453 set fd [open shell5.csv w]454 puts $fd "1,2,3"455 puts $fd "4,5"456 puts $fd "6,7,8"457 close $fd458 catchcmd test.db [string trim {459.mode csv460CREATE TABLE t6(a, b, c);461.import shell5.csv t6462 }]463 db eval { SELECT * FROM t6 ORDER BY a }464} {1 2 3 4 5 {} 6 7 8}465 466do_test shell5-4.2 {467 forcedelete shell5.csv468 set fd [open shell5.csv w]469 puts $fd "1,2,3"470 puts $fd "4,5"471 puts $fd "6,7,8,9"472 close $fd473 catchcmd test.db [string trim {474.mode csv475CREATE TABLE t7(a, b, c);476.import shell5.csv t7477 }]478 db eval { SELECT * FROM t7 ORDER BY a }479} {1 2 3 4 5 {} 6 7 8}480 481do_test shell5-4.3 {482 forcedelete shell5.csv483 set fd [open shell5.csv w]484 puts $fd ",,"485 puts $fd "1,2,3"486 close $fd487 catchcmd test.db [string trim {488.mode csv489CREATE TABLE t8(a, b, c);490.import -skip 1 shell5.csv t8491.nullvalue #492 }]493 db eval { SELECT * FROM t8 }494} {1 2 3}495 496do_test shell5-4.4 {497 forcedelete shell5.csv498 set fd [open shell5.csv w]499 puts $fd "1,2,3"500 close $fd501 catchcmd test.db [string trim {502.mode csv503CREATE TEMP TABLE t8(a, b, c);504.import shell5.csv t8505.nullvalue #506SELECT * FROM temp.t8507 }]508} {0 1,2,3}509 510#----------------------------------------------------------------------------511# Tests for the shell automatic column rename.512#513db close514 515# Import columns containing duplicates516do_test shell5-5.1 {517 set out [open shell5.csv w]518 fconfigure $out -translation lf519 puts $out {"","x","x","y","z","z_0","z_5","z"}520 puts $out {0,"x2","x3","y4","z5","z6","z7","z8"}521 close $out522 forcedelete test.db523 catchcmd test.db {.import -csv shell5.csv t1524.mode line --colsep ' = '525SELECT * FROM t1;}526} {1 { ? = 0527 x_02 = x2528 x_03 = x3529 y = y4530 z_05 = z5531 z_0 = z6532 z_5 = z7533 z_08 = z8534Columns renamed during .import shell5.csv due to duplicates:535"x" to "x_02",536"x" to "x_03",537"z" to "z_05",538"z" to "z_08"}}539 540do_test shell5-5.1b {541 set out [open shell5.csv w]542 fconfigure $out -translation lf543 puts $out {"COW","cow","CoW","cOw"}544 puts $out {"uuu","lll","ulu","lul"}545 close $out546 forcedelete test.db547 catchcmd test.db {.import -csv shell5.csv t1548.mode line549SELECT * FROM t1;}550} {1 {COW_1: uuu551cow_2: lll552CoW_3: ulu553cOw_4: lul554Columns renamed during .import shell5.csv due to duplicates:555"COW" to "COW_1",556"cow" to "cow_2",557"CoW" to "CoW_3",558"cOw" to "cOw_4"}}559 560#----------------------------------------------------------------------------561# Tests for preserving utf-8 that is not also ASCII.562#563 564do_test_with_ansi_output shell5-6.1 {565 set out [open shell5.csv w]566 fconfigure $out -translation lf567 puts $out {あい,うえお}568 puts $out {1,2}569 close $out570 forcedelete test.db571 catchcmd test.db {.import -csv shell5.csv t1572.mode line --colsep " = "573SELECT * FROM t1;}574} {0 { あい = 1575うえお = 2}}576 577do_test_with_ansi_output shell5-6.2 {578 set out [open shell5.csv w]579 fconfigure $out -translation lf580 puts $out {1,2}581 puts $out {あい,うえお}582 close $out583 forcedelete test.db584 catchcmd test.db {.import -csv shell5.csv t1585.mode line586SELECT * FROM t1;}587} {0 {1: あい5882: うえお}}589 590# 2024-03-11 https://sqlite.org/forum/forumpost/ca014d7358591# Import into a table that contains computed columns.592#593do_test shell5-7.1 {594 set out [open shell5.csv w]595 fconfigure $out -translation lf596 puts $out {aaa|bbb}597 close $out598 forcedelete test.db599 catchcmd ":memory: -list" {CREATE TABLE t1(a TEXT, b TEXT, c AS (a||b));600.import shell5.csv t1601SELECT * FROM t1;}602} {0 aaa|bbb|aaabbb}603 604#-------------------------------------------------------------------------605 606do_test shell5-8.1 {607 608 set out [open shell5.csv w]609 fconfigure $out -translation lf610 puts $out x611 close $out612 613 catchcmd :memory: {.import --csv shell5.csv '""""""""""""""""""""""""""""""""""""""""""""""'}614} {0 {}}615 616finish_test617