Scattered Thoughts

I suggest using the fact foreign keys are constraints with unique names,
and using these names to explicitly specify what column(s) to join
between the two foreign key tables.

In PostgreSQL [2], foreign key contraint names only need to be unique
per table, which allows using the foreign table "as is" as the
constraint name, which allows for nice short names.
In other databases, the names will just need to be a little longer.

Given this schema:

CREATE TABLE baz (
id integer NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE bar (
id integer NOT NULL,
baz_id integer,
PRIMARY KEY (id),
CONSTRAINT baz FOREIGN KEY (baz_id) REFERENCES baz
);

CREATE TABLE foo (
id integer NOT NULL,
bar_id integer,
PRIMARY KEY (id),
CONSTRAINT bar FOREIGN KEY (bar_id) REFERENCES bar
);

We could write a normal SQL query like this:

SELECT
bar.id AS bar_id,
baz.id AS baz_id
FROM foo
JOIN bar ON bar.id = foo.bar_id
LEFT JOIN baz ON baz.id = bar.baz_id
WHERE foo.id = 123

I suggest adding a new binary operator,
allowed anywhere where a table name is expected,
taking the table alias to join from as left operand,
and the name of the foreign kery contraint to follow as the right operand.

Perhaps "->" could be used for this purpose,
since it's currently not used by the SQL spec in the FROM clause.

This would allow rewriting the above query into this:

SELECT
bar.id AS bar_id,
baz.id AS baz_id
FROM foo
JOIN foo->bar
LEFT JOIN bar->baz
…

SQL is kind of a monster language to parse properly[1], and has some unfortunate and confusing edge cases[2]. It would have cost a ton of time and energy to implement properly, and all it would have ended up with is "SQL but worse."

SQL is also really awkward around APIs, since API norms and schemas don't map cleanly to SQL tables and JOINs. For example, many APIs take arguments, and while the most natural way to express an API call in SQL is as a SELECT or JOIN, requiring user-provided arguments there is really clunky. Worse, if the API that takes an argument sets a default value if that argument is not provided, then you end up in a situation where a `SELECT * FROM API` can return fewer results than a `SELECT * FROM API WHERE <some predicate>`. Using a new language meant that I could design it to best highlight Trustfall's capabilities with minimal work.

Trustfall's core is language-agnostic, so it should be possible to put a new language parser in front of it in the future -- including SQL. So I haven't ruled it out forever, I just didn't want the first thing I built to be "SQL but worse" because it's in a domain poorly suited for it.

[1]: https://twitter.com/sc13ts/status/1413729761247916036

[2]: https://www.scattered-thoughts.net/writing/select-wat-from-sql/

I love these ideas! I've been thinking about the "fully relational" language ever since I worked with some product folks and marketers at my start up 15 years ago who "couldn't code" but were wizards at cooking up SQL queries to answer questions about what was going on with our users and product. There was a language written in rust, Tablam[0] that I followed for a while, which seemed to espouse those ideas, but it seems like it's not being owrked on anymore. And Jamie from Scattered Thoughts[1] has posted some interesting articles in that direction as well. He used to work on the old YC-company/product LightTable or Eve or something, which was in the same space.

I've also always thought Joe Armstrong's (RIP) thought of "why do we need modules" is really interesting, too. There's a language I've seen posted on HN here a couple times that seems to go in that approach, with functions named by their normalized hash contents, and referred to anywhere by that, but I can't seem to remember what it's called right now. Something like "Universe" I think?

[0] https://github.com/Tablam/TablaM [1] https://www.scattered-thoughts.net [2] https://erlang.org/pipermail/erlang-questions/2011-May/058768.html

> Yep. I meant it was easy to do it the inefficient way where you just refresh the entire query when any table mentioned in the query changes. You would just have to also check if something was a view and recursively parse the SQL that is used in the view. Just use Postgres `LISTEN` and triggers or the WAL for change monitoring.

Unfortunately, it turns out that recursively refreshing views still leads to surprising behavior. I think post summarizes the problem quite nicely: https://scattered-thoughts.net/writing/internal-consistency-in-streaming-systems/. If you cannot refresh all of the views, at a single point in time, then there will be internal inconsistencies in your dataset.

When looking at automatic refreshing, simple triggers and `LISTEN/NOTIFY` don't scale, as was mentioned in the comment regarding Hasura's multiplexing. I think, in the absence of incrementally maintained views, their multiplexing strategy is a good compromise for databases like postgres. However, it should be noted that continuous query / subscription of views is the exact scenario under which incremental computation will provide both lower latency and greater resource efficiency.

Thinking of programming as a dialogue with the computer is a really strong metaphor that I gladly recognize as something that inspired you [1][2].

Short: You provide the goals and the constraints, then through a back and forth dialogue with the computer you describe and clarify the problem (and also your understanding). You keep on negotiating and providing more constraints until it simply finds a possible solution (the problem domain is now computable) or the best fitting solution now satisfies your needs (in time).

The hardest parts are probably:
1. Collecting, developing the constraint solvers. Should we imagine that collection more as platform of user contributed (programmed/trained) packages?
2. Developing the dialouge that is not just textual but visual (in both ways) (notepad vs IDE vs Theater(?!) don't forget macros/acting out!)). Showing is always faster then writing and gives one better cues on the closures/framework of the actual discussion.

[1] http://scattered-thoughts.net/blog/2014/07/21/imperative-thinking-and-the-making-of-sandwiches/
[2] https://gist.github.com/jamii/8957881c8eaa4035f4ae

Quoting from https://www.scattered-thoughts.net/writing/against-sql/:

---

There are many cases where a small change to a computation requires totally changing the structure of the query, but subqueries are my favourite because they're the most obvious way to express many queries and yet also provide so many cliffs to fall off.

-- for each manager, find their employee with the highest salary
> select
> manager.name,
> (select employee.name
> from employee
> where employee.manager = manager.name
> order by employee.salary desc
> limit 1)
> from manager;
name | name
-------+------
alice | bob

---

If the inner query has more than one row, the query will raise an error. That's difficult to know from the SQL alone.