Skip to content

Commit 6167134

Browse files
tmlx1990tmlx1990
committed
fix: OtterMind#974 优化MySQL修改列顺序算法。
1 parent b1de760 commit 6167134

1 file changed

Lines changed: 131 additions & 27 deletions

File tree

  • chat2db-server/chat2db-plugins/chat2db-mysql/src/main/java/ai/chat2db/plugin/mysql/builder

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

Lines changed: 131 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -8,25 +8,27 @@
88
import ai.chat2db.spi.model.Table;
99
import ai.chat2db.spi.model.TableColumn;
1010
import ai.chat2db.spi.model.TableIndex;
11+
import cn.hutool.core.util.ArrayUtil;
1112
import org.apache.commons.lang3.StringUtils;
1213

13-
import java.util.List;
14-
import java.util.stream.Collectors;
14+
import java.util.Arrays;
15+
import java.util.HashMap;
16+
import java.util.Map;
1517

1618

1719
public class MysqlSqlBuilder extends DefaultSqlBuilder implements SqlBuilder {
1820
@Override
1921
public String buildCreateTableSql(Table table) {
2022
StringBuilder script = new StringBuilder();
2123
script.append("CREATE TABLE ");
22-
if(StringUtils.isNotBlank(table.getDatabaseName())) {
24+
if (StringUtils.isNotBlank(table.getDatabaseName())) {
2325
script.append("`").append(table.getDatabaseName()).append("`").append(".");
2426
}
2527
script.append("`").append(table.getName()).append("`").append(" (").append("\n");
2628

2729
// append column
2830
for (TableColumn column : table.getColumnList()) {
29-
if(StringUtils.isBlank(column.getName())|| StringUtils.isBlank(column.getColumnType())){
31+
if (StringUtils.isBlank(column.getName()) || StringUtils.isBlank(column.getColumnType())) {
3032
continue;
3133
}
3234
MysqlColumnTypeEnum typeEnum = MysqlColumnTypeEnum.getByType(column.getColumnType());
@@ -35,7 +37,7 @@ public String buildCreateTableSql(Table table) {
3537

3638
// append primary key and index
3739
for (TableIndex tableIndex : table.getIndexList()) {
38-
if(StringUtils.isBlank(tableIndex.getName())|| StringUtils.isBlank(tableIndex.getType())){
40+
if (StringUtils.isBlank(tableIndex.getName()) || StringUtils.isBlank(tableIndex.getType())) {
3941
continue;
4042
}
4143
MysqlIndexTypeEnum mysqlIndexTypeEnum = MysqlIndexTypeEnum.getByType(tableIndex.getType());
@@ -78,7 +80,7 @@ public String buildCreateTableSql(Table table) {
7880
public String buildModifyTaleSql(Table oldTable, Table newTable) {
7981
StringBuilder script = new StringBuilder();
8082
script.append("ALTER TABLE ");
81-
if(StringUtils.isNotBlank(oldTable.getDatabaseName())) {
83+
if (StringUtils.isNotBlank(oldTable.getDatabaseName())) {
8284
script.append("`").append(oldTable.getDatabaseName()).append("`").append(".");
8385
}
8486
script.append("`").append(oldTable.getName()).append("`").append("\n");
@@ -94,7 +96,7 @@ public String buildModifyTaleSql(Table oldTable, Table newTable) {
9496

9597
// append modify column
9698
for (TableColumn tableColumn : newTable.getColumnList()) {
97-
if (StringUtils.isNotBlank(tableColumn.getEditStatus()) && StringUtils.isNotBlank(tableColumn.getColumnType())&& StringUtils.isNotBlank(tableColumn.getName())){
99+
if (StringUtils.isNotBlank(tableColumn.getEditStatus()) && StringUtils.isNotBlank(tableColumn.getColumnType()) && StringUtils.isNotBlank(tableColumn.getName())) {
98100
MysqlColumnTypeEnum typeEnum = MysqlColumnTypeEnum.getByType(tableColumn.getColumnType());
99101
script.append("\t").append(typeEnum.buildModifyColumn(tableColumn)).append(",\n");
100102
}
@@ -111,7 +113,7 @@ public String buildModifyTaleSql(Table oldTable, Table newTable) {
111113
// append reorder column
112114
script.append("\t").append(buildGenerateReorderColumnSql(oldTable, newTable));
113115

114-
if(script.length()>2) {
116+
if (script.length() > 2) {
115117
script = new StringBuilder(script.substring(0, script.length() - 2));
116118
script.append(";");
117119
}
@@ -120,7 +122,6 @@ public String buildModifyTaleSql(Table oldTable, Table newTable) {
120122
}
121123

122124

123-
124125
@Override
125126
public String pageLimit(String sql, int offset, int pageNo, int pageSize) {
126127
StringBuilder sqlBuilder = new StringBuilder(sql.length() + 14);
@@ -138,12 +139,10 @@ public String pageLimit(String sql, int offset, int pageNo, int pageSize) {
138139
}
139140

140141

141-
142-
143142
@Override
144143
public String buildCreateDatabaseSql(Database database) {
145144
StringBuilder sqlBuilder = new StringBuilder();
146-
sqlBuilder.append("CREATE DATABASE `"+database.getName()+"`");
145+
sqlBuilder.append("CREATE DATABASE `" + database.getName() + "`");
147146
if (StringUtils.isNotBlank(database.getCharset())) {
148147
sqlBuilder.append(" DEFAULT CHARACTER SET=").append(database.getCharset());
149148
}
@@ -152,38 +151,143 @@ public String buildCreateDatabaseSql(Database database) {
152151
}
153152
return sqlBuilder.toString();
154153
}
154+
155155
public String buildGenerateReorderColumnSql(Table oldTable, Table newTable) {
156156
StringBuilder sql = new StringBuilder();
157157
int n = 0;
158-
for (int i = 0; i < newTable.getColumnList().size(); i++) {
159-
TableColumn column = newTable.getColumnList().get(i);
160-
//String columnName = newTable.getColumnList().get(i).getName();
161-
// 获取oldTable.getColumnList()中name属性的list
162-
List<String> oldColumnList = oldTable.getColumnList().stream()
163-
.map(TableColumn::getName)
164-
.collect(Collectors.toList());
165-
166-
if (i > oldColumnList.indexOf(column.getName())) {
158+
// Create a map to store the index of each column in the old table's column list
159+
Map<String, Integer> oldColumnIndexMap = new HashMap<>();
160+
for (int i = 0; i < oldTable.getColumnList().size(); i++) {
161+
oldColumnIndexMap.put(oldTable.getColumnList().get(i).getName(), i);
162+
}
163+
String[] oldColumnArray = oldTable.getColumnList().stream().map(TableColumn::getName).toArray(String[]::new);
164+
String[] newColumnArray = newTable.getColumnList().stream().map(TableColumn::getName).toArray(String[]::new);
165+
166+
buildSql(oldColumnArray, newColumnArray, sql, oldTable, newTable, n);
167+
168+
return sql.toString();
169+
}
170+
171+
private String[] buildSql(String[] originalArray, String[] targetArray, StringBuilder sql, Table oldTable, Table newTable, int n) {
172+
// 先完成首位移动
173+
if (!originalArray[0].equals(targetArray[0])) {
174+
int a = findIndex(originalArray, targetArray[0]);
175+
TableColumn column = oldTable.getColumnList().stream().filter(col -> StringUtils.equals(col.getName(), originalArray[a])).findFirst().get();
176+
String[] newArray = moveElement(originalArray, a, 0);
177+
System.out.println(ArrayUtil.toString(newArray));
178+
sql.append(" MODIFY COLUMN ");
179+
MysqlColumnTypeEnum typeEnum = MysqlColumnTypeEnum.getByType(column.getColumnType());
180+
sql.append(typeEnum.buildColumn(column));
181+
sql.append(" FIRST;\n");
182+
n++;
183+
if (Arrays.equals(newArray, targetArray)) {
184+
return newArray;
185+
}
186+
String[] resultArray = buildSql(newArray, targetArray, sql, oldTable, newTable, n);
187+
if (Arrays.equals(resultArray, targetArray)) {
188+
return resultArray;
189+
}
190+
}
191+
192+
// 在完成最后一位移动
193+
int max = originalArray.length - 1;
194+
if (!originalArray[max].equals(targetArray[max])) {
195+
int a = findIndex(originalArray, targetArray[max]);
196+
//System.out.println("Move " + originalArray[a] + " after " + (a > 0 ? originalArray[max] : "start"));
197+
TableColumn column = oldTable.getColumnList().stream().filter(col -> StringUtils.equals(col.getName(), originalArray[a])).findFirst().get();
198+
String[] newArray = moveElement(originalArray, a, max);
199+
System.out.println(ArrayUtil.toString(newArray));
200+
if (n > 0) {
201+
sql.append("ALTER TABLE ");
202+
if (StringUtils.isNotBlank(oldTable.getDatabaseName())) {
203+
sql.append("`").append(oldTable.getDatabaseName()).append("`").append(".");
204+
}
205+
sql.append("`").append(oldTable.getName()).append("`").append("\n");
206+
}
207+
sql.append(" MODIFY COLUMN ");
208+
MysqlColumnTypeEnum typeEnum = MysqlColumnTypeEnum.getByType(column.getColumnType());
209+
sql.append(typeEnum.buildColumn(column));
210+
sql.append(" ");
211+
sql.append(" AFTER ");
212+
sql.append(oldTable.getColumnList().get(max).getName());
213+
sql.append(";\n");
214+
n++;
215+
if (Arrays.equals(newArray, targetArray)) {
216+
return newArray;
217+
}
218+
String[] resultArray = buildSql(newArray, targetArray, sql, oldTable, newTable, n);
219+
if (Arrays.equals(resultArray, targetArray)) {
220+
return resultArray;
221+
}
222+
}
223+
224+
225+
for (int i = 0; i < originalArray.length; i++) {
226+
int a = findIndex(targetArray, originalArray[i]);
227+
if (i != a && isMoveValid(originalArray, targetArray, i, a)) {
228+
// oldTable.getColumnList中查找name为a
229+
int finalI = i;
230+
TableColumn column = oldTable.getColumnList().stream().filter(col -> StringUtils.equals(col.getName(), originalArray[finalI])).findFirst().get();
167231
if (n > 0) {
168232
sql.append("ALTER TABLE ");
169-
sql.append("`").append(column.getDatabaseName()).append("`").append(".");
233+
if (StringUtils.isNotBlank(oldTable.getDatabaseName())) {
234+
sql.append("`").append(oldTable.getDatabaseName()).append("`").append(".");
235+
}
170236
sql.append("`").append(oldTable.getName()).append("`").append("\n");
171237
}
172238
sql.append(" MODIFY COLUMN ");
173239
MysqlColumnTypeEnum typeEnum = MysqlColumnTypeEnum.getByType(column.getColumnType());
174240
sql.append(typeEnum.buildColumn(column));
175241
sql.append(" ");
176-
if (i > 0) {
177-
sql.append(" AFTER ");
178-
sql.append(newTable.getColumnList().get(i - 1).getName());
242+
sql.append(" AFTER ");
243+
if (i < a) {
244+
sql.append(originalArray[a]);
179245
} else {
180-
sql.append(" FIRST");
246+
sql.append(originalArray[a - 1]);
181247
}
248+
182249
sql.append(";\n");
183250
n++;
251+
String[] newArray = moveElement(originalArray, i, a);
252+
if (Arrays.equals(newArray, targetArray)) {
253+
return newArray;
254+
}
255+
String[] resultArray = buildSql(newArray, targetArray, sql, oldTable, newTable, n);
256+
if (Arrays.equals(resultArray, targetArray)) {
257+
return resultArray;
258+
}
259+
}
260+
}
261+
return null;
262+
}
263+
264+
private static int findIndex(String[] array, String element) {
265+
for (int i = 0; i < array.length; i++) {
266+
if (array[i].equals(element)) {
267+
return i;
184268
}
269+
}
270+
return -1;
271+
}
185272

273+
private static boolean isMoveValid(String[] originalArray, String[] targetArray, int i, int a) {
274+
System.out.println("i : " + i + " a:" + a);
275+
return (i == 0 || a == 0 || !originalArray[i - 1].equals(targetArray[a - 1])) &&
276+
(i >= originalArray.length - 1 || a >= targetArray.length - 1 || !originalArray[i + 1].equals(targetArray[a + 1]));
277+
}
278+
279+
private static String[] moveElement(String[] originalArray, int from, int to) {
280+
String[] newArray = new String[originalArray.length];
281+
System.arraycopy(originalArray, 0, newArray, 0, originalArray.length);
282+
String temp = newArray[from];
283+
if (from < to) {
284+
System.arraycopy(originalArray, from + 1, newArray, from, to - from);
285+
} else {
286+
System.arraycopy(originalArray, to, newArray, to + 1, from - to);
186287
}
187-
return sql.toString();
288+
newArray[to] = temp;
289+
System.out.println(ArrayUtil.toString(newArray));
290+
return newArray;
188291
}
292+
189293
}

0 commit comments

Comments
 (0)