I'm currently in the process of testing our solution that has the whole "gamut" of layers: UI, Middle, and the omnipresent Database.
我目前正在测试我们的解决方案，该解决方案具有完整的“色域”层：UI，中间和无所不在的数据库。
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.
在我到达当前团队之前，查询测试是由测试人员手动制作查询，理论上会返回一个结果集，存储过程应根据各种相关性规则返回，排序，你有什么。
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.
我遇到的问题是进行测试的最佳位置。我是否只是直接戳服务，并将该数据集与我可以从存储过程中提取的数据进行比较？我有一个粗略的想法，并且到目前为止已经取得了足够的成功，但我觉得我们仍然缺少一些重要的东西，所以我希望社区能够看到他们是否有任何有价值的见解可能有助于制定我的测试方法更好。
8 个解决方案
#1
3
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.
测试存储过程将要求每个测试人员都有一个单独的db实例。这是一项要求。如果您共享环境，则无法依赖测试结果。他们将毫无价值。
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.
您还需要确保在每次测试后将数据库回滚到其先前的状态，以使结果可预测且稳定。由于需要在每次测试后回滚状态，因此这些测试比标准单元测试需要更长的时间才能完成，所以它们可能是你想要运行一夜的东西。
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.
有一些工具可以帮助您解决这个问题。 DbUnit就是其中之一，我也相信微软有一个Visual Studio for Database Professionals工具，它包含对数据库测试的一些支持。
#2
3
Here are some guidelines:
以下是一些指导原则：
- Use an isolated database for unit testing (e.g. No other test runs or activity)
- 使用隔离数据库进行单元测试（例如，没有其他测试运行或活动）
- Always insert all the test data you intend to query within the same test
- 始终在同一测试中插入您要查询的所有测试数据
- Write the tests to randomly create different volumes of data e.g. random number of inserts say between 1 and 10 rows
- 编写测试以随机创建不同数量的数据，例如随机插入数量表示1到10行
- Randomize the data e.g. for a boolean field random insert and true or false
- 随机化数据，例如对于布尔字段随机插入和true或false
- Keep a count in the test of the variables (e.g. number of rows, number of trues)
- 保持对变量测试的计数（例如行数，真实数）
- For the Asserts execute query and compare against local test variables
- 对于Asserts执行查询并与本地测试变量进行比较
- Use Enterprises Services transactions to rollback database to previous state
- 使用企业服务事务将数据库回滚到以前的状态
See the link below for the Enterprises Services Transaction technique:
请参阅以下链接，了解企业服务交易技术：
http://weblogs.asp.net/rosherove/articles/DbUnitTesting.aspx
http://weblogs.asp.net/rosherove/articles/DbUnitTesting.aspx
#3
1
As part of our continuous integration, we run our nightly 'build' of the database queries. This involves a suite of DB calls which are updated regularly from the real calls in the code as well as any expected ad-hoc queries.
作为我们持续集成的一部分，我们运行数据库查询的夜间“构建”。这涉及一组DB调用，它们定期根据代码中的实际调用以及任何预期的即席查询进行更新。
These calls are timed to ensure that:
这些电话是为了确保：
1/ They don't take too long.
1 /他们不需要太长时间。
2/ They don't differ wildly (in a bad way) from the previous night.
2 /它们与前一晚的情况差别不大（差劲很大）。
In this way, we catch errant queries or DB changes quickly.
通过这种方式，我们可以快速捕获错误的查询或数据库更改。
#4
1
The query planner is your friend, especially in this case. It is always good practice to check to see that indexes are used when you expect them to be and that the query doesn't require extra work to be done. Even if you have stress tests included in your suite, it is still a good idea to catch expensive queries before your app starts grinding to a halt.
查询计划程序是您的朋友，尤其是在这种情况下。最好检查一下索引是否在您期望的时候使用，并且查询不需要额外的工作。即使您的套件中包含压力测试，在应用程序开始停止之前捕获昂贵的查询仍然是个好主意。
#5
1
We have a blank database set aside for each developer and tester.
我们为每个开发人员和测试人员预留了一个空白数据库。
When the tests are run - each test clears the database and loads the data it is expecting to use. This gives us a known state at all times.
运行测试时 - 每个测试都会清除数据库并加载它希望使用的数据。这在任何时候都给我们一个已知的状态。
We can then test several different scenarios on the same DB (one after the other) and we never stamp on another testers toes.
然后我们可以在同一个DB上测试几个不同的场景（一个接一个），我们永远不会在另一个测试者脚趾上加盖标记。
That covers testing the data access itself. For service testing we do much the same thing but we test the inside of the service only - we don't actually hit the service we create an instance of the service processing class and pass in everything we need. That way we are testing the code and not the infrastructure (message etc..)
这包括测试数据访问本身。对于服务测试，我们做了很多相同的事情，但我们只测试服务的内部 - 我们实际上没有点击服务，我们创建服务处理类的实例并传递我们需要的一切。这样我们就是测试代码而不是基础设施（消息等）。
#6
1
Django offers a database unit test capability. You can borrow their design ideas and reproduce it in other environments.
Django提供数据库单元测试功能。您可以借用他们的设计理念并在其他环境中重现它。
The Django folks offer a subclass of Python's standard unittest TestCase class that populates a database with a known fixture -- a known set of data rows.
Django人员提供Python的标准unittest TestCase类的子类，该类使用已知的fixture（一组已知的数据行）填充数据库。
In the case of Django (and Python) it's easiest to populate the database from a JSON data extract. Other file formats for the fixture can be used for other frameworks. For example, if you're working in Oracle, you might find CSV files easier to work with.
在Django（和Python）的情况下，最简单的方法是从JSON数据提取中填充数据库。夹具的其他文件格式可用于其他框架。例如，如果您在Oracle中工作，则可能会发现CSV文件更易于使用。
This TestCase subclass allows to write a typical-looking TestCase that exercises the database with the known data fixture.
这个TestCase子类允许编写一个典型的TestCase，它使用已知的数据夹具来运行数据库。
Additionally, the Django test runner creates a temporary schema for test purposes. This is easy for Django because they have a complete object-relational management component that includes DDL creation. If you don't have this available, you'll still need the DDL script so you can create and dispose of a test schema for unittest purposes.
此外，Django测试运行器为测试目的创建临时模式。这对Django来说很容易，因为它们有一个完整的对象关系管理组件，包括DDL创建。如果您没有这个，您仍然需要DDL脚本，以便您可以创建和处置测试模式以进行单元测试。
#7
1
SQLServerCentral has an article here (you may have to register but it is free and without strings) about a TSQL Unit Testing Framework called tsqlUnit. It is open source and follows along in the tradition of the xUnit framework.
SQLServerCentral在这里有一篇文章（你可能需要注册，但它是免费的，没有字符串）关于一个名为tsqlUnit的TSQL单元测试框架。它是开源的，并遵循xUnit框架的传统。
It follows the SEAT TDD pattern:
它遵循SEAT TDD模式：
Setup - prepare the test conditions by manipulating the objects, tables, and/or data
设置 - 通过操作对象，表格和/或数据来准备测试条件
Exercise - invoke the production code
练习 - 调用生产代码
Assert - check that the actual result equals the expected result
断言 - 检查实际结果是否等于预期结果
Teardown - return everything back to the way it was before the test started. This is actually done by Rolling back a transaction, which keeps everything nice and tidy.
拆解 - 将所有内容恢复到测试开始前的状态。这实际上是通过回滚事务来完成的，这使得一切都变得干净整洁。
Although I have not used it, it looks promising and is certainly something I will be looking at in more detail.
虽然我没有使用它，但它看起来很有希望，我肯定会更详细地研究它。
The framework can be downloaded here.
该框架可以在这里下载。
#8
0
I find it useful to test the SQL being sent down to the database rather than the result of querying the database.
我发现测试发送到数据库的SQL而不是查询数据库的结果很有用。
Not that I don't do the later, but I find it much faster to test for that than having the database too much lifting.
并不是说我没有做到这一点，但我发现测试它要比使数据库过多提升要快得多。
#1
3
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.
测试存储过程将要求每个测试人员都有一个单独的db实例。这是一项要求。如果您共享环境，则无法依赖测试结果。他们将毫无价值。
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.
您还需要确保在每次测试后将数据库回滚到其先前的状态，以使结果可预测且稳定。由于需要在每次测试后回滚状态，因此这些测试比标准单元测试需要更长的时间才能完成，所以它们可能是你想要运行一夜的东西。
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.
有一些工具可以帮助您解决这个问题。 DbUnit就是其中之一，我也相信微软有一个Visual Studio for Database Professionals工具，它包含对数据库测试的一些支持。
#2
3
Here are some guidelines:
以下是一些指导原则：
- Use an isolated database for unit testing (e.g. No other test runs or activity)
- 使用隔离数据库进行单元测试（例如，没有其他测试运行或活动）
- Always insert all the test data you intend to query within the same test
- 始终在同一测试中插入您要查询的所有测试数据
- Write the tests to randomly create different volumes of data e.g. random number of inserts say between 1 and 10 rows
- 编写测试以随机创建不同数量的数据，例如随机插入数量表示1到10行
- Randomize the data e.g. for a boolean field random insert and true or false
- 随机化数据，例如对于布尔字段随机插入和true或false
- Keep a count in the test of the variables (e.g. number of rows, number of trues)
- 保持对变量测试的计数（例如行数，真实数）
- For the Asserts execute query and compare against local test variables
- 对于Asserts执行查询并与本地测试变量进行比较
- Use Enterprises Services transactions to rollback database to previous state
- 使用企业服务事务将数据库回滚到以前的状态
See the link below for the Enterprises Services Transaction technique:
请参阅以下链接，了解企业服务交易技术：
http://weblogs.asp.net/rosherove/articles/DbUnitTesting.aspx
http://weblogs.asp.net/rosherove/articles/DbUnitTesting.aspx
#3
1
As part of our continuous integration, we run our nightly 'build' of the database queries. This involves a suite of DB calls which are updated regularly from the real calls in the code as well as any expected ad-hoc queries.
作为我们持续集成的一部分，我们运行数据库查询的夜间“构建”。这涉及一组DB调用，它们定期根据代码中的实际调用以及任何预期的即席查询进行更新。
These calls are timed to ensure that:
这些电话是为了确保：
1/ They don't take too long.
1 /他们不需要太长时间。
2/ They don't differ wildly (in a bad way) from the previous night.
2 /它们与前一晚的情况差别不大（差劲很大）。
In this way, we catch errant queries or DB changes quickly.
通过这种方式，我们可以快速捕获错误的查询或数据库更改。
#4
1
The query planner is your friend, especially in this case. It is always good practice to check to see that indexes are used when you expect them to be and that the query doesn't require extra work to be done. Even if you have stress tests included in your suite, it is still a good idea to catch expensive queries before your app starts grinding to a halt.
查询计划程序是您的朋友，尤其是在这种情况下。最好检查一下索引是否在您期望的时候使用，并且查询不需要额外的工作。即使您的套件中包含压力测试，在应用程序开始停止之前捕获昂贵的查询仍然是个好主意。
#5
1
We have a blank database set aside for each developer and tester.
我们为每个开发人员和测试人员预留了一个空白数据库。
When the tests are run - each test clears the database and loads the data it is expecting to use. This gives us a known state at all times.
运行测试时 - 每个测试都会清除数据库并加载它希望使用的数据。这在任何时候都给我们一个已知的状态。
We can then test several different scenarios on the same DB (one after the other) and we never stamp on another testers toes.
然后我们可以在同一个DB上测试几个不同的场景（一个接一个），我们永远不会在另一个测试者脚趾上加盖标记。
That covers testing the data access itself. For service testing we do much the same thing but we test the inside of the service only - we don't actually hit the service we create an instance of the service processing class and pass in everything we need. That way we are testing the code and not the infrastructure (message etc..)
这包括测试数据访问本身。对于服务测试，我们做了很多相同的事情，但我们只测试服务的内部 - 我们实际上没有点击服务，我们创建服务处理类的实例并传递我们需要的一切。这样我们就是测试代码而不是基础设施（消息等）。
#6
1
Django offers a database unit test capability. You can borrow their design ideas and reproduce it in other environments.
Django提供数据库单元测试功能。您可以借用他们的设计理念并在其他环境中重现它。
The Django folks offer a subclass of Python's standard unittest TestCase class that populates a database with a known fixture -- a known set of data rows.
Django人员提供Python的标准unittest TestCase类的子类，该类使用已知的fixture（一组已知的数据行）填充数据库。
In the case of Django (and Python) it's easiest to populate the database from a JSON data extract. Other file formats for the fixture can be used for other frameworks. For example, if you're working in Oracle, you might find CSV files easier to work with.
在Django（和Python）的情况下，最简单的方法是从JSON数据提取中填充数据库。夹具的其他文件格式可用于其他框架。例如，如果您在Oracle中工作，则可能会发现CSV文件更易于使用。
This TestCase subclass allows to write a typical-looking TestCase that exercises the database with the known data fixture.
这个TestCase子类允许编写一个典型的TestCase，它使用已知的数据夹具来运行数据库。
Additionally, the Django test runner creates a temporary schema for test purposes. This is easy for Django because they have a complete object-relational management component that includes DDL creation. If you don't have this available, you'll still need the DDL script so you can create and dispose of a test schema for unittest purposes.
此外，Django测试运行器为测试目的创建临时模式。这对Django来说很容易，因为它们有一个完整的对象关系管理组件，包括DDL创建。如果您没有这个，您仍然需要DDL脚本，以便您可以创建和处置测试模式以进行单元测试。
#7
1
SQLServerCentral has an article here (you may have to register but it is free and without strings) about a TSQL Unit Testing Framework called tsqlUnit. It is open source and follows along in the tradition of the xUnit framework.
SQLServerCentral在这里有一篇文章（你可能需要注册，但它是免费的，没有字符串）关于一个名为tsqlUnit的TSQL单元测试框架。它是开源的，并遵循xUnit框架的传统。
It follows the SEAT TDD pattern:
它遵循SEAT TDD模式：
Setup - prepare the test conditions by manipulating the objects, tables, and/or data
设置 - 通过操作对象，表格和/或数据来准备测试条件
Exercise - invoke the production code
练习 - 调用生产代码
Assert - check that the actual result equals the expected result
断言 - 检查实际结果是否等于预期结果
Teardown - return everything back to the way it was before the test started. This is actually done by Rolling back a transaction, which keeps everything nice and tidy.
拆解 - 将所有内容恢复到测试开始前的状态。这实际上是通过回滚事务来完成的，这使得一切都变得干净整洁。
Although I have not used it, it looks promising and is certainly something I will be looking at in more detail.
虽然我没有使用它，但它看起来很有希望，我肯定会更详细地研究它。
The framework can be downloaded here.
该框架可以在这里下载。
#8
0
I find it useful to test the SQL being sent down to the database rather than the result of querying the database.
我发现测试发送到数据库的SQL而不是查询数据库的结果很有用。
Not that I don't do the later, but I find it much faster to test for that than having the database too much lifting.
并不是说我没有做到这一点，但我发现测试它要比使数据库过多提升要快得多。