PHP Object-Relational Mapping: ORM or ROM?

By Manuel Lemos

manuellemos.net

<email contact>

Contents:

- NetMake - ScriptCase sponsors the PHP programming innovation award
- 2Checkout payments available to accept credit cards
- New preview options in the site design editor
- What is Object-Relational Mapping?
- Why use ORM?
- ORM implementation approaches
- ORM or ROM?
- PHP ORM libraries and tools
- Metastorage: the ORM tool used to develop the PHPClasses site


Before the main topic of this article, as usual, here follows some news about the latest developments of the site.


- NetMake - ScriptCase sponsors the PHP programming innovation award

NetMake is the newest sponsor of the PHP programming innovation award
organized by the PHPClasses site.

netmake.com.br/

This company develops a productivity tool named ScriptCase to help PHP
developers create Web applications in a faster way. The main aspect of
ScriptCase that distinguishes it from other PHP productivity tools is
that the tool itself is a Web based application.

scriptcase.net/

Despite being on the Brazilian market since 2001, only recently NetMake
has been expanding to the international market by making available
ScriptCase in English.


- 2Checkout payments available to accept credit cards

Some users have complained that they wanted to buy premium subscriptions or post paid jobs in the site jobs board, but due to restrictions of Paypal they cannot make the payments.

Therefore the site now accepts credit card payments using the 2Checkout payment services. 2Checkout is company that acts as a reseller of products available on Web sites like the PHPClasses site.

2Checkout accepts credit cards among other types of payments and does not require the creation of an account like in some cases when you use Paypal.

So if you tried to pay a premium subscription or a paid job posting before and could not make it due to Paypal restrictions, you may try the 2Checkout payment method now. Just choose 2Checkout in the payment method page of the checkout pages.


- New preview options in the site design editor

The development of the new site design editor continues. Since I have been busy with other developments, it did not advance much lately. The only new feature is the ability to control the type of page you see on the preview window when you edit a theme HTML or CSS file.

Now you can control whether you will see an empty page, the site home page, the browse categories page, or a package page. You can also control whether you will see it as an anonymous user, as a logged user, or as a premium subscriber (without advertising).

files.phpclasses.org/graphics/desig ...


- What is Object-Relational Mapping?

Object-Relational Mapping, usually referred as ORM, is a software development approach to treat data stored in relational (SQL) database table records as if they were objects.

Basically we can create classes with variables that represent fields of a database table. To insert a table record you need to create an object of the class, assign the variable values, and call a function of the class that takes care of inserting the table record. It may look like this.

$client = new client;
$client->name = 'John Doe';
$client->address = 'Av. Somewhere Over The Rainbow, 555';
$client->save();

To update an existing table record, you call a function to retrieve the record field values into a class object, change the object variables, and then call the save function again, for instance like this.

$client = new client('John Doe');
$client->name = 'John Done';
$client->save();

To delete a table record is very similar, except that you should call a delete function instead.

$client = new client('John Doe');
$client->name = 'John Done';
$client->delete();


- Why use ORM?

As you may see, using an ORM approach it becomes much simpler to manipulate information in a database table. You no longer need to write SQL by hand. The ORM classes take care of building and executing the necessary SQL statements to store and retrieve data.

Since the ORM tasks are repetitive and tedious, with the help of ORM libraries or tools you take much less time to develop your database driven applications.

Eventually you may write less error prone applications, as you no longer need to manually write SQL that could contain mistakes.


- ORM implementation approaches

As it was mentioned above, the ORM tasks are repetitive. It is practically the same process that to map each a table into a class. It only varies in the name of the tables, table fields and field values types.

This fact makes ORM class building a perfect candidate to be automated by the means of libraries or tools.

One approach to automate the ORM tasks is to use generic ORM class libraries that provide the necessary functionality to build the SQL queries at run time and execute them when the functions to access the data are called.

Another approach is to use code generation tools to generate code for the ORM classes. Such code may be optimized to build and execute the database queries to store and retrieve the objects in the tables without needing additional runtime ORM libraries.

There are pros and cons to each of the approaches. Generic ORM class libraries tend to be large, as they need to perform all sorts of operations that all ORM classes need, but sometimes only part of the ORM functionality is necessary. So, loading the ORM class libraries may take much more memory than it was necessary.

Also, often ORM class libraries tend to execute query building at run time in a way that adds more overhead to the actual query execution.

On the other hand code generation tools may get rid of the query building overhead by performing smart optimizations, so the generated code executes queries already built at the code generation time.

The main disadvantage of code generation approaches is that often it requires the code to be regenerated when the database table schema changes. Actually this not a real disadvantage because the schema of a database table does not change frequently. Usually it only happens when you upgrade an application, so the code can be regenerated then as part of the upgrade process.


- ORM or ROM?

The expression Object-Relational Mapping suggests that you depart from the Object model to create ORM classes.

In practice many ORM libraries and code generation tools do not work like that. They depart from an existing database table schema to create or generate classes that map the table fields into object variables. In that case it would be more accurate to call that Relational-Object Mapping - ROM .

Departing from the relational (database table) domain to the object domain is not a big issue for simple tables without relationships. However in real world applications you often have relationships represented by tables with fields that reference records in other database tables.

In some cases you have a reciprocal relationship between two database tables. For instance, in a content management system you may have authors that publish articles. If an article may be co-authored by several authors you have what is called a many-to-many relationship.

Since you do not know up-front how many authors an article may have, nor how many articles an author may publish, the usual solution for this problem is to use an additional intermediate table to represent the relationship between the authors and their articles.

If you depart from the object model, there is no such thing as intermediate tables. You just tell that the relationship between two classes is of many-to-many and the ORM implementation takes care of the complicated details such and creating and accessing intermediate tables.

Another issue is that some databases do not have all the native data types as in the object model supported by the programming language you use. For instance, in PHP you have boolean variables that can only have two values: true or false. In some databases there is no boolean data type. Often boolean data types are emulated by single character text fields that have two values: 'Y' and 'N', or '1' and '0', or 't' or 'f'.

If you use ORM library that departs from the relational domain and automatically guesses the types of values of each variable, what shall it do when it finds a text field with only 1 character of size? Is it a boolean or only an actual text field?

For these and other reasons, it is more convenient to use ORM tools that depart from the object domain, as it represents what the developer really has in mind. There is no room for ambiguity of data type guessing, nor the complexity of implementing relationships that require relational tricks such as using intermediate tables.

The problem of departing from the object domain is that sometimes it is not quite possible. In many cases you already have an application running with a given database table that you cannot change because it was modeled a long time before you started working with ORM tools.

For new projects using a tool or library that departs from the object domain is more recommended, as it takes good care of solving the object-relational issues without further complication.


- PHP ORM libraries and tools

There are many ORM solutions for PHP. Some of them are standalone tools, others are integrated in larger Web development frameworks, usually as implementations of the ActiveRecord.

ActiveRecord is design pattern described by software engineering guru Martin Fowler in his book Patterns of Enterprise Application Architecture published in 2002.

martinfowler.com/eaaCatalog/activeR ...

More recently ActiveRecord became a popular design pattern due to its implementation in the Ruby On Rails framework. Since this approach is to build a wrapper around each database table, it departs from the database domain.

Some frameworks try to solve the problems of departing from the database domain more or less well to minimize the complications mentioned above. Still some are not very efficient because they impose the overhead of querying the database server to determine the the names and types of each mapped database table.

There are also PHP ORM solutions that use the code generation approach, and others that mix ORM class libraries with the generative approach.

On the PHPClasses site there are some based ORM solutions in the Code Generation category page:

phpclasses.org/browse/class/90.html

If you search the site for object-relational you also find other ORM solutions not based on code generation:

phpclasses.org/search.html?words=ob ...

There are many other PHP ORM solutions in other sites, but since I do not use them, I will not recommend any one in particular. You may want to try those listed in this Wikipedia page about ORM solutions for different programming languages:

en.wikipedia.org/wiki/List_of_objec ...


- Metastorage: the ORM tool used to develop the PHPClasses site

I have been using an ORM tool to develop the PHPClasses site since 2002. That was when it became evident that I need to depart to a more productive development approach to implement new site features in a faster manner.

Since the intention was to accelerate the development of new site features, I decided to develop a code generation based tool that departs from the object domain to generate ORM classes. The tool is called Metastorage.

metastorage.net/

Metastorage generates ORM classes from a definition in a simple XML format that describes each class with variables, relationships and eventual validation rules.

The class definitions may also include a description of functions that I need that the class to have in order to implement my applications. Metastorage generates smart code for such functions. They only do what I really need.

Usually I need to have a function to save the objects that are created or modified. The classes may or may not have other types of functions that are useful in only some cases. When I do not need such types of functions, Metastorage does not generate them. This way Metastorage does not generate dumb code that is never used.

For instance, if I do not need a function to delete objects, I just do not request that type of function. The resulting generated ORM class will be as small as necessary.

Metastorage also supports its own Object Query Language - OQL. It is very similar in concept to SQL, but with an object oriented syntax. This way I can search for objects that match certain conditions, always thinking in terms of objects. This is good because certain OQL clauses can translate to very hairy SQL clauses that you would have an hard time to write manually.

For instance imagine you have two classes that represent parents and children in a room. If you want to search for all children whose parents are not in the room, in Metastorage OQL you just need to use a simple syntax like "object not in collection" which would translate to very complex join clauses in SQL.

Another advantage of using an OQL with a code generation tool like Metastorage is that you will find implementation errors early. Metastorage evaluates an OQL expression syntax at code generation time. If you express a condition that does not make sense, it will fail and tell you what the error is at compile time.

With handwritten SQL, you only find out the errors when the application executes the SQL clauses. You risk yourself to ship buggy code that may pass quality tests that do not cover all cases.

For instance, SQL lets you compare a regular integer field with a primary key integer field. For Metastorage, an integer is is an integer, but a primary key is a reference to an object. So the types do not match.

Any attempt to compare such types of expressions would fail at code generation time with Metastorage. The same goes for boolean variables that were mapped to single character text fields. Booleans are booleans, text is text, they can not mix.

Another interesting feature of Metastorage is the support for report data extraction classes. These are special types of classes that Metastorage can generate for extracting data for read only purposes.

For instance, imagine you want to send a newsletter to 1 million subscribers with records in your database. If you use a regular ORM tool, you probably execute a search query retrieve the objects that represent the newsletter subscribers.

That is not a good idea because bringing 1 million objects to memory would take too much memory to be feasible. To send a newsletter you probably only need the e-mail address, the subscriber name and maybe not much more.

With Metastorage generated report data extract classes you can tell it to generate a function that executes a simple query to retrieve only specific variables of objects of certain classes. The results are returned into arrays rather than complete objects with all the variables.

This is a much more efficient and feasible solution when dealing with operations that require processing large amounts of data for read only purposes, like sending newsletters, generating reports to show to the users, etc..

There would be a lot more to talk about Metastorage, as I already did in past articles, but it is outside of the scope of this article. Metastorage is an Open Source project. If you want to know more about Metastorage capabilities, feel free to visit the project page:

metastorage.net/

If you have other questions, comments, criticisms about what is written here on Object-Relational Mapping, feel free to post a comment to this article.