Java tutorial
/* * (C) Copyright 2010 Nuxeo SA (http://nuxeo.com/) and others. * * Licensed 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 * * http://www.apache.org/licenses/LICENSE-2.0 * * 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. * * Contributors: * Anahide Tchertchian */ package org.nuxeo.ecm.platform.query.nxql; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import java.util.GregorianCalendar; import java.util.List; import java.util.Map; import java.util.regex.Matcher; import java.util.regex.Pattern; import org.apache.commons.lang.StringUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.nuxeo.common.collections.ScopeType; import org.nuxeo.ecm.core.api.DocumentModel; import org.nuxeo.ecm.core.api.NuxeoException; import org.nuxeo.ecm.core.api.PropertyException; import org.nuxeo.ecm.core.api.SortInfo; import org.nuxeo.ecm.core.api.model.PropertyNotFoundException; import org.nuxeo.ecm.core.query.sql.NXQL; import org.nuxeo.ecm.core.query.sql.model.Literal; import org.nuxeo.ecm.core.schema.SchemaManager; import org.nuxeo.ecm.core.schema.types.Field; import org.nuxeo.ecm.core.schema.types.Schema; import org.nuxeo.ecm.core.schema.types.SimpleTypeImpl; import org.nuxeo.ecm.core.schema.types.Type; import org.nuxeo.ecm.core.schema.types.primitives.StringType; import org.nuxeo.ecm.core.search.api.client.querymodel.Escaper; import org.nuxeo.ecm.platform.query.api.PageProviderService; import org.nuxeo.ecm.platform.query.api.PredicateDefinition; import org.nuxeo.ecm.platform.query.api.PredicateFieldDefinition; import org.nuxeo.ecm.platform.query.api.WhereClauseDefinition; import org.nuxeo.ecm.platform.query.core.FieldDescriptor; import org.nuxeo.runtime.api.Framework; import org.nuxeo.runtime.services.config.ConfigurationService; /** * Helper to generate NXQL queries from XMap descriptors * * @since 5.4 * @author Anahide Tchertchian */ public class NXQLQueryBuilder { private static final Log log = LogFactory.getLog(NXQLQueryBuilder.class); // @since 5.9.2 public static final String DEFAULT_SELECT_STATEMENT = "SELECT * FROM Document"; // @since 5.9 public static final String SORTED_COLUMN = "SORTED_COLUMN"; public static final String REGEXP_NAMED_PARAMETER = "[^a-zA-Z]:\\s*" + "([a-zA-Z0-9:]*)"; public static final String REGEXP_EXCLUDE_QUOTE = "'[^']*'"; public static final String REGEXP_EXCLUDE_DOUBLE_QUOTE = "\"[^\"]*\""; private NXQLQueryBuilder() { } public static String getSortClause(SortInfo... sortInfos) { StringBuilder queryBuilder = new StringBuilder(); if (sortInfos != null) { int index = 0; for (SortInfo sortInfo : sortInfos) { String sortColumn = sortInfo.getSortColumn(); boolean sortAscending = sortInfo.getSortAscending(); if (index == 0) { queryBuilder.append("ORDER BY ").append(sortColumn).append(' ') .append(sortAscending ? "" : "DESC"); } else { queryBuilder.append(", ").append(sortColumn).append(' ').append(sortAscending ? "" : "DESC"); } index++; } } return queryBuilder.toString(); } public static String getQuery(DocumentModel model, WhereClauseDefinition whereClause, Object[] params, SortInfo... sortInfos) { return getQuery(model, whereClause, null, params, sortInfos); } /** * @since 8.4 */ public static String getQuery(DocumentModel model, WhereClauseDefinition whereClause, String quickFiltersClause, Object[] params, SortInfo... sortInfos) { StringBuilder queryBuilder = new StringBuilder(); String selectStatement = whereClause.getSelectStatement(); if (StringUtils.isBlank(selectStatement)) { selectStatement = DEFAULT_SELECT_STATEMENT; } queryBuilder.append(selectStatement); if (whereClause != null) { queryBuilder.append(getQueryElement(model, whereClause, quickFiltersClause, params)); } String sortClause = getSortClause(sortInfos); if (sortClause != null && sortClause.length() > 0) { queryBuilder.append(" "); queryBuilder.append(sortClause); } return queryBuilder.toString().trim(); } public static String getQueryElement(DocumentModel model, WhereClauseDefinition whereClause, Object[] params) { return getQueryElement(model, whereClause, null, params); } /** * @since 8.4 */ public static String getQueryElement(DocumentModel model, WhereClauseDefinition whereClause, String quickFiltersClause, Object[] params) { List<String> elements = new ArrayList<String>(); PredicateDefinition[] predicates = whereClause.getPredicates(); if (predicates != null) { Escaper escaper = null; Class<? extends Escaper> escaperClass = whereClause.getEscaperClass(); if (escaperClass != null) { try { escaper = escaperClass.newInstance(); } catch (ReflectiveOperationException e) { throw new NuxeoException(e); } } for (PredicateDefinition predicate : predicates) { String predicateString = getQueryElement(model, predicate, escaper); if (predicateString == null) { continue; } predicateString = predicateString.trim(); if (!predicateString.equals("")) { elements.add(predicateString); } } } // add fixed part if applicable String fixedPart = whereClause.getFixedPart(); if (!StringUtils.isBlank(fixedPart)) { if (StringUtils.isNotBlank(quickFiltersClause)) { fixedPart = appendClause(fixedPart, quickFiltersClause); } if (elements.isEmpty()) { elements.add(getQuery(fixedPart, params, whereClause.getQuoteFixedPartParameters(), whereClause.getEscapeFixedPartParameters(), model)); } else { elements.add('(' + getQuery(fixedPart, params, whereClause.getQuoteFixedPartParameters(), whereClause.getEscapeFixedPartParameters(), model) + ')'); } } else if (StringUtils.isNotBlank(quickFiltersClause)) { fixedPart = quickFiltersClause; } if (elements.isEmpty()) { return ""; } // XXX: for now only a one level implement conjunctive WHERE clause String clauseValues = StringUtils.join(elements, " AND ").trim(); // GR: WHERE (x = 1) is invalid NXQL while (elements.size() == 1 && clauseValues.startsWith("(") && clauseValues.endsWith(")")) { clauseValues = clauseValues.substring(1, clauseValues.length() - 1).trim(); } if (clauseValues.length() == 0) { return ""; } return " WHERE " + clauseValues; } public static String getQuery(String pattern, Object[] params, boolean quoteParameters, boolean escape, DocumentModel searchDocumentModel, SortInfo... sortInfos) { String sortedColumn; if (sortInfos == null || sortInfos.length == 0) { // If there is no ORDER BY use the id sortedColumn = NXQL.ECM_UUID; } else { sortedColumn = sortInfos[0].getSortColumn(); } if (pattern != null && pattern.contains(SORTED_COLUMN)) { pattern = pattern.replace(SORTED_COLUMN, sortedColumn); } StringBuilder queryBuilder; // handle named parameters replacements if (searchDocumentModel != null) { // Find all query named parameters as ":parameter" not between // quotes and add them to matches String query = pattern.replaceAll(REGEXP_EXCLUDE_DOUBLE_QUOTE, StringUtils.EMPTY); query = query.replaceAll(REGEXP_EXCLUDE_QUOTE, StringUtils.EMPTY); Pattern p1 = Pattern.compile(REGEXP_NAMED_PARAMETER); Matcher m1 = p1.matcher(query); List<String> matches = new ArrayList<String>(); while (m1.find()) { matches.add(m1.group().substring(m1.group().indexOf(":") + 1)); } for (String key : matches) { Object parameter = getRawValue(searchDocumentModel, new FieldDescriptor(key)); if (parameter == null) { continue; } key = ":" + key; if (parameter instanceof String[]) { replaceStringList(pattern, Arrays.asList((String[]) parameter), quoteParameters, escape, key); } else if (parameter instanceof List) { replaceStringList(pattern, (List<?>) parameter, quoteParameters, escape, key); } else if (parameter instanceof Boolean) { pattern = buildPattern(pattern, key, ((Boolean) parameter) ? "1" : "0"); } else if (parameter instanceof Number) { pattern = buildPattern(pattern, key, parameter.toString()); } else if (parameter instanceof Literal) { if (quoteParameters) { pattern = buildPattern(pattern, key, "'" + parameter.toString() + "'"); } else { pattern = buildPattern(pattern, key, ((Literal) parameter).asString()); } } else { if (quoteParameters) { pattern = buildPattern(pattern, key, "'" + parameter + "'"); } else { pattern = buildPattern(pattern, key, parameter != null ? parameter.toString() : null); } } } } if (params == null) { queryBuilder = new StringBuilder(pattern + ' '); } else { // handle "standard" parameters replacements (referenced by ? characters) // XXX: the + " " is a workaround for the buggy implementation // of the split function in case the pattern ends with '?' String[] queryStrList = (pattern + ' ').split("\\?"); queryBuilder = new StringBuilder(queryStrList[0]); for (int i = 0; i < params.length; i++) { if (params[i] instanceof String[]) { appendStringList(queryBuilder, Arrays.asList((String[]) params[i]), quoteParameters, escape); } else if (params[i] instanceof List) { appendStringList(queryBuilder, (List<?>) params[i], quoteParameters, escape); } else if (params[i] instanceof Boolean) { boolean b = ((Boolean) params[i]).booleanValue(); queryBuilder.append(b ? 1 : 0); } else if (params[i] instanceof Number) { queryBuilder.append(params[i]); } else if (params[i] instanceof Literal) { if (quoteParameters) { queryBuilder.append(params[i].toString()); } else { queryBuilder.append(((Literal) params[i]).asString()); } } else { if (params[i] == null) { if (quoteParameters) { queryBuilder.append("''"); } } else { String queryParam = params[i].toString(); queryBuilder.append(prepareStringLiteral(queryParam, quoteParameters, escape)); } } queryBuilder.append(queryStrList[i + 1]); } } queryBuilder.append(getSortClause(sortInfos)); return queryBuilder.toString().trim(); } public static void appendStringList(StringBuilder queryBuilder, List<?> listParam, boolean quoteParameters, boolean escape) { // avoid appending parentheses if the query builder ends with one boolean addParentheses = !queryBuilder.toString().endsWith("("); if (addParentheses) { queryBuilder.append('('); } List<String> result = new ArrayList<String>(listParam.size()); for (Object param : listParam) { result.add(prepareStringLiteral(param.toString(), quoteParameters, escape)); } queryBuilder.append(StringUtils.join(result, ", ")); if (addParentheses) { queryBuilder.append(')'); } } public static String replaceStringList(String pattern, List<?> listParams, boolean quoteParameters, boolean escape, String key) { List<String> result = new ArrayList<String>(listParams.size()); for (Object param : listParams) { result.add(prepareStringLiteral(param.toString(), quoteParameters, escape)); } return buildPattern(pattern, key, '(' + StringUtils.join(result, ", " + "") + ')'); } /** * Return the string literal in a form ready to embed in an NXQL statement. */ public static String prepareStringLiteral(String s, boolean quoteParameter, boolean escape) { if (escape) { if (quoteParameter) { return NXQL.escapeString(s); } else { return NXQL.escapeStringInner(s); } } else { if (quoteParameter) { return "'" + s + "'"; } else { return s; } } } public static String getQueryElement(DocumentModel model, PredicateDefinition predicateDescriptor, Escaper escaper) { String type = predicateDescriptor.getType(); if (PredicateDefinition.ATOMIC_PREDICATE.equals(type)) { return atomicQueryElement(model, predicateDescriptor, escaper); } if (PredicateDefinition.SUB_CLAUSE_PREDICATE.equals(type)) { return subClauseQueryElement(model, predicateDescriptor); } throw new NuxeoException("Unknown predicate type: " + type); } protected static String subClauseQueryElement(DocumentModel model, PredicateDefinition predicateDescriptor) { PredicateFieldDefinition[] values = predicateDescriptor.getValues(); if (values == null || values.length != 1) { throw new NuxeoException("subClause predicate needs exactly one field"); } PredicateFieldDefinition fieldDescriptor = values[0]; if (!getFieldType(model, fieldDescriptor).equals("string")) { if (fieldDescriptor.getXpath() != null) { throw new NuxeoException( String.format("type of field %s is not string", fieldDescriptor.getXpath())); } else { throw new NuxeoException(String.format("type of field %s.%s is not string", fieldDescriptor.getSchema(), fieldDescriptor.getName())); } } Object subclauseValue = getRawValue(model, fieldDescriptor); if (subclauseValue == null) { return ""; } return "(" + subclauseValue + ")"; } protected static String atomicQueryElement(DocumentModel model, PredicateDefinition predicateDescriptor, Escaper escaper) { String operator = null; String operatorField = predicateDescriptor.getOperatorField(); String operatorSchema = predicateDescriptor.getOperatorSchema(); PredicateFieldDefinition[] values = predicateDescriptor.getValues(); if (operatorField != null && operatorSchema != null) { PredicateFieldDefinition operatorFieldDescriptor = new FieldDescriptor(operatorSchema, operatorField); operator = getPlainStringValue(model, operatorFieldDescriptor); if (operator != null) { operator = operator.toUpperCase(); } } if (StringUtils.isBlank(operator)) { operator = predicateDescriptor.getOperator(); } String hint = predicateDescriptor.getHint(); String parameter = getParameterWithHint(operator, predicateDescriptor.getParameter(), hint); if (operator.equals("=") || operator.equals("!=") || operator.equals("<") || operator.equals(">") || operator.equals("<=") || operator.equals(">=") || operator.equals("<>") || operator.equals("LIKE") || operator.equals("ILIKE")) { // Unary predicate String value = getStringValue(model, values[0]); if (value == null) { // value not provided: ignore predicate return ""; } if (escaper != null && (operator.equals("LIKE") || operator.equals("ILIKE"))) { value = escaper.escape(value); } return serializeUnary(parameter, operator, value); } else if (operator.equals("BETWEEN")) { String min = getStringValue(model, values[0]); String max = getStringValue(model, values[1]); if (min != null && max != null) { StringBuilder builder = new StringBuilder(); builder.append(parameter); builder.append(' '); builder.append(operator); builder.append(' '); builder.append(min); builder.append(" AND "); builder.append(max); return builder.toString(); } else if (max != null) { return serializeUnary(parameter, "<=", max); } else if (min != null) { return serializeUnary(parameter, ">=", min); } else { // both min and max are not provided, ignore predicate return ""; } } else if (operator.equals("IN")) { List<String> options = getListValue(model, values[0]); if (options == null || options.isEmpty()) { return ""; } else if (options.size() == 1) { return serializeUnary(parameter, "=", options.get(0)); } else { StringBuilder builder = new StringBuilder(); builder.append(parameter); builder.append(" IN ("); for (int i = 0; i < options.size(); i++) { if (i != 0) { builder.append(", "); } builder.append(options.get(i)); } builder.append(')'); return builder.toString(); } } else if (operator.equals("STARTSWITH")) { String fieldType = getFieldType(model, values[0]); if (fieldType.equals("string")) { String value = getStringValue(model, values[0]); if (value == null) { return ""; } else { return serializeUnary(parameter, operator, value); } } else { List<String> options = getListValue(model, values[0]); if (options == null || options.isEmpty()) { return ""; } else if (options.size() == 1) { return serializeUnary(parameter, operator, options.get(0)); } else { StringBuilder builder = new StringBuilder(); builder.append('('); for (int i = 0; i < options.size() - 1; i++) { builder.append(serializeUnary(parameter, operator, options.get(i))); builder.append(" OR "); } builder.append(serializeUnary(parameter, operator, options.get(options.size() - 1))); builder.append(')'); return builder.toString(); } } } else if (operator.equals("EMPTY") || operator.equals("ISEMPTY")) { return parameter + " = ''"; } else if (operator.equals("FULLTEXT ALL") // BBB || operator.equals("FULLTEXT")) { String value = getPlainStringValue(model, values[0]); if (value == null) { // value not provided: ignore predicate return ""; } if (escaper != null) { value = escaper.escape(value); } return parameter + ' ' + serializeFullText(value); } else if (operator.equals("IS NULL")) { Boolean value = getBooleanValue(model, values[0]); if (value == null) { // value not provided: ignore predicate return ""; } else if (Boolean.TRUE.equals(value)) { return parameter + " IS NULL"; } else { return parameter + " IS NOT NULL"; } } else { throw new NuxeoException("Unsupported operator: " + operator); } } protected static String getParameterWithHint(String operator, String parameter, String hint) { String ret = parameter; // add ecm:fulltext. prefix if needed if ((operator.equals("FULLTEXT ALL") || operator.equals("FULLTEXT")) && !parameter.startsWith(NXQL.ECM_FULLTEXT)) { ret = NXQL.ECM_FULLTEXT + '.' + parameter; } // add the hint if (hint != null && !hint.isEmpty()) { ret = String.format("/*+%s */ %s", hint.trim(), ret); } return ret; } /** * Prepares a statement for a fulltext field by converting FULLTEXT virtual operators to a syntax that the search * syntax accepts. * * @param value * @return the serialized statement */ public static final String DEFAULT_SPECIAL_CHARACTERS_REGEXP = "!#$%&'()+,./\\\\:-@{|}`^~"; public static final String IGNORED_CHARS_KEY = "org.nuxeo.query.builder.ignored.chars"; /** * Remove any special character that could be mis-interpreted as a low level full-text query operator. This method * should be used by user facing callers of CoreQuery*PageProviders that use a fixed part or a pattern query. Fields * in where clause already dealt with. * * @since 5.6 * @return sanitized text value */ public static String sanitizeFulltextInput(String value) { // Ideally the low level full-text language // parser should be robust to any user input however this is much more // complicated to implement correctly than the following simple user // input filtering scheme. ConfigurationService cs = Framework.getService(ConfigurationService.class); String ignoredChars = cs.getProperty(IGNORED_CHARS_KEY, DEFAULT_SPECIAL_CHARACTERS_REGEXP); String res = ""; value = value.replaceAll("[" + ignoredChars + "]", " "); value = value.trim(); String[] tokens = value.split("[\\s]+"); for (int i = 0; i < tokens.length; i++) { if ("-".equals(tokens[i]) || "*".equals(tokens[i]) || "*-".equals(tokens[i]) || "-*".equals(tokens[i])) { continue; } if (res.length() > 0) { res += " "; } if (tokens[i].startsWith("-") || tokens[i].endsWith("*")) { res += tokens[i]; } else { res += tokens[i].replace("-", " ").replace("*", " "); } } return res.trim(); } public static String serializeFullText(String value) { value = sanitizeFulltextInput(value); return "= " + NXQL.escapeString(value); } protected static String serializeUnary(String parameter, String operator, String rvalue) { StringBuilder builder = new StringBuilder(); builder.append(parameter); builder.append(' '); builder.append(operator); builder.append(' '); builder.append(rvalue); return builder.toString(); } public static String getPlainStringValue(DocumentModel model, PredicateFieldDefinition fieldDescriptor) { Object rawValue = getRawValue(model, fieldDescriptor); if (rawValue == null) { return null; } String value = (String) rawValue; if (value.equals("")) { return null; } return value; } public static Integer getIntValue(DocumentModel model, PredicateFieldDefinition fieldDescriptor) { Object rawValue = getRawValue(model, fieldDescriptor); if (rawValue == null || "".equals(rawValue)) { return null; } else if (rawValue instanceof Integer) { return (Integer) rawValue; } else if (rawValue instanceof String) { return Integer.valueOf((String) rawValue); } else { return Integer.valueOf(rawValue.toString()); } } public static String getFieldType(DocumentModel model, PredicateFieldDefinition fieldDescriptor) { String xpath = fieldDescriptor.getXpath(); String schema = fieldDescriptor.getSchema(); String name = fieldDescriptor.getName(); try { SchemaManager typeManager = Framework.getService(SchemaManager.class); Field field = null; if (xpath != null) { if (model != null) { field = model.getProperty(xpath).getField(); } } else { if (schema != null) { Schema schemaObj = typeManager.getSchema(schema); if (schemaObj == null) { throw new NuxeoException("failed to obtain schema: " + schema); } field = schemaObj.getField(name); } else { // assume named parameter use case: hard-code on String in this case return StringType.ID; } } if (field == null) { throw new NuxeoException("failed to obtain field: " + schema + ":" + name); } Type type = field.getType(); if (type instanceof SimpleTypeImpl) { // type with constraint type = type.getSuperType(); } return type.getName(); } catch (PropertyException e) { e.addInfo("failed to get field type for " + (xpath != null ? xpath : (schema + ":" + name))); throw e; } } @SuppressWarnings("unchecked") public static Object getRawValue(DocumentModel model, PredicateFieldDefinition fieldDescriptor) { String xpath = fieldDescriptor.getXpath(); String schema = fieldDescriptor.getSchema(); String name = fieldDescriptor.getName(); try { if (xpath != null) { return model.getPropertyValue(xpath); } else if (schema == null) { return model.getPropertyValue(name); } else { return model.getProperty(schema, name); } } catch (PropertyNotFoundException e) { // fall back on named parameters if any Map<String, Object> params = (Map<String, Object>) model.getContextData() .getScopedValue(ScopeType.DEFAULT, PageProviderService.NAMED_PARAMETERS); if (params != null) { if (xpath != null) { return params.get(xpath); } else { return params.get(name); } } } catch (PropertyException e) { return null; } return null; } public static String getStringValue(DocumentModel model, PredicateFieldDefinition fieldDescriptor) { Object rawValue = getRawValue(model, fieldDescriptor); if (rawValue == null) { return null; } String value; if (rawValue instanceof GregorianCalendar) { GregorianCalendar gc = (GregorianCalendar) rawValue; value = "TIMESTAMP '" + getDateFormat().format(gc.getTime()) + "'"; } else if (rawValue instanceof Date) { Date date = (Date) rawValue; value = "TIMESTAMP '" + getDateFormat().format(date) + "'"; } else if (rawValue instanceof Integer || rawValue instanceof Long || rawValue instanceof Double) { value = rawValue.toString(); // no quotes } else if (rawValue instanceof Boolean) { value = ((Boolean) rawValue).booleanValue() ? "1" : "0"; } else { value = rawValue.toString().trim(); if (value.equals("")) { return null; } String fieldType = getFieldType(model, fieldDescriptor); if ("long".equals(fieldType) || "integer".equals(fieldType) || "double".equals(fieldType)) { return value; } else { return NXQL.escapeString(value); } } return value; } protected static DateFormat getDateFormat() { // not thread-safe so don't use a static instance return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); } @SuppressWarnings("unchecked") public static List<String> getListValue(DocumentModel model, PredicateFieldDefinition fieldDescriptor) { Object rawValue = getRawValue(model, fieldDescriptor); if (rawValue == null) { return null; } List<String> values = new ArrayList<String>(); if (rawValue instanceof ArrayList) { rawValue = ((ArrayList<Object>) rawValue).toArray(); } for (Object element : (Object[]) rawValue) { if (element != null) { if (element instanceof Number) { values.add(element.toString()); } else { String value = element.toString().trim(); if (!value.equals("")) { values.add(NXQL.escapeString(value)); } } } } return values; } public static Boolean getBooleanValue(DocumentModel model, PredicateFieldDefinition fieldDescriptor) { Object rawValue = getRawValue(model, fieldDescriptor); if (rawValue == null) { return null; } else { return (Boolean) rawValue; } } /** * @since 8.4 */ public static String appendClause(String query, String clause) { return query + " AND " + clause; } /** * @since 8.4 */ public static String buildPattern(String pattern, String key, String replacement) { int index = pattern.indexOf(key); while (index >= 0) { // All keys not prefixed by a letter or a digit has to be replaced, because // It could be part of a schema name if (!Character.isLetterOrDigit(pattern.charAt(index - 1)) && (index + key.length() == pattern.length() || !Character.isLetterOrDigit(pattern.charAt(index + key.length())))) { pattern = pattern.substring(0, index) + pattern.substring(index).replaceFirst(key, replacement); } index = pattern.indexOf(key, index + 1); } return pattern; } }