Select Git revision
DbPSearch.java
DbPSearch.java 11.54 KiB
import java.util.logging.Logger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
/* import javax.sql.*; needed if using DataSource instead of DriverManager for DB-connections */
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLClassLoader;
import java.util.Enumeration;
import java.util.List;
import java.util.ArrayList;
import java.lang.ClassNotFoundException;
import vo.parameter.*;
public class DbPSearch
{
private static final Logger LOGGER = Logger.getLogger(DbPSearch.class.getName());
private DBConn dbConn;
DbPSearch(DBConn dbConn)
{
this.dbConn = dbConn;
}
public String[] queryOverlapingPubdid(Coord coord)
{
LOGGER.info("trace");
String inputRegion = toPgSphereSqlTypeString(coord.pos);
String dbRegion = toRegionColumnName(coord.pos.system);
String theQuery = "SELECT obs_publisher_did FROM obscore WHERE ("+inputRegion+" && " + dbRegion + ")";
boolean vel_valid = (coord.band != null) && (coord.band.system != Band.System.NONE);
if(vel_valid)
{
String prefix = toSpecColumnNamePrefix(coord.band.system);
String vel_no_overlap
= "((" + prefix + "_min > " + Double.toString(coord.band.getMax())
+ ") OR (" + prefix + "_max < " + Double.toString(coord.band.getMin()) + "))";
theQuery += " AND ( ("+prefix+"_min is null) OR ("+prefix+"_max is null) OR (NOT " + vel_no_overlap + "))";
/* NOTE '... OR (em_min is null)' statement causes to include 2D datasets if they overlap in sky
* It is the legacy-search behaviour - however is that useful ?
*/
}
if(coord.collection != null)
{
String addColl = "";
if(coord.collection.length() > 0)
addColl += "(obs_collection LIKE '%" + coord.collection + "%')";
if(addColl.length() > 0) theQuery += " AND (" + addColl + ")";
}
theQuery += appendIntervalConstraint(coord.fov, "s_fov"); theQuery += appendIntervalConstraint(coord.spatres, "s_resolution");
theQuery += appendIntervalConstraint(coord.specrp, "em_res_power");
theQuery += appendIntervalConstraint(coord.exptime, "t_exptime");
theQuery += appendIntervalConstraint(coord.timeres, "t_resolution");
theQuery += appendStringMatchConstraint(coord.id, "obs_publisher_did");
theQuery += appendStringMatchConstraint(coord.facility, "facility_name");
theQuery += appendStringMatchConstraint(coord.instrument, "instrument_name");
theQuery += appendStringMatchConstraint(coord.dptype, "dataproduct_type");
theQuery += appendStringMatchConstraint(coord.target, "target_name");
theQuery += appendStringMatchConstraint(coord.format, "access_format");
if(coord.calib != null)
theQuery += " AND (" + coord.calib + " = calib_level)";
if(coord.maxrec != null)
theQuery += " LIMIT " + coord.maxrec;
//theQuery += " ORDER BY obs_collection";
LOGGER.info(theQuery);
List<String> pubdidList = new ArrayList<>();
LOGGER.info("Connecting to: " + dbConn.uri()
+ " with optional user/pwd: " + dbConn.userName() +" / "+ dbConn.password() );
try(
Connection conn = DriverManager.getConnection(dbConn.uri(), dbConn.userName(), dbConn.password());
Statement st = conn.createStatement();
ResultSet res = st.executeQuery(theQuery);)
{
while (res.next())
{
String pubdid_str = res.getString("obs_publisher_did");
pubdidList.add(pubdid_str);
}
}
catch (SQLException se)
{
logSqlExInfo(se);
se.printStackTrace();
}
String[] pubdidArr = pubdidList.toArray(new String[0]);
LOGGER.info("pubdidArr[] length: " + pubdidArr.length);
return pubdidArr;
}
private String appendIntervalConstraint(Interval interval, String colName)
{
if(interval != null)
{
String no_overlap
= "((" + colName + " > " + Double.toString(interval.getMax())
+ ") OR (" + colName + " < " + Double.toString(interval.getMin()) + "))";
return " AND ( (" + colName + " is null) OR (NOT " + no_overlap + "))";
}
else
{
return "";
}
}
private String appendStringMatchConstraint(String str, String colName) {
if(str != null)
{
return " AND ( (" + colName + " is null) OR ('" + str.trim() + "' = " + colName + "))";
}
else
{
return "";
}
}
public FormatResponseFilter.ObsCore[] queryOutputData(String[] pubdidArr, Pos pos)
{
LOGGER.info("");
String commaSepPubdids = String.join("\',\'", pubdidArr);
String inputRegion = toPgSphereSqlTypeString(pos);
String dbRegion = toRegionColumnName(pos.system);
String theQuery ="SELECT *,"
+ inputRegion + " <@ " + dbRegion + " AS inputInsideDb, "
+ inputRegion + " @> " + dbRegion + " AS dbInsideInput FROM obscore WHERE (obs_publisher_did IN (\'"
+commaSepPubdids+"\'))";
theQuery += " ORDER BY obs_collection";
//LOGGER.info(theQuery);
List<FormatResponseFilter.ObsCore> obsCoreList = new ArrayList<>();
LOGGER.info("Connecting to: " + dbConn.uri()
+ " with optional user/pwd: " + dbConn.userName() +" / "+ dbConn.password() );
try(
Connection conn = DriverManager.getConnection(dbConn.uri(), dbConn.userName(), dbConn.password());
Statement st = conn.createStatement();
ResultSet res = st.executeQuery(theQuery);)
{
while (res.next())
{
FormatResponseFilter.ObsCore obsCore = new FormatResponseFilter.ObsCore();
obsCore.dataproduct_type = res.getString("dataproduct_type");
obsCore.calib_level = res.getInt("calib_level");
obsCore.obs_collection = res.getString("obs_collection");
obsCore.obs_id = res.getString("obs_id");
obsCore.obs_publisher_did = res.getString("obs_publisher_did");
obsCore.access_url = res.getString("access_url");
obsCore.access_format = res.getString("access_format");
obsCore.access_estsize = res.getLong("access_estsize");
obsCore.target_name = res.getString("target_name");
obsCore.s_ra = res.getDouble("s_ra");
obsCore.s_dec = res.getDouble("s_dec");
obsCore.s_fov = res.getDouble("s_fov");
obsCore.s_region = res.getString("s_region");
obsCore.s_region_galactic = res.getString("s_region_galactic");
obsCore.s_xel1 = res.getLong("s_xel1");
obsCore.s_xel2 = res.getLong("s_xel2");
obsCore.s_resolution = res.getDouble("s_resolution");
obsCore.t_min = res.getDouble("t_min");
obsCore.t_max = res.getDouble("t_max");
obsCore.t_exptime = res.getDouble("t_exptime");
obsCore.t_resolution = res.getDouble("t_resolution");
obsCore.t_xel = res.getLong("t_xel");
obsCore.vel_min = res.getDouble("vel_min"); //boolean em_min_valid = !res.wasNull();
obsCore.vel_max = res.getDouble("vel_max"); //boolean em_max_valid = !res.wasNull();
obsCore.em_min = res.getDouble("em_min"); boolean em_min_valid = !res.wasNull();
obsCore.em_max = res.getDouble("em_max"); boolean em_max_valid = !res.wasNull();
obsCore.em_valid = em_min_valid && em_max_valid;;
obsCore.em_res_power = res.getDouble("em_res_power");
obsCore.em_xel = res.getLong("em_xel");
obsCore.o_ucd = res.getString("o_ucd");
obsCore.pol_states = res.getString("pol_states");
obsCore.pol_xel = res.getLong("pol_xel");
obsCore.facility_name = res.getString("facility_name");
obsCore.instrument_name = res.getString("instrument_name");
obsCore.vertices_str = res.getString("polygon_region_galactic");
obsCore.inputInsideDb = res.getBoolean("inputInsideDb");
obsCore.dbInsideInput = res.getBoolean("dbInsideInput");
obsCoreList.add(obsCore);
}
LOGGER.info("From DB collected # of ObsCore : " + obsCoreList.size());
}
catch (SQLException se)
{
logSqlExInfo(se);
se.printStackTrace();
}
FormatResponseFilter.ObsCore[] cubes = obsCoreList.toArray(new FormatResponseFilter.ObsCore[0]);
return cubes;
}
private String toPgSphereSqlTypeString(Pos pos)
{
double lon;
double lat;
double radius;
double dlon;
double dlat;
String inputRegion = null;
switch(pos.shape)
{
case CIRCLE:
lon = pos.circle.lon;
lat = pos.circle.lat;
radius = pos.circle.radius;
inputRegion = "scircle '<(" + Double.toString(lon) + "d," + Double.toString(lat) + "d),"
+ Double.toString(radius) + "d>'";
break;
case RANGE:
lon = (pos.range.lon1 + pos.range.lon2)/2.0;
lat = (pos.range.lat1 + pos.range.lat2)/2.0;
dlon = (pos.range.lon2 - pos.range.lon1)/2.0;
dlat = (pos.range.lat2 - pos.range.lat1)/2.0;
/* South-West and North-East corners of a box */
String sw_lon = Double.toString(lon - dlon/2.0);
String sw_lat = Double.toString(lat - dlat/2.0);
String ne_lon = Double.toString(lon + dlon/2.0);
String ne_lat = Double.toString(lat + dlat/2.0);
inputRegion = "sbox '( ("+ sw_lon + "d, " + sw_lat + "d), (" + ne_lon +"d, " + ne_lat + "d) )'";
break;
case POLYGON:
// FIXME redefine Polygon as point-array:
assert(pos.polygon.lon.length == pos.polygon.lat.length);
// Polygon has at least 3 points
inputRegion = "spoly '( (" + pos.polygon.lon[0] + "d, " + pos.polygon.lat[0] + "d),";
for(int ii=1; ii < pos.polygon.lon.length; ii++)
{
inputRegion += ", (" + pos.polygon.lon[ii] + "d, " + pos.polygon.lat[ii] + "d)";
}
inputRegion += " )'";
break;
default:
throw new IllegalArgumentException("Coord::shape was: " + pos.shape.toString()
+ " but valid is CIRCLE or RANGE or POLYGON");
}
return inputRegion;
}
private String toRegionColumnName(Pos.System system)
{
String dbRegion;
switch(system)
{
case GALACTIC:
dbRegion = "polygon_region_galactic";
break;
default:
dbRegion = "polygon_region";
}
return dbRegion;
}
private String toSpecColumnNamePrefix(Band.System system)
{
// vlkb-volib/Band.System: WAVE_Barycentric, VELO_LSRK, GRID, NONE
String prefix;
switch(system)
{
case VELO_LSRK: prefix = "vel"; break;
default: prefix = "em";
}
return prefix;
}
private void logSqlExInfo(SQLException se)
{
LOGGER.info("SQLState : " + se.getSQLState());
LOGGER.info("ErrorCode: " + se.getErrorCode());
LOGGER.info("Message : " + se.getMessage());
Throwable t = se.getCause();
while(t != null) {
LOGGER.info("Cause: " + t);
t = t.getCause();
}
}
}