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 <[email protected]>
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";
}
