Examples with ParsedQuery org.folio.rest.persist.facets.ParsedQuery used on opensource projects

Example 1 with ParsedQuery

use of org.folio.rest.persist.facets.ParsedQuery in project raml-module-builder by folio-org.

the class PostgresClient method parseQuery.

/**
 * returns ParsedQuery with:
 * 1. Original query stripped of the order by, limit and offset clauses (if they existed in the query)
 * 2. Original query stripped of the limit and offset clauses (if they existed in the query)
 * 3. where clause part of query (included in the stripped query)
 * 4. original order by clause that was removed (or null)
 * 5. original limit clause that was removed (or null)
 * 6. original offset clause that was removed (or null)
 * @param query
 * @return
 */
static ParsedQuery parseQuery(String query) {
    List<OrderByElement> orderBy = null;
    net.sf.jsqlparser.statement.select.Limit limit = null;
    Expression where = null;
    net.sf.jsqlparser.statement.select.Offset offset = null;
    long start = System.nanoTime();
    String queryWithoutLimitOffset = "";
    try {
        try {
            net.sf.jsqlparser.statement.Statement statement = CCJSqlParserUtil.parse(query);
            Select selectStatement = (Select) statement;
            orderBy = ((PlainSelect) selectStatement.getSelectBody()).getOrderByElements();
            limit = ((PlainSelect) selectStatement.getSelectBody()).getLimit();
            offset = ((PlainSelect) selectStatement.getSelectBody()).getOffset();
            where = ((PlainSelect) selectStatement.getSelectBody()).getWhere();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        int startOfLimit = getLastStartPos(query, "limit");
        if (limit != null) {
            String suffix = Pattern.compile(limit.toString().trim(), Pattern.CASE_INSENSITIVE).matcher(query.substring(startOfLimit)).replaceFirst("");
            query = query.substring(0, startOfLimit) + suffix;
        } else if (startOfLimit != -1) {
            // offset returns null if it was placed before the limit although postgres does allow this
            // we are here if offset appears in the query and not within quotes
            query = query.substring(0, startOfLimit) + Pattern.compile("limit\\s+[\\d]+", Pattern.CASE_INSENSITIVE).matcher(query.substring(startOfLimit)).replaceFirst("");
        }
        int startOfOffset = getLastStartPos(query, "offset");
        if (offset != null) {
            String suffix = Pattern.compile(offset.toString().trim(), Pattern.CASE_INSENSITIVE).matcher(query.substring(startOfOffset)).replaceFirst("");
            query = query.substring(0, startOfOffset) + suffix;
        } else if (startOfOffset != -1) {
            // offset returns null if it was placed before the limit although postgres does allow this
            // we are here if offset appears in the query and not within quotes
            query = query.substring(0, startOfOffset) + Pattern.compile("offset\\s+[\\d]+", Pattern.CASE_INSENSITIVE).matcher(query.substring(startOfOffset)).replaceFirst("");
        }
        queryWithoutLimitOffset = query;
        // in the rare case where the order by clause somehow appears in the where clause
        int startOfOrderBy = getLastStartPos(query, "order by");
        if (orderBy != null) {
            StringBuilder sb = new StringBuilder("order by[ ]+");
            int size = orderBy.size();
            for (int i = 0; i < size; i++) {
                sb.append(orderBy.get(i).toString().replaceAll(" ", "[ ]+"));
                if (i < size - 1) {
                    sb.append(",?[ ]+");
                }
            }
            String regex = escape(sb.toString().trim());
            query = query.substring(0, startOfOrderBy) + Pattern.compile(regex, Pattern.CASE_INSENSITIVE).matcher(query.substring(startOfOrderBy)).replaceFirst("");
        } else if (startOfOrderBy != -1) {
            // offset returns null if it was placed before the limit although postgres does allow this
            // we are here if offset appears in the query and not within quotes
            query = query.substring(0, startOfOrderBy) + Pattern.compile("order by.*", Pattern.CASE_INSENSITIVE).matcher(query.substring(startOfOrderBy)).replaceFirst("");
        }
    } catch (Exception e) {
        log.error(e.getMessage(), e);
    }
    ParsedQuery pq = new ParsedQuery();
    pq.setCountFuncQuery(query);
    pq.setQueryWithoutLimOff(queryWithoutLimitOffset);
    if (where != null) {
        pq.setWhereClause(where.toString());
    }
    if (orderBy != null) {
        pq.setOrderByClause(orderBy.toString());
    }
    if (limit != null) {
        pq.setLimitClause(limit.toString());
    }
    if (offset != null) {
        pq.setOffsetClause(offset.toString());
    }
    long end = System.nanoTime();
    log.debug("Parse query for count_estimate function (ns) " + (end - start));
    return pq;
}

Example 2 with ParsedQuery

use of org.folio.rest.persist.facets.ParsedQuery in project raml-module-builder by folio-org.

the class PostgresClient method doGet.

private void doGet(SQLConnection connection, boolean transactionMode, String table, Class<?> clazz, String fieldName, String where, boolean returnCount, boolean returnIdField, boolean setId, List<FacetField> facets, Handler<AsyncResult<Results>> replyHandler) {
    long start = System.nanoTime();
    try {
        String addIdField = "";
        if (returnIdField) {
            addIdField = "," + idField;
        }
        String select = "SELECT ";
        if (!"null".equals(fieldName) && fieldName.contains("*")) {
            // if we are requesting all fields (*) , then dont add the id field to the select
            // this will return two id columns which will create ambiguity in facet queries
            addIdField = "";
        }
        String[] q = new String[] { select + fieldName + addIdField + " FROM " + convertToPsqlStandard(tenantId) + "." + table + " " + where };
        ParsedQuery parsedQuery = null;
        if (returnCount || (facets != null && !facets.isEmpty())) {
            parsedQuery = parseQuery(q[0]);
        }
        if (returnCount) {
            // optimize the entire query building process needed!!
            Map<String, String> replaceMapping = new HashMap<>();
            replaceMapping.put("tenantId", convertToPsqlStandard(tenantId));
            replaceMapping.put("query", org.apache.commons.lang.StringEscapeUtils.escapeSql(parsedQuery.getCountFuncQuery()));
            StrSubstitutor sub = new StrSubstitutor(replaceMapping);
            q[0] = select + sub.replace(countClauseTemplate) + q[0].replaceFirst(select, " ");
        }
        if (facets != null && !facets.isEmpty()) {
            q[0] = buildFacetQuery(table, parsedQuery, facets, returnCount, q[0]);
        }
        log.debug("query = " + q[0]);
        connection.query(q[0], query -> {
            if (!transactionMode) {
                connection.close();
            }
            try {
                if (query.failed()) {
                    log.error(query.cause().getMessage(), query.cause());
                    replyHandler.handle(Future.failedFuture(query.cause()));
                } else {
                    replyHandler.handle(Future.succeededFuture(processResult(query.result(), clazz, returnCount, setId)));
                }
                long end = System.nanoTime();
                StatsTracker.addStatElement(STATS_KEY + ".get", (end - start));
                if (log.isDebugEnabled()) {
                    log.debug("timer: get " + q[0] + " (ns) " + (end - start));
                }
            } catch (Exception e) {
                log.error(e.getMessage(), e);
                replyHandler.handle(Future.failedFuture(e));
            }
        });
    } catch (Exception e) {
        if (!transactionMode) {
            connection.close();
        }
        log.error(e.getMessage(), e);
        replyHandler.handle(Future.failedFuture(e));
    }
}

Example 3 with ParsedQuery

use of org.folio.rest.persist.facets.ParsedQuery in project raml-module-builder by folio-org.

the class PostgresClientTest method parseQuery.

@ParameterizedTest
@ValueSource(strings = { // "SELECT * FROM table WHERE items_mt_view.jsonb->>' ORDER BY items_mt_view.jsonb->>\\'aaa\\'  ORDER BY items2_mt_view.jsonb' ORDER BY items_mt_view.jsonb->>'aaa limit' OFFSET 31 limit 10",
"SELECT * FROM table WHERE items_mt_view.jsonb->>'title' LIKE '%12345%' ORDER BY items_mt_view.jsonb->>'title' DESC OFFSET 30 limit 10", "select jsonb,_id FROM counter_mod_inventory_storage.item  WHERE jsonb@>'{\"barcode\":4}' order by jsonb->'a'  asc, jsonb->'b' desc, jsonb->'c'", "select jsonb,_id FROM counter_mod_inventory_storage.item  WHERE jsonb @> '{\"barcode\":4}' limit 100 offset 0", // "SELECT * FROM table WHERE items0_mt_view.jsonb->>' ORDER BY items1_mt_view.jsonb->>''aaa'' ' ORDER BY items2_mt_view.jsonb->>' ORDER BY items3_mt_view.jsonb->>''aaa'' '",
"SELECT _id FROM test_tenant_mod_inventory_storage.material_type  WHERE jsonb@>'{\"id\":\"af6c5503-71e7-4b1f-9810-5c9f1af7c570\"}' LIMIT 1 OFFSET 0 ", "select * from diku999_circulation_storage.audit_loan WHERE audit_loan.jsonb->>'id' = 'cf23adf0-61ba-4887-bf82-956c4aae2260 order by created_date LIMIT 10 OFFSET 0' order by created_date LIMIT 10 OFFSET 0 ", "select * from slowtest99_mod_inventory_storage.item where (item.jsonb->'barcode') = to_jsonb('1000000'::int)  order by a LIMIT 30;", "SELECT  * FROM slowtest_cql5_mod_inventory_storage.item  WHERE lower(f_unaccent(item.jsonb->>'default')) LIKE lower(f_unaccent('true')) ORDER BY lower(f_unaccent(item.jsonb->>'code')) DESC, item.jsonb->>'code' DESC LIMIT 10 OFFSET 0" // "SELECT * FROM harvard_mod_configuration.config_data  WHERE ((true) AND ( (config_data.jsonb->>'userId' ~ '') IS NOT TRUE)) OR (lower(f_unaccent(config_data.jsonb->>'userId')) ~ lower(f_unaccent('(^|[[:punct:]]|[[:space:]])joeshmoe($|[[:punct:]]|[[:space:]])')))  ORDER BY lower(f_unaccent(item.jsonb->>'code')) DESC, item.jsonb->>'code' DESC LIMIT 10 OFFSET 0",
})
void parseQuery(String query) throws JSQLParserException {
    long start = System.nanoTime();
    List<String> facets = new ArrayList<>();
    facets.add("barcode");
    facets.add("materialTypeId");
    List<FacetField> facetList = FacetManager.convertFacetStrings2FacetFields(facets, "jsonb");
    FacetManager.setCalculateOnFirst(0);
    ParsedQuery pQ = PostgresClient.parseQuery(query);
    // buildFacetQuery("tablename", pQ, facetList, true, query);
    net.sf.jsqlparser.statement.Statement statement = CCJSqlParserUtil.parse(query);
    Select selectStatement = (Select) statement;
    List<OrderByElement> orderBy = ((PlainSelect) selectStatement.getSelectBody()).getOrderByElements();
    net.sf.jsqlparser.statement.select.Limit limit = ((PlainSelect) selectStatement.getSelectBody()).getLimit();
    net.sf.jsqlparser.statement.select.Offset offset = ((PlainSelect) selectStatement.getSelectBody()).getOffset();
    // in the rare case where the order by clause somehow appears in the where clause
    if (orderBy != null) {
        int startOfOrderBy = PostgresClient.getLastStartPos(query, "order by");
        StringBuilder sb = new StringBuilder("order by[ ]+");
        int size = orderBy.size();
        for (int i = 0; i < size; i++) {
            sb.append(orderBy.get(i).toString().replaceAll(" ", "[ ]+"));
            if (i < size - 1) {
                sb.append(",?[ ]+");
            }
        }
        String regex = sb.toString().trim();
        query = query.substring(0, startOfOrderBy) + Pattern.compile(regex, Pattern.CASE_INSENSITIVE).matcher(query.substring(startOfOrderBy)).replaceFirst("");
    }
    int startOfLimit = PostgresClient.getLastStartPos(query, "limit");
    if (limit != null) {
        query = query.substring(0, startOfLimit) + Pattern.compile(limit.toString().trim(), Pattern.CASE_INSENSITIVE).matcher(query.substring(startOfLimit)).replaceFirst("");
    } else if (startOfLimit != -1) {
        // offset returns null if it was placed before the limit although postgres does allow this
        // we are here if offset appears in the query and not within quotes
        query = query.substring(0, startOfLimit) + Pattern.compile("limit\\s+[\\d]+", Pattern.CASE_INSENSITIVE).matcher(query.substring(startOfLimit)).replaceFirst("");
    }
    if (offset != null) {
        int startOfOffset = PostgresClient.getLastStartPos(query, "offset");
        query = query.substring(0, startOfOffset) + Pattern.compile(offset.toString().trim(), Pattern.CASE_INSENSITIVE).matcher(query.substring(startOfOffset)).replaceFirst("");
    }
    long end = System.nanoTime();
    log.info(query + " from " + (end - start));
}