[javascript] added database function to fetch all the ships present in a specific system

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;