# HG changeset patch # User Thomas Arendsen Hein # Date 1349960050 -7200 # Node ID ae5119da92cd62f1e7ba0e20e8a4f9c641b6158c # Parent f02aa4ff3c0f360f243334d31ccb694dd4c97442# Parent f72c253663fc1328b992445a8ffd37387a2d45ee merged flys-aft diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/ChangeLog --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/ChangeLog Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,425 @@ +2012-09-11 Sascha L. Teichmann + + * README.txt: Added infos how to build. + + * doc/conf-oracle.xml: Demo config for Oracle. + + * bin/run.sh: New start script. + * bin/log4j.properties: Demo log4j config. + + * pom.xml: Added config for Maven assembly plugin. + * pom-oracle.xml: New. Has extra dependency to Oracle JDBC. + +2012-09-11 Sascha L. Teichmann + + * pom.xml: Java 1.5 -> 1.6 + * README.txt: Removed new line. + +2012-02-16 Sascha L. Teichmann + + * README.txt: Describe configuration and function. TODO: + Write about running. + +2012-02-16 Sascha L. Teichmann + + * README.txt: New. Contains error messages by now. TODO: Write + more about the whole process. + + * src/main/java/de/intevation/aft/SyncContext.java, + src/main/java/de/intevation/aft/DischargeTable.java, + src/main/java/de/intevation/aft/Notification.java, + src/main/java/de/intevation/aft/River.java, + src/main/java/de/intevation/aft/Sync.java: + Adjusted and improved error messages. + +2012-02-08 Sascha L. Teichmann + + * src/main/resources/sql/flys-common.properties: Insert + new discharge tables as 'Historische Abflusstafel' kind. + +2012-01-11 Sascha L. Teichmann + + * src/main/resources/sql/flys-oracle-jdbc-oracledriver.properties: + Added 'FROM DUAL' clause when selecting new ids from sequences. + Sync process between AFT(Oracle) and FLYS(Oracle) is working now! + +2012-01-10 Sascha L. Teichmann + + * src/main/resources/sql/flys-oracle-jdbc-oracledriver.properties: New. + Statements to make the FLYS database connection Oracle compatible. + Untested! + +2012-01-10 Sascha L. Teichmann + + * src/main/resources/sql/aft-oracle-jdbc-oracledriver.properties: New. + Statements to make the AFT database connection Oracle compatible. + +2012-01-09 Sascha L. Teichmann + + * src/main/java/de/intevation/aft/DischargeTable.java: Write + warning if there are discharge tables with same descriptions + in FLYS or AFT and ignore the redundant ones. This led + to an ever growing FLYS database. + +2012-01-09 Sascha L. Teichmann + + * src/main/java/de/intevation/aft/Sync.java: Log if modifications + are found or not. + + * src/main/java/de/intevation/aft/River.java: Commit/rollback + changes on gauge if a gauge is updated. + +2012-01-09 Sascha L. Teichmann + + * src/main/java/de/intevation/aft/DischargeTable.java: Store + the W/Q values in sets to prevent value duplications leading + to unique constraint violations in FLYS. Log a warning + when loading a W/Q value duplication. + + This have the nice side effect that the W/Q values are + written sorted by Q/W which is of benefit for FLYS. + +2012-01-09 Sascha L. Teichmann + + * src/main/java/de/intevation/aft/River.java: Fixed logic bug + when writing discharge tables of an gauge existing in both dbs. + + * src/main/java/de/intevation/aft/DischargeTable.java: Moved + some SQL code from River here to simplify the persistence. + +2012-01-09 Sascha L. Teichmann + + * src/main/java/de/intevation/aft/TimeInterval.java(toString): Added + toString() method. + + * src/main/java/de/intevation/aft/SyncContext.java: Added debug + logging when creating a new time inteval. + + * src/main/java/de/intevation/aft/DischargeTable.java: Added + warning when start and end of a time interval from AFT + are ordered start > end. + +2012-01-07 Sascha L. Teichmann + + * src/main/java/de/intevation/db/SymbolicStatement.java: + Added setLong() method. Used when setting the official number + of a gauge. + + * src/main/java/de/intevation/aft/River.java: Store the new + discharge tables in FLYS when gauges exist in both + FLYS and AFT and there are discharge tables that are only in AFT. + Store official number as long. + +2012-01-07 Sascha L. Teichmann + + * src/main/java/de/intevation/aft/River.java: Store + the W/Q differences of existing discharge tables + to the FLYS database. + + * src/main/java/de/intevation/aft/DischargeTable.java: Added + getter/setter for W/Q values. + +2012-01-06 Sascha L. Teichmann + + * src/main/java/de/intevation/aft/River.java: Do the pairing + of discharge table of a gauge that needs updates. TODO: + Build the W/Q difference of found FLYS/AFT matches and + create the discharge tables in FLYS that are found in AFT. + +2012-01-06 Sascha L. Teichmann + + * src/main/resources/sql/flys-common.properties: Added statement + to load all discharge tables of a given gauge. + + * src/main/java/de/intevation/aft/DischargeTable.java: + + * src/main/java/de/intevation/aft/DIPSGauge.java: Store + the official number, too. + + * src/main/java/de/intevation/aft/River.java: In case of + updating a gauge load all discharge tables of that gauge + from FLYS and AFT. TODO: Do pairing based on the descriptions. + + * src/main/java/de/intevation/aft/DischargeTable.java: Code + to load the discharge table from FLYS and AFT. + +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 + to send/receive documents from streams. + + * src/main/java/de/intevation/aft/Notification.java: New. + Sends XML documents via HTTP POST to given URLs. + + * src/main/java/de/intevation/aft/Sync.java: Send notifications + if the FLYS database was modified. Useful to invalidate caches + in the artifact server. + +2012-01-05 Sascha L. Teichmann + + * doc/conf.xml: Added demo notification url. + + * src/main/java/de/intevation/aft/River.java, + src/main/java/de/intevation/aft/Rivers.java, + src/main/java/de/intevation/aft/Sync.java: Modifications + are bubbled up to main() to send notifactions. + +2012-01-05 Sascha L. Teichmann + + * src/main/java/de/intevation/aft/DischargeTable.java: Store + W/Q values to FLYS. + + * src/main/resources/sql/flys-common.properties: Added statements + to store W/Q values into FLYS database. + +2012-01-05 Sascha L. Teichmann + + * src/main/java/de/intevation/aft/WQ.java: New. W/Q model used + for AFT and FLYS. + + * src/main/java/de/intevation/aft/DischargeTable.java: Holds + a list of its W/Q values now. Values are loadable from AFT + and FLYS. + + * src/main/resources/sql/aft-common.properties, + src/main/resources/sql/flys-common.properties: Added statements + to load W/Q values for a given discharge table. + +2012-01-04 Sascha L. Teichmann + + * 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 + + * src/main/java/de/intevation/aft/TimeInterval.java: New. + Model for FLYS time intervals. + + * src/main/java/de/intevation/aft/SyncContext.java: Preload + existing time intervals from FLYS. + + * src/main/java/de/intevation/aft/Sync.java: Call init() + after construction to ensure that the db connections are + closed properly. + + * src/main/resources/sql/flys-common.properties: Added statement + to fetch the time intervals from FLYS. + +2012-01-03 Sascha L. Teichmann + + * src/main/java/de/intevation/aft/River.java: Fetch discharge table + infos from AFT. + + * src/main/resources/sql/aft-common.properties: Added statement to fetch + infos from ABFLUSSTAFEL. + +2012-01-02 Sascha L. Teichmann + + * src/main/resources/sql/flys-common.properties: Added statements + to create gauges in FLYS. + + * src/main/java/de/intevation/aft/DIPSGauge.java: Make more fields + accessible for gauge creation in FLYS. + + * src/main/java/de/intevation/aft/River.java: Store new gauges + in FLYS. + + * src/main/java/de/intevation/db/ConnectedStatements.java: + Added logging, make methods of transaction handling public. + + * src/main/java/de/intevation/db/SymbolicStatement.java(setDouble): + Fixed argument type problem. + +2012-01-02 Sascha L. Teichmann + + * src/main/java/de/intevation/db/ConnectionBuilder.java: + Set auto commit of new connection to false to enable transaction. + + * src/main/java/de/intevation/db/ConnectedStatements.java: + Added methods to begin, commit and rollback transactions. + Relies on savepoint support which is check by database metadata. + +2011-12-22 Sascha L. Teichmann + + * src/main/java/de/intevation/aft/River.java: Figure out + which gauges must be updated, which must be created. + + * src/main/java/de/intevation/aft/DIPSGauge.java: Store + info from AFT and FLYS, too. + + * src/main/resources/sql/flys-common.properties: Fetch the + official number, too. +2011-12-20 Sascha L. Teichmann + + * src/main/java/de/intevation/aft/River.java: Removed + index DIPS gauge number -> DIPS gauge. + + * src/main/java/de/intevation/aft/SyncContext.java: Readded + here, because the index can be shared by all rivers. + +2011-12-20 Sascha L. Teichmann + + * doc/repair.xsl: Repair XSL transform which brings the + DIPS gauge numbers of the 15 FLYS gauges to the same + numbers as they are used in "Pegel Online". + + !!! The purpose of this script is to do more repairing !!! + + * doc/pegelstationen.xml: Sub document of repair. Used + for lookup the correct pegel numbers. + + * doc/conf.xml: Changed to optionally load the repair XSLT. + + * src/main/java/de/intevation/aft/Sync.java: Load the + repair XSL transformation if configured. + + * src/main/java/de/intevation/utils/XML.java: Added code + to make XSL transforms possible. + + * src/main/java/de/intevation/aft/River.java, + src/main/java/de/intevation/aft/Rivers.java: Fixed logging. + +2011-12-20 Sascha L. Teichmann + + * src/main/java/de/intevation/aft/River.java, + src/main/java/de/intevation/aft/DIPSGauge.java: Make DIPS check + more verbose. + +2011-12-16 Sascha L. Teichmann + + * src/main/java/de/intevation/db/SymbolicStatement.java: + Made the setX() methods cascadable. + + * src/main/java/de/intevation/aft/River.java: Fetches + the gauges from the database. + + * src/main/resources/sql/aft-common.properties, + src/main/resources/sql/flys-common.properties: Added gauges + statements. + +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 + statements here. + + * src/main/java/de/intevation/db/Statements.java: Added method + to access the hole map of statements. + + * src/main/java/de/intevation/db/ConnectedStatements.java: New. + A cache that binds prepared statements to a connection. + +2011-12-13 Sascha L. Teichmann + + * src/main/java/de/intevation/db/ConnectionBuilder.java: Added + access to Statements. + + * src/main/java/de/intevation/db/SymbolicStatement.java: New. + Made top level from inner class of Statements. + + * src/main/java/de/intevation/db/Statements.java: Moved SymbolicStatement + out to top level class. + + * src/main/java/de/intevation/aft/Rivers.java: Syncing beginns at + river level. + + * src/main/java/de/intevation/aft/Sync.java: Start the syncing with + the rivers of both dbs. + +2011-12-13 Sascha L. Teichmann + + * doc/conf.xml: Adjusted structure to be more generic. + + * src/main/java/de/intevation/utils/XML.java: Allow namespace aware + file parsing. + + * src/main/java/de/intevation/db/ConnectionBuilder.java: New. Evaluate + config and builds a new db connection. + + * src/main/java/de/intevation/aft/Sync.java: Load config file. + + * pom.xml: Added dependency to SQLite JDBC driver. + +2011-12-13 Sascha L. Teichmann + + * doc/conf.xml: New. Configuration file. + +2011-12-13 Sascha L. Teichmann + + * ChangeLog: New. Argh! Forgot to check it in before. + + * src/main/java/de/intevation/utils/XML.java: New. XML/XPath support. + Mainly a stripped down version of + de.intevation.artifacts.common.utils.XMLUtils + +2011-12-12 Sascha L. Teichmann + + * src/main/java/de/intevation/db/Statements.java: Added support + for symbolic prepared statements. + +2011-12-12 Sascha L. Teichmann + + * src/main/java/de/intevation/db/Statements.java: New. Load statements + from ressources. + + * src/main/resources/sql/aft-common.properties: New. Common statements + for the AFT side of the sync. + + * src/main/resources/sql/flys-common.properties: New. Common statements + for the FLYS side of the sync. + + * pom.xml: Added dependency to log4j diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/README.txt --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/README.txt Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,316 @@ +FLYS-AFT: +""""""""" + +Der FLYS-AFT-ETL-Prozessor aktualisiert eine FLYS-Datenbank mithilfe +eines DIPS-XML-Exports und einer AFT-Datenbank in bezug auf Pegel und +Abflusstafeln. + +Vorbedingungen: +--------------- + + * Es existiert ein DIPS-XML-Export unter einen erreichbaren Pfad + im Dateisystem. + + * Es existiert eine AFT-Datenbank mit bekannten Credentials. + + * Es existiert eine FLYS-Datenbank mit bekannten Credentials. + +Bau: +---- + * Maven2 sollte installiert und im Pfad liegen. + ( http://maven.apache.org/docs/2.2.1/release-notes.html ) + + $ mvn --version + Sollte Versionsinformationen ausgeben. + + * Für die Nutzung der Oracle JDBC-Bindings muss das Oracle-Treiber-Jar + in das lokale Maven-Repository installiert werden: + + $ mvn install:install-file -DgroupId=ojdbc5.jar -DartifactId=ojdbc5 \ + -Dversion=0 -Dpackaging=jar -Dfile=ojdbc.jar -DgeneratePom=true + + * Für den eigentlichen Oracle-kompatiblen Bau kann dann folgendes + aufgerufen werden: + + $ mvn -f pom-oracle.xml clean compile assembly:single + + $ cp target/de.intevation.aft-1.0-SNAPSHOT-jar-with-dependencies.jar \ + bin/etl.jar + + * Folgendes führt das fertige Programm dann aus: + + $ bin/run.sh + +Konfiguration: +-------------- + +Zur Konfiguration wird eine Konfiguration-Datei benötigt. Diese wird +standardmässig im aktuellen Arbeitsverzeichnis uter dem Name 'config.xml' +gesucht. Der Pfad zu dieser Datei kann allerdings auch mit der +System-Property config.file gesetzt werden. + +Dies geschieht über den Kommandozeilenparameter "-Dconfig.file=/pfad/zur/config.xml" +im Start-Skript bin/run.sh + +Die Konfigurationsdatei hat folgende Struktur: + + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 +10 +11 +12 +13 /the/path/to/the/dips/file +14 /the/path/to/the/xslt/to/repair/dips +15 +16 +17 +18 +19 oracle.jdbc.OracleDriver +20 flys +21 flys +22 jdbc:oracle:thin:@//localhost:1521/XE +23 +24 +25 +26 +27 +28 oracle.jdbc.OracleDriver +29 aft +30 aft +31 jdbc:oracle:thin:@//localhost:1521/XE +32 +33 +34 + +Sie besteht aus vier Bereichen: + + * DIPS: + Zeile 13: Pfad zur XML-Datei mit dem DIPS-Export + Zeile 14: Pfad zur Reparatur-XSL-Transformation (s.u.). + Dieser ist optional. + * FLYS: + Zeile 19: JDBC-Treiber für den Zugriff auf die FLYS-Datenbank + Zeile 20: DB-Nutzername + Zeile 21: Connection-URL zur FLYS-Datenbank + + * AFT: + Zeile 28: JDBC-Treiber für den Zugriff auf die AFT-Datenbank + Zeile 29: DB-Nutzername + Zeile 30: Connection-URL zur AFT-Datenbank + + * Benachrichtigungen: + Zeile 5: URL des Web-Service, der benachrichtigt werden soll. + Zeile 6-18: Die Nachricht, die an den Web-Service verschickt werden soll. + +Funktionsweise: +--------------- + + Als erstes wird die DIPS-Datei geladen. Ist angegeben, dass + eine Reparatur-XSL-Transformation auf diese angewendet werden + soll, wird diese ebenfalls gelanden und auf das DIPS-Dokument + angewandt. + + !!! Hinweis: Unter doc/repair.xsl findet sich eine Beispiel-Transformation, + !!! Die mithilfe von doc/pegelstationen.xml für die Flüsse + !!! Saar, Mosel und Elbe die Pegelnummern der FLYS-Pegel + !!! auf die Pegelnummernvon Pegel-Online anpasst. + + Die so vorbehandelten DIPS-Daten werden mit der AFT-Datenbank + verbunden. Verbindungspunkt ist hierbei die Pegelnummer, die + in beiden Systemen gleich sein muss. + + Wurde für einzelne Pegel die Verbindung zwischen AFT und DIPS + erfolgreich hergestellt, wird versucht mit der entsprechenden + Pegelnummer auch eine Verbindung zu FLYS hergestellt. + + Werden Pegel in AFT und DIPS gefunden, die sich nicht in FLYS befinden, + werden diese in FLYS angelegt und mit den Abflusstafeln aus AFT + gefüllt. + + Werden Pegel in AFT, DIPS und FLYS gefunden, werde die Abflusstafeln + in FLYS mithilfe von AFT aktualisiert. Die Verbindung der Abflusstafeln + wird über deren Bezeichner hergestellt: + + AFT: "ABFLUSSTAFEL.ABFLUSSTAFEL_BEZ" + FLYS: "discharge_tables.decsription" + + Für alle vorhandenen Paare von AFT/FLYS-Abflusstafeln werden + die W/Q-Werte abgeglichen und FLYS entsprechend aktualisiert. + Abflusstafeln, die in FLYS noch nicht vorhanden sind, werden + in FLYS übernommen. + + Wenn es nach dem Abgleich der AFT- und FLYS-DB eine Veränderung + in FLYS gegeben hat, können an konfigurierbare Web-Dienste + Nachrichten verschickt werden, dass sich Daten geändert haben. + Die FLYS-Applikation selbst bestitzt einen Dienst, der aufgerufen + werden kann, um dessen internen Caches zu invalidieren. + Dies vermeidet Dateninkonsistenzen. + +Fehlermeldungen: +================ + +Wärend die Synchronisationsprozesses können verschiedene Fehler +auftreten. + +Allgemein: +---------- + +SYNC: syncing failed. + + Wärend der Synchronisation ist ein Fehler aufgetreten. Details + finden sich in der Regel oberhalb dieser Fehlermeldung. + +REPAIR: Cannot open DIPS repair XSLT file. + + Die zur Reparatur angegebene XSL-Transformation konnte nicht geladen + werden. + +REPAIR: Fixing DIPS failed. + + Die Anwendung der XSL-Transformation zur Reparatur der DIPS-Daten + ist fehlgeschlagen. Datails hierzu sollten sich oberhalb dieser + Fehlermeldung zu finden sein. + +Benachrichtigung: +----------------- + +NOTIFY: Invalid URL ''. Ignored. + + Die zur Benachrichtigung angegebene URL ist nicht valide und + wird daher ignoriert. + +NOTIFY: '' is not an HTTP(S) connection. + + Die zur Benachrichtigung angegebene URL öffnet keine + HTTP- bzw. HTTPS-Verbindung. + +NOTIFY: Sending message to '' failed. + + Der Versand der Benachrichtigung an die URL ist fehlgeschlagen. + +DIPS: +----- + +DIPS: MESSSTELLE '' not found in DIPS. Gauge number used for lookup: + + Es wurde vergeblich versucht, mithilfe einer AFT-Pegelnummer in DIPS + ein entsprechendes Gegenstück zu finden. + +DIPS: MESSSTELLE '' is assigned to river ''. Needs to be on ''. + + Aus Sicht von AFT wird Messstelle an erwartet. + DIPS ordnet sie aber zu. + +DIPS: Gauge '' has no datum. Ignored. + + Der DIPS-Pegel hat keinen PNP und kann deshalb nicht + importiert werden. + +DIPS: Setting AEO of gauge '' to zero. + + Der AEO-Wert ist bei dem DIPS-Pegel nicht gesetzt und + wird mit Null angenommen. + +DIPS: Setting station of gauge '' to zero. + + Der DIPS-Pegel '' hat keine zugeordnete Stationierung und + es wird angenommen, dass dieser an km 0 liegt. + +DIPS: Station of gauge '' is zero. + + Im Regelfall ist ein Stationierung an km 0 ein Datenfehler. + +DIPS: Cannot find ''. + + Der Pfad zum XML-Dokument mit den DIPS-Daten konnte nicht gefunden + werden. + +DIPS: Cannot load DIPS document. + + Das XML-Dokument mit den DIPS-Daten konnte nicht geladen werden. + +DIPS: '' collides with '' on gauge number . + + In DIPS gibt es zwei Pegel mit NAME1 und NAME2, die dieselbe Pegelnummer + haben. + +DIPS: Gauge '' has invalid gauge number ''. + + Der DIPS-Pegel Name hat eine Pegelnummer , die sich nicht + in einen 64bit-Integer erwandeln lässt. + +AFT: +---- + +AFT: ABFLUSSTAFEL_NR = : > . -> swap + + Eine AFT-Abflusstafel hat vertauschte GUELTIG_VON- und GUELTIG_BIS-Werte. + Diese werden implizit in die zeitlich richtige Reihenfolge gebracht. + +FLYS/AFT: Value duplication w= q=. -> ignore. + + Beim Laden einer Abflusstafel wurden ein W/Q-Duplikat entdeckt + und ignoriert. + +AFT: Invalid MESSSTELLE_NR for MESSSTELLE '': + + Die Messtellen-Nummer für die Messtelle ist ungültig. + Erwartet wird ein String, der sich in einen 64bit-Integer umwandeln lässt. + +AFT: Found discharge table '' with same description. -> ignore. + + In AFT wurde eine Abflusstafel gefunden, die die gleiche Bezeichnung + trägt wie eine andere, die demselben Pegel zugeordnet ist. Somit + ist keine eindeutige Zuordnung möglich. + +FLYS: +----- + +FLYS: Found discharge table '' with same description. -> ignore + + In FLYS wurde eine Abflusstafel gefunden, die die gleiche Bezeichnung + trägt wie eine andere, die demselben Pegel zugeordnet ist. Somit + ist keine eindeutige Zuordnung möglich. + +FLYS: Gauge '' has no official number. Ignored. + + Der Pegel in FYLS hat keinen Pegelnummer und wird deshalb + nicht in Betracht gezogen. + +FLYS: Gauge '' number is not found in AFT/DIPS. + + Der Pegel hat eine Pegelnummer, die aber nicht in AFT/DIPS + zu finden ist. + +FLYS: discharge table has no description. Ignored. + + Die Abflusstafel in FLYS hat keine Beschreibung. Diese wird + allerdings zum Abgleich mit DIPS/AFT benötigt. + +FLYS: Found discharge table '' with same description. -> ignore + + In FLYS wurde eine Abflusstafel gefunden, die die gleiche Bezeichnung + trägt wie eine andere, die demselben Pegel zugeordnet ist. Somit + ist keine eindeutige Zuordnung möglich. + +FLYS: Gauge '' has no official number. Ignored. + + Der Pegel in FYLS hat keinen Pegelnummer und wird deshalb + nicht in Betracht gezogen. + +FLYS: Gauge '' number is not found in AFT/DIPS. + + Der Pegel hat eine Pegelnummer, die aber nicht in AFT/DIPS + zu finden ist. + +FLYS: discharge table has no description. Ignored. + + Die Abflusstafel in FLYS hat keine Beschreibung. Diese wird + allerdings zum Abgleich mit DIPS/AFT benötigt. diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/bin/log4j.properties --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/bin/log4j.properties Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,10 @@ +# Set root logger level to DEBUG and its only appender to A1. +log4j.rootLogger=DEBUG, A1 +log4j.category.org.hibernate=DEBUG + +# A1 is set to be a ConsoleAppender. +log4j.appender.A1=org.apache.log4j.ConsoleAppender + +# A1 uses PatternLayout. +log4j.appender.A1.layout=org.apache.log4j.PatternLayout +log4j.appender.A1.layout.ConversionPattern=%d - %m%n diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/bin/run.sh --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/bin/run.sh Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,8 @@ +#!/bin/bash + +bin_dir=`dirname $0` +bin_dir=`readlink -f $bin_dir` +exec java \ + -Dlog4j.configuration=file://$bin_dir/log4j.properties \ + -Dconfig.file=$bin_dir/../doc/conf-oracle.xml \ + -jar $bin_dir/etl.jar diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/doc/conf-oracle.xml --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/doc/conf-oracle.xml Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,26 @@ + + + + + /path/to/the/DiPs_FLYS_7_1_7_5.xml + /path/to/the/doc/repair.xsl + + + + + oracle.jdbc.OracleDriver + flys + flys + jdbc:oracle:thin:@//localhost:1521/XE + + + + + + oracle.jdbc.OracleDriver + aft + aft + jdbc:oracle:thin:@//localhost:1521/XE + + + diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/doc/conf.xml --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/doc/conf.xml Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,34 @@ + + + + + + + + + + + + + /the/path/to/the/dips/file + /the/path/to/the/xslt/to/repair/dips + + + + + org.postgresql.Driver + flys + flys + jdbc:postgresql://localhost:5432/flys + + + + + + org.sqlite.JDBC + + + jdbc:sqlite:/path/to/aft.db + + + diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/doc/pegelstationen.xml --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/doc/pegelstationen.xml Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,19 @@ + + + + + + + + + + + + + + + + + + + diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/doc/repair.xsl --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/doc/repair.xsl Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,45 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/pom-oracle.xml --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/pom-oracle.xml Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,75 @@ + + + 4.0.0 + de.intevation + de.intevation.aft + 1.0-SNAPSHOT + jar + de.intevation.aft + http://maven.apache.org + + UTF-8 + + + + + org.apache.maven.plugins + maven-compiler-plugin + 2.0.2 + + 1.6 + 1.6 + + + + org.apache.maven.plugins + maven-jar-plugin + + + + de.intevation.aft.Sync + de.intevation.aft + + + + + + maven-assembly-plugin + + + + de.intevation.aft.Sync + + + + jar-with-dependencies + + + + + + + + log4j + log4j + 1.2.14 + + + org.xerial + sqlite-jdbc + 3.7.2 + runtime + + + postgresql + postgresql + 8.4-702.jdbc4 + runtime + + + ojdbc5.jar + ojdbc5 + 0 + + + diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/pom.xml --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/pom.xml Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,70 @@ + + + 4.0.0 + de.intevation + de.intevation.aft + 1.0-SNAPSHOT + jar + de.intevation.aft + http://maven.apache.org + + UTF-8 + + + + + org.apache.maven.plugins + maven-compiler-plugin + 2.0.2 + + 1.6 + 1.6 + + + + org.apache.maven.plugins + maven-jar-plugin + + + + de.intevation.aft.Sync + de.intevation.aft + + + + + + maven-assembly-plugin + + + + de.intevation.aft.Sync + + + + jar-with-dependencies + + + + + + + + log4j + log4j + 1.2.14 + + + org.xerial + sqlite-jdbc + 3.7.2 + runtime + + + postgresql + postgresql + 8.4-702.jdbc4 + runtime + + + diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/src/main/java/de/intevation/aft/DIPSGauge.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/src/main/java/de/intevation/aft/DIPSGauge.java Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,193 @@ +package de.intevation.aft; + +import org.w3c.dom.Element; +import org.w3c.dom.NodeList; + +import java.util.ArrayList; +import java.util.List; +import java.util.Date; +import java.util.Calendar; +import java.util.Collections; +import java.util.Comparator; + +import java.util.regex.Pattern; +import java.util.regex.Matcher; + +import org.apache.log4j.Logger; + +public class DIPSGauge +{ + private static Logger log = Logger.getLogger(DIPSGauge.class); + + public static final Pattern DATE_PATTERN = Pattern.compile( + "(\\d{4})-(\\d{2})-(\\d{2})\\s+(\\d{2}):(\\d{2}):(\\d{2})"); + + public static final Comparator DATE_CMP = new Comparator() { + public int compare(Datum a, Datum b) { + return a.date.compareTo(b.date); + } + }; + + public static class Datum { + + protected double value; + protected Date date; + + public Datum() { + } + + public Datum(Element element) { + value = Double.parseDouble(element.getAttribute("WERT")); + String dateString = element.getAttribute("GUELTIGAB"); + if (dateString.length() == 0) { + throw + new IllegalArgumentException("missing GUELTIGAB attribute"); + } + Matcher m = DATE_PATTERN.matcher(dateString); + if (!m.matches()) { + throw + new IllegalArgumentException("GUELTIGAB does not match"); + } + + int year = Integer.parseInt(m.group(1)); + int month = Integer.parseInt(m.group(2)); + int day = Integer.parseInt(m.group(3)); + int hours = Integer.parseInt(m.group(4)); + int mins = Integer.parseInt(m.group(5)); + int secs = Integer.parseInt(m.group(6)); + + Calendar cal = Calendar.getInstance(); + cal.set(year, month, day, hours, mins, secs); + + date = cal.getTime(); + } + + public double getValue() { + return value; + } + + public void setValue(double value) { + this.value = value; + } + + public Date getDate() { + return date; + } + + public void setDate(Date date) { + this.date = date; + } + } // class datum + + protected double aeo; + + protected double station; + + protected String name; + + protected String riverName; + + protected List datums; + + protected int flysId; + + protected String aftName; + + protected Long officialNumber; + + public DIPSGauge() { + } + + public DIPSGauge(Element element) { + + name = element.getAttribute("NAME"); + riverName = element.getAttribute("GEWAESSER"); + + String aeoString = element.getAttribute("EINZUGSGEBIET_AEO"); + if (aeoString.length() == 0) { + log.warn("DIPS: Setting AEO of gauge '" + name + "' to zero."); + aeoString = "0"; + } + aeo = Double.parseDouble(aeoString); + + String stationString = element.getAttribute("STATIONIERUNG"); + if (stationString.length() == 0) { + log.warn("DIPS: Setting station of gauge '" + name + "' to zero."); + stationString = "0"; + } + station = Double.parseDouble(stationString); + if (station == 0d) { + log.warn("DIPS: Station of gauge '" + name + "' is zero."); + } + + datums = new ArrayList(); + NodeList nodes = element.getElementsByTagName("PNP"); + for (int i = 0, N = nodes.getLength(); i < N; ++i) { + Element e = (Element)nodes.item(i); + Datum datum = new Datum(e); + datums.add(datum); + } + Collections.sort(datums, DATE_CMP); + } + + public List getDatums() { + return datums; + } + + public String getName() { + return name; + } + + public String getRiverName() { + return riverName; + } + + public int getFlysId() { + return flysId; + } + + public void setFlysId(int flysId) { + this.flysId = flysId; + } + + public String getAftName() { + return aftName != null ? aftName : name; + } + + public void setAftName(String aftName) { + this.aftName = aftName; + } + + public double getStation() { + return station; + } + + public double getAeo() { + return aeo; + } + + public void setAeo(double aeo) { + this.aeo = aeo; + } + + public void setStation(double station) { + this.station = station; + } + + public boolean hasDatums() { + return !datums.isEmpty(); + } + + public Datum getLatestDatum() { + return datums.get(datums.size()-1); + } + + public Long getOfficialNumber() { + return officialNumber; + } + + public void setOfficialNumber(Long officialNumber) { + this.officialNumber = officialNumber; + } +} +// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/src/main/java/de/intevation/aft/DischargeTable.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/src/main/java/de/intevation/aft/DischargeTable.java Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,322 @@ +package de.intevation.aft; + +import java.util.List; +import java.util.Date; +import java.util.ArrayList; +import java.util.TreeSet; +import java.util.Set; + +import java.sql.SQLException; +import java.sql.ResultSet; +import java.sql.Types; + +import de.intevation.db.SymbolicStatement; +import de.intevation.db.ConnectedStatements; + +import org.apache.log4j.Logger; + +public class DischargeTable +{ + private static Logger log = Logger.getLogger(DischargeTable.class); + + protected int id; + protected int gaugeId; + protected TimeInterval timeInterval; + protected String description; + protected Set values; + + public DischargeTable() { + } + + public DischargeTable( + int gaugeId, + TimeInterval timeInterval, + String description + ) { + this.gaugeId = gaugeId; + this.timeInterval = timeInterval; + this.description = description; + values = new TreeSet(WQ.EPS_CMP); + } + + 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; + } + + public void clearValues() { + values.clear(); + } + + public Set getValues() { + return values; + } + + public void setValues(Set values) { + this.values = values; + } + + + protected void loadValues(SymbolicStatement.Instance query) + throws SQLException + { + ResultSet rs = query.executeQuery(); + while (rs.next()) { + int id = rs.getInt("id"); + double w = rs.getDouble("w"); + double q = rs.getDouble("q"); + if (!values.add(new WQ(id, w, q))) { + log.warn("FLYS/AFT: Value duplication w="+w+" q="+q+". -> ignore."); + } + } + rs.close(); + } + + public void loadAftValues(SyncContext context) throws SQLException { + loadValues(context.getAftStatements() + .getStatement("select.tafelwert") + .clearParameters() + .setInt("number", getId())); + } + + public void loadFlysValues(SyncContext context) throws SQLException { + loadValues(context.getFlysStatements() + .getStatement("select.discharge.table.values") + .clearParameters() + .setInt("table_id", getId())); + } + + public void storeFlysValues( + SyncContext context, + int dischargeTableId + ) + throws SQLException + { + ConnectedStatements flysStatements = context.getFlysStatements(); + + // Create the ids. + SymbolicStatement.Instance nextId = flysStatements + .getStatement("next.discharge.table.values.id"); + + // Insert the values. + SymbolicStatement.Instance insertDTV = flysStatements + .getStatement("insert.discharge.table.value"); + + for (WQ wq: values) { + ResultSet rs = nextId.executeQuery(); + rs.next(); + int wqId = rs.getInt("discharge_table_values_id"); + rs.close(); + + insertDTV + .clearParameters() + .setInt("id", wqId) + .setInt("table_id", dischargeTableId) + .setDouble("w", wq.getW()) + .setDouble("q", wq.getQ()) + .execute(); + } + } + + public static List loadFlysDischargeTables( + SyncContext context, + int gaugeId + ) + throws SQLException + { + List dts = new ArrayList(); + + ResultSet rs = context + .getFlysStatements() + .getStatement("select.gauge.discharge.tables") + .clearParameters() + .setInt("gauge_id", gaugeId) + .executeQuery(); + + OUTER: while (rs.next()) { + int id = rs.getInt("id"); + String description = rs.getString("description"); + if (description == null) { + description = ""; + } + for (DischargeTable dt: dts) { + if (dt.getDescription().equals(description)) { + log.warn("FLYS: Found discharge table '" + + description + "' with same description. -> ignore"); + continue OUTER; + } + } + Date startTime = rs.getDate("start_time"); + Date stopTime = rs.getDate("stop_time"); + TimeInterval ti = startTime == null + ? null + : new TimeInterval(startTime, stopTime); + + DischargeTable dt = new DischargeTable( + id, gaugeId, ti, description); + dts.add(dt); + } + rs.close(); + + return dts; + } + + public static List loadAftDischargeTables( + SyncContext context, + Long officialNumber + ) + throws SQLException + { + return loadAftDischargeTables(context, officialNumber, 0); + } + + public static List loadAftDischargeTables( + SyncContext context, + Long officialNumber, + int flysGaugeId + ) + throws SQLException + { + List dts = new ArrayList(); + + ResultSet rs = context + .getAftStatements() + .getStatement("select.abflusstafel") + .clearParameters() + .setString("number", "%" + officialNumber) + .executeQuery(); + + OUTER: while (rs.next()) { + int dtId = rs.getInt("ABFLUSSTAFEL_NR"); + Date from = rs.getDate("GUELTIG_VON"); + Date to = rs.getDate("GUELTIG_BIS"); + + if (from != null && to != null && from.compareTo(to) > 0) { + log.warn("AFT: ABFLUSSTAFEL_NR = " + + dtId + ": " + from + " > " + to + ". -> swap"); + Date temp = from; + from = to; + to = temp; + } + + String description = rs.getString("ABFLUSSTAFEL_BEZ"); + if (description == null) { + description = String.valueOf(officialNumber); + } + + for (DischargeTable dt: dts) { + if (dt.getDescription().equals(description)) { + log.warn("AFT: Found discharge table '" + + description + "' with same description. -> ignore."); + continue OUTER; + } + } + + double datumValue = rs.getDouble("PEGELNULLPUNKT"); + Double datum = rs.wasNull() ? null : datumValue; + + TimeInterval timeInterval = from == null + ? null + : new TimeInterval(from, to); + + DischargeTable dt = new DischargeTable( + dtId, + flysGaugeId, + timeInterval, + description); + dts.add(dt); + } + rs.close(); + + return dts; + } + + public void persistFlysTimeInterval( + SyncContext context + ) + throws SQLException + { + if (timeInterval != null) { + timeInterval = context.fetchOrCreateFLYSTimeInterval( + timeInterval); + } + } + + public int persistFlysDischargeTable( + SyncContext context, + int gaugeId + ) + throws SQLException + { + ConnectedStatements flysStatements = + context.getFlysStatements(); + + ResultSet rs = flysStatements + .getStatement("next.discharge.id") + .executeQuery(); + + rs.next(); + int flysId = rs.getInt("discharge_table_id"); + rs.close(); + + SymbolicStatement.Instance insertDT = flysStatements + .getStatement("insert.dischargetable") + .clearParameters() + .setInt("id", flysId) + .setInt("gauge_id", gaugeId) + .setString("description", description); + + if (timeInterval != null) { + insertDT.setInt("time_interval_id", timeInterval.getId()); + } + else { + insertDT.setNull("time_interval_id", Types.INTEGER); + } + + insertDT.execute(); + + if (log.isDebugEnabled()) { + log.debug("FLYS: Created discharge table id: " + id); + } + + return flysId; + } +} +// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : diff -r f02aa4ff3c0f -r ae5119da92cd 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 Thu Oct 11 14:54:10 2012 +0200 @@ -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 f02aa4ff3c0f -r ae5119da92cd flys-aft/src/main/java/de/intevation/aft/Notification.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/src/main/java/de/intevation/aft/Notification.java Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,101 @@ +package de.intevation.aft; + +import de.intevation.utils.XML; + +import java.io.IOException; +import java.io.InputStream; +import java.io.OutputStream; + +import java.net.URL; +import java.net.URLConnection; +import java.net.HttpURLConnection; + +import org.w3c.dom.Document; +import org.w3c.dom.Node; +import org.w3c.dom.NodeList; + +import org.apache.log4j.Logger; + +public class Notification +{ + private static Logger log = Logger.getLogger(Notification.class); + + protected Document message; + + public Notification() { + } + + public Notification(Document message) { + this.message = message; + } + + public Notification(Node message) { + this(wrap(message)); + } + + public static Document wrap(Node node) { + Document document = XML.newDocument(); + + // Send first element as message. + // Fall back to root node. + Node toImport = node; + + NodeList children = node.getChildNodes(); + for (int i = 0, N = children.getLength(); i < N; ++i) { + Node child = children.item(i); + if (child.getNodeType() == Node.ELEMENT_NODE) { + toImport = child; + break; + } + } + + toImport = document.importNode(toImport, true); + document.appendChild(toImport); + document.normalizeDocument(); + return document; + } + + public Document sendPOST(URL url) { + + OutputStream out = null; + InputStream in = null; + Document result = null; + + try { + URLConnection ucon = url.openConnection(); + + if (!(ucon instanceof HttpURLConnection)) { + log.warn("NOTIFY: '" + url + "' is not an HTTP(S) connection."); + return null; + } + + HttpURLConnection con = (HttpURLConnection)ucon; + + con.setRequestMethod("POST"); + con.setDoInput(true); + con.setDoOutput(true); + con.setUseCaches(false); + con.setRequestProperty("Content-Type", "text/xml"); + + out = con.getOutputStream(); + XML.toStream(message, out); + out.flush(); + in = con.getInputStream(); + result = XML.parseDocument(in); + } + catch (IOException ioe) { + log.error("NOTIFY: Sending message to '" + url + "' failed.", ioe); + } + finally { + if (out != null) { + try { out.close(); } catch (IOException ioe) {} + } + if (in != null) { + try { in.close(); } catch (IOException ioe) {} + } + } + + return result; + } +} +// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : diff -r f02aa4ff3c0f -r ae5119da92cd 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 Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,400 @@ +package de.intevation.aft; + +import java.util.List; +import java.util.ArrayList; +import java.util.HashMap; +import java.util.Map; + +import java.sql.ResultSet; +import java.sql.SQLException; + +import org.apache.log4j.Logger; + +import de.intevation.db.ConnectedStatements; +import de.intevation.db.SymbolicStatement; + +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 boolean sync(SyncContext context) throws SQLException { + log.info("sync river: " + this); + + Map dipsGauges = context.getDIPSGauges(); + + ConnectedStatements flysStatements = context.getFlysStatements(); + ConnectedStatements aftStatements = context.getAftStatements(); + + ResultSet messstellenRs = aftStatements + .getStatement("select.messstelle") + .clearParameters() + .setInt("GEWAESSER_NR", id2).executeQuery(); + + String riverName = getName(); + + Map aftDIPSGauges = new HashMap(); + + while (messstellenRs.next()) { + String name = messstellenRs.getString("NAME"); + String num = messstellenRs.getString("MESSSTELLE_NR"); + Long number = SyncContext.numberToLong(num); + if (number == null) { + log.warn("AFT: Invalid MESSSTELLE_NR for MESSSTELLE '"+name+"'"); + continue; + } + DIPSGauge dipsGauge = dipsGauges.get(number); + if (dipsGauge == null) { + log.warn( + "DIPS: MESSSTELLE '" + name + "' not found in DIPS. " + + "Gauge number used for lookup: " + number); + continue; + } + String gaugeRiver = dipsGauge.getRiverName(); + if (!gaugeRiver.equalsIgnoreCase(riverName)) { + log.warn( + "DIPS: MESSSTELLE '" + name + + "' is assigned to river '" + gaugeRiver + + "'. Needs to be on '" + riverName + "'."); + continue; + } + dipsGauge.setAftName(name); + dipsGauge.setOfficialNumber(number); + aftDIPSGauges.put(number, dipsGauge); + } + + messstellenRs.close(); + + List updateGauges = new ArrayList(); + + ResultSet gaugesRs = flysStatements + .getStatement("select.gauges") + .clearParameters() + .setInt("river_id", id1).executeQuery(); + + while (gaugesRs.next()) { + int gaugeId = gaugesRs.getInt("id"); + String name = gaugesRs.getString("name"); + long number = gaugesRs.getLong("official_number"); + if (gaugesRs.wasNull()) { + log.warn("FLYS: Gauge '" + name + + "' has no official number. Ignored."); + continue; + } + Long key = Long.valueOf(number); + DIPSGauge aftDIPSGauge = aftDIPSGauges.remove(key); + if (aftDIPSGauge == null) { + log.warn("FLYS: Gauge '" + name + "' number " + number + + " is not found in AFT/DIPS."); + continue; + } + aftDIPSGauge.setFlysId(gaugeId); + log.info("Gauge '" + name + + "' found in FLYS, AFT and DIPS. -> Update"); + updateGauges.add(aftDIPSGauge); + } + gaugesRs.close(); + + boolean modified = createGauges(context, aftDIPSGauges); + + modified |= updateGauges(context, updateGauges); + + return modified; + } + + protected boolean updateGauges( + SyncContext context, + List gauges + ) + throws SQLException + { + boolean modified = false; + + for (DIPSGauge gauge: gauges) { + modified |= updateGauge(context, gauge); + } + + return modified; + } + + protected boolean updateGauge( + SyncContext context, + DIPSGauge gauge + ) + throws SQLException + { + log.info("FLYS: Updating gauge '" + gauge.getAftName() + "'."); + // We need to load all discharge tables from both database + // of the gauge and do some pairing based on their descriptions. + + boolean modified = false; + + ConnectedStatements flysStatements = context.getFlysStatements(); + + flysStatements.beginTransaction(); + try { + List flysDTs = + DischargeTable.loadFlysDischargeTables( + context, gauge.getFlysId()); + + List aftDTs = + DischargeTable.loadAftDischargeTables( + context, gauge.getOfficialNumber()); + + Map desc2FlysDT = + new HashMap(); + + for (DischargeTable dt: flysDTs) { + String description = dt.getDescription(); + if (description == null) { + log.warn("FLYS: discharge table " + dt.getId() + + " has no description. Ignored."); + continue; + } + desc2FlysDT.put(description, dt); + } + + List createDTs = new ArrayList(); + + for (DischargeTable aftDT: aftDTs) { + String description = aftDT.getDescription(); + DischargeTable flysDT = desc2FlysDT.remove(description); + if (flysDT != null) { + // Found in AFT and FLYS. + log.info("FLYS: Discharge table '" + description + + "' found in AFT and FLYS. -> update"); + // Create the W/Q diff. + modified |= writeWQChanges(context, flysDT, aftDT); + } + else { + log.info("FLYS: Discharge table '" + description + + "' not found in FLYS. -> create"); + createDTs.add(aftDT); + } + } + + for (String description: desc2FlysDT.keySet()) { + log.info("FLYS: Discharge table '" + description + + "' found in FLYS but not in AFT. -> ignore"); + } + + log.info("FLYS: Copy " + createDTs.size() + + " discharge tables over from AFT."); + + // Create the new discharge tables. + for (DischargeTable aftDT: createDTs) { + createDischargeTable(context, aftDT, gauge.getFlysId()); + modified = true; + } + + flysStatements.commitTransaction(); + } + catch (SQLException sqle) { + flysStatements.rollbackTransaction(); + log.error(sqle, sqle); + modified = false; + } + + return modified; + } + + protected boolean writeWQChanges( + SyncContext context, + DischargeTable flysDT, + DischargeTable aftDT + ) + throws SQLException + { + flysDT.loadFlysValues(context); + aftDT.loadAftValues(context); + WQDiff diff = new WQDiff(flysDT.getValues(), aftDT.getValues()); + if (diff.hasChanges()) { + diff.writeChanges(context, flysDT.getId()); + return true; + } + return false; + } + + protected boolean createGauges( + SyncContext context, + Map gauges + ) + throws SQLException + { + ConnectedStatements flysStatements = context.getFlysStatements(); + + SymbolicStatement.Instance nextId = + flysStatements.getStatement("next.gauge.id"); + + SymbolicStatement.Instance insertStmnt = + flysStatements.getStatement("insert.gauge"); + + boolean modified = false; + + for (Map.Entry entry: gauges.entrySet()) { + Long officialNumber = entry.getKey(); + DIPSGauge gauge = entry.getValue(); + + log.info("Gauge '" + gauge.getAftName() + + "' not in FLYS but in AFT/DIPS. -> Create"); + + if (!gauge.hasDatums()) { + log.warn("DIPS: Gauge '" + + gauge.getAftName() + "' has no datum. Ignored."); + continue; + } + + ResultSet rs = null; + flysStatements.beginTransaction(); + try { + (rs = nextId.executeQuery()).next(); + int gaugeId = rs.getInt("gauge_id"); + rs.close(); rs = null; + + insertStmnt + .clearParameters() + .setInt("id", gaugeId) + .setString("name", gauge.getAftName()) + .setInt("river_id", id1) + .setDouble("station", gauge.getStation()) + .setDouble("aeo", gauge.getAeo()) + .setLong("official_number", officialNumber) + .setDouble("datum", gauge.getLatestDatum().getValue()); + + insertStmnt.execute(); + + log.info("FLYS: Created gauge '" + gauge.getAftName() + + "' with id " + gaugeId + "."); + + gauge.setFlysId(gaugeId); + createDischargeTables(context, gauge); + flysStatements.commitTransaction(); + modified = true; + } + catch (SQLException sqle) { + flysStatements.rollbackTransaction(); + log.error(sqle, sqle); + } + finally { + if (rs != null) { + rs.close(); + } + } + } + + return modified; + } + + protected void createDischargeTable( + SyncContext context, + DischargeTable aftDT, + int flysGaugeId + ) + throws SQLException + { + aftDT.persistFlysTimeInterval(context); + int flysId = aftDT.persistFlysDischargeTable(context, flysGaugeId); + + aftDT.loadAftValues(context); + aftDT.storeFlysValues(context, flysId); + } + + protected void createDischargeTables( + SyncContext context, + DIPSGauge gauge + ) + throws SQLException + { + log.info("FLYS: Create discharge tables for '" + + gauge.getAftName() + "'."); + + // Load the discharge tables from AFT. + List dts = loadAftDischargeTables( + context, gauge); + + // Persist the time intervals. + persistFlysTimeIntervals(context, dts); + + // Persist the discharge tables + int [] flysDTIds = persistFlysDischargeTables( + context, dts, gauge.getFlysId()); + + // Copy over the W/Q values + copyWQsFromAftToFlys(context, dts, flysDTIds); + } + + protected List loadAftDischargeTables( + SyncContext context, + DIPSGauge gauge + ) + throws SQLException + { + return DischargeTable.loadAftDischargeTables( + context, gauge.getOfficialNumber(), gauge.getFlysId()); + } + + protected void persistFlysTimeIntervals( + SyncContext context, + List dts + ) + throws SQLException + { + for (DischargeTable dt: dts) { + dt.persistFlysTimeInterval(context); + } + } + + protected int [] persistFlysDischargeTables( + SyncContext context, + List dts, + int flysGaugeId + ) + throws SQLException + { + boolean debug = log.isDebugEnabled(); + + int [] flysDTIds = new int[dts.size()]; + + for (int i = 0; i < flysDTIds.length; ++i) { + flysDTIds[i] = dts.get(i) + .persistFlysDischargeTable(context, flysGaugeId); + } + + return flysDTIds; + } + + protected void copyWQsFromAftToFlys( + SyncContext context, + List dts, + int [] flysDTIds + ) + throws SQLException + { + for (int i = 0; i < flysDTIds.length; ++i) { + DischargeTable dt = dts.get(i); + dt.loadAftValues(context); + dt.storeFlysValues(context, flysDTIds[i]); + dt.clearValues(); // To save memory. + } + } + + public String toString() { + return "[River: name=" + name + ", " + super.toString() + "]"; + } +} +// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/src/main/java/de/intevation/aft/Rivers.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/src/main/java/de/intevation/aft/Rivers.java Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,69 @@ +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.ConnectedStatements; + +import org.apache.log4j.Logger; + +public class Rivers +{ + private static Logger log = Logger.getLogger(Rivers.class); + + public Rivers() { + } + + public boolean sync(SyncContext context) throws SQLException { + + log.info("sync: rivers"); + + ConnectedStatements flysStatements = context.getFlysStatements(); + ConnectedStatements aftStatements = context.getAftStatements(); + + 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); + commonRivers.add(river); + } + } + + aftRs.close(); + + boolean modified = false; + + for (River river: commonRivers) { + modified |= river.sync(context); + } + + return modified; + } +} +// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/src/main/java/de/intevation/aft/Sync.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/src/main/java/de/intevation/aft/Sync.java Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,170 @@ +package de.intevation.aft; + +import java.io.File; + +import java.net.URL; +import java.net.MalformedURLException; + +import java.sql.SQLException; + +import org.w3c.dom.Document; +import org.w3c.dom.NodeList; +import org.w3c.dom.Element; + +import org.apache.log4j.Logger; + +import javax.xml.xpath.XPathConstants; + +import de.intevation.utils.XML; + +import de.intevation.db.ConnectionBuilder; + +public class Sync +{ + private static Logger log = Logger.getLogger(Sync.class); + + public static final String FLYS = "flys"; + public static final String AFT = "aft"; + + public static final String XPATH_DIPS = "/sync/dips/file/text()"; + public static final String XPATH_REPAIR = "/sync/dips/repair/text()"; + + public static final String XPATH_NOTIFICATIONS = + "/sync/notifications/notification"; + + public static final String CONFIG_FILE = + System.getProperty("config.file", "config.xml"); + + public static void sendNotifications(Document config) { + NodeList notifications = (NodeList)XML.xpath( + config, XPATH_NOTIFICATIONS, XPathConstants.NODESET, null, null); + + if (notifications == null) { + return; + } + + for (int i = 0, N = notifications.getLength(); i < N; ++i) { + Element notification = (Element)notifications.item(i); + String urlString = notification.getAttribute("url"); + + URL url; + try { + url = new URL(urlString); + } + catch (MalformedURLException mfue) { + log.warn("NOTIFY: Invalid URL '" + urlString + "'. Ignored.", mfue); + continue; + } + + Notification n = new Notification(notification); + + Document result = n.sendPOST(url); + + if (result != null) { + log.info("Send notifcation to '" + urlString + "'."); + log.info(XML.toString(result)); + } + } + } + + public static void main(String [] args) { + + File configFile = new File(CONFIG_FILE); + + if (!configFile.isFile() || !configFile.canRead()) { + log.error("cannot read config file"); + System.exit(1); + } + + Document config = XML.parseDocument(configFile, Boolean.FALSE); + + if (config == null) { + log.error("Cannot load config file."); + System.exit(1); + } + + String dipsF = (String)XML.xpath( + config, XPATH_DIPS, XPathConstants.STRING, null, null); + + if (dipsF == null || dipsF.length() == 0) { + log.error("Cannot find path to DIPS XML in config."); + System.exit(1); + } + + File dipsFile = new File(dipsF); + + if (!dipsFile.isFile() || !dipsFile.canRead()) { + log.error("DIPS: Cannot find '" + dipsF + "'."); + System.exit(1); + } + + Document dips = XML.parseDocument(dipsFile, Boolean.FALSE); + + if (dips == null) { + log.error("DIPS: Cannot load DIPS document."); + System.exit(1); + } + + String repairF = (String)XML.xpath( + config, XPATH_REPAIR, XPathConstants.STRING, null, null); + + if (repairF != null && repairF.length() > 0) { + File repairFile = new File(repairF); + if (!repairFile.isFile() || !repairFile.canRead()) { + log.warn("REPAIR: Cannot open DIPS repair XSLT file."); + } + else { + Document fixed = XML.transform(dips, repairFile); + if (fixed == null) { + log.warn("REPAIR: Fixing DIPS failed."); + } + else { + dips = fixed; + } + } + } + + int exitCode = 0; + + ConnectionBuilder aftConnectionBuilder = + new ConnectionBuilder(AFT, config); + + ConnectionBuilder flysConnectionBuilder = + new ConnectionBuilder(FLYS, config); + + SyncContext syncContext = null; + + boolean modified = false; + try { + syncContext = new SyncContext( + aftConnectionBuilder.getConnectedStatements(), + flysConnectionBuilder.getConnectedStatements(), + dips); + syncContext.init(); + Rivers rivers = new Rivers(); + modified = rivers.sync(syncContext); + } + catch (SQLException sqle) { + log.error("SYNC: Syncing failed.", sqle); + exitCode = 1; + } + finally { + if (syncContext != null) { + syncContext.close(); + } + } + + if (modified) { + log.info("Modifications found."); + sendNotifications(config); + } + else { + log.info("No modifications found."); + } + + if (exitCode != 0) { + System.exit(1); + } + } +} +// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/src/main/java/de/intevation/aft/SyncContext.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/src/main/java/de/intevation/aft/SyncContext.java Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,199 @@ +package de.intevation.aft; + +import java.util.Map; +import java.util.Date; +import java.util.HashMap; +import java.util.TreeMap; + +import java.sql.SQLException; +import java.sql.ResultSet; + +import org.w3c.dom.Document; + +import de.intevation.db.ConnectedStatements; + +import org.w3c.dom.NodeList; +import org.w3c.dom.Element; + +import org.apache.log4j.Logger; + +public class SyncContext +{ + private static Logger log = Logger.getLogger(SyncContext.class); + + protected ConnectedStatements aftStatements; + protected ConnectedStatements flysStatements; + protected Document dips; + + protected Map numberToGauge; + protected Map flysTimeIntervals; + + public SyncContext() { + } + + public SyncContext( + ConnectedStatements aftStatements, + ConnectedStatements flysStatements, + Document dips + ) { + this.aftStatements = aftStatements; + this.flysStatements = flysStatements; + this.dips = dips; + } + + public void init() throws SQLException { + numberToGauge = indexByNumber(dips); + flysTimeIntervals = loadTimeIntervals(); + } + + public ConnectedStatements getAftStatements() { + return aftStatements; + } + + public void setAftStatements(ConnectedStatements aftStatements) { + this.aftStatements = aftStatements; + } + + public ConnectedStatements getFlysStatements() { + return flysStatements; + } + + public void setFlysStatements(ConnectedStatements flysStatements) { + this.flysStatements = flysStatements; + } + + public Document getDips() { + return dips; + } + + public void setDips(Document dips) { + this.dips = dips; + } + + void close() { + aftStatements.close(); + flysStatements.close(); + } + + public static Long numberToLong(String s) { + try { + return Long.valueOf(s.trim()); + } + catch (NumberFormatException nfe) { + } + return null; + } + + public Map getDIPSGauges() { + return numberToGauge; + } + + protected static Map indexByNumber(Document document) { + Map map = new HashMap(); + NodeList nodes = document.getElementsByTagName("PEGELSTATION"); + for (int i = nodes.getLength()-1; i >= 0; --i) { + Element element = (Element)nodes.item(i); + String numberString = element.getAttribute("NUMMER"); + Long number = numberToLong(numberString); + if (number != null) { + DIPSGauge newG = new DIPSGauge(element); + DIPSGauge oldG = map.put(number, newG); + if (oldG != null) { + log.warn("DIPS: '" + newG.getName() + + "' collides with '" + oldG.getName() + + "' on gauge number " + number + "."); + } + } + else { + log.warn("DIPS: Gauge '" + element.getAttribute("NAME") + + "' has invalid gauge number '" + numberString + "'."); + } + } + return map; + } + + protected Map loadTimeIntervals() + throws SQLException { + + boolean debug = log.isDebugEnabled(); + + Map intervals = + new TreeMap(); + + ResultSet rs = null; + + try { + rs = flysStatements + .getStatement("select.timeintervals") + .executeQuery(); + + while (rs.next()) { + int id = rs.getInt("id"); + Date start = rs.getDate("start_time"); + Date stop = rs.getDate("stop_time"); + + if (debug) { + log.debug("id: " + id); + log.debug("start: " + start); + log.debug("stop: " + stop); + } + + TimeInterval ti = new TimeInterval(id, start, stop); + intervals.put(ti, ti); + } + } + finally { + if (rs != null) { + rs.close(); + } + } + + if (debug) { + log.debug("loaded time intervals: " + intervals.size()); + } + + 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; + + if (log.isDebugEnabled()) { + log.debug("FLYS: Created time interval id: " + key.getId()); + log.debug("FLYS: " + key); + } + + flysStatements.getStatement("insert.timeinterval") + .clearParameters() + .setInt("id", key.getId()) + .setObject("start_time", key.getStart()) + .setObject("stop_time", key.getStop()) + .execute(); + } + 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 : + diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/src/main/java/de/intevation/aft/TimeInterval.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/src/main/java/de/intevation/aft/TimeInterval.java Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,70 @@ +package de.intevation.aft; + +import java.util.Date; + +public class TimeInterval +implements Comparable +{ + protected int id; + protected Date start; + protected Date stop; + + public TimeInterval() { + } + + 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; + long diff = s1 - s2; + return diff < 0L + ? -1 + : diff > 0L ? 1 : 0; + } + + @Override + public int compareTo(TimeInterval other) { + int cmp = compare(start, other.start); + return cmp != 0 + ? cmp + : compare(stop, other.stop); + } + + public int getId() { + return id; + } + + public void setId(int id) { + this.id = id; + } + + public Date getStart() { + return start; + } + + public void setStart(Date start) { + this.start = start; + } + + public Date getStop() { + return stop; + } + + public void setStop(Date stop) { + this.stop = stop; + } + + public String toString() { + return "[TimeInterval: start=" + start + ", stop=" + stop + "]"; + } +} +// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/src/main/java/de/intevation/aft/WQ.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/src/main/java/de/intevation/aft/WQ.java Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,67 @@ +package de.intevation.aft; + +import java.util.Comparator; + +public class WQ +{ + public static final double EPSILON = 1e-4; + + public static final Comparator EPS_CMP = new Comparator() { + @Override + public int compare(WQ a, WQ b) { + int cmp = compareEpsilon(a.q, b.q); + if (cmp != 0) return cmp; + return compareEpsilon(a.w, b.w); + } + }; + + protected int id; + + protected double w; + protected double q; + + public WQ() { + } + + public WQ(double w, double q) { + this.w = w; + this.q = q; + } + + public WQ(int id, double w, double q) { + this.id = id; + this.w = w; + this.q = q; + } + + public static final int compareEpsilon(double a, double b) { + double diff = a - b; + if (diff < -EPSILON) return -1; + return diff > EPSILON ? +1 : 0; + } + + public int getId() { + return id; + } + + public void setId(int id) { + this.id = id; + } + + public double getW() { + return w; + } + + public void setW(double w) { + this.w = w; + } + + public double getQ() { + return q; + } + + public void setQ(double q) { + this.q = q; + } +} +// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : diff -r f02aa4ff3c0f -r ae5119da92cd 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 Thu Oct 11 14:54:10 2012 +0200 @@ -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 f02aa4ff3c0f -r ae5119da92cd flys-aft/src/main/java/de/intevation/db/ConnectedStatements.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/src/main/java/de/intevation/db/ConnectedStatements.java Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,110 @@ +package de.intevation.db; + +import java.util.HashMap; +import java.util.Map; +import java.util.Deque; +import java.util.ArrayDeque; + +import java.sql.Connection; +import java.sql.SQLException; +import java.sql.Savepoint; +import java.sql.DatabaseMetaData; + +import org.apache.log4j.Logger; + +public class ConnectedStatements +{ + private static Logger log = Logger.getLogger(ConnectedStatements.class); + + protected Connection connection; + + protected Map statements; + + protected Map boundStatements; + + protected Deque savepoints; + + public ConnectedStatements( + Connection connection, + Map statements + ) + throws SQLException + { + this.connection = connection; + this.statements = statements; + checkSavePoints(); + + boundStatements = new HashMap(); + } + + protected void checkSavePoints() throws SQLException { + DatabaseMetaData metaData = connection.getMetaData(); + if (metaData.supportsSavepoints()) { + log.info("Driver '" + metaData.getDriverName() + + "' does support savepoints."); + savepoints = new ArrayDeque(); + } + else { + log.info("Driver '" + metaData.getDriverName() + + "' does not support savepoints."); + } + } + + public SymbolicStatement.Instance getStatement(String key) + throws SQLException + { + SymbolicStatement.Instance stmnt = boundStatements.get(key); + if (stmnt != null) { + return stmnt; + } + + SymbolicStatement ss = statements.get(key); + if (ss == null) { + return null; + } + + stmnt = ss.new Instance(connection); + boundStatements.put(key, stmnt); + return stmnt; + } + + public void beginTransaction() throws SQLException { + if (savepoints != null) { + savepoints.push(connection.setSavepoint()); + } + } + + public void commitTransaction() throws SQLException { + if (savepoints != null) { + savepoints.pop(); + } + connection.commit(); + } + + public void rollbackTransaction() throws SQLException { + if (savepoints != null) { + Savepoint savepoint = savepoints.pop(); + connection.rollback(savepoint); + } + else { + connection.rollback(); + } + } + + public void close() { + for (SymbolicStatement.Instance s: boundStatements.values()) { + s.close(); + } + + try { + if (savepoints != null && !savepoints.isEmpty()) { + Savepoint savepoint = savepoints.peekFirst(); + connection.rollback(savepoint); + } + connection.close(); + } + catch (SQLException sqle) { + } + } +} +// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/src/main/java/de/intevation/db/ConnectionBuilder.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/src/main/java/de/intevation/db/ConnectionBuilder.java Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,93 @@ +package de.intevation.db; + +import de.intevation.utils.XML; + +import java.util.HashMap; + +import org.w3c.dom.Document; + +import javax.xml.xpath.XPathConstants; + +import java.sql.Connection; +import java.sql.SQLException; +import java.sql.DriverManager; +import java.sql.DatabaseMetaData; + +import org.apache.log4j.Logger; + +public class ConnectionBuilder +{ + private static Logger log = Logger.getLogger(ConnectionBuilder.class); + + public static final String XPATH_DRIVER = "/sync/side[@name=$type]/db/driver/text()"; + public static final String XPATH_USER = "/sync/side[@name=$type]/db/user/text()"; + public static final String XPATH_PASSWORD = "/sync/side[@name=$type]/db/password/text()"; + public static final String XPATH_URL = "/sync/side[@name=$type]/db/url/text()"; + + protected String type; + protected String driver; + protected String user; + protected String password; + protected String url; + + public ConnectionBuilder(String type, Document document) { + this.type = type; + extractCredentials(document); + } + + protected void extractCredentials(Document document) { + HashMap map = new HashMap(); + map.put("type", type); + + driver = (String)XML.xpath( + document, XPATH_DRIVER, XPathConstants.STRING, null, map); + user = (String)XML.xpath( + document, XPATH_USER, XPathConstants.STRING, null, map); + password = (String)XML.xpath( + document, XPATH_PASSWORD, XPathConstants.STRING, null, map); + url = (String)XML.xpath( + document, XPATH_URL, XPathConstants.STRING, null, map); + + if (log.isDebugEnabled()) { + log.debug("driver: " + driver); + log.debug("user: " + user); + log.debug("password: *******"); + log.debug("url: " + url); + } + } + + public Connection getConnection() throws SQLException { + + if (driver != null && driver.length() > 0) { + try { + Class.forName(driver); + } + catch (ClassNotFoundException cnfe) { + throw new SQLException(cnfe); + } + } + + Connection connection = + DriverManager.getConnection(url, user, password); + + connection.setAutoCommit(false); + + DatabaseMetaData metaData = connection.getMetaData(); + + if (metaData.supportsTransactionIsolationLevel( + Connection.TRANSACTION_READ_UNCOMMITTED)) { + connection.setTransactionIsolation( + Connection.TRANSACTION_READ_UNCOMMITTED); + } + + return connection; + } + + public ConnectedStatements getConnectedStatements() throws SQLException { + return new ConnectedStatements( + getConnection(), + new Statements(type, driver != null ? driver : "") + .getStatements()); + } +} +// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/src/main/java/de/intevation/db/Statements.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/src/main/java/de/intevation/db/Statements.java Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,125 @@ +package de.intevation.db; + +import java.util.Properties; + +import java.io.IOException; +import java.io.InputStream; + +import org.apache.log4j.Logger; + +import java.util.Map; +import java.util.HashMap; +import java.util.Enumeration; + +public class Statements +{ + private static Logger log = Logger.getLogger(Statements.class); + + public static final String RESOURCE_PATH = "/sql/"; + public static final String COMMON_PROPERTIES = "-common.properties"; + + protected String type; + protected String driver; + + protected Map statements; + + public Statements(String type, String driver) { + this.type = type; + this.driver = driver; + } + + public SymbolicStatement getStatement(String key) { + return getStatements().get(key); + } + + public Map getStatements() { + if (statements == null) { + statements = loadStatements(); + } + return statements; + } + + protected Map loadStatements() { + Map statements = + new HashMap(); + + Properties properties = loadProperties(); + + for (Enumeration e = properties.propertyNames(); e.hasMoreElements();) { + String key = (String)e.nextElement(); + String value = properties.getProperty(key); + SymbolicStatement symbolic = new SymbolicStatement(value); + statements.put(key, symbolic); + } + + return statements; + } + + protected String driverToProperties() { + return + type + "-" + + driver.replace('.', '-').toLowerCase() + ".properties"; + } + + protected Properties loadCommon() { + Properties common = new Properties(); + + String path = RESOURCE_PATH + type + COMMON_PROPERTIES; + + InputStream in = Statements.class.getResourceAsStream(path); + + if (in != null) { + try { + common.load(in); + } + catch (IOException ioe) { + log.error("cannot load defaults: " + path, ioe); + } + finally { + try { + in.close(); + } + catch (IOException ioe) { + } + } + } + else { + log.warn("cannot find: " + path); + } + + return common; + } + + protected Properties loadProperties() { + + Properties common = loadCommon(); + + Properties properties = new Properties(common); + + String path = RESOURCE_PATH + driverToProperties(); + + InputStream in = Statements.class.getResourceAsStream(path); + + if (in != null) { + try { + properties.load(in); + } + catch (IOException ioe) { + log.error("cannot load statements: " + path, ioe); + } + finally { + try { + in.close(); + } + catch (IOException ioe) { + } + } + } + else { + log.warn("cannot find: " + path); + } + + return properties; + } +} +// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/src/main/java/de/intevation/db/SymbolicStatement.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/src/main/java/de/intevation/db/SymbolicStatement.java Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,187 @@ +package de.intevation.db; + +import java.util.List; +import java.util.ArrayList; +import java.util.Map; +import java.util.HashMap; + +import java.util.regex.Matcher; +import java.util.regex.Pattern; + +import java.sql.Connection; +import java.sql.SQLException; +import java.sql.PreparedStatement; +import java.sql.Timestamp; +import java.sql.ResultSet; + +import org.apache.log4j.Logger; + +public class SymbolicStatement { + + private static Logger log = Logger.getLogger(SymbolicStatement.class); + + public static final Pattern VAR = Pattern.compile(":([a-zA-Z0-9_]+)"); + + protected String statement; + protected String compiled; + protected Map> positions; + + public class Instance { + + /** TODO: Support more types. */ + + protected PreparedStatement stmnt; + + public Instance(Connection connection) throws SQLException { + stmnt = connection.prepareStatement(compiled); + } + + public void close() { + try { + stmnt.close(); + } + catch (SQLException sqle) { + log.error("cannot close statement", sqle); + } + } + + public Instance setInt(String key, int value) + throws SQLException + { + List pos = positions.get(key.toLowerCase()); + if (pos != null) { + for (Integer p: pos) { + stmnt.setInt(p, value); + } + } + + return this; + } + + public Instance setString(String key, String value) + throws SQLException + { + List pos = positions.get(key.toLowerCase()); + if (pos != null) { + for (Integer p: pos) { + stmnt.setString(p, value); + } + } + return this; + } + + public Instance setObject(String key, Object value) + throws SQLException + { + List pos = positions.get(key.toLowerCase()); + if (pos != null) { + for (Integer p: pos) { + stmnt.setObject(p, value); + } + } + return this; + } + + public Instance setTimestamp(String key, Timestamp value) + throws SQLException + { + List pos = positions.get(key.toLowerCase()); + if (pos != null) { + for (Integer p: pos) { + stmnt.setTimestamp(p, value); + } + } + return this; + } + + public Instance setDouble(String key, double value) + throws SQLException + { + List pos = positions.get(key.toLowerCase()); + if (pos != null) { + for (Integer p: pos) { + stmnt.setDouble(p, value); + } + } + return this; + } + + public Instance setLong(String key, long value) + throws SQLException + { + List pos = positions.get(key.toLowerCase()); + if (pos != null) { + for (Integer p: pos) { + stmnt.setLong(p, value); + } + } + return this; + } + + public Instance setNull(String key, int sqlType) + throws SQLException + { + List pos = positions.get(key.toLowerCase()); + if (pos != null) { + for (Integer p: pos) { + stmnt.setNull(p, sqlType); + } + } + return this; + } + + public Instance set(Map map) throws SQLException { + for (Map.Entry entry: map.entrySet()) { + setObject(entry.getKey(), entry.getValue()); + } + return this; + } + + public Instance clearParameters() throws SQLException { + stmnt.clearParameters(); + return this; + } + + 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) { + this.statement = statement; + compile(); + } + + public String getStatement() { + return statement; + } + + protected void compile() { + positions = new HashMap>(); + + StringBuffer sb = new StringBuffer(); + Matcher m = VAR.matcher(statement); + int index = 1; + while (m.find()) { + String key = m.group(1).toLowerCase(); + List list = positions.get(key); + if (list == null) { + list = new ArrayList(); + positions.put(key, list); + } + list.add(index++); + m.appendReplacement(sb, "?"); + } + m.appendTail(sb); + compiled = sb.toString(); + } +} // class SymbolicStatement diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/src/main/java/de/intevation/utils/XML.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/src/main/java/de/intevation/utils/XML.java Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,332 @@ +package de.intevation.utils; + +import java.io.FileInputStream; +import java.io.BufferedInputStream; +import java.io.File; +import java.io.IOException; +import java.io.InputStream; +import java.io.OutputStream; +import java.io.StringWriter; + +import org.w3c.dom.Document; + +import javax.xml.parsers.DocumentBuilderFactory; +import javax.xml.parsers.ParserConfigurationException; + +import org.xml.sax.SAXException; + +import org.apache.log4j.Logger; + +import java.util.HashMap; +import java.util.Map; + +import javax.xml.namespace.NamespaceContext; +import javax.xml.namespace.QName; + +import javax.xml.xpath.XPath; +import javax.xml.xpath.XPathExpressionException; +import javax.xml.xpath.XPathFactory; +import javax.xml.xpath.XPathVariableResolver; + +import javax.xml.transform.Transformer; +import javax.xml.transform.TransformerFactory; +import javax.xml.transform.TransformerConfigurationException; +import javax.xml.transform.TransformerException; +import javax.xml.transform.TransformerFactoryConfigurationError; + +import javax.xml.transform.stream.StreamResult; +import javax.xml.transform.stream.StreamSource; + +import javax.xml.transform.dom.DOMSource; +import javax.xml.transform.dom.DOMResult; + +public final class XML +{ + /** Logger for this class. */ + private static Logger log = Logger.getLogger(XML.class); + + public static class MapXPathVariableResolver + implements XPathVariableResolver + { + protected Map variables; + + + public MapXPathVariableResolver() { + this.variables = new HashMap(); + } + + + public MapXPathVariableResolver(Map variables) { + this.variables = variables; + } + + + public void addVariable(String name, String value) { + variables.put(name, value); + } + + + @Override + public Object resolveVariable(QName variableName) { + String key = variableName.getLocalPart(); + return variables.get(key); + } + } // class MapXPathVariableResolver + + private XML() { + } + + /** + * Creates a new XML document + * @return the new XML document ot null if something went wrong during + * creation. + */ + public static final Document newDocument() { + DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); + factory.setNamespaceAware(true); + + try { + return factory.newDocumentBuilder().newDocument(); + } + catch (ParserConfigurationException pce) { + log.error(pce.getLocalizedMessage(), pce); + } + return null; + } + + /** + * Loads a XML document namespace aware from a file + * @param file The file to load. + * @return the XML document or null if something went wrong + * during loading. + */ + public static final Document parseDocument(File file) { + return parseDocument(file, Boolean.TRUE); + } + + public static final Document parseDocument(File file, Boolean namespaceAware) { + InputStream inputStream = null; + try { + inputStream = new BufferedInputStream(new FileInputStream(file)); + return parseDocument(inputStream, namespaceAware); + } + catch (IOException ioe) { + log.error(ioe.getLocalizedMessage(), ioe); + } + finally { + if (inputStream != null) { + try { inputStream.close(); } + catch (IOException ioe) {} + } + } + return null; + } + + + public static final Document parseDocument(InputStream inputStream) { + return parseDocument(inputStream, Boolean.TRUE); + } + + public static final Document parseDocument( + InputStream inputStream, + Boolean namespaceAware + ) { + DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); + + if (namespaceAware != null) { + factory.setNamespaceAware(namespaceAware.booleanValue()); + } + + try { + return factory.newDocumentBuilder().parse(inputStream); + } + catch (ParserConfigurationException pce) { + log.error(pce.getLocalizedMessage(), pce); + } + catch (SAXException se) { + log.error(se.getLocalizedMessage(), se); + } + catch (IOException ioe) { + log.error(ioe.getLocalizedMessage(), ioe); + } + return null; + } + + + /** + * Creates a new XPath without a namespace context. + * @return the new XPath. + */ + public static final XPath newXPath() { + return newXPath(null, null); + } + + /** + * Creates a new XPath with a given namespace context. + * @param namespaceContext The namespace context to be used or null + * if none should be used. + * @return The new XPath + */ + public static final XPath newXPath( + NamespaceContext namespaceContext, + XPathVariableResolver resolver) + { + XPathFactory factory = XPathFactory.newInstance(); + XPath xpath = factory.newXPath(); + if (namespaceContext != null) { + xpath.setNamespaceContext(namespaceContext); + } + + if (resolver != null) { + xpath.setXPathVariableResolver(resolver); + } + return xpath; + } + + /** + * Evaluates an XPath query on a given object and returns the result + * as a given type. No namespace context is used. + * @param root The object which is used as the root of the tree to + * be searched in. + * @param query The XPath query + * @param returnTyp The type of the result. + * @return The result of type 'returnTyp' or null if something + * went wrong during XPath evaluation. + */ + public static final Object xpath( + Object root, + String query, + QName returnTyp + ) { + return xpath(root, query, returnTyp, null); + } + + /** + * Evaluates an XPath query on a given object and returns the result + * as a given type. Optionally a namespace context is used. + * @param root The object which is used as the root of the tree to + * be searched in. + * @param query The XPath query + * @param returnType The type of the result. + * @param namespaceContext The namespace context to be used or null + * if none should be used. + * @return The result of type 'returnTyp' or null if something + * went wrong during XPath evaluation. + */ + public static final Object xpath( + Object root, + String query, + QName returnType, + NamespaceContext namespaceContext + ) { + return xpath(root, query, returnType, namespaceContext, null); + } + + public static final Object xpath( + Object root, + String query, + QName returnType, + NamespaceContext namespaceContext, + Map variables) + { + if (root == null) { + return null; + } + + XPathVariableResolver resolver = variables != null + ? new MapXPathVariableResolver(variables) + : null; + + try { + XPath xpath = newXPath(namespaceContext, resolver); + if (xpath != null) { + return xpath.evaluate(query, root, returnType); + } + } + catch (XPathExpressionException xpee) { + log.error(xpee.getLocalizedMessage(), xpee); + } + + return null; + } + + public static Document transform( + Document document, + File xformFile + ) { + try { + Transformer transformer = + TransformerFactory + .newInstance() + .newTransformer( + new StreamSource(xformFile)); + + DOMResult result = new DOMResult(); + + transformer.transform(new DOMSource(document), result); + + return (Document)result.getNode(); + } + catch (TransformerConfigurationException tce) { + log.error(tce, tce); + } + catch (TransformerException te) { + log.error(te, te); + } + + return null; + } + + /** + * Streams out an XML document to a given output stream. + * @param document The document to be streamed out. + * @param out The output stream to be used. + * @return true if operation succeeded else false. + */ + public static boolean toStream(Document document, OutputStream out) { + try { + Transformer transformer = + TransformerFactory.newInstance().newTransformer(); + DOMSource source = new DOMSource(document); + StreamResult result = new StreamResult(out); + transformer.transform(source, result); + return true; + } + catch (TransformerConfigurationException tce) { + log.error(tce.getLocalizedMessage(), tce); + } + catch (TransformerFactoryConfigurationError tfce) { + log.error(tfce.getLocalizedMessage(), tfce); + } + catch (TransformerException te) { + log.error(te.getLocalizedMessage(), te); + } + + return false; + } + + public static String toString(Document document) { + try { + Transformer transformer = + TransformerFactory.newInstance().newTransformer(); + DOMSource source = new DOMSource(document); + StringWriter out = new StringWriter(); + StreamResult result = new StreamResult(out); + transformer.transform(source, result); + out.flush(); + return out.toString(); + } + catch (TransformerConfigurationException tce) { + log.error(tce.getLocalizedMessage(), tce); + } + catch (TransformerFactoryConfigurationError tfce) { + log.error(tfce.getLocalizedMessage(), tfce); + } + catch (TransformerException te) { + log.error(te.getLocalizedMessage(), te); + } + + return null; + } +} +// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/src/main/resources/sql/aft-common.properties --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/src/main/resources/sql/aft-common.properties Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,10 @@ +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 +select.tafelwert = SELECT TAFELWERT_NR AS id, WASSERSTAND AS w, ABFLUSS AS q FROM TAFELWERT \ + WHERE ABFLUSSTAFEL_NR = :number + diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/src/main/resources/sql/aft-oracle-jdbc-oracledriver.properties --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/src/main/resources/sql/aft-oracle-jdbc-oracledriver.properties Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,6 @@ +select.abflusstafel = SELECT ABFLUSSTAFEL_NR, \ + ABFLUSSTAFEL_BEZ, \ + GUELTIG_VON, \ + GUELTIG_BIS, \ + PEGELNULLPUNKT FROM ABFLUSSTAFEL WHERE MESSSTELLE_NR LIKE :number + diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/src/main/resources/sql/flys-common.properties --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/src/main/resources/sql/flys-common.properties Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,18 @@ +select.river = SELECT id, name FROM rivers +select.gauges = SELECT id, name, official_number FROM gauges WHERE river_id = :river_id +next.gauge.id = SELECT NEXTVAL('GAUGES_ID_SEQ') AS gauge_id +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, 1, :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_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 +select.gauge.discharge.tables = SELECT dt.id AS id, dt.description AS description, ti.start_time AS start_time, ti.stop_time AS stop_time \ + FROM discharge_tables dt LEFT OUTER JOIN time_intervals ti ON dt.time_interval_id = ti.id \ + WHERE gauge_id = :gauge_id diff -r f02aa4ff3c0f -r ae5119da92cd flys-aft/src/main/resources/sql/flys-oracle-jdbc-oracledriver.properties --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-aft/src/main/resources/sql/flys-oracle-jdbc-oracledriver.properties Thu Oct 11 14:54:10 2012 +0200 @@ -0,0 +1,5 @@ +next.gauge.id = SELECT GAUGES_ID_SEQ.NEXTVAL AS gauge_id FROM DUAL +next.timeinterval.id = SELECT TIME_INTERVALS_ID_SEQ.NEXTVAL AS time_interval_id FROM DUAL +next.discharge.id = SELECT DISCHARGE_TABLES_ID_SEQ.NEXTVAL AS discharge_table_id FROM DUAL +next.discharge.table.values.id = SELECT DISCHARGE_TABLE_VALUES_ID_SEQ.NEXTVAL AS discharge_table_values_id FROM DUAL +