# 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.13 2008/08/11 18:44:58 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;

   }

 } {}

 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}

 finish_test