[TYPO3-core] RFC #11108: DBAL wildly quotes fields and table names

[Xavier Perseguers]

Hi,

> I'm testing this patch again and I found a problem with the login 
> screen/session management (weird!). Investigating where the problem 
> comes from...

FYI, after hours of testing (!) in both Oracle and MSSQL, I managed to get a working solution which I enclosed here.

This patch was committed as revision 24017 on DBAL-trunk.

For those of you interested into the problem I encountered, here it is.

You may know that table names and fields are normally case-insensitive in Oracle, and that (in fact) they are stored as uppercase. When one manually create a table, say 'test':

CREATE TABLE test (
     factor   NUMBER(11)    NOT NULL
);

it is possible to issue queries without taking care of the case:

SELECT * FROM test;

or

SELECT * FROM TEST;

Problem with the first version of my patch is that tables had been created by TYPO3 using backticks to enclose their name and field definitions and as such tables were created like that:

CREATE TABLE "be_groups" (
"uid"                    NUMBER(20) NOT NULL,
"pid"                    NUMBER(20) DEFAULT 0,
"tstamp"                 NUMBER(20) DEFAULT 0,
"title"                  VARCHAR(50) DEFAULT '',
"non_exclude_fields"     CLOB,

In this very case, Oracle does not consider those name case-insensitive but treat them as case-sensitive, meaning one has to explicit use quotes now on to issue queries:

SELECT * FROM "be_groups"

Otherwise he gets a "table not found".

I tried to remove backticks to get case-insensitive table names but then ADOdb (sometimes) issue queries and add manually the double quotes (e.g., when automatically creating sequences to simulate 
auto_increment feature of MySQL). As such, old (= before patch) code to quote names should still be used.

The option I added allow explicit use of a subcomponent of ADOdb (a DataDictionary) to properly quote names. This is useful for MSSQL:

$TYPO3_CONF_VARS['EXTCONF']['dbal']['handlerCfg'] = array(
         '_DEFAULT' => array(
                 'type' => 'native',
                 'config' => array(
                         'username' => '', // Set by default (overridden)
                         'password' => '', // Set by default (overridden)
                         'host'     => '', // Set by default (overridden)
                         'database' => '', // Set by default (overridden)
                 ),
        ),
         'MYPROJECT' => array(
                 'type' => 'adodb',
                 'config' => array(
                         'username'     => 'sa',
                         'password'     => 'password',
                         'host'         => 'ws2003',
                         'database'     => 'myproject',
                         'driver'       => 'mssql',
=====>                  'useNameQuote' => TRUE,
                 ),
         ),
);


-- 
Xavier Perseguers
http://xavier.perseguers.ch/en

One contribution a day keeps the fork away
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: 11108_v2.diff
Url: http://lists.netfielders.de/pipermail/typo3-team-core/attachments/20090904/65d9812b/attachment-0001.txt