diff options
Diffstat (limited to '')
| -rw-r--r-- | nodejs/database/002_ships.sql | 1 | ||||
| -rw-r--r-- | nodejs/database/ships.js | 10 |
2 files changed, 11 insertions, 0 deletions
diff --git a/nodejs/database/002_ships.sql b/nodejs/database/002_ships.sql index 32b6ffb..402adef 100644 --- a/nodejs/database/002_ships.sql +++ b/nodejs/database/002_ships.sql @@ -3,3 +3,4 @@ CREATE TABLE ships ( data JSON NOT NULL ); CREATE UNIQUE INDEX ships_data_symbol ON ships (json_extract(data, '$.symbol')); +CREATE INDEX ships_data_nav_systemSymbol ON ships (json_extract(data, '$.nav.systemSymbol')); diff --git a/nodejs/database/ships.js b/nodejs/database/ships.js index ecf685f..f9eb668 100644 --- a/nodejs/database/ships.js +++ b/nodejs/database/ships.js @@ -2,6 +2,7 @@ import db from './db.js'; const addShipStatement = db.prepare(`INSERT INTO ships(data) VALUES (json(?));`); const getShipStatement = db.prepare(`SELECT data FROM ships WHERE data->>'symbol' = ?;`); +const getShipsAtStatement = db.prepare(`SELECT data FROM ships WHERE data->>'$.nav.systemSymbol' = ?;`); const setShipCargoStatement = db.prepare(`UPDATE ships SET data = (SELECT json_set(data, '$.cargo', json(:cargo)) FROM ships WHERE data->>'symbol' = :symbol) WHERE data->>'symbol' = :symbol;`); const setShipFuelStatement = db.prepare(`UPDATE ships SET data = (SELECT json_set(data, '$.fuel', json(:fuel)) FROM ships WHERE data->>'symbol' = :symbol) WHERE data->>'symbol' = :symbol;`); const setShipNavStatement = db.prepare(`UPDATE ships SET data = (SELECT json_set(data, '$.nav', json(:nav)) FROM ships WHERE data->>'symbol' = :symbol) WHERE data->>'symbol' = :symbol;`); @@ -15,6 +16,15 @@ export function getShip(symbol) { return JSON.parse(data.data); } +export function getShipsAt(symbol) { + const data = getShipsAtStatement.all(symbol); + if (data === undefined) { + return null; + } + return data.map(elt => JSON.parse(elt.data)); +} + + export function setShip(data) { if (getShip(data.symbol) === null) { return addShipStatement.run(JSON.stringify(data)).lastInsertRowid; |