# 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.11 2007/07/23 19:39:47 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # 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 {constraint failed}} 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 {constraint failed}} 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 {constraint failed}} 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 {constraint failed}} 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 { CREATE TABLE t2( x INTEGER CHECK( typeof(coalesce(x,0))=="integer" ), y REAL CHECK( typeof(coalesce(y,0.1))=="real" ), z TEXT CHECK( typeof(coalesce(z,''))=="text" ) ); } } {} do_test check-2.2 { execsql { INSERT INTO t2 VALUES(1,2.2,'three'); SELECT * FROM t2; } } {1 2.2 three} 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 {constraint failed}} do_test check-2.5 { catchsql { INSERT INTO t2 VALUES(NULL, 5, NULL); } } {1 {constraint failed}} do_test check-2.6 { catchsql { INSERT INTO t2 VALUES(NULL, NULL, 3.14159); } } {1 {constraint failed}} 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( q