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

Example 1 with SqlSelect

use of org.folio.cql2pgjson.model.SqlSelect 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 2 with SqlSelect

use of org.folio.cql2pgjson.model.SqlSelect 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);
}

Example 3 with SqlSelect

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

the class CQL2PgJSONTest method toSql.

@Test
public void toSql() throws QueryValidationException {
    SqlSelect s = cql2pgJson.toSql("email=Long sortBy name/sort.descending");
    assertThat(s.getWhere(), allOf(containsString("get_tsvector"), containsString("users.user_data->>'email'")));
    assertEquals("left(lower(f_unaccent(users.user_data->>'name')),600) DESC, lower(f_unaccent(users.user_data->>'name')) DESC", s.getOrderBy());
    String sql = s.toString();
    assertTrue(sql.startsWith("WHERE get_tsvector("));
    assertTrue(sql.endsWith(" ORDER BY " + "left(lower(f_unaccent(users.user_data->>'name')),600) DESC, lower(f_unaccent(users.user_data->>'name')) DESC"));
}

Example 4 with SqlSelect

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

the class CQL2PgJSONTest method toSqlSimple.

@Test
public void toSqlSimple() throws QueryValidationException {
    SqlSelect s = cql2pgJson.toSql("cql.allRecords=1");
    assertEquals("true", s.getWhere());
    assertEquals("", s.getOrderBy());
    assertEquals("WHERE true", s.toString());
}

Example 5 with SqlSelect

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

the class CQL2PGCLIMain method parseCQL.

protected static String parseCQL(CQL2PgJSON cql2pgJson, String dbName, String cql) throws QueryValidationException {
    SqlSelect sql = cql2pgJson.toSql(cql);
    String orderby = sql.getOrderBy();
    logger.log(Level.FINE, () -> String.format("orderby for cql query '%s' is '%s'", cql, orderby));
    if (StringUtils.isBlank(orderby)) {
        return String.format("select * from %s where %s", dbName, sql.getWhere());
    }
    return String.format("select * from %s where %s order by %s", dbName, sql.getWhere(), orderby);
}