DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
The Latest "Software Integration: The Intersection of APIs, Microservices, and Cloud-Based Systems" Trend Report
Get the report
  1. DZone
  2. Data Engineering
  3. Databases
  4. Database Testing with PHPUnit and MySQL

Database Testing with PHPUnit and MySQL

Matthew Turland user avatar by
Matthew Turland
·
Oct. 21, 11 · Interview
Like (0)
Save
Tweet
Share
11.47K Views

Join the DZone community and get the full member experience.

Join For Free

I recently made a contribution to the PHPUnit project that I thought I’d take a blog post to discuss. One of the extensions bundled with PHPUnit adds support for database testing. This extension was contributed by Mike Lively and is a port of the DbUnit extension for the JUnit Java unit testing framework. If you’re interested in learning more about database unit testing, check out this presentation by Sebastian Bergmann on the subject.

One of the major components of both extensions is the data set. Database unit tests involve loading a seed data set into a database, executing code that performs an operation on that data set such as deleting a record, and then checking the state of the data set to confirm that the operation had the desired effect. DbUnit supports multiple formats for seed data sets. The PHPUnit Database extension includes support for DbUnit’s XML and flat XML formats plus CSV format as well.

If you’re using MySQL as your database, CSV has been the only format supported by both the mysqldump utility and the PHPUnit Database extension up to this point. My contribution adds support for its XML format to the extension. While this support was developed to work in the PHPUnit 3.4.x branch, it won’t be available in a stable release until 3.5.0. In the meantime, this is how you can use it now.

  1. Go to the commit on Github and apply the additions and modifications included in it to your PHPUnit installation.
  2. From a shell, get your XML seed data set and store it in a location accessible to your unit test cases.
     
    mysqldump --xml -t -u username -p database > seed.xml
  3. Create a test case class that extends PHPUnit_Extensions_Database_TestCase. Implement getConnection() and getDataSet() as per the documentation where the latter will include a method call to create the data set from the XML file as shown below.
     
    $dataSet = $this->createMySQLXMLDataSet('/path/to/seed.xml');
  4. At this point, you can execute operations on the database to get it to its expected state following a test, produce an XML dump of the database in that state, and then compare that dump to the actual database contents in a test method to confirm that the two are equal.

    $expected = $this->createMySQLXMLDataSet('/path/to/expected.xml');
    $actual = new PHPUnit_Extension_Database_DataSet_QueryDataSet($this->getConnection());
    // Specify a SELECT query as the 2nd parameter here to limit the data set, else the entire table is used
    $actual->addTable('tablename');
    $this->assertDataSetsEqual($expected, $actual);

    That’s it! Hopefully this proves useful to someone else.


    Source: http://matthewturland.com/2010/01/04/database-testing-with-phpunit-and-mysql/
Database PHPUnit unit test Data set MySQL

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Demystifying the Infrastructure as Code Landscape
  • Steel Threads Are a Technique That Will Make You a Better Engineer
  • Choosing the Right Framework for Your Project
  • Integrate AWS Secrets Manager in Spring Boot Application

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: