mysql-schema-diff

mysql-schema-diff.clj

(require '[clojure.java.jdbc :as jdbc])
(require '[clojure.set :as set])
(use 'clojure.data)
(def not-nil? (complement nil?))

(def sql-query ["SELECT concat(table_schema,'.',table_name) as 'schema',table_rows FROM tables
                          where table_schema <> 'mysql'
                          and table_schema <> 'information_schema'
                          and table_name NOT REGEXP '[0-9]'
                          and table_rows > 0"])

(def databases
  [{:name "Kwazulu Natal"
   :master {:classname "com.mysql.jdbc.Driver"
            :subprotocol "mysql"
            :user "mysqluser"
            :password "mysqluser"
            :subname "//10.3.0.4:3306/information_schema"}
   :slave  {:classname "com.mysql.jdbc.Driver"
            :subprotocol "mysql"
            :user "mysqluser"
            :password "mysqluser"
            :subname "//10.0.0.27:3306/information_schema"}}
  {:name "Cape Town"
    :master {:classname "com.mysql.jdbc.Driver"
            :subprotocol "mysql"
            :user "mysqluser"
            :password "mysqluser"
            :subname "//10.2.0.3:3306/information_schema"}
    :slave  {:classname "com.mysql.jdbc.Driver"
            :subprotocol "mysql"
            :user "mysqluser"
            :password "mysqluser"
            :subname "//10.0.0.26:3306/information_schema"}}
   {:name "Albrecht Cape"
       :master {:classname "com.mysql.jdbc.Driver"
                 :subprotocol "mysql"
                 :user "mysqluser"
                 :password "mysqluser"
                 :subname "//10.2.13.2:3306/information_schema"}
        :slave  {:classname "com.mysql.jdbc.Driver"
                 :subprotocol "mysql"
                 :user "mysqluser"
                 :password "mysqluser"
                 :subname "//10.0.1.167:3306/information_schema"}}])

(defn format-output [fst & more]
  (str fst "\n\n" (clojure.string/join "\n" (flatten more))))

(defn schema-row-diff [input]
  (for [x databases]
    (let [db-name (-> x :name)
          slave-db (jdbc/query (:slave x) sql-query)
          master-db (jdbc/query (:master x) sql-query)
          diffed (clojure.set/difference (set master-db) (set slave-db))
          results (map :schema (into '() diffed))]
	(conj results db-name))))

(spit "tmp.txt" (clojure.string/join "\n" (flatten (schema-row-diff databases))))