Examples with DbIndex org.folio.cql2pgjson.model.DbIndex used on opensource projects

Example 1 with DbIndex

use of org.folio.cql2pgjson.model.DbIndex in project raml-module-builder by folio-org.

the class CQL2PgJSON method queryBySql.

/**
 * Create an SQL expression using SQL as is syntax.
 *
 * @param hasIndex
 * @param vals
 * @param node
 * @param comparator
 * @param modifiers
 * @return
 */
private String queryBySql(DbIndex dbIndex, IndexTextAndJsonValues vals, CQLTermNode node, String comparator, CqlModifiers modifiers) {
    String indexMod = vals.getIndexText();
    if (comparator.equals("==")) {
        comparator = "=";
    }
    Index schemaIndex = dbIndex.getIndex();
    String sql;
    String term = "'" + Cql2SqlUtil.cql2like(node.getTerm()) + "'";
    if (CqlTermFormat.NUMBER.equals(modifiers.getCqlTermFormat())) {
        sql = "(" + indexMod + ")::numeric " + comparator + term;
    } else if (schemaIndex != null) {
        sql = createSQLLengthCase(comparator, indexMod, term, schemaIndex);
    } else {
        sql = indexMod + " " + comparator + term;
    }
    if (!dbIndex.hasIndex() && !dbIndex.hasFullTextIndex() && !dbIndex.hasLikeIndex()) {
        String s = String.format("%s, CQL >>> SQL: %s >>> %s", indexMod, node.toCQL(), sql);
        logger.log(Level.WARNING, "Doing SQL query without index for {0}", s);
    }
    logger.log(Level.FINE, "index {0} generated SQL {1}", new Object[] { indexMod, sql });
    return sql;
}

Example 2 with DbIndex

use of org.folio.cql2pgjson.model.DbIndex 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 DbIndex

use of org.folio.cql2pgjson.model.DbIndex in project raml-module-builder by folio-org.

the class CQL2PgJSON method queryByLike.

/**
 * Create an SQL expression using LIKE query syntax.
 *
 * @param hasIndex
 * @param vals
 * @param node
 * @param comparator
 * @param modifiers
 * @return
 */
private String queryByLike(String index, DbIndex dbIndex, IndexTextAndJsonValues vals, CQLTermNode node, String comparator, CqlModifiers modifiers, Table targetTable) throws QueryValidationException {
    final String indexText = vals.getIndexText();
    final Index schemaIndex = ObjectUtils.firstNonNull(dbIndex.getGinIndex(), dbIndex.getLikeIndex(), dbIndex.getUniqueIndex(), dbIndex.getIndex());
    String sql = null;
    List<Modifier> relationModifiers = modifiers.getRelationModifiers();
    if (!relationModifiers.isEmpty()) {
        sql = arrayNode(index, node, modifiers, relationModifiers, schemaIndex, vals, targetTable);
    } else {
        String likeOperator = comparator.equals("<>") ? "NOT LIKE" : "LIKE";
        String term = "'" + Cql2SqlUtil.cql2like(node.getTerm()) + "'";
        String indexMod;
        if (schemaIndex != null && schemaIndex.getMultiFieldNames() != null) {
            indexMod = schemaIndex.getFinalSqlExpression(targetTable.getTableName());
        } else if (schemaIndex != null && schemaIndex.getSqlExpression() != null) {
            indexMod = schemaIndex.getSqlExpression();
        } else {
            indexMod = wrapIndexExpression(indexText, schemaIndex);
        }
        if (schemaIndex != null && schemaIndex == dbIndex.getIndex()) {
            sql = createLikeLengthCase(comparator, indexMod, schemaIndex, likeOperator, term);
        } else {
            sql = indexMod + " " + likeOperator + " " + wrapQueryExpression(term, schemaIndex);
        }
    }
    if (Cql2SqlUtil.hasCqlWildCard(node.getTerm())) {
        // FIXME: right truncation "abc*" works with index/uniqueIndex
        if (!dbIndex.hasGinIndex() && !dbIndex.hasLikeIndex()) {
            String s = String.format("%s, CQL >>> SQL: %s >>> %s", indexText, node.toCQL(), sql);
            logger.log(Level.WARNING, "Doing wildcard LIKE search without index for {0}", s);
        }
    } else {
        if (schemaIndex == null) {
            String s = String.format("%s, CQL >>> SQL: %s >>> %s", indexText, node.toCQL(), sql);
            logger.log(Level.WARNING, "Doing LIKE search without index for {0}", s);
        }
    }
    logger.log(Level.FINE, "index {0} generated SQL {1}", new Object[] { indexText, sql });
    return sql;
}

Example 4 with DbIndex

use of org.folio.cql2pgjson.model.DbIndex in project raml-module-builder by folio-org.

the class DbSchemaUtilsTest method testGetDbIndexForNullTable.

@Test
public void testGetDbIndexForNullTable() {
    DbIndex dbIndex = DbSchemaUtils.getDbIndex(null, "address");
    assertFalse(dbIndex.hasFullTextIndex());
    assertFalse(dbIndex.hasGinIndex());
    assertFalse(dbIndex.hasIndex());
    assertFalse(dbIndex.hasUniqueIndex());
    assertFalse(dbIndex.hasLikeIndex());
}

Example 5 with DbIndex

use of org.folio.cql2pgjson.model.DbIndex in project raml-module-builder by folio-org.

the class DbSchemaUtilsTest method testGetDbIndexForAddress.

@Test
public void testGetDbIndexForAddress() {
    DbIndex dbIndex = DbSchemaUtils.getDbIndex(table, "address");
    assertFalse(dbIndex.hasFullTextIndex());
    assertFalse(dbIndex.hasGinIndex());
    assertFalse(dbIndex.hasIndex());
    assertFalse(dbIndex.hasUniqueIndex());
    assertTrue(dbIndex.hasLikeIndex());
}