[TYPO3-english] TYPO3 4.3.0-dev with Oracle

[Ries van Twisk]

On May 2, 2009, at 5:17 PM, David Bruchmann wrote:

> ----- Ursprüngliche Nachricht -----
> Von:        Ries van Twisk <typo3 at rvt.dds.nl>
> Gesendet:   Samstag, 2. Mai 2009 23:45:44
> An:         TYPO3 English <typo3-english at lists.netfielders.de>
> CC:
> Betreff:    Re: [TYPO3-english] TYPO3 4.3.0-dev with Oracle
>
>>
>> MySQL allows this mixing of field name cases while most, of not
>> all database change the case it to all uppercase (DB2 I believe) or
>> all lowercase (PostgerSQL).
>>
>> So this query:
>>
>> SELECT MyField FROM table;
>>
>> Might return a different field name cases in your result sets.
>>
>> Even TYPO3 core is not solid and uses mixed cases, which is IMHO not
>> playing really nice.
>>
>
> Hy Ries,
>
> tt_content is a good example for camelcase-fieldnames.
> In Kickstarter I remember that all fieldnames have to be lowercase,  
> but
> perhaps I remember wrong.
>
> Im wondering that authors of dbal and adodb didn't took care on that
> case-point or did that "feature" resist due to historical reasons?

I believe that DBAL does some extensive query parsing and can connect  
to multipel DB's (quite nice).

The PDO extension I wrote was  a lot faster and made a couple of small  
assumptions, DBAL is 'smarter' but much slower. I personally
use one DB most of the times...

I had to make a mapping table for SELECT statements that maps the  
lower/uppercase version to something that is expected by the application

This mapping table is very simple and looks like this :

$fcm['be_groups']['groupmods'] = 'groupMods';
$fcm['be_groups']['locktodomain'] = 'lockToDomain';
$fcm['be_groups']['tsconfig'] = 'TSconfig';
$fcm['be_users']['realname'] = 'realName';
$fcm['be_users']['usermods'] = 'userMods';
....
...

Here you can see that for the table be_groups we have a field  
locktodomain,
from PostgreSQL this is mapped back to lockToDomain.

Out of PostgreSQL we get

$row['locktodomain']

Then we re-map it back to

$row['lockToDomain']

So you can access to value again with the module/extension/core

$value=$row['lockToDomain'];


There where also issues with quoting of integer,
in MySQL you can do this

INSERT INTO table (myIntegerField) VALUES ('23');

MySQL casts the string ('23') automagickly to a integer (it's bogus  
that MySQL accepts a string as a integer)
, you see that many extension quote strings wrong... Not sure anymore  
about core.
This quoating of strings is a problem in other DB's

For this I also wrote a field type mapper that figure out under runtime
the typo3 of a specific field.

For PostgreSQL this is implemented as (the result is cached):

         protected function getFieldTypesSQL() {
                 return 'SELECT column_name,data_type FROM  
information_schema.columns WHERE table_name=%s';
         }

Then the field type mapper takes care of quoting the field correctly,

for this fullQuoteArray(..) needed to get overwritten (using XLASS)  
and re-routed to
the correct responsible for the current DB


other problems are with SQLite that core doesn't close a resource  
after it was used,
for SQLite this is a problem...

THis was also fairly easy to be solved by tracking the number of open  
resources
and close them as soon as it needed to be released.


For my exercise I also write a migration tool that could create DB  
tables
and insert data straight into PostgreSQL PostgreSQL.
Then I could just switch my installation using a BE command and then
run under PostgreSQL.


if Xavier is interested, I am more then happy to send him my current  
work


Ries