Fork me on GitHub
#sql
<
2019-06-18
>
kostafey12:06:50

Hmm... it's impossible to reproduce exact this problem with H2 database (the actual database is Oracle).

kostafey12:06:29

So, problem solved.

kostafey12:06:42

But map works for Oracle databases and :as-arrays? is false. The :result-set-fn doesn't change the result for both doall or vec values.

kostafey15:06:15

Another question. I'd like to know the total number of records in the result set inside row-fn. Something like that: https://github.com/kostafey/java.jdbc/commit/d67fe0d8cf720f5fc37d320bb4c696f3f852f432

seancorfield16:06:35

@kostafey The :row-fn cannot possibly know the number of rows -- it is called per-row, while the result set is being processed.

seancorfield16:06:00

Interesting re: map vs. mapv on the array-based results, but I'm not terribly surprised: don't use lazy functions inside result set processing with query.

kostafey16:06:29

Right, but the result set is ready when :row-fn called per record. So, we can pass a count of records like here: https://github.com/kostafey/java.jdbc/commit/d67fe0d8cf720f5fc37d320bb4c696f3f852f432

seancorfield16:06:33

You'd probably do better extending IResultReadColumn to Clob to do the coercion.

seancorfield16:06:08

No, the result set is not ready. JDBC is somewhat lazy, so you must only use eager functions inside both :row-fn and :result-set-fn

kostafey16:06:21

But the code above works as expected.

seancorfield16:06:28

And, as you've seen, different JDBC drivers behave differently.

seancorfield16:06:37

Yes, mapv is eager. map is lazy.

kostafey16:06:06

I mean the second question 🙂

kostafey16:06:43

Pass a total records count to row-fn: (map #(row-fn % (count (rest rs))) (rest rs))

seancorfield16:06:54

No, the result set can be streamed lazily by the JDBC driver. :row-fn is called as the ResultSet is iterated over -- it's a lazy mutable data structure.

kostafey16:06:09

So, count call enforces the result set fetching?

seancorfield16:06:35

count would force the entire rs structure to be realized -- preventing any lazy processing.

kostafey16:06:43

Ok. Thank you. But probably we can pass a handler to result set by itself, e.g. (map #(row-fn % (rest rs)) (rest rs))

kostafey16:06:17

Then a user can decide what to do with it.

seancorfield16:06:29

If you want to do something with knowledge of "the whole result set", use :result-set-fn. If you want to do something with knowledge of a single row, use :row-fn.

ghadi16:06:31

@kostafey pass in your own result-set-fn

seancorfield16:06:25

Just bear in mind that both of those operate in the context of an open, in-process result set which may be both huge (many millions of rows in the general case) and lazy (because JDBC can lazily stream result sets from the DB in blocks of rows).

seancorfield16:06:53

If you want to avoid the lazy/eager issue, just do a query and post-process the result (without using :row-fn or :result-set-fn at all).

seancorfield16:06:41

If you want to translate a SQL type to a Clojure type that isn't handled automatically by the JDBC driver, consider IResultSetReadColumn as the "proper" way to do it (assuming you have a single DB in play).

seancorfield16:06:35

This all is why next.jdbc does not support :row-fn and :result-set-fn by the way -- I've seen so many people trip up on the laziness issue exposed through those functions.

seancorfield16:06:49

In next.jdbc, you either execute! to get an entire, fully-realized result set as a Clojure data structure (vector of hash maps, or vector of column names followed by row values -- as-arrays), or you use plan to produce an IReduceInit that you then reduce or transduce.

kostafey16:06:09

I don't want to fetch all result set, I only need to know a records count in :row-fn. Is it impossible?

ghadi16:06:12

row-fn does not have access to the whole result set

ghadi16:06:19

you need to use result-set-fn for that

ghadi16:06:38

row-fn has access to a single row from the result set

kszabo16:06:10

you can use a subselect in your db layer to get a count in your rows

kostafey16:06:17

I mean, ok, I will use :result-set-fn, but if I call (count rs) inside it, it will fetch all rs records. That is not good. Is it possible to count records without fetching?

kszabo16:06:05

but it’s going to do the work 2 times (but that is unavoidable)

kostafey16:06:15

@thenonameguy Thank you, looks it's an idea.

ghadi16:06:23

count(*) in your query

seancorfield16:06:38

SQL lets you do SELECT COUNT(*) AS total FROM ... and that won't "fetch all rs records". Use the right tool for the job.

👍 4
kostafey16:06:51

@seancorfield Ok, thank you very much.

seancorfield16:06:13

Depending on how invested you are already in clojure.java.jdbc, you might want to consider using next.jdbc which is a different API -- both simpler and, mostly, faster than clojure.java.jdbc and it's where effort will be focused for development, from now on (if it isn't clear, I maintain both libraries).

👍 4
seancorfield17:06:34

I also meant to say that

(def db
  {:subname (str "file://"
                 (.replaceAll (System/getProperty "user.home") "\\\\" "/")
                 "/tmp/data;AUTO_SERVER=TRUE")
   :subprotocol "h2"})
is not recommended as a way to specify data sources: :dbtype / :dbname is better supported (and is the "only" way in next.jdbc).

kostafey17:06:13

It's only for this temporary/demo/test case. I use clojure.java.jdbc in https://github.com/kostafey/ejc-sql

seancorfield17:06:55

Using Monolithic Contrib? That's been deprecated for eight years https://github.com/kostafey/ejc-sql/blob/master/project.clj#L7

kostafey17:06:55

Yep. Some parts of it require to be updated.

seancorfield17:06:38

Just FYI, your commit to pass row count to :row-fn would be a breaking change for anyone currently using that option so clojure.java.jdbc could not possibly do that (but I'm sure you know that).

kostafey17:06:37

Yes, it's a subject of this discussion. My clone of clojure.java.jdbc is only for tests & experiments. And I'll fix the last commit in ejc-sql soon.

seancorfield17:06:02

FWIW, I just looked over the Java docs for ResultSet and ResultSetMetadata and everything is cursor-based so there isn't even a Java-level method for getting the number of rows in a result set.

👍 4
seancorfield19:06:53

For resolving Clob values to String automatically, I'd probably still recommend extending the IResultSetReadColumn protocol to Clob. Just sayin' 🙂

seancorfield19:06:14

(then it happens automatically and you don't need to map over the rows at all)

Roger Amorin Vieira20:06:27

Hi, I'm learning Clojure about a 2 weeks and I'm building a small project with the language, and I want to build tests, but I don't know how to test functions that need the database, some people say to me use ring-mock but i don't have found any information how to use with a database, someone could help me? Some library? Piece of code? (Example of my code)

(defn insert-to-db
   [to-insert destin table-destin]
   (loop [index 0]
       (when (< index (count to-insert))
           (insert-element! destin table-destin (nth to-insert index))
           (recur (inc index))))
How do I test this? I think about to do with a database in localhost, but ins't a very good solution.

seancorfield20:06:30

Hi @contato509 First off, a stylistic comment: since you want to iterate through a collection and perform a side-effecting operation (and don't care about the return value), it's more idiomatic (and simpler) to use doseq:

(defn insert-to-db
  [to-insert destin table-destin]
  (doseq [row to-insert]
    (insert-element! destin table-destin row)))

seancorfield20:06:07

There are several approaches to testing things when you have a database involved. You can isolate the DB operations from the pure functions and test just the (pure) business logic, on the assumption that simple CRUD operations "just work". You can do that isolation and have the business logic return a data structure that describes the DB updates you want to perform -- and then test that you get the right structures back (and, yes, you'd separately need to write tests for the DB update logic against a real DB but you could treat that as a separate library -- and it should be simple generic CRUD code). You could try to mock the DB access functions (this can be a painful approach but sometimes it's worth it). You can run your tests inside a transaction and roll it back at the end -- so, use a real DB but just ensure your tests make no updates. You can run a DB in Docker and set it up / tear it down for your test suite as needed. And several other options.

seancorfield20:06:32

What we do at work mostly assumes a real DB in Docker with test data, and our test scripts setup the DB from scratch and tear it down completely around the tests.

Roger Amorin Vieira20:06:39

Thanks for the tip about doseq, will make my code more clean.

Roger Amorin Vieira20:06:12

I think I will take the painful approach, in my case will be better.