teichmann@4753: select.rivers = \ teichmann@4753: SELECT r.id AS id, r.name AS name, min(wcv.position) AS min_km, max(wcv.position) AS max_km \ teichmann@4753: FROM rivers r \ teichmann@4753: JOIN wsts w ON r.id = w.river_id \ teichmann@4753: JOIN wst_columns wc ON w.id = wc.wst_id \ teichmann@4753: JOIN wst_column_values wcv ON wcv.wst_column_id = wc.id \ teichmann@4753: WHERE w.kind = 0 \ teichmann@4753: GROUP BY r.id, r.name teichmann@5257: select.gauges = \ teichmann@6893: SELECT id, name, official_number, station \ teichmann@5257: FROM gauges \ teichmann@5257: WHERE river_id = :river_id teichmann@5257: next.gauge.id = \ teichmann@5257: SELECT NEXTVAL('GAUGES_ID_SEQ') AS gauge_id teichmann@5257: insert.gauge = \ teichmann@5257: INSERT INTO gauges (id, name, river_id, station, aeo, official_number, datum) \ teichmann@5257: VALUES(:id, :name, :river_id, :station, :aeo, :official_number, :datum) teichmann@5257: select.timeintervals = \ teichmann@5257: SELECT id, start_time, stop_time FROM time_intervals teichmann@5257: next.timeinterval.id = \ teichmann@5257: SELECT NEXTVAL('TIME_INTERVALS_ID_SEQ') AS time_interval_id teichmann@5257: insert.timeinterval = \ teichmann@5257: INSERT INTO time_intervals (id, start_time, stop_time) \ teichmann@5257: VALUES (:id, :start_time, :stop_time) teichmann@5257: next.discharge.id = \ teichmann@5257: SELECT NEXTVAL('DISCHARGE_TABLES_ID_SEQ') AS discharge_table_id teichmann@4775: insert.dischargetable = \ teichmann@4775: INSERT INTO discharge_tables \ teichmann@4775: (id, gauge_id, description, bfg_id, kind, time_interval_id) \ teichmann@4775: VALUES (:id, :gauge_id, :description, :bfg_id, 1, :time_interval_id) teichmann@5257: select.discharge.table.values = \ teichmann@5257: SELECT id, w, q FROM discharge_table_values WHERE table_id = :table_id teichmann@5257: next.discharge.table.values.id = \ teichmann@5257: SELECT NEXTVAL('DISCHARGE_TABLE_VALUES_ID_SEQ') AS discharge_table_values_id teichmann@5257: insert.discharge.table.value = \ teichmann@5257: INSERT INTO discharge_table_values (id, table_id, w, q) \ teichmann@5257: VALUES (:id, :table_id, :w, :q) teichmann@5257: delete.discharge.table.value = \ teichmann@5257: DELETE FROM discharge_table_values WHERE id = :id teichmann@4775: select.gauge.discharge.tables = \ teichmann@4775: SELECT \ teichmann@5257: dt.id AS id, \ teichmann@4775: dt.description AS description, \ teichmann@5257: ti.start_time AS start_time, \ teichmann@5257: ti.stop_time AS stop_time, \ teichmann@5257: dt.bfg_id AS bfg_id \ teichmann@4775: FROM discharge_tables dt \ teichmann@4775: LEFT OUTER JOIN time_intervals ti ON dt.time_interval_id = ti.id \ teichmann@5995: WHERE dt.gauge_id = :gauge_id AND dt.kind = 1 teichmann@5258: select.gauge.master.discharge.table = \ teichmann@5258: SELECT \ teichmann@5258: dt.id AS id, \ teichmann@5258: dt.bfg_id AS bfg_id \ teichmann@5258: FROM discharge_tables dt JOIN gauges g ON dt.gauge_id = g.id \ teichmann@5479: WHERE g.id = :gauge_id AND dt.kind = 0 teichmann@5258: update.bfg.id.discharge.table = \ teichmann@5258: UPDATE discharge_tables SET bfg_id = :bfg_id WHERE id = :id teichmann@5930: delete.discharge.table.values = \ teichmann@5930: DELETE from discharge_table_values where table_id = :id teichmann@5930: delete.discharge.table = \ teichmann@5930: DELETE from discharge_tables where id = :id and kind = 1