65.9K
CodeProject 正在变化。 阅读更多。
Home

使用简单正则表达式和一些算法魔法进行SQL对象依赖关系分析

Askaeff
starIconstarIconstarIconstarIconstarIcon

5.00/5 (2投票s)

2018年1月10日

CPOL

4分钟阅读

viewsIcon

12157

一个简单而强大的工具，可以相当快速可靠地分析 SQL 对象依赖关系。

引言

数据库诞生于 70 年代。从那时起，它们变得越来越大，逻辑越来越复杂，并且仍在不断增长。因此，有许多工具，SQL 解析器，可以按对象分词脚本。

但是，常见的解析器 (例如 ANTLR) 可能会遇到脚本解析错误，每种 SQL 方言都有其特殊性，并且在处理大量数据库对象时会遇到时间问题。

因此，我想展示一种更简单的方法，即结合使用正则表达式和一些技巧。

主要概念

  1. 获取整个数据库对象的单文件 SQL 脚本。
  2. 使用匹配 DDL 语句的正则表达式将 SQL 脚本分割成文本块
  3. 在这些文本块中搜索某个文本字符串

实现

1. 获取所有 SQL 对象脚本

每个 RDBMS 都有一个生成 SQL drop/create 脚本的选项。因此，首先我们获取所有数据库对象的单个 SQL 脚本。或者我们可以使用 northwind.sql 作为示例。

2. 使用正则表达式获取所有 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);

首先，我构建了一些 有用的正则表达式 并将它们收集到一个 static 类中

    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 相同，但增加了 PROC/TABLE/VIEW/FUNC/TRIGGER 语句
  • DdlObjectsPrepared - 与 DdlObjects 相同，但移除了 $ (sqlcmd 变量) 限制
  • DdlObjects_ - 与 DdlObjects 相同，但移除了包围的 [] 限制
  • DdlObjectsPrepared_ - 与 DdlObjectsPrepared 相同，但移除了包围的 [] 限制
  • DdlObjectsPreparedProcViewTrigger - 仅用于 proc、views、triggers 和 functions 的 DDL 语句，即仅代码对象
  • DdlObjectsPreparedWithIndex - 与 DdlObjectsPrepared_ 相同，但增加了 index 语句
  • DdlIndexAll - 与 DdlObjectsPrepared_ 相同，但增加了 alter 语句
  • Variables - 查找脚本中所有 @variables 的正则表达式
  • 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 脚本加载到 FastColoredTextBox (非常感谢其作者!!!) 中，然后将一些正则表达式应用于其所有内容。作为输出，我们得到了 RESULT 变量，其中包含找到的范围列表。

Range 是一个强大的类 (再次感谢 FastColoredTextBox 的作者)。Range 包含 SQL 脚本中找到的片段的行号和列号。我们只是将范围列表存储在虚拟 listbox (通用技术) 中，并在 SelectedIndexChanged 时执行以下操作

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

通过这两行，我们立即导航到找到的代码片段 (即 DDL 语句)。

然后我们构建另一个范围列表，但不是使用正则表达式字符串，而是放入要查找的对象的名称。

最后一步是在第一个范围列表中查找第二个范围列表的出现。这在方法中完成

private void FindUsage(string regex)

在 Form1.cs 中。

就是这样，其余的工作就是通用的 .NET 编码来将所有东西组合在一起。

简而言之，上述逻辑如下：

  1. 我们使用正则表达式查找所有 create/alter 语句。并记住那些行号。
  2. 我们使用正则表达式查找文本 (对象，例如表名) 的所有出现。并且也记住行号。
  3. 现在将这两个数据集连接起来，查找文本行位于 create/alter 语句行之间的位置。

结果是，我们得到了一个简单的 GUI 设计器，您可以在其中

  • 打开 SQL 脚本或整个文件夹 (脚本将被合并到一个 temp 文件中并作为单个脚本打开)
  • 搜索所有 DDL 语句 (CREATE/ALTER) - 列表将在左侧窗格中构建，并提供完整的导航支持。
  • 用鼠标选择一段文本 (例如，某个 SQL 对象名称)
  • 右键单击并选择 "查找用法" - 列表将在右侧窗格中构建 - 这些是包含所选文本的 SQL 对象

另一个优点是，您不仅可以搜索 SQL 对象在 SQL 对象中的出现，还可以搜索几乎任何代码片段、文本、注释等。

重要提示

由于这种分析 SQL 依赖关系的方法不是基于元数据信息，因此您应该始终牢记，您正在连接两个行数据集。所以有一些限制，或者更确切地说，“特性” :)

假设我们有一个存储过程

create proc test

as

declare @somevar int

create table #tmp(

colum_we_search nvarchar(255),
somevar int)

如果我们应用匹配 "CREATE TABLE" 语句的正则表达式，我们将匹配 temp 表以及 CREATE PROC。

然后，如果我们尝试搜索 "COLUMN_WE_SEARCH" - 它将在那个 temp 表 #tmp 中找到，而不是在过程 test 中。

这可以通过更精确的初始正则表达式来解决。对于这种情况，我编写了 DdlObjectsPreparedProcViewTrigger 正则表达式语句 (见上文)。

关注点

我想添加 MSAGL https://github.com/Microsoft/automatic-graph-layout 支持来可视化依赖关系。

FastColoredTextBox - 是高端的，是完全的突破！我甚至没料到它会有这么多很酷的功能！

历史

  • 2018 年 1 月 10 日：初始版本
© . All rights reserved.