get-datasource (clj)

(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 connection-tests
  (testing "datasource via jdbcUrl"
    (when-not (postgres?)
      (let [[url etc] (#'c/spec->url+etc (db))
            ds (jdbc/get-datasource (assoc etc :jdbcUrl url))]
        (cond (derby?) (is (= {:create true} etc))
              (mssql?) (is (= (cond-> #{:user :password}
                                (not (jtds?))
                                (conj :encrypt :trustServerCertificate))
                              (set (keys etc))))
              (mysql?) (is (= #{:user :password :useSSL :allowMultiQueries}
                              (disj (set (keys etc)) :disableMariaDbDriver)))
              :else    (is (= {} etc)))
        (is (instance? javax.sql.DataSource ds))
        (is (str/index-of (pr-str ds) (str "jdbc:"
                                           (cond (jtds?)
                                                 "jtds:sqlserver"
                                                 (mssql?)
                                                 "sqlserver"
                                                 :else
                                                 (:dbtype (db))))))
        ;; checks get-datasource on a DataSource is identity
        (is (identical? ds (jdbc/get-datasource ds)))
        (with-open [con (jdbc/get-connection ds {})]
          (is (instance? java.sql.Connection con)))))))

  At some point, the datasource/connection tests should probably be extended
  to accept EDN specs from an external source (environment variables?)."
  (:require [clojure.string :as str]
            [clojure.test :refer [deftest is testing use-fixtures]]
            [next.jdbc.connection :as c]
            [next.jdbc.protocols :as p]
            [next.jdbc.specs :as specs]
            [next.jdbc.test-fixtures :refer [with-test-db db]]))

(deftest test-uri-strings
  (testing "datasource via String"
    (let [db-spec (db)
          db-spec (if (= "embedded-postgres" (:dbtype db-spec))
                    (assoc db-spec :dbtype "postgresql")
                    db-spec)
          [url etc] (#'c/spec->url+etc db-spec)
          {:keys [user password]} etc
          etc (dissoc etc :user :password)
          uri (-> url
                  ;; strip jdbc: prefix for fun
                  (str/replace #"^jdbc:" "")
                  (str/replace #";" "?") ; for SQL Server tests
                  (str/replace #":sqlserver" "") ; for SQL Server tests
                  (cond-> (and user password)
                    (str/replace #"://" (str "://" user ":" password "@"))))
          ds (p/get-datasource (assoc etc :jdbcUrl uri))]
      (when (and user password)
        (with-open [con (p/get-connection ds {})]
          (is (instance? java.sql.Connection con)))))))

(ns next.jdbc.test-fixtures
  "Multi-database testing fixtures."
  (:require [clojure.string :as str]
            [next.jdbc :as jdbc]
            [next.jdbc.prepare :as prep]
            [next.jdbc.sql :as sql])
  (:import (io.zonky.test.db.postgres.embedded EmbeddedPostgres)))

  Tests can reach into here and call ds (above) to get a DataSource for use
  in test functions (that operate inside this fixture)."
  [t]
  (doseq [db test-db-specs]
    (reset! test-db-spec db)
    (if (= "embedded-postgres" (:dbtype db))
      (reset! test-datasource
              (.getPostgresDatabase ^EmbeddedPostgres @embedded-pg))
      (reset! test-datasource (jdbc/get-datasource db)))
    (let [fruit (if (mysql?) "fruit" "FRUIT") ; MySQL is case sensitive!
          btest (if (mysql?) "btest" "BTEST")
          auto-inc-pk
          (cond (or (derby?) (hsqldb?))
                (str "GENERATED ALWAYS AS IDENTITY"
                     " (START WITH 1, INCREMENT BY 1)"
                     " PRIMARY KEY")
                (postgres?)
                (str "GENERATED ALWAYS AS IDENTITY"
                     " PRIMARY KEY")
                (mssql?)
                "IDENTITY PRIMARY KEY"
                (sqlite?)
                "PRIMARY KEY AUTOINCREMENT"
                :else
                "AUTO_INCREMENT PRIMARY KEY")]
      (with-open [con (jdbc/get-connection (ds))]
        (when (stored-proc?)
          (try
            (jdbc/execute-one! con ["DROP PROCEDURE FRUITP"])
            (catch Throwable _)))
        (try
          (do-commands con [(str "DROP TABLE " fruit)])
          (catch Exception _))
        (try
          (do-commands con [(str "DROP TABLE " btest)])
          (catch Exception _))
        (when (postgres?)
          (try
            (do-commands con ["DROP TABLE LANG_TEST"])
            (catch Exception _))
          (try
            (do-commands con ["DROP TYPE LANGUAGE"])
            (catch Exception _))
          (do-commands con ["CREATE TYPE LANGUAGE AS ENUM('en','fr','de')"])
          (do-commands con ["
CREATE TABLE LANG_TEST (
  LANG LANGUAGE NOT NULL
)"]))
        (do-commands con [(str "
CREATE TABLE " fruit " (
  ID INTEGER " auto-inc-pk ",
  NAME VARCHAR(32),
  APPEARANCE VARCHAR(32) DEFAULT NULL,
  COST INT DEFAULT NULL,
  GRADE REAL DEFAULT NULL
)")])
        (let [created (atom false)]
          ;; MS SQL Server does not support bool/boolean:
          (doseq [btype ["BOOL" "BOOLEAN" "BIT"]]
            ;; Derby does not support bit:
            (doseq [bitty ["BIT" "SMALLINT"]]
              (try
                (when-not @created
                  (do-commands con [(str "
CREATE TABLE " btest " (
  NAME VARCHAR(32),
  IS_IT " btype ",
  TWIDDLE " bitty "
)")])
                  (reset! created true))
                (catch Throwable _))))
          (when-not @created
            (println (:dbtype db) "failed btest creation")
            #_(throw (ex-info (str (:dbtype db) " has no boolean type?") {}))))
        (when (stored-proc?)
          (let [[begin end] (if (postgres?) ["$$" "$$"] ["BEGIN" "END"])]
            (try
              (do-commands con [(str "
CREATE PROCEDURE FRUITP" (cond (hsqldb?) "() READS SQL DATA DYNAMIC RESULT SETS 2 "
                               (mssql?) " AS "
                               (postgres?) "() LANGUAGE SQL AS "
                               :else "() ") "
 " begin " " (if (hsqldb?)
               (str "ATOMIC
  DECLARE result1 CURSOR WITH RETURN FOR SELECT * FROM " fruit " WHERE COST < 90;
  DECLARE result2 CURSOR WITH RETURN FOR SELECT * FROM " fruit " WHERE GRADE >= 90.0;
  OPEN result1;
  OPEN result2;")
               (str "
  SELECT * FROM " fruit " WHERE COST < 90;
  SELECT * FROM " fruit " WHERE GRADE >= 90.0;")) "
 " end "
")])
              (catch Throwable t
                (println 'procedure (:dbtype db) (ex-message t))))))
       (sql/insert-multi! con :fruit
                          [:name :appearance :cost :grade]
                          [["Apple" "red" 59 nil]
                           ["Banana" "yellow" nil 92.2]
                           ["Peach" nil 139 90.0]
                           ["Orange" "juicy" 89 88.6]]
                          {:return-keys false})
       (t)))))

(comment
  ;; this is a convenience to bring next.jdbc's test dependencies
  ;; into any REPL running Clojure 1.12.0 Alpha 2's new add-libs API
  ;; which allows me to develop and test next.jdbc inside my work's
  ;; "everything" REPL environment
  (require '[clojure.repl.deps :refer [add-libs]]
           '[clojure.edn :as edn])
  (def test-deps (-> (slurp "https://raw.githubusercontent.com/seancorfield/next-jdbc/develop/deps.edn")
                     (edn/read-string)
                     :aliases
                     :test
                     :extra-deps))
  (add-libs test-deps)
  ;; now you can load this file... and then you can load other test
  ;; files and run their tests as needed... which will leave (ds)
  ;; set to the embedded PostgreSQL datasource -- reset it with this:
  (let [db test-h2-mem #_test-mysql-map]
    (reset! test-db-spec db)
    (reset! test-datasource (jdbc/get-datasource db))))

  At some point, the datasource/connection tests should probably be extended
  to accept EDN specs from an external source (environment variables?)."
  (:require [clojure.string :as str]
            [clojure.test :refer [deftest is testing]]
            [next.jdbc.connection :as c]
            [next.jdbc.protocols :as p])
  (:import (com.zaxxer.hikari HikariDataSource)
           (com.mchange.v2.c3p0 ComboPooledDataSource PooledDataSource)))

(deftest test-sourceable-via-metadata
  (doseq [db test-dbs]
    (let [ds (p/get-datasource
              ^{`p/get-datasource (fn [v] (p/get-datasource (first v)))} [db])]
      (is (instance? javax.sql.DataSource ds)))))

(deftest test-get-connection
  (doseq [db test-dbs]
    (println 'test-get-connection (:dbtype db))
    (testing "datasource via Associative"
      (let [ds (p/get-datasource db)]
        (is (instance? javax.sql.DataSource ds))
        (is (str/index-of (pr-str ds) (str "jdbc:" (:dbtype db))))
        ;; checks get-datasource on a DataSource is identity
        (is (identical? ds (p/get-datasource ds)))
        (with-open [con (p/get-connection ds {})]
          (is (instance? java.sql.Connection con)))))
    (testing "datasource via String"
      (let [[url _] (#'c/spec->url+etc db)
            ds (p/get-datasource url)]
        (is (instance? javax.sql.DataSource ds))
        (is (str/index-of (pr-str ds) url))
        (.setLoginTimeout ds 0)
        (is (= 0 (.getLoginTimeout ds)))
        (with-open [con (p/get-connection ds {})]
          (is (instance? java.sql.Connection con)))))
    (testing "datasource via jdbcUrl"
      (let [[url etc] (#'c/spec->url+etc db)
            ds (p/get-datasource (assoc etc :jdbcUrl url))]
        (if (= "derby" (:dbtype db))
          (is (= {:create true} etc))
          (is (= {} etc)))
        (is (instance? javax.sql.DataSource ds))
        (is (str/index-of (pr-str ds) (str "jdbc:" (:dbtype db))))
        ;; checks get-datasource on a DataSource is identity
        (is (identical? ds (p/get-datasource ds)))
        (.setLoginTimeout ds 1)
        (is (= 1 (.getLoginTimeout ds)))
        (with-open [con (p/get-connection ds {})]
          (is (instance? java.sql.Connection con)))))
    (testing "datasource via HikariCP"
      ;; the type hint is only needed because we want to call .close
      (with-open [^HikariDataSource ds (c/->pool HikariDataSource db)]
        (is (instance? javax.sql.DataSource ds))
        ;; checks get-datasource on a DataSource is identity
        (is (identical? ds (p/get-datasource ds)))
        (with-open [con (p/get-connection ds {})]
          (is (instance? java.sql.Connection con)))))
    (testing "datasource via c3p0"
      ;; the type hint is only needed because we want to call .close
      (with-open [^PooledDataSource ds (c/->pool ComboPooledDataSource db)]
        (is (instance? javax.sql.DataSource ds))
        ;; checks get-datasource on a DataSource is identity
        (is (identical? ds (p/get-datasource ds)))
        (with-open [con (p/get-connection ds {})]
          (is (instance? java.sql.Connection con)))))
    (testing "connection via map (Object)"
      (with-open [con (p/get-connection db {})]
        (is (instance? java.sql.Connection con))))))

;; # How Clerk Works 🕵🏻‍♀️
(ns how-clerk-works
  {:nextjournal.clerk/toc true}
  (:require [next.jdbc :as jdbc]
            [nextjournal.clerk :as clerk]
            [nextjournal.clerk.parser :as parser]
            [nextjournal.clerk.eval :as eval]
            [nextjournal.clerk.analyzer :as ana]
            [weavejester.dependency :as dep]))

;; For side effectful functions that should be cached, like a database query, you can add a value like this `#inst` to control when evaluation should happen.
(def query-results
  (let [_run-at #_(java.util.Date.) #inst "2021-05-20T08:28:29.445-00:00"
        ds (next.jdbc/get-datasource {:dbtype "sqlite" :dbname "chinook.db"})]
    (with-open [conn (next.jdbc/get-connection ds)]
      (clerk/table (next.jdbc/execute! conn ["SELECT AlbumId, Bytes, Name, TrackID, UnitPrice FROM tracks"])))))

  The clojure.java.jdbc/with-db-* functions would all be treated the same way."
  (:require [next.jdbc :as jdbc]))

(jdbc/with-transaction [tx (jdbc/get-datasource db) {} {}] ;; 2 or 3 forms
  (jdbc/execute! tx ["select * from table where foo = ?" 123]))

(jdbc/with-transaction [[tx] (jdbc/get-datasource db)] ;; requires a symbol
  (jdbc/execute! tx ["select * from table where foo = ?" 123]))

(ns ^:no-doc next.jdbc.default-options
  "Implementation of default options logic."
  (:require [next.jdbc.protocols :as p]))

(extend-protocol p/Sourceable
  DefaultOptions
  (get-datasource [this]
                  (p/get-datasource (:connectable this))))

  At some point, the datasource/connection tests should probably be extended
  to accept EDN specs from an external source (environment variables?)."
  (:require [clojure.string :as str]
            [clojure.test :refer [deftest is testing]]
            [next.jdbc.connection :as c]
            [next.jdbc.protocols :as p])
  (:import (com.zaxxer.hikari HikariDataSource)
           (com.mchange.v2.c3p0 ComboPooledDataSource PooledDataSource)))

(deftest test-sourceable-via-metadata
  (doseq [db test-dbs]
    (let [ds (p/get-datasource
              ^{`p/get-datasource (fn [v] (p/get-datasource (first v)))} [db])]
      (is (instance? javax.sql.DataSource ds)))))

(deftest test-get-connection
  (doseq [db test-dbs]
    (println 'test-get-connection (:dbtype db))
    (testing "datasource via Associative"
      (let [ds (p/get-datasource db)]
        (is (instance? javax.sql.DataSource ds))
        (is (str/index-of (pr-str ds) (str "jdbc:" (:dbtype db))))
        ;; checks get-datasource on a DataSource is identity
        (is (identical? ds (p/get-datasource ds)))
        (with-open [con (p/get-connection ds {})]
          (is (instance? java.sql.Connection con)))))
    (testing "datasource via String"
      (let [[url _] (#'c/spec->url+etc db)
            ds (p/get-datasource url)]
        (is (instance? javax.sql.DataSource ds))
        (is (str/index-of (pr-str ds) url))
        (.setLoginTimeout ds 0)
        (is (= 0 (.getLoginTimeout ds)))
        (with-open [con (p/get-connection ds {})]
          (is (instance? java.sql.Connection con)))))
    (testing "datasource via jdbcUrl"
      (let [[url etc] (#'c/spec->url+etc db)
            ds (p/get-datasource (assoc etc :jdbcUrl url))]
        (if (= "derby" (:dbtype db))
          (is (= {:create true} etc))
          (is (= {} etc)))
        (is (instance? javax.sql.DataSource ds))
        (is (str/index-of (pr-str ds) (str "jdbc:" (:dbtype db))))
        ;; checks get-datasource on a DataSource is identity
        (is (identical? ds (p/get-datasource ds)))
        (.setLoginTimeout ds 1)
        (is (= 1 (.getLoginTimeout ds)))
        (with-open [con (p/get-connection ds {})]
          (is (instance? java.sql.Connection con)))))
    (testing "datasource via HikariCP"
      ;; the type hint is only needed because we want to call .close
      (with-open [^HikariDataSource ds (c/->pool HikariDataSource db)]
        (is (instance? javax.sql.DataSource ds))
        ;; checks get-datasource on a DataSource is identity
        (is (identical? ds (p/get-datasource ds)))
        (with-open [con (p/get-connection ds {})]
          (is (instance? java.sql.Connection con)))))
    (testing "datasource via c3p0"
      ;; the type hint is only needed because we want to call .close
      (with-open [^PooledDataSource ds (c/->pool ComboPooledDataSource db)]
        (is (instance? javax.sql.DataSource ds))
        ;; checks get-datasource on a DataSource is identity
        (is (identical? ds (p/get-datasource ds)))
        (with-open [con (p/get-connection ds {})]
          (is (instance? java.sql.Connection con)))))
    (testing "connection via map (Object)"
      (with-open [con (p/get-connection db {})]
        (is (instance? java.sql.Connection con))))))

  At some point, the datasource/connection tests should probably be extended
  to accept EDN specs from an external source (environment variables?)."
  (:require [clojure.string :as str]
            [clojure.test :refer [deftest is testing use-fixtures]]
            [next.jdbc.connection :as c]
            [next.jdbc.protocols :as p]
            [next.jdbc.specs :as specs]
            [next.jdbc.test-fixtures :refer [with-test-db db]]))

(deftest test-uri-strings
  (testing "datasource via String"
    (let [db-spec (db)
          db-spec (if (= "embedded-postgres" (:dbtype db-spec))
                    (assoc db-spec :dbtype "postgresql")
                    db-spec)
          [url etc] (#'c/spec->url+etc db-spec)
          {:keys [user password]} etc
          etc (dissoc etc :user :password)
          uri (-> url
                  ;; strip jdbc: prefix for fun
                  (str/replace #"^jdbc:" "")
                  (str/replace #";" "?") ; for SQL Server tests
                  (str/replace #":sqlserver" "") ; for SQL Server tests
                  (cond-> (and user password)
                    (str/replace #"://" (str "://" user ":" password "@"))))
          ds (p/get-datasource (assoc etc :jdbcUrl uri))]
      (when (and user password)
        (with-open [con (p/get-connection ds {})]
          (is (instance? java.sql.Connection con)))))))