Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > jdbcapi > SURQueryMixTest


1 /*
2  *
3  * Derby - Class SURQueryMixTest
4  *
5  * Licensed to the Apache Software Foundation (ASF) under one or more
6  * contributor license agreements. See the NOTICE file distributed with
7  * this work for additional information regarding copyright ownership.
8  * The ASF licenses this file to You under the Apache License, Version 2.0
9  * (the "License"); you may not use this file except in compliance with
10  * the License. You may obtain a copy of the License at
11  *
12  * http://www.apache.org/licenses/LICENSE-2.0
13  *
14  * Unless required by applicable law or agreed to in writing,
15  * software distributed under the License is distributed on an
16  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
17  * either express or implied. See the License for the specific
18  * language governing permissions and limitations under the License.
19  */
20 package org.apache.derbyTesting.functionTests.tests.jdbcapi;
21 import java.sql.DatabaseMetaData ;
22 import java.sql.ResultSet ;
23 import java.sql.ResultSetMetaData ;
24 import java.sql.SQLException ;
25 import java.sql.PreparedStatement ;
26 import java.sql.Statement ;
27 import java.sql.Types ;
28 import java.util.ArrayList ;
29 import java.util.HashMap ;
30 import java.util.HashSet ;
31 import java.util.Iterator ;
32 import java.util.List ;
33 import java.util.Map ;
34 import java.util.Random ;
35 import java.util.Set ;
36 import junit.extensions.TestSetup;
37 import junit.framework.Test;
38 import junit.framework.TestSuite;
39
40 /**
41  * Tests for Scrollable Updatable ResultSet (SUR). This TestCase tests
42  * scrolling (navigation), updates (using updateXXX() and updateRow() or
43  * positioned updates), deletion of records (using deleteRow() or positioned
44  * deletes) of ResultSets.
45  * @author Andreas Korneliussen
46  */
47 public class SURQueryMixTest extends SURBaseTest
48 {
49     /**
50      * Constructor
51      * @param name model name of data model for this TestCase
52      * @param query to use for producing the resultset
53      * @param cursorName name of cursor
54      * @param positioned flag to determine if the Test should use positioned
55      * updates/deletes instead of updateRow() and deleteRow()
56      */
57     public SURQueryMixTest(final String model, final String query,
58                            final String cursorName, final boolean positioned)
59     {
60         super("SURQueryMixTest{Model=" + model + ",Query=" +query + ",Cursor="
61                 + cursorName + ",Positioned=" + positioned + "}");
62         this.query = query;
63         this.cursorName = cursorName;
64         this.positioned = positioned;
65         this.checkRowUpdated = false;
66         this.checkRowDeleted = false;
67     }
68
69     /**
70      * Test SUR properties of the query
71      */
72     public void runTest()
73         throws SQLException
74     {
75         println(query);
76         DatabaseMetaData dbMeta = getConnection().getMetaData();
77                 
78         if (dbMeta.ownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)) {
79             checkRowDeleted = true;
80         }
81         
82         Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
83                                           ResultSet.CONCUR_UPDATABLE);
84         
85         s.setCursorName(cursorName);
86         ResultSet rs = s.executeQuery(query);
87
88         checkRowUpdated = dbMeta.ownUpdatesAreVisible(rs.getType());
89         checkRowDeleted = dbMeta.ownDeletesAreVisible(rs.getType());
90         
91         // Create map with rows
92 Map rows = createRowMap(rs);
93         
94         // Set of rows which are updated (contains Integer with position in RS)
95 final Set updatedRows = new HashSet ();
96         
97         // Set of rows which are deleted (contains Integer with position in RS)
98 final Set deletedRows = new HashSet ();
99                 
100         // Test navigation
101 testNavigation(rs, rows, updatedRows, deletedRows);
102         
103         // Only test updatability if the ResultSet is updatable:
104 // (Note: this enables the test do run successfully even if
105 // scrollable updatable resultsets are not implemented.
106 // If SUR is not implemented, a well behaved JDBC driver will
107 // downgrade the concurrency mode to READ_ONLY).
108 // SUR may be implemented incrementally, i.e first in embedded mode
109 // then in the network driver.)
110 if (rs.getConcurrency()==ResultSet.CONCUR_UPDATABLE) {
111         
112             // update a random sample of 2 records
113 updateRandomSampleOfNRecords(rs, rows, updatedRows, 2);
114             testNavigation(rs, rows, updatedRows, deletedRows);
115             
116             // update a random sample of 5 records
117 updateRandomSampleOfNRecords(rs, rows, updatedRows, 5);
118             testNavigation(rs, rows, updatedRows, deletedRows);
119             
120             // update a random sample of 10 records
121 updateRandomSampleOfNRecords(rs, rows, updatedRows, 10);
122             testNavigation(rs, rows, updatedRows, deletedRows);
123             
124             // delete a random sample of 2 records
125 deleteRandomSampleOfNRecords(rs, rows, deletedRows, 2);
126             testNavigation(rs, rows, updatedRows, deletedRows);
127             
128             // delete a random sample of 5 records
129 deleteRandomSampleOfNRecords(rs, rows, deletedRows, 5);
130             testNavigation(rs, rows, updatedRows, deletedRows);
131             
132             // delete a random sample of 10 records
133 deleteRandomSampleOfNRecords(rs, rows, deletedRows, 10);
134             testNavigation(rs, rows, updatedRows, deletedRows);
135         } else {
136             assertTrue("ResultSet concurrency downgraded to CONCUR_READ_ONLY",
137                        false);
138         }
139         
140         rs.close();
141         s.close();
142     }
143     
144     /**
145      * Creates a Map of the values in the ResultSet.
146      * The key object in the map, is the postion in the
147      * ResultSet (Integer 1..n), while the value is a
148      * concatenation of the strings for all columns in the row.
149      */
150     private Map createRowMap(final ResultSet rs)
151         throws SQLException
152     {
153         final Map rows = new HashMap ();
154         rs.beforeFirst();
155         assertTrue("Unexpected return from isBeforeFirst()",
156                    rs.isBeforeFirst());
157         
158         int i = 0;
159         int sum = 0;
160         int expectedSum = 0;
161         boolean checkSum = true;
162         while (rs.next()) {
163             expectedSum += i;
164             i++;
165             String row = getRowString(rs);
166             println(row);
167             rows.put(new Integer (i), row);
168             sum += rs.getInt(1);
169             if (rs.getInt(1) < 0) {
170                 checkSum = false;
171             }
172         }
173         if (i<SURDataModelSetup.recordCount) {
174             checkSum = false;
175         }
176         
177         assertTrue("Unexpected return from isAfterLast()", rs.isAfterLast());
178         
179         if (checkSum) {
180             assertEquals("Sum for column 1 is not correct", expectedSum, sum);
181         }
182         
183         return rows;
184     }
185
186     /**
187      * Create a random sample of rows
188      * @param rows Map to create sample from
189      * @param k number of rows in the sample
190      * @return a list containing k elements of rows
191      **/
192     private List createRandomSample(final Map rows, int k) {
193         Random r = new Random ();
194         ArrayList sampledKeys = new ArrayList ();
195         int n = 0;
196         for (Iterator i = rows.keySet().iterator(); i.hasNext();) {
197             Object key = i.next();
198             n++;
199             if (n<=k) {
200                 sampledKeys.add(key);
201             } else {
202                 // sampledKeys now has a size of k
203 double d = r.nextDouble();
204                 // p = probability of going into the sample
205 double p = (double) k / (double) n;
206                 if (d<p) {
207                     // Replace a random value from the sample with the new value
208 int keyToReplace = Math.abs(r.nextInt())%k;
209                     sampledKeys.set(keyToReplace, key);
210                 }
211             }
212         }
213         return sampledKeys;
214     }
215     
216     /**
217      * Delete a random sample of n records in the resultset
218      * @param rs result set to be updated
219      * @param rows map of rows, will also be updated
220      * @param deletedRows set of rows being deleted (position in RS)
221      * @param k number of records to be deleted
222      */
223     private void deleteRandomSampleOfNRecords(final ResultSet rs,
224                                               final Map rows,
225                                               final Set deletedRows,
226                                               final int k)
227         throws SQLException
228     {
229         List sampledKeys = createRandomSample(rows, k);
230         println("Sampled keys:" + sampledKeys);
231         ResultSetMetaData meta = rs.getMetaData();
232         for (Iterator i = sampledKeys.iterator(); i.hasNext();) {
233             Integer key = (Integer ) i.next();
234             rs.absolute(key.intValue());
235             if (rs.rowDeleted()) continue; // skip deleting row if already deleted
236 if (positioned) {
237                 createStatement().executeUpdate
238                         ("DELETE FROM T1 WHERE CURRENT OF \"" + cursorName +
239                          "\"");
240             } else {
241                 rs.deleteRow();
242             }
243             rs.relative(0);
244             println("Deleted row " + key);
245             // Update the rows table
246 rows.put(key, getRowString(rs));
247             
248             // Update the updatedRows set
249 deletedRows.add(key);
250         }
251     }
252     
253     /**
254      * Update a random sample of n records in the resultset
255      * @param rs result set to be updated
256      * @param rows map of rows, will also be updated
257      * @param updatedRows set of being updated (position in RS)
258      * @param k number of records to be updated
259      */
260     private void updateRandomSampleOfNRecords(final ResultSet rs,
261                                               final Map rows,
262                                               final Set updatedRows,
263                                               final int k)
264         throws SQLException
265     {
266         List sampledKeys = createRandomSample(rows, k);
267         println("Sampled keys:" + sampledKeys);
268         ResultSetMetaData meta = rs.getMetaData();
269         for (Iterator i = sampledKeys.iterator(); i.hasNext();) {
270             Integer key = (Integer ) i.next();
271             rs.absolute(key.intValue());
272             
273             if (positioned) {
274                 updatePositioned(rs, meta);
275                 rs.relative(0); // If this call is not here, the old values are
276 // returned in rs.getXXX calls
277 } else {
278                 updateRow(rs, meta);
279             }
280             // Update the rows table
281 rows.put(key, getRowString(rs));
282             
283             // Update the updatedRows set
284 updatedRows.add(key);
285         }
286     }
287
288     /**
289      * Updates the current row in the ResultSet using updateRow()
290      * @param rs ResultSet to be updated
291      * @param meta meta for the ResultSet
292      **/
293     private void updateRow(final ResultSet rs, final ResultSetMetaData meta)
294         throws SQLException
295     {
296         for (int column = 1; column<=meta.getColumnCount(); column++) {
297             if (meta.getColumnType(column)==Types.INTEGER) {
298                 // Set to negative value
299 rs.updateInt(column, -rs.getInt(column));
300             } else {
301                 rs.updateString(column, "UPDATED_" + rs.getString(column));
302             }
303         }
304         rs.updateRow();
305     }
306     
307     /**
308      * Updates the current row in the ResultSet using updateRow()
309      * @param rs ResultSet to be updated
310      * @param meta meta for the ResultSet
311      **/
312     private void updatePositioned(final ResultSet rs,
313                                   final ResultSetMetaData meta)
314         throws SQLException
315     {
316         StringBuffer sb = new StringBuffer ();
317         sb.append("UPDATE T1 SET ");
318         for (int column = 1; column<=meta.getColumnCount(); column++) {
319             sb.append(meta.getColumnName(column));
320             sb.append("=?");
321             if (column<meta.getColumnCount()) {
322                 sb.append(",");
323             }
324         }
325         sb.append(" WHERE CURRENT OF \"");
326         sb.append(cursorName);
327         sb.append("\"");
328         println(sb.toString());
329         PreparedStatement ps = prepareStatement(sb.toString());
330         
331         for (int column = 1; column<=meta.getColumnCount(); column++) {
332            if (meta.getColumnType(column)==Types.INTEGER) {
333                 // Set to negative value
334 ps.setInt(column, -rs.getInt(column));
335             } else {
336                 ps.setString(column, "UPDATED_" + rs.getString(column));
337             }
338         }
339         assertEquals("Expected one row to be updated", 1, ps.executeUpdate());
340     }
341     
342     
343     /**
344      * Tests navigation in ResultSet.
345      * @param ResultSet rs ResultSet to test navigation of.
346      * Needs to be scrollable
347      * @param Map rows a sample of the rows which are in the ResultSet. Maps
348      * position to a concatenation of the string values
349      * @param Set updatedRows a integer set of which rows that have been
350      * updated. Used to test rowUpdated()
351      * @param Set deletedRows a integer set of which rows that have been
352      * deleted. Used to test rowDeleted()
353      */
354     private void testNavigation(final ResultSet rs, final Map rows,
355                                 final Set updatedRows, final Set deletedRows)
356         throws SQLException
357     {
358         rs.afterLast();
359         {
360             int i = rows.size();
361             while (rs.previous()) {
362                 String rowString = getRowString(rs);
363                 assertEquals("Navigating with rs.previous(). The row is " +
364                              "different compared to the value when navigating " +
365                              "forward.", rows.get(new Integer (i)), rowString);
366                 
367                 
368                 if (checkRowUpdated && updatedRows.contains(new Integer (i))) {
369                     assertTrue("Expected rs.rowUpdated() to return true on " +
370                                "updated row " + rowString, rs.rowUpdated());
371                 }
372                 if (checkRowDeleted && deletedRows.contains(new Integer (i))) {
373                     assertTrue("Expected rs.rowDeleted() to return true on " +
374                                "deleted row " + rowString, rs.rowDeleted());
375                 }
376                 i--;
377             }
378         }
379         // Test absolute
380 for (int i = 1; i <= rows.size(); i++) {
381             assertTrue("Unexpected return from absolute()", rs.absolute(i));
382             String rowString = getRowString(rs);
383             assertEquals("Navigating with rs.absolute(). The row is " +
384                          "different compared to the value" +
385                          " when navigating forward.",
386                          rows.get(new Integer (i)),
387                          rowString);
388             if (checkRowUpdated && updatedRows.contains(new Integer (i))) {
389                 assertTrue("Expected rs.rowUpdated() to return true on " +
390                            "updated row " + rowString, rs.rowUpdated());
391             }
392             if (checkRowDeleted && deletedRows.contains(new Integer (i))) {
393                 assertTrue("Expected rs.rowDeleted() to return true on " +
394                            "deleted row " + rowString, rs.rowDeleted());
395             }
396         }
397         assertFalse("Unexpected return from absolute()", rs.absolute(0));
398         assertTrue("Unexpected return from isBeforeFirst()",
399                    rs.isBeforeFirst());
400         assertFalse("Unexpected return from absolute()",
401                     rs.absolute(rows.size() + 1));
402         assertTrue("Unexpected return from isAfterLast()", rs.isAfterLast());
403         assertTrue("Unexpected return from absolute()", rs.absolute(-1));
404         assertTrue("Unexpected return from isLast()", rs.isLast());
405         assertTrue("Unexpected return from absolute()", rs.absolute(1));
406         assertTrue("Unexpected return from isFirst()", rs.isFirst());
407         
408         // Test relative
409 {
410             rs.beforeFirst();
411             assertTrue("Unexptected return from isBeforeFirst()",
412                        rs.isBeforeFirst());
413             
414             int relativePos = rows.size();
415             assertTrue("Unexpected return from relative()",
416                        rs.relative(relativePos));
417             
418             // Should now be on the last row
419 assertTrue("Unexptected return from isLast()", rs.isLast());
420             assertEquals("Navigating with rs.relative(+). " +
421                          "A tuple was different compared to the value" +
422                          " when navigating forward.",
423                          rows.get(new Integer (relativePos)),
424                          getRowString(rs));
425             
426             assertTrue("Unexpected return from relative()",
427                        rs.relative((-relativePos + 1)));
428             
429             // Should now be on the first row
430 assertTrue("Unexptected return from isFirst()", rs.isFirst());
431             
432             assertEquals("Navigating with rs.relative(-). " +
433                          "A tuple was different compared to the value" +
434                          " when navigating forward.",
435                          rows.get(new Integer (1)),
436                          getRowString(rs));
437             
438         }
439         // Test navigation in the end of the ResultSet
440 rs.afterLast();
441         assertTrue("Unexpected return from isAfterLast()", rs.isAfterLast());
442         assertTrue("Unexpected return from previous()", rs.previous());
443         assertTrue("Unexpected return from isLast()", rs.isLast());
444         assertFalse("Unexpected return from next()", rs.next());
445         assertTrue("Unexpected return from isAfterLast()", rs.isAfterLast());
446         rs.last();
447         assertTrue("Unexpected return from isLast()", rs.isLast());
448         assertFalse("Unexpected return from next()", rs.next());
449         assertTrue("Unexpected return from isAfterLast()", rs.isAfterLast());
450
451
452         // Test navigation in the beginning of the ResultSet
453 rs.beforeFirst();
454         assertTrue("Unexpected return from isBeforeFirst()",
455                    rs.isBeforeFirst());
456         assertTrue("Unexpected return from next()", rs.next());
457         assertTrue("Unexpected return from isFirst", rs.isFirst());
458         assertFalse("Unexpected return from previous()", rs.previous());
459         assertTrue("Unexpected return from isBeforeFirst()",
460                    rs.isBeforeFirst());
461         
462         rs.first();
463         assertTrue("Unexpected return from isFirst", rs.isFirst());
464         assertFalse("Unexpected return from previous()", rs.previous());
465         assertTrue("Unexpected return from isBeforeFirst()",
466                    rs.isBeforeFirst());
467     }
468
469     /**
470      * Get a concatenation of the values of the
471      * current Row in the ResultSet
472      */
473     private String getRowString(final ResultSet rs)
474         throws SQLException
475     {
476         int numberOfColumns = rs.getMetaData().getColumnCount();
477         StringBuffer sb = new StringBuffer ();
478         if (rs.rowDeleted()) return "";
479         for (int i = 1; i <= numberOfColumns; i++) {
480             sb.append(rs.getString(i));
481             if (i < numberOfColumns) {
482                 sb.append(',');
483             }
484         }
485         return sb.toString();
486     }
487     
488     private final String query;
489     private final String cursorName;
490     private final boolean positioned;
491     private boolean checkRowUpdated;
492     private boolean checkRowDeleted;
493     
494     private final static String [] selectConditions = new String [] {
495         "WHERE c like 'T%'",
496         " ",
497         "WHERE b > 5",
498         "WHERE id >= a",
499         "WHERE id > 1 and id < 900",
500         "WHERE id = 1",
501         "WHERE id in (1,3,4,600,900,955,966,977,978)",
502         "WHERE a in (1,3,4,600,9200,955,966,977,978)",
503         "WHERE a>2 and a<9000"
504     };
505     
506     private final static String [] projectConditions = new String [] {
507         "id,c,a,b",
508         "id,c",
509         "a,b",
510         "*",
511         "id,a,b,c",
512         "id,a",
513         "a,b,c",
514         "a,c"
515     };
516     
517     private static TestSuite createTestCases(final String modelName) {
518         TestSuite suite = new TestSuite();
519         for (int doPos = 0; doPos<2; doPos++) {
520             boolean positioned = doPos>0; // true if to use positioned updates
521
522             for (int i = 0; i < selectConditions.length; i++) {
523                 for (int j = 0; j < projectConditions.length; j++) {
524                     final String cursorName = "cursor_" + i + "_" + j;
525                     
526                     final String stmtString = "SELECT " + projectConditions[j] +
527                             " FROM T1 " + selectConditions[i];
528                     suite.addTest(new SURQueryMixTest(modelName, stmtString, cursorName,
529                                               positioned));
530                 }
531             }
532         }
533         return suite;
534     }
535     
536     /**
537      * The suite contains all testcases in this class running on different data models
538      */
539     public static Test suite()
540     {
541         TestSuite mainSuite = new TestSuite();
542         
543         // DB2 client doesn't support this functionality
544 if (usingDerbyNet())
545             return mainSuite;
546   
547         
548         // Iterate over all data models and decorate the tests:
549 for (Iterator i = SURDataModelSetup.SURDataModel.values().iterator();
550              i.hasNext();) {
551             
552             SURDataModelSetup.SURDataModel model =
553                 (SURDataModelSetup.SURDataModel) i.next();
554             
555             TestSuite suite = createTestCases(model.toString());
556             TestSetup decorator = new SURDataModelSetup(suite, model);
557             mainSuite.addTest(decorator);
558         }
559         return mainSuite;
560     }
561
562     protected void tearDown() throws Exception {
563         super.tearDown();
564         con = null;
565     }
566     
567   
568 }
569

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags