Public
Edited
Feb 17, 2023
Insert cell
Insert cell
Insert cell
Insert cell
baseSchemaCreate = {
db.run(`DROP TABLE IF EXISTS event`);
db.run(
`CREATE TABLE event (
id TEXT primary key, -- uuid
todo_id TEXT,
event_type TEXT, -- create | complete | rename | delete | add_to_list
value ANY
) STRICT;`
);
db.run(`CREATE INDEX event_todo ON event (todo_id);`);
}
Insert cell
Insert cell
dagSchemaCreate = {
baseSchemaCreate;
db.run(`DROP TABLE IF EXISTS event_dag`);
db.run(`CREATE TABLE event_dag (
parent_id TEXT, -- the event that came before this one
event_id TEXT, -- the id of the event
PRIMARY KEY (parent_id, event_id)
) STRICT;`);
db.run(`CREATE INDEX event_dag_event ON event_dag (event_id);`);
}
Insert cell
Insert cell
Insert cell
initialEventsCreate = {
dagSchemaCreate;
deleteAll(db); // dropping all since this cell is intended to run with empty tables
transact(db, () => {
const todoid = nanoid();
const createEventId = eventId("a");

// Insert an event to create the todo
runSafely(
db,
`INSERT INTO event
(id, todo_id, event_type, value)
VALUES
(?, ?, "create", NULL)`,
[createEventId, todoid]
);

// Add a record in the `event_dag`
// Given it is the first event in the system, it points to `ROOT`
runSafely(
db,
`
INSERT INTO event_dag
(parent_id, event_id)
VALUES
(?, ?)`,
["ROOT", createEventId]
);

// Now give the todo we created some text
const renameEventId = eventId("a");
runSafely(
db,
`
INSERT INTO event
(id, todo_id, event_type, value)
VALUES
(?, ?, 'rename', 'get coffee')`,
[renameEventId, todoid]
);

// And record it in the `event_dag`.
// The rename happens after the create so the `createEventId` is the
// parent of the rename event.
runSafely(
db,
`INSERT INTO event_dag
(parent_id, event_id)
VALUES
(?, ?)`,
[createEventId, renameEventId]
);

// Now lets add it to a specific list
const addToListEventId = eventId("a");
runSafely(
db,
`INSERT INTO event
(id, todo_id, event_type, value)
VALUES
(?, ?, 'add_to_list', 'work')`,
[addToListEventId, todoid]
);
// Now attach the rename to the dag
runSafely(db, `INSERT INTO event_dag (parent_id, event_id) VALUES (?, ?)`, [
renameEventId,
addToListEventId
]);
});
}
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
eventIds_pass1 = {
initialEventsCreate;
const query = `WITH RECURSIVE
after_node(event_id,level) AS (
VALUES('ROOT',0)
UNION ALL
SELECT event_dag.event_id, after_node.level+1
FROM event_dag JOIN after_node ON event_dag.parent_id=after_node.event_id
ORDER BY 2,1 DESC
)

SELECT event.id, event.todo_id, event.event_type, event.value
FROM after_node JOIN event
ON after_node.event_id = event.id;
`;

return Inputs.table(getAll(db, query), {
header: ["event_id", "todo_id", "event_type", "value"]
});
}
Insert cell
Insert cell
Insert cell
{
initialEventsCreate;
const query = `SELECT l.event_id
FROM event_dag as l
WHERE NOT EXISTS (SELECT NULL FROM event_dag as r WHERE r.parent_id = l.event_id)`;

return Inputs.table(getAll(db, query), {
header: ["event_id"]
});
}
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
processList = {
initialEventsCreate;
runSafely(
db,
`CREATE TABLE IF NOT EXISTS todo (id primary key, complete, content, is_deleted, list)`
);
const events = pullAllEvents(db);
transact(db, () => {
for (let [_, todoId, eventType, value] of events) {
switch (eventType) {
case "create":
runSafely(db, `INSERT OR IGNORE INTO todo (id) VALUES (?);`, [
todoId
]);
break;
case "complete":
runSafely(
db,
`INSERT INTO todo (id, complete) VALUES (?, ?) ON CONFLICT DO UPDATE SET complete=excluded.complete`,
[todoId, value]
);
break;
case "delete":
runSafely(
db,
`INSERT INTO todo (id, is_deleted) VALUES (?, ?) ON CONFLICT DO UPDATE set is_deleted=excluded.is_deleted`,
[todoId, value]
);
break;
case "rename":
runSafely(
db,
`INSERT INTO todo (id, content) VALUES (?, ?) ON CONFLICT DO UPDATE SET content=excluded.content`,
[todoId, value]
);
break;
case "add_to_list":
runSafely(
db,
`INSERT INTO todo (id, list) VALUES (?, ?) ON CONFLICT DO UPDATE SET list=excluded.list`,
[todoId, value]
);
}
}
});
}
Insert cell
{
processList;
return Inputs.table(getAll(db, `SELECT * FROM todo`), {
header: ["id", "complete", "content", "is_deleted", "list"]
});
}
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
pullAllEvents = (db) => {
const query = `WITH RECURSIVE
after_node(event_id,level) AS (
VALUES('ROOT',0)
UNION ALL
SELECT event_dag.event_id, after_node.level+1
FROM event_dag JOIN after_node ON event_dag.parent_id=after_node.event_id
ORDER BY 2,1 DESC
)

SELECT event.id, event.todo_id, event.event_type, event.value
FROM after_node JOIN event
ON after_node.event_id = event.id;
`;

return getAll(db, query);
};
Insert cell
attachToDAG = (db, ) => {}
Insert cell
Insert cell

Purpose-built for displays of data

Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
Learn more