Skip to content

Commit df15363

Browse files
Chat2DB-ProChat2DB-Pro
committed
Improve startup speed
1 parent 4b11dd8 commit df15363

28 files changed

Lines changed: 556 additions & 85 deletions

File tree

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

Lines changed: 13 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1,11 +1,5 @@
11
package ai.chat2db.server.domain.core.impl;
22

3-
import java.time.LocalDateTime;
4-
import java.util.List;
5-
import java.util.Map;
6-
import java.util.function.Function;
7-
import java.util.stream.Collectors;
8-
93
import ai.chat2db.server.domain.api.chart.ChartCreateParam;
104
import ai.chat2db.server.domain.api.chart.ChartListQueryParam;
115
import ai.chat2db.server.domain.api.chart.ChartQueryParam;
@@ -36,6 +30,12 @@
3630
import org.springframework.beans.factory.annotation.Autowired;
3731
import org.springframework.stereotype.Service;
3832

33+
import java.time.LocalDateTime;
34+
import java.util.List;
35+
import java.util.Map;
36+
import java.util.function.Function;
37+
import java.util.stream.Collectors;
38+
3939
/**
4040
* @author moji
4141
* @version ChartServiceImpl.java, v 0.1 2023年06月09日 16:06 moji Exp $
@@ -47,8 +47,11 @@ public class ChartServiceImpl implements ChartService {
4747
@Autowired
4848
private DataSourceService dataSourceService;
4949

50-
@Autowired
51-
private DashboardChartRelationMapper dashboardChartRelationMapper;
50+
51+
private DashboardChartRelationMapper getDashboardMapper() {
52+
return Dbutils.getMapper(DashboardChartRelationMapper.class);
53+
}
54+
5255

5356
@Autowired
5457
private ChartConverter chartConverter;
@@ -135,10 +138,10 @@ public ActionResult deleteWithPermission(Long id) {
135138
getMapper().updateById(chartDO);
136139
LambdaQueryWrapper<DashboardChartRelationDO> queryWrapper = new LambdaQueryWrapper<>();
137140
queryWrapper.eq(DashboardChartRelationDO::getChartId, id);
138-
List<DashboardChartRelationDO> relationDO = dashboardChartRelationMapper.selectList(queryWrapper);
141+
List<DashboardChartRelationDO> relationDO = getDashboardMapper().selectList(queryWrapper);
139142
List<Long> relationIds = relationDO.stream().map(DashboardChartRelationDO::getId).toList();
140143
if (CollectionUtils.isNotEmpty(relationIds)) {
141-
dashboardChartRelationMapper.deleteBatchIds(relationIds);
144+
getDashboardMapper().deleteBatchIds(relationIds);
142145
}
143146
return ActionResult.isSuccess();
144147
}

‎chat2db-server/chat2db-server-domain/chat2db-server-domain-repository/pom.xml‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -29,5 +29,13 @@
2929
<groupId>com.zaxxer</groupId>
3030
<artifactId>HikariCP</artifactId>
3131
</dependency>
32+
<dependency>
33+
<groupId>org.flywaydb</groupId>
34+
<artifactId>flyway-core</artifactId>
35+
</dependency>
36+
<dependency>
37+
<groupId>org.flywaydb</groupId>
38+
<artifactId>flyway-mysql</artifactId>
39+
</dependency>
3240
</dependencies>
3341
</project>

‎chat2db-server/chat2db-server-domain/chat2db-server-domain-repository/src/main/java/ai/chat2db/server/domain/repository/Dbutils.java‎

Lines changed: 66 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,11 @@
11
package ai.chat2db.server.domain.repository;
22

3+
import ai.chat2db.server.domain.repository.entity.DataSourceDO;
4+
import ai.chat2db.server.domain.repository.mapper.DataSourceMapper;
5+
import ai.chat2db.server.tools.common.model.ConfigJson;
6+
import ai.chat2db.server.tools.common.util.ConfigUtils;
7+
import cn.hutool.core.lang.UUID;
8+
import com.alibaba.fastjson2.JSON;
39
import com.baomidou.mybatisplus.annotation.DbType;
410
import com.baomidou.mybatisplus.core.MybatisConfiguration;
511
import com.baomidou.mybatisplus.core.config.GlobalConfig;
@@ -20,6 +26,7 @@
2026
import org.apache.ibatis.session.SqlSessionFactory;
2127
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
2228
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
29+
import org.flywaydb.core.Flyway;
2330

2431
import javax.sql.DataSource;
2532
import java.io.File;
@@ -29,12 +36,29 @@
2936
import java.net.JarURLConnection;
3037
import java.net.URL;
3138
import java.util.Enumeration;
39+
import java.util.concurrent.ExecutorService;
40+
import java.util.concurrent.Executors;
3241
import java.util.jar.JarEntry;
3342
import java.util.jar.JarFile;
3443

3544
@Slf4j
3645
public class Dbutils {
3746

47+
private static final ThreadLocal<SqlSession> SQL_SESSION_THREAD_LOCAL = new ThreadLocal<>();
48+
49+
public static void setSession() {
50+
SqlSession session = sqlSessionFactory.openSession(true);
51+
SQL_SESSION_THREAD_LOCAL.set(session);
52+
}
53+
54+
public static void removeSession() {
55+
SqlSession session = SQL_SESSION_THREAD_LOCAL.get();
56+
if (session != null) {
57+
session.close();
58+
}
59+
SQL_SESSION_THREAD_LOCAL.remove();
60+
}
61+
3862
private static SqlSessionFactory sqlSessionFactory;
3963

4064
static {
@@ -45,6 +69,7 @@ public class Dbutils {
4569
}
4670
}
4771

72+
4873
private static void before() throws IOException {
4974
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
5075
//这是mybatis-plus的配置对象，对mybatis的Configuration进行增强
@@ -65,16 +90,34 @@ private static void before() throws IOException {
6590
globalConfig.setIdentifierGenerator(new DefaultIdentifierGenerator());
6691
//设置超类mapper
6792
globalConfig.setSuperMapperClass(BaseMapper.class);
68-
Environment environment = new Environment("1", new JdbcTransactionFactory(), initDataSource());
93+
DataSource dataSource = initDataSource();
94+
Environment environment = new Environment("1", new JdbcTransactionFactory(), dataSource);
6995
configuration.setEnvironment(environment);
7096
//设置数据源
7197
registryMapperXml(configuration, "mapper");
7298
//构建sqlSessionFactory
7399
sqlSessionFactory = builder.build(configuration);
100+
101+
initFlyway(dataSource);
74102
//创建session
75103

76104
}
77105

106+
private static void initFlyway(DataSource dataSource) {
107+
String currentVersion = ConfigUtils.getLocalVersion();
108+
ConfigJson configJson = ConfigUtils.getConfig();
109+
// Represents that the current version has been successfully launched
110+
if (StringUtils.isNotBlank(currentVersion) && configJson != null && StringUtils.equals(currentVersion,
111+
configJson.getLatestStartupSuccessVersion())) {
112+
return;
113+
}
114+
Flyway flyway = Flyway.configure()
115+
.dataSource(dataSource)
116+
.locations("classpath:db/migration")
117+
.load();
118+
flyway.migrate();
119+
}
120+
78121
/**
79122
* 初始化配置
80123
*
@@ -95,19 +138,20 @@ private static void initConfiguration(MybatisConfiguration configuration) {
95138
private static DataSource initDataSource() {
96139
HikariDataSource dataSource = new HikariDataSource();
97140
String environment = StringUtils.defaultString(System.getProperty("spring.profiles.active"), "dev");
98-
if("dev".equalsIgnoreCase(environment)) {
99-
dataSource.setJdbcUrl("jdbc:h2:~/.chat2db/db/chat2db_dev;FILE_LOCK=NO;MODE=MYSQL");
100-
}else {
141+
if ("dev".equalsIgnoreCase(environment)) {
142+
dataSource.setJdbcUrl("jdbc:h2:file:~/.chat2db/db/chat2db_dev;MODE=MYSQL");
143+
} else {
101144
dataSource.setJdbcUrl("jdbc:h2:~/.chat2db/db/chat2db;MODE=MYSQL;FILE_LOCK=NO");
102145
}
103146
dataSource.setDriverClassName("org.h2.Driver");
104147
dataSource.setIdleTimeout(60000);
105148
dataSource.setAutoCommit(true);
106-
dataSource.setMaximumPoolSize(5);
149+
dataSource.setMaximumPoolSize(500);
107150
dataSource.setMinimumIdle(1);
108151
dataSource.setMaxLifetime(60000 * 10);
109152
dataSource.setConnectionTestQuery("SELECT 1");
110153
return dataSource;
154+
111155
}
112156

113157
/**
@@ -122,7 +166,7 @@ private static Interceptor initInterceptor() {
122166
PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
123167
paginationInnerInterceptor.setDbType(DbType.H2);
124168
paginationInnerInterceptor.setOverflow(true);
125-
paginationInnerInterceptor.setMaxLimit(500L);
169+
paginationInnerInterceptor.setMaxLimit(2000L);
126170
interceptor.addInnerInterceptor(paginationInnerInterceptor);
127171
return interceptor;
128172
}
@@ -167,7 +211,22 @@ private static void registryMapperXml(MybatisConfiguration configuration, String
167211
}
168212

169213
public static <T> T getMapper(Class<T> clazz) {
170-
SqlSession session = sqlSessionFactory.openSession();
214+
SqlSession session = SQL_SESSION_THREAD_LOCAL.get();
171215
return session.getMapper(clazz);
172216
}
217+
218+
// public static void main(String[] args) {
219+
//
220+
// ExecutorService e = Executors.newCachedThreadPool();
221+
// for (int i = 0; i < 20; i++) {
222+
// e.execute(() -> {
223+
// SqlSession session = sqlSessionFactory.openSession();
224+
// DataSourceMapper mapper = session.getMapper(DataSourceMapper.class);
225+
// DataSourceDO dataSourceDO = mapper.selectById(1);
226+
// session.close();
227+
// System.out.println(JSON.toJSONString(dataSourceDO));
228+
// });
229+
// }
230+
//
231+
// }
173232
}

‎chat2db-server/chat2db-server-domain/chat2db-server-domain-repository/src/main/resources/db/migration/V1_0_0__初始化信息.sql‎

Lines changed: 70 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,70 @@
1+
CREATE TABLE IF NOT EXISTS `data_source` (
2+
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
3+
`gmt_create` datetime NOT NULL COMMENT '创建时间',
4+
`gmt_modified` datetime NOT NULL COMMENT '修改时间',
5+
`alias` varchar(128) DEFAULT NULL COMMENT '别名',
6+
`url` varchar(1024) DEFAULT NULL COMMENT '连接地址',
7+
`user_name` varchar(128) DEFAULT NULL COMMENT '用户名',
8+
`password` varchar(256) DEFAULT NULL COMMENT '密码',
9+
`type` varchar(32) DEFAULT NULL COMMENT '数据库类型',
10+
`env_type` varchar(32) DEFAULT NULL COMMENT '环境类型',
11+
`user_id` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT '用户id',
12+
PRIMARY KEY (`id`)
13+
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='数据源连接表'
14+
;
15+
16+
CREATE TABLE IF NOT EXISTS `operation_log` (
17+
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
18+
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
19+
`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
20+
`data_source_id` bigint(20) unsigned NOT NULL COMMENT '数据源连接ID',
21+
`database_name` varchar(128) DEFAULT NULL COMMENT 'db名称',
22+
`type` varchar(32) NOT NULL COMMENT '数据库类型',
23+
`ddl` text DEFAULT NULL COMMENT 'ddl内容',
24+
`user_id` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT '用户id',
25+
PRIMARY KEY (`id`)
26+
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='我的执行记录表'
27+
;
28+
29+
CREATE TABLE IF NOT EXISTS `operation_saved` (
30+
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
31+
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
32+
`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
33+
`data_source_id` bigint(20) unsigned NOT NULL COMMENT '数据源连接ID',
34+
`database_name` varchar(128) DEFAULT NULL COMMENT 'db名称',
35+
`name` varchar(128) DEFAULT NULL COMMENT '保存名称',
36+
`type` varchar(32) NOT NULL COMMENT '数据库类型',
37+
`status` varchar(32) NOT NULL COMMENT 'ddl语句状态:DRAFT/RELEASE',
38+
`ddl` text DEFAULT NULL COMMENT 'ddl内容',
39+
`tab_opened` text DEFAULT NULL COMMENT '是否在tab中被打开,y表示打开,n表示未打开',
40+
`user_id` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT '用户id',
41+
PRIMARY KEY (`id`)
42+
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='我的保存表'
43+
;
44+
45+
46+
CREATE TABLE IF NOT EXISTS `dbhub_user` (
47+
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
48+
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
49+
`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
50+
`user_name` varchar(32) NOT NULL COMMENT '用户名',
51+
`password` varchar(256) DEFAULT NULL COMMENT '密码',
52+
`nick_name` varchar(256) DEFAULT NULL COMMENT '昵称',
53+
`email` varchar(256) DEFAULT NULL COMMENT '邮箱',
54+
PRIMARY KEY (`id`)
55+
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数据源连接表'
56+
;
57+
58+
CREATE TABLE IF NOT EXISTS `system_config` (
59+
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
60+
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
61+
`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
62+
`code` varchar(32) NOT NULL COMMENT '配置项编码',
63+
`content` varchar(256) DEFAULT NULL COMMENT '配置项内容',
64+
`summary` varchar(256) DEFAULT NULL COMMENT '配置项说明',
65+
PRIMARY KEY (`id`)
66+
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='配置中心表'
67+
;
68+
create UNIQUE INDEX uk_code on system_config(code) ;
69+
70+
INSERT INTO `dbhub_user` (`user_name`,`password`,`nick_name`) VALUES ('dbhub','$2a$10$yElafjDHPoPHSaCo6cjJGuWmtXWNVz/cOOOtDg99eNfvUfalzfane','管理员');

‎chat2db-server/chat2db-server-domain/chat2db-server-domain-repository/src/main/resources/db/migration/V1_0_2__修改Console.sql‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
ALTER TABLE `operation_saved` ADD COLUMN `db_schema_name` varchar(128) NULL COMMENT 'schema名称';

‎chat2db-server/chat2db-server-domain/chat2db-server-domain-repository/src/main/resources/db/migration/V1_0_3__增加SSH.sql‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
ALTER TABLE `data_source` ADD COLUMN `host` varchar(128) NULL COMMENT 'host地址';
2+
ALTER TABLE `data_source` ADD COLUMN `port` varchar(128) NULL COMMENT '端口';
3+
ALTER TABLE `data_source` ADD COLUMN `ssh` varchar(1024) NULL COMMENT 'ssh配置信息json';
4+
ALTER TABLE `data_source` ADD COLUMN `ssl` varchar(1024) NULL COMMENT 'ssl配置信息json';
5+
ALTER TABLE `data_source` ADD COLUMN `sid` varchar(32) NULL COMMENT 'sid';
6+
ALTER TABLE `data_source` ADD COLUMN `driver` varchar(128) NULL COMMENT '驱动信息';
7+
ALTER TABLE `data_source` ADD COLUMN `jdbc` varchar(128) NULL COMMENT 'jdbc版本';
8+
ALTER TABLE `data_source` ADD COLUMN `extend_info` varchar(4096) NULL COMMENT '自定义扩展字段json';
9+
create INDEX idx_user_id on data_source(user_id) ;

‎chat2db-server/chat2db-server-domain/chat2db-server-domain-repository/src/main/resources/db/migration/V1_0_4__增加报表.sql‎

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,39 @@
1+
CREATE TABLE IF NOT EXISTS `dashboard` (
2+
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
3+
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
4+
`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
5+
`name` varchar(128) DEFAULT NULL COMMENT '报表名称',
6+
`description` varchar(128) DEFAULT NULL COMMENT '报表描述',
7+
`schema` text DEFAULT NULL COMMENT '报表布局信息',
8+
`deleted` text DEFAULT NULL COMMENT '是否被删除,y表示删除,n表示未删除',
9+
`user_id` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT '用户id',
10+
PRIMARY KEY (`id`)
11+
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='自定义报表表'
12+
;
13+
14+
CREATE TABLE IF NOT EXISTS `chart` (
15+
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
16+
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
17+
`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
18+
`name` varchar(128) DEFAULT NULL COMMENT '图表名称',
19+
`description` varchar(128) DEFAULT NULL COMMENT '图表描述',
20+
`schema` text DEFAULT NULL COMMENT '图表信息',
21+
`data_source_id` bigint(20) unsigned DEFAULT NULL COMMENT '数据源连接ID',
22+
`type` varchar(32) DEFAULT NULL COMMENT '数据库类型',
23+
`database_name` varchar(128) DEFAULT NULL COMMENT 'db名称',
24+
`ddl` text DEFAULT NULL COMMENT 'ddl内容',
25+
`deleted` text DEFAULT NULL COMMENT '是否被删除,y表示删除,n表示未删除',
26+
`user_id` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT '用户id',
27+
PRIMARY KEY (`id`)
28+
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='自定义报表表'
29+
;
30+
31+
CREATE TABLE IF NOT EXISTS `dashboard_chart_relation` (
32+
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
33+
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
34+
`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
35+
`dashboard_id` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT '报表id',
36+
`chart_id` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT '图表id',
37+
PRIMARY KEY (`id`)
38+
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='自定义报表表'
39+
;

‎chat2db-server/chat2db-server-domain/chat2db-server-domain-repository/src/main/resources/db/migration/V1_0_5__增加置顶表.sql‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,14 @@
1+
CREATE TABLE IF NOT EXISTS `pin_table` (
2+
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
3+
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
4+
`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
5+
`data_source_id` bigint(20) unsigned NOT NULL COMMENT '数据源连接ID',
6+
`database_name` varchar(128) DEFAULT NULL COMMENT 'db名称',
7+
`schema_name` varchar(128) DEFAULT NULL COMMENT 'schema名称',
8+
`table_name` varchar(128) DEFAULT NULL COMMENT 'table_name',
9+
`deleted` text DEFAULT NULL COMMENT '是否被删除,y表示删除,n表示未删除',
10+
`user_id` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT '用户id',
11+
PRIMARY KEY (`id`)
12+
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='PIN TABLES'
13+
;
14+
create INDEX idx_user_id_data_source_id on pin_table(user_id,data_source_id) ;

0 commit comments

Comments
 (0)