A Better SQL in 11 Lines of Code
37 points by mpweiher
37 points by mpweiher
I'm curious to understand how this differs from datomic style datalog?
It works with existing SQL servers!
Where does it say that? Looking at the implementation it's its own thing. No where in the repo does it compile to SQL.
Their "equivalent query that spans over 20 lines" can be written more cleanly using some more modern syntax in 12 lines (excluding empty lines added for readability):
SELECT MIN(an.name) AS cool_actor_pseudonym,
MIN(t.title) AS series_named_after_char
FROM title AS t
INNER JOIN movie_keyword AS mk ON mk.movie_id = t.id
INNER JOIN keyword AS k ON k.id = mk.keyword_id
INNER JOIN movie_companies AS mc ON mc.movie_id = t.id
INNER JOIN company_name AS cn ON cn.id = mc.company_id
INNER JOIN cast_info AS ci ON ci.movie_id = t.id
INNER JOIN name AS n ON n.id = ci.person_id
INNER JOIN aka_name AS an ON an.person_id = n.id
WHERE cn.country_code ='[us]'
AND k.keyword ='character-name-in-title'
And probably there is still a way to remove that pesky MIN() aggregate in cleaner way (using subquery or lateral joins.
… more modern? INNER JOIN? Hasn’t that been obviously how you should write such a thing across all engines for at least thirty years?
I think I’d skip all your AS x table aliases, myself. Slightly longer, but fewer words and less to keep in your head as you read it.
SQL people are quite slow with adapting new features. I do not know how "hip" the author of this post is.
It looks similar to BMG / ALF
https://github.com/enspirit/bmg https://www.try-alf.org/about/
I was thinking about doing something along similar lines, but real-time capable.
(I also realize this overlaps with Datalog and other projects.)
I always think of an SQL relation as a dict from a tuple to another tuple. I wanted to implement simple queries on that on top of another data storage (I'm in love with LMDB, although I don't think it's advantages would shine here) that can keep a query open and intercept it as rows are inserted, updated, or deleted.
Also I think the same mechanism could be used as-is to generate indexes and materialized views (which I see as relation subtypes with some changes).
My idea was just to implement the bare minimum of queries, and perhaps implement another layer on top that can do aggregation, etc.
I think if I also added a PostgreSQL logical decoder that inserts to this storage, then it could be easy to use this to add real-time functionality to applications backed by PostgreSQL.