Mercurial > dive4elements > river

annotate flys-aft/src/main/resources/sql/flys-common.properties @ 4773:f0b3ad10f67f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
author Sascha L. Teichmann <teichmann@intevation.de>
date Sat, 05 Jan 2013 13:30:57 +0100
parents a310aceb2e51
children d9f1202cef78
rev   line source
4753
a310aceb2e51 Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4109
diff changeset
1 select.rivers = \
a310aceb2e51 Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4109
diff changeset
2 SELECT r.id AS id, r.name AS name, min(wcv.position) AS min_km, max(wcv.position) AS max_km \
a310aceb2e51 Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4109
diff changeset
3 FROM rivers r \
a310aceb2e51 Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4109
diff changeset
4 JOIN wsts w ON r.id = w.river_id \
a310aceb2e51 Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4109
diff changeset
5 JOIN wst_columns wc ON w.id = wc.wst_id \
a310aceb2e51 Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4109
diff changeset
6 JOIN wst_column_values wcv ON wcv.wst_column_id = wc.id \
a310aceb2e51 Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4109
diff changeset
7 WHERE w.kind = 0 \
a310aceb2e51 Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4109
diff changeset
8 GROUP BY r.id, r.name
4085
067341e86375 Separate FLYS gauges to be updated or to create.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4076
diff changeset
9 select.gauges = SELECT id, name, official_number FROM gauges WHERE river_id = :river_id
4087
aad1886ea226 Store new gauges in FLYS db.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4085
diff changeset
10 next.gauge.id = SELECT NEXTVAL('GAUGES_ID_SEQ') AS gauge_id
aad1886ea226 Store new gauges in FLYS db.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4085
diff changeset
11 insert.gauge = INSERT INTO gauges (id, name, river_id, station, aeo, official_number, datum) \
aad1886ea226 Store new gauges in FLYS db.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4085
diff changeset
12 VALUES(:id, :name, :river_id, :station, :aeo, :official_number, :datum)
4089
859b4781554a Prefetch existing time intervals from FLYS db.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4087
diff changeset
13 select.timeintervals = SELECT id, start_time, stop_time FROM time_intervals
4090
d556e29592f5 Create new discharge tables if needed.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4089
diff changeset
14 next.timeinterval.id = SELECT NEXTVAL('TIME_INTERVALS_ID_SEQ') AS time_interval_id
d556e29592f5 Create new discharge tables if needed.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4089
diff changeset
15 insert.timeinterval = INSERT INTO time_intervals (id, start_time, stop_time) VALUES (:id, :start_time, :stop_time)
d556e29592f5 Create new discharge tables if needed.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4089
diff changeset
16 next.discharge.id = SELECT NEXTVAL('DISCHARGE_TABLES_ID_SEQ') AS discharge_table_id
d556e29592f5 Create new discharge tables if needed.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4089
diff changeset
17 insert.dischargetable = INSERT INTO discharge_tables (id, gauge_id, description, kind, time_interval_id) \
4109
8ba11e13ae8a AFT-Import: Insert
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4097
diff changeset
18 VALUES (:id, :gauge_id, :description, 1, :time_interval_id)
4091
a91c7e982c32 Make W/Q values from AFT and FLYS loadable.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4090
diff changeset
19 select.discharge.table.values = SELECT id, w, q FROM discharge_table_values WHERE table_id = :table_id
4092
b3fc044f75ba Added code to store W/Q values into FLYS database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4091
diff changeset
20 next.discharge.table.values.id = SELECT NEXTVAL('DISCHARGE_TABLE_VALUES_ID_SEQ') AS discharge_table_values_id
4096
82f5266f881b Add code to build the difference of the W/Q values of two discharge tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4092
diff changeset
21 insert.discharge.table.value = INSERT INTO discharge_table_values (id, table_id, w, q) VALUES (:id, :table_id, :w, :q)
82f5266f881b Add code to build the difference of the W/Q values of two discharge tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4092
diff changeset
22 delete.discharge.table.value = DELETE FROM discharge_table_values WHERE id = :id
4097
83c72bc07591 Load all discharge tables from AFT and FLYS of a gauge which needs updating.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4096
diff changeset
23 select.gauge.discharge.tables = SELECT dt.id AS id, dt.description AS description, ti.start_time AS start_time, ti.stop_time AS stop_time \
83c72bc07591 Load all discharge tables from AFT and FLYS of a gauge which needs updating.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4096
diff changeset
24 FROM discharge_tables dt LEFT OUTER JOIN time_intervals ti ON dt.time_interval_id = ti.id \
83c72bc07591 Load all discharge tables from AFT and FLYS of a gauge which needs updating.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4096
diff changeset
25 WHERE gauge_id = :gauge_id
http://dive4elements.wald.intevation.org