gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/autoinc.test (about) 1 # 2004 November 12 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 testing the AUTOINCREMENT features. 13 # 14 # $Id: autoinc.test,v 1.14 2009/06/23 20:28:54 drh Exp $ 15 # 16 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 set testprefix autoinc 20 21 # If the library is not compiled with autoincrement support then 22 # skip all tests in this file. 23 # 24 ifcapable {!autoinc} { 25 finish_test 26 return 27 } 28 29 if {[permutation]=="inmemory_journal"} { 30 finish_test 31 return 32 } 33 34 sqlite3_db_config_lookaside db 0 0 0 35 36 # The database is initially empty. 37 # 38 do_test autoinc-1.1 { 39 execsql { 40 SELECT name FROM sqlite_master WHERE type='table'; 41 } 42 } {} 43 44 # Add a table with the AUTOINCREMENT feature. Verify that the 45 # SQLITE_SEQUENCE table gets created. 46 # 47 do_test autoinc-1.2 { 48 execsql { 49 CREATE TABLE t1(x INTEGER PRIMARY KEY AUTOINCREMENT, y); 50 SELECT name FROM sqlite_master WHERE type='table'; 51 } 52 } {t1 sqlite_sequence} 53 54 # The SQLITE_SEQUENCE table is initially empty 55 # 56 do_test autoinc-1.3 { 57 execsql { 58 SELECT * FROM sqlite_sequence; 59 } 60 } {} 61 do_test autoinc-1.3.1 { 62 catchsql { 63 CREATE INDEX seqidx ON sqlite_sequence(name) 64 } 65 } {1 {table sqlite_sequence may not be indexed}} 66 67 # Close and reopen the database. Verify that everything is still there. 68 # 69 do_test autoinc-1.4 { 70 db close 71 sqlite3 db test.db 72 execsql { 73 SELECT * FROM sqlite_sequence; 74 } 75 } {} 76 77 # We are not allowed to drop the sqlite_sequence table. 78 # 79 do_test autoinc-1.5 { 80 catchsql {DROP TABLE sqlite_sequence} 81 } {1 {table sqlite_sequence may not be dropped}} 82 do_test autoinc-1.6 { 83 execsql {SELECT name FROM sqlite_master WHERE type='table'} 84 } {t1 sqlite_sequence} 85 86 # Insert an entries into the t1 table and make sure the largest key 87 # is always recorded in the sqlite_sequence table. 88 # 89 do_test autoinc-2.1 { 90 execsql { 91 SELECT * FROM sqlite_sequence 92 } 93 } {} 94 do_test autoinc-2.2 { 95 execsql { 96 INSERT INTO t1 VALUES(12,34); 97 SELECT * FROM sqlite_sequence; 98 } 99 } {t1 12} 100 do_test autoinc-2.3 { 101 execsql { 102 INSERT INTO t1 VALUES(1,23); 103 SELECT * FROM sqlite_sequence; 104 } 105 } {t1 12} 106 do_test autoinc-2.4 { 107 execsql { 108 INSERT INTO t1 VALUES(123,456); 109 SELECT * FROM sqlite_sequence; 110 } 111 } {t1 123} 112 do_test autoinc-2.5 { 113 execsql { 114 INSERT INTO t1 VALUES(NULL,567); 115 SELECT * FROM sqlite_sequence; 116 } 117 } {t1 124} 118 do_test autoinc-2.6 { 119 execsql { 120 DELETE FROM t1 WHERE y=567; 121 SELECT * FROM sqlite_sequence; 122 } 123 } {t1 124} 124 do_test autoinc-2.7 { 125 execsql { 126 INSERT INTO t1 VALUES(NULL,567); 127 SELECT * FROM sqlite_sequence; 128 } 129 } {t1 125} 130 do_test autoinc-2.8 { 131 execsql { 132 DELETE FROM t1; 133 SELECT * FROM sqlite_sequence; 134 } 135 } {t1 125} 136 do_test autoinc-2.9 { 137 execsql { 138 INSERT INTO t1 VALUES(12,34); 139 SELECT * FROM sqlite_sequence; 140 } 141 } {t1 125} 142 do_test autoinc-2.10 { 143 execsql { 144 INSERT INTO t1 VALUES(125,456); 145 SELECT * FROM sqlite_sequence; 146 } 147 } {t1 125} 148 do_test autoinc-2.11 { 149 execsql { 150 INSERT INTO t1 VALUES(-1234567,-1); 151 SELECT * FROM sqlite_sequence; 152 } 153 } {t1 125} 154 do_test autoinc-2.12 { 155 execsql { 156 INSERT INTO t1 VALUES(234,5678); 157 SELECT * FROM sqlite_sequence; 158 } 159 } {t1 234} 160 do_test autoinc-2.13 { 161 execsql { 162 DELETE FROM t1; 163 INSERT INTO t1 VALUES(NULL,1); 164 SELECT * FROM sqlite_sequence; 165 } 166 } {t1 235} 167 do_test autoinc-2.14 { 168 execsql { 169 SELECT * FROM t1; 170 } 171 } {235 1} 172 173 # Manually change the autoincrement values in sqlite_sequence. 174 # 175 do_test autoinc-2.20 { 176 execsql { 177 UPDATE sqlite_sequence SET seq=1234 WHERE name='t1'; 178 INSERT INTO t1 VALUES(NULL,2); 179 SELECT * FROM t1; 180 } 181 } {235 1 1235 2} 182 do_test autoinc-2.21 { 183 execsql { 184 SELECT * FROM sqlite_sequence; 185 } 186 } {t1 1235} 187 do_test autoinc-2.22 { 188 execsql { 189 UPDATE sqlite_sequence SET seq=NULL WHERE name='t1'; 190 INSERT INTO t1 VALUES(NULL,3); 191 SELECT * FROM t1; 192 } 193 } {235 1 1235 2 1236 3} 194 do_test autoinc-2.23 { 195 execsql { 196 SELECT * FROM sqlite_sequence; 197 } 198 } {t1 1236} 199 do_test autoinc-2.24 { 200 execsql { 201 UPDATE sqlite_sequence SET seq='a-string' WHERE name='t1'; 202 INSERT INTO t1 VALUES(NULL,4); 203 SELECT * FROM t1; 204 } 205 } {235 1 1235 2 1236 3 1237 4} 206 do_test autoinc-2.25 { 207 execsql { 208 SELECT * FROM sqlite_sequence; 209 } 210 } {t1 1237} 211 do_test autoinc-2.26 { 212 execsql { 213 DELETE FROM sqlite_sequence WHERE name='t1'; 214 INSERT INTO t1 VALUES(NULL,5); 215 SELECT * FROM t1; 216 } 217 } {235 1 1235 2 1236 3 1237 4 1238 5} 218 do_test autoinc-2.27 { 219 execsql { 220 SELECT * FROM sqlite_sequence; 221 } 222 } {t1 1238} 223 do_test autoinc-2.28 { 224 execsql { 225 UPDATE sqlite_sequence SET seq='-12345678901234567890' 226 WHERE name='t1'; 227 INSERT INTO t1 VALUES(NULL,6); 228 SELECT * FROM t1; 229 } 230 } {235 1 1235 2 1236 3 1237 4 1238 5 1239 6} 231 do_test autoinc-2.29 { 232 execsql { 233 SELECT * FROM sqlite_sequence; 234 } 235 } {t1 1239} 236 237 # Test multi-row inserts 238 # 239 do_test autoinc-2.50 { 240 execsql { 241 DELETE FROM t1 WHERE y>=3; 242 INSERT INTO t1 SELECT NULL, y+2 FROM t1; 243 SELECT * FROM t1; 244 } 245 } {235 1 1235 2 1240 3 1241 4} 246 do_test autoinc-2.51 { 247 execsql { 248 SELECT * FROM sqlite_sequence 249 } 250 } {t1 1241} 251 252 ifcapable tempdb { 253 do_test autoinc-2.52 { 254 execsql { 255 CREATE TEMP TABLE t2 AS SELECT y FROM t1; 256 } 257 execsql { 258 INSERT INTO t1 SELECT NULL, y+4 FROM t2; 259 SELECT * FROM t1; 260 } 261 } {235 1 1235 2 1240 3 1241 4 1242 5 1243 6 1244 7 1245 8} 262 do_test autoinc-2.53 { 263 execsql { 264 SELECT * FROM sqlite_sequence 265 } 266 } {t1 1245} 267 do_test autoinc-2.54 { 268 execsql { 269 DELETE FROM t1; 270 INSERT INTO t1 SELECT NULL, y FROM t2; 271 SELECT * FROM t1; 272 } 273 } {1246 1 1247 2 1248 3 1249 4} 274 do_test autoinc-2.55 { 275 execsql { 276 SELECT * FROM sqlite_sequence 277 } 278 } {t1 1249} 279 } 280 281 # Create multiple AUTOINCREMENT tables. Make sure all sequences are 282 # tracked separately and do not interfere with one another. 283 # 284 do_test autoinc-2.70 { 285 catchsql { 286 DROP TABLE t2; 287 } 288 execsql { 289 CREATE TABLE t2(d, e INTEGER PRIMARY KEY AUTOINCREMENT, f); 290 INSERT INTO t2(d) VALUES(1); 291 SELECT * FROM sqlite_sequence; 292 } 293 } [ifcapable tempdb {list t1 1249 t2 1} else {list t1 1241 t2 1}] 294 do_test autoinc-2.71 { 295 execsql { 296 INSERT INTO t2(d) VALUES(2); 297 SELECT * FROM sqlite_sequence; 298 } 299 } [ifcapable tempdb {list t1 1249 t2 2} else {list t1 1241 t2 2}] 300 do_test autoinc-2.72 { 301 execsql { 302 INSERT INTO t1(x) VALUES(10000); 303 SELECT * FROM sqlite_sequence; 304 } 305 } {t1 10000 t2 2} 306 do_test autoinc-2.73 { 307 execsql { 308 CREATE TABLE t3(g INTEGER PRIMARY KEY AUTOINCREMENT, h); 309 INSERT INTO t3(h) VALUES(1); 310 SELECT * FROM sqlite_sequence; 311 } 312 } {t1 10000 t2 2 t3 1} 313 do_test autoinc-2.74 { 314 execsql { 315 INSERT INTO t2(d,e) VALUES(3,100); 316 SELECT * FROM sqlite_sequence; 317 } 318 } {t1 10000 t2 100 t3 1} 319 320 321 # When a table with an AUTOINCREMENT is deleted, the corresponding entry 322 # in the SQLITE_SEQUENCE table should also be deleted. But the SQLITE_SEQUENCE 323 # table itself should remain behind. 324 # 325 do_test autoinc-3.1 { 326 execsql {SELECT name FROM sqlite_sequence} 327 } {t1 t2 t3} 328 do_test autoinc-3.2 { 329 execsql { 330 DROP TABLE t1; 331 SELECT name FROM sqlite_sequence; 332 } 333 } {t2 t3} 334 do_test autoinc-3.3 { 335 execsql { 336 DROP TABLE t3; 337 SELECT name FROM sqlite_sequence; 338 } 339 } {t2} 340 do_test autoinc-3.4 { 341 execsql { 342 DROP TABLE t2; 343 SELECT name FROM sqlite_sequence; 344 } 345 } {} 346 347 # AUTOINCREMENT on TEMP tables. 348 # 349 ifcapable tempdb { 350 do_test autoinc-4.1 { 351 execsql { 352 SELECT 1, name FROM sqlite_master WHERE type='table'; 353 SELECT 2, name FROM temp.sqlite_master WHERE type='table'; 354 } 355 } {1 sqlite_sequence} 356 do_test autoinc-4.2 { 357 execsql { 358 CREATE TABLE t1(x INTEGER PRIMARY KEY AUTOINCREMENT, y); 359 CREATE TEMP TABLE t3(a INTEGER PRIMARY KEY AUTOINCREMENT, b); 360 SELECT 1, name FROM sqlite_master WHERE type='table'; 361 SELECT 2, name FROM sqlite_temp_master WHERE type='table'; 362 } 363 } {1 sqlite_sequence 1 t1 2 t3 2 sqlite_sequence} 364 do_test autoinc-4.3 { 365 execsql { 366 SELECT 1, * FROM main.sqlite_sequence; 367 SELECT 2, * FROM temp.sqlite_sequence; 368 } 369 } {} 370 do_test autoinc-4.4 { 371 execsql { 372 INSERT INTO t1 VALUES(10,1); 373 INSERT INTO t3 VALUES(20,2); 374 INSERT INTO t1 VALUES(NULL,3); 375 INSERT INTO t3 VALUES(NULL,4); 376 } 377 } {} 378 379 ifcapable compound { 380 do_test autoinc-4.4.1 { 381 execsql { 382 SELECT * FROM t1 UNION ALL SELECT * FROM t3; 383 } 384 } {10 1 11 3 20 2 21 4} 385 } ;# ifcapable compound 386 387 do_test autoinc-4.5 { 388 execsql { 389 SELECT 1, * FROM main.sqlite_sequence; 390 SELECT 2, * FROM temp.sqlite_sequence; 391 } 392 } {1 t1 11 2 t3 21} 393 do_test autoinc-4.6 { 394 execsql { 395 INSERT INTO t1 SELECT * FROM t3; 396 SELECT 1, * FROM main.sqlite_sequence; 397 SELECT 2, * FROM temp.sqlite_sequence; 398 } 399 } {1 t1 21 2 t3 21} 400 do_test autoinc-4.7 { 401 execsql { 402 INSERT INTO t3 SELECT x+100, y FROM t1; 403 SELECT 1, * FROM main.sqlite_sequence; 404 SELECT 2, * FROM temp.sqlite_sequence; 405 } 406 } {1 t1 21 2 t3 121} 407 do_test autoinc-4.8 { 408 execsql { 409 DROP TABLE t3; 410 SELECT 1, * FROM main.sqlite_sequence; 411 SELECT 2, * FROM temp.sqlite_sequence; 412 } 413 } {1 t1 21} 414 do_test autoinc-4.9 { 415 execsql { 416 CREATE TEMP TABLE t2(p INTEGER PRIMARY KEY AUTOINCREMENT, q); 417 INSERT INTO t2 SELECT * FROM t1; 418 DROP TABLE t1; 419 SELECT 1, * FROM main.sqlite_sequence; 420 SELECT 2, * FROM temp.sqlite_sequence; 421 } 422 } {2 t2 21} 423 do_test autoinc-4.10 { 424 execsql { 425 DROP TABLE t2; 426 SELECT 1, * FROM main.sqlite_sequence; 427 SELECT 2, * FROM temp.sqlite_sequence; 428 } 429 } {} 430 } 431 432 # Make sure AUTOINCREMENT works on ATTACH-ed tables. 433 # 434 ifcapable tempdb&&attach { 435 do_test autoinc-5.1 { 436 forcedelete test2.db 437 forcedelete test2.db-journal 438 sqlite3 db2 test2.db 439 execsql { 440 CREATE TABLE t4(m INTEGER PRIMARY KEY AUTOINCREMENT, n); 441 CREATE TABLE t5(o, p INTEGER PRIMARY KEY AUTOINCREMENT); 442 } db2; 443 execsql { 444 ATTACH 'test2.db' as aux; 445 SELECT 1, * FROM main.sqlite_sequence; 446 SELECT 2, * FROM temp.sqlite_sequence; 447 SELECT 3, * FROM aux.sqlite_sequence; 448 } 449 } {} 450 do_test autoinc-5.2 { 451 execsql { 452 INSERT INTO t4 VALUES(NULL,1); 453 SELECT 1, * FROM main.sqlite_sequence; 454 SELECT 2, * FROM temp.sqlite_sequence; 455 SELECT 3, * FROM aux.sqlite_sequence; 456 } 457 } {3 t4 1} 458 do_test autoinc-5.3 { 459 execsql { 460 INSERT INTO t5 VALUES(100,200); 461 SELECT * FROM sqlite_sequence 462 } db2 463 } {t4 1 t5 200} 464 do_test autoinc-5.4 { 465 execsql { 466 SELECT 1, * FROM main.sqlite_sequence; 467 SELECT 2, * FROM temp.sqlite_sequence; 468 SELECT 3, * FROM aux.sqlite_sequence; 469 } 470 } {3 t4 1 3 t5 200} 471 } 472 473 # Requirement REQ00310: Make sure an insert fails if the sequence is 474 # already at its maximum value. 475 # 476 ifcapable {rowid32} { 477 do_test autoinc-6.1 { 478 execsql { 479 CREATE TABLE t6(v INTEGER PRIMARY KEY AUTOINCREMENT, w); 480 INSERT INTO t6 VALUES(2147483647,1); 481 SELECT seq FROM main.sqlite_sequence WHERE name='t6'; 482 } 483 } 2147483647 484 } 485 ifcapable {!rowid32} { 486 do_test autoinc-6.1 { 487 execsql { 488 CREATE TABLE t6(v INTEGER PRIMARY KEY AUTOINCREMENT, w); 489 INSERT INTO t6 VALUES(9223372036854775807,1); 490 SELECT seq FROM main.sqlite_sequence WHERE name='t6'; 491 } 492 } 9223372036854775807 493 } 494 do_test autoinc-6.2 { 495 catchsql { 496 INSERT INTO t6 VALUES(NULL,1); 497 } 498 } {1 {database or disk is full}} 499 500 # Allow the AUTOINCREMENT keyword inside the parentheses 501 # on a separate PRIMARY KEY designation. 502 # 503 do_test autoinc-7.1 { 504 execsql { 505 CREATE TABLE t7(x INTEGER, y REAL, PRIMARY KEY(x AUTOINCREMENT)); 506 INSERT INTO t7(y) VALUES(123); 507 INSERT INTO t7(y) VALUES(234); 508 DELETE FROM t7; 509 INSERT INTO t7(y) VALUES(345); 510 SELECT * FROM t7; 511 } 512 } {3 345.0} 513 514 # Test that if the AUTOINCREMENT is applied to a non integer primary key 515 # the error message is sensible. 516 do_test autoinc-7.2 { 517 catchsql { 518 CREATE TABLE t8(x TEXT PRIMARY KEY AUTOINCREMENT); 519 } 520 } {1 {AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY}} 521 522 523 # Ticket #1283. Make sure that preparing but never running a statement 524 # that creates the sqlite_sequence table does not mess up the database. 525 # 526 do_test autoinc-8.1 { 527 catch {db2 close} 528 catch {db close} 529 forcedelete test.db 530 sqlite3 db test.db 531 set DB [sqlite3_connection_pointer db] 532 set STMT [sqlite3_prepare $DB { 533 CREATE TABLE t1( 534 x INTEGER PRIMARY KEY AUTOINCREMENT 535 ) 536 } -1 TAIL] 537 sqlite3_finalize $STMT 538 set STMT [sqlite3_prepare $DB { 539 CREATE TABLE t1( 540 x INTEGER PRIMARY KEY AUTOINCREMENT 541 ) 542 } -1 TAIL] 543 sqlite3_step $STMT 544 sqlite3_finalize $STMT 545 execsql { 546 INSERT INTO t1 VALUES(NULL); 547 SELECT * FROM t1; 548 } 549 } {1} 550 551 # Ticket #3148 552 # Make sure the sqlite_sequence table is not damaged when doing 553 # an empty insert - an INSERT INTO ... SELECT ... where the SELECT 554 # clause returns an empty set. 555 # 556 do_test autoinc-9.1 { 557 db eval { 558 CREATE TABLE t2(x INTEGER PRIMARY KEY AUTOINCREMENT, y); 559 INSERT INTO t2 VALUES(NULL, 1); 560 CREATE TABLE t3(a INTEGER PRIMARY KEY AUTOINCREMENT, b); 561 INSERT INTO t3 SELECT * FROM t2 WHERE y>1; 562 563 SELECT * FROM sqlite_sequence WHERE name='t3'; 564 } 565 } {t3 0} 566 567 ifcapable trigger { 568 catchsql { pragma recursive_triggers = off } 569 570 # Ticket #3928. Make sure that triggers to not make extra slots in 571 # the SQLITE_SEQUENCE table. 572 # 573 do_test autoinc-3928.1 { 574 db eval { 575 CREATE TABLE t3928(a INTEGER PRIMARY KEY AUTOINCREMENT, b); 576 CREATE TRIGGER t3928r1 BEFORE INSERT ON t3928 BEGIN 577 INSERT INTO t3928(b) VALUES('before1'); 578 INSERT INTO t3928(b) VALUES('before2'); 579 END; 580 CREATE TRIGGER t3928r2 AFTER INSERT ON t3928 BEGIN 581 INSERT INTO t3928(b) VALUES('after1'); 582 INSERT INTO t3928(b) VALUES('after2'); 583 END; 584 INSERT INTO t3928(b) VALUES('test'); 585 SELECT * FROM t3928 ORDER BY a; 586 } 587 } {1 before1 2 after1 3 after2 4 before2 5 after1 6 after2 7 test 8 before1 9 before2 10 after1 11 before1 12 before2 13 after2} 588 do_test autoinc-3928.2 { 589 db eval { 590 SELECT * FROM sqlite_sequence WHERE name='t3928' 591 } 592 } {t3928 13} 593 594 do_test autoinc-3928.3 { 595 db eval { 596 DROP TRIGGER t3928r1; 597 DROP TRIGGER t3928r2; 598 CREATE TRIGGER t3928r3 BEFORE UPDATE ON t3928 599 WHEN typeof(new.b)=='integer' BEGIN 600 INSERT INTO t3928(b) VALUES('before-int-' || new.b); 601 END; 602 CREATE TRIGGER t3928r4 AFTER UPDATE ON t3928 603 WHEN typeof(new.b)=='integer' BEGIN 604 INSERT INTO t3928(b) VALUES('after-int-' || new.b); 605 END; 606 DELETE FROM t3928 WHERE a!=1; 607 UPDATE t3928 SET b=456 WHERE a=1; 608 SELECT * FROM t3928 ORDER BY a; 609 } 610 } {1 456 14 before-int-456 15 after-int-456} 611 do_test autoinc-3928.4 { 612 db eval { 613 SELECT * FROM sqlite_sequence WHERE name='t3928' 614 } 615 } {t3928 15} 616 617 do_test autoinc-3928.5 { 618 db eval { 619 CREATE TABLE t3928b(x); 620 INSERT INTO t3928b VALUES(100); 621 INSERT INTO t3928b VALUES(200); 622 INSERT INTO t3928b VALUES(300); 623 DELETE FROM t3928; 624 CREATE TABLE t3928c(y INTEGER PRIMARY KEY AUTOINCREMENT, z); 625 CREATE TRIGGER t3928br1 BEFORE DELETE ON t3928b BEGIN 626 INSERT INTO t3928(b) VALUES('before-del-'||old.x); 627 INSERT INTO t3928c(z) VALUES('before-del-'||old.x); 628 END; 629 CREATE TRIGGER t3928br2 AFTER DELETE ON t3928b BEGIN 630 INSERT INTO t3928(b) VALUES('after-del-'||old.x); 631 INSERT INTO t3928c(z) VALUES('after-del-'||old.x); 632 END; 633 DELETE FROM t3928b; 634 SELECT * FROM t3928 ORDER BY a; 635 } 636 } {16 before-del-100 17 after-del-100 18 before-del-200 19 after-del-200 20 before-del-300 21 after-del-300} 637 do_test autoinc-3928.6 { 638 db eval { 639 SELECT * FROM t3928c ORDER BY y; 640 } 641 } {1 before-del-100 2 after-del-100 3 before-del-200 4 after-del-200 5 before-del-300 6 after-del-300} 642 do_test autoinc-3928.7 { 643 db eval { 644 SELECT * FROM sqlite_sequence WHERE name LIKE 't3928%' ORDER BY name; 645 } 646 } {t3928 21 t3928c 6} 647 648 # Ticket [a696379c1f0886615541a48b35bd8181a80e88f8] 649 do_test autoinc-a69637.1 { 650 db eval { 651 CREATE TABLE ta69637_1(x INTEGER PRIMARY KEY AUTOINCREMENT, y); 652 CREATE TABLE ta69637_2(z); 653 CREATE TRIGGER ra69637_1 AFTER INSERT ON ta69637_2 BEGIN 654 INSERT INTO ta69637_1(y) VALUES(new.z+1); 655 END; 656 INSERT INTO ta69637_2 VALUES(123); 657 SELECT * FROM ta69637_1; 658 } 659 } {1 124} 660 do_test autoinc-a69637.2 { 661 db eval { 662 CREATE VIEW va69637_2 AS SELECT * FROM ta69637_2; 663 CREATE TRIGGER ra69637_2 INSTEAD OF INSERT ON va69637_2 BEGIN 664 INSERT INTO ta69637_1(y) VALUES(new.z+10000); 665 END; 666 INSERT INTO va69637_2 VALUES(123); 667 SELECT * FROM ta69637_1; 668 } 669 } {1 124 2 10123} 670 } 671 672 # 2016-10-03 ticket https://www.sqlite.org/src/tktview/7b3328086a5c1 673 # Make sure autoincrement plays nicely with the xfer optimization 674 # 675 do_execsql_test autoinc-10.1 { 676 DELETE FROM sqlite_sequence; 677 CREATE TABLE t10a(a INTEGER PRIMARY KEY AUTOINCREMENT, b UNIQUE); 678 INSERT INTO t10a VALUES(888,9999); 679 CREATE TABLE t10b(x INTEGER PRIMARY KEY AUTOINCREMENT, y UNIQUE); 680 INSERT INTO t10b SELECT * FROM t10a; 681 SELECT * FROM sqlite_sequence; 682 } {t10a 888 t10b 888} 683 684 # 2018-04-21 autoincrement does not cause problems for upsert 685 # 686 do_execsql_test autoinc-11.1 { 687 CREATE TABLE t11(a INTEGER PRIMARY KEY AUTOINCREMENT,b UNIQUE); 688 INSERT INTO t11(a,b) VALUES(2,3),(5,6),(4,3),(1,2) 689 ON CONFLICT(b) DO UPDATE SET a=a+1000; 690 SELECT seq FROM sqlite_sequence WHERE name='t11'; 691 } {5} 692 693 # 2018-05-23 ticket d8dc2b3a58cd5dc2918a1d4acbba4676a23ada4c 694 # Does not crash if the sqlite_sequence table schema is missing 695 # or corrupt. 696 # 697 do_test autoinc-12.1 { 698 db close 699 forcedelete test.db 700 sqlite3 db test.db 701 sqlite3_db_config db DEFENSIVE 0 702 db eval { 703 CREATE TABLE fake_sequence(name TEXT PRIMARY KEY,seq) WITHOUT ROWID; 704 PRAGMA writable_schema=on; 705 UPDATE sqlite_master SET 706 sql=replace(sql,'fake_','sqlite_'), 707 name='sqlite_sequence', 708 tbl_name='sqlite_sequence' 709 WHERE name='fake_sequence'; 710 } 711 db close 712 sqlite3 db test.db 713 set res [catch {db eval { 714 CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT); 715 INSERT INTO t1(b) VALUES('one'); 716 }} msg] 717 lappend res $msg 718 } {1 {database disk image is malformed}} 719 do_test autoinc-12.2 { 720 db close 721 forcedelete test.db 722 sqlite3 db test.db 723 sqlite3_db_config db DEFENSIVE 0 724 db eval { 725 CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT); 726 INSERT INTO t1(b) VALUES('one'); 727 PRAGMA writable_schema=on; 728 UPDATE sqlite_master SET 729 sql=replace(sql,'sqlite_','x_'), 730 name='x_sequence', 731 tbl_name='x_sequence' 732 WHERE name='sqlite_sequence'; 733 } 734 db close 735 sqlite3 db test.db 736 set res [catch {db eval { 737 INSERT INTO t1(b) VALUES('two'); 738 }} msg] 739 lappend res $msg 740 } {1 {database disk image is malformed}} 741 ifcapable vtab { 742 set err "database disk image is malformed" 743 } else { 744 set err {malformed database schema (sqlite_sequence) - near "VIRTUAL": syntax error} 745 } 746 do_test autoinc-12.3 { 747 db close 748 forcedelete test.db 749 sqlite3 db test.db 750 sqlite3_db_config db DEFENSIVE 0 751 db eval { 752 CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT); 753 INSERT INTO t1(b) VALUES('one'); 754 PRAGMA writable_schema=on; 755 UPDATE sqlite_master SET 756 sql='CREATE VIRTUAL TABLE sqlite_sequence USING sqlite_dbpage' 757 WHERE name='sqlite_sequence'; 758 } 759 db close 760 sqlite3 db test.db 761 set res [catch {db eval { 762 INSERT INTO t1(b) VALUES('two'); 763 }} msg] 764 lappend res $msg 765 } [list 1 $err] 766 do_test autoinc-12.4 { 767 db close 768 forcedelete test.db 769 sqlite3 db test.db 770 db eval { 771 CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT); 772 INSERT INTO t1(b) VALUES('one'); 773 CREATE TABLE fake(name TEXT PRIMARY KEY,seq) WITHOUT ROWID; 774 } 775 set root1 [db one {SELECT rootpage FROM sqlite_master 776 WHERE name='sqlite_sequence'}] 777 set root2 [db one {SELECT rootpage FROM sqlite_master 778 WHERE name='fake'}] 779 sqlite3_db_config db DEFENSIVE 0 780 db eval { 781 PRAGMA writable_schema=on; 782 UPDATE sqlite_master SET rootpage=$root2 783 WHERE name='sqlite_sequence'; 784 UPDATE sqlite_master SET rootpage=$root1 785 WHERE name='fake'; 786 } 787 db close 788 sqlite3 db test.db 789 set res [catch {db eval { 790 INSERT INTO t1(b) VALUES('two'); 791 }} msg] 792 lappend res $msg 793 } {1 {database disk image is malformed}} 794 breakpoint 795 do_test autoinc-12.5 { 796 db close 797 forcedelete test.db 798 sqlite3 db test.db 799 sqlite3_db_config db DEFENSIVE 0 800 db eval { 801 CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT); 802 INSERT INTO t1(b) VALUES('one'); 803 PRAGMA writable_schema=on; 804 UPDATE sqlite_master SET 805 sql='CREATE TABLE sqlite_sequence(x)' 806 WHERE name='sqlite_sequence'; 807 } 808 db close 809 sqlite3 db test.db 810 set res [catch {db eval { 811 INSERT INTO t1(b) VALUES('two'); 812 }} msg] 813 lappend res $msg 814 } {1 {database disk image is malformed}} 815 do_test autoinc-12.6 { 816 db close 817 forcedelete test.db 818 sqlite3 db test.db 819 sqlite3_db_config db DEFENSIVE 0 820 db eval { 821 CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT); 822 INSERT INTO t1(b) VALUES('one'); 823 PRAGMA writable_schema=on; 824 UPDATE sqlite_master SET 825 sql='CREATE TABLE sqlite_sequence(x,y INTEGER PRIMARY KEY)' 826 WHERE name='sqlite_sequence'; 827 } 828 db close 829 sqlite3 db test.db 830 set res [catch {db eval { 831 INSERT INTO t1(b) VALUES('two'),('three'),('four'); 832 INSERT INTO t1(b) VALUES('five'); 833 PRAGMA integrity_check; 834 }} msg] 835 lappend res $msg 836 } {0 ok} 837 do_test autoinc-12.7 { 838 db close 839 forcedelete test.db 840 sqlite3 db test.db 841 sqlite3_db_config db DEFENSIVE 0 842 db eval { 843 CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT); 844 INSERT INTO t1(b) VALUES('one'); 845 PRAGMA writable_schema=on; 846 UPDATE sqlite_master SET 847 sql='CREATE TABLE sqlite_sequence(y INTEGER PRIMARY KEY,x)' 848 WHERE name='sqlite_sequence'; 849 } 850 db close 851 sqlite3 db test.db 852 set res [catch {db eval { 853 INSERT INTO t1(b) VALUES('two'),('three'),('four'); 854 INSERT INTO t1(b) VALUES('five'); 855 PRAGMA integrity_check; 856 }} msg] 857 lappend res $msg 858 } {0 ok} 859 860 #-------------------------------------------------------------------------- 861 reset_db 862 do_execsql_test 13.0 { 863 CREATE TABLE t1(i INTEGER PRIMARY KEY AUTOINCREMENT, j); 864 CREATE TABLE t2(i INTEGER PRIMARY KEY AUTOINCREMENT, j); 865 CREATE TABLE t3(i INTEGER PRIMARY KEY AUTOINCREMENT, j); 866 867 INSERT INTO t1 VALUES(NULL, 1); 868 INSERT INTO t2 VALUES(NULL, 2); 869 INSERT INTO t3 VALUES(NULL, 3); 870 871 SELECT name FROM sqlite_sequence; 872 } {t1 t2 t3} 873 874 do_execsql_test 13.1 { 875 UPDATE sqlite_sequence SET name=NULL WHERE name='t2'; 876 INSERT INTO t3 VALUES(NULL, 4); 877 DELETE FROM t3; 878 INSERT INTO t3 VALUES(NULL, 5); 879 SELECT * FROM t3; 880 } {3 5} 881 882 883 finish_test