1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/intpkey.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,605 @@
1.4 +# 2001 September 15
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.
1.15 +#
1.16 +# This file implements tests for the special processing associated
1.17 +# with INTEGER PRIMARY KEY columns.
1.18 +#
1.19 +# $Id: intpkey.test,v 1.24 2007/11/29 17:43:28 danielk1977 Exp $
1.20 +
1.21 +set testdir [file dirname $argv0]
1.22 +source $testdir/tester.tcl
1.23 +
1.24 +# Create a table with a primary key and a datatype other than
1.25 +# integer
1.26 +#
1.27 +do_test intpkey-1.0 {
1.28 + execsql {
1.29 + CREATE TABLE t1(a TEXT PRIMARY KEY, b, c);
1.30 + }
1.31 +} {}
1.32 +
1.33 +# There should be an index associated with the primary key
1.34 +#
1.35 +do_test intpkey-1.1 {
1.36 + execsql {
1.37 + SELECT name FROM sqlite_master
1.38 + WHERE type='index' AND tbl_name='t1';
1.39 + }
1.40 +} {sqlite_autoindex_t1_1}
1.41 +
1.42 +# Now create a table with an integer primary key and verify that
1.43 +# there is no associated index.
1.44 +#
1.45 +do_test intpkey-1.2 {
1.46 + execsql {
1.47 + DROP TABLE t1;
1.48 + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
1.49 + SELECT name FROM sqlite_master
1.50 + WHERE type='index' AND tbl_name='t1';
1.51 + }
1.52 +} {}
1.53 +
1.54 +# Insert some records into the new table. Specify the primary key
1.55 +# and verify that the key is used as the record number.
1.56 +#
1.57 +do_test intpkey-1.3 {
1.58 + execsql {
1.59 + INSERT INTO t1 VALUES(5,'hello','world');
1.60 + }
1.61 + db last_insert_rowid
1.62 +} {5}
1.63 +do_test intpkey-1.4 {
1.64 + execsql {
1.65 + SELECT * FROM t1;
1.66 + }
1.67 +} {5 hello world}
1.68 +do_test intpkey-1.5 {
1.69 + execsql {
1.70 + SELECT rowid, * FROM t1;
1.71 + }
1.72 +} {5 5 hello world}
1.73 +
1.74 +# Attempting to insert a duplicate primary key should give a constraint
1.75 +# failure.
1.76 +#
1.77 +do_test intpkey-1.6 {
1.78 + set r [catch {execsql {
1.79 + INSERT INTO t1 VALUES(5,'second','entry');
1.80 + }} msg]
1.81 + lappend r $msg
1.82 +} {1 {PRIMARY KEY must be unique}}
1.83 +do_test intpkey-1.7 {
1.84 + execsql {
1.85 + SELECT rowid, * FROM t1;
1.86 + }
1.87 +} {5 5 hello world}
1.88 +do_test intpkey-1.8 {
1.89 + set r [catch {execsql {
1.90 + INSERT INTO t1 VALUES(6,'second','entry');
1.91 + }} msg]
1.92 + lappend r $msg
1.93 +} {0 {}}
1.94 +do_test intpkey-1.8.1 {
1.95 + db last_insert_rowid
1.96 +} {6}
1.97 +do_test intpkey-1.9 {
1.98 + execsql {
1.99 + SELECT rowid, * FROM t1;
1.100 + }
1.101 +} {5 5 hello world 6 6 second entry}
1.102 +
1.103 +# A ROWID is automatically generated for new records that do not specify
1.104 +# the integer primary key.
1.105 +#
1.106 +do_test intpkey-1.10 {
1.107 + execsql {
1.108 + INSERT INTO t1(b,c) VALUES('one','two');
1.109 + SELECT b FROM t1 ORDER BY b;
1.110 + }
1.111 +} {hello one second}
1.112 +
1.113 +# Try to change the ROWID for the new entry.
1.114 +#
1.115 +do_test intpkey-1.11 {
1.116 + execsql {
1.117 + UPDATE t1 SET a=4 WHERE b='one';
1.118 + SELECT * FROM t1;
1.119 + }
1.120 +} {4 one two 5 hello world 6 second entry}
1.121 +
1.122 +# Make sure SELECT statements are able to use the primary key column
1.123 +# as an index.
1.124 +#
1.125 +do_test intpkey-1.12.1 {
1.126 + execsql {
1.127 + SELECT * FROM t1 WHERE a==4;
1.128 + }
1.129 +} {4 one two}
1.130 +do_test intpkey-1.12.2 {
1.131 + set sqlite_query_plan
1.132 +} {t1 *}
1.133 +
1.134 +# Try to insert a non-integer value into the primary key field. This
1.135 +# should result in a data type mismatch.
1.136 +#
1.137 +do_test intpkey-1.13.1 {
1.138 + set r [catch {execsql {
1.139 + INSERT INTO t1 VALUES('x','y','z');
1.140 + }} msg]
1.141 + lappend r $msg
1.142 +} {1 {datatype mismatch}}
1.143 +do_test intpkey-1.13.2 {
1.144 + set r [catch {execsql {
1.145 + INSERT INTO t1 VALUES('','y','z');
1.146 + }} msg]
1.147 + lappend r $msg
1.148 +} {1 {datatype mismatch}}
1.149 +do_test intpkey-1.14 {
1.150 + set r [catch {execsql {
1.151 + INSERT INTO t1 VALUES(3.4,'y','z');
1.152 + }} msg]
1.153 + lappend r $msg
1.154 +} {1 {datatype mismatch}}
1.155 +do_test intpkey-1.15 {
1.156 + set r [catch {execsql {
1.157 + INSERT INTO t1 VALUES(-3,'y','z');
1.158 + }} msg]
1.159 + lappend r $msg
1.160 +} {0 {}}
1.161 +do_test intpkey-1.16 {
1.162 + execsql {SELECT * FROM t1}
1.163 +} {-3 y z 4 one two 5 hello world 6 second entry}
1.164 +
1.165 +#### INDICES
1.166 +# Check to make sure indices work correctly with integer primary keys
1.167 +#
1.168 +do_test intpkey-2.1 {
1.169 + execsql {
1.170 + CREATE INDEX i1 ON t1(b);
1.171 + SELECT * FROM t1 WHERE b=='y'
1.172 + }
1.173 +} {-3 y z}
1.174 +do_test intpkey-2.1.1 {
1.175 + execsql {
1.176 + SELECT * FROM t1 WHERE b=='y' AND rowid<0
1.177 + }
1.178 +} {-3 y z}
1.179 +do_test intpkey-2.1.2 {
1.180 + execsql {
1.181 + SELECT * FROM t1 WHERE b=='y' AND rowid<0 AND rowid>=-20
1.182 + }
1.183 +} {-3 y z}
1.184 +do_test intpkey-2.1.3 {
1.185 + execsql {
1.186 + SELECT * FROM t1 WHERE b>='y'
1.187 + }
1.188 +} {-3 y z}
1.189 +do_test intpkey-2.1.4 {
1.190 + execsql {
1.191 + SELECT * FROM t1 WHERE b>='y' AND rowid<10
1.192 + }
1.193 +} {-3 y z}
1.194 +
1.195 +do_test intpkey-2.2 {
1.196 + execsql {
1.197 + UPDATE t1 SET a=8 WHERE b=='y';
1.198 + SELECT * FROM t1 WHERE b=='y';
1.199 + }
1.200 +} {8 y z}
1.201 +do_test intpkey-2.3 {
1.202 + execsql {
1.203 + SELECT rowid, * FROM t1;
1.204 + }
1.205 +} {4 4 one two 5 5 hello world 6 6 second entry 8 8 y z}
1.206 +do_test intpkey-2.4 {
1.207 + execsql {
1.208 + SELECT rowid, * FROM t1 WHERE b<'second'
1.209 + }
1.210 +} {5 5 hello world 4 4 one two}
1.211 +do_test intpkey-2.4.1 {
1.212 + execsql {
1.213 + SELECT rowid, * FROM t1 WHERE 'second'>b
1.214 + }
1.215 +} {5 5 hello world 4 4 one two}
1.216 +do_test intpkey-2.4.2 {
1.217 + execsql {
1.218 + SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b
1.219 + }
1.220 +} {4 4 one two 5 5 hello world}
1.221 +do_test intpkey-2.4.3 {
1.222 + execsql {
1.223 + SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b AND 0<rowid
1.224 + }
1.225 +} {4 4 one two 5 5 hello world}
1.226 +do_test intpkey-2.5 {
1.227 + execsql {
1.228 + SELECT rowid, * FROM t1 WHERE b>'a'
1.229 + }
1.230 +} {5 5 hello world 4 4 one two 6 6 second entry 8 8 y z}
1.231 +do_test intpkey-2.6 {
1.232 + execsql {
1.233 + DELETE FROM t1 WHERE rowid=4;
1.234 + SELECT * FROM t1 WHERE b>'a';
1.235 + }
1.236 +} {5 hello world 6 second entry 8 y z}
1.237 +do_test intpkey-2.7 {
1.238 + execsql {
1.239 + UPDATE t1 SET a=-4 WHERE rowid=8;
1.240 + SELECT * FROM t1 WHERE b>'a';
1.241 + }
1.242 +} {5 hello world 6 second entry -4 y z}
1.243 +do_test intpkey-2.7 {
1.244 + execsql {
1.245 + SELECT * FROM t1
1.246 + }
1.247 +} {-4 y z 5 hello world 6 second entry}
1.248 +
1.249 +# Do an SQL statement. Append the search count to the end of the result.
1.250 +#
1.251 +proc count sql {
1.252 + set ::sqlite_search_count 0
1.253 + return [concat [execsql $sql] $::sqlite_search_count]
1.254 +}
1.255 +
1.256 +# Create indices that include the integer primary key as one of their
1.257 +# columns.
1.258 +#
1.259 +do_test intpkey-3.1 {
1.260 + execsql {
1.261 + CREATE INDEX i2 ON t1(a);
1.262 + }
1.263 +} {}
1.264 +do_test intpkey-3.2 {
1.265 + count {
1.266 + SELECT * FROM t1 WHERE a=5;
1.267 + }
1.268 +} {5 hello world 0}
1.269 +do_test intpkey-3.3 {
1.270 + count {
1.271 + SELECT * FROM t1 WHERE a>4 AND a<6;
1.272 + }
1.273 +} {5 hello world 2}
1.274 +do_test intpkey-3.4 {
1.275 + count {
1.276 + SELECT * FROM t1 WHERE b>='hello' AND b<'hello2';
1.277 + }
1.278 +} {5 hello world 3}
1.279 +do_test intpkey-3.5 {
1.280 + execsql {
1.281 + CREATE INDEX i3 ON t1(c,a);
1.282 + }
1.283 +} {}
1.284 +do_test intpkey-3.6 {
1.285 + count {
1.286 + SELECT * FROM t1 WHERE c=='world';
1.287 + }
1.288 +} {5 hello world 3}
1.289 +do_test intpkey-3.7 {
1.290 + execsql {INSERT INTO t1 VALUES(11,'hello','world')}
1.291 + count {
1.292 + SELECT * FROM t1 WHERE c=='world';
1.293 + }
1.294 +} {5 hello world 11 hello world 5}
1.295 +do_test intpkey-3.8 {
1.296 + count {
1.297 + SELECT * FROM t1 WHERE c=='world' AND a>7;
1.298 + }
1.299 +} {11 hello world 4}
1.300 +do_test intpkey-3.9 {
1.301 + count {
1.302 + SELECT * FROM t1 WHERE 7<a;
1.303 + }
1.304 +} {11 hello world 1}
1.305 +
1.306 +# Test inequality constraints on integer primary keys and rowids
1.307 +#
1.308 +do_test intpkey-4.1 {
1.309 + count {
1.310 + SELECT * FROM t1 WHERE 11=rowid
1.311 + }
1.312 +} {11 hello world 0}
1.313 +do_test intpkey-4.2 {
1.314 + count {
1.315 + SELECT * FROM t1 WHERE 11=rowid AND b=='hello'
1.316 + }
1.317 +} {11 hello world 0}
1.318 +do_test intpkey-4.3 {
1.319 + count {
1.320 + SELECT * FROM t1 WHERE 11=rowid AND b=='hello' AND c IS NOT NULL;
1.321 + }
1.322 +} {11 hello world 0}
1.323 +do_test intpkey-4.4 {
1.324 + count {
1.325 + SELECT * FROM t1 WHERE rowid==11
1.326 + }
1.327 +} {11 hello world 0}
1.328 +do_test intpkey-4.5 {
1.329 + count {
1.330 + SELECT * FROM t1 WHERE oid==11 AND b=='hello'
1.331 + }
1.332 +} {11 hello world 0}
1.333 +do_test intpkey-4.6 {
1.334 + count {
1.335 + SELECT * FROM t1 WHERE a==11 AND b=='hello' AND c IS NOT NULL;
1.336 + }
1.337 +} {11 hello world 0}
1.338 +
1.339 +do_test intpkey-4.7 {
1.340 + count {
1.341 + SELECT * FROM t1 WHERE 8<rowid;
1.342 + }
1.343 +} {11 hello world 1}
1.344 +do_test intpkey-4.8 {
1.345 + count {
1.346 + SELECT * FROM t1 WHERE 8<rowid AND 11>=oid;
1.347 + }
1.348 +} {11 hello world 1}
1.349 +do_test intpkey-4.9 {
1.350 + count {
1.351 + SELECT * FROM t1 WHERE 11<=_rowid_ AND 12>=a;
1.352 + }
1.353 +} {11 hello world 1}
1.354 +do_test intpkey-4.10 {
1.355 + count {
1.356 + SELECT * FROM t1 WHERE 0>=_rowid_;
1.357 + }
1.358 +} {-4 y z 1}
1.359 +do_test intpkey-4.11 {
1.360 + count {
1.361 + SELECT * FROM t1 WHERE a<0;
1.362 + }
1.363 +} {-4 y z 1}
1.364 +do_test intpkey-4.12 {
1.365 + count {
1.366 + SELECT * FROM t1 WHERE a<0 AND a>10;
1.367 + }
1.368 +} {1}
1.369 +
1.370 +# Make sure it is OK to insert a rowid of 0
1.371 +#
1.372 +do_test intpkey-5.1 {
1.373 + execsql {
1.374 + INSERT INTO t1 VALUES(0,'zero','entry');
1.375 + }
1.376 + count {
1.377 + SELECT * FROM t1 WHERE a=0;
1.378 + }
1.379 +} {0 zero entry 0}
1.380 +do_test intpkey-5.2 {
1.381 + execsql {
1.382 + SELECT rowid, a FROM t1
1.383 + }
1.384 +} {-4 -4 0 0 5 5 6 6 11 11}
1.385 +
1.386 +# Test the ability of the COPY command to put data into a
1.387 +# table that contains an integer primary key.
1.388 +#
1.389 +# COPY command has been removed. But we retain these tests so
1.390 +# that the tables will contain the right data for tests that follow.
1.391 +#
1.392 +do_test intpkey-6.1 {
1.393 + execsql {
1.394 + BEGIN;
1.395 + INSERT INTO t1 VALUES(20,'b-20','c-20');
1.396 + INSERT INTO t1 VALUES(21,'b-21','c-21');
1.397 + INSERT INTO t1 VALUES(22,'b-22','c-22');
1.398 + COMMIT;
1.399 + SELECT * FROM t1 WHERE a>=20;
1.400 + }
1.401 +} {20 b-20 c-20 21 b-21 c-21 22 b-22 c-22}
1.402 +do_test intpkey-6.2 {
1.403 + execsql {
1.404 + SELECT * FROM t1 WHERE b=='hello'
1.405 + }
1.406 +} {5 hello world 11 hello world}
1.407 +do_test intpkey-6.3 {
1.408 + execsql {
1.409 + DELETE FROM t1 WHERE b='b-21';
1.410 + SELECT * FROM t1 WHERE b=='b-21';
1.411 + }
1.412 +} {}
1.413 +do_test intpkey-6.4 {
1.414 + execsql {
1.415 + SELECT * FROM t1 WHERE a>=20
1.416 + }
1.417 +} {20 b-20 c-20 22 b-22 c-22}
1.418 +
1.419 +# Do an insert of values with the columns specified out of order.
1.420 +#
1.421 +do_test intpkey-7.1 {
1.422 + execsql {
1.423 + INSERT INTO t1(c,b,a) VALUES('row','new',30);
1.424 + SELECT * FROM t1 WHERE rowid>=30;
1.425 + }
1.426 +} {30 new row}
1.427 +do_test intpkey-7.2 {
1.428 + execsql {
1.429 + SELECT * FROM t1 WHERE rowid>20;
1.430 + }
1.431 +} {22 b-22 c-22 30 new row}
1.432 +
1.433 +# Do an insert from a select statement.
1.434 +#
1.435 +do_test intpkey-8.1 {
1.436 + execsql {
1.437 + CREATE TABLE t2(x INTEGER PRIMARY KEY, y, z);
1.438 + INSERT INTO t2 SELECT * FROM t1;
1.439 + SELECT rowid FROM t2;
1.440 + }
1.441 +} {-4 0 5 6 11 20 22 30}
1.442 +do_test intpkey-8.2 {
1.443 + execsql {
1.444 + SELECT x FROM t2;
1.445 + }
1.446 +} {-4 0 5 6 11 20 22 30}
1.447 +
1.448 +do_test intpkey-9.1 {
1.449 + execsql {
1.450 + UPDATE t1 SET c='www' WHERE c='world';
1.451 + SELECT rowid, a, c FROM t1 WHERE c=='www';
1.452 + }
1.453 +} {5 5 www 11 11 www}
1.454 +
1.455 +
1.456 +# Check insert of NULL for primary key
1.457 +#
1.458 +do_test intpkey-10.1 {
1.459 + execsql {
1.460 + DROP TABLE t2;
1.461 + CREATE TABLE t2(x INTEGER PRIMARY KEY, y, z);
1.462 + INSERT INTO t2 VALUES(NULL, 1, 2);
1.463 + SELECT * from t2;
1.464 + }
1.465 +} {1 1 2}
1.466 +do_test intpkey-10.2 {
1.467 + execsql {
1.468 + INSERT INTO t2 VALUES(NULL, 2, 3);
1.469 + SELECT * from t2 WHERE x=2;
1.470 + }
1.471 +} {2 2 3}
1.472 +do_test intpkey-10.3 {
1.473 + execsql {
1.474 + INSERT INTO t2 SELECT NULL, z, y FROM t2;
1.475 + SELECT * FROM t2;
1.476 + }
1.477 +} {1 1 2 2 2 3 3 2 1 4 3 2}
1.478 +
1.479 +# This tests checks to see if a floating point number can be used
1.480 +# to reference an integer primary key.
1.481 +#
1.482 +do_test intpkey-11.1 {
1.483 + execsql {
1.484 + SELECT b FROM t1 WHERE a=2.0+3.0;
1.485 + }
1.486 +} {hello}
1.487 +do_test intpkey-11.1 {
1.488 + execsql {
1.489 + SELECT b FROM t1 WHERE a=2.0+3.5;
1.490 + }
1.491 +} {}
1.492 +
1.493 +integrity_check intpkey-12.1
1.494 +
1.495 +# Try to use a string that looks like a floating point number as
1.496 +# an integer primary key. This should actually work when the floating
1.497 +# point value can be rounded to an integer without loss of data.
1.498 +#
1.499 +do_test intpkey-13.1 {
1.500 + execsql {
1.501 + SELECT * FROM t1 WHERE a=1;
1.502 + }
1.503 +} {}
1.504 +do_test intpkey-13.2 {
1.505 + execsql {
1.506 + INSERT INTO t1 VALUES('1.0',2,3);
1.507 + SELECT * FROM t1 WHERE a=1;
1.508 + }
1.509 +} {1 2 3}
1.510 +do_test intpkey-13.3 {
1.511 + catchsql {
1.512 + INSERT INTO t1 VALUES('1.5',3,4);
1.513 + }
1.514 +} {1 {datatype mismatch}}
1.515 +ifcapable {bloblit} {
1.516 + do_test intpkey-13.4 {
1.517 + catchsql {
1.518 + INSERT INTO t1 VALUES(x'123456',3,4);
1.519 + }
1.520 + } {1 {datatype mismatch}}
1.521 +}
1.522 +do_test intpkey-13.5 {
1.523 + catchsql {
1.524 + INSERT INTO t1 VALUES('+1234567890',3,4);
1.525 + }
1.526 +} {0 {}}
1.527 +
1.528 +# Compare an INTEGER PRIMARY KEY against a TEXT expression. The INTEGER
1.529 +# affinity should be applied to the text value before the comparison
1.530 +# takes place.
1.531 +#
1.532 +do_test intpkey-14.1 {
1.533 + execsql {
1.534 + CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT);
1.535 + INSERT INTO t3 VALUES(1, 1, 'one');
1.536 + INSERT INTO t3 VALUES(2, 2, '2');
1.537 + INSERT INTO t3 VALUES(3, 3, 3);
1.538 + }
1.539 +} {}
1.540 +do_test intpkey-14.2 {
1.541 + execsql {
1.542 + SELECT * FROM t3 WHERE a>2;
1.543 + }
1.544 +} {3 3 3}
1.545 +do_test intpkey-14.3 {
1.546 + execsql {
1.547 + SELECT * FROM t3 WHERE a>'2';
1.548 + }
1.549 +} {3 3 3}
1.550 +do_test intpkey-14.4 {
1.551 + execsql {
1.552 + SELECT * FROM t3 WHERE a<'2';
1.553 + }
1.554 +} {1 1 one}
1.555 +do_test intpkey-14.5 {
1.556 + execsql {
1.557 + SELECT * FROM t3 WHERE a<c;
1.558 + }
1.559 +} {1 1 one}
1.560 +do_test intpkey-14.6 {
1.561 + execsql {
1.562 + SELECT * FROM t3 WHERE a=c;
1.563 + }
1.564 +} {2 2 2 3 3 3}
1.565 +
1.566 +# Check for proper handling of primary keys greater than 2^31.
1.567 +# Ticket #1188
1.568 +#
1.569 +do_test intpkey-15.1 {
1.570 + execsql {
1.571 + INSERT INTO t1 VALUES(2147483647, 'big-1', 123);
1.572 + SELECT * FROM t1 WHERE a>2147483648;
1.573 + }
1.574 +} {}
1.575 +do_test intpkey-15.2 {
1.576 + execsql {
1.577 + INSERT INTO t1 VALUES(NULL, 'big-2', 234);
1.578 + SELECT b FROM t1 WHERE a>=2147483648;
1.579 + }
1.580 +} {big-2}
1.581 +do_test intpkey-15.3 {
1.582 + execsql {
1.583 + SELECT b FROM t1 WHERE a>2147483648;
1.584 + }
1.585 +} {}
1.586 +do_test intpkey-15.4 {
1.587 + execsql {
1.588 + SELECT b FROM t1 WHERE a>=2147483647;
1.589 + }
1.590 +} {big-1 big-2}
1.591 +do_test intpkey-15.5 {
1.592 + execsql {
1.593 + SELECT b FROM t1 WHERE a<2147483648;
1.594 + }
1.595 +} {y zero 2 hello second hello b-20 b-22 new 3 big-1}
1.596 +do_test intpkey-15.6 {
1.597 + execsql {
1.598 + SELECT b FROM t1 WHERE a<12345678901;
1.599 + }
1.600 +} {y zero 2 hello second hello b-20 b-22 new 3 big-1 big-2}
1.601 +do_test intpkey-15.7 {
1.602 + execsql {
1.603 + SELECT b FROM t1 WHERE a>12345678901;
1.604 + }
1.605 +} {}
1.606 +
1.607 +
1.608 +finish_test