Mercurial > gemma

changeset 4938:9f9d72a1d398 fairway-marks-import

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Save some typos and wrong parameter counts when adding new fairway mark types
author Tom Gottfried <tom@intevation.de>
date Mon, 17 Feb 2020 14:47:54 +0100
parents 40da1b8aba01
children 39b67b910204 c4d84be3a476
files pkg/imports/fm.go pkg/imports/fm_bcnlat.go pkg/imports/fm_boycar.go pkg/imports/fm_boylat.go
diffstat 4 files changed, 74 insertions(+), 134 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/imports/fm.go	Mon Feb 17 12:24:00 2020 +0100
+++ b/pkg/imports/fm.go	Mon Feb 17 14:47:54 2020 +0100
@@ -19,6 +19,7 @@
 	"encoding/json"
 	"fmt"
 	"io"
+	"strings"
 	"time"
 
 	"gemma.intevation.de/gemma/pkg/wfs"
@@ -58,6 +59,62 @@
 	Sorind *string `json:"hydro_sorind"`
 }
 
+const (
+	// Format string to be completed with type and additional attributes
+	insertFairwayMarkSQL = `
+WITH a AS (
+  SELECT users.current_user_area_utm() AS a
+)
+INSERT INTO waterway.fairway_marks_%s (
+  geom,
+  datsta,
+  datend,
+  persta,
+  perend,
+  objnam,
+  nobjnm,
+  inform,
+  ninfom,
+  scamin,
+  picrep,
+  txtdsc,
+  sordat,
+  sorind,
+  %s
+)
+SELECT newfm, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15,
+    %s
+  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)))
+ON CONFLICT (
+  CAST((geom,
+      datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
+      scamin, picrep, txtdsc, sordat, sorind,
+      0, %[2]s
+    ) AS waterway.fairway_marks_%[1]s)
+  )
+  DO NOTHING
+RETURNING id
+`
+)
+
+// Create INSERT statement for specific fairway marks type
+func getFMInsertSQL(fmType string, attributes []string) string {
+	attNums := "$16"
+	for i := 1; i < len(attributes); i++ {
+		attNums += fmt.Sprintf(",$%d", 16+i)
+	}
+
+	return fmt.Sprintf(
+		insertFairwayMarkSQL,
+		fmType,
+		strings.Join(attributes, ","),
+		attNums,
+	)
+}
+
 // Common operation of FM imports to get features from WFS service
 func getFMFeatures(
 	ctx context.Context,
--- a/pkg/imports/fm_bcnlat.go	Mon Feb 17 12:24:00 2020 +0100
+++ b/pkg/imports/fm_bcnlat.go	Mon Feb 17 14:47:54 2020 +0100
@@ -80,47 +80,6 @@
 }
 
 const (
-	insertBCNLATSQL = `
-with a as (
-  select users.current_user_area_utm() AS a
-)
-INSERT INTO waterway.fairway_marks_bcnlat (
-  geom,
-  datsta,
-  datend,
-  persta,
-  perend,
-  objnam,
-  nobjnm,
-  inform,
-  ninfom,
-  scamin,
-  picrep,
-  txtdsc,
-  sordat,
-  sorind,
-  colour,
-  colpat,
-  condtn,
-  bcnshp,
-  catlam
-)
-SELECT newfm, $3, $4, $5, $6, $7, $8, $9,
-    $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20
-  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)))
-ON CONFLICT (
-  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)
-  )
-  DO NOTHING
-RETURNING id
-`
 	insertDirimpSQL = `
 INSERT INTO waterway.fairway_marks_bcnlat_dirimps (fm_bcnlat_id, dirimp)
   VALUES ($1, $2)
@@ -150,7 +109,11 @@
 
 			feedback.Info("Store fairway marks of type BCNLAT/bcnlat")
 
-			insertStmt, err := tx.PrepareContext(ctx, insertBCNLATSQL)
+			insertStmt, err := tx.PrepareContext(
+				ctx,
+				getFMInsertSQL("bcnlat", []string{
+					"colour", "colpat", "condtn", "bcnshp", "catlam"}),
+			)
 			if err != nil {
 				return
 			}
--- a/pkg/imports/fm_boycar.go	Mon Feb 17 12:24:00 2020 +0100
+++ b/pkg/imports/fm_boycar.go	Mon Feb 17 14:47:54 2020 +0100
@@ -77,51 +77,6 @@
 	props *boycarProperties
 }
 
-const (
-	insertBOYCARSQL = `
-with a as (
-  select users.current_user_area_utm() AS a
-)
-INSERT INTO waterway.fairway_marks_boycar (
-  geom,
-  datsta,
-  datend,
-  persta,
-  perend,
-  objnam,
-  nobjnm,
-  inform,
-  ninfom,
-  scamin,
-  picrep,
-  txtdsc,
-  sordat,
-  sorind,
-  colour,
-  colpat,
-  conrad,
-  marsys,
-  boyshp,
-  catcam
-)
-SELECT newfm, $3, $4, $5, $6, $7, $8, $9,
-    $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21
-  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)))
-ON CONFLICT (
-  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)
-  )
-  DO NOTHING
-RETURNING id
-`
-)
-
 // Do executes the actual import.
 func (fm *Boycar) Do(
 	ctx context.Context,
@@ -145,7 +100,12 @@
 
 			feedback.Info("Store fairway marks of type BOYCAR")
 
-			insertStmt, err := tx.PrepareContext(ctx, insertBOYCARSQL)
+			insertStmt, err := tx.PrepareContext(
+				ctx,
+				getFMInsertSQL("boycar", []string{
+					"colour", "colpat", "conrad",
+					"marsys", "boyshp", "catcam"}),
+			)
 			if err != nil {
 				return
 			}
--- a/pkg/imports/fm_boylat.go	Mon Feb 17 12:24:00 2020 +0100
+++ b/pkg/imports/fm_boylat.go	Mon Feb 17 14:47:54 2020 +0100
@@ -79,51 +79,6 @@
 	props *boylatProperties
 }
 
-const (
-	insertBOYLATSQL = `
-with a as (
-  select users.current_user_area_utm() AS a
-)
-INSERT INTO waterway.fairway_marks_boylat (
-  geom,
-  datsta,
-  datend,
-  persta,
-  perend,
-  objnam,
-  nobjnm,
-  inform,
-  ninfom,
-  scamin,
-  picrep,
-  txtdsc,
-  sordat,
-  sorind,
-  colour,
-  colpat,
-  conrad,
-  marsys,
-  boyshp,
-  catlam
-)
-SELECT newfm, $3, $4, $5, $6, $7, $8, $9,
-    $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21
-  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)))
-ON CONFLICT (
-  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)
-  )
-  DO NOTHING
-RETURNING id
-`
-)
-
 // Do executes the actual import.
 func (fm *Boylat) Do(
 	ctx context.Context,
@@ -147,7 +102,12 @@
 
 			feedback.Info("Store fairway marks of type BOYLAT")
 
-			insertStmt, err := tx.PrepareContext(ctx, insertBOYLATSQL)
+			insertStmt, err := tx.PrepareContext(
+				ctx,
+				getFMInsertSQL("boylat", []string{
+					"colour", "colpat", "conrad",
+					"marsys", "boyshp", "catlam"}),
+			)
 			if err != nil {
 				return
 			}