Debian Codegraph @

test/temptable2.test

raw
1 # 2016 March 3
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 # TESTRUNNER: slow
13 #
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set testprefix temptable2
18
19 do_execsql_test 1.1 {
20 CREATE TEMP TABLE t1(a, b);
21 CREATE INDEX i1 ON t1(a, b);
22 }
23
24 do_execsql_test 1.2 {
25 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<100000 )
26 INSERT INTO t1 SELECT randomblob(100), randomblob(100) FROM X;
27 } {}
28
29 do_execsql_test 1.3 {
30 PRAGMA temp.integrity_check;
31 } {ok}
32
33 #-------------------------------------------------------------------------
34 #
35 reset_db
36 do_execsql_test 2.1 {
37 CREATE TEMP TABLE t2(a, b);
38 INSERT INTO t2 VALUES(1, 2);
39 } {}
40
41 do_execsql_test 2.2 {
42 BEGIN;
43 INSERT INTO t2 VALUES(3, 4);
44 SELECT * FROM t2;
45 } {1 2 3 4}
46
47 do_execsql_test 2.3 {
48 ROLLBACK;
49 SELECT * FROM t2;
50 } {1 2}
51
52 #-------------------------------------------------------------------------
53 #
54 reset_db
55 do_execsql_test 3.1.1 {
56 PRAGMA main.cache_size = 10;
57 PRAGMA temp.cache_size = 10;
58
59 CREATE TEMP TABLE t1(a, b);
60 CREATE INDEX i1 ON t1(a, b);
61
62 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<1000 )
63 INSERT INTO t1 SELECT randomblob(100), randomblob(100) FROM x;
64
65 SELECT count(*) FROM t1;
66 } {1000}
67 do_execsql_test 3.1.2 {
68 BEGIN;
69 UPDATE t1 SET b=randomblob(100) WHERE (rowid%10)==0;
70 ROLLBACK;
71 }
72 do_execsql_test 3.1.3 {
73 SELECT count(*) FROM t1;
74 } {1000}
75 do_execsql_test 3.1.4 { PRAGMA temp.integrity_check } {ok}
76
77 do_execsql_test 3.2.1 {
78 BEGIN;
79 UPDATE t1 SET b=randomblob(100) WHERE (rowid%10)==0;
80 SAVEPOINT abc;
81 UPDATE t1 SET b=randomblob(100) WHERE (rowid%10)==1;
82 ROLLBACK TO abc;
83 UPDATE t1 SET b=randomblob(100) WHERE (rowid%10)==2;
84 COMMIT;
85 }
86 do_execsql_test 3.2.2 { PRAGMA temp.integrity_check } {ok}
87
88 #-------------------------------------------------------------------------
89 #
90 reset_db
91 do_execsql_test 4.1.1 {
92 PRAGMA main.cache_size = 10;
93 PRAGMA temp.cache_size = 10;
94
95 CREATE TEMP TABLE t1(a, b);
96 CREATE INDEX i1 ON t1(a, b);
97
98 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<10 )
99 INSERT INTO t1 SELECT randomblob(100), randomblob(100) FROM x;
100
101 SELECT count(*) FROM t1;
102 PRAGMA temp.page_count;
103 } {10 9}
104
105 do_execsql_test 4.1.2 {
106 BEGIN;
107 UPDATE t1 SET b=randomblob(100);
108 ROLLBACK;
109 }
110
111 do_execsql_test 4.1.3 {
112 CREATE TEMP TABLE t2(a, b);
113 CREATE INDEX i2 ON t2(a, b);
114 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<500 )
115 INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM x;
116
117 SELECT count(*) FROM t2;
118 SELECT count(*) FROM t1;
119 } {500 10}
120
121 do_test 4.1.4 {
122 set n [db one { PRAGMA temp.page_count }]
123 expr ($n >280 && $n < 300)
124 } 1
125
126 do_execsql_test 4.1.4 { PRAGMA temp.integrity_check } {ok}
127
128 #-------------------------------------------------------------------------
129 #
130 reset_db
131 do_execsql_test 5.1.1 {
132 PRAGMA main.cache_size = 10;
133 PRAGMA temp.cache_size = 10;
134
135 CREATE TEMP TABLE t2(a, b);
136 CREATE INDEX i2 ON t2(a, b);
137 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<500 )
138 INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM x;
139
140 CREATE TEMP TABLE t1(a, b);
141 CREATE INDEX i1 ON t1(a, b);
142 INSERT INTO t1 VALUES(1, 2);
143 }
144
145 # Test that the temp database is now much bigger than the configured
146 # cache size (10 pages).
147 do_test 5.1.2 {
148 set n [db one { PRAGMA temp.page_count }]
149 expr ($n > 270 && $n < 290)
150 } {1}
151
152 do_execsql_test 5.1.3 {
153 BEGIN;
154 UPDATE t1 SET a=2;
155 UPDATE t2 SET a=randomblob(100);
156 SELECT count(*) FROM t1;
157 ROLLBACK;
158 } {1}
159
160 do_execsql_test 5.1.4 {
161 UPDATE t2 SET a=randomblob(100);
162
163 SELECT * FROM t1;
164 } {1 2}
165
166 do_execsql_test 5.1.5 { PRAGMA temp.integrity_check } {ok}
167
168 #-------------------------------------------------------------------------
169 # Test this:
170 #
171 # 1. Page is DIRTY at the start of a transaction.
172 # 2. Page is written out as part of the transaction.
173 # 3. Page is then read back in.
174 # 4. Transaction is rolled back. Is the page now clean or dirty?
175 #
176 # This actually does work. Step 4 marks the page as clean. But it also
177 # writes to the database file itself. So marking it clean is correct -
178 # the page does match the contents of the db file.
179 #
180 reset_db
181
182 do_execsql_test 6.1 {
183 PRAGMA main.cache_size = 10;
184 PRAGMA temp.cache_size = 10;
185
186 CREATE TEMP TABLE t1(x);
187 INSERT INTO t1 VALUES('one');
188
189 CREATE TEMP TABLE t2(a, b);
190 CREATE INDEX i2 ON t2(a, b);
191 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<500 )
192 INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM x;
193 }
194
195 do_execsql_test 6.2 {
196 UPDATE t1 SET x='two'; -- step 1
197 BEGIN;
198 UPDATE t2 SET a=randomblob(100); -- step 2
199 SELECT * FROM t1; -- step 3
200 ROLLBACK; -- step 4
201
202 SELECT count(*) FROM t2;
203 SELECT * FROM t1;
204 } {two 500 two}
205
206 #-------------------------------------------------------------------------
207 #
208 reset_db
209 sqlite3 db ""
210 do_execsql_test 7.1 {
211 PRAGMA auto_vacuum=INCREMENTAL;
212 CREATE TABLE t1(x);
213 INSERT INTO t1 VALUES(zeroblob(900));
214 INSERT INTO t1 VALUES(zeroblob(900));
215 INSERT INTO t1 SELECT x FROM t1;
216 INSERT INTO t1 SELECT x FROM t1;
217 INSERT INTO t1 SELECT x FROM t1;
218 INSERT INTO t1 SELECT x FROM t1;
219 BEGIN;
220 DELETE FROM t1 WHERE rowid%2;
221 PRAGMA incremental_vacuum(4);
222 ROLLBACK;
223 PRAGMA integrity_check;
224 } {ok}
225
226 #-------------------------------------------------------------------------
227 # Try changing the page size using a backup operation when pages are
228 # stored in main-memory only.
229 #
230 reset_db
231 do_execsql_test 8.1 {
232 PRAGMA auto_vacuum = OFF;
233 CREATE TABLE t2(a, b);
234 CREATE INDEX i2 ON t2(a, b);
235 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<20 )
236 INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM x ORDER BY 1, 2;
237 PRAGMA page_count;
238 } {13}
239
240 do_test 8.2 {
241 sqlite3 tmp ""
242 execsql {
243 PRAGMA auto_vacuum = OFF;
244 PRAGMA page_size = 8192;
245 CREATE TABLE t1(a, b);
246 CREATE INDEX i1 ON t1(a, b);
247 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<100 )
248 INSERT INTO t1 SELECT randomblob(100), randomblob(100) FROM x ORDER BY 1, 2;
249 PRAGMA page_count;
250 } tmp
251 } {10}
252
253 do_test 8.3 {
254 sqlite3_backup B tmp main db main
255 B step 5
256 B finish
257 } {SQLITE_READONLY}
258
259 do_test 8.4 {
260 execsql {
261 SELECT count(*) FROM t1;
262 PRAGMA integrity_check;
263 PRAGMA page_size;
264 } tmp
265 } {100 ok 8192}
266
267 do_test 8.5 {
268 tmp eval { UPDATE t1 SET a=randomblob(100) }
269 } {}
270
271 do_test 8.6 {
272 sqlite3_backup B tmp main db main
273 B step 1000
274 B finish
275 } {SQLITE_READONLY}
276
277 tmp close
278
279 #-------------------------------------------------------------------------
280 # Try inserts and deletes with a large db in auto-vacuum mode. Check
281 #
282 foreach {tn mode} {
283 1 delete
284 2 wal
285 } {
286 reset_db
287 sqlite3 db ""
288 do_execsql_test 9.$tn.1.1 {
289 PRAGMA cache_size = 15;
290 PRAGMA auto_vacuum = 1;
291 }
292 execsql "PRAGMA journal_mode = $mode"
293
294 do_execsql_test 9.$tn.1.2 {
295 CREATE TABLE tx(a, b);
296 CREATE INDEX i1 ON tx(a);
297 CREATE INDEX i2 ON tx(b);
298 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<1000 )
299 INSERT INTO tx SELECT randomblob(100), randomblob(100) FROM x;
300 }
301
302 for {set i 2} {$i<20} {incr i} {
303 do_execsql_test 9.$tn.$i.1 { DELETE FROM tx WHERE (random()%3)==0 }
304
305 do_execsql_test 9.$tn.$i.2 { PRAGMA integrity_check } ok
306
307 do_execsql_test 9.$tn.$i.3 {
308 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<400 )
309 INSERT INTO tx SELECT randomblob(100), randomblob(100) FROM x;
310 }
311
312 do_execsql_test 9.$tn.$i.4 { PRAGMA integrity_check } ok
313
314 do_execsql_test 9.$tn.$i.5 {
315 BEGIN;
316 DELETE FROM tx WHERE (random()%3)==0;
317 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<500 )
318 INSERT INTO tx SELECT randomblob(100), randomblob(100) FROM x;
319 COMMIT;
320 }
321
322 do_execsql_test 9.$tn.$i.6 { PRAGMA integrity_check } ok
323 }
324 }
325
326 #-------------------------------------------------------------------------
327 # When using mmap mode with a temp file, SQLite must search the cache
328 # before using a mapped page even when there is no write transaction
329 # open. For a temp file, the on-disk version may not be up to date.
330 #
331 sqlite3 db ""
332 do_execsql_test 10.0 {
333 PRAGMA cache_size = 50;
334 PRAGMA page_size = 1024;
335 CREATE TABLE t1(a, b, PRIMARY KEY(a)) WITHOUT ROWID;
336 CREATE INDEX i1 ON t1(a);
337 CREATE TABLE t2(x, y);
338 INSERT INTO t2 VALUES(1, 2);
339 }
340
341 do_execsql_test 10.1 {
342 BEGIN;
343 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<500 )
344 INSERT INTO t1 SELECT randomblob(100), randomblob(100) FROM x;
345 COMMIT;
346 INSERT INTO t2 VALUES(3, 4);
347 }
348
349 ifcapable mmap {
350 if {[permutation]!="journaltest" && $::TEMP_STORE<2} {
351 # The journaltest permutation does not support mmap, so this part of
352 # the test is omitted.
353 do_execsql_test 10.2 { PRAGMA mmap_size = 512000 } 512000
354 }
355 }
356
357 do_execsql_test 10.3 { SELECT * FROM t2 } {1 2 3 4}
358 do_execsql_test 10.4 { PRAGMA integrity_check } ok
359
360 finish_test