Mercurial > dive4elements > river

comparison artifacts/src/main/java/org/dive4elements/river/artifacts/model/minfo/SedimentLoadFactory.java @ 8072:5f28aa1be795

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
author Tom Gottfried <tom@intevation.de>
date Wed, 06 Aug 2014 19:17:12 +0200
parents 5e3f4b4fcb28
children
comparison
equal deleted inserted replaced
8071:6e6506ec0ae0 8072:5f28aa1be795
39 // Cache name/keys 39 // Cache name/keys
40 public static final String LOADS_CACHE_NAME = "sedimentloads"; 40 public static final String LOADS_CACHE_NAME = "sedimentloads";
41 public static final String LOAD_DATA_CACHE_NAME = "sedimentload-data"; 41 public static final String LOAD_DATA_CACHE_NAME = "sedimentload-data";
42 42
43 /** Query to get description and start year of single type 43 /** Query to get description and start year of single type
44 * sediment_yields. */ 44 * sediment_load_lss. */
45 public static final String SQL_SELECT_SINGLES = 45 public static final String SQL_SELECT_SINGLES =
46 "SELECT DISTINCT " + 46 "SELECT DISTINCT " +
47 " sy.description AS description, " + 47 " sy.description AS description, " +
48 " ti.start_time AS year " + 48 " ti.start_time AS year " +
49 " FROM sediment_yield sy " + 49 " FROM sediment_load_ls sy " +
50 " JOIN rivers r ON sy.river_id = r.id " + 50 " JOIN rivers r ON sy.river_id = r.id " +
51 " JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " + 51 " JOIN sediment_load_ls_values syv ON sy.id = syv.sediment_load_ls_id " +
52 " JOIN time_intervals ti ON sy.time_interval_id = ti.id " + 52 " JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
53 " WHERE r.name = :name " + 53 " WHERE r.name = :name " +
54 " AND ti.stop_time IS NULL " + 54 " AND ti.stop_time IS NULL " +
55 " AND syv.station BETWEEN :startKm AND :endKm"; 55 " AND syv.station BETWEEN :startKm AND :endKm";
56 56
57 /** Query to get fraction name of single sediment_yield. */ 57 /** Query to get fraction name of single sediment_load_ls. */
58 public static final String SQL_SELECT_SINGLE_TIMES_BY_ID = 58 public static final String SQL_SELECT_SINGLE_TIMES_BY_ID =
59 "SELECT DISTINCT " + 59 "SELECT DISTINCT " +
60 " ti.start_time AS starttime, " + 60 " ti.start_time AS starttime, " +
61 " ti.stop_time AS stoptime " + 61 " ti.stop_time AS stoptime " +
62 " FROM sediment_yield sy " + 62 " FROM sediment_load_ls sy " +
63 " JOIN time_intervals ti ON ti.id = sy.time_interval_id " + 63 " JOIN time_intervals ti ON ti.id = sy.time_interval_id " +
64 " WHERE sy.id = :id "; 64 " WHERE sy.id = :id ";
65 65
66 /** Query to get fraction name of single sediment_yield. */ 66 /** Query to get fraction name of single sediment_load_ls. */
67 public static final String SQL_SELECT_SINGLE_FRACTION_BY_ID = 67 public static final String SQL_SELECT_SINGLE_FRACTION_BY_ID =
68 "SELECT DISTINCT " + 68 "SELECT DISTINCT " +
69 " gf.name AS fraction " + 69 " gf.name AS fraction " +
70 " FROM sediment_yield sy " + 70 " FROM sediment_load_ls sy " +
71 " JOIN grain_fraction gf ON gf.id = grain_fraction_id " + 71 " JOIN grain_fraction gf ON gf.id = grain_fraction_id " +
72 " WHERE sy.id = :id "; 72 " WHERE sy.id = :id ";
73 73
74 /** Query to get unit name of single sediment_yield. */ 74 /** Query to get unit name of single sediment_load_ls. */
75 public static final String SQL_SELECT_SINGLE_UNIT_BY_ID = 75 public static final String SQL_SELECT_SINGLE_UNIT_BY_ID =
76 "SELECT DISTINCT " + 76 "SELECT DISTINCT " +
77 " u.name AS unit " + 77 " u.name AS unit " +
78 " FROM sediment_yield sy " + 78 " FROM sediment_load_ls sy " +
79 " JOIN units u ON u.id = unit_id " + 79 " JOIN units u ON u.id = unit_id " +
80 " WHERE sy.id = :id "; 80 " WHERE sy.id = :id ";
81 81
82 /** Query to get description of single sediment_yield. */ 82 /** Query to get description of single sediment_load_ls. */
83 public static final String SQL_SELECT_SINGLE_BY_ID = 83 public static final String SQL_SELECT_SINGLE_BY_ID =
84 "SELECT DISTINCT " + 84 "SELECT DISTINCT " +
85 " sy.description AS description " + 85 " sy.description AS description " +
86 " FROM sediment_yield sy " + 86 " FROM sediment_load_ls sy " +
87 " WHERE sy.id = :id "; 87 " WHERE sy.id = :id ";
88 88
89 /** Query to get description, name and time range for official 89 /** Query to get description, name and time range for official
90 * epoch-type sediment yields. */ 90 * epoch-type sediment yields. */
91 public static final String SQL_SELECT_OFFEPOCHS = 91 public static final String SQL_SELECT_OFFEPOCHS =
92 "SELECT DISTINCT " + 92 "SELECT DISTINCT " +
93 " ti.start_time AS startYear, " + 93 " ti.start_time AS startYear, " +
94 " ti.stop_time AS end " + 94 " ti.stop_time AS end " +
95 " FROM sediment_yield sy " + 95 " FROM sediment_load_ls sy " +
96 " JOIN rivers r ON sy.river_id = r.id " + 96 " JOIN rivers r ON sy.river_id = r.id " +
97 " JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " + 97 " JOIN sediment_load_ls_values syv ON sy.id = syv.sediment_load_ls_id " +
98 " JOIN time_intervals ti ON sy.time_interval_id = ti.id " + 98 " JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
99 " WHERE r.name = :name " + 99 " WHERE r.name = :name " +
100 " AND ti.stop_time IS NOT NULL " + 100 " AND ti.stop_time IS NOT NULL " +
101 " AND syv.station BETWEEN :startKm AND :endKm " + 101 " AND syv.station BETWEEN :startKm AND :endKm " +
102 " AND sy.kind = 1"; 102 " AND sy.kind = 1";
107 "SELECT DISTINCT " + 107 "SELECT DISTINCT " +
108 " sy.description AS description, " + 108 " sy.description AS description, " +
109 " ti.start_time AS startYear, " + 109 " ti.start_time AS startYear, " +
110 " ti.stop_time AS end, " + 110 " ti.stop_time AS end, " +
111 " u.name AS unit" + 111 " u.name AS unit" +
112 " FROM sediment_yield sy " + 112 " FROM sediment_load_ls sy " +
113 " JOIN rivers r ON sy.river_id = r.id " + 113 " JOIN rivers r ON sy.river_id = r.id " +
114 " JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " + 114 " JOIN sediment_load_ls_values syv ON sy.id = syv.sediment_load_ls_id " +
115 " JOIN time_intervals ti ON sy.time_interval_id = ti.id " + 115 " JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
116 " JOIN units u ON u.id = sy.unit_id " + 116 " JOIN units u ON u.id = sy.unit_id " +
117 " WHERE r.name = :name " + 117 " WHERE r.name = :name " +
118 " AND ti.stop_time IS NOT NULL " + 118 " AND ti.stop_time IS NOT NULL " +
119 " AND sy.kind = 0" + 119 " AND sy.kind = 0" +
124 " sy.description AS description, " + 124 " sy.description AS description, " +
125 " syv.value AS load, " + 125 " syv.value AS load, " +
126 " syv.station AS km, " + 126 " syv.station AS km, " +
127 " u.name AS unit, " + 127 " u.name AS unit, " +
128 " gf.name AS fraction " + 128 " gf.name AS fraction " +
129 " FROM sediment_yield sy " + 129 " FROM sediment_load_ls sy " +
130 " JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " + 130 " JOIN sediment_load_ls_values syv ON sy.id = syv.sediment_load_ls_id " +
131 " JOIN units u ON u.id = sy.unit_id" + 131 " JOIN units u ON u.id = sy.unit_id" +
132 " JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " + 132 " JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " +
133 " WHERE sy.id = :id" + 133 " WHERE sy.id = :id" +
134 " ORDER BY syv.station"; 134 " ORDER BY syv.station";
135 135
138 " sy.description AS description, " + 138 " sy.description AS description, " +
139 " ti.start_time AS year, " + 139 " ti.start_time AS year, " +
140 " syv.value AS load, " + 140 " syv.value AS load, " +
141 " syv.station AS km, " + 141 " syv.station AS km, " +
142 " u.name AS unit " + 142 " u.name AS unit " +
143 " FROM sediment_yield sy " + 143 " FROM sediment_load_ls sy " +
144 " JOIN rivers r ON sy.river_id = r.id " + 144 " JOIN rivers r ON sy.river_id = r.id " +
145 " JOIN time_intervals ti ON sy.time_interval_id = ti.id " + 145 " JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
146 " JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " + 146 " JOIN sediment_load_ls_values syv ON sy.id = syv.sediment_load_ls_id " +
147 " JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " + 147 " JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " +
148 " JOIN units u ON u.id = sy.unit_id" + 148 " JOIN units u ON u.id = sy.unit_id" +
149 " WHERE r.name = :name " + 149 " WHERE r.name = :name " +
150 " AND ti.start_time BETWEEN :begin AND :end " + 150 " AND ti.start_time BETWEEN :begin AND :end " +
151 " AND ti.stop_time IS NULL " + 151 " AND ti.stop_time IS NULL " +
159 " ti.start_time AS startYear, " + 159 " ti.start_time AS startYear, " +
160 " ti.stop_time AS end, " + 160 " ti.stop_time AS end, " +
161 " syv.value AS load, " + 161 " syv.value AS load, " +
162 " syv.station AS km, " + 162 " syv.station AS km, " +
163 " u.name AS unit " + 163 " u.name AS unit " +
164 " FROM sediment_yield sy " + 164 " FROM sediment_load_ls sy " +
165 " JOIN rivers r ON sy.river_id = r.id " + 165 " JOIN rivers r ON sy.river_id = r.id " +
166 " JOIN time_intervals ti ON sy.time_interval_id = ti.id " + 166 " JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
167 " JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " + 167 " JOIN sediment_load_ls_values syv ON sy.id = syv.sediment_load_ls_id " +
168 " JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " + 168 " JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " +
169 " JOIN units u ON u.id = sy.unit_id" + 169 " JOIN units u ON u.id = sy.unit_id" +
170 " WHERE r.name = :name " + 170 " WHERE r.name = :name " +
171 " AND sy.description = :descr " + 171 " AND sy.description = :descr " +
172 " AND gf.name = 'unknown' " + 172 " AND gf.name = 'unknown' " +
176 public static final String SQL_SELECT_UNKNOWN = 176 public static final String SQL_SELECT_UNKNOWN =
177 "SELECT DISTINCT " + 177 "SELECT DISTINCT " +
178 " sy.description AS description, " + 178 " sy.description AS description, " +
179 " ti.start_time AS startYear, " + 179 " ti.start_time AS startYear, " +
180 " ti.stop_time AS end " + 180 " ti.stop_time AS end " +
181 "FROM sediment_yield sy " + 181 "FROM sediment_load_ls sy " +
182 " JOIN rivers r ON sy.river_id = r.id " + 182 " JOIN rivers r ON sy.river_id = r.id " +
183 " JOIN time_intervals ti ON sy.time_interval_id = ti.id " + 183 " JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
184 " JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " + 184 " JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " +
185 " JOIN units u ON sy.unit_id = u.id " + 185 " JOIN units u ON sy.unit_id = u.id " +
186 "WHERE r.name = :river " + 186 "WHERE r.name = :river " +
194 " ti.start_time AS startYear, " + 194 " ti.start_time AS startYear, " +
195 " syv.value AS load, " + 195 " syv.value AS load, " +
196 " syv.station AS km," + 196 " syv.station AS km," +
197 " ti.stop_time AS endYear, " + 197 " ti.stop_time AS endYear, " +
198 " u.name AS unit" + 198 " u.name AS unit" +
199 " FROM sediment_yield sy " + 199 " FROM sediment_load_ls sy " +
200 " JOIN rivers r ON sy.river_id = r.id " + 200 " JOIN rivers r ON sy.river_id = r.id " +
201 " JOIN time_intervals ti ON sy.time_interval_id = ti.id " + 201 " JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
202 " JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " + 202 " JOIN sediment_load_ls_values syv ON sy.id = syv.sediment_load_ls_id " +
203 " JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " + 203 " JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " +
204 " JOIN units u ON u.id = sy.unit_id " + 204 " JOIN units u ON u.id = sy.unit_id " +
205 " WHERE r.name = :name " + 205 " WHERE r.name = :name " +
206 " AND ti.start_time BETWEEN :sbegin AND :send " + 206 " AND ti.start_time BETWEEN :sbegin AND :send " +
207 " AND ti.stop_time BETWEEN :ebegin AND :eend " + 207 " AND ti.stop_time BETWEEN :ebegin AND :eend " +
http://dive4elements.wald.intevation.org