summaryrefslogtreecommitdiff
path: root/nodejs/database
diff options
context:
space:
mode:
authorJulien Dessaux2023-09-12 23:06:10 +0200
committerJulien Dessaux2023-09-12 23:06:39 +0200
commite52662f829cecb71ec4dd7624df948b0f7e62de9 (patch)
tree83b1a159c2718f0c90534d2fff6910b00036f650 /nodejs/database
parent[javascript] implement the api call to fetch information about all ships (diff)
downloadspacetraders-e52662f829cecb71ec4dd7624df948b0f7e62de9.tar.gz
spacetraders-e52662f829cecb71ec4dd7624df948b0f7e62de9.tar.bz2
spacetraders-e52662f829cecb71ec4dd7624df948b0f7e62de9.zip
[javascript] added database function to fetch all the ships present in a specific system
Diffstat (limited to '')
-rw-r--r--nodejs/database/002_ships.sql1
-rw-r--r--nodejs/database/ships.js10
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;