Clojurians Log v2

Hello 🙂 I’m playing around with SQLite+next.jdbc, and I wonder how to deal with SQLite PRAGMA statements. Is it possible to have them defined in the data source or something?

@danielostling Can you give specific examples? (I use MySQL mainly so I don't know what sort of thing you're talking about)

Sure! I was trying to get the foreign_keys pragma to work for SQLite, to get on delete cascade behavior, as seen here: https://www.sqlite.org/foreignkeys.html#fk_actions It works when done in a console session with the sqlite3 binary, but I was not able to make it work with jdbc/execute-one!, sql/delete! or the same wrapped in jdbc/with-transaction where I would put the pragma in a separate jdbc/execute-one! statement before the actual SQL delete

So, one example of me not getting this to work is

(defn remove-policy
  "Remove a policy."
  [policy-id]
  (let [data-source (db-get-ds)]
    (jdbc/with-transaction [tx data-source]
      (jdbc/execute! tx ["pragma foreign_keys=on"])
      (jdbc/execute! tx ["delete from policy where id = ?" policy-id])))

Oh, it’s actually execute!, not execute-one! I now realize. Not sure if that matters though.

Under the hood, those behave the same way, but execute-one! only returns one "result" -- a hash map -- regardless of how many rows you have in the result. execute! always returns a vector of hash maps, even when the result only contains one row.

(so: it doesn't matter)

https://stackoverflow.com/questions/9958382/sqlite-jdbc-pragma-setting seems to suggest that you need to use a particular JDBC pooling library to make this happen?

Although looking at the source of the SQLite JDBC driver suggests that you might be able to pass these as connection options, directly creating the connection...

Passing :foreign_keys true in your db-spec when you make a connection might be enough to make that work @danielostling

i.e., {:dbtype "sqlite" :dbname "yourdb" :foreign_keys true}

Hm, does that replace jdbc/get-datasource or where should that be used?

(or it might need to be :foreignKeys true)

How are you specifying your connection details?

As a hash map (recommended) or as a jdbc: string?

“jdbc:sqlite:some-db.db”

I’ll switch to what I should be doing, that’s my inexperience showing 🙂

I’ll try it out a bit. Thanks for the help, I appreciate it. And thanks for next.jdbc, very useful 🙂