From 01263017fcd03fa9560ddb8a5013fdf786c7d609 Mon Sep 17 00:00:00 2001
From: Julien Dessaux
Date: Wed, 12 Feb 2025 23:55:18 +0100
Subject: [golang] implement ship refueling

---
 golang/pkg/database/sql/001_trading.sql | 30 ++++++++++++++++++++++++++++++
 golang/pkg/database/transactions.go     | 19 +++++++++++++++++++
 2 files changed, 49 insertions(+)
 create mode 100644 golang/pkg/database/sql/001_trading.sql
 create mode 100644 golang/pkg/database/transactions.go

(limited to 'golang/pkg/database')

diff --git a/golang/pkg/database/sql/001_trading.sql b/golang/pkg/database/sql/001_trading.sql
new file mode 100644
index 0000000..fa8cbfe
--- /dev/null
+++ b/golang/pkg/database/sql/001_trading.sql
@@ -0,0 +1,30 @@
+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 systems (
+  id INTEGER PRIMARY KEY,
+  data JSON NOT NULL
+);
+CREATE UNIQUE INDEX systems_data_symbol on systems (json_extract(data, '$.symbol'));
+
+CREATE TABLE transactions (
+  id INTEGER PRIMARY KEY,
+  data JSON NOT NULL
+);
+CREATE UNIQUE INDEX transactions_data_symbol on transactions (json_extract(data, '$.symbol'));
+CREATE INDEX transactions_data_type on transactions (json_extract(data, '$.type'));
+CREATE INDEX transactions_data_shipSymbol on transactions (json_extract(data, '$.shipSymbol'));
+CREATE INDEX transactions_data_waypointSymbol on transactions (json_extract(data, '$.waypointSymbol'));
+
+CREATE TABLE waypoints (
+  id INTEGER PRIMARY KEY,
+  data JSON NOT NULL,
+  updated DATE NOT NULL
+);
+CREATE UNIQUE INDEX waypoints_data_symbol on waypoints(json_extract(data, '$.symbol'));
diff --git a/golang/pkg/database/transactions.go b/golang/pkg/database/transactions.go
new file mode 100644
index 0000000..71719c7
--- /dev/null
+++ b/golang/pkg/database/transactions.go
@@ -0,0 +1,19 @@
+package database
+
+import (
+	"encoding/json"
+	"fmt"
+
+	"git.adyxax.org/adyxax/spacetraders/golang/pkg/model"
+)
+
+func (db *DB) AppendTransaction(transaction *model.Transaction) error {
+	data, err := json.Marshal(transaction)
+	if err != nil {
+		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 nil
+}
-- 
cgit v1.2.3