This is an automated email from the ASF dual-hosted git repository.
ilgrosso pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/syncope.git
The following commit(s) were added to refs/heads/master by this push:
new b213144f0e [SYNCOPE-1982] Improve query perfomance on
OraclePlainSchemaRepoExtImpl (#1450)
b213144f0e is described below
commit b213144f0ea227963c1561c80f54d442cb1f4d78
Author: Valerio Crescia <[email protected]>
AuthorDate: Sat Jul 11 08:44:35 2026 +0200
[SYNCOPE-1982] Improve query perfomance on OraclePlainSchemaRepoExtImpl
(#1450)
---
.../persistence/jpa/OraclePersistenceContext.java | 3 +-
.../persistence/jpa/dao/OracleJPAAnySearchDAO.java | 2 +-
.../jpa/dao/repo/OraclePlainSchemaRepoExtImpl.java | 59 ++++++++++++----------
3 files changed, 35 insertions(+), 29 deletions(-)
diff --git
a/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/OraclePersistenceContext.java
b/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/OraclePersistenceContext.java
index d49afa29b4..99b5ebacf3 100644
---
a/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/OraclePersistenceContext.java
+++
b/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/OraclePersistenceContext.java
@@ -103,9 +103,8 @@ public class OraclePersistenceContext {
public PlainSchemaRepoExt plainSchemaRepoExt(
final AnyUtilsFactory anyUtilsFactory,
final @Lazy ExternalResourceDAO resourceDAO,
- final @Lazy PlainSchemaDAO plainSchemaDAO,
final EntityManager entityManager) {
- return new OraclePlainSchemaRepoExtImpl(anyUtilsFactory, resourceDAO,
plainSchemaDAO, entityManager);
+ return new OraclePlainSchemaRepoExtImpl(anyUtilsFactory, resourceDAO,
entityManager);
}
}
diff --git
a/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/OracleJPAAnySearchDAO.java
b/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/OracleJPAAnySearchDAO.java
index bf2dedc071..7c84705360 100644
---
a/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/OracleJPAAnySearchDAO.java
+++
b/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/OracleJPAAnySearchDAO.java
@@ -49,7 +49,7 @@ public class OracleJPAAnySearchDAO extends
AbstractJPAAnySearchDAO {
* or JSON_TABLE(plainAttrs, '$[*]?(@.schema == "loginDate").values[*]' \
* COLUMNS valuez PATH '$.dateValue') AS loginDate
*/
- public static String from(final PlainSchema schema) {
+ protected static String from(final PlainSchema schema) {
return new StringBuilder("JSON_TABLE(plainAttrs, '$[*]?(@.schema ==
\"").append(schema.getKey()).append("\").").
append(schema.isUniqueConstraint() ? "uniqueValue" :
"values[*]").
append("' COLUMNS ").append(schema.isUniqueConstraint() ?
"uniqueValue" : "valuez").
diff --git
a/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/repo/OraclePlainSchemaRepoExtImpl.java
b/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/repo/OraclePlainSchemaRepoExtImpl.java
index d7f253165b..f056e9d082 100644
---
a/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/repo/OraclePlainSchemaRepoExtImpl.java
+++
b/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/repo/OraclePlainSchemaRepoExtImpl.java
@@ -22,8 +22,6 @@ import jakarta.persistence.EntityManager;
import jakarta.persistence.Query;
import org.apache.commons.lang3.StringUtils;
import org.apache.syncope.core.persistence.api.dao.ExternalResourceDAO;
-import org.apache.syncope.core.persistence.api.dao.NotFoundException;
-import org.apache.syncope.core.persistence.api.dao.PlainSchemaDAO;
import org.apache.syncope.core.persistence.api.entity.AnyUtils;
import org.apache.syncope.core.persistence.api.entity.AnyUtilsFactory;
import org.apache.syncope.core.persistence.api.entity.PlainAttrValue;
@@ -31,22 +29,19 @@ import
org.apache.syncope.core.persistence.api.entity.PlainSchema;
import org.apache.syncope.core.persistence.jpa.dao.OracleJPAAnySearchDAO;
import org.apache.syncope.core.persistence.jpa.dao.SearchSupport;
import org.apache.syncope.core.persistence.jpa.entity.JPARealm;
+import org.apache.syncope.core.provisioning.api.serialization.POJOHelper;
public class OraclePlainSchemaRepoExtImpl extends AbstractPlainSchemaRepoExt {
protected static final String HAS_ATTRS_QUERY = "SELECT COUNT(id) AS
counts FROM %TABLE% "
+ "WHERE JSON_EXISTS(plainAttrs, '$[*]?(@.schema ==
\"%SCHEMA%\")')";
- protected final PlainSchemaDAO plainSchemaDAO;
-
public OraclePlainSchemaRepoExtImpl(
final AnyUtilsFactory anyUtilsFactory,
final ExternalResourceDAO resourceDAO,
- final PlainSchemaDAO plainSchemaDAO,
final EntityManager entityManager) {
super(anyUtilsFactory, resourceDAO, entityManager);
- this.plainSchemaDAO = plainSchemaDAO;
}
@Override
@@ -54,22 +49,39 @@ public class OraclePlainSchemaRepoExtImpl extends
AbstractPlainSchemaRepoExt {
return hasAttrs(schema, HAS_ATTRS_QUERY, StringUtils.EMPTY);
}
+ protected boolean existsPlainAttrUniqueValue(
+ final String table,
+ final String key,
+ final PlainSchema schema,
+ final PlainAttrValue attrValue) {
+
+ String queryString = new StringBuilder("SELECT COUNT(id) FROM
").append(table).
+ append(" WHERE ").
+ append("JSON_EXISTS(plainAttrs, '$[*]?(@.schema ==
\"").append(schema.getKey()).append("\" ").
+ append("&&
@.uniqueValue.").append(OracleJPAAnySearchDAO.key(schema.getType())).append("
== $value)' ").
+ append("PASSING ?1 AS \"value\") AND id <> ?2").
+ toString();
+
+ Query query = entityManager.createNativeQuery(queryString);
+
+ query.setParameter(1, StringUtils.stripEnd(StringUtils.stripStart(
+ POJOHelper.serialize(attrValue.getValue()), "\""), "\""));
+ query.setParameter(2, key);
+
+ return ((Number) query.getSingleResult()).longValue() > 0;
+ }
+
@Override
public boolean existsPlainAttrUniqueValue(
final String realmKey,
final PlainSchema schema,
final PlainAttrValue attrValue) {
- Query query = entityManager.createNativeQuery(
- "SELECT COUNT(id) FROM "
- + JPARealm.TABLE + ","
- +
OracleJPAAnySearchDAO.from(plainSchemaDAO.findById(schema.getKey()).
- orElseThrow(() -> new NotFoundException("PlainSchema "
+ schema.getKey())))
- + " WHERE " + schema.getKey() + ".uniqueValue=?1 AND id <>
?2");
- query.setParameter(1, attrValue.getValue());
- query.setParameter(2, realmKey);
-
- return ((Number) query.getSingleResult()).longValue() > 0;
+ return existsPlainAttrUniqueValue(
+ JPARealm.TABLE,
+ realmKey,
+ schema,
+ attrValue);
}
@Override
@@ -79,15 +91,10 @@ public class OraclePlainSchemaRepoExtImpl extends
AbstractPlainSchemaRepoExt {
final PlainSchema schema,
final PlainAttrValue attrValue) {
- Query query = entityManager.createNativeQuery(
- "SELECT COUNT(id) FROM "
- + new SearchSupport(anyUtils.anyTypeKind()).table().name() +
","
- +
OracleJPAAnySearchDAO.from(plainSchemaDAO.findById(schema.getKey()).
- orElseThrow(() -> new NotFoundException("PlainSchema "
+ schema.getKey())))
- + " WHERE " + schema.getKey() + ".uniqueValue=?1 AND id <>
?2");
- query.setParameter(1, attrValue.getValue());
- query.setParameter(2, anyKey);
-
- return ((Number) query.getSingleResult()).longValue() > 0;
+ return existsPlainAttrUniqueValue(
+ new SearchSupport(anyUtils.anyTypeKind()).table().name(),
+ anyKey,
+ schema,
+ attrValue);
}
}
