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.
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.
andersmurphy | 17 hours ago
I'm curious to understand how this differs from datomic style datalog?
sjamaan | 16 hours ago
It works with existing SQL servers!
andersmurphy | 15 hours ago
Where does it say that? Looking at the implementation it's its own thing. No where in the repo does it compile to SQL.
sjamaan | 15 hours ago
Oh shit, I must've skimmed this one a little too quickly :)
andersmurphy | 15 hours ago
You had me excited for a minute!
hauleth | 14 hours ago
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):
And probably there is still a way to remove that pesky
MIN()aggregate in cleaner way (using subquery or lateral joins.chrismorgan | 10 hours ago
… 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 xtable aliases, myself. Slightly longer, but fewer words and less to keep in your head as you read it.hauleth | 10 hours ago
SQL people are quite slow with adapting new features. I do not know how "hip" the author of this post is.
amw-zero | 7 hours ago
It looks similar to BMG / ALF
https://github.com/enspirit/bmg https://www.try-alf.org/about/
koala | 11 hours ago
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.