编写一个单元测试框架,测试SQL存储过程Writing a unit testing framework for testing SQL stored procedures
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.
(原文：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.
(原文：Thnx for the link, I will sure check it.)
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:
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.
(原文：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.
(原文：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.)
Try TST: http://tst.codeplex.com
(原文：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.
(原文：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.)
- 有什么问题这个create table语句(复制)What is wrong with this create table statement [duplicate]
- 是什么数据类型使用散列密码字段长度呢?What data type to use for hashed password field and what length?
- 这是什么时间格式?(不是UNIX,UTC,没有什么)What time format is this? (not UNIX, not UTC, nothing)
- 通过cli从远程mysql导入sql服务器mysql import sql via cli from remote server
- 最好的货币MySQL数据类型是什么?What is the best datatype for currencies in MySQL?
- 编写一个单元测试框架,测试SQL存储过程Writing a unit testing framework for testing SQL stored procedures
- 我怎么能在MySQL在两列布尔逻辑吗?How can I do boolean logic on two columns in MySQL?
- 我怎么做布尔逻辑在MySql的两列,其中一个是一个Varchar吗?How do I do boolean logic on two columns in MySql, one of which is a Varchar?
- 我如何创建一个有序列表最常见的子字符串在MySQL varchar列?How can I create an ordered list of the most common substrings inside of my MySQL varchar column?
- 简单随机样本从一个Sql数据库Simple Random Samples from a Sql database
- 查询优化技术?(关闭)Query optimization techniques? [closed]