xref: /sqlite-3.40.0/test/shell6.test (revision 94c67eca) 
1# 2009 Nov 11
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. Specifically,
13# the ".recommend" command.
14#
15#
16
17# Test plan:
18#
19#   shell1-1.*: Basic command line option handling.
20#   shell1-2.*: Basic "dot" command token parsing.
21#   shell1-3.*: Basic test that "dot" command can be called.
22#
23set testdir [file dirname $argv0]
24source $testdir/tester.tcl
25set testprefix shell6
26
27if {$tcl_platform(platform)=="windows"} {
28  set CLI "sqlite3.exe"
29} else {
30  set CLI "./sqlite3"
31}
32if {![file executable $CLI]} {
33  finish_test
34  return
35}
36
37
38proc squish {txt} {
39  regsub -all {[[:space:]]+} $txt { }
40}
41
42proc do_rec_test {tn sql res} {
43  set res [squish [string trim $res]]
44  set tst [subst -nocommands {
45    squish [lindex [catchcmd [list -rec test.db {$sql;}]] 1]
46  }]
47  uplevel [list do_test $tn $tst $res]
48}
49
50proc do_setup_rec_test {tn setup sql res} {
51  reset_db
52  db eval $setup
53  uplevel [list do_rec_test $tn $sql $res]
54}
55
56
57do_setup_rec_test 1.1 { CREATE TABLE t1(a, b, c) } {
58  SELECT * FROM t1
59} {
60  (no new indexes)
61  0|0|0|SCAN TABLE t1
62}
63
64do_setup_rec_test 1.2 {
65  CREATE TABLE t1(a, b, c);
66} {
67  SELECT * FROM t1 WHERE b>?;
68} {
69  CREATE INDEX t1_idx_00000062 ON t1(b)
70  0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000062 (b>?)
71}
72
73do_setup_rec_test 1.3 {
74  CREATE TABLE t1(a, b, c);
75} {
76  SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ?
77} {
78  CREATE INDEX t1_idx_3e094c27 ON t1(b COLLATE NOCASE)
79  0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_3e094c27 (b>? AND b<?)
80}
81
82do_setup_rec_test 1.4 {
83  CREATE TABLE t1(a, b, c);
84} {
85  SELECT a FROM t1 ORDER BY b;
86} {
87  CREATE INDEX t1_idx_00000062 ON t1(b)
88  0|0|0|SCAN TABLE t1 USING INDEX t1_idx_00000062
89}
90
91do_setup_rec_test 1.5 {
92  CREATE TABLE t1(a, b, c);
93} {
94  SELECT a FROM t1 WHERE a=? ORDER BY b;
95} {
96  CREATE INDEX t1_idx_000123a7 ON t1(a, b)
97  0|0|0|SEARCH TABLE t1 USING COVERING INDEX t1_idx_000123a7 (a=?)
98}
99
100do_setup_rec_test 1.6 {
101  CREATE TABLE t1(a, b, c);
102} {
103  SELECT min(a) FROM t1
104} {
105  CREATE INDEX t1_idx_00000061 ON t1(a)
106  0|0|0|SEARCH TABLE t1 USING COVERING INDEX t1_idx_00000061
107}
108
109do_setup_rec_test 1.7 {
110  CREATE TABLE t1(a, b, c);
111} {
112  SELECT * FROM t1 ORDER BY a, b, c;
113} {
114  CREATE INDEX t1_idx_033e95fe ON t1(a, b, c)
115  0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_033e95fe
116}
117
118do_setup_rec_test 1.8 {
119  CREATE TABLE t1(a, b, c);
120} {
121  SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC;
122} {
123  CREATE INDEX t1_idx_5be6e222 ON t1(a, b COLLATE NOCASE DESC, c)
124  0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_5be6e222
125}
126
127do_setup_rec_test 1.9 {
128  CREATE TABLE t1(a COLLATE NOCase, b, c);
129} {
130  SELECT * FROM t1 WHERE a=?
131} {
132  CREATE INDEX t1_idx_00000061 ON t1(a)
133  0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000061 (a=?)
134}
135
136
137# Tables with names that require quotes.
138#
139do_setup_rec_test 8.1 {
140  CREATE TABLE "t t"(a, b, c);
141} {
142  SELECT * FROM "t t" WHERE a=?
143} {
144  CREATE INDEX 't t_idx_00000061' ON 't t'(a)
145  0|0|0|SEARCH TABLE t t USING INDEX t t_idx_00000061 (a=?)
146}
147
148do_setup_rec_test 8.2 {
149  CREATE TABLE "t t"(a, b, c);
150} {
151  SELECT * FROM "t t" WHERE b BETWEEN ? AND ?
152} {
153  CREATE INDEX 't t_idx_00000062' ON 't t'(b)
154  0|0|0|SEARCH TABLE t t USING INDEX t t_idx_00000062 (b>? AND b<?)
155}
156
157# Columns with names that require quotes.
158#
159do_setup_rec_test 9.1 {
160  CREATE TABLE t3(a, "b b", c);
161} {
162  SELECT * FROM t3 WHERE "b b" = ?
163} {
164  CREATE INDEX t3_idx_00050c52 ON t3('b b')
165  0|0|0|SEARCH TABLE t3 USING INDEX t3_idx_00050c52 (b b=?)
166}
167
168do_setup_rec_test 9.2 {
169  CREATE TABLE t3(a, "b b", c);
170} {
171  SELECT * FROM t3 ORDER BY "b b"
172} {
173  CREATE INDEX t3_idx_00050c52 ON t3('b b')
174  0|0|0|SCAN TABLE t3 USING INDEX t3_idx_00050c52
175}
176
177# Transitive constraints
178#
179do_setup_rec_test 10.1 {
180  CREATE TABLE t5(a, b);
181  CREATE TABLE t6(c, d);
182} {
183  SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=?
184} {
185  CREATE INDEX t5_idx_000123a7 ON t5(a, b)
186  CREATE INDEX t6_idx_00000063 ON t6(c)
187  0|0|1|SEARCH TABLE t6 USING INDEX t6_idx_00000063 (c=?)
188  0|1|0|SEARCH TABLE t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?)
189}
190
191finish_test
192