View Javadoc

1   /*
2    * $Id: StdSqlWriter.java,v 1.7 2005/04/01 10:33:52 mch Exp $
3    *
4    * (C) Copyright Astrogrid...
5    */
6   
7   package org.astrogrid.tableserver.jdbc;
8   
9   import org.astrogrid.query.condition.*;
10  
11  import java.io.IOException;
12  import java.text.DecimalFormat;
13  import javax.xml.parsers.ParserConfigurationException;
14  import org.apache.commons.logging.Log;
15  import org.apache.commons.logging.LogFactory;
16  import org.astrogrid.cfg.ConfigException;
17  import org.astrogrid.dataservice.metadata.queryable.ConeConfigQueryableResource;
18  import org.astrogrid.dataservice.metadata.queryable.QueryableResourceReader;
19  import org.astrogrid.dataservice.metadata.queryable.SearchField;
20  import org.astrogrid.dataservice.metadata.queryable.SearchGroup;
21  import org.astrogrid.query.Query;
22  import org.astrogrid.query.QueryVisitor;
23  import org.astrogrid.query.adql.Adql074Writer;
24  import org.astrogrid.query.adql.AdqlXml074Parser;
25  import org.astrogrid.query.returns.ReturnSpec;
26  import org.astrogrid.query.returns.ReturnTable;
27  import org.astrogrid.query.sql.SqlParser;
28  import org.astrogrid.geom.Angle;
29  import org.astrogrid.units.Units;
30  import org.xml.sax.SAXException;
31  
32  
33  /***
34   * For writing out Querys as SQL statment strings, as close as we can to 'standard SQL'.
35   */
36  
37  public class StdSqlWriter implements QueryVisitor {
38     
39     
40  
41     protected static Log log = LogFactory.getLog(Adql074Writer.class);
42     
43     private DecimalFormat longDec = new DecimalFormat("#############################0");
44  
45     /*** SQL String components being built */
46     protected StringBuffer select = new StringBuffer();
47     protected StringBuffer from = new StringBuffer();
48     protected StringBuffer where = new StringBuffer();
49     protected StringBuffer orderby = new StringBuffer();
50     
51     /* Because the methods are stateless, we have to set which stringbuffer we
52      * are working on - this is needed for visiting eg column references which
53      * appear in seleft, where, order by, etc */
54     protected StringBuffer current = null;
55     
56     /*** Query being written */
57     Query query = null;
58     
59     /*** Root write call */
60     public void visitQuery(Query queryToWrite)  throws IOException  {
61        query = queryToWrite;
62        log.debug("Making SQL from "+query.toString());
63        
64        //--- SELECT, ORDER BY ---
65        visitReturnSpec(query.getResultsDef());
66        visitLimit(query.getLocalLimit());
67        
68        //-- FROM ---
69        if (query.getScope() != null) {
70           
71           visitScope(query.getScope());
72        }
73  
74        //-- WHERE --
75        if (query.getCriteria() != null) {
76        
77           current = where;
78           query.getCriteria().acceptVisitor(this);
79        }
80        
81     }
82  
83     public String getSql() {
84        String sql = "SELECT "+select.toString()+" FROM "+from.toString();
85        if (where.toString().trim().length()>0) {
86           sql = sql +" WHERE "+where.toString();
87        }
88        if (orderby.toString().trim().length()>0) {
89           sql = sql + " ORDER BY "+orderby.toString();
90        }
91        return sql;
92     }
93     
94     public void visitScope(String[] scope) {
95  
96        current = from;
97  
98        // we just duplicate alias names as table names for now
99        for (int i = 0; i < scope.length; i++) {
100       /*
101          String alias = scope[i];
102          if (query.getAlias(alias) != null) {
103             alias = query.getAlias(alias);
104          }
105          sql.append(" "+query.getScope()[i]+" as "+alias+" ");
106        */
107          from.append(" "+scope[i]+" ");
108          if (i<scope.length-1) {
109             from.append(",");
110          }
111       }
112    }
113 
114    public void visitReturnSpec(ReturnSpec spec) throws IOException  {
115  
116       current = select; //so called visitors know what we're visiting
117       
118       if (spec instanceof ReturnTable) {
119          if (((ReturnTable) spec).getColDefs()==null)  {
120             current.append(" * ");
121          }
122          else {
123             Expression[] colDefs = ((ReturnTable) spec).getColDefs();
124    
125             for (int i = 0; i < colDefs.length; i++) {
126                colDefs[i].acceptVisitor(this);
127                if (i<colDefs.length-1) {
128                   current.append(", ");
129                }
130             }
131          }
132          
133          if (((ReturnTable) spec).getSortOrder() != null) {
134             current.append(" ORDER BY ");
135             Expression[] sortCols = ((ReturnTable) spec).getSortOrder();
136    
137             for (int i = 0; i < sortCols.length; i++) {
138                sortCols[i].acceptVisitor(this);
139                if (i<sortCols.length-1) {
140                   current.append(", ");
141                }
142             }
143          }
144       }
145    }
146 
147    /*** Standard SQL can't handle this, so we leave it blank */
148    public void visitLimit(long limit) {
149    }
150    
151    public void visitIntersection(Intersection expression)  throws IOException {
152          current.append(" (");
153          Condition[] conditions = expression.getConditions();
154          for (int i = 0; i < conditions.length; i++) {
155             conditions[i].acceptVisitor(this);
156             if (i<conditions.length-1) {
157                current.append(" AND ");
158             }
159          }
160          current.append(") ");
161     }
162     
163     public void visitUnion(Union expression)  throws IOException {
164          current.append(" (");
165          Condition[] conditions = ((Union) expression).getConditions();
166          for (int i = 0; i < conditions.length; i++) {
167             conditions[i].acceptVisitor(this);
168             if (i<conditions.length-1) {
169                current.append(" OR ");
170             }
171          }
172          current.append(") ");
173     }
174 
175       
176    public void visitNumericComparison(NumericComparison expression)  throws IOException {
177 
178        expression.getLHS().acceptVisitor(this);
179        current.append( expression.getOperator().toString());
180        expression.getRHS().acceptVisitor(this);
181    }
182 
183    public void visitStringComparison(StringComparison expression)  throws IOException {
184       
185       expression.getLHS().acceptVisitor(this);
186       current.append( expression.getOperator().toString());
187       expression.getRHS().acceptVisitor(this);
188    }
189    
190    public void visitNumber(LiteralNumber expression)  {
191       current.append( " ("+expression.getValue()+") ");  //surround with brackets as negative numbers sometimes don't work very well without
192    }
193 
194    public void visitAngle(LiteralAngle expression)  {
195       current.append( " ("+expression.getAngle().asDegrees()+") ");
196    }
197 
198    public void visitDate(LiteralDate date) throws IOException {
199       current.append( " "+date.getDate()+" ");
200    }
201    
202    public void visitString(LiteralString string) {
203       current.append( " '"+string.getValue()+"' ");
204    }
205 
206    public void visitRawSearchField(RawSearchField field)  {
207       current.append( " "+field.getField()+" "); //bit of a botch - should throw an exception?
208    }
209 
210    public void visitMath(MathExpression math)   throws IOException {
211          math.getLHS().acceptVisitor(this);
212          current.append( " "+math.getOperator()+" ");
213          math.getRHS().acceptVisitor(this);
214    }
215    
216    public void visitFunction(Function function)  throws IOException {
217       
218       if (function.getName().trim().toUpperCase().equals(CircleCondition.NAME.toUpperCase())) {
219          visitCircle(CircleCondition.makeCircle(function));
220          return;
221       }
222       
223       current.append(" "+function.getName()+"(");
224       
225       for (int i = 0; i < function.getArgs().length; i++) {
226          ((Expression) function.getArg(i)).acceptVisitor(this);
227          if (i<function.getArgs().length-1) {
228             current.append(", ");
229          }
230       }
231       current.append(")");
232    }
233    
234    /*** Writes out the ADQL tag for the given column as a child of the given parentTag with
235     * the given elementName */
236    public void visitColumnReference(ColumnReference colRef) {
237 
238       current.append(" ");
239       String tableName = colRef.getTableName();
240       //replace with alias is there is one
241 //no don't - just use full table name      if (query.getAlias(tableName) != null) {
242 //         tableName = query.getAlias(tableName);
243 //      }
244 
245       
246       if ((colRef.getDatasetName() != null) && (colRef.getDatasetName().trim().length()>0)) {
247          current.append(colRef.getDatasetName()+":");
248       }
249       if ((tableName != null) && (tableName.trim().length()>0)) {
250          current.append(tableName+".");
251       }
252 
253       current.append(colRef.getColName()+" ");
254    }
255    
256    /*** might be overridden? **/
257    public void visitCircle(CircleCondition circleFunc) throws IOException {
258 
259       Angle ra;
260       Angle dec;
261       Angle radius;
262       
263       try {
264          ra = ((LiteralAngle) circleFunc.getArg(1)).getAngle();
265          dec = ((LiteralAngle) circleFunc.getArg(2)).getAngle();
266    
267          radius = ((LiteralAngle) circleFunc.getArg(3)).getAngle();
268       }
269       catch (ClassCastException cce) {
270          //assume it's the circleFunc args
271          throw new UnsupportedOperationException("CIRCLE arguments must be LiteralAngles ("+cce+")");
272       }
273 
274       ConeConfigQueryableResource queryable = new ConeConfigQueryableResource();
275 
276       //go through spatial groups checking against scope looking to see if any of them have any spatial fields
277       SearchGroup[] tables = queryable.getSpatialGroups();
278       boolean spatialFound = false; //marker that we've found at least one to search on
279       for (int i = 0; i < tables.length; i++)
280       {
281          //is this table in scope?
282          for (int j = 0; j < query.getScope().length; j++)
283          {
284             if (tables[i].getId().equals(query.getScope()[j])) {
285                if (spatialFound) { //a previous one was found, so add an OR to search this one too
286                   current.append(" OR ");
287                }
288                spatialFound = true; // found one
289                SearchField[] cols = queryable.getSpatialFields(tables[i]);
290 
291                //assume, for the moment, that we get two columns, the first is RA and the
292                //second is DEC
293                current.append(makeSqlCircleCondition(cols[0], cols[1], ra, dec, radius));
294             }
295          }
296       }
297    }
298    
299    /*** Returns the SQL condition expression for a circle.  This circle is
300     * 'flat' on the sphere, when vieweing the sphere from the center.  ie, the
301     * radius is a declination angle from the given RA & DEC point.  This means
302     * the circle is distorted in coordinate space
303     */
304    public String makeSqlCircleCondition(SearchField raCol, SearchField decCol, Angle ra, Angle dec, Angle radius) {
305       //Some db functions take radians, some degrees.  Fail if the config is not
306       //specified, so we force people to get it right...
307       boolean funcsInRads = true; //ConfigFactory.getCommonConfig().getBoolean(SqlMaker.DB_TRIGFUNCS_IN_RADIANS);
308       
309       String raColRad = makeColumnRadiansId(raCol);
310       String decColRad = makeColumnRadiansId(decCol);
311       
312       //naively, we could use the 'least squares' distance (pythagoros) to see if
313       //the objects are within radius distance. However this doesn't work well
314       //except very near the equator, and is useless over the poles.
315       
316       
317       //'haversine' distance formulae.  The correct one to use...
318       if (funcsInRads) {
319          return
320             /* @todo - note that the (float) typecasts are for postgres that can't cope with subtracting double precisions from single */
321             makeSqlBoundsCondition(raCol, decCol, ra, dec, radius) + " AND "+
322             "("+
323             "(2 * ASIN( SQRT( "+
324             //surround dec with extra brackets in order to cope with negative decs
325             "POWER( SIN( ("+decColRad+" - ("+ (float) dec.asRadians()+") ) / 2 ) ,2) + "+
326             "COS("+dec.asRadians()+") * COS("+decColRad+") * "+
327             "POWER( SIN( ("+raColRad+" - "+(float) ra.asRadians()+") / 2 ), 2) "+
328             "))) < "+(float) radius.asRadians()+
329             ")";
330       }
331       else {
332          throw new UnsupportedOperationException("Not done degree funcs yet - do they exist?");
333       }
334       /***/
335    }
336 
337    /*** Returns a SQL condition expression to 'bound' a given circle. Otherwise circle queries
338     * query is likely to take a very very long time as it trawls through all the rows calculating
339     * the distance.
340     * <p> At the moment this is just a DEC-binding (which is easier than RA :-)
341     * @param raCol, decCol - the column names that contain the RA & DEC values of the objects
342     */
343     public String makeSqlBoundsCondition(SearchField raCol, SearchField decCol, Angle ra, Angle dec, Angle radius) {
344 
345       String decColSqlId = decCol.getGroup()+"."+decCol.getName();
346       
347       if (dec.asDegrees() - radius.asDegrees() < -90) {
348          //circle includes north pole.  Add bounds as just a dec limit.
349          return "("+decColSqlId+"<"+getAngleInColUnits(Angle.fromRadians(dec.asRadians()+radius.asRadians()), decCol.getUnits())+")";
350       }
351    
352       if (dec.asDegrees() + radius.asDegrees() > +90) {
353          //circle includes south pole. Add bounds as just a dec limit
354          return "("+decColSqlId+">"+getAngleInColUnits(Angle.fromRadians(dec.asRadians()-radius.asRadians()), decCol.getUnits())+")";
355       }
356 
357       //make upper and lower limit
358       return "( ("+decColSqlId+"<"+getAngleInColUnits(Angle.fromRadians(dec.asRadians()+radius.asRadians()), decCol.getUnits())+") "+
359             " and "+
360               " ("+decColSqlId+">"+getAngleInColUnits(Angle.fromRadians(dec.asRadians()-radius.asRadians()), decCol.getUnits())+") )";
361     
362     }
363    
364    
365    /***
366     * Returns the right SQL to translate a conesearch column to radians
367     */
368    public String makeColumnRadiansId(SearchField col) {
369       Units colUnits = col.getUnits();
370        String sqlColId = col.getGroup()+"."+col.getName();
371       
372       if (colUnits.equals("rad")) {
373          return sqlColId;
374       }
375       else if (colUnits.equals("deg")) {
376          return "RADIANS("+sqlColId+")";
377       }
378       else if (colUnits.equals("marcsec")) {
379          return "RADIANS("+sqlColId+"/360000000)";
380       }
381       else {
382          throw new ConfigException("Unknown units '"+colUnits+"' for conesearch columns, only 'rad', 'deg' or 'marcsec' supported");
383       }
384    }
385    
386    /*** Returns the given angle in the column's units.   Useful for making SQL a bit
387     * simpler - convert the angles to the column units rather than vice versa with functions */
388    public String getAngleInColUnits(Angle givenAngle, Units colUnits) {
389       /* @todo - note that the (float) typecasts are for postgres that can't cope with subtracting double precisions from single */
390       if (colUnits.equals("rad")) {
391          return ""+(float) givenAngle.asRadians();
392       }
393       else if (colUnits.equals("deg")) {
394          return ""+(float) givenAngle.asDegrees();
395       }
396       else if (colUnits.equals("marcsec")) {
397          //this gives 2.3E8 etc which can cause confusion return ""+givenAngle.asArcSecs()*1000);
398          //return givenAngle.asArcSecs()+"000"; //...and this is highly dubious, but means we will usually get 230000000 instead
399          return longDec.format(givenAngle.asDegrees()*1000);
400          
401       }
402       else {
403          throw new ConfigException("Unknown units '"+colUnits+"' for conesearch columns, only 'rad', 'deg' or 'marcsec' supported");
404       }
405    }
406    
407    /***
408     * Easy test/debug
409     */
410    public static void main(String[] args) throws IOException, IOException, ParserConfigurationException, SAXException {
411 
412       /*
413       String sql =
414          "SELECT CrossNeighboursEDR.sdssID, ReliableStars.objID, \n"+
415          "       ReliableStars.ra, ReliableStars.dec, \n"+
416          "       ReliableStars.sCorMagR2, ReliableStars.sCorMagI,\n"+
417          "       ReliableStars.sCorMagB, ReliableStars.sigMuD,\n"+
418          "       ReliableStars.sigMuAcosD, ReliableStars.muD,\n"+
419          "       ReliableStars.muAcosD \n"+
420          "FROM ReliableStars, CrossNeighboursEDR \n"+
421          "WHERE (CrossNeighboursEDR.ssaID = ReliableStars.objID) AND \n"+
422          "      (CrossNeighboursEDR.SdssPrimary = 1) AND \n"+
423          "      (CrossNeighboursEDR.sdsstype = 6) AND \n"+
424          "      (ReliableStars.ra >= 0) AND \n"+
425          "      (ReliableStars.ra <= 1) AND \n"+
426          "      (ReliableStars.dec >= 2) AND \n"+
427          "      (ReliableStars.dec <= 3) AND \n"+
428          "      (ReliableStars.sCorMagR2 > -99) AND \n"+
429          "      (ReliableStars.sCorMagI > -99) AND \n"+
430          "      (POWER(muAcosD,2) + POWER(muD,2) > 4 * \nSQRT(POWER(muAcosD * sigMuAcosD,2) + POWER(muD * sigMuD,2)))   \n";
431        */
432       
433       String sql = "select t1.decdeg from dqc as t1 where t1.decdeg > 60.0";
434       
435       Query q = SqlParser.makeQuery(sql);
436       
437       String adql = Adql074Writer.makeAdql(q);
438       
439       System.out.println(adql);
440       
441       q = AdqlXml074Parser.makeQuery(adql);
442 
443       System.out.println(q);
444 
445       StdSqlWriter sqlMaker = new StdSqlWriter();
446       q.acceptVisitor(sqlMaker);
447       
448       String sql2 = sqlMaker.getSql();
449       
450       System.out.println(sql2);
451       
452       //make sure it's reparsable
453       SqlParser.makeQuery(sql2);
454    }
455 }
456 
457 /*
458  $Log: StdSqlWriter.java,v $
459  Revision 1.7  2005/04/01 10:33:52  mch
460  more temporary fixes for postgres
461 
462  Revision 1.6  2005/03/31 23:16:32  mch
463  temp fix for postgres
464 
465  Revision 1.5  2005/03/31 16:09:40  mch
466  Fixes and workarounds for null values, misisng metadoc columns
467 
468  Revision 1.4  2005/03/31 15:06:16  mch
469  Fixes and workarounds for null values, misisng metadoc columns
470 
471  Revision 1.3  2005/03/24 17:50:48  mch
472  Fixed various resource bits
473 
474  Revision 1.2  2005/03/21 18:45:55  mch
475  Naughty big lump of changes
476 
477  Revision 1.1  2005/03/10 16:42:55  mch
478  Split fits, sql and xdb
479 
480  Revision 1.2  2005/03/10 13:49:52  mch
481  Updating metadata
482 
483  Revision 1.1.1.1  2005/02/17 18:37:35  mch
484  Initial checkin
485 
486  Revision 1.1  2005/02/17 18:17:46  mch
487  Moved SqlWriters back into server as they need metadata information
488 
489  Revision 1.1.1.1  2005/02/16 17:11:23  mch
490  Initial checkin
491 
492  Revision 1.1.2.3  2004/12/20 17:14:15  mch
493  Fixes to making cone searches form Queryable
494 
495  Revision 1.1.2.2  2004/12/10 12:37:13  mch
496  Cone searches to look in metadata, lots of metadata interpreterrs
497 
498  Revision 1.1.2.1  2004/12/08 23:23:37  mch
499  Made SqlWriter and AdqlWriter implement QueryVisitor
500 
501  Revision 1.1.2.1  2004/12/08 18:36:40  mch
502  Added Vizier, rationalised SqlWriters etc, separated out TableResults from QueryResults
503 
504 
505  */
506 
507 
508 
509