Using Phing to create and seed databases

Influenced by my current reading of "Continuous Integration, Improving Software Quality and Reducing Risk" by Paul M.Duval et al. and it's chapter about Continuous Database Integration I was curious if Phing provides any out-of-the-box tasks to enable the use of this process step in a Phing based buildfile. And it does via its PDOSQLExecTask which is available in the Phing 2.3.0beta1 release amongst other useful tasks e.g. the SvnCheckoutTask.

This task can be used to automate e.g. the following common and repeating scenarios:

• Create and seed databases with clean test data prior to (component) tests.


• Create and seed databases with default/standard data at deployment.


• ...

The following buildfile shows the PDOSQLExecTask in action. The first target uses all Sql files located in ./test-sql as an input source whereas the second one uses inline Sql statements.

<?xml version="1.0" ?>
<project name="example" basedir="." default="prepare-test-databases-1">

   <property name="pdo.driver" value="mysql" />
   <property name="db.host" value="localhost" />
   <property name="db.name" value="application_test" />

   <!-- Create and seed databases from file -->
   <target name="prepare-test-databases-1">
       <pdo url="${pdo.driver}:host=${db.host};dbname=${db.name}" encoding="utf8"
            userId="username" password="userpassword"
            onerror="abort">
            <fileset dir="test-sql">
                <include name="*.sql"/>
            </fileset>
       </pdo>
   </target>

   <!-- Create and seed databases inline -->
   <target name="prepare-test-databases-2">
       <pdo url="${pdo.driver}:host=${db.host};dbname=${db.name}"
            userId="username" password="userpassword"
            onerror="abort">

            DROP DATABASE IF EXISTS `${db.name}`;

            CREATE DATABASE `${db.name}`;

            USE `${db.name}`;

            DROP TABLE IF EXISTS `users`;

            CREATE TABLE `users` (`id` int(11) NOT NULL auto_increment COMMENT 'User Id',
                                  `first_name` varchar(128) NOT NULL default '' COMMENT 'User first name',
                                  `last_name` varchar(128) NOT NULL default '' COMMENT 'User last name',
                                  `password` varchar(25) NOT NULL default '' COMMENT 'User password',
                                   PRIMARY KEY  (`id`))
                                   ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Users test table';

            INSERT INTO `users` (`id`, `first_name`, `last_name`, `password`) 
              VALUES (0, 'John', 'Doe', '3dHdju47');
            INSERT INTO `users` (`id`, `first_name`, `last_name`, `password`) 
              VALUES (0, 'Jane', 'Doe', 'ZjG300irl');

            <!-- Other DDL/DML Sql statements -->

       </pdo>
   </target>

   <!-- Test part of the application -->
   <target name="test-application-usermanagement" depends="prepare-test-databases-1">

       <!-- PHPUnitTask Setup etc. -->

   </target>

</project>