snapshotdb.md

Some code to generate an SQLite dump from a database via a JDBC connection.

First we need a deps.edn file

{:deps {org.clojure/java.jdbc {:mvn/version "0.7.12"}
        org.xerial/sqlite-jdbc {:mvn/version "3.34.0"}
        com.oracle.database.jdbc/ojdbc8 {:mvn/version "21.1.0.0"}}}

And some code...

(ns snapshotdb
  (:require [clojure.java.jdbc :as j]
            [clojure.string :as str]))


(defn db-tables
  [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
  [db-spec {:keys [catalog schemaPattern tableNamePattern columnNamePattern]}]
  (j/with-db-metadata
    [meta db-spec]
    (j/metadata-result
      (.getColumns meta catalog schemaPattern tableNamePattern columnNamePattern))))


(defn snapshot
  [src-db dst-db table-columns]
  (doseq [[table_name ms] table-columns]
    (let [column_names (map :column_name ms)
          create-query (str "create table " table_name " (" (str/join ", " column_names) ")")]
      (j/execute! dst-db create-query)
      (when-let [results (seq (j/query src-db (str "select * from " table_name)))]
        (println (str table_name " x" (count results)))
        (j/insert-multi! dst-db table_name results)))))

We need two db connection specs and probably some filtering on the tables involved.

(def src-db
  {:dbtype   "oracle"
   :dbname   "xe"
   :user     "USER"
   :password "PASS"
   :port     1521})

(def dst-db
  {:classname   "org.sqlite.JDBC"
   :subprotocol "sqlite"
   :subname     "sqlite.db"})

(def table-columns
  (for [{:keys [table_name]} (db-tables src-db {:schemaPattern "USER"})]
    [table_name (db-columns src-db {:schemaPattern "USER" :tableNamePattern table_name})]))

(time (snapshot src-db dst-db table-columns))