PHPUnit's own manual has some as-yet-unwritten sections entitled "Operations" and "Database Testing Best Practices."
PHPUnit自己的手册中有一些尚未编写的章节，标题是“操作”和“数据库测试最佳实践”。
What are best practices for testing a database with PHPUnit, particularly in MySQL?
使用PHPUnit(尤其是MySQL)测试数据库的最佳实践是什么?
2 个解决方案
#1
9
When I'm doing database testing with PHPUnit I load my MySQL dump at the start of the first suite which contains any of the information I'm assuming to be true across all tests. When each test starts I use a setupDatabase method. This method deletes all the rows from the tables I know have changed then it loads a flat XML dataset containing the data I need to hold true. After this is done I run whatever code I'm testing. Finally, I use a collection of simple methods to select rows from the database to assert the changes I made were done correctly.
当我使用PHPUnit进行数据库测试时，我在第一个套件的开头加载MySQL转储，其中包含我假设在所有测试中为真的任何信息。每次测试开始时，我都使用setupDatabase方法。这个方法从我知道已经更改的表中删除所有的行，然后它加载一个包含我需要保持为true的数据的平面XML数据集。在这之后，我运行我正在测试的任何代码。最后，我使用一组简单的方法从数据库中选择行，以断言我所做的更改是正确的。
I wouldn't say this is a best practice but's worked pretty well for me. The only problems I've run into are having to do a find-and-replace on the XML datasets every time the schema changes and the tests run slowly as a result of all the deleting and inserting.
我不认为这是最好的练习，但对我来说效果很好。我遇到的惟一问题是每次模式更改时，必须对XML数据集进行查找和替换，并且由于所有删除和插入的结果，测试运行缓慢。
The Zend Framework has an interesting library for PHPUnit that allows tests to compare a database table to a flat XML dataset but I haven't had a chance to use it yet.
Zend框架有一个有趣的PHPUnit库，允许测试将数据库表与平面XML数据集进行比较，但我还没有机会使用它。
#2
3
Some unordered thoughts:
一些无序的想法:
It's good to have fixtures (with or without db structure), loaded into db in startUp() of every test. It can came ie. from JSON or XML files, one for each table. If you couple it with functions like getNthFixture($sTable, $nIndex) or countFixtures($sTable), you can easily test your queries. Even further, you can use [LINQ][1] to get expected results from set of fixtures with little or no difference between DB & fixtures query. I find it quite easy to adapt at early prototyping / developing stage, when db structure is changing very often. Adding assertion to compare directly LINQ query result with db query result makes creating tests pure pleasure ;)
在每一个测试的启动()中，有固定装置(有或没有db结构)，装载到db中是很好的。它可以是ie。从JSON或XML文件，每个表一个。如果您将它与getNthFixture($sTable， $nIndex)或countFixtures($sTable)等函数结合使用，您可以轻松地测试查询。甚至，您可以使用[LINQ][1]来获得预期的结果，这组固定装置很少或没有区别。我发现，当db结构经常发生变化时，在早期原型/开发阶段适应非常容易。添加断言来直接比较LINQ查询结果和db查询结果，使创建测试变得非常有趣;)
Another hints: db should be reinitialised before every testing method, not before test case. Ideally you should drop the base and rebuild it from complete set of fixtures.
另一个提示:应该在每个测试方法之前重新初始化db，而不是在测试用例之前。理想情况下，您应该放弃该基础并从完整的固定装置中重新构建它。
And, if you can, try make tests which works with different databases (some things, of course are not portable, but majority is). Use at least sqlite aside of mysql/postgres/other_big_rdbm.
如果可以的话，可以尝试使用不同的数据库进行测试(当然，有些东西是不可移植的，但大多数是可移植的)。除了mysql/postgres/other_big_rdbm之外，至少使用sqlite。
If you are testing framework or other complex system, you should probably mock up database access singleton. If some hardcoded stuff is buried deep in not-so-flexible-orm, it can be pain in the, let say, neck.
如果您正在测试框架或其他复杂的系统，您可能应该模拟数据库访问单例。如果一些硬编码的东西被深埋在不那么灵活的东西里，比如说，脖子就会感到疼痛。
Good idea is to log all queries which didn't passed test, and/or to display them in fail messages. Also for db error messages. If you are testing big databases when performance is a concern, try to log slow queries at the same time.
好主意是记录所有未通过测试的查询，并/或在失败消息中显示它们。也适用于db错误消息。如果您在测试大型数据库时考虑到性能，那么请尝试同时记录慢速查询。
More magical and maybe a little bit harder, is to automate testing whether all columns used in where / having / joins are indexed. Is maybe something which should belong to Jointed Php / Database Code Sniffer (tm) instead of unit tests, and is not very simple to implement, but once used greatly assures quality of code.
更神奇，也许更困难的是，自动化测试在where / have / join中使用的所有列是否都被编入索引。它可能应该属于联合Php /数据库代码嗅探器(tm)，而不是单元测试，而且实现起来不是很简单，但是一旦使用，就可以极大地保证代码的质量。
Another good advice, going from sad personal experience: always add tests for veryfing charsets, specially if you work with many different languages. ISO-8859-1 world is very small ;)
另一个很好的建议，来自于悲伤的个人经历:总是为所有的字符集添加测试，特别是当你使用许多不同的语言时。ISO-8859-1世界很小;
[1]: http://phplinq.codeplex.com/ LINQ
[1]:http://phplinq.codeplex.com/ LINQ
#1
9
When I'm doing database testing with PHPUnit I load my MySQL dump at the start of the first suite which contains any of the information I'm assuming to be true across all tests. When each test starts I use a setupDatabase method. This method deletes all the rows from the tables I know have changed then it loads a flat XML dataset containing the data I need to hold true. After this is done I run whatever code I'm testing. Finally, I use a collection of simple methods to select rows from the database to assert the changes I made were done correctly.
当我使用PHPUnit进行数据库测试时，我在第一个套件的开头加载MySQL转储，其中包含我假设在所有测试中为真的任何信息。每次测试开始时，我都使用setupDatabase方法。这个方法从我知道已经更改的表中删除所有的行，然后它加载一个包含我需要保持为true的数据的平面XML数据集。在这之后，我运行我正在测试的任何代码。最后，我使用一组简单的方法从数据库中选择行，以断言我所做的更改是正确的。
I wouldn't say this is a best practice but's worked pretty well for me. The only problems I've run into are having to do a find-and-replace on the XML datasets every time the schema changes and the tests run slowly as a result of all the deleting and inserting.
我不认为这是最好的练习，但对我来说效果很好。我遇到的惟一问题是每次模式更改时，必须对XML数据集进行查找和替换，并且由于所有删除和插入的结果，测试运行缓慢。
The Zend Framework has an interesting library for PHPUnit that allows tests to compare a database table to a flat XML dataset but I haven't had a chance to use it yet.
Zend框架有一个有趣的PHPUnit库，允许测试将数据库表与平面XML数据集进行比较，但我还没有机会使用它。
#2
3
Some unordered thoughts:
一些无序的想法:
It's good to have fixtures (with or without db structure), loaded into db in startUp() of every test. It can came ie. from JSON or XML files, one for each table. If you couple it with functions like getNthFixture($sTable, $nIndex) or countFixtures($sTable), you can easily test your queries. Even further, you can use [LINQ][1] to get expected results from set of fixtures with little or no difference between DB & fixtures query. I find it quite easy to adapt at early prototyping / developing stage, when db structure is changing very often. Adding assertion to compare directly LINQ query result with db query result makes creating tests pure pleasure ;)
在每一个测试的启动()中，有固定装置(有或没有db结构)，装载到db中是很好的。它可以是ie。从JSON或XML文件，每个表一个。如果您将它与getNthFixture($sTable， $nIndex)或countFixtures($sTable)等函数结合使用，您可以轻松地测试查询。甚至，您可以使用[LINQ][1]来获得预期的结果，这组固定装置很少或没有区别。我发现，当db结构经常发生变化时，在早期原型/开发阶段适应非常容易。添加断言来直接比较LINQ查询结果和db查询结果，使创建测试变得非常有趣;)
Another hints: db should be reinitialised before every testing method, not before test case. Ideally you should drop the base and rebuild it from complete set of fixtures.
另一个提示:应该在每个测试方法之前重新初始化db，而不是在测试用例之前。理想情况下，您应该放弃该基础并从完整的固定装置中重新构建它。
And, if you can, try make tests which works with different databases (some things, of course are not portable, but majority is). Use at least sqlite aside of mysql/postgres/other_big_rdbm.
如果可以的话，可以尝试使用不同的数据库进行测试(当然，有些东西是不可移植的，但大多数是可移植的)。除了mysql/postgres/other_big_rdbm之外，至少使用sqlite。
If you are testing framework or other complex system, you should probably mock up database access singleton. If some hardcoded stuff is buried deep in not-so-flexible-orm, it can be pain in the, let say, neck.
如果您正在测试框架或其他复杂的系统，您可能应该模拟数据库访问单例。如果一些硬编码的东西被深埋在不那么灵活的东西里，比如说，脖子就会感到疼痛。
Good idea is to log all queries which didn't passed test, and/or to display them in fail messages. Also for db error messages. If you are testing big databases when performance is a concern, try to log slow queries at the same time.
好主意是记录所有未通过测试的查询，并/或在失败消息中显示它们。也适用于db错误消息。如果您在测试大型数据库时考虑到性能，那么请尝试同时记录慢速查询。
More magical and maybe a little bit harder, is to automate testing whether all columns used in where / having / joins are indexed. Is maybe something which should belong to Jointed Php / Database Code Sniffer (tm) instead of unit tests, and is not very simple to implement, but once used greatly assures quality of code.
更神奇，也许更困难的是，自动化测试在where / have / join中使用的所有列是否都被编入索引。它可能应该属于联合Php /数据库代码嗅探器(tm)，而不是单元测试，而且实现起来不是很简单，但是一旦使用，就可以极大地保证代码的质量。
Another good advice, going from sad personal experience: always add tests for veryfing charsets, specially if you work with many different languages. ISO-8859-1 world is very small ;)
另一个很好的建议，来自于悲伤的个人经历:总是为所有的字符集添加测试，特别是当你使用许多不同的语言时。ISO-8859-1世界很小;
[1]: http://phplinq.codeplex.com/ LINQ
[1]:http://phplinq.codeplex.com/ LINQ