# HG changeset patch # User Sascha L. Teichmann # Date 1325870206 0 # Node ID 82f5266f881b22745b6a98d60705dddc0f5e9156 # Parent da9df364157845a262696edecaee9345fa61c7f3 Add code to build the difference of the W/Q values of two discharge tables. flys-aft/trunk@3617 c6561f87-3c4e-4783-a992-168aeb5c3f6f diff -r da9df3641578 -r 82f5266f881b flys-aft/ChangeLog --- 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 + + * 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 * src/main/java/de/intevation/utils/XML.java: Added code diff -r da9df3641578 -r 82f5266f881b flys-aft/src/main/java/de/intevation/aft/WQ.java --- 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_EPS_CMP = new Comparator() { + public static final Comparator EPS_CMP = new Comparator() { @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); } }; diff -r da9df3641578 -r 82f5266f881b flys-aft/src/main/java/de/intevation/aft/WQDiff.java --- /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 toAdd; + protected Set toDelete; + + public WQDiff() { + } + + public WQDiff(Collection a, Collection b) { + toAdd = new TreeSet(WQ.EPS_CMP); + toDelete = new TreeSet(WQ.EPS_CMP); + build(a, b); + } + + public void build(Collection a, Collection b) { + toAdd.addAll(b); + toAdd.removeAll(a); + + toDelete.addAll(a); + toDelete.removeAll(b); + } + + public void clear() { + toAdd.clear(); + toDelete.clear(); + } + + public Set getToAdd() { + return toAdd; + } + + public void setToAdd(Set toAdd) { + this.toAdd = toAdd; + } + + public Set getToDelete() { + return toDelete; + } + + public void setToDelete(Set 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 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 : diff -r da9df3641578 -r 82f5266f881b flys-aft/src/main/resources/sql/flys-common.properties --- 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