[TYPO3-dev] DB query iterator; worth a try?

[Jigal van Hemert]

Christian Kuhn wrote:
> For the db api:
> Imho, just adding a core class that supports iterators is not enough 
> (and you could do this in an library extension or in an extension 
> repository / model layer). Maybe it's time to think of on how we can 
> create a better structure in the long run.

That is an interesting point. A better db API can certainly be useful 
for v.4. There are certainly ways to replace the old API with a new one 
and move the old API to an optional system extension.

> My problem:
> We added TRUNCATEtable() and insertMultipleRows() to t3lib_db for 4.4, 
> subSELECT() with 4.3, all are nice to have. And, thanks to Xavier, dbal 
> is on a new level.

The implementation of DBAL which means rewriting a MySQL query is in 
itself a suboptimal way.
If the 'A' means 'Abstraction' then you might expect an API which does 
not involve building actual queries in code outside the core. In many 
cases this would mean a severe limitation on what can be done with the 
database.
On the other end of the spectrum you could leave everything to the code 
which calls the API. This freedom makes it virtually impossible to use 
other DBMS-s

> But, as Dmitry mentioned in other threads, we're really bloating this 
> API. While I'm +1 for those methods, I'm against further arbitrary 
> methods that might be neat (like a new more sophisticated delete method 
> that that supports joins), because they are a) more or less bound to 
> mysql and b) they make t3lib_db in its current incarnation even more ugly.

That's what happens when you want a cross-DBMS API and support for 
zillions of features. Not to blame anyone; it's just the consequence of 
a choice.

It's funny that you mention the delete method which supports joins. Here 
you clearly see the problem with cross-DBMS APIs: MySQL supports 
(multi-table) DELETE queries with JOINs, while Oracle (IIRC) doesn't 
support multi-table DELETE queries and prefers subqueries.

> Here are some needs for a future solution. This is a brainstorming and 
> it's mixing up different layers:
> - be extensible, eg. with an api to register own handlers for special 
> query needs, no need for xclasses

In this case XCLASSes are an excellent solution in my eyes. > 99.5% of 
the TYPO3 installations uses only a MySQL database. Any code to do with 
hooks, handlers, etc. is only ballast for MySQL only installations. An 
XCLASS is a pretty clean solution, especially because DBAL is maintained 
along with the core. So if the original code is enhanced/modified the 
XCLASS versions are modified accordingly.

> - still fully cross-db compatible
> - have a better oo interface supporting eg. iterators
> - get a simple and clean api, which is straight to use and easy to 
> unterstand
> - still have build-in goodies like quoting, which proofed to make so 
> many extensions and core more secure
> - support things like views or stored procedures

This list is covered by PDO. We could use a DBAL system extension to 
handle oddities such as multi-DBMS installations, support for short 
table names (e.g. Oracle), etc.

> - don't loose performance

This needs testing and profiling.

> - and, as always, be fully backwards compatible

Why? If the new API has enough advantages it will be used. After some 
time the old API can be moved to an optional extension.
The new API can also be offered in an extension for older TYPO3 versions.

> Any ideas on how something like that could be achieved? I know on the 
> other hand, if we want real abstraction on model / repository level, it 
> would probably be wiser to use extbase for such a task anyway. But that 
> doesn't solve basic db needs in our low-level t3lib_db.

We could have both, but that would mean an even more bloated API...

Just brainstorming too :-)

-- 
Jigal van Hemert
skype:jigal.van.hemert
msn: jigal at xs4all.nl
http://twitter.com/jigalvh