diff options
Diffstat (limited to 'src/dbx')
-rw-r--r-- | src/dbx/.gitignore | 1 | ||||
-rw-r--r-- | src/dbx/db.go | 233 | ||||
-rw-r--r-- | src/dbx/mintages.go | 182 | ||||
-rw-r--r-- | src/dbx/sql/000-genesis.sql | 42 | ||||
-rw-r--r-- | src/dbx/users.go | 68 |
5 files changed, 526 insertions, 0 deletions
diff --git a/src/dbx/.gitignore b/src/dbx/.gitignore new file mode 100644 index 0000000..d14a707 --- /dev/null +++ b/src/dbx/.gitignore @@ -0,0 +1 @@ +sql/last.sql
\ No newline at end of file diff --git a/src/dbx/db.go b/src/dbx/db.go new file mode 100644 index 0000000..b8112b9 --- /dev/null +++ b/src/dbx/db.go @@ -0,0 +1,233 @@ +package dbx + +import ( + "fmt" + "io/fs" + "log" + "sort" + + "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 *sqlx.DB + DBName string +) + +func Init(sqlDir fs.FS) { + db = sqlx.MustConnect("sqlite3", DBName) + atexit.Register(Close) + Try(applyMigrations(sqlDir)) + + /* TODO: Remove debug code */ + Try(CreateUser(User{ + Email: "mail@thomasvoss.com", + Username: "Thomas", + Password: "69", + AdminP: true, + })) + Try(CreateUser(User{ + Email: "foo@BAR.baz", + Username: "Foobar", + Password: "420", + AdminP: false, + })) + Try2(GetMintages("ad", TypeCirc)) +} + +func Close() { + db.Close() +} + +func applyMigrations(dir fs.FS) error { + var latest int + migratedp := true + + 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 */ + if ok && e.Error() == "no such table: migration" { + migratedp = false + } else { + return err + } + } + + if !migratedp { + latest = -1 + } + + files, err := fs.ReadDir(dir, ".") + if err != nil { + return err + } + + var ( + last string + scripts []string + ) + + for _, f := range files { + if n := f.Name(); n == "last.sql" { + last = n + } else { + scripts = append(scripts, f.Name()) + } + } + + sort.Strings(scripts) + for _, f := range scripts[latest+1:] { + qry, err := fs.ReadFile(dir, f) + if err != nil { + return err + } + + tx, err := db.Begin() + if err != nil { + return err + } + + var n int + if _, err = fmt.Sscanf(f, "%d", &n); err != nil { + goto error + } + + 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 { + goto error + } + + if err = tx.Commit(); err != nil { + goto error + } + + log.Printf("Applied database migration ‘%s’\n", f) + continue + + error: + tx.Rollback() + return err + } + + if last != "" { + qry, err := fs.ReadFile(dir, last) + if err != nil { + return err + } + if _, err := db.Exec(string(qry)); err != nil { + return fmt.Errorf("error in ‘%s’: %w", last, err) + } + log.Printf("Ran ‘%s’\n", last) + } + + 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) { + if val == nil { + fv.Set(reflect.Zero(fv.Type())) + return + } + v := reflect.ValueOf(val) + if v.Type().ConvertibleTo(fv.Type()) { + fv.Set(v.Convert(fv.Type())) + } +} */ diff --git a/src/dbx/mintages.go b/src/dbx/mintages.go new file mode 100644 index 0000000..d78e59c --- /dev/null +++ b/src/dbx/mintages.go @@ -0,0 +1,182 @@ +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 { + Year int + Mintmark string + Mintages [ndenoms]int + References []string +} + +type MCRow struct { + Year int + Name string + Number int + Mintmark string + Mintage int + Reference string +} + +type MintageType int + +/* DO NOT REORDER! */ +const ( + TypeCirc MintageType = iota + TypeNifc + TypeProof +) + +/* DO NOT REORDER! */ +const ( + MintageUnknown = -iota - 1 + MintageInvalid +) + +const ndenoms = 8 + +func NewMintageType(s string) MintageType { + switch s { + case "circ": + return TypeCirc + case "nifc": + return TypeNifc + case "proof": + return TypeProof + } + /* TODO: Handle this */ + panic("TODO") +} + +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 + } + + 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) + } + + if err = rs.Err(); err != nil { + return zero, err + } + + 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 + } + + 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 new file mode 100644 index 0000000..c16c6ae --- /dev/null +++ b/src/dbx/sql/000-genesis.sql @@ -0,0 +1,42 @@ +PRAGMA encoding = "UTF-8"; + +CREATE TABLE migration ( + id INTEGER PRIMARY KEY CHECK (id = 1), + latest INTEGER +); +INSERT INTO migration (id, latest) VALUES (1, -1); + +CREATE TABLE mintages_s ( + country CHAR(2) NOT NULL COLLATE BINARY + CHECK(length(country) = 2), + -- Codes correspond to contants in mintages.go + type INTEGER NOT NULL + CHECK(type BETWEEN 0 AND 2), + 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), + -- Codes correspond to contants in mintages.go + type INTEGER NOT NULL + CHECK(type BETWEEN 0 AND 2), + year INTEGER NOT NULL, + name TEXT NOT NULL, + number INTEGER NOT NULL, + mintmark TEXT, + mintage INTEGER, + reference TEXT +); + +CREATE TABLE users ( + email TEXT COLLATE BINARY, + username TEXT COLLATE BINARY, + password TEXT COLLATE BINARY, + adminp INTEGER, + translates TEXT COLLATE BINARY +);
\ No newline at end of file diff --git a/src/dbx/users.go b/src/dbx/users.go new file mode 100644 index 0000000..4235b28 --- /dev/null +++ b/src/dbx/users.go @@ -0,0 +1,68 @@ +package dbx + +import ( + "database/sql" + "errors" + + "golang.org/x/crypto/bcrypt" + "golang.org/x/text/unicode/norm" +) + +type User struct { + Email string `db:"email"` + Username string `db:"username"` + Password string `db:"password"` + AdminP bool `db:"adminp"` + Translates string `db:"translates"` +} + +var LoginFailed = errors.New("No user with the given username and password") + +func CreateUser(user User) error { + user.Username = norm.NFC.String(user.Username) + user.Password = norm.NFC.String(user.Password) + + hash, err := bcrypt.GenerateFromPassword([]byte(user.Password), 15) + if err != nil { + return err + } + + _, err = db.Exec(` + INSERT INTO users ( + email, + username, + password, + adminp, + translates + ) VALUES (?, ?, ?, ?, ?) + `, user.Email, user.Username, string(hash), user.AdminP, user.Translates) + return err +} + +func Login(username, password string) (User, error) { + username = norm.NFC.String(username) + password = norm.NFC.String(password) + + /* TODO: Pass a context here? */ + rs, err := db.Queryx(`SELECT * FROM users WHERE username = ?`, username) + if err != nil { + return User{}, err + } + + var u User + switch err = rs.Scan(&u); { + case errors.Is(err, sql.ErrNoRows): + return User{}, LoginFailed + case err != nil: + return User{}, err + } + + err = bcrypt.CompareHashAndPassword([]byte(u.Password), []byte(password)) + switch { + case errors.Is(err, bcrypt.ErrMismatchedHashAndPassword): + return User{}, LoginFailed + case err != nil: + return User{}, err + } + return u, nil +} |