db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-268) Add Support for truncate table
Date Fri, 03 Sep 2010 15:29:35 GMT

    [ https://issues.apache.org/jira/browse/DERBY-268?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12905937#action_12905937
] 

Rick Hillegas commented on DERBY-268:
-------------------------------------

As Knut notes, the TRUNCATE TABLE command is defined in the 2008 SQL Standard, part 2, section
14.10 <truncate table statement>. The standard syntax is:

TRUNCATE TABLE tableName [ identityBehavior ]

identityBehavior ::=
   CONTINUE IDENTITY
   | RESTART IDENTITY

The default behavior is CONTINUE IDENTITY, which means that the table's identity column (if
it has one) continues its sequence where it left off. RESTART IDENTITY means that the identity
column resets to start again at its original initial value.

Derby appears to implement a compatible subset of this standard syntax if you are running
in debug mode:

TRUNCATE TABLE tableName

Derby preserves the standard CONTINUE IDENTITY default behavior.

The following comment in AlterTableNode explains why TRUNCATE TABLE has been disabled in non-debug
mode:

		//truncate table is not supported in this release
		//semantics are not yet clearly defined by SQL Council yet
		//truncate will be allowed only in DEBUG builds for testing purposes.

I believe that was a statement about the 2003 rev of the Standard--it is no longer true. I
believe that we can expose this useful command in non-debug, production mode now.

This seems to me to be a good issue for a newcomer. It involves the following:

1) Remove the disabling logic from the initializer of AlterTableNode.

2) Write regression tests to verify that TRUNCATE behaves correctly. In particular, verify
that CONTINUE IDENTITY semantics are enforced.

3) Document this command in the Reference Guide.

A follow-on effort might be to implement the optional CONTINUE IDENTITY and RESTART IDENTITY
clauses. Fortunately, the tricky bit of RESTART IDENTITY has already been implemented. The
tricky bit is the following implied statement which is executed after truncating the table:

ALTER TABLE  tableName ALTER COLUMN RESTART WITH initialValue


> Add Support for truncate table
> ------------------------------
>
>                 Key: DERBY-268
>                 URL: https://issues.apache.org/jira/browse/DERBY-268
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Lance Andersen
>            Priority: Minor
>
> Adding support for truncate table command will aid to portability

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message