Archives
Visitors
  • 46193This month:
  • 1659Today:
  • 19Currently online:



LeaseWeb CDN

Posts Tagged ‘php’

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:

<?php

class MySQL_CRUD_API_Test extends PHPUnit_Framework_TestCase
{
	private function expectQueries($queries)
	{
		$mysqli = $this->getMockBuilder('mysqli')
			->setMethods(array('query','real_escape_string'))
			->getMock();
		$mysqli->expects($this->any())
			->method('real_escape_string')
			->will($this->returnCallback(function($str) { return addslashes($str); }));
		$mysqli->expects($this->any())
			->method('query')
			->will($this->returnCallback(function($query) use ($queries) {
				$this->assertTrue(isset($queries[$query]));
				$results = $queries[$query];
				$mysqli_result = $this->getMockBuilder('mysqli_result')
					->setMethods(array('fetch_row','close'))
					->disableOriginalConstructor()
					->getMock();
				$mysqli_result->expects($this->any())
					->method('fetch_row')
					->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
}
$result->close();

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.

Conclusion

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.

MindaPHP now has Memcache support

The PHP framework I am building (MindaPHP) already contains support for MySQL and cURL. Today I have added Memcache support. Memcache can be used for two main purposes in PHP: session storage and application caching. In the framework we only support debugging Memcache for application caching. This is how the debugger looks when the Cache class is used:

mindaphp_cache

The cache is used to store the results from the Bing query. You can try this on: http://maurits.server.nlware.com/hello/bing (click the debugger link in the bottom bar after searching).

Memcache for application caching

Now you can speed up your application using the following commands:

$var = Cache::get($key)
$success = Cache::set($key,$var,$expire=0)
$success = Cache::delete($key)
$success = Cache::add($key,$var,$expire=0)
$success = Cache::replace($key,$var,$expire=0)
$var = Cache::increment($key,$value=1)
$var = Cache::decrement($key,$value=1)

The commands “get” and “set” do retrieval and storage of values in the cache based on the “key” parameter. The commands “add” and “replace” are comparable to “set”, but either fail when the key does (in case of add) or does not (in case of replace) exist. The “increment” and “decrement” commands can be used for counters, but beware that “increment” fails when the key does not exist. This is why you may want to call “add” before you increment.

Memcache for session storage

If you want to use Memcache for session storage in PHP (with any framework), you configured this in “php.ini” with the following statements:

session.save_handler = memcache
session.save_path = "tcp://localhost:11211"

Note that you need the Memcache daemon and the php Memcache extension installed. The following command installs the required software on a Debian based Linux (like Ubuntu):

sudo apt-get install php5-memcache memcached

Have fun accelerating you application!

MySQL-CRUD-API now supports SQL Server 2012

Although the project was initially aimed at only providing support for MySQL, now MS SQL Server 2012 is also supported. MySQL-CRUD-API is a single PHP file that will provide a full REST API for your data structure. With the now added SQLSRV-CRUD-API class you can also connect to a SQL Server database. This only works if the SQLSRV driver is installed in PHP and this is only available for Windows.

sql_server

The SQL Server code relies on the “OFFSET” command, which was added to SQL Server in version 2012. It also allows for UTF-8 character encoding, IMHO a character set any modern database should use. The offset command was added by popular demand (to SQL Server), because the pagination in SQL Server was quite cumbersome, especially when compared to MySQL. Now they are on par again. Also the choice of the SQLSRV driver over the more compatible, but inferior, FreeTDS driver was intentional.

SELECT * FROM [posts] ORDER BY [published] DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

The response on this project has been mostly positive. There are people arguing that it is bad to expose the data structure of your database. My counter argument is that if you do proper database administration (data management) this is not true. And in that case you do not need all that boilerplate code that APIs generally consist of as it can all be automated. That is this project’s philosophy.

https://github.com/mevdschee/mysql-crud-api

Check out the code on Github and tell me what you think. Use the comments for feedback.

MindaPHP now has RESTful API support

When building applications today you need to follow cool new architectures like “Microservice design” and “API-first”. APIs play an increasingly important role in applications today. Together with database abstraction layers they bring the database technology further from the business logic than ever.

In line with the “Ease of learning” vision for MindaPHP, I decided to add a minimal RESTful API client in the form of a cURL wrapper. It has full integration with the debugger as you can see below:

mindaphp_api

The wrapper class hardly influences the performance of cURL when the debugger is disabled. When the debugger is enabled the performance and the memory usage may be affected, but this gives you a great deal of control as you can see in the image above. In the example below you see how you can use the cURL wrapper to call the Bing search engine and extract the first 10 links for a search query.

<?php
$query = isset($_POST['q'])?$_POST['q']:'';
$results = array();

if ($query) {
    if (Curl::call('GET','http://www.bing.com/search',array('q'=>$query),$result)==200) {
        
        $dom = new DOMDocument();
        @$dom->loadHTML($result);
        
        $xpath = new DOMXpath($dom);
        $elements = $xpath->query('//ol["b_results"]/li[@class="b_algo"]//h2/a');

        foreach ($elements as $element) {
            $text = $element->nodeValue;
            $link = $element->getAttribute("href");
            $results[] = compact('text','link');
        }
    }
}

For more information check out Github or the demo on one of the links below:

 

Code: https://github.com/mevdschee/MindaPHP
Demo: http://maurits.server.nlware.com/hello/bing

MySQL-CRUD-API now has transforms!

Last week I created a new GitHub project called “MySQL-CRUD-API” and it allows you to quickly setup a simple REST API with CRUD functionality by just adding a single “api.php” file to your project and configuring it’s database connection. Today I show how the relational support of this project works.

Supported table relations

There are three types of table relations supported:

  • BelongsTo
  • HasMany
  • HasAndBelongsToMany

Blog example

When you use the “list” command of the API it allows you to specify multiple tables. If these tables have relations (foreign keys) the output will be filtered in such a way that only relevant records are returned. This is what the API outputs when you list posts and comments filtered on a specific post:

{
    "posts": {
        "columns": [
            "id",
            "user_id",
            "category_id",
            "content"
        ],
        "records": [
            [
                "1",
                "1",
                "1",
                "blog started"
            ]
        ]
    },
    "comments": {
        "relations": {
            "post_id": "posts.id"
        },
        "columns": [
            "id",
            "post_id",
            "message"
        ],
        "records": [
            [
                "1",
                "1",
                "great"
            ],
            [
                "2",
                "1",
                "fantastic"
            ]
        ]
    }
}

Not so useful right? You would probably like to see something like this:

{
    "posts": [
        {
            "id": "1",
            "comments": [
                {
                    "id": "1",
                    "post_id": "1",
                    "message": "great"
                },
                {
                    "id": "2",
                    "post_id": "1",
                    "message": "fantastic"
                }
            ],
            "user_id": "1",
            "category_id": "1",
            "content": "blog started"
        }
    ]
}

That is exactly what the function “mysql_crud_api_transform()” does. You run this function on the client after receiving the API response. This is beneficial as it uses the CPU and RAM of the API consumer instead of that of the API server. This transformation function is implemented in PHP and JavaScript, so that you can make spiders and users with browsers equally happy!

<?php
function mysql_crud_api_transform(&$tables) {
	$getobjs = function(&$tables,$table_name,$where_index=false,$match_value=false) use (&$getobjs) {
		$objects = array();
		foreach($tables[$table_name]['records'] as $record) {
			if ($where_index===false || $record[$where_index]==$match_value) {
				$object = array();
				foreach ($tables[$table_name]['columns'] as $index=>$column) {
					$object[$column] = $record[$index];
					foreach ($tables as $relation=>$reltable) {
						foreach ($reltable['relations'] as $key=>$target) {
							if ($target == "$table_name.$column") {
								$columnidx = array_flip($reltable['columns']);
								$object[$relation] = $getobjs($tables,$relation,$columnidx[$key],$record[$index]);
							}
						}
					}
				}
				$objects[] = $object;
			}
		}
		return $objects;
	};
	$tree = array();
	foreach ($tables as $name=>$table) {
		if (!isset($table['relations'])) {
			$tree[$name] = $getobjs($tables,$name);
		}
	}
	return $tree;
}

And the JavaScript version:

function mysql_crud_api_transform(tables) {
	var array_flip = function (trans) {
		var key, tmp_ar = {};
		for (key in trans) {
			tmp_ar[trans[key]] = key;
		}
		return tmp_ar;
	};
	var get_objects = function (tables,table_name,where_index,match_value) {
		var objects = [];
		for (var record in tables[table_name]['records']) {
			record = tables[table_name]['records'][record];
			if (!where_index || record[where_index]==match_value) {
				var object = {};
				for (var index in tables[table_name]['columns']) {
					var column = tables[table_name]['columns'][index];
					object[column] = record[index];
					for (var relation in tables) {
						var reltable = tables[relation];
						for (var key in reltable['relations']) {
							var target = reltable['relations'][key];
							if (target == table_name+'.'+column) {
								column_indices = array_flip(reltable['columns']);
								object[relation] = get_objects(tables,relation,column_indices[key],record[index]);
							}
						}
					}
				}
				objects.push(object);
			}
		}
		return objects;
	};
	tree = {};
	for (var name in tables) {
		var table = tables[name];
		if (!table['relations']) {
			tree[name] = get_objects(tables,name);
		}
	}
	return tree;
}

Check out all the source code on my GitHub account: https://github.com/mevdschee/mysql-crud-api