# 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.9 2006/01/03 00:33:50 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;      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 {  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}finish_test