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

[Bas v.d. Wiel]

-----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
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.9 (GNU/Linux)
Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org

iEYEARECAAYFAkmK124ACgkQ2l7ND2B9iE3LiQCgi2ZaGrOypSNA3iIG/uamwUfG
FhIAoODrD/BjDIrROKMm7ChskgRID3g0
=5oFh
-----END PGP SIGNATURE-----