public async Task AddCommand(Guid transactionId, TransactionCommandType type, string command)
{
var msg = new TransactionQueueMessage
{
TransactionId = transactionId,
Type = type,
Command = command
};
await _queue.PutRawMessageAsync(msg.ToJson());
}
public override StatementScriptBuilder Build(Statement statement, bool appendSeparator = true)
{
base.Build(statement, appendSeparator);
if (statement is SelectStatement select)
{
this.BuildSelectStatement(select, appendSeparator);
}
else if (statement is InsertStatement insert)
{
this.Append($"INSERT INTO {insert.TableName}", false);
if (insert.Columns.Count > 0)
{
this.AppendLine($"({ string.Join(",", insert.Columns.Select(item => item.ToString()))})");
}
if (insert.SelectStatements != null && insert.SelectStatements.Count > 0)
{
this.AppendChildStatements(insert.SelectStatements, true);
}
else
{
this.AppendLine($"VALUES({string.Join(",", insert.Values.Select(item => item))});");
}
}
else if (statement is UnionStatement union)
{
this.AppendLine(this.GetUnionTypeName(union.Type));
this.Build(union.SelectStatement);
}
else if (statement is UpdateStatement update)
{
List <TokenInfo> tableNames = new List <TokenInfo>();
#region Handle for mysql and plsql update join
if (update.TableNames.Count == 1)
{
string[] items = update.TableNames[0].Symbol.Split(' ');
if (items.Length > 1)
{
string alias = items[1];
bool added = false;
foreach (NameValueItem nameValue in update.SetItems)
{
if (nameValue.Name.Symbol.Trim().StartsWith(alias + ".", StringComparison.OrdinalIgnoreCase))
{
if (!added)
{
tableNames.Add(new TokenInfo(alias));
added = true;
}
if (nameValue.Value.Symbol?.IndexOf("SELECT", StringComparison.OrdinalIgnoreCase) > 0)
{
string from = nameof(TSqlParser.FROM);
string where = nameof(TSqlParser.WHERE);;
string oldValue = nameValue.Value.Symbol;
int fromIndex = oldValue.ToUpper().IndexOf(from);
nameValue.Value.Symbol = Regex.Replace(oldValue.Substring(0, fromIndex), "SELECT ", "", RegexOptions.IgnoreCase).Trim(' ', '(');
if (update.FromItems == null || update.FromItems.Count == 0)
{
update.FromItems = new List <FromItem>();
FromItem fromItem = new FromItem()
{
TableName = update.TableNames[0]
};
int whereIndex = oldValue.ToUpper().IndexOf(where);
string tableName = oldValue.Substring(fromIndex + from.Length, whereIndex - (fromIndex + from.Length) - 1);
string condition = oldValue.Substring(whereIndex + where.Length).Trim(')');
fromItem.JoinItems.Add(new JoinItem()
{
TableName = new TableName(tableName), Condition = new TokenInfo(condition)
});
update.FromItems.Add(fromItem);
}
}
}
}
}
}
#endregion
if (tableNames.Count == 0)
{
tableNames.AddRange(update.TableNames);
}
this.AppendLine($"UPDATE {string.Join(",", tableNames)} SET");
this.AppendLine(string.Join("," + Environment.NewLine + indent, update.SetItems.Select(item => $"{item.Name}={item.Value}")));
if (update.FromItems != null && update.FromItems.Count > 0)
{
int i = 0;
foreach (FromItem fromItem in update.FromItems)
{
if (i == 0)
{
this.AppendLine($"FROM {fromItem.TableName}");
}
foreach (JoinItem joinItem in fromItem.JoinItems)
{
string condition = joinItem.Condition == null ? "" : $" ON {joinItem.Condition}";
this.AppendLine($"{joinItem.Type} JOIN {joinItem.TableName}{condition}");
}
i++;
}
}
if (update.Condition != null && update.Condition.Symbol != null)
{
this.AppendLine($"WHERE {update.Condition}");
}
if (update.Option != null)
{
this.AppendLine(update.Option.ToString());
}
this.AppendLine(";");
}
else if (statement is DeleteStatement delete)
{
this.AppendLine($"DELETE FROM {delete.TableName}");
if (delete.Condition != null)
{
this.AppendLine($"WHERE {delete.Condition}");
}
this.AppendLine(";");
}
else if (statement is DeclareStatement declare)
{
if (declare.Type == DeclareType.Variable)
{
string defaultValue = (declare.DefaultValue == null ? "" : $" = {declare.DefaultValue}");
this.AppendLine($"DECLARE {declare.Name} {declare.DataType}{defaultValue};");
}
else if (declare.Type == DeclareType.Table)
{
this.AppendLine($"DECLARE {declare.Name} TABLE (");
int i = 0;
foreach (var column in declare.Table.Columns)
{
this.AppendLine($"{column.Name} {column.DataType}{(i == declare.Table.Columns.Count - 1 ? "" : ",")}");
}
this.AppendLine(")");
}
}
else if (statement is SetStatement set)
{
if (set.Key != null && set.Value != null)
{
TokenInfo valueToken = set.Value;
if (valueToken != null)
{
if (valueToken.Type == TokenType.RoutineName)
{
this.MakeupRoutineName(valueToken);
}
else
{
TokenInfo child = valueToken.Tokens.FirstOrDefault(item => item.Type == TokenType.RoutineName);
if (child != null)
{
this.MakeupRoutineName(valueToken);
}
}
}
this.AppendLine($"SET {set.Key } = {set.Value };");
}
}
if (statement is IfStatement @if)
{
foreach (IfStatementItem item in @if.Items)
{
if (item.Type == IfStatementType.IF)
{
this.AppendLine($"{item.Type} {item.Condition}");
}
else
{
this.AppendLine($"{item.Type}");
}
this.AppendLine("BEGIN");
if (item.Statements.Count > 0)
{
this.AppendChildStatements(item.Statements, true);
}
else
{
this.AppendLine("PRINT('BLANK!');");
}
this.AppendLine("END");
}
}
else if (statement is CaseStatement @case)
{
string variableName = @case.VariableName.ToString();
IfStatement ifStatement = new IfStatement();
int i = 0;
foreach (var item in @case.Items)
{
IfStatementItem ifItem = new IfStatementItem();
ifItem.Type = i == 0 ? IfStatementType.IF : item.Type;
if (item.Type != IfStatementType.ELSE)
{
ifItem.Condition = new TokenInfo($"{variableName}={item.Condition}")
{
Type = TokenType.Condition
};
}
i++;
}
this.Build(ifStatement);
}
else if (statement is LoopStatement loop)
{
this.AppendLine($"WHILE {loop.Condition}");
this.AppendLine("BEGIN");
this.AppendChildStatements(loop.Statements, true);
this.AppendLine("END");
}
else if (statement is WhileStatement @while)
{
this.AppendLine($"WHILE { @while.Condition }");
this.AppendLine("BEGIN");
this.AppendChildStatements(@while.Statements, true);
this.AppendLine("END");
}
else if (statement is LoopExitStatement whileExit)
{
this.AppendLine($"IF {whileExit.Condition}");
this.AppendLine("BEGIN");
this.AppendLine("BREAK");
this.AppendLine("END");
}
else if (statement is TryCatchStatement tryCatch)
{
this.AppendLine("BEGIN TRY");
this.AppendChildStatements(tryCatch.TryStatements, true);
this.AppendLine("END TRY");
this.AppendLine("BEGIN CATCH");
this.AppendChildStatements(tryCatch.CatchStatements, true);
this.AppendLine("END CATCH");
}
else if (statement is ReturnStatement @return)
{
this.AppendLine($"RETURN {@return.Value};");
}
else if (statement is PrintStatement print)
{
this.AppendLine($"PRINT {print.Content.Symbol?.Replace("||", "+")};");
}
else if (statement is CallStatement execute)
{
this.AppendLine($"EXECUTE {execute.Name} {string.Join(",", execute.Arguments)};");
}
else if (statement is TransactionStatement transaction)
{
TransactionCommandType commandType = transaction.CommandType;
switch (commandType)
{
case TransactionCommandType.BEGIN:
this.AppendLine("BEGIN TRANS");
break;
case TransactionCommandType.COMMIT:
this.AppendLine("COMMIT");
break;
case TransactionCommandType.ROLLBACK:
this.AppendLine("ROLLBACK");
break;
}
}
else if (statement is LeaveStatement leave)
{
this.AppendLine("RETURN;");
}
else if (statement is DeclareCursorStatement declareCursor)
{
this.AppendLine($"DECLARE {declareCursor.CursorName} CURSOR FOR");
this.Build(declareCursor.SelectStatement);
}
else if (statement is OpenCursorStatement openCursor)
{
this.AppendLine($"OPEN {openCursor.CursorName}");
}
else if (statement is FetchCursorStatement fetchCursor)
{
this.AppendLine($"FETCH NEXT FROM {fetchCursor.CursorName} INTO {string.Join(",", fetchCursor.Variables)}");
}
else if (statement is CloseCursorStatement closeCursor)
{
this.AppendLine($"CLOSE {closeCursor.CursorName}");
if (closeCursor.IsEnd)
{
this.AppendLine($"DEALLOCATE {closeCursor.CursorName}");
}
}
else if (statement is DeallocateCursorStatement deallocateCursor)
{
this.AppendLine($"DEALLOCATE {deallocateCursor.CursorName}");
}
else if (statement is TruncateStatement truncate)
{
this.AppendLine($"TRUNCATE TABLE {truncate.TableName}");
}
return(this);
}
public override StatementScriptBuilder Build(Statement statement, bool appendSeparator = true)
{
base.Build(statement, appendSeparator);
if (statement is SelectStatement select)
{
this.BuildSelectStatement(select, appendSeparator);
}
else if (statement is UnionStatement union)
{
this.AppendLine(this.GetUnionTypeName(union.Type));
this.Build(union.SelectStatement);
}
else if (statement is InsertStatement insert)
{
this.AppendLine($"INSERT INTO {insert.TableName}");
if (insert.Columns.Count > 0)
{
this.AppendLine($"({ string.Join(",", insert.Columns.Select(item => item.ToString()))})");
}
if (insert.SelectStatements != null && insert.SelectStatements.Count > 0)
{
this.AppendChildStatements(insert.SelectStatements, true);
}
else
{
this.AppendLine($"VALUES({string.Join(",", insert.Values.Select(item => item))});");
}
}
else if (statement is UpdateStatement update)
{
this.AppendLine($"UPDATE");
List <TokenInfo> tableNames = new List <TokenInfo>();
if (update.FromItems != null)
{
int i = 0;
foreach (FromItem fromItem in update.FromItems)
{
if (i == 0 && fromItem.TableName != null)
{
tableNames.Add(fromItem.TableName);
}
List <JoinItem> usedJoinItems = new List <JoinItem>();
foreach (NameValueItem nameValue in update.SetItems)
{
string[] ids = nameValue.Value.Symbol.Split('.').Select(item => item.Trim()).ToArray();
string tableName = ids[0];
JoinItem joinItem = fromItem.JoinItems.FirstOrDefault(item => item.TableName.Name?.Symbol?.ToUpper().Trim('"') == tableName.ToUpper() ||
item.TableName.Alias?.Symbol?.ToUpper()?.Trim('"') == tableName.ToUpper());
if (joinItem != null)
{
usedJoinItems.Add(joinItem);
string condition = joinItem.Condition == null ? "" : $" WHERE {joinItem.Condition}";
nameValue.Value = new TokenInfo($"(SELECT {nameValue.Name} FROM {joinItem.TableName}{condition})");
}
}
var otherJoinItems = fromItem.JoinItems.Where(item => !usedJoinItems.Contains(item));
if (otherJoinItems != null && otherJoinItems.Count() > 0)
{
foreach (var otherJoinItem in otherJoinItems)
{
//TODO
}
}
i++;
}
}
if (tableNames.Count == 0 && update.TableNames.Count > 0)
{
tableNames.AddRange(update.TableNames);
}
this.Append($" {string.Join(",", tableNames)}", false);
this.AppendLine("SET");
this.AppendLine(string.Join("," + Environment.NewLine + indent, update.SetItems.Select(item => $"{item.Name}={item.Value}")));
if (update.Condition != null && update.Condition.Symbol != null)
{
this.AppendLine($"WHERE {update.Condition}");
}
this.AppendLine(";");
}
else if (statement is DeleteStatement delete)
{
this.AppendLine($"DELETE {delete.TableName}");
if (delete.Condition != null)
{
this.AppendLine($"WHERE {delete.Condition}");
}
this.AppendLine(";");
}
else if (statement is DeclareStatement declare)
{
if (declare.Type == DeclareType.Variable)
{
string defaultValue = (declare.DefaultValue == null ? "" : $" :={declare.DefaultValue}");
this.AppendLine($"DECLARE {declare.Name} {declare.DataType}{defaultValue};");
}
else if (declare.Type == DeclareType.Table)
{
}
}
else if (statement is IfStatement @if)
{
foreach (IfStatementItem item in @if.Items)
{
if (item.Type == IfStatementType.IF || item.Type == IfStatementType.ELSEIF)
{
this.AppendLine($"{item.Type} {item.Condition} THEN");
}
else
{
this.AppendLine($"{item.Type}");
}
this.AppendLine("BEGIN");
this.AppendChildStatements(item.Statements, true);
this.AppendLine("END;");
}
this.AppendLine("END IF;");
}
else if (statement is CaseStatement @case)
{
this.AppendLine($"CASE {@case.VariableName}");
foreach (IfStatementItem item in @case.Items)
{
if (item.Type != IfStatementType.ELSE)
{
this.AppendLine($"WHEN {item.Condition} THEN");
}
else
{
this.AppendLine("ELSE");
}
this.AppendLine("BEGIN");
this.AppendChildStatements(item.Statements, true);
this.AppendLine("END;");
}
this.AppendLine("END CASE;");
}
else if (statement is SetStatement set)
{
if (set.Key != null && set.Value != null)
{
this.AppendLine($"{set.Key } := {set.Value };");
}
}
else if (statement is LoopStatement loop)
{
if (loop.Type == LoopType.LOOP)
{
this.AppendLine("LOOP");
}
else
{
this.AppendLine($"{loop.Type.ToString()} {loop.Condition} LOOP");
}
this.AppendChildStatements(loop.Statements, true);
this.AppendLine("END LOOP;");
}
else if (statement is LoopExitStatement loopExit)
{
this.AppendLine($"EXIT WHEN {loopExit.Condition};");
}
else if (statement is WhileStatement @while)
{
LoopStatement loopStatement = @while as LoopStatement;
this.AppendLine($"WHILE {@while.Condition} LOOP");
this.AppendChildStatements(@while.Statements, true);
this.AppendLine("END LOOP;");
}
else if (statement is ReturnStatement @return)
{
this.AppendLine($"RETURN {@return.Value};");
}
else if (statement is PrintStatement print)
{
this.AppendLine($"DBMS_OUTPUT.PUT_LINE({print.Content.Symbol?.ToString()?.Replace("+", "||")});");
}
else if (statement is CallStatement execute)
{
this.AppendLine($"{execute.Name}({string.Join(",", execute.Arguments.Select(item => item.Symbol?.Split('=')?.LastOrDefault()))});");
}
else if (statement is TransactionStatement transaction)
{
TransactionCommandType commandType = transaction.CommandType;
switch (commandType)
{
case TransactionCommandType.COMMIT:
this.AppendLine("COMMIT;");
break;
case TransactionCommandType.ROLLBACK:
this.AppendLine("ROLLBACK;");
break;
}
}
else if (statement is LeaveStatement leave)
{
this.AppendLine("RETURN;");
}
else if (statement is TryCatchStatement tryCatch)
{
this.AppendLine("EXCEPTION");
this.AppendLine("BEGIN");
this.AppendChildStatements(tryCatch.CatchStatements, true);
this.AppendLine("END;");
this.AppendChildStatements(tryCatch.TryStatements, true);
}
else if (statement is ExceptionStatement exception)
{
this.AppendLine("EXCEPTION");
foreach (ExceptionItem exceptionItem in exception.Items)
{
this.AppendLine($"WHEN {exceptionItem.Name} THEN");
this.AppendLine("BEGIN");
this.AppendChildStatements(exceptionItem.Statements, true);
this.AppendLine("END;");
}
}
else if (statement is DeclareCursorStatement declareCursor)
{
this.AppendLine($"DECLARE CURSOR {declareCursor.CursorName} IS");
this.Build(declareCursor.SelectStatement);
}
else if (statement is OpenCursorStatement openCursor)
{
this.AppendLine($"OPEN {openCursor.CursorName};");
}
else if (statement is FetchCursorStatement fetchCursor)
{
if (fetchCursor.Variables.Count > 0)
{
this.AppendLine($"FETCH {fetchCursor.CursorName} INTO {string.Join(",", fetchCursor.Variables)};");
}
}
else if (statement is CloseCursorStatement closeCursor)
{
this.AppendLine($"CLOSE {closeCursor.CursorName};");
}
else if (statement is TruncateStatement truncate)
{
this.AppendLine($"TRUNCATE TABLE {truncate.TableName};");
}
return(this);
}
public override StatementScriptBuilder Build(Statement statement, bool appendSeparator = true)
{
base.Build(statement, appendSeparator);
if (statement is SelectStatement select)
{
this.BuildSelectStatement(select, appendSeparator);
}
else if (statement is UnionStatement union)
{
this.AppendLine(this.GetUnionTypeName(union.Type));
this.Build(union.SelectStatement);
}
else if (statement is InsertStatement insert)
{
this.Append($"INSERT INTO {insert.TableName}");
if (insert.Columns.Count > 0)
{
this.AppendLine($"({ string.Join(",", insert.Columns.Select(item => item.ToString()))})");
}
if (insert.SelectStatements != null && insert.SelectStatements.Count > 0)
{
this.AppendChildStatements(insert.SelectStatements, true);
}
else
{
this.AppendLine($"VALUES({string.Join(",", insert.Values.Select(item => item))});");
}
}
else if (statement is UpdateStatement update)
{
this.Append($"UPDATE");
List <TokenInfo> tableNames = new List <TokenInfo>();
if (update.FromItems != null)
{
tableNames.Add(update.FromItems.First().TableName);
}
else if (update.TableNames.Count > 0)
{
tableNames.AddRange(update.TableNames);
}
this.Append($" {string.Join(",", tableNames)}");
if (update.FromItems != null && update.FromItems.Count > 0)
{
int i = 0;
foreach (FromItem fromItem in update.FromItems)
{
if (fromItem.TableName != null && i > 0)
{
this.AppendLine($" {fromItem.TableName}");
}
foreach (JoinItem joinItem in fromItem.JoinItems)
{
string condition = joinItem.Condition == null ? "" : $" ON {joinItem.Condition}";
this.AppendLine($"{joinItem.Type} JOIN {joinItem.TableName}{condition}");
}
i++;
}
}
this.AppendLine("SET");
this.AppendLine(string.Join("," + Environment.NewLine + indent, update.SetItems.Select(item => $"{item.Name}={item.Value}")));
if (update.Condition != null && update.Condition.Symbol != null)
{
this.AppendLine($"WHERE {update.Condition}");
}
this.AppendLine(";");
}
else if (statement is DeleteStatement delete)
{
this.AppendLine($"DELETE FROM {delete.TableName}");
if (delete.Condition != null)
{
this.AppendLine($"WHERE {delete.Condition}");
}
this.AppendLine(";");
}
else if (statement is DeclareStatement declare)
{
if (declare.Type == DeclareType.Variable)
{
string defaultValue = (declare.DefaultValue == null ? "" : $" DEFAULT {declare.DefaultValue}");
this.AppendLine($"DECLARE {declare.Name} {declare.DataType}{defaultValue};");
}
else if (declare.Type == DeclareType.Table)
{
this.AppendLine(BuildTemporaryTable(declare.Table));
}
}
else if (statement is IfStatement @if)
{
foreach (IfStatementItem item in @if.Items)
{
if (item.Type == IfStatementType.IF || item.Type == IfStatementType.ELSEIF)
{
this.AppendLine($"{item.Type} {item.Condition} THEN");
}
else
{
this.AppendLine($"{item.Type}");
}
this.AppendLine("BEGIN");
this.AppendChildStatements(item.Statements, true);
this.AppendLine("END;");
}
this.AppendLine("END IF;");
}
else if (statement is CaseStatement @case)
{
this.AppendLine($"CASE {@case.VariableName}");
foreach (IfStatementItem item in @case.Items)
{
if (item.Type != IfStatementType.ELSE)
{
this.AppendLine($"WHEN {item.Condition} THEN");
}
else
{
this.AppendLine("ELSE");
}
this.AppendLine("BEGIN");
this.AppendChildStatements(item.Statements, true);
this.AppendLine("END;");
}
this.AppendLine("END CASE;");
}
else if (statement is SetStatement set)
{
if (set.Key != null && set.Value != null)
{
this.AppendLine($"SET {set.Key } = {set.Value };");
}
}
else if (statement is LoopStatement loop)
{
TokenInfo name = loop.Name;
if (loop.Type != LoopType.LOOP)
{
if (loop.Type == LoopType.LOOP)
{
this.AppendLine($"WHILE 1=1 DO");
}
else
{
this.AppendLine($"WHILE {loop.Condition} DO");
}
}
else
{
this.AppendLine("LOOP");
}
this.AppendLine("BEGIN");
this.AppendChildStatements(loop.Statements, true);
this.AppendLine("END;");
if (loop.Type != LoopType.LOOP)
{
this.AppendLine($"END WHILE;");
}
else
{
this.AppendLine($"END LOOP {(name == null ? "" : name + ":")};");
}
}
else if (statement is WhileStatement @while)
{
this.AppendLine($"WHILE {@while.Condition} DO");
this.AppendChildStatements(@while.Statements, true);
this.AppendLine("END WHILE;");
}
else if (statement is LoopExitStatement whileExit)
{
this.AppendLine($"IF {whileExit.Condition} THEN");
this.AppendLine("BEGIN");
this.AppendLine("BREAK;");
this.AppendLine("END;");
this.AppendLine("END IF;");
}
else if (statement is ReturnStatement @return)
{
this.AppendLine($"RETURN {@return.Value};");
}
else if (statement is PrintStatement print)
{
this.AppendLine($"SELECT {print.Content.Symbol?.Replace("||", "+")};");
}
else if (statement is CallStatement call)
{
this.AppendLine($"CALL {call.Name}({string.Join(",", call.Arguments.Select(item => item.Symbol?.Split('=')?.LastOrDefault()))});");
}
else if (statement is TransactionStatement transaction)
{
TransactionCommandType commandType = transaction.CommandType;
switch (commandType)
{
case TransactionCommandType.BEGIN:
this.AppendLine("START TRANSACTION;");
break;
case TransactionCommandType.COMMIT:
this.AppendLine("COMMIT;");
break;
case TransactionCommandType.ROLLBACK:
this.AppendLine("ROLLBACK;");
break;
}
}
else if (statement is LeaveStatement leave)
{
this.AppendLine("LEAVE sp;");
}
else if (statement is TryCatchStatement tryCatch)
{
this.AppendLine("DECLARE EXIT HANDLER FOR 1 #[REPLACE ERROR CODE HERE]");
this.AppendLine("BEGIN");
this.AppendChildStatements(tryCatch.CatchStatements, true);
this.AppendLine("END;");
this.AppendChildStatements(tryCatch.TryStatements, true);
}
else if (statement is ExceptionStatement exception)
{
foreach (ExceptionItem exceptionItem in exception.Items)
{
this.AppendLine($"DECLARE EXIT HANDLER FOR {exceptionItem.Name}");
this.AppendLine("BEGIN");
this.AppendChildStatements(exceptionItem.Statements, true);
this.AppendLine("END;");
}
}
else if (statement is DeclareCursorStatement declareCursor)
{
this.AppendLine($"DECLARE {declareCursor.CursorName} CURSOR FOR");
this.Build(declareCursor.SelectStatement);
}
else if (statement is DeclareCursorHandlerStatement declareCursorHandler)
{
this.AppendLine($"DECLARE CONTINUE HANDLER");
this.AppendLine($"FOR NOT FOUND");
this.AppendLine($"BEGIN");
this.AppendChildStatements(declareCursorHandler.Statements, true);
this.AppendLine($"END;");
}
else if (statement is OpenCursorStatement openCursor)
{
this.AppendLine($"OPEN {openCursor.CursorName};");
}
else if (statement is FetchCursorStatement fetchCursor)
{
if (fetchCursor.Variables.Count > 0)
{
this.AppendLine($"FETCH {fetchCursor.CursorName} INTO {string.Join(",", fetchCursor.Variables)};");
}
}
else if (statement is CloseCursorStatement closeCursor)
{
this.AppendLine($"CLOSE {closeCursor.CursorName};");
}
else if (statement is TruncateStatement truncate)
{
this.AppendLine($"TRUNCATE TABLE {truncate.TableName};");
}
return(this);
}