Page 1 of 1

Minimalist PDO ActiveRecord Implementation

Posted: Sat May 23, 2009 11:25 pm
by volomike
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.

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);
 
 

Re: Minimalist PDO ActiveRecord Implementation

Posted: Sat May 23, 2009 11:41 pm
by volomike
Some things have occurred to me since the last post:

- needs to handle nulls
- needs to handle serial/autonumber columns
- needs to handle default columns
- does it have the error handling it needs?
- will bindValue be enough to protect me from SQL injection?
- I probably should add a theLastSQL property to return the last SQL statement that was issued after save(), update(), or insert()
- will this work the same across MySQL5, PostgreSQL8.2+, and SQLite3, or need special logic for each platform?
- boolean results can act differently sometimes across the DB platforms -- I should try to make them act consistent, but not mess up strings that actually say 't', 'f', 'true', 'false', 'TRUE', or 'FALSE'.

Re: Minimalist PDO ActiveRecord Implementation

Posted: Sun May 24, 2009 8:18 am
by volomike
One might also ask, "Where's the .remove() or .delete()?" The reason I didn't include that is because the following code is illogical as the best opportunity to solve that need:

$oRec = $DB->getNewRecord('users','id = 4');
$oRec.delete();

Why would I do a SELECT when, if I know the table and the WHERE clause, just send an exec straight from PDO like:

// assume $DB is already established and connected to your database
$DB->exec('DELETE FROM users WHERE id = 4');

Therefore, I didn't include it.