You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by ni...@apache.org on 2023/12/12 03:23:44 UTC
(shardingsphere) branch master updated: Support parsing SQL Server INSERT INTO sql #29152 (#29329)
This is an automated email from the ASF dual-hosted git repository.
niuzihao 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 bedd9ca2a3d Support parsing SQL Server INSERT INTO sql #29152 (#29329)
bedd9ca2a3d is described below
commit bedd9ca2a3df184bad14981e9845dbce7e0a36ce
Author: LotusMoon <ch...@aliyun.com>
AuthorDate: Tue Dec 12 11:23:37 2023 +0800
Support parsing SQL Server INSERT INTO sql #29152 (#29329)
* Support parsing SQL Server INSERT INTO sql(#29152)
* Fix the oracle parser error test
* solve this nchar problem by adjusting Oracle's lexical or grammatical rules
* pass the spotless:apply check
* Fix PostgreSQL E2E error
---
.../src/main/antlr4/imports/oracle/BaseRule.g4 | 2 +
.../src/main/antlr4/imports/oracle/Literals.g4 | 10 +++-
.../visitor/statement/OracleStatementVisitor.java | 6 +-
.../src/main/antlr4/imports/sqlserver/BaseRule.g4 | 9 ++-
.../src/main/antlr4/imports/sqlserver/Symbol.g4 | 1 +
.../statement/SQLServerStatementVisitor.java | 29 +++++++++-
.../parser/src/main/resources/case/dml/insert.xml | 67 +++++++++++++++++++++-
.../parser/src/main/resources/case/dml/select.xml | 39 +++++++++++--
.../main/resources/sql/supported/dml/insert.xml | 4 ++
.../main/resources/sql/supported/dml/select.xml | 1 +
10 files changed, 154 insertions(+), 14 deletions(-)
diff --git a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4 b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
index e11ae2d0bc6..8472d58648d 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
@@ -48,6 +48,8 @@ intervalUnit
stringLiterals
: STRING_
+ | NCHAR_TEXT
+ | UCHAR_TEXT
;
numberLiterals
diff --git a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/Literals.g4 b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/Literals.g4
index 9027672dcd3..03936901a58 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/Literals.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/Literals.g4
@@ -28,7 +28,7 @@ IDENTIFIER_
;
STRING_
- : (N | U)? SINGLE_QUOTED_TEXT
+ : SINGLE_QUOTED_TEXT
;
SINGLE_QUOTED_TEXT
@@ -55,6 +55,14 @@ BIT_NUM_
: '0b' ('0' | '1')+ | B SQ_ ('0' | '1')+ SQ_
;
+NCHAR_TEXT
+ : N STRING_
+ ;
+
+UCHAR_TEXT
+ : U STRING_
+ ;
+
fragment INT_
: [0-9]+
;
diff --git a/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java b/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
index 5f9c266aa2a..e13066d1237 100644
--- a/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
+++ b/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
@@ -238,7 +238,11 @@ public abstract class OracleStatementVisitor extends OracleStatementBaseVisitor<
@Override
public final ASTNode visitStringLiterals(final StringLiteralsContext ctx) {
- return new StringLiteralValue(ctx.getText());
+ if (null != ctx.STRING_()) {
+ return new StringLiteralValue(ctx.getText());
+ } else {
+ return new StringLiteralValue(ctx.getText().substring(1));
+ }
}
@Override
diff --git a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/BaseRule.g4 b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/BaseRule.g4
index f8f3bfb59f0..e493579b65e 100644
--- a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/BaseRule.g4
+++ b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/BaseRule.g4
@@ -35,6 +35,7 @@ literals
stringLiterals
: STRING_
+ | NCHAR_TEXT
;
numberLiterals
@@ -151,7 +152,7 @@ sequenceName
;
tableName
- : (owner DOT_)? name
+ : (databaseName DOT_ (owner DOT_) ? | (owner DOT_) ?) name
;
queueName
@@ -167,7 +168,11 @@ serviceName
;
columnName
- : (owner DOT_)? name
+ : (owner DOT_)? (name | scriptVariableName)
+ ;
+
+scriptVariableName
+ : DOLLAR_ LP_ name RP_
;
owner
diff --git a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/Symbol.g4 b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/Symbol.g4
index da742daf655..5a0d8f39b4c 100644
--- a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/Symbol.g4
+++ b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/Symbol.g4
@@ -57,3 +57,4 @@ BQ_: '`';
QUESTION_: '?';
AT_: '@';
SEMI_: ';';
+DOLLAR_: '$';
diff --git a/parser/sql/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/SQLServerStatementVisitor.java b/parser/sql/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/SQLServerStatementVisitor.java
index fed3241059e..3fafe09b496 100644
--- a/parser/sql/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/SQLServerStatementVisitor.java
+++ b/parser/sql/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/SQLServerStatementVisitor.java
@@ -25,6 +25,7 @@ import org.antlr.v4.runtime.misc.Interval;
import org.antlr.v4.runtime.tree.TerminalNode;
import org.apache.shardingsphere.sql.parser.api.ASTNode;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementBaseVisitor;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.AggregationClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.AggregationFunctionContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.AliasContext;
@@ -228,7 +229,11 @@ public abstract class SQLServerStatementVisitor extends SQLServerStatementBaseVi
@Override
public final ASTNode visitStringLiterals(final StringLiteralsContext ctx) {
- return new StringLiteralValue(ctx.getText());
+ if (null != ctx.STRING_()) {
+ return new StringLiteralValue(ctx.getText());
+ } else {
+ return new StringLiteralValue(ctx.getText().substring(1));
+ }
}
@Override
@@ -289,14 +294,27 @@ public abstract class SQLServerStatementVisitor extends SQLServerStatementBaseVi
SimpleTableSegment result = new SimpleTableSegment(new TableNameSegment(ctx.name().getStart().getStartIndex(), ctx.name().getStop().getStopIndex(), (IdentifierValue) visit(ctx.name())));
OwnerContext owner = ctx.owner();
if (null != owner) {
- result.setOwner(new OwnerSegment(owner.getStart().getStartIndex(), owner.getStop().getStopIndex(), (IdentifierValue) visit(owner.identifier())));
+ OwnerSegment ownerSegment = new OwnerSegment(owner.getStart().getStartIndex(), owner.getStop().getStopIndex(), (IdentifierValue) visit(owner.identifier()));
+ if (null != ctx.databaseName()) {
+ SQLServerStatementParser.DatabaseNameContext dbName = ctx.databaseName();
+ ownerSegment.setOwner(new OwnerSegment(dbName.getStart().getStartIndex(), dbName.getStop().getStopIndex(), (IdentifierValue) visit(dbName.identifier())));
+ }
+ result.setOwner(ownerSegment);
+ } else if (null != ctx.databaseName()) {
+ SQLServerStatementParser.DatabaseNameContext dbName = ctx.databaseName();
+ result.setOwner(new OwnerSegment(dbName.getStart().getStartIndex(), dbName.getStop().getStopIndex(), (IdentifierValue) visit(dbName.identifier())));
}
return result;
}
@Override
public final ASTNode visitColumnName(final ColumnNameContext ctx) {
- ColumnSegment result = new ColumnSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), (IdentifierValue) visit(ctx.name()));
+ ColumnSegment result;
+ if (null != ctx.name()) {
+ result = new ColumnSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), (IdentifierValue) visit(ctx.name()));
+ } else {
+ result = new ColumnSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), (IdentifierValue) visit(ctx.scriptVariableName()));
+ }
OwnerContext owner = ctx.owner();
if (null != owner) {
result.setOwner(new OwnerSegment(owner.getStart().getStartIndex(), owner.getStop().getStopIndex(), (IdentifierValue) visit(owner.identifier())));
@@ -304,6 +322,11 @@ public abstract class SQLServerStatementVisitor extends SQLServerStatementBaseVi
return result;
}
+ @Override
+ public ASTNode visitScriptVariableName(final SQLServerStatementParser.ScriptVariableNameContext ctx) {
+ return new IdentifierValue(ctx.getText());
+ }
+
@Override
public final ASTNode visitIndexName(final IndexNameContext ctx) {
IndexNameSegment indexName = new IndexNameSegment(ctx.start.getStartIndex(), ctx.stop.getStopIndex(), (IdentifierValue) visit(ctx.identifier()));
diff --git a/test/it/parser/src/main/resources/case/dml/insert.xml b/test/it/parser/src/main/resources/case/dml/insert.xml
index f9bae458b1a..0969b479f28 100644
--- a/test/it/parser/src/main/resources/case/dml/insert.xml
+++ b/test/it/parser/src/main/resources/case/dml/insert.xml
@@ -2699,7 +2699,7 @@
</function>
</assignment-value>
<assignment-value>
- <literal-expression value="'500 Oracle Parkway" start-index="59" stop-index="79" literal-start-index="59" literal-stop-index="79" />
+ <literal-expression value="500 Oracle Parkway" start-index="60" stop-index="79" literal-start-index="59" literal-stop-index="79" />
</assignment-value>
<assignment-value>
<column name="sysdate" start-index="81" stop-index="87" literal-start-index="81" literal-stop-index="87" />
@@ -2900,4 +2900,69 @@
</from>
</select>
</insert>
+ <insert sql-case-id="insert_with_nchar_1">
+ <table name="T1" start-index="12" stop-index="17">
+ <owner name="dbo" start-index="12" stop-index="14" />
+ </table>
+ <columns start-index="18" stop-index="18" />
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="1" start-index="27" stop-index="27" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Natalia" start-index="30" stop-index="39" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+ <insert sql-case-id="insert_with_nchar_2">
+ <table name="T1" start-index="12" stop-index="17">
+ <owner name="dbo" start-index="12" stop-index="14" />
+ </table>
+ <columns start-index="18" stop-index="18" />
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="2" start-index="27" stop-index="27" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Mark" start-index="30" stop-index="36" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+ <insert sql-case-id="insert_with_nchar_3">
+ <table name="T1" start-index="12" stop-index="17">
+ <owner name="dbo" start-index="12" stop-index="14" />
+ </table>
+ <columns start-index="18" stop-index="18" />
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="3" start-index="27" stop-index="27" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Randolph" start-index="30" stop-index="40" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+ <insert sql-case-id="insert_with_dbName">
+ <table name="VariableTest" start-index="12" stop-index="46">
+ <owner name="dbo" start-index="31" stop-index="33">
+ <owner name="AdventureWorks2022" start-index="12" stop-index="29"/>
+ </owner>
+ </table>
+ <columns start-index="47" stop-index="52">
+ <column name="Col1" start-index="48" stop-index="51" />
+ </columns>
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="$(tablename)" start-index="61" stop-index="74" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/dml/select.xml b/test/it/parser/src/main/resources/case/dml/select.xml
index 4068b52514b..506e3db1f03 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -7307,19 +7307,19 @@
<select sql-case-id="select_with_unicode_string">
<projections start-index="7" stop-index="22" literal-start-index="7" literal-stop-index="22">
- <expression-projection text="'test" start-index="7" stop-index="13" literal-start-index="7"
+ <expression-projection text="test" start-index="7" stop-index="13" literal-start-index="7"
literal-stop-index="13">
- <literalText>'test</literalText>
+ <literalText>test</literalText>
<expr>
- <literal-expression value="'test" start-index="7" stop-index="13" literal-start-index="7"
+ <literal-expression value="test" start-index="7" stop-index="13" literal-start-index="7"
literal-stop-index="13"/>
</expr>
</expression-projection>
- <expression-projection text="'test" start-index="16" stop-index="22" literal-start-index="16"
+ <expression-projection text="test" start-index="16" stop-index="22" literal-start-index="16"
literal-stop-index="22">
- <literalText>'test</literalText>
+ <literalText>test</literalText>
<expr>
- <literal-expression value="'test" start-index="16" stop-index="22" literal-start-index="16"
+ <literal-expression value="test" start-index="16" stop-index="22" literal-start-index="16"
literal-stop-index="22"/>
</expr>
</expression-projection>
@@ -7498,4 +7498,31 @@
<column-item name="updatable" order-direction="ASC" start-index="116" stop-index="124" />
</order-by>
</select>
+ <select sql-case-id="select_with_script_variables">
+ <from>
+ <simple-table name="Person" alias="x" start-index="28" stop-index="42">
+ <owner name="Person" start-index="28" stop-index="33"/>
+ </simple-table>
+ </from>
+ <projections start-index="7" stop-index="21">
+ <column-projection name="$(ColumnName)" start-index="7" stop-index="21">
+ <owner name="x" start-index="7" stop-index="7" />
+ </column-projection>>
+ </projections>
+ <where start-index="44" stop-index="71">
+ <expr>
+ <binary-operation-expression start-index="50" stop-index="71">
+ <left>
+ <column name="BusinessEntityID" start-index="50" stop-index="67">
+ <owner name="x" start-index="50" stop-index="50" />
+ </column>
+ </left>
+ <operator>></operator>
+ <right>
+ <literal-expression value="5" start-index="71" stop-index="71" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/insert.xml b/test/it/parser/src/main/resources/sql/supported/dml/insert.xml
index a1a9d530623..7f83dbbb58a 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/insert.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/insert.xml
@@ -103,4 +103,8 @@
<sql-case id="insert_with_national_character_set" value="INSERT INTO customers VALUES (1000, TO_NCHAR('John Smith'),N'500 Oracle Parkway',sysdate);" db-types="Oracle" />
<sql-case id="insert_with_oracle_datetime_type" value="INSERT INTO t_order (create_date, create_timestamp, create_interval_year, create_interval_day) VALUES (TO_DATE('2009', 'YYYY'), TO_DATE('2009', 'YYYY'), (TO_DATE('2009', 'YYYY') - TO_DATE('2009', 'YYYY')) year to MONTH, (TO_DATE('2009', 'YYYY') - TO_DATE('2009', 'YYYY')) DAY TO SECOND);" db-types="Oracle" />
<sql-case id="insert_all_into" value="INSERT ALL INTO T_MASK(ID,EMAIL,NAME,PHONE,ADDRESS) VALUES (1,'2','3','4','5') INTO T_MASK(ID,EMAIL,NAME,PHONE,ADDRESS) VALUES (2,'2','3','4','5') INTO T_MASK(ID,EMAIL,NAME,PHONE,ADDRESS) VALUES (3,'2','3','4','5') SELECT 1 FROM DUAL" db-types="Oracle" />
+ <sql-case id="insert_with_nchar_1" value="INSERT INTO dbo.T1 VALUES (1, N'Natalia')" db-types="SQLServer"/>
+ <sql-case id="insert_with_nchar_2" value="INSERT INTO dbo.T1 VALUES (2, N'Mark')" db-types="SQLServer"/>
+ <sql-case id="insert_with_nchar_3" value="INSERT INTO dbo.T1 VALUES (3, N'Randolph')" db-types="SQLServer"/>
+ <sql-case id="insert_with_dbName" value="INSERT INTO AdventureWorks2022.dbo.VariableTest(Col1) VALUES('$(tablename)')" db-types="SQLServer"/>
</sql-cases>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/select.xml b/test/it/parser/src/main/resources/sql/supported/dml/select.xml
index a7927b8ecd1..7e7e5ef66b1 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select.xml
@@ -229,4 +229,5 @@
<sql-case id="select_wm_concat_function3" value="SELECT REPLACE(WM_CONCAT(NAME),',','|') FROM TEST_TABLE" db-types="Oracle" />
<sql-case id="select_wm_concat_function4" value="SELECT NAME,WM_CONCAT(DECODE(SUBSTR((TO_CHAR(NAME)),0,1),'.','0'||TO_CHAR(NAME),TO_CHAR(NAME))) WM_NAME FROM TEST_TABLE WHERE NAME ='TEST' GROUP BY NAME" db-types="Oracle" />
<sql-case id="select_with_user_updatable_columns" value="SELECT column_name, updatable FROM user_updatable_columns WHERE table_name = 'LOCATIONS_VIEW' ORDER BY column_name, updatable" db-types="Oracle" />
+ <sql-case id="select_with_script_variables" value="SELECT x.$(ColumnName) FROM Person.Person x WHERE x.BusinessEntityID > 5" db-types="SQLServer" />
</sql-cases>