Clojurians Log v2

Now I pass jsonb data as

(->
        (h/insert-into :tbl)
        (h/values [{:json-field :?data}])
        (sql/format {:data {:a 1}}))

(->
        (h/insert-into :tbl)
        (h/values [{:json-field #sql/??? {:a 1}}])
        (sql/format)

I don't believe there's any way to do that in 1.0. It's something I plan to address in 2.0.

I'm just in the process of updating next.jdbc so the next.jdbc.types wrappers will pass through HoneySQL 1.0 untouched (they have been vectors with metadata, which HoneySQL tried to unwrap as values -- they're changing to functions which I've tested that HoneySQL will pass through as-is).

Once that new version of next.jdbc is out, you could probably use one of the as-* wrappers inline to achieve this @maxp

@seancorfield could you do something like data_readers for the new honey sql? Where they all get slurped up to find the namespaces that need loading for extensions?

Just encountered a problem with JOIN ordering:

(sql/format (-> (hh/select :a)
                (hh/from [:table1 :t1])
                (hh/left-join [:table2 :t2] [:= :t1.id :t2.id])
                (hh/join [:table3 :t3] [:= :t2.x :t3.x])))
=> ["SELECT a FROM table1 t1 INNER JOIN table3 t3 ON t2.x = t3.x LEFT JOIN table2 t2 ON t1.id = t2.id"

Since different joins can depend on each other, seems like there should be a single :join array or maps where each map has a key like :join-kind. This way, it would be possible to preserve ordering.

Hey, I'm using the honeysql.types/array function to create an array in Postgres. However when the array is empty I get a type cast error. I need to specifiy the type as follows:

array[]::text[]

(sql/format (sql/call :cast :x (sql/raw "int[]")))
=> ["CAST(x AS int[])"]

Alternatively, just use sql/raw to use the :: syntax.

Thanks, I'll try this out!

I just glanced at the v2 branch, and seems like it carries the join ordering logic over, so I'd guess the issue is still there.

Works like a charm, thanks

Worked around it with this for now:

(fmt/register-clause! :ordered-join (dec (:join @fmt/clause-store)))

(defhelper ordered-join [m clauses]
  (assoc m :ordered-join clauses))

(defmethod fmt/format-clause :ordered-join [[_ join-groups] _]
  (fmt/space-join (map #(apply fmt/format-join %)
                       (partition 3 join-groups))))

Created https://github.com/seancorfield/honeysql/issues/277 to keep track of it.

My plan for V2 was to very specifically get rid of the whole data_readers stuff that is part of V1. Can you elaborate on what you're suggesting here @dominicm?

@p-himik I thought most databases allow the joins/aliases in any order?

Apparently not - PostgreSQL didn't allow me to refer to the t2 alias before it was declared.

Looks like MySQL barfs on it too... OK, I'll have a think about that...

In V2 that becomes [:cast :x [:raw "int[]"]]

@seancorfield A while ago I made a comment about the problem with multi-methods is that they have to be loaded. In some contexts, there's no natural/incidental name to require and use which would then create the right match on the multi-method. honeysql-postgres is an example of this, it doesn't do anything except trigger side-effects, and it's really easy to forget to load the ns from every place you use it. Alex at the time suggested that libraries should provide a "loading mechanism" for multi-methods when that was the case, e.g. how data_readers.cljc is implemented. Search the classpath for honeysql/extensions.edn and load the namespaces listed within.

Well, HoneySQL V2 has register-*! functions that you call at app startup to add clauses, operators, and other syntax, so any extension library would need to call those and expose it's own initialization function that you'd need to call. I'll create a ticket for auto-initialization via an EDN file so that can be automated. Interesting idea.

https://github.com/seancorfield/honeysql/issues/278

FYI, the v2 branch of honeysql now has :?foo support and [:param :foo] (as the new version of #sql/param :foo or (sql/param :foo)) and there's a first cut of [:raw "SQL"] taking just a string -- still deciding what exactly to do about that.

Going back to @maxp’s question earlier, something that would make that sort of thing easier -- and I'm somewhat surprised v1 doesn't already have this -- is a way to identify a structured value that should be treated as a parameter (i.e., a way to prevent interpretation of [..`]` or {..`}` etc). My initial thought is [:lift <some-expr>] since it would "lift" the expression out as a parameter but I don't know how that reads for other folks? (well, my initial thought was really to use [:param <some-expr>] for it but given v1's use of sql/param for named parameters would make things very confusing!).

An issue raised by @camsaul on GitHub is worth discussing here: there are situations where some DBs need an extra (..`)` around either part of a statement or part of an expression, to disambiguate how clauses should bind (in SQL, not in HoneySQL, so it isn't purely a clause priority issue). My solution to this is to add [:nest ...] for expressions and {:nest ...} for statements (as a very low priority (fake) "clause"). How do folks feel about that? It should rarely be necessary but should allow you to take direct control when needed, without complicating things. Open to suggestions on the name and/or alternate approaches.