You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@fineract.apache.org by ad...@apache.org on 2023/04/26 15:56:07 UTC
[fineract] branch develop updated: FINERACT-1757: Uppercase letters in the datatable name is failing on postgres
This is an automated email from the ASF dual-hosted git repository.
adamsaghy 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 6f2b0198a FINERACT-1757: Uppercase letters in the datatable name is failing on postgres
6f2b0198a is described below
commit 6f2b0198a293b99451a3baa851c60b22caa92907
Author: Jose Alberto Hernandez <al...@MacBook-Pro.local>
AuthorDate: Fri Apr 14 00:14:51 2023 -0600
FINERACT-1757: Uppercase letters in the datatable name is failing on postgres
---
.../service/ReadWriteNonCoreDataServiceImpl.java | 22 ++++-
.../ReadWriteNonCoreDataServiceImplTest.java | 5 +
.../common/system/DatatableHelper.java | 6 ++
.../datatable/DatatableIntegrationTest.java | 102 +++++++++++++++++++++
4 files changed, 132 insertions(+), 3 deletions(-)
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 cbb612a7a..fac9c6e39 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
@@ -204,17 +204,19 @@ public class ReadWriteNonCoreDataServiceImpl implements ReadWriteNonCoreDataServ
}
@Override
- public List<JsonObject> queryDataTable(String datatable, String columnFilter, String valueFilter, String resultColumns) {
+ public List<JsonObject> queryDataTable(String datatable, final String columnFilter, String valueFilter, String resultColumns) {
Arrays.asList(datatable, columnFilter, valueFilter, resultColumns).forEach(SQLInjectionValidator::validateDynamicQuery);
List<ResultsetColumnHeaderData> resultsetColumnHeaderData = genericDataService.fillResultsetColumnHeaders(datatable);
validateRequestParams(columnFilter, valueFilter, resultColumns, resultsetColumnHeaderData);
+ String filterColumnType = resultsetColumnHeaderData.stream().filter(column -> Objects.equals(columnFilter, column.getColumnName()))
+ .findFirst().map(ResultsetColumnHeaderData::getColumnType).orElse(columnFilter + " does not exist in datatable");
String sql = "select " + resultColumns + " from " + datatable + " where " + columnFilter + " = ?";
SqlRowSet rowSet = null;
- String filterColumnType = resultsetColumnHeaderData.stream().filter(column -> Objects.equals(columnFilter, column.getColumnName()))
- .findFirst().map(ResultsetColumnHeaderData::getColumnType).orElse(columnFilter + " does not exist in datatable");
if (databaseTypeResolver.isPostgreSQL()) {
+ sql = "select " + escapeFieldNames(resultColumns) + " from " + sqlGenerator.escape(datatable) + " where "
+ + escapeFieldNames(columnFilter) + " = ?";
rowSet = callFilteredPgSql(sql, valueFilter, filterColumnType);
} else if (databaseTypeResolver.isMySQL()) {
rowSet = callFilteredMysql(sql, valueFilter, filterColumnType);
@@ -234,6 +236,7 @@ public class ReadWriteNonCoreDataServiceImpl implements ReadWriteNonCoreDataServ
private void extractResults(SqlRowSet rowSet, String[] resultColumnNames, List<JsonObject> results) {
JsonObject json = new JsonObject();
for (String rcn : resultColumnNames) {
+ rcn = rcn.replaceAll("\"", "");
Object rowValue = rowSet.getObject(rcn);
if (rowValue != null) {
if (rowValue instanceof Character) {
@@ -332,6 +335,19 @@ public class ReadWriteNonCoreDataServiceImpl implements ReadWriteNonCoreDataServ
return jdbcTemplate.queryForRowSet(sql, new Object[] { finalValueFilter }, argType);
}
+ private String escapeFieldNames(final String inputFields) {
+ final String delimiter = ",";
+ if (StringUtils.isBlank(inputFields)) {
+ return inputFields;
+ }
+ final String[] fieldList = StringUtils.split(inputFields, delimiter);
+ final String[] outputFields = new String[fieldList.length];
+ for (int i = 0; i < fieldList.length; i++) {
+ outputFields[i] = sqlGenerator.escape(fieldList[i].trim());
+ }
+ return String.join(",", outputFields);
+ }
+
private static void validateRequestParams(String columnFilter, String valueFilter, String resultColumns,
List<ResultsetColumnHeaderData> resultsetColumnHeaderData) {
List<ApiParameterError> paramErrors = new ArrayList<>();
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 cd0bec1df..b3d6b96a8 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
@@ -27,6 +27,7 @@ import com.google.gson.JsonObject;
import java.util.Collections;
import java.util.List;
import org.apache.fineract.infrastructure.core.exception.PlatformApiDataValidationException;
+import org.apache.fineract.infrastructure.core.service.database.DatabaseSpecificSQLGenerator;
import org.apache.fineract.infrastructure.core.service.database.DatabaseTypeResolver;
import org.apache.fineract.infrastructure.dataqueries.data.ResultsetColumnHeaderData;
import org.apache.fineract.infrastructure.security.utils.SQLInjectionException;
@@ -52,6 +53,9 @@ public class ReadWriteNonCoreDataServiceImplTest {
@Mock
private DatabaseTypeResolver databaseTypeResolver;
+ @Mock
+ private DatabaseSpecificSQLGenerator sqlGenerator;
+
@InjectMocks
private ReadWriteNonCoreDataServiceImpl underTest;
@@ -81,6 +85,7 @@ public class ReadWriteNonCoreDataServiceImplTest {
.thenReturn(sqlRS);
when(sqlRS.next()).thenReturn(true).thenReturn(false);
when(sqlRS.getObject(ArgumentMatchers.anyString())).thenReturn("value1").thenReturn("value2");
+ when(sqlGenerator.escape(ArgumentMatchers.anyString())).thenReturn("rc1").thenReturn("rc2").thenReturn("table").thenReturn("cf1");
when(databaseTypeResolver.isPostgreSQL()).thenReturn(true);
ResultsetColumnHeaderData cf1 = ResultsetColumnHeaderData.detailed("cf1", "text", 10L, false, false, null, null, false, false);
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 526bb9469..c8c6c4bc7 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
@@ -158,6 +158,12 @@ public class DatatableHelper extends IntegrationTest {
return Utils.performServerDelete(this.requestSpec, this.responseSpec, deleteEntryUrl, jsonAttributeToGetBack);
}
+ public String runDatatableQuery(final String datatableName, final String columnFilter, final String valueFilter,
+ final String resultColumns) {
+ return Utils.performServerGet(this.requestSpec, this.responseSpec, DATATABLE_URL + "/" + datatableName + "/query" + "?columnFilter="
+ + columnFilter + "&valueFilter=" + valueFilter + "&resultColumns=" + resultColumns + "&" + Utils.TENANT_IDENTIFIER);
+ }
+
public static void verifyDatatableCreatedOnServer(final RequestSpecification requestSpec, final ResponseSpecification responseSpec,
final String generatedDatatableName) {
LOG.info("------------------------------CHECK DATATABLE DETAILS------------------------------------\n");
diff --git a/integration-tests/src/test/java/org/apache/fineract/integrationtests/datatable/DatatableIntegrationTest.java b/integration-tests/src/test/java/org/apache/fineract/integrationtests/datatable/DatatableIntegrationTest.java
index ad8122047..09ca16a77 100644
--- a/integration-tests/src/test/java/org/apache/fineract/integrationtests/datatable/DatatableIntegrationTest.java
+++ b/integration-tests/src/test/java/org/apache/fineract/integrationtests/datatable/DatatableIntegrationTest.java
@@ -309,6 +309,108 @@ public class DatatableIntegrationTest extends IntegrationTest {
assertEquals(datatableName, deletedDataTableName, "ERROR IN DELETING THE DATATABLE");
}
+ @Test
+ public void validateCreateReadDeleteDatatableWithCaseSensitive() throws ParseException {
+
+ // creating datatable for client entity
+ final HashMap<String, Object> columnMap = new HashMap<>();
+ final List<HashMap<String, Object>> datatableColumnsList = new ArrayList<>();
+ columnMap.put("datatableName", Utils.uniqueRandomStringGenerator(CLIENT_APP_TABLE_NAME + "_", 5));
+ columnMap.put("apptableName", CLIENT_APP_TABLE_NAME);
+ columnMap.put("entitySubType", "PERSON");
+ columnMap.put("multiRow", false);
+ String itsADate = "itsADate";
+ String itsADecimal = "itsADecimal";
+ String itsAString = "itsAString";
+ String dateFormat = "dateFormat";
+
+ DatatableHelper.addDatatableColumns(datatableColumnsList, itsADate, "Date", true, null, null);
+ DatatableHelper.addDatatableColumns(datatableColumnsList, itsADecimal, "Decimal", true, null, null);
+ DatatableHelper.addDatatableColumns(datatableColumnsList, itsAString, "String", true, 10, null);
+ columnMap.put("columns", datatableColumnsList);
+ String datatabelRequestJsonString = new Gson().toJson(columnMap);
+ LOG.info("map : {}", datatabelRequestJsonString);
+
+ HashMap<String, Object> datatableResponse = this.datatableHelper.createDatatable(datatabelRequestJsonString, "");
+ String datatableName = (String) datatableResponse.get("resourceIdentifier");
+ DatatableHelper.verifyDatatableCreatedOnServer(this.requestSpec, this.responseSpec, datatableName);
+
+ // creating client with datatables
+ final Integer clientID = ClientHelper.createClientAsPerson(requestSpec, responseSpec);
+
+ // creating new client datatable entry
+ final boolean genericResultSet = true;
+
+ final HashMap<String, Object> datatableEntryMap = new HashMap<>();
+ datatableEntryMap.put(itsADate, Utils.randomDateGenerator("yyyy-MM-dd"));
+ datatableEntryMap.put(itsADecimal, Utils.randomDecimalGenerator(4, 3));
+ datatableEntryMap.put(itsAString, Utils.randomStringGenerator("", 8));
+ datatableEntryMap.put("locale", "en");
+ datatableEntryMap.put(dateFormat, "yyyy-MM-dd");
+
+ String datatabelEntryRequestJsonString = new Gson().toJson(datatableEntryMap);
+ LOG.info("map : {}", datatabelEntryRequestJsonString);
+
+ HashMap<String, Object> datatableEntryResponse = this.datatableHelper.createDatatableEntry(datatableName, clientID,
+ genericResultSet, datatabelEntryRequestJsonString);
+ assertNotNull(datatableEntryResponse.get("resourceId"), "ERROR IN CREATING THE ENTITY DATATABLE RECORD");
+
+ // Read the Datatable entry generated with genericResultSet in true (default)
+ final HashMap<String, Object> items = this.datatableHelper.readDatatableEntry(datatableName, clientID, genericResultSet,
+ (Integer) datatableEntryResponse.get("resourceId"), "");
+ assertNotNull(items);
+ assertEquals(1, ((List) items.get("data")).size());
+
+ assertEquals("client_id", ((Map) ((List) items.get("columnHeaders")).get(0)).get("columnName"));
+ assertEquals(clientID, ((List) ((Map) ((List) items.get("data")).get(0)).get("row")).get(0));
+
+ assertEquals(itsADate, ((Map) ((List) items.get("columnHeaders")).get(1)).get("columnName"));
+ assertEquals(datatableEntryMap.get(itsADate),
+ Utils.arrayDateToString((List) ((List) ((Map) ((List) items.get("data")).get(0)).get("row")).get(1)));
+
+ assertEquals(itsADecimal, ((Map) ((List) items.get("columnHeaders")).get(2)).get("columnName"));
+ assertEquals(datatableEntryMap.get(itsADecimal), ((List) ((Map) ((List) items.get("data")).get(0)).get("row")).get(2));
+
+ assertEquals(itsAString, ((Map) ((List) items.get("columnHeaders")).get(3)).get("columnName"));
+ assertEquals(datatableEntryMap.get(itsAString), ((List) ((Map) ((List) items.get("data")).get(0)).get("row")).get(3));
+
+ // Update datatable entry
+ final String randomValue = Utils.randomStringGenerator("", 8);
+ datatableEntryMap.put(itsADate, Utils.randomDateGenerator("yyyy-MM-dd"));
+ datatableEntryMap.put(itsADecimal, Utils.randomDecimalGenerator(4, 3));
+ datatableEntryMap.put(itsAString, randomValue);
+
+ datatableEntryMap.put("locale", "en");
+ datatableEntryMap.put(dateFormat, "yyyy-MM-dd");
+
+ datatabelEntryRequestJsonString = new Gson().toJson(datatableEntryMap);
+ LOG.info("map : {}", datatabelEntryRequestJsonString);
+
+ HashMap<String, Object> updatedDatatableEntryResponse = this.datatableHelper.updateDatatableEntry(datatableName, clientID, false,
+ datatabelEntryRequestJsonString);
+
+ assertEquals(clientID, updatedDatatableEntryResponse.get("clientId"));
+
+ assertEquals(datatableEntryMap.get(itsADate),
+ Utils.arrayDateToString((List) ((Map) updatedDatatableEntryResponse.get("changes")).get(itsADate)));
+ assertEquals(datatableEntryMap.get(itsADecimal), ((Map) updatedDatatableEntryResponse.get("changes")).get(itsADecimal));
+ assertEquals(datatableEntryMap.get(itsAString), ((Map) updatedDatatableEntryResponse.get("changes")).get(itsAString));
+
+ // Read the datatable with a query
+ LOG.info("query in {} for value : {}", itsAString, randomValue);
+ final String queryResult = this.datatableHelper.runDatatableQuery(datatableName, itsAString, randomValue, "client_id,itsADecimal");
+ assertNotNull(queryResult);
+ LOG.info("query result : {}", queryResult);
+
+ // deleting datatable entries
+ Integer appTableId = this.datatableHelper.deleteDatatableEntries(datatableName, clientID, "clientId");
+ assertEquals(clientID, appTableId, "ERROR IN DELETING THE DATATABLE ENTRIES");
+
+ // deleting the datatable
+ String deletedDataTableName = this.datatableHelper.deleteDatatable(datatableName);
+ assertEquals(datatableName, deletedDataTableName, "ERROR IN DELETING THE DATATABLE");
+ }
+
@Test
public void validateInsertNullValues() {
// Fetch / Create TST code