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

[Xavier Perseguers]

Hi,

REMINDER #4



Xavier Perseguers wrote:
> 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
http://xavier.perseguers.ch/en

One contribution a day keeps the fork away