Thoughts and learnings in my journey through all things technology. Developer operations, software development, server administration and anything else that I decide should be written.

Tags


Unit testing your Database using Custom Functions in SQLite

2nd September 2016

Overview

One of the largest challenges in unit testing is often making the differentiation in functional unit testing database queries or mocking them. I generally opt for a more functional test leveraging PHPUnit's database test case fixtures. We often utilize a database abstraction layer and in this particular case are using MySQL. However, when using SQLite we lose support for several functions which are leveraged.

In some simple searching the SQLite driver supported creating custom functions and PHP does not leave you without option as the function was implemented. This means there is a path forward for us to re-implement some of the functions we are leveraging in MySQL and convert them so we can leverage them in SQLite.

Finding the Golden Egg

Once you start looking, you will find that SQLite contains methods to create or recreate functions. However, this is in the C library, so we need to ensure that the driver has support for this function. We then head to the PHP manual and find: sqlite_create_function and PDO::sqliteCreateFunction.

Great! Since our abstraction library is leveraging PDO we're can be off to the races.

Implementing compatibility

In MySQL we are leveraging the functions: concat, if, mid and substring amongst others.

In all of the examples below we will be starting off with a PDO connection: $pdo = new PDO('sqlite::memory:');

CONCAT

ANSI SQL supports concatenation through a double pipe. MySQL does support this but you would need to set the SQL_MODE variable to handle it. Since our software supports both our own cloud and on-premise installations this was a no go (and we really don't want to rewrite all of the queries that currently have CONCAT. So let's implement this:

$pdo->sqliteCreateFunction('concat', function() {
    return implode(', ', func_get_args());
});

Let's give this a quick go.

$rows = $pdo->query("SELECT CONCAT('foo', 'bar') AS baz")->fetchAll(PDO::FETCH_ASSOC);

array(1) {  
  [0]=>
  array(1) {
    ["baz"]=>
    string(8) "foo, bar"
  }
}

IF

Well, in ANSI SQL we can always do a CASE statement but that is a lot more writing especially when we can just pass our truthy condition and the true value and false value. This is a quick small one that works really well:

$pdo->sqliteCreateFunction('if', function($if, $true, $false) {
    return ($if) ? $true : $false;
});

array(1) {  
  [0]=>
  array(1) {
    ["baz"]=>
    string(3) "foo"
  }
}

SUBSTRING & MID

Did you know you can also use built-in functions when the parameters match? You can!

$rows = $pdo->query("SELECT MID('MySQLSQLite', 5) as database")->fetchAll(PDO::FETCH_ASSOC);

array(1) {  
  [0]=>
  array(1) {
    ["database"]=>
    string(6) "SQLite"
  }
}

Conclusion

These are just a few quick functions that you can build out. Getting further with compatibility is certainly possible. There is likely going to be more difficult functions to re-implement but you can likely get more than 99% of the way there by just doing some quick and simple handling whilst ensuring you can run unit tests quickly on any machine with SQLite (which is the majority especially if you're reading this)!

I am a VP of engineering for a small start up. I have over a decade of experience in engineering, database administration, server administration and management.

View Comments