Examples with Operator org.molgenis.data.QueryRule.Operator used on opensource projects

Example 1 with Operator

use of org.molgenis.data.QueryRule.Operator in project molgenis by molgenis.

the class PostgreSqlQueryGenerator method getSqlWhere.

static <E extends Entity> String getSqlWhere(EntityType entityType, Query<E> q, List<Object> parameters, AtomicInteger mrefFilterIndex) {
    StringBuilder result = new StringBuilder();
    for (QueryRule r : q.getRules()) {
        Attribute attr = null;
        if (r.getField() != null) {
            attr = entityType.getAttribute(r.getField());
            if (attr == null) {
                throw new MolgenisDataException(format("Unknown attribute [%s]", r.getField()));
            }
            if (isPersistedInOtherTable(attr)) {
                mrefFilterIndex.incrementAndGet();
            }
        }
        StringBuilder predicate = new StringBuilder();
        Operator operator = r.getOperator();
        switch(operator) {
            case AND:
                result.append(" AND ");
                break;
            case NESTED:
                QueryImpl<Entity> nestedQ = new QueryImpl<>(r.getNestedRules());
                result.append('(').append(getSqlWhere(entityType, nestedQ, parameters, mrefFilterIndex)).append(')');
                break;
            case OR:
                result.append(" OR ");
                break;
            case LIKE:
                requireNonNull(attr, format(UNSPECIFIED_ATTRIBUTE_MSG, LIKE));
                String columnName;
                if (isPersistedInOtherTable(attr)) {
                    columnName = getFilterColumnName(attr, mrefFilterIndex.get());
                } else {
                    columnName = "this." + getColumnName(attr);
                }
                if (isStringType(attr) || isTextType(attr)) {
                    result.append(' ').append(columnName);
                } else {
                    result.append(" CAST(").append(columnName).append(" as TEXT)");
                }
                result.append(" LIKE ?");
                parameters.add("%" + PostgreSqlUtils.getPostgreSqlQueryValue(r.getValue(), attr) + '%');
                break;
            case IN:
                {
                    requireNonNull(attr, format(UNSPECIFIED_ATTRIBUTE_MSG, IN));
                    Object inValue = r.getValue();
                    if (inValue == null) {
                        throw new MolgenisDataException("Missing value for IN query");
                    }
                    if (!(inValue instanceof Iterable<?>)) {
                        throw new MolgenisDataException(format("IN value is of type [%s] instead of [Iterable]", inValue.getClass().getSimpleName()));
                    }
                    StringBuilder in = new StringBuilder();
                    Attribute inAttr = attr;
                    Stream<Object> postgreSqlIds = stream(((Iterable<?>) inValue).spliterator(), false).map(idValue -> PostgreSqlUtils.getPostgreSqlQueryValue(idValue, inAttr));
                    for (Iterator<Object> it = postgreSqlIds.iterator(); it.hasNext(); ) {
                        Object postgreSqlId = it.next();
                        in.append('?');
                        if (it.hasNext()) {
                            in.append(',');
                        }
                        parameters.add(postgreSqlId);
                    }
                    if (isPersistedInOtherTable(attr)) {
                        result.append(getFilterColumnName(attr, mrefFilterIndex.get()));
                    } else {
                        result.append("this");
                    }
                    Attribute equalsAttr;
                    if (attr.isMappedBy()) {
                        equalsAttr = attr.getRefEntity().getIdAttribute();
                    } else {
                        equalsAttr = entityType.getAttribute(r.getField());
                    }
                    result.append('.').append(getColumnName(equalsAttr));
                    result.append(" IN (").append(in).append(')');
                    break;
                }
            case NOT:
                result.append(" NOT ");
                break;
            case RANGE:
                requireNonNull(attr, format(UNSPECIFIED_ATTRIBUTE_MSG, RANGE));
                Object range = r.getValue();
                if (range == null) {
                    throw new MolgenisDataException("Missing value for RANGE query");
                }
                if (!(range instanceof Iterable<?>)) {
                    throw new MolgenisDataException(format("RANGE value is of type [%s] instead of [Iterable]", range.getClass().getSimpleName()));
                }
                Iterator<?> rangeValues = ((Iterable<?>) range).iterator();
                // from
                parameters.add(rangeValues.next());
                // to
                parameters.add(rangeValues.next());
                StringBuilder column = new StringBuilder();
                if (isPersistedInOtherTable(attr)) {
                    column.append(getFilterColumnName(attr, mrefFilterIndex.get()));
                } else {
                    column.append("this");
                }
                column.append('.').append(getColumnName(entityType.getAttribute(r.getField())));
                predicate.append(column).append(" >= ? AND ").append(column).append(" <= ?");
                result.append(predicate);
                break;
            case EQUALS:
                if (attr == null) {
                    throw new MolgenisDataException("Missing attribute field in EQUALS query rule");
                }
                if (isPersistedInOtherTable(attr)) {
                    predicate.append(getFilterColumnName(attr, mrefFilterIndex.get()));
                } else {
                    predicate.append("this");
                }
                Attribute equalsAttr;
                if (attr.isMappedBy()) {
                    equalsAttr = attr.getRefEntity().getIdAttribute();
                } else {
                    equalsAttr = entityType.getAttribute(r.getField());
                }
                predicate.append('.').append(getColumnName(equalsAttr));
                if (r.getValue() == null) {
                    // expression = null is not valid, use IS NULL
                    predicate.append(" IS NULL ");
                } else {
                    Object postgreSqlVal = PostgreSqlUtils.getPostgreSqlQueryValue(r.getValue(), attr);
                    // It does however return those rows when queried with "... NOT abstract IS TRUE"
                    if (attr.getDataType() == BOOL) {
                        Boolean bool = (Boolean) postgreSqlVal;
                        // noinspection ConstantConditions (getPostgreSqlQueryValue() != null if r.getValue() != null)
                        if (bool)
                            predicate.append(" IS TRUE");
                        else
                            predicate.append(" IS FALSE");
                    } else {
                        predicate.append(" =");
                        predicate.append(" ? ");
                        parameters.add(postgreSqlVal);
                    }
                }
                if (result.length() > 0 && !result.toString().endsWith(" OR ") && !result.toString().endsWith(" AND ") && !result.toString().endsWith(" NOT ")) {
                    result.append(" AND ");
                }
                result.append(predicate);
                break;
            case GREATER:
            case GREATER_EQUAL:
            case LESS:
            case LESS_EQUAL:
                requireNonNull(attr, format(UNSPECIFIED_ATTRIBUTE_MSG, format("%s, %s, %s or %s", GREATER, GREATER_EQUAL, LESS, LESS_EQUAL)));
                if (isPersistedInOtherTable(attr)) {
                    predicate.append(getFilterColumnName(attr, mrefFilterIndex.get()));
                } else {
                    predicate.append("this");
                }
                predicate.append('.').append(getColumnName(entityType.getAttribute(r.getField())));
                switch(operator) {
                    case GREATER:
                        predicate.append(" >");
                        break;
                    case GREATER_EQUAL:
                        predicate.append(" >=");
                        break;
                    case LESS:
                        predicate.append(" <");
                        break;
                    case LESS_EQUAL:
                        predicate.append(" <=");
                        break;
                    // $CASES-OMITTED$
                    default:
                        throw new RuntimeException(format("Unexpected query operator [%s]", operator));
                }
                predicate.append(" ? ");
                parameters.add(PostgreSqlUtils.getPostgreSqlQueryValue(r.getValue(), attr));
                if (result.length() > 0 && !result.toString().endsWith(" OR ") && !result.toString().endsWith(" AND ") && !result.toString().endsWith(" NOT ")) {
                    result.append(" AND ");
                }
                result.append(predicate);
                break;
            case DIS_MAX:
            case FUZZY_MATCH:
            case FUZZY_MATCH_NGRAM:
            case SEARCH:
            case SHOULD:
                // PostgreSQL does not support semantic searching and sorting matching rows on relevance.
                throw new UnsupportedOperationException(format("Query operator [%s] not supported by PostgreSQL repository", operator.toString()));
            default:
                throw new UnexpectedEnumException(operator);
        }
    }
    return result.toString().trim();
}

Example 2 with Operator

use of org.molgenis.data.QueryRule.Operator in project molgenis by molgenis.

the class QueryValidatorTest method validateValidProvider.

@DataProvider(name = "validateValidProvider")
public static Iterator<Object[]> validateValidProvider() {
    List<Object[]> queries = new ArrayList<>(256);
    EnumSet.of(EQUALS).forEach(operator -> {
        // BOOL
        Entity boolEntityType = createEntityType(BOOL);
        asList(Boolean.TRUE, Boolean.FALSE, null, "true", "false", "True", "False").forEach(value -> queries.add(new Object[] { boolEntityType, new QueryImpl<>().eq("attr", value) }));
        // CATEGORICAL, XREF, CATEGORICAL_MREF, MREF, ONE_TO_MANY
        EnumSet.of(STRING, INT, LONG, EMAIL, HYPERLINK).forEach(refIdAttrType -> EnumSet.of(CATEGORICAL, XREF, CATEGORICAL_MREF, MREF, ONE_TO_MANY).forEach(refAttrType -> {
            Entity refEntityType = createEntityType(refAttrType, refIdAttrType);
            asList("1", 1, 1L, null).forEach(idValue -> queries.add(new Object[] { refEntityType, new QueryImpl<>().eq("attr", idValue) }));
            Entity refEntity = when(mock(Entity.class).getIdValue()).thenReturn("1").getMock();
            queries.add(new Object[] { refEntityType, new QueryImpl<>().eq("attr", refEntity) });
        }));
        // DATE
        Entity dateEntityType = createEntityType(DATE);
        asList(LocalDate.now(), "2016-11-25", null).forEach(value -> queries.add(new Object[] { dateEntityType, new QueryImpl<>().eq("attr", value) }));
        // DATE_TIME
        Entity dateTimeEntityType = createEntityType(DATE_TIME);
        asList(Instant.now(), "1985-08-12T11:12:13+0500", null).forEach(value -> queries.add(new Object[] { dateTimeEntityType, new QueryImpl<>().eq("attr", value) }));
        // DECIMAL
        Entity decimalEntityType = createEntityType(DECIMAL);
        asList(1.23, "1.23", 1, 1L, null).forEach(value -> queries.add(new Object[] { decimalEntityType, new QueryImpl<>().eq("attr", value) }));
        // EMAIL, HTML, HYPERLINK, SCRIPT, STRING, TEXT
        EnumSet.of(EMAIL, HTML, HYPERLINK, SCRIPT, STRING, TEXT).forEach(attrType -> {
            Entity entityType = createEntityType(attrType);
            asList("abc", 1, 1L, 1.23, null).forEach(value -> queries.add(new Object[] { entityType, new QueryImpl<>().eq("attr", value) }));
        });
        // INT, LONG
        EnumSet.of(INT, LONG).forEach(attrType -> {
            Entity entityType = createEntityType(attrType);
            asList(1, 1L, "1", null).forEach(value -> queries.add(new Object[] { entityType, new QueryImpl<>().eq("attr", value) }));
        });
        // FILE
        Entity fileEntityType = createEntityType(FILE, STRING);
        asList("file0", mock(FileMeta.class), null).forEach(idValue -> queries.add(new Object[] { fileEntityType, new QueryImpl<>().eq("attr", idValue) }));
        // ENUM
        Entity enumEntityType = createEntityType(ENUM);
        asList(TestEnum.ENUM0, TestEnum.ENUM1, "ENUM0", "ENUM1", null).forEach(value -> queries.add(new Object[] { enumEntityType, new QueryImpl<>().eq("attr", value) }));
    });
    EnumSet.of(GREATER, GREATER_EQUAL, LESS, LESS_EQUAL).forEach(operator -> {
        Entity entityType = createEntityType(INT);
        QueryImpl<Entity> query = new QueryImpl<>();
        query.addRule(new QueryRule("attr", operator, 1));
        queries.add(new Object[] { entityType, query });
    });
    EnumSet.of(FUZZY_MATCH, FUZZY_MATCH_NGRAM, LIKE).forEach(operator -> {
        Entity entityType = createEntityType(STRING);
        QueryImpl<Entity> query = new QueryImpl<>();
        query.addRule(new QueryRule("attr", operator, "abc"));
        queries.add(new Object[] { entityType, query });
    });
    EnumSet.of(IN, RANGE).forEach(operator -> {
        Entity entityType = createEntityType(INT);
        QueryImpl<Entity> query = new QueryImpl<>();
        query.addRule(new QueryRule("attr", operator, asList(1, 2)));
        queries.add(new Object[] { entityType, query });
    });
    return queries.iterator();
}