public override void Visit(TSqlScript node)
        {
            var typesToUpcase = Constants.TSqlKeywords.Concat(Constants.TSqlDataTypes).ToArray();

            for (var index = 0; index < node.ScriptTokenStream?.Count; index++)
            {
                var token = node.ScriptTokenStream[index];
                if (!typesToUpcase.Contains(token.Text, StringComparer.CurrentCultureIgnoreCase))
                {
                    continue;
                }

                if (IsUpperCase(token.Text))
                {
                    continue;
                }

                var dynamicSQLAdjustment = AdjustColumnForDymamicSQL(token);

                // get a count of all tabs on the line that occur prior to the last token in this node
                var tabsOnLine = ColumnNumberCalculator.CountTabsBeforeToken(token.Line, index, node.ScriptTokenStream);
                var column     = ColumnNumberCalculator.GetColumnNumberBeforeToken(tabsOnLine, token);

                errorCallback(RULE_NAME, RULE_TEXT, token.Line, column + dynamicSQLAdjustment);
            }
        }
Example #2
0
        public List <Token> GetTokenCollection(TSqlScript tsqlScript)
        {
            List <Token> lstTokens = new List <Token>();

            if (tsqlScript != null)
            {
                foreach (TSqlBatch batch in tsqlScript.Batches)
                {
                    if (batch.Statements.Count == 0)
                    {
                        continue;
                    }

                    foreach (TSqlStatement statement in batch.Statements)
                    {
                        foreach (var token in statement.ScriptTokenStream)
                        {
                            lstTokens.Add(new Token {
                                TokenType = token.TokenType.ToString() + (token.IsKeyword() ? "  --> keyword" : ""), Text = token.Text
                            });
                        }
                    }
                }
            }
            return(lstTokens);
        }
Example #3
0
        public string Convert(CSchema schema)
        {
            string[] parts = { schema.SchemaName };

            var createSchemaStatement = new CreateSchemaStatement();

            createSchemaStatement.Name = new Identifier {
                Value = schema.SchemaName
            };

            //generate DDL
            var script = new TSqlScript();
            var batch  = new TSqlBatch();

            script.Batches.Add(batch);
            batch.Statements.Add(createSchemaStatement);
            var dacpacModel = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions());
            var existing    = dacpacModel.GetObject(Schema.TypeClass, new ObjectIdentifier(parts), DacQueryScopes.All);

            if (existing != null)
            {
                return(existing.GetScript());
            }
            dacpacModel.AddObjects(script);
            existing = dacpacModel.GetObject(Schema.TypeClass, new ObjectIdentifier(parts), DacQueryScopes.All);
            return(existing.GetScript());
        }
Example #4
0
        public bool ParseView(string aViewSql)
        {
            TSql100Parser SqlParser = new TSql100Parser(false);

            ColumnList.Clear();

            TSqlFragment result = SqlParser.Parse(new StringReader(aViewSql), out ParseViewSqlErrors);

            foreach (ParseError aParseError in ParseViewSqlErrors)
            {
                AddLogText(string.Format("Parse sql error:{0} at line nr:{1} column:{2} ",
                                         aParseError.Message, aParseError.Line, aParseError.Column));
            }


            if (ParseViewSqlErrors.Count == 0)
            {
                TSqlScript SqlScript = result as TSqlScript;
                foreach (TSqlBatch sqlBatch in SqlScript.Batches)
                {
                    foreach (TSqlStatement sqlStatement in sqlBatch.Statements)
                    {
                        ProcessViewStatementBody(sqlStatement);
                    }
                }
                return(true);
            }
            else
            {
                return(false);
            }
        }
Example #5
0
 /// <summary>
 /// 列参照をインスタンス化します。
 /// </summary>
 public SelectItem(string expression)
 {
     if (!string.IsNullOrEmpty(expression))
     {
         try
         {
             TSqlScript script = (TSqlScript)TransactSQL._Parser.Parse(new StringReader($"SELECT {expression}"), out IList <ParseError> errors);
             if (errors.Count > 0 ||
                 script.Batches.Count != 1 ||
                 script.Batches[0].Statements.Count != 1 ||
                 !(script.Batches[0].Statements[0] is SelectStatement selectStatement) ||
                 !(selectStatement.QueryExpression is QuerySpecification querySpecification) ||
                 querySpecification.SelectElements.Count != 1)
             {
                 throw new ArgumentException("指定された SELECT項目 の解析に失敗しました。");
             }
             SelectElement = ((QuerySpecification)((SelectStatement)script.Batches[0].Statements[0]).QueryExpression).SelectElements[0];
         }
         catch (InvalidCastException)
         {
             throw new ArgumentException("指定された SELECT項目 の解析に失敗しました。");
         }
     }
     else
     {
         throw new ArgumentException("指定された SELECT項目 の解析に失敗しました。");
     }
 }
Example #6
0
        public override void Visit(TSqlScript node)
        {
            var childNoCountVisitor = new ChildNoCountVisitor();

            node.AcceptChildren(childNoCountVisitor);
            if (childNoCountVisitor.NoCountIsOn)
            {
                return;
            }

            // walk child nodes to determine if rowset operations are occurring
            var childRowsetVisitor = new ChildRowsetVisitor();

            node.AcceptChildren(childRowsetVisitor);

            // walk child nodes to determine if DDL operations are occurring
            var childDDLStatementFoundVisitor = new ChildDDLStatementFoundVisitor();

            node.AcceptChildren(childDDLStatementFoundVisitor);

            if (childDDLStatementFoundVisitor.DDLStatementFound && !childRowsetVisitor.RowsetActionFound)
            {
                return;
            }

            errorCallback(RULE_NAME, RULE_TEXT, node.StartLine, node.StartColumn);
        }
        private void ParseDelete(string query)
        {
            TSqlFragment tree = InitiateTSql110Parser(query);

            if (SyntaxError)
            {
                return;
            }

            if (tree is TSqlScript)
            {
                TSqlScript tSqlScript = tree as TSqlScript;
                foreach (TSqlBatch sqlBatch in tSqlScript.Batches)
                {
                    foreach (TSqlStatement sqlStatement in sqlBatch.Statements)
                    {
                        ParseSqlDeleteStatement(sqlStatement);
                    }
                }
            }
            RbacTSqlFragmentVisitor rbacTSqlFragmentVisitor = new RbacTSqlFragmentVisitor();

            tree.Accept(rbacTSqlFragmentVisitor);
            this.Warnings.AddRange(rbacTSqlFragmentVisitor.Warnings);

            TablesReferred = new List <RbacTable>(TablesReferred.DistinctBy(t => t.Name));
            IsParsed       = true;
        }
Example #8
0
        public List <ParseResults> Parse(string procText)
        {
            var parser     = new TSql150Parser(true);
            var textReader = new StringReader(procText);
            var sqlTree    = parser.Parse(textReader, out var errors);

            if (errors.Count > 0)
            {
                throw new ParserException(errors);
            }
            var visitor = new Visitor();

            sqlTree.Accept(visitor);

            TSqlScript sqlScript = (TSqlScript)sqlTree;

            Debug.Assert(sqlScript.Batches.Count == 1, "Currently our visitor doesn't support multiple batches.");
            Debug.Assert(visitor.Results.Count == sqlScript.Batches[0].Statements.Count, "Statements processed by visitor don't match statements counted by parser.");

            int i = 0;

            foreach (var stm in sqlScript.Batches[0].Statements)
            {
                visitor.Results[i++].SetStatementText(procText.Substring(stm.StartOffset, stm.FragmentLength));
            }
            return(visitor.Results);
        }
Example #9
0
        public static List <NamedTableReference> GetListUsedTableReference(this TSqlScript script)
        {
            List <NamedTableReference> listObjects = new List <NamedTableReference>();

            RecurseObjectReference(script, "Microsoft.SqlServer.TransactSql.ScriptDom", ref listObjects);
            return(listObjects);
        }
Example #10
0
        public void StatementVisitor()
        {
            var        visitor = new StatementVisitor();
            TSqlScript script  = Parse("TsqlSample1.sql");

            script.Accept(visitor);
        }
Example #11
0
        public void PrintStatements(TSqlScript tsqlScript)
        {
            if (tsqlScript != null)
            {
                foreach (TSqlBatch batch in tsqlScript.Batches)
                {
                    if (batch.Statements.Count == 0)
                    {
                        continue;
                    }

                    foreach (TSqlStatement statement in batch.Statements)
                    {
                        var peter = new
                        {
                            StatementType = statement.GetType().ToString(),
                            Tokens        = statement.ScriptTokenStream.Select(s => new { TokenType = s.TokenType, Text = s.Text })
                        };
                        Console.WriteLine(peter);

                        //Console.WriteLine(string.Format("{0}\r\n", statement.GetType().ToString()));

                        //Console.WriteLine(string.Join("\n", statement.ScriptTokenStream.Select(s => new { TokenType = s.TokenType, Text=s.Text })));
                    }
                }
            }
        }
Example #12
0
 private async Task ExecuteSqlAsync([NotNull] TSqlScript sqlScript)
 {
     foreach (var batch in sqlScript.Batches)
     {
         await ExecuteSqlAsync(batch).ConfigureAwait(false);
     }
 }
Example #13
0
        private IList <DatabaseContext> GetFromUseStatement(TSqlScript script)
        {
            var visitor = new UseStatementVisitor();

            script.Accept(visitor);
            if (visitor.Fragments.Count > 2)
            {
                // TODO : create a validation rule as well
                _logger.Log(LogLevel.Warning, $"More than one USE statement detected, first value is used to determine database context.");
            }

            var useStatementContext = visitor
                                      .Fragments
                                      .Select(x => new DatabaseContext()
            {
                Type = DatabaseContextType.UseStatement,
                Name = x.DatabaseName.Value
            })
                                      .FirstOrDefault();

            return(useStatementContext != null
                ? new List <DatabaseContext>()
            {
                useStatementContext
            }
                : null);
        }
Example #14
0
        public ProcedureDefinition(TSqlScript script)
        {
            if (script == null)
            {
                throw new ArgumentNullException(nameof(script));
            }

            _script = script;
        }
Example #15
0
        public string FormatSql(TSqlScript tsqlScript, SqlVersion version)
        {
            String script;

            _generatorFactory
            .Get(version)
            .GenerateScript(tsqlScript, out script);

            return(script);
        }
Example #16
0
        public IReadOnlyCollection <RuleResult> Evaluate(string scriptText, TSqlScript script)
        {
            results = new List <RuleResult>();

            script.Accept(this);

            FinalizeResults();

            return(new List <RuleResult>(results));
        }
Example #17
0
        public void SprocVisitor()
        {
            var        visitor = new StoredProcVisitor();
            TSqlScript script  = Parse("StoredProc.sql");

            script.Accept(visitor);
            Assert.IsTrue(visitor.HasNocountOn);
            Assert.IsTrue(visitor.HasTransactionIsolationLevel);
            Assert.IsTrue(visitor.HasComments);
        }
Example #18
0
        public static ScriptDomSqlParser Parse(string sqlText)
        {
            var visitor = new ScriptDomSqlParser();
            var parser  = new TSql120Parser(true);

            using (var txtReader = new StringReader(sqlText))
            {
                ParsedScript   = (TSqlScript)parser.Parse(txtReader, out var errors);
                visitor.Errors = errors;
                if (errors.Any())
                {
                    return(visitor);
                }
                ParsedScript.Accept(visitor);
            }

            if (visitor.Result == null)
            {
                return(visitor);
            }

            foreach (var result in visitor.Result)
            {
                var defaultTable = result.Tables.FirstOrDefault();
                if (defaultTable == null)
                {
                    continue;
                }
                foreach (var field in result.Fields)
                {
                    var table = result.Tables.FirstOrDefault(t => t == field.Prefix || t.EndsWith("." + field.Prefix));
                    if (table != null)
                    {
                        field.Table = table;
                    }
                    else if (field.Alias == null)
                    {
                        field.Table = defaultTable;
                    }
                    else
                    {
                        var a = field.Alias.ToLower();
                        if (result.Aliases.ContainsKey(a))
                        {
                            field.Table = result.Aliases[a];
                        }
                        else
                        {
                            //Debugger.Break();
                        }
                    }
                }
            }
            return(visitor);
        }
Example #19
0
        public override void Visit(TSqlScript node)
        {
            var childAnsiNullsVisitor = new ChildAnsiNullsVisitor();

            node.AcceptChildren(childAnsiNullsVisitor);
            if (!childAnsiNullsVisitor.SetAnsiNullsFound && !ErrorLogged)
            {
                ErrorCallback(RULE_NAME, RULE_TEXT, node.StartLine, node.StartColumn);
                ErrorLogged = true;
            }
        }
Example #20
0
        public override void Visit(TSqlScript node)
        {
            var childQuotedidentifierVisitor = new ChildQuotedidentifierVisitor();

            node.AcceptChildren(childQuotedidentifierVisitor);
            if (!childQuotedidentifierVisitor.QuotedIdentifierFound && !ErrorLogged)
            {
                ErrorCallback(RULE_NAME, RULE_TEXT, node.StartLine, node.StartColumn);
                ErrorLogged = true;
            }
        }
        public string Convert(CTable table)
        {
            string[] parts = { table.Schema.SchemaName, table.TableName };

            var createTable = new CreateTableStatement();

            ///set schema and table name
            createTable.SchemaObjectName = new SchemaObjectName();

            createTable.SchemaObjectName.Identifiers.Add(new Identifier {
                Value = table.Schema.SchemaName
            });
            createTable.SchemaObjectName.Identifiers.Add(new Identifier {
                Value = table.TableName
            });

            //add columns
            createTable.Definition = new TableDefinition();

            foreach (var col in table.Column)
            {
                var dataType = new SqlDataTypeReference {
                    SqlDataTypeOption = GetSqlDataTypeOption(col.ColumnType)
                };
                if (col.ColumnLength > 0)
                {
                    dataType.Parameters.Add(new IntegerLiteral {
                        Value = col.ColumnLength.ToString()
                    });
                }
                var column = new ColumnDefinition
                {
                    ColumnIdentifier = new Identifier {
                        Value = col.ColumnName
                    },
                    DataType = dataType
                };

                createTable.Definition.ColumnDefinitions.Add(column);
            }

            //generate DDL
            var script = new TSqlScript();
            var batch  = new TSqlBatch();

            script.Batches.Add(batch);
            batch.Statements.Add(createTable);
            var dacpacModel = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions());

            dacpacModel.AddObjects(script);
            var existing = dacpacModel.GetObject(Table.TypeClass, new ObjectIdentifier(parts), DacQueryScopes.All);

            return(existing.GetScript());
        }
        public override void Visit(TSqlScript node)
        {
            var childTransactionIsolationLevelVisitor = new ChildTransactionIsolationLevelVisitor();

            node.AcceptChildren(childTransactionIsolationLevelVisitor);
            if (!childTransactionIsolationLevelVisitor.TransactionIsolationLevelFound && !ErrorLogged)
            {
                ErrorCallback(RULE_NAME, RULE_TEXT, node.StartLine, node.StartColumn);
                ErrorLogged = true;
            }
        }
Example #23
0
 private static string GetLineColOfFirstToken(TSqlScript script, int firstTokenIndex)
 {
     if (firstTokenIndex >= 0 && firstTokenIndex <= script.ScriptTokenStream.Count)
     {
         return($"Ln { script.ScriptTokenStream[firstTokenIndex].Line}, Col { script.ScriptTokenStream[firstTokenIndex].Column}");
     }
     else
     {
         return(string.Empty);
     }
 }
Example #24
0
        public string GetTokenText(TSqlScript script, int firstTokenIndex, int lastTokenIndex)
        {
            var startIndex = Math.Max(firstTokenIndex, 0);
            var endIndex   = Math.Min(lastTokenIndex, script.ScriptTokenStream.Count - 1);
            var sb         = new StringBuilder();

            for (int i = startIndex; i <= endIndex; i++)
            {
                sb.Append(script.ScriptTokenStream[i].Text);
            }
            return(sb.ToString());
        }
        public void WriteScript(TSqlScript script, KeywordCasing casing = KeywordCasing.Lowercase)
        {
            _generator.Options.KeywordCasing = casing;

            WriteHeader();

            foreach (var batch in script.Batches)
            {
                WriteBatch(batch);
                Go(casing);
            }
        }
Example #26
0
        private bool ParseSqlFragments(string script, out TSqlScript sqlFragments)
        {
            IList <ParseError> errors;
            TSql140Parser      parser = new TSql140Parser(true);

            using (System.IO.StringReader reader = new System.IO.StringReader(script))
            {
                sqlFragments = parser.Parse(reader, out errors) as TSqlScript;
            }

            return(errors.Count == 0);
        }
Example #27
0
        public string GetFragmentOrTokenText(TSqlScript script, RuleResult ruleResult)
        {
            var resultWithFragment = ruleResult as RuleResultWithFragment;

            if (resultWithFragment != null)
            {
                return($"{GetFragmentText(resultWithFragment.Fragment)}");
            }
            else
            {
                return($"{GetTokenText(script, ruleResult.FirstTokenIndex, ruleResult.LastTokenIndex)}");
            }
        }
Example #28
0
        protected IList <TSqlStatement> GetStatements(TSqlScript script)
        {
            List <TSqlStatement> tsqlStatementList = new List <TSqlStatement>();

            foreach (TSqlBatch batch in script.Batches)
            {
                foreach (TSqlStatement statement in batch.Statements)
                {
                    tsqlStatementList.Add(statement);
                }
            }
            return(tsqlStatementList);
        }
Example #29
0
        public void SmokeTestWithVisitor()
        {
            var        visitor = new TopRowFilterVisitor();
            TSqlScript script  = Parse("TsqlSample1.sql");

            script.Accept(visitor);
            Assert.IsTrue(visitor.HasParenthesis);

            visitor = new TopRowFilterVisitor();
            script  = Parse("TsqlSample2.sql");
            script.Accept(visitor);
            Assert.IsFalse(visitor.HasParenthesis);
        }
Example #30
0
        private static IEnumerable <string> GetStatements(TSqlFragment fragment)
        {
            Sql120ScriptGenerator sg     = new Sql120ScriptGenerator();
            TSqlScript            script = fragment as TSqlScript;

            if (script != null)
            {
                return(script.Batches.SelectMany(x => x.Statements, (_, x) => ScriptFragment(sg, x)));
            }
            else
            {
                // TSqlFragment is a TSqlBatch or a TSqlStatement
                return(new[] { ScriptFragment(sg, fragment) });
            }
        }
Example #31
0
        public override void ExplicitVisit(TSqlScript node)
        {
            WasVisited = true;
            SqlFragment = node;

            IsUpperCase = true;
            for (int i = node.FirstTokenIndex; i <= node.LastTokenIndex; i++)
            {
              TSqlParserToken token = node.ScriptTokenStream[i];
              if (_tokenTypes.Contains(token.TokenType))
              {
            if (!Regex.IsMatch(token.Text, "[A-Z]"))
            {
              IsUpperCase = false;
              break;
            }
              }
            }
        }
        public static XDocument Convert(TSqlScript tsqlScript)
        {
            var querySpecificationFromTSqlScript = TSqlScriptParsingHelpers.QuerySpecificationFromTSqlScript(tsqlScript);
            var orderBysFromTSqlScript = TSqlScriptParsingHelpers.OrderBysFromTSqlScript(tsqlScript);

            var columns = TSqlScriptParsingHelpers.CollectColumnNames(querySpecificationFromTSqlScript);
            var tables = TSqlScriptParsingHelpers.TableAndJoinInfos(querySpecificationFromTSqlScript);
            var whereClauses = TSqlScriptParsingHelpers.WhereClauseInfos(querySpecificationFromTSqlScript);
            var orderBys = TSqlScriptParsingHelpers.OrderByInfos(querySpecificationFromTSqlScript ,orderBysFromTSqlScript);

            var firstTableName = tables.First().Value.TableName.ToLower();
            var firstTableWhereClauses = whereClauses
                .Where(clause => whereClauses.Keys
                .Where(x => x.StartsWith(firstTableName + ".")).Contains(clause.Key))
                .ToDictionary(clause => clause.Key, clause => clause.Value)
                .Values;
            var firstTableOrderBys = orderBys
                .Where(clause => orderBys.Keys
                .Where(x => x.StartsWith(firstTableName + ".")).Contains(clause.Key))
                .ToDictionary(clause => clause.Key, clause => clause.Value)
                .Values;
            var linkTableWhereClauses = whereClauses
                .Where(clause => whereClauses.Keys
                .Where(x => !x.StartsWith(firstTableName + ".")).Contains(clause.Key))
                .ToDictionary(clause => clause.Key, clause => clause.Value)
                .Values;
             var linkTableOrderBys = orderBys
                .Where(clause => orderBys.Keys
                .Where(x => !x.StartsWith(firstTableName + ".")).Contains(clause.Key))
                .ToDictionary(clause => clause.Key, clause => clause.Value)
                .Values;

            var fetchXml = new XDocument(
                new XElement("fetch",
                             new XAttribute("version", "1.0"),
                             new XAttribute("output-format", "xml-platform"),
                             new XAttribute("mapping", "logical"),
                             new XAttribute("distinct", "false"),

                             // First entity
                             new XElement("entity",
                                          new XAttribute("name", firstTableName),

                                          FirstEntityAttributes(firstTableName, columns),
                                          FirstEntityFilters(firstTableWhereClauses),
                                          FirstEntityOrderBy(firstTableOrderBys),

                                          // Link entities and joins
                                          from linkEntityKey in tables.Keys
                                          where linkEntityKey != firstTableName
                                          select new XElement("link-entity",
                                                              new XAttribute("name", tables[linkEntityKey].TableName),
                                                              new XAttribute("to", tables[linkEntityKey].To),
                                                              new XAttribute("from", tables[linkEntityKey].From),
                                                              new XAttribute("link-type",
                                                                             tables[linkEntityKey].JoinType == "inner"
                                                                                 ? "inner"
                                                                                 : "outer"),

                                                              LinkEntityAttributes(linkEntityKey, columns),
                                                              LinkEntityFilters(linkEntityKey, linkTableWhereClauses),
                                                              LinkEntityOrderBy(linkEntityKey, linkTableOrderBys)
                                              ))));

            return fetchXml;
        }
 public override void ExplicitVisit(TSqlScript fragment)
 {
     _fragments.Add(fragment);
 }
 public static QuerySpecification QuerySpecificationFromTSqlScript(TSqlScript tsqlScript)
 {
     return tsqlScript.Batches.SelectMany(batch => (from SelectStatement selectStatement in batch.Statements
                                                    select selectStatement.QueryExpression
                                                    into queryExpression
                                                    select queryExpression as QuerySpecification)).First();
 }
Example #35
0
 public override void Visit(TSqlScript node) { this.action(node); }
 public static OrderByClause OrderBysFromTSqlScript(TSqlScript tsqlScript)
 {
     return tsqlScript.Batches.SelectMany(batch => (from SelectStatement selectStatement in batch.Statements
                                                    select selectStatement.OrderByClause
                                                    into orderByClause
                                                    select orderByClause)).First();
 }