summaryrefslogtreecommitdiffhomepage
path: root/src/dbx
diff options
context:
space:
mode:
Diffstat (limited to 'src/dbx')
-rw-r--r--src/dbx/.gitignore1
-rw-r--r--src/dbx/db.go233
-rw-r--r--src/dbx/mintages.go182
-rw-r--r--src/dbx/sql/000-genesis.sql42
-rw-r--r--src/dbx/users.go68
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
+}