[DERBY-5403] implement further syntax for truncate table support - identityBehavior clause

• Type: Improvement
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Component/s: SQL
• Labels: derby_triage10_10
• Urgency: Normal
• Issue & fix info: Newcomer
• Assignee: Unassigned
• Reporter: Myrna van Lunteren
• Created: 06/Sep/11 16:55
• Updated: 28/Sep/12 04:12

Description

Since 10.7 basic support for TRUNCATE TABLE has been implemented (see DERBY-268) and documented (see DERBY-4802).

However, according to the SQL Standard (F200 in SQL:2008) the full syntax includes an identityBehavior clause:

TRUNCATE TABLE tableName [ identityBehavior ]

identityBehavior ::=
CONTINUE IDENTITY

RESTART IDENTITY

This clause has not been fully implemented.

Reading through DERBY-268 yields more details, e.g.:
(https://issues.apache.org/jira/browse/DERBY-268?focusedCommentId=12905937&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-12905937):
"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 "

and:

(https://issues.apache.org/jira/browse/DERBY-268?focusedCommentId=12912378&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-12912378) "[...]sqlgrammar.jj would be a good starting point"

and:
( https://issues.apache.org/jira/browse/DERBY-268?focusedCommentId=12916134&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-12916134):
[...]all of this code should be in the engine. The good news is that both TRUNCATE TABLE and ALTER TABLE ALTER COLUMN RESTART are handled by the AlterTableConstantAction machinery at run time. If I were tackling this, I would first try something along these lines:

o AlterTableNode.bindStatement() will need to build a tableElementList structure for TRUNCATE TABLE, describing the identity column which needs to be re-initialized. For ALTER TABLE ALTER COLUMN RESTART, that tableElementList is created by the parser.

o That tableElementList structure will then be picked up by AlterTableNode.prepConstantAction and turned into a ColumnInfo array when the run time structures are generated for TRUNCATE TABLE. The ColumnInfo[] structure should contain enough information to describe the change to the identity column.

o The column info structure will then be processed by AlterTableConstantAction.executeConstantAction() at run time.

You may need to tweak the code a bit to get this to function, but I think this basic processing flow should work.[...]"

and the comments in DERBY-268 after Oct 10, 2010 all refer to an aborted attempt by Eranda to work on this functionality, including a tentative patch.

I'll mark this issue beginner because of that preliminary work.

Issue Links

• is related to: DERBY-4788 Automatically apply the bulk-insert optimization when the size of the source stream warrants it.
• is related to: DERBY-4801 Re-use the bulk-import mechanism to support the TRUNCATE command
• is related to: DERBY-268 Add Support for truncate table