refset

> Fixed a crash when starting Inkscape with a graphics tablet plugged in

Great news! Having to reconnect the USB cable each time is no fun.

Also in this vein is Shaper, a SQL-first approach for handling entire dashboards (and powered by DuckDB): https://taleshape.com/shaper/docs/getting-started/

In case anyone else was curious about the screensaver mentioned, I couldn't find any screenshots so just got Claude to cook up an HTML port: https://refset.github.io/xgrav-canvas-js/xgrav.html

I work on https://github.com/xtdb/xtdb which is broadly Postgres-compatible with a few key SQL extensions (SQL:2011 bitemporal tables + immutability, first-class nested data, pipeline syntax, etc). Built on Arrow and the JVM but is otherwise mostly from scratch.

XTDB is perhaps not directly relevant to the topic at hand, but I am a firm believer that ML workflows can benefit from robust temporal modelling.

Neat examples, and I agree that extending SQL like this has real potential. Another project along very similar lines is https://github.com/ryrobes/larsql

> other requirements

In my experience, usually along the lines of "what was the state of the world?" (valid-time as-of query) instead of "what was the state of the database?" (system-time as-of query).

Could Metabase be a better fit?

> It would be interesting if someone purpose-built a relation and rules database for compilers

While not quite in rustc proper, along these lines: https://github.com/rust-lang/chalk + https://github.com/rust-lang/polonius

> it's kind of frustrating that XTDB has to be its own top-level database instead of a storage engine or plugin for another. XTDB's core competence is its approach to temporal row tagging and querying. What part of this core competence requires a new SQL parser?

Many implementation options were considered before we embarked on v2, including building on Calcite. We opted to maximise flexibility over the long term (we have bigger ambitions beyond the bitemporal angle) and to keep non-Clojure/Kotlin dependencies to a minimum.

Recently released Clojure implementation of the same pattern: https://github.com/eerohele/muutos

SierraDB looks closer to Rama than XTDB https://blog.redplanetlabs.com/2024/01/09/everything-wrong-w...

XTDB doesn't currently solve the problems of user-defined projections (via stored procedures, triggers, Incremental View Maintenance etc.) or multi-partition scaling.

Aka 'bitemporal' - https://tidyfirst.substack.com/p/eventual-business-consisten...

> which is more efficient than "hacking it" with recursive queries in a relational db

It seems to me that the way recursive CTEs were originally defined is the biggest reason that relational databases haven't been more successful with users who need to run serious graph workloads - in Frank McSherry's words:

> As it turns out, WTIH RECURSIVE has a bevy of limitations and mysterious semantics (four pages of limitations in the version of the standard I have, and I still haven't found the semantics yet). I certainly cannot enumerate, or even understand the full list [...] There are so many things I don't understand here.

https://github.com/frankmcsherry/blog/blob/master/posts/2022...

For anyone else curious about what a practical loop implementation might look like, Steve Yegge YOLO-bootstrapped his 'Efrit' project using a few lines of Elisp: https://github.com/steveyegge/efrit/blob/4feb67574a330cc789f...

And for more context on Efrit this is a fun watch: "When Steve Gives Claude Full Access To 50 Years of Emacs Capabilities" https://www.youtube.com/watch?v=ZJUyVVFOXOc

This is close: https://panr.github.io/hugo-theme-terminal-demo/

That's the joke (!)

Unfortunately not, I've just had this one opened in my browser for ages as a reminder (after seeing it on HN IIRC) and recognised it again in the OP instantly :)

Great seeing another example here of The Monospace Web design theme https://owickstrom.github.io/the-monospace-web/

> pg_memories revolutionized our AI's ability to remember things. Before, we were using... well, also a database, but this one has better marketing.

https://pg-memories.netlify.app/

Specifically 1.4 "Compression is an Artificial Intelligence Problem" https://www.mattmahoney.net/dc/dce.html#Section_14

"Notes and Domino is a cross-platform, distributed document-oriented NoSQL database and messaging framework and rapid application development environment that includes pre-built applications like email, calendar, etc." [0]

Lotus Notes was the original offline-first everything app, including cutting edge PKI and encryption. It worked over dial-up and needed only a handful of MBs of memory (before the Java rewrite at least). Has anything else really come close since?

[0] https://en.wikipedia.org/wiki/HCL_Notes

Looks like DriftDB is focused on the 'system time' AS OF dimension, a.k.a. rollback querying. AsOf joins are more about doing analysis over user-defined domain timestamps (/ 'valid time'). Combining both concepts gets you a bitemporal database.

The SQL:2011 syntax puts the temporal filters directly after base table reference (and before the table alias) [0]

i.e. it would be `SELECT * FROM orders FOR SYSTEM_TIME AS OF "@seq:1000" WHERE customer_id="cust1"` rather than `SELECT * FROM orders WHERE customer_id="cust1" AS OF "@seq:1000"` (the latter being an example from the DriftDB readme)

[0] https://docs.xtdb.com/reference/main/sql/queries.html#_tempo...

The world deserves something better than json_agg. For XTDB we coined NEST_ONE and NEST_MANY: https://xtdb.com/blog/the-missing-sql-subqueries

The output format is either raw Arrow DenseUnions (e.g. via FlightSQL) or Transit via a pgwire protocol extension type.

AsOf join in those systems solves a rather narrow problem of performance and SQL expressiveness for data with overlapping user-defined timestamps. The bitemporal model solves much broader issues of versioning and consistent reporting whilst also reducing the need for many user-defined timestamp columns.

In a bitemporal database, every regular looking join over the current state of the world is secretly an AsOf join (across two dimensions of time), without constantly having to think about it when writing queries or extending the schema.

That only covers the 'transaction time' axis though? And the page says retention is limited to 1 week. No doubt useful for some things, but probably not end-user reporting requirements.

> waiting for that eagerly!

Wait no longer! I just updated the page with the unlisted video link: https://youtu.be/6Q_pAI20QPA

We are hoping to record another (more polished) session with Professor Snodgrass soon :)

tl;dr

  CREATE VIEW IF NOT EXISTS world_facts_as_of_now AS
  SELECT
    rowid, txn_time, valid_time,
    e, a, v, ns_user_ref, fact_meta
  FROM (
    SELECT *,
      ROW_NUMBER() OVER (
        PARTITION BY e, a
        ORDER BY valid_preferred DESC, txn_id DESC
      ) AS row_num
    FROM world_facts
  ) sub
  WHERE row_num = 1
    AND assert = 1
  ORDER BY rowid ASC;

...cool approach, but poor query optimizer!

It would be interesting to see what Turso's (SQLite fork) recent DBSP-based Incremental View Maintenance capability [0] would make of a view like this.

[0] https://github.com/tursodatabase/turso/tree/main/core/increm...

An ability to add a custom thumbnail image to a deep link might be a good compromise.

Julian Hyde (Apache Calcite, Google) gave a crisp presentation on this and how SQL could express 'measures' to bridge the gap: https://communityovercode.org/wp-content/uploads/2023/10/mon...

> A semantic layer, also known as a metrics layer, lies between business users and the database, and lets those users compose queries in the concepts that they understand. It also governs access to the data, manages data transformations, and can tune the database by defining materializations.

There's also now a paper: https://arxiv.org/pdf/2406.00251