|
1
21
22 package org.apache.derbyTesting.functionTests.tests.lang;
23
24 import java.sql.Connection  ;
25 import java.sql.DriverManager ;
26 import java.sql.Statement ;
27 import java.sql.PreparedStatement ;
28 import java.sql.ResultSet ;
29 import java.sql.SQLException ;
30 import java.sql.SQLWarning ;
31
32 import org.apache.derby.tools.ij;
33 import org.apache.derby.tools.JDBCDisplayUtil;
34
35
46
47 public class updateCursor {
48
49 private static Connection conn;
50
51 public static void main(String [] args) {
52 System.out.println("Test updateable cursor using index starting");
53
54 try {
55 ij.getPropertyArg(args);
58 conn = ij.startJBMS();
59
60 conn.setAutoCommit(true);
61
62 setup(true);
63
64 conn.setAutoCommit(false);
65
66 System.out.println("************************************TESTING VIRTUAL MEM HEAP*********");
67 testVirtualMemHeap();
68 System.out.println("************************************TESTING NONCOVERINGINDEX*********");
69 testNonCoveringIndex();
70 System.out.println("************************************TESTING DESC INDEX*********");
71 testDescendingIndex();
72
73 System.out.println("************************************TESTING UPDATE DELETE WARNING*********");
74 testUpdateDeleteWarning();
75
76 teardown();
77
78 conn.close();
79
80 } catch (Throwable e) {
81 System.out.println("FAIL: exception thrown:");
82 JDBCDisplayUtil.ShowException(System.out,e);
83 }
84
85 System.out.println("Test updateable cursor using index finished");
86 }
87
88 static void setup(boolean first) throws SQLException {
89 Statement stmt = conn.createStatement();
90
91 if (first) {
92 verifyCount(
93 stmt.executeUpdate("create table t1 (c1 int, c2 char(50), c3 int, c4 char(50), c5 int, c6 varchar(1000))"),
94 0);
95
96 verifyCount(
97 stmt.executeUpdate("create index i11 on t1 (c3, c1, c5)"),
98 0);
99
100 verifyCount(
101 stmt.executeUpdate("create table t2 (c1 int)"),
102 0);
103
104 verifyCount(
105 stmt.executeUpdate("create table t3(c1 char(20) not null primary key)"),
106 0);
107
108 verifyCount(
109 stmt.executeUpdate("create table t4(c1 char(20) references t3(c1) on delete cascade)"),
110 0);
111 } else {
112 verifyBoolean(
113 stmt.execute("delete from t1"),
114 false);
115 }
116
117 StringBuffer sb = new StringBuffer (1000);
118 for (int i = 0; i < 1000; i++)
119 sb.append('a');
120 String largeString = new String (sb);
121
122 for (int i = 246; i > 0; i = i - 5)
123 {
124 verifyCount(
125 stmt.executeUpdate("insert into t1 values ("
126 + (i+4) + ", '" + i + "', " + i + ", '" + i + "', " + i + ", '" + largeString + "'), ("
127 + (i+3) + ", '" + i + "', " + (i+1) + ", '" + i + "', " + i + ", '" + largeString + "'), ("
128 + (i+2) + ", '" + i + "', " + (i+2) + ", '" + i + "', " + i + ", '" + largeString + "'), ("
129 + (i+1) + ", '" + i + "', " + (i+3) + ", '" + i + "', " + i + ", '" + largeString + "'), ("
130 + i + ", '" + i + "', " + (i+4) + ", '" + i + "', " + i + ", '" + largeString + "')"),
131 5);
132 }
133
134 stmt.executeUpdate("insert into t2 values (1)");
135
136 stmt.close();
137
138 System.out.println("PASS: setup complete");
139 }
140
141
142 static void teardown() throws SQLException {
143 Statement stmt = conn.createStatement();
144
145 verifyCount(
146 stmt.executeUpdate("drop table t1"),
147 0);
148 verifyCount(
149 stmt.executeUpdate("drop table t2"),
150 0);
151 verifyCount(
152 stmt.executeUpdate("drop table t4"),
153 0);
154 verifyCount(
155 stmt.executeUpdate("drop table t3"),
156 0);
157
158 conn.commit();
159 stmt.close();
160
161 System.out.println("PASS: teardown complete");
162 }
163
164 static void verifyCount(int count, int expect) throws SQLException {
165 if (count!=expect) {
166 System.out.println("FAIL: Expected "+expect+" got "+count+" rows");
167 throw new SQLException ("Wrong number of rows returned");
168 }
169 else
170 System.out.println("PASS: expected and got "+count+
171 (count == 1? " row":" rows"));
172 }
173
174 static void verifyBoolean(boolean got, boolean expect) throws SQLException {
175 if (got!=expect) {
176 System.out.println("FAIL: Expected "+expect+" got "+got);
177 throw new SQLException ("Wrong boolean returned");
178 }
179 else
180 System.out.println("PASS: expected and got "+got);
181 }
182
183 static void nextRow(ResultSet r, int which) throws SQLException {
184 verifyBoolean(r.next(), true);
185 if (which == 1)
186 System.out.println("Row: "+r.getInt(1)+","+r.getInt(2));
187 else if (which == 2)
188 System.out.println("Row: "+r.getInt(1)+","+r.getString(2));
189 }
190
191 static boolean ifRow(ResultSet r, int which) throws SQLException {
192 boolean b = r.next();
193
194 if (b)
195 {
196 if (which == 1)
197 System.out.println("Row: "+r.getInt(1)+","+r.getInt(2));
198 else if (which == 2)
199 System.out.println("Row: "+r.getInt(1)+","+r.getString(2));
200 }
201 return b;
202 }
203
204 static void testVirtualMemHeap() throws SQLException {
205 PreparedStatement select;
206 Statement update;
207 ResultSet cursor;
208
209 update = conn.createStatement();
210 select = conn.prepareStatement("select c1, c3 from t1 where c3 > 1 and c1 > 0 for update");
211 cursor = select.executeQuery(); String cursorName = cursor.getCursorName();
213
214 System.out.println(
215 "Notice the order in the rows we get: from 2 to 102 asc order on second column (c3)");
216 System.out.println(
217 "then from 202 down to 103 on that column; then from 203 up to 250. The reason is");
218 System.out.println(
219 "we are using asc index on c3, all the rows updated are in the future direction of the");
220 System.out.println(
221 "index scan, so they all get filled into a hash table. The MAX_MEMORY_PER_TABLE");
222 System.out.println(
223 "property determines max cap of hash table 100. So from row 103 it goes into virtual");
224 System.out.println(
225 "memory heap, whose in memory part is also 100 entries. So row 103 to 202 goes into");
226 System.out.println(
227 "the in-memory part and gets dumped out in reverse order. Finally Row 203 to 250");
228 System.out.println(
229 "goes into file system. Here we mean row ids.");
230
231 for (int i = 0; i < 249; i++)
232 {
233 nextRow(cursor, 1);
234 update.execute("update t1 set c3 = c3 + 250 where current of " + cursorName);
235 }
236 if (! ifRow(cursor, 1))
237 System.out.println("UPDATE WITH VIRTUAL MEM HEAP: got 249 rows");
238 else
239 System.out.println("UPDATE WITH VIRTUAL MEM HEAP FAILED! STILL GOT ROWS");
240 cursor.close();
241 select.close();
242
243 System.out.println("************ See what we have in table:");
244 select = conn.prepareStatement("select c1, c3 from t1");
245 cursor = select.executeQuery(); for (int i = 0; i < 250; i++)
247 nextRow(cursor, 1);
248 if (! ifRow(cursor, 1))
249 System.out.println("AFTER UPDATE WITH VIRTUAL MEM HEAP: got 250 rows");
250 else
251 System.out.println("UPDATE WITH VIRTUAL MEM HEAP RESULT:FAILED!!! GOT MORE ROWS");
252 conn.rollback();
253 }
254
255 static void testNonCoveringIndex() throws SQLException {
256 PreparedStatement select;
257 Statement update;
258 ResultSet cursor;
259
260 update = conn.createStatement();
261 select = conn.prepareStatement("select c3, c2 from t1 where c3 > 125 and c1 > 0 for update");
262 cursor = select.executeQuery(); String cursorName = cursor.getCursorName();
264
265 for (int i = 0; i < 125; i++)
266 {
267 nextRow(cursor, 2);
268 update.execute("update t1 set c3 = c3 + 25 where current of " + cursorName);
269 }
270 if (! ifRow(cursor, 2))
271 System.out.println("UPDATE USING NONCOVERING INDEX: got 125 rows");
272 else
273 System.out.println("UPDATE USING NONCOVERING INDEX FAILED! STILL GOT ROWS");
274 cursor.close();
275 select.close();
276
277 System.out.println("************ See what we have in table:");
278 select = conn.prepareStatement("select c1, c3 from t1");
279 cursor = select.executeQuery(); for (int i = 0; i < 250; i++)
281 nextRow(cursor, 2);
282 if (! ifRow(cursor, 2))
283 System.out.println("AFTER UPDATE USING NONCOVERING INDEX: got 250 rows");
284 else
285 System.out.println("UPDATE USING NONCOVERING INDEX: FAILED!!! GOT MORE ROWS");
286 conn.rollback();
287 }
288
289 static void testDescendingIndex() throws SQLException {
290 PreparedStatement select;
291 Statement update;
292 ResultSet cursor;
293
294 update = conn.createStatement();
295 conn.setAutoCommit(true);
296 verifyCount(
297 update.executeUpdate("drop index i11"),
298 0);
299 verifyCount(
300 update.executeUpdate("create index i11 on t1 (c3 desc, c1, c5 desc)"),
301 0);
302 conn.setAutoCommit(false);
303
304 update = conn.createStatement();
305 select = conn.prepareStatement("select c3, c1 from t1 where c3 > 125 and c1 > 0 for update");
306 cursor = select.executeQuery(); for (int i = 0; i < 125; i++)
308 {
309 nextRow(cursor, 2);
310
313 if (i % 2 == 0)
314 update.execute("update t1 set c3 = c3 + 1 where current of " + cursor.getCursorName());
315 else
316 update.execute("update t1 set c3 = c3 - 1 where current of " + cursor.getCursorName());
317 }
318 if (! ifRow(cursor, 2))
319 System.out.println("TEST UPDATE USING DESC INDEX: got 125 rows");
320 else
321 System.out.println("TEST UPDATE USING DESC INDEX FAILED! GOT MORE ROWS");
322 cursor.close();
323 select.close();
324
325 System.out.println("************ See what we have in table:");
326
327 select = conn.prepareStatement("select c3, c2 from t1");
328 cursor = select.executeQuery(); for (int i = 0; i < 250; i++)
330 nextRow(cursor, 2);
331 if (! ifRow(cursor, 2))
332 System.out.println("TEST UPDATE USING DESC INDEX: got 250 rows");
333 else
334 System.out.println("TEST UPDATE USING DESC INDEX FAILED! GOT MORE ROWS");
335 conn.rollback();
336 }
337
338 static void testUpdateDeleteWarning() throws SQLException {
339 Statement stmt = conn.createStatement();
340 stmt.executeUpdate("update t2 set c1 = 2 where c1 = 1");
341 SQLWarning sw = stmt.getWarnings();
342 if (sw != null)
343 System.out.println("TEST FAILED! The update should not return a warning.");
344 stmt.executeUpdate("update t2 set c1 = 2 where c1 = 1");
345 sw = stmt.getWarnings();
346 String state, msg;
347 if (sw == null)
348 System.out.println("TEST FAILED! The update should return a warning.");
349 else
350 {
351 state = sw.getSQLState();
352 if (! state.equals("02000"))
353 System.out.println("TEST FAILED! Wrong sql state.");
354 msg = sw.getMessage();
355 if (! msg.startsWith("No row was found for FETCH, UPDATE or DELETE"))
356 System.out.println("TEST FAILED! Wrong message: " + msg);
357 }
358
359 stmt.executeUpdate("delete from t2 where c1 = 2");
360 sw = stmt.getWarnings();
361 if (sw != null)
362 System.out.println("TEST FAILED! The delete should not return a warning.");
363 stmt.executeUpdate("delete from t2 where c1 = 2");
364 sw = stmt.getWarnings();
365 if (sw == null)
366 System.out.println("TEST FAILED! The delete should return a warning.");
367 else
368 {
369 state = sw.getSQLState();
370 if (! state.equals("02000"))
371 System.out.println("TEST FAILED! Wrong sql state.");
372 msg = sw.getMessage();
373 if (! msg.startsWith("No row was found for FETCH, UPDATE or DELETE"))
374 System.out.println("TEST FAILED! Wrong message: " + msg);
375 }
376
377 stmt.executeUpdate("delete from t3");
378 sw = stmt.getWarnings();
379 if (sw == null)
380 System.out.println("TEST FAILED! The delete cascade should return a warning.");
381 else
382 {
383 state = sw.getSQLState();
384 if (! state.equals("02000"))
385 System.out.println("TEST FAILED! Wrong sql state.");
386 msg = sw.getMessage();
387 if (! msg.startsWith("No row was found for FETCH, UPDATE or DELETE"))
388 System.out.println("TEST FAILED! Wrong message: " + msg);
389 }
390 }
391 }
392 |
Popular Tags |
Java API By Example, From Geeks To Geeks.