From 36cc33f9e96a38ecea98ac8d26275b4828347d80 Mon Sep 17 00:00:00 2001 From: Julien Dessaux Date: Sat, 1 Jul 2023 23:13:13 +0200 Subject: Moved the nodejs agent to its own subfolder to make room for my haskell agent --- nodejs/database/000_init.sql | 8 ++++ nodejs/database/001_systems.sql | 6 +++ nodejs/database/002_ships.sql | 6 +++ nodejs/database/003_surveys.sql | 6 +++ nodejs/database/config.js | 23 ++++++++++++ nodejs/database/db.js | 33 +++++++++++++++++ nodejs/database/ships.js | 82 +++++++++++++++++++++++++++++++++++++++++ nodejs/database/surveys.js | 19 ++++++++++ nodejs/database/systems.js | 71 +++++++++++++++++++++++++++++++++++ 9 files changed, 254 insertions(+) create mode 100644 nodejs/database/000_init.sql create mode 100644 nodejs/database/001_systems.sql create mode 100644 nodejs/database/002_ships.sql create mode 100644 nodejs/database/003_surveys.sql create mode 100644 nodejs/database/config.js create mode 100644 nodejs/database/db.js create mode 100644 nodejs/database/ships.js create mode 100644 nodejs/database/surveys.js create mode 100644 nodejs/database/systems.js (limited to 'nodejs/database') diff --git a/nodejs/database/000_init.sql b/nodejs/database/000_init.sql new file mode 100644 index 0000000..ecda95a --- /dev/null +++ b/nodejs/database/000_init.sql @@ -0,0 +1,8 @@ +CREATE TABLE schema_version ( + version INTEGER NOT NULL +); +CREATE TABLE config ( + id INTEGER PRIMARY KEY, + key TEXT NOT NULL UNIQUE, + value TEXT NOT NULL +); diff --git a/nodejs/database/001_systems.sql b/nodejs/database/001_systems.sql new file mode 100644 index 0000000..9ad76b1 --- /dev/null +++ b/nodejs/database/001_systems.sql @@ -0,0 +1,6 @@ +CREATE TABLE systems ( + id INTEGER PRIMARY KEY, + data JSON NOT NULL, + updated DATE DEFAULT NULL +); +CREATE UNIQUE INDEX systems_data_symbol on systems (json_extract(data, '$.symbol')); diff --git a/nodejs/database/002_ships.sql b/nodejs/database/002_ships.sql new file mode 100644 index 0000000..629f739 --- /dev/null +++ b/nodejs/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/nodejs/database/003_surveys.sql b/nodejs/database/003_surveys.sql new file mode 100644 index 0000000..0548796 --- /dev/null +++ b/nodejs/database/003_surveys.sql @@ -0,0 +1,6 @@ +CREATE TABLE surveys ( + id INTEGER PRIMARY KEY, + data JSON NOT NULL +); +CREATE INDEX surveys_data_symbol on surveys (json_extract(data, '$.symbol')); +CREATE INDEX surveys_data_expiration on surveys (json_extract(data, '$.expiration')); diff --git a/nodejs/database/config.js b/nodejs/database/config.js new file mode 100644 index 0000000..7a50f68 --- /dev/null +++ b/nodejs/database/config.js @@ -0,0 +1,23 @@ +import db from './db.js'; + +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() { + try { + return getTokenStatement.get().token; + } catch (err) { + console.log(err); + return null; + } +} + +export function registerAgent(data) { + try { + registerAgentStatement.run(JSON.stringify(data)); + return true; + } catch (err) { + console.log(err); + return false; + } +} diff --git a/nodejs/database/db.js b/nodejs/database/db.js new file mode 100644 index 0000000..4855233 --- /dev/null +++ b/nodejs/database/db.js @@ -0,0 +1,33 @@ +import fs from 'fs'; +import Database from 'better-sqlite3'; + +const allMigrations = [ + 'database/000_init.sql', + 'database/001_systems.sql', + 'database/002_ships.sql', + 'database/003_surveys.sql', +]; + +const db = new Database( + process.env.NODE_ENV === 'test' ? 'test.db' : 'spacetraders.db', + process.env.NODE_ENV === 'development' ? { verbose: console.log } : null +); +db.pragma('foreign_keys = ON'); +db.pragma('journal_mode = WAL'); + +db.transaction(function migrate() { + let version; + try { + version = db.prepare('SELECT version FROM schema_version').get().version; + } catch { + version = 0; + } + if (version === allMigrations.length) return; + while (version < allMigrations.length) { + db.exec(fs.readFileSync(allMigrations[version], 'utf8')); + version++; + } + db.exec(`DELETE FROM schema_version; INSERT INTO schema_version (version) VALUES (${version});`); +})(); + +export default db; diff --git a/nodejs/database/ships.js b/nodejs/database/ships.js new file mode 100644 index 0000000..0220be6 --- /dev/null +++ b/nodejs/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/nodejs/database/surveys.js b/nodejs/database/surveys.js new file mode 100644 index 0000000..60c85b2 --- /dev/null +++ b/nodejs/database/surveys.js @@ -0,0 +1,19 @@ +import db from './db.js'; + +const deleteExpiredSurveysStatement = db.prepare(`DELETE FROM surveys WHERE data->>'expiration' < ?;`); +const getSurveysStatement = db.prepare(`SELECT data FROM surveys WHERE data->>'symbol' = ?;`); +const setSurveysStatement = db.prepare(`INSERT INTO surveys(data) VALUES (json(?));`); + +export function deleteExpired() { + return deleteExpiredSurveysStatement.run(new Date().toISOString()).changes; +} + +export function get(symbol) { + deleteExpired(); + return getSurveysStatement.all(symbol); +} + +export function set(survey) { + deleteExpired(); + return setSurveysStatement.run(JSON.stringify(survey)); +} diff --git a/nodejs/database/systems.js b/nodejs/database/systems.js new file mode 100644 index 0000000..0d637df --- /dev/null +++ b/nodejs/database/systems.js @@ -0,0 +1,71 @@ +import db from './db.js'; + +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 getSystem(symbol) { + try { + const data = getSystemStatement.get(symbol); + if (data === undefined) { + return null; + } + return JSON.parse(data.data); + } catch (err) { + console.log(err); + return null; + } +} + +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) { + return null; + } +} + +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; + } +} -- cgit v1.2.3