test/shell5.test

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