Minimalist PDO ActiveRecord Implementation
Posted: Sat May 23, 2009 11:25 pm
I've written a minimalist PDO ActiveRecord implementation. If you think I've made a huge blunder here, or have constructive criticism, please share. Thanks.
Usage:
It's fairly straight forward but with a few minor catches. The class creates a $DB object that is basically a PDO object, but also includes a getRecord method that outputs an ActiveRecord. The ActiveRecord has properties for your table columns, and then the class methods .save(), .insert(), and .update(). It also supports the public property theRecord which returns an array of values that have been set upon it. Note that when calling getRecord() you can either call it without parameters, which is useful for an UPDATE statement, or with a table parameter only, which is useful for an INSERT statement, or call with both a table and a where clause parameter, which is useful for an UPDATE or SAVE statement under certain conditions. Calling with a table means that the record object will only have the column name parameters. Calling with a table and a where clause means that the record will have column names + actual row values from the table. Calling with neither means it's an empty object, so no metadata about your table.
A demo:
$oRec = $DB->getRecord();
$oRec->ID = 4;
$oRec->FirstName = 'Volo';
$oRec->LastName = 'Mike';
$oRec->update();
And here's the other catch -- note the private methods that translate names. Thus, in the database I might have column names: id, fkey_user_id, dt_modified, first_name, and last_name, but the object property names become ID, FKUserID, DateModified, FirstName, and LastName. If you read those functions, you kind of get the hang of it and you'll note that I don't deviate that much from the norm on column names in the database.
Usage:
It's fairly straight forward but with a few minor catches. The class creates a $DB object that is basically a PDO object, but also includes a getRecord method that outputs an ActiveRecord. The ActiveRecord has properties for your table columns, and then the class methods .save(), .insert(), and .update(). It also supports the public property theRecord which returns an array of values that have been set upon it. Note that when calling getRecord() you can either call it without parameters, which is useful for an UPDATE statement, or with a table parameter only, which is useful for an INSERT statement, or call with both a table and a where clause parameter, which is useful for an UPDATE or SAVE statement under certain conditions. Calling with a table means that the record object will only have the column name parameters. Calling with a table and a where clause means that the record will have column names + actual row values from the table. Calling with neither means it's an empty object, so no metadata about your table.
A demo:
$oRec = $DB->getRecord();
$oRec->ID = 4;
$oRec->FirstName = 'Volo';
$oRec->LastName = 'Mike';
$oRec->update();
And here's the other catch -- note the private methods that translate names. Thus, in the database I might have column names: id, fkey_user_id, dt_modified, first_name, and last_name, but the object property names become ID, FKUserID, DateModified, FirstName, and LastName. If you read those functions, you kind of get the hang of it and you'll note that I don't deviate that much from the norm on column names in the database.
Code: Select all
<?php
class ActiveRecord {
public $theRecord = array();
private $_Table = '';
private $_Where = '';
private $_hDB;
public function __construct(&$oDB, $sTable = '', $sWhere = '') {
if ((!empty($sTable)) and (!empty($sWhere))) {
$rsRows = $oDB->query("SELECT * FROM $sTable WHERE $sWhere LIMIT 1");
foreach ($rsRows as $rwRow) {
foreach ($rwRow as $sKey => $sVal) {
if (!is_numeric($sKey)) {
$sKey = $this->_getObjectKeyName($sKey);
$this->theRecord[$sKey] = $sVal;
}
}
break;
}
} elseif (!empty($sTable)) {
$rsRows = $oDB->query("SELECT * FROM $sTable LIMIT 1");
foreach ($rsRows as $rwRow) {
foreach ($rwRow as $sKey => $sVal) {
if (!is_numeric($sKey)) {
$sKey = $this->_getObjectKeyName($sKey);
$this->theRecord[$sKey] = '';
}
}
break;
}
}
$this->_Table = $sTable;
$this->_Where = $sWhere;
$this->_hDB = $oDB;
}
private function _getObjectKeyName(&$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('Url ','URL ',$sKey);
$sKey = str_replace(' Url',' URL',$sKey);
$sKey = str_replace('name','Name',$sKey); //not a bug -- I conjoin name usually
$sKey = str_replace(' Id',' ID',$sKey);
$sKey = str_replace('Id ','ID ',$sKey);
$sKey = str_replace(' ','',$sKey);
$sKey = ($sKey == 'Id') ? 'ID' : $sKey;
$sKey = ($sKey == 'Ip') ? 'IP' : $sKey;
$sKey = ($sKey == 'Url') ? 'URL' : $sKey;
$sKey = str_replace('Fk','FK',$sKey);
$sKey = ($sKey == 'UserName') ? 'Username' : $sKey;
return $sKey;
}
private function _getDBKeyName(&$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('_U_R_L_','_url_',$sKey);
$sKey = str_replace('_U_R_L','_url',$sKey);
$sKey = str_replace('_Name','name',$sKey); //not a bug -- I separate name 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);
return $sKey;
}
public function save() {
$sTable = $this->_Table;
$sWhere = $this->_Where;
if (empty($sWhere)) {
$nCount = 0;
} else {
$sSQL = "SELECT COUNT(*) AS counted FROM $sTable WHERE $sWhere;";
$rsRows = $this->_hDB->query($sSQL);
foreach ($rsRows as $rwRow) {
$nCount = $rwRow['counted'];
break;
}
}
if ($nCount > 0) {
return $this->update();
} else {
return $this->insert();
}
}
public function update() {
$sTable = $this->_Table;
$sWhere = $this->_Where;
if ((empty($sTable)) or (empty($sWhere))) {
throw new Exception('Missing table name or WHERE clause in UPDATE SQL.');
}
$s = '';
foreach ($this->theRecord as $sKey => $sVal) {
$sKey = $this->_getDBKeyName($sKey);
$s .= $sKey . '=:' . $sKey . ',';
}
$s = substr($s, 0, -1);
$sSQL = "
UPDATE
$sTable
SET
$s
WHERE
$sWhere;
";
$st = $this->_hDB->prepare($sSQL);
foreach ($this->theRecord as $sKey => $sVal) {
$sKey = $this->_getDBKeyName($sKey);
$st->bindValue(':' . $sKey, $sVal);
}
return $st->execute();
}
public function insert() {
$sTable = $this->_Table;
if (empty($sTable)) {
throw new Exception('Missing table name in INSERT SQL.');
}
$s1 = ''; $s2 = '';
foreach ($this->theRecord as $sKey => $sVal) {
$sKey = $this->_getDBKeyName($sKey);
$s1 .= '' . $sKey . ',';
$s2 .= ':' . $sKey . ',';
}
$s1 = substr($s1, 0, -1);
$s2 = substr($s2, 0, -1);
$sSQL = "
INSERT INTO $sTable (
$s1
) VALUES (
$s2
);
";
$st = $this->_hDB->prepare($sSQL);
foreach ($this->theRecord as $sKey => $sVal) {
$sKey = $this->_getDBKeyName($sKey);
$st->bindValue(':' . $sKey, $sVal);
}
return $st->execute();
}
public function __set($var, $val){
$this->theRecord[$var] = $val;
}
public function __get($var){
if(isset($this->theRecord[$var])){
return $this->theRecord[$var];
} else {
throw new Exception("Property '$var' does not exist");
}
}
} //end ActiveRecord class
class DBClass extends PDO {
public function getRecord($sTable = '', $sWhere = '') {
$oRecord = new ActiveRecord($this, $sTable, $sWhere);
return $oRecord;
}
} //end DBClass
global $Settings;
global $DB;
// THIS INSTANTIATES THE CLASS SIMPLY BY INCLUDING THE SCRIPT, AS WELL AS CONNECTS THE DATABASE
$DB = new DBClass ($Settings->DSN);