This is an automated email from the ASF dual-hosted git repository. fmartelli pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/syncope.git
commit 427db760a32c9989b7fd2248608286fc43037c9b Author: fmartelli <[email protected]> AuthorDate: Fri Dec 14 10:09:16 2018 +0100 [SYNCOPE-1416] removes null attr views --- .../core/persistence/jpa/dao/JPAAnySearchDAO.java | 89 ++++++++++++++++++---- core/persistence-jpa/src/main/resources/views.xml | 42 ---------- .../core/persistence/jpa/inner/AnySearchTest.java | 23 ++++++ .../src/main/resources/postgres/views.xml | 42 ---------- .../src/main/resources/sqlserver/views.xml | 42 ---------- .../org/apache/syncope/fit/core/SearchITCase.java | 13 +++- 6 files changed, 110 insertions(+), 141 deletions(-) diff --git a/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java b/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java index ee20e8d..11ace3a 100644 --- a/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java +++ b/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java @@ -130,7 +130,10 @@ public class JPAAnySearchDAO extends AbstractAnySearchDAO { Pair<String, Set<String>> filter = getAdminRealmsFilter(adminRealms, svs, parameters); // 1. get the query string from the search condition - StringBuilder queryString = getQuery(buildEffectiveCond(cond, filter.getRight()), parameters, svs); + Pair<StringBuilder, Set<String>> queryInfo = + getQuery(buildEffectiveCond(cond, filter.getRight()), parameters, svs); + + StringBuilder queryString = queryInfo.getLeft(); // 2. take into account administrative realms queryString.insert(0, "SELECT u.any_id FROM ("); @@ -164,16 +167,19 @@ public class JPAAnySearchDAO extends AbstractAnySearchDAO { Pair<String, Set<String>> filter = getAdminRealmsFilter(adminRealms, svs, parameters); // 1. get the query string from the search condition - StringBuilder queryString = getQuery(buildEffectiveCond(cond, filter.getRight()), parameters, svs); + Pair<StringBuilder, Set<String>> queryInfo = getQuery(buildEffectiveCond(cond, filter.getRight()), + parameters, svs); + + StringBuilder queryString = queryInfo.getLeft(); // 2. take into account realms and ordering OrderBySupport obs = parseOrderBy(kind, svs, orderBy); if (queryString.charAt(0) == '(') { queryString.insert(0, buildSelect(obs)); - queryString.append(buildWhere(svs, obs)); + queryString.append(buildWhere(svs, queryInfo.getRight(), obs)); } else { queryString.insert(0, buildSelect(obs).append('(')); - queryString.append(')').append(buildWhere(svs, obs)); + queryString.append(')').append(buildWhere(svs, queryInfo.getRight(), obs)); } queryString. append(filter.getLeft()). @@ -233,14 +239,49 @@ public class JPAAnySearchDAO extends AbstractAnySearchDAO { return select; } - protected void processOBS(final SearchSupport svs, final OrderBySupport obs, final StringBuilder where) { + protected void processOBS( + final SearchSupport svs, + final Set<String> involvedPlainAttrs, + final OrderBySupport obs, + final StringBuilder where) { + + Set<String> attrs = new HashSet<>(involvedPlainAttrs); + for (OrderBySupport.Item item : obs.items) { + attrs.add(item.orderBy.substring(0, item.orderBy.indexOf(" "))); + } + obs.views.forEach(searchView -> { where.append(','); if (searchView.name.equals(svs.asSearchViewSupport().attr().name)) { + StringBuilder attrWhere = new StringBuilder(); + StringBuilder nullAttrWhere = new StringBuilder(); + where.append(" (SELECT * FROM ").append(searchView.name); if (svs.nonMandatorySchemas || obs.nonMandatorySchemas) { - where.append(" UNION SELECT * FROM ").append(svs.asSearchViewSupport().nullAttr().name); + attrs.forEach(field -> { + if (attrWhere.length() == 0) { + attrWhere.append(" WHERE schema_id='").append(field).append("'"); + } else { + attrWhere.append(" OR ").append("schema_id='").append(field).append("'"); + } + + nullAttrWhere.append(" UNION SELECT any_id, "). + append("'"). + append(field). + append("' AS schema_id, "). + append("null AS booleanvalue, "). + append("null AS datevalue, "). + append("null AS doublevalue, "). + append("null AS longvalue, "). + append("null AS stringvalue FROM ").append(svs.field().name). + append(" WHERE "). + append("any_id NOT IN ("). + append("SELECT any_id FROM "). + append(svs.asSearchViewSupport().attr().name).append(' ').append(searchView.alias). + append(" WHERE ").append("schema_id='").append(field).append("')"); + }); + where.append(attrWhere).append(nullAttrWhere); } where.append(')'); @@ -251,9 +292,10 @@ public class JPAAnySearchDAO extends AbstractAnySearchDAO { }); } - private StringBuilder buildWhere(final SearchSupport svs, final OrderBySupport obs) { + private StringBuilder buildWhere( + final SearchSupport svs, final Set<String> involvedPlainAttrs, final OrderBySupport obs) { StringBuilder where = new StringBuilder(" u"); - processOBS(svs, obs, where); + processOBS(svs, involvedPlainAttrs, obs, where); where.append(" WHERE "); obs.views.forEach(searchView -> { where.append("u.any_id=").append(searchView.alias).append(".any_id AND "); @@ -375,8 +417,10 @@ public class JPAAnySearchDAO extends AbstractAnySearchDAO { return obs; } - private StringBuilder getQuery(final SearchCond cond, final List<Object> parameters, final SearchSupport svs) { + private Pair<StringBuilder, Set<String>> getQuery( + final SearchCond cond, final List<Object> parameters, final SearchSupport svs) { StringBuilder query = new StringBuilder(); + Set<String> involvedAttributes = new HashSet<>(); switch (cond.getType()) { case LEAF: @@ -419,6 +463,11 @@ public class JPAAnySearchDAO extends AbstractAnySearchDAO { } else if (cond.getAttributeCond() != null) { query.append(getQuery(cond.getAttributeCond(), cond.getType() == SearchCond.Type.NOT_LEAF, parameters, svs)); + try { + involvedAttributes.add(check(cond.getAttributeCond(), svs.anyTypeKind).getLeft().getKey()); + } catch (IllegalArgumentException e) { + // ignore + } } else if (cond.getAnyCond() != null) { query.append(getQuery(cond.getAnyCond(), cond.getType() == SearchCond.Type.NOT_LEAF, parameters, svs)); @@ -426,29 +475,41 @@ public class JPAAnySearchDAO extends AbstractAnySearchDAO { break; case AND: - String andSubQuery = getQuery(cond.getLeftSearchCond(), parameters, svs).toString(); + Pair<StringBuilder, Set<String>> leftAndInfo = getQuery(cond.getLeftSearchCond(), parameters, svs); + involvedAttributes.addAll(leftAndInfo.getRight()); + + Pair<StringBuilder, Set<String>> rigthAndInfo = getQuery(cond.getRightSearchCond(), parameters, svs); + involvedAttributes.addAll(rigthAndInfo.getRight()); + + String andSubQuery = leftAndInfo.getKey().toString(); // Add extra parentheses andSubQuery = andSubQuery.replaceFirst("WHERE ", "WHERE ("); query.append(andSubQuery). append(" AND any_id IN ( "). - append(getQuery(cond.getRightSearchCond(), parameters, svs)). + append(rigthAndInfo.getKey()). append("))"); break; case OR: - String orSubQuery = getQuery(cond.getLeftSearchCond(), parameters, svs).toString(); + Pair<StringBuilder, Set<String>> leftOrInfo = getQuery(cond.getLeftSearchCond(), parameters, svs); + involvedAttributes.addAll(leftOrInfo.getRight()); + + Pair<StringBuilder, Set<String>> rigthOrInfo = getQuery(cond.getRightSearchCond(), parameters, svs); + involvedAttributes.addAll(rigthOrInfo.getRight()); + + String orSubQuery = leftOrInfo.getKey().toString(); // Add extra parentheses orSubQuery = orSubQuery.replaceFirst("WHERE ", "WHERE ("); query.append(orSubQuery). append(" OR any_id IN ( "). - append(getQuery(cond.getRightSearchCond(), parameters, svs)). + append(rigthOrInfo.getKey()). append("))"); break; default: } - return query; + return Pair.of(query, involvedAttributes); } protected String getQuery( diff --git a/core/persistence-jpa/src/main/resources/views.xml b/core/persistence-jpa/src/main/resources/views.xml index e8e9a21..f1e8a60 100644 --- a/core/persistence-jpa/src/main/resources/views.xml +++ b/core/persistence-jpa/src/main/resources/views.xml @@ -78,20 +78,6 @@ under the License. FROM UPlainAttrValue uav, UPlainAttr ua WHERE uav.attribute_id = ua.id </entry> - <entry key="user_search_null_attr"> - CREATE VIEW user_search_null_attr AS - - SELECT u.id AS any_id, - PlainSchema.id AS schema_id, - NULL AS booleanvalue, - NULL AS datevalue, - NULL AS doublevalue, - NULL AS longvalue, - NULL AS stringvalue - FROM SyncopeUser u CROSS JOIN PlainSchema - LEFT OUTER JOIN UPlainAttr ua ON (PlainSchema.id = ua.schema_id AND ua.owner_id = u.id) - WHERE ua.id IS NULL - </entry> <entry key="user_search_urelationship"> CREATE VIEW user_search_urelationship AS @@ -171,20 +157,6 @@ under the License. FROM APlainAttrValue uav, APlainAttr ua WHERE uav.attribute_id = ua.id </entry> - <entry key="anyObject_search_null_attr"> - CREATE VIEW anyObject_search_null_attr AS - - SELECT u.id AS any_id, - PlainSchema.id AS schema_id, - NULL AS booleanvalue, - NULL AS datevalue, - NULL AS doublevalue, - NULL AS longvalue, - NULL AS stringvalue - FROM AnyObject u CROSS JOIN PlainSchema - LEFT OUTER JOIN APlainAttr ua ON (PlainSchema.id = ua.schema_id AND ua.owner_id = u.id) - WHERE ua.id IS NULL - </entry> <entry key="anyObject_search_arelationship"> CREATE VIEW anyObject_search_arelationship AS @@ -244,20 +216,6 @@ under the License. FROM GPlainAttrValue uav, GPlainAttr ua WHERE uav.attribute_id = ua.id </entry> - <entry key="group_search_null_attr"> - CREATE VIEW group_search_null_attr AS - - SELECT u.id AS any_id, - PlainSchema.id AS schema_id, - NULL AS booleanvalue, - NULL AS datevalue, - NULL AS doublevalue, - NULL AS longvalue, - NULL AS stringvalue - FROM SyncopeGroup u CROSS JOIN PlainSchema - LEFT OUTER JOIN GPlainAttr ua ON (PlainSchema.id = ua.schema_id AND ua.owner_id = u.id) - WHERE ua.id IS NULL - </entry> <entry key="group_search_resource"> CREATE VIEW group_search_resource AS diff --git a/core/persistence-jpa/src/test/java/org/apache/syncope/core/persistence/jpa/inner/AnySearchTest.java b/core/persistence-jpa/src/test/java/org/apache/syncope/core/persistence/jpa/inner/AnySearchTest.java index 2c2f4d1..43cc944 100644 --- a/core/persistence-jpa/src/test/java/org/apache/syncope/core/persistence/jpa/inner/AnySearchTest.java +++ b/core/persistence-jpa/src/test/java/org/apache/syncope/core/persistence/jpa/inner/AnySearchTest.java @@ -747,4 +747,27 @@ public class AnySearchTest extends AbstractTest { AnyTypeKind.USER); assertFalse(users.isEmpty()); } + + @Test + public void issueSYNCOPE1416() { + AttributeCond idLeftCond = new AttributeCond(AttributeCond.Type.ISNOTNULL); + idLeftCond.setSchema("surname"); + + AttributeCond idRightCond = new AttributeCond(AttributeCond.Type.ISNOTNULL); + idRightCond.setSchema("firstname"); + + SearchCond searchCondition = SearchCond.getAndCond( + SearchCond.getLeafCond(idLeftCond), SearchCond.getLeafCond(idRightCond)); + + List<OrderByClause> orderByClauses = new ArrayList<>(); + OrderByClause orderByClause = new OrderByClause(); + orderByClause.setField("ctype"); + orderByClause.setDirection(OrderByClause.Direction.ASC); + orderByClauses.add(orderByClause); + + List<User> users = searchDAO.search(searchCondition, orderByClauses, AnyTypeKind.USER); + assertEquals( + searchDAO.count(SyncopeConstants.FULL_ADMIN_REALMS, searchCondition, AnyTypeKind.USER), + users.size()); + } } diff --git a/fit/core-reference/src/main/resources/postgres/views.xml b/fit/core-reference/src/main/resources/postgres/views.xml index b6664c0..529901e 100644 --- a/fit/core-reference/src/main/resources/postgres/views.xml +++ b/fit/core-reference/src/main/resources/postgres/views.xml @@ -78,20 +78,6 @@ under the License. FROM UPlainAttrValue uav, UPlainAttr ua WHERE uav.attribute_id = ua.id </entry> - <entry key="user_search_null_attr"> - CREATE VIEW user_search_null_attr AS - - SELECT u.id AS any_id, - PlainSchema.id AS schema_id, - NULL::int4 AS booleanvalue, - NULL::timestamp AS datevalue, - NULL::float8 AS doublevalue, - NULL::int8 AS longvalue, - NULL AS stringvalue - FROM SyncopeUser u CROSS JOIN PlainSchema - LEFT OUTER JOIN UPlainAttr ua ON (PlainSchema.id = ua.schema_id AND ua.owner_id = u.id) - WHERE ua.id IS NULL - </entry> <entry key="user_search_urelationship"> CREATE VIEW user_search_urelationship AS @@ -171,20 +157,6 @@ under the License. FROM APlainAttrValue uav, APlainAttr ua WHERE uav.attribute_id = ua.id </entry> - <entry key="anyObject_search_null_attr"> - CREATE VIEW anyObject_search_null_attr AS - - SELECT u.id AS any_id, - PlainSchema.id AS schema_id, - NULL::int4 AS booleanvalue, - NULL::timestamp AS datevalue, - NULL::float8 AS doublevalue, - NULL::int8 AS longvalue, - NULL AS stringvalue - FROM AnyObject u CROSS JOIN PlainSchema - LEFT OUTER JOIN APlainAttr ua ON (PlainSchema.id = ua.schema_id AND ua.owner_id = u.id) - WHERE ua.id IS NULL - </entry> <entry key="anyObject_search_arelationship"> CREATE VIEW anyObject_search_arelationship AS @@ -244,20 +216,6 @@ under the License. FROM GPlainAttrValue uav, GPlainAttr ua WHERE uav.attribute_id = ua.id </entry> - <entry key="group_search_null_attr"> - CREATE VIEW group_search_null_attr AS - - SELECT u.id AS any_id, - PlainSchema.id AS schema_id, - NULL::int4 AS booleanvalue, - NULL::timestamp AS datevalue, - NULL::float8 AS doublevalue, - NULL::int8 AS longvalue, - NULL AS stringvalue - FROM SyncopeGroup u CROSS JOIN PlainSchema - LEFT OUTER JOIN GPlainAttr ua ON (PlainSchema.id = ua.schema_id AND ua.owner_id = u.id) - WHERE ua.id IS NULL - </entry> <entry key="group_search_resource"> CREATE VIEW group_search_resource AS diff --git a/fit/core-reference/src/main/resources/sqlserver/views.xml b/fit/core-reference/src/main/resources/sqlserver/views.xml index ab6be98..529901e 100644 --- a/fit/core-reference/src/main/resources/sqlserver/views.xml +++ b/fit/core-reference/src/main/resources/sqlserver/views.xml @@ -78,20 +78,6 @@ under the License. FROM UPlainAttrValue uav, UPlainAttr ua WHERE uav.attribute_id = ua.id </entry> - <entry key="user_search_null_attr"> - CREATE VIEW user_search_null_attr AS - - SELECT u.id AS any_id, - PlainSchema.id AS schema_id, - NULL AS booleanvalue, - CAST (NULL AS DATETIME2) datevalue, - CAST (NULL AS FLOAT) doublevalue, - CAST (NULL AS BIGINT) longvalue, - CAST (NULL AS VARCHAR(255)) AS stringvalue - FROM SyncopeUser u CROSS JOIN PlainSchema - LEFT OUTER JOIN UPlainAttr ua ON (PlainSchema.id = ua.schema_id AND ua.owner_id = u.id) - WHERE ua.id IS NULL - </entry> <entry key="user_search_urelationship"> CREATE VIEW user_search_urelationship AS @@ -171,20 +157,6 @@ under the License. FROM APlainAttrValue uav, APlainAttr ua WHERE uav.attribute_id = ua.id </entry> - <entry key="anyObject_search_null_attr"> - CREATE VIEW anyObject_search_null_attr AS - - SELECT u.id AS any_id, - PlainSchema.id AS schema_id, - NULL AS booleanvalue, - CAST (NULL AS DATETIME2) datevalue, - CAST (NULL AS FLOAT) doublevalue, - CAST (NULL AS BIGINT) longvalue, - CAST (NULL AS VARCHAR(255)) AS stringvalue - FROM AnyObject u CROSS JOIN PlainSchema - LEFT OUTER JOIN APlainAttr ua ON (PlainSchema.id = ua.schema_id AND ua.owner_id = u.id) - WHERE ua.id IS NULL - </entry> <entry key="anyObject_search_arelationship"> CREATE VIEW anyObject_search_arelationship AS @@ -244,20 +216,6 @@ under the License. FROM GPlainAttrValue uav, GPlainAttr ua WHERE uav.attribute_id = ua.id </entry> - <entry key="group_search_null_attr"> - CREATE VIEW group_search_null_attr AS - - SELECT u.id AS any_id, - PlainSchema.id AS schema_id, - NULL AS booleanvalue, - CAST (NULL AS DATETIME2) datevalue, - CAST (NULL AS FLOAT) doublevalue, - CAST (NULL AS BIGINT) longvalue, - CAST (NULL AS VARCHAR(255)) AS stringvalue - FROM SyncopeGroup u CROSS JOIN PlainSchema - LEFT OUTER JOIN GPlainAttr ua ON (PlainSchema.id = ua.schema_id AND ua.owner_id = u.id) - WHERE ua.id IS NULL - </entry> <entry key="group_search_resource"> CREATE VIEW group_search_resource AS diff --git a/fit/core-reference/src/test/java/org/apache/syncope/fit/core/SearchITCase.java b/fit/core-reference/src/test/java/org/apache/syncope/fit/core/SearchITCase.java index f11de53..449c24d 100644 --- a/fit/core-reference/src/test/java/org/apache/syncope/fit/core/SearchITCase.java +++ b/fit/core-reference/src/test/java/org/apache/syncope/fit/core/SearchITCase.java @@ -32,11 +32,11 @@ import org.apache.syncope.common.lib.SyncopeClientException; import org.apache.syncope.common.lib.SyncopeConstants; import org.apache.syncope.common.lib.request.AnyObjectCR; import org.apache.syncope.common.lib.request.AnyObjectUR; -import org.apache.syncope.common.lib.request.AttrPatch; import org.apache.syncope.common.lib.request.GroupCR; import org.apache.syncope.common.lib.request.MembershipUR; import org.apache.syncope.common.lib.request.UserCR; import org.apache.syncope.common.lib.request.UserUR; +import org.apache.syncope.common.lib.patch.AttrPatch; import org.apache.syncope.common.lib.to.AnyObjectTO; import org.apache.syncope.common.lib.to.AnyTypeTO; import org.apache.syncope.common.lib.to.PagedResult; @@ -290,6 +290,17 @@ public class SearchITCase extends AbstractITCase { and("username").equalTo("bellini").query()). build()); assertEquals(users, issueSYNCOPE1321); + + // SYNCOPE-1416 (check the search for attributes of type different from stringvalue) + PagedResult<UserTO> issueSYNCOPE1416 = userService.search(new AnyQuery.Builder(). + realm(SyncopeConstants.ROOT_REALM). + fiql(SyncopeClient.getUserSearchConditionBuilder(). + is("loginDate").lexicalNotBefore("2009-05-26"). + and("username").equalTo("rossini").query()). + orderBy(SyncopeClient.getOrderByClauseBuilder().asc("loginDate").build()). + build()); + assertEquals(1, issueSYNCOPE1416.getSize()); + assertEquals("rossini", issueSYNCOPE1416.getResult().get(0).getUsername()); } @Test
