# HG changeset patch # User Sascha L. Teichmann # Date 1357393975 -3600 # Node ID d9f1202cef785e199e5339e2a3e4c25c28704228 # Parent 9dcc32fc51679b550cc664915fca51eb41c78b58 Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in AFT and 'discharge_table.bfg_id' in FLYS. TODO: Create new column in discharge_tabel. diff -r 9dcc32fc5167 -r d9f1202cef78 flys-aft/src/main/java/de/intevation/aft/DischargeTable.java --- a/flys-aft/src/main/java/de/intevation/aft/DischargeTable.java Sat Jan 05 13:46:27 2013 +0100 +++ b/flys-aft/src/main/java/de/intevation/aft/DischargeTable.java Sat Jan 05 14:52:55 2013 +0100 @@ -23,6 +23,7 @@ protected int gaugeId; protected TimeInterval timeInterval; protected String description; + protected String bfgId; protected Set values; public DischargeTable() { @@ -31,11 +32,13 @@ public DischargeTable( int gaugeId, TimeInterval timeInterval, - String description + String description, + String bfgId ) { this.gaugeId = gaugeId; this.timeInterval = timeInterval; this.description = description; + this.bfgId = bfgId; values = new TreeSet(WQ.EPS_CMP); } @@ -43,9 +46,10 @@ int id, int gaugeId, TimeInterval timeInterval, - String description + String description, + String bfgId ) { - this(gaugeId, timeInterval, description); + this(gaugeId, timeInterval, description, bfgId); this.id = id; } @@ -81,6 +85,15 @@ this.description = description; } + public String getBfgId() { + return bfgId; + } + + public void setBfgId(String bfgId) { + this.bfgId = bfgId; + } + + public void clearValues() { values.clear(); } @@ -140,10 +153,15 @@ .getStatement("insert.discharge.table.value"); for (WQ wq: values) { + int wqId; ResultSet rs = nextId.executeQuery(); - rs.next(); - int wqId = rs.getInt("discharge_table_values_id"); - rs.close(); + try { + rs.next(); + wqId = rs.getInt("discharge_table_values_id"); + } + finally { + rs.close(); + } insertDTV .clearParameters() @@ -169,31 +187,38 @@ .clearParameters() .setInt("gauge_id", gaugeId) .executeQuery(); + try { + OUTER: while (rs.next()) { + int id = rs.getInt("id"); + String description = rs.getString("description"); + String bfgId = rs.getString("bfg_id"); + if (description == null) { + description = ""; + } + if (bfgId == null) { + bfgId = ""; + } + for (DischargeTable dt: dts) { + if (dt.getBfgId().equals(bfgId)) { + log.warn("FLYS: Found discharge table '" + + bfgId + "' with same bfg_id. -> ignore"); + continue OUTER; + } + } + Date startTime = rs.getDate("start_time"); + Date stopTime = rs.getDate("stop_time"); + TimeInterval ti = startTime == null + ? null + : new TimeInterval(startTime, stopTime); - OUTER: while (rs.next()) { - int id = rs.getInt("id"); - String description = rs.getString("description"); - if (description == null) { - description = ""; + DischargeTable dt = new DischargeTable( + id, gaugeId, ti, description, bfgId); + dts.add(dt); } - for (DischargeTable dt: dts) { - if (dt.getDescription().equals(description)) { - log.warn("FLYS: Found discharge table '" + - description + "' with same description. -> ignore"); - continue OUTER; - } - } - Date startTime = rs.getDate("start_time"); - Date stopTime = rs.getDate("stop_time"); - TimeInterval ti = startTime == null - ? null - : new TimeInterval(startTime, stopTime); - - DischargeTable dt = new DischargeTable( - id, gaugeId, ti, description); - dts.add(dt); } - rs.close(); + finally { + rs.close(); + } return dts; } @@ -222,57 +247,66 @@ .clearParameters() .setString("number", "%" + officialNumber) .executeQuery(); - - OUTER: while (rs.next()) { - int dtId = rs.getInt("ABFLUSSTAFEL_NR"); - Date from = rs.getDate("GUELTIG_VON"); - Date to = rs.getDate("GUELTIG_BIS"); + try { + OUTER: while (rs.next()) { + int dtId = rs.getInt("ABFLUSSTAFEL_NR"); + Date from = rs.getDate("GUELTIG_VON"); + Date to = rs.getDate("GUELTIG_BIS"); - if (from == null) { - log.warn("AFT: ABFLUSSTAFEL_NR = " - + dtId + ": GUELTIG_VON = NULL -> ignored."); - } - - if (to == null) { - log.warn("AFT: ABFLUSSTAFEL_NR = " - + dtId + ": GUELTIG_BIS = NULL -> ignored."); - } - - if (from == null || to == null) { - continue; - } - - if (from.compareTo(to) > 0) { + if (from == null) { log.warn("AFT: ABFLUSSTAFEL_NR = " - + dtId + ": " + from + " > " + to + ". -> swap"); - Date temp = from; - from = to; - to = temp; - } - - String description = rs.getString("ABFLUSSTAFEL_BEZ"); - if (description == null) { - description = String.valueOf(officialNumber); - } + + dtId + ": GUELTIG_VON = NULL -> ignored."); + } - for (DischargeTable dt: dts) { - if (dt.getDescription().equals(description)) { - log.warn("AFT: Found discharge table '" + - description + "' with same description. -> ignore."); - continue OUTER; + if (to == null) { + log.warn("AFT: ABFLUSSTAFEL_NR = " + + dtId + ": GUELTIG_BIS = NULL -> ignored."); } - } - TimeInterval timeInterval = new TimeInterval(from, to); + if (from == null || to == null) { + continue; + } - DischargeTable dt = new DischargeTable( - dtId, - flysGaugeId, - timeInterval, - description); - dts.add(dt); + if (from.compareTo(to) > 0) { + log.warn("AFT: ABFLUSSTAFEL_NR = " + + dtId + ": " + from + " > " + to + ". -> swap"); + Date temp = from; + from = to; + to = temp; + } + + String description = rs.getString("ABFLUSSTAFEL_BEZ"); + if (description == null) { + description = String.valueOf(officialNumber); + } + + String bfgId = rs.getString("BFG_ID"); + if (bfgId == null) { + bfgId = ""; + } + + for (DischargeTable dt: dts) { + if (dt.getBfgId().equals(bfgId)) { + log.warn("AFT: Found discharge table '" + + bfgId + "' with same bfg_id. -> ignore."); + continue OUTER; + } + } + + TimeInterval timeInterval = new TimeInterval(from, to); + + DischargeTable dt = new DischargeTable( + dtId, + flysGaugeId, + timeInterval, + description, + bfgId); + dts.add(dt); + } } - rs.close(); + finally { + rs.close(); + } return dts; } @@ -297,20 +331,26 @@ ConnectedStatements flysStatements = context.getFlysStatements(); + int flysId; + ResultSet rs = flysStatements .getStatement("next.discharge.id") .executeQuery(); - - rs.next(); - int flysId = rs.getInt("discharge_table_id"); - rs.close(); + try { + rs.next(); + flysId = rs.getInt("discharge_table_id"); + } + finally { + rs.close(); + } SymbolicStatement.Instance insertDT = flysStatements .getStatement("insert.dischargetable") .clearParameters() .setInt("id", flysId) .setInt("gauge_id", gaugeId) - .setString("description", description); + .setString("description", description) + .setString("bfg_id", bfgId); if (timeInterval != null) { insertDT.setInt("time_interval_id", timeInterval.getId()); diff -r 9dcc32fc5167 -r d9f1202cef78 flys-aft/src/main/java/de/intevation/aft/River.java --- a/flys-aft/src/main/java/de/intevation/aft/River.java Sat Jan 05 13:46:27 2013 +0100 +++ b/flys-aft/src/main/java/de/intevation/aft/River.java Sat Jan 05 14:52:55 2013 +0100 @@ -179,8 +179,8 @@ throws SQLException { log.info("FLYS: Updating gauge '" + gauge.getAftName() + "'."); - // We need to load all discharge tables from both database - // of the gauge and do some pairing based on their descriptions. + // We need to load all discharge tables from both databases + // of the gauge and do some pairing based on their bfg_id. boolean modified = false; @@ -196,40 +196,40 @@ DischargeTable.loadAftDischargeTables( context, gauge.getOfficialNumber()); - Map desc2FlysDT = + Map bfgId2FlysDT = new HashMap(); for (DischargeTable dt: flysDTs) { - String description = dt.getDescription(); - if (description == null) { + String bfgId = dt.getBfgId(); + if (bfgId == null) { log.warn("FLYS: discharge table " + dt.getId() - + " has no description. Ignored."); + + " has no bfg_id. Ignored."); continue; } - desc2FlysDT.put(description, dt); + bfgId2FlysDT.put(bfgId, dt); } List createDTs = new ArrayList(); for (DischargeTable aftDT: aftDTs) { - String description = aftDT.getDescription(); - DischargeTable flysDT = desc2FlysDT.remove(description); + String bfgId = aftDT.getBfgId(); + DischargeTable flysDT = bfgId2FlysDT.remove(bfgId); if (flysDT != null) { // Found in AFT and FLYS. - log.info("FLYS: Discharge table '" + description + log.info("FLYS: Discharge table '" + bfgId + "' found in AFT and FLYS. -> update"); // Create the W/Q diff. modified |= writeWQChanges(context, flysDT, aftDT); } else { - log.info("FLYS: Discharge table '" + description + log.info("FLYS: Discharge table '" + bfgId + "' not found in FLYS. -> create"); createDTs.add(aftDT); } } - for (String description: desc2FlysDT.keySet()) { - log.info("FLYS: Discharge table '" + description + for (String bfgId: bfgId2FlysDT.keySet()) { + log.info("FLYS: Discharge table '" + bfgId + "' found in FLYS but not in AFT. -> ignore"); } diff -r 9dcc32fc5167 -r d9f1202cef78 flys-aft/src/main/java/de/intevation/db/ConnectionBuilder.java --- a/flys-aft/src/main/java/de/intevation/db/ConnectionBuilder.java Sat Jan 05 13:46:27 2013 +0100 +++ b/flys-aft/src/main/java/de/intevation/db/ConnectionBuilder.java Sat Jan 05 14:52:55 2013 +0100 @@ -17,7 +17,6 @@ import org.apache.log4j.Logger; import org.w3c.dom.Document; -import org.w3c.dom.Node; import org.w3c.dom.NodeList; public class ConnectionBuilder diff -r 9dcc32fc5167 -r d9f1202cef78 flys-aft/src/main/resources/sql/aft-common.properties --- a/flys-aft/src/main/resources/sql/aft-common.properties Sat Jan 05 13:46:27 2013 +0100 +++ b/flys-aft/src/main/resources/sql/aft-common.properties Sat Jan 05 14:52:55 2013 +0100 @@ -3,11 +3,15 @@ SELECT NAME, MESSSTELLE_NR \ FROM MESSSTELLE \ WHERE GEWAESSER_NR = :GEWAESSER_NR AND STATIONIERUNG BETWEEN :START_KM AND :END_KM -select.abflusstafel = SELECT ABFLUSSTAFEL_NR, \ - ABFLUSSTAFEL_BEZ, \ - strftime('%s', GUELTIG_VON) * 1000 AS GUELTIG_VON, \ - strftime('%s', GUELTIG_BIS) * 1000 AS GUELTIG_BIS, \ - PEGELNULLPUNKT FROM ABFLUSSTAFEL WHERE MESSSTELLE_NR LIKE :number +select.abflusstafel = \ + SELECT ABFLUSSTAFEL_NR, \ + ABFLUSSTAFEL_BEZ, \ + strftime('%s', GUELTIG_VON) * 1000 AS GUELTIG_VON, \ + strftime('%s', GUELTIG_BIS) * 1000 AS GUELTIG_BIS, \ + PEGELNULLPUNKT, \ + BFG_ID \ + FROM ABFLUSSTAFEL \ + WHERE MESSSTELLE_NR LIKE :number select.tafelwert = SELECT TAFELWERT_NR AS id, WASSERSTAND AS w, ABFLUSS AS q FROM TAFELWERT \ WHERE ABFLUSSTAFEL_NR = :number diff -r 9dcc32fc5167 -r d9f1202cef78 flys-aft/src/main/resources/sql/aft-oracle-jdbc-oracledriver.properties --- a/flys-aft/src/main/resources/sql/aft-oracle-jdbc-oracledriver.properties Sat Jan 05 13:46:27 2013 +0100 +++ b/flys-aft/src/main/resources/sql/aft-oracle-jdbc-oracledriver.properties Sat Jan 05 14:52:55 2013 +0100 @@ -1,6 +1,9 @@ -select.abflusstafel = SELECT ABFLUSSTAFEL_NR, \ - ABFLUSSTAFEL_BEZ, \ - GUELTIG_VON, \ - GUELTIG_BIS, \ - PEGELNULLPUNKT FROM ABFLUSSTAFEL WHERE MESSSTELLE_NR LIKE :number - +select.abflusstafel = \ + SELECT ABFLUSSTAFEL_NR, \ + ABFLUSSTAFEL_BEZ, \ + GUELTIG_VON, \ + GUELTIG_BIS, \ + PEGELNULLPUNKT, \ + BFG_ID \ + FROM ABFLUSSTAFEL \ + WHERE MESSSTELLE_NR LIKE :number diff -r 9dcc32fc5167 -r d9f1202cef78 flys-aft/src/main/resources/sql/flys-common.properties --- a/flys-aft/src/main/resources/sql/flys-common.properties Sat Jan 05 13:46:27 2013 +0100 +++ b/flys-aft/src/main/resources/sql/flys-common.properties Sat Jan 05 14:52:55 2013 +0100 @@ -14,12 +14,21 @@ next.timeinterval.id = SELECT NEXTVAL('TIME_INTERVALS_ID_SEQ') AS time_interval_id insert.timeinterval = INSERT INTO time_intervals (id, start_time, stop_time) VALUES (:id, :start_time, :stop_time) next.discharge.id = SELECT NEXTVAL('DISCHARGE_TABLES_ID_SEQ') AS discharge_table_id -insert.dischargetable = INSERT INTO discharge_tables (id, gauge_id, description, kind, time_interval_id) \ - VALUES (:id, :gauge_id, :description, 1, :time_interval_id) +insert.dischargetable = \ + INSERT INTO discharge_tables \ + (id, gauge_id, description, bfg_id, kind, time_interval_id) \ + VALUES (:id, :gauge_id, :description, :bfg_id, 1, :time_interval_id) select.discharge.table.values = SELECT id, w, q FROM discharge_table_values WHERE table_id = :table_id next.discharge.table.values.id = SELECT NEXTVAL('DISCHARGE_TABLE_VALUES_ID_SEQ') AS discharge_table_values_id insert.discharge.table.value = INSERT INTO discharge_table_values (id, table_id, w, q) VALUES (:id, :table_id, :w, :q) delete.discharge.table.value = DELETE FROM discharge_table_values WHERE id = :id -select.gauge.discharge.tables = SELECT dt.id AS id, dt.description AS description, ti.start_time AS start_time, ti.stop_time AS stop_time \ - FROM discharge_tables dt LEFT OUTER JOIN time_intervals ti ON dt.time_interval_id = ti.id \ - WHERE gauge_id = :gauge_id +select.gauge.discharge.tables = \ + SELECT \ + dt.id AS id, \ + dt.description AS description, \ + ti.start_time AS start_time, \ + ti.stop_time AS stop_time, \ + dt.bfg_id AS bfg_id \ + FROM discharge_tables dt \ + LEFT OUTER JOIN time_intervals ti ON dt.time_interval_id = ti.id \ + WHERE gauge_id = :gauge_id