From 66ac5c365191d7515f7f796e95a754f6882cda8e Mon Sep 17 00:00:00 2001 From: Thomas Voss Date: Sat, 5 Jul 2025 11:12:37 +0200 Subject: Code simplification --- src/dbx/db.go | 232 +++++++++++++++++++++----------------------- src/dbx/mintages.go | 167 ++++++++++++++++++++++++++----- src/dbx/sql/000-genesis.sql | 26 +++-- src/dbx/users.go | 6 +- 4 files changed, 269 insertions(+), 162 deletions(-) (limited to 'src/dbx') diff --git a/src/dbx/db.go b/src/dbx/db.go index fcb345e..b8112b9 100644 --- a/src/dbx/db.go +++ b/src/dbx/db.go @@ -1,27 +1,24 @@ package dbx import ( - "database/sql" "fmt" "io/fs" "log" - "reflect" "sort" - "strings" "git.thomasvoss.com/euro-cash.eu/pkg/atexit" . "git.thomasvoss.com/euro-cash.eu/pkg/try" + "github.com/jmoiron/sqlx" "github.com/mattn/go-sqlite3" ) var ( - db *sql.DB + db *sqlx.DB DBName string ) func Init(sqlDir fs.FS) { - db = Try2(sql.Open("sqlite3", DBName)) - Try(db.Ping()) + db = sqlx.MustConnect("sqlite3", DBName) atexit.Register(Close) Try(applyMigrations(sqlDir)) @@ -38,7 +35,7 @@ func Init(sqlDir fs.FS) { Password: "420", AdminP: false, })) - Try2(GetMintages("ad")) + Try2(GetMintages("ad", TypeCirc)) } func Close() { @@ -49,7 +46,7 @@ func applyMigrations(dir fs.FS) error { var latest int migratedp := true - rows, err := db.Query("SELECT latest FROM migration") + err := db.QueryRow("SELECT latest FROM migration").Scan(&latest) if err != nil { e, ok := err.(sqlite3.Error) /* IDK if there is a better way to do this… lol */ @@ -58,19 +55,9 @@ func applyMigrations(dir fs.FS) error { } else { return err } - } else { - defer rows.Close() } - if migratedp { - rows.Next() - if err := rows.Err(); err != nil { - return err - } - if err := rows.Scan(&latest); err != nil { - return err - } - } else { + if !migratedp { latest = -1 } @@ -104,24 +91,31 @@ func applyMigrations(dir fs.FS) error { return err } - if _, err := tx.Exec(string(qry)); err != nil { - tx.Rollback() - return fmt.Errorf("error in ‘%s’: %w", f, err) + var n int + if _, err = fmt.Sscanf(f, "%d", &n); err != nil { + goto error } - var n int - if _, err := fmt.Sscanf(f, "%d", &n); err != nil { - return err + if _, err = tx.Exec(string(qry)); err != nil { + err = fmt.Errorf("error in ‘%s’: %w", f, err) + goto error } + _, err = tx.Exec("UPDATE migration SET latest = ? WHERE id = 1", n) if err != nil { - return err + goto error } - if err := tx.Commit(); err != nil { - return err + if err = tx.Commit(); err != nil { + goto error } + log.Printf("Applied database migration ‘%s’\n", f) + continue + + error: + tx.Rollback() + return err } if last != "" { @@ -138,96 +132,96 @@ func applyMigrations(dir fs.FS) error { return nil } -func scanToStruct[T any](rs *sql.Rows) (T, error) { - return scanToStruct2[T](rs, true) -} - -func scanToStructs[T any](rs *sql.Rows) ([]T, error) { - xs := []T{} - for rs.Next() { - x, err := scanToStruct2[T](rs, false) - if err != nil { - return nil, err - } - xs = append(xs, x) - } - return xs, rs.Err() -} - -func scanToStruct2[T any](rs *sql.Rows, callNextP bool) (T, error) { - var t, zero T - - cols, err := rs.Columns() - if err != nil { - return zero, err - } - - v := reflect.ValueOf(&t).Elem() - tType := v.Type() - - rawValues := make([]any, len(cols)) - for i := range rawValues { - var zero any - rawValues[i] = &zero - } - - if callNextP { - rs.Next() - if err := rs.Err(); err != nil { - return zero, err - } - } - if err := rs.Scan(rawValues...); err != nil { - return zero, err - } - - /* col idx → [field idx, array idx] */ - arrayTargets := make(map[int][2]int) - colToField := make(map[string]int) - - for i := 0; i < tType.NumField(); i++ { - field := tType.Field(i) - tag := field.Tag.Get("db") - if tag == "" { - continue - } - - if strings.Contains(tag, ";") { - dbcols := strings.Split(tag, ";") - fv := v.Field(i) - if fv.Kind() != reflect.Array { - return zero, fmt.Errorf("field ‘%s’ is not array", - field.Name) - } - if len(dbcols) != fv.Len() { - return zero, fmt.Errorf("field ‘%s’ array length mismatch", - field.Name) - } - for j, colName := range cols { - for k, dbColName := range dbcols { - if colName == dbColName { - arrayTargets[j] = [2]int{i, k} - } - } - } - } else { - colToField[tag] = i - } - } - - for i, col := range cols { - vp := rawValues[i].(*any) - if fieldIdx, ok := colToField[col]; ok { - assignValue(v.Field(fieldIdx), *vp) - } else if target, ok := arrayTargets[i]; ok { - assignValue(v.Field(target[0]).Index(target[1]), *vp) - } - } - - return t, nil -} - -func assignValue(fv reflect.Value, val any) { +/* func scanToStruct[T any](rs *sql.Rows) (T, error) { + return scanToStruct2[T](rs, true) + } + + func scanToStructs[T any](rs *sql.Rows) ([]T, error) { + xs := []T{} + for rs.Next() { + x, err := scanToStruct2[T](rs, false) + if err != nil { + return nil, err + } + xs = append(xs, x) + } + return xs, rs.Err() + } + + func scanToStruct2[T any](rs *sql.Rows, callNextP bool) (T, error) { + var t, zero T + + cols, err := rs.Columns() + if err != nil { + return zero, err + } + + v := reflect.ValueOf(&t).Elem() + tType := v.Type() + + rawValues := make([]any, len(cols)) + for i := range rawValues { + var zero any + rawValues[i] = &zero + } + + if callNextP { + rs.Next() + if err := rs.Err(); err != nil { + return zero, err + } + } + if err := rs.Scan(rawValues...); err != nil { + return zero, err + } + + /\* col idx → [field idx, array idx] *\/ + arrayTargets := make(map[int][2]int) + colToField := make(map[string]int) + + for i := 0; i < tType.NumField(); i++ { + field := tType.Field(i) + tag := field.Tag.Get("db") + if tag == "" { + continue + } + + if strings.Contains(tag, ";") { + dbcols := strings.Split(tag, ";") + fv := v.Field(i) + if fv.Kind() != reflect.Array { + return zero, fmt.Errorf("field ‘%s’ is not array", + field.Name) + } + if len(dbcols) != fv.Len() { + return zero, fmt.Errorf("field ‘%s’ array length mismatch", + field.Name) + } + for j, colName := range cols { + for k, dbColName := range dbcols { + if colName == dbColName { + arrayTargets[j] = [2]int{i, k} + } + } + } + } else { + colToField[tag] = i + } + } + + for i, col := range cols { + vp := rawValues[i].(*any) + if fieldIdx, ok := colToField[col]; ok { + assignValue(v.Field(fieldIdx), *vp) + } else if target, ok := arrayTargets[i]; ok { + assignValue(v.Field(target[0]).Index(target[1]), *vp) + } + } + + return t, nil + } */ + +/* func assignValue(fv reflect.Value, val any) { if val == nil { fv.Set(reflect.Zero(fv.Type())) return @@ -236,4 +230,4 @@ func assignValue(fv reflect.Value, val any) { if v.Type().ConvertibleTo(fv.Type()) { fv.Set(v.Convert(fv.Type())) } -} +} */ diff --git a/src/dbx/mintages.go b/src/dbx/mintages.go index 4a6d5d3..d78e59c 100644 --- a/src/dbx/mintages.go +++ b/src/dbx/mintages.go @@ -1,31 +1,57 @@ package dbx +import ( + "database/sql" + "slices" +) + type MintageData struct { Standard []MSRow Commemorative []MCRow } +type msRowInternal struct { + Country string + Type MintageType + Year int + Denomination float64 + Mintmark sql.Null[string] + Mintage sql.Null[int] + Reference sql.Null[string] +} + +type mcRowInternal struct { + Country string + Type MintageType + Year int + Name string + Number int + Mintmark sql.Null[string] + Mintage sql.Null[int] + Reference sql.Null[string] +} + type MSRow struct { - Type int `db:"type"` - Year int `db:"year"` - Mintmark string `db:"mintmark"` - Mintages [ndenoms]int `db:"€0,01;€0,02;€0,05;€0,10;€0,20;€0,50;€1,00;€2,00"` - Reference string `db:"reference"` + Year int + Mintmark string + Mintages [ndenoms]int + References []string } type MCRow struct { - Type int `db:"type"` - Year int `db:"year"` - Name string `db:"name"` - Number int `db:"number"` - Mintmark string `db:"mintmark"` - Mintage int `db:"mintage"` - Reference string `db:"reference"` + Year int + Name string + Number int + Mintmark string + Mintage int + Reference string } +type MintageType int + /* DO NOT REORDER! */ const ( - TypeCirc = iota + TypeCirc MintageType = iota TypeNifc TypeProof ) @@ -38,28 +64,119 @@ const ( const ndenoms = 8 -func GetMintages(country string) (MintageData, error) { - var zero MintageData +func NewMintageType(s string) MintageType { + switch s { + case "circ": + return TypeCirc + case "nifc": + return TypeNifc + case "proof": + return TypeProof + } + /* TODO: Handle this */ + panic("TODO") +} - srows, err := db.Query(`SELECT * FROM mintages_s WHERE country = ?`, country) +func GetMintages(country string, typ MintageType) (MintageData, error) { + var ( + zero MintageData + xs []MSRow + ys []MCRow + ) + + rs, err := db.Queryx(` + SELECT * FROM mintages_s + WHERE country = ? AND type = ? + ORDER BY year, mintmark, denomination + `, country, typ) if err != nil { return zero, err } - defer srows.Close() - xs, err := scanToStructs[MSRow](srows) - if err != nil { - return zero, err + + for rs.Next() { + var x msRowInternal + if err = rs.StructScan(&x); err != nil { + return zero, err + } + + loop: + msr := MSRow{ + Year: x.Year, + Mintmark: sqlOr(x.Mintmark, ""), + References: make([]string, 0, ndenoms), + } + for i := range msr.Mintages { + msr.Mintages[i] = MintageUnknown + } + msr.Mintages[denomToIdx(x.Denomination)] = + sqlOr(x.Mintage, MintageUnknown) + if x.Reference.Valid { + msr.References = append(msr.References, x.Reference.V) + } + + for rs.Next() { + var y msRowInternal + if err = rs.StructScan(&y); err != nil { + return zero, err + } + + if x.Year != y.Year || x.Mintmark != y.Mintmark { + x = y + xs = append(xs, msr) + goto loop + } + + msr.Mintages[denomToIdx(y.Denomination)] = + sqlOr(y.Mintage, MintageUnknown) + if y.Reference.Valid { + msr.References = append(msr.References, y.Reference.V) + } + } + + xs = append(xs, msr) } - crows, err := db.Query(`SELECT * FROM mintages_c WHERE country = ?`, country) - if err != nil { + if err = rs.Err(); err != nil { return zero, err } - defer crows.Close() - ys, err := scanToStructs[MCRow](crows) + + rs, err = db.Queryx(` + SELECT * FROM mintages_c + WHERE country = ? AND type = ? + ORDER BY year, mintmark, number + `, country, typ) if err != nil { return zero, err } - return MintageData{xs, ys}, nil + for rs.Next() { + var y mcRowInternal + if err = rs.StructScan(&y); err != nil { + return zero, err + } + ys = append(ys, MCRow{ + Year: y.Year, + Name: y.Name, + Number: y.Number, + Mintmark: sqlOr(y.Mintmark, ""), + Mintage: sqlOr(y.Mintage, MintageUnknown), + Reference: sqlOr(y.Reference, ""), + }) + } + + return MintageData{xs, ys}, rs.Err() +} + +func sqlOr[T any](v sql.Null[T], dflt T) T { + if v.Valid { + return v.V + } + return dflt +} + +func denomToIdx(d float64) int { + return slices.Index([]float64{ + 0.01, 0.02, 0.05, 0.10, + 0.20, 0.50, 1.00, 2.00, + }, d) } diff --git a/src/dbx/sql/000-genesis.sql b/src/dbx/sql/000-genesis.sql index 56ae7c3..c16c6ae 100644 --- a/src/dbx/sql/000-genesis.sql +++ b/src/dbx/sql/000-genesis.sql @@ -7,30 +7,26 @@ CREATE TABLE migration ( INSERT INTO migration (id, latest) VALUES (1, -1); CREATE TABLE mintages_s ( - country CHAR(2) NOT NULL COLLATE BINARY + country CHAR(2) NOT NULL COLLATE BINARY CHECK(length(country) = 2), - type INTEGER NOT NULL -- Codes correspond to contants in mintages.go + -- Codes correspond to contants in mintages.go + type INTEGER NOT NULL CHECK(type BETWEEN 0 AND 2), - year INTEGER NOT NULL, - mintmark TEXT, - [€0,01] INTEGER, - [€0,02] INTEGER, - [€0,05] INTEGER, - [€0,10] INTEGER, - [€0,20] INTEGER, - [€0,50] INTEGER, - [€1,00] INTEGER, - [€2,00] INTEGER, - reference TEXT + year INTEGER NOT NULL, + denomination REAL NOT NULL, + mintmark TEXT, + mintage INTEGER, + reference TEXT ); CREATE TABLE mintages_c ( country CHAR(2) NOT NULL COLLATE BINARY CHECK(length(country) = 2), - type INTEGER NOT NULL -- Codes correspond to contants in mintages.go + -- Codes correspond to contants in mintages.go + type INTEGER NOT NULL CHECK(type BETWEEN 0 AND 2), - name TEXT NOT NULL, year INTEGER NOT NULL, + name TEXT NOT NULL, number INTEGER NOT NULL, mintmark TEXT, mintage INTEGER, diff --git a/src/dbx/users.go b/src/dbx/users.go index e2270db..4235b28 100644 --- a/src/dbx/users.go +++ b/src/dbx/users.go @@ -44,13 +44,13 @@ func Login(username, password string) (User, error) { password = norm.NFC.String(password) /* TODO: Pass a context here? */ - rs, err := db.Query(`SELECT * FROM users WHERE username = ?`, username) + rs, err := db.Queryx(`SELECT * FROM users WHERE username = ?`, username) if err != nil { return User{}, err } - u, err := scanToStruct[User](rs) - switch { + var u User + switch err = rs.Scan(&u); { case errors.Is(err, sql.ErrNoRows): return User{}, LoginFailed case err != nil: -- cgit v1.2.3