Back
with-options (clj)
(source)function
(with-options connectable opts)
Given a connectable/transactable object and a set of (default) options
that should be used on all operations on that object, return a new
wrapper object that can be used in its place.
Bear in mind that `get-datasource`, `get-connection`, and `with-transaction`
return plain Java objects, so if you call any of those on this wrapped
object, you'll need to re-wrap the Java object `with-options` again. See
the Datasources, Connections & Transactions section of Getting Started for
more details, and some examples of use with these functions.
`with-transaction+options` exists to automatically rewrap a `Connection`
with the options from a `with-options` wrapper.
Examples
next-jdbc
(ns next.jdbc-test
"Basic tests for the primary API of `next.jdbc`."
(:require [clojure.core.reducers :as r]
[clojure.string :as str]
[clojure.test :refer [deftest is testing use-fixtures]]
[next.jdbc :as jdbc]
[next.jdbc.connection :as c]
[next.jdbc.test-fixtures
:refer [with-test-db db ds column
default-options stored-proc?
derby? hsqldb? jtds? mssql? mysql? postgres? sqlite?]]
[next.jdbc.prepare :as prep]
[next.jdbc.result-set :as rs]
[next.jdbc.specs :as specs]
[next.jdbc.types :as types])
(:import (com.zaxxer.hikari HikariDataSource)
(com.mchange.v2.c3p0 ComboPooledDataSource PooledDataSource)
(java.sql ResultSet ResultSetMetaData)))
(deftest spec-tests
(let [db-spec {:dbtype "h2:mem" :dbname "clojure_test"}]
;; some sanity checks on instrumented function calls:
(jdbc/get-datasource db-spec)
(jdbc/get-connection db-spec)
;; and again with options:
(let [db-spec' (jdbc/with-options db-spec {})]
(jdbc/get-datasource db-spec')
(jdbc/get-connection db-spec'))))
(deftest basic-tests
;; use ds-opts instead of (ds) anywhere you want default options applied:
(let [ds-opts (jdbc/with-options (ds) (default-options))]
(testing "plan"
(is (= "Apple"
(reduce (fn [_ row] (reduced (:name row)))
nil
(jdbc/plan
ds-opts
["select * from fruit where appearance = ?" "red"]))))
(is (= "Banana"
(reduce (fn [_ row] (reduced (:no-such-column row "Banana")))
nil
(jdbc/plan
ds-opts
["select * from fruit where appearance = ?" "red"])))))
(testing "execute-one!"
(is (nil? (jdbc/execute-one!
(ds)
["select * from fruit where appearance = ?" "neon-green"])))
(is (= "Apple" ((column :FRUIT/NAME)
(jdbc/execute-one!
ds-opts
["select * from fruit where appearance = ?" "red"]))))
(is (= "red" (:fruit/looks-like
(jdbc/execute-one!
ds-opts
["select appearance as looks_like from fruit where id = ?" 1]
jdbc/snake-kebab-opts))))
(let [ds' (jdbc/with-options ds-opts jdbc/snake-kebab-opts)]
(is (= "red" (:fruit/looks-like
(jdbc/execute-one!
ds'
["select appearance as looks_like from fruit where id = ?" 1])))))
(jdbc/with-transaction+options [ds' (jdbc/with-options ds-opts jdbc/snake-kebab-opts)]
(is (= (merge (default-options) jdbc/snake-kebab-opts)
(:options ds')))
(is (= "red" (:fruit/looks-like
(jdbc/execute-one!
ds'
["select appearance as looks_like from fruit where id = ?" 1])))))
(is (= "red" (:looks-like
(jdbc/execute-one!
ds-opts
["select appearance as looks_like from fruit where id = ?" 1]
jdbc/unqualified-snake-kebab-opts)))))
(testing "execute!"
(let [rs (jdbc/execute!
ds-opts
["select * from fruit where appearance = ?" "neon-green"])]
(is (vector? rs))
(is (= [] rs)))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit where appearance = ?" "red"])]
(is (= 1 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs)))))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:builder-fn rs/as-maps})]
(is (every? map? rs))
(is (every? meta rs))
(is (= 4 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 4 ((column :FRUIT/ID) (last rs)))))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:builder-fn rs/as-arrays})]
(is (every? vector? rs))
(is (= 5 (count rs)))
(is (every? #(= 5 (count %)) rs))
;; columns come first
(is (every? qualified-keyword? (first rs)))
;; :FRUIT/ID should be first column
(is (= (column :FRUIT/ID) (ffirst rs)))
;; and all its corresponding values should be ints
(is (every? int? (map first (rest rs))))
(is (every? string? (map second (rest rs))))))
(testing "execute! with adapter"
(let [rs (jdbc/execute! ; test again, with adapter and lower columns
ds-opts
["select * from fruit order by id"]
{:builder-fn (rs/as-arrays-adapter
rs/as-lower-arrays
(fn [^ResultSet rs _ ^Integer i]
(.getObject rs i)))})]
(is (every? vector? rs))
(is (= 5 (count rs)))
(is (every? #(= 5 (count %)) rs))
;; columns come first
(is (every? qualified-keyword? (first rs)))
;; :fruit/id should be first column
(is (= :fruit/id (ffirst rs)))
;; and all its corresponding values should be ints
(is (every? int? (map first (rest rs))))
(is (every? string? (map second (rest rs))))))
(testing "execute! with unqualified"
(let [rs (jdbc/execute!
(ds)
["select * from fruit order by id"]
{:builder-fn rs/as-unqualified-maps})]
(is (every? map? rs))
(is (every? meta rs))
(is (= 4 (count rs)))
(is (= 1 ((column :ID) (first rs))))
(is (= 4 ((column :ID) (last rs)))))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:builder-fn rs/as-unqualified-arrays})]
(is (every? vector? rs))
(is (= 5 (count rs)))
(is (every? #(= 5 (count %)) rs))
;; columns come first
(is (every? simple-keyword? (first rs)))
;; :ID should be first column
(is (= (column :ID) (ffirst rs)))
;; and all its corresponding values should be ints
(is (every? int? (map first (rest rs))))
(is (every? string? (map second (rest rs))))))
(testing "execute! with :max-rows / :maxRows"
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:max-rows 2})]
(is (every? map? rs))
(is (every? meta rs))
(is (= 2 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 2 ((column :FRUIT/ID) (last rs)))))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:statement {:maxRows 2}})]
(is (every? map? rs))
(is (every? meta rs))
(is (= 2 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 2 ((column :FRUIT/ID) (last rs)))))))
(testing "prepare"
;; default options do not flow over get-connection
(let [rs (with-open [con (jdbc/get-connection (ds))
ps (jdbc/prepare
con
["select * from fruit order by id"]
(default-options))]
(jdbc/execute! ps))]
(is (every? map? rs))
(is (every? meta rs))
(is (= 4 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 4 ((column :FRUIT/ID) (last rs)))))
;; default options do not flow over get-connection
(let [rs (with-open [con (jdbc/get-connection (ds))
ps (jdbc/prepare
con
["select * from fruit where id = ?"]
(default-options))]
(jdbc/execute! (prep/set-parameters ps [4]) nil {}))]
(is (every? map? rs))
(is (every? meta rs))
(is (= 1 (count rs)))
(is (= 4 ((column :FRUIT/ID) (first rs))))))
(testing "statement"
;; default options do not flow over get-connection
(let [rs (with-open [con (jdbc/get-connection (ds))]
(jdbc/execute! (prep/statement con (default-options))
["select * from fruit order by id"]))]
(is (every? map? rs))
(is (every? meta rs))
(is (= 4 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 4 ((column :FRUIT/ID) (last rs)))))
;; default options do not flow over get-connection
(let [rs (with-open [con (jdbc/get-connection (ds))]
(jdbc/execute! (prep/statement con (default-options))
["select * from fruit where id = 4"]))]
(is (every? map? rs))
(is (every? meta rs))
(is (= 1 (count rs)))
(is (= 4 ((column :FRUIT/ID) (first rs))))))
(testing "transact"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/transact (ds)
(fn [t] (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"]))
{:rollback-only true})))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "with-transaction rollback-only"
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(is (jdbc/active-tx?) "should be in a transaction")
(jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"]))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con {:rollback-only true}]
(is (jdbc/active-tx?) "should be in a transaction")
(jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"]))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction exception"
(is (thrown? Throwable
(jdbc/with-transaction [t (ds)]
(jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])
(is (jdbc/active-tx?) "should be in a transaction")
(throw (ex-info "abort" {})))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (thrown? Throwable
(jdbc/with-transaction [t con]
(jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])
(is (jdbc/active-tx?) "should be in a transaction")
(throw (ex-info "abort" {})))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction call rollback"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t (ds)]
(let [result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t)
;; still in a next.jdbc TX even tho' we rolled back!
(is (jdbc/active-tx?) "should be in a transaction")
result))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction with unnamed save point"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t (ds)]
(let [save-point (.setSavepoint t)
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
;; still in a next.jdbc TX even tho' we rolled back to a save point!
(is (jdbc/active-tx?) "should be in a transaction")
result))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [save-point (.setSavepoint t)
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction with named save point"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t (ds)]
(let [save-point (.setSavepoint t (name (gensym)))
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [save-point (.setSavepoint t (name (gensym)))
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con)))))))
(deftest execute-batch-connectable-tests
(testing "simple batch insert"
(is (= [1 1 1 1 1 1 1 1 1 13]
(try
(let [result (jdbc/execute-batch! (ds)
"INSERT INTO fruit (name, appearance) VALUES (?,?)"
[["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{})]
(conj result (count (jdbc/execute! (ds) ["select * from fruit"]))))
(finally
(jdbc/execute-one! (ds) ["delete from fruit where id > 4"])))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "batch with-options"
(is (= [1 1 1 1 1 1 1 1 1 13]
(try
(let [result (jdbc/execute-batch! (jdbc/with-options (ds) {})
"INSERT INTO fruit (name, appearance) VALUES (?,?)"
[["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{})]
(conj result (count (jdbc/execute! (ds) ["select * from fruit"]))))
(finally
(jdbc/execute-one! (ds) ["delete from fruit where id > 4"])))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "batch with-logging"
(is (= [1 1 1 1 1 1 1 1 1 13]
(try
(let [result (jdbc/execute-batch! (jdbc/with-logging (ds) println println)
"INSERT INTO fruit (name, appearance) VALUES (?,?)"
[["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{})]
(conj result (count (jdbc/execute! (ds) ["select * from fruit"]))))
(finally
(jdbc/execute-one! (ds) ["delete from fruit where id > 4"])))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "small batch insert"
(is (= [1 1 1 1 1 1 1 1 1 13]
(try
(let [result (jdbc/execute-batch! (ds)
"INSERT INTO fruit (name, appearance) VALUES (?,?)"
[["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{:batch-size 3})]
(conj result (count (jdbc/execute! (ds) ["select * from fruit"]))))
(finally
(jdbc/execute-one! (ds) ["delete from fruit where id > 4"])))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "big batch insert"
(is (= [1 1 1 1 1 1 1 1 1 13]
(try
(let [result (jdbc/execute-batch! (ds)
"INSERT INTO fruit (name, appearance) VALUES (?,?)"
[["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{:batch-size 8})]
(conj result (count (jdbc/execute! (ds) ["select * from fruit"]))))
(finally
(jdbc/execute-one! (ds) ["delete from fruit where id > 4"])))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "large batch insert"
(when-not (or (jtds?) (sqlite?))
(is (= [1 1 1 1 1 1 1 1 1 13]
(try
(let [result (jdbc/execute-batch! (ds)
"INSERT INTO fruit (name, appearance) VALUES (?,?)"
[["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{:batch-size 4
:large true})]
(conj result (count (jdbc/execute! (ds) ["select * from fruit"]))))
(finally
(jdbc/execute-one! (ds) ["delete from fruit where id > 4"])))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))))
(testing "return generated keys"
(when-not (or (mssql?) (sqlite?))
(let [results
(try
(let [result (jdbc/execute-batch! (ds)
"INSERT INTO fruit (name, appearance) VALUES (?,?)"
[["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
;; note: we need both :return-keys true for creating
;; the PreparedStatement and :return-generated-keys
;; true to control the way batch execution happens:
{:batch-size 4 :return-keys true
:return-generated-keys true})]
(conj result (count (jdbc/execute! (ds) ["select * from fruit"]))))
(finally
(jdbc/execute-one! (ds) ["delete from fruit where id > 4"])))]
(is (= 13 (last results)))
(is (every? map? (butlast results)))
;; Derby and SQLite only return one generated key per batch so there
;; are only three keys, plus the overall count here:
(is (< 3 (count results))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))))
(deftest issue-204
(testing "against a Connection"
(is (seq (with-open [con (jdbc/get-connection (ds))]
(jdbc/on-connection
[x con]
(jdbc/execute! x ["select * from fruit"]))))))
(testing "against a wrapped Connection"
(is (seq (with-open [con (jdbc/get-connection (ds))]
(jdbc/on-connection
[x (jdbc/with-options con {})]
(jdbc/execute! x ["select * from fruit"]))))))
(testing "against a wrapped Datasource"
(is (seq (jdbc/on-connection
[x (jdbc/with-options (ds) {})]
(jdbc/execute! x ["select * from fruit"])))))
(testing "against a Datasource"
(is (seq (jdbc/on-connection
[x (ds)]
(jdbc/execute! x ["select * from fruit"]))))))
(deftest issue-256
(testing "against a Connection"
(is (seq (with-open [con (jdbc/get-connection (ds))]
(jdbc/on-connection+options
[x con] ; raw connection stays raw
(is (instance? java.sql.Connection x))
(jdbc/execute! x ["select * from fruit"]))))))
(testing "against a wrapped Connection"
(is (seq (with-open [con (jdbc/get-connection (ds))]
(jdbc/on-connection+options
[x (jdbc/with-options con {:test-option 42})]
;; ensure we get the same wrapped connection
(is (instance? java.sql.Connection (:connectable x)))
(is (= {:test-option 42} (:options x)))
(jdbc/execute! x ["select * from fruit"]))))))
(testing "against a wrapped Datasource"
(is (seq (jdbc/on-connection+options
[x (jdbc/with-options (ds) {:test-option 42})]
;; ensure we get a wrapped connection
(is (instance? java.sql.Connection (:connectable x)))
(is (= {:test-option 42} (:options x)))
(jdbc/execute! x ["select * from fruit"])))))
(testing "against a Datasource"
(is (seq (jdbc/on-connection+options
[x (ds)] ; unwrapped datasource has no options
;; ensure we get a wrapped connection (empty options)
(is (instance? java.sql.Connection (:connectable x)))
(is (= {} (:options x)))
(jdbc/execute! x ["select * from fruit"]))))))
next-jdbc
(ns next.jdbc.sql-test
"Tests for the syntactic sugar SQL functions."
(:require [clojure.test :refer [deftest is testing use-fixtures]]
[next.jdbc :as jdbc]
[next.jdbc.specs :as specs]
[next.jdbc.sql :as sql]
[next.jdbc.test-fixtures
:refer [with-test-db ds column default-options
derby? jtds? maria? mssql? mysql? postgres? sqlite?]]
[next.jdbc.types :refer [as-other as-real as-varchar]]))
(deftest test-query
(let [ds-opts (jdbc/with-options (ds) (default-options))
rs (sql/query ds-opts ["select * from fruit order by id"])]
(is (= 4 (count rs)))
(is (every? map? rs))
(is (every? meta rs))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 4 ((column :FRUIT/ID) (last rs))))))
(deftest test-find-all-offset
(let [ds-opts (jdbc/with-options (ds) (default-options))
rs (sql/find-by-keys
ds-opts :fruit :all
(assoc
(if (or (mysql?) (sqlite?))
{:limit 2 :offset 1}
{:offset 1 :fetch 2})
:columns [:ID
["CASE WHEN grade > 91 THEN 'ok ' ELSE 'bad' END"
:QUALITY]]
:order-by [:id]))]
(is (= 2 (count rs)))
(is (every? map? rs))
(is (every? meta rs))
(is (every? #(= 2 (count %)) rs))
(is (= 2 ((column :FRUIT/ID) (first rs))))
(is (= "ok " ((column :QUALITY) (first rs))))
(is (= 3 ((column :FRUIT/ID) (last rs))))
(is (= "bad" ((column :QUALITY) (last rs))))))
(deftest test-find-by-keys
(let [ds-opts (jdbc/with-options (ds) (default-options))]
(let [rs (sql/find-by-keys ds-opts :fruit {:appearance "neon-green"})]
(is (vector? rs))
(is (= [] rs)))
(let [rs (sql/find-by-keys ds-opts :fruit {:appearance "yellow"})]
(is (= 1 (count rs)))
(is (every? map? rs))
(is (every? meta rs))
(is (= 2 ((column :FRUIT/ID) (first rs)))))))
(deftest test-get-by-id
(let [ds-opts (jdbc/with-options (ds) (default-options))]
(is (nil? (sql/get-by-id ds-opts :fruit -1)))
(let [row (sql/get-by-id ds-opts :fruit 3)]
(is (map? row))
(is (= "Peach" ((column :FRUIT/NAME) row))))
(let [row (sql/get-by-id ds-opts :fruit "juicy" :appearance {})]
(is (map? row))
(is (= 4 ((column :FRUIT/ID) row)))
(is (= "Orange" ((column :FRUIT/NAME) row))))
(let [row (sql/get-by-id ds-opts :fruit "Banana" :FRUIT/NAME {})]
(is (map? row))
(is (= 2 ((column :FRUIT/ID) row))))))
(deftest test-update!
(let [ds-opts (jdbc/with-options (ds) (default-options))]
(try
(is (= {:next.jdbc/update-count 1}
(sql/update! ds-opts :fruit {:appearance "brown"} {:id 2})))
(is (= "brown" ((column :FRUIT/APPEARANCE)
(sql/get-by-id ds-opts :fruit 2))))
(finally
(sql/update! ds-opts :fruit {:appearance "yellow"} {:id 2})))
(try
(is (= {:next.jdbc/update-count 1}
(sql/update! ds-opts :fruit {:appearance "green"}
["name = ?" "Banana"])))
(is (= "green" ((column :FRUIT/APPEARANCE)
(sql/get-by-id ds-opts :fruit 2))))
(finally
(sql/update! ds-opts :fruit {:appearance "yellow"} {:id 2})))))
seancorfield/next-jdbc
(ns next.jdbc.sql-test
"Tests for the syntactic sugar SQL functions."
(:require [clojure.test :refer [deftest is testing use-fixtures]]
[next.jdbc :as jdbc]
[next.jdbc.specs :as specs]
[next.jdbc.sql :as sql]
[next.jdbc.test-fixtures
:refer [with-test-db ds column default-options
derby? jtds? maria? mssql? mysql? postgres? sqlite?]]
[next.jdbc.types :refer [as-other as-real as-varchar]]))
(deftest test-query
(let [ds-opts (jdbc/with-options (ds) (default-options))
rs (sql/query ds-opts ["select * from fruit order by id"])]
(is (= 4 (count rs)))
(is (every? map? rs))
(is (every? meta rs))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 4 ((column :FRUIT/ID) (last rs))))))
(deftest test-find-all-offset
(let [ds-opts (jdbc/with-options (ds) (default-options))
rs (sql/find-by-keys
ds-opts :fruit :all
(assoc
(if (or (mysql?) (sqlite?))
{:limit 2 :offset 1}
{:offset 1 :fetch 2})
:columns [:ID
["CASE WHEN grade > 91 THEN 'ok ' ELSE 'bad' END"
:QUALITY]]
:order-by [:id]))]
(is (= 2 (count rs)))
(is (every? map? rs))
(is (every? meta rs))
(is (every? #(= 2 (count %)) rs))
(is (= 2 ((column :FRUIT/ID) (first rs))))
(is (= "ok " ((column :QUALITY) (first rs))))
(is (= 3 ((column :FRUIT/ID) (last rs))))
(is (= "bad" ((column :QUALITY) (last rs))))))
(deftest test-find-by-keys
(let [ds-opts (jdbc/with-options (ds) (default-options))]
(let [rs (sql/find-by-keys ds-opts :fruit {:appearance "neon-green"})]
(is (vector? rs))
(is (= [] rs)))
(let [rs (sql/find-by-keys ds-opts :fruit {:appearance "yellow"})]
(is (= 1 (count rs)))
(is (every? map? rs))
(is (every? meta rs))
(is (= 2 ((column :FRUIT/ID) (first rs)))))))
(deftest test-get-by-id
(let [ds-opts (jdbc/with-options (ds) (default-options))]
(is (nil? (sql/get-by-id ds-opts :fruit -1)))
(let [row (sql/get-by-id ds-opts :fruit 3)]
(is (map? row))
(is (= "Peach" ((column :FRUIT/NAME) row))))
(let [row (sql/get-by-id ds-opts :fruit "juicy" :appearance {})]
(is (map? row))
(is (= 4 ((column :FRUIT/ID) row)))
(is (= "Orange" ((column :FRUIT/NAME) row))))
(let [row (sql/get-by-id ds-opts :fruit "Banana" :FRUIT/NAME {})]
(is (map? row))
(is (= 2 ((column :FRUIT/ID) row))))))
(deftest test-update!
(let [ds-opts (jdbc/with-options (ds) (default-options))]
(try
(is (= {:next.jdbc/update-count 1}
(sql/update! ds-opts :fruit {:appearance "brown"} {:id 2})))
(is (= "brown" ((column :FRUIT/APPEARANCE)
(sql/get-by-id ds-opts :fruit 2))))
(finally
(sql/update! ds-opts :fruit {:appearance "yellow"} {:id 2})))
(try
(is (= {:next.jdbc/update-count 1}
(sql/update! ds-opts :fruit {:appearance "green"}
["name = ?" "Banana"])))
(is (= "green" ((column :FRUIT/APPEARANCE)
(sql/get-by-id ds-opts :fruit 2))))
(finally
(sql/update! ds-opts :fruit {:appearance "yellow"} {:id 2})))))
seancorfield/next-jdbc
(ns next.jdbc-test
"Basic tests for the primary API of `next.jdbc`."
(:require [clojure.core.reducers :as r]
[clojure.string :as str]
[clojure.test :refer [deftest is testing use-fixtures]]
[next.jdbc :as jdbc]
[next.jdbc.connection :as c]
[next.jdbc.test-fixtures
:refer [with-test-db db ds column
default-options stored-proc?
derby? hsqldb? jtds? mssql? mysql? postgres? sqlite?]]
[next.jdbc.prepare :as prep]
[next.jdbc.result-set :as rs]
[next.jdbc.specs :as specs]
[next.jdbc.types :as types])
(:import (com.zaxxer.hikari HikariDataSource)
(com.mchange.v2.c3p0 ComboPooledDataSource PooledDataSource)
(java.sql ResultSet ResultSetMetaData)))
(deftest spec-tests
(let [db-spec {:dbtype "h2:mem" :dbname "clojure_test"}]
;; some sanity checks on instrumented function calls:
(jdbc/get-datasource db-spec)
(jdbc/get-connection db-spec)
;; and again with options:
(let [db-spec' (jdbc/with-options db-spec {})]
(jdbc/get-datasource db-spec')
(jdbc/get-connection db-spec'))))
(deftest basic-tests
;; use ds-opts instead of (ds) anywhere you want default options applied:
(let [ds-opts (jdbc/with-options (ds) (default-options))]
(testing "plan"
(is (= "Apple"
(reduce (fn [_ row] (reduced (:name row)))
nil
(jdbc/plan
ds-opts
["select * from fruit where appearance = ?" "red"]))))
(is (= "Banana"
(reduce (fn [_ row] (reduced (:no-such-column row "Banana")))
nil
(jdbc/plan
ds-opts
["select * from fruit where appearance = ?" "red"])))))
(testing "execute-one!"
(is (nil? (jdbc/execute-one!
(ds)
["select * from fruit where appearance = ?" "neon-green"])))
(is (= "Apple" ((column :FRUIT/NAME)
(jdbc/execute-one!
ds-opts
["select * from fruit where appearance = ?" "red"]))))
(is (= "red" (:fruit/looks-like
(jdbc/execute-one!
ds-opts
["select appearance as looks_like from fruit where id = ?" 1]
jdbc/snake-kebab-opts))))
(let [ds' (jdbc/with-options ds-opts jdbc/snake-kebab-opts)]
(is (= "red" (:fruit/looks-like
(jdbc/execute-one!
ds'
["select appearance as looks_like from fruit where id = ?" 1])))))
(jdbc/with-transaction+options [ds' (jdbc/with-options ds-opts jdbc/snake-kebab-opts)]
(is (= (merge (default-options) jdbc/snake-kebab-opts)
(:options ds')))
(is (= "red" (:fruit/looks-like
(jdbc/execute-one!
ds'
["select appearance as looks_like from fruit where id = ?" 1])))))
(is (= "red" (:looks-like
(jdbc/execute-one!
ds-opts
["select appearance as looks_like from fruit where id = ?" 1]
jdbc/unqualified-snake-kebab-opts)))))
(testing "execute!"
(let [rs (jdbc/execute!
ds-opts
["select * from fruit where appearance = ?" "neon-green"])]
(is (vector? rs))
(is (= [] rs)))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit where appearance = ?" "red"])]
(is (= 1 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs)))))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:builder-fn rs/as-maps})]
(is (every? map? rs))
(is (every? meta rs))
(is (= 4 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 4 ((column :FRUIT/ID) (last rs)))))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:builder-fn rs/as-arrays})]
(is (every? vector? rs))
(is (= 5 (count rs)))
(is (every? #(= 5 (count %)) rs))
;; columns come first
(is (every? qualified-keyword? (first rs)))
;; :FRUIT/ID should be first column
(is (= (column :FRUIT/ID) (ffirst rs)))
;; and all its corresponding values should be ints
(is (every? int? (map first (rest rs))))
(is (every? string? (map second (rest rs))))))
(testing "execute! with adapter"
(let [rs (jdbc/execute! ; test again, with adapter and lower columns
ds-opts
["select * from fruit order by id"]
{:builder-fn (rs/as-arrays-adapter
rs/as-lower-arrays
(fn [^ResultSet rs _ ^Integer i]
(.getObject rs i)))})]
(is (every? vector? rs))
(is (= 5 (count rs)))
(is (every? #(= 5 (count %)) rs))
;; columns come first
(is (every? qualified-keyword? (first rs)))
;; :fruit/id should be first column
(is (= :fruit/id (ffirst rs)))
;; and all its corresponding values should be ints
(is (every? int? (map first (rest rs))))
(is (every? string? (map second (rest rs))))))
(testing "execute! with unqualified"
(let [rs (jdbc/execute!
(ds)
["select * from fruit order by id"]
{:builder-fn rs/as-unqualified-maps})]
(is (every? map? rs))
(is (every? meta rs))
(is (= 4 (count rs)))
(is (= 1 ((column :ID) (first rs))))
(is (= 4 ((column :ID) (last rs)))))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:builder-fn rs/as-unqualified-arrays})]
(is (every? vector? rs))
(is (= 5 (count rs)))
(is (every? #(= 5 (count %)) rs))
;; columns come first
(is (every? simple-keyword? (first rs)))
;; :ID should be first column
(is (= (column :ID) (ffirst rs)))
;; and all its corresponding values should be ints
(is (every? int? (map first (rest rs))))
(is (every? string? (map second (rest rs))))))
(testing "execute! with :max-rows / :maxRows"
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:max-rows 2})]
(is (every? map? rs))
(is (every? meta rs))
(is (= 2 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 2 ((column :FRUIT/ID) (last rs)))))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:statement {:maxRows 2}})]
(is (every? map? rs))
(is (every? meta rs))
(is (= 2 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 2 ((column :FRUIT/ID) (last rs)))))))
(testing "prepare"
;; default options do not flow over get-connection
(let [rs (with-open [con (jdbc/get-connection (ds))
ps (jdbc/prepare
con
["select * from fruit order by id"]
(default-options))]
(jdbc/execute! ps))]
(is (every? map? rs))
(is (every? meta rs))
(is (= 4 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 4 ((column :FRUIT/ID) (last rs)))))
;; default options do not flow over get-connection
(let [rs (with-open [con (jdbc/get-connection (ds))
ps (jdbc/prepare
con
["select * from fruit where id = ?"]
(default-options))]
(jdbc/execute! (prep/set-parameters ps [4]) nil {}))]
(is (every? map? rs))
(is (every? meta rs))
(is (= 1 (count rs)))
(is (= 4 ((column :FRUIT/ID) (first rs))))))
(testing "statement"
;; default options do not flow over get-connection
(let [rs (with-open [con (jdbc/get-connection (ds))]
(jdbc/execute! (prep/statement con (default-options))
["select * from fruit order by id"]))]
(is (every? map? rs))
(is (every? meta rs))
(is (= 4 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 4 ((column :FRUIT/ID) (last rs)))))
;; default options do not flow over get-connection
(let [rs (with-open [con (jdbc/get-connection (ds))]
(jdbc/execute! (prep/statement con (default-options))
["select * from fruit where id = 4"]))]
(is (every? map? rs))
(is (every? meta rs))
(is (= 1 (count rs)))
(is (= 4 ((column :FRUIT/ID) (first rs))))))
(testing "transact"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/transact (ds)
(fn [t] (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"]))
{:rollback-only true})))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "with-transaction rollback-only"
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(is (jdbc/active-tx?) "should be in a transaction")
(jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"]))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con {:rollback-only true}]
(is (jdbc/active-tx?) "should be in a transaction")
(jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"]))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction exception"
(is (thrown? Throwable
(jdbc/with-transaction [t (ds)]
(jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])
(is (jdbc/active-tx?) "should be in a transaction")
(throw (ex-info "abort" {})))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (thrown? Throwable
(jdbc/with-transaction [t con]
(jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])
(is (jdbc/active-tx?) "should be in a transaction")
(throw (ex-info "abort" {})))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction call rollback"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t (ds)]
(let [result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t)
;; still in a next.jdbc TX even tho' we rolled back!
(is (jdbc/active-tx?) "should be in a transaction")
result))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction with unnamed save point"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t (ds)]
(let [save-point (.setSavepoint t)
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
;; still in a next.jdbc TX even tho' we rolled back to a save point!
(is (jdbc/active-tx?) "should be in a transaction")
result))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [save-point (.setSavepoint t)
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction with named save point"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t (ds)]
(let [save-point (.setSavepoint t (name (gensym)))
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [save-point (.setSavepoint t (name (gensym)))
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con)))))))
(deftest execute-batch-connectable-tests
(testing "simple batch insert"
(is (= [1 1 1 1 1 1 1 1 1 13]
(try
(let [result (jdbc/execute-batch! (ds)
"INSERT INTO fruit (name, appearance) VALUES (?,?)"
[["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{})]
(conj result (count (jdbc/execute! (ds) ["select * from fruit"]))))
(finally
(jdbc/execute-one! (ds) ["delete from fruit where id > 4"])))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "batch with-options"
(is (= [1 1 1 1 1 1 1 1 1 13]
(try
(let [result (jdbc/execute-batch! (jdbc/with-options (ds) {})
"INSERT INTO fruit (name, appearance) VALUES (?,?)"
[["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{})]
(conj result (count (jdbc/execute! (ds) ["select * from fruit"]))))
(finally
(jdbc/execute-one! (ds) ["delete from fruit where id > 4"])))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "batch with-logging"
(is (= [1 1 1 1 1 1 1 1 1 13]
(try
(let [result (jdbc/execute-batch! (jdbc/with-logging (ds) println println)
"INSERT INTO fruit (name, appearance) VALUES (?,?)"
[["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{})]
(conj result (count (jdbc/execute! (ds) ["select * from fruit"]))))
(finally
(jdbc/execute-one! (ds) ["delete from fruit where id > 4"])))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "small batch insert"
(is (= [1 1 1 1 1 1 1 1 1 13]
(try
(let [result (jdbc/execute-batch! (ds)
"INSERT INTO fruit (name, appearance) VALUES (?,?)"
[["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{:batch-size 3})]
(conj result (count (jdbc/execute! (ds) ["select * from fruit"]))))
(finally
(jdbc/execute-one! (ds) ["delete from fruit where id > 4"])))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "big batch insert"
(is (= [1 1 1 1 1 1 1 1 1 13]
(try
(let [result (jdbc/execute-batch! (ds)
"INSERT INTO fruit (name, appearance) VALUES (?,?)"
[["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{:batch-size 8})]
(conj result (count (jdbc/execute! (ds) ["select * from fruit"]))))
(finally
(jdbc/execute-one! (ds) ["delete from fruit where id > 4"])))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "large batch insert"
(when-not (or (jtds?) (sqlite?))
(is (= [1 1 1 1 1 1 1 1 1 13]
(try
(let [result (jdbc/execute-batch! (ds)
"INSERT INTO fruit (name, appearance) VALUES (?,?)"
[["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{:batch-size 4
:large true})]
(conj result (count (jdbc/execute! (ds) ["select * from fruit"]))))
(finally
(jdbc/execute-one! (ds) ["delete from fruit where id > 4"])))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))))
(testing "return generated keys"
(when-not (or (mssql?) (sqlite?))
(let [results
(try
(let [result (jdbc/execute-batch! (ds)
"INSERT INTO fruit (name, appearance) VALUES (?,?)"
[["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
;; note: we need both :return-keys true for creating
;; the PreparedStatement and :return-generated-keys
;; true to control the way batch execution happens:
{:batch-size 4 :return-keys true
:return-generated-keys true})]
(conj result (count (jdbc/execute! (ds) ["select * from fruit"]))))
(finally
(jdbc/execute-one! (ds) ["delete from fruit where id > 4"])))]
(is (= 13 (last results)))
(is (every? map? (butlast results)))
;; Derby and SQLite only return one generated key per batch so there
;; are only three keys, plus the overall count here:
(is (< 3 (count results))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))))
(deftest issue-204
(testing "against a Connection"
(is (seq (with-open [con (jdbc/get-connection (ds))]
(jdbc/on-connection
[x con]
(jdbc/execute! x ["select * from fruit"]))))))
(testing "against a wrapped Connection"
(is (seq (with-open [con (jdbc/get-connection (ds))]
(jdbc/on-connection
[x (jdbc/with-options con {})]
(jdbc/execute! x ["select * from fruit"]))))))
(testing "against a wrapped Datasource"
(is (seq (jdbc/on-connection
[x (jdbc/with-options (ds) {})]
(jdbc/execute! x ["select * from fruit"])))))
(testing "against a Datasource"
(is (seq (jdbc/on-connection
[x (ds)]
(jdbc/execute! x ["select * from fruit"]))))))
(deftest issue-256
(testing "against a Connection"
(is (seq (with-open [con (jdbc/get-connection (ds))]
(jdbc/on-connection+options
[x con] ; raw connection stays raw
(is (instance? java.sql.Connection x))
(jdbc/execute! x ["select * from fruit"]))))))
(testing "against a wrapped Connection"
(is (seq (with-open [con (jdbc/get-connection (ds))]
(jdbc/on-connection+options
[x (jdbc/with-options con {:test-option 42})]
;; ensure we get the same wrapped connection
(is (instance? java.sql.Connection (:connectable x)))
(is (= {:test-option 42} (:options x)))
(jdbc/execute! x ["select * from fruit"]))))))
(testing "against a wrapped Datasource"
(is (seq (jdbc/on-connection+options
[x (jdbc/with-options (ds) {:test-option 42})]
;; ensure we get a wrapped connection
(is (instance? java.sql.Connection (:connectable x)))
(is (= {:test-option 42} (:options x)))
(jdbc/execute! x ["select * from fruit"])))))
(testing "against a Datasource"
(is (seq (jdbc/on-connection+options
[x (ds)] ; unwrapped datasource has no options
;; ensure we get a wrapped connection (empty options)
(is (instance? java.sql.Connection (:connectable x)))
(is (= {} (:options x)))
(jdbc/execute! x ["select * from fruit"]))))))
souenzzo/atemoia
(ns atemoia.server-test
(:require [atemoia.server :as atemoia]
[cheshire.core :as json]
[clojure.test :refer [deftest is]]
[io.pedestal.http :as http]
[io.pedestal.test :refer [response-for]]
[next.jdbc :as jdbc]
[next.jdbc.result-set :as rs]))
(deftest hello
(let [atm-conn (jdbc/with-options (jdbc/get-connection {:jdbcUrl "jdbc:h2:mem:"})
{:builder-fn rs/as-lower-maps})
service-fn (-> {::atemoia/atm-conn atm-conn}
atemoia/create-service
http/dev-interceptors
http/create-servlet
::http/service-fn)]
(atemoia/install-schema {::atemoia/atm-conn atm-conn})
(is (= []
(-> service-fn
(response-for :get "/todo")
:body
(json/parse-string true)))
"fetching todos before creating")
(is (= 201
(-> service-fn
(response-for :post "/todo"
:body (json/generate-string {:note "hello world"}))
:status))
"creating a todo")
(is (= [{:todo/id 1
:todo/note "hello world"}]
(-> service-fn
(response-for :get "/todo")
:body
(json/parse-string true)))
"fetching the todos, after creating one")
(dotimes [idx 10]
(is (= 201
(-> service-fn
(response-for :post "/todo"
:body (json/generate-string {:note (str "hello world" idx)}))
:status))
(str "creating many todos: idx = " idx)))
(is (== 10
(-> service-fn
(response-for :get "/todo")
:body
(json/parse-string true)
count))
"fetching many todos")))
jacekschae/learn-reitit-course-files
(ns cheffy.conversation.db
(:require [next.jdbc.sql :as sql]
[next.jdbc :as jdbc])
(:import (java.util UUID)))
(defmethod dispatch :find-conversation-by-uid
[[_ db {:keys [uid]}]]
(with-open [conn (jdbc/get-connection db)]
(let [conn-opts (jdbc/with-options conn (:options db))
conversations (sql/find-by-keys conn-opts :conversation {:uid uid})]
(doall
(into []
(for [{:conversation/keys [conversation-id] :as conversation} conversations
:let [{:message/keys [created-at]}
(jdbc/execute-one! conn-opts ["SELECT created_at FROM message
WHERE conversation_id = ?
ORDER BY created_at DESC
LIMIT 1" conversation-id])
with
(jdbc/execute-one! conn-opts ["SELECT uid FROM conversation
WHERE uid != ? AND conversation_id = ?" uid conversation-id])
[{:account/keys [name picture]}] (sql/find-by-keys conn-opts :account with)]]
(assoc conversation
:conversation/updated-at created-at
:conversation/with-name name
:conversation/with-picture picture)))))))
(defmethod dispatch :start-conversation
[[_ db {:keys [to from] :as message}]]
(with-open [conn (jdbc/get-connection db)]
(let [conn-opts (jdbc/with-options conn (:options db))
conversing (jdbc/execute-one! conn-opts ["SELECT a.conversation_id
FROM conversation a
JOIN conversation b
ON a.conversation_id = b.conversation_id
WHERE a.uid = ? AND b.uid = ?" to from])]
(if-let [conversation-id (:conversation/conversation-id conversing)]
(do
(dispatch [:insert-message conn-opts (assoc message :conversation-id conversation-id)])
conversation-id)
(jdbc/with-transaction [tx conn]
(let [conversation-id (str (UUID/randomUUID))]
(sql/insert! tx :message (-> message
(assoc :uid from :conversation-id conversation-id)
(dissoc :from :to))
(:options db))
(sql/insert-multi! tx :conversation
[:notifications :uid :conversation_id]
[[1 to conversation-id]
[0 from conversation-id]])
conversation-id))))))
jacekschae/learn-reitit-course-files
(ns user
(:require [integrant.repl :as ig-repl]
[integrant.core :as ig]
[integrant.repl.state :as state]
[cheffy.server]
[next.jdbc :as jdbc]
[next.jdbc.sql :as sql]
[clj-http.client :as http]
[muuntaja.core :as m]))
(with-open [conn (jdbc/get-connection db)]
(let [conn-opts (jdbc/with-options conn (:options db))
conversations (sql/find-by-keys conn-opts :conversation {:uid "auth0|5fe31324a2914d006f574ceb"})]
(mapv #(assoc % :conversation/updated-at (:message/created_at
(jdbc/execute-one!
conn-opts
[(str "SELECT created_at"
" FROM message"
" WHERE conversation_id = ?"
" ORDER BY created_at DESC"
" LIMIT 1")
(:conversation/conversation_id %)])))
(jdbc/execute! conn-opts
[(str "SELECT c.*"
" , a.name AS with_name"
" , a.picture AS with_picture"
" FROM conversation c"
" JOIN conversation other"
" ON other.conversation_id = c.conversation_id"
" AND other.uid <> c.uid"
" JOIN account a"
" ON a.uid = other.uid"
" WHERE c.uid = ?")
"auth0|5fe31324a2914d006f574ceb"]))