--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/cmd/meshmigrate/main.go	Tue Feb 13 01:02:16 2024 +0100
@@ -0,0 +1,193 @@
+// 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>
+
+// meshmigrate can be used to migrate mesh indices from one version to another.
+package main
+
+import (
+	"database/sql"
+	"flag"
+	"fmt"
+	"log"
+	"os"
+	"strconv"
+	"strings"
+
+	"gemma.intevation.de/gemma/pkg/mesh"
+	"github.com/jackc/pgx"
+	"github.com/jackc/pgx/stdlib"
+)
+
+type credentials struct {
+	host     string
+	port     uint
+	database string
+	user     string
+	password string
+}
+
+func (c *credentials) openDB() (*sql.DB, error) {
+	cc, err := pgx.ParseConnectionString("sslmode=prefer")
+	if err != nil {
+		return nil, err
+	}
+	cc.Host = c.host
+	cc.Port = uint16(c.port)
+	cc.User = c.user
+	cc.Password = c.password
+	cc.Database = c.database
+	return stdlib.OpenDB(cc), nil
+}
+
+const (
+	fetchSQL = `
+SELECT
+	id,
+	coalesce(mesh_index_version, 1) AS mesh_index_version,
+	mesh_index
+FROM waterway.sounding_results WHERE `
+)
+
+func process(
+	creds *credentials,
+	to int,
+	ids []int64,
+	limit int,
+	dry, backup bool,
+) error {
+	db, err := creds.openDB()
+	if err != nil {
+		return err
+	}
+	defer db.Close()
+
+	sql := fetchSQL + fmt.Sprintf("coalesce(mesh_index_version, 1) < %d", to)
+
+	if len(ids) > 0 {
+		sql += " AND " + idsFilter(ids)
+	}
+
+	rows, err := db.Query(sql)
+	if err != nil {
+		return err
+	}
+	defer rows.Close()
+
+	var totalIn, totalOut int64
+
+	for rows.Next() {
+		var id int64
+		var version int
+		var data []byte
+		if err := rows.Scan(&id, &version, &data); err != nil {
+			return err
+		}
+		if len(data) == 0 {
+			log.Printf("mesh %d is empty\n", id)
+			continue
+		}
+		log.Printf("processing mesh %d\n", id)
+		totalIn += int64(len(data))
+		if backup {
+			fname := fmt.Sprintf("mesh-%d-v%d.idx.gz", id, version)
+			if err := os.WriteFile(fname, data, 0666); err != nil {
+				return err
+			}
+		}
+		src := new(mesh.STRTree)
+		if err := src.FromBytes(data, version); err != nil {
+			return err
+		}
+		src.OptimizeForSerialization(to)
+		out, ver, err := src.Bytes(to)
+		if err != nil {
+			return err
+		}
+		totalOut += int64(len(out))
+		if backup {
+			fname := fmt.Sprintf("migrated-mesh-%d-v%d.idx.gz", id, ver)
+			if err := os.WriteFile(fname, out, 0666); err != nil {
+				return err
+			}
+		}
+	}
+	if rows.Err(); err != nil {
+		return err
+	}
+	log.Printf("in: %d (%.2f MB)\n", totalIn, float64(totalIn)/(1024*1024))
+	log.Printf("out: %d (%.2f MB)\n", totalOut, float64(totalOut)/(1024*1024))
+	log.Printf("ratio: %.2f%%\n", float64(totalOut)/float64(totalIn)*100)
+
+	return nil
+}
+
+func idsFilter(ids []int64) string {
+	if len(ids) == 0 {
+		return ""
+	}
+	var b strings.Builder
+	for i, id := range ids {
+		if i > 0 {
+			b.WriteByte(',')
+		}
+		b.WriteString(strconv.FormatInt(id, 10))
+	}
+	return "id IN (" + b.String() + ")"
+}
+
+func check(err error) {
+	if err != nil {
+		log.Fatalf("error: %v\n", err)
+	}
+}
+
+func toIDs(s string) ([]int64, error) {
+	if s == "" {
+		return nil, nil
+	}
+	var ids []int64
+	for _, f := range strings.Split(s, ",") {
+		f = strings.TrimSpace(f)
+		id, err := strconv.ParseInt(f, 10, 64)
+		if err != nil {
+			return nil, err
+		}
+		ids = append(ids, id)
+	}
+	return ids, nil
+}
+
+func main() {
+	var (
+		creds  credentials
+		idsS   string
+		limit  int
+		dry    bool
+		backup bool
+		to     int
+	)
+	flag.StringVar(&creds.host, "host", "localhost", "host of the database server")
+	flag.UintVar(&creds.port, "port", 5432, "port of the database server")
+	flag.StringVar(&creds.database, "database", "gemma", "database name")
+	flag.StringVar(&creds.user, "user", "gemma", "database user")
+	flag.StringVar(&creds.password, "password", "gemma", "database user password")
+	flag.IntVar(&limit, "limit", -1, "limiting number of mesh to migrate (-1: no limit)")
+	flag.StringVar(&idsS, "ids", "", "filter ids (empty: no ids)")
+	flag.BoolVar(&dry, "dry", false, "to a dry run")
+	flag.BoolVar(&backup, "backup", true, "store backup in file system")
+	flag.IntVar(&to, "to", 2, "version to store")
+	flag.Parse()
+	ids, err := toIDs(idsS)
+	check(err)
+	check(process(&creds, to, ids, limit, dry, backup))
+}