Move bundled copy of sqlite one level deeper to better separate it...

third_party/sqlite/test/autoinc.test

Index: third_party/sqlite/test/autoinc.test
===================================================================
--- third_party/sqlite/test/autoinc.test	(revision 56608)
+++ third_party/sqlite/test/autoinc.test	(working copy)
@@ -1,641 +0,0 @@
-# 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}
-
-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}
-
-finish_test