public static void ParseSQL()
{
var sql = @"select AreaId = A.mcw_areaId, SurrogateKey = A.AreaKey, Code = S.statecode, Name = S.statename From CRM.dim_Area as A inner join CRM.dim_AreaState as S ON A.statecode = S.statecode ; DELET blogs SET url = 'aaa' where url='dasfds'";
//@"select p.firstname, p.lastname, p.custid FROM persons as p ;
//SELECT id, name FROM companies;
//select s.test from (select 'hello' as test) as s; ";
TSqlParser parser = new TSql120Parser(true);
IList <ParseError> parseErrors;
var tReader = new StringReader(sql);
TSqlFragment sqlFragment = parser.Parse(tReader, out parseErrors);
var queryTokens = parser.GetTokenStream(tReader, out parseErrors);
if (parseErrors.Count > 0)
{
Console.WriteLine("Errors:");
}
parseErrors.Select(e => e.Message.Indent(2)).ToList().ForEach(Console.WriteLine);
OwnVisitor visitor = new OwnVisitor();
sqlFragment.Accept(visitor);
// sqlFragment.AcceptChildren(visitor);
Console.WriteLine("Done.");
Console.ReadKey();
}
public IList <ParseError> TryGetTSqlFragment(out TSqlFragment fragment)
{
IList <ParseError> errors;
if (this.tsqlFragment != null)
{
fragment = this.tsqlFragment;
return(null);
}
errors = null;
fragment = null;
lock (this.lockObject)
{
if (this.tsqlFragment == null)
{
var parser = new TSql120Parser(initialQuotedIdentifiers: false);
using (StringReader reader = new StringReader(File.ReadAllText(TargetUri.LocalPath)))
{
this.tsqlFragment = parser.Parse(reader, out errors);
}
}
}
fragment = this.tsqlFragment;
return(errors);
}
public SqlRuleVisitor(IConfigReader configReader, IReporter reporter)
{
Parser = new TSql120Parser(true);
Violations = new List <RuleViolation>();
RuleVisitorBuilder = new RuleVisitorBuilder(configReader, reporter);
Reporter = reporter;
}
private static TSqlScript getSqlFragment(string tSql, string sqlHash)
{
TSqlScript sqlFragment;
if (_sqlFragments.TryGetValue(sqlHash, out sqlFragment))
{
return(sqlFragment);
}
IList <ParseError> errors;
using (var reader = new StringReader(tSql))
{
var parser = new TSql120Parser(initialQuotedIdentifiers: true);
sqlFragment = (TSqlScript)parser.Parse(reader, out errors);
}
if (errors == null || !errors.Any())
{
_sqlFragments.TryAdd(sqlHash, sqlFragment);
return(sqlFragment);
}
var sb = new StringBuilder();
sb.AppendLine(tSql);
sb.AppendLine("Errors:");
foreach (var error in errors)
{
sb.AppendLine(error.Message);
}
throw new InvalidOperationException(sb.ToString());
}
public static void Parser1(string sql)
{
var sb = new StringBuilder();
var tSql120Parser = new TSql120Parser(true);
IList <ParseError> list;
var fragment = tSql120Parser.Parse(new StringReader(sql), out list);
var flag = list.Any();
if (flag)
{
foreach (var current in list)
{
sb.AppendLine($"error {current.Number}: ({current.Line},{current.Column}): {current.Message}");
}
Console.WriteLine(sb.ToString());
}
else
{
try
{
//this.EmitFragment(0, fragment);
Console.WriteLine();
}
catch (Exception ex)
{
Console.WriteLine();
}
}
}
private IList <string> ModifySteps(IList <string> steps)
{
var parser = new TSql120Parser(true);
var modifySteps = new List <string>();
foreach (var step in steps)
{
IList <ParseError> errors;
var script = parser.Parse(new StringReader(step), out errors);
var visitor = new TableMigrationVisitor(_rowCount.ToString());
script.Accept(visitor);
if (string.IsNullOrEmpty(visitor.NewStatement.NewText))
{
modifySteps.Add(step);
}
else
{
var newStep = step.Replace(
step.Substring(visitor.NewStatement.StartPos, visitor.NewStatement.Length),
visitor.NewStatement.NewText);
modifySteps.Add(newStep);
}
}
return(modifySteps);
}
private void Parse()
{
Errors.Text = "";
MaxDepth = Int32.Parse(DepthText.Text);
var parser = new TSql120Parser(true);
IList <ParseError> errors;
var script = parser.Parse(new StringReader(GetText()), out errors);
if (errors.Count > 0)
{
Errors.Text = "";
foreach (var e in errors)
{
Errors.Text += "Error: " + e.Message + " at: " + e.Offset + "\r\n";
}
return;
}
var enumerator = new EnumeratorVisitor();
script.Accept(enumerator);
Results.Items.Clear();
foreach (var node in enumerator.Nodes)
{
foreach (var i in GetChildren(node))
{
Results.Items.Add(i);
}
}
}
private void bParse_Click(object sender, EventArgs e)
{
this.rtb2.Clear();
var tSql120Parser = new TSql120Parser(true);
IList<ParseError> list;
var fragment = tSql120Parser.Parse(new StringReader(this.rtb1.Text), out list);
var flag = list.Any<ParseError>();
if (flag)
{
this.rtb2.SelectionColor = Color.Red;
foreach (var current in list)
{
this.rtb2.AppendText(string.Format("error {0}: ({1},{2}): {3}{4}", new object[]
{
current.Number,
current.Line,
current.Column,
current.Message,
Environment.NewLine
}));
}
}
else
{
try
{
this.EmitFragment(0, fragment);
}
catch (Exception ex)
{
this.rtb2.SelectionColor = Color.Red;
this.rtb2.AppendText(ex.ToString());
}
}
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var procedure = ruleExecutionContext.ModelElement;
var script = procedure.GetScript();
var parser = new TSql120Parser(true);
IList <ParseError> errors = new List <ParseError>();
var fragment = parser.Parse(new StringReader(script), out errors);
var visitor = new TransactionVisitor();
fragment.Accept(visitor);
var dif = visitor.GetDifference();
if (dif.Any())
{
return
new List <SqlRuleProblem>()
{
new SqlRuleProblem("No balanced transaction", procedure)
}
}
;
return(new List <SqlRuleProblem>());
}
}
public IList<ParseError> TryGetTSqlFragment(out TSqlFragment fragment)
{
IList<ParseError> errors;
if (this.tsqlFragment != null)
{
fragment = this.tsqlFragment;
return null;
}
errors = null;
fragment = null;
lock (this.lockObject)
{
if (this.tsqlFragment == null)
{
var parser = new TSql120Parser(initialQuotedIdentifiers: false);
using (StringReader reader = new StringReader(File.ReadAllText(TargetUri.LocalPath)))
{
this.tsqlFragment = parser.Parse(reader, out errors);
}
}
}
fragment = this.tsqlFragment;
return errors;
}
private IList<string> ModifySteps(IList<string> steps)
{
var parser = new TSql120Parser(true);
var modifySteps = new List<string>();
foreach (var step in steps)
{
IList<ParseError> errors;
var script = parser.Parse(new StringReader(step), out errors);
var visitor = new TableMigrationVisitor(_rowCount.ToString());
script.Accept(visitor);
if (string.IsNullOrEmpty(visitor.NewStatement.NewText))
{
modifySteps.Add(step);
}
else
{
var newStep = step.Replace(
step.Substring(visitor.NewStatement.StartPos, visitor.NewStatement.Length),
visitor.NewStatement.NewText);
modifySteps.Add(newStep);
}
}
return modifySteps;
}
public string ScriptAlter(Database db)
{
bool replaced = false;
string alter = null;
if (RoutineType != RoutineKind.XmlSchemaCollection) {
IList<ParseError> errors;
TSqlFragment script = new TSql120Parser(initialQuotedIdentifiers: QuotedId).Parse(new StringReader(Text), out errors);
alter = ScriptBase(db, string.Join(string.Empty, script.ScriptTokenStream.Select(t =>
{
if (t.TokenType == TSqlTokenType.Create && !replaced)
{
replaced = true;
return "ALTER";
}
else
{
return t.Text;
}
})));
}
if (replaced)
return alter;
else
throw new Exception(string.Format("Unable to script routine {0} {1}.{2} as ALTER", RoutineType, Schema, Name));
}
private void button1_Click(object sender, EventArgs e)
{
TextReader rdr = new StringReader(textBox1.Text);
IList<ParseError> errors = null;
TSql120Parser parser = new TSql120Parser(true);
TSqlFragment tree = parser.Parse(rdr, out errors);
foreach(ParseError err in errors)
{
Console.WriteLine(err.Message);
}
MyVisitor checker = new MyVisitor();
tree.Accept(checker);
if(false == checker.containsOnlySelects)
{
MessageBox.Show("The code contains something other than SELECT statement");
}
else
{
MessageBox.Show("Looks ok!");
}
}
static void Main(string[] args)
{
TextReader txtRdr = new StringReader(@"select AreaId = A.mcw_areaId, SurrogateKey = A.AreaKey, Code = S.statecode, Name = S.statename From CRM.dim_Area as A inner join CRM.dim_AreaState as S ON A.statecode = S.statecode ; DELET FROM blogs where url='dasfds'");
//TextReader txtRdr = new StreamReader("myscriptfile.sql");
TSql120Parser parser = new TSql120Parser(true);
IList <ParseError> errors;
TSqlFragment sqlFragment = parser.Parse(txtRdr, out errors);
foreach (var err in errors)
{
Console.WriteLine(err.Message);
}
SQLVisitor myVisitor = new SQLVisitor();
sqlFragment.Accept(myVisitor);
myVisitor.DumpStatistics();
ParseSQL();
Console.ReadKey();
}
private static TSqlScript getSqlFragment(string tSql, string sqlHash)
{
TSqlScript sqlFragment;
if (_sqlFragments.TryGetValue(sqlHash, out sqlFragment))
{
return sqlFragment;
}
IList<ParseError> errors;
using (var reader = new StringReader(tSql))
{
var parser = new TSql120Parser(initialQuotedIdentifiers: true);
sqlFragment = (TSqlScript)parser.Parse(reader, out errors);
}
if (errors == null || !errors.Any())
{
_sqlFragments.TryAdd(sqlHash, sqlFragment);
return sqlFragment;
}
var sb = new StringBuilder();
sb.AppendLine(tSql);
sb.AppendLine("Errors:");
foreach (var error in errors)
sb.AppendLine(error.Message);
throw new InvalidOperationException(sb.ToString());
}
public void ScanReturnValues_ForExtraNewlinesAndWhiteSpace_Ignores()
{
// Setup
var scanner = new StoredProcedureReturnValueScanner();
var sqlParser = new TSql120Parser(false);
var outReport = new StoredProcedureReport
{
ReturnValues = new List <ReturnSqlReportEntry>()
};
var sql = SqlSamples.LotsOfWhitespace;
var parsedSql =
sqlParser.Parse(new StringReader(sql), out var parseErrors);
var script = parsedSql as TSqlScript;
var createSpStatement =
script?.Batches[0].Statements[0] as CreateProcedureStatement;
// Act
scanner.ScanReturnValues(outReport, createSpStatement);
// Assert
Assert.That(parseErrors.Count, Is.EqualTo(0));
Assert.That(outReport.ReturnValues.Count, Is.EqualTo(2));
Assert.That(outReport.ReturnValues[0].ReturnValueName, Is.EqualTo("'Hello' AS SomeName"));
Assert.That(outReport.ReturnValues[1].ReturnValueName, Is.EqualTo("'Word555' AS SomeWord"));
}
/// <summary>
/// https://docs.microsoft.com/ru-ru/dotnet/api/microsoft.sqlserver.transactsql.scriptdom?view=sql-dacfx-140.3881.1
/// SQL Scripts formatter
/// </summary>
/// <param name="script">query string</param>
/// <returns></returns>
public static string FormatTSQLScript(string script)
{
var query = script;
var parser = new TSql120Parser(false);
IList <ParseError> errors;
var parsedQuery = parser.Parse(new StringReader(query), out errors);
if (errors.Count > 0)
{
foreach (var err in errors)
{
ShowConsoleMessage(err.Message, ConsoleColor.Red, false);
}
}
var generator = new Sql120ScriptGenerator(new SqlScriptGeneratorOptions()
{
KeywordCasing = KeywordCasing.Uppercase,
IncludeSemicolons = true,
NewLineBeforeFromClause = true,
NewLineBeforeOrderByClause = true,
NewLineBeforeWhereClause = true,
AlignClauseBodies = false
});
string formattedQuery;
generator.GenerateScript(parsedQuery, out formattedQuery);
return(formattedQuery);
}
public IEnumerable<string> ParseErrors(string sql) {
var parser = new TSql120Parser(false);
using (var reader = new StringReader(sql)) {
IList<ParseError> errors;
parser.Parse(reader, out errors);
foreach (var error in errors) {
yield return error.Message;
}
}
}
private string GetFunctionBody(string sqlText)
{
var functionBody = new CodeWriter();
var parser = new TSql120Parser(false);
var statementList = new StatementList();
IList <ParseError> errors;
var script2 = parser.Parse(new StringReader(sqlText), out errors) as TSqlScript;
if (errors.Count > 0)
{
var errorList = new StringBuilder();
foreach (var error in errors)
{
errorList.AppendLine($"{error.Message}<br/>");
}
throw new ApplicationException(errorList.ToString());
}
var scriptGen = new Sql120ScriptGenerator();
foreach (var batch2 in script2.Batches)
{
foreach (var statement in batch2.Statements)
{
var createFunctionStatement = statement as CreateFunctionStatement;
if (createFunctionStatement == null)
{
continue;
}
if (createFunctionStatement.StatementList == null)
{
continue;
}
foreach (var statement2 in createFunctionStatement.StatementList.Statements)
{
string scriptOut;
scriptGen.GenerateScript(statement2, out scriptOut);
if (statement2 is MergeStatement && !scriptOut.EndsWith(";"))
{
scriptOut += ";";
}
functionBody.WriteLine(scriptOut);
}
}
}
return(functionBody.ToString());
}
public List <ITable> GetDataTables()
{
var tables = new List <ITable>();
using (var reader = GetScriptReader())
{
var parser = new TSql120Parser(true);
IList <ParseError> errors;
TSqlFragment sqlFragment = parser.Parse(reader, out errors);
if (errors.Count > 0)
{
if (DebugLogging.Enable)
{
OutputWindowMessage.WriteMessage("Script Parser: Script file: \"{0}\" contains errors:", _path);
}
foreach (var error in errors)
{
OutputWindowMessage.WriteWarning(_path, error.Line, "Script Parser: Error in {0} error: {1}", _path, error.Message);
}
ContainsErrors = true;
}
var visitor = new MergeVisitor();
sqlFragment.Accept(visitor);
foreach (var merge in visitor.Merges)
{
try
{
if (DebugLogging.Enable)
{
DumpMergeText(merge);
}
tables.Add(new MergeStatementParser(merge).GetDescriptor(_path, _project));
}
catch (MergeStatamentParsingException msp)
{
OutputWindowMessage.WriteMessage("Unable to read table from the script file: \"{1}\" - error message: \"{1}\" ", _path);
}
}
}
if (ContainsErrors)
{
OutputWindowMessage.WriteMessage("Errors were encountered parsing the script file - correct errors to save changes");
}
return(tables);
}
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);
}
private bool IsCreateOrAlterIndex(string script)
{
var parser = new TSql120Parser(true);
IList <ParseError> errors;
var fragment = parser.Parse(new StringReader(script), out errors);
var visitor = new IndexVisitior();
fragment.Accept(visitor);
return(visitor.ContainsIndexChange());
}
public List<ITable> GetDataTables()
{
var tables = new List<ITable>();
using (var reader = GetScriptReader())
{
var parser = new TSql120Parser(true);
IList<ParseError> errors;
TSqlFragment sqlFragment = parser.Parse(reader, out errors);
if (errors.Count > 0)
{
if (DebugLogging.Enable)
{
OutputWindowMessage.WriteMessage("Script Parser: Script file: \"{0}\" contains errors:", _path);
}
foreach (var error in errors)
{
OutputWindowMessage.WriteWarning(_path, error.Line, "Script Parser: Error in {0} error: {1}", _path, error.Message);
}
ContainsErrors = true;
}
var visitor = new MergeVisitor();
sqlFragment.Accept(visitor);
foreach (var merge in visitor.Merges)
{
try
{
if (DebugLogging.Enable)
{
DumpMergeText(merge);
}
tables.Add(new MergeStatementParser(merge).GetDescriptor(_path, _project));
}
catch (MergeStatamentParsingException msp)
{
OutputWindowMessage.WriteMessage("Unable to read table from the script file: \"{1}\" - error message: \"{1}\" ", _path);
}
}
}
if (ContainsErrors)
{
OutputWindowMessage.WriteMessage("Errors were encountered parsing the script file - correct errors to save changes");
}
return tables;
}
public static IEnumerable <string> ValidateSql(this string str)
{
if (string.IsNullOrWhiteSpace(str))
{
return(new[] { "SQL query should be non empty." });
}
var parser = new TSql120Parser(false);
IList <ParseError> errors;
parser.Parse(new StringReader(str), out errors);
return(errors.Select(err => err.Message));
}
public IEnumerable <string> ParseErrors(string sql)
{
var parser = new TSql120Parser(false);
using (var reader = new StringReader(sql)) {
IList <ParseError> errors;
parser.Parse(reader, out errors);
foreach (var error in errors)
{
yield return(error.Message);
}
}
}
public EqualityJoin(string qualifiedObjectNameLeft, string qualifiedObjectNameRight)
{
var parser = new TSql120Parser(initialQuotedIdentifiers: false);
IList<ParseError> errors;
using (var reader = new StringReader(qualifiedObjectNameLeft))
{
_left = parser.ParseSchemaObjectName(reader, out errors);
}
using (var reader = new StringReader(qualifiedObjectNameRight))
{
_right = parser.ParseSchemaObjectName(reader, out errors);
}
}
private void Parse()
{
Errors.Text = "";
MaxDepth = Int32.Parse(DepthText.Text);
var parser = new TSql120Parser(true);
IList <ParseError> errors;
var script = parser.Parse(new StringReader(GetText()), out errors);
if (errors.Count > 0)
{
Errors.Text = "";
foreach (var e in errors)
{
Errors.Text += "Error: " + e.Message + " at: " + e.Offset + "\r\n";
}
return;
}
var enumerator = new EnumeratorVisitor();
script.Accept(enumerator);
Results.Items.Clear();
foreach (var node in enumerator.Nodes)
{
foreach (var i in GetChildren(node))
{
Results.Items.Add(i);
}
}
Tokens.Items.Clear();
var newItem = new TreeViewItem();
newItem.Header = "Tokens";
newItem.IsExpanded = true;
foreach (var t in script.ScriptTokenStream)
{
var newChild = new TreeViewItem();
newChild.Header = string.Format("{0} : {1} : {2} : {3}", t.TokenType, t.Text, t.Offset, t.Column);
newItem.Items.Add(newChild);
newChild.Tag = t;
}
Tokens.Items.Add(newItem);
}
public EqualityJoin(string qualifiedObjectNameLeft, string qualifiedObjectNameRight)
{
var parser = new TSql120Parser(initialQuotedIdentifiers: false);
IList <ParseError> errors;
using (var reader = new StringReader(qualifiedObjectNameLeft))
{
_left = parser.ParseSchemaObjectName(reader, out errors);
}
using (var reader = new StringReader(qualifiedObjectNameRight))
{
_right = parser.ParseSchemaObjectName(reader, out errors);
}
}
public static IProcedureDefinition ParseProcedure(string sqlScript)
{
var SqlParser = new TSql120Parser(false);
IList <ParseError> parseErrors;
var result = SqlParser.Parse(new StringReader(sqlScript),
out parseErrors);
if (parseErrors.Count > 0)
{
throw new SQLParseException("SqlParseError");
}
return(new ProcedureDefinition(result as TSqlScript));
}
internal static bool IsSqlValid(this string str)
{
if (string.IsNullOrWhiteSpace(str))
{
return(false);
}
var parser = new TSql120Parser(false);
IList <ParseError> errors;
using (var reader = new StringReader(str))
{
parser.Parse(reader, out errors);
}
return(errors.Count() == 0);
}
static void Main(string[] args)
{
TSql120Parser parser = new TSql120Parser(false);
IList <ParseError> errors;
using (StringReader sr = new StringReader(@"create table t1 (c1 int primary key)
GO
create table t2 (c1 int primary key)"))
{
TSqlFragment fragment = parser.Parse(sr, out errors);
IEnumerable <string> batches = GetBatches(fragment);
foreach (var batch in batches)
{
Console.WriteLine(batch);
}
}
}
static void Main(string[] args)
{
//var scriptText = "SELECT (SELECT a.ID FROM LastLogin a) [T], * FROM dbo.People p FULL OUTER JOIN ThirdTable ON ThirdTable.ID = p.ID LEFT OUTER JOIN Employees e ON e.PeopleID = p.ID WHERE p.Name IN (SELECT c.Name FROM Contacts c) AND p.ID = @A;";
var scriptText =
@"UPDATE People SET FirstName = 'Daniel';
SELECT TOP 500 * FROM #TempTable
";
var parser = new TSql120Parser(true);
var rules = Rules.RuleFactory.GetAllRules();
var engine = new Logic.RuleEngine(rules);
var warnings = engine.GetWarnings(scriptText, parser);
if (warnings.ParseErrors.Count > 0)
{
Console.WriteLine("*** Parse Errors: ***");
foreach (var parseError in warnings.ParseErrors)
{
var output =
string.Format("Line: {0}; Column: {1}; Warning: {2}",
parseError.Line,
parseError.Column,
parseError.Message);
Console.WriteLine(output);
}
}
if (warnings.Warnings.Length > 0)
{
Console.WriteLine("*** Warnings: ***");
foreach (var warning in warnings.Warnings)
{
var output =
string.Format("Line: {0}; Column: {1}; Length: {2}; Warning: {3}",
warning.Line,
warning.Column,
warning.Length,
warning.Warning);
Console.WriteLine(output);
}
}
Console.ReadLine();
}
static void Main(string[] args)
{
IList<ParseError> errors = null;
TextReader reader = new StreamReader(@"..\..\..\SQL\CreateProc_HumanResources_uspUpdateEmployeeHireInfo.sql");
TSql120Parser parser = new TSql120Parser(true);
TSqlFragment tree = parser.Parse(reader, out errors);
foreach (ParseError err in errors)
{
Console.WriteLine(err.Message);
}
tree.Accept(new myVisitor());
reader.Dispose();
}
public void Parse(String SQL)
{
TSql120Parser SqlParser = new TSql120Parser(false);
IList <ParseError> parseErrors;
TSqlFragment result = SqlParser.Parse(new StringReader(SQL),
out parseErrors);
TSqlScript SqlScript = result as TSqlScript;
foreach (TSqlBatch sqlBatch in SqlScript.Batches)
{
foreach (TSqlStatement sqlStatement in sqlBatch.Statements)
{
ProcessViewStatementBody(sqlStatement);
}
}
}
{ //Parser error message
public static List <string> Parse(string sql)
{
TSql120Parser parser = new TSql120Parser(false);
IList <ParseError> errors;
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(new List <string>());;
}
private string GetTableTypeBody(string sqlText)
{
var createTableTypeBody = new CodeWriter();
var parser = new TSql120Parser(false);
var statementList = new StatementList();
IList <ParseError> errors;
var script2 = parser.Parse(new StringReader(sqlText), out errors) as TSqlScript;
if (errors.Count > 0)
{
var errorList = new StringBuilder();
foreach (var error in errors)
{
errorList.AppendLine($"{error.Message}<br/>");
}
throw new ApplicationException(errorList.ToString());
}
var scriptGen = new Sql120ScriptGenerator();
foreach (var batch2 in script2.Batches)
{
foreach (var statement in batch2.Statements)
{
var createTypeTableStatement = statement as CreateTypeTableStatement;
if (createTypeTableStatement == null)
{
continue;
}
string scriptOut;
scriptGen.GenerateScript(createTypeTableStatement, out scriptOut);
createTableTypeBody.WriteLine(scriptOut);
}
}
return(createTableTypeBody.ToString());
}
private static List <TSqlParserToken> TokenizeSql(string sql, out List <string> parserErrors)
{
using (TextReader textReader = new StringReader(sql))
{
var parser = new TSql120Parser(true);
IList <ParseError> errors;
var queryTokens = parser.GetTokenStream(textReader, out errors);
if (errors.Any())
{
parserErrors = errors.Select(e => $"Error: {e.Number}; Line: {e.Line}; Column: {e.Column}; Offset: {e.Offset}; Message: {e.Message};").ToList();
}
else
{
parserErrors = null;
}
return(queryTokens.ToList());
}
}
private List <string> ParseFieldValues(string fieldValueString)
{
List <string> result = new List <string>();
var sqlParser = new TSql120Parser(false);
IList <ParseError> errors;
using (var reader = new System.IO.StringReader(fieldValueString)) {
var queryTokens = sqlParser.GetTokenStream(reader, out errors);
var tokens = queryTokens.Where(q => q.TokenType == TSqlTokenType.AsciiStringLiteral || q.TokenType == TSqlTokenType.Null || q.TokenType == TSqlTokenType.Identifier || q.TokenType == TSqlTokenType.Integer || q.TokenType == TSqlTokenType.Numeric || q.TokenType == TSqlTokenType.UnicodeStringLiteral);
foreach (var token in tokens)
{
result.Add(GetFieldText(token.Text));
}
return(result);
}
}
private List <string> ParseColumnNames(string columnNameString)
{
List <string> result = new List <string>();
var sqlParser = new TSql120Parser(false);
IList <ParseError> errors;
using (var reader = new System.IO.StringReader(columnNameString)) {
var queryTokens = sqlParser.GetTokenStream(reader, out errors);
var tokens = queryTokens.Where(q => q.TokenType == TSqlTokenType.QuotedIdentifier || q.TokenType == TSqlTokenType.AsciiStringOrQuotedIdentifier || q.TokenType == TSqlTokenType.Identifier);
foreach (var token in tokens)
{
result.Add(GetColumnName(token.Text));
}
return(result);
}
}
public void Parse()
{
using (var reader = GetScriptReader())
{
var parser = new TSql120Parser(true);
IList<ParseError> errors;
var sqlFragment = parser.Parse(reader, out errors);
if (errors.Count > 0)
{
foreach (var error in errors)
{
Log.WriteInfo(_path, error.Line, "Script Parser: Error in {0} error: {1}", _path, error.Message);
}
}
sqlFragment.Accept(_visitor);
}
}
private List <CreateTableStatement> GetCreateTableStatements()
{
using (var script = new StringReader(_script))
{
var parser = new TSql120Parser(false);
IList <ParseError> errors;
var fragment = parser.Parse(script, out errors);
if (fragment != null)
{
var visitor = new CreateTableVisitor();
fragment.Accept(visitor);
return(visitor.Creates);
}
}
return(null);
}
private string GetComments(string oldScriptBlock)
{
var comments = new StringBuilder();
var parser = new TSql120Parser(false);
IList<ParseError> errors;
var tokens = parser.GetTokenStream(new StringReader(oldScriptBlock), out errors);
foreach (var token in tokens)
{
switch (token.TokenType)
{
case TSqlTokenType.MultilineComment:
case TSqlTokenType.SingleLineComment:
comments.AppendLine(token.Text);
break;
}
}
return comments.ToString();
}
private void button1_Click(object sender, EventArgs e)
{
TextReader rdr = new StringReader(textBox1.Text);
IList<ParseError> errors = null;
TSql120Parser parser = new TSql120Parser(true);
TSqlFragment tree = parser.Parse(rdr, out errors);
foreach(ParseError err in errors)
{
Console.WriteLine(err.Message);
}
Sql120ScriptGenerator scrGen = new Sql120ScriptGenerator();
string formattedSQL = null;
scrGen.GenerateScript(tree, out formattedSQL);
textBox2.Text = formattedSQL;
rdr.Dispose();
}
public virtual string GetPlanForQuery(string query)
{
IList<ParseError> errors;
var fragment = new TSql120Parser(false).Parse(new StringReader(query), out errors );
var visitor = new ProcedureVisitor();
fragment.Accept(visitor);
foreach (var proc in visitor.Procedures)
{
var procName = proc.ProcedureReference.Name;
return GetPlanForProc(procName);
}
foreach (var func in visitor.Functions)
{
var procName = func.Name;
return GetPlanForFunc(procName, func.Parameters);
}
return "";
}
protected override string GetReplacementScript(string script)
{
var parser = new TSql120Parser(true);
IList<ParseError> errors;
var fragment = parser.Parse(new StringReader(script), out errors);
var visitor = new IndexVisitior();
fragment.Accept(visitor);
var newScript = script;
foreach (var create in visitor.Creates)
{
var newCreate = GenerateCreateWithEditionCheck(create);
var generator = new Sql120ScriptGenerator();
string newStatement;
generator.GenerateScript(newCreate, out newStatement);
newScript = newScript.Replace(
script.Substring(fragment.StartOffset, fragment.FragmentLength), newStatement);
}
return newScript;
}
public void Go(){
IList<ParseError> errors;
var fragment = new TSql120Parser(false).Parse(new StringReader(_scripts), out errors);
if (fragment == null)
return;
var visitor = new ProcedureVisitor();
fragment.Accept(visitor);
using (var procedureRepository = new ProcedureRepository(DacpacPath.Get(_sourceProject)))
using (var functionRepository = new FunctionRepository(DacpacPath.Get(_sourceProject)))
{
foreach (var procedure in visitor.Procedures)
{
var browser = new SolutionBrowserForm("test " + procedure.ProcedureReference.Name.BaseIdentifier.Value.UnQuote() + " does something");
browser.ShowDialog();
var destination = browser.DestinationItem;
if (destination == null)
continue;
if (String.IsNullOrEmpty(DacpacPath.Get(_sourceProject)) && !File.Exists(DacpacPath.Get(_sourceProject)))
{
MessageBox.Show("Cannot find dacpac for project");
return;
}
var parentProjectItem = destination;
var name = browser.GetObjectName();
var proc = procedureRepository.FirstOrDefault(p => p.Name.EqualsName(procedure.ProcedureReference.Name));
if (proc == null)
{
MessageBox.Show(string.Format("Cannot find stored procedure {0} in project compiled dacpac", procedure.ProcedureReference.Name));
return;
}
var testBuilder = new ProcedureBuilder(procedure.ProcedureReference.Name.BaseIdentifier.Value.UnQuote(), name, proc);
var script = testBuilder.GetScript();
CreateNewFile(parentProjectItem, name , script);
}
foreach (var procedure in visitor.Functions)
{
var browser = new SolutionBrowserForm("test " + procedure.Name.BaseIdentifier.Value.UnQuote() + " does something");
browser.ShowDialog();
var destination = browser.DestinationItem;
if (destination == null)
continue;
if (String.IsNullOrEmpty(DacpacPath.Get(_sourceProject)) && !File.Exists(DacpacPath.Get(_sourceProject)))
{
MessageBox.Show("Cannot find dacpac for project");
return;
}
var parentProjectItem = destination;
var name = browser.GetObjectName();
var proc = functionRepository.FirstOrDefault(p => p.Name.EqualsName(procedure.Name));
if (proc == null)
{
MessageBox.Show(string.Format("Cannot find stored procedure {0} in project compiled dacpac", procedure.Name));
return;
}
var testBuilder = new ProcedureBuilder(procedure.Name.BaseIdentifier.Value.UnQuote(), name, proc);
var script = testBuilder.GetScript();
CreateNewFile(parentProjectItem, name, script);
}
}
}
private List<CreateTableStatement> GetCreateTableStatements()
{
using (var script = new StringReader(_script))
{
var parser = new TSql120Parser(false);
IList<ParseError> errors;
var fragment = parser.Parse(script, out errors);
if (fragment != null)
{
var visitor = new CreateTableVisitor();
fragment.Accept(visitor);
return visitor.Creates;
}
}
return null;
}
private void Parse()
{
Errors.Text = "";
MaxDepth = Int32.Parse(DepthText.Text);
var parser = new TSql120Parser(true);
IList<ParseError> errors;
var script = parser.Parse(new StringReader(GetText()), out errors);
if (errors.Count > 0)
{
Errors.Text = "";
foreach (var e in errors)
{
Errors.Text += "Error: " + e.Message + " at: " + e.Offset + "\r\n";
}
return;
}
var enumerator = new EnumeratorVisitor();
script.Accept(enumerator);
Results.Items.Clear();
foreach (var node in enumerator.Nodes)
{
foreach (var i in GetChildren(node))
{
Results.Items.Add(i);
}
}
Tokens.Items.Clear();
var newItem = new TreeViewItem();
newItem.Header = "Tokens";
newItem.IsExpanded = true;
foreach (var t in script.ScriptTokenStream)
{
var newChild = new TreeViewItem();
newChild.Header = string.Format("{0} : {1} : {2} : {3}", t.TokenType, t.Text, t.Offset, t.Column);
newItem.Items.Add(newChild);
newChild.Tag = t;
}
Tokens.Items.Add(newItem);
}
public void CreateSchemas()
{
IList<ParseError> errors;
var fragment = new TSql120Parser(false).Parse(new StringReader(_scripts), out errors);
if (fragment == null)
return;
var visitor = new ProcedureVisitor();
fragment.Accept(visitor);
foreach (var procedure in visitor.Procedures)
{
var browser = new SolutionBrowserForm(procedure.ProcedureReference.Name.BaseIdentifier.Value.Quote());
browser.ShowDialog();
var destination = browser.DestinationItem;
if (destination == null)
continue;
var parentProjectItem = destination;
var name = browser.GetObjectName();
var script = GetScript(name);
for (var i = 1; i <= parentProjectItem.ProjectItems.Count; i++)
{
var item = parentProjectItem.ProjectItems.Item(i);
if (item.Name.UnQuote() == name.UnQuote())
{
CreateNewFile(item, name, script);
return;
}
}
var folder = parentProjectItem.ProjectItems.AddFolder(name.UnQuote());
CreateNewFile(folder, name, script);
}
foreach (var procedure in visitor.Functions)
{
var browser = new SolutionBrowserForm(procedure
.Name.BaseIdentifier.Value.Quote());
browser.ShowDialog();
var destination = browser.DestinationItem;
if (destination == null)
continue;
var parentProjectItem = destination;
var name = browser.GetObjectName();
var script = GetScript(name);
for (var i = 1; i <= parentProjectItem.ProjectItems.Count; i++)
{
var item = parentProjectItem.ProjectItems.Item(i);
if (item.Name.UnQuote() == name.UnQuote())
{
CreateNewFile(item, name, script);
return;
}
}
var folder = parentProjectItem.ProjectItems.AddFolder(name.UnQuote());
CreateNewFile(folder, name, script);
}
}
private void Parse()
{
Errors.Text = "";
MaxDepth = Int32.Parse(DepthText.Text);
var parser = new TSql120Parser(true);
IList<ParseError> errors;
var script = parser.Parse(new StringReader(GetText()), out errors);
if (errors.Count > 0)
{
Errors.Text = "";
foreach (var e in errors)
{
Errors.Text += "Error: " + e.Message + " at: " + e.Offset + "\r\n";
}
return;
}
var enumerator = new EnumeratorVisitor();
script.Accept(enumerator);
Results.Items.Clear();
foreach (var node in enumerator.Nodes)
{
foreach (var i in GetChildren(node))
{
Results.Items.Add(i);
}
}
}
private string ScriptBase(Database db, string definition)
{
var before = ScriptQuotedIdAndAnsiNulls(db, false);
var after = ScriptQuotedIdAndAnsiNulls(db, true);
if (after != string.Empty)
after = Environment.NewLine + "GO" + Environment.NewLine + after;
#if FixRoutineNames
// correct the name if it is incorrect
var identifierEnd = new[] {TSqlTokenType.As, TSqlTokenType.On, TSqlTokenType.Variable, TSqlTokenType.LeftParenthesis};
var identifier = new[] {TSqlTokenType.Identifier, TSqlTokenType.QuotedIdentifier, TSqlTokenType.Dot};
var commentOrWhitespace = new[] {TSqlTokenType.MultilineComment, TSqlTokenType.SingleLineComment,TSqlTokenType.WhiteSpace};
IList<ParseError> errors;
TSqlFragment script = new TSql120Parser(initialQuotedIdentifiers: QuotedId).Parse(new StringReader(definition), out errors);
var id =
script.ScriptTokenStream.SkipWhile(t => !identifier.Contains(t.TokenType))
.TakeWhile(t => identifier.Contains(t.TokenType) || commentOrWhitespace.Contains(t.TokenType))
.Where(t => identifier.Contains(t.TokenType)).ToArray();
var replaced = false;
definition = string.Join(string.Empty, script.ScriptTokenStream.Select(t => {
if (id.Contains(t)) {
if (replaced)
return string.Empty;
else {
replaced = true;
return string.Format("[{0}].[{1}]", Schema, Name);
}
} else {
return t.Text;
}
}));
#endif
return before + definition + after;
}