View Javadoc

1   /*
2    * $Id: SqlResults.java,v 1.15 2004/11/22 14:43:21 jdt Exp $
3    *
4    * (C) Copyright Astrogrid...
5    */
6   
7   package org.astrogrid.datacenter.queriers.sql;
8   
9   import java.io.BufferedWriter;
10  import java.io.IOException;
11  import java.io.PrintWriter;
12  import java.io.Writer;
13  import java.sql.ResultSet;
14  import java.sql.ResultSetMetaData;
15  import java.sql.SQLException;
16  import org.apache.commons.logging.Log;
17  import org.astrogrid.config.SimpleConfig;
18  import org.astrogrid.datacenter.queriers.Querier;
19  import org.astrogrid.datacenter.queriers.QueryResults;
20  import org.astrogrid.datacenter.queriers.status.QuerierProcessingResults;
21  import org.astrogrid.datacenter.queriers.status.QuerierStatus;
22  import org.astrogrid.datacenter.query.Query;
23  import org.astrogrid.datacenter.returns.ReturnTable;
24  
25  /***
26   * Implementation of <tt>QueryResults</tt> as a wrapper around a <tt>ResultSet</tt>
27   *
28   * <p>Can be used (I believe) for any
29   * SQL/JDBC query results.
30   *
31   * @author M Hill
32   */
33  
34  public class SqlResults extends QueryResults {
35     
36     protected ResultSet sqlResults;
37     protected static final Log log = org.apache.commons.logging.LogFactory.getLog(SqlResults.class);
38     
39  //   public final static int DEFAULT_MAX_RETURN_ROWS = -1;
40     
41     /*** Key used to define maximum number of rows allowed - defaults to 200, -1 = any */
42  //   public final static String MAX_RETURN_ROWS_KEY = "datacenter.sql.maxreturn";
43  
44     /***
45      * Construct this wrapper around the given JDBC/SQL ResultSet.  We don't
46      * know how big this result set will be, so it's likely we'll need a workspace
47      * for any temporary files created when doing conversions
48      */
49     public SqlResults(Querier parentQuerier, ResultSet givenResults)
50     {
51        super(parentQuerier);
52        this.sqlResults = givenResults;
53        
54     }
55  
56     /*** Returns number of rows */
57     public int getCount() {
58        return -1;
59        /*
60        try {
61           if (sqlResults.last() == false) return 0;  //disturbs the cursor for votable conversion...
62           return sqlResults.getRow();
63        }
64        catch (SQLException sqle)  { //may be an unsupported operation
65           log.error(sqle);
66           return -1;
67        }
68         */
69     }
70     
71     /***
72      * Converts results to VOTable to given writer.  All done by hand :-(
73      */
74     public void writeVotable(Writer out, QuerierProcessingResults statusToUpdate) throws IOException
75     {
76        assert (out != null);
77        
78        long localLimit = SimpleConfig.getSingleton().getInt(Query.MAX_RETURN_KEY, -1);
79        long queryLimit = querier.getQuery().getLimit();
80        
81       
82        try
83        {
84           PrintWriter printOut = new PrintWriter(new BufferedWriter(out));
85           
86  //         printOut.println("<!DOCTYPE VOTABLE SYSTEM 'http://us-vo.org/xml/VOTable.dtd'>");
87           printOut.println("<VOTABLE version='1.0'>");
88           
89           /* don't know where to find this info - in the netadata I expect
90            <DEFINITIONS>
91            <COOSYS ID="myJ2000" system="eq_FK5" equinox="2000." epoch="2000."/>
92            </DEFINITIONS>
93            */
94           
95           printOut.println("<RESOURCE>");
96           /* don't know where to find this info - in the netadata I expect
97            <PARAM ID="RA" datatype="E" value="200.0"/>
98            <PARAM ID="DE" datatype="E" value="40.0"/>
99            <PARAM ID="SR" datatype="E" value="30.0"/>
100           <PARAM ID="PositionalError" datatype="E" value="0.1"/>
101           <PARAM ID="Credit" datatype="A" arraysize="*" value="Charles Messier, Richard Gelderman"/>
102           */
103          printOut.println("<TABLE>");
104          
105          //list columns
106          ResultSetMetaData metadata = sqlResults.getMetaData();
107          
108          int cols = metadata.getColumnCount();
109          for (int i=1;i<=cols;i++)
110          {
111             String tablename = "";
112             
113             //nb some drivers do not provide this function, and it is often poorly implemented
114 //             if (metadata.getTableName(i).length() >0) {
115 //                tablename = metadata.getTableName(i)+".";
116 //             }
117             printOut.println("<FIELD ID='"+tablename+metadata.getColumnName(i)+"' "
118                                 +" name='"+metadata.getColumnLabel(i)+"' "
119                                 +RdbmsResourceGenerator.getVotableTypeAttr(sqlResults.getMetaData().getColumnType(i))
120                                 +" ucd='"+getUcdFor(metadata.getColumnName(i))+"' "
121                                 +"/>");
122          }
123 
124          printOut.flush(); //so something comes back to the browser quickly
125          printOut.println("<DATA>");
126          printOut.println("<TABLEDATA>");
127 
128          String note = statusToUpdate.getMessage();
129          
130 //         sqlResults.beforeFirst();
131          int row = 0;
132          statusToUpdate.newProgress("Processing Row", getCount());
133          while (sqlResults.next() && ((queryLimit == -1) || (row<=queryLimit)))
134          {
135             row++;
136             statusToUpdate.setProgress(row);
137 
138             printOut.print("  <TR> ");
139             for (int i=1;i<=cols;i++)
140             {
141                printOut.print("<TD>"+sqlResults.getString(i)+"</TD>");
142             }
143             printOut.println(" </TR>");
144             
145             if ((localLimit!=-1) && (row>localLimit)) {
146                //deliberately don't finish table properly
147                statusToUpdate.addDetail("Results limited to "+localLimit+" rows by datacenter");
148                log.warn("Limiting returned results to "+localLimit);
149                break;
150             }
151             
152             if (querier.getStatus().getStage().equals(QuerierStatus.ABORTED)) {
153                printOut.println(" ------------------ Querier Aborted ----------------- ");
154                return;
155             }
156             printOut.flush(); //so we see each row as it arrives on the browser
157          }
158          statusToUpdate.addDetail(row+" rows sent");
159          statusToUpdate.clearProgress();
160          
161          printOut.println("</TABLEDATA>");
162          printOut.println("</DATA>");
163          
164          printOut.println("</TABLE>");
165          
166          printOut.println("</RESOURCE>");
167          
168          printOut.println("</VOTABLE>");
169          
170          printOut.flush();
171       }
172       catch (SQLException sqle)
173       {
174          log.error("Could not convert results",sqle);
175          throw new IOException(sqle+", converting to VOtable");
176       }
177       
178       //don't close sqlResults - seems to cause the results to cycle through
179    }
180 
181    /***
182     * Converts results to HTML to given writer
183     */
184    public void writeHtml(Writer out, QuerierProcessingResults statusToUpdate) throws IOException
185    {
186       assert (out != null);
187       
188       long localLimit = SimpleConfig.getSingleton().getInt(Query.MAX_RETURN_KEY, -1);
189       long queryLimit = querier.getQuery().getLimit();
190       
191       try
192       {
193          PrintWriter printOut = new PrintWriter(new BufferedWriter(out));
194          
195          printOut.println("<HTML>");
196          
197          printOut.println("<HEAD>");
198          printOut.println("<TITLE>Query Results</TITLE>");
199          printOut.println("</HEAD>");
200 
201          printOut.println("<BODY>");
202          
203          printOut.println("<TABLE>");
204          
205          //list columns
206          ResultSetMetaData metadata = sqlResults.getMetaData();
207          
208          int cols = metadata.getColumnCount();
209          printOut.println("</TR>");
210          for (int i=1;i<=cols;i++)
211          {
212             printOut.print("<TH>"+metadata.getColumnName(i)+"</TH>");
213          }
214          printOut.println("</TR>");
215 
216          int row = 0;
217          statusToUpdate.newProgress("Processing Row", getCount());
218          while (sqlResults.next() && ((queryLimit == -1) || (row<=queryLimit)))
219          {
220             row++;
221             statusToUpdate.setProgress(row);
222 
223             printOut.println("<TR>");
224             for (int i=1;i<=cols;i++)
225             {
226                printOut.println("<TD>"+sqlResults.getString(i)+"</TD>");
227             }
228             printOut.println("</TR>");
229 
230             //a different check to the 'natural' queryLimit check in the while loop. If the results hit the
231             //users limit, that's fine.  If it hits the datacenter limit, we need to make sure the user is informed
232             if ((localLimit!=-1) && (row>localLimit)) {
233                statusToUpdate.addDetail("Results limited to "+localLimit+" rows by datacenter");
234                log.warn("Limiting returned results to "+localLimit);
235                break;
236             }
237 
238             if (querier.getStatus().getStage().equals(QuerierStatus.ABORTED)) {
239                printOut.println(" ------------------ Querier Aborted ----------------- ");
240                return;
241             }
242          }
243          statusToUpdate.addDetail(row+" rows sent");
244          statusToUpdate.clearProgress();
245          
246          printOut.println("</TABLE>");
247          printOut.println("</BODY>");
248          
249          printOut.println("</HTML>");
250          printOut.flush();
251       }
252       catch (SQLException sqle)
253       {
254          log.error("Could not convert results",sqle);
255          throw new IOException(sqle+", converting to Html");
256       }
257       
258       //don't close sqlResults - seems to cause the results to cycle through
259    }
260    
261    /***
262     * Converts results to CSV to given writer
263     */
264    public void writeCSV(Writer out, QuerierProcessingResults statusToUpdate) throws IOException
265    {
266       long maxAllowed = SimpleConfig.getSingleton().getInt(Query.MAX_RETURN_KEY, -1);
267       long queryLimit = querier.getQuery().getLimit();
268       
269       try
270       {
271          PrintWriter printOut = new PrintWriter(new BufferedWriter(out));
272          
273          //list columns
274          ResultSetMetaData metadata = sqlResults.getMetaData();
275          
276          int cols = metadata.getColumnCount();
277          for (int i=1;i<=cols;i++)
278          {
279             printOut.print(/* sometimes not implemented metadata.getTableName(i)+"."+ */ metadata.getColumnName(i));
280             if (i<cols) {  printOut.print(", "); }
281          }
282 
283          printOut.println();
284 
285          String note = statusToUpdate.getMessage();
286 
287          int row = 0;
288          int maxRow = getCount();
289          String ofMax = " of "+maxRow;
290          if (maxRow == -1) ofMax = "";
291          while (sqlResults.next() && ((queryLimit == -1) || (row<=queryLimit)))
292          {
293             row++;
294             statusToUpdate.setMessage(note+"\nProcessing Row "+row+ofMax);
295 
296             for (int c=1;c<=cols;c++)
297             {
298                printOut.print(sqlResults.getString(c));
299                if (c<cols) { printOut.print(", "); }
300             }
301             printOut.println();
302             
303             if ((maxAllowed!=-1) && (row>maxAllowed)) {
304                statusToUpdate.addDetail("Results limited to "+maxAllowed+" rows by datacenter");
305                log.warn("Limiting returned results to "+maxAllowed);
306                printOut.println("------------- Results Limited to "+maxAllowed+" ------------");
307                break;
308             }
309             
310             if (querier.getStatus().getStage().equals(QuerierStatus.ABORTED)) {
311                printOut.println(" ------------------ Querier Aborted ----------------- ");
312                return;
313             }
314          }
315 
316          statusToUpdate.addDetail(row+" rows sent");
317          statusToUpdate.clearProgress();
318          
319          printOut.flush();
320       }
321       catch (SQLException sqle)
322       {
323          log.error("Could not convert results",sqle);
324          throw new IOException(sqle+", converting to CSV");
325       }
326    }
327    
328    /*** Native form is/will be webrowresults (or whatever it's called)
329     */
330    public void writeRaw(Writer out, QuerierProcessingResults statusToUpdate) throws IOException
331    {
332       throw new UnsupportedOperationException("Raw (WebRowXml) not yet supported");
333    }
334    
335    public String getUcdFor(String columnName)
336    {
337       return "unknown";
338    }
339    
340    /*** Returns the formats that this plugin can provide.  Doesn't provide Raw */
341    public static String[] getFormats() {
342       return new String[] { ReturnTable.VOTABLE, ReturnTable.CSV, ReturnTable.HTML } ;
343    }
344    
345    
346    
347 }
348 
349 /*
350  $Log: SqlResults.java,v $
351  Revision 1.15  2004/11/22 14:43:21  jdt
352  Restored Martin's changes from PAL_MCHJDT_705
353 
354  Revision 1.13  2004/11/12 13:49:12  mch
355  Fix where keyword maker might not have had keywords made
356 
357  Revision 1.12  2004/11/08 16:15:50  mch
358  added flush to each row
359 
360  Revision 1.11  2004/11/08 15:04:15  mch
361  cleared progress once complete
362 
363  Revision 1.10  2004/11/05 12:26:49  mch
364  Renamed RdbmsResourcePlugin
365 
366  Revision 1.9  2004/11/03 01:35:18  mch
367  PAL_MCH_Candidate2 merge Part II
368 
369  Revision 1.8.6.2  2004/11/01 16:01:25  mch
370  removed unnecessary getLocalLimit parameter, and added check for abort in sqlResults
371 
372  Revision 1.8.6.1  2004/10/27 00:43:39  mch
373  Started adding getCount, some resource fixes, some jsps
374 
375  Revision 1.8  2004/10/18 13:11:30  mch
376  Lumpy Merge
377 
378  Revision 1.7.2.1  2004/10/15 19:59:06  mch
379  Lots of changes during trip to CDS to improve int test pass rate
380 
381  Revision 1.7  2004/10/08 14:07:02  mch
382  Fixed max limit bug
383 
384  Revision 1.6  2004/10/08 14:02:14  mch
385  Fixed max limit bug
386 
387  Revision 1.5  2004/10/08 09:42:58  mch
388  Added limit checks
389 
390  Revision 1.4  2004/10/06 21:51:53  mch
391  Fixed too many commas in header
392 
393  Revision 1.3  2004/10/05 20:26:43  mch
394  Prepared for better resource metadata generators
395 
396  Revision 1.2  2004/10/01 18:04:58  mch
397  Some factoring out of status stuff, added monitor page
398 
399  Revision 1.1  2004/09/28 15:02:13  mch
400  Merged PAL and server packages
401 
402  Revision 1.37  2004/09/08 20:42:48  mch
403  flush added
404 
405  Revision 1.36  2004/09/07 00:54:20  mch
406  Tidied up Querier/Plugin/Results, and removed deprecated SPI-visitor-SQL-translator
407 
408  Revision 1.35  2004/09/01 21:37:59  mch
409  Fixes for Servlets, more servlets and better and nicer status reports
410 
411  Revision 1.34  2004/09/01 12:10:58  mch
412  added results.toHtml
413 
414  Revision 1.33  2004/08/27 17:47:19  mch
415  Added first servlet; started making more use of ReturnSpec
416 
417  Revision 1.32  2004/08/27 11:20:43  mch
418  Fix to remove comma at end of lines in a CSV
419 
420  Revision 1.31  2004/08/18 18:44:12  mch
421  Created metadata plugin service and added helper methods
422 
423  Revision 1.30  2004/08/04 09:27:03  mch
424  Added metadata, converted sql data type to VOTable data type
425 
426  Revision 1.29  2004/07/03 16:53:11  mch
427  Removed getTableName() which is not always implemented
428 
429  Revision 1.28  2004/07/01 23:07:14  mch
430  Introduced metadata generator
431 
432  Revision 1.27  2004/03/18 00:31:33  mch
433  Added adql 7.3.1 tests and max row information to status
434 
435  Revision 1.26  2004/03/16 17:05:38  mch
436  Prettified of max
437 
438  Revision 1.25  2004/03/16 16:19:51  mch
439  Fix to limits stopping everything...
440 
441  Revision 1.24  2004/03/15 21:44:54  mch
442  Better note update
443 
444  Revision 1.23  2004/03/15 21:31:40  mch
445  Added limit warning to querier status
446 
447  Revision 1.22  2004/03/15 20:45:01  mch
448  Added warning when limiting results
449 
450  Revision 1.21  2004/03/15 20:39:31  mch
451  Changed default to any - will initial default in default properties
452 
453  Revision 1.20  2004/03/15 20:38:54  mch
454  Added max row limit
455 
456  Revision 1.19  2004/03/15 19:16:12  mch
457  Lots of fixes to status updates
458 
459  Revision 1.18  2004/03/15 11:25:35  mch
460  Fixes to emailer and JSP targetting
461 
462  Revision 1.17  2004/03/14 16:55:48  mch
463  Added XSLT ADQL->SQL support
464 
465  Revision 1.16  2004/03/14 02:17:07  mch
466  Added CVS format and emailer
467 
468  Revision 1.15  2004/03/12 04:45:26  mch
469  It05 MCH Refactor
470 
471  Revision 1.12  2004/03/10 23:09:59  mch
472  Fixed unknown sql type stopping query
473 
474  Revision 1.11  2004/03/10 02:32:01  mch
475  Removed getCount attempt
476 
477  Revision 1.10  2004/03/10 00:46:00  mch
478  catch unsupported operation
479 
480  Revision 1.9  2004/03/09 22:58:39  mch
481  Provided for piping/writing out of results rather than returning as string
482 
483  Revision 1.8  2004/03/09 21:54:58  mch
484  Added Writer methods to toVotables for JSPs
485 
486  Revision 1.7  2004/03/09 18:50:06  mch
487  Fixed workspace used when closed
488 
489  Revision 1.6  2004/03/08 00:31:28  mch
490  Split out webservice implementations for versioning
491 
492  Revision 1.5  2004/01/15 14:49:47  nw
493  improved documentation
494 
495  Revision 1.4  2003/11/27 00:52:58  nw
496  refactored to introduce plugin-back end and translator maps.
497  interfaces in place. still broken code in places.
498 
499  Revision 1.3  2003/11/21 17:37:56  nw
500  made a start tidying up the server.
501  reduced the number of failing tests
502  found commented out code
503 
504  Revision 1.2  2003/11/18 11:10:16  mch
505  Removed client dependencies on server
506 
507  Revision 1.1  2003/11/14 00:38:29  mch
508  Code restructure
509 
510  Revision 1.6  2003/10/02 12:53:49  mch
511  It03-Close
512 
513  */
514 
515 
516 
517 
518 
519