Mercurial > dive4elements > river
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());