# HG changeset patch # User Sascha L. Teichmann # Date 1323885195 0 # Node ID dbd0b3b1b8b8296f83a5912bff83f0b7510cf9f3 # Parent 100c4e0a45e1980ad6d1814447771921be9b8186 Sync each river. flys-aft/trunk@3419 c6561f87-3c4e-4783-a992-168aeb5c3f6f diff -r 100c4e0a45e1 -r dbd0b3b1b8b8 flys-aft/ChangeLog --- a/flys-aft/ChangeLog Tue Dec 13 14:32:55 2011 +0000 +++ b/flys-aft/ChangeLog Wed Dec 14 17:53:15 2011 +0000 @@ -1,3 +1,27 @@ +2011-12-14 Sascha L. Teichmann + + * src/main/java/de/intevation/db/SymbolicStatement.java: + Added execute(), executeQuery() & Co. + + * src/main/java/de/intevation/aft/IdPair.java: New. Base class + for id pairs to identify same object in both databases. + + * src/main/java/de/intevation/aft/River.java: New. To sync + the objects of one river. + + * src/main/java/de/intevation/aft/Rivers.java: Figure out + only the rivers which are in both databases and sync them. + + * src/main/java/de/intevation/aft/Sync.java: Only pass the + connected statements to the sync. + + * src/main/resources/sql/flys-common.properties: Fixed SQL for + fetching the rivers. + + * pom.xml: Added dependency to PostgreSQL. + + * doc/conf.xml: SQLite needs a driver class. + 2011-12-13 Sascha L. Teichmann * src/main/java/de/intevation/db/ConnectionBuilder.java: Removed diff -r 100c4e0a45e1 -r dbd0b3b1b8b8 flys-aft/doc/conf.xml --- a/flys-aft/doc/conf.xml Tue Dec 13 14:32:55 2011 +0000 +++ b/flys-aft/doc/conf.xml Wed Dec 14 17:53:15 2011 +0000 @@ -12,7 +12,7 @@ - + org.sqlite.JDBC jdbc:sqlite:/path/to/aft.db diff -r 100c4e0a45e1 -r dbd0b3b1b8b8 flys-aft/pom.xml --- a/flys-aft/pom.xml Tue Dec 13 14:32:55 2011 +0000 +++ b/flys-aft/pom.xml Wed Dec 14 17:53:15 2011 +0000 @@ -45,6 +45,13 @@ org.xerial sqlite-jdbc 3.7.2 + runtime + + + postgresql + postgresql + 8.4-702.jdbc4 + runtime diff -r 100c4e0a45e1 -r dbd0b3b1b8b8 flys-aft/src/main/java/de/intevation/aft/IdPair.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/src/main/java/de/intevation/aft/IdPair.java Wed Dec 14 17:53:15 2011 +0000 @@ -0,0 +1,36 @@ +package de.intevation.aft; + +public class IdPair +{ + protected int id1; + protected int id2; + + public IdPair() { + } + + public IdPair(int id1, int id2) { + this.id1 = id1; + this.id2 = id2; + } + + public int getId1() { + return id1; + } + + public void setId1(int id1) { + this.id1 = id1; + } + + public int getId2() { + return id2; + } + + public void setId2(int id2) { + this.id2 = id2; + } + + public String toString() { + return "[IdPair: id1=" + id1 + ", id2=" + id2 + "]"; + } +} +// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : diff -r 100c4e0a45e1 -r dbd0b3b1b8b8 flys-aft/src/main/java/de/intevation/aft/River.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/src/main/java/de/intevation/aft/River.java Wed Dec 14 17:53:15 2011 +0000 @@ -0,0 +1,42 @@ +package de.intevation.aft; + +import java.sql.SQLException; + +import org.apache.log4j.Logger; + +import de.intevation.db.ConnectedStatements; + +public class River +extends IdPair +{ + private static Logger log = Logger.getLogger(River.class); + + protected String name; + + public River() { + } + + public River(int id1, int id2, String name) { + super(id1, id2); + this.name = name; + } + + public String getName() { + return name; + } + + public void sync( + ConnectedStatements aftStatements, + ConnectedStatements flysStatements + ) + throws SQLException + { + log.info("sync river: " + this); + } + + public String toString() { + return "[River: name=" + name + ", " + super.toString() + "]"; + } + +} +// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : diff -r 100c4e0a45e1 -r dbd0b3b1b8b8 flys-aft/src/main/java/de/intevation/aft/Rivers.java --- a/flys-aft/src/main/java/de/intevation/aft/Rivers.java Tue Dec 13 14:32:55 2011 +0000 +++ b/flys-aft/src/main/java/de/intevation/aft/Rivers.java Wed Dec 14 17:53:15 2011 +0000 @@ -1,27 +1,67 @@ package de.intevation.aft; +import java.util.Map; +import java.util.HashMap; +import java.util.List; +import java.util.ArrayList; + + import java.sql.SQLException; +import java.sql.ResultSet; -import de.intevation.db.ConnectionBuilder; +import de.intevation.db.ConnectedStatements; + +import org.apache.log4j.Logger; public class Rivers { - protected ConnectionBuilder aftConnectionBuilder; - protected ConnectionBuilder flysConnectionBuilder; + private static Logger log = Logger.getLogger(Rivers.class); - public Rivers( - ConnectionBuilder aftConnectionBuilder, - ConnectionBuilder flysConnectionBuilder - ) { - this.aftConnectionBuilder = aftConnectionBuilder; - this.flysConnectionBuilder = flysConnectionBuilder; + public Rivers() { } - public void sync() throws SQLException { - } + public void sync( + ConnectedStatements aftStatements, + ConnectedStatements flysStatements + ) + throws SQLException + { + log.info("sync: rivers"); - public void close() { + Map flysRivers = new HashMap(); + + ResultSet flysRs = flysStatements + .getStatement("select.river").executeQuery(); + + while (flysRs.next()) { + Integer id = flysRs.getInt("id"); + String name = flysRs.getString("name").toLowerCase(); + flysRivers.put(name, id); + } + + flysRs.close(); + + List commonRivers = new ArrayList(); + + ResultSet aftRs = aftStatements + .getStatement("select.gewaesser").executeQuery(); + + while (aftRs.next()) { + String name = aftRs.getString("NAME"); + Integer id1 = flysRivers.get(name.toLowerCase()); + if (id1 != null) { + int id2 = aftRs.getInt("GEWAESSER_NR"); + River river = new River(id1, id2, name); + System.err.println(river); + commonRivers.add(river); + } + } + + aftRs.close(); + + for (River river: commonRivers) { + river.sync(aftStatements, flysStatements); + } } } // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : - diff -r 100c4e0a45e1 -r dbd0b3b1b8b8 flys-aft/src/main/java/de/intevation/aft/Sync.java --- a/flys-aft/src/main/java/de/intevation/aft/Sync.java Tue Dec 13 14:32:55 2011 +0000 +++ b/flys-aft/src/main/java/de/intevation/aft/Sync.java Wed Dec 14 17:53:15 2011 +0000 @@ -9,6 +9,7 @@ import de.intevation.utils.XML; import de.intevation.db.ConnectionBuilder; +import de.intevation.db.ConnectedStatements; public class Sync { @@ -40,18 +41,25 @@ ConnectionBuilder flysConnectionBuilder = new ConnectionBuilder(FLYS, config); - Rivers rivers = new Rivers( - aftConnectionBuilder, - flysConnectionBuilder); - + ConnectedStatements aftStatements = null; + ConnectedStatements flysStatements = null; try { - rivers.sync(); + aftStatements = aftConnectionBuilder.getConnectedStatements(); + flysStatements = flysConnectionBuilder.getConnectedStatements(); + Rivers rivers = new Rivers(); + rivers.sync(aftStatements, flysStatements); } catch (SQLException sqle) { + sqle.printStackTrace(); System.err.println("syncing failed: " + sqle.getMessage()); } finally { - rivers.close(); + if (aftStatements != null) { + aftStatements.close(); + } + if (flysStatements != null) { + flysStatements.close(); + } } } } diff -r 100c4e0a45e1 -r dbd0b3b1b8b8 flys-aft/src/main/java/de/intevation/db/SymbolicStatement.java --- a/flys-aft/src/main/java/de/intevation/db/SymbolicStatement.java Tue Dec 13 14:32:55 2011 +0000 +++ b/flys-aft/src/main/java/de/intevation/db/SymbolicStatement.java Wed Dec 14 17:53:15 2011 +0000 @@ -12,6 +12,7 @@ import java.sql.SQLException; import java.sql.PreparedStatement; import java.sql.Timestamp; +import java.sql.ResultSet; import org.apache.log4j.Logger; @@ -120,6 +121,18 @@ stmnt.clearParameters(); } + public boolean execute() throws SQLException { + return stmnt.execute(); + } + + public ResultSet executeQuery() throws SQLException { + return stmnt.executeQuery(); + } + + public int executeUpdate() throws SQLException { + return stmnt.executeUpdate(); + } + } // class Instance public SymbolicStatement(String statement) { diff -r 100c4e0a45e1 -r dbd0b3b1b8b8 flys-aft/src/main/resources/sql/flys-common.properties --- a/flys-aft/src/main/resources/sql/flys-common.properties Tue Dec 13 14:32:55 2011 +0000 +++ b/flys-aft/src/main/resources/sql/flys-common.properties Wed Dec 14 17:53:15 2011 +0000 @@ -1,1 +1,1 @@ -select.river.id = SELECT id FROM rivers where lower(name) = lower(:name) +select.river = SELECT id, name FROM rivers