예제 #1
0
        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);
        }
예제 #2
0
        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);
        }
예제 #3
0
        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);
        }
예제 #5
0
        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);
        }
예제 #7
0
        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);
        }
예제 #10
0
        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.");
                }
            }
        }
예제 #11
0
        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);
        }
    }
예제 #12
0
        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;
        }
예제 #13
0
        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)));
        }
예제 #14
0
        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)
                })
예제 #15
0
        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);
 }
예제 #17
0
        /*
            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;
        }
예제 #18
0
 public override void Visit(InsertSpecification node) { this.action(node); }
예제 #19
0
        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;
        }
예제 #20
0
        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;
        }
예제 #21
0
        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());
            }
        }