# HG changeset patch # User Sascha L. Teichmann # Date 1325699966 0 # Node ID d556e29592f5fb6f663c6afda1b0d8b9f0167a24 # Parent 859b4781554a04f84e2f3374391384aec12a188d Create new discharge tables if needed. flys-aft/trunk@3590 c6561f87-3c4e-4783-a992-168aeb5c3f6f diff -r 859b4781554a -r d556e29592f5 flys-aft/ChangeLog --- a/flys-aft/ChangeLog Tue Jan 03 12:25:06 2012 +0000 +++ b/flys-aft/ChangeLog Wed Jan 04 17:59:26 2012 +0000 @@ -1,7 +1,29 @@ +2012-01-04 Sascha L. Teichmann + + * src/main/java/de/intevation/aft/SyncContext.java(fetchOrCreateFLYSTimeInterval): + Create FLYS time intervals if they are not in the database. + + * src/main/java/de/intevation/aft/DischargeTable.java: New. Model + for discharge tables. + + * src/main/java/de/intevation/aft/TimeInterval.java: Added + convinience constructors. + + * src/main/java/de/intevation/aft/River.java: Store discharge tables. + + * src/main/java/de/intevation/aft/Sync.java: Exit with errorcode + if syncing fails. + + * src/main/resources/sql/aft-common.properties: Fetch the + description of a discharge table, too. + + * src/main/resources/sql/flys-common.properties: Added statements + to create time intevals and discharge tables. + 2012-01-03 Sascha L. Teichmann * src/main/java/de/intevation/aft/TimeInterval.java: New. - Model for FLYS time intervls. + Model for FLYS time intervals. * src/main/java/de/intevation/aft/SyncContext.java: Preload existing time intervals from FLYS. diff -r 859b4781554a -r d556e29592f5 flys-aft/src/main/java/de/intevation/aft/DischargeTable.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/src/main/java/de/intevation/aft/DischargeTable.java Wed Jan 04 17:59:26 2012 +0000 @@ -0,0 +1,66 @@ +package de.intevation.aft; + +public class DischargeTable +{ + protected int id; + protected int gaugeId; + protected TimeInterval timeInterval; + protected String description; + + public DischargeTable() { + } + + public DischargeTable( + int gaugeId, + TimeInterval timeInterval, + String description + ) { + this.gaugeId = gaugeId; + this.timeInterval = timeInterval; + this.description = description; + } + + public DischargeTable( + int id, + int gaugeId, + TimeInterval timeInterval, + String description + ) { + this(gaugeId, timeInterval, description); + this.id = id; + } + + public int getId() { + return id; + } + + public void setId(int id) { + this.id = id; + } + + public int getGaugeId() { + return gaugeId; + } + + public void setGaugeId(int gaugeId) { + this.gaugeId = gaugeId; + } + + public TimeInterval getTimeInterval() { + return timeInterval; + } + + public void setTimeInterval(TimeInterval timeInterval) { + this.timeInterval = timeInterval; + } + + public String getDescription() { + return description; + } + + public void setDescription(String description) { + this.description = description; + } +} +// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : + diff -r 859b4781554a -r d556e29592f5 flys-aft/src/main/java/de/intevation/aft/River.java --- a/flys-aft/src/main/java/de/intevation/aft/River.java Tue Jan 03 12:25:06 2012 +0000 +++ b/flys-aft/src/main/java/de/intevation/aft/River.java Wed Jan 04 17:59:26 2012 +0000 @@ -8,6 +8,7 @@ import java.sql.ResultSet; import java.sql.SQLException; +import java.sql.Types; import org.apache.log4j.Logger; @@ -195,6 +196,8 @@ ConnectedStatements flysStatements = context.getFlysStatements(); ConnectedStatements aftStatements = context.getAftStatements(); + List dts = new ArrayList(); + ResultSet rs = null; try { rs = aftStatements.getStatement("select.abflusstafel") @@ -203,24 +206,89 @@ executeQuery(); while (rs.next()) { - int dtId = rs.getInt("ABFLUSSTAFEL_NR"); - Date from = rs.getDate("GUELTIG_VON"); - Date to = rs.getDate("GUELTIG_BIS"); + int dtId = rs.getInt("ABFLUSSTAFEL_NR"); + Date from = rs.getDate("GUELTIG_VON"); + Date to = rs.getDate("GUELTIG_BIS"); + String description = rs.getString("ABFLUSSTAFEL_BEZ"); + if (description == null) { + description = String.valueOf(officialNumber); + } double datumValue = rs.getDouble("PEGELNULLPUNKT"); Double datum = rs.wasNull() ? null : datumValue; if (debug) { - log.debug("id: " + dtId); - log.debug("valid from: " + from); - log.debug("valid to: " + to); - log.debug("datum: " + datum); + log.debug("id: " + dtId); + log.debug("valid from: " + from); + log.debug("valid to: " + to); + log.debug("datum: " + datum); + log.debug("description: " + description); + } + + TimeInterval timeInterval = from == null + ? null + : new TimeInterval(from, to); + + DischargeTable dt = new DischargeTable( + gauge.getFlysId(), + timeInterval, + description); + dts.add(dt); + } + } + finally { + if (rs != null) { + rs.close(); + rs = null; + } + } + + // Persist the time intervals. + for (DischargeTable dt: dts) { + TimeInterval timeInterval = dt.getTimeInterval(); + if (timeInterval != null) { + dt.setTimeInterval( + context.fetchOrCreateFLYSTimeInterval(timeInterval)); + } + } + + // Persist the discharge tables + try { + SymbolicStatement.Instance nextId = + flysStatements.getStatement("next.discharge.id"); + + SymbolicStatement.Instance insertDT = + flysStatements.getStatement("insert.dischargetable"); + + for (DischargeTable dt: dts) { + rs = nextId.executeQuery(); + rs.next(); + int id = rs.getInt("discharge_table_id"); + rs.close(); rs = null; + + insertDT.clearParameters() + .setInt("id", id) + .setInt("gauge_id", dt.getGaugeId()) + .setString("description", dt.getDescription()); + + TimeInterval timeInterval = dt.getTimeInterval(); + if (timeInterval != null) { + insertDT.setInt("time_interval_id", timeInterval.getId()); + } + else { + insertDT.setNull("time_interval_id", Types.INTEGER); + } + + insertDT.execute(); + if (debug) { + log.debug("FLYS: Created discharge table id: " + id); } } } finally { if (rs != null) { rs.close(); + rs = null; } } } diff -r 859b4781554a -r d556e29592f5 flys-aft/src/main/java/de/intevation/aft/Sync.java --- a/flys-aft/src/main/java/de/intevation/aft/Sync.java Tue Jan 03 12:25:06 2012 +0000 +++ b/flys-aft/src/main/java/de/intevation/aft/Sync.java Wed Jan 04 17:59:26 2012 +0000 @@ -83,7 +83,8 @@ } } } - + + int exitCode = 0; ConnectionBuilder aftConnectionBuilder = new ConnectionBuilder(AFT, config); @@ -103,12 +104,17 @@ } catch (SQLException sqle) { log.error("syncing failed", sqle); + exitCode = 1; } finally { if (syncContext != null) { syncContext.close(); } } + + if (exitCode != 0) { + System.exit(1); + } } } // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : diff -r 859b4781554a -r d556e29592f5 flys-aft/src/main/java/de/intevation/aft/SyncContext.java --- a/flys-aft/src/main/java/de/intevation/aft/SyncContext.java Tue Jan 03 12:25:06 2012 +0000 +++ b/flys-aft/src/main/java/de/intevation/aft/SyncContext.java Wed Jan 04 17:59:26 2012 +0000 @@ -3,8 +3,7 @@ import java.util.Map; import java.util.Date; import java.util.HashMap; -import java.util.Set; -import java.util.TreeSet; +import java.util.TreeMap; import java.sql.SQLException; import java.sql.ResultSet; @@ -22,11 +21,12 @@ { private static Logger log = Logger.getLogger(SyncContext.class); - protected ConnectedStatements aftStatements; - protected ConnectedStatements flysStatements; - protected Document dips; - protected Map numberToGauge; - protected Set flysTimeIntervals; + protected ConnectedStatements aftStatements; + protected ConnectedStatements flysStatements; + protected Document dips; + + protected Map numberToGauge; + protected Map flysTimeIntervals; public SyncContext() { } @@ -112,11 +112,14 @@ return map; } - protected Set loadTimeIntervals() throws SQLException { + protected Map loadTimeIntervals() + throws SQLException { boolean debug = log.isDebugEnabled(); - Set intervals = new TreeSet(); + Map intervals = + new TreeMap(); + ResultSet rs = null; try { @@ -136,7 +139,7 @@ } TimeInterval ti = new TimeInterval(id, start, stop); - intervals.add(ti); + intervals.put(ti, ti); } } finally { @@ -152,6 +155,44 @@ return intervals; } + public TimeInterval fetchOrCreateFLYSTimeInterval(TimeInterval key) + throws SQLException + { + TimeInterval old = flysTimeIntervals.get(key); + if (old != null) { + return old; + } + + ResultSet rs = null; + try { + rs = flysStatements.getStatement("next.timeinterval.id") + .executeQuery(); + rs.next(); + key.setId(rs.getInt("time_interval_id")); + rs.close(); rs = null; + + flysStatements.getStatement("insert.timeinterval") + .clearParameters() + .setInt("id", key.getId()) + .setObject("start_time", key.getStart()) + .setObject("stop_time", key.getStop()) + .execute(); + + if (log.isDebugEnabled()) { + log.debug("FLYS: Created time interval id: " + key.getId()); + } + } + finally { + if (rs != null) { + rs.close(); + } + } + + flysTimeIntervals.put(key, key); + + return key; + } + } // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : diff -r 859b4781554a -r d556e29592f5 flys-aft/src/main/java/de/intevation/aft/TimeInterval.java --- a/flys-aft/src/main/java/de/intevation/aft/TimeInterval.java Tue Jan 03 12:25:06 2012 +0000 +++ b/flys-aft/src/main/java/de/intevation/aft/TimeInterval.java Wed Jan 04 17:59:26 2012 +0000 @@ -12,12 +12,16 @@ public TimeInterval() { } - public TimeInterval(int id, Date start, Date stop) { - this.id = id; + public TimeInterval(Date start, Date stop) { this.start = start; this.stop = stop; } + public TimeInterval(int id, Date start, Date stop) { + this(start, stop); + this.id = id; + } + protected static int compare(Date d1, Date d2) { long s1 = d1 != null ? d1.getTime()/1000L : 0L; long s2 = d2 != null ? d2.getTime()/1000L : 0L; diff -r 859b4781554a -r d556e29592f5 flys-aft/src/main/resources/sql/aft-common.properties --- a/flys-aft/src/main/resources/sql/aft-common.properties Tue Jan 03 12:25:06 2012 +0000 +++ b/flys-aft/src/main/resources/sql/aft-common.properties Wed Jan 04 17:59:26 2012 +0000 @@ -1,6 +1,7 @@ select.gewaesser = SELECT GEWAESSER_NR, NAME FROM SL_GEWAESSER select.messstelle = SELECT NAME, MESSSTELLE_NR FROM MESSSTELLE WHERE GEWAESSER_NR = :GEWAESSER_NR 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 diff -r 859b4781554a -r d556e29592f5 flys-aft/src/main/resources/sql/flys-common.properties --- a/flys-aft/src/main/resources/sql/flys-common.properties Tue Jan 03 12:25:06 2012 +0000 +++ b/flys-aft/src/main/resources/sql/flys-common.properties Wed Jan 04 17:59:26 2012 +0000 @@ -4,3 +4,9 @@ insert.gauge = INSERT INTO gauges (id, name, river_id, station, aeo, official_number, datum) \ VALUES(:id, :name, :river_id, :station, :aeo, :official_number, :datum) select.timeintervals = SELECT id, start_time, stop_time FROM time_intervals +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, 0, :time_interval_id) +