summaryrefslogtreecommitdiff
path: root/golang/pkg/database
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--golang/pkg/database/markets.go42
-rw-r--r--golang/pkg/database/shipyards.go42
-rw-r--r--golang/pkg/database/sql/001_trading.sql9
-rw-r--r--golang/pkg/database/systems.go32
-rw-r--r--golang/pkg/database/transactions.go2
5 files changed, 119 insertions, 8 deletions
diff --git a/golang/pkg/database/markets.go b/golang/pkg/database/markets.go
new file mode 100644
index 0000000..11498d2
--- /dev/null
+++ b/golang/pkg/database/markets.go
@@ -0,0 +1,42 @@
+package database
+
+import (
+ "database/sql"
+ "encoding/json"
+ "fmt"
+ "time"
+
+ "git.adyxax.org/adyxax/spacetraders/golang/pkg/model"
+)
+
+func (db *DB) LoadMarket(symbol string) (*model.Market, error) {
+ var buf []byte
+ if err := db.QueryRow(`SELECT data FROM markets WHERE data->>'symbol' = ?;`, symbol).Scan(&buf); err != nil {
+ return nil, fmt.Errorf("failed to query row: %w", err)
+ }
+ var market model.Market
+ if err := json.Unmarshal(buf, &market); err != nil {
+ return nil, fmt.Errorf("failed to unmarshal market: %w", err)
+ }
+ return &market, nil
+}
+
+func (db *DB) SaveMarket(market *model.Market) error {
+ data, err := json.Marshal(market)
+ if err != nil {
+ return fmt.Errorf("failed to marshal market: %w", err)
+ }
+ _, err = db.Exec(
+ `INSERT INTO markets(data, updated)
+ VALUES (json(:data), :updated)
+ ON CONFLICT DO UPDATE SET data = :data, updated = :updated
+ WHERE data->>'symbol' = :symbol;`,
+ sql.Named("data", data),
+ sql.Named("symbol", market.Symbol),
+ sql.Named("updated", time.Now()),
+ )
+ if err != nil {
+ return fmt.Errorf("failed to exec: %w", err)
+ }
+ return nil
+}
diff --git a/golang/pkg/database/shipyards.go b/golang/pkg/database/shipyards.go
new file mode 100644
index 0000000..4d44f9a
--- /dev/null
+++ b/golang/pkg/database/shipyards.go
@@ -0,0 +1,42 @@
+package database
+
+import (
+ "database/sql"
+ "encoding/json"
+ "fmt"
+ "time"
+
+ "git.adyxax.org/adyxax/spacetraders/golang/pkg/model"
+)
+
+func (db *DB) LoadShipyard(symbol string) (*model.Shipyard, error) {
+ var buf []byte
+ if err := db.QueryRow(`SELECT data FROM shipyards WHERE data->>'symbol' = ?;`, symbol).Scan(&buf); err != nil {
+ return nil, fmt.Errorf("failed to query row: %w", err)
+ }
+ var shipyard model.Shipyard
+ if err := json.Unmarshal(buf, &shipyard); err != nil {
+ return nil, fmt.Errorf("failed to unmarshal shipyard: %w", err)
+ }
+ return &shipyard, nil
+}
+
+func (db *DB) SaveShipyard(shipyard *model.Shipyard) error {
+ data, err := json.Marshal(shipyard)
+ if err != nil {
+ return fmt.Errorf("failed to marshal shipyard: %w", err)
+ }
+ _, err = db.Exec(
+ `INSERT INTO shipyards(data, updated)
+ VALUES (json(:data), :updated)
+ ON CONFLICT DO UPDATE SET data = :data, updated = :updated
+ WHERE data->>'symbol' = :symbol;`,
+ sql.Named("data", data),
+ sql.Named("symbol", shipyard.Symbol),
+ sql.Named("updated", time.Now()),
+ )
+ if err != nil {
+ return fmt.Errorf("failed to exec: %w", err)
+ }
+ return nil
+}
diff --git a/golang/pkg/database/sql/001_trading.sql b/golang/pkg/database/sql/001_trading.sql
index 68f2568..e218962 100644
--- a/golang/pkg/database/sql/001_trading.sql
+++ b/golang/pkg/database/sql/001_trading.sql
@@ -4,13 +4,16 @@ CREATE TABLE agents (
);
CREATE TABLE markets (
id INTEGER PRIMARY KEY,
- systemSymbol TEXT NOT NULL,
data JSON NOT NULL,
updated DATE NOT NULL
);
-CREATE INDEX markets_systemSymbol on markets (systemSymbol);
CREATE UNIQUE INDEX markets_data_symbol on markets(json_extract(data, '$.symbol'));
-
+CREATE TABLE shipyards (
+ id INTEGER PRIMARY KEY,
+ data JSON NOT NULL,
+ updated DATE DEFAULT NULL
+);
+CREATE UNIQUE INDEX shipyards_data_symbol on shipyards (json_extract(data, '$.symbol'));
CREATE TABLE systems (
id INTEGER PRIMARY KEY,
data JSON NOT NULL
diff --git a/golang/pkg/database/systems.go b/golang/pkg/database/systems.go
index 858c3dd..b5d533b 100644
--- a/golang/pkg/database/systems.go
+++ b/golang/pkg/database/systems.go
@@ -12,7 +12,7 @@ import (
func (db *DB) LoadSystem(symbol string) (*model.System, error) {
var buf []byte
if err := db.QueryRow(`SELECT data FROM systems WHERE data->>'symbol' = ?;`, symbol).Scan(&buf); err != nil {
- return nil, fmt.Errorf("failed to query system: %w", err)
+ return nil, fmt.Errorf("failed to query row: %w", err)
}
var system model.System
if err := json.Unmarshal(buf, &system); err != nil {
@@ -27,7 +27,7 @@ func (db *DB) SaveSystem(system *model.System) error {
return fmt.Errorf("failed to marshal system: %w", err)
}
if _, err := db.Exec(`INSERT INTO systems(data) VALUES (json(?));`, data); err != nil {
- return fmt.Errorf("failed to append system: %w", err)
+ return fmt.Errorf("failed to exec: %w", err)
}
return nil
}
@@ -36,7 +36,7 @@ func (db *DB) SaveSystem(system *model.System) error {
func (db *DB) LoadWaypoint(symbol string) (*model.Waypoint, error) {
var buf []byte
if err := db.QueryRow(`SELECT data FROM waypoints WHERE data->>'symbol' = ?;`, symbol).Scan(&buf); err != nil {
- return nil, fmt.Errorf("failed to query waypoint: %w", err)
+ return nil, fmt.Errorf("failed to query row: %w", err)
}
var waypoint model.Waypoint
if err := json.Unmarshal(buf, &waypoint); err != nil {
@@ -45,13 +45,37 @@ func (db *DB) LoadWaypoint(symbol string) (*model.Waypoint, error) {
return &waypoint, nil
}
+func (db *DB) LoadWaypointsInSystem(systemSymbol string) ([]model.Waypoint, error) {
+ rows, err := db.Query(`SELECT data FROM waypoints WHERE data->>'systemSymbol' = ?;`, systemSymbol)
+ if err != nil {
+ return nil, fmt.Errorf("failed to query rows: %w", err)
+ }
+ defer rows.Close()
+ waypoints := make([]model.Waypoint, 0)
+ for rows.Next() {
+ var buf []byte
+ if err := rows.Scan(&buf); err != nil {
+ return nil, fmt.Errorf("failed to scan row: %w", err)
+ }
+ var waypoint model.Waypoint
+ if err := json.Unmarshal(buf, &waypoint); err != nil {
+ return nil, fmt.Errorf("failed to unmarshal waypoint: %w", err)
+ }
+ waypoints = append(waypoints, waypoint)
+ }
+ if err := rows.Err(); err != nil {
+ return nil, fmt.Errorf("failed to scan rows: %w", err)
+ }
+ return waypoints, nil
+}
+
func (db *DB) SaveWaypoint(waypoint *model.Waypoint) error {
data, err := json.Marshal(waypoint)
if err != nil {
return fmt.Errorf("failed to marshal waypoint: %w", err)
}
if _, err := db.Exec(`INSERT INTO waypoints(data, updated) VALUES (json(?), ?);`, data, time.Now()); err != nil {
- return fmt.Errorf("failed to append waypoint: %w", err)
+ return fmt.Errorf("failed to exec: %w", err)
}
return nil
}
diff --git a/golang/pkg/database/transactions.go b/golang/pkg/database/transactions.go
index 71719c7..08278a1 100644
--- a/golang/pkg/database/transactions.go
+++ b/golang/pkg/database/transactions.go
@@ -13,7 +13,7 @@ func (db *DB) AppendTransaction(transaction *model.Transaction) error {
return fmt.Errorf("failed to marshal transaction: %w", err)
}
if _, err := db.Exec(`INSERT INTO transactions(data) VALUES (json(?));`, data); err != nil {
- return fmt.Errorf("failed to append transaction: %w", err)
+ return fmt.Errorf("failed to exec: %w", err)
}
return nil
}
generated by cgit v1.2.3 (git 2.39.1) at 2025-02-22 14:04:28 +0000