path: root/docs/sqlmap/latex/tut1.tex

\section{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 Example~\ref{example:1}.

\begin{example}\label{example:1}
The Person Table
\begin{verbatim}
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
\end{verbatim}
\end{example}

\begin{mybox}{Tip:}
    This example is bundled with a SQLite database file ``Data/test.db''
    that contains the \tt{Person} table and some data, ready to use.
\end{mybox}

The first thing our story says is that client would like to display a list of
people. Example~\ref{example:2} shows our test for that.

\begin{example}\label{example:2}
Tests/PersonTest.php
\begin{verbatim}
<?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");
    }
}
?>
\end{verbatim}
\end{example}

Well, Example 2 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?

\begin{mybox}{Note:}
    Save the PersonTest.php into a \tt{Tests} directory. The unit tests are
    written for the SimpleTest framework (http://simpletest.sf.net).
\end{mybox}

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

\begin{example}\label{example:2a}
Unit test entry file, \tt{run\_tests.php}.
\begin{verbatim}
<?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());
?>
\end{verbatim}
\end{example}
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
Example~\ref{example:3}.

\begin{example}\label{example:3}
Models/Person.php
\begin{verbatim}
<?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;
    }
}
?>
\end{verbatim}
\end{example}

OK, that was fun! The \tt{\$this->assertXXX} methods are built into
\tt{UnitTestCase} class. So to run Example~\ref{example:2}, we just need the
\tt{TMapper} object and \tt{queryForList} method. Wonderfully, the SQLMap
DataMapper framework has a \tt{TMapper}class 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 \tt{TMapper->instance()} method is called, an instance of the SQLMap
\tt{TSqlMapper} class is returned that has various methods available such as
\tt{queryForList}. In this example, the SQLMap \tt{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
\tt{queryForList()}, there are also \tt{delete()}, \tt{insert()},
\tt{queryForObject()}, \tt{queryForPagedList()} and a few other methods in the
SQLMap API. (See Chapter 9 in the SQLMap DataMapper Developer Guide for
details.)

Looking at Example~\ref{example:2}, we see that the \tt{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 in Example~\ref{example:4}.

\begin{example}\label{example:4}
We use XML elements to map a database statement to an application object.
\begin{verbatim}
<?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>
\end{verbatim}
\end{example}

The SQLMap mapping documents can hold several sets of related elements, like
those shown in Example~\ref{example:4}. 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. Example~\ref{example:5} shows a configuration file for
our SQLite database.

\begin{example}\label{example:5}
sqlmap.config - a configuration file for our SQLite database
\begin{verbatim}
<?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>
\end{verbatim}
\end{example}

The \tt{<provider>} specifies the database provider class, in this case
\tt{TAdodbProvider} using the Adodb library. The \tt{<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 Example~\ref{example:4}. 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 Example~\ref{example:2}. The heart of the code is the call to the
``\tt{TMapper}'' object (under the remark "try it"). The \tt{TMapper} object
is a singleton that handles the instantiation and configuration of an SQLMap
\tt{TSqlMapper} object, which provides a facade to the SQLMap DataMapper
framework API.

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

The framework comes bundled with a default \tt{TMapper} class for you to use
immediately to get access to the SQLMap SqlMapper object. If you want to use a
different name other than \tt{sqlmap.config} at the default location for the
configuration file, or need to use more than one database and have one
SqlMapper 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.

\begin{mybox}{Tip:}
    You can also call \tt{TMapper::configure('/path/to/your/sqlmap.config')}
    to configure the \tt{TMapper} for a specific configuration file.
\end{mybox}

If we put this all together into a solution, we can ``green bar'' our test. At
this point you should have the following files.
\begin{verbatim}
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.
\end{verbatim}

Run the tests by pointing your browser URL to the ``run\_tests.php'' server
file.

\begin{figure}[!h]
    \centering
        \includegraphics[width=0.7\textwidth]{example1}
    \caption{Green Bar!}
    \label{fig:diagram}
\end{figure}