[TYPO3-core] RFC #11142: DBAL: Column's default value is not properly quoted in CREATE TABLE

[Xavier Perseguers]

Hi,

This is a SVN patch request.

Type: Bugfix

Branches: trunk

BT reference:
http://bugs.typo3.org/view.php?id=11142

Screenshot reference [parsed_columns.png]:
http://bugs.typo3.org/file_download.php?file_id=7455&type=bug

Problem:
------------------------------------
When creating a table a few steps are involved to parse the MySQL table 
creation definition and rewrite it to be compatible with another DBMS.

First step after parsing gives back an array [parsed_columns.png].

The value ['DEFAULT']['value'][0] may contain a numeric value. The 
method compileFieldCfg() returns a definition as

I8 default '0' NOQUOTE

meaning this should be created as a integer with default value '0' (the 
single quote comes from ['DEFAULT']['value'][1])

and it should be unquoted later on.

When having a string, if the default value is empty, then following 
definition is returned for instance:

C 255 default "''" NOQUOTE

You have to read it {double-quote}{single-quote}{single-quote}{double-quote}

The default value will get unquoted which result in '' (2x single quote) 
which is OK.

However when there is a default value which is non-numeric the following 
definition is returned:

C 255 default 'wwwhomepage' NOQUOTE

which is then unquoted and the actual code sent to the database is 
something like that:

CREATE TABLE "tx_euldap_server" (
     ...
     "www" VARCHAR(255) DEFAULT wwwhomepage,
     ...
)

which does not work as anybody may understand. For instance the SQL part 
of what is shown on [parsed_columns.png] is

CREATE TABLE "tx_euldap_server" (
     ...
     "country" VARCHAR(255) DEFAULT countrycode,
     "www" VARCHAR(255) DEFAULT wwwhomepage,
     "user" VARCHAR(255) DEFAULT '',
     ...
)


Solution:
------------------------------------
It is clear that the only valid definition is the last one with an empty 
default value. My patch removes the NOQUOTE attribute when default value 
is not numeric and it results to a valid SQL:

CREATE TABLE "tx_euldap_server" (
     ...
     "country" VARCHAR(255) DEFAULT 'countrycode',
     "www" VARCHAR(255) DEFAULT 'wwwhomepage',
     "user" VARCHAR(255) DEFAULT '',
     ...
)

-- 
Xavier Perseguers
DBAL-Member

http://xavier.perseguers.ch/en/tutorials/typo3.html
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: 11142.diff
Url: http://lists.netfielders.de/pipermail/typo3-team-core/attachments/20090519/7bd863b6/attachment-0001.txt