Reworked the systems handling and caching with sqlite

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();