You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@fineract.apache.org by ar...@apache.org on 2022/12/12 15:01:40 UTC
[fineract] branch develop updated: FINERACT-1760-Index-or-Unique-column-creation-datatables
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 <ru...@gmail.com>
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));
+ }
+
}