Skip to content

Commit 79bbfe6

Browse files
Chat2DB-ProChat2DB-Pro
committed
fix table sort error
1 parent 57667cc commit 79bbfe6

3 files changed

Lines changed: 34 additions & 7 deletions

File tree

‎chat2db-server/chat2db-plugins/chat2db-sqlserver/src/main/java/ai/chat2db/plugin/sqlserver/SqlServerMetaData.java‎

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -91,15 +91,18 @@ public String tableDDL(Connection connection, String databaseName, String schema
9191
});
9292
}
9393

94-
private static String SELECT_TABLES_SQL = "SELECT t.name AS TableName, mm.value as comment FROM sys.tables t LEFT JOIN(SELECT * from sys.extended_properties ep where ep.minor_id = 0 AND ep.name = 'MS_Description') mm ON t.object_id = mm.major_id WHERE t.schema_id= SCHEMA_ID('%S') ORDER by t.name";
94+
private static String SELECT_TABLES_SQL = "SELECT t.name AS TableName, mm.value as comment FROM sys.tables t LEFT JOIN(SELECT * from sys.extended_properties ep where ep.minor_id = 0 AND ep.name = 'MS_Description') mm ON t.object_id = mm.major_id WHERE t.schema_id= SCHEMA_ID('%S')";
9595

9696
@Override
9797
public List<Table> tables(Connection connection, String databaseName, String schemaName, String tableName) {
9898
List<Table> tables = new ArrayList<>();
9999
String sql = String.format(SELECT_TABLES_SQL, schemaName);
100100
if (StringUtils.isNotBlank(tableName)) {
101101
sql += " AND t.name = '" + tableName + "'";
102+
}else {
103+
sql += " ORDER BY t.name";
102104
}
105+
103106
return SQLExecutor.getInstance().execute(connection, sql, resultSet -> {
104107
while (resultSet.next()) {
105108
Table table = new Table();
@@ -153,7 +156,7 @@ public List<TableColumn> columns(Connection connection, String databaseName, Str
153156

154157

155158
private static String OBJECT_SQL
156-
= "SELECT name FROM sys.objects WHERE type = '%s' and SCHEMA_ID = SCHEMA_ID('%s');";
159+
= "SELECT name FROM sys.objects WHERE type = '%s' and SCHEMA_ID = SCHEMA_ID('%s') order by name;";
157160

158161
@Override
159162
public Function function(Connection connection, @NotEmpty String databaseName, String schemaName,
@@ -232,7 +235,7 @@ private Procedure removeVersion(Procedure procedure) {
232235
private static String TRIGGER_SQL_LIST
233236
= "SELECT OBJECT_NAME(parent_obj) AS TableName, name AS triggerName, OBJECT_DEFINITION(id) AS "
234237
+ "triggerDefinition, CASE WHEN status & 1 = 1 THEN 'Enabled' ELSE 'Disabled' END AS Status FROM sysobjects "
235-
+ "WHERE xtype = 'TR' ";
238+
+ "WHERE xtype = 'TR' order by name";
236239

237240
@Override
238241
public List<Trigger> triggers(Connection connection, String databaseName, String schemaName) {

‎chat2db-server/chat2db-spi/src/main/java/ai/chat2db/spi/enums/DataTypeEnum.java‎

Lines changed: 14 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,7 @@
22

33
import ai.chat2db.server.tools.base.enums.BaseEnum;
44
import lombok.Getter;
5+
import org.apache.commons.lang3.StringUtils;
56

67
/**
78
* Driver class enumeration
@@ -108,17 +109,17 @@ public static DataTypeEnum getByCode(String code) {
108109

109110
public String getSqlValue(String value) {
110111
if (this == DataTypeEnum.BOOLEAN) {
111-
if("true".equalsIgnoreCase(value) || "false".equalsIgnoreCase(value)){
112+
if ("true".equalsIgnoreCase(value) || "false".equalsIgnoreCase(value)) {
112113
return value;
113-
}else {
114+
} else {
114115
return "'" + value + "'";
115116
}
116117
}
117118
if (this == DataTypeEnum.NUMERIC) {
118119
return value;
119120
}
120121
if (this == DataTypeEnum.STRING) {
121-
return "'" + value + "'";
122+
return getStringValue(value);
122123
}
123124
if (this == DataTypeEnum.DATETIME) {
124125
return "'" + value + "'";
@@ -155,4 +156,14 @@ public String getSqlValue(String value) {
155156
}
156157
return "'" + value + "'";
157158
}
159+
160+
public static String getStringValue(String value) {
161+
if (StringUtils.isBlank(value)) {
162+
return "'" + value + "'";
163+
}
164+
value = value.replace("\\", "\\\\");
165+
value = value.replace("'", "\\'");
166+
return "'" + value + "'";
167+
}
168+
158169
}

‎chat2db-server/chat2db-spi/src/main/java/ai/chat2db/spi/util/SqlUtils.java‎

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -124,7 +124,7 @@ public static List<String> parse(String sql, DbType dbType) {
124124
// Iterate through each statement
125125
for (Statement stmt : statements.getStatements()) {
126126
if (!(stmt instanceof CreateProcedure)) {
127-
list.add(stmt.toString());
127+
list.add(updateNow(stmt.toString(),dbType));
128128
}
129129
}
130130
if (CollectionUtils.isEmpty(list)) {
@@ -136,6 +136,19 @@ public static List<String> parse(String sql, DbType dbType) {
136136
return list;
137137
}
138138

139+
private static String updateNow(String sql,DbType dbType) {
140+
if(StringUtils.isBlank(sql) || !DbType.mysql.equals(dbType)){
141+
return sql;
142+
}
143+
if(sql.contains("default now ()")){
144+
return sql.replace("default now ()","default CURRENT_TIMESTAMP");
145+
}
146+
if(sql.contains("DEFAULT now ()")){
147+
return sql.replace("DEFAULT now ()","DEFAULT CURRENT_TIMESTAMP");
148+
}
149+
return sql;
150+
}
151+
139152
private static final String DEFAULT_VALUE = "CHAT2DB_UPDATE_TABLE_DATA_USER_FILLED_DEFAULT";
140153

141154
public static String getSqlValue(String value, String dataType) {

0 commit comments

Comments
 (0)