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