Mercurial > gemma
changeset 4976:ff965141d085
Merged fairway-marks-import back into default branch.
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Tue, 03 Mar 2020 15:33:04 +0100 |
| parents | b65898de11ad (current diff) b599ad718971 (diff) |
| children | 4bf6cde2d996 |
| files | |
| diffstat | 42 files changed, 2496 insertions(+), 142 deletions(-) [+] |
line wrap: on
line diff
--- a/client/src/components/importconfiguration/Import.vue Fri Feb 21 10:08:32 2020 +0100 +++ b/client/src/components/importconfiguration/Import.vue Tue Mar 03 15:33:04 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: '140px;' }, + { 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:140px" 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 Fri Feb 21 10:08:32 2020 +0100 +++ b/client/src/components/importconfiguration/ImportDetails.vue Tue Mar 03 15:33:04 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 Fri Feb 21 10:08:32 2020 +0100 +++ b/client/src/components/importconfiguration/ScheduledImports.vue Tue Mar 03 15:33:04 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 Fri Feb 21 10:08:32 2020 +0100 +++ b/client/src/components/importconfiguration/types/Fairwaymarks.vue Tue Mar 03 15:33:04 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 Fri Feb 21 10:08:32 2020 +0100 +++ b/client/src/components/importoverview/ImportOverview.vue Tue Mar 03 15:33:04 2020 +0100 @@ -78,10 +78,10 @@ <UITableHeader :columns="[ { id: 'id', title: `${idLabel}`, width: '70px' }, - { id: 'kind', title: `${kindLabel}`, width: '50px' }, - { id: 'enqueued', title: `${enqueuedLabel}`, width: '138px' }, + { id: 'kind', title: `${kindLabel}`, width: '55px' }, + { 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 Fri Feb 21 10:08:32 2020 +0100 +++ b/client/src/components/importoverview/LogEntry.vue Tue Mar 03 15:33:04 2020 +0100 @@ -9,16 +9,16 @@ /> {{ entry.id }} </div> - <div style="width: 50px;" class="table-cell center"> - {{ entry.kind.toUpperCase() }} + <div style="width: 55px;" 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 Fri Feb 21 10:08:32 2020 +0100 +++ b/client/src/components/layers/Layers.vue Tue Mar 03 15:33:04 2020 +0100 @@ -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/map/layers.js Fri Feb 21 10:08:32 2020 +0100 +++ b/client/src/components/map/layers.js Tue Mar 03 15:33:04 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 Fri Feb 21 10:08:32 2020 +0100 +++ b/client/src/store/importschedule.js Tue Mar 03 15:33:04 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/go.mod Fri Feb 21 10:08:32 2020 +0100 +++ b/go.mod Tue Mar 03 15:33:04 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 Fri Feb 21 10:08:32 2020 +0100 +++ b/go.sum Tue Mar 03 15:33:04 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 Fri Feb 21 10:08:32 2020 +0100 +++ b/pkg/auth/opendb.go Tue Mar 03 15:33:04 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 Fri Feb 21 10:08:32 2020 +0100 +++ b/pkg/controllers/geostyling.go Tue Mar 03 15:33:04 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 Fri Feb 21 10:08:32 2020 +0100 +++ b/pkg/controllers/publish.go Tue Mar 03 15:33:04 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 Fri Feb 21 10:08:32 2020 +0100 +++ b/pkg/controllers/routes.go Tue Mar 03 15:33:04 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 Fri Feb 21 10:08:32 2020 +0100 +++ b/pkg/geoserver/boot.go Tue Mar 03 15:33:04 2020 +0100 @@ -14,6 +14,7 @@ package geoserver import ( + "archive/zip" "bytes" "encoding/json" "encoding/xml" @@ -244,6 +245,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 +263,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 { @@ -384,6 +385,134 @@ 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)) + + 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 + } + } + 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"` + } + ) + + 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) + 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 deleteWorkspace() error { // Should we delete our workspace first? @@ -478,19 +607,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 +691,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 +742,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 +817,7 @@ ensureWorkspace, ensureDataStore, ensureFeatures, + ensureLayerGroups, ensureStyles, } { if err := ensure(); err != nil {
--- a/pkg/imports/dsr.go Fri Feb 21 10:08:32 2020 +0100 +++ b/pkg/imports/dsr.go Tue Mar 03 15:33:04 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 Tue Mar 03 15:33:04 2020 +0100 @@ -0,0 +1,599 @@ +// 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, + &PointWFSJobCreator{ + 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"), + func() interface{} { return new(bcnlatHydroProperties) }, + ), + }) + + RegisterJobCreator(BCNLATIENCJobKind, + &PointWFSJobCreator{ + 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"), + func() interface{} { return new(bcnlatIencProperties) }, + ), + }) + + RegisterJobCreator(BOYLATHYDROJobKind, + &PointWFSJobCreator{ + 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"), + func() interface{} { return new(boylatHydroProperties) }, + ), + }) + + RegisterJobCreator(BOYLATIENCJobKind, + &PointWFSJobCreator{ + 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"), + func() interface{} { return new(boylatIencProperties) }, + ), + }) + + RegisterJobCreator(BOYCARJobKind, + &PointWFSJobCreator{ + description: "fairway marks boycar", + depends: [2][]string{{"fairway_marks_boycar"}, {}}, + newConsumer: newSQLConsumer( + prepareStmnts( + createInsertFMSQL("boycar", + "colour", "colpat", "conrad", + "marsys", "boyshp", "catcam"), + ), + consume, + createInvalidation("boycar"), + func() interface{} { return new(boycarProperties) }, + ), + }) + + RegisterJobCreator(BOYSAWJobKind, + &PointWFSJobCreator{ + description: "fairway marks boysaw", + depends: [2][]string{{"fairway_marks_boysaw"}, {}}, + newConsumer: newSQLConsumer( + prepareStmnts( + createInsertFMSQL("boysaw", + "colour", "colpat", "conrad", "marsys", "boyshp"), + ), + consume, + createInvalidation("boysaw"), + func() interface{} { return new(boysawProperties) }, + ), + }) + + RegisterJobCreator(BOYSPPJobKind, + &PointWFSJobCreator{ + description: "fairway marks boyspp", + depends: [2][]string{{"fairway_marks_boyspp"}, {}}, + newConsumer: newSQLConsumer( + prepareStmnts( + createInsertFMSQL("boyspp", + "colour", "colpat", "conrad", + "marsys", "boyshp", "catspm"), + ), + consume, + createInvalidation("boyspp"), + func() interface{} { return new(boysppProperties) }, + ), + }) + + RegisterJobCreator(DAYMARHYDROJobKind, + &PointWFSJobCreator{ + 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"), + func() interface{} { return new(daymarHydroProperties) }, + ), + }) + + RegisterJobCreator(DAYMARIENCJobKind, + &PointWFSJobCreator{ + 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"), + func() interface{} { return new(daymarIencProperties) }, + ), + }) + + RegisterJobCreator(LIGHTSJobKind, + &PointWFSJobCreator{ + 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"), + func() interface{} { return new(lightsProperties) }, + ), + }) + + RegisterJobCreator(NOTMRKJobKind, + &PointWFSJobCreator{ + 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"), + func() interface{} { return new(notmrkProperties) }, + ), + }) + + RegisterJobCreator(RTPBCNJobKind, + &PointWFSJobCreator{ + description: "fairway marks rtpbcn", + depends: [2][]string{{"fairway_marks_rtpbcn"}, {}}, + newConsumer: newSQLConsumer( + prepareStmnts( + createInsertFMSQL("rtpbcn", + "condtn", "siggrp", "catrtb", "radwal"), + ), + consume, + createInvalidation("rtpbcn"), + func() interface{} { return new(rtpbcnProperties) }, + ), + }) + + RegisterJobCreator(TOPMARJobKind, + &PointWFSJobCreator{ + description: "fairway marks topmar", + depends: [2][]string{{"fairway_marks_topmar"}, {}}, + newConsumer: newSQLConsumer( + prepareStmnts( + createInsertFMSQL("topmar", + "colour", "colpat", "condtn", "topshp"), + ), + consume, + createInvalidation("topmar"), + 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(*SQLPointConsumer) error { + + invalidateFairwayMarksSQL := fmt.Sprintf(invalidateFairwayMarksSQLtmpl, fmType) + + return func(spc *SQLPointConsumer) 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 *SQLPointConsumer, 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 *SQLPointConsumer, + points pointSlice, + properties interface{}, + epsg int, +) error { + var fmid int64 + err := spc.savepoint(func() error { + return spc.stmts[0].QueryRowContext( + spc.ctx, + append( + []interface{}{ + points.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 Fri Feb 21 10:08:32 2020 +0100 +++ b/pkg/imports/misc.go Tue Mar 03 15:33:04 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 Fri Feb 21 10:08:32 2020 +0100 +++ b/pkg/imports/modelconvert.go Tue Mar 03 15:33:04 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/pointwfs.go Tue Mar 03 15:33:04 2020 +0100 @@ -0,0 +1,345 @@ +// 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 ( + WFSPointConsumer interface { + Commit() error + Rollback() error + + NewProperties() interface{} + Consume(points pointSlice, properties interface{}, epsg int) error + } + + PointWFSJobCreator struct { + description string + depends [2][]string + + newConsumer func(context.Context, *sql.Conn, Feedback) (WFSPointConsumer, error) + } + + PointWFSJob struct { + models.WFSImport + creator *PointWFSJobCreator + } +) + +func (pwjc *PointWFSJobCreator) Description() string { + return pwjc.description +} + +func (pwjc *PointWFSJobCreator) Depends() [2][]string { + return pwjc.depends +} + +func (*PointWFSJobCreator) AutoAccept() bool { + return true +} + +// StageDone is a NOP for WFS imports. +func (*PointWFSJobCreator) StageDone(context.Context, *sql.Tx, int64) error { + return nil +} + +func (pwjc *PointWFSJobCreator) Create() Job { + return &PointWFSJob{creator: pwjc} +} + +// Description gives a short info about relevant facts of this import. +func (pwj *PointWFSJob) Description() (string, error) { + return pwj.URL + "|" + pwj.FeatureType, nil +} + +// CleanUp for WFS imports is a NOP. +func (*PointWFSJob) CleanUp() error { + return nil +} + +func (pwj *PointWFSJob) Do( + ctx context.Context, + importID int64, + conn *sql.Conn, + feedback Feedback, +) (interface{}, error) { + + start := time.Now() + + feedback.Info("Import %s", pwj.creator.Description()) + + feedback.Info("Loading capabilities from %s", pwj.URL) + caps, err := wfs.GetCapabilities(pwj.URL) + if err != nil { + feedback.Error("Loading capabilities failed: %v", err) + return nil, err + } + + ft := caps.FindFeatureType(pwj.FeatureType) + if ft == nil { + return nil, fmt.Errorf("unknown feature type '%s'", pwj.FeatureType) + } + + feedback.Info("Found feature type '%s'", pwj.FeatureType) + + epsg, err := wfs.CRSToEPSG(ft.DefaultCRS) + if err != nil { + feedback.Error("Unsupported CRS: '%s'", ft.DefaultCRS) + return nil, err + } + + if nilString(pwj.SortBy) != "" { + feedback.Info("Features will be sorted by '%s'", *pwj.SortBy) + } + + dl, err := wfs.GetFeatures(caps, pwj.FeatureType, nilString(pwj.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 := pwj.creator.newConsumer(ctx, conn, feedback) + if err != nil { + return nil, err + } + defer consumer.Rollback() + + if err := dl.Download(nilString(pwj.User), nilString(pwj.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 + } + + props := consumer.NewProperties() + if err := json.Unmarshal(*feature.Properties, props); err != nil { + badProperties++ + continue + } + + switch feature.Geometry.Type { + case "Point": + var p pointSlice + if err := json.Unmarshal(*feature.Geometry.Coordinates, &p); err != nil { + return err + } + + err := consumer.Consume(p, props, epsg) + switch { + case err == ErrFeatureDuplicated: + dupes++ + case err == ErrFeatureIgnored: + // be silent + case err != nil: + return err + default: + features++ + } + + default: + 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 ( + SQLPointConsumer struct { + ctx context.Context + tx *sql.Tx + feedback Feedback + newProperties func() interface{} + consume func(*SQLPointConsumer, pointSlice, interface{}, int) error + preCommit func(*SQLPointConsumer) error + savepoint func(func() error) error + stmts []*sql.Stmt + } +) + +func (spc *SQLPointConsumer) Rollback() error { + if tx := spc.tx; tx != nil { + spc.releaseStmts() + spc.tx = nil + spc.ctx = nil + return tx.Rollback() + } + return nil +} + +func (spc *SQLPointConsumer) Commit() error { + var err error + if tx := spc.tx; tx != nil { + if spc.preCommit != nil { + err = spc.preCommit(spc) + } + spc.releaseStmts() + spc.tx = nil + spc.ctx = nil + if err2 := tx.Commit(); err2 != nil { + // A real error on commit overrules the first. + err = err2 + } + } + return err +} + +func (spc *SQLPointConsumer) NewProperties() interface{} { + return spc.newProperties() +} + +func (spc *SQLPointConsumer) Consume( + points pointSlice, + properties interface{}, + epsg int, +) error { + return spc.consume(spc, points, properties, epsg) +} + +func newSQLConsumer( + init func(*SQLPointConsumer) error, + consume func(*SQLPointConsumer, pointSlice, interface{}, int) error, + preCommit func(*SQLPointConsumer) error, + newProperties func() interface{}, + +) func(context.Context, *sql.Conn, Feedback) (WFSPointConsumer, error) { + return func(ctx context.Context, conn *sql.Conn, feedback Feedback) (WFSPointConsumer, error) { + tx, err := conn.BeginTx(ctx, nil) + if err != nil { + return nil, err + } + spc := &SQLPointConsumer{ + ctx: ctx, + tx: tx, + feedback: feedback, + newProperties: newProperties, + consume: consume, + preCommit: preCommit, + savepoint: Savepoint(ctx, tx, "feature"), + } + if err := init(spc); err != nil { + tx.Rollback() + return nil, err + } + return spc, nil + } +} + +func (spc *SQLPointConsumer) releaseStmts() { + for i := len(spc.stmts); i > 0; i-- { + spc.stmts[i-1].Close() + spc.stmts[i-1] = nil + } + spc.stmts = nil +} + +func prepareStmnts(queries ...string) func(*SQLPointConsumer) error { + return func(spc *SQLPointConsumer) error { + for _, query := range queries { + stmt, err := spc.tx.PrepareContext(spc.ctx, query) + if err != nil { + return err + } + spc.stmts = append(spc.stmts, stmt) + } + return nil + } +}
--- a/pkg/models/imports.go Fri Feb 21 10:08:32 2020 +0100 +++ b/pkg/models/imports.go Tue Mar 03 15:33:04 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 Fri Feb 21 10:08:32 2020 +0100 +++ b/pkg/models/intservices.go Tue Mar 03 15:33:04 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 Fri Feb 21 10:08:32 2020 +0100 +++ b/pkg/pgxutils/errors.go Tue Mar 03 15:33:04 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 Fri Feb 21 10:08:32 2020 +0100 +++ b/schema/default_sysconfig.sql Tue Mar 03 15:33:04 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 Fri Feb 21 10:08:32 2020 +0100 +++ b/schema/gemma.sql Tue Mar 03 15:33:04 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 Tue Mar 03 15:33:04 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 Tue Mar 03 15:33:04 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 Tue Mar 03 15:33:04 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 Tue Mar 03 15:33:04 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 Tue Mar 03 15:33:04 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 Tue Mar 03 15:33:04 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 Tue Mar 03 15:33:04 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 Tue Mar 03 15:33:04 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 Tue Mar 03 15:33:04 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 Tue Mar 03 15:33:04 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 Tue Mar 03 15:33:04 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 Tue Mar 03 15:33:04 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 Tue Mar 03 15:33:04 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 Tue Mar 03 15:33:04 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 Fri Feb 21 10:08:32 2020 +0100 +++ b/schema/version.sql Tue Mar 03 15:33:04 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 Fri Feb 21 10:08:32 2020 +0100 +++ b/style-templates/upload-styles.sh Tue Mar 03 15:33:04 2020 +0100 @@ -19,9 +19,11 @@ 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. @@ -83,10 +85,6 @@ esac done -if [ $# != 0 ] ; then - { usage ; exit 23 ; } -fi - # Main ------------------------------------------------------------ # Login to gemma server @@ -108,11 +106,17 @@ 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 . -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.'