Mercurial > gemma

comparison pkg/controllers/surveys.go @ 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 8132434f4093
children ec6163c6687d
comparison
equal deleted inserted replaced
2316:bc43cd047ead 2317:8a8680e70d2e
22 "gemma.intevation.de/gemma/pkg/models" 22 "gemma.intevation.de/gemma/pkg/models"
23 "github.com/gorilla/mux" 23 "github.com/gorilla/mux"
24 ) 24 )
25 25
26 const ( 26 const (
27 // Use the same logic as in schema/gemma.sql to find out
28 // which is the reference gauge and which from its values is meant.
29 // The LEFT for the JOIN is necessary because we still want to list
30 // the surveys even if the corresponding value cannot be found because the
31 // depth_reference and reference_water_level strings do not match.
32 //
33 // As this information could be vary from survey entry to survey,
34 // in theory even for the same bottleneck
35 // it seems to be the right place to return it from the /surveys endpoint.
36 listSurveysSQL = ` 27 listSurveysSQL = `
37 SELECT 28 SELECT
38 s.bottleneck_id, 29 s.bottleneck_id,
39 s.date_info::text, 30 s.date_info::text,
40 s.depth_reference, 31 s.depth_reference,
47 ) AS bg 38 ) AS bg
48 JOIN waterway.sounding_results AS s 39 JOIN waterway.sounding_results AS s
49 ON bg.id = s.bottleneck_id 40 ON bg.id = s.bottleneck_id
50 ) 41 )
51 LEFT JOIN waterway.gauges_reference_water_levels AS r 42 LEFT JOIN waterway.gauges_reference_water_levels AS r
52 ON s.depth_reference = r.reference_water_level AND bg.location = r.gauge_id 43 ON s.depth_reference = r.depth_reference AND bg.location = r.gauge_id
53 WHERE bg.objnam=$1` 44 WHERE bg.objnam=$1`
54 ) 45 )
55 46
56 func listSurveys( 47 func listSurveys(
57 _ interface{}, 48 _ interface{},