001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.updateCursor
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to You under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derbyTesting.functionTests.tests.lang;
023:
024: import java.sql.Connection;
025: import java.sql.DriverManager;
026: import java.sql.Statement;
027: import java.sql.PreparedStatement;
028: import java.sql.ResultSet;
029: import java.sql.SQLException;
030: import java.sql.SQLWarning;
031:
032: import org.apache.derby.tools.ij;
033: import org.apache.derby.tools.JDBCDisplayUtil;
034:
035: /**
036: This tests updateable cursor using index, Beetle entry 3865.
037:
038: Not done in ij since we need to do many "next" and "update" to be
039: able to excercise the code of creating temp conglomerate for virtual
040: memory heap. We need at minimum
041: 200 rows in table, if "maxMemoryPerTable" property is set to 1 (KB).
042: This includes 100 rows to fill the hash table and another 100 rows
043: to fill the in-memory heap.
044:
045: */
046:
047: public class updateCursor {
048:
049: private static Connection conn;
050:
051: public static void main(String[] args) {
052: System.out
053: .println("Test updateable cursor using index starting");
054:
055: try {
056: // use the ij utility to read the property file and
057: // make the initial connection.
058: ij.getPropertyArg(args);
059: conn = ij.startJBMS();
060:
061: conn.setAutoCommit(true);
062:
063: setup(true);
064:
065: conn.setAutoCommit(false);
066:
067: System.out
068: .println("************************************TESTING VIRTUAL MEM HEAP*********");
069: testVirtualMemHeap();
070: System.out
071: .println("************************************TESTING NONCOVERINGINDEX*********");
072: testNonCoveringIndex();
073: System.out
074: .println("************************************TESTING DESC INDEX*********");
075: testDescendingIndex();
076:
077: System.out
078: .println("************************************TESTING UPDATE DELETE WARNING*********");
079: testUpdateDeleteWarning();
080:
081: teardown();
082:
083: conn.close();
084:
085: } catch (Throwable e) {
086: System.out.println("FAIL: exception thrown:");
087: JDBCDisplayUtil.ShowException(System.out, e);
088: }
089:
090: System.out
091: .println("Test updateable cursor using index finished");
092: }
093:
094: static void setup(boolean first) throws SQLException {
095: Statement stmt = conn.createStatement();
096:
097: if (first) {
098: verifyCount(
099: stmt
100: .executeUpdate("create table t1 (c1 int, c2 char(50), c3 int, c4 char(50), c5 int, c6 varchar(1000))"),
101: 0);
102:
103: verifyCount(
104: stmt
105: .executeUpdate("create index i11 on t1 (c3, c1, c5)"),
106: 0);
107:
108: verifyCount(stmt.executeUpdate("create table t2 (c1 int)"),
109: 0);
110:
111: verifyCount(
112: stmt
113: .executeUpdate("create table t3(c1 char(20) not null primary key)"),
114: 0);
115:
116: verifyCount(
117: stmt
118: .executeUpdate("create table t4(c1 char(20) references t3(c1) on delete cascade)"),
119: 0);
120: } else {
121: verifyBoolean(stmt.execute("delete from t1"), false);
122: }
123:
124: StringBuffer sb = new StringBuffer(1000);
125: for (int i = 0; i < 1000; i++)
126: sb.append('a');
127: String largeString = new String(sb);
128:
129: for (int i = 246; i > 0; i = i - 5) {
130: verifyCount(stmt.executeUpdate("insert into t1 values ("
131: + (i + 4) + ", '" + i + "', " + i + ", '" + i
132: + "', " + i + ", '" + largeString + "'), ("
133: + (i + 3) + ", '" + i + "', " + (i + 1) + ", '" + i
134: + "', " + i + ", '" + largeString + "'), ("
135: + (i + 2) + ", '" + i + "', " + (i + 2) + ", '" + i
136: + "', " + i + ", '" + largeString + "'), ("
137: + (i + 1) + ", '" + i + "', " + (i + 3) + ", '" + i
138: + "', " + i + ", '" + largeString + "'), (" + i
139: + ", '" + i + "', " + (i + 4) + ", '" + i + "', "
140: + i + ", '" + largeString + "')"), 5);
141: }
142:
143: stmt.executeUpdate("insert into t2 values (1)");
144:
145: stmt.close();
146:
147: System.out.println("PASS: setup complete");
148: }
149:
150: static void teardown() throws SQLException {
151: Statement stmt = conn.createStatement();
152:
153: verifyCount(stmt.executeUpdate("drop table t1"), 0);
154: verifyCount(stmt.executeUpdate("drop table t2"), 0);
155: verifyCount(stmt.executeUpdate("drop table t4"), 0);
156: verifyCount(stmt.executeUpdate("drop table t3"), 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 "
167: + count + " rows");
168: throw new SQLException("Wrong number of rows returned");
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)
175: throws SQLException {
176: if (got != expect) {
177: System.out.println("FAIL: Expected " + expect + " got "
178: + got);
179: throw new SQLException("Wrong boolean returned");
180: } else
181: System.out.println("PASS: expected and got " + got);
182: }
183:
184: static void nextRow(ResultSet r, int which) throws SQLException {
185: verifyBoolean(r.next(), true);
186: if (which == 1)
187: System.out.println("Row: " + r.getInt(1) + ","
188: + r.getInt(2));
189: else if (which == 2)
190: System.out.println("Row: " + r.getInt(1) + ","
191: + r.getString(2));
192: }
193:
194: static boolean ifRow(ResultSet r, int which) throws SQLException {
195: boolean b = r.next();
196:
197: if (b) {
198: if (which == 1)
199: System.out.println("Row: " + r.getInt(1) + ","
200: + r.getInt(2));
201: else if (which == 2)
202: System.out.println("Row: " + r.getInt(1) + ","
203: + r.getString(2));
204: }
205: return b;
206: }
207:
208: static void testVirtualMemHeap() throws SQLException {
209: PreparedStatement select;
210: Statement update;
211: ResultSet cursor;
212:
213: update = conn.createStatement();
214: select = conn
215: .prepareStatement("select c1, c3 from t1 where c3 > 1 and c1 > 0 for update");
216: cursor = select.executeQuery(); // cursor is now open
217: String cursorName = cursor.getCursorName();
218:
219: System.out
220: .println("Notice the order in the rows we get: from 2 to 102 asc order on second column (c3)");
221: System.out
222: .println("then from 202 down to 103 on that column; then from 203 up to 250. The reason is");
223: System.out
224: .println("we are using asc index on c3, all the rows updated are in the future direction of the");
225: System.out
226: .println("index scan, so they all get filled into a hash table. The MAX_MEMORY_PER_TABLE");
227: System.out
228: .println("property determines max cap of hash table 100. So from row 103 it goes into virtual");
229: System.out
230: .println("memory heap, whose in memory part is also 100 entries. So row 103 to 202 goes into");
231: System.out
232: .println("the in-memory part and gets dumped out in reverse order. Finally Row 203 to 250");
233: System.out
234: .println("goes into file system. Here we mean row ids.");
235:
236: for (int i = 0; i < 249; i++) {
237: nextRow(cursor, 1);
238: update
239: .execute("update t1 set c3 = c3 + 250 where current of "
240: + cursorName);
241: }
242: if (!ifRow(cursor, 1))
243: System.out
244: .println("UPDATE WITH VIRTUAL MEM HEAP: got 249 rows");
245: else
246: System.out
247: .println("UPDATE WITH VIRTUAL MEM HEAP FAILED! STILL GOT ROWS");
248: cursor.close();
249: select.close();
250:
251: System.out.println("************ See what we have in table:");
252: select = conn.prepareStatement("select c1, c3 from t1");
253: cursor = select.executeQuery(); // cursor is now open
254: for (int i = 0; i < 250; i++)
255: nextRow(cursor, 1);
256: if (!ifRow(cursor, 1))
257: System.out
258: .println("AFTER UPDATE WITH VIRTUAL MEM HEAP: got 250 rows");
259: else
260: System.out
261: .println("UPDATE WITH VIRTUAL MEM HEAP RESULT:FAILED!!! GOT MORE ROWS");
262: conn.rollback();
263: }
264:
265: static void testNonCoveringIndex() throws SQLException {
266: PreparedStatement select;
267: Statement update;
268: ResultSet cursor;
269:
270: update = conn.createStatement();
271: select = conn
272: .prepareStatement("select c3, c2 from t1 where c3 > 125 and c1 > 0 for update");
273: cursor = select.executeQuery(); // cursor is now open
274: String cursorName = cursor.getCursorName();
275:
276: for (int i = 0; i < 125; i++) {
277: nextRow(cursor, 2);
278: update
279: .execute("update t1 set c3 = c3 + 25 where current of "
280: + cursorName);
281: }
282: if (!ifRow(cursor, 2))
283: System.out
284: .println("UPDATE USING NONCOVERING INDEX: got 125 rows");
285: else
286: System.out
287: .println("UPDATE USING NONCOVERING INDEX FAILED! STILL GOT ROWS");
288: cursor.close();
289: select.close();
290:
291: System.out.println("************ See what we have in table:");
292: select = conn.prepareStatement("select c1, c3 from t1");
293: cursor = select.executeQuery(); // cursor is now open
294: for (int i = 0; i < 250; i++)
295: nextRow(cursor, 2);
296: if (!ifRow(cursor, 2))
297: System.out
298: .println("AFTER UPDATE USING NONCOVERING INDEX: got 250 rows");
299: else
300: System.out
301: .println("UPDATE USING NONCOVERING INDEX: FAILED!!! GOT MORE ROWS");
302: conn.rollback();
303: }
304:
305: static void testDescendingIndex() throws SQLException {
306: PreparedStatement select;
307: Statement update;
308: ResultSet cursor;
309:
310: update = conn.createStatement();
311: conn.setAutoCommit(true);
312: verifyCount(update.executeUpdate("drop index i11"), 0);
313: verifyCount(
314: update
315: .executeUpdate("create index i11 on t1 (c3 desc, c1, c5 desc)"),
316: 0);
317: conn.setAutoCommit(false);
318:
319: update = conn.createStatement();
320: select = conn
321: .prepareStatement("select c3, c1 from t1 where c3 > 125 and c1 > 0 for update");
322: cursor = select.executeQuery(); // cursor is now open
323: for (int i = 0; i < 125; i++) {
324: nextRow(cursor, 2);
325: /* mixed direction, half of them (whose change direction is the same as the index
326: * scan) have to go into the hash table.
327: */
328: if (i % 2 == 0)
329: update
330: .execute("update t1 set c3 = c3 + 1 where current of "
331: + cursor.getCursorName());
332: else
333: update
334: .execute("update t1 set c3 = c3 - 1 where current of "
335: + cursor.getCursorName());
336: }
337: if (!ifRow(cursor, 2))
338: System.out
339: .println("TEST UPDATE USING DESC INDEX: got 125 rows");
340: else
341: System.out
342: .println("TEST UPDATE USING DESC INDEX FAILED! GOT MORE ROWS");
343: cursor.close();
344: select.close();
345:
346: System.out.println("************ See what we have in table:");
347:
348: select = conn.prepareStatement("select c3, c2 from t1");
349: cursor = select.executeQuery(); // cursor is now open
350: for (int i = 0; i < 250; i++)
351: nextRow(cursor, 2);
352: if (!ifRow(cursor, 2))
353: System.out
354: .println("TEST UPDATE USING DESC INDEX: got 250 rows");
355: else
356: System.out
357: .println("TEST UPDATE USING DESC INDEX FAILED! GOT MORE ROWS");
358: conn.rollback();
359: }
360:
361: static void testUpdateDeleteWarning() throws SQLException {
362: Statement stmt = conn.createStatement();
363: stmt.executeUpdate("update t2 set c1 = 2 where c1 = 1");
364: SQLWarning sw = stmt.getWarnings();
365: if (sw != null)
366: System.out
367: .println("TEST FAILED! The update should not return a warning.");
368: stmt.executeUpdate("update t2 set c1 = 2 where c1 = 1");
369: sw = stmt.getWarnings();
370: String state, msg;
371: if (sw == null)
372: System.out
373: .println("TEST FAILED! The update should return a warning.");
374: else {
375: state = sw.getSQLState();
376: if (!state.equals("02000"))
377: System.out.println("TEST FAILED! Wrong sql state.");
378: msg = sw.getMessage();
379: if (!msg
380: .startsWith("No row was found for FETCH, UPDATE or DELETE"))
381: System.out.println("TEST FAILED! Wrong message: "
382: + msg);
383: }
384:
385: stmt.executeUpdate("delete from t2 where c1 = 2");
386: sw = stmt.getWarnings();
387: if (sw != null)
388: System.out
389: .println("TEST FAILED! The delete should not return a warning.");
390: stmt.executeUpdate("delete from t2 where c1 = 2");
391: sw = stmt.getWarnings();
392: if (sw == null)
393: System.out
394: .println("TEST FAILED! The delete should return a warning.");
395: else {
396: state = sw.getSQLState();
397: if (!state.equals("02000"))
398: System.out.println("TEST FAILED! Wrong sql state.");
399: msg = sw.getMessage();
400: if (!msg
401: .startsWith("No row was found for FETCH, UPDATE or DELETE"))
402: System.out.println("TEST FAILED! Wrong message: "
403: + msg);
404: }
405:
406: stmt.executeUpdate("delete from t3");
407: sw = stmt.getWarnings();
408: if (sw == null)
409: System.out
410: .println("TEST FAILED! The delete cascade should return a warning.");
411: else {
412: state = sw.getSQLState();
413: if (!state.equals("02000"))
414: System.out.println("TEST FAILED! Wrong sql state.");
415: msg = sw.getMessage();
416: if (!msg
417: .startsWith("No row was found for FETCH, UPDATE or DELETE"))
418: System.out.println("TEST FAILED! Wrong message: "
419: + msg);
420: }
421: }
422: }
|