public static void main(String[] args) {
DbInstance dbinstance =
new DbInstance(
false, // pooled
"null", // datasourcePath
"<REPLACE_WITH_DB_URL>", // dbUrl
"qsmaster",
null,
"com.mysql.jdbc.Driver",
"lsstdb");
DataSource ds = JdbcFactory.getDataSource(dbinstance);
String sql = "select * from DeepSource where qserv_areaspec_box(0.4, 1.05, 0.5, 1.15)";
Connection conn = null;
try {
conn = DataSourceUtils.getConnection(ds);
long cTime = System.currentTimeMillis();
Statement stmt = null;
try {
stmt = conn.createStatement();
_log.briefDebug("Executing SQL query: " + sql);
// ResultSet rs = stmt.executeQuery(sql);
// _log.briefDebug ("SELECT took "+(System.currentTimeMillis()-cTime)+"ms");
File file = new File("/tmp/lsstTest.tbl");
IpacTableExtractor.query(null, ds, file, 10000, sql);
_log.briefDebug("SELECT took " + (System.currentTimeMillis() - cTime) + "ms");
} catch (Exception e) {
System.out.println("Exception " + e.getMessage());
} finally {
closeStatement(stmt);
}
} catch (Exception e) {
System.out.println("Exception " + e.getMessage());
e.printStackTrace();
} finally {
if (conn != null) {
DataSourceUtils.releaseConnection(conn, ds);
}
}
}
// qserv_areaspec_circle does not work at the moment, only qserv_areaspec_box works
void doGetData(File oFile, List<Param> params, WorldPt wpt) throws DataAccessException {
DbInstance dbinstance =
new DbInstance(
false, // pooled
"null", // datasourcePath
QSERV_URI, // dbUrl
QSERV_USER,
StringUtils.isEmpty(QSERV_PASS) ? null : QSERV_PASS,
"com.mysql.jdbc.Driver",
"lsstdb");
DataSource ds = JdbcFactory.getDataSource(dbinstance);
String searchMethod = null;
String catTable = null;
String selectedColumns = null;
String[] constraints = null;
for (Param p : params) {
String pname = p.getName();
if (pname.equals(CatalogRequest.SEARCH_METHOD)) {
searchMethod = p.getValue();
} else if (pname.equals(ServerParams.REQUESTED_DATA_SET)) {
catTable = p.getValue();
} else if (pname.equals(CatalogRequest.SELECTED_COLUMNS)) {
selectedColumns = p.getValue();
} else if (pname.equals(CatalogRequest.CONSTRAINTS)) {
String val = p.getValue();
if (!StringUtils.isEmpty(val)) {
_log.briefDebug("CONSTRAINTS: " + val);
constraints = val.split(",");
}
}
}
String update = getSelectedColumnsUpdate(selectedColumns);
if (update != null) selectedColumns = update;
String pname;
String sql;
if (searchMethod != null && searchMethod.equals(CatalogRequest.Method.CONE.getDesc())) {
double radius = 0.0;
for (Param p : params) {
pname = p.getName();
if (pname.equals(CatalogRequest.RADIUS)) {
radius = Double.parseDouble(p.getValue());
}
}
// qserv_areaspec_circle(ra, dec, radius)
// sql="select * from "+catTable+" where qserv_areaspec_circle("+wpt.getLon()+",
// "+wpt.getLat()+", "+radius+")";
// Per Serge, the above query only can not be applied to unpartitioned table
sql =
"select "
+ selectedColumns
+ " from "
+ catTable
+ " where scisql_s2PtInCircle(ra, decl, "
+ wpt.getLon()
+ ", "
+ wpt.getLat()
+ ", "
+ radius
+ ")=1";
} else if (searchMethod != null && searchMethod.equals(CatalogRequest.Method.BOX.getDesc())) {
double size = 0.0;
for (Param p : params) {
pname = p.getName();
if (pname.equals(CatalogRequest.SIZE)) {
size = Double.parseDouble(p.getValue());
}
}
double halfsize = size / 2.0;
double lon1 = wpt.getLon() - halfsize;
if (lon1 < 0) lon1 += 360;
double lon2 = wpt.getLon() + halfsize;
if (lon2 > 360) lon1 -= 360;
double lat1 = wpt.getLat() - halfsize;
if (lat1 < -90) lat1 = -180 - lat1;
double lat2 = wpt.getLat() + halfsize;
if (lat2 > 90) lat2 = 180 - lat2;
// qserv_areaspec_box(raA, decA, raB, decB)
// sql="select * from "+catTable+" where qserv_areaspec_box("+lon1+", "+lat1+", "+lon2+",
// "+lat2+")";
// Per Serge, the above query only can not be applied to unpartitioned table
sql =
"select * from "
+ catTable
+ " where scisql_s2PtInBox(ra, decl, "
+ lon1
+ ", "
+ lat1
+ ", "
+ lon2
+ ", "
+ lat2
+ ")=1";
} else {
throw new RuntimeException(searchMethod + " search is not Implemented");
}
// add additional constraints
if (constraints != null) {
for (String constr : constraints) {
sql += " and " + constr;
}
}
// workaround for https://jira.lsstcorp.org/browse/DM-1841
sql += " LIMIT 3000";
Connection conn = null;
Statement stmt = null;
try {
conn = DataSourceUtils.getConnection(ds);
long cTime = System.currentTimeMillis();
stmt = conn.createStatement();
_log.briefDebug("Executing SQL query: " + sql);
// ResultSet rs = stmt.executeQuery(sql);
IpacTableExtractor.query(null, ds, oFile, 10000, sql);
_log.briefDebug("SELECT took " + (System.currentTimeMillis() - cTime) + "ms");
} catch (Exception e) {
_log.error(e);
throw new DataAccessException("Query failed: " + e.getMessage());
} finally {
if (stmt != null) {
try {
closeStatement(stmt);
} catch (Exception e1) {
}
}
if (conn != null) {
DataSourceUtils.releaseConnection(conn, ds);
}
}
}