diff options
Diffstat (limited to 'src/dbx')
| -rw-r--r-- | src/dbx/.gitignore | 1 | ||||
| -rw-r--r-- | src/dbx/db.go | 239 | ||||
| -rw-r--r-- | src/dbx/mintages.go | 65 | ||||
| -rw-r--r-- | src/dbx/sql/000-genesis.sql | 46 | ||||
| -rw-r--r-- | src/dbx/users.go | 68 |
5 files changed, 419 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..fcb345e --- /dev/null +++ b/src/dbx/db.go @@ -0,0 +1,239 @@ +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/mattn/go-sqlite3" +) + +var ( + db *sql.DB + DBName string +) + +func Init(sqlDir fs.FS) { + db = Try2(sql.Open("sqlite3", DBName)) + Try(db.Ping()) + 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")) +} + +func Close() { + db.Close() +} + +func applyMigrations(dir fs.FS) error { + var latest int + migratedp := true + + rows, err := db.Query("SELECT latest FROM migration") + 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 + } + } 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 { + 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 + } + + 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 { + return err + } + _, err = tx.Exec("UPDATE migration SET latest = ? WHERE id = 1", n) + if err != nil { + return err + } + + if err := tx.Commit(); err != nil { + return err + } + log.Printf("Applied database migration ‘%s’\n", f) + } + + 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..4a6d5d3 --- /dev/null +++ b/src/dbx/mintages.go @@ -0,0 +1,65 @@ +package dbx + +type MintageData struct { + Standard []MSRow + Commemorative []MCRow +} + +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"` +} + +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"` +} + +/* DO NOT REORDER! */ +const ( + TypeCirc = iota + TypeNifc + TypeProof +) + +/* DO NOT REORDER! */ +const ( + MintageUnknown = -iota - 1 + MintageInvalid +) + +const ndenoms = 8 + +func GetMintages(country string) (MintageData, error) { + var zero MintageData + + srows, err := db.Query(`SELECT * FROM mintages_s WHERE country = ?`, country) + if err != nil { + return zero, err + } + defer srows.Close() + xs, err := scanToStructs[MSRow](srows) + if err != nil { + return zero, err + } + + crows, err := db.Query(`SELECT * FROM mintages_c WHERE country = ?`, country) + if err != nil { + return zero, err + } + defer crows.Close() + ys, err := scanToStructs[MCRow](crows) + if err != nil { + return zero, err + } + + return MintageData{xs, ys}, nil +} diff --git a/src/dbx/sql/000-genesis.sql b/src/dbx/sql/000-genesis.sql new file mode 100644 index 0000000..56ae7c3 --- /dev/null +++ b/src/dbx/sql/000-genesis.sql @@ -0,0 +1,46 @@ +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), + type INTEGER NOT NULL -- Codes correspond to contants in mintages.go + 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 +); + +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 + CHECK(type BETWEEN 0 AND 2), + name TEXT NOT NULL, + year INTEGER 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..e2270db --- /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.Query(`SELECT * FROM users WHERE username = ?`, username) + if err != nil { + return User{}, err + } + u, err := scanToStruct[User](rs) + + switch { + 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 +} |