[
https://issues.apache.org/jira/browse/DERBY-3155?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas updated DERBY-3155:
---------------------------------
Attachment: derby-3155-08-ah-updateAction.diff
Attaching derby-3155-08-ah-updateAction.diff. This patch adds basic support for
the UPDATE action of MERGE statements. I am running tests now.
This patch includes tests to stress simple examples of the following scenarios:
1) Target tables with identity, generated, and default columns.
2) Target tables with before/after row/statement UPDATE triggers.
3) Target tables with primary, foreign, and check constraints.
4) Source tables which are the OLD and NEW transition tables of triggers.
5) MERGE statements combining all three kinds of actions: INSERT, UPDATE, and
DELETE.
When setting an identity column to the literal DEFAULT value, the UPDATE action
of a MERGE statement behaves in the same incorrect way as an ordinary UPDATE
statement. This behavior should be corrected when we fix DERBY-6414.
Touches the following files:
---------------------
M java/engine/org/apache/derby/impl/sql/compile/MergeNode.java
M java/engine/org/apache/derby/impl/sql/compile/MatchingClauseNode.java
Bulk of the compile-time changes needed to support UPDATE actions.
---------------------
M java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java
Changes to distinguish MERGE actions from UPDATEs driven by VTIs. It seems that
trigger transition tables are modelled as VTIs when they drive UPDATEs.
---------------------
M java/engine/org/apache/derby/impl/sql/compile/FromList.java
M java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
The binding of column references now involves poking the table name into the
reference. Later on, this makes it possible to associate bound columns with
their base tables when code-generating the temporary rows which are buffered up
for UPDATE actions.
---------------------
M java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java
Up until now, it has not been necessary to optimize the dummy SELECT which is
cooked up while compiling INSERT/DELETE actions. However, we need to
pre-process that SELECT for UPDATE actions. That is because an important
re-mapping of column references happens during pre-processing. That re-mapping
is needed in order to correctly code-generate CHECK constraints.
---------------------
M java/engine/org/apache/derby/impl/sql/execute/DMLVTIResultSet.java
M java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java
M java/engine/org/apache/derby/impl/sql/execute/DMLWriteResultSet.java
M java/engine/org/apache/derby/impl/sql/execute/DeleteResultSet.java
The ResultDescription field, common across all of the leaf DML classes, has
been pulled up into their common superclass. This makes it possible to pull
some normalization logic up into the superclass for sharing between the INSERT
and UPDATE actions of MERGE statements.
---------------------
M java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java
Normalize the rows of UPDATE actions in order to enforce storability
restrictions.
---------------------
M java/engine/org/apache/derby/impl/store/access/heap/HeapRowLocation.java
The RowLocations returned by the driving left join now implement RefDataValue.
This eliminates a ClassCastException at run time.
---------------------
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java
New tests for the new functionality.
> Support for SQL:2003 MERGE statement
> ------------------------------------
>
> Key: DERBY-3155
> URL: https://issues.apache.org/jira/browse/DERBY-3155
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Trejkaz
> Assignee: Rick Hillegas
> Labels: derby_triage10_10
> Attachments: MergeStatement.html, MergeStatement.html,
> MergeStatement.html, derby-3155-01-ac-grammar.diff,
> derby-3155-02-ag-fixParserWarning.diff,
> derby-3155-03-ae-backingStoreHashtableWithRowLocation.diff,
> derby-3155-03-af-backingStoreHashtableWithRowLocation.diff,
> derby-3155-03-ag-backingStoreHashtableWithRowLocation.diff,
> derby-3155-03-ah-backingStoreHashtableWithRowLocation.diff,
> derby-3155-04-ae-deleteAction.diff, derby-3155-04-af-deleteAction.diff,
> derby-3155-05-aa-triggerTransitionTableAsTarget.diff,
> derby-3155-06-aa-triggerTransitionTableAsSource.diff,
> derby-3155-07-ad-insertAction.diff, derby-3155-08-ah-updateAction.diff
>
>
> A relatively common piece of logic in a database application is to check for
> a row's existence and then either update or insert depending on its existence.
> SQL:2003 added a MERGE statement to perform this operation. It looks like
> this:
> MERGE INTO table_name USING table_name ON (condition)
> WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
> WHEN NOT MATCHED THEN INSERT column1 [, column2 ...] VALUES (value1 [,
> value2 ...])
> At the moment, the only workaround for this would be to write a stored
> procedure to do the same operation, or to implement the logic client-side.
--
This message was sent by Atlassian JIRA
(v6.1#6144)
