In the following we provide a generic translator that takes a set of translation rules and performs the translation on a given input String. Two more or less complete sets permitting to translate MySQL from/to Oracle SQL dialects are also provided.
The Eclipse Project tree looks like:
1 - Package trans.util:
This package contains useful classes.1.1 - Strings.java:
package trans.util;
import java.util.ArrayList;
public abstract class Strings
{
public static ArrayList<String> string2Lines(String in)
{
ArrayList<String> result = new ArrayList<String>();
for (String s : in.trim().split("\n"))
{
if ((s != null) && !s.isEmpty())
{
result.add(s);
}
}
return(result);
}
public static String lines2String(ArrayList<String> lines)
{
String result = "";
for (String s : lines)
{
result += s + "\n";
}
return(result);
}
}
This abstract class provides methods splitting one String content to several lines and vice versa.
1.2 - Files.java:
package trans.util;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
public abstract class Files
{
public static String LINE_SEPARATOR = System.getProperty("line.separator");
public static String FILE_SEPARATOR = System.getProperty("file.separator");
public static void string2File(String s, String path) throws IOException
{
FileWriter fw = new FileWriter(path);
BufferedWriter bw = new BufferedWriter(fw);
bw.write(s);
bw.close();
fw.close();
}
public static String file2String(String filename) throws IOException
{
FileReader fr = new FileReader(filename);
BufferedReader br = new BufferedReader(fr);
StringBuilder result = new StringBuilder();
String line;
while ((line = br.readLine()) != null)
{
result.append(line);
result.append(LINE_SEPARATOR);
}
br.close();
fr.close();
return result.toString();
}
}
This class provides methods returning the content of a file as a String and storing some String content in a file given its path. This class is not really needed by the translation logic.
2 - Package trans.rules:
This package contains an abstract class TranslationRules.java that defines translation rules.package trans.rules;
import java.util.HashMap;
import java.util.Map;
public abstract class TranslationRules
{
public static String COMMENT_DELETED_LINES_PREFIX = "-- NOT TRANSLATED : ";
private String[] deleteLinesStartingWith;
public String[] getDeleteLinesStartingWith() {
return deleteLinesStartingWith;
}
public void setDeleteLinesStartingWith(String[] deleteLinesStartingWith) {
this.deleteLinesStartingWith = deleteLinesStartingWith;
}
private boolean commentDeletedLines;
public boolean isCommentDeletedLines() {
return commentDeletedLines;
}
public void setCommentDeletedLines(boolean commentDeletedLines) {
this.commentDeletedLines = commentDeletedLines;
}
private String[] delete;
public String[] getDelete() {
return delete;
}
public void setDelete(String[] delete) {
this.delete = delete;
}
private Map <String, String> replace = new HashMap <String,String >();
public Map <String, String> getReplace() {
return replace;
}
public void setReplace(Map <String, String > replace) {
this.replace = replace;
}
private Map <String, String> replaceMany = new HashMap <String,String >();
public Map <String, String> getReplaceMany() {
return replaceMany;
}
public void setReplaceMany(Map <String, String > replaceMany) {
this.replaceMany = replaceMany;
}
public TranslationRules(String[] deleteLinesStartingWith, String[] delete, Map <String, String> replace, Map <String, String> replaceMany)
{
this.deleteLinesStartingWith = deleteLinesStartingWith;
this.commentDeletedLines = false;
this.delete = delete;
this.replace = replace;
this.replaceMany = replaceMany;
}
}
The class defines the following properties:
- String[] deleteLinesStartingWith: This array defines the regular expression patterns which when matched at the beginning of a line of the input to be translated will result in the line being deleted or commented (with the prefix in String COMMENT_DELETED_LINES_PREFIX) if boolean commentDeletedLines is set to true.
- String[] delete: This array defines the regular expression patterns which when matched inside the input to be translated will be deleted (replaced by the empty String).
- Map <String, String> replace: This map defines the regular expression patterns (in the map keys) which when matched inside the input to be translated will be transformed (to the corresponding map values).
- Map <String, String> replaceMany: Similar to the previous but may need several replacement iterations.
The class TranslationRules.java is implemented by two concrete classes.
- Oracle2MySQL.java:
package trans.rules;
import java.util.HashMap;
import java.util.Map;
public class Oracle2MySQL extends TranslationRules
{
public static String[] ORACLE2MYSQL_DELETE_LINES_STARTING_WITH = {"^(CREATE|create|Create)(\\s+)(USER|user|User)",
"^(GRANT|grant|Grant)"};
public static String[] ORACLE2MYSQL_DELETE = {};
public static Map <String, String > ORACLE2MYSQL_REPLACE = new HashMap <String, String>();
static
{
ORACLE2MYSQL_REPLACE.put("NUMERIC", "DECIMAL");
ORACLE2MYSQL_REPLACE.put("NUMBER", "DECIMAL");
ORACLE2MYSQL_REPLACE.put("VARCHAR2", "VARCHAR");
}
public static Map <String, String> ORACLE2MYSQL_REPLACE_MANY = new HashMap <String, String>();
static
{
ORACLE2MYSQL_REPLACE_MANY.put("(CONSTRAINT|constraint|Constraint)(\\s+)(\\w+)(\\s+)(UNIQUE|unique|Unique)(\\s*)\\((\\s*)(\\w+)(.*)\\)","UNIQUE INDEX $3 ($8)");
}
public Oracle2MySQL()
{
super(ORACLE2MYSQL_DELETE_LINES_STARTING_WITH, ORACLE2MYSQL_DELETE, ORACLE2MYSQL_REPLACE, ORACLE2MYSQL_REPLACE_MANY);
}
public Oracle2MySQL(boolean commentDeletedLines)
{
super(ORACLE2MYSQL_DELETE_LINES_STARTING_WITH, ORACLE2MYSQL_DELETE, ORACLE2MYSQL_REPLACE, ORACLE2MYSQL_REPLACE_MANY);
setCommentDeletedLines(commentDeletedLines);
}
}
- MySQL2Oracle.java:
package trans.rules;
import java.util.HashMap;
import java.util.Map;
public class MySQL2Oracle extends TranslationRules
{
public static String[] MYSQL2ORACLE_DELETE_LINES_STARTING_WITH = {"^(SET|set|Set)",
"^(USE|use|Use)",
"^(CREATE|create|Create)(\\s+)(SCHEMA|schema|Schema)",
"^(CREATE|create|Create)(\\s+)(USER|user|User)",
"^(GRANT|grant|Grant)"};
public static String[] MYSQL2ORACLE_DELETE = {"`mysql`\\.",
"(ON|on|On)(\\s+)(DELETE|delete|Delete)(\\s+)(((NO|no|No)(\\s+)(ACTION|action|Action))|CASCADE|cascade|Cascade|RESTRICT|restrict|Restrict|((SET|set|Set)(\\s+)(NULL|null|Null)))",
"(ON|on|On)(\\s+)(UPDATE|update|Update)(\\s+)(((NO|no|No)(\\s+)(ACTION|action|Action))|CASCADE|cascade|Cascade|RESTRICT|restrict|Restrict|((SET|set|Set)(\\s+)(NULL|null|Null)))",
"(IF|if|If)(\\s+)(EXISTS|exists|Exists)",
"(IF|if|If)(\\s+)(NOT|not|Not)(\\s+)(EXISTS|exists|Exists)",
"(ENGINE|engine|Engine)(\\s+)=(\\s+)(INNODB|innodb|InnoDB)",
"(ENGINE|engine|Engine)(\\s+)=(\\s+)(MYISAM|myisam|MyIsam)",
"`"};
public static Map <String, String> MYSQL2ORACLE_REPLACE = new HashMap <String, String>();
static
{
MYSQL2ORACLE_REPLACE.put("DECIMAL", "NUMERIC");
MYSQL2ORACLE_REPLACE.put("VARCHAR", "VARCHAR2");
}
public static Map <String, String> MYSQL2ORACLE_REPLACE_MANY = new HashMap <String, String>();
static
{
MYSQL2ORACLE_REPLACE_MANY.put("(UNIQUE|unique|Unique)(\\s+)(INDEX|index|Index)(\\s+)(\\w+)(\\s*)\\((\\s*)(\\w+)(\\s+ASC|asc|DESC|desc)?\\)"
, "CONSTRAINT $5 UNIQUE ($8)");
MYSQL2ORACLE_REPLACE_MANY.put("(CREATE|create|Create)(\\s+)(TABLE|table|Table)(\\s+)(IF NOT EXISTS|if not exists|If Not Exists)?(\\s+)(\\w+)(\\s+)\\(([^;]+)(INDEX|index|Index)(\\s+)(\\w+)(\\s*)\\((\\s*)(\\w+)(\\s+ASC|asc|DESC|desc)?(\\s*)\\)(\\s*),([^;]+)\\)(\\s+);"
,"$1$2$3$4$5$6$7$8($9$19)$20;\nCREATE INDEX $12 ON $7($15);");
}
public MySQL2Oracle()
{
super(MYSQL2ORACLE_DELETE_LINES_STARTING_WITH, MYSQL2ORACLE_DELETE, MYSQL2ORACLE_REPLACE, MYSQL2ORACLE_REPLACE_MANY);
}
public MySQL2Oracle(String schema)
{
super(MYSQL2ORACLE_DELETE_LINES_STARTING_WITH, MYSQL2ORACLE_DELETE, MYSQL2ORACLE_REPLACE, MYSQL2ORACLE_REPLACE_MANY);
getDelete()[0] = "`" + schema + "`\\.";
}
public MySQL2Oracle(boolean commentDeletedLines)
{
super(MYSQL2ORACLE_DELETE_LINES_STARTING_WITH, MYSQL2ORACLE_DELETE, MYSQL2ORACLE_REPLACE, MYSQL2ORACLE_REPLACE_MANY);
setCommentDeletedLines(commentDeletedLines);
}
public MySQL2Oracle(String schema, boolean commentDeletedLines)
{
super(MYSQL2ORACLE_DELETE_LINES_STARTING_WITH, MYSQL2ORACLE_DELETE, MYSQL2ORACLE_REPLACE, MYSQL2ORACLE_REPLACE_MANY);
getDelete()[0] = "`" + schema + "`\\.";
setCommentDeletedLines(commentDeletedLines);
}
}
Comments: Static instances are provided to instantiate inherited properties for the both classes above.
Off course these Arrays and Maps can be enriched to match you custom expectations, here are some advice if you want to extend them:
- MYSQL2ORACLE_DELETE_LINES_STARTING_WITH: Here you have the choice to either provide regular expressions (prefixed by ^ which enforces matching the pattern at the beginning of the line) or a simple String. You do not need to matter about trailing blank space characters at the beginning of the line since each line of the input will be trimmed before treatment.
- MYSQL2ORACLE_DELETE: Here regular expressions are expected. Off course constant String patterns can be provided since they are also regular expression and if you want this just pay attention to escape characters having special meanings for regular expressions: example don't use "." if you want to match a simple dot but rather "\\." since the dot means matching any character in a regular expression.
- MYSQL2ORACLE_REPLACE: Here also regular expressions (including constant Strings) are expected. For example in line 26 we add the entry that will transform each VARCHAR (MySQL syntax) occurrence by VARCHAR2 (Oracle syntax).
- MYSQL2ORACLE_REPLACE_MANY: Here also regular expressions (including constant Strings) are expected. Example in lines 34-35 we provide the way to extract an inner foreign key index declaration from a CREATE TABLE query and append it to its end. Since only one foreign key index declaration will be matched by the regular expression, there should be as many replacements as foreign key index declarations.
One important parameter for the MySQL2Oracle.java is the schema. This helps in removing all the schema references in the script. For both classes the property commentDeletedLines can be set upon construction.
3 - Package trans.engine:
This package contains the generic class that will perform the translation given a TranslationRules instance.package trans.engine;
import java.io.IOException;
import java.util.Map;
import trans.rules.MySQL2Oracle;
import trans.rules.TranslationRules;
import trans.util.Files;
import trans.util.Strings;
public class TranslationAgent
{
private TranslationRules translationRules;
public TranslationRules getTranslationRules() {
return translationRules;
}
public void setTranslationRules(TranslationRules translationRules) {
this.translationRules = translationRules;
}
public TranslationAgent(TranslationRules translationRules)
{
this.translationRules = translationRules;
}
public String deleteLinesStartingWithBadItems(String in)
{
String result = "";
for (String s : Strings.string2Lines(in))
{
boolean goodLine = true;
for (String bad : getTranslationRules().getDeleteLinesStartingWith())
{
if ((!bad.startsWith("^") && s.trim().startsWith(bad) || (bad.startsWith("^") && s.trim().matches(bad+"(.+)"))))
{
goodLine = false;
break;
}
}
if (goodLine)
{
result += s + "\n";
}
else
{
if (getTranslationRules().isCommentDeletedLines())
{
result += TranslationRules.COMMENT_DELETED_LINES_PREFIX + s + "\n";
}
}
}
return(result);
}
public String removeBadItems(String in)
{
String result = in;
for (String bad : getTranslationRules().getDelete())
{
result = result.replaceAll(bad,"");
}
return(result);
}
public String replaceBadItems(String in)
{
String result = in;
for (Map.Entry<String,String> rep : getTranslationRules().getReplace().entrySet())
{
result = result.replaceAll(rep.getKey(),rep.getValue());
}
return(result);
}
public String replaceBadItemsManyTimes(String in)
{
String result = in;
String exResult = "";
do
{
exResult = result;
for (Map.Entry<String,String> rep : getTranslationRules().getReplaceMany().entrySet())
{
result = result.replaceAll(rep.getKey(),rep.getValue());
}
}
while (!result.equals(exResult));
return(result);
}
public String translate(String in)
{
return(replaceBadItemsManyTimes(replaceBadItems(removeBadItems(deleteLinesStartingWithBadItems(in)))).trim().replaceAll("[\\t ]+\n","\n"));
}
public void translate(String inPath, String outPath) throws IOException
{
Files.string2File(translate(Files.file2String(inPath)), outPath);
}
public static void main(String args[]) throws IOException
{
System.out.println(new TranslationAgent(new MySQL2Oracle("mshj",true)).translate(Files.file2String(args[0])));
}
}
The String translate(String in) return the translation of its String input by calling successively deleteLinesStartingWithBadItems which deletes or comments the non-needed lines, removeBadItems which removes non-needed tokens, replaceBadItems which replaces tokens by their translations and finally replaceBadItemsManyTimes which perform unbounded number of replacement iterations.
The replaceBadItemsManyTimes performs as many replacement iterations as there should be, for example when translating inner foreign key index declarations in a MySQL script: each index will be detected in one iteration and thus there will be as many iterations as such index declarations.
The main method displays the result of the translation (from MySQL to Oracle SQL dialect) of the script at the path args[0].
(+) What is supported:
- Regular insert into, delete, drop, create, update, alter, select ... instructions- Constraints (foreign keys and uniqueness)
- Indexes
(-) What is not supported:
- Granting privileges- Creating users and schema
Off course all this can be improved. The aim was to rapidly create an incomplete yet useful translator using only replacement and Java Regular Expressions. A more complete solution could be achieved by using parsers (JavaCC).
