You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2019/12/24 09:29:33 UTC
[GitHub] [incubator-shardingsphere] zhaoyongsheng1990 opened a new issue
#3796: ON DUPLICATE KEY UPDATE support parameters
zhaoyongsheng1990 opened a new issue #3796: ON DUPLICATE KEY UPDATE support parameters
URL: https://github.com/apache/incubator-shardingsphere/issues/3796
## Feature Request
4.0.0-RC3 version not support ON DUPLICATE KEY UPDATE parameters
**For English only**, other languages will not accept.
Please pay attention on issues you submitted, because we maybe need more details.
If no response **more than 7 days** and we cannot make decision by current information, we will **close it**.
Please answer these questions before submitting your issue. Thanks!
### Is your feature request related to a problem?
#3210
#1756
### Describe the feature you would like.
ON DUPLICATE KEY UPDATE support parameters
-------------------------------------------------------------------------------------------------------
this is my implement:
add 3 files :
OnDuplicateKeyValuesExtractor.java
package org.apache.shardingsphere.core.parse.core.extractor.impl.dml.insert;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import com.google.common.base.Optional;
import org.antlr.v4.runtime.ParserRuleContext;
import org.apache.shardingsphere.core.parse.core.extractor.api.CollectionSQLSegmentExtractor;
import org.apache.shardingsphere.core.parse.core.extractor.impl.common.expression.ExpressionExtractor;
import org.apache.shardingsphere.core.parse.core.extractor.util.ExtractorUtils;
import org.apache.shardingsphere.core.parse.core.extractor.util.RuleName;
import org.apache.shardingsphere.core.parse.sql.segment.dml.assignment.OnDuplicateKeyValuesSegment;
import org.apache.shardingsphere.core.parse.sql.segment.dml.expr.ExpressionSegment;
/**
* On duplicate key values extractor.
*
* @author sonny.zhao
*/
public final class OnDuplicateKeyValuesExtractor implements CollectionSQLSegmentExtractor {
private final ExpressionExtractor expressionExtractor = new ExpressionExtractor();
@Override
public Collection<OnDuplicateKeyValuesSegment> extract(final ParserRuleContext ancestorNode, final Map<ParserRuleContext, Integer> parameterMarkerIndexes) {
Optional<ParserRuleContext> onDuplicateKeyValuesClauseNode = ExtractorUtils.findFirstChildNode(ancestorNode, RuleName.ON_DUPLICATE_KEY_CLAUSE);
if (!onDuplicateKeyValuesClauseNode.isPresent()) {
return Collections.emptyList();
}
Collection<OnDuplicateKeyValuesSegment> result = new LinkedList<>();
result.add(new OnDuplicateKeyValuesSegment(onDuplicateKeyValuesClauseNode.get().getStart().getStartIndex(), onDuplicateKeyValuesClauseNode.get().getStop().getStopIndex(), extractExpressionSegments(onDuplicateKeyValuesClauseNode.get(), parameterMarkerIndexes)));
return result;
}
private List<ExpressionSegment> extractExpressionSegments(final ParserRuleContext assignmentValuesNode, final Map<ParserRuleContext, Integer> parameterMarkerIndexes) {
Collection<ParserRuleContext> parserRuleContexts = ExtractorUtils.getAllDescendantNodes(assignmentValuesNode, RuleName.PARAMETER_MARKER);
List<ExpressionSegment> result = new ArrayList<>(parserRuleContexts.size());
for (ParserRuleContext each : parserRuleContexts) {
Optional<? extends ExpressionSegment> expressionSegment = expressionExtractor.extract(each, parameterMarkerIndexes);
if (expressionSegment.isPresent()) {
result.add(expressionSegment.get());
}
}
return result;
}
}
-------------------------------------------------------------------------------------------------------
OnDuplicateKeyValuesFiller.java
package org.apache.shardingsphere.core.parse.core.filler.impl.dml;
import org.apache.shardingsphere.core.parse.core.filler.SQLSegmentFiller;
import org.apache.shardingsphere.core.parse.sql.segment.dml.assignment.OnDuplicateKeyValuesSegment;
import org.apache.shardingsphere.core.parse.sql.statement.SQLStatement;
import org.apache.shardingsphere.core.parse.sql.statement.dml.InsertStatement;
/**
* OnDuplicateKey values filler.
*
* @author sonny.zhao
*/
public final class OnDuplicateKeyValuesFiller implements SQLSegmentFiller<OnDuplicateKeyValuesSegment> {
@Override
public void fill(final OnDuplicateKeyValuesSegment sqlSegment, final SQLStatement sqlStatement) {
((InsertStatement) sqlStatement).getOnDuplicateKeyValues().add(sqlSegment);
}
}
-------------------------------------------------------------------------------------------------------
OnDuplicateKeyValueContext.java
package org.apache.shardingsphere.core.preprocessor.segment.insert;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.List;
import lombok.Getter;
import lombok.ToString;
import org.apache.shardingsphere.core.exception.ShardingException;
import org.apache.shardingsphere.core.parse.sql.segment.dml.expr.ExpressionSegment;
import org.apache.shardingsphere.core.parse.sql.segment.dml.expr.simple.LiteralExpressionSegment;
import org.apache.shardingsphere.core.parse.sql.segment.dml.expr.simple.ParameterMarkerExpressionSegment;
/**
* Insert value context.
*
* @author sonny.zhao
*/
@Getter
@ToString
public final class OnDuplicateKeyValueContext {
private final int parametersCount;
private final List<ExpressionSegment> valueExpressions;
private final List<Object> parameters;
public OnDuplicateKeyValueContext(final Collection<ExpressionSegment> assignments, final List<Object> parameters, final int parametersOffset) {
parametersCount = calculateParametersCount(assignments);
valueExpressions = getValueExpressions(assignments);
this.parameters = getParameters(parameters, parametersOffset);
}
private int calculateParametersCount(final Collection<ExpressionSegment> assignments) {
int result = 0;
for (ExpressionSegment each : assignments) {
if (each instanceof ParameterMarkerExpressionSegment) {
result++;
}
}
return result;
}
private List<ExpressionSegment> getValueExpressions(final Collection<ExpressionSegment> assignments) {
List<ExpressionSegment> result = new ArrayList<>(assignments.size());
result.addAll(assignments);
return result;
}
private List<Object> getParameters(final List<Object> parameters, final int parametersOffset) {
if (0 == parametersCount) {
return Collections.emptyList();
}
List<Object> result = new ArrayList<>(parametersCount);
result.addAll(parameters.subList(parametersOffset, parametersOffset + parametersCount));
return result;
}
/**
* Get value.
*
* @param index index
* @return value
*/
public Object getValue(final int index) {
ExpressionSegment valueExpression = valueExpressions.get(index);
return valueExpression instanceof ParameterMarkerExpressionSegment ? parameters.get(getParameterIndex(valueExpression)) : ((LiteralExpressionSegment) valueExpression).getLiterals();
}
private int getParameterIndex(final ExpressionSegment valueExpression) {
int result = 0;
for (ExpressionSegment each : valueExpressions) {
if (valueExpression == each) {
return result;
}
if (each instanceof ParameterMarkerExpressionSegment) {
result++;
}
}
throw new ShardingException("Can not get parameter index.");
}
}
-------------------------------------------------------------------------------------------------------
modify 5 files
extractor-rule-definition.xml
<extractor-rule id="onDuplicateKeyValuesColumns" extractor-class="org.apache.shardingsphere.core.parse.core.extractor.impl.dml.insert.OnDuplicateKeyValuesExtractor" />
-------------------------------------------------------------------------------------------------------
filler-rule-definition.xml
<filler-rule sql-segment-class="org.apache.shardingsphere.core.parse.sql.segment.dml.assignment.OnDuplicateKeyValuesSegment" filler-class="org.apache.shardingsphere.core.parse.core.filler.impl.dml.OnDuplicateKeyValuesFiller" />
-------------------------------------------------------------------------------------------------------
sql-statement-rule-definition.xml
<sql-statement-rule context="insert" sql-statement-class="org.apache.shardingsphere.core.parse.sql.statement.dml.InsertStatement" extractor-rule-refs="table, columns, insertColumns, insertValues, setAssignments, onDuplicateKeyColumns, onDuplicateKeyValuesColumns" />
-------------------------------------------------------------------------------------------------------
InsertStatement.java
private final Collection<OnDuplicateKeyValuesSegment> onDuplicateKeyValues = new LinkedList<>();
public List<List<ExpressionSegment>> getAllOnDuplicateKeyValueExpressions() {
return null == setAssignment ? OnDuplicateKeyValueExpressionsFromValues() : Collections.singletonList(getAllOnDuplicateKeyValueExpressionsFromSetAssignment());
}
private List<List<ExpressionSegment>> OnDuplicateKeyValueExpressionsFromValues() {
List<List<ExpressionSegment>> result = new ArrayList<>(onDuplicateKeyValues.size());
for (OnDuplicateKeyValuesSegment each : onDuplicateKeyValues) {
result.add(each.getValues());
}
return result;
}
private List<ExpressionSegment> getAllOnDuplicateKeyValueExpressionsFromSetAssignment() {
List<ExpressionSegment> result = new ArrayList<>(setAssignment.getAssignments().size());
for (AssignmentSegment each : setAssignment.getAssignments()) {
result.add(each.getValue());
}
return result;
}
-------------------------------------------------------------------------------------------------------
InsertSQLStatementContext.java
private final List<OnDuplicateKeyValueContext> onDuplicateKeyValueContexts;
public InsertSQLStatementContext(final TableMetas tableMetas, final List<Object> parameters, final InsertStatement sqlStatement) {
super(sqlStatement);
columnNames = sqlStatement.useDefaultColumns() ? tableMetas.getAllColumnNames(getTablesContext().getSingleTableName()) : sqlStatement.getColumnNames();
insertValueContexts = getInsertValueContexts(parameters);
onDuplicateKeyValueContexts = getOnDuplicateKeyValueContexts(parameters); // add this row
}
private List<OnDuplicateKeyValueContext> getOnDuplicateKeyValueContexts(final List<Object> parameters) {
List<OnDuplicateKeyValueContext> result = new LinkedList<>();
int parametersOffset = 0;
for (Collection<ExpressionSegment> each : ((InsertStatement) getSqlStatement()).getAllOnDuplicateKeyValueExpressions()) {
OnDuplicateKeyValueContext onDuplicateKeyValueContext = new OnDuplicateKeyValueContext(each, parameters, parametersOffset);
result.add(onDuplicateKeyValueContext);
parametersOffset += onDuplicateKeyValueContext.getParametersCount();
}
return result;
}
// this look like not grace , but two List not compatibility shardingConditions
public List<List<Object>> getGroupedParameters() {
List<List<Object>> result = new LinkedList<>();
List<Object> parameters = new ArrayList<>();
for (InsertValueContext each : insertValueContexts) {
parameters.addAll(each.getParameters());
}
for (OnDuplicateKeyValueContext each : onDuplicateKeyValueContexts) {
parameters.addAll(each.getParameters());
}
result.add(parameters);
return result;
}
-------------------------------------------------------------------------------------------------------
it worked , but not perfect ,please reference
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] zhaoyongsheng1990 closed issue #3796: ON
DUPLICATE KEY UPDATE support parameters
Posted by GitBox <gi...@apache.org>.
zhaoyongsheng1990 closed issue #3796: ON DUPLICATE KEY UPDATE support parameters
URL: https://github.com/apache/incubator-shardingsphere/issues/3796
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] zhaoyongsheng1990 commented on issue
#3796: ON DUPLICATE KEY UPDATE support parameters
Posted by GitBox <gi...@apache.org>.
zhaoyongsheng1990 commented on issue #3796: ON DUPLICATE KEY UPDATE support parameters
URL: https://github.com/apache/incubator-shardingsphere/issues/3796#issuecomment-568950658
does not need this change , it worked with refers to the value of col_name, in mysql docs says:
In assignment value expressions in the ON DUPLICATE KEY UPDATE clause, you can use the VALUES(col_name) function to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred.
thanks .
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services