Database Migration

Question

I am working on database migration tool in java. The tool is copying database tables with their data's to the destination database. But i want it to work on different databases. Copy from mysql and create in derby etc. With JDBC, we can gather enough information about the table and its columns. But i am going to ask this, if i can recreate tables on java with sql free. I mean different databases have different data types and some times they differs at sql syntax. So can JDBC or any other library (can be open source) do this job at an easy and global way?

PS: First question in SO, i have been reading posts but first time I am contributing the site, so hello everybody.

Answer 1

I'm not aware of JDBC having a generic facility to do this. You probably have to make a utility library that generates the SQL for table creation.

Start with one that does ANSI SQL and test it on as many platforms as you intend to support. Remember, Java is still write-once, debug everywhere so you will need to test this on any platform you intend to support the system on. Subclass the generator if you have to make dialectic variations of the create statement for any of your platforms.

Answer 2

try to see what HIBERNATE provides for migration. I know that H can generate model objects from database schema and a schema from model objects. So maybe you can reuse some parts from HIBERNATE. They have the notion of DIALECT that does exactly what you are saying: defining specifics of a db implementation.

Answer 3

Apache's DdlUtils is done what i need. When I am searching about crossdb found it, and it is very useful yet powerful. It can generate a database from scratch, just with the parameters. Or it can grab existing database table definitions, also with index definitions. You can use delimiter if you want (it is a deadly important option for me to use Apache Derby). You can just print out these definitions or apply them directly to source database (i havent tried the second one yet). It translates definitions for the selected database. But one big problem is there is no good tutorial about how to start using it. I searched through the packages to find a good place to start. Here is what i have achieved, a sample code to generate full database table create sql.

DerbyPlatform dp = new DerbyPlatform();
dp.setDelimitedIdentifierModeOn(true);
Database dbs = new Database();
DerbyModelReader dmr = new DerbyModelReader(dp);
Database test = dmr.getDatabase(conn, "MyDBTest");

DerbyBuilder db = new DerbyBuilder(dp);
String testSqlDerby = dp.getCreateTablesSql(test, true, true);
System.out.println(testSqlDerby);

System.out.println("\n\n\n\n");

MySql50Platform mp = new MySql50Platform();
mp.setDelimitedIdentifierModeOn(true);
MySqlBuilder mb = new MySqlBuilder(mp);
String testSqlMysql = mp.getCreateTablesSql(test, true, true);
System.out.println(testSqlMysql);

Answer 4

Try SchemaCrawler, which is also open source, and written in Java. It provides programmatic access to database metadata, and also allows scripting in JavaScript. http://schemacrawler.sourceforge.net/