Skip to content

Commit 80861cf

Browse files
Chat2DB-ProChat2DB-Pro
committed
support view trigger producer function
1 parent 227d106 commit 80861cf

7 files changed

Lines changed: 124 additions & 2 deletions

File tree

‎chat2db-server/chat2db-server-domain/chat2db-server-domain-core/src/main/java/ai/chat2db/server/domain/core/impl/DlTemplateServiceImpl.java‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,6 @@
1212
import com.alibaba.druid.sql.ast.SQLStatement;
1313
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
1414
import com.alibaba.druid.sql.parser.ParserException;
15-
import com.alibaba.druid.sql.parser.SQLParserUtils;
1615

1716
import ai.chat2db.server.domain.api.param.DlCountParam;
1817
import ai.chat2db.server.domain.api.param.DlExecuteParam;
@@ -31,6 +30,7 @@
3130
import ai.chat2db.spi.sql.Chat2DBContext;
3231
import ai.chat2db.spi.sql.SQLExecutor;
3332
import ai.chat2db.spi.util.JdbcUtils;
33+
import ai.chat2db.spi.util.SqlUtils;
3434
import com.google.common.collect.Lists;
3535
import lombok.extern.slf4j.Slf4j;
3636
import org.apache.commons.collections4.CollectionUtils;
@@ -58,7 +58,7 @@ public ListResult<ExecuteResult> execute(DlExecuteParam param) {
5858
sqlAnalyseParam.setSql(param.getSql());
5959
DbType dbType =
6060
JdbcUtils.parse2DruidDbType(Chat2DBContext.getConnectInfo().getDbType());
61-
List<String> sqlList = SQLParserUtils.splitAndRemoveComment(param.getSql(), dbType);
61+
List<String> sqlList = SqlUtils.parse(param.getSql(), dbType);
6262
if (CollectionUtils.isEmpty(sqlList)) {
6363
throw new BusinessException("dataSource.sqlAnalysisError");
6464
}

‎chat2db-server/chat2db-server-test/pom.xml‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,18 @@
2323
<artifactId>spring-boot-starter-test</artifactId>
2424
<scope>test</scope>
2525
</dependency>
26+
<dependency>
27+
<groupId>com.github.jsqlparser</groupId>
28+
<artifactId>jsqlparser</artifactId>
29+
<version>4.6</version>
30+
<scope>test</scope>
31+
</dependency>
32+
<dependency>
33+
<groupId>com.github.vertical-blank</groupId>
34+
<artifactId>sql-formatter</artifactId>
35+
<version>2.0.4</version>
36+
<scope>test</scope>
37+
</dependency>
2638
</dependencies>
2739

2840
</project>

‎chat2db-server/chat2db-server-test/src/test/java/ai/chat2db/server/test/temp/SQLParseTest.java‎

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,42 @@
1+
package ai.chat2db.server.test.temp;
2+
3+
import com.github.vertical_blank.sqlformatter.SqlFormatter;
4+
import net.sf.jsqlparser.JSQLParserException;
5+
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
6+
import net.sf.jsqlparser.statement.Statement;
7+
import net.sf.jsqlparser.statement.Statements;
8+
9+
public class SQLParseTest {
10+
11+
public static void main(String[] args) throws JSQLParserException {
12+
String sql = "CREATE OR REPLACE PROCEDURE public.raise_salary(emp_id integer, percentage numeric)\n"
13+
+ " LANGUAGE plpgsql\n"
14+
+ "AS $procedure$\n"
15+
+ "BEGIN\n"
16+
+ " UPDATE employees\n"
17+
+ " SET salary = salary + (salary * percentage / 100)\n"
18+
+ " WHERE id = emp_id;\n"
19+
+ "COMMIT;\n"
20+
+ "END; -- sdsd\n"
21+
+ "$procedure$";
22+
23+
Statements statements = CCJSqlParserUtil.parseStatements(sql);
24+
25+
// 如果是多条语句,解析后的实际类型是StatementList
26+
27+
28+
// 遍历每个语句
29+
for (Statement stmt : statements.getStatements()) {
30+
// 如果是单条语句,实际类型是Statement
31+
System.out.println(stmt.toString());
32+
33+
System.out.println(" dddd:"+SqlFormatter.format(stmt.toString()));
34+
System.out.println(" hu:"+ cn.hutool.db.sql.SqlFormatter.format(stmt.toString()));
35+
}
36+
String s = SqlFormatter.format("SELECT * FROM table1");
37+
System.out.println(s);
38+
39+
40+
41+
}
42+
}

‎chat2db-server/chat2db-server-web/chat2db-server-web-api/src/main/java/ai/chat2db/server/web/api/controller/sql/SqlController.java‎

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,28 @@
1+
package ai.chat2db.server.web.api.controller.sql;
2+
3+
import ai.chat2db.server.tools.base.wrapper.result.DataResult;
4+
import ai.chat2db.server.web.api.aspect.ConnectionInfoAspect;
5+
import ai.chat2db.server.web.api.controller.sql.request.SqlFormatRequest;
6+
import cn.hutool.db.sql.SqlFormatter;
7+
import jakarta.validation.Valid;
8+
import org.springframework.web.bind.annotation.GetMapping;
9+
import org.springframework.web.bind.annotation.RequestMapping;
10+
import org.springframework.web.bind.annotation.RestController;
11+
12+
@ConnectionInfoAspect
13+
@RequestMapping("/api/sql")
14+
@RestController
15+
public class SqlController {
16+
17+
@GetMapping("/format")
18+
public DataResult<String> list(@Valid SqlFormatRequest sqlFormatRequest) {
19+
20+
String sql = sqlFormatRequest.getSql();
21+
try {
22+
sql = SqlFormatter.format(sql);
23+
} catch (Exception e) {
24+
// ignore
25+
}
26+
return DataResult.of(sql);
27+
}
28+
}

‎chat2db-server/chat2db-server-web/chat2db-server-web-api/src/main/java/ai/chat2db/server/web/api/controller/sql/request/SqlFormatRequest.java‎

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,17 @@
1+
package ai.chat2db.server.web.api.controller.sql.request;
2+
3+
import lombok.AllArgsConstructor;
4+
import lombok.Data;
5+
import lombok.NoArgsConstructor;
6+
import lombok.experimental.SuperBuilder;
7+
8+
@Data
9+
@SuperBuilder
10+
@NoArgsConstructor
11+
@AllArgsConstructor
12+
public class SqlFormatRequest {
13+
14+
private String sql;
15+
16+
private String dbType;
17+
}

‎chat2db-server/chat2db-spi/pom.xml‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -69,5 +69,10 @@
6969
<groupId>commons-beanutils</groupId>
7070
<artifactId>commons-beanutils</artifactId>
7171
</dependency>
72+
<dependency>
73+
<groupId>com.github.jsqlparser</groupId>
74+
<artifactId>jsqlparser</artifactId>
75+
<version>4.6</version>
76+
</dependency>
7277
</dependencies>
7378
</project>

‎chat2db-server/chat2db-spi/src/main/java/ai/chat2db/spi/util/SqlUtils.java‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -40,6 +40,7 @@
4040
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlRenameTableStatement;
4141
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlRenameTableStatement.Item;
4242
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlTableIndex;
43+
import com.alibaba.druid.sql.parser.SQLParserUtils;
4344

4445
import ai.chat2db.server.tools.base.excption.BusinessException;
4546
import ai.chat2db.server.tools.common.util.EasyBooleanUtils;
@@ -52,6 +53,9 @@
5253
import ai.chat2db.spi.model.TableColumn;
5354
import ai.chat2db.spi.model.TableIndex;
5455
import ai.chat2db.spi.model.TableIndexColumn;
56+
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
57+
import net.sf.jsqlparser.statement.Statement;
58+
import net.sf.jsqlparser.statement.Statements;
5559
import org.apache.commons.collections4.CollectionUtils;
5660
import org.apache.commons.lang3.BooleanUtils;
5761
import org.apache.commons.lang3.StringUtils;
@@ -461,4 +465,18 @@ private static SQLTableSource getSQLExprTableSource(SQLTableSource sqlTableSourc
461465
return null;
462466
}
463467

468+
public static List<String> parse(String sql,DbType dbType) {
469+
List<String> list = new ArrayList<>();
470+
try {
471+
Statements statements = CCJSqlParserUtil.parseStatements(sql);
472+
// 遍历每个语句
473+
for (Statement stmt : statements.getStatements()) {
474+
list.add(stmt.toString());
475+
}
476+
} catch (Exception e) {
477+
list = SQLParserUtils.splitAndRemoveComment(sql, dbType);
478+
}
479+
return list;
480+
}
481+
464482
}

0 commit comments

Comments
 (0)