wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2001 September 15 |
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. This |
||
12 | # file is a copy of "trans.test" modified to run under autovacuum mode. |
||
13 | # the point is to stress the autovacuum logic and try to get it to fail. |
||
14 | # |
||
15 | # $Id: avtrans.test,v 1.6 2007/09/12 17:01:45 danielk1977 Exp $ |
||
16 | |||
17 | |||
18 | set testdir [file dirname $argv0] |
||
19 | source $testdir/tester.tcl |
||
20 | |||
21 | |||
22 | # Create several tables to work with. |
||
23 | # |
||
24 | do_test avtrans-1.0 { |
||
25 | execsql { PRAGMA auto_vacuum=ON } |
||
26 | wal_set_journal_mode |
||
27 | execsql { |
||
28 | CREATE TABLE one(a int PRIMARY KEY, b text); |
||
29 | INSERT INTO one VALUES(1,'one'); |
||
30 | INSERT INTO one VALUES(2,'two'); |
||
31 | INSERT INTO one VALUES(3,'three'); |
||
32 | SELECT b FROM one ORDER BY a; |
||
33 | } |
||
34 | } {one two three} |
||
35 | do_test avtrans-1.1 { |
||
36 | execsql { |
||
37 | CREATE TABLE two(a int PRIMARY KEY, b text); |
||
38 | INSERT INTO two VALUES(1,'I'); |
||
39 | INSERT INTO two VALUES(5,'V'); |
||
40 | INSERT INTO two VALUES(10,'X'); |
||
41 | SELECT b FROM two ORDER BY a; |
||
42 | } |
||
43 | } {I V X} |
||
44 | do_test avtrans-1.9 { |
||
45 | sqlite3 altdb test.db |
||
46 | execsql {SELECT b FROM one ORDER BY a} altdb |
||
47 | } {one two three} |
||
48 | do_test avtrans-1.10 { |
||
49 | execsql {SELECT b FROM two ORDER BY a} altdb |
||
50 | } {I V X} |
||
51 | integrity_check avtrans-1.11 |
||
52 | wal_check_journal_mode avtrans-1.12 |
||
53 | |||
54 | # Basic transactions |
||
55 | # |
||
56 | do_test avtrans-2.1 { |
||
57 | set v [catch {execsql {BEGIN}} msg] |
||
58 | lappend v $msg |
||
59 | } {0 {}} |
||
60 | do_test avtrans-2.2 { |
||
61 | set v [catch {execsql {END}} msg] |
||
62 | lappend v $msg |
||
63 | } {0 {}} |
||
64 | do_test avtrans-2.3 { |
||
65 | set v [catch {execsql {BEGIN TRANSACTION}} msg] |
||
66 | lappend v $msg |
||
67 | } {0 {}} |
||
68 | do_test avtrans-2.4 { |
||
69 | set v [catch {execsql {COMMIT TRANSACTION}} msg] |
||
70 | lappend v $msg |
||
71 | } {0 {}} |
||
72 | do_test avtrans-2.5 { |
||
73 | set v [catch {execsql {BEGIN TRANSACTION 'foo'}} msg] |
||
74 | lappend v $msg |
||
75 | } {0 {}} |
||
76 | do_test avtrans-2.6 { |
||
77 | set v [catch {execsql {ROLLBACK TRANSACTION 'foo'}} msg] |
||
78 | lappend v $msg |
||
79 | } {0 {}} |
||
80 | do_test avtrans-2.10 { |
||
81 | execsql { |
||
82 | BEGIN; |
||
83 | SELECT a FROM one ORDER BY a; |
||
84 | SELECT a FROM two ORDER BY a; |
||
85 | END; |
||
86 | } |
||
87 | } {1 2 3 1 5 10} |
||
88 | integrity_check avtrans-2.11 |
||
89 | wal_check_journal_mode avtrans-2.12 |
||
90 | |||
91 | # Check the locking behavior |
||
92 | # |
||
93 | sqlite3_soft_heap_limit 0 |
||
94 | do_test avtrans-3.1 { |
||
95 | execsql { |
||
96 | BEGIN; |
||
97 | UPDATE one SET a = 0 WHERE 0; |
||
98 | SELECT a FROM one ORDER BY a; |
||
99 | } |
||
100 | } {1 2 3} |
||
101 | do_test avtrans-3.2 { |
||
102 | catchsql { |
||
103 | SELECT a FROM two ORDER BY a; |
||
104 | } altdb |
||
105 | } {0 {1 5 10}} |
||
106 | do_test avtrans-3.3 { |
||
107 | catchsql { |
||
108 | SELECT a FROM one ORDER BY a; |
||
109 | } altdb |
||
110 | } {0 {1 2 3}} |
||
111 | do_test avtrans-3.4 { |
||
112 | catchsql { |
||
113 | INSERT INTO one VALUES(4,'four'); |
||
114 | } |
||
115 | } {0 {}} |
||
116 | do_test avtrans-3.5 { |
||
117 | catchsql { |
||
118 | SELECT a FROM two ORDER BY a; |
||
119 | } altdb |
||
120 | } {0 {1 5 10}} |
||
121 | do_test avtrans-3.6 { |
||
122 | catchsql { |
||
123 | SELECT a FROM one ORDER BY a; |
||
124 | } altdb |
||
125 | } {0 {1 2 3}} |
||
126 | do_test avtrans-3.7 { |
||
127 | catchsql { |
||
128 | INSERT INTO two VALUES(4,'IV'); |
||
129 | } |
||
130 | } {0 {}} |
||
131 | do_test avtrans-3.8 { |
||
132 | catchsql { |
||
133 | SELECT a FROM two ORDER BY a; |
||
134 | } altdb |
||
135 | } {0 {1 5 10}} |
||
136 | do_test avtrans-3.9 { |
||
137 | catchsql { |
||
138 | SELECT a FROM one ORDER BY a; |
||
139 | } altdb |
||
140 | } {0 {1 2 3}} |
||
141 | do_test avtrans-3.10 { |
||
142 | execsql {END TRANSACTION} |
||
143 | } {} |
||
144 | do_test avtrans-3.11 { |
||
145 | set v [catch {execsql { |
||
146 | SELECT a FROM two ORDER BY a; |
||
147 | } altdb} msg] |
||
148 | lappend v $msg |
||
149 | } {0 {1 4 5 10}} |
||
150 | do_test avtrans-3.12 { |
||
151 | set v [catch {execsql { |
||
152 | SELECT a FROM one ORDER BY a; |
||
153 | } altdb} msg] |
||
154 | lappend v $msg |
||
155 | } {0 {1 2 3 4}} |
||
156 | do_test avtrans-3.13 { |
||
157 | set v [catch {execsql { |
||
158 | SELECT a FROM two ORDER BY a; |
||
159 | } db} msg] |
||
160 | lappend v $msg |
||
161 | } {0 {1 4 5 10}} |
||
162 | do_test avtrans-3.14 { |
||
163 | set v [catch {execsql { |
||
164 | SELECT a FROM one ORDER BY a; |
||
165 | } db} msg] |
||
166 | lappend v $msg |
||
167 | } {0 {1 2 3 4}} |
||
168 | sqlite3_soft_heap_limit $cmdlinearg(soft-heap-limit) |
||
169 | integrity_check avtrans-3.15 |
||
170 | |||
171 | do_test avtrans-4.1 { |
||
172 | set v [catch {execsql { |
||
173 | COMMIT; |
||
174 | } db} msg] |
||
175 | lappend v $msg |
||
176 | } {1 {cannot commit - no transaction is active}} |
||
177 | do_test avtrans-4.2 { |
||
178 | set v [catch {execsql { |
||
179 | ROLLBACK; |
||
180 | } db} msg] |
||
181 | lappend v $msg |
||
182 | } {1 {cannot rollback - no transaction is active}} |
||
183 | do_test avtrans-4.3 { |
||
184 | catchsql { |
||
185 | BEGIN TRANSACTION; |
||
186 | UPDATE two SET a = 0 WHERE 0; |
||
187 | SELECT a FROM two ORDER BY a; |
||
188 | } db |
||
189 | } {0 {1 4 5 10}} |
||
190 | do_test avtrans-4.4 { |
||
191 | catchsql { |
||
192 | SELECT a FROM two ORDER BY a; |
||
193 | } altdb |
||
194 | } {0 {1 4 5 10}} |
||
195 | do_test avtrans-4.5 { |
||
196 | catchsql { |
||
197 | SELECT a FROM one ORDER BY a; |
||
198 | } altdb |
||
199 | } {0 {1 2 3 4}} |
||
200 | do_test avtrans-4.6 { |
||
201 | catchsql { |
||
202 | BEGIN TRANSACTION; |
||
203 | SELECT a FROM one ORDER BY a; |
||
204 | } db |
||
205 | } {1 {cannot start a transaction within a transaction}} |
||
206 | do_test avtrans-4.7 { |
||
207 | catchsql { |
||
208 | SELECT a FROM two ORDER BY a; |
||
209 | } altdb |
||
210 | } {0 {1 4 5 10}} |
||
211 | do_test avtrans-4.8 { |
||
212 | catchsql { |
||
213 | SELECT a FROM one ORDER BY a; |
||
214 | } altdb |
||
215 | } {0 {1 2 3 4}} |
||
216 | do_test avtrans-4.9 { |
||
217 | set v [catch {execsql { |
||
218 | END TRANSACTION; |
||
219 | SELECT a FROM two ORDER BY a; |
||
220 | } db} msg] |
||
221 | lappend v $msg |
||
222 | } {0 {1 4 5 10}} |
||
223 | do_test avtrans-4.10 { |
||
224 | set v [catch {execsql { |
||
225 | SELECT a FROM two ORDER BY a; |
||
226 | } altdb} msg] |
||
227 | lappend v $msg |
||
228 | } {0 {1 4 5 10}} |
||
229 | do_test avtrans-4.11 { |
||
230 | set v [catch {execsql { |
||
231 | SELECT a FROM one ORDER BY a; |
||
232 | } altdb} msg] |
||
233 | lappend v $msg |
||
234 | } {0 {1 2 3 4}} |
||
235 | integrity_check avtrans-4.12 |
||
236 | do_test avtrans-4.98 { |
||
237 | altdb close |
||
238 | execsql { |
||
239 | DROP TABLE one; |
||
240 | DROP TABLE two; |
||
241 | } |
||
242 | } {} |
||
243 | integrity_check avtrans-4.99 |
||
244 | |||
245 | # Check out the commit/rollback behavior of the database |
||
246 | # |
||
247 | do_test avtrans-5.1 { |
||
248 | execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} |
||
249 | } {} |
||
250 | do_test avtrans-5.2 { |
||
251 | execsql {BEGIN TRANSACTION} |
||
252 | execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} |
||
253 | } {} |
||
254 | do_test avtrans-5.3 { |
||
255 | execsql {CREATE TABLE one(a text, b int)} |
||
256 | execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} |
||
257 | } {one} |
||
258 | do_test avtrans-5.4 { |
||
259 | execsql {SELECT a,b FROM one ORDER BY b} |
||
260 | } {} |
||
261 | do_test avtrans-5.5 { |
||
262 | execsql {INSERT INTO one(a,b) VALUES('hello', 1)} |
||
263 | execsql {SELECT a,b FROM one ORDER BY b} |
||
264 | } {hello 1} |
||
265 | do_test avtrans-5.6 { |
||
266 | execsql {ROLLBACK} |
||
267 | execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} |
||
268 | } {} |
||
269 | do_test avtrans-5.7 { |
||
270 | set v [catch { |
||
271 | execsql {SELECT a,b FROM one ORDER BY b} |
||
272 | } msg] |
||
273 | lappend v $msg |
||
274 | } {1 {no such table: one}} |
||
275 | |||
276 | # Test commits and rollbacks of table CREATE TABLEs, CREATE INDEXs |
||
277 | # DROP TABLEs and DROP INDEXs |
||
278 | # |
||
279 | do_test avtrans-5.8 { |
||
280 | execsql { |
||
281 | SELECT name fROM sqlite_master |
||
282 | WHERE type='table' OR type='index' |
||
283 | ORDER BY name |
||
284 | } |
||
285 | } {} |
||
286 | do_test avtrans-5.9 { |
||
287 | execsql { |
||
288 | BEGIN TRANSACTION; |
||
289 | CREATE TABLE t1(a int, b int, c int); |
||
290 | SELECT name fROM sqlite_master |
||
291 | WHERE type='table' OR type='index' |
||
292 | ORDER BY name; |
||
293 | } |
||
294 | } {t1} |
||
295 | do_test avtrans-5.10 { |
||
296 | execsql { |
||
297 | CREATE INDEX i1 ON t1(a); |
||
298 | SELECT name fROM sqlite_master |
||
299 | WHERE type='table' OR type='index' |
||
300 | ORDER BY name; |
||
301 | } |
||
302 | } {i1 t1} |
||
303 | do_test avtrans-5.11 { |
||
304 | execsql { |
||
305 | COMMIT; |
||
306 | SELECT name fROM sqlite_master |
||
307 | WHERE type='table' OR type='index' |
||
308 | ORDER BY name; |
||
309 | } |
||
310 | } {i1 t1} |
||
311 | do_test avtrans-5.12 { |
||
312 | execsql { |
||
313 | BEGIN TRANSACTION; |
||
314 | CREATE TABLE t2(a int, b int, c int); |
||
315 | CREATE INDEX i2a ON t2(a); |
||
316 | CREATE INDEX i2b ON t2(b); |
||
317 | DROP TABLE t1; |
||
318 | SELECT name fROM sqlite_master |
||
319 | WHERE type='table' OR type='index' |
||
320 | ORDER BY name; |
||
321 | } |
||
322 | } {i2a i2b t2} |
||
323 | do_test avtrans-5.13 { |
||
324 | execsql { |
||
325 | ROLLBACK; |
||
326 | SELECT name fROM sqlite_master |
||
327 | WHERE type='table' OR type='index' |
||
328 | ORDER BY name; |
||
329 | } |
||
330 | } {i1 t1} |
||
331 | do_test avtrans-5.14 { |
||
332 | execsql { |
||
333 | BEGIN TRANSACTION; |
||
334 | DROP INDEX i1; |
||
335 | SELECT name fROM sqlite_master |
||
336 | WHERE type='table' OR type='index' |
||
337 | ORDER BY name; |
||
338 | } |
||
339 | } {t1} |
||
340 | do_test avtrans-5.15 { |
||
341 | execsql { |
||
342 | ROLLBACK; |
||
343 | SELECT name fROM sqlite_master |
||
344 | WHERE type='table' OR type='index' |
||
345 | ORDER BY name; |
||
346 | } |
||
347 | } {i1 t1} |
||
348 | do_test avtrans-5.16 { |
||
349 | execsql { |
||
350 | BEGIN TRANSACTION; |
||
351 | DROP INDEX i1; |
||
352 | CREATE TABLE t2(x int, y int, z int); |
||
353 | CREATE INDEX i2x ON t2(x); |
||
354 | CREATE INDEX i2y ON t2(y); |
||
355 | INSERT INTO t2 VALUES(1,2,3); |
||
356 | SELECT name fROM sqlite_master |
||
357 | WHERE type='table' OR type='index' |
||
358 | ORDER BY name; |
||
359 | } |
||
360 | } {i2x i2y t1 t2} |
||
361 | do_test avtrans-5.17 { |
||
362 | execsql { |
||
363 | COMMIT; |
||
364 | SELECT name fROM sqlite_master |
||
365 | WHERE type='table' OR type='index' |
||
366 | ORDER BY name; |
||
367 | } |
||
368 | } {i2x i2y t1 t2} |
||
369 | do_test avtrans-5.18 { |
||
370 | execsql { |
||
371 | SELECT * FROM t2; |
||
372 | } |
||
373 | } {1 2 3} |
||
374 | do_test avtrans-5.19 { |
||
375 | execsql { |
||
376 | SELECT x FROM t2 WHERE y=2; |
||
377 | } |
||
378 | } {1} |
||
379 | do_test avtrans-5.20 { |
||
380 | execsql { |
||
381 | BEGIN TRANSACTION; |
||
382 | DROP TABLE t1; |
||
383 | DROP TABLE t2; |
||
384 | SELECT name fROM sqlite_master |
||
385 | WHERE type='table' OR type='index' |
||
386 | ORDER BY name; |
||
387 | } |
||
388 | } {} |
||
389 | do_test avtrans-5.21 { |
||
390 | set r [catch {execsql { |
||
391 | SELECT * FROM t2 |
||
392 | }} msg] |
||
393 | lappend r $msg |
||
394 | } {1 {no such table: t2}} |
||
395 | do_test avtrans-5.22 { |
||
396 | execsql { |
||
397 | ROLLBACK; |
||
398 | SELECT name fROM sqlite_master |
||
399 | WHERE type='table' OR type='index' |
||
400 | ORDER BY name; |
||
401 | } |
||
402 | } {i2x i2y t1 t2} |
||
403 | do_test avtrans-5.23 { |
||
404 | execsql { |
||
405 | SELECT * FROM t2; |
||
406 | } |
||
407 | } {1 2 3} |
||
408 | integrity_check avtrans-5.23 |
||
409 | |||
410 | |||
411 | # Try to DROP and CREATE tables and indices with the same name |
||
412 | # within a transaction. Make sure ROLLBACK works. |
||
413 | # |
||
414 | do_test avtrans-6.1 { |
||
415 | execsql2 { |
||
416 | INSERT INTO t1 VALUES(1,2,3); |
||
417 | BEGIN TRANSACTION; |
||
418 | DROP TABLE t1; |
||
419 | CREATE TABLE t1(p,q,r); |
||
420 | ROLLBACK; |
||
421 | SELECT * FROM t1; |
||
422 | } |
||
423 | } {a 1 b 2 c 3} |
||
424 | do_test avtrans-6.2 { |
||
425 | execsql2 { |
||
426 | INSERT INTO t1 VALUES(1,2,3); |
||
427 | BEGIN TRANSACTION; |
||
428 | DROP TABLE t1; |
||
429 | CREATE TABLE t1(p,q,r); |
||
430 | COMMIT; |
||
431 | SELECT * FROM t1; |
||
432 | } |
||
433 | } {} |
||
434 | do_test avtrans-6.3 { |
||
435 | execsql2 { |
||
436 | INSERT INTO t1 VALUES(1,2,3); |
||
437 | SELECT * FROM t1; |
||
438 | } |
||
439 | } {p 1 q 2 r 3} |
||
440 | do_test avtrans-6.4 { |
||
441 | execsql2 { |
||
442 | BEGIN TRANSACTION; |
||
443 | DROP TABLE t1; |
||
444 | CREATE TABLE t1(a,b,c); |
||
445 | INSERT INTO t1 VALUES(4,5,6); |
||
446 | SELECT * FROM t1; |
||
447 | DROP TABLE t1; |
||
448 | } |
||
449 | } {a 4 b 5 c 6} |
||
450 | do_test avtrans-6.5 { |
||
451 | execsql2 { |
||
452 | ROLLBACK; |
||
453 | SELECT * FROM t1; |
||
454 | } |
||
455 | } {p 1 q 2 r 3} |
||
456 | do_test avtrans-6.6 { |
||
457 | execsql2 { |
||
458 | BEGIN TRANSACTION; |
||
459 | DROP TABLE t1; |
||
460 | CREATE TABLE t1(a,b,c); |
||
461 | INSERT INTO t1 VALUES(4,5,6); |
||
462 | SELECT * FROM t1; |
||
463 | DROP TABLE t1; |
||
464 | } |
||
465 | } {a 4 b 5 c 6} |
||
466 | do_test avtrans-6.7 { |
||
467 | catchsql { |
||
468 | COMMIT; |
||
469 | SELECT * FROM t1; |
||
470 | } |
||
471 | } {1 {no such table: t1}} |
||
472 | |||
473 | # Repeat on a table with an automatically generated index. |
||
474 | # |
||
475 | do_test avtrans-6.10 { |
||
476 | execsql2 { |
||
477 | CREATE TABLE t1(a unique,b,c); |
||
478 | INSERT INTO t1 VALUES(1,2,3); |
||
479 | BEGIN TRANSACTION; |
||
480 | DROP TABLE t1; |
||
481 | CREATE TABLE t1(p unique,q,r); |
||
482 | ROLLBACK; |
||
483 | SELECT * FROM t1; |
||
484 | } |
||
485 | } {a 1 b 2 c 3} |
||
486 | do_test avtrans-6.11 { |
||
487 | execsql2 { |
||
488 | BEGIN TRANSACTION; |
||
489 | DROP TABLE t1; |
||
490 | CREATE TABLE t1(p unique,q,r); |
||
491 | COMMIT; |
||
492 | SELECT * FROM t1; |
||
493 | } |
||
494 | } {} |
||
495 | do_test avtrans-6.12 { |
||
496 | execsql2 { |
||
497 | INSERT INTO t1 VALUES(1,2,3); |
||
498 | SELECT * FROM t1; |
||
499 | } |
||
500 | } {p 1 q 2 r 3} |
||
501 | do_test avtrans-6.13 { |
||
502 | execsql2 { |
||
503 | BEGIN TRANSACTION; |
||
504 | DROP TABLE t1; |
||
505 | CREATE TABLE t1(a unique,b,c); |
||
506 | INSERT INTO t1 VALUES(4,5,6); |
||
507 | SELECT * FROM t1; |
||
508 | DROP TABLE t1; |
||
509 | } |
||
510 | } {a 4 b 5 c 6} |
||
511 | do_test avtrans-6.14 { |
||
512 | execsql2 { |
||
513 | ROLLBACK; |
||
514 | SELECT * FROM t1; |
||
515 | } |
||
516 | } {p 1 q 2 r 3} |
||
517 | do_test avtrans-6.15 { |
||
518 | execsql2 { |
||
519 | BEGIN TRANSACTION; |
||
520 | DROP TABLE t1; |
||
521 | CREATE TABLE t1(a unique,b,c); |
||
522 | INSERT INTO t1 VALUES(4,5,6); |
||
523 | SELECT * FROM t1; |
||
524 | DROP TABLE t1; |
||
525 | } |
||
526 | } {a 4 b 5 c 6} |
||
527 | do_test avtrans-6.16 { |
||
528 | catchsql { |
||
529 | COMMIT; |
||
530 | SELECT * FROM t1; |
||
531 | } |
||
532 | } {1 {no such table: t1}} |
||
533 | |||
534 | do_test avtrans-6.20 { |
||
535 | execsql { |
||
536 | CREATE TABLE t1(a integer primary key,b,c); |
||
537 | INSERT INTO t1 VALUES(1,-2,-3); |
||
538 | INSERT INTO t1 VALUES(4,-5,-6); |
||
539 | SELECT * FROM t1; |
||
540 | } |
||
541 | } {1 -2 -3 4 -5 -6} |
||
542 | do_test avtrans-6.21 { |
||
543 | execsql { |
||
544 | CREATE INDEX i1 ON t1(b); |
||
545 | SELECT * FROM t1 WHERE b<1; |
||
546 | } |
||
547 | } {4 -5 -6 1 -2 -3} |
||
548 | do_test avtrans-6.22 { |
||
549 | execsql { |
||
550 | BEGIN TRANSACTION; |
||
551 | DROP INDEX i1; |
||
552 | SELECT * FROM t1 WHERE b<1; |
||
553 | ROLLBACK; |
||
554 | } |
||
555 | } {1 -2 -3 4 -5 -6} |
||
556 | do_test avtrans-6.23 { |
||
557 | execsql { |
||
558 | SELECT * FROM t1 WHERE b<1; |
||
559 | } |
||
560 | } {4 -5 -6 1 -2 -3} |
||
561 | do_test avtrans-6.24 { |
||
562 | execsql { |
||
563 | BEGIN TRANSACTION; |
||
564 | DROP TABLE t1; |
||
565 | ROLLBACK; |
||
566 | SELECT * FROM t1 WHERE b<1; |
||
567 | } |
||
568 | } {4 -5 -6 1 -2 -3} |
||
569 | |||
570 | do_test avtrans-6.25 { |
||
571 | execsql { |
||
572 | BEGIN TRANSACTION; |
||
573 | DROP INDEX i1; |
||
574 | CREATE INDEX i1 ON t1(c); |
||
575 | SELECT * FROM t1 WHERE b<1; |
||
576 | } |
||
577 | } {1 -2 -3 4 -5 -6} |
||
578 | do_test avtrans-6.26 { |
||
579 | execsql { |
||
580 | SELECT * FROM t1 WHERE c<1; |
||
581 | } |
||
582 | } {4 -5 -6 1 -2 -3} |
||
583 | do_test avtrans-6.27 { |
||
584 | execsql { |
||
585 | ROLLBACK; |
||
586 | SELECT * FROM t1 WHERE b<1; |
||
587 | } |
||
588 | } {4 -5 -6 1 -2 -3} |
||
589 | do_test avtrans-6.28 { |
||
590 | execsql { |
||
591 | SELECT * FROM t1 WHERE c<1; |
||
592 | } |
||
593 | } {1 -2 -3 4 -5 -6} |
||
594 | |||
595 | # The following repeats steps 6.20 through 6.28, but puts a "unique" |
||
596 | # constraint the first field of the table in order to generate an |
||
597 | # automatic index. |
||
598 | # |
||
599 | do_test avtrans-6.30 { |
||
600 | execsql { |
||
601 | BEGIN TRANSACTION; |
||
602 | DROP TABLE t1; |
||
603 | CREATE TABLE t1(a int unique,b,c); |
||
604 | COMMIT; |
||
605 | INSERT INTO t1 VALUES(1,-2,-3); |
||
606 | INSERT INTO t1 VALUES(4,-5,-6); |
||
607 | SELECT * FROM t1 ORDER BY a; |
||
608 | } |
||
609 | } {1 -2 -3 4 -5 -6} |
||
610 | do_test avtrans-6.31 { |
||
611 | execsql { |
||
612 | CREATE INDEX i1 ON t1(b); |
||
613 | SELECT * FROM t1 WHERE b<1; |
||
614 | } |
||
615 | } {4 -5 -6 1 -2 -3} |
||
616 | do_test avtrans-6.32 { |
||
617 | execsql { |
||
618 | BEGIN TRANSACTION; |
||
619 | DROP INDEX i1; |
||
620 | SELECT * FROM t1 WHERE b<1; |
||
621 | ROLLBACK; |
||
622 | } |
||
623 | } {1 -2 -3 4 -5 -6} |
||
624 | do_test avtrans-6.33 { |
||
625 | execsql { |
||
626 | SELECT * FROM t1 WHERE b<1; |
||
627 | } |
||
628 | } {4 -5 -6 1 -2 -3} |
||
629 | do_test avtrans-6.34 { |
||
630 | execsql { |
||
631 | BEGIN TRANSACTION; |
||
632 | DROP TABLE t1; |
||
633 | ROLLBACK; |
||
634 | SELECT * FROM t1 WHERE b<1; |
||
635 | } |
||
636 | } {4 -5 -6 1 -2 -3} |
||
637 | |||
638 | do_test avtrans-6.35 { |
||
639 | execsql { |
||
640 | BEGIN TRANSACTION; |
||
641 | DROP INDEX i1; |
||
642 | CREATE INDEX i1 ON t1(c); |
||
643 | SELECT * FROM t1 WHERE b<1; |
||
644 | } |
||
645 | } {1 -2 -3 4 -5 -6} |
||
646 | do_test avtrans-6.36 { |
||
647 | execsql { |
||
648 | SELECT * FROM t1 WHERE c<1; |
||
649 | } |
||
650 | } {4 -5 -6 1 -2 -3} |
||
651 | do_test avtrans-6.37 { |
||
652 | execsql { |
||
653 | DROP INDEX i1; |
||
654 | SELECT * FROM t1 WHERE c<1; |
||
655 | } |
||
656 | } {1 -2 -3 4 -5 -6} |
||
657 | do_test avtrans-6.38 { |
||
658 | execsql { |
||
659 | ROLLBACK; |
||
660 | SELECT * FROM t1 WHERE b<1; |
||
661 | } |
||
662 | } {4 -5 -6 1 -2 -3} |
||
663 | do_test avtrans-6.39 { |
||
664 | execsql { |
||
665 | SELECT * FROM t1 WHERE c<1; |
||
666 | } |
||
667 | } {1 -2 -3 4 -5 -6} |
||
668 | integrity_check avtrans-6.40 |
||
669 | |||
670 | ifcapable !floatingpoint { |
||
671 | finish_test |
||
672 | return |
||
673 | } |
||
674 | |||
675 | # Test to make sure rollback restores the database back to its original |
||
676 | # state. |
||
677 | # |
||
678 | do_test avtrans-7.1 { |
||
679 | execsql {BEGIN} |
||
680 | for {set i 0} {$i<1000} {incr i} { |
||
681 | set r1 [expr {rand()}] |
||
682 | set r2 [expr {rand()}] |
||
683 | set r3 [expr {rand()}] |
||
684 | execsql "INSERT INTO t2 VALUES($r1,$r2,$r3)" |
||
685 | } |
||
686 | execsql {COMMIT} |
||
687 | set ::checksum [execsql {SELECT md5sum(x,y,z) FROM t2}] |
||
688 | set ::checksum2 [ |
||
689 | execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master} |
||
690 | ] |
||
691 | execsql {SELECT count(*) FROM t2} |
||
692 | } {1001} |
||
693 | do_test avtrans-7.2 { |
||
694 | execsql {SELECT md5sum(x,y,z) FROM t2} |
||
695 | } $checksum |
||
696 | do_test avtrans-7.2.1 { |
||
697 | execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master} |
||
698 | } $checksum2 |
||
699 | do_test avtrans-7.3 { |
||
700 | execsql { |
||
701 | BEGIN; |
||
702 | DELETE FROM t2; |
||
703 | ROLLBACK; |
||
704 | SELECT md5sum(x,y,z) FROM t2; |
||
705 | } |
||
706 | } $checksum |
||
707 | do_test avtrans-7.4 { |
||
708 | execsql { |
||
709 | BEGIN; |
||
710 | INSERT INTO t2 SELECT * FROM t2; |
||
711 | ROLLBACK; |
||
712 | SELECT md5sum(x,y,z) FROM t2; |
||
713 | } |
||
714 | } $checksum |
||
715 | do_test avtrans-7.5 { |
||
716 | execsql { |
||
717 | BEGIN; |
||
718 | DELETE FROM t2; |
||
719 | ROLLBACK; |
||
720 | SELECT md5sum(x,y,z) FROM t2; |
||
721 | } |
||
722 | } $checksum |
||
723 | do_test avtrans-7.6 { |
||
724 | execsql { |
||
725 | BEGIN; |
||
726 | INSERT INTO t2 SELECT * FROM t2; |
||
727 | ROLLBACK; |
||
728 | SELECT md5sum(x,y,z) FROM t2; |
||
729 | } |
||
730 | } $checksum |
||
731 | do_test avtrans-7.7 { |
||
732 | execsql { |
||
733 | BEGIN; |
||
734 | CREATE TABLE t3 AS SELECT * FROM t2; |
||
735 | INSERT INTO t2 SELECT * FROM t3; |
||
736 | ROLLBACK; |
||
737 | SELECT md5sum(x,y,z) FROM t2; |
||
738 | } |
||
739 | } $checksum |
||
740 | do_test avtrans-7.8 { |
||
741 | execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master} |
||
742 | } $checksum2 |
||
743 | ifcapable tempdb { |
||
744 | do_test avtrans-7.9 { |
||
745 | execsql { |
||
746 | BEGIN; |
||
747 | CREATE TEMP TABLE t3 AS SELECT * FROM t2; |
||
748 | INSERT INTO t2 SELECT * FROM t3; |
||
749 | ROLLBACK; |
||
750 | SELECT md5sum(x,y,z) FROM t2; |
||
751 | } |
||
752 | } $checksum |
||
753 | } |
||
754 | do_test avtrans-7.10 { |
||
755 | execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master} |
||
756 | } $checksum2 |
||
757 | ifcapable tempdb { |
||
758 | do_test avtrans-7.11 { |
||
759 | execsql { |
||
760 | BEGIN; |
||
761 | CREATE TEMP TABLE t3 AS SELECT * FROM t2; |
||
762 | INSERT INTO t2 SELECT * FROM t3; |
||
763 | DROP INDEX i2x; |
||
764 | DROP INDEX i2y; |
||
765 | CREATE INDEX i3a ON t3(x); |
||
766 | ROLLBACK; |
||
767 | SELECT md5sum(x,y,z) FROM t2; |
||
768 | } |
||
769 | } $checksum |
||
770 | } |
||
771 | do_test avtrans-7.12 { |
||
772 | execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master} |
||
773 | } $checksum2 |
||
774 | ifcapable tempdb { |
||
775 | do_test avtrans-7.13 { |
||
776 | execsql { |
||
777 | BEGIN; |
||
778 | DROP TABLE t2; |
||
779 | ROLLBACK; |
||
780 | SELECT md5sum(x,y,z) FROM t2; |
||
781 | } |
||
782 | } $checksum |
||
783 | } |
||
784 | do_test avtrans-7.14 { |
||
785 | execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master} |
||
786 | } $checksum2 |
||
787 | integrity_check avtrans-7.15 |
||
788 | |||
789 | # Arrange for another process to begin modifying the database but abort |
||
790 | # and die in the middle of the modification. Then have this process read |
||
791 | # the database. This process should detect the journal file and roll it |
||
792 | # back. Verify that this happens correctly. |
||
793 | # |
||
794 | set fd [open test.tcl w] |
||
795 | puts $fd { |
||
796 | sqlite3 db test.db |
||
797 | db eval { |
||
798 | PRAGMA default_cache_size=20; |
||
799 | BEGIN; |
||
800 | CREATE TABLE t3 AS SELECT * FROM t2; |
||
801 | DELETE FROM t2; |
||
802 | } |
||
803 | sqlite_abort |
||
804 | } |
||
805 | close $fd |
||
806 | do_test avtrans-8.1 { |
||
807 | catch {exec [info nameofexec] test.tcl} |
||
808 | execsql {SELECT md5sum(x,y,z) FROM t2} |
||
809 | } $checksum |
||
810 | do_test avtrans-8.2 { |
||
811 | execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master} |
||
812 | } $checksum2 |
||
813 | integrity_check avtrans-8.3 |
||
814 | |||
815 | # In the following sequence of tests, compute the MD5 sum of the content |
||
816 | # of a table, make lots of modifications to that table, then do a rollback. |
||
817 | # Verify that after the rollback, the MD5 checksum is unchanged. |
||
818 | # |
||
819 | do_test avtrans-9.1 { |
||
820 | execsql { |
||
821 | PRAGMA default_cache_size=10; |
||
822 | } |
||
823 | db close |
||
824 | sqlite3 db test.db |
||
825 | execsql { |
||
826 | BEGIN; |
||
827 | CREATE TABLE t3(x TEXT); |
||
828 | INSERT INTO t3 VALUES(randstr(10,400)); |
||
829 | INSERT INTO t3 VALUES(randstr(10,400)); |
||
830 | INSERT INTO t3 SELECT randstr(10,400) FROM t3; |
||
831 | INSERT INTO t3 SELECT randstr(10,400) FROM t3; |
||
832 | INSERT INTO t3 SELECT randstr(10,400) FROM t3; |
||
833 | INSERT INTO t3 SELECT randstr(10,400) FROM t3; |
||
834 | INSERT INTO t3 SELECT randstr(10,400) FROM t3; |
||
835 | INSERT INTO t3 SELECT randstr(10,400) FROM t3; |
||
836 | INSERT INTO t3 SELECT randstr(10,400) FROM t3; |
||
837 | INSERT INTO t3 SELECT randstr(10,400) FROM t3; |
||
838 | INSERT INTO t3 SELECT randstr(10,400) FROM t3; |
||
839 | COMMIT; |
||
840 | SELECT count(*) FROM t3; |
||
841 | } |
||
842 | } {1024} |
||
843 | |||
844 | # The following procedure computes a "signature" for table "t3". If |
||
845 | # T3 changes in any way, the signature should change. |
||
846 | # |
||
847 | # This is used to test ROLLBACK. We gather a signature for t3, then |
||
848 | # make lots of changes to t3, then rollback and take another signature. |
||
849 | # The two signatures should be the same. |
||
850 | # |
||
851 | proc signature {} { |
||
852 | return [db eval {SELECT count(*), md5sum(x) FROM t3}] |
||
853 | } |
||
854 | |||
855 | # Repeat the following group of tests 20 times for quick testing and |
||
856 | # 40 times for full testing. Each iteration of the test makes table |
||
857 | # t3 a little larger, and thus takes a little longer, so doing 40 tests |
||
858 | # is more than 2.0 times slower than doing 20 tests. Considerably more. |
||
859 | # |
||
860 | if {[info exists G(isquick)]} { |
||
861 | set limit 20 |
||
862 | } else { |
||
863 | set limit 40 |
||
864 | } |
||
865 | |||
866 | # Do rollbacks. Make sure the signature does not change. |
||
867 | # |
||
868 | for {set i 2} {$i<=$limit} {incr i} { |
||
869 | set ::sig [signature] |
||
870 | set cnt [lindex $::sig 0] |
||
871 | if {$i%2==0} { |
||
872 | execsql {PRAGMA fullfsync=ON} |
||
873 | } else { |
||
874 | execsql {PRAGMA fullfsync=OFF} |
||
875 | } |
||
876 | set sqlite_sync_count 0 |
||
877 | set sqlite_fullsync_count 0 |
||
878 | do_test avtrans-9.$i.1-$cnt { |
||
879 | execsql { |
||
880 | BEGIN; |
||
881 | DELETE FROM t3 WHERE random()%10!=0; |
||
882 | INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; |
||
883 | INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; |
||
884 | ROLLBACK; |
||
885 | } |
||
886 | signature |
||
887 | } $sig |
||
888 | do_test avtrans-9.$i.2-$cnt { |
||
889 | execsql { |
||
890 | BEGIN; |
||
891 | DELETE FROM t3 WHERE random()%10!=0; |
||
892 | INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; |
||
893 | DELETE FROM t3 WHERE random()%10!=0; |
||
894 | INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; |
||
895 | ROLLBACK; |
||
896 | } |
||
897 | signature |
||
898 | } $sig |
||
899 | if {$i<$limit} { |
||
900 | do_test avtrans-9.$i.3-$cnt { |
||
901 | execsql { |
||
902 | INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0; |
||
903 | } |
||
904 | } {} |
||
905 | if {$tcl_platform(platform)=="unix"} { |
||
906 | do_test avtrans-9.$i.4-$cnt { |
||
907 | expr {$sqlite_sync_count>0} |
||
908 | } 1 |
||
909 | ifcapable pager_pragmas { |
||
910 | do_test avtrans-9.$i.5-$cnt { |
||
911 | expr {$sqlite_fullsync_count>0} |
||
912 | } [expr {$i%2==0}] |
||
913 | } else { |
||
914 | do_test avtrans-9.$i.5-$cnt { |
||
915 | expr {$sqlite_fullsync_count==0} |
||
916 | } {1} |
||
917 | } |
||
918 | } |
||
919 | wal_check_journal_mode avtrans-9.$i-6.$cnt |
||
920 | } |
||
921 | set ::pager_old_format 0 |
||
922 | } |
||
923 | integrity_check avtrans-10.1 |
||
924 | wal_check_journal_mode avtrans-10.2 |
||
925 | |||
926 | finish_test |