Wednesday, January 15, 2014

SQL Server to Oracle DDL BeanShell Script

I couldn't find any script to convert a relatively large SQL Server Database (just tables) to Oracle SQL DDL in a way that all the identifiers (table names, column names, constraint names) were reduced to Oracle's max of 30 characters. So I came up with the following. The original identifiers had underscores to separate words, the script choose the last or second to last underscore-separated word and appends a number for uniqueness. Run in jEdit.


// This is a jEdit macro
// only run once on output of SQL Server Generate Scripts wizard with
// all options set False other than Script -Create, -USE DATABASE, -Foreign Keys, -Primary Keys
// Save as .bsh and place in %AppData%/jEdit/macros

import java.util.regex.*;

SearchAndReplace.setSearchString("\\[(.+?)\\]");
SearchAndReplace.setReplaceString("$1");
SearchAndReplace.setBeanShellReplace(false);
SearchAndReplace.setWholeWord(false);
SearchAndReplace.setIgnoreCase(true);
SearchAndReplace.setRegexp(true);
SearchAndReplace.setSearchFileSet(new CurrentBufferSet());
SearchAndReplace.replaceAll(view);

SearchAndReplace.setSearchString("varchar(max)");
SearchAndReplace.setReplaceString("CLOB");
SearchAndReplace.setBeanShellReplace(false);
SearchAndReplace.setWholeWord(false);
SearchAndReplace.setIgnoreCase(true);
SearchAndReplace.setRegexp(false);
SearchAndReplace.setSearchFileSet(new CurrentBufferSet());
SearchAndReplace.replaceAll(view);

SearchAndReplace.setSearchString("(\\s)ASC(\\b)");
SearchAndReplace.setReplaceString("$1$2");
SearchAndReplace.setBeanShellReplace(false);
SearchAndReplace.setWholeWord(false);
SearchAndReplace.setIgnoreCase(true);
SearchAndReplace.setRegexp(true);
SearchAndReplace.setSearchFileSet(new CurrentBufferSet());
SearchAndReplace.replaceAll(view);

SearchAndReplace.setSearchString("SET ANSI_NULLS ON\\nGO\\nSET QUOTED_IDENTIFIER ON\\nGO\\n");
SearchAndReplace.setReplaceString("");
SearchAndReplace.setBeanShellReplace(false);
SearchAndReplace.setWholeWord(false);
SearchAndReplace.setIgnoreCase(true);
SearchAndReplace.setRegexp(true);
SearchAndReplace.setSearchFileSet(new CurrentBufferSet());
SearchAndReplace.replaceAll(view);

SearchAndReplace.setSearchString("GO");
SearchAndReplace.setReplaceString(";");
SearchAndReplace.setBeanShellReplace(false);
SearchAndReplace.setWholeWord(false);
SearchAndReplace.setIgnoreCase(true);
SearchAndReplace.setRegexp(false);
SearchAndReplace.setSearchFileSet(new CurrentBufferSet());
SearchAndReplace.replaceAll(view);

SearchAndReplace.setSearchString("WITH \\([^\\)]+\\)");
SearchAndReplace.setReplaceString("");
SearchAndReplace.setBeanShellReplace(false);
SearchAndReplace.setWholeWord(false);
SearchAndReplace.setIgnoreCase(true);
SearchAndReplace.setRegexp(true);
SearchAndReplace.setSearchFileSet(new CurrentBufferSet());
SearchAndReplace.replaceAll(view);

SearchAndReplace.setSearchString("CLUSTERED");
SearchAndReplace.setReplaceString("");
SearchAndReplace.setBeanShellReplace(false);
SearchAndReplace.setWholeWord(false);
SearchAndReplace.setIgnoreCase(true);
SearchAndReplace.setRegexp(false);
SearchAndReplace.setSearchFileSet(new CurrentBufferSet());
SearchAndReplace.replaceAll(view);
SearchAndReplace.setSearchString(" ON PRIMARY");
SearchAndReplace.setReplaceString("");
SearchAndReplace.setBeanShellReplace(false);
SearchAndReplace.setWholeWord(false);
SearchAndReplace.setIgnoreCase(true);
SearchAndReplace.setRegexp(false);
SearchAndReplace.setSearchFileSet(new CurrentBufferSet());
SearchAndReplace.replaceAll(view);
SearchAndReplace.setSearchString(" WITH CHECK");
SearchAndReplace.setReplaceString("");
SearchAndReplace.setBeanShellReplace(false);
SearchAndReplace.setWholeWord(false);
SearchAndReplace.setIgnoreCase(true);
SearchAndReplace.setRegexp(false);
SearchAndReplace.setSearchFileSet(new CurrentBufferSet());
SearchAndReplace.replaceAll(view);
SearchAndReplace.setSearchString("tinyint");
SearchAndReplace.setReplaceString("int");
SearchAndReplace.setBeanShellReplace(false);
SearchAndReplace.setWholeWord(false);
SearchAndReplace.setIgnoreCase(true);
SearchAndReplace.setRegexp(false);
SearchAndReplace.setSearchFileSet(new CurrentBufferSet());
SearchAndReplace.replaceAll(view);

SearchAndReplace.setSearchString("ALTER TABLE .+? CHECK CONSTRAINT .+?\\n.+?\\n");
SearchAndReplace.setReplaceString("");
SearchAndReplace.setBeanShellReplace(false);
SearchAndReplace.setWholeWord(false);
SearchAndReplace.setIgnoreCase(true);
SearchAndReplace.setRegexp(true);
SearchAndReplace.setSearchFileSet(new CurrentBufferSet());
SearchAndReplace.replaceAll(view);

String text = textArea.getText();
String pattern = "ADD\\s+CONSTRAINT FK_\\w+?\\sFOREIGN KEY";
Matcher matcher = Pattern.compile(pattern).matcher(text);
StringBuffer sb = new StringBuffer();
int i = 1;
while (matcher.find()) {
 matcher.appendReplacement(sb, "ADD CONSTRAINT FK_" + (i++) + " FOREIGN KEY");
}
matcher.appendTail(sb);

text = sb.toString();

pattern = "CREATE\\s+TABLE (\\w+?)\\(";
matcher = Pattern.compile(pattern).matcher(text);
int i = 1;
ArrayList toReplace = new ArrayList();
ArrayList replacements = new ArrayList();
while (matcher.find()) {
 if (matcher.group(1).length() >= 30) {
  String[] split =  matcher.group(1).split("_");
  int splitIndex = split.length-2 < 0 ? 0 : split.length-2;
  String replacement = split[splitIndex] + (i++);
  toReplace.add(matcher.group(1));
  replacements.add(replacement);
 }
}
for (int j = 0; j < toReplace.size(); j++) {
 text = text.replaceAll(toReplace.get(j), replacements.get(j));
}

pattern = "\\W(\\w+?)\\sint\\sNOT\\sNULL";
matcher = Pattern.compile(pattern).matcher(text);
i = 1;
toReplace = new ArrayList();
replacements = new ArrayList();
while (matcher.find()) {
 if (matcher.group(1).length() >= 30) {
  String[] split =  matcher.group(1).split("_");
  int splitIndex = split.length-1 < 0 ? 0 : split.length-1;
  String replacement = split[splitIndex] + (i++);
  toReplace.add(matcher.group(1));
  replacements.add(replacement);
 }
}
for (int j = 0; j < toReplace.size(); j++) {
 text = text.replaceAll(toReplace.get(j), replacements.get(j));
}

textArea.setText(text);


No comments:

Post a Comment