I’ve been hacking on a secret project that I’ve had in the works since, oh grief, the first check-in was:
<code>commit ccb93ca5f2b256babfa0f2ef9110ac0ac4019527 Author: Elf M. Sternberg <email@example.com> Date: Mon Feb 4 23:03:01 2013 -0800 Initial check-in for Scarlett.</code>
And then, like a lot of people, I learned about Zettlekasten, and the Noguchi Technique, and other things. I started to use Evernote and abandoned my web-based thing, but it was still there on my hard drive. And then came the new generation of note-managing tools that combine Zettlekasten, Noguchi, and maybe some other features. Tools like Roam, Bear Notes, and Bytebase, which take note-taking and structuring to whole new levels.
In the past week, I started hammering out what it would take to get scarlett-2 off the ground. There were a few things I wanted to learn how to use. The two biggest things were: more Rust, and ReasonML.
What have I got? Well, I’ve got an API design, and I have a prototype running in Rust that writes notes to the back-end database, using the sqlx library. But what I’m really proud of this chunk of SQL:
<code> SELECT parent_id, id, content, notetype, nature, position FROM ( WITH RECURSIVE children(parent_id, id, content, notetype, nature, position, cycle) AS ( SELECT notes.id, notes.id, notes.content, notes.notetype, 'page', 0, ','||notes.id||',' FROM notes INNER JOIN pages ON pages.note_id = notes.id WHERE pages.id = ? AND notes.notetype="page" UNION SELECT note_relationships.parent_id, notes.id, notes.content, notes.notetype, note_relationships.nature, note_relationships.position, children.cycle||notes.id||',' FROM notes INNER JOIN note_relationships ON notes.id = note_relationships.note_id INNER JOIN children ON note_relationships.parent_id = children.id WHERE children.cycle NOT LIKE '%,'||notes.id||',%' ORDER BY note_relationships.position) SELECT * from children);</code>
pages barely exist. They’re just there to have a title– but that’s one of the two critical abstractions, because titles are how you find other things. The central item is the
note, and notes have a nested, tree-like relationship with other notes. Notes are manually positioned, and there are different kinds of notes, mostly the ‘root’ note and the ‘content’ note, but there may eventually be other kinds for supporting multimedia, since I have a metric arseload of PDFs that I need to track and organize, somehow.
So there’s a table,
notes_relationships, that has the
(note_id, parent_note_id, position, nature) pair. (“Nature” describes the nature of the relationship; a note is “hosted” in a parent note, but may also be included in other parent notes, so that notes can be visible in more than one page.
So that SQL up there extracts a tree by building a Recursive Common Table Expression, a temporary table that can be referenced by itself to extract tree and graph data. This version works in SQLite, but it’s just as portable to Postgresql.
The RCTE here is named
children, and it has a few moving parts; The innermost
SELECT statement with the
WHERE pages.id = ? expression is the seed expression: it returns the note object that houses the page’s content. The
SELECT after the
UNION represents the recursion; it then finds all the notes whose
parent_id is the same as the last
note_id added to the
children table in the previous recursive call.
cycle variable terminates the recursion for any expression that has a cycle in it-- a case where Note-1 has Note-2 as a child, but Note-2 claims Note-1 is one of its children! While I’ll do everything in my power to prevent cycles like this, of any depth, from getting into the database in the first place, bugs happen, and preventative measures like this help me sleep better at night.
The encapsulating SELECT retrieves all the values from the
children recursive query, but returns only the fields the user cares about; notably, it strips out the
cycle because there’s no reason to pass that internal administrative stuff up to any clients. Especially not in Rust, where I don’t want to fetch up a multi-kilobyte string that I’m not going to be using.
For my next trick, I’ll be figuring out how to turn that thing, which will just be a
Vec<FetchedNotes>, into the sort of tree Serde knows how to send out to clients.
The objective here, such as it is, is to write the back-end in a way that enables multiple front-ends: a CLI, a Curses tool, a web-based tool, and a Flutter-based application. I want my Zettlekasten to follow me around, be available everywhere, and to literally be my second brain. And because I’m an idiosyncratic sort of fellow, I’m going to write it myself, because then I’ll learn something: about Rust, Reason, and Flutter.
And who knows? Maybe I’ll share it with you. One thing’s for sure: I want it to be pretty.
And I don’t want to have to write React to make it so. I wonder if I can leverage SDOM into Ocaml/ReScript somehow?