|
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.DatabaseMetaData ;
27 import java.sql.ResultSet ;
28 import java.sql.PreparedStatement ;
29 import java.sql.Statement ;
30 import java.sql.SQLException ;
31 import java.sql.Types ;
32 import java.util.BitSet ;
33
34 import org.apache.derby.tools.ij;
35 import org.apache.derby.tools.JDBCDisplayUtil;
36
37
42 public class SpillHash
43 {
44 private static PreparedStatement joinStmt;
45 private static PreparedStatement distinctStmt;
46 private static final int LOTS_OF_ROWS = 10000;
47 private static int errorCount = 0;
48
49 public static void main (String args[])
50 {
51 try {
52
53 ij.getPropertyArg(args);
56 Connection conn = ij.startJBMS();
57 Statement stmt = conn.createStatement();
58
59 for( int i = 0; i < prep.length; i++)
60 stmt.executeUpdate( prep[i]);
61 PreparedStatement insA = conn.prepareStatement( "insert into ta(ca1,ca2) values(?,?)");
62 PreparedStatement insB = conn.prepareStatement( "insert into tb(cb1,cb2) values(?,?)");
63 insertDups( insA, insB, initDupVals);
64
65 joinStmt =
66 conn.prepareStatement( "select ta.ca1, ta.ca2, tb.cb2 from ta, tb where ca1 = cb1");
67 distinctStmt =
68 conn.prepareStatement( "select distinct ca1 from ta");
69
70 runStatements( conn, 0, new String [][][] {initDupVals});
71
72 System.out.println( "Growing database.");
73
74 conn.setAutoCommit(false);
76 for( int i = 1; i <= LOTS_OF_ROWS; i++)
77 {
78 insA.setInt(1, i);
79 insA.setString(2, ca2Val(i));
80 insA.executeUpdate();
81 insB.setInt(1, i);
82 insB.setString(2, cb2Val(i));
83 insB.executeUpdate();
84
85 if( (i & 0xff) == 0)
86 conn.commit();
87 }
88 conn.commit();
89 insertDups( insA, insB, spillDupVals);
90 conn.commit();
91
92 conn.setAutoCommit(true);
93 runStatements( conn, LOTS_OF_ROWS, new String [][][] {initDupVals, spillDupVals});
94
95 conn.close();
96 } catch (Exception e) {
97 System.out.println("FAIL -- unexpected exception "+e);
98 JDBCDisplayUtil.ShowException(System.out, e);
99 e.printStackTrace();
100 errorCount++;
101 }
102 if( errorCount == 0)
103 {
104 System.out.println( "PASSED.");
105 System.exit(0);
106 }
107 else
108 {
109 System.out.println( "FAILED: " + errorCount + ((errorCount == 1) ? " error" : " errors"));
110 System.exit(1);
111 }
112 }
114 private static final String [] prep =
115 {
116 "create table ta (ca1 integer, ca2 char(200))",
117 "create table tb (cb1 integer, cb2 char(200))",
118 "insert into ta(ca1,ca2) values(null, 'Anull')",
119 "insert into tb(cb1,cb2) values(null, 'Bnull')"
120 };
121
122 private static final String [][] initDupVals =
123 {
124 { "0a", "0b"},
125 { "1a", "1b"},
126 { "2a"}
127 };
128 private static final String [][] spillDupVals =
129 {
130 {},
131 { "1c"},
132 { "2b"},
133 { "3a", "3b", "3c"}
134 };
135
136 private static int expectedMincc2( int cc1)
137 {
138 return 4*cc1;
139 }
140
141 private static int expectedMaxcc2( int cc1)
142 {
143 return expectedMincc2( cc1) + (cc1 & 0x3);
144 }
145
146 private static void insertDups( PreparedStatement insA, PreparedStatement insB, String [][] dupVals)
147 throws SQLException
148 {
149 for( int i = 0; i < dupVals.length; i++)
150 {
151 insA.setInt(1, -i);
152 insB.setInt(1, -i);
153 String [] vals = dupVals[i];
154 for( int j = 0; j < vals.length; j++)
155 {
156 insA.setString( 2, "A" + vals[j]);
157 insA.executeUpdate();
158 insB.setString( 2, "B" + vals[j]);
159 insB.executeUpdate();
160 }
161 }
162 }
164 private static String ca2Val( int col1Val)
165 {
166 return "A" + col1Val;
167 }
168
169 private static String cb2Val( int col1Val)
170 {
171 return "B" + col1Val;
172 }
173
174 private static void runStatements( Connection conn, int maxColValue, String [][][] dupVals)
175 throws SQLException
176 {
177 runJoin( conn, maxColValue, dupVals);
178 runDistinct( conn, maxColValue, dupVals);
179 runCursor( conn, maxColValue, dupVals);
180 }
181
182 private static void runJoin( Connection conn, int maxColValue, String [][][] dupVals)
183 throws SQLException
184 {
185 System.out.println( "Running join");
186 int expectedRowCount = maxColValue; ResultSet rs = joinStmt.executeQuery();
188 BitSet joinRowFound = new BitSet ( maxColValue);
189 int dupKeyCount = 0;
190 for( int i = 0; i < dupVals.length; i++)
191 {
192 if( dupVals[i].length > dupKeyCount)
193 dupKeyCount = dupVals[i].length;
194 }
195 BitSet [] dupsFound = new BitSet [dupKeyCount];
196 int[] dupCount = new int[ dupKeyCount];
197 for( int i = 0; i < dupKeyCount; i++)
198 {
199 dupCount[i] = 0;
201 for( int j = 0; j < dupVals.length; j++)
202 {
203 if( i < dupVals[j].length)
204 dupCount[i] += dupVals[j][i].length;
205 }
206 dupsFound[i] = new BitSet (dupCount[i]*dupCount[i]);
207 expectedRowCount += dupCount[i]*dupCount[i];
208 }
209
210 int count;
211 for( count = 0; rs.next(); count++)
212 {
213 int col1Val = rs.getInt(1);
214 if( rs.wasNull())
215 {
216 System.out.println( "Null in join column.");
217 errorCount++;
218 continue;
219 }
220 if( col1Val > maxColValue)
221 {
222 System.out.println( "Invalid value in first join column.");
223 errorCount++;
224 continue;
225 }
226 if( col1Val > 0)
227 {
228 if( joinRowFound.get( col1Val - 1))
229 {
230 System.out.println( "Multiple rows for value " + col1Val);
231 errorCount++;
232 }
233 joinRowFound.set( col1Val - 1);
234 String col2Val = trim( rs.getString(2));
235 String col3Val = trim( rs.getString(3));
236 if( !( ca2Val( col1Val).equals( col2Val) && cb2Val( col1Val).equals( col3Val)))
237 {
238 System.out.println( "Incorrect value in column 2 or 3 of join.");
239 errorCount++;
240 }
241 }
242 else {
244 int dupKeyIdx = -col1Val;
245 int col2Idx = findDupVal( rs, 2, 'A', dupKeyIdx, dupVals);
246 int col3Idx = findDupVal( rs, 3, 'B', dupKeyIdx, dupVals);
247 if( col2Idx < 0 || col3Idx < 0)
248 continue;
249
250 int idx = col2Idx + dupCount[dupKeyIdx]*col3Idx;
251 if( dupsFound[dupKeyIdx].get( idx))
252 {
253 System.out.println( "Repeat of row with key value 0");
254 errorCount++;
255 }
256 dupsFound[dupKeyIdx].set( idx);
257 }
258 };
259 if( count != expectedRowCount)
260 {
261 System.out.println( "Incorrect number of rows in join.");
262 errorCount++;
263 }
264 rs.close();
265 }
267 private static int findDupVal( ResultSet rs, int col, char prefix, int keyIdx, String [][][] dupVals)
268 throws SQLException
269 {
270 String colVal = rs.getString(col);
271 if( colVal != null && colVal.length() > 1 || colVal.charAt(0) == prefix)
272 {
273 colVal = trim( colVal.substring( 1));
274 int dupIdx = 0;
275 for( int i = 0; i < dupVals.length; i++)
276 {
277 if( keyIdx < dupVals[i].length)
278 {
279 for( int j = 0; j < dupVals[i][keyIdx].length; j++, dupIdx++)
280 {
281 if( colVal.equals( dupVals[i][keyIdx][j]))
282 return dupIdx;
283 }
284 }
285 }
286 }
287 System.out.println( "Incorrect value in column " + col + " of join with duplicate keys.");
288 errorCount++;
289 return -1;
290 }
292 private static String trim( String str)
293 {
294 if( str == null)
295 return str;
296 return str.trim();
297 }
298
299 private static void runDistinct( Connection conn, int maxColValue, String [][][] dupVals)
300 throws SQLException
301 {
302 System.out.println( "Running distinct");
303 ResultSet rs = distinctStmt.executeQuery();
304 checkAllCa1( rs, false, false, maxColValue, dupVals, "DISTINCT");
305 }
306
307 private static void checkAllCa1( ResultSet rs,
308 boolean expectDups,
309 boolean holdOverCommit,
310 int maxColValue,
311 String [][][] dupVals,
312 String label)
313 throws SQLException
314 {
315 int dupKeyCount = 0;
316 for( int i = 0; i < dupVals.length; i++)
317 {
318 if( dupVals[i].length > dupKeyCount)
319 dupKeyCount = dupVals[i].length;
320 }
321 int[] expectedDupCount = new int[dupKeyCount];
322 int[] dupFoundCount = new int[dupKeyCount];
323 for( int i = 0; i < dupKeyCount; i++)
324 {
325
326 dupFoundCount[i] = 0;
327 if( !expectDups)
328 expectedDupCount[i] = 1;
329 else
330 {
331 expectedDupCount[i] = 0;
332 for( int j = 0; j < dupVals.length; j++)
333 {
334 if( i < dupVals[j].length)
335 expectedDupCount[i] += dupVals[j][i].length;
336 }
337 }
338 }
339 BitSet found = new BitSet ( maxColValue);
340 int count = 0;
341 boolean nullFound = false;
342 try
343 {
344 for( count = 0; rs.next();)
345 {
346 int col1Val = rs.getInt(1);
347 if( rs.wasNull())
348 {
349 if( nullFound)
350 {
351 System.out.println( "Too many nulls returned by " + label);
352 errorCount++;
353 continue;
354 }
355 nullFound = true;
356 continue;
357 }
358 if( col1Val <= -dupKeyCount || col1Val > maxColValue)
359 {
360 System.out.println( "Invalid value returned by " + label);
361 errorCount++;
362 continue;
363 }
364 if( col1Val <= 0)
365 {
366 dupFoundCount[ -col1Val]++;
367 if( !expectDups)
368 {
369 if( dupFoundCount[ -col1Val] > 1)
370 {
371 System.out.println( label + " returned a duplicate.");
372 errorCount++;
373 continue;
374 }
375 }
376 else if( dupFoundCount[ -col1Val] > expectedDupCount[ -col1Val])
377 {
378 System.out.println( label + " returned too many duplicates.");
379 errorCount++;
380 continue;
381 }
382 }
383 else
384 {
385 if( found.get( col1Val))
386 {
387 System.out.println( label + " returned a duplicate.");
388 errorCount++;
389 continue;
390 }
391 found.set( col1Val);
392 count++;
393 }
394 if( holdOverCommit)
395 {
396 rs.getStatement().getConnection().commit();
397 holdOverCommit = false;
398 }
399 }
400 if( count != maxColValue)
401 {
402 System.out.println( "Incorrect number of rows in " + label);
403 errorCount++;
404 }
405 for( int i = 0; i < dupFoundCount.length; i++)
406 {
407 if( dupFoundCount[i] != expectedDupCount[i])
408 {
409 System.out.println( "A duplicate key row is missing in " + label);
410 errorCount++;
411 break;
412 }
413 }
414 }
415 finally
416 {
417 rs.close();
418 }
419 }
421 private static void runCursor( Connection conn, int maxColValue, String [][][] dupVals)
422 throws SQLException
423 {
424 System.out.println( "Running scroll insensitive cursor");
425 DatabaseMetaData dmd = conn.getMetaData();
426 boolean holdOverCommit = dmd.supportsOpenCursorsAcrossCommit();
427 Statement stmt;
428 if( holdOverCommit)
429 stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
430 ResultSet.CONCUR_READ_ONLY,
431 ResultSet.HOLD_CURSORS_OVER_COMMIT);
432 else
433 stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
434 ResultSet.CONCUR_READ_ONLY);
435 ResultSet rs = stmt.executeQuery( "SELECT ca1 FROM ta");
436 checkAllCa1( rs, true, holdOverCommit, maxColValue, dupVals, "scroll insensitive cursor");
437 }
438 }
439 |
Popular Tags |
Java API By Example, From Geeks To Geeks.