private void RegisterSqlParser(SqlVersion sqlVersion, bool initialQuotedIdentifiers)
{
Logger.Info($"Register requested parser: SqlVersion={sqlVersion}, InitialQuotedIdentifiers={initialQuotedIdentifiers}.");
var aSqlParser = new TSql90Parser(initialQuotedIdentifiers);
var requestedParser = aSqlParser.Create(sqlVersion, initialQuotedIdentifiers);
IocContainer.Instance.RegisterInstance(requestedParser, Reuse.Singleton);
}
private void Parse(TSql90Parser parser, string inputScript)
{
IList<ParseError> errors;
using (StringReader sr = new StringReader(inputScript))
{
fragment = parser.Parse(sr, out errors);
}
if (errors != null && errors.Count > 0)
{
StringBuilder sb = new StringBuilder();
foreach (var error in errors)
{
sb.AppendLine(error.Message);
sb.AppendLine("offset " + error.Offset.ToString());
}
throw new ArgumentException("InvalidSQLScript", sb.ToString());
}
}
private void Parse(TSql90Parser parser, string inputScript)
{
IList <ParseError> errors;
using (StringReader sr = new StringReader(inputScript))
{
fragment = parser.Parse(sr, out errors);
}
if (errors != null && errors.Count > 0)
{
StringBuilder sb = new StringBuilder();
foreach (var error in errors)
{
sb.AppendLine(error.Message);
sb.AppendLine("offset " + error.Offset.ToString());
}
throw new ArgumentException("InvalidSQLScript", sb.ToString());
}
}
private TSqlParser CreateSQLParser()
{
TSqlParser sqlParser;
switch (ConfigurationManager.AppSettings["ParserCompatibilityLevel"])
{
case "90":
sqlParser = new TSql90Parser(true);
break;
case "100":
sqlParser = new TSql100Parser(true);
break;
case "110":
sqlParser = new TSql110Parser(true);
break;
default:
throw new ArgumentException("Invalid argument provided for [ParserCompatibilityLevel] configuration setting!");
}
return sqlParser;
}
protected override void ProcessRecord()
{
using (var reader = new StringReader(Script))
{
var parser = new TSql90Parser(false);
IList<ParseError> errors;
var result = parser.Parse(reader, out errors);
if (errors.Count == 0)
{
var visitor = new ArgumentsVisitor();
result.Accept(visitor);
WriteObject(new
{
ProcedureName = visitor.ProcedureName,
Parameters = visitor.Parameters
});
}
else
{
foreach (var error in errors)
{
var errorRecord = new ErrorRecord(
new FormatException($"{error.Message} Line:{error.Line}:{error.Column}"),
null,
ErrorCategory.InvalidArgument,
Script
);
WriteError(errorRecord);
}
}
}
}
protected override void ProcessRecord()
{
using (var reader = new StringReader(Script))
{
var parser = new TSql90Parser(false);
IList <ParseError> errors;
var result = parser.Parse(reader, out errors);
if (errors.Count == 0)
{
var visitor = new ArgumentsVisitor();
result.Accept(visitor);
WriteObject(new
{
ProcedureName = visitor.ProcedureName,
Parameters = visitor.Parameters
});
}
else
{
foreach (var error in errors)
{
var errorRecord = new ErrorRecord(
new FormatException($"{error.Message} Line:{error.Line}:{error.Column}"),
null,
ErrorCategory.InvalidArgument,
Script
);
WriteError(errorRecord);
}
}
}
}
protected override void ProcessRecord()
{
using (var reader = new StringReader(Script))
{
var parser = new TSql90Parser(false);
IList <ParseError> errors;
var result = parser.Parse(reader, out errors);
if (errors.Count == 0)
{
var visitor = new TableUsageVisitor();
result.Accept(visitor);
visitor.InsertStatements
.Where(s => s.InsertSpecification.Target is NamedTableReference).ToList()
.ForEach(s =>
{
var target = ((NamedTableReference)s.InsertSpecification.Target);
var name = AliasVisitor.ResolveAlias(s, target.SchemaObject.BaseIdentifier.Value);
WriteObject(new { Table = name, Action = "Insert" });
}
);
visitor.UpdateStatements
.Where(s => s.UpdateSpecification.Target is NamedTableReference).ToList()
.ForEach(s =>
{
var columns = s.UpdateSpecification.SetClauses
.OfType <AssignmentSetClause>()
.Where(setClause => setClause.Column != null)
.Select(setClause => setClause.Column.MultiPartIdentifier.Identifiers.Last().Value);
var target = ((NamedTableReference)s.UpdateSpecification.Target);
var name = AliasVisitor.ResolveAlias(s, target.SchemaObject.BaseIdentifier.Value);
WriteObject(new { Table = name, Action = "Update", Columns = columns });
}
);
visitor.DeleteStatements
.Where(s => s.DeleteSpecification.Target is NamedTableReference).ToList()
.ForEach(s =>
{
var target = ((NamedTableReference)s.DeleteSpecification.Target);
var name = AliasVisitor.ResolveAlias(s, target.SchemaObject.BaseIdentifier.Value);
WriteObject(new { Table = name, Action = "Delete" });
}
);
}
else
{
foreach (var error in errors)
{
var errorRecord = new ErrorRecord(
new FormatException($"{error.Message} Line:{error.Line}:{error.Column}"),
null,
ErrorCategory.InvalidArgument,
Script
);
WriteError(errorRecord);
}
}
}
}
protected override void ProcessRecord()
{
using (var reader = new StringReader(Script))
{
var parser = new TSql90Parser(false);
IList<ParseError> errors;
var result = parser.Parse(reader, out errors);
if (errors.Count == 0)
{
var visitor = new TableUsageVisitor();
result.Accept(visitor);
visitor.InsertStatements
.Where(s => s.InsertSpecification.Target is NamedTableReference).ToList()
.ForEach(s =>
{
var target = ((NamedTableReference)s.InsertSpecification.Target);
var name = AliasVisitor.ResolveAlias(s, target.SchemaObject.BaseIdentifier.Value);
WriteObject(new { Table = name, Action = "Insert" });
}
);
visitor.UpdateStatements
.Where(s => s.UpdateSpecification.Target is NamedTableReference).ToList()
.ForEach(s =>
{
var columns = s.UpdateSpecification.SetClauses
.OfType<AssignmentSetClause>()
.Where(setClause => setClause.Column != null)
.Select(setClause => setClause.Column.MultiPartIdentifier.Identifiers.Last().Value);
var target = ((NamedTableReference)s.UpdateSpecification.Target);
var name = AliasVisitor.ResolveAlias(s, target.SchemaObject.BaseIdentifier.Value);
WriteObject(new { Table = name, Action = "Update", Columns = columns });
}
);
visitor.DeleteStatements
.Where(s => s.DeleteSpecification.Target is NamedTableReference).ToList()
.ForEach(s =>
{
var target = ((NamedTableReference)s.DeleteSpecification.Target);
var name = AliasVisitor.ResolveAlias(s, target.SchemaObject.BaseIdentifier.Value);
WriteObject(new { Table = name, Action = "Delete" });
}
);
}
else
{
foreach (var error in errors)
{
var errorRecord = new ErrorRecord(
new FormatException($"{error.Message} Line:{error.Line}:{error.Column}"),
null,
ErrorCategory.InvalidArgument,
Script
);
WriteError(errorRecord);
}
}
}
}
private void SQLParser90(bool quotedIdentifier, string inputScript)
{
TSql90Parser parser90 = new TSql90Parser(quotedIdentifier);
Parse(parser90, inputScript);
}
private void SQLParser90(bool quotedIdentifier, string inputScript)
{
TSql90Parser parser90 = new TSql90Parser(quotedIdentifier);
Parse(parser90, inputScript);
}
/// <summary>
/// A simple T-SQL comment cleaner based on the SQLDOM that ships with SQL Management Studio / DACFX.
/// After parsing the input file, we just loop through the input token stream, ignore any comment related token
/// and write out the other tokens to the output file.
/// </summary>
/// <param name="sourceFile"></param>
/// <param name="destFile"></param>
/// <param name="compatLevel"></param>
/// <returns></returns>
static bool CleanSQLScript(
string sourceFile,
string destFile,
int compatLevel)
{
using (var srcRdr = new StreamReader(sourceFile))
{
TSqlParser parser;
switch (compatLevel)
{
case 80:
{
// SQL 2000
parser = new TSql80Parser(true);
break;
}
case 90:
{
// SQL 2005
parser = new TSql90Parser(true);
break;
}
case 100:
{
// SQL 2008 / R2
parser = new TSql100Parser(true);
break;
}
case 110:
{
// SQL 2012
parser = new TSql110Parser(true);
break;
}
case 120:
{
// SQL 2014
parser = new TSql120Parser(true);
break;
}
case 130:
{
// SQL 2016
parser = new TSql130Parser(true);
break;
}
case 140:
{
// SQL 2017
parser = new TSql140Parser(true);
break;
}
default:
{
parser = new TSql110Parser(true);
break;
}
}
IList <ParseError> errors;
var tree = parser.Parse(srcRdr, out errors);
if (errors.Count > 0)
{
// TODO report parse errors
Console.WriteLine("Errors when parsing T-SQL");
return(false);
}
using (var writer = new StreamWriter(destFile))
{
foreach (var tok in tree.ScriptTokenStream)
{
// ignore all comments
if (tok.TokenType != TSqlTokenType.MultilineComment &&
tok.TokenType != TSqlTokenType.SingleLineComment)
{
writer.Write(tok.Text);
}
}
writer.Flush();
writer.Close();
}
}
return(true);
}
