changeset 4090:d556e29592f5

Create new discharge tables if needed. flys-aft/trunk@3590 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 04 Jan 2012 17:59:26 +0000
parents 859b4781554a
children a91c7e982c32
files flys-aft/ChangeLog flys-aft/src/main/java/de/intevation/aft/DischargeTable.java flys-aft/src/main/java/de/intevation/aft/River.java flys-aft/src/main/java/de/intevation/aft/Sync.java flys-aft/src/main/java/de/intevation/aft/SyncContext.java flys-aft/src/main/java/de/intevation/aft/TimeInterval.java flys-aft/src/main/resources/sql/aft-common.properties flys-aft/src/main/resources/sql/flys-common.properties
diffstat 8 files changed, 235 insertions(+), 21 deletions(-) [+]
line wrap: on
line diff
--- 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	<sascha.teichmann@inteavtion.de>
+
+	* 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	<sascha.teichmann@inteavtion.de>
 
 	* 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.
--- /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 :
+
--- 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<DischargeTable> dts = new ArrayList<DischargeTable>();
+
         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;
             }
         }
     }
--- 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 :
--- 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<Long, DIPSGauge> numberToGauge;
-    protected Set<TimeInterval>    flysTimeIntervals;
+    protected ConnectedStatements             aftStatements;
+    protected ConnectedStatements             flysStatements;
+    protected Document                        dips;
+
+    protected Map<Long, DIPSGauge>            numberToGauge;
+    protected Map<TimeInterval, TimeInterval> flysTimeIntervals;
 
     public SyncContext() {
     }
@@ -112,11 +112,14 @@
         return map;
     }
 
-    protected Set<TimeInterval> loadTimeIntervals() throws SQLException {
+    protected Map<TimeInterval, TimeInterval> loadTimeIntervals() 
+    throws SQLException {
 
         boolean debug = log.isDebugEnabled();
 
-        Set<TimeInterval> intervals = new TreeSet<TimeInterval>();
+        Map<TimeInterval, TimeInterval> intervals =
+            new TreeMap<TimeInterval, TimeInterval>();
+
         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 :
 
--- 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;
--- 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
--- 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)
+

http://dive4elements.wald.intevation.org