Examples with IntStringPair com.serotonin.db.pair.IntStringPair used on opensource projects

Example 16 with IntStringPair

use of com.serotonin.db.pair.IntStringPair in project ma-core-public by infiniteautomation.

the class AbstractDao method applyConditions.

/**
 * TODO This needs to be reworked to use the args list
 * to avoid SQL Injection Attacks
 * @param sql
 * @param args
 * @param query
 * @param or
 * @return
 */
protected String applyConditions(String sql, List<Object> args, Map<String, String> query, boolean or) {
    if (query != null && !query.isEmpty()) {
        int i = 0;
        Set<String> properties = this.propertyTypeMap.keySet();
        for (String prop : query.keySet()) {
            boolean mapped = false;
            String dbProp = prop;
            // this will be done after the query
            if (!filterMap.containsKey(prop)) {
                if (propertiesMap.containsKey(prop)) {
                    IntStringPair pair = propertiesMap.get(prop);
                    dbProp = pair.getValue();
                    mapped = true;
                }
                if (mapped || properties.contains(prop)) {
                    String tempSql = (i == 0) ? WHERE : (or ? OR : AND);
                    String condition = query.get(prop);
                    if (condition.startsWith("RegExp:")) {
                        condition = condition.substring(7, condition.length());
                        // simple RegExp handling
                        if (condition.startsWith("^") && condition.endsWith("$")) {
                            condition = condition.substring(1, condition.length() - 1);
                            condition = condition.replace(".*.*", "%");
                            condition = condition.replace(".*", "%");
                            // Derby doesn't handle LIKE for anything but varchars
                            switch(Common.databaseProxy.getType()) {
                                case MYSQL:
                                case POSTGRES:
                                case MSSQL:
                                case H2:
                                    if (mapped)
                                        tempSql += "lower(" + dbProp + ") LIKE '" + condition.toLowerCase() + "'";
                                    else
                                        tempSql += "lower(" + this.tablePrefix + dbProp + ") LIKE '" + condition.toLowerCase() + "'";
                                    break;
                                case DERBY:
                                    if (mapped)
                                        tempSql += "(CHAR(" + dbProp + ") LIKE '" + condition + "')";
                                    else
                                        tempSql += "(CHAR(" + this.tablePrefix + dbProp + ") LIKE '" + condition + "')";
                                    break;
                                default:
                                    LOG.warn("No case for converting regex expressing for database of type: " + Common.databaseProxy.getType());
                            }
                        } else {
                            // all other cases, add condition which will ensure no results are returned
                            tempSql += this.tablePrefix + "id = '-1'";
                        }
                    } else if (condition.startsWith("Int:")) {
                        // Parse the value as Int:operatorvalue - Int:>10000 OR Int:>=
                        int endAt = 5;
                        if (condition.charAt(5) == '=')
                            endAt = 6;
                        String value = condition.substring(endAt, condition.length());
                        String compare = condition.substring(4, endAt);
                        if (mapped)
                            tempSql += dbProp + " " + compare + " " + value;
                        else
                            tempSql += this.tablePrefix + dbProp + " " + compare + " " + value;
                    } else if (condition.startsWith("Long:")) {
                        // Parse the value as Long:operatorvalue - Long:>10000
                        String ms = condition.substring(6, condition.length());
                        String compare = condition.substring(5, 6);
                        if (mapped)
                            tempSql += dbProp + " " + compare + " " + ms;
                        else
                            tempSql += this.tablePrefix + dbProp + " " + compare + " " + ms;
                    } else if (condition.startsWith("LongRange:")) {
                        // Parse the value as LongRange:>startValue:<EndValue
                        String[] parts = condition.split(":");
                        String startCompare = parts[1].substring(0, 1);
                        String startMs = parts[1].substring(1, parts[1].length());
                        String endCompare = parts[2].substring(0, 1);
                        String endMs = parts[2].substring(1, parts[2].length());
                        if (mapped)
                            tempSql += dbProp + startCompare + startMs + " AND " + dbProp + endCompare + endMs;
                        else
                            tempSql += this.tablePrefix + dbProp + startCompare + startMs + " AND " + this.tablePrefix + dbProp + endCompare + endMs;
                    } else if (condition.startsWith("Duration:")) {
                        // Parse the value as Duration:operatorvalue - Duration:>1:00:00
                        String durationString = condition.substring(10, condition.length());
                        String compare = condition.substring(9, 10);
                        Long longValue = DeltamationCommon.unformatDuration(durationString);
                        if (mapped)
                            tempSql += dbProp + " " + compare + " " + longValue;
                        else
                            tempSql += this.tablePrefix + dbProp + " " + compare + " " + longValue;
                    } else if (condition.startsWith("BooleanIs:")) {
                        // Parse the value as BooleanIs:value
                        String booleanString = condition.substring(10, condition.length());
                        // Boolean value = Boolean.parseBoolean(booleanString);
                        if (mapped)
                            tempSql += dbProp + " IS " + booleanString;
                        else
                            tempSql += this.tablePrefix + dbProp + " = " + booleanString;
                    } else if (condition.startsWith("NullCheck:")) {
                        // Parse the value as NullCheck:true or NullCheck:false
                        String checkForString = condition.substring(10, condition.length());
                        Boolean checkFor = Boolean.parseBoolean(checkForString);
                        if (checkFor) {
                            if (mapped)
                                tempSql += dbProp + " IS NULL";
                            else
                                tempSql += this.tablePrefix + dbProp + " IS NULL";
                        } else {
                            if (mapped)
                                tempSql += dbProp + " IS NOT NULL";
                            else
                                tempSql += this.tablePrefix + dbProp + " IS NOT NULL";
                        }
                    } else {
                        // if (condition.isEmpty()) // occurs when empty array is set in query
                        // continue;
                        String[] parts = condition.split(",");
                        String qMarks = "";
                        for (int j = 0; j < parts.length; j++) {
                            args.add(parts[j]);
                            qMarks += j == 0 ? "?" : ",?";
                        }
                        // TODO not sure if IN will work with string values
                        if (mapped)
                            tempSql += dbProp + " IN (" + qMarks + ")";
                        else
                            tempSql += this.tablePrefix + dbProp + " IN (" + qMarks + ")";
                    }
                    sql += tempSql;
                    i++;
                }
            }
        // end if in filter map
        }
    }
    return sql;
}

Example 17 with IntStringPair

use of com.serotonin.db.pair.IntStringPair in project ma-core-public by infiniteautomation.

the class AbstractDao method applySort.

/**
 * Apply The Sort to the Query
 * @param sql
 * @param sort
 * @param selectArgs
 * @return
 */
protected String applySort(String sql, List<SortOption> sort, List<Object> selectArgs) {
    // always sort so that the offset/limit work as intended
    if (sort == null)
        sort = new ArrayList<SortOption>();
    if (sort.isEmpty())
        sort.add(new SortOption("id", false));
    int i = 0;
    Set<String> properties = this.propertyTypeMap.keySet();
    for (SortOption option : sort) {
        String prop = option.getAttribute();
        boolean mapped = false;
        if (!comparatorMap.containsKey(prop)) {
            // Don't allow sorting on values that have a comparator
            if (propertiesMap.containsKey(prop)) {
                IntStringPair pair = propertiesMap.get(prop);
                prop = pair.getValue();
                PropertyArguments args = propertyArgumentsMap.get(option.getAttribute());
                if (args != null) {
                    Collections.addAll(selectArgs, args.getArguments());
                }
                mapped = true;
            }
            if (mapped || properties.contains(prop)) {
                sql += i++ == 0 ? " ORDER BY " : ", ";
                if (mapped)
                    sql += prop;
                else
                    sql += this.tablePrefix + prop;
                if (option.isDesc()) {
                    sql += " DESC";
                }
            }
        }
    }
    return sql;
}

Example 18 with IntStringPair

use of com.serotonin.db.pair.IntStringPair in project ma-core-public by infiniteautomation.

the class EventHandlerDao method getPropertiesMap.

/* (non-Javadoc)
	 * @see com.serotonin.m2m2.db.dao.AbstractBasicDao#getPropertiesMap()
	 */
@Override
protected Map<String, IntStringPair> getPropertiesMap() {
    HashMap<String, IntStringPair> map = new HashMap<String, IntStringPair>();
    map.put("eventTypeName", new IntStringPair(Types.VARCHAR, "ehm.eventTypeName"));
    map.put("eventSubtypeName", new IntStringPair(Types.VARCHAR, "ehm.eventSubtypeName"));
    map.put("eventTypeRef1", new IntStringPair(Types.INTEGER, "ehm.eventTypeRef1"));
    map.put("eventTypeRef2", new IntStringPair(Types.INTEGER, "ehm.eventTypeRef2"));
    return map;
}

Example 19 with IntStringPair

use of com.serotonin.db.pair.IntStringPair in project ma-core-public by infiniteautomation.

the class EventInstanceDao method getPropertiesMap.

/* (non-Javadoc)
	 * @see com.serotonin.m2m2.db.dao.AbstractBasicDao#getPropertiesMap()
	 */
@Override
protected Map<String, IntStringPair> getPropertiesMap() {
    Map<String, IntStringPair> map = new HashMap<String, IntStringPair>();
    map.put("activeTimestamp", new IntStringPair(Types.BIGINT, "activeTs"));
    map.put("activeTimestampString", new IntStringPair(Types.BIGINT, "activeTs"));
    map.put("rtnTimestampString", new IntStringPair(Types.BIGINT, "rtnTs"));
    /*
         * IF(evt.rtnTs=null,
         * 		IF(evt.rtnApplicable='Y',
         * 			(NOW() - evt.activeTs),
         * 			-1),
         * 		IF(evt.rtnApplicable='Y',
         * 			(evt.rtnTs - evt.activeTs),
         * 			-1)
         *  )
         */
    switch(Common.databaseProxy.getType()) {
        case MYSQL:
        case MSSQL:
            map.put("totalTimeString", new IntStringPair(Types.BIGINT, "IF(evt.rtnTs is null,IF(evt.rtnApplicable='Y',(? - evt.activeTs),-1),IF(evt.rtnApplicable='Y',(evt.rtnTs - evt.activeTs),-1))"));
            break;
        case H2:
        case DERBY:
            map.put("totalTimeString", new IntStringPair(Types.BIGINT, "CASE WHEN evt.rtnTs IS NULL THEN " + "CASE WHEN evt.rtnApplicable='Y' THEN (? - evt.activeTs) ELSE -1 END " + "ELSE CASE WHEN evt.rtnApplicable='Y' THEN (evt.rtnTs - evt.activeTs) ELSE -1 END END"));
            break;
        default:
            throw new ShouldNeverHappenException("Unsupported database for Alarms.");
    }
    map.put("messageString", new IntStringPair(Types.VARCHAR, "message"));
    map.put("rtnTimestampString", new IntStringPair(Types.BIGINT, "rtnTs"));
    map.put("userNotified", new IntStringPair(Types.CHAR, "silenced"));
    map.put("acknowledged", new IntStringPair(Types.BIGINT, "ackTs"));
    // Mapping for user
    map.put("userId", new IntStringPair(Types.INTEGER, "ue.userId"));
    return map;
}

Example 20 with IntStringPair

use of com.serotonin.db.pair.IntStringPair in project ma-modules-public by infiniteautomation.

the class MultistateGraphicComponent method getImageStateList.

public List<IntStringPair> getImageStateList() {
    List<IntStringPair> result = new ArrayList<IntStringPair>();
    for (Integer state : stateImageMap.keySet()) {
        Integer imageId = stateImageMap.get(state);
        IntStringPair stateList = null;
        for (IntStringPair ivp : result) {
            if (ivp.getKey() == imageId) {
                stateList = ivp;
                break;
            }
        }
        if (stateList == null) {
            stateList = new IntStringPair(imageId, state.toString());
            result.add(stateList);
        } else
            stateList.setValue(stateList.getValue() + ',' + state.toString());
    }
    return result;
}