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