FlyingZC opened a new issue, #27827: URL: https://github.com/apache/shardingsphere/issues/27827
# Background Hi community, ShardingSphere parser engine helps users parse a SQL to get the AST (Abstract Syntax Tree) and visit this tree to get SQLStatement (Java Object). Currently, we are planning to enhance the support for openGauss SQL parsing in ShardingSphere. More details: https://shardingsphere.apache.org/document/current/en/reference/sharding/parse/ # Task This issue is to support more openGauss sql parse, as follows: ```sql ALTER TRIGGER delete_trigger ON test_trigger_src_tbl RENAME TO delete_trigger_renamed; --禁用insert_trigger触发器 ``` ```sql DROP TRIGGER insert_trigger ON test_trigger_src_tbl; ``` ```sql DROP TRIGGER update_trigger ON test_trigger_src_tbl; ``` ```sql DROP TRIGGER delete_trigger_renamed ON test_trigger_src_tbl; --创建mysql兼容数据库 ``` ```sql create user test_user password 'Gauss@123'; --创建原表及触发表 db_mysql=# create table test_mysql_trigger_src_tbl (id INT); db_mysql=# create table test_mysql_trigger_des_tbl (id INT); db_mysql=# create table animals (id INT, name CHAR(30)); db_mysql=# create table food (id INT, foodtype VARCHAR(32), remark VARCHAR(32), time_flag TIMESTAMP); --创建MySQL兼容definer语法触发器 db_mysql=# create definer=test_user trigger trigger1 after insert on test_mysql_trigger_src_tbl for each row begin insert into test_mysql_trigger_des_tbl values(1); end; / --创建MySQL兼容trigger_order语法触发器 db_mysql=# create trigger animal_trigger1 after insert on animals for each row begin insert into food(id, foodtype, remark, time_flag) values (1,'ice cream', 'sdsdsdsd', now()); end; / --创建MySQL兼容FOLLOWS触发器 db_mysql=# create trigger animal_trigger2 after insert on animals for each row follows animal_trigger1 begin insert into food(id, foodtype, remark, time_flag) values (2,'chocolate', 'sdsdsdsd', now()); end; / db_mysql=# create trigger animal_trigger3 after insert on animals for each row follows animal_trigger1 begin insert into food(id, foodtype, remark, time_flag) values (3,'cake', 'sdsdsdsd', now()); end; / db_mysql=# create trigger animal_trigger4 after insert on animals for each row follows animal_trigger1 begin insert into food(id, foodtype, remark, time_flag) values (4,'sausage', 'sdsdsdsd', now()); end; / --执行insert触发事件并检查触发结果 db_mysql=# insert into animals (id, name) values(1,'lion'); db_mysql=# select * from animals; db_mysql=# select id, foodtype, remark from food; --创建MySQL兼容PROCEDES触发器 db_mysql=# create trigger animal_trigger5 after insert on animals for each row precedes animal_trigger3 begin insert into food(id, foodtype, remark, time_flag) values (5,'milk', 'sdsds', now()); end; / db_mysql=# create trigger animal_trigger6 after insert on animals for each row precedes animal_trigger2 begin insert into food(id, foodtype, remark, time_flag) values (6,'strawberry', 'sdsds', now()); end; / --执行insert触发事件并检查触发结果 db_mysql=# insert into animals (id, name) values(2, 'dog'); db_mysql=# select * from animals; db_mysql=# select id, foodtype, remark from food; --创建MySQL兼容if not exists语法触发器 db_mysql=# create trigger if not exists animal_trigger1 after insert on animals for each row begin insert into food(id, foodtype, remark, time_flag) values (1,'ice cream', 'sdsdsdsd', now()); end; / --mysql兼容删除触发器语法 db_mysql=# drop trigger animal_trigger1; db_mysql=# drop trigger if exists animal_trigger1; --在指定模式下创建、重命名、删除触发器语法，触发器的模式需要与表模式相同 create schema testscm; create table food (id int, foodtype varchar(32), remark varchar(32), time_flag timestamp); create table testscm.animals_scm (id int, name char(30)); -- 在指定模式下创建触发器 create trigger testscm.animals_trigger after insert on testscm.animals_scm for each row begin insert into food(id, foodtype, remark, time_flag) values (1,'bamboo', 'healthy', now()); end; / create trigger if not exists testscm.animals_trigger after insert on testscm.animals_scm for each row begin insert into food(id, foodtype, remark, time_flag) values (1,'bamboo', 'healthy', now()); end; / -- 重命名指定模式下的触发器 alter trigger testscm.animals_trigger on testscm.animals_scm rename to animals_trigger_new; -- 删除指定模式下的触发器 drop trigger testscm.animals_trigger_new; drop trigger if exists testscm.animals_trigger_new; ``` # Process 1. First confirm that this is a correct openGauss sql syntax, if not please leave a message under the issue and ignore it; 2. Compare SQL definitions in Official SQL Doc and ShardingSphere SQL Doc; 3. If there is any difference in ShardingSphere SQL Doc, please correct them by referring to the Official SQL Doc; 4. Run mvn install the current_file_module; 5. Check whether there are any exceptions. If indeed, please fix them. (Especially xxxVisitor.class); 6. Add new corresponding SQL case in SQL Cases and expected parsed result in Expected Statement XML; 7. Run SQLParserParameterizedTest to make sure no exceptions. # Relevant Skills 1. Master JAVA language 2. Have a basic understanding of Antlr `g4` file 3. Be familiar with openGauss SQLs -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
