[TYPO3-dev] How standard compliant is "truncate"?

[ries van Twisk]

On Feb 5, 2009, at 7:11 AM, Bas v.d. Wiel wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> ries van Twisk wrote:
>> On Feb 5, 2009, at 6:51 AM, Peter Kuehn [wmdb] wrote:
>>
>>> Martin Kutschker schrieb:
>>>
>>>> We could add a simple wrapper in t3lib_db.
>>> exec_TRUNCATEquery($table) that checks
>>> if running on mysql
>>>  use "truncate"
>>>  else "delete from without where"
>>> ?
>>>
>>> id love to have that one - would even be cool for cli-importers and
>>> stuff where i frequently have to clear tmp-tables.
>>> gRTz
>>> pekue
>>
>> You only need to pass an Array of tables in the case you want to
>> truncate
>> more then one table. The reason is because of possible foreign
>> references
>> that will not be checked and if found the truncate will not be  
>> executed.
>> Now I know that this doesn't really count for TYPO3 but the API  
>> should
>> be correct.
>>
>> Ries
>
> In my experience a TRUNCATE is *a lot* faster than 'DELETE FROM
> tablename'.. probably due to the fact that TRUNCATE apparently doesn't
> guarantee referential integrity, which is fine as far as I'm concerned
> seeing how 'flat' TYPO3's DB schema is anyway. It's not standard SQL
> though, so I would still prefer the 'DELETE FROM' method for general
> public use. At least it doesn't bomb out/behave unexpectedly when a  
> user
> decides to implement foreign key constraints for whatever reason..  
> like
> myself once, out of curiosity, but I quickly had to retrace my steps.
> Migration to another RDBMS would also be a whole lot easier if TYPO3
> exclusively used standard SQL everywhere.
>
> I still feel many parent/child relationships between TYPO3 tables  
> could
> benefit from foreign key constraints. Simple things like setting
> 'deleted' to 1 on related content elements when a page gets deleted.
>
> Bas

Bas.

the only thing I would be a bit worried about is IF the user makes for  
his
own extension two tables with foreign references constrains
then TRUNCATE WILL bomb out if only one table get's truncated.

That's why I think an API that accepts an array of tables would be on  
it's place.
And now I think of it... May be a cascade Boolean needs to be added to  
such API aswell.

PostgreSQL supports the CASCADE FLAG, not sure if MySQL has such a one  
or that you need to wrap in in a transaction.
I will let the MySQL GURU's speak for that one....

Ries