Skip to content

Commit eb9ff6e

Browse files
Chat2DB-ProChat2DB-Pro
authored
Merge pull request OtterMind#1008 from tmlx1990/fix974
Fix974、992 修复MySQL修改列顺序后生成SQL语句错误问题
2 parents 804cb03 + 6167134 commit eb9ff6e

2 files changed

Lines changed: 166 additions & 10 deletions

File tree

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

Lines changed: 154 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -8,22 +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

14+
import java.util.Arrays;
15+
import java.util.HashMap;
16+
import java.util.Map;
17+
1318

1419
public class MysqlSqlBuilder extends DefaultSqlBuilder implements SqlBuilder {
1520
@Override
1621
public String buildCreateTableSql(Table table) {
1722
StringBuilder script = new StringBuilder();
1823
script.append("CREATE TABLE ");
19-
if(StringUtils.isNotBlank(table.getDatabaseName())) {
24+
if (StringUtils.isNotBlank(table.getDatabaseName())) {
2025
script.append("`").append(table.getDatabaseName()).append("`").append(".");
2126
}
2227
script.append("`").append(table.getName()).append("`").append(" (").append("\n");
2328

2429
// append column
2530
for (TableColumn column : table.getColumnList()) {
26-
if(StringUtils.isBlank(column.getName())|| StringUtils.isBlank(column.getColumnType())){
31+
if (StringUtils.isBlank(column.getName()) || StringUtils.isBlank(column.getColumnType())) {
2732
continue;
2833
}
2934
MysqlColumnTypeEnum typeEnum = MysqlColumnTypeEnum.getByType(column.getColumnType());
@@ -32,7 +37,7 @@ public String buildCreateTableSql(Table table) {
3237

3338
// append primary key and index
3439
for (TableIndex tableIndex : table.getIndexList()) {
35-
if(StringUtils.isBlank(tableIndex.getName())|| StringUtils.isBlank(tableIndex.getType())){
40+
if (StringUtils.isBlank(tableIndex.getName()) || StringUtils.isBlank(tableIndex.getType())) {
3641
continue;
3742
}
3843
MysqlIndexTypeEnum mysqlIndexTypeEnum = MysqlIndexTypeEnum.getByType(tableIndex.getType());
@@ -75,7 +80,7 @@ public String buildCreateTableSql(Table table) {
7580
public String buildModifyTaleSql(Table oldTable, Table newTable) {
7681
StringBuilder script = new StringBuilder();
7782
script.append("ALTER TABLE ");
78-
if(StringUtils.isNotBlank(oldTable.getDatabaseName())) {
83+
if (StringUtils.isNotBlank(oldTable.getDatabaseName())) {
7984
script.append("`").append(oldTable.getDatabaseName()).append("`").append(".");
8085
}
8186
script.append("`").append(oldTable.getName()).append("`").append("\n");
@@ -91,7 +96,7 @@ public String buildModifyTaleSql(Table oldTable, Table newTable) {
9196

9297
// append modify column
9398
for (TableColumn tableColumn : newTable.getColumnList()) {
94-
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())) {
95100
MysqlColumnTypeEnum typeEnum = MysqlColumnTypeEnum.getByType(tableColumn.getColumnType());
96101
script.append("\t").append(typeEnum.buildModifyColumn(tableColumn)).append(",\n");
97102
}
@@ -104,7 +109,11 @@ public String buildModifyTaleSql(Table oldTable, Table newTable) {
104109
script.append("\t").append(mysqlIndexTypeEnum.buildModifyIndex(tableIndex)).append(",\n");
105110
}
106111
}
107-
if(script.length()>2) {
112+
113+
// append reorder column
114+
script.append("\t").append(buildGenerateReorderColumnSql(oldTable, newTable));
115+
116+
if (script.length() > 2) {
108117
script = new StringBuilder(script.substring(0, script.length() - 2));
109118
script.append(";");
110119
}
@@ -113,7 +122,6 @@ public String buildModifyTaleSql(Table oldTable, Table newTable) {
113122
}
114123

115124

116-
117125
@Override
118126
public String pageLimit(String sql, int offset, int pageNo, int pageSize) {
119127
StringBuilder sqlBuilder = new StringBuilder(sql.length() + 14);
@@ -131,12 +139,10 @@ public String pageLimit(String sql, int offset, int pageNo, int pageSize) {
131139
}
132140

133141

134-
135-
136142
@Override
137143
public String buildCreateDatabaseSql(Database database) {
138144
StringBuilder sqlBuilder = new StringBuilder();
139-
sqlBuilder.append("CREATE DATABASE `"+database.getName()+"`");
145+
sqlBuilder.append("CREATE DATABASE `" + database.getName() + "`");
140146
if (StringUtils.isNotBlank(database.getCharset())) {
141147
sqlBuilder.append(" DEFAULT CHARACTER SET=").append(database.getCharset());
142148
}
@@ -146,4 +152,142 @@ public String buildCreateDatabaseSql(Database database) {
146152
return sqlBuilder.toString();
147153
}
148154

155+
public String buildGenerateReorderColumnSql(Table oldTable, Table newTable) {
156+
StringBuilder sql = new StringBuilder();
157+
int n = 0;
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();
231+
if (n > 0) {
232+
sql.append("ALTER TABLE ");
233+
if (StringUtils.isNotBlank(oldTable.getDatabaseName())) {
234+
sql.append("`").append(oldTable.getDatabaseName()).append("`").append(".");
235+
}
236+
sql.append("`").append(oldTable.getName()).append("`").append("\n");
237+
}
238+
sql.append(" MODIFY COLUMN ");
239+
MysqlColumnTypeEnum typeEnum = MysqlColumnTypeEnum.getByType(column.getColumnType());
240+
sql.append(typeEnum.buildColumn(column));
241+
sql.append(" ");
242+
sql.append(" AFTER ");
243+
if (i < a) {
244+
sql.append(originalArray[a]);
245+
} else {
246+
sql.append(originalArray[a - 1]);
247+
}
248+
249+
sql.append(";\n");
250+
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;
268+
}
269+
}
270+
return -1;
271+
}
272+
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);
287+
}
288+
newArray[to] = temp;
289+
System.out.println(ArrayUtil.toString(newArray));
290+
return newArray;
291+
}
292+
149293
}

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

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -318,6 +318,18 @@ private String buildDataType(TableColumn column, MysqlColumnTypeEnum type) {
318318
return columnType;
319319
}
320320

321+
public String buildColumn(TableColumn column) {
322+
MysqlColumnTypeEnum type = COLUMN_TYPE_MAP.get(column.getColumnType().toUpperCase());
323+
if (type == null) {
324+
return "";
325+
}
326+
StringBuilder script = new StringBuilder();
327+
328+
script.append("`").append(column.getName()).append("`").append(" ");
329+
script.append(buildDataType(column, type)).append(" ");
330+
return script.toString();
331+
}
332+
321333
private String unsignedDataType(String dataTypeName, String middle) {
322334
String[] split = dataTypeName.split(" ");
323335
if (split.length == 2) {

0 commit comments

Comments
 (0)