Skip to content

Commit 81743fb

Browse files
pdxzzzpdxzzz
committed
MySQL database export
1 parent c654fae commit 81743fb

9 files changed

Lines changed: 201 additions & 11 deletions

File tree

‎chat2db-server/chat2db-plugins/chat2db-mysql/src/main/java/ai/chat2db/plugin/mysql/MysqlDBManage.java‎

Lines changed: 110 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -6,10 +6,118 @@
66
import ai.chat2db.spi.sql.SQLExecutor;
77
import org.springframework.util.StringUtils;
88

9-
import java.sql.Connection;
10-
import java.sql.SQLException;
9+
import java.sql.*;
1110

1211
public class MysqlDBManage extends DefaultDBManage implements DBManage {
12+
@Override
13+
public String exportDatabase(Connection connection, String databaseName, String schemaName, boolean containData) throws SQLException {
14+
StringBuilder sqlBuilder = new StringBuilder();
15+
exportTables(connection, sqlBuilder, containData);
16+
exportViews(connection, sqlBuilder);
17+
exportProcedures(connection, sqlBuilder);
18+
exportTriggers(connection, sqlBuilder);
19+
return sqlBuilder.toString();
20+
}
21+
private void exportTables(Connection connection,StringBuilder sqlBuilder, boolean containData) throws SQLException {
22+
try (Statement statement = connection.createStatement(); ResultSet tables = statement.executeQuery("SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'")) {
23+
while (tables.next()) {
24+
String tableName = tables.getString(1);
25+
exportTable(connection, tableName, sqlBuilder, containData);
26+
}
27+
}
28+
}
29+
30+
31+
private void exportTable(Connection connection, String tableName, StringBuilder sqlBuilder, boolean containData) throws SQLException {
32+
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("show create table " + tableName)) {
33+
if (resultSet.next()) {
34+
String createTableSql = "DROP TABLE IF EXISTS `" + tableName + "`;\n" +
35+
resultSet.getString(2) + ";\n";
36+
sqlBuilder.append(createTableSql).append("\n");
37+
38+
if (containData) {
39+
exportTableData(connection, tableName, sqlBuilder);
40+
}
41+
}
42+
}
43+
}
44+
45+
private void exportTableData(Connection connection, String tableName, StringBuilder sqlBuilder) throws SQLException {
46+
StringBuilder insertSql = new StringBuilder();
47+
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("select * from " + tableName)) {
48+
ResultSetMetaData metaData = resultSet.getMetaData();
49+
while (resultSet.next()) {
50+
insertSql.append("INSERT INTO ").append(tableName).append(" VALUES (");
51+
for (int i = 1; i <= metaData.getColumnCount(); i++) {
52+
insertSql.append("'").append(resultSet.getString(i)).append("'");
53+
if (i < metaData.getColumnCount()) {
54+
insertSql.append(", ");
55+
}
56+
}
57+
insertSql.append(");\n");
58+
}
59+
insertSql.append("\n");
60+
}
61+
sqlBuilder.append(insertSql);
62+
}
63+
64+
private void exportViews(Connection connection, StringBuilder sqlBuilder) throws SQLException {
65+
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SHOW FULL TABLES WHERE Table_type = 'VIEW'")) {
66+
while (resultSet.next()) {
67+
String viewName = resultSet.getString(1);
68+
exportView(connection, viewName, sqlBuilder);
69+
}
70+
}
71+
}
72+
73+
private void exportView(Connection connection, String viewName, StringBuilder sqlBuilder) throws SQLException {
74+
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SHOW CREATE VIEW " + viewName)) {
75+
if (resultSet.next()) {
76+
String createViewSql = "DROP VIEW IF EXISTS `" + viewName + "`;\n" + resultSet.getString("Create View") + ";\n";
77+
sqlBuilder.append(createViewSql).append("\n");
78+
}
79+
}
80+
}
81+
82+
private void exportProcedures(Connection connection, StringBuilder sqlBuilder) throws SQLException {
83+
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SHOW PROCEDURE STATUS WHERE Db = DATABASE()")) {
84+
while (resultSet.next()) {
85+
String procedureName = resultSet.getString("Name");
86+
exportProcedure(connection, procedureName, sqlBuilder);
87+
}
88+
}
89+
}
90+
91+
private void exportProcedure(Connection connection, String procedureName, StringBuilder sqlBuilder) throws SQLException {
92+
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SHOW CREATE PROCEDURE " + procedureName)) {
93+
if (resultSet.next()) {
94+
String createProcedureSql = "DROP PROCEDURE IF EXISTS `" + procedureName + "`;\n" +
95+
"delimiter ;;\n" + resultSet.getString("Create Procedure") + ";;\n" + "delimiter ;\n";
96+
sqlBuilder.append(createProcedureSql).append("\n");
97+
}
98+
}
99+
}
100+
101+
private void exportTriggers(Connection connection, StringBuilder sqlBuilder) throws SQLException {
102+
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SHOW TRIGGERS")) {
103+
while (resultSet.next()) {
104+
String triggerName = resultSet.getString("Trigger");
105+
exportTrigger(connection, triggerName, sqlBuilder);
106+
}
107+
}
108+
}
109+
110+
private void exportTrigger(Connection connection, String triggerName, StringBuilder sqlBuilder) throws SQLException {
111+
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SHOW CREATE TRIGGER " + triggerName)) {
112+
if (resultSet.next()) {
113+
String createTriggerSql = "DROP TRIGGER IF EXISTS `" + triggerName + "`;\n" +
114+
"delimiter ;;\n" + resultSet.getString("SQL Original Statement") + ";;\n" +
115+
"delimiter ;\n";
116+
sqlBuilder.append(createTriggerSql).append("\n");
117+
}
118+
}
119+
}
120+
13121
@Override
14122
public void updateProcedure(Connection connection, String databaseName, String schemaName, Procedure procedure) throws SQLException {
15123
try {

‎chat2db-server/chat2db-server-domain/chat2db-server-domain-api/src/main/java/ai/chat2db/server/domain/api/param/datasource/DatabaseExportParam.java‎

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,26 @@
1+
package ai.chat2db.server.domain.api.param.datasource;
2+
3+
import lombok.AllArgsConstructor;
4+
import lombok.Builder;
5+
import lombok.Data;
6+
import lombok.NoArgsConstructor;
7+
8+
/**
9+
* @author: zgq
10+
* @date: 2024年02月27日 22:08
11+
*/
12+
@Data
13+
@AllArgsConstructor
14+
@NoArgsConstructor
15+
@Builder
16+
public class DatabaseExportParam {
17+
/**
18+
* DB名称
19+
*/
20+
private String databaseName;
21+
22+
private String schemaName;
23+
24+
private Boolean containData;
25+
26+
}

‎chat2db-server/chat2db-server-domain/chat2db-server-domain-api/src/main/java/ai/chat2db/server/domain/api/service/DatabaseService.java‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2,12 +2,15 @@
22

33
import ai.chat2db.server.domain.api.param.*;
44
import ai.chat2db.server.domain.api.param.datasource.DatabaseCreateParam;
5+
import ai.chat2db.server.domain.api.param.datasource.DatabaseExportParam;
56
import ai.chat2db.server.domain.api.param.datasource.DatabaseQueryAllParam;
67
import ai.chat2db.server.tools.base.wrapper.result.DataResult;
78
import ai.chat2db.spi.model.*;
89
import ai.chat2db.server.tools.base.wrapper.result.ActionResult;
910
import ai.chat2db.server.tools.base.wrapper.result.ListResult;
1011

12+
import java.sql.SQLException;
13+
1114
/**
1215
* 数据源管理服务
1316
*
@@ -87,4 +90,6 @@ public interface DatabaseService {
8790
* @return
8891
*/
8992
ActionResult modifySchema( SchemaOperationParam request);
93+
94+
String exportDatabase(DatabaseExportParam param) throws SQLException;
9095
}

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

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,6 +7,7 @@
77
import java.util.concurrent.CountDownLatch;
88

99
import ai.chat2db.server.domain.api.param.datasource.DatabaseCreateParam;
10+
import ai.chat2db.server.domain.api.param.datasource.DatabaseExportParam;
1011
import ai.chat2db.server.domain.api.param.datasource.DatabaseQueryAllParam;
1112
import ai.chat2db.server.domain.api.param.MetaDataQueryParam;
1213
import ai.chat2db.server.domain.api.param.SchemaOperationParam;
@@ -17,7 +18,6 @@
1718
import ai.chat2db.server.tools.base.wrapper.result.DataResult;
1819
import ai.chat2db.server.tools.base.wrapper.result.ListResult;
1920
import ai.chat2db.spi.MetaData;
20-
import ai.chat2db.spi.SqlBuilder;
2121
import ai.chat2db.spi.model.Database;
2222
import ai.chat2db.spi.model.MetaSchema;
2323
import ai.chat2db.spi.model.Schema;
@@ -177,4 +177,12 @@ public ActionResult modifySchema(SchemaOperationParam param) {
177177
return ActionResult.isSuccess();
178178
}
179179

180+
@Override
181+
public String exportDatabase(DatabaseExportParam param) throws SQLException {
182+
return Chat2DBContext.getDBManage().exportDatabase(Chat2DBContext.getConnection(),
183+
param.getDatabaseName(),
184+
param.getSchemaName(),
185+
param.getContainData());
186+
}
187+
180188
}

‎chat2db-server/chat2db-server-web/chat2db-server-web-api/src/main/java/ai/chat2db/server/web/api/controller/rdb/DatabaseController.java‎

Lines changed: 22 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,8 +1,9 @@
11
package ai.chat2db.server.web.api.controller.rdb;
22

3+
import ai.chat2db.server.domain.api.param.MetaDataQueryParam;
34
import ai.chat2db.server.domain.api.param.datasource.DatabaseCreateParam;
5+
import ai.chat2db.server.domain.api.param.datasource.DatabaseExportParam;
46
import ai.chat2db.server.domain.api.param.datasource.DatabaseQueryAllParam;
5-
import ai.chat2db.server.domain.api.param.MetaDataQueryParam;
67
import ai.chat2db.server.domain.api.service.DatabaseService;
78
import ai.chat2db.server.tools.base.wrapper.result.ActionResult;
89
import ai.chat2db.server.tools.base.wrapper.result.DataResult;
@@ -13,20 +14,20 @@
1314
import ai.chat2db.server.web.api.controller.rdb.converter.DatabaseConverter;
1415
import ai.chat2db.server.web.api.controller.rdb.converter.RdbWebConverter;
1516
import ai.chat2db.server.web.api.controller.rdb.request.DatabaseCreateRequest;
17+
import ai.chat2db.server.web.api.controller.rdb.request.DatabaseExportRequest;
1618
import ai.chat2db.server.web.api.controller.rdb.request.UpdateDatabaseRequest;
1719
import ai.chat2db.server.web.api.controller.rdb.vo.MetaSchemaVO;
1820
import ai.chat2db.spi.model.Database;
1921
import ai.chat2db.spi.model.MetaSchema;
2022
import ai.chat2db.spi.model.Sql;
23+
import jakarta.servlet.http.HttpServletResponse;
2124
import jakarta.validation.Valid;
2225
import org.apache.commons.lang3.StringUtils;
23-
import org.apache.poi.util.StringUtil;
2426
import org.springframework.beans.factory.annotation.Autowired;
25-
import org.springframework.web.bind.annotation.GetMapping;
26-
import org.springframework.web.bind.annotation.PostMapping;
27-
import org.springframework.web.bind.annotation.RequestBody;
28-
import org.springframework.web.bind.annotation.RequestMapping;
29-
import org.springframework.web.bind.annotation.RestController;
27+
import org.springframework.web.bind.annotation.*;
28+
29+
import java.io.PrintWriter;
30+
import java.util.Objects;
3031

3132
/**
3233
* database controller
@@ -108,4 +109,18 @@ public ActionResult modifyDatabase(@Valid @RequestBody UpdateDatabaseRequest req
108109
.name(request.getNewDatabaseName()).build();
109110
return databaseService.modifyDatabase(param);
110111
}
112+
@PostMapping("/export")
113+
public void exportDatabase(@Valid @RequestBody DatabaseExportRequest request, HttpServletResponse response){
114+
String fileName = Objects.isNull(request.getDatabaseName()) ? request.getSchemaName() : request.getDatabaseName();
115+
response.setContentType("text/sql");
116+
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".sql");
117+
response.setCharacterEncoding("utf-8");
118+
DatabaseExportParam param = databaseConverter.request2param(request);
119+
try (PrintWriter printWriter = response.getWriter()) {
120+
String sql = databaseService.exportDatabase(param);
121+
printWriter.println(sql);
122+
} catch (Exception e) {
123+
throw new RuntimeException(e);
124+
}
125+
}
111126
}

‎chat2db-server/chat2db-server-web/chat2db-server-web-api/src/main/java/ai/chat2db/server/web/api/controller/rdb/converter/DatabaseConverter.java‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,11 +1,15 @@
11
package ai.chat2db.server.web.api.controller.rdb.converter;
22

3+
import ai.chat2db.server.domain.api.param.datasource.DatabaseExportParam;
34
import ai.chat2db.server.web.api.controller.rdb.request.DatabaseCreateRequest;
5+
import ai.chat2db.server.web.api.controller.rdb.request.DatabaseExportRequest;
46
import ai.chat2db.spi.model.Database;
57
import org.mapstruct.Mapper;
68

79
@Mapper(componentModel = "spring")
810
public abstract class DatabaseConverter {
911

1012
public abstract Database request2param(DatabaseCreateRequest request);
13+
14+
public abstract DatabaseExportParam request2param(DatabaseExportRequest request);
1115
}

‎chat2db-server/chat2db-server-web/chat2db-server-web-api/src/main/java/ai/chat2db/server/web/api/controller/rdb/request/DatabaseExportRequest.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.rdb.request;
2+
3+
import ai.chat2db.server.web.api.controller.data.source.request.DataSourceBaseRequest;
4+
import lombok.AllArgsConstructor;
5+
import lombok.Data;
6+
import lombok.NoArgsConstructor;
7+
8+
/**
9+
* @author: zgq
10+
* @date: 2024年02月27日 22:03
11+
*/
12+
@Data
13+
@AllArgsConstructor
14+
@NoArgsConstructor
15+
public class DatabaseExportRequest extends DataSourceBaseRequest {
16+
private Boolean containData;
17+
}

‎chat2db-server/chat2db-spi/src/main/java/ai/chat2db/spi/DBManage.java‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,5 @@
11
package ai.chat2db.spi;
22

3-
import ai.chat2db.server.tools.base.wrapper.result.ActionResult;
43
import ai.chat2db.spi.model.Procedure;
54
import ai.chat2db.spi.sql.ConnectInfo;
65
import jakarta.validation.constraints.NotEmpty;
@@ -121,4 +120,6 @@ void dropProcedure(Connection connection, @NotEmpty String databaseName, String
121120
* @param procedure
122121
*/
123122
void updateProcedure(Connection connection, @NotEmpty String databaseName, String schemaName, @NotNull Procedure procedure) throws SQLException;
123+
124+
String exportDatabase(Connection connection, String databaseName, String schemaName,boolean containData) throws SQLException;
124125
}

‎chat2db-server/chat2db-spi/src/main/java/ai/chat2db/spi/jdbc/DefaultDBManage.java‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -139,6 +139,12 @@ public void updateProcedure(Connection connection, String databaseName, String s
139139

140140
}
141141

142+
@Override
143+
public String exportDatabase(Connection connection, String databaseName, String schemaName, boolean containData) throws SQLException {
144+
return null;
145+
}
146+
147+
142148
@Override
143149
public void dropTable(Connection connection,String databaseName, String schemaName, String tableName) {
144150
String sql = "DROP TABLE "+ tableName ;

0 commit comments

Comments
 (0)