How to mock MySQLi when unit testing with PHPUnit

PHPUnit is the most used unit testing framework for PHP. Today I wanted to unit test some PHP code that relies on MySQLi. The dilemma is that you either need an actual database and load a fixture or you need to mock the database. As Claudio Lasalla clearly puts:

Unit tests are not “unit” tests if they test things other than the System Under Test (SUT).

And further explains:

Unit tests check on the behavior of units. Think of a class as being a unit. Classes, more often than not, have external dependencies. Tests for such classes should not use their real dependencies because if the dependencies have defects, the tests fail, even though the code inside the class may be perfectly fine.

This theory made total sense to me. That’s why I decided to mock the MySQLi dependency. In this post I will show you just how far I came before I realized this was not going to work out (for me).

The code

The test class, that extends “PHPUnit Framework TestCase”, has an extra method “expectQueries()”. The class looks like this:


class MySQL_CRUD_API_Test extends PHPUnit_Framework_TestCase
	private function expectQueries($queries)
		$mysqli = $this->getMockBuilder('mysqli')
			->will($this->returnCallback(function($str) { return addslashes($str); }));
			->will($this->returnCallback(function($query) use ($queries) {
				$results = $queries[$query];
				$mysqli_result = $this->getMockBuilder('mysqli_result')
					->will($this->returnCallback(function() use ($results) {
						static $r = 0;
						return isset($results[$r])?$results[$r++]:false;
				return $mysqli_result;

		return $mysqli;

	public function testSomeSubjectThatUsesMysqli()
		$mysqli = $this->expectQueries(array(
			"SELECT * FROM `table`" =>array(array('1','value1'),array('2','value2'),array('3','value3')),
			"SELECT * FROM `table` LIMIT 2" =>array(array('1','value1'),array('2','value2')),
			// other queries that may be called
		// do something that uses $mysqli

The subject-under-test is actually doing something like this:

$result = $mysqli->query("SELECT * FROM `table`");
while ($row = $result->fetch_row()) {
	// do something with the data in $row

And in the test it will return the corresponding rows for the queries that you execute. Nice huh?

Not ready

This is a proof-of-concept of a mock of the MySQLi component for PHPUnit. The ‘real_escape_string’ function has a sloppy implementation. It does not (yet) support the much used ‘prepare’, ‘execute’ or ‘fetch_fields’ methods. To give an idea of the completeness, for MySQLi it now support 2/62 functions and properties, for MySQLi Statement 0/28 and for MySQLi Result 2/15. Apart from this incompleteness there is the problem that you may need to support meta information, such as field names and types, to have a fully working mock. If you feel like continuing my work, then feel free to take my code.


Although this was a nice exercise and it may even be the right thing to do in theory, it did not seem to make much sense (to me) in practice. So I gave up on this approach and my current implementation runs all tests against a real database. It loads a database from a SQL file (fixture) in the static ‘setUpBeforeClass()’ function. This may not be so ‘correct’ or ‘clean’ (from a unit testing point of view), but it is much faster to write and easier to maintain.

My question for you: Am I wrong or is the theory wrong? Please tell me using the comments.


Refactoring, upgrading and other software maintenance

In 1976, Lientz and Swanson categorized software maintenance activities into four classes (from Wikipedia):

  • Corrective: diagnosing and fixing errors, possibly ones found by users
  • Perfective: implementing new or changed user requirements which concern functional enhancements to the software
  • Adaptive: modifying the system to cope with changes in the software environment (DBMS, OS)
  • Preventive: increasing software maintainability or reliability to prevent problems in the future

“Corrective” and “perfective” maintenance are very visible and bring value to the customer. The need for “adaptive” and “preventive” maintenance on the other hand is harder to defend.

Customer value or waste?

The customer will demand any “corrective” change to be executed by the software developers with high priority, especially when it impacts his business. Any customer will also have a (wish) list of “perfective” enhancements to be executed and the client is willing to pay and prioritize this maintenance.

“Adaptive” maintenance on the other hand brings no (direct) value to the customer. It will be hard to get the customer to pay or even prioritize an operating system upgrade. Customers do like to argue “it works, do not touch it”. But the Internet has changed our world. We live in an all-connected world where security fixes need to be applied monthly. At some point the vendor stops providing (security) updates. At this point the product is called end-of-life (EOL). Not upgrading is considered a risk nowadays. This is why software developers can often argue that upgrading needs to get priority and the costs are justified. Examples of “adaptive” maintenance:

  1. Dependency upgrades
  2. Framework upgrades
  3. Operating system upgrades
  4. Database upgrades

“Preventive” maintenance is even harder to defend. Like “adaptive” it does not bring (direct) value to the customer. In the Agile methodology everything not adding value to the customer is called “waste”. Examples of “preventive” maintenance:

  1. Small code refactoring
  2. Big code refactoring
  3. Database refactoring

What is refactoring?

Wikipedia has a rather long definition that I tried to compact to:

Refactoring: technique for restructuring, altering internal structure without changing external behavior, undertaken to improve maintainability and extensibility

It may be trivial to conclude that refactoring is waste. Refactoring does not change behavior (functionality) and does therefor not bring value to the customer. Interestingly it is not mentioned as a waste in The Seven Wastes of Software Development by Matt Stine nor in the How to Manage the “7 Wastes” of Agile Software Development by Vijaya Kumar Bandaru. These posts are both referring to the 2006 book Implementing Lean Software Development: From Concept to Cash by Mary and Tom Poppendieck. Vijaya only says that “not refactoring” can lead to “partially done work”. From this remark we can conclude that not properly refactored code is not “done”. This is confirmed by Mayank Gupta who writes that “A common mistake is to not keep refactoring in the definition of done.”

Refactoring needs unit tests

Changing code without a functional reason, seems counter-productive at first. You spend money and take a risk to break the software, without promising any business benefits. This is why in software development the change management “change committee” should include software architects and security engineers, not only business people. The truth is that refactoring leads to lower costs for fixing bugs and extending with new features. This is mainly, because refactoring leads to software developers “touching” (and thinking about) the code. To counter the negative effects of refactoring (breaking software) one can introduce (and require) unit tests. These are programmed (automated) tests that ensure that after refactoring the code still works as before.

Selling refactoring to the business

Refactoring code that is touched by user stories in the sprint is easily done, since people are working on that code anyway. Making refactoring part of your definition-of-done encourages refactoring every time a piece of code is touched. To ensure high-quality, make sure the touched code also checks all the other marks on your definition-of-done. Some of our important definition-of-done topics:

  1. Unit tests written/ran
  2. Refactoring done
  3. Documentation updated
  4. Comitted to Git
  5. Functional tests written/ran
  6. Accepted by customer

Refactoring code unrelated to user stories in the sprint needs to be sold to the customer. Otherwise the customer will not understand why certain bugs suddenly arise. If you do not succeed to do so you must have patience. Every time you touch a part of the code for a user story, take the chance to refactor the related code or database. This way after a year or two you are able to renew your code base and data structure one feature or table at a time. Just make sure every user story is a little more expansive, so that you can squeeze in the preventive maintenance needed.


Jenkins & The Continuous Integration Game

Jeff Atwood writes about The Gamification and he states that there is “… a long, rich history of programmers as gamers“. He writes that programmers want “…to move beyond being a mere player and change the game, control it, modify its parameters, maybe even create our own games.”

Sure, but even when this is not true I believe that most people enjoy to compete in a game like fashion. We see this happen at the supermarket where we can tag reduction stickers on products and where we can collect trophies when we shop enough (often aimed at kids, but enjoyed by a broad public).

Recently I ran into “ci-game” or as it is fully called “The Continuous Integration Game”. It is a plugin for Jenkins that allows comitters to compete and collect points by improving the code in the repository. It requires that you have set up Jenkins to autmatically build you project(s). When a user commits code, the change is evaluated by the plugin. The plugin calculates an amount of points, either positive or negative and that is added to the comitters current score.

Since we use a lot of PHP and the game was designed for Java it was a bit of a challenge to get it working. We installed jenkins from the Ubuntu 12.04 repository. After installing Jenkins we installed the required plugins. After that we set up the Template for Jenkins Jobs for PHP Projects. Then we upgraded Jenkins from within Jenkins itself. After that we installed the ci-game plugin. Everything works and we are enjoying some healthy competition in the office.


The rules of the game are:

  • -10 points for breaking a build
  • 0 points for breaking a build that already was broken
  • +1 points for doing a build with no failures (unstable builds gives no points)
  • -1 points for each new test failures
  • +1 points for each new test that passes

Rules that depend on other plugins (and are therefor optional):

  • PMD Plugin. Adding/removing a HIGH priority warning = -5/+5. Adding/removing a MEDIUM priority warning = -3/+3. Adding/removing a LOW priority warning = -1/+1.
  • Task Scanner Plugin. Adding/removing a HIGH priority task = -5/+5. Adding/removing a MEDIUM priority task = -3/+3. Adding/removing a LOW priority task = -1/+1
  • Violations Plugin. Adding/removing a violation = -1/+1. Adding/removing a duplication violation = -5/+5.
  • FindBugs Plugin. Adding/removing a HIGH priority findbugs warning = -5/+5. Adding/removing a MEDIUM priority findbugs warning = -3/+3. Adding/removing a LOW priority findbugs warning = -1/+1
  • Warnings Plugin. Adding/removing a compiler warning = -1/+1.
  • Checkstyle Plugin. Adding/removing a checkstyle warning = -1/+1.

Continuous Integration for Symfony2 using Jenkins

Today I will show how to set up Continuous Integration (CI) for Symfony2 using Jenkins and the PHP-template for Jenkins jobs by Sebastian Bergmann.

This configuration works successfully with Symfony ver. 2.1 and Jenkins ver. 1.480.1.

Installing requirements

Before creating template jobs you need to install the Required Jenkins Plugins and Required PHP Tools.

Using the Symfony2 PHP Jenkins job template

I based the first steps on, but I will use config.xml, build.xml, phpunit.xml and additional files from this website:

  1. Fetch the jenkins-cli.jar from your Jenkins server
    wget http://localhost:8080/jnlpJars/jenkins-cli.jar
  2. Download and install the job template
    curl | \
    java -jar jenkins-cli.jar -s http://localhost:8080/jenkins create-job symfony2-php-template

    or add the template manually:

    cd $JENKINS_HOME/jobs
    mkdir symfony2-php-template
    cd symfony2-php-template
    cd ..
    chown -R jenkins:jenkins symfony2-php-template/
  3. Reload Jenkins’ configuration, for instance using the Jenkins CLI:
    java -jar jenkins-cli.jar -s http://localhost:8080 reload-configuration
  4. Click on “New Job”
  5. Enter the “Job name”
  6. Select “Copy existing job” and enter “symfony2-php-template” into the “Copy from” field.
  7. Click “OK”
  8. Configure your new job with version control and whatever other fields you need.

Project configuring and issues fixing

  1. Make changes from the basic Jenkins-PHP config to Symfony2-Jenkins-PHP as described here:
    • Move the Jenkins folder to [SYMFONY2_ROOT]/app/Resources/ inside your Symfony2 project
    • Move build.xml to the root folder of your Symfony2 application
    • Move phpunit.xml to [SYMFONY2_ROOT]/app folder or update the existing one. The logging node is needed!
  2. If you get the following message: PHP Fatal error:  Class ‘XSLTProcessor’ not found in /usr/share/php/TheSeer/fXSL/fxsltprocessor.php on line 58, you can fix  it with 
    sudo apt-get install php5-xsl
  3. You might also get this error message, because we run Symfony 2.1, which needs a composer: PHP Warning:  require(/var/lib/jenkins/jobs/TestJob/workspace/app/../vendor/autoload.php): failed to open stream: No such file or directory in /var/lib/jenkins/jobs/TestJob/workspace/app/autoload.php on line 5 . You can fix this problem like this:
  4. Sometimes you encounter a problem on the “vendors” step, saying something like this:

    [exec] The deps file is not valid ini syntax. Perhaps missing a trailing newline?
    [exec] PHP Warning:  parse_ini_file(/var/lib/jenkins/jobs/TestJob/workspace/deps): failed to open stream: No such file or directory in  /var/lib/jenkins/jobs/TestJob/workspace/bin/vendors on line 69

    You can fix this by removing “vendors” from build dependency and the “vendors” target in build.xml

  5. If you get a Status: 2 during the codesniffer step (phpcs) you need to install Symfony2 coding standard
  6. Enable checkbox “Poll SCM” and write: */5 * * * * (This step makes Jenkins check every 5 minutes if there are changes in the repository. If any changes are found, it will automatically build a project)

That’s it! You are now ready to build your PHP Symfony2 project with Jenkins! If you find bugs (or fixes), links, or have other suggestions, do not hesitate to comment below.

Enjoy the result!