Mercurial > dive4elements > river

comparison flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/WstValueTableFactory.java @ 633:d08f77e7f7e8

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
WST value table: Qs are now stored in ranges for each column. flys-artifacts/trunk@2006 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 25 May 2011 15:31:25 +0000
parents 07640ab913fd
children c09c9e05ecfa
comparison
equal deleted inserted replaced
632:07640ab913fd 633:d08f77e7f7e8
1 package de.intevation.flys.artifacts.model; 1 package de.intevation.flys.artifacts.model;
2 2
3 import java.util.List;
3 import java.util.ArrayList; 4 import java.util.ArrayList;
4 import java.util.Comparator; 5
5 import java.util.List; 6 import net.sf.ehcache.Cache;
6 import java.util.Collections; 7 import net.sf.ehcache.Element;
8
9 import de.intevation.flys.artifacts.cache.CacheFactory;
10
11 import de.intevation.flys.backend.SessionHolder;
12
13 import org.apache.log4j.Logger;
14
15 import de.intevation.flys.model.River;
16 import de.intevation.flys.model.Wst;
7 17
8 import org.hibernate.Session; 18 import org.hibernate.Session;
9 import org.hibernate.Query; 19 import org.hibernate.Query;
10 import org.hibernate.SQLQuery; 20 import org.hibernate.SQLQuery;
11 21
12 import org.hibernate.type.StandardBasicTypes; 22 import org.hibernate.type.StandardBasicTypes;
13 23
14 import net.sf.ehcache.Cache;
15 import net.sf.ehcache.Element;
16
17 import org.apache.log4j.Logger;
18
19 import de.intevation.flys.model.River;
20 import de.intevation.flys.model.Wst;
21 import de.intevation.flys.model.WstColumn;
22
23 import de.intevation.flys.artifacts.cache.CacheFactory;
24
25 import de.intevation.flys.backend.SessionHolder;
26
27 public class WstValueTableFactory 24 public class WstValueTableFactory
28 { 25 {
29 private static Logger log = Logger.getLogger(WstValueTableFactory.class); 26 private static Logger log = Logger.getLogger(WstValueTableFactory.class);
30 27
28 public static final int DEFAULT_KIND = 0;
29
31 // TODO: put this into a property file 30 // TODO: put this into a property file
32 public static final String SQL_POS_WQ = 31
33 "SELECT position, w, q, column_pos" + 32 public static final String HQL_WST =
34 " FROM wst_value_table" + 33 "from Wst where river=:river and kind=:kind";
35 " WHERE wst_id = :wst_id"; 34
35 public static final String SQL_SELECT_NAMES_POS =
36 "SELECT position, name FROM wst_columns " +
37 "WHERE wst_id = :wst_id ORDER BY position";
38
39 public static final String SQL_SELECT_QS =
40 "SELECT column_pos, q, a, b FROM wst_q_values " +
41 "WHERE wst_id = :wst_id";
42
43 public static final String SQL_SELECT_WS =
44 "SELECT km, w, column_pos FROM wst_w_values " +
45 "WHERE wst_id = :wst_id";
36 46
37 private WstValueTableFactory() { 47 private WstValueTableFactory() {
38 } 48 }
39 49
40 public static WstValueTable getTable(River river) { 50 public static WstValueTable getTable(River river) {
41 return getTable(river, 0); 51 return getTable(river, DEFAULT_KIND);
42 } 52 }
43 53
44 public static WstValueTable getTable(River river, int kind) { 54 public static WstValueTable getTable(River river, int kind) {
45 55
46 Cache cache = CacheFactory.getCache(WstValueTableCacheKey.CACHE_NAME); 56 Cache cache = CacheFactory.getCache(WstValueTableCacheKey.CACHE_NAME);
68 } 78 }
69 79
70 return valueTable; 80 return valueTable;
71 } 81 }
72 82
83 public static WstValueTable getTableUncached(River river) {
84 return getTableUncached(river, DEFAULT_KIND);
85 }
86
73 public static WstValueTable getTableUncached(River river, int kind) { 87 public static WstValueTable getTableUncached(River river, int kind) {
74 88
75 Session session = SessionHolder.HOLDER.get(); 89 Session session = SessionHolder.HOLDER.get();
76 90
77 Query query = session.createQuery( 91 Wst wst = loadWst(session, river, kind);
78 "from Wst where river=:river and kind=:kind"); 92
93 if (wst == null) {
94 return null;
95 }
96
97 WstValueTable.Column [] columns = loadColumns(session, wst);
98
99 loadQRanges(session, columns, wst);
100
101 List<WstValueTable.Row> rows = loadRows(session, wst, columns.length);
102
103 return new WstValueTable(columns, rows);
104 }
105
106 protected static Wst loadWst(Session session, River river, int kind) {
107 Query query = session.createQuery(HQL_WST);
79 query.setParameter("river", river); 108 query.setParameter("river", river);
80 query.setInteger("kind", kind); 109 query.setInteger("kind", kind);
81 110
82 List<Wst> wsts = query.list(); 111 List<Wst> wsts = query.list();
83 112
84 if (wsts.isEmpty()) { 113 return wsts.isEmpty() ? null : wsts.get(0);
85 return null; 114 }
86 } 115
87 116 protected static List<WstValueTable.Row> loadRows(
88 Wst wst = wsts.get(0); 117 Session session,
89 118 Wst wst,
90 // TODO: Do this sorting at database level 119 int numColumns
91 List<WstColumn> wstColumns = new ArrayList(wst.getColumns()); 120 ) {
92 Collections.sort(wstColumns, new Comparator<WstColumn>() { 121 SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_WS)
93 public int compare(WstColumn a, WstColumn b) { 122 .addScalar("km", StandardBasicTypes.DOUBLE)
94 int pa = a.getPosition(); 123 .addScalar("w", StandardBasicTypes.DOUBLE)
95 int pb = b.getPosition(); 124 .addScalar("column_pos", StandardBasicTypes.INTEGER);
96 if (pa < pb) return -1; 125
97 if (pa > pb) return +1; 126 sqlQuery.setInteger("wst_id", wst.getId());
98 return 0; 127
99 } 128 List<Object []> results = sqlQuery.list();
100 }); 129
130 int lastColumn = Integer.MAX_VALUE;
131 double [] ws = null;
132
133 ArrayList<WstValueTable.Row> rows = new ArrayList<WstValueTable.Row>();
134
135 for (Object [] result: results) {
136 int column = (Integer)result[2];
137 if (column < lastColumn) {
138 ws = new double[numColumns];
139 WstValueTable.Row row =
140 new WstValueTable.Row((Double)result[0], ws);
141 rows.add(row);
142 }
143 Double w = (Double)result[1];
144 ws[column] = w != null ? w : Double.NaN;
145 lastColumn = column;
146 }
147
148 rows.trimToSize();
149 return rows;
150 }
151
152 protected static WstValueTable.Column [] loadColumns(
153 Session session,
154 Wst wst
155 ) {
156 SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_NAMES_POS)
157 .addScalar("position", StandardBasicTypes.INTEGER)
158 .addScalar("name", StandardBasicTypes.STRING);
159
160 sqlQuery.setInteger("wst_id", wst.getId());
161
162 List<Object []> columnNames = sqlQuery.list();
101 163
102 WstValueTable.Column [] columns = 164 WstValueTable.Column [] columns =
103 new WstValueTable.Column[wstColumns.size()]; 165 new WstValueTable.Column[columnNames.size()];
104 166
105 for (int i = 0; i < columns.length; ++i) { 167 for (int i = 0; i < columns.length; ++i) {
106 columns[i] = new WstValueTable.Column(wstColumns.get(i).getName()); 168 columns[i] = new WstValueTable.Column(
107 } 169 (String)columnNames.get(i)[1]);
108 170 }
109 // using native SQL here to avoid myriad of small objects. 171 return columns;
110 SQLQuery sqlQuery = session.createSQLQuery(SQL_POS_WQ) 172 }
111 .addScalar("position", StandardBasicTypes.DOUBLE) 173
112 .addScalar("w", StandardBasicTypes.DOUBLE) 174 protected static void loadQRanges(
175 Session session,
176 WstValueTable.Column [] columns,
177 Wst wst
178 ) {
179 SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_QS)
180 .addScalar("column_pos", StandardBasicTypes.INTEGER)
113 .addScalar("q", StandardBasicTypes.DOUBLE) 181 .addScalar("q", StandardBasicTypes.DOUBLE)
114 .addScalar("column_pos", StandardBasicTypes.INTEGER); 182 .addScalar("a", StandardBasicTypes.DOUBLE)
183 .addScalar("b", StandardBasicTypes.DOUBLE);
115 184
116 sqlQuery.setInteger("wst_id", wst.getId()); 185 sqlQuery.setInteger("wst_id", wst.getId());
117 186
118 WstValueTable valueTable = new WstValueTable(columns); 187 List<Object []> qRanges = sqlQuery.list();
119 188
120 int lastColumnNo = -1; 189 int start = -1;
121 WstValueTable.Row row = null; 190 int Q = qRanges.size();
122 191 Integer lastColumn = null;
123 Double lastQ = -Double.MAX_VALUE; 192
124 boolean qSorted = true; 193 for (int i = 0; i < Q; ++i) {
125 194 Object [] qRange = qRanges.get(i);
126 for (Object r: sqlQuery.list()) { 195 Integer columnId = (Integer)qRange[0];
127 Object[] result = (Object[]) r; 196 if (lastColumn == null) {
128 197 lastColumn = columnId;
129 double km = (Double) result[0]; 198 start = i;
130 Double w = (Double) result[1]; 199 }
131 Double q = (Double) result[2]; 200 else if (!lastColumn.equals(columnId)) {
132 int columnNo = (Integer)result[3]; 201 QRangeTree qRangeTree = new QRangeTree(qRanges, start, i);
133 202 columns[lastColumn].setQRangeTree(qRangeTree);
134 if (columnNo > lastColumnNo) { // new row 203 lastColumn = columnId;
135 if (row != null) { 204 start = i;
136 row.qSorted = qSorted; 205 }
137 valueTable.rows.add(row); 206 }
138 } 207
139 row = new WstValueTable.Row( 208 if (start != -1) {
140 km, 209 QRangeTree qRangeTree = new QRangeTree(qRanges, start, Q);
141 new double[columnNo+1], 210 columns[lastColumn].setQRangeTree(qRangeTree);
142 new double[columnNo+1]); 211 }
143 lastQ = -Double.MAX_VALUE; 212
144 qSorted = true; 213 /* This is debug code to visualize the q ranges trees
145 } 214
146 215 java.io.PrintWriter out = null;
147 row.ws[columnNo] = w != null ? w : Double.NaN; 216 try {
148 row.qs[columnNo] = q != null ? q : Double.NaN; 217 out = new java.io.PrintWriter(
149 218 new java.io.FileWriter(
150 if (qSorted && (q == null || lastQ > q)) { 219 "/tmp/qranges" + System.currentTimeMillis() + ".dot"));
151 qSorted = false; 220
152 } 221 out.println("graph \"Q ranges trees\" {");
153 lastQ = q; 222
154 223 for (int i = 0; i < columns.length; ++i) {
155 lastColumnNo = columnNo; 224 QRangeTree tree = columns[i].getQRangeTree();
156 } 225 out.println(tree.toGraph());
157 226 }
158 if (row != null) { 227
159 valueTable.rows.add(row); 228 out.println("}");
160 } 229
161 230 out.flush();
162 // XXX: should not be necessary 231 }
163 valueTable.sortRows(); 232 catch (java.io.IOException ioe) {
164 233 log.error(ioe);
165 return valueTable; 234 }
166 } 235 finally {
236 if (out != null) {
237 out.close();
238 }
239 }
240 */
241 }
242
167 } 243 }
168 // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : 244 // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :
http://dive4elements.wald.intevation.org