Simple script to generate clojure.spec info for a database.

db-spec.clj

(ns db-spec.core
  (:require [clojure.java.jdbc :as j]
            [clojure.spec :as s])
  (:import (java.sql Types)))

(defn db-tables-raw
  [db-spec {:keys [catalog schemaPattern tableNamePattern]}]
  (j/with-db-metadata [meta db-spec]
    (j/metadata-result
      (.getTables meta catalog schemaPattern tableNamePattern (into-array String ["TABLE"])))))

(defn db-columns-raw
  [db-spec {:keys [catalog schemaPattern tableNamePattern columnNamePattern]}]
  (j/with-db-metadata
    [meta db-spec]
    (j/metadata-result
      (.getColumns meta catalog schemaPattern tableNamePattern columnNamePattern))))

(def db-types
  (into {} (for [f (.getFields Types)]
             [(int (.get f nil)) (keyword "java.sql.Types" (.getName f))])))
(def col-type (comp db-types int :data_type))

(def db-tables (memoize db-tables-raw))
(def db-columns (memoize db-columns-raw))
(def db-query (memoize j/query))


(defmulti col-spec col-type)

(defmethod col-spec :default
  [col]
  (col-type col))

(defmethod col-spec :java.sql.Types/VARCHAR
  [{:keys [char_octet_length]}]
  `(s/and string? (max-length ~(int char_octet_length))))

(defmethod col-spec :java.sql.Types/DECIMAL
  [{:keys [column_size decimal_digits]}]
  `number?)

(defmethod col-spec :java.sql.Types/TIMESTAMP
  [{:keys []}]
  `inst?)

(defmethod col-spec :java.sql.Types/FLOAT
  [{:keys []}]
  )

(defmethod col-spec :java.sql.Types/BLOB
  [{:keys [char_octet_length]}]
  `(s/and string? (max-length ~(int char_octet_length))))

(defmethod col-spec :java.sql.Types/VARBINARY
  [{:keys [char_octet_length]}]
  `(s/and string? (max-length ~(int char_octet_length))))

(defmethod col-spec :java.sql.Types/CHAR [_] char?)
(defmethod col-spec :java.sql.Types/CLOB
  [{:keys [char_octet_length]}]
  `(s/and string? (max-length ~(int char_octet_length))))

(defmethod col-spec :java.sql.Types/LONGVARCHAR [_]
  `string?)


(comment

  (def db
    {:classname   "oracle.jdbc.OracleDriver"
     :subprotocol "oracle"
     :subname     "thin:@localhost:1521/xe"
     :user        "XXX"
     :password    "XXX"})

  (db-query db "select * from pmr_project")
  (db-tables db {:schemaPattern "MYSCHEMA"})
  (db-columns db {:schemaPattern "MYSCHEMA"})

  (frequencies (map col-type (db-columns db {:schemaPattern "MYSCHEMA" :tableNamePattern "PROJ_%"})))

  (for [col (db-columns db {:schemaPattern "MYSCHEMA" :tableNamePattern "PROJ_%"})]
    `(s/def ~(keyword (:table_name col) (:column_name col))
       ~(col-spec col)))
  
  (for [[table_name cols] (group-by :table_name (db-columns db {:schemaPattern "MYSCHEMA" :tableNamePattern "PROJ_%"}))]
    `(s/def ~(keyword table_name "*") (s/keys :req-un ~(mapv #(keyword table_name (:column_name %)) cols)))))