Парсим с помощью ANTLR 4 хранимки T-SQL для получения связей между таблицами

  • Tutorial
Доброго дня, друзья.

Задачу обозначил как абстрактную, т.е. для освоения методов решения, хотя практическое применение кто-нибудь может и найдет.

Изначально идея возникла когда я пришел в контору SQL-разработчиком. Там были сотни хранимых процедур, битая документация, не было связей между таблицами.

К делу. Далее использование Antlr и примеры кода.

Спасибо коллегам с foxclub.ru и KvanTTT за критику и это:
Да, и чота я не пойму для чего используется метод accept в шаблоне Visitor.
Я использую тока Visit*

Ответ KvanTTT

— Он вам не понадобится, т.к. используется в сгенерированном коде и вызывает необходмые методы Visit.

Antlr — эта штука генерит лексеры и парсеры на разных языках, по-умолчанию java.

Cборка самого antlr

set PATH=c:\apache-maven-3.3.9\bin\;%PATH%  
  rem mvn -DskipTests install   
  mvn install -DskipTests=true

собрать c:\dev\antlr4\runtime\CSharp\runtime\CSharp\Antlr4.vs2013.sln

добавляем в tsql.g4(файл грамматики SQL для antlr):

  options  
  {  
  	language = CSharp;  
  }

генерим парсер с помощью gen_csharp.cmd

  java -jar c:\dev\antlr4\tool\target\antlr4-4.7-SNAPSHOT-complete.jar -o Generated_Csharp tsql.g4  
  rem java -jar c:\antlrworks2\antlrworks2\modules\ext\antlr4.jar -o Generated_Csharp tsql.g4  
  pause

Получаем восемь файлов: tsqlBaseListener.cs, tsqlBaseVisitor.cs, tsqlLexer.cs, tsqlListener.cs, tsqlParser.cs, tsqlVisitor.cs, tsql.tokens, tsqlLexer.tokens

Создать проект и добавить в него полученные файлы и ссылку
c:\dev\antlr4\runtime\CSharp\runtime\CSharp\Antlr4.Runtime\bin\net35\Release\Antlr4.Runtime.dll

Ну дальше, поправим код парсеров..., но

Antlr предоставляет два шаблона проектирования на выбор: Listener и Visitor.

Я попытался сначала разобраться с Visitor, но как оказалось он чаще используется для постоянно изменяемого кода, в котором могут быть синтаксические ошибки, и в методах используется возврат результата разбора.

А хранимые процедуры T-SQL скомпилированы, т.е. статичны и не содержат синтаксических ошибок. Да и кода с помощью Listener писать меньше. Код обхода с помощью Visitor я помещу в конце.

Итак наша программа Program.cs с помощью Listener

using System;
using System.IO;
using Antlr4.Runtime;
using Antlr4.Runtime.Misc;

namespace tsql1
{
    class Program
    {
        static void Main(string[] args)
        {
            //try
            //{

            string text = System.IO.File.ReadAllText(@"c:\dev\antlr4\grammars-v4-master\tsql\examples\dml_select.sql");
            StringReader reader = new StringReader(text);
            // В качестве входного потока символов устанавливаем ...
            AntlrInputStream input = new AntlrInputStream(reader);
            // Настраиваем лексер на этот поток
            tsqlLexer lexer = new tsqlLexer(input);
            // Создаем поток токенов на основе лексера
            CommonTokenStream tokens = new CommonTokenStream(lexer);
            // Создаем парсер
            tsqlParser parser = new tsqlParser(tokens);
            // Specify our entry point
            //tsqlParser.Query_specificationContext  
            tsqlParser.Tsql_fileContext Tsql_fileContext1 = parser.tsql_file();
            Console.WriteLine("Tsql_fileContext1.ChildCount = " + Tsql_fileContext1.ChildCount.ToString());

            // Walk it and attach our listener
            Antlr4.Runtime.Tree.ParseTreeWalker walker = new Antlr4.Runtime.Tree.ParseTreeWalker();
            AntlrTsqListener listener = new AntlrTsqListener();
            walker.Walk(listener, Tsql_fileContext1);
             //}
             //    catch (Exception e)
             //{
             //    Console.WriteLine(e.Message);
             //}
             Console.ReadKey();
        }

    }

    public class AntlrTsqListener : tsqlBaseListener
    {
        private enum JoinMode {
            Undefined,
            Where,
            Join
            };
        private JoinMode mode;
        private enum BranchType
        {
            Select,
            Table_sources,
            Search_condition
            //Join
        };
        private BranchType branch;

        private string alias = "";

        public override void EnterQuery_specification(tsqlParser.Query_specificationContext ctx)
        {
            mode = JoinMode.Undefined;
        }
        public override void EnterTable_sources(tsqlParser.Table_sourcesContext ctx)
        {
            if (ctx.ChildCount > 1)
                mode = JoinMode.Where;
            branch = BranchType.Table_sources;
        }
        public override void EnterTable_source_item_joined([NotNull] tsqlParser.Table_source_item_joinedContext ctx)
        {
            if ((mode == JoinMode.Undefined  & ctx.ChildCount == 1) || (mode == JoinMode.Where))
                return;
            mode = JoinMode.Join;
            branch = BranchType.Table_sources;
        }
        public override void EnterTable_name_with_hint([NotNull] tsqlParser.Table_name_with_hintContext ctx)
        {
            if (mode == JoinMode.Undefined)
                return;
            if (branch == BranchType.Table_sources)
                Console.WriteLine(branch.ToString());
            alias = "";
        }
        public override void EnterTable_name([NotNull] tsqlParser.Table_nameContext ctx)
        {
            if (branch == BranchType.Search_condition || branch == BranchType.Select || mode == JoinMode.Undefined)
                return;
            Console.WriteLine(ctx.GetText());
        }
        public override void EnterTable_alias([NotNull] tsqlParser.Table_aliasContext ctx)
        {
            if (branch == BranchType.Search_condition || branch == BranchType.Select | mode == JoinMode.Undefined)
                return;
            alias = ctx.GetChild(0).GetText();
            Console.WriteLine("alias=" + alias);
        }
        public override void EnterSearch_condition([NotNull] tsqlParser.Search_conditionContext ctx)
        {
            if (mode == JoinMode.Undefined)
                return;
            branch = BranchType.Search_condition;
            Console.WriteLine("Search_condition");
            Console.WriteLine(ctx.GetText());
            return;
        }
        public override void EnterSelect_statement([NotNull] tsqlParser.Select_statementContext ctx)
        {
            Console.WriteLine("Select_statement");
            branch = BranchType.Select;
            return;
        }

Входные данные:

select *
from t1, t2
where t1.id = t2.id

SELECT p.*
FROM Production.Product AS p
ORDER BY Name ASC;
GO

select *
from zxc as t1
    inner join qwe t2 on t1.id = t2.id
    inner join asd t3 on t3.id = t2.id
...

Пример вывода:

Tsql_fileContext1.ChildCount = 105
Select_statement
Table_sources
t1
Table_sources
t2
Search_condition
t1.id=t2.id
Select_statement
Select_statement
Table_sources
zxc
alias=t1
Table_sources
qwe
alias=t2
Search_condition
t1.id=t2.id
Table_sources
asd
alias=t3
Search_condition
t3.id=t2.id
...

Данный результат можно разложить в Excel если захочется.

Все.

Прошу сильно не бить. Может что-то забыл, может что-то устарело.

Грамматики для antlr на github
Старая грамматика для T-SQL на pastebin
Antlr на antlr.org

Код обхода с помощью Visitor
  using System;  
  using System.IO;  
  using Antlr4.Runtime;  
  using Antlr4.Runtime.Misc;  
    
  namespace tsql1  
  {  
      class Program  
      {  
          static void Main(string[] args)  
          {  
              //try  
              //{  
                    
                  string text = System.IO.File.ReadAllText(@"c:\dev\antlr4\grammars-v4-master\tsql\examples\dml_insert.sql");  
                  StringReader reader = new StringReader(text);  
                  // В качестве входного потока символов устанавливаем ...  
                  AntlrInputStream input = new AntlrInputStream(reader);  
                  // Настраиваем лексер на этот поток  
                  tsqlLexer lexer = new tsqlLexer(input);  
                  // Создаем поток токенов на основе лексера  
                  CommonTokenStream tokens = new CommonTokenStream(lexer);  
                  // Создаем парсер  
                  tsqlParser parser = new tsqlParser(tokens);  
                  // Specify our entry point  
                  //tsqlParser.Query_specificationContext    
                  tsqlParser.Tsql_fileContext Tsql_fileContext1 = parser.tsql_file();  
              Console.WriteLine("Tsql_fileContext1.ChildCount = " + Tsql_fileContext1.ChildCount.ToString());  
    
              /*                // Walk it and attach our listener  
                              Antlr4.Runtime.Tree.ParseTreeWalker walker = new Antlr4.Runtime.Tree.ParseTreeWalker();  
                              AntlrTsqListener listener = new AntlrTsqListener();  
                              walker.Walk(listener, Tsql_fileContext1);*/  
              AntlrTsqVisitor visitor = new AntlrTsqVisitor();  
                  var result = visitor.Visit(Tsql_fileContext1);  
              //}  
              //    catch (Exception e)  
              //{  
              //    Console.WriteLine(e.Message);  
              //}  
              Console.ReadKey();  
          }  
    
      }  
  /*  
      public class AntlrTsqListener: tsqlBaseListener  
      {  
          public override void EnterQuery_specification(tsqlParser.Query_specificationContext ctx)  
          {  
              int ii = 0;  
              //Console.WriteLine(ctx.ToStringTree());  
              Console.WriteLine("ctx.ChildCount" + ctx.ChildCount.ToString());  
              for (ii = 0; ii < ctx.ChildCount; ++ii)  
              {  
                  Console.WriteLine("ii=" + ii.ToString());  
                  Console.WriteLine(ctx.GetChild(ii).GetType().ToString());  
                  Console.WriteLine(ctx.GetChild(ii).GetText());  
              }  
              //Console.WriteLine(ctx.GetChild<tsqlParser.i>().ToString());  
          }  
      }  
     */  
    
      public class AntlrTsqVisitor : tsqlBaseVisitor<String>  
      {  
  /*  
          public override string VisitSql_clauses(tsqlParser.Sql_clausesContext ctx)  
          {  
              Console.WriteLine("VisitSql_clauses");  
              return VisitChildren(ctx).ToString();  
          }  
 */  
          public override string VisitSql_clause(tsqlParser.Sql_clauseContext ctx)  
          {  
              Console.WriteLine("VisitSql_clause");  
              try  
              {  
                  return VisitDml_clause(ctx.dml_clause()).ToString();  
              }  
              catch (Exception e)  
              {  
                  return "";  
              }  
          }  
  /*  
          public override string VisitDml_clause(tsqlParser.Dml_clauseContext ctx)  
          {  
              Console.WriteLine("VisitDml_clause");  
              return VisitChildren(ctx).ToString();  
          }  
 */  
          public override string VisitSelect_statement([NotNull] tsqlParser.Select_statementContext ctx)  
          {  
              Console.WriteLine("VisitSelect_statement");  
              return VisitTable_sources(ctx.query_expression().query_specification().table_sources()).ToString();  
          }  
          public override string VisitDelete_statement([NotNull] tsqlParser.Delete_statementContext ctx)  
          {  
              Console.WriteLine("VisitDelete_statement");  
              try  
              {  
                  return VisitTable_sources(ctx.table_sources());  
              }  
              catch (Exception e)  
              {  
                  return "";  
              }  
          }  
          public override string VisitUpdate_statement([NotNull] tsqlParser.Update_statementContext ctx)  
          {  
              Console.WriteLine("VisitUpdate_statement");  
              try  
              {  
                  return VisitTable_sources(ctx.table_sources());  
              }  
              catch (Exception e)  
              {  
                  return "";  
              }  
          }  
          public override string VisitInsert_statement([NotNull] tsqlParser.Insert_statementContext ctx)  
          {  
              Console.WriteLine("VisitInsert_statement");  
              try  
              {  
                  return VisitTable_sources(ctx.insert_statement_value().derived_table().subquery().select_statement().query_expression().query_specification().table_sources());  
              }  
              catch (Exception e)  
              {  
                  return "";  
              }  
          }  
  /*  
          public override string VisitTable_sources([NotNull] tsqlParser.Table_sourcesContext ctx)  
          {  
              Console.WriteLine("VisitTable_sources");  
              return VisitChildren(ctx).ToString();  
          }  
          public override string VisitTable_source([NotNull] tsqlParser.Table_sourceContext ctx)  
          {  
              Console.WriteLine("VisitTable_source");  
              return VisitChildren(ctx).ToString();  
          }  
          public override string VisitTable_source_item_joined([NotNull] tsqlParser.Table_source_item_joinedContext ctx)  
          {  
              Console.WriteLine("VisitTable_source_item_joined");  
              return VisitChildren(ctx).ToString();  
          }  
 */  
          public override string VisitTable_source_item([NotNull] tsqlParser.Table_source_itemContext ctx)  
          {  
              Console.WriteLine("VisitTable_source_item");  
              int ii = 0;  
              //Console.WriteLine(ctx.ToStringTree());  
              Console.WriteLine("ctx.ChildCount " + ctx.ChildCount.ToString());  
              for (ii = 0; ii < ctx.ChildCount; ++ii)  
              {  
                  Console.WriteLine("ii=" + ii.ToString());  
                  Console.WriteLine(ctx.GetChild(ii).GetType().ToString());  
                  Console.WriteLine(ctx.GetChild(ii).GetText());  
                  //if (ctx.GetChild(ii).GetType().ToString() == "tsqlParser+Table_sourcesContext")  
                  //{  
                  //    this.VisitTable_sources(ctx.table_sources());  
                  //}  
              }  
              //Console.WriteLine(ctx.GetChild<tsqlParser.i>().ToString());  
              return ctx.ToString();  
          }  
          public override string VisitJoin_part([NotNull] tsqlParser.Join_partContext ctx)  
          {  
              Console.WriteLine("VisitJoin_part");  
              int ii = 0;  
              //Console.WriteLine(ctx.ToStringTree());  
              Console.WriteLine("ctx.ChildCount " + ctx.ChildCount.ToString());  
              for (ii = 0; ii < ctx.ChildCount; ++ii)  
              {  
                  Console.WriteLine("ii=" + ii.ToString());  
                  Console.WriteLine(ctx.GetChild(ii).GetType().ToString());  
                  Console.WriteLine(ctx.GetChild(ii).GetText());  
                  if (ctx.GetChild(ii).GetType().ToString() == "tsqlParser+Table_sourceContext")  
                  {  
                      this.VisitTable_source(ctx.table_source());  
                  }  
              }  
              //Console.WriteLine(ctx.GetChild<tsqlParser.i>().ToString());  
              return ctx.ToString();  
          }  
      }  
  }
Поделиться публикацией
Ой, у вас баннер убежал!

Ну. И что?
Реклама
Комментарии 20
  • 0

    Может я чего подзабыл, но разве зависимости для хранимок не лежат где-то в системных таблицах?

    • 0
      не уверен, что из зависимостей мы можем получить
      конкретные связи между таблицами по полям
      • 0

        Для того, чтобы убедиться, что процедура все еще валидна после переименования или удаления колонки, нужно знать не только таблицы, но и колонки (а также и их типы). Ну т.е. я реально не помню, но кажется мне отчего-то, что должно это где-то быть.


        Это впрочем не делает примененный способ менее полезным. Я сам так делал совсем недавно, правда, на примере VBA — т.е. парсил код, и искал зависимости между процедурами и функциями.

        • 0
          Валидность процедур проверить просто,
          пересоздав их(или перекомпилировать).

          А вот того кто меняет структуру таблиц не заботясь о том, что упадет могут и расстрелять.))
    • 0
      тов. KvanTTT поправил меня, тезис «как оказалось он(Visitor) чаще используется для постоянно изменяемого кода, в котором могут быть синтаксические ошибки»,
      Listener тоже так используется(а может использовался). Я ж говорю, подзабыл, сам видел в исходниках NetBeans. Обязательно рассмотрю реализацию в NetBeans. Надеюсь статья не последняя.
      • 0

        Писал аналогичный скрипт для PL/SQL на Python. Подскажите, как решались проблемы с:
        1) Сложными конструкциями в блоках select и for
        2) Устаревшим синтаксисом в стандартном наборе ANTRL

        • 0
          Если я Вас правильно понял, то
          достаточно просто повнимательнее взглянуть на грамматику — там иерархия выражений. Если не хватает чего-то в грамматике, то надо дописать в ней. Обрабатываем только нужные нам узлы. Методы именуются так же как выражения в грамматике + префикс «Enter» в начале.
          Проще самому потестировать этот код и поудалять мешающие return.
          Чтобы обход дерева был более наглядным можно вставлять в методы код обхода дочерних узлов
          for (ii = 0; ii < ctx.ChildCount; ++ii)
          {
              Console.WriteLine("ii=" + ii.ToString());
              Console.WriteLine(ctx.GetChild(ii).GetType().ToString());
              Console.WriteLine(ctx.GetChild(ii).GetText());
          }
          
          • 0
            Да, вот по ссылке books
            лежит книга по предыдущей версии ATNLR от Terence Parr — «The Definitive ANTLR Reference»
            • 0

              Эх, бедный антлер, как только над ним не изгаляются: antl, ANTRL, ATNLR =)


              Ну а так вообще конечно лучше новую книгу читать The Definitive ANTLR 4 Reference. В четвертой версии ANTLR появилось несколько фундамельных изменений (полноценный LL парсинг, поддержка левой рекурсии). Хотя даже с 2013 года в ANTLR 4 много нововведений и рантаймов, доступно в списке релизов.

            • 0
              1) Сложными конструкциями в блоках select и for

              Что вы имеете в виду, можно пример? Можно использовать дополнителный стек для контроля вложенности конструкций.


              2) Устаревшим синтаксисом в стандартном наборе ANTRL

              Опять-таки, что за устаревший синтаксис в стандартном наборе? Грамматика PL/SQL в репозитории сейчас актуальная.

              • 0
                1) Я как раз про дополнительную обработку всего и вся рекурсивно, включая select секцию и не только.
                2) Синтаксис Oracle SQL, PL/SQL в стандартном наборе действительно стал значительно лучше в последнее время. Только я не вижу некоторых новых конструкции из Oracle 12c (навскидку WITH_PLSQL, MATCH_RECOGNIZE). А между прочим расширенная поддержка 11g заканчивается уже в этом месяце.

                Я вообще к чему это всё, инструмент — рабочий, общую картину увидеть позволяет. Но к сожалению от 10% до 30% связей мы не увидим. Частично из-за старой граматики (это как раз исправимо, пиши граматику сам), а вот проблема с динамическим кодом неразрешима.

                В итоге получается, что инструменту на 100% доверять нельзя и всё-равно приходится посмотривать весь код глазами. Поиграться — да, надёжный инструмент — нет.

                У Информатики даже продукт готовый есть за много тысяч долларов — Metadata Manager, увы, с той же степенью надёжности.

                В случае с Oracle, если код в хранимых процедурах, то гораздо проще пользоваться таблицей ALL_DEPENDENCIES (пролема с динамикой остаётся). Если код в SVN, то только ANTRL.
                • 0
                  Вы говорите примерно следующее,
                  «жалко, что молоток сам не выпрямляет гнутые гвозди», но молоток не был для этого предназначен. Инструмент на 100% рабочий. То что нужно детерминировать процедуры с динамическим кодом(которого думаю небольшой процент) и поставить заглушки и получить статический код и обработать его отдельно, я не вижу проблемы. Грамматику дописать? Конечно, и тут надо поработать, изучить как пишутся правила и т.д. Но, елки, ты ж программист.
                  Удачи.
                  • 0
                    Тыж программист, ну да =)
                  • 0
                    Только я не вижу некоторых новых конструкции из Oracle 12c (навскидку WITH_PLSQL, MATCH_RECOGNIZE). А между прочим расширенная поддержка 11g заканчивается уже в этом месяце.

                    Можете создать issue на GitHub — рассмотрим. А в идеале вообще Pull Request.


                    Частично из-за старой граматики (это как раз исправимо, пиши граматику сам), а вот проблема с динамическим кодом неразрешима.

                    Ну да, это не проблема грамматики — нужно проводить дальнейшие действия над деревом разбора, возможно абстрактно интерпретировать.

                    • 0
                      В новой грамматике сломать поддержку Python — бесценно.
                      • 0

                        Точнее сказать ограничить грамматику Java рантаймом.


                        Не поддерживаются в ANTLR вставки кода на универсальном языке, смотри мою issue Unified Actions Language. С другой стороны, уменьшать возможности грамматики из-за вырезания всех вставок кода я не хочу. Просто измените фрагменты кода Java на Python — это несложно.


                        Однако есть другой подход, который подходит к существующей версией ANTLR, смотри грамматику JavaScript. Однако в нем есть свои недостати: он громоздкий в реализации, да и не уверен, что применим к Python без доработки (поддерживаются C# и Java).

              • 0
                Кста, классный сайт по теории
                mathhelpplanet.com
                • 0
                  Дополнение для новой грамматики:
                  CaseChangingCharStream.cs на github
                  Program.cs:
                  ...
                  //string text = System.IO.File.ReadAllText(@"c:\dev\antlr4\grammars-v4-master\tsql\examples\dml_select.sql");
                  //StringReader reader = new StringReader(text);
                  //// В качестве входного потока символов устанавливаем ...
                  //AntlrInputStream input = new AntlrInputStream(reader);
                  // Настраиваем лексер на этот поток
                  
                  TSqlLexer lexer = new TSqlLexer(new CaseChangingCharStream(new AntlrFileStream(@"c:\dev\antlr4\grammars-v4-master\tsql\examples\dml_select1.sql"), true));//(input);
                  ...
                  

                  Спасибо тов. KvanTTT
                • 0
                  Хороший туториал на https://tomassetti.me

                  Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                  Самое читаемое