--- a/flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/WstValueTableFactory.java	Tue May 24 14:46:45 2011 +0000
+++ b/flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/WstValueTableFactory.java	Wed May 25 15:31:25 2011 +0000
@@ -1,9 +1,19 @@
 package de.intevation.flys.artifacts.model;
 
+import java.util.List;
 import java.util.ArrayList;
-import java.util.Comparator;
-import java.util.List;
-import java.util.Collections;
+
+import net.sf.ehcache.Cache;
+import net.sf.ehcache.Element;
+
+import de.intevation.flys.artifacts.cache.CacheFactory;
+
+import de.intevation.flys.backend.SessionHolder;
+
+import org.apache.log4j.Logger;
+
+import de.intevation.flys.model.River;
+import de.intevation.flys.model.Wst;
 
 import org.hibernate.Session;
 import org.hibernate.Query;
@@ -11,34 +21,34 @@
 
 import org.hibernate.type.StandardBasicTypes;
 
-import net.sf.ehcache.Cache;
-import net.sf.ehcache.Element;
-
-import org.apache.log4j.Logger;
-
-import de.intevation.flys.model.River;
-import de.intevation.flys.model.Wst;
-import de.intevation.flys.model.WstColumn;
-
-import de.intevation.flys.artifacts.cache.CacheFactory;
-
-import de.intevation.flys.backend.SessionHolder;
-
 public class WstValueTableFactory
 {
     private static Logger log = Logger.getLogger(WstValueTableFactory.class);
 
+    public static final int DEFAULT_KIND = 0;
+
     // TODO: put this into a property file
-    public static final String SQL_POS_WQ = 
-        "SELECT position, w, q, column_pos" +
-        "    FROM wst_value_table"          +
-        "    WHERE wst_id = :wst_id";
+
+    public static final String HQL_WST =
+        "from Wst where river=:river and kind=:kind";
+
+    public static final String SQL_SELECT_NAMES_POS =
+        "SELECT position, name FROM wst_columns " +
+        "WHERE wst_id = :wst_id ORDER BY position";
+
+    public static final String SQL_SELECT_QS =
+        "SELECT column_pos, q, a, b FROM wst_q_values " +
+        "WHERE wst_id = :wst_id";
+
+    public static final String SQL_SELECT_WS =
+        "SELECT km, w, column_pos FROM wst_w_values " +
+        "WHERE wst_id = :wst_id";
 
     private WstValueTableFactory() {
     }
 
     public static WstValueTable getTable(River river) {
-        return getTable(river, 0);
+        return getTable(river, DEFAULT_KIND);
     }
 
     public static WstValueTable getTable(River river, int kind) {
@@ -70,99 +80,165 @@
         return valueTable;
     }
 
+    public static WstValueTable getTableUncached(River river) {
+        return getTableUncached(river, DEFAULT_KIND);
+    }
+
     public static WstValueTable getTableUncached(River river, int kind) {
 
         Session session = SessionHolder.HOLDER.get();
 
-        Query query = session.createQuery(
-            "from Wst where river=:river and kind=:kind");
+        Wst wst = loadWst(session, river, kind);
+
+        if (wst == null) {
+            return null;
+        }
+
+        WstValueTable.Column [] columns = loadColumns(session, wst);
+
+        loadQRanges(session, columns, wst);
+
+        List<WstValueTable.Row> rows = loadRows(session, wst, columns.length);
+
+        return new WstValueTable(columns, rows);
+    }
+
+    protected static Wst loadWst(Session session, River river, int kind) {
+        Query query = session.createQuery(HQL_WST);
         query.setParameter("river", river);
         query.setInteger("kind",    kind);
 
         List<Wst> wsts = query.list();
 
-        if (wsts.isEmpty()) {
-            return null;
-        }
-
-        Wst wst = wsts.get(0);
+        return wsts.isEmpty() ? null : wsts.get(0);
+    }
 
-        // TODO: Do this sorting at database level
-        List<WstColumn> wstColumns = new ArrayList(wst.getColumns());
-        Collections.sort(wstColumns, new Comparator<WstColumn>() {
-            public int compare(WstColumn a, WstColumn b) {
-                int pa = a.getPosition();
-                int pb = b.getPosition();
-                if (pa < pb) return -1;
-                if (pa > pb) return +1;
-                return 0;
-            }
-        });
-
-        WstValueTable.Column [] columns =
-            new WstValueTable.Column[wstColumns.size()];
-
-        for (int i = 0; i < columns.length; ++i) {
-            columns[i] = new WstValueTable.Column(wstColumns.get(i).getName());
-        }
-
-        // using native SQL here to avoid myriad of small objects.
-        SQLQuery sqlQuery = session.createSQLQuery(SQL_POS_WQ)
-            .addScalar("position",   StandardBasicTypes.DOUBLE)
+    protected static List<WstValueTable.Row> loadRows(
+        Session session, 
+        Wst     wst,
+        int     numColumns
+    ) {
+        SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_WS)
+            .addScalar("km",         StandardBasicTypes.DOUBLE)
             .addScalar("w",          StandardBasicTypes.DOUBLE)
-            .addScalar("q",          StandardBasicTypes.DOUBLE)
             .addScalar("column_pos", StandardBasicTypes.INTEGER);
 
         sqlQuery.setInteger("wst_id", wst.getId());
 
-        WstValueTable valueTable = new WstValueTable(columns);
-
-        int lastColumnNo      = -1;
-        WstValueTable.Row row = null;
-
-        Double lastQ     = -Double.MAX_VALUE;
-        boolean qSorted  = true;
-
-        for (Object  r: sqlQuery.list()) {
-            Object[] result = (Object[]) r;
+        List<Object []> results = sqlQuery.list();
 
-            double km    = (Double) result[0];
-            Double w     = (Double) result[1];
-            Double q     = (Double) result[2];
-            int columnNo = (Integer)result[3];
+        int lastColumn = Integer.MAX_VALUE;
+        double [] ws = null;
 
-            if (columnNo > lastColumnNo) { // new row
-                if (row != null) {
-                    row.qSorted = qSorted;
-                    valueTable.rows.add(row);
-                }
-                row = new WstValueTable.Row(
-                    km,
-                    new double[columnNo+1],
-                    new double[columnNo+1]);
-                lastQ = -Double.MAX_VALUE;
-                qSorted = true;
+        ArrayList<WstValueTable.Row> rows = new ArrayList<WstValueTable.Row>();
+
+        for (Object [] result: results) {
+            int column = (Integer)result[2];
+            if (column < lastColumn) {
+                ws = new double[numColumns];
+                WstValueTable.Row row =
+                    new WstValueTable.Row((Double)result[0], ws);
+                rows.add(row);
+            }
+            Double w = (Double)result[1];
+            ws[column] = w != null ? w : Double.NaN;
+            lastColumn = column;
+        }
+
+        rows.trimToSize();
+        return rows;
+    }
+
+    protected static WstValueTable.Column [] loadColumns(
+        Session session,
+        Wst     wst
+    ) {
+        SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_NAMES_POS)
+            .addScalar("position",   StandardBasicTypes.INTEGER)
+            .addScalar("name",       StandardBasicTypes.STRING);
+
+        sqlQuery.setInteger("wst_id", wst.getId());
+
+        List<Object []> columnNames = sqlQuery.list();
+
+        WstValueTable.Column [] columns =
+            new WstValueTable.Column[columnNames.size()];
+
+        for (int i = 0; i < columns.length; ++i) {
+            columns[i] = new WstValueTable.Column(
+                (String)columnNames.get(i)[1]);
+        }
+        return columns;
+    }
+
+    protected static void loadQRanges(
+        Session                 session,
+        WstValueTable.Column [] columns,
+        Wst                     wst
+    ) {
+        SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_QS)
+            .addScalar("column_pos", StandardBasicTypes.INTEGER)
+            .addScalar("q",          StandardBasicTypes.DOUBLE)
+            .addScalar("a",          StandardBasicTypes.DOUBLE)
+            .addScalar("b",          StandardBasicTypes.DOUBLE);
+
+        sqlQuery.setInteger("wst_id", wst.getId());
+
+        List<Object []> qRanges = sqlQuery.list();
+
+        int     start      = -1;
+        int     Q          = qRanges.size();
+        Integer lastColumn = null;
+
+        for (int i = 0; i < Q; ++i) {
+            Object [] qRange = qRanges.get(i);
+            Integer columnId = (Integer)qRange[0];
+            if (lastColumn == null) {
+                lastColumn = columnId;
+                start = i;
+            }
+            else if (!lastColumn.equals(columnId)) {
+                QRangeTree qRangeTree = new QRangeTree(qRanges, start, i);
+                columns[lastColumn].setQRangeTree(qRangeTree);
+                lastColumn = columnId;
+                start = i;
+            }
+        }
+
+        if (start != -1) {
+            QRangeTree qRangeTree = new QRangeTree(qRanges, start, Q);
+            columns[lastColumn].setQRangeTree(qRangeTree);
+        }
+
+        /* This is debug code to visualize the q ranges trees 
+
+        java.io.PrintWriter out = null;
+        try {
+            out = new java.io.PrintWriter(
+                new java.io.FileWriter(
+                    "/tmp/qranges" + System.currentTimeMillis() + ".dot"));
+
+            out.println("graph \"Q ranges trees\" {");
+
+            for (int i = 0; i < columns.length; ++i) {
+                QRangeTree tree = columns[i].getQRangeTree();
+                out.println(tree.toGraph());
             }
 
-            row.ws[columnNo] = w != null ? w : Double.NaN;
-            row.qs[columnNo] = q != null ? q : Double.NaN;
+            out.println("}");
 
-            if (qSorted && (q == null || lastQ > q)) {
-                qSorted = false;
-            }
-            lastQ = q;
-
-            lastColumnNo = columnNo;
+            out.flush();
         }
-
-        if (row != null) {
-            valueTable.rows.add(row);
+        catch (java.io.IOException ioe) {
+            log.error(ioe);
         }
+        finally {
+            if (out != null) {
+                out.close();
+            }
+        }
+        */
+    }
 
-        // XXX: should not be necessary
-        valueTable.sortRows();
-
-        return valueTable;
-    }
 }
 // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :