Skip to content

Commit a016459

Browse files
shanhexishanhexi
committed
merge
2 parents c949fc6 + 17e3dda commit a016459

9 files changed

Lines changed: 691 additions & 16 deletions

File tree

‎chat2db-client/src/typings/ai.ts‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,9 @@
11
export enum AIType {
22
CHAT2DBAI = 'CHAT2DBAI',
33
ZHIPUAI = 'ZHIPUAI',
4-
// BAICHUANAI='BAICHUANAI',
5-
// WENXINAI='WENXINAI',
6-
// TONGYIQIANWENAI='TONGYIQIANWENAI',
4+
BAICHUANAI='BAICHUANAI',
5+
WENXINAI='WENXINAI',
6+
TONGYIQIANWENAI='TONGYIQIANWENAI',
77
OPENAI = 'OPENAI',
88
AZUREAI = 'AZUREAI',
99
RESTAI = 'RESTAI',

‎chat2db-server/chat2db-plugins/chat2db-db2/src/main/java/ai/chat2db/plugin/db2/builder/DB2SqlBuilder.java‎

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,5 @@
11
package ai.chat2db.plugin.db2.builder;
22

3-
import ai.chat2db.spi.SqlBuilder;
43
import ai.chat2db.spi.jdbc.DefaultSqlBuilder;
54
import ai.chat2db.spi.model.Schema;
65
import org.apache.commons.lang3.StringUtils;
@@ -13,9 +12,9 @@ public String pageLimit(String sql, int offset, int pageNo, int pageSize) {
1312
int startRow = offset + 1;
1413
int endRow = offset + pageSize;
1514
StringBuilder sqlBuilder = new StringBuilder(sql.length() + 120);
16-
sqlBuilder.append("SELECT * FROM (SELECT TMP_PAGE.*,ROWNUMBER() OVER() AS PAGEHELPER_ROW_ID FROM ( \n");
15+
sqlBuilder.append("SELECT * FROM (SELECT TMP_PAGE.*,ROWNUMBER() OVER() AS CAHT2DB_AUTO_ROW_ID FROM ( \n");
1716
sqlBuilder.append(sql);
18-
sqlBuilder.append("\n ) AS TMP_PAGE) TMP_PAGE WHERE PAGEHELPER_ROW_ID BETWEEN ");
17+
sqlBuilder.append("\n ) AS TMP_PAGE) TMP_PAGE WHERE CAHT2DB_AUTO_ROW_ID BETWEEN ");
1918
sqlBuilder.append(startRow);
2019
sqlBuilder.append(" AND ");
2120
sqlBuilder.append(endRow);
@@ -33,4 +32,7 @@ public String buildCreateSchemaSql(Schema schema) {
3332

3433
return sqlBuilder.toString();
3534
}
35+
36+
37+
3638
}

‎chat2db-server/chat2db-plugins/chat2db-dm/src/main/java/ai/chat2db/plugin/dm/DMDBManage.java‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,13 +1,31 @@
11
package ai.chat2db.plugin.dm;
22

33
import java.sql.Connection;
4+
import java.sql.SQLException;
45

56
import ai.chat2db.spi.DBManage;
67
import ai.chat2db.spi.jdbc.DefaultDBManage;
8+
import ai.chat2db.spi.sql.Chat2DBContext;
9+
import ai.chat2db.spi.sql.ConnectInfo;
710
import ai.chat2db.spi.sql.SQLExecutor;
11+
import org.apache.commons.lang3.ObjectUtils;
12+
import org.apache.commons.lang3.StringUtils;
813

914
public class DMDBManage extends DefaultDBManage implements DBManage {
1015

16+
@Override
17+
public void connectDatabase(Connection connection, String database) {
18+
ConnectInfo connectInfo = Chat2DBContext.getConnectInfo();
19+
if (ObjectUtils.anyNull(connectInfo) || StringUtils.isEmpty(connectInfo.getSchemaName())) {
20+
return;
21+
}
22+
String schemaName = connectInfo.getSchemaName();
23+
try {
24+
SQLExecutor.getInstance().execute(connection, "SET SCHEMA \"" + schemaName + "\"");
25+
} catch (SQLException e) {
26+
e.printStackTrace();
27+
}
28+
}
1129

1230
@Override
1331
public void dropTable(Connection connection, String databaseName, String schemaName, String tableName) {

‎chat2db-server/chat2db-plugins/chat2db-dm/src/main/java/ai/chat2db/plugin/dm/DMMetaData.java‎

Lines changed: 90 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,18 +1,24 @@
11
package ai.chat2db.plugin.dm;
22

33
import java.sql.Connection;
4+
import java.sql.ResultSet;
45
import java.sql.SQLException;
5-
import java.util.ArrayList;
6-
import java.util.Arrays;
7-
import java.util.List;
6+
import java.util.*;
7+
import java.util.stream.Collectors;
88

9+
import ai.chat2db.plugin.dm.builder.DMSqlBuilder;
10+
import ai.chat2db.plugin.dm.type.DMColumnTypeEnum;
11+
import ai.chat2db.plugin.dm.type.DMIndexTypeEnum;
912
import ai.chat2db.spi.MetaData;
13+
import ai.chat2db.spi.SqlBuilder;
1014
import ai.chat2db.spi.jdbc.DefaultMetaService;
1115
import ai.chat2db.spi.model.*;
1216
import ai.chat2db.spi.sql.SQLExecutor;
1317
import ai.chat2db.spi.util.SortUtils;
1418
import ai.chat2db.spi.util.SqlUtils;
19+
import com.google.common.collect.Lists;
1520
import jakarta.validation.constraints.NotEmpty;
21+
import org.apache.commons.lang3.StringUtils;
1622

1723
public class DMMetaData extends DefaultMetaService implements MetaData {
1824

@@ -138,4 +144,85 @@ public Table view(Connection connection, String databaseName, String schemaName,
138144
return table;
139145
});
140146
}
147+
148+
private static String INDEX_SQL = "SELECT i.TABLE_NAME, i.INDEX_TYPE, i.INDEX_NAME, i.UNIQUENESS ,c.COLUMN_NAME, c.COLUMN_POSITION, c.DESCEND, cons.CONSTRAINT_TYPE FROM ALL_INDEXES i JOIN ALL_IND_COLUMNS c ON i.INDEX_NAME = c.INDEX_NAME AND i.TABLE_NAME = c.TABLE_NAME AND i.TABLE_OWNER = c.TABLE_OWNER LEFT JOIN ALL_CONSTRAINTS cons ON i.INDEX_NAME = cons.INDEX_NAME AND i.TABLE_NAME = cons.TABLE_NAME AND i.TABLE_OWNER = cons.OWNER WHERE i.TABLE_NAME = '%s' ORDER BY i.INDEX_NAME, c.COLUMN_POSITION;";
149+
150+
@Override
151+
public List<TableIndex> indexes(Connection connection, String databaseName, String schemaName, String tableName) {
152+
String sql = String.format(INDEX_SQL, schemaName, tableName);
153+
return SQLExecutor.getInstance().execute(connection, sql, resultSet -> {
154+
LinkedHashMap<String, TableIndex> map = new LinkedHashMap();
155+
while (resultSet.next()) {
156+
String keyName = resultSet.getString("INDEX_NAME");
157+
TableIndex tableIndex = map.get(keyName);
158+
if (tableIndex != null) {
159+
List<TableIndexColumn> columnList = tableIndex.getColumnList();
160+
columnList.add(getTableIndexColumn(resultSet));
161+
columnList = columnList.stream().sorted(Comparator.comparing(TableIndexColumn::getOrdinalPosition))
162+
.collect(Collectors.toList());
163+
tableIndex.setColumnList(columnList);
164+
} else {
165+
TableIndex index = new TableIndex();
166+
index.setDatabaseName(databaseName);
167+
index.setSchemaName(schemaName);
168+
index.setTableName(tableName);
169+
index.setName(keyName);
170+
index.setUnique("UNIQUE".equalsIgnoreCase(resultSet.getString("UNIQUENESS")));
171+
// index.setType(resultSet.getString("Index_type"));
172+
// index.setComment(resultSet.getString("Index_comment"));
173+
List<TableIndexColumn> tableIndexColumns = new ArrayList<>();
174+
tableIndexColumns.add(getTableIndexColumn(resultSet));
175+
index.setColumnList(tableIndexColumns);
176+
if ("P".equalsIgnoreCase(resultSet.getString("CONSTRAINT_TYPE"))) {
177+
index.setType(DMIndexTypeEnum.PRIMARY_KEY.getName());
178+
} else if (index.getUnique()) {
179+
index.setType(DMIndexTypeEnum.UNIQUE.getName());
180+
} else if ("BITMAP".equalsIgnoreCase(resultSet.getString("INDEX_TYPE"))) {
181+
index.setType(DMIndexTypeEnum.BITMAP.getName());
182+
} else {
183+
index.setType(DMIndexTypeEnum.NORMAL.getName());
184+
}
185+
map.put(keyName, index);
186+
}
187+
}
188+
return map.values().stream().collect(Collectors.toList());
189+
});
190+
191+
}
192+
193+
private TableIndexColumn getTableIndexColumn(ResultSet resultSet) throws SQLException {
194+
TableIndexColumn tableIndexColumn = new TableIndexColumn();
195+
tableIndexColumn.setColumnName(resultSet.getString("COLUMN_NAME"));
196+
tableIndexColumn.setOrdinalPosition(resultSet.getShort("COLUMN_POSITION"));
197+
// tableIndexColumn.setCollation(resultSet.getString("Collation"));
198+
// tableIndexColumn.setCardinality(resultSet.getLong("Cardinality"));
199+
// tableIndexColumn.setSubPart(resultSet.getLong("Sub_part"));
200+
String collation = resultSet.getString("DESCEND");
201+
if ("ASC".equalsIgnoreCase(collation)) {
202+
tableIndexColumn.setAscOrDesc("ASC");
203+
} else if ("DESC".equalsIgnoreCase(collation)) {
204+
tableIndexColumn.setAscOrDesc("DESC");
205+
}
206+
return tableIndexColumn;
207+
}
208+
209+
@Override
210+
public SqlBuilder getSqlBuilder() {
211+
return new DMSqlBuilder();
212+
}
213+
214+
@Override
215+
public TableMeta getTableMeta(String databaseName, String schemaName, String tableName) {
216+
return TableMeta.builder()
217+
.columnTypes(DMColumnTypeEnum.getTypes())
218+
.charsets(Lists.newArrayList())
219+
.collations(Lists.newArrayList())
220+
.indexTypes(DMIndexTypeEnum.getIndexTypes())
221+
.build();
222+
}
223+
224+
@Override
225+
public String getMetaDataName(String... names) {
226+
return Arrays.stream(names).filter(name -> StringUtils.isNotBlank(name)).map(name -> "\"" + name + "\"").collect(Collectors.joining("."));
227+
}
141228
}

‎chat2db-server/chat2db-plugins/chat2db-dm/src/main/java/ai/chat2db/plugin/dm/builder/DMSqlBuilder.java‎

Lines changed: 133 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,133 @@
1+
package ai.chat2db.plugin.dm.builder;
2+
3+
import ai.chat2db.plugin.dm.type.DMColumnTypeEnum;
4+
import ai.chat2db.plugin.dm.type.DMIndexTypeEnum;
5+
import ai.chat2db.spi.jdbc.DefaultSqlBuilder;
6+
import ai.chat2db.spi.model.Schema;
7+
import ai.chat2db.spi.model.Table;
8+
import ai.chat2db.spi.model.TableColumn;
9+
import ai.chat2db.spi.model.TableIndex;
10+
import org.apache.commons.lang3.StringUtils;
11+
12+
public class DMSqlBuilder extends DefaultSqlBuilder {
13+
14+
15+
@Override
16+
public String buildCreateTableSql(Table table) {
17+
StringBuilder script = new StringBuilder();
18+
19+
script.append("CREATE TABLE ").append("\"").append(table.getSchemaName()).append("\".\"").append(table.getName()).append("\" (").append("\n");
20+
21+
for (TableColumn column : table.getColumnList()) {
22+
if (StringUtils.isBlank(column.getName()) || StringUtils.isBlank(column.getColumnType())) {
23+
continue;
24+
}
25+
DMColumnTypeEnum typeEnum = DMColumnTypeEnum.getByType(column.getColumnType());
26+
script.append("\t").append(typeEnum.buildCreateColumnSql(column)).append(",\n");
27+
}
28+
29+
script = new StringBuilder(script.substring(0, script.length() - 2));
30+
script.append("\n);");
31+
32+
for (TableIndex tableIndex : table.getIndexList()) {
33+
if (StringUtils.isBlank(tableIndex.getName()) || StringUtils.isBlank(tableIndex.getType())) {
34+
continue;
35+
}
36+
DMIndexTypeEnum indexTypeEnum = DMIndexTypeEnum.getByType(tableIndex.getType());
37+
script.append("\n").append("").append(indexTypeEnum.buildIndexScript(tableIndex)).append(";");
38+
}
39+
40+
for (TableColumn column : table.getColumnList()) {
41+
if (StringUtils.isBlank(column.getName()) || StringUtils.isBlank(column.getColumnType()) || StringUtils.isBlank(column.getComment())) {
42+
continue;
43+
}
44+
script.append("\n").append(buildComment(column)).append(";");
45+
}
46+
47+
if (StringUtils.isNotBlank(table.getComment())) {
48+
script.append("\n").append(buildTableComment(table)).append(";");
49+
}
50+
51+
52+
return script.toString();
53+
}
54+
55+
private String buildTableComment(Table table) {
56+
StringBuilder script = new StringBuilder();
57+
script.append("COMMENT ON TABLE ").append("\"").append(table.getSchemaName()).append("\".\"").append(table.getName()).append("\" IS '").append(table.getComment()).append("'");
58+
return script.toString();
59+
}
60+
61+
private String buildComment(TableColumn column) {
62+
StringBuilder script = new StringBuilder();
63+
script.append("COMMENT ON COLUMN ").append("\"").append(column.getSchemaName()).append("\".\"").append(column.getTableName()).append("\".\"").append(column.getName()).append("\" IS '").append(column.getComment()).append("'");
64+
return script.toString();
65+
}
66+
67+
@Override
68+
public String buildModifyTaleSql(Table oldTable, Table newTable) {
69+
StringBuilder script = new StringBuilder();
70+
71+
if (!StringUtils.equalsIgnoreCase(oldTable.getName(), newTable.getName())) {
72+
script.append("ALTER TABLE ").append("\"").append(oldTable.getSchemaName()).append("\".\"").append(oldTable.getName()).append("\"");
73+
script.append(" ").append("RENAME TO ").append("\"").append(newTable.getName()).append("\"").append(";\n");
74+
}
75+
if (!StringUtils.equalsIgnoreCase(oldTable.getComment(), newTable.getComment())) {
76+
script.append("").append(buildTableComment(newTable)).append(";\n");
77+
}
78+
79+
80+
// append modify column
81+
for (TableColumn tableColumn : newTable.getColumnList()) {
82+
if (StringUtils.isNotBlank(tableColumn.getEditStatus())) {
83+
DMColumnTypeEnum typeEnum = DMColumnTypeEnum.getByType(tableColumn.getColumnType());
84+
script.append("\t").append(typeEnum.buildModifyColumn(tableColumn)).append(";\n");
85+
if (StringUtils.isNotBlank(tableColumn.getComment())) {
86+
script.append("\n").append(buildComment(tableColumn)).append(";\n");
87+
}
88+
}
89+
}
90+
91+
// append modify index
92+
for (TableIndex tableIndex : newTable.getIndexList()) {
93+
if (StringUtils.isNotBlank(tableIndex.getEditStatus()) && StringUtils.isNotBlank(tableIndex.getType())) {
94+
DMIndexTypeEnum mysqlIndexTypeEnum = DMIndexTypeEnum.getByType(tableIndex.getType());
95+
script.append("\t").append(mysqlIndexTypeEnum.buildModifyIndex(tableIndex)).append(";\n");
96+
}
97+
}
98+
if (script.length() > 2) {
99+
script = new StringBuilder(script.substring(0, script.length() - 2));
100+
script.append(";");
101+
}
102+
103+
return script.toString();
104+
}
105+
106+
107+
@Override
108+
public String pageLimit(String sql, int offset, int pageNo, int pageSize) {
109+
StringBuilder sqlStr = new StringBuilder(sql.length() + 17);
110+
sqlStr.append(sql);
111+
if (offset == 0) {
112+
sqlStr.append(" LIMIT ");
113+
sqlStr.append(pageSize);
114+
} else {
115+
sqlStr.append(" LIMIT ");
116+
sqlStr.append(pageSize);
117+
sqlStr.append(" OFFSET ");
118+
sqlStr.append(offset);
119+
}
120+
return sqlStr.toString();
121+
}
122+
123+
@Override
124+
public String buildCreateSchemaSql(Schema schema) {
125+
StringBuilder sqlBuilder = new StringBuilder();
126+
sqlBuilder.append("CREATE SCHEMA \""+schema.getName()+"\"");
127+
if(StringUtils.isNotBlank(schema.getOwner())){
128+
sqlBuilder.append(" AUTHORIZATION ").append(schema.getOwner());
129+
}
130+
131+
return sqlBuilder.toString();
132+
}
133+
}

0 commit comments

Comments
 (0)