PageRenderTime 52ms CodeModel.GetById 16ms RepoModel.GetById 0ms app.codeStats 0ms

/src/test/clojure/clojure/java/test_jdbc.clj

http://github.com/clojure/java.jdbc
Clojure | 1048 lines | 897 code | 108 blank | 43 comment | 233 complexity | bda637054fede81600e0bf99afeff157 MD5 | raw file
  1. ;; Copyright (c) Stephen C. Gilardi. All rights reserved. The use and
  2. ;; distribution terms for this software are covered by the Eclipse Public
  3. ;; License 1.0 (http://opensource.org/licenses/eclipse-1.0.php) which can
  4. ;; be found in the file epl-v10.html at the root of this distribution. By
  5. ;; using this software in any fashion, you are agreeing to be bound by the
  6. ;; terms of this license. You must not remove this notice, or any other,
  7. ;; from this software.
  8. ;;
  9. ;; test_jdbc.clj
  10. ;;
  11. ;; This namespace contains tests that exercise the JDBC portion of java.jdbc
  12. ;; so these tests expect databases to be available. Embedded databases can
  13. ;; be tested without external infrastructure (Apache Derby, HSQLDB). Other
  14. ;; databases will be available for testing in different environments. The
  15. ;; available databases for testing can be configured below.
  16. ;;
  17. ;; scgilardi (gmail)
  18. ;; Created 13 September 2008
  19. ;;
  20. ;; seancorfield (gmail)
  21. ;; Migrated from clojure.contrib.test-sql 17 April 2011
  22. (ns clojure.java.test-jdbc
  23. (:require [clojure.test :refer :all]
  24. [clojure.java.jdbc :as sql]
  25. [clojure.string :as str]))
  26. (try
  27. (require 'clojure.java.jdbc.spec)
  28. (require 'clojure.spec.test)
  29. (let [syms ((resolve 'clojure.spec.test/enumerate-namespace) 'clojure.java.jdbc)]
  30. ((resolve 'clojure.spec.test/instrument) syms))
  31. (println "Instrumenting clojure.java.jdbc with clojure.spec")
  32. (catch Exception _))
  33. ;; Set test-databases according to whether you have the local database available:
  34. ;; Possible values so far: [:mysql :postgres :derby :hsqldb :mysql-str :postgres-str]
  35. ;; Apache Derby and HSQLDB can run without an external setup.
  36. (def test-databases
  37. (if-let [dbs (System/getenv "TEST_DBS")]
  38. (map keyword (.split dbs ","))
  39. ;; enable more by default once the build server is equipped?
  40. [:derby :hsqldb :h2 :sqlite]))
  41. ;; MS SQL Server requires more specialized configuration:
  42. (def mssql-host (or (System/getenv "TEST_MSSQL_HOST") "127.0.0.1\\SQLEXPRESS"))
  43. (def mssql-port (or (System/getenv "TEST_MSSQL_PORT") "1433"))
  44. (def mssql-user (or (System/getenv "TEST_MSSQL_USER") "sa"))
  45. (def mssql-pass (or (System/getenv "TEST_MSSQL_PASS") ""))
  46. (def mssql-dbname (or (System/getenv "TEST_MSSQL_NAME") "clojure_test"))
  47. (def jtds-host (or (System/getenv "TEST_JTDS_HOST") mssql-host))
  48. (def jtds-port (or (System/getenv "TEST_JTDS_PORT") mssql-port))
  49. (def jtds-user (or (System/getenv "TEST_JTDS_USER") mssql-user))
  50. (def jtds-pass (or (System/getenv "TEST_JTDS_PASS") mssql-pass))
  51. (def jtds-dbname (or (System/getenv "TEST_JTDS_NAME") mssql-dbname))
  52. ;; PostgreSQL host/port
  53. (def postgres-host (or (System/getenv "TEST_POSTGRES_HOST") "127.0.0.1"))
  54. (def postgres-port (or (System/getenv "TEST_POSTGRES_PORT") "5432"))
  55. ;; database connections used for testing:
  56. (def mysql-db {:subprotocol "mysql"
  57. :subname "//127.0.0.1:3306/clojure_test"
  58. :user "clojure_test"
  59. :password "clojure_test"})
  60. (def derby-db {:dbtype "derby"
  61. :dbname "clojure_test_derby"
  62. :create true})
  63. (def hsqldb-db {:dbtype "hsqldb"
  64. :dbname "clojure_test_hsqldb"})
  65. (def h2-db {:dbtype "h2"
  66. :dbname "./clojure_test_h2"})
  67. (def sqlite-db {:dbtype "sqlite"
  68. :dbname "clojure_test_sqlite"})
  69. (def postgres-db {:subprotocol "postgresql"
  70. :subname (str "//" postgres-host ":" postgres-port "/clojure_test")
  71. :user "clojure_test"
  72. :password "clojure_test"})
  73. (def mssql-db {:subprotocol "sqlserver"
  74. :subname (str "//" mssql-host ":" mssql-port ";DATABASENAME=" mssql-dbname)
  75. :user mssql-user
  76. :password mssql-pass})
  77. (def jtds-db {:subprotocol "jtds:sqlserver"
  78. :subname (str "//" jtds-host ":" jtds-port "/" jtds-dbname)
  79. :user jtds-user
  80. :password jtds-pass})
  81. ;; To test against the stringified DB connection settings:
  82. (def mysql-str-db
  83. "mysql://clojure_test:clojure_test@localhost:3306/clojure_test")
  84. (def mysql-jdbc-str-db
  85. "jdbc:mysql://clojure_test:clojure_test@localhost:3306/clojure_test")
  86. (def postgres-str-db
  87. "postgres://clojure_test:clojure_test@localhost/clojure_test")
  88. (defn- test-specs
  89. "Return a sequence of db-spec maps that should be used for tests"
  90. []
  91. (for [db test-databases]
  92. @(ns-resolve 'clojure.java.test-jdbc (symbol (str (name db) "-db")))))
  93. (defn- clean-up
  94. "Attempt to drop any test tables before we start a test."
  95. [t]
  96. (doseq [db (test-specs)]
  97. (doseq [table [:fruit :fruit2 :veggies :veggies2]]
  98. (try
  99. (sql/db-do-commands db (sql/drop-table-ddl table))
  100. (catch java.sql.SQLException _
  101. ;; ignore
  102. ))))
  103. (t))
  104. (use-fixtures
  105. :each clean-up)
  106. ;; We start with all tables dropped and each test has to create the tables
  107. ;; necessary for it to do its job, and populate it as needed...
  108. (defn- derby? [db]
  109. (if (string? db)
  110. (re-find #"derby:" db)
  111. (= "derby" (or (:subprotocol db) (:dbtype db)))))
  112. (defn- hsqldb? [db]
  113. (if (string? db)
  114. (re-find #"hsqldb:" db)
  115. (= "hsqldb" (or (:subprotocol db) (:dbtype db)))))
  116. (defn- mssql? [db]
  117. (if (string? db)
  118. (re-find #"sqlserver" db)
  119. (re-find #"sqlserver" (or (:subprotocol db) (:dbtype db)))))
  120. (defn- mysql? [db]
  121. (if (string? db)
  122. (re-find #"mysql:" db)
  123. (= "mysql" (or (:subprotocol db) (:dbtype db)))))
  124. (defn- postgres? [db]
  125. (if (string? db)
  126. (re-find #"postgres" db)
  127. (= "postgresql" (or (:subprotocol db) (:dbtype db)))))
  128. (defmulti create-test-table
  129. "Create a standard test table. Uses db-do-commands.
  130. For MySQL, ensure table uses an engine that supports transactions!"
  131. (fn [table db]
  132. (cond
  133. (mysql? db) :mysql
  134. (postgres? db) :postgres
  135. :else :default)))
  136. (defmethod create-test-table :mysql
  137. [table db]
  138. (sql/db-do-commands
  139. db (sql/create-table-ddl
  140. table
  141. [[:id :int "PRIMARY KEY AUTO_INCREMENT"]
  142. [:name "VARCHAR(32)"]
  143. [:appearance "VARCHAR(32)"]
  144. [:cost :int]
  145. [:grade :real]]
  146. {:table-spec "ENGINE=InnoDB"})))
  147. (defmethod create-test-table :postgres
  148. [table db]
  149. (sql/db-do-commands
  150. db (sql/create-table-ddl
  151. table
  152. [[:id :serial "PRIMARY KEY"]
  153. [:name "VARCHAR(32)"]
  154. [:appearance "VARCHAR(32)"]
  155. [:cost :int]
  156. [:grade :real]]
  157. {:table-spec ""})))
  158. (defmethod create-test-table :default
  159. [table db]
  160. (sql/db-do-commands
  161. db (sql/create-table-ddl
  162. table
  163. [[:id :int "DEFAULT 0"]
  164. [:name "VARCHAR(32)" "PRIMARY KEY"]
  165. [:appearance "VARCHAR(32)"]
  166. [:cost :int]
  167. [:grade :real]]
  168. {:table-spec ""})))
  169. (deftest test-drop-table-ddl
  170. (is (= "DROP TABLE something" (sql/drop-table-ddl :something))))
  171. (deftest test-uri-spec-parsing
  172. (is (= {:advanced "false" :ssl "required" :password "clojure_test"
  173. :user "clojure_test" :subname "//localhost/clojure_test"
  174. :subprotocol "postgresql"}
  175. (@#'sql/parse-properties-uri
  176. (java.net.URI.
  177. (str "postgres://clojure_test:clojure_test@localhost/clojure_test?"
  178. "ssl=required&advanced=false")))))
  179. (is (= {:password "clojure_test" :user "clojure_test"
  180. :subname "//localhost:3306/clojure_test", :subprotocol "mysql"}
  181. (@#'sql/parse-properties-uri
  182. (java.net.URI.
  183. "mysql://clojure_test:clojure_test@localhost:3306/clojure_test")))))
  184. (defn- returned-key [db k]
  185. (condp = (or (:subprotocol db) (:dbtype db))
  186. "derby" {(keyword "1") nil}
  187. "hsqldb" nil
  188. "h2" nil
  189. "mysql" {:generated_key k}
  190. nil (if (mysql? db) ; string-based tests
  191. {:generated_key k}
  192. k)
  193. "jtds:sqlserver" {:id nil}
  194. "sqlserver" {:generated_keys nil}
  195. "sqlite" {(keyword "last_insert_rowid()") k}
  196. k))
  197. (defn- select-key [db]
  198. (condp = (or (:subprotocol db) (:dbtype db))
  199. "postgresql" :id
  200. identity))
  201. (defn- generated-key [db k]
  202. (condp = (or (:subprotocol db) (:dbtype db))
  203. "derby" 0
  204. "hsqldb" 0
  205. "h2" 0
  206. "jtds:sqlserver" 0
  207. "sqlserver" 0
  208. "sqlite" 0
  209. k))
  210. (defn- float-or-double [db v]
  211. (condp = (or (:subprotocol db) (:dbtype db))
  212. "derby" (Float. v)
  213. "h2" (Float. v)
  214. "jtds:sqlserver" (Float. v)
  215. "sqlserver" (Float. v)
  216. "postgresql" (Float. v)
  217. v))
  218. (deftest test-create-table
  219. (doseq [db (test-specs)]
  220. (create-test-table :fruit db)
  221. (is (= 0 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))))
  222. (deftest test-drop-table
  223. (doseq [db (test-specs)]
  224. (create-test-table :fruit2 db)
  225. (sql/db-do-commands db (sql/drop-table-ddl :fruit2))
  226. (is (thrown? java.sql.SQLException
  227. (sql/query db ["SELECT * FROM fruit2"] {:result-set-fn count})))))
  228. (deftest test-do-commands
  229. (doseq [db (test-specs)]
  230. (create-test-table :fruit2 db)
  231. (sql/db-do-commands db "DROP TABLE fruit2")
  232. (is (thrown? java.sql.SQLException
  233. (sql/query db ["SELECT * FROM fruit2"] {:result-set-fn count})))))
  234. (deftest test-do-commands-transaction
  235. (doseq [db (test-specs)]
  236. (create-test-table :fruit2 db)
  237. (sql/db-do-commands db true "DROP TABLE fruit2")
  238. (is (thrown? java.sql.SQLException
  239. (sql/query db ["SELECT * FROM fruit2"] {:result-set-fn count})))))
  240. (deftest test-do-commands-multi
  241. (doseq [db (test-specs)]
  242. (sql/db-do-commands db
  243. [(sql/create-table-ddl :fruit3
  244. [[:name "VARCHAR(32)"]
  245. [:appearance "VARCHAR(32)"]
  246. [:cost :int]])
  247. "DROP TABLE fruit3"])
  248. (is (thrown? java.sql.SQLException
  249. (sql/query db ["SELECT * FROM fruit2"] {:result-set-fn count})))))
  250. (deftest test-do-commands-multi-transaction
  251. (doseq [db (test-specs)]
  252. (sql/db-do-commands db true
  253. [(sql/create-table-ddl :fruit3
  254. [[:name "VARCHAR(32)"]
  255. [:appearance "VARCHAR(32)"]
  256. [:cost :int]])
  257. "DROP TABLE fruit3"])
  258. (is (thrown? java.sql.SQLException
  259. (sql/query db ["SELECT * FROM fruit2"] {:result-set-fn count})))))
  260. (deftest test-do-prepared1a
  261. (doseq [db (test-specs)]
  262. (create-test-table :fruit2 db)
  263. ;; single string is acceptable
  264. (sql/db-do-prepared db "INSERT INTO fruit2 ( name, appearance, cost, grade ) VALUES ( 'test', 'test', 1, 1.0 )")
  265. (is (= 1 (sql/query db ["SELECT * FROM fruit2"] {:result-set-fn count})))))
  266. (deftest test-do-prepared1b
  267. (doseq [db (test-specs)]
  268. (create-test-table :fruit2 db)
  269. (with-open [con (sql/get-connection db)]
  270. (let [stmt (sql/prepare-statement con "INSERT INTO fruit2 ( name, appearance, cost, grade ) VALUES ( 'test', 'test', 1, 1.0 )")]
  271. ;; single PreparedStatement is acceptable
  272. (is (= [1] (sql/db-do-prepared db stmt)))))
  273. (is (= 1 (sql/query db ["SELECT * FROM fruit2"] {:result-set-fn count})))))
  274. (deftest test-do-prepared1ci
  275. (doseq [db (test-specs)]
  276. (create-test-table :fruit2 db)
  277. (is (= (returned-key db 1)
  278. ((select-key db) (sql/db-do-prepared-return-keys db "INSERT INTO fruit2 ( name, appearance, cost, grade ) VALUES ( 'test', 'test', 1, 1.0 )"))))
  279. (is (= 1 (sql/query db ["SELECT * FROM fruit2"] {:result-set-fn count})))))
  280. (deftest test-do-prepared1cii
  281. (doseq [db (test-specs)]
  282. (create-test-table :fruit2 db)
  283. (is (= (returned-key db 1)
  284. ((select-key db) (sql/db-do-prepared-return-keys db ["INSERT INTO fruit2 ( name, appearance, cost, grade ) VALUES ( 'test', 'test', 1, 1.0 )"]))))
  285. (is (= 1 (sql/query db ["SELECT * FROM fruit2"] {:result-set-fn count})))))
  286. (deftest test-do-prepared1di
  287. (doseq [db (test-specs)]
  288. (create-test-table :fruit db)
  289. (with-open [con (sql/get-connection db)]
  290. (let [stmt (sql/prepare-statement con "INSERT INTO fruit ( name, appearance, cost, grade ) VALUES ( 'test', 'test', 1, 1.0 )"
  291. {:return-keys true})]
  292. (is (= (returned-key db 1)
  293. ((select-key db) (sql/db-do-prepared-return-keys db [stmt]))))))
  294. (is (= 1 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))))
  295. (deftest test-do-prepared1dii
  296. (doseq [db (test-specs)]
  297. (create-test-table :fruit db)
  298. (with-open [con (sql/get-connection db)]
  299. (let [stmt (sql/prepare-statement con "INSERT INTO fruit ( name, appearance, cost, grade ) VALUES ( 'test', 'test', 1, 1.0 )"
  300. {:return-keys true})]
  301. (is (= (returned-key db 1)
  302. ((select-key db) (sql/db-do-prepared-return-keys db stmt))))))
  303. (is (= 1 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))))
  304. (deftest test-do-prepared1e
  305. (doseq [db (test-specs)]
  306. ;; Derby/SQL Server does not have auto-generated id column which we're testing here
  307. (when-not (#{"derby" "jtds:sqlserver"} (or (:subprotocol db) (:dbtype db)))
  308. (create-test-table :fruit db)
  309. (with-open [con (sql/get-connection db)]
  310. (let [stmt (sql/prepare-statement con "INSERT INTO fruit ( name, appearance, cost, grade ) VALUES ( 'test', 'test', 1, 1.0 )"
  311. {:return-keys ["id"]})]
  312. (is (= (returned-key db 1) ((select-key db) (sql/db-do-prepared-return-keys db stmt))))))
  313. (is (= 1 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count}))))))
  314. (deftest test-do-prepared2
  315. (doseq [db (test-specs)]
  316. (create-test-table :fruit2 db)
  317. (sql/db-do-prepared db "DROP TABLE fruit2")
  318. (is (thrown? java.sql.SQLException
  319. (sql/query db ["SELECT * FROM fruit2"] {:result-set-fn count})))))
  320. (deftest test-do-prepared3a
  321. (doseq [db (test-specs)]
  322. (create-test-table :fruit2 db)
  323. (sql/db-do-prepared db ["INSERT INTO fruit2 ( name, appearance, cost, grade ) VALUES ( ?, ?, ?, ? )" ["test" "test" 1 1.0]] {:multi? true})
  324. (is (= 1 (sql/query db ["SELECT * FROM fruit2"] {:result-set-fn count})))))
  325. (deftest test-do-prepared3b
  326. (doseq [db (test-specs)]
  327. (create-test-table :fruit2 db)
  328. (sql/db-do-prepared db ["INSERT INTO fruit2 ( name, appearance, cost, grade ) VALUES ( ?, ?, ?, ? )" "test" "test" 1 1.0])
  329. (is (= 1 (sql/query db ["SELECT * FROM fruit2"] {:result-set-fn count})))))
  330. (deftest test-do-prepared4
  331. (doseq [db (test-specs)]
  332. (create-test-table :fruit2 db)
  333. (sql/db-do-prepared db ["INSERT INTO fruit2 ( name, appearance, cost, grade ) VALUES ( ?, ?, ?, ? )" ["test" "test" 1 1.0] ["two" "two" 2 2.0]] {:multi? true})
  334. (is (= 2 (sql/query db ["SELECT * FROM fruit2"] {:result-set-fn count})))))
  335. (deftest test-insert-rows
  336. (doseq [db (test-specs)]
  337. (create-test-table :fruit db)
  338. (let [r (sql/insert-multi! db
  339. :fruit
  340. nil
  341. [[1 "Apple" "red" 59 87]
  342. [2 "Banana" "yellow" 29 92.2]
  343. [3 "Peach" "fuzzy" 139 90.0]
  344. [4 "Orange" "juicy" 139 88.6]])]
  345. (is (= '(1 1 1 1) r)))
  346. (is (= 4 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))
  347. (is (= 4 (sql/with-db-connection [con db]
  348. (sql/query con (sql/prepare-statement (sql/db-connection con) "SELECT * FROM fruit") {:result-set-fn count}))))
  349. (is (= 2 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count :max-rows 2})))
  350. (is (= 2 (sql/with-db-connection [con db]
  351. (sql/query con [(sql/prepare-statement (sql/db-connection con) "SELECT * FROM fruit" {:max-rows 2})] {:result-set-fn count}))))
  352. (is (= "Apple" (sql/query db ["SELECT * FROM fruit WHERE appearance = ?" "red"] {:row-fn :name :result-set-fn first})))
  353. (is (= "juicy" (sql/query db ["SELECT * FROM fruit WHERE name = ?" "Orange"] {:row-fn :appearance :result-set-fn first})))
  354. (is (= "Apple" (:name (sql/get-by-id db :fruit 1))))
  355. (is (= ["Apple"] (map :name (sql/find-by-keys db :fruit {:appearance "red"}))))
  356. (is (= "Peach" (:name (sql/get-by-id db :fruit 3 :id))))
  357. (is (= ["Peach"] (map :name (sql/find-by-keys db :fruit {:id 3 :cost 139}))))
  358. (is (= ["Peach" "Orange"] (map :name (sql/find-by-keys db :fruit {:cost 139} {:order-by [:id]}))))
  359. (is (= ["Orange" "Peach"] (map :name (sql/find-by-keys db :fruit {:cost 139} {:order-by [{:appearance :desc}]}))))))
  360. (deftest test-insert-values
  361. (doseq [db (test-specs)]
  362. (create-test-table :fruit db)
  363. (let [r (sql/insert-multi! db
  364. :fruit
  365. [:name :cost]
  366. [["Mango" 722]
  367. ["Feijoa" 441]])]
  368. (is (= '(1 1) r)))
  369. (is (= 2 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))
  370. (is (= "Mango" (sql/query db ["SELECT * FROM fruit WHERE cost = ?" 722] {:row-fn :name :result-set-fn first})))))
  371. (deftest test-insert-records
  372. (doseq [db (test-specs)]
  373. (create-test-table :fruit db)
  374. (let [r (map (select-key db) (sql/insert-multi! db
  375. :fruit
  376. [{:name "Pomegranate" :appearance "fresh" :cost 585}
  377. {:name "Kiwifruit" :grade 93}]))]
  378. (is (= (list (returned-key db 1) (returned-key db 2)) r)))
  379. (is (= 2 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))
  380. (is (= "Pomegranate" (sql/query db ["SELECT * FROM fruit WHERE cost = ?" 585] {:row-fn :name :result-set-fn first})))))
  381. (deftest test-insert-via-execute
  382. (doseq [db (test-specs)]
  383. (create-test-table :fruit db)
  384. (sql/execute! db [(str "INSERT INTO fruit ( name, appearance, cost ) "
  385. "VALUES ( ?, ?, ? )")
  386. "Apple" "Green" 75])
  387. (sql/execute! db [(str "INSERT INTO fruit ( name, appearance, cost ) "
  388. "VALUES ( 'Pear', 'Yellow', 99 )")])
  389. (is (= 2 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))
  390. (is (= "Pear" (sql/query db ["SELECT * FROM fruit WHERE cost = ?" 99]
  391. {:row-fn :name :result-set-fn first})))))
  392. (deftest execute-with-prepared-statement
  393. (doseq [db (test-specs)]
  394. (create-test-table :fruit db)
  395. (sql/with-db-connection [conn db]
  396. (let [connection (:connection conn)
  397. prepared-statement (sql/prepare-statement connection (str "INSERT INTO fruit ( name, appearance, cost ) "
  398. "VALUES ( ?, ?, ? )"))]
  399. (sql/execute! db [prepared-statement "Apple" "Green" 75])
  400. (sql/execute! db [prepared-statement "Pear" "Yellow" 99])))
  401. (is (= 2 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))
  402. (is (= "Pear" (sql/query db ["SELECT * FROM fruit WHERE cost = ?" 99]
  403. {:row-fn :name :result-set-fn first})))))
  404. (deftest execute-with-prepared-statement-return-keys
  405. (doseq [db (test-specs)]
  406. ;; Derby/SQL Server does not have auto-generated id column which we're testing here
  407. (when-not (#{"derby" "jtds:sqlserver"} (or (:subprotocol db) (:dbtype db)))
  408. (create-test-table :fruit db)
  409. (sql/with-db-connection [conn db]
  410. (let [connection (:connection conn)
  411. prepared-statement (sql/prepare-statement connection (str "INSERT INTO fruit ( name, appearance, cost ) "
  412. "VALUES ( ?, ?, ? )")
  413. {:return-keys ["id"]})]
  414. ;; what is returned is affected row counts due to how execute! works
  415. (is (= [1] (sql/execute! db [prepared-statement "Apple" "Green" 75])))
  416. (is (= [1] (sql/execute! db [prepared-statement "Pear" "Yellow" 99])))))
  417. (is (= 2 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))
  418. (is (= "Pear" (sql/query db ["SELECT * FROM fruit WHERE cost = ?" 99]
  419. {:row-fn :name :result-set-fn first}))))))
  420. (deftest test-update-values
  421. (doseq [db (test-specs)]
  422. (create-test-table :fruit db)
  423. (let [r (sql/insert-multi! db
  424. :fruit
  425. nil
  426. [[1 "Apple" "red" 59 87]
  427. [2 "Banana" "yellow" 29 92.2]
  428. [3 "Peach" "fuzzy" 139 90.0]
  429. [4 "Orange" "juicy" 89 88.6]])]
  430. (is (= '(1 1 1 1) r)))
  431. (sql/update! db
  432. :fruit
  433. {:appearance "bruised" :cost 14}
  434. ["name=?" "Banana"])
  435. (is (= 4 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))
  436. (is (= "Apple" (sql/query db ["SELECT * FROM fruit WHERE appearance = ?" "red"]
  437. {:row-fn :name :result-set-fn first})))
  438. (is (= "Banana" (sql/query db ["SELECT * FROM fruit WHERE appearance = ?" "bruised"]
  439. {:row-fn :name :result-set-fn first})))
  440. (is (= 14 (sql/query db ["SELECT * FROM fruit WHERE name = ?" "Banana"]
  441. {:row-fn :cost :result-set-fn first})))))
  442. (defn update-or-insert-values
  443. [db table row where]
  444. (sql/with-db-transaction [t-conn db]
  445. (let [result (sql/update! t-conn table row where)]
  446. (if (zero? (first result))
  447. (sql/insert! t-conn table row)
  448. result))))
  449. (defn update-or-insert-values-with-isolation
  450. [db table row where]
  451. (sql/with-db-transaction [t-conn db {:isolation :read-uncommitted}]
  452. (let [result (sql/update! t-conn table row where)]
  453. (if (zero? (first result))
  454. (sql/insert! t-conn table row)
  455. result))))
  456. (deftest test-update-or-insert-values
  457. (doseq [db (test-specs)]
  458. (create-test-table :fruit db)
  459. (update-or-insert-values db
  460. :fruit
  461. {:name "Pomegranate" :appearance "fresh" :cost 585}
  462. ["name=?" "Pomegranate"])
  463. (is (= 1 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))
  464. (is (= 585 (sql/query db ["SELECT * FROM fruit WHERE appearance = ?" "fresh"]
  465. {:row-fn :cost :result-set-fn first})))
  466. (update-or-insert-values db
  467. :fruit
  468. {:name "Pomegranate" :appearance "ripe" :cost 565}
  469. ["name=?" "Pomegranate"])
  470. (is (= 1 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))
  471. (is (= 565 (sql/query db ["SELECT * FROM fruit WHERE appearance = ?" "ripe"]
  472. {:row-fn :cost :result-set-fn first})))
  473. (update-or-insert-values db
  474. :fruit
  475. {:name "Apple" :appearance "green" :cost 74}
  476. ["name=?" "Apple"])
  477. (is (= 2 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))))
  478. (deftest test-update-or-insert-values-with-isolation
  479. (doseq [db (test-specs)]
  480. (create-test-table :fruit db)
  481. (update-or-insert-values-with-isolation db
  482. :fruit
  483. {:name "Pomegranate" :appearance "fresh" :cost 585}
  484. ["name=?" "Pomegranate"])
  485. (is (= 1 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))
  486. (is (= 585 (sql/query db ["SELECT * FROM fruit WHERE appearance = ?" "fresh"]
  487. {:row-fn :cost :result-set-fn first})))
  488. (update-or-insert-values db
  489. :fruit
  490. {:name "Pomegranate" :appearance "ripe" :cost 565}
  491. ["name=?" "Pomegranate"])
  492. (is (= 1 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))
  493. (is (= 565 (sql/query db ["SELECT * FROM fruit WHERE appearance = ?" "ripe"]
  494. {:row-fn :cost :result-set-fn first})))
  495. (update-or-insert-values db
  496. :fruit
  497. {:name "Apple" :appearance "green" :cost 74}
  498. ["name=?" "Apple"])
  499. (is (= 2 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))))
  500. (defn- file-not-found-exception-via-reflection
  501. "In Clojure 1.3.0 this caused a wrapped exception and we introduced throw-non-rte
  502. to workaround that. This was fixed in 1.4.0 but we never removed the workaround.
  503. Added this hack from the mailing list specifically to test the exception handling
  504. so that we can verify only Clojure 1.3.0 fails the tests and drop support for it."
  505. [f]
  506. (java.io.FileReader. f))
  507. (deftest test-partial-exception
  508. (doseq [db (test-specs)]
  509. (create-test-table :fruit db)
  510. (try
  511. (sql/with-db-transaction [t-conn db]
  512. (sql/insert-multi! t-conn
  513. :fruit
  514. [:name :appearance]
  515. [["Grape" "yummy"]
  516. ["Pear" "bruised"]])
  517. (is (= 2 (sql/query t-conn ["SELECT * FROM fruit"] {:result-set-fn count})))
  518. (file-not-found-exception-via-reflection "/etc/password_no_such_file"))
  519. (catch java.io.FileNotFoundException _
  520. (is (= 0 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count}))))
  521. (catch Exception _
  522. (is false "Unexpected exception encountered (not wrapped?).")))))
  523. (deftest test-partial-exception-with-isolation
  524. (doseq [db (test-specs)]
  525. (create-test-table :fruit db)
  526. (try
  527. (sql/with-db-transaction [t-conn db {:isolation :serializable}]
  528. (sql/insert-multi! t-conn
  529. :fruit
  530. [:name :appearance]
  531. [["Grape" "yummy"]
  532. ["Pear" "bruised"]])
  533. (is (= 2 (sql/query t-conn ["SELECT * FROM fruit"] {:result-set-fn count})))
  534. (throw (Exception. "deliberate exception")))
  535. (catch Exception _
  536. (is (= 0 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))))))
  537. (deftest test-sql-exception
  538. (doseq [db (test-specs)]
  539. (create-test-table :fruit db)
  540. (try
  541. (sql/with-db-transaction [t-conn db]
  542. (sql/insert! t-conn
  543. :fruit
  544. [:name :appearance]
  545. ["Apple" "strange" "whoops"]))
  546. (catch IllegalArgumentException _
  547. (is (= 0 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))))
  548. (is (= 0 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))))
  549. (deftest test-sql-exception-with-isolation
  550. (doseq [db (test-specs)]
  551. (create-test-table :fruit db)
  552. (try
  553. (sql/with-db-transaction [t-conn db {:isolation :read-uncommitted}]
  554. (sql/insert! t-conn
  555. :fruit
  556. [:name :appearance]
  557. ["Apple" "strange" "whoops"]))
  558. (catch IllegalArgumentException _
  559. (is (= 0 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))))
  560. (is (= 0 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))))
  561. (deftest test-insert-values-exception
  562. (doseq [db (test-specs)]
  563. (create-test-table :fruit db)
  564. (is (thrown? IllegalArgumentException
  565. (sql/with-db-transaction [t-conn db]
  566. (sql/insert-multi! t-conn
  567. :fruit
  568. [:name :appearance]
  569. [["Grape" "yummy"]
  570. ["Pear" "bruised"]
  571. ["Apple" "strange" "whoops"]]))))
  572. (is (= 0 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))))
  573. (deftest test-insert-values-exception-with-isolation
  574. (doseq [db (test-specs)]
  575. (create-test-table :fruit db)
  576. (is (thrown? IllegalArgumentException
  577. (sql/with-db-transaction [t-conn db {:isolation :read-uncommitted}]
  578. (sql/insert-multi! t-conn
  579. :fruit
  580. [:name :appearance]
  581. [["Grape" "yummy"]
  582. ["Pear" "bruised"]
  583. ["Apple" "strange" "whoops"]]))))
  584. (is (= 0 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))))
  585. (deftest test-rollback
  586. (doseq [db (test-specs)]
  587. (create-test-table :fruit db)
  588. (try
  589. (sql/with-db-transaction [t-conn db]
  590. (is (not (sql/db-is-rollback-only t-conn)))
  591. (sql/db-set-rollback-only! t-conn)
  592. (is (sql/db-is-rollback-only t-conn))
  593. (sql/insert-multi! t-conn
  594. :fruit
  595. [:name :appearance]
  596. [["Grape" "yummy"]
  597. ["Pear" "bruised"]
  598. ["Apple" "strange"]])
  599. (is (= 3 (sql/query t-conn ["SELECT * FROM fruit"] {:result-set-fn count}))))
  600. (catch java.sql.SQLException _
  601. (is (= 0 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))))
  602. (is (= 0 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))))
  603. (deftest test-rollback-with-isolation
  604. (doseq [db (test-specs)]
  605. (create-test-table :fruit db)
  606. (try
  607. (sql/with-db-transaction [t-conn db {:isolation :read-uncommitted}]
  608. (is (not (sql/db-is-rollback-only t-conn)))
  609. (sql/db-set-rollback-only! t-conn)
  610. (is (sql/db-is-rollback-only t-conn))
  611. (sql/insert-multi! t-conn
  612. :fruit
  613. [:name :appearance]
  614. [["Grape" "yummy"]
  615. ["Pear" "bruised"]
  616. ["Apple" "strange"]])
  617. (is (= 3 (sql/query t-conn ["SELECT * FROM fruit"] {:result-set-fn count}))))
  618. (catch java.sql.SQLException _
  619. (is (= 0 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))))
  620. (is (= 0 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))))
  621. (deftest test-transactions-with-possible-generated-keys-result-set
  622. (doseq [db (test-specs)]
  623. (create-test-table :fruit db)
  624. (sql/with-db-transaction [t-conn db]
  625. (sql/db-set-rollback-only! t-conn)
  626. (sql/insert! t-conn
  627. :fruit
  628. [:name :appearance]
  629. ["Grape" "yummy"])
  630. (is (= 1 (sql/query t-conn ["SELECT * FROM fruit"] {:result-set-fn count}))))
  631. (is (= 0 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))))
  632. (deftest test-transactions-with-possible-generated-keys-result-set-and-isolation
  633. (doseq [db (test-specs)]
  634. (create-test-table :fruit db)
  635. (sql/with-db-transaction [t-conn db {:isolation :read-uncommitted}]
  636. (sql/db-set-rollback-only! t-conn)
  637. (sql/insert! t-conn
  638. :fruit
  639. [:name :appearance]
  640. ["Grape" "yummy"])
  641. (is (= 1 (sql/query t-conn ["SELECT * FROM fruit"] {:result-set-fn count}))))
  642. (is (= 0 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))))
  643. (deftest test-nested-transactions-check-transaction-isolation-level
  644. (doseq [db (test-specs)]
  645. (create-test-table :fruit db)
  646. (sql/with-db-transaction [t-conn db {:isolation :read-uncommitted}]
  647. (is (thrown? IllegalStateException
  648. (sql/with-db-transaction [t-conn' t-conn {:isolation :serializable}]
  649. (sql/insert! t-conn'
  650. :fruit
  651. [:name :appearance]
  652. ["Grape" "yummy"])))))
  653. (is (= 0 (sql/query db ["SELECT * FROM fruit"] {:result-set-fn count})))))
  654. (deftest test-raw-metadata
  655. (doseq [db (test-specs)]
  656. (create-test-table :fruit db)
  657. (let [table-info (with-open [conn (sql/get-connection db)]
  658. (into []
  659. (sql/result-set-seq
  660. (-> conn
  661. (.getMetaData)
  662. (.getTables nil nil nil
  663. (into-array ["TABLE" "VIEW"]))))))]
  664. (is (not= [] table-info))
  665. (is (= "fruit" (-> table-info
  666. first
  667. :table_name
  668. clojure.string/lower-case))))))
  669. (deftest test-metadata-managed
  670. (doseq [db (test-specs)]
  671. (create-test-table :fruit db)
  672. (sql/with-db-metadata [metadata db]
  673. (let [table-info (sql/metadata-query (.getTables metadata
  674. nil nil nil
  675. (into-array ["TABLE" "VIEW"])))]
  676. (is (not= [] table-info))
  677. (is (= "fruit" (-> table-info
  678. first
  679. :table_name
  680. clojure.string/lower-case)))))))
  681. (deftest test-metadata-managed-computed
  682. (doseq [db (test-specs)]
  683. (create-test-table :fruit db)
  684. (is (= "fruit"
  685. (sql/with-db-metadata [metadata db]
  686. (sql/metadata-query (.getTables metadata
  687. nil nil nil
  688. (into-array ["TABLE" "VIEW"]))
  689. {:row-fn (comp clojure.string/lower-case :table_name)
  690. :result-set-fn first}))))))
  691. (deftest test-metadata
  692. (doseq [db (test-specs)]
  693. (create-test-table :fruit db)
  694. (sql/with-db-metadata [metadata db]
  695. ;; make sure to close the ResultSet
  696. (with-open [table-info-result (.getTables metadata
  697. nil nil nil
  698. (into-array ["TABLE" "VIEW"]))]
  699. (let [table-info (sql/metadata-result table-info-result)]
  700. (is (not= [] table-info))
  701. (is (= "fruit" (-> table-info
  702. first
  703. :table_name
  704. clojure.string/lower-case))))))))
  705. (deftest empty-query
  706. (doseq [db (test-specs)]
  707. (create-test-table :fruit db)
  708. (is (= [] (sql/query db ["SELECT * FROM fruit"])))))
  709. (deftest query-with-string
  710. (doseq [db (test-specs)]
  711. (create-test-table :fruit db)
  712. (is (= [] (sql/query db "SELECT * FROM fruit")))))
  713. (deftest insert-one-row
  714. (doseq [db (test-specs)]
  715. (create-test-table :fruit db)
  716. (let [new-keys (map (select-key db) (sql/insert! db :fruit {:name "Apple"}))]
  717. (is (= [(returned-key db 1)] new-keys)))))
  718. (deftest insert-one-row-opts
  719. (doseq [db (test-specs)]
  720. (create-test-table :fruit db)
  721. (let [new-keys (map (select-key db) (sql/insert! db :fruit {:name "Apple"} {}))]
  722. (is (= [(returned-key db 1)] new-keys)))))
  723. (deftest insert-one-col-val
  724. (doseq [db (test-specs)]
  725. (create-test-table :fruit db)
  726. (let [new-keys (sql/insert! db :fruit [:name] ["Apple"])]
  727. (is (= [1] new-keys)))))
  728. (deftest insert-one-col-val-opts
  729. (doseq [db (test-specs)]
  730. (create-test-table :fruit db)
  731. (let [new-keys (sql/insert! db :fruit [:name] ["Apple"] {})]
  732. (is (= [1] new-keys)))))
  733. (deftest insert-query
  734. (doseq [db (test-specs)]
  735. (create-test-table :fruit db)
  736. (let [new-keys (map (select-key db) (sql/insert! db :fruit {:name "Apple"}))]
  737. (is (= [(returned-key db 1)] new-keys))
  738. (is (= [{:id (generated-key db 1) :name "Apple" :appearance nil :grade nil :cost nil}]
  739. (sql/query db "SELECT * FROM fruit")))
  740. (is (= [{:id (generated-key db 1) :name "Apple" :appearance nil :grade nil :cost nil}]
  741. (sql/query db ["SELECT * FROM fruit"])))
  742. (is (= [{:ID (generated-key db 1) :NAME "Apple" :APPEARANCE nil :GRADE nil :COST nil}]
  743. (sql/query db ["SELECT * FROM fruit"] {:identifiers str/upper-case})))
  744. (is (= [{:ID (generated-key db 1) :NAME "Apple" :APPEARANCE nil :GRADE nil :COST nil}]
  745. (sql/query (assoc db :identifiers str/upper-case) ["SELECT * FROM fruit"])))
  746. (is (= [{:fruit/id (generated-key db 1) :fruit/name "Apple" :fruit/appearance nil
  747. :fruit/grade nil :fruit/cost nil}]
  748. (sql/query db ["SELECT * FROM fruit"] {:qualifier "fruit"})))
  749. (is (= [{:fruit/id (generated-key db 1) :fruit/name "Apple" :fruit/appearance nil
  750. :fruit/grade nil :fruit/cost nil}]
  751. (sql/query (assoc db :qualifier "fruit") ["SELECT * FROM fruit"])))
  752. (is (= [{:id (generated-key db 1) :name "Apple" :appearance nil :grade nil :cost nil}]
  753. (with-open [con (sql/get-connection db)]
  754. (sql/query db [(sql/prepare-statement con "SELECT * FROM fruit")]))))
  755. (is (= [{:id (generated-key db 1) :name "Apple" :appearance nil :grade nil :cost nil}]
  756. (sql/query db ["SELECT * FROM fruit"] {:max-rows 1})))
  757. (cond (derby? db) nil
  758. (hsqldb? db) (is (seq (with-out-str
  759. (sql/query db ["SELECT * FROM fruit"]
  760. {:explain? "EXPLAIN PLAN FOR"}))))
  761. (mssql? db) nil
  762. :else (is (seq (with-out-str
  763. (sql/query db ["SELECT * FROM fruit"]
  764. {:explain? true}))))))))
  765. (deftest insert-two-by-map-and-query
  766. (doseq [db (test-specs)]
  767. (create-test-table :fruit db)
  768. (let [new-keys (map (select-key db) (sql/insert-multi! db :fruit [{:name "Apple"} {:name "Pear"}]))
  769. rows (sql/query db ["SELECT * FROM fruit ORDER BY name"])]
  770. (is (= [(returned-key db 1) (returned-key db 2)] new-keys))
  771. (is (= [{:id (generated-key db 1) :name "Apple" :appearance nil :grade nil :cost nil}
  772. {:id (generated-key db 2) :name "Pear" :appearance nil :grade nil :cost nil}] rows)))))
  773. (deftest insert-identifiers-respected-1
  774. (doseq [db (filter postgres? (test-specs))]
  775. (create-test-table :fruit db)
  776. (let [inserted (sql/insert! db
  777. :fruit
  778. {:name "Apple"}
  779. {:identifiers clojure.string/upper-case
  780. :qualifier "foo"})
  781. rows (sql/query db ["SELECT * FROM fruit ORDER BY name"]
  782. {:identifiers clojure.string/upper-case
  783. :qualifier "foo"})]
  784. (is (= rows inserted)))))
  785. (deftest insert-identifiers-respected-2
  786. (doseq [db (filter postgres? (test-specs))]
  787. (create-test-table :fruit db)
  788. (let [inserted (sql/insert-multi! db
  789. :fruit
  790. [{:name "Apple"} {:name "Pear"}]
  791. {:identifiers clojure.string/upper-case})
  792. rows (sql/query db ["SELECT * FROM fruit ORDER BY name"]
  793. {:identifiers clojure.string/upper-case})]
  794. (is (= rows inserted)))))
  795. (deftest insert-two-by-map-and-query-as-arrays
  796. (doseq [db (test-specs)]
  797. (create-test-table :fruit db)
  798. (let [new-keys (map (select-key db) (sql/insert-multi! db :fruit [{:name "Apple"} {:name "Pear"}]))
  799. rows (sql/query db ["SELECT * FROM fruit ORDER BY name"]
  800. {:as-arrays? :cols-as-is})]
  801. (is (= [(returned-key db 1) (returned-key db 2)] new-keys))
  802. (is (= [[:id :name :appearance :cost :grade]
  803. [(generated-key db 1) "Apple" nil nil nil]
  804. [(generated-key db 2) "Pear" nil nil nil]] rows)))))
  805. (deftest insert-two-by-cols-and-query
  806. (doseq [db (test-specs)]
  807. (create-test-table :fruit db)
  808. (let [update-counts (sql/insert-multi! db :fruit [:name] [["Apple"] ["Pear"]])
  809. rows (sql/query db ["SELECT * FROM fruit ORDER BY name"])]
  810. (is (= [1 1] update-counts))
  811. (is (= [{:id (generated-key db 1) :name "Apple" :appearance nil :grade nil :cost nil}
  812. {:id (generated-key db 2) :name "Pear" :appearance nil :grade nil :cost nil}] rows)))))
  813. (deftest insert-update-and-query
  814. (doseq [db (test-specs)]
  815. (create-test-table :fruit db)
  816. (let [new-keys (map (select-key db) (sql/insert! db :fruit {:name "Apple"}))
  817. update-result (sql/update! db :fruit {:cost 12 :grade 1.2 :appearance "Green"}
  818. ["id = ?" (generated-key db 1)])
  819. rows (sql/query db ["SELECT * FROM fruit"])]
  820. (is (= [(returned-key db 1)] new-keys))
  821. (is (= [1] update-result))
  822. (is (= [{:id (generated-key db 1)
  823. :name "Apple" :appearance "Green"
  824. :grade (float-or-double db 1.2)
  825. :cost 12}] rows)))))
  826. (deftest insert-delete-and-query
  827. (doseq [db (test-specs)]
  828. (create-test-table :fruit db)
  829. (let [new-keys (map (select-key db) (sql/insert! db :fruit {:name "Apple"}))
  830. delete-result (sql/delete! db :fruit
  831. ["id = ?" (generated-key db 1)])
  832. rows (sql/query db ["SELECT * FROM fruit"])]
  833. (is (= [(returned-key db 1)] new-keys))
  834. (is (= [1] delete-result))
  835. (is (= [] rows)))))
  836. (deftest insert-delete-and-query-in-connection
  837. (doseq [db (test-specs)]
  838. (sql/with-db-connection [con-db db]
  839. (create-test-table :fruit con-db)
  840. (let [new-keys (map (select-key db) (sql/insert! con-db :fruit {:name "Apple"}))
  841. delete-result (sql/delete! con-db :fruit
  842. ["id = ?" (generated-key con-db 1)])
  843. rows (sql/query con-db ["SELECT * FROM fruit"])]
  844. (is (= [(returned-key con-db 1)] new-keys))
  845. (is (= [1] delete-result))
  846. (is (= [] rows))))))
  847. (deftest illegal-insert-arguments
  848. (doseq [db (test-specs)]
  849. (is (thrown? IllegalArgumentException (sql/insert! db)))
  850. (is (thrown? IllegalArgumentException (sql/insert! db {:name "Apple"} [:name])))
  851. (is (thrown? IllegalArgumentException (sql/insert! db {:name "Apple"} [:name] {:entities identity})))
  852. (is (thrown? IllegalArgumentException (sql/insert! db [:name])))
  853. (is (thrown? ClassCastException (sql/insert! db [:name] {:entities identity})))))
  854. (deftest test-execute!-fails-with-multi-param-groups
  855. (doseq [db (test-specs)]
  856. (create-test-table :fruit db)
  857. ;; RuntimeException -> SQLException -> ArrayIndexOutOfBoundsException
  858. (is (thrown? Exception
  859. (sql/execute!
  860. db
  861. ["INSERT INTO fruit (name,appearance) VALUES (?,?)"
  862. ["Apple" "rosy"]
  863. ["Pear" "yellow"]
  864. ["Orange" "round"]])))))
  865. (deftest test-execute!-with-multi?-true-param-groups
  866. (doseq [db (test-specs)]
  867. (create-test-table :fruit db)
  868. ;; RuntimeException -> SQLException -> ArrayIndexOutOfBoundsException
  869. (let [counts (sql/execute!
  870. db
  871. ["INSERT INTO fruit (name,appearance) VALUES (?,?)"
  872. ["Apple" "rosy"]
  873. ["Pear" "yellow"]
  874. ["Orange" "round"]]
  875. {:multi? true})
  876. rows (sql/query db ["SELECT * FROM fruit ORDER BY name"])]
  877. (is (= [1 1 1] counts))
  878. (is (= [{:id (generated-key db 1) :name "Apple" :appearance "rosy" :cost nil :grade nil}
  879. {:id (generated-key db 3) :name "Orange" :appearance "round" :cost nil :grade nil}
  880. {:id (generated-key db 2) :name "Pear" :appearance "yellow" :cost nil :grade nil}] rows)))))
  881. (deftest test-resultset-read-column
  882. (extend-protocol sql/IResultSetReadColumn
  883. String
  884. (result-set-read-column [s _ _] ::FOO))
  885. (try
  886. (doseq [db (test-specs)]
  887. (create-test-table :fruit db)
  888. (sql/insert-multi! db
  889. :fruit
  890. [:name :cost :grade]
  891. [["Crepes" 12 87.7]
  892. ["Vegetables" -88 nil]
  893. ["Teenage Mutant Ninja Turtles" 0 100.0]])
  894. (is (= {:name ::FOO, :cost -88, :grade nil}
  895. (sql/query db ["SELECT name, cost, grade FROM fruit WHERE name = ?"
  896. "Vegetables"]
  897. {:result-set-fn first}))))
  898. ;; somewhat "undo" the first extension
  899. (finally
  900. (extend-protocol sql/IResultSetReadColumn
  901. String
  902. (result-set-read-column [s _ _] s)))))
  903. (deftest test-sql-value
  904. (extend-protocol sql/ISQLValue
  905. clojure.lang.Keyword
  906. (sql-value [_] "KW"))
  907. (doseq [db (test-specs)]
  908. (create-test-table :fruit db)
  909. (sql/insert! db
  910. :fruit
  911. [:name :cost :grade]
  912. [:test 12 nil])
  913. (is (= {:name "KW", :cost 12, :grade nil}
  914. (sql/query db ["SELECT name, cost, grade FROM fruit"]
  915. {:result-set-fn first}))))
  916. ;; somewhat "undo" the first extension
  917. (extend-protocol sql/ISQLValue
  918. clojure.lang.Keyword
  919. (sql-value [k] k)))
  920. (deftest test-sql-parameter
  921. (extend-protocol sql/ISQLParameter
  922. clojure.lang.Keyword
  923. (set-parameter [v ^java.sql.PreparedStatement s ^long i]
  924. (if (= :twelve v)
  925. (.setLong s i 12)
  926. (.setString s i (str (name v) i)))))
  927. (doseq [db (test-specs)]
  928. (create-test-table :fruit db)
  929. (sql/insert! db
  930. :fruit
  931. [:name :cost :grade]
  932. [:test :twelve nil])
  933. (is (= {:name "test1", :cost 12, :grade nil}
  934. (sql/query db ["SELECT name, cost, grade FROM fruit"]
  935. {:result-set-fn first}))))
  936. ;; somewhat "undo" the first extension
  937. (extend-protocol sql/ISQLParameter
  938. clojure.lang.Keyword
  939. (set-parameter [v ^java.sql.PreparedStatement s ^long i]
  940. (.setObject s i (sql/sql-value v)))))