aboutsummaryrefslogtreecommitdiffhomepage
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.go11
-rw-r--r--src/dbx/mintages.go186
-rw-r--r--src/dbx/sql/last.sql157
-rw-r--r--src/dbx/users.go7
5 files changed, 280 insertions, 82 deletions
diff --git a/src/dbx/.gitignore b/src/dbx/.gitignore
deleted file mode 100644
index d14a707..0000000
--- a/src/dbx/.gitignore
+++ /dev/null
@@ -1 +0,0 @@
-sql/last.sql \ No newline at end of file
diff --git a/src/dbx/db.go b/src/dbx/db.go
index b839531..5ee3782 100644
--- a/src/dbx/db.go
+++ b/src/dbx/db.go
@@ -1,6 +1,7 @@
package dbx
import (
+ "context"
"fmt"
"io/fs"
"log"
@@ -20,6 +21,16 @@ var (
func Init(sqlDir fs.FS) {
db = sqlx.MustConnect("sqlite3", DBName)
atexit.Register(Close)
+
+ conn := Try2(db.Conn(context.Background()))
+ Try(conn.Raw(func(driverConn any) error {
+ return driverConn.(*sqlite3.SQLiteConn).RegisterFunc("C_",
+ func(s, _ string) string {
+ return s
+ }, true)
+ }))
+ conn.Close()
+
Try(applyMigrations(sqlDir))
/* TODO: Remove debug code */
diff --git a/src/dbx/mintages.go b/src/dbx/mintages.go
index d78e59c..2223eff 100644
--- a/src/dbx/mintages.go
+++ b/src/dbx/mintages.go
@@ -1,16 +1,22 @@
package dbx
import (
+ "context"
"database/sql"
"slices"
)
-type MintageData struct {
- Standard []MSRow
- Commemorative []MCRow
+type CountryMintageData struct {
+ Standard []MSCountryRow
+ Commemorative []MCommemorative
}
-type msRowInternal struct {
+type YearMintageData struct {
+ Standard []MSYearRow
+ Commemorative []MCommemorative
+}
+
+type msRow struct {
Country string
Type MintageType
Year int
@@ -20,7 +26,21 @@ type msRowInternal struct {
Reference sql.Null[string]
}
-type mcRowInternal struct {
+type MSCountryRow struct {
+ Year int
+ Mintmark sql.Null[string]
+ Mintages [ndenoms]sql.Null[int]
+ References [ndenoms]sql.Null[string]
+}
+
+type MSYearRow struct {
+ Country string
+ Mintmark sql.Null[string]
+ Mintages [ndenoms]sql.Null[int]
+ References [ndenoms]sql.Null[string]
+}
+
+type MCommemorative struct {
Country string
Type MintageType
Year int
@@ -31,22 +51,6 @@ type mcRowInternal struct {
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! */
@@ -56,12 +60,6 @@ const (
TypeProof
)
-/* DO NOT REORDER! */
-const (
- MintageUnknown = -iota - 1
- MintageInvalid
-)
-
const ndenoms = 8
func NewMintageType(s string) MintageType {
@@ -73,64 +71,57 @@ func NewMintageType(s string) MintageType {
case "proof":
return TypeProof
}
- /* TODO: Handle this */
- panic("TODO")
+ /* We can get here if the user sends a request manually, so just
+ fallback to this */
+ return TypeCirc
}
-func GetMintages(country string, typ MintageType) (MintageData, error) {
+func GetMintagesByYear(year int, typ MintageType) (YearMintageData, error) {
var (
- zero MintageData
- xs []MSRow
- ys []MCRow
+ zero YearMintageData
+ xs []MSYearRow
+ ys []MCommemorative
)
- rs, err := db.Queryx(`
+ rs, err := db.QueryxContext(context.TODO(), `
SELECT * FROM mintages_s
- WHERE country = ? AND type = ?
- ORDER BY year, mintmark, denomination
- `, country, typ)
+ WHERE year = ? AND type = ?
+ ORDER BY country, mintmark, denomination
+ `, year, typ)
if err != nil {
return zero, err
}
for rs.Next() {
- var x msRowInternal
+ var x msRow
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)
+ msr := MSYearRow{
+ Country: x.Country,
+ Mintmark: x.Mintmark,
}
+ i := denomToIdx(x.Denomination)
+ msr.Mintages[i] = x.Mintage
+ msr.References[i] = x.Reference
for rs.Next() {
- var y msRowInternal
+ var y msRow
if err = rs.StructScan(&y); err != nil {
return zero, err
}
- if x.Year != y.Year || x.Mintmark != y.Mintmark {
+ if x.Country != y.Country || 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)
- }
+ i = denomToIdx(y.Denomination)
+ msr.Mintages[i] = y.Mintage
+ msr.References[i] = y.Reference
}
xs = append(xs, msr)
@@ -140,38 +131,77 @@ func GetMintages(country string, typ MintageType) (MintageData, error) {
return zero, err
}
- rs, err = db.Queryx(`
- SELECT * FROM mintages_c
- WHERE country = ? AND type = ?
- ORDER BY year, mintmark, number
+ db.SelectContext(context.TODO(), &ys, `
+ SELECT * FROM mintages_c
+ WHERE year = ? and type = ?
+ ORDER BY country, mintmark, number
+ `, year, typ)
+
+ return YearMintageData{xs, ys}, nil
+}
+
+func GetMintagesByCountry(country string, typ MintageType) (CountryMintageData, error) {
+ var (
+ zero CountryMintageData
+ xs []MSCountryRow
+ ys []MCommemorative
+ )
+
+ rs, err := db.QueryxContext(context.TODO(), `
+ 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 y mcRowInternal
- if err = rs.StructScan(&y); err != nil {
+ var x msRow
+ if err = rs.StructScan(&x); 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()
-}
+ loop:
+ msr := MSCountryRow{
+ Year: x.Year,
+ Mintmark: x.Mintmark,
+ }
+ i := denomToIdx(x.Denomination)
+ msr.Mintages[i] = x.Mintage
+ msr.References[i] = x.Reference
+
+ for rs.Next() {
+ var y msRow
+ 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
+ }
+
+ i = denomToIdx(y.Denomination)
+ msr.Mintages[i] = y.Mintage
+ msr.References[i] = y.Reference
+ }
-func sqlOr[T any](v sql.Null[T], dflt T) T {
- if v.Valid {
- return v.V
+ xs = append(xs, msr)
}
- return dflt
+
+ if err = rs.Err(); err != nil {
+ return zero, err
+ }
+
+ db.SelectContext(context.TODO(), &ys, `
+ SELECT * FROM mintages_c
+ WHERE country = ? and type = ?
+ ORDER BY year, mintmark, number
+ `, country, typ)
+
+ return CountryMintageData{xs, ys}, rs.Err()
}
func denomToIdx(d float64) int {
diff --git a/src/dbx/sql/last.sql b/src/dbx/sql/last.sql
new file mode 100644
index 0000000..22ebab8
--- /dev/null
+++ b/src/dbx/sql/last.sql
@@ -0,0 +1,157 @@
+DELETE FROM mintages_s;
+DELETE FROM mintages_c;
+
+INSERT INTO mintages_s (
+ country,
+ type,
+ year,
+ denomination,
+ mintmark,
+ mintage,
+ reference
+) VALUES
+ ('at', 0, 2017, 0.01, NULL, 37700000, NULL),
+ ('at', 0, 2017, 0.02, NULL, 57200000, NULL),
+ ('at', 0, 2017, 0.05, NULL, 35200000, NULL),
+ ('at', 0, 2017, 0.10, NULL, 39500000, NULL),
+ ('at', 0, 2017, 0.20, NULL, 30000000, NULL),
+ ('at', 0, 2017, 0.50, NULL, 15000000, NULL),
+ ('at', 0, 2017, 1.00, NULL, 8000000, NULL),
+ ('at', 0, 2017, 2.00, NULL, 17700000, NULL),
+ ('de', 0, 2017, 0.01, 'A', 81600000, NULL),
+ ('de', 0, 2017, 0.02, 'A', 72200000, NULL),
+ ('de', 0, 2017, 0.05, 'A', 30000000, NULL),
+ ('de', 0, 2017, 0.10, 'A', 25200000, NULL),
+ ('de', 0, 2017, 0.20, 'A', 21600000, NULL),
+ ('de', 0, 2017, 0.50, 'A', 0, NULL),
+ ('de', 0, 2017, 1.00, 'A', 0, NULL),
+ ('de', 0, 2017, 2.00, 'A', 18120000, NULL),
+ ('de', 0, 2017, 0.01, 'D', 85680000, NULL),
+ ('de', 0, 2017, 0.02, 'D', 75810000, NULL),
+ ('de', 0, 2017, 0.05, 'D', 31500000, NULL),
+ ('de', 0, 2017, 0.10, 'D', 26460000, NULL),
+ ('de', 0, 2017, 0.20, 'D', 22680000, NULL),
+ ('de', 0, 2017, 0.50, 'D', 0, NULL),
+ ('de', 0, 2017, 1.00, 'D', 0, NULL),
+ ('de', 0, 2017, 2.00, 'D', 19110000, NULL),
+ ('ad', 0, 2014, 0.01, NULL, 60000, NULL),
+ ('ad', 0, 2014, 0.02, NULL, 60000, NULL),
+ ('ad', 0, 2014, 0.05, NULL, 860000, NULL),
+ ('ad', 0, 2014, 0.10, NULL, 860000, NULL),
+ ('ad', 0, 2014, 0.20, NULL, 860000, NULL),
+ ('ad', 0, 2014, 0.50, NULL, 340000, NULL),
+ ('ad', 0, 2014, 1.00, NULL, 511843, NULL),
+ ('ad', 0, 2014, 2.00, NULL, 360000, NULL),
+ ('ad', 0, 2015, 0.01, NULL, 0, NULL),
+ ('ad', 0, 2015, 0.02, NULL, 0, NULL),
+ ('ad', 0, 2015, 0.05, NULL, 0, NULL),
+ ('ad', 0, 2015, 0.10, NULL, 0, NULL),
+ ('ad', 0, 2015, 0.20, NULL, 0, NULL),
+ ('ad', 0, 2015, 0.50, NULL, 0, NULL),
+ ('ad', 0, 2015, 1.00, NULL, 0, NULL),
+ ('ad', 0, 2015, 2.00, NULL, 1072400, NULL),
+ ('ad', 0, 2016, 0.01, NULL, 0, NULL),
+ ('ad', 0, 2016, 0.02, NULL, 0, NULL),
+ ('ad', 0, 2016, 0.05, NULL, 0, NULL),
+ ('ad', 0, 2016, 0.10, NULL, 0, NULL),
+ ('ad', 0, 2016, 0.20, NULL, 0, NULL),
+ ('ad', 0, 2016, 0.50, NULL, 0, NULL),
+ ('ad', 0, 2016, 1.00, NULL, 2339200, NULL),
+ ('ad', 0, 2016, 2.00, NULL, 0, NULL),
+ ('ad', 0, 2017, 0.01, NULL, 2582395, NULL),
+ ('ad', 0, 2017, 0.02, NULL, 1515000, NULL),
+ ('ad', 0, 2017, 0.05, NULL, 2191421, NULL),
+ ('ad', 0, 2017, 0.10, NULL, 1103000, NULL),
+ ('ad', 0, 2017, 0.20, NULL, 1213000, NULL),
+ ('ad', 0, 2017, 0.50, NULL, 968800, NULL),
+ ('ad', 0, 2017, 1.00, NULL, 17000, NULL),
+ ('ad', 0, 2017, 2.00, NULL, 794588, NULL),
+ ('ad', 0, 2018, 0.01, NULL, 2430000, NULL),
+ ('ad', 0, 2018, 0.02, NULL, 2550000, NULL),
+ ('ad', 0, 2018, 0.05, NULL, 1800000, NULL),
+ ('ad', 0, 2018, 0.10, NULL, 980000, NULL),
+ ('ad', 0, 2018, 0.20, NULL, 1014000, NULL),
+ ('ad', 0, 2018, 0.50, NULL, 890000, NULL),
+ ('ad', 0, 2018, 1.00, NULL, 0, NULL),
+ ('ad', 0, 2018, 2.00, NULL, 868000, NULL),
+ ('ad', 0, 2019, 0.01, NULL, 2447000, NULL),
+ ('ad', 0, 2019, 0.02, NULL, 1727000, NULL),
+ ('ad', 0, 2019, 0.05, NULL, 2100000, NULL),
+ ('ad', 0, 2019, 0.10, NULL, 1610000, NULL),
+ ('ad', 0, 2019, 0.20, NULL, 1570000, NULL),
+ ('ad', 0, 2019, 0.50, NULL, 930000, NULL),
+ ('ad', 0, 2019, 1.00, NULL, 0, NULL),
+ ('ad', 0, 2019, 2.00, NULL, 1058310, NULL),
+ ('ad', 0, 2020, 0.01, NULL, 0, NULL),
+ ('ad', 0, 2020, 0.02, NULL, 0, NULL),
+ ('ad', 0, 2020, 0.05, NULL, 0, NULL),
+ ('ad', 0, 2020, 0.10, NULL, 860000, NULL),
+ ('ad', 0, 2020, 0.20, NULL, 175000, NULL),
+ ('ad', 0, 2020, 0.50, NULL, 740000, NULL),
+ ('ad', 0, 2020, 1.00, NULL, 0, NULL),
+ ('ad', 0, 2020, 2.00, NULL, 1500000, NULL),
+ ('ad', 0, 2021, 0.01, NULL, 200000, NULL),
+ ('ad', 0, 2021, 0.02, NULL, 700000, NULL),
+ ('ad', 0, 2021, 0.05, NULL, 0, NULL),
+ ('ad', 0, 2021, 0.10, NULL, 1400000, NULL),
+ ('ad', 0, 2021, 0.20, NULL, 1420000, NULL),
+ ('ad', 0, 2021, 0.50, NULL, 600000, NULL),
+ ('ad', 0, 2021, 1.00, NULL, 50000, NULL),
+ ('ad', 0, 2021, 2.00, NULL, 1474500, NULL),
+ ('ad', 0, 2022, 0.01, NULL, 700000, NULL),
+ ('ad', 0, 2022, 0.02, NULL, 450000, NULL),
+ ('ad', 0, 2022, 0.05, NULL, 400000, NULL),
+ ('ad', 0, 2022, 0.10, NULL, 700000, NULL),
+ ('ad', 0, 2022, 0.20, NULL, 700000, NULL),
+ ('ad', 0, 2022, 0.50, NULL, 380000, NULL),
+ ('ad', 0, 2022, 1.00, NULL, 0, NULL),
+ ('ad', 0, 2022, 2.00, NULL, 1708000, NULL),
+ ('ad', 0, 2023, 0.01, NULL, 0, NULL),
+ ('ad', 0, 2023, 0.02, NULL, 0, NULL),
+ ('ad', 0, 2023, 0.05, NULL, 0, NULL),
+ ('ad', 0, 2023, 0.10, NULL, 0, NULL),
+ ('ad', 0, 2023, 0.20, NULL, 0, NULL),
+ ('ad', 0, 2023, 0.50, NULL, 0, NULL),
+ ('ad', 0, 2023, 1.00, NULL, 0, NULL),
+ ('ad', 0, 2023, 2.00, NULL, 2075250, NULL),
+ ('ad', 0, 2024, 0.01, NULL, 0, NULL),
+ ('ad', 0, 2024, 0.02, NULL, 900300, NULL),
+ ('ad', 0, 2024, 0.05, NULL, 1950000, NULL),
+ ('ad', 0, 2024, 0.10, NULL, 1000000, NULL),
+ ('ad', 0, 2024, 0.20, NULL, 700000, NULL),
+ ('ad', 0, 2024, 0.50, NULL, 500000, NULL),
+ ('ad', 0, 2024, 1.00, NULL, 1050000, NULL),
+ ('ad', 0, 2024, 2.00, NULL, 1601200, NULL);
+
+INSERT INTO mintages_c (
+ country,
+ type,
+ year,
+ name,
+ number,
+ mintmark,
+ mintage,
+ reference
+) VALUES
+ ('de', 0, 2015, C_('Hessen', 'CC Name'), 1, 'A', 6000000, NULL),
+ ('de', 0, 2015, C_('German Reunification', 'CC Name'), 2, 'A', 6000000, NULL),
+ ('de', 0, 2015, C_('EU Flag', 'CC Name'), 3, 'A', 6000000, NULL),
+ ('de', 0, 2015, C_('Hessen', 'CC Name'), 1, 'D', 6300000, NULL),
+ ('de', 0, 2015, C_('German Reunification', 'CC Name'), 2, 'D', 6300000, NULL),
+ ('de', 0, 2015, C_('EU Flag', 'CC Name'), 3, 'D', 6300000, NULL),
+ ('de', 0, 2015, C_('Hessen', 'CC Name'), 1, 'F', 7200000, NULL),
+ ('de', 0, 2015, C_('German Reunification', 'CC Name'), 2, 'F', 7200000, NULL),
+ ('de', 0, 2015, C_('EU Flag', 'CC Name'), 3, 'F', 7200000, NULL),
+ ('de', 0, 2015, C_('Hessen', 'CC Name'), 1, 'G', 4200000, NULL),
+ ('de', 0, 2015, C_('German Reunification', 'CC Name'), 2, 'G', 4200000, NULL),
+ ('de', 0, 2015, C_('EU Flag', 'CC Name'), 3, 'G', 4200000, NULL),
+ ('de', 0, 2015, C_('Hessen', 'CC Name'), 1, 'J', 6300000, NULL),
+ ('de', 0, 2015, C_('German Reunification', 'CC Name'), 2, 'J', 6300000, NULL),
+ ('de', 0, 2015, C_('EU Flag', 'CC Name'), 3, 'J', 6300000, NULL),
+ ('sk', 0, 2014, C_('Slovak Republic to the EU', 'CC Name'), 1, NULL, 1000000, NULL),
+ ('sk', 0, 2015, C_('Ľudovít Štúr', 'CC Name'), 1, NULL, 1000000, NULL),
+ ('sk', 0, 2015, C_('EU Flag', 'CC Name'), 2, NULL, 1000000, NULL),
+ ('nl', 0, 2015, C_('EU Flag', 'CC Name'), 2, NULL, NULL, NULL),
+ ('fr', 0, 2015, C_('Peace and security', 'CC Name'), 1, NULL, 4000000, NULL),
+ ('fr', 0, 2015, C_('Fête de la Fédération', 'CC Name'), 2, NULL, 4000000, NULL),
+ ('fr', 0, 2015, C_('EU Flag', 'CC Name'), 3, NULL, 4000000, NULL); \ No newline at end of file
diff --git a/src/dbx/users.go b/src/dbx/users.go
index 4235b28..bf78dcd 100644
--- a/src/dbx/users.go
+++ b/src/dbx/users.go
@@ -1,6 +1,7 @@
package dbx
import (
+ "context"
"database/sql"
"errors"
@@ -27,7 +28,7 @@ func CreateUser(user User) error {
return err
}
- _, err = db.Exec(`
+ _, err = db.ExecContext(context.TODO(), `
INSERT INTO users (
email,
username,
@@ -43,8 +44,8 @@ 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)
+ rs, err := db.QueryxContext(context.TODO(),
+ `SELECT * FROM users WHERE username = ?`, username)
if err != nil {
return User{}, err
}