Using JDBC metadata to find and replace fks in a database. Would allow data massaging like "merging records".

replacing_fks.clj

(ns condense.replacing-fks
  (:require [clojure.inspector]
            [clojure.pprint :refer [pprint]]
            [clojure.java.jdbc :as j]
            [clojure.string :as string]
            [clojure.spec :as s]
            [honeysql.core :as sql]
            [honeysql.helpers :as hh]))

(defn db-primary-keys
  [db-spec {:keys [catalog schema table]}]
  (j/with-db-metadata [meta db-spec]
                      (j/metadata-result
                        (.getPrimaryKeys meta catalog schema table))))

(defn db-exported-keys
  [db-spec {:keys [catalog schema table] :as m}]
  (j/with-db-metadata [meta db-spec]
                      (j/metadata-result
                        (.getExportedKeys meta catalog schema table))))

(defn db-from
  [[catalog schem table]]
  (keyword (string/join "." (remove nil? [catalog schem table]))))

(defn db-update
  [[catalog schem table] set-map where-map]
  (-> (hh/update (db-from [catalog schem table]))
      (hh/sset (for [[k v] set-map] [(keyword k) v]))
      (hh/where (into [:and] (for [[k v] where-map] [:= (keyword k) v])))))

(defn replace-fks
  "Look for fks and update them."
  [db catalog schema table old-pk new-pk]
  (let [primary-keys (db-primary-keys db {:catalog catalog :schema schema :table table})
        exported-keys (db-exported-keys db {:catalog catalog :schema schema :table table})
        fk-groups (group-by (juxt :fktable_cat :fktable_schem :fktable_name :fk_name) exported-keys)]
    (assert (= (count primary-keys) (count old-pk) (count new-pk)))
    (for [[[fktable_cat fktable_schem fktable_name _] cols] (sort-by first fk-groups)
          :let [cols (sort-by :key_seq cols)
                fk-cols (map :fkcolumn_name cols)
                pk-cols (map :pkcolumn_name cols)
                new-vals (map (comp new-pk keyword) pk-cols)
                old-vals (map (comp old-pk keyword) pk-cols)]]
      (db-update [fktable_cat fktable_schem fktable_name]
                 (zipmap fk-cols new-vals)
                 (zipmap fk-cols old-vals)))))

(comment
  (replace-fks local-db nil "AFM" "CND_USER" {:CND_USER_ID "OILVER"} {:CND_USER_ID "OLIVER"})
  (replace-fks local-db nil "AFM" "FL" {:BL_ID 1 :FL_ID 1} {:BL_ID 2 :FL_ID 1}))