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 84c641c add SQLServer with clause for delete statement. (#8005)
84c641c is described below
commit 84c641c2ad6f57bced63640bbb283d64fa23a5b7
Author: Zonglei Dong <dongzong...@apache.org>
AuthorDate: Wed Nov 11 19:34:40 2020 +0800
add SQLServer with clause for delete statement. (#8005)
* add SQLServer with clause for delete statement.
* add SQLServer test case for delete with clause.
---
.../main/antlr4/imports/sqlserver/DMLStatement.g4 | 2 +-
.../impl/SQLServerDMLStatementSQLVisitor.java | 5 +-
.../handler/dml/DeleteStatementHandler.java | 14 +++++
.../sqlserver/dml/SQLServerDeleteStatement.java | 12 +++++
.../statement/dml/impl/DeleteStatementAssert.java | 13 +++++
.../statement/dml/DeleteStatementTestCase.java | 8 ++-
.../src/main/resources/case/dml/delete.xml | 60 ++++++++++++++++++++++
.../main/resources/sql/supported/dml/delete.xml | 2 +
8 files changed, 112 insertions(+), 4 deletions(-)
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/DMLStatement.g4
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/DMLStatement.g4
index 960ba98..6b3997d 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/DMLStatement.g4
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/DMLStatement.g4
@@ -57,7 +57,7 @@ assignmentValue
;
delete
- : DELETE top? (singleTableClause | multipleTablesClause) outputClause?
whereClause?
+ : withClause? DELETE top? (singleTableClause | multipleTablesClause)
outputClause? whereClause?
;
singleTableClause
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/impl/SQLServerDMLStatementSQLVisitor.java
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/impl/SQLServerDMLStatementSQLVisitor.java
index 542f09d..097c617 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/impl/SQLServerDMLStatementSQLVisitor.java
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/impl/SQLServerDMLStatementSQLVisitor.java
@@ -17,8 +17,8 @@
package org.apache.shardingsphere.sql.parser.sqlserver.visitor.statement.impl;
-import
org.apache.shardingsphere.sql.parser.api.visitor.operation.SQLStatementVisitor;
import org.apache.shardingsphere.sql.parser.api.visitor.ASTNode;
+import
org.apache.shardingsphere.sql.parser.api.visitor.operation.SQLStatementVisitor;
import org.apache.shardingsphere.sql.parser.api.visitor.type.DMLSQLVisitor;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.AggregationClauseContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.AliasContext;
@@ -296,6 +296,9 @@ public final class SQLServerDMLStatementSQLVisitor extends
SQLServerStatementSQL
@Override
public ASTNode visitDelete(final DeleteContext ctx) {
SQLServerDeleteStatement result = new SQLServerDeleteStatement();
+ if (null != ctx.withClause()) {
+ result.setWithSegment((WithSegment) visit(ctx.withClause()));
+ }
if (null != ctx.multipleTablesClause()) {
result.setTableSegment((TableSegment)
visit(ctx.multipleTablesClause()));
} else {
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/DeleteStatementHandler.java
b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/DeleteStatementHandler.java
index 04cbb3e..bbd3590 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/DeleteStatementHandler.java
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/DeleteStatementHandler.java
@@ -22,6 +22,7 @@ import lombok.NoArgsConstructor;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.OrderBySegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.OutputSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WithSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.statement.dml.DeleteStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.handler.SQLStatementHandler;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.MySQLStatement;
@@ -75,4 +76,17 @@ public final class DeleteStatementHandler implements
SQLStatementHandler {
}
return Optional.empty();
}
+
+ /**
+ * Get with segment.
+ *
+ * @param deleteStatement delete statement
+ * @return with segment
+ */
+ public static Optional<WithSegment> getWithSegment(final DeleteStatement
deleteStatement) {
+ if (deleteStatement instanceof SQLServerStatement) {
+ return ((SQLServerDeleteStatement)
deleteStatement).getWithSegment();
+ }
+ return Optional.empty();
+ }
}
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/sqlserver/dml/SQLServerDeleteStatement.java
b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/sqlserver/dml/SQLServerDeleteStatement.java
index da8af16..7c7ef10 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/sqlserver/dml/SQLServerDeleteStatement.java
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/sqlserver/dml/SQLServerDeleteStatement.java
@@ -20,6 +20,7 @@ package
org.apache.shardingsphere.sql.parser.sql.dialect.statement.sqlserver.dml
import lombok.Setter;
import lombok.ToString;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.OutputSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WithSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.statement.dml.DeleteStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.sqlserver.SQLServerStatement;
@@ -31,8 +32,19 @@ import java.util.Optional;
@Setter
@ToString
public final class SQLServerDeleteStatement extends DeleteStatement implements
SQLServerStatement {
+
+ private WithSegment withSegment;
private OutputSegment outputSegment;
+
+ /**
+ * Get with segment.
+ *
+ * @return with segment.
+ */
+ public Optional<WithSegment> getWithSegment() {
+ return Optional.ofNullable(withSegment);
+ }
/**
* Get output segment.
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/statement/dml/impl/DeleteStatementAssert.java
b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/statement/dml/impl/DeleteStatementAssert.java
index a8218f3..e515529 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/statement/dml/impl/DeleteStatementAssert.java
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/statement/dml/impl/DeleteStatementAssert.java
@@ -22,6 +22,7 @@ import lombok.NoArgsConstructor;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.OrderBySegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.OutputSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WithSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.statement.dml.DeleteStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.handler.dml.DeleteStatementHandler;
import
org.apache.shardingsphere.test.sql.parser.parameterized.asserts.SQLCaseAssertContext;
@@ -30,6 +31,7 @@ import
org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.l
import
org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.orderby.OrderByClauseAssert;
import
org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.output.OutputClauseAssert;
import
org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.where.WhereClauseAssert;
+import
org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.with.WithClauseAssert;
import
org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.statement.dml.DeleteStatementTestCase;
import java.util.Optional;
@@ -51,6 +53,7 @@ public final class DeleteStatementAssert {
* @param expected expected delete statement test case
*/
public static void assertIs(final SQLCaseAssertContext assertContext,
final DeleteStatement actual, final DeleteStatementTestCase expected) {
+ assertWithClause(assertContext, actual, expected);
assertTable(assertContext, actual, expected);
assertOutput(assertContext, actual, expected);
assertWhereClause(assertContext, actual, expected);
@@ -58,6 +61,16 @@ public final class DeleteStatementAssert {
assertLimitClause(assertContext, actual, expected);
}
+ private static void assertWithClause(final SQLCaseAssertContext
assertContext, final DeleteStatement actual, final DeleteStatementTestCase
expected) {
+ Optional<WithSegment> withSegment =
DeleteStatementHandler.getWithSegment(actual);
+ if (null != expected.getWithClause()) {
+ assertTrue(assertContext.getText("Actual with segment should
exist."), withSegment.isPresent());
+ WithClauseAssert.assertIs(assertContext, withSegment.get(),
expected.getWithClause());
+ } else {
+ assertFalse(assertContext.getText("Actual with segment should not
exist."), withSegment.isPresent());
+ }
+ }
+
private static void assertTable(final SQLCaseAssertContext assertContext,
final DeleteStatement actual, final DeleteStatementTestCase expected) {
// TODO to support table assert
}
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/statement/dml/DeleteStatementTestCase.java
b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/statement/dml/DeleteStatementTestCase.java
index 1267fe5..08ff5a8 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/statement/dml/DeleteStatementTestCase.java
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/statement/dml/DeleteStatementTestCase.java
@@ -24,6 +24,7 @@ import
org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain
import
org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.output.ExpectedOutputClause;
import
org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.predicate.ExpectedWhereClause;
import
org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.table.ExpectedSimpleTable;
+import
org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.with.ExpectedWithClause;
import
org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.statement.SQLParserTestCase;
import javax.xml.bind.annotation.XmlElement;
@@ -37,6 +38,9 @@ import java.util.List;
@Setter
public final class DeleteStatementTestCase extends SQLParserTestCase {
+ @XmlElement(name = "with")
+ private ExpectedWithClause withClause;
+
@XmlElement(name = "table")
private final List<ExpectedSimpleTable> tables = new LinkedList<>();
@@ -45,10 +49,10 @@ public final class DeleteStatementTestCase extends
SQLParserTestCase {
@XmlElement(name = "where")
private ExpectedWhereClause whereClause;
-
+
@XmlElement(name = "order-by")
private ExpectedOrderByClause orderByClause;
-
+
@XmlElement(name = "limit")
private ExpectedLimitClause limitClause;
}
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/delete.xml
b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/delete.xml
index b26ebaf..bf42319 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/delete.xml
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/delete.xml
@@ -297,4 +297,64 @@
</and-predicate>
</where>
</delete>
+
+ <delete sql-case-id="delete_with_with_clause">
+ <with start-index="0" stop-index="70">
+ <common-table-expression name = "cte" start-index="5"
stop-index="70">
+ <columns start-index="9" stop-index="27">
+ <column name="order_id" start-index="10" stop-index="17"/>
+ <column name="user_id" start-index="20" stop-index="26"/>
+ </columns>
+ <subquery-expression>
+ <select>
+ <from>
+ <simple-table name="t_order" start-index="63"
stop-index="69" />
+ </from>
+ <projections start-index="40" stop-index="56">
+ <column-projection name="order_id"
start-index="40" stop-index="47" />
+ <column-projection name="user_id" start-index="50"
stop-index="56" />
+ </projections>
+ </select>
+ </subquery-expression>
+ </common-table-expression>
+ </with>
+ <table name="t_order" start-index="79" stop-index="85" />
+ <where start-index="96" stop-index="132">
+ <and-predicate>
+ <predicate start-index="96" stop-index="132">
+ <column-left-value owner="t_order" name="order_id"
start-index="110" stop-index="117" />
+ <operator type="=" />
+ <column-right-value owner="cte" name="order_id"
start-index="125" stop-index="132" />
+ </predicate>
+ </and-predicate>
+ </where>
+ </delete>
+
+ <delete sql-case-id="delete_without_columns_with_with_clause">
+ <with start-index="0" stop-index="50">
+ <common-table-expression name="cte" start-index="5"
stop-index="50">
+ <subquery-expression>
+ <select>
+ <from>
+ <simple-table name="t_order" start-index="43"
stop-index="49" />
+ </from>
+ <projections start-index="20" stop-index="36">
+ <column-projection name="order_id"
start-index="20" stop-index="27" />
+ <column-projection name="user_id" start-index="30"
stop-index="36" />
+ </projections>
+ </select>
+ </subquery-expression>
+ </common-table-expression>
+ </with>
+ <table name="t_order" start-index="59" stop-index="65" />
+ <where start-index="76" stop-index="112">
+ <and-predicate>
+ <predicate start-index="82" stop-index="112">
+ <column-left-value owner="t_order" name="order_id"
start-index="90" stop-index="97" />
+ <operator type="=" />
+ <column-right-value owner="cte" name="order_id"
start-index="105" stop-index="112" />
+ </predicate>
+ </and-predicate>
+ </where>
+ </delete>
</sql-parser-test-cases>
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/delete.xml
b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/delete.xml
index 0c393f3..15a9484 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/delete.xml
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/delete.xml
@@ -30,4 +30,6 @@
<sql-case id="delete_with_output_clause_column_shorthand" value="DELETE
FROM t_order OUTPUT DELETED.* WHERE order_id = ?" db-types="SQLServer" />
<sql-case id="delete_with_top" value="DELETE TOP(10) FROM t_order WHERE
order_id = ?" db-types="SQLServer" />
<sql-case id="delete_with_top_percent" value="DELETE TOP(10) PERCENT FROM
t_order WHERE order_id = ?" db-types="SQLServer" />
+ <sql-case id="delete_with_with_clause" value="WITH cte (order_id, user_id)
AS (SELECT order_id, user_id FROM t_order) DELETE t_order FROM cte WHERE
t_order.order_id = cte.order_id" db-types="SQLServer" />
+ <sql-case id="delete_without_columns_with_with_clause" value="WITH cte AS
(SELECT order_id, user_id FROM t_order) DELETE t_order FROM cte WHERE
t_order.order_id = cte.order_id" db-types="SQLServer" />
</sql-cases>
