modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/utils/QueryConstants.java - airavata - Git at Google

 /**
  * Licensed to the Apache Software Foundation (ASF) under one
  * or more contributor license agreements.  See the NOTICE file
  * distributed with this work for additional information
  * regarding copyright ownership.  The ASF licenses this file
  * to you under the Apache License, Version 2.0 (the
  * "License"); you may not use this file except in compliance
  * with the License.  You may obtain a copy of the License at
  * <p>
  * http://www.apache.org/licenses/LICENSE-2.0
  * <p>
  * Unless required by applicable law or agreed to in writing,
  * software distributed under the License is distributed on an
  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
  * KIND, either express or implied.  See the License for the
  * specific language governing permissions and limitations
  * under the License.
  */
 package org.apache.airavata.registry.core.utils;

 import org.apache.airavata.model.status.JobStatus;
 import org.apache.airavata.model.status.QueueStatusModel;
 import org.apache.airavata.model.user.UserProfile;
 import org.apache.airavata.registry.core.entities.airavataworkflowcatalog.AiravataWorkflowEntity;
 import org.apache.airavata.registry.core.entities.appcatalog.*;
 import org.apache.airavata.registry.core.entities.expcatalog.*;
 import org.apache.airavata.registry.core.entities.replicacatalog.DataProductEntity;

 import javax.print.attribute.standard.JobState;

 public interface QueryConstants {

     String FIND_USER_PROFILE_BY_USER_ID = "SELECT u FROM UserProfileEntity u " +
             "where u.userId LIKE :" + UserProfile._Fields.USER_ID.getFieldName() + " " +
             "AND u.gatewayId LIKE :" + UserProfile._Fields.GATEWAY_ID.getFieldName() + "";

     String FIND_ALL_USER_PROFILES_BY_GATEWAY_ID = "SELECT u FROM UserProfileEntity u " +
             "where u.gatewayId LIKE :" + UserProfile._Fields.GATEWAY_ID.getFieldName() + "";

     // Application Deployment Queries
     String FIND_APPLICATION_DEPLOYMENTS_FOR_GATEWAY_ID = "SELECT AD FROM " + ApplicationDeploymentEntity.class.getSimpleName() + " AD " +
             "WHERE AD.gatewayId LIKE :" + DBConstants.ApplicationDeployment.GATEWAY_ID;
     String FIND_APPLICATION_DEPLOYMENTS_FOR_APPLICATION_MODULE_ID = "SELECT AD FROM " + ApplicationDeploymentEntity.class.getSimpleName() + " AD " +
             "WHERE AD.appModuleId LIKE :" + DBConstants.ApplicationDeployment.APPLICATION_MODULE_ID;
     String FIND_APPLICATION_DEPLOYMENTS_FOR_COMPUTE_HOST_ID = "SELECT AD FROM " + ApplicationDeploymentEntity.class.getSimpleName() + " AD " +
             "WHERE AD.computeHostId LIKE :" + DBConstants.ApplicationDeployment.COMPUTE_HOST_ID;
     String GET_ALL_APPLICATION_DEPLOYMENTS = "SELECT AD FROM " + ApplicationDeploymentEntity.class.getSimpleName() + " AD";
     String FIND_ACCESSIBLE_APPLICATION_DEPLOYMENTS = "SELECT AD FROM " + ApplicationDeploymentEntity.class.getSimpleName() + " AD " +
             "WHERE AD.gatewayId LIKE :" + DBConstants.ApplicationDeployment.GATEWAY_ID + " AND AD.appDeploymentId IN :" +
             DBConstants.ApplicationDeployment.ACCESSIBLE_APPLICATION_DEPLOYMENT_IDS + " AND AD.computeHostId IN :" +
             DBConstants.ApplicationDeployment.ACCESSIBLE_COMPUTE_HOST_IDS;
     String FIND_ACCESSIBLE_APPLICATION_DEPLOYMENTS_FOR_APP_MODULE = "SELECT AD FROM " + ApplicationDeploymentEntity.class.getSimpleName() + " AD " +
             "WHERE AD.gatewayId LIKE :" + DBConstants.ApplicationDeployment.GATEWAY_ID + " AND AD.appDeploymentId IN :" +
             DBConstants.ApplicationDeployment.ACCESSIBLE_APPLICATION_DEPLOYMENT_IDS + " AND AD.computeHostId IN :" +
             DBConstants.ApplicationDeployment.ACCESSIBLE_COMPUTE_HOST_IDS + " AND AD.appModuleId = :" +
             DBConstants.ApplicationDeployment.APPLICATION_MODULE_ID;

     // Application Module Queries
     String FIND_APPLICATION_MODULES_FOR_GATEWAY_ID = "SELECT AM FROM " + ApplicationModuleEntity.class.getSimpleName() + " AM " +
             "WHERE AM.gatewayId LIKE :" + DBConstants.ApplicationModule.GATEWAY_ID;
     String FIND_APPLICATION_MODULES_FOR_APPLICATION_MODULE_NAME = "SELECT AM FROM " + ApplicationModuleEntity.class.getSimpleName() + " AM " +
             "WHERE AM.appModuleName LIKE :" + DBConstants.ApplicationModule.APPLICATION_MODULE_NAME;
     String FIND_ACCESSIBLE_APPLICATION_MODULES = "SELECT AM FROM " + ApplicationModuleEntity.class.getSimpleName() + " AM " +
             "WHERE AM.gatewayId LIKE :" + DBConstants.ApplicationModule.GATEWAY_ID + " AND " +
             "EXISTS (SELECT 1 FROM " + ApplicationDeploymentEntity.class.getSimpleName() + " AD " +
             "WHERE AM.appModuleId = AD.appModuleId AND AD.appDeploymentId IN :" + DBConstants.ApplicationDeployment.ACCESSIBLE_APPLICATION_DEPLOYMENT_IDS +
             " AND AD.computeHostId IN :" + DBConstants.ApplicationDeployment.ACCESSIBLE_COMPUTE_HOST_IDS + ")";

     // Application Interface Queries
     String FIND_APPLICATION_INTERFACES_FOR_GATEWAY_ID = "SELECT AI FROM " + ApplicationInterfaceEntity.class.getSimpleName() + " AI " +
             "WHERE AI.gatewayId LIKE :" + DBConstants.ApplicationInterface.GATEWAY_ID;
     String FIND_APPLICATION_INTERFACES_FOR_APPLICATION_NAME = "SELECT AI FROM " + ApplicationInterfaceEntity.class.getSimpleName() + " AI " +
             "WHERE AI.applicationName LIKE :" + DBConstants.ApplicationInterface.APPLICATION_NAME;
     String GET_ALL_APPLICATION_INTERFACES = "SELECT AI FROM " + ApplicationInterfaceEntity.class.getSimpleName() + " AI";

     // Application Inputs Queries
     String FIND_APPLICATION_INPUTS = "SELECT AI FROM " + ApplicationInputEntity.class.getSimpleName() + " AI " +
             "WHERE AI.interfaceId LIKE :" + DBConstants.ApplicationInput.APPLICATION_INTERFACE_ID;

     // Application Outputs Queries
     String FIND_APPLICATION_OUTPUTS = "SELECT AI FROM " + ApplicationOutputEntity.class.getSimpleName() + " AI " +
             "WHERE AI.interfaceId LIKE :" + DBConstants.ApplicationOutput.APPLICATION_INTERFACE_ID;

     String FIND_ALL_GATEWAY_PROFILES = "SELECT G FROM " + GatewayProfileEntity.class.getSimpleName() + " G";
     String FIND_ALL_COMPUTE_RESOURCE_PREFERENCES = "SELECT DISTINCT CR FROM " + ComputeResourcePreferenceEntity.class.getSimpleName() + " CR " +
             "WHERE CR.gatewayId LIKE :" + DBConstants.ComputeResourcePreference.GATEWAY_ID;
     String FIND_ALL_STORAGE_RESOURCE_PREFERENCES = "SELECT DISTINCT S FROM " + StoragePreferenceEntity.class.getSimpleName() + " S " +
             "WHERE S.gatewayId LIKE :" + DBConstants.StorageResourcePreference.GATEWAY_ID;

     String FIND_COMPUTE_RESOURCE = "SELECT DISTINCT CR FROM " + ComputeResourceEntity.class.getSimpleName() + " CR " +
             "WHERE CR.hostName LIKE :" + DBConstants.ComputeResource.HOST_NAME;
     String FIND_ALL_COMPUTE_RESOURCES = "SELECT CR FROM " + ComputeResourceEntity.class.getSimpleName() + " CR";
     String GET_FILE_SYSTEM = "SELECT DISTINCT FS FROM " + ComputeResourceFileSystemEntity.class.getSimpleName() + " FS " +
             "WHERE FS.computeResourceId LIKE :" + DBConstants.ComputeResource.COMPUTE_RESOURCE_ID;
     String GET_JOB_MANAGER_COMMAND = "SELECT DISTINCT JM FROM " + JobManagerCommandEntity.class.getSimpleName() + " JM " +
             "WHERE JM.resourceJobManagerId LIKE :" + DBConstants.ResourceJobManager.RESOURCE_JOB_MANAGER_ID;
     String GET_PARALLELISM_PREFIX = "SELECT DISTINCT PF FROM " + ParallelismCommandEntity.class.getSimpleName() + " PF " +
             "WHERE PF.resourceJobManagerId LIKE :" + DBConstants.ResourceJobManager.RESOURCE_JOB_MANAGER_ID;

     String FIND_ACCESSIBLE_GROUP_RESOURCE_PROFILES = "SELECT G FROM " + GroupResourceProfileEntity.class.getSimpleName() + " G " +
             "WHERE G.gatewayId LIKE :" + DBConstants.GroupResourceProfile.GATEWAY_ID + " AND G.groupResourceProfileId IN :"
             + DBConstants.GroupResourceProfile.ACCESSIBLE_GROUP_RESOURCE_IDS;
     String FIND_ALL_GROUP_COMPUTE_PREFERENCES = "SELECT GC FROM " + GroupComputeResourcePrefEntity.class.getSimpleName() + " GC " +
             "WHERE GC.groupResourceProfileId LIKE :" + DBConstants.GroupResourceProfile.GROUP_RESOURCE_PROFILE_ID;
     String FIND_ALL_GROUP_BATCH_QUEUE_RESOURCE_POLICY = "SELECT BQ FROM " + BatchQueueResourcePolicyEntity.class.getSimpleName() + " BQ " +
             "WHERE BQ.groupResourceProfileId LIKE :" + DBConstants.GroupResourceProfile.GROUP_RESOURCE_PROFILE_ID;
     String FIND_ALL_GROUP_COMPUTE_RESOURCE_POLICY = "SELECT CR FROM " + ComputeResourcePolicyEntity.class.getSimpleName() + " CR " +
             "WHERE CR.groupResourceProfileId LIKE :" + DBConstants.GroupResourceProfile.GROUP_RESOURCE_PROFILE_ID;

     String GET_ALL_USER_RESOURCE_PROFILE = "SELECT URP FROM " + UserResourceProfileEntity.class.getSimpleName() + " URP";
     String GET_ALL_GATEWAY_ID = "SELECT DISTINCT URP FROM " + UserResourceProfileEntity.class.getSimpleName() + " URP " +
             "WHERE URP.gatewayId LIKE :" + DBConstants.UserResourceProfile.GATEWAY_ID;

     String GET_ALL_GATEWAYS = "SELECT G FROM " + GatewayEntity.class.getSimpleName() + " G";
     String GET_GATEWAY_FROM_GATEWAY_NAME = "SELECT G FROM " + GatewayEntity.class.getSimpleName() + " G " +
             "WHERE G.gatewayName LIKE :" + DBConstants.Gateway.GATEWAY_NAME;

     String GET_ALL_GATEWAY_NOTIFICATIONS = "SELECT N FROM " + NotificationEntity.class.getSimpleName() + " N " +
             "WHERE N.gatewayId LIKE :" + DBConstants.Notification.GATEWAY_ID;

     String GET_ALL_GATEWAY_USERS = "SELECT U FROM " + UserEntity.class.getSimpleName() + " U " +
             "WHERE U.gatewayId LIKE :" + DBConstants.User.GATEWAY_ID;

     String GET_ALL_PROJECTS_FOR_OWNER = "SELECT P FROM " + ProjectEntity.class.getSimpleName() + " P " +
             "WHERE P.owner LIKE :" + DBConstants.Project.OWNER;

     String GET_EXPERIMENTS_FOR_USER = "SELECT E FROM " + ExperimentEntity.class.getSimpleName() + " E " +
             "WHERE E.userName LIKE :" + DBConstants.Experiment.USER_NAME +
             " AND E.gatewayId = :" + DBConstants.Experiment.GATEWAY_ID;
     String GET_EXPERIMENTS_FOR_PROJECT_ID = "SELECT E FROM " + ExperimentEntity.class.getSimpleName() + " E " +
             "WHERE E.projectId LIKE :" + DBConstants.Experiment.PROJECT_ID +
             " AND E.gatewayId = :" + DBConstants.Experiment.GATEWAY_ID;
     String GET_EXPERIMENTS_FOR_GATEWAY_ID = "SELECT E FROM " + ExperimentEntity.class.getSimpleName() + " E " +
             "WHERE E.gatewayId LIKE :" + DBConstants.Experiment.GATEWAY_ID;

     String GET_PROCESS_FOR_EXPERIMENT_ID = "SELECT P FROM " + ProcessEntity.class.getSimpleName() + " P " +
             "WHERE P.experimentId = :" + DBConstants.Process.EXPERIMENT_ID;

     String GET_TASK_FOR_PARENT_PROCESS_ID = "SELECT T FROM " + TaskEntity.class.getSimpleName() + " T " +
             "WHERE T.parentProcessId LIKE :" + DBConstants.Task.PARENT_PROCESS_ID;

     String GET_JOB_FOR_PROCESS_ID = "SELECT J FROM " + JobEntity.class.getSimpleName() + " J " +
             "WHERE J.processId LIKE :" + DBConstants.Job.PROCESS_ID;

     String GET_JOB_FOR_TASK_ID = "SELECT J FROM " + JobEntity.class.getSimpleName() + " J " +
             "WHERE J.taskId LIKE :" + DBConstants.Job.TASK_ID;

     String GET_JOB_FOR_JOB_ID = "SELECT J FROM " + JobEntity.class.getSimpleName() + " J " +
             "WHERE J.jobId LIKE :" + DBConstants.Job.JOB_ID;

     String GET_ALL_QUEUE_STATUS_MODELS = "SELECT QSM FROM " + QueueStatusEntity.class.getSimpleName() + " QSM";

     String GET_ALL_USER_COMPUTE_RESOURCE_PREFERENCE = "SELECT UCRP FROM " + UserComputeResourcePreferenceEntity.class.getSimpleName() + " UCRP " +
             "WHERE UCRP.userId LIKE :" + DBConstants.UserComputeResourcePreference.USER_ID + " AND UCRP.gatewayId LIKE :" +
             DBConstants.UserComputeResourcePreference.GATEWAY_ID;

     String GET_ALL_USER_STORAGE_PREFERENCE = "SELECT USP FROM " + UserStoragePreferenceEntity.class.getSimpleName() + " USP " +
             "WHERE USP.userId LIKE :" + DBConstants.UserStoragePreference.USER_ID + " AND USP.gatewayId LIKE :" +
             DBConstants.UserStoragePreference.GATEWAY_ID;

     String FIND_ALL_CHILD_DATA_PRODUCTS = "SELECT DP FROM " + DataProductEntity.class.getSimpleName() + " DP " +
             "WHERE DP.parentProductUri LIKE :" + DBConstants.DataProduct.PARENT_PRODUCT_URI;

     String FIND_DATA_PRODUCT_BY_NAME = "SELECT DP FROM " + DataProductEntity.class.getSimpleName() + " DP " +
             "WHERE DP.gatewayId LIKE :" + DBConstants.DataProduct.GATEWAY_ID + " AND DP.ownerName LIKE :" +
             DBConstants.DataProduct.OWNER_NAME + " AND dp.productName LIKE :" + DBConstants.DataProduct.PRODUCT_NAME;

     String GET_WORKFLOW_FOR_EXPERIMENT_ID = "SELECT W FROM " + AiravataWorkflowEntity.class.getSimpleName() + " W " +
             "WHERE W.experimentId LIKE :" + DBConstants.Workflow.EXPERIMENT_ID;

     String FIND_STORAGE_RESOURCE = "SELECT DISTINCT SR FROM " + StorageResourceEntity.class.getSimpleName() + " SR " +
             "WHERE SR.hostName LIKE :" + DBConstants.StorageResource.HOST_NAME;

     String FIND_ALL_STORAGE_RESOURCES = "SELECT SR FROM " + StorageResourceEntity.class.getSimpleName() + " SR";

     String FIND_ALL_AVAILABLE_STORAGE_RESOURCES = "SELECT SR FROM " + StorageResourceEntity.class.getSimpleName() + " SR " +
             "WHERE SR.enabled = TRUE";

     String FIND_ALL_GRID_FTP_ENDPOINTS_BY_DATA_MOVEMENT = "SELECT GFE FROM " + GridftpEndpointEntity.class.getSimpleName() +
             " GFE WHERE GFE.gridftpDataMovement.dataMovementInterfaceId LIKE :" + DBConstants.DataMovement.GRID_FTP_DATA_MOVEMENT_ID;

     String FIND_PARSING_TEMPLATES_FOR_APPLICATION_INTERFACE_ID = "SELECT PT FROM " + ParsingTemplateEntity.class.getSimpleName() + " PT " +
             "WHERE PT.applicationInterface = :" + DBConstants.ParsingTemplate.APPLICATION_INTERFACE_ID;

     String FIND_ALL_PARSING_TEMPLATES_FOR_GATEWAY_ID = "SELECT PT FROM " + ParsingTemplateEntity.class.getSimpleName() + " PT " +
             "WHERE PT.gatewayId = :" + DBConstants.ParsingTemplate.GATEWAY_ID;

     String FIND_ALL_PARSERS_FOR_GATEWAY_ID = "SELECT P FROM " + ParserEntity.class.getSimpleName() + " P " +
             "WHERE P.gatewayId = :" + DBConstants.Parser.GATEWAY_ID;

     String FIND_QUEUE_STATUS = "SELECT  L  FROM " + QueueStatusEntity.class.getSimpleName() + " L WHERE L.hostName LIKE :"
             + DBConstants.QueueStatus.HOST_NAME + " AND L.queueName LIKE :" + DBConstants.QueueStatus.QUEUE_NAME +" ORDER BY L.time DESC";

     String FIND_PROCESS_WITH_STATUS = "SELECT P FROM " + ProcessStatusEntity.class.getSimpleName() + " P " +
             " where P.state = :" + DBConstants.ProcessStatus.STATE;

     String GET_ALL_PROCESSES = "SELECT P FROM " + ProcessEntity.class.getSimpleName() +" P ";

     String DELETE_JOB_NATIVE_QUERY = "DELETE FROM JOB WHERE JOB_ID = ?1 AND TASK_ID = ?2";


     String FIND_JOB_COUNT_NATIVE_QUERY = "SELECT DISTINCT JS.JOB_ID FROM JOB_STATUS JS WHERE JS.JOB_ID IN " +
             "(SELECT J.JOB_ID FROM JOB J where J.PROCESS_ID IN " +
             "(SELECT P.PROCESS_ID FROM PROCESS P  where P.EXPERIMENT_ID IN " +
             "(SELECT E.EXPERIMENT_ID FROM EXPERIMENT E where E.GATEWAY_ID= ?1))) " +
             "AND JS.STATE = ?2 and JS.TIME_OF_STATE_CHANGE > now() - interval ?3 minute";

     String FIND_AVG_TIME_UPTO_METASCHEDULER_NATIVE_QUERY = "SELECT AVG(difference) FROM(select es.TIME_OF_STATE_CHANGE AS esTime1, ps.TIME_OF_STATE_CHANGE as psTime1, " +
             " TIMESTAMPDIFF(MICROSECOND, es.TIME_OF_STATE_CHANGE, ps.TIME_OF_STATE_CHANGE) AS difference FROM EXPERIMENT_STATUS es, " +
             " EXPERIMENT_STATUS ps WHERE es.EXPERIMENT_ID IN (select EXPERIMENT_ID FROM EXPERIMENT WHERE GATEWAY_ID= ?1) " +
             " AND ps.EXPERIMENT_ID=es.EXPERIMENT_ID AND es.STATE='CREATED' AND (ps.STATE='SCHEDULED' OR (ps.STATE='LAUNCHED ' " +
             " AND ps.EXPERIMENT_ID NOT IN(select ps1.EXPERIMENT_ID FROM EXPERIMENT_STATUS ps1 WHERE ps1.STATE='SCHEDULED'))" +
             " AND ps.TIME_OF_STATE_CHANGE <= ALL(select ps1.TIME_OF_STATE_CHANGE FROM EXPERIMENT_STATUS ps1 WHERE " +
             " ps1.EXPERIMENT_ID=ps.EXPERIMENT_ID AND ps1.STATE='SCHEDULED'))  " +
             " AND es.TIME_OF_STATE_CHANGE > now()-interval ?2 minute) abstract_t";

     String FIND_AVG_TIME_QUEUED_NATIVE_QUERY = "SELECT AVG(difference) FROM (SELECT es.TIME_OF_STATE_CHANGE AS esTime1, ps.TIME_OF_STATE_CHANGE as psTime1, " +
             " TIMESTAMPDIFF(MICROSECOND, es.TIME_OF_STATE_CHANGE, ps.TIME_OF_STATE_CHANGE) AS difference FROM EXPERIMENT_STATUS es," +
             "  EXPERIMENT_STATUS ps WHERE es.EXPERIMENT_ID IN (select EXPERIMENT_ID FROM EXPERIMENT WHERE GATEWAY_ID=?1) " +
             "  AND ps.EXPERIMENT_ID=es.EXPERIMENT_ID AND es.STATE='SCHEDULED' AND ps.STATE='LAUNCHED' " +
             "  AND ps.TIME_OF_STATE_CHANGE >= ALL(SELECT ps1.TIME_OF_STATE_CHANGE FROM EXPERIMENT_STATUS ps1 " +
             "  WHERE ps1.EXPERIMENT_ID=ps.EXPERIMENT_ID AND ps1.STATE='LAUNCHED') AND " +
             "  es.TIME_OF_STATE_CHANGE <= ALL(SELECT ps1.TIME_OF_STATE_CHANGE FROM EXPERIMENT_STATUS ps1 " +
             "  WHERE ps1.EXPERIMENT_ID=es.EXPERIMENT_ID AND ps1.STATE='SCHEDULED') AND es.TIME_OF_STATE_CHANGE > now()-interval ?2 minute)abstract_t";

     String FIND_AVG_TIME_HELIX_NATIVE_QUERY = "SELECT AVG(difference) FROM(SELECT es.TIME_OF_STATE_CHANGE AS esTime1, ps.TIME_OF_STATE_CHANGE as psTime1, " +
             " TIMESTAMPDIFF(MICROSECOND, es.TIME_OF_STATE_CHANGE, ps.TIME_OF_STATE_CHANGE) AS difference from EXPERIMENT_STATUS es, " +
             " JOB_STATUS ps where es.EXPERIMENT_ID IN (SELECT EXPERIMENT_ID FROM EXPERIMENT WHERE GATEWAY_ID=?1) " +
             " AND ps.JOB_ID IN(SELECT j.JOB_ID FROM JOB j where j.PROCESS_ID IN(SELECT DISTINCT p.PROCESS_ID  FROM PROCESS p " +
             " WHERE p.EXPERIMENT_ID=es.EXPERIMENT_ID)) AND  ps.TASK_ID IN(SELECT j.TASK_ID FROM JOB j where j.PROCESS_ID IN(SELECT DISTINCT p.PROCESS_ID  FROM PROCESS p " +
             "  WHERE p.EXPERIMENT_ID=es.EXPERIMENT_ID)) AND es.STATE='LAUNCHED' AND ps.STATE='SUBMITTED' " +
             " AND ps.TIME_OF_STATE_CHANGE >= ALL(SELECT ps1.TIME_OF_STATE_CHANGE FROM JOB_STATUS ps1 WHERE ps1.JOB_ID=ps.JOB_ID " +
             " AND ps1.STATE='SUBMITTED') AND es.TIME_OF_STATE_CHANGE >= ALL(SELECT es1.TIME_OF_STATE_CHANGE FROM EXPERIMENT_STATUS es1 " +
             " WHERE es1.EXPERIMENT_ID=es.EXPERIMENT_ID AND es1.STATE='LAUNCHED') AND  es.TIME_OF_STATE_CHANGE > now()-interval ?2 minute) abstract_t";


 }
	/**
	* Licensed to the Apache Software Foundation (ASF) under one
	* or more contributor license agreements. See the NOTICE file
	* distributed with this work for additional information
	* regarding copyright ownership. The ASF licenses this file
	* to you under the Apache License, Version 2.0 (the
	* "License"); you may not use this file except in compliance
	* with the License. You may obtain a copy of the License at
	* <p>
	* http://www.apache.org/licenses/LICENSE-2.0
	* <p>
	* Unless required by applicable law or agreed to in writing,
	* software distributed under the License is distributed on an
	* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
	* KIND, either express or implied. See the License for the
	* specific language governing permissions and limitations
	* under the License.
	*/
	package org.apache.airavata.registry.core.utils;

	import org.apache.airavata.model.status.JobStatus;
	import org.apache.airavata.model.status.QueueStatusModel;
	import org.apache.airavata.model.user.UserProfile;
	import org.apache.airavata.registry.core.entities.airavataworkflowcatalog.AiravataWorkflowEntity;
	import org.apache.airavata.registry.core.entities.appcatalog.*;
	import org.apache.airavata.registry.core.entities.expcatalog.*;
	import org.apache.airavata.registry.core.entities.replicacatalog.DataProductEntity;

	import javax.print.attribute.standard.JobState;

	public interface QueryConstants {

	String FIND_USER_PROFILE_BY_USER_ID = "SELECT u FROM UserProfileEntity u " +
	"where u.userId LIKE :" + UserProfile._Fields.USER_ID.getFieldName() + " " +
	"AND u.gatewayId LIKE :" + UserProfile._Fields.GATEWAY_ID.getFieldName() + "";

	String FIND_ALL_USER_PROFILES_BY_GATEWAY_ID = "SELECT u FROM UserProfileEntity u " +
	"where u.gatewayId LIKE :" + UserProfile._Fields.GATEWAY_ID.getFieldName() + "";

	// Application Deployment Queries
	String FIND_APPLICATION_DEPLOYMENTS_FOR_GATEWAY_ID = "SELECT AD FROM " + ApplicationDeploymentEntity.class.getSimpleName() + " AD " +
	"WHERE AD.gatewayId LIKE :" + DBConstants.ApplicationDeployment.GATEWAY_ID;
	String FIND_APPLICATION_DEPLOYMENTS_FOR_APPLICATION_MODULE_ID = "SELECT AD FROM " + ApplicationDeploymentEntity.class.getSimpleName() + " AD " +
	"WHERE AD.appModuleId LIKE :" + DBConstants.ApplicationDeployment.APPLICATION_MODULE_ID;
	String FIND_APPLICATION_DEPLOYMENTS_FOR_COMPUTE_HOST_ID = "SELECT AD FROM " + ApplicationDeploymentEntity.class.getSimpleName() + " AD " +
	"WHERE AD.computeHostId LIKE :" + DBConstants.ApplicationDeployment.COMPUTE_HOST_ID;
	String GET_ALL_APPLICATION_DEPLOYMENTS = "SELECT AD FROM " + ApplicationDeploymentEntity.class.getSimpleName() + " AD";
	String FIND_ACCESSIBLE_APPLICATION_DEPLOYMENTS = "SELECT AD FROM " + ApplicationDeploymentEntity.class.getSimpleName() + " AD " +
	"WHERE AD.gatewayId LIKE :" + DBConstants.ApplicationDeployment.GATEWAY_ID + " AND AD.appDeploymentId IN :" +
	DBConstants.ApplicationDeployment.ACCESSIBLE_APPLICATION_DEPLOYMENT_IDS + " AND AD.computeHostId IN :" +
	DBConstants.ApplicationDeployment.ACCESSIBLE_COMPUTE_HOST_IDS;
	String FIND_ACCESSIBLE_APPLICATION_DEPLOYMENTS_FOR_APP_MODULE = "SELECT AD FROM " + ApplicationDeploymentEntity.class.getSimpleName() + " AD " +
	"WHERE AD.gatewayId LIKE :" + DBConstants.ApplicationDeployment.GATEWAY_ID + " AND AD.appDeploymentId IN :" +
	DBConstants.ApplicationDeployment.ACCESSIBLE_APPLICATION_DEPLOYMENT_IDS + " AND AD.computeHostId IN :" +
	DBConstants.ApplicationDeployment.ACCESSIBLE_COMPUTE_HOST_IDS + " AND AD.appModuleId = :" +
	DBConstants.ApplicationDeployment.APPLICATION_MODULE_ID;

	// Application Module Queries
	String FIND_APPLICATION_MODULES_FOR_GATEWAY_ID = "SELECT AM FROM " + ApplicationModuleEntity.class.getSimpleName() + " AM " +
	"WHERE AM.gatewayId LIKE :" + DBConstants.ApplicationModule.GATEWAY_ID;
	String FIND_APPLICATION_MODULES_FOR_APPLICATION_MODULE_NAME = "SELECT AM FROM " + ApplicationModuleEntity.class.getSimpleName() + " AM " +
	"WHERE AM.appModuleName LIKE :" + DBConstants.ApplicationModule.APPLICATION_MODULE_NAME;
	String FIND_ACCESSIBLE_APPLICATION_MODULES = "SELECT AM FROM " + ApplicationModuleEntity.class.getSimpleName() + " AM " +
	"WHERE AM.gatewayId LIKE :" + DBConstants.ApplicationModule.GATEWAY_ID + " AND " +
	"EXISTS (SELECT 1 FROM " + ApplicationDeploymentEntity.class.getSimpleName() + " AD " +
	"WHERE AM.appModuleId = AD.appModuleId AND AD.appDeploymentId IN :" + DBConstants.ApplicationDeployment.ACCESSIBLE_APPLICATION_DEPLOYMENT_IDS +
	" AND AD.computeHostId IN :" + DBConstants.ApplicationDeployment.ACCESSIBLE_COMPUTE_HOST_IDS + ")";

	// Application Interface Queries
	String FIND_APPLICATION_INTERFACES_FOR_GATEWAY_ID = "SELECT AI FROM " + ApplicationInterfaceEntity.class.getSimpleName() + " AI " +
	"WHERE AI.gatewayId LIKE :" + DBConstants.ApplicationInterface.GATEWAY_ID;
	String FIND_APPLICATION_INTERFACES_FOR_APPLICATION_NAME = "SELECT AI FROM " + ApplicationInterfaceEntity.class.getSimpleName() + " AI " +
	"WHERE AI.applicationName LIKE :" + DBConstants.ApplicationInterface.APPLICATION_NAME;
	String GET_ALL_APPLICATION_INTERFACES = "SELECT AI FROM " + ApplicationInterfaceEntity.class.getSimpleName() + " AI";

	// Application Inputs Queries
	String FIND_APPLICATION_INPUTS = "SELECT AI FROM " + ApplicationInputEntity.class.getSimpleName() + " AI " +
	"WHERE AI.interfaceId LIKE :" + DBConstants.ApplicationInput.APPLICATION_INTERFACE_ID;

	// Application Outputs Queries
	String FIND_APPLICATION_OUTPUTS = "SELECT AI FROM " + ApplicationOutputEntity.class.getSimpleName() + " AI " +
	"WHERE AI.interfaceId LIKE :" + DBConstants.ApplicationOutput.APPLICATION_INTERFACE_ID;

	String FIND_ALL_GATEWAY_PROFILES = "SELECT G FROM " + GatewayProfileEntity.class.getSimpleName() + " G";
	String FIND_ALL_COMPUTE_RESOURCE_PREFERENCES = "SELECT DISTINCT CR FROM " + ComputeResourcePreferenceEntity.class.getSimpleName() + " CR " +
	"WHERE CR.gatewayId LIKE :" + DBConstants.ComputeResourcePreference.GATEWAY_ID;
	String FIND_ALL_STORAGE_RESOURCE_PREFERENCES = "SELECT DISTINCT S FROM " + StoragePreferenceEntity.class.getSimpleName() + " S " +
	"WHERE S.gatewayId LIKE :" + DBConstants.StorageResourcePreference.GATEWAY_ID;

	String FIND_COMPUTE_RESOURCE = "SELECT DISTINCT CR FROM " + ComputeResourceEntity.class.getSimpleName() + " CR " +
	"WHERE CR.hostName LIKE :" + DBConstants.ComputeResource.HOST_NAME;
	String FIND_ALL_COMPUTE_RESOURCES = "SELECT CR FROM " + ComputeResourceEntity.class.getSimpleName() + " CR";
	String GET_FILE_SYSTEM = "SELECT DISTINCT FS FROM " + ComputeResourceFileSystemEntity.class.getSimpleName() + " FS " +
	"WHERE FS.computeResourceId LIKE :" + DBConstants.ComputeResource.COMPUTE_RESOURCE_ID;
	String GET_JOB_MANAGER_COMMAND = "SELECT DISTINCT JM FROM " + JobManagerCommandEntity.class.getSimpleName() + " JM " +
	"WHERE JM.resourceJobManagerId LIKE :" + DBConstants.ResourceJobManager.RESOURCE_JOB_MANAGER_ID;
	String GET_PARALLELISM_PREFIX = "SELECT DISTINCT PF FROM " + ParallelismCommandEntity.class.getSimpleName() + " PF " +
	"WHERE PF.resourceJobManagerId LIKE :" + DBConstants.ResourceJobManager.RESOURCE_JOB_MANAGER_ID;

	String FIND_ACCESSIBLE_GROUP_RESOURCE_PROFILES = "SELECT G FROM " + GroupResourceProfileEntity.class.getSimpleName() + " G " +
	"WHERE G.gatewayId LIKE :" + DBConstants.GroupResourceProfile.GATEWAY_ID + " AND G.groupResourceProfileId IN :"
	+ DBConstants.GroupResourceProfile.ACCESSIBLE_GROUP_RESOURCE_IDS;
	String FIND_ALL_GROUP_COMPUTE_PREFERENCES = "SELECT GC FROM " + GroupComputeResourcePrefEntity.class.getSimpleName() + " GC " +
	"WHERE GC.groupResourceProfileId LIKE :" + DBConstants.GroupResourceProfile.GROUP_RESOURCE_PROFILE_ID;
	String FIND_ALL_GROUP_BATCH_QUEUE_RESOURCE_POLICY = "SELECT BQ FROM " + BatchQueueResourcePolicyEntity.class.getSimpleName() + " BQ " +
	"WHERE BQ.groupResourceProfileId LIKE :" + DBConstants.GroupResourceProfile.GROUP_RESOURCE_PROFILE_ID;
	String FIND_ALL_GROUP_COMPUTE_RESOURCE_POLICY = "SELECT CR FROM " + ComputeResourcePolicyEntity.class.getSimpleName() + " CR " +
	"WHERE CR.groupResourceProfileId LIKE :" + DBConstants.GroupResourceProfile.GROUP_RESOURCE_PROFILE_ID;

	String GET_ALL_USER_RESOURCE_PROFILE = "SELECT URP FROM " + UserResourceProfileEntity.class.getSimpleName() + " URP";
	String GET_ALL_GATEWAY_ID = "SELECT DISTINCT URP FROM " + UserResourceProfileEntity.class.getSimpleName() + " URP " +
	"WHERE URP.gatewayId LIKE :" + DBConstants.UserResourceProfile.GATEWAY_ID;

	String GET_ALL_GATEWAYS = "SELECT G FROM " + GatewayEntity.class.getSimpleName() + " G";
	String GET_GATEWAY_FROM_GATEWAY_NAME = "SELECT G FROM " + GatewayEntity.class.getSimpleName() + " G " +
	"WHERE G.gatewayName LIKE :" + DBConstants.Gateway.GATEWAY_NAME;

	String GET_ALL_GATEWAY_NOTIFICATIONS = "SELECT N FROM " + NotificationEntity.class.getSimpleName() + " N " +
	"WHERE N.gatewayId LIKE :" + DBConstants.Notification.GATEWAY_ID;

	String GET_ALL_GATEWAY_USERS = "SELECT U FROM " + UserEntity.class.getSimpleName() + " U " +
	"WHERE U.gatewayId LIKE :" + DBConstants.User.GATEWAY_ID;

	String GET_ALL_PROJECTS_FOR_OWNER = "SELECT P FROM " + ProjectEntity.class.getSimpleName() + " P " +
	"WHERE P.owner LIKE :" + DBConstants.Project.OWNER;

	String GET_EXPERIMENTS_FOR_USER = "SELECT E FROM " + ExperimentEntity.class.getSimpleName() + " E " +
	"WHERE E.userName LIKE :" + DBConstants.Experiment.USER_NAME +
	" AND E.gatewayId = :" + DBConstants.Experiment.GATEWAY_ID;
	String GET_EXPERIMENTS_FOR_PROJECT_ID = "SELECT E FROM " + ExperimentEntity.class.getSimpleName() + " E " +
	"WHERE E.projectId LIKE :" + DBConstants.Experiment.PROJECT_ID +
	" AND E.gatewayId = :" + DBConstants.Experiment.GATEWAY_ID;
	String GET_EXPERIMENTS_FOR_GATEWAY_ID = "SELECT E FROM " + ExperimentEntity.class.getSimpleName() + " E " +
	"WHERE E.gatewayId LIKE :" + DBConstants.Experiment.GATEWAY_ID;

	String GET_PROCESS_FOR_EXPERIMENT_ID = "SELECT P FROM " + ProcessEntity.class.getSimpleName() + " P " +
	"WHERE P.experimentId = :" + DBConstants.Process.EXPERIMENT_ID;

	String GET_TASK_FOR_PARENT_PROCESS_ID = "SELECT T FROM " + TaskEntity.class.getSimpleName() + " T " +
	"WHERE T.parentProcessId LIKE :" + DBConstants.Task.PARENT_PROCESS_ID;

	String GET_JOB_FOR_PROCESS_ID = "SELECT J FROM " + JobEntity.class.getSimpleName() + " J " +
	"WHERE J.processId LIKE :" + DBConstants.Job.PROCESS_ID;

	String GET_JOB_FOR_TASK_ID = "SELECT J FROM " + JobEntity.class.getSimpleName() + " J " +
	"WHERE J.taskId LIKE :" + DBConstants.Job.TASK_ID;

	String GET_JOB_FOR_JOB_ID = "SELECT J FROM " + JobEntity.class.getSimpleName() + " J " +
	"WHERE J.jobId LIKE :" + DBConstants.Job.JOB_ID;

	String GET_ALL_QUEUE_STATUS_MODELS = "SELECT QSM FROM " + QueueStatusEntity.class.getSimpleName() + " QSM";

	String GET_ALL_USER_COMPUTE_RESOURCE_PREFERENCE = "SELECT UCRP FROM " + UserComputeResourcePreferenceEntity.class.getSimpleName() + " UCRP " +
	"WHERE UCRP.userId LIKE :" + DBConstants.UserComputeResourcePreference.USER_ID + " AND UCRP.gatewayId LIKE :" +
	DBConstants.UserComputeResourcePreference.GATEWAY_ID;

	String GET_ALL_USER_STORAGE_PREFERENCE = "SELECT USP FROM " + UserStoragePreferenceEntity.class.getSimpleName() + " USP " +
	"WHERE USP.userId LIKE :" + DBConstants.UserStoragePreference.USER_ID + " AND USP.gatewayId LIKE :" +
	DBConstants.UserStoragePreference.GATEWAY_ID;

	String FIND_ALL_CHILD_DATA_PRODUCTS = "SELECT DP FROM " + DataProductEntity.class.getSimpleName() + " DP " +
	"WHERE DP.parentProductUri LIKE :" + DBConstants.DataProduct.PARENT_PRODUCT_URI;

	String FIND_DATA_PRODUCT_BY_NAME = "SELECT DP FROM " + DataProductEntity.class.getSimpleName() + " DP " +
	"WHERE DP.gatewayId LIKE :" + DBConstants.DataProduct.GATEWAY_ID + " AND DP.ownerName LIKE :" +
	DBConstants.DataProduct.OWNER_NAME + " AND dp.productName LIKE :" + DBConstants.DataProduct.PRODUCT_NAME;

	String GET_WORKFLOW_FOR_EXPERIMENT_ID = "SELECT W FROM " + AiravataWorkflowEntity.class.getSimpleName() + " W " +
	"WHERE W.experimentId LIKE :" + DBConstants.Workflow.EXPERIMENT_ID;

	String FIND_STORAGE_RESOURCE = "SELECT DISTINCT SR FROM " + StorageResourceEntity.class.getSimpleName() + " SR " +
	"WHERE SR.hostName LIKE :" + DBConstants.StorageResource.HOST_NAME;

	String FIND_ALL_STORAGE_RESOURCES = "SELECT SR FROM " + StorageResourceEntity.class.getSimpleName() + " SR";

	String FIND_ALL_AVAILABLE_STORAGE_RESOURCES = "SELECT SR FROM " + StorageResourceEntity.class.getSimpleName() + " SR " +
	"WHERE SR.enabled = TRUE";

	String FIND_ALL_GRID_FTP_ENDPOINTS_BY_DATA_MOVEMENT = "SELECT GFE FROM " + GridftpEndpointEntity.class.getSimpleName() +
	" GFE WHERE GFE.gridftpDataMovement.dataMovementInterfaceId LIKE :" + DBConstants.DataMovement.GRID_FTP_DATA_MOVEMENT_ID;

	String FIND_PARSING_TEMPLATES_FOR_APPLICATION_INTERFACE_ID = "SELECT PT FROM " + ParsingTemplateEntity.class.getSimpleName() + " PT " +
	"WHERE PT.applicationInterface = :" + DBConstants.ParsingTemplate.APPLICATION_INTERFACE_ID;

	String FIND_ALL_PARSING_TEMPLATES_FOR_GATEWAY_ID = "SELECT PT FROM " + ParsingTemplateEntity.class.getSimpleName() + " PT " +
	"WHERE PT.gatewayId = :" + DBConstants.ParsingTemplate.GATEWAY_ID;

	String FIND_ALL_PARSERS_FOR_GATEWAY_ID = "SELECT P FROM " + ParserEntity.class.getSimpleName() + " P " +
	"WHERE P.gatewayId = :" + DBConstants.Parser.GATEWAY_ID;

	String FIND_QUEUE_STATUS = "SELECT L FROM " + QueueStatusEntity.class.getSimpleName() + " L WHERE L.hostName LIKE :"
	+ DBConstants.QueueStatus.HOST_NAME + " AND L.queueName LIKE :" + DBConstants.QueueStatus.QUEUE_NAME +" ORDER BY L.time DESC";

	String FIND_PROCESS_WITH_STATUS = "SELECT P FROM " + ProcessStatusEntity.class.getSimpleName() + " P " +
	" where P.state = :" + DBConstants.ProcessStatus.STATE;

	String GET_ALL_PROCESSES = "SELECT P FROM " + ProcessEntity.class.getSimpleName() +" P ";

	String DELETE_JOB_NATIVE_QUERY = "DELETE FROM JOB WHERE JOB_ID = ?1 AND TASK_ID = ?2";


	String FIND_JOB_COUNT_NATIVE_QUERY = "SELECT DISTINCT JS.JOB_ID FROM JOB_STATUS JS WHERE JS.JOB_ID IN " +
	"(SELECT J.JOB_ID FROM JOB J where J.PROCESS_ID IN " +
	"(SELECT P.PROCESS_ID FROM PROCESS P where P.EXPERIMENT_ID IN " +
	"(SELECT E.EXPERIMENT_ID FROM EXPERIMENT E where E.GATEWAY_ID= ?1))) " +
	"AND JS.STATE = ?2 and JS.TIME_OF_STATE_CHANGE > now() - interval ?3 minute";

	String FIND_AVG_TIME_UPTO_METASCHEDULER_NATIVE_QUERY = "SELECT AVG(difference) FROM(select es.TIME_OF_STATE_CHANGE AS esTime1, ps.TIME_OF_STATE_CHANGE as psTime1, " +
	" TIMESTAMPDIFF(MICROSECOND, es.TIME_OF_STATE_CHANGE, ps.TIME_OF_STATE_CHANGE) AS difference FROM EXPERIMENT_STATUS es, " +
	" EXPERIMENT_STATUS ps WHERE es.EXPERIMENT_ID IN (select EXPERIMENT_ID FROM EXPERIMENT WHERE GATEWAY_ID= ?1) " +
	" AND ps.EXPERIMENT_ID=es.EXPERIMENT_ID AND es.STATE='CREATED' AND (ps.STATE='SCHEDULED' OR (ps.STATE='LAUNCHED ' " +
	" AND ps.EXPERIMENT_ID NOT IN(select ps1.EXPERIMENT_ID FROM EXPERIMENT_STATUS ps1 WHERE ps1.STATE='SCHEDULED'))" +
	" AND ps.TIME_OF_STATE_CHANGE <= ALL(select ps1.TIME_OF_STATE_CHANGE FROM EXPERIMENT_STATUS ps1 WHERE " +
	" ps1.EXPERIMENT_ID=ps.EXPERIMENT_ID AND ps1.STATE='SCHEDULED')) " +
	" AND es.TIME_OF_STATE_CHANGE > now()-interval ?2 minute) abstract_t";

	String FIND_AVG_TIME_QUEUED_NATIVE_QUERY = "SELECT AVG(difference) FROM (SELECT es.TIME_OF_STATE_CHANGE AS esTime1, ps.TIME_OF_STATE_CHANGE as psTime1, " +
	" TIMESTAMPDIFF(MICROSECOND, es.TIME_OF_STATE_CHANGE, ps.TIME_OF_STATE_CHANGE) AS difference FROM EXPERIMENT_STATUS es," +
	" EXPERIMENT_STATUS ps WHERE es.EXPERIMENT_ID IN (select EXPERIMENT_ID FROM EXPERIMENT WHERE GATEWAY_ID=?1) " +
	" AND ps.EXPERIMENT_ID=es.EXPERIMENT_ID AND es.STATE='SCHEDULED' AND ps.STATE='LAUNCHED' " +
	" AND ps.TIME_OF_STATE_CHANGE >= ALL(SELECT ps1.TIME_OF_STATE_CHANGE FROM EXPERIMENT_STATUS ps1 " +
	" WHERE ps1.EXPERIMENT_ID=ps.EXPERIMENT_ID AND ps1.STATE='LAUNCHED') AND " +
	" es.TIME_OF_STATE_CHANGE <= ALL(SELECT ps1.TIME_OF_STATE_CHANGE FROM EXPERIMENT_STATUS ps1 " +
	" WHERE ps1.EXPERIMENT_ID=es.EXPERIMENT_ID AND ps1.STATE='SCHEDULED') AND es.TIME_OF_STATE_CHANGE > now()-interval ?2 minute)abstract_t";

	String FIND_AVG_TIME_HELIX_NATIVE_QUERY = "SELECT AVG(difference) FROM(SELECT es.TIME_OF_STATE_CHANGE AS esTime1, ps.TIME_OF_STATE_CHANGE as psTime1, " +
	" TIMESTAMPDIFF(MICROSECOND, es.TIME_OF_STATE_CHANGE, ps.TIME_OF_STATE_CHANGE) AS difference from EXPERIMENT_STATUS es, " +
	" JOB_STATUS ps where es.EXPERIMENT_ID IN (SELECT EXPERIMENT_ID FROM EXPERIMENT WHERE GATEWAY_ID=?1) " +
	" AND ps.JOB_ID IN(SELECT j.JOB_ID FROM JOB j where j.PROCESS_ID IN(SELECT DISTINCT p.PROCESS_ID FROM PROCESS p " +
	" WHERE p.EXPERIMENT_ID=es.EXPERIMENT_ID)) AND ps.TASK_ID IN(SELECT j.TASK_ID FROM JOB j where j.PROCESS_ID IN(SELECT DISTINCT p.PROCESS_ID FROM PROCESS p " +
	" WHERE p.EXPERIMENT_ID=es.EXPERIMENT_ID)) AND es.STATE='LAUNCHED' AND ps.STATE='SUBMITTED' " +
	" AND ps.TIME_OF_STATE_CHANGE >= ALL(SELECT ps1.TIME_OF_STATE_CHANGE FROM JOB_STATUS ps1 WHERE ps1.JOB_ID=ps.JOB_ID " +
	" AND ps1.STATE='SUBMITTED') AND es.TIME_OF_STATE_CHANGE >= ALL(SELECT es1.TIME_OF_STATE_CHANGE FROM EXPERIMENT_STATUS es1 " +
	" WHERE es1.EXPERIMENT_ID=es.EXPERIMENT_ID AND es1.STATE='LAUNCHED') AND es.TIME_OF_STATE_CHANGE > now()-interval ?2 minute) abstract_t";


	}