Examples with IBaseSQLCondition com.servoy.base.query.IBaseSQLCondition used on opensource projects

Example 1 with IBaseSQLCondition

use of com.servoy.base.query.IBaseSQLCondition in project servoy-client by Servoy.

the class SQLGenerator method getEmptyDataSetForDummyQuery.

/**
 * check if the query is will never return any rows, in that case just return an empty dataset.
 */
public static IDataSet getEmptyDataSetForDummyQuery(ISQLSelect sqlSelect) {
    if (sqlSelect instanceof QuerySelect && ((QuerySelect) sqlSelect).getCondition(CONDITION_SEARCH) != null) {
        // all named conditions in QuerySelecta are AND-ed, if one always results to false, skip the query
        for (IBaseSQLCondition condition : ((QuerySelect) sqlSelect).getCondition(CONDITION_SEARCH).getConditions()) {
            boolean skipQuery = false;
            if (condition instanceof SetCondition && ((SetCondition) condition).isAndCondition()) {
                // check for EQUALS_OPERATOR
                int ncols = ((SetCondition) condition).getKeys().length;
                int[] operators = ((SetCondition) condition).getOperators();
                boolean eqop = true;
                for (int i = 0; i < ncols; i++) {
                    if (operators[i] != IBaseSQLCondition.EQUALS_OPERATOR) {
                        eqop = false;
                    }
                }
                if (eqop) {
                    Object value = ((SetCondition) condition).getValues();
                    if (value instanceof Placeholder) {
                        Object phval = ((Placeholder) value).getValue();
                        // cleared foundset
                        skipQuery = phval instanceof DynamicPkValuesArray && ((DynamicPkValuesArray) phval).getPKs().getRowCount() == 0;
                    } else if (value instanceof Object[][]) {
                        skipQuery = ((Object[][]) value).length == 0 || ((Object[][]) value)[0].length == 0;
                    }
                }
            }
            if (skipQuery) {
                // no need to query, dummy condition (where 1=2) here
                List<IQuerySelectValue> columns = ((QuerySelect) sqlSelect).getColumns();
                String[] columnNames = new String[columns.size()];
                ColumnType[] columnTypes = new ColumnType[columns.size()];
                for (int i = 0; i < columns.size(); i++) {
                    IQuerySelectValue col = columns.get(i);
                    columnNames[i] = col.getAliasOrName();
                    BaseColumnType columnType = col.getColumnType();
                    columnTypes[i] = columnType == null ? ColumnType.getInstance(Types.OTHER, 0, 0) : ColumnType.getInstance(columnType.getSqlType(), columnType.getLength(), columnType.getScale());
                }
                return BufferedDataSetInternal.createBufferedDataSet(columnNames, columnTypes, new SafeArrayList<Object[]>(0), false);
            }
        }
    }
    // query needs to be run
    return null;
}

Example 2 with IBaseSQLCondition

use of com.servoy.base.query.IBaseSQLCondition in project servoy-client by Servoy.

the class BaseSQLGenerator method parseFindExpression.

public static IBaseSQLCondition parseFindExpression(IQueryFactory queryFactory, Object raw, IBaseQuerySelectValue qCol, BaseQueryTable columnTable, int dataProviderType, String formatString, IBaseColumn c, boolean addNullPkNotNullCondition, IValueConverter valueConverter, ITypeConverter typeConverter, BaseColumn firstForeignPKColumn, ILogger logger) {
    IBaseSQLCondition or = null;
    // filter on the || (=or)
    // $NON-NLS-1$
    String[] rawElements = raw instanceof String[] ? (String[]) raw : raw.toString().split("\\|\\|");
    for (String element : rawElements) {
        String data = element;
        if (!(c instanceof BaseColumn) || ((BaseColumn) c).getType() != Types.CHAR) {
            // if char, it fills up with spaces, so don't trim
            data = data.trim();
        }
        if (// filter out the zero length strings
        data.length() == 0) {
            continue;
        }
        try {
            // find the format (only applicable for date columns)
            if (dataProviderType == IColumnTypeConstants.DATETIME) {
                int pipe_index = data.indexOf('|');
                if (// the format is speced from within javascript '1-1-2003...30-1-2003|dd-MM-yyyy'
                pipe_index != -1) {
                    formatString = data.substring(pipe_index + 1);
                    data = data.substring(0, pipe_index);
                }
            }
            // find the operators and the modifiers
            boolean isNot = false;
            boolean hash = false;
            int nullCheck = NULLCHECK_NONE;
            int operator = IBaseSQLCondition.EQUALS_OPERATOR;
            // for between
            String data2 = null;
            boolean parsing = true;
            while (parsing && data.length() > 0) {
                char first = data.charAt(0);
                switch(first) {
                    case // ! negation
                    '!':
                        if (// $NON-NLS-1$
                        data.startsWith("!!")) {
                            parsing = false;
                        } else {
                            isNot = true;
                        }
                        data = data.substring(1);
                        break;
                    case // # case insensitive (Text) or day search (Date)
                    '#':
                        if (// $NON-NLS-1$
                        data.startsWith("##")) {
                            parsing = false;
                        } else {
                            hash = true;
                        }
                        data = data.substring(1);
                        break;
                    case // ^ or ^= nullchecks
                    '^':
                        if (// $NON-NLS-1$
                        data.startsWith("^^")) {
                            data = data.substring(1);
                        } else {
                            if (// $NON-NLS-1$
                            data.startsWith("^=")) {
                                nullCheck = NULLCHECK_NULL_EMPTY;
                            } else {
                                nullCheck = NULLCHECK_NULL;
                            }
                        }
                        parsing = false;
                        break;
                    default:
                        // unary operators
                        if (// $NON-NLS-1$ //$NON-NLS-2$
                        data.startsWith("<=") || data.startsWith("=<")) {
                            operator = IBaseSQLCondition.LTE_OPERATOR;
                            data = data.substring(2);
                        } else if (// $NON-NLS-1$ //$NON-NLS-2$
                        data.startsWith(">=") || data.startsWith("=>")) {
                            operator = IBaseSQLCondition.GTE_OPERATOR;
                            data = data.substring(2);
                        } else if (// $NON-NLS-1$
                        data.startsWith("==")) {
                            operator = IBaseSQLCondition.EQUALS_OPERATOR;
                            data = data.substring(2);
                        } else if (// $NON-NLS-1$
                        data.startsWith("<")) {
                            operator = IBaseSQLCondition.LT_OPERATOR;
                            data = data.substring(1);
                        } else if (// $NON-NLS-1$
                        data.startsWith(">")) {
                            operator = IBaseSQLCondition.GT_OPERATOR;
                            data = data.substring(1);
                        } else if (// $NON-NLS-1$
                        data.startsWith("=")) {
                            operator = IBaseSQLCondition.EQUALS_OPERATOR;
                            data = data.substring(1);
                        } else {
                            // between ?
                            // $NON-NLS-1$
                            int index = data.indexOf("...");
                            if (index != -1) {
                                data2 = data.substring(index + 3);
                                data = data.substring(0, index);
                                operator = IBaseSQLCondition.BETWEEN_OPERATOR;
                            }
                            // regular data
                            parsing = false;
                        }
                }
            }
            IBaseSQLCondition condition = null;
            if (nullCheck != NULLCHECK_NONE) {
                // nullchecks
                IBaseSQLCondition compareEmpty = null;
                if (nullCheck == NULLCHECK_NULL_EMPTY) {
                    switch(dataProviderType) {
                        case IColumnTypeConstants.INTEGER:
                        case IColumnTypeConstants.NUMBER:
                            compareEmpty = queryFactory.createCompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, qCol, Integer.valueOf(0));
                            break;
                        case IColumnTypeConstants.TEXT:
                            // $NON-NLS-1$
                            compareEmpty = queryFactory.createCompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, qCol, "");
                            break;
                    }
                }
                condition = queryFactory.or(compareEmpty, queryFactory.createCompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, qCol, null));
            } else if (data.length() > 0) {
                // get the operators
                Object value = null;
                // for between
                Object value2 = null;
                int modifier = 0;
                switch(dataProviderType) {
                    case IColumnTypeConstants.INTEGER:
                    case IColumnTypeConstants.NUMBER:
                        Object initialObj = (raw instanceof String || raw instanceof String[]) ? data : raw;
                        Object objRightType = typeConverter.getAsRightType(dataProviderType, c.getFlags(), initialObj, formatString, c.getLength(), false);
                        // Because if it is already a Number then it shouldn't be converted to String and then back
                        if (initialObj != null && objRightType == null) {
                            // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
                            logger.log("Cannot convert (" + initialObj.getClass() + ") " + initialObj + " to a number/int.");
                            value = null;
                        } else {
                            value = objRightType;
                        }
                        // parse data2 (between)
                        if (data2 != null) {
                            value2 = typeConverter.getAsRightType(dataProviderType, c.getFlags(), data2, formatString, c.getLength(), false);
                            if (value2 == null) {
                                // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
                                logger.log("Cannot convert (" + data2.getClass() + ") " + data2 + " to a number/int.");
                            }
                        }
                        break;
                    case IColumnTypeConstants.DATETIME:
                        // special date parsing
                        boolean dateSearch = hash;
                        Date date;
                        Date tmp = null;
                        if (// $NON-NLS-1$
                        data.equalsIgnoreCase("now")) {
                            date = (Date) typeConverter.getAsRightType(dataProviderType, c.getFlags(), tmp = new Date(), c.getLength(), false);
                        } else if (// $NON-NLS-1$ //$NON-NLS-2$
                        data.startsWith("//") || data.equalsIgnoreCase("today")) {
                            date = (Date) typeConverter.getAsRightType(dataProviderType, c.getFlags(), tmp = new Date(), c.getLength(), false);
                            dateSearch = true;
                        } else {
                            // Now get asRightType with RAW and not with the string.
                            // Because if it is already a Date then it shouldn't be converted to String and then back
                            Object initialObj1 = ((raw instanceof String || raw instanceof String[]) ? data : raw);
                            Object tst = typeConverter.getAsRightType(dataProviderType, c.getFlags(), initialObj1, formatString, c.getLength(), false);
                            if (tst == null && initialObj1 != null) {
                                // Format failed.. Reporting that to the user
                                // $NON-NLS-1$ //$NON-NLS-2$
                                logger.log("Cannot parse " + initialObj1 + " using format " + formatString + '.');
                                date = null;
                            } else {
                                date = (Date) tst;
                            }
                        }
                        if (dateSearch && date != null) {
                            if (operator == IBaseSQLCondition.EQUALS_OPERATOR) {
                                value = getStartOfDay(date, c, typeConverter);
                                value2 = getEndOfDay(date, c, typeConverter);
                                operator = IBaseSQLCondition.BETWEEN_OPERATOR;
                            } else if (operator == IBaseSQLCondition.BETWEEN_OPERATOR || operator == IBaseSQLCondition.LT_OPERATOR || operator == IBaseSQLCondition.GTE_OPERATOR) {
                                value = getStartOfDay(date, c, typeConverter);
                            } else {
                                value = getEndOfDay(date, c, typeConverter);
                            }
                        } else {
                            value = date;
                        }
                        // parse data2 (between)
                        if (data2 != null) {
                            dateSearch = hash;
                            if (// $NON-NLS-1$
                            data2.equalsIgnoreCase("now")) {
                                date = (Date) typeConverter.getAsRightType(dataProviderType, c.getFlags(), (tmp != null ? tmp : new Date()), c.getLength(), false);
                            } else if (// $NON-NLS-1$ //$NON-NLS-2$
                            data2.startsWith("//") || data2.equalsIgnoreCase("today")) {
                                date = (Date) typeConverter.getAsRightType(dataProviderType, c.getFlags(), (tmp != null ? tmp : new Date()), c.getLength(), false);
                                dateSearch = true;
                            } else {
                                Object dt = typeConverter.getAsRightType(dataProviderType, c.getFlags(), data2, formatString, c.getLength(), false);
                                if (dt instanceof Date) {
                                    date = (Date) dt;
                                } else {
                                    // $NON-NLS-1$ //$NON-NLS-2$
                                    logger.log("Cannot parse '" + data2 + "' using format " + formatString + '.');
                                    date = null;
                                }
                            }
                            if (dateSearch && date != null) {
                                value2 = getEndOfDay(date, c, typeConverter);
                            } else {
                                value2 = date;
                            }
                        }
                        break;
                    case IColumnTypeConstants.TEXT:
                        if (hash) {
                            modifier |= IBaseSQLCondition.CASEINSENTITIVE_MODIFIER;
                        }
                        if (operator == IBaseSQLCondition.EQUALS_OPERATOR) {
                            // count the amount of percents based upon the amount we decide what to do
                            char[] chars = data.toCharArray();
                            StringBuilder dataBuf = new StringBuilder();
                            boolean escapeNext = false;
                            for (char d : chars) {
                                if (!escapeNext && d == '\\') {
                                    escapeNext = true;
                                } else {
                                    if (!escapeNext && d == '%') {
                                        // found a like operator, use backslash as escape in like,
                                        // use unmodified value, db will use escape backslash from like expression
                                        operator = IBaseSQLCondition.LIKE_OPERATOR;
                                        if (data.indexOf('\\') >= 0) {
                                            // escape char, put escape in sql when seen in string  //$NON-NLS-1$
                                            value2 = "\\";
                                        }
                                        break;
                                    }
                                    dataBuf.append(d);
                                    escapeNext = false;
                                }
                            }
                            if (operator == IBaseSQLCondition.EQUALS_OPERATOR) {
                                data = dataBuf.toString();
                            }
                        // else escape in db will handle escape. use original data
                        } else {
                            value2 = data2;
                        }
                        value = data;
                        break;
                    default:
                        operator = IBaseSQLCondition.LIKE_OPERATOR;
                        // +2 for %...%
                        value = typeConverter.getAsRightType(dataProviderType, c.getFlags(), data, formatString, c.getLength() + 2, false);
                }
                // create the condition
                if (value != null) {
                    Object operand;
                    // for like, value2 may be the escape character
                    if (value2 != null && operator == IBaseSQLCondition.BETWEEN_OPERATOR) {
                        operand = new Object[] { typeConverter.getAsRightType(c.getDataProviderType(), c.getFlags(), valueConverter == null ? value : valueConverter.convertFromObject(value), null, c.getLength(), false), typeConverter.getAsRightType(c.getDataProviderType(), c.getFlags(), valueConverter == null ? value2 : valueConverter.convertFromObject(value2), null, c.getLength(), false) };
                    } else if (operator == IBaseSQLCondition.LIKE_OPERATOR) {
                        operand = value2 == null ? value : new Object[] { value, value2 };
                    } else {
                        operand = typeConverter.getAsRightType(c.getDataProviderType(), c.getFlags(), valueConverter == null ? value : valueConverter.convertFromObject(value), null, c.getLength(), false);
                    }
                    condition = queryFactory.createCompareCondition(operator | modifier, qCol, operand);
                }
            }
            if (condition != null) {
                if (isNot) {
                    condition = condition.negate();
                } else {
                    // Skip this if the search is on the related pk column, the user explicitly wants to find records that have no related record (left outer join)
                    if (addNullPkNotNullCondition && nullCheck != NULLCHECK_NONE && (c.getFlags() & IBaseColumn.IDENT_COLUMNS) == 0) {
                        // in case of composite pk, checking only the first pk column is enough
                        condition = queryFactory.and(condition, queryFactory.createCompareCondition(IBaseSQLCondition.NOT_OPERATOR, queryFactory.createQueryColumn(columnTable, firstForeignPKColumn.getID(), firstForeignPKColumn.getSQLName(), firstForeignPKColumn.getType(), firstForeignPKColumn.getLength(), firstForeignPKColumn.getScale(), firstForeignPKColumn.getFlags()), null));
                    }
                }
                or = queryFactory.or(or, condition);
            }
        } catch (Exception ex) {
            logger.error("Error in parsing find expression '" + element + "'", ex);
        }
    }
    return or;
}