gistfile1.md

TL;DR - refactoring of jdbc code discussed by clojure devs, enabled by recent modularity.

https://github.com/primedia/iws-oracle/tree/defsproc

Goals

• Reduce duplication of boilerplate JDBC code.
• Expose Oracle Stored Procedure (contract) as data (map).

JDBC

;;     PROCEDURE P_RGETUSERMGTCO_WAREHOUSE
;;     (
;;  1     iUserid            IN             INTEGER
;;  2     ,iIncSelectAll     IN             INTEGER            DEFAULT 0
;;  3     ,oDataSet          OUT            SYS_REFCURSOR
;;  4     ,iStatusFilter     IN             INTEGER            DEFAULT 0
;;     )

(defn get-user-mgtco
  "For AG sites console. Uses webicon schema."
  [user-id conn]
  (with-open [stmt (.prepareCall conn
                                 "{ call p_rgetusermgtco_warehouse(?,?,?,?) }")]
    (doto stmt
      (.setInt 1 user-id)
      (.setInt 2 0)
      (.registerOutParameter 3 oc/ORACLE_CURSOR)
      (.setInt 4 1)
      (.execute))
    (doall (resultset-seq (.getObject stmt 3)))))

def-sproc

+70 lines macro code

(def user-mgtco-sproc
    {:sproc "p_rgetusermgtco_warehouse"
     :input {:user-id    {:type :int :order 1 :value 0}
             :select-all {:type :int :order 2 :value 0} 
             :check-form {:type :int :order 4 :value 1}}
     :output {:cursor {:type :cursor :order 3 :row-spec {}}}})
    
(def-sproc get-user-mgtco user-mgtco-sproc [:user-id] cursor)

Pros (of declarative approach)

• Changes (logging, ex handling, etc) localized to def-sproc
• Increasing returns code size
• Since macro, generates equivalent, performant code

Future

• Compare our (expected) schema w/ database

  • select * from all_arguments where lower(object_name) = lower('p_rgetusermgtco_warehouse')
  • on randr startup, periodic job (uses shared jar), iws dashboard

• Not sure about abstractions

  • Needs more input
  • Shouldn't have to know order's relationship to generated SQL