You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airavata.apache.org by is...@apache.org on 2023/02/01 04:51:55 UTC
[airavata] branch develop updated: Add sql subquery based filtering
This is an automated email from the ASF dual-hosted git repository.
isjarana pushed a commit to branch develop
in repository https://gitbox.apache.org/repos/asf/airavata.git
The following commit(s) were added to refs/heads/develop by this push:
new 453cced2c1 Add sql subquery based filtering
new e4a06b0f4c Merge pull request #377 from isururanawaka/metaschedular
453cced2c1 is described below
commit 453cced2c1d9c8cbcb54c9bf0cd9212fd1992167
Author: Isuru Ranawaka <ir...@gmail.com>
AuthorDate: Tue Jan 31 23:49:51 2023 -0500
Add sql subquery based filtering
---
.../core/repositories/AbstractRepository.java | 31 +++++++++++++++++++++-
.../repositories/expcatalog/JobRepository.java | 2 +-
.../expcatalog/JobStatusRepository.java | 7 ++---
.../registry/core/utils/QueryConstants.java | 6 ++---
4 files changed, 36 insertions(+), 10 deletions(-)
diff --git a/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/repositories/AbstractRepository.java b/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/repositories/AbstractRepository.java
index 48fc4a41b0..a530e47393 100644
--- a/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/repositories/AbstractRepository.java
+++ b/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/repositories/AbstractRepository.java
@@ -155,7 +155,7 @@ public abstract class AbstractRepository<T, E, Id> {
}
- public void execute(String query, String... params) {
+ public void executeWithNativeQuery(String query, String... params) {
EntityManager entityManager = null;
try {
entityManager = getEntityManager();
@@ -185,6 +185,35 @@ public abstract class AbstractRepository<T, E, Id> {
}
+
+ public List selectWithNativeQuery(String query, String... params) {
+ EntityManager entityManager = null;
+ try {
+ entityManager = getEntityManager();
+ } catch (Exception e) {
+ logger.error("Failed to get EntityManager", e);
+ throw new RuntimeException("Failed to get EntityManager", e);
+ }
+ try {
+ Query nativeQuery = entityManager.createNativeQuery(query);
+ for(int i=0;i<params.length;i++){
+ nativeQuery.setParameter((i+1),params[i]);
+ }
+ return nativeQuery.getResultList();
+ } catch(Exception e) {
+ logger.error("Failed to execute transaction", e);
+ throw e;
+ }finally {
+ if (entityManager != null && entityManager.isOpen()) {
+ if (entityManager.getTransaction().isActive()) {
+ entityManager.getTransaction().rollback();
+ }
+ entityManager.close();
+ }
+ }
+
+ }
+
abstract protected EntityManager getEntityManager();
}
diff --git a/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/repositories/expcatalog/JobRepository.java b/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/repositories/expcatalog/JobRepository.java
index 7f76f66dbe..37081f64fb 100644
--- a/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/repositories/expcatalog/JobRepository.java
+++ b/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/repositories/expcatalog/JobRepository.java
@@ -157,7 +157,7 @@ public class JobRepository extends ExpCatAbstractRepository<JobModel, JobEntity,
}
public void removeJob(JobModel jobModel) throws RegistryException {
- execute(QueryConstants.DELETE_JOB_NATIVE_QUERY,jobModel.getJobId(),jobModel.getTaskId());
+ executeWithNativeQuery(QueryConstants.DELETE_JOB_NATIVE_QUERY,jobModel.getJobId(),jobModel.getTaskId());
}
diff --git a/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/repositories/expcatalog/JobStatusRepository.java b/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/repositories/expcatalog/JobStatusRepository.java
index e2bc97e277..adb4558d66 100644
--- a/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/repositories/expcatalog/JobStatusRepository.java
+++ b/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/repositories/expcatalog/JobStatusRepository.java
@@ -110,10 +110,7 @@ public class JobStatusRepository extends ExpCatAbstractRepository<JobStatus, Job
public List<JobStatus> getDistinctListofJobStatus(String status, String gatewayId, double time){
JobStatusRepository jobStatusRepository = new JobStatusRepository();
- Map<String, Object> queryParameters = new HashMap<>();
- queryParameters.put(DBConstants.Job.JOB_STATUS, status);
- queryParameters.put(DBConstants.Job.GATEWAY_ID,gatewayId);
- queryParameters.put(DBConstants.Job.TIME_INTERVAL, String.valueOf(time));
- return jobStatusRepository.select(QueryConstants.FIND_JOB_COUNT, -1, 0, queryParameters);
+ return jobStatusRepository.selectWithNativeQuery(QueryConstants.FIND_JOB_COUNT_NATIVE_QUERY,
+ status,gatewayId,String.valueOf(time));
}
}
diff --git a/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/utils/QueryConstants.java b/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/utils/QueryConstants.java
index 797ba1eb92..84f2f143f5 100644
--- a/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/utils/QueryConstants.java
+++ b/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/utils/QueryConstants.java
@@ -199,11 +199,11 @@ public interface QueryConstants {
String DELETE_JOB_NATIVE_QUERY = "DELETE FROM JOB WHERE JOB_ID = ?1 AND TASK_ID = ?2";
- String FIND_JOB_COUNT = "SELECT DISTINCT JS FROM "+ JobStatusEntity.class.getSimpleName()+" JS WHERE JS.JOB_ID IN " +
+ String FIND_JOB_COUNT_NATIVE_QUERY = "SELECT DISTINCT JS FROM "+ JobStatusEntity.class.getSimpleName()+" JS WHERE JS.JOB_ID IN " +
"(SELECT J.JOB_ID FROM "+ JobEntity.class.getSimpleName()+" J where J.PROCESS_ID IN " +
"(SELECT P.PROCESS_ID FROM "+ ProcessEntity.class.getSimpleName()+ " P where P.EXPERIMENT_ID IN " +
- "(SELECT E.EXPERIMENT_ID FROM "+ExperimentEntity.class.getSimpleName()+" E where E.GATEWAY_ID= :"+DBConstants.Job.GATEWAY_ID+" ))) " +
- "AND JS.STATE= :"+ DBConstants.Job.JOB_STATUS + " and JS.TIME_OF_STATE_CHANGE > now() - interval :"+DBConstants.Job.TIME_INTERVAL +" minute";
+ "(SELECT E.EXPERIMENT_ID FROM "+ExperimentEntity.class.getSimpleName()+" E where E.GATEWAY_ID= ?1))) " +
+ "AND JS.STATE = ?2 and JS.TIME_OF_STATE_CHANGE > now() - interval ?3 minute";
}