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 2021/06/30 05:57:05 UTC
[shardingsphere] branch master updated: add oracle insert statement
(#11027)
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 3ac046f add oracle insert statement (#11027)
3ac046f is described below
commit 3ac046f129670321dfebabab929ce2132eeb2586
Author: Thanoshan MV <48...@users.noreply.github.com>
AuthorDate: Wed Jun 30 11:26:30 2021 +0530
add oracle insert statement (#11027)
---
.../src/main/antlr4/imports/oracle/BaseRule.g4 | 12 ++
.../src/main/antlr4/imports/oracle/DMLStatement.g4 | 49 ++++-
.../main/antlr4/imports/oracle/OracleKeyword.g4 | 8 +
.../impl/OracleDMLStatementSQLVisitor.java | 141 +++++++++++++-
.../generic/InsertMultiTableElementSegment.java} | 23 ++-
.../handler/dml/InsertStatementHandler.java | 30 +++
.../oracle/dml/OracleInsertStatement.java | 28 +++
.../insert/InsertMultiTableElementAssert.java | 54 ++++++
.../statement/dml/impl/InsertStatementAssert.java | 34 +++-
.../insert/ExpectedInsertMultiTableElement.java} | 21 +-
.../statement/dml/InsertStatementTestCase.java | 9 +-
.../src/main/resources/case/dml/insert.xml | 214 +++++++++++++++++++++
.../main/resources/sql/supported/dml/insert.xml | 14 +-
13 files changed, 599 insertions(+), 38 deletions(-)
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/BaseRule.g4 b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/BaseRule.g4
index b060070..0a0eba3 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/BaseRule.g4
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/BaseRule.g4
@@ -759,3 +759,15 @@ networkCost
defaultSelectivity
: INTEGER_
;
+
+dataItem
+ : variableName
+ ;
+
+variableName
+ : identifier | stringLiterals
+ ;
+
+materializedViewName
+ : (owner DOT_)? name
+ ;
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/DMLStatement.g4 b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
index 8ad77e5..cad5e2a 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
@@ -20,19 +20,19 @@ grammar DMLStatement;
import Symbol, Keyword, OracleKeyword, Literals, BaseRule, Comments;
insert
- : INSERT (insertSingleTable | insertMultiTable)
+ : INSERT hint? (insertSingleTable | insertMultiTable)
;
insertSingleTable
- : insertIntoClause (insertValuesClause | select)
+ : insertIntoClause (insertValuesClause returningClause? | selectSubquery) errorLoggingClause?
;
insertMultiTable
- : (ALL multiTableElement+ | conditionalInsertClause) select
+ : (ALL multiTableElement+ | conditionalInsertClause) selectSubquery
;
multiTableElement
- : insertIntoClause insertValuesClause
+ : insertIntoClause insertValuesClause? errorLoggingClause?
;
conditionalInsertClause
@@ -48,11 +48,45 @@ conditionalInsertElsePart
;
insertIntoClause
- : INTO tableName (AS? alias)?
+ : INTO dmlTableExprClause alias? columnNames?
;
insertValuesClause
- : columnNames? VALUES assignmentValues (COMMA_ assignmentValues)*
+ : VALUES assignmentValues
+ ;
+
+returningClause
+ : (RETURN | RETURNING) exprs INTO dataItem (COMMA_ dataItem)*
+ ;
+
+dmlTableExprClause
+ : dmlTableClause | dmlSubqueryClause | tableCollectionExpr
+ ;
+
+dmlTableClause
+ : tableName (partitionExtClause | AT_ dbLink)?
+ | (viewName | materializedViewName) (AT_ dbLink)?
+ ;
+
+partitionExtClause
+ : PARTITION (LP_ partitionName RP_ | FOR LP_ partitionKeyValue (COMMA_ partitionKeyValue) RP_)
+ | SUBPARTITION (LP_ subpartitionName RP_ | FOR LP_ subpartitionKeyValue (COMMA_ subpartitionKeyValue) RP_)
+ ;
+
+dmlSubqueryClause
+ : LP_ selectSubquery subqueryRestrictionClause? RP_
+ ;
+
+subqueryRestrictionClause
+ : WITH (READ ONLY | CHECK OPTION) (CONSTRAINT constraintName)?
+ ;
+
+tableCollectionExpr
+ : TABLE LP_ collectionExpr RP_ (LP_ PLUS_ RP_)?
+ ;
+
+collectionExpr
+ : selectSubquery | columnName | functionCall | expr
;
update
@@ -73,7 +107,6 @@ setAssignmentsClause
assignmentValues
: LP_ assignmentValue (COMMA_ assignmentValue)* RP_
- | LP_ RP_
;
assignmentValue
@@ -466,5 +499,5 @@ mergeColumnValue
;
errorLoggingClause
- : LOG ERRORS (INTO tableName)? (LP_ simpleExpr RP_)? (REJECT LIMIT (numberLiterals | UNLIMITED))?
+ : LOG ERRORS (INTO tableName)? (LP_ simpleExpr RP_)? (REJECT LIMIT (INTEGER_ | UNLIMITED))?
;
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4 b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
index 6ba3ec8..67c42d8 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
@@ -2226,3 +2226,11 @@ TYPES
SELECTIVITY
: S E L E C T I V I T Y
;
+
+RETURNING
+ : R E T U R N I N G
+ ;
+
+OPTION
+ : O P T I O N
+ ;
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleDMLStatementSQLVisitor.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleDMLStatementSQLVisitor.java
index 2b0ce33..6eda1b5 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleDMLStatementSQLVisitor.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleDMLStatementSQLVisitor.java
@@ -29,8 +29,13 @@ import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.Assign
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.AssignmentValuesContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.ColumnNameContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.ColumnNamesContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.ConditionalInsertClauseContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.ConditionalInsertElsePartContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.ConditionalInsertWhenPartContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.DeleteContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.DeleteWhereClauseContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.DmlSubqueryClauseContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.DmlTableClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.DuplicateSpecificationContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.ExprContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.ForUpdateClauseContext;
@@ -39,6 +44,9 @@ import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.FromCl
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.GroupByClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.HavingClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.InsertContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.InsertIntoClauseContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.InsertMultiTableContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.InsertSingleTableContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.InsertValuesClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.IntoClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.JoinSpecificationContext;
@@ -48,6 +56,7 @@ import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.MergeA
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.MergeContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.MergeSetAssignmentsClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.MergeUpdateClauseContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.MultiTableElementContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.MultipleTableNamesContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.MultipleTablesClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.OrderByItemContext;
@@ -63,6 +72,7 @@ import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.SetAss
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.SingleTableClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.SubqueryContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.SubqueryFactoringClauseContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.TableCollectionExprContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.TableFactorContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.TableNameContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.TableReferenceContext;
@@ -98,6 +108,7 @@ import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate.Hav
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate.LockSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate.WhereSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.AliasSegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.InsertMultiTableElementSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.OwnerSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WithSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.DeleteMultiTableSegment;
@@ -135,33 +146,143 @@ public final class OracleDMLStatementSQLVisitor extends OracleStatementSQLVisito
// TODO :FIXME, since there is no segment for insertValuesClause, InsertStatement is created by sub rule.
// TODO :deal with insert select
if (null != ctx.insertSingleTable()) {
- OracleInsertStatement result = (OracleInsertStatement) visit(ctx.insertSingleTable().insertValuesClause());
- result.setTable((SimpleTableSegment) visit(ctx.insertSingleTable().insertIntoClause().tableName()));
- result.setParameterCount(getCurrentParameterIndex());
+ OracleInsertStatement result = (OracleInsertStatement) visit(ctx.insertSingleTable());
return result;
+ } else {
+ OracleInsertStatement result = (OracleInsertStatement) visit(ctx.insertMultiTable());
+ return result;
+ }
+ }
+
+ @Override
+ public ASTNode visitInsertSingleTable(final InsertSingleTableContext ctx) {
+ OracleInsertStatement result = (OracleInsertStatement) visit(ctx.insertIntoClause());
+ if (null != ctx.insertValuesClause()) {
+ result.getValues().addAll(createInsertValuesSegments(ctx.insertValuesClause().assignmentValues()));
+ }
+ if (null != ctx.selectSubquery()) {
+ OracleSelectStatement subquery = (OracleSelectStatement) visit(ctx.selectSubquery());
+ SubquerySegment subquerySegment = new SubquerySegment(ctx.selectSubquery().start.getStartIndex(), ctx.selectSubquery().stop.getStopIndex(), subquery);
+ result.setSelectSubquery(subquerySegment);
+ }
+ result.setParameterCount(getCurrentParameterIndex());
+ return result;
+ }
+
+ private Collection<InsertValuesSegment> createInsertValuesSegments(final AssignmentValuesContext ctx) {
+ Collection<InsertValuesSegment> result = new LinkedList<>();
+ result.add((InsertValuesSegment) visit(ctx));
+ return result;
+ }
+
+ @Override
+ public ASTNode visitInsertMultiTable(final InsertMultiTableContext ctx) {
+ OracleInsertStatement result = new OracleInsertStatement();
+ if (null != ctx.conditionalInsertClause()) {
+ result.setInsertMultiTableElementSegment((InsertMultiTableElementSegment) visit(ctx.conditionalInsertClause()));
+ } else {
+ result.setInsertMultiTableElementSegment(createInsertMultiTableElementSegment(ctx.multiTableElement()));
+ }
+ OracleSelectStatement subquery = (OracleSelectStatement) visit(ctx.selectSubquery());
+ SubquerySegment subquerySegment = new SubquerySegment(ctx.selectSubquery().start.getStartIndex(), ctx.selectSubquery().stop.getStopIndex(), subquery);
+ result.setSelectSubquery(subquerySegment);
+ result.setParameterCount(getCurrentParameterIndex());
+ return result;
+ }
+
+ private InsertMultiTableElementSegment createInsertMultiTableElementSegment(final List<MultiTableElementContext> ctx) {
+ Collection<OracleInsertStatement> insertStatements = new LinkedList<>();
+ for (MultiTableElementContext each: ctx) {
+ insertStatements.add((OracleInsertStatement) visit(each));
}
- return new OracleInsertStatement();
+ InsertMultiTableElementSegment result = new InsertMultiTableElementSegment(ctx.get(0).getStart().getStartIndex(), ctx.get(ctx.size() - 1).getStop().getStopIndex());
+ result.getInsertStatements().addAll(insertStatements);
+ return result;
}
@SuppressWarnings("unchecked")
@Override
public ASTNode visitInsertValuesClause(final InsertValuesClauseContext ctx) {
OracleInsertStatement result = new OracleInsertStatement();
+ result.getValues().addAll(createInsertValuesSegments(ctx.assignmentValues()));
+ return result;
+ }
+
+ @Override
+ public ASTNode visitInsertIntoClause(final InsertIntoClauseContext ctx) {
+ OracleInsertStatement result = new OracleInsertStatement();
+ if (null != ctx.dmlTableExprClause().dmlTableClause()) {
+ result.setTable((SimpleTableSegment) visit(ctx.dmlTableExprClause().dmlTableClause()));
+ } else if (null != ctx.dmlTableExprClause().dmlSubqueryClause()) {
+ result.setInsertSelect((SubquerySegment) visit(ctx.dmlTableExprClause().dmlSubqueryClause()));
+ } else {
+ result.setInsertSelect((SubquerySegment) visit(ctx.dmlTableExprClause().tableCollectionExpr()));
+ }
if (null != ctx.columnNames()) {
ColumnNamesContext columnNames = ctx.columnNames();
CollectionValue<ColumnSegment> columnSegments = (CollectionValue<ColumnSegment>) visit(columnNames);
result.setInsertColumns(new InsertColumnsSegment(columnNames.start.getStartIndex(), columnNames.stop.getStopIndex(), columnSegments.getValue()));
} else {
- result.setInsertColumns(new InsertColumnsSegment(ctx.start.getStartIndex() - 1, ctx.start.getStartIndex() - 1, Collections.emptyList()));
+ result.setInsertColumns(new InsertColumnsSegment(ctx.stop.getStopIndex() + 1, ctx.stop.getStopIndex() + 1, Collections.emptyList()));
}
- result.getValues().addAll(createInsertValuesSegments(ctx.assignmentValues()));
return result;
}
- private Collection<InsertValuesSegment> createInsertValuesSegments(final Collection<AssignmentValuesContext> assignmentValuesContexts) {
- Collection<InsertValuesSegment> result = new LinkedList<>();
- for (AssignmentValuesContext each : assignmentValuesContexts) {
- result.add((InsertValuesSegment) visit(each));
+ @Override
+ public ASTNode visitDmlTableClause(final DmlTableClauseContext ctx) {
+ SimpleTableSegment result = (SimpleTableSegment) visit(ctx.tableName());
+ return result;
+ }
+
+ @Override
+ public ASTNode visitDmlSubqueryClause(final DmlSubqueryClauseContext ctx) {
+ OracleSelectStatement subquery = (OracleSelectStatement) visit(ctx.selectSubquery());
+ SubquerySegment result = new SubquerySegment(ctx.selectSubquery().start.getStartIndex(), ctx.selectSubquery().stop.getStopIndex(), subquery);
+ return result;
+ }
+
+ @Override
+ public ASTNode visitTableCollectionExpr(final TableCollectionExprContext ctx) {
+ OracleSelectStatement subquery = (OracleSelectStatement) visit(ctx.collectionExpr().selectSubquery());
+ SubquerySegment result = new SubquerySegment(ctx.collectionExpr().selectSubquery().start.getStartIndex(), ctx.collectionExpr().selectSubquery().stop.getStopIndex(), subquery);
+ return result;
+ }
+
+ @Override
+ public ASTNode visitConditionalInsertClause(final ConditionalInsertClauseContext ctx) {
+ Collection<OracleInsertStatement> insertStatements = new LinkedList<>();
+ for (ConditionalInsertWhenPartContext each: ctx.conditionalInsertWhenPart()) {
+ insertStatements.addAll(createInsertStatementsFromConditionalInsertWhen(each));
+ }
+ if (null != ctx.conditionalInsertElsePart()) {
+ insertStatements.addAll(createInsertStatementsFromConditionalInsertElse(ctx.conditionalInsertElsePart()));
+ }
+ InsertMultiTableElementSegment result = new InsertMultiTableElementSegment(ctx.start.getStartIndex(), ctx.stop.getStopIndex());
+ result.getInsertStatements().addAll(insertStatements);
+ return result;
+ }
+
+ private Collection<OracleInsertStatement> createInsertStatementsFromConditionalInsertWhen(final ConditionalInsertWhenPartContext ctx) {
+ Collection<OracleInsertStatement> result = new LinkedList<>();
+ for (MultiTableElementContext each: ctx.multiTableElement()) {
+ result.add((OracleInsertStatement) visit(each));
+ }
+ return result;
+ }
+
+ private Collection<OracleInsertStatement> createInsertStatementsFromConditionalInsertElse(final ConditionalInsertElsePartContext ctx) {
+ Collection<OracleInsertStatement> result = new LinkedList<>();
+ for (MultiTableElementContext each: ctx.multiTableElement()) {
+ result.add((OracleInsertStatement) visit(each));
+ }
+ return result;
+ }
+
+ @Override
+ public ASTNode visitMultiTableElement(final MultiTableElementContext ctx) {
+ OracleInsertStatement result = (OracleInsertStatement) visit(ctx.insertIntoClause());
+ if (null != ctx.insertValuesClause()) {
+ result.getValues().addAll(createInsertValuesSegments(ctx.insertValuesClause().assignmentValues()));
}
return result;
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/dml/OracleInsertStatement.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/generic/InsertMultiTableElementSegment.java
similarity index 60%
copy from shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/dml/OracleInsertStatement.java
copy to shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/generic/InsertMultiTableElementSegment.java
index 6dbdd03..7414448 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/dml/OracleInsertStatement.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/generic/InsertMultiTableElementSegment.java
@@ -15,15 +15,26 @@
* limitations under the License.
*/
-package org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.dml;
+package org.apache.shardingsphere.sql.parser.sql.common.segment.generic;
-import lombok.ToString;
+import lombok.Getter;
+import lombok.RequiredArgsConstructor;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.SQLSegment;
import org.apache.shardingsphere.sql.parser.sql.common.statement.dml.InsertStatement;
-import org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.OracleStatement;
+
+import java.util.Collection;
+import java.util.LinkedList;
/**
- * Oracle insert statement.
+ * Insert multi table element segment.
*/
-@ToString
-public final class OracleInsertStatement extends InsertStatement implements OracleStatement {
+@RequiredArgsConstructor
+@Getter
+public final class InsertMultiTableElementSegment implements SQLSegment {
+
+ private final int startIndex;
+
+ private final int stopIndex;
+
+ private final Collection<InsertStatement> insertStatements = new LinkedList<>();
}
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 dc9173d..ce06bf3 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
@@ -21,12 +21,16 @@ import lombok.AccessLevel;
import lombok.NoArgsConstructor;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.assignment.SetAssignmentSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.OnDuplicateKeyColumnsSegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.subquery.SubquerySegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.InsertMultiTableElementSegment;
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.InsertStatement;
import org.apache.shardingsphere.sql.parser.sql.dialect.handler.SQLStatementHandler;
import org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.MySQLStatement;
import org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dml.MySQLInsertStatement;
+import org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.OracleStatement;
+import org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.dml.OracleInsertStatement;
import org.apache.shardingsphere.sql.parser.sql.dialect.statement.postgresql.PostgreSQLStatement;
import org.apache.shardingsphere.sql.parser.sql.dialect.statement.postgresql.dml.PostgreSQLInsertStatement;
import org.apache.shardingsphere.sql.parser.sql.dialect.statement.sqlserver.SQLServerStatement;
@@ -94,4 +98,30 @@ public final class InsertStatementHandler implements SQLStatementHandler {
}
return Optional.empty();
}
+
+ /**
+ * Get insert multi table element segment.
+ *
+ * @param insertStatement insert statement
+ * @return insert multi table element segment
+ */
+ public static Optional<InsertMultiTableElementSegment> getInsertMultiTableElementSegment(final InsertStatement insertStatement) {
+ if (insertStatement instanceof OracleStatement) {
+ return ((OracleInsertStatement) insertStatement).getInsertMultiTableElementSegment();
+ }
+ return Optional.empty();
+ }
+
+ /**
+ * Get select subquery segment.
+ *
+ * @param insertStatement insert statement
+ * @return select subquery segment
+ */
+ public static Optional<SubquerySegment> getSelectSubquery(final InsertStatement insertStatement) {
+ if (insertStatement instanceof OracleStatement) {
+ return ((OracleInsertStatement) insertStatement).getSelectSubquery();
+ }
+ return Optional.empty();
+ }
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/dml/OracleInsertStatement.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/dml/OracleInsertStatement.java
index 6dbdd03..7531dde 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/dml/OracleInsertStatement.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/dml/OracleInsertStatement.java
@@ -17,13 +17,41 @@
package org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.dml;
+import lombok.Setter;
import lombok.ToString;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.subquery.SubquerySegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.InsertMultiTableElementSegment;
import org.apache.shardingsphere.sql.parser.sql.common.statement.dml.InsertStatement;
import org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.OracleStatement;
+import java.util.Optional;
+
/**
* Oracle insert statement.
*/
+@Setter
@ToString
public final class OracleInsertStatement extends InsertStatement implements OracleStatement {
+
+ private SubquerySegment selectSubquery;
+
+ private InsertMultiTableElementSegment insertMultiTableElementSegment;
+
+ /**
+ * Get insert select segment.
+ *
+ * @return insert select segment
+ */
+ public Optional<SubquerySegment> getSelectSubquery() {
+ return Optional.ofNullable(selectSubquery);
+ }
+
+ /**
+ * Get insert multi table element segment.
+ *
+ * @return insert select segment
+ */
+ public Optional<InsertMultiTableElementSegment> getInsertMultiTableElementSegment() {
+ return Optional.ofNullable(insertMultiTableElementSegment);
+ }
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/segment/insert/InsertMultiTableElementAssert.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/segment/insert/InsertMultiTableElementAssert.java
new file mode 100644
index 0000000..af1cd91
--- /dev/null
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/segment/insert/InsertMultiTableElementAssert.java
@@ -0,0 +1,54 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.insert;
+
+import lombok.AccessLevel;
+import lombok.NoArgsConstructor;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.InsertMultiTableElementSegment;
+import org.apache.shardingsphere.sql.parser.sql.common.statement.dml.InsertStatement;
+import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.SQLCaseAssertContext;
+import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.SQLSegmentAssert;
+import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.statement.dml.impl.InsertStatementAssert;
+import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.insert.ExpectedInsertMultiTableElement;
+
+import static org.hamcrest.CoreMatchers.is;
+import static org.junit.Assert.assertThat;
+
+/**
+ * Insert multi table element assert.
+ */
+@NoArgsConstructor(access = AccessLevel.PRIVATE)
+public final class InsertMultiTableElementAssert {
+
+ /**
+ * Assert actual insert multi table element segment is correct with expected multi table element.
+ *
+ * @param assertContext assert context
+ * @param actual actual insert multi table element
+ * @param expected expected insert multi table element
+ */
+ public static void assertIs(final SQLCaseAssertContext assertContext, final InsertMultiTableElementSegment actual, final ExpectedInsertMultiTableElement expected) {
+ assertThat(assertContext.getText("Insert values size assertion error: "), actual.getInsertStatements().size(), is(expected.getInsertTestCases().size()));
+ int count = 0;
+ for (InsertStatement each : actual.getInsertStatements()) {
+ InsertStatementAssert.assertIs(assertContext, each, expected.getInsertTestCases().get(count));
+ SQLSegmentAssert.assertIs(assertContext, actual, expected);
+ count++;
+ }
+ }
+}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/statement/dml/impl/InsertStatementAssert.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/statement/dml/impl/InsertStatementAssert.java
index 85a46d6..019fe54 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/statement/dml/impl/InsertStatementAssert.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/statement/dml/impl/InsertStatementAssert.java
@@ -21,12 +21,15 @@ import lombok.AccessLevel;
import lombok.NoArgsConstructor;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.assignment.SetAssignmentSegment;
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.segment.dml.expr.subquery.SubquerySegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.InsertMultiTableElementSegment;
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.InsertStatement;
import org.apache.shardingsphere.sql.parser.sql.dialect.handler.dml.InsertStatementHandler;
import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.SQLCaseAssertContext;
import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.insert.InsertColumnsClauseAssert;
+import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.insert.InsertMultiTableElementAssert;
import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.insert.InsertValuesClauseAssert;
import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.insert.OnDuplicateKeyColumnsAssert;
import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.output.OutputClauseAssert;
@@ -38,6 +41,7 @@ import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain
import java.util.Optional;
import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertNull;
import static org.junit.Assert.assertTrue;
/**
@@ -62,10 +66,16 @@ public final class InsertStatementAssert {
assertOnDuplicateKeyColumns(assertContext, actual, expected);
assertWithClause(assertContext, actual, expected);
assertOutputClause(assertContext, actual, expected);
+ assertInsertMultiTableElement(assertContext, actual, expected);
+ assertSelectSubqueryClause(assertContext, actual, expected);
}
private static void assertTable(final SQLCaseAssertContext assertContext, final InsertStatement actual, final InsertStatementTestCase expected) {
- TableAssert.assertIs(assertContext, actual.getTable(), expected.getTable());
+ if (null != expected.getTable()) {
+ TableAssert.assertIs(assertContext, actual.getTable(), expected.getTable());
+ } else {
+ assertNull(assertContext.getText("Actual table should not exist."), actual.getTable());
+ }
}
private static void assertInsertColumnsClause(final SQLCaseAssertContext assertContext, final InsertStatement actual, final InsertStatementTestCase expected) {
@@ -134,4 +144,24 @@ public final class InsertStatementAssert {
assertFalse(assertContext.getText("Actual output segment should not exist."), outputSegment.isPresent());
}
}
+
+ private static void assertInsertMultiTableElement(final SQLCaseAssertContext assertContext, final InsertStatement actual, final InsertStatementTestCase expected) {
+ Optional<InsertMultiTableElementSegment> insertTableElementSegment = InsertStatementHandler.getInsertMultiTableElementSegment(actual);
+ if (null != expected.getInsertTableElement()) {
+ assertTrue(assertContext.getText("Actual insert multi table element segment should exist."), insertTableElementSegment.isPresent());
+ InsertMultiTableElementAssert.assertIs(assertContext, insertTableElementSegment.get(), expected.getInsertTableElement());
+ } else {
+ assertFalse(assertContext.getText("Actual insert multi table element segment should not exist."), insertTableElementSegment.isPresent());
+ }
+ }
+
+ private static void assertSelectSubqueryClause(final SQLCaseAssertContext assertContext, final InsertStatement actual, final InsertStatementTestCase expected) {
+ Optional<SubquerySegment> selectSubquery = InsertStatementHandler.getSelectSubquery(actual);
+ if (null != expected.getSelectSubquery()) {
+ assertTrue(assertContext.getText("Actual select subquery segment should exist."), selectSubquery.isPresent());
+ SelectStatementAssert.assertIs(assertContext, selectSubquery.get().getSelect(), expected.getSelectSubquery());
+ } else {
+ assertFalse(assertContext.getText("Actual select subquery segment should not exist."), selectSubquery.isPresent());
+ }
+ }
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/dml/OracleInsertStatement.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/segment/impl/insert/ExpectedInsertMultiTableElement.java
similarity index 53%
copy from shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/dml/OracleInsertStatement.java
copy to shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/segment/impl/insert/ExpectedInsertMultiTableElement.java
index 6dbdd03..45843c6 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/dml/OracleInsertStatement.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/segment/impl/insert/ExpectedInsertMultiTableElement.java
@@ -15,15 +15,22 @@
* limitations under the License.
*/
-package org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.dml;
+package org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.insert;
-import lombok.ToString;
-import org.apache.shardingsphere.sql.parser.sql.common.statement.dml.InsertStatement;
-import org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.OracleStatement;
+import lombok.Getter;
+import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.AbstractExpectedSQLSegment;
+import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.statement.dml.InsertStatementTestCase;
+
+import javax.xml.bind.annotation.XmlElement;
+import java.util.LinkedList;
+import java.util.List;
/**
- * Oracle insert statement.
+ * Expected insert multi table element.
*/
-@ToString
-public final class OracleInsertStatement extends InsertStatement implements OracleStatement {
+@Getter
+public final class ExpectedInsertMultiTableElement extends AbstractExpectedSQLSegment {
+
+ @XmlElement(name = "table-element")
+ private List<InsertStatementTestCase> insertTestCases = new LinkedList<>();
}
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/InsertStatementTestCase.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/statement/dml/InsertStatementTestCase.java
index 99ec6e1..faefc19 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/statement/dml/InsertStatementTestCase.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/statement/dml/InsertStatementTestCase.java
@@ -20,12 +20,13 @@ package org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domai
import lombok.Getter;
import lombok.Setter;
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.insert.ExpectedInsertColumnsClause;
+import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.insert.ExpectedInsertMultiTableElement;
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.insert.ExpectedInsertValuesClause;
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.insert.ExpectedOnDuplicateKeyColumns;
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.with.ExpectedWithClause;
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.set.ExpectedSetClause;
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;
@@ -60,4 +61,10 @@ public final class InsertStatementTestCase extends SQLParserTestCase {
@XmlElement(name = "output")
private ExpectedOutputClause outputClause;
+
+ @XmlElement(name = "multi-table-element")
+ private ExpectedInsertMultiTableElement insertTableElement;
+
+ @XmlElement(name = "select-subquery")
+ private SelectStatementTestCase selectSubquery;
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/insert.xml b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/insert.xml
index dba938f..22ed987 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/insert.xml
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/insert.xml
@@ -1610,4 +1610,218 @@
</value>
</values>
</insert>
+
+ <insert sql-case-id="insert_without_columns">
+ <table name="departments" start-index="12" stop-index="22" />
+ <columns start-index="23" stop-index="23" />
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="280" start-index="32" stop-index="34" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Recreation" start-index="37" stop-index="48" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="121" start-index="51" stop-index="53" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="1700" start-index="56" stop-index="59" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+
+ <insert sql-case-id="insert_with_dml_table_expr_select">
+ <select>
+ <from>
+ <simple-table name="employees" start-index="98" stop-index="106" />
+ </from>
+ <projections start-index="20" stop-index="91">
+ <column-projection name="employee_id" start-index="20" stop-index="30" />
+ <column-projection name="last_name" start-index="33" stop-index="41" />
+ <column-projection name="email" start-index="44" stop-index="48" />
+ <column-projection name="hire_date" start-index="51" stop-index="59" />
+ <column-projection name="job_id" start-index="62" stop-index="67" />
+ <column-projection name="salary" start-index="70" stop-index="75" />
+ <column-projection name="commission_pct" start-index="78" stop-index="91" />
+ </projections>
+ </select>
+ <columns start-index="108" stop-index="108" />
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="207" start-index="117" stop-index="119" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Gregory" start-index="122" stop-index="130" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="pgregory@example.com" start-index="133" stop-index="154" />
+ </assignment-value>
+ <assignment-value>
+ <column name="sysdate" start-index="157" stop-index="163" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="PU_CLERK" start-index="166" stop-index="175" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="1.2E+3" start-index="178" stop-index="182" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="185..188" start-index="185" stop-index="188" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+
+ <insert sql-case-id="insert_with_select_subquery">
+ <table name="bonuses" start-index="12" stop-index="18" />
+ <columns start-index="19" stop-index="19" />
+ <select-subquery>
+ <from>
+ <simple-table name="employees" start-index="56" stop-index="64" />
+ </from>
+ <projections start-index="27" stop-index="49">
+ <column-projection name="employee_id" start-index="27" stop-index="37" />
+ <expression-projection text="salary*1.1" start-index="40" stop-index="49" />
+ </projections>
+ <where start-index="66" stop-index="92">
+ <expr>
+ <binary-operation-expression start-index="72" stop-index="92">
+ <left>
+ <column name="commission_pct" start-index="72" stop-index="85" />
+ </left>
+ <operator>></operator>
+ <right>
+ <literal-expression value="0.25" start-index="89" stop-index="92" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select-subquery>
+ </insert>
+
+ <insert sql-case-id="insert_with_multitable_element">
+ <multi-table-element start-index="11" stop-index="231">
+ <table-element>
+ <table name="sales" start-index="16" stop-index="20" />
+ <columns start-index="22" stop-index="56">
+ <column name="prod_id" start-index="23" stop-index="29" />
+ <column name="cust_id" start-index="32" stop-index="38" />
+ <column name="time_id" start-index="41" stop-index="47" />
+ <column name="amount" start-index="50" stop-index="55" />
+ </columns>
+ <values>
+ <value>
+ <assignment-value>
+ <column name="product_id" start-index="66" stop-index="75" />
+ </assignment-value>
+ <assignment-value>
+ <column name="customer_id" start-index="78" stop-index="88" />
+ </assignment-value>
+ <assignment-value>
+ <column name="weekly_start_date" start-index="91" stop-index="107" />
+ </assignment-value>
+ <assignment-value>
+ <column name="sales_sun" start-index="110" stop-index="118" />
+ </assignment-value>
+ </value>
+ </values>
+ </table-element>
+ <table-element>
+ <table name="sales" start-index="126" stop-index="130" />
+ <columns start-index="132" stop-index="166">
+ <column name="prod_id" start-index="133" stop-index="139" />
+ <column name="cust_id" start-index="142" stop-index="148" />
+ <column name="time_id" start-index="151" stop-index="157" />
+ <column name="amount" start-index="160" stop-index="165" />
+ </columns>
+ <values>
+ <value>
+ <assignment-value>
+ <column name="product_id" start-index="176" stop-index="185" />
+ </assignment-value>
+ <assignment-value>
+ <column name="customer_id" start-index="188" stop-index="198" />
+ </assignment-value>
+ <assignment-value>
+ <column name="sales_mon" start-index="201" stop-index="209" />
+ </assignment-value>
+ <assignment-value>
+ <common-expression text="weekly_start_date+1" start-index="212" stop-index="230" />
+ </assignment-value>
+ </value>
+ </values>
+ </table-element>
+ </multi-table-element>
+ <select-subquery>
+ <from>
+ <simple-table name="sales_input_table" start-index="310" stop-index="326" />
+ </from>
+ <projections start-index="240" stop-index="303">
+ <column-projection name="product_id" start-index="240" stop-index="249" />
+ <column-projection name="customer_id" start-index="252" stop-index="262" />
+ <column-projection name="weekly_start_date" start-index="265" stop-index="281" />
+ <column-projection name="sales_sun" start-index="284" stop-index="292" />
+ <column-projection name="sales_mon" start-index="295" stop-index="303" />
+ </projections>
+ </select-subquery>
+ </insert>
+
+ <insert sql-case-id="insert_all_with_multitable_with_conditional_when">
+ <multi-table-element start-index="7" stop-index="185">
+ <table-element>
+ <table name="small_orders" start-index="48" stop-index="59" />
+ <columns start-index="60" stop-index="60" />
+ </table-element>
+ <table-element>
+ <table name="medium_orders" start-index="124" stop-index="136" />
+ <columns start-index="137" stop-index="137" />
+ </table-element>
+ <table-element>
+ <table name="large_orders" start-index="174" stop-index="185" />
+ <columns start-index="186" stop-index="186" />
+ </table-element>
+ </multi-table-element>
+ <select-subquery>
+ <from>
+ <simple-table name="orders" start-index="248" stop-index="253" />
+ </from>
+ <projections start-index="194" stop-index="241">
+ <column-projection name="order_id" start-index="194" stop-index="201" />
+ <column-projection name="order_total" start-index="204" stop-index="214" />
+ <column-projection name="sales_rep_id" start-index="217" stop-index="228" />
+ <column-projection name="customer_id" start-index="231" stop-index="241" />
+ </projections>
+ </select-subquery>
+ </insert>
+
+ <insert sql-case-id="insert_all_with_multitable_with_conditional_when_with_conditional_else">
+ <multi-table-element start-index="7" stop-index="158">
+ <table-element>
+ <table name="small_orders" start-index="48" stop-index="59" />
+ <columns start-index="60" stop-index="60" />
+ </table-element>
+ <table-element>
+ <table name="medium_orders" start-index="123" stop-index="135" />
+ <columns start-index="136" stop-index="136" />
+ </table-element>
+ <table-element>
+ <table name="large_orders" start-index="147" stop-index="158" />
+ <columns start-index="159" stop-index="159" />
+ </table-element>
+ </multi-table-element>
+ <select-subquery>
+ <from>
+ <simple-table name="orders" start-index="221" stop-index="226" />
+ </from>
+ <projections start-index="167" stop-index="214">
+ <column-projection name="order_id" start-index="167" stop-index="174" />
+ <column-projection name="order_total" start-index="177" stop-index="187" />
+ <column-projection name="sales_rep_id" start-index="190" stop-index="201" />
+ <column-projection name="customer_id" start-index="204" stop-index="214" />
+ </projections>
+ </select-subquery>
+ </insert>
</sql-parser-test-cases>
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/insert.xml b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/insert.xml
index 2da1526..0e9be8c 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/insert.xml
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/insert.xml
@@ -34,11 +34,11 @@
<sql-case id="insert_without_columns_and_with_generate_key_column" value="INSERT INTO t_order_item values(?, ?, ?, 'insert', '2017-08-08')" />
<sql-case id="insert_without_columns_and_without_generate_key_column" value="INSERT INTO t_order_item values(?, ?, 'insert', '2017-08-08')" />
<sql-case id="insert_set_without_generate_key_column" value="INSERT INTO t_order_item SET order_id = ?, user_id = ?, status = 'insert', creation_date='2017-08-08'" db-types="MySQL" />
- <sql-case id="insert_with_batch" value="INSERT INTO t_order (order_id, user_id, status) VALUES (?, ?, ?), (?, ?, ?)" />
- <sql-case id="insert_with_batch_and_irregular_parameters" value="INSERT INTO t_order (order_id, user_id, status) VALUES (?, 1, 'insert'), (?, ?, ?)" />
+ <sql-case id="insert_with_batch" value="INSERT INTO t_order (order_id, user_id, status) VALUES (?, ?, ?), (?, ?, ?)" db-types="MySQL, SQLServer, PostgreSQL"/>
+ <sql-case id="insert_with_batch_and_irregular_parameters" value="INSERT INTO t_order (order_id, user_id, status) VALUES (?, 1, 'insert'), (?, ?, ?)" db-types="MySQL, SQLServer, PostgreSQL"/>
<sql-case id="insert_with_batch_and_composite_expression" value="INSERT INTO t_order (order_id, user_id, status) VALUES (?, ?, SUBSTR(?, 1)), (?, ?, SUBSTR(?, 1))" db-types="H2,MySQL" />
- <sql-case id="insert_with_batch_and_with_generate_key_column" value="INSERT INTO t_order_item(item_id, order_id, user_id, status, creation_date) values (?, ?, ?, 'insert', '2017-08-08'), (?, ?, ?, 'insert', '2017-08-08')" />
- <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')" />
+ <sql-case id="insert_with_batch_and_with_generate_key_column" value="INSERT INTO t_order_item(item_id, order_id, user_id, status, creation_date) values (?, ?, ?, 'insert', '2017-08-08'), (?, ?, ?, 'insert', '2017-08-08')" db-types="MySQL, SQLServer, PostgreSQL"/>
+ <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"/>
<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" />
<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" />
<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"/>
@@ -76,4 +76,10 @@
<sql-case id="insert_with_output_clause_without_output_table_columns" value="INSERT INTO t_order (order_id, user_id) OUTPUT INSERTED.order_id, INSERTED.user_id INTO @MyTableVar VALUES (?, ?)" db-types="SQLServer" />
<sql-case id="insert_with_output_clause_without_output_table" value="INSERT INTO t_order (order_id, user_id) OUTPUT INSERTED.order_id, INSERTED.user_id VALUES (?, ?)" db-types="SQLServer" />
<sql-case id="insert_with_output_clause_column_shorthand" value="INSERT INTO t_order (order_id, user_id) OUTPUT INSERTED.* VALUES (?, ?)" db-types="SQLServer" />
+ <sql-case id="insert_without_columns" value="INSERT INTO departments VALUES (280, 'Recreation', 121, 1700)" db-types="Oracle" />
+ <sql-case id="insert_with_dml_table_expr_select" value="INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary, commission_pct FROM employees) VALUES (207, 'Gregory', 'pgregory@example.com', sysdate, 'PU_CLERK', 1.2E3, NULL)" db-types="Oracle" />
+ <sql-case id="insert_with_select_subquery" value="INSERT INTO bonuses SELECT employee_id, salary*1.1 FROM employees WHERE commission_pct > 0.25" db-types="Oracle" />
+ <sql-case id="insert_with_multitable_element" value="INSERT ALL INTO sales (prod_id, cust_id, time_id, amount) VALUES (product_id, customer_id, weekly_start_date, sales_sun) INTO sales (prod_id, cust_id, time_id, amount) VALUES (product_id, customer_id, sales_mon, weekly_start_date+1) SELECT product_id, customer_id, weekly_start_date, sales_sun, sales_mon FROM sales_input_table" db-types="Oracle" />
+ <sql-case id="insert_all_with_multitable_with_conditional_when" value="INSERT ALL WHEN order_total <= 100000 THEN INTO small_orders WHEN order_total > 1000000 AND order_total <= 200000 THEN INTO medium_orders WHEN order_total > 200000 THEN INTO large_orders SELECT order_id, order_total, sales_rep_id, customer_id FROM orders" db-types="Oracle" />
+ <sql-case id="insert_all_with_multitable_with_conditional_when_with_conditional_else" value="INSERT ALL WHEN order_total <= 100000 THEN INTO small_orders WHEN order_total > 100000 AND order_total <= 200000 THEN INTO medium_orders ELSE INTO large_orders SELECT order_id, order_total, sales_rep_id, customer_id FROM orders" db-types="Oracle" />
</sql-cases>