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); } }
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); }
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()); }
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); } }
/// <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項目 の解析に失敗しました。"); } }
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; }
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); }
public static List <NamedTableReference> GetListUsedTableReference(this TSqlScript script) { List <NamedTableReference> listObjects = new List <NamedTableReference>(); RecurseObjectReference(script, "Microsoft.SqlServer.TransactSql.ScriptDom", ref listObjects); return(listObjects); }
public void StatementVisitor() { var visitor = new StatementVisitor(); TSqlScript script = Parse("TsqlSample1.sql"); script.Accept(visitor); }
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 }))); } } } }
private async Task ExecuteSqlAsync([NotNull] TSqlScript sqlScript) { foreach (var batch in sqlScript.Batches) { await ExecuteSqlAsync(batch).ConfigureAwait(false); } }
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); }
public ProcedureDefinition(TSqlScript script) { if (script == null) { throw new ArgumentNullException(nameof(script)); } _script = script; }
public string FormatSql(TSqlScript tsqlScript, SqlVersion version) { String script; _generatorFactory .Get(version) .GenerateScript(tsqlScript, out script); return(script); }
public IReadOnlyCollection <RuleResult> Evaluate(string scriptText, TSqlScript script) { results = new List <RuleResult>(); script.Accept(this); FinalizeResults(); return(new List <RuleResult>(results)); }
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); }
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); }
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; } }
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; } }
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); } }
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); } }
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); }
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)}"); } }
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); }
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); }
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) }); } }
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(); }
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(); }