Debian Codegraph @

test/import01.sql

raw
1 #!sqlite3
2 #
3 # 2025-12-28
4 #
5 # The author disclaims copyright to this source code. In place of
6 # a legal notice, here is a blessing:
7 #
8 # May you do good and not evil.
9 # May you find forgiveness for yourself and forgive others.
10 # May you share freely, never taking more than you give.
11 #
12 #***********************************************************************
13 #
14 # Test cases for the ".import" command of the CLI.
15 # To run these tests:
16 #
17 # ./sqlite3 test/import01.sql
18 #
19
20 .testcase setup
21 .open :memory:
22 .mode tty
23 .check ''
24
25 .testcase 100
26 CREATE TABLE t1(a,b,c);
27 .import -csv <<END t1
28 111,222,333
29 abc,def,ghi
30 END
31 SELECT * FROM t1;
32 .check <<END
33 ╭───────┬───────┬───────╮
34 │ a │ b │ c │
35 ╞═══════╪═══════╪═══════╡
36 │ '111' │ '222' │ '333' │
37 │ abc │ def │ ghi │
38 ╰───────┴───────┴───────╯
39 END
40
41 .testcase 110
42 DELETE FROM t1;
43 .import -colsep ";" <<END t1
44 this;is a;test
45 one;two;three
46 END
47 SELECT * FROM t1;
48 .check <<END
49 ╭──────┬──────┬───────╮
50 │ a │ b │ c │
51 ╞══════╪══════╪═══════╡
52 │ this │ is a │ test │
53 │ one │ two │ three │
54 ╰──────┴──────┴───────╯
55 END
56
57 .testcase 120
58 DELETE FROM t1;
59 .import -colsep "," -rowsep ';' <<END t1
60 this,is a,test;one,two,three;
61 END
62 SELECT * FROM t1;
63 .check <<END
64 ╭──────┬──────┬───────╮
65 │ a │ b │ c │
66 ╞══════╪══════╪═══════╡
67 │ this │ is a │ test │
68 │ one │ two │ three │
69 ╰──────┴──────┴───────╯
70 END
71
72 .testcase 130
73 DELETE FROM t1;
74 .import -csv -colsep "," -rowsep "\n" <<END t1
75 this,"is a","test ""with quotes"""
76 "second",,"line"
77 END
78 SELECT * FROM t1;
79 .check <<END
80 ╭────────┬──────┬────────────────────╮
81 │ a │ b │ c │
82 ╞════════╪══════╪════════════════════╡
83 │ this │ is a │ test "with quotes" │
84 │ second │ │ line │
85 ╰────────┴──────┴────────────────────╯
86 END
87 .testcase 131
88 DELETE FROM t1;
89 .import -ascii -colsep "," -rowsep "\n" <<END t1
90 this,"is a","test ""with quotes"""
91 "second",,"line"
92 END
93 SELECT * FROM t1;
94 .check <<END
95 ╭──────────┬────────┬────────────────────────╮
96 │ a │ b │ c │
97 ╞══════════╪════════╪════════════════════════╡
98 │ this │ "is a" │ "test ""with quotes""" │
99 │ "second" │ │ "line" │
100 ╰──────────┴────────┴────────────────────────╯
101 END
102
103 .testcase 140
104 DROP TABLE t1;
105 .import -csv <<END t1
106 "abc","def","xy z"
107 "This","is","a"
108 "test","...",
109 END
110 SELECT * FROM t1;
111 .check <<END
112 ╭──────┬─────┬──────╮
113 │ abc │ def │ xy z │
114 ╞══════╪═════╪══════╡
115 │ This │ is │ a │
116 │ test │ ... │ │
117 ╰──────┴─────┴──────╯
118 END
119 .testcase 141
120 SELECT sql FROM sqlite_schema WHERE name='t1';
121 .check <<END
122 ╭───────────────────────────────────╮
123 │ sql │
124 ╞═══════════════════════════════════╡
125 │ CREATE TABLE "t1"( │
126 │ "abc" ANY, "def" ANY, "xy z" ANY) │
127 ╰───────────────────────────────────╯
128 END
129
130 .testcase 150
131 DROP TABLE t1;
132 .import -csv -v <<END t1
133 "abc","def","xy z"
134 "This","is","a"
135 "test","...",
136 END
137 SELECT * FROM t1;
138 .check <<END
139 Column separator ",", row separator "\n"
140 CREATE TABLE "main"."t1"(
141 "abc" ANY, "def" ANY, "xy z" ANY)
142
143 Added 2 rows with 0 errors using 3 lines of input
144 ╭──────┬─────┬──────╮
145 │ abc │ def │ xy z │
146 ╞══════╪═════╪══════╡
147 │ This │ is │ a │
148 │ test │ ... │ │
149 ╰──────┴─────┴──────╯
150 END
151
152 .testcase 160
153 DROP TABLE t1;
154 .import -csv -schema TEMP <<END t2
155 "x"
156 "abcdef"
157 END
158 SELECT * FROM t2;
159 .tables
160 .check <<END
161 ╭────────╮
162 │ x │
163 ╞════════╡
164 │ abcdef │
165 ╰────────╯
166 temp.t2
167 END
168
169 .testcase 170
170 .import -csv -skip 2 <<END t3
171 a,b,c
172 d,e,f
173 g,h,i
174 j,k,l
175 m,n,o
176 END
177 SELECT * FROM t3;
178 .check <<END
179 ╭───┬───┬───╮
180 │ g │ h │ i │
181 ╞═══╪═══╪═══╡
182 │ j │ k │ l │
183 │ m │ n │ o │
184 ╰───┴───┴───╯
185 END
186
187 .testcase 180
188 DELETE FROM t3;
189 .import -csv -skip 7 <<END t3
190 a,b,c
191 d,e,f
192 g,h,i
193 j,k,l
194 m,n,o
195 END
196 SELECT * FROM t3;
197 .check <<END
198 END
199
200 .testcase 200 --error-prefix ERROR:
201 .import -csv
202 .check 'ERROR: Missing FILE argument'
203 .testcase 201
204 .import -csv file1.csv
205 .check 'ERROR: Missing TABLE argument'
206 .testcase --error-prefix test-error: 202
207 .import -csvxyzzy file1.csv
208 .check <<END
209 test-error: .import -csvxyzzy file1.csv
210 test-error: ^--- unknown option
211 END
212 .testcase 203
213 .import -csv file1.csv t4 -colsep
214 .check <<END
215 test-error: .import -csv file1.csv t4 -colsep
216 test-error: missing argument ---^
217 END