Mercurial > gemma

comparison pkg/imports/agm.go @ 3302:ec6163c6687d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
'Historicise' gauges on import Gauge data sets will be updated or a new version will be inserted depending on temporal validity and a timestamp marking the last update in the RIS-Index of a data set. The trigger on date_info is removed because the value is actually an attribut coming from the RIS-Index. Gauge measurements and predictions are associated to the version with matching temporal validity. Bottlenecks are always associated to the actual version of the gauge, although this might change as soon as bottlenecks are 'historicised', too.
author Tom Gottfried <tom@intevation.de>
date Thu, 16 May 2019 18:41:43 +0200
parents 831193935739
children 45a629a3a8b8
comparison
equal deleted inserted replaced
3301:6514b943654e 3302:ec6163c6687d
73 ), 73 ),
74 to_delete AS ( 74 to_delete AS (
75 SELECT o.id AS id 75 SELECT o.id AS id
76 FROM waterway.gauge_measurements o 76 FROM waterway.gauge_measurements o
77 JOIN waterway.gauge_measurements n 77 JOIN waterway.gauge_measurements n
78 ON n.fk_gauge_id = o.fk_gauge_id AND n.measure_date = o.measure_date 78 USING (location, validity, measure_date)
79 WHERE n.id IN (SELECT key FROM staged) 79 WHERE n.id IN (SELECT key FROM staged)
80 AND o.id NOT IN (SELECT key FROM staged) 80 AND o.id NOT IN (SELECT key FROM staged)
81 ) 81 )
82 DELETE FROM waterway.gauge_measurements WHERE id IN (SELECT id from to_delete)` 82 DELETE FROM waterway.gauge_measurements WHERE id IN (SELECT id from to_delete)`
83 83
157 water_level, 157 water_level,
158 date_info, 158 date_info,
159 source_organization 159 source_organization
160 FROM waterway.gauge_measurements 160 FROM waterway.gauge_measurements
161 WHERE 161 WHERE
162 fk_gauge_id = ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int) AND 162 location
163 measure_date = $6 AND staging_done` 163 = ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int)
164 AND measure_date = $6
165 AND staging_done
166 `
164 167
165 agmInsertSQL = ` 168 agmInsertSQL = `
166 INSERT INTO waterway.gauge_measurements ( 169 INSERT INTO waterway.gauge_measurements (
167 fk_gauge_id, 170 location,
171 validity,
168 measure_date, 172 measure_date,
169 country_code, 173 country_code,
170 sender, 174 sender,
171 language_code, 175 language_code,
172 date_issue, 176 date_issue,
173 reference_code, 177 reference_code,
174 water_level, 178 water_level,
175 date_info, 179 date_info,
176 source_organization, 180 source_organization,
177 staging_done 181 staging_done
178 ) VALUES( 182 ) VALUES (
179 ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int), 183 ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int),
184 (SELECT validity FROM waterway.gauges
185 WHERE location
186 = ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int)
187 AND validity @> CAST($6 AS timestamp with time zone)),
180 $6, 188 $6,
181 $7, 189 $7,
182 $8, 190 $8,
183 $9, 191 $9,
184 $10, 192 $10,