Debian Codegraph @

test/shell5.test

raw
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 .line 1: Missing FILE argument.*/}
37 do_test shell5-1.1.2 {
38 catchcmd "test.db" ".import FOO"
39 } {/1 .line 1: Missing TABLE argument.*/}
40 do_test shell5-1.1.3 {
41 # too many arguments
42 catchcmd "test.db" ".import FOO BAR BAD"
43 } {1 {line 1: .import FOO BAR BAD
44 line 1: ^--- unknown argument}}
45
46 # .separator STRING Change separator used by output mode and .import
47 do_test shell5-1.2.1 {
48 catchcmd "test.db" ".separator"
49 } {1 {Usage: .separator COL ?ROW?}}
50 do_test shell5-1.2.2 {
51 catchcmd "test.db" ".separator ONE"
52 } {0 {}}
53 do_test shell5-1.2.3 {
54 catchcmd "test.db" ".separator ONE TWO"
55 } {0 {}}
56 do_test shell5-1.2.4 {
57 # too many arguments
58 catchcmd "test.db" ".separator ONE TWO THREE"
59 } {1 {Usage: .separator COL ?ROW?}}
60
61 # column separator should default to "|"
62 do_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"
68 do_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 value
75 do_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 exist
82 do_test shell5-1.4.1 {
83 forcedelete FOO
84 set res [catchcmd "test.db" {CREATE TABLE t1(a, b);
85 .import FOO t1}]
86 } {1 {line 2: cannot open "FOO"}}
87
88 # the remainder of these test cases require virtual tables.
89 #
90 ifcapable !vtab {
91 puts "Skipping subsequent tests due to SQLITE_OMIT_VIRTUALTABLE"
92 finish_test
93 return
94 }
95
96 # empty import file
97 do_test shell5-1.4.2 {
98 forcedelete shell5.csv
99 set in [open shell5.csv w]
100 close $in
101 set res [catchcmd ":memory:" {
102 .mode list
103 ATTACH 'test.db' AS test;
104 .import -schema test shell5.csv t1
105 SELECT COUNT(*) FROM test.t1;}]
106 } {0 0}
107
108 # import file with 1 row, 1 column (expecting 2 cols)
109 do_test shell5-1.4.3 {
110 set in [open shell5.csv w]
111 puts $in "1"
112 close $in
113 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)
118 do_test shell5-1.4.4 {
119 set in [open shell5.csv w]
120 puts $in "1|2|3"
121 close $in
122 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 columns
127 do_test shell5-1.4.5 {
128 set in [open shell5.csv w]
129 puts $in "1|2"
130 close $in
131 set res [catchcmd "test.db -list" {DELETE FROM t1;
132 .import shell5.csv t1
133 SELECT COUNT(*) FROM t1;}]
134 } {0 1}
135
136 # import file with 2 rows, 2 columns
137 # note we end up with 3 rows because of the 1 row
138 # imported above.
139 do_test shell5-1.4.6 {
140 set in [open shell5.csv w]
141 puts $in "2|3"
142 puts $in "3|4"
143 close $in
144 set res [catchcmd ":memory:" {
145 .mode list
146 ATTACH 'test.db' AS test;
147 .import -schema test shell5.csv t1
148 SELECT COUNT(*) FROM test.t1;}]
149 } {0 3}
150
151 # import file with 1 row, 2 columns, using a comma
152 do_test shell5-1.4.7 {
153 set in [open shell5.csv w]
154 puts $in "4,5"
155 close $in
156 set res [catchcmd ":memory:" {
157 .mode list
158 ATTACH 'test.db' AS test;
159 .separator ,
160 .import --schema test shell5.csv t1
161 SELECT COUNT(*) FROM test.t1;}]
162 } {0 4}
163
164 # import file with 1 row, 2 columns, text data
165 do_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 $in
169 set res [catchcmd "test.db" {.mode list
170 .import shell5.csv t1
171 SELECT COUNT(*) FROM t1;}]
172 } {0 5}
173
174 do_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 data
179 # note that currently sqlite doesn't support quoted fields, and
180 # imports the entire field, quotes and all.
181 do_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 $in
185 set res [catchcmd "test.db -list" {.import shell5.csv t1
186 SELECT COUNT(*) FROM t1;}]
187 } {0 6}
188
189 do_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 data
194 do_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 $in
198 set res [catchcmd "test.db -list" {.import shell5.csv t1
199 SELECT COUNT(*) FROM t1;}]
200 } {0 7}
201
202 do_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 BOM
207 #
208 do_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 $in
214 set res [catchcmd "test.db -list" {CREATE TABLE t2(x INT, y INT);
215 .import shell5.csv t2
216 .mode quote
217 .header on
218 SELECT * FROM t2;}]
219 string map {\n | \n\r |} $res
220 } {0 {'x','y'|2,3|4,5}}
221
222 # import file with 2 rows, 2 columns or text with an initial BOM
223 #
224 do_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 $in
229 set res [catchcmd "test.db -list" {DELETE FROM t2;
230 .import shell5.csv t2
231 .mode quote
232 .header on
233 SELECT * FROM t2;}]
234 string map {\n | \n\r |} $res
235 } {0 {'x','y'|'two',3|4,5}}
236
237 # check importing very long field
238 do_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 $in
243 set res [catchcmd "test.db -list" {.import shell5.csv t1
244 SELECT 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.
249 set cols 999
250 do_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 $data
262 close $in
263 set res [catchcmd "test.db -list" {DROP TABLE IF EXISTS t2;
264 .import shell5.csv t2
265 SELECT COUNT(*) FROM t2;}]
266 } {0 1}
267
268 # try importing a large number of rows
269 set rows 9999
270 do_test shell5-1.7.1 {
271 set in [open shell5.csv w]
272 puts $in a
273 for {set i 1} {$i<=$rows} {incr i} {
274 puts $in $i
275 }
276 close $in
277 set res [catchcmd "test.db" {.mode csv
278 .import shell5.csv t3
279 .mode quote
280 SELECT COUNT(*) FROM t3;}]
281 } [list 0 $rows]
282
283 # Import from a pipe. (Unix only, as it requires "awk")
284 if {$tcl_platform(platform)=="unix"} {
285 do_test shell5-1.8 {
286 forcedelete test.db
287 catchcmd test.db {.mode csv
288 .import "|awk 'END{print \"x,y\";for(i=1;i<=5;i++){print i \",this is \" i}}'" t1
289 SELECT * FROM t1;}
290 } {0 {1,"this is 1"
291 2,"this is 2"
292 3,"this is 3"
293 4,"this is 4"
294 5,"this is 5"}}
295 }
296
297 # Import columns containing quoted strings
298 do_test shell5-1.9 {
299 set out [open shell5.csv w]
300 fconfigure $out -translation lf
301 puts $out {1,"",11}
302 puts $out {2,"x",22}
303 puts $out {3,"""",33}
304 puts $out {4,"hello",44}
305 puts $out "5,55,\"\"\r"
306 puts $out {6,66,"x"}
307 puts $out {7,77,""""}
308 puts $out {8,88,"hello"}
309 puts $out {"",9,99}
310 puts $out {"x",10,110}
311 puts $out {"""",11,121}
312 puts $out {"hello",12,132}
313 close $out
314 forcedelete test.db
315 catchcmd test.db {.mode csv
316 CREATE TABLE t1(a,b,c);
317 .import shell5.csv t1
318 }
319 sqlite3 db test.db
320 db eval {SELECT *, '|' FROM t1 ORDER BY rowid}
321 } {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 |}
322 db close
323
324 # Import columns containing quoted strings
325 do_test shell5-1.10 {
326 set out [open shell5.csv w]
327 fconfigure $out -translation lf
328 puts $out {column1,column2,column3,column4}
329 puts $out "field1,field2,\"x3 \"\"\r\ndata\"\" 3\",field4"
330 puts $out "x1,x2,\"x3 \"\"\ndata\"\" 3\",x4"
331 close $out
332 forcedelete test.db
333 catchcmd test.db {.mode csv
334 CREATE TABLE t1(a,b,c,d);
335 .import shell5.csv t1
336 }
337 sqlite3 db test.db
338 db eval {SELECT hex(c) FROM t1 ORDER BY rowid}
339 } {636F6C756D6E33 783320220D0A64617461222033 783320220A64617461222033}
340
341 # The --escape option
342 #
343 do_test shell5-1.10.1 {
344 set out [open shell5.csv w]
345 fconfigure $out -translation lf
346 puts $out {column1,column2,column3,column4}
347 puts $out "x1,x2%\"x3,\"x3\\\"data\\\"3\",x4"
348 close $out
349 db close
350 forcedelete test.db
351 catchcmd test.db {
352 CREATE TABLE t1(a,b,c,d);
353 .import --csv --qesc \\ --esc % shell5.csv t1
354 }
355 sqlite3 db test.db
356 db eval {SELECT b, c FROM t1 ORDER BY rowid}
357 } {column2 column3 x2\"x3 x3\"data\"3}
358
359 # Blank last column with \r\n line endings.
360 do_test shell5-1.11 {
361 set out [open shell5.csv w]
362 fconfigure $out -translation binary
363 puts $out "column1,column2,column3\r"
364 puts $out "a,b, \r"
365 puts $out "x,y,\r"
366 puts $out "p,q,r\r"
367 close $out
368 catch {db close}
369 forcedelete test.db
370 catchcmd test.db {.mode csv
371 .import shell5.csv t1
372 }
373 sqlite3 db test.db
374 db eval {SELECT *, '|' FROM t1}
375 } {a b { } | x y {} | p q r |}
376 db close
377
378 #----------------------------------------------------------------------------
379 #
380 reset_db
381 sqlite3 db test.db
382 do_test shell5-2.1 {
383 set fd [open shell5.csv w]
384 puts $fd ",hello"
385 close $fd
386 catchcmd test.db [string trim {
387 .mode csv
388 CREATE TABLE t1(a, b);
389 .import shell5.csv t1
390 }]
391 db eval { SELECT * FROM t1 }
392 } {{} hello}
393
394 do_test shell5-2.2 {
395 set fd [open shell5.csv w]
396 puts $fd {"",hello}
397 close $fd
398 catchcmd test.db [string trim {
399 .mode csv
400 CREATE TABLE t2(a, b);
401 .import shell5.csv t2
402 }]
403 db eval { SELECT * FROM t2 }
404 } {{} hello}
405
406 do_test shell5-2.3 {
407 set fd [open shell5.csv w]
408 puts $fd {"x""y",hello}
409 close $fd
410 catchcmd test.db [string trim {
411 .mode csv
412 CREATE TABLE t3(a, b);
413 .import shell5.csv t3
414 }]
415 db eval { SELECT * FROM t3 }
416 } {x\"y hello}
417
418 do_test shell5-2.4 {
419 set fd [open shell5.csv w]
420 puts $fd {"xy""",hello}
421 close $fd
422 catchcmd test.db [string trim {
423 .mode csv
424 CREATE TABLE t4(a, b);
425 .import shell5.csv t4
426 }]
427 db eval { SELECT * FROM t4 }
428 } {xy\" hello}
429
430 do_test shell5-2.5 {
431 set fd [open shell5.csv w]
432 puts $fd {"one","2"}
433 puts $fd {}
434 close $fd
435 catchcmd test.db [string trim {
436 .mode csv
437 CREATE TABLE t4(a, b);
438 .import shell5.csv t4
439 }]
440 db eval { SELECT * FROM t4 }
441 } {xy\" hello one 2 {} {}}
442
443 #----------------------------------------------------------------------------
444 # Tests for the shell "ascii" import/export mode.
445 #
446 do_test shell5-3.1 {
447 set fd [open shell5.csv w]
448 fconfigure $fd -translation binary
449 puts -nonewline $fd "\"test 1\"\x1F,test 2\r\n\x1E"
450 puts -nonewline $fd "test 3\x1Ftest 4\n"
451 close $fd
452 catchcmd test.db {
453 .mode ascii
454 CREATE TABLE t5(a, b);
455 .import shell5.csv t5
456 }
457 db eval { SELECT * FROM t5 }
458 } "\{\"test 1\"} \{,test 2\r\n\} \{test 3\} \{test 4\n\}"
459
460 do_test shell5-3.2 {
461 set x [catchcmd test.db {
462 .mode ascii
463 SELECT * FROM t5;
464 }]
465 # Handle platform end-of-line differences
466 regsub -all {[\n\r]?\n} $x <EOL> x
467 set x
468 } "0 \{\"test 1\"\x1F,test 2<EOL>\x1Etest 3\x1Ftest 4<EOL>\x1E\}"
469
470 do_test shell5-4.1 {
471 forcedelete shell5.csv
472 set fd [open shell5.csv w]
473 puts $fd "1,2,3"
474 puts $fd "4,5"
475 puts $fd "6,7,8"
476 close $fd
477 catchcmd test.db [string trim {
478 .mode csv
479 CREATE TABLE t6(a, b, c);
480 .import shell5.csv t6
481 }]
482 db eval { SELECT * FROM t6 ORDER BY a }
483 } {1 2 3 4 5 {} 6 7 8}
484
485 do_test shell5-4.2 {
486 forcedelete shell5.csv
487 set fd [open shell5.csv w]
488 puts $fd "1,2,3"
489 puts $fd "4,5"
490 puts $fd "6,7,8,9"
491 close $fd
492 catchcmd test.db [string trim {
493 .mode csv
494 CREATE TABLE t7(a, b, c);
495 .import shell5.csv t7
496 }]
497 db eval { SELECT * FROM t7 ORDER BY a }
498 } {1 2 3 4 5 {} 6 7 8}
499
500 do_test shell5-4.3 {
501 forcedelete shell5.csv
502 set fd [open shell5.csv w]
503 puts $fd ",,"
504 puts $fd "1,2,3"
505 close $fd
506 catchcmd test.db [string trim {
507 .mode csv
508 CREATE TABLE t8(a, b, c);
509 .import -skip 1 shell5.csv t8
510 .nullvalue #
511 }]
512 db eval { SELECT * FROM t8 }
513 } {1 2 3}
514
515 do_test shell5-4.4 {
516 forcedelete shell5.csv
517 set fd [open shell5.csv w]
518 puts $fd "1,2,3"
519 close $fd
520 catchcmd test.db [string trim {
521 .mode csv
522 CREATE TEMP TABLE t8(a, b, c);
523 .import shell5.csv t8
524 .nullvalue #
525 .mode quote
526 SELECT * FROM temp.t8
527 }]
528 } {0 '1','2','3'}
529
530 #----------------------------------------------------------------------------
531 # Tests for the shell automatic column rename.
532 #
533 db close
534
535 # Import columns containing duplicates
536 do_test shell5-5.1 {
537 set out [open shell5.csv w]
538 fconfigure $out -translation lf
539 puts $out {"","x","x","y","z","z_0","z_5","z"}
540 puts $out {0,"x2","x3","y4","z5","z6","z7","z8"}
541 close $out
542 forcedelete test.db
543 catchcmd test.db {.import -csv shell5.csv t1
544 .mode line --colsep ' = '
545 SELECT * FROM t1;}
546 } {1 { ? = 0
547 x_02 = x2
548 x_03 = x3
549 y = y4
550 z_05 = z5
551 z_0 = z6
552 z_5 = z7
553 z_08 = z8
554 Columns renamed during .import shell5.csv due to duplicates:
555 "x" to "x_02",
556 "x" to "x_03",
557 "z" to "z_05",
558 "z" to "z_08"}}
559
560 do_test shell5-5.1b {
561 set out [open shell5.csv w]
562 fconfigure $out -translation lf
563 puts $out {"COW","cow","CoW","cOw"}
564 puts $out {"uuu","lll","ulu","lul"}
565 close $out
566 forcedelete test.db
567 catchcmd test.db {.import -csv shell5.csv t1
568 .mode line
569 SELECT * FROM t1;}
570 } {1 {COW_1: uuu
571 cow_2: lll
572 CoW_3: ulu
573 cOw_4: lul
574 Columns renamed during .import shell5.csv due to duplicates:
575 "COW" to "COW_1",
576 "cow" to "cow_2",
577 "CoW" to "CoW_3",
578 "cOw" to "cOw_4"}}
579
580 #----------------------------------------------------------------------------
581 # Tests for preserving utf-8 that is not also ASCII.
582 #
583
584 do_test_with_ansi_output shell5-6.1 {
585 set out [open shell5.csv w]
586 fconfigure $out -translation lf
587 puts $out {あい,うえお}
588 puts $out {1,2}
589 close $out
590 forcedelete test.db
591 catchcmd test.db {.import -csv shell5.csv t1
592 .mode line --colsep " = "
593 SELECT * FROM t1;}
594 } {0 { あい = 1
595 うえお = 2}}
596
597 do_test_with_ansi_output shell5-6.2 {
598 set out [open shell5.csv w]
599 fconfigure $out -translation lf
600 puts $out {1,2}
601 puts $out {あい,うえお}
602 close $out
603 forcedelete test.db
604 catchcmd test.db {.import -csv shell5.csv t1
605 .mode line
606 SELECT * FROM t1;}
607 } {0 {1: あい
608 2: うえお}}
609
610 # 2024-03-11 https://sqlite.org/forum/forumpost/ca014d7358
611 # Import into a table that contains computed columns.
612 #
613 do_test shell5-7.1 {
614 set out [open shell5.csv w]
615 fconfigure $out -translation lf
616 puts $out {aaa|bbb}
617 close $out
618 forcedelete test.db
619 catchcmd ":memory: -list" {CREATE TABLE t1(a TEXT, b TEXT, c AS (a||b));
620 .import shell5.csv t1
621 SELECT * FROM t1;}
622 } {0 aaa|bbb|aaabbb}
623
624 #-------------------------------------------------------------------------
625
626 do_test shell5-8.1 {
627
628 set out [open shell5.csv w]
629 fconfigure $out -translation lf
630 puts $out x
631 close $out
632
633 catchcmd :memory: {.import --csv shell5.csv '""""""""""""""""""""""""""""""""""""""""""""""'}
634 } {0 {}}
635
636
637 # 2025-12-29 https://sqlite.org/forum/forumpost/6c1c0e213d
638 # .import honor .bail
639 #
640 do_test shell5-9.1 {
641 catchcmd ":memory:" {
642 CREATE TABLE t1(a,b,c INT CHECK(c<>5));
643 .bail on
644 .import -csv <<END t1
645 1,2,3
646 "a","b","c"
647 3,4,5
648 "q","r","s"
649 END
650 SELECT * FROM t1;}
651 } {1 {<stdin>:7: INSERT failed: CHECK constraint failed: c<>5}}
652 do_test shell5-9.2 {
653 catchcmd ":memory:" {
654 CREATE TABLE t1(a,b,c INT CHECK(c<>5));
655 .bail off
656 .import -csv <<END t1
657 1,2,3
658 "a","b","c"
659 3,4,5
660 "q","r","s"
661 END
662 SELECT * FROM t1;}
663 } {1 {1|2|3
664 a|b|c
665 q|r|s
666 <stdin>:7: INSERT failed: CHECK constraint failed: c<>5}}
667
668 finish_test