Test First!

Let's say that our most important client has a database and one of the tables in the database is a list of people. Our client tells us:

"We would like to use a web application to display the people in this table and to add, edit, and delete individual records."

Not a complicated story, but it will cover the CRUD most developers want to learn first. :) Let's start with the people table that the client mentioned. Since we're keeping it simple, we'll say it's a table in an Access database. The table definition is shown as:

Name              Type            Size
PER_ID            Long Integer      4
PER_FIRST_NAME    Text             40
PER_LAST_NAME     Text             40
PER_BIRTH_DATE    Date/Time         8
PER_WEIGHT_KG     Double            8
PER_HEIGHT_M      Double            8

The first thing our story says is that client would like to display a list of people. The following example shows our test for that.

<?php
class PersonTest extends UnitTestCase
{
    function testPersonList()
    {
        //try it
        $people = TMapper::instance()->queryForList("SelectAll");

        //test it
        $this->assertNotNull($people, "Person list is not returned");
        $this->assertTrue($people->getCount() > 0, "Person list is empty");
        $person = $people[0];
        $this->assertNotNull($person, "Person not returned");
    }
}

Well, the example sure looks easy enough! We ask a method to "select all", and it returns a list of person objects. But, what code do we need to write to pass this test?

Now, to setup the testing framework, suppose you have the SimpleTest framework installed. Then we need to create an entry file to run the tests. See the SimpleTest documentation for further details on setting up tests.

<?php
require_once('../tests/simpletest/unit_tester.php');
require_once('../tests/simpletest/reporter.php');
require_once('../SQLMap/TMapper.php');
require_once('Models/Person.php');

//supress strict warnings from Adodb.
error_reporting(E_ALL);

$test = new GroupTest('All tests');
$test->addTestFile('Tests/PersonTest.php'); $test->run(new HtmlReporter());

To run the tests, point your browser to the "run_test.php" script file served from your web server.

Let's see. The test uses a list of person objects. We could start with a blank object, just to satisfy the test, and add the display properties later. But let's be naughty and skip a step. Our fully-formed person object is shown in the following example

<?php
class Person
{
    public $ID = -1;
    public $FirstName;
    public $LastName;
    public $WeightInKilograms = 0.0;
    public $HeightInMeters = 0.0;

    private $_birthDate;

    //setters and getter for BirthDate
    public function getBirthDate()
    {
        return $this->_birthDate;
    }

    public function setBirthDate($value)
    {
        $this->_birthDate = $value;
    }
}

OK, that was fun! The $this->assertXXX(...) methods are built into UnitTestCase class. So to run the unit test example, we just need the TMapper object and queryForList method. Wonderfully, the SQLMap DataMapper framework has a TMapperclass built into it that will work just fine for for us to use in this tutorial, so we don't need to write that either.

When the TMapper->instance() method is called, an instance of the SQLMap TSqlMapper class is returned that has various methods available such as queryForList. In this example, the SQLMap TSqlMapper->queryForList() method executes our SQL statement (or stored procedure) and returns the result as a list. Each row in the result becomes an entry in the list. Along with queryForList(), there are also delete(), insert(), queryForObject(), queryForPagedList() and a few other methods in the SQLMap API.

Looking at unit test example, we see that the queryForList() method takes the name of the statement we want to run. OK. Easy enough. But where does SQLMap get the "SelectAll" statement? Some systems try to generate SQL statements for you, but SQLMap specializes in data mapping, not code generation. It's our job (or the job of our database administrator) to craft the SQL or provide a stored procedure. We then describe the statement in an XML element, like the one shown the following where we use XML elements to map a database statement to an application object.

<?xml version="1.0" encoding="utf-8" ?>
<sqlMap>
    <select id="SelectAll" resultClass="Person">
        SELECT
            per_id as ID,
            per_first_name as FirstName,
            per_last_name as LastName,
            per_birth_date as BirthDate,
            per_weight_kg as WeightInKilograms,
            per_height_m as HeightInMeters
        FROM
            person
    </select>
</sqlMap>

The SQLMap mapping documents can hold several sets of related elements, like those shown in the unit test case example. We can also have as many mapping documents as we need to help organize our code. Additionally, having multiple mapping documents is handy when several developers are working on the project at once.

So, the framework gets the SQL code for the query from the mapping, and plugs it into a prepared statement. But, how does SQLMap know where to find the table's datasource?

Surprise! More XML! You can define a configuration file for each datasource your application uses. The following code shows a configuration file named "sqlmap.config" for our SQLite database.

<?xml version="1.0" encoding="UTF-8" ?>
<sqlMapConfig>
    <provider class="TAdodbProvider">
        <datasource driver="sqlite" host="Data/test.db" />
    </provider>
    <sqlMaps>
        <sqlMap resource="Data/person.xml"/>
    </sqlMaps>
</sqlMapConfig>

The <provider> specifies the database provider class, in this case TAdodbProvider using the Adodb library. The <datasource> tag specifies the database connection details. In this case, for an SQLite database, we just need the driver name, and the host that points to the actual SQLite database file.

The last part of the configuration file ("sqlMaps") is where we list our mapping documents, like the one shown back in the previous code sample. We can list as many documents as we need here, and they will all be read when the configuration is parsed.

OK, so how does the configuration get parsed?

Look back at the unit test case example. The heart of the code is the call to the "TMapper" object (under the remark "try it"). The TMapper object is a singleton that handles the instantiation and configuration of an SQLMap TSqlMapper object, which provides a facade to the SQLMap DataMapper framework API.

The first time that the TMapper is called, it reads in the sqlmap.config file and associated mapping documents to create an instance of the TSqlMapper class. On subsequent calls, it reuses the TSqlMapper object so that the configuration is not re-read.

The framework comes bundled with a default TMapper class for you to use immediately to get access to the SQLMap client TSqlMapper object. If you want to use a different name other than sqlmap.config at the default location for the configuration file, or need to use more than one database and have one TSqlMapper per database, you can also write your own class to mimic the role of the Mapper class view by copying and modifying the standard version.

If we put this all together into a solution, we can "green bar" our test. At this point you should have the following files.

Data/person.xml             % Mapping file.
Data/test.db                % SQLite database file.

Models/Person.php           % Person class file.

Tests/PersonTest.php        % Unit test case for Person mapping.

run_tests.php               % Unit test entry point.
sqlmap.config               % SQLMap configuration file.

Run the tests by pointing your browser URL to the "run_tests.php" server file.