static void Main(string[] args) { var sqlText = @" create procedure something as select 100; select 200 exec sp_who; exec sp_who2; " ; var sql = new StringReader(sqlText); var parser = new TSql140Parser(false); var script = parser.Parse(sql, out IList <ParseError> errors); var visitor = new ExecVisitor(); script.Accept(visitor); TSqlParserToken startComment = new TSqlParserToken(TSqlTokenType.SingleLineComment, "/*"); TSqlParserToken endComment = new TSqlParserToken(TSqlTokenType.SingleLineComment, "*/"); var newScriptTokenStream = new List <TSqlParserToken>(script.ScriptTokenStream); for (var i = visitor.Statements.Count - 1; i >= 0; i--) { var stmt = visitor.Statements[i]; newScriptTokenStream.Insert(stmt.LastTokenIndex, endComment); newScriptTokenStream.Insert(stmt.FirstTokenIndex, startComment); } var newFragment = parser.Parse(newScriptTokenStream, out errors); Console.WriteLine(GetScript(newFragment.ScriptTokenStream)); }
public async System.Threading.Tasks.Task Stream_Generate_Insert_For_Single_Entity() { using (var testDatabase = SqlServerTestStore.Create(DatabaseName)) { var loggingFactory = new TestSqlLoggerFactory(); var serviceProvider = new ServiceCollection() .AddEntityFrameworkSqlServer() .AddSingleton <ILoggerFactory>(loggingFactory) .BuildServiceProvider(); var optionsBuilder = new DbContextOptionsBuilder() .EnableSensitiveDataLogging() .UseSqlServer(testDatabase.ConnectionString, b => b.ApplyConfiguration()) .UseInternalServiceProvider(serviceProvider); using (var db = new BloggingContext(optionsBuilder.Options)) { await CreateBlogDatabaseAsync <Blog>(db); } loggingFactory.Clear(); using (var db = new BloggingContext(optionsBuilder.Options)) { var toAdd = db.Add(new Blog { Name = "Blog to Insert", George = true, TheGu = new Guid("0456AEF1-B7FC-47AA-8102-975D6BA3A9BF"), NotFigTime = new DateTime(1973, 9, 3, 0, 10, 33, 777), ToEat = 64, OrNothing = 0.123456789, Fuse = 777, WayRound = 9876543210, Away = 0.12345f, AndChew = new byte[16] }).Entity; var statement = ""; using (var stream = new MemoryStream()) { db.Generate(stream); stream.Seek(0, SeekOrigin.Begin); var streamReader = new StreamReader(stream); statement = await streamReader.ReadToEndAsync(); Assert.IsTrue(!string.IsNullOrEmpty(statement)); } var reader = new StringReader(statement); IList <ParseError> errors; var parser = new TSql140Parser(false); var parseResult = parser.Parse(reader, out errors); Assert.IsFalse(errors.Any()); } } }
protected void RunSql(string sql, object[] parameters) { if (DbConnectionType == typeof(SQLiteConnection)) { using (new DbContextTest()) { int i = 0; using (DatabaseContext ctx = new DatabaseContext()) { if (parameters != null) { ctx.Database.ExecuteSqlCommand(sql, parameters); } else { ctx.Database.ExecuteSqlCommand(sql); } i++; } } } else { TSql140Parser parser = new TSql140Parser(true, SqlEngineType.Standalone); IList <ParseError> errors; TSqlFragment result = parser.Parse(new StringReader(sql), out errors); ParseError error = errors.FirstOrDefault(); if (error != null) { throw new QueryParseException(error.Message); } } }
async void _build_syntax_btn_Click(object sender, RoutedEventArgs e) { var syntax_txt = _query_tb.Text; var bg_default = _analysis_tb.Background; _analysis_tb.Background = Brushes.Gold; var analysis_txt = await Task.Factory.StartNew(() => { var parser = new TSql140Parser(false); IList <ParseError> errors; var fragment = parser.Parse(new StringReader(syntax_txt), out errors); if (errors.Count > 0) { return("ERROR:\n" + String.Join("\n", errors.Select(err => err.Message))); } fragment.Accept(new MyNaiveMutator()); var renderer = new Sql140ScriptGenerator(); string sql; renderer.GenerateScript(fragment, out sql); return(sql); }); this.Dispatcher.Invoke(() => { _analysis_tb.Text = analysis_txt; _analysis_tb.Background = bg_default; }); }
public ParsedData GetColumns(string sql) { var parser = new TSql140Parser(true); var parsedData = new ParsedData(); using (var reader = new StringReader(sql)) { var result = parser.Parse(reader, out var errors); var traverser = new Traverser(_log); var columnData = traverser.TraverseObject(result, null).Distinct().ToArray(); var tableData = columnData.Where(c => c?.AmbiguousTableReferences != null) .SelectMany(c => c.AmbiguousTableReferences) .Concat(columnData.Select(c => c.AbsoluteTableReference)) .Where(c => c != null) .Distinct().ToArray(); parsedData.databaselist = tableData.Select(t => t.GetDatabase()).Where(c => c != null).Distinct().ToList(); parsedData.schemalist = tableData.Select(t => t.GetSchema()).Where(c => c != null).Distinct().ToList(); parsedData.tablelist = tableData.Select(t => t.GetFullyQualifiedName(_bracketOutput)).Where(c => c != null).Distinct().ToList(); parsedData.columnlist = columnData.Select(c => c.GetFullyQualifiedName(_bracketOutput)).Where(c => c != null).Distinct().ToList(); parsedData.errors = errors.Select(e => e.ToString()).ToList(); } return(parsedData); }
private TSqlScript ParseSp(IFileInfoWrapper spFile) { var sql = _fileWrapper.ReadAllText(spFile.FullName); var sqlParser = new TSql140Parser(false); var parsedSql = sqlParser.Parse(new StringReader(sql), out var parseErrors); if (parseErrors.Count != 0) { throw new Exception($"Error parsing SQL file {spFile.FullName}"); } var nonCommentTokens = parsedSql.ScriptTokenStream .Where(x => x.TokenType != TSqlTokenType.MultilineComment) .Where(x => x.TokenType != TSqlTokenType.SingleLineComment) .Select(x => x.Text); var result = sqlParser.Parse(new StringReader(string.Join("", nonCommentTokens)), out var parseErrors2); if (parseErrors2.Count != 0) { throw new Exception($"Error parsing SQL with comments removed {spFile.FullName}"); } return(result as TSqlScript); }
public static IList <ParsedStoredProcedureIdentifier> GetExecutedProcedures(string tsqlBatch) { var parser = new TSql140Parser(false); var sr = new StringReader(tsqlBatch); var fragment = parser.Parse(sr, out IList <ParseError> fragmentErrors); var visitor = new StoredProcedureVisitor(); fragment.Accept(visitor); var executedProcs = new List <ParsedStoredProcedureIdentifier>(); foreach (var proc in visitor.ExecutedProcedures) { if (proc.ProcedureReference.ProcedureReference == null) { //TODO: We need to handle procedure variables somehow! continue; } var id = proc.ProcedureReference.ProcedureReference.Name; var procedure = new ParsedStoredProcedureIdentifier(id.DatabaseIdentifier?.Value, id.SchemaIdentifier?.Value, id.BaseIdentifier.Value); executedProcs.Add(procedure); } return(executedProcs); }
private TSqlFragment CreateTSqlFragment(string sqlText) { var parser = new TSql140Parser(false, SqlEngineType.Standalone); var reader = new StringReader(sqlText); IList <ParseError> errors = new List <ParseError>(); return(parser.Parse(reader, out errors)); }
public TSqlScript ReadFromString([NotNull] string sql) { var parser = new TSql140Parser(initialQuotedIdentifiers: true, engineType: SqlEngineType.All); using (var reader = new StringReader(sql)) { var script = (TSqlScript)parser.Parse(reader, out var errors); return(script); } }
public IEnumerable <ParseError> GetErrors(string sql) { var parser = new TSql140Parser(true); using (var reader = new StringReader(sql)) { parser.Parse(reader, out var errors); return(errors); } }
public Column[] GetTraverserData(string sql) { var parser = new TSql140Parser(true); using (var reader = new StringReader(sql)) { var result = parser.Parse(reader, out var errors); var traverser = new Traverser(_log); return(traverser.TraverseObject(result, null).Distinct().ToArray()); } }
public SQLExecutionResult[] Execute(TextReader script) { var parser = new TSql140Parser(false); var parseResult = parser.Parse(script, out var errors); if (errors.Any()) { throw new ParseException(errors); } return(Visit <SQLExecutionResult[]>(parseResult)); }
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 async System.Threading.Tasks.Task Stream_Generate_Delete_For_Single_Entity() { using (var testDatabase = SqlServerTestStore.Create(DatabaseName)) { var loggingFactory = new TestSqlLoggerFactory(); var serviceProvider = new ServiceCollection() .AddEntityFrameworkSqlServer() .AddSingleton <ILoggerFactory>(loggingFactory) .BuildServiceProvider(); var optionsBuilder = new DbContextOptionsBuilder() .EnableSensitiveDataLogging() .UseSqlServer(testDatabase.ConnectionString, b => b.ApplyConfiguration()) .UseInternalServiceProvider(serviceProvider); using (var db = new BloggingContext(optionsBuilder.Options)) { await CreateBlogDatabaseAsync <Blog>(db); } loggingFactory.Clear(); using (var db = new BloggingContext(optionsBuilder.Options)) { var toDelete = db.Blogs.Single(b => b.Name == "Blog2"); toDelete.Name = "Blog to delete"; var deletedId = toDelete.Id; db.Entry(toDelete).State = EntityState.Deleted; var statement = ""; using (var stream = new MemoryStream()) { db.Generate(stream); stream.Seek(0, SeekOrigin.Begin); var streamReader = new StreamReader(stream); statement = await streamReader.ReadToEndAsync(); Assert.IsTrue(!string.IsNullOrEmpty(statement)); } var reader = new StringReader(statement); IList <ParseError> errors; var parser = new TSql140Parser(false); var parseResult = parser.Parse(reader, out errors); Assert.IsFalse(errors.Any()); } } }
public IEnumerable <SelectStatement> GetStatements(string sql) { var parser = new TSql140Parser(true); using (var reader = new StringReader(sql)) { var result = parser.Parse(reader, out var errors); foreach (var selectStatement in (result as TSqlScript).Batches.SelectMany(b => b.Statements.Cast <SelectStatement>())) { yield return(selectStatement); } } }
public TSqlScript ReadFromFile([NotNull] string fileName) { var parser = new TSql140Parser(initialQuotedIdentifiers: true, engineType: SqlEngineType.All); using (var file = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read)) { using (var reader = new StreamReader(file)) { var script = (TSqlScript)parser.Parse(reader, out var errors); return(script); } } }
private TSqlFragment Parse(string script) { var parser = new TSql140Parser(false); using (var reader = new StringReader(script)) { var result = parser.Parse(reader, out var errors); if (errors.Any()) { throw new ParseException(errors); } return(result); } }
public SQLVisitor ParseScript(string script) { IList <ParseError> parseErrors; TSqlParser tsqlParser = new TSql140Parser(true); TSqlFragment result; using (var stringReader = new StringReader(script)) { result = tsqlParser.Parse(stringReader, out parseErrors); var myVisitor = new SQLVisitor(); result.Accept(myVisitor); return(myVisitor); } }
public static void Validate(string sql) { var parser = new TSql140Parser(false); IList<ParseError> errors; using (var reader = new StringReader(sql)) { parser.Parse(reader, out errors); } if (errors == null || errors.Count == 0) { return; } var message = $"Sql errors:{string.Join("\r\n", errors.Select(error => error.Message))}"; throw new Exception(message); }
internal static TSqlFragment Load(TextReader reader) { TSqlParser parser = new TSql140Parser(true); using (reader) { TSqlFragment fragment = parser.Parse(reader, out IList <ParseError> errors); if (errors.Count > 0) { throw new InvalidOperationException($@"Error parsing SQL statement {String.Join(Environment.NewLine, errors.Select(x => $"{x.Message} at {x.Line},{x.Column}"))}"); } return(fragment); } }
public static void Validate(string sql) { var parser = new TSql140Parser(false); IList <ParseError> errors; using (var reader = new StringReader(sql)) { parser.Parse(reader, out errors); } if (errors == null || errors.Count == 0) { return; } var message = $"Sql errors:{string.Join("\r\n", errors.Select(error => error.Message))}"; throw new Exception(message); }
/// <summary> /// Converts a tsql object into a fragment /// </summary> /// <param name="obj"></param> /// <param name="parseErrors"></param> /// <returns></returns> public static TSqlFragment GetFragment(this TSqlObject obj, out IList <ParseError> parseErrors) { var tsqlParser = new TSql140Parser(true); TSqlFragment fragment = null; using (StringReader stringReader = new StringReader(obj.GetScript())) { fragment = tsqlParser.Parse(stringReader, out parseErrors); //so even after parsing, some scripts are coming back as tsql script, lets try to get the root object if (fragment.GetType() == typeof(TSqlScript)) { fragment = ((TSqlScript)fragment).Batches.FirstOrDefault()?.Statements.FirstOrDefault(); } } return(fragment); }
public static ICollection <string> IsSqlQueryValid(string sql) { var errors = new List <string>(); var parser = new TSql140Parser(false); using (TextReader reader = new StringReader(sql)) { parser.Parse(reader, out var parseErrors); if (parseErrors == null || parseErrors.Any()) { return(errors); } errors = parseErrors.Select(e => e.Message).ToList(); return(errors); } }
public bool IsSQLQueryValid(string sql, out string errors) { errors = new List <string>(); TSql140Parser parser = new TSql140Parser(false); TSqlFragment fragment; IList <ParseError> parseErrors; using (TextReader reader = new StringReader(sql)) { fragment = parser.Parse(reader, out parseErrors); if (parseErrors != null && parseErrors.Count > 0) { errors = parseErrors.Select(e => e.Message).ToList(); return(false); } } return(true); }
public static IEnumerable GetCodeAnalysisRuleErrors(SqlString ruleName, SqlString sqlText) { var rule = GetRuleByName(ruleName.Value); var resultCollection = new List <ISqlCodeAnalysisProblem>(); var parser = new TSql140Parser(false, SqlEngineType.All); var reader = new StringReader(sqlText.Value); IList <ParseError> errors = new List <ParseError>(); TSqlFragment sqlFragment = parser.Parse(reader, out errors); IEnumerable <ISqlCodeAnalysisProblem> problems = rule.GetFragmentProblems(sqlFragment); if (problems != null) { resultCollection.AddRange(problems); } return(resultCollection); }
/// <summary> /// Searches the entire fragment, for specific types /// </summary> /// <param name="baseFragment"></param> /// <param name="typesToLookFor"></param> /// <returns></returns> public static TSqlFragment GetFragment(this TSqlFragment baseFragment, params Type[] typesToLookFor) { //for some odd reason, sometimes the fragments do not pass in properly to the rules.... //this function can reparse that frsagment into its true fragment, and not a sql script... if (!(baseFragment is TSqlScript)) { return(baseFragment); } var stmt = ((TSqlScript)baseFragment)?.Batches.FirstOrDefault()?.Statements.FirstOrDefault(); if (stmt == null) { return(baseFragment); } //we dont need to parse the fragment unless it is of type TSqlStatement or TSqlStatementSnippet.... just return the type it found if (!(stmt.GetType() == typeof(TSqlStatement) || stmt.GetType() == typeof(TSqlStatementSnippet))) { return(stmt); } var tsqlParser = new TSql140Parser(true); TSqlFragment fragment = null; using (StringReader stringReader = new StringReader(((TSqlStatementSnippet)stmt).Script)) { IList <ParseError> parseErrors = new List <ParseError>(); fragment = tsqlParser.Parse(stringReader, out parseErrors); if (parseErrors.Any()) { return(baseFragment); } TypesVisitor visitor = new TypesVisitor(typesToLookFor); fragment.Accept(visitor); if (visitor.Statements.Any()) { return(visitor.Statements.First()); } } //if we got here, the object was tsqlscript, but was not parseable.... so we bail out return(baseFragment); }
public static TSqlFragment GetFragment(this FileInfo file) { TSqlFragment fragment = null; var tsqlParser = new TSql140Parser(true); using (TextReader textReader = file.OpenText()) { fragment = tsqlParser.Parse(textReader, out IList <ParseError> parseErrors) as TSqlScript; if (fragment == null) { throw new ApplicationException($"Unable to parse file {file.ToString()}"); } else if (parseErrors.Any()) { throw new ApplicationException($"Unable to parse file {file.ToString()}, errors: {string.Join("\r\n", parseErrors.Select(e => $"Line: {e.Line}, Error: {e.Message}"))}"); } } return(fragment); }
private static List <string> GetTableReferences(string file, SqlEngineType engineType) { using (TextReader reader = File.OpenText(file)) { TSql140Parser parser = new TSql140Parser(initialQuotedIdentifiers: false, engineType: engineType); IList <ParseError> errors = null; StatementList statements = parser.ParseStatementList(reader, out errors); if (errors.Count > 0) { throw new Exception(string.Format("File {0} had {1} errors", file, errors.Count)); } TableVisitor visitor = new TableVisitor(); statements.Accept(visitor); List <string> tables = visitor.Tables.Distinct().OrderBy(t => t).ToList(); return(tables); } }
public async System.Threading.Tasks.Task Generate_Update_For_Single_Entity() { using (var testDatabase = SqlServerTestStore.Create(DatabaseName)) { var loggingFactory = new TestSqlLoggerFactory(); var serviceProvider = new ServiceCollection() .AddEntityFrameworkSqlServer() .AddSingleton <ILoggerFactory>(loggingFactory) .BuildServiceProvider(); var optionsBuilder = new DbContextOptionsBuilder() .EnableSensitiveDataLogging() .UseSqlServer(testDatabase.ConnectionString, b => b.ApplyConfiguration()) .UseInternalServiceProvider(serviceProvider); using (var db = new BloggingContext(optionsBuilder.Options)) { await CreateBlogDatabaseAsync <Blog>(db); } loggingFactory.Clear(); using (var db = new BloggingContext(optionsBuilder.Options)) { var toUpdate = db.Blogs.Single(b => b.Name == "Blog1"); toUpdate.Name = "Blog is Updated"; var updatedId = toUpdate.Id; db.Entry(toUpdate).State = EntityState.Modified; var statement = db.Generate(); var reader = new StringReader(statement); IList <ParseError> errors; var parser = new TSql140Parser(false); var parseResult = parser.Parse(reader, out errors); Assert.IsFalse(errors.Any()); } } }
public static List <String> GetStatementTargets(string storedProcedureDefinition) { StringReader reader = new StringReader(storedProcedureDefinition); //specify parser for appropriate SQL version var parser = new TSql140Parser(true); IList <ParseError> errors; TSqlFragment sqlFragment = parser.Parse(reader, out errors); if (errors.Count > 0) { throw new Exception("Error parsing stored procedure definition"); } SQLVisitor sqlVisitor = new SQLVisitor(); sqlFragment.Accept(sqlVisitor); return(sqlVisitor.StatementTargets); }
public TableData[] GetTables(string sql) { var parser = new TSql140Parser(true); using (var reader = new StringReader(sql)) { var result = parser.Parse(reader, out var errors); var traverser = new Traverser(_log); var columnData = traverser.TraverseObject(result, null).Distinct().ToArray(); var tableData = columnData .Where(c => c?.AmbiguousTableReferences != null) .SelectMany(c => c.AmbiguousTableReferences) .Concat(columnData.Select(c => c.AbsoluteTableReference)) .Where(c => c != null) .Distinct() .Select(t => new TableData(t, _bracketOutput)) .Distinct() .ToArray(); foreach (var table in tableData) { table.Columns = columnData .Where(c => c.AbsoluteTableReference != null && table.Equals(new TableData(c.AbsoluteTableReference, _bracketOutput))) .Select(c => new ColumnData(c, _bracketOutput)) .Where(cd => _outputSelectStar || cd.ColumnNM != "*") .Distinct() .ToArray(); table.PossibleColumns = columnData .Where(c => c.AmbiguousTableReferences != null && c.AmbiguousTableReferences.Any(t => table.Equals(new TableData(t, _bracketOutput)))) .Select(c => new ColumnData(c, _bracketOutput)) .Where(cd => _outputSelectStar || cd.ColumnNM != "*") .Distinct() .ToArray(); } return(tableData); } }