Mercurial > dive4elements > river

changeset 4075:dbd0b3b1b8b8

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Sync each river. flys-aft/trunk@3419 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 14 Dec 2011 17:53:15 +0000
parents 100c4e0a45e1
children 039413d7e394
files flys-aft/ChangeLog flys-aft/doc/conf.xml flys-aft/pom.xml flys-aft/src/main/java/de/intevation/aft/IdPair.java flys-aft/src/main/java/de/intevation/aft/River.java flys-aft/src/main/java/de/intevation/aft/Rivers.java flys-aft/src/main/java/de/intevation/aft/Sync.java flys-aft/src/main/java/de/intevation/db/SymbolicStatement.java flys-aft/src/main/resources/sql/flys-common.properties
diffstat 9 files changed, 191 insertions(+), 21 deletions(-) [+]
line wrap: on
line diff
--- 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	<sascha.teichmann@inteavtion.de>
+
+	* 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	<sascha.teichmann@inteavtion.de>
 
 	* src/main/java/de/intevation/db/ConnectionBuilder.java: Removed 
--- 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 @@
   <!-- The AFT side -->
   <side name="aft">
     <db>
-      <driver/>
+      <driver>org.sqlite.JDBC</driver>
       <user/>
       <password/>
       <url>jdbc:sqlite:/path/to/aft.db</url>
--- 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 @@
       <groupId>org.xerial</groupId>
       <artifactId>sqlite-jdbc</artifactId>
       <version>3.7.2</version>
+      <scope>runtime</scope>
+    </dependency>
+    <dependency>
+      <groupId>postgresql</groupId>
+      <artifactId>postgresql</artifactId>
+      <version>8.4-702.jdbc4</version>
+      <scope>runtime</scope>
     </dependency>
   </dependencies>
 </project>
--- /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 :
--- /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 :
--- 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<String, Integer> flysRivers = new HashMap<String, Integer>();
+
+        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<River> commonRivers = new ArrayList<River>();
+
+        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 :
-
--- 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();
+            }
         }
     }
 }
--- 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) {
--- 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
http://dive4elements.wald.intevation.org