Select Git revision
DbPSearch.java
-
Robert Butora authored
config: adss DB-config to Format-filter (Search-servlet ahs its own config entry) and Db-search module is inited by Search-config or Format-config
Robert Butora authoredconfig: adss DB-config to Format-filter (Search-servlet ahs its own config entry) and Db-search module is inited by Search-config or Format-config
DbPSearch.java 15.39 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;
public class DbPSearch
{
private static final Logger LOGGER = Logger.getLogger(DbPSearch.class.getName());
//private static final SearchSettings.DBConn dbConn = SearchSettings.getInstance("discovery.properties").dbConn;
private DBConn dbConn;
//private static final String DB_DRIVER = "org.postgresql.Driver";
DbPSearch(DBConn dbConn)
{
this.dbConn = dbConn;
}
public String[] queryOverlapingPubdid(Coord coord, SubsurveyId subsurveyId)
{
LOGGER.info("trace");
double lon = coord.lon;
double lat = coord.lat;
double radius = coord.radius;
double dlon = coord.dlon;
double dlat = coord.dlat;
boolean vel_valid = coord.vel_valid;
String vel_type = coord.vel_type;
double vel_low = coord.vel_low;
double vel_up = coord.vel_up;
String inputRegion = null;
if(coord.shape.equals("CIRCLE"))
{
inputRegion = "scircle '<(" + Double.toString(lon) + "d," + Double.toString(lat) + "d),"
+ Double.toString(radius) + "d>'";
}
else if( coord.shape.equals("RECT") )
{
/*Vert vert[] = toVertices(lon, lat, dlon, dlat);
inputRegion = "spoly '{"
+ "(" + Double.toString(vert[0].lon) + "d," + Double.toString(vert[0].lat) + "d),"
+ "(" + Double.toString(vert[1].lon) + "d," + Double.toString(vert[1].lat) + "d),"
+ "(" + Double.toString(vert[2].lon) + "d," + Double.toString(vert[2].lat) + "d),"
+ "(" + Double.toString(vert[3].lon) + "d," + Double.toString(vert[3].lat) + "d)"
+ "}'";
*/
/* 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) )'";
}
else
{
throw new IllegalArgumentException("Coord::shape was: " + coord.shape + " but valid is CIRCLE or RECT");
}
String theQuery;
if(coord.skySystem.equals("GALACTIC"))
{
theQuery ="SELECT obs_publisher_did FROM obscore WHERE (" + inputRegion + " && polygon_region_galactic)";
}
else
{
theQuery ="SELECT obs_publisher_did FROM obscore WHERE (" + inputRegion + " && polygon_region)";
}
if(vel_valid)
{
String vel_no_overlap = "((em_min > " + Double.toString(vel_up) + ") OR (em_max < " + Double.toString(vel_low) + "))";
theQuery += " AND ( (NOT " + vel_no_overlap + ") OR (em_min is null) OR (em_max is null))";
/* NOTE '... OR (em_min is null)' statement causes to include 2D-continuum datasets if they overlap in sky
* It is the legacy-search behaviour - however is that useful ?
*/
}
if(subsurveyId != null)
{
/* FIXME replace this implementation with exact string match once survey_id is defined / added to obs_core */
String addSS = "";
if((subsurveyId.surveyName != null) && (subsurveyId.surveyName.length() > 0))
addSS += "(obs_collection LIKE '" + subsurveyId.surveyName + "%')";
if((subsurveyId.species != null) && (subsurveyId.species.length() > 0) )
{
if(addSS.length() > 0) addSS += " OR ";
addSS += "(obs_collection LIKE '%" + subsurveyId.species + "%')";
}
if((subsurveyId.transition != null) && (subsurveyId.transition.length() > 0) )
{
if(addSS.length() > 0) addSS += " OR ";
addSS += "(obs_collection LIKE '%" + subsurveyId.transition + "')";
};
if(addSS.length() > 0) theQuery += " AND (" + addSS + ")";
}
//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;
}
public FormatResponseFilter.ObsCore[] queryOutputData(String[] pubdidArr, Coord coord/*, SubsurveyId subsurveyId*/)
{
LOGGER.info("");
double lon = coord.lon;
double lat = coord.lat;
double radius = coord.radius;
double dlon = coord.dlon;
double dlat = coord.dlat;
boolean vel_valid = coord.vel_valid;
String vel_type = coord.vel_type;
double vel_low = coord.vel_low;
double vel_up = coord.vel_up;
String inputRegion = null;
if(coord.shape.equals("CIRCLE"))
{
inputRegion = "scircle '<(" + Double.toString(lon) + "d," + Double.toString(lat) + "d),"
+ Double.toString(radius) + "d>'";
}
else if( coord.shape.equals("RECT") )
{
/*Vert vert[] = toVertices(lon, lat, dlon, dlat);
inputRegion = "spoly '{"
+ "(" + Double.toString(vert[0].lon) + "d," + Double.toString(vert[0].lat) + "d),"
+ "(" + Double.toString(vert[1].lon) + "d," + Double.toString(vert[1].lat) + "d),"
+ "(" + Double.toString(vert[2].lon) + "d," + Double.toString(vert[2].lat) + "d),"
+ "(" + Double.toString(vert[3].lon) + "d," + Double.toString(vert[3].lat) + "d)"
+ "}'";
*/
/* 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) )'";
}
else
{
throw new IllegalArgumentException("Coord::shape was: " + coord.shape + " but valid is CIRCLE or RECT");
}
String commaSepPubdids = String.join("\',\'", pubdidArr);
//String theQuery ="SELECT dataproduct_type,obs_publisher_did,obs_collection,polygon_region_galactic,access_url,em_min,em_max,"
String theQuery;
if(coord.skySystem.equals("GALACTIC"))
{
theQuery ="SELECT *,"
+ inputRegion + " <@ polygon_region_galactic AS inputInsideDb, "
+ inputRegion + " @> polygon_region_galactic AS dbInsideInput FROM obscore WHERE (obs_publisher_did IN (\'"+commaSepPubdids+"\'))";
}
else
{
theQuery ="SELECT *,"
+ inputRegion + " <@ polygon_region AS inputInsideDb, "
+ inputRegion + " @> polygon_region 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);)
{
//ResultSet res = doQuery(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_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.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;
}
/*
public static void loadDriver()// throws ClassNotFoundException
{
/* https://docs.oracle.com/javase/tutorial/jdbc/basics/connecting.html :
* Any JDBC 4.0 drivers that are found in your class path are automatically loaded.
* (However, you must manually load any drivers prior to JDBC 4.0 with the method
* Class.forName.)
* /
/* OR
* DriverManager.registerDriver(new org.postgresql.Driver());
* LOGGER.info(getClasspathString());
* LOGGER.info(getRegisteredDriverList());
*i /
try
{
// Class.forName(DB_DRIVER);
}
catch (ClassNotFoundException e)
{
LOGGER.info("DB driver "+ DB_DRIVER +" not found: " + e.getMessage());
e.printStackTrace();
}
}
*/
/*
private ResultSet doQuery(String theQuery) throws SQLException, ClassNotFoundException
{
/* https://docs.oracle.com/javase/tutorial/jdbc/basics/connecting.html :
* Any JDBC 4.0 drivers that are found in your class path are automatically loaded.
* (However, you must manually load any drivers prior to JDBC 4.0 with the method
* Class.forName.)
* /
// Class.forName(DB_DRIVER);
/* OR
* DriverManager.registerDriver(new org.postgresql.Driver());
* LOGGER.info(getClasspathString());
* LOGGER.info(getRegisteredDriverList());
* /
LOGGER.info("Connecting to: " + dbConn.uri() + " with optional user/pwd: " + dbConn.userName() +" / "+ dbConn.password() );
Connection conn = DriverManager.getConnection(dbConn.uri(), dbConn.userName(), dbConn.password());
Statement st = conn.createStatement();
ResultSet res = st.executeQuery(theQuery);
return res;
}
*/
/* public Subsurvey[] getSurveyTable()
{
List<Subsurvey> survList = new ArrayList<Subsurvey>();
String theQuery = "SELECT name,species,transition,rest_frequency,restf_fits_unit,velocity_fits_unit,description FROM surveys";
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);)
{
//ResultSet res = doQuery(theQuery);
if(res == null)
{
LOGGER.info("Query yielded no resuls: " + theQuery);
}
else
{
while (res.next())
{
Subsurvey surv = new Subsurvey();
surv.rf = res.getDouble("rest_frequency");
surv.surveyname = res.getString("name");
surv.species = res.getString("species");
surv.transition = res.getString("transition");
surv.rf_unit = res.getString("restf_fits_unit");
surv.vel_unit = res.getString("velocity_fits_unit");
surv.description = res.getString("description");
survList.add(surv);
}
}
}
catch (SQLException se)
{
logSqlExInfo(se);
se.printStackTrace();
}
return survList.toArray(new Subsurvey[0]);
}
*/
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();
}
}
private String getClasspathString()
{
StringBuffer classpath = new StringBuffer("getClasspathString:\r\n");
ClassLoader applicationClassLoader = this.getClass().getClassLoader();
if (applicationClassLoader == null) { applicationClassLoader = ClassLoader.getSystemClassLoader();
}
URL[] urls = ((URLClassLoader)applicationClassLoader).getURLs();
for(int i=0; i < urls.length; i++) {
classpath.append(urls[i].getFile()).append("\r\n");
}
return classpath.toString();
}
/* Returns the list of JDBC Drivers loaded by the caller's class loader */
private String getRegisteredDriverList()
{
StringBuffer drvList = new StringBuffer("getRegisteredDriverList:\r\n");
for (Enumeration e = DriverManager.getDrivers();
e.hasMoreElements(); )
{
Driver d = (Driver) e.nextElement();
String driverClass = d.getClass().getName();
drvList.append(driverClass).append("\r\n");
}
return drvList.toString();
}
}