Need a simple ORM or DBAL for existing PHP app

Question

I am working on extending an existing PHP application. Unfortunately for me, the existing app is a mess. It's all spaghetti code with raw mysql_* calls. Groan. No way that I am going to do that in the parts that I am extending.

So, I am looking for a simple ORM of DBAL that I can easily drop in and start using. Desired features:

• It must work on an existing database schema. Preferably with minimal or no additional configuration. The existing database schema is the same quality as the existing PHP code (no sensible naming conventions, not normalised, etc.). I don't want to spend days converting the database schema manually into annotated object properties a la Doctrine 2.
• It must be able to work alongside the existing raw mysql_* queries. I have no idea how hydrating ORMs like Doctrine 2 or Propel behave when scripts are manually manipulating the data in the database behind their backs, but I assume it's not pretty.
• It must run on PHP 5.2.x. I'd love to use PHP 5.3 but I have zero interest in going over the existing 125K lines of spaghetti code mess to make sure it runs on PHP 5.3.
• Relationships not required. In the few places I need to get to relational data, I'll be happy to call an extra find() or query() or whatever myself.
• Bonus points if it has some trigger support (e.g. beforeSave, afterSave). Not a requirement, but just nice to have.

Edit: Someone put me out of my misery. I just found out that the 125K lines of spaghetti code also changes the database schema. E.g, add an extra option somewhere and a whole slew of ALTER TABLE statements start flying. I could probably fill a year's worth of TheDailyWTF with this codebase. So, one more requirement:

• Must be able to cope with a changing database schema automatically (e.g. adding columns).

I have been looking at a few solutions, but I am unsure how well they would work given the requirements. Doctrine 2, RedBeanPhp and the like all require PHP 5.3, so they are out. There's a legacy version of RedBeanPhp for PHP 5.2.x but I don't know if it would work with a messy, existing database schema. NotORM looks okay for getting data out but I don't know if it can be configured for the existing database schema, and how you can easily put data back into the database.

Ideally I would like something simple. E.g:

$user = User::find($id);
$user->name = 'John Woo';
$user->save();

Or:

$articles = ORM::find('article')->where('date' => '2010-01-01');
foreach ($articles as $article) {
    echo $article->name;
}

Any tips or even alternative solutions are welcome!

Terrel

Answer 1

I use... http://github.com/j4mie/idiorm/

it has an active record implementation too in the form of Paris.

With regard to your edit. Idiorm copes with changing schemas and the syntax almost exactly matches the type you want in your question.

Ruisdael

I have been isuing Idiorm and Paris in the application for a few weeks now and I absolutely love it! – Terrel

Answer 2

How well did you look into Doctrine? I am using Doctrine 1.2 for these kind of things. Quite easy to setup, allows you to start off with an existing schema. It automatically figures out the relations between tables that have foreign key constraints.

It has extensive trigger and behaviour support, so the bonus points can be spent as well, and it has relational support as well, so your additional queries are not necessary. It has beautiful lazy loading, and it comes with a flexible query language (called DQL) that allows you to do almost exactly the same stuff that you can do in SQL in only a fraction of the effort.

Your example will look like this:

/* To just find one user */
$user = Doctrine::getTable('User')->findOneById($id);

/* Alternative - illustrating DQL */
$user = Doctrine_Query::create()
    ->from('User u')
    ->where('u.id = ?',array($id))
    ->fetchOne();

$user->name = 'John Woo';
$user->save();

It must be able to work alongside the existing raw mysql_* queries. I have no idea how hydrating ORMs like Doctrine 2 or Propel behave when scripts are manually manipulating the data in the database behind their backs, but I assume it's not pretty.

Well, that is technically impossible to auto-manage; a SQL database is simply not pushing back stuff to your ORM, so to update stuff that was changed in the background, you need to perform an additional query one way or the other. Fortunately, Doctrine makes this very easy for you:

/* @var User $user */
/* Change a user using some raw mysql queries in my spaghetti function */
$this->feedSpaghetti($user->id);

/* Reload changes from database */
$user->refresh();

Academe

But what happens when some other part of the app, still using raw mysql_* calls, changes the data behind Doctrine's back? Or worse, when the schema changes underneath it's feet? Like I said, the app is 125K spaghetti code with a seriously denormalised database behind it. Assume the worst. Multiply by a factor 2. Can Doctrine handle that? – Terrel

Not sure if you already read my update. You do need to refresh the record, but hey, due to the nature of SQL, it will be like that in any ORM. If you are afraid of a Doctrine record getting outdated, simply refresh it, and its field values will be re-acquired from the database. – Academe