| OLD | NEW |
|---|
| (Empty) |
| 1 # 2008 December 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 # | |
| 12 # $Id: savepoint.test,v 1.13 2009/07/18 08:30:45 danielk1977 Exp $ | |
| 13 | |
| 14 set testdir [file dirname $argv0] | |
| 15 source $testdir/tester.tcl | |
| 16 source $testdir/lock_common.tcl | |
| 17 source $testdir/malloc_common.tcl | |
| 18 | |
| 19 #---------------------------------------------------------------------- | |
| 20 # The following tests - savepoint-1.* - test that the SAVEPOINT, RELEASE | |
| 21 # and ROLLBACK TO comands are correctly parsed, and that the auto-commit | |
| 22 # flag is correctly set and unset as a result. | |
| 23 # | |
| 24 do_test savepoint-1.1 { | |
| 25 wal_set_journal_mode | |
| 26 execsql { | |
| 27 SAVEPOINT sp1; | |
| 28 RELEASE sp1; | |
| 29 } | |
| 30 } {} | |
| 31 do_test savepoint-1.2 { | |
| 32 execsql { | |
| 33 SAVEPOINT sp1; | |
| 34 ROLLBACK TO sp1; | |
| 35 } | |
| 36 } {} | |
| 37 do_test savepoint-1.3 { | |
| 38 execsql { SAVEPOINT sp1 } | |
| 39 db close | |
| 40 } {} | |
| 41 sqlite3 db test.db | |
| 42 do_test savepoint-1.4.1 { | |
| 43 execsql { | |
| 44 SAVEPOINT sp1; | |
| 45 SAVEPOINT sp2; | |
| 46 RELEASE sp1; | |
| 47 } | |
| 48 sqlite3_get_autocommit db | |
| 49 } {1} | |
| 50 do_test savepoint-1.4.2 { | |
| 51 execsql { | |
| 52 SAVEPOINT sp1; | |
| 53 SAVEPOINT sp2; | |
| 54 RELEASE sp2; | |
| 55 } | |
| 56 sqlite3_get_autocommit db | |
| 57 } {0} | |
| 58 do_test savepoint-1.4.3 { | |
| 59 execsql { RELEASE sp1 } | |
| 60 sqlite3_get_autocommit db | |
| 61 } {1} | |
| 62 do_test savepoint-1.4.4 { | |
| 63 execsql { | |
| 64 SAVEPOINT sp1; | |
| 65 SAVEPOINT sp2; | |
| 66 ROLLBACK TO sp1; | |
| 67 } | |
| 68 sqlite3_get_autocommit db | |
| 69 } {0} | |
| 70 do_test savepoint-1.4.5 { | |
| 71 execsql { RELEASE SAVEPOINT sp1 } | |
| 72 sqlite3_get_autocommit db | |
| 73 } {1} | |
| 74 do_test savepoint-1.4.6 { | |
| 75 execsql { | |
| 76 SAVEPOINT sp1; | |
| 77 SAVEPOINT sp2; | |
| 78 SAVEPOINT sp3; | |
| 79 ROLLBACK TO SAVEPOINT sp3; | |
| 80 ROLLBACK TRANSACTION TO sp2; | |
| 81 ROLLBACK TRANSACTION TO SAVEPOINT sp1; | |
| 82 } | |
| 83 sqlite3_get_autocommit db | |
| 84 } {0} | |
| 85 do_test savepoint-1.4.7 { | |
| 86 execsql { RELEASE SAVEPOINT SP1 } | |
| 87 sqlite3_get_autocommit db | |
| 88 } {1} | |
| 89 do_test savepoint-1.5 { | |
| 90 execsql { | |
| 91 SAVEPOINT sp1; | |
| 92 ROLLBACK TO sp1; | |
| 93 } | |
| 94 } {} | |
| 95 do_test savepoint-1.6 { | |
| 96 execsql COMMIT | |
| 97 } {} | |
| 98 wal_check_journal_mode savepoint-1.7 | |
| 99 | |
| 100 #------------------------------------------------------------------------ | |
| 101 # These tests - savepoint-2.* - test rollbacks and releases of savepoints | |
| 102 # with a very simple data set. | |
| 103 # | |
| 104 | |
| 105 do_test savepoint-2.1 { | |
| 106 execsql { | |
| 107 CREATE TABLE t1(a, b, c); | |
| 108 BEGIN; | |
| 109 INSERT INTO t1 VALUES(1, 2, 3); | |
| 110 SAVEPOINT one; | |
| 111 UPDATE t1 SET a = 2, b = 3, c = 4; | |
| 112 } | |
| 113 execsql { SELECT * FROM t1 } | |
| 114 } {2 3 4} | |
| 115 do_test savepoint-2.2 { | |
| 116 execsql { | |
| 117 ROLLBACK TO one; | |
| 118 } | |
| 119 execsql { SELECT * FROM t1 } | |
| 120 } {1 2 3} | |
| 121 do_test savepoint-2.3 { | |
| 122 execsql { | |
| 123 INSERT INTO t1 VALUES(4, 5, 6); | |
| 124 } | |
| 125 execsql { SELECT * FROM t1 } | |
| 126 } {1 2 3 4 5 6} | |
| 127 do_test savepoint-2.4 { | |
| 128 execsql { | |
| 129 ROLLBACK TO one; | |
| 130 } | |
| 131 execsql { SELECT * FROM t1 } | |
| 132 } {1 2 3} | |
| 133 | |
| 134 | |
| 135 do_test savepoint-2.5 { | |
| 136 execsql { | |
| 137 INSERT INTO t1 VALUES(7, 8, 9); | |
| 138 SAVEPOINT two; | |
| 139 INSERT INTO t1 VALUES(10, 11, 12); | |
| 140 } | |
| 141 execsql { SELECT * FROM t1 } | |
| 142 } {1 2 3 7 8 9 10 11 12} | |
| 143 do_test savepoint-2.6 { | |
| 144 execsql { | |
| 145 ROLLBACK TO two; | |
| 146 } | |
| 147 execsql { SELECT * FROM t1 } | |
| 148 } {1 2 3 7 8 9} | |
| 149 do_test savepoint-2.7 { | |
| 150 execsql { | |
| 151 INSERT INTO t1 VALUES(10, 11, 12); | |
| 152 } | |
| 153 execsql { SELECT * FROM t1 } | |
| 154 } {1 2 3 7 8 9 10 11 12} | |
| 155 do_test savepoint-2.8 { | |
| 156 execsql { | |
| 157 ROLLBACK TO one; | |
| 158 } | |
| 159 execsql { SELECT * FROM t1 } | |
| 160 } {1 2 3} | |
| 161 do_test savepoint-2.9 { | |
| 162 execsql { | |
| 163 INSERT INTO t1 VALUES('a', 'b', 'c'); | |
| 164 SAVEPOINT two; | |
| 165 INSERT INTO t1 VALUES('d', 'e', 'f'); | |
| 166 } | |
| 167 execsql { SELECT * FROM t1 } | |
| 168 } {1 2 3 a b c d e f} | |
| 169 do_test savepoint-2.10 { | |
| 170 execsql { | |
| 171 RELEASE two; | |
| 172 } | |
| 173 execsql { SELECT * FROM t1 } | |
| 174 } {1 2 3 a b c d e f} | |
| 175 do_test savepoint-2.11 { | |
| 176 execsql { | |
| 177 ROLLBACK; | |
| 178 } | |
| 179 execsql { SELECT * FROM t1 } | |
| 180 } {} | |
| 181 wal_check_journal_mode savepoint-2.12 | |
| 182 | |
| 183 #------------------------------------------------------------------------ | |
| 184 # This block of tests - savepoint-3.* - test that when a transaction | |
| 185 # savepoint is rolled back, locks are not released from database files. | |
| 186 # And that when a transaction savepoint is released, they are released. | |
| 187 # | |
| 188 # These tests do not work in WAL mode. WAL mode does not take RESERVED | |
| 189 # locks on the database file. | |
| 190 # | |
| 191 if {[wal_is_wal_mode]==0} { | |
| 192 do_test savepoint-3.1 { | |
| 193 execsql { SAVEPOINT "transaction" } | |
| 194 execsql { PRAGMA lock_status } | |
| 195 } {main unlocked temp closed} | |
| 196 | |
| 197 do_test savepoint-3.2 { | |
| 198 execsql { INSERT INTO t1 VALUES(1, 2, 3) } | |
| 199 execsql { PRAGMA lock_status } | |
| 200 } {main reserved temp closed} | |
| 201 | |
| 202 do_test savepoint-3.3 { | |
| 203 execsql { ROLLBACK TO "transaction" } | |
| 204 execsql { PRAGMA lock_status } | |
| 205 } {main reserved temp closed} | |
| 206 | |
| 207 do_test savepoint-3.4 { | |
| 208 execsql { INSERT INTO t1 VALUES(1, 2, 3) } | |
| 209 execsql { PRAGMA lock_status } | |
| 210 } {main reserved temp closed} | |
| 211 | |
| 212 do_test savepoint-3.5 { | |
| 213 execsql { RELEASE "transaction" } | |
| 214 execsql { PRAGMA lock_status } | |
| 215 } {main unlocked temp closed} | |
| 216 } | |
| 217 | |
| 218 #------------------------------------------------------------------------ | |
| 219 # Test that savepoints that include schema modifications are handled | |
| 220 # correctly. Test cases savepoint-4.*. | |
| 221 # | |
| 222 do_test savepoint-4.1 { | |
| 223 execsql { | |
| 224 CREATE TABLE t2(d, e, f); | |
| 225 SELECT sql FROM sqlite_master; | |
| 226 } | |
| 227 } {{CREATE TABLE t1(a, b, c)} {CREATE TABLE t2(d, e, f)}} | |
| 228 do_test savepoint-4.2 { | |
| 229 execsql { | |
| 230 BEGIN; | |
| 231 CREATE TABLE t3(g,h); | |
| 232 INSERT INTO t3 VALUES('I', 'II'); | |
| 233 SAVEPOINT one; | |
| 234 DROP TABLE t3; | |
| 235 } | |
| 236 } {} | |
| 237 do_test savepoint-4.3 { | |
| 238 execsql { | |
| 239 CREATE TABLE t3(g, h, i); | |
| 240 INSERT INTO t3 VALUES('III', 'IV', 'V'); | |
| 241 } | |
| 242 execsql {SELECT * FROM t3} | |
| 243 } {III IV V} | |
| 244 do_test savepoint-4.4 { | |
| 245 execsql { ROLLBACK TO one; } | |
| 246 execsql {SELECT * FROM t3} | |
| 247 } {I II} | |
| 248 do_test savepoint-4.5 { | |
| 249 execsql { | |
| 250 ROLLBACK; | |
| 251 SELECT sql FROM sqlite_master; | |
| 252 } | |
| 253 } {{CREATE TABLE t1(a, b, c)} {CREATE TABLE t2(d, e, f)}} | |
| 254 | |
| 255 do_test savepoint-4.6 { | |
| 256 execsql { | |
| 257 BEGIN; | |
| 258 INSERT INTO t1 VALUES('o', 't', 't'); | |
| 259 SAVEPOINT sp1; | |
| 260 CREATE TABLE t3(a, b, c); | |
| 261 INSERT INTO t3 VALUES('z', 'y', 'x'); | |
| 262 } | |
| 263 execsql {SELECT * FROM t3} | |
| 264 } {z y x} | |
| 265 do_test savepoint-4.7 { | |
| 266 execsql { | |
| 267 ROLLBACK TO sp1; | |
| 268 CREATE TABLE t3(a); | |
| 269 INSERT INTO t3 VALUES('value'); | |
| 270 } | |
| 271 execsql {SELECT * FROM t3} | |
| 272 } {value} | |
| 273 do_test savepoint-4.8 { | |
| 274 execsql COMMIT | |
| 275 } {} | |
| 276 wal_check_journal_mode savepoint-4.9 | |
| 277 | |
| 278 #------------------------------------------------------------------------ | |
| 279 # Test some logic errors to do with the savepoint feature. | |
| 280 # | |
| 281 | |
| 282 ifcapable incrblob { | |
| 283 do_test savepoint-5.1.1 { | |
| 284 execsql { | |
| 285 CREATE TABLE blobs(x); | |
| 286 INSERT INTO blobs VALUES('a twentyeight character blob'); | |
| 287 } | |
| 288 set fd [db incrblob blobs x 1] | |
| 289 puts -nonewline $fd "hello" | |
| 290 catchsql {SAVEPOINT abc} | |
| 291 } {1 {cannot open savepoint - SQL statements in progress}} | |
| 292 do_test savepoint-5.1.2 { | |
| 293 close $fd | |
| 294 catchsql {SAVEPOINT abc} | |
| 295 } {0 {}} | |
| 296 | |
| 297 do_test savepoint-5.2 { | |
| 298 execsql {RELEASE abc} | |
| 299 catchsql {RELEASE abc} | |
| 300 } {1 {no such savepoint: abc}} | |
| 301 | |
| 302 do_test savepoint-5.3.1 { | |
| 303 execsql {SAVEPOINT abc} | |
| 304 catchsql {ROLLBACK TO def} | |
| 305 } {1 {no such savepoint: def}} | |
| 306 do_test savepoint-5.3.2.1 { | |
| 307 execsql {SAVEPOINT def} | |
| 308 set fd [db incrblob -readonly blobs x 1] | |
| 309 set rc [catch {seek $fd 0;read $fd} res] | |
| 310 lappend rc $res | |
| 311 } {0 {hellontyeight character blob}} | |
| 312 do_test savepoint-5.3.2.2 { | |
| 313 catchsql {ROLLBACK TO def} | |
| 314 } {0 {}} | |
| 315 do_test savepoint-5.3.2.3 { | |
| 316 set rc [catch {seek $fd 0; read $fd} res] | |
| 317 set rc | |
| 318 } {0} | |
| 319 do_test savepoint-5.3.3 { | |
| 320 catchsql {RELEASE def} | |
| 321 } {0 {}} | |
| 322 do_test savepoint-5.3.4 { | |
| 323 close $fd | |
| 324 execsql {savepoint def} | |
| 325 set fd [db incrblob blobs x 1] | |
| 326 catchsql {release def} | |
| 327 } {1 {cannot release savepoint - SQL statements in progress}} | |
| 328 do_test savepoint-5.3.5 { | |
| 329 close $fd | |
| 330 execsql {release abc} | |
| 331 } {} | |
| 332 | |
| 333 # Rollback mode: | |
| 334 # | |
| 335 # Open a savepoint transaction and insert a row into the database. Then, | |
| 336 # using a second database handle, open a read-only transaction on the | |
| 337 # database file. Check that the savepoint transaction cannot be committed | |
| 338 # until after the read-only transaction has been closed. | |
| 339 # | |
| 340 # WAL mode: | |
| 341 # | |
| 342 # As above, except that the savepoint transaction can be successfully | |
| 343 # committed before the read-only transaction has been closed. | |
| 344 # | |
| 345 do_test savepoint-5.4.1 { | |
| 346 execsql { | |
| 347 SAVEPOINT main; | |
| 348 INSERT INTO blobs VALUES('another blob'); | |
| 349 } | |
| 350 } {} | |
| 351 do_test savepoint-5.4.2 { | |
| 352 sqlite3 db2 test.db | |
| 353 execsql { BEGIN ; SELECT count(*) FROM blobs } db2 | |
| 354 } {1} | |
| 355 if {[wal_is_wal_mode]} { | |
| 356 do_test savepoint-5.4.3 { catchsql "RELEASE main" } {0 {}} | |
| 357 do_test savepoint-5.4.4 { db2 close } {} | |
| 358 } else { | |
| 359 do_test savepoint-5.4.3 { | |
| 360 catchsql { RELEASE main } | |
| 361 } {1 {database is locked}} | |
| 362 do_test savepoint-5.4.4 { | |
| 363 db2 close | |
| 364 catchsql { RELEASE main } | |
| 365 } {0 {}} | |
| 366 } | |
| 367 do_test savepoint-5.4.5 { | |
| 368 execsql { SELECT x FROM blobs WHERE rowid = 2 } | |
| 369 } {{another blob}} | |
| 370 do_test savepoint-5.4.6 { | |
| 371 execsql { SELECT count(*) FROM blobs } | |
| 372 } {2} | |
| 373 } | |
| 374 wal_check_journal_mode savepoint-5.5 | |
| 375 | |
| 376 #------------------------------------------------------------------------- | |
| 377 # The following tests, savepoint-6.*, test an incr-vacuum inside of a | |
| 378 # couple of nested savepoints. | |
| 379 # | |
| 380 ifcapable {autovacuum && pragma} { | |
| 381 db close | |
| 382 forcedelete test.db | |
| 383 sqlite3 db test.db | |
| 384 | |
| 385 do_test savepoint-6.1 { | |
| 386 execsql { PRAGMA auto_vacuum = incremental } | |
| 387 wal_set_journal_mode | |
| 388 execsql { | |
| 389 CREATE TABLE t1(a, b, c); | |
| 390 CREATE INDEX i1 ON t1(a, b); | |
| 391 BEGIN; | |
| 392 INSERT INTO t1 VALUES(randstr(10,400),randstr(10,400),randstr(10,400)); | |
| 393 } | |
| 394 set r "randstr(10,400)" | |
| 395 for {set ii 0} {$ii < 10} {incr ii} { | |
| 396 execsql "INSERT INTO t1 SELECT $r, $r, $r FROM t1" | |
| 397 } | |
| 398 execsql { COMMIT } | |
| 399 } {} | |
| 400 | |
| 401 integrity_check savepoint-6.2 | |
| 402 | |
| 403 do_test savepoint-6.3 { | |
| 404 execsql { | |
| 405 PRAGMA cache_size = 10; | |
| 406 BEGIN; | |
| 407 UPDATE t1 SET a = randstr(10,10) WHERE (rowid%4)==0; | |
| 408 SAVEPOINT one; | |
| 409 DELETE FROM t1 WHERE rowid%2; | |
| 410 PRAGMA incr_vacuum; | |
| 411 SAVEPOINT two; | |
| 412 INSERT INTO t1 SELECT randstr(10,400), randstr(10,400), c FROM t1; | |
| 413 DELETE FROM t1 WHERE rowid%2; | |
| 414 PRAGMA incr_vacuum; | |
| 415 ROLLBACK TO one; | |
| 416 COMMIT; | |
| 417 } | |
| 418 } {} | |
| 419 | |
| 420 integrity_check savepoint-6.4 | |
| 421 | |
| 422 wal_check_journal_mode savepoint-6.5 | |
| 423 } | |
| 424 | |
| 425 #------------------------------------------------------------------------- | |
| 426 # The following tests, savepoint-7.*, attempt to break the logic | |
| 427 # surrounding savepoints by growing and shrinking the database file. | |
| 428 # | |
| 429 db close | |
| 430 forcedelete test.db | |
| 431 sqlite3 db test.db | |
| 432 | |
| 433 do_test savepoint-7.1 { | |
| 434 execsql { PRAGMA auto_vacuum = incremental } | |
| 435 wal_set_journal_mode | |
| 436 execsql { | |
| 437 PRAGMA cache_size = 10; | |
| 438 BEGIN; | |
| 439 CREATE TABLE t1(a PRIMARY KEY, b); | |
| 440 INSERT INTO t1(a) VALUES('alligator'); | |
| 441 INSERT INTO t1(a) VALUES('angelfish'); | |
| 442 INSERT INTO t1(a) VALUES('ant'); | |
| 443 INSERT INTO t1(a) VALUES('antelope'); | |
| 444 INSERT INTO t1(a) VALUES('ape'); | |
| 445 INSERT INTO t1(a) VALUES('baboon'); | |
| 446 INSERT INTO t1(a) VALUES('badger'); | |
| 447 INSERT INTO t1(a) VALUES('bear'); | |
| 448 INSERT INTO t1(a) VALUES('beetle'); | |
| 449 INSERT INTO t1(a) VALUES('bird'); | |
| 450 INSERT INTO t1(a) VALUES('bison'); | |
| 451 UPDATE t1 SET b = randstr(1000,1000); | |
| 452 UPDATE t1 SET b = b||randstr(1000,1000); | |
| 453 UPDATE t1 SET b = b||randstr(1000,1000); | |
| 454 UPDATE t1 SET b = b||randstr(10,1000); | |
| 455 COMMIT; | |
| 456 } | |
| 457 expr ([execsql { PRAGMA page_count }] > 20) | |
| 458 } {1} | |
| 459 do_test savepoint-7.2.1 { | |
| 460 execsql { | |
| 461 BEGIN; | |
| 462 SAVEPOINT one; | |
| 463 CREATE TABLE t2(a, b); | |
| 464 INSERT INTO t2 SELECT a, b FROM t1; | |
| 465 ROLLBACK TO one; | |
| 466 } | |
| 467 execsql { | |
| 468 PRAGMA integrity_check; | |
| 469 } | |
| 470 } {ok} | |
| 471 do_test savepoint-7.2.2 { | |
| 472 execsql { | |
| 473 COMMIT; | |
| 474 PRAGMA integrity_check; | |
| 475 } | |
| 476 } {ok} | |
| 477 | |
| 478 do_test savepoint-7.3.1 { | |
| 479 execsql { | |
| 480 CREATE TABLE t2(a, b); | |
| 481 INSERT INTO t2 SELECT a, b FROM t1; | |
| 482 } | |
| 483 } {} | |
| 484 do_test savepoint-7.3.2 { | |
| 485 execsql { | |
| 486 BEGIN; | |
| 487 SAVEPOINT one; | |
| 488 DELETE FROM t2; | |
| 489 PRAGMA incremental_vacuum; | |
| 490 SAVEPOINT two; | |
| 491 INSERT INTO t2 SELECT a, b FROM t1; | |
| 492 ROLLBACK TO two; | |
| 493 COMMIT; | |
| 494 } | |
| 495 execsql { PRAGMA integrity_check } | |
| 496 } {ok} | |
| 497 wal_check_journal_mode savepoint-7.3.3 | |
| 498 | |
| 499 do_test savepoint-7.4.1 { | |
| 500 db close | |
| 501 forcedelete test.db | |
| 502 sqlite3 db test.db | |
| 503 execsql { PRAGMA auto_vacuum = incremental } | |
| 504 wal_set_journal_mode | |
| 505 execsql { | |
| 506 CREATE TABLE t1(a, b, PRIMARY KEY(a, b)); | |
| 507 INSERT INTO t1 VALUES(randstr(1000,1000), randstr(1000,1000)); | |
| 508 BEGIN; | |
| 509 DELETE FROM t1; | |
| 510 SAVEPOINT one; | |
| 511 PRAGMA incremental_vacuum; | |
| 512 ROLLBACK TO one; | |
| 513 COMMIT; | |
| 514 } | |
| 515 | |
| 516 execsql { PRAGMA integrity_check } | |
| 517 } {ok} | |
| 518 | |
| 519 do_test savepoint-7.5.1 { | |
| 520 execsql { | |
| 521 PRAGMA incremental_vacuum; | |
| 522 CREATE TABLE t5(x, y); | |
| 523 INSERT INTO t5 VALUES(1, randstr(1000,1000)); | |
| 524 INSERT INTO t5 VALUES(2, randstr(1000,1000)); | |
| 525 INSERT INTO t5 VALUES(3, randstr(1000,1000)); | |
| 526 | |
| 527 BEGIN; | |
| 528 INSERT INTO t5 VALUES(4, randstr(1000,1000)); | |
| 529 INSERT INTO t5 VALUES(5, randstr(1000,1000)); | |
| 530 DELETE FROM t5 WHERE x=1 OR x=2; | |
| 531 SAVEPOINT one; | |
| 532 PRAGMA incremental_vacuum; | |
| 533 SAVEPOINT two; | |
| 534 INSERT INTO t5 VALUES(1, randstr(1000,1000)); | |
| 535 INSERT INTO t5 VALUES(2, randstr(1000,1000)); | |
| 536 ROLLBACK TO two; | |
| 537 ROLLBACK TO one; | |
| 538 COMMIT; | |
| 539 PRAGMA integrity_check; | |
| 540 } | |
| 541 } {ok} | |
| 542 do_test savepoint-7.5.2 { | |
| 543 execsql { | |
| 544 DROP TABLE t5; | |
| 545 } | |
| 546 } {} | |
| 547 wal_check_journal_mode savepoint-7.5.3 | |
| 548 | |
| 549 # Test oddly named and quoted savepoints. | |
| 550 # | |
| 551 do_test savepoint-8-1 { | |
| 552 execsql { SAVEPOINT "save1" } | |
| 553 execsql { RELEASE save1 } | |
| 554 } {} | |
| 555 do_test savepoint-8-2 { | |
| 556 execsql { SAVEPOINT "Including whitespace " } | |
| 557 execsql { RELEASE "including Whitespace " } | |
| 558 } {} | |
| 559 | |
| 560 # Test that the authorization callback works. | |
| 561 # | |
| 562 ifcapable auth { | |
| 563 proc auth {args} { | |
| 564 eval lappend ::authdata [lrange $args 0 4] | |
| 565 return SQLITE_OK | |
| 566 } | |
| 567 db auth auth | |
| 568 | |
| 569 do_test savepoint-9.1 { | |
| 570 set ::authdata [list] | |
| 571 execsql { SAVEPOINT sp1 } | |
| 572 set ::authdata | |
| 573 } {SQLITE_SAVEPOINT BEGIN sp1 {} {}} | |
| 574 do_test savepoint-9.2 { | |
| 575 set ::authdata [list] | |
| 576 execsql { ROLLBACK TO sp1 } | |
| 577 set ::authdata | |
| 578 } {SQLITE_SAVEPOINT ROLLBACK sp1 {} {}} | |
| 579 do_test savepoint-9.3 { | |
| 580 set ::authdata [list] | |
| 581 execsql { RELEASE sp1 } | |
| 582 set ::authdata | |
| 583 } {SQLITE_SAVEPOINT RELEASE sp1 {} {}} | |
| 584 | |
| 585 proc auth {args} { | |
| 586 eval lappend ::authdata [lrange $args 0 4] | |
| 587 return SQLITE_DENY | |
| 588 } | |
| 589 db auth auth | |
| 590 | |
| 591 do_test savepoint-9.4 { | |
| 592 set ::authdata [list] | |
| 593 set res [catchsql { SAVEPOINT sp1 }] | |
| 594 concat $::authdata $res | |
| 595 } {SQLITE_SAVEPOINT BEGIN sp1 {} {} 1 {not authorized}} | |
| 596 do_test savepoint-9.5 { | |
| 597 set ::authdata [list] | |
| 598 set res [catchsql { ROLLBACK TO sp1 }] | |
| 599 concat $::authdata $res | |
| 600 } {SQLITE_SAVEPOINT ROLLBACK sp1 {} {} 1 {not authorized}} | |
| 601 do_test savepoint-9.6 { | |
| 602 set ::authdata [list] | |
| 603 set res [catchsql { RELEASE sp1 }] | |
| 604 concat $::authdata $res | |
| 605 } {SQLITE_SAVEPOINT RELEASE sp1 {} {} 1 {not authorized}} | |
| 606 | |
| 607 catch { db eval ROLLBACK } | |
| 608 db auth "" | |
| 609 } | |
| 610 | |
| 611 #------------------------------------------------------------------------- | |
| 612 # The following tests - savepoint-10.* - test the interaction of | |
| 613 # savepoints and ATTACH statements. | |
| 614 # | |
| 615 | |
| 616 # First make sure it is not possible to attach or detach a database while | |
| 617 # a savepoint is open (it is not possible if any transaction is open). | |
| 618 # | |
| 619 do_test savepoint-10.1.1 { | |
| 620 catchsql { | |
| 621 SAVEPOINT one; | |
| 622 ATTACH 'test2.db' AS aux; | |
| 623 } | |
| 624 } {1 {cannot ATTACH database within transaction}} | |
| 625 do_test savepoint-10.1.2 { | |
| 626 execsql { | |
| 627 RELEASE one; | |
| 628 ATTACH 'test2.db' AS aux; | |
| 629 } | |
| 630 catchsql { | |
| 631 SAVEPOINT one; | |
| 632 DETACH aux; | |
| 633 } | |
| 634 } {1 {cannot DETACH database within transaction}} | |
| 635 do_test savepoint-10.1.3 { | |
| 636 execsql { | |
| 637 RELEASE one; | |
| 638 DETACH aux; | |
| 639 } | |
| 640 } {} | |
| 641 | |
| 642 # The lock state of the TEMP database can vary if SQLITE_TEMP_STORE=3 | |
| 643 # And the following set of tests is only really interested in the status | |
| 644 # of the aux1 and aux2 locks. So record the current lock status of | |
| 645 # TEMP for use in the answers. | |
| 646 set templockstate [lindex [db eval {PRAGMA lock_status}] 3] | |
| 647 | |
| 648 | |
| 649 if {[wal_is_wal_mode]==0} { | |
| 650 do_test savepoint-10.2.1 { | |
| 651 forcedelete test3.db | |
| 652 forcedelete test2.db | |
| 653 execsql { | |
| 654 ATTACH 'test2.db' AS aux1; | |
| 655 ATTACH 'test3.db' AS aux2; | |
| 656 DROP TABLE t1; | |
| 657 CREATE TABLE main.t1(x, y); | |
| 658 CREATE TABLE aux1.t2(x, y); | |
| 659 CREATE TABLE aux2.t3(x, y); | |
| 660 SELECT name FROM sqlite_master; | |
| 661 SELECT name FROM aux1.sqlite_master; | |
| 662 SELECT name FROM aux2.sqlite_master; | |
| 663 } | |
| 664 } {t1 t2 t3} | |
| 665 do_test savepoint-10.2.2 { | |
| 666 execsql { PRAGMA lock_status } | |
| 667 } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked] | |
| 668 | |
| 669 do_test savepoint-10.2.3 { | |
| 670 execsql { | |
| 671 SAVEPOINT one; | |
| 672 INSERT INTO t1 VALUES(1, 2); | |
| 673 PRAGMA lock_status; | |
| 674 } | |
| 675 } [list main reserved temp $templockstate aux1 unlocked aux2 unlocked] | |
| 676 do_test savepoint-10.2.4 { | |
| 677 execsql { | |
| 678 INSERT INTO t3 VALUES(3, 4); | |
| 679 PRAGMA lock_status; | |
| 680 } | |
| 681 } [list main reserved temp $templockstate aux1 unlocked aux2 reserved] | |
| 682 do_test savepoint-10.2.5 { | |
| 683 execsql { | |
| 684 SAVEPOINT two; | |
| 685 INSERT INTO t2 VALUES(5, 6); | |
| 686 PRAGMA lock_status; | |
| 687 } | |
| 688 } [list main reserved temp $templockstate aux1 reserved aux2 reserved] | |
| 689 do_test savepoint-10.2.6 { | |
| 690 execsql { SELECT * FROM t2 } | |
| 691 } {5 6} | |
| 692 do_test savepoint-10.2.7 { | |
| 693 execsql { ROLLBACK TO two } | |
| 694 execsql { SELECT * FROM t2 } | |
| 695 } {} | |
| 696 do_test savepoint-10.2.8 { | |
| 697 execsql { PRAGMA lock_status } | |
| 698 } [list main reserved temp $templockstate aux1 reserved aux2 reserved] | |
| 699 do_test savepoint-10.2.9 { | |
| 700 execsql { SELECT 'a', * FROM t1 ; SELECT 'b', * FROM t3 } | |
| 701 } {a 1 2 b 3 4} | |
| 702 do_test savepoint-10.2.9 { | |
| 703 execsql { | |
| 704 INSERT INTO t2 VALUES(5, 6); | |
| 705 RELEASE one; | |
| 706 } | |
| 707 execsql { | |
| 708 SELECT * FROM t1; | |
| 709 SELECT * FROM t2; | |
| 710 SELECT * FROM t3; | |
| 711 } | |
| 712 } {1 2 5 6 3 4} | |
| 713 do_test savepoint-10.2.9 { | |
| 714 execsql { PRAGMA lock_status } | |
| 715 } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked] | |
| 716 | |
| 717 do_test savepoint-10.2.10 { | |
| 718 execsql { | |
| 719 SAVEPOINT one; | |
| 720 INSERT INTO t1 VALUES('a', 'b'); | |
| 721 SAVEPOINT two; | |
| 722 INSERT INTO t2 VALUES('c', 'd'); | |
| 723 SAVEPOINT three; | |
| 724 INSERT INTO t3 VALUES('e', 'f'); | |
| 725 } | |
| 726 execsql { | |
| 727 SELECT * FROM t1; | |
| 728 SELECT * FROM t2; | |
| 729 SELECT * FROM t3; | |
| 730 } | |
| 731 } {1 2 a b 5 6 c d 3 4 e f} | |
| 732 do_test savepoint-10.2.11 { | |
| 733 execsql { ROLLBACK TO two } | |
| 734 execsql { | |
| 735 SELECT * FROM t1; | |
| 736 SELECT * FROM t2; | |
| 737 SELECT * FROM t3; | |
| 738 } | |
| 739 } {1 2 a b 5 6 3 4} | |
| 740 do_test savepoint-10.2.12 { | |
| 741 execsql { | |
| 742 INSERT INTO t3 VALUES('g', 'h'); | |
| 743 ROLLBACK TO two; | |
| 744 } | |
| 745 execsql { | |
| 746 SELECT * FROM t1; | |
| 747 SELECT * FROM t2; | |
| 748 SELECT * FROM t3; | |
| 749 } | |
| 750 } {1 2 a b 5 6 3 4} | |
| 751 do_test savepoint-10.2.13 { | |
| 752 execsql { ROLLBACK } | |
| 753 execsql { | |
| 754 SELECT * FROM t1; | |
| 755 SELECT * FROM t2; | |
| 756 SELECT * FROM t3; | |
| 757 } | |
| 758 } {1 2 5 6 3 4} | |
| 759 do_test savepoint-10.2.14 { | |
| 760 execsql { PRAGMA lock_status } | |
| 761 } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked] | |
| 762 } | |
| 763 | |
| 764 #------------------------------------------------------------------------- | |
| 765 # The following tests - savepoint-11.* - test the interaction of | |
| 766 # savepoints and creating or dropping tables and indexes in | |
| 767 # auto-vacuum mode. | |
| 768 # | |
| 769 do_test savepoint-11.1 { | |
| 770 db close | |
| 771 forcedelete test.db | |
| 772 sqlite3 db test.db | |
| 773 execsql { PRAGMA auto_vacuum = full; } | |
| 774 wal_set_journal_mode | |
| 775 execsql { | |
| 776 CREATE TABLE t1(a, b, UNIQUE(a, b)); | |
| 777 INSERT INTO t1 VALUES(1, randstr(1000,1000)); | |
| 778 INSERT INTO t1 VALUES(2, randstr(1000,1000)); | |
| 779 } | |
| 780 } {} | |
| 781 do_test savepoint-11.2 { | |
| 782 execsql { | |
| 783 SAVEPOINT one; | |
| 784 CREATE TABLE t2(a, b, UNIQUE(a, b)); | |
| 785 SAVEPOINT two; | |
| 786 CREATE TABLE t3(a, b, UNIQUE(a, b)); | |
| 787 } | |
| 788 } {} | |
| 789 integrity_check savepoint-11.3 | |
| 790 do_test savepoint-11.4 { | |
| 791 execsql { ROLLBACK TO two } | |
| 792 } {} | |
| 793 integrity_check savepoint-11.5 | |
| 794 do_test savepoint-11.6 { | |
| 795 execsql { | |
| 796 CREATE TABLE t3(a, b, UNIQUE(a, b)); | |
| 797 ROLLBACK TO one; | |
| 798 } | |
| 799 } {} | |
| 800 integrity_check savepoint-11.7 | |
| 801 do_test savepoint-11.8 { | |
| 802 execsql { ROLLBACK } | |
| 803 execsql { PRAGMA wal_checkpoint } | |
| 804 file size test.db | |
| 805 } {8192} | |
| 806 | |
| 807 do_test savepoint-11.9 { | |
| 808 execsql { | |
| 809 DROP TABLE IF EXISTS t1; | |
| 810 DROP TABLE IF EXISTS t2; | |
| 811 DROP TABLE IF EXISTS t3; | |
| 812 } | |
| 813 } {} | |
| 814 do_test savepoint-11.10 { | |
| 815 execsql { | |
| 816 BEGIN; | |
| 817 CREATE TABLE t1(a, b); | |
| 818 CREATE TABLE t2(x, y); | |
| 819 INSERT INTO t2 VALUES(1, 2); | |
| 820 SAVEPOINT one; | |
| 821 INSERT INTO t2 VALUES(3, 4); | |
| 822 SAVEPOINT two; | |
| 823 DROP TABLE t1; | |
| 824 ROLLBACK TO two; | |
| 825 } | |
| 826 execsql {SELECT * FROM t2} | |
| 827 } {1 2 3 4} | |
| 828 do_test savepoint-11.11 { | |
| 829 execsql COMMIT | |
| 830 } {} | |
| 831 do_test savepoint-11.12 { | |
| 832 execsql {SELECT * FROM t2} | |
| 833 } {1 2 3 4} | |
| 834 wal_check_journal_mode savepoint-11.13 | |
| 835 | |
| 836 #------------------------------------------------------------------------- | |
| 837 # The following tests - savepoint-12.* - test the interaction of | |
| 838 # savepoints and "ON CONFLICT ROLLBACK" clauses. | |
| 839 # | |
| 840 do_test savepoint-12.1 { | |
| 841 execsql { | |
| 842 CREATE TABLE t4(a PRIMARY KEY, b); | |
| 843 INSERT INTO t4 VALUES(1, 'one'); | |
| 844 } | |
| 845 } {} | |
| 846 do_test savepoint-12.2 { | |
| 847 # The final statement of the following SQL hits a constraint when the | |
| 848 # conflict handling mode is "OR ROLLBACK" and there are a couple of | |
| 849 # open savepoints. At one point this would fail to clear the internal | |
| 850 # record of the open savepoints, resulting in an assert() failure | |
| 851 # later on. | |
| 852 # | |
| 853 catchsql { | |
| 854 BEGIN; | |
| 855 INSERT INTO t4 VALUES(2, 'two'); | |
| 856 SAVEPOINT sp1; | |
| 857 INSERT INTO t4 VALUES(3, 'three'); | |
| 858 SAVEPOINT sp2; | |
| 859 INSERT OR ROLLBACK INTO t4 VALUES(1, 'one'); | |
| 860 } | |
| 861 } {1 {UNIQUE constraint failed: t4.a}} | |
| 862 do_test savepoint-12.3 { | |
| 863 sqlite3_get_autocommit db | |
| 864 } {1} | |
| 865 do_test savepoint-12.4 { | |
| 866 execsql { SAVEPOINT one } | |
| 867 } {} | |
| 868 wal_check_journal_mode savepoint-12.5 | |
| 869 | |
| 870 #------------------------------------------------------------------------- | |
| 871 # The following tests - savepoint-13.* - test the interaction of | |
| 872 # savepoints and "journal_mode = off". | |
| 873 # | |
| 874 if {[wal_is_wal_mode]==0} { | |
| 875 do_test savepoint-13.1 { | |
| 876 db close | |
| 877 catch {forcedelete test.db} | |
| 878 sqlite3 db test.db | |
| 879 execsql { | |
| 880 BEGIN; | |
| 881 CREATE TABLE t1(a PRIMARY KEY, b); | |
| 882 INSERT INTO t1 VALUES(1, 2); | |
| 883 COMMIT; | |
| 884 PRAGMA journal_mode = off; | |
| 885 } | |
| 886 } {off} | |
| 887 do_test savepoint-13.2 { | |
| 888 execsql { | |
| 889 BEGIN; | |
| 890 INSERT INTO t1 VALUES(3, 4); | |
| 891 INSERT INTO t1 SELECT a+4,b+4 FROM t1; | |
| 892 COMMIT; | |
| 893 } | |
| 894 } {} | |
| 895 do_test savepoint-13.3 { | |
| 896 execsql { | |
| 897 BEGIN; | |
| 898 INSERT INTO t1 VALUES(9, 10); | |
| 899 SAVEPOINT s1; | |
| 900 INSERT INTO t1 VALUES(11, 12); | |
| 901 COMMIT; | |
| 902 } | |
| 903 } {} | |
| 904 do_test savepoint-13.4 { | |
| 905 execsql { | |
| 906 BEGIN; | |
| 907 INSERT INTO t1 VALUES(13, 14); | |
| 908 SAVEPOINT s1; | |
| 909 INSERT INTO t1 VALUES(15, 16); | |
| 910 ROLLBACK TO s1; | |
| 911 ROLLBACK; | |
| 912 SELECT * FROM t1; | |
| 913 } | |
| 914 } {1 2 3 4 5 6 7 8 9 10 11 12} | |
| 915 } | |
| 916 | |
| 917 db close | |
| 918 delete_file test.db | |
| 919 do_multiclient_test tn { | |
| 920 do_test savepoint-14.$tn.1 { | |
| 921 sql1 { | |
| 922 CREATE TABLE foo(x); | |
| 923 INSERT INTO foo VALUES(1); | |
| 924 INSERT INTO foo VALUES(2); | |
| 925 } | |
| 926 sql2 { | |
| 927 BEGIN; | |
| 928 SELECT * FROM foo; | |
| 929 } | |
| 930 } {1 2} | |
| 931 do_test savepoint-14.$tn.2 { | |
| 932 sql1 { | |
| 933 SAVEPOINT one; | |
| 934 INSERT INTO foo VALUES(1); | |
| 935 } | |
| 936 csql1 { RELEASE one } | |
| 937 } {1 {database is locked}} | |
| 938 do_test savepoint-14.$tn.3 { | |
| 939 sql1 { ROLLBACK TO one } | |
| 940 sql2 { COMMIT } | |
| 941 sql1 { RELEASE one } | |
| 942 } {} | |
| 943 | |
| 944 do_test savepoint-14.$tn.4 { | |
| 945 sql2 { | |
| 946 BEGIN; | |
| 947 SELECT * FROM foo; | |
| 948 } | |
| 949 } {1 2} | |
| 950 do_test savepoint-14.$tn.5 { | |
| 951 sql1 { | |
| 952 SAVEPOINT one; | |
| 953 INSERT INTO foo VALUES(1); | |
| 954 } | |
| 955 csql1 { RELEASE one } | |
| 956 } {1 {database is locked}} | |
| 957 do_test savepoint-14.$tn.6 { | |
| 958 sql2 { COMMIT } | |
| 959 sql1 { | |
| 960 ROLLBACK TO one; | |
| 961 INSERT INTO foo VALUES(3); | |
| 962 INSERT INTO foo VALUES(4); | |
| 963 INSERT INTO foo VALUES(5); | |
| 964 RELEASE one; | |
| 965 } | |
| 966 } {} | |
| 967 do_test savepoint-14.$tn.7 { | |
| 968 sql2 { CREATE INDEX fooidx ON foo(x); } | |
| 969 sql3 { PRAGMA integrity_check } | |
| 970 } {ok} | |
| 971 } | |
| 972 | |
| 973 do_multiclient_test tn { | |
| 974 do_test savepoint-15.$tn.1 { | |
| 975 sql1 { | |
| 976 CREATE TABLE foo(x); | |
| 977 INSERT INTO foo VALUES(1); | |
| 978 INSERT INTO foo VALUES(2); | |
| 979 } | |
| 980 sql2 { BEGIN; SELECT * FROM foo; } | |
| 981 } {1 2} | |
| 982 do_test savepoint-15.$tn.2 { | |
| 983 sql1 { | |
| 984 PRAGMA locking_mode = EXCLUSIVE; | |
| 985 BEGIN; | |
| 986 INSERT INTO foo VALUES(3); | |
| 987 } | |
| 988 csql1 { COMMIT } | |
| 989 } {1 {database is locked}} | |
| 990 do_test savepoint-15.$tn.3 { | |
| 991 sql1 { ROLLBACK } | |
| 992 sql2 { COMMIT } | |
| 993 sql1 { | |
| 994 INSERT INTO foo VALUES(3); | |
| 995 PRAGMA locking_mode = NORMAL; | |
| 996 INSERT INTO foo VALUES(4); | |
| 997 } | |
| 998 sql2 { CREATE INDEX fooidx ON foo(x); } | |
| 999 sql3 { PRAGMA integrity_check } | |
| 1000 } {ok} | |
| 1001 } | |
| 1002 | |
| 1003 do_multiclient_test tn { | |
| 1004 do_test savepoint-16.$tn.1 { | |
| 1005 sql1 { | |
| 1006 CREATE TABLE foo(x); | |
| 1007 INSERT INTO foo VALUES(1); | |
| 1008 INSERT INTO foo VALUES(2); | |
| 1009 } | |
| 1010 } {} | |
| 1011 do_test savepoint-16.$tn.2 { | |
| 1012 | |
| 1013 db eval {SELECT * FROM foo} { | |
| 1014 sql1 { INSERT INTO foo VALUES(3) } | |
| 1015 sql2 { SELECT * FROM foo } | |
| 1016 sql1 { INSERT INTO foo VALUES(4) } | |
| 1017 break | |
| 1018 } | |
| 1019 | |
| 1020 sql2 { CREATE INDEX fooidx ON foo(x); } | |
| 1021 sql3 { PRAGMA integrity_check } | |
| 1022 } {ok} | |
| 1023 do_test savepoint-16.$tn.3 { | |
| 1024 sql1 { SELECT * FROM foo } | |
| 1025 } {1 2 3 4} | |
| 1026 } | |
| 1027 | |
| 1028 #------------------------------------------------------------------------- | |
| 1029 # This next block of tests verifies that a problem reported on the mailing | |
| 1030 # list has been resolved. At one point the second "CREATE TABLE t6" would | |
| 1031 # fail as table t6 still existed in the internal cache of the db schema | |
| 1032 # (even though it had been removed from the database by the ROLLBACK | |
| 1033 # command). | |
| 1034 # | |
| 1035 sqlite3 db test.db | |
| 1036 do_execsql_test savepoint-17.1 { | |
| 1037 BEGIN; | |
| 1038 CREATE TABLE t6(a, b); | |
| 1039 INSERT INTO t6 VALUES(1, 2); | |
| 1040 SAVEPOINT one; | |
| 1041 INSERT INTO t6 VALUES(3, 4); | |
| 1042 ROLLBACK TO one; | |
| 1043 SELECT * FROM t6; | |
| 1044 ROLLBACK; | |
| 1045 } {1 2} | |
| 1046 | |
| 1047 do_execsql_test savepoint-17.2 { | |
| 1048 CREATE TABLE t6(a, b); | |
| 1049 } {} | |
| 1050 | |
| 1051 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2363173002:
[sqlite] Remove obsolete reference version 3.8.7.4. (Closed)
