Fork me on GitHub
#sql
<
2020-06-25
>
seancorfield00:06:02

I wonder if user is treated as USER which is PG's session variable and "user" is treated as user which matches the (lowercase) table you created?

seancorfield00:06:27

(our old DBA had a rule that you never create a plural table -- always singular)

seancorfield00:06:43

MySQL's information_schema has all CAPITALIZED and nearly all plural table names, just as a data point.

adam00:06:02

I went with singular names after reading the reasoning here: https://stackoverflow.com/a/5841297/1257775

seancorfield00:06:19

The "irregular plurals" is a really good reason to stick with singular names.

adam00:06:04

I am trying my luck in building a variation of find-by-keys... just find with the keys being optional and with limit and offset support. That would cover the majority of select use cases.

seancorfield00:06:51

next.jdbc.sql.builder has a bunch of SQL string construction functions that might help you there (and they handle all the quoting stuff too).

seancorfield00:06:06

limit and offset are MySQL specific aren't they? I know SQL Server has a different syntax. I don't think SQL-92 has a standard for those (I should look!)...

seancorfield00:06:06

Oh, good to know. Maybe I should make them core options in the "friendly" functions?

adam00:06:30

They would be certainly helpful. Almost every product have some sort of pagination.

seancorfield01:06:58

Looks like the more standard approach is offset / fetch -- and although PG supports limit it is non-standard (and like MySQL's version)

adam01:06:11

I see. Always used it via ORMs that’s why I got attached to the friendly functions quickly.

seancorfield02:06:27

https://github.com/seancorfield/next-jdbc/issues/124 -- find-by-keys now supports pagination options, and :all to return "all" rows (subject to pagination)

seancorfield00:06:50

Note that :suffix can be provided in options to the query/find/get functions to provide a SQL string to append so :suffix "limit ? offset ?" would probably already let you do that...

seancorfield00:06:40

It was added originally to allow for FOR UPDATE to be appended to a SELECT.

adam00:06:22

Thanks, will look into it. Maybe I’ll cheat and use Honey SQL in the background. Still not sure :)

seancorfield02:06:22

This means that (sql/find-by-keys :table :all {:offset (* page page-size) :fetch page-size}) will let you page through all rows in TABLE and the query will be SELECT * FROM table OFFSET ? ROWS FETCH NEXT ? ROWS ONLY with the offset and fetch values added as query parameters.

seancorfield02:06:04

Instead of :all you can still pass a hash map (query by example) or a vector ("SQL String" param1 param2 ..) to do a limited query.

seancorfield02:06:36

(I wasn't planning to enhance and of the next.jdbc.sql functions... but...)

dharrigan03:06:04

There is a better way. Doing a select with limit and offset requires the db to load in the entire table and scan and filter - which can be very expensive.

dharrigan03:06:22

This explains it, with some references to other examples:

dharrigan03:06:52

I'm not advocating not doing your #124, simply that there are better ways, more efficient ways 🙂

dharrigan03:06:35

Perhaps borrowing the concept of a seek function

seancorfield03:06:06

@U11EL3P9U If folks care about performance, they shouldn't be using the "friendly SQL functions" in the first place 🙂 and next.jdbc is not the "SQL police": folks who want to write SQL that does fast pagination (such as using a where condition that includes an id or timestamp field based on the "largest" value already seen), then they can do that via execute! (or better still using plan).

dharrigan03:06:03

Indeed 🙂 Just pointing out (for others benefit who may be only aware of offset/limit) that there are alternatives 🙂

dharrigan03:06:30

A nicely crafted SQL query goes a long way 🙂

adam03:06:04

Ah just saw the above. I was building this lol:

(defn find
  [connectable table {:keys [fields where offset limit] :or {fields :*}}]
  (execute! connectable (honey/format (-> {:select fields
                                           :from   [table]}
                                          (cond->
                                           where (assoc :where where)
                                           limit (assoc :limit limit)
                                           offset (assoc :offset offset)))
                                           :quoting :ansi)))
Will test the official implementation tomorrow.

adam18:06:31

I didn’t realize how nicely HoneySQL plays with next.jdbc when I wrote the above. I ended up using an extremely thin wrapper to query my DB cleanly:

(db/query ds (db/fmt {:select [:id :name :role]
                               :from   [:user]}))

seancorfield19:06:46

Just pushed an update that supports :columns in the options passed to find-by-keys. It supports basic aliases and computed expressions (if you provide an alias).

❤️ 1
seancorfield19:06:24

But, yes, I'd mostly suggest using HoneySQL for anything beyond the basic operations. And I personally prefer using the honeysql.helpers so I'd write

(db/query ds (h/fmt (-> (select :id :name :role) (from :user))))

seancorfield19:06:03

We use HoneySQL very heavily at work (one of my former colleagues gave a talk at Clojure/West about it, years ago).

seancorfield19:06:49

I took over as maintainer of HoneySQL a while back and Justin (the original author) recently transferred it to my account so you can guarantee that next.jdbc + HoneySQL will always work well together.

adam20:06:33

Thanks for the background info. Any technical for preferring honey’s helper functions, or just a matter of taste?

adam20:06:20

I wrote the same thing twice, the data version was 181 chars and the fns version was 186

seancorfield21:06:41

With the data version, I tend to forget when things are nested in vectors vs just a vector of things.

seancorfield21:06:30

And I find the fn-based code easier to read when composing, since it looks more like SQL than a whole bunch of assoc/`update` calls.

seancorfield21:06:59

For example:

(-> (cond-> (-> (select      [:u.id :id])
                                (from        [:user :u])
                                (where       [:= :u.username username])
                                (user-status :u.id :has-user? true))
                      (not= username (u/username self))
                      (user-gender (:id self))
                      (seq exclude)
                      (merge-where [:not-in :q.id exclude]))
                    (add-order-by descriptor)
                    (sql/build :limit page-size :offset offset)
                    (sql/format :quoting :mysql))
That would be a lot uglier with assoc/`update` calls all over.

seancorfield21:06:38

especially for things like user-status which is this logic:

(-> query
      (cond->
        has-user?
        (merge-where   [:and
                        [:= :u.id id-col]
                        [:= :u.suspended false]
                        [:= :u.deleted false]])
        (not has-user?)
        (merge-join    [:user :u]
                       [:and
                        [:= :u.id id-col]
                        [:= :u.suspended false]
                        [:= :u.deleted false]]))
      (merge-left-join [:approvedtadpole :tp]
                       [:= :tp.userid id-col])
      (merge-where     [:or
                        [:= :u.statusid 1]
                        [:and
                         [:= :u.statusid 2]
                         [:<> :tp.userid nil]]])))

seancorfield03:06:25

The only thing "missing" from the official version is a list of fields -- it's always SELECT *. I'll have a think about that.

seancorfield19:06:32

@daniel.stephen.lee There are two things at play with clojure.java.jdbc/insert-multi!: 1) if you specify a sequence of hash maps, c.j.j does a separate insert for each map (because they could be different) 2) if you use array of column names followed by array of rows (array of array of column values) then c.j.j attempts to use a single batch statement for the insert...

seancorfield19:06:53

...however, some databases still do multiple round-trips for batch inserts 😞 so you need a database-specific option at connection time (in the JDBC URL string, effectively) to tell the driver to rewrite the batch to a single insert.

seancorfield19:06:45

The c.j.j docs don't really cover that level of detail but the replacement for c.j.j does: https://cljdoc.org/d/seancorfield/next.jdbc/1.0.409/doc/getting-started/prepared-statements#caveats

seancorfield19:06:18

"In particular, PostgreSQL requires the :reWriteBatchedInserts true option "

Dan19:06:11

would I put :rewriteBatchedStatement true'` in the db-spec then?

Dan19:06:44

or :reWriteBatchedInserts truerather for postgres

seancorfield19:06:58

Yes, if you're using the array approach.

seancorfield19:06:08

See my comment above.

seancorfield19:06:28

(in the message where I mentioned you to invite you in)