public static string FormatSqlStatement(string sqlStatement)
{
_parseErrors = null;
sqlStatement = sqlStatement.Replace("?", _dynamicPlaceholder);
var fragment = _parser.Parse(new StringReader(sqlStatement), out _parseErrors);
if (_parseErrors.Count == 0)
{
var result = fragment.GetText().Replace(_dynamicPlaceholder, "?");
return(fragment.GetText());
}
return(null);
}
public static void Main(string[] args)
{
TSqlFragment parseResult;
IList <ParseError> errors;
TSql100Parser parser = new TSql100Parser(true);
using (TextReader reader = new StringReader(SQL))
{
parseResult = parser.Parse(reader, out errors);
}
if (errors.Count > 0)
{
Console.Error.WriteLine("===== Parse Error =====");
foreach (ParseError errorItem in errors)
{
Console.Error.WriteLine(errorItem.Message);
}
return;
}
Console.WriteLine("===== Parse Success =====");
MySampleVisitor visitor = new MySampleVisitor(SQL);
parseResult.Accept(visitor);
}
public string Parse(string sqlSelect, out string fields, out string from, out string groupby, out string where, out string having, out string orderby)
{
TSql100Parser parser = new TSql100Parser(false);
TextReader rd = new StringReader(sqlSelect);
IList <ParseError> errors;
var fragments = parser.Parse(rd, out errors);
fields = string.Empty;
from = string.Empty;
groupby = string.Empty;
where = string.Empty;
orderby = string.Empty;
having = string.Empty;
if (errors.Count > 0)
{
var retMessage = string.Empty;
return(retMessage);
}
try
{
}
catch (Exception ex)
{
return(ex.ToString());
}
return(string.Empty);
}
protected bool Parse(out IScriptFragment fragment)
{
TSql100Parser parser = new TSql100Parser(false);
IList <ParseError> errors;
fragment = parser.Parse(new StringReader(QueryToValidate), out errors);
if (errors != null && errors.Count > 0)
{
List <string> errorList = new List <string>();
foreach (var error in errors)
{
if (ErrorMessageList == null)
{
ErrorMessageList = new List <string>();
}
ErrorMessageList.Add(error.Message);
}
}
if (ErrorMessageList == null)
{
return(true);
}
else
{
return(false);
}
}
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);
}
}
public static TSqlScript Convert(string sql, out string prettySql)
{
var hasQuotedIdentifiers = false;
var parser = new TSql100Parser(hasQuotedIdentifiers);
var options = new SqlScriptGeneratorOptions();
options.SqlVersion = SqlVersion.Sql100;
options.KeywordCasing = KeywordCasing.Uppercase;
options.MultilineSelectElementsList = true;
options.MultilineWherePredicatesList = true;
IScriptFragment fragment;
IList<ParseError> errors;
using (var sr = new StringReader(sql))
{
fragment = parser.Parse(sr, out errors);
}
if (errors != null && errors.Count > 0)
{
var errorMessage = new StringBuilder();
foreach (var error in errors)
{
errorMessage.Append(error.Message);
errorMessage.Append("offset " + error.Offset);
}
throw new ApplicationException(errorMessage.ToString());
}
new Sql100ScriptGenerator(options).GenerateScript(fragment, out prettySql);
var parsedSql = fragment as TSqlScript;
return parsedSql;
}
private static void Parse(string query)
{
Console.WriteLine(@"------------------------------------------");
Console.WriteLine(@"Parsing statement ""{0}""", query);
var parser = new TSql100Parser(false);
IList <ParseError> errors;
IScriptFragment result = parser.Parse(new StringReader(query), out errors);
if (errors.Count > 0)
{
Console.WriteLine(@"Errors encountered: ""{0}""", errors[0].Message);
return;
}
TSqlStatement statement = ((TSqlScript)result).Batches[0].Statements[0];
if (statement is SelectStatement)
{
TableSource tableSource = (((QuerySpecification)((SelectStatement)statement).QueryExpression).FromClauses[0]);
Console.WriteLine(@"Top level FROM clause at Line {0}, Column {1} (Character Offset {2})",
tableSource.StartLine, tableSource.StartColumn, tableSource.StartOffset);
Console.WriteLine();
Console.WriteLine();
}
}
private static bool ContainsSyntaxErrors(string sql)
{
var parser = new TSql100Parser(true);
IList<ParseError> errors;
parser.Parse(new StringReader(sql), out errors);
return errors.Count > 0;
}
public List <string> Parse(string sql)
{
this.UpdateStatus(new Dictionary <string, string> {
{ "clearQueryBoxErrors", "" }
});
TSql100Parser parser = new TSql100Parser(false);
TSqlFragment fragment;
IList <ParseError> errors;
fragment = parser.Parse(new StringReader(sql), out errors);
if (errors != null && errors.Count > 0)
{
List <string> errorList = new List <string>();
foreach (var error in errors)
{
errorList.Add(string.Format("Line {0} Column {1} {2}", error.Line, error.Column, error.Message));
Dictionary <string, string> newError = new Dictionary <string, string>()
{
{ "highlightErrors", string.Format("{0},{1}", error.Line, error.Column) }
};
this.UpdateStatus(newError);
}
return(errorList);
}
return(null);
}
private TableNameDeclareCheckVisitor visitTSql(string tsql)
{
TSqlParser parser = new TSql100Parser(false);
IList <ParseError> errors;
TextReader reader = new StringReader(tsql);
TSqlFragment parsed = parser.Parse(reader, out errors);
if (errors.Count != 0)
{
string errorMessage = "";
foreach (ParseError error in errors)
{
errorMessage = errorMessage + "\n" + error.Line + " : " + error.Message;
}
throw new Exception("パース失敗\n" + errorMessage);
}
TableNameDeclareCheckVisitor visitor = new TableNameDeclareCheckVisitor();
parsed.Accept(visitor);
return(visitor);
}
// Main run method
private IList <RuleProblem> Run(TextReader input, IList <Rule> rules)
{
List <RuleProblem> problems = new List <RuleProblem>();
var parser = new TSql100Parser(true);
var parseErrors = new List <ParseError>() as IList <ParseError>;
IScriptFragment scriptFragment = parser.Parse(input, out parseErrors);
if (parseErrors.Count > 0)
{
// TODO: do custom exception
var error = parseErrors[0];
var ex = new ArgumentException(error.Message);
throw ex;
}
var context = new RuleContext
{
ScriptFragment = scriptFragment,
};
foreach (var rule in rules)
{
var rulePoblems = rule.Analyze(context);
problems.AddRange(rulePoblems);
}
return(problems);
}
public static List <string> Parse(string sql)
{
var parser = new TSql100Parser(false);
IList <ParseError> errors;
var fragment = parser.Parse(new StringReader(sql), out errors);
return(errors.Select(e => e.Message).ToList());
}
protected void ParseSql(string sqlCommandText)
{
TSql100Parser parser = new TSql100Parser(false);
parser.Parse(new StringReader(sqlCommandText), out IList <ParseError> errors);
File.WriteAllText(@"C:\Development\Tools & Scripts\Prototypes\EntityModel\GeneratedSql.txt", sqlCommandText);
Assert.IsFalse(errors.Any(), "Incorrect SQL generated: '{0}'");
}
/// <summary>
/// 校验SQL语句
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static bool IsIncorrectSql(string sql)
{
TSql100Parser parser = new TSql100Parser(false);
IList <ParseError> errors = new List <ParseError>();
using (StringReader reader = new StringReader(sql))
{
parser.Parse(reader, out errors);
return(errors.Count > 0);
}
}
public List <string> Parse(string sql)
{
var parser = new TSql100Parser(false);
parser.Parse(new StringReader(sql), out var errors);
if (errors == null || errors.Count <= 0)
{
return(null);
}
return(errors.Select(error => error.Message).ToList());
}
private TSqlScript Parse(string fileName)
{
var parser = new TSql100Parser(true);
var parseErrors = new List<ParseError>() as IList<ParseError>;
TSqlScript script;
using (var sr = new StreamReader(GetFilePath(fileName)))
{
script = parser.Parse(sr, out parseErrors) as TSqlScript;
}
return script;
}
private TSqlScript Parse(string fileName)
{
var parser = new TSql100Parser(true);
var parseErrors = new List <ParseError>() as IList <ParseError>;
TSqlScript script;
using (var sr = new StreamReader(GetFilePath(fileName)))
{
script = parser.Parse(sr, out parseErrors) as TSqlScript;
}
return(script);
}
public IEnumerable <string> Parse(string sqlSelect)
{
TSql100Parser parser = new TSql100Parser(false);
TextReader rd = new StringReader(sqlSelect);
IList <ParseError> errors;
var columns = new List <string>();
var fragments = parser.Parse(rd, out errors);
var columnVisitor = new SQLVisitor();
fragments.Accept(columnVisitor);
columns = new List <string>(columnVisitor.Columns);
return(columns);
}
public void ValidSqlInsertGenerated()
{
var entityPropertyCollection = CreateEntityPropertyCollection();
entityPropertyCollection.Properties = new[] { CreateNameProperty(), CreateImportCodeProperty() };
entityPropertyCollection.PrimaryEntityColumnName = null;
entityPropertyCollection.PrimaryEntityIds = null;
entityPropertyCollection.PrimaryEntityTableName = null;
var command = PriceNetSqlGenerator.GenerateInsertCommand(entityPropertyCollection);
TSql100Parser parser = new TSql100Parser(false);
parser.Parse(new StringReader(command.CommandText), out IList <ParseError> errors);
Assert.IsFalse(errors.Any());
}
public List<string> Parse(string sql)
{
TSql100Parser parser = new TSql100Parser(false);
IScriptFragment fragment;
IList<ParseError> errors;
fragment = parser.Parse(new StringReader(sql), out errors);
if (errors != null && errors.Count > 0)
{
List<string> errorList = new List<string>();
foreach (var error in errors)
{
errorList.Add(error.Message);
}
return errorList;
}
return null;
}
public List <string> Parse()
{
TSql100Parser parser = new TSql100Parser(false);
IScriptFragment fragment;
IList <ParseError> errors;
fragment = parser.Parse(new StringReader(this.sql.ToString()), out errors);
if (errors != null && errors.Count > 0)
{
List <string> errorList = new List <string>();
foreach (var error in errors)
{
errorList.Add(error.Message);
}
return(errorList);
}
return(null);
}
public List <string> Parse(string sql)
{
TSql100Parser parser = new TSql100Parser(false);
Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragment fragment;
IList <ParseError> errors;
fragment = parser.Parse(new StringReader(sql), out errors);
if (errors != null && errors.Count > 0)
{
List <string> errorList = new List <string>();
foreach (var error in errors)
{
errorList.Add(error.Message);
}
return(errorList);
}
return(null);
}
private void Parse(TSql100Parser 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(TSql100Parser 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());
}
}
public TSqlScript ParseScript(string script)
{
IList <ParseError> parseErrors;
TSql100Parser tsqlParser = new TSql100Parser(true);
TSqlFragment fragment;
using (StringReader stringReader = new StringReader(script))
{
fragment = (TSqlFragment)tsqlParser.Parse(stringReader, out parseErrors);
}
if (parseErrors.Count > 0)
{
var retMessage = string.Empty;
foreach (var error in parseErrors)
{
retMessage += error.Number + " - " + error.Message + " - position: " + error.Offset + ";\r\n";
}
Console.WriteLine(retMessage);
}
return((TSqlScript)fragment);
}
public static TSqlScript ParseScript(string script, out IList <string> errorsList)
{
IList <ParseError> parseErrors;
TSql100Parser tsqlParser = new TSql100Parser(true);
TSqlFragment fragment;
using (StringReader stringReader = new StringReader(script))
{
fragment = (TSqlFragment)tsqlParser.Parse(stringReader, out parseErrors);
}
errorsList = new List <string>();
if (parseErrors.Count > 0)
{
var retMessage = string.Empty;
foreach (var error in parseErrors)
{
retMessage += error.Column + " - " + error.Message + " - position: " + error.Offset + "; ";
}
}
return((TSqlScript)fragment);
}
private TSqlFragment Parse(TextReader reader)
{
//引数のfalseが謎。
//Specifies whether quoted identifier handling is on.
TSqlParser parser = new TSql100Parser(false);
IList <ParseError> errors;
TSqlFragment parsed;
parsed = parser.Parse(reader, out errors);
if (errors.Count != 0)
{
string errorMessage = "";
foreach (ParseError error in errors)
{
errorMessage = errorMessage + "\n" + error.Line + " : " + error.Message;
}
throw new ParseException("パース失敗\n" + errorMessage);
}
return(parsed);
}
// Main run method
private IList<RuleProblem> Run(TextReader input, IList<Rule> rules)
{
List<RuleProblem> problems = new List<RuleProblem>();
var parser = new TSql100Parser(true);
var parseErrors = new List<ParseError>() as IList<ParseError>;
IScriptFragment scriptFragment = parser.Parse(input, out parseErrors);
if (parseErrors.Count > 0)
{
// TODO: do custom exception
var error = parseErrors[0];
var ex = new ArgumentException(error.Message);
throw ex;
}
var context = new RuleContext
{
ScriptFragment = scriptFragment,
};
foreach (var rule in rules)
{
var rulePoblems = rule.Analyze(context);
problems.AddRange(rulePoblems);
}
return problems;
}
public bool validated(string sql)
{
bool valid = true;
TSql100Parser parser = new TSql100Parser(false);
IScriptFragment fragment;
IList<ParseError> errors;
fragment = parser.Parse(new StringReader(sql), out errors);
if (errors != null && errors.Count > 0)
{
valid = false;
//List<string> errorList = new List<string>();
foreach (var error in errors)
{
//errorList.Add(error.Message);
Console.WriteLine(error.Message);
}
}
return valid;
}
/// <summary>
/// Determines whether the specified SQL is parameterized.
/// </summary>
/// <param name="sql">The SQL.</param>
/// <returns><c>true</c> if the specified SQL is parameterized; otherwise, <c>false</c>.</returns>
public static bool IsParameterized(string sql)
{
var ret = false;
using (var reader = new StringReader(sql))
{
IList <ParseError> errors;
var parser = new TSql100Parser(true);
var script = parser.Parse(reader, out errors) as TSqlScript;
if (script != null)
{
foreach (var batch in script.Batches)
{
foreach (var statement in batch.Statements)
{
if (statement is SelectStatement)
{
var selectStatement = (SelectStatement)statement;
if (selectStatement.QueryExpression is QuerySpecification)
{
ret = HasLiterals(((QuerySpecification)selectStatement.QueryExpression).WhereClause);
}
else if (selectStatement.QueryExpression is BinaryQueryExpression) //union
{
ret = CheckBinaryQuery((BinaryQueryExpression)selectStatement.QueryExpression);
}
}
else if (statement is DeleteStatement)
{
ret = HasLiterals(((DeleteStatement)statement).DeleteSpecification.WhereClause);
}
else if (statement is UpdateStatement)
{
ret = HasLiterals(((UpdateStatement)statement).UpdateSpecification.WhereClause);
}
else if (statement is InsertStatement)
{
var insertStatement = statement as InsertStatement;
if (insertStatement.InsertSpecification.InsertSource is SelectInsertSource)
{
var selectInsertSource = (SelectInsertSource)insertStatement.InsertSpecification.InsertSource;
if (selectInsertSource.Select is QuerySpecification)
{
ret = HasLiterals(((QuerySpecification)selectInsertSource.Select).WhereClause);
}
else if (selectInsertSource.Select is BinaryQueryExpression)
{
ret = CheckBinaryQuery((BinaryQueryExpression)selectInsertSource.Select);
}
}
else
{
ret = false;
}
}
if (ret)
{
break;
}
//Console.WriteLine(statement.ToString());
}
}
}
}
return(!ret);
}
/// <summary>
/// Simplifies a query by removing the value of certain types of token. Basically the ones that might be parameterised
/// </summary>
/// <param name="query"></param>
/// <param name="value"></param>
/// <returns></returns>
public static bool TrySimplify(string query, out string value)
{
value = "";
var parser = new TSql100Parser(true);
IList<ParseError> errors;
var result = parser.Parse(new StringReader(query), out errors);
// cannot parse, cannot simplify
if (errors.Count > 0)
{
LastError = "Cannot parse";
return false;
}
// without at least one batch with at least one statement, cannot simplify
// (should be 1 batch, 1 statement really as we are tracing StatementEnd)
var script = result as TSqlScript;
if (script == null || script.Batches.Count <= 0 && script.Batches[0].Statements.Count <= 0)
{
LastError = "Not 1 batch 1 statement";
return false;
}
// only interested in certain types of statements (date manipulation ones)
if (!HandledStatementType.Contains(script.Batches[0].Statements[0].GetType()))
{
LastError = "Not handled statement";
return false;
}
// basically remove all comments, newlines and extra whitespace
var options = new SqlScriptGeneratorOptions
{
AlignClauseBodies = false,
AlignColumnDefinitionFields = false,
AlignSetClauseItem = false,
AsKeywordOnOwnLine = false,
IncludeSemicolons = false,
IndentSetClause = false,
IndentViewBody = false,
NewLineBeforeCloseParenthesisInMultilineList = false,
NewLineBeforeFromClause = false,
NewLineBeforeGroupByClause = false,
NewLineBeforeHavingClause = false,
NewLineBeforeJoinClause = false,
NewLineBeforeOpenParenthesisInMultilineList = false,
NewLineBeforeOrderByClause = false,
NewLineBeforeOutputClause = false,
NewLineBeforeWhereClause = false,
MultilineInsertSourcesList = false,
MultilineInsertTargetsList = false,
MultilineSelectElementsList = false,
MultilineSetClauseItems = false,
MultilineViewColumnsList = false,
MultilineWherePredicatesList = false
};
var generator = new Sql100ScriptGenerator(options);
var tokens = generator.GenerateTokens(script);
var summary = new StringBuilder();
foreach (var token in tokens)
{
// replace values for parameterisable token types
if(ReplaceTokens.Contains(token.TokenType))
{
summary.Append("?");
}
else
{
summary.Append(token.Text);
}
}
// trim some junk
value = summary.ToString().TrimEnd(RemoveChars);
return true;
}
static void parseScriptDom()
{
TSql100Parser parser = new TSql100Parser(true);
IList<ParseError> parseErrors;
TSqlFragment result = parser.Parse(new StringReader(filename),
out parseErrors);
TSqlScript SqlScript = result as TSqlScript;
StreamWriter sw = new StreamWriter(filenameoutput);
foreach (TSqlBatch sqlBatch in SqlScript.Batches)
{
foreach (TSqlStatement sqlStatement in sqlBatch.Statements)
{
sw.Write(sqlStatement.ToString());
}
}
sw.Close();
}
public override QvxDataTable ExtractQuery(string query, List <QvxTable> qvxTables)
{
QvxDataTable returnTable = null;
IList <ParseError> errors = null;
var parser = new TSql100Parser(true);
TSqlScript script;
using (TextReader reader = new StringReader(query))
{
script = parser.Parse(reader, out errors) as TSqlScript;
}
IList <TSqlParserToken> tokens = script.ScriptTokenStream;
// get record folder
TSqlParserToken fromToken = tokens.First(t => t.TokenType == TSqlTokenType.From);
int indexOfFromToken = tokens.IndexOf(fromToken);
IEnumerable <TSqlParserToken> tableTokens = tokens.Skip(indexOfFromToken);
TSqlParserToken identifier = tableTokens.First(t => t.TokenType == TSqlTokenType.Identifier || t.TokenType == TSqlTokenType.AsciiStringOrQuotedIdentifier);
string folderName = identifier.Text;
if (identifier.TokenType == TSqlTokenType.AsciiStringOrQuotedIdentifier)
{
folderName = folderName.Substring(1, folderName.Length - 2);
}
// get report name
tableTokens = tokens.Skip(tokens.IndexOf(identifier));
TSqlParserToken reportSeparator = tableTokens.First(t => t.TokenType == TSqlTokenType.Dot);
tableTokens = tokens.Skip(tokens.IndexOf(reportSeparator));
TSqlParserToken reportNameToken = tableTokens.First(t => t.TokenType == TSqlTokenType.Identifier || t.TokenType == TSqlTokenType.AsciiStringOrQuotedIdentifier);
string reportName = reportNameToken.Text;
if (reportNameToken.TokenType == TSqlTokenType.AsciiStringOrQuotedIdentifier)
{
reportName = reportName.Substring(1, reportName.Length - 2);
}
if (this.MParameters.ContainsKey("folder_name"))
{
if (folderName == this.MParameters["folder_name"] && this.MTables == null)
{
this.Init();
}
else if (folderName != this.MParameters["folder_name"])
{
this.MParameters["folder_name"] = folderName;
this.Init();
}
}
else
{
this.MParameters.Add("folder_name", folderName);
this.Init();
}
var newTable = this.FindTable(reportName, this.MTables);
returnTable = new QvxDataTable(newTable);
return(returnTable);
}
static void test()
{
StreamReader sr = new StreamReader(filename);
string parseSourceCode = sr.ReadToEnd();
String[] queries = parseSourceCode.Split(new string[] { "GO" }, StringSplitOptions.RemoveEmptyEntries);
StreamWriter sw = new StreamWriter(filenameoutput);
foreach (String q in queries)
{
String qq = q.Replace('\r', ' ').Replace('\n', ' ');
var parser = new TSql100Parser(true);
System.IO.TextReader txtRdr = new System.IO.StringReader(qq.Trim());
IList<ParseError> parseErrors = new List<ParseError>();
var fragment = parser.Parse(txtRdr, out parseErrors);
if (fragment != null)
{
var tSqlScript = ((TSqlScript)(fragment));
var tokens = tSqlScript.ScriptTokenStream;
try
{
var stmts = tSqlScript.Batches.FirstOrDefault().Statements;
foreach (TSqlStatement stmt in stmts)
{
if (stmt is SelectStatement)
{
SelectStatement s = (SelectStatement)stmt;
sw.Write(s.ToString());
}
else if (stmt is AlterTableAddTableElementStatement)
{
AlterTableAddTableElementStatement s = (AlterTableAddTableElementStatement)stmt;
SQlVisitor v = new SQlVisitor(sw);
v.Visit(s);
}
else
if (stmt is CreateTableStatement)
{
CreateTableStatement s = (CreateTableStatement)stmt;
SQlVisitor v = new SQlVisitor(sw);
v.Visit(s,tokens);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
}
sw.Close();
}