dbUnit: possible to create database out of a dataset?

Question

Is it possible to create the in memory database structure with a DataSet and not using SQL to create the structure?

Currently I have this code, which works. Now I would like to replace the initTable function.

class QueryTest extends \PHPUnit_Extensions_Database_TestCase
{
    // only instantiate pdo once for test clean-up/fixture load
    static private $pdo = null;

    // only instantiate PHPUnit_Extensions_Database_DB_IDatabaseConnection once per test
    private $conn = null;

    // create connection
    public function getConnection()
    {
        if ($this->conn === null) {
            if (self::$pdo == null) {
                self::$pdo = new \PDO('sqlite::memory:');
            }
            $this->conn = $this->createDefaultDBConnection(self::$pdo, "db");

            self::initTable();
        }

        return $this->conn;
    }

    protected function setUp(){
        parent::setUp();
        //$this->pdo->query('CREATE TABLE `test`;');
    }

     public function initTable()
    {
        $query = "
        CREATE TABLE IF NOT EXISTS `guestbook` (
        id INT PRIMARY KEY,
        content VARCHAR(50) NOT NULL DEFAULT '',
        user VARCHAR(20) NOT NULL DEFAULT'',
        created VARCHAR(20) NOT NULL DEFAULT ''
        )
        ";
        static::$pdo->query($query);
    }

    public function getDataSet(){

        return $this->createXMLDataSet(dirname(__FILE__) . '/../../_files/database.xml');
    } 

    public function testData()
    {
        $guestbook = new Guestbook();
        $guestbook->addEntry("suzy", "Hello world!", self::$pdo);

        $queryTable = $this->getConnection()->createQueryTable(
            'guestbook', 'SELECT id, content, user FROM guestbook'
        );
        $expectedTable = $this->createFlatXmlDataSet(dirname(__FILE__) . '/../../_files/expected.xml')
                              ->getTable("guestbook");
        $this->assertTablesEqual($expectedTable, $queryTable);
    }
}

class Guestbook
{
    public function addEntry($name, $message, $pdo)
    {
        $pdo->query('INSERT INTO `guestbook` (`id`, `content`, `user`) VALUES (3, "' . $message . '", "' . $name . '");');
    }
}

Answer 1

I could solve my problem by using this implementation for the unit test.

class QueryPlannerTest extends \PHPUnit_Extensions_Database_TestCase
{
    /**
     * @var type \PDO
     */
    static private $pdo = null;

    /**
     * @var \PHPUnit_Extensions_Database_DB_IDatabaseConnection
     */
    private $conn = null;

    /**
     * @var QueryPlanner
     */
    protected $testObject;

    /**
     * Connects to in-memory database and retuns a connection.
     *
     * @return \PHPUnit_Extensions_Database_DB_IDatabaseConnection
     */
    public function getConnection()
    {
        if ($this->conn === null) {
            if (self::$pdo == null) {
                self::$pdo = new \PDO('sqlite::memory:');
            }

            $this->conn = $this->createDefaultDBConnection(self::$pdo, "db");

            // register the pdo object in the singleton class
            $db = Database::getInstance(self::$pdo);

            self::initDatabase();
        }

        return $this->conn;
    }

    /**
     * Returns a dataset for the current tests.
     *
     * @return \XmlDataSet
     */
    public function getDataSet()
    {
        return $this->createXMLDataSet(dirname(__FILE__) . '/../../datasource/QueryPlanerTestDatabase.xml');
    }

    /**
     * Initializes the in-memory database.
     */
    public static function initDatabase()
    {
        $query = "CREATE TABLE IF NOT EXISTS `user` (
                 `iduser` INT UNSIGNED,
                 `email` VARCHAR(100),
                 `password` VARCHAR(60),
                 `salt` VARCHAR(22),
                 `firstname` VARCHAR(150),
                 `lastname` VARCHAR(150),
                 `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                 `activated` TINYINT(1) NOT NULL DEFAULT '0',
                 `activation_code` VARCHAR(10),
                 `banned` TINYINT(1) NOT NULL DEFAULT '0')";

        self::$pdo->query($query);
    }

    /**
     * Resets the database after each test case.
     *
     * @return \PHPUnit_Extensions_Database_Operation_Truncate
     */
    protected function getTearDownOperation()
    {
        return \PHPUnit_Extensions_Database_Operation_Factory::TRUNCATE();
    }

If you use this to set up your unit test you can simply assert the result of a query with your dataset.