AryaWu/sqlite
0
1# 2004 November 122#3# The author disclaims copyright to this source code. In place of4# 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. The12# 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 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19set testprefix autoinc20 21# If the library is not compiled with autoincrement support then22# skip all tests in this file.23#24ifcapable {!autoinc} {25 finish_test26 return27}28 29if {[permutation]=="inmemory_journal"} {30 finish_test31 return32}33 34sqlite3_db_config_lookaside db 0 0 035 36# The database is initially empty.37#38do_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 the45# SQLITE_SEQUENCE table gets created.46#47do_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 empty55#56do_test autoinc-1.3 {57 execsql {58 SELECT * FROM sqlite_sequence;59 }60} {}61do_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#69do_test autoinc-1.4 {70 db close71 sqlite3 db test.db72 execsql {73 SELECT * FROM sqlite_sequence;74 }75} {}76 77# We are not allowed to drop the sqlite_sequence table.78#79do_test autoinc-1.5 {80 catchsql {DROP TABLE sqlite_sequence}81} {1 {table sqlite_sequence may not be dropped}}82do_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 key87# is always recorded in the sqlite_sequence table.88#89do_test autoinc-2.1 {90 execsql {91 SELECT * FROM sqlite_sequence92 }93} {}94do_test autoinc-2.2 {95 execsql {96 INSERT INTO t1 VALUES(12,34);97 SELECT * FROM sqlite_sequence;98 }99} {t1 12}100do_test autoinc-2.3 {101 execsql {102 INSERT INTO t1 VALUES(1,23);103 SELECT * FROM sqlite_sequence;104 }105} {t1 12}106do_test autoinc-2.4 {107 execsql {108 INSERT INTO t1 VALUES(123,456);109 SELECT * FROM sqlite_sequence;110 }111} {t1 123}112do_test autoinc-2.5 {113 execsql {114 INSERT INTO t1 VALUES(NULL,567);115 SELECT * FROM sqlite_sequence;116 }117} {t1 124}118do_test autoinc-2.6 {119 execsql {120 DELETE FROM t1 WHERE y=567;121 SELECT * FROM sqlite_sequence;122 }123} {t1 124}124do_test autoinc-2.7 {125 execsql {126 INSERT INTO t1 VALUES(NULL,567);127 SELECT * FROM sqlite_sequence;128 }129} {t1 125}130do_test autoinc-2.8 {131 execsql {132 DELETE FROM t1;133 SELECT * FROM sqlite_sequence;134 }135} {t1 125}136do_test autoinc-2.9 {137 execsql {138 INSERT INTO t1 VALUES(12,34);139 SELECT * FROM sqlite_sequence;140 }141} {t1 125}142do_test autoinc-2.10 {143 execsql {144 INSERT INTO t1 VALUES(125,456);145 SELECT * FROM sqlite_sequence;146 }147} {t1 125}148do_test autoinc-2.11 {149 execsql {150 INSERT INTO t1 VALUES(-1234567,-1);151 SELECT * FROM sqlite_sequence;152 }153} {t1 125}154do_test autoinc-2.12 {155 execsql {156 INSERT INTO t1 VALUES(234,5678);157 SELECT * FROM sqlite_sequence;158 }159} {t1 234}160do_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}167do_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#175do_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}182do_test autoinc-2.21 {183 execsql {184 SELECT * FROM sqlite_sequence;185 }186} {t1 1235}187do_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}194do_test autoinc-2.23 {195 execsql {196 SELECT * FROM sqlite_sequence;197 }198} {t1 1236}199do_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}206do_test autoinc-2.25 {207 execsql {208 SELECT * FROM sqlite_sequence;209 }210} {t1 1237}211do_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}218do_test autoinc-2.27 {219 execsql {220 SELECT * FROM sqlite_sequence;221 }222} {t1 1238}223do_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}231do_test autoinc-2.29 {232 execsql {233 SELECT * FROM sqlite_sequence;234 }235} {t1 1239}236 237# Test multi-row inserts238#239do_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}246do_test autoinc-2.51 {247 execsql {248 SELECT * FROM sqlite_sequence249 }250} {t1 1241}251 252ifcapable 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_sequence265 }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_sequence277 }278 } {t1 1249}279}280 281# Create multiple AUTOINCREMENT tables. Make sure all sequences are282# tracked separately and do not interfere with one another.283#284do_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}]294do_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}]300do_test autoinc-2.72 {301 execsql {302 INSERT INTO t1(x) VALUES(10000);303 SELECT * FROM sqlite_sequence;304 }305} {t1 10000 t2 2}306do_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}313do_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 entry322# in the SQLITE_SEQUENCE table should also be deleted. But the SQLITE_SEQUENCE323# table itself should remain behind.324#325do_test autoinc-3.1 {326 execsql {SELECT name FROM sqlite_sequence}327} {t1 t2 t3}328do_test autoinc-3.2 {329 execsql {330 DROP TABLE t1;331 SELECT name FROM sqlite_sequence;332 }333} {t2 t3}334do_test autoinc-3.3 {335 execsql {336 DROP TABLE t3;337 SELECT name FROM sqlite_sequence;338 }339} {t2}340do_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#349ifcapable 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 compound386 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#434ifcapable tempdb&&attach {435 do_test autoinc-5.1 {436 forcedelete test2.db437 forcedelete test2.db-journal438 sqlite3 db2 test2.db439 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_sequence462 } db2463 } {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 is474# already at its maximum value.475#476ifcapable {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 } 2147483647484}485ifcapable {!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 } 9223372036854775807493}494do_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 parentheses501# on a separate PRIMARY KEY designation.502#503do_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 key515# the error message is sensible.516do_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 statement524# that creates the sqlite_sequence table does not mess up the database.525#526do_test autoinc-8.1 {527 catch {db2 close}528 catch {db close}529 forcedelete test.db530 sqlite3 db test.db531 set DB [sqlite3_connection_pointer db]532 set STMT [sqlite3_prepare $DB {533 CREATE TABLE t1(534 x INTEGER PRIMARY KEY AUTOINCREMENT535 )536 } -1 TAIL]537 sqlite3_finalize $STMT538 set STMT [sqlite3_prepare $DB {539 CREATE TABLE t1(540 x INTEGER PRIMARY KEY AUTOINCREMENT541 )542 } -1 TAIL]543 sqlite3_step $STMT544 sqlite3_finalize $STMT545 execsql {546 INSERT INTO t1 VALUES(NULL);547 SELECT * FROM t1;548 }549} {1}550 551# Ticket #3148552# Make sure the sqlite_sequence table is not damaged when doing553# an empty insert - an INSERT INTO ... SELECT ... where the SELECT554# clause returns an empty set.555#556do_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 567ifcapable trigger {568 catchsql { pragma recursive_triggers = off } 569 570 # Ticket #3928. Make sure that triggers to not make extra slots in571 # 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 BEGIN577 INSERT INTO t3928(b) VALUES('before1');578 INSERT INTO t3928(b) VALUES('before2');579 END;580 CREATE TRIGGER t3928r2 AFTER INSERT ON t3928 BEGIN581 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' BEGIN600 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' BEGIN604 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 BEGIN626 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 BEGIN630 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 BEGIN654 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 BEGIN664 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://sqlite.org/src/tktview/7b3328086a5c1673# Make sure autoincrement plays nicely with the xfer optimization674#675do_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 upsert685#686do_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 d8dc2b3a58cd5dc2918a1d4acbba4676a23ada4c694# Does not crash if the sqlite_sequence table schema is missing695# or corrupt.696#697do_test autoinc-12.1 {698 db close699 forcedelete test.db700 sqlite3 db test.db701 sqlite3_db_config db DEFENSIVE 0702 db eval {703 CREATE TABLE fake_sequence(name TEXT PRIMARY KEY,seq) WITHOUT ROWID;704 PRAGMA writable_schema=on;705 UPDATE sqlite_master SET706 sql=replace(sql,'fake_','sqlite_'),707 name='sqlite_sequence',708 tbl_name='sqlite_sequence'709 WHERE name='fake_sequence';710 }711 db close712 sqlite3 db test.db713 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 $msg718} {1 {database disk image is malformed}}719do_test autoinc-12.2 {720 db close721 forcedelete test.db722 sqlite3 db test.db723 sqlite3_db_config db DEFENSIVE 0724 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 SET729 sql=replace(sql,'sqlite_','x_'),730 name='x_sequence',731 tbl_name='x_sequence'732 WHERE name='sqlite_sequence';733 }734 db close735 sqlite3 db test.db736 set res [catch {db eval {737 INSERT INTO t1(b) VALUES('two');738 }} msg]739 lappend res $msg740} {1 {database disk image is malformed}}741ifcapable vtab {742 set err "database disk image is malformed"743} else {744 set err {malformed database schema (sqlite_sequence) - near "VIRTUAL": syntax error}745}746do_test autoinc-12.3 {747 db close748 forcedelete test.db749 sqlite3 db test.db750 sqlite3_db_config db DEFENSIVE 0751 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 SET756 sql='CREATE VIRTUAL TABLE sqlite_sequence USING sqlite_dbpage'757 WHERE name='sqlite_sequence';758 }759 db close760 sqlite3 db test.db761 set res [catch {db eval {762 INSERT INTO t1(b) VALUES('two');763 }} msg]764 lappend res $msg765} [list 1 $err]766do_test autoinc-12.4 {767 db close768 forcedelete test.db769 sqlite3 db test.db770 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_master776 WHERE name='sqlite_sequence'}]777 set root2 [db one {SELECT rootpage FROM sqlite_master778 WHERE name='fake'}]779 sqlite3_db_config db DEFENSIVE 0780 db eval {781 PRAGMA writable_schema=on;782 UPDATE sqlite_master SET rootpage=$root2783 WHERE name='sqlite_sequence';784 UPDATE sqlite_master SET rootpage=$root1785 WHERE name='fake';786 }787 db close788 sqlite3 db test.db789 set res [catch {db eval {790 INSERT INTO t1(b) VALUES('two');791 }} msg]792 lappend res $msg793} {1 {database disk image is malformed}}794breakpoint795do_test autoinc-12.5 {796 db close797 forcedelete test.db798 sqlite3 db test.db799 sqlite3_db_config db DEFENSIVE 0800 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 SET805 sql='CREATE TABLE sqlite_sequence(x)'806 WHERE name='sqlite_sequence';807 }808 db close809 sqlite3 db test.db810 set res [catch {db eval {811 INSERT INTO t1(b) VALUES('two');812 }} msg]813 lappend res $msg814} {1 {database disk image is malformed}}815do_test autoinc-12.6 {816 db close817 forcedelete test.db818 sqlite3 db test.db819 sqlite3_db_config db DEFENSIVE 0820 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 SET825 sql='CREATE TABLE sqlite_sequence(x,y INTEGER PRIMARY KEY)'826 WHERE name='sqlite_sequence';827 }828 db close829 sqlite3 db test.db830 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 $msg836} {0 ok}837do_test autoinc-12.7 {838 db close839 forcedelete test.db840 sqlite3 db test.db841 sqlite3_db_config db DEFENSIVE 0842 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 SET847 sql='CREATE TABLE sqlite_sequence(y INTEGER PRIMARY KEY,x)'848 WHERE name='sqlite_sequence';849 }850 db close851 sqlite3 db test.db852 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 $msg858} {0 ok}859 860#--------------------------------------------------------------------------861reset_db862do_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 874do_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 883finish_test884