fixing full text search issue
Project: http://git-wip-us.apache.org/repos/asf/airavata/repo Commit: http://git-wip-us.apache.org/repos/asf/airavata/commit/a6860dc4 Tree: http://git-wip-us.apache.org/repos/asf/airavata/tree/a6860dc4 Diff: http://git-wip-us.apache.org/repos/asf/airavata/diff/a6860dc4 Branch: refs/heads/develop Commit: a6860dc491d23f59bdd489026853147f55596ed2 Parents: 6be0702 Author: scnakandala <supun.nakand...@gmail.com> Authored: Tue Oct 18 14:59:28 2016 -0400 Committer: scnakandala <supun.nakand...@gmail.com> Committed: Tue Oct 18 14:59:28 2016 -0400 ---------------------------------------------------------------------- .../db/repositories/AbstractRepository.java | 10 ++++ .../db/repositories/EntityRepository.java | 55 +++++++++++--------- .../main/resources/sharing-registry-derby.sql | 1 + .../main/resources/sharing-registry-mysql.sql | 1 + .../registry/SharingRegistryServiceTest.java | 4 +- 5 files changed, 45 insertions(+), 26 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/airavata/blob/a6860dc4/modules/sharing-registry/sharing-registry-server/src/main/java/org/apache/airavata/sharing/registry/db/repositories/AbstractRepository.java ---------------------------------------------------------------------- diff --git a/modules/sharing-registry/sharing-registry-server/src/main/java/org/apache/airavata/sharing/registry/db/repositories/AbstractRepository.java b/modules/sharing-registry/sharing-registry-server/src/main/java/org/apache/airavata/sharing/registry/db/repositories/AbstractRepository.java index 9f5a706..ed99f62 100644 --- a/modules/sharing-registry/sharing-registry-server/src/main/java/org/apache/airavata/sharing/registry/db/repositories/AbstractRepository.java +++ b/modules/sharing-registry/sharing-registry-server/src/main/java/org/apache/airavata/sharing/registry/db/repositories/AbstractRepository.java @@ -121,6 +121,16 @@ public abstract class AbstractRepository<T, E, Id> { return gatewayList; } + public List<T> selectFromNativeQuery(String queryString, int offset, int limit) throws SharingRegistryException { + int newLimit = limit < 0 ? DBConstants.SELECT_MAX_ROWS: limit; + List resultSet = JPAUtils.execute(entityManager -> entityManager.createNativeQuery(queryString).setFirstResult(offset) + .setMaxResults(newLimit).getResultList()); + Mapper mapper = ObjectMapperSingleton.getInstance(); + List<T> gatewayList = new ArrayList<>(); + resultSet.stream().forEach(rs -> gatewayList.add(mapper.map(rs, thriftGenericClass))); + return gatewayList; + } + public String getSelectQuery(Map<String, String> filters){ String query = "SELECT DISTINCT p from " + dbEntityGenericClass.getSimpleName() + " as p"; if(filters != null && filters.size() != 0){ http://git-wip-us.apache.org/repos/asf/airavata/blob/a6860dc4/modules/sharing-registry/sharing-registry-server/src/main/java/org/apache/airavata/sharing/registry/db/repositories/EntityRepository.java ---------------------------------------------------------------------- diff --git a/modules/sharing-registry/sharing-registry-server/src/main/java/org/apache/airavata/sharing/registry/db/repositories/EntityRepository.java b/modules/sharing-registry/sharing-registry-server/src/main/java/org/apache/airavata/sharing/registry/db/repositories/EntityRepository.java index 6064077..46a7cd0 100644 --- a/modules/sharing-registry/sharing-registry-server/src/main/java/org/apache/airavata/sharing/registry/db/repositories/EntityRepository.java +++ b/modules/sharing-registry/sharing-registry-server/src/main/java/org/apache/airavata/sharing/registry/db/repositories/EntityRepository.java @@ -20,10 +20,12 @@ */ package org.apache.airavata.sharing.registry.db.repositories; +import org.apache.airavata.common.exception.ApplicationSettingsException; +import org.apache.airavata.common.utils.ServerSettings; import org.apache.airavata.sharing.registry.db.entities.EntityEntity; import org.apache.airavata.sharing.registry.db.entities.EntityPK; -import org.apache.airavata.sharing.registry.db.entities.SharingEntity; import org.apache.airavata.sharing.registry.db.utils.DBConstants; +import org.apache.airavata.sharing.registry.db.utils.JPAUtils; import org.apache.airavata.sharing.registry.models.*; import org.slf4j.Logger; import org.slf4j.LoggerFactory; @@ -52,48 +54,53 @@ public class EntityRepository extends AbstractRepository<Entity, EntityEntity, E groupIdString += groupId + "','"; groupIdString = groupIdString.substring(0, groupIdString.length()-2); - String query = "SELECT DISTINCT E FROM " + EntityEntity.class.getSimpleName() + " E, " + SharingEntity.class.getSimpleName() + " S WHERE " + - "E." + DBConstants.EntityTable.ENTITY_ID + " = S." + DBConstants.SharingTable.ENTITY_ID + " AND " + - "E." + DBConstants.EntityTable.DOMAIN_ID + " = S." + DBConstants.SharingTable.DOMAIN_ID + " AND " + - "E." + DBConstants.EntityTable.DOMAIN_ID + " = '" + domainId + "' AND " + - "S." + DBConstants.SharingTable.GROUP_ID + " IN(" + groupIdString + ") AND E." + DBConstants.EntityTable.ENTITY_TYPE_ID + "='" + - entityTypeId + "' AND "; + String query = "SELECT E.* FROM ENTITY AS E INNER JOIN SHARING AS S ON (E.ENTITY_ID=S.ENTITY_ID AND E.DOMAIN_ID=S.DOMAIN_ID) WHERE " + + "E.DOMAIN_ID = '" + domainId + "' AND " + "S.GROUP_ID IN(" + groupIdString + ") AND E." + + "ENTITY_TYPE_ID" + "='" + entityTypeId + "' AND "; for(SearchCriteria searchCriteria : filters){ if(searchCriteria.getSearchField().equals(EntitySearchField.NAME)){ - query += "E." + DBConstants.EntityTable.NAME + " LIKE '%" + searchCriteria.getValue() + "%' AND "; + query += "E.NAME LIKE '%" + searchCriteria.getValue() + "%' AND "; }else if(searchCriteria.getSearchField().equals(EntitySearchField.DESCRIPTION)){ - query += "E." + DBConstants.EntityTable.DESCRIPTION + " LIKE '%" + searchCriteria.getValue() + "%' AND "; + query += "E.DESCRIPTION LIKE '%" + searchCriteria.getValue() + "%' AND "; }else if(searchCriteria.getSearchField().equals(EntitySearchField.PERMISSION_TYPE_ID)){ - query += "S." + DBConstants.SharingTable.PERMISSION_TYPE_ID + " = '" + searchCriteria.getValue() + "' AND "; + query += "S.PERMISSION_TYPE_ID = '" + searchCriteria.getValue() + "' AND "; }else if(searchCriteria.getSearchField().equals(EntitySearchField.FULL_TEXT)){ - //FULL TEXT Search with Query Expansion - String queryTerms = ""; - for(String word : searchCriteria.getValue().trim().replaceAll(" +", " ").split(" ")){ - queryTerms += queryTerms + " +" + word; + try { + if(ServerSettings.getSetting(JPAUtils.SHARING_REG_JDBC_DRIVER).contains("derby")){ + query += "E.FULL_TEXT LIKE '%" + searchCriteria.getValue() + "%' AND "; + }else{ + //FULL TEXT Search with Query Expansion + String queryTerms = ""; + for(String word : searchCriteria.getValue().trim().replaceAll(" +", " ").split(" ")){ + queryTerms += queryTerms + " +" + word; + } + queryTerms = queryTerms.trim(); + query += "MATCH(E.FULL_TEXT) AGAINST ('" + queryTerms + + "' IN BOOLEAN MODE) AND "; + } + } catch (ApplicationSettingsException e) { + logger.error(e.getMessage(), e); + throw new SharingRegistryException(e.getMessage()); } - queryTerms = queryTerms.trim(); - query += "MATCH(E." + DBConstants.EntityTable.FULL_TEXT + ") AGAINST ('" + queryTerms - + "' IN BOOLEAN MODE WITH QUERY EXPANSION) AND "; }else if(searchCriteria.getSearchField().equals(EntitySearchField.PARRENT_ENTITY_ID)){ - query += "E." + DBConstants.EntityTable.PARENT_ENTITY_ID + " = '" + searchCriteria.getValue() + "' AND "; + query += "E.PARENT_ENTITY_ID = '" + searchCriteria.getValue() + "' AND "; }else if(searchCriteria.getSearchField().equals(EntitySearchField.CREATED_TIME)){ if(searchCriteria.getSearchCondition().equals(SearchCondition.GTE)){ - query += "E." + DBConstants.EntityTable.CREATED_TIME + " >= " + Integer.parseInt(searchCriteria.getValue().trim()) + " AND "; + query += "E.CREATED_TIME >= " + Integer.parseInt(searchCriteria.getValue().trim()) + " AND "; }else{ - query += "E." + DBConstants.EntityTable.CREATED_TIME + " <= " + Integer.parseInt(searchCriteria.getValue().trim()) + " AND "; + query += "E.CREATED_TIME <= " + Integer.parseInt(searchCriteria.getValue().trim()) + " AND "; } }else if(searchCriteria.getSearchField().equals(EntitySearchField.UPDATED_TIME)){ if(searchCriteria.getSearchCondition().equals(SearchCondition.GTE)){ - query += "E." + DBConstants.EntityTable.UPDATED_TIME + " >= " + Integer.parseInt(searchCriteria.getValue().trim()) + " AND "; + query += "E.UPDATED_TIME >= " + Integer.parseInt(searchCriteria.getValue().trim()) + " AND "; }else{ - query += "E." + DBConstants.EntityTable.UPDATED_TIME + " <= " + Integer.parseInt(searchCriteria.getValue().trim()) + " AND "; + query += "E.UPDATED_TIME <= " + Integer.parseInt(searchCriteria.getValue().trim()) + " AND "; } } } - query = query.substring(0, query.length() - 5); - return select(query, offset, limit); + return selectFromNativeQuery(query, offset, limit); } public String getSelectQuery(Map<String, String> filters){ http://git-wip-us.apache.org/repos/asf/airavata/blob/a6860dc4/modules/sharing-registry/sharing-registry-server/src/main/resources/sharing-registry-derby.sql ---------------------------------------------------------------------- diff --git a/modules/sharing-registry/sharing-registry-server/src/main/resources/sharing-registry-derby.sql b/modules/sharing-registry/sharing-registry-server/src/main/resources/sharing-registry-derby.sql index 923894e..19389c9 100644 --- a/modules/sharing-registry/sharing-registry-server/src/main/resources/sharing-registry-derby.sql +++ b/modules/sharing-registry/sharing-registry-server/src/main/resources/sharing-registry-derby.sql @@ -123,6 +123,7 @@ CREATE TABLE SHARING ( UPDATED_TIME BIGINT NOT NULL, PRIMARY KEY (PERMISSION_TYPE_ID, ENTITY_ID, GROUP_ID, DOMAIN_ID, INHERITED_PARENT_ID), FOREIGN KEY (PERMISSION_TYPE_ID, DOMAIN_ID) REFERENCES PERMISSION_TYPE(PERMISSION_TYPE_ID, DOMAIN_ID) ON DELETE CASCADE ON UPDATE NO ACTION, + FOREIGN KEY (ENTITY_ID, DOMAIN_ID) REFERENCES ENTITY(ENTITY_ID, DOMAIN_ID) ON DELETE CASCADE ON UPDATE NO ACTION, FOREIGN KEY (INHERITED_PARENT_ID, DOMAIN_ID) REFERENCES ENTITY(ENTITY_ID, DOMAIN_ID) ON DELETE CASCADE ON UPDATE NO ACTION, FOREIGN KEY (GROUP_ID, DOMAIN_ID) REFERENCES USER_GROUP(GROUP_ID, DOMAIN_ID) ON DELETE CASCADE ON UPDATE NO ACTION ); http://git-wip-us.apache.org/repos/asf/airavata/blob/a6860dc4/modules/sharing-registry/sharing-registry-server/src/main/resources/sharing-registry-mysql.sql ---------------------------------------------------------------------- diff --git a/modules/sharing-registry/sharing-registry-server/src/main/resources/sharing-registry-mysql.sql b/modules/sharing-registry/sharing-registry-server/src/main/resources/sharing-registry-mysql.sql index 9bfe64a..7697d52 100644 --- a/modules/sharing-registry/sharing-registry-server/src/main/resources/sharing-registry-mysql.sql +++ b/modules/sharing-registry/sharing-registry-server/src/main/resources/sharing-registry-mysql.sql @@ -123,6 +123,7 @@ CREATE TABLE SHARING ( UPDATED_TIME BIGINT NOT NULL, PRIMARY KEY (PERMISSION_TYPE_ID, ENTITY_ID, GROUP_ID, DOMAIN_ID, INHERITED_PARENT_ID), FOREIGN KEY (PERMISSION_TYPE_ID, DOMAIN_ID) REFERENCES PERMISSION_TYPE(PERMISSION_TYPE_ID, DOMAIN_ID) ON DELETE CASCADE ON UPDATE NO ACTION, + FOREIGN KEY (ENTITY_ID, DOMAIN_ID) REFERENCES ENTITY(ENTITY_ID, DOMAIN_ID) ON DELETE CASCADE ON UPDATE NO ACTION, FOREIGN KEY (INHERITED_PARENT_ID, DOMAIN_ID) REFERENCES ENTITY(ENTITY_ID, DOMAIN_ID) ON DELETE CASCADE ON UPDATE NO ACTION, FOREIGN KEY (GROUP_ID, DOMAIN_ID) REFERENCES USER_GROUP(GROUP_ID, DOMAIN_ID) ON DELETE CASCADE ON UPDATE NO ACTION ); http://git-wip-us.apache.org/repos/asf/airavata/blob/a6860dc4/modules/sharing-registry/sharing-registry-server/src/test/java/org/apache/airavata/sharing/registry/SharingRegistryServiceTest.java ---------------------------------------------------------------------- diff --git a/modules/sharing-registry/sharing-registry-server/src/test/java/org/apache/airavata/sharing/registry/SharingRegistryServiceTest.java b/modules/sharing-registry/sharing-registry-server/src/test/java/org/apache/airavata/sharing/registry/SharingRegistryServiceTest.java index df9a556..9802113 100644 --- a/modules/sharing-registry/sharing-registry-server/src/test/java/org/apache/airavata/sharing/registry/SharingRegistryServiceTest.java +++ b/modules/sharing-registry/sharing-registry-server/src/test/java/org/apache/airavata/sharing/registry/SharingRegistryServiceTest.java @@ -49,7 +49,7 @@ public class SharingRegistryServiceTest { @Test - public void test() throws TException { + public void test() throws TException, InterruptedException { String serverHost = "localhost"; int serverPort = 7878; @@ -249,7 +249,7 @@ public class SharingRegistryServiceTest { entity3.setName("test-experiment-2"); entity3.setDescription("test experiment 2 description"); entity3.setParentEntityId("test-project-1"); - entity3.setFullText("test experiment 1 3-methyl 1-butanol"); + entity3.setFullText("test experiment 1 3-methyl 1-butanol stampede"); sharingServiceClient.createEntity(entity3); Entity entity4 = new Entity();
