From 61b5c8493e57c73469f31a795037922bd9d34931 Mon Sep 17 00:00:00 2001 From: Julien Dessaux Date: Wed, 31 May 2023 01:19:31 +0200 Subject: Reworked the systems handling and caching with sqlite --- database/001_systems.sql | 6 ++-- database/systems.js | 51 ++++++++++++++++++++++++++--- lib/systems.js | 85 ++++++++++++++++++++++++++++++++++-------------- main.js | 7 ++-- 4 files changed, 116 insertions(+), 33 deletions(-) diff --git a/database/001_systems.sql b/database/001_systems.sql index 8448955..9ad76b1 100644 --- a/database/001_systems.sql +++ b/database/001_systems.sql @@ -1,6 +1,6 @@ CREATE TABLE systems ( id INTEGER PRIMARY KEY, - symbol TEXT NOT NULL UNIQUE, - data TEXT NOT NULL, - updated DATE DEFAULT (datetime('now')) + data JSON NOT NULL, + updated DATE DEFAULT NULL ); +CREATE UNIQUE INDEX systems_data_symbol on systems (json_extract(data, '$.symbol')); diff --git a/database/systems.js b/database/systems.js index 46b4663..e3822d3 100644 --- a/database/systems.js +++ b/database/systems.js @@ -1,7 +1,25 @@ import db from './db.js'; -const getSystemStatement = db.prepare(`SELECT data from systems where symbol = ?;`); -const setSystemStatement = db.prepare(`INSERT INTO systems(symbol, data) VALUES (?, ?);`); +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 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;`); + +export function init() { + try { + return db.prepare(`INSERT INTO config(key, value) VALUES ('systems_initialized', TRUE);`).run().lastInsertRowid; + } catch (err) { + return null; + } +} + +export function isInit() { + try { + return db.prepare(`SELECT value FROM config WHERE key = 'systems_initialized'`).get().value === '1'; + } catch (err) { + return false; + } +} export function getSystem(symbol) { try { @@ -16,9 +34,34 @@ export function getSystem(symbol) { } } -export function setSystem(symbol, 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) { + return null; + } +} + +export function setSystemWaypoints(symbol, waypoints) { try { - return setSystemStatement.run(symbol, JSON.stringify(data)).lastInsertRowid; + return setSystemWaypointsStatement.run({ + date: new Date().toISOString(), + symbol: symbol, + waypoints: JSON.stringify(waypoints), + }); } catch (err) { console.log(err); return null; diff --git a/lib/systems.js b/lib/systems.js index 4b480de..3cc7c7a 100644 --- a/lib/systems.js +++ b/lib/systems.js @@ -1,49 +1,86 @@ import * as api from './api.js'; import * as db from '../database/systems.js'; -// Retrieves a list of waypoints that have a specific ctx.trait like a SHIPYARD or a MARKETPLACE in the system ctx.symbol -export async function trait(ctx) { - const s = await system(ctx); - return s.filter(s => s.traits.some(t => t.symbol === ctx.trait)); +// Retrieves all systems information, should be called only once after registering +export async function init(ctx) { + if (db.isInit()) { + return; + } + for (let page=1; true; ++page) { + const response = await api.send({endpoint: `/systems?limit=20&page=${page}`, priority:100}); + if (response.error !== undefined) { + throw response; + } + response.data.forEach(system => db.setSystem(system)); + if (response.meta.total <= response.meta.limit * page) { + break; + } + } + console.log('Finished retrieving all systems information'); + db.init(); } -// Retrieves a list of waypoints that have a specific ctx.type like ASTEROID_FIELD in the system ctx.symbol -export async function type(ctx, response) { - const s = await system(ctx); - return s.filter(s => s.type === ctx.type); +// Retrieves a shipyard's information for ctx.symbol +export async function shipyard(ctx) { + const systemSymbol = ctx.symbol.match(/([^-]+-[^-]+)/)[1]; // TODO generalise this extraction + console.log(systemSymbol); + return await api.send({endpoint: `/systems/${systemSymbol}/waypoints/${ctx.symbol}/shipyard`}); } -// Retrieves the system's information for ctx.symbol and cache it in the database +// Retrieves the system's information for ctx.symbol and caches it in the database export async function system(ctx) { let s = db.getSystem(ctx.symbol); if (s === null) { - const response = await api.send({endpoint: `/systems/${ctx.symbol}/waypoints?limit=20&page=1`}); + const response = await api.send({endpoint: `/systems/${ctx.symbol}`}); if (response.error !== undefined) { switch(response.error.code) { case 404: - throw `Error retrieving waypoints for system ${ctx.symbol}: ${response.error.message}`; + throw `Error retrieving info for system ${ctx.symbol}: ${response.error.message}`; default: // yet unhandled error throw response; } } - if (response.meta !== undefined && response.meta.total > response.meta.limit) { - throw `Error retrieving waypoints for system ${ctx.symbol}: Pagination is not implemented yet!`; - } s = response.data; - db.setSystem(ctx.symbol, s); + db.setSystem(s); } return s; } -export async function systems(ctx) { - const response = await api.send({endpoint: `/systems?limit=20&page=1`}); - // TODO pagination - return response; +// Retrieves a list of waypoints that have a specific ctx.trait like a SHIPYARD or a MARKETPLACE in the system ctx.symbol +export async function trait(ctx) { + const s = await system(ctx); + return s.waypoints.filter(s => s.traits.some(t => t.symbol === ctx.trait)); } -// Retrieves a shipyard's information for ctx.symbol -export async function shipyard(ctx) { - const systemSymbol = ctx.symbol.match(/([^-]+-[^-]+)/)[1]; // TODO generalise this extraction - console.log(systemSymbol); - return await api.send({endpoint: `/systems/${systemSymbol}/waypoints/${ctx.symbol}/shipyard`}); +// Retrieves a list of waypoints that have a specific ctx.type like ASTEROID_FIELD in the system ctx.symbol +export async function type(ctx, response) { + const s = await system(ctx); + return s.waypoints.filter(s => s.type === ctx.type); +} + +// Retrieves the system's information for ctx.symbol and caches it in the database +export async function waypoints(ctx) { + await system(ctx); + let updated = db.getSystemUpdated(ctx.symbol); + if (updated === null) { + let waypoints = []; + for (let page=1; true; ++page) { + const response = await api.send({endpoint: `/systems/${ctx.symbol}/waypoints?limit=20&page=${page}`, priority: 98}); + if (response.error !== undefined) { + switch(response.error.code) { + case 404: + throw `Error retrieving waypoints for system ${ctx.symbol}: ${response.error.message}`; + default: // yet unhandled error + throw response; + } + } + waypoints = waypoints.concat(response.data); + if (response.meta.total <= response.meta.limit * page) { + break; + } + } + db.setSystemWaypoints(ctx.symbol, waypoints); + return waypoints; + } + return db.getSystem(ctx.symbol).waypoints; } diff --git a/main.js b/main.js index 04214fd..a2a03a2 100755 --- a/main.js +++ b/main.js @@ -109,11 +109,14 @@ default: case 'systems.shipyards': api.debugLog(await systems.trait({symbol: process.argv[3], trait: 'SHIPYARD'})); break; + case 'systems.init': + await systems.init(); + break; case 'systems.system': api.debugLog(await systems.system({symbol: process.argv[3]})); break; - case 'systems.systems': - api.debugLog(await systems.systems()); + case 'systems.waypoints': + api.debugLog(await systems.waypoints({symbol: process.argv[3]})); break; default: usage(); -- cgit v1.2.3