This is an automated email from the ASF dual-hosted git repository.
arnold pushed a commit to branch develop
in repository https://gitbox.apache.org/repos/asf/fineract.git
The following commit(s) were added to refs/heads/develop by this push:
new 59871353f FINERACT-1760-Index-or-Unique-column-creation-datatables
59871353f is described below
commit 59871353fb24465a88ba1bd8e060facdb46dc567
Author: Ruchi Dhamankar <[email protected]>
AuthorDate: Fri Dec 9 14:31:20 2022 +0530
FINERACT-1760-Index-or-Unique-column-creation-datatables
---
.../DatatableCommandFromApiJsonDeserializer.java | 24 ++-
.../database/DatabaseIndependentQueryService.java | 6 +
...eQueryService.java => DatabaseIndexMapper.java} | 19 +-
.../service/database/DatabaseQueryService.java | 3 +
...{DatabaseQueryService.java => IndexDetail.java} | 15 +-
.../core/service/database/MySQLQueryService.java | 13 ++
.../service/database/PostgreSQLQueryService.java | 13 ++
.../api/DatatablesApiResourceSwagger.java | 16 +-
.../data/ResultsetColumnHeaderData.java | 22 ++-
.../service/GenericDataServiceImpl.java | 37 +++-
.../service/ReadWriteNonCoreDataServiceImpl.java | 214 +++++++++++++++++++-
.../ReadWriteNonCoreDataServiceImplTest.java | 12 +-
.../DatatableUniqueAndIndexColumnTest.java | 220 +++++++++++++++++++++
.../common/system/DatatableHelper.java | 41 +++-
14 files changed, 615 insertions(+), 40 deletions(-)
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/serialization/DatatableCommandFromApiJsonDeserializer.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/serialization/DatatableCommandFromApiJsonDeserializer.java
index 5f3db62f7..af95ff80a 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/serialization/DatatableCommandFromApiJsonDeserializer.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/serialization/DatatableCommandFromApiJsonDeserializer.java
@@ -74,19 +74,21 @@ public class DatatableCommandFromApiJsonDeserializer {
public static final String DROPDOWN = "dropdown";
private static final String DATATABLE_NAME_REGEX_PATTERN =
"^[a-zA-Z][a-zA-Z0-9\\-_\\s]{0,48}[a-zA-Z0-9]$";
private static final String DATATABLE_COLUMN_NAME_REGEX_PATTERN =
"^[a-zA-Z][a-zA-Z0-9\\-_\\s]{0,}[a-zA-Z0-9]$";
+ private static final String INDEXED = "indexed";
+ private static final String UNIQUE = "unique";
/**
* The parameters supported for this command.
*/
private static final Set<String> SUPPORTED_PARAMETERS_FOR_CREATE = new
HashSet<>(
Arrays.asList(DATATABLE_NAME, ENTITY_SUB_TYPE, APPTABLE_NAME,
MULTI_ROW, COLUMNS));
private static final Set<String> SUPPORTED_PARAMETERS_FOR_CREATE_COLUMNS =
new HashSet<>(
- Arrays.asList(NAME, TYPE, LENGTH, MANDATORY, CODE));
+ Arrays.asList(NAME, TYPE, LENGTH, MANDATORY, CODE, UNIQUE,
INDEXED));
private static final Set<String> SUPPORTED_PARAMETERS_FOR_UPDATE = new
HashSet<>(
Arrays.asList(APPTABLE_NAME, ENTITY_SUB_TYPE, CHANGE_COLUMNS,
ADD_COLUMNS, DROP_COLUMNS));
private static final Set<String> SUPPORTED_PARAMETERS_FOR_ADD_COLUMNS =
new HashSet<>(
- Arrays.asList(NAME, TYPE, LENGTH, MANDATORY, AFTER, CODE));
+ Arrays.asList(NAME, TYPE, LENGTH, MANDATORY, AFTER, CODE, UNIQUE,
INDEXED));
private static final Set<String> SUPPORTED_PARAMETERS_FOR_CHANGE_COLUMNS =
new HashSet<>(
- Arrays.asList(NAME, NEW_NAME, LENGTH, MANDATORY, AFTER, CODE,
NEW_CODE));
+ Arrays.asList(NAME, NEW_NAME, LENGTH, MANDATORY, AFTER, CODE,
NEW_CODE, UNIQUE, INDEXED));
private static final Set<String> SUPPORTED_PARAMETERS_FOR_DROP_COLUMNS =
new HashSet<>(List.of(NAME));
private static final Object[] SUPPORTED_COLUMN_TYPES = { STRING, NUMBER,
BOOLEAN, DECIMAL, DATE, DATETIME, TEXT, DROPDOWN };
private static final Object[] SUPPORTED_APPTABLE_NAMES = { M_LOAN,
M_SAVINGS_ACCOUNT, M_CLIENT, M_GROUP, M_CENTER, M_OFFICE,
@@ -180,7 +182,11 @@ public class DatatableCommandFromApiJsonDeserializer {
validateType(baseDataValidator, column);
final Boolean mandatory =
this.fromApiJsonHelper.extractBooleanNamed(MANDATORY, column);
+ final Boolean unique =
this.fromApiJsonHelper.extractBooleanNamed(UNIQUE, column);
+ final Boolean indexed =
this.fromApiJsonHelper.extractBooleanNamed(INDEXED, column);
baseDataValidator.reset().parameter(MANDATORY).value(mandatory).ignoreIfNull().notBlank().isOneOfTheseValues(true,
false);
+
baseDataValidator.reset().parameter(UNIQUE).value(unique).ignoreIfNull().notBlank().isOneOfTheseValues(true,
false);
+
baseDataValidator.reset().parameter(INDEXED).value(indexed).ignoreIfNull().notBlank().isOneOfTheseValues(true,
false);
}
}
@@ -262,6 +268,12 @@ public class DatatableCommandFromApiJsonDeserializer {
final Boolean after =
this.fromApiJsonHelper.extractBooleanNamed(AFTER, column);
baseDataValidator.reset().parameter(AFTER).value(after).ignoreIfNull().notBlank().isOneOfTheseValues(true,
false);
+
+ final Boolean unique =
this.fromApiJsonHelper.extractBooleanNamed(UNIQUE, column);
+
baseDataValidator.reset().parameter(UNIQUE).value(unique).ignoreIfNull().notBlank().isOneOfTheseValues(true,
false);
+
+ final Boolean indexed =
this.fromApiJsonHelper.extractBooleanNamed(INDEXED, column);
+
baseDataValidator.reset().parameter(INDEXED).value(indexed).ignoreIfNull().notBlank().isOneOfTheseValues(true,
false);
}
}
@@ -283,6 +295,12 @@ public class DatatableCommandFromApiJsonDeserializer {
final Boolean after =
this.fromApiJsonHelper.extractBooleanNamed(AFTER, column);
baseDataValidator.reset().parameter(AFTER).value(after).ignoreIfNull().notBlank().isOneOfTheseValues(true,
false);
+
+ final Boolean unique =
this.fromApiJsonHelper.extractBooleanNamed(UNIQUE, column);
+
baseDataValidator.reset().parameter(UNIQUE).value(unique).ignoreIfNull().notBlank().isOneOfTheseValues(true,
false);
+
+ final Boolean indexed =
this.fromApiJsonHelper.extractBooleanNamed(INDEXED, column);
+
baseDataValidator.reset().parameter(INDEXED).value(indexed).ignoreIfNull().notBlank().isOneOfTheseValues(true,
false);
}
}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseIndependentQueryService.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseIndependentQueryService.java
index b62fba71e..480d11897 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseIndependentQueryService.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseIndependentQueryService.java
@@ -22,6 +22,7 @@ import static
org.apache.commons.collections4.CollectionUtils.isNotEmpty;
import java.sql.SQLException;
import java.util.Collection;
+import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.support.rowset.SqlRowSet;
@@ -66,4 +67,9 @@ public class DatabaseIndependentQueryService implements
DatabaseQueryService {
public SqlRowSet getTableColumns(DataSource dataSource, String tableName) {
return choose(dataSource).getTableColumns(dataSource, tableName);
}
+
+ @Override
+ public List<IndexDetail> getTableIndexes(DataSource dataSource, String
tableName) {
+ return choose(dataSource).getTableIndexes(dataSource, tableName);
+ }
}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseQueryService.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseIndexMapper.java
similarity index 68%
copy from
fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseQueryService.java
copy to
fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseIndexMapper.java
index b004685e4..480a08d46 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseQueryService.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseIndexMapper.java
@@ -18,15 +18,20 @@
*/
package org.apache.fineract.infrastructure.core.service.database;
-import javax.sql.DataSource;
+import java.util.ArrayList;
+import java.util.List;
import org.springframework.jdbc.support.rowset.SqlRowSet;
-public interface DatabaseQueryService {
+public final class DatabaseIndexMapper {
- boolean isSupported();
+ private DatabaseIndexMapper() {}
- boolean isTablePresent(DataSource dataSource, String tableName);
-
- // TODO: This needs to be improved to have a custom POJO return type
instead of the raw SqlRowSet
- SqlRowSet getTableColumns(DataSource dataSource, String tableName);
+ public static List<IndexDetail> getIndexDetails(SqlRowSet rowset) {
+ List<IndexDetail> indexes = new ArrayList<>();
+ rowset.beforeFirst();
+ while (rowset.next()) {
+ indexes.add(new IndexDetail(rowset.getString(1)));
+ }
+ return indexes;
+ }
}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseQueryService.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseQueryService.java
index b004685e4..14c8cff49 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseQueryService.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseQueryService.java
@@ -18,6 +18,7 @@
*/
package org.apache.fineract.infrastructure.core.service.database;
+import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.support.rowset.SqlRowSet;
@@ -29,4 +30,6 @@ public interface DatabaseQueryService {
// TODO: This needs to be improved to have a custom POJO return type
instead of the raw SqlRowSet
SqlRowSet getTableColumns(DataSource dataSource, String tableName);
+
+ List<IndexDetail> getTableIndexes(DataSource dataSource, String tableName);
}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseQueryService.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/IndexDetail.java
similarity index 68%
copy from
fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseQueryService.java
copy to
fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/IndexDetail.java
index b004685e4..51d79e2e3 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseQueryService.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/IndexDetail.java
@@ -18,15 +18,12 @@
*/
package org.apache.fineract.infrastructure.core.service.database;
-import javax.sql.DataSource;
-import org.springframework.jdbc.support.rowset.SqlRowSet;
+import lombok.Getter;
+import lombok.RequiredArgsConstructor;
-public interface DatabaseQueryService {
+@Getter
+@RequiredArgsConstructor
+public class IndexDetail {
- boolean isSupported();
-
- boolean isTablePresent(DataSource dataSource, String tableName);
-
- // TODO: This needs to be improved to have a custom POJO return type
instead of the raw SqlRowSet
- SqlRowSet getTableColumns(DataSource dataSource, String tableName);
+ private final String indexName;
}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/MySQLQueryService.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/MySQLQueryService.java
index fbf8afa24..10dc42a00 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/MySQLQueryService.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/MySQLQueryService.java
@@ -20,6 +20,7 @@ package
org.apache.fineract.infrastructure.core.service.database;
import static java.lang.String.format;
+import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
@@ -60,4 +61,16 @@ public class MySQLQueryService implements
DatabaseQueryService {
throw new IllegalArgumentException("Table " + tableName + " is not
found");
}
}
+
+ @Override
+ public List<IndexDetail> getTableIndexes(DataSource dataSource, String
tableName) {
+ final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
+ final String sql = "SELECT i.INDEX_NAME FROM
INFORMATION_SCHEMA.STATISTICS i WHERE TABLE_SCHEMA = schema() AND TABLE_NAME =
?";
+ final SqlRowSet indexDefinitions = jdbcTemplate.queryForRowSet(sql,
new Object[] { tableName }); // NOSONAR
+ if (indexDefinitions.next()) {
+ return DatabaseIndexMapper.getIndexDetails(indexDefinitions);
+ } else {
+ throw new IllegalArgumentException("Table " + tableName + " is not
found");
+ }
+ }
}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/PostgreSQLQueryService.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/PostgreSQLQueryService.java
index 3500227e6..9ab2b5b05 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/PostgreSQLQueryService.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/core/service/database/PostgreSQLQueryService.java
@@ -20,6 +20,7 @@ package
org.apache.fineract.infrastructure.core.service.database;
import static java.lang.String.format;
+import java.util.List;
import java.util.Objects;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
@@ -62,4 +63,16 @@ public class PostgreSQLQueryService implements
DatabaseQueryService {
throw new IllegalArgumentException("Table " + tableName + " is not
found");
}
}
+
+ @Override
+ public List<IndexDetail> getTableIndexes(DataSource dataSource, String
tableName) {
+ JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
+ String sql = "SELECT indexname FROM pg_indexes WHERE schemaname =
'public' AND tablename = '" + tableName + "'";
+ final SqlRowSet indexDefinitions = jdbcTemplate.queryForRowSet(sql);
// NOSONAR
+ if (indexDefinitions.next()) {
+ return DatabaseIndexMapper.getIndexDetails(indexDefinitions);
+ } else {
+ throw new IllegalArgumentException("Table " + tableName + " is not
found");
+ }
+ }
}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/api/DatatablesApiResourceSwagger.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/api/DatatablesApiResourceSwagger.java
index d21306f46..a0fd0b924 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/api/DatatablesApiResourceSwagger.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/api/DatatablesApiResourceSwagger.java
@@ -67,6 +67,10 @@ final class DatatablesApiResourceSwagger {
public Boolean mandatory;
@Schema(example = "1653", description = "Length of the text field.
Mandatory if type String is used, otherwise an error is returned.")
public Long length;
+ @Schema(example = "true", description = "Defaults to false")
+ public Boolean unique;
+ @Schema(example = "true", description = "Defaults to false")
+ public Boolean indexed;
}
@Schema(required = true, example = "m_client")
@@ -119,6 +123,10 @@ final class DatatablesApiResourceSwagger {
public String code;
@Schema(example = "true")
public boolean mandatory;
+ @Schema(example = "true")
+ public boolean unique;
+ @Schema(example = "true", description = "Defaults to false")
+ public Boolean indexed;
}
static final class PutDataTablesRequestChangeColumns {
@@ -135,13 +143,17 @@ final class DatatablesApiResourceSwagger {
public String newCode;
@Schema(example = "true")
public boolean mandatory;
+ @Schema(example = "true")
+ public boolean unique;
+ @Schema(example = "true", description = "Defaults to false")
+ public Boolean indexed;
}
@Schema(example = "m_client")
- public String appTableName;
+ public String apptableName;
public List<PutDataTablesRequestDropColumns> dropColumns;
public List<PutDataTablesRequestAddColumns> addColumns;
- public List<PutDataTablesRequestChangeColumns> ChangeColumns;
+ public List<PutDataTablesRequestChangeColumns> changeColumns;
}
@Schema(description = "PutDataTablesResponse")
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/ResultsetColumnHeaderData.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/ResultsetColumnHeaderData.java
index 5d6c6ea9d..33c75219d 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/ResultsetColumnHeaderData.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/ResultsetColumnHeaderData.java
@@ -34,6 +34,8 @@ public final class ResultsetColumnHeaderData implements
Serializable {
private final String columnDisplayType;
private final boolean isColumnNullable;
private final boolean isColumnPrimaryKey;
+ private final boolean isColumnUnique;
+ private final boolean isColumnIndexed;
private final List<ResultsetColumnValueData> columnValues;
private final String columnCode;
@@ -45,20 +47,22 @@ public final class ResultsetColumnHeaderData implements
Serializable {
final boolean columnIsPrimaryKey = false;
final List<ResultsetColumnValueData> columnValues = new ArrayList<>();
final String columnCode = null;
+ final boolean columnIsUnique = false;
+ final boolean columnIsIndexed = false;
return new ResultsetColumnHeaderData(columnName, columnType,
columnLength, columnNullable, columnIsPrimaryKey, columnValues,
- columnCode);
+ columnCode, columnIsUnique, columnIsIndexed);
}
public static ResultsetColumnHeaderData detailed(final String columnName,
final String columnType, final Long columnLength,
final boolean columnNullable, final boolean columnIsPrimaryKey,
final List<ResultsetColumnValueData> columnValues,
- final String columnCode) {
+ final String columnCode, final boolean columnIsUnique, final
boolean columnIsIndexed) {
return new ResultsetColumnHeaderData(columnName, columnType,
columnLength, columnNullable, columnIsPrimaryKey, columnValues,
- columnCode);
+ columnCode, columnIsUnique, columnIsIndexed);
}
private ResultsetColumnHeaderData(final String columnName, final String
columnType, final Long columnLength,
final boolean columnNullable, final boolean columnIsPrimaryKey,
final List<ResultsetColumnValueData> columnValues,
- final String columnCode) {
+ final String columnCode, final boolean columnIsUnique, final
boolean columnIsIndexed) {
this.columnName = columnName;
this.columnType = columnType;
this.columnLength = columnLength;
@@ -66,6 +70,8 @@ public final class ResultsetColumnHeaderData implements
Serializable {
this.isColumnPrimaryKey = columnIsPrimaryKey;
this.columnValues = columnValues;
this.columnCode = columnCode;
+ this.isColumnUnique = columnIsUnique;
+ this.isColumnIndexed = columnIsIndexed;
// Refer org.drizzle.jdbc.internal.mysql.MySQLType.java
adjustColumnTypes();
@@ -255,6 +261,14 @@ public final class ResultsetColumnHeaderData implements
Serializable {
return isColumnPrimaryKey;
}
+ public boolean getIsColumnUnique() {
+ return isColumnUnique;
+ }
+
+ public boolean getIsColumnIndexed() {
+ return isColumnIndexed;
+ }
+
public String getColumnDisplayType() {
return this.columnDisplayType;
}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/GenericDataServiceImpl.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/GenericDataServiceImpl.java
index e28bd7701..d8d52ce82 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/GenericDataServiceImpl.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/GenericDataServiceImpl.java
@@ -30,6 +30,7 @@ import lombok.extern.slf4j.Slf4j;
import
org.apache.fineract.infrastructure.core.exception.PlatformDataIntegrityException;
import org.apache.fineract.infrastructure.core.service.RoutingDataSource;
import
org.apache.fineract.infrastructure.core.service.database.DatabaseIndependentQueryService;
+import org.apache.fineract.infrastructure.core.service.database.IndexDetail;
import
org.apache.fineract.infrastructure.dataqueries.data.GenericResultsetData;
import
org.apache.fineract.infrastructure.dataqueries.data.ResultsetColumnHeaderData;
import
org.apache.fineract.infrastructure.dataqueries.data.ResultsetColumnValueData;
@@ -213,6 +214,7 @@ public class GenericDataServiceImpl implements
GenericDataService {
@Override
public List<ResultsetColumnHeaderData> fillResultsetColumnHeaders(final
String datatable) {
final SqlRowSet columnDefinitions = getDatatableMetaData(datatable);
+ final List<IndexDetail> indexDefinitions =
getDatatableIndexData(datatable);
final List<ResultsetColumnHeaderData> columnHeaders = new
ArrayList<>();
@@ -226,6 +228,11 @@ public class GenericDataServiceImpl implements
GenericDataService {
final boolean columnNullable = "YES".equalsIgnoreCase(isNullable)
|| "TRUE".equalsIgnoreCase(isNullable);
final boolean columnIsPrimaryKey =
"PRI".equalsIgnoreCase(isPrimaryKey) || "TRUE".equalsIgnoreCase(isPrimaryKey);
+ final boolean columnIsUnique = checkUnique(datatable, columnName,
indexDefinitions);
+ boolean columnIsIndexed = false;
+ if (!columnIsUnique) {
+ columnIsIndexed = checkIndexed(datatable, columnName,
indexDefinitions);
+ }
List<ResultsetColumnValueData> columnValues = new ArrayList<>();
String codeName = null;
@@ -238,12 +245,40 @@ public class GenericDataServiceImpl implements
GenericDataService {
}
columnHeaders.add(ResultsetColumnHeaderData.detailed(columnName,
columnType, columnLength, columnNullable, columnIsPrimaryKey,
- columnValues, codeName));
+ columnValues, codeName, columnIsUnique, columnIsIndexed));
}
return columnHeaders;
}
+ private boolean checkUnique(String datatable, String columnName,
List<IndexDetail> indexDefinitions) {
+ String keyNameToCheck = "uk_" + datatable + "_" + columnName;
+ return checkKeyPresent(keyNameToCheck, indexDefinitions);
+ }
+
+ private boolean checkIndexed(String datatable, String columnName,
List<IndexDetail> indexDefinitions) {
+ String keyNameToCheck = "idx_" + datatable + "_" + columnName;
+ return checkKeyPresent(keyNameToCheck, indexDefinitions);
+ }
+
+ private boolean checkKeyPresent(String keyNameToCheck, List<IndexDetail>
indexDefinitions) {
+ for (IndexDetail indexDetail : indexDefinitions) {
+ if (indexDetail.getIndexName().equals(keyNameToCheck)) {
+ return true;
+ }
+ }
+ return false;
+ }
+
+ @SuppressWarnings("AvoidHidingCauseException")
+ private List<IndexDetail> getDatatableIndexData(String datatable) {
+ try {
+ return databaseIndependentQueryService.getTableIndexes(dataSource,
datatable);
+ } catch (IllegalArgumentException e) {
+ throw new DatatableNotFoundException(datatable);
+ }
+ }
+
/*
* Candidate for using caching there to get allowed 'column values' from
code/codevalue tables
*/
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImpl.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImpl.java
index b1cb16292..c716cc21f 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImpl.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImpl.java
@@ -492,7 +492,8 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
return category.equals(DataTableApiConstant.CATEGORY_PPI);
}
- private JsonElement addColumn(final String name, final String dataType,
final boolean isMandatory, final Integer length) {
+ private JsonElement addColumn(final String name, final String dataType,
final boolean isMandatory, final Integer length,
+ final boolean isUnique, final boolean isIndexed) {
JsonObject column = new JsonObject();
column.addProperty("name", name);
column.addProperty("type", dataType);
@@ -500,6 +501,8 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
column.addProperty("length", length);
}
column.addProperty("mandatory", (isMandatory ? "true" : "false"));
+ column.addProperty("unique", (isUnique ? "true" : "false"));
+ column.addProperty("indexed", (isIndexed ? "true" : "false"));
return column;
}
@@ -713,6 +716,7 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
final String type = column.has("type") ?
column.get("type").getAsString().toLowerCase() : null;
final Integer length = column.has("length") ?
column.get("length").getAsInt() : null;
final Boolean mandatory = column.has("mandatory") ?
column.get("mandatory").getAsBoolean() : false;
+ final Boolean unique = column.has("unique") ?
column.get("unique").getAsBoolean() : false;
final String code = column.has("code") ?
column.get("code").getAsString() : null;
if (StringUtils.isNotBlank(code)) {
@@ -748,6 +752,11 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
}
}
}
+ if (unique) {
+ String uniqueKeyName = "uk_" + dataTableNameAlias + "_" + name;
+ constrainBuilder.append(", CONSTRAINT
").append(sqlGenerator.escape(uniqueKeyName)).append(" ")
+ .append("UNIQUE (" + sqlGenerator.escape(name) + ")");
+ }
if (mandatory) {
sqlBuilder = sqlBuilder.append(" NOT NULL");
@@ -811,8 +820,10 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
sqlBuilder = sqlBuilder.append(sqlGenerator.escape(fkColumnName) +
" BIGINT NOT NULL, ");
// Add Created At and Updated At
- columns.add(addColumn(DataTableApiConstant.CREATEDAT_FIELD_NAME,
DataTableApiConstant.DATETIME_FIELD_TYPE, false, null));
- columns.add(addColumn(DataTableApiConstant.UPDATEDAT_FIELD_NAME,
DataTableApiConstant.DATETIME_FIELD_TYPE, false, null));
+ columns.add(addColumn(DataTableApiConstant.CREATEDAT_FIELD_NAME,
DataTableApiConstant.DATETIME_FIELD_TYPE, false, null, false,
+ false));
+ columns.add(addColumn(DataTableApiConstant.UPDATEDAT_FIELD_NAME,
DataTableApiConstant.DATETIME_FIELD_TYPE, false, null, false,
+ false));
for (final JsonElement column : columns) {
parseDatatableColumnObjectForCreate(column.getAsJsonObject(),
sqlBuilder, constrainBuilder, dataTableNameAlias,
codeMappings, isConstraintApproach);
@@ -847,6 +858,8 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
this.jdbcTemplate.execute(sqlBuilder.toString());
+ // create indexes
+ createIndexesForTable(datatableName, columns);
registerDatatable(datatableName, apptableName, entitySubType);
registerColumnCodeMapping(codeMappings);
} catch (final PersistenceException | DataAccessException e) {
@@ -871,6 +884,24 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
return new
CommandProcessingResultBuilder().withCommandId(command.commandId()).withResourceIdAsString(datatableName).build();
}
+ private void createIndexesForTable(String datatableName, JsonArray
columns) {
+ for (final JsonElement column : columns) {
+ createIndexForColumn(datatableName, column.getAsJsonObject());
+ }
+ }
+
+ private void createIndexForColumn(String datatableName, JsonObject column)
{
+ String name = column.has("name") ? column.get("name").getAsString() :
null;
+ final Boolean unique = column.has("unique") ?
column.get("unique").getAsBoolean() : false;
+ final Boolean indexed = column.has("indexed") ?
column.get("indexed").getAsBoolean() : false;
+ if (indexed) {
+ if (!unique) {
+ String indexName = "idx_" + datatableName + "_" + name;
+ createIndexForColumnOnTable(indexName, datatableName, name);
+ }
+ }
+ }
+
private long addMultirowRecord(String sql) throws SQLException {
KeyHolder keyHolder = new GeneratedKeyHolder();
int insertsCount = this.jdbcTemplate.update(c ->
c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS), keyHolder);
@@ -956,8 +987,11 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
if (length == null && type.toLowerCase().equals("varchar")) {
length =
mapColumnNameDefinition.get(name).getColumnLength().intValue();
}
-
- sqlBuilder = sqlBuilder.append(", CHANGE " + sqlGenerator.escape(name)
+ " " + sqlGenerator.escape(newName) + " " + type);
+ if (databaseTypeResolver.isMySQL()) {
+ sqlBuilder = sqlBuilder.append(", CHANGE " +
sqlGenerator.escape(name) + " " + sqlGenerator.escape(newName) + " " + type);
+ } else if (databaseTypeResolver.isPostgreSQL()) {
+ sqlBuilder = sqlBuilder.append(", RENAME " +
sqlGenerator.escape(name) + " TO " + sqlGenerator.escape(newName));
+ }
if (length != null && length > 0) {
if (type.toLowerCase().equals("decimal")) {
sqlBuilder.append("(19,6)");
@@ -967,9 +1001,13 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
}
if (mandatory) {
- sqlBuilder = sqlBuilder.append(" NOT NULL");
+ if (databaseTypeResolver.isMySQL()) {
+ sqlBuilder = sqlBuilder.append(" NOT NULL");
+ }
} else {
- sqlBuilder = sqlBuilder.append(" DEFAULT NULL");
+ if (databaseTypeResolver.isMySQL()) {
+ sqlBuilder = sqlBuilder.append(" DEFAULT NULL");
+ }
}
if (after != null) {
@@ -997,6 +1035,7 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
final String type = column.has("type") ?
column.get("type").getAsString().toLowerCase() : null;
final Integer length = column.has("length") ?
column.get("length").getAsInt() : null;
final Boolean mandatory = column.has("mandatory") ?
column.get("mandatory").getAsBoolean() : false;
+ final Boolean unique = column.has("unique") ?
column.get("unique").getAsBoolean() : false;
final String after = column.has("after") ?
column.get("after").getAsString() : null;
final String code = column.has("code") ?
column.get("code").getAsString() : null;
@@ -1032,6 +1071,12 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
}
}
+ if (unique) {
+ String uniqueKeyName = "uk_" + dataTableNameAlias + "_" + name;
+ constrainBuilder.append(",ADD CONSTRAINT
").append(sqlGenerator.escape(uniqueKeyName)).append(" ")
+ .append("UNIQUE (" + sqlGenerator.escape(name) + ")");
+ }
+
if (mandatory) {
sqlBuilder = sqlBuilder.append(" NOT NULL");
} else {
@@ -1233,6 +1278,7 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
}
sqlBuilder.append(constrainBuilder);
this.jdbcTemplate.execute(sqlBuilder.toString());
+ createIndexesForTable(datatableName, addColumns);
registerColumnCodeMapping(codeMappings);
}
if (changeColumns != null) {
@@ -1259,6 +1305,10 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
this.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) {
if (e.getMessage().contains("Error on rename")) {
throw new
PlatformServiceUnavailableException("error.msg.datatable.column.update.not.allowed",
@@ -1309,6 +1359,156 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
}
}
+ private void updateUniqueConstraintsForTable(String datatableName,
JsonArray changeColumns,
+ Map<String, ResultsetColumnHeaderData> mapColumnNameDefinition) {
+ for (final JsonElement column : changeColumns) {
+
+ String name = column.getAsJsonObject().has("name") ?
column.getAsJsonObject().get("name").getAsString() : null;
+
+ if (!mapColumnNameDefinition.containsKey(name)) {
+ throw new
PlatformDataIntegrityException("error.msg.datatable.column.missing.update.parse",
+ "Column " + name + " does not exist.", name);
+ }
+
+ updateColumnUniqueConstraints(datatableName,
column.getAsJsonObject(),
+
mapColumnNameDefinition.get(column.getAsJsonObject().get("name").getAsString()));
+ }
+ }
+
+ private void updateColumnUniqueConstraints(String datatableName,
JsonObject column, ResultsetColumnHeaderData columnMetaData) {
+ // check for unique constraint update
+ String name = column.has("name") ? column.get("name").getAsString() :
null;
+ String columnNewName = column.has("newName") ?
column.get("newName").getAsString() : null;
+ final Boolean setUnique = column.has("unique") ?
column.get("unique").getAsBoolean() : false;
+ final Boolean isAlreadyUnique = columnMetaData.getIsColumnUnique();
+ String uniqueKeyName = "uk_" + datatableName + "_" + name;
+
+ if (isAlreadyUnique) {
+ if (!setUnique) {
+ // drop existing constraint
+ dropUniqueConstraint(datatableName, uniqueKeyName);
+ } else {
+ // if columnname changed
+ checkColumnRenameAndModifyUniqueConstraint(datatableName,
columnNewName, uniqueKeyName);
+ }
+ } else {
+ if (setUnique) {
+ checkColumnRenameAndCreateUniqueConstraint(datatableName,
name, columnNewName, uniqueKeyName);
+ }
+ }
+ }
+
+ private void checkColumnRenameAndCreateUniqueConstraint(String
datatableName, String name, String columnNewName, String constraintKey) {
+ if (columnNewName != null) {
+ // create constraint with new column name
+ String uniqueKeyName = "uk_" + datatableName + "_" + columnNewName;
+ createUniqueConstraint(datatableName, columnNewName,
uniqueKeyName);
+ } else {
+ // create constraint for column
+ createUniqueConstraint(datatableName, name, constraintKey);
+ }
+ }
+
+ private void checkColumnRenameAndModifyUniqueConstraint(String
datatableName, String columnNewName, String existingConstraint) {
+ if (columnNewName != null) {
+ // drop existing constraint
+ dropUniqueConstraint(datatableName, existingConstraint);
+ // create constraint with new column name
+ String uniqueKeyName = "uk_" + datatableName + "_" + columnNewName;
+ createUniqueConstraint(datatableName, columnNewName,
uniqueKeyName);
+ }
+ }
+
+ private void createUniqueConstraint(String datatableName, String
columnName, String uniqueKeyName) {
+ StringBuilder constrainBuilder = new StringBuilder();
+ constrainBuilder.append("ALTER TABLE
").append(sqlGenerator.escape(datatableName)).append(" ADD CONSTRAINT ")
+ .append(sqlGenerator.escape(uniqueKeyName)).append(" UNIQUE ("
+ sqlGenerator.escape(columnName) + ");");
+ this.jdbcTemplate.execute(constrainBuilder.toString());
+ }
+
+ private void dropUniqueConstraint(String datatableName, String
uniqueKeyName) {
+ StringBuilder constrainBuilder = new StringBuilder();
+ constrainBuilder.append("ALTER TABLE
").append(sqlGenerator.escape(datatableName)).append(" DROP CONSTRAINT ")
+ .append(sqlGenerator.escape(uniqueKeyName)).append(";");
+ this.jdbcTemplate.execute(constrainBuilder.toString());
+ }
+
+ private void updateIndexesForTable(String datatableName, JsonArray
changeColumns,
+ Map<String, ResultsetColumnHeaderData> mapColumnNameDefinition) {
+ for (final JsonElement column : changeColumns) {
+ String name = column.getAsJsonObject().has("name") ?
column.getAsJsonObject().get("name").getAsString() : null;
+ if (!mapColumnNameDefinition.containsKey(name)) {
+ throw new
PlatformDataIntegrityException("error.msg.datatable.column.missing.update.parse",
+ "Column " + name + " does not exist.", name);
+ }
+ updateIndexForColumn(datatableName, column.getAsJsonObject(),
+
mapColumnNameDefinition.get(column.getAsJsonObject().get("name").getAsString()));
+ }
+ }
+
+ private void updateIndexForColumn(String datatableName, JsonObject column,
ResultsetColumnHeaderData columnMetaData) {
+ String name = column.has("name") ? column.get("name").getAsString() :
null;
+ String columnNewName = column.has("newName") ?
column.get("newName").getAsString() : null;
+ final Boolean setForUnique = column.has("unique") ?
column.get("unique").getAsBoolean() : false;
+ final Boolean setForIndexed = column.has("indexed") ?
column.get("indexed").getAsBoolean() : false;
+ if (!setForUnique) {
+ final Boolean isAlreadyIndexed =
columnMetaData.getIsColumnIndexed();
+ String uniqueIndexName = "idx_" + datatableName + "_" + name;
+ if (isAlreadyIndexed) {
+ if (!setForIndexed) {
+ // drop index
+ dropIndex(datatableName, uniqueIndexName);
+ } else { // if column name changed
+ checkColumnRenameAndModifyIndex(datatableName,
columnNewName, uniqueIndexName);
+ }
+
+ } else {
+ if (setForIndexed) {
+ checkColumnRenameAndCreateIndex(datatableName, name,
columnNewName, uniqueIndexName);
+ }
+ }
+
+ }
+
+ }
+
+ private void checkColumnRenameAndCreateIndex(String datatableName, String
columnExistingName, String columnNewName, String indexName) {
+ if (columnNewName != null) {
+ String uniqueIndexName = "idx_" + datatableName + "_" +
columnNewName;
+ // create index with new column name
+ createIndexForColumnOnTable(uniqueIndexName, datatableName,
columnNewName);
+ } else {
+ // create index with previous name
+ createIndexForColumnOnTable(indexName, datatableName,
columnExistingName);
+ }
+ }
+
+ private void checkColumnRenameAndModifyIndex(String datatableName, String
columnNewName, String existingIndex) {
+ if (columnNewName != null) {
+ // drop index with previous name
+ dropIndex(datatableName, existingIndex);
+ // create index with new name
+ String uniqueIndexName = "idx_" + datatableName + "_" +
columnNewName;
+ createIndexForColumnOnTable(uniqueIndexName, datatableName,
columnNewName);
+ }
+ }
+
+ private void createIndexForColumnOnTable(String uniqueIndexName, String
datatableName, String columnName) {
+ StringBuilder sqlIndexUpdateBuilder = new StringBuilder();
+ sqlIndexUpdateBuilder.append("CREATE INDEX
").append(sqlGenerator.escape(uniqueIndexName)).append(" ON ")
+ .append(sqlGenerator.escape(datatableName)).append("
(").append(sqlGenerator.escape(columnName)).append(");");
+ this.jdbcTemplate.execute(sqlIndexUpdateBuilder.toString());
+ }
+
+ private void dropIndex(String datatableName, String uniqueIndexName) {
+ StringBuilder sqlIndexUpdateBuilder = new StringBuilder();
+ if (databaseTypeResolver.isMySQL()) {
+ sqlIndexUpdateBuilder.append("ALTER TABLE
").append(sqlGenerator.escape(datatableName)).append(" ");
+ }
+ sqlIndexUpdateBuilder.append("DROP INDEX
").append(sqlGenerator.escape(uniqueIndexName)).append(";");
+ this.jdbcTemplate.execute(sqlIndexUpdateBuilder.toString());
+ }
+
@Transactional
@Override
public void deleteDatatable(final String datatableName) {
diff --git
a/fineract-provider/src/test/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImplTest.java
b/fineract-provider/src/test/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImplTest.java
index 34d88cd35..cd0bec1df 100644
---
a/fineract-provider/src/test/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImplTest.java
+++
b/fineract-provider/src/test/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImplTest.java
@@ -83,9 +83,9 @@ public class ReadWriteNonCoreDataServiceImplTest {
when(sqlRS.getObject(ArgumentMatchers.anyString())).thenReturn("value1").thenReturn("value2");
when(databaseTypeResolver.isPostgreSQL()).thenReturn(true);
- ResultsetColumnHeaderData cf1 =
ResultsetColumnHeaderData.detailed("cf1", "text", 10L, false, false, null,
null);
- ResultsetColumnHeaderData rc1 =
ResultsetColumnHeaderData.detailed("rc1", "text", 10L, false, false, null,
null);
- ResultsetColumnHeaderData rc2 =
ResultsetColumnHeaderData.detailed("rc2", "text", 10L, false, false, null,
null);
+ ResultsetColumnHeaderData cf1 =
ResultsetColumnHeaderData.detailed("cf1", "text", 10L, false, false, null,
null, false, false);
+ ResultsetColumnHeaderData rc1 =
ResultsetColumnHeaderData.detailed("rc1", "text", 10L, false, false, null,
null, false, false);
+ ResultsetColumnHeaderData rc2 =
ResultsetColumnHeaderData.detailed("rc2", "text", 10L, false, false, null,
null, false, false);
when(genericDataService.fillResultsetColumnHeaders("table")).thenReturn(List.of(cf1,
rc1, rc2));
List<JsonObject> results = underTest.queryDataTable("table", "cf1",
"vf1", "rc1,rc2");
@@ -109,9 +109,9 @@ public class ReadWriteNonCoreDataServiceImplTest {
when(sqlRS.getObject(ArgumentMatchers.anyString())).thenReturn("value1").thenReturn("value2");
when(databaseTypeResolver.isPostgreSQL()).thenReturn(false);
when(databaseTypeResolver.isMySQL()).thenReturn(false);
- ResultsetColumnHeaderData cf1 =
ResultsetColumnHeaderData.detailed("cf1", "text", 10L, false, false, null,
null);
- ResultsetColumnHeaderData rc1 =
ResultsetColumnHeaderData.detailed("rc1", "text", 10L, false, false, null,
null);
- ResultsetColumnHeaderData rc2 =
ResultsetColumnHeaderData.detailed("rc2", "text", 10L, false, false, null,
null);
+ ResultsetColumnHeaderData cf1 =
ResultsetColumnHeaderData.detailed("cf1", "text", 10L, false, false, null,
null, false, false);
+ ResultsetColumnHeaderData rc1 =
ResultsetColumnHeaderData.detailed("rc1", "text", 10L, false, false, null,
null, false, false);
+ ResultsetColumnHeaderData rc2 =
ResultsetColumnHeaderData.detailed("rc2", "text", 10L, false, false, null,
null, false, false);
when(genericDataService.fillResultsetColumnHeaders("table")).thenReturn(List.of(cf1,
rc1, rc2));
assertThrows(IllegalStateException.class, () ->
underTest.queryDataTable("table", "cf1", "vf1", "rc1,rc2"));
diff --git
a/integration-tests/src/test/java/org/apache/fineract/integrationtests/DatatableUniqueAndIndexColumnTest.java
b/integration-tests/src/test/java/org/apache/fineract/integrationtests/DatatableUniqueAndIndexColumnTest.java
new file mode 100644
index 000000000..5b98cf043
--- /dev/null
+++
b/integration-tests/src/test/java/org/apache/fineract/integrationtests/DatatableUniqueAndIndexColumnTest.java
@@ -0,0 +1,220 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.fineract.integrationtests;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertFalse;
+import static org.junit.jupiter.api.Assertions.assertNotNull;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+
+import io.restassured.builder.RequestSpecBuilder;
+import io.restassured.builder.ResponseSpecBuilder;
+import io.restassured.http.ContentType;
+import io.restassured.specification.RequestSpecification;
+import io.restassured.specification.ResponseSpecification;
+import java.util.List;
+import org.apache.fineract.client.models.GetDataTablesResponse;
+import org.apache.fineract.client.models.PostColumnHeaderData;
+import org.apache.fineract.client.models.PostDataTablesRequest;
+import org.apache.fineract.client.models.PostDataTablesResponse;
+import org.apache.fineract.client.models.PutDataTablesRequest;
+import org.apache.fineract.client.models.PutDataTablesRequestAddColumns;
+import org.apache.fineract.client.models.PutDataTablesRequestChangeColumns;
+import org.apache.fineract.client.models.PutDataTablesResponse;
+import org.apache.fineract.client.models.ResultsetColumnHeaderData;
+import org.apache.fineract.integrationtests.common.Utils;
+import org.apache.fineract.integrationtests.common.system.DatatableHelper;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+
+public class DatatableUniqueAndIndexColumnTest {
+
+ private static final String LOAN_APP_TABLE_NAME = "m_loan";
+ private RequestSpecification requestSpec;
+ private ResponseSpecification responseSpec;
+ private DatatableHelper datatableHelper;
+
+ @BeforeEach
+ public void setup() {
+ Utils.initializeRESTAssured();
+ this.requestSpec = new
RequestSpecBuilder().setContentType(ContentType.JSON).build();
+ this.requestSpec.header("Authorization", "Basic " +
Utils.loginIntoServerAndGetBase64EncodedAuthenticationKey());
+ this.responseSpec = new
ResponseSpecBuilder().expectStatusCode(200).build();
+ this.datatableHelper = new DatatableHelper(this.requestSpec,
this.responseSpec);
+ }
+
+ @Test
+ public void testDatableCreationWithUniqueAndIndexedColumns() {
+ // create dataTable
+ String datatableName = Utils.randomNameGenerator("dt_" +
LOAN_APP_TABLE_NAME + "_", 5).toLowerCase().toLowerCase();
+ String column1Name = "itsanumber";
+ String column2Name = "itsastring";
+
+ PostDataTablesRequest request = new PostDataTablesRequest();
+ request.setDatatableName(datatableName);
+ request.setApptableName(LOAN_APP_TABLE_NAME);
+ request.setMultiRow(false);
+
+ PostColumnHeaderData column1HeaderRequestData = new
PostColumnHeaderData();
+ column1HeaderRequestData.setName(column1Name);
+ column1HeaderRequestData.setType("Number");
+ column1HeaderRequestData.setMandatory(false);
+ column1HeaderRequestData.setLength(10L);
+ column1HeaderRequestData.setCode("");
+ column1HeaderRequestData.setUnique(true);
+ column1HeaderRequestData.setIndexed(true);
+
+ request.addColumnsItem(column1HeaderRequestData);
+
+ PostColumnHeaderData column2HeaderRequestData = new
PostColumnHeaderData();
+ column2HeaderRequestData.setName(column2Name);
+ column2HeaderRequestData.setType("String");
+ column2HeaderRequestData.setMandatory(false);
+ column2HeaderRequestData.setLength(10L);
+ column2HeaderRequestData.setCode("");
+ column2HeaderRequestData.setUnique(false);
+ column2HeaderRequestData.setIndexed(true);
+
+ request.addColumnsItem(column2HeaderRequestData);
+
+ PostDataTablesResponse response =
datatableHelper.createDatatable(request);
+ // Get Details of created datatable and verify unique and index
+ GetDataTablesResponse dataTable =
datatableHelper.getDataTableDetails(datatableName);
+
+ // verfify columns
+ List<ResultsetColumnHeaderData> columnHeaderData =
dataTable.getColumnHeaderData();
+ assertNotNull(columnHeaderData);
+
+ // two columns with 1 primary key and 2 audit columns created
+ assertEquals(columnHeaderData.size(), 5);
+
+ // verify Only Unique is set for column with both unique and index set
to true
+ for (ResultsetColumnHeaderData column : columnHeaderData) {
+ if (column.getColumnName().equalsIgnoreCase(column1Name)) {
+ assertTrue(column.getIsColumnUnique());
+ assertFalse(column.getIsColumnIndexed());
+ }
+ if (column.getColumnName().equalsIgnoreCase(column2Name)) {
+ assertTrue(column.getIsColumnIndexed());
+ }
+ }
+
+ }
+
+ @Test
+ public void testDatableModificationWithUniqueAndIndexedColumns() {
+ // create dataTable
+ String datatableName = Utils.randomNameGenerator("dt_" +
LOAN_APP_TABLE_NAME + "_", 5).toLowerCase().toLowerCase();
+ String column1Name = "itsanumber";
+ String column2Name = "itsastring";
+
+ PostDataTablesRequest request = new PostDataTablesRequest();
+ request.setDatatableName(datatableName);
+ request.setApptableName(LOAN_APP_TABLE_NAME);
+ request.setMultiRow(false);
+
+ PostColumnHeaderData column1HeaderRequestData = new
PostColumnHeaderData();
+ column1HeaderRequestData.setName(column1Name);
+ column1HeaderRequestData.setType("Number");
+ column1HeaderRequestData.setMandatory(false);
+ column1HeaderRequestData.setLength(10L);
+ column1HeaderRequestData.setCode("");
+ column1HeaderRequestData.setUnique(true);
+ column1HeaderRequestData.setIndexed(true);
+
+ request.addColumnsItem(column1HeaderRequestData);
+
+ PostColumnHeaderData column2HeaderRequestData = new
PostColumnHeaderData();
+ column2HeaderRequestData.setName(column2Name);
+ column2HeaderRequestData.setType("String");
+ column2HeaderRequestData.setMandatory(false);
+ column2HeaderRequestData.setLength(10L);
+ column2HeaderRequestData.setCode("");
+ column2HeaderRequestData.setUnique(false);
+ column2HeaderRequestData.setIndexed(true);
+
+ request.addColumnsItem(column2HeaderRequestData);
+
+ PostDataTablesResponse response =
datatableHelper.createDatatable(request);
+
+ assertEquals(datatableName, response.getResourceIdentifier());
+
+ // Modify datatable add columns and change columns
+ PutDataTablesRequest updateRequest = new PutDataTablesRequest();
+ updateRequest.setApptableName(LOAN_APP_TABLE_NAME);
+
+ String column3Name = "number1";
+ String column4Name = "number2";
+
+ PutDataTablesRequestAddColumns addColumn1 = new
PutDataTablesRequestAddColumns();
+ addColumn1.setName(column3Name);
+ addColumn1.setType("Number");
+ addColumn1.setMandatory(false);
+ addColumn1.setCode("");
+ addColumn1.setUnique(true);
+ addColumn1.setIndexed(false);
+
+ updateRequest.addAddColumnsItem(addColumn1);
+
+ PutDataTablesRequestAddColumns addColumn2 = new
PutDataTablesRequestAddColumns();
+ addColumn2.setName(column4Name);
+ addColumn2.setType("Number");
+ addColumn2.setMandatory(false);
+ addColumn2.setCode("");
+ addColumn2.setUnique(false);
+ addColumn2.setIndexed(true);
+
+ updateRequest.addAddColumnsItem(addColumn2);
+
+ PutDataTablesRequestChangeColumns changeColumns = new
PutDataTablesRequestChangeColumns();
+ changeColumns.setName(column1Name);
+ String newColumnName = column1Name + "new";
+ changeColumns.setNewName(newColumnName);
+ changeColumns.setIndexed(true);
+
+ updateRequest.addChangeColumnsItem(changeColumns);
+
+ // update dataTable
+ PutDataTablesResponse updateResponse =
datatableHelper.updateDatatable(datatableName, updateRequest);
+
+ // Get Details of created datatable and verify unique and index
+ GetDataTablesResponse dataTable =
datatableHelper.getDataTableDetails(datatableName);
+
+ // verify columns
+ List<ResultsetColumnHeaderData> columnHeaderData =
dataTable.getColumnHeaderData();
+ assertNotNull(columnHeaderData);
+
+ // 2 columns with 1 primary key ,2 audit columns and 2 new columns
created
+ assertEquals(columnHeaderData.size(), 7);
+
+ // verify unique and index is set for new columns and renamed column
has index set
+ for (ResultsetColumnHeaderData column : columnHeaderData) {
+ if (column.getColumnName().equalsIgnoreCase(column3Name)) {
+ assertTrue(column.getIsColumnUnique());
+ }
+ if (column.getColumnName().equalsIgnoreCase(column4Name)) {
+ assertTrue(column.getIsColumnIndexed());
+ }
+ if (column.getColumnName().equalsIgnoreCase(newColumnName)) {
+ assertTrue(column.getIsColumnIndexed());
+ }
+ }
+
+ }
+}
diff --git
a/integration-tests/src/test/java/org/apache/fineract/integrationtests/common/system/DatatableHelper.java
b/integration-tests/src/test/java/org/apache/fineract/integrationtests/common/system/DatatableHelper.java
index a47ae6ed5..cf771a2cc 100644
---
a/integration-tests/src/test/java/org/apache/fineract/integrationtests/common/system/DatatableHelper.java
+++
b/integration-tests/src/test/java/org/apache/fineract/integrationtests/common/system/DatatableHelper.java
@@ -30,13 +30,19 @@ import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Objects;
+import org.apache.fineract.client.models.GetDataTablesResponse;
+import org.apache.fineract.client.models.PostDataTablesRequest;
+import org.apache.fineract.client.models.PostDataTablesResponse;
import
org.apache.fineract.client.models.PutDataTablesAppTableIdDatatableIdResponse;
+import org.apache.fineract.client.models.PutDataTablesRequest;
+import org.apache.fineract.client.models.PutDataTablesResponse;
import org.apache.fineract.client.util.JSON;
+import org.apache.fineract.integrationtests.client.IntegrationTest;
import org.apache.fineract.integrationtests.common.Utils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
-public class DatatableHelper {
+public class DatatableHelper extends IntegrationTest {
private static final Gson GSON = new JSON().getGson();
@@ -214,4 +220,37 @@ public class DatatableHelper {
return datatableColumnsList;
}
+ public static List<HashMap<String, Object>>
addDatatableColumnsWithUniqueAndIndex(List<HashMap<String, Object>>
datatableColumnsList,
+ String columnName, String columnType, boolean isMandatory, Integer
length, String codeName, boolean isUnique,
+ boolean isIndexed) {
+
+ final HashMap<String, Object> datatableColumnMap = new HashMap<>();
+
+ datatableColumnMap.put("name", columnName);
+ datatableColumnMap.put("type", columnType);
+ datatableColumnMap.put("mandatory", isMandatory);
+ if (length != null) {
+ datatableColumnMap.put("length", length);
+ }
+ if (codeName != null) {
+ datatableColumnMap.put("code", codeName);
+ }
+ datatableColumnMap.put("unique", isUnique);
+ datatableColumnMap.put("indexed", isIndexed);
+ datatableColumnsList.add(datatableColumnMap);
+ return datatableColumnsList;
+ }
+
+ public PostDataTablesResponse createDatatable(PostDataTablesRequest
request) {
+ return ok(fineract().dataTables.createDatatable(request));
+ }
+
+ public GetDataTablesResponse getDataTableDetails(final String
dataTableName) {
+ return ok(fineract().dataTables.getDatatable(dataTableName));
+ }
+
+ public PutDataTablesResponse updateDatatable(String dataTableName,
PutDataTablesRequest request) {
+ return ok(fineract().dataTables.updateDatatable(dataTableName,
request));
+ }
+
}
