Base database namespace, and a specific one for easy database usage

db (slash) polaris.clj

(ns ns-toolkit.db.polaris
  "Namespace for accessing the Polaris database for all the data it has."
  (:require [ns-toolkit.config :as cfg]
            [ns-toolkit.db :as db]
            [ns-toolkit.logging :refer [log-execution-time!]]
            [cheshire.core :as json]
            [clj-time.coerce :refer [to-timestamp from-date]]
            [clojure.java.jdbc :refer [IResultSetReadColumn ISQLValue result-set-read-column] :as sql]
            [clojure.string :as cs]
            [clojure.tools.logging :refer [error errorf warnf infof info]]
            [honeysql.core :as hsql]
            [honeysql.helpers :refer [limit]])
  (:import com.mchange.v2.c3p0.ComboPooledDataSource
           java.sql.SQLException
           java.lang.Throwable
           org.postgresql.util.PGobject))

;; Create a pooled connection from the config parameters in project
(defonce pooled-connection
  (delay
    (let [{:keys [classname subprotocol subname user password min-pool-size max-pool-size]} (cfg/polaris-db)]
      {:datasource (doto (ComboPooledDataSource.)
                     (.setDriverClass classname)
                     (.setJdbcUrl (str "jdbc:" subprotocol ":" subname))
                     (.setUser user)
                     (.setPassword password)
                     ;; yay magic numbers!
                     (.setMaxIdleTimeExcessConnections (* 30 60))
                     (.setMaxIdleTime (* 3 60 60))
                     (.setMinPoolSize (or min-pool-size 3))
                     (.setMaxPoolSize (or max-pool-size 15)))})))

;; Somewhat faking java.jdbc's original *connection* behavior so that
;; we don't have to pass one around.
(def ^:dynamic *connection* nil)

(defn connection []
  []
  (or *connection* @pooled-connection))

(defmacro transaction
  [& body]
  `(sql/with-db-transaction [con# (connection)]
     (binding [*connection* con#]
       ~@body)))

(defn query
  "A wrapper around clojure.java.jdbc's clunky query macro.

   Argument can be a SQL string, a parameterized vector as in the
   sql/with-query-results macro, or (recommended) a honeysql query
   object. Any extra arguments are passed through directly to
   java.jdbc/query, so processing optimizations (like stream
   processing) can be obtained that way."
  [expr & query-opts]
  (let [query-arg (cond (string? expr) [expr]
                        (map? expr) (hsql/format expr)
                        :else expr)
        ;; I'm assuming it's okay to use the concurrency options here, but
        ;; I can't say I understand them well enough to be sure.
        query-arg-with-opts (apply vector
                                   {:concurrency :read-only
                                    :result-type :forward-only}
                                   query-arg)]
    (try
      (apply sql/query (connection) query-arg-with-opts query-opts)
      (catch SQLException se
        (warnf "SQLException thrown on: %s :: %s" query-arg-with-opts (.getMessage se)))
      (catch Throwable t
        (warnf "Exception thrown on: %s :: %s" query-arg-with-opts (.getMessage t))))))

(defn do-commands
  "Function to execute several SQL commands and not retain any of the output.
  This is typically done when you need to migrate or update the database with
  DDL commands, and you want them all to be done within one transaction."
  [& cmds]
  (if-not (empty? cmds)
    (try
      (apply sql/db-do-commands (connection) true cmds)
      (catch SQLException se
        (warnf "SQLException thrown on: %s :: %s" cmds (.getMessage se)))
      (catch Throwable t
        (warnf "Exception thrown on: %s :: %s" cmds (.getMessage t))))))

(defn insert!
  "Function to execute a SQL insert command for the provided rows into the
  provided table name - given as a keyword. This is the simple way to get
  data into the database - trapping for any errors, and logging them."
  [tbl rows]
  (if-not (empty? rows)
    (try
      (apply sql/insert! (connection) tbl rows)
      (catch SQLException se
        (warnf "SQLException thrown on: %s :: %s" rows (.getMessage se)))
      (catch Throwable t
        (warnf "Exception thrown on: %s :: %s" rows (.getMessage t))))))

(defn delete!
  "Function to execute a SQL delete command for the provided where clause
  from the provided table name - given as a keyword. This is the simple way
  to remove data from the database - trapping for any errors, and logging them."
  [tbl clause]
  (if-not (empty? clause)
    (try
      (sql/delete! (connection) tbl clause)
      (catch SQLException se
        (warnf "SQLException thrown on: %s :: %s" clause (.getMessage se)))
      (catch Throwable t
        (warnf "Exception thrown on: %s :: %s" clause (.getMessage t))))))

(defn update!
  "Function to execute a SQL update command for the provided where clause
  from the provided table name - given as a keyword. This is the simple way
  to update values in the database - trapping for any errors, and logging them.
  An example would be:

    (update! :people {:age 54} [\"name = ?\" lname])

  would set the 'age' to 54 for all rows where 'name' is `lname`."
  [tbl set-map clause]
  (if-not (empty? clause)
    (try
      (sql/update! (connection) tbl set-map clause)
      (catch SQLException se
        (warnf "SQLException thrown on: %s :: %s" clause (.getMessage se)))
      (catch Throwable t
        (warnf "Exception thrown on: %s :: %s" clause (.getMessage t))))))

db.clj

(ns ns-toolkit.db
  "Namespace for general database tools for the instances we need to access."
  (:require [ns-toolkit.config :as cfg]
            [ns-toolkit.logging :refer [log-execution-time!]]
            [cheshire.core :as json]
            [clj-time.coerce :refer [to-timestamp from-date]]
            [clojure.java.jdbc :refer [IResultSetReadColumn ISQLValue result-set-read-column] :as sql]
            [clojure.string :as cs]
            [clojure.tools.logging :refer [error errorf warnf infof info]]
            [honeysql.core :as hsql]
            [honeysql.helpers :refer [limit]])
  (:import com.mchange.v2.c3p0.ComboPooledDataSource
           java.sql.SQLException
           java.lang.Throwable
           org.postgresql.util.PGobject))

;;
;; Some support code customizing clojure.java.jdbc to handle
;; database arrays.
;;
(defn value-to-json-pgobject
  "Function to take a _complex_ clojure data element and convert it into
  JSON for inserting into postgresql 9.4+. This is the core of the mapping
  **into** the postgres database."
  [value]
  (doto (PGobject.)
        (.setType "json")
        (.setValue (json/generate-string value))))

(defn value-to-jsonb-pgobject
  "Function to take a _complex_ clojure data element and convert it into
  JSONB for inserting into postgresql 9.4+. This is the core of the mapping
  **into** the postgres database."
  [value]
  (doto (PGobject.)
        (.setType "jsonb")
        (.setValue (json/generate-string value))))

;;
;; Extending this protocol causes java.jdbc to automatically convert
;; different types as we read them in, and is necessary to support
;; reading arrays into a vector.
;;
(extend-protocol ISQLValue
  clojure.lang.IPersistentMap
  (sql-value [value] (value-to-jsonb-pgobject value))

  clojure.lang.IPersistentVector
  (sql-value [value] (value-to-jsonb-pgobject value))

  clojure.lang.IPersistentList
  (sql-value [value] (value-to-jsonb-pgobject value))

  clojure.lang.LazySeq
  (sql-value [value] (value-to-jsonb-pgobject value))

  org.joda.time.DateTime
  (sql-value [value] (to-timestamp value)))

(extend-protocol IResultSetReadColumn
  PGobject
  (result-set-read-column [pgobj metadata idx]
    (let [type  (.getType pgobj)
          value (.getValue pgobj)]
      (case type
        "json" (json/parse-string value true)
        "jsonb" (json/parse-string value true)
        value)))

  java.sql.Timestamp
  (result-set-read-column [ts _ _]
    (from-date ts))

  java.sql.Date
  (result-set-read-column [ts _ _]
    (from-date ts)))

;;
;; General functions that will be used in conversion from, or to, the types
;; necessary for saving in the database.
;;

(defn format-as-uuid
  "Function to format a UUID as a hex string into the typical representation of
  a UUID with the '-' at the proper locations in the string. This function checks
  to make sure it's the right length coming in and then just chops it up and
  rebuilds it in the proper form."
  [id]
  (if (and (string? id) (= (count id) 32))
    (cs/lower-case (str (subs id 0 8) "-" (subs id 8 12) "-" (subs id 12 16) "-" (subs id 16 20) "-" (subs id 20)))
    id))

polaris.clj

(ns ns-toolkit.polaris
  "This is the code that handles the Polaris data as data objects - and not
  just Encompass maps. This is the business logic on top of the Encompass
  data that can be easily exposed in a simple service to allow others access
  to the Polaris data."
  (:require [clj-time.format :refer [formatter parse]]
            [clj-time.core :refer [from-time-zone time-zone-for-id]]
            [clojure.set :refer [rename-keys]]
            [clojure.tools.logging :refer [infof warnf]]
            [ns-toolkit.db.polaris :as db]
            [ns-toolkit.logging :refer [log-execution-time!]]
            [ns-toolkit.util :refer [nil-if-empty uuid?]]))

(defn last-version
  "Function to return the version for a given loan - based on either the
  `loan_number` or the `guid` - you can pass in either. The result
  will be a single map something like this:

    { :guid #uuid \"58ce24fe-df70-4d22-92aa-1f042bdff7df\"
      :loan_number \"123456789\"
      :version 12
      :lastmodified <#DateTime>
      :encompass_lastmodified <#DateTime> }
  "
  [id]
  (let [fld (cond
              (uuid? id) "guid"
              (string? id) "loan_number")]
    (db/query [(str "select guid, loan_number, version, lastmodified, encompass_lastmodified
                       from loan_data
                      where " fld " = ? order by lastmodified desc
                      limit 1") id]
              :result-set-fn first)))

(log-execution-time! last-version {:msg-fn (fn [ret id] (format "%s" id))})