Mercurial > gemma
changeset 574:18c007104106
Try to insert bottlenecks from IFBN service into database.
Currently fails.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 05 Sep 2018 17:50:53 +0200 |
parents | 03c15abb8372 |
children | 5484558e1b50 |
files | cmd/bottlenecks/main.go |
diffstat | 1 files changed, 136 insertions(+), 9 deletions(-) [+] |
line wrap: on
line diff
--- a/cmd/bottlenecks/main.go Wed Sep 05 16:30:12 2018 +0200 +++ b/cmd/bottlenecks/main.go Wed Sep 05 17:50:53 2018 +0200 @@ -1,17 +1,100 @@ package main import ( + "database/sql" "flag" "fmt" "log" + "regexp" + "strconv" + + "github.com/jackc/pgx" + "github.com/jackc/pgx/stdlib" "gemma.intevation.de/gemma/pkg/soap/ifbn" ) -func main() { +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, + $2, + $3, + $4, + isrsrange(isrs_fromText($5), isrs_fromText($6)), + ST_MakePolygon(ST_GeomFromText('LINESTRING(0 0,1 0,1 1,0 0)', 4326))::Geography, + $7, + $8, + $9, + $10, + $11, + $12, + $13 +) ON CONFLICT (bottleneck_id) DO NOTHING` + +var ( + url = flag.String("url", "", "the IFBN service") + 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") +) - url := flag.String("url", "", "the IFBN service") - insecure := flag.Bool("insecure", false, "skip SSL verification") +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 main() { flag.Parse() @@ -24,17 +107,61 @@ log.Fatalf("error: %v\n", err) } - fmt.Printf("%v\n", resp) - if resp.Export_bn_by_isrsResult == nil { return } bns := resp.Export_bn_by_isrsResult.BottleNeckType - log.Printf("bottle necks: %d\n", len(bns)) + err = 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 string + + if bn.Limiting_factor != nil { + limiting = string(*bn.Limiting_factor) + } - for i := range bns { - bn := bns[i] - log.Printf("%s: %s %v\n", bn.Fk_g_fid, bn.OBJNAM, bn.Date_Info) + fmt.Printf("%s '%s' %s %s\n", bn.Fk_g_fid, bn.OBJNAM, rb, lb) + + if _, err := st.Exec( + bn.Bottleneck_id, + bn.Fk_g_fid, + bn.OBJNAM, + bn.NOBJNM, + bn.From_ISRS, bn.To_ISRS, + rb, + lb, + bn.Responsible_country, + revisitingTime(bn.Revisiting_time), + limiting, + bn.Date_Info, + bn.Source, + ); err != nil { + tx.Rollback() + return err + } + } + + return tx.Commit() + }) + + if err != nil { + log.Fatalf("error: %v\n", err) } }