Object-relational mapping with Doctrine, Flash Builder, and PHP

Rich Internet applications built with Adobe Flex and Flash Builder have been steadily gaining a foothold in enterprise development for quite some time. As the platform has grown and evolved, PHP has also made amazing progress toward becoming a mature, powerful object-oriented language with rapid adoption and dozens of frameworks and design pattern implementations. As PHP continues to prosper, developers are able to borrow more and more of the things Java has got right, taking one check after another from the “Java-only” column. One outstanding example of this is object-relational mapping (ORM). A few different PHP ORM implementations are available, and all of them have positive attributes. However, after some experimentation, I’ve found that Doctrine is my favorite.

For those who haven’t used ORM before, I don’t expect it to be a hard sale. Before I used ORM, I had wished for it a thousand times in ignorance. ORM is, simply put, a way of mapping the objects you use in development to their representation in relational databases. Doesn’t it just seem natural that your Customer object should have a Save method that automatically translates the object’s properties into persistent data? Take this pseudo-code as an example:

$customer->name = "Richard";
$customer->balance = 101.23;
$customer->save();
echo "DONE!";

I know: It looks like a joke. But this is just a small part of what Doctrine can do for you. It can also prevent SQL injection automatically, enforce business rules and data cleanliness standards, and allow you to switch from MySQL to SQLite in 3 minutes. Doctrine even has its own query language—DQL—which makes the most insanely complex SQL code read like “See Spot run.” This article shows how to set up Doctrine and use it in PHP code. Then, it exposes PHP services to Adobe Flash Platform applications using Zend_Amf. You’ll then call the Doctrine-enabled services from a Flex client. Finally, I’ll show you how to switch your database from MySQL to SQLite with four lines of code. And I won’t write a single SQL statement.

System Requirements

The system requirements for this article are:

• PHP version 5.2.9 or later
• MySQL version 4 or later
• Doctrine version 1.2
• Apache HTTP Server version 2
• Adobe Flash Builder Alpha 2 or later
• Zend Framework version 1.8 or later

Development environment notes

For purposes of this article, I used Zend Server Community Edition (CE) version 4.0.6 on the Mac as my Apache/MySQL/PHP stack. Feel free to use any stack that satisfies the minimum requirements, but if you are looking for a convenient, cross-platform testing environment, I highly recommend Zend Server CE. It comes with the Zend Framework preinstalled along with phpMyAdmin for MySQL administration. Also, note that for some portions of this article, you need to run PHP scripts from the command line. For this to function properly, make sure your PHP executable is in your environment PATH variable. For instructions on modifying your environment path, see your operating system’s documentation.

Database Setup

Because you’ll be using Doctrine for most of your database setup and interaction, you only need to create the database at this point. In phpMyAdmin (or your MySQL tool of choice), create a new database named doctrine_classifieds.

Setting Up Doctrine

From the Doctrine project page, click the download link for version 1.2 (see Figure 1). As of this writing, the current version is 1.2 Release Candidate 1.

alt="Doctrine download options" src="/images/articles/11512/doctrine_download.png" />

Figure 1. Doctrine download options

Select the Sandbox release, and save the file to a convenient location. The sandbox is a preconfigured environment that contains all the files you need to get started with minimal setup time. To deploy the sandbox, create a new project folder in your web server’s web root directory called doctrine. Next, decompress the sandbox download. The result will be a folder containing the Doctrine files and directory structure, as shown in Figure 2.

alt="Doctrine's files after decompression" src="/images/articles/11512/doctrine_files.png" />

Figure 2. Doctrine’s files after decompression

Cut or copy and paste these files into the doctrine directory beneath your web root, and Doctrine is now installed.

As in many other PHP applications and frameworks, Doctrine uses a bootstrap file—a PHP script that loads essential classes and sets configuration variables so you can use them in other parts of your application. To begin, create a new file named bootstrap.php in the doctrine folder beneath your web root.

The first thing you need to do is include the Doctrine.php file from the lib subdirectory. The following line does just that:

require_once(dirname(__FILE__) . '/lib/Doctrine.php');

Next, register the Doctrine class autoloader:

spl_autoload_register(array('Doctrine', 'autoload'));

Finally, use a local variable to get an instance of the Doctrine_Manager class:

$manager = Doctrine_Manager::getInstance();

Save the bootstrap.php file, and you’re ready to move on. I explore some of Doctrine’s functionality and do some setup tasks with the next script, test.php.

Create the test.php file in your doctrine folder alongside the bootstrap file. The first thing that should go between your PHP tags is a statement to include the bootstrap file:

require_once('bootstrap.php');

Next, run a simple test to make sure bootstrap.php is error and typo free:

echo Doctrine::getPath();

Save test.php, and open a command-line or Terminal window. Navigate to the doctrine directory, then, execute the test.php script by typing:

• In Mac OS X, php test.php.
• In Windows, php.exe test.php.

The output should be the full path to your Doctrine.php file (<path_to>/<your_web_root>/doctrine/lib). If so, your Doctrine installation and bootstrap are ready to be used. If not, carefully check the terminal/command-line output: It should point you to the source of the problem.

Doctrine’s schema files and portability

A fundamental feature of Doctrine is that it is database agnostic. That means that, with a little planning and preparation, you can use any of a variety of database systems with no changes to your application code. To illustrate this point, I build an application for MySQL initially, and then switch to SQLite. A database engine switch (or even a complete platform switch) is a fairly common occurrence in my development experience. In the past, I would protest such dramatic changes and usually prevail by citing the time and expense required to perform such a transition. Even when the change made sense on other levels, it would often be avoided. Such tough decisions are now a thing of the past, thanks to Doctrine, and you’ll be surprised and pleased at how easy and convenient the process is.

The key to Doctrine’s flexibility is its use of platform-independent schema files. These files take two forms in Doctrine: models and Yet Another Markup Language (YAML) schemas. Models are PHP classes representing your database objects, and though Doctrine adds code to them, they are basically just like custom classes you would write yourself. YAML schema files are declarative outlines of your objects and their relationships. YAML functions similarly to Extensible Markup Language (XML), but YAML differs from XML in that indentation is used to denote the elements, and the result is a less verbose format. YAML is said to be quicker to parse, and I find it much more readable than XML. Figure 3 shows what the application’s schema looks like in TextMate.

alt="The YAML schema" src="/images/articles/11512/doctrine_yaml.png" />

Figure 3. The YAML schema

Note: It is important to remember when working with YAML that tabs are not allowed. You indent with spaces, instead. Most code-centric text editors will have an option to use “soft tabs,” or a predetermined number of spaces inserted when you press the Tab key. Check your editor’s documentation for more information.

The YAML schema file is an independent representation of a database in a single file. Doctrine provides methods for creating both databases and PHP models from schema files; additionally, Doctrine can generate schema files from models or an existing database. Because the schema for my Flex classifieds application is included with this article, you’ll need to create the database tables and PHP models. Before you get back into the code, download schema.yml and place it in your doctrine folder with bootstrap.php and test.php. For the new code, you again use the test.php file from the command line.

Download schema.yml

First, comment-out the echo line:

//echo Doctrine::getPath();

Next, add the following line to generate PHP models from the YAML schema:

Doctrine_Core::generateModelsFromYaml('schema.yml', 'models');

As you can see, the first argument is the schema file. The second argument is the name of the output folder in which the PHP models will reside. Save the file and go back to the command-line/Terminal window. Once again, execute test.php. If the command is successful, there should be no output; otherwise, a verbose error message appears. If everything looks okay, open an Explorer/Finder window and navigate to the doctrine folder. You should now have a models subfolder containing a few PHP files and a subfolder named generated. You’ll use these models to create the necessary tables and fields in the doctrine_classifieds database.

At the top of the test.php file, add this code to build a MySQL connection string:

$dsn = 'mysql:dbname=doctrine_classifieds;host=127.0.0.1';
$user = 'root';
$password = '';

Note: Make sure you enter your valid credentials.

Next, create a new PDO object for a database handle, and pass that handle to the Doctrine_Manager class, assigning the connection to the $conn variable:

$dbh = new PDO($dsn, $user, $password);
$conn = Doctrine_Manager::connection($dbh);

To use your newly generated models, you need to load them with the loadModels() method. The method accepts one argument—the path of a folder containing one or more models:

Doctrine_Core::loadModels('models/generated');
Doctrine_Core::loadModels('models');

Then, comment out the command from the previous step:

//Doctrine_Core::generateModelsFromYaml('schema.yml', 'models');

Finally, add the new call to createTablesFromModels(), passing in the base model folder, models:

Doctrine_Core::createTablesFromModels('models');

Execute test.php from the command line, and check using phpMyAdmin or similar to make sure the doctrine_classifieds database contains four tables. Now, you’re ready to set up Zend_Amf, the gateway between Flash Platform applications and your Doctrine-enabled PHP services.

Setting Up the Zend Framework with Zend_Amf

Note: If you are using Zend Server CD, you do not need to download and install the Zend Framework. Simply skip to the next step and create the gateway.php file.

The first step in setting up the Zend Framework on your development server is to download the latest version. Click the Download Now banner on the project download page, and on the resulting page, scroll to the bottom—the “minimal” download. Click either the .zip or .tar.gz link, and save the file (see Figure 4).

alt="Downloading the Zend Framework minimal distribution" src="/images/articles/11512/zf_download.png" />

Figure 4. Downloading the Zend Framework minimal distribution

When the download is complete, decompress the archive and navigate into the main folder. Paste the library folder to the doctrine directory beneath your web root.

With the Zend Framework files now in place, you need to create a gateway file. The gateway file is a PHP script that initializes your Zend_Amf server class and makes PHP services available to Flash applications through Action Message Format (AMF). To begin, create a new file named gateway.php in your doctrine folder. The contents of the file look like this:

<?php
set_include_path('library');
include 'Zend/Amf/Server.php';
$server = new Zend_Amf_Server();
$response = $server->handle();
echo $response;
?>

Note: Line 1, the set_include_path call, is not necessary if you are running Zend Server CE.

The first two lines make the Zend Framework and Zend_Amf available to the script. Next, a new Zend_Amf_Server is instantiated. To make sure the server accepts the incoming request, the $response variable is set to the server’s handle() method. Finally, the result of $server->handle() is returned to the client. To test your gateway, point your web browser to http://localhost/doctrine/gateway.php. If the gateway is working properly, you should see the text “Zend Amf Endpoint.” Alternatively, you may be prompted to download the gateway.php file. Either of these results is a success. If neither happens, check your code for typos, and make sure any included files are correctly referenced. Also try checking the Apache error log for more details.

Next, you must build the PHP services for the Flex client. Before moving on, however, take a few moments to add some sample records to the ad and user tables in the doctrine_classifieds database.

Building the PHP Services

Now that Doctrine and Zend_Amf are running, you’re ready to build your remote services. The services will be part of a PHP class called ClassifiedService.php. In doctrine/library, create a new directory called services. Create the ClassifiedService.php file inside the services directory.

Just inside the opening PHP tag, add a statement including the bootstrap.php file you created earlier (adjust the path to suit your setup):

require_once('/usr/local/zend/apache2/htdocs/doctrine/bootstrap.php');

Then, place this block of code; it should be familiar, as you used it earlier to set up the database connection in test.php and load your models:

$dsn = 'mysql:dbname=doctrine_classifieds;host=127.0.0.1';
$user = 'root';
$password = '';
$dbh = new PDO($dsn, $user, $password);
$conn = Doctrine_Manager::connection($dbh);
Doctrine_Core::loadModels('/usr/local/zend/apache2/htdocs/doctrine/models/generated');
Doctrine_Core::loadModels('/usr/local/zend/apache2/htdocs/doctrine/models');

Now, open the class and declare the first function. The class name is ClassifiedService, and the first function is getAllAds:

class ClassifiedService {      
    function getAllAds() {
        $q = Doctrine_Query::create()
            ->select('*')
            ->from('Ad');
        $ads = $q->execute();
        return $ads->toArray();
    }
}

The getAllAds function builds a simple select query in DQL. DQL is one of the most incredible features of Doctrine, enabling mere mortals to perform heroically complex queries in a simple and efficient way. You can view the DQL documentation for examples and more information. After the query is executed, the result is transformed into an array and returned to the client.

To make your services available to Flash Platform clients, you must make a few changes to the gateway.php file. First, add an include for the ClassifiedService.php file:

include 'services/ClassifiedService.php';

Then, the Zend_Amf server is instructed to make the ClassifiedService class available to clients with this line, added just above the $server->handle() line:

$server->setClass('ClassifiedService');

And that’s all there is to it. Now you can build the Flash Builder application.

Building the Flash Builder Application

Begin by creating a new project in Flash Builder by clicking File > New > Flex Project. Name the project Classifieds. For the application type, choose Web, and then click Finish. The main application file, Classifieds.mxml, should be open in the editor pane. In the Source view, place your cursor in the line below the end of the opening <s:Application> tag, and insert the following code:

<mx:VBox verticalAlign="top" width="100%" height="494" horizontalAlign="center">
		<mx:Spacer height="15"/>
		<s:Label text="Doctrine-Powered Classifieds" fontSize="25"/>
		<mx:List id="adList" width="575" height="300" itemRenderer="com.flexandair.AdListRenderer"></mx:List>
	</mx:VBox>

The VBox, which encapsulates the other elements, is a container that aligns all its child objects along the vertical axis. The spacer provides a small margin across the top of the VBox, and the Label control tells you that you’re looking at Doctrine Classifieds. The List control will hold the list of items posted in the classifieds database. It has an id property of adList; you use the id to reference the list inside the Adobe ActionScript block later. The list also has its itemRenderer property set to com.flexandair.AdListRenderer. This is a custom visual renderer, and each item listed will appear as an instance of AdListRenderer. Create the AdListRenderer now.

In the left pane, right-click the src folder, and then click New > Package. In the box that appears, type the package name: com.flexandair. This name format is called dot-syntax, and it’s used to avoid naming conflicts. The idea is that if you use a domain name you own, you’ll never have exactly the same package name as someone else. Someone else might have a control called AdListRenderer, but the full path would be com.<their_domain>.AdListRenderer. By using dot-syntax, you could use both of those controls in the same application. To create the AdListRenderer component, right-click the new com.flexandair package, and then click New > MXML Component. Name the component AdListRenderer, and specify a based on property of mx.containers.Canvas. The entire code for the AdListRenderer is as follows:

<?xml version="1.0" encoding="utf-8"?>
<mx:Canvas xmlns:fx="http://ns.adobe.com/mxml/2009" 
		   xmlns:s="library://ns.adobe.com/flex/spark" 
		   xmlns:mx="library://ns.adobe.com/flex/halo" width="575" height="135">
	<fx:Declarations>
		<!-- Place non-visual elements (e.g., services, value objects) here -->
	</fx:Declarations>
	<s:Label x="135" y="21" text="{data.title}" fontSize="16" color="#317EBF" fontWeight="bold" fontFamily="Arial" id="titleLabel"/>
	<s:Label x="440" y="21" text="{data.category}" fontSize="16" color="#FB9005" fontWeight="bold" fontFamily="Arial" id="priceLabel"/>
	<s:TextArea contentBackgroundAlpha="0" text="{data.description}" x="132" y="65" height="55" width="420" editable="false" borderVisible="false" id="adText"/>
	<s:Label x="136" y="52" text="Ad Text/Description:"/>
	<s:Button x="17" y="38" label="Email Seller" width="90"/>
	<s:Button x="17" y="67" label="Edit" width="90" enabled="false"/>
	<s:Button x="17" y="96" label="Delete" width="90" enabled="false"/>
	<s:Label x="19" y="23" text="Actions:"/>
</mx:Canvas>

Most of the controls and properties are self-explanatory. Notice that several controls have their text properties set to {data.something}. When the list control in the main application adds another of these renderers to show an ad, that ad’s properties are passed as a data object. So, if you wanted to show an item’s title, you would use {data.title}. Now that the renderer is ready to go, switch back to the main application file, Classifieds.mxml.

To interact with the Zend_Amf gateway you created earlier, you’ll need to use a remote object. The remote object tells the Flash Builder application where the services are and how to access them. Place this code inside the <fx:Declarations> tag:

<s:RemoteObject showBusyCursor="true" id="adService" endpoint="http://localhost/doctrine/gateway.php" source="ClassifiedService" destination="ClassifiedService" fault="onFault()">
			<s:method name="getAllAds" result="getAdsResult()" />
		</s:RemoteObject>

The remote object has an id of adService. As with other MXML controls, you use this ID to interact with the service. The endpoint property points to the URL of your gateway.php file. The source property must match the remote class you are accessing, which in this case is ClassifiedService. The destination is a mandatory string property. It can be anything, but I usually repeat the source name. You’re also specifying a fault handler called onFault(). Anytime there is a network or security fault, this ActionScript function will be called. You’ll create the function itself in a moment. For now, within the <s:RemoteObject> tag is a child element named <s:method>. For each method of the ClassifiedService class you’d like to call from this application, you must create a method tag. The tag should specify a name (which exactly matches the name in PHP) and a result handler, a function called when a result comes back from PHP. So, in addition to the onFault() function, you need a getAdsResult() function in ActionScript.

To create an ActionScript block, give yourself a few empty lines and begin typing <Script. You should be prompted to insert the <fx:Script> tag, and you should do so, as there must also be CDATA tags, and all this is much easier when auto-inserted. Once within your script block, create the first function, onFault():

public function onFault():void {
				
}

If you are unfamiliar with ActionScript code, here’s a breakdown of the code:

• public. This is the scope keyword. This function can be called from other .mxml files or .as files in the project.
• function. This is a function.
• onFault. This is the function name. Camel-casing is the preferred convention.
• (). Empty parentheses; this function accepts no arguments.
• void. This is the return type. This function returns nothing, so the return type is void.

There is no function body here, so when the function runs, nothing happens. Now for the next function, getAdsResult():

public function getAdsResult():void {
	adList.dataProvider = adService.getAllAds.lastResult;
}

This function performs one simple task: It sets the dataProvider property of the ads list to the lastResult property of adService.getAllAds. That means that the array that PHP returns will be used to populate the list of ads.

Before you run the application, you need to trigger the call to PHP. Ideally, the application would get the list of classified ads as soon as it finishes loading. You’ve already specified that when the result comes in, the ads will be shown in the list. To initiate the call, you simply need to add a new event listener to the applicationComplete property of the <s:Application> tag. Locate the right angle bracket (>) at the end of the opening <s:Application> tag. Immediately to the left of the bracket, add the following code:

applicationComplete="adService.getAllAds()"

Save your project files, and then click the green Run arrow to launch the application. Once the application has loaded, you should see something like Figure 5.

alt="The application running" src="/images/articles/11512/app_running.png" />

Figure 5. The application running

The Easiest Migration Ever

Just to show how easy it is to completely switch database systems with Doctrine, you’ll do it right now in just a couple of minutes. Until now, you’ve been using MySQL to store the classified ads. Now, switch to SQLite. SQLite is a flat-file database format; it has an extremely small footprint and uses no server or running process at all.

To begin, create a new empty file in your text editor named test.db. Save the file to the doctrine directory beneath your web root. Then, open test.php, erase its contents between the PHP tags, and paste the code that follows, being careful to note the slight alterations. Also, be sure to change the path to test.db to match your setup:

require_once('bootstrap.php');
 
$dsn = 'sqlite:////usr/local/zend/apache2/htdocs/doctrine/test.db?mode=0666';
$conn = Doctrine_Manager::connection($dsn);

Doctrine_Core::loadModels('models/generated');
Doctrine_Core::loadModels('models');
Doctrine_Core::createTablesFromModels('models');

Save the file, and execute it from the command line to set up the tables in your empty SQLite database. Next, insert a sample record by first commenting out the last line from the code above:

//Doctrine_Core::createTablesFromModels('models');

Add the following lines at the bottom of the file:

$ad = new Ad();
$ad->title = "1997 Acer Laptop - $230 OBO";
$ad->description = "Gently used. 256k RAM, blazing 150mHz CPU!";
$ad->category = "Electronics";
$ad->user_id = 1;
$ad->item_condition = "Used";
$ad->save();

Then, execute the file again. Open ClassifiedService.php; you’ll now change it to use the new SQLite database. Remove all the lines between the require statement on line 2 and the first call to Doctrine_Core::loadModels(). Replace the deleted lines with these, the connection settings for SQLite:

$dsn = 'sqlite:////usr/local/zend/apache2/htdocs/doctrine/test.db?mode=0666';
$conn = Doctrine_Manager::connection($dsn);

Again, remember to alter the path to test.db, if necessary. Save the file, and go back into Flash Builder. Then relaunch the Classifieds application. You should see the ad you just manually inserted, pulled from the new SQLite database (see Figure 6).

alt="A database system change takes just four lines of code." src="/images/articles/11512/app_running_SQLite.png" />

Figure 6. A database system change takes just four lines of code.

As you can see, the switch from one database system to another requires very little work. It’s even quicker between systems that use similar data source name formats, like Postgres and MySQL.

Next Steps

This article just scratched the surface of what is possible with Doctrine, especially when combined with Flash Platform development. An obvious next step would be to add full CRUD (create, retrieve, update, delete) support to the Classifieds application. A new or edited Ad object can be passed as an argument to remote objects in the Flash Builder application, so additions and updates can easily be completed. You can add any number of remotely available methods to the ClassifiedService class, and you now have the exhaustive Doctrine feature set at your disposal.

Additional Resources

• Doctrine 1.2 Documentation. An in-depth guide and walkthrough
• Doctrine 1.2 API Documentation. Detailed information on built-in functions, their arguments, and output
• Zend Framework Reference Guide Ð Zend_Amf
• Adobe Flash Platform Beta LiveDocs
• Flexandair.com. My development blog with question and contact forms