1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
|
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML168
-- SQL Test Suite, V6.0, Interactive SQL, dml168.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION FLATER
set schema FLATER;
0 rows inserted/updated/deleted
ij> --O SELECT USER FROM HU.ECCO;
VALUES USER;
1
--------------------------------------------------------------------------------------------------------------------------------
FLATER
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
--O ROLLBACK WORK;
-- date_time print
-- TEST:0876 SQL_IDENTIFIER and CHARACTER_DATA domains!
--O CREATE TABLE T0876 (
--O C1 INFORMATION_SCHEMA.SQL_IDENTIFIER,
--O C2 INFORMATION_SCHEMA.CHARACTER_DATA);
-- PASS:0876 If table created successfully?
--O COMMIT WORK;
--O INSERT INTO T0876 VALUES ('T0876',
--O 'This table tests a couple of domains.');
-- PASS:0876 If 1 row inserted successfully?
--O SELECT COUNT(*)
--O FROM T0876
--O WHERE C1 = 'T0876';
-- PASS:0876 If COUNT = 1?
--O COMMIT WORK;
--O DROP TABLE T0876 CASCADE;
-- PASS:0876 If table dropped successfully?
--O COMMIT WORK;
-- END TEST >>> 0876 <<< END TEST
-- *********************************************
-- TEST:0878 Keyword COLUMN in ALTER TABLE is optional!
CREATE TABLE T0878 (C1 INT);
0 rows inserted/updated/deleted
ij> -- PASS:0878 If table created successfully?
COMMIT WORK;
ij> ALTER TABLE T0878 ADD C2 CHAR (4);
0 rows inserted/updated/deleted
ij> -- PASS:0878 If table altered successfully?
COMMIT WORK;
ij> --O ALTER TABLE T0878
--O ALTER C2 SET DEFAULT 'ABCD';
-- PASS:0878 If table altered successfully?
--O COMMIT WORK;
--O ALTER TABLE T0878
--O DROP C1 CASCADE;
-- PASS:0878 If table altered successfully?
--O COMMIT WORK;
--O INSERT INTO T0878 VALUES (DEFAULT);
-- PASS:0878 If 1 row inserted successfully?
--O SELECT * FROM T0878;
-- PASS:0878 If answer = 'ABCD'?
--O COMMIT WORK;
--O DROP TABLE T0878 CASCADE;
DROP TABLE T0878 ;
0 rows inserted/updated/deleted
ij> -- PASS:0878 If table dropped successfully?
COMMIT WORK;
ij> -- END TEST >>> 0878 <<< END TEST
-- *********************************************
-- TEST:0879 <drop table constraint definition>!
CREATE TABLE T0879 (
C1 INT,
C2 INT NOT NULL,
CONSTRAINT DELME CHECK (C1 > 0),
CONSTRAINT REFME UNIQUE (C2));
0 rows inserted/updated/deleted
ij> -- PASS:0879 If table created successfully?
COMMIT WORK;
ij> --O CREATE TABLE U0879 (
--O C1 INT REFERENCES T0879 (C2));
-- PASS:0879 If table created successfully?
--O COMMIT WORK;
ALTER TABLE T0879
--O DROP CONSTRAINT DELME RESTRICT;
DROP CONSTRAINT DELME ;
0 rows inserted/updated/deleted
ij> -- PASS:0879 If table altered successfully?
COMMIT WORK;
ij> INSERT INTO T0879 VALUES (0, 0);
1 row inserted/updated/deleted
ij> -- PASS:0879 If 1 row inserted successfully?
INSERT INTO T0879 VALUES (-1, -1);
1 row inserted/updated/deleted
ij> -- PASS:0879 If 1 row inserted successfully?
SELECT COUNT(*) FROM T0879;
1
-----------
2
ij> -- PASS:0879 If COUNT = 2?
--O INSERT INTO U0879 VALUES (20);
-- PASS:0879 If ERROR - integrity constraint violation?
INSERT INTO T0879 VALUES (2, 0);
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'REFME' defined on 'T0879'.
ij> -- PASS:0879 If ERROR - integrity constraint violation?
COMMIT WORK;
ij> --O ALTER TABLE T0879
--O DROP CONSTRAINT REFME RESTRICT;
-- PASS:0879 If ERROR - syntax error or access rule violation?
--O COMMIT WORK;
ALTER TABLE T0879
--O DROP CONSTRAINT REFME CASCADE;
DROP CONSTRAINT REFME ;
0 rows inserted/updated/deleted
ij> -- PASS:0879 If table altered successfully?
COMMIT WORK;
ij> --O INSERT INTO U0879 VALUES (20);
-- PASS:0879 If 1 row inserted successfully?
INSERT INTO T0879 VALUES (0, 0);
1 row inserted/updated/deleted
ij> -- PASS:0879 If 1 row inserted successfully?
COMMIT WORK;
ij> --O DROP TABLE T0879 CASCADE;
DROP TABLE T0879 ;
0 rows inserted/updated/deleted
ij> -- PASS:0879 If table dropped successfully?
COMMIT WORK;
ij> --O DROP TABLE U0879 CASCADE;
-- PASS:0879 If table dropped successfully?
--O COMMIT WORK;
-- END TEST >>> 0879 <<< END TEST
-- *********************************************
-- *************************************************////END-OF-MODULE
;
ij>
|
