This is an automated email from the ASF dual-hosted git repository.
andreapatricelli pushed a commit to branch 4_0_X
in repository https://gitbox.apache.org/repos/asf/syncope.git
The following commit(s) were added to refs/heads/4_0_X by this push:
new 8db44e4cf0 [SYNCOPE-1906] Fixing order by clause on plain attribute
for mariadb and mysql (#1171)
8db44e4cf0 is described below
commit 8db44e4cf0a860b22a12f7927ed2a2f78f9f063a
Author: Andrea Patricelli <[email protected]>
AuthorDate: Mon Sep 8 10:51:28 2025 +0200
[SYNCOPE-1906] Fixing order by clause on plain attribute for mariadb and
mysql (#1171)
---
.../jpa/dao/MariaDBJPAAnySearchDAO.java | 30 ++++++
.../persistence/jpa/dao/MySQLJPAAnySearchDAO.java | 14 ++-
.../core/persistence/jpa/inner/AnySearchTest.java | 90 ++++++++++++++++++
.../apache/syncope/fit/core/UserIssuesITCase.java | 102 +++++++++++++++++++++
4 files changed, 233 insertions(+), 3 deletions(-)
diff --git
a/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/MariaDBJPAAnySearchDAO.java
b/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/MariaDBJPAAnySearchDAO.java
index 9bef72a4aa..148f52ac00 100644
---
a/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/MariaDBJPAAnySearchDAO.java
+++
b/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/MariaDBJPAAnySearchDAO.java
@@ -38,6 +38,7 @@ import
org.apache.syncope.core.persistence.api.entity.PlainAttr;
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.springframework.data.domain.Sort;
public class MariaDBJPAAnySearchDAO extends AbstractJPAAnySearchDAO {
@@ -68,6 +69,35 @@ public class MariaDBJPAAnySearchDAO extends
AbstractJPAAnySearchDAO {
entityManager);
}
+ @Override
+ protected void parseOrderByForPlainSchema(
+ final SearchSupport svs,
+ final OrderBySupport obs,
+ final OrderBySupport.Item item,
+ final Sort.Order clause,
+ final PlainSchema schema,
+ final String fieldName) {
+
+ // keep track of involvement of non-mandatory schemas in the order by
clauses
+ obs.nonMandatorySchemas =
!"true".equals(schema.getMandatoryCondition());
+
+ obs.views.add(svs.field());
+
+ item.select = new StringBuilder().
+ append("( SELECT
usa").append('.').append(key(schema.getType())).
+ append(" FROM ").append(schema.isUniqueConstraint()
+ ? svs.asSearchViewSupport().uniqueAttr().name()
+ : svs.asSearchViewSupport().attr().name()).
+ append(" usa WHERE usa.any_id = ").
+ append(defaultSV(svs).alias()).
+ append(".any_id").
+ append(" AND usa.schema_id ='").append(fieldName).append("'").
+ append(" LIMIT 1").
+ append(") AS ").append(fieldName).toString();
+ item.where = "plainSchema = '" + fieldName + '\'';
+ item.orderBy = fieldName + ' ' + clause.getDirection().name();
+ }
+
@Override
protected Pair<Boolean, AnySearchNode> getQuery(
final AttrCond cond,
diff --git
a/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/MySQLJPAAnySearchDAO.java
b/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/MySQLJPAAnySearchDAO.java
index ca3b237fcb..4a5e497067 100644
---
a/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/MySQLJPAAnySearchDAO.java
+++
b/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/MySQLJPAAnySearchDAO.java
@@ -84,9 +84,17 @@ public class MySQLJPAAnySearchDAO extends
AbstractJPAAnySearchDAO {
obs.views.add(svs.field());
- item.select = svs.field().alias() + '.'
- + (schema.isUniqueConstraint() ? "attrUniqueValue" :
key(schema.getType()))
- + " AS " + fieldName;
+ item.select = new StringBuilder().append(schema.isUniqueConstraint()
+ ? "( SELECT JSON_UNQUOTE(JSON_EXTRACT(usa.attrUniqueValue, '$.
" + key(schema.getType()) + "')) "
+ : "( SELECT usa." + key(schema.getType())).
+ append(" FROM ").
+ append(svs.field().name()).
+ append(" usa WHERE usa.any_id = ").
+ append(svs.field().alias()).
+ append(".any_id").
+ append(" AND usa.plainSchema
='").append(fieldName).append("'").
+ append(" LIMIT 1").
+ append(") AS ").append(fieldName).toString();
item.where = "plainSchema = '" + fieldName + '\'';
item.orderBy = fieldName + ' ' + clause.getDirection().name();
}
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 e1dc24f772..b27d83fec6 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
@@ -36,10 +36,12 @@ import java.util.stream.Stream;
import org.apache.syncope.common.lib.SyncopeConstants;
import org.apache.syncope.common.lib.types.AnyTypeKind;
import org.apache.syncope.common.lib.types.IdRepoEntitlement;
+import
org.apache.syncope.core.persistence.api.attrvalue.PlainAttrValidationManager;
import org.apache.syncope.core.persistence.api.dao.AnyObjectDAO;
import org.apache.syncope.core.persistence.api.dao.AnySearchDAO;
import org.apache.syncope.core.persistence.api.dao.AnyTypeDAO;
import org.apache.syncope.core.persistence.api.dao.GroupDAO;
+import org.apache.syncope.core.persistence.api.dao.PlainSchemaDAO;
import org.apache.syncope.core.persistence.api.dao.RealmDAO;
import org.apache.syncope.core.persistence.api.dao.RealmSearchDAO;
import org.apache.syncope.core.persistence.api.dao.RoleDAO;
@@ -56,6 +58,7 @@ 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.AnyType;
import org.apache.syncope.core.persistence.api.entity.PlainAttr;
+import org.apache.syncope.core.persistence.api.entity.PlainSchema;
import org.apache.syncope.core.persistence.api.entity.anyobject.AMembership;
import org.apache.syncope.core.persistence.api.entity.anyobject.AnyObject;
import org.apache.syncope.core.persistence.api.entity.group.Group;
@@ -105,6 +108,12 @@ public class AnySearchTest extends AbstractTest {
@Autowired
private RoleDAO roleDAO;
+ @Autowired
+ private PlainSchemaDAO plainSchemaDAO;
+
+ @Autowired
+ private PlainAttrValidationManager validator;
+
@BeforeEach
public void adjustLoginDateForLocalSystem() throws ParseException {
User rossini = userDAO.findByUsername("rossini").orElseThrow();
@@ -948,4 +957,85 @@ public class AnySearchTest extends AbstractTest {
assertNotNull(users);
assertEquals(4, users.size());
}
+
+ @Test
+ public void issueSYNCOPE1906() {
+ User bellini = userDAO.findByUsername("bellini").orElseThrow();
+
+ PlainSchema ctypeSchema =
plainSchemaDAO.findById("ctype").orElseThrow();
+ assertNotNull(ctypeSchema);
+
+ userDAO.save(addPlainAttr(bellini, ctypeSchema, "aa1"));
+
+ User puccini = userDAO.findByUsername("puccini").orElseThrow();
+ assertNotNull(bellini);
+
+ userDAO.save(addPlainAttr(puccini, ctypeSchema, "aa2"));
+
+ User verdi = userDAO.findByUsername("verdi").orElseThrow();
+ assertNotNull(verdi);
+
+ userDAO.save(addPlainAttr(verdi, ctypeSchema, "aa3"));
+
+ User vivaldi = userDAO.findByUsername("vivaldi").orElseThrow();
+ assertNotNull(vivaldi);
+
+ userDAO.save(addPlainAttr(vivaldi, ctypeSchema, "aa4"));
+
+ User rossini = userDAO.findByUsername("rossini").orElseThrow();
+ assertNotNull(rossini);
+
+ userDAO.save(addPlainAttr(rossini, ctypeSchema, "aa5"));
+
+ entityManager.flush();
+
+ AnyCond idCond = new AnyCond(AttrCond.Type.ISNOTNULL);
+ idCond.setSchema("id");
+
+ List<User> users =
+ searchDAO.search(SearchCond.of(idCond), List.of(new
Sort.Order(Sort.Direction.DESC, "ctype")),
+ AnyTypeKind.USER);
+
+ assertEquals("bellini", users.get(4).getUsername());
+ assertEquals("puccini", users.get(3).getUsername());
+ assertEquals("verdi", users.get(2).getUsername());
+ assertEquals("vivaldi", users.get(1).getUsername());
+ assertEquals("rossini", users.get(0).getUsername());
+
+ // order by ctype even not searching by it
+ AttrCond surnameCond = new AttrCond(AttrCond.Type.ILIKE);
+ surnameCond.setSchema("surname");
+ surnameCond.setExpression("%ini");
+
+ AttrCond coolCond = new AttrCond(AttrCond.Type.ISNULL);
+ coolCond.setSchema("cool");
+
+ users = searchDAO.search(SearchCond.and(SearchCond.of(surnameCond),
SearchCond.of(coolCond)),
+ List.of(new Sort.Order(Sort.Direction.DESC, "ctype")),
AnyTypeKind.USER);
+
+ assertFalse(users.isEmpty());
+ assertEquals(2, users.size());
+
+ // order by unique attribute
+ users = searchDAO.search(SearchCond.of(idCond), List.of(new
Sort.Order(Sort.Direction.DESC, "fullname")),
+ AnyTypeKind.USER);
+
+ assertEquals("vivaldi", users.get(4).getUsername());
+ assertEquals("puccini", users.get(3).getUsername());
+ assertEquals("rossini", users.get(2).getUsername());
+ assertEquals("verdi", users.get(1).getUsername());
+ assertEquals("bellini", users.get(0).getUsername());
+ }
+
+ private User addPlainAttr(final User user, final PlainSchema plainSchema,
final String value) {
+ user.getPlainAttr(plainSchema.getKey())
+ .ifPresentOrElse(ctype ->
ctype.getValues().get(0).setStringValue(value), () -> {
+ PlainAttr attr = new PlainAttr();
+ attr.setPlainSchema(plainSchema);
+ attr.add(validator, value);
+
+ user.add(attr);
+ });
+ return user;
+ }
}
diff --git
a/fit/core-reference/src/test/java/org/apache/syncope/fit/core/UserIssuesITCase.java
b/fit/core-reference/src/test/java/org/apache/syncope/fit/core/UserIssuesITCase.java
index e607048274..e10c4ac310 100644
---
a/fit/core-reference/src/test/java/org/apache/syncope/fit/core/UserIssuesITCase.java
+++
b/fit/core-reference/src/test/java/org/apache/syncope/fit/core/UserIssuesITCase.java
@@ -102,6 +102,7 @@ import org.apache.syncope.common.lib.types.StatusRType;
import org.apache.syncope.common.lib.types.TaskType;
import org.apache.syncope.common.lib.types.UnmatchingRule;
import org.apache.syncope.common.rest.api.RESTHeaders;
+import org.apache.syncope.common.rest.api.beans.AnyQuery;
import org.apache.syncope.common.rest.api.beans.RealmQuery;
import org.apache.syncope.common.rest.api.beans.ReconQuery;
import org.apache.syncope.common.rest.api.beans.TaskQuery;
@@ -1895,4 +1896,105 @@ public class UserIssuesITCase extends AbstractITCase {
SCHEMA_SERVICE.delete(SchemaType.PLAIN,
userWithDotSchema.getKey());
}
}
+
+ @Test
+ public void issueSYNCOPE1906() {
+ UserCR userCR =
UserITCase.getUniqueSample("[email protected]");
+ userCR.getPlainAttrs()
+ .removeIf(attr -> "ctype".equals(attr.getSchema()) ||
"fullname".equals(attr.getSchema()));
+ userCR.getPlainAttrs().add(attr("ctype", "aa1"));
+ userCR.getPlainAttrs().add(attr("fullname", "aa1"));
+ UserTO aa1 = createUser(userCR).getEntity();
+
+ userCR =
UserITCase.getUniqueSample("[email protected]");
+ userCR.getPlainAttrs()
+ .removeIf(attr -> "ctype".equals(attr.getSchema()) ||
"fullname".equals(attr.getSchema()));
+ userCR.getPlainAttrs().add(attr("ctype", "aa2"));
+ userCR.getPlainAttrs().add(attr("fullname", "aa2"));
+ UserTO aa2 = createUser(userCR).getEntity();
+
+ userCR =
UserITCase.getUniqueSample("[email protected]");
+ userCR.getPlainAttrs()
+ .removeIf(attr -> "ctype".equals(attr.getSchema()) ||
"fullname".equals(attr.getSchema()));
+ userCR.getPlainAttrs().add(attr("ctype", "aa3"));
+ userCR.getPlainAttrs().add(attr("fullname", "aa3"));
+ UserTO aa3 = createUser(userCR).getEntity();
+
+ userCR =
UserITCase.getUniqueSample("[email protected]");
+ userCR.getPlainAttrs()
+ .removeIf(attr -> "ctype".equals(attr.getSchema()) ||
"fullname".equals(attr.getSchema()));
+ userCR.getPlainAttrs().add(attr("ctype", "aa4"));
+ userCR.getPlainAttrs().add(attr("fullname", "aa4"));
+ UserTO aa4 = createUser(userCR).getEntity();
+
+ userCR =
UserITCase.getUniqueSample("[email protected]");
+ userCR.getPlainAttrs()
+ .removeIf(attr -> "ctype".equals(attr.getSchema()) ||
"fullname".equals(attr.getSchema()));
+ userCR.getPlainAttrs().add(attr("ctype", "aa5"));
+ userCR.getPlainAttrs().add(attr("fullname", "aa5"));
+ UserTO aa5 = createUser(userCR).getEntity();
+
+ // add also other users with valued ctype
+ userCR = UserITCase.getUniqueSample("[email protected]");
+ userCR.getPlainAttrs()
+ .removeIf(attr -> "ctype".equals(attr.getSchema()) ||
"fullname".equals(attr.getSchema()));
+ userCR.getPlainAttrs().add(attr("ctype", "a ctype"));
+ userCR.getPlainAttrs().add(attr("fullname", "a fullname"));
+ createUser(userCR);
+
+ userCR = UserITCase.getUniqueSample("[email protected]");
+ userCR.getPlainAttrs()
+ .removeIf(attr -> "ctype".equals(attr.getSchema()) ||
"fullname".equals(attr.getSchema()));
+ userCR.getPlainAttrs().add(attr("ctype", "a ctype 2"));
+ userCR.getPlainAttrs().add(attr("fullname", "a fullname 2"));
+ createUser(userCR);
+ try {
+ await().until(() -> USER_SERVICE.search(new
AnyQuery.Builder().fiql(
+
SyncopeClient.getUserSearchConditionBuilder().is("email").equalTo("*issuesyncope1906*")
+ .query())
+ .size(0)
+ .page(1)
+ .build()).getTotalCount() == 5);
+ List<UserTO> users = USER_SERVICE.search(new
AnyQuery.Builder().fiql(
+
SyncopeClient.getUserSearchConditionBuilder().is("email").equalTo("*issuesyncope1906*")
+ .query()).size(10).page(1).orderBy("ctype
DESC").build()).getResult();
+
+ assertEquals(5, users.size());
+
+ assertEquals(aa1.getUsername(), users.get(4).getUsername());
+ assertEquals(aa2.getUsername(), users.get(3).getUsername());
+ assertEquals(aa3.getUsername(), users.get(2).getUsername());
+ assertEquals(aa4.getUsername(), users.get(1).getUsername());
+ assertEquals(aa5.getUsername(), users.get(0).getUsername());
+
+ users = USER_SERVICE.search(new AnyQuery.Builder().fiql(
+
SyncopeClient.getUserSearchConditionBuilder().is("email").equalTo("*issuesyncope1906*")
+ .query()).size(10).page(1).orderBy("ctype
ASC").build()).getResult();
+
+ assertEquals(aa5.getUsername(), users.get(4).getUsername());
+ assertEquals(aa4.getUsername(), users.get(3).getUsername());
+ assertEquals(aa3.getUsername(), users.get(2).getUsername());
+ assertEquals(aa2.getUsername(), users.get(1).getUsername());
+ assertEquals(aa1.getUsername(), users.get(0).getUsername());
+
+ // order by unique attribute
+ users = USER_SERVICE.search(new AnyQuery.Builder().fiql(
+
SyncopeClient.getUserSearchConditionBuilder().is("email").equalTo("*issuesyncope1906*")
+
.query()).size(10).page(1).orderBy("fullname ASC").build()).getResult();
+
+ assertEquals(aa5.getUsername(), users.get(4).getUsername());
+ assertEquals(aa4.getUsername(), users.get(3).getUsername());
+ assertEquals(aa3.getUsername(), users.get(2).getUsername());
+ assertEquals(aa2.getUsername(), users.get(1).getUsername());
+ assertEquals(aa1.getUsername(), users.get(0).getUsername());
+
+ } finally {
+ USER_SERVICE.search(new AnyQuery.Builder().fiql(
+
SyncopeClient.getUserSearchConditionBuilder().is("ctype").equalTo("aa*").query())
+ .size(10)
+ .page(1)
+ .orderBy("ctype DESC")
+ .build()).getResult().forEach(u -> deleteUser(u.getKey()));
+ }
+ }
}
