1 /*
2 * $Id: SqlResults.java,v 1.11 2006/08/21 15:39:30 clq2 Exp $
3 *
4 * (C) Copyright Astrogrid...
5 */
6
7 package org.astrogrid.tableserver.jdbc;
8
9 import java.io.IOException;
10 import java.sql.ResultSet;
11 import java.sql.ResultSetMetaData;
12 import java.sql.SQLException;
13 import java.sql.Types;
14 import java.util.Date;
15 import org.apache.commons.logging.Log;
16 import org.astrogrid.cfg.ConfigFactory;
17 import org.astrogrid.dataservice.DatacenterException;
18 import org.astrogrid.dataservice.queriers.Querier;
19 import org.astrogrid.dataservice.queriers.TableResults;
20 import org.astrogrid.dataservice.queriers.status.QuerierStatus;
21 import org.astrogrid.query.Query;
22 import org.astrogrid.query.returns.ReturnTable;
23 import org.astrogrid.tableserver.metadata.ColumnInfo;
24 import org.astrogrid.tableserver.metadata.TableMetaDocInterpreter;
25 import org.astrogrid.tableserver.metadata.TooManyColumnsException;
26 import org.astrogrid.tableserver.out.TableWriter;
27
28 /***
29 * Implementation of <tt>QueryResults</tt> as a wrapper around a <tt>ResultSet</tt>
30 *
31 * <p>Can be used (I believe) for any
32 * SQL/JDBC query results.
33 *
34 * @author M Hill
35 * @author K Andrews
36 */
37
38 public class SqlResults extends TableResults {
39
40 protected ResultSet sqlResults;
41 protected static final Log log = org.apache.commons.logging.LogFactory.getLog(SqlResults.class);
42
43 /***
44 * Construct this wrapper around the given JDBC/SQL ResultSet. We don't
45 * know how big this result set will be, so it's likely we'll need
46 * a workspace for any temporary files created when doing conversions
47 */
48 public SqlResults(Querier parentQuerier, ResultSet givenResults)
49 {
50 super(parentQuerier);
51 this.sqlResults = givenResults;
52
53 }
54
55 /*** Returns number of rows */
56 public int getCount() {
57 return -1;
58 /*
59 try {
60 if (sqlResults.last() == false) return 0; //disturbs the cursor for votable conversion...
61 return sqlResults.getRow();
62 }
63 catch (SQLException sqle) { //may be an unsupported operation
64 log.error(sqle);
65 return -1;
66 }
67 */
68 }
69
70 /***/
71 public Class getJavaType(int sqlType) {
72 switch (sqlType)
73 {
74 case Types.BIGINT: return Long.class;
75 case Types.BOOLEAN: return Boolean.class;
76 case Types.VARCHAR: return String.class;
77 case Types.CHAR: return String.class;
78 case Types.DOUBLE: return Double.class;
79 case Types.FLOAT: return Float.class;
80 case Types.INTEGER: return Integer.class;
81 case Types.REAL: return Float.class;
82 case Types.SMALLINT: return Integer.class;
83 case Types.TINYINT: return Integer.class;
84 case Types.DATE: return Date.class;
85 case Types.TIMESTAMP:return Date.class;
86 default: {
87 log.error("Don't know what Java class SQL type "+sqlType+" maps to, storing as string", new RuntimeException()); //add runtime exception so we get a stack trace
88 return String.class;
89 }
90 }
91 }
92
93 /***
94 * Writes out results from SQL Result set to given table writer
95 * @TOFIX The metadata stuff in here is still pretty dubious...
96 * can we do it better?
97 */
98 public void writeTable(TableWriter tableWriter, QuerierStatus statusToUpdate) throws IOException
99 {
100
101 int unknownCount = 1;
102 int dummyColIndex = 1;
103
104 long localLimit = ConfigFactory.getCommonConfig().getInt(Query.MAX_RETURN_KEY, -1);
105 long queryLimit = querier.getQuery().getLimit();
106
107 try
108 {
109 tableWriter.open();
110
111 //list columns
112 ResultSetMetaData metadata = sqlResults.getMetaData();
113
114 //NB metadata columns start at 1 (not zero)
115 int numCols = metadata.getColumnCount();
116 ColumnInfo[] cols = new ColumnInfo[numCols];
117
118 //Expression[] colDefs = ((ReturnTable) querier.getQuery().getResultsDef()).getColDefs(); //columns defined by querier
119 String[] colDefs = querier.getQuery().getColumnReferences();
120 /*
121 for (int i = 0; i < colDefs.length; i++) {
122 System.out.println("Column " + Integer.toString(i) +
123 " is " + colDefs[i]);
124 }
125 */
126 TableMetaDocInterpreter interpreter = new TableMetaDocInterpreter();
127 for (int i=1;i<=numCols;i++) // Handle all columns in results
128 {
129 // We need to know the table and column name to set up the
130 // relevant column metadata in the output table
131 String columnName = null;
132 String tableName = null;
133
134 try {
135 // First, see if we can get these from the JDBC results
136 columnName = metadata.getColumnName(i).trim();
137 /*
138 * We might have a null/empty column name if the column is a
139 * derived column (math expression or whatever) - e.g.
140 * true for HSQLDB.
141 */
142 if (columnName.trim().length()==0) {
143 columnName = null;
144 /*
145 throw new SQLException(
146 "JDBC metadata returns empty column name");
147 */
148 }
149 tableName = metadata.getTableName(i).trim();
150 if (tableName.trim().length()==0) {
151 tableName = null;
152 /*
153 throw new SQLException(
154 "JDBC metadata returns empty table name");
155 */
156 }
157 else {
158 // The table name in the metadata might well be a
159 // column alias; try to get the real table name.
160 // If the input tableName from the metadata is not
161 // actually a table name or table alias, the tableName
162 // variable will be set back to null by the statement
163 // below.
164 tableName = querier.getQuery().getTableName(tableName);
165 }
166 }
167 catch (SQLException se) { // Couldn't get table/column name
168 // No need to do anything here
169 }
170 /*
171 log.debug("=============== Found table name " + tableName);
172 log.debug("=============== Found column name " + columnName);
173 */
174
175 if (tableName == null) { // Didn't manage to get it from metadata
176 // Try and get table name from interpreteter
177 if (columnName != null) {
178 log.debug("Trying to guess table name for column "+columnName);
179 try {
180 cols[i-1] = interpreter.guessColumn(
181 querier.getQuery().getTableReferences(), columnName);
182 // Note : If the column isn't found at all,
183 // cols[i-1] will now be null
184 // TOFIX Change TableMetaDocInterpreter to throw
185 // an exception in this case?
186 }
187 catch (TooManyColumnsException me) {
188 log.error(me+" guessing which column "+
189 columnName+" belongs to which table");
190 //but carry on with a dummy table name
191 cols[i-1] = new ColumnInfo();
192 cols[i-1].setName(columnName);
193 cols[i-1].setId("UNKNOWN."+columnName);
194 tableName = null; // Just in case
195 }
196 if (cols[i-1] == null) { // Didn't find a column of that name
197 log.error(" Didn't find column with name '"+
198 columnName+"' in any table");
199
200 cols[i-1] = new ColumnInfo();
201
202 // Sanify column name if necessary
203 //(some JDBC drivers return column names like ?column?
204 //for derived columns which will break VOTable validity
205 //if used directly.
206 if (columnName.matches("//w+") == false) {
207 // Column name contains other than alphanumeric and
208 // underscore characters
209 String newColumnName = "UNKNOWN_" +
210 Integer.toString(unknownCount);
211 unknownCount = unknownCount+1;
212 cols[i-1].setName(newColumnName);
213 cols[i-1].setId("UNKNOWN."+newColumnName);
214 log.info("Got dubious unmatched column name " +
215 columnName + " from JDBC, replacing it with " +
216 newColumnName);
217 columnName = newColumnName;
218 }
219 else {
220 // JDBC column name is OK to use
221 cols[i-1] = new ColumnInfo();
222 cols[i-1].setName(columnName);
223 cols[i-1].setId("UNKNOWN."+columnName);
224 }
225 tableName = null; // Just in case
226 }
227 }
228 else { // Don't have column or table name
229 // Use unknownCount to ensure unique table names
230 cols[i-1] = new ColumnInfo();
231 cols[i-1].setName("UNKNOWN_" +
232 Integer.toString(unknownCount));
233 cols[i-1].setId("UNKNOWN."+"UNKNOWN_"+
234 Integer.toString(unknownCount));
235 unknownCount = unknownCount+1;
236 tableName = null; // Just in case
237 }
238 }
239 // When we get here, we have a ColumnInfo structure created
240 // and may or may not have found the table name.
241 if (tableName != null) { // We found the table
242 // If we have the table name, we can provide proper metadata
243 // from the DSA's own configuration
244 cols[i-1] = interpreter.getColumn(null, tableName, columnName);
245 cols[i-1].setId(tableName+"."+columnName);
246 cols[i-1].setUcd(interpreter.getColumn(
247 null, tableName, columnName).getUcd("1"),"1");
248 cols[i-1].setUnits(interpreter.getColumn(
249 null, tableName, columnName).getUnits());
250 cols[i-1].setPublicType(interpreter.getColumn(
251 null, tableName, columnName).getPublicType());
252 }
253 // Now try to add some final JDBC metadata
254 //read direct from sql metadata
255 cols[i-1].setBackType(""+metadata.getColumnType(i));
256 try {
257 //read from sql metadata and convert
258 //(some dbs don't implement this function)
259 cols[i-1].setJavaType(Class.forName(
260 metadata.getColumnClassName(i)));
261 }
262 catch (ClassNotFoundException cnfe) {
263 log.error(cnfe+" for column "+columnName,cnfe);
264 }
265 catch (SQLException se) {
266 //log but carry on; eg postgres drivers don't seem to
267 //have implemented getColumnClassName
268 log.debug(se+" trying to get column class name for column "+
269 columnName);
270 }
271 }
272 // Now that we have the metadata, we can start processing the table.
273 tableWriter.startTable(cols);
274
275 int row = 0;
276 statusToUpdate.newProgress("Processing Row", getCount());
277 String[] colValues = new String[numCols];
278 while (sqlResults.next() && ((queryLimit <=0) || (row<=queryLimit)))
279 {
280 row++;
281 statusToUpdate.setProgress(row);
282
283 for (int i=1;i<=numCols;i++)
284 {
285 if (cols[i-1] != null) { //if there is no column metadata,
286 //then it wasn't found in the metadoc and shouldn't be displayed
287 try {
288 colValues[i-1] = sqlResults.getString(i);
289 }
290 catch (SQLException se) {
291 log.error(se+" reading value of column "+i+" row "+row,se);
292 colValues[i-1] = se.toString();
293 }
294 catch (Exception se) {
295 log.error(se+" reading value of column "+i+" row "+row,se);
296 colValues[i-1] = se.toString();
297 }
298 }
299 }
300 tableWriter.writeRow(colValues);
301
302 //a different check to the 'natural' queryLimit check in the while loop. If the results hit the
303 //users limit, that's fine. If it hits the datacenter limit, we need to make sure the user is informed
304 if ((localLimit>0) && (row>localLimit)) {
305 statusToUpdate.addDetail("Results limited to "+localLimit+" rows by datacenter");
306 log.warn("Limiting returned results to "+localLimit);
307 tableWriter.writeRow(new String[] {"Limited to "+localLimit+" by datacenter"});
308 break;
309 }
310
311 if (querier.isAborted()) {
312 tableWriter.abort();
313 querier.setResultsSize(row);
314 return;
315 }
316 }
317 tableWriter.endTable();
318
319 statusToUpdate.addDetail(row+" rows sent");
320 statusToUpdate.clearProgress();
321
322 querier.setResultsSize(row);
323
324 tableWriter.close();
325 }
326 catch (SQLException sqle)
327 {
328 log.error(sqle+" reading results",sqle);
329 throw new DatacenterException(sqle+", reading results", sqle);
330 }
331
332 //don't close sqlResults - seems to cause the results to cycle through
333 }
334
335
336 public String getUcdFor(String columnName)
337 {
338 //return RdbmsResourceReader.getUcdOf();
339 return "unknown";
340 }
341
342 /*** Returns the formats that this plugin can provide. Doesn't provide Raw */
343 public static String[] listFormats() {
344 return new String[] { ReturnTable.VOTABLE, ReturnTable.CSV, ReturnTable.HTML } ;
345 }
346 }
347
348 /*
349 $Log: SqlResults.java,v $
350 Revision 1.11 2006/08/21 15:39:30 clq2
351 PAL_KEA_1716
352
353 Revision 1.10.6.2 2006/08/18 15:20:46 kea
354 Preparing for final checkin. Added some extra types to DSA metadoc
355 schema, and added test column of type boolean to SampleStars plugin
356 (we can handle boolean data).
357
358 Revision 1.10.6.1 2006/08/17 14:51:29 kea
359 Final checkins for placeholder bugzilla ticket 1716.
360
361 Revision 1.10 2006/06/15 16:50:09 clq2
362 PAL_KEA_1612
363
364 Revision 1.9.64.7 2006/06/13 21:05:17 kea
365 Getting tests working fully after Jeff's new ADQLbeans jar.
366
367 Revision 1.9.64.6 2006/05/05 09:31:04 kea
368 Syntax translation testing functionality, HSQLDB upgrade.
369
370 Revision 1.9.64.5 2006/04/27 12:52:19 kea
371 Added harness for populating, translating to SQL and running a suite of
372 templated test queries in the live DSA. (Good for syntax checking of
373 SQL translation against multiple DBMSs).
374
375 Revision 1.9.64.4 2006/04/25 15:37:25 kea
376 Fixing unit tests, except conesearch-related ones (not implemented yet).
377
378 Revision 1.9.64.3 2006/04/21 12:10:37 kea
379 Renamed ReturnSimple back to ReturnTable (since it is indeed intended
380 for returning tables).
381
382 Revision 1.9.64.2 2006/04/20 15:08:28 kea
383 More moving sideways.
384
385 Revision 1.9.64.1 2006/04/19 13:57:32 kea
386 Interim checkin. All source is now compiling, using the new Query model
387 where possible (some legacy classes are still using OldQuery). Unit
388 tests are broken. Next step is to move the legacy classes sideways out
389 of the active tree.
390
391 Revision 1.9 2005/05/27 16:21:04 clq2
392 mchv_1
393
394 Revision 1.8.10.4 2005/05/13 16:56:32 mch
395 'some changes'
396
397 Revision 1.8.10.3 2005/05/04 10:24:33 mch
398 fixes to tests
399
400 Revision 1.8.10.2 2005/05/03 19:35:01 mch
401 fixes to tests
402
403 Revision 1.8.10.1 2005/04/29 16:55:47 mch
404 prep for type-fix for postgres
405
406 Revision 1.8 2005/03/31 12:10:28 mch
407 Fixes and workarounds for null values, misisng metadoc columns
408
409 Revision 1.7 2005/03/30 18:25:45 mch
410 fix for sql-server jdbc problem
411
412 Revision 1.6 2005/03/30 16:07:00 mch
413 debug etc for bad sql types
414
415 Revision 1.5 2005/03/30 15:52:15 mch
416 debug etc for bad sql types
417
418 Revision 1.4 2005/03/30 15:18:55 mch
419 debug etc for bad sql types
420
421 Revision 1.3 2005/03/21 18:45:55 mch
422 Naughty big lump of changes
423
424 Revision 1.2 2005/03/10 22:39:17 mch
425 Fixed tests more metadata fixes
426
427
428 */
429
430
431
432
433
434
435
436
437