如何(单元)测试数据密集型PL/SQL应用程序 [英] How to (unit-)test data intensive PL/SQL application

问题描述

我们的团队愿意对正在运行的项目中编写的新代码进行单元测试，以扩展现有的大型Oracle系统.

该系统仅用PL/SQL编写，由数千个表，数百个存储过程包组成，主要是从表中获取数据和/或插入/更新其他数据.

我们的扩展名也不例外.大多数函数从一个非常复杂的SELECT语句中，通过许多相互绑定的表返回数据(在返回表之前添加一些逻辑)，或者从一个复杂的数据结构转换为另一个(以另一种方式复杂).

对此类代码进行单元测试的最佳方法是什么?

对于现有代码库，没有任何单元测试.更糟的是，只有包，触发器和视图受源代码控制，表结构(包括更改表"内容和必需的数据转换)是通过版本控制以外的其他渠道进行部署的.没有办法在我们项目的范围内进行更改.

维护测试数据集似乎是不可能的，因为每周都会有新代码部署到生产环境中，通常无需事先通知，通常会更改数据结构(在此添加一列，在此删除一列).

我很乐意为您提供帮助的任何建议或参考.一些团队成员可能会因弄清楚如何开始我们的单元测试经验而感到疲倦，因为他们没有涵盖PL/SQL数据密集型的旧系统(仅那些书中"未开发的Java项目).

解决方案

目前有几种不同的PL/SQL测试工具. 史蒂芬·费尔斯坦(Steven Feuerstein)写下了其中两个， Quest Code Tester for Oracle (以前称为QUTE).我是utplsql的忠实拥护者，但是它不再有一个活跃的支持社区(这很可惜).它也往往很冗长，尤其是在设置测试装置时.它确实具有纯PL/SQL软件包的基本虚拟特性；源代码有些陈旧，但它是FOSS.

QCTO带有GUI，这意味着-与其他Quest产品(即TOAD)一样，它仅适用于Windows.它不能完全自动执行测试数据生成，但是提供了一个支持它的接口.与其他Quest产品一样，QCTO也获得了许可，尽管有免费的副本.

Steven(公开，他是我的Oracle英雄之一)已经编写了所有PL/SQL测试工具的功能比较.显然，QOTC名列前茅，但我认为比较是诚实的. 查看.

关于utplsql中测试装置的建议

管理用于单元测试的测试数据可能是真正的痛苦.不幸的是，utplsql没有太多负担.所以

• 始终根据已知值进行测试:
  • 避免使用dbms_random;
  • 尝试将序列的使用限制为值无关紧要的列；
  • 日期也很棘手.避免对日期进行硬编码:使用以sysdate填充的变量.学习欣赏add_months()，last_day()，interval，trunc(sysdate, 'MM')等
• 隔离其他用户的测试数据.从头开始构建它.尽可能使用不同的值.
• 仅创建所需数量的测试数据.体积测试是另一项责任.
• 在更改数据的测试过程中，会为每个单元测试创​​建特定的记录.
• 也:不要依赖一个测试的成功输出来提供另一个测试的输入.
• 在适当的情况下，仅对单元测试之间的数据共享记录进行报告的测试程序.
• 尽可能共享框架数据(例如引用的主键).
• 使用自由文本字段(名称，描述，注释)来标识使用记录的测试.
• 最小化创建新记录所涉及的工作:
  • 仅分配测试套件和表约束所需的值；
  • 尽可能使用默认值；
  • 尽可能地进行程序化.

其他需要牢记的东西:

• 设置测试夹具可能是一项耗时的工作.如果您有大量数据，请考虑建立一个过程来设置可以在每个会话中运行一次的静态数据，并且在ut_setup本身中仅包含易失性数据.在测试只读功能时，这特别有用.
• 请记住，创建测试数据本身就是一项编程活动，因此容易出现错误.
• 使用utplsql的所有功能.在推断易失性数据的值时，utAssert.EqQuery，utAssert.EqQueryValue，utAssert.EqTable，utAssert.EqTabCount和utAssert.Eq_RefC_Query都是非常有用的功能.
• 在诊断测试运行异常时，如预期的那样，使用已使用的数据可能会很有用.因此，请考虑使用空洞的ut_teardown过程，并在ut_setup开始时清除测试数据.

处理旧版代码

评论加里的帖子使我想起了您可能会发现有用的另一件事. Steven F将ulplsql编写为JUnit的PL/SQL实现，而JUnit是Test First运动的Java先锋.但是，TDD的技术也可以应用于大量的遗留代码(在这种情况下，遗留代码是没有任何单元测试的任何程序集).

要记住的关键是，您不必立即对所有内容进行单元测试.逐步开始.为新内容构建单元测试，测试优先" .在应用更改之前，对要更改的位进行构建单元测试，因此您知道在进行更改后它们仍然可以工作.

这方面有很多想法，但是(不可避免的是，如果丢脸的话)它主要来自OO程序员.迈克尔·费瑟斯(Michael Feathers)是主要角色.阅读他的文章有效地使用旧版代码.如果您觉得有帮助，他随后写了一本同名的书.

Our team is willing to unit-test a new code written under a running project extending an existing huge Oracle system.

The system is written solely in PL/SQL, consists of thousands of tables, hundreds of stored procedures packages, mostly getting data from tables and/or inserting/updating other data.

Our extension is not an exception. Most functions return data from a quite complex SELECT statementa over many mutually bound tables (with a little added logic before returning them) or make transformation from one complicated data structure to another (complicated in another way).

What is the best approach to unit-test such code?

There are no unit tests for existing code base. To make things worse, only packages, triggers and views are source-controlled, table structures (including "alter table" stuff and necessary data transformations are deployed via channel other than version control). There is no way to change this within our project's scope.

Maintaining testing data set seems to be impossible since there is new code deployed to the production environment on weekly basis, usually without prior notice, often changing data structure (add a column here, remove one there).

I'd be glad for any suggestion or reference to help us. Some team members tend to be tired by figuring out how to even start for our experience with unit-testing does not cover PL/SQL data intensive legacy systems (only those "from-the-book" greenfield Java projects).

解决方案

There are several different test tools for PL/SQL out there. Steven Feuerstein has written two of them, utplsql and Quest Code Tester for Oracle (formerly QUTE). I am a big fan of utplsql, but it no longer has an active support community (which is a shame). It also tends to be quite verbose, especially when it comes to setting up test fixtures. It does have the cardinal virtual of being pure PL/SQL packages; the source code is a bit gnarly but it is FOSS.

QCTO comes with a GUI, which means - like other Quest products i.e. TOAD - it is Windows only. It doesn't exactly automate test data generation, but it provides an interface to support it. Also like other Quest products, QCTO is licensed although there is a freeware copy.

Steven (disclosure, he he is one of my Oracle heroes) has written a feature comparison of all the PL/SQL testing tools. Obviously, QOTC comes out tops, but I think the comparison is honest. Check it out.

Advice on test fixtures in utplsql

Managing test data for unit testing can be a real pain in the neck. Unfortunately utplsql doesn't offer much to shoulder the burden. So

• Always test against known values:
  • Avoid using dbms_random;
  • Try to restrict the use of sequences to columns whose values don't matter;
  • Dates are also tricky. Avoid hard-coding dates: use variables which are populated with sysdate. Learn to appreciate add_months(), last_day(), interval, trunc(sysdate, 'MM'), etc.
• Isolate the test data from other users. Build it from scratch. Use distinctive values wherever possible.
• Only create as much test data as you need. Volumetric testing is a different responsibility.
• When testing procedures which change the data create specific records for each unit test.
• Also: don't rely on the successful output from one test to provide the input from another test.
• When testing procedures which simply report against data share records between unit tests when appropriate.
• Share framework data (e.g. referenced primary keys) whenever possible.
• Use free text fields (names, descriptions, comments) to identify which test or tests use the record.
• Minimise the work involved in creating new records:
  • Only assign values which are necessary to the test suite and the table's constraints;
  • Use default values as much as possible;
  • Proceduralize as much as possible.

Other things to bear in mind:

• setting up a test fixture can be a time-consuming exercise. If you have a lot of data consider building a procedure to set up the static data which can be run once per session, and include only volatile data in the ut_setup itself. This is especially helpful when testing read-only functionality.
• remember that creating test data is a programming exercise in its own right, and so prone to bugs.
• use all the features of utplsql. utAssert.EqQuery, utAssert.EqQueryValue, utAssert.EqTable, utAssert.EqTabCount and utAssert.Eq_RefC_Query are all very useful features when it comes to inferring the values of volatile data.
• when diagnosing a test run which didn't go the way we were expecting it can be useful to have the data which was used. So consider having a hollow ut_teardown procedure and clearing down the test data at the start of ut_setup.

Dealing with legacy code

Commenting on Gary's post reminded me of one other thing you may find useful. Steven F wrote ulplsql as a PL/SQL implementation of JUnit, the Java vanguard of the Test First movement. However, the techniques of TDD can be also applied to large amounts of legacy code (in this context, legacy code is any set of programs without any unit tests).

The key thing to bear in mind is that you don't have to get everything under unit test immediately. Start incrementally. Build unit tests for new stuff, Test First. Build unit tests for the bits you're going to change before you apply the change, so you know they still work after you have made the change.

There is a lot of thought in this area, but (inevitably if shamefully) it mainly comes from the OO programmers. Michael Feathers is the main chap. Read his article Working Effectively With Legacy Code. If you find it helpful he subsequently wrote a book of the same name.

这篇关于如何(单元)测试数据密集型PL/SQL应用程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！