This is an automated email from the ASF dual-hosted git repository.
andreapatricelli pushed a commit to branch 3_0_X
in repository https://gitbox.apache.org/repos/asf/syncope.git
The following commit(s) were added to refs/heads/3_0_X by this push:
new 7aefc22275 [SYNCOPE-1906] fixed order by also on unique attributes
(#1174)
7aefc22275 is described below
commit 7aefc2227546fc7842b19976299f29d2502daf59
Author: Andrea Patricelli <[email protected]>
AuthorDate: Wed Sep 10 10:01:15 2025 +0200
[SYNCOPE-1906] fixed order by also on unique attributes (#1174)
---
.../persistence/jpa/dao/MaJPAJSONAnySearchDAO.java | 30 +++++
.../persistence/jpa/dao/MyJPAJSONAnySearchDAO.java | 6 +-
.../core/persistence/jpa/dao/JPAAnySearchDAO.java | 12 +-
.../core/persistence/jpa/inner/AnySearchTest.java | 13 ++
.../apache/syncope/fit/core/UserIssuesITCase.java | 137 +++++++++++----------
5 files changed, 124 insertions(+), 74 deletions(-)
diff --git
a/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/MaJPAJSONAnySearchDAO.java
b/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/MaJPAJSONAnySearchDAO.java
index 00fcbdce4d..7238fb5602 100644
---
a/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/MaJPAJSONAnySearchDAO.java
+++
b/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/MaJPAJSONAnySearchDAO.java
@@ -30,6 +30,7 @@ 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.UserDAO;
import org.apache.syncope.core.persistence.api.dao.search.AttrCond;
+import org.apache.syncope.core.persistence.api.dao.search.OrderByClause;
import org.apache.syncope.core.persistence.api.entity.AnyUtilsFactory;
import org.apache.syncope.core.persistence.api.entity.EntityFactory;
import org.apache.syncope.core.persistence.api.entity.JSONPlainAttr;
@@ -64,6 +65,35 @@ public class MaJPAJSONAnySearchDAO extends JPAAnySearchDAO {
validator);
}
+ @Override
+ protected void parseOrderByForPlainSchema(
+ final SearchSupport svs,
+ final OrderBySupport obs,
+ final OrderBySupport.Item item,
+ final OrderByClause 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 AnySearchNode getQuery(
final AttrCond cond,
diff --git
a/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/MyJPAJSONAnySearchDAO.java
b/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/MyJPAJSONAnySearchDAO.java
index fe61bec605..790e5d7fae 100644
---
a/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/MyJPAJSONAnySearchDAO.java
+++
b/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/MyJPAJSONAnySearchDAO.java
@@ -80,9 +80,9 @@ public class MyJPAJSONAnySearchDAO extends JPAAnySearchDAO {
obs.views.add(svs.field());
- item.select = new StringBuilder().append("( SELECT usa").
- append('.').
- append((schema.isUniqueConstraint() ? "attrUniqueValue" :
key(schema.getType()))).
+ 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 = ").
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 5ec0e795b7..ba0a7dda41 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
@@ -995,9 +995,15 @@ public class JPAAnySearchDAO extends AbstractAnySearchDAO {
obs.views.add(svs.asSearchViewSupport().uniqueAttr());
item.select = new StringBuilder().
-
append(svs.asSearchViewSupport().uniqueAttr().alias).append('.').
- append(key(schema.getType())).
- append(" AS ").append(fieldName).toString();
+ append("( SELECT
usa").append('.').append(key(schema.getType())).
+ append(" FROM ").
+ append(svs.asSearchViewSupport().uniqueAttr().name).
+ append(" usa WHERE usa.any_id = ").
+ append(svs.asSearchViewSupport().uniqueAttr().alias).
+ append(".any_id").
+ append(" AND usa.schema_id
='").append(fieldName).append("'").
+ append(isOracle() ? " FETCH FIRST 1 ROWS ONLY " : " LIMIT
1").
+ append(") AS ").append(fieldName).toString();
item.where = new StringBuilder().
append(svs.asSearchViewSupport().uniqueAttr().alias).
append(".schema_id='").append(fieldName).append("'").toString();
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 5f917efca4..e49f0d6aba 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
@@ -1052,6 +1052,19 @@ public class AnySearchTest extends AbstractTest {
assertFalse(users.isEmpty());
assertEquals(2, users.size());
+
+ // order by unique attribute
+ OrderByClause orderByFullname = new OrderByClause();
+ orderByFullname.setField("fullname");
+ orderByFullname.setDirection(OrderByClause.Direction.DESC);
+
+ users = searchDAO.search(SearchCond.getLeaf(idCond),
List.of(orderByFullname), 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) {
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 79d23ba03c..b142ec2c29 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
@@ -1573,20 +1573,13 @@ public class UserIssuesITCase extends AbstractITCase {
// 2. remove resources, auxiliary classes and roles
userUR.getResources().clear();
- userUR.getResources().add(new StringPatchItem.Builder()
- .value(RESOURCE_NAME_TESTDB)
- .operation(PatchOperation.DELETE)
- .build());
+ userUR.getResources().add(new
StringPatchItem.Builder().value(RESOURCE_NAME_TESTDB)
+ .operation(PatchOperation.DELETE).build());
userUR.getAuxClasses().clear();
- userUR.getAuxClasses().add(new StringPatchItem.Builder()
- .value("csv")
- .operation(PatchOperation.DELETE)
+ userUR.getAuxClasses().add(new
StringPatchItem.Builder().value("csv").operation(PatchOperation.DELETE)
.build());
userUR.getRoles().clear();
- userUR.getRoles().add(new StringPatchItem.Builder()
- .value("Other")
- .operation(PatchOperation.DELETE)
- .build());
+ userUR.getRoles().add(new
StringPatchItem.Builder().value("Other").operation(PatchOperation.DELETE).build());
updateUser(userUR);
UserTO userTO =
USER_SERVICE.read("1417acbe-cbf6-4277-9372-e75e04f97000");
@@ -1603,8 +1596,7 @@ public class UserIssuesITCase extends AbstractITCase {
req.setUsername(new
StringReplacePatchItem.Builder().value("newUsername" +
getUUIDString()).build());
WebClient webClient = WebClient.create(ADDRESS + "/users/" +
userTO.getKey(), ADMIN_UNAME, ADMIN_PWD, null).
- accept(MediaType.APPLICATION_JSON_TYPE).
- type(MediaType.APPLICATION_JSON_TYPE);
+
accept(MediaType.APPLICATION_JSON_TYPE).type(MediaType.APPLICATION_JSON_TYPE);
Response response = webClient.invoke(HttpMethod.PATCH,
JSON_MAPPER.writeValueAsString(req));
assertEquals(Response.Status.OK.getStatusCode(), response.getStatus());
@@ -1736,10 +1728,7 @@ public class UserIssuesITCase extends AbstractITCase {
// 2. pull users from resource-db-pull
ExecTO execution =
AbstractTaskITCase.execProvisioningTask(TASK_SERVICE,
- TaskType.PULL,
- "7c2242f4-14af-4ab5-af31-cdae23783655",
- MAX_WAIT_SECONDS,
- false);
+ TaskType.PULL, "7c2242f4-14af-4ab5-af31-cdae23783655",
MAX_WAIT_SECONDS, false);
assertEquals("SUCCESS", execution.getStatus());
assertFalse(rossini.isSuspended());
assertEquals("active", rossini.getStatus());
@@ -1761,12 +1750,8 @@ public class UserIssuesITCase extends AbstractITCase {
+ "'false' WHERE USERNAME = 'rossini'");
// 5. pull again rossini from resource-db-pull
- execution = AbstractTaskITCase.execProvisioningTask(
- TASK_SERVICE,
- TaskType.PULL,
- "7c2242f4-14af-4ab5-af31-cdae23783655",
- MAX_WAIT_SECONDS,
- false);
+ execution = AbstractTaskITCase.execProvisioningTask(TASK_SERVICE,
TaskType.PULL,
+ "7c2242f4-14af-4ab5-af31-cdae23783655", MAX_WAIT_SECONDS,
false);
assertEquals("SUCCESS", execution.getStatus());
rossini = USER_SERVICE.read("rossini");
@@ -1785,11 +1770,7 @@ public class UserIssuesITCase extends AbstractITCase {
// 7. pull again rossini from resource-db-pull
execution = AbstractTaskITCase.execProvisioningTask(
- TASK_SERVICE,
- TaskType.PULL,
- "7c2242f4-14af-4ab5-af31-cdae23783655",
- MAX_WAIT_SECONDS,
- false);
+ TASK_SERVICE, TaskType.PULL,
"7c2242f4-14af-4ab5-af31-cdae23783655", MAX_WAIT_SECONDS, false);
assertEquals("SUCCESS", execution.getStatus());
rossini = USER_SERVICE.read("rossini");
@@ -1809,8 +1790,7 @@ public class UserIssuesITCase extends AbstractITCase {
} finally {
// restore attributes and (if needed) status
updateUser(new UserUR.Builder(rossini.getKey()).
- plainAttrs(
- attrAddReplacePatch("surname", "Rossini"),
+ plainAttrs(attrAddReplacePatch("surname", "Rossini"),
new AttrPatch.Builder(
new
Attr.Builder("email").build()).operation(PatchOperation.DELETE).build()).
resource(new StringPatchItem.Builder().
@@ -1846,16 +1826,12 @@ public class UserIssuesITCase extends AbstractITCase {
// 3. propagation tasks cleanup
TASK_SERVICE.search(new TaskQuery.Builder(TaskType.PROPAGATION)
- .anyTypeKind(AnyTypeKind.USER)
- .resource(RESOURCE_NAME_LDAP)
- .entityKey("c9b2dec2-00a7-4855-97c0-d854842b4b24")
- .build()).getResult()
+ .anyTypeKind(AnyTypeKind.USER).resource(RESOURCE_NAME_LDAP)
+
.entityKey("c9b2dec2-00a7-4855-97c0-d854842b4b24").build()).getResult()
.forEach(pt -> TASK_SERVICE.delete(TaskType.PROPAGATION,
pt.getKey()));
TASK_SERVICE.search(new TaskQuery.Builder(TaskType.PROPAGATION)
- .anyTypeKind(AnyTypeKind.USER)
- .resource(RESOURCE_NAME_LDAP)
- .entityKey("b3cbc78d-32e6-4bd4-92e0-bbe07566a2ee")
- .build()).getResult()
+ .anyTypeKind(AnyTypeKind.USER).resource(RESOURCE_NAME_LDAP)
+
.entityKey("b3cbc78d-32e6-4bd4-92e0-bbe07566a2ee").build()).getResult()
.forEach(pt -> TASK_SERVICE.delete(TaskType.PROPAGATION,
pt.getKey()));
// 4. delete group cGroupForPropagation: no deprovision should be
fired on bellini, since there is already
@@ -1863,8 +1839,7 @@ public class UserIssuesITCase extends AbstractITCase {
GROUP_SERVICE.delete(cGroupForPropagation.getKey());
await().during(5, TimeUnit.SECONDS).atMost(MAX_WAIT_SECONDS,
TimeUnit.SECONDS).until(
() -> TASK_SERVICE.search(new
TaskQuery.Builder(TaskType.PROPAGATION)
- .anyTypeKind(AnyTypeKind.USER)
- .resource(RESOURCE_NAME_LDAP)
+
.anyTypeKind(AnyTypeKind.USER).resource(RESOURCE_NAME_LDAP)
.entityKey("c9b2dec2-00a7-4855-97c0-d854842b4b24").build())
.getResult().stream().map(PropagationTaskTO.class::cast)
.collect(Collectors.toList()).stream().noneMatch(pt ->
ResourceOperation.DELETE == pt.
@@ -1872,8 +1847,7 @@ public class UserIssuesITCase extends AbstractITCase {
GROUP_SERVICE.delete(dGroupForPropagation.getKey());
await().atMost(MAX_WAIT_SECONDS, TimeUnit.SECONDS).until(
() -> TASK_SERVICE.search(new
TaskQuery.Builder(TaskType.PROPAGATION)
- .anyTypeKind(AnyTypeKind.USER)
- .resource(RESOURCE_NAME_LDAP)
+
.anyTypeKind(AnyTypeKind.USER).resource(RESOURCE_NAME_LDAP)
.entityKey("b3cbc78d-32e6-4bd4-92e0-bbe07566a2ee").build())
.getResult().stream().map(PropagationTaskTO.class::cast)
.collect(Collectors.toList()).stream().anyMatch(pt ->
ResourceOperation.DELETE == pt.
@@ -1886,7 +1860,7 @@ public class UserIssuesITCase extends AbstractITCase {
userWithDotSchema.setKey("user.testWithDot");
userWithDotSchema.setAnyTypeClass("minimal user");
SCHEMA_SERVICE.create(SchemaType.PLAIN, userWithDotSchema);
-
+
ResourceTO ldap = RESOURCE_SERVICE.read(RESOURCE_NAME_LDAP);
ldap.setKey("ldapWithDot");
@@ -1928,53 +1902,61 @@ public class UserIssuesITCase extends AbstractITCase {
@Test
public void issueSYNCOPE1906() {
UserCR userCR =
UserITCase.getUniqueSample("[email protected]");
- userCR.getPlainAttrs().removeIf(attr ->
"ctype".equals(attr.getSchema()));
+ 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()));
+ 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()));
+ 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()));
+ 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()));
+ 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()));
+ userCR = UserITCase.getUniqueSample("[email protected]");
+ userCR.getPlainAttrs()
+ .removeIf(attr -> "ctype".equals(attr.getSchema()) ||
"fullname".equals(attr.getSchema()));
userCR.getPlainAttrs().add(attr("ctype", "a ctype"));
- createUser(userCR).getEntity();
+ userCR.getPlainAttrs().add(attr("fullname", "a fullname"));
+ createUser(userCR);
- userCR =
UserITCase.getUniqueSample("[email protected]");
- userCR.getPlainAttrs().removeIf(attr ->
"ctype".equals(attr.getSchema()));
+ 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"));
- createUser(userCR).getEntity();
+ userCR.getPlainAttrs().add(attr("fullname", "a fullname 2"));
+ createUser(userCR);
try {
await().until(() -> USER_SERVICE.search(new
AnyQuery.Builder().fiql(
-
SyncopeClient.getUserSearchConditionBuilder().is("ctype").equalTo("aa*").query())
- .size(0)
- .page(1)
- .orderBy("ctype DESC")
- .build()).getTotalCount() == 5);
+
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("ctype").equalTo("aa*").query())
- .size(10)
- .page(1)
- .orderBy("ctype DESC")
- .build()).getResult();
+
SyncopeClient.getUserSearchConditionBuilder().is("email").equalTo("*issuesyncope1906*")
+ .query()).size(10).page(1).orderBy("ctype
DESC").build()).getResult();
assertEquals(5, users.size());
@@ -1983,13 +1965,32 @@ public class UserIssuesITCase extends AbstractITCase {
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()));
+ .size(10).page(1).orderBy("ctype
DESC").build()).getResult().forEach(u -> deleteUser(u.getKey()));
}
}
}
