| 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 |