You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by du...@apache.org on 2024/04/02 05:59:32 UTC
(shardingsphere) branch master updated: Enhanced support for Oracle SQL parsing. (#30729)
This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang 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 44663fe43c2 Enhanced support for Oracle SQL parsing. (#30729)
44663fe43c2 is described below
commit 44663fe43c21225cbfeedd831d3f2535a8af3735
Author: 啊哈 <16...@users.noreply.github.com>
AuthorDate: Tue Apr 2 13:59:25 2024 +0800
Enhanced support for Oracle SQL parsing. (#30729)
* Enhance support for some Oracle SQL parsing.
* Add some new Oracle SQL parsing test cases.
* Fixed some code formatting issues and errors.
---------
Co-authored-by: Desheng Zhang <82...@qq.com>
---
.../oracle/src/main/antlr4/imports/oracle/PLSQL.g4 | 7 +++-
.../src/main/resources/case/ddl/create-trigger.xml | 5 +++
.../resources/sql/supported/ddl/create-trigger.xml | 41 ++++++----------------
3 files changed, 21 insertions(+), 32 deletions(-)
diff --git a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4 b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4
index ed9b25989a6..f58dd3f23b4 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4
@@ -94,6 +94,7 @@ statement
| forallStatement
| gotoStatement
| ifStatement
+ | modifyingStatement
| nullStatement
| openStatement
| openForStatement
@@ -216,6 +217,8 @@ iterationCtlSeq
: qualIterationCtl (COMMA_ qualIterationCtl)*
;
+modifyingExpression: INSERTING | DELETING | UPDATING;
+
qualIterationCtl
: REVERSE? iterationCcontrol predClauseSeq
;
@@ -314,6 +317,8 @@ ifStatement
END IF SEMI_
;
+modifyingStatement: IF modifyingExpression THEN statement+ (ELSIF modifyingExpression THEN statement+)* (ELSE statement+)? END IF SEMI_;
+
nullStatement
: NULL SEMI_
;
@@ -582,7 +587,7 @@ dmlEventClause
;
dmlEventElement
- : (DELETE | INSERT | UPDATE) (OF LP_ columnName (COMMA_ columnName)* RP_)?
+ : (DELETE | INSERT | UPDATE) (OF LP_? columnName (COMMA_ columnName)* RP_?)?
;
systemTrigger
diff --git a/test/it/parser/src/main/resources/case/ddl/create-trigger.xml b/test/it/parser/src/main/resources/case/ddl/create-trigger.xml
index 5ca32c2a1c3..563d503f8ac 100644
--- a/test/it/parser/src/main/resources/case/ddl/create-trigger.xml
+++ b/test/it/parser/src/main/resources/case/ddl/create-trigger.xml
@@ -27,4 +27,9 @@
<create-trigger sql-case-id="create_trigger_with_body" />
<create-trigger sql-case-id="create_trigger_with_execute_immediate_statement" />
<create-trigger sql-case-id="create_trigger_with_assignment_statement" />
+ <create-trigger sql-case-id="create_trigger_with_logon" />
+ <create-trigger sql-case-id="create_trigger_with_cascade_1" />
+ <create-trigger sql-case-id="create_trigger_with_cascade_2" />
+ <create-trigger sql-case-id="create_trigger_with_cascade_3" />
+ <create-trigger sql-case-id="create_trigger_with_cascade_4" />
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/sql/supported/ddl/create-trigger.xml b/test/it/parser/src/main/resources/sql/supported/ddl/create-trigger.xml
index aafdd9e562a..58f6b4ccf8d 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/create-trigger.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/create-trigger.xml
@@ -17,37 +17,16 @@
-->
<sql-cases>
- <sql-case id="create_trigger" value="CREATE TRIGGER reminder1
- ON Sales.Customer
- AFTER INSERT, UPDATE
- AS INSERT INTO SalesPersonQuotaHistory VALUES(t1, 100)" db-types="SQLServer" />
- <sql-case id="create_trigger_with_database_scoped" value="CREATE TRIGGER ddl_trig_database
- ON ALL SERVER
- AFTER INSERT
- AS
- SELECT EVENTDATA()" db-types="SQLServer" />
-
+ <sql-case id="create_trigger" value="CREATE TRIGGER reminder1 ON Sales.Customer AFTER INSERT, UPDATE AS INSERT INTO SalesPersonQuotaHistory VALUES(t1, 100)" db-types="SQLServer" />
+ <sql-case id="create_trigger_with_database_scoped" value="CREATE TRIGGER ddl_trig_database ON ALL SERVER AFTER INSERT AS SELECT EVENTDATA()" db-types="SQLServer" />
<sql-case id="create_trigger_with_create_view" value="CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create view v1 as select 1; END" db-types="MySQL" />
<sql-case id="create_trigger_with_dml_event_clause" value="CREATE TRIGGER scott.emp_permit_changes BEFORE DELETE OR INSERT OR UPDATE ON scott.emp BEGIN IF (IS_SERVERERROR (1017)) THEN NULL; ELSE NULL; END IF; END;" db-types="Oracle" />
- <sql-case id="create_trigger_with_body" value="CREATE TRIGGER log_errors
- AFTER SERVERERROR ON DATABASE
- BEGIN
- IF (IS_SERVERERROR (1017)) THEN
- NULL;
- ELSE
- NULL;
- END IF;
- END;" db-types="Oracle" />
- <sql-case id="create_trigger_with_execute_immediate_statement" value="CREATE TRIGGER adg_logon_sync_trigger
- AFTER LOGON ON user.schema
- begin
- if (SYS_CONTEXT('USERENV', 'DATABASE_ROLE') IN ('PHYSICAL STANDBY')) then
- execute immediate 'alter session sync with primary';
- end if;
- end;" db-types="Oracle" />
- <sql-case id="create_trigger_with_assignment_statement" value="CREATE TRIGGER Contacts_BI
- BEFORE INSERT ON Contacts FOR EACH ROW
- BEGIN
- :NEW.ID := Contacts_Seq.NEXTVAL;
- END;" db-types="Oracle" />
+ <sql-case id="create_trigger_with_body" value="CREATE TRIGGER log_errors AFTER SERVERERROR ON DATABASE BEGIN IF (IS_SERVERERROR (1017)) THEN NULL; ELSE NULL; END IF; END;" db-types="Oracle" />
+ <sql-case id="create_trigger_with_execute_immediate_statement" value="CREATE TRIGGER adg_logon_sync_trigger AFTER LOGON ON user.schema begin if (SYS_CONTEXT('USERENV', 'DATABASE_ROLE') IN ('PHYSICAL STANDBY')) then execute immediate 'alter session sync with primary'; end if; end;" db-types="Oracle" />
+ <sql-case id="create_trigger_with_assignment_statement" value="CREATE TRIGGER Contacts_BI BEFORE INSERT ON Contacts FOR EACH ROW BEGIN :NEW.ID := Contacts_Seq.NEXTVAL; END;" db-types="Oracle" />
+ <sql-case id="create_trigger_with_logon" value="CREATE OR REPLACE TRIGGER check_user AFTER LOGON ON DATABASE BEGIN check_user; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR (-20000, 'Unexpected error: '|| DBMS_Utility.Format_Error_Stack); END;" db-types="Oracle" />
+ <sql-case id="create_trigger_with_cascade_1" value="CREATE OR REPLACE TRIGGER dept_cascade3 AFTER UPDATE OF Deptno ON dept BEGIN UPDATE emp SET Update_id = NULL WHERE Update_id = Integritypackage.Updateseq; END;" db-types="Oracle" />
+ <sql-case id="create_trigger_with_cascade_2" value="CREATE OR REPLACE TRIGGER dept_del_cascade AFTER DELETE ON dept FOR EACH ROW BEGIN DELETE FROM emp WHERE emp.Deptno = :OLD.Deptno; END;" db-types="Oracle" />
+ <sql-case id="create_trigger_with_cascade_3" value="CREATE OR REPLACE TRIGGER dept_cascade2 AFTER DELETE OR UPDATE OF Deptno ON dept FOR EACH ROW BEGIN IF UPDATING THEN UPDATE emp SET Deptno = :NEW.Deptno, Update_id = Integritypackage.Updateseq WHERE emp.Deptno = :OLD.Deptno AND Update_id IS NULL; END IF; IF DELETING THEN DELETE FROM emp WHERE emp.Deptno = :OLD.Deptno; END IF; END;" db-types="Oracle" />
+ <sql-case id="create_trigger_with_cascade_4" value="CREATE OR REPLACE TRIGGER dept_restrict BEFORE DELETE OR UPDATE OF Deptno ON dept FOR EACH ROW DECLARE Dummy INTEGER; Employees_present EXCEPTION; employees_not_present EXCEPTION; CURSOR Dummy_cursor (Dn NUMBER) IS SELECT Deptno FROM emp WHERE Deptno = Dn; BEGIN OPEN Dummy_cursor (:OLD.Deptno); FETCH Dummy_cursor INTO Dummy; IF Dummy_cursor%FOUND THEN RAISE Employees_present; ELSE RAISE Employees_not_present; END IF; CLOSE Dummy_cu [...]
</sql-cases>