CODEWITHSUNDEEP
javasqljdbcclojure
joefromct
joefromct

Reputation: 1556

generate "create table" script from jdbc (clojure/java)

Does anyone know if anything is available to generate a "create table script" FROM a database through jdbc using clojure or java?

I could just fetch from the system tables or information schema, but per different database type that would be different. for instance MSSQL would have to be queries to build the create table (or view) from information schema and db2 from ibm.systables. Was hoping maybe this problem had been solved before in the JDBC world.

For instance, i'm looking to diff the create table statements between a source database and a target database.

Thanks,

Upvotes: 2

Views: 1898

Answers (2)

mobyte
mobyte

Reputation: 3752

In MySQL you can use show create table:

(require '[clojure.java.jdbc :as jdbc])

(defn gen-script [db table]
  (jdbc/with-connection db
    (jdbc/with-query-results rs [(str "show create table " table)]
      (get (first rs) (keyword "create table")))))

test:

(def db {:classname "com.mysql.jdbc.Driver"
         :subprotocol "mysql"
         :subname (str "//localhost:3306/testtest")
         :user "root"
         :password "..."})

(println (gen-script db "example"))

=> CREATE TABLE `example` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `name` varchar(30) DEFAULT NULL,
     `age` int(11) DEFAULT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1

In other DBs you can start with this one:

(defn- gen-script [db table]
  (jdbc/with-connection db
    (let [conn (jdbc/connection)
          meta (.getMetaData conn)]
      (format "create table %s (\n%s\n)"
         table
         (apply str
           (interpose ",\n"
            (map (fn [x]
                   (format "  %s %s(%s)%s%s%s"
                      (:column_name x)
                      (:type_name x)
                      (:column_size x)
                      (if (not= "YES" (:is_nullable x)) " NOT NULL" "")
                      (if (= "YES" (:is_autoincrement x)) " AUTO_INCREMENT""")             
                      (if (= "YES" (:column_def x)) " DEFAULT" "")))
                 (resultset-seq (.getColumns meta nil nil table "%")))))))))

test:

(println (gen-script db "example"))

=> create table example (
     id INT(10) NOT NULL AUTO_INCREMENT,
     name VARCHAR(30),
     age INT(10)

)

Upvotes: 1

shyam
shyam

Reputation: 9368

Try using the hibernate tools project. It will generate a hibernate mapping and from it you can generate the create statements.

But, why go that way when you database can do that for you...

Upvotes: 0

Related Questions