gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/shell5.test (about) 1 # 2010 August 4 2 # 3 # The author disclaims copyright to this source code. In place of 4 # 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 # 12 # The focus of this file is testing the CLI shell tool. 13 # These tests are specific to the .import command. 14 # 15 # $Id: shell5.test,v 1.7 2009/07/17 16:54:48 shaneh Exp $ 16 # 17 18 # Test plan: 19 # 20 # shell5-1.*: Basic tests specific to the ".import" command. 21 # 22 set testdir [file dirname $argv0] 23 source $testdir/tester.tcl 24 set CLI [test_cli_invocation] 25 db close 26 forcedelete test.db test.db-journal test.db-wal 27 28 #---------------------------------------------------------------------------- 29 # Test cases shell5-1.*: Basic handling of the .import and .separator commands. 30 # 31 32 # .import FILE TABLE Import data from FILE into TABLE 33 do_test shell5-1.1.1 { 34 catchcmd "test.db" ".import" 35 } {/1 .ERROR: missing FILE argument.*/} 36 do_test shell5-1.1.2 { 37 catchcmd "test.db" ".import FOO" 38 } {/1 .ERROR: missing TABLE argument.*/} 39 do_test shell5-1.1.3 { 40 # too many arguments 41 catchcmd "test.db" ".import FOO BAR BAD" 42 } {/1 .ERROR: extra argument.*/} 43 44 # .separator STRING Change separator used by output mode and .import 45 do_test shell5-1.2.1 { 46 catchcmd "test.db" ".separator" 47 } {1 {Usage: .separator COL ?ROW?}} 48 do_test shell5-1.2.2 { 49 catchcmd "test.db" ".separator ONE" 50 } {0 {}} 51 do_test shell5-1.2.3 { 52 catchcmd "test.db" ".separator ONE TWO" 53 } {0 {}} 54 do_test shell5-1.2.4 { 55 # too many arguments 56 catchcmd "test.db" ".separator ONE TWO THREE" 57 } {1 {Usage: .separator COL ?ROW?}} 58 59 # column separator should default to "|" 60 do_test shell5-1.3.1.1 { 61 set res [catchcmd "test.db" ".show"] 62 list [regexp {colseparator: \"\|\"} $res] 63 } {1} 64 65 # row separator should default to "\n" 66 do_test shell5-1.3.1.2 { 67 set res [catchcmd "test.db" ".show"] 68 list [regexp {rowseparator: \"\\n\"} $res] 69 } {1} 70 71 # set separator to different value. 72 # check that .show reports new value 73 do_test shell5-1.3.2 { 74 set res [catchcmd "test.db" {.separator , 75 .show}] 76 list [regexp {separator: \",\"} $res] 77 } {1} 78 79 # import file doesn't exist 80 do_test shell5-1.4.1 { 81 forcedelete FOO 82 set res [catchcmd "test.db" {CREATE TABLE t1(a, b); 83 .import FOO t1}] 84 } {1 {Error: cannot open "FOO"}} 85 86 # empty import file 87 do_test shell5-1.4.2 { 88 forcedelete shell5.csv 89 set in [open shell5.csv w] 90 close $in 91 set res [catchcmd ":memory:" {ATTACH 'test.db' AS test; 92 .import -schema test shell5.csv t1 93 SELECT COUNT(*) FROM test.t1;}] 94 } {0 0} 95 96 # import file with 1 row, 1 column (expecting 2 cols) 97 do_test shell5-1.4.3 { 98 set in [open shell5.csv w] 99 puts $in "1" 100 close $in 101 set res [catchcmd ":memory:" {ATTACH 'test.db' AS test; 102 .import -schema test shell5.csv t1}] 103 } {1 {shell5.csv:1: expected 2 columns but found 1 - filling the rest with NULL}} 104 105 # import file with 1 row, 3 columns (expecting 2 cols) 106 do_test shell5-1.4.4 { 107 set in [open shell5.csv w] 108 puts $in "1|2|3" 109 close $in 110 set res [catchcmd ":memory:" {ATTACH 'test.db' AS test; 111 .import --schema test shell5.csv t1}] 112 } {1 {shell5.csv:1: expected 2 columns but found 3 - extras ignored}} 113 114 # import file with 1 row, 2 columns 115 do_test shell5-1.4.5 { 116 set in [open shell5.csv w] 117 puts $in "1|2" 118 close $in 119 set res [catchcmd "test.db" {DELETE FROM t1; 120 .import shell5.csv t1 121 SELECT COUNT(*) FROM t1;}] 122 } {0 1} 123 124 # import file with 2 rows, 2 columns 125 # note we end up with 3 rows because of the 1 row 126 # imported above. 127 do_test shell5-1.4.6 { 128 set in [open shell5.csv w] 129 puts $in "2|3" 130 puts $in "3|4" 131 close $in 132 set res [catchcmd ":memory:" {ATTACH 'test.db' AS test; 133 .import -schema test shell5.csv t1 134 SELECT COUNT(*) FROM test.t1;}] 135 } {0 3} 136 137 # import file with 1 row, 2 columns, using a comma 138 do_test shell5-1.4.7 { 139 set in [open shell5.csv w] 140 puts $in "4,5" 141 close $in 142 set res [catchcmd ":memory:" {ATTACH 'test.db' AS test; 143 .separator , 144 .import --schema test shell5.csv t1 145 SELECT COUNT(*) FROM test.t1;}] 146 } {0 4} 147 148 # import file with 1 row, 2 columns, text data 149 do_test shell5-1.4.8.1 { 150 set in [open shell5.csv w] 151 puts $in "5|Now is the time for all good men to come to the aid of their country." 152 close $in 153 set res [catchcmd "test.db" {.import shell5.csv t1 154 SELECT COUNT(*) FROM t1;}] 155 } {0 5} 156 157 do_test shell5-1.4.8.2 { 158 catchcmd "test.db" {SELECT b FROM t1 WHERE a='5';} 159 } {0 {Now is the time for all good men to come to the aid of their country.}} 160 161 # import file with 1 row, 2 columns, quoted text data 162 # note that currently sqlite doesn't support quoted fields, and 163 # imports the entire field, quotes and all. 164 do_test shell5-1.4.9.1 { 165 set in [open shell5.csv w] 166 puts $in "6|'Now is the time for all good men to come to the aid of their country.'" 167 close $in 168 set res [catchcmd "test.db" {.import shell5.csv t1 169 SELECT COUNT(*) FROM t1;}] 170 } {0 6} 171 172 do_test shell5-1.4.9.2 { 173 catchcmd "test.db" {SELECT b FROM t1 WHERE a='6';} 174 } {0 {'Now is the time for all good men to come to the aid of their country.'}} 175 176 # import file with 1 row, 2 columns, quoted text data 177 do_test shell5-1.4.10.1 { 178 set in [open shell5.csv w] 179 puts $in "7|\"Now is the time for all good men to come to the aid of their country.\"" 180 close $in 181 set res [catchcmd "test.db" {.import shell5.csv t1 182 SELECT COUNT(*) FROM t1;}] 183 } {0 7} 184 185 do_test shell5-1.4.10.2 { 186 catchcmd "test.db" {SELECT b FROM t1 WHERE a='7';} 187 } {0 {Now is the time for all good men to come to the aid of their country.}} 188 189 # import file with 2 rows, 2 columns and an initial BOM 190 # 191 do_test shell5-1.4.11 { 192 set in [open shell5.csv wb] 193 puts -nonewline $in "\xef\xbb\xbf" 194 puts $in "2|3" 195 puts $in "4|5" 196 close $in 197 set res [catchcmd "test.db" {CREATE TABLE t2(x INT, y INT); 198 .import shell5.csv t2 199 .mode quote 200 .header on 201 SELECT * FROM t2;}] 202 string map {\n | \n\r |} $res 203 } {0 {'x','y'|2,3|4,5}} 204 205 # import file with 2 rows, 2 columns or text with an initial BOM 206 # 207 do_test shell5-1.4.12 { 208 set in [open shell5.csv wb] 209 puts $in "\xef\xbb\xbf\"two\"|3" 210 puts $in "4|5" 211 close $in 212 set res [catchcmd "test.db" {DELETE FROM t2; 213 .import shell5.csv t2 214 .mode quote 215 .header on 216 SELECT * FROM t2;}] 217 string map {\n | \n\r |} $res 218 } {0 {'x','y'|'two',3|4,5}} 219 220 # check importing very long field 221 do_test shell5-1.5.1 { 222 set str [string repeat X 999] 223 set in [open shell5.csv w] 224 puts $in "8|$str" 225 close $in 226 set res [catchcmd "test.db" {.import shell5.csv t1 227 SELECT length(b) FROM t1 WHERE a='8';}] 228 } {0 999} 229 230 # try importing into a table with a large number of columns. 231 # This is limited by SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999. 232 set cols 999 233 do_test shell5-1.6.1 { 234 set data {} 235 for {set i 1} {$i<$cols} {incr i} { 236 append data "c$i|" 237 } 238 append data "c$cols\n"; 239 for {set i 1} {$i<$cols} {incr i} { 240 append data "$i|" 241 } 242 append data "$cols" 243 set in [open shell5.csv w] 244 puts $in $data 245 close $in 246 set res [catchcmd "test.db" {DROP TABLE IF EXISTS t2; 247 .import shell5.csv t2 248 SELECT COUNT(*) FROM t2;}] 249 } {0 1} 250 251 # try importing a large number of rows 252 set rows 9999 253 do_test shell5-1.7.1 { 254 set in [open shell5.csv w] 255 puts $in a 256 for {set i 1} {$i<=$rows} {incr i} { 257 puts $in $i 258 } 259 close $in 260 set res [catchcmd "test.db" {.mode csv 261 .import shell5.csv t3 262 SELECT COUNT(*) FROM t3;}] 263 } [list 0 $rows] 264 265 # Import from a pipe. (Unix only, as it requires "awk") 266 if {$tcl_platform(platform)=="unix"} { 267 do_test shell5-1.8 { 268 forcedelete test.db 269 catchcmd test.db {.mode csv 270 .import "|awk 'END{print \"x,y\";for(i=1;i<=5;i++){print i \",this is \" i}}'" t1 271 SELECT * FROM t1;} 272 } {0 {1,"this is 1" 273 2,"this is 2" 274 3,"this is 3" 275 4,"this is 4" 276 5,"this is 5"}} 277 } 278 279 # Import columns containing quoted strings 280 do_test shell5-1.9 { 281 set out [open shell5.csv w] 282 fconfigure $out -translation lf 283 puts $out {1,"",11} 284 puts $out {2,"x",22} 285 puts $out {3,"""",33} 286 puts $out {4,"hello",44} 287 puts $out "5,55,\"\"\r" 288 puts $out {6,66,"x"} 289 puts $out {7,77,""""} 290 puts $out {8,88,"hello"} 291 puts $out {"",9,99} 292 puts $out {"x",10,110} 293 puts $out {"""",11,121} 294 puts $out {"hello",12,132} 295 close $out 296 forcedelete test.db 297 catchcmd test.db {.mode csv 298 CREATE TABLE t1(a,b,c); 299 .import shell5.csv t1 300 } 301 sqlite3 db test.db 302 db eval {SELECT *, '|' FROM t1 ORDER BY rowid} 303 } {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 |} 304 db close 305 306 # Import columns containing quoted strings 307 do_test shell5-1.10 { 308 set out [open shell5.csv w] 309 fconfigure $out -translation lf 310 puts $out {column1,column2,column3,column4} 311 puts $out "field1,field2,\"x3 \"\"\r\ndata\"\" 3\",field4" 312 puts $out "x1,x2,\"x3 \"\"\ndata\"\" 3\",x4" 313 close $out 314 forcedelete test.db 315 catchcmd test.db {.mode csv 316 CREATE TABLE t1(a,b,c,d); 317 .import shell5.csv t1 318 } 319 sqlite3 db test.db 320 db eval {SELECT hex(c) FROM t1 ORDER BY rowid} 321 } {636F6C756D6E33 783320220D0A64617461222033 783320220A64617461222033} 322 323 # Blank last column with \r\n line endings. 324 do_test shell5-1.11 { 325 set out [open shell5.csv w] 326 fconfigure $out -translation binary 327 puts $out "column1,column2,column3\r" 328 puts $out "a,b, \r" 329 puts $out "x,y,\r" 330 puts $out "p,q,r\r" 331 close $out 332 catch {db close} 333 forcedelete test.db 334 catchcmd test.db {.mode csv 335 .import shell5.csv t1 336 } 337 sqlite3 db test.db 338 db eval {SELECT *, '|' FROM t1} 339 } {a b { } | x y {} | p q r |} 340 db close 341 342 #---------------------------------------------------------------------------- 343 # 344 reset_db 345 sqlite3 db test.db 346 do_test shell5-2.1 { 347 set fd [open shell5.csv w] 348 puts $fd ",hello" 349 close $fd 350 catchcmd test.db [string trim { 351 .mode csv 352 CREATE TABLE t1(a, b); 353 .import shell5.csv t1 354 }] 355 db eval { SELECT * FROM t1 } 356 } {{} hello} 357 358 do_test shell5-2.2 { 359 set fd [open shell5.csv w] 360 puts $fd {"",hello} 361 close $fd 362 catchcmd test.db [string trim { 363 .mode csv 364 CREATE TABLE t2(a, b); 365 .import shell5.csv t2 366 }] 367 db eval { SELECT * FROM t2 } 368 } {{} hello} 369 370 do_test shell5-2.3 { 371 set fd [open shell5.csv w] 372 puts $fd {"x""y",hello} 373 close $fd 374 catchcmd test.db [string trim { 375 .mode csv 376 CREATE TABLE t3(a, b); 377 .import shell5.csv t3 378 }] 379 db eval { SELECT * FROM t3 } 380 } {x\"y hello} 381 382 do_test shell5-2.4 { 383 set fd [open shell5.csv w] 384 puts $fd {"xy""",hello} 385 close $fd 386 catchcmd test.db [string trim { 387 .mode csv 388 CREATE TABLE t4(a, b); 389 .import shell5.csv t4 390 }] 391 db eval { SELECT * FROM t4 } 392 } {xy\" hello} 393 394 do_test shell5-2.5 { 395 set fd [open shell5.csv w] 396 puts $fd {"one","2"} 397 puts $fd {} 398 close $fd 399 catchcmd test.db [string trim { 400 .mode csv 401 CREATE TABLE t4(a, b); 402 .import shell5.csv t4 403 }] 404 db eval { SELECT * FROM t4 } 405 } {xy\" hello one 2 {} {}} 406 407 #---------------------------------------------------------------------------- 408 # Tests for the shell "ascii" import/export mode. 409 # 410 do_test shell5-3.1 { 411 set fd [open shell5.csv w] 412 fconfigure $fd -encoding binary -translation binary 413 puts -nonewline $fd "\"test 1\"\x1F,test 2\r\n\x1E" 414 puts -nonewline $fd "test 3\x1Ftest 4\n" 415 close $fd 416 catchcmd test.db { 417 .mode ascii 418 CREATE TABLE t5(a, b); 419 .import shell5.csv t5 420 } 421 db eval { SELECT * FROM t5 } 422 } "\{\"test 1\"} \{,test 2\r\n\} \{test 3\} \{test 4\n\}" 423 424 do_test shell5-3.2 { 425 set x [catchcmd test.db { 426 .mode ascii 427 SELECT * FROM t5; 428 }] 429 # Handle platform end-of-line differences 430 regsub -all {[\n\r]?\n} $x <EOL> x 431 set x 432 } "0 \{\"test 1\"\x1F,test 2<EOL>\x1Etest 3\x1Ftest 4<EOL>\x1E\}" 433 434 do_test shell5-4.1 { 435 forcedelete shell5.csv 436 set fd [open shell5.csv w] 437 puts $fd "1,2,3" 438 puts $fd "4,5" 439 puts $fd "6,7,8" 440 close $fd 441 catchcmd test.db [string trim { 442 .mode csv 443 CREATE TABLE t6(a, b, c); 444 .import shell5.csv t6 445 }] 446 db eval { SELECT * FROM t6 ORDER BY a } 447 } {1 2 3 4 5 {} 6 7 8} 448 449 do_test shell5-4.2 { 450 forcedelete shell5.csv 451 set fd [open shell5.csv w] 452 puts $fd "1,2,3" 453 puts $fd "4,5" 454 puts $fd "6,7,8,9" 455 close $fd 456 catchcmd test.db [string trim { 457 .mode csv 458 CREATE TABLE t7(a, b, c); 459 .import shell5.csv t7 460 }] 461 db eval { SELECT * FROM t7 ORDER BY a } 462 } {1 2 3 4 5 {} 6 7 8} 463 464 do_test shell5-4.3 { 465 forcedelete shell5.csv 466 set fd [open shell5.csv w] 467 puts $fd ",," 468 puts $fd "1,2,3" 469 close $fd 470 catchcmd test.db [string trim { 471 .mode csv 472 CREATE TABLE t8(a, b, c); 473 .import -skip 1 shell5.csv t8 474 .nullvalue # 475 }] 476 db eval { SELECT * FROM t8 } 477 } {1 2 3} 478 479 do_test shell5-4.4 { 480 forcedelete shell5.csv 481 set fd [open shell5.csv w] 482 puts $fd "1,2,3" 483 close $fd 484 catchcmd test.db [string trim { 485 .mode csv 486 CREATE TEMP TABLE t8(a, b, c); 487 .import shell5.csv t8 488 .nullvalue # 489 SELECT * FROM temp.t8 490 }] 491 } {0 1,2,3} 492 493 #---------------------------------------------------------------------------- 494 # Tests for the shell automatic column rename. 495 # 496 db close 497 498 # Import columns containing duplicates 499 do_test shell5-5.1 { 500 set out [open shell5.csv w] 501 fconfigure $out -translation lf 502 puts $out {"","x","x","y","z","z_0","z_5","z"} 503 puts $out {0,"x2","x3","y4","z5","z6","z7","z8"} 504 close $out 505 forcedelete test.db 506 catchcmd test.db {.import -csv shell5.csv t1 507 .mode line 508 SELECT * FROM t1;} 509 } {1 { ? = 0 510 x_02 = x2 511 x_03 = x3 512 y = y4 513 z_05 = z5 514 z_0 = z6 515 z_5 = z7 516 z_08 = z8 517 Columns renamed during .import shell5.csv due to duplicates: 518 "x" to "x_02", 519 "x" to "x_03", 520 "z" to "z_05", 521 "z" to "z_08"}} 522 523 do_test shell5-5.1 { 524 set out [open shell5.csv w] 525 fconfigure $out -translation lf 526 puts $out {"COW","cow","CoW","cOw"} 527 puts $out {"uuu","lll","ulu","lul"} 528 close $out 529 forcedelete test.db 530 catchcmd test.db {.import -csv shell5.csv t1 531 .mode line 532 SELECT * FROM t1;} 533 } {1 {COW_1 = uuu 534 cow_2 = lll 535 CoW_3 = ulu 536 cOw_4 = lul 537 Columns renamed during .import shell5.csv due to duplicates: 538 "COW" to "COW_1", 539 "cow" to "cow_2", 540 "CoW" to "CoW_3", 541 "cOw" to "cOw_4"}} 542 543 #---------------------------------------------------------------------------- 544 # Tests for preserving utf-8 that is not also ASCII. 545 # 546 547 do_test shell5-6.1 { 548 set out [open shell5.csv w] 549 fconfigure $out -translation lf 550 puts $out {あい,うえお} 551 puts $out {1,2} 552 close $out 553 forcedelete test.db 554 catchcmd test.db {.import -csv shell5.csv t1 555 .mode line 556 SELECT * FROM t1;} 557 } {0 { あい = 1 558 うえお = 2}} 559 560 do_test shell5-6.2 { 561 set out [open shell5.csv w] 562 fconfigure $out -translation lf 563 puts $out {1,2} 564 puts $out {あい,うえお} 565 close $out 566 forcedelete test.db 567 catchcmd test.db {.import -csv shell5.csv t1 568 .mode line 569 SELECT * FROM t1;} 570 } {0 { 1 = あい 571 2 = うえお}} 572 573 finish_test