Mercurial > gemma
changeset 3659:66f2cb789905 single-beam
Merged default into single-beam branch.
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Fri, 14 Jun 2019 12:02:58 +0200 |
| parents | 1c3df921361d (current diff) 7126fdc5779a (diff) |
| children | b30c0b02e8fb |
| files | pkg/imports/sr.go |
| diffstat | 28 files changed, 732 insertions(+), 247 deletions(-) [+] |
line wrap: on
line diff
--- a/client/src/assets/application.scss Fri Jun 14 11:59:14 2019 +0200 +++ b/client/src/assets/application.scss Fri Jun 14 12:02:58 2019 +0200 @@ -222,3 +222,8 @@ width: 50% !important; height: 50% !important; } + +.custom-control-input:checked~.custom-control-label::before { + border-color: $color-info; + background-color: $color-info; +}
--- a/client/src/components/Login.vue Fri Jun 14 11:59:14 2019 +0200 +++ b/client/src/components/Login.vue Fri Jun 14 12:02:58 2019 +0200 @@ -52,7 +52,7 @@ </div> <button v-if="showPasswordReset == false" - class="btn btn-primary btn-block shadow-sm" + class="btn btn-info btn-block shadow-sm" :disabled="submitted || showPasswordReset" type="submit" >
--- a/client/src/components/fairway/Fairwayprofile.vue Fri Jun 14 11:59:14 2019 +0200 +++ b/client/src/components/fairway/Fairwayprofile.vue Fri Jun 14 12:02:58 2019 +0200 @@ -94,8 +94,9 @@ </DiagramLegend> <div ref="diagramContainer" - class="d-flex flex-fill justify-content-center align-items-center diagram-container" + class="d-flex flex-fill justify-content-center align-items-center diagram-container position-relative" > + <div class="direction-indicator"></div> <div v-if="!fairwayData"> <translate>No data available.</translate> </div> @@ -104,6 +105,30 @@ </div> </template> +<style lang="sass" scoped> +.direction-indicator + width: 70px + height: 0 + border-top: dashed 2px #333 + position: absolute + bottom: 20px + left: 115px + margin-left: -35px + &::after + content: "" + width: 0 + height: 0 + border-width: 10px + border-top-width: 5px + border-bottom-width: 5px + border-style: solid + border-color: transparent + border-left-color: #333 + position: absolute + right: -20px + top: -6px +</style> + <script> /* This is Free Software under GNU Affero General Public License v >= 3.0 * without warranty, see README.md and license for details.
--- a/client/src/components/map/layers.js Fri Jun 14 11:59:14 2019 +0200 +++ b/client/src/components/map/layers.js Fri Jun 14 12:02:58 2019 +0200 @@ -126,8 +126,15 @@ // linestring new Style({ stroke: new Stroke({ + color: "#FFFFFF", + width: 5, + lineDash: [7, 7] + }) + }), + new Style({ + stroke: new Stroke({ color: "#333333", - width: 2, + width: 3, lineDash: [7, 7] }) }) @@ -230,9 +237,9 @@ visible: true, source: new ImageSource({ preload: 1, - url: "https://service.d4d-portal.info/wms/", + url: store.state.application.config.ecdis_wms_url, crossOrigin: "anonymous", - params: { LAYERS: "d4d", VERSION: "1.1.1", TILED: true } + params: JSON.parse(store.state.application.config.ecdis_wms_params) }) }), new ImageLayer({
--- a/client/src/components/systemconfiguration/DataAccuracy.vue Fri Jun 14 11:59:14 2019 +0200 +++ b/client/src/components/systemconfiguration/DataAccuracy.vue Fri Jun 14 12:02:58 2019 +0200 @@ -330,7 +330,17 @@ }, methods: { submit() { - this.$store.commit("application/config", this.config); + this.$store.dispatch("application/saveConfig", { + bn_revtime_multiplier: this.config.bn_revtime_multiplier, + gm_latest_hours: this.config.gm_latest_hours, + gm_min_values_14d: this.config.gm_min_values_14d, + gm_forecast_offset_24h: this.config.gm_forecast_offset_24h, + gm_forecast_offset_72h: this.config.gm_forecast_offset_72h, + gm_forecast_vs_reality_nsc_24h: this.config + .gm_forecast_vs_reality_nsc_24h, + gm_forecast_vs_reality_nsc_72h: this.config + .gm_forecast_vs_reality_nsc_72h + }); } } };
--- a/client/src/components/systemconfiguration/MapLayers.vue Fri Jun 14 11:59:14 2019 +0200 +++ b/client/src/components/systemconfiguration/MapLayers.vue Fri Jun 14 12:02:58 2019 +0200 @@ -5,14 +5,50 @@ <div class="row"> <div class="col-sm-6"> <div class="form-group"> - <label for="ecdis-url" class="font-weight-bold">ECDIS URL</label> + <label for="ecdis-url" class="font-weight-bold"> + ECDIS WMS URL + </label> <input type="url" class="form-control" placeholder="https://..." - v-model="config.ecdis_url" + @input="lookupWMSCapabilities()" + v-model="config.ecdis_wms_url" /> </div> + <label for="ecdis-layers"> + <translate>Layers</translate> + <transition name="fade" + ><font-awesome-icon + icon="spinner" + spin + v-if="availableWMSLayersLoading" + class="ml-2" + /></transition> + </label> + <div class="container-fluid"> + <div class="row"> + <div + class="custom-control custom-checkbox col-sm-4" + v-for="layer in availableWMSLayers" + :key="'layer-' + layer" + > + <input + type="checkbox" + class="custom-control-input" + v-model="selectedWMSLayers" + :id="'layer-' + layer" + :value="layer" + /> + <label + class="custom-control-label text-break" + :for="'layer-' + layer" + > + {{ layer }} + </label> + </div> + </div> + </div> </div> </div> </div> @@ -39,15 +75,84 @@ * Markus Kottländer <markus@intevation.de> */ import { mapState } from "vuex"; +import WMSCapabilities from "ol/format/WMSCapabilities"; +import { HTTP } from "@/lib/http"; + +const WMSCapabilitiesParser = new WMSCapabilities(); export default { + data() { + return { + selectedWMSLayers: [], + availableWMSLayers: [], + availableWMSLayersLoading: false, + lookupWMSCapabilitiesTimeout: null, + wmsVersion: "" + }; + }, computed: { ...mapState("application", ["config"]) }, methods: { + lookupWMSCapabilities() { + if (this.lookupWMSCapabilitiesTimeout) { + clearTimeout(this.lookupWMSCapabilitiesTimeout); + } + this.lookupWMSCapabilitiesTimeout = setTimeout(() => { + let url; + try { + let urlParts = new URL(this.config.ecdis_wms_url); + url = + urlParts.protocol + + "//" + + urlParts.host + + urlParts.pathname.trim("/") + + "/"; + } catch (e) { + url = this.config.ecdis_wms_url; + } + this.availableWMSLayersLoading = true; + HTTP.get(url + "?request=GetCapabilities&service=WMS") + .then(response => { + let capabilities = WMSCapabilitiesParser.read(response.data); + this.wmsVersion = capabilities.version; + this.availableWMSLayers = []; + this.getLayersRecursive(capabilities.Capability.Layer.Layer); + }) + .catch(() => { + this.availableWMSLayers = []; + }) + .finally(() => (this.availableWMSLayersLoading = false)); + }, 500); + }, + getLayersRecursive(layers) { + layers.forEach(l => { + if (l.hasOwnProperty("Layer")) { + this.getLayersRecursive(l.Layer); + } else { + this.availableWMSLayers.push(l.Name); + } + }); + }, submit() { - this.$store.commit("application/config", this.config); + this.$store.dispatch("application/saveConfig", { + ecdis_wms_url: this.config.ecdis_wms_url, + ecdis_wms_params: JSON.stringify({ + LAYERS: this.selectedWMSLayers + .filter(l => this.availableWMSLayers.find(al => al === l)) + .join(","), + VERSION: this.wmsVersion, + TILED: true + }) + }); } + }, + mounted() { + let ecdisWmsParams = JSON.parse(this.config.ecdis_wms_params); + if (ecdisWmsParams.LAYERS) { + this.selectedWMSLayers = ecdisWmsParams.LAYERS.split(","); + } + this.lookupWMSCapabilities(); } }; </script>
--- a/client/src/components/systemconfiguration/MorphologyClassbreaks.vue Fri Jun 14 11:59:14 2019 +0200 +++ b/client/src/components/systemconfiguration/MorphologyClassbreaks.vue Fri Jun 14 12:02:58 2019 +0200 @@ -8,11 +8,11 @@ <div class="d-flex flex-wrap"> <div class="input-group mb-3 mr-2 classbreak" - v-for="(value, i) in config.morphology_classbreaks" + v-for="(value, i) in morphologyClassbreaks" :key="i" > <input - v-model="config.morphology_classbreaks[i]" + v-model="morphologyClassbreaks[i]" type="number" min="0" step="0.1" @@ -22,7 +22,7 @@ <button class="btn btn-sm btn-outline-secondary" type="button" - @click="config.morphology_classbreaks.splice(i, 1)" + @click="morphologyClassbreaks.splice(i, 1)" > <font-awesome-icon icon="times" /> </button> @@ -31,11 +31,9 @@ <button class="btn btn-sm btn-success mb-3" @click=" - config.morphology_classbreaks.push( - config.morphology_classbreaks.length - ? config.morphology_classbreaks[ - config.morphology_classbreaks.length - 1 - ] + morphologyClassbreaks.push( + morphologyClassbreaks.length + ? morphologyClassbreaks[morphologyClassbreaks.length - 1] : 1 ) " @@ -51,11 +49,11 @@ <div class="d-flex flex-wrap"> <div class="input-group mb-3 mr-2 classbreak" - v-for="(value, i) in config.morphology_classbreaks_compare" + v-for="(value, i) in morphologyClassbreaksCompare" :key="i" > <input - v-model="config.morphology_classbreaks_compare[i]" + v-model="morphologyClassbreaksCompare[i]" type="number" step="0.1" class="form-control form-control-sm" @@ -64,7 +62,7 @@ <button class="btn btn-sm btn-outline-secondary" type="button" - @click="config.morphology_classbreaks_compare.splice(i, 1)" + @click="morphologyClassbreaksCompare.splice(i, 1)" > <font-awesome-icon icon="times" /> </button> @@ -73,10 +71,10 @@ <button class="btn btn-sm btn-success mb-3" @click=" - config.morphology_classbreaks_compare.push( - config.morphology_classbreaks_compare.length - ? config.morphology_classbreaks_compare[ - config.morphology_classbreaks_compare.length - 1 + morphologyClassbreaksCompare.push( + morphologyClassbreaksCompare.length + ? morphologyClassbreaksCompare[ + morphologyClassbreaksCompare.length - 1 ] : 1 ) @@ -122,13 +120,32 @@ import { mapState } from "vuex"; export default { + data() { + return { + morphologyClassbreaks: [], + morphologyClassbreaksCompare: [] + }; + }, computed: { ...mapState("application", ["config"]) }, methods: { submit() { - this.$store.commit("application/config", this.config); + this.$store.dispatch("application/saveConfig", { + morphology_classbreaks: this.morphologyClassbreaks.join(","), + morphology_classbreaks_compare: this.morphologyClassbreaksCompare.join( + "," + ) + }); } + }, + mounted() { + this.morphologyClassbreaks = this.config.morphology_classbreaks + .split(",") + .map(n => Number(n)); + this.morphologyClassbreaksCompare = this.config.morphology_classbreaks_compare + .split(",") + .map(n => Number(n)); } }; </script>
--- a/client/src/components/systemconfiguration/Systemconfiguration.vue Fri Jun 14 11:59:14 2019 +0200 +++ b/client/src/components/systemconfiguration/Systemconfiguration.vue Fri Jun 14 12:02:58 2019 +0200 @@ -6,6 +6,9 @@ <div class="text-left flex-fill" style="overflow: auto"> <PDFTemplates /> <ColorSettings v-if="isSysAdmin" /> + <MapLayers v-if="isSysAdmin" /> + <DataAccuracy v-if="isSysAdmin" /> + <MorphologyClassbreaks v-if="isSysAdmin" /> </div> <!-- card-body --> </div> @@ -35,7 +38,10 @@ components: { Spacer: () => import("../Spacer"), PDFTemplates: () => import("./PDFTemplates"), - ColorSettings: () => import("./ColorSettings") + ColorSettings: () => import("./ColorSettings"), + MapLayers: () => import("./MapLayers"), + DataAccuracy: () => import("./DataAccuracy"), + MorphologyClassbreaks: () => import("./MorphologyClassbreaks") }, computed: { ...mapGetters("user", ["isSysAdmin"]),
--- a/client/src/store/application.js Fri Jun 14 11:59:14 2019 +0200 +++ b/client/src/store/application.js Fri Jun 14 12:02:58 2019 +0200 @@ -14,6 +14,8 @@ * Bernhard E. Reiter <bernhard.reiter@intevation.de> */ +import { HTTP } from "@/lib/http"; +import { displayError, displayInfo } from "@/lib/errors"; import { version } from "../../package.json"; // initial state @@ -140,85 +142,32 @@ } }, actions: { - loadConfig({ commit, state }) { - if (!Object.keys(state.config).length) { - setTimeout(() => { - commit("config", { - ecdis_url: "https://service.d4d-portal.info/wms/", - bn_revtime_multiplier: 1.5, - gm_min_values_14d: 1124, - gm_latest_hours: 24, - gm_forecast_offset_24h: 15, - gm_forecast_offset_72h: 15, - gm_forecast_vs_reality_nsc_24h: -12.5, - gm_forecast_vs_reality_nsc_72h: -12.5, - morphology_classbreaks: [ - 1, - 1.5, - 1.7, - 1.9, - 2.1, - 2.3, - 2.5, - 2.7, - 2.9, - 3.1, - 3.3, - 3.5, - 4.0, - 4.5, - 5, - 5.5, - 6, - 6.5, - 7 - ], - morphology_classbreaks_compare: [ - -2, - -1.9, - -1.8, - -1.7, - -1.6, - -1.5, - -1.4, - -1.3, - -1.2, - -1.1, - -1, - -0.9, - -0.8, - -0.7, - -0.6, - -0.5, - -0.4, - -0.3, - -0.2, - -0.1, - 0, - 0.1, - 0.2, - 0.3, - 0.4, - 0.5, - 0.6, - 0.7, - 0.8, - 0.9, - 1, - 1.1, - 1.2, - 1.3, - 1.4, - 1.5, - 1.6, - 1.7, - 1.8, - 1.9, - 2 - ] + loadConfig({ commit }) { + HTTP.get("/system/settings", { + headers: { "X-Gemma-Auth": localStorage.getItem("token") } + }).then(response => { + commit("config", response.data); + }); + }, + saveConfig(context, config) { + HTTP.put("/system/settings", config, { + headers: { + "X-Gemma-Auth": localStorage.getItem("token"), + "Content-type": "application/json" + } + }) + .then(() => { + displayInfo({ + message: "Configuration saved!" }); - }, 1000); - } + }) + .catch(error => { + const { status, data } = error.response; + displayError({ + title: "Backend Error", + message: `${status}: ${data.message || data}` + }); + }); } } };
--- a/pkg/controllers/bottlenecks.go Fri Jun 14 11:59:14 2019 +0200 +++ b/pkg/controllers/bottlenecks.go Fri Jun 14 12:02:58 2019 +0200 @@ -33,7 +33,7 @@ const ( selectLimitingSQL = ` -SELECT limiting from waterway.bottlenecks WHERE objnam = $1` +SELECT limiting from waterway.bottlenecks WHERE NOT erased AND objnam = $1` selectAvailableDepthSQL = ` WITH data AS ( @@ -48,6 +48,7 @@ JOIN waterway.bottlenecks bn ON fa.bottleneck_id = bn.id WHERE + NOT bn.erased AND bn.objnam = $1 AND efa.level_of_service = $2 AND efa.measure_type = 'Measured' AND @@ -74,20 +75,6 @@ ORDER BY measure_date ` - selectGaugeLevelsSQL = ` -SELECT - grwl.depth_reference, - grwl.value -FROM waterway.gauges_reference_water_levels grwl - JOIN waterway.bottlenecks bns - ON grwl.location = bns.gauge_location - AND grwl.validity = bns.gauge_validity -WHERE bns.objnam = $1 AND ( - grwl.depth_reference like 'HDC%' OR - grwl.depth_reference like 'LDC%' OR - grwl.depth_reference like 'MW%' -) -` selectGaugeLDCSQL = ` SELECT grwl.value @@ -95,7 +82,7 @@ JOIN waterway.bottlenecks bns ON grwl.location = bns.gauge_location AND grwl.validity = bns.gauge_validity -WHERE bns.objnam = $1 AND grwl.depth_reference like 'LDC%' +WHERE NOT bns.erased AND bns.objnam = $1 AND grwl.depth_reference like 'LDC%' ` )
--- a/pkg/controllers/diff.go Fri Jun 14 11:59:14 2019 +0200 +++ b/pkg/controllers/diff.go Fri Jun 14 12:02:58 2019 +0200 @@ -36,7 +36,7 @@ const ( diffIDSQL = ` SELECT sd.id FROM - caching.sounding_differences sd JOIN + caching.sounding_differences sd JOIN waterway.sounding_results srm ON sd.minuend = srm.id JOIN waterway.sounding_results srs ON sd.subtrahend = srs.id WHERE srm.bottleneck_id = srs.bottleneck_id AND
--- a/pkg/controllers/routes.go Fri Jun 14 11:59:14 2019 +0200 +++ b/pkg/controllers/routes.go Fri Jun 14 12:02:58 2019 +0200 @@ -79,6 +79,15 @@ NoConn: true, })).Methods(http.MethodGet) + api.Handle("/system/settings", any(&JSONHandler{ + Handle: getSystemSettings, + })).Methods(http.MethodGet) + + api.Handle("/system/settings", any(&JSONHandler{ + Input: func(*http.Request) interface{} { return &map[string]string{} }, + Handle: setSystemSettings, + })).Methods(http.MethodPut) + api.Handle("/system/style/{feature}/{attr}", any(&JSONHandler{ Handle: getFeatureStyle, })).Methods(http.MethodGet)
--- a/pkg/controllers/search.go Fri Jun 14 11:59:14 2019 +0200 +++ b/pkg/controllers/search.go Fri Jun 14 12:02:58 2019 +0200 @@ -42,6 +42,7 @@ ST_AsGeoJSON(ST_Centroid(area))::json AS geom, 'bottleneck' AS type FROM waterway.bottlenecks + WHERE NOT erased ORDER BY objnam) r ` )
--- a/pkg/controllers/stretches.go Fri Jun 14 11:59:14 2019 +0200 +++ b/pkg/controllers/stretches.go Fri Jun 14 12:02:58 2019 +0200 @@ -35,14 +35,14 @@ distinct(b.objnam), b.limiting FROM waterway.sections s, waterway.bottlenecks b -WHERE ST_Intersects(b.area, s.area) AND s.name = $1` +WHERE NOT b.erased AND ST_Intersects(b.area, s.area) AND s.name = $1` selectStretchBottlenecks = ` SELECT distinct(b.objnam), b.limiting FROM waterway.stretches s, waterway.bottlenecks b -WHERE ST_Intersects(b.area, s.area) AND s.name = $1` +WHERE NOT b.erased AND ST_Intersects(b.area, s.area) AND s.name = $1` ) type (
--- a/pkg/controllers/surveys.go Fri Jun 14 11:59:14 2019 +0200 +++ b/pkg/controllers/surveys.go Fri Jun 14 12:02:58 2019 +0200 @@ -34,9 +34,10 @@ FROM waterway.bottlenecks AS b JOIN waterway.gauges AS g ON b.gauge_location = g.location AND b.gauge_validity = g.validity - JOIN waterway.sounding_results AS s ON b.id = s.bottleneck_id + JOIN waterway.sounding_results AS s ON b.bottleneck_id = s.bottleneck_id LEFT JOIN waterway.gauges_reference_water_levels AS r - USING (depth_reference, location, validity) + ON s.depth_reference = r.depth_reference + AND g.location = r.location AND g.validity = r.validity WHERE b.objnam = $1` )
--- a/pkg/controllers/system.go Fri Jun 14 11:59:14 2019 +0200 +++ b/pkg/controllers/system.go Fri Jun 14 12:02:58 2019 +0200 @@ -21,9 +21,10 @@ "net/http" "strings" + "github.com/gorilla/mux" + "gemma.intevation.de/gemma/pkg/config" "gemma.intevation.de/gemma/pkg/models" - "github.com/gorilla/mux" ) const ( @@ -33,6 +34,15 @@ setFeatureColourSQL = `UPDATE systemconf.feature_colours SET (r, g, b, a) = ($3, $4, $5, $6) WHERE feature_name = $1 AND style_attr = $2` + + getSettingsSQL = ` +SELECT config_key, config_val +FROM sys_admin.system_config` + + updateSettingSQL = ` +INSERT INTO sys_admin.system_config (config_key, config_val) +VALUES ($1, $2) +ON CONFLICT (config_key) DO UPDATE SET config_val = $2` ) // System status end points @@ -100,6 +110,75 @@ return } +func getSystemSettings( + _ interface{}, + req *http.Request, + conn *sql.Conn, +) (jr JSONResult, err error) { + + var rows *sql.Rows + if rows, err = conn.QueryContext(req.Context(), getSettingsSQL); err != nil { + return + } + defer rows.Close() + + settings := map[string]string{} + + for rows.Next() { + var key, val string + if err = rows.Scan(&key, &val); err != nil { + return + } + settings[key] = val + } + if err = rows.Err(); err != nil { + return + } + + jr = JSONResult{Result: settings} + return +} + +func setSystemSettings( + input interface{}, + req *http.Request, + conn *sql.Conn, +) (jr JSONResult, err error) { + + settings := input.(*map[string]string) + + ctx := req.Context() + var tx *sql.Tx + if tx, err = conn.BeginTx(ctx, nil); err != nil { + return + } + defer tx.Rollback() + + var setStmt *sql.Stmt + if setStmt, err = tx.PrepareContext(ctx, updateSettingSQL); err != nil { + return + } + defer setStmt.Close() + + for key, value := range *settings { + if _, err = setStmt.ExecContext(ctx, key, value); err != nil { + return + } + } + + if err = tx.Commit(); err != nil { + return + } + + jr = JSONResult{ + Code: http.StatusCreated, + Result: struct { + Result string `json:"result"` + }{"success"}, + } + return +} + // Map/Feature style end points func getFeatureStyle(
--- a/pkg/imports/bn.go Fri Jun 14 11:59:14 2019 +0200 +++ b/pkg/imports/bn.go Fri Jun 14 12:02:58 2019 +0200 @@ -24,6 +24,7 @@ "time" "gemma.intevation.de/gemma/pkg/soap/ifbn" + "github.com/jackc/pgx/pgtype" ) // Bottleneck is an import job to import @@ -43,16 +44,29 @@ const ( hasBottleneckSQL = ` -SELECT true FROM waterway.bottlenecks WHERE bottleneck_id = $1` +WITH upd AS ( + UPDATE waterway.bottlenecks SET + erased = true + WHERE bottleneck_id = $1 + AND NOT erased + -- Don't touch old entry if new validity contains old: will be updated + AND NOT validity <@ $2 + RETURNING 1 +) +-- Decide whether a new version will be INSERTed +SELECT EXISTS(SELECT 1 FROM upd) + OR NOT EXISTS(SELECT 1 FROM waterway.bottlenecks WHERE bottleneck_id = $1) +` insertBottleneckSQL = ` WITH -bounds (b) AS (VALUES (isrs_fromText($5)), (isrs_fromText($6))), +bounds (b) AS (VALUES (isrs_fromText($6)), (isrs_fromText($7))), r AS (SELECT isrsrange( (SELECT b FROM bounds ORDER BY b USING <~ FETCH FIRST ROW ONLY), (SELECT b FROM bounds ORDER BY b USING >~ FETCH FIRST ROW ONLY)) AS r) INSERT INTO waterway.bottlenecks ( bottleneck_id, + validity, gauge_location, gauge_validity, objnam, @@ -68,29 +82,89 @@ source_organization ) VALUES ( $1, - isrs_fromText($2), + $2, + isrs_fromText($3), COALESCE( (SELECT validity FROM waterway.gauges - WHERE location = isrs_fromText($2) AND NOT erased), + WHERE location = isrs_fromText($3) + AND validity @> lower(CAST($2 AS tstzrange))), tstzrange(NULL, NULL)), - $3, $4, + $5, (SELECT r FROM r), ISRSrange_area( ISRSrange_axis((SELECT r FROM r), - $14), + $15), (SELECT ST_Collect(CAST(area AS geometry)) FROM waterway.waterway_area)), - $7, $8, $9, $10, $11, $12, - $13 + $13, + $14 ) RETURNING id` + moveSRSQL = ` +UPDATE waterway.sounding_results +-- Associate measurements to matching bottleneck version +SET bottleneck_validity = $2 +WHERE bottleneck_id = $1 + AND CAST(date_info AS timestamptz) <@ CAST($2 AS tstzrange) +` + + fixBNValiditySQL = ` +UPDATE waterway.bottlenecks SET + -- Set enddate of old entry to new startdate in case of overlap: + validity = validity - $2 +WHERE bottleneck_id = $1 + AND validity && $2 + AND erased +` + + updateBottleneckSQL = ` +WITH +bounds (b) AS (VALUES (isrs_fromText($5)), (isrs_fromText($6))), +r AS (SELECT isrsrange( + (SELECT b FROM bounds ORDER BY b USING <~ FETCH FIRST ROW ONLY), + (SELECT b FROM bounds ORDER BY b USING >~ FETCH FIRST ROW ONLY)) AS r) +UPDATE waterway.bottlenecks b SET + gauge_location = isrs_fromtext($2), + gauge_validity = COALESCE( + (SELECT validity FROM waterway.gauges g + WHERE g.location = isrs_fromText($2) + AND g.validity @> lower(b.validity)), + tstzrange(NULL, NULL)), + objnam = $3, + nobjnm = $4, + stretch = (SELECT r FROM r), + area = ISRSrange_area( + ISRSrange_axis((SELECT r FROM r), $14), + (SELECT ST_Collect(CAST(area AS geometry)) + FROM waterway.waterway_area)), + rb = $7, + lb = $8, + responsible_country = $9, + revisiting_time = $10, + limiting = $11, + date_info = $12, + source_organization = $13, + validity = $15 +WHERE bottleneck_id = $1 + AND NOT erased + AND $12 > date_info +RETURNING id +` + + deleteBottleneckMaterialSQL = ` +DELETE FROM waterway.bottlenecks_riverbed_materials +WHERE bottleneck_id = $1 + AND riverbed <> ALL($2) +RETURNING riverbed +` + insertBottleneckMaterialSQL = ` INSERT INTO waterway.bottlenecks_riverbed_materials ( bottleneck_id, @@ -98,7 +172,8 @@ ) VALUES ( $1, $2 -)` +) ON CONFLICT (bottleneck_id, riverbed) DO NOTHING +` ) type bnJobCreator struct{} @@ -198,7 +273,8 @@ feedback.Info("Found %d bottlenecks for import", len(bns)) - var hasStmt, insertStmt, insertMaterialStmt, trackStmt *sql.Stmt + var hasStmt, insertStmt, moveSRStmt, fixValidityStmt, updateStmt, + deleteMaterialStmt, insertMaterialStmt, trackStmt *sql.Stmt for _, x := range []struct { sql string @@ -206,6 +282,10 @@ }{ {hasBottleneckSQL, &hasStmt}, {insertBottleneckSQL, &insertStmt}, + {moveSRSQL, &moveSRStmt}, + {fixBNValiditySQL, &fixValidityStmt}, + {updateBottleneckSQL, &updateStmt}, + {deleteBottleneckMaterialSQL, &deleteMaterialStmt}, {insertBottleneckMaterialSQL, &insertMaterialStmt}, {trackImportSQL, &trackStmt}, } { @@ -223,7 +303,8 @@ for _, bn := range bns { if err := storeBottleneck( ctx, importID, conn, feedback, bn, &nids, tolerance, - hasStmt, insertStmt, insertMaterialStmt, trackStmt); err != nil { + hasStmt, insertStmt, moveSRStmt, fixValidityStmt, updateStmt, + deleteMaterialStmt, insertMaterialStmt, trackStmt); err != nil { return nil, err } } @@ -249,28 +330,52 @@ bn *ifbn.BottleNeckType, nids *[]string, tolerance float64, - hasStmt, insertStmt, insertMaterialStmt, trackStmt *sql.Stmt, + hasStmt, insertStmt, moveSRStmt, fixValidityStmt, updateStmt, + deleteMaterialStmt, insertMaterialStmt, trackStmt *sql.Stmt, ) error { - - tx, err := conn.BeginTx(ctx, nil) - if err != nil { - return err - } - defer tx.Rollback() + feedback.Info("Processing %s (%s)", bn.OBJNAM, bn.Bottleneck_id) - var found bool - err = tx.StmtContext(ctx, hasStmt).QueryRowContext(ctx, - bn.Bottleneck_id).Scan(&found) - switch { - case err == sql.ErrNoRows: - // This is good. - case err != nil: - return err - case found: - feedback.Info("'%s' already in database. Skip", bn.OBJNAM) - // TODO: Deep comparison database vs. SOAP. + if bn.AdditionalData == nil || bn.AdditionalData.KeyValuePair == nil { + feedback.Warn("Missing validity information") return nil } + const ( + fromKey = "Valid_from_date" + toKey = "Valid_to_date" + ) + fromTo := make(map[string]time.Time) + for _, kv := range bn.AdditionalData.KeyValuePair { + k := string(kv.Key) + if k == fromKey || k == toKey { + if t, err := time.Parse(time.RFC3339, kv.Value); err != nil { + return err + } else { + fromTo[k] = t + } + } + } + + var tfrom, tto pgtype.Timestamptz + if t, ok := fromTo[fromKey]; ok { + tfrom.Set(t) + } else { + feedback.Warn("Missing start date") + return nil + } + var uBound pgtype.BoundType + if t, ok := fromTo[toKey]; ok { + tto.Set(t) + uBound = pgtype.Exclusive + } else { + uBound = pgtype.Unbounded + } + validity := pgtype.Tstzrange{ + Lower: tfrom, + Upper: tto, + LowerType: pgtype.Inclusive, + UpperType: uBound, + Status: pgtype.Present, + } rb, lb := splitRBLB(bn.Rb_lb) @@ -297,42 +402,149 @@ country = string(*bn.Responsible_country) } - var nid int64 + var materials []string + if bn.Riverbed != nil { + for _, material := range bn.Riverbed.Material { + if material != nil { + materials = append(materials, string(*material)) + } + } + } - err = tx.StmtContext(ctx, insertStmt).QueryRowContext( - ctx, + tx, err := conn.BeginTx(ctx, nil) + if err != nil { + return err + } + defer tx.Rollback() + + var isNew bool + var nid int64 + err = tx.StmtContext(ctx, hasStmt).QueryRowContext(ctx, bn.Bottleneck_id, - bn.Fk_g_fid, - bn.OBJNAM, - bn.NOBJNM, - bn.From_ISRS, bn.To_ISRS, - rb, - lb, - country, - revisitingTime, - limiting, - bn.Date_Info, - bn.Source, - tolerance, - ).Scan(&nid) - if err != nil { - feedback.Warn("Failed to insert '%s' into database", bn.OBJNAM) + validity, + ).Scan(&isNew) + switch { + case err != nil: feedback.Warn(handleError(err).Error()) + if err2 := tx.Rollback(); err2 != nil { + return err2 + } + return nil + case isNew: + err = tx.StmtContext(ctx, insertStmt).QueryRowContext( + ctx, + bn.Bottleneck_id, + &validity, + bn.Fk_g_fid, + bn.OBJNAM, + bn.NOBJNM, + bn.From_ISRS, bn.To_ISRS, + rb, + lb, + country, + revisitingTime, + limiting, + bn.Date_Info, + bn.Source, + tolerance, + ).Scan(&nid) + if err != nil { + feedback.Warn(handleError(err).Error()) + return nil + } + feedback.Info("insert new version") + case !isNew: + // try to update + err := tx.StmtContext(ctx, updateStmt).QueryRowContext(ctx, + bn.Bottleneck_id, + bn.Fk_g_fid, + bn.OBJNAM, + bn.NOBJNM, + bn.From_ISRS, bn.To_ISRS, + rb, + lb, + country, + revisitingTime, + limiting, + bn.Date_Info, + bn.Source, + tolerance, + &validity, + ).Scan(&nid) + switch { + case err == sql.ErrNoRows: + feedback.Info("unchanged") + if err := tx.Rollback(); err != nil { + return err + } + return nil + case err != nil: + feedback.Warn(handleError(err).Error()) + if err := tx.Rollback(); err != nil { + return err + } + return nil + default: + feedback.Info("update") + + // Remove obsolete riverbed materials + var pgMaterials pgtype.VarcharArray + pgMaterials.Set(materials) + mtls, err := tx.StmtContext(ctx, + deleteMaterialStmt).QueryContext(ctx, + nid, + &pgMaterials, + ) + if err != nil { + return err + } + defer mtls.Close() + for mtls.Next() { + var delMat string + if err := mtls.Scan(&delMat); err != nil { + return err + } + feedback.Warn("Removed riverbed material %s", delMat) + } + if err := mtls.Err(); err != nil { + return err + } + } + } + + // Move sounding results to new matching bottleneck version, if applicable + if _, err = tx.StmtContext(ctx, moveSRStmt).ExecContext(ctx, + bn.Bottleneck_id, + &validity, + ); err != nil { + feedback.Warn(handleError(err).Error()) + if err2 := tx.Rollback(); err2 != nil { + return err2 + } return nil } - if bn.Riverbed != nil { - for _, material := range bn.Riverbed.Material { - if material != nil { - mat := string(*material) - if _, err := tx.StmtContext(ctx, - insertMaterialStmt).ExecContext( - ctx, nid, material); err != nil { - feedback.Warn( - "Failed to insert riverbed material '%s' for bottleneck '%s'.", - mat, bn.OBJNAM) - feedback.Warn(handleError(err).Error()) - } + // Set end of validity of old version to start of new version + // in case of overlap + if _, err = tx.StmtContext(ctx, fixValidityStmt).ExecContext(ctx, + bn.Bottleneck_id, + validity, + ); err != nil { + feedback.Warn(handleError(err).Error()) + if err2 := tx.Rollback(); err2 != nil { + return err2 + } + return nil + } + + // Insert riverbed materials + if materials != nil { + for _, mat := range materials { + if _, err := tx.StmtContext(ctx, + insertMaterialStmt).ExecContext( + ctx, nid, mat); err != nil { + feedback.Warn("Failed to insert riverbed material '%s'", mat) + feedback.Warn(handleError(err).Error()) } } } @@ -345,7 +557,6 @@ if err = tx.Commit(); err != nil { return err } - feedback.Info("Inserted '%s' into database", bn.OBJNAM) *nids = append(*nids, bn.Bottleneck_id) return nil }
--- a/pkg/imports/fa.go Fri Jun 14 11:59:14 2019 +0200 +++ b/pkg/imports/fa.go Fri Jun 14 12:02:58 2019 +0200 @@ -47,7 +47,7 @@ const ( listBottlenecksSQL = ` -SELECT +SELECT DISTINCT bottleneck_id FROM waterway.bottlenecks WHERE responsible_country = users.current_user_country() @@ -79,7 +79,8 @@ source_organization ) VALUES ( $1, - (SELECT id FROM waterway.bottlenecks WHERE bottleneck_id = $2), + (SELECT id FROM waterway.bottlenecks + WHERE NOT erased AND bottleneck_id = $2), $3, $4, $5,
--- a/pkg/imports/sr.go Fri Jun 14 11:59:14 2019 +0200 +++ b/pkg/imports/sr.go Fri Jun 14 12:02:58 2019 +0200 @@ -114,11 +114,13 @@ insertHullSQL = ` INSERT INTO waterway.sounding_results ( bottleneck_id, + bottleneck_validity, date_info, depth_reference, area ) SELECT - (SELECT id from waterway.bottlenecks where objnam = $1), + bottleneck_id, + validity, $2::date, $3, (SELECT @@ -127,6 +129,8 @@ ELSE ST_MakeValid(ST_Transform(ST_GeomFromWKB($5, $6::integer), 4326))::geography END) +FROM waterway.bottlenecks +WHERE objnam = $1 AND validity @> CAST($2 AS timestamptz) RETURNING id, ST_X(ST_Centroid(area::geometry)), @@ -188,7 +192,9 @@ JOIN waterway.bottlenecks bns ON grwl.location = bns.gauge_location AND grwl.validity = bns.gauge_validity -WHERE bns.objnam = $1 AND grwl.depth_reference like 'LDC%' +WHERE bns.objnam = $1 + AND bns.validity @> CAST($2 AS timestamptz) + AND grwl.depth_reference like 'LDC%' ` reprojectPointsSingleBeamSQL = ` @@ -242,7 +248,11 @@ feedback.Info("Found ZPG as reference system -> translating Z values to LDC") var ldc float64 var depthReference string - err := conn.QueryRowContext(ctx, selectGaugeLDCSQL, m.Bottleneck).Scan( + err := conn.QueryRowContext(ctx, + selectGaugeLDCSQL, + m.Bottleneck, + m.Date.Time, + ).Scan( &ldc, &depthReference, ) @@ -643,7 +653,11 @@ ) xyz, boundary = nil, nil // not need from now on. feedback.Info("Calculating hull took %s.", time.Since(start)) - if err != nil { + switch { + case err == sql.ErrNoRows: + return nil, fmt.Errorf( + "No bottleneck matching given name and time available") + case err != nil: return nil, err } feedback.Info("Best suited UTM EPSG: %d", epsg)
--- a/pkg/imports/wg.go Fri Jun 14 11:59:14 2019 +0200 +++ b/pkg/imports/wg.go Fri Jun 14 12:02:58 2019 +0200 @@ -79,8 +79,8 @@ erased = true WHERE isrs_astext(location) = $1 AND NOT erased - -- Don't touch old entry if validity did not change: will be updated - AND validity <> $2 + -- Don't touch old entry if new validity contains old: will be updated + AND NOT validity <@ $2 RETURNING 1 ) -- Decide whether a new version will be INSERTed @@ -124,6 +124,14 @@ AND measure_date <@ CAST($2 AS tstzrange) ` + moveBNSQL = ` +UPDATE waterway.bottlenecks +-- Associate bottlenecks to matching gauge version +SET gauge_validity = $2 +WHERE isrs_astext(gauge_location) = $1 + AND lower(validity) <@ CAST($2 AS tstzrange) +` + fixValiditySQL = ` UPDATE waterway.gauges SET -- Set enddate of old entry to new startdate in case of overlap: @@ -143,7 +151,8 @@ geodref = $12, date_info = $13, source_organization = $14, - lastupdate = $15 + lastupdate = $15, + validity = $16 WHERE location = ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int) AND NOT erased AND $15 > lastupdate @@ -199,7 +208,8 @@ return nil, err } - var eraseGaugeStmt, insertStmt, moveGMStmt, fixValidityStmt, updateStmt, + var eraseGaugeStmt, insertStmt, moveGMStmt, moveBNStmt, + fixValidityStmt, updateStmt, deleteReferenceWaterLevelsStmt, isNtSDepthRefStmt, insertWaterLevelStmt *sql.Stmt for _, x := range []struct { @@ -209,6 +219,7 @@ {eraseGaugeSQL, &eraseGaugeStmt}, {insertGaugeSQL, &insertStmt}, {moveGMSQL, &moveGMStmt}, + {moveBNSQL, &moveBNStmt}, {fixValiditySQL, &fixValidityStmt}, {updateGaugeSQL, &updateStmt}, {deleteReferenceWaterLevelsSQL, &deleteReferenceWaterLevelsStmt}, @@ -365,33 +376,6 @@ unchanged++ continue } - // Move gauge measurements to new matching gauge version, - // if applicable - if _, err = tx.StmtContext(ctx, moveGMStmt).ExecContext(ctx, - code.String(), - &validity, - ); err != nil { - feedback.Warn(handleError(err).Error()) - if err2 := tx.Rollback(); err2 != nil { - return nil, err2 - } - unchanged++ - continue - } - // Set end of validity of old version to start of new version - // in case of overlap - if _, err = tx.StmtContext(ctx, fixValidityStmt).ExecContext( - ctx, - code.String(), - &validity, - ); err != nil { - feedback.Warn(handleError(err).Error()) - if err2 := tx.Rollback(); err2 != nil { - return nil, err2 - } - unchanged++ - continue - } feedback.Info("insert new version") case !isNew: // try to update @@ -411,6 +395,7 @@ &dateInfo, source, time.Time(*dr.Lastupdate), + &validity, ).Scan(&dummy) switch { case err2 == sql.ErrNoRows: @@ -461,6 +446,46 @@ } } + // Move gauge measurements and bottlenecks to new matching + // gauge version, if applicable + if _, err = tx.StmtContext(ctx, moveGMStmt).ExecContext(ctx, + code.String(), + &validity, + ); err != nil { + feedback.Warn(handleError(err).Error()) + if err2 := tx.Rollback(); err2 != nil { + return nil, err2 + } + unchanged++ + continue + } + if _, err = tx.StmtContext(ctx, moveBNStmt).ExecContext(ctx, + code.String(), + &validity, + ); err != nil { + feedback.Warn(handleError(err).Error()) + if err2 := tx.Rollback(); err2 != nil { + return nil, err2 + } + unchanged++ + continue + } + + // Set end of validity of old version to start of new version + // in case of overlap + if _, err = tx.StmtContext(ctx, fixValidityStmt).ExecContext( + ctx, + code.String(), + &validity, + ); err != nil { + feedback.Warn(handleError(err).Error()) + if err2 := tx.Rollback(); err2 != nil { + return nil, err2 + } + unchanged++ + continue + } + // "Upsert" reference water levels for _, wl := range []struct { level **erdms.RisreflevelcodeType
--- a/pkg/models/sr.go Fri Jun 14 11:59:14 2019 +0200 +++ b/pkg/models/sr.go Fri Jun 14 12:02:58 2019 +0200 @@ -41,7 +41,8 @@ FROM waterway.bottlenecks bn JOIN waterway.gauges g ON bn.gauge_location = g.location AND bn.gauge_validity = g.validity - JOIN waterway.gauges_reference_water_levels rl USING (location, validity) + JOIN waterway.gauges_reference_water_levels rl + ON g.location = rl.location AND g.validity = rl.validity WHERE bn.objnam = $1 AND rl.depth_reference = $2)` @@ -50,7 +51,7 @@ checkBottleneckDateUniqueSQL = ` SELECT true FROM waterway.sounding_results sr JOIN - waterway.bottlenecks bn ON sr.bottleneck_id = bn.id + waterway.bottlenecks bn ON sr.bottleneck_id = bn.bottleneck_id WHERE bn.objnam = $1 AND sr.date_info = $2` )
--- a/schema/auth.sql Fri Jun 14 11:59:14 2019 +0200 +++ b/schema/auth.sql Fri Jun 14 12:02:58 2019 +0200 @@ -25,10 +25,11 @@ -- -- Privileges for waterway_user -- -GRANT USAGE ON SCHEMA public, users, waterway, systemconf, caching TO waterway_user; +GRANT USAGE ON SCHEMA public, users, waterway, systemconf, sys_admin, caching TO waterway_user; GRANT SELECT ON ALL TABLES IN SCHEMA public, users, waterway TO waterway_user; GRANT SELECT, UPDATE, DELETE, INSERT ON ALL TABLES IN SCHEMA caching TO waterway_user; GRANT SELECT ON systemconf.feature_colours TO waterway_user; +GRANT SELECT ON sys_admin.system_config TO waterway_user; GRANT UPDATE (pw, map_extent, email_address) ON users.list_users TO waterway_user; @@ -65,7 +66,7 @@ ON users.list_users, users.responsibility_areas TO sys_admin; GRANT USAGE ON SCHEMA sys_admin TO sys_admin; GRANT SELECT ON ALL TABLES IN SCHEMA sys_admin TO sys_admin; -GRANT UPDATE ON sys_admin.system_config TO sys_admin; +GRANT INSERT, UPDATE ON sys_admin.system_config TO sys_admin; GRANT UPDATE ON systemconf.feature_colours TO sys_admin; GRANT UPDATE ON sys_admin.published_services TO sys_admin; GRANT INSERT, DELETE ON sys_admin.password_reset_requests TO sys_admin;
--- a/schema/auth_tests.sql Fri Jun 14 11:59:14 2019 +0200 +++ b/schema/auth_tests.sql Fri Jun 14 12:02:58 2019 +0200 @@ -75,16 +75,16 @@ PREPARE bn_insert (varchar, geometry(MULTIPOLYGON, 4326)) AS INSERT INTO waterway.bottlenecks ( - gauge_location, gauge_validity, + gauge_location, gauge_validity, validity, bottleneck_id, stretch, area, rb, lb, responsible_country, - revisiting_time, limiting, source_organization) + revisiting_time, limiting, date_info, source_organization) SELECT - location, validity, + location, validity, validity, $1, isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, ('AT', 'XXX', '00001', '00000', 2)::isrs), $2, 'AT', 'AT', 'AT', - 1, 'depth', 'testorganization' + 1, 'depth', current_timestamp, 'testorganization' FROM waterway.gauges WHERE location = ('AT', 'XXX', '00001', 'G0001', 1)::isrs; SELECT lives_ok($$
--- a/schema/default_sysconfig.sql Fri Jun 14 11:59:14 2019 +0200 +++ b/schema/default_sysconfig.sql Fri Jun 14 12:02:58 2019 +0200 @@ -23,4 +23,19 @@ INSERT INTO systemconf.feature_colours VALUES ('Bottlenecks', 'stroke', 250, 40, 255, 1); INSERT INTO systemconf.feature_colours VALUES ('Bottlenecks', 'fill', 255, 37, 196, 0.14); +-- +-- Settings +-- +INSERT INTO sys_admin.system_config VALUES ('ecdis_wms_url', 'https://service.d4d-portal.info/wms/'); +INSERT INTO sys_admin.system_config VALUES ('ecdis_wms_params', '{"LAYERS": "d4d", "VERSION": "1.1.1", "TILED": true}'); +INSERT INTO sys_admin.system_config VALUES ('bn_revtime_multiplier', 1.5); +INSERT INTO sys_admin.system_config VALUES ('gm_min_values_14d', 1224); +INSERT INTO sys_admin.system_config VALUES ('gm_latest_hours', 24); +INSERT INTO sys_admin.system_config VALUES ('gm_forecast_offset_24h', 15); +INSERT INTO sys_admin.system_config VALUES ('gm_forecast_offset_72h', 15); +INSERT INTO sys_admin.system_config VALUES ('gm_forecast_vs_reality_nsc_24h', -12.5); +INSERT INTO sys_admin.system_config VALUES ('gm_forecast_vs_reality_nsc_72h', -12.5); +INSERT INTO sys_admin.system_config VALUES ('morphology_classbreaks', '1,1.5,1.7,1.9,2.1,2.3,2.5,2.7,2.9,3.1,3.3,3.5,4.0,4.5,5,5.5,6,6.5,7'); +INSERT INTO sys_admin.system_config VALUES ('morphology_classbreaks_compare', '-2,-1.9,-1.8,-1.7,-1.6,-1.5,-1.4,-1.3,-1.2,-1.1,-1,-0.9,-0.8,-0.7,-0.6,-0.5,-0.4,-0.3,-0.2,-0.1,0,0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,1,1.1,1.2,1.3,1.4,1.5,1.6,1.7,1.8,1.9,2'); + COMMIT;
--- a/schema/gemma.sql Fri Jun 14 11:59:14 2019 +0200 +++ b/schema/gemma.sql Fri Jun 14 12:02:58 2019 +0200 @@ -280,7 +280,7 @@ geom geography(POINT, 4326) NOT NULL, applicability_from_km int8, applicability_to_km int8, - validity tstzrange NOT NULL, + validity tstzrange NOT NULL CHECK (NOT isempty(validity)), zero_point double precision NOT NULL, geodref varchar, date_info timestamp with time zone NOT NULL, @@ -288,7 +288,6 @@ lastupdate timestamp with time zone NOT NULL, -- entry removed from external data source (RIS-Index)/historicised: erased boolean NOT NULL DEFAULT false, - CHECK (erased OR NOT isempty(validity)), PRIMARY KEY (location, validity), EXCLUDE USING GiST (isrs_astext(location) WITH =, validity WITH &&) DEFERRABLE INITIALLY DEFERRED @@ -488,9 +487,14 @@ -- CREATE TABLE bottlenecks ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, - bottleneck_id varchar UNIQUE NOT NULL, + bottleneck_id varchar NOT NULL, + validity tstzrange NOT NULL CHECK (NOT isempty(validity)), + UNIQUE (bottleneck_id, validity), + EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&) + DEFERRABLE INITIALLY DEFERRED, gauge_location isrs NOT NULL, gauge_validity tstzrange NOT NULL, + CHECK(lower(validity) <@ gauge_validity), CONSTRAINT gauge_key FOREIGN KEY (gauge_location, gauge_validity) REFERENCES gauges ON UPDATE CASCADE, @@ -512,13 +516,15 @@ -- different model approach? -- depth_reference char(3) NOT NULL REFERENCES depth_references, -- XXX: Also an attribut of sounding result? - date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, + date_info timestamp with time zone NOT NULL, source_organization varchar NOT NULL, - -- additional_data xml -- Currently not relevant for GEMMA + erased boolean NOT NULL DEFAULT false, staging_done boolean NOT NULL DEFAULT false ) - CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks - FOR EACH ROW EXECUTE PROCEDURE update_date_info() + -- Allow only one non-erased entry per bottleneck + CREATE UNIQUE INDEX bottlenecks_erased_unique_constraint + ON bottlenecks (bottleneck_id) + WHERE NOT erased CREATE TABLE bottlenecks_riverbed_materials ( bottleneck_id int NOT NULL REFERENCES bottlenecks(id) @@ -530,8 +536,15 @@ CREATE TABLE sounding_results ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, - bottleneck_id int NOT NULL REFERENCES bottlenecks(id), + bottleneck_id varchar NOT NULL, + bottleneck_validity tstzrange NOT NULL, + CONSTRAINT bottleneck_key + FOREIGN KEY (bottleneck_id, bottleneck_validity) + REFERENCES bottlenecks (bottleneck_id, validity) + ON UPDATE CASCADE, date_info date NOT NULL, + CHECK (tstzrange(date_info::timestamptz, + date_info::timestamptz + '1 d'::interval) && bottleneck_validity), UNIQUE (bottleneck_id, date_info), area geography(POLYGON, 4326) NOT NULL CHECK(ST_IsValid(CAST(area AS geometry))),
--- a/schema/geoserver_views.sql Fri Jun 14 11:59:14 2019 +0200 +++ b/schema/geoserver_views.sql Fri Jun 14 12:02:58 2019 +0200 @@ -135,7 +135,8 @@ LEFT JOIN fairway_availability_latest fal ON b.id = fal.bottleneck_id LEFT JOIN sounding_result_latest srl - ON b.id = srl.bottleneck_id; + ON b.bottleneck_id = srl.bottleneck_id + WHERE NOT b.erased; CREATE OR REPLACE VIEW waterway.stretches_geoserver AS SELECT @@ -199,7 +200,8 @@ FROM waterway.bottlenecks bn LEFT JOIN ( SELECT bottleneck_id, max(date_info) AS current FROM waterway.sounding_results - GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id + GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.bottleneck_id + WHERE NOT bn.erased ORDER BY objnam; CREATE OR REPLACE VIEW waterway.sounding_differences AS @@ -218,4 +220,5 @@ JOIN waterway.sounding_results srs ON sd.subtrahend = srs.id JOIN waterway.bottlenecks bn - ON srm.bottleneck_id = bn.id; + ON srm.bottleneck_id = bn.bottleneck_id + AND srm.bottleneck_validity = bn.validity;
--- a/schema/tap_tests_data.sql Fri Jun 14 11:59:14 2019 +0200 +++ b/schema/tap_tests_data.sql Fri Jun 14 12:02:58 2019 +0200 @@ -57,7 +57,7 @@ current_timestamp, 'testorganization', current_timestamp) - RETURNING location, validity), + RETURNING location, validity, validity), bns AS ( VALUES ( 'testbottleneck1', @@ -65,19 +65,19 @@ ('AT', 'XXX', '00001', '00000', 2)::isrs), ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326), 'AT', 'AT', 'AT', - 1, 'depth', 'testorganization', false + 1, 'depth', current_timestamp, 'testorganization', false ), ( 'testbottleneck2', isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, ('AT', 'XXX', '00001', '00000', 2)::isrs), ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326), 'AT', 'AT', 'AT', - 1, 'depth', 'testorganization', true + 1, 'depth', current_timestamp, 'testorganization', true )) INSERT INTO waterway.bottlenecks ( - gauge_location, gauge_validity, + gauge_location, gauge_validity, validity, bottleneck_id, stretch, area, rb, lb, responsible_country, - revisiting_time, limiting, source_organization, staging_done) + revisiting_time, limiting, date_info, source_organization, staging_done) SELECT * FROM gs, bns; INSERT INTO waterway.distance_marks_virtual VALUES (