Clojurians Log v2

@seancorfield - could you advise me how to make LATERAL join?

https://www.postgresql.org/docs/9.4/queries-table-expressions.html#QUERIES-LATERAL

Honeysql doesn't support lateral joins (atm) as it's very PostgreSQL specific, whereas honeysql is meant to be more vendor netural. I would suggest if you need a lateral join to drop to doing raw sql

join, :merge-join
      :left-join, :merge-left-join
      :right-join, :merge-right-join
      :full-join, :merge-full-join
      :cross-join, :merge-cross-join

are the things it supports

@maxp It would be relatively easy to add it yourself by extending the multi-methods in HoneySQL if you wanted.

My feeling in HoneySQL 2.0 (which is still in "hammock mode") is that essentially arbitrary keywords should be auto-translatable (from :kebab-case to KEBAB CASE) but there will need to be some guidance somewhere about priorities of clauses (since everything goes through a hash map and that is inherently unordered).

@seancorfield you've mentioned honey2.0 in a couple of places. It sounds "breaking", why not create GoldenSyrupSQL instead?

It will have new namespaces at least, possibly a whole new library, but I'd like to preserve the underlying data format if I can (so migration is easier).

It will definitely be breaking for anyone who is extending HoneySQL today because one of my main goals is to make extension a lot easier! Right now you have to extend it in several places just to add a simple new clause, which is a lot of work. For example, supporting a lateral join should be at most the declaration of its relative priority compared to other clauses, if that's at all possible.

It will almost certainly support "dialects" out of the box, instead of just trying to stay "pure" and portable. It will have a base level of ANSI SQL and then a "layer" for each DB that is notably different (primarily MySQL, PostgreSQL, and SQL Server I suspect but I'm happy to support "any" SQL dialect).

I want to try and support a variety of "syntactic shapes" out of the box so using "unknown" operators and/or functions is a lot easier. For example, some expressions are just joined together with spaces, some have commas, some are infix, some are prefix, some always get parenthesized, some can have parens, some can't...

Happy to see all that and do some testing with whatever pops out 🙂

I'd love to see the builder functions disappear...

I use the builder functions almost exclusively so, no. But they will get simplified. The whole merge vs non-merge thing is confusing and somewhat pointless.

But they should be generated consistently from a data structure that describes the available keywords perhaps.

I find this much more readable

(-> (select :*)
    (from [:table :t])
    (where [:= :id 42])
    (join [:other :o] [:= :o.id :t.otherid])) 

(-> {:select [:*]}
    (assoc :from [:table :t])
    (assoc :where [:= :id 42])
    (assoc :join [[:other :o] [:= :o.id :t.otherid]]))

(and in fact that is wrong, it should be (assoc :from [[:table :t]]) and that whole do I wrap it with a vector / don't I? question is why I avoid the data structure approach -- but some people find the helper approach just as confusing)

Why would you be associating at all? Most of my queries are whole maps with a tiny bit of dynamism.

I think the fact the documentation omits the data structure form is what has always irked me. I have to run the helper to find out what the data looks like.

Most of my queries are a base query with a shit ton of dynamism 😜

I’ve had to write my own merge-with helper

Same here: we have tons of functions that conditionally add in all sorts of select/join/where clause fragments. That's why we use HoneySQL in the first place. If we had mostly static queries, we'd probably just use string-bashing.

The documentation for 2.0 will definitely include lots of detail about the data structure and I will write Specs for it I expect.