Daily Archives: September 21, 2008

Reinventing the Wheel – Stone-Simple PHP Object Relational Mapping (ORM)

Man is it fun to build stuff from scratch. About noon yesterday I was starting to write too much SQL – I have a user table that looked as shown below and I had a request object (also shown below) with lots of parameters that needed to go into the table. And I was going to have to this over and over for users, courses, tools, institutions, etc etc — Aargh. I love creative complex SQL – but Mundane SQL is not fun at all – because it is bug prone.

Friday night I asked Noah which PHP ORM he liked – we went through about 8 existing PHP ORMs (mostly over-imitating Rails) over IM and SMS. Each one he brought up – I decided it just sucked because it was too complex and required me to hack my PHP installation so bad and change my development style too much. What I wanted was effectively 80% of the features of ActiveRecord with one single PHP file that I would require_once at the beginning of my file and I could hack SQL when I wanted and use the ORM to do the nasty, dirty work.

So in the past 24 hours I developed my own PHP ORM – here are the features:

  • It does not need any hacking of PHP
  • It is unit-tested – using the unit test framework I built yesterday
  • It is one simple PHP file that you include in your source code
  • It uses ActiveRecord conventions (id, created_at, updated_at), etc.
  • It is infinitely hackable – you can step right around it if you like
  • It did foreign keys like ActiveRecord institution_id
  • It required ZERO configuration except for the table existing with some columns – no XML configuration – zip zero none – nada
  • It adapts to changes in the table trivially – if you add a column – the object knows about the column.

So here is a bit of sample code to use my ORM:

require_once("orm.php");
$usr = new ORM("user", "userid", "lti_user");
$userid = $_REQUEST[user_id];
$usr->read($userid);
$usr->setall($_REQUEST, '/^user_/');
if ( $usr->id() ) {
$usr->update();
} else {
$usr->insert($userid);
}

It is pretty Rails-like – the read() either loads the existing record or not. The setall() takes an associative array of key/value pairs (optionally limited by a regex) and updates all the fields in the object. The setall() has optional soft field matching (on by default) so the request fields don’t have to be identical to the database fields. If the object was loaded, id() exists so we update() otherwise we insert(). On the second and following ORM() call the key and table name are not needed. You can also load the ORM prior to the construction of the first model for error checking earlier.

The ORM code is 352 lines of PHP with a 152 line unit test. There is a little more work to do. But for now it works well enough for me to get back to writing my IMS LTI application without building large amounts of nasty, repetitive SQL.

The beauty of Rails and ActiveRecord is that it shows us how to build an ORM – in a sense – the ORMs of the future can assume all the thinking that went into Rails and then find new, cooler places to go – and perhaps to do so cleaner and better – and further evolve the breed.

Here is the table:

create table lti_user (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
userid CHAR(255) NOT NULL,
eid CHAR(255) NULL,
displayid CHAR(255) NULL,
password CHAR(255) NULL,
firstname CHAR(255) NULL,
lastname CHAR(255) NULL,
email CHAR(255) NULL,
locale CHAR(255) NULL,
institution_id MEDIUMINT NULL,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
PRIMARY KEY (id)
);

Here is a dump of the request object:

Request Dump
Array
(
[action] => direct
[sec_digest] => 8PCloWWp4brpvuSylfyn8SaoTF4=
[sec_org_digest] => 8PCloWWp4brpvuSylfyn8SaoTF4=
[sec_nonce] => f6348bcf-87f8-11dd-b7d8-552f83eeb0b4
[sec_created] => 2008-09-21T16:18:44Z
[sec_secret] => secret
[user_id] => 0ae836b9-7fc9-4060-006f-27b2066ac545
[user_role] => Student
[user_displayid] => csev
[user_firstname] => Charles
[user_lastname] => Severance
[user_fullname] => Charles Severance
[user_email] => csev@umich.edu
[user_locale] => en_US
[user_roster] => SI300-010-F08
[course_id] => 8213060-006f-27b2066ac545
[course_code] => SI300-001-F08
[course_name] => SI300
[course_title] => Social Computing
[org_id] => umich.edu
[org_title] => University of Michigan (CTools)
[org_name] => UMich
[org_url] => https://ctools.umich.edu
[launch_resource_id] => 27b2066ac545
[launch_targets] => widget,post,iframe
[launch_resource_url] => http://www.dr-chuck.com/
[launch_tool_id] => sakai.lti.168
[launch_tool_name] => Video
[launch_tool_title] => Video Review for Midterm
[launch_width] => 320
[launch_height] => 240
[camtoolspref] =>
)