This is an automated email from the ASF dual-hosted git repository.
ilgrosso pushed a commit to branch 2_1_X
in repository https://gitbox.apache.org/repos/asf/syncope.git
The following commit(s) were added to refs/heads/2_1_X by this push:
new ffbe1fc [SYNCOPE-1609] Changes jsonb queries in order to improve
performances (#233)
ffbe1fc is described below
commit ffbe1fc8c3b607f2c21f059aadbaf59be470d3f9
Author: Fabio Martelli <[email protected]>
AuthorDate: Tue Jan 12 09:03:45 2021 +0100
[SYNCOPE-1609] Changes jsonb queries in order to improve performances (#233)
---
.../persistence/jpa/dao/PGJPAJSONAnySearchDAO.java | 1103 ++++++++++++++++++--
.../jpa/dao/PGJPAJSONPlainSchemaDAO.java | 2 +-
.../src/main/resources/pgjsonb/views.xml | 7 -
.../resources/docker-compose/docker-compose-ha.yml | 2 +-
.../docker-compose/docker-compose-pgjsonb.yml | 2 +-
.../docker-compose/docker-compose-postgresql.yml | 2 +-
pom.xml | 2 +-
7 files changed, 996 insertions(+), 124 deletions(-)
diff --git
a/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/PGJPAJSONAnySearchDAO.java
b/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/PGJPAJSONAnySearchDAO.java
index 421a519..2de1f54 100644
---
a/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/PGJPAJSONAnySearchDAO.java
+++
b/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/PGJPAJSONAnySearchDAO.java
@@ -19,75 +19,53 @@
package org.apache.syncope.core.persistence.jpa.dao;
import java.text.ParseException;
-import java.util.Arrays;
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.Date;
+import java.util.HashSet;
import java.util.List;
+import java.util.Optional;
import java.util.Set;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
import java.util.stream.Collectors;
+import javax.persistence.Query;
+import javax.persistence.TemporalType;
+import org.apache.commons.lang3.ArrayUtils;
+import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.Pair;
+import org.apache.commons.lang3.tuple.Triple;
+import org.apache.syncope.common.lib.SyncopeClientException;
+import org.apache.syncope.common.lib.types.AnyTypeKind;
import org.apache.syncope.common.lib.types.AttrSchemaType;
+import org.apache.syncope.common.lib.types.ClientExceptionType;
import org.apache.syncope.core.persistence.api.dao.search.AnyCond;
+import org.apache.syncope.core.persistence.api.dao.search.AnyTypeCond;
+import org.apache.syncope.core.persistence.api.dao.search.AssignableCond;
import org.apache.syncope.core.persistence.api.dao.search.AttrCond;
+import org.apache.syncope.core.persistence.api.dao.search.DynRealmCond;
+import org.apache.syncope.core.persistence.api.dao.search.MemberCond;
+import org.apache.syncope.core.persistence.api.dao.search.MembershipCond;
import org.apache.syncope.core.persistence.api.dao.search.OrderByClause;
+import org.apache.syncope.core.persistence.api.dao.search.PrivilegeCond;
+import org.apache.syncope.core.persistence.api.dao.search.RelationshipCond;
+import org.apache.syncope.core.persistence.api.dao.search.RelationshipTypeCond;
+import org.apache.syncope.core.persistence.api.dao.search.ResourceCond;
+import org.apache.syncope.core.persistence.api.dao.search.RoleCond;
+import org.apache.syncope.core.persistence.api.dao.search.SearchCond;
+import org.apache.syncope.core.persistence.api.entity.Any;
import org.apache.syncope.core.persistence.api.entity.AnyUtils;
-import org.apache.syncope.core.persistence.api.entity.PlainAttr;
-import org.apache.syncope.core.persistence.api.entity.PlainAttrUniqueValue;
+import org.apache.syncope.core.persistence.api.entity.DynRealm;
+import org.apache.syncope.core.persistence.api.entity.Entity;
import org.apache.syncope.core.persistence.api.entity.PlainAttrValue;
import org.apache.syncope.core.persistence.api.entity.PlainSchema;
-import org.apache.syncope.core.provisioning.api.serialization.POJOHelper;
-import org.apache.syncope.core.persistence.api.entity.JSONPlainAttr;
+import org.apache.syncope.core.persistence.api.entity.Realm;
import org.apache.syncope.core.provisioning.api.utils.FormatUtils;
+import org.apache.syncope.core.provisioning.api.utils.RealmUtils;
public class PGJPAJSONAnySearchDAO extends AbstractJPAJSONAnySearchDAO {
- @Override
- protected void processOBS(
- final SearchSupport svs,
- final Set<String> involvedPlainAttrs,
- final OrderBySupport obs,
- final StringBuilder where) {
-
- Set<String> attrs = obs.items.stream().
- map(item -> item.orderBy.substring(0, item.orderBy.indexOf("
"))).collect(Collectors.toSet());
-
- obs.views.forEach(searchView -> {
- if (searchView.name.equals(svs.field().name)) {
- StringBuilder attrWhere = new StringBuilder();
- StringBuilder nullAttrWhere = new StringBuilder();
-
- where.append(", (SELECT * FROM ").append(searchView.name);
-
- if (svs.nonMandatorySchemas || obs.nonMandatorySchemas) {
- attrs.forEach(field -> {
- if (attrWhere.length() == 0) {
- attrWhere.append(" WHERE ");
- } else {
- attrWhere.append(" OR ");
- }
- attrWhere.append("plainAttrs @>
'[{\"schema\":\"").append(field).append("\"}]'::jsonb");
-
- nullAttrWhere.append(" UNION SELECT DISTINCT
any_id,").append(svs.table().alias).append(".*, ").
- append("'{\"schema\": \"").
- append(field).
- append("\"}'::jsonb as attrs, '{}'::jsonb as
attrValues").
- append(" FROM
").append(svs.table().name).append(" ").append(svs.table().alias).
- append(", ").append(svs.field().name).
- append(" WHERE ").
- append("any_id NOT IN ").
- append("(SELECT distinct any_id FROM ").
- append(svs.field().name).
- append(" WHERE
").append(svs.table().alias).append(".id=any_id AND ").
- append("plainAttrs @>
'[{\"schema\":\"").append(field).append("\"}]'::jsonb)");
- });
- where.append(attrWhere).append(nullAttrWhere);
- }
-
- where.append(')');
- } else {
- where.append(',').append(searchView.name);
- }
- where.append(' ').append(searchView.alias);
- });
- }
+ protected static final String ALWAYS_FALSE_ASSERTION = "1=2";
@Override
protected void parseOrderByForPlainSchema(
@@ -101,14 +79,14 @@ public class PGJPAJSONAnySearchDAO extends
AbstractJPAJSONAnySearchDAO {
// keep track of involvement of non-mandatory schemas in the order by
clauses
obs.nonMandatorySchemas =
!"true".equals(schema.getMandatoryCondition());
- obs.views.add(svs.field());
+ obs.views.add(svs.table());
- item.select = svs.field().alias + ".attrValues ->> '" +
key(schema.getType()) + "' AS " + fieldName;
- item.where = "attrs ->> 'schema' = '" + fieldName + "'";
+ item.select = fieldName + " -> 0 AS " + fieldName;
+ item.where = StringUtils.EMPTY;
item.orderBy = fieldName + " " + clause.getDirection().name();
}
- private void fillAttrQuery(
+ protected void fillAttrQuery(
final AnyUtils anyUtils,
final StringBuilder query,
final PlainAttrValue attrValue,
@@ -119,53 +97,118 @@ public class PGJPAJSONAnySearchDAO extends
AbstractJPAJSONAnySearchDAO {
final SearchSupport svs) {
// This first branch is required for handling with not conditions
given on multivalue fields (SYNCOPE-1419)
- if (not && schema.isMultivalue()
- && !(cond instanceof AnyCond)
- && cond.getType() != AttrCond.Type.ISNULL && cond.getType() !=
AttrCond.Type.ISNOTNULL) {
-
- query.append("id NOT IN (SELECT DISTINCT any_id FROM ");
- query.append(svs.field().name).append(" WHERE ");
+ if (not && !(cond instanceof AnyCond)) {
+ query.append("NOT (");
fillAttrQuery(anyUtils, query, attrValue, schema, cond, false,
parameters, svs);
query.append(")");
+ } else if (not && cond.getType() == AttrCond.Type.ISNULL) {
+ cond.setType(AttrCond.Type.ISNOTNULL);
+ fillAttrQuery(anyUtils, query, attrValue, schema, cond, true,
parameters, svs);
} else {
- if (!not && cond.getType() == AttrCond.Type.EQ) {
- PlainAttr<?> container = anyUtils.newPlainAttr();
- container.setSchema(schema);
- if (attrValue instanceof PlainAttrUniqueValue) {
- container.setUniqueValue((PlainAttrUniqueValue) attrValue);
- } else {
- ((JSONPlainAttr) container).add(attrValue);
+ String key = key(schema.getType());
+
+ String value = cond.getExpression();
+ if (schema.getType() == AttrSchemaType.Date) {
+ try {
+ value =
String.valueOf(FormatUtils.parseDate(value).getTime());
+ } catch (ParseException e) {
+ LOG.error("Could not parse {} as date", value, e);
}
+ }
- query.append("plainAttrs @> '").
-
append(POJOHelper.serialize(Arrays.asList(container)).replace("'", "''")).
- append("'::jsonb");
+ boolean isStr = true;
+ boolean lower;
+ if (schema.getType() == AttrSchemaType.String || schema.getType()
== AttrSchemaType.Enum) {
+ lower = (cond.getType() == AttrCond.Type.IEQ || cond.getType()
== AttrCond.Type.ILIKE);
} else {
- String key = key(schema.getType());
- boolean lower = (schema.getType() == AttrSchemaType.String ||
schema.getType() == AttrSchemaType.Enum)
- && (cond.getType() == AttrCond.Type.IEQ ||
cond.getType() == AttrCond.Type.ILIKE);
-
- query.append("attrs ->> 'schema' =
?").append(setParameter(parameters, cond.getSchema())).
- append(" AND ").
- append(lower ? "LOWER(" : "").
- append(schema.isUniqueConstraint()
- ? "attrs -> 'uniqueValue'" : "attrValues").
- append(" ->> '").append(key).append("'").
- append(lower ? ")" : "");
-
- appendOp(query, cond.getType(), not);
-
- String value = cond.getExpression();
- if (schema.getType() == AttrSchemaType.Date) {
- try {
- value =
String.valueOf(FormatUtils.parseDate(value).getTime());
- } catch (ParseException e) {
- LOG.error("Could not parse {} as date", value, e);
+ lower = false;
+ try {
+ switch (schema.getType()) {
+ case Date:
+ case Long:
+ Long.parseLong(value);
+ break;
+ case Double:
+ Double.parseDouble(value);
+ break;
+ case Boolean:
+ if (!("true".equalsIgnoreCase(value)
+ || "false".equalsIgnoreCase(value))) {
+ throw new IllegalArgumentException();
+ }
+ break;
+ default:
}
+
+ isStr = false;
+ } catch (Exception nfe) {
+ // ignore}
}
- query.append(lower ? "LOWER(" : "").
- append("?").append(setParameter(parameters, value)).
- append(lower ? ")" : "");
+ }
+
+ switch (cond.getType()) {
+
+ case ISNULL:
+ // shouldn't occour: processed before
+ break;
+
+ case ISNOTNULL:
+
query.append("jsonb_path_exists(").append(schema.getKey()).append(", '$[*]')");
+ break;
+
+ case ILIKE:
+ case LIKE:
+ // jsonb_path_exists(Nome, '$[*] ? (@.stringValue
like_regex "EL.*" flag "i")')
+ if (schema.getType() == AttrSchemaType.String ||
schema.getType() == AttrSchemaType.Enum) {
+
query.append("jsonb_path_exists(").append(schema.getKey()).append(", '$[*] ? ").
+ append("(@.").append(key).append(" like_regex
\"").
+ append(value.replaceAll("%", ".*")).
+ append("\"").
+ append(lower ? " flag \"i\"" :
"").append(")')");
+ } else {
+ query.append(" 1=2");
+ LOG.error("LIKE is only compatible with string or enum
schemas");
+ }
+ break;
+
+ case IEQ:
+ case EQ:
+
query.append("jsonb_path_exists(").append(schema.getKey()).append(", '$[*] ? ").
+ append("(@.").append(key);
+ if (isStr) {
+ query.append(" like_regex
\"").append(value).append("\"");
+ } else {
+ query.append(" == ").append(value);
+ }
+
+ query.append(lower ? " flag \"i\"" : "").append(")')");
+ break;
+
+ case GE:
+
query.append("jsonb_path_exists(").append(schema.getKey()).append(", '$[*] ? ").
+ append("(@.").append(key).append(" >= ").
+ append(value).append(")')");
+ break;
+
+ case GT:
+
query.append("jsonb_path_exists(").append(schema.getKey()).append(", '$[*] ? ").
+ append("(@.").append(key).append(" > ").
+ append(value).append(")')");
+ break;
+
+ case LE:
+
query.append("jsonb_path_exists(").append(schema.getKey()).append(", '$[*] ? ").
+ append("(@.").append(key).append(" <= ").
+ append(value).append(")')");
+ break;
+
+ case LT:
+
query.append("jsonb_path_exists(").append(schema.getKey()).append(", '$[*] ? ").
+ append("(@.").append(key).append(" < ").
+ append(value).append(")')");
+ break;
+
+ default:
}
}
}
@@ -181,7 +224,7 @@ public class PGJPAJSONAnySearchDAO extends
AbstractJPAJSONAnySearchDAO {
try {
checked = check(cond, svs.anyTypeKind);
} catch (IllegalArgumentException e) {
- return EMPTY_QUERY;
+ return ALWAYS_FALSE_ASSERTION;
}
// normalize NULL / NOT NULL checks
@@ -193,32 +236,868 @@ public class PGJPAJSONAnySearchDAO extends
AbstractJPAJSONAnySearchDAO {
}
}
- StringBuilder query =
- new StringBuilder("SELECT DISTINCT any_id FROM
").append(svs.field().name).append(" WHERE ");
+ StringBuilder query = new StringBuilder();
+
switch (cond.getType()) {
case ISNOTNULL:
- query.append("plainAttrs @> '[{\"schema\":\"").
- append(checked.getLeft().getKey()).
- append("\"}]'::jsonb");
+ query.append(not ? " NOT " : " ").
+
append("jsonb_path_exists(").append(checked.getLeft().getKey()).append(",'$[*]')");
break;
case ISNULL:
- query.append("any_id NOT IN (").
- append("SELECT any_id FROM ").append(svs.field().name).
- append(" WHERE plainAttrs @> '[{\"schema\":\"").
- append(checked.getLeft().getKey()).
- append("\"}]'::jsonb)");
+ query.append(not ? " " : " NOT ").
+
append("jsonb_path_exists(").append(checked.getLeft().getKey()).append(",'$[*]')");
break;
default:
- if (not && !(cond instanceof AnyCond) &&
checked.getLeft().isMultivalue()) {
- query = new StringBuilder("SELECT DISTINCT id AS any_id
FROM ").append(svs.table().name).
- append(" WHERE ");
- }
fillAttrQuery(anyUtilsFactory.getInstance(svs.anyTypeKind),
query, checked.getRight(), checked.getLeft(), cond,
not, parameters, svs);
}
return query.toString();
}
+
+ @Override
+ protected String getQuery(
+ final AnyTypeCond cond,
+ final boolean not,
+ final List<Object> parameters,
+ final SearchSupport svs) {
+
+ StringBuilder query = new StringBuilder("type_id");
+
+ if (not) {
+ query.append("<>");
+ } else {
+ query.append('=');
+ }
+
+ query.append('?').append(setParameter(parameters,
cond.getAnyTypeKey()));
+
+ return query.toString();
+ }
+
+ @Override
+ protected String getQuery(
+ final RoleCond cond,
+ final boolean not,
+ final List<Object> parameters,
+ final SearchSupport svs) {
+
+ StringBuilder query = new StringBuilder("(");
+
+ if (not) {
+ query.append("id NOT IN (");
+ } else {
+ query.append("id IN (");
+ }
+
+ query.append("SELECT DISTINCT any_id FROM ").
+ append(svs.role().name).append(" WHERE ").
+ append("role_id=?").append(setParameter(parameters,
cond.getRole())).
+ append(") ");
+
+ if (not) {
+ query.append("AND id NOT IN (");
+ } else {
+ query.append("OR id IN (");
+ }
+
+ query.append("SELECT DISTINCT any_id FROM ").
+ append(svs.dynrolemembership().name).append(" WHERE ").
+ append("role_id=?").append(setParameter(parameters,
cond.getRole())).
+ append(")");
+
+ query.append(")");
+
+ return query.toString();
+ }
+
+ @Override
+ protected String getQuery(
+ final PrivilegeCond cond,
+ final boolean not,
+ final List<Object> parameters,
+ final SearchSupport svs) {
+
+ StringBuilder query = new StringBuilder("(");
+
+ if (not) {
+ query.append("id NOT IN (");
+ } else {
+ query.append("id IN (");
+ }
+
+ query.append("SELECT DISTINCT any_id FROM ").
+ append(svs.priv().name).append(" WHERE ").
+ append("privilege_id=?").append(setParameter(parameters,
cond.getPrivilege())).
+ append(") ");
+
+ if (not) {
+ query.append("AND id NOT IN (");
+ } else {
+ query.append("OR id IN (");
+ }
+
+ query.append("SELECT DISTINCT any_id FROM ").
+ append(svs.dynpriv().name).append(" WHERE ").
+ append("privilege_id=?").append(setParameter(parameters,
cond.getPrivilege())).
+ append(")");
+
+ query.append(")");
+
+ return query.toString();
+ }
+
+ @Override
+ protected String getQuery(
+ final DynRealmCond cond,
+ final boolean not,
+ final List<Object> parameters,
+ final SearchSupport svs) {
+
+ StringBuilder query = new StringBuilder();
+
+ if (not) {
+ query.append("id NOT IN (");
+ } else {
+ query.append("id IN (");
+ }
+
+ query.append("SELECT DISTINCT any_id FROM ").
+ append(svs.dynrealmmembership().name).append(" WHERE ").
+ append("dynRealm_id=?").append(setParameter(parameters,
cond.getDynRealm())).
+ append(")");
+
+ return query.toString();
+ }
+
+ @Override
+ protected String getQuery(
+ final ResourceCond cond,
+ final boolean not,
+ final List<Object> parameters,
+ final SearchSupport svs) {
+
+ StringBuilder query = new StringBuilder();
+
+ if (not) {
+ query.append("id NOT IN (");
+ } else {
+ query.append("id IN (");
+ }
+
+ query.append("SELECT DISTINCT any_id FROM ").
+ append(svs.resource().name).
+ append(" WHERE resource_id=?").
+ append(setParameter(parameters, cond.getResourceKey()));
+
+ if (svs.anyTypeKind == AnyTypeKind.USER || svs.anyTypeKind ==
AnyTypeKind.ANY_OBJECT) {
+ query.append(" UNION SELECT DISTINCT any_id FROM ").
+ append(svs.groupResource().name).
+ append(" WHERE resource_id=?").
+ append(setParameter(parameters, cond.getResourceKey()));
+ }
+
+ query.append(')');
+
+ return query.toString();
+ }
+
+ @Override
+ protected String getQuery(
+ final AssignableCond cond,
+ final List<Object> parameters,
+ final SearchSupport svs) {
+
+ Realm realm;
+ try {
+ realm = check(cond);
+ } catch (IllegalArgumentException e) {
+ return ALWAYS_FALSE_ASSERTION;
+ }
+
+ StringBuilder query = new StringBuilder("(");
+ if (cond.isFromGroup()) {
+ realmDAO.findDescendants(realm).forEach(current -> {
+ query.append("realm_id=?").append(setParameter(parameters,
current.getKey())).append(" OR ");
+ });
+ query.setLength(query.length() - 4);
+ } else {
+ for (Realm current = realm; current.getParent() != null; current =
current.getParent()) {
+ query.append("realm_id=?").append(setParameter(parameters,
current.getKey())).append(" OR ");
+ }
+ query.append("realm_id=?").append(setParameter(parameters,
realmDAO.getRoot().getKey()));
+ }
+
+ query.append(")");
+
+ return query.toString();
+ }
+
+ @Override
+ protected String getQuery(
+ final MemberCond cond,
+ final boolean not,
+ final List<Object> parameters,
+ final SearchSupport svs) {
+
+ String memberKey;
+ try {
+ memberKey = check(cond);
+ } catch (IllegalArgumentException e) {
+ return ALWAYS_FALSE_ASSERTION;
+ }
+
+ StringBuilder query = new StringBuilder("(");
+
+ if (not) {
+ query.append("id NOT IN (");
+ } else {
+ query.append("id IN (");
+ }
+
+ query.append("SELECT DISTINCT group_id AS any_id FROM ").
+ append(new
SearchSupport(AnyTypeKind.USER).membership().name).append(" WHERE ").
+ append("any_id=?").append(setParameter(parameters, memberKey)).
+ append(") ");
+
+ if (not) {
+ query.append("AND id NOT IN (");
+ } else {
+ query.append("OR id IN (");
+ }
+
+ query.append("SELECT DISTINCT group_id AS any_id FROM ").
+ append(new
SearchSupport(AnyTypeKind.ANY_OBJECT).membership().name).append(" WHERE ").
+ append("any_id=?").append(setParameter(parameters, memberKey)).
+ append(")");
+
+ query.append(")");
+
+ return query.toString();
+ }
+
+ @Override
+ protected String getQuery(
+ final RelationshipTypeCond cond,
+ final boolean not,
+ final List<Object> parameters,
+ final SearchSupport svs) {
+
+ StringBuilder query = new StringBuilder("(");
+
+ if (not) {
+ query.append("id NOT IN (");
+ } else {
+ query.append("id IN (");
+ }
+
+ query.append("SELECT any_id ").append("FROM ").
+ append(svs.relationship().name).
+ append(" WHERE type=?").append(setParameter(parameters,
cond.getRelationshipTypeKey())).
+ append(" UNION SELECT right_any_id AS any_id FROM ").
+ append(svs.relationship().name).
+ append(" WHERE type=?").append(setParameter(parameters,
cond.getRelationshipTypeKey())).
+ append(')');
+
+ query.append(")");
+
+ return query.toString();
+ }
+
+ @Override
+ protected String getQuery(
+ final RelationshipCond cond,
+ final boolean not,
+ final List<Object> parameters,
+ final SearchSupport svs) {
+
+ String rightAnyObjectKey;
+ try {
+ rightAnyObjectKey = check(cond);
+ } catch (IllegalArgumentException e) {
+ return ALWAYS_FALSE_ASSERTION;
+ }
+
+ StringBuilder query = new StringBuilder("(");
+
+ if (not) {
+ query.append("id NOT IN (");
+ } else {
+ query.append("id IN (");
+ }
+
+ query.append("SELECT DISTINCT any_id FROM ").
+ append(svs.relationship().name).append(" WHERE ").
+ append("right_any_id=?").append(setParameter(parameters,
rightAnyObjectKey)).
+ append(')');
+
+ query.append(")");
+
+ return query.toString();
+ }
+
+ @Override
+ protected String getQuery(
+ final MembershipCond cond,
+ final boolean not,
+ final List<Object> parameters,
+ final SearchSupport svs) {
+
+ List<String> groupKeys;
+ try {
+ groupKeys = check(cond);
+ } catch (IllegalArgumentException e) {
+ return ALWAYS_FALSE_ASSERTION;
+ }
+
+ String where = groupKeys.stream().
+ map(key -> "group_id=?" + setParameter(parameters, key)).
+ collect(Collectors.joining(" OR "));
+
+ StringBuilder query = new StringBuilder("(");
+
+ if (not) {
+ query.append("id NOT IN (");
+ } else {
+ query.append("id IN (");
+ }
+
+ query.append("SELECT DISTINCT any_id FROM ").
+ append(svs.membership().name).append(" WHERE ").
+ append("(").append(where).append(")").
+ append(") ");
+
+ if (not) {
+ query.append("AND id NOT IN (");
+ } else {
+ query.append("OR id IN (");
+ }
+
+ query.append("SELECT DISTINCT any_id FROM ").
+ append(svs.dyngroupmembership().name).append(" WHERE ").
+ append("(").append(where).append(")").
+ append(")");
+
+ query.append(")");
+
+ return query.toString();
+ }
+
+ @Override
+ protected String getQuery(
+ final AnyCond cond,
+ final boolean not,
+ final List<Object> parameters,
+ final SearchSupport svs) {
+
+ Triple<PlainSchema, PlainAttrValue, AnyCond> checked;
+ try {
+ checked = check(cond, svs.anyTypeKind);
+ } catch (IllegalArgumentException e) {
+ return ALWAYS_FALSE_ASSERTION;
+ }
+
+ StringBuilder query = new StringBuilder();
+
+ PlainSchema schema = schemaDAO.find(cond.getSchema());
+ if (schema == null) {
+ fillAttrQuery(query, checked.getMiddle(), checked.getLeft(),
checked.getRight(), not, parameters, svs);
+ } else {
+ fillAttrQuery(anyUtilsFactory.getInstance(svs.anyTypeKind),
+ query, checked.getMiddle(), checked.getLeft(),
checked.getRight(), not, parameters, svs);
+ }
+
+ return query.toString();
+ }
+
+ @Override
+ protected String buildAdminRealmsFilter(
+ final Set<String> realmKeys,
+ final SearchSupport svs,
+ final List<Object> parameters) {
+
+ List<String> realmKeyArgs = realmKeys.stream().
+ map(realmKey -> "?" + setParameter(parameters, realmKey)).
+ collect(Collectors.toList());
+ return "realm_id IN (" + StringUtils.join(realmKeyArgs, ", ") + ")";
+ }
+
+ @Override
+ protected int doCount(final Set<String> adminRealms, final SearchCond
cond, final AnyTypeKind kind) {
+ List<Object> parameters = new ArrayList<>();
+
+ SearchSupport svs = buildSearchSupport(kind);
+
+ Pair<String, Set<String>> filter = getAdminRealmsFilter(adminRealms,
svs, parameters);
+
+ Pair<StringBuilder, Set<String>> queryInfo =
+ getQuery(buildEffectiveCond(cond, filter.getRight()),
parameters, svs);
+
+ StringBuilder queryString =
+ new StringBuilder("SELECT
count(").append(svs.table().alias).append(".id").append(")");
+
+ buildFrom(queryString, queryInfo, svs, null);
+
+ buildWhere(queryString, queryInfo, filter);
+
+ Query countQuery =
entityManager().createNativeQuery(queryString.toString());
+ fillWithParameters(countQuery, parameters);
+
+ return ((Number) countQuery.getSingleResult()).intValue();
+ }
+
+ @Override
+ @SuppressWarnings("unchecked")
+ protected <T extends Any<?>> List<T> doSearch(
+ final Set<String> adminRealms,
+ final SearchCond cond,
+ final int page,
+ final int itemsPerPage,
+ final List<OrderByClause> orderBy,
+ final AnyTypeKind kind) {
+
+ try {
+ List<Object> parameters = new ArrayList<>();
+
+ SearchSupport svs = buildSearchSupport(kind);
+
+ Pair<String, Set<String>> filter =
getAdminRealmsFilter(adminRealms, svs, parameters);
+
+ SearchCond effectiveCond = buildEffectiveCond(cond,
filter.getRight());
+
+ // 1. get the query string from the search condition
+ Pair<StringBuilder, Set<String>> queryInfo =
getQuery(effectiveCond, parameters, svs);
+
+ // 2. take into account realms and ordering
+ OrderBySupport obs = parseOrderBy(svs, orderBy);
+
+ StringBuilder queryString = new StringBuilder("SELECT
").append(svs.table().alias).append(".id");
+ obs.items.forEach(item -> {
+ queryString.append(",").append(item.select);
+ });
+
+ buildFrom(queryString, queryInfo, svs, obs);
+
+ buildWhere(queryString, queryInfo, filter);
+
+ LOG.debug("Query: {}, parameters: {}", queryString, parameters);
+
+ queryString.append(buildOrderBy(obs));
+
+ LOG.debug("Query with auth and order by statements: {},
parameters: {}", queryString, parameters);
+
+ // 3. prepare the search query
+ Query query =
entityManager().createNativeQuery(queryString.toString());
+
+ // 4. page starts from 1, while setFirtResult() starts from 0
+ query.setFirstResult(itemsPerPage * (page <= 0 ? 0 : page - 1));
+
+ if (itemsPerPage >= 0) {
+ query.setMaxResults(itemsPerPage);
+ }
+
+ // 5. populate the search query with parameter values
+ fillWithParameters(query, parameters);
+
+ // 6. Prepare the result (avoiding duplicates)
+ return buildResult(query.getResultList(), kind);
+ } catch (SyncopeClientException e) {
+ throw e;
+ } catch (Exception e) {
+ LOG.error("While searching for {}", kind, e);
+ }
+
+ return Collections.emptyList();
+ }
+
+ protected StringBuilder buildOrderBy(final OrderBySupport obs) {
+ StringBuilder orderBy = new StringBuilder();
+
+ obs.items.forEach(item -> {
+ orderBy.append(item.orderBy).append(',');
+ });
+ if (!obs.items.isEmpty()) {
+ orderBy.insert(0, " ORDER BY ");
+ orderBy.deleteCharAt(orderBy.length() - 1);
+ }
+
+ return orderBy;
+ }
+
+ protected Pair<StringBuilder, Set<String>> getQuery(
+ final SearchCond cond, final List<Object> parameters, final
SearchSupport svs) {
+
+ boolean not = cond.getType() == SearchCond.Type.NOT_LEAF;
+
+ StringBuilder query = new StringBuilder();
+ Set<String> involvedPlainAttrs = new HashSet<>();
+
+ switch (cond.getType()) {
+ case LEAF:
+ case NOT_LEAF:
+ cond.getLeaf(AnyTypeCond.class).
+ filter(leaf -> AnyTypeKind.ANY_OBJECT ==
svs.anyTypeKind).
+ ifPresent(leaf -> query.append(getQuery(leaf, not,
parameters, svs)));
+
+ cond.getLeaf(RelationshipTypeCond.class).
+ filter(leaf -> AnyTypeKind.GROUP != svs.anyTypeKind).
+ ifPresent(leaf -> query.append(getQuery(leaf, not,
parameters, svs)));
+
+ cond.getLeaf(RelationshipCond.class).
+ filter(leaf -> AnyTypeKind.GROUP != svs.anyTypeKind).
+ ifPresent(leaf -> query.append(getQuery(leaf, not,
parameters, svs)));
+
+ cond.getLeaf(MembershipCond.class).
+ filter(leaf -> AnyTypeKind.GROUP != svs.anyTypeKind).
+ ifPresent(leaf -> query.append(getQuery(leaf, not,
parameters, svs)));
+
+ cond.getLeaf(MemberCond.class).
+ filter(leaf -> AnyTypeKind.GROUP == svs.anyTypeKind).
+ ifPresent(leaf -> query.append(getQuery(leaf, not,
parameters, svs)));
+
+ cond.getLeaf(AssignableCond.class).
+ ifPresent(leaf -> query.append(getQuery(leaf,
parameters, svs)));
+
+ cond.getLeaf(RoleCond.class).
+ filter(leaf -> AnyTypeKind.USER == svs.anyTypeKind).
+ ifPresent(leaf -> query.append(getQuery(leaf, not,
parameters, svs)));
+
+ cond.getLeaf(PrivilegeCond.class).
+ filter(leaf -> AnyTypeKind.USER == svs.anyTypeKind).
+ ifPresent(leaf -> query.append(getQuery(leaf, not,
parameters, svs)));
+
+ cond.getLeaf(DynRealmCond.class).
+ ifPresent(leaf -> query.append(getQuery(leaf, not,
parameters, svs)));
+
+ cond.getLeaf(ResourceCond.class).
+ ifPresent(leaf -> query.append(getQuery(leaf, not,
parameters, svs)));
+
+ Optional<AnyCond> anyCond = cond.getLeaf(AnyCond.class);
+ if (anyCond.isPresent()) {
+ query.append(getQuery(anyCond.get(), not, parameters,
svs));
+ } else {
+ cond.getLeaf(AttrCond.class).ifPresent(leaf -> {
+ query.append(getQuery(leaf, not, parameters, svs));
+ try {
+ involvedPlainAttrs.add(check(leaf,
svs.anyTypeKind).getLeft().getKey());
+ } catch (IllegalArgumentException e) {
+ // ignore
+ }
+ });
+ }
+
+ // allow for additional search conditions
+ getQueryForCustomConds(cond, parameters, svs, not, query);
+ break;
+
+ case AND:
+ Pair<StringBuilder, Set<String>> leftAndInfo =
getQuery(cond.getLeft(), parameters, svs);
+ involvedPlainAttrs.addAll(leftAndInfo.getRight());
+
+ Pair<StringBuilder, Set<String>> rigthAndInfo =
getQuery(cond.getRight(), parameters, svs);
+ involvedPlainAttrs.addAll(rigthAndInfo.getRight());
+
+ query.append("(").
+ append(leftAndInfo.getKey()).
+ append(" AND ").
+ append(rigthAndInfo.getKey()).
+ append(")");
+ break;
+
+ case OR:
+ Pair<StringBuilder, Set<String>> leftOrInfo =
getQuery(cond.getLeft(), parameters, svs);
+ involvedPlainAttrs.addAll(leftOrInfo.getRight());
+
+ Pair<StringBuilder, Set<String>> rigthOrInfo =
getQuery(cond.getRight(), parameters, svs);
+ involvedPlainAttrs.addAll(rigthOrInfo.getRight());
+
+ query.append("(").
+ append(leftOrInfo.getKey()).
+ append(" OR ").
+ append(rigthOrInfo.getKey()).
+ append(")");
+ break;
+
+ default:
+ }
+
+ return Pair.of(query, involvedPlainAttrs);
+ }
+
+ protected void fillAttrQuery(
+ final StringBuilder query,
+ final PlainAttrValue attrValue,
+ final PlainSchema schema,
+ final AttrCond cond,
+ final boolean not,
+ final List<Object> parameters,
+ final SearchSupport svs) {
+
+ // This first branch is required for handling with not conditions
given on multivalue fields (SYNCOPE-1419)
+ if (not && !(cond instanceof AnyCond)) {
+
+ query.append("NOT (");
+ fillAttrQuery(query, attrValue, schema, cond, false, parameters,
svs);
+ query.append(")");
+ } else if (not && cond.getType() == AttrCond.Type.ISNULL) {
+ cond.setType(AttrCond.Type.ISNOTNULL);
+ fillAttrQuery(query, attrValue, schema, cond, true, parameters,
svs);
+ } else {
+ boolean lower = (schema.getType() == AttrSchemaType.String ||
schema.getType() == AttrSchemaType.Enum)
+ && (cond.getType() == AttrCond.Type.IEQ || cond.getType()
== AttrCond.Type.ILIKE);
+
+ String column = cond.getSchema();
+ if ((schema.getType() == AttrSchemaType.String || schema.getType()
== AttrSchemaType.Enum) && lower) {
+ column = "LOWER (" + column + ")";
+ }
+
+ switch (cond.getType()) {
+
+ case ISNULL:
+ // shouldn't occour: processed before
+ break;
+
+ case ISNOTNULL:
+ query.append(column).append(" IS NOT NULL");
+ break;
+
+ case ILIKE:
+ case LIKE:
+ if (schema.getType() == AttrSchemaType.String ||
schema.getType() == AttrSchemaType.Enum) {
+ query.append(column);
+ query.append(" LIKE ");
+ if (lower) {
+
query.append("LOWER(?").append(setParameter(parameters,
cond.getExpression())).append(')');
+ } else {
+ query.append('?').append(setParameter(parameters,
cond.getExpression()));
+ }
+ } else {
+ query.append(" 1=2");
+ LOG.error("LIKE is only compatible with string or enum
schemas");
+ }
+ break;
+
+ case IEQ:
+ case EQ:
+ query.append(column);
+ query.append('=');
+
+ if ((schema.getType() == AttrSchemaType.String
+ || schema.getType() == AttrSchemaType.Enum) &&
lower) {
+
query.append("LOWER(?").append(setParameter(parameters,
attrValue.getValue())).append(')');
+ } else {
+ query.append('?').append(setParameter(parameters,
attrValue.getValue()));
+ }
+ break;
+
+ case GE:
+ query.append(column);
+ if (not) {
+ query.append('<');
+ } else {
+ query.append(">=");
+ }
+ query.append('?').append(setParameter(parameters,
attrValue.getValue()));
+ break;
+
+ case GT:
+ query.append(column);
+ if (not) {
+ query.append("<=");
+ } else {
+ query.append('>');
+ }
+ query.append('?').append(setParameter(parameters,
attrValue.getValue()));
+ break;
+
+ case LE:
+ query.append(column);
+ if (not) {
+ query.append('>');
+ } else {
+ query.append("<=");
+ }
+ query.append('?').append(setParameter(parameters,
attrValue.getValue()));
+ break;
+
+ case LT:
+ query.append(column);
+ if (not) {
+ query.append(">=");
+ } else {
+ query.append('<');
+ }
+ query.append('?').append(setParameter(parameters,
attrValue.getValue()));
+ break;
+
+ default:
+ }
+ }
+ }
+
+ protected void fillWithParameters(final Query query, final List<Object>
parameters) {
+ for (int i = 0; i < parameters.size(); i++) {
+ if (parameters.get(i) instanceof Date) {
+ query.setParameter(i + 1, (Date) parameters.get(i),
TemporalType.TIMESTAMP);
+ } else if (parameters.get(i) instanceof Boolean) {
+ query.setParameter(i + 1, ((Boolean) parameters.get(i))
+ ? 1
+ : 0);
+ } else {
+ query.setParameter(i + 1, parameters.get(i));
+ }
+ }
+ }
+
+ protected OrderBySupport parseOrderBy(
+ final SearchSupport svs,
+ final List<OrderByClause> orderBy) {
+
+ AnyUtils anyUtils = anyUtilsFactory.getInstance(svs.anyTypeKind);
+
+ OrderBySupport obs = new OrderBySupport();
+
+ Set<String> orderByUniquePlainSchemas = new HashSet<>();
+ Set<String> orderByNonUniquePlainSchemas = new HashSet<>();
+ orderBy.forEach(clause -> {
+ OrderBySupport.Item item = new OrderBySupport.Item();
+
+ parseOrderByForCustom(svs, clause, item, obs);
+
+ if (item.isEmpty()) {
+ if (anyUtils.getField(clause.getField()) == null) {
+ PlainSchema schema = schemaDAO.find(clause.getField());
+ if (schema != null) {
+ if (schema.isUniqueConstraint()) {
+ orderByUniquePlainSchemas.add(schema.getKey());
+ } else {
+ orderByNonUniquePlainSchemas.add(schema.getKey());
+ }
+ if (orderByUniquePlainSchemas.size() > 1 ||
orderByNonUniquePlainSchemas.size() > 1) {
+ SyncopeClientException invalidSearch =
+
SyncopeClientException.build(ClientExceptionType.InvalidSearchExpression);
+ invalidSearch.getElements().add("Order by more
than one attribute is not allowed; "
+ + "remove one from " +
(orderByUniquePlainSchemas.size() > 1
+ ? orderByUniquePlainSchemas :
orderByNonUniquePlainSchemas));
+ throw invalidSearch;
+ }
+ parseOrderByForPlainSchema(svs, obs, item, clause,
schema, clause.getField());
+ }
+ } else {
+ // Manage difference among external key attribute and
internal JPA @Id
+ String fieldName = "key".equals(clause.getField()) ? "id"
: clause.getField();
+
+ // Adjust field name to column name
+ if (ArrayUtils.contains(RELATIONSHIP_FIELDS, fieldName)) {
+ fieldName += "_id";
+ }
+
+ obs.views.add(svs.field());
+
+ item.select = svs.table().alias + "." + fieldName;
+ item.where = StringUtils.EMPTY;
+ item.orderBy = svs.table().alias + "." + fieldName + " " +
clause.getDirection().name();
+ }
+ }
+
+ if (item.isEmpty()) {
+ LOG.warn("Cannot build any valid clause from {}", clause);
+ } else {
+ obs.items.add(item);
+ }
+ });
+
+ return obs;
+ }
+
+ protected Pair<String, Set<String>> getAdminRealmsFilter(
+ final Set<String> adminRealms,
+ final SearchSupport svs,
+ final List<Object> parameters) {
+
+ Set<String> realmKeys = new HashSet<>();
+ Set<String> dynRealmKeys = new HashSet<>();
+ RealmUtils.normalize(adminRealms).forEach(realmPath -> {
+ if (realmPath.startsWith("/")) {
+ Realm realm = realmDAO.findByFullPath(realmPath);
+ if (realm == null) {
+ SyncopeClientException noRealm =
SyncopeClientException.build(ClientExceptionType.InvalidRealm);
+ noRealm.getElements().add("Invalid realm specified: " +
realmPath);
+ throw noRealm;
+ } else {
+ realmKeys.addAll(realmDAO.findDescendants(realm).stream().
+ map(Entity::getKey).collect(Collectors.toSet()));
+ }
+ } else {
+ DynRealm dynRealm = dynRealmDAO.find(realmPath);
+ if (dynRealm == null) {
+ LOG.warn("Ignoring invalid dynamic realm {}", realmPath);
+ } else {
+ dynRealmKeys.add(dynRealm.getKey());
+ }
+ }
+ });
+ if (!dynRealmKeys.isEmpty()) {
+ realmKeys.addAll(realmDAO.findAll().stream().
+ map(Entity::getKey).collect(Collectors.toSet()));
+ }
+
+ return Pair.of(buildAdminRealmsFilter(realmKeys, svs, parameters),
dynRealmKeys);
+ }
+
+ protected void buildFrom(
+ final StringBuilder query,
+ final Pair<StringBuilder, Set<String>> queryInfo,
+ final SearchSupport svs,
+ final OrderBySupport obs) {
+
+ query.append(" FROM ").append(svs.table().name).append("
").append(svs.table().alias);
+
+ Set<String> schemas = queryInfo.getRight();
+
+ if (obs != null) {
+ Pattern pattern = Pattern.compile("(.*) -> 0 AS .*");
+ obs.items.forEach(item -> {
+ Matcher matcher = pattern.matcher(item.select);
+ if (matcher.find()) {
+ schemas.add(matcher.group(1));
+ }
+ });
+ }
+
+ schemas.forEach(schema -> {
+ // i.e jsonb_path_query(plainattrs, '$[*] ?
(@.schema=="Nome")."values"') AS Nome
+ PlainSchema pschema = schemaDAO.find(schema);
+ if (pschema == null) {
+ // just to be sure
+ LOG.warn("Ignoring invalid schema '{}'", schema);
+ } else {
+ query.append(",").
+ append("jsonb_path_query_array(plainattrs, '$[*] ?
(@.schema==\"").
+ append(schema).append("\").").
+ append("\"").append(pschema.isUniqueConstraint() ?
"uniqueValue" : "values").append("\"')").
+ append(" AS ").append(schema);
+ }
+ });
+ }
+
+ protected void buildWhere(
+ final StringBuilder query,
+ final Pair<StringBuilder, Set<String>> queryInfo,
+ final Pair<String, Set<String>> realms) {
+ if (queryInfo.getLeft().length() > 0) {
+ query.append(" WHERE ").append(queryInfo.getLeft());
+ }
+
+ if (realms.getLeft().length() > 0) {
+ if (queryInfo.getLeft().length() > 0) {
+ query.append(" AND ").append(realms.getLeft());
+ } else {
+ query.append(" WHERE ").append(realms.getLeft());
+ }
+ }
+ }
+
}
diff --git
a/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/PGJPAJSONPlainSchemaDAO.java
b/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/PGJPAJSONPlainSchemaDAO.java
index c4d27d6..3c0cf54 100644
---
a/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/PGJPAJSONPlainSchemaDAO.java
+++
b/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/PGJPAJSONPlainSchemaDAO.java
@@ -27,7 +27,7 @@ public class PGJPAJSONPlainSchemaDAO extends
AbstractJPAJSONPlainSchemaDAO {
@Override
public <T extends PlainAttr<?>> boolean hasAttrs(final PlainSchema schema,
final Class<T> reference) {
Query query = entityManager().createNativeQuery(
- "SELECT COUNT(id) FROM " + new
SearchSupport(getAnyTypeKind(reference)).field().name
+ "SELECT COUNT(id) FROM " + new
SearchSupport(getAnyTypeKind(reference)).table().name
+ " WHERE plainAttrs @> '[{\"schema\":\"" + schema.getKey() +
"\"}]'::jsonb");
return ((Number) query.getSingleResult()).intValue() > 0;
diff --git a/core/persistence-jpa-json/src/main/resources/pgjsonb/views.xml
b/core/persistence-jpa-json/src/main/resources/pgjsonb/views.xml
index eb450be..f07024d 100644
--- a/core/persistence-jpa-json/src/main/resources/pgjsonb/views.xml
+++ b/core/persistence-jpa-json/src/main/resources/pgjsonb/views.xml
@@ -47,13 +47,6 @@ under the License.
</entry>
<!-- user -->
- <entry key="user_search">
- CREATE VIEW user_search AS
-
- SELECT u.id as any_id, u.*,attrs,attrValues
- FROM SyncopeUser u, jsonb_array_elements(COALESCE(u.plainAttrs,
'[{}]'::jsonb)) attrs,
- jsonb_array_elements(COALESCE(attrs -> 'values', '[{}]'::jsonb)) attrValues
- </entry>
<entry key="user_search_urelationship">
CREATE VIEW user_search_urelationship AS
diff --git a/docker/src/main/resources/docker-compose/docker-compose-ha.yml
b/docker/src/main/resources/docker-compose/docker-compose-ha.yml
index 0ebde3a..dd537d6 100644
--- a/docker/src/main/resources/docker-compose/docker-compose-ha.yml
+++ b/docker/src/main/resources/docker-compose/docker-compose-ha.yml
@@ -22,7 +22,7 @@ version: '3.3'
services:
db:
- image: postgres:11.5
+ image: postgres:12
restart: always
environment:
POSTGRES_DB: syncope
diff --git
a/docker/src/main/resources/docker-compose/docker-compose-pgjsonb.yml
b/docker/src/main/resources/docker-compose/docker-compose-pgjsonb.yml
index 5468573..0f5bb38 100644
--- a/docker/src/main/resources/docker-compose/docker-compose-pgjsonb.yml
+++ b/docker/src/main/resources/docker-compose/docker-compose-pgjsonb.yml
@@ -21,7 +21,7 @@ version: '3.3'
services:
db:
- image: postgres:11.5
+ image: postgres:12
restart: always
environment:
POSTGRES_DB: syncope
diff --git
a/docker/src/main/resources/docker-compose/docker-compose-postgresql.yml
b/docker/src/main/resources/docker-compose/docker-compose-postgresql.yml
index 56de700..7a661ea 100644
--- a/docker/src/main/resources/docker-compose/docker-compose-postgresql.yml
+++ b/docker/src/main/resources/docker-compose/docker-compose-postgresql.yml
@@ -21,7 +21,7 @@ version: '3.3'
services:
db:
- image: postgres:11
+ image: postgres:12
restart: always
environment:
POSTGRES_DB: syncope
diff --git a/pom.xml b/pom.xml
index b1d1c9b..3e886af 100644
--- a/pom.xml
+++ b/pom.xml
@@ -537,7 +537,7 @@ under the License.
<nodejs.version>v8.11.4</nodejs.version>
<protractor.version>5.4.0</protractor.version>
- <docker.postgresql.version>11</docker.postgresql.version>
+ <docker.postgresql.version>12</docker.postgresql.version>
<docker.mysql.version>8.0</docker.mysql.version>
<docker.mariadb.version>10.5</docker.mariadb.version>
