diff options
Diffstat (limited to '')
-rw-r--r-- | nodejs/database/004_markets.sql | 7 | ||||
-rw-r--r-- | nodejs/database/db.js | 1 | ||||
-rw-r--r-- | nodejs/database/markets.js | 34 |
3 files changed, 42 insertions, 0 deletions
diff --git a/nodejs/database/004_markets.sql b/nodejs/database/004_markets.sql new file mode 100644 index 0000000..668e4d7 --- /dev/null +++ b/nodejs/database/004_markets.sql @@ -0,0 +1,7 @@ +CREATE TABLE markets ( + id INTEGER PRIMARY KEY, + system TEXT NOT NULL, + data JSON NOT NULL +); +CREATE INDEX markets_system on markets (system); +CREATE UNIQUE INDEX markets_symbol on markets(json_extract(data, '$.symbol')); diff --git a/nodejs/database/db.js b/nodejs/database/db.js index 78fa0dd..4438fec 100644 --- a/nodejs/database/db.js +++ b/nodejs/database/db.js @@ -6,6 +6,7 @@ const allMigrations = [ 'database/001_systems.sql', 'database/002_ships.sql', 'database/003_surveys.sql', + 'database/004_markets.sql', ]; const db = new Database( diff --git a/nodejs/database/markets.js b/nodejs/database/markets.js new file mode 100644 index 0000000..522f57f --- /dev/null +++ b/nodejs/database/markets.js @@ -0,0 +1,34 @@ +import db from './db.js'; +import * as utils from '../lib/utils.js'; + +const addMarketStatement = db.prepare(`INSERT INTO markets(system, data) VALUES (?, json(?));`); +const getMarketAtWaypointStatement = db.prepare(`SELECT data FROM markets WHERE data->>'symbol' = ?;`); +const getMarketsInSystemStatement = db.prepare(`SELECT data FROM markets WHERE system = ?;`); +const updateMarketStatement = db.prepare(`UPDATE markets SET data = json(:data) WHERE data->>'symbol' = :symbol;`); + +export function getMarketAtWaypoint(symbol) { + const data = getMarketAtWaypointStatement.get(symbol); + if (data === undefined) { + return null; + } + return JSON.parse(data.data); +} + +export function getMarketsInSystem(symbol) { + const data = getMarketsInSystemStatement.get(symbol); + if (data === undefined) { + return null; + } + return JSON.parse(data.data); +} + +export function setMarket(data) { + if (getMarketAtWaypoint(data.symbol) === null) { + const system = utils.systemFromWaypoint(data.symbol); + return addMarketStatement.run(system, JSON.stringify(data)).lastInsertRowid; + } + return updateMarketStatement.run({ + data: JSON.stringify(data), + symbol: data.symbol, + }).changes; +} |