MySQL上的数据库事务内的代码接收验收测试 [英] Codeception acceptance tests within database transaction on MySQL

问题描述

使用Codeception为我们的Custom(带有Symfony组件和Doctrine)框架编写验收测试.我们有一套实用方法，供Phpunit测试分别使用，用于创建要测试的各种实体.这包括诸如用户之类的东西，以及其他相关数据.

Writing acceptance tests using Codeception for our Custom (with Symfony components and Doctrine) framework. We have a set of utility methods, used separately by our Phpunit tests, for creating various entities to test against. This includes things such as users, and other related data.

对于我们的Codeception测试，我们想利用此功能，使我们可以播种自定义数据并在之后进行清理.在我们的单元测试中，这是由事务处理的.鉴于Codeception通过Phantomjs通过HTTP向我们的应用程序发出请求，因此无法使用事务，因为已为测试所用的数据库创建了一个单独的数据库连接(看起来可以使用Dbh来实现，但这不受支持)由MySQL提供，因为不支持嵌套事务.这样的结果是验收测试可以播种数据，但是除非持久化数据，否则外部请求中不存在该数据.

In the case of our Codeception tests we want to make use of this functionality, allowing us to seed custom data and clean it up afterwards. In the context of our Unit tests this is handled by a transaction. Given that Codeception makes requests to our application through Phantomjs over HTTP it can't make use of transactions as a separate database connection is created to the one used by the tests (It looks like this would be possible using Dbh but this isn't supported by MySQL as there's no support for nested transactions). The result of this is that the acceptance test can seed data, but this data doesn't exist within the external request unless the data is persisted.

我能想到的唯一其他解决方案是在测试套件运行之前触发数据库转储.将我们的测试数据保存到数据库中，以便外部请求可以访问它，然后在测试套件完成后还原转储的数据库.不过，我们有一个相当大的数据库，这将增加运行测试套件的大量开销.

The only other solution I can think is to trigger a database dump before the test suite runs. Persist our testing data to the database so it's accessible to the external requests, and then restore the dumped database when the test suite has completed. We have a pretty big database though and this is going to add substantial overhead to running the test suite.

我希望还有其他选择，或者我们可以尝试其他方法. 谢谢.

I'm hoping there's other options available, or something else we can try. Thanks.

推荐答案

总有其他选项:).

在我的情况下，使用转储是不可能的，因为我们还有一个非常大的数据库(精简成基本版后为1.2 GB，实时上大于250GB). 相反，我有一堆.sql文件，它们删除了特定测试插入的所有记录.

In my case, using a dump was out of the question because we also have a very large database (1.2 GB when stripped down to the essentials, >250GB on live). Instead, I have a bunch of .sql files that remove any records that were inserted by a specific test.

我有一个DbHelper类，该类具有运行查询的功能:

I have a class DbHelper that has this function to run queries:

public function executeOnDatabase($path_to_sql_file)
{
    $sql = file_get_contents($path_to_sql_file);
    $dbh = $this->getModule('Db')->dbh;
    $this->debugSection('Query', $sql);
    $sth = $dbh->prepare($sql);

    return $sth->execute();
}

以魅力形式工作，而没有更多的开销来运行测试.

Works as a charm, and no more overhead running the tests.

更新:

如果要处理用户及其相关数据，通常很容易将需要删除的所有记录作为目标.用户名和电子邮件很可能是唯一的，您可以使用它们来获取用户的ID(自动递增主键).该ID可能在相关表中使用. 例如这样的

If you deal with users and their related data, it is usually pretty easy to target all records you need to remove. Most likely the username and email are unique, and you can use those to get the id (auto increment primary key) of the user. That id is likely used in the related tables. For example like this:

SET @user_id = (SELECT `id` FROM `users` WHERE `username` = 'rgsfdg');
SET @email = '[e-mail]';
DELETE FROM `mail_queue` WHERE `send_to` = @email;
DELETE FROM `user_settings` WHERE `user_id` = @user_id;
DELETE FROM `users` WHERE `id` = @user_id;

这篇关于MySQL上的数据库事务内的代码接收验收测试的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！