Search in sources :

Example 1 with BaseColumn

use of com.servoy.base.persistence.BaseColumn 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;
}
Also used : IBaseSQLCondition(com.servoy.base.query.IBaseSQLCondition) BaseColumn(com.servoy.base.persistence.BaseColumn) IBaseColumn(com.servoy.base.persistence.IBaseColumn) Date(java.util.Date)

Aggregations

BaseColumn (com.servoy.base.persistence.BaseColumn)1 IBaseColumn (com.servoy.base.persistence.IBaseColumn)1 IBaseSQLCondition (com.servoy.base.query.IBaseSQLCondition)1 Date (java.util.Date)1