How to export MySQL database table into H2 in Clojure

H2 database use the standard SQL statements, many features in MySQL are not supported in H2. To port a MySQL table to H2 need some tunes. In this post I will illustrate the process with an example and Clojure code.

Step 1. Export table definition from MySQL

To make the table definition works in H2 database, we need it be compatible with ANSI SQL and strip all the MySQL specific options. The following line will give you a fine SQL statement.

 
mysqldump -uroot databasename post --no-data --compatible=ansi,no_table_options,no_field_options,no_key_options --hex-blob --skip-opt > post.sql
 

This is what we get

 
 
CREATE TABLE "post" (
  "col_postid" INT(11) NOT NULL,
  "col_title" VARCHAR(1000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  "col_contents" mediumtext COLLATE utf8_unicode_ci NOT NULL,
  "col_insertdate" datetime DEFAULT NULL,
  "col_keywords" VARCHAR(1000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  "col_description" text COLLATE utf8_unicode_ci NOT NULL,
  "col_category" VARCHAR(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  "col_outerlink" INT(11) NOT NULL DEFAULT '0',
  "col_format" SMALLINT(2) DEFAULT '0',
  "col_origin" mediumtext COLLATE utf8_unicode_ci NOT NULL,
  "col_publishstate" SMALLINT(6) DEFAULT '1',
  "col_seotitle" VARCHAR(1000) COLLATE utf8_unicode_ci DEFAULT '',
  "col_taglist" VARCHAR(1000) COLLATE utf8_unicode_ci DEFAULT '',
  PRIMARY KEY  ("col_postid"),
  KEY "seotitleidx" ("col_seotitle"(333)),
  KEY "stateindex" ("col_publishstate"),
  KEY "catindex" ("col_category")
);
 

Step 2. Remove incompatible options

There are still some incompatible options in the statement generated above.

We need to remove all CHARACTER SET and COLLATE options. If you are using Emacs, the plugin multiple cursors is tremendously helpful.

In this case, select the how block of SQL text and execute M-x mc/mark-all-in-region and input collate utf8_unicode_ci, now all the occurrences will be attached with a cursor, now delete, they all gone.

Remove the ranges defined on key:

 
("col_seotitle"(333))
--will be
("col_seotitle")
 

Step 3. Create table in H2

Now we can create the table in H2. We must use MySQL mode in connection configuration.

 
(def demo-mysql-settings
   {
    :classname   "org.h2.Driver"
    :subprotocol "h2:mem"
    :subname     "demomysql;DB_CLOSE_DELAY=-1;MODE=MySQL"
    :user        "sa"
    :password    ""
   }
)
 

Clojure don't support multiple lines of text, but again with the help of multiple cursors, it's easy to transform the text to be usable for Clojure code. First you need to escape all double quotes, selection the statements and execute M-x mc/mark-all-in-region and input double quote then input \". Select the text again and M-x mc/edit-begin-of-lines and input double quote. And so on. The final version is this

 
(require '[clojure.java.jdbc :as j])
 
(defn zoo []
  (j/execute! demo-mysql-settings [
    (join "\n" '(
    "CREATE TABLE \"post\" ("
    "  \"col_postid\" int(11) NOT NULL,"
    "  \"col_title\" varchar(1000)  NOT NULL default '',"
    "  \"col_contents\" mediumtext  NOT NULL,"
    "  \"col_insertdate\" datetime default NULL,"
    "  \"col_keywords\" varchar(1000)  NOT NULL default '',"
    "  \"col_description\" text  NOT NULL,"
    "  \"col_category\" varchar(100)  NOT NULL default '',"
    "  \"col_outerlink\" int(11) NOT NULL default '0',"
    "  \"col_format\" smallint(2) default '0',"
    "  \"col_origin\" mediumtext  NOT NULL,"
    "  \"col_publishstate\" smallint(6) default '1',"
    "  \"col_seotitle\" varchar(1000)  default '',"
    "  \"col_taglist\" varchar(1000)  default '',"
    "  PRIMARY KEY  (\"col_postid\"),"
    "  KEY \"seotitleidx\" (\"col_seotitle\"),"
    "  KEY \"stateindex\" (\"col_publishstate\"),"
    "  KEY \"catindex\" (\"col_category\")"
    ");"
    ))
  ])
)
 

Execute this function in Clojure will create the table for you .