# 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