Зависимости между SQL объектами: используем регулярные выражения и небольшой алгоритмический фокус / forpes.ru

Главная
Зависимости между SQL объектами: используем регулярные выражения и небольшой алгоритмический фокус

Зависимости между SQL объектами: используем регулярные выражения и небольшой алгоритмический фокус +8

25.01.2018 11:20

skaeff 1 2600 Источник

Введение

Базы данных пришли в мир в 70 году. С тех пор они стали больше по размеру, с более сложной логикой и продолжают расти. С тех пор появилось много инструментов для синтаксического анализа SQL и выстраивания зависимостей. Все они разбивают SQL на токены, используя тот или иной синтаксический анализатор и грамматики.

Но обычные синтаксические анализаторы (ANTLR, например) могут столкнуться с ошибками разбора скриптов, каждый диалект SQL имеет свои собственные особенности. Время анализа тоже может быть существенным на больших БД.

Я хочу показать намного более простой способ: RegEx + небольшой трюк,
итак…

Главная «фишка»

Получите однофайловый SQL-скрипт для всех объектов db
Разбить SQL-скрипт на текстовые блоки, используя регулярное выражение, которое соответствует операторам DDL
Поиск некоторой текстовой строки среди этих текстовых блоков
...
PROFIT!!!1

Реализация или как всё устроено

1. Создать скрипт всех SQL-объектов. Одним файлом

Каждая RDBMS имеет возможность генерировать скрипты SQL drop / create. Итак, сначала мы получаем единый SQL-скрипт для всех объектов базы данных. Или мы можем взять northwind.sql в качестве примера.

2. Найти в скрипте все drop/create/alter DDL используя регулярные выражения

Я использую это выражение

\b(create|alter)\s+(unique)*\s*(nonclustered)*\s*(procedure|proc|table|trigger|view|function|index)\b\s([^=<>\s\']+).([^=<>\s\'\(]+)[*\s\b\t]*

FastColoredTextBox предоставляет отличные инструменты для работы с регулярными выражениями, поэтому мы используем

var result = range.GetRangesByLines(regexStr, RegexOptions.IgnoreCase);

Подробнее об использованных регулярных выражениях

Я написал несколько полезных регулярных выражений и собирал их в одном статическом классе

public static class RegexValues
{
    public static string SqlCmdObjects = @"\[\$\(([^=<>\[\]\s\']+)\)\].\[[^=<>\s\']+\]";
    public static string SqlCmdObjectsShort = @"\$\(([^=<>\[\]\s\']+)\)";

    public static string DdlObjects = @"\b(create)\s+(procedure|proc|table|trigger|view|function)\b\s\[\$\(([^=<>\[\]\s\']+)\)\].\[[^=<>\s\']+\]";
    public static string DdlObjectsPrepared = @"\b(create)\s+(procedure|proc|table|trigger|view|function)\b\s\[([^=<>\[\]\s\']+)\].(\[[^=<>\s\']+\])";

    public static string DdlObjects_ = @"\b(create)\s+(procedure|proc|table|trigger|view|function)\b\s\$\(([^=<>\[\]\s\']+)\).[^=<>\s\']+";

    public static string DdlObjectsPrepared_ = @"\b(create)\s+(procedure|proc|table|trigger|view|function)\b\s([^=<>\s\']+).([^=<>\s\'\(]+)[*\s\b\t]*";

    public static string DdlObjectsPreparedProcViewTrigger = @"\b(create)\s+(procedure|proc|trigger|view|function)\b\s([^=<>\s\']+).([^=<>\s\'\(]+)[*\s\b\t]*";

    public static string DdlObjectsPreparedWithIndex = @"\b(create)\s+(unique)*\s*(nonclustered)*\s*(procedure|proc|table|trigger|view|function|index)\b\s([^=<>\s\']+).([^=<>\s\'\(]+)[*\s\b\t]*";

    public static string DdlIndexAll = @"\b(create|alter)\s+(procedure|proc|table|trigger|view|function)\b\s\[([^=<>\[\]\s\']+)\].\[[^=<>\s\']+\]";

    public static string Variables = @"\@([^=<>\s\'\)\(\,]+)";
    public static string SqlCmdVariables = @"\:SETVAR\s+([a-zA-Z_]+)\s+([a-zA-Z_]+)";
}

SqlCmdObjects — находит строки такого вида [$(some_var)].[Obj_name] или [$(some_var)].[Obj_schema].[Obj_name]
SqlCmdObjectsShort — соответствует $(some_var)
DdlObjects — находит то же, что и SqlCmdObjects, но в связке с инструкциями CREATE PROC/TABLE/VIEW/FUNC/TRIGGER
DdlObjectsPrepared — то же, что и DdlObjects, но удалено ограничение $(sqlcmd)
DdlObjects_ — то же, что и DdlObjects, но удалено ограничение на скобки []
DdlObjectsPrepared_ — то же, что и DdlObjectsPrepared, но удалено ограничение на скобки []
DdlObjectsPreparedProcViewTrigger — операторы DDL только для proc, views, триггеров и функций, т.е. только для «процедурных» объектов
DdlObjectsPreparedWithIndex — то же, что и DdlObjectsPrepared_, но расширен матчингом индексов
DdlIndexAll — то же, что и DdlObjectsPrepared_, но учитывается также оператор ALTER
Variables — регулярное выражение, которое находит все SQL переменные в скрипте
SqlCmdVariables — находит переменные SQL CMD (например :SETVAR var1 val1)

Основой этого программного обеспечения фактически являются следующие строки кода:

var range = Syncronized(() => fastColoredTextBox1.Selection.Clone());
range.Normalize();

range.Start = new Place(0, 0);
range.End = Syncronized(() => new Place(tb.GetLineLength(tb.LinesCount - 1), tb.LinesCount - 1));

т. е. разбивка SQL файла на диапазоны (Range).

Мы просто загружаем SQL-скрипт в FastColoredTextBox (спасибо авторам!) И применяем некоторое регулярное выражение ко всему его содержимому. В качестве результата мы получили переменную RESULT, в которой содержится список найденных диапазонов.

Диапазон (Range) — это мощный класс (еще раз спасибо автору FastColoredTextBox). Диапазон содержит номер строки и номер столбца найденного фрагмента в сценарии SQL. Мы просто сохраняем список диапазонов в виртуальном списке (общий метод), а на SelectedIndexChanged делаем следующее

fastColoredTextBox1.Selection = range;
fastColoredTextBox1.DoSelectionVisible();

Это обеспечивает нам удобную и быструю навигацию по SQL файлу

3. Найти все вхождения SQL объекта (или произвольной строки) в файле.

Мы создаем еще один список диапазонов (класс Range из библиотеки FastColoredTextBox), но вместо строки регулярных выражений мы помещаем имя некоторого SQL объекта

4. Пересечения двух списков диапазонов — тот самый алгоритмический фокус.

Последний шаг — найти вхождения из второго списка диапазонов в первый. Это делается в методе

private void FindUsage(string regex)

см класс Form1.cs

Вот и все, остальная работа — всего лишь обычный .NET-кодинг, чтобы собрать все компоненты вместе.

В качестве ИТОГО + небольшой HOWTO

1. находим все инструкции create/alter с помощью regex. И запоминаем эти номера строк.

2. находим все вхождения текста (объект, например, имя таблицы), используя регулярное выражение. А также запоминаем номера строк.

3. соединяем эти два набора данных, находя, где текстовая строка располагается между строками create/alter

В результате у нас появился простой GUI-дизайнер, где вы можете:

Открыть скрипт SQL или всю папку (скрипты будут объединены в временный файл и открыты как единый скрипт)
найти все операторы DDL (CREATE / ALTER) — список будет создан на левой панели с полной поддержкой навигации.
выбрать части текста с помощью мыши (например, некоторое имя объекта SQL)
щелкнуть правой кнопкой мыши и выберите «Найти использование» — список будет создан на правой панели — это объекты SQL, содержащие выделенный текст

Также, вы можете искать не только по названиям объектов SQL, но и фактически любую часть кода, текст, комментарий и т. д.

Важное замечание

Поскольку этот способ анализа зависимостей SQL основан не на метаданных, вы всегда должны помнить, что вы объединяете два набора данных из строк. Таким образом, есть некоторые ограничения или, лучше сказать, «фичи» :)

Допустим, у нас есть хранимая процедура

create proc test

as

declare @somevar int

create table #tmp(

colum_we_search nvarchar(255),
somevar int)

Если мы используем инструкцию regex, которая матчит «CREATE TABLE», мы получим в результатах матчинга таблицу #TMP вместе с CREATE PROC.

Затем, если мы попытаемся найти «COLUMN_WE_SEARCH» — она будет найдена внутри этой временной таблицы #TMP, а не в процедуре TEST.

Это можно обыграть используя более точное начальное регулярное выражение. Для таких случаев я написал оператор regex DdlObjectsPreparedProcViewTrigger (см. Выше) — матчинг операторов DDL только для proc, views, триггеров и функций, т.е. только для SQL объектов, содержащих код.

Разное и вкусное

Хотелось бы в будущем добавить поддержку MSAGL для визуализации зависимостей и большей наглядности!

FastColoredTextBox — это привет-конец, полный прорыв! Я даже не ожидал, что у него так много интересных функций!

> Исходники
> Собранная версия (архив с exe файлом)

Комментарии (1)

KvanTTT
27.01.2018 19:44
#10638724
Но обычные синтаксические анализаторы (ANTLR, например) могут столкнуться с ошибками разбора скриптов, каждый диалект SQL имеет свои собственные особенности.
Не аргумент. Во-первых, там встроена методика восстановления от ошибок. Во-вторых, регулярки также нужно писать другие для разных SQL диалектов.

Время анализа тоже может быть существенным на больших БД.
А регулярки быстро работают? Вы тестили скорость работы ANTLR?

Я написал несколько полезных регулярных выражений и собирал их в одном статическом классе
Они нечитаемые.

В общем статья скорее вредная — лучше использовать полноценный парсер, тем более для SQL диалектов есть PlSql, T-SQL и MySql грамматики.

Почему хотя бы не использовать универсальные токены, на основе которых уже что-то там искать?