Details
-
Type:
Defect
-
Status:
Resolved
-
Priority:
Minor
-
Resolution: Completed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
-
Environment:Dependencies used:
[org.clojure/clojure "1.4.0"]
[org.clojure/java.jdbc "0.2.3"]
[org.xerial/sqlite-jdbc "3.7.2"]
Description
To be able to enforce foreign key constraints in SQLite you need to execute the following statement:
PRAGMA foreign_keys = ON;
However, this statement can't be used from within a transaction. From the SQLite docs:
It is not possible to enable or disable foreign key constraints in the middle of a multi-statement transaction (when SQLite is not in autocommit mode). Attempting to do so does not return an error; it simply has no effect.
In clojure.java.jdbc both do-commands and do-prepared unconditionally run the supplied statements in a transaction. To me it seems like these two functions are the only ones that provide a way to execute an arbitrary SQL statement.
Example using [org.clojure/java.jdbc "0.2.3"] and [org.xerial/sqlite-jdbc "3.7.2"]:
(sql/with-connection {:classname "org.sqlite.JDBC"
:subprotocol "sqlite"
:subname "foo.db"}
(sql/do-commands "PRAGMA foreign_keys = ON;")
(sql/with-query-results res ["PRAGMA foreign_keys;"]
(doall res)))
; => ({:foreign_keys 0})
When using this function instead of do-commands foreign keys work as expected:
(defn do-raw [& commands] (with-open [^java.sql.Statement stmt (let [^java.sql.Connection con (sql/connection)] (.createStatement con))] (doseq [^String cmd commands] (.addBatch stmt cmd)) (let [result (.executeBatch stmt)] (if (and (= 1 (count result)) (= -2 (first result))) (list (.getUpdateCount stmt)) (seq result)))))
This function is simply do-commands with execute-batch inlined and the wrapping transaction form removed.
It seems like the more flexible thing to do would be to allow users to specify that a transaction not be used on the existing operations. I'm reworking the API substantially soon so I'll bear that in mind. I might not make it possible with the existing API but I'll make sure that's an option in the new API that I'm adding.