[node] implement shipyard info retrieval and database caching

2 files changed, 33 insertions, 0 deletions

diff --git a/nodejs/database/005_shipyards.sql b/nodejs/database/005_shipyards.sql
new file mode 100644
index 0000000..e4d3f28
--- /dev/null
+++ b/nodejs/database/005_shipyards.sql
@@ -0,0 +1,6 @@
+CREATE TABLE shipyards (
+       id INTEGER PRIMARY KEY,
+       data JSON NOT NULL,
+       updated DATE DEFAULT NULL
+);
+CREATE UNIQUE INDEX shipyards_data_symbol on shipyards (json_extract(data, '$.symbol'));
diff --git a/nodejs/database/shipyards.ts b/nodejs/database/shipyards.ts
new file mode 100644
index 0000000..4c243f9
--- /dev/null
+++ b/nodejs/database/shipyards.ts
@@ -0,0 +1,27 @@
+import { DbData, db } from './db.ts';
+import { Shipyard } from '../lib/types';
+
+const addStatement = db.prepare(`INSERT INTO shipyards(data, updated) VALUES (json(:data), :date);`);
+const getStatement = db.prepare(`SELECT data FROM shipyards WHERE data->>'symbol' = ?;`);
+const updateStatement = db.prepare(`UPDATE shipyards SET data = json(:data), updated = :date WHERE data->>'symbol' = :symbol;`);
+
+export function get(symbol: string): Shipyard|null {
+	const data = getStatement.get(symbol) as DbData|undefined;
+	if (!data) return null;
+	return JSON.parse(data.data);
+}
+
+export function set(data: Shipyard): void {
+    if (get(data.symbol) === null) {
+		addStatement.run({
+			data: JSON.stringify(data),
+			date: new Date().toISOString(),
+		});
+    } else {
+		updateStatement.run({
+			data: JSON.stringify(data),
+			date: new Date().toISOString(),
+			symbol: data.symbol,
+		});
+	}
+}