| 1 |
# 2017-10-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 |
# This file implements regression tests for SQLite library. The |
| 12 |
# focus of this file is testing the sqlite_dbpage virtual table. |
| 13 |
# |
| 14 |
|
| 15 |
set testdir [file dirname $argv0] |
| 16 |
source $testdir/tester.tcl |
| 17 |
set testprefix dbpage |
| 18 |
|
| 19 |
ifcapable !vtab||!compound { |
| 20 |
finish_test |
| 21 |
return |
| 22 |
} |
| 23 |
|
| 24 |
sqlite3_db_config db DEFENSIVE 0 |
| 25 |
do_test 100 { |
| 26 |
execsql { |
| 27 |
PRAGMA auto_vacuum=0; |
| 28 |
PRAGMA page_size=4096; |
| 29 |
PRAGMA journal_mode=WAL; |
| 30 |
} |
| 31 |
execsql { |
| 32 |
CREATE TABLE t1(a,b); |
| 33 |
WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100) |
| 34 |
INSERT INTO t1(a,b) SELECT x, printf('%d-x%.*c',x,x,'x') FROM c; |
| 35 |
PRAGMA integrity_check; |
| 36 |
} |
| 37 |
} {ok} |
| 38 |
do_execsql_test 110 { |
| 39 |
SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage('main') ORDER BY pgno; |
| 40 |
} {1 X'53514C6974' 2 X'0500000001' 3 X'0D0000004E' 4 X'0D00000016'} |
| 41 |
do_execsql_test 120 { |
| 42 |
SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=2; |
| 43 |
} {2 X'0500000001'} |
| 44 |
do_execsql_test 130 { |
| 45 |
SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=4; |
| 46 |
} {4 X'0D00000016'} |
| 47 |
do_execsql_test 140 { |
| 48 |
SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=5; |
| 49 |
} {} |
| 50 |
do_execsql_test 150 { |
| 51 |
SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=0; |
| 52 |
} {} |
| 53 |
do_execsql_test 160 { |
| 54 |
ATTACH ':memory:' AS aux1; |
| 55 |
PRAGMA aux1.page_size=4096; |
| 56 |
CREATE TABLE aux1.t2(a,b,c); |
| 57 |
INSERT INTO t2 VALUES(11,12,13); |
| 58 |
SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage('aux1'); |
| 59 |
} {1 X'53514C6974' 2 X'0D00000001'} |
| 60 |
do_execsql_test 170 { |
| 61 |
CREATE TABLE aux1.x3(x,y,z); |
| 62 |
INSERT INTO x3(x,y,z) VALUES(1,'main',1),(2,'aux1',1); |
| 63 |
SELECT pgno, schema, substr(data,1,6) |
| 64 |
FROM sqlite_dbpage, x3 |
| 65 |
WHERE sqlite_dbpage.schema=x3.y AND sqlite_dbpage.pgno=x3.z |
| 66 |
ORDER BY x3.x; |
| 67 |
} {1 main SQLite 1 aux1 SQLite} |
| 68 |
|
| 69 |
do_execsql_test 200 { |
| 70 |
CREATE TEMP TABLE saved_content(x); |
| 71 |
INSERT INTO saved_content(x) SELECT data FROM sqlite_dbpage WHERE pgno=4; |
| 72 |
UPDATE sqlite_dbpage SET data=zeroblob(4096) WHERE pgno=4; |
| 73 |
} {} |
| 74 |
do_catchsql_test 210 { |
| 75 |
PRAGMA integrity_check; |
| 76 |
} {1 {database disk image is malformed}} |
| 77 |
do_execsql_test 220 { |
| 78 |
SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage('main') ORDER BY pgno; |
| 79 |
} {1 X'53514C6974' 2 X'0500000001' 3 X'0D0000004E' 4 X'0000000000'} |
| 80 |
do_execsql_test 230 { |
| 81 |
UPDATE sqlite_dbpage SET data=(SELECT x FROM saved_content) WHERE pgno=4; |
| 82 |
} {} |
| 83 |
do_catchsql_test 230 { |
| 84 |
PRAGMA integrity_check; |
| 85 |
} {0 ok} |
| 86 |
do_execsql_test 240 { |
| 87 |
DELETE FROM saved_content; |
| 88 |
INSERT INTO saved_content(x) |
| 89 |
SELECT data FROM sqlite_dbpage WHERE schema='aux1' AND pgno=2; |
| 90 |
} {} |
| 91 |
do_execsql_test 241 { |
| 92 |
UPDATE sqlite_dbpage SET data=zeroblob(4096) WHERE pgno=2 AND schema='aux1'; |
| 93 |
} {} |
| 94 |
do_catchsql_test 250 { |
| 95 |
PRAGMA aux1.integrity_check; |
| 96 |
} {1 {database disk image is malformed}} |
| 97 |
do_execsql_test 260 { |
| 98 |
UPDATE sqlite_dbpage SET data=(SELECT x FROM saved_content) |
| 99 |
WHERE pgno=2 AND schema='aux1'; |
| 100 |
} {} |
| 101 |
do_catchsql_test 270 { |
| 102 |
PRAGMA aux1.integrity_check; |
| 103 |
} {0 ok} |
| 104 |
|
| 105 |
db close |
| 106 |
sqlite3 db :memory: |
| 107 |
do_execsql_test 300 { |
| 108 |
SELECT * FROM sqlite_temp_schema, sqlite_dbpage; |
| 109 |
} {} |
| 110 |
|
| 111 |
#------------------------------------------------------------------------- |
| 112 |
reset_db |
| 113 |
do_execsql_test 400 { |
| 114 |
ATTACH ':memory:' AS aux1; |
| 115 |
BEGIN; |
| 116 |
CREATE VIRTUAL TABLE aux1.t1 USING sqlite_dbpage; |
| 117 |
INSERT INTO t1 VALUES(17, NULL); |
| 118 |
COMMIT; |
| 119 |
} |
| 120 |
|
| 121 |
#------------------------------------------------------------------------- |
| 122 |
reset_db |
| 123 |
forcedelete test.db2 |
| 124 |
sqlite3 db2 test.db2 |
| 125 |
db2 eval { |
| 126 |
PRAGMA auto_vacuum=NONE; |
| 127 |
CREATE TABLE t1(x, y); |
| 128 |
} |
| 129 |
|
| 130 |
do_execsql_test 500 { |
| 131 |
PRAGMA auto_vacuum=NONE; |
| 132 |
CREATE TABLE x1(a); |
| 133 |
INSERT INTO x1 VALUES( hex(randomblob(2000)) ); |
| 134 |
INSERT INTO x1 VALUES( hex(randomblob(2000)) ); |
| 135 |
INSERT INTO x1 VALUES( hex(randomblob(2000)) ); |
| 136 |
INSERT INTO x1 VALUES( hex(randomblob(2000)) ); |
| 137 |
PRAGMA page_count; |
| 138 |
} {18} |
| 139 |
|
| 140 |
do_test 510 { |
| 141 |
db eval BEGIN |
| 142 |
db2 eval { PRAGMA page_count } { |
| 143 |
db eval { |
| 144 |
INSERT INTO sqlite_dbpage values($page_count, NULL); |
| 145 |
} |
| 146 |
} |
| 147 |
db2 eval { SELECT pgno, data FROM sqlite_dbpage } { |
| 148 |
db eval { |
| 149 |
INSERT INTO sqlite_dbpage values($pgno, $data); |
| 150 |
} |
| 151 |
} |
| 152 |
|
| 153 |
db eval COMMIT |
| 154 |
} {} |
| 155 |
|
| 156 |
db close |
| 157 |
sqlite3 db test.db |
| 158 |
|
| 159 |
do_execsql_test 520 { |
| 160 |
PRAGMA page_count; |
| 161 |
SELECT * FROM t1; |
| 162 |
} {2} |
| 163 |
|
| 164 |
db2 close |
| 165 |
|
| 166 |
#------------------------------------------------------------------------- |
| 167 |
reset_db |
| 168 |
forcedelete test.db2 |
| 169 |
do_execsql_test 610 { |
| 170 |
ATTACH 'test.db2' AS aux; |
| 171 |
CREATE TABLE t1(x); |
| 172 |
CREATE TABLE t2(y); |
| 173 |
INSERT INTO t1 VALUES(1234); |
| 174 |
CREATE TABLE aux.x1(z); |
| 175 |
} |
| 176 |
|
| 177 |
set pgno [db one {SELECT max(rootpage) FROM sqlite_schema}] |
| 178 |
sqlite3 db2 test.db2 |
| 179 |
db2 eval { |
| 180 |
BEGIN; |
| 181 |
SELECT * FROM x1; |
| 182 |
} |
| 183 |
|
| 184 |
do_catchsql_test 620 { |
| 185 |
UPDATE sqlite_dbpage SET data = ( |
| 186 |
SELECT data FROM sqlite_dbpage WHERE pgno=$pgno-1 |
| 187 |
) WHERE pgno = $pgno; |
| 188 |
} {1 {database is locked}} |
| 189 |
|
| 190 |
db2 eval { |
| 191 |
COMMIT; |
| 192 |
} |
| 193 |
|
| 194 |
do_catchsql_test 630 { |
| 195 |
UPDATE sqlite_dbpage SET data = ( |
| 196 |
SELECT data FROM sqlite_dbpage WHERE pgno=$pgno-1 |
| 197 |
) WHERE pgno = $pgno; |
| 198 |
} {0 {}} |
| 199 |
|
| 200 |
db close |
| 201 |
sqlite3 db test.db |
| 202 |
|
| 203 |
do_execsql_test 640 { |
| 204 |
SELECT * FROM t2; |
| 205 |
} {1234} |
| 206 |
|
| 207 |
db2 close |
| 208 |
|
| 209 |
#------------------------------------------------------------------------- |
| 210 |
reset_db |
| 211 |
do_execsql_test 700 { |
| 212 |
CREATE TABLE t1(x); |
| 213 |
INSERT INTO t1 VALUES( hex(randomblob(1000)) ); |
| 214 |
INSERT INTO t1 VALUES( hex(randomblob(1000)) ); |
| 215 |
INSERT INTO t1 VALUES( hex(randomblob(1000)) ); |
| 216 |
} |
| 217 |
|
| 218 |
forcedelete test.db2 |
| 219 |
sqlite3 db2 test.db2 |
| 220 |
db2 eval { |
| 221 |
CREATE TABLE y1(y); |
| 222 |
INSERT INTO y1 VALUES( hex(randomblob(1000)) ); |
| 223 |
} |
| 224 |
|
| 225 |
set max [db2 one {PRAGMA page_count}] |
| 226 |
|
| 227 |
do_test 710 { |
| 228 |
execsql { |
| 229 |
BEGIN; |
| 230 |
} |
| 231 |
|
| 232 |
for {set ii 1} {$ii <= $max} {incr ii} { |
| 233 |
set data [db2 one {SELECT data FROM sqlite_dbpage WHERE pgno=$ii}] |
| 234 |
execsql { |
| 235 |
UPDATE sqlite_dbpage SET data=$data WHERE pgno=$ii |
| 236 |
} |
| 237 |
} |
| 238 |
|
| 239 |
execsql { |
| 240 |
SAVEPOINT abc; |
| 241 |
INSERT INTO sqlite_dbpage VALUES(2, NULL); |
| 242 |
ROLLBACK TO abc; |
| 243 |
COMMIT; |
| 244 |
} |
| 245 |
} {} |
| 246 |
|
| 247 |
db close |
| 248 |
sqlite3 db test.db |
| 249 |
|
| 250 |
do_execsql_test 720 { |
| 251 |
PRAGMA integrity_check |
| 252 |
} {ok} |
| 253 |
|
| 254 |
#------------------------------------------------------------------------- |
| 255 |
reset_db |
| 256 |
do_execsql_test 800 { |
| 257 |
CREATE TABLE x1(x); |
| 258 |
} |
| 259 |
|
| 260 |
do_execsql_test 810 { |
| 261 |
SELECT pgno FROM sqlite_dbpage WHERE pgno=555 |
| 262 |
} {} |
| 263 |
|
| 264 |
do_execsql_test 820 { |
| 265 |
SELECT pgno FROM sqlite_dbpage WHERE pgno=4294967297 |
| 266 |
} {} |
| 267 |
|
| 268 |
|
| 269 |
finish_test |