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
This example is bundled with a SQLite database file "Data/test.db"
that contains the Person table and some data, ready to use.
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.
class PersonTest extends UnitTestCase
$people = TMapper::instance()->queryForList("SelectAll");
$this->assertNotNull($people, "Person list is not returned");
$this->assertTrue($people->getCount() > 0, "Person list is empty");
$person = $people;
$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.
//supress strict warnings from Adodb.
$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
public $ID = -1;
public $WeightInKilograms = 0.0;
public $HeightInMeters = 0.0;
//setters and getter for BirthDate
public function getBirthDate()
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
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
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" ?>
<select id="SelectAll" resultClass="Person">
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
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
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
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" ?>
<datasource driver="sqlite" host="Data/test.db" />
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
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.
You can also call TMapper::configure('/path/to/your/sqlmap.config')
to configure the TMapper for a specific configuration file.
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
Figure 2: Green Bar!