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
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
|
/*
Derby - Class org.apache.derbyTesting.functionTests.tests.lang.DropTableTest
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/
package org.apache.derbyTesting.functionTests.tests.lang;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import junit.framework.Test;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.TestConfiguration;
public final class DropTableTest extends BaseJDBCTestCase {
/**
* Public constructor required for running test as standalone JUnit.
*/
public DropTableTest(String name)
{
super(name);
}
public static Test suite()
{
return TestConfiguration.defaultSuite(DropTableTest.class);
}
public void testDropTableWithConstraints() throws Exception
{
//test table with different constraints
Statement st = createStatement();
setAutoCommit(false);
// test simple table - all should work
st.executeUpdate( "create table t1 ( a int)");
st.executeUpdate( " drop table t1");
// t1 shouldn't be found
assertStatementError("42X05", st, "select * from t1");
// test table with unique constraint - all should work
st.executeUpdate( "create table t1 (a int not null unique)");
st.executeUpdate( " drop table t1");
// t1 shouldn't be found
assertStatementError("42X05", st, "select * from t1");
// test table with primary constraint - all should work
st.executeUpdate( "create table t1 ( a int not null primary key)");
st.executeUpdate( " drop table t1");
// t1 shouldn't be found
assertStatementError("42X05", st, "select * from t1");
// test table with check constraint - all should work
st.executeUpdate( "create table t1 ( a int check(a > 0))");
st.executeUpdate( " drop table t1");
// t1 shouldn't be found
assertStatementError("42X05", st, "select * from t1");
// test table with index - all should work
st.executeUpdate( "create table t1 ( a int)");
st.executeUpdate( " create index t1index on t1(a)");
st.executeUpdate( " drop table t1");
// t1 shouldn't be found
assertStatementError("42X05", st, "select * from t1");
// test table with foreign key references;
st.executeUpdate( "create table t1(a int not null primary key)");
st.executeUpdate(
" create table t2(a int constraint reft1a references t1(a))");
// this should fail with a dependent constraint error
assertStatementError("X0Y25", st, "drop table t1");
// dropping dependent constraint
st.executeUpdate( "alter table t2 drop constraint reft1a");
// this should work since dependent constraint was dropped
st.executeUpdate( "drop table t1");
// t1 shouldn't be found
assertStatementError("42X05", st, "select * from t1");
// the following should work since no referential
// constraint is left
st.executeUpdate( "insert into t2 values(1)");
st.executeUpdate( " drop table t2");
}
public void testDropTableWithView() throws SQLException{
// test table with view
Statement st = createStatement();
setAutoCommit(false);
st.executeUpdate( "create table t1(a int, b int)");
st.executeUpdate( " create table t2(c int, d int)");
st.executeUpdate( " create view vt1a as select a from t1");
st.executeUpdate( " create view vt1b as select b from t1");
st.executeUpdate( " create view vt1t2 as select * from t1, t2");
st.executeUpdate( " create view vvt1a as select * from vt1a");
st.executeUpdate( " create view vvvt1a as select * from vvt1a");
// this should fail with view being a dependent object
assertStatementError("X0Y23", st, "drop table t1");
// dropping dependent views
st.executeUpdate( "drop view vvvt1a");
st.executeUpdate( " drop view vvt1a");
st.executeUpdate( " drop view vt1t2");
st.executeUpdate( " drop view vt1b");
st.executeUpdate( " drop view vt1a");
// this should work after dependent views were dropped
st.executeUpdate( "drop table t1");
// this shouldn't find the view
assertStatementError("42X05", st, "select * from vt1a");
assertStatementError("42X05", st, " select * from vt1b");
assertStatementError("42X05", st, " select * from vt1t2");
assertStatementError("42X05", st, " select * from vvt1a");
assertStatementError("42X05", st, " select * from vvvt1a");
st.executeUpdate( " drop table t2");
}
public void testDropTableWithPreparedStatement() throws SQLException{
// test table with prepared statement
Statement st = createStatement();
setAutoCommit(false);
st.executeUpdate( "create table t1(a int)");
PreparedStatement pSt = prepareStatement( "select * from t1");
// this should work, statement will be invalidated and
// will fail when recompiled
st.executeUpdate( "drop table t1");
assertStatementError("42X05", pSt);
}
public void testDropTableWithTriggers() throws SQLException{
// test table with triggers
Statement st = createStatement();
setAutoCommit(false);
st.executeUpdate( "create table t1(a int)");
st.executeUpdate( " create table t2(a int)");
st.executeUpdate(
" create trigger t1trig after insert on t1 for each "
+ "row insert into t2 values(1)");
// this should work - trigger should be deleted
st.executeUpdate( "drop table t1");
// t1 shouldn't be found
assertStatementError("42X05", st, "select * from t1");
st.executeUpdate( " drop table t2");
// test table within the body of a trigger on another table
st.executeUpdate( "create table t1(a int)");
st.executeUpdate( " create table t2(a int)");
st.executeUpdate(
" create trigger t2trig after insert on t2 for each "
+ "row insert into t1 values(1)");
// this should fail because t2trig depends on t1 (used to work
// before DERBY-2041)
assertStatementError("X0Y25", st, "drop table t1");
// trigger should still work
st.executeUpdate("insert into t2 values(1)");
JDBC.assertSingleValueResultSet(
st.executeQuery("select * from t1"), "1");
JDBC.assertSingleValueResultSet(
st.executeQuery("select * from t2"), "1");
st.executeUpdate( " drop table t2");
st.executeUpdate( " drop table t1");
}
public void testDropTableDropView() throws SQLException{
// test drop view
Statement st = createStatement();
setAutoCommit(false);
st.executeUpdate( "create table t1(a int)");
st.executeUpdate( " create view vt1 as select * from t1");
st.executeUpdate( " create view vvt1 as select * from vt1");
// these should fail
assertStatementError("X0Y23", st, "drop view vt1");
assertStatementError("42X01", st, " drop view vt1 restrict");
assertStatementError("42X01", st, " drop view vt1 cascade");
st.executeUpdate( "drop view vvt1"); // Clean up.
st.executeUpdate( "drop view vt1"); // Clean up.
st.executeUpdate( "drop table t1"); // Clean up.
}
public void testDropTableIndexesDropped() throws SQLException{
// make sure that indexes are dropped for drop table
Statement st = createStatement();
setAutoCommit(false);
st.executeUpdate( "create table t2(a int not null primary key)");
st.executeUpdate(
"create table reft2(a int constraint ref1 references t2)");
// count should be 2
JDBC.assertSingleValueResultSet( st.executeQuery(
"select count(*) from (sys.sysconglomerates c), (sys.systables t) "
+ "where t.tableid = c.tableid and "
+ "t.tablename = 'REFT2'"), "2");
// drop dependent referential constraint
st.executeUpdate( "alter table reft2 drop constraint ref1");
// should work since dependent constraint was previously
// dropped
st.executeUpdate( "drop table t2");
// count should be 1
JDBC.assertSingleValueResultSet( st.executeQuery(
"select count(*) "
+ "from (sys.sysconglomerates c), (sys.systables t) "
+ "where t.tableid = c.tableid and "
+ "t.tablename = 'REFT2'"), "1");
rollback();
// unsuccessful drop table should not affect open cursor
// beetle 4393
st.executeUpdate(
" create table T1 (i int, c varchar(255), d varchar(255))");
st.executeUpdate( " insert into T1(i) values(1)");
st.executeUpdate( " insert into T1(i) values(2)");
Statement st1 = createStatement();
st1.setCursorName("X1");
ResultSet rs1 = st1.executeQuery( "select i from t1 for update of c");
PreparedStatement pSt =
prepareStatement("update t1 set c = CHAR(i) where current of X1");
assertStatementError("X0X95",st,"drop table T1");
rs1.next();
pSt.executeUpdate();
ResultSet rs = st.executeQuery("select * from T1");
String[] expColNames = new String[]{"I","C","D"};
JDBC.assertColumnNames(rs, expColNames);
String[][] expRS = new String[][]{
{"1","1",null},
{"2",null,null}
};
JDBC.assertFullResultSet(rs, expRS);
st1.close();
st.executeUpdate("drop table T1"); // Clean up
//pretend all of the above didn't happen
setAutoCommit(true);
}
}
|
