您使用哪些最佳实践来测试数据库查询？ [英] What best practices do you use for testing database queries?

问题描述

我目前正在测试我们的解决方案，它具有完整的色域层：UI，中间层和无处不在的数据库。

I'm currently in the process of testing our solution that has the whole "gamut" of layers: UI, Middle, and the omnipresent Database.

到现在的团队，查询测试是由测试人员手动创建查询，理论上返回一个结果集的存储过程应该返回基于各种相关性规则，排序，你有什么。

Before my arrival on my current team, query testing was done by the testers manually crafting queries that would theoretically return a result set that the stored procedure should return based on various relevancy rules, sorting, what have you.

这对测试者的查询提出了bug，而不是针对实际的查询。

This had the side effect of bugs being filed against the tester's query more often than against the actual query in question.

我建议使用已知的结果集，你可以只是推断它应该如何返回，因为你控制数据存在 - 以前，数据从生产，消毒，然后填充在我们的测试数据库。

I proposed actually working with a known result set that you could just infer how it should return since you control the data present -- previously, data was pulled from production, sanitized, and then populated in our test databases.

人们仍然坚持创建自己的查询来测试开发人员创建的内容。我怀疑很多人还是。我认为这是不理想的，只是不必要地增加我们的测试足迹。

People were still insistent on creating their own queries to test what the developers have created. I suspect that many still are. I have it in my mind that this isn't ideal at all, and just increases our testing footprint needlessly.

所以，我很好奇，你使用的做法测试这样的场景，以及什么会被认为是理想的最佳端到端覆盖你可以得到，而不引入混乱数据？

So, I'm curious, which practices do you use to test scenarios like this, and what would be considered ideal for the best end-to-end coverage you can get, without introducing chaotic data?

我有的问题是哪里是最好的地方做什么测试。我只是直接戳服务，并将该数据集与我可以从存储过程中拉出来？我有一个大概的想法，并已经取得足够的成功，到目前为止，但我觉得我们仍然缺少一些重要的在这里，所以我期待社区，看看他们是否有任何有价值的见解，可能有助于制定我的测试方法更好。

The issue I have is where's the best place to do what testing. Do I just poke the service directly, and compare that dataset to that which I can pull from the stored procedure? I have a rough idea, and have been successful enough so far, but I feel like we're still missing something important here, so I'm looking to the community to see if they have any valuable insights that might help formulate my testing approach better.

推荐答案

测试存储过程会要求测试的每个人都有一个单独的数据库实例。这是一个要求。如果您共享环境，您将无法依赖测试的结果。它们将毫无价值。

Testing stored procs will require that each person who tests has a separate instance of the db. This is a requirement. If you share environments you won't be able to rely upon the results of your test. They'll be worthless.

您还需要确保在每次测试后将数据库回滚到之前的状态，以使结果可预测和稳定。因为这需要在每次测试后回滚状态，这些测试将需要比标准单元测试更长的时间才能完成，所以它们可能是你想要在夜间运行的东西。

You will also need to ensure that you roll back the db to it's previous state after every test so as to make the results predictable and stable. Because of this need to roll back the state after every test these tests will take a lot longer to complete than standard unit tests so they'll probably be something you want to run over night.

有几个工具可以帮助你。 DbUnit是其中之一，我也相信微软有一个Visual Studio for Database Professionals，它包含一些对数据库测试的支持。

There are a few tools out there to help you with this. DbUnit is one of them and I also believe Microsoft had a tool Visual Studio for Database Professionals that contained some support for DB testing.

这篇关于您使用哪些最佳实践来测试数据库查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！