TonyBibbs^2.0

In the world of object persistence few PHP5 libraries can compete with Propel and as a result nearly all of our PHP solutions include Propel as a way to limit the amount of SQL written which helps ensure some level of database portability. Propel is based on Torque, a Java-based ORM that I'm vaguely familiar with, however, I do have experience with Hibernate which is a competing ORM implementation to Torque. Assuming you appreciate the things that an ORM can bring to your software development projects, there is a common problem amongst all implementations that, frankly, annoys me. All suggest that developers should not write any SQL at all, rather, be forced to learn some sort of pseudo-SQL'ish language that will help guarantee database portability. This is the sort of over-engineering that drives many software architects nuts and, for me, is one of the huge detractors to Java in general (though no real fault of the Java language itself).

In Hibernate they have HQL, a bastardization of SQL meant to make the SQL portable. In Propel/Torque they have a Criteria class which provides an object-oriented way to write SQL with the same goals as HQL. Why did they do this? Frankly I have no idea other than they put far too much emphasis on the use of object oriented principles and database portability at the expense of ease of development. Fact is, most developers should know SQL, it's a requirement for everyday development jobs and we shouldn't be forcing them to learn yet another way to write queries against a database, rather, build on what they already know (SQL) and limit the damage that can be done with non-portable SQL. How can you do this? With the concept of named queries and a simple data access object (DAO).

What are named queries? It's a concept I've borrowed from the Hibernate world and is conceptually quite simple. For any queries that can't be done with the native ORM-generated domain objects (or are too hard to be done in that model) you can put the native SQL into one or more xml-based files referred to hereon as named query files. Each SQL statement in the named query file is given a name such as getCustomersLastTenOrders. Then you can use a simple DAO to execute the named query which can return a set of ORM-generated domain object that matched the query.

Ok, so why put all the queries into one or more XML files? Simple, ease-of-reuse. In some of my past MySQL projects our code made use of the nasty "REPLACE INTO" syntax which is not portable to other databases. While that was a mistake, the bigger mistake was that the syntax was used all over in the source code so when it came time to fix the problem so we could port it to another database it was quite painful. Now imagine having your "REPLACE INTO" queries inside of a single named query file and in your code that uses that SQL simply executing the corresponding named query. While using that nasty bit of SQL is bad, you have now isolated the problem to one file and to make the SQL portable you simple need to fix the query in the named query file and then every place you use that named query gets the benefit of your fix.

Another use of named queries is for regression testing your SQL after massive database changes. While I don't have this implemented in the existing DAO it would be quite possible to build a set of test data assembled into a SQL script that you can easily import into your database. Inside of the definition of your named query you could specify the the test parameters for the query and then you can easily write PHPUnit scripts that would test all of the SQL in your application. So how does all this work?

First you will want to grab the DAO PEAR package. I'll assume before doing so you have installed Propel and have built the domain objects and if you have no clue what I'm talking about it's time to read up on it. From there you simple need to create your first named query file (see an example here and the XSD here). From there it's simply a matter of running your first named query...something like the following:

10:  require 'Geeklog/DAO/DAO.php';
20:  
30:  $options = array('databaseName'=>'myDatabase',
40:                   'namedQueryFile'=>'/path/to/NamedQueries.xml',
50:                   'pathDomainObjects'=>'/path/to/propel/objects/');
60:  $dao = &Geeklog_DAO::singleton($options);
70:  $link = $dao->find('getLinkByIdOrRewriteId', array($this->urlArgs[2], $this->urlArgs[2]));

For reference the corresponding named query looks like this:

<query name="getLinkByIdOrRewriteId">
        <sql propelPeer="Gl2LinkPeer">
            SELECT gl2_link.* 
            FROM gl2_link, gl2_item 
            WHERE gl2_item.item_id = gl2_link.link_id 
              AND ((gl2_link.link_id = ?) OR (gl2_item.rewrite_id = ?))
        </sql>
</query>

Notice that the query is in the form of a prepared statement. That's because the DAO's find() method expects queries to be in that form as it, to a small degree, helps with SQL injection. The name attribute on the query tag is the logical name we give the query. The propelPeer attribute on the SQL tag specifies which Propel peer to use to generate the resulting objects (you could argue that just the base Propel object name "GL2Link" should have been used instead...something I am considering to clean up in a later release of the DAO). The rest of what you see is straight SQL.

Before we get too far into the code it should be noted that the urlArgs[] array is a filtered set of data received from the GET or POST. I wouldn't be doing my job if I didn't at least mention the importance of filtering SQL parameters prior to using them. I'd strongly recommend using the new input filtering methods.

So what's going on in the code? Line 10 simply brings in the DAO and for this short article we are only using the find() method but you should look at the entire API (it should be noted the PropelDAO implements the DAOInterface but since Propel is the only support implementation I'm simply linking directly to it).

Line 30 is where we configure the DAO by giving it the database name, absolute path to the named query file and where our project's Propel domain objects reside. Know where Propel put the objects is needed because the DAO will return instances of those objects from the query results.

Line 60 gets an instance of the DAO and then line 70 calls the search() method on the API. What $link contains is an array of Propel Gl2Link objects that we can begin using immediately. It should be noted that the parameters starting on line 30 can be specified via PHP constants (say, in a configuration file) which would eliminate the need of even having line 30 and you could simple modify line 60 as follows:

60:  $dao = &Geeklog_DAO::singleton();

Finally, you should note How about some other example? This is how you can use the DAO to fetch an object by primary key:

10: $dao = &Geeklog_DAO::singleton();
20: $user = new Gl2User();
30: $user->setUserId($_REQUEST['user_id']);
40: $user = $dao->get($user);

Here's a simple save:

10: $dao = &Geeklog_DAO::singleton();
20: $user = new Gl2User();
30: $user->setUserId($_REQUEST['user_id']);
40: $user = $dao->get($user);
50: $user->setFirstName('Jane');
60: $user->setLastName('Doe');
70: $dao->save($user);

Finally a simple delete:

10: $itemToDelete = new Gl2ListItem();
20: $itemToDelete->setListItemId($_REQUEST['itemId']);
30: $dao = &Geeklog_DAO::singleton();
40: $dao->delete($itemToDelete);

So let's recap the benefits of all this:

1. First, use of an ORM reduces the amount of SQL and database work a developer has to do freeing up their mental CPU cycles for innovation.
2. Prevents developers from learning some sort of SQL'ish language in order to achieve database portability
3. Leverages the developers existing SQL skills
4. Named queries give you a way to reuse queries and achieve database portability
5. In an MVC model it gets rid of any SQL from your commands
6. The DAO abstracts any specific calls to Propel making it possible to swap out ORM implementations in the figure

I'd like to conclude all this by reminding developers that the use of an ORM is not an all-or-nothing situation. Too often developers try to make features of an ORM (e.g. HQL) fit the problem they are working on. The named query, DAO implementation I've just demonstrated isn't any different. In general we try to do everything in the DAO model but there are situations where we do use the DAO's getConnection() method and issue raw SQL outside the confines of Propel, the DAO and named queries. Knowing when to do that is often the key to keeping your sanity.