From fd1a80bb41140a7dc8d05c87a5e064ad0db9b0ba Mon Sep 17 00:00:00 2001 From: Aron Roberts Date: Thu, 28 Mar 2013 18:15:48 -0700 Subject: [PATCH] CSPACE-5943: Added code, as yet untested, to restrict partial term matching queries to a specified authority. --- .../common/vocabulary/AuthorityResource.java | 2 +- .../AuthorityItemDocumentModelHandler.java | 14 +++- .../client/java/RepositoryJavaClientImpl.java | 74 ++++++++++--------- 3 files changed, 54 insertions(+), 36 deletions(-) diff --git a/services/authority/service/src/main/java/org/collectionspace/services/common/vocabulary/AuthorityResource.java b/services/authority/service/src/main/java/org/collectionspace/services/common/vocabulary/AuthorityResource.java index 5c599db68..d18fa32d3 100644 --- a/services/authority/service/src/main/java/org/collectionspace/services/common/vocabulary/AuthorityResource.java +++ b/services/authority/service/src/main/java/org/collectionspace/services/common/vocabulary/AuthorityResource.java @@ -120,7 +120,7 @@ public abstract class AuthorityResource final static String URN_PREFIX_ID = "id("; final static int URN_ID_PREFIX_LEN = URN_PREFIX_LEN + URN_PREFIX_ID.length(); final static String FETCH_SHORT_ID = "_fetch_"; - final static String PARENT_WILDCARD = "_ALL_"; + public final static String PARENT_WILDCARD = "_ALL_"; final Logger logger = LoggerFactory.getLogger(AuthorityResource.class); diff --git a/services/authority/service/src/main/java/org/collectionspace/services/common/vocabulary/nuxeo/AuthorityItemDocumentModelHandler.java b/services/authority/service/src/main/java/org/collectionspace/services/common/vocabulary/nuxeo/AuthorityItemDocumentModelHandler.java index 967661d9a..6e75f7260 100644 --- a/services/authority/service/src/main/java/org/collectionspace/services/common/vocabulary/nuxeo/AuthorityItemDocumentModelHandler.java +++ b/services/authority/service/src/main/java/org/collectionspace/services/common/vocabulary/nuxeo/AuthorityItemDocumentModelHandler.java @@ -40,6 +40,7 @@ import org.collectionspace.services.common.document.DocumentWrapper; import org.collectionspace.services.common.repository.RepositoryClient; import org.collectionspace.services.common.vocabulary.AuthorityJAXBSchema; import org.collectionspace.services.common.vocabulary.AuthorityItemJAXBSchema; +import org.collectionspace.services.common.vocabulary.AuthorityResource; import org.collectionspace.services.common.vocabulary.RefNameServiceUtils; import org.collectionspace.services.config.service.ListResultField; @@ -626,6 +627,7 @@ public abstract class AuthorityItemDocumentModelHandler /* don't even THINK of re-using this method. * String example_uri = "/locationauthorities/7ec60f01-84ab-4908-9a6a/items/a5466530-713f-43b4-bc05"; */ + @Deprecated private String extractInAuthorityCSID(String uri) { String IN_AUTHORITY_REGEX = "/(.*?)/(.*?)/(.*)"; Pattern p = Pattern.compile(IN_AUTHORITY_REGEX); @@ -694,10 +696,20 @@ public abstract class AuthorityItemDocumentModelHandler return termInfoGroupListName.substring(0, termInfoGroupListName.lastIndexOf(LIST_SUFFIX)).toLowerCase(); } + protected String getInAuthorityValue() { + // FIXME: Replace this placeholder / stub + return AuthorityResource.PARENT_WILDCARD; + } + @Override public Map getJDBCQueryParams() { + // FIXME: Get all of the following values from appropriate external constants + final String TERM_GROUP_TABLE_NAME_PARAM = "TERM_GROUP_TABLE_NAME"; + final String IN_AUTHORITY_PARAM = "IN_AUTHORITY"; + Map params = super.getJDBCQueryParams(); - params.put(RepositoryJavaClientImpl.JDBC_TABLE_NAME_PARAM, getTermGroupTableName()); + params.put(TERM_GROUP_TABLE_NAME_PARAM, getTermGroupTableName()); + params.put(IN_AUTHORITY_PARAM, getInAuthorityValue()); return params; } diff --git a/services/common/src/main/java/org/collectionspace/services/nuxeo/client/java/RepositoryJavaClientImpl.java b/services/common/src/main/java/org/collectionspace/services/nuxeo/client/java/RepositoryJavaClientImpl.java index de39486aa..182199751 100644 --- a/services/common/src/main/java/org/collectionspace/services/nuxeo/client/java/RepositoryJavaClientImpl.java +++ b/services/common/src/main/java/org/collectionspace/services/nuxeo/client/java/RepositoryJavaClientImpl.java @@ -101,7 +101,6 @@ public class RepositoryJavaClientImpl implements RepositoryClient queryParams = ctx.getQueryParams(); final String partialTerm = queryParams.getFirst(IQueryManager.SEARCH_TYPE_PARTIALTERM); - // FIXME: Look into whether this performance concern specific to query - // planning with prepared statements may be affecting us: - // http://stackoverflow.com/a/678452 - // If that proves to be a significant concern, we can instead use - // JDBCTools.executeQuery(), and attempt to sanitize user input - // against potential SQL injection attacks. - + // FIXME: Get all of the following values from appropriate external constants + final String TERM_GROUP_TABLE_NAME_PARAM = "TERM_GROUP_TABLE_NAME"; + final String IN_AUTHORITY_PARAM = "IN_AUTHORITY"; + final String PARENT_WILDCARD = "_ALL_"; // Get this from AuthorityResource or equivalent + // FIXME: Replace this placeholder query with an actual query resulting // from CSPACE-5945 work - String sql = + String selectStatement = "SELECT DISTINCT hierarchy.id as id" - + " FROM hierarchy " - + " LEFT JOIN hierarchy h1 " + + " FROM hierarchy "; + + String joinClauses = + " LEFT JOIN hierarchy h1 " + " ON h1.parentid = hierarchy.id " - + " LEFT JOIN " + handler.getJDBCQueryParams().get(JDBC_TABLE_NAME_PARAM) + " tg " + + " LEFT JOIN " + handler.getJDBCQueryParams().get(TERM_GROUP_TABLE_NAME_PARAM) + " tg " + " ON tg.id = h1.id " - + " LEFT JOIN " + handler.getServiceContext().getCommonPartLabel() + " commonschema " - + " ON commonschema.id = hierarchy.id " + " LEFT JOIN misc " - + " ON misc.id = hierarchy.id " - + " WHERE (tg.termdisplayname ILIKE ?) " + + " ON misc.id = hierarchy.id "; + + String whereClause = + " WHERE (tg.termdisplayname ILIKE ?) " + " AND (misc.lifecyclestate <> 'deleted') "; - - // FIXME: Need to add a WHERE clause restriction on inAuthority - - // FIXME: Need to handle the '_ALL_' case for inAuthority by removing - // that restriction (see AuthorityResource.getAuthorityItemList()) - -/* - Pseudo-code-like continuation - String inAuthority = handler.getJDBCQueryParams().get(IN_AUTHORITY_PARAM); - if (Tools.notBlank(inAuthority) { - if (!inAuthority.equals.(AuthorityResource.PARENT_WILDCARD)) { - sql = sql + " AND (commonschema.inauthority = '" + handler.getInAuthorityValue() + "') "; + List params = new ArrayList<>(); + params.add(partialTerm + JDBCTools.SQL_WILDCARD); + + // If a particular authority is specified, restrict the query further + // to records within that authority + String inAuthorityValue = (String) handler.getJDBCQueryParams().get(IN_AUTHORITY_PARAM); + if (Tools.notBlank(inAuthorityValue)) { + // Handle the '_ALL_' case for inAuthority + if (inAuthorityValue.equals(PARENT_WILDCARD)) { + // Add nothing to the query here if it should match within all authorities + } else { + joinClauses = joinClauses + + " LEFT JOIN " + handler.getServiceContext().getCommonPartLabel() + " commonschema " + + " ON commonschema.id = hierarchy.id "; + whereClause = whereClause + + " AND (commonschema.inauthority = ?)"; + params.add(inAuthorityValue); } } -*/ - - // FIXME: We might also consider skipping the JOIN on the common schema table - // in the '_ALL_' case, where we are not restricting by inAuthority value + + String sql = selectStatement + joinClauses + whereClause; - List params = new ArrayList<>(); - params.add(partialTerm + JDBCTools.SQL_WILDCARD); + // FIXME: Look into whether the following performance concern around + // query planning with prepared statements may be affecting us: + // http://stackoverflow.com/a/678452 + // If that proves to be a significant concern, we can instead use + // JDBCTools.executeQuery(), and attempt to sanitize user input + // against potential SQL injection attacks. PreparedStatementSimpleBuilder jdbcFilterQueryBuilder = new PreparedStatementSimpleBuilder(sql, params); List docIds = new ArrayList<>(); -- 2.47.3