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 <[email protected]>
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" />
