Translate DDL for creating tables from MySQL to H2
I'm trying to set up some modulation / integration tests using an in-memory H2 database and it's all related to Spring. This is for an existing application that uses MySQL for developers and in production.
Now I need a DDL SQL script to create my database structure in H2. I can easily export DDL statements from MySQL. But I don't know what the syntax differences are - at least I'm sure things like engine=InnoDB
should go.
Are there any other syntax differences I should be aware of?
Is there a tool that can automatically convert DDL statements from MySQL syntax to H2 syntax?
source to share
I had a quick jump to this for a recent project and it certainly shouldn't be considered the best or cleanest solution. It should also be noted that I have used this exclusively for testing integration based integration. Ideally, I'll get this on github in the next few weeks for people to add to it, but maybe it helps in the meantime. Java solution:
/**
* Designed to go through MySQL schema produced from forward engineering tools and normalize slightly to
* work with H2 for testing. This only works on the SQL constructs themselves and is not able to
* detect errors such as constraints with the same name.
*
* Assumptions
* - INDEX is created separately from main SQL body
* - Incompatible key words such as order are not used
* - Assumes the name of a constraint is not duplicated
* @author jpgough
*
*/
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
public class MySqlToH2 {
public static String convert(String filePath) throws IOException {
String[] rawSQL = new String(Files.readAllBytes(Paths.get(filePath))).split("\\n");
StringBuilder builder = new StringBuilder();
for(String line : rawSQL) {
if(line.contains("SET")) {
continue;
} else if(line.contains("INDEX")) {
continue;
} else if(line.contains("IF NOT EXISTS")) {
line = line.replaceAll("IF NOT EXISTS", "");
} else if(line.contains("--")) {
continue;
} else if(line.contains("ENGINE")) {
line = ";";
}
line = line.replace("`", "");
builder.append(line).append("\n");
}
return builder.toString();
}
}
source to share