changeset 4096:82f5266f881b

Add code to build the difference of the W/Q values of two discharge tables. flys-aft/trunk@3617 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Fri, 06 Jan 2012 17:16:46 +0000
parents da9df3641578
children 83c72bc07591
files flys-aft/ChangeLog flys-aft/src/main/java/de/intevation/aft/WQ.java flys-aft/src/main/java/de/intevation/aft/WQDiff.java flys-aft/src/main/resources/sql/flys-common.properties
diffstat 4 files changed, 137 insertions(+), 4 deletions(-) [+]
line wrap: on
line diff
--- a/flys-aft/ChangeLog	Fri Jan 06 12:10:40 2012 +0000
+++ b/flys-aft/ChangeLog	Fri Jan 06 17:16:46 2012 +0000
@@ -1,3 +1,17 @@
+2012-01-06	Sascha L. Teichmann	<sascha.teichmann@inteavtion.de>
+
+	* src/main/java/de/intevation/aft/WQDiff.java: New.
+	  Calculates the difference of two W/Q value table of a
+	  discharge table. This can be used to write an optimized
+	  change set in terms of executed SQL to the FLYS database.
+
+	* src/main/java/de/intevation/aft/WQ.java: Changed the EPS_CMP
+	  comparator to first sort by Q and then by W because the Qs
+	  are more distinct and the dominant component.
+
+	* src/main/resources/sql/flys-common.properties: Added statement
+	  to delete W/Q values.
+
 2012-01-06	Sascha L. Teichmann	<sascha.teichmann@inteavtion.de>
 
 	* src/main/java/de/intevation/utils/XML.java: Added code
--- a/flys-aft/src/main/java/de/intevation/aft/WQ.java	Fri Jan 06 12:10:40 2012 +0000
+++ b/flys-aft/src/main/java/de/intevation/aft/WQ.java	Fri Jan 06 17:16:46 2012 +0000
@@ -6,12 +6,12 @@
 {
     public static final double EPSILON = 1e-4;
 
-    public static final Comparator<WQ> WQ_EPS_CMP = new Comparator<WQ>() {
+    public static final Comparator<WQ> EPS_CMP = new Comparator<WQ>() {
         @Override
         public int compare(WQ a, WQ b) {
-            int cmp = compareEpsilon(a.w, b.w);
+            int cmp = compareEpsilon(a.q, b.q);
             if (cmp != 0) return cmp;
-            return compareEpsilon(a.q, b.q);
+            return compareEpsilon(a.w, b.w);
         }
     };
 
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/flys-aft/src/main/java/de/intevation/aft/WQDiff.java	Fri Jan 06 17:16:46 2012 +0000
@@ -0,0 +1,118 @@
+package de.intevation.aft;
+
+import java.util.Collection;
+import java.util.Set;
+import java.util.TreeSet;
+import java.util.Iterator;
+
+import java.sql.ResultSet;
+import java.sql.SQLException;
+
+import de.intevation.db.ConnectedStatements;
+import de.intevation.db.SymbolicStatement;
+
+public class WQDiff
+{
+    protected Set<WQ> toAdd;
+    protected Set<WQ> toDelete;
+
+    public WQDiff() {
+    }
+
+    public WQDiff(Collection<WQ> a, Collection<WQ> b) {
+        toAdd    = new TreeSet<WQ>(WQ.EPS_CMP);
+        toDelete = new TreeSet<WQ>(WQ.EPS_CMP);
+        build(a, b);
+    }
+
+    public void build(Collection<WQ> a, Collection<WQ> b) {
+        toAdd.addAll(b);
+        toAdd.removeAll(a);
+
+        toDelete.addAll(a);
+        toDelete.removeAll(b);
+    }
+
+    public void clear() {
+        toAdd.clear();
+        toDelete.clear();
+    }
+
+    public Set<WQ> getToAdd() {
+        return toAdd;
+    }
+
+    public void setToAdd(Set<WQ> toAdd) {
+        this.toAdd = toAdd;
+    }
+
+    public Set<WQ> getToDelete() {
+        return toDelete;
+    }
+
+    public void setToDelete(Set<WQ> toDelete) {
+        this.toDelete = toDelete;
+    }
+
+    public boolean hasChanges() {
+        return !(toAdd.isEmpty() && toDelete.isEmpty());
+    }
+
+    public void writeChanges(
+        SyncContext context, 
+        int         tableId
+    )
+    throws SQLException
+    {
+        ConnectedStatements flysStatements = context.getFlysStatements();
+
+        // Delete the old entries
+        if (!toDelete.isEmpty()) {
+            SymbolicStatement.Instance deleteDTV =
+                flysStatements.getStatement("delete.discharge.table.value");
+            for (WQ wq: toDelete) {
+                deleteDTV
+                    .clearParameters()
+                    .setInt("id", wq.getId())
+                    .execute();
+            }
+        }
+
+        // Add the new entries.
+        if (!toAdd.isEmpty()) {
+            SymbolicStatement.Instance nextId =
+                flysStatements.getStatement("next.discharge.table.values.id");
+
+            SymbolicStatement.Instance insertDTV =
+                flysStatements.getStatement("insert.discharge.table.value");
+
+            // Recycle old ids as much as possible.
+            Iterator<WQ> oldIds = toDelete.iterator();
+
+            // Create ids for new entries.
+            for (WQ wq: toAdd) {
+                if (oldIds.hasNext()) {
+                    wq.setId(oldIds.next().getId());
+                }
+                else {
+                    ResultSet rs = nextId.executeQuery();
+                    rs.next();
+                    wq.setId(rs.getInt("discharge_table_values_id"));
+                    rs.close();
+                }
+            }
+
+            // Write the new entries.
+            for (WQ wq: toAdd) {
+                insertDTV
+                    .clearParameters()
+                    .setInt("id", wq.getId())
+                    .setInt("table_id", tableId)
+                    .setDouble("w", wq.getW())
+                    .setDouble("q", wq.getQ())
+                    .execute();
+            }
+        }
+    }
+}
+// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :
--- a/flys-aft/src/main/resources/sql/flys-common.properties	Fri Jan 06 12:10:40 2012 +0000
+++ b/flys-aft/src/main/resources/sql/flys-common.properties	Fri Jan 06 17:16:46 2012 +0000
@@ -11,4 +11,5 @@
                         VALUES (:id, :gauge_id, :description, 0, :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_tables (id, table_id, w, q) VALUES (:id, :table_id, :w, :q)
+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

http://dive4elements.wald.intevation.org