Temporary Tables for Unit Testing

One of the most aggravating aspects of unit tests is the requirement to set up mock data sources -- and by extension, the orm to switch between a true data source and some kind of data-enriched muffin to ensure predictable data I/O for unit tests.

I am finding that temporary tables serve as a great utility for a database-like muffin.

• Temporary tables exist only for the lifetime of the connection.
• If you create a temporary table with the same name name as a real table (but the designation 'Temporary', all SQL I/O will get intercepted by the temporary table. This is the single most perfect reason why this approach works as well as it does. A temp table acts intercepts all traffic to a real table of the same name for its lifetime, leaving the original table preserved from the affects of the tests. (If you are on significant servers, you might feel better relying on a different database for use in unit testing, of course, to bulletproof your systems against inadvertent changes due to unit tests.)
• You can even join temporary tables with non-temporary tables, so if you have tables that are static/read only(list of states, correspondence with your mom) you can leave them intact.

Like all things Unit Testing related, the system is best implemented early and often but if you have good control over your data I/O temp table you should be able to overlay it at any point in development.

Considerations:

• You will get in trouble of course is with other database-like muffins like Memcache-d et.all, as data cached there proabbly won't be from your temp tables unless you blow away your caches as part of the tests.
• Similarly file related processes need to be coordinated carefully. (You'd need to designate a special test directory unique for EACH TEST and recreate your filesystem inside it to avoid cross-test contamination and damage to actual site.)
• Temp tables work fine in mySql - I'm pretty specialized in mySql and can't speak to its utility in other DB types.
• Of course, persistent connections could really screw not only your unit tests but your mainline application, if you use them in conjunction with temporary tables. Be sure to kill off any connection coupled to temporary tables when you are done with it.
• As with all weird new things, this is best tried outside of a production environment.
• This system depends on your ability to ensure that all your activity goes through a single connection. If your system spawns connections willy nilly, then you can't depend on your temp tables (which are bound to a single connection) to protect your underlying database.
• If you spawn a new set of temporary tables with every connection, then the data will be balkanized to each connection. I.e., if process 1 spawns temp tables and adds 3 users, then process 2 (in the same request) creates a new conn that spawns temp tables and adds 2 more users, then the two connections won't be able to see each others' records. This is actually a good thing as it allows you to reset your test environment by destroying and reloading your connection.
• MySql has documented the limitations of Temporary Tables, most notably, their failure to self join. You can of course duplicate an entire temporary table as a second temporary table and join to that, if its not too "looking glass" for you.

That caveat aside, I've created a really simple switcher that loads my entire schema in the beginning of Unit Tests (with the temporary designation) that shifts the app into test mode and loads the temporary tables over the real ones. For instance, since I have a table called "keywords", my temporary table includes the following:

	CREATE TEMPORARY TABLE `keywords` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`kw_date` datetime NOT NULL,
`msg_id` varchar(255) NOT NULL,
`keyword` varchar(255) NOT NULL,
`kw_count` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `kw_date` (`kw_date`,`msg_id`,`keyword`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

The schema is identical to the persistent table of the same name -- the only real difference is the single word "Temporary" in he first line of the definition.

Example Implementation in Zend Framework

Anyone seeing this site can tell I have a hard-on for Zend Framework. Its extreme structure and control lend itself to this process. However there is no reason a similar system to the one described below can't be implemented in (sigh) custom code or another environment. In Drupal, for instance. all things end up running through db_query or other fairly compact database bottlenecks so you could dump your database, change references to temp tables and reload it.

The requirements of a Temp Table based system are:

1. You must use a single connection for all transactions
2. You must be able to alter the connection, loading temp tables over the real tables during the configuration stage
3. You must destroy and reintiialize the connection after each and every test.

Fortunately the Zend_Config scenario and phpUnit work well with this requirement system. I am not in fact using the full MVC implementation of ZF for these tests as I am developing a backend system without a lot of need for a rigorous control system (making it I guess a M-V implementation of ZF), so this is further proof that this approach works for a wide variety of conditions.

For my system I created the following:

A centralized Config class using Zend_Config_Ini to load DB connections. the configuration file is as follows:

	[base]

email.db.username = myuser
email.db.password = mypass
email.db.dbname   = mydb

files.message_storage   = "/users/...."
...

[test:base]

db.dbname = mailproc_test

The configuration class itself records testing state as a static variable. Note that if you switch states during a request the sytem errors out.

		<?php

class DCSBootstrap_Config
extends Zend_Config_Ini {

    public function  __construct($section = 'test') {

        parent::__construct(dirname(__FILE__) . DIRECTORY_SEPARATOR .'config.ini', $section);

    }

    private static $_config_instances = array();
    /**
     * Note -- unlike a lot of the instance methods in this project
     * this function stores one of many adapters based on the mode required.
     * This keeps test and real requests discrete.
     * This in turn requires a bit of responsibility in the calling contexts...
     *
     * @param string $section
     * @return Zend_Db_Adapter_Mysqli
     */

    public static function instance($section = self::MODE_BASE) {
        if (!array_key_exists($section, self::$_config_instances)) {
            self::$_config_instances[$section] = new self($section);
        }

        return self::$_config_instances[$section];
    }
    
    const MODE_BASE = 'base';
    const MODE_TEST = 'test';

    /**
     *
     * @var string
     */
    private static $_current_section = NULL;
    /**
     * This set/get static function tracks the current test state.
     * @param sting $pNewSection
     * @return string
     */
    public static function current_section($pNewSection = NULL) {
        if ($pNewSection) {
            self::$_current_section = $pNewSection;
        }

        return self::$_current_section;
    }

    /**
     * Here is where the magic happens during unit testing.
     * All the temporary data is loaded oto the current database
     * from a dump of the SQL structure. In my case the database is
     * loaded without any records but there's no reason why you can't
     * load record data as well in SQL.
     */
    public static function init_test_mode() {
        self::current_section(self::MODE_TEST);
        $adapter = WLL__Adapter::instance();
        $filename = dirname(dirname(__FILE__)) . '/Email/mailproc_test.sql';
        $queries = file_get_contents($filename);
        foreach(array_map('e_trim', split(';', $queries)) as $query) {
            if ($query) {
                if (preg_match('~CREATE TEMPORARY TABLE (.*)~', $query, $m)){
                    error_log(__METHOD__ . ': creating temporary table for ' . $m[1]);
                }
                $adapter->query($query);
            }
        }
    }
}

This class is a factory for itself; if you pull data from the instance() static method you only have to load config data once for each section. This does go against UT theory but config data is always read only.

Secondly, the database adapters for all my tables also come out of a factory:

	
<?php

/**
 * This class is a specialized factory. It cannot be instantiated.
 */
class WLL__Adapter{

    private function __construct() {

    }

    private static $_instances = array();
/**
 * Note -- unlike a lot of the instance methods in this project
 * this function stores one of many adapters based on the mode required.
 * This keeps test and real requests discrete.
 * This in turn requires a bit of responsibility in the calling contexts...
 *
 * At this point we are locking the section to a singe mode -- any attemt to
 * switch modes after a single adapter has been given out will
 * trigger an exception.
 */

    private static $_applied_mode = NULL;
/**
 * @return Zend_Db_Adapter_Mysqli
 */
    public static function instance(){
        $section = DCSBootstrap_Config::current_section();

        if ((! is_null(self::$_applied_mode)) && (self::$_applied_mode != $section)){
            throw new Exception(__METHOD__ . ': mid game switch of mode to ' . $section . ' from ' .self::$_applied_mode);
        }

        if (!array_key_exists($section, self::$_instances)){
            $config = DCSBootstrap_Config::instance($section);
            self::$_instances[$section] = new Zend_Db_Adapter_Mysqli($config->email->db);
            self::$_applied_mode = $section;
        }

        return self::$_instances[$section];
    }

    public static function reset_instance(){
        foreach(self::$_instances as $key => $adapter){
            error_log('closing connection for ' . $key);
            $adapter->getConnection()->close();
            unset(self::$_instances[$key]);
        }
    }
}

You will notice that the only way to get objects from this class is through the factory due to the private constructor. Also the class is tied to the configuration factory above. Lastly, though this may not be absolutely necessary, I "Lock" you to one database per request. The red line above declares that once you choose a mode, if you choose another mode, an exception is thrown. That way if low level code tries to switch databases, the system crashes to a halt before damage can be done to your database. (extensive damage anyway... :D)

This class also includes a "reset" function that destroys your connections so at the end of the tests the temporary tables are erased. This can happen multiple times during a round of execution. You may want to load a subset of your database into temporary tables (or at least a subset of your records) as this will happen repeatedly.

The last step in this process (besides writing actual tests of course) is a base class that manages loading and unloding the database. (you actually DON'T want this done in a test suite becuase you want to scrub your database for each and every test.) here's mine:

	<?php

/*
 * Tests the execution of database test mode.
 *
 * @author bingomanatee
*/
abstract class DCS_Unit_Db_Abstract extends PHPUnit_Framework_TestCase {

    protected function setUp() {

        switch(DCSBootstrap_Config::current_section()) {
            case DCSBootstrap_Config::MODE_BASE:
                throw new Exception(__METHOD__ . ': Base Data Connection already made!');
                break;

            case DCSBootstrap_Config::MODE_TEST:
                DCSBootstrap_Config::init_test_mode();
// turn it on again
                break;

            default:
// round one
                DCSBootstrap_Config::init_test_mode();
        } // end switch
    }

    protected function tearDown(){
        WLL__Adapter::reset_instance();
    }

}

And, of course, a table class that implements the adapter.

	<?php

class WLL__Table_Abstract
extends Zend_Db_Table_Abstract {

        public function  __construct() {
        parent::__construct(WLL__Adapter::instance());
    }

}

This abstract class loads the currently active adapter (or creates one if its been reset) into every table, so all SQL going through the table gateway will point to temporary data.

This is not the only way to do Unit Testable data; the Domain patter allows for a nice branching to file based data but it is a very limited system that enforces a fairly rigid system on your development. Temp table testing on the other hand allows full use of SQL (albeit through a Table ADO). While I do use the OOP MySQL system of Zend_Db, straight SQL can be fed through the table gateways -- and in fact you can pipe straight SQL directly through the adapter, bypassing the table clas system altogether, for complex queries.