Mercurial > dive4elements > river
view etl/src/main/resources/sql/flys-common.properties @ 9697:15404504ea9e
yet another attempt
author | dnt_bjoernsen <d.tironi@bjoernsen.de> |
---|---|
date | Wed, 20 Jan 2021 16:17:32 +0100 |
parents | e5675e85ac38 |
children |
line wrap: on
line source
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, station \ 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 dt.gauge_id = :gauge_id AND dt.kind = 1 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 delete.discharge.table.values = \ DELETE from discharge_table_values where table_id = :id delete.discharge.table = \ DELETE from discharge_tables where id = :id and kind = 1