Skip to content

Commit f6079a6

Browse files
neon-balconyneon-balcony
authored
Merge pull request lowcoder-org#244 from neon-balcony/develop
implement GUI mode for SQL server
2 parents e11e1f1 + 693de47 commit f6079a6

File tree

16 files changed

+410
-244
lines changed

16 files changed

+410
-244
lines changed

‎server/api-service/openblocks-plugins/mssqlPlugin/pom.xml

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -58,6 +58,11 @@
5858
</exclusion>
5959
</exclusions>
6060
</dependency>
61+
<dependency>
62+
<groupId>org.assertj</groupId>
63+
<artifactId>assertj-core</artifactId>
64+
<scope>test</scope>
65+
</dependency>
6166

6267
</dependencies>
6368

‎server/api-service/openblocks-plugins/mssqlPlugin/src/main/java/com/openblocks/plugin/mssql/MssqlQueryExecutor.java

Lines changed: 159 additions & 79 deletions
Large diffs are not rendered by default.

‎server/api-service/openblocks-plugins/mssqlPlugin/src/main/java/com/openblocks/plugin/mssql/gui/GuiConstants.java

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,8 @@
1+
package com.openblocks.plugin.mssql.gui;
2+
3+
public final class GuiConstants {
4+
5+
public static final String MSSQL_COLUMN_DELIMITER_FRONT = "[";
6+
public static final String MSSQL_COLUMN_DELIMITER_BACK = "]";
7+
8+
}

‎server/api-service/openblocks-plugins/mssqlPlugin/src/main/java/com/openblocks/plugin/mssql/gui/MssqlBulkInsertCommand.java

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
1+
package com.openblocks.plugin.mssql.gui;
2+
3+
import static com.openblocks.plugin.mssql.gui.GuiConstants.MSSQL_COLUMN_DELIMITER_BACK;
4+
import static com.openblocks.plugin.mssql.gui.GuiConstants.MSSQL_COLUMN_DELIMITER_FRONT;
5+
import static com.openblocks.sdk.plugin.sqlcommand.changeset.BulkObjectChangeSet.parseBulkRecords;
6+
7+
import java.util.Map;
8+
9+
import com.openblocks.sdk.plugin.sqlcommand.GuiSqlCommand;
10+
import com.openblocks.sdk.plugin.sqlcommand.changeset.BulkObjectChangeSet;
11+
import com.openblocks.sdk.plugin.sqlcommand.command.BulkInsertCommand;
12+
13+
public class MssqlBulkInsertCommand extends BulkInsertCommand {
14+
protected MssqlBulkInsertCommand(String table, BulkObjectChangeSet bulkObjectChangeSet) {
15+
super(table, bulkObjectChangeSet, MSSQL_COLUMN_DELIMITER_FRONT, MSSQL_COLUMN_DELIMITER_BACK);
16+
}
17+
18+
public static BulkInsertCommand from(Map<String, Object> commandDetail) {
19+
String table = GuiSqlCommand.parseTable(commandDetail);
20+
String recordStr = parseBulkRecords(commandDetail);
21+
BulkObjectChangeSet bulkObjectChangeSet = new BulkObjectChangeSet(recordStr);
22+
return new MssqlBulkInsertCommand(table, bulkObjectChangeSet);
23+
}
24+
}

‎server/api-service/openblocks-plugins/mssqlPlugin/src/main/java/com/openblocks/plugin/mssql/gui/MssqlBulkUpdateCommand.java

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,27 @@
1+
package com.openblocks.plugin.mssql.gui;
2+
3+
import static com.openblocks.plugin.mssql.gui.GuiConstants.MSSQL_COLUMN_DELIMITER_BACK;
4+
import static com.openblocks.plugin.mssql.gui.GuiConstants.MSSQL_COLUMN_DELIMITER_FRONT;
5+
import static com.openblocks.sdk.plugin.sqlcommand.GuiSqlCommand.parseTable;
6+
import static com.openblocks.sdk.plugin.sqlcommand.changeset.BulkObjectChangeSet.parseBulkRecords;
7+
import static com.openblocks.sdk.plugin.sqlcommand.changeset.BulkObjectChangeSet.parsePrimaryKey;
8+
9+
import java.util.Map;
10+
11+
import com.openblocks.sdk.plugin.sqlcommand.changeset.BulkObjectChangeSet;
12+
import com.openblocks.sdk.plugin.sqlcommand.command.BulkUpdateCommand;
13+
14+
public class MssqlBulkUpdateCommand extends BulkUpdateCommand {
15+
16+
protected MssqlBulkUpdateCommand(String table, BulkObjectChangeSet bulkObjectChangeSet, String primaryKey) {
17+
super(table, bulkObjectChangeSet, primaryKey, MSSQL_COLUMN_DELIMITER_FRONT, MSSQL_COLUMN_DELIMITER_BACK);
18+
}
19+
20+
public static MssqlBulkUpdateCommand from(Map<String, Object> commandDetail) {
21+
String table = parseTable(commandDetail);
22+
String recordStr = parseBulkRecords(commandDetail);
23+
BulkObjectChangeSet bulkObjectChangeSet = new BulkObjectChangeSet(recordStr);
24+
return new MssqlBulkUpdateCommand(table, bulkObjectChangeSet, parsePrimaryKey(commandDetail));
25+
}
26+
27+
}

‎server/api-service/openblocks-plugins/mssqlPlugin/src/main/java/com/openblocks/plugin/mssql/gui/MssqlDeleteCommand.java

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,44 @@
1+
package com.openblocks.plugin.mssql.gui;
2+
3+
import static com.openblocks.plugin.mssql.gui.GuiConstants.MSSQL_COLUMN_DELIMITER_BACK;
4+
import static com.openblocks.plugin.mssql.gui.GuiConstants.MSSQL_COLUMN_DELIMITER_FRONT;
5+
import static com.openblocks.sdk.plugin.sqlcommand.filter.FilterSet.parseFilterSet;
6+
7+
import java.util.Map;
8+
9+
import com.openblocks.sdk.plugin.sqlcommand.GuiSqlCommand;
10+
import com.openblocks.sdk.plugin.sqlcommand.command.DeleteCommand;
11+
import com.openblocks.sdk.plugin.sqlcommand.filter.FilterSet;
12+
13+
public class MssqlDeleteCommand extends DeleteCommand {
14+
15+
protected MssqlDeleteCommand(String table, FilterSet filterSet, boolean allowMultiModify) {
16+
super(table, filterSet, allowMultiModify, MSSQL_COLUMN_DELIMITER_FRONT, MSSQL_COLUMN_DELIMITER_BACK);
17+
}
18+
19+
public static DeleteCommand from(Map<String, Object> commandDetail) {
20+
String table = GuiSqlCommand.parseTable(commandDetail);
21+
FilterSet filterSet = parseFilterSet(commandDetail);
22+
boolean allowMultiModify = GuiSqlCommand.parseAllowMultiModify(commandDetail);
23+
return new MssqlDeleteCommand(table, filterSet, allowMultiModify);
24+
}
25+
26+
@Override
27+
public GuiSqlCommandRenderResult render(Map<String, Object> requestMap) {
28+
return super.render(requestMap);
29+
}
30+
31+
@Override
32+
protected void renderTable(String renderedTable, StringBuilder sb) {
33+
sb.append("delete ");
34+
if (!allowMultiModify) {
35+
sb.append("top (1) ");
36+
}
37+
sb.append("from ").append(renderedTable);
38+
}
39+
40+
@Override
41+
protected void renderLimit(StringBuilder sb) {
42+
// do nothing
43+
}
44+
}

‎server/api-service/openblocks-sdk/src/main/java/com/openblocks/sdk/plugin/sqlcommand/command/mssql/MssqlInsertCommand.java renamed to ‎server/api-service/openblocks-plugins/mssqlPlugin/src/main/java/com/openblocks/plugin/mssql/gui/MssqlInsertCommand.java

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,7 @@
1-
package com.openblocks.sdk.plugin.sqlcommand.command.mssql;
1+
package com.openblocks.plugin.mssql.gui;
2+
3+
import static com.openblocks.plugin.mssql.gui.GuiConstants.MSSQL_COLUMN_DELIMITER_BACK;
4+
import static com.openblocks.plugin.mssql.gui.GuiConstants.MSSQL_COLUMN_DELIMITER_FRONT;
25

36
import java.util.Map;
47

@@ -8,9 +11,6 @@
811

912
public class MssqlInsertCommand extends InsertCommand {
1013

11-
private static final String MSSQL_COLUMN_DELIMITER_FRONT = "[";
12-
private static final String MSSQL_COLUMN_DELIMITER_BACK = "]";
13-
1414
private MssqlInsertCommand(Map<String, Object> commandDetail) {
1515
super(commandDetail, MSSQL_COLUMN_DELIMITER_FRONT, MSSQL_COLUMN_DELIMITER_BACK);
1616
}

‎server/api-service/openblocks-plugins/mssqlPlugin/src/main/java/com/openblocks/plugin/mssql/gui/MssqlUpdateCommand.java

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,41 @@
1+
package com.openblocks.plugin.mssql.gui;
2+
3+
import static com.openblocks.plugin.mssql.gui.GuiConstants.MSSQL_COLUMN_DELIMITER_BACK;
4+
import static com.openblocks.plugin.mssql.gui.GuiConstants.MSSQL_COLUMN_DELIMITER_FRONT;
5+
6+
import java.util.Map;
7+
8+
import com.google.common.annotations.VisibleForTesting;
9+
import com.openblocks.sdk.plugin.sqlcommand.changeset.ChangeSet;
10+
import com.openblocks.sdk.plugin.sqlcommand.command.UpdateCommand;
11+
import com.openblocks.sdk.plugin.sqlcommand.filter.FilterSet;
12+
13+
public class MssqlUpdateCommand extends UpdateCommand {
14+
15+
private MssqlUpdateCommand(Map<String, Object> commandDetail) {
16+
super(commandDetail, MSSQL_COLUMN_DELIMITER_FRONT, MSSQL_COLUMN_DELIMITER_BACK);
17+
}
18+
19+
@VisibleForTesting
20+
protected MssqlUpdateCommand(String table, ChangeSet changeSet, FilterSet filterSet, boolean allowMultiModify) {
21+
super(table, changeSet, filterSet, allowMultiModify, MSSQL_COLUMN_DELIMITER_FRONT, MSSQL_COLUMN_DELIMITER_BACK);
22+
}
23+
24+
@Override
25+
protected void appendTable(String renderedTable, StringBuilder sb) {
26+
sb.append("update ");
27+
if (!allowMultiModify) {
28+
sb.append(" top (1) ");
29+
}
30+
sb.append(renderedTable);
31+
}
32+
33+
@Override
34+
protected void appendLimit(StringBuilder sb) {
35+
// do nothing
36+
}
37+
38+
public static MssqlUpdateCommand from(Map<String, Object> commandDetail) {
39+
return new MssqlUpdateCommand(commandDetail);
40+
}
41+
}

‎server/api-service/openblocks-plugins/mssqlPlugin/src/main/java/com/openblocks/plugin/mssql/model/MssqlQueryConfig.java

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,7 @@
99
import org.apache.commons.collections4.MapUtils;
1010

1111
import com.fasterxml.jackson.annotation.JsonCreator;
12+
import com.fasterxml.jackson.annotation.JsonProperty;
1213
import com.openblocks.sdk.exception.PluginException;
1314

1415
import lombok.Getter;
@@ -17,11 +18,20 @@
1718
public class MssqlQueryConfig {
1819
private final String sql;
1920
private final boolean disablePreparedStatement;
21+
private final String mode;
22+
private final String guiStatementType;
23+
private final Map<String, Object> guiStatementDetail;
2024

2125
@JsonCreator
22-
private MssqlQueryConfig(String sql, boolean disablePreparedStatement) {
26+
private MssqlQueryConfig(String sql, boolean disablePreparedStatement,
27+
String mode,
28+
@JsonProperty("commandType") String guiStatementType,
29+
@JsonProperty("command") Map<String, Object> guiStatementDetail) {
2330
this.sql = sql;
2431
this.disablePreparedStatement = disablePreparedStatement;
32+
this.mode = mode;
33+
this.guiStatementType = guiStatementType;
34+
this.guiStatementDetail = guiStatementDetail;
2535
}
2636

2737
public static MssqlQueryConfig from(Map<String, Object> queryConfigs) {
@@ -41,4 +51,8 @@ public String getSql() {
4151
return sql.trim();
4252
}
4353

54+
public boolean isGuiMode() {
55+
return "GUI".equalsIgnoreCase(mode);
56+
}
57+
4458
}

‎server/api-service/openblocks-plugins/mysqlPlugin/src/main/java/com/openblocks/plugin/mysql/MysqlQueryExecutor.java

Lines changed: 7 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -11,6 +11,7 @@
1111
import static com.openblocks.sdk.exception.PluginCommonError.QUERY_EXECUTION_ERROR;
1212
import static com.openblocks.sdk.plugin.common.QueryExecutionUtils.getIdenticalColumns;
1313
import static com.openblocks.sdk.plugin.common.SqlQueryUtils.isInsertQuery;
14+
import static com.openblocks.sdk.util.ExceptionUtils.wrapException;
1415
import static com.openblocks.sdk.util.JsonUtils.toJson;
1516
import static com.openblocks.sdk.util.MustacheHelper.doPrepareStatement;
1617
import static com.openblocks.sdk.util.MustacheHelper.extractMustacheKeysInOrder;
@@ -36,6 +37,7 @@
3637

3738
import javax.annotation.Nonnull;
3839

40+
import org.apache.commons.collections4.CollectionUtils;
3941
import org.apache.commons.collections4.MapUtils;
4042
import org.apache.commons.lang3.StringUtils;
4143
import org.pf4j.Extension;
@@ -243,10 +245,7 @@ private void bindPreparedStatementForGuiMode(PreparedStatement preparedQuery, Li
243245
bindParam(index + 1, value, preparedQuery, "");
244246
}
245247
} catch (Exception e) {
246-
if (e instanceof PluginException pluginException) {
247-
throw pluginException;
248-
}
249-
throw new PluginException(PREPARED_STATEMENT_BIND_PARAMETERS_ERROR, "PREPARED_STATEMENT_BIND_PARAMETERS_ERROR", e.getMessage());
248+
throw wrapException(PREPARED_STATEMENT_BIND_PARAMETERS_ERROR, "PREPARED_STATEMENT_BIND_PARAMETERS_ERROR", e);
250249
}
251250
}
252251

@@ -298,7 +297,7 @@ private List<Long> getGeneratedIds(ResultSet generatedKeys) throws SQLException
298297
private List<LocaleMessage> populateHintMessages(List<String> columnNames) {
299298
List<LocaleMessage> messages = new ArrayList<>();
300299
List<String> identicalColumns = getIdenticalColumns(columnNames);
301-
if (!org.springframework.util.CollectionUtils.isEmpty(identicalColumns)) {
300+
if (!CollectionUtils.isEmpty(identicalColumns)) {
302301
messages.add(new LocaleMessage("DUPLICATE_COLUMN", String.join("/", identicalColumns)));
303302
}
304303
return messages;
@@ -316,10 +315,7 @@ private void bindPreparedStatementParamsWithMustacheKeyValues(PreparedStatement
316315
bindParam(index + 1, value, preparedStatement, mustacheKey);
317316
}
318317
} catch (Exception e) {
319-
if (e instanceof PluginException pluginException) {
320-
throw pluginException;
321-
}
322-
throw new PluginException(PREPARED_STATEMENT_BIND_PARAMETERS_ERROR, "PREPARED_STATEMENT_BIND_PARAMETERS_ERROR", e.getMessage());
318+
throw wrapException(PREPARED_STATEMENT_BIND_PARAMETERS_ERROR, "PREPARED_STATEMENT_BIND_PARAMETERS_ERROR", e);
323319
}
324320
}
325321

@@ -353,7 +349,8 @@ private void bindParam(int bindIndex, Object value, PreparedStatement preparedSt
353349
return;
354350
}
355351
throw new PluginException(PREPARED_STATEMENT_BIND_PARAMETERS_ERROR, "PS_BIND_ERROR",
356-
bindKeyName, value.getClass().getSimpleName());
352+
StringUtils.isBlank(bindKeyName) ? String.valueOf(value) : bindKeyName,
353+
value.getClass().getSimpleName());
357354
}
358355

359356
private void releaseResources(AutoCloseable... autoCloseables) {

‎server/api-service/openblocks-plugins/postgresPlugin/src/main/java/com/openblocks/plugin/postgres/PostgresExecutor.java

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -10,6 +10,7 @@
1010
import static com.openblocks.sdk.plugin.common.QueryExecutionUtils.getIdenticalColumns;
1111
import static com.openblocks.sdk.util.JsonUtils.fromJsonList;
1212
import static com.openblocks.sdk.util.JsonUtils.toJson;
13+
import static com.openblocks.sdk.util.MustacheHelper.doPrepareStatement;
1314
import static com.openblocks.sdk.util.MustacheHelper.renderMustacheString;
1415

1516
import java.io.IOException;
@@ -147,7 +148,7 @@ private QueryExecutionResult executeCommon(HikariDataSource hikariDataSource,
147148
bindPreparedStatementForGuiMode(preparedQuery, renderResult.bindParams());
148149
} else {
149150
List<String> mustacheValuesInOrder = MustacheHelper.extractMustacheKeysInOrder(rawQuery);
150-
String updatedQuery = MustacheHelper.replaceMustacheWithQuestionMark(rawQuery, mustacheValuesInOrder);
151+
var updatedQuery = doPrepareStatement(rawQuery, mustacheValuesInOrder, requestParams);
151152
List<DataType> explicitCastDataTypes = PostgresDataTypeUtils.extractExplicitCasting(updatedQuery);
152153

153154
preparedQuery = connection.prepareStatement(updatedQuery);
@@ -297,7 +298,8 @@ private void bindParam(int bindIndex, Object value, PreparedStatement preparedSt
297298
return;
298299
}
299300
throw new PluginException(PREPARED_STATEMENT_BIND_PARAMETERS_ERROR, "PS_BIND_ERROR",
300-
bindKeyName, value.getClass().getSimpleName());
301+
StringUtils.isBlank(bindKeyName) ? String.valueOf(value) : bindKeyName,
302+
value.getClass().getSimpleName());
301303
}
302304

303305
private void releaseResources(Connection connectionFromPool, Statement statement,
@@ -339,7 +341,7 @@ private void releaseResources(Connection connectionFromPool, Statement statement
339341
private List<LocaleMessage> populateHintMessages(List<String> columnNames) {
340342
List<LocaleMessage> messages = new ArrayList<>();
341343
List<String> identicalColumns = getIdenticalColumns(columnNames);
342-
if (!org.springframework.util.CollectionUtils.isEmpty(identicalColumns)) {
344+
if (!CollectionUtils.isEmpty(identicalColumns)) {
343345
messages.add(new LocaleMessage("DUPLICATE_COLUMN", String.join("/", identicalColumns)));
344346
}
345347
return messages;

‎server/api-service/openblocks-sdk/src/main/java/com/openblocks/sdk/plugin/common/QueryExecutionUtils.java

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
import java.util.function.Function;
99
import java.util.stream.Collectors;
1010

11-
import org.springframework.util.CollectionUtils;
11+
import org.apache.commons.collections4.MapUtils;
1212

1313
import reactor.core.scheduler.Scheduler;
1414
import reactor.core.scheduler.Schedulers;
@@ -57,7 +57,7 @@ public static String getStringValueSafelyFromFormData(Map<String, Object> formDa
5757

5858
@SuppressWarnings("unchecked")
5959
public static Object getValueSafelyFromFormData(Map<String, Object> formData, String field) {
60-
if (CollectionUtils.isEmpty(formData)) {
60+
if (MapUtils.isEmpty(formData)) {
6161
return null;
6262
}
6363

‎server/api-service/openblocks-sdk/src/main/java/com/openblocks/sdk/plugin/sqlcommand/command/DeleteCommand.java

Lines changed: 11 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -31,21 +31,27 @@ public GuiSqlCommandRenderResult render(Map<String, Object> requestMap) {
3131
String renderedTable = MustacheHelper.renderMustacheString(table, requestMap);
3232

3333
StringBuilder sb = new StringBuilder();
34-
sb.append("delete from ").append(renderedTable);
34+
renderTable(renderedTable, sb);
3535
if (filterSet.isEmpty()) {
36-
if (!allowMultiModify) {
37-
sb.append(" limit 1");
38-
}
36+
renderLimit(sb);
3937
return new GuiSqlCommandRenderResult(sb.toString(), Collections.emptyList());
4038
}
4139

4240
GuiSqlCommandRenderResult render = filterSet.render(requestMap, columnFrontDelimiter, columnBackDelimiter, isRenderWithRawSql(),
4341
escapeStrFunc());
4442
sb.append(render.sql());
43+
renderLimit(sb);
44+
return new GuiSqlCommandRenderResult(sb.toString(), render.bindParams());
45+
}
46+
47+
protected void renderTable(String renderedTable, StringBuilder sb) {
48+
sb.append("delete from ").append(renderedTable);
49+
}
50+
51+
protected void renderLimit(StringBuilder sb) {
4552
if (!allowMultiModify) {
4653
sb.append(" limit 1");
4754
}
48-
return new GuiSqlCommandRenderResult(sb.toString(), render.bindParams());
4955
}
5056

5157
@Override

0 commit comments

Comments
 (0)