in fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/DatatableWriteServiceImpl.java [326:521]
public void updateDatatable(final String datatableName, final JsonCommand command) {
try {
this.context.authenticatedUser();
this.fromApiJsonDeserializer.validateForUpdate(command.json());
final JsonElement element = this.fromJsonHelper.parse(command.json());
final JsonArray changeColumns = this.fromJsonHelper.extractJsonArrayNamed(API_PARAM_CHANGECOLUMNS, element);
final JsonArray addColumns = this.fromJsonHelper.extractJsonArrayNamed(API_PARAM_ADDCOLUMNS, element);
final JsonArray dropColumns = this.fromJsonHelper.extractJsonArrayNamed(API_PARAM_DROPCOLUMNS, element);
final String entityName = this.fromJsonHelper.extractStringNamed(API_PARAM_APPTABLE_NAME, element);
final String entitySubType = this.fromJsonHelper.extractStringNamed(API_PARAM_SUBTYPE, element);
datatableUtil.validateDatatableName(datatableName);
int rowCount = getDatatableRowCount(datatableName);
final List<ResultsetColumnHeaderData> columnHeaderData = this.genericDataService.fillResultsetColumnHeaders(datatableName);
final Map<String, ResultsetColumnHeaderData> mapColumnNameDefinition = searchUtil.mapHeadersToName(columnHeaderData);
final boolean isConstraintApproach = this.configurationDomainService.isConstraintApproachEnabledForDatatables();
if (!StringUtils.isBlank(entitySubType)) {
jdbcTemplate.update("update x_registered_table SET entity_subtype=? WHERE registered_table_name = ?", // NOSONAR
new Object[] { entitySubType, datatableName });
}
if (!StringUtils.isBlank(entityName)) {
EntityTables entityTable = datatableUtil.resolveEntity(entityName);
EntityTables oldEntityTable = datatableUtil.queryForApplicationEntity(datatableName);
if (entityTable != oldEntityTable) {
final String oldFKName = datatableUtil.getFKField(oldEntityTable);
final String newFKName = datatableUtil.getFKField(entityTable);
final String oldConstraintName = datatableName.toLowerCase().replaceAll("\\s", "_") + "_" + oldFKName;
final String newConstraintName = datatableName.toLowerCase().replaceAll("\\s", "_") + "_" + newFKName;
StringBuilder sqlBuilder = new StringBuilder();
String fullOldFk = "fk_" + oldFKName;
String fullOldConstraint = "fk_" + oldConstraintName;
String fullNewFk = "fk_" + newFKName;
String fullNewConstraint = "fk_" + newConstraintName;
if (mapColumnNameDefinition.containsKey(TABLE_FIELD_ID)) {
sqlBuilder.append(ALTER_TABLE).append(sqlGenerator.escape(datatableName)).append(" DROP KEY ")
.append(sqlGenerator.escape(fullOldFk)).append(",").append("DROP FOREIGN KEY ")
.append(sqlGenerator.escape(fullOldConstraint)).append(",").append("CHANGE COLUMN ")
.append(sqlGenerator.escape(oldFKName)).append(" ").append(sqlGenerator.escape(newFKName))
.append(" BIGINT NOT NULL,").append("ADD KEY ").append(sqlGenerator.escape(fullNewFk)).append(" (")
.append(sqlGenerator.escape(newFKName)).append("),").append("ADD CONSTRAINT ")
.append(sqlGenerator.escape(fullNewConstraint)).append(FOREIGN_KEY_CLAUSE)
.append(sqlGenerator.escape(newFKName)).append(") ").append(REFERENCES_CLAUSE)
.append(sqlGenerator.escape(entityTable.getApptableName())).append(" (").append(TABLE_FIELD_ID).append(")");
} else {
sqlBuilder.append(ALTER_TABLE).append(sqlGenerator.escape(datatableName)).append(" DROP FOREIGN KEY ")
.append(sqlGenerator.escape(fullOldConstraint)).append(",").append("CHANGE COLUMN ")
.append(sqlGenerator.escape(oldFKName)).append(" ").append(sqlGenerator.escape(newFKName))
.append(" BIGINT NOT NULL,").append("ADD CONSTRAINT ").append(sqlGenerator.escape(fullNewConstraint))
.append(FOREIGN_KEY_CLAUSE).append(sqlGenerator.escape(newFKName)).append(") ").append(REFERENCES_CLAUSE)
.append(sqlGenerator.escape(entityTable.getApptableName())).append(" (").append(TABLE_FIELD_ID).append(")");
}
this.jdbcTemplate.execute(sqlBuilder.toString());
deregisterDatatable(datatableName);
registerDatatable(datatableName, entityName, entitySubType);
}
}
if (changeColumns == null && addColumns == null && dropColumns == null) {
return;
}
if (dropColumns != null) {
if (rowCount > 0) {
throw new GeneralPlatformDomainRuleException("error.msg.non.empty.datatable.column.cannot.be.deleted",
"Non-empty datatable columns can not be deleted.");
}
StringBuilder sqlBuilder = new StringBuilder(ALTER_TABLE + sqlGenerator.escape(datatableName));
final StringBuilder constrainBuilder = new StringBuilder();
final List<String> codeMappings = new ArrayList<>();
for (final JsonElement column : dropColumns) {
parseDatatableColumnForDrop(column.getAsJsonObject(), sqlBuilder, datatableName, constrainBuilder, codeMappings);
}
// Remove the first comma, right after ALTER TABLE datatable
final int indexOfFirstComma = sqlBuilder.indexOf(",");
if (indexOfFirstComma != -1) {
sqlBuilder.deleteCharAt(indexOfFirstComma);
}
sqlBuilder.append(constrainBuilder);
this.jdbcTemplate.execute(sqlBuilder.toString());
deleteColumnCodeMapping(codeMappings);
}
if (addColumns != null) {
StringBuilder sqlBuilder = new StringBuilder(ALTER_TABLE + sqlGenerator.escape(datatableName));
final StringBuilder constrainBuilder = new StringBuilder();
final Map<String, Long> codeMappings = new HashMap<>();
for (final JsonElement column : addColumns) {
JsonObject columnAsJson = column.getAsJsonObject();
if (rowCount > 0 && columnAsJson.has(API_FIELD_MANDATORY) && columnAsJson.get(API_FIELD_MANDATORY).getAsBoolean()) {
throw new GeneralPlatformDomainRuleException("error.msg.non.empty.datatable.mandatory.column.cannot.be.added",
"Non empty datatable mandatory columns can not be added.");
}
parseDatatableColumnForAdd(columnAsJson, sqlBuilder, datatableName.toLowerCase().replaceAll("\\s", "_"),
constrainBuilder, codeMappings, isConstraintApproach);
}
// Remove the first comma, right after ALTER TABLE datatable
final int indexOfFirstComma = sqlBuilder.indexOf(",");
if (indexOfFirstComma != -1) {
sqlBuilder.deleteCharAt(indexOfFirstComma);
}
sqlBuilder.append(constrainBuilder);
jdbcTemplate.execute(sqlBuilder.toString());
createIndexesForTable(datatableName, addColumns);
registerColumnCodeMapping(codeMappings);
}
if (changeColumns != null) {
final StringBuilder renameBuilder = new StringBuilder();
StringBuilder changeBuilder = new StringBuilder();
final StringBuilder constrainBuilder = new StringBuilder();
final Map<String, Long> codeMappings = new HashMap<>();
final List<String> removeMappings = new ArrayList<>();
for (final JsonElement column : changeColumns) {
// remove NULL values from column where mandatory is true
removeNullValuesFromStringColumn(datatableName, column.getAsJsonObject(), mapColumnNameDefinition);
parseDatatableColumnForUpdate(column.getAsJsonObject(), mapColumnNameDefinition, datatableName, renameBuilder,
changeBuilder, constrainBuilder, codeMappings, removeMappings, isConstraintApproach);
}
// Remove the first comma, right after ALTER TABLE datatable
StringBuilder sqlBuilder = renameBuilder;
if (!changeBuilder.isEmpty() || !constrainBuilder.isEmpty()) {
int idx = changeBuilder.indexOf(",");
if (idx > -1) {
changeBuilder.deleteCharAt(idx);
} else if ((idx = constrainBuilder.indexOf(",")) > -1) {
constrainBuilder.deleteCharAt(idx);
}
sqlBuilder.append(ALTER_TABLE + sqlGenerator.escape(datatableName)).append(changeBuilder).append(constrainBuilder);
}
try {
if (!sqlBuilder.isEmpty()) {
jdbcTemplate.execute(sqlBuilder.toString());
}
deleteColumnCodeMapping(removeMappings);
registerColumnCodeMapping(codeMappings);
// update unique constraint
updateUniqueConstraintsForTable(datatableName, changeColumns, mapColumnNameDefinition);
// update indexes
updateIndexesForTable(datatableName, changeColumns, mapColumnNameDefinition);
} catch (final Exception e) {
log.error("Exception while modifying a datatable", e);
if (e.getMessage().contains("Error on rename")) {
throw new PlatformServiceUnavailableException("error.msg.datatable.column.update.not.allowed",
"One of the column name modification not allowed", e);
}
// handle all other exceptions in here
// check if exception message contains the
// "invalid use of null value" SQL exception message
// throw a 503 HTTP error -
// PlatformServiceUnavailableException
if (e.getMessage().toLowerCase().contains("invalid use of null value")) {
throw new PlatformServiceUnavailableException("error.msg.datatable.column.update.not.allowed",
"One of the data table columns contains null values", e);
}
}
}
} catch (final JpaSystemException | DataIntegrityViolationException e) {
final Throwable realCause = e.getCause();
final List<ApiParameterError> dataValidationErrors = new ArrayList<>();
final DataValidatorBuilder baseDataValidator = new DataValidatorBuilder(dataValidationErrors).resource(RESOURCE_DATATABLE);
if (realCause.getMessage().toLowerCase().contains("unknown column")) {
baseDataValidator.reset().parameter(API_FIELD_NAME).failWithCode(DOES_NOT_EXIST);
} else if (realCause.getMessage().toLowerCase().contains("can't drop")) {
baseDataValidator.reset().parameter(API_FIELD_NAME).failWithCode(DOES_NOT_EXIST);
} else if (realCause.getMessage().toLowerCase().contains("duplicate column")) {
baseDataValidator.reset().parameter(API_FIELD_NAME).failWithCode("column.already.exists");
}
baseDataValidator.throwValidationErrors();
} catch (final PersistenceException ee) {
Throwable realCause = ExceptionUtils.getRootCause(ee.getCause());
final List<ApiParameterError> dataValidationErrors = new ArrayList<>();
final DataValidatorBuilder baseDataValidator = new DataValidatorBuilder(dataValidationErrors).resource(RESOURCE_DATATABLE);
if (realCause.getMessage().toLowerCase().contains("duplicate column name")) {
baseDataValidator.reset().parameter(API_FIELD_NAME).failWithCode("duplicate.column.name");
} else if ((realCause.getMessage().contains("Table") || realCause.getMessage().contains("relation"))
&& realCause.getMessage().contains("already exists")) {
baseDataValidator.reset().parameter(API_PARAM_DATATABLE_NAME).value(datatableName).failWithCode("datatable.already.exists");
} else if (realCause.getMessage().contains("Column") && realCause.getMessage().contains("big")) {
baseDataValidator.reset().parameter("column").failWithCode("length.too.big");
} else if (realCause.getMessage().contains("Row") && realCause.getMessage().contains("large")) {
baseDataValidator.reset().parameter("row").failWithCode("size.too.large");
}
baseDataValidator.throwValidationErrors();
}
}