Examples with Index org.folio.dbschema.Index used on opensource projects

Example 1 with Index

use of org.folio.dbschema.Index 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 Index

use of org.folio.dbschema.Index 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 3 with Index

use of org.folio.dbschema.Index in project raml-module-builder by folio-org.

the class IndexTest method indexExpressionDotsInPathTruncated.

@Test
void indexExpressionDotsInPathTruncated() {
    Index idx = index("testIdx", "dummy");
    idx.setMultiFieldNames("blah.blah2.field1,blah.blah2.field2");
    assertEquals("left(concat_space_sql(test_table.jsonb->'blah'->'blah2'->>'field1' , test_table.jsonb->'blah'->'blah2'->>'field2'),600)", idx.getFinalTruncatedSqlExpression("test_table"));
}

Example 4 with Index

use of org.folio.dbschema.Index in project raml-module-builder by folio-org.

the class IndexTest method sqlsetMultiFieldNamesWrap.

@Test
void sqlsetMultiFieldNamesWrap() {
    Index idx = index("testIdx", "testIdx");
    idx.setCaseSensitive(false);
    idx.setRemoveAccents(false);
    idx.setMultiFieldNames("test1,test2.test3");
    assertEquals("lower(concat_space_sql(test_table.jsonb->>'test1' , test_table.jsonb->'test2'->>'test3'))", idx.getFinalSqlExpression("test_table"));
}

Example 5 with Index

use of org.folio.dbschema.Index in project raml-module-builder by folio-org.

the class IndexTest method nullSQLExpression.

@Test
void nullSQLExpression() {
    Index idx = index("testIdx", "testIdx");
    idx.setSqlExpression(null);
    assertEquals("left(testIdx,600)", idx.getFinalTruncatedSqlExpression("test_table"));
}