Examples with BaseAssetQuery org.openremote.model.asset.BaseAssetQuery used on opensource projects

Example 1 with BaseAssetQuery

use of org.openremote.model.asset.BaseAssetQuery in project openremote by openremote.

the class AssetStorageService method buildWhereClause.

protected String buildWhereClause(BaseAssetQuery query, int level, List<ParameterBinder> binders) {
    // level = 1 is main query
    // level = 2 is union
    // level = 3 is CTE
    StringBuilder sb = new StringBuilder();
    boolean recursive = query.select.recursive;
    sb.append(" where true");
    if (level == 2) {
        return sb.toString();
    }
    if (level == 1 && query.id != null) {
        sb.append(" and A.ID = ?");
        final int pos = binders.size() + 1;
        binders.add(st -> st.setString(pos, query.id));
    }
    if (level == 1 && query.name != null) {
        sb.append(query.name.caseSensitive ? " and A.NAME " : " and upper(A.NAME)");
        switch(query.name.match) {
            case EXACT:
                sb.append(" = ? ");
                break;
            case NOT_EXACT:
                sb.append(" <> ? ");
                break;
            case BEGIN:
            case END:
            case CONTAINS:
                sb.append(" like ? ");
                break;
        }
        final int pos = binders.size() + 1;
        binders.add(st -> st.setString(pos, query.name.prepareValue()));
    }
    if (level == 1 && query.location != null) {
        if (query.location instanceof RadialLocationPredicate) {
            RadialLocationPredicate location = (RadialLocationPredicate) query.location;
            sb.append(" and ST_Distance_Sphere(A.LOCATION, ST_MakePoint(");
            sb.append(location.lng);
            sb.append(",");
            sb.append(location.lat);
            sb.append(location.negated ? ")) > " : ")) <= ");
            sb.append(location.radius);
        } else if (query.location instanceof RectangularLocationPredicate) {
            RectangularLocationPredicate location = (RectangularLocationPredicate) query.location;
            sb.append(location.negated ? " and NOT" : " and");
            sb.append(" ST_Within(A.LOCATION,");
            sb.append("ST_MakeEnvelope(");
            sb.append(location.lngMin);
            sb.append(",");
            sb.append(location.latMin);
            sb.append(",");
            sb.append(location.lngMax);
            sb.append(",");
            sb.append(location.latMax);
            sb.append("))");
        }
    }
    if (query.parent != null) {
        // Can only restrict recursive query parent by asset type
        if (level == 1 && query.parent.id != null) {
            sb.append(" and p.ID = a.PARENT_ID");
            sb.append(" and A.PARENT_ID = ?");
            final int pos = binders.size() + 1;
            binders.add(st -> st.setString(pos, query.parent.id));
        } else if (query.parent.type != null) {
            sb.append(" and p.ID = a.PARENT_ID");
            sb.append(" and P.ASSET_TYPE = ?");
            final int pos = binders.size() + 1;
            binders.add(st -> st.setString(pos, query.parent.type));
        } else if (level == 1 && query.parent.noParent) {
            sb.append(" and A.PARENT_ID is null");
        }
    }
    if (level == 1 && query.path != null && query.path.hasPath()) {
        sb.append(" and ? <@ get_asset_tree_path(A.ID)");
        final int pos = binders.size() + 1;
        binders.add(st -> st.setArray(pos, st.getConnection().createArrayOf("text", query.path.path)));
    }
    if (!recursive || level == 3) {
        if (query.tenant != null && query.tenant.realmId != null) {
            sb.append(" and R.ID = ?");
            final int pos = binders.size() + 1;
            binders.add(st -> st.setString(pos, query.tenant.realmId));
        } else if (query.tenant != null && query.tenant.realm != null) {
            sb.append(" and R.NAME = ?");
            final int pos = binders.size() + 1;
            binders.add(st -> st.setString(pos, query.tenant.realm));
        }
        if (query.userId != null) {
            sb.append(" and ua.ASSET_ID = a.ID and ua.USER_ID = ?");
            final int pos = binders.size() + 1;
            binders.add(st -> st.setString(pos, query.userId));
        }
        if (level == 1 && query.select.access == Access.PUBLIC_READ) {
            sb.append(" and A.ACCESS_PUBLIC_READ is true");
        }
        if (query.type != null) {
            sb.append(query.type.caseSensitive ? " and A.ASSET_TYPE" : " and upper(A.ASSET_TYPE)");
            switch(query.type.match) {
                case EXACT:
                    sb.append(" = ? ");
                    break;
                case NOT_EXACT:
                    sb.append(" <> ? ");
                    break;
                case BEGIN:
                case END:
                case CONTAINS:
                    sb.append(" like ? ");
                    break;
            }
            final int pos = binders.size() + 1;
            binders.add(st -> st.setString(pos, query.type.prepareValue()));
        }
        if (query.attributeMeta != null) {
            for (AttributeMetaPredicate attributeMetaPredicate : query.attributeMeta) {
                String attributeMetaFilter = buildAttributeMetaFilter(attributeMetaPredicate, binders);
                if (attributeMetaFilter.length() > 0) {
                    sb.append(" and A.ID in (select A.ID from");
                    sb.append(" jsonb_each(A.ATTRIBUTES) as AX,");
                    sb.append(" jsonb_array_elements(AX.VALUE #> '{meta}') as AM");
                    sb.append(" where true");
                    sb.append(attributeMetaFilter);
                    sb.append(")");
                }
            }
        }
        if (query.attribute != null) {
            for (AssetQuery.AttributePredicate attributePredicate : query.attribute) {
                StringBuilder attributeFilterBuilder = new StringBuilder();
                attributeFilterBuilder.append(buildAttributeFilter(attributePredicate, binders));
                if (attributeFilterBuilder.length() > 0) {
                    sb.append(" and A.ID in (select A.ID from");
                    sb.append(" jsonb_each(A.ATTRIBUTES) as AX");
                    sb.append(" where true");
                    sb.append(attributeFilterBuilder.toString());
                    sb.append(")");
                }
            }
        }
    }
    return sb.toString();
}