Mercurial > dive4elements > river
diff etl/src/main/resources/sql/flys-common.properties @ 5838:5aa05a7a34b7
Rename modules to more fitting names.
author | Sascha L. Teichmann <teichmann@intevation.de> |
---|---|
date | Thu, 25 Apr 2013 15:23:37 +0200 (2013-04-25) |
parents | flys-aft/src/main/resources/sql/flys-common.properties@11fba4dabe05 |
children | 33e514a0c6b7 |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/etl/src/main/resources/sql/flys-common.properties Thu Apr 25 15:23:37 2013 +0200 @@ -0,0 +1,57 @@ +select.rivers = \ + SELECT r.id AS id, r.name AS name, min(wcv.position) AS min_km, max(wcv.position) AS max_km \ + FROM rivers r \ + JOIN wsts w ON r.id = w.river_id \ + JOIN wst_columns wc ON w.id = wc.wst_id \ + JOIN wst_column_values wcv ON wcv.wst_column_id = wc.id \ + WHERE w.kind = 0 \ + GROUP BY r.id, r.name +select.gauges = \ + SELECT id, name, official_number \ + FROM gauges \ + WHERE river_id = :river_id +next.gauge.id = \ + SELECT NEXTVAL('GAUGES_ID_SEQ') AS gauge_id +insert.gauge = \ + INSERT INTO gauges (id, name, river_id, station, aeo, official_number, datum) \ + VALUES(:id, :name, :river_id, :station, :aeo, :official_number, :datum) +select.timeintervals = \ + SELECT id, start_time, stop_time FROM time_intervals +next.timeinterval.id = \ + SELECT NEXTVAL('TIME_INTERVALS_ID_SEQ') AS time_interval_id +insert.timeinterval = \ + INSERT INTO time_intervals (id, start_time, stop_time) \ + VALUES (:id, :start_time, :stop_time) +next.discharge.id = \ + SELECT NEXTVAL('DISCHARGE_TABLES_ID_SEQ') AS discharge_table_id +insert.dischargetable = \ + INSERT INTO discharge_tables \ + (id, gauge_id, description, bfg_id, kind, time_interval_id) \ + VALUES (:id, :gauge_id, :description, :bfg_id, 1, :time_interval_id) +select.discharge.table.values = \ + SELECT id, w, q FROM discharge_table_values WHERE table_id = :table_id +next.discharge.table.values.id = \ + SELECT NEXTVAL('DISCHARGE_TABLE_VALUES_ID_SEQ') AS discharge_table_values_id +insert.discharge.table.value = \ + INSERT INTO discharge_table_values (id, table_id, w, q) \ + VALUES (:id, :table_id, :w, :q) +delete.discharge.table.value = \ + DELETE FROM discharge_table_values WHERE id = :id +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, \ + dt.bfg_id AS bfg_id \ + FROM discharge_tables dt \ + LEFT OUTER JOIN time_intervals ti ON dt.time_interval_id = ti.id \ + WHERE gauge_id = :gauge_id +select.gauge.master.discharge.table = \ + SELECT \ + dt.id AS id, \ + dt.bfg_id AS bfg_id \ + FROM discharge_tables dt JOIN gauges g ON dt.gauge_id = g.id \ + WHERE g.id = :gauge_id AND dt.kind = 0 +update.bfg.id.discharge.table = \ + UPDATE discharge_tables SET bfg_id = :bfg_id WHERE id = :id