TYPO3 Core

The attached patch uses a separate table to store tags. Thus, it's faster for selecting data.
However, due to the lack of JOINs in TYPO3_DB, the flush operations and garbage collection are very inefficient since they have to use iterations over the cache identifiers and will create a lot of DELETE queries...

Extended TYPO3_DB to support something like

DELETE tbl1 FROM tbl1, tbl2 WHERE tbl1.identifier = tbl2.identifier;

A DELETE with multiple tables is non-standard.

PostgreSQL has the non-standard USING clause.

DELETE FROM tbl1 USING tbl2 WHERE tbl1.identifier = tbl2.identifier;

http://www.postgresql.org/docs/8.4/interactive/sql-delete.html

A more portable way would be to use sub-selects. Maybe the DBAL team is possible to come up with a portable solution.

A note in a forum claimed that this will work with Oracle 9i:

DELETE FROM (tbl1, tbl2 WHERE tbl1.identifier = tbl2.identifier);

Though a reply mentioned an error when executing this command.

SQL Server 2000 seems also to support the extended syntax like Mysql.

http://msdn.microsoft.com/en-us/library/aa258847(SQL.80).aspx

It offers also an example how to make the DELETE without this extension. The solution requires sub-selects.

/* SQL-92-Standard subquery */
USE pubs
DELETE FROM titleauthor
WHERE title_id IN
(SELECT title_id
FROM titles
WHERE title LIKE '%computers%')

/* Transact-SQL extension */
USE pubs
DELETE titleauthor
FROM titleauthor INNER JOIN titles
ON titleauthor.title_id = titles.title_id
WHERE titles.title LIKE '%computers%'

Extending the DELETEquery function like this should do the trick:

DELETEquery($table,$where,$table2='',$joinCondition='')

This will maybe not allow for all possible SQL query variations, but will be sufficient for most cases.

Hi,

Trying your stuff on Oracle 11 leads to this:

DELETE FROM (T1, T2 WHERE T1.column1 = T2.column2);

Column 13: Missing SELECT keyword

In addition, it seems that this cannot be achieved without using triggers or ON CASCADE DELETE:
http://www.orafaq.com/forum/t/55408/0/

Xavier, this is the error I mentioned. The thread you link to suggests this syntax:

DELETE FROM (SELECT * FROM tbl1, tbl2 WHERE tbl1.identifier = tbl2.identifier);

Anyway, as Oracle supports sub-queries, DBAL can provide a variant suitable for it. And as a fallback it still could use a SELECT and multiple DELETEs.

OK. With SELECT, at least one of the table should have a key, which I don't have ATM otherwise I get the same error as in the thread I linked. But as you said, we certainly can find a way for DBAL to work with this.

Committed to SVN Trunk (rev. 6025)

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.

Still looking for a DBAL way to get a portable simple solution for the cache clearing problem.