changeset 7698:7efeaa2058e1

SQ Relation: Added CSV dumping to raw SedDB query results. If system property 'minfo.dump.sq.seddb.prefix' is set files are generate from this to contain the results of the raw SQL queries.
author Sascha L. Teichmann <teichmann@intevation.de>
date Mon, 16 Dec 2013 16:39:12 +0100
parents 640342d9ab8a
children 4daaf5e51dfb
files artifacts/src/main/java/org/dive4elements/river/artifacts/model/sq/MeasurementFactory.java contrib/find_alien_loggers.rb
diffstat 1 files changed, 197 insertions(+), 67 deletions(-) [+]
line wrap: on
line diff
--- a/artifacts/src/main/java/org/dive4elements/river/artifacts/model/sq/MeasurementFactory.java	Mon Dec 16 16:24:07 2013 +0100
+++ b/artifacts/src/main/java/org/dive4elements/river/artifacts/model/sq/MeasurementFactory.java	Mon Dec 16 16:39:12 2013 +0100
@@ -8,6 +8,9 @@
 
 package org.dive4elements.river.artifacts.model.sq;
 
+import java.io.FileWriter;
+import java.io.IOException;
+import java.text.SimpleDateFormat;
 import java.util.ArrayList;
 import java.util.Calendar;
 import java.util.Collections;
@@ -30,11 +33,15 @@
 
 import org.dive4elements.river.backend.SedDBSessionHolder;
 
+import au.com.bytecode.opencsv.CSVWriter;
+
 public class MeasurementFactory
 {
     private static final Logger log =
         Logger.getLogger(MeasurementFactory.class);
 
+    public static final String MINFO_DUMP_SQ_SEDDB_PREFIX = "minfo.dump.sq.seddb.prefix";
+
     public static final String SQL_TOTALS =
         "SELECT " +
             "m.Q_BPEGEL AS Q_BPEGEL,"+
@@ -113,78 +120,132 @@
         "ORDER BY " +
             "m.DATUM, g.UFERABST, g.GLOTRECHTEID, gp.LFDNR";
 
-    public static final BasicTransformerAdapter TOTALS_TRANSFORMER =
-        new BasicTransformerAdapter() {
-            private static final long serialVersionUID = 1L;
-
-            @Override
-            public Object transformTuple(Object [] tuple, String [] aliases) {
-                Map<String, Object> map = new HashMap<String, Object>();
-                for (int i = 0; i < tuple.length; ++i) {
-                    Object value = tuple[i];
-                    if (value != null) {
-                        map.put(aliases[i], value);
-                    }
-                }
-                return new Measurement(map, Collections.<Sieve>emptyList());
-            }
-        };
-
     private static final int index(String s) {
         return Integer.parseInt(s.substring(s.length()-2))-1;
     }
 
-    public static final BasicTransformerAdapter FRACTIONS_TRANSFORMER =
-        new BasicTransformerAdapter() {
-            private static final long serialVersionUID = 1L;
-
-            @Override
-            public Object transformTuple(Object [] tuple, String [] aliases) {
-                Map<String, Object> map = new HashMap<String, Object>();
+    public abstract static class CSVTransformer extends BasicTransformerAdapter {
+        private static final long serialVersionUID = 1L;
 
-                Sieve [] sieves = new Sieve[21];
-
-                List<Sieve> validSieves = new ArrayList<Sieve>(21);
+        private CSVWriter rawWriter;
+        private boolean   metaDataWritten;
 
-                for (int i = 0; i < tuple.length; ++i) {
-                    Object value = tuple[i];
-                    if (value == null) {
-                        continue;
+        public CSVTransformer() {
+            this(null);
+        }
+
+        public CSVTransformer(CSVWriter rawWriter) {
+            this.rawWriter = rawWriter;
+        }
+
+        protected void writeRaw(Object [] tuple, String [] aliases) {
+            if (rawWriter == null) {
+                return;
+            }
+
+            if (!metaDataWritten) {
+                rawWriter.writeNext(aliases);
+                metaDataWritten = true;
+            }
+
+            String [] nextLine = new String[tuple.length];
+            for (int i = 0; i < tuple.length; ++i) {
+                nextLine[i] = tuple[i] != null ? tuple[i].toString() : "";
+            }
+            rawWriter.writeNext(nextLine);
+        }
+    }
+
+    public static class FractionsTransformer extends CSVTransformer {
+
+        private static final long serialVersionUID = 1L;
+
+        public FractionsTransformer() {
+            this(null);
+        }
+
+        public FractionsTransformer(CSVWriter rawWriter) {
+            super(rawWriter);
+        }
+
+        @Override
+        public Object transformTuple(Object [] tuple, String [] aliases) {
+
+            writeRaw(tuple, aliases);
+
+            Map<String, Object> map = new HashMap<String, Object>();
+
+            Sieve [] sieves = new Sieve[21];
+
+            List<Sieve> validSieves = new ArrayList<Sieve>(21);
+
+            for (int i = 0; i < tuple.length; ++i) {
+                Object value = tuple[i];
+                if (value == null) {
+                    continue;
+                }
+                String alias = aliases[i];
+                if (alias.startsWith("SIEB")
+                ||  alias.startsWith("RSIEB")) {
+                    int idx = index(alias);
+                    Sieve s = sieves[idx];
+                    double v = (Double)value;
+                    if (s == null) {
+                        s = new Sieve();
+                        sieves[idx] = s;
                     }
-                    String alias = aliases[i];
-                    if (alias.startsWith("SIEB")
-                    ||  alias.startsWith("RSIEB")) {
-                        int idx = index(alias);
-                        Sieve s = sieves[idx];
-                        double v = (Double)value;
-                        if (s == null) {
-                            s = new Sieve();
-                            sieves[idx] = s;
-                        }
-                        if (alias.startsWith("SIEB")) {
-                            s.setDiameter(v);
-                        }
-                        else {
-                            s.setLoad(v);
-                        }
-                    }
-                    else if (alias.equals("REST")) {
-                        Sieve s = new Sieve(0d, (Double)value);
-                        validSieves.add(s);
+                    if (alias.startsWith("SIEB")) {
+                        s.setDiameter(v);
                     }
                     else {
-                        map.put(alias, value);
-                    }
-
-                }
-                for (Sieve s: sieves) {
-                    if (s != null) {
-                        validSieves.add(s);
+                        s.setLoad(v);
                     }
                 }
-                return new Measurement(map, validSieves);
+                else if (alias.equals("REST")) {
+                    Sieve s = new Sieve(0d, (Double)value);
+                    validSieves.add(s);
+                }
+                else {
+                    map.put(alias, value);
+                }
+
             }
-        };
+            for (Sieve s: sieves) {
+                if (s != null) {
+                    validSieves.add(s);
+                }
+            }
+            return new Measurement(map, validSieves);
+        }
+    } // class FractionsTransformer
+
+    public static class TotalsTransformer extends CSVTransformer {
+
+        private static final long serialVersionUID = 1L;
+
+        public TotalsTransformer() {
+            this(null);
+        }
+
+        public TotalsTransformer(CSVWriter rawWriter) {
+            super(rawWriter);
+        }
+
+        @Override
+        public Object transformTuple(Object [] tuple, String [] aliases) {
+
+            writeRaw(tuple, aliases);
+
+            Map<String, Object> map = new HashMap<String, Object>();
+            for (int i = 0; i < tuple.length; ++i) {
+                Object value = tuple[i];
+                if (value != null) {
+                    map.put(aliases[i], value);
+                }
+            }
+            return new Measurement(map, Collections.<Sieve>emptyList());
+        }
+    } // class TotalsTransformer
 
     private MeasurementFactory() {
     }
@@ -210,6 +271,7 @@
         }
     }
 
+    @SuppressWarnings("unchecked")
     protected static List<Measurement> loadTotals(
         Session   session,
         String    river,
@@ -226,13 +288,69 @@
         query.setDate("from", dateRange.getFrom());
         query.setDate("to", dateRange.getTo());
 
-        query.setResultTransformer(TOTALS_TRANSFORMER);
+        CSVWriter csvWriter =
+            getCVSWriter("totals", river, location, dateRange);
 
-        @SuppressWarnings("unchecked")
-        List<Measurement> result = (List<Measurement>)query.list();
-        return result;
+        try {
+            TotalsTransformer totalTransformer = new TotalsTransformer(csvWriter);
+            query.setResultTransformer(totalTransformer);
+
+            return (List<Measurement>)query.list();
+        }
+        finally {
+            closeGraceful(csvWriter);
+        }
     }
 
+    private static CSVWriter getCVSWriter(
+        String    type,
+        String    river,
+        double    location,
+        DateRange dateRange
+    ) {
+        String dumpPrefix = System.getProperty(MINFO_DUMP_SQ_SEDDB_PREFIX);
+        if (dumpPrefix == null) {
+            return null;
+        }
+
+        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-ddd");
+        StringBuilder sb = new StringBuilder(dumpPrefix);
+        Date from = dateRange.getFrom();
+        Date to   = dateRange.getTo();
+
+        sb.append(type)
+          .append('-').append(df.format(new Date()))
+          .append('-').append(river)
+          .append('-').append(location)
+          .append('-').append(from != null ? df.format(from) : "")
+          .append('-').append(to != null ? df.format(to) : "")
+          .append(".csv");
+
+        String fileName = sb.toString();
+
+        try {
+            return new CSVWriter(new FileWriter(fileName), ';');
+        }
+        catch (IOException ioe) {
+            log.error("Cannot open '" + fileName + "' for writing.", ioe);
+        }
+
+        return null;
+    }
+
+    private static void closeGraceful(CSVWriter writer) {
+        if (writer != null) {
+            try {
+                writer.flush();
+                writer.close();
+            }
+            catch (IOException ioe) {
+                log.error(ioe);
+            }
+        }
+    }
+
+    @SuppressWarnings("unchecked")
     protected static List<Measurement> loadFractions(
         Session   session,
         String    river,
@@ -301,10 +419,22 @@
         query.setDate("from", dateRange.getFrom());
         query.setDate("to", dateRange.getTo());
 
-        query.setResultTransformer(FRACTIONS_TRANSFORMER);
+        List<Measurement> measuments;
 
-        @SuppressWarnings("unchecked")
-        List<Measurement> measuments = (List<Measurement>)query.list();
+        CSVWriter csvWriter =
+            getCVSWriter("fractions", river, location, dateRange);
+
+        try {
+            FractionsTransformer fractionsTransformer =
+                new FractionsTransformer(csvWriter);
+
+            query.setResultTransformer(fractionsTransformer);
+
+            measuments = (List<Measurement>)query.list();
+        }
+        finally {
+            closeGraceful(csvWriter);
+        }
 
         if (debug) {
             log.debug("num fraction results: " + measuments.size());

http://dive4elements.wald.intevation.org