diff options
Diffstat (limited to '')
| -rw-r--r-- | database/002_ships.sql | 6 | ||||
| -rw-r--r-- | database/config.js | 2 | ||||
| -rw-r--r-- | database/db.js | 1 | ||||
| -rw-r--r-- | database/ships.js | 82 | ||||
| -rw-r--r-- | database/systems.js | 6 |
5 files changed, 93 insertions, 4 deletions
diff --git a/database/002_ships.sql b/database/002_ships.sql new file mode 100644 index 0000000..629f739 --- /dev/null +++ b/database/002_ships.sql @@ -0,0 +1,6 @@ +CREATE TABLE ships ( + id INTEGER PRIMARY KEY, + data JSON NOT NULL, + updated DATE DEFAULT NULL +); +CREATE UNIQUE INDEX ships_data_symbol on ships (json_extract(data, '$.symbol')); diff --git a/database/config.js b/database/config.js index 4a86cb6..7a50f68 100644 --- a/database/config.js +++ b/database/config.js @@ -1,6 +1,6 @@ import db from './db.js'; -const getTokenStatement = db.prepare(`SELECT json_extract(value, '$.token') as token from config where key = 'register_data';`); +const getTokenStatement = db.prepare(`SELECT value->>'token' as token from config where key = 'register_data';`); const registerAgentStatement = db.prepare(`INSERT INTO config(key, value) VALUES ('register_data', json(?));`); export function getToken() { diff --git a/database/db.js b/database/db.js index 6deddcc..a6d5174 100644 --- a/database/db.js +++ b/database/db.js @@ -4,6 +4,7 @@ import Database from 'better-sqlite3'; const allMigrations = [ 'database/000_init.sql', 'database/001_systems.sql', + 'database/002_ships.sql', ]; const db = new Database( diff --git a/database/ships.js b/database/ships.js new file mode 100644 index 0000000..0220be6 --- /dev/null +++ b/database/ships.js @@ -0,0 +1,82 @@ +import db from './db.js'; + +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;`); + +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); + return null; + } +} + +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; + } + } else { + try { + return updateShipStatement.run({ + data: JSON.stringify(data), + date: new Date().toISOString(), + symbol: data.symbol, + }).changes; + } catch (err) { + console.log(err); + return null; + } + } +} + +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; + } +} + +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; + } +} + +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; + } +} diff --git a/database/systems.js b/database/systems.js index 337126c..479deb6 100644 --- a/database/systems.js +++ b/database/systems.js @@ -1,9 +1,9 @@ import db from './db.js'; -const getSystemStatement = db.prepare(`SELECT data FROM systems WHERE json_extract(data, '$.symbol') = ?;`); -const getSystemUpdatedStatement = db.prepare(`SELECT updated FROM systems WHERE json_extract(data, '$.symbol') = ?;`); +const getSystemStatement = db.prepare(`SELECT data FROM systems WHERE data->>'symbol' = ?;`); +const getSystemUpdatedStatement = db.prepare(`SELECT updated FROM systems WHERE data->>'symbol' = ?;`); 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 json_extract(data, '$.symbol') = :symbol), updated = :date WHERE json_extract(data, '$.symbol') = :symbol;`); +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 { |