Mercurial > gemma
changeset 2317:8a8680e70d2e
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