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

[Martin Kutschker]

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

But after writing this I wonder if the JOIN is really needed. What exactly is it that the caching
framework is trying to do?

Masi