teichmann@5863: /* Copyright (C) 2011, 2012, 2013 by Bundesanstalt für Gewässerkunde teichmann@5863: * Software engineering by Intevation GmbH teichmann@5863: * teichmann@5863: * This file is Free Software under the GNU AGPL (>=v3) teichmann@5863: * and comes with ABSOLUTELY NO WARRANTY! Check out the teichmann@5863: * documentation coming with Dive4Elements River for details. teichmann@5863: */ teichmann@5863: teichmann@5831: package org.dive4elements.river.artifacts.model; sascha@443: felix@1894: import java.util.Arrays; sascha@633: import java.util.List; sascha@443: import java.util.ArrayList; sascha@633: sascha@633: import net.sf.ehcache.Cache; sascha@633: import net.sf.ehcache.Element; sascha@633: teichmann@5831: import org.dive4elements.river.artifacts.cache.CacheFactory; sascha@633: teichmann@5831: import org.dive4elements.river.backend.SessionHolder; sascha@633: sascha@633: import org.apache.log4j.Logger; sascha@633: teichmann@5831: import org.dive4elements.river.model.River; teichmann@5831: import org.dive4elements.river.model.Wst; sascha@443: sascha@443: import org.hibernate.Session; sascha@443: import org.hibernate.Query; sascha@443: import org.hibernate.SQLQuery; sascha@443: sascha@443: import org.hibernate.type.StandardBasicTypes; sascha@443: felix@1839: /** felix@1839: * Creates WstValueTable s from database. felix@1839: * WstValueTable s are used to interpolate given w/q/km values. felix@1839: */ sascha@443: public class WstValueTableFactory sascha@443: { sascha@443: private static Logger log = Logger.getLogger(WstValueTableFactory.class); sascha@443: sascha@633: public static final int DEFAULT_KIND = 0; sascha@633: sascha@443: // TODO: put this into a property file sascha@633: sascha@633: public static final String HQL_WST = sascha@633: "from Wst where river=:river and kind=:kind"; sascha@633: sascha@633: public static final String SQL_SELECT_NAMES_POS = sascha@633: "SELECT position, name FROM wst_columns " + sascha@633: "WHERE wst_id = :wst_id ORDER BY position"; sascha@633: felix@1909: /** Select Qs for wst (view sorted by column). */ sascha@633: public static final String SQL_SELECT_QS = sascha@633: "SELECT column_pos, q, a, b FROM wst_q_values " + sascha@633: "WHERE wst_id = :wst_id"; sascha@633: felix@1909: // (sorted by km) sascha@633: public static final String SQL_SELECT_WS = sascha@633: "SELECT km, w, column_pos FROM wst_w_values " + sascha@633: "WHERE wst_id = :wst_id"; sascha@443: felix@1909: /** Statement to query qranges of a single column. */ felix@1909: public static final String SQL_SELECT_QS_AT_COL = sascha@2609: "SELECT q, a, b FROM wst_q_values " + felix@1909: "WHERE wst_id = :wst_id AND column_pos = :column_pos"; felix@1909: felix@1909: // (sorted by km) felix@1909: public static final String SQL_SELECT_WS_AT_COL = felix@1909: "SELECT km, w FROM wst_w_values " + felix@1909: "WHERE wst_id = :wst_id AND column_pos = :column_pos"; felix@1909: felix@1909: sascha@443: private WstValueTableFactory() { sascha@443: } sascha@443: felix@1909: sascha@443: public static WstValueTable getTable(River river) { sascha@633: return getTable(river, DEFAULT_KIND); sascha@443: } sascha@443: felix@1839: felix@1894: /** felix@1894: * Get WstValueTable to interpolate values of a given Wst. felix@1894: */ felix@1894: public static WstValueTable getTable(int wst_id) { felix@1894: felix@1898: Cache cache = CacheFactory.getCache(WstValueTableCacheKey.CACHE_NAME); felix@1898: felix@1898: WstValueTableCacheKey cacheKey; felix@1898: felix@1898: if (cache != null) { felix@1898: // "-1" is the symbolic river-id for "no river, but wst_id". felix@1898: cacheKey = new WstValueTableCacheKey(-1, wst_id); felix@1898: Element element = cache.get(cacheKey); felix@1898: if (element != null) { felix@1898: log.debug("Got specific wst value table from cache"); felix@1898: return (WstValueTable) element.getValue(); felix@1898: } felix@1898: } felix@1898: else { felix@1898: cacheKey = null; felix@1898: } felix@1894: felix@1894: Session session = SessionHolder.HOLDER.get(); felix@1894: felix@1894: // Fetch data for one column only. sascha@3076: felix@1894: WstValueTable.Column [] columns = loadColumns(session, wst_id); aheinecke@5423: int [] map = loadQRangesMap(session, columns, wst_id); aheinecke@5423: List rows = loadRows(session, wst_id, columns.length, map); felix@1894: felix@1927: WstValueTable valueTable = new WstValueTable(columns, rows); felix@1898: felix@1898: if (valueTable != null && cacheKey != null) { felix@1898: log.debug("Store wst value table in cache"); felix@1898: Element element = new Element(cacheKey, valueTable); felix@1898: cache.put(element); felix@1898: } felix@1898: felix@1898: return valueTable; felix@1894: } felix@1898: felix@1909: /** felix@1909: * Get Table for a specific column of a wst. felix@1909: */ felix@1909: public static WstValueTable getWstColumnTable(int wst_id, int col_pos) { felix@1898: felix@1927: Cache cache = CacheFactory.getCache(WstValueTableCacheKey.CACHE_NAME); felix@1927: felix@1927: WstValueTableCacheKey cacheKey; felix@1927: felix@1927: if (cache != null) { felix@1927: // A negaitve/negative number is the symbolic 'river-id' for felix@1927: // "no river and kind but wst_id and colpos". felix@1927: cacheKey = new WstValueTableCacheKey(-wst_id, -col_pos); felix@1927: Element element = cache.get(cacheKey); felix@1927: if (element != null) { felix@1927: log.debug("Got specific wst value table from cache"); felix@1927: return (WstValueTable) element.getValue(); felix@1927: } felix@1927: } felix@1927: else { felix@1927: cacheKey = null; felix@1927: } felix@1909: felix@1909: Session session = SessionHolder.HOLDER.get(); felix@1909: felix@1909: // Fetch data for one column only. sascha@3076: felix@1909: WstValueTable.Column [] columns = loadColumn(session, wst_id, col_pos); felix@1909: loadQRanges(session, columns, wst_id, col_pos); felix@1909: List rows = loadRowsOneColumn(session, wst_id, col_pos); felix@1909: felix@1927: WstValueTable valueTable = new WstValueTable(columns, rows); felix@1927: felix@1927: if (valueTable != null && cacheKey != null) { felix@1927: log.debug("Store wst value table in cache (wst: " felix@1927: + wst_id + "/ col: " + col_pos + ")"); felix@1927: Element element = new Element(cacheKey, valueTable); felix@1927: cache.put(element); felix@1927: } felix@1927: felix@1927: return valueTable; felix@1909: } felix@1909: felix@1909: felix@1909: /** felix@1909: * Get table for first wst of given kind at given river. felix@1909: */ sascha@443: public static WstValueTable getTable(River river, int kind) { sascha@443: sascha@632: Cache cache = CacheFactory.getCache(WstValueTableCacheKey.CACHE_NAME); sascha@443: sascha@626: WstValueTableCacheKey cacheKey; sascha@443: sascha@443: if (cache != null) { sascha@626: cacheKey = new WstValueTableCacheKey(river.getId(), kind); sascha@443: Element element = cache.get(cacheKey); sascha@443: if (element != null) { sascha@443: log.debug("got wst value table from cache"); sascha@443: return (WstValueTable)element.getValue(); sascha@443: } sascha@443: } sascha@443: else { sascha@443: cacheKey = null; sascha@443: } sascha@443: sascha@443: WstValueTable valueTable = getTableUncached(river, kind); sascha@443: sascha@632: if (valueTable != null && cacheKey != null) { sascha@443: log.debug("store wst value table in cache"); sascha@443: Element element = new Element(cacheKey, valueTable); sascha@443: cache.put(element); sascha@443: } sascha@443: sascha@443: return valueTable; sascha@443: } sascha@443: sascha@633: public static WstValueTable getTableUncached(River river) { sascha@633: return getTableUncached(river, DEFAULT_KIND); sascha@633: } sascha@633: sascha@443: public static WstValueTable getTableUncached(River river, int kind) { sascha@443: sascha@443: Session session = SessionHolder.HOLDER.get(); sascha@443: sascha@633: Wst wst = loadWst(session, river, kind); sascha@633: sascha@633: if (wst == null) { sascha@633: return null; sascha@633: } sascha@633: sascha@633: WstValueTable.Column [] columns = loadColumns(session, wst); sascha@633: aheinecke@5423: int map [] = loadQRangesMap(session, columns, wst); sascha@633: aheinecke@5423: List rows = aheinecke@5423: loadRows(session, wst.getId(), columns.length, map); sascha@633: sascha@633: return new WstValueTable(columns, rows); sascha@633: } sascha@633: felix@1780: /** felix@1780: * @param kind Kind of wst. felix@1780: */ sascha@633: protected static Wst loadWst(Session session, River river, int kind) { sascha@633: Query query = session.createQuery(HQL_WST); sascha@443: query.setParameter("river", river); sascha@443: query.setInteger("kind", kind); sascha@443: sascha@443: List wsts = query.list(); sascha@443: felix@1839: // TODO Multiple wsts can match, why return just the first one? sascha@633: return wsts.isEmpty() ? null : wsts.get(0); sascha@633: } sascha@443: felix@1909: felix@1909: /** felix@1909: * Load rows with a single columns result. felix@3269: * felix@1909: * @param session session to use for querying db. felix@3269: * @param wstId id of wst (in db). felix@1909: * @param column_pos the column_pos (within the db) of the wst_value_table felix@1909: * of which the values shall be fetched. felix@3269: * felix@1909: * @return resultant rows. felix@1909: */ felix@1909: protected static List loadRowsOneColumn( felix@1909: Session session, sascha@2610: int wstId, felix@1909: int column_pos felix@1909: ) { felix@1909: SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_WS_AT_COL) sascha@2610: .addScalar("km", StandardBasicTypes.DOUBLE) sascha@2610: .addScalar("w", StandardBasicTypes.DOUBLE); felix@1909: sascha@2610: sqlQuery.setInteger("wst_id", wstId); felix@1909: sqlQuery.setInteger("column_pos", column_pos); felix@1909: felix@1909: List results = sqlQuery.list(); felix@1909: felix@1909: double [] ws = null; felix@1909: sascha@2610: List rows = sascha@2610: new ArrayList(results.size()); felix@1909: felix@1909: // Walk over rows. felix@1909: for (Object [] result: results) { felix@1909: ws = new double[1]; felix@1909: WstValueTable.Row row = felix@1909: new WstValueTable.Row((Double) result[0], ws); felix@1909: rows.add(row); felix@1909: felix@1909: Double w = (Double) result[1]; felix@1909: ws[0] = w != null ? w : Double.NaN; felix@1909: } felix@1909: felix@1909: return rows; felix@1909: } felix@1909: sascha@633: protected static List loadRows( sascha@742: Session session, felix@1839: int wst_id, sascha@633: int numColumns felix@1909: ) { aheinecke@5423: return loadRows(session, wst_id, numColumns, null); aheinecke@5423: } aheinecke@5423: aheinecke@5423: protected static List loadRows( aheinecke@5423: Session session, aheinecke@5423: int wst_id, aheinecke@5423: int numColumns, aheinecke@5423: int [] map aheinecke@5423: ) { sascha@633: SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_WS) sascha@633: .addScalar("km", StandardBasicTypes.DOUBLE) sascha@443: .addScalar("w", StandardBasicTypes.DOUBLE) sascha@443: .addScalar("column_pos", StandardBasicTypes.INTEGER); sascha@443: felix@1839: sqlQuery.setInteger("wst_id", wst_id); sascha@443: sascha@633: List results = sqlQuery.list(); sascha@443: sascha@633: int lastColumn = Integer.MAX_VALUE; sascha@633: double [] ws = null; sascha@443: sascha@633: ArrayList rows = new ArrayList(); sascha@633: sascha@633: for (Object [] result: results) { sascha@633: int column = (Integer)result[2]; sascha@633: if (column < lastColumn) { sascha@633: ws = new double[numColumns]; felix@1894: Arrays.fill(ws, Double.NaN); sascha@633: WstValueTable.Row row = sascha@633: new WstValueTable.Row((Double)result[0], ws); sascha@633: rows.add(row); sascha@633: } sascha@633: Double w = (Double)result[1]; aheinecke@5423: int index = map != null ? map[column] : column; aheinecke@5423: ws[index] = w != null ? w : Double.NaN; sascha@633: lastColumn = column; sascha@633: } sascha@633: sascha@633: rows.trimToSize(); sascha@633: return rows; sascha@633: } sascha@633: felix@1839: protected static List loadRows( felix@1839: Session session, felix@1839: Wst wst, felix@1839: int numColumns felix@1839: ) { felix@1839: return loadRows(session, wst.getId(), numColumns); felix@1839: } felix@1839: felix@1839: felix@1909: protected static WstValueTable.Column [] loadColumn( felix@1909: Session session, felix@1909: int wst_id, felix@1909: int col_pos felix@1909: ) { felix@1909: return new WstValueTable.Column [] { felix@1909: new WstValueTable.Column(WKmsFactory.getWKmsName(col_pos, wst_id))}; felix@1909: } felix@1909: felix@1909: felix@1839: /** felix@1839: * Get columns from wst-id. felix@1839: */ sascha@633: protected static WstValueTable.Column [] loadColumns( sascha@633: Session session, felix@1839: int wst_id sascha@633: ) { sascha@633: SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_NAMES_POS) sascha@633: .addScalar("position", StandardBasicTypes.INTEGER) sascha@633: .addScalar("name", StandardBasicTypes.STRING); sascha@633: felix@1839: sqlQuery.setInteger("wst_id", wst_id); sascha@633: sascha@633: List columnNames = sqlQuery.list(); sascha@633: sascha@633: WstValueTable.Column [] columns = sascha@633: new WstValueTable.Column[columnNames.size()]; sascha@633: sascha@633: for (int i = 0; i < columns.length; ++i) { sascha@633: columns[i] = new WstValueTable.Column( sascha@633: (String)columnNames.get(i)[1]); sascha@633: } sascha@633: return columns; sascha@633: } sascha@633: felix@1839: /** felix@1839: * Get columns from Wst. felix@1839: */ felix@1839: protected static WstValueTable.Column [] loadColumns( felix@1839: Session session, felix@1839: Wst wst felix@1839: ) { felix@1839: return loadColumns(session, wst.getId()); felix@1839: } felix@1839: felix@1909: felix@1909: /** felix@1909: * Build a QRange-Tree. felix@1909: */ felix@1909: protected static void loadQRanges( felix@1909: Session session, felix@1909: WstValueTable.Column [] columns, felix@1909: int wst_id, felix@1909: int column_pos felix@1909: ) { felix@1909: SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_QS_AT_COL) sascha@2609: .addScalar("q", StandardBasicTypes.DOUBLE) sascha@2609: .addScalar("a", StandardBasicTypes.DOUBLE) sascha@2609: .addScalar("b", StandardBasicTypes.DOUBLE); felix@1909: felix@1909: sqlQuery.setInteger("wst_id", wst_id); felix@1909: sqlQuery.setInteger("column_pos", column_pos); felix@1909: felix@1909: List qRanges = sqlQuery.list(); felix@1909: felix@1909: int qSize = qRanges.size(); felix@1909: sascha@2609: QRangeTree qRangeTree = new QRangeTree( sascha@2609: qRanges, QRangeTree.WITHOUT_COLUMN, 0, qSize); felix@1909: columns[0].setQRangeTree(qRangeTree); felix@1909: } felix@1909: aheinecke@5423: protected static int [] loadQRangesMap( sascha@633: Session session, sascha@633: WstValueTable.Column [] columns, felix@1839: int wst_id sascha@633: ) { sascha@633: SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_QS) sascha@633: .addScalar("column_pos", StandardBasicTypes.INTEGER) sascha@633: .addScalar("q", StandardBasicTypes.DOUBLE) sascha@633: .addScalar("a", StandardBasicTypes.DOUBLE) sascha@633: .addScalar("b", StandardBasicTypes.DOUBLE); sascha@633: felix@1839: sqlQuery.setInteger("wst_id", wst_id); sascha@633: sascha@633: List qRanges = sqlQuery.list(); sascha@633: sascha@633: int start = -1; sascha@633: int Q = qRanges.size(); sascha@633: Integer lastColumn = null; sascha@633: sascha@633: for (int i = 0; i < Q; ++i) { sascha@633: Object [] qRange = qRanges.get(i); sascha@633: Integer columnId = (Integer)qRange[0]; sascha@633: if (lastColumn == null) { sascha@633: lastColumn = columnId; sascha@633: start = i; sascha@633: } sascha@633: else if (!lastColumn.equals(columnId)) { sascha@633: QRangeTree qRangeTree = new QRangeTree(qRanges, start, i); sascha@633: columns[lastColumn].setQRangeTree(qRangeTree); sascha@633: lastColumn = columnId; sascha@633: start = i; sascha@633: } sascha@633: } sascha@633: sascha@633: if (start != -1) { sascha@633: QRangeTree qRangeTree = new QRangeTree(qRanges, start, Q); sascha@633: columns[lastColumn].setQRangeTree(qRangeTree); sascha@633: } sascha@633: aheinecke@5423: return sortColumnsByAverageQ(columns); aheinecke@5423: sascha@742: /* This is debug code to visualize the q ranges trees sascha@633: sascha@633: java.io.PrintWriter out = null; sascha@633: try { sascha@633: out = new java.io.PrintWriter( sascha@633: new java.io.FileWriter( sascha@633: "/tmp/qranges" + System.currentTimeMillis() + ".dot")); sascha@633: sascha@633: out.println("graph \"Q ranges trees\" {"); sascha@633: sascha@633: for (int i = 0; i < columns.length; ++i) { sascha@633: QRangeTree tree = columns[i].getQRangeTree(); sascha@633: out.println(tree.toGraph()); sascha@443: } sascha@443: sascha@633: out.println("}"); sascha@443: sascha@633: out.flush(); sascha@443: } sascha@633: catch (java.io.IOException ioe) { sascha@633: log.error(ioe); sascha@443: } sascha@633: finally { sascha@633: if (out != null) { sascha@633: out.close(); sascha@633: } sascha@633: } sascha@633: */ felix@1839: } felix@1839: aheinecke@5423: private static final class QIndex implements Comparable { aheinecke@5423: double q; aheinecke@5423: int index; aheinecke@5423: aheinecke@5423: QIndex(double q, int index) { aheinecke@5423: this.q = q; aheinecke@5423: this.index = index; aheinecke@5423: } aheinecke@5423: aheinecke@5423: @Override aheinecke@5423: public int compareTo(QIndex other) { aheinecke@5423: double diff = q - other.q; aheinecke@5423: if (diff < 0d) return -1; aheinecke@5423: if (diff > 0d) return +1; aheinecke@5423: return 0; aheinecke@5423: } aheinecke@5423: } // class QIndex aheinecke@5423: aheinecke@5423: /** Ensure that the q colums are sorted in ascending order. */ aheinecke@5423: protected static int [] sortColumnsByAverageQ(WstValueTable.Column [] columns) { aheinecke@5423: QIndex [] order = new QIndex[columns.length]; aheinecke@5423: for (int i = 0; i < order.length; ++i) { aheinecke@5423: QRangeTree tree = columns[i].getQRangeTree(); aheinecke@5423: double avg = tree.averageQ(); aheinecke@5423: double max = tree.maxQ(); aheinecke@5423: double q = (avg+max)*0.5d; aheinecke@5423: order[i] = new QIndex(q, i); aheinecke@5423: } aheinecke@5423: Arrays.sort(order); aheinecke@5423: WstValueTable.Column [] copy = new WstValueTable.Column[order.length]; aheinecke@5423: int [] map = new int[order.length]; aheinecke@5423: for (int i = 0; i < copy.length; ++i) { aheinecke@5423: copy[i] = columns[order[i].index]; aheinecke@5423: map[order[i].index] = i; aheinecke@5423: } aheinecke@5423: System.arraycopy(copy, 0, columns, 0, order.length); aheinecke@5423: return map; aheinecke@5423: } aheinecke@5423: aheinecke@5423: protected static int [] loadQRangesMap( felix@1839: Session session, felix@1839: WstValueTable.Column [] columns, felix@1839: Wst wst felix@1839: ) { aheinecke@5423: return loadQRangesMap(session, columns, wst.getId()); sascha@633: } sascha@443: sascha@443: } sascha@443: // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :