Fork me on GitHub
#sql
<
2018-01-31
>
danielcompton00:01:31

@briantrice what's missing in your opinion?

briantrice01:01:44

based on the above conversation, nearly everything

briantrice01:01:58

sqlalchemy’s declarative modeling is sort of a metamodel from which the migrations are derived. plus, database schemas get dumped and diffed for migration derivation/checking.

briantrice01:01:52

I want my SQL and migrations to be derived from a single checkable model.

briantrice01:01:14

I’ve had that in sqlalchemy+alembic and can’t find an equivalent.

briantrice01:01:32

Hibernate is not acceptable.

joost-diepenmaat10:01:33

I’m having a terrible time making sense of where opts are allowed to be “merged in” with the database spec in clojure.java.jdbc

joost-diepenmaat10:01:50

I’m trying to set up a sqlite connection with :entities defined as (quoted \") and that fails immediately

joost-diepenmaat10:01:58

without any body that already fails with

joost-diepenmaat11:01:09

java.sql.BatchUpdateException: batch entry 1: [SQLITE_ERROR] SQL error or missing database (near ".": syntax error)

joost-diepenmaat11:01:40

it works if I leave off the :entities option but then I don’t get quoting

joost-diepenmaat11:01:52

I also found that clojure.java.jdbc.spec fails to validate the (with-db-connection [db spec opts] ...) 3-arity variant

joost-diepenmaat11:01:05

but I’m not even sure that will work for :entities

joost-diepenmaat11:01:28

but I have no idea which namespace s here refers to

joost-diepenmaat11:01:00

there’s nothing like that in current clojure.java.jdbc jar as far as I can see

joost-diepenmaat11:01:24

What I’m trying to achieve is a way to pass in the connection and the quoting mechanism as a single argument so I don’t need to keep track of two related arguments everywhere.

joost-diepenmaat11:01:13

Ok, so I found a workaround, which is to use something similar to (jdbc/with-db-connection [db* map-without-entities-entry] (do-stuff (assoc db* :entities (quoted \"))))

joost-diepenmaat11:01:30

but it’s really hard to make out from the code or the docs that you should have to do these kinds of tricks, or even that a db connection is a map.

seancorfield18:01:52

@joost-diepenmaat Sorry you're having such a hard time. with-db-connection requires a body -- something to execute with that db connection. The following worked for me, for example:

user=> (jdbc/with-db-connection [db {:dbtype "sqlite" :dbname "clojure_test" :foreign_keys true :entities (jdbc/quoted \" )}] (jdbc/db-do-commands db (jdbc/create-table-ddl :fruit [[:color "varchar(20)"]])) (jdbc/query db "select * from fruit"))
()
user=> (jdbc/with-db-connection [db {:dbtype "sqlite" :dbname "clojure_test" :foreign_keys true :entities (jdbc/quoted \" )}] (jdbc/insert! db :fruit {:color "red"}) (jdbc/query db "select * from fruit"))
({:color "red"})
user=> 
(I recommend using the simpler :dbtype / :dbname approach)

seancorfield18:01:00

I've no idea where that page of documentation came from -- it's not in the source folder -- and it's referring to a namespace that doesn't even exist clojure.java.jdbc.sql -- can you tell me where you found a link to it? I can't find any links to it within the http://clojure-doc.org source code.

seancorfield18:01:18

(I'm thinking that maybe you're working from a tutorial online somewhere that is horribly out of date -- which is probably causing some of the difficulties you are running into 😞 )

seancorfield18:01:02

The spec validation issue is a bug -- and it affects all three with-db-* function -- thank you for spotting that! I've created https://dev.clojure.org/jira/browse/JDBC-165 to track that.

seancorfield18:01:38

(I added some debugging locally to verify that quoting really does work in the code I showed above)

joost-diepenmaat20:01:30

@seancorfield thanks for the helpful replies. Your example of with-db-connection still throws for me. Might be a difference in the java sqlite driver.

joost-diepenmaat20:01:25

could also have something todo with the as-properties coercion of the :entities key in get-connection

joost-diepenmaat20:01:26

That documentation is an article on http://clojure-doc.org - I also have no idea what the origin is, I just found it while googling for “clojure.java.jdbc entities”

seancorfield20:01:26

It's unfortunate that comes up in Google/Bing since it's not even meant to be there. I'll put up a stub page pointing to the correct docs which should change its footprint in Google/Bing...

joost-diepenmaat20:01:04

For me part of the problem in this particular case is that I’m upgrading a very old codebase from clojure 1.2 with clojure.contrib.sql to clojure 1.9 with clojure.java.jdbc so not everything on my end is as nice and clean as I’d like.

joost-diepenmaat20:01:13

But I must admit I am still fairly unclear on when to use opts as a separate parameter to various functions, and when it’s ok to merge options in the jdbc spec, and the difference between a db-spec and a db-connection (which I think is now clearer than it was in the old clojure.contrib.sql days)

seancorfield20:01:26

It's unfortunate that comes up in Google/Bing since it's not even meant to be there. I'll put up a stub page pointing to the correct docs which should change its footprint in Google/Bing...

seancorfield20:01:47

What version of SQLite's driver are you using @joost-diepenmaat?

joost-diepenmaat20:01:08

I just upgraded to org.xerial/sqlite-jdbc “3.21.0.1” (latest) and that fixes the SQLITE_ERROR in your example.

joost-diepenmaat20:01:24

I previously had org.xerial/sqlite-jdbc “3.6.20”

joost-diepenmaat20:01:44

So with that in, it seems I should be able to use :entities and :identifiers directly in the db spec.

seancorfield20:01:13

Yes. The basic idea is if you want options applied to every operation, you can put them in the DB spec. If you only need them for specific operations, you can specify them in those operations.

seancorfield20:01:01

In addition, the with-db-* macros let you specify options to apply to the newly opened connection (my bad -- wasn't what I previously said).

joost-diepenmaat20:01:18

ok thanks, that’s what I thought already I just got sidetracked by the SQLITE_ERROR issue

seancorfield20:01:40

Specifically, those with-db-* macros will get a new connection from the db-spec, produce a new db spec with the connection added in, bind that to the new symbol -- so when you have (jdbc/with-db-connection [db db-spec] ...) if db-spec is a map of options, so is db but it has the actual new connection in there as well.

seancorfield20:01:47

The docs are still a work in progress. I'd hoped moving them to http://clojure-doc.org (from the repo) would encourage more people to contribute -- since they could send PRs instead of dealing with the CA and patches -- but it hasn't. So it's mostly been down to me.

seancorfield21:01:43

Happy to help with any issues that come up using java.jdbc!

Jacob22:01:03

I got this statement to run

(defn drop-tables []  "DROP TABLE tmp_card_balance"
                                          "DROP TABLE tmp_xfer_jnl_current_balance")
with this statement
(jdbc/db-do-commands (@db-conn/db-connections (keyword "whfs-test")) [(queries/create-tables)])
but the tmp_card_balance is never dropped but the tmp_xfer_jnl_current_balance is dropped. Do you know what the issue could be?