使用 tSQLt 对计算列进行单元测试
我最近一直在对调用包含（持久）计算列的表的过程编写一些 tSQLt 数据库单元测试（通过 Red Gate SQL Test），请注意，如果我使用 FakeTable SP，我发现计算列未填充（他们评估为空）。计算列是测试的关键，因此我不能忽略测试中的列，而且我不想重复逻辑。
我正在使用 tSQLt.AssertEqualsTable SP 评估结果，因此我想确保两者中的列值相同。
在实践中，我通过不使用 FakeTable 而是在测试结束时使用（部分）回滚事务语句来解决这个问题（根据 http://sqlity.net/en/585/how-to-rollback-in-procedures/）或显式删除测试价值观。
我确信一定有更好的方法来编码这个测试，并且欢迎任何建议。
I’ve been writing some tSQLt database unit tests (via Red Gate SQL Test) on procedures which call tables containing (persisted) computed columns recently, and note that if I use the FakeTable SP, I find that the computed columns are not populated (they evaluate as null). The computed column is key to the test, so I can't just ignore the column in the test, and I'd rather not duplicate the logic.
I'm evaluating the results using the tSQLt.AssertEqualsTable SP, and so I want to make sure the column values are the same in both.
In practice, I’ve worked around this by not using FakeTable, but by using a (partial) rollback transaction statement at the end of the test (per the blog post at http://sqlity.net/en/585/how-to-rollback-in-procedures/) or explicitly deleting the test values.
I'm sure there must be a better way of coding this test, and would welcome any suggestions.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
测试时应将计算列中的逻辑与过程中的逻辑分开。该过程将获取该列中的信息并对其采取行动。该过程不应该关心该列是计算列还是实际列。这意味着，在您的测试中，您可以硬编码一个值以放入该列中。 FakeTable 通过将任何计算列转换为真实列来实现这一点。
在另一组测试中，您可以（并且应该）测试计算列的计算是否正确。
为此，可以使用 FakeTable 的补充。这保留了表的计算属性。您需要将 EXECUTE tSQLt.FakeTable 的 @CompulatedColumn 参数设置为 1。 (http:// /tsqlt.org/user-guide/isolated-dependency/faketable/）
顺便说一句，您不需要在测试中回滚任何内容。 tSQLt 已经在处理这个问题了。如果事务管理是该过程的要求，则您提到的文章中描述的逻辑仅在您自己的过程中需要。
You should separate the logic in the computed column from the logic in the procedure when testing. The procedure is going to take the information in that column and act on it. The procedure should not care about the column being a computed column or a real column. That means, that in your test you can hardcode a value to put in that column. FakeTable makes that possible by turning any computed column into a real column.
In another set of tests you can (and should) test that the computed column is computed correctly.
For that an addition to FakeTable is available. This preserves the computed property of the table. You need to set the @ComputedColumn parameter of EXECUTE tSQLt.FakeTable to 1. (http://tsqlt.org/user-guide/isolating-dependencies/faketable/)
Btw, you do not need to rollback anything in a test. tSQLt is taking care of that already. The logic described in the article you mentioned is only needed in your own procedure, if transaction management is a requirement of that proc.
现在，邮件列表中提供了 tSQLt 的预发布更新：http://groups.google.com /group/tsqlt
预发行版包含在 FakeTable 期间保留计算列或默认值的功能。
示例：
EXEC tSQLt.FakeTable 'dbo.tst1', @CompulatedColumns = 1;
EXEC tSQLt.FakeTable 'dbo.tst1', @Defaults = 1;
这些将很快准备好正式发布。
There is now a pre-release update to tSQLt available on the mailing list: http://groups.google.com/group/tsqlt
The pre-release contains features to preserve computed columns or defaults during FakeTable.
Examples:
EXEC tSQLt.FakeTable 'dbo.tst1', @ComputedColumns = 1;
EXEC tSQLt.FakeTable 'dbo.tst1', @Defaults = 1;
These will be ready for official release soon.