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 ++++++++++++++++++++++++++++++++++++++++++++---- 2 files changed, 50 insertions(+), 7 deletions(-) (limited to 'database') 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; -- cgit v1.2.3