# HG changeset patch
# User Sascha L. Teichmann <teichmann@intevation.de>
# Date 1536314507 -7200
# Node ID 26cdc43ff97775e3fc0ccdfa6f621f7fa33d9e93
# Parent  b26ed3f3691ef53976d56bd7c27ba15e5ffc4043
bottlenecks: Add a -dump flag to dump INSERT statements instead of writing directly to the database.
Useful for a static setup.

diff -r b26ed3f3691e -r 26cdc43ff977 cmd/bottlenecks/main.go
--- a/cmd/bottlenecks/main.go	Fri Sep 07 11:42:08 2018 +0200
+++ b/cmd/bottlenecks/main.go	Fri Sep 07 12:01:47 2018 +0200
@@ -7,6 +7,7 @@
 	"log"
 	"regexp"
 	"strconv"
+	"strings"
 
 	"github.com/jackc/pgx"
 	"github.com/jackc/pgx/stdlib"
@@ -15,37 +16,70 @@
 )
 
 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
+  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_GeomFromText('Point(13.05501 47.80949)', 4326), 0.01)))::Geography,
-	$7,
-	$8,
-	$9,
-	$10,
-	$11,
-	$12,
-	$13
+  $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")
+	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")
@@ -94,25 +128,51 @@
 	return v
 }
 
-func main() {
+func quote(s string) string {
+	return "'" + strings.Replace(s, "'", "'''", -1) + "'"
+}
 
-	flag.Parse()
+func dumpSQLStatements(bns []*ifbn.BottleNeckType) error {
+
+	fmt.Println("BEGIN;")
 
-	client := ifbn.NewIBottleneckService(*url, *insecure, nil)
+	for i := range bns {
+		bn := bns[i]
+		rb, lb := splitRBLB(bn.Rb_lb)
+
+		var limiting, country string
 
-	req := &ifbn.Export_bn_by_isrs{}
+		if bn.Limiting_factor != nil {
+			limiting = string(*bn.Limiting_factor)
+		}
+
+		if bn.Responsible_country != nil {
+			country = string(*bn.Responsible_country)
+		}
 
-	resp, err := client.Export_bn_by_isrs(req)
-	if err != nil {
-		log.Fatalf("error: %v\n", err)
+		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("END;")
+	return err
+}
 
-	if resp.Export_bn_by_isrsResult == nil {
-		return
-	}
-	bns := resp.Export_bn_by_isrsResult.BottleNeckType
-
-	err = run(func(db *sql.DB) error {
+func storeInDatabase(bns []*ifbn.BottleNeckType) error {
+	return run(func(db *sql.DB) error {
 
 		stmt, err := db.Prepare(insertSQL)
 		if err != nil {
@@ -164,6 +224,31 @@
 
 		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)