/trunk/test/autoinc.test |
@@ -0,0 +1,668 @@ |
# 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.14 2009/06/23 20:28:54 drh 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 { |
file delete -force test2.db |
file delete -force 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} |
file delete -force 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 |