예제 #1
0
  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);
      }
    }
  }
예제 #2
0
  // 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);
      }
    }
  }