1# 2014 August 30 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 13source [file join [file dirname [info script]] rbu_common.tcl] 14set ::testprefix rbu1 15 16db close 17sqlite3_shutdown 18sqlite3_config_uri 1 19 20# Create a simple RBU database. That expects to write to a table: 21# 22# CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 23# 24proc create_rbu1 {filename} { 25 forcedelete $filename 26 sqlite3 rbu1 $filename 27 rbu1 eval { 28 CREATE TABLE data_t1(a, b, c, rbu_control); 29 INSERT INTO data_t1 VALUES(1, 2, 3, 0); 30 INSERT INTO data_t1 VALUES(2, 'two', 'three', 0); 31 INSERT INTO data_t1 VALUES(3, NULL, 8.2, 0); 32 } 33 rbu1 close 34 return $filename 35} 36 37# Create a simple RBU database. That expects to write to a table: 38# 39# CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 40# 41# This RBU includes both insert and delete operations. 42# 43proc create_rbu4 {filename} { 44 forcedelete $filename 45 sqlite3 rbu1 $filename 46 rbu1 eval { 47 CREATE TABLE data_t1(a, b, c, rbu_control); 48 INSERT INTO data_t1 VALUES(1, 2, 3, 0); 49 INSERT INTO data_t1 VALUES(2, NULL, 5, 1); 50 INSERT INTO data_t1 VALUES(3, 8, 9, 0); 51 INSERT INTO data_t1 VALUES(4, NULL, 11, 1); 52 } 53 rbu1 close 54 return $filename 55} 56# 57# Create a simple RBU database. That expects to write to a table: 58# 59# CREATE TABLE t1(c, b, '(a)' INTEGER PRIMARY KEY); 60# 61# This RBU includes both insert and delete operations. 62# 63proc create_rbu4b {filename} { 64 forcedelete $filename 65 sqlite3 rbu1 $filename 66 rbu1 eval { 67 CREATE TABLE data_t1(c, b, '(a)', rbu_control); 68 INSERT INTO data_t1 VALUES(3, 2, 1, 0); 69 INSERT INTO data_t1 VALUES(5, NULL, 2, 1); 70 INSERT INTO data_t1 VALUES(9, 8, 3, 0); 71 INSERT INTO data_t1 VALUES(11, NULL, 4, 1); 72 } 73 rbu1 close 74 return $filename 75} 76 77# Create a simple RBU database. That expects to write to a table: 78# 79# CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d); 80# 81# This RBU includes update statements. 82# 83proc create_rbu5 {filename} { 84 forcedelete $filename 85 sqlite3 rbu5 $filename 86 rbu5 eval { 87 CREATE TABLE data_t1(a, b, c, d, rbu_control); 88 INSERT INTO data_t1 VALUES(1, NULL, NULL, 5, '...x'); -- SET d = 5 89 INSERT INTO data_t1 VALUES(2, NULL, 10, 5, '..xx'); -- SET c=10, d = 5 90 INSERT INTO data_t1 VALUES(3, 11, NULL, NULL, '.x..'); -- SET b=11 91 } 92 rbu5 close 93 return $filename 94} 95 96 97# Same as [step_rbu], except using a URI to open the target db. 98# 99proc step_rbu_uri {target rbu} { 100 while 1 { 101 sqlite3rbu rbu file:$target?xyz=&abc=123 $rbu 102 set rc [rbu step] 103 rbu close 104 if {$rc != "SQLITE_OK"} break 105 } 106 set rc 107} 108 109# Same as [step_rbu], except using an external state database - "state.db" 110# 111proc step_rbu_state {target rbu} { 112 while 1 { 113 sqlite3rbu rbu $target $rbu state.db 114 set rc [rbu step] 115 rbu close 116 if {$rc != "SQLITE_OK"} break 117 } 118 set rc 119} 120 121proc dbfilecksum {file} { 122 sqlite3 ck $file 123 set cksum [dbcksum ck main] 124 ck close 125 set cksum 126} 127 128foreach {tn3 create_vfs destroy_vfs} { 129 1 {} {} 130 2 { 131 sqlite3rbu_create_vfs -default myrbu "" 132 } { 133 sqlite3rbu_destroy_vfs myrbu 134 } 135 3 { 136 sqlite3_register_cksumvfs 137 } { 138 sqlite3_unregister_cksumvfs 139 } 140} { 141 142 eval $create_vfs 143 144 foreach {tn2 cmd} { 145 1 run_rbu 146 2 step_rbu 3 step_rbu_uri 4 step_rbu_state 147 5 step_rbu_legacy 148 } { 149 foreach {tn schema} { 150 1 { 151 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 152 } 153 2 { 154 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 155 CREATE INDEX i1 ON t1(b); 156 } 157 3 { 158 CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID; 159 } 160 4 { 161 CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID; 162 CREATE INDEX i1 ON t1(b); 163 } 164 5 { 165 CREATE TABLE t1(a, b, c, PRIMARY KEY(a, c)) WITHOUT ROWID; 166 CREATE INDEX i1 ON t1(b); 167 } 168 6 { 169 CREATE TABLE t1(a, b, c, PRIMARY KEY(c)) WITHOUT ROWID; 170 CREATE INDEX i1 ON t1(b, a); 171 } 172 7 { 173 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 174 CREATE INDEX i1 ON t1(b, c); 175 CREATE INDEX i2 ON t1(c, b); 176 CREATE INDEX i3 ON t1(a, b, c, a, b, c); 177 } 178 179 8 { 180 CREATE TABLE t1(a PRIMARY KEY, b, c); 181 CREATE INDEX i1 ON t1(b, c); 182 CREATE INDEX i2 ON t1(c, b); 183 CREATE INDEX i3 ON t1(a, b, c, a, b, c); 184 } 185 186 9 { 187 CREATE TABLE t1(a, b, c, PRIMARY KEY(a, c)); 188 CREATE INDEX i1 ON t1(b); 189 } 190 191 10 { 192 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 193 CREATE INDEX i1 ON t1(b DESC); 194 } 195 196 11 { 197 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 198 CREATE INDEX i1 ON t1(b DESC, a ASC, c DESC); 199 } 200 201 12 { 202 CREATE TABLE t1(a INT PRIMARY KEY DESC, b, c) WITHOUT ROWID; 203 } 204 205 13 { 206 CREATE TABLE t1(a INT, b, c, PRIMARY KEY(a DESC)) WITHOUT ROWID; 207 } 208 209 14 { 210 CREATE TABLE t1(a, b, c, PRIMARY KEY(a DESC, c)) WITHOUT ROWID; 211 CREATE INDEX i1 ON t1(b); 212 } 213 214 15 { 215 CREATE TABLE t1(a, b, c, PRIMARY KEY(a, c DESC)) WITHOUT ROWID; 216 CREATE INDEX i1 ON t1(b); 217 } 218 219 16 { 220 CREATE TABLE t1(a, b, c, PRIMARY KEY(c DESC, a)) WITHOUT ROWID; 221 CREATE INDEX i1 ON t1(b DESC, c, a); 222 } 223 } { 224 reset_db 225 execsql $schema 226 create_rbu1 rbu.db 227 set check [dbfilecksum rbu.db] 228 forcedelete state.db 229 230 do_test $tn3.1.$tn2.$tn.1 { 231 $cmd test.db rbu.db 232 } {SQLITE_DONE} 233 234 do_execsql_test $tn3.1.$tn2.$tn.2 { SELECT * FROM t1 ORDER BY a ASC } { 235 1 2 3 236 2 two three 237 3 {} 8.2 238 } 239 do_execsql_test $tn3.1.$tn2.$tn.3 { SELECT * FROM t1 ORDER BY b ASC } { 240 3 {} 8.2 241 1 2 3 242 2 two three 243 } 244 do_execsql_test $tn3.1.$tn2.$tn.4 { SELECT * FROM t1 ORDER BY c ASC } { 245 1 2 3 246 3 {} 8.2 247 2 two three 248 } 249 250 do_execsql_test $tn3.1.$tn2.$tn.5 { PRAGMA integrity_check } ok 251 252 if {$cmd=="step_rbu_state"} { 253 do_test $tn3.1.$tn2.$tn.6 { file exists state.db } 1 254 do_test $tn3.1.$tn2.$tn.7 { expr {$check == [dbfilecksum rbu.db]} } 1 255 } else { 256 do_test $tn3.1.$tn2.$tn.8 { file exists state.db } 0 257 do_test $tn3.1.$tn2.$tn.9 { expr {$check == [dbfilecksum rbu.db]} } 0 258 } 259 } 260 } 261 262 #------------------------------------------------------------------------- 263 # Check that an RBU cannot be applied to a table that has no PK. 264 # 265 # UPDATE: At one point RBU required that all tables featured either 266 # explicit IPK columns or were declared WITHOUT ROWID. This has been 267 # relaxed so that external PRIMARY KEYs on tables with automatic rowids 268 # are now allowed. 269 # 270 # UPDATE 2: Tables without any PRIMARY KEY declaration are now allowed. 271 # However the input table must feature an "rbu_rowid" column. 272 # 273 reset_db 274 create_rbu1 rbu.db 275 do_execsql_test $tn3.2.1 { CREATE TABLE t1(a, b, c) } 276 do_test $tn3.2.2 { 277 sqlite3rbu rbu test.db rbu.db 278 rbu step 279 } {SQLITE_ERROR} 280 do_test $tn3.2.3 { 281 list [catch { rbu close } msg] $msg 282 } {1 {SQLITE_ERROR - table data_t1 requires rbu_rowid column}} 283 reset_db 284 do_execsql_test $tn3.2.4 { CREATE TABLE t1(a PRIMARY KEY, b, c) } 285 do_test $tn3.2.5 { 286 sqlite3rbu rbu test.db rbu.db 287 rbu step 288 } {SQLITE_OK} 289 do_test $tn3.2.6 { 290 list [catch { rbu close } msg] $msg 291 } {0 SQLITE_OK} 292 293 #------------------------------------------------------------------------- 294 # Check that if a UNIQUE constraint is violated the current and all 295 # subsequent [rbu step] calls return SQLITE_CONSTRAINT. And that the RBU 296 # transaction is rolled back by the [rbu close] that deletes the rbu 297 # handle. 298 # 299 foreach {tn errcode errmsg schema} { 300 1 SQLITE_CONSTRAINT "UNIQUE constraint failed: t1.a" { 301 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 302 INSERT INTO t1 VALUES(3, 2, 1); 303 } 304 305 2 SQLITE_CONSTRAINT "UNIQUE constraint failed: t1.c" { 306 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c UNIQUE); 307 INSERT INTO t1 VALUES(4, 2, 'three'); 308 } 309 310 3 SQLITE_CONSTRAINT "UNIQUE constraint failed: t1.a" { 311 CREATE TABLE t1(a PRIMARY KEY, b, c); 312 INSERT INTO t1 VALUES(3, 2, 1); 313 } 314 315 4 SQLITE_CONSTRAINT "UNIQUE constraint failed: t1.c" { 316 CREATE TABLE t1(a PRIMARY KEY, b, c UNIQUE); 317 INSERT INTO t1 VALUES(4, 2, 'three'); 318 } 319 320 } { 321 reset_db 322 execsql $schema 323 set cksum [dbcksum db main] 324 325 do_test $tn3.3.$tn.1 { 326 create_rbu1 rbu.db 327 sqlite3rbu rbu test.db rbu.db 328 while {[set res [rbu step]]=="SQLITE_OK"} {} 329 set res 330 } $errcode 331 332 do_test $tn3.3.$tn.2 { rbu step } $errcode 333 334 do_test $tn3.3.$tn.3 { 335 list [catch { rbu close } msg] $msg 336 } [list 1 "$errcode - $errmsg"] 337 338 do_test $tn3.3.$tn.4 { dbcksum db main } $cksum 339 } 340 341 #------------------------------------------------------------------------- 342 # 343 foreach {tn2 cmd} {1 run_rbu 2 step_rbu 3 step_rbu_state } { 344 foreach {tn schema} { 345 1 { 346 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 347 } 348 2 { 349 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 350 CREATE INDEX i1 ON t1(b); 351 } 352 3 { 353 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 354 CREATE INDEX i1 ON t1(b); 355 CREATE INDEX i2 ON t1(c, b); 356 CREATE INDEX i3 ON t1(c, b, c); 357 } 358 4 { 359 CREATE TABLE t1(a INT PRIMARY KEY, b, c) WITHOUT ROWID; 360 CREATE INDEX i1 ON t1(b); 361 CREATE INDEX i2 ON t1(c, b); 362 CREATE INDEX i3 ON t1(c, b, c); 363 } 364 5 { 365 CREATE TABLE t1(a INT PRIMARY KEY, b, c); 366 CREATE INDEX i1 ON t1(b); 367 CREATE INDEX i2 ON t1(c, b); 368 CREATE INDEX i3 ON t1(c, b, c); 369 } 370 371 6 { 372 CREATE TABLE t1(a INT PRIMARY KEY DESC, b, c); 373 CREATE INDEX i1 ON t1(b DESC); 374 CREATE INDEX i2 ON t1(c, b); 375 CREATE INDEX i3 ON t1(c DESC, b, c); 376 } 377 7 { 378 CREATE TABLE t1(a INT PRIMARY KEY DESC, b, c) WITHOUT ROWID; 379 CREATE INDEX i1 ON t1(b); 380 CREATE INDEX i2 ON t1(c, b); 381 CREATE INDEX i3 ON t1(c, b, c); 382 } 383 } { 384 reset_db 385 execsql $schema 386 execsql { 387 INSERT INTO t1 VALUES(2, 'hello', 'world'); 388 INSERT INTO t1 VALUES(4, 'hello', 'planet'); 389 INSERT INTO t1 VALUES(6, 'hello', 'xyz'); 390 } 391 392 create_rbu4 rbu.db 393 set check [dbfilecksum rbu.db] 394 forcedelete state.db 395 396 do_test $tn3.4.$tn2.$tn.1 { 397 $cmd test.db rbu.db 398 } {SQLITE_DONE} 399 400 do_execsql_test $tn3.4.$tn2.$tn.2 { 401 SELECT * FROM t1 ORDER BY a ASC; 402 } { 403 1 2 3 404 3 8 9 405 6 hello xyz 406 } 407 408 do_execsql_test $tn3.4.$tn2.$tn.3 { PRAGMA integrity_check } ok 409 410 if {$cmd=="step_rbu_state"} { 411 do_test $tn3.4.$tn2.$tn.4 { file exists state.db } 1 412 do_test $tn3.4.$tn2.$tn.5 { expr {$check == [dbfilecksum rbu.db]} } 1 413 } else { 414 do_test $tn3.4.$tn2.$tn.6 { file exists state.db } 0 415 do_test $tn3.4.$tn2.$tn.7 { expr {$check == [dbfilecksum rbu.db]} } 0 416 } 417 } 418 } 419 420 foreach {tn2 cmd} {1 run_rbu 2 step_rbu 3 step_rbu_state} { 421 foreach {tn schema} { 422 1 { 423 CREATE TABLE t1(c, b, '(a)' INTEGER PRIMARY KEY); 424 CREATE INDEX i1 ON t1(c, b); 425 } 426 2 { 427 CREATE TABLE t1(c, b, '(a)' PRIMARY KEY); 428 } 429 3 { 430 CREATE TABLE t1(c, b, '(a)' PRIMARY KEY) WITHOUT ROWID; 431 } 432 } { 433 reset_db 434 execsql $schema 435 execsql { 436 INSERT INTO t1('(a)', b, c) VALUES(2, 'hello', 'world'); 437 INSERT INTO t1('(a)', b, c) VALUES(4, 'hello', 'planet'); 438 INSERT INTO t1('(a)', b, c) VALUES(6, 'hello', 'xyz'); 439 } 440 441 create_rbu4b rbu.db 442 set check [dbfilecksum rbu.db] 443 forcedelete state.db 444 445 do_test $tn3.5.$tn2.$tn.1 { 446 $cmd test.db rbu.db 447 } {SQLITE_DONE} 448 449 do_execsql_test $tn3.5.$tn2.$tn.2 { 450 SELECT * FROM t1 ORDER BY "(a)" ASC; 451 } { 452 3 2 1 453 9 8 3 454 xyz hello 6 455 } 456 457 do_execsql_test $tn3.4.$tn2.$tn.3 { PRAGMA integrity_check } ok 458 459 if {$cmd=="step_rbu_state"} { 460 do_test $tn3.5.$tn2.$tn.4 { file exists state.db } 1 461 do_test $tn3.5.$tn2.$tn.5 { expr {$check == [dbfilecksum rbu.db]} } 1 462 } else { 463 do_test $tn3.5.$tn2.$tn.6 { file exists state.db } 0 464 do_test $tn3.5.$tn2.$tn.7 { expr {$check == [dbfilecksum rbu.db]} } 0 465 } 466 } 467 } 468 469 #------------------------------------------------------------------------- 470 # 471 foreach {tn2 cmd} {1 run_rbu 2 step_rbu 3 step_rbu_state} { 472 foreach {tn schema} { 473 1 { 474 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d); 475 } 476 2 { 477 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d); 478 CREATE INDEX i1 ON t1(d); 479 CREATE INDEX i2 ON t1(d, c); 480 CREATE INDEX i3 ON t1(d, c, b); 481 CREATE INDEX i4 ON t1(b); 482 CREATE INDEX i5 ON t1(c); 483 CREATE INDEX i6 ON t1(c, b); 484 } 485 3 { 486 CREATE TABLE t1(a PRIMARY KEY, b, c, d) WITHOUT ROWID; 487 CREATE INDEX i1 ON t1(d); 488 CREATE INDEX i2 ON t1(d, c); 489 CREATE INDEX i3 ON t1(d, c, b); 490 CREATE INDEX i4 ON t1(b); 491 CREATE INDEX i5 ON t1(c); 492 CREATE INDEX i6 ON t1(c, b); 493 } 494 4 { 495 CREATE TABLE t1(a PRIMARY KEY, b, c, d); 496 CREATE INDEX i1 ON t1(d); 497 CREATE INDEX i2 ON t1(d, c); 498 CREATE INDEX i3 ON t1(d, c, b); 499 CREATE INDEX i4 ON t1(b); 500 CREATE INDEX i5 ON t1(c); 501 CREATE INDEX i6 ON t1(c, b); 502 } 503 } { 504 reset_db 505 execsql $schema 506 execsql { 507 INSERT INTO t1 VALUES(1, 2, 3, 4); 508 INSERT INTO t1 VALUES(2, 5, 6, 7); 509 INSERT INTO t1 VALUES(3, 8, 9, 10); 510 } 511 512 create_rbu5 rbu.db 513 set check [dbfilecksum rbu.db] 514 forcedelete state.db 515 516 do_test $tn3.5.$tn2.$tn.1 { 517 $cmd test.db rbu.db 518 } {SQLITE_DONE} 519 520 do_execsql_test $tn3.5.$tn2.$tn.2 { 521 SELECT * FROM t1 ORDER BY a ASC; 522 } { 523 1 2 3 5 524 2 5 10 5 525 3 11 9 10 526 } 527 528 do_execsql_test $tn3.6.$tn2.$tn.3 { PRAGMA integrity_check } ok 529 530 if {$cmd=="step_rbu_state"} { 531 do_test $tn3.6.$tn2.$tn.4 { file exists state.db } 1 532 do_test $tn3.6.$tn2.$tn.5 { expr {$check == [dbfilecksum rbu.db]} } 1 533 } else { 534 do_test $tn3.6.$tn2.$tn.6 { file exists state.db } 0 535 do_test $tn3.6.$tn2.$tn.7 { expr {$check == [dbfilecksum rbu.db]} } 0 536 } 537 } 538 } 539 540 #------------------------------------------------------------------------- 541 # Test some error cases: 542 # 543 # * A virtual table with no rbu_rowid column. 544 # * A no-PK table with no rbu_rowid column. 545 # * A PK table with an rbu_rowid column. 546 # 547 # 6: An update string of the wrong length 548 # 549 ifcapable fts3 { 550 foreach {tn schema error} { 551 1 { 552 CREATE TABLE t1(a, b); 553 CREATE TABLE rbu.data_t1(a, b, rbu_control); 554 } {SQLITE_ERROR - table data_t1 requires rbu_rowid column} 555 556 2 { 557 CREATE VIRTUAL TABLE t1 USING fts4(a, b); 558 CREATE TABLE rbu.data_t1(a, b, rbu_control); 559 } {SQLITE_ERROR - table data_t1 requires rbu_rowid column} 560 561 3 { 562 CREATE TABLE t1(a PRIMARY KEY, b); 563 CREATE TABLE rbu.data_t1(a, b, rbu_rowid, rbu_control); 564 } {SQLITE_ERROR - table data_t1 may not have rbu_rowid column} 565 566 4 { 567 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 568 CREATE TABLE rbu.data_t1(a, b, rbu_rowid, rbu_control); 569 } {SQLITE_ERROR - table data_t1 may not have rbu_rowid column} 570 571 5 { 572 CREATE TABLE t1(a, b PRIMARY KEY) WITHOUT ROWID; 573 CREATE TABLE rbu.data_t1(a, b, rbu_rowid, rbu_control); 574 } {SQLITE_ERROR - table data_t1 may not have rbu_rowid column} 575 576 6 { 577 CREATE TABLE t1(a, b PRIMARY KEY) WITHOUT ROWID; 578 CREATE TABLE rbu.data_t1(a, b, rbu_control); 579 INSERT INTO rbu.data_t1 VALUES(1, 2, 'x.x'); 580 } {SQLITE_ERROR - invalid rbu_control value} 581 582 7 { 583 CREATE TABLE t1(a, b PRIMARY KEY) WITHOUT ROWID; 584 CREATE TABLE rbu.data_t1(a, b, rbu_control); 585 INSERT INTO rbu.data_t1 VALUES(1, 2, NULL); 586 } {SQLITE_ERROR - invalid rbu_control value} 587 588 8 { 589 CREATE TABLE t1(a, b PRIMARY KEY) WITHOUT ROWID; 590 CREATE TABLE rbu.data_t1(a, b, rbu_control); 591 INSERT INTO rbu.data_t1 VALUES(1, 2, 4); 592 } {SQLITE_ERROR - invalid rbu_control value} 593 594 9 { 595 CREATE TABLE t1(a, b PRIMARY KEY) WITHOUT ROWID; 596 CREATE TABLE rbu.data_t1(a, b, rbu_control); 597 INSERT INTO rbu.data_t1 VALUES(1, 2, 3); 598 } {SQLITE_ERROR - invalid rbu_control value} 599 600 10 { 601 CREATE TABLE t2(a, b); 602 CREATE TABLE rbu.data_t1(a, b, rbu_control); 603 INSERT INTO rbu.data_t1 VALUES(1, 2, 2); 604 } {SQLITE_ERROR - no such table: t1} 605 606 11 { 607 CREATE TABLE rbu.data_t2(a, b, rbu_control); 608 INSERT INTO rbu.data_t2 VALUES(1, 2, 2); 609 } {SQLITE_ERROR - no such table: t2} 610 611 } { 612 reset_db 613 forcedelete rbu.db 614 execsql { ATTACH 'rbu.db' AS rbu } 615 execsql $schema 616 617 do_test $tn3.7.$tn { 618 list [catch { run_rbu test.db rbu.db } msg] $msg 619 } [list 1 $error] 620 } 621 } 622 623 # Test that an RBU database containing no input tables is handled 624 # correctly. 625 reset_db 626 forcedelete rbu.db 627 do_test $tn3.8.1 { 628 list [catch { run_rbu test.db rbu.db } msg] $msg 629 } {0 SQLITE_DONE} 630 631 # Test that an RBU database containing only empty data_xxx tables is 632 # also handled correctly. 633 reset_db 634 forcedelete rbu.db 635 do_execsql_test $tn3.8.2.1 { 636 CREATE TABLE t1(a PRIMARY KEY, b); 637 INSERT INTO t1 VALUES(1, 2); 638 ATTACH 'rbu.db' AS rbu; 639 CREATE TABLE data_t1(a, b, rbu_control); 640 DETACH rbu; 641 } 642 do_test $tn3.8.2.1 { 643 list [catch { run_rbu test.db rbu.db } msg] $msg 644 } {0 SQLITE_DONE} 645 646 # Test that RBU can update indexes containing NULL values. 647 # 648 reset_db 649 forcedelete rbu.db 650 do_execsql_test $tn3.9.1 { 651 CREATE TABLE t1(a PRIMARY KEY, b, c); 652 CREATE INDEX i1 ON t1(b, c); 653 INSERT INTO t1 VALUES(1, 1, NULL); 654 INSERT INTO t1 VALUES(2, NULL, 2); 655 INSERT INTO t1 VALUES(3, NULL, NULL); 656 657 ATTACH 'rbu.db' AS rbu; 658 CREATE TABLE rbu.data_t1(a, b, c, rbu_control); 659 INSERT INTO data_t1 VALUES(1, NULL, NULL, 1); 660 INSERT INTO data_t1 VALUES(3, NULL, NULL, 1); 661 } {} 662 663 do_test $tn3.9.2 { 664 list [catch { run_rbu test.db rbu.db } msg] $msg 665 } {0 SQLITE_DONE} 666 667 do_execsql_test $tn3.9.3 { 668 SELECT * FROM t1 669 } {2 {} 2} 670 do_execsql_test $tn3.9.4 { PRAGMA integrity_check } {ok} 671 672 catch { db close } 673 eval $destroy_vfs 674} 675 676 677finish_test 678