# 2005 November 2 # # 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 file is testing CHECK constraints # # $Id: check.test,v 1.13 2009/06/05 17:09:12 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl set ::testprefix check # Only run these tests if the build includes support for CHECK constraints ifcapable !check { finish_test return } do_test check-1.1 { execsql { CREATE TABLE t1( x INTEGER CHECK( x<5 ), y REAL CHECK( y>x ) ); } } {} do_test check-1.2 { execsql { INSERT INTO t1 VALUES(3,4); SELECT * FROM t1; } } {3 4.0} do_test check-1.3 { catchsql { INSERT INTO t1 VALUES(6,7); } } {1 {CHECK constraint failed: t1}} do_test check-1.4 { execsql { SELECT * FROM t1; } } {3 4.0} do_test check-1.5 { catchsql { INSERT INTO t1 VALUES(4,3); } } {1 {CHECK constraint failed: t1}} do_test check-1.6 { execsql { SELECT * FROM t1; } } {3 4.0} do_test check-1.7 { catchsql { INSERT INTO t1 VALUES(NULL,6); } } {0 {}} do_test check-1.8 { execsql { SELECT * FROM t1; } } {3 4.0 {} 6.0} do_test check-1.9 { catchsql { INSERT INTO t1 VALUES(2,NULL); } } {0 {}} do_test check-1.10 { execsql { SELECT * FROM t1; } } {3 4.0 {} 6.0 2 {}} do_test check-1.11 { execsql { DELETE FROM t1 WHERE x IS NULL OR x!=3; UPDATE t1 SET x=2 WHERE x==3; SELECT * FROM t1; } } {2 4.0} do_test check-1.12 { catchsql { UPDATE t1 SET x=7 WHERE x==2 } } {1 {CHECK constraint failed: t1}} do_test check-1.13 { execsql { SELECT * FROM t1; } } {2 4.0} do_test check-1.14 { catchsql { UPDATE t1 SET x=5 WHERE x==2 } } {1 {CHECK constraint failed: t1}} do_test check-1.15 { execsql { SELECT * FROM t1; } } {2 4.0} do_test check-1.16 { catchsql { UPDATE t1 SET x=4, y=11 WHERE x==2 } } {0 {}} do_test check-1.17 { execsql { SELECT * FROM t1; } } {4 11.0} do_test check-2.1 { execsql { PRAGMA writable_schema = 1; CREATE TABLE t2( x INTEGER CONSTRAINT one CHECK( typeof(coalesce(x,0))=="integer" ), y REAL CONSTRAINT two CHECK( typeof(coalesce(y,0.1))=='real' ), z TEXT CONSTRAINT three CHECK( typeof(coalesce(z,''))=='text' ) ); PRAGMA writable_schema = 0; } } {} do_test check-2.2 { execsql { INSERT INTO t2 VALUES(1,2.2,'three'); SELECT * FROM t2; } } {1 2.2 three} db close sqlite3 db test.db do_test check-2.3 { execsql { INSERT INTO t2 VALUES(NULL, NULL, NULL); SELECT * FROM t2; } } {1 2.2 three {} {} {}} do_test check-2.4 { catchsql { INSERT INTO t2 VALUES(1.1, NULL, NULL); } } {1 {CHECK constraint failed: one}} do_test check-2.5 { catchsql { INSERT INTO t2 VALUES(NULL, 5, NULL); } } {1 {CHECK constraint failed: two}} do_test check-2.6 { catchsql { INSERT INTO t2 VALUES(NULL, NULL, 3.14159); } } {1 {CHECK constraint failed: three}} # Undocumented behavior: The CONSTRAINT name clause can follow a constraint. # Such a clause is ignored. But the parser must accept it for backwards # compatibility. # do_test check-2.10 { execsql { CREATE TABLE t2b( x INTEGER CHECK( typeof(coalesce(x,0))=='integer' ) CONSTRAINT one, y TEXT PRIMARY KEY constraint two, z INTEGER, UNIQUE(x,z) constraint three ); } } {} do_test check-2.11 { catchsql { INSERT INTO t2b VALUES('xyzzy','hi',5); } } {1 {CHECK constraint failed: t2b}} do_test check-2.12 { execsql { CREATE TABLE t2c( x INTEGER CONSTRAINT x_one CONSTRAINT x_two CHECK( typeof(coalesce(x,0))=='integer' ) CONSTRAINT x_two CONSTRAINT x_three, y INTEGER, z INTEGER, CONSTRAINT u_one UNIQUE(x,y,z) CONSTRAINT u_two ); } } {} do_test check-2.13 { catchsql { INSERT INTO t2c VALUES('xyzzy',7,8); } } {1 {CHECK constraint failed: x_two}} do_test check-2.cleanup { execsql { DROP TABLE IF EXISTS t2b; DROP TABLE IF EXISTS t2c; } } {} ifcapable subquery { do_test check-3.1 { catchsql { CREATE TABLE t3( x, y, z, CHECK( x<(SELECT min(x) FROM t1) ) ); } } {1 {subqueries prohibited in CHECK constraints}} } do_test check-3.2 { execsql { SELECT name FROM sqlite_master ORDER BY name } } {t1 t2} do_test check-3.3 { catchsql { CREATE TABLE t3( x, y, z, CHECK( q0 )); CREATE TABLE t811(b, CHECK( xyzzy.t811.b BETWEEN 5 AND 10 )); } {} # Make sure check constraints involving the ROWID are not ignored # do_execsql_test 9.1 { CREATE TABLE t1( a INTEGER PRIMARY KEY, b INTEGER NOT NULL CONSTRAINT 'b-check' CHECK( b>a ), c INTEGER NOT NULL CONSTRAINT 'c-check' CHECK( c>rowid*2 ), d INTEGER NOT NULL CONSTRAINT 'd-check' CHECK( d BETWEEN b AND c ) ); INSERT INTO t1(a,b,c,d) VALUES(1,2,4,3),(2,4,6,5),(3,10,30,20); } {} do_catchsql_test 9.2 { UPDATE t1 SET b=0 WHERE a=1; } {1 {CHECK constraint failed: b-check}} do_catchsql_test 9.3 { UPDATE t1 SET c=a*2 WHERE a=1; } {1 {CHECK constraint failed: c-check}} # Integrity check on a VIEW with columns. # db close db2 close forcedelete test.db sqlite3 db test.db do_execsql_test 10.1 { CREATE TABLE t1(x); CREATE VIEW v1(y) AS SELECT x FROM t1; PRAGMA integrity_check; } {ok} #------------------------------------------------------------------------- reset_db do_execsql_test 11.0 { CREATE TABLE t1 (Col0 CHECK(1 COLLATE BINARY BETWEEN 1 AND 1) ) ; } do_execsql_test 11.1 { INSERT INTO t1 VALUES (NULL); } do_execsql_test 11.2 { INSERT INTO t1 VALUES (NULL); } do_execsql_test 11.3 { CREATE TABLE t2(b, a CHECK( CASE 'abc' COLLATE nocase WHEN a THEN 1 ELSE 0 END) ); } do_execsql_test 11.4 { INSERT INTO t2(a) VALUES('abc'); } do_execsql_test 11.5 { INSERT INTO t2(b, a) VALUES(1, 'abc'||''); } do_execsql_test 11.6 { INSERT INTO t2(b, a) VALUES(2, 'abc'); } finish_test finish_test