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 ++++++++++++++++++++++++++++++
 1 file changed, 30 insertions(+)
 create mode 100644 golang/pkg/database/sql/001_trading.sql

(limited to 'golang/pkg/database/sql')

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'));
-- 
cgit v1.2.3