Mercurial > dive4elements > river

annotate flys-aft/src/main/resources/sql/flys-common.properties @ 4106:9ba12119e662

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added SQL statements to make the connection to the AFT database Oracle compatible. flys-aft/trunk@3641 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 10 Jan 2012 11:48:40 +0000
parents 83c72bc07591
children 8ba11e13ae8a
rev   line source
4075
dbd0b3b1b8b8 Sync each river.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4068
diff changeset
1 select.river = SELECT id, name FROM rivers
4085
067341e86375 Separate FLYS gauges to be updated or to create.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4076
diff changeset
2 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
3 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
4 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
5 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
6 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
7 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
8 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
9 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
10 insert.dischargetable = INSERT INTO discharge_tables (id, gauge_id, description, kind, time_interval_id) \
d556e29592f5 Create new discharge tables if needed.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4089
diff changeset
11 VALUES (:id, :gauge_id, :description, 0, :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
12 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
13 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
14 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
15 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
16 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
17 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
18 WHERE gauge_id = :gauge_id
http://dive4elements.wald.intevation.org