Search in sources :

Example 1 with QueryValidationException

use of org.folio.cql2pgjson.exception.QueryValidationException in project raml-module-builder by folio-org.

the class CQL2PgJSON method toSql.

/**
 * Convert the CQL query into a SQL query and return the WHERE and the ORDER BY clause.
 * @param cql  the query to convert
 * @return SQL query
 * @throws QueryValidationException
 */
public SqlSelect toSql(String cql) throws QueryValidationException {
    try {
        CQLParser parser = new CQLParser();
        CQLNode node = parser.parse(cql);
        return toSql(node);
    } catch (IOException | CQLParseException e) {
        throw new QueryValidationException(e);
    }
}
Also used : QueryValidationException(org.folio.cql2pgjson.exception.QueryValidationException) IOException(java.io.IOException) CQLParser(org.z3950.zing.cql.CQLParser) CQLParseException(org.z3950.zing.cql.CQLParseException) CQLNode(org.z3950.zing.cql.CQLNode)

Example 2 with QueryValidationException

use of org.folio.cql2pgjson.exception.QueryValidationException in project raml-module-builder by folio-org.

the class CQL2PgJSON method arrayNode.

private String arrayNode(String index, CQLTermNode node, CqlModifiers modifiers, List<Modifier> relationModifiers, Index schemaIndex, IndexTextAndJsonValues incomingvals, Table targetTable) throws QueryValidationException {
    StringBuilder sqlAnd = new StringBuilder();
    StringBuilder sqlOr = new StringBuilder();
    // avoid recursion
    modifiers.setRelationModifiers(new LinkedList<>());
    for (Modifier relationModifier : relationModifiers) {
        final String modifierName = relationModifier.getType().substring(1);
        final String modifierValue = relationModifier.getValue();
        String foundModifier = lookupModifier(schemaIndex, modifierName);
        if (foundModifier == null) {
            throw new QueryValidationException("CQL: Unsupported relation modifier " + relationModifier.getType());
        }
        if (modifierValue == null) {
            if (sqlOr.length() == 0) {
                sqlOr.append("(");
            } else {
                sqlOr.append(" or ");
            }
            IndexTextAndJsonValues vals = new IndexTextAndJsonValues();
            vals.setIndexText(SqlUtil.Cql2PgUtil.cqlNameAsSqlText("t.c", foundModifier));
            sqlOr.append(indexNode(index, this.dbTable, node, vals, modifiers));
        } else {
            final String comparator = relationModifier.getComparison();
            if (!"=".equals(comparator)) {
                throw new QueryValidationException("CQL: Unsupported comparison for relation modifier " + relationModifier.getType());
            }
            sqlAnd.append(" and ");
            sqlAnd.append(queryByFt(SqlUtil.Cql2PgUtil.cqlNameAsSqlText("t.c", foundModifier), modifierValue, comparator, schemaIndex, targetTable));
        }
    }
    if (sqlOr.length() > 0) {
        sqlOr.append(")");
    } else {
        String modifiersSubfield = null;
        if (schemaIndex != null) {
            modifiersSubfield = schemaIndex.getArraySubfield();
        }
        if (modifiersSubfield == null) {
            throw new QueryValidationException("CQL: No arraySubfield defined for index " + index);
        }
        IndexTextAndJsonValues vals = new IndexTextAndJsonValues();
        vals.setIndexText(SqlUtil.Cql2PgUtil.cqlNameAsSqlText("t.c", modifiersSubfield));
        sqlOr.append(indexNode(index, this.dbTable, node, vals, modifiers));
    }
    return "id in (select t.id" + " from (select id as id, " + "             jsonb_array_elements(" + incomingvals.getIndexJson() + ") as c" + "      ) as t" + " where " + sqlOr.toString() + sqlAnd.toString() + ")";
}
Also used : IndexTextAndJsonValues(org.folio.cql2pgjson.model.IndexTextAndJsonValues) QueryValidationException(org.folio.cql2pgjson.exception.QueryValidationException) Modifier(org.z3950.zing.cql.Modifier)

Example 3 with QueryValidationException

use of org.folio.cql2pgjson.exception.QueryValidationException in project raml-module-builder by folio-org.

the class CQL2PgJSON method cql2pgJson.

/**
 * Return an SQL WHERE clause for the CQL expression.
 * @param cql  CQL expression to convert
 * @return SQL WHERE clause, without leading "WHERE ", may contain "ORDER BY" clause
 * @throws QueryValidationException  when parsing or validating cql fails
 *
 * @deprecated use toSql instead
 */
@Deprecated
public String cql2pgJson(String cql) throws QueryValidationException {
    try {
        CQLParser parser = new CQLParser();
        CQLNode node = parser.parse(cql);
        return pg(node);
    } catch (IOException | CQLParseException e) {
        throw new QueryValidationException(e);
    }
}
Also used : QueryValidationException(org.folio.cql2pgjson.exception.QueryValidationException) IOException(java.io.IOException) CQLParser(org.z3950.zing.cql.CQLParser) CQLParseException(org.z3950.zing.cql.CQLParseException) CQLNode(org.z3950.zing.cql.CQLNode)

Example 4 with QueryValidationException

use of org.folio.cql2pgjson.exception.QueryValidationException in project raml-module-builder by folio-org.

the class CQL2PgJSONTest method select.

/**
 * @param expectedNames the semicolon+space separated list of expected names, or -- if there should
 *          be an exception -- the expected substring of the error message prepended by an exclamation mark.
 */
public void select(CQL2PgJSON aCql2pgJson, String sqlFile, String cql, String expectedNames) {
    if (!cql.contains(" sortBy ")) {
        cql += " sortBy name";
    }
    String sql = null;
    try {
        String blob = "user_data";
        String tablename = "users";
        // this mess up
        if ("instances.jsonb".equals(aCql2pgJson.getjsonField())) {
            blob = "jsonb";
            tablename = "instances";
        }
        SqlSelect sqlSelect = aCql2pgJson.toSql(cql);
        if (rejectLower) {
            assertThat(sqlSelect.getWhere().toLowerCase(Locale.ROOT), not(containsString("lower")));
        }
        sql = "select " + blob + "->'name' from " + tablename + " " + sqlSelect;
        logger.info("select: CQL --> SQL: " + cql + " --> " + sql);
        runSqlFile(sqlFile);
        logger.fine("select: sqlfile done");
        String actualNames = "";
        try (Statement statement = conn.createStatement();
            ResultSet result = statement.executeQuery(sql)) {
            while (result.next()) {
                if (!actualNames.isEmpty()) {
                    actualNames += "; ";
                }
                actualNames += result.getString(1).replace("\"", "");
            }
        }
        if (!expectedNames.equals(actualNames)) {
            logger.fine("select: Test FAILURE on " + cql + "#" + expectedNames);
        }
        logger.fine("select: Got names [" + actualNames + "], expected [" + expectedNames + "]");
        assertEquals("CQL: " + cql + ", SQL: " + sql, expectedNames, actualNames);
    } catch (QueryValidationException | SQLException e) {
        logger.fine("select: " + e.getClass().getSimpleName() + " for query " + cql + " : " + e.getMessage());
        if (!expectedNames.startsWith("!")) {
            throw new RuntimeException(sql != null ? sql : cql, e);
        }
        assertThat(e.toString(), containsString(expectedNames.substring(1).trim()));
    }
    logger.fine("select: done with " + cql);
}
Also used : SqlSelect(org.folio.cql2pgjson.model.SqlSelect) QueryValidationException(org.folio.cql2pgjson.exception.QueryValidationException) SQLException(java.sql.SQLException) Statement(java.sql.Statement) ResultSet(java.sql.ResultSet)

Example 5 with QueryValidationException

use of org.folio.cql2pgjson.exception.QueryValidationException in project raml-module-builder by folio-org.

the class PgUtil method getWithOptimizedSql.

/**
 * Run the cql query using optimized SQL (if possible) or standard SQL.
 * <p>
 * PostgreSQL has no statistics about a field within a JSONB resulting in bad performance.
 * <p>
 * This method requires that the sortField has a b-tree index (non-unique) and caseSensitive=false
 * and removeAccents=true, and that the cql query is supported by a full text index.
 * <p>
 * This method starts a full table scan until getOptimizedSqlSize() records have been scanned.
 * Then it assumes that there are only a few result records and uses the full text match.
 * If the requested number of records have been found it stops immediately.
 *  @param table
 * @param clazz
 * @param cql
 * @param queryTimeout query timeout in milliseconds, or 0 for no timeout
 * @param okapiHeaders
 * @param vertxContext
 * @param responseDelegateClass
 * @return
 */
public static <T, C> Future<Response> getWithOptimizedSql(String table, Class<T> clazz, Class<C> collectionClazz, String sortField, String cql, int offset, int limit, int queryTimeout, Map<String, String> okapiHeaders, Context vertxContext, Class<? extends ResponseDelegate> responseDelegateClass) {
    final Method respond500;
    try {
        respond500 = responseDelegateClass.getMethod(RESPOND_500_WITH_TEXT_PLAIN, Object.class);
    } catch (Exception e) {
        logger.error(e.getMessage(), e);
        return response(e.getMessage(), null, null);
    }
    final Method respond200;
    final Method respond400;
    try {
        respond200 = responseDelegateClass.getMethod(RESPOND_200_WITH_APPLICATION_JSON, collectionClazz);
        respond400 = responseDelegateClass.getMethod(RESPOND_400_WITH_TEXT_PLAIN, Object.class);
    } catch (Exception e) {
        logger.error(e.getMessage(), e);
        return response(e.getMessage(), respond500, respond500);
    }
    try {
        CQL2PgJSON cql2pgJson = new CQL2PgJSON(table + "." + JSON_COLUMN);
        CQLWrapper cqlWrapper = new CQLWrapper(cql2pgJson, cql, limit, offset);
        PreparedCQL preparedCql = new PreparedCQL(table, cqlWrapper, okapiHeaders);
        String sql = generateOptimizedSql(sortField, preparedCql, offset, limit);
        if (sql == null) {
            // the cql is not suitable for optimization, generate simple sql
            return get(preparedCql, clazz, collectionClazz, okapiHeaders, vertxContext, responseDelegateClass);
        }
        logger.info("Optimized SQL generated. Source CQL: " + cql);
        Promise<Response> promise = Promise.promise();
        PostgresClient postgresClient = postgresClient(vertxContext, okapiHeaders);
        postgresClient.select(sql, queryTimeout, reply -> {
            try {
                if (reply.failed()) {
                    Throwable cause = reply.cause();
                    logger.error("Optimized SQL failed: " + cause.getMessage() + ": " + sql, cause);
                    response(cause.getMessage(), respond500, respond500).onComplete(promise);
                    return;
                }
                C collection = collection(clazz, collectionClazz, reply.result(), offset, limit);
                response(collection, respond200, respond500).onComplete(promise);
            } catch (Exception e) {
                logger.error(e.getMessage(), e);
                response(e.getMessage(), respond500, respond500).onComplete(promise);
            }
        });
        return promise.future();
    } catch (FieldException | QueryValidationException e) {
        logger.error(e.getMessage(), e);
        return response(e.getMessage(), respond400, respond500);
    } catch (Exception e) {
        logger.error(e.getMessage(), e);
        return response(e.getMessage(), respond500, respond500);
    }
}
Also used : Method(java.lang.reflect.Method) FieldException(org.folio.cql2pgjson.exception.FieldException) InvocationTargetException(java.lang.reflect.InvocationTargetException) QueryValidationException(org.folio.cql2pgjson.exception.QueryValidationException) CQLParseException(org.z3950.zing.cql.CQLParseException) CQLFeatureUnsupportedException(org.folio.cql2pgjson.exception.CQLFeatureUnsupportedException) JsonProcessingException(com.fasterxml.jackson.core.JsonProcessingException) IOException(java.io.IOException) Response(javax.ws.rs.core.Response) HttpServerResponse(io.vertx.core.http.HttpServerResponse) CQL2PgJSON(org.folio.cql2pgjson.CQL2PgJSON) FieldException(org.folio.cql2pgjson.exception.FieldException) QueryValidationException(org.folio.cql2pgjson.exception.QueryValidationException) CQLWrapper(org.folio.rest.persist.cql.CQLWrapper)

Aggregations

QueryValidationException (org.folio.cql2pgjson.exception.QueryValidationException)9 IOException (java.io.IOException)4 Async (io.vertx.ext.unit.Async)3 PgException (io.vertx.pgclient.PgException)3 Test (org.junit.Test)3 CQLNode (org.z3950.zing.cql.CQLNode)3 CQLParseException (org.z3950.zing.cql.CQLParseException)3 CQLFeatureUnsupportedException (org.folio.cql2pgjson.exception.CQLFeatureUnsupportedException)2 FieldException (org.folio.cql2pgjson.exception.FieldException)2 IndexTextAndJsonValues (org.folio.cql2pgjson.model.IndexTextAndJsonValues)2 SqlSelect (org.folio.cql2pgjson.model.SqlSelect)2 CQLParser (org.z3950.zing.cql.CQLParser)2 JsonProcessingException (com.fasterxml.jackson.core.JsonProcessingException)1 HttpServerResponse (io.vertx.core.http.HttpServerResponse)1 InvocationTargetException (java.lang.reflect.InvocationTargetException)1 Method (java.lang.reflect.Method)1 ResultSet (java.sql.ResultSet)1 SQLException (java.sql.SQLException)1 Statement (java.sql.Statement)1 ArrayList (java.util.ArrayList)1