2N/A#pragma ident "%Z%%M% %I% %E% SMI"
2N/A# The author disclaims copyright to this source code. In place of
2N/A# a legal notice, here is a blessing:
2N/A# May you do good and not evil.
2N/A# May you find forgiveness for yourself and forgive others.
2N/A# May you share freely, never taking more than you give.
2N/A#***********************************************************************
2N/A# This file implements regression tests for SQLite library.
2N/A# This file implements tests for the special processing associated
2N/A# with INTEGER PRIMARY KEY columns.
2N/Aset testdir [file dirname $argv0]
2N/A# Create a table with a primary key and a datatype other than
2N/Ado_test intpkey-1.0 {
2N/A CREATE TABLE t1(a TEXT PRIMARY KEY, b, c);
2N/A# There should be an index associated with the primary key
2N/Ado_test intpkey-1.1 {
2N/A SELECT name FROM sqlite_master
2N/A WHERE type='index' AND tbl_name='t1';
2N/A} {{(t1 autoindex 1)}}
2N/A# Now create a table with an integer primary key and verify that
2N/A# there is no associated index.
2N/Ado_test intpkey-1.2 {
2N/A CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
2N/A SELECT name FROM sqlite_master
2N/A WHERE type='index' AND tbl_name='t1';
2N/A# Insert some records into the new table. Specify the primary key
2N/A# and verify that the key is used as the record number.
2N/Ado_test intpkey-1.3 {
2N/A INSERT INTO t1 VALUES(5,'hello','world');
2N/A db last_insert_rowid
2N/Ado_test intpkey-1.4 {
2N/Ado_test intpkey-1.5 {
2N/A SELECT rowid, * FROM t1;
2N/A# Attempting to insert a duplicate primary key should give a constraint
2N/Ado_test intpkey-1.6 {
2N/A set r [catch {execsql {
2N/A INSERT INTO t1 VALUES(5,'second','entry');
2N/A} {1 {PRIMARY KEY must be unique}}
2N/Ado_test intpkey-1.7 {
2N/A SELECT rowid, * FROM t1;
2N/Ado_test intpkey-1.8 {
2N/A set r [catch {execsql {
2N/A INSERT INTO t1 VALUES(6,'second','entry');
2N/Ado_test intpkey-1.8.1 {
2N/A db last_insert_rowid
2N/Ado_test intpkey-1.9 {
2N/A SELECT rowid, * FROM t1;
2N/A} {5 5 hello world 6 6 second entry}
2N/A# A ROWID is automatically generated for new records that do not specify
2N/A# the integer primary key.
2N/Ado_test intpkey-1.10 {
2N/A INSERT INTO t1(b,c) VALUES('one','two');
2N/A SELECT b FROM t1 ORDER BY b;
2N/A# Try to change the ROWID for the new entry.
2N/Ado_test intpkey-1.11 {
2N/A UPDATE t1 SET a=4 WHERE b='one';
2N/A} {4 one two 5 hello world 6 second entry}
2N/A# Make sure SELECT statements are able to use the primary key column
2N/Ado_test intpkey-1.12 {
2N/A SELECT * FROM t1 WHERE a==4;
2N/A# Try to insert a non-integer value into the primary key field. This
2N/A# should result in a data type mismatch.
2N/Ado_test intpkey-1.13.1 {
2N/A set r [catch {execsql {
2N/A INSERT INTO t1 VALUES('x','y','z');
2N/A} {1 {datatype mismatch}}
2N/Ado_test intpkey-1.13.2 {
2N/A set r [catch {execsql {
2N/A INSERT INTO t1 VALUES('','y','z');
2N/A} {1 {datatype mismatch}}
2N/Ado_test intpkey-1.14 {
2N/A set r [catch {execsql {
2N/A INSERT INTO t1 VALUES(3.4,'y','z');
2N/A} {1 {datatype mismatch}}
2N/Ado_test intpkey-1.15 {
2N/A set r [catch {execsql {
2N/A INSERT INTO t1 VALUES(-3,'y','z');
2N/Ado_test intpkey-1.16 {
2N/A execsql {SELECT * FROM t1}
2N/A} {-3 y z 4 one two 5 hello world 6 second entry}
2N/A# Check to make sure indices work correctly with integer primary keys
2N/Ado_test intpkey-2.1 {
2N/A CREATE INDEX i1 ON t1(b);
2N/A SELECT * FROM t1 WHERE b=='y'
2N/Ado_test intpkey-2.1.1 {
2N/A SELECT * FROM t1 WHERE b=='y' AND rowid<0
2N/Ado_test intpkey-2.1.2 {
2N/A SELECT * FROM t1 WHERE b=='y' AND rowid<0 AND rowid>=-20
2N/Ado_test intpkey-2.1.3 {
2N/A SELECT * FROM t1 WHERE b>='y'
2N/Ado_test intpkey-2.1.4 {
2N/A SELECT * FROM t1 WHERE b>='y' AND rowid<10
2N/Ado_test intpkey-2.2 {
2N/A UPDATE t1 SET a=8 WHERE b=='y';
2N/A SELECT * FROM t1 WHERE b=='y';
2N/Ado_test intpkey-2.3 {
2N/A SELECT rowid, * FROM t1;
2N/A} {4 4 one two 5 5 hello world 6 6 second entry 8 8 y z}
2N/Ado_test intpkey-2.4 {
2N/A SELECT rowid, * FROM t1 WHERE b<'second'
2N/A} {5 5 hello world 4 4 one two}
2N/Ado_test intpkey-2.4.1 {
2N/A SELECT rowid, * FROM t1 WHERE 'second'>b
2N/A} {5 5 hello world 4 4 one two}
2N/Ado_test intpkey-2.4.2 {
2N/A SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b
2N/A} {4 4 one two 5 5 hello world}
2N/Ado_test intpkey-2.4.3 {
2N/A SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b AND 0<rowid
2N/A} {4 4 one two 5 5 hello world}
2N/Ado_test intpkey-2.5 {
2N/A SELECT rowid, * FROM t1 WHERE b>'a'
2N/A} {5 5 hello world 4 4 one two 6 6 second entry 8 8 y z}
2N/Ado_test intpkey-2.6 {
2N/A DELETE FROM t1 WHERE rowid=4;
2N/A SELECT * FROM t1 WHERE b>'a';
2N/A} {5 hello world 6 second entry 8 y z}
2N/Ado_test intpkey-2.7 {
2N/A UPDATE t1 SET a=-4 WHERE rowid=8;
2N/A SELECT * FROM t1 WHERE b>'a';
2N/A} {5 hello world 6 second entry -4 y z}
2N/Ado_test intpkey-2.7 {
2N/A} {-4 y z 5 hello world 6 second entry}
2N/A# Do an SQL statement. Append the search count to the end of the result.
2N/A set ::sqlite_search_count 0
2N/A return [concat [execsql $sql] $::sqlite_search_count]
2N/A# Create indices that include the integer primary key as one of their
2N/Ado_test intpkey-3.1 {
2N/A CREATE INDEX i2 ON t1(a);
2N/Ado_test intpkey-3.2 {
2N/A SELECT * FROM t1 WHERE a=5;
2N/Ado_test intpkey-3.3 {
2N/A SELECT * FROM t1 WHERE a>4 AND a<6;
2N/Ado_test intpkey-3.4 {
2N/A SELECT * FROM t1 WHERE b>='hello' AND b<'hello2';
2N/Ado_test intpkey-3.5 {
2N/A CREATE INDEX i3 ON t1(c,a);
2N/Ado_test intpkey-3.6 {
2N/A SELECT * FROM t1 WHERE c=='world';
2N/Ado_test intpkey-3.7 {
2N/A execsql {INSERT INTO t1 VALUES(11,'hello','world')}
2N/A SELECT * FROM t1 WHERE c=='world';
2N/A} {5 hello world 11 hello world 5}
2N/Ado_test intpkey-3.8 {
2N/A SELECT * FROM t1 WHERE c=='world' AND a>7;
2N/Ado_test intpkey-3.9 {
2N/A SELECT * FROM t1 WHERE 7<a;
2N/A# Test inequality constraints on integer primary keys and rowids
2N/Ado_test intpkey-4.1 {
2N/A SELECT * FROM t1 WHERE 11=rowid
2N/Ado_test intpkey-4.2 {
2N/A SELECT * FROM t1 WHERE 11=rowid AND b=='hello'
2N/Ado_test intpkey-4.3 {
2N/A SELECT * FROM t1 WHERE 11=rowid AND b=='hello' AND c IS NOT NULL;
2N/Ado_test intpkey-4.4 {
2N/A SELECT * FROM t1 WHERE rowid==11
2N/Ado_test intpkey-4.5 {
2N/A SELECT * FROM t1 WHERE oid==11 AND b=='hello'
2N/Ado_test intpkey-4.6 {
2N/A SELECT * FROM t1 WHERE a==11 AND b=='hello' AND c IS NOT NULL;
2N/Ado_test intpkey-4.7 {
2N/A SELECT * FROM t1 WHERE 8<rowid;
2N/Ado_test intpkey-4.8 {
2N/A SELECT * FROM t1 WHERE 8<rowid AND 11>=oid;
2N/Ado_test intpkey-4.9 {
2N/A SELECT * FROM t1 WHERE 11<=_rowid_ AND 12>=a;
2N/Ado_test intpkey-4.10 {
2N/A SELECT * FROM t1 WHERE 0>=_rowid_;
2N/Ado_test intpkey-4.11 {
2N/A SELECT * FROM t1 WHERE a<0;
2N/Ado_test intpkey-4.12 {
2N/A SELECT * FROM t1 WHERE a<0 AND a>10;
2N/A# Make sure it is OK to insert a rowid of 0
2N/Ado_test intpkey-5.1 {
2N/A INSERT INTO t1 VALUES(0,'zero','entry');
2N/A SELECT * FROM t1 WHERE a=0;
2N/Ado_test intpkey=5.2 {
2N/A SELECT rowid, a FROM t1
2N/A} {-4 -4 0 0 5 5 6 6 11 11}
2N/A# Test the ability of the COPY command to put data into a
2N/A# table that contains an integer primary key.
2N/Ado_test intpkey-6.1 {
2N/A puts $f "20\tb-20\tc-20"
2N/A puts $f "21\tb-21\tc-21"
2N/A puts $f "22\tb-22\tc-22"
2N/A SELECT * FROM t1 WHERE a>=20;
2N/A} {20 b-20 c-20 21 b-21 c-21 22 b-22 c-22}
2N/Ado_test intpkey-6.2 {
2N/A SELECT * FROM t1 WHERE b=='hello'
2N/A} {5 hello world 11 hello world}
2N/Ado_test intpkey-6.3 {
2N/A DELETE FROM t1 WHERE b='b-21';
2N/A SELECT * FROM t1 WHERE b=='b-21';
2N/Ado_test intpkey-6.4 {
2N/A SELECT * FROM t1 WHERE a>=20
2N/A} {20 b-20 c-20 22 b-22 c-22}
2N/A# Do an insert of values with the columns specified out of order.
2N/Ado_test intpkey-7.1 {
2N/A INSERT INTO t1(c,b,a) VALUES('row','new',30);
2N/A SELECT * FROM t1 WHERE rowid>=30;
2N/Ado_test intpkey-7.2 {
2N/A SELECT * FROM t1 WHERE rowid>20;
2N/A} {22 b-22 c-22 30 new row}
2N/A# Do an insert from a select statement.
2N/Ado_test intpkey-8.1 {
2N/A CREATE TABLE t2(x INTEGER PRIMARY KEY, y, z);
2N/A INSERT INTO t2 SELECT * FROM t1;
2N/A SELECT rowid FROM t2;
2N/A} {-4 0 5 6 11 20 22 30}
2N/Ado_test intpkey-8.2 {
2N/A} {-4 0 5 6 11 20 22 30}
2N/Ado_test intpkey-9.1 {
2N/A UPDATE t1 SET c='www' WHERE c='world';
2N/A SELECT rowid, a, c FROM t1 WHERE c=='www';
2N/A} {5 5 www 11 11 www}
2N/A# Check insert of NULL for primary key
2N/Ado_test intpkey-10.1 {
2N/A CREATE TABLE t2(x INTEGER PRIMARY KEY, y, z);
2N/A INSERT INTO t2 VALUES(NULL, 1, 2);
2N/Ado_test intpkey-10.2 {
2N/A INSERT INTO t2 VALUES(NULL, 2, 3);
2N/A SELECT * from t2 WHERE x=2;
2N/Ado_test intpkey-10.3 {
2N/A INSERT INTO t2 SELECT NULL, z, y FROM t2;
2N/A} {1 1 2 2 2 3 3 2 1 4 3 2}
2N/A# This tests checks to see if a floating point number can be used
2N/A# to reference an integer primary key.
2N/Ado_test intpkey-11.1 {
2N/A SELECT b FROM t1 WHERE a=2.0+3.0;
2N/Ado_test intpkey-11.1 {
2N/A SELECT b FROM t1 WHERE a=2.0+3.5;
2N/Aintegrity_check intpkey-12.1