Mercurial > gemma

changeset 1572:056a86b24be2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Made bottleneck primary key an int. Attention: This may break something!
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 12 Dec 2018 19:21:02 +0100
parents b3f2d43f43e4
children 761e8744e1f3
files pkg/controllers/surveys.go pkg/imports/bn.go pkg/imports/sr.go pkg/models/sr.go schema/gemma.sql
diffstat 5 files changed, 73 insertions(+), 26 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/controllers/surveys.go	Wed Dec 12 17:21:11 2018 +0100
+++ b/pkg/controllers/surveys.go	Wed Dec 12 19:21:02 2018 +0100
@@ -23,10 +23,13 @@
 )
 
 const (
-	listSurveysSQL = `SELECT s.bottleneck_id,
-                                 s.date_info::text
-FROM waterway.bottlenecks b, waterway.sounding_results s
-WHERE b.objnam=$1 AND s.bottleneck_id = b.bottleneck_id;`
+	listSurveysSQL = `
+SELECT
+  s.bottleneck_id,
+  s.date_info::text
+FROM waterway.bottlenecks b JOIN waterway.sounding_results s
+ON b.id = s.bottleneck_id
+WHERE b.objnam=$1`
 )
 
 func listSurveys(
--- a/pkg/imports/bn.go	Wed Dec 12 17:21:11 2018 +0100
+++ b/pkg/imports/bn.go	Wed Dec 12 19:21:02 2018 +0100
@@ -32,7 +32,12 @@
 
 const BNJobKind JobKind = "bn"
 
-const insertSQL = `INSERT INTO waterway.bottlenecks (
+const (
+	hasBottleneckSQL = `
+SELECT true FROM waterway.bottlenecks WHERE bottleneck_id = $1`
+
+	insertSQL = `
+INSERT INTO waterway.bottlenecks (
   bottleneck_id,
   fk_g_fid,
   objnam,
@@ -60,7 +65,9 @@
   $11,
   $12,
   $13
-) ON CONFLICT (bottleneck_id) DO NOTHING`
+)
+RETURNING id`
+)
 
 type bnJobCreator struct{}
 
@@ -156,9 +163,35 @@
 	}
 	defer tx.Rollback()
 
+	hasStmt, err := tx.PrepareContext(ctx, hasBottleneckSQL)
+	if err != nil {
+		return nil, err
+	}
+	defer hasStmt.Close()
+	insertStmt, err := tx.PrepareContext(ctx, insertSQL)
+	if err != nil {
+		return nil, err
+	}
+	defer insertStmt.Close()
+
+	var nids []int64
+
 	start := time.Now()
-	for i := range bns {
-		bn := bns[i]
+
+nextBN:
+	for _, bn := range bns {
+
+		var found bool
+		err := hasStmt.QueryRowContext(ctx, bn.Bottleneck_id).Scan(&found)
+		switch {
+		case err == sql.ErrNoRows:
+			// This is good.
+		case err != nil:
+			return nil, err
+		case found:
+			continue nextBN
+		}
+
 		rb, lb := splitRBLB(bn.Rb_lb)
 
 		var limiting, country string
@@ -171,7 +204,10 @@
 			country = string(*bn.Responsible_country)
 		}
 
-		tx.Exec(insertSQL,
+		var nid int64
+
+		err = insertStmt.QueryRowContext(
+			ctx,
 			bn.Bottleneck_id,
 			bn.Fk_g_fid,
 			bn.OBJNAM,
@@ -184,24 +220,31 @@
 			limiting,
 			bn.Date_Info,
 			bn.Source,
-		)
-		feedback.Info("Insert \"%s\" into database", bn.OBJNAM)
-		//TODO: Track for potential later removal? Bottlenecks have an string PK, track wants int64.
-		// if err = track(ctx, tx, importID, "waterway.bottlenecks", bn.Bottleneck_id); err != nil {
-		// 	return nil, err
-		// }
+		).Scan(&nid)
+		if err != nil {
+			return nil, err
+		}
+		nids = append(nids, nid)
+		feedback.Info("Inserted '%s'into database", bn.OBJNAM)
+		if err := track(ctx, tx, importID, "waterway.bottlenecks", nid); err != nil {
+			return nil, err
+		}
+	}
+	if len(nids) == 0 {
+		feedback.Error("No new bottlenecks found")
+		return nil, errors.New("No new bottlenecks found")
 	}
 
-	feedback.Info("Storing %d bottlenecks took %s", len(bns), time.Since(start))
+	feedback.Info("Storing %d bottlenecks took %s", len(nids), time.Since(start))
 	if err = tx.Commit(); err == nil {
 		feedback.Info("Import of bottlenecks was successful")
 	}
 
-	// TODO: needs to be filled.
+	// TODO: needs to be filled more useful.
 	summary := struct {
-		BottleneckCount int `json:"bottleneckCount"`
+		Bottlenecks []int64 `json:"bottlenecks"`
 	}{
-		BottleneckCount: len(bns),
+		Bottlenecks: nids,
 	}
 	return &summary, err
 }
--- a/pkg/imports/sr.go	Wed Dec 12 17:21:11 2018 +0100
+++ b/pkg/imports/sr.go	Wed Dec 12 19:21:02 2018 +0100
@@ -114,7 +114,7 @@
   point_cloud,
   area
 ) VALUES (
-  (SELECT bottleneck_id from waterway.bottlenecks where objnam = $1),
+  (SELECT id from waterway.bottlenecks where objnam = $1),
   $2::date,
   $3,
   ST_Transform(ST_GeomFromWKB($4, $6::integer), 4326)::geography,
--- a/pkg/models/sr.go	Wed Dec 12 17:21:11 2018 +0100
+++ b/pkg/models/sr.go	Wed Dec 12 19:21:02 2018 +0100
@@ -46,7 +46,7 @@
 
 	checkBottleneckDateUniqueSQL = `
 SELECT true FROM waterway.sounding_results sr JOIN
-  waterway.bottlenecks bn ON sr.bottleneck_id = bn.bottleneck_id
+  waterway.bottlenecks bn ON sr.bottleneck_id = bn.id
 WHERE bn.objnam = $1 AND sr.date_info = $2`
 )
 
--- a/schema/gemma.sql	Wed Dec 12 17:21:11 2018 +0100
+++ b/schema/gemma.sql	Wed Dec 12 19:21:02 2018 +0100
@@ -402,7 +402,8 @@
     -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and
     -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL)
     CREATE TABLE bottlenecks (
-        bottleneck_id varchar PRIMARY KEY,
+        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
+        bottleneck_id varchar UNIQUE NOT NULL,
         fk_g_fid isrs NOT NULL REFERENCES gauges,
         -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
         objnam varchar,
@@ -431,7 +432,7 @@
         FOR EACH ROW EXECUTE PROCEDURE update_date_info()
 
     CREATE TABLE bottlenecks_riverbed_materials (
-        bottleneck_id varchar REFERENCES bottlenecks,
+        bottleneck_id int REFERENCES bottlenecks(id),
         riverbed varchar REFERENCES riverbed_materials,
         -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3
         PRIMARY KEY (bottleneck_id, riverbed)
@@ -439,7 +440,7 @@
 
     CREATE TABLE sounding_results (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
-        bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
+        bottleneck_id int NOT NULL REFERENCES bottlenecks(id),
         date_info date NOT NULL,
         UNIQUE (bottleneck_id, date_info),
         area geography(POLYGON, 4326) NOT NULL,
@@ -476,7 +477,7 @@
     CREATE TABLE fairway_availability (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
         position_code char(2) REFERENCES position_codes,
-        bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
+        bottleneck_id int NOT NULL REFERENCES bottlenecks(id),
         surdat date NOT NULL,
         UNIQUE (bottleneck_id, surdat),
         -- additional_data xml -- Currently not relevant for GEMMA
@@ -539,7 +540,7 @@
     FROM waterway.bottlenecks bn LEFT JOIN (
       SELECT bottleneck_id, max(date_info) AS current FROM
       waterway.sounding_results
-      GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.bottleneck_id
+      GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id
     ORDER BY objnam
 
     CREATE TABLE import_configuration (