From 174bc0c1e7003c5ae852547c184e349df0003053 Mon Sep 17 00:00:00 2001 From: "Vadim B. Mikheev" Date: Thu, 28 Aug 1997 04:49:34 +0000 Subject: [PATCH] Tests for CHECK/DEFAULT --- src/test/regress/data/constrf.data | 1 + src/test/regress/data/constro.data | 3 + src/test/regress/expected/Makefile | 4 +- src/test/regress/input/Makefile | 5 +- src/test/regress/input/constraints.source | 72 +++++++++++ src/test/regress/output/Makefile | 5 +- src/test/regress/output/constraints.source | 139 +++++++++++++++++++++ src/test/regress/sql/Makefile | 4 +- src/test/regress/sql/tests | 1 + 9 files changed, 226 insertions(+), 8 deletions(-) create mode 100644 src/test/regress/data/constrf.data create mode 100644 src/test/regress/data/constro.data create mode 100644 src/test/regress/input/constraints.source create mode 100644 src/test/regress/output/constraints.source diff --git a/src/test/regress/data/constrf.data b/src/test/regress/data/constrf.data new file mode 100644 index 0000000000..fdf3cea38f --- /dev/null +++ b/src/test/regress/data/constrf.data @@ -0,0 +1 @@ +\N \N \N diff --git a/src/test/regress/data/constro.data b/src/test/regress/data/constro.data new file mode 100644 index 0000000000..f93f457803 --- /dev/null +++ b/src/test/regress/data/constro.data @@ -0,0 +1,3 @@ +\N \N \N +\N \N \N +\N \N \N diff --git a/src/test/regress/expected/Makefile b/src/test/regress/expected/Makefile index 1ec0111bf8..c398e18947 100644 --- a/src/test/regress/expected/Makefile +++ b/src/test/regress/expected/Makefile @@ -7,11 +7,11 @@ # # # IDENTIFICATION -# $Header: /cvsroot/pgsql/src/test/regress/expected/Attic/Makefile,v 1.1 1997/04/26 05:44:17 scrappy Exp $ +# $Header: /cvsroot/pgsql/src/test/regress/expected/Attic/Makefile,v 1.2 1997/08/28 04:49:17 vadim Exp $ # #------------------------------------------------------------------------- -CLFILES= create_function_1.out create_function_2.out copy.out +CLFILES= create_function_1.out create_function_2.out copy.out constraints.out clean: rm -f $(CLFILES) diff --git a/src/test/regress/input/Makefile b/src/test/regress/input/Makefile index 6b9e3dc41e..2d00f3ac8a 100644 --- a/src/test/regress/input/Makefile +++ b/src/test/regress/input/Makefile @@ -7,7 +7,7 @@ # # # IDENTIFICATION -# $Header: /cvsroot/pgsql/src/test/regress/input/Attic/Makefile,v 1.5 1997/05/05 06:53:31 vadim Exp $ +# $Header: /cvsroot/pgsql/src/test/regress/input/Attic/Makefile,v 1.6 1997/08/28 04:49:18 vadim Exp $ # #------------------------------------------------------------------------- @@ -21,7 +21,8 @@ include ../../../Makefile.global INFILES= copy.sql \ create_function_1.sql \ create_function_2.sql \ - misc.sql + misc.sql \ + constraints.sql all: $(INFILES) diff --git a/src/test/regress/input/constraints.source b/src/test/regress/input/constraints.source new file mode 100644 index 0000000000..a0043e3651 --- /dev/null +++ b/src/test/regress/input/constraints.source @@ -0,0 +1,72 @@ +-- +-- Check constraints +-- + +-- Check constraints on INSERT +drop sequence seq; +drop table test; +create sequence seq; +create table test (x int default nextval ( 'seq') , +y text default '-NULL-', z int default -1 * currval('seq') ) +constraint test1 check (x > 3 and y <> 'check failed' and x < 8 ), +check x + z = 0; +insert into test values (null, null, null); +insert into test values (null, null, -2); +select * from test; +select nextval('seq'); +insert into test values (null, null, null); +insert into test values (1, null, -2); +insert into test values (7, null, -7); +insert into test values (5, 'check failed', -5); +insert into test values (7, '!check failed', -7); +insert into test values (null, null, null); +select * from test; +insert into test values (null, 'check failed', 5); +insert into test values (5, 'check failed', null); +insert into test values (5, '!check failed', null); +insert into test values (null, null, null); +select * from test; +insert into test values (null, null, null); +select currval('seq'); + +-- Check constraints on INSERT INTO + +drop table test; +drop sequence seq; +create sequence seq start 4; +create table dummy (xd int, yd text, zd int); + +create table test (x int default nextval ( 'seq') , +y text default '-NULL-', z int default -1 * currval('seq') ) +constraint test1 check (x > 3 and y <> 'check failed' and x < 7 ), check +x + z = 0; + +select nextval('seq'); +insert into dummy values (null, null, null); +insert into dummy values (5, '!check failed', null); +insert into dummy values (null, 'try again', null); +insert into test select * from dummy; +select * from test; +insert into test select * from dummy where yd = 'try again'; + +-- Check constraints on UPDATE +update test set x = null where x = 6; +select currval('seq'); + +-- Check constraints on COPY FROM +drop table test; +drop sequence seq; +create sequence seq start 4; +create table test (x int default nextval ( 'seq') , +y text default '-NULL-', z int default -1 * currval('seq') ) +constraint test1 check (x > 3 and y <> 'check failed' and x < 7 ), check +x + z = 0; +copy test from '_OBJWD_/data/constro.data'; +select * from test; +copy test from '_OBJWD_/data/constrf.data'; +select * from test; +select nextval('seq') - 1 as currval; + +-- Clean up +drop sequence seq; +drop table test; diff --git a/src/test/regress/output/Makefile b/src/test/regress/output/Makefile index a792733bf3..c381fa4b25 100644 --- a/src/test/regress/output/Makefile +++ b/src/test/regress/output/Makefile @@ -7,7 +7,7 @@ # # # IDENTIFICATION -# $Header: /cvsroot/pgsql/src/test/regress/output/Attic/Makefile,v 1.7 1997/05/05 06:52:58 vadim Exp $ +# $Header: /cvsroot/pgsql/src/test/regress/output/Attic/Makefile,v 1.8 1997/08/28 04:49:23 vadim Exp $ # #------------------------------------------------------------------------- @@ -21,7 +21,8 @@ include ../../../Makefile.global INFILES= copy.out \ create_function_1.out \ create_function_2.out \ - misc.out + misc.out \ + constraints.out all: $(INFILES) diff --git a/src/test/regress/output/constraints.source b/src/test/regress/output/constraints.source new file mode 100644 index 0000000000..0fa8644e32 --- /dev/null +++ b/src/test/regress/output/constraints.source @@ -0,0 +1,139 @@ +QUERY: drop sequence seq; +WARN:Relation seq Does Not Exist! +QUERY: drop table test; +WARN:Relation test Does Not Exist! +QUERY: create sequence seq; +QUERY: create table test (x int default nextval ( 'seq') , +y text default '-NULL-', z int default -1 * currval('seq') ) +constraint test1 check (x > 3 and y <> 'check failed' and x < 8 ), +check x + z = 0; +QUERY: insert into test values (null, null, null); +WARN:ExecAppend: rejected due to CHECK constraint test1 +QUERY: insert into test values (null, null, -2); +WARN:ExecAppend: rejected due to CHECK constraint test1 +QUERY: select * from test; +x|y|z +-+-+- +(0 rows) + +QUERY: select nextval('seq'); +nextval +------- + 3 +(1 row) + +QUERY: insert into test values (null, null, null); +QUERY: insert into test values (1, null, -2); +WARN:ExecAppend: rejected due to CHECK constraint $2 +QUERY: insert into test values (7, null, -7); +QUERY: insert into test values (5, 'check failed', -5); +WARN:ExecAppend: rejected due to CHECK constraint test1 +QUERY: insert into test values (7, '!check failed', -7); +QUERY: insert into test values (null, null, null); +QUERY: select * from test; +x|y | z +-+-------------+-- +4|-NULL- |-4 +7|-NULL- |-7 +7|!check failed|-7 +5|-NULL- |-5 +(4 rows) + +QUERY: insert into test values (null, 'check failed', 5); +WARN:ExecAppend: rejected due to CHECK constraint $2 +QUERY: insert into test values (5, 'check failed', null); +WARN:ExecAppend: rejected due to CHECK constraint $2 +QUERY: insert into test values (5, '!check failed', null); +WARN:ExecAppend: rejected due to CHECK constraint $2 +QUERY: insert into test values (null, null, null); +QUERY: select * from test; +x|y | z +-+-------------+-- +4|-NULL- |-4 +7|-NULL- |-7 +7|!check failed|-7 +5|-NULL- |-5 +7|-NULL- |-7 +(5 rows) + +QUERY: insert into test values (null, null, null); +WARN:ExecAppend: rejected due to CHECK constraint test1 +QUERY: select currval('seq'); +currval +------- + 8 +(1 row) + +QUERY: drop table test; +QUERY: drop sequence seq; +QUERY: create sequence seq start 4; +QUERY: create table dummy (xd int, yd text, zd int); +QUERY: create table test (x int default nextval ( 'seq') , +y text default '-NULL-', z int default -1 * currval('seq') ) +constraint test1 check (x > 3 and y <> 'check failed' and x < 7 ), check +x + z = 0; +QUERY: select nextval('seq'); +NOTICE:seq.nextval: sequence was re-created + +nextval +------- + 4 +(1 row) + +QUERY: insert into dummy values (null, null, null); +QUERY: insert into dummy values (5, '!check failed', null); +QUERY: insert into dummy values (null, 'try again', null); +QUERY: insert into test select * from dummy; +QUERY: select * from test; +x|y | z +-+-------------+-- +5|-NULL- |-5 +5|!check failed|-5 +6|try again |-6 +(3 rows) + +QUERY: insert into test select * from dummy where yd = 'try again'; +WARN:ExecAppend: rejected due to CHECK constraint test1 +QUERY: update test set x = null where x = 6; +WARN:ExecReplace: rejected due to CHECK constraint $2 +QUERY: select currval('seq'); +currval +------- + 8 +(1 row) + +QUERY: drop table test; +QUERY: drop sequence seq; +QUERY: create sequence seq start 4; +QUERY: create table test (x int default nextval ( 'seq') , +y text default '-NULL-', z int default -1 * currval('seq') ) +constraint test1 check (x > 3 and y <> 'check failed' and x < 7 ), check +x + z = 0; +QUERY: copy test from '_OBJWD_/data/constro.data'; +NOTICE:seq.nextval: sequence was re-created +QUERY: select * from test; +x|y | z +-+------+-- +4|-NULL-|-4 +5|-NULL-|-5 +6|-NULL-|-6 +(3 rows) + +QUERY: copy test from '_OBJWD_/data/constrf.data'; +WARN:CopyFrom: rejected due to CHECK constraint test1 +QUERY: select * from test; +x|y | z +-+------+-- +4|-NULL-|-4 +5|-NULL-|-5 +6|-NULL-|-6 +(3 rows) + +QUERY: select nextval('seq') - 1 as currval; +currval +------- + 7 +(1 row) + +QUERY: drop sequence seq; +QUERY: drop table test; diff --git a/src/test/regress/sql/Makefile b/src/test/regress/sql/Makefile index 4ca90fb637..8f630a2724 100644 --- a/src/test/regress/sql/Makefile +++ b/src/test/regress/sql/Makefile @@ -7,11 +7,11 @@ # # # IDENTIFICATION -# $Header: /cvsroot/pgsql/src/test/regress/sql/Attic/Makefile,v 1.2 1997/04/27 02:58:26 scrappy Exp $ +# $Header: /cvsroot/pgsql/src/test/regress/sql/Attic/Makefile,v 1.3 1997/08/28 04:49:31 vadim Exp $ # #------------------------------------------------------------------------- -CLFILES= create_function_1.sql create_function_2.sql copy.sql misc.sql +CLFILES= create_function_1.sql create_function_2.sql copy.sql misc.sql constraints.sql clean: rm -f $(CLFILES) diff --git a/src/test/regress/sql/tests b/src/test/regress/sql/tests index ee759e29fe..b2c3ddef55 100644 --- a/src/test/regress/sql/tests +++ b/src/test/regress/sql/tests @@ -31,6 +31,7 @@ create_function_1 create_type create_table create_function_2 +constraints copy create_misc create_aggregate -- 2.40.0