Implement db_truncate()

I'm open to this, although of course it would have to be done in the same trivial-wrapper approach as other DB actions. We'd have to document that the reset-of-autoinc behavior is required, so SQLite can simulate it however it wants to.

SQLite does the right thing: if you issue a DELETE without a WHERE clause, it just drops the table and recreates it, just what MySQL and PostgreSQL do with TRUNCATE.

It is better in my opinion to simply special case the "DELETE without a WHERE clause" argument to use TRUNCATE on engines that support it.

Is TRUNCATE part of the SQL 2003 spec, or is it a common extension? If the latter, then I'd rather put the delete-only implementation in the base class and the TRUNCATE version in the db-specific drivers. If the former, then the current setup is fine.

We do want new unit tests for the new code path either way.

Added a test that the serial columns are reset. This failed on SQLite (fixed). Should fail on PostgreSQL, that doesn't reset serial columns when TRUNCATE is used (todo: fix that).

@Crell: TRUNCATE is part of the very new SQL:2008, nothing before that.

Reviewed the patch, seems OK, but won't be good to have also an utility db_truncate_table() to explicitly truncate (or empty) a table and reset the serial columns?

I think that the practical differences between 'DELETE FROM foo' and 'TRUNCATE TABLE foo' warrant that we make this 2 functions.

- If the auto increments are not reset when DELETE is used, than that might be a feature that we like to maintain. There might be a valid case for deleting everything and maintaining the auto increments?

- The MySQL implementation of TRUNCATE TABLE ignores locks, whereas DELETE doesn’t. According to SQL:2008, truncate is a schema-change statement hence it should not respect locking. This might have unwanted side-effects. For example, if db_delete() uses TRUNCATE TABLE, than db_delete() might cause the transaction support to fail?

- Truncate requires different permissions -- in MySQL, it requires DROP-permission; e.g. schema level permissions.

- The SQL:2008 specification says that TRUNCATE TABLE is illegal on a referenced table of a foreign key. If we're adding support for foreign keys, and we're attempting to do so, DamZ's patch might have some issues.

Discussed that with Dries, and we concluded that due to implementation differences between engines (and other smaller concerns, like keeping the autoincrement and requirement a different set of permissions), we cannot simple special case DELETE with no condition.

There are some use cases for an explicit truncate operation (like dropping huge search tables, or cache tables), so we decided to implement it. Here is the patch, already tested on SQLite.

There is a minor syntax error in the query:

TRUNCATE FROM {test} - Array ( ) SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM simpletest591157test' at line 1

The patch fixes it.

This looks good to me.

I'm not sure truncate is supposed to return status codes -- on MySQL it does but on PostgreSQL it might not. It is not a show stopper for me -- just wanted to point it out. We might be able to remove the return option stuff.

The docblock for the $truncateClass variable still says "Delete class". Other than that this looks good on visual inspection.

We don't currently deal with status codes in most other situations, since even with PDO they're still not as standardized as I'd like. I believe merge queries also return "whatever the DB gives us" and then documents the return as "undefined", so I'm OK with doing the same here. Let's fix the docblock and then this has my +1 to RTBC.

Fixed the docblock. Seems ready to go.

Code looks good. Do we have any use cases in core? I can't think of one.

Great. Committed to CVS HEAD. We can tweak as necessary. Thanks!

For use cases in core I can only think of:

      if ($cid == '*') {
        db_delete($table)->execute();
      }
in cache_clear_all().

#452936: Use db_truncate() in cache_clear_all()