public ISyntaxNode Visit(TSqlFragment node, TSqlFragment parent, string sourceProperty, ISyntaxNode result)
{
DeleteSpecification delete = node as DeleteSpecification;
if (delete == null)
{
return(result);
}
StatementNode statement = new StatementNode()
{
Parent = result,
Fragment = node,
ParentFragment = parent,
TargetProperty = sourceProperty
};
if (result is ScriptNode script)
{
if (parent is DeleteStatement)
{
script.Statements.Add(statement);
return(statement);
}
}
return(result);
}
public override void Visit(DeleteSpecification node)
{
if (node.WhereClause != null)
{
return;
}
errorCallback(RULE_NAME, RULE_TEXT, node.StartLine, GetColumnNumber(node));
}
public override void Visit(DeleteSpecification node)
{
base.Visit(node);
if (node.WhereClause == null)
{
AddFinding(node, "DELETE without WHERE clause.");
}
}
public static EngineResult Evaluate(DataModificationSpecification dml, Scope scope)
{
IOutputSink sink;
// TODO: support scalar expressions in TableOutputSink, not just column names
// how to handle INSERTED. and DELETED. aliases?
if (dml.OutputClause != null || dml.OutputIntoClause != null)
{
sink = new TableOutputSink(
(dml.OutputClause?.SelectColumns ?? dml.OutputIntoClause?.SelectColumns)?
.Select(s => new Column
{
Name = ((ColumnReferenceExpression)((SelectScalarExpression)s).Expression)
.MultiPartIdentifier.Identifiers.Select(x => x.Value).ToArray(),
Type = DbType.AnsiString
}).ToList());
}
else
{
sink = new NullOutputSink();
}
var result = dml switch
{
InsertSpecification insert => Evaluate(insert, sink, scope),
MergeSpecification merge => Evaluate(merge, sink, scope),
DeleteSpecification delete => Evaluate(delete, sink, scope),
UpdateSpecification update => Evaluate(update, sink, scope),
_ => throw FeatureNotSupportedException.Subtype(dml)
};
if (dml.OutputIntoClause != null)
{
var(table, scope2) = Evaluate(dml.OutputIntoClause.IntoTable, null, scope);
Evaluate(
table,
dml.OutputIntoClause.IntoTableColumns,
((TableOutputSink)sink).Output,
new NullOutputSink(),
scope2);
}
return(dml.OutputClause != null
? new EngineResult(((TableOutputSink)sink).Output)
: result);
}
}
public override void Visit(DeleteSpecification node)
{
WhereClause lol = node.WhereClause;
if (lol != null)
{
if (!GetNodeTokenText(lol).ToLower().Contains("select"))
{
seperate(GetNodeTokenText(lol).ToLower().Replace("where", ""));
}
else
{
seperate(GetNodeTokenText(lol).ToLower().Substring(6, GetNodeTokenText(lol).Length - 6));
}
}
}
public static EngineResult Evaluate(DeleteSpecification delete, IOutputSink sink, Scope scope)
{
var tableRef = (NamedTableReference)delete.Target;
var table = scope.Env.Tables[tableRef.SchemaObject.BaseIdentifier.Value];
var rowCount = 0;
foreach (var row in table.Rows.ToList())
{
if (delete.WhereClause == null ||
Evaluate(delete.WhereClause.SearchCondition, new RowArgument(row), scope))
{
table.Rows.Remove(row);
rowCount++;
}
sink.Deleted(row, scope.Env);
}
scope.Env.RowCount = rowCount;
return(new EngineResult(rowCount));
}
protected override object InternalVisit(DeleteSpecification node)
{
//TODO:node.FromClause;
//TODO:node.OutputIntoClause;
var env = Database.GlobalEnvironment.NewChild();
var table = (Table)Visit <IResultTable>(node.Target);
var top = EvaluateExpression <TopResult>(node.TopRowFilter, env);
var predicate = EvaluateExpression <Func <Row, bool> >(node.WhereClause, env, row => true);
var rows = Filter.From(table.Rows, predicate, top).ToArray();
foreach (Row item in rows)
{
// TODO(Richo): What happens if one of these throws an error?
item.Delete();
}
table.AcceptChanges();
return(new SQLExecutionResult(rows.Count(),
ApplyOutputClause(new RecordTable("DELETED", table.Columns, rows), node.OutputClause)));
}
private string buildChunkedDelete(DeleteSpecification delete)
{
var counter = new DeclareVariableStatement();
var counterVariable = new DeclareVariableElement();
counterVariable.DataType = new SqlDataTypeReference() {SqlDataTypeOption = SqlDataTypeOption.Int};
counterVariable.VariableName = new Identifier() {Value = "@rowcount"};
counterVariable.Value = new IntegerLiteral() {Value = "10000"};
counter.Declarations.Add(counterVariable);
delete.TopRowFilter = new TopRowFilter();
delete.TopRowFilter.Expression = new ParenthesisExpression() {Expression = new IntegerLiteral() {Value = "10000"} };
var setCounter = new SetVariableStatement();
setCounter.Variable = new VariableReference() {Name = "@rowcount"};
setCounter.Expression = new GlobalVariableExpression() {Name = "@@rowcount"};
setCounter.AssignmentKind = AssignmentKind.Equals;
var deleteStatement = new DeleteStatement();
deleteStatement.DeleteSpecification = delete;
var beginEnd = new BeginEndBlockStatement();
beginEnd.StatementList = new StatementList();
beginEnd.StatementList.Statements.Add(deleteStatement);
beginEnd.StatementList.Statements.Add(setCounter);
var whilePredicate = new BooleanComparisonExpression();
whilePredicate.ComparisonType = BooleanComparisonType.GreaterThan;
whilePredicate.FirstExpression = new VariableReference() {Name = "@rowcount"};
whilePredicate.SecondExpression = new IntegerLiteral() {Value = "0"};
var whileStatement = new WhileStatement();
whileStatement.Predicate = whilePredicate;
whileStatement.Statement = beginEnd;
var text = ScriptDom.GenerateTSql(counter) + "\r\n" + ScriptDom.GenerateTSql(whileStatement);
return text;
}
private WSqlStatement ParseDeleteStatement(DeleteSpecification delSpec)
{
if (delSpec == null)
return null;
var wdelSpec = new WDeleteSpecification
{
Target = ParseTableReference(delSpec.Target),
FirstTokenIndex = delSpec.FirstTokenIndex,
LastTokenIndex = delSpec.LastTokenIndex
};
//From Clause
if (delSpec.FromClause != null && delSpec.FromClause.TableReferences != null)
{
wdelSpec.FromClause = new WFromClause
{
FirstTokenIndex = delSpec.FromClause.FirstTokenIndex,
LastTokenIndex = delSpec.FromClause.LastTokenIndex,
TableReferences = new List<WTableReference>(delSpec.FromClause.TableReferences.Count)
};
foreach (var pref in delSpec.FromClause.TableReferences.Select(ParseTableReference).Where(pref => pref != null))
{
wdelSpec.FromClause.TableReferences.Add(pref);
}
}
//where clause
if (delSpec.WhereClause != null && delSpec.WhereClause.SearchCondition != null)
{
wdelSpec.WhereClause = new WWhereClause
{
FirstTokenIndex = delSpec.WhereClause.FirstTokenIndex,
LastTokenIndex = delSpec.WhereClause.LastTokenIndex,
SearchCondition = ParseBooleanExpression(delSpec.WhereClause.SearchCondition)
};
}
//top row filter
if (delSpec.TopRowFilter != null)
{
wdelSpec.TopRowFilter = new WTopRowFilter
{
Expression = ParseScalarExpression(delSpec.TopRowFilter.Expression),
Percent = delSpec.TopRowFilter.Percent,
WithTies = delSpec.TopRowFilter.WithTies,
FirstTokenIndex = delSpec.TopRowFilter.FirstTokenIndex,
LastTokenIndex = delSpec.TopRowFilter.LastTokenIndex
};
}
return wdelSpec;
}
public override void Visit(DeleteSpecification node) { this.action(node); }
public override void ExplicitVisit(DeleteSpecification fragment)
{
_fragments.Add(fragment);
}
private string buildChunkedDelete(DeleteSpecification delete)
{
var counter = new DeclareVariableStatement();
var counterVariable = new DeclareVariableElement();
counterVariable.DataType = new SqlDataTypeReference()
{
SqlDataTypeOption = SqlDataTypeOption.Int
};
counterVariable.VariableName = new Identifier()
{
Value = "@rowcount"
};
counterVariable.Value = new IntegerLiteral()
{
Value = "10000"
};
counter.Declarations.Add(counterVariable);
delete.TopRowFilter = new TopRowFilter();
delete.TopRowFilter.Expression = new ParenthesisExpression()
{
Expression = new IntegerLiteral()
{
Value = "10000"
}
};
var setCounter = new SetVariableStatement();
setCounter.Variable = new VariableReference()
{
Name = "@rowcount"
};
setCounter.Expression = new GlobalVariableExpression()
{
Name = "@@rowcount"
};
setCounter.AssignmentKind = AssignmentKind.Equals;
var deleteStatement = new DeleteStatement();
deleteStatement.DeleteSpecification = delete;
var beginEnd = new BeginEndBlockStatement();
beginEnd.StatementList = new StatementList();
beginEnd.StatementList.Statements.Add(deleteStatement);
beginEnd.StatementList.Statements.Add(setCounter);
var whilePredicate = new BooleanComparisonExpression();
whilePredicate.ComparisonType = BooleanComparisonType.GreaterThan;
whilePredicate.FirstExpression = new VariableReference()
{
Name = "@rowcount"
};
whilePredicate.SecondExpression = new IntegerLiteral()
{
Value = "0"
};
var whileStatement = new WhileStatement();
whileStatement.Predicate = whilePredicate;
whileStatement.Statement = beginEnd;
var text = ScriptDom.GenerateTSql(counter) + "\r\n" + ScriptDom.GenerateTSql(whileStatement);
return(text);
}
