public ISyntaxNode Visit(TSqlFragment node, TSqlFragment parent, string sourceProperty, ISyntaxNode result)
{
InsertSpecification insert = node as InsertSpecification;
if (insert == null)
{
return(result);
}
StatementNode statement = new StatementNode()
{
Parent = result,
Fragment = node,
ParentFragment = parent,
TargetProperty = sourceProperty
};
if (result is ScriptNode script)
{
if (parent is InsertStatement)
{
script.Statements.Add(statement);
return(statement);
}
}
return(result);
}
public override void ExplicitVisit(InsertSpecification node)
{
if (node.InsertOption == InsertOption.Into || node.InsertOption == InsertOption.None)
{
_buffer.Append("into ");
}
else
{
throw new InvalidOperationException();
}
node.Target.Accept(this);
_buffer.AppendLine();
if (node.Columns != null)
{
_buffer.Append("\t(");
for (int index = 0, count = node.Columns.Count - 1; index <= count; ++index)
{
var column = node.Columns[index];
column.Accept(this);
if (index < count)
{
_buffer.Append(", ");
}
}
_buffer.Append(")");
_buffer.AppendLine();
}
node.InsertSource.Accept(this);
}
private BeginEndBlockStatement BuildBeginEndWrappedBody(InsertSpecification originalInsert)
{
var block = new BeginEndBlockStatement();
block.StatementList = new StatementList();
block.StatementList.Statements.Add(BuildDelete(originalInsert));
return block;
}
private BeginEndBlockStatement BuildBeginEndWrappedBody(InsertSpecification originalInsert)
{
var block = new BeginEndBlockStatement();
block.StatementList = new StatementList();
block.StatementList.Statements.Add(BuildDelete(originalInsert));
return(block);
}
public override void ExplicitVisit(InsertSpecification node)
{
base.ExplicitVisit(node);
if (!node.Columns.Any())
{
AddFinding(node, "INSERT statement should specify the columns.");
}
}
/*
* go from:
*
* insert into dbo.table(columns...)
* select top X from dbo.another_table(columns...)
*
* to:
*
* while (select count(*) from dbo.table) > 0
* begin
* with to_delete(
* select top X from dbo.another_table(columns...)
* )
* delete to_delete.* into dbo.table(columns...)
* end
*
*/
private WhileStatement BuildWhileStatement(InsertSpecification originalInsert)
{
var whyle = new WhileStatement();
whyle.Predicate =
BuildWhilePredicate(
BuildSelectSubQuery((originalInsert.InsertSource as SelectInsertSource).Select as QuerySpecification));
whyle.Statement = BuildBeginEndWrappedBody(originalInsert);
return(whyle);
}
public static EngineResult Evaluate(InsertSpecification insert, IOutputSink sink, Scope scope)
{
var table = scope.Env.GetTable((NamedTableReference)insert.Target);
return(insert.InsertSource switch
{
ValuesInsertSource values => Evaluate(table, insert.Columns, values, NullArgument.It, sink, scope),
SelectInsertSource select => Evaluate(table, insert.Columns, Evaluate(@select.Select, scope).ResultSet,
sink, scope),
_ => throw FeatureNotSupportedException.Subtype(insert.InsertSource)
});
private QuerySpecification BuildNewRowSource(InsertSpecification originalInsert)
{
var specification = (originalInsert.InsertSource as SelectInsertSource).Select as QuerySpecification;
specification.TopRowFilter = new TopRowFilter
{
Expression = new IntegerLiteral
{
Value = _rowCount
}
};
return(specification);
}
private TSqlStatement BuildDelete(InsertSpecification originalInsert)
{
var delete = new DeleteStatement();
delete.WithCtesAndXmlNamespaces = new WithCtesAndXmlNamespaces();
var cte = new CommonTableExpression();
cte.ExpressionName = new Identifier()
{
Value = "to_delete"
};
cte.QueryExpression = BuildNewRowSource(originalInsert);
delete.WithCtesAndXmlNamespaces.CommonTableExpressions.Add(cte);
delete.DeleteSpecification = new DeleteSpecification();
var tableName = new SchemaObjectName();
tableName.Identifiers.Add(new Identifier()
{
Value = "to_delete"
});
delete.DeleteSpecification.Target = new NamedTableReference()
{
SchemaObject = tableName
};
var outputInto = delete.DeleteSpecification.OutputIntoClause = new OutputIntoClause();
var deletedTable = new MultiPartIdentifier();
deletedTable.Identifiers.Add(new Identifier()
{
Value = "deleted"
});
outputInto.SelectColumns.Add(new SelectStarExpression()
{
Qualifier = deletedTable
});
outputInto.IntoTable = originalInsert.Target;
foreach (var col in originalInsert.Columns)
{
outputInto.IntoTableColumns.Add(col);
}
return(delete);
}
public override void Visit(InsertSpecification node)
{
base.Visit(node);
var hasTopClause = node.TopRowFilter != null;
var selectSource = node.InsertSource as SelectInsertSource;
if (selectSource != null)
{
if (hasTopClause && selectSource?.Select?.OrderByClause != null)
{
AddFinding(node, "The ORDER BY in the SELECT has no effect, TOP takes random rows.");
}
}
}
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);
}
}
private TSqlStatement BuildDelete(InsertSpecification originalInsert)
{
var delete = new DeleteStatement();
delete.WithCtesAndXmlNamespaces = new WithCtesAndXmlNamespaces();
var cte = new CommonTableExpression();
cte.ExpressionName = new Identifier()
{
Value = "to_delete"
};
cte.QueryExpression = BuildNewRowSource(originalInsert);
delete.WithCtesAndXmlNamespaces.CommonTableExpressions.Add(cte);
delete.DeleteSpecification = new DeleteSpecification();
var tableName = new SchemaObjectName();
tableName.Identifiers.Add( new Identifier()
{
Value = "to_delete"
});
delete.DeleteSpecification.Target = new NamedTableReference() {SchemaObject = tableName };
var outputInto = delete.DeleteSpecification.OutputIntoClause = new OutputIntoClause();
var deletedTable = new MultiPartIdentifier();
deletedTable.Identifiers.Add(new Identifier()
{
Value = "deleted"
});
outputInto.SelectColumns.Add(new SelectStarExpression()
{
Qualifier = deletedTable
});
outputInto.IntoTable = originalInsert.Target;
foreach (var col in originalInsert.Columns)
{
outputInto.IntoTableColumns.Add(col);
}
return delete;
}
protected override object InternalVisit(InsertSpecification node)
{
var table = (Table)Visit <IResultTable>(node.Target);
List <string> providedColumns = node.Columns
.Select(columnReference => Visit <string>(columnReference))
.ToList();
var providedRows = Visit <object[][]>(node.InsertSource);
Func <object[], Row> CreateRow;
if (providedColumns.Count == 0)
{
CreateRow = row =>
{
Row dr = table.NewRow(row);
table.AddRow(dr);
return(dr);
};
}
else
{
Dictionary <string, object> values = new Dictionary <string, object>();
foreach (var item in providedColumns)
{
values.Add(item, null);
}
CreateRow = row =>
{
for (int i = 0; i < providedColumns.Count; i++)
{
values[providedColumns[i]] = row[i];
}
Row dr = table.NewRow(values);
table.AddRow(dr);
return(dr);
};
}
var rows = providedRows.Select(CreateRow).ToArray();
return(new SQLExecutionResult(rows.Count(),
ApplyOutputClause(new RecordTable("INSERTED", table.Columns, rows), node.OutputClause)));
}
public QsiActionNode VisitInsertSpecificiation(InsertSpecification insertSpecification)
{
var node = new QsiDataInsertActionNode();
var tableNode = TableVisitor.VisitTableReference(insertSpecification.Target);
if (tableNode is not QsiTableReferenceNode tableReferenceNode)
{
throw new QsiException(QsiError.Syntax);
}
node.Target.SetValue(tableReferenceNode);
if (!ListUtility.IsNullOrEmpty(insertSpecification.Columns))
{
node.Columns = insertSpecification.Columns
.Select(ExpressionVisitor.VisitColumnReferenceExpression)
.Select(c => c.Column.Value switch
{
QsiColumnReferenceNode columnReferenceNode => columnReferenceNode.Name,
QsiAllColumnNode allColumnNode => allColumnNode.Path,
_ => throw new QsiException(QsiError.Syntax)
})
public static IEnumerable <SchemaObjectReference> GetSchemaObjectReferences(
this InsertSpecification insertSpecification,
ILogger logger,
SchemaFile file
)
{
var targetReferences = insertSpecification
.Target
.GetSchemaObjectReferences(logger, file)
.ToList();
var insertSourceReferences = insertSpecification
.InsertSource
.GetSchemaObjectReferences(logger, file)
.ToList();
var targetReference = targetReferences.First();
var outputIntoReferences = new List <SchemaObjectReference>()
{
new SchemaObjectReference()
{
Alias = "inserted",
Identifier = targetReference.Identifier,
Value = targetReference.Value
},
new SchemaObjectReference()
{
Alias = "deleted",
Identifier = targetReference.Identifier,
Value = targetReference.Value
}
};
return(targetReferences
.Concat(insertSourceReferences)
.Concat(outputIntoReferences)
.ToList());
}
public override void ExplicitVisit(InsertSpecification fragment)
{
_fragments.Add(fragment);
}
/*
go from:
insert into dbo.table(columns...)
select top X from dbo.another_table(columns...)
to:
while (select count(*) from dbo.table) > 0
begin
with to_delete(
select top X from dbo.another_table(columns...)
)
delete to_delete.* into dbo.table(columns...)
end
*/
private WhileStatement BuildWhileStatement(InsertSpecification originalInsert)
{
var whyle = new WhileStatement();
whyle.Predicate =
BuildWhilePredicate(
BuildSelectSubQuery((originalInsert.InsertSource as SelectInsertSource).Select as QuerySpecification));
whyle.Statement = BuildBeginEndWrappedBody(originalInsert);
return whyle;
}
public override void Visit(InsertSpecification node) { this.action(node); }
private QuerySpecification BuildNewRowSource(InsertSpecification originalInsert)
{
var specification = (originalInsert.InsertSource as SelectInsertSource).Select as QuerySpecification;
specification.TopRowFilter = new TopRowFilter
{
Expression = new IntegerLiteral
{
Value = _rowCount
}
};
return specification;
}
private WSqlStatement ParseInsertStatement(InsertSpecification insSpec)
{
var winsSpec = new WInsertSpecification
{
Target = ParseTableReference(insSpec.Target),
InsertOption = insSpec.InsertOption,
InsertSource = ParseInsertSource(insSpec.InsertSource),
FirstTokenIndex = insSpec.FirstTokenIndex,
LastTokenIndex = insSpec.LastTokenIndex
};
if (insSpec.TopRowFilter != null)
{
winsSpec.TopRowFilter = new WTopRowFilter
{
Expression = ParseScalarExpression(insSpec.TopRowFilter.Expression),
WithTies = insSpec.TopRowFilter.WithTies,
Percent = insSpec.TopRowFilter.Percent,
FirstTokenIndex = insSpec.TopRowFilter.FirstTokenIndex,
LastTokenIndex = insSpec.TopRowFilter.LastTokenIndex
};
}
//Columns
winsSpec.Columns = new List<WColumnReferenceExpression>(insSpec.Columns.Count);
foreach (var wexpr in insSpec.Columns.Select(column => new WColumnReferenceExpression
{
MultiPartIdentifier = ParseMultiPartIdentifier(column.MultiPartIdentifier),
ColumnType = column.ColumnType,
FirstTokenIndex = column.FirstTokenIndex,
LastTokenIndex = column.LastTokenIndex
}))
{
winsSpec.Columns.Add(wexpr);
}
return winsSpec;
}
public static IList <FieldPairReference> GetFieldPairReferences(
this InsertSpecification insertSpecification,
ILogger logger,
SchemaFile file
)
{
var newReferences = insertSpecification
.GetSchemaObjectReferences(logger, file)
.ToList();
using (new StatementContext(file.FileContext, newReferences))
{
var sourceColumns = insertSpecification
.InsertSource
.GetFields(logger, file)
.ToList();
var target = insertSpecification
.Target
.GetSchemaObjectReferences(logger, file)
.First();
var targetColumns = target
.Value
.Columns;
var targetColumnsWithoutIdentity = targetColumns
.Where(x => !x.HasIdentity)
.ToList();
var targetPairs = new List <FieldPairReference>();
// it there are no source/target columns means we don't actually know schema definition for it.. so skip
if (target.Value.Type != SchemaObjectType.NotSpecified && targetColumns.Any() && sourceColumns.Any())
{
if (insertSpecification.Columns.Any())
{
var selectedColumns = insertSpecification.Columns
.Join(targetColumns,
x => x.MultiPartIdentifier.Identifiers.Last().Value,
y => y.Name,
(x, y) => new { Fragment = x, Known = y },
StringComparer.InvariantCultureIgnoreCase)
.ToList();
if (sourceColumns.Count == selectedColumns.Count)
{
targetPairs = selectedColumns
.Zip(sourceColumns, (t, s) => new FieldPairReference()
{
Left = t.Known,
Right = s,
Fragment = t.Fragment,
})
.ToList();
}
else
{
logger.Log(LogLevel.Error,
$"Can't match up columns in insert statement. " +
$"Count of columns selected from traget ({selectedColumns.Count}) " +
$"doesn't match with count of source columns ({sourceColumns.Count})");
}
}
else if (sourceColumns.Count == targetColumns.Count)
{
targetPairs = targetColumns
.Zip(sourceColumns, (t, s) => new FieldPairReference()
{
Left = t,
Right = s,
Fragment = insertSpecification.Target
})
.ToList();
}
else if (sourceColumns.Count == targetColumnsWithoutIdentity.Count)
{
targetPairs = targetColumnsWithoutIdentity
.Zip(sourceColumns, (t, s) => new FieldPairReference()
{
Left = t,
Right = s,
Fragment = insertSpecification.Target
})
.ToList();
}
else
{
logger.Log(LogLevel.Error, $"Can't match up columns in insert statement. Target: {targetColumns.Count} vs Source : {sourceColumns.Count}");
}
}
var insertSourcePairs = insertSpecification
.InsertSource
.GetFieldPairReferences(logger, file)
.ToList();
var outputIntoPairs = insertSpecification
.OutputIntoClause
?.GetFieldPairs(logger, file)
?? new List <FieldPairReference>();
return(targetPairs
.Concat(insertSourcePairs)
.Concat(outputIntoPairs)
.ToList());
}
}