[TYPO3-dev] [DBAL] Problems with Postgresql and extension SQL files

[Martin Kutschker]

Sebastian Roth schrieb:
> Hi there,
> 
> while trying the dbal extension with Typo3 4.0.4 and my locally
> installed PostgreSQL database (version is 8.1, installed from fink on
> MacOSX) i ran into some trouble installing the static_info_tables
> extension.
> 
> The raw picture is: While installing the extension the file
> ext_tables_static+adt.sql gets parsed by dbal and executed into the
> database. Unfortunately while parsing it some columns will be created
> in a different order that expected. The expected order is set with the
> CREATE TABLE statement in the SQL.
> Unfortunately the data will now be inserted _DEPENDING_ on the correct
> order of the columns. This is done with the short syntax :
> 
> INSERT INTO static_languages VALUES ('2', '0', 'AA', 'Afar', '', '',
> '', 'Afaraf', '0', '0');
> 
> As you can see, this leads into problem if the 8th column is maybe an
> varchar(2) ;-).
> So i modified the SQL file a bit to make it look like this:
> 
> INSERT INTO static_languages
> (uid,pid,lg_iso_2,lg_name_en,lg_typo3,lg_country_iso_2,lg_collate_locale,lg_name_local,lg_sacred,lg_constructed) 
> 
> VALUES ('1', '0', 'AB', 'Abkhazian', '', '', '',
> '%GÔøΩ%@<90>%G"ßÔøΩ%@<81>%GÔøΩ%@<83>$(B'Q(B
> $(B'R%GÔøΩ%@(B<8b>$(B'Y%GÔøΩ%@(B<88>%GÔøΩ%@<99>$(B'Q(B',
> '0', '0');
> 
> Using the second syntax it worked. I tried to step into the dbal-code
> but were not able to find the place where the column order gets
> screwed up.
> An working quickfix would be support for the following syntax (my
> example is borrowed from the pgsql-docs):
> INSERT INTO products (product_no, name, price) VALUES
>    (1, 'Cheese', 9.99),
>    (2, 'Bread', 1.99),
>    (3, 'Milk', 2.99);
> 
> Using this syntax we would even save some bytes in the sql files.
> 
> What's your opionion, how can we fix this?

Always use full and only ANSI-SQL-compatible INSERT syntax. The short 
syntax will also break if you add columns.

The installer/EM should issue a warning when it encounters the short syntax.

Masi