Database iterators (or not?)

So while digging into the pagination helper class, I had the idea that I wanted almost every object that can iterate to be able to be passed to the helper, although in a real world its not that easy and will need another design for that, but the idea got me thinking and experimenting, so I cooked up initial support for iterating results for the MySQL drivers so far.

Consider the following example of how we currently iterate over rows:

<?php
	$result = $db->query('SELECT `id`, `name` FROM `persons`');

	if(!$result || !$result->getNumRows())
	{
		tuxxedo_error('There are no results to display');
	}

	while($row = $result->fetchArray())
	{
		printf('[%d] %s<br />', $row['id'], $row['name']);
	}
?>

The while expression is whats in focus here, with iterators that line can simply be replaced by:

<?php
	...

	foreach($result as $row)
	{
		...
	}

	...
?>

Which is a much clearer design and less keystrokes to achieve the same thing.

The issue

The issue that now stands is that some of the drivers that Engine supports does not natively expose an interface for implementing iterators without having to fetch all of the current data into a buffer and emulate it like that, which sucks.

Analyzing the issue a bit, the following table will show what the drivers natively expose of methods that can be used to achieve the iterator support without any form of emulation:

Which leads us to that next and reset is the methods that needs to be emulated, as the current method is exposed by all drivers (the fetching function/methods). Key and valid can be implemented in raw code thats using the database layer features to determine which, where, what.

Lets take a look at emulating, and what disadvantage there is when the drivers does not expose some of the key ways to achieve an iterator.

• Reset(): To achieve a reset can be done by checking if the pointer position, however since there is no reset method it cannot easily tell the driver that we want to return to position one of the result set.In PDO’s case, this is rather problematic as there is no way to return to the original position WITHOUT fetching all the data when the result object is created which will be costly.
• Next(): Next is in theory really straight forward due to the fact that each fetch call advances to the next row in the result, however it needs some analyzing before we can be 100% sure this method will effectively work. By relying on fetch to advance to the next row we also eliminate the need to use the pointer position to point to a specific row and possible mismatches might occur on this phase.In a worst case scenario all the data would need to be fetched when the result object is created, and use the same pattern as the reset method for PDO, which is why this option will make a lot of sense for PDO and perhaps SQLite.

Both have a huge downside, which is the fact that we “may”, if not “have” to allocate that additional memory just to gain cross driver support for a specific feature because we need to emulate it to make the design complete.

Conclusion

So in the end, is it really worth slowing down two drivers due to this “nifty” feature? While I think its debatable, due to the major code changes those two driver result classes have to overcome to gain support for this and using the ability can allow us to extend this emulation to the fetch methods too. I think I will take some time to think over the situation and implement/remove accordingly.

But wait, there is more…

So I forgot to over one item, that is the fetch mode. I want to change the database drivers to have a setFetchMode method available on either the result class or both the result and connection class (meaning that results will inherit the connection ones for a default) and making the fetch() method non private and exposed to everyone.

This change was born when the iterator idea was, as there was no possible way to tell how you wanted the rows to be returned when iterating, the first idea was to hack __invoke() in a way like this:

<?php
	foreach($result(FETCH_OBJECT) as $row)
	{
		echo $row->id;
	}
?>

Bear with me that the FETCH_OBJECT is just a random, undecided name of a fetching constant at this moment, but I think the way of having a setFetchMethod serves the whole concept much better and seems less hacky.