编写一个单元测试框架,测试SQL存储过程Writing a unit testing framework for testing SQL stored procedures

- 此内容更新于:2014-12-30
主题:

原文:

Today I had an idea of writing a unit testing framework for stored procedures in MySQL. The full idea is written on a recent post on my blog. In short it goes like this: I want to automate my procedure testing, I want to use a standardized way to test my procedures. Unit testing is widely documented, and there are a zillion XUnit frameworks out there, why not write one for MySQL (or any other database). It would be open source of course. What do you think? It is silly, stupid, needless or what? Or another idea would be to write a general database framework in SQL. Hmm, I really want to discuss that with someone, collect thoughts and ideas.

vfilby的回复:不同但相关的场景:我见过的情况下,外键不小心被撤销,一两个月后造成严重破坏。这可能是一个好主意自动化测试对于这种情况。

(原文:A different but related scenario: I have seen cases where foreign keys were accidentally dropped and caused havoc after a month or two. It might be a good idea to have automated tests for scenarios like this.)

Nikola Stjelja的回复:的,当然. .你可以单元测试整个数据库,设置场景,测试它们,然后回滚数据库到其之前的状态。每一个主要RDMBS工具来这样做。

(原文:of, course.. you could actually unit test your entire database, setting scenarios , testing them and then rolling back the database to its previous state. Every major RDMBS has tools to do that.)

FlySwat的回复:它是愚蠢的,愚蠢的,不必要的或什么?是的

(原文:It is silly, stupid, needless or what? Yes)

Nikola Stjelja的回复:任何具体arugments

(原文:Any concrete arugments)

Michael Mior的回复:你的意思喜欢MyTAP吗?

(原文:You mean like MyTAP?)

解决方案:
已经有一个测试框架为Sql Server - TSQLUnit。也许你可以从中获得一些有用的信息。
原文:

There's already one testing framework for Sql Server - TSQLUnit. Maybe you can get some useful info from it.

Nikola Stjelja的回复:非常感谢的链接,我将检查它。

(原文:Thnx for the link, I will sure check it.)

解决方案:
是的,好主意。我一直有一个公平的和pgTAP成功。我使用它在许多项目中,对现有数据库测试驱动开发和编写测试程序,以便能够有效地重构。 我经常被问到如果有类似MySQL。也许你写什么了?
原文:

Yes, great idea. I've been having a fair bit of success with pgTAP. I've used it in a number of projects, both for test driven database development and to write tests for existing procedures in order to be able to effectively refactor them.

I have often been asked if there's something like it for MySQL. Maybe you've written something by now?

解决方案:
已经有单元测试。除了dbUnit和sqlUnit,试试: MyTAP:http://theory.github.com/mytap/ datacharmer.org网站:datacharmer.org
原文:

There are already unit tests out there. Apart from dbUnit and sqlUnit, try:

MyTAP: http://theory.github.com/mytap/ datacharmer.org: http://datacharmer.blogspot.com/2006/01/mysql-5-general-purpose-routine_27.html

解决方案:
原文:

I tend to unit test the data access layer, it is always a pain in the ass because you have to setup a proper database with proper data. There are data generators out there that can help (like RedGate's Data Generator) make the setup process simpler.

My thinking behind just testing the DAL, is that you are essentially testing the stored procedures themselves with the added .Net DB code which I don't think we need to worry about unit testing. This way you can leverage all the tools and processes you already have for unit testing. It seems like a lot of effort to develop a separate framework for something that can (IMHO) be performed equally well with existing tools.

I have an open mind though. If there are benefits I am overlooking please do tell me.

Cheers, V

flukus的回复:如果它触动数据库变# 39;不是一个单元测试

(原文:If it touches the database it's not a unit test)

vfilby的回复:然后调用它集成测试。我不# 39;认为我们会得到一个更好的答案,认为语义。

(原文:Then call it an integration test. I don't think we are going to get a better answer by arguing semantics.)

解决方案:
原文:

One of the benefits would be that the test would be written in the same envirment the stored procedures written and executed, maintaned by a specialized databse developer outside the main application. There is no need for the application developer to be a master of programming a relational database nor for the database developer to master an modern application developing language. You now have test for every thing. Why not have them for the database written sql and executed outside any in house developed application.

If you are developing a multi tier application it makes sense to separate each part and test it separately.

vfilby的回复:我想在我的印象中,数据库主要是针对数据,我尽量保持逻辑的数据库。甚至大多数的我免费存储处理器相对逻辑。我看到点dba # 39;年代,我# 39;想让他们远离我的单元测试:)

(原文:I guess in my mind, databases were primarily meant for data, I try to keep logic out of the database. Even the majority of my stored procs are relatively logic free. I see the point for DBA's, I'd like to keep them away from my unit tests :))

Nikola Stjelja的回复:是的,他们是正确的,但他们也有数据操作功能。我们usaually做的是提供在我们的软件和业务逻辑调用数据库,但是我们真正需要的是通过一些paramteres并期望结果是在一个电话。当我们做到这一点,我们需要自动化测试。

(原文:Yes they are, but they also have data manipulation capabilites. What we are usaually doing is providing bussiness logic in our software mixed with calls to the database, but what we really need is to pass some paramteres and expect a result in one call. When we do that, we need to automate tests.)

解决方案:
试着测试:http://tst.codeplex.com
原文:

Try TST: http://tst.codeplex.com

Rahat Khanna的回复:这对MySql单元测试并不是。人要求为MySql开发单元测试框架。

(原文:This Unit Testing is not for MySql. The person is asking to develop a Unit Testing Framework for MySql.)

解决方案:
不应该有足够有价值的逻辑在数据库中进行测试。
原文:

There shouldn't be enough logic in the database to make testing worthwhile.

Nikola Stjelja的回复:为什么不呢?一个数据库是一个强大的工具,在大多数情况下是未使用的。为什么不使用它呢?

(原文:Why not? A database is a powerful tool which in most cases sits unused to the full. Why not use it?)

Hogan的回复:噢。你忘了,有时数据是错误的。

(原文:oops. You forget, sometimes the data is wrong.)