You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by lu...@apache.org on 2021/08/18 02:01:11 UTC
[shardingsphere] branch master updated: add SQLServer grammar
Create Function (#11705)
This is an automated email from the ASF dual-hosted git repository.
lujingshang 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 061e85d add SQLServer grammar Create Function (#11705)
061e85d is described below
commit 061e85da36a0a358b6ebb4e562c303eb72686abb
Author: Icesource <ic...@gmail.com>
AuthorDate: Wed Aug 18 10:00:41 2021 +0800
add SQLServer grammar Create Function (#11705)
* add SQLServer grammer declareVariable
* add SQLServer grammar setVariable
* add SQLServer grammar Create Function
---
.../src/main/antlr4/imports/sqlserver/BaseRule.g4 | 16 ++-
.../main/antlr4/imports/sqlserver/DDLStatement.g4 | 116 ++++++++++++++++++++-
.../src/main/antlr4/imports/sqlserver/Keyword.g4 | 28 +++++
.../antlr4/imports/sqlserver/SQLServerKeyword.g4 | 56 ++++++++++
.../sql/parser/autogen/SQLServerStatement.g4 | 1 +
.../impl/SQLServerDDLStatementSQLVisitor.java | 8 ++
.../ddl/SQLServerCreateFunctionStatement.java} | 45 ++------
.../main/resources/case/ddl/create-function.xml | 2 +
.../sql/supported/ddl/create-function.xml | 19 ++++
9 files changed, 252 insertions(+), 39 deletions(-)
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/BaseRule.g4 b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/BaseRule.g4
index 5e227dc..2a0ba3b 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/BaseRule.g4
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/BaseRule.g4
@@ -96,13 +96,18 @@ unreservedWord
| RULE | SYNONYM | COLLECTION | SCRIPT | KILL | BACKUP | LOG | SHOWPLAN
| SUBSCRIBE | QUERY | NOTIFICATIONS | CHECKPOINT | SEQUENCE | INSTANCE | DO | DEFINER | LOCAL | CASCADED
| NEXT | NAME | INTEGER | TYPE | MAX | MIN | SUM | COUNT | AVG | FIRST | DATETIME2
- | OUTPUT | INSERTED | DELETED
+ | OUTPUT | INSERTED | DELETED | GROUP | ROWS | DATE | DATEPART | CAST | DAY
+ | FORWARD_ONLY | KEYSET | FAST_FORWARD | READ_ONLY | SCROLL_LOCKS | OPTIMISTIC | TYPE_WARNING | SCHEMABINDING | CALLER
;
schemaName
: identifier
;
+functionName
+ : (owner DOT_)? name
+ ;
+
tableName
: (owner DOT_)? name
;
@@ -210,6 +215,7 @@ simpleExpr
| parameterMarker
| literals
| columnName
+ | variableName
| simpleExpr OR_ simpleExpr
| (PLUS_ | MINUS_ | TILDE_ | NOT_ | BINARY) simpleExpr
| ROW? LP_ expr (COMMA_ expr)* RP_
@@ -252,7 +258,7 @@ regularFunction
;
regularFunctionName
- : identifier | IF | LOCALTIME | LOCALTIMESTAMP | INTERVAL
+ : (owner DOT_)? identifier | IF | LOCALTIME | LOCALTIMESTAMP | INTERVAL
;
caseExpression
@@ -292,7 +298,7 @@ dataTypeName
: BIGINT | NUMERIC | BIT | SMALLINT | DECIMAL | SMALLMONEY | INT | TINYINT | MONEY | FLOAT | REAL
| DATE | DATETIMEOFFSET | SMALLDATETIME | DATETIME | DATETIME2 | TIME | CHAR | VARCHAR | TEXT | NCHAR | NVARCHAR
| NTEXT | BINARY | VARBINARY | IMAGE | SQL_VARIANT | XML | UNIQUEIDENTIFIER | HIERARCHYID | GEOMETRY
- | GEOGRAPHY | IDENTIFIER_
+ | GEOGRAPHY | IDENTIFIER_ | INTEGER
;
atTimeZoneExpr
@@ -422,3 +428,7 @@ ignoredIdentifiers
matchNone
: 'Default does not match anything'
;
+
+variableName
+ : AT_ identifier
+ ;
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/DDLStatement.g4 b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/DDLStatement.g4
index 078e1d9..d2a1dc3 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/DDLStatement.g4
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/DDLStatement.g4
@@ -17,7 +17,7 @@
grammar DDLStatement;
-import Symbol, Keyword, SQLServerKeyword, Literals, BaseRule;
+import Symbol, Keyword, SQLServerKeyword, Literals, BaseRule, DMLStatement;
createTable
: CREATE TABLE tableName fileTableClause createDefinitionClause
@@ -27,6 +27,10 @@ createIndex
: CREATE createIndexSpecification INDEX indexName ON tableName columnNamesWithSort
;
+createFunction
+ : CREATE (OR ALTER)? FUNCTION functionName funcParameters funcReturns
+ ;
+
alterTable
: ALTER TABLE tableName alterDefinitionClause (COMMA_ alterDefinitionClause)*
;
@@ -450,3 +454,113 @@ onHistoryTableClause
ifExist
: IF EXISTS
;
+
+declareVariable
+ : DECLARE (variable (COMMA_ variable)* | tableVariable)
+ ;
+
+variable
+ : variableName AS? dataType (EQ_ simpleExpr)?
+ | variableName CURSOR
+ ;
+
+tableVariable
+ : variableName AS? variTableTypeDefinition
+ ;
+
+variTableTypeDefinition
+ : TABLE LP_ tableVariableClause (COMMA_ tableVariableClause)* RP_
+ ;
+
+tableVariableClause
+ : variableTableColumnDefinition | variableTableConstraint
+ ;
+
+variableTableColumnDefinition
+ : columnName (dataTypeName | AS expr) (COLLATE collationName)? ((DEFAULT expr)? | IDENTITY (LP_ NUMBER_ COMMA_ NUMBER_ RP_)?) ROWGUIDCOL? variableTableColumnConstraint
+ ;
+
+variableTableColumnConstraint
+ : (NULL | NOT NULL)?
+ | (PRIMARY KEY | UNIQUE)?
+ | CHECK LP_ expr RP_
+ | WITH indexOption
+ ;
+
+variableTableConstraint
+ : (PRIMARY KEY | UNIQUE) LP_ columnName (COMMA_ columnName)* RP_
+ | CHECK expr
+ ;
+
+setVariable
+ : SET variableName setVariableClause
+ ;
+
+setVariableClause
+ : (DOT_ identifier)? EQ_ (expr | identifier DOT_ identifier | NCHAR_TEXT)
+ | compoundOperation expr
+ | EQ_ cursorVariable
+ | EQ_ LP_ select RP_
+ ;
+
+cursorVariable
+ : variableName
+ | CURSOR cursorClause FOR select (FOR (READ_ONLY | UPDATE (OF name (COMMA_ name)*)))
+ ;
+
+cursorClause
+ : (FORWARD_ONLY | SCROLL)? (STATIC | KEYSET | DYNAMIC | FAST_FORWARD)? (READ_ONLY | SCROLL_LOCKS | OPTIMISTIC)? (TYPE_WARNING)?
+ ;
+
+compoundOperation
+ : PLUS_ EQ_
+ | MINUS_ EQ_
+ | ASTERISK_ EQ_
+ | SLASH_ EQ_
+ | MOD_ EQ_
+ | AMPERSAND_ EQ_
+ | CARET_ EQ_
+ | VERTICAL_BAR_ EQ_
+ ;
+
+
+funcParameters
+ : LP_ (variableName AS? (owner DOT_)? dataType (EQ_ ignoredIdentifier)? READONLY?)* RP_
+ ;
+
+funcReturns
+ : funcScalarReturn | funcInlineReturn | funcMutiReturn
+ ;
+
+funcMutiReturn
+ : RETURNS variableName TABLE createTableDefinitions (WITH functionOption (COMMA_ functionOption)*)? AS? BEGIN compoundStatement RETURN END
+ ;
+
+funcInlineReturn
+ : RETURNS TABLE (WITH functionOption (COMMA_ functionOption)*)? AS? RETURN LP_? select RP_?
+ ;
+
+funcScalarReturn
+ : RETURNS dataType (WITH functionOption (COMMA_ functionOption)*)? AS? BEGIN compoundStatement RETURN expr
+ ;
+
+tableTypeDefinition
+ : (columnDefinition columnConstraint | computedColumnDefinition) tableConstraint*
+ ;
+
+compoundStatement
+ : validStatement*
+ ;
+
+functionOption
+ : ENCRYPTION?
+ | SCHEMABINDING?
+ | (RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT)?
+ | (EXECUTE AS CALLER)?
+ | (INLINE = ( ON | OFF ))?
+ ;
+
+validStatement
+ : (createTable | alterTable | dropTable | truncateTable| insert
+ | update | delete | select | setVariable | declareVariable) SEMI_?
+ ;
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/Keyword.g4 b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/Keyword.g4
index 1a3d49f..e69736a 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/Keyword.g4
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/Keyword.g4
@@ -639,3 +639,31 @@ FORCED
HINT
: H I N T
;
+
+DECLARE
+ : D E C L A R E
+ ;
+
+CURSOR
+ : C U R S O R
+ ;
+
+OF
+ : O F
+ ;
+
+RETURNS
+ : R E T U R N S
+ ;
+
+DATEPART
+ : D A T E P A R T
+ ;
+
+RETURN
+ : R E T U R N
+ ;
+
+READONLY
+ : R E A D O N L Y
+ ;
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/SQLServerKeyword.g4 b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/SQLServerKeyword.g4
index 1d13ec1..460b38d 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/SQLServerKeyword.g4
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/SQLServerKeyword.g4
@@ -1070,3 +1070,59 @@ FILTER_COLUMN
RETENTION_PERIOD
: R E T E N T I O N UL_ P E R I O D
;
+
+FORWARD_ONLY
+ : F O R W A R D UL_ O N L Y
+ ;
+
+SCROLL
+ : S C R O L L
+ ;
+
+STATIC
+ : S T A T I C
+ ;
+
+KEYSET
+ : K E Y S E T
+ ;
+
+DYNAMIC
+ : D Y N A M I C
+ ;
+
+FAST_FORWARD
+ : F A S T UL_ F O R W A R D
+ ;
+
+READ_ONLY
+ : R E A D UL_ O N L Y
+ ;
+
+SCROLL_LOCKS
+ : S C R O L L UL_ L O C K S
+ ;
+
+OPTIMISTIC
+ : O P T I M I S T I C
+ ;
+
+TYPE_WARNING
+ : T Y P E UL_ W A R N I N G
+ ;
+
+SCHEMABINDING
+ : S C H E M A B I N D I N G
+ ;
+
+CALLER
+ : C A L L E R
+ ;
+
+INPUT
+ : I N P U T
+ ;
+
+CALLED
+ : C A L L E D
+ ;
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/org/apache/shardingsphere/sql/parser/autogen/SQLServerStatement.g4 b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/org/apache/shardingsphere/sql/parser/autogen/SQLServerStatement.g4
index 4ef4036..417d40e 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/org/apache/shardingsphere/sql/parser/autogen/SQLServerStatement.g4
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/org/apache/shardingsphere/sql/parser/autogen/SQLServerStatement.g4
@@ -31,6 +31,7 @@ execute
| alterTable
| dropTable
| truncateTable
+ | createFunction
| setTransaction
| beginTransaction
| setImplicitTransactions
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/SQLServerDDLStatementSQLVisitor.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/SQLServerDDLStatementSQLVisitor.java
index dbfda3e..23b9422 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/SQLServerDDLStatementSQLVisitor.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/SQLServerDDLStatementSQLVisitor.java
@@ -35,6 +35,7 @@ import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.Col
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.CreateDefinitionClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.CreateIndexContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.CreateTableContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.CreateFunctionContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.CreateTableDefinitionContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.DropColumnSpecificationContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.DropConstraintNameContext;
@@ -66,6 +67,7 @@ import org.apache.shardingsphere.sql.parser.sql.dialect.statement.sqlserver.ddl.
import org.apache.shardingsphere.sql.parser.sql.dialect.statement.sqlserver.ddl.SQLServerDropIndexStatement;
import org.apache.shardingsphere.sql.parser.sql.dialect.statement.sqlserver.ddl.SQLServerDropTableStatement;
import org.apache.shardingsphere.sql.parser.sql.dialect.statement.sqlserver.ddl.SQLServerTruncateStatement;
+import org.apache.shardingsphere.sql.parser.sql.dialect.statement.sqlserver.ddl.SQLServerCreateFunctionStatement;
import java.util.Collection;
import java.util.Collections;
@@ -310,4 +312,10 @@ public final class SQLServerDDLStatementSQLVisitor extends SQLServerStatementSQL
}
return result;
}
+
+ @Override
+ public ASTNode visitCreateFunction(final CreateFunctionContext ctx) {
+ return new SQLServerCreateFunctionStatement();
+ }
+
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/org/apache/shardingsphere/sql/parser/autogen/SQLServerStatement.g4 b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/sqlserver/ddl/SQLServerCreateFunctionStatement.java
similarity index 55%
copy from shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/org/apache/shardingsphere/sql/parser/autogen/SQLServerStatement.g4
copy to shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/sqlserver/ddl/SQLServerCreateFunctionStatement.java
index 4ef4036..444d40e 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/org/apache/shardingsphere/sql/parser/autogen/SQLServerStatement.g4
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/sqlserver/ddl/SQLServerCreateFunctionStatement.java
@@ -15,40 +15,15 @@
* limitations under the License.
*/
-grammar SQLServerStatement;
+package org.apache.shardingsphere.sql.parser.sql.dialect.statement.sqlserver.ddl;
-import Symbol, Comments, DMLStatement, DDLStatement, TCLStatement, DCLStatement, StoreProcedure;
+import lombok.ToString;
+import org.apache.shardingsphere.sql.parser.sql.common.statement.ddl.CreateFunctionStatement;
+import org.apache.shardingsphere.sql.parser.sql.dialect.statement.sqlserver.SQLServerStatement;
-execute
- : (select
- | insert
- | update
- | delete
- | createIndex
- | alterIndex
- | dropIndex
- | createTable
- | alterTable
- | dropTable
- | truncateTable
- | setTransaction
- | beginTransaction
- | setImplicitTransactions
- | commit
- | rollback
- | savepoint
- | grant
- | revoke
- | deny
- | createUser
- | dropUser
- | alterUser
- | createRole
- | dropRole
- | alterRole
- | createLogin
- | dropLogin
- | alterLogin
- | call
- ) SEMI_?
- ;
+/**
+ * SQLServer create index statement.
+ */
+@ToString
+public class SQLServerCreateFunctionStatement extends CreateFunctionStatement implements SQLServerStatement {
+}
diff --git a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/ddl/create-function.xml b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/ddl/create-function.xml
index 3201851..921d5a2 100644
--- a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/ddl/create-function.xml
+++ b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/ddl/create-function.xml
@@ -21,4 +21,6 @@
<create-function sql-case-id="create_function_with_argname" />
<create-function sql-case-id="create_function_with_multiple_output_parameters" />
<create-function sql-case-id="create_function_return_table" />
+ <create-function sql-case-id="create_function_with_execute" />
+ <create-function sql-case-id="create_function_inline_table" />
</sql-parser-test-cases>
diff --git a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/ddl/create-function.xml b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/ddl/create-function.xml
index 6cd2490..724705c 100644
--- a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/ddl/create-function.xml
+++ b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/ddl/create-function.xml
@@ -34,4 +34,23 @@
<sql-case id="create_function_return_table" value="CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
LANGUAGE SQL" db-types="PostgreSQL" />
+ <sql-case id="create_function_with_execute" value="CREATE FUNCTION dbo.ISOweek (@DATE datetime)
+ RETURNS int WITH EXECUTE AS CALLER AS
+ BEGIN
+ DECLARE @ISOweek int;
+ SET @ISOweek= DATEPART(wk,@DATE)+1-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');
+ SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;
+ SET @ISOweek=1;
+ RETURN(@ISOweek);
+ END" db-types="SQLServer" />
+ <sql-case id="create_function_inline_table" value="CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
+ RETURNS TABLE AS RETURN (
+ SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
+ FROM Production.Product AS P
+ JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
+ JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
+ JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
+ WHERE C.StoreID = @storeid
+ GROUP BY P.ProductID, P.Name
+ );" db-types="SQLServer" />
</sql-cases>