David and Katherine Goodwin

What is Propel?

Propel is a PHP framework that provides a fairly complete ORM solution - object persistence and querying, allowing you to access your database of choice using objects. Its API is similar to that of the Apache Torque project upon which it is based. Torque is written in Java, Propel in PHP. Propel is available for PHP4 and PHP5, although I've only used the PHP5 version, I assume the API remains the same.

What is Object-Relational Mapping?

Whatever reason you have for using objects in your code, anybody who uses them to represent their data in 'traditional' SQL driven PHP applications will know about the blood, sweat, tears that go into the masses of code produced to perform what is in many cases 'boilerplate' actions.

Get a connection to the database, generate the SQL query string making sure to escape embedded user input, execute the query, check for errors, check the number of results, retrieve the results and return the results in a form expected by the API (normally objects or arrays).

It's always the same, with only minor variations. Aside from
the code to talk to the database, what you are essentially doing is converting results in the format your relational database provides, into nice clean objects that you want to use in the rest of your application. This is known as Object-Relational
Mapping. It's something you can do manually, using standard relational database accessing facilities, but the term is more usually applied to tools or frameworks that do the entire job for you - from creating the SQL for the query to returning your
populated objects.

Why ?

When writing any database driven applications, the bain of the developer is having to write reams of code to do the common and repeated CRUD operations (Create, Retrieve, Update, Delete). Lazy (or busy, depending on which way you look at it - it normally amounts to the same thing though) programmers will prefer to use existing, proven code to do a job rather than duplicating effort. This leads those busy developers to look for good tools that can make them more productive (and, in many circumstances, more money!).

Another essential but often tedious element of web development is form validation. It's all too easy to let this aspect of the application slip - after all, users normally put in the right information. In security and usability terms this can be disasterous, so validation should happen on data from forms by default - opt-out for the exceptions rather than opt-in for almost all cases. When validation is included throughout the application, this can mean repeated checks, regular expressions (that many developers find difficult to read) splattered throughout the code and lots of time spent on something that could be much easier.

Enter Propel

Propel makes the CRUD operations on the data, and form validation, very easy - it can even generate an SQL schema.

Propel requires three files :

• build.properties
• runtime-conf.xml
• schema.xml

Although XML configuration files can often strike fear into the heart of even the bravest developer, these are not too daunting.

build.properties and runtime-conf.xml allow you to differentiate between build and runtime parameters for database access. The runtime file can also be used to specify logging. In practice both of these files probably contain around 30-40 lines of information, and rarely need changing.

The magic therefore lives in the schema.xml file - which is essentially an XML version of a database schema. As with an SQL schema, it defines tables and their associated fields. Each field can have one of a range of data types, and other constraints (field size, uniqueness, foreign keys etc). A very simple example is given below, which shows what might be used for a fictional restaurant's menu.

A basic schema.xml:

<database name="restaurant">
 <table name="dish" idMethod="native">
   <column name="id" type="INTEGER" required="true" primaryKey="true" autoIncrement="true" />
   <column name="name" type="VARCHAR" size="200" required="true" />
   <column name="description" type="VARCHAR" size="200" />
   <column name='course_name' type='varchar' size='20'/>
   <foreign-key foreignTable='course'>
     <reference local='course_name' foreign='course_name' />
   </foreign-key>
   <validator column='name'>
     <rule name='match' value='/^[a-zA-Z ]+$/'
       message="Name can only contain letters or spaces"/>
   </validator>
 </table>
 <table name='course' idMethod='native'>
   <column name='course_name' type='varchar' required='true' primaryKey='true' size='20'/>
 </table>
</database>

For every field you define in the schema.xml propel will generate a camel cased set and get method - so for instance the Dish object would have getId(), getName(), setName() etc. If you used underscores in a field name (e.g. field_name) it would be converted to getFieldName() or setFieldName(...).

Data Insertion / Creation

In the below example, the include path setup and Propel initialisation have been omitted as in a production application they
would normally be factored out into a single include file for ease of reuse.

<?php
require_once("common.php");

$dish = new Dish();

$dish->setName("Fred Fish Fingers");
$dish->setDescription("Great for children");

if(!$dish->validate()) {
        echo "Errors : \n";
        $errors = $dish->getValidationFailures();
        foreach($errors as $key => $value) {
                echo "Validation error - " . $value->getMessage($key) . "\n";
        }
}
else {
        echo "Saving - validation ok\n";
        $dish->save();
}
?>

Data Retrieval

Retrieving data through Propel is very easy. If you know value of the primary key field for the object you seek, it's as simple as :

$my_dish = DishPeer::retrieveByPk($the_id);

Of course, that's of limited use in many circumstances, so there are numerous ways to query the database. The easiest for simple operations is through the use of Criteria and Criterion objects. These allow you to programmatically express your search
criteria, without having to write any SQL.

The first, shows a simple ordering of data (effectively equivalent to 'SELECT * from dish order by name ASC').

$c = new Criteria();
$c->addAscendingOrderByColumn(DishPeer::NAME);
$dishes = DishPeer::doSelect($c);

Or, if we wish to find a particular dish by name, we can do something like :

$c = new Criteria();
$c->add(DishPeer::NAME, $something);
$dishes = DishPeer::doSelect($c);

If you wish to apply two different add methods on the same database colum (e.g. DishPeer::NAME), you'll need to use either the addAnd or addOr methods - using 'add' twice on the same column name will result in the first call being replaced by the last.

In the event that you wish to undertake more advanced queries, you can write SQL statements for use with the Propel Objects :

$sql = "SELECT dish.* FROM dish WHERE dish.course_name = 'breakfast' AND dish.name like '%mud%'";
$con = Propel::getConnection("restaurant");
$stmt = $con->createStatement();
$rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_NUM);
$dishes = DishPeer::populateObjects($rs);

Obviously if you choose to write SQL yourself, you (the programmer) become responsible for escaping any special characters in the variables you embed within the SQL string. If, on the other hand, you use the Criteria approach, Propel will handle sanitisation for you.

Finally, if we have foreign keys defined within a table, all of the related objects are accessible from the main object, as shown by the following example :

$course = CoursePeer::retrieveByPk('main');
$dishes = $course->getDishs();

Data Deletion

Once you have a particular propel object, you can just call the 'delete()' function. It's as simple as that.

$the_dish = DishPeer::retrieveByPk(4);
$the_dish->delete();

If you wish to delete a range of objects, you can use a Criteria based approach to select and then do a mass delete (instead of doing a doSelect($c), use doDelete($c)).

Data Updating

This operates in the same way, to creation (above), although obviously requires retrieval beforehand. Once you've retrieved a Propel object, you can simply call the Propel generated methods on your object.

Remember to call validate() before trying to save() an individual object, otherwise an exception will be thrown if there are any problems.

$dish = DishPeer::retrieveByPk(1);
$dish->setName("Sausage and Chips");
if($dish->validate()) {
    $dish->save();
}
else {
    // handle error.
}

Technorati Tags: Documentation PHP propel