summaryrefslogtreecommitdiffhomepage
path: root/src/dbx
diff options
context:
space:
mode:
Diffstat (limited to 'src/dbx')
-rw-r--r--src/dbx/db.go227
-rw-r--r--src/dbx/mintages.go65
-rw-r--r--src/dbx/sql/000-genesis.sql74
-rw-r--r--src/dbx/users.go64
4 files changed, 430 insertions, 0 deletions
diff --git a/src/dbx/db.go b/src/dbx/db.go
new file mode 100644
index 0000000..9086265
--- /dev/null
+++ b/src/dbx/db.go
@@ -0,0 +1,227 @@
+package dbx
+
+import (
+ "database/sql"
+ "embed"
+ "fmt"
+ "io/fs"
+ "log"
+ "path/filepath"
+ "reflect"
+ "sort"
+ "strings"
+
+ "github.com/mattn/go-sqlite3"
+)
+
+var (
+ DB *sql.DB
+ DBName string
+
+ //go:embed "sql/*.sql"
+ migrations embed.FS
+)
+
+func Init() {
+ var err error
+ if DB, err = sql.Open("sqlite3", DBName); err != nil {
+ log.Fatal(err)
+ }
+ if err = DB.Ping(); err != nil {
+ log.Fatal(err)
+ }
+
+ if err := applyMigrations("sql"); err != nil {
+ log.Fatal(err)
+ }
+
+ /* TODO: Remove debug code */
+ if err := CreateUser(User{
+ Email: "mail@thomasvoss.com",
+ Username: "Thomas",
+ Password: "69",
+ AdminP: true,
+ }); err != nil {
+ log.Fatal(err)
+ }
+ if err := CreateUser(User{
+ Email: "foo@BAR.baz",
+ Username: "Foobar",
+ Password: "420",
+ AdminP: false,
+ }); err != nil {
+ log.Fatal(err)
+ }
+ if _, err := GetMintages("ad"); err != nil {
+ log.Fatal(err)
+ }
+}
+
+func applyMigrations(dir string) 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(migrations, dir)
+ if err != nil {
+ return err
+ }
+
+ scripts := []string{}
+ for _, f := range files {
+ scripts = append(scripts, f.Name())
+ }
+
+ sort.Strings(scripts)
+ for _, f := range scripts[latest+1:] {
+ qry, err := migrations.ReadFile(filepath.Join(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’", f)
+ }
+
+ return nil
+}
+
+func scanToStructs[T any](rs *sql.Rows) ([]T, error) {
+ xs := []T{}
+ for rs.Next() {
+ x, err := scanToStruct[T](rs)
+ if err != nil {
+ return nil, err
+ }
+ xs = append(xs, x)
+ }
+ return xs, rs.Err()
+}
+
+func scanToStruct[T any](rs *sql.Rows) (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 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, ";") {
+ parts := strings.Split(tag, ";")
+ tag, dbcols := parts[0], parts[1:]
+ if tag != "array" {
+ /* TODO: This is bad… it should log something */
+ return zero, fmt.Errorf("invalid `db:\"…\"` tag ‘%s’", 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..96cc871
--- /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:"array;€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..6daad31
--- /dev/null
+++ b/src/dbx/sql/000-genesis.sql
@@ -0,0 +1,74 @@
+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
+);
+
+-- TODO: Remove dummy data
+INSERT INTO mintages_s (
+ country,
+ type,
+ year,
+ mintmark,
+ [€0,01],
+ [€0,02],
+ [€0,05],
+ [€0,10],
+ [€0,20],
+ [€0,50],
+ [€1,00],
+ [€2,00],
+ reference
+) VALUES
+ ("ad", 0, 2014, NULL, 60000, 60000, 860000, 860000, 860000, 340000, 511843, 360000, NULL),
+ ("ad", 0, 2015, NULL, 0, 0, 0, 0, 0, 0, 0, 1072400, NULL),
+ ("ad", 0, 2016, NULL, 0, 0, 0, 0, 0, 0, 2339200, 0, NULL),
+ ("ad", 0, 2017, NULL, 2582395, 1515000, 2191421, 1103000, 1213000, 968800, 17000, 794588, NULL),
+ ("ad", 0, 2018, NULL, 2430000, 2550000, 1800000, 980000, 1014000, 890000, 0, 868000, NULL),
+ ("ad", 0, 2019, NULL, 2447000, 1727000, 2100000, 1610000, 1570000, 930000, 0, 1058310, NULL),
+ ("ad", 0, 2020, NULL, 0, 0, 0, 860000, 175000, 740000, 0, 1500000, NULL),
+ ("ad", 0, 2021, NULL, 200000, 700000, 0, 1400000, 1420000, 600000, 50000, 1474500, NULL),
+ ("ad", 0, 2022, NULL, 700000, 450000, 400000, 700000, 700000, 380000, 0, 1708000, NULL),
+ ("ad", 0, 2023, NULL, 0, 0, 0, 0, 0, 0, 0, 2075250, NULL),
+ ("ad", 0, 2024, NULL, 0, 900300, 1950000, 1000000, 700000, 500000, 1050000, 1601200, NULL),
+ ("ad", 0, 2025, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
+
+CREATE TABLE users (
+ email TEXT COLLATE BINARY,
+ username TEXT COLLATE BINARY,
+ password TEXT COLLATE BINARY,
+ adminp INTEGER
+); \ No newline at end of file
diff --git a/src/dbx/users.go b/src/dbx/users.go
new file mode 100644
index 0000000..a0712ee
--- /dev/null
+++ b/src/dbx/users.go
@@ -0,0 +1,64 @@
+package dbx
+
+import (
+ "database/sql"
+ "errors"
+
+ "golang.org/x/crypto/bcrypt"
+ "golang.org/x/text/unicode/norm"
+)
+
+type User struct {
+ Email string
+ Username string
+ Password string
+ AdminP bool
+}
+
+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
+ ) VALUES (?, ?, ?, ?)
+ `, user.Email, user.Username, string(hash), user.AdminP)
+ return err
+}
+
+func Login(username, password string) (User, error) {
+ username = norm.NFC.String(username)
+ password = norm.NFC.String(password)
+
+ u := User{}
+ /* TODO: Pass a context here? */
+ err := DB.QueryRow(`SELECT * FROM users WHERE username = ?`, username).
+ Scan(&u.Email, &u.Username, &u.Password, &u.AdminP)
+
+ 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
+}