Debian Codegraph @

test/dbpage.test

raw
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