1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
|
# 2010 July 09
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. The
# focus of this file is testing the SELECT statement.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable !vtab {
finish_test
return
}
set ::asc 1
proc a_string {n} { string range [string repeat [incr ::asc]. $n] 1 $n }
db func a_string a_string
register_dbstat_vtab db
do_execsql_test stat-0.0 {
PRAGMA auto_vacuum = OFF;
CREATE VIRTUAL TABLE temp.stat USING dbstat;
SELECT * FROM stat;
} {}
ifcapable wal {
do_execsql_test stat-0.1 {
PRAGMA journal_mode = WAL;
PRAGMA journal_mode = delete;
SELECT * FROM stat;
} {wal delete sqlite_master / 1 leaf 0 0 916 0}
}
do_test stat-1.0 {
execsql {
CREATE TABLE t1(a, b);
CREATE INDEX i1 ON t1(b);
INSERT INTO t1(rowid, a, b) VALUES(2, 2, 3);
INSERT INTO t1(rowid, a, b) VALUES(3, 4, 5);
}
} {}
do_test stat-1.1 {
execsql {
SELECT * FROM stat WHERE name = 't1';
}
} {t1 / 2 leaf 2 10 998 5}
do_test stat-1.2 {
execsql {
SELECT * FROM stat WHERE name = 'i1';
}
} {i1 / 3 leaf 2 10 1000 5}
do_test stat-1.3 {
execsql {
SELECT * FROM stat WHERE name = 'sqlite_master';
}
} {sqlite_master / 1 leaf 2 77 831 40}
do_test stat-1.4 {
execsql {
DROP TABLE t1;
}
} {}
do_execsql_test stat-2.1 {
CREATE TABLE t3(a PRIMARY KEY, b);
INSERT INTO t3(rowid, a, b) VALUES(2, a_string(111), a_string(222));
INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
SELECT * FROM stat WHERE name != 'sqlite_master';
} [list \
sqlite_autoindex_t3_1 / 3 internal 3 368 623 125 \
sqlite_autoindex_t3_1 /000/ 8 leaf 8 946 46 123 \
sqlite_autoindex_t3_1 /001/ 9 leaf 8 988 2 131 \
sqlite_autoindex_t3_1 /002/ 15 leaf 7 857 137 132 \
sqlite_autoindex_t3_1 /003/ 20 leaf 6 739 257 129 \
t3 / 2 internal 15 0 907 0 \
t3 /000/ 4 leaf 2 678 328 340 \
t3 /001/ 5 leaf 2 682 324 342 \
t3 /002/ 6 leaf 2 682 324 342 \
t3 /003/ 7 leaf 2 690 316 346 \
t3 /004/ 10 leaf 2 682 324 342 \
t3 /005/ 11 leaf 2 690 316 346 \
t3 /006/ 12 leaf 2 698 308 350 \
t3 /007/ 13 leaf 2 706 300 354 \
t3 /008/ 14 leaf 2 682 324 342 \
t3 /009/ 16 leaf 2 690 316 346 \
t3 /00a/ 17 leaf 2 698 308 350 \
t3 /00b/ 18 leaf 2 706 300 354 \
t3 /00c/ 19 leaf 2 714 292 358 \
t3 /00d/ 21 leaf 2 722 284 362 \
t3 /00e/ 22 leaf 2 730 276 366 \
t3 /00f/ 23 leaf 2 738 268 370 \
]
do_execsql_test stat-2.2 { DROP TABLE t3 } {}
do_execsql_test stat-3.1 {
CREATE TABLE t4(x);
CREATE INDEX i4 ON t4(x);
INSERT INTO t4(rowid, x) VALUES(2, a_string(7777));
SELECT * FROM stat WHERE name != 'sqlite_master';
} [list \
i4 / 3 leaf 1 103 905 7782 \
i4 /000+000000 9 overflow 0 1020 0 0 \
i4 /000+000001 10 overflow 0 1020 0 0 \
i4 /000+000002 11 overflow 0 1020 0 0 \
i4 /000+000003 12 overflow 0 1020 0 0 \
i4 /000+000004 13 overflow 0 1020 0 0 \
i4 /000+000005 14 overflow 0 1020 0 0 \
i4 /000+000006 15 overflow 0 1020 0 0 \
i4 /000+000007 16 overflow 0 539 481 0 \
t4 / 2 leaf 1 640 367 7780 \
t4 /000+000000 22 overflow 0 1020 0 0 \
t4 /000+000001 23 overflow 0 1020 0 0 \
t4 /000+000002 21 overflow 0 1020 0 0 \
t4 /000+000003 20 overflow 0 1020 0 0 \
t4 /000+000004 19 overflow 0 1020 0 0 \
t4 /000+000005 18 overflow 0 1020 0 0 \
t4 /000+000006 17 overflow 0 1020 0 0 \
]
do_execsql_test stat-4.1 {
CREATE TABLE t5(x);
CREATE INDEX i5 ON t5(x);
SELECT * FROM stat WHERE name = 't5' OR name = 'i5';
} [list \
i5 / 5 leaf 0 0 1016 0 \
t5 / 4 leaf 0 0 1016 0 \
]
db close
file delete -force test.db
sqlite3 db test.db
register_dbstat_vtab db
breakpoint
do_execsql_test stat-5.1 {
PRAGMA auto_vacuum = OFF;
CREATE VIRTUAL TABLE temp.stat USING dbstat;
CREATE TABLE t1(x);
INSERT INTO t1 VALUES(zeroblob(1513));
INSERT INTO t1 VALUES(zeroblob(1514));
SELECT * FROM stat WHERE name = 't1';
} [list \
t1 / 2 leaf 2 993 5 1517 \
t1 /000+000000 3 overflow 0 1020 0 0 \
t1 /001+000000 4 overflow 0 1020 0 0 \
]
finish_test
|
