Public
Script
Val Town is a social website to write and deploy JavaScript.
Build APIs and schedule functions from your browser.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
import { sqlite } from "https://esm.town/v/std/sqlite?v=4";
const schema = [
`CREATE TABLE IF NOT EXISTS nodes (
body TEXT,
id TEXT GENERATED ALWAYS AS (json_extract(body, '$.id')) VIRTUAL NOT NULL UNIQUE
);`,
`CREATE INDEX IF NOT EXISTS id_idx ON nodes(id);`,
`CREATE TABLE IF NOT EXISTS edges (
source TEXT,
target TEXT,
properties TEXT,
UNIQUE(source, target, properties) ON CONFLICT REPLACE,
FOREIGN KEY(source) REFERENCES nodes(id),
FOREIGN KEY(target) REFERENCES nodes(id)
);`,
`CREATE INDEX IF NOT EXISTS source_idx ON edges(source);`,
`CREATE INDEX IF NOT EXISTS target_idx ON edges(target);`,
];
const insertNode = "INSERT INTO nodes VALUES(json(?))";
const insertEdge = "INSERT INTO edges VALUES(?, ?, json(?))";
const deleteEdge = "DELETE FROM edges WHERE source = ? AND target = ?";
const deleteEdges = "DELETE FROM edges WHERE source = ? OR target = ?";
const deleteIncomingEdges = "DELETE FROM edges WHERE target = ?";
const deleteOutgoingEdges = "DELETE FROM edges WHERE source = ?";
const searchEdges = "SELECT * FROM edges WHERE source = ? UNION SELECT * FROM edges WHERE target = ?";
const searchEdgesInbound = "SELECT * FROM edges WHERE source = ?";
const searchEdgesOutbound = "SELECT * FROM edges WHERE target = ?";
const updateNode = "UPDATE nodes SET body = json(?) WHERE id = ?";
const updateEdge = "UPDATE edges SET properties = json(?) WHERE source = ? AND target = ?";
const searchNode = (resultColumn, searchClauses) =>
`SELECT ${resultColumn} FROM nodes ${searchClauses.length > 0 ? "WHERE" : ""} ${searchClauses.map(clause => clause)}`;
const traverse =
"WITH RECURSIVE traverse(x, y, obj) AS (SELECT id, '()', body FROM nodes WHERE id = 1 UNION SELECT id, '()', body FROM nodes JOIN traverse ON id = x UNION SELECT source, '<-', properties FROM edges JOIN traverse on target = x UNION SELECT target, '->', p
// Needs to be turned from Jinja template to JS template string
const searchWhere = (id, key, predicate, tree) =>
`{% if and_or %}{{ and_or }}{% endif %} ${id ? "id = ?" : "" } ${key ? "json_extract(body, '$.${key}') ${predicate} ?" : ""} {% if tree %}AND json_tree.key='{{ key }}' AND {% endif %}json_extract(body, '$.{{ key }}') {{ predicate }} ?{% endif %} {% if tr
async function executeStatement(statement, args = []) {
return await sqlite.execute({
sql: statement,
args: args,
});
}
async function init() {
await sqlite.batch(schema);
}
August 16, 2024