public override void Translate()
{
if (this.sourceDbInterpreter.DatabaseType == this.targetDbInterpreter.DatabaseType)
{
return;
}
if (this.hasError)
{
return;
}
this.LoadMappings();
SqlAnalyserBase sourceAnalyser = this.GetSqlAnalyser(this.sourceDbInterpreter.DatabaseType);
SqlAnalyserBase targetAnalyser = this.GetSqlAnalyser(this.targetDbInterpreter.DatabaseType);
Action <T, CommonScript> processTokens = (dbObj, script) =>
{
if (typeof(T) == typeof(Function))
{
AnalyseResult result = sourceAnalyser.AnalyseFunction(dbObj.Definition.ToUpper());
if (!result.HasError)
{
RoutineScript routine = result.Script as RoutineScript;
if (this.targetDbInterpreter.DatabaseType == DatabaseType.MySql && routine.ReturnTable != null)
{
routine.Type = RoutineType.PROCEDURE;
}
}
}
ScriptTokenProcessor tokenProcessor = new ScriptTokenProcessor(script, dbObj, this.sourceDbInterpreter, this.targetDbInterpreter);
tokenProcessor.UserDefinedTypes = this.UserDefinedTypes;
tokenProcessor.TargetDbOwner = this.TargetDbOwner;
tokenProcessor.Process();
dbObj.Definition = targetAnalyser.GenerateScripts(script);
};
foreach (T dbObj in this.scripts)
{
if (this.hasError)
{
break;
}
try
{
Type type = typeof(T);
this.FeedbackInfo($"Begin to translate {type.Name} \"{dbObj.Name}\".");
bool tokenProcessed = false;
this.Validate(dbObj);
string originalDefinition = dbObj.Definition;
AnalyseResult result = sourceAnalyser.Analyse <T>(originalDefinition.ToUpper());
CommonScript script = result.Script;
bool replaced = false;
if (result.HasError)
{
#region Special handle for view
if (typeof(T) == typeof(View))
{
//Currently, ANTLR can't parse some complex tsql accurately, so it uses general strategy.
if (this.sourceDbInterpreter.DatabaseType == DatabaseType.SqlServer)
{
ViewTranslator viewTranslator = new ViewTranslator(this.sourceDbInterpreter, this.targetDbInterpreter, new List <View>()
{
dbObj as View
}, this.TargetDbOwner)
{
SkipError = this.SkipError
};
viewTranslator.Translate();
replaced = true;
}
//Currently, ANTLR can't parse some view correctly, use procedure to parse it temporarily.
if (this.sourceDbInterpreter.DatabaseType == DatabaseType.Oracle)
{
string oldDefinition = dbObj.Definition;
int asIndex = oldDefinition.IndexOf(" AS ", StringComparison.OrdinalIgnoreCase);
StringBuilder sbNewDefinition = new StringBuilder();
sbNewDefinition.AppendLine($"CREATE OR REPLACE PROCEDURE {dbObj.Name} AS");
sbNewDefinition.AppendLine("BEGIN");
sbNewDefinition.AppendLine($"{oldDefinition.Substring(asIndex + 5).TrimEnd(';') + ";"}");
sbNewDefinition.AppendLine($"END {dbObj.Name};");
dbObj.Definition = sbNewDefinition.ToString();
AnalyseResult procResult = sourceAnalyser.Analyse <Procedure>(dbObj.Definition.ToUpper());
if (!procResult.HasError)
{
processTokens(dbObj, procResult.Script);
tokenProcessed = true;
dbObj.Definition = Regex.Replace(dbObj.Definition, " PROCEDURE ", " VIEW ", RegexOptions.IgnoreCase);
dbObj.Definition = Regex.Replace(dbObj.Definition, @"(BEGIN[\r][\n])|(END[\r][\n])", "", RegexOptions.IgnoreCase);
replaced = true;
}
}
}
#endregion
}
if (!result.HasError && !tokenProcessed)
{
processTokens(dbObj, script);
}
bool formatHasError = false;
string definition = this.ReplaceVariables(dbObj.Definition);
dbObj.Definition = definition; // this.FormatSql(definition, out formatHasError);
if (formatHasError)
{
dbObj.Definition = definition;
}
if (this.OnTranslated != null)
{
this.OnTranslated(this.targetDbInterpreter.DatabaseType, dbObj, new TranslateResult()
{
Error = result.Error, Data = dbObj.Definition
});
}
this.FeedbackInfo($"End translate {type.Name} \"{dbObj.Name}\", translate result: { (result.HasError ? "Error" : "OK") }.");
if (!replaced && result.HasError)
{
this.FeedbackError(this.ParseSqlSyntaxError(result.Error, originalDefinition).ToString(), this.SkipError);
if (!this.SkipError)
{
this.hasError = true;
}
}
}
catch (Exception ex)
{
var sce = new ScriptConvertException <T>(ex)
{
SourceServer = this.sourceDbInterpreter.ConnectionInfo.Server,
SourceDatabase = this.sourceDbInterpreter.ConnectionInfo.Database,
SourceObject = dbObj.Name,
TargetServer = this.targetDbInterpreter.ConnectionInfo.Server,
TargetDatabase = this.targetDbInterpreter.ConnectionInfo.Database,
TargetObject = dbObj.Name
};
if (!this.SkipError)
{
this.hasError = true;
throw sce;
}
else
{
this.FeedbackError(ExceptionHelper.GetExceptionDetails(ex), this.SkipError);
}
}
}
}
public string GenerateRoutineScripts(RoutineScript script)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine($"CREATE {script.Type.ToString()} {script.FullName}");
sb.AppendLine("(");
if (script.Parameters.Count > 0)
{
int i = 0;
foreach (Parameter parameter in script.Parameters)
{
ParameterType parameterType = parameter.ParameterType;
string strParameterType = "";
if (parameterType.HasFlag(ParameterType.IN) && parameterType.HasFlag(ParameterType.OUT))
{
strParameterType = "INOUT";
}
else if (parameterType != ParameterType.NONE)
{
strParameterType = parameterType.ToString();
}
sb.AppendLine($"{strParameterType} {parameter.Name} {parameter.DataType}{(i == script.Parameters.Count - 1 ? "" : ",")}");
i++;
}
}
sb.AppendLine(")");
if (script.Type == RoutineType.FUNCTION)
{
sb.AppendLine($"RETURNS {script.ReturnDataType}");
}
int beginIndex = sb.Length - 1;
bool hasLeaveStatement = false;
sb.AppendLine("BEGIN");
foreach (Statement statement in script.Statements.Where(item => item is DeclareStatement))
{
sb.AppendLine(this.BuildStatement(statement));
}
#region Cursor
Action appendDeclareCursor = () =>
{
foreach (Statement statement in script.Statements.Where(item => item is DeclareCursorStatement))
{
sb.AppendLine(this.BuildStatement(statement));
}
};
if (script.Statements.Any(item => item is OpenCursorStatement) && !script.Statements.Any(item => item is DeclareCursorHandlerStatement))
{
if (!script.Statements.Any(item => item is DeclareStatement && (item as DeclareStatement).Name.Symbol == "FINISHED"))
{
DeclareStatement declareStatement = new DeclareStatement()
{
Name = new TokenInfo("FINISHED")
{
Type = TokenType.VariableName
},
DataType = new TokenInfo("INT")
{
Type = TokenType.DataType
},
DefaultValue = new TokenInfo("0")
};
sb.AppendLine(this.BuildStatement(declareStatement));
}
appendDeclareCursor();
DeclareCursorHandlerStatement handler = new DeclareCursorHandlerStatement();
handler.Statements.Add(new SetStatement()
{
Key = new TokenInfo("FINISHED")
{
Type = TokenType.VariableName
}, Value = new TokenInfo("1")
});
sb.AppendLine(this.BuildStatement(handler));
}
else
{
appendDeclareCursor();
}
#endregion
if (script.ReturnTable != null)
{
sb.AppendLine(MySqlStatementScriptBuilder.BuildTemporaryTable(script.ReturnTable));
}
FetchCursorStatement fetchCursorStatement = null;
foreach (Statement statement in script.Statements.Where(item => !(item is DeclareStatement || item is DeclareCursorStatement)))
{
if (statement is FetchCursorStatement fetch)
{
fetchCursorStatement = fetch;
continue;
}
else if (statement is WhileStatement @while)
{
FetchCursorStatement fs = @while.Statements.FirstOrDefault(item => item is FetchCursorStatement) as FetchCursorStatement;
if (fetchCursorStatement != null && fs != null)
{
@while.Condition.Symbol = "FINISHED = 0";
if (fs.Variables.Count == 0)
{
@while.Statements.Insert(0, fetchCursorStatement);
}
}
}
if (statement is LeaveStatement)
{
hasLeaveStatement = true;
}
sb.AppendLine(this.BuildStatement(statement));
}
sb.AppendLine("END");
if (hasLeaveStatement)
{
sb.Insert(beginIndex, "sp:");
}
return(this.FormatScripts(sb.ToString()));
}
public string GenerateRoutineScripts(RoutineScript script)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine($"CREATE OR REPLACE {script.Type.ToString()} {script.FullName}");
if (script.Parameters.Count > 0)
{
sb.AppendLine("(");
int i = 0;
foreach (Parameter parameter in script.Parameters)
{
ParameterType parameterType = parameter.ParameterType;
string dataType = parameter.DataType.Symbol;
string strParameterType = "";
int parenthesesIndex = dataType.IndexOf("(");
if (parenthesesIndex > 0)
{
dataType = dataType.Substring(0, parenthesesIndex);
}
if (parameterType.HasFlag(ParameterType.IN) && parameterType.HasFlag(ParameterType.OUT))
{
strParameterType = "IN OUT";
}
else if (parameterType != ParameterType.NONE)
{
strParameterType = parameterType.ToString();
}
sb.AppendLine($"{parameter.Name} {strParameterType} {dataType}{(i == script.Parameters.Count - 1 ? "" : ",")}");
i++;
}
sb.AppendLine(")");
}
if (script.Type == RoutineType.FUNCTION)
{
if (script.ReturnDataType != null)
{
string dataType = script.ReturnDataType.Symbol;
if (DataTypeHelper.IsCharType(dataType))
{
DataTypeInfo dataTypeInfo = DataTypeHelper.GetDataTypeInfo(dataType);
dataType = dataTypeInfo.DataType;
}
sb.AppendLine($"RETURN {dataType}");
}
else if (script.ReturnTable != null)
{
//sb.AppendLine($"RETURN {script.ReturnTable}");
}
}
sb.AppendLine("AS");
foreach (Statement statement in script.Statements.Where(item => item is DeclareStatement || item is DeclareCursorStatement))
{
sb.Append(this.BuildStatement(statement).Replace("DECLARE ", ""));
}
sb.AppendLine("BEGIN");
if (script.ReturnTable != null)
{
}
FetchCursorStatement fetchCursorStatement = null;
foreach (Statement statement in script.Statements.Where(item => !(item is DeclareStatement || item is DeclareCursorStatement)))
{
if (statement is FetchCursorStatement fetch)
{
fetchCursorStatement = fetch;
continue;
}
else if (statement is WhileStatement @while)
{
FetchCursorStatement fs = @while.Statements.FirstOrDefault(item => item is FetchCursorStatement) as FetchCursorStatement;
if (fetchCursorStatement != null && fs != null)
{
@while.Condition.Symbol = "1=1";
if (fs.Variables.Count == 0)
{
@while.Statements.Insert(0, new LoopExitStatement()
{
Condition = new TokenInfo($"{fs.CursorName}%NOTFOUND")
});
@while.Statements.Insert(0, fetchCursorStatement);
}
}
}
sb.AppendLine(this.BuildStatement(statement));
}
sb.AppendLine($"END {script.FullName};");
return(this.FormatScripts(sb.ToString()));
}
public override AnalyseResult AnalyseProcedure(string content)
{
SqlSyntaxError error = null;
DdlStatementContext ddlStatement = this.GetDdlStatementContext(content, out error);
AnalyseResult result = new AnalyseResult()
{
Error = error
};
if (!result.HasError && ddlStatement != null)
{
RoutineScript script = new RoutineScript()
{
Type = RoutineType.PROCEDURE
};
CreateProcedureContext proc = ddlStatement.createProcedure();
if (proc != null)
{
#region Name
this.SetScriptName(script, proc.fullId());
#endregion
#region Parameters
ProcedureParameterContext[] parameters = proc.procedureParameter();
if (parameters != null)
{
foreach (ProcedureParameterContext parameter in parameters)
{
Parameter parameterInfo = new Parameter();
UidContext uid = parameter.uid();
parameterInfo.Name = new TokenInfo(uid)
{
Type = TokenType.ParameterName
};
parameterInfo.DataType = new TokenInfo(parameter.dataType().GetText())
{
Type = TokenType.DataType
};
this.SetParameterType(parameterInfo, parameter.children);
script.Parameters.Add(parameterInfo);
}
}
#endregion
#region Body
this.SetScriptBody(script, proc.routineBody());
#endregion
}
this.ExtractFunctions(script, ddlStatement);
result.Script = script;
}
return(result);
}
public string GenerateRoutineScripts(RoutineScript script)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine($"CREATE {script.Type.ToString()} {script.FullName}");
if (script.Parameters.Count > 0)
{
sb.AppendLine("(");
int i = 0;
foreach (Parameter parameter in script.Parameters)
{
ParameterType parameterType = parameter.ParameterType;
string strParameterType = "";
if (parameterType == ParameterType.IN)
{
strParameterType = "";
}
else if (parameterType.HasFlag(ParameterType.IN) && parameterType.HasFlag(ParameterType.OUT))
{
strParameterType = "OUT";
}
else if (parameterType != ParameterType.NONE)
{
strParameterType = parameterType.ToString();
}
string defaultValue = parameter.DefaultValue == null ? "" : "=" + parameter.DefaultValue;
sb.AppendLine($"{parameter.Name} {parameter.DataType} {defaultValue} {strParameterType}{(i == script.Parameters.Count - 1 ? "" : ",")}");
i++;
}
sb.AppendLine(")");
}
else if (script.Type == RoutineType.FUNCTION)
{
sb.AppendLine("(");
sb.AppendLine(")");
}
if (script.Type == RoutineType.FUNCTION)
{
if (script.ReturnTable == null)
{
sb.AppendLine($"RETURNS {script.ReturnDataType}");
}
else
{
sb.AppendLine($"RETURNS {script.ReturnTable.Name}({string.Join(",", script.ReturnTable.Columns.Select(t => $"{t.Name} {t.DataType}")) })");
}
}
sb.AppendLine("AS");
sb.AppendLine("BEGIN");
Action <IEnumerable <Statement> > appendStatements = (statements) =>
{
foreach (Statement statement in statements)
{
if (statement is WhileStatement @while)
{
FetchCursorStatement fetchCursorStatement = @while.Statements.FirstOrDefault(item => item is FetchCursorStatement) as FetchCursorStatement;
if (fetchCursorStatement != null && !statements.Any(item => item is FetchCursorStatement))
{
@while.Condition.Symbol = "@@FETCH_STATUS = 0";
sb.AppendLine(this.BuildStatement(fetchCursorStatement));
}
}
sb.AppendLine(this.BuildStatement(statement));
}
};
ExceptionStatement exceptionStatement = (ExceptionStatement)script.Statements.FirstOrDefault(item => item is ExceptionStatement);
if (exceptionStatement != null)
{
sb.AppendLine("BEGIN TRY");
appendStatements(script.Statements.Where(item => !(item is ExceptionStatement)));
sb.AppendLine("END TRY");
sb.AppendLine("BEGIN CATCH");
foreach (ExceptionItem exceptionItem in exceptionStatement.Items)
{
sb.AppendLine($"IF {exceptionItem.Name} = ERROR_PROCEDURE() OR {exceptionItem.Name} = ERROR_NUMBER()");
sb.AppendLine("BEGIN");
appendStatements(exceptionItem.Statements);
sb.AppendLine("END");
}
sb.AppendLine("END CATCH");
}
else
{
appendStatements(script.Statements);
}
sb.AppendLine("END");
return(this.FormatScripts(sb.ToString()));
}