# 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.12 2008/05/29 03:20:59 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 } # 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; } } {} # 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} finish_test