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] [Updated] (DERBY-3155) Support for SQL:2003 MERGE statement
Date Thu, 27 Feb 2014 15:49:24 GMT

     [ 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-31-aa-deletePrivs.diff

Attaching derby-3155-31-aa-deletePrivs.diff. This patch corrects privilege management for
the DELETE actions of MERGE statements. I am running tests now.

It's my understanding that the user needs the following permissions in order to run a MERGE
statement with a DELETE action. This is what the patch attempts to implement and test:

1) DELETE permission on the target table.

2) For the ON clause and the search condition in the WHEN MATCHED clause

a) SELECT permission on all referenced columns
b) EXECUTE permission on all referenced routines
c) USAGE privilege on all referenced types


Touches the following files:

----------------

M       java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
M       java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java

Move some privilege setting (for UDTs) from UpdateNode up to QueryTreeNode for use by all
nodes.

----------------

M       java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java

Good code hygiene. Added some braces for a dangling consequence of an if statement.

----------------

M       java/engine/org/apache/derby/impl/sql/compile/MergeNode.java
M       java/engine/org/apache/derby/impl/sql/compile/MatchingClauseNode.java
M       java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java

Turn off privilege checking at inappropriate moments.

----------------

A       java/testing/org/apache/derbyTesting/functionTests/tests/lang/Permission.java
M       java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java
M       java/testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java
M       java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java

Tests.


> 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, 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, derby-3155-09-aa-correlationNames.diff, derby-3155-10-aa-correlationNames.diff,
derby-3155-11-ab-beforeTriggersCantFireMerge.diff, derby-3155-12-aa-canOmitInsertColumnList.diff,
derby-3155-13-aa-allowSystemAndTempTables.diff, derby-3155-14-aa-replaceCorrelationNamesOnLeftSideOfSETclauses.diff,
derby-3155-15-aa-replumbMergeResultSetCleanup.diff, derby-3155-16-aa-treatCurrentRowLocationNodeLikeBaseColumnNode.diff,
derby-3155-17-aa-serializingRowLocations.diff, derby-3155-18-aa-basicView.diff, derby-3155-19-aa-forbidSubqueriesInMatchedClauses.diff,
derby-3155-20-aa-reworkColumnMatching.diff, derby-3155-21-ac-cleanupAndForbidSynonyms.diff,
derby-3155-22-ad-testIdentifiersOnLeftSideOfSetClauses.diff, derby-3155-23-aa-forbidDerivedColumnLists.diff,
derby-3155-24-aa-supportParameters.diff, derby-3155-25-aa-parametersAsInsertValues.diff, derby-3155-26-aa-copyRowLocationForIndexScans.diff,
derby-3155-27-aa-adjustMatchingRefinements.diff, derby-3155-28-aa-cardinalityViolations.diff,
derby-3155-29-aa-missingSchema.diff, derby-3155-30-ab-moreCorrelationNames.diff, derby-3155-31-aa-deletePrivs.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.5#6160)

Mime
View raw message