1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/autoinc.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,560 @@
1.4 +# 2004 November 12
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#*************************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this script is testing the AUTOINCREMENT features.
1.16 +#
1.17 +# $Id: autoinc.test,v 1.13 2008/08/11 18:44:58 drh Exp $
1.18 +#
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +# If the library is not compiled with autoincrement support then
1.24 +# skip all tests in this file.
1.25 +#
1.26 +ifcapable {!autoinc} {
1.27 + finish_test
1.28 + return
1.29 +}
1.30 +
1.31 +# The database is initially empty.
1.32 +#
1.33 +do_test autoinc-1.1 {
1.34 + execsql {
1.35 + SELECT name FROM sqlite_master WHERE type='table';
1.36 + }
1.37 +} {}
1.38 +
1.39 +# Add a table with the AUTOINCREMENT feature. Verify that the
1.40 +# SQLITE_SEQUENCE table gets created.
1.41 +#
1.42 +do_test autoinc-1.2 {
1.43 + execsql {
1.44 + CREATE TABLE t1(x INTEGER PRIMARY KEY AUTOINCREMENT, y);
1.45 + SELECT name FROM sqlite_master WHERE type='table';
1.46 + }
1.47 +} {t1 sqlite_sequence}
1.48 +
1.49 +# The SQLITE_SEQUENCE table is initially empty
1.50 +#
1.51 +do_test autoinc-1.3 {
1.52 + execsql {
1.53 + SELECT * FROM sqlite_sequence;
1.54 + }
1.55 +} {}
1.56 +do_test autoinc-1.3.1 {
1.57 + catchsql {
1.58 + CREATE INDEX seqidx ON sqlite_sequence(name)
1.59 + }
1.60 +} {1 {table sqlite_sequence may not be indexed}}
1.61 +
1.62 +# Close and reopen the database. Verify that everything is still there.
1.63 +#
1.64 +do_test autoinc-1.4 {
1.65 + db close
1.66 + sqlite3 db test.db
1.67 + execsql {
1.68 + SELECT * FROM sqlite_sequence;
1.69 + }
1.70 +} {}
1.71 +
1.72 +# We are not allowed to drop the sqlite_sequence table.
1.73 +#
1.74 +do_test autoinc-1.5 {
1.75 + catchsql {DROP TABLE sqlite_sequence}
1.76 +} {1 {table sqlite_sequence may not be dropped}}
1.77 +do_test autoinc-1.6 {
1.78 + execsql {SELECT name FROM sqlite_master WHERE type='table'}
1.79 +} {t1 sqlite_sequence}
1.80 +
1.81 +# Insert an entries into the t1 table and make sure the largest key
1.82 +# is always recorded in the sqlite_sequence table.
1.83 +#
1.84 +do_test autoinc-2.1 {
1.85 + execsql {
1.86 + SELECT * FROM sqlite_sequence
1.87 + }
1.88 +} {}
1.89 +do_test autoinc-2.2 {
1.90 + execsql {
1.91 + INSERT INTO t1 VALUES(12,34);
1.92 + SELECT * FROM sqlite_sequence;
1.93 + }
1.94 +} {t1 12}
1.95 +do_test autoinc-2.3 {
1.96 + execsql {
1.97 + INSERT INTO t1 VALUES(1,23);
1.98 + SELECT * FROM sqlite_sequence;
1.99 + }
1.100 +} {t1 12}
1.101 +do_test autoinc-2.4 {
1.102 + execsql {
1.103 + INSERT INTO t1 VALUES(123,456);
1.104 + SELECT * FROM sqlite_sequence;
1.105 + }
1.106 +} {t1 123}
1.107 +do_test autoinc-2.5 {
1.108 + execsql {
1.109 + INSERT INTO t1 VALUES(NULL,567);
1.110 + SELECT * FROM sqlite_sequence;
1.111 + }
1.112 +} {t1 124}
1.113 +do_test autoinc-2.6 {
1.114 + execsql {
1.115 + DELETE FROM t1 WHERE y=567;
1.116 + SELECT * FROM sqlite_sequence;
1.117 + }
1.118 +} {t1 124}
1.119 +do_test autoinc-2.7 {
1.120 + execsql {
1.121 + INSERT INTO t1 VALUES(NULL,567);
1.122 + SELECT * FROM sqlite_sequence;
1.123 + }
1.124 +} {t1 125}
1.125 +do_test autoinc-2.8 {
1.126 + execsql {
1.127 + DELETE FROM t1;
1.128 + SELECT * FROM sqlite_sequence;
1.129 + }
1.130 +} {t1 125}
1.131 +do_test autoinc-2.9 {
1.132 + execsql {
1.133 + INSERT INTO t1 VALUES(12,34);
1.134 + SELECT * FROM sqlite_sequence;
1.135 + }
1.136 +} {t1 125}
1.137 +do_test autoinc-2.10 {
1.138 + execsql {
1.139 + INSERT INTO t1 VALUES(125,456);
1.140 + SELECT * FROM sqlite_sequence;
1.141 + }
1.142 +} {t1 125}
1.143 +do_test autoinc-2.11 {
1.144 + execsql {
1.145 + INSERT INTO t1 VALUES(-1234567,-1);
1.146 + SELECT * FROM sqlite_sequence;
1.147 + }
1.148 +} {t1 125}
1.149 +do_test autoinc-2.12 {
1.150 + execsql {
1.151 + INSERT INTO t1 VALUES(234,5678);
1.152 + SELECT * FROM sqlite_sequence;
1.153 + }
1.154 +} {t1 234}
1.155 +do_test autoinc-2.13 {
1.156 + execsql {
1.157 + DELETE FROM t1;
1.158 + INSERT INTO t1 VALUES(NULL,1);
1.159 + SELECT * FROM sqlite_sequence;
1.160 + }
1.161 +} {t1 235}
1.162 +do_test autoinc-2.14 {
1.163 + execsql {
1.164 + SELECT * FROM t1;
1.165 + }
1.166 +} {235 1}
1.167 +
1.168 +# Manually change the autoincrement values in sqlite_sequence.
1.169 +#
1.170 +do_test autoinc-2.20 {
1.171 + execsql {
1.172 + UPDATE sqlite_sequence SET seq=1234 WHERE name='t1';
1.173 + INSERT INTO t1 VALUES(NULL,2);
1.174 + SELECT * FROM t1;
1.175 + }
1.176 +} {235 1 1235 2}
1.177 +do_test autoinc-2.21 {
1.178 + execsql {
1.179 + SELECT * FROM sqlite_sequence;
1.180 + }
1.181 +} {t1 1235}
1.182 +do_test autoinc-2.22 {
1.183 + execsql {
1.184 + UPDATE sqlite_sequence SET seq=NULL WHERE name='t1';
1.185 + INSERT INTO t1 VALUES(NULL,3);
1.186 + SELECT * FROM t1;
1.187 + }
1.188 +} {235 1 1235 2 1236 3}
1.189 +do_test autoinc-2.23 {
1.190 + execsql {
1.191 + SELECT * FROM sqlite_sequence;
1.192 + }
1.193 +} {t1 1236}
1.194 +do_test autoinc-2.24 {
1.195 + execsql {
1.196 + UPDATE sqlite_sequence SET seq='a-string' WHERE name='t1';
1.197 + INSERT INTO t1 VALUES(NULL,4);
1.198 + SELECT * FROM t1;
1.199 + }
1.200 +} {235 1 1235 2 1236 3 1237 4}
1.201 +do_test autoinc-2.25 {
1.202 + execsql {
1.203 + SELECT * FROM sqlite_sequence;
1.204 + }
1.205 +} {t1 1237}
1.206 +do_test autoinc-2.26 {
1.207 + execsql {
1.208 + DELETE FROM sqlite_sequence WHERE name='t1';
1.209 + INSERT INTO t1 VALUES(NULL,5);
1.210 + SELECT * FROM t1;
1.211 + }
1.212 +} {235 1 1235 2 1236 3 1237 4 1238 5}
1.213 +do_test autoinc-2.27 {
1.214 + execsql {
1.215 + SELECT * FROM sqlite_sequence;
1.216 + }
1.217 +} {t1 1238}
1.218 +do_test autoinc-2.28 {
1.219 + execsql {
1.220 + UPDATE sqlite_sequence SET seq='12345678901234567890'
1.221 + WHERE name='t1';
1.222 + INSERT INTO t1 VALUES(NULL,6);
1.223 + SELECT * FROM t1;
1.224 + }
1.225 +} {235 1 1235 2 1236 3 1237 4 1238 5 1239 6}
1.226 +do_test autoinc-2.29 {
1.227 + execsql {
1.228 + SELECT * FROM sqlite_sequence;
1.229 + }
1.230 +} {t1 1239}
1.231 +
1.232 +# Test multi-row inserts
1.233 +#
1.234 +do_test autoinc-2.50 {
1.235 + execsql {
1.236 + DELETE FROM t1 WHERE y>=3;
1.237 + INSERT INTO t1 SELECT NULL, y+2 FROM t1;
1.238 + SELECT * FROM t1;
1.239 + }
1.240 +} {235 1 1235 2 1240 3 1241 4}
1.241 +do_test autoinc-2.51 {
1.242 + execsql {
1.243 + SELECT * FROM sqlite_sequence
1.244 + }
1.245 +} {t1 1241}
1.246 +
1.247 +ifcapable tempdb {
1.248 + do_test autoinc-2.52 {
1.249 + execsql {
1.250 + CREATE TEMP TABLE t2 AS SELECT y FROM t1;
1.251 + }
1.252 + execsql {
1.253 + INSERT INTO t1 SELECT NULL, y+4 FROM t2;
1.254 + SELECT * FROM t1;
1.255 + }
1.256 + } {235 1 1235 2 1240 3 1241 4 1242 5 1243 6 1244 7 1245 8}
1.257 + do_test autoinc-2.53 {
1.258 + execsql {
1.259 + SELECT * FROM sqlite_sequence
1.260 + }
1.261 + } {t1 1245}
1.262 + do_test autoinc-2.54 {
1.263 + execsql {
1.264 + DELETE FROM t1;
1.265 + INSERT INTO t1 SELECT NULL, y FROM t2;
1.266 + SELECT * FROM t1;
1.267 + }
1.268 + } {1246 1 1247 2 1248 3 1249 4}
1.269 + do_test autoinc-2.55 {
1.270 + execsql {
1.271 + SELECT * FROM sqlite_sequence
1.272 + }
1.273 + } {t1 1249}
1.274 +}
1.275 +
1.276 +# Create multiple AUTOINCREMENT tables. Make sure all sequences are
1.277 +# tracked separately and do not interfere with one another.
1.278 +#
1.279 +do_test autoinc-2.70 {
1.280 + catchsql {
1.281 + DROP TABLE t2;
1.282 + }
1.283 + execsql {
1.284 + CREATE TABLE t2(d, e INTEGER PRIMARY KEY AUTOINCREMENT, f);
1.285 + INSERT INTO t2(d) VALUES(1);
1.286 + SELECT * FROM sqlite_sequence;
1.287 + }
1.288 +} [ifcapable tempdb {list t1 1249 t2 1} else {list t1 1241 t2 1}]
1.289 +do_test autoinc-2.71 {
1.290 + execsql {
1.291 + INSERT INTO t2(d) VALUES(2);
1.292 + SELECT * FROM sqlite_sequence;
1.293 + }
1.294 +} [ifcapable tempdb {list t1 1249 t2 2} else {list t1 1241 t2 2}]
1.295 +do_test autoinc-2.72 {
1.296 + execsql {
1.297 + INSERT INTO t1(x) VALUES(10000);
1.298 + SELECT * FROM sqlite_sequence;
1.299 + }
1.300 +} {t1 10000 t2 2}
1.301 +do_test autoinc-2.73 {
1.302 + execsql {
1.303 + CREATE TABLE t3(g INTEGER PRIMARY KEY AUTOINCREMENT, h);
1.304 + INSERT INTO t3(h) VALUES(1);
1.305 + SELECT * FROM sqlite_sequence;
1.306 + }
1.307 +} {t1 10000 t2 2 t3 1}
1.308 +do_test autoinc-2.74 {
1.309 + execsql {
1.310 + INSERT INTO t2(d,e) VALUES(3,100);
1.311 + SELECT * FROM sqlite_sequence;
1.312 + }
1.313 +} {t1 10000 t2 100 t3 1}
1.314 +
1.315 +
1.316 +# When a table with an AUTOINCREMENT is deleted, the corresponding entry
1.317 +# in the SQLITE_SEQUENCE table should also be deleted. But the SQLITE_SEQUENCE
1.318 +# table itself should remain behind.
1.319 +#
1.320 +do_test autoinc-3.1 {
1.321 + execsql {SELECT name FROM sqlite_sequence}
1.322 +} {t1 t2 t3}
1.323 +do_test autoinc-3.2 {
1.324 + execsql {
1.325 + DROP TABLE t1;
1.326 + SELECT name FROM sqlite_sequence;
1.327 + }
1.328 +} {t2 t3}
1.329 +do_test autoinc-3.3 {
1.330 + execsql {
1.331 + DROP TABLE t3;
1.332 + SELECT name FROM sqlite_sequence;
1.333 + }
1.334 +} {t2}
1.335 +do_test autoinc-3.4 {
1.336 + execsql {
1.337 + DROP TABLE t2;
1.338 + SELECT name FROM sqlite_sequence;
1.339 + }
1.340 +} {}
1.341 +
1.342 +# AUTOINCREMENT on TEMP tables.
1.343 +#
1.344 +ifcapable tempdb {
1.345 + do_test autoinc-4.1 {
1.346 + execsql {
1.347 + SELECT 1, name FROM sqlite_master WHERE type='table';
1.348 + SELECT 2, name FROM sqlite_temp_master WHERE type='table';
1.349 + }
1.350 + } {1 sqlite_sequence}
1.351 + do_test autoinc-4.2 {
1.352 + execsql {
1.353 + CREATE TABLE t1(x INTEGER PRIMARY KEY AUTOINCREMENT, y);
1.354 + CREATE TEMP TABLE t3(a INTEGER PRIMARY KEY AUTOINCREMENT, b);
1.355 + SELECT 1, name FROM sqlite_master WHERE type='table';
1.356 + SELECT 2, name FROM sqlite_temp_master WHERE type='table';
1.357 + }
1.358 + } {1 sqlite_sequence 1 t1 2 t3 2 sqlite_sequence}
1.359 + do_test autoinc-4.3 {
1.360 + execsql {
1.361 + SELECT 1, * FROM main.sqlite_sequence;
1.362 + SELECT 2, * FROM temp.sqlite_sequence;
1.363 + }
1.364 + } {}
1.365 + do_test autoinc-4.4 {
1.366 + execsql {
1.367 + INSERT INTO t1 VALUES(10,1);
1.368 + INSERT INTO t3 VALUES(20,2);
1.369 + INSERT INTO t1 VALUES(NULL,3);
1.370 + INSERT INTO t3 VALUES(NULL,4);
1.371 + }
1.372 + } {}
1.373 +
1.374 + ifcapable compound {
1.375 + do_test autoinc-4.4.1 {
1.376 + execsql {
1.377 + SELECT * FROM t1 UNION ALL SELECT * FROM t3;
1.378 + }
1.379 + } {10 1 11 3 20 2 21 4}
1.380 + } ;# ifcapable compound
1.381 +
1.382 + do_test autoinc-4.5 {
1.383 + execsql {
1.384 + SELECT 1, * FROM main.sqlite_sequence;
1.385 + SELECT 2, * FROM temp.sqlite_sequence;
1.386 + }
1.387 + } {1 t1 11 2 t3 21}
1.388 + do_test autoinc-4.6 {
1.389 + execsql {
1.390 + INSERT INTO t1 SELECT * FROM t3;
1.391 + SELECT 1, * FROM main.sqlite_sequence;
1.392 + SELECT 2, * FROM temp.sqlite_sequence;
1.393 + }
1.394 + } {1 t1 21 2 t3 21}
1.395 + do_test autoinc-4.7 {
1.396 + execsql {
1.397 + INSERT INTO t3 SELECT x+100, y FROM t1;
1.398 + SELECT 1, * FROM main.sqlite_sequence;
1.399 + SELECT 2, * FROM temp.sqlite_sequence;
1.400 + }
1.401 + } {1 t1 21 2 t3 121}
1.402 + do_test autoinc-4.8 {
1.403 + execsql {
1.404 + DROP TABLE t3;
1.405 + SELECT 1, * FROM main.sqlite_sequence;
1.406 + SELECT 2, * FROM temp.sqlite_sequence;
1.407 + }
1.408 + } {1 t1 21}
1.409 + do_test autoinc-4.9 {
1.410 + execsql {
1.411 + CREATE TEMP TABLE t2(p INTEGER PRIMARY KEY AUTOINCREMENT, q);
1.412 + INSERT INTO t2 SELECT * FROM t1;
1.413 + DROP TABLE t1;
1.414 + SELECT 1, * FROM main.sqlite_sequence;
1.415 + SELECT 2, * FROM temp.sqlite_sequence;
1.416 + }
1.417 + } {2 t2 21}
1.418 + do_test autoinc-4.10 {
1.419 + execsql {
1.420 + DROP TABLE t2;
1.421 + SELECT 1, * FROM main.sqlite_sequence;
1.422 + SELECT 2, * FROM temp.sqlite_sequence;
1.423 + }
1.424 + } {}
1.425 +}
1.426 +
1.427 +# Make sure AUTOINCREMENT works on ATTACH-ed tables.
1.428 +#
1.429 +ifcapable tempdb&&attach {
1.430 + do_test autoinc-5.1 {
1.431 + file delete -force test2.db
1.432 + file delete -force test2.db-journal
1.433 + sqlite3 db2 test2.db
1.434 + execsql {
1.435 + CREATE TABLE t4(m INTEGER PRIMARY KEY AUTOINCREMENT, n);
1.436 + CREATE TABLE t5(o, p INTEGER PRIMARY KEY AUTOINCREMENT);
1.437 + } db2;
1.438 + execsql {
1.439 + ATTACH 'test2.db' as aux;
1.440 + SELECT 1, * FROM main.sqlite_sequence;
1.441 + SELECT 2, * FROM temp.sqlite_sequence;
1.442 + SELECT 3, * FROM aux.sqlite_sequence;
1.443 + }
1.444 + } {}
1.445 + do_test autoinc-5.2 {
1.446 + execsql {
1.447 + INSERT INTO t4 VALUES(NULL,1);
1.448 + SELECT 1, * FROM main.sqlite_sequence;
1.449 + SELECT 2, * FROM temp.sqlite_sequence;
1.450 + SELECT 3, * FROM aux.sqlite_sequence;
1.451 + }
1.452 + } {3 t4 1}
1.453 + do_test autoinc-5.3 {
1.454 + execsql {
1.455 + INSERT INTO t5 VALUES(100,200);
1.456 + SELECT * FROM sqlite_sequence
1.457 + } db2
1.458 + } {t4 1 t5 200}
1.459 + do_test autoinc-5.4 {
1.460 + execsql {
1.461 + SELECT 1, * FROM main.sqlite_sequence;
1.462 + SELECT 2, * FROM temp.sqlite_sequence;
1.463 + SELECT 3, * FROM aux.sqlite_sequence;
1.464 + }
1.465 + } {3 t4 1 3 t5 200}
1.466 +}
1.467 +
1.468 +# Requirement REQ00310: Make sure an insert fails if the sequence is
1.469 +# already at its maximum value.
1.470 +#
1.471 +ifcapable {rowid32} {
1.472 + do_test autoinc-6.1 {
1.473 + execsql {
1.474 + CREATE TABLE t6(v INTEGER PRIMARY KEY AUTOINCREMENT, w);
1.475 + INSERT INTO t6 VALUES(2147483647,1);
1.476 + SELECT seq FROM main.sqlite_sequence WHERE name='t6';
1.477 + }
1.478 + } 2147483647
1.479 +}
1.480 +ifcapable {!rowid32} {
1.481 + do_test autoinc-6.1 {
1.482 + execsql {
1.483 + CREATE TABLE t6(v INTEGER PRIMARY KEY AUTOINCREMENT, w);
1.484 + INSERT INTO t6 VALUES(9223372036854775807,1);
1.485 + SELECT seq FROM main.sqlite_sequence WHERE name='t6';
1.486 + }
1.487 + } 9223372036854775807
1.488 +}
1.489 +do_test autoinc-6.2 {
1.490 + catchsql {
1.491 + INSERT INTO t6 VALUES(NULL,1);
1.492 + }
1.493 +} {1 {database or disk is full}}
1.494 +
1.495 +# Allow the AUTOINCREMENT keyword inside the parentheses
1.496 +# on a separate PRIMARY KEY designation.
1.497 +#
1.498 +do_test autoinc-7.1 {
1.499 + execsql {
1.500 + CREATE TABLE t7(x INTEGER, y REAL, PRIMARY KEY(x AUTOINCREMENT));
1.501 + INSERT INTO t7(y) VALUES(123);
1.502 + INSERT INTO t7(y) VALUES(234);
1.503 + DELETE FROM t7;
1.504 + INSERT INTO t7(y) VALUES(345);
1.505 + SELECT * FROM t7;
1.506 + }
1.507 +} {3 345.0}
1.508 +
1.509 +# Test that if the AUTOINCREMENT is applied to a non integer primary key
1.510 +# the error message is sensible.
1.511 +do_test autoinc-7.2 {
1.512 + catchsql {
1.513 + CREATE TABLE t8(x TEXT PRIMARY KEY AUTOINCREMENT);
1.514 + }
1.515 +} {1 {AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY}}
1.516 +
1.517 +
1.518 +# Ticket #1283. Make sure that preparing but never running a statement
1.519 +# that creates the sqlite_sequence table does not mess up the database.
1.520 +#
1.521 +do_test autoinc-8.1 {
1.522 + catch {db2 close}
1.523 + catch {db close}
1.524 + file delete -force test.db
1.525 + sqlite3 db test.db
1.526 + set DB [sqlite3_connection_pointer db]
1.527 + set STMT [sqlite3_prepare $DB {
1.528 + CREATE TABLE t1(
1.529 + x INTEGER PRIMARY KEY AUTOINCREMENT
1.530 + )
1.531 + } -1 TAIL]
1.532 + sqlite3_finalize $STMT
1.533 + set STMT [sqlite3_prepare $DB {
1.534 + CREATE TABLE t1(
1.535 + x INTEGER PRIMARY KEY AUTOINCREMENT
1.536 + )
1.537 + } -1 TAIL]
1.538 + sqlite3_step $STMT
1.539 + sqlite3_finalize $STMT
1.540 + execsql {
1.541 + INSERT INTO t1 VALUES(NULL);
1.542 + SELECT * FROM t1;
1.543 + }
1.544 +} {1}
1.545 +
1.546 +# Ticket #3148
1.547 +# Make sure the sqlite_sequence table is not damaged when doing
1.548 +# an empty insert - an INSERT INTO ... SELECT ... where the SELECT
1.549 +# clause returns an empty set.
1.550 +#
1.551 +do_test autoinc-9.1 {
1.552 + db eval {
1.553 + CREATE TABLE t2(x INTEGER PRIMARY KEY AUTOINCREMENT, y);
1.554 + INSERT INTO t2 VALUES(NULL, 1);
1.555 + CREATE TABLE t3(a INTEGER PRIMARY KEY AUTOINCREMENT, b);
1.556 + INSERT INTO t3 SELECT * FROM t2 WHERE y>1;
1.557 +
1.558 + SELECT * FROM sqlite_sequence WHERE name='t3';
1.559 + }
1.560 +} {t3 0}
1.561 +
1.562 +
1.563 +finish_test