Mercurial > gemma
changeset 5054:5e6ec587014e time-sliding
merged default into time-sliding branch
author | Fadi Abbud <fadi.abbud@intevation.de> |
---|---|
date | Thu, 05 Mar 2020 12:15:37 +0100 |
parents | 8b39081fa3aa (current diff) 61eb65394a13 (diff) |
children | 8741fae4babd |
files | |
diffstat | 53 files changed, 2674 insertions(+), 232 deletions(-) [+] |
line wrap: on
line diff
--- a/client/src/assets/application.scss Mon Mar 02 16:17:50 2020 +0100 +++ b/client/src/assets/application.scss Thu Mar 05 12:15:37 2020 +0100 @@ -124,7 +124,7 @@ .expanded { max-height: 999px; max-width: 999px; - margin: 0 0.25rem 0.5rem 0.25rem; + margin: 0 0.05rem 0.5rem 0.25rem; } .box-body {
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/client/src/assets/fm_legend.svg Thu Mar 05 12:15:37 2020 +0100 @@ -0,0 +1,30 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<svg + xmlns:dc="http://purl.org/dc/elements/1.1/" + xmlns:cc="http://creativecommons.org/ns#" + xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" + xmlns:svg="http://www.w3.org/2000/svg" + xmlns="http://www.w3.org/2000/svg" + id="svg2" + height="20" + width="20" + version="1.0"> + <metadata + id="metadata7"> + <rdf:RDF> + <cc:Work + rdf:about=""> + <dc:format>image/svg+xml</dc:format> + <dc:type + rdf:resource="http://purl.org/dc/dcmitype/StillImage" /> + <dc:title></dc:title> + </cc:Work> + </rdf:RDF> + </metadata> + <defs + id="defs4" /> + <path + style="fill:#000407;fill-opacity:1;fill-rule:evenodd;stroke:none;stroke-width:0.16059898" + id="path4582" + d="M 12.204078,6.7050935 11.180261,9.6862128 C 10.869802,9.6104528 10.55185,9.5608801 10.231721,9.5406654 10.102359,9.532456 9.970398,9.5295187 9.8402602,9.5306484 8.9293256,9.5384812 8.0084945,9.7753488 7.1703073,10.268395 4.8156856,11.653464 3.9033276,14.574413 4.9520295,17.023594 H 3.6572037 v 0.908388 h 3.4880084 c 0.2067413,0.888581 1.0010572,1.550779 1.9522782,1.550779 0.9515447,0 1.7458677,-0.661753 1.9522857,-1.550779 l 5.184334,0.0053 V 17.0289 L 14.853959,17.0236 C 15.534737,15.45425 15.428271,13.68935 14.537784,12.175522 13.897895,11.087715 12.93409,10.306851 11.837713,9.8869816 L 12.851491,6.9209104 Z M 9.0974903,16.270769 c 0.6648861,0 1.2044927,0.539614 1.2044927,1.204492 0,0.664879 -0.5396066,1.204492 -1.2044927,1.204492 -0.6648786,0 -1.2044923,-0.539613 -1.2044923,-1.204492 0,-0.664878 0.5396137,-1.204492 1.2044923,-1.204492 z M 9.9988524,5.3450219 14.682323,0.66757693 15.66599,7.2380794 Z" /> +</svg>
--- a/client/src/components/Contextbox.vue Mon Mar 02 16:17:50 2020 +0100 +++ b/client/src/components/Contextbox.vue Thu Mar 05 12:15:37 2020 +0100 @@ -80,7 +80,7 @@ background: #fff; } .contextbox > div:last-child { - width: 795px; + width: 860px; } .contextboxcollapsed { @@ -89,7 +89,7 @@ } .contextboxextended { - max-width: 795px; + max-width: 860px; } .close-contextbox {
--- a/client/src/components/Pdftool.vue Mon Mar 02 16:17:50 2020 +0100 +++ b/client/src/components/Pdftool.vue Thu Mar 05 12:15:37 2020 +0100 @@ -5,7 +5,7 @@ { expanded: showPdfTool } ]" > - <div style="width: 18rem"> + <div style="width: 17rem"> <UIBoxHeader icon="file-pdf" :title="generatePdfLable"
--- a/client/src/components/Search.vue Mon Mar 02 16:17:50 2020 +0100 +++ b/client/src/components/Search.vue Thu Mar 05 12:15:37 2020 +0100 @@ -85,7 +85,7 @@ <style lang="scss" scoped> .searchcontainer { opacity: 0.96; - width: 795px; + width: 860px; } .searchcontainer .searchbar { @@ -94,7 +94,7 @@ } .searchgroup { - width: 763px; + width: 827px; overflow: hidden; }
--- a/client/src/components/fairway/AvailableFairwayDepthDialogue.vue Mon Mar 02 16:17:50 2020 +0100 +++ b/client/src/components/fairway/AvailableFairwayDepthDialogue.vue Thu Mar 05 12:15:37 2020 +0100 @@ -5,7 +5,7 @@ { expanded: showFairwayDepth } ]" > - <div style="width: 18rem"> + <div style="width: 17rem"> <UIBoxHeader icon="chart-line" :title="label" :closeCallback="close" /> <div class="box-body"> <UISpinnerOverlay v-if="loading" /> @@ -59,7 +59,7 @@ <div class="d-flex"> <select v-if="type === $options.BOTTLENECK" - class="form-control font-weight-bold" + class="w-90 form-control font-weight-bold" v-model="selectedEntry" > <option :value="null">{{ placeholder }}</option>
--- a/client/src/components/fairway/BottleneckDialogue.vue Mon Mar 02 16:17:50 2020 +0100 +++ b/client/src/components/fairway/BottleneckDialogue.vue Thu Mar 05 12:15:37 2020 +0100 @@ -5,7 +5,7 @@ { expanded: showProfiles } ]" > - <div style="width: 18rem"> + <div style="width: 17rem"> <UIBoxHeader icon="chart-area" :title="profilesLable" @@ -16,7 +16,7 @@ <div class="d-flex flex-row"> <select v-model="selectedBottleneck" - class="form-control font-weight-bold" + class="w-90 form-control font-weight-bold" > <option :value="null"> <translate>Select Bottleneck</translate>
--- a/client/src/components/gauge/Gauges.vue Mon Mar 02 16:17:50 2020 +0100 +++ b/client/src/components/gauge/Gauges.vue Thu Mar 05 12:15:37 2020 +0100 @@ -5,7 +5,7 @@ { expanded: showGauges } ]" > - <div style="width: 18rem"> + <div style="width: 17rem"> <UIBoxHeader icon="ruler-vertical" :title="gaugesLabel" @@ -16,7 +16,7 @@ <div class="d-flex"> <select v-model="selectedGaugeISRS" - class="form-control font-weight-bold" + class="w-90 form-control font-weight-bold" > <option :value="null"> <translate>Select Gauge</translate>
--- a/client/src/components/importconfiguration/Import.vue Mon Mar 02 16:17:50 2020 +0100 +++ b/client/src/components/importconfiguration/Import.vue Thu Mar 05 12:15:37 2020 +0100 @@ -6,8 +6,8 @@ <UITableHeader :columns="[ { id: 'id', title: `${idLabel}`, width: '60px;' }, - { id: 'kind', title: `${typeLabel}`, width: '60px;' }, - { id: 'user', title: `${ownerLabel}`, width: '250px' }, + { id: 'kind', title: `${typeLabel}`, width: '125px;' }, + { id: 'user', title: `${ownerLabel}`, width: '220px' }, { id: 'country', title: `${countryLabel}`, width: '80px' }, { id: 'config.cron', title: `${scheduleLabel}`, width: '100px' }, { id: 'config.send-email', title: `${emailLabel}`, width: '70px' } @@ -21,10 +21,10 @@ <div style="width:60px" class="table-cell py-1"> {{ schedule.id }} </div> - <div style="width:60px" class="table-cell py-1"> - {{ schedule.kind.toUpperCase() }} + <div style="width:125px" class="table-cell py-1"> + {{ schedule.kind.replace("fm_", "").toUpperCase() }} </div> - <div style="width:250px;" class="table-cell py-1"> + <div style="width:220px;" class="table-cell py-1"> {{ schedule.user }} </div> <div style="width:80px;" class="table-cell py-1">
--- a/client/src/components/importconfiguration/ImportDetails.vue Mon Mar 02 16:17:50 2020 +0100 +++ b/client/src/components/importconfiguration/ImportDetails.vue Thu Mar 05 12:15:37 2020 +0100 @@ -10,6 +10,17 @@ class="custom-select custom-select-sm" id="importtype" > + <optgroup :label="onetimeLabel"> + <option :value="$options.IMPORTTYPES.SOUNDINGRESULTS"> + <translate>Soundingresults</translate> + </option> + <option :value="$options.IMPORTTYPES.APPROVEDGAUGEMEASUREMENTS"> + <translate>Approved Gaugemeasurements</translate> + </option> + <option :value="$options.IMPORTTYPES.WATERWAYPROFILES"> + <translate>Waterway Profiles</translate> + </option> + </optgroup> <optgroup :label="regularLabel"> <option :value="$options.IMPORTTYPES.WATERWAYAREA"> <translate>Waterway area</translate> @@ -38,16 +49,8 @@ <option :value="$options.IMPORTTYPES.GAUGEMEASUREMENT"> <translate>Gauge measurement</translate> </option> - </optgroup> - <optgroup :label="onetimeLabel"> - <option :value="$options.IMPORTTYPES.SOUNDINGRESULTS"> - <translate>Soundingresults</translate> - </option> - <option :value="$options.IMPORTTYPES.APPROVEDGAUGEMEASUREMENTS"> - <translate>Approved Gaugemeasurements</translate> - </option> - <option :value="$options.IMPORTTYPES.WATERWAYPROFILES"> - <translate>Waterway Profiles</translate> + <option :value="$options.IMPORTTYPES.FAIRWAYMARKS"> + <translate>Fairwaymarks</translate> </option> </optgroup> </select>
--- a/client/src/components/importconfiguration/ScheduledImports.vue Mon Mar 02 16:17:50 2020 +0100 +++ b/client/src/components/importconfiguration/ScheduledImports.vue Thu Mar 05 12:15:37 2020 +0100 @@ -104,9 +104,12 @@ @urlChanged="setUrl" @featureTypeChanged="setFeatureType" @sortByChanged="setSortBy" + @selectedMarkChanged="setSelectedMark" + :isUpdate="!this.id" :url="url" :featureType="featureType" :sortBy="sortBy" + :mark="selectedMark" /> <Gaugemeasurement v-if="import_ == $options.IMPORTTYPES.GAUGEMEASUREMENT && !directImport" @@ -712,6 +715,9 @@ setSortBy(value) { this.sortBy = value; }, + setSelectedMark(value) { + this.selectedMark = value; + }, setTolerance(value) { this.tolerance = value; }, @@ -789,6 +795,7 @@ this.directImport = false; this.trys = this.currentSchedule.trys; this.waitRetry = this.currentSchedule.waitRetry; + this.selectedMark = this.currentSchedule.selectedMark; this.retry = this.currentSchedule.trys === null || this.currentSchedule.trys === undefined || @@ -921,9 +928,13 @@ if (this.trys) data["trys"] = Number(this.trys); data["send-email"] = this.eMailNotification; this.triggerActive = false; + const type = + this.import_ === "fairwaymarks" + ? `fm_${this.selectedMark.toLowerCase()}` + : IMPORTTYPEKIND[this.import_]; this.$store .dispatch("importschedule/triggerImport", { - type: IMPORTTYPEKIND[this.import_], + type: type, data }) .then(response => { @@ -957,8 +968,10 @@ } let data = {}; let config = {}; - data["kind"] = IMPORTTYPEKIND[this.import_]; - + data["kind"] = + this.import_ === "fairwaymarks" + ? `fm_${this.selectedMark.toLowerCase()}` + : IMPORTTYPEKIND[this.import_]; if (this.isURLRequired) { if (!this.url) return; config["url"] = this.url;
--- a/client/src/components/importconfiguration/types/Fairwaymarks.vue Mon Mar 02 16:17:50 2020 +0100 +++ b/client/src/components/importconfiguration/types/Fairwaymarks.vue Thu Mar 05 12:15:37 2020 +0100 @@ -15,10 +15,29 @@ </div> </div> </div> - <div v-if="!url" class="d-flex px-2"> - <small - ><translate class="text-danger">Please enter a URL</translate></small - > + <div class="d-flex px-2"> + <div class="flex-column w-100"> + <div class="flex-row text-left"> + <small class="text-muted"> + <translate>Type of mark</translate> + </small> + </div> + <div class="w-50 mt-2"> + <template v-if="isUpdate"> + <select v-model="selectedMark" class="form-control form-control-sm"> + <option + v-for="(option, value) in $options.FAIRWAYMARKS" + :key="value" + :value="value" + >{{ option }}</option + > + </select> + </template> + <template v-else=""> + <span class="pl-1">{{ selectedMark }}</span> + </template> + </div> + </div> </div> <div class="d-flex px-2"> <div class="flex-column mt-2 mr-3 w-50"> @@ -80,8 +99,19 @@ * Thomas Junk <thomas.junk@intevation.de> */ export default { - name: "waterwayarea", - props: ["url", "featureType", "sortBy"], + name: "fairwaymarks", + props: ["url", "featureType", "sortBy", "mark", "isUpdate"], + computed: { + selectedMark: { + get() { + return this.mark; + }, + set(value) { + this.selected = value; + this.$emit("selectedMarkChanged", value); + } + } + }, methods: { urlChanged(e) { this.$emit("urlChanged", e.target.value); @@ -92,6 +122,23 @@ sortByChanged(e) { this.$emit("sortByChanged", e.target.value); } + }, + FAIRWAYMARKS: { + BCNISD: "Beacon, isolated danger (MARITIME/Hydro feature)", + BCNLAT_hydro: "Beacon, lateral (MARITIME/Hydro feature)", + bcnlat_ienc: "Beacon, lateral (IENC feature)", + BOYCAR: "Buoy, cardinal (MARITIME/Hydro feature)", + BOYISD: "Buoy, isolated danger (MARITIME/Hydro feature)", + BOYLAT_hydro: "Buoy, lateral (MARITIME/Hydro feature)", + BOYLAT_ienc: "Buoy, lateral (IENC feature)", + BOYSAW: "Buoy, safe water (MARITIME/Hydro feature)", + BOYSPP: "Buoy, special purpose/general (MARITIME/Hydro feature)", + DAYMAR_hydro: "Daymark (MARITIME/Hydro feature)", + DAYMAR_ienc: "Daymark (IENC feature)", + LIGHTS: "Light (MARITIME/Hydro feature)", + RTPBCN: "Radar transponder beacon (MARITIME/Hydro feature)", + TOPMAR: "Topmark (MARITIME/Hydro feature)", + notmrk: "Notice mark (IENC feature)" } }; </script>
--- a/client/src/components/importoverview/ImportOverview.vue Mon Mar 02 16:17:50 2020 +0100 +++ b/client/src/components/importoverview/ImportOverview.vue Thu Mar 05 12:15:37 2020 +0100 @@ -78,10 +78,14 @@ <UITableHeader :columns="[ { id: 'id', title: `${idLabel}`, width: '70px' }, - { id: 'kind', title: `${kindLabel}`, width: '50px' }, - { id: 'enqueued', title: `${enqueuedLabel}`, width: '138px' }, + { + id: 'kind', + title: `${kindLabel.replace('fm_').toUpperCase()}`, + width: '125px' + }, + { id: 'enqueued', title: `${enqueuedLabel}`, width: '135px' }, { id: 'user', title: `${ownerLabel}`, width: '80px' }, - { id: 'country', title: `${countryLabel}`, width: '55px' }, + { id: 'country', title: `${countryLabel}`, width: '50px' }, { id: 'signer', title: `${signerLabel}`, width: '80px' }, { id: 'state', title: `${statusLabel}`, width: '72px' }, { id: 'changed', title: `${changedLabel}`, width: '138px' },
--- a/client/src/components/importoverview/LogEntry.vue Mon Mar 02 16:17:50 2020 +0100 +++ b/client/src/components/importoverview/LogEntry.vue Thu Mar 05 12:15:37 2020 +0100 @@ -9,16 +9,16 @@ /> {{ entry.id }} </div> - <div style="width: 50px;" class="table-cell center"> - {{ entry.kind.toUpperCase() }} + <div style="width: 125px;" class="table-cell center"> + {{ entry.kind.replace("fm_", "").toUpperCase() }} </div> - <div style="width: 138px;" class="table-cell center"> + <div style="width: 135px;" class="table-cell center"> {{ entry.enqueued | dateTime }} </div> <div style="width: 80px;" class="table-cell truncate"> {{ entry.user }} </div> - <div style="width: 55px;" class="table-cell center"> + <div style="width: 50px;" class="table-cell center"> {{ userCountries[entry.user] }} </div> <div style="width: 80px;" class="table-cell truncate">
--- a/client/src/components/layers/Layers.vue Mon Mar 02 16:17:50 2020 +0100 +++ b/client/src/components/layers/Layers.vue Thu Mar 05 12:15:37 2020 +0100 @@ -5,7 +5,7 @@ { expanded: showLayers } ]" > - <div class="position-relative" style="width: 20rem; min-height: 350px;"> + <div class="position-relative" style="width: 17rem; min-height: 350px;"> <UIBoxHeader icon="layer-group" :title="label" @@ -37,6 +37,7 @@ <Layerselect layerId="DISTANCEMARKS" /> <Layerselect layerId="DISTANCEMARKSAXIS" /> <Layerselect layerId="GAUGES" /> + <Layerselect layerId="FAIRWAYMARKS" /> <Layerselect v-if="reviewActive" layerId="FDREVIEWLAYER" /> </div> <UISpinnerOverlay v-else style="top: 34px;" />
--- a/client/src/components/layers/LegendElement.vue Mon Mar 02 16:17:50 2020 +0100 +++ b/client/src/components/layers/LegendElement.vue Thu Mar 05 12:15:37 2020 +0100 @@ -1,11 +1,21 @@ <template> <div :id="id" class="legendelement"> - <img - v-if="!isVectorLayer" - style="margin: 0 auto;display: flex;" - :src="imgSrc" - :key="id" - /> + <div v-if="layer.get('id') === 'FAIRWAYMARKS'"> + <img + v-if="!isVectorLayer" + style="margin: 0 auto;display: flex;" + src="@/assets/fm_legend.svg" + :key="id" + /> + </div> + <div v-else=""> + <img + v-if="!isVectorLayer" + style="margin: 0 auto;display: flex;" + :src="imgSrc" + :key="id" + /> + </div> </div> </template>
--- a/client/src/components/map/layers.js Mon Mar 02 16:17:50 2020 +0100 +++ b/client/src/components/map/layers.js Thu Mar 05 12:15:37 2020 +0100 @@ -1,20 +1,21 @@ -import TileWMS from "ol/source/TileWMS"; +import { GeoJSON, WFS } from "ol/format"; +import { Icon, Stroke, Style } from "ol/style"; import { + Image as ImageLayer, Tile as TileLayer, - Vector as VectorLayer, - Image as ImageLayer + Vector as VectorLayer } from "ol/layer"; -import { Icon, Stroke, Style } from "ol/style"; -import VectorSource from "ol/source/Vector"; +import { and as andFilter, equalTo } from "ol/format/filter"; + +import { HTTP } from "@/lib/http"; import { ImageWMS as ImageSource } from "ol/source"; +import OSM from "ol/source/OSM"; import Point from "ol/geom/Point"; +import TileWMS from "ol/source/TileWMS"; +import VectorSource from "ol/source/Vector"; import { bbox as bboxStrategy } from "ol/loadingstrategy"; -import { WFS, GeoJSON } from "ol/format"; -import OSM from "ol/source/OSM"; -import { equalTo, and as andFilter } from "ol/format/filter"; -import { HTTP } from "@/lib/http"; +import store from "@/store/index"; import { styleFactory } from "./styles"; -import store from "@/store/index"; export const buildVectorLoader = ( featureRequestOptions, @@ -260,7 +261,11 @@ minResolution: 0, source: new ImageSource({ url: window.location.origin + "/api/internal/wms", - params: { LAYERS: "waterway_area", VERSION: "1.1.1", TILED: true }, + params: { + LAYERS: "waterway_area", + VERSION: "1.1.1", + TILED: true + }, imageLoadFunction: function(tile, src) { HTTP.get(src, { headers: { @@ -476,7 +481,11 @@ label: "Waterway Axis", source: new ImageSource({ url: window.location.origin + "/api/internal/wms", - params: { LAYERS: "waterway_axis", VERSION: "1.1.1", TILED: true }, + params: { + LAYERS: "waterway_axis", + VERSION: "1.1.1", + TILED: true + }, imageLoadFunction: function(tile, src) { HTTP.get(src, { headers: { @@ -668,6 +677,35 @@ source }); })(), + new TileLayer({ + id: "FAIRWAYMARKS", + label: "Fairway marks", + visible: true, + source: new TileWMS({ + preload: 0, + projection: "EPSG:3857", + url: window.location.origin + "/api/internal/wms", + params: { + LAYERS: "fairway_marks", + VERSION: "1.1.1", + TILED: true + }, + tileLoadFunction: function(tile, src) { + HTTP.get(src, { + headers: { + "X-Gemma-Auth": localStorage.getItem("token") + }, + responseType: "blob" + }) + .then(response => { + tile.getImage().src = URL.createObjectURL(response.data); + }) + .catch(() => { + store.dispatch("application/reportBackendError"); + }); + } // TODO tile.setState(TileState.ERROR); + }) + }), DRAWLAYER, CUTLAYER, FDREVIEWLAYER
--- a/client/src/store/importschedule.js Mon Mar 02 16:17:50 2020 +0100 +++ b/client/src/store/importschedule.js Thu Mar 05 12:15:37 2020 +0100 @@ -60,6 +60,24 @@ distancemarksashore: "dma" }; +const FAIRWAYMARKKINDS = { + fm_bcnisd: "BCNISD", + fm_bcnlat_hydro: "BCNLAT_hydro", + fm_bcnlat_ienc: "bcnlat_ienc", + fm_boycar: "BOYCAR", + fm_boyisd: "BOYISD", + fm_boylat_hydro: "BOYLAT_hydro", + fm_boylat_ienc: "boylat_ienc", + fm_boysaw: "BOYSAW", + fm_boyspp: "BOYSPP", + fm_daymar_hydro: "DAYMAR_hydro", + fm_daymar_ienc: "daymar_ienc", + fm_lights: "LIGHTS", + fm_rtpbcn: "RTPBCN", + fm_topmar: "TOPMAR", + fm_notmrk: "notmrk" +}; + const initializeCurrentSchedule = () => { return { id: null, @@ -91,7 +109,8 @@ depth: null, sourceOrganization: null, trys: null, - waitRetry: null + waitRetry: null, + selectedMark: null }; }; @@ -141,7 +160,14 @@ const { kind, config, id } = payload; const eMailNotification = config["send-email"]; const { cron, url } = config; - Vue.set(state.currentSchedule, "importType", KINDIMPORTTYPE[kind]); + if (FAIRWAYMARKKINDS[kind]) { + Vue.set(state.currentSchedule, "importType", "fairwaymarks"); + debugger; + Vue.set(state.currentSchedule, "selectedMark", FAIRWAYMARKKINDS[kind]); + } else { + Vue.set(state.currentSchedule, "importType", KINDIMPORTTYPE[kind]); + } + Vue.set(state.currentSchedule, "id", id); Vue.set(state.currentSchedule, "trys", config["trys"]); Vue.set(state.currentSchedule, "waitRetry", config["wait-retry"]);
--- a/client/src/store/map.js Mon Mar 02 16:17:50 2020 +0100 +++ b/client/src/store/map.js Thu Mar 05 12:15:37 2020 +0100 @@ -14,18 +14,19 @@ * * Thomas Junk <thomas.junk@intevation.de> */ +import { Circle, Fill, Stroke, Style } from "ol/style"; +import { getArea, getLength } from "ol/sphere"; + import Draw from "ol/interaction/Draw"; -import { Stroke, Style, Fill, Circle } from "ol/style"; -import { fromLonLat } from "ol/proj"; -import { getLength, getArea } from "ol/sphere"; -import { transformExtent } from "ol/proj"; -import bbox from "@turf/bbox"; -import app from "@/main"; +import Feature from "ol/Feature"; import { HTTP } from "@/lib/http"; -import Feature from "ol/Feature"; import Point from "ol/geom/Point"; import { Vector as VectorLayer } from "ol/layer"; +import app from "@/main"; +import bbox from "@turf/bbox"; +import { fromLonLat } from "ol/proj"; import { toLonLat } from "ol/proj"; +import { transformExtent } from "ol/proj"; // initial state const init = () => { @@ -572,6 +573,31 @@ commit("addIdentifiedFeatures", features); }); } + + var fmSource = map.getLayer("FAIRWAYMARKS").getSource(); + var fmURL = fmSource.getGetFeatureInfoUrl( + event.coordinate, + currentResolution, + "EPSG:3857", + { INFO_FORMAT: "application/json" } + ); + if (fmSource) { + HTTP.get(fmURL, { + headers: { + "X-Gemma-Auth": localStorage.getItem("token") + } + }).then(response => { + let features = response.data.features.map(f => { + let feat = new Feature({ + geometry: new Point(f.geometry.coordinates) + }); + feat.setId(f.id); + feat.setProperties(f.properties); + return feat; + }); + commit("addIdentifiedFeatures", features); + }); + } }); }, refreshLayers({ state }) {
--- a/go.mod Mon Mar 02 16:17:50 2020 +0100 +++ b/go.mod Thu Mar 05 12:15:37 2020 +0100 @@ -5,6 +5,7 @@ require ( github.com/cockroachdb/apd v1.1.0 // indirect github.com/etcd-io/bbolt v1.3.3 + github.com/fatih/structs v1.1.0 github.com/fogleman/contourmap v0.0.0-20190814184649-9f61d36c4199 github.com/gofrs/uuid v3.2.0+incompatible // indirect github.com/gorilla/mux v1.7.3
--- a/go.sum Mon Mar 02 16:17:50 2020 +0100 +++ b/go.sum Thu Mar 05 12:15:37 2020 +0100 @@ -26,6 +26,8 @@ github.com/dgryski/go-sip13 v0.0.0-20181026042036-e10d5fee7954/go.mod h1:vAd38F8PWV+bWy6jNmig1y/TA+kYO4g3RSRF0IAv0no= github.com/etcd-io/bbolt v1.3.3 h1:gSJmxrs37LgTqR/oyJBWok6k6SvXEUerFTbltIhXkBM= github.com/etcd-io/bbolt v1.3.3/go.mod h1:ZF2nL25h33cCyBtcyWeZ2/I3HQOfTP+0PIEvHjkjCrw= +github.com/fatih/structs v1.1.0 h1:Q7juDM0QtcnhCpeyLGQKyg4TOIghuNXrkL32pHAUMxo= +github.com/fatih/structs v1.1.0/go.mod h1:9NiDSp5zOcgEDl+j00MP/WkGVPOlPRLejGD8Ga6PJ7M= github.com/fogleman/contourmap v0.0.0-20190814184649-9f61d36c4199 h1:kufr0u0RIG5ACpjFsPRbbuHa0FhMWsS3tnSFZ2hf07s= github.com/fogleman/contourmap v0.0.0-20190814184649-9f61d36c4199/go.mod h1:mqaaaP4j7nTF8T/hx5OCljA7BYWHmrH2uh+Q023OchE= github.com/fogleman/gg v1.2.1-0.20190220221249-0403632d5b90/go.mod h1:R/bRT+9gY/C5z7JzPU0zXsXHKM4/ayA+zqcVNZzPa1k=
--- a/pkg/auth/opendb.go Mon Mar 02 16:17:50 2020 +0100 +++ b/pkg/auth/opendb.go Thu Mar 05 12:15:37 2020 +0100 @@ -145,6 +145,24 @@ return fn(conn) } +// RunAllAs runs the given functions fns with a database connection impersonated +// as the given role. +// To make this work a metamorphic user has to be configured in +// the system configuration. +func RunAllAs(ctx context.Context, role string, fns ...func(*sql.Conn) error) error { + conn, err := metamorphConn(ctx, role) + if err != nil { + return err + } + defer conn.Close() + for _, fn := range fns { + if err := fn(conn); err != nil { + return err + } + } + return nil +} + // RunAsSessionUser is a convinience wrapper araound which extracts // the logged in user from a session and calls RunAs with it. func RunAsSessionUser(req *http.Request, fn func(*sql.Conn) error) error {
--- a/pkg/controllers/geostyling.go Mon Mar 02 16:17:50 2020 +0100 +++ b/pkg/controllers/geostyling.go Thu Mar 05 12:15:37 2020 +0100 @@ -31,20 +31,20 @@ styleName = "style" ) -func extractStyle(req *http.Request) (string, error) { +func extractStyle(req *http.Request) ([]byte, error) { f, _, err := req.FormFile(styleName) if err != nil { - return "", err + return nil, err } defer f.Close() var buf bytes.Buffer if _, err := io.Copy(&buf, io.LimitReader(f, maxStyleSize)); err != nil { - return "", err + return nil, err } - return buf.String(), nil + return buf.Bytes(), nil } func supportedWMSFeature(name string) bool {
--- a/pkg/controllers/publish.go Mon Mar 02 16:17:50 2020 +0100 +++ b/pkg/controllers/publish.go Thu Mar 05 12:15:37 2020 +0100 @@ -24,11 +24,13 @@ func published(req *http.Request) (mw.JSONResult, error) { return mw.JSONResult{ Result: struct { - Internal []models.IntEntry `json:"internal"` - External []models.ExtEntry `json:"external"` + Internal []models.IntEntry `json:"internal"` + LayerGroups []models.LayerGroup `json:"layer-groups"` + External []models.ExtEntry `json:"external"` }{ - Internal: models.InternalServices.Filter(models.InternalAll), - External: models.ExternalServices.Filter(models.ExternalAll), + Internal: models.InternalServices.Filter(models.InternalAll), + LayerGroups: models.InternalServices.LayerGroups(), + External: models.ExternalServices.Filter(models.ExternalAll), }, }, nil }
--- a/pkg/controllers/routes.go Mon Mar 02 16:17:50 2020 +0100 +++ b/pkg/controllers/routes.go Thu Mar 05 12:15:37 2020 +0100 @@ -4,7 +4,7 @@ // SPDX-License-Identifier: AGPL-3.0-or-later // License-Filename: LICENSES/AGPL-3.0.txt // -// Copyright (C) 2018 by via donau +// Copyright (C) 2018, 2020 by via donau // – Österreichische Wasserstraßen-Gesellschaft mbH // Software engineering by Intevation GmbH // @@ -243,6 +243,19 @@ "bn", "gm", "fa", "wx", "wa", "wg", "dmv", "fd", "dma", "sec", "dsec", "dst", "dsr", + "fm_bcnlat_hydro", + "fm_bcnlat_ienc", + "fm_boycar", + "fm_boylat_hydro", + "fm_boylat_ienc", + "fm_boysaw", + "fm_boyspp", + "fm_daymar_hydro", + "fm_daymar_ienc", + "fm_lights", + "fm_rtpbcn", + "fm_topmar", + "fm_notmrk", }, "|") api.Handle("/imports/{kind:"+kinds+"}", waterwayAdmin(&mw.JSONHandler{
--- a/pkg/geoserver/boot.go Mon Mar 02 16:17:50 2020 +0100 +++ b/pkg/geoserver/boot.go Thu Mar 05 12:15:37 2020 +0100 @@ -14,6 +14,7 @@ package geoserver import ( + "archive/zip" "bytes" "encoding/json" "encoding/xml" @@ -70,32 +71,6 @@ return bytes.NewReader(buf.Bytes()) } -type ftXML struct { - XMLName xml.Name `xml:"featureType"` - Name string `xml:"name"` - Title string `xml:"title"` - SRS *string `xml:"srs,omitempty"` - Metadata ftMetadata -} - -type ftMetadata struct { - XMLName xml.Name `xml:"metadata"` - Entry ftMetadataEntry -} - -type ftMetadataEntry struct { - XMLName xml.Name `xml:"entry"` - Key string `xml:"key,attr"` - VirtTable ftVirtTable -} - -type ftVirtTable struct { - XMLName xml.Name `xml:"virtualTable"` - Name string `xml:"name"` - SQL string `xml:"sql"` - KeyColumn *string `xml:"keyColumn,omitempty"` -} // End code for handling with XML - func asJSON(req *http.Request) { req.Header.Set("Content-Type", "application/json") } @@ -244,6 +219,14 @@ } func ensureFeatures() error { + tables := models.InternalServices.Filter(models.IntWFS) + if len(tables) == 0 { + log.Println("info: no tables to publish") + return nil + } + + log.Printf("info: number of tables to publish %d\n", len(tables)) + var ( geoURL = config.GeoServerURL() user = config.GeoServerUser() @@ -254,14 +237,6 @@ datastoreURL := geoURL + "/rest/workspaces/" + workspaceName + "/datastores/" + datastoreName - tables := models.InternalServices.Filter(models.IntWFS) - if len(tables) == 0 { - log.Println("info: no tables to publish") - return nil - } - - log.Printf("info: number of tables to publish %d\n", len(tables)) - var features struct { FeatureTypes struct { FeatureType []struct { @@ -325,48 +300,197 @@ log.Printf("info: creating featuretype %s.\n", table) var req *http.Request + + ft := map[string]interface{}{ + "name": table, + "nativeName": table, + "title": table, + } + if srs := tables[i].SRS; srs != nil { + ft["srs"] = *srs + } + + var entries []map[string]interface{} + if models.IntSQLView(tables[i]) { - // XXX: Creating SQL views with JSON via GeoServer REST-API fails - // Begin code for handling with XML instead - ft := ftXML{ - Name: table, - Title: table, - SRS: tables[i].SRS, - Metadata: ftMetadata{ - Entry: ftMetadataEntry{ - Key: "JDBC_VIRTUAL_TABLE", - VirtTable: ftVirtTable{ - Name: table, - SQL: *tables[i].SQL, - KeyColumn: tables[i].KeyColumn}}}} + vt := map[string]interface{}{ + "name": table, + "sql": *tables[i].SQL, + } + if kc := tables[i].KeyColumn; kc != nil { + vt["keyColumn"] = *kc + } + entry := map[string]interface{}{ + "@key": "JDBC_VIRTUAL_TABLE", + "virtualTable": vt, + } + entries = append(entries, entry) + } + + /* XXX: Experimental + if table == "sounding_differences" { + di := map[string]interface{}{ + "enabled": true, + "attribute": "minuend", + "endAttribute": "subtrahend", + "presentation": "CONTINUOUS_INTERVAL", + "units": "ISO8601", + "nearestMatchEnabled": false, + } + entry := map[string]interface{}{ + "@key": "time", + "dimensionInfo": di, + } + entries = append(entries, entry) + } + */ + + if len(entries) > 0 { + ft["metadata"] = map[string]interface{}{ + "entry": entries, + } + } + + doc := map[string]interface{}{ + "featureType": ft, + } + + req, err = http.NewRequest( + http.MethodPost, + datastoreURL+"/featuretypes", + toStream(doc)) + if err != nil { + return err + } + asJSON(req) + auth(req) + + resp, err := http.DefaultClient.Do(req) + if err != nil { + return err + } + + if resp.StatusCode != http.StatusCreated { + return fmt.Errorf("status code '%s' (%d)", + http.StatusText(resp.StatusCode), + resp.StatusCode) + } + } + + return nil +} + +func ensureLayerGroups() error { + + groups := models.InternalServices.LayerGroups() + if len(groups) == 0 { + log.Println("info: no groups layers to publish") + return nil + } + + log.Printf("info: number of layer groups to publish %d\n", len(groups)) - req, err = http.NewRequest( - http.MethodPost, - datastoreURL+"/featuretypes", - toXMLStream(ft)) - if err != nil { - return err + var ( + geoURL = config.GeoServerURL() + user = config.GeoServerUser() + password = config.GeoServerPassword() + auth = basicAuth(user, password) + ) + + type layerGroups struct { + LayerGroups struct { + LayerGroup []struct { + Name string `json:"name"` + } `json:"layerGroup"` + } `json:"layerGroups"` + } + + var lg layerGroups + + hasLayerGroup := func(name string) bool { + for i := range lg.LayerGroups.LayerGroup { + if lg.LayerGroups.LayerGroup[i].Name == name { + return true } - asContentType(req, "text/xml") - // End code for handling with XML instead - } else { - ft := map[string]interface{}{ - "featureType": map[string]interface{}{ - "name": table, - "nativeName": table, - "title": table, - }, + } + return false + } + + layerGroupsURL := geoURL + "/rest/workspaces/" + workspaceName + + "/layergroups" + + // Fetch all layer groups. + req, err := http.NewRequest( + http.MethodGet, + layerGroupsURL+".json", + nil) + if err != nil { + return err + } + auth(req) + resp, err := http.DefaultClient.Do(req) + if err != nil { + return err + } + + err = json.NewDecoder(resp.Body).Decode(&lg) + resp.Body.Close() + if err != nil { + // XXX: ignore this error. + } + + var already []string + + defer func() { + if len(already) > 0 { + log.Printf("info: already having layer groups: %s\n", + strings.Join(already, ", ")) + } + }() + + for i := range groups { + if hasLayerGroup(groups[i].Name) { + already = append(already, groups[i].Name) + continue + } + + log.Printf("info: creating layer group %s.\n", groups[i].Name) + + type ( + Layers struct { + Layer []string `xml:"layer"` } + Styles struct { + Style []string `xml:"style"` + } + LayerGroup struct { + XMLName xml.Name `xml:"layerGroup"` + Name string `xml:"name"` + Title string `xml:"title"` + Layers Layers `xml:"layers"` + Styles Styles `xml:"styles"` + } + ) - req, err = http.NewRequest( - http.MethodPost, - datastoreURL+"/featuretypes", - toStream(ft)) - if err != nil { - return err - } - asJSON(req) + lgr := LayerGroup{ + Name: groups[i].Name, + Title: groups[i].Name, + Layers: Layers{ + Layer: groups[i].Layers, + }, + Styles: Styles{ + Style: groups[i].Layers, + }, } + + req, err = http.NewRequest( + http.MethodPost, + layerGroupsURL+".xml", + toXMLStream(&lgr)) + if err != nil { + return err + } + asContentType(req, "text/xml") auth(req) resp, err := http.DefaultClient.Do(req) @@ -478,19 +602,33 @@ return stylePreprocessors[name] } +func isZip(data []byte) bool { + if len(data) == 0 { + return false + } + _, err := zip.NewReader(bytes.NewReader(data), int64(len(data))) + return err == nil +} + func updateStyle(entry *models.IntEntry, create bool) error { log.Printf("info: creating style %s\n", entry.Name) // Try to load the style data. - data, err := entry.LoadStyle() + binary, err := entry.LoadStyle() if err != nil { return err } - if processor := FindStylePreprocessor(entry.Name); processor != nil { - if data, err = processor(data); err != nil { - return err + zip := isZip(binary) + + if !zip { // We only support templating for plain XML styles. + if processor := FindStylePreprocessor(entry.Name); processor != nil { + data, err := processor(string(binary)) + if err != nil { + return err + } + binary = []byte(data) } } @@ -548,14 +686,17 @@ req, err := http.NewRequest( http.MethodPut, styleURL+"/"+url.PathEscape(entry.Name), - strings.NewReader(data)) + bytes.NewReader(binary)) if err != nil { return err } auth(req) - if isSymbologyEncoding(data) { + switch { + case zip: + asContentType(req, "application/zip") + case isSymbologyEncoding(binary): asContentType(req, "application/vnd.ogc.se+xml") - } else { + default: asContentType(req, "application/vnd.ogc.sld+xml") } resp, err := http.DefaultClient.Do(req) @@ -596,8 +737,8 @@ } // isSymbologyEncoding tries to figure out if its plain SLD or SE. -func isSymbologyEncoding(data string) bool { - decoder := xml.NewDecoder(strings.NewReader(data)) +func isSymbologyEncoding(data []byte) bool { + decoder := xml.NewDecoder(bytes.NewReader(data)) decoder.CharsetReader = charset.NewReaderLabel for { @@ -671,6 +812,7 @@ ensureWorkspace, ensureDataStore, ensureFeatures, + ensureLayerGroups, ensureStyles, } { if err := ensure(); err != nil {
--- a/pkg/imports/dsr.go Mon Mar 02 16:17:50 2020 +0100 +++ b/pkg/imports/dsr.go Thu Mar 05 12:15:37 2020 +0100 @@ -1,3 +1,4 @@ +// This is Free Software under GNU Affero General Public License v >= 3.0 // without warranty, see README.md and license for details. // // SPDX-License-Identifier: AGPL-3.0-or-later
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pkg/imports/fm.go Thu Mar 05 12:15:37 2020 +0100 @@ -0,0 +1,598 @@ +// This is Free Software under GNU Affero General Public License v >= 3.0 +// without warranty, see README.md and license for details. +// +// SPDX-License-Identifier: AGPL-3.0-or-later +// License-Filename: LICENSES/AGPL-3.0.txt +// +// Copyright (C) 2020 by via donau +// – Österreichische Wasserstraßen-Gesellschaft mbH +// Software engineering by Intevation GmbH +// +// Author(s): +// * Tom Gottfried <tom.gottfried@intevation.de> +// * Sascha L. Teichmann <sascha.teichmann@intevation.de> + +package imports + +import ( + "database/sql" + "fmt" + "strings" + + "gemma.intevation.de/gemma/pkg/pgxutils" + "github.com/fatih/structs" +) + +type ( + // Properties common to all types of fairway marks + FairwayMarksProperties struct { + Datsta *string `json:"hydro_datsta"` + Datend *string `json:"hydro_datend"` + Persta *string `json:"hydro_persta"` + Perend *string `json:"hydro_perend"` + Objnam *string `json:"hydro_objnam"` + Nobjnm *string `json:"hydro_nobjnm"` + Inform *string `json:"hydro_inform"` + Ninfom *string `json:"hydro_ninfom"` + Scamin *int `json:"hydro_scamin"` + Picrep *string `json:"hydro_picrep"` + Txtdsc *string `json:"hydro_txtdsc"` + Sordat *string `json:"hydro_sordat"` + Sorind *string `json:"hydro_sorind"` + } + + BcnlatProperties struct { + FairwayMarksProperties + Colour *string `json:"hydro_colour"` + Colpat *string `json:"hydro_colpat"` + Condtn *int `json:"hydro_condtn"` + Bcnshp *int `json:"hydro_bcnshp"` + } + + bcnlatHydroProperties struct { + BcnlatProperties + Catlam *int64 `json:"hydro_catlam"` + } + + bcnlatIencProperties struct { + BcnlatProperties + Catlam *int64 `json:"ienc_catlam"` + Dirimp *string `json:"ienc_dirimp" structs:"-"` + } + + BoylatProperties struct { + FairwayMarksProperties + Colour *string `json:"hydro_colour"` + Colpat *string `json:"hydro_colpat"` + Conrad *int `json:"hydro_conrad"` + Boyshp *int `json:"hydro_boyshp"` + } + + boylatHydroProperties struct { + BoylatProperties + Marsys *int64 `json:"hydro_marsys"` + Catlam *int64 `json:"hydro_catlam"` + } + + boylatIencProperties struct { + BoylatProperties + Marsys *int64 `json:"ienc_marsys"` + Catlam *int64 `json:"ienc_catlam"` + } + + boycarProperties struct { + FairwayMarksProperties + Colour *string `json:"hydro_colour"` + Colpat *string `json:"hydro_colpat"` + Conrad *int `json:"hydro_conrad"` + Marsys *int `json:"hydro_marsys"` + Boyshp *int `json:"hydro_boyshp"` + Catcam *int `json:"hydro_catcam"` + } + + boysawProperties struct { + FairwayMarksProperties + Colour *string `json:"hydro_colour"` + Colpat *string `json:"hydro_colpat"` + Conrad *int `json:"hydro_conrad"` + Marsys *int64 `json:"hydro_marsys"` + Boyshp *int `json:"hydro_boyshp"` + } + + boysppProperties struct { + FairwayMarksProperties + Colour *string `json:"hydro_colour"` + Colpat *string `json:"hydro_colpat"` + Conrad *int `json:"hydro_conrad"` + Marsys *int64 `json:"hydro_marsys"` + Boyshp *int `json:"hydro_boyshp"` + Catspm *string `json:"hydro_catspm"` + } + + DaymarProperties struct { + FairwayMarksProperties + Colour *string `json:"hydro_colour"` + Colpat *string `json:"hydro_colpat"` + Condtn *int `json:"hydro_condtn"` + Topshp *int `json:"hydro_topshp"` + } + + daymarHydroProperties struct { + DaymarProperties + } + + daymarIencProperties struct { + DaymarProperties + Dirimp *string `json:"ienc_dirimp" structs:"-"` + Orient *float64 `json:"hydro_orient"` + } + + lightsProperties struct { + FairwayMarksProperties + Colour *string `json:"hydro_colour"` + Condtn *int `json:"hydro_condtn"` + Orient *float64 `json:"hydro_orient"` + Catlit *string `json:"hydro_catlit"` + Exclit *int `json:"hydro_exclit"` + Litchr *int `json:"hydro_litchr"` + Litvis *string `json:"hydro_litvis"` + Mltylt *int `json:"hydro_mltylt"` + Sectr1 *float64 `json:"hydro_sectr1"` + Sectr2 *float64 `json:"hydro_sectr2"` + Siggrp *string `json:"hydro_siggrp"` + Sigper *float64 `json:"hydro_sigper"` + Sigseq *string `json:"hydro_sigseq"` + Status *string `json:"hydro_status"` + } + + notmrkProperties struct { + FairwayMarksProperties + Condtn *int `json:"hydro_condtn"` + Marsys *int `json:"hydro_bcnshp"` + Dirimp *string `json:"ienc_dirimp" structs:"-"` + Orient *float64 `json:"hydro_orient"` + Status *string `json:"hydro_status"` + Addmrk *string `json:"ienc_addmrk"` + Catnmk *int `json:"ienc_catnmk"` + Disipd *float64 `json:"ienc_disipd"` + Disipu *float64 `json:"ienc_disipu"` + Disbk1 *float64 `json:"ienc_disbk1"` + Disbk2 *float64 `json:"ienc_disbk2"` + Fnctnm *int `json:"ienc_fnctnm"` + Bnkwtw *int `json:"ienc_bnkwtw"` + } + + rtpbcnProperties struct { + FairwayMarksProperties + Condtn *int `json:"hydro_condtn"` + Siggrp *string `json:"hydro_siggrp"` + Catrtb *int `json:"hydro_catrtb"` + Radwal *string `json:"hydro_radwal"` + } + + topmarProperties struct { + FairwayMarksProperties + Colour *string `json:"hydro_colour"` + Colpat *string `json:"hydro_colpat"` + Condtn *int `json:"hydro_condtn"` + Topshp *int `json:"hydro_topshp"` + } +) + +func (props *bcnlatIencProperties) attrib() *string { + return props.Dirimp +} + +func (props *daymarIencProperties) attrib() *string { + return props.Dirimp +} + +func (props *notmrkProperties) attrib() *string { + return props.Dirimp +} + +const ( + BCNLATHYDROJobKind JobKind = "fm_bcnlat_hydro" + BCNLATIENCJobKind JobKind = "fm_bcnlat_ienc" + BOYLATHYDROJobKind JobKind = "fm_boylat_hydro" + BOYLATIENCJobKind JobKind = "fm_boylat_ienc" + BOYCARJobKind JobKind = "fm_boycar" + BOYSAWJobKind JobKind = "fm_boysaw" + BOYSPPJobKind JobKind = "fm_boyspp" + DAYMARHYDROJobKind JobKind = "fm_daymar_hydro" + DAYMARIENCJobKind JobKind = "fm_daymar_ienc" + LIGHTSJobKind JobKind = "fm_lights" + NOTMRKJobKind JobKind = "fm_notmrk" + RTPBCNJobKind JobKind = "fm_rtpbcn" + TOPMARJobKind JobKind = "fm_topmar" +) + +func init() { + RegisterJobCreator(BCNLATHYDROJobKind, + &WFSFeatureJobCreator{ + description: "fairway marks bcnlat (HYDRO)", + depends: [2][]string{{"fairway_marks_bcnlat_hydro"}, {}}, + newConsumer: newSQLConsumer( + prepareStmnts( + createInsertFMSQL("bcnlat_hydro", + "colour", "colpat", "condtn", "bcnshp", "catlam"), + ), + consume, + createInvalidation("bcnlat_hydro"), + newPointSlice(func() interface{} { return new(bcnlatHydroProperties) }), + ), + }) + + RegisterJobCreator(BCNLATIENCJobKind, + &WFSFeatureJobCreator{ + description: "fairway marks bcnlat (IENC)", + depends: [2][]string{{"fairway_marks_bcnlat_ienc"}, {}}, + newConsumer: newSQLConsumer( + prepareStmnts( + createInsertFMSQL("bcnlat_ienc", + "colour", "colpat", "condtn", "bcnshp", "catlam"), + insertBcnlatDirimpSQL, + ), + consume, + createInvalidation("bcnlat_ienc"), + newPointSlice(func() interface{} { return new(bcnlatIencProperties) }), + ), + }) + + RegisterJobCreator(BOYLATHYDROJobKind, + &WFSFeatureJobCreator{ + description: "fairway marks boylat (HYDRO)", + depends: [2][]string{{"fairway_marks_boylat_hydro"}, {}}, + newConsumer: newSQLConsumer( + prepareStmnts( + createInsertFMSQL("boylat_hydro", + "colour", "colpat", "conrad", + "marsys", "boyshp", "catlam"), + ), + consume, + createInvalidation("boylat_hydro"), + newPointSlice(func() interface{} { return new(boylatHydroProperties) }), + ), + }) + + RegisterJobCreator(BOYLATIENCJobKind, + &WFSFeatureJobCreator{ + description: "fairway marks boylat (IENC)", + depends: [2][]string{{"fairway_marks_boylat_ienc"}, {}}, + newConsumer: newSQLConsumer( + prepareStmnts( + createInsertFMSQL("boylat_ienc", + "colour", "colpat", "conrad", + "marsys", "boyshp", "catlam"), + ), + consume, + createInvalidation("boylat_ienc"), + newPointSlice(func() interface{} { return new(boylatIencProperties) }), + ), + }) + + RegisterJobCreator(BOYCARJobKind, + &WFSFeatureJobCreator{ + description: "fairway marks boycar", + depends: [2][]string{{"fairway_marks_boycar"}, {}}, + newConsumer: newSQLConsumer( + prepareStmnts( + createInsertFMSQL("boycar", + "colour", "colpat", "conrad", + "marsys", "boyshp", "catcam"), + ), + consume, + createInvalidation("boycar"), + newPointSlice(func() interface{} { return new(boycarProperties) }), + ), + }) + + RegisterJobCreator(BOYSAWJobKind, + &WFSFeatureJobCreator{ + description: "fairway marks boysaw", + depends: [2][]string{{"fairway_marks_boysaw"}, {}}, + newConsumer: newSQLConsumer( + prepareStmnts( + createInsertFMSQL("boysaw", + "colour", "colpat", "conrad", "marsys", "boyshp"), + ), + consume, + createInvalidation("boysaw"), + newPointSlice(func() interface{} { return new(boysawProperties) }), + ), + }) + + RegisterJobCreator(BOYSPPJobKind, + &WFSFeatureJobCreator{ + description: "fairway marks boyspp", + depends: [2][]string{{"fairway_marks_boyspp"}, {}}, + newConsumer: newSQLConsumer( + prepareStmnts( + createInsertFMSQL("boyspp", + "colour", "colpat", "conrad", + "marsys", "boyshp", "catspm"), + ), + consume, + createInvalidation("boyspp"), + newPointSlice(func() interface{} { return new(boysppProperties) }), + ), + }) + + RegisterJobCreator(DAYMARHYDROJobKind, + &WFSFeatureJobCreator{ + description: "fairway marks daymar (HYDRO)", + depends: [2][]string{{"fairway_marks_daymar_hydro"}, {}}, + newConsumer: newSQLConsumer( + prepareStmnts( + createInsertFMSQL("daymar_hydro", + "colour", "colpat", "condtn", "topshp"), + ), + consume, + createInvalidation("daymar_hydro"), + newPointSlice(func() interface{} { return new(daymarHydroProperties) }), + ), + }) + + RegisterJobCreator(DAYMARIENCJobKind, + &WFSFeatureJobCreator{ + description: "fairway marks daymar (IENC)", + depends: [2][]string{{"fairway_marks_daymar_ienc"}, {}}, + newConsumer: newSQLConsumer( + prepareStmnts( + createInsertFMSQL("daymar_ienc", + "colour", "colpat", "condtn", "topshp", "orient"), + insertDaymarDirimpSQL, + ), + consume, + createInvalidation("daymar_ienc"), + newPointSlice(func() interface{} { return new(daymarIencProperties) }), + ), + }) + + RegisterJobCreator(LIGHTSJobKind, + &WFSFeatureJobCreator{ + description: "fairway marks lights", + depends: [2][]string{{"fairway_marks_lights"}, {}}, + newConsumer: newSQLConsumer( + prepareStmnts( + createInsertFMSQL("lights", + "colour", "condtn", "orient", + "catlit", "exclit", "litchr", + "litvis", "mltylt", "sectr1", + "sectr2", "siggrp", "sigper", + "sigseq", "status"), + ), + consume, + createInvalidation("lights"), + newPointSlice(func() interface{} { return new(lightsProperties) }), + ), + }) + + RegisterJobCreator(NOTMRKJobKind, + &WFSFeatureJobCreator{ + description: "fairway marks notmrk", + depends: [2][]string{{"fairway_marks_lights"}, {}}, + newConsumer: newSQLConsumer( + prepareStmnts( + createInsertFMSQL("notmrk", + "condtn", "marsys", "orient", + "status", "addmrk", "catnmk", + "disipd", "disipu", "disbk1", + "disbk2", "fnctnm", "bnkwtw"), + insertNotmrkDirimpSQL, + ), + consume, + createInvalidation("notmrk"), + newPointSlice(func() interface{} { return new(notmrkProperties) }), + ), + }) + + RegisterJobCreator(RTPBCNJobKind, + &WFSFeatureJobCreator{ + description: "fairway marks rtpbcn", + depends: [2][]string{{"fairway_marks_rtpbcn"}, {}}, + newConsumer: newSQLConsumer( + prepareStmnts( + createInsertFMSQL("rtpbcn", + "condtn", "siggrp", "catrtb", "radwal"), + ), + consume, + createInvalidation("rtpbcn"), + newPointSlice(func() interface{} { return new(rtpbcnProperties) }), + ), + }) + + RegisterJobCreator(TOPMARJobKind, + &WFSFeatureJobCreator{ + description: "fairway marks topmar", + depends: [2][]string{{"fairway_marks_topmar"}, {}}, + newConsumer: newSQLConsumer( + prepareStmnts( + createInsertFMSQL("topmar", + "colour", "colpat", "condtn", "topshp"), + ), + consume, + createInvalidation("topmar"), + newPointSlice(func() interface{} { return new(topmarProperties) }), + ), + }) +} + +const ( + // Format string to be completed with type and additional attributes + /* Instead of the row comparisons in the WHERE clauses + of the CTE with the UPDATE and the INSERT ... SELECT, we could have + used the row-based UNIQUE indexes as arbiter indexes + in an INSERT ... ON CONFLICT ... DO UPDATE, but that turned out + to be able to bypass the UNIQUE index in some cases. + */ + insertFMSQLtmpl = ` +WITH a AS ( + SELECT users.current_user_area_utm() AS a +), +g AS ( + SELECT newfm + FROM ST_Transform(ST_GeomFromWKB($1, $2::integer), 4326) AS newfm (newfm) + WHERE pg_has_role('sys_admin', 'MEMBER') + OR ST_Intersects((select a from a), + ST_Transform(newfm, (select ST_SRID(a) from a))) +), +t AS ( + -- Currently valid and otherwise identical entry's validity. + /* If there are no intermittent updates of validity, + there will always be only one currently valid and + otherwise identical entry. */ + UPDATE waterway.fairway_marks_%[1]s SET last_found = current_timestamp + WHERE validity @> current_timestamp + AND (geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + %[2]s + ) IS NOT DISTINCT FROM ( + (SELECT newfm FROM g), + $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, + %[3]s) + RETURNING 1 +) +INSERT INTO waterway.fairway_marks_%[1]s ( + geom, + datsta, + datend, + persta, + perend, + objnam, + nobjnm, + inform, + ninfom, + scamin, + picrep, + txtdsc, + sordat, + sorind, + %[2]s +) +SELECT newfm, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, + %[3]s + FROM g + WHERE NOT EXISTS(SELECT 1 FROM t) +RETURNING id +` + + // Assume validity ended for all entries not found in data source + invalidateFairwayMarksSQLtmpl = ` +WITH + a AS ( + SELECT users.current_user_area_utm() AS a + ) +UPDATE waterway.fairway_marks_%s + SET validity = tstzrange(lower(validity), current_timestamp) + WHERE validity @> current_timestamp + AND last_found < current_timestamp + AND (pg_has_role('sys_admin', 'MEMBER') + OR ST_Intersects((select a from a), + ST_Transform(CAST(geom AS geometry), (select ST_SRID(a) from a)))) + ` + + insertBcnlatDirimpSQL = ` +INSERT INTO waterway.fairway_marks_bcnlat_dirimps (fm_bcnlat_id, dirimp) + VALUES ($1, $2) +` + + insertDaymarDirimpSQL = ` +INSERT INTO waterway.fairway_marks_daymar_dirimps (fm_daymar_id, dirimp) + VALUES ($1, $2) +` + + insertNotmrkDirimpSQL = ` +INSERT INTO waterway.fairway_marks_notmrk_dirimps (fm_notmrk_id, dirimp) + VALUES ($1, $2) +` +) + +func createInvalidation(fmType string) func(*SQLGeometryConsumer) error { + + invalidateFairwayMarksSQL := fmt.Sprintf(invalidateFairwayMarksSQLtmpl, fmType) + + return func(spc *SQLGeometryConsumer) error { + res, err := spc.tx.ExecContext(spc.ctx, invalidateFairwayMarksSQL) + if err != nil { + return err + } + old, err := res.RowsAffected() + if err != nil { + return err + } + if old == 0 { + return ErrFeaturesUnmodified + } + spc.feedback.Info("Number of features removed from data source: %d", old) + return nil + } +} + +// Create INSERT statement for specific fairway marks type +func createInsertFMSQL(fmType string, attributes ...string) string { + attNums := "$16" + for i := 1; i < len(attributes); i++ { + attNums += fmt.Sprintf(",$%d", 16+i) + } + + return fmt.Sprintf( + insertFMSQLtmpl, + fmType, + strings.Join(attributes, ","), + attNums, + ) +} + +func storeAttribs(spc *SQLGeometryConsumer, id int64, attrs *string) { + if attrs == nil || *attrs == "" { + return + } + dirimps := strings.Split(*attrs, ",") + for _, dirimp := range dirimps { + if err := spc.savepoint(func() error { + _, err := spc.stmts[1].ExecContext( + spc.ctx, id, dirimp) + return err + }); err != nil { + spc.feedback.Warn( + pgxutils.ReadableError{Err: err}.Error()) + spc.feedback.Info( + "Tried to import '%s' as dirimp value", + dirimp) + } + } +} + +func consume( + spc *SQLGeometryConsumer, + points, properties interface{}, + epsg int, +) error { + var fmid int64 + err := spc.savepoint(func() error { + return spc.stmts[0].QueryRowContext( + spc.ctx, + append( + []interface{}{ + points.(*pointSlice).asWKB(), + epsg, + }, + structs.Values(properties)...)..., + ).Scan(&fmid) + }) + switch { + case err == sql.ErrNoRows: + return ErrFeatureDuplicated + // ignore -> filtered by responsibility area or a duplicate + case err != nil: + spc.feedback.Error(pgxutils.ReadableError{Err: err}.Error()) + return ErrFeatureIgnored + default: + if attr, ok := properties.(interface{ attrib() *string }); ok { + storeAttribs(spc, fmid, attr.attrib()) + } + } + return nil +}
--- a/pkg/imports/misc.go Mon Mar 02 16:17:50 2020 +0100 +++ b/pkg/imports/misc.go Thu Mar 05 12:15:37 2020 +0100 @@ -1,3 +1,6 @@ +// This is Free Software under GNU Affero General Public License v >= 3.0 +// without warranty, see README.md and license for details. +// // SPDX-License-Identifier: AGPL-3.0-or-later // License-Filename: LICENSES/AGPL-3.0.txt // @@ -34,7 +37,7 @@ // of managing database SAVEPOINTs. // If the returned function is called with a callback // the callback is run in a SAVEPOINT. -// If the callback returns w/o an error the SAVEPOINT +// If the callback returns w/o an error or with sql.ErrNoRows the SAVEPOINT // is released. Otherwise the SAVEPOINT is rolled back. func Savepoint( ctx context.Context, @@ -61,9 +64,13 @@ } } }() - if err = fn(); err == nil { + + // Release SAVEPOINT if statements run in fn returned no database error + if err = fn(); err == nil || err == sql.ErrNoRows { done = true - _, err = tx.ExecContext(ctx, release) + if _, err2 := tx.ExecContext(ctx, release); err2 != nil { + err = err2 + } } return }
--- a/pkg/imports/modelconvert.go Mon Mar 02 16:17:50 2020 +0100 +++ b/pkg/imports/modelconvert.go Thu Mar 05 12:15:37 2020 +0100 @@ -4,7 +4,7 @@ // SPDX-License-Identifier: AGPL-3.0-or-later // License-Filename: LICENSES/AGPL-3.0.txt // -// Copyright (C) 2018 by via donau +// Copyright (C) 2018, 2020 by via donau // – Österreichische Wasserstraßen-Gesellschaft mbH // Software engineering by Intevation GmbH // @@ -18,20 +18,33 @@ ) var kindToImportModel = map[JobKind]func() interface{}{ - BNJobKind: func() interface{} { return new(models.BottleneckImport) }, - GMJobKind: func() interface{} { return new(models.GaugeMeasurementImport) }, - FAJobKind: func() interface{} { return new(models.FairwayAvailabilityImport) }, - WXJobKind: func() interface{} { return new(models.WaterwayAxisImport) }, - WAJobKind: func() interface{} { return new(models.WaterwayAreaImport) }, - WGJobKind: func() interface{} { return new(models.WaterwayGaugeImport) }, - DMVJobKind: func() interface{} { return new(models.DistanceMarksVirtualImport) }, - FDJobKind: func() interface{} { return new(models.FairwayDimensionImport) }, - DMAJobKind: func() interface{} { return new(models.DistanceMarksAshoreImport) }, - STJobKind: func() interface{} { return new(models.StretchImport) }, - SECJobKind: func() interface{} { return new(models.SectionImport) }, - DSECJobKind: func() interface{} { return new(models.SectionDelete) }, - DSTJobKind: func() interface{} { return new(models.StretchDelete) }, - DSRJobKind: func() interface{} { return new(models.SoundingResultDelete) }, + BNJobKind: func() interface{} { return new(models.BottleneckImport) }, + GMJobKind: func() interface{} { return new(models.GaugeMeasurementImport) }, + FAJobKind: func() interface{} { return new(models.FairwayAvailabilityImport) }, + WXJobKind: func() interface{} { return new(models.WaterwayAxisImport) }, + WAJobKind: func() interface{} { return new(models.WaterwayAreaImport) }, + WGJobKind: func() interface{} { return new(models.WaterwayGaugeImport) }, + DMVJobKind: func() interface{} { return new(models.DistanceMarksVirtualImport) }, + FDJobKind: func() interface{} { return new(models.FairwayDimensionImport) }, + DMAJobKind: func() interface{} { return new(models.DistanceMarksAshoreImport) }, + BCNLATHYDROJobKind: func() interface{} { return FindJobCreator(BCNLATHYDROJobKind).Create() }, + BCNLATIENCJobKind: func() interface{} { return FindJobCreator(BCNLATIENCJobKind).Create() }, + BOYCARJobKind: func() interface{} { return FindJobCreator(BOYCARJobKind).Create() }, + BOYLATHYDROJobKind: func() interface{} { return FindJobCreator(BOYLATHYDROJobKind).Create() }, + BOYLATIENCJobKind: func() interface{} { return FindJobCreator(BOYLATIENCJobKind).Create() }, + BOYSAWJobKind: func() interface{} { return FindJobCreator(BOYSAWJobKind).Create() }, + BOYSPPJobKind: func() interface{} { return FindJobCreator(BOYSPPJobKind).Create() }, + DAYMARHYDROJobKind: func() interface{} { return FindJobCreator(DAYMARHYDROJobKind).Create() }, + DAYMARIENCJobKind: func() interface{} { return FindJobCreator(DAYMARIENCJobKind).Create() }, + LIGHTSJobKind: func() interface{} { return FindJobCreator(LIGHTSJobKind).Create() }, + RTPBCNJobKind: func() interface{} { return FindJobCreator(RTPBCNJobKind).Create() }, + TOPMARJobKind: func() interface{} { return FindJobCreator(TOPMARJobKind).Create() }, + NOTMRKJobKind: func() interface{} { return FindJobCreator(NOTMRKJobKind).Create() }, + STJobKind: func() interface{} { return new(models.StretchImport) }, + SECJobKind: func() interface{} { return new(models.SectionImport) }, + DSECJobKind: func() interface{} { return new(models.SectionDelete) }, + DSTJobKind: func() interface{} { return new(models.StretchDelete) }, + DSRJobKind: func() interface{} { return new(models.SoundingResultDelete) }, } // ImportModelForJobKind returns the constructor function to @@ -196,7 +209,7 @@ func ConvertToInternal(kind JobKind, src interface{}) interface{} { fn := convertModel[kind] if fn == nil { - return nil + return src } return fn(src) }
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pkg/imports/wfsjob.go Thu Mar 05 12:15:37 2020 +0100 @@ -0,0 +1,355 @@ +// This is Free Software under GNU Affero General Public License v >= 3.0 +// without warranty, see README.md and license for details. +// +// SPDX-License-Identifier: AGPL-3.0-or-later +// License-Filename: LICENSES/AGPL-3.0.txt +// +// Copyright (C) 2020 by via donau +// – Österreichische Wasserstraßen-Gesellschaft mbH +// Software engineering by Intevation GmbH +// +// Author(s): +// * Tom Gottfried <tom.gottfried@intevation.de> +// * Sascha L. Teichmann <sascha.teichmann@intevation.de> + +package imports + +import ( + "context" + "database/sql" + "encoding/json" + "errors" + "fmt" + "io" + "time" + + "gemma.intevation.de/gemma/pkg/models" + "gemma.intevation.de/gemma/pkg/wfs" +) + +var ( + ErrFeatureIgnored = errors.New("feature ignored") + ErrFeatureDuplicated = errors.New("feature duplicated") + ErrFeaturesUnmodified = errors.New("features unmodified") +) + +type ( + WFSFeatureConsumer interface { + Commit() error + Rollback() error + + NewFeature() (kind string, geom interface{}, properties interface{}) + + Consume(geom, properties interface{}, epsg int) error + } + + WFSFeatureJobCreator struct { + description string + depends [2][]string + + newConsumer func(context.Context, *sql.Conn, Feedback) (WFSFeatureConsumer, error) + + stageDone func(context.Context, *sql.Tx, int64) error + } + + WFSFeatureJob struct { + models.WFSImport + creator *WFSFeatureJobCreator + } +) + +func (wfjc *WFSFeatureJobCreator) Description() string { + return wfjc.description +} + +func (wfjc *WFSFeatureJobCreator) Depends() [2][]string { + return wfjc.depends +} + +func (wfjc *WFSFeatureJobCreator) AutoAccept() bool { + return wfjc.stageDone == nil +} + +func (wfjc *WFSFeatureJobCreator) StageDone(ctx context.Context, tx *sql.Tx, id int64) error { + if wfjc.stageDone == nil { + return nil + } + return wfjc.stageDone(ctx, tx, id) +} + +func (wfjc *WFSFeatureJobCreator) Create() Job { + return &WFSFeatureJob{creator: wfjc} +} + +// Description gives a short info about relevant facts of this import. +func (wfj *WFSFeatureJob) Description() (string, error) { + return wfj.URL + "|" + wfj.FeatureType, nil +} + +// CleanUp for WFS imports is a NOP. +func (*WFSFeatureJob) CleanUp() error { + return nil +} + +func (wfj *WFSFeatureJob) Do( + ctx context.Context, + importID int64, + conn *sql.Conn, + feedback Feedback, +) (interface{}, error) { + + start := time.Now() + + feedback.Info("Import %s", wfj.creator.Description()) + + feedback.Info("Loading capabilities from %s", wfj.URL) + caps, err := wfs.GetCapabilities(wfj.URL) + if err != nil { + feedback.Error("Loading capabilities failed: %v", err) + return nil, err + } + + ft := caps.FindFeatureType(wfj.FeatureType) + if ft == nil { + return nil, fmt.Errorf("unknown feature type '%s'", wfj.FeatureType) + } + + feedback.Info("Found feature type '%s'", wfj.FeatureType) + + epsg, err := wfs.CRSToEPSG(ft.DefaultCRS) + if err != nil { + feedback.Error("Unsupported CRS: '%s'", ft.DefaultCRS) + return nil, err + } + + if nilString(wfj.SortBy) != "" { + feedback.Info("Features will be sorted by '%s'", *wfj.SortBy) + } + + dl, err := wfs.GetFeatures(caps, wfj.FeatureType, nilString(wfj.SortBy)) + if err != nil { + feedback.Error("Cannot create GetFeature URLs. %v", err) + return nil, err + } + + var ( + unsupported = stringCounter{} + missingProperties int + badProperties int + dupes int + features int + ) + + consumer, err := wfj.creator.newConsumer(ctx, conn, feedback) + if err != nil { + return nil, err + } + defer consumer.Rollback() + + if err := dl.Download(nilString(wfj.User), nilString(wfj.Password), func(url string, r io.Reader) error { + feedback.Info("Get features from: '%s'", url) + rfc, err := wfs.ParseRawFeatureCollection(r) + if err != nil { + return fmt.Errorf("parsing GetFeature document failed: %v", err) + } + if rfc.CRS != nil { + crsName := rfc.CRS.Properties.Name + if epsg, err = wfs.CRSToEPSG(crsName); err != nil { + feedback.Error("Unsupported CRS: %d", crsName) + return err + } + } + + // No features -> ignore. + if rfc.Features == nil { + return nil + } + + feedback.Info("Using EPSG: %d", epsg) + + feedback.Info( + "Found %d features in data source", len(rfc.Features)) + + for _, feature := range rfc.Features { + if feature.Properties == nil || feature.Geometry.Coordinates == nil { + missingProperties++ + continue + } + + kind, geom, props := consumer.NewFeature() + + if err := json.Unmarshal(*feature.Properties, props); err != nil { + badProperties++ + continue + } + + if feature.Geometry.Type == kind { + if err := json.Unmarshal(*feature.Geometry.Coordinates, geom); err != nil { + return err + } + + err := consumer.Consume(geom, props, epsg) + switch { + case err == ErrFeatureDuplicated: + dupes++ + case err == ErrFeatureIgnored: + // be silent + case err != nil: + return err + default: + features++ + } + } else { + unsupported[feature.Geometry.Type]++ + } + } + return nil + }); err != nil { + return nil, err + } + + if dupes > 0 { + feedback.Info( + "Features outside responsibility area, duplicates or unchanged: %d", + dupes) + } + + if badProperties > 0 { + feedback.Warn("Bad properties: %d", badProperties) + } + + if missingProperties > 0 { + feedback.Warn("Missing properties: %d", missingProperties) + } + + if len(unsupported) != 0 { + feedback.Warn("Unsupported types found: %s", unsupported) + } + + if err = consumer.Commit(); err == nil || err == ErrFeaturesUnmodified { + feedback.Info("Storing %d features took %s", + features, time.Since(start)) + } + + // Commit before eventually returning UnchangedError because we might + // have updated last_found + if features == 0 && err == ErrFeaturesUnmodified { + return nil, UnchangedError("no valid new features found") + } + + if err == ErrFeaturesUnmodified { + // It's not really an error. + err = nil + } + + return nil, err +} + +type ( + SQLGeometryConsumer struct { + ctx context.Context + tx *sql.Tx + feedback Feedback + consume func(*SQLGeometryConsumer, interface{}, interface{}, int) error + newFeature func() (string, interface{}, interface{}) + preCommit func(*SQLGeometryConsumer) error + savepoint func(func() error) error + stmts []*sql.Stmt + } +) + +func (sgc *SQLGeometryConsumer) Rollback() error { + if tx := sgc.tx; tx != nil { + sgc.releaseStmts() + sgc.tx = nil + sgc.ctx = nil + return tx.Rollback() + } + return nil +} + +func (sgc *SQLGeometryConsumer) Commit() error { + var err error + if tx := sgc.tx; tx != nil { + if sgc.preCommit != nil { + err = sgc.preCommit(sgc) + } + sgc.releaseStmts() + sgc.tx = nil + sgc.ctx = nil + if err2 := tx.Commit(); err2 != nil { + // A real error on commit overrules the first. + err = err2 + } + } + return err +} + +func (sgc *SQLGeometryConsumer) NewFeature() (string, interface{}, interface{}) { + return sgc.newFeature() +} + +func (sgc *SQLGeometryConsumer) Consume( + geom, properties interface{}, + epsg int, +) error { + return sgc.consume(sgc, geom, properties, epsg) +} + +func (sgc *SQLGeometryConsumer) ConsumePolygon( + polygon polygonSlice, + properties interface{}, + epsg int, +) error { + return sgc.consume(sgc, polygon, properties, epsg) +} + +func newSQLConsumer( + init func(*SQLGeometryConsumer) error, + consume func(*SQLGeometryConsumer, interface{}, interface{}, int) error, + preCommit func(*SQLGeometryConsumer) error, + newFeature func() (string, interface{}, interface{}), + +) func(context.Context, *sql.Conn, Feedback) (WFSFeatureConsumer, error) { + return func(ctx context.Context, conn *sql.Conn, feedback Feedback) (WFSFeatureConsumer, error) { + tx, err := conn.BeginTx(ctx, nil) + if err != nil { + return nil, err + } + sgc := &SQLGeometryConsumer{ + ctx: ctx, + tx: tx, + feedback: feedback, + consume: consume, + newFeature: newFeature, + preCommit: preCommit, + savepoint: Savepoint(ctx, tx, "feature"), + } + if err := init(sgc); err != nil { + tx.Rollback() + return nil, err + } + return sgc, nil + } +} + +func (sgc *SQLGeometryConsumer) releaseStmts() { + for i := len(sgc.stmts); i > 0; i-- { + sgc.stmts[i-1].Close() + sgc.stmts[i-1] = nil + } + sgc.stmts = nil +} + +func prepareStmnts(queries ...string) func(*SQLGeometryConsumer) error { + return func(sgc *SQLGeometryConsumer) error { + for _, query := range queries { + stmt, err := sgc.tx.PrepareContext(sgc.ctx, query) + if err != nil { + return err + } + sgc.stmts = append(sgc.stmts, stmt) + } + return nil + } +}
--- a/pkg/imports/wkb.go Mon Mar 02 16:17:50 2020 +0100 +++ b/pkg/imports/wkb.go Thu Mar 05 12:15:37 2020 +0100 @@ -30,6 +30,12 @@ polygonSlice [][][]float64 ) +func newPointSlice(newProperties func() interface{}) func() (string, interface{}, interface{}) { + return func() (string, interface{}, interface{}) { + return "Point", new(pointSlice), newProperties() + } +} + func (ls lineSlice) asWKB() []byte { size := 1 + 4 + 4 + len(ls)*(2*8)
--- a/pkg/models/imports.go Mon Mar 02 16:17:50 2020 +0100 +++ b/pkg/models/imports.go Thu Mar 05 12:15:37 2020 +0100 @@ -4,7 +4,7 @@ // SPDX-License-Identifier: AGPL-3.0-or-later // License-Filename: LICENSES/AGPL-3.0.txt // -// Copyright (C) 2018, 2019 by via donau +// Copyright (C) 2018, 2019, 2020 by via donau // – Österreichische Wasserstraßen-Gesellschaft mbH // Software engineering by Intevation GmbH //
--- a/pkg/models/intservices.go Mon Mar 02 16:17:50 2020 +0100 +++ b/pkg/models/intservices.go Thu Mar 05 12:15:37 2020 +0100 @@ -27,21 +27,29 @@ const DatabaseScheme = "waterway" -type IntEntry struct { - Schema string `json:"schema"` - Name string `json:"name"` - SQL *string `json:"sql"` - KeyColumn *string `json:"keycolumn"` - SRS *string `json:"srs"` - Style bool `json:"style"` - WMS bool `json:"wms"` - WFS bool `json:"wfs"` -} +type ( + IntEntry struct { + Schema string `json:"schema"` + Name string `json:"name"` + SQL *string `json:"sql"` + KeyColumn *string `json:"keycolumn"` + SRS *string `json:"srs"` + Style bool `json:"style"` + WMS bool `json:"wms"` + WFS bool `json:"wfs"` + } -type IntServices struct { - entries []IntEntry - mu sync.Mutex -} + LayerGroup struct { + Name string `json:"name"` + Layers []string `json:"layers"` + } + + IntServices struct { + mu sync.Mutex + entries []IntEntry + layerGroups []LayerGroup + } +) const ( selectServicesSQL = ` @@ -53,21 +61,26 @@ WHERE schema = $1 ORDER by name` + selectGroupedLayersSQL = ` +SELECT group_name, name +FROM sys_admin.grouped_layers +ORDER BY group_name, ord` + selectStyleSQL = ` -SELECT XMLSERIALIZE(DOCUMENT style AS text) +SELECT style FROM sys_admin.published_services WHERE name = $1 AND schema = $2` updateStyleSQL = ` UPDATE sys_admin.published_services -SET style = XMLPARSE(DOCUMENT $1) +SET style = $1 WHERE name = $2 AND schema = $3` ) var InternalServices = &IntServices{} -func (e *IntEntry) LoadStyle() (string, error) { - var style string +func (e *IntEntry) LoadStyle() ([]byte, error) { + var style []byte ctx := context.Background() err := auth.RunAs(ctx, "sys_admin", func(conn *sql.Conn) error { @@ -79,7 +92,7 @@ return style, err } -func UpdateInternalStyle(req *http.Request, name, style string) error { +func UpdateInternalStyle(req *http.Request, name string, style []byte) error { return auth.RunAsSessionUser(req, func(conn *sql.Conn) error { _, err := conn.ExecContext( req.Context(), updateStyleSQL, @@ -91,6 +104,28 @@ }) } +func (ps *IntServices) LayerGroups() []LayerGroup { + ps.mu.Lock() + defer ps.mu.Unlock() + + if ps.entries == nil { + if err := ps.load(); err != nil { + log.Printf("error: %v\n", err) + return nil + } + } + + // To avoid races we simple make a deep copy. + // As we don't have such many of them it light weight enough for now. + groups := make([]LayerGroup, len(ps.layerGroups)) + for i := range groups { + layers := make([]string, len(ps.layerGroups[i].Layers)) + copy(layers, ps.layerGroups[i].Layers) + groups[i] = LayerGroup{Name: ps.layerGroups[i].Name, Layers: layers} + } + return groups +} + func (ps *IntServices) Find(name string) (string, bool) { ps.mu.Lock() defer ps.mu.Unlock() @@ -129,33 +164,63 @@ func (ps *IntServices) load() error { // make empty slice to prevent retry if slice is empty. ps.entries = []IntEntry{} + ps.layerGroups = []LayerGroup{} ctx := context.Background() - return auth.RunAs(ctx, "sys_admin", - func(conn *sql.Conn) error { - rows, err := conn.QueryContext( - ctx, selectServicesSQL, DatabaseScheme) - if err != nil { + + // Load the internal layers. + entries := func(conn *sql.Conn) error { + rows, err := conn.QueryContext( + ctx, selectServicesSQL, DatabaseScheme) + if err != nil { + return err + } + defer rows.Close() + for rows.Next() { + var entry IntEntry + if err := rows.Scan( + &entry.Schema, &entry.Name, + &entry.SQL, &entry.KeyColumn, &entry.SRS, &entry.Style, + &entry.WMS, &entry.WFS, + ); err != nil { return err } - defer rows.Close() - for rows.Next() { - var entry IntEntry - if err := rows.Scan( - &entry.Schema, &entry.Name, - &entry.SQL, &entry.KeyColumn, &entry.SRS, &entry.Style, - &entry.WMS, &entry.WFS, - ); err != nil { - return err - } - ps.entries = append(ps.entries, entry) + ps.entries = append(ps.entries, entry) + } + return rows.Err() + } + + // Load the layer groups. + groups := func(conn *sql.Conn) error { + rows, err := conn.QueryContext(ctx, selectGroupedLayersSQL) + if err != nil { + return err + } + defer rows.Close() + + for rows.Next() { + var group, layer string + if err := rows.Scan(&group, &layer); err != nil { + return err } - return rows.Err() - }) + if n := len(ps.layerGroups); n > 0 && ps.layerGroups[n-1].Name == group { + ps.layerGroups[n-1].Layers = append(ps.layerGroups[n-1].Layers, layer) + } else { + ps.layerGroups = append(ps.layerGroups, LayerGroup{ + Name: group, + Layers: []string{layer}, + }) + } + } + return rows.Err() + } + + return auth.RunAllAs(ctx, "sys_admin", entries, groups) } func (ps *IntServices) Invalidate() { ps.mu.Lock() ps.entries = nil + ps.layerGroups = nil ps.mu.Unlock() }
--- a/pkg/pgxutils/errors.go Mon Mar 02 16:17:50 2020 +0100 +++ b/pkg/pgxutils/errors.go Thu Mar 05 12:15:37 2020 +0100 @@ -98,6 +98,18 @@ return } } + switch err.TableName { + case "fairway_marks_bcnlat_dirimps", + "fairway_marks_daymar_dirimps", + "fairway_marks_notmrk_dirimps": + switch err.ConstraintName { + case "fairway_marks_bcnlat_dirimps_dirimp_fkey", + "fairway_marks_daymar_dirimps_dirimp_fkey", + "fairway_marks_notmrk_dirimps_dirimp_fkey": + m = "Invalid value for dirimp" + return + } + } } case uniqueViolation: switch err.SchemaName {
--- a/schema/default_sysconfig.sql Mon Mar 02 16:17:50 2020 +0100 +++ b/schema/default_sysconfig.sql Thu Mar 05 12:15:37 2020 +0100 @@ -96,6 +96,16 @@ ('waterway', 'waterway_area', NULL, NULL, NULL), ('waterway', 'waterway_profiles', NULL, NULL, NULL), ('waterway', 'fairway_dimensions', NULL, NULL, NULL), + ('waterway', 'fairway_marks_bcnlat', NULL, NULL, NULL), + ('waterway', 'fairway_marks_boycar', NULL, NULL, NULL), + ('waterway', 'fairway_marks_boylat', NULL, NULL, NULL), + ('waterway', 'fairway_marks_boysaw', NULL, NULL, NULL), + ('waterway', 'fairway_marks_boyspp', NULL, NULL, NULL), + ('waterway', 'fairway_marks_daymar', NULL, NULL, NULL), + ('waterway', 'fairway_marks_lights', NULL, NULL, NULL), + ('waterway', 'fairway_marks_rtpbcn', NULL, NULL, NULL), + ('waterway', 'fairway_marks_topmar', NULL, NULL, NULL), + ('waterway', 'fairway_marks_notmrk', NULL, NULL, NULL), -- GeoServer SQL views ('waterway', 'gauges_geoserver', 4326, 'isrs_code', $$ SELECT @@ -276,6 +286,23 @@ $$); -- +-- group layers +-- +INSERT INTO sys_admin.layer_groups VALUES ('fairway_marks'); + +INSERT INTO sys_admin.grouped_layers VALUES + ('fairway_marks', 'waterway', 'fairway_marks_boylat', 0), + ('fairway_marks', 'waterway', 'fairway_marks_boycar', 1), + ('fairway_marks', 'waterway', 'fairway_marks_bcnlat', 2), + ('fairway_marks', 'waterway', 'fairway_marks_boysaw', 3), + ('fairway_marks', 'waterway', 'fairway_marks_boyspp', 4), + ('fairway_marks', 'waterway', 'fairway_marks_daymar', 5), + ('fairway_marks', 'waterway', 'fairway_marks_lights', 6), + ('fairway_marks', 'waterway', 'fairway_marks_rtpbcn', 7), + ('fairway_marks', 'waterway', 'fairway_marks_topmar', 8), + ('fairway_marks', 'waterway', 'fairway_marks_notmrk', 9); + +-- -- Settings -- INSERT INTO sys_admin.system_config VALUES ('ecdis_wms_url', 'https://service.d4d-portal.info/wms/');
--- a/schema/gemma.sql Mon Mar 02 16:17:50 2020 +0100 +++ b/schema/gemma.sql Thu Mar 05 12:15:37 2020 +0100 @@ -288,8 +288,7 @@ -- TODO: Do we need name and/or definition from IENC feature catalogue? -- (see page 381 of edition 2.3) ); -INSERT INTO dirimps VALUES (1), (2), (3), (4); --- dirimp_5 left out because it cannot be used for waterway area +INSERT INTO dirimps VALUES (1), (2), (3), (4), (5); CREATE TABLE depth_references ( depth_reference varchar(4) PRIMARY KEY @@ -406,14 +405,28 @@ key_column varchar, -- SRID to be used with SQL view: srid int REFERENCES spatial_ref_sys, - -- SLD style document: - style xml CHECK(style IS DOCUMENT), + -- SLD style document or ZIP blob: + style bytea, as_wms boolean NOT NULL DEFAULT TRUE, as_wfs boolean NOT NULL DEFAULT TRUE, -- Either give a valid relation or a SQL statement: CHECK (to_regclass(schema || '.' || name) IS NOT NULL OR view_def IS NOT NULL) ) + + CREATE TABLE layer_groups ( + name varchar PRIMARY KEY + ) + + CREATE TABLE grouped_layers ( + group_name varchar REFERENCES layer_groups ON DELETE CASCADE, + schema varchar, + name varchar, + ord int NOT NULL DEFAULT 0, + PRIMARY KEY (group_name, schema, name), + UNIQUE (group_name, schema, name, ord), + FOREIGN KEY(schema, name) REFERENCES published_services + ) ; @@ -840,6 +853,343 @@ CHECK(measure_type = 'minimum guaranteed' OR value_lifetime IS NOT NULL) ) + + -- Attributes common to all fairway marks + CREATE TABLE fairway_marks ( + validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL) + CHECK (NOT isempty(validity)), + -- Last time an import job found this entry in a data source: + last_found timestamp with time zone NOT NULL DEFAULT current_timestamp, + geom geography(POINT, 4326) NOT NULL, + -- Attributes according to IENC Feature Catalogue: + datsta varchar, + datend varchar, + persta varchar, + perend varchar, + objnam varchar, + nobjnm varchar, + inform varchar, + ninfom varchar, + scamin int, + picrep varchar, + txtdsc varchar, + sordat varchar, + sorind varchar + ) + + -- Additional attributes for IENC features BCNLAT/bcnlat + CREATE TABLE fairway_marks_bcnlat ( + colour varchar, + colpat varchar, + condtn int, + bcnshp int, + catlam int + ) INHERITS (fairway_marks) + + CREATE TABLE fairway_marks_bcnlat_hydro ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY + ) INHERITS (fairway_marks_bcnlat) + -- Prevent identical entries using composite type comparison + -- (i.e. considering two NULL values in a field equal): + CREATE UNIQUE INDEX fairway_marks_bcnlat_hydro_distinct_rows + ON fairway_marks_bcnlat_hydro + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + colour, colpat, condtn, bcnshp, catlam, 0 + ) AS fairway_marks_bcnlat_hydro) + )) + CREATE INDEX fairway_marks_bcnlat_hydro_validity + ON fairway_marks_bcnlat_hydro USING GiST (validity) + + CREATE TABLE fairway_marks_bcnlat_ienc ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY + ) INHERITS (fairway_marks_bcnlat) + -- Prevent identical entries using composite type comparison + -- (i.e. considering two NULL values in a field equal): + CREATE UNIQUE INDEX fairway_marks_bcnlat_ienc_distinct_rows + ON fairway_marks_bcnlat_ienc + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + colour, colpat, condtn, bcnshp, catlam, 0 + ) AS fairway_marks_bcnlat_ienc) + )) + CREATE INDEX fairway_marks_bcnlat_ienc_validity + ON fairway_marks_bcnlat_ienc USING GiST (validity) + + CREATE TABLE fairway_marks_bcnlat_dirimps ( + fm_bcnlat_id int REFERENCES fairway_marks_bcnlat_ienc, + dirimp smallint REFERENCES dirimps, + PRIMARY KEY (fm_bcnlat_id, dirimp) + ) + + -- Additional attributes for IENC feature BOYCAR + CREATE TABLE fairway_marks_boycar ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + conrad int, + marsys int, + boyshp int, + catcam int + ) INHERITS (fairway_marks) + -- Prevent identical entries using composite type comparison + -- (i.e. considering two NULL values in a field equal): + CREATE UNIQUE INDEX fairway_marks_boycar_distinct_rows + ON fairway_marks_boycar + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, conrad, marsys, boyshp, catcam + ) AS fairway_marks_boycar) + )) + CREATE INDEX fairway_marks_boycar_validity + ON fairway_marks_boycar USING GiST (validity) + + -- Additional attributes for IENC feature BOYLAT + CREATE TABLE fairway_marks_boylat ( + colour varchar, + colpat varchar, + conrad int, + marsys int, + boyshp int, + catlam int + ) INHERITS (fairway_marks) + + CREATE TABLE fairway_marks_boylat_hydro ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY + ) INHERITS (fairway_marks_boylat) + -- Prevent identical entries using composite type comparison + -- (i.e. considering two NULL values in a field equal): + CREATE UNIQUE INDEX fairway_marks_boylat_hydro_distinct_rows + ON fairway_marks_boylat_hydro + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + colour, colpat, conrad, marsys, boyshp, catlam, 0 + ) AS fairway_marks_boylat_hydro) + )) + CREATE INDEX fairway_marks_boylat_hydro_validity + ON fairway_marks_boylat_hydro USING GiST (validity) + + CREATE TABLE fairway_marks_boylat_ienc ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY + ) INHERITS (fairway_marks_boylat) + -- Prevent identical entries using composite type comparison + -- (i.e. considering two NULL values in a field equal): + CREATE UNIQUE INDEX fairway_marks_boylat_ienc_distinct_rows + ON fairway_marks_boylat_ienc + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + colour, colpat, conrad, marsys, boyshp, catlam, 0 + ) AS fairway_marks_boylat_ienc) + )) + CREATE INDEX fairway_marks_boylat_ienc_validity + ON fairway_marks_boylat_ienc USING GiST (validity) + + -- Additional attributes for IENC feature BOYSAW + CREATE TABLE fairway_marks_boysaw ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + conrad int, + marsys int, + boyshp int + ) INHERITS (fairway_marks) + -- Prevent identical entries using composite type comparison + -- (i.e. considering two NULL values in a field equal): + CREATE UNIQUE INDEX fairway_marks_boysaw_distinct_rows + ON fairway_marks_boysaw + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, conrad, marsys, boyshp + ) AS fairway_marks_boysaw) + )) + CREATE INDEX fairway_marks_boysaw_validity + ON fairway_marks_boysaw USING GiST (validity) + + -- Additional attributes for IENC feature BOYSPP + CREATE TABLE fairway_marks_boyspp ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + conrad int, + marsys int, + boyshp int, + catspm varchar + ) INHERITS (fairway_marks) + -- Prevent identical entries using composite type comparison + -- (i.e. considering two NULL values in a field equal): + CREATE UNIQUE INDEX fairway_marks_boyspp_distinct_rows + ON fairway_marks_boyspp + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, conrad, marsys, boyshp, catspm + ) AS fairway_marks_boyspp) + )) + CREATE INDEX fairway_marks_boyspp_validity + ON fairway_marks_boyspp USING GiST (validity) + + -- Additional attributes for IENC features DAYMAR/daymar + CREATE TABLE fairway_marks_daymar ( + colour varchar, + colpat varchar, + condtn int, + topshp int + ) INHERITS (fairway_marks) + + CREATE TABLE fairway_marks_daymar_hydro ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY + ) INHERITS (fairway_marks_daymar) + -- Prevent identical entries using composite type comparison + -- (i.e. considering two NULL values in a field equal): + CREATE UNIQUE INDEX fairway_marks_daymar_hydro_distinct_rows + ON fairway_marks_daymar_hydro + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + colour, colpat, condtn, topshp, 0 + ) AS fairway_marks_daymar_hydro) + )) + CREATE INDEX fairway_marks_daymar_hydro_validity + ON fairway_marks_daymar_hydro USING GiST (validity) + + CREATE TABLE fairway_marks_daymar_ienc ( + orient double precision, + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY + ) INHERITS (fairway_marks_daymar) + -- Prevent identical entries using composite type comparison + -- (i.e. considering two NULL values in a field equal): + CREATE UNIQUE INDEX fairway_marks_daymar_ienc_distinct_rows + ON fairway_marks_daymar_ienc + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + colour, colpat, condtn, topshp, orient, 0 + ) AS fairway_marks_daymar_ienc) + )) + CREATE INDEX fairway_marks_daymar_ienc_validity + ON fairway_marks_daymar_ienc USING GiST (validity) + + CREATE TABLE fairway_marks_daymar_dirimps ( + fm_daymar_id int REFERENCES fairway_marks_daymar_ienc, + dirimp smallint REFERENCES dirimps, + PRIMARY KEY (fm_daymar_id, dirimp) + ) + + -- Additional attributes for IENC feature LIGHTS + CREATE TABLE fairway_marks_lights ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + condtn int, + orient double precision, + catlit varchar, + exclit int, + litchr int, + litvis varchar, + mltylt int, + sectr1 double precision, + sectr2 double precision, + siggrp varchar, + sigper double precision, + sigseq varchar, + status varchar + ) INHERITS (fairway_marks) + -- Prevent identical entries using composite type comparison + -- (i.e. considering two NULL values in a field equal): + CREATE UNIQUE INDEX fairway_marks_lights_distinct_rows + ON fairway_marks_lights + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, condtn, orient, catlit, exclit, litchr, litvis, + mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status + ) AS fairway_marks_lights) + )) + CREATE INDEX fairway_marks_lights_validity + ON fairway_marks_lights USING GiST (validity) + + -- Additional attributes for IENC feature RTPBCN + CREATE TABLE fairway_marks_rtpbcn ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + condtn int, + siggrp varchar, + catrtb int, + radwal varchar + ) INHERITS (fairway_marks) + -- Prevent identical entries using composite type comparison + -- (i.e. considering two NULL values in a field equal): + CREATE UNIQUE INDEX fairway_marks_rtpbcn_distinct_rows + ON fairway_marks_rtpbcn + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, condtn, siggrp, catrtb, radwal + ) AS fairway_marks_rtpbcn) + )) + CREATE INDEX fairway_marks_rtpbcn_validity + ON fairway_marks_rtpbcn USING GiST (validity) + + -- Additional attributes for IENC feature TOPMAR + CREATE TABLE fairway_marks_topmar ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + condtn int, + topshp int + ) INHERITS (fairway_marks) + -- Prevent identical entries using composite type comparison + -- (i.e. considering two NULL values in a field equal): + CREATE UNIQUE INDEX fairway_marks_topmar_distinct_rows + ON fairway_marks_topmar + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, condtn, topshp + ) AS fairway_marks_topmar) + )) + CREATE INDEX fairway_marks_topmar_validity + ON fairway_marks_topmar USING GiST (validity) + + -- Additional attributes for IENC feature NOTMRK + CREATE TABLE fairway_marks_notmrk ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + condtn int, + marsys int, + orient double precision, + status varchar, + addmrk varchar, + catnmk int, + disipd double precision, + disipu double precision, + disbk1 double precision, + disbk2 double precision, + fnctnm int, + bnkwtw int + ) INHERITS (fairway_marks) + -- Prevent identical entries using composite type comparison + -- (i.e. considering two NULL values in a field equal): + CREATE UNIQUE INDEX fairway_marks_notmrk_distinct_rows + ON fairway_marks_notmrk + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, condtn, marsys, orient, status, addmrk, catnmk, + disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw + ) AS fairway_marks_notmrk) + )) + CREATE INDEX fairway_marks_notmrk_validity + ON fairway_marks_notmrk USING GiST (validity) + + CREATE TABLE fairway_marks_notmrk_dirimps ( + fm_notmrk_id int REFERENCES fairway_marks_notmrk, + dirimp smallint REFERENCES dirimps, + PRIMARY KEY (fm_notmrk_id, dirimp) + ) ;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1317/01.migrate_styles.sql Thu Mar 05 12:15:37 2020 +0100 @@ -0,0 +1,4 @@ +ALTER TABLE sys_admin.published_services ADD COLUMN bin_blob bytea; +UPDATE sys_admin.published_services SET bin_blob = style::text::bytea WHERE style is NOT NULL; +ALTER TABLE sys_admin.published_services DROP COLUMN style; +ALTER TABLE sys_admin.published_services RENAME COLUMN bin_blob TO style;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1400/01.add_fairway_marks.sql Thu Mar 05 12:15:37 2020 +0100 @@ -0,0 +1,81 @@ +CREATE TABLE waterway.fairway_marks ( + geom geography(POINT, 4326) NOT NULL, + datsta varchar, + datend varchar, + persta varchar, + perend varchar, + objnam varchar, + nobjnm varchar, + inform varchar, + ninfom varchar, + scamin int, + picrep varchar, + txtdsc varchar, + sordat varchar, + sorind varchar +); + +CREATE TABLE waterway.fairway_marks_bcnlat ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + condtn int, + bcnshp int, + catlam int +) INHERITS (waterway.fairway_marks); +CREATE UNIQUE INDEX fairway_marks_bcnlat_distinct_rows + ON waterway.fairway_marks_bcnlat + ((CAST((geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, condtn, bcnshp, catlam + ) AS waterway.fairway_marks_bcnlat) + )); + +CREATE TABLE waterway.fairway_marks_bcnlat_dirimps ( + fm_bcnlat_id int REFERENCES waterway.fairway_marks_bcnlat, + dirimp smallint REFERENCES dirimps, + PRIMARY KEY (fm_bcnlat_id, dirimp) +); + + +CREATE TABLE waterway.fairway_marks_boycar ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + conrad int, + marsys int, + boyshp int, + catcam int +) INHERITS (waterway.fairway_marks); +CREATE UNIQUE INDEX fairway_marks_boycar_distinct_rows + ON waterway.fairway_marks_boycar + ((CAST((geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, conrad, marsys, boyshp, catcam + ) AS waterway.fairway_marks_boycar) + )); + +CREATE TABLE waterway.fairway_marks_boylat ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + conrad int, + marsys int, + boyshp int, + catlam int +) INHERITS (waterway.fairway_marks); +CREATE UNIQUE INDEX fairway_marks_boylat_distinct_rows + ON waterway.fairway_marks_boylat + ((CAST((geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, conrad, marsys, boyshp, catlam + ) AS waterway.fairway_marks_boylat) + )); + + +GRANT SELECT on ALL tables in schema waterway TO waterway_user ; +GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway + TO waterway_admin;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1401/01.add_fairway_marks_layers.sql Thu Mar 05 12:15:37 2020 +0100 @@ -0,0 +1,6 @@ +INSERT INTO sys_admin.published_services ( + schema, name, srid, key_column, view_def +) VALUES + ('waterway', 'fairway_marks_bcnlat', NULL, NULL, NULL), + ('waterway', 'fairway_marks_boycar', NULL, NULL, NULL), + ('waterway', 'fairway_marks_boylat', NULL, NULL, NULL);
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1402/01.group_layers.sql Thu Mar 05 12:15:37 2020 +0100 @@ -0,0 +1,15 @@ +CREATE TABLE sys_admin.layer_groups ( + name varchar PRIMARY KEY +); + +CREATE TABLE sys_admin.grouped_layers ( + group_name varchar REFERENCES sys_admin.layer_groups ON DELETE CASCADE, + schema varchar, + name varchar, + ord int NOT NULL DEFAULT 0, + PRIMARY KEY (group_name, schema, name), + UNIQUE (group_name, schema, name, ord), + FOREIGN KEY(schema, name) REFERENCES sys_admin.published_services +); + +GRANT SELECT ON sys_admin.layer_groups, sys_admin.grouped_layers TO sys_admin;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1402/02.fairwaymarks_group_layer.sql Thu Mar 05 12:15:37 2020 +0100 @@ -0,0 +1,6 @@ +INSERT INTO sys_admin.layer_groups VALUES ('fairway_marks'); + +INSERT INTO sys_admin.grouped_layers VALUES + ('fairway_marks', 'waterway', 'fairway_marks_boylat', 0), + ('fairway_marks', 'waterway', 'fairway_marks_boycar', 1), + ('fairway_marks', 'waterway', 'fairway_marks_bcnlat', 2);
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1403/01.add_dirimp_5.sql Thu Mar 05 12:15:37 2020 +0100 @@ -0,0 +1,1 @@ +INSERT INTO dirimps VALUES (5);
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1403/02.add_fairway_mark_types.sql Thu Mar 05 12:15:37 2020 +0100 @@ -0,0 +1,151 @@ +CREATE TABLE waterway.fairway_marks_boysaw ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + conrad int, + marsys int, + boyshp int +) INHERITS (waterway.fairway_marks); +CREATE UNIQUE INDEX fairway_marks_boysaw_distinct_rows + ON waterway.fairway_marks_boysaw + ((CAST((geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, conrad, marsys, boyshp + ) AS waterway.fairway_marks_boysaw) + )); + +CREATE TABLE waterway.fairway_marks_boyspp ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + conrad int, + marsys int, + boyshp int, + catspm varchar +) INHERITS (waterway.fairway_marks); +CREATE UNIQUE INDEX fairway_marks_boyspp_distinct_rows + ON waterway.fairway_marks_boyspp + ((CAST((geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, conrad, marsys, boyshp, catspm + ) AS waterway.fairway_marks_boyspp) + )); + +CREATE TABLE waterway.fairway_marks_daymar ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + condtn int, + topshp int, + orient double precision +) INHERITS (waterway.fairway_marks); +CREATE UNIQUE INDEX fairway_marks_daymar_distinct_rows + ON waterway.fairway_marks_daymar + ((CAST((geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, condtn, topshp, orient + ) AS waterway.fairway_marks_daymar) + )); + +CREATE TABLE waterway.fairway_marks_daymar_dirimps ( + fm_daymar_id int REFERENCES waterway.fairway_marks_daymar, + dirimp smallint REFERENCES dirimps, + PRIMARY KEY (fm_daymar_id, dirimp) +); + +CREATE TABLE waterway.fairway_marks_lights ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + condtn int, + orient double precision, + catlit varchar, + exclit int, + litchr int, + litvis varchar, + mltylt int, + sectr1 double precision, + sectr2 double precision, + siggrp varchar, + sigper double precision, + sigseq varchar, + status varchar +) INHERITS (waterway.fairway_marks); +CREATE UNIQUE INDEX fairway_marks_lights_distinct_rows + ON waterway.fairway_marks_lights + ((CAST((geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, condtn, orient, catlit, exclit, litchr, litvis, + mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status + ) AS waterway.fairway_marks_lights) + )); + +CREATE TABLE waterway.fairway_marks_rtpbcn ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + condtn int, + siggrp varchar, + catrtb int, + radwal varchar +) INHERITS (waterway.fairway_marks); +CREATE UNIQUE INDEX fairway_marks_rtpbcn_distinct_rows + ON waterway.fairway_marks_rtpbcn + ((CAST((geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, condtn, siggrp, catrtb, radwal + ) AS waterway.fairway_marks_rtpbcn) + )); + +CREATE TABLE waterway.fairway_marks_topmar ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + condtn int, + topshp int +) INHERITS (waterway.fairway_marks); +CREATE UNIQUE INDEX fairway_marks_topmar_distinct_rows + ON waterway.fairway_marks_topmar + ((CAST((geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, condtn, topshp + ) AS waterway.fairway_marks_topmar) + )); + +CREATE TABLE waterway.fairway_marks_notmrk ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + condtn int, + marsys int, + orient double precision, + status varchar, + addmrk varchar, + catnmk int, + disipd double precision, + disipu double precision, + disbk1 double precision, + disbk2 double precision, + fnctnm int, + bnkwtw int +) INHERITS (waterway.fairway_marks); +CREATE UNIQUE INDEX fairway_marks_notmrk_distinct_rows + ON waterway.fairway_marks_notmrk + ((CAST((geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, condtn, marsys, orient, status, addmrk, catnmk, + disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw + ) AS waterway.fairway_marks_notmrk) + )); + +CREATE TABLE waterway.fairway_marks_notmrk_dirimps ( + fm_notmrk_id int REFERENCES waterway.fairway_marks_notmrk, + dirimp smallint REFERENCES dirimps, + PRIMARY KEY (fm_notmrk_id, dirimp) +); + +GRANT SELECT on ALL tables in schema waterway TO waterway_user ; +GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway + TO waterway_admin;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1404/01.add_grouped_fm_layers.sql Thu Mar 05 12:15:37 2020 +0100 @@ -0,0 +1,19 @@ +INSERT INTO sys_admin.published_services ( + schema, name, srid, key_column, view_def +) VALUES + ('waterway', 'fairway_marks_boysaw', NULL, NULL, NULL), + ('waterway', 'fairway_marks_boyspp', NULL, NULL, NULL), + ('waterway', 'fairway_marks_daymar', NULL, NULL, NULL), + ('waterway', 'fairway_marks_lights', NULL, NULL, NULL), + ('waterway', 'fairway_marks_rtpbcn', NULL, NULL, NULL), + ('waterway', 'fairway_marks_topmar', NULL, NULL, NULL), + ('waterway', 'fairway_marks_notmrk', NULL, NULL, NULL); + +INSERT INTO sys_admin.grouped_layers VALUES + ('fairway_marks', 'waterway', 'fairway_marks_boysaw', 3), + ('fairway_marks', 'waterway', 'fairway_marks_boyspp', 4), + ('fairway_marks', 'waterway', 'fairway_marks_daymar', 5), + ('fairway_marks', 'waterway', 'fairway_marks_lights', 6), + ('fairway_marks', 'waterway', 'fairway_marks_rtpbcn', 7), + ('fairway_marks', 'waterway', 'fairway_marks_topmar', 8), + ('fairway_marks', 'waterway', 'fairway_marks_notmrk', 9);
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1405/01.add_fm_validity.sql Thu Mar 05 12:15:37 2020 +0100 @@ -0,0 +1,51 @@ +CREATE TABLE waterway.fairway_marks_new ( + validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL) + CHECK (NOT isempty(validity)), + last_found timestamp with time zone NOT NULL DEFAULT current_timestamp, + LIKE waterway.fairway_marks +); + +DO LANGUAGE plpgsql +$$ +DECLARE table_suffix varchar; +BEGIN + /* Re-write tables inheriting from fairway_marks to include new columns + in correct position */ + FOREACH table_suffix IN ARRAY ARRAY[ + 'bcnlat', 'boycar', 'boylat', 'boysaw', 'boyspp', + 'daymar', 'lights', 'rtpbcn', 'topmar', 'notmrk'] + LOOP + EXECUTE format('CREATE TABLE waterway.fairway_marks_%s_new ' + '(LIKE waterway.fairway_marks_%1$s INCLUDING ALL) ' + 'INHERITS (waterway.fairway_marks_new)', table_suffix); + /* Drop index referring to wrong columns. See + https://www.postgresql.org/message-id/flat/16272-6e32da020e9a9381%40postgresql.org + */ + EXECUTE format('DROP INDEX waterway.fairway_marks_%s_new_row_idx', + table_suffix); + EXECUTE format('INSERT INTO waterway.fairway_marks_%s_new ' + 'SELECT tstzrange(current_timestamp, NULL), current_timestamp, * ' + 'FROM waterway.fairway_marks_%1$s', table_suffix); + EXECUTE format('DROP TABLE waterway.fairway_marks_%s CASCADE', + table_suffix); + EXECUTE format('ALTER TABLE waterway.fairway_marks_%s_new ' + 'RENAME TO fairway_marks_%1$s', table_suffix); + END LOOP; + + /* Recreate foreign key constraints */ + FOREACH table_suffix IN ARRAY ARRAY['bcnlat', 'daymar', 'notmrk'] + LOOP + EXECUTE format('ALTER TABLE waterway.fairway_marks_%1$s_dirimps ' + 'ADD FOREIGN KEY (fm_%1$s_id) ' + 'REFERENCES waterway.fairway_marks_%1$s', table_suffix); + END LOOP; +END; +$$; + +DROP TABLE waterway.fairway_marks; +ALTER TABLE waterway.fairway_marks_new RENAME TO fairway_marks; + +-- Restore GRANTs +GRANT SELECT on ALL tables in schema waterway TO waterway_user ; +GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway + TO waterway_admin;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1405/02.recreate_fm_indexes.sql Thu Mar 05 12:15:37 2020 +0100 @@ -0,0 +1,92 @@ +CREATE UNIQUE INDEX fairway_marks_bcnlat_distinct_rows + ON waterway.fairway_marks_bcnlat + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, condtn, bcnshp, catlam + ) AS waterway.fairway_marks_bcnlat) + )); + +CREATE UNIQUE INDEX fairway_marks_boycar_distinct_rows + ON waterway.fairway_marks_boycar + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, conrad, marsys, boyshp, catcam + ) AS waterway.fairway_marks_boycar) + )); + +CREATE UNIQUE INDEX fairway_marks_boylat_distinct_rows + ON waterway.fairway_marks_boylat + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, conrad, marsys, boyshp, catlam + ) AS waterway.fairway_marks_boylat) + )); + + +CREATE UNIQUE INDEX fairway_marks_boysaw_distinct_rows + ON waterway.fairway_marks_boysaw + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, conrad, marsys, boyshp + ) AS waterway.fairway_marks_boysaw) + )); + +CREATE UNIQUE INDEX fairway_marks_boyspp_distinct_rows + ON waterway.fairway_marks_boyspp + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, conrad, marsys, boyshp, catspm + ) AS waterway.fairway_marks_boyspp) + )); + +CREATE UNIQUE INDEX fairway_marks_daymar_distinct_rows + ON waterway.fairway_marks_daymar + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, condtn, topshp, orient + ) AS waterway.fairway_marks_daymar) + )); + +CREATE UNIQUE INDEX fairway_marks_lights_distinct_rows + ON waterway.fairway_marks_lights + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, condtn, orient, catlit, exclit, litchr, litvis, + mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status + ) AS waterway.fairway_marks_lights) + )); + +CREATE UNIQUE INDEX fairway_marks_rtpbcn_distinct_rows + ON waterway.fairway_marks_rtpbcn + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, condtn, siggrp, catrtb, radwal + ) AS waterway.fairway_marks_rtpbcn) + )); + +CREATE UNIQUE INDEX fairway_marks_topmar_distinct_rows + ON waterway.fairway_marks_topmar + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, condtn, topshp + ) AS waterway.fairway_marks_topmar) + )); + +CREATE UNIQUE INDEX fairway_marks_notmrk_distinct_rows + ON waterway.fairway_marks_notmrk + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, condtn, marsys, orient, status, addmrk, catnmk, + disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw + ) AS waterway.fairway_marks_notmrk) + ));
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1406/01.distinguish_bcnlat_hydro_ienc.sql Thu Mar 05 12:15:37 2020 +0100 @@ -0,0 +1,58 @@ +CREATE TABLE waterway.fairway_marks_bcnlat_new ( + colour varchar, + colpat varchar, + condtn int, + bcnshp int, + catlam int +) INHERITS (waterway.fairway_marks); + +CREATE TABLE waterway.fairway_marks_bcnlat_hydro ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY +) INHERITS (waterway.fairway_marks_bcnlat_new); +CREATE UNIQUE INDEX fairway_marks_bcnlat_hydro_distinct_rows + ON waterway.fairway_marks_bcnlat_hydro + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + colour, colpat, condtn, bcnshp, catlam, 0 + ) AS waterway.fairway_marks_bcnlat_hydro) + )); + +CREATE TABLE waterway.fairway_marks_bcnlat_ienc ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY +) INHERITS (waterway.fairway_marks_bcnlat_new); +CREATE UNIQUE INDEX fairway_marks_bcnlat_ienc_distinct_rows + ON waterway.fairway_marks_bcnlat_ienc + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + colour, colpat, condtn, bcnshp, catlam, 0 + ) AS waterway.fairway_marks_bcnlat_ienc) + )); + +-- Assume all features are IENC features, since there is currently no known +-- data source for HYDRO features +INSERT INTO waterway.fairway_marks_bcnlat_ienc + SELECT validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + colour, colpat, condtn, bcnshp, catlam, id + FROM waterway.fairway_marks_bcnlat; +SELECT setval( + pg_get_serial_sequence('waterway.fairway_marks_bcnlat_ienc', 'id'), + max(id)) + FROM waterway.fairway_marks_bcnlat_ienc; + +-- Let foreign key constraint point to new table +ALTER TABLE waterway.fairway_marks_bcnlat_dirimps + DROP CONSTRAINT fairway_marks_bcnlat_dirimps_fm_bcnlat_id_fkey, + ADD FOREIGN KEY (fm_bcnlat_id) + REFERENCES waterway.fairway_marks_bcnlat_ienc; + +-- Finally +DROP TABLE waterway.fairway_marks_bcnlat; +ALTER TABLE waterway.fairway_marks_bcnlat_new RENAME TO fairway_marks_bcnlat; + +GRANT SELECT on ALL tables in schema waterway TO waterway_user; +GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway + TO waterway_admin;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1407/01.distinguish_boylat_hydro_ienc.sql Thu Mar 05 12:15:37 2020 +0100 @@ -0,0 +1,50 @@ +CREATE TABLE waterway.fairway_marks_boylat_new ( + colour varchar, + colpat varchar, + conrad int, + marsys int, + boyshp int, + catlam int +) INHERITS (waterway.fairway_marks); + +CREATE TABLE waterway.fairway_marks_boylat_hydro ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY +) INHERITS (waterway.fairway_marks_boylat_new); +CREATE UNIQUE INDEX fairway_marks_boylat_hydro_distinct_rows + ON waterway.fairway_marks_boylat_hydro + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + colour, colpat, conrad, marsys, boyshp, catlam, 0 + ) AS waterway.fairway_marks_boylat_hydro) + )); + +CREATE TABLE waterway.fairway_marks_boylat_ienc ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY +) INHERITS (waterway.fairway_marks_boylat_new); +CREATE UNIQUE INDEX fairway_marks_boylat_ienc_distinct_rows + ON waterway.fairway_marks_boylat_ienc + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + colour, colpat, conrad, marsys, boyshp, catlam, 0 + ) AS waterway.fairway_marks_boylat_ienc) + )); + +-- Assume all features are IENC features, since there are currently only +-- very few features in the data source for HYDRO features +INSERT INTO waterway.fairway_marks_boylat_ienc + OVERRIDING USER VALUE + SELECT validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + colour, colpat, conrad, marsys, boyshp, catlam, id + FROM waterway.fairway_marks_boylat; + +-- Finally +DROP TABLE waterway.fairway_marks_boylat; +ALTER TABLE waterway.fairway_marks_boylat_new RENAME TO fairway_marks_boylat; + +GRANT SELECT on ALL tables in schema waterway TO waterway_user; +GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway + TO waterway_admin;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1408/01.distinguish_daymar_hydro_ienc.sql Thu Mar 05 12:15:37 2020 +0100 @@ -0,0 +1,69 @@ +CREATE TABLE waterway.fairway_marks_daymar_new ( + colour varchar, + colpat varchar, + condtn int, + topshp int +) INHERITS (waterway.fairway_marks); + +CREATE TABLE waterway.fairway_marks_daymar_hydro ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY +) INHERITS (waterway.fairway_marks_daymar_new); +CREATE UNIQUE INDEX fairway_marks_daymar_hydro_distinct_rows + ON waterway.fairway_marks_daymar_hydro + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + colour, colpat, condtn, topshp, 0 + ) AS waterway.fairway_marks_daymar_hydro) + )); + +CREATE TABLE waterway.fairway_marks_daymar_ienc ( + orient double precision, + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY +) INHERITS (waterway.fairway_marks_daymar_new); +CREATE UNIQUE INDEX fairway_marks_daymar_ienc_distinct_rows + ON waterway.fairway_marks_daymar_ienc + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + colour, colpat, condtn, topshp, orient, 0 + ) AS waterway.fairway_marks_daymar_ienc) + )); + +-- Assume all features not being definitely IENC features are HYDRO features +INSERT INTO waterway.fairway_marks_daymar_hydro + OVERRIDING USER VALUE + SELECT validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + colour, colpat, condtn, topshp, id + FROM waterway.fairway_marks_daymar + WHERE orient IS NULL AND id NOT IN( + SELECT fm_daymar_id FROM waterway.fairway_marks_daymar_dirimps); + +INSERT INTO waterway.fairway_marks_daymar_ienc + SELECT validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + colour, colpat, condtn, topshp, orient, id + FROM waterway.fairway_marks_daymar + WHERE orient IS NOT NULL OR id IN( + SELECT fm_daymar_id FROM waterway.fairway_marks_daymar_dirimps); +SELECT setval( + pg_get_serial_sequence('waterway.fairway_marks_daymar_ienc', 'id'), + max(id)) + FROM waterway.fairway_marks_daymar_ienc; + +-- Let foreign key constraint point to new table +ALTER TABLE waterway.fairway_marks_daymar_dirimps + DROP CONSTRAINT fairway_marks_daymar_dirimps_fm_daymar_id_fkey, + ADD FOREIGN KEY (fm_daymar_id) + REFERENCES waterway.fairway_marks_daymar_ienc; + +-- Finally +DROP TABLE waterway.fairway_marks_daymar; +ALTER TABLE waterway.fairway_marks_daymar_new RENAME TO fairway_marks_daymar; + +GRANT SELECT on ALL tables in schema waterway TO waterway_user; +GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway + TO waterway_admin;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1409/01.add_fm_validity_indexes.sql Thu Mar 05 12:15:37 2020 +0100 @@ -0,0 +1,17 @@ +DO LANGUAGE plpgsql +$$ +DECLARE table_suffix varchar; +BEGIN + FOREACH table_suffix IN ARRAY ARRAY[ + 'bcnlat_hydro', 'bcnlat_ienc', 'boycar', + 'boylat_hydro', 'boylat_ienc', 'boysaw', + 'boyspp', 'daymar_hydro', 'daymar_ienc', + 'lights', 'rtpbcn', 'topmar', 'notmrk'] + LOOP + EXECUTE format( + 'CREATE INDEX fairway_marks_%1$s_validity ' + 'ON waterway.fairway_marks_%1$s USING GiST (validity)', + table_suffix); + END LOOP; +END; +$$;
--- a/schema/version.sql Mon Mar 02 16:17:50 2020 +0100 +++ b/schema/version.sql Thu Mar 05 12:15:37 2020 +0100 @@ -1,1 +1,1 @@ -INSERT INTO gemma_schema_version(version) VALUES (1316); +INSERT INTO gemma_schema_version(version) VALUES (1409);
--- a/style-templates/upload-styles.sh Mon Mar 02 16:17:50 2020 +0100 +++ b/style-templates/upload-styles.sh Thu Mar 05 12:15:37 2020 +0100 @@ -19,14 +19,18 @@ usage() { cat <<EOF -$ME [OPTION]... +$ME [OPTION]... [input-file]... -Upload map styles to gemma. +Upload map styles to gemma. Uses either the given input files or all files +in the directory where this script resides. Input files must have the name +of a published layer in gemma and the suffix .sld-template. Options: -P, --g_port=GPORT connect to gemma server at GPORT. Default 8000. -g, --g_host=GHOST connect to gemma server on GHOST. Default "localhost". -u, --g_user=GUSER login to gemma as user GUSER. Default "sophie". + -d, --extra_dir=DIR search DIR in addition to the default styles directory. + This option can be specified multiple times. --g_pw=GPW password for GUSER. Default "so2Phie4". --help display this help and exit @@ -45,12 +49,13 @@ g_host="localhost" g_user="sophie" g_pw="so2Phie4" +declare -a extra_dirs # Parse options: OPTS=`getopt \ - -l help,g_port:,g_host:,g_user:,g_pw: \ - -o P:g:u: -n "$ME" -- "$@"` + -l help,g_port:,g_host:,g_user:,g_pw:,extra_dir: \ + -o P:g:u:d: -n "$ME" -- "$@"` [ $? -eq 0 ] || { usage ; exit 1 ; } eval set -- "$OPTS" @@ -73,6 +78,10 @@ g_pw="$2" shift 2 ;; + --extra_dir|-d) + extra_dirs+=("$2") + shift 2 + ;; --help) { usage ; exit 0 ; } ;; @@ -83,10 +92,6 @@ esac done -if [ $# != 0 ] ; then - { usage ; exit 23 ; } -fi - # Main ------------------------------------------------------------ # Login to gemma server @@ -108,11 +113,18 @@ if jq -e 'any(. == "sys_admin")' <<<"$roles" > /dev/null then echo "== Configuring geoserver styles" >&2 - for style in $(basename -s .sld-template $(ls $datadir/*.sld-template)) + if [ $# -gt 0 ]; then + files=("$@") + else + files=($(find "$datadir" "${extra_dirs[@]}" \ + -name "*.sld-template" -or -name "*.zip")) + fi + for file in ${files[@]} do - echo "uploading $style ..." + style=$(basename $(basename "$file" .zip) .sld-template) + echo "uploading ${style} ..." curl -f -s -S -H "X-Gemma-Auth:${token}" -X POST \ - -F style=@"${datadir}/${style}.sld-template" \ + -F style=@"${file}" \ "http://${g_host}:${g_port}/api/geo/style/${style}" done echo 'done.'