Mercurial > gemma
changeset 5696:a6dc68389c37 sr-v2
Started a migration tool to convert meshes.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Tue, 13 Feb 2024 01:02:16 +0100 |
parents | ef80748ae4f3 |
children | 40bf5d8283fb |
files | cmd/meshmigrate/main.go |
diffstat | 1 files changed, 193 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- /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)) +}