This is an automated email from the ASF dual-hosted git repository.
madhan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/ranger.git
The following commit(s) were added to refs/heads/master by this push:
new bef8c899e RANGER-4483: added support for NOT_EQUALS in DB queries
bef8c899e is described below
commit bef8c899e6d76574bd09c1c4cd54864b3fce16a9
Author: Madhan Neethiraj <mad...@apache.org>
AuthorDate: Wed Oct 18 12:37:18 2023 -0700
RANGER-4483: added support for NOT_EQUALS in DB queries
---
.../org/apache/ranger/common/RangerSearchUtil.java | 36 ++----
.../org/apache/ranger/common/SearchCriteria.java | 6 +
.../java/org/apache/ranger/common/SearchField.java | 2 +-
.../java/org/apache/ranger/common/SearchUtil.java | 91 ++++++++------
.../apache/ranger/common/TestRangerSearchUtil.java | 134 +++++++++++++++++++++
5 files changed, 206 insertions(+), 63 deletions(-)
diff --git
a/security-admin/src/main/java/org/apache/ranger/common/RangerSearchUtil.java
b/security-admin/src/main/java/org/apache/ranger/common/RangerSearchUtil.java
index e8aab9af5..5ddc965a4 100644
---
a/security-admin/src/main/java/org/apache/ranger/common/RangerSearchUtil.java
+++
b/security-admin/src/main/java/org/apache/ranger/common/RangerSearchUtil.java
@@ -270,9 +270,9 @@ public class RangerSearchUtil extends SearchUtil {
if (intFieldValue != null) {
if (searchField.getCustomCondition() ==
null) {
whereClause.append(" and ")
-
.append(searchField.getFieldName())
- .append("=:")
-
.append(searchField.getClientFieldName());
+
.append(searchField.getFieldName())
+
.append(getSqlOperator(searchField.getSearchType()))
+
.append(":").append(searchField.getClientFieldName());
} else {
whereClause.append(" and
").append(searchField.getCustomCondition());
}
@@ -281,12 +281,9 @@ public class RangerSearchUtil extends SearchUtil {
String strFieldValue =
searchCriteria.getParam(searchField.getClientFieldName());
if (strFieldValue != null) {
if (searchField.getCustomCondition() ==
null) {
- whereClause.append(" and
").append("LOWER(").append(searchField.getFieldName()).append(")");
- if (searchField.getSearchType()
== SearchField.SEARCH_TYPE.FULL) {
- whereClause.append("=
:").append(searchField.getClientFieldName());
- } else {
-
whereClause.append("like :").append(searchField.getClientFieldName());
- }
+ whereClause.append(" and
").append("LOWER(").append(searchField.getFieldName()).append(")")
+
.append(getSqlOperator(searchField.getSearchType()))
+
.append(":").append(searchField.getClientFieldName());
} else {
whereClause.append(" and
").append(searchField.getCustomCondition());
}
@@ -299,9 +296,9 @@ public class RangerSearchUtil extends SearchUtil {
if (boolFieldValue != null) {
if (searchField.getCustomCondition() ==
null) {
whereClause.append(" and ")
-
.append(searchField.getFieldName())
- .append("=:")
-
.append(searchField.getClientFieldName());
+
.append(searchField.getFieldName())
+
.append(getSqlOperator(searchField.getSearchType()))
+
.append(":").append(searchField.getClientFieldName());
} else {
whereClause.append(" and
").append(searchField.getCustomCondition());
}
@@ -312,17 +309,9 @@ public class RangerSearchUtil extends SearchUtil {
null,
searchField.getClientFieldName(), null);
if (fieldValue != null) {
if (searchField.getCustomCondition() ==
null) {
- whereClause.append(" and
").append(searchField.getFieldName());
- if
(SearchField.SEARCH_TYPE.LESS_THAN.equals(searchField.getSearchType())) {
- whereClause.append("<
:");
- } else if
(SearchField.SEARCH_TYPE.LESS_EQUAL_THAN.equals(searchField.getSearchType())) {
- whereClause.append("<=
:");
- } else if
(SearchField.SEARCH_TYPE.GREATER_THAN.equals(searchField.getSearchType())) {
- whereClause.append(">
:");
- } else if
(SearchField.SEARCH_TYPE.GREATER_EQUAL_THAN.equals(searchField.getSearchType()))
{
- whereClause.append(">=
:");
- }
-
whereClause.append(searchField.getClientFieldName());
+ whereClause.append(" and
").append(searchField.getFieldName())
+
.append(getSqlOperator(searchField.getSearchType()))
+
.append(":").append(searchField.getClientFieldName());
} else {
whereClause.append(" and
").append(searchField.getCustomCondition());
}
@@ -441,5 +430,4 @@ public class RangerSearchUtil extends SearchUtil {
}
return null;
}
-
}
diff --git
a/security-admin/src/main/java/org/apache/ranger/common/SearchCriteria.java
b/security-admin/src/main/java/org/apache/ranger/common/SearchCriteria.java
index a919dc482..026e03dbc 100644
--- a/security-admin/src/main/java/org/apache/ranger/common/SearchCriteria.java
+++ b/security-admin/src/main/java/org/apache/ranger/common/SearchCriteria.java
@@ -50,6 +50,12 @@ public class SearchCriteria {
List<SearchGroup> searchGroups = new ArrayList<SearchGroup>();
+ public SearchCriteria() { }
+
+ public SearchCriteria(String paramName, Object paramValue) {
+ addParam(paramName, paramValue);
+ }
+
/**
* @return the startIndex
*/
diff --git
a/security-admin/src/main/java/org/apache/ranger/common/SearchField.java
b/security-admin/src/main/java/org/apache/ranger/common/SearchField.java
index a53a75cc4..e51acc066 100644
--- a/security-admin/src/main/java/org/apache/ranger/common/SearchField.java
+++ b/security-admin/src/main/java/org/apache/ranger/common/SearchField.java
@@ -31,7 +31,7 @@ public class SearchField {
};
public enum SEARCH_TYPE {
- FULL, PARTIAL, LESS_THAN, LESS_EQUAL_THAN, GREATER_THAN,
GREATER_EQUAL_THAN
+ FULL, PARTIAL, LESS_THAN, LESS_EQUAL_THAN, GREATER_THAN,
GREATER_EQUAL_THAN, NOT_EQUALS
};
private String clientFieldName;
diff --git
a/security-admin/src/main/java/org/apache/ranger/common/SearchUtil.java
b/security-admin/src/main/java/org/apache/ranger/common/SearchUtil.java
index 08002413c..8da2b47b6 100644
--- a/security-admin/src/main/java/org/apache/ranger/common/SearchUtil.java
+++ b/security-admin/src/main/java/org/apache/ranger/common/SearchUtil.java
@@ -466,13 +466,8 @@ public class SearchUtil {
if (count > 0) {
whereClause.append(" or ");
}
- whereClause
-
.append(searchField.getFieldName())
-
.append(" = :")
-
.append(searchField
-
.getClientFieldName()
-
+ "_"
-
+ count);
+
whereClause.append(searchField.getFieldName())
+
.append("= :").append(searchField.getClientFieldName() + "_" + count);
}
if (intValueList.size()
> 1) {
@@ -481,10 +476,9 @@ public class SearchUtil {
} else {
whereClause.append("
and ")
-
.append(searchField.getFieldName())
-
.append(" in ( :")
-
.append(searchField.getClientFieldName())
-
.append(")");
+
.append(searchField.getFieldName())
+ .append(" in
")
+ .append("
(:").append(searchField.getClientFieldName()).append(")");
}
} else {
whereClause.append(" and
").append(
@@ -507,9 +501,9 @@ public class SearchUtil {
if (intFieldValue != null) {
if (searchField.getCustomCondition() ==
null) {
whereClause.append(" and ")
-
.append(searchField.getFieldName())
- .append("=:")
-
.append(searchField.getClientFieldName());
+
.append(searchField.getFieldName())
+
.append(getSqlOperator(searchField.getSearchType()))
+
.append(":").append(searchField.getClientFieldName());
} else {
whereClause.append(" and
").append(
searchField.getCustomCondition());
@@ -520,15 +514,9 @@ public class SearchUtil {
.getClientFieldName());
if (strFieldValue != null) {
if (searchField.getCustomCondition() ==
null) {
- whereClause.append(" and
").append("LOWER(")
-
.append(searchField.getFieldName()).append(")");
- if (searchField.getSearchType()
== SearchField.SEARCH_TYPE.FULL) {
- whereClause.append("=
:").append(
-
searchField.getClientFieldName());
- } else {
-
whereClause.append("like :").append(
-
searchField.getClientFieldName());
- }
+ whereClause.append(" and
").append("LOWER(").append(searchField.getFieldName()).append(")")
+
.append(getSqlOperator(searchField.getSearchType()))
+
.append(":").append(searchField.getClientFieldName());
} else {
whereClause.append(" and
").append(
searchField.getCustomCondition());
@@ -541,8 +529,8 @@ public class SearchUtil {
if (searchField.getCustomCondition() ==
null) {
whereClause.append(" and ")
.append(searchField.getFieldName())
- .append("=:")
-
.append(searchField.getClientFieldName());
+
.append(getSqlOperator(searchField.getSearchType()))
+
.append(":").append(searchField.getClientFieldName());
} else {
whereClause.append(" and
").append(
searchField.getCustomCondition());
@@ -553,18 +541,9 @@ public class SearchUtil {
.getClientFieldName());
if (fieldValue != null) {
if (searchField.getCustomCondition() ==
null) {
- whereClause.append(" and
").append(
-
searchField.getFieldName());
- if
(SearchField.SEARCH_TYPE.LESS_THAN.equals(searchField.getSearchType())) {
- whereClause.append("<
:");
- } else if
(SearchField.SEARCH_TYPE.LESS_EQUAL_THAN.equals(searchField.getSearchType())) {
- whereClause.append("<=
:");
- } else if
(SearchField.SEARCH_TYPE.GREATER_THAN.equals(searchField.getSearchType())) {
- whereClause.append(">
:");
- } else if
(SearchField.SEARCH_TYPE.GREATER_EQUAL_THAN.equals(searchField.getSearchType()))
{
- whereClause.append(">=
:");
- }
-
whereClause.append(searchField.getClientFieldName());
+ whereClause.append(" and
").append(searchField.getFieldName())
+
.append(getSqlOperator(searchField.getSearchType()))
+
.append(":").append(searchField.getClientFieldName());
} else {
whereClause.append(" and
").append(
searchField.getCustomCondition());
@@ -768,5 +747,41 @@ public class SearchUtil {
}
return value;
}
-
+
+ public String getSqlOperator(SearchField.SEARCH_TYPE searchType) {
+ final String ret;
+
+ switch (searchType) {
+ case PARTIAL:
+ ret = " like ";
+ break;
+
+ case LESS_THAN:
+ ret = " < ";
+ break;
+
+ case LESS_EQUAL_THAN:
+ ret = " <= ";
+ break;
+
+ case GREATER_THAN:
+ ret = " > ";
+ break;
+
+ case GREATER_EQUAL_THAN:
+ ret = " >= ";
+ break;
+
+ case NOT_EQUALS:
+ ret = " != ";
+ break;
+
+ case FULL:
+ default:
+ ret = " = ";
+ break;
+ }
+
+ return ret;
+ }
}
diff --git
a/security-admin/src/test/java/org/apache/ranger/common/TestRangerSearchUtil.java
b/security-admin/src/test/java/org/apache/ranger/common/TestRangerSearchUtil.java
new file mode 100644
index 000000000..8c73ebbea
--- /dev/null
+++
b/security-admin/src/test/java/org/apache/ranger/common/TestRangerSearchUtil.java
@@ -0,0 +1,134 @@
+/*
+ * 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.ranger.common;
+
+import org.junit.Assert;
+import org.junit.Test;
+
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.List;
+
+public class TestRangerSearchUtil {
+ private final RangerSearchUtil searchUtil = new RangerSearchUtil();
+ private final List<SearchField> searchFields = new ArrayList<>();
+
+ private static final String SEARCH_PARAM_ID = "id";
+ private static final String SEARCH_PARAM_NAME = "name";
+ private static final String SEARCH_PARAM_NAME_CONTAINS =
"nameContains";
+ private static final String SEARCH_PARAM_IS_ENABLED =
"isEnabled";
+ private static final String SEARCH_PARAM_CREATED_TIME =
"createdTime";
+ private static final String SEARCH_PARAM_CREATED_TIME_FROM =
"createdTimeFrom";
+ private static final String SEARCH_PARAM_CREATED_TIME_TO =
"createdTimeTo";
+ private static final String SEARCH_PARAM_EXCLUDE_ID =
"excludeId";
+ private static final String SEARCH_PARAM_EXCLUDE_NAME =
"excludeName";
+
+ private static final String WHERE_PREFIX = "WHERE 1 = 1 ";
+ private static final String WHERE_PREFIX_AND = WHERE_PREFIX + " and ";
+
+ public TestRangerSearchUtil() {
+ searchFields.add(new SearchField(SEARCH_PARAM_ID,
"obj.id", SearchField.DATA_TYPE.INTEGER,
SearchField.SEARCH_TYPE.FULL));
+ searchFields.add(new SearchField(SEARCH_PARAM_NAME,
"obj.name", SearchField.DATA_TYPE.STRING,
SearchField.SEARCH_TYPE.FULL));
+ searchFields.add(new SearchField(SEARCH_PARAM_IS_ENABLED,
"obj.isEnabled", SearchField.DATA_TYPE.BOOLEAN,
SearchField.SEARCH_TYPE.FULL));
+ searchFields.add(new SearchField(SEARCH_PARAM_CREATED_TIME,
"obj.createdTime", SearchField.DATA_TYPE.DATE,
SearchField.SEARCH_TYPE.FULL));
+ searchFields.add(new SearchField(SEARCH_PARAM_NAME_CONTAINS,
"obj.name", SearchField.DATA_TYPE.STRING,
SearchField.SEARCH_TYPE.PARTIAL));
+ searchFields.add(new
SearchField(SEARCH_PARAM_CREATED_TIME_FROM, "obj.createdTime",
SearchField.DATA_TYPE.DATE, SearchField.SEARCH_TYPE.GREATER_EQUAL_THAN));
+ searchFields.add(new SearchField(SEARCH_PARAM_CREATED_TIME_TO,
"obj.createdTime", SearchField.DATA_TYPE.DATE,
SearchField.SEARCH_TYPE.LESS_THAN));
+ searchFields.add(new SearchField(SEARCH_PARAM_EXCLUDE_ID,
"obj.id", SearchField.DATA_TYPE.INTEGER,
SearchField.SEARCH_TYPE.NOT_EQUALS));
+ searchFields.add(new SearchField(SEARCH_PARAM_EXCLUDE_NAME,
"obj.name", SearchField.DATA_TYPE.STRING,
SearchField.SEARCH_TYPE.NOT_EQUALS));
+ }
+
+ @Test
+ public void testEmptyCriteria() {
+ SearchCriteria criteria = new SearchCriteria();
+ String whereClause =
searchUtil.buildWhereClause(criteria, searchFields).toString();
+
+ Assert.assertEquals(WHERE_PREFIX, whereClause);
+ }
+
+ @Test
+ public void testIntEquals() {
+ SearchCriteria criteria = new
SearchCriteria(SEARCH_PARAM_ID, 1);
+ String whereClause =
searchUtil.buildWhereClause(criteria, searchFields).toString();
+
+ Assert.assertEquals(WHERE_PREFIX_AND + "obj.id = :id",
whereClause);
+ }
+
+ @Test
+ public void testStringEquals() {
+ SearchCriteria criteria = new
SearchCriteria(SEARCH_PARAM_NAME, "test-name");
+ String whereClause =
searchUtil.buildWhereClause(criteria, searchFields).toString();
+
+ Assert.assertEquals(WHERE_PREFIX_AND + "LOWER(obj.name) =
:name", whereClause);
+ }
+
+ @Test
+ public void testBooleanEquals() {
+ SearchCriteria criteria = new
SearchCriteria(SEARCH_PARAM_IS_ENABLED, false);
+ String whereClause =
searchUtil.buildWhereClause(criteria, searchFields).toString();
+
+ Assert.assertEquals(WHERE_PREFIX_AND + "obj.isEnabled =
:isEnabled", whereClause);
+ }
+
+ @Test
+ public void testDateEquals() {
+ SearchCriteria criteria = new
SearchCriteria(SEARCH_PARAM_CREATED_TIME, new Date());
+ String whereClause =
searchUtil.buildWhereClause(criteria, searchFields).toString();
+
+ Assert.assertEquals(WHERE_PREFIX_AND + "obj.createdTime =
:createdTime", whereClause);
+ }
+
+ @Test
+ public void testStringContains() {
+ SearchCriteria criteria = new
SearchCriteria(SEARCH_PARAM_NAME_CONTAINS, "test-name");
+ String whereClause =
searchUtil.buildWhereClause(criteria, searchFields).toString();
+
+ Assert.assertEquals(WHERE_PREFIX_AND + "LOWER(obj.name) like
:nameContains", whereClause);
+ }
+
+ @Test
+ public void testDateFrom() {
+ SearchCriteria criteria = new
SearchCriteria(SEARCH_PARAM_CREATED_TIME_FROM, new Date());
+ String whereClause =
searchUtil.buildWhereClause(criteria, searchFields).toString();
+
+ Assert.assertEquals(WHERE_PREFIX_AND + "obj.createdTime >=
:createdTimeFrom", whereClause);
+ }
+
+ @Test
+ public void testDateTo() {
+ SearchCriteria criteria = new
SearchCriteria(SEARCH_PARAM_CREATED_TIME_TO, new Date());
+ String whereClause =
searchUtil.buildWhereClause(criteria, searchFields).toString();
+
+ Assert.assertEquals(WHERE_PREFIX_AND + "obj.createdTime <
:createdTimeTo", whereClause);
+ }
+
+ @Test
+ public void testIntNotEquals() {
+ SearchCriteria criteria = new
SearchCriteria(SEARCH_PARAM_EXCLUDE_ID, 1);
+ String whereClause =
searchUtil.buildWhereClause(criteria, searchFields).toString();
+
+ Assert.assertEquals(WHERE_PREFIX_AND + "obj.id != :excludeId",
whereClause);
+ }
+
+ @Test
+ public void testStringNotEquals() {
+ SearchCriteria criteria = new
SearchCriteria(SEARCH_PARAM_EXCLUDE_NAME, "test-name");
+ String whereClause =
searchUtil.buildWhereClause(criteria, searchFields).toString();
+
+ Assert.assertEquals(WHERE_PREFIX_AND + "LOWER(obj.name) !=
:excludeName", whereClause);
+ }
+}
\ No newline at end of file
