Mercurial > gemma
changeset 4076:0507a9e4772b pdfscaling
merge with default
| author | Thomas Junk <thomas.junk@intevation.de> |
|---|---|
| date | Thu, 25 Jul 2019 15:10:27 +0200 |
| parents | 917c72e8360d (current diff) 5867dcf8e93c (diff) |
| children | d34e9b8ad396 |
| files | pkg/imports/errors.go |
| diffstat | 23 files changed, 475 insertions(+), 279 deletions(-) [+] |
line wrap: on
line diff
--- a/client/src/components/gauge/Waterlevel.vue Tue Jul 23 17:13:51 2019 +0200 +++ b/client/src/components/gauge/Waterlevel.vue Thu Jul 25 15:10:27 2019 +0200 @@ -460,8 +460,8 @@ .attr("clip-path", "url(#waterlevel-clip)"); svg.selectAll(".hdc-line").attr("stroke", "red"); svg.selectAll(".ldc-line").attr("stroke", "green"); - svg.selectAll(".mw-line").attr("stroke", "grey"); - svg.selectAll(".rn-line").attr("stroke", "grey"); + svg.selectAll(".mw-line").attr("stroke", "rgb(128,128,128)"); + svg.selectAll(".rn-line").attr("stroke", "rgb(128,128,128)"); svg .selectAll(".ref-waterlevel-label") .style("font-size", "10px")
--- a/client/src/components/importoverview/AGMLogItem.vue Tue Jul 23 17:13:51 2019 +0200 +++ b/client/src/components/importoverview/AGMLogItem.vue Thu Jul 25 15:10:27 2019 +0200 @@ -40,13 +40,15 @@ {{ entry }} </div> <div :class="isNew(line) ? 'col-6' : 'col-4'"> - {{ line.versions[0][entry] }} + <span :class="line.versions[1] ? '' : 'text-danger'">{{ + line.versions[0][entry] + }}</span> </div> <div v-if="isOld(line) && isDifferent(line, entry)" :class="isNew(line) ? 'col-6' : 'col-4'" > - {{ line.versions[1][entry] }} + {{ line.versions[1] ? line.versions[1][entry] : $options.DELETED }} </div> </div> </div> @@ -54,6 +56,22 @@ </template> <script> +/* This is Free Software under GNU Affero General Public License v >= 3.0 + * without warranty, see README.md and license for details. + * + * SPDX-License-Identifier: AGPL-3.0-or-later + * License-Filename: LICENSES/AGPL-3.0.txt + * + * Copyright (C) 2018, 2019 by via donau + * – Österreichische Wasserstraßen-Gesellschaft mbH + * Software engineering by Intevation GmbH + * + * Author(s): + * * Thomas Junk <thomas.junk@intevation.de> + * * Markus Kottländer <markus.kottlaender@intevation.de> + * * Fadi Abbud <fadi.abbud@intevation.de> + */ +import app from "@/main"; export default { props: ["line", "index", "showDiff"], computed: { @@ -72,12 +90,14 @@ return !this.isNew(result); }, isDifferent(result, entry) { + if (!result.versions[1]) return true; return ( this.isOld(result) && result.versions[0][entry] != result.versions[1][entry] ); } - } + }, + DELETED: app.$gettext("deleted") }; </script>
--- a/pkg/controllers/user.go Tue Jul 23 17:13:51 2019 +0200 +++ b/pkg/controllers/user.go Thu Jul 25 15:10:27 2019 +0200 @@ -29,6 +29,7 @@ "gemma.intevation.de/gemma/pkg/auth" "gemma.intevation.de/gemma/pkg/misc" "gemma.intevation.de/gemma/pkg/models" + "gemma.intevation.de/gemma/pkg/pgxutils" "gemma.intevation.de/gemma/pkg/scheduler" ) @@ -262,6 +263,8 @@ } if err != nil { + m, c := pgxutils.ReadableError{err}.MessageAndCode() + err = JSONError{Code: c, Message: m} return }
--- a/pkg/imports/agm.go Tue Jul 23 17:13:51 2019 +0200 +++ b/pkg/imports/agm.go Thu Jul 25 15:10:27 2019 +0200 @@ -21,12 +21,12 @@ "database/sql" "encoding/csv" "encoding/json" - "errors" "fmt" "io" "math" "os" "path/filepath" + "sort" "strconv" "strings" "time" @@ -65,30 +65,24 @@ } const ( - // delete the old and keep the new measures. agmStageDoneDeleteSQL = ` -WITH staged AS ( +DELETE FROM waterway.gauge_measurements WHERE id IN ( SELECT key FROM import.track_imports WHERE import_id = $1 AND - relation = 'waterway.gauge_measurements'::regclass -), -to_delete AS ( - SELECT o.id AS id - FROM waterway.gauge_measurements o - JOIN waterway.gauge_measurements n - USING (location, measure_date) - WHERE n.id IN (SELECT key FROM staged) - AND o.id NOT IN (SELECT key FROM staged) -) -DELETE FROM waterway.gauge_measurements WHERE id IN (SELECT id from to_delete)` + relation = 'waterway.gauge_measurements'::regclass AND + deletion +)` agmStageDoneSQL = ` UPDATE waterway.gauge_measurements SET staging_done = true WHERE id IN ( - SELECT key FROM import.track_imports + SELECT key + FROM import.track_imports WHERE import_id = $1 AND - relation = 'waterway.gauge_measurements'::regclass)` + relation = 'waterway.gauge_measurements'::regclass AND + NOT deletion +)` ) func (agmJobCreator) StageDone( @@ -121,6 +115,7 @@ } type agmLine struct { + id int64 Location models.Isrs `json:"fk-gauge-id"` CountryCode string `json:"country-code"` Sender string `json:"sender"` @@ -158,6 +153,7 @@ language_code, date_issue, reference_code, + measure_date, water_level, date_info, source_organization @@ -165,7 +161,7 @@ WHERE location = ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int) - AND measure_date = $6 + AND measure_date BETWEEN $6 AND $7 AND staging_done ` @@ -211,7 +207,46 @@ ` ) -var errContinue = errors.New("continue") +func parseAGMHeaders(headers []string, fkGaugeIDIdx, measureDateIdx, valueIdx *int) error { + + headerFields := []struct { + idx *int + name string + }{ + {fkGaugeIDIdx, "fk_gauge_id"}, + {measureDateIdx, "measure_date"}, + {valueIdx, "value"}, // "water_level", + } + +nextHeader: + for i, f := range headers { + h := strings.Replace(strings.ToLower( + strings.TrimSpace(f)), " ", "_", -1) + + for j := range headerFields { + if headerFields[j].name == h { + if *headerFields[j].idx != -1 { + return fmt.Errorf( + "There is more than one column namend '%s'", h) + } + *headerFields[j].idx = i + continue nextHeader + } + } + } + + var missing []string + for i := range headerFields { + if headerFields[i].name != "unit" && *headerFields[i].idx == -1 { + missing = append(missing, headerFields[i].name) + } + } + if len(missing) > 0 { + return fmt.Errorf("Missing columns: %s", strings.Join(missing, ", ")) + } + + return nil +} // Do executes the actual approved gauge measurements import. func (agm *ApprovedGaugeMeasurements) Do( @@ -244,40 +279,11 @@ valueIdx = -1 ) - headerFields := []struct { - idx *int - name string - }{ - {&fkGaugeIDIdx, "fk_gauge_id"}, - {&measureDateIdx, "measure_date"}, - {&valueIdx, "value"}, // "water_level", - } - -nextHeader: - for i, f := range headers { - h := strings.Replace(strings.ToLower( - strings.TrimSpace(f)), " ", "_", -1) - - for j := range headerFields { - if headerFields[j].name == h { - if *headerFields[j].idx != -1 { - return nil, fmt.Errorf( - "There is more than one column namend '%s'", h) - } - *headerFields[j].idx = i - continue nextHeader - } - } - } - - var missing []string - for i := range headerFields { - if headerFields[i].name != "unit" && *headerFields[i].idx == -1 { - missing = append(missing, headerFields[i].name) - } - } - if len(missing) > 0 { - return nil, fmt.Errorf("Missing columns: %s", strings.Join(missing, ", ")) + if err := parseAGMHeaders( + headers, + &fkGaugeIDIdx, &measureDateIdx, &valueIdx, + ); err != nil { + return nil, err } gaugeCheckStmt, err := conn.PrepareContext(ctx, agmGaugeCheckSQL) @@ -298,7 +304,7 @@ } defer insertStmt.Close() - trackStmt, err := conn.PrepareContext(ctx, trackImportSQL) + trackStmt, err := conn.PrepareContext(ctx, trackImportDeletionSQL) if err != nil { return nil, err } @@ -314,6 +320,7 @@ agmLines := []*agmLine{} ignored := 0 + mdMinMax := map[models.Isrs]*[2]time.Time{} lines: for line := 1; ; line++ { @@ -368,6 +375,16 @@ if err != nil { return nil, fmt.Errorf("Invalid 'measure_date' line %d: %v", line, err) } + if v := mdMinMax[*gid]; v != nil { + if md.Before(v[0]) { + v[0] = md + } + if md.After(v[1]) { + v[1] = md + } + } else { + mdMinMax[*gid] = &[2]time.Time{md, md} + } newSender := agm.Originator newCountryCode := gid.CountryCode @@ -399,21 +416,55 @@ )) } + oldGMLines := map[models.Isrs]map[int64]*agmLine{} + for gid, minMax := range mdMinMax { + oldGMLines[gid], err = getOldGMLines( + ctx, selectStmt, gid, minMax[0], minMax[1]) + if err != nil { + return nil, err + } + } + + tx, err := conn.BeginTx(ctx, nil) + if err != nil { + return nil, err + } + defer tx.Rollback() + + txInsertStmt := tx.StmtContext(ctx, insertStmt) + txTrackStmt := tx.StmtContext(ctx, trackStmt) + agmLines: for _, line := range agmLines { - var ( - oldID int64 - oldCountryCode string - oldSender string - oldLanguageCode string - oldDateIssue time.Time - oldReferenceCode string - oldValue float64 - oldDateInfo time.Time - oldSourceOrganization string - ) + + var ase *agmSummaryEntry - err = selectStmt.QueryRowContext( + if old := oldGMLines[line.Location]; old != nil { + ut := line.MeasureDate.Unix() + if o, ok := old[ut]; ok { + if !o.hasDiff(line) { // identical + // don't delete + delete(old, ut) + continue agmLines + } + ase = &agmSummaryEntry{ + FKGaugeID: line.Location, + MeasureDate: line.MeasureDate, + Versions: []*agmLine{o, line}, + } + } + } + if ase == nil { + ase = &agmSummaryEntry{ + FKGaugeID: line.Location, + MeasureDate: line.MeasureDate, + Versions: []*agmLine{line}, + } + } + + var newID int64 + + if err := txInsertStmt.QueryRowContext( ctx, line.Location.CountryCode, line.Location.LoCode, @@ -421,109 +472,143 @@ line.Location.Orc, line.Location.Hectometre, line.MeasureDate.Time, - ).Scan( + line.CountryCode, + line.Sender, + line.LanguageCode, + line.DateIssue.Time, + line.ReferenceCode, + line.WaterLevel, + line.DateInfo.Time, + line.SourceOrganization, + ).Scan(&newID); err != nil { + return nil, err + } + + if _, err := txTrackStmt.ExecContext( + ctx, importID, "waterway.gauge_measurements", + newID, + false, + ); err != nil { + return nil, err + } + + entries = append(entries, ase) + } + + var removed int + + // Issue deletes + for _, old := range oldGMLines { + removed += len(old) + for _, line := range old { + if _, err := txTrackStmt.ExecContext( + ctx, importID, "waterway.gauge_measurements", + line.id, + true, + ); err != nil { + return nil, err + } + entries = append(entries, &agmSummaryEntry{ + FKGaugeID: line.Location, + MeasureDate: line.MeasureDate, + Versions: []*agmLine{line, nil}, + }) + } + } + + feedback.Info("Measurements to update/insert: %d", len(entries)) + feedback.Info("Measurements to delete: %d", removed) + + if len(entries) == 0 && removed == 0 { + return nil, UnchangedError("No changes from AGM import") + } + + if err = tx.Commit(); err != nil { + return nil, fmt.Errorf("Commit failed: %v", err) + } + + // Sort here to mix the deletes right beside the matching inserts/updates. + // This also makes the output deterministic. + sort.Slice(entries, func(i, j int) bool { + return entries[i].FKGaugeID.Less(&entries[j].FKGaugeID) + }) + + feedback.Info("Imported %d entries with changes", len(entries)) + feedback.Info("Importing approved gauge measurements took %s", + time.Since(start)) + + return entries, nil +} + +func getOldGMLines( + ctx context.Context, + stmt *sql.Stmt, + location models.Isrs, + from time.Time, + to time.Time, +) (map[int64]*agmLine, error) { + var ( + oldID int64 + oldCountryCode string + oldSender string + oldLanguageCode string + oldDateIssue time.Time + oldReferenceCode string + oldMeasureDate time.Time + oldValue float64 + oldDateInfo time.Time + oldSourceOrganization string + ) + gmLines := map[int64]*agmLine{} + + gms, err := stmt.QueryContext( + ctx, + location.CountryCode, + location.LoCode, + location.FairwaySection, + location.Orc, + location.Hectometre, + from, + to, + ) + if err != nil { + return nil, err + } + defer gms.Close() + for gms.Next() { + if err = gms.Scan( &oldID, &oldCountryCode, &oldSender, &oldLanguageCode, &oldDateIssue, &oldReferenceCode, + &oldMeasureDate, &oldValue, &oldDateInfo, &oldSourceOrganization, - ) - - var newEntry bool - switch { - case err == sql.ErrNoRows: - // Complete new one - newEntry = true - case err != nil: + ); err != nil { return nil, err } - - switch err := func() error { - tx, err := conn.BeginTx(ctx, nil) - if err != nil { - return err - } - defer tx.Rollback() - - var newID int64 - - if err := tx.StmtContext(ctx, insertStmt).QueryRowContext( - ctx, - line.Location.CountryCode, - line.Location.LoCode, - line.Location.FairwaySection, - line.Location.Orc, - line.Location.Hectometre, - line.MeasureDate.Time, - line.CountryCode, - line.Sender, - line.LanguageCode, - line.DateIssue.Time, - line.ReferenceCode, - line.WaterLevel, - line.DateInfo.Time, - line.SourceOrganization, - ).Scan(&newID); err != nil { - warn(handleError(err).Error()) - ignored++ - return errContinue - } - - if _, err := tx.StmtContext(ctx, trackStmt).ExecContext( - ctx, importID, "waterway.gauge_measurements", newID, - ); err != nil { - return err - } - - if err = tx.Commit(); err != nil { - err = fmt.Errorf("Commit failed: %v", err) - } - return err - }(); { - case err == errContinue: - continue agmLines - case err != nil: - return nil, err - } - - ase := &agmSummaryEntry{ - FKGaugeID: line.Location, - MeasureDate: line.MeasureDate, - } - - if newEntry { - ase.Versions = []*agmLine{line} - } else { - o := newAGMLine( - line.Location, - oldCountryCode, - oldSender, - oldLanguageCode, - oldDateIssue, - oldReferenceCode, - line.MeasureDate.Time, - oldValue, - oldDateInfo, - oldSourceOrganization, - ) - // Ignore if there is no diff. - if !line.hasDiff(o) { - continue - } - ase.Versions = []*agmLine{o, line} - } - entries = append(entries, ase) + line := newAGMLine( + location, + oldCountryCode, + oldSender, + oldLanguageCode, + oldDateIssue, + oldReferenceCode, + oldMeasureDate, + oldValue, + oldDateInfo, + oldSourceOrganization, + ) + line.id = oldID + gmLines[oldMeasureDate.Unix()] = line } - - feedback.Info("Imported %d entries with changes", len(entries)) - feedback.Info("Importing approved gauge measurements took %s", - time.Since(start)) - - return entries, nil + if err = gms.Err(); err != nil { + return nil, err + } + return gmLines, nil } func newAGMLine(
--- a/pkg/imports/bn.go Tue Jul 23 17:13:51 2019 +0200 +++ b/pkg/imports/bn.go Thu Jul 25 15:10:27 2019 +0200 @@ -24,6 +24,7 @@ "strings" "time" + "gemma.intevation.de/gemma/pkg/pgxutils" "gemma.intevation.de/gemma/pkg/soap/ifbn" "github.com/jackc/pgx/pgtype" ) @@ -590,7 +591,7 @@ ) } if err != nil { - feedback.Warn(handleError(err).Error()) + feedback.Warn(pgxutils.ReadableError{err}.Error()) return nil } defer bns.Close() @@ -602,7 +603,7 @@ bnIds = append(bnIds, nid) } if err := bns.Err(); err != nil { - feedback.Warn(handleError(err).Error()) + feedback.Warn(pgxutils.ReadableError{err}.Error()) return nil } if len(bnIds) == 0 { @@ -619,7 +620,7 @@ &validity, &pgBnIds, ); err != nil { - feedback.Warn(handleError(err).Error()) + feedback.Warn(pgxutils.ReadableError{err}.Error()) if err2 := tx.Rollback(); err2 != nil { return err2 } @@ -632,7 +633,7 @@ bn.Bottleneck_id, validity, ); err != nil { - feedback.Warn(handleError(err).Error()) + feedback.Warn(pgxutils.ReadableError{err}.Error()) if err2 := tx.Rollback(); err2 != nil { return err2 } @@ -669,7 +670,7 @@ &pgMaterials, ); err != nil { feedback.Warn("Failed to insert riverbed materials") - feedback.Warn(handleError(err).Error()) + feedback.Warn(pgxutils.ReadableError{err}.Error()) return nil } }
--- a/pkg/imports/errors.go Tue Jul 23 17:13:51 2019 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,84 +0,0 @@ -// This is Free Software under GNU Affero General Public License v >= 3.0 -// without warranty, see README.md and license for details. -// -// SPDX-License-Identifier: AGPL-3.0-or-later -// License-Filename: LICENSES/AGPL-3.0.txt -// -// Copyright (C) 2019 by via donau -// – Österreichische Wasserstraßen-Gesellschaft mbH -// Software engineering by Intevation GmbH -// -// Author(s): -// * Tom Gottfried <tom.gottfried@intevation.de> - -package imports - -import ( - "strings" - - "github.com/jackc/pgx" -) - -func handleError(err error) error { - switch e := err.(type) { - case pgx.PgError: - return dbError(e) - } - return err -} - -// Handle PostgreSQL error codes -const ( - notNullViolation = "23502" - foreignKeyViolation = "23503" - violatesRowLevelSecurity = "42501" - noDataFound = "P0002" -) - -type dbError pgx.PgError - -func (err dbError) Error() string { - switch err.Code { - case notNullViolation: - switch err.SchemaName { - case "waterway": - switch err.TableName { - case "gauges": - switch err.ColumnName { - case "objname": - return "Missing objname" - case "geom": - return "Missing lat/lon" - case "zero_point": - return "Missing zeropoint" - } - } - } - case foreignKeyViolation: - switch err.SchemaName { - case "waterway": - switch err.TableName { - case "gauge_measurements", "gauge_predictions", "bottlenecks": - switch err.ConstraintName { - case "gauge_key": - return "Referenced gauge with matching temporal validity not available" - } - } - } - case noDataFound: - // Most recent line from stacktrace contains name of failed function - recent := strings.SplitN(err.Where, "\n", 1)[0] - switch { - case strings.Contains(recent, "isrsrange_points"): - return "No distance mark found for at least one given ISRS Location Code" - case strings.Contains(recent, "isrsrange_axis"): - return "No contiguous axis found between given ISRS Location Codes" - case strings.Contains(recent, "isrsrange_area"): - return "No area around axis between given ISRS Location Codes" - } - - case violatesRowLevelSecurity: - return "Could not save: Data outside the area of responsibility." - } - return "Unexpected database error: " + err.Message -}
--- a/pkg/imports/fd.go Tue Jul 23 17:13:51 2019 +0200 +++ b/pkg/imports/fd.go Thu Jul 25 15:10:27 2019 +0200 @@ -22,6 +22,7 @@ "time" "gemma.intevation.de/gemma/pkg/misc" + "gemma.intevation.de/gemma/pkg/pgxutils" "gemma.intevation.de/gemma/pkg/wfs" ) @@ -310,7 +311,7 @@ // ignore -> filtered by responsibility_areas continue features case err != nil: - feedback.Warn(handleError(err).Error()) + feedback.Warn(pgxutils.ReadableError{err}.Error()) continue features } // Store for potential later removal.
--- a/pkg/imports/gm.go Tue Jul 23 17:13:51 2019 +0200 +++ b/pkg/imports/gm.go Thu Jul 25 15:10:27 2019 +0200 @@ -25,6 +25,7 @@ "time" "gemma.intevation.de/gemma/pkg/models" + "gemma.intevation.de/gemma/pkg/pgxutils" "gemma.intevation.de/gemma/pkg/soap/nts" "github.com/jackc/pgx/pgtype" ) @@ -456,7 +457,7 @@ case err == sql.ErrNoRows: // thats expected, nothing to do case err != nil: - feedback.Warn(handleError(err).Error()) + feedback.Warn(pgxutils.ReadableError{err}.Error()) default: newP++ } @@ -487,7 +488,7 @@ case err == sql.ErrNoRows: // thats expected, nothing to do case err != nil: - feedback.Warn(handleError(err).Error()) + feedback.Warn(pgxutils.ReadableError{err}.Error()) default: newM++ }
--- a/pkg/imports/sec.go Tue Jul 23 17:13:51 2019 +0200 +++ b/pkg/imports/sec.go Thu Jul 25 15:10:27 2019 +0200 @@ -19,6 +19,7 @@ "time" "gemma.intevation.de/gemma/pkg/models" + "gemma.intevation.de/gemma/pkg/pgxutils" ) type Section struct { @@ -181,7 +182,7 @@ sec.Source, sec.Tolerance, ).Scan(&id); err != nil { - return nil, handleError(err) + return nil, pgxutils.ReadableError{err} } if err := track(ctx, tx, importID, "waterway.sections", id); err != nil {
--- a/pkg/imports/st.go Tue Jul 23 17:13:51 2019 +0200 +++ b/pkg/imports/st.go Thu Jul 25 15:10:27 2019 +0200 @@ -20,6 +20,7 @@ "time" "gemma.intevation.de/gemma/pkg/models" + "gemma.intevation.de/gemma/pkg/pgxutils" ) type Stretch struct { @@ -202,7 +203,7 @@ st.Source, st.Tolerance, ).Scan(&id); err != nil { - return nil, handleError(err) + return nil, pgxutils.ReadableError{err} } // store the associated countries.
--- a/pkg/imports/track.go Tue Jul 23 17:13:51 2019 +0200 +++ b/pkg/imports/track.go Thu Jul 25 15:10:27 2019 +0200 @@ -22,6 +22,10 @@ trackImportSQL = ` INSERT INTO import.track_imports (import_id, relation, key) VALUES ($1, $2::regclass, $3)` + + trackImportDeletionSQL = ` + INSERT INTO import.track_imports (import_id, deletion, relation, key) + VALUES ($1, $4, $2::regclass, $3)` ) func track(ctx context.Context, tx *sql.Tx, importID int64, relation string, key int64) error {
--- a/pkg/imports/wa.go Tue Jul 23 17:13:51 2019 +0200 +++ b/pkg/imports/wa.go Thu Jul 25 15:10:27 2019 +0200 @@ -24,6 +24,7 @@ "strconv" "time" + "gemma.intevation.de/gemma/pkg/pgxutils" "gemma.intevation.de/gemma/pkg/wfs" ) @@ -246,7 +247,7 @@ outside++ // ignore -> filtered by responsibility_areas case err != nil: - feedback.Warn(handleError(err).Error()) + feedback.Warn(pgxutils.ReadableError{err}.Error()) default: features++ }
--- a/pkg/imports/wg.go Tue Jul 23 17:13:51 2019 +0200 +++ b/pkg/imports/wg.go Thu Jul 25 15:10:27 2019 +0200 @@ -22,6 +22,7 @@ "github.com/jackc/pgx/pgtype" "gemma.intevation.de/gemma/pkg/models" + "gemma.intevation.de/gemma/pkg/pgxutils" "gemma.intevation.de/gemma/pkg/soap/erdms" ) @@ -326,7 +327,7 @@ ).Scan(&isNew) switch { case err != nil: - feedback.Warn(handleError(err).Error()) + feedback.Warn(pgxutils.ReadableError{err}.Error()) if err2 := tx.Rollback(); err2 != nil { return nil, err2 } @@ -351,7 +352,7 @@ source, time.Time(*dr.Lastupdate), ); err != nil { - feedback.Warn(handleError(err).Error()) + feedback.Warn(pgxutils.ReadableError{err}.Error()) if err2 := tx.Rollback(); err2 != nil { return nil, err2 } @@ -388,7 +389,7 @@ unchanged++ continue case err2 != nil: - feedback.Warn(handleError(err2).Error()) + feedback.Warn(pgxutils.ReadableError{err2}.Error()) if err3 := tx.Rollback(); err3 != nil { return nil, err3 } @@ -435,7 +436,7 @@ code.String(), &validity, ); err != nil { - feedback.Warn(handleError(err).Error()) + feedback.Warn(pgxutils.ReadableError{err}.Error()) if err2 := tx.Rollback(); err2 != nil { return nil, err2 } @@ -483,7 +484,7 @@ string(**wl.level), int64(**wl.value), ); err != nil { - feedback.Warn(handleError(err).Error()) + feedback.Warn(pgxutils.ReadableError{err}.Error()) tx.Rollback() continue }
--- a/pkg/imports/wx.go Tue Jul 23 17:13:51 2019 +0200 +++ b/pkg/imports/wx.go Thu Jul 25 15:10:27 2019 +0200 @@ -23,6 +23,7 @@ "io" "time" + "gemma.intevation.de/gemma/pkg/pgxutils" "gemma.intevation.de/gemma/pkg/wfs" ) @@ -320,7 +321,7 @@ // ignore -> filtered by responsibility_areas return nil case err != nil: - feedback.Warn(handleError(err).Error()) + feedback.Warn(pgxutils.ReadableError{err}.Error()) default: *features++ }
--- a/pkg/models/isrs.go Tue Jul 23 17:13:51 2019 +0200 +++ b/pkg/models/isrs.go Thu Jul 25 15:10:27 2019 +0200 @@ -43,6 +43,37 @@ return nil } +func (isrs *Isrs) Less(other *Isrs) bool { + if isrs.CountryCode < other.CountryCode { + return true + } + if isrs.CountryCode > other.CountryCode { + return false + } + if isrs.LoCode < other.LoCode { + return true + } + if isrs.LoCode > other.LoCode { + return false + } + if isrs.FairwaySection < other.FairwaySection { + return true + } + if isrs.FairwaySection > other.FairwaySection { + return false + } + if isrs.Orc < other.Orc { + return true + } + if isrs.Orc > other.Orc { + return false + } + if isrs.Hectometre < other.Hectometre { + return true + } + return false +} + func (isrs *Isrs) MarshalJSON() ([]byte, error) { if isrs == nil { return nil, nil
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pkg/pgxutils/errors.go Thu Jul 25 15:10:27 2019 +0200 @@ -0,0 +1,121 @@ +// This is Free Software under GNU Affero General Public License v >= 3.0 +// without warranty, see README.md and license for details. +// +// SPDX-License-Identifier: AGPL-3.0-or-later +// License-Filename: LICENSES/AGPL-3.0.txt +// +// Copyright (C) 2019 by via donau +// – Österreichische Wasserstraßen-Gesellschaft mbH +// Software engineering by Intevation GmbH +// +// Author(s): +// * Tom Gottfried <tom.gottfried@intevation.de> +// * Sascha L. Teichmann <sascha.teichmann@intevation.de> + +package pgxutils + +import ( + "net/http" + "strings" + + "github.com/jackc/pgx" +) + +const ( + notNullViolation = "23502" + foreignKeyViolation = "23503" + uniqueViolation = "23505" + violatesRowLevelSecurity = "42501" + noDataFound = "P0002" +) + +type ReadableError struct { + Err error +} + +func (re ReadableError) Error() string { + m, _ := re.MessageAndCode() + return m +} + +// MessageAndCode returns a user-readable message +// and a matching HTTP status code. +// If its not a pgx.PgError it defaults to +// calling the parent Error method and returns its +// result together with http.StatusInternalServerError. +func (re ReadableError) MessageAndCode() (string, int) { + if e, ok := re.Err.(pgx.PgError); ok { + return messageAndCode(e) + } + return re.Err.Error(), http.StatusInternalServerError +} + +func messageAndCode(err pgx.PgError) (m string, c int) { + + c = http.StatusInternalServerError + + switch err.Code { + case notNullViolation: + switch err.SchemaName { + case "waterway": + switch err.TableName { + case "gauges": + switch err.ColumnName { + case "objname": + m = "Missing objname" + return + case "geom": + m = "Missing lat/lon" + return + case "zero_point": + m = "Missing zeropoint" + return + } + } + } + case foreignKeyViolation: + switch err.SchemaName { + case "waterway": + switch err.TableName { + case "gauge_measurements", "gauge_predictions", "bottlenecks": + switch err.ConstraintName { + case "gauge_key": + m = "Referenced gauge with matching temporal validity not available" + return + } + } + } + case uniqueViolation: + switch err.SchemaName { + case "internal": + switch err.TableName { + case "user_profiles": + switch err.ConstraintName { + case "user_profiles_pkey": + m = "A user with that name already exists" + c = http.StatusConflict + return + } + } + } + case noDataFound: + // Most recent line from stacktrace contains name of failed function + recent := strings.SplitN(err.Where, "\n", 1)[0] + switch { + case strings.Contains(recent, "isrsrange_points"): + m = "No distance mark found for at least one given ISRS Location Code" + return + case strings.Contains(recent, "isrsrange_axis"): + m = "No contiguous axis found between given ISRS Location Codes" + return + case strings.Contains(recent, "isrsrange_area"): + m = "No area around axis between given ISRS Location Codes" + return + } + case violatesRowLevelSecurity: + m = "Could not save: Data outside the area of responsibility." + return + } + m = "Unexpected database error: " + err.Message + return +}
--- a/schema/auth_tests.sql Tue Jul 23 17:13:51 2019 +0200 +++ b/schema/auth_tests.sql Thu Jul 25 15:10:27 2019 +0200 @@ -151,7 +151,7 @@ log AS ( INSERT INTO import.import_logs (import_id, msg) SELECT id, 'test' FROM job) - INSERT INTO import.track_imports + INSERT INTO import.track_imports (import_id, relation, key) SELECT id, 'waterway.bottlenecks', 0 FROM job $$, 'Waterway admin can add import job and related data');
--- a/schema/gemma.sql Tue Jul 23 17:13:51 2019 +0200 +++ b/schema/gemma.sql Thu Jul 25 15:10:27 2019 +0200 @@ -733,12 +733,9 @@ UNIQUE (bottleneck_id, surdat), -- additional_data xml -- Currently not relevant for GEMMA critical boolean, - date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, + date_info timestamp with time zone NOT NULL, source_organization varchar NOT NULL ) - CREATE TRIGGER fairway_availability_date_info - BEFORE UPDATE ON fairway_availability - FOR EACH ROW EXECUTE PROCEDURE update_date_info() CREATE TABLE fa_reference_values ( fairway_availability_id int NOT NULL REFERENCES fairway_availability, @@ -863,6 +860,7 @@ CREATE TABLE track_imports ( import_id int NOT NULL REFERENCES imports(id) ON DELETE CASCADE, + deletion bool NOT NULL DEFAULT false, relation regclass NOT NULL, key int NOT NULL, UNIQUE (relation, key) @@ -875,7 +873,7 @@ tmp RECORD; BEGIN FOR tmp IN - SELECT * FROM import.track_imports WHERE import_id = imp_id + SELECT * FROM import.track_imports WHERE import_id = imp_id AND NOT deletion LOOP EXECUTE format('DELETE FROM %s WHERE id = $1', tmp.relation) USING tmp.key; END LOOP; @@ -883,15 +881,6 @@ $$ LANGUAGE plpgsql; -CREATE FUNCTION import.del_import() RETURNS trigger AS -$$ -BEGIN - EXECUTE format('DELETE FROM %s WHERE id = $1', OLD.relation) USING OLD.key; - RETURN NULL; -END; -$$ -LANGUAGE plpgsql; - CREATE SCHEMA caching CREATE TABLE sounding_differences (
--- a/schema/tap_tests_data.sql Tue Jul 23 17:13:51 2019 +0200 +++ b/schema/tap_tests_data.sql Thu Jul 25 15:10:27 2019 +0200 @@ -147,7 +147,7 @@ log AS ( INSERT INTO import.import_logs (import_id, msg) SELECT id, 'test' FROM job) -INSERT INTO import.track_imports +INSERT INTO import.track_imports (import_id, relation, key) SELECT id, 'waterway.bottlenecks', 1 FROM job; WITH
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1007/01.delete-import-tracking.sql Thu Jul 25 15:10:27 2019 +0200 @@ -0,0 +1,1 @@ +ALTER TABLE import.track_imports ADD COLUMN deletion bool NOT NULL DEFAULT false;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1008/01.import-delete.sql Thu Jul 25 15:10:27 2019 +0200 @@ -0,0 +1,16 @@ +DROP FUNCTION import.del_import(); + +CREATE OR REPLACE FUNCTION import.del_import(imp_id int) RETURNS void AS +$$ +DECLARE + tmp RECORD; +BEGIN + FOR tmp IN + SELECT * FROM import.track_imports WHERE import_id = imp_id AND NOT deletion + LOOP + EXECUTE format('DELETE FROM %s WHERE id = $1', tmp.relation) USING tmp.key; + END LOOP; +END; +$$ +LANGUAGE plpgsql; +
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1009/01.drop-fa-date_info-default.sql Thu Jul 25 15:10:27 2019 +0200 @@ -0,0 +1,2 @@ +ALTER TABLE waterway.fairway_availability ALTER COLUMN date_info DROP DEFAULT; +DROP TRIGGER fairway_availability_date_info ON waterway.fairway_availability;