[TYPO3-english] CREATE OR REPLACE VIEW on extension

[Xavier Perseguers]

Hi,

>> I've tried to add a new view to my own extension, but I can't get it to be
>> created when I install the extension. All my tables are created when I
>> install it except my view. The view syntax is correct inside ext_tables.sql
>> because I could create it successfully inside phpmyadmin extension.
>>
>> I've tried to add TCA array on ext_tables.php as I do with tables, but
>> without success.
>>
>> Do I have to do something special to create it?
>>
>> I didn't see any documentation about it out there :(
>
> Probably the TYPO3 sql parser does not understand the VIEWs. The
> sql-file is not passed 'as is' to the database, but parsed and adopted
> (e.g.  all create table calls for existing tables are changed to alter
> table statements).
>
> The  db-experts may give more details here.

Thanks Björn, exactly.

If you open t3lib/class.t3lib_sqlparser.php you won't find any 
parseCREATEVIEW method, which would be the case if it were supported.

As explained, your ext_tables.sql file is not executed as this, this is 
what is really great with TYPO3, the parser allows you to always 
describe your data structure as CREATE TABLE statements and TYPO3 will 
compare the expected structure with what already exists and potentially 
rewrite it with some ALTER TABLE statements to change a column type or 
add/remove columns to an existing table. But this means what is in your 
ext_tables.sql should be understood by the SQL parser.

Views are currently not supported. They were not supported in old 
versions of MySQL and are normally read-only or writeable only under 
certain circumstances. Moreover, the DB API should be compatible with 
other DBMS, when you use DBAL. This is why they were not supported.

But I'm not against them at all. If you manage to provide a patch for 
both TYPO3 Core (class.t3lib_sqlparser.php) and DBAL 
(class.ux_t3lib_sqlparser.php), potentially class.t3lib_db.php (Core) 
and class.ux_t3lib_db.php (DBAL) too and even potentially some unit 
tests (would be so great) to be located within dbal/tests, then I can 
ensure support (at least "partial partial") for them will be added.

Regards

-- 
Xavier Perseguers