You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "FlyingZC (via GitHub)" <gi...@apache.org> on 2023/08/02 10:03:14 UTC
[GitHub] [shardingsphere] FlyingZC opened a new issue, #27827: Support parsing openGauss ALTER sql
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] github-actions[bot] commented on issue #27827: Support parsing openGauss ALTER sql
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #27827:
URL: https://github.com/apache/shardingsphere/issues/27827#issuecomment-1703266235
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Support parsing openGauss ALTER sql [shardingsphere]
Posted by "FlyingZC (via GitHub)" <gi...@apache.org>.
FlyingZC closed issue #27827: Support parsing openGauss ALTER sql
URL: https://github.com/apache/shardingsphere/issues/27827
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Support parsing openGauss ALTER sql [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #27827:
URL: https://github.com/apache/shardingsphere/issues/27827#issuecomment-1783911315
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org