Examples with CQLSortNode org.z3950.zing.cql.CQLSortNode used on opensource projects

Example 1 with CQLSortNode

use of org.z3950.zing.cql.CQLSortNode in project okapi by folio-org.

the class CQLUtil method reducer.

public static CQLNode reducer(CQLNode vn1, CQLTermNode tn, Comparator<CQLTermNode> cmp) {
    if (vn1 instanceof CQLBooleanNode) {
        return reduceBoolean((CQLBooleanNode) vn1, tn, cmp);
    } else if (vn1 instanceof CQLTermNode) {
        CQLTermNode n1 = (CQLTermNode) vn1;
        if (cmp != null && cmp.compare(n1, tn) == 0) {
            return null;
        }
        return new CQLTermNode(n1.getIndex(), n1.getRelation(), n1.getTerm());
    } else if (vn1 instanceof CQLSortNode) {
        CQLSortNode n1 = (CQLSortNode) vn1;
        CQLNode n2 = reducer(n1.getSubtree(), tn, cmp);
        if (n2 == null) {
            return null;
        } else {
            CQLSortNode sn = new CQLSortNode(n2);
            List<ModifierSet> mods = n1.getSortIndexes();
            for (ModifierSet mSet : mods) {
                sn.addSortIndex(mSet);
            }
            return sn;
        }
    } else if (vn1 instanceof CQLPrefixNode) {
        CQLPrefixNode n1 = (CQLPrefixNode) vn1;
        CQLNode n2 = reducer(n1.getSubtree(), tn, cmp);
        if (n2 == null) {
            return null;
        } else {
            CQLPrefix prefix = n1.getPrefix();
            return new CQLPrefixNode(prefix.getName(), prefix.getIdentifier(), n2);
        }
    } else {
        throw new IllegalArgumentException("unknown type for CQLNode: " + vn1.toString());
    }
}

Example 2 with CQLSortNode

use of org.z3950.zing.cql.CQLSortNode in project raml-module-builder by folio-org.

the class CQL2PgJSON method toSql.

// suppress "reduce to one continue in for loop"
@SuppressWarnings("squid:S135")
private SqlSelect toSql(CQLSortNode node) throws QueryValidationException {
    StringBuilder order = new StringBuilder();
    String where = pg(node.getSubtree());
    boolean firstIndex = true;
    for (ModifierSet modifierSet : node.getSortIndexes()) {
        if (firstIndex) {
            firstIndex = false;
        } else {
            order.append(", ");
        }
        String desc = "";
        CqlModifiers modifiers = new CqlModifiers(modifierSet);
        if (modifiers.getCqlSort() == CqlSort.DESCENDING) {
            desc = " DESC";
        }
        // ASC not needed, it's Postgres' default
        String field = modifierSet.getBase();
        DbIndex dbIndex = dbIndexMap.computeIfAbsent(field, f -> DbSchemaUtils.getDbIndex(dbTable, f));
        if (dbIndex.isForeignKey() || "id".equals(field)) {
            order.append(field).append(desc);
            continue;
        }
        IndexTextAndJsonValues vals = getIndexTextAndJsonValues(field);
        // if sort field is marked explicitly as number type
        if (modifiers.getCqlTermFormat() == CqlTermFormat.NUMBER) {
            order.append(vals.getIndexJson()).append(desc);
            continue;
        }
        // We assume that a CREATE INDEX for this has been installed.
        order.append(wrapForLength(wrapInLowerUnaccent(vals.getIndexText(), modifiers))).append(desc).append(", ").append(wrapInLowerUnaccent(vals.getIndexText(), modifiers)).append(desc);
    }
    return new SqlSelect(where, order.toString());
}

Example 3 with CQLSortNode

use of org.z3950.zing.cql.CQLSortNode in project raml-module-builder by folio-org.

the class PgUtil method checkOptimizedCQL.

/**
 * Anticipate whether {@link #getWithOptimizedSql} will optimize for query
 * @param cql CQL query string
 * @param column sorting criteria to check for (eg "title")
 * @return null if not eligible; CQL sort node it would be optimized
 */
public static CQLSortNode checkOptimizedCQL(String cql, String column) {
    CQLSortNode cqlSortNode = getSortNode(cql);
    if (cqlSortNode == null) {
        return null;
    }
    List<ModifierSet> sortIndexes = cqlSortNode.getSortIndexes();
    if (sortIndexes.size() != 1) {
        return null;
    }
    ModifierSet modifierSet = sortIndexes.get(0);
    if (!modifierSet.getBase().equals(column)) {
        return null;
    }
    return cqlSortNode;
}

Example 4 with CQLSortNode

use of org.z3950.zing.cql.CQLSortNode in project raml-module-builder by folio-org.

the class PgUtil method getSortNode.

/**
 * Return the sort node from the sortBy clause of the cql query, or null if no
 * sortBy clause exists or cql is invalid.
 * @param cql  the CQL query to parse
 * @return sort node, or null
 */
static CQLSortNode getSortNode(String cql) {
    try {
        CQLParser parser = new CQLParser();
        CQLNode node = parser.parse(cql);
        return getSortNode(node);
    } catch (IOException | CQLParseException | NullPointerException e) {
        return null;
    }
}

Example 5 with CQLSortNode

use of org.z3950.zing.cql.CQLSortNode in project raml-module-builder by folio-org.

the class PgUtil method generateOptimizedSql.

/**
 * Generate optimized sql given a specific cql query, tenant, index column name hint and configurable size to hinge the optimization on.
 *
 * @param column the column that has an index to be used for sorting
 * @param preparedCql the cql query
 * @param offset start index of objects to return
 * @param limit max number of objects to return
 * @throws QueryValidationException
 * @return the generated SQL string, or null if the CQL query is not suitable for optimization.
 */
static String generateOptimizedSql(String column, PreparedCQL preparedCql, int offset, int limit) throws QueryValidationException {
    if (limit == 0) {
        return null;
    }
    String cql = preparedCql.getCqlWrapper().getQuery();
    CQLSortNode cqlSortNode = checkOptimizedCQL(cql, column);
    if (cqlSortNode == null) {
        return null;
    }
    List<ModifierSet> sortIndexes = cqlSortNode.getSortIndexes();
    ModifierSet modifierSet = sortIndexes.get(0);
    String ascDesc = getAscDesc(modifierSet);
    cql = cqlSortNode.getSubtree().toCQL();
    String lessGreater = "";
    if (ascDesc.equals("DESC")) {
        lessGreater = ">";
    } else {
        lessGreater = "<";
    }
    String tableName = preparedCql.getFullTableName();
    String where = preparedCql.getCqlWrapper().getField().toSql(cql).getWhere();
    // If there are many matches use a full table scan in data_column sort order
    // using the data_column index, but stop this scan after OPTIMIZED_SQL_SIZE index entries.
    // Otherwise use full text matching because there are only a few matches.
    // 
    // "headrecords" are the matching records found within the first OPTIMIZED_SQL_SIZE records
    // by stopping at the data_column from "OFFSET OPTIMIZED_SQL_SIZE LIMIT 1".
    // If "headrecords" are enough to return the requested "LIMIT" number of records we are done.
    // Otherwise use the full text index to create "allrecords" with all matching
    // records and do sorting and LIMIT afterwards.
    String wrappedColumn = "lower(f_unaccent(jsonb->>'" + column + "')) ";
    String cutWrappedColumn = "left(" + wrappedColumn + ",600) ";
    String innerSql = "SELECT " + wrappedColumn + " AS data_column " + "FROM " + tableName + " WHERE " + where;
    String countSql = preparedCql.getSchemaName() + ".count_estimate('" + innerSql.replace("'", "''") + "')";
    String sql = " WITH " + " headrecords AS (" + "   SELECT jsonb, (" + wrappedColumn + ") AS data_column FROM " + tableName + "   WHERE (" + where + ")" + "     AND " + cutWrappedColumn + lessGreater + "             ( SELECT " + cutWrappedColumn + "               FROM " + tableName + "               ORDER BY " + cutWrappedColumn + ascDesc + "               OFFSET " + optimizedSqlSize + " LIMIT 1" + "             )" + "   ORDER BY " + cutWrappedColumn + ascDesc + "   LIMIT " + limit + " OFFSET " + offset + " ), " + " allrecords AS (" + "   SELECT jsonb, " + wrappedColumn + " AS data_column FROM " + tableName + "   WHERE (" + where + ")" + "     AND (SELECT COUNT(*) FROM headrecords) < " + limit + " )," + " totalCount AS (SELECT " + countSql + " AS count)" + " SELECT jsonb, data_column, (SELECT count FROM totalCount)" + "   FROM headrecords" + "   WHERE (SELECT COUNT(*) FROM headrecords) >= " + limit + " UNION" + " (SELECT jsonb, data_column, (SELECT count FROM totalCount)" + "   FROM allrecords" + "   ORDER BY data_column " + ascDesc + "   LIMIT " + limit + " OFFSET " + offset + " )" + " ORDER BY data_column " + ascDesc;
    logger.info("optimized SQL generated from CQL: " + sql);
    return sql;
}