SQL翻译器的随机化测试介绍

尼耳多
105 阅读11分钟

不是所有的SQL都是平等的。我更想说的是，没有一种SQL方言甚至接近于平等。特别是，当谈论微软SQL服务器和PostgreSQL时，它们的语法可能看起来很相似。然而，在语义方面，它们却有天壤之别。今天我将描述

  • 我们如何在Haskell中实现从一种SQL方言到另一种SQL方言的自动翻译器
  • 我们在这一过程中面临的一些挑战
  • 最重要的是，我们如何使用随机化测试来驱动我们的翻译器，同时发现这两个数据库引擎之间的一些山头。

手头的问题

对引起所有工作的动机的简短介绍是相当重要的。事实上，那些对两种方言之间的差异有一点了解的人可能会问。为什么有人会在第一时间为这样一个翻译器而烦恼呢？在大多数情况下，用目标语言手动重写代码，测试它的行为是否符合预期，然后就可以了。

我们的一个客户计划将他们的数据库引擎从Microsoft SQL Server过渡到PostgreSQL。虽然这是一件合理的事情，但一个主要的障碍是，他们的很多数据处理逻辑是用T-SQL写的，而不是用一些与数据库无关的编程语言。更准确地说，我们在FP Complete的工程师们看到了一个10MB的文件，其中有大约200K行的T-SQL代码。任何正常人都不会急于用手来完成这种翻译。此外，对那些旨在被计算机理解的语言的翻译也是一项适合计算机而非人类的任务。

计划中的解决方案

该计划非常直接：

  • 隔离所有实际被利用的语言特征的一个子集。
  • 为Transact-SQL（T-SQL）写一个解析器。
  • 为每个数据类型、函数、查询等找出一个直接翻译成目标语言的方法。
  • 生成一个打印机，将程序语言/PostgreSQL（PL/pgSQL）的代码写出来。

无论你做了多少研究，或者你对某种特定的语言有多亲近，你都不可能知道所有的细枝末节，特别是同时用两种语言。为了对我们的译员有信心，我们决定也采用随机测试来指导我们最后三个阶段的计划。

迭代过程

尽管它们很重要，但就这篇博文而言，句法差异对我们来说并不感兴趣。因此，我不会谈及解析或打印。我们关心的是T-SQL和PL/pgSQL之间的函数、数据类型、查询和最肯定的过程的语义对应。换句话说，我们确实关心它们的正确翻译。

下面我将尝试描述我们是如何让随机测试来驱动我们的翻译工作，以发现语言之间没有记录的或难以发现的差异。这个过程的一个简化版本是：

  • 从T-SQL中抽取一个函数
  • 在PL/pgSQL中找出一个匹配的函数
  • 如果没有匹配的，就实现一个，并将其作为一个自定义函数加载到Postgres中。
  • 使用QuickCheck生成随机值，该函数通常会将这些值作为输入。
  • 在MS SQL中执行该查询
  • 自动翻译上述查询并在PostgreSQL中执行它
  • 比较返回的结果
  • 记录所有的差异并研究其背后的原因
  • 使用新学的关于该函数的知识来调整翻译或测试
  • 重复这个过程，直到两个数据库的输出完全相同或足够好，不管这意味着什么。

在上面的分解中，我们以函数为例，但这个过程也延伸到其他概念，我将在后面谈到。

数据库连接

为了让我们开始在这些引擎上执行查询，我们需要找出一种方法从Haskell连接到它们。在postgresql-simple包的帮助下，为Postres做这件事非常简单。连接到SQL Server就不那么简单了，因为没有可靠的开源解决方案可以使用。幸运的是，我们已经有了一个半完整的内部FreeTDS绑定，经过一些小的调整，我们能够成功地使用它来测试翻译器。

对于任何对连接ODBC兼容数据库感兴趣的人，Chris Done在这方面做了一些工作，并发布了一个功能齐全的odbc绑定，你也可以用它来连接SQL Server和其他数据库。

点击下面了解更多关于一个独特的提议

数据类型

我们从任何Haskeller最自然的地方开始测试，即数据类型。对我们来说，幸运的是，SQL标准在该领域对相关语言的影响最大。我们能够在Haskell中创建与两个引擎中类似的数据类型相对应的数据类型：DATETIME2 类似于TIMESTAMP ，INT 类似于INTEGER ，等等。

其中一个有趣的事情是找到两个引擎都同样支持的正确的数值范围，比如说时间戳范围。因为我们使用freetds 进行通信，所以与SQL Server原生支持的范围相比，我们遇到了一些更严格的限制。例如，DATE 的年份值可以在1753和9999之间，而SQL服务器的记录范围是0001到9999。然而，对于我们试图翻译的代码来说，这些界限已经足够好了。

其中最不容易的是unicode字符的范围。第一个问题是，PostgreSQL使用UTF-8，而SQL Server使用：

  • 对于NVARCHAR 。UCS-2，UTF-16的祖先。
  • 对于VARCHAR 。Windows-1252，一个更古老的扩展ASCII编码

代码点的实际编码/解码在Haskell中为我们解决了很多问题，所以通过使用一些辅助函数，我们可以将生成的数据以它们期望的格式传递给相应的数据库。

从Unicode标准中找到一个有效的字符子集是更具挑战性的部分。但是，在随机测试的帮助下，我们能够迅速识别所有的字符--在我们关心的字符中，这些字符在两个引擎之间表现不同。我们没有手动编写生成器来产生有效的Unicode代码点，而是采用了更简单的方法，只是下载了一个包含旧Unicode-1.1.5标准的所有字符的文件。我们通过禁用那些我们知道与我们无关的字符块来进一步减少输入：例如平假名、片假名、Bopomofo和许多其他字符。然后，我们所做的就是通过从一个预定义的列表中选择编码点来生成随机字符串，并将它们作为输入给各种函数。这种简单的技术不仅迅速揭示了UCS-2中不支持的所有代码点，而且还发现了一些重要的差异：

  • SQL Server将Unicode字符视为规范化的字符，而PostgreSQL则没有这样做。例如，a+˛ 的组合将等同于ą ，ss 等于ß ，等等。
  • 在SQL Server中的大小写不敏感的排序是有点问题的，因为Postgres不能做到开箱即用。
  • 旧的拼写法在一些代码点上会产生意想不到的结果，尽管它们在UCS-2中是存在的。

在这种方法中，QuickCheck最关键的功能，除了任意的数据生成，就是缩减。试图在充满随机垃圾的屏幕上识别一些不匹配的象形文字是非常可笑的，而且毫无意义，尤其是当库可以自动缩小这些数据的大小时，仍然会引发失败。

如果你不熟悉QuickCheck，这里有我几年前写的一篇介绍级博客。QuickCheck和测试的魔力。在不久的将来，我们还有一篇非常详细的关于属性测试的博文，敬请关注。

函数

由于函数与生成的数据紧密相连，我们将用一个具体的例子来扩展上一节。考虑一个简单的纯LEN() 函数，它对字符串（如CHAR,,VARCHAR, 等）进行操作并返回字符数。通过对文档的快速检查，很容易发现Postgres中的一个匹配函数，这就是length() 。容易被忽略的部分是，它们在某些输入上确实产生了不同的结果。具体来说，LEN() 将尾部的白色空间视为不重要的，而PostgreSQL只对CHAR 数据类型修剪所有的尾部白色空间，而对其他像VARCHAR 和TEXT 的字符串类型则不修剪。由于这是在类型层面上进行的，这种行为影响到所有的函数，而在SQL Server中，它只针对LEN() 。

有些人可能会觉得上述差异并不重要，但是像这样的小事情会以令人惊讶的方式极大地改变一个大程序的逻辑。但问题是，我们仅仅通过向两个函数输入随机字符串并比较输出结果，就能迅速发现这种奇特的差异。

另一个有趣的是ISNUMERIC() 。在测试过程中，我们不得不忽略一大堆字符的输入，如货币符号、方框字符、逗号、制表符、回车符等。这些字符在T-SQL中也被认为是数字，只要它们本身或在字符串的开头。我们确实需要产生一个尽可能接近的翻译，但翻译所有的bug似乎也是多余的。

当然，最容易翻译的是纯数学函数，但也不是所有的函数都是一对一的映射。需要注意的是，在比较产生的浮点结果时，我们不能期望它们是相同的，必须检查相等，直至出现错误。有几种方法可以考虑到小的误差，但这里是我最喜欢的一种，在过去对我很有效，即比较到一个相对误差。

epsilonEq :: (Num a, Ord a) =>
             a -- ^ Epsilon, a maximum tolerated error. Sign is ignored.
          -> a -- ^ Expected result.
          -> a -- ^ Tested value.
          -> Bool
epsilonEq epsilon x y = x == y || abs (y - x) <= n * epsilon
  where n = 1 + if abs x < abs y then abs y else abs x

查询和程序

T-SQL和PL/pgSQL都是程序性语言，因此它们依赖于世界的状态以及数据库中的数据。由于这个原因，设计可重复的测试要比在纯粹的环境中困难得多。然而，这仍然是可能的。

我想到的最简单的有状态的程序是那些返回当前时间（例如：GETUTCDATE() ）或随机数（例如：RAND() ）的程序。测试这类程序和其他依赖于它们的程序必须手工完成，可能需要一些微不足道的单元测试。但随机测试在这种情况下根本不适用。

各种查询和程序，如果只依赖于存储在数据库中的数据和输入参数，仍然可以用随机测试来测试。为了做到这一点，在运行属性之前，我们需要清空数据库，也许只是清空几个表，然后将初始数据加载到所需的表中。我们甚至可以从一些预定义的大集合中随机选择初始数据，只要我们知道所有这些数据都是有效的，而且我们试图测试的过程预计会返回相同的输出或以完全相同的方式修改数据库的状态。

重点是，在存在可变状态的情况下，作为一名QA工程师，我们有责任确定程序依赖于全局状态的哪些部分，并确保在每个测试用例执行之前，它被初始化为两个数据库的等效状态。同样地，我们需要确定它预计会修改数据库的哪些部分，这样我们就可以检索这些数据，并可靠地验证其在数据库之间的等效性。在某些情况下，如果我们能够在实际测试失败之前不重新初始化状态，我们可以加快甚至提高测试的质量，但这在很大程度上取决于我们试图测试的程序内部的逻辑。

总结

Haskell和随机化测试的力量与QuickCheck和Validity这样的库可以用在一些意想不到的场合。它被证明是一个非常有价值的工具，不仅可以验证实现的逻辑，还可以驱动实际的实现，这一点从 "测试优先 "的方法中可以看出。此外，我们所描述的过程，也可以应用于其他编程语言之间的翻译，它不一定非要针对SQL。

这里我想说的另一个迷人的观点是，翻译器本身并不一定要用Haskell写--尽管我不知道为什么你会选择其他语言。但它的要点是，我在这篇文章中描述的随机化测试套件并不直接依赖于翻译器这个库，只是依赖于它产生的输出，所以如果有必要，我们可以简单地把它当作一个黑盒子，同时使用所有我们喜欢的工具来测试它。

avatar
文章
阅读
粉丝