Mercurial > gemma

changeset 2317:8a8680e70d2e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Cleanup schema for reference water levels Remove duplicate lookup table and leave an out-commented foreign key constraint to make clear what should be referenced. The removed comment in surveys.go mostly became obsolete and was otherwise trying to explain SQL basics.
author Tom Gottfried <tom@intevation.de>
date Mon, 18 Feb 2019 18:45:12 +0100
parents bc43cd047ead
children 06c4e57435f1
files pkg/controllers/surveys.go pkg/imports/wg.go schema/gemma.sql
diffstat 3 files changed, 9 insertions(+), 21 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/controllers/surveys.go	Mon Feb 18 16:41:09 2019 +0100
+++ b/pkg/controllers/surveys.go	Mon Feb 18 18:45:12 2019 +0100
@@ -24,15 +24,6 @@
 )
 
 const (
-	// Use the same logic as in schema/gemma.sql to find out
-	// which is the reference gauge and which from its values is meant.
-	// The LEFT for the JOIN is necessary because we still want to list
-	// the surveys even if the corresponding value cannot be found because the
-	// depth_reference and reference_water_level strings do not match.
-	//
-	// As this information could be vary from survey entry to survey,
-	// in theory even for the same bottleneck
-	// it seems to be the right place to return it from the /surveys endpoint.
 	listSurveysSQL = `
 SELECT
   s.bottleneck_id,
@@ -49,7 +40,7 @@
 	ON bg.id = s.bottleneck_id
   )
 LEFT JOIN waterway.gauges_reference_water_levels AS r
-ON s.depth_reference = r.reference_water_level AND bg.location = r.gauge_id
+ON s.depth_reference = r.depth_reference AND bg.location = r.gauge_id
 WHERE bg.objnam=$1`
 )
 
--- a/pkg/imports/wg.go	Mon Feb 18 16:41:09 2019 +0100
+++ b/pkg/imports/wg.go	Mon Feb 18 18:45:12 2019 +0100
@@ -116,7 +116,7 @@
 	insertReferenceWaterLevelsSQL = `
 INSERT INTO waterway.gauges_reference_water_levels (
   gauge_id,
-  reference_water_level,
+  depth_reference,
   value
 ) VALUES (
   ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int),
--- a/schema/gemma.sql	Mon Feb 18 16:41:09 2019 +0100
+++ b/schema/gemma.sql	Mon Feb 18 18:45:12 2019 +0100
@@ -120,10 +120,6 @@
     --   (which would mean a model differing a bit from RIS-Index ideas)
 );
 
-CREATE TABLE reference_water_levels (
-    reference_water_level varchar(20) PRIMARY KEY
-);
-
 CREATE TABLE catdis (
     catdis smallint PRIMARY KEY
     -- TODO: Do we need name and/or definition from IENC feature catalogue?
@@ -255,9 +251,10 @@
 
     CREATE TABLE gauges_reference_water_levels (
         gauge_id isrs NOT NULL REFERENCES gauges,
-        reference_water_level varchar(20)
-            NOT NULL REFERENCES reference_water_levels,
-        PRIMARY KEY (gauge_id, reference_water_level),
+        -- Omit foreign key constraint to be able to store not standards
+        -- (RIS, NtS) compliant names, too:
+        depth_reference varchar NOT NULL, -- REFERENCES depth_references,
+        PRIMARY KEY (gauge_id, depth_reference),
         value int NOT NULL
     )
 
@@ -511,11 +508,11 @@
         fal.critical AS fa_critical
     FROM bottlenecks b, gauges g,
         (SELECT gauge_id, value FROM gauges_reference_water_levels
-           WHERE reference_water_level='LDC') rwl_ldc,
+           WHERE depth_reference = 'LDC') rwl_ldc,
         (SELECT gauge_id, value FROM gauges_reference_water_levels
-           WHERE reference_water_level='MW') rwl_mw,
+           WHERE depth_reference = 'MW') rwl_mw,
         (SELECT gauge_id, value FROM gauges_reference_water_levels
-           WHERE reference_water_level='HDC') rwl_hdc
+           WHERE depth_reference = 'HDC') rwl_hdc
         LEFT JOIN LATERAL (
             SELECT bottleneck_id,date_info,critical
             FROM  fairway_availability_latest