You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by pa...@apache.org on 2020/07/16 08:53:58 UTC
[shardingsphere] branch master updated: support mysql insert select
statement parse and add some test cases (#6350)
This is an automated email from the ASF dual-hosted git repository.
panjuan 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 c981289 support mysql insert select statement parse and add some test cases (#6350)
c981289 is described below
commit c9812897fb35724138a530ab2df436e51979c817
Author: DuanZhengqiang <st...@gmail.com>
AuthorDate: Thu Jul 16 03:53:39 2020 -0500
support mysql insert select statement parse and add some test cases (#6350)
* support mysql insert select statement parse and add some test cases
* add apache license
* modify test cases
* replace InsertSelectSegment and SelectSegment with SubquerySegment
---
.../parser/mysql/visitor/impl/MySQLDMLVisitor.java | 16 ++
.../parser/sql/statement/dml/InsertStatement.java | 12 ++
.../statement/dml/impl/InsertStatementAssert.java | 10 ++
.../statement/dml/InsertStatementTestCase.java | 3 +
.../src/test/resources/case/dml/insert.xml | 175 +++++++++++++++++++++
.../test/resources/sql/supported/dml/insert.xml | 5 +
6 files changed, 221 insertions(+)
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/impl/MySQLDMLVisitor.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/impl/MySQLDMLVisitor.java
index c92c750..346746f 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/impl/MySQLDMLVisitor.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/impl/MySQLDMLVisitor.java
@@ -35,6 +35,7 @@ import org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.ExprCon
import org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.FromClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.GroupByClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.InsertContext;
+import org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.InsertSelectClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.InsertValuesClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.JoinSpecificationContext;
import org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.JoinedTableContext;
@@ -142,6 +143,8 @@ public final class MySQLDMLVisitor extends MySQLVisitor implements DMLVisitor {
InsertStatement result;
if (null != ctx.insertValuesClause()) {
result = (InsertStatement) visit(ctx.insertValuesClause());
+ } else if (null != ctx.insertSelectClause()) {
+ result = (InsertStatement) visit(ctx.insertSelectClause());
} else {
result = new InsertStatement();
result.setSetAssignment((SetAssignmentSegment) visit(ctx.setAssignmentsClause()));
@@ -155,6 +158,19 @@ public final class MySQLDMLVisitor extends MySQLVisitor implements DMLVisitor {
}
@Override
+ public ASTNode visitInsertSelectClause(final InsertSelectClauseContext ctx) {
+ InsertStatement result = new InsertStatement();
+ result.setInsertColumns(createInsertColumns(ctx.columnNames(), ctx.start.getStartIndex()));
+ result.setInsertSelect(createInsertSelectSegment(ctx));
+ return result;
+ }
+
+ private SubquerySegment createInsertSelectSegment(final InsertSelectClauseContext ctx) {
+ SelectStatement selectStatement = (SelectStatement) visit(ctx.select());
+ return new SubquerySegment(ctx.select().start.getStartIndex(), ctx.select().stop.getStopIndex(), selectStatement);
+ }
+
+ @Override
public ASTNode visitInsertValuesClause(final InsertValuesClauseContext ctx) {
InsertStatement result = new InsertStatement();
result.setInsertColumns(createInsertColumns(ctx.columnNames(), ctx.start.getStartIndex()));
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/statement/dml/InsertStatement.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/statement/dml/InsertStatement.java
index 6b8441b..1bd5645 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/statement/dml/InsertStatement.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/statement/dml/InsertStatement.java
@@ -26,6 +26,7 @@ import org.apache.shardingsphere.sql.parser.sql.segment.dml.column.ColumnSegment
import org.apache.shardingsphere.sql.parser.sql.segment.dml.column.InsertColumnsSegment;
import org.apache.shardingsphere.sql.parser.sql.segment.dml.column.OnDuplicateKeyColumnsSegment;
import org.apache.shardingsphere.sql.parser.sql.segment.dml.expr.ExpressionSegment;
+import org.apache.shardingsphere.sql.parser.sql.segment.dml.expr.subquery.SubquerySegment;
import org.apache.shardingsphere.sql.parser.sql.segment.generic.table.SimpleTableSegment;
import java.util.ArrayList;
@@ -48,6 +49,8 @@ public final class InsertStatement extends DMLStatement {
private SetAssignmentSegment setAssignment;
+ private SubquerySegment insertSelect;
+
private OnDuplicateKeyColumnsSegment onDuplicateKeyColumns;
private final Collection<InsertValuesSegment> values = new LinkedList<>();
@@ -80,6 +83,15 @@ public final class InsertStatement extends DMLStatement {
}
/**
+ * Get insert select segment.
+ *
+ * @return insert select segment
+ */
+ public Optional<SubquerySegment> getInsertSelect() {
+ return Optional.ofNullable(insertSelect);
+ }
+
+ /**
* Get on duplicate key columns segment.
*
* @return on duplicate key columns segment
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/test/java/org/apache/shardingsphere/sql/parser/integrate/asserts/statement/dml/impl/InsertStatementAssert.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/test/java/org/apache/shardingsphere/sql/parser/integrate/asserts/statement/dml/impl/InsertStatementAssert.java
index 0fa9597..5f7fc44 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/test/java/org/apache/shardingsphere/sql/parser/integrate/asserts/statement/dml/impl/InsertStatementAssert.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/test/java/org/apache/shardingsphere/sql/parser/integrate/asserts/statement/dml/impl/InsertStatementAssert.java
@@ -49,6 +49,7 @@ public final class InsertStatementAssert {
assertInsertColumnsClause(assertContext, actual, expected);
assertInsertValuesClause(assertContext, actual, expected);
assertSetClause(assertContext, actual, expected);
+ assertInsertSelectClause(assertContext, actual, expected);
assertOnDuplicateKeyColumns(assertContext, actual, expected);
}
@@ -83,6 +84,15 @@ public final class InsertStatementAssert {
}
}
+ private static void assertInsertSelectClause(final SQLCaseAssertContext assertContext, final InsertStatement actual, final InsertStatementTestCase expected) {
+ if (null != expected.getSelectTestCase()) {
+ assertTrue(assertContext.getText("Actual insert select segment should exist."), actual.getInsertSelect().isPresent());
+ SelectStatementAssert.assertIs(assertContext, actual.getInsertSelect().get().getSelect(), expected.getSelectTestCase());
+ } else {
+ assertFalse(assertContext.getText("Actual insert select segment should not exist."), actual.getInsertSelect().isPresent());
+ }
+ }
+
private static void assertOnDuplicateKeyColumns(final SQLCaseAssertContext assertContext, final InsertStatement actual, final InsertStatementTestCase expected) {
if (null != expected.getOnDuplicateKeyColumns()) {
assertTrue(assertContext.getText("Actual on duplicate key columns segment should exist."), actual.getOnDuplicateKeyColumns().isPresent());
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/test/java/org/apache/shardingsphere/sql/parser/integrate/jaxb/cases/domain/statement/dml/InsertStatementTestCase.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/test/java/org/apache/shardingsphere/sql/parser/integrate/jaxb/cases/domain/statement/dml/InsertStatementTestCase.java
index 9aefb20..ba7a50c 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/test/java/org/apache/shardingsphere/sql/parser/integrate/jaxb/cases/domain/statement/dml/InsertStatementTestCase.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/test/java/org/apache/shardingsphere/sql/parser/integrate/jaxb/cases/domain/statement/dml/InsertStatementTestCase.java
@@ -47,6 +47,9 @@ public final class InsertStatementTestCase extends SQLParserTestCase {
@XmlElement(name = "set")
private ExpectedSetClause setClause;
+ @XmlElement(name = "select")
+ private SelectStatementTestCase selectTestCase;
+
@XmlElement(name = "on-duplicate-key-columns")
private ExpectedOnDuplicateKeyColumns onDuplicateKeyColumns;
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/test/resources/case/dml/insert.xml b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/test/resources/case/dml/insert.xml
index 1541766..2e88c73 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/test/resources/case/dml/insert.xml
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/test/resources/case/dml/insert.xml
@@ -1113,4 +1113,179 @@
</value>
</values>
</insert>
+
+ <insert sql-case-id="insert_select_with_all_columns" parameters="100">
+ <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>
+ <select>
+ <table-reference>
+ <table-factor>
+ <table name="t_order" start-index="86" stop-index="92" />
+ </table-factor>
+ </table-reference>
+ <projections start-index="55" stop-index="79">
+ <column-projection name="order_id" start-index="55" stop-index="62" />
+ <column-projection name="user_id" start-index="65" stop-index="71" />
+ <column-projection name="status" start-index="74" stop-index="79" />
+ </projections>
+ <where start-index="94" stop-index="111">
+ <and-predicate>
+ <predicate start-index="100" stop-index="111" literal-stop-index="113">
+ <column-left-value name="order_id" start-index="100" stop-index="107" />
+ <operator type="=" />
+ <compare-right-value>
+ <parameter-marker-expression value="0" start-index="111" stop-index="111" />
+ <literal-expression value="100" start-index="111" stop-index="113" />
+ </compare-right-value>
+ </predicate>
+ </and-predicate>
+ </where>
+ </select>
+ </insert>
+
+ <insert sql-case-id="insert_select_without_columns" parameters="100">
+ <table name="t_order" start-index="12" stop-index="18" />
+ <columns start-index="19" stop-index="19" />
+ <select>
+ <table-reference>
+ <table-factor>
+ <table name="t_order" start-index="58" stop-index="64" />
+ </table-factor>
+ </table-reference>
+ <projections start-index="27" stop-index="51">
+ <column-projection name="order_id" start-index="27" stop-index="34" />
+ <column-projection name="user_id" start-index="37" stop-index="43" />
+ <column-projection name="status" start-index="46" stop-index="51" />
+ </projections>
+ <where start-index="66" stop-index="83">
+ <and-predicate>
+ <predicate start-index="72" stop-index="83" literal-stop-index="85">
+ <column-left-value name="order_id" start-index="72" stop-index="79" />
+ <operator type="=" />
+ <compare-right-value>
+ <parameter-marker-expression value="0" start-index="83" stop-index="83" />
+ <literal-expression value="100" start-index="83" stop-index="85" />
+ </compare-right-value>
+ </predicate>
+ </and-predicate>
+ </where>
+ </select>
+ </insert>
+
+ <insert sql-case-id="insert_select_with_generate_key_column" parameters="100">
+ <table name="t_order_item" start-index="12" stop-index="23" />
+ <columns start-index="24" stop-index="74">
+ <column name="item_id" start-index="25" stop-index="31" />
+ <column name="order_id" start-index="34" stop-index="41" />
+ <column name="user_id" start-index="44" stop-index="50" />
+ <column name="status" start-index="53" stop-index="58" />
+ <column name="creation_date" start-index="61" stop-index="73" />
+ </columns>
+ <select>
+ <table-reference>
+ <table-factor>
+ <table name="t_order_item" start-index="139" stop-index="150" />
+ </table-factor>
+ </table-reference>
+ <projections start-index="83" stop-index="132">
+ <column-projection name="item_id" start-index="83" stop-index="89" />
+ <column-projection name="order_id" start-index="92" stop-index="99" />
+ <column-projection name="user_id" start-index="102" stop-index="108" />
+ <expression-projection start-index="111" stop-index="118" />
+ <expression-projection start-index="121" stop-index="132" />
+ </projections>
+ <where start-index="152" stop-index="168">
+ <and-predicate>
+ <predicate start-index="158" stop-index="168" literal-stop-index="170">
+ <column-left-value name="item_id" start-index="158" stop-index="164" />
+ <operator type="=" />
+ <compare-right-value>
+ <parameter-marker-expression value="0" start-index="168" stop-index="168" />
+ <literal-expression value="100" start-index="168" stop-index="170" />
+ </compare-right-value>
+ </predicate>
+ </and-predicate>
+ </where>
+ </select>
+ </insert>
+
+ <insert sql-case-id="insert_select_without_generate_key_column" parameters="100">
+ <table name="t_order_item" start-index="12" stop-index="23" />
+ <columns start-index="24" stop-index="65">
+ <column name="order_id" start-index="25" stop-index="32" />
+ <column name="user_id" start-index="35" stop-index="41" />
+ <column name="status" start-index="44" stop-index="49" />
+ <column name="creation_date" start-index="52" stop-index="64" />
+ </columns>
+ <select>
+ <table-reference>
+ <table-factor>
+ <table name="t_order_item" start-index="121" stop-index="132" />
+ </table-factor>
+ </table-reference>
+ <projections start-index="74" stop-index="114">
+ <column-projection name="order_id" start-index="74" stop-index="81" />
+ <column-projection name="user_id" start-index="84" stop-index="90" />
+ <expression-projection start-index="93" stop-index="100" />
+ <expression-projection start-index="103" stop-index="114" />
+ </projections>
+ <where start-index="134" stop-index="151">
+ <and-predicate>
+ <predicate start-index="140" stop-index="151" literal-stop-index="153">
+ <column-left-value name="order_id" start-index="140" stop-index="147" />
+ <operator type="=" />
+ <compare-right-value>
+ <parameter-marker-expression value="0" start-index="151" stop-index="151" />
+ <literal-expression value="100" start-index="151" stop-index="153" />
+ </compare-right-value>
+ </predicate>
+ </and-predicate>
+ </where>
+ </select>
+ </insert>
+
+ <insert sql-case-id="insert_select_on_duplicate_key_update" parameters="100">
+ <table name="t_order" start-index="12" stop-index="18" />
+ <columns start-index="19" stop-index="45">
+ <column name="order_id" start-index="20" stop-index="27" />
+ <column name="user_id" start-index="30" stop-index="36" />
+ <column name="status" start-index="39" stop-index="44" />
+ </columns>
+ <select>
+ <table-reference>
+ <table-factor>
+ <table name="t_order" start-index="85" stop-index="91" />
+ </table-factor>
+ </table-reference>
+ <projections start-index="54" stop-index="78">
+ <column-projection name="order_id" start-index="54" stop-index="61" />
+ <column-projection name="user_id" start-index="64" stop-index="70" />
+ <column-projection name="status" start-index="73" stop-index="78" />
+ </projections>
+ <where start-index="93" stop-index="110">
+ <and-predicate>
+ <predicate start-index="99" stop-index="110" literal-stop-index="112">
+ <column-left-value name="order_id" start-index="99" stop-index="106" />
+ <operator type="=" />
+ <compare-right-value>
+ <parameter-marker-expression value="0" start-index="110" stop-index="110" literal-stop-index="112" />
+ <literal-expression value="100" start-index="110" stop-index="112" />
+ </compare-right-value>
+ </predicate>
+ </and-predicate>
+ </where>
+ </select>
+ <on-duplicate-key-columns start-index="112" stop-index="158" literal-start-index="114" literal-stop-index="160">
+ <assignment start-index="136" stop-index="158" literal-start-index="138" literal-stop-index="160">
+ <column name="status" start-index="136" stop-index="141" literal-start-index="138" literal-stop-index="143" />
+ <assignment-value>
+ <common-expression text="VALUES(status)" start-index="145" stop-index="158" literal-start-index="147" literal-stop-index="160" />
+ </assignment-value>
+ </assignment>
+ </on-duplicate-key-columns>
+ </insert>
</sql-parser-test-cases>
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/test/resources/sql/supported/dml/insert.xml b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/test/resources/sql/supported/dml/insert.xml
index c2c172a..5ddfec8 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/test/resources/sql/supported/dml/insert.xml
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/test/resources/sql/supported/dml/insert.xml
@@ -55,4 +55,9 @@
<sql-case id="insert_on_duplicate_key_update_with_base64_aes_encrypt" value="INSERT INTO t_order SET order_id = ?, user_id = ?, status = convert(to_base64(aes_encrypt(?, 'key')) USING utf8) ON DUPLICATE KEY UPDATE status = VALUES(status)" db-types="MySQL" />
<sql-case id="insert_all_with_all_placeholders" value="INSERT ALL INTO t_order (order_id, user_id, status) VALUES (?, ?, ?) INTO t_order (order_id, user_id, status) VALUES (?, ?, ?) INTO t_order (order_id, user_id, status) VALUES (?, ?, ?) SELECT * FROM dual" db-types="Oracle"/>
<sql-case id="insert_with_str_date_add" value="INSERT INTO t_order(present_date, order_id, user_id) VALUES (date_add(now(),interval ? second), ?, ?)" db-types="MySQL" />
+ <sql-case id="insert_select_with_all_columns" value="INSERT INTO t_order (order_id, user_id, status) SELECT order_id, user_id, status FROM t_order WHERE order_id = ?" db-types="MySQL" />
+ <sql-case id="insert_select_without_columns" value="INSERT INTO t_order SELECT order_id, user_id, status FROM t_order WHERE order_id = ?" db-types="MySQL" />
+ <sql-case id="insert_select_with_generate_key_column" value="INSERT INTO t_order_item(item_id, order_id, user_id, status, creation_date) SELECT item_id, order_id, user_id, 'insert', '2017-08-08' FROM t_order_item WHERE item_id = ?" db-types="MySQL" />
+ <sql-case id="insert_select_without_generate_key_column" value="INSERT INTO t_order_item(order_id, user_id, status, creation_date) SELECT order_id, user_id, 'insert', '2017-08-08' FROM t_order_item WHERE order_id = ?" db-types="MySQL" />
+ <sql-case id="insert_select_on_duplicate_key_update" value="INSERT INTO t_order(order_id, user_id, status) SELECT order_id, user_id, status FROM t_order WHERE order_id = ? ON DUPLICATE KEY UPDATE status = VALUES(status)" db-types="MySQL" />
</sql-cases>