Clojurians Log v2

@viesti FYI, the V2 version of HoneySQL supports on conflict and returning per the PG docs https://www.postgresqltutorial.com/postgresql-upsert/ (although I need to add all those examples as test cases on the v2 branch). :do-update-set will handle both cases that are separate in the nilenso extension library: {:on-conflict :col, :do-update-set :foo} => ON CONFLICT (col) DO UPDATE SET foo = EXCLUDED.foo and {:on-conflict :col, :do-update-set {:foo :excluded.foo, :bar 42}} => ON CONFLICT (col) DO UPDATE SET foo = excluded.foo, bar = 42 -- and it supports {:do-nothing true} as well (being a map means that it needs something for a value), and also {:on-constraint :foo_key} or a :where clause for :on-conflict instead of just a column keyword.

See https://github.com/seancorfield/honeysql/blob/v2/test/honey/sql_test.cljc#L402

awesome! 🙂

the postgresql docs also have some examples of WHERE condition used in the conflict action: https://www.postgresql.org/docs/13/sql-insert.html

Yup, that works too, but I don't have a test for it yet. I'll look at that example and add that as a test too.

that's pretty wicked

I'm playing around with honeysql right now (v1!) and I'm so happy how easy it is to work with functions (psql functions)

(def pg_lock (-> (helpers/select :%pg_try_advisory_lock.1) core/format))

(s/query db pg_lock)

etc...

Do you think window functions, such as over() would be supported in v2?

Yes, over is on my list. Just slowly working through everything in the nilenso library and the PG docs and the ANSI SQL standard...

I don't envy you 🙂

Although I'm happy to test out the fruits of your labo(u)r

The page @viesti linked to above has DEFAULT syntax that I hadn't seen before... so that's going in now.

I'm happy too that in v2, there will be "native" postgis function calls, rather than me doing sql/call

you're on my christmas card list this year sean!

My goal is that [:f arg1 arg2] will always be treated as a function call if it is valid. With :select that means you need an "empty alias" form to disambiguate: :select [ :foo, [:foo :bar], [ [:foo 42] ], [ [:foo 42] :bar ] ] => SELECT foo, foo AS bar, foo(42), foo(42) AS bar

interesting

so, would :select [:pg_try_advisory_lock 1] be the new normal?

That would be SELECT pg_try_advisory_lock, 1

What is the syntax you would want to generate there?

select pg_try_advisory_lock(1)

:select [[[:pg_try_advisory_lock 1]]] -- see my example above.

It's basically :select [[:something :alias]] without the alias.

[:pg_try_advisory_lock 1] is the :something in that.

would (def pg_lock (-> (helpers/select :%pg_try_advisory_lock.1) core/format)) i.e., the helpers + the format do the right-thang?

(format {:select :%pg_try_advisory_lock.1})
=> ["SELECT pg_try_advisory_lock(1)"]

So, yeah, you can use the shorthand with only a single argument.

yes, that works now 🙂 but with your new way of using : does that mean that :% is deprecated?

(I hate that syntax but I decided to support it anyway)

That eval was from the V2 branch.

right, so no change then, I can continue to use :%, even 'tho you dislike it

In V1 [:foo 42] is sometimes a function call and sometimes not. That's why you need sql/call sometimes in V1.

You can't use :%... with more than one argument right now I believe (and that won't change in V2).

If you recommend another way, other than using :% then I'm keen to go along, but if you think :% is okay__, then I'll continue to use

It will be supported and documented for 0-arity and 1-arity functions with very simple arguments (absolutely no formatting is done on the part after the dot)

right, so for simple functions such as the one above, it's okay.

:%now is valid in V1 and V2. [:now] would be the preferred syntax in V2 (in my opinion 🙂 )

I would use the new-improved version. It would be worthwhile to show an example of using that (perhaps using the example I used, of acquiring a lock, which calls a function with only one argument)?

(in your docs)

Yup, this is exactly the sort of thing that will go into the all new documentation I'm writing.

you rock 🙂

There will also be extensive "differences from V1" docs -- the start of them is here https://github.com/seancorfield/honeysql/blob/v2/doc/differences-from-1-x.md

I don't have any helper functions yet so I can't fully update the README yet (so that it will pass the seancorfield/readme tests)

I want to auto-generate helpers from the clause definitions but that's difficult for ClojureScript so I may implement all the built-in helpers and then have a Clojure-only mechanism for auto-generating helpers for any new clauses registered but in cljs you'll have to either write those yourself or just use the data structure stuff.

@dharrigan 🙂 https://github.com/seancorfield/honeysql/blob/v2/test/honey/sql_test.cljc#L218-L220

magic!

I'm offski. ttfn! 🙂