Mercurial > gemma

comparison pkg/imports/bn.go @ 4069:12f476e91c70 historization_ng

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