Rolling Your Own ORM
Posted: Thu Dec 04, 2008 12:01 pm
You know, it dawned on me the other day, using PHP5's OOP features with: __construct(), __call(), call_user_func_array(), __get(), and __set(), one could build their own ORM class in a really cool way. It would look like:
Example # 1
This would automatically build an empty record object with no properties, and then fill it with 3 columns, convert the column names from property-style to db-style, determine if we need to use an INSERT or an UPDATE based on the where clause we pass in Save(), and then run the SQL. It could also handle checks on the database field values to ensure they are not catching any SQL injection. Also, it would ensure that the UPDATE or INSERT only used applicable columns that it had been set -- a key point in the design. The Unquote() call could ensure that ID is not enclosed with single quotes around it (where, by default, everything would get quotes unless you did this). The 'GENERATE_ID' could be automatically detected by the __set() call and it could replace it with a unique primary key value pulled from a PostgreSQL Sequence, or whatever you want on MySQL, or whatever -- a unique ID.
The __set() routine would also have to be smart and throw an error if one tries to set a property that is being used as a reserved class method like Save(). Although I haven't tested -- perhaps PHP handles that on its own.
or
Example # 2
This would automatically build a users record for the row with ID = 433. One could then either update this with Save() or just roll through the properties on it with a foreach($oRecord as $sKey => $sVal), and doing an if/then check on whether the value is an array (such as for a property collection variable that we don't want to misinterpret as a column name).
or
Example # 3
This would automatically build a users record for the row with ID = 433, overwrite the FirstName with a new value, and save it back to the same record in the database table. However, since we created a new ORM object with a table designation and a row, the UPDATE statement in this case has every column and is unlike the first example.
You could also add class methods like Remove(), Update(), Insert(), and Select() to provide even more control.
Another opportunity could be to handle multiple records like so:
Example # 4
Some other things to think about -- with PostgreSQL and MySQL, there are tables you can inspect, or PHP functions you can call, to determine whether columns are going to need a single quote or not. Therefore, in examples 2, 3, and 4, you wouldn't need to run Unquote() because it would already know. Of course, that has a performance hit, so you could perhaps have a parameter you could pass to turn that off, or you could cache that information in shared memory.
And here's some handy functions I wrote that convert object Property Names to/from DB Column Names, and which are used when calling __construct(), Save(), Remove(), Update(), Insert(), or Select() (-- another key point). Note these change the original value and don't return a value, saving you a step.
You could play with those column translation functions as you see fit, using your own tastes.
P.S. Also, some newbies who read this might not know you can usually convert a database record or an array into an object like so:
$oRecord = (object) array('FirstName' => 'Volo', 'LastName' => 'Mike');
and
$oRecord = (object) $rwRow;
..which might be useful in your ORM design.
Example # 1
Code: Select all
$oRecord = new ORM();
$oRecord->FirstName = 'Volo';
$oRecord->LastName = 'Mike';
$oRecord->ID = 'GENERATE_ID';
$oRecord->Unquote('ID');
$oRecord->Save('users','id = ' . $oRecord->ID);The __set() routine would also have to be smart and throw an error if one tries to set a property that is being used as a reserved class method like Save(). Although I haven't tested -- perhaps PHP handles that on its own.
or
Example # 2
Code: Select all
$oRecord = new ORM('users','id = 433');or
Example # 3
Code: Select all
$oRecord = new ORM('users', 'id = 433');
$oRecord->FirstName = 'Mike';
$oRecord->Save('users','id = 433');You could also add class methods like Remove(), Update(), Insert(), and Select() to provide even more control.
Another opportunity could be to handle multiple records like so:
Example # 4
Code: Select all
$oRecords = new ORM('users',"last_name = 'Smith'", MULTIPLE);
foreach ($oRecords as $oRecord) {
// do something with properties or class methods of $oRecord
}And here's some handy functions I wrote that convert object Property Names to/from DB Column Names, and which are used when calling __construct(), Save(), Remove(), Update(), Insert(), or Select() (-- another key point). Note these change the original value and don't return a value, saving you a step.
Code: Select all
public function MakeObjectKeyName(&$sKey) {
$sKey = strtolower($sKey);
$sKey = str_replace('fkey_','fk_',$sKey);
$sKey = str_replace('dt_','Date_',$sKey);
$sKey = str_replace('_',' ',$sKey);
$sKey = ucwords($sKey);
$sKey = str_replace('Ip ','IP ',$sKey);
$sKey = str_replace(' Ip',' IP',$sKey);
$sKey = str_replace('name','Name',$sKey); //not a bug -- I conjoin name this way usually
$sKey = str_replace(' Id',' ID',$sKey);
$sKey = str_replace('Id ','ID ',$sKey);
$sKey = str_replace(' ','',$sKey);
$sKey = ($sKey == 'Id') ? 'ID' : $sKey;
$sKey = str_replace('Fk','FK',$sKey);
}
public function MakeDBKeyName(&$sKey) {
foreach (range('A','Z') as $c) {
$sKey = str_replace($c, "_$c",$sKey);
}
$sKey = str_replace('_F_K_','_fkey_',$sKey);
$sKey = str_replace('_I_D_','_id_',$sKey);
$sKey = str_replace('_I_D','_id',$sKey);
$sKey = str_replace('_I_P_','_ip_',$sKey);
$sKey = str_replace('_I_P','_ip',$sKey);
$sKey = str_replace('_Name','name',$sKey); //not a bug -- I conjoin name this way usually
$sKey = str_replace('_Date_','_dt_',$sKey);
$sKey = str_replace('_Date','_Date',$sKey);
if (strpos($sKey, '_') == 0) {
$sKey = substr($sKey, 1);
}
$sLast = substr($sKey, -1);
if ($sLast == '_') {
$sKey = strrev($sKey);
if (strpos($sKey, '_') == 0) {
$sKey = substr($sKey, 1);
}
$sKey = strrev($sKey);
}
$sKey = strtolower($sKey);
}P.S. Also, some newbies who read this might not know you can usually convert a database record or an array into an object like so:
$oRecord = (object) array('FirstName' => 'Volo', 'LastName' => 'Mike');
and
$oRecord = (object) $rwRow;
..which might be useful in your ORM design.