Page 1 of 1

PDO Iterator

Posted: Sun Jul 27, 2008 7:24 pm
by allspiritseve
I wanted to make a PDO Iterator that iterates statements. However, I just found out that MySQL doesn't support scrolling cursors... I sort of made a hack, that remembers the rows so you can still iterate through them multiple times. If anyone can suggest a better approach, I'd love to hear it.

Code: Select all

class PDOIterator implements Iterator   {
 
private $stmt;
private $cursor = -1;
private $valid = true;
private $array = array();
    
function __construct ($stmt)    {
    $this->stmt = $stmt;
    $this->next();
}
    
function current()  {   
    return $this->array[$this->cursor];
}
 
function next() {   
    $this->cursor++;
    if (empty ($this->array[$this->cursor])):
        $row = $this->stmt->fetch (PDO::FETCH_ASSOC);
        if (empty ($row)):
            $this->valid = false;
        else: 
            $this->array[$this->cursor] = $row;
        endif;  
    endif;  
}
 
function key()  {
    return $this->cursor;
}
 
function valid()    {
    return $this->valid;
}
 
function rewind()   {
    $this->cursor = 0;
}
 
}

Re: PDO Iterator

Posted: Mon Jul 28, 2008 10:29 am
by inghamn
My PDOResultIterator is very similar, just tailored for my environment. One thing I'd love to figure out is a way to do prepared statements using it. The biggest difference is I go ahead and populate the internal array all at once from the very start, rather than trying to muck around with cursors.

I ended up building a PDO Iterator as well for my framework stuff. However, I built it to work in hand with the ActiveRecord style models I'm using. Before I clutter this up with code, some info on the models. The ActiveRecord style I went with means I also need Collection classes to handle the aggregrating a bunch of ActiveRecord objects. It allowed for code that seemed to make the most sense, to me at least.

Code: Select all

 
$user = new User(32);
$user->setEmail($somestring);
$user->save();
 
 
$users = new UserList(array('department_id'=>$deptID));
foreach($users as $user)
{
    echo $user->getUsername();
}
 

To me, it made sense to use a collection type object to get a bunch of single users. Rather than using the User class to look up multiple users. What this means is I needed a PDOResultIterator that the collection classes could use.

The PDOResultIterator assembles the query, handles the iterating, and caches results. But calls a callback function for hydrating each row of the results. The PDOResultIterator calls the loadResult() function with the Index of the internal array that contains the result.

The collection classes are responsible for providing their version of the callback. 99% of the time, the collection classes just set up to select ID fields from the database. Then, their callback function takes each row from PDOResultIterator and returns a new object from that ID.

The PDOResultIterator takes this object, caches it in the array and returns it.

Code: Select all

 
abstract class PDOResultIterator implements ArrayAccess,SeekableIterator,Countable
{
    protected $select = "";
    protected $joins = "";
    protected $where = "";
    protected $sort = "";
    protected $limit = "";
    protected $groupBy = "";
    private $sql;
 
    protected $list = array();
    private $valid = false;
    private $cacheEnabled = true;
    private $cache = array();
    private $key;
 
    protected function populateList(array $options=null)
    {
        $PDO = Database::getConnection();
 
        # Make sure to clear out any previous list that was created
        $this->list = array();
 
        if (count($options)) { $this->where = ' where '.implode(' and ',$options); }
        $orderBy = $this->sort ? "order by {$this->sort}" : '';
        $groupBy = $this->groupBy ? "group by {$this->groupBy}" : '';
        $limit = $this->limit ? 'limit '.$this->limit : '';
        $this->sql = "{$this->select} {$this->joins} {$this->where} $groupBy $orderBy $limit";
 
        $result = $PDO->query($this->sql);
        if ($result)
        {
            foreach($result as $row) { $this->list[] = $row['id']; }
        }
        else { $e = $PDO->errorInfo(); throw new Exception($this->sql.$e[2]); }
    }
 
    public function getPagination($pageSize) { return new Paginator($this,$pageSize); }
 
    abstract public function find($fields=null,$sort='',$limit=null,$groupBy=null);
    abstract protected function loadResult($key);
 
    # Array Access section
    public function offsetExists($offset) { return array_key_exists($offset,$this->list); }
    public function offsetSet($offset,$value) { } # Read-only for now
    public function offsetUnset($offset) { } # Read-only for now
    public function offsetGet($offset)
    {
        if ($this->offsetExists($offset))
        {
            if ($this->cacheEnabled)
            {
                if (!isset($this->cache[$offset])) { $this->cache[$offset] = $this->loadResult($offset); }
                return $this->cache[$offset];
            }
            else { return $this->loadResult($offset); }
        }
        else { throw new OutOfBoundsException('Invalid seek position'); }
    }
 
 
 
    # Iterator Section
    public function rewind() { $this->key = 0; }
    public function next() { $this->key++; }
    public function key() { return $this->key; }
    public function valid() { return array_key_exists($this->key,$this->list); }
    public function current()
    {
        if ($this->cacheEnabled)
        {
            if (!isset($this->cache[$this->key])) { $this->cache[$this->key] = $this->loadResult($this->key); }
            return $this->cache[$this->key];
        }
        else { return $this->loadResult($this->key); }
    }
    public function seek($index)
    {
        if (isset($this->list[$index])) { $this->key = $index; }
        else { throw new OutOfBoundsException('Invalid seek position'); }
    }
 
    public function getIterator() { return $this; }
 
    # Countable Section
    public function count() { return count($this->list); }
 
    # Getters
    public function getSelect() { return $this->select; }
    public function getJoins() { return $this->joins; }
    public function getWhere() { return $this->where; }
    public function getGroupBy() { return $this->groupBy; } # IUB Libraries - Added getter for groupBy
    public function getSort() { return $this->sort; }
    public function getLimit() { return $this->limit; }
    public function getSQL() { return $this->sql; }
 
    # Cache Enable/Disable functions
    public function enableCache() { $this->cacheEnabled = true; }
    public function disableCache() { $this->cacheEnabled = false; }
}
 
And here's an example UserList collection class utilizing the PDOResultIterator. (The callback function loadResult() is down at the bottom)

Code: Select all

 
class UserList extends PDOResultIterator
{
    public function __construct($fields=null)
    {
        $this->select = "select distinct users.id from users";
        if (is_array($fields)) $this->find($fields);
    }
 
    public function find($fields=null,$sort="username",$limit=null,$groupBy=null)
    {
        $this->sort = $sort;
        $this->limit = $limit;
        $this->groupBy = $groupBy;
 
        $options = array();
        if (isset($fields['id'])) { $options[] = "id='$fields[id]'"; }
        if (isset($fields['username'])) { $options[] = "username='$fields[username]'"; }
        if (isset($fields['password'])) { $options[] = "password='$fields[password]'"; }
        if (isset($fields['authenticationMethod'])) { $options[] = "authenticationMethod='$fields[authenticationMethod]'"; }
        if (isset($fields['firstname'])) { $options[] = "firstname='$fields[firstname]'"; }
        if (isset($fields['lastname'])) { $options[] = "lastname='$fields[lastname]'"; }
        if (isset($fields['department_id'])) { $options[] = "department_id='$fields[department_id]'"; }
 
        # To search on Role, you have to join the userRoles table
        if (isset($fields['role']))
        {
            $this->joins.= ' left join user_roles on users.id=roles.user_id left join roles on role_id=roles.id';
            $options[] = "role='$fields[role]'";
        }
 
        if (isset($fields['plan_id']))
        {
            $this->joins.= ' left join plan_users on users.id=plan_users.user_id';
            $options[] = "plan_id='$fields[plan_id]'";
        }
 
        $this->populateList($options);
    }
 
    protected function loadResult($key) { return new User($this->list[$key]); }
}
 

Re: PDO Iterator

Posted: Mon Jul 28, 2008 10:41 am
by allspiritseve
inghamn wrote:My PDOResultIterator is very similar, just tailored for my environment.
I'm using data mappers, and I just have them return iterators instead of having the iterators deal with any SQL. I think it makes things a bit simpler.
inghamn wrote:One thing I'd love to figure out is a way to do prepared statements using it.
PDO::query returns a PDOStatement object. You should be able to use prepared statements in the same way, after they've been executed.

Re: PDO Iterator

Posted: Mon Jul 28, 2008 12:55 pm
by inghamn
allspiritseve wrote: I'm using data mappers, and I just have them return iterators instead of having the iterators deal with any SQL. I think it makes things a bit simpler.
Is it all your own code, or are you using something like Propel or Doctrine?

allspiritseve wrote: PDO::query returns a PDOStatement object. You should be able to use prepared statements in the same way, after they've been executed.
Right now my models are setting up parts of the SQL. Some of which can be done using prepare-execute, some have to be escaped. Doing all that in the models is a pain, and doing it in the PDOResultIterator is, well, complicated enough I haven't figured a good way to do it.

Using something like Propel or Doctrine as a database layer would get rid of the need for my PDOResultIterator, at the expense of a lot more complexity, I think.

Re: PDO Iterator

Posted: Mon Jul 28, 2008 1:09 pm
by allspiritseve
inghamn wrote:Is it all your own code, or are you using something like Propel or Doctrine?
It's all my own code.
inghamn wrote:Right now my models are setting up parts of the SQL. Some of which can be done using prepare-execute, some have to be escaped. Doing all that in the models is a pain, and doing it in the PDOResultIterator is, well, complicated enough I haven't figured a good way to do it.

Using something like Propel or Doctrine as a database layer would get rid of the need for my PDOResultIterator, at the expense of a lot more complexity, I think.
I assume by models you mean your domain objects which are active records. I much prefer the data mapper, where all of the persistence logic (anything to do with SQL) is extracted to a separate service object. here's a quick example:
class UserMapper {

function save (User $user) {
if ($user->persisted()):
return $this->update ($user);
else:
return $this->insert ($user);
endif;
}

function update (user $user) {
$stmt = $db->prepare ('UPDATE users SET ...');
$stmz->bindValue (':name', $user->getName();
// ...
return $stmt->execute();
}

function getAll() {
$stmt = $db->prepare ('SELECT * FROM users WHERE ... ');
// ...
return new PDOIterator ($stmt);
}

function getById ($id) {
$stmt = $db->prepare ('SELECT * FROM users WHERE id = :id');
$stmt->bindValue (':id', $id);
$stmt->execute();
return new User ($stmt->fetch (PDO::FETCH_ASSOC));
}

// etc...

}
I think this makes so much more sense than activerecord... why should you ask a user to save itself? This makes much more sense to me:
$userMapper = new UserMapper ($db);
$user = $userMapper->create();
$user->setName ('Cory');
$userMapper->save ($user);

Re: PDO Iterator

Posted: Mon Jul 28, 2008 1:41 pm
by Christopher
allspiritseve wrote:I think this makes so much more sense than activerecord... why should you ask a user to save itself? This makes much more sense to me:
I don't think one pattern makes more "sense" than the other. They are different and you may prefer one for technical or personal reasons.

One answer to your question is that because ActiveRecord (and the Gateway patterns) have their own save method, you can pass them around and do not also need to DataMapper class available when doing the save. This can simplify the solution to some problems.

The obvious next step in OR/M is to have the Datamapper internally track instances of the objects to be persisted and automatically save if necessary when the script ends (or some other event). Then the explicit $m->save($object) is no longer needed.

Re: PDO Iterator

Posted: Mon Jul 28, 2008 1:56 pm
by allspiritseve
arborint wrote:I don't think one pattern makes more "sense" than the other. They are different and you may prefer one for technical or personal reasons.
I guess I meant in terms of what better models the real world... rather than what makes more sense for an application.
arborint wrote:The obvious next step in OR/M is to have the Datamapper internally track instances of the objects to be persisted and automatically save if necessary when the script ends (or some other event). Then the explicit $m->save($object) is no longer needed.
I don't know, I guess that's a point where I feel ORMs are doing too much... an identity map, maybe, but I prefer the explicit save. Off the top of my head, I could think of creating a domain object that then validating it-- if validation fails, would the ORM still save that object? I wouldn't know until I implemented it... and if it wouldn't, that would mean the ORM had some knowledge of the controller's layer, which in my opinion there should be a clear separation there.

Re: PDO Iterator

Posted: Mon Jul 28, 2008 2:21 pm
by Christopher
allspiritseve wrote:I don't know, I guess that's a point where I feel ORMs are doing too much... an identity map, maybe, but I prefer the explicit save.
I don't think one solution is right for all problems. Active Record, Table Data Gateway, Data Mapper and full OR/M are all a good solution to some problems and "doing too much" for others.
allspiritseve wrote:Off the top of my head, I could think of creating a domain object that then validating it-- if validation fails, would the ORM still save that object? I wouldn't know until I implemented it... and if it wouldn't, that would mean the ORM had some knowledge of the controller's layer, which in my opinion there should be a clear separation there.
One question is whether Model validation should be in the Controller. I have been persuaded over the past few years by people I respect that perhaps validation is better in the Model. It certainly has seems cleaner to me in many situations.

Re: PDO Iterator

Posted: Mon Jul 28, 2008 2:33 pm
by allspiritseve
arborint wrote:I don't think one solution is right for all problems. Active Record, Table Data Gateway, Data Mapper and full OR/M are all a good solution to some problems and "doing too much" for others.
I agree... However, I have a preference for gateways and mappers, hence "I feel" and "I prefer".
arborint wrote:One question is whether Model validation should be in the Controller. I have been persuaded over the past few years by people I respect that perhaps validation is better in the Model. It certainly has seems cleaner to me in many situations.
Really? I have been persuaded by people I respect that validation belongs in the controller ;)