# 2004 November 12 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #************************************************************************* # This file implements regression tests for SQLite library. The # focus of this script is testing the AUTOINCREMENT features. # # $Id: autoinc.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $ # set testdir [file dirname $argv0] source $testdir/tester.tcl # If the library is not compiled with autoincrement support then # skip all tests in this file. # ifcapable {!autoinc} { finish_test return } sqlite3_db_config_lookaside db 0 0 0 # The database is initially empty. # do_test autoinc-1.1 { execsql { SELECT name FROM sqlite_master WHERE type='table'; } } {} # Add a table with the AUTOINCREMENT feature. Verify that the # SQLITE_SEQUENCE table gets created. # do_test autoinc-1.2 { execsql { CREATE TABLE t1(x INTEGER PRIMARY KEY AUTOINCREMENT, y); SELECT name FROM sqlite_master WHERE type='table'; } } {t1 sqlite_sequence} # The SQLITE_SEQUENCE table is initially empty # do_test autoinc-1.3 { execsql { SELECT * FROM sqlite_sequence; } } {} do_test autoinc-1.3.1 { catchsql { CREATE INDEX seqidx ON sqlite_sequence(name) } } {1 {table sqlite_sequence may not be indexed}} # Close and reopen the database. Verify that everything is still there. # do_test autoinc-1.4 { db close sqlite3 db test.db execsql { SELECT * FROM sqlite_sequence; } } {} # We are not allowed to drop the sqlite_sequence table. # do_test autoinc-1.5 { catchsql {DROP TABLE sqlite_sequence} } {1 {table sqlite_sequence may not be dropped}} do_test autoinc-1.6 { execsql {SELECT name FROM sqlite_master WHERE type='table'} } {t1 sqlite_sequence} # Insert an entries into the t1 table and make sure the largest key # is always recorded in the sqlite_sequence table. # do_test autoinc-2.1 { execsql { SELECT * FROM sqlite_sequence } } {} do_test autoinc-2.2 { execsql { INSERT INTO t1 VALUES(12,34); SELECT * FROM sqlite_sequence; } } {t1 12} do_test autoinc-2.3 { execsql { INSERT INTO t1 VALUES(1,23); SELECT * FROM sqlite_sequence; } } {t1 12} do_test autoinc-2.4 { execsql { INSERT INTO t1 VALUES(123,456); SELECT * FROM sqlite_sequence; } } {t1 123} do_test autoinc-2.5 { execsql { INSERT INTO t1 VALUES(NULL,567); SELECT * FROM sqlite_sequence; } } {t1 124} do_test autoinc-2.6 { execsql { DELETE FROM t1 WHERE y=567; SELECT * FROM sqlite_sequence; } } {t1 124} do_test autoinc-2.7 { execsql { INSERT INTO t1 VALUES(NULL,567); SELECT * FROM sqlite_sequence; } } {t1 125} do_test autoinc-2.8 { execsql { DELETE FROM t1; SELECT * FROM sqlite_sequence; } } {t1 125} do_test autoinc-2.9 { execsql { INSERT INTO t1 VALUES(12,34); SELECT * FROM sqlite_sequence; } } {t1 125} do_test autoinc-2.10 { execsql { INSERT INTO t1 VALUES(125,456); SELECT * FROM sqlite_sequence; } } {t1 125} do_test autoinc-2.11 { execsql { INSERT INTO t1 VALUES(-1234567,-1); SELECT * FROM sqlite_sequence; } } {t1 125} do_test autoinc-2.12 { execsql { INSERT INTO t1 VALUES(234,5678); SELECT * FROM sqlite_sequence; } } {t1 234} do_test autoinc-2.13 { execsql { DELETE FROM t1; INSERT INTO t1 VALUES(NULL,1); SELECT * FROM sqlite_sequence; } } {t1 235} do_test autoinc-2.14 { execsql { SELECT * FROM t1; } } {235 1} # Manually change the autoincrement values in sqlite_sequence. # do_test autoinc-2.20 { execsql { UPDATE sqlite_sequence SET seq=1234 WHERE name='t1'; INSERT INTO t1 VALUES(NULL,2); SELECT * FROM t1; } } {235 1 1235 2} do_test autoinc-2.21 { execsql { SELECT * FROM sqlite_sequence; } } {t1 1235} do_test autoinc-2.22 { execsql { UPDATE sqlite_sequence SET seq=NULL WHERE name='t1'; INSERT INTO t1 VALUES(NULL,3); SELECT * FROM t1; } } {235 1 1235 2 1236 3} do_test autoinc-2.23 { execsql { SELECT * FROM sqlite_sequence; } } {t1 1236} do_test autoinc-2.24 { execsql { UPDATE sqlite_sequence SET seq='a-string' WHERE name='t1'; INSERT INTO t1 VALUES(NULL,4); SELECT * FROM t1; } } {235 1 1235 2 1236 3 1237 4} do_test autoinc-2.25 { execsql { SELECT * FROM sqlite_sequence; } } {t1 1237} do_test autoinc-2.26 { execsql { DELETE FROM sqlite_sequence WHERE name='t1'; INSERT INTO t1 VALUES(NULL,5); SELECT * FROM t1; } } {235 1 1235 2 1236 3 1237 4 1238 5} do_test autoinc-2.27 { execsql { SELECT * FROM sqlite_sequence; } } {t1 1238} do_test autoinc-2.28 { execsql { UPDATE sqlite_sequence SET seq='12345678901234567890' WHERE name='t1'; INSERT INTO t1 VALUES(NULL,6); SELECT * FROM t1; } } {235 1 1235 2 1236 3 1237 4 1238 5 1239 6} do_test autoinc-2.29 { execsql { SELECT * FROM sqlite_sequence; } } {t1 1239} # Test multi-row inserts # do_test autoinc-2.50 { execsql { DELETE FROM t1 WHERE y>=3; INSERT INTO t1 SELECT NULL, y+2 FROM t1; SELECT * FROM t1; } } {235 1 1235 2 1240 3 1241 4} do_test autoinc-2.51 { execsql { SELECT * FROM sqlite_sequence } } {t1 1241} ifcapable tempdb { do_test autoinc-2.52 { execsql { CREATE TEMP TABLE t2 AS SELECT y FROM t1; } execsql { INSERT INTO t1 SELECT NULL, y+4 FROM t2; SELECT * FROM t1; } } {235 1 1235 2 1240 3 1241 4 1242 5 1243 6 1244 7 1245 8} do_test autoinc-2.53 { execsql { SELECT * FROM sqlite_sequence } } {t1 1245} do_test autoinc-2.54 { execsql { DELETE FROM t1; INSERT INTO t1 SELECT NULL, y FROM t2; SELECT * FROM t1; } } {1246 1 1247 2 1248 3 1249 4} do_test autoinc-2.55 { execsql { SELECT * FROM sqlite_sequence } } {t1 1249} } # Create multiple AUTOINCREMENT tables. Make sure all sequences are # tracked separately and do not interfere with one another. # do_test autoinc-2.70 { catchsql { DROP TABLE t2; } execsql { CREATE TABLE t2(d, e INTEGER PRIMARY KEY AUTOINCREMENT, f); INSERT INTO t2(d) VALUES(1); SELECT * FROM sqlite_sequence; } } [ifcapable tempdb {list t1 1249 t2 1} else {list t1 1241 t2 1}] do_test autoinc-2.71 { execsql { INSERT INTO t2(d) VALUES(2); SELECT * FROM sqlite_sequence; } } [ifcapable tempdb {list t1 1249 t2 2} else {list t1 1241 t2 2}] do_test autoinc-2.72 { execsql { INSERT INTO t1(x) VALUES(10000); SELECT * FROM sqlite_sequence; } } {t1 10000 t2 2} do_test autoinc-2.73 { execsql { CREATE TABLE t3(g INTEGER PRIMARY KEY AUTOINCREMENT, h); INSERT INTO t3(h) VALUES(1); SELECT * FROM sqlite_sequence; } } {t1 10000 t2 2 t3 1} do_test autoinc-2.74 { execsql { INSERT INTO t2(d,e) VALUES(3,100); SELECT * FROM sqlite_sequence; } } {t1 10000 t2 100 t3 1} # When a table with an AUTOINCREMENT is deleted, the corresponding entry # in the SQLITE_SEQUENCE table should also be deleted. But the SQLITE_SEQUENCE # table itself should remain behind. # do_test autoinc-3.1 { execsql {SELECT name FROM sqlite_sequence} } {t1 t2 t3} do_test autoinc-3.2 { execsql { DROP TABLE t1; SELECT name FROM sqlite_sequence; } } {t2 t3} do_test autoinc-3.3 { execsql { DROP TABLE t3; SELECT name FROM sqlite_sequence; } } {t2} do_test autoinc-3.4 { execsql { DROP TABLE t2; SELECT name FROM sqlite_sequence; } } {} # AUTOINCREMENT on TEMP tables. # ifcapable tempdb { do_test autoinc-4.1 { execsql { SELECT 1, name FROM sqlite_master WHERE type='table'; SELECT 2, name FROM sqlite_temp_master WHERE type='table'; } } {1 sqlite_sequence} do_test autoinc-4.2 { execsql { CREATE TABLE t1(x INTEGER PRIMARY KEY AUTOINCREMENT, y); CREATE TEMP TABLE t3(a INTEGER PRIMARY KEY AUTOINCREMENT, b); SELECT 1, name FROM sqlite_master WHERE type='table'; SELECT 2, name FROM sqlite_temp_master WHERE type='table'; } } {1 sqlite_sequence 1 t1 2 t3 2 sqlite_sequence} do_test autoinc-4.3 { execsql { SELECT 1, * FROM main.sqlite_sequence; SELECT 2, * FROM temp.sqlite_sequence; } } {} do_test autoinc-4.4 { execsql { INSERT INTO t1 VALUES(10,1); INSERT INTO t3 VALUES(20,2); INSERT INTO t1 VALUES(NULL,3); INSERT INTO t3 VALUES(NULL,4); } } {} ifcapable compound { do_test autoinc-4.4.1 { execsql { SELECT * FROM t1 UNION ALL SELECT * FROM t3; } } {10 1 11 3 20 2 21 4} } ;# ifcapable compound do_test autoinc-4.5 { execsql { SELECT 1, * FROM main.sqlite_sequence; SELECT 2, * FROM temp.sqlite_sequence; } } {1 t1 11 2 t3 21} do_test autoinc-4.6 { execsql { INSERT INTO t1 SELECT * FROM t3; SELECT 1, * FROM main.sqlite_sequence; SELECT 2, * FROM temp.sqlite_sequence; } } {1 t1 21 2 t3 21} do_test autoinc-4.7 { execsql { INSERT INTO t3 SELECT x+100, y FROM t1; SELECT 1, * FROM main.sqlite_sequence; SELECT 2, * FROM temp.sqlite_sequence; } } {1 t1 21 2 t3 121} do_test autoinc-4.8 { execsql { DROP TABLE t3; SELECT 1, * FROM main.sqlite_sequence; SELECT 2, * FROM temp.sqlite_sequence; } } {1 t1 21} do_test autoinc-4.9 { execsql { CREATE TEMP TABLE t2(p INTEGER PRIMARY KEY AUTOINCREMENT, q); INSERT INTO t2 SELECT * FROM t1; DROP TABLE t1; SELECT 1, * FROM main.sqlite_sequence; SELECT 2, * FROM temp.sqlite_sequence; } } {2 t2 21} do_test autoinc-4.10 { execsql { DROP TABLE t2; SELECT 1, * FROM main.sqlite_sequence; SELECT 2, * FROM temp.sqlite_sequence; } } {} } # Make sure AUTOINCREMENT works on ATTACH-ed tables. # ifcapable tempdb&&attach { do_test autoinc-5.1 { forcedelete test2.db forcedelete test2.db-journal sqlite3 db2 test2.db execsql { CREATE TABLE t4(m INTEGER PRIMARY KEY AUTOINCREMENT, n); CREATE TABLE t5(o, p INTEGER PRIMARY KEY AUTOINCREMENT); } db2; execsql { ATTACH 'test2.db' as aux; SELECT 1, * FROM main.sqlite_sequence; SELECT 2, * FROM temp.sqlite_sequence; SELECT 3, * FROM aux.sqlite_sequence; } } {} do_test autoinc-5.2 { execsql { INSERT INTO t4 VALUES(NULL,1); SELECT 1, * FROM main.sqlite_sequence; SELECT 2, * FROM temp.sqlite_sequence; SELECT 3, * FROM aux.sqlite_sequence; } } {3 t4 1} do_test autoinc-5.3 { execsql { INSERT INTO t5 VALUES(100,200); SELECT * FROM sqlite_sequence } db2 } {t4 1 t5 200} do_test autoinc-5.4 { execsql { SELECT 1, * FROM main.sqlite_sequence; SELECT 2, * FROM temp.sqlite_sequence; SELECT 3, * FROM aux.sqlite_sequence; } } {3 t4 1 3 t5 200} } # Requirement REQ00310: Make sure an insert fails if the sequence is # already at its maximum value. # ifcapable {rowid32} { do_test autoinc-6.1 { execsql { CREATE TABLE t6(v INTEGER PRIMARY KEY AUTOINCREMENT, w); INSERT INTO t6 VALUES(2147483647,1); SELECT seq FROM main.sqlite_sequence WHERE name='t6'; } } 2147483647 } ifcapable {!rowid32} { do_test autoinc-6.1 { execsql { CREATE TABLE t6(v INTEGER PRIMARY KEY AUTOINCREMENT, w); INSERT INTO t6 VALUES(9223372036854775807,1); SELECT seq FROM main.sqlite_sequence WHERE name='t6'; } } 9223372036854775807 } do_test autoinc-6.2 { catchsql { INSERT INTO t6 VALUES(NULL,1); } } {1 {database or disk is full}} # Allow the AUTOINCREMENT keyword inside the parentheses # on a separate PRIMARY KEY designation. # do_test autoinc-7.1 { execsql { CREATE TABLE t7(x INTEGER, y REAL, PRIMARY KEY(x AUTOINCREMENT)); INSERT INTO t7(y) VALUES(123); INSERT INTO t7(y) VALUES(234); DELETE FROM t7; INSERT INTO t7(y) VALUES(345); SELECT * FROM t7; } } {3 345.0} # Test that if the AUTOINCREMENT is applied to a non integer primary key # the error message is sensible. do_test autoinc-7.2 { catchsql { CREATE TABLE t8(x TEXT PRIMARY KEY AUTOINCREMENT); } } {1 {AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY}} # Ticket #1283. Make sure that preparing but never running a statement # that creates the sqlite_sequence table does not mess up the database. # do_test autoinc-8.1 { catch {db2 close} catch {db close} forcedelete test.db sqlite3 db test.db set DB [sqlite3_connection_pointer db] set STMT [sqlite3_prepare $DB { CREATE TABLE t1( x INTEGER PRIMARY KEY AUTOINCREMENT ) } -1 TAIL] sqlite3_finalize $STMT set STMT [sqlite3_prepare $DB { CREATE TABLE t1( x INTEGER PRIMARY KEY AUTOINCREMENT ) } -1 TAIL] sqlite3_step $STMT sqlite3_finalize $STMT execsql { INSERT INTO t1 VALUES(NULL); SELECT * FROM t1; } } {1} # Ticket #3148 # Make sure the sqlite_sequence table is not damaged when doing # an empty insert - an INSERT INTO ... SELECT ... where the SELECT # clause returns an empty set. # do_test autoinc-9.1 { db eval { CREATE TABLE t2(x INTEGER PRIMARY KEY AUTOINCREMENT, y); INSERT INTO t2 VALUES(NULL, 1); CREATE TABLE t3(a INTEGER PRIMARY KEY AUTOINCREMENT, b); INSERT INTO t3 SELECT * FROM t2 WHERE y>1; SELECT * FROM sqlite_sequence WHERE name='t3'; } } {t3 0} ifcapable trigger { catchsql { pragma recursive_triggers = off } # Ticket #3928. Make sure that triggers to not make extra slots in # the SQLITE_SEQUENCE table. # do_test autoinc-3928.1 { db eval { CREATE TABLE t3928(a INTEGER PRIMARY KEY AUTOINCREMENT, b); CREATE TRIGGER t3928r1 BEFORE INSERT ON t3928 BEGIN INSERT INTO t3928(b) VALUES('before1'); INSERT INTO t3928(b) VALUES('before2'); END; CREATE TRIGGER t3928r2 AFTER INSERT ON t3928 BEGIN INSERT INTO t3928(b) VALUES('after1'); INSERT INTO t3928(b) VALUES('after2'); END; INSERT INTO t3928(b) VALUES('test'); SELECT * FROM t3928 ORDER BY a; } } {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} do_test autoinc-3928.2 { db eval { SELECT * FROM sqlite_sequence WHERE name='t3928' } } {t3928 13} do_test autoinc-3928.3 { db eval { DROP TRIGGER t3928r1; DROP TRIGGER t3928r2; CREATE TRIGGER t3928r3 BEFORE UPDATE ON t3928 WHEN typeof(new.b)=='integer' BEGIN INSERT INTO t3928(b) VALUES('before-int-' || new.b); END; CREATE TRIGGER t3928r4 AFTER UPDATE ON t3928 WHEN typeof(new.b)=='integer' BEGIN INSERT INTO t3928(b) VALUES('after-int-' || new.b); END; DELETE FROM t3928 WHERE a!=1; UPDATE t3928 SET b=456 WHERE a=1; SELECT * FROM t3928 ORDER BY a; } } {1 456 14 before-int-456 15 after-int-456} do_test autoinc-3928.4 { db eval { SELECT * FROM sqlite_sequence WHERE name='t3928' } } {t3928 15} do_test autoinc-3928.5 { db eval { CREATE TABLE t3928b(x); INSERT INTO t3928b VALUES(100); INSERT INTO t3928b VALUES(200); INSERT INTO t3928b VALUES(300); DELETE FROM t3928; CREATE TABLE t3928c(y INTEGER PRIMARY KEY AUTOINCREMENT, z); CREATE TRIGGER t3928br1 BEFORE DELETE ON t3928b BEGIN INSERT INTO t3928(b) VALUES('before-del-'||old.x); INSERT INTO t3928c(z) VALUES('before-del-'||old.x); END; CREATE TRIGGER t3928br2 AFTER DELETE ON t3928b BEGIN INSERT INTO t3928(b) VALUES('after-del-'||old.x); INSERT INTO t3928c(z) VALUES('after-del-'||old.x); END; DELETE FROM t3928b; SELECT * FROM t3928 ORDER BY a; } } {16 before-del-100 17 after-del-100 18 before-del-200 19 after-del-200 20 before-del-300 21 after-del-300} do_test autoinc-3928.6 { db eval { SELECT * FROM t3928c ORDER BY y; } } {1 before-del-100 2 after-del-100 3 before-del-200 4 after-del-200 5 before-del-300 6 after-del-300} do_test autoinc-3928.7 { db eval { SELECT * FROM sqlite_sequence WHERE name LIKE 't3928%' ORDER BY name; } } {t3928 21 t3928c 6} # Ticket [a696379c1f0886615541a48b35bd8181a80e88f8] do_test autoinc-a69637.1 { db eval { CREATE TABLE ta69637_1(x INTEGER PRIMARY KEY AUTOINCREMENT, y); CREATE TABLE ta69637_2(z); CREATE TRIGGER ra69637_1 AFTER INSERT ON ta69637_2 BEGIN INSERT INTO ta69637_1(y) VALUES(new.z+1); END; INSERT INTO ta69637_2 VALUES(123); SELECT * FROM ta69637_1; } } {1 124} do_test autoinc-a69637.2 { db eval { CREATE VIEW va69637_2 AS SELECT * FROM ta69637_2; CREATE TRIGGER ra69637_2 INSTEAD OF INSERT ON va69637_2 BEGIN INSERT INTO ta69637_1(y) VALUES(new.z+10000); END; INSERT INTO va69637_2 VALUES(123); SELECT * FROM ta69637_1; } } {1 124 2 10123} } finish_test