AryaWu/sqlite
0
1# 2010 July 092#3# The author disclaims copyright to this source code. In place of4# 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# This file implements regression tests for SQLite library. The12# focus of this file is testing the SELECT statement.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set testprefix stat18 19ifcapable !vtab||!compound {20 finish_test21 return22}23 24# This module uses hard-coded results that depend on exact measurements of25# pages sizes at the byte level, and hence will not work if the reserved_bytes26# value is nonzero.27if {[nonzero_reserved_bytes]} {finish_test; return;}28 29set ::asc 130proc a_string {n} { string range [string repeat [incr ::asc]. $n] 1 $n }31db func a_string a_string32 33register_dbstat_vtab db34do_execsql_test stat-0.0 {35 PRAGMA table_info(dbstat);36} {/0 name TEXT .* 1 path TEXT .* 9 pgsize INTEGER/}37 38# Attempts to drop an eponymous virtual table are a no-op.39do_catchsql_test stat-0.1a {40 DROP TABLE dbstat;41} {1 {table dbstat may not be dropped}}42do_execsql_test stat-0.1b {43 PRAGMA table_info=dbstat;44} {/0 name TEXT .* 1 path TEXT .* 9 pgsize INTEGER/}45 46db close47forcedelete test.db48sqlite3 db test.db49db func a_string a_string50register_dbstat_vtab db51do_execsql_test stat-0.2 {52 PRAGMA auto_vacuum = OFF;53 CREATE VIRTUAL TABLE temp.stat USING dbstat;54 SELECT * FROM stat;55} {}56 57 58if {[wal_is_capable]} {59 do_execsql_test stat-0.1 {60 PRAGMA journal_mode = WAL;61 PRAGMA journal_mode = delete;62 SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload63 FROM stat;64 } {wal delete sqlite_schema / 1 leaf 0 0 916 0}65}66 67do_test stat-1.0 {68 execsql {69 CREATE TABLE t1(a, b);70 CREATE INDEX i1 ON t1(b);71 INSERT INTO t1(rowid, a, b) VALUES(2, 2, 3);72 INSERT INTO t1(rowid, a, b) VALUES(3, 4, 5);73 }74} {}75do_test stat-1.1 {76 execsql {77 SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload78 FROM stat WHERE name = 't1';79 }80} {t1 / 2 leaf 2 10 998 5}81do_test stat-1.2 {82 execsql {83 SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload84 FROM stat WHERE name = 'i1';85 }86} {i1 / 3 leaf 2 10 1000 5}87do_test stat-1.3 {88 execsql {89 SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload90 FROM stat WHERE name = 'sqlite_schema';91 }92} {sqlite_schema / 1 leaf 2 77 831 40}93do_test stat-1.4 {94 execsql {95 DROP TABLE t1;96 }97} {}98 99do_execsql_test stat-2.1 {100 CREATE TABLE t3(a PRIMARY KEY, b);101 INSERT INTO t3(rowid, a, b) VALUES(2, a_string(111), a_string(222));102 INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3103 ORDER BY rowid;104 INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3105 ORDER BY rowid;106 INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3107 ORDER BY rowid;108 INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3109 ORDER BY rowid;110 INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3111 ORDER BY rowid;112 SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload113 FROM stat WHERE name != 'sqlite_schema' ORDER BY name;114} [list \115 sqlite_autoindex_t3_1 / 3 internal 3 368 623 125 \116 sqlite_autoindex_t3_1 /000/ 8 leaf 8 946 46 123 \117 sqlite_autoindex_t3_1 /001/ 9 leaf 8 988 2 131 \118 sqlite_autoindex_t3_1 /002/ 15 leaf 7 857 137 132 \119 sqlite_autoindex_t3_1 /003/ 20 leaf 6 739 257 129 \120 t3 / 2 internal 15 0 907 0 \121 t3 /000/ 4 leaf 2 678 328 340 \122 t3 /001/ 5 leaf 2 682 324 342 \123 t3 /002/ 6 leaf 2 682 324 342 \124 t3 /003/ 7 leaf 2 690 316 346 \125 t3 /004/ 10 leaf 2 682 324 342 \126 t3 /005/ 11 leaf 2 690 316 346 \127 t3 /006/ 12 leaf 2 698 308 350 \128 t3 /007/ 13 leaf 2 706 300 354 \129 t3 /008/ 14 leaf 2 682 324 342 \130 t3 /009/ 16 leaf 2 690 316 346 \131 t3 /00a/ 17 leaf 2 698 308 350 \132 t3 /00b/ 18 leaf 2 706 300 354 \133 t3 /00c/ 19 leaf 2 714 292 358 \134 t3 /00d/ 21 leaf 2 722 284 362 \135 t3 /00e/ 22 leaf 2 730 276 366 \136 t3 /00f/ 23 leaf 2 738 268 370 \137]138 139do_execsql_test stat-2.1agg {140 SELECT * FROM dbstat WHERE aggregate=TRUE ORDER BY name;141} [list \142 sqlite_autoindex_t3_1 {} 5 {} 32 3898 1065 132 {} 5120 \143 sqlite_schema {} 1 {} 2 84 824 49 {} 1024 \144 t3 {} 17 {} 47 11188 5815 370 {} 17408 \145]146 147# With every index entry overflowing, make sure no pages are missed 148# (other than the locking page which is 64 in this test build.)149#150do_execsql_test stat-2.2 {151 UPDATE t3 SET a=a||hex(randomblob(700));152 VACUUM;153 SELECT pageno FROM stat EXCEPT SELECT pageno-1 FROM stat;154} {64 136}155 156do_execsql_test stat-2.3 { DROP TABLE t3; VACUUM; } {}157 158do_execsql_test stat-3.1 {159 CREATE TABLE t4(x);160 CREATE INDEX i4 ON t4(x);161 INSERT INTO t4(rowid, x) VALUES(2, a_string(7777));162 SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload163 FROM stat WHERE name != 'sqlite_schema' ORDER BY name;164} [list \165 i4 / 3 leaf 1 103 905 7782 \166 i4 /000+000000 4 overflow 0 1020 0 0 \167 i4 /000+000001 5 overflow 0 1020 0 0 \168 i4 /000+000002 6 overflow 0 1020 0 0 \169 i4 /000+000003 7 overflow 0 1020 0 0 \170 i4 /000+000004 8 overflow 0 1020 0 0 \171 i4 /000+000005 9 overflow 0 1020 0 0 \172 i4 /000+000006 10 overflow 0 1020 0 0 \173 i4 /000+000007 11 overflow 0 539 481 0 \174 t4 / 2 leaf 1 640 367 7780 \175 t4 /000+000000 12 overflow 0 1020 0 0 \176 t4 /000+000001 13 overflow 0 1020 0 0 \177 t4 /000+000002 14 overflow 0 1020 0 0 \178 t4 /000+000003 15 overflow 0 1020 0 0 \179 t4 /000+000004 16 overflow 0 1020 0 0 \180 t4 /000+000005 17 overflow 0 1020 0 0 \181 t4 /000+000006 18 overflow 0 1020 0 0 \182]183 184do_execsql_test stat-3.2 {185 SELECT *, '|' FROM dbstat WHERE aggregate=TRUE ORDER BY name;186} [list \187 i4 {} 9 {} 1 7782 1386 7782 {} 9216 | \188 sqlite_schema {} 1 {} 2 74 834 40 {} 1024 | \189 t4 {} 8 {} 1 7780 367 7780 {} 8192 | \190]191 192 193do_execsql_test stat-4.1 {194 CREATE TABLE t5(x);195 CREATE INDEX i5 ON t5(x);196 SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload197 FROM stat WHERE name = 't5' OR name = 'i5';198} [list \199 i5 / 20 leaf 0 0 1016 0 \200 t5 / 19 leaf 0 0 1016 0 \201]202 203db close204forcedelete test.db205sqlite3 db test.db206register_dbstat_vtab db207do_execsql_test stat-5.1 {208 PRAGMA auto_vacuum = OFF;209 CREATE TABLE tx(y);210 ATTACH ':memory:' AS aux1;211 CREATE VIRTUAL TABLE temp.stat USING dbstat(aux1);212 CREATE TABLE aux1.t1(x);213 INSERT INTO t1 VALUES(zeroblob(1513));214 INSERT INTO t1 VALUES(zeroblob(1514));215 SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload216 FROM stat WHERE name = 't1';217} [list \218 t1 / 2 leaf 2 993 5 1517 \219 t1 /000+000000 3 overflow 0 1020 0 0 \220 t1 /001+000000 4 overflow 0 1020 0 0 \221]222 223do_execsql_test stat-5.20 {224 SELECT name, quote(path), pageno, quote(pagetype), ncell, payload,225 unused, mx_payload, '|' FROM dbstat('main',1);226} {sqlite_schema NULL 1 NULL 1 34 878 34 | tx NULL 1 NULL 0 0 1016 0 |}227do_execsql_test stat-5.21 {228 SELECT name, quote(path), pageno, quote(pagetype), ncell, payload,229 unused, mx_payload, '|' FROM dbstat('aux1',1);230} {sqlite_schema NULL 1 NULL 1 34 878 34 | t1 NULL 3 NULL 2 3033 5 1517 |}231 232 233do_catchsql_test stat-6.1 {234 CREATE VIRTUAL TABLE temp.s2 USING dbstat(mainx);235} {1 {no such database: mainx}}236 237#-------------------------------------------------------------------------238# Test that the argument passed to the dbstat constructor is dequoted239# before it is matched against the names of attached databases.240#241forcedelete test.db2242do_execsql_test 7.1 {243 ATTACH 'test.db2' AS '123';244 PRAGMA "123".auto_vacuum = OFF;245 CREATE TABLE "123".x1(a, b);246 INSERT INTO x1 VALUES(1, 2);247}248 249do_execsql_test 7.1.1 {250 SELECT * FROM dbstat('123');251} {252 sqlite_schema / 1 leaf 1 37 875 37 0 1024 253 x1 / 2 leaf 1 4 1008 4 1024 1024254}255do_execsql_test 7.1.2 {256 SELECT * FROM dbstat(123);257} {258 sqlite_schema / 1 leaf 1 37 875 37 0 1024 259 x1 / 2 leaf 1 4 1008 4 1024 1024260}261do_execsql_test 7.1.3 {262 CREATE VIRTUAL TABLE x2 USING dbstat('123');263 SELECT * FROM x2;264} {265 sqlite_schema / 1 leaf 1 37 875 37 0 1024 266 x1 / 2 leaf 1 4 1008 4 1024 1024267}268do_execsql_test 7.1.4 {269 CREATE VIRTUAL TABLE x3 USING dbstat(123);270 SELECT * FROM x3;271} {272 sqlite_schema / 1 leaf 1 37 875 37 0 1024 273 x1 / 2 leaf 1 4 1008 4 1024 1024274}275 276do_execsql_test 7.2 {277 DETACH 123;278 DROP TABLE x2;279 DROP TABLE x3;280 ATTACH 'test.db2' AS '123corp';281}282do_execsql_test 7.2.1 {283 SELECT * FROM dbstat('123corp');284} {285 sqlite_schema / 1 leaf 1 37 875 37 0 1024 286 x1 / 2 leaf 1 4 1008 4 1024 1024287}288do_catchsql_test 7.2.2 {289 SELECT * FROM dbstat(123corp);290} {1 {unrecognized token: "123corp"}}291do_execsql_test 7.2.3 {292 CREATE VIRTUAL TABLE x2 USING dbstat('123corp');293 SELECT * FROM x2;294} {295 sqlite_schema / 1 leaf 1 37 875 37 0 1024 296 x1 / 2 leaf 1 4 1008 4 1024 1024297}298do_catchsql_test 7.2.4 {299 CREATE VIRTUAL TABLE x3 USING dbstat(123corp);300 SELECT * FROM x3;301} {1 {unrecognized token: "123corp"}}302 303 304do_execsql_test 8.1 {305 CREATE VIRTUAL TABLE st4 USING dbstat;306}307do_execsql_test 8.2 {308 SELECT * FROM st4 WHERE st4.aggregate = NULL;309}310do_execsql_test 8.3 {311 SELECT aggregate=1 FROM st4 WHERE aggregate = 5312}313do_execsql_test 8.4 {314 SELECT * FROM st4 WHERE name = NULL;315} {}316do_execsql_test 8.5 {317 SELECT * FROM st4 WHERE schema = NULL;318} {}319 320#-------------------------------------------------------------------------321reset_db322breakpoint323do_catchsql_test 9.1 {324 CREATE TABLE dbstat(x, y);325 DROP TABLE nosuchdb.dbstat;326} {/1 {(no such table: nosuchdb.dbstat|table dbstat may not be dropped)}/}327 328finish_test329