| 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 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 | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2363173002:
[sqlite] Remove obsolete reference version 3.8.7.4. (Closed)
