3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
13 # The focus of this file is testing the CLI shell tool.
14 # These tests are specific to the .import command.
16 # $Id: shell5.test,v 1.7 2009/07/17 16:54:48 shaneh Exp $
21 # shell5-1.*: Basic tests specific to the ".import" command.
23 set testdir [file dirname $argv0]
24 source $testdir/tester.tcl
25 set CLI [test_cli_invocation]
27 forcedelete test.db test.db-journal test.db-wal
29 #----------------------------------------------------------------------------
30 # Test cases shell5-1.*: Basic handling of the .import and .separator commands.
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 {
42 catchcmd "test.db" ".import FOO BAR BAD"
43 } {/1 .ERROR: extra argument.*/}
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"
52 do_test shell5-1.2.3 {
53 catchcmd "test.db" ".separator ONE TWO"
55 do_test shell5-1.2.4 {
57 catchcmd "test.db" ".separator ONE TWO THREE"
58 } {1 {Usage: .separator COL ?ROW?}}
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]
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]
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 ,
77 list [regexp {separator: \",\"} $res]
80 # import file doesn't exist
81 do_test shell5-1.4.1 {
83 set res [catchcmd "test.db" {CREATE TABLE t1(a, b);
85 } {1 {Error: cannot open "FOO"}}
87 # the remainder of these test cases require virtual tables.
90 puts "Skipping subsequent tests due to SQLITE_OMIT_VIRTUALTABLE"
96 do_test shell5-1.4.2 {
97 forcedelete shell5.csv
98 set in [open shell5.csv w]
100 set res [catchcmd ":memory:" {ATTACH 'test.db' AS test;
101 .import -schema test shell5.csv t1
102 SELECT COUNT(*) FROM test.t1;}]
105 # import file with 1 row, 1 column (expecting 2 cols)
106 do_test shell5-1.4.3 {
107 set in [open shell5.csv w]
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 1 - filling the rest with NULL}}
114 # import file with 1 row, 3 columns (expecting 2 cols)
115 do_test shell5-1.4.4 {
116 set in [open shell5.csv w]
119 set res [catchcmd ":memory:" {ATTACH 'test.db' AS test;
120 .import --schema test shell5.csv t1}]
121 } {1 {shell5.csv:1: expected 2 columns but found 3 - extras ignored}}
123 # import file with 1 row, 2 columns
124 do_test shell5-1.4.5 {
125 set in [open shell5.csv w]
128 set res [catchcmd "test.db" {DELETE FROM t1;
129 .import shell5.csv t1
130 SELECT COUNT(*) FROM t1;}]
133 # import file with 2 rows, 2 columns
134 # note we end up with 3 rows because of the 1 row
136 do_test shell5-1.4.6 {
137 set in [open shell5.csv w]
141 set res [catchcmd ":memory:" {ATTACH 'test.db' AS test;
142 .import -schema test shell5.csv t1
143 SELECT COUNT(*) FROM test.t1;}]
146 # import file with 1 row, 2 columns, using a comma
147 do_test shell5-1.4.7 {
148 set in [open shell5.csv w]
151 set res [catchcmd ":memory:" {ATTACH 'test.db' AS test;
153 .import --schema test shell5.csv t1
154 SELECT COUNT(*) FROM test.t1;}]
157 # import file with 1 row, 2 columns, text data
158 do_test shell5-1.4.8.1 {
159 set in [open shell5.csv w]
160 puts $in "5|Now is the time for all good men to come to the aid of their country."
162 set res [catchcmd "test.db" {.import shell5.csv t1
163 SELECT COUNT(*) FROM t1;}]
166 do_test shell5-1.4.8.2 {
167 catchcmd "test.db" {SELECT b FROM t1 WHERE a='5';}
168 } {0 {Now is the time for all good men to come to the aid of their country.}}
170 # import file with 1 row, 2 columns, quoted text data
171 # note that currently sqlite doesn't support quoted fields, and
172 # imports the entire field, quotes and all.
173 do_test shell5-1.4.9.1 {
174 set in [open shell5.csv w]
175 puts $in "6|'Now is the time for all good men to come to the aid of their country.'"
177 set res [catchcmd "test.db" {.import shell5.csv t1
178 SELECT COUNT(*) FROM t1;}]
181 do_test shell5-1.4.9.2 {
182 catchcmd "test.db" {SELECT b FROM t1 WHERE a='6';}
183 } {0 {'Now is the time for all good men to come to the aid of their country.'}}
185 # import file with 1 row, 2 columns, quoted text data
186 do_test shell5-1.4.10.1 {
187 set in [open shell5.csv w]
188 puts $in "7|\"Now is the time for all good men to come to the aid of their country.\""
190 set res [catchcmd "test.db" {.import shell5.csv t1
191 SELECT COUNT(*) FROM t1;}]
194 do_test shell5-1.4.10.2 {
195 catchcmd "test.db" {SELECT b FROM t1 WHERE a='7';}
196 } {0 {Now is the time for all good men to come to the aid of their country.}}
198 # import file with 2 rows, 2 columns and an initial BOM
200 do_test shell5-1.4.11 {
201 set in [open shell5.csv wb]
202 puts -nonewline $in "\xef\xbb\xbf"
206 set res [catchcmd "test.db" {CREATE TABLE t2(x INT, y INT);
207 .import shell5.csv t2
211 string map {\n | \n\r |} $res
212 } {0 {'x','y'|2,3|4,5}}
214 # import file with 2 rows, 2 columns or text with an initial BOM
216 do_test shell5-1.4.12 {
217 set in [open shell5.csv wb]
218 puts $in "\xef\xbb\xbf\"two\"|3"
221 set res [catchcmd "test.db" {DELETE FROM t2;
222 .import shell5.csv t2
226 string map {\n | \n\r |} $res
227 } {0 {'x','y'|'two',3|4,5}}
229 # check importing very long field
230 do_test shell5-1.5.1 {
231 set str [string repeat X 999]
232 set in [open shell5.csv w]
235 set res [catchcmd "test.db" {.import shell5.csv t1
236 SELECT length(b) FROM t1 WHERE a='8';}]
239 # try importing into a table with a large number of columns.
240 # This is limited by SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999.
242 do_test shell5-1.6.1 {
244 for {set i 1} {$i<$cols} {incr i} {
247 append data "c$cols\n";
248 for {set i 1} {$i<$cols} {incr i} {
252 set in [open shell5.csv w]
255 set res [catchcmd "test.db" {DROP TABLE IF EXISTS t2;
256 .import shell5.csv t2
257 SELECT COUNT(*) FROM t2;}]
260 # try importing a large number of rows
262 do_test shell5-1.7.1 {
263 set in [open shell5.csv w]
265 for {set i 1} {$i<=$rows} {incr i} {
269 set res [catchcmd "test.db" {.mode csv
270 .import shell5.csv t3
271 SELECT COUNT(*) FROM t3;}]
274 # Import from a pipe. (Unix only, as it requires "awk")
275 if {$tcl_platform(platform)=="unix"} {
278 catchcmd test.db {.mode csv
279 .import "|awk 'END{print \"x,y\";for(i=1;i<=5;i++){print i \",this is \" i}}'" t1
288 # Import columns containing quoted strings
290 set out [open shell5.csv w]
291 fconfigure $out -translation lf
294 puts $out {3,"""",33}
295 puts $out {4,"hello",44}
296 puts $out "5,55,\"\"\r"
298 puts $out {7,77,""""}
299 puts $out {8,88,"hello"}
301 puts $out {"x",10,110}
302 puts $out {"""",11,121}
303 puts $out {"hello",12,132}
306 catchcmd test.db {.mode csv
307 CREATE TABLE t1(a,b,c);
308 .import shell5.csv t1
311 db eval {SELECT *, '|' FROM t1 ORDER BY rowid}
312 } {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 |}
315 # Import columns containing quoted strings
316 do_test shell5-1.10 {
317 set out [open shell5.csv w]
318 fconfigure $out -translation lf
319 puts $out {column1,column2,column3,column4}
320 puts $out "field1,field2,\"x3 \"\"\r\ndata\"\" 3\",field4"
321 puts $out "x1,x2,\"x3 \"\"\ndata\"\" 3\",x4"
324 catchcmd test.db {.mode csv
325 CREATE TABLE t1(a,b,c,d);
326 .import shell5.csv t1
329 db eval {SELECT hex(c) FROM t1 ORDER BY rowid}
330 } {636F6C756D6E33 783320220D0A64617461222033 783320220A64617461222033}
332 # Blank last column with \r\n line endings.
333 do_test shell5-1.11 {
334 set out [open shell5.csv w]
335 fconfigure $out -translation binary
336 puts $out "column1,column2,column3\r"
343 catchcmd test.db {.mode csv
344 .import shell5.csv t1
347 db eval {SELECT *, '|' FROM t1}
348 } {a b { } | x y {} | p q r |}
351 #----------------------------------------------------------------------------
356 set fd [open shell5.csv w]
359 catchcmd test.db [string trim {
361 CREATE TABLE t1(a, b);
362 .import shell5.csv t1
364 db eval { SELECT * FROM t1 }
368 set fd [open shell5.csv w]
371 catchcmd test.db [string trim {
373 CREATE TABLE t2(a, b);
374 .import shell5.csv t2
376 db eval { SELECT * FROM t2 }
380 set fd [open shell5.csv w]
381 puts $fd {"x""y",hello}
383 catchcmd test.db [string trim {
385 CREATE TABLE t3(a, b);
386 .import shell5.csv t3
388 db eval { SELECT * FROM t3 }
392 set fd [open shell5.csv w]
393 puts $fd {"xy""",hello}
395 catchcmd test.db [string trim {
397 CREATE TABLE t4(a, b);
398 .import shell5.csv t4
400 db eval { SELECT * FROM t4 }
404 set fd [open shell5.csv w]
408 catchcmd test.db [string trim {
410 CREATE TABLE t4(a, b);
411 .import shell5.csv t4
413 db eval { SELECT * FROM t4 }
414 } {xy\" hello one 2 {} {}}
416 #----------------------------------------------------------------------------
417 # Tests for the shell "ascii" import/export mode.
420 set fd [open shell5.csv w]
421 fconfigure $fd -translation binary
422 puts -nonewline $fd "\"test 1\"\x1F,test 2\r\n\x1E"
423 puts -nonewline $fd "test 3\x1Ftest 4\n"
427 CREATE TABLE t5(a, b);
428 .import shell5.csv t5
430 db eval { SELECT * FROM t5 }
431 } "\{\"test 1\"} \{,test 2\r\n\} \{test 3\} \{test 4\n\}"
434 set x [catchcmd test.db {
438 # Handle platform end-of-line differences
439 regsub -all {[\n\r]?\n} $x <EOL> x
441 } "0 \{\"test 1\"\x1F,test 2<EOL>\x1Etest 3\x1Ftest 4<EOL>\x1E\}"
444 forcedelete shell5.csv
445 set fd [open shell5.csv w]
450 catchcmd test.db [string trim {
452 CREATE TABLE t6(a, b, c);
453 .import shell5.csv t6
455 db eval { SELECT * FROM t6 ORDER BY a }
456 } {1 2 3 4 5 {} 6 7 8}
459 forcedelete shell5.csv
460 set fd [open shell5.csv w]
465 catchcmd test.db [string trim {
467 CREATE TABLE t7(a, b, c);
468 .import shell5.csv t7
470 db eval { SELECT * FROM t7 ORDER BY a }
471 } {1 2 3 4 5 {} 6 7 8}
474 forcedelete shell5.csv
475 set fd [open shell5.csv w]
479 catchcmd test.db [string trim {
481 CREATE TABLE t8(a, b, c);
482 .import -skip 1 shell5.csv t8
485 db eval { SELECT * FROM t8 }
489 forcedelete shell5.csv
490 set fd [open shell5.csv w]
493 catchcmd test.db [string trim {
495 CREATE TEMP TABLE t8(a, b, c);
496 .import shell5.csv t8
498 SELECT * FROM temp.t8
502 #----------------------------------------------------------------------------
503 # Tests for the shell automatic column rename.
507 # Import columns containing duplicates
509 set out [open shell5.csv w]
510 fconfigure $out -translation lf
511 puts $out {"","x","x","y","z","z_0","z_5","z"}
512 puts $out {0,"x2","x3","y4","z5","z6","z7","z8"}
515 catchcmd test.db {.import -csv shell5.csv t1
526 Columns renamed during .import shell5.csv due to duplicates:
533 set out [open shell5.csv w]
534 fconfigure $out -translation lf
535 puts $out {"COW","cow","CoW","cOw"}
536 puts $out {"uuu","lll","ulu","lul"}
539 catchcmd test.db {.import -csv shell5.csv t1
546 Columns renamed during .import shell5.csv due to duplicates:
552 #----------------------------------------------------------------------------
553 # Tests for preserving utf-8 that is not also ASCII.
557 set out [open shell5.csv w]
558 fconfigure $out -translation lf
563 catchcmd test.db {.import -csv shell5.csv t1
570 set out [open shell5.csv w]
571 fconfigure $out -translation lf
576 catchcmd test.db {.import -csv shell5.csv t1
582 # 2024-03-11 https://sqlite.org/forum/forumpost/ca014d7358
583 # Import into a table that contains computed columns.
586 set out [open shell5.csv w]
587 fconfigure $out -translation lf
591 catchcmd :memory: {CREATE TABLE t1(a TEXT, b TEXT, c AS (a||b));
592 .import shell5.csv t1
596 #-------------------------------------------------------------------------
600 set out [open shell5.csv w]
601 fconfigure $out -translation lf
605 catchcmd :memory: {.import --csv shell5.csv '""""""""""""""""""""""""""""""""""""""""""""""'}