You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by du...@apache.org on 2022/09/07 05:41:00 UTC
[shardingsphere] branch master updated: add support for parse OptOnConflict for postgres (#20821)
This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 2fed2724bf9 add support for parse OptOnConflict for postgres (#20821)
2fed2724bf9 is described below
commit 2fed2724bf903d13a4a0367385fa04d5b8c05c19
Author: tianhao960 <ti...@users.noreply.github.com>
AuthorDate: Wed Sep 7 13:40:52 2022 +0800
add support for parse OptOnConflict for postgres (#20821)
---
.../impl/PostgreSQLStatementSQLVisitor.java | 12 ++++++++
.../handler/dml/InsertStatementHandler.java | 3 ++
.../postgresql/dml/PostgreSQLInsertStatement.java | 12 ++++++++
.../src/main/resources/case/dml/insert.xml | 34 ++++++++++++++++++++++
.../main/resources/sql/supported/dml/insert.xml | 1 +
5 files changed, 62 insertions(+)
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/java/org/apache/shardingsphere/sql/parser/postgresql/visitor/statement/impl/PostgreSQLStatementSQLVisitor.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/java/org/apache/shardingsphere/sql/parser/postgresql/visitor/statement/impl/PostgreSQLStatementSQLVisitor.java
index 45adf0448d1..44aa598842f 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/java/org/apache/shardingsphere/sql/parser/postgresql/visitor/statement/impl/PostgreSQLStatementSQLVisitor.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/java/org/apache/shardingsphere/sql/parser/postgresql/visitor/statement/impl/PostgreSQLStatementSQLVisitor.java
@@ -70,6 +70,7 @@ import org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.Na
import org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.NameListContext;
import org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.NaturalJoinTypeContext;
import org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.NumberLiteralsContext;
+import org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.OptOnConflictContext;
import org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.OuterJoinTypeContext;
import org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.OwnerContext;
import org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.ParameterMarkerContext;
@@ -117,6 +118,7 @@ import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.assignment.In
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.assignment.SetAssignmentSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.ColumnSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.InsertColumnsSegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.OnDuplicateKeyColumnsSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.combine.CombineSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.BetweenExpression;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.BinaryOperationExpression;
@@ -629,11 +631,21 @@ public abstract class PostgreSQLStatementSQLVisitor extends PostgreSQLStatementP
// TODO :deal with insert select
PostgreSQLInsertStatement result = (PostgreSQLInsertStatement) visit(ctx.insertRest());
result.setTable((SimpleTableSegment) visit(ctx.insertTarget()));
+ if (null != ctx.optOnConflict()) {
+ result.setOnDuplicateKeyColumnsSegment((OnDuplicateKeyColumnsSegment) visit(ctx.optOnConflict()));
+ }
result.setParameterCount(getCurrentParameterIndex());
result.getParameterMarkerSegments().addAll(getParameterMarkerSegments());
return result;
}
+ @Override
+ public ASTNode visitOptOnConflict(final OptOnConflictContext ctx) {
+ SetClauseListContext setClauseListContext = ctx.setClauseList();
+ Collection<AssignmentSegment> assignments = ((SetAssignmentSegment) visit(setClauseListContext)).getAssignments();
+ return new OnDuplicateKeyColumnsSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), assignments);
+ }
+
@Override
public ASTNode visitInsertTarget(final InsertTargetContext ctx) {
SimpleTableSegment result = (SimpleTableSegment) visit(ctx.qualifiedName());
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/InsertStatementHandler.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/InsertStatementHandler.java
index d34e3cfaeff..f192c9582ba 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/InsertStatementHandler.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/InsertStatementHandler.java
@@ -59,6 +59,9 @@ public final class InsertStatementHandler implements SQLStatementHandler {
if (insertStatement instanceof OpenGaussStatement) {
return ((OpenGaussInsertStatement) insertStatement).getOnDuplicateKeyColumns();
}
+ if (insertStatement instanceof PostgreSQLStatement) {
+ return ((PostgreSQLInsertStatement) insertStatement).getOnDuplicateKeyColumns();
+ }
return Optional.empty();
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/postgresql/dml/PostgreSQLInsertStatement.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/postgresql/dml/PostgreSQLInsertStatement.java
index b31c8bd04d1..1223685c575 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/postgresql/dml/PostgreSQLInsertStatement.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/postgresql/dml/PostgreSQLInsertStatement.java
@@ -19,6 +19,7 @@ package org.apache.shardingsphere.sql.parser.sql.dialect.statement.postgresql.dm
import lombok.Setter;
import lombok.ToString;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.OnDuplicateKeyColumnsSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WithSegment;
import org.apache.shardingsphere.sql.parser.sql.common.statement.dml.InsertStatement;
import org.apache.shardingsphere.sql.parser.sql.dialect.statement.postgresql.PostgreSQLStatement;
@@ -34,6 +35,8 @@ public final class PostgreSQLInsertStatement extends InsertStatement implements
private WithSegment withSegment;
+ private OnDuplicateKeyColumnsSegment onDuplicateKeyColumnsSegment;
+
/**
* Get with segment.
*
@@ -42,4 +45,13 @@ public final class PostgreSQLInsertStatement extends InsertStatement implements
public Optional<WithSegment> getWithSegment() {
return Optional.ofNullable(withSegment);
}
+
+ /**
+ * Get on duplicate key columns segment.
+ *
+ * @return on duplicate key columns segment
+ */
+ public Optional<OnDuplicateKeyColumnsSegment> getOnDuplicateKeyColumns() {
+ return Optional.ofNullable(onDuplicateKeyColumnsSegment);
+ }
}
diff --git a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/insert.xml b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/insert.xml
index 1a43bd7bd74..f6aa568f1f8 100644
--- a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/insert.xml
+++ b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/insert.xml
@@ -533,6 +533,40 @@
</on-duplicate-key-columns>
</insert>
+ <insert sql-case-id="insert_on_duplicate_key_update_with_placeholders_postgres" parameters="1, 1, 'init', 'init'">
+ <table name="t_order" start-index="12" stop-index="18" />
+ <columns start-index="20" stop-index="46">
+ <column name="order_id" start-index="21" stop-index="28" />
+ <column name="user_id" start-index="31" stop-index="37" />
+ <column name="status" start-index="40" stop-index="45" />
+ </columns>
+ <values>
+ <value>
+ <assignment-value>
+ <parameter-marker-expression parameter-index="0" start-index="56" stop-index="56" />
+ <literal-expression value="1" start-index="56" stop-index="56" />
+ </assignment-value>
+ <assignment-value>
+ <parameter-marker-expression parameter-index="1" start-index="59" stop-index="59" />
+ <literal-expression value="1" start-index="59" stop-index="59" />
+ </assignment-value>
+ <assignment-value>
+ <parameter-marker-expression parameter-index="2" start-index="62" stop-index="62" />
+ <literal-expression value="init" start-index="62" stop-index="67" />
+ </assignment-value>
+ </value>
+ </values>
+ <on-duplicate-key-columns start-index="65" stop-index="111" literal-start-index="70" literal-stop-index="121">
+ <assignment start-index="102" stop-index="107" literal-start-index="107" literal-stop-index="121">
+ <column name="status" start-index="102" stop-index="107" literal-start-index="107" literal-stop-index="112" />
+ <assignment-value>
+ <parameter-marker-expression parameter-index="3" start-index="111" stop-index="111" />
+ <literal-expression value="init" start-index="116" stop-index="121" />
+ </assignment-value>
+ </assignment>
+ </on-duplicate-key-columns>
+ </insert>
+
<insert sql-case-id="insert_set_with_all_placeholders_for_table_identifier" parameters="1, 1, 'init'">
<table name="t_order" start-index="12" stop-index="18" />
<set start-index="20" stop-index="84" literal-stop-index="89">
diff --git a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/insert.xml b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/insert.xml
index 799f9c4ba72..1a177ee727a 100644
--- a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/insert.xml
+++ b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/insert.xml
@@ -41,6 +41,7 @@
<sql-case id="insert_with_batch_and_without_generate_key_column" value="INSERT INTO t_order_item(order_id, user_id, status, creation_date) values (?, ?, 'insert', '2017-08-08'), (?, ?, 'insert', '2017-08-08')" db-types="MySQL, SQLServer, PostgreSQL,openGauss" />
<sql-case id="insert_on_duplicate_key_update" value="INSERT INTO t_order (order_id, user_id, status) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE status = VALUES(status)" db-types="MySQL,openGauss" />
<sql-case id="insert_on_duplicate_key_update_with_placeholders" value="INSERT INTO t_order (order_id, user_id, status) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE status = ?" db-types="MySQL,openGauss" />
+ <sql-case id="insert_on_duplicate_key_update_with_placeholders_postgres" value="INSERT INTO t_order (order_id, user_id, status) VALUES (?, ?, ?) ON CONFLICT (order_id) DO UPDATE SET status = ?" db-types="PostgreSQL" />
<sql-case id="insert_on_duplicate_key_update_with_complicated_expression" value="INSERT INTO emp(order_id,emp_id,age,salary) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE salary = VALUES(salary)+VALUES(salary)*0.2" db-types="MySQL" />
<sql-case id="insert_with_multiple_values" value="INSERT INTO t_order (order_id, user_id, status) VALUES (1, 1, 'insert'), (2, 2, 'insert2')" db-types="MySQL" />
<sql-case id="insert_on_duplicate_key_update_with_table_identifier" value="INSERT INTO t_order (t_order.order_id, t_order.user_id, t_order.status) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE t_order.status = VALUES(t_order.status)" db-types="MySQL" />