[TYPO3-core] RFC: Bug #11903: Use separate tables for tags in the caching framework

[Martin Kutschker]

Martin Kutschker schrieb:
> Martin Kutschker schrieb:
>> Martin Kutschker schrieb:
>>> Oliver Hader schrieb:
>>>> Hi Masi,
>>>>
>>>>> I know that the patch is in but yet a -1 from me. Why? Because the patch sneaks in a new feature of
>>>>> t3lib_db. This should have been a separate RFC. The reason is that the new feature
>>>>> DELETEmultipleTablesQuery() isn't DBAL save.
>>>>>
>>>>> This surprises me as I have attached many notes in the bug tracker concerning other database
>>>>> systems.  I suggest to use two different WHERE clauses: one for the JOIN and one for WHERE clause.
>>>> Yes, that's the information I pointed out in the "Notes:" section of
>>>> this RFC. There is no overall solution for all DMBS right know and I
>>>> discussed this also in private with Xavier as DBAL team leader.
>>> Oh. To me the conclusio (of my own research) was, that having such a feature is possible, but it
>>> must be implemented differently for all database systems.
>>>
>>>
>>>> Your example would only delete elements from one table.
>>> Ouch. I thought that the point was the join, not the deleting from multiple tables at once. Mysql
>>> offers both options, but I haven't checked properly for both options. I'll reinvestigate that matter.
>> After re-reading the docs for Mysql, PostgreSQL and SQL server I find that these three databases
>> support THIS syntax:
>>
>> DELETE
>>   [FROM] tbl_name
>>   USING|FROM table_references
>>   WHERE where_condition
>>
>> Note: Mysql and Posgresql use USING, whereas SQL server uses a second FROM. The "table_references"
>> can be anything that is found in the FROM clause of a SELECT statement.
>>
>> Unfortunately this syntax deletes only from one table. Neither PostgreSQL nor SQL server allow
>> multi-tables in the (first) FROM. The USING (second FROM) is only used for a join.
> 
> After studying the cache framework code I see that it a) deletes only from two tables and b) the
> WHERE clause operates only one of of the two tables (except for the join).
> 
> So for Mysql the statement looks like this:
> 
> DELETE table1, table2
>   FROM table1 JOIN table2 ON (table1.id=table2.id)
>   WHERE table1.column1=value
> 
> PostgreSQL has a RETURNING clause, so it could be written like that:
> 
> DELETE FROM table2
>   WHERE id IN
>    (DELETE FROM table1 USING table1 JOIN table2 ON (table1.id=table2.id)
>     WHERE table1.column1=value RETURNING table1.id)
> 
> For the SQL Server (and Oracle) I haven't found a way to get the job done in a single (nested)
> statement. So have to use two statements:
> 
> DELETE FROM table2
>  WHERE id IN
>   (SELECT table1.id
>    FROM table1 JOIN table2 ON (table1.id=table2.id)
>    WHERE table1.column1=value)
> DELETE FROM table1
>  WHERE table1.column1=value

If both tables have a key (which they do for our use cases) then on Oracle this might work:

DELETE FROM
 (SELECT table1.id, table2.id
   FROM table1 JOIN table2 ON (table1.id=table2.id)
   WHERE table1.column1=value)

Maybe DELETEmultipleTablesQuery() won't fly, but there is a chance for DELETEqueryTwoTables().

Masi