roetgers.org

25Oct/095

Integrate Propel with the Zend Framework

Let me guess your first question after reading the title: "Why should I use Propel? The Zend Framework has its own database abstraction." Check out my previous blog post on that topic for an answer: Why I prefer Propel over Zend_Db

Installing Propel

The easiest and fastest way to install Propel is PEAR. Open your command line and type the following:

pear channel-discover pear.phpdb.org
pear install -a phpdb/propel_generator
pear install -a phpdb/propel_runtime

Afterwards Propel should be installed including all needed dependencies (like Phing). If you have problems installing Propel, check out the official documentation for alternative ways.

Setting Up the Zend Framework Project

If you never set up the Zend Framework before, please refer to the official Quickstart Guide. I use the same structure as created by the zf Command Line Tool.

Setting Up Propel

At first I must create some configuration files, so Propel knows how my database layout should look like, where to put the generated models and how to connect to my database server. Place the configuration files under application/configs/.

Creating schema.xml

The schema.xml file describes my database. Consequently that is my root tag.

Inside the database tag I can define the tables I need in my database. I will create some tables which could be used for a simple blog application.

The file will grow now: Each table needs some columns of course. Columns have to be placed inside the corresponding table tag.

Last step is to define the foreign keys. The foreign key definitions must be placed inside the table tags like the columns.

By parsing the schema.xml Propel learns how my database should look like. It will create SQL statements and models out of it for me. But before I can start the generation process, I have two more things to do.

Creating build.properties

The Propel generator needs some additional information about my database. I provide the information in the form of a simple property file.

propel.project = Blog

# database settings
propel.database = mysql
propel.database.url = mysql:host=localhost;dbname=blog
propel.database.user = myuser
propel.database.password = mypassword

# directories
propel.output.dir = /var/www/blog/htdocs/application/models
propel.php.dir = ${propel.output.dir}/classes
propel.phpconf.dir = ${propel.output.dir}/conf
propel.sql.dir = ${propel.output.dir}/db/sql

# mysql specific
propel.mysql.tableType = InnoDB

propel.addIncludes = false
propel.addGenericAccessors = true
propel.addGenericMutators = true

The database settings should be self-explanatory. The "directories" part configures where the Propel generator puts his generated files. I point all subdirs in my general output dir which is my "models" directory. The last directives tell the Propel generator what customizations I wish. This is only neccessary if you want to change default values.

Creating runtime-conf.xml

The last config file is responsible for the runtime settings.

<?xml version="1.0" encoding="UTF-8"?>
<config>
<propel>
<datasources default="blog">
<datasource id="blog">
<adapter>mysql</adapter>
<connection>
<dsn>mysql:host=localhost;dbname=blog</dsn>
<user>myuser</user>
<password>mypassword</password>
<settings>
<setting id="charset">utf8</setting>
</settings>
</connection>
</datasource>
</datasources>
</propel>
</config>

Attention: The datasource id must match the database name I defined in schema.xml. That's it! Now comes the fun.

Generating Models

I open the command line again to start propel-gen. propel-gen expects an absolute path to the directory in which the config files reside. In my case it is /var/www/blog/htdocs/application/configs.

propel-gen /var/www/blog/htdocs/application/configs

You should see a lot of text appearing on your terminal and it should end with:

BUILD FINISHED

Total time: 1.1469 second

Open your models directory and you should find three new folders called classes, conf and db. Open the folder db and afterwards the folder sql. Open the file schema.sql. Depending on what DBMS you are using the file should look exactly or at least pretty similar to this:

# This is a fix for InnoDB in MySQL >= 4.1.x
# It "suspends judgement" for fkey relationships until are tables are set.
SET FOREIGN_KEY_CHECKS = 0;

#-----------------------------------------------------------------------------
#-- post
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `post`;

CREATE TABLE `post`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255) NOT NULL,
`text` TEXT NOT NULL,
`timestamp` DATETIME NOT NULL,
`user_id` INTEGER NOT NULL,
PRIMARY KEY (`id`),
INDEX `post_FI_1` (`user_id`),
CONSTRAINT `post_FK_1`
FOREIGN KEY (`user_id`)
REFERENCES `user` (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- comment
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `comment`;

CREATE TABLE `comment`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255) NOT NULL,
`text` TEXT NOT NULL,
`timestamp` DATETIME NOT NULL,
`post_id` INTEGER NOT NULL,
`user_id` INTEGER NOT NULL,
PRIMARY KEY (`id`),
INDEX `comment_FI_1` (`post_id`),
CONSTRAINT `comment_FK_1`
FOREIGN KEY (`post_id`)
REFERENCES `post` (`id`),
INDEX `comment_FI_2` (`user_id`),
CONSTRAINT `comment_FK_2`
FOREIGN KEY (`user_id`)
REFERENCES `user` (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- user
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`email` VARCHAR(255) NOT NULL,
`password` VARCHAR(40) NOT NULL,
PRIMARY KEY (`id`)
)Type=InnoDB;

# This restores the fkey checks, after having unset them earlier
SET FOREIGN_KEY_CHECKS = 1;

As you can see Propel generated all SQL statements needed to create tables and columns I defined in my schema.xml including the foreign keys. Apply the SQL now to your database.

Fine ... but where are my Models?

Open the classes directory which resides under the model folder. There should be a subdirectory called Blog. Inside the Blog folder you find your models.
For example there should be a file Post.php and a file PostPeer.php. Post.php contains a class called Post which represents a row from our post table. The class Post extends BasePost. The class BasePost includes a lot of logic generated by Propel in order to interact with the database. This class will never be touched by a developer. Propel regenerates the Base classes whenever neccessary. Therefore your changes would be lost. If additional logic is needed in the row object, it must be added in the Post class. These classes are only created if they do not exist. Propel will never touch them again after initial creation. Same applies to the Peer classes.
In order to use my new models I need to integrate Propel with my Zend Framework bootstrap process.

Integrating Propel with the Zend Framework

First of all make sure the PEAR folder (or whereever you located Propel) is part of the include_path. If not, add it in your php.ini or via set_include_path. The generated model files are not compatible with the Zend Framework autoloader, so I need to bootstrap Propel seperately. Open your Bootstrap.php file in your APPLICATION_PATH and add the following _initPropel method to it.

<?php

class Bootstrap extends Zend_Application_Bootstrap_Bootstrap
{
/* other bootstrapping ... */

protected function _initPropel()
{
require_once 'propel/Propel.php';
Propel::init(APPLICATION_PATH . '/models/conf/Blog-conf.php');
}
}

Having Fun with Propel

Open your IndexController.php to do some experiments. At first I create some data so later I can select something.

<?php

class IndexController extends Zend_Controller_Action
{
public function indexAction()
{
$user = new User();
$user->setEmail('mike@roetgers.org')
->setPassword(sha1('qwertz'))
->save();

$anotherUser = new User();
$anotherUser->setEmail('anothermike@roetgers.org')
->setPassword(sha1('qwerty'))
->save();

$post = new Post();
$post->setTitle('Some title ...')
->setText('Some text ...')
->setTimestamp(new DateTime())
->setUser($user)
->save();

$comment = new Comment();
$comment->setTitle('The sun ...')
->setText('... is shining.')
->setUser($anotherUser)
->setPost($post)
->save();
}
}

No more SQL. I can forget about the database and just work with comfortable objects. For example I don't have to pass the user_id into the Post object in order to associate it with the user who wrote it. I simply pass the whole User object, Propel does the rest.

<?php

class IndexController extends Zend_Controller_Action
{

// ...

public function selectAction()
{
$criteria = new Criteria();
$user = UserPeer::doSelectOne($criteria);

$posts = $user->getPosts();

foreach ($posts as $post) {
$comments = $post->getComments();
// ...
}
}
}

The Criteria objects are like the WHERE statements in my SQL statement. With their help I can define what exact data I want from the database. The UserPeer class returns one User. In this case the Criteria object is empty, so it grabs the first one from the database. With the User object I can then fetch all posts of that specific user, iterate over them and grab all associated comments.
Let the Comment object return its creation timestamp. You don't get a string with the DATETIME information from the database but a DateTime object just like you would expect it.

Propel is very intuitive. If you have an IDE with auto completion you can handle your database without the slightest effort. Check out the documentation for more examples.

Tagged as: Bootstrap, Generator, Integration, Model, ORM, Propel, Zend Framework 5 Comments

21Oct/095

Why I Prefer Propel over Zend_Db(_Table)

First of all: I like Zend_Db and its subcomponents. I used it in some of my private and also business projects. It is a firm tool to work with your database.
The post is focussed on "ORM features" so I will center on the subcomponent Zend_Db_Table. In the beginning I'll show what you can do with Zend_Db_Table, afterwards I point out which problems Zend_Db_Table in my opinion has and how Propel solves it.

Zend_Db_Table Features

I use the same table layout like it is used in the official Zend_Db manual. That means I have the following tables:

Database Layout

In detail the tables look like this:

The Table Class

In order to operate for example on the table bugs, you can create a class which extends Zend_Db_Table_Abstract.

class Bugs extends Zend_Db_Table_Abstract
{
protected $_name = 'bugs';
}

That's it. If you instantiate the class Bugs, you get an object which represents the bugs table (see Table Data Gateway). Those four lines are enough to enable the developer to insert new rows, update existing rows, delete rows and of course select rows over a simple object orientated API.

Some Examples

$bugs = new Bugs();
$bugs->insert(array(
'bug_description' => 'A description ...',
'bug_status' => 'NEW'
));

$select = $bugs->select()->where('bug_status = ?', 'NEW');
$rowset = $bugs->fetchAll($select);

foreach ($rowset as $row) {
$row->bug_status = 'CLOSED';
$row->save();
}

$bugs->delete(
$bugs->getAdapter()->quoteInto('bug_status = ?', 'CLOSED')
);

As you can see I create a new bug, select all bugs which have the status "NEW", iterate over the set of rows, change each row and save it back to the database. Afterwards I delete all bugs with the status "CLOSED". The API is easy, the code is readable.

What I have is a special table class which represents a table from my database. I can add custom methods to it for recurring and/or complex table operations. That is good. But additionally I need a Bug class which represents a single row from my bugs table. Luckily I can tell the Bugs class that it shall return Bug objects instead of generic Zend_Db_Table_Row objects.

class Bugs extends Zend_Db_Table_Abstract
{
protected $_name = 'bugs';

protected $_rowClass = 'Bug';
}

The class Bug must extend Zend_Db_Table_Row_Abstract. If I execute a select statement via the Bugs class, it will return Bug objects. Now I can implement row specific functionality in my Bug class. The same is possible with the rowset classes.

One big thing is still missing which is inevitable if you are working with a relational database.

Relationships

Relationsships are noted in the table classes. I start with the simplest table:

class Accounts extends Zend_Db_Table_Abstract
{
protected $_name = 'accounts';

protected $_rowClass = 'Account';

protected $_dependentTables = array('Bugs');
}

The table accounts has a dependent table called bugs. You only declare dependent tables if your DBMS does not support cascading write operations (referential integrity). The class for the table products looks pretty similar:

class Products extends Zend_Db_Table_Abstract
{
protected $_name = 'products';

protected $_rowClass = 'Product';

protected $_dependentTables = array('BugsProducts');
}

Now it gets more exciting. The table bugs has references to the table accounts. I declare them as "reference rules" in the $_referenceMap attribute:

class Bugs extends Zend_Db_Table_Abstract
{
protected $_name = 'bugs';

protected $_rowClass = 'Bug';

protected $_dependentTables = array('BugsProducts');

protected $_referenceMap = array(
'Reporter' => array(
'columns' => 'reported_by',
'refTableClass' => 'Accounts',
'refColumns' => 'account_name'
),
'Engineer' => array(
'columns' => 'assigned_to',
'refTableClass' => 'Accounts',
'refColumns' => 'account_name'
),
'Verifier' => array(
'columns' => 'verified_by',
'refTableClass' => 'Accounts',
'refColumns' => 'account_name'
)
);
}

For every reference rule I have to note the column in the dependent table ("columns"), the parent table class ("refTableClass") and the referenced column in the parent table ("refColumns"). In the reference map you can additionally declare the cascading write operations (onDelete and onUpdate, see Cascading Write Operations in the official documentation for more). One table is still missing:

class BugsProducts extends Zend_Db_Table_Abstract
{
protected $_name = 'bugs_products';

protected $_referenceMap = array(
'Bug' => array(
'columns' => 'bug_id',
'refTableClass' => 'Bugs',
'refColumns' => 'bug_id'
),
'Product' => array(
'columns' => 'product_id',
'refTableClass' => 'Products',
'refColumns' => 'product_id'
)
);
}

My table objects are now able to work with relations. An example: I will select a random account from the accounts table and afterwards select all bugs reported by that account.

$accounts = new Accounts();
$account = $accounts->fetchRow(null, new Zend_Db_Expr('RAND()'));

$bugs = $account->findBugsByReporter();

The syntax is "find<TABLE>By<RULE>". I can query the parent table with the same schema:

$bugs = $account->findBugsByReporter();
$bug = $bugs->current();
$reporter = $bug->findParentAccountsByReporter();

The syntax is "findParent<TABLE>By<RULE>". Many-to-many relationships with an intersetion table are possible too:

$bugs = new Bugs();
$bug = $bugs->fetchRow(null, new Zend_Db_Expr('RAND()'));

$products = $bug->findProductsViaBugsProductsByBug();

The syntax is "find<TABLE>Via<INTERSECTIONTABLE>By<RULE>". The syntax is probably a bit confusing in the first moment but you get used to it fast.

Ok, the intro was a bit lengthy. Next I will show what annoys me about Zend_Db_Table and how Propel does it better.

Zend_Db_Table Drawbacks and Propel Advantages

First let me say: All the things I showed with Zend_Db_Table above, Propel can do too.

Table Metadata

Remember how a simple table class looks like?

class Bugs extends Zend_Db_Table_Abstract
{
protected $_name = 'bugs';
}

The only thing I configure is the table's name. What happens if I use such a class? How does it get the needed metadata? The manual says:

By default, Zend_Db_Table_Abstract queries the underlying database for table metadata whenever that data is needed to perform table operations.

That happens everytime when I execute for example an insert or a find method. A Zend_Db advocate would probably argument that I can utilize a cache. Yes I can do that. It's actually very easy to set that up:

$cache = Zend_Cache::factory(
$frontendName,
$backendName,
$frontendOptions,
$backendOptions
);
Zend_Db_Table_Abstract::setDefaultMetadataCache($cache);

From now on every table object can use the cache instance to save the requested metadatas. The DESCRIBE queries are reduced to a minimum. But in my opinion that's not the best solution. Calling Zend_Cache is not as expensive as querying the database but it still costs performance. Additionally I must have a strategy if I want to take a database change live. Do I just put it live and wait until the cache times out? Could lead to weird behaviour. I could invalidate all metadata cache records after deploying the new version on the live systems. What if I use memcache? I can't even put a tag on all metadata cache entries so I have to maintain a list of all metadata cache record names in order to kill them with a script. Or I invalidate the relevant ones by hand. Doesn't sound very comfortable to me.
Luckily Zend_Db_Table offers a third option. I can hardcode the table metadata in my table class. From the manual:

class Example extends Zend_Db_Table_Abstract
{
protected $_name = 'example';

protected $_metadata = array(
'<column_name>' => array(
'SCHEMA_NAME' => <string>,
'TABLE_NAME' => <string>,
'COLUMN_NAME' => <string>,
'COLUMN_POSITION' => <int>,
'DATA_TYPE' => <string>,
'DEFAULT' => NULL|<value>,
'NULLABLE' => <bool>,
'LENGTH' => <string - length>,
'SCALE' => NULL|<value>,
'PRECISION' => NULL|<value>,
'UNSIGNED' => NULL|<bool>,
'PRIMARY' => <bool>,
'PRIMARY_POSITION' => <int>,
'IDENTITY' => <bool>,
),
// additional columns...
);
}

In principle I like this solution. It's plain PHP what means it's fast. And there is less magic, because I can see what the table class knows. But imagine you have a database with 100 tables and an average of 5 columns per table. I bet you do everything to prevent database changes.

So how does Propel solve this problem? Propel hardcodes the metadata in PHP too. But you, as the developer, don't have to care about it. If you change the database, just run the Propel Generator again. Best solution is probably to make the generation a task in your deployment script. Propel regenerates all table classes and adapts the database changes instantly. So no manual work for you.

Where Is My Autocompletion?

Zend_Db_Table is very generic. Doesn't sound too bad, does it? However my IDE has no idea about a lot of methods and attributes the table and row classes offer me. Remember all those find* methods? None of them are available via autocompletion. That is logical. How could Zend_Db_Table_Abstract know what reference rules I defined in my Bugs class which extends the abstract class. The attributes in a row object are realized with the magic methods __get, __set, __isset and __unset. Whenever I use a row class I have to manually look up what columns the table has, because the IDE does not offer autocompletion for magic attributes.
I defined a special row class in every table class (e.g. the table class Bugs returns sets of Bug objects intead of Zend_Db_Table_Row objects). Now if I use the fetchRow() method of my Bugs class, it returns a Bug object. But my IDE still thinks it returned a Zend_Db_Table_Row object because the @return phpdoc tag in Zend_Db_Table_Abstract still says so. That is annoying.

Again the Propel Generator can score here because it materializes all those magic methods and adds correct phpdoc to them so my IDE knows about everything.

Working with Objects

I'm using an ORM because I want to think in terms of objects and not database tables. But sometimes Zend_Db_Table prevents me from doing exactly that. For example I create a new bug:

$accounts = new Accounts();
$assignee = $accounts->find("Mike");

$bugs = new Bugs();
$bug = $bugs->createRow();
$bug->bug_description = 'My description ...';
$bug->bug_status = 'ASSIGNED';

and now I want to add my assignee to it:

$bug->assigned_to = $assignee;
$bug->save();

What is saved in the column assigned_to? Not the primary key but"Object". You have to pass the primary key! Passing the whole object is not possible. That is not very intuitive. Let's do the same with Propel:

$assignee = AccountPeer::retrieveByPK("Mike");

$bug = new Bug();
$bug->setBugDescription('My description ...')
->setBugStatus('ASSIGNED')
->setAssignee($assignee)
->save();

That is what I mean with thinking in term of objects. It seems like nothing but it makes work a lot easier.

Conclusion

As I said in the very beginning: Zend_Db_Table is a firm tool. But Propel is more conveniant. You don't have to concentrate so much on what I may pass here or how the method's name is there. You can focus on building the actual application. In my opinion Zend_Db_Table could improve a lot if it would offer the possibility to generate table and row classes like propel does it. The Zend Framework offers all tools needed (Zend_CodeGenerator, Zend_Reflection). A colleague of mine is already working on a solution and hopefully release it soon. Until then I will definitely continue using Propel.

Why I Prefer Propel over Zend_Db(_Table) by Mike Rötgers is licensed under a Creative Commons Attribution 3.0 Germany License.

Tagged as: Comparision, ORM, PHP, Propel, Zend Framework, Zend_Db, Zend_Db_Table 5 Comments