Mercurial > dive4elements > river

comparison artifacts/src/main/java/org/dive4elements/river/artifacts/model/WstValueTableFactory.java @ 5838:5aa05a7a34b7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Rename modules to more fitting names.
author Sascha L. Teichmann <teichmann@intevation.de>
date Thu, 25 Apr 2013 15:23:37 +0200
parents flys-artifacts/src/main/java/org/dive4elements/river/artifacts/model/WstValueTableFactory.java@bd047b71ab37
children 4897a58c8746
comparison
equal deleted inserted replaced
5837:d9901a08d0a6 5838:5aa05a7a34b7
1 package org.dive4elements.river.artifacts.model;
2
3 import java.util.Arrays;
4 import java.util.List;
5 import java.util.ArrayList;
6
7 import net.sf.ehcache.Cache;
8 import net.sf.ehcache.Element;
9
10 import org.dive4elements.river.artifacts.cache.CacheFactory;
11
12 import org.dive4elements.river.backend.SessionHolder;
13
14 import org.apache.log4j.Logger;
15
16 import org.dive4elements.river.model.River;
17 import org.dive4elements.river.model.Wst;
18
19 import org.hibernate.Session;
20 import org.hibernate.Query;
21 import org.hibernate.SQLQuery;
22
23 import org.hibernate.type.StandardBasicTypes;
24
25 /**
26 * Creates WstValueTable s from database.
27 * WstValueTable s are used to interpolate given w/q/km values.
28 */
29 public class WstValueTableFactory
30 {
31 private static Logger log = Logger.getLogger(WstValueTableFactory.class);
32
33 public static final int DEFAULT_KIND = 0;
34
35 // TODO: put this into a property file
36
37 public static final String HQL_WST =
38 "from Wst where river=:river and kind=:kind";
39
40 public static final String SQL_SELECT_NAMES_POS =
41 "SELECT position, name FROM wst_columns " +
42 "WHERE wst_id = :wst_id ORDER BY position";
43
44 /** Select Qs for wst (view sorted by column). */
45 public static final String SQL_SELECT_QS =
46 "SELECT column_pos, q, a, b FROM wst_q_values " +
47 "WHERE wst_id = :wst_id";
48
49 // (sorted by km)
50 public static final String SQL_SELECT_WS =
51 "SELECT km, w, column_pos FROM wst_w_values " +
52 "WHERE wst_id = :wst_id";
53
54 /** Statement to query qranges of a single column. */
55 public static final String SQL_SELECT_QS_AT_COL =
56 "SELECT q, a, b FROM wst_q_values " +
57 "WHERE wst_id = :wst_id AND column_pos = :column_pos";
58
59 // (sorted by km)
60 public static final String SQL_SELECT_WS_AT_COL =
61 "SELECT km, w FROM wst_w_values " +
62 "WHERE wst_id = :wst_id AND column_pos = :column_pos";
63
64
65 private WstValueTableFactory() {
66 }
67
68
69 public static WstValueTable getTable(River river) {
70 return getTable(river, DEFAULT_KIND);
71 }
72
73
74 /**
75 * Get WstValueTable to interpolate values of a given Wst.
76 */
77 public static WstValueTable getTable(int wst_id) {
78
79 Cache cache = CacheFactory.getCache(WstValueTableCacheKey.CACHE_NAME);
80
81 WstValueTableCacheKey cacheKey;
82
83 if (cache != null) {
84 // "-1" is the symbolic river-id for "no river, but wst_id".
85 cacheKey = new WstValueTableCacheKey(-1, wst_id);
86 Element element = cache.get(cacheKey);
87 if (element != null) {
88 log.debug("Got specific wst value table from cache");
89 return (WstValueTable) element.getValue();
90 }
91 }
92 else {
93 cacheKey = null;
94 }
95
96 Session session = SessionHolder.HOLDER.get();
97
98 // Fetch data for one column only.
99
100 WstValueTable.Column [] columns = loadColumns(session, wst_id);
101 int [] map = loadQRangesMap(session, columns, wst_id);
102 List<WstValueTable.Row> rows = loadRows(session, wst_id, columns.length, map);
103
104 WstValueTable valueTable = new WstValueTable(columns, rows);
105
106 if (valueTable != null && cacheKey != null) {
107 log.debug("Store wst value table in cache");
108 Element element = new Element(cacheKey, valueTable);
109 cache.put(element);
110 }
111
112 return valueTable;
113 }
114
115 /**
116 * Get Table for a specific column of a wst.
117 */
118 public static WstValueTable getWstColumnTable(int wst_id, int col_pos) {
119
120 Cache cache = CacheFactory.getCache(WstValueTableCacheKey.CACHE_NAME);
121
122 WstValueTableCacheKey cacheKey;
123
124 if (cache != null) {
125 // A negaitve/negative number is the symbolic 'river-id' for
126 // "no river and kind but wst_id and colpos".
127 cacheKey = new WstValueTableCacheKey(-wst_id, -col_pos);
128 Element element = cache.get(cacheKey);
129 if (element != null) {
130 log.debug("Got specific wst value table from cache");
131 return (WstValueTable) element.getValue();
132 }
133 }
134 else {
135 cacheKey = null;
136 }
137
138 Session session = SessionHolder.HOLDER.get();
139
140 // Fetch data for one column only.
141
142 WstValueTable.Column [] columns = loadColumn(session, wst_id, col_pos);
143 loadQRanges(session, columns, wst_id, col_pos);
144 List<WstValueTable.Row> rows = loadRowsOneColumn(session, wst_id, col_pos);
145
146 WstValueTable valueTable = new WstValueTable(columns, rows);
147
148 if (valueTable != null && cacheKey != null) {
149 log.debug("Store wst value table in cache (wst: "
150 + wst_id + "/ col: " + col_pos + ")");
151 Element element = new Element(cacheKey, valueTable);
152 cache.put(element);
153 }
154
155 return valueTable;
156 }
157
158
159 /**
160 * Get table for first wst of given kind at given river.
161 */
162 public static WstValueTable getTable(River river, int kind) {
163
164 Cache cache = CacheFactory.getCache(WstValueTableCacheKey.CACHE_NAME);
165
166 WstValueTableCacheKey cacheKey;
167
168 if (cache != null) {
169 cacheKey = new WstValueTableCacheKey(river.getId(), kind);
170 Element element = cache.get(cacheKey);
171 if (element != null) {
172 log.debug("got wst value table from cache");
173 return (WstValueTable)element.getValue();
174 }
175 }
176 else {
177 cacheKey = null;
178 }
179
180 WstValueTable valueTable = getTableUncached(river, kind);
181
182 if (valueTable != null && cacheKey != null) {
183 log.debug("store wst value table in cache");
184 Element element = new Element(cacheKey, valueTable);
185 cache.put(element);
186 }
187
188 return valueTable;
189 }
190
191 public static WstValueTable getTableUncached(River river) {
192 return getTableUncached(river, DEFAULT_KIND);
193 }
194
195 public static WstValueTable getTableUncached(River river, int kind) {
196
197 Session session = SessionHolder.HOLDER.get();
198
199 Wst wst = loadWst(session, river, kind);
200
201 if (wst == null) {
202 return null;
203 }
204
205 WstValueTable.Column [] columns = loadColumns(session, wst);
206
207 int map [] = loadQRangesMap(session, columns, wst);
208
209 List<WstValueTable.Row> rows =
210 loadRows(session, wst.getId(), columns.length, map);
211
212 return new WstValueTable(columns, rows);
213 }
214
215 /**
216 * @param kind Kind of wst.
217 */
218 protected static Wst loadWst(Session session, River river, int kind) {
219 Query query = session.createQuery(HQL_WST);
220 query.setParameter("river", river);
221 query.setInteger("kind", kind);
222
223 List<Wst> wsts = query.list();
224
225 // TODO Multiple wsts can match, why return just the first one?
226 return wsts.isEmpty() ? null : wsts.get(0);
227 }
228
229
230 /**
231 * Load rows with a single columns result.
232 *
233 * @param session session to use for querying db.
234 * @param wstId id of wst (in db).
235 * @param column_pos the column_pos (within the db) of the wst_value_table
236 * of which the values shall be fetched.
237 *
238 * @return resultant rows.
239 */
240 protected static List<WstValueTable.Row> loadRowsOneColumn(
241 Session session,
242 int wstId,
243 int column_pos
244 ) {
245 SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_WS_AT_COL)
246 .addScalar("km", StandardBasicTypes.DOUBLE)
247 .addScalar("w", StandardBasicTypes.DOUBLE);
248
249 sqlQuery.setInteger("wst_id", wstId);
250 sqlQuery.setInteger("column_pos", column_pos);
251
252 List<Object []> results = sqlQuery.list();
253
254 double [] ws = null;
255
256 List<WstValueTable.Row> rows =
257 new ArrayList<WstValueTable.Row>(results.size());
258
259 // Walk over rows.
260 for (Object [] result: results) {
261 ws = new double[1];
262 WstValueTable.Row row =
263 new WstValueTable.Row((Double) result[0], ws);
264 rows.add(row);
265
266 Double w = (Double) result[1];
267 ws[0] = w != null ? w : Double.NaN;
268 }
269
270 return rows;
271 }
272
273 protected static List<WstValueTable.Row> loadRows(
274 Session session,
275 int wst_id,
276 int numColumns
277 ) {
278 return loadRows(session, wst_id, numColumns, null);
279 }
280
281 protected static List<WstValueTable.Row> loadRows(
282 Session session,
283 int wst_id,
284 int numColumns,
285 int [] map
286 ) {
287 SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_WS)
288 .addScalar("km", StandardBasicTypes.DOUBLE)
289 .addScalar("w", StandardBasicTypes.DOUBLE)
290 .addScalar("column_pos", StandardBasicTypes.INTEGER);
291
292 sqlQuery.setInteger("wst_id", wst_id);
293
294 List<Object []> results = sqlQuery.list();
295
296 int lastColumn = Integer.MAX_VALUE;
297 double [] ws = null;
298
299 ArrayList<WstValueTable.Row> rows = new ArrayList<WstValueTable.Row>();
300
301 for (Object [] result: results) {
302 int column = (Integer)result[2];
303 if (column < lastColumn) {
304 ws = new double[numColumns];
305 Arrays.fill(ws, Double.NaN);
306 WstValueTable.Row row =
307 new WstValueTable.Row((Double)result[0], ws);
308 rows.add(row);
309 }
310 Double w = (Double)result[1];
311 int index = map != null ? map[column] : column;
312 ws[index] = w != null ? w : Double.NaN;
313 lastColumn = column;
314 }
315
316 rows.trimToSize();
317 return rows;
318 }
319
320 protected static List<WstValueTable.Row> loadRows(
321 Session session,
322 Wst wst,
323 int numColumns
324 ) {
325 return loadRows(session, wst.getId(), numColumns);
326 }
327
328
329 protected static WstValueTable.Column [] loadColumn(
330 Session session,
331 int wst_id,
332 int col_pos
333 ) {
334 return new WstValueTable.Column [] {
335 new WstValueTable.Column(WKmsFactory.getWKmsName(col_pos, wst_id))};
336 }
337
338
339 /**
340 * Get columns from wst-id.
341 */
342 protected static WstValueTable.Column [] loadColumns(
343 Session session,
344 int wst_id
345 ) {
346 SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_NAMES_POS)
347 .addScalar("position", StandardBasicTypes.INTEGER)
348 .addScalar("name", StandardBasicTypes.STRING);
349
350 sqlQuery.setInteger("wst_id", wst_id);
351
352 List<Object []> columnNames = sqlQuery.list();
353
354 WstValueTable.Column [] columns =
355 new WstValueTable.Column[columnNames.size()];
356
357 for (int i = 0; i < columns.length; ++i) {
358 columns[i] = new WstValueTable.Column(
359 (String)columnNames.get(i)[1]);
360 }
361 return columns;
362 }
363
364 /**
365 * Get columns from Wst.
366 */
367 protected static WstValueTable.Column [] loadColumns(
368 Session session,
369 Wst wst
370 ) {
371 return loadColumns(session, wst.getId());
372 }
373
374
375 /**
376 * Build a QRange-Tree.
377 */
378 protected static void loadQRanges(
379 Session session,
380 WstValueTable.Column [] columns,
381 int wst_id,
382 int column_pos
383 ) {
384 SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_QS_AT_COL)
385 .addScalar("q", StandardBasicTypes.DOUBLE)
386 .addScalar("a", StandardBasicTypes.DOUBLE)
387 .addScalar("b", StandardBasicTypes.DOUBLE);
388
389 sqlQuery.setInteger("wst_id", wst_id);
390 sqlQuery.setInteger("column_pos", column_pos);
391
392 List<Object []> qRanges = sqlQuery.list();
393
394 int qSize = qRanges.size();
395
396 QRangeTree qRangeTree = new QRangeTree(
397 qRanges, QRangeTree.WITHOUT_COLUMN, 0, qSize);
398 columns[0].setQRangeTree(qRangeTree);
399 }
400
401 protected static int [] loadQRangesMap(
402 Session session,
403 WstValueTable.Column [] columns,
404 int wst_id
405 ) {
406 SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_QS)
407 .addScalar("column_pos", StandardBasicTypes.INTEGER)
408 .addScalar("q", StandardBasicTypes.DOUBLE)
409 .addScalar("a", StandardBasicTypes.DOUBLE)
410 .addScalar("b", StandardBasicTypes.DOUBLE);
411
412 sqlQuery.setInteger("wst_id", wst_id);
413
414 List<Object []> qRanges = sqlQuery.list();
415
416 int start = -1;
417 int Q = qRanges.size();
418 Integer lastColumn = null;
419
420 for (int i = 0; i < Q; ++i) {
421 Object [] qRange = qRanges.get(i);
422 Integer columnId = (Integer)qRange[0];
423 if (lastColumn == null) {
424 lastColumn = columnId;
425 start = i;
426 }
427 else if (!lastColumn.equals(columnId)) {
428 QRangeTree qRangeTree = new QRangeTree(qRanges, start, i);
429 columns[lastColumn].setQRangeTree(qRangeTree);
430 lastColumn = columnId;
431 start = i;
432 }
433 }
434
435 if (start != -1) {
436 QRangeTree qRangeTree = new QRangeTree(qRanges, start, Q);
437 columns[lastColumn].setQRangeTree(qRangeTree);
438 }
439
440 return sortColumnsByAverageQ(columns);
441
442 /* This is debug code to visualize the q ranges trees
443
444 java.io.PrintWriter out = null;
445 try {
446 out = new java.io.PrintWriter(
447 new java.io.FileWriter(
448 "/tmp/qranges" + System.currentTimeMillis() + ".dot"));
449
450 out.println("graph \"Q ranges trees\" {");
451
452 for (int i = 0; i < columns.length; ++i) {
453 QRangeTree tree = columns[i].getQRangeTree();
454 out.println(tree.toGraph());
455 }
456
457 out.println("}");
458
459 out.flush();
460 }
461 catch (java.io.IOException ioe) {
462 log.error(ioe);
463 }
464 finally {
465 if (out != null) {
466 out.close();
467 }
468 }
469 */
470 }
471
472 private static final class QIndex implements Comparable<QIndex> {
473 double q;
474 int index;
475
476 QIndex(double q, int index) {
477 this.q = q;
478 this.index = index;
479 }
480
481 @Override
482 public int compareTo(QIndex other) {
483 double diff = q - other.q;
484 if (diff < 0d) return -1;
485 if (diff > 0d) return +1;
486 return 0;
487 }
488 } // class QIndex
489
490 /** Ensure that the q colums are sorted in ascending order. */
491 protected static int [] sortColumnsByAverageQ(WstValueTable.Column [] columns) {
492 QIndex [] order = new QIndex[columns.length];
493 for (int i = 0; i < order.length; ++i) {
494 QRangeTree tree = columns[i].getQRangeTree();
495 double avg = tree.averageQ();
496 double max = tree.maxQ();
497 double q = (avg+max)*0.5d;
498 order[i] = new QIndex(q, i);
499 }
500 Arrays.sort(order);
501 WstValueTable.Column [] copy = new WstValueTable.Column[order.length];
502 int [] map = new int[order.length];
503 for (int i = 0; i < copy.length; ++i) {
504 copy[i] = columns[order[i].index];
505 map[order[i].index] = i;
506 }
507 System.arraycopy(copy, 0, columns, 0, order.length);
508 return map;
509 }
510
511 protected static int [] loadQRangesMap(
512 Session session,
513 WstValueTable.Column [] columns,
514 Wst wst
515 ) {
516 return loadQRangesMap(session, columns, wst.getId());
517 }
518
519 }
520 // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :
http://dive4elements.wald.intevation.org