private static Row CursorFetch(FetchCursorStatement fetch, Cursor cursor, Scope scope)
{
var orientation = fetch.FetchType?.Orientation ?? FetchOrientation.None;
switch (orientation)
{
case FetchOrientation.None:
case FetchOrientation.Next:
return(cursor.MoveNext());
case FetchOrientation.Prior:
return(cursor.MovePrior());
case FetchOrientation.First:
return(cursor.MoveFirst());
case FetchOrientation.Last:
return(cursor.MoveLast());
case FetchOrientation.Absolute:
return(cursor.MoveAbsolute(Evaluate <int>(fetch.FetchType?.RowOffset, NullArgument.It, scope)));
case FetchOrientation.Relative:
return(cursor.MoveRelative(Evaluate <int>(fetch.FetchType?.RowOffset, NullArgument.It, scope)));
default:
throw FeatureNotSupportedException.Value(orientation);
}
}
public FetchCursorStatement ParseFetchCursorSatement(FetchCursorContext node)
{
FetchCursorStatement statement = new FetchCursorStatement();
statement.CursorName = new TokenInfo(node.uid())
{
Type = TokenType.CursorName
};
statement.Variables.AddRange(node.uidList().uid().Select(item => new TokenInfo(item)
{
Type = TokenType.VariableName
}));
return(statement);
}
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 override void Visit(FetchCursorStatement node) { this.action(node); }
public override void ExplicitVisit(FetchCursorStatement fragment)
{
_fragments.Add(fragment);
}
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 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()));
}