[
https://issues.apache.org/jira/browse/DERBY-534?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Knut Anders Hatlen updated DERBY-534:
-------------------------------------
Attachment: derby-534-04-a-referencing.diff
derby-534-04-a-referencing.diff makes it possible to create triggers whose WHEN
clause references the transition tables and transition variables defined in the
REFERENCING clause.
Unfortunately, for this to work, the SYSTRIGGERS table needed an extra column
to hold the text of the original WHEN clause, so some upgrade logic is needed.
And since we haven't added columns to existing system tables before, some new
mechanisms were needed.
Additionally, that extra column had to be reflected in TriggerDescriptor, which
is a Formatable, so some version handling was needed in its serialization
logic. (Although I haven't found any scenario where it's stored in the database
with one version and read with another version. I think it's only stored as
part of a stored prepared statement, which will be discarded on version change
anyway. So we might be able to get rid of that piece of logic later.)
With a little bit of refactoring, I was able to reuse the existing processing
of transition tables/variables in triggered SQL statements for WHEN clauses.
This logic is driven from CreateTriggerNode.bindReferencesClause(), and it
essentially replaces references to the transition tables/variables with
internal SQL syntax for VTIs and Java method calls.
Patch details:
{{java/engine/org/apache/derby/iapi/services/io/RegisteredFormatIds.java}}
{{java/engine/org/apache/derby/iapi/services/io/StoredFormatIds.java}}
{{java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java}}
{{java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor_v10_10.java}}
- Add a new field to TriggerDescriptor for the WHEN clause text, update
Formatable logic, and provide a new class using the old Formatable logic for
compatibility.
{{java/engine/org/apache/derby/iapi/sql/compile/Parser.java}}
{{java/engine/org/apache/derby/impl/sql/compile/ParserImpl.java}}
{{java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java}}
- Add a method for parsing an SQL fragment that contains only a search
condition. Used by CreateTriggerNode to re-parse the WHEN clause after
references to transition tables or transition variables have been rewritten.
{{java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj}}
- Add a method for parsing a stand-alone search condition to help implementing
the new method added to the Parser interface.
- Pass original WHEN clause text as argument to CreateTriggerNode (will
eventually end up in the new column in SYSTRIGGERS).
- Raise an exception if the WHEN clause is used in a soft-upgraded database.
{{java/engine/org/apache/derby/iapi/sql/dictionary/CatalogRowFactory.java}}
{{java/engine/org/apache/derby/impl/sql/catalog/SYSTRIGGERSRowFactory.java}}
- Add code for the new column in SYSTRIGGERS.
- Add logic to expose different shape depending on soft and full upgrade.
{{java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java}}
- Remove unused code for fetching all trigger descriptors.
- Change code that creates or upgrades catalogues, so that it uses the new
CatalogRowFactory method that exposes what shape the table should have in a
fully upgraded database (otherwise, since the database is still in soft upgrade
when the upgrade code is running, it would just upgrade the table to its old
shape).
{{java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java}}
- Add code that drives upgrade of the SYSTRIGGERS table if needed.
{{java/engine/org/apache/derby/iapi/sql/dictionary/DataDescriptorGenerator.java}}
{{java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java}}
{{java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java}}
- Pass the WHEN clause text through the layers. Mainly new parameters in
existing methods.
{{java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java}}
- Refactor code to perform the same transformations on the WHEN clause text as
it currently performs on the triggered SQL statement text.
{{java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java}}
- Add basic test cases for WHEN clauses that use transition tables and
transition variables.
{{java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_11.java}}
{{java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeRun.java}}
- Test that the WHEN clause cannot be used in a soft-upgraded database, and
that it can be used in a fully upgraded database.
{{java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out}}
{{java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out}}
{{java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out}}
{{java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out}}
{{java/testing/org/apache/derbyTesting/functionTests/tests/lang/SystemCatalogTest.java}}
Update tests to expect the extra column in SYSTRIGGERS.
> Support use of the WHEN clause in CREATE TRIGGER statements
> -----------------------------------------------------------
>
> Key: DERBY-534
> URL: https://issues.apache.org/jira/browse/DERBY-534
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Kristian Waagan
> Assignee: Knut Anders Hatlen
> Priority: Minor
> Labels: derby_triage10_11
> Attachments: derby-534-01-a-syntax.diff,
> derby-534-02-a-refactor.diff, derby-534-03-a-npe-testcase.diff,
> derby-534-04-a-referencing.diff, parser.diff, WhenClause.html, WhenClause.html
>
>
> Support use of the WHEN clause in CREATE TRIGGER statements. The clause is
> described in the SQL standard (2003) in chapter "11.39 <trigger definition>"
> under "<triggered action>".
> There are traces in the code that suggests some work has been done on this
> earlier. If anyone knows something about this, please add a comment to this
> issue.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
