1 package org.codehaus.dataforge.engine;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 import java.io.IOException;
20 import java.io.InputStream;
21 import java.math.BigDecimal;
22 import java.sql.Connection;
23 import java.sql.Driver;
24 import java.sql.DriverManager;
25 import java.sql.ResultSet;
26 import java.sql.ResultSetMetaData;
27 import java.sql.SQLException;
28 import java.sql.Statement;
29 import java.util.StringTokenizer;
30
31 import javax.sql.DataSource;
32
33 import junit.framework.TestCase;
34
35 import org.codehaus.dataforge.engine.datasource.DataSourceManager;
36 import org.codehaus.dataforge.engine.datasource.MapDataSourceFactory;
37 import org.codehaus.dataforge.engine.transformers.ResultSetJDBCTransformer;
38 import org.codehaus.dataforge.engine.transformers.TransformerResultSetMetaData;
39
40 import com.walding.common.database.JDBCDataSource;
41 import com.walding.common.database.JDBCUtility;
42 import com.walding.common.io.StreamUtility;
43
44 /***
45 * @author Ben Walding
46 *
47 */
48 public class QuickDB extends TestCase
49 {
50 Connection conn;
51
52 public void setUp() throws Exception
53 {
54 conn = getConnection();
55 setupDB(conn);
56 }
57
58 public void tearDown() throws Exception
59 {
60 Statement stmt = conn.createStatement();
61 stmt.execute("DROP TABLE Company");
62 stmt.execute("DROP TABLE Person");
63 stmt.execute("DROP TABLE CompanyPerson");
64 stmt.execute("DROP TABLE PersonArchive");
65 stmt.execute("DROP TABLE Diversity");
66 stmt.execute("DROP TABLE GroupByTable");
67 stmt.execute("DROP TABLE Hierarchy");
68 JDBCUtility.close(stmt);
69 JDBCUtility.close(conn);
70 }
71
72 public static Connection getConnection() throws Exception
73 {
74 return getDataSource().getConnection();
75 }
76
77 public static DataSource getDataSource() throws Exception
78 {
79 Class driverClass = Class.forName("org.hsqldb.jdbcDriver");
80 DriverManager.registerDriver((Driver) driverClass.newInstance());
81 return new JDBCDataSource("jdbc:hsqldb:.", "sa", "");
82 }
83
84 protected void setupDB(Connection conn) throws IOException, SQLException
85 {
86 InputStream is = getClass().getResourceAsStream("quickdb.sql");
87 String s = StreamUtility.readInputStreamIntoString(is);
88 StringTokenizer st = new StringTokenizer(s, ";");
89 Statement stmt = conn.createStatement();
90 while (st.hasMoreTokens())
91 {
92 String tok = st.nextToken();
93
94 stmt.execute(tok);
95 }
96 }
97
98 protected void verifyCompany(Connection newConn) throws Exception
99 {
100 ResultSet rst = newConn.createStatement().executeQuery("SELECT * FROM Company ORDER BY rfCompany");
101
102 ResultSetJDBCTransformer rsrs = new ResultSetJDBCTransformer(rst);
103 ResultSet rs = rsrs.open();
104
105 assertTrue("1. rs.next()", rs.next());
106 assertEquals("r[1][1]", new Integer(1), rs.getObject(1));
107 assertEquals("r[1][2]", "Grange", rs.getObject(2));
108
109 assertTrue("2. rs.next()", rs.next());
110 assertEquals("r[2][1]", new Integer(2), rs.getObject(1));
111 assertEquals("r[2][2]", "Satellis", rs.getObject(2));
112
113 assertTrue("3. rs.next()", rs.next());
114 assertEquals("r[3][1]", new Integer(3), rs.getObject(1));
115 assertEquals("r[3][2]", "Delphinius", rs.getObject(2));
116
117 assertTrue("4. rs.next()", rs.next());
118 assertEquals("r[4][1]", new Integer(4), rs.getObject(1));
119 assertEquals("r[4][2]", "Ethernetillus", rs.getObject(2));
120
121 assertTrue("5. rs.next()", rs.next());
122 assertEquals("r[5][1]", new Integer(5), rs.getObject(1));
123 assertEquals("r[5][2]", "Compassion", rs.getObject(2));
124
125 }
126
127 protected void verifyPerson(Connection newConn) throws Exception
128 {
129 ResultSet rst = newConn.createStatement().executeQuery("SELECT * FROM Person ORDER BY rfPerson");
130
131 ResultSetJDBCTransformer rsrs = new ResultSetJDBCTransformer(rst);
132 ResultSet rs = rsrs.open();
133 verifyPerson(rs);
134
135 }
136
137 public static void verifyPersonSchema(ResultSetMetaData rsmd) throws SQLException
138 {
139 assertEquals("rsmd.getColumnCount()", 4, rsmd.getColumnCount());
140 assertEquals("rsmd.getColumnName(1)", "RFPERSON", rsmd.getColumnName(1).toUpperCase());
141 assertEquals("rsmd.getColumnName(2)", "TXFIRSTNAME", rsmd.getColumnName(2).toUpperCase());
142 assertEquals("rsmd.getColumnName(3)", "TXLASTNAME", rsmd.getColumnName(3).toUpperCase());
143 assertEquals("rsmd.getColumnName(4)", "PCPROFITSHARE", rsmd.getColumnName(4).toUpperCase());
144 }
145
146 public static void verifyPersonData1(ResultSet rs) throws Exception
147 {
148 assertEquals("r[1][1]", new Integer(1), rs.getObject(1));
149 assertEquals("r[1][2]", "Bob", rs.getObject(2));
150 assertEquals("r[1][3]", "Dawkins", rs.getObject(3));
151 assertEquals("r[1][4]", new BigDecimal("33.3"), rs.getObject(4));
152 }
153
154 public static void verifyPersonData2(ResultSet rs) throws Exception
155 {
156 assertEquals("r[2][1]", new Integer(2), rs.getObject(1));
157 assertEquals("r[2][2]", "Gerald", rs.getObject(2));
158 assertEquals("r[2][3]", "Astinheimer", rs.getObject(3));
159 assertEquals("r[2][4]", new BigDecimal("20.5"), rs.getObject(4));
160 }
161
162 public static void verifyPersonData3(ResultSet rs) throws Exception
163 {
164 assertEquals("r[3][1]", new Integer(3), rs.getObject(1));
165 assertEquals("r[3][2]", "Robby", rs.getObject(2));
166 assertEquals("r[3][3]", "Schnutz", rs.getObject(3));
167 assertEquals("r[3][4]", new BigDecimal("15.0"), rs.getObject(4));
168 }
169
170 public static void verifyPersonData4(ResultSet rs) throws Exception
171 {
172 assertEquals("r[4][1]", new Integer(4), rs.getObject(1));
173 assertEquals("r[4][2]", "Leopold", rs.getObject(2));
174 assertEquals("r[4][3]", "Humphreys", rs.getObject(3));
175 assertEquals("r[4][4]", new BigDecimal("40.0"), rs.getObject(4));
176 }
177
178 public static void verifyPerson(ResultSet rs) throws Exception
179 {
180
181 TransformerResultSetMetaData rsmd = (TransformerResultSetMetaData) rs.getMetaData();
182 verifyPersonSchema(rsmd);
183
184 assertTrue(rs.next());
185 verifyPersonData1(rs);
186
187 assertTrue(rs.next());
188 verifyPersonData2(rs);
189
190 assertTrue(rs.next());
191 verifyPersonData3(rs);
192
193 assertTrue(rs.next());
194 verifyPersonData4(rs);
195
196 assertFalse("Check only 4 rows", rs.next());
197 }
198
199 protected void verifyPersonArchive(Connection newConn) throws Exception
200 {
201 ResultSet rst = newConn.createStatement().executeQuery("SELECT * FROM PersonArchive ORDER BY rfPerson");
202
203 ResultSetJDBCTransformer rsrs = new ResultSetJDBCTransformer(rst);
204 ResultSet rs = rsrs.open();
205 verifyPersonArchive(rs);
206
207 }
208
209 public static void verifyPersonArchive(ResultSet rs) throws Exception
210 {
211
212 TransformerResultSetMetaData rsmd = (TransformerResultSetMetaData) rs.getMetaData();
213
214 assertEquals("rsmd.getColumnCount()", 4, rsmd.getColumnCount());
215 assertEquals("rsmd.getColumnName(1)", "RFPERSON", rsmd.getColumnName(1).toUpperCase());
216 assertEquals("rsmd.getColumnName(2)", "TXFIRSTNAME", rsmd.getColumnName(2).toUpperCase());
217 assertEquals("rsmd.getColumnName(3)", "TXLASTNAME", rsmd.getColumnName(3).toUpperCase());
218 assertEquals("rsmd.getColumnName(4)", "PCPROFITSHARE", rsmd.getColumnName(4).toUpperCase());
219
220 assertTrue(rs.next());
221 assertEquals("r[1][1]", new Integer(1), rs.getObject(1));
222 assertEquals("r[1][2]", "Robert", rs.getObject(2));
223 assertEquals("r[1][3]", "Dawkins", rs.getObject(3));
224 assertEquals("r[1][4]", new BigDecimal("25.0"), rs.getObject(4));
225
226 assertTrue(rs.next());
227 assertEquals("r[2][1]", new Integer(3), rs.getObject(1));
228 assertEquals("r[2][2]", "Robby", rs.getObject(2));
229 assertEquals("r[2][3]", "Schnutz", rs.getObject(3));
230 assertEquals("r[2][4]", new BigDecimal("15.0"), rs.getObject(4));
231
232 assertTrue(rs.next());
233 assertEquals("r[3][1]", new Integer(5), rs.getObject(1));
234 assertEquals("r[3][2]", "Lenin", rs.getObject(2));
235 assertEquals("r[3][3]", "Harrods", rs.getObject(3));
236 assertEquals("r[3][4]", new BigDecimal("42.0"), rs.getObject(4));
237
238 assertFalse("Check only 3 rows", rs.next());
239 }
240
241 protected void verifyCompanyPerson(Connection newConn) throws Exception
242 {
243 ResultSet rst = newConn.createStatement().executeQuery("SELECT * FROM CompanyPerson");
244
245 ResultSetJDBCTransformer rsrs = new ResultSetJDBCTransformer(rst);
246 ResultSet rs = rsrs.open();
247
248 assertTrue("1. rs.next()", rs.next());
249 assertEquals("r[1][0]", new Integer(1), rs.getObject(1));
250 assertEquals("r[1][1]", new Integer(1), rs.getObject(2));
251 assertEquals("r[1][2]", new Integer(3), rs.getObject(3));
252
253 assertTrue("2. rs.next()", rs.next());
254 assertEquals("r[2][0]", new Integer(2), rs.getObject(1));
255 assertEquals("r[2][1]", new Integer(2), rs.getObject(2));
256 assertEquals("r[2][2]", new Integer(4), rs.getObject(3));
257
258 assertTrue("3. rs.next()", rs.next());
259 assertEquals("r[3][0]", new Integer(3), rs.getObject(1));
260 assertEquals("r[3][1]", new Integer(3), rs.getObject(2));
261 assertEquals("r[31][2]", new Integer(1), rs.getObject(3));
262
263 assertTrue("4. rs.next()", rs.next());
264 assertEquals("r[4][0]", new Integer(4), rs.getObject(1));
265 assertEquals("r[4][1]", new Integer(4), rs.getObject(2));
266 assertEquals("r[4][2]", new Integer(2), rs.getObject(3));
267
268 assertTrue("5. rs.next()", rs.next());
269 assertEquals("r[5][0]", new Integer(5), rs.getObject(1));
270 assertEquals("r[5][1]", new Integer(5), rs.getObject(2));
271 assertEquals("r[5][2]", new Integer(4), rs.getObject(3));
272
273 assertTrue("6. rs.next()", rs.next());
274 assertEquals("r[6][0]", new Integer(6), rs.getObject(1));
275 assertEquals("r[6][1]", new Integer(99999), rs.getObject(2));
276 assertEquals("r[6][2]", new Integer(4), rs.getObject(3));
277
278 assertTrue("7. rs.next()", rs.next());
279 assertEquals("r[7][0]", new Integer(7), rs.getObject(1));
280 assertEquals("r[7][1]", new Integer(5), rs.getObject(2));
281 assertEquals("r[7][2]", new Integer(99999), rs.getObject(3));
282
283 assertFalse("Check only 4 rows", rs.next());
284
285 }
286
287 public void configureQuickDB() throws Exception
288 {
289 DataSource ds = getDataSource();
290
291 MapDataSourceFactory mdsf = new MapDataSourceFactory();
292 mdsf.addDataSource("qdb", ds);
293 DataSourceManager.instance().registerFactory(mdsf);
294 }
295
296 protected void verifyGroupByTable(Connection newConn) throws Exception
297 {
298 ResultSet rst = newConn.createStatement().executeQuery("SELECT * FROM GroupByTable");
299
300 ResultSetJDBCTransformer rsrs = new ResultSetJDBCTransformer(rst);
301 ResultSet rs = rsrs.open();
302
303 TransformerResultSetMetaData rsmd = (TransformerResultSetMetaData) rs.getMetaData();
304
305 assertEquals("rsmd.getColumnCount()", 5, rsmd.getColumnCount());
306 assertEquals("rsmd.getColumnName(2)", "RFGROUPBYTABLE", rsmd.getColumnName(1).toUpperCase());
307 assertEquals("rsmd.getColumnName(2)", "TXALPHA", rsmd.getColumnName(2).toUpperCase());
308 assertEquals("rsmd.getColumnName(3)", "TXBETA", rsmd.getColumnName(3).toUpperCase());
309 assertEquals("rsmd.getColumnName(4)", "TXGAMMA", rsmd.getColumnName(4).toUpperCase());
310 assertEquals("rsmd.getColumnName(5)", "FLDELTA", rsmd.getColumnName(5).toUpperCase());
311
312 assertTrue(rs.next());
313 assertEquals("r[1][1]", new Integer(1), rs.getObject(1));
314 assertEquals("r[1][2]", "directory", rs.getObject(2));
315 assertEquals("r[1][3]", "represents", rs.getObject(3));
316 assertEquals("r[1][4]", "organisation", rs.getObject(4));
317 assertEquals("r[1][5]", new BigDecimal("10.0"), rs.getObject(5));
318
319 assertTrue(rs.next());
320 assertEquals("r[2][1]", new Integer(2), rs.getObject(1));
321 assertEquals("r[2][2]", "directory", rs.getObject(2));
322 assertEquals("r[2][3]", "lists", rs.getObject(3));
323 assertEquals("r[2][4]", "files", rs.getObject(4));
324 assertEquals("r[2][5]", new BigDecimal("13.0"), rs.getObject(5));
325
326 assertTrue(rs.next());
327 assertEquals("r[3][1]", new Integer(3), rs.getObject(1));
328 assertEquals("r[3][2]", "documents", rs.getObject(2));
329 assertEquals("r[3][3]", "are", rs.getObject(3));
330 assertEquals("r[3][4]", "files", rs.getObject(4));
331 assertEquals("r[3][5]", new BigDecimal("15.0"), rs.getObject(5));
332
333 assertTrue(rs.next());
334 assertEquals("r[4][1]", new Integer(4), rs.getObject(1));
335 assertEquals("r[4][2]", "flies", rs.getObject(2));
336 assertEquals("r[4][3]", "are", rs.getObject(3));
337 assertEquals("r[4][4]", "animals", rs.getObject(4));
338 assertEquals("r[4][5]", new BigDecimal("16.0"), rs.getObject(5));
339
340 assertTrue(rs.next());
341 assertEquals("r[5][1]", new Integer(5), rs.getObject(1));
342 assertEquals("r[5][2]", "rats", rs.getObject(2));
343 assertEquals("r[5][3]", "are", rs.getObject(3));
344 assertEquals("r[5][4]", "vermin", rs.getObject(4));
345 assertEquals("r[5][5]", new BigDecimal("1.0"), rs.getObject(5));
346
347 assertTrue(rs.next());
348 assertEquals("r[6][1]", new Integer(6), rs.getObject(1));
349 assertEquals("r[6][2]", "rats", rs.getObject(2));
350 assertEquals("r[6][3]", "are", rs.getObject(3));
351 assertEquals("r[6][4]", "furry", rs.getObject(4));
352 assertEquals("r[6][5]", new BigDecimal("90.0"), rs.getObject(5));
353
354 assertFalse("Check only 6 rows", rs.next());
355 }
356
357 /***
358 * @param conn2
359 */
360 public void verifyHierarchy(Connection newConn) throws Exception
361 {
362 ResultSet rst = newConn.createStatement().executeQuery("SELECT * FROM Hierarchy");
363
364 ResultSetJDBCTransformer rsrs = new ResultSetJDBCTransformer(rst);
365 ResultSet rs = rsrs.open();
366
367 TransformerResultSetMetaData rsmd = (TransformerResultSetMetaData) rs.getMetaData();
368 assertEquals("rsmd.getColumnCount()", 3, rsmd.getColumnCount());
369 assertEquals("rsmd.getColumnName(2)", "RFHIERARCHY", rsmd.getColumnName(1).toUpperCase());
370 assertEquals("rsmd.getColumnName(2)", "RFPERSON", rsmd.getColumnName(2).toUpperCase());
371 assertEquals("rsmd.getColumnName(3)", "SUPERVISOR_RFPERSON", rsmd.getColumnName(3).toUpperCase());
372
373 assertTrue(rs.next());
374 assertEquals("r[1][1]", new Integer(1), rs.getObject(1));
375 assertEquals("r[1][2]", new Integer(2), rs.getObject(2));
376 assertEquals("r[1][3]", new Integer(1), rs.getObject(3));
377
378 assertTrue(rs.next());
379 assertEquals("r[2][1]", new Integer(2), rs.getObject(1));
380 assertEquals("r[2][2]", new Integer(3), rs.getObject(2));
381 assertEquals("r[2][3]", new Integer(2), rs.getObject(3));
382
383 assertTrue(rs.next());
384 assertEquals("r[3][1]", new Integer(3), rs.getObject(1));
385 assertEquals("r[3][2]", new Integer(4), rs.getObject(2));
386 assertEquals("r[3][3]", new Integer(3), rs.getObject(3));
387
388 assertTrue(rs.next());
389 assertEquals("r[4][1]", new Integer(4), rs.getObject(1));
390 assertEquals("r[4][2]", new Integer(1), rs.getObject(2));
391 assertEquals("r[4][3]", null, rs.getObject(3));
392
393 assertFalse("Check only 4 rows", rs.next());
394 }
395 }