예제 #1
0
        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();
        }
예제 #2
0
        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);
        }
예제 #3
0
 public SqlRuleVisitor(IConfigReader configReader, IReporter reporter)
 {
     Parser             = new TSql120Parser(true);
     Violations         = new List <RuleViolation>();
     RuleVisitorBuilder = new RuleVisitorBuilder(configReader, reporter);
     Reporter           = reporter;
 }
예제 #4
0
        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();
                }
            }
        }
예제 #6
0
        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);
        }
예제 #7
0
        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);
                }
            }
        }
예제 #8
0
		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>());
        }
    }
예제 #10
0
        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;
        }
예제 #12
0
        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!");
            }
        }
예제 #14
0
        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();
        }
예제 #15
0
        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"));
        }
예제 #17
0
        /// <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);
        }
예제 #18
0
 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;
         }
     }
 }
예제 #19
0
        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());
        }
예제 #20
0
        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);
        }
예제 #21
0
        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);
        }
예제 #22
0
        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());
        }
예제 #23
0
        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));
    }
예제 #25
0
        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);
                }
            }
        }
예제 #26
0
 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);
     }
 }
예제 #27
0
        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);
        }
예제 #28
0
        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);
            }
        }
예제 #29
0
        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));
        }
예제 #30
0
        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);
        }
예제 #31
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);
                }
            }
        }
예제 #32
0
        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();
        }
예제 #34
0
        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>());;
        }
예제 #36
0
        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());
            }
        }
예제 #38
0
        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);
            }
        }
예제 #39
0
        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);
            }
        }
예제 #40
0
        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);
            }
        }
예제 #41
0
        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);
        }
예제 #42
0
        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();
        }
예제 #44
0
        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;
        }
예제 #46
0
        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);
                }
            }
        }
예제 #47
0
        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;
        }
예제 #48
0
        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);
        }
예제 #49
0
        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);
                }
            }
        }
예제 #51
0
        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;
        }