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.1.1.1 2012/02/21 17:04:17 misho Exp $
16: #
17:
18: # Test plan:
19: #
20: # shell5-1.*: Basic tests specific to the ".import" command.
21: #
22:
23: set CLI "./sqlite3"
24:
25: proc do_test {name cmd expected} {
26: puts -nonewline "$name ..."
27: set res [uplevel $cmd]
28: if {$res eq $expected} {
29: puts Ok
30: } else {
31: puts Error
32: puts " Got: $res"
33: puts " Expected: $expected"
34: exit
35: }
36: }
37:
38: proc catchcmd {db {cmd ""}} {
39: global CLI
40: set out [open cmds.txt w]
41: puts $out $cmd
42: close $out
43: set line "exec $CLI $db < cmds.txt"
44: set rc [catch { eval $line } msg]
45: list $rc $msg
46: }
47:
48: file delete -force test.db test.db.journal
49:
50: #----------------------------------------------------------------------------
51: # Test cases shell5-1.*: Basic handling of the .import and .separator commands.
52: #
53:
54: # .import FILE TABLE Import data from FILE into TABLE
55: do_test shell5-1.1.1 {
56: catchcmd "test.db" ".import"
57: } {1 {Error: unknown command or invalid arguments: "import". Enter ".help" for help}}
58: do_test shell5-1.1.2 {
59: catchcmd "test.db" ".import FOO"
60: } {1 {Error: unknown command or invalid arguments: "import". Enter ".help" for help}}
61: do_test shell5-1.1.2 {
62: catchcmd "test.db" ".import FOO BAR"
63: } {1 {Error: no such table: BAR}}
64: do_test shell5-1.1.3 {
65: # too many arguments
66: catchcmd "test.db" ".import FOO BAR BAD"
67: } {1 {Error: unknown command or invalid arguments: "import". Enter ".help" for help}}
68:
69: # .separator STRING Change separator used by output mode and .import
70: do_test shell1-1.2.1 {
71: catchcmd "test.db" ".separator"
72: } {1 {Error: unknown command or invalid arguments: "separator". Enter ".help" for help}}
73: do_test shell1-1.2.2 {
74: catchcmd "test.db" ".separator FOO"
75: } {0 {}}
76: do_test shell1-1.2.3 {
77: # too many arguments
78: catchcmd "test.db" ".separator FOO BAD"
79: } {1 {Error: unknown command or invalid arguments: "separator". Enter ".help" for help}}
80:
81: # separator should default to "|"
82: do_test shell5-1.3.1 {
83: set res [catchcmd "test.db" ".show"]
84: list [regexp {separator: \"\|\"} $res]
85: } {1}
86:
87: # set separator to different value.
88: # check that .show reports new value
89: do_test shell5-1.3.2 {
90: set res [catchcmd "test.db" {.separator ,
91: .show}]
92: list [regexp {separator: \",\"} $res]
93: } {1}
94:
95: # import file doesn't exist
96: do_test shell5-1.4.1 {
97: file delete -force FOO
98: set res [catchcmd "test.db" {CREATE TABLE t1(a, b);
99: .import FOO t1}]
100: } {1 {Error: cannot open "FOO"}}
101:
102: # empty import file
103: do_test shell5-1.4.2 {
104: file delete -force shell5.csv
105: set in [open shell5.csv w]
106: close $in
107: set res [catchcmd "test.db" {.import shell5.csv t1
108: SELECT COUNT(*) FROM t1;}]
109: } {0 0}
110:
111: # import file with 1 row, 1 column (expecting 2 cols)
112: do_test shell5-1.4.3 {
113: set in [open shell5.csv w]
114: puts $in "1"
115: close $in
116: set res [catchcmd "test.db" {.import shell5.csv t1}]
117: } {1 {Error: shell5.csv line 1: expected 2 columns of data but found 1}}
118:
119: # import file with 1 row, 3 columns (expecting 2 cols)
120: do_test shell5-1.4.4 {
121: set in [open shell5.csv w]
122: puts $in "1|2|3"
123: close $in
124: set res [catchcmd "test.db" {.import shell5.csv t1}]
125: } {1 {Error: shell5.csv line 1: expected 2 columns of data but found 3}}
126:
127: # import file with 1 row, 2 columns
128: do_test shell5-1.4.5 {
129: set in [open shell5.csv w]
130: puts $in "1|2"
131: close $in
132: set res [catchcmd "test.db" {.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 "test.db" {.import shell5.csv t1
145: SELECT COUNT(*) FROM t1;}]
146: } {0 3}
147:
148: # import file with 1 row, 2 columns, using a comma
149: do_test shell5-1.4.7 {
150: set in [open shell5.csv w]
151: puts $in "4,5"
152: close $in
153: set res [catchcmd "test.db" {.separator ,
154: .import shell5.csv t1
155: SELECT COUNT(*) FROM t1;}]
156: } {0 4}
157:
158: # import file with 1 row, 2 columns, text data
159: do_test shell5-1.4.8.1 {
160: set in [open shell5.csv w]
161: puts $in "5|Now is the time for all good men to come to the aid of their country."
162: close $in
163: set res [catchcmd "test.db" {.import shell5.csv t1
164: SELECT COUNT(*) FROM t1;}]
165: } {0 5}
166:
167: do_test shell5-1.4.8.2 {
168: catchcmd "test.db" {SELECT b FROM t1 WHERE a='5';}
169: } {0 {Now is the time for all good men to come to the aid of their country.}}
170:
171: # import file with 1 row, 2 columns, quoted text data
172: # note that currently sqlite doesn't support quoted fields, and
173: # imports the entire field, quotes and all.
174: do_test shell5-1.4.9.1 {
175: set in [open shell5.csv w]
176: puts $in "6|'Now is the time for all good men to come to the aid of their country.'"
177: close $in
178: set res [catchcmd "test.db" {.import shell5.csv t1
179: SELECT COUNT(*) FROM t1;}]
180: } {0 6}
181:
182: do_test shell5-1.4.9.2 {
183: catchcmd "test.db" {SELECT b FROM t1 WHERE a='6';}
184: } {0 {'Now is the time for all good men to come to the aid of their country.'}}
185:
186: # import file with 1 row, 2 columns, quoted text data
187: do_test shell5-1.4.10.1 {
188: set in [open shell5.csv w]
189: puts $in "7|\"Now is the time for all good men to come to the aid of their country.\""
190: close $in
191: set res [catchcmd "test.db" {.import shell5.csv t1
192: SELECT COUNT(*) FROM t1;}]
193: } {0 7}
194:
195: do_test shell5-1.4.10.2 {
196: catchcmd "test.db" {SELECT b FROM t1 WHERE a='7';}
197: } {0 {"Now is the time for all good men to come to the aid of their country."}}
198:
199: # check importing very long field
200: do_test shell5-1.5.1 {
201: set str [string repeat X 999]
202: set in [open shell5.csv w]
203: puts $in "8|$str"
204: close $in
205: set res [catchcmd "test.db" {.import shell5.csv t1
206: SELECT length(b) FROM t1 WHERE a='8';}]
207: } {0 999}
208:
209: # try importing into a table with a large number of columns.
210: # This is limited by SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999.
211: set cols 999
212: do_test shell5-1.6.1 {
213: set sql {CREATE TABLE t2(}
214: set data {}
215: for {set i 1} {$i<$cols} {incr i} {
216: append sql "c$i,"
217: append data "$i|"
218: }
219: append sql "c$cols);"
220: append data "$cols"
221: catchcmd "test.db" $sql
222: set in [open shell5.csv w]
223: puts $in $data
224: close $in
225: set res [catchcmd "test.db" {.import shell5.csv t2
226: SELECT COUNT(*) FROM t2;}]
227: } {0 1}
228:
229: # try importing a large number of rows
230: set rows 999999
231: do_test shell5-1.7.1 {
232: set in [open shell5.csv w]
233: for {set i 1} {$i<=$rows} {incr i} {
234: puts $in $i
235: }
236: close $in
237: set res [catchcmd "test.db" {CREATE TABLE t3(a);
238: .import shell5.csv t3
239: SELECT COUNT(*) FROM t3;}]
240: } [list 0 $rows]
241:
242:
243: puts "CLI tests completed successfully"
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to shell5.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / tool