From f89af1145d350b1516659f6b2c7ab1e49522da19 Mon Sep 17 00:00:00 2001 From: Julien Dessaux Date: Sun, 3 Sep 2023 23:01:18 +0200 Subject: [javascript] refactored ships and systems database interface --- nodejs/database/002_ships.sql | 3 +- nodejs/database/ships.js | 84 +++++++++++++------------------------------ nodejs/database/systems.js | 65 ++++++++------------------------- 3 files changed, 40 insertions(+), 112 deletions(-) (limited to 'nodejs/database') diff --git a/nodejs/database/002_ships.sql b/nodejs/database/002_ships.sql index 3ead742..32b6ffb 100644 --- a/nodejs/database/002_ships.sql +++ b/nodejs/database/002_ships.sql @@ -1,6 +1,5 @@ CREATE TABLE ships ( id INTEGER PRIMARY KEY, - data JSON NOT NULL, - updated DATE DEFAULT NULL + data JSON NOT NULL ); CREATE UNIQUE INDEX ships_data_symbol ON ships (json_extract(data, '$.symbol')); diff --git a/nodejs/database/ships.js b/nodejs/database/ships.js index 0220be6..ecf685f 100644 --- a/nodejs/database/ships.js +++ b/nodejs/database/ships.js @@ -1,82 +1,48 @@ 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 setShipStatement = db.prepare(`INSERT INTO ships(data, updated) VALUES (json(?), ?);`); -const setShipCargoStatement = db.prepare(`UPDATE ships SET data = (SELECT json_set(data, '$.cargo', json(:cargo)) FROM ships WHERE data->>'symbol' = :symbol), updated = :date 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), updated = :date 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), updated = :date WHERE data->>'symbol' = :symbol;`); -const updateShipStatement = db.prepare(`UPDATE ships SET data = json(:data), updated = :date WHERE data->>'symbol' = :symbol;`); +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;`); +const updateShipStatement = db.prepare(`UPDATE ships SET data = json(:data) WHERE data->>'symbol' = :symbol;`); export function getShip(symbol) { - try { - const data = getShipStatement.get(symbol); - if (data === undefined) { - return null; - } - return JSON.parse(data.data); - } catch (err) { - console.log(err); + const data = getShipStatement.get(symbol); + if (data === undefined) { return null; } + return JSON.parse(data.data); } export function setShip(data) { if (getShip(data.symbol) === null) { - try { - return setShipStatement.run(JSON.stringify(data), new Date().toISOString()).lastInsertRowid; - } catch (err) { - console.log(err); - return null; - } + return addShipStatement.run(JSON.stringify(data)).lastInsertRowid; } else { - try { - return updateShipStatement.run({ - data: JSON.stringify(data), - date: new Date().toISOString(), - symbol: data.symbol, - }).changes; - } catch (err) { - console.log(err); - return null; - } + return updateShipStatement.run({ + data: JSON.stringify(data), + symbol: data.symbol, + }).changes; } } export function setShipCargo(symbol, cargo) { - try { - setShipCargoStatement.run({ - cargo: JSON.stringify(cargo), - date: new Date().toISOString(), - symbol: symbol, - }).changes; - } catch (err) { - console.log(err); - return null; - } + return setShipCargoStatement.run({ + cargo: JSON.stringify(cargo), + symbol: symbol, + }).changes; } export function setShipFuel(symbol, fuel) { - try { - setShipFuelStatement.run({ - date: new Date().toISOString(), - fuel: JSON.stringify(fuel), - symbol: symbol, - }).changes; - } catch (err) { - console.log(err); - return null; - } + return setShipFuelStatement.run({ + fuel: JSON.stringify(fuel), + symbol: symbol, + }).changes; } export function setShipNav(symbol, nav) { - try { - setShipNavStatement.run({ - date: new Date().toISOString(), - nav: JSON.stringify(nav), - symbol: symbol, - }).changes; - } catch (err) { - console.log(err); - return null; - } + return setShipNavStatement.run({ + nav: JSON.stringify(nav), + symbol: symbol, + }).changes; } diff --git a/nodejs/database/systems.js b/nodejs/database/systems.js index 0d637df..6cfc75a 100644 --- a/nodejs/database/systems.js +++ b/nodejs/database/systems.js @@ -1,71 +1,34 @@ import db from './db.js'; +const addSystemStatement = db.prepare(`INSERT INTO systems(data) VALUES (json(?));`); const getSystemStatement = db.prepare(`SELECT data FROM systems WHERE data->>'symbol' = ?;`); const getSystemUpdatedStatement = db.prepare(`SELECT updated FROM systems WHERE data->>'symbol' = ?;`); -const initStatement = db.prepare(`INSERT INTO config(key, value) VALUES ('systems_initialized', TRUE);`); -const isInitStatement = db.prepare(`SELECT value FROM config WHERE key = 'systems_initialized'`); -const setSystemStatement = db.prepare(`INSERT INTO systems(data) VALUES (json(?));`); const setSystemWaypointsStatement = db.prepare(`UPDATE systems SET data = (SELECT json_set(data, '$.waypoints', json(:waypoints)) FROM systems WHERE data->>'symbol' = :symbol), updated = :date WHERE data->>'symbol' = :symbol;`); -export function init() { - try { - return initStatement.run().lastInsertRowid; - } catch (err) { - return null; - } -} - -export function isInit() { - try { - return isInitStatement.get().value === '1'; - } catch (err) { - return false; - } +export function addSystem(data) { + return addSystemStatement.run(JSON.stringify(data)).lastInsertRowid; } export function getSystem(symbol) { - try { - const data = getSystemStatement.get(symbol); - if (data === undefined) { - return null; - } - return JSON.parse(data.data); - } catch (err) { - console.log(err); + const data = getSystemStatement.get(symbol); + if (data === undefined) { return null; } + return JSON.parse(data.data); } export function getSystemUpdated(symbol) { - try { - const updated = getSystemUpdatedStatement.get(symbol); - if (updated === undefined) { - return null; - } - return updated.updated; - } catch (err) { - console.log(err); - return null; - } -} - -export function setSystem(data) { - try { - return setSystemStatement.run(JSON.stringify(data)).lastInsertRowid; - } catch (err) { + const updated = getSystemUpdatedStatement.get(symbol); + if (updated === undefined) { return null; } + return updated.updated; } export function setSystemWaypoints(symbol, waypoints) { - try { - return setSystemWaypointsStatement.run({ - date: new Date().toISOString(), - symbol: symbol, - waypoints: JSON.stringify(waypoints), - }).changes; - } catch (err) { - console.log(err); - return null; - } + return setSystemWaypointsStatement.run({ + date: new Date().toISOString(), + symbol: symbol, + waypoints: JSON.stringify(waypoints), + }).changes; } -- cgit v1.2.3