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