Mercurial > gemma
view cmd/bottlenecks/main.go @ 1141:a473d91b0856
merge
author | Markus Kottlaender <markus@intevation.de> |
---|---|
date | Mon, 12 Nov 2018 14:48:18 +0100 |
parents | a244b18cb916 |
children |
line wrap: on
line source
// 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 by via donau // – Österreichische Wasserstraßen-Gesellschaft mbH // Software engineering by Intevation GmbH // // Author(s): // * Sascha L. Teichmann <sascha.teichmann@intevation.de> package main import ( "database/sql" "flag" "fmt" "log" "regexp" "strconv" "strings" "github.com/jackc/pgx" "github.com/jackc/pgx/stdlib" "gemma.intevation.de/gemma/pkg/soap/ifbn" ) const insertSQL = `INSERT INTO waterway.bottlenecks ( bottleneck_id, fk_g_fid, objnam, nobjnm, stretch, area, rb, lb, responsible_country, revisiting_time, limiting, date_info, source_organization ) VALUES( $1, isrs_fromText($2), $3, $4, isrsrange(isrs_fromText($5), isrs_fromText($6)), ST_MakePolygon(ST_ExteriorRing(ST_Buffer(ST_SetSRID(ST_Makepoint(13.05501, 47.80949), 4326), 0.01)))::Geography, $7, $8, $9, $10, $11, $12, $13 ) ON CONFLICT (bottleneck_id) DO NOTHING` const insertDumpSQL = `INSERT INTO waterway.bottlenecks ( bottleneck_id, fk_g_fid, objnam, nobjnm, stretch, area, rb, lb, responsible_country, revisiting_time, limiting, date_info, source_organization ) VALUES( %s, isrs_fromText(%s), %s, %s, isrsrange(isrs_fromText(%s), isrs_fromText(%s)), ST_MakePolygon(ST_ExteriorRing(ST_Buffer(ST_SetSRID(ST_Makepoint(13.05501, 47.80949), 4326), 0.01)))::Geography, %s, %s, %s, %d, %s, %s::timestamp with time zone, %s ) ON CONFLICT (bottleneck_id) DO NOTHING; ` var ( url = flag.String("url", "", "the IFBN service") dump = flag.Bool("dump", false, "dump SQL insert statements") insecure = flag.Bool("insecure", false, "skip SSL verification") dbhost = flag.String("dbhost", "localhost", "database host") dbport = flag.Uint("dbport", 5432, "database port") dbname = flag.String("dbname", "gemma", "database user") dbuser = flag.String("dbuser", "scott", "database user") dbpassword = flag.String("dbpw", "tiger", "database password") dbssl = flag.String("dbssl", "prefer", "database SSL mode") ) func run(fn func(*sql.DB) error) error { // To ease SSL config ride a bit on parsing. cc, err := pgx.ParseConnectionString("sslmode=" + *dbssl) if err != nil { return err } // Do the rest manually to allow whitespace in user/password. cc.Host = *dbhost cc.Port = uint16(*dbport) cc.User = *dbuser cc.Password = *dbpassword cc.Database = *dbname db := stdlib.OpenDB(cc) defer db.Close() return fn(db) } var rblbRe = regexp.MustCompile(`(..)_(..)`) func splitRBLB(s string) (string, string) { m := rblbRe.FindStringSubmatch(s) if len(m) == 0 { return "", "" } return m[1], m[2] } func revisitingTime(s string) int { v, err := strconv.Atoi(s) if err != nil { v = 0 } return v } func quote(s string) string { return "'" + strings.Replace(s, "'", "'''", -1) + "'" } func dumpSQLStatements(bns []*ifbn.BottleNeckType) error { fmt.Println("BEGIN;") for i := range bns { bn := bns[i] rb, lb := splitRBLB(bn.Rb_lb) var limiting, country string if bn.Limiting_factor != nil { limiting = string(*bn.Limiting_factor) } if bn.Responsible_country != nil { country = string(*bn.Responsible_country) } if _, err := fmt.Printf(insertDumpSQL, quote(bn.Bottleneck_id), quote(bn.Fk_g_fid), quote(bn.OBJNAM), quote(bn.NOBJNM), quote(bn.From_ISRS), quote(bn.To_ISRS), quote(rb), quote(lb), quote(country), revisitingTime(bn.Revisiting_time), quote(limiting), quote(bn.Date_Info.Format("2006-01-02 15:04:05.999 MST")), quote(bn.Source), ); err != nil { return err } } _, err := fmt.Println("COMMIT;") return err } func storeInDatabase(bns []*ifbn.BottleNeckType) error { return run(func(db *sql.DB) error { stmt, err := db.Prepare(insertSQL) if err != nil { return err } defer stmt.Close() tx, err := db.Begin() if err != nil { return err } st := tx.Stmt(stmt) for i := range bns { bn := bns[i] rb, lb := splitRBLB(bn.Rb_lb) var limiting, country string if bn.Limiting_factor != nil { limiting = string(*bn.Limiting_factor) } if bn.Responsible_country != nil { country = string(*bn.Responsible_country) } fmt.Printf("%s '%s' %s %s\n", bn.Fk_g_fid, bn.OBJNAM, bn.From_ISRS, bn.To_ISRS) if _, err := st.Exec( bn.Bottleneck_id, bn.Fk_g_fid, bn.OBJNAM, bn.NOBJNM, bn.From_ISRS, bn.To_ISRS, rb, lb, country, revisitingTime(bn.Revisiting_time), limiting, bn.Date_Info, bn.Source, ); err != nil { tx.Rollback() return err } } return tx.Commit() }) } func main() { flag.Parse() client := ifbn.NewIBottleneckService(*url, *insecure, nil) req := &ifbn.Export_bn_by_isrs{} resp, err := client.Export_bn_by_isrs(req) if err != nil { log.Fatalf("error: %v\n", err) } if resp.Export_bn_by_isrsResult == nil { return } bns := resp.Export_bn_by_isrsResult.BottleNeckType if *dump { err = dumpSQLStatements(bns) } else { err = storeInDatabase(bns) } if err != nil { log.Fatalf("error: %v\n", err) } }