Mercurial > gemma

comparison pkg/imports/bn.go @ 4110:861760675497 request_hist_bns

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged default
author Sascha Wilde <wilde@intevation.de>
date Tue, 30 Jul 2019 11:30:31 +0200
parents 3d2f02c16765 af2b20d6c921
children f39d20427e89
comparison
equal deleted inserted replaced
4102:3d2f02c16765 4110:861760675497
54 (SELECT b FROM bounds ORDER BY b USING >~ FETCH FIRST ROW ONLY)) AS r) 54 (SELECT b FROM bounds ORDER BY b USING >~ FETCH FIRST ROW ONLY)) AS r)
55 INSERT INTO waterway.bottlenecks ( 55 INSERT INTO waterway.bottlenecks (
56 bottleneck_id, 56 bottleneck_id,
57 validity, 57 validity,
58 gauge_location, 58 gauge_location,
59 gauge_validity,
60 objnam, 59 objnam,
61 nobjnm, 60 nobjnm,
62 stretch, 61 stretch,
63 area, 62 area,
64 rb, 63 rb,
66 responsible_country, 65 responsible_country,
67 revisiting_time, 66 revisiting_time,
68 limiting, 67 limiting,
69 date_info, 68 date_info,
70 source_organization 69 source_organization
71 ) SELECT 70 ) VALUES (
72 $1, 71 $1,
73 validity * $2, -- intersections with gauge validity ranges 72 $2::tstzrange,
74 location, 73 isrs_fromText($3),
75 validity,
76 $4, 74 $4,
77 $5, 75 $5,
78 (SELECT r FROM r), 76 (SELECT r FROM r),
79 ISRSrange_area( 77 ISRSrange_area(
80 ISRSrange_axis((SELECT r FROM r), 78 ISRSrange_axis((SELECT r FROM r),
86 $10, 84 $10,
87 $11, 85 $11,
88 $12, 86 $12,
89 $13, 87 $13,
90 $14 88 $14
91 FROM waterway.gauges 89 )
92 WHERE location = isrs_fromText($3) AND validity && $2
93 RETURNING id 90 RETURNING id
94 ` 91 `
95 92
96 updateBottleneckSQL = ` 93 updateBottleneckSQL = `
97 WITH 94 WITH
101 (SELECT b FROM bounds ORDER BY b USING >~ FETCH FIRST ROW ONLY)) AS r) 98 (SELECT b FROM bounds ORDER BY b USING >~ FETCH FIRST ROW ONLY)) AS r)
102 UPDATE waterway.bottlenecks SET ( 99 UPDATE waterway.bottlenecks SET (
103 bottleneck_id, 100 bottleneck_id,
104 validity, 101 validity,
105 gauge_location, 102 gauge_location,
106 gauge_validity,
107 objnam, 103 objnam,
108 nobjnm, 104 nobjnm,
109 stretch, 105 stretch,
110 area, 106 area,
111 rb, 107 rb,
113 responsible_country, 109 responsible_country,
114 revisiting_time, 110 revisiting_time,
115 limiting, 111 limiting,
116 date_info, 112 date_info,
117 source_organization 113 source_organization
118 ) = ( SELECT 114 ) = (
119 $2, 115 $2,
120 validity * $3, -- intersections with gauge validity ranges 116 $3::tstzrange,
121 location, 117 isrs_fromText($4),
122 validity,
123 $5, 118 $5,
124 $6, 119 $6,
125 (SELECT r FROM r), 120 (SELECT r FROM r),
126 ISRSrange_area( 121 ISRSrange_area(
127 ISRSrange_axis((SELECT r FROM r), 122 ISRSrange_axis((SELECT r FROM r),
133 $11, 128 $11,
134 $12::smallint, 129 $12::smallint,
135 $13, 130 $13,
136 $14::timestamptz, 131 $14::timestamptz,
137 $15 132 $15
138 FROM waterway.gauges 133 )
139 WHERE location = isrs_fromText($4) AND validity && $3 )
140 WHERE id=$1 134 WHERE id=$1
141 RETURNING id 135 RETURNING id
142 ` 136 `
143 137
144 findExactMatchBottleneckSQL = ` 138 findExactMatchBottleneckSQL = `
150 SELECT id FROM waterway.bottlenecks 144 SELECT id FROM waterway.bottlenecks
151 WHERE ( 145 WHERE (
152 bottleneck_id, 146 bottleneck_id,
153 validity, 147 validity,
154 gauge_location, 148 gauge_location,
155 gauge_validity,
156 objnam, 149 objnam,
157 nobjnm, 150 nobjnm,
158 stretch, 151 stretch,
159 rb, 152 rb,
160 lb, 153 lb,
164 date_info, 157 date_info,
165 source_organization, 158 source_organization,
166 staging_done 159 staging_done
167 ) = ( SELECT 160 ) = ( SELECT
168 $1, 161 $1,
169 validity * $2, -- intersections with gauge validity ranges 162 $2::tstzrange,
170 location, 163 isrs_fromText($3),
171 validity,
172 $4, 164 $4,
173 $5, 165 $5,
174 (SELECT r FROM r), 166 (SELECT r FROM r),
175 $8, 167 $8,
176 $9, 168 $9,
178 $11::smallint, 170 $11::smallint,
179 $12, 171 $12,
180 $13::timestamptz, 172 $13::timestamptz,
181 $14, 173 $14,
182 true 174 true
183 FROM waterway.gauges
184 WHERE location = isrs_fromText($3) AND validity && $2
185 ) 175 )
186 ` 176 `
187 177
188 findMatchBottleneckSQL = ` 178 findMatchBottleneckSQL = `
189 SELECT id FROM waterway.bottlenecks 179 SELECT id FROM waterway.bottlenecks
190 WHERE ( 180 WHERE (
191 bottleneck_id, 181 bottleneck_id,
192 validity, 182 validity,
193 staging_done 183 staging_done
194 ) = ( SELECT 184 ) = (
195 $1, 185 $1,
196 validity * $2, -- intersections with gauge validity ranges 186 $2::tstzrange,
197 true 187 true
198 FROM waterway.gauges
199 WHERE location = isrs_fromText($3) AND validity && $2
200 ) 188 )
201 ` 189 `
190 // FIXME: Is this still neede wtih the new simplified historization
191 // model? My intuition is: no it isn't and should be removed, but we
192 // should double check before doing so... [sw]
193 //
202 // Alignment with gauge validity might have generated new entries 194 // Alignment with gauge validity might have generated new entries
203 // for the same time range. Thus, remove the old ones 195 // for the same time range. Thus, remove the old ones
204 deleteObsoleteBNSQL = ` 196 deleteObsoleteBNSQL = `
205 DELETE FROM waterway.bottlenecks 197 DELETE FROM waterway.bottlenecks
206 WHERE bottleneck_id = $1 AND validity <@ $2 AND id <> ALL($3) 198 WHERE bottleneck_id = $1 AND validity <@ $2 AND id <> ALL($3)
538 // exists: 530 // exists:
539 var existing_bn_id *int64 531 var existing_bn_id *int64
540 err = findMatchingBNStmt.QueryRowContext(ctx, 532 err = findMatchingBNStmt.QueryRowContext(ctx,
541 bn.Bottleneck_id, 533 bn.Bottleneck_id,
542 &validity, 534 &validity,
543 bn.Fk_g_fid,
544 ).Scan(&existing_bn_id) 535 ).Scan(&existing_bn_id)
545 switch { 536 switch {
546 case err == sql.ErrNoRows: 537 case err == sql.ErrNoRows:
547 existing_bn_id = nil 538 existing_bn_id = nil
548 case err != nil: 539 case err != nil: