Debian Codegraph @

test/modeA.sql

raw
1 #!sqlite3
2 #
3 # 2025-11-12
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 ".mode" command of the CLI.
15 # To run these tests:
16 #
17 # ./sqlite3 test/modeA.sql
18 #
19 #
20 .open :memory:
21 CREATE TABLE t1(a,b,c,d,e);
22 INSERT INTO t1 VALUES(1,2.5,'three',x'4444',NULL);
23 INSERT INTO t1 SELECT b,c,d,e,a FROM t1;
24 INSERT INTO t1 SELECT d,e,a,b,c FROM t1;
25 .mode box
26
27 .testcase 100
28 SELECT * FROM t1;
29 .check <<END
30 ╭─────┬───────┬───────┬───────┬───────╮
31 │ a │ b │ c │ d │ e │
32 ╞═════╪═══════╪═══════╪═══════╪═══════╡
33 │ 1 │ 2.5 │ three │ DD │ │
34 │ 2.5 │ three │ DD │ │ 1 │
35 │ DD │ │ 1 │ 2.5 │ three │
36 │ │ 1 │ 2.5 │ three │ DD │
37 ╰─────┴───────┴───────┴───────┴───────╯
38 END
39
40 .testcase 110
41 .mode --null xyz
42 SELECT * FROM t1;
43 .check <<END
44 ╭─────┬───────┬───────┬───────┬───────╮
45 │ a │ b │ c │ d │ e │
46 ╞═════╪═══════╪═══════╪═══════╪═══════╡
47 │ 1 │ 2.5 │ three │ DD │ xyz │
48 │ 2.5 │ three │ DD │ xyz │ 1 │
49 │ DD │ xyz │ 1 │ 2.5 │ three │
50 │ xyz │ 1 │ 2.5 │ three │ DD │
51 ╰─────┴───────┴───────┴───────┴───────╯
52 END
53
54 # Default output mode is qbox --quote relaxed
55 #
56 .mode tty --wrap 10
57 CREATE TABLE t2(a,b,c,d);
58 INSERT INTO t2 VALUES(1,2.5,'three',x'4444');
59 INSERT INTO t2 VALUES('The quick fox jumps over the lazy brown dog',2,3,4);
60 INSERT INTO t2 VALUES('10','', -1.25,NULL);
61 INSERT INTO t2 VALUES('a,b,c','"Double-Quoted"','-1.25','NULL');
62 .testcase 120
63 SELECT * FROM t2;
64 .check <<END
65 ╭────────────┬────────────┬─────────┬─────────╮
66 │ a │ b │ c │ d │
67 ╞════════════╪════════════╪═════════╪═════════╡
68 │ 1 │ 2.5 │ three │ x'4444' │
69 ├────────────┼────────────┼─────────┼─────────┤
70 │ The quick │ 2 │ 3 │ 4 │
71 │ fox jumps │ │ │ │
72 │ over the │ │ │ │
73 │ lazy brown │ │ │ │
74 │ dog │ │ │ │
75 ├────────────┼────────────┼─────────┼─────────┤
76 │ '10' │ │ -1.25 │ NULL │
77 ├────────────┼────────────┼─────────┼─────────┤
78 │ a,b,c │ "Double- │ '-1.25' │ 'NULL' │
79 │ │ Quoted" │ │ │
80 ╰────────────┴────────────┴─────────┴─────────╯
81 END
82 .testcase 121
83 .mode tty --wrap 1 --limits off
84 SELECT 'xyz123' AS a, 2 AS b;
85 .check <<END
86 ╭───┬───╮
87 │ a │ b │
88 ╞═══╪═══╡
89 │ x │ 2 │
90 │ y │ │
91 │ z │ │
92 │ 1 │ │
93 │ 2 │ │
94 │ 3 │ │
95 ╰───┴───╯
96 END
97 .testcase 130
98 .mode tty -wrap 10
99 .mode
100 .check <<END
101 .mode qbox --limits on --quote relaxed --sw auto --textjsonb on
102 END
103 .testcase 140
104 .mode -v
105 .check <<END
106 .mode qbox --align "" --border on --blob-quote auto --colsep "" --escape auto --limits on --multiinsert 3000 --null "NULL" --quote relaxed --rowsep "" --sw auto --tablename "" --textjsonb on --titles on --widths "" --wordwrap off --wrap 10
107 END
108 .testcase 150 --error-prefix "Error:"
109 .mode foo
110 .check <<END
111 Error: .mode foo
112 Error: ^--- unknown mode
113 Error: Use ".help .mode" for more info
114 END
115
116 .testcase 160
117 .mode --null xyzzy -v
118 .output -glob ' --null "xyzzy"'
119 .testcase 170
120 .mode -null abcde -v
121 .output -glob ' --null "abcde"'
122
123 # Test cases for the ".explain off" command
124 .mode box -reset
125 .testcase 180
126 EXPLAIN SELECT * FROM t1;
127 .output --notglob *────* --keep
128 .output --notglob "* id │ parent │ notused │ detail *" --keep
129 .output --glob "* Init *"
130 .testcase 190
131 EXPLAIN QUERY PLAN SELECT * FROM t1;
132 .output --glob "*`--SCAN *"
133 .explain off
134 .testcase 200
135 EXPLAIN SELECT * FROM t1;
136 .output --glob *────*
137 .testcase 210
138 EXPLAIN QUERY PLAN SELECT * FROM t1;
139 .output --glob "* id │ parent │ notused │ detail *"
140 .explain auto
141
142 # Test cases for limit settings in the .mode command.
143 .testcase 300
144 .mode box --reset
145 .mode
146 .check <<END
147 .mode box
148 END
149 .testcase 310
150 .mode --limits 5,300,20
151 .mode
152 .check <<END
153 .mode box --limits on
154 END
155 .testcase 320
156 .mode --limits 5,300,19
157 .mode
158 .check <<END
159 .mode box --limits 5,300,19
160 END
161 .testcase 330
162 .mode --limits 0,0,0
163 .mode -v
164 .check <<END
165 .mode box --align "" --border on --blob-quote auto --colsep "" --escape auto --limits off --multiinsert 0 --null "" --quote off --rowsep "" --sw 0 --tablename "" --textjsonb off --titles on --widths "" --wordwrap off
166 END
167
168 .testcase 400
169 .mode --linelimit 123
170 .mode
171 .check <<END
172 .mode box --limits 123,0,0
173 END
174
175 .testcase 410
176 .mode --linelimit 0 -charlimit 123
177 .mode
178 .check <<END
179 .mode box --limits 0,123,0
180 END
181
182 .testcase 420
183 .mode --charlimit 0 -titlelimit 123
184 .mode
185 .check <<END
186 .mode box --limits 0,0,123
187 END
188
189 .testcase 430
190 .mode list
191 .mode
192 .check <<END
193 .mode list
194 END
195
196 .testcase 440
197 .mode -limits 0,123,0
198 .mode
199 .check <<END
200 .mode list --limits 0,123,0
201 END
202
203 .testcase 450
204 .mode -limits 123,0,0
205 .mode
206 .check <<END
207 .mode list
208 END
209
210 # --titlelimit functionality
211 #
212 .testcase 500
213 .mode line --limits off --titlelimit 20
214 SELECT a AS 'abcdefghijklmnopqrstuvwxyz', b FROM t2 WHERE c=3;
215 .check <<END
216 abcdefghijklmnopq...: The quick fox jumps over the lazy brown dog
217 b: 2
218 END
219 .testcase 510
220 .mode line --titlelimit 10
221 SELECT a AS 'abcdefghijklmnopqrstuvwxyz', b FROM t2 WHERE c=3;
222 .check <<END
223 abcdefg...: The quick fox jumps over the lazy brown dog
224 b: 2
225 END
226 .testcase 520
227 .mode line --titlelimit 2
228 SELECT a AS 'abcdefghijklmnopqrstuvwxyz', b FROM t2 WHERE c=3;
229 .check <<END
230 ab: The quick fox jumps over the lazy brown dog
231 b: 2
232 END
233 .testcase 530
234 .mode line --titlelimit 4
235 SELECT a AS 'abcd', b FROM t2 WHERE c=3;
236 .check <<END
237 abcd: The quick fox jumps over the lazy brown dog
238 b: 2
239 END
240 .testcase 540
241 .mode line --titlelimit 3
242 SELECT a AS 'abcd', b FROM t2 WHERE c=3;
243 .check <<END
244 ...: The quick fox jumps over the lazy brown dog
245 b: 2
246 END
247
248 # line --screenwidth and --colsep
249 #
250 .testcase 550
251 .mode line --sw 40 --colsep ":-hi-:"
252 SELECT a AS 'abc', b FROM t2 WHERE c=3;
253 .check <<END
254 abc:-hi-:The quick fox jumps over the
255 lazy brown dog
256 b:-hi-:2
257 END
258 .testcase 551
259 .mode line --sw 40 --colsep ":-hi-:" --wordwrap off
260 SELECT a AS 'abc', b FROM t2 WHERE c=3;
261 .check <<END
262 abc:-hi-:The quick fox jumps over the la
263 zy brown dog
264 b:-hi-:2
265 END
266 # 23456789 123456789 123456789 123456789
267
268 # https://sqlite.org/forum/forumpost/2025-12-31T19:14:24z
269 #
270 # For legacy compatibility, ".header" settings are not changed
271 # by ".mode" unless the --title or --reset option is used on .mode.
272 #
273 .testcase 600
274 DROP TABLE IF EXISTS t1;
275 CREATE TABLE t1(a,b,c);
276 INSERT INTO t1 VALUES(1,2,3);
277 .header on
278 .mode csv
279 SELECT * FROM t1;
280 .check --glob a,b,c*
281
282 .testcase 610
283 .mode csv -reset
284 SELECT * FROM t1;
285 .check 1,2,3
286
287 .testcase 620
288 .mode tty
289 .mode csv
290 .header on
291 SELECT * FROM t1;
292 .check --glob a,b,c*
293
294 .testcase 630
295 .mode tty
296 .mode csv --title on
297 SELECT * FROM t1;
298 .check --glob a,b,c*
299 .testcase 631
300 .mode tty
301 .mode csv --title off
302 SELECT * FROM t1;
303 .check 1,2,3
304
305 # Verification of claims about .insert mode in the climode.html
306 # documentation.
307 .testcase 700
308 CREATE TABLE tbl1(one,two);
309 INSERT INTO tbl1 VALUES('hello!',10),('goodbye',20);
310 .mode insert new_table --multiinsert 0
311 SELECT * FROM tbl1;
312 .check <<END
313 INSERT INTO new_table VALUES('hello!',10);
314 INSERT INTO new_table VALUES('goodbye',20);
315 END
316 .testcase 710
317 .mode insert new_table --titles on
318 SELECT * FROM tbl1;
319 .check <<END
320 INSERT INTO new_table(one,two) VALUES('hello!',10);
321 INSERT INTO new_table(one,two) VALUES('goodbye',20);
322 END
323 .testcase 720
324 .mode insert new_table --titles off
325 SELECT * FROM tbl1;
326 .check <<END
327 INSERT INTO new_table VALUES('hello!',10);
328 INSERT INTO new_table VALUES('goodbye',20);
329 END
330
331 # QRF reports an error if the string is too big.
332 #
333 .testcase 800
334 .mode box
335 .limit length 1000
336 WITH c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<100)
337 SELECT hex(randomblob(100)) c;
338 .check -glob "*: string or blob too big"
339 .limit length 10000000