[TYPO3-mvc] Oracle DB Backend

[Xavier Perseguers]

Hi Zach,

Zachary Davis wrote:
> I have a client that's using DBAL with an Oracle database backend. My
> understanding is that ExtBase is not currently working with Oracle
> (correct, right?) However, we intend to use ExtBase for this project,

Correct.

> [...]
>
> My question, then, is whether someone here can point me in the general
> direction that we should take on this project. Is writing a new storage
> backend in ExtBase the path that will most likely lead to our work
> getting committed? Or will we be better off working on DBAL and it's
> query parsing mechanisms? Or is there a another, better approach here
> that I'm not seeing? If someone from the ExtBase team could give me some
> general direction on where would be the best place to implement this,
> I'm pretty confident we can take it from there.

I'm happy to read your message. Sounds like "DBAL" support for Extbase 
is a real need, great!

In order for all of you interested, here's my point of the situation:

- DBAL is currently an XCLASS on t3lib_DB, this means that it is 
intended at replacing the various exec_* methods to support the 
underlying DBMS.

- What is does is _parsing_ the MySQL query to figure out the field and 
table names, remap them if needed (which is the case very often when 
having an Oracle DBMS and which will be _always_ the case when using 
Extbase due to the very long table names) and "recompile" the query 
parts into a SQL string which is sent to the underlying DBMS

- Programming in Extbase does not involve writing SQL queries at all, 
all is done through a OO meta-SQL model and Extbase generates the 
corresponding SQL string automatically. As such, Extbase only uses 
sql_query() method from t3lib_DB ($GLOBALS['TYPO3_DB']). This is a way 
of using the TYPO3 DB API but in fact it's nearly the same as calling 
mysql_query right away (except that DB connection is handled by TYPO3).

- DBAL could try to handle all subtleties of SQL used by Extbase when 
receiving a query in the XCLASS version of sql_query(), parse it, remap 
fields and send it to the underlying DBMS but:

	1) Implementing all that support into current SQL parser is far from 
being really feasible
	2) It would be dumb to do it this way as the query was not 
human-created but was Extbase-generated. As such a "parsed version" of 
the query already existed at some stage in Extbase

- This is why the generation of the SQL query out of the SQL query 
"Extbase" representation should be done according to the underlying DBMS

- Basically it means this would be an approach similar to DBAL but 
without using code from DBAL (or nearly nothing from it).

- This could live into Extbase itself or could be an additional part of 
DBAL. The choice _may_ depend on how this problem will be tackled with 
in FLOW3 or on a choice we make for TYPO3 v4 and Extbase.

Now, to be complete and in order for you to have the big picture...

I started a DBAL2 branch where I basically recreated a SQL parser from 
scratch with the purpose of being much easier to maintain and above all, 
being extensible (user functions, ...). It's far from being finished but 
there's already a few A-Z SQL queries that may be handled with it 
(parsing and regenerating of SQL string).

I need to model the query with some SQL building block objects. Funny is 
that Extbase already has some representation of that with its query 
language, for sure not exactly the same but both could be merged 
perhaps? AFAIK some tests have been made in FLOW3 to use Doctrine2 as 
"DBAL" framework. Yet another chance to reuse a query object model.

And finally, maybe we could promote Extbase's way of creating SQL 
queries as the future of the TYPO3 DB API (I'm projecting myself quite 
some time into future).

I hope this gives you the big picture and the road to it... Want to help 
on that?

Regards
Xavier