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 DbObstap { private static final Logger LOGGER = Logger.getLogger(DbObstap.class.getName()); private DbConnArgs dbConnArgs; DbObstap(DbConnArgs dbConnArgs) { this.dbConnArgs = dbConnArgs; } static class Obstap { String dataproduct_type; Integer calib_level; String obs_collection; String obs_title; String obs_id; String obs_publisher_did; String bib_reference; String data_rights; String access_url; String access_format; Long access_estsize; String target_name; Double s_ra, s_dec, s_fov; String s_region; Long s_xel1, s_xel2; Double s_resolution; Double t_min, t_max, t_exptime, t_resolution; Long t_xel; Double em_min, em_max, em_res_power; Long em_xel; boolean em_valid; String o_ucd; String pol_states; Long pol_xel; String facility_name; String instrument_name; // VLKB extesnion Integer overlapCodeSky; Integer overlapCodeVel; Integer overlapCode; String s_region_galactic; Double vel_min, vel_max; } public String[] queryOverlapingPubdid(QueryArgs qArgs) throws Exception { LOGGER.fine("trace"); String inputRegion = toPgSphereSqlTypeString(qArgs.pos); String dbRegion = toRegionColumnName(qArgs.pos.system); String theQuery = "SELECT obs_publisher_did FROM obscore WHERE ("+inputRegion+" && " + dbRegion + ")"; boolean vel_valid = (qArgs.band != null) && (qArgs.band.system != Band.System.NONE); if(vel_valid) { String prefix = toSpecColumnNamePrefix(qArgs.band.system); String vel_no_overlap = "((" + prefix + "_min > " + Double.toString(qArgs.band.max) + ") OR (" + prefix + "_max < " + Double.toString(qArgs.band.min) + "))"; 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(qArgs.collection != null) { String addColl = ""; if(qArgs.collection.length() > 0) addColl += "(obs_collection LIKE '%" + qArgs.collection + "%')"; if(addColl.length() > 0) theQuery += " AND (" + addColl + ")"; } theQuery += appendIntervalConstraint(qArgs.fov, "s_fov"); theQuery += appendIntervalConstraint(qArgs.spatres, "s_resolution"); theQuery += appendIntervalConstraint(qArgs.specrp, "em_res_power"); theQuery += appendIntervalConstraint(qArgs.exptime, "t_exptime"); theQuery += appendIntervalConstraint(qArgs.timeres, "t_resolution"); theQuery += appendStringMatchConstraint(qArgs.id, "obs_publisher_did"); theQuery += appendStringMatchConstraint(qArgs.facility, "facility_name"); theQuery += appendStringMatchConstraint(qArgs.instrument, "instrument_name"); theQuery += appendStringMatchConstraint(qArgs.dptype, "dataproduct_type"); theQuery += appendStringMatchConstraint(qArgs.target, "target_name"); theQuery += appendStringMatchConstraint(qArgs.format, "access_format"); if(qArgs.calib != null) theQuery += " AND (" + qArgs.calib + " = calib_level)"; if(qArgs.maxrec != null) theQuery += " LIMIT " + qArgs.maxrec; LOGGER.fine(theQuery); List<String> pubdidList = new ArrayList<>(); LOGGER.fine("Connecting to: " + dbConnArgs.uri() + " with user: " + dbConnArgs.userName() ); try( Connection conn = DriverManager.getConnection(dbConnArgs.uri(), dbConnArgs.userName(), dbConnArgs.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) { dbError(se); // se.printStackTrace(); throw new Exception(se.toString()); } String[] pubdidArr = pubdidList.toArray(new String[0]); LOGGER.fine("pubdidArr[] length: " + pubdidArr.length); return pubdidArr; } private String appendIntervalConstraint(Interval interval, String colName) { if(interval != null) { String no_overlap = "((" + colName + " > " + Double.toString(interval.max) + ") OR (" + colName + " < " + Double.toString(interval.min) + "))"; 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 Obstap[] queryOutputData(String[] pubdidArr, Pos pos, Band band) throws Exception { LOGGER.fine("trace"); 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+"\'))"; List<Obstap> obstapList = new ArrayList<>(); LOGGER.fine(theQuery); LOGGER.fine("Connecting to: " + dbConnArgs.uri() + " with optional user/pwd: " + dbConnArgs.userName() +" / "+ dbConnArgs.password() ); try( Connection conn = DriverManager.getConnection(dbConnArgs.uri(), dbConnArgs.userName(), dbConnArgs.password()); Statement st = conn.createStatement(); ResultSet res = st.executeQuery(theQuery);) { while (res.next()) { Obstap obstap = new Obstap(); obstap.dataproduct_type = this.getString(res,"dataproduct_type"); obstap.calib_level = this.getInt(res,"calib_level"); obstap.obs_collection = this.getString(res,"obs_collection"); obstap.obs_title = this.getString(res,"obs_title"); obstap.obs_id = this.getString(res,"obs_id"); obstap.obs_publisher_did = this.getString(res,"obs_publisher_did"); obstap.bib_reference = this.getString(res,"bib_reference"); obstap.data_rights = this.getString(res,"data_rights"); obstap.access_url = this.getString(res,"access_url"); obstap.access_format = this.getString(res,"access_format"); obstap.access_estsize = this.getLong(res,"access_estsize"); obstap.target_name = this.getString(res,"target_name"); obstap.s_ra = this.getDouble(res,"s_ra"); obstap.s_dec = this.getDouble(res,"s_dec"); obstap.s_fov = this.getDouble(res,"s_fov"); obstap.s_region = this.getString(res,"s_region"); obstap.s_xel1 = this.getLong(res,"s_xel1"); obstap.s_xel2 = this.getLong(res,"s_xel2"); obstap.s_resolution = this.getDouble(res,"s_resolution"); obstap.t_min = this.getDouble(res,"t_min"); obstap.t_max = this.getDouble(res,"t_max"); obstap.t_exptime = this.getDouble(res,"t_exptime"); obstap.t_resolution = this.getDouble(res,"t_resolution"); obstap.t_xel = this.getLong(res,"t_xel"); obstap.em_min = this.getDouble(res,"em_min"); boolean em_min_valid = !res.wasNull(); obstap.em_max = this.getDouble(res,"em_max"); boolean em_max_valid = !res.wasNull(); obstap.em_valid = em_min_valid && em_max_valid;; obstap.em_res_power = this.getDouble(res,"em_res_power"); obstap.em_xel = this.getLong(res,"em_xel"); obstap.o_ucd = this.getString(res,"o_ucd"); obstap.pol_states = this.getString(res,"pol_states"); obstap.pol_xel = this.getLong(res ,"pol_xel"); obstap.facility_name = this.getString(res,"facility_name"); obstap.instrument_name = this.getString(res,"instrument_name"); // VLKB extension obstap.s_region_galactic = this.getString(res,"s_region_galactic"); obstap.vel_min = this.getDouble(res,"vel_min"); //boolean em_min_valid = !res.wasNull(); obstap.vel_max = this.getDouble(res,"vel_max"); //boolean em_max_valid = !res.wasNull(); boolean inputInsideDb = res.getBoolean("inputInsideDb"); boolean dbInsideInput = res.getBoolean("dbInsideInput"); obstap.overlapCodeSky = convertToOverlapCodeSky(inputInsideDb, dbInsideInput); obstap.overlapCodeVel = convertToOverlapCodeVel(band,obstap.em_valid,obstap.em_min,obstap.em_max); obstap.overlapCode = convertToOverlapCode(obstap.overlapCodeSky, obstap.overlapCodeVel); obstapList.add(obstap); } LOGGER.fine("From DB collected # of Obstap : " + obstapList.size()); } catch (SQLException se) { dbError(se); // se.printStackTrace(); throw new Exception(se.toString()); } Obstap[] cubes = obstapList.toArray(new Obstap[0]); return cubes; } /* convert overlap codes */ private Integer convertToOverlapCodeSky(boolean inpInDb, boolean dbInInp) { if(!inpInDb && !dbInInp) return 4; // parial overlap else if( inpInDb && !dbInInp) return 3; // input region completely inside fits-datacube else if(!inpInDb && dbInInp) return 2; // datacube completely inside input-region else return 5; // exact match: both inpInDb dbInInp are true } private Integer convertToOverlapCodeVel(Band band, boolean v_valid, double v_min, double v_max) { if((band != null) && v_valid) { if(band.system == Band.System.VELO_LSRK) { // FIXME assert qArgs: vel_min <= vel_max // FIXME assert cube: v_min <= v_max boolean dbInInp = (band.min <= v_min) && (v_min <= band.max) && (band.min <= v_max) && (v_max <= band.max); boolean inpInDb = (v_min <= band.min) && (band.min <= v_max) && (v_min <= band.max ) && (band.max <= v_max); return convertToOverlapCodeSky(inpInDb, dbInInp); } else return null;// FIXME other v_type NotImplemented yet } return null; } private Integer convertToOverlapCode(Integer ovcSky, Integer ovcVel) { if(ovcVel == null) return ovcSky; // 2D images else if(ovcSky == ovcVel) return ovcSky; else return 4;// partial overlap } /* conversions tolerate missing columns */ private Integer getInt(ResultSet res, String colLabel) { try { Integer value = res.getInt(colLabel); return (res.wasNull() ? null : value); } catch(SQLException se) { dbError(se); return null; } } private Long getLong(ResultSet res, String colLabel) { try { Long value = res.getLong(colLabel); return (res.wasNull() ? null : value); } catch(SQLException se) { dbError(se); return null; } } private Double getDouble(ResultSet res, String colLabel) { try { Double value = res.getDouble(colLabel); return (res.wasNull() ? null : value); } catch(SQLException se) { dbError(se); return null; } } private String getString(ResultSet res, String colLabel) { try { String value = res.getString(colLabel); return (res.wasNull() ? null : value); } catch(SQLException se) { dbError(se); return null; } } 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("Pos::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 dbError(SQLException se) { LOGGER.fine("SQLState : " + se.getSQLState()); LOGGER.fine("ErrorCode: " + se.getErrorCode()); LOGGER.warning("Message: " + se.getMessage()); Throwable t = se.getCause(); while(t != null) { LOGGER.fine("Cause: " + t); t = t.getCause(); } } }