in fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImpl.java [1117:1315]
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("changeColumns", element);
final JsonArray addColumns = this.fromJsonHelper.extractJsonArrayNamed("addColumns", element);
final JsonArray dropColumns = this.fromJsonHelper.extractJsonArrayNamed("dropColumns", element);
final String apptableName = this.fromJsonHelper.extractStringNamed("apptableName", element);
final String entitySubType = this.fromJsonHelper.extractStringNamed("entitySubType", element);
validateDatatableName(datatableName);
int rowCount = getRowCount(datatableName);
final List<ResultsetColumnHeaderData> columnHeaderData = this.genericDataService.fillResultsetColumnHeaders(datatableName);
final Map<String, ResultsetColumnHeaderData> mapColumnNameDefinition = new HashMap<>();
for (final ResultsetColumnHeaderData columnHeader : columnHeaderData) {
mapColumnNameDefinition.put(columnHeader.getColumnName(), columnHeader);
}
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(apptableName)) {
validateAppTable(apptableName);
final String oldApptableName = queryForApplicationTableName(datatableName);
if (!StringUtils.equals(oldApptableName, apptableName)) {
final String oldFKName = oldApptableName.substring(2) + "_id";
final String newFKName = apptableName.substring(2) + "_id";
final String actualAppTableName = mapToActualAppTable(apptableName);
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("id")) {
sqlBuilder = sqlBuilder.append("ALTER TABLE " + sqlGenerator.escape(datatableName) + " ")
.append("DROP KEY " + sqlGenerator.escape(fullOldFk) + ",")
.append("DROP FOREIGN KEY " + sqlGenerator.escape(fullOldConstraint) + ",")
.append("CHANGE COLUMN " + sqlGenerator.escape(oldFKName) + " " + sqlGenerator.escape(newFKName)
+ " BIGINT NOT NULL,")
.append("ADD KEY " + sqlGenerator.escape(fullNewFk) + " (" + sqlGenerator.escape(newFKName) + "),")
.append("ADD CONSTRAINT " + sqlGenerator.escape(fullNewConstraint) + " ")
.append("FOREIGN KEY (" + sqlGenerator.escape(newFKName) + ") ")
.append("REFERENCES " + sqlGenerator.escape(actualAppTableName) + " (id)");
} else {
sqlBuilder = sqlBuilder.append("ALTER TABLE " + sqlGenerator.escape(datatableName) + " ")
.append("DROP FOREIGN KEY " + sqlGenerator.escape(fullOldConstraint) + ",")
.append("CHANGE COLUMN " + sqlGenerator.escape(oldFKName) + " " + sqlGenerator.escape(newFKName)
+ " BIGINT NOT NULL,")
.append("ADD CONSTRAINT " + sqlGenerator.escape(fullNewConstraint) + " ")
.append("FOREIGN KEY (" + sqlGenerator.escape(newFKName) + ") ")
.append("REFERENCES " + sqlGenerator.escape(actualAppTableName) + " (id)");
}
this.jdbcTemplate.execute(sqlBuilder.toString());
deregisterDatatable(datatableName);
registerDatatable(datatableName, apptableName, 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 = 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("mandatory") && columnAsJson.get("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 = sqlBuilder.deleteCharAt(indexOfFirstComma);
}
sqlBuilder.append(constrainBuilder);
jdbcTemplate.execute(sqlBuilder.toString());
createIndexesForTable(datatableName, addColumns);
registerColumnCodeMapping(codeMappings);
}
if (changeColumns != null) {
StringBuilder sqlBuilder = new StringBuilder("ALTER TABLE " + sqlGenerator.escape(datatableName));
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, sqlBuilder, datatableName,
constrainBuilder, codeMappings, removeMappings, isConstraintApproach);
}
// Remove the first comma, right after ALTER TABLE datatable
final int indexOfFirstComma = sqlBuilder.indexOf(",");
if (indexOfFirstComma != -1) {
sqlBuilder = sqlBuilder.deleteCharAt(indexOfFirstComma);
}
sqlBuilder.append(constrainBuilder);
try {
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("datatable");
if (realCause.getMessage().toLowerCase().contains("unknown column")) {
baseDataValidator.reset().parameter("name").failWithCode("does.not.exist");
} else if (realCause.getMessage().toLowerCase().contains("can't drop")) {
baseDataValidator.reset().parameter("name").failWithCode("does.not.exist");
} else if (realCause.getMessage().toLowerCase().contains("duplicate column")) {
baseDataValidator.reset().parameter("name").failWithCode("column.already.exists");
}
throwExceptionIfValidationWarningsExist(dataValidationErrors);
} catch (final PersistenceException ee) {
Throwable realCause = ExceptionUtils.getRootCause(ee.getCause());
final List<ApiParameterError> dataValidationErrors = new ArrayList<>();
final DataValidatorBuilder baseDataValidator = new DataValidatorBuilder(dataValidationErrors).resource("datatable");
if (realCause.getMessage().toLowerCase().contains("duplicate column name")) {
baseDataValidator.reset().parameter("name").failWithCode("duplicate.column.name");
} else if ((realCause.getMessage().contains("Table") || realCause.getMessage().contains("relation"))
&& realCause.getMessage().contains("already exists")) {
baseDataValidator.reset().parameter("datatableName").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");
}
throwExceptionIfValidationWarningsExist(dataValidationErrors);
}
}