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);
}
}
