Examples with QueryColumn lucee.runtime.type.QueryColumn used on opensource projects

Example 6 with QueryColumn

use of lucee.runtime.type.QueryColumn in project Lucee by lucee.

the class QueryColumnData method call.

public static Array call(PageContext pc, Query query, String columnName, UDF udf) throws PageException {
    Array arr = new ArrayImpl();
    QueryColumn column = query.getColumn(KeyImpl.init(columnName));
    Iterator<Object> it = column.valueIterator();
    Object value;
    short type = SQLCaster.toCFType(column.getType(), lucee.commons.lang.CFTypes.TYPE_UNDEFINED);
    while (it.hasNext()) {
        value = it.next();
        if (!NullSupportHelper.full(pc) && value == null)
            value = "";
        // callback call
        if (udf != null)
            value = udf.call(pc, new Object[] { value }, true);
        // convert (if necessary)
        value = Caster.castTo(pc, type, column.getTypeAsString(), value, value);
        arr.append(value);
    }
    return arr;
}

Example 7 with QueryColumn

use of lucee.runtime.type.QueryColumn in project Lucee by lucee.

the class QuerySetColumn method call.

public static String call(PageContext pc, Query query, String columnName, String newColumnName) throws PageException {
    columnName = columnName.trim();
    newColumnName = newColumnName.trim();
    Collection.Key src = KeyImpl.getInstance(columnName);
    Collection.Key trg = KeyImpl.getInstance(newColumnName);
    Query qp = Caster.toQuery(query, null);
    if (qp != null)
        qp.rename(src, trg);
    else {
        QueryColumn qc = query.removeColumn(src);
        Array content = new ArrayImpl();
        int len = qc.size();
        for (int i = 1; i <= len; i++) {
            content.setE(i, qc.get(i, null));
        }
        query.addColumn(trg, content, qc.getType());
    }
    return null;
}

Example 8 with QueryColumn

use of lucee.runtime.type.QueryColumn in project Lucee by lucee.

the class DBInfo method typeColumns.

private void typeColumns(DatabaseMetaData metaData) throws PageException, SQLException {
    required("table", table);
    Stopwatch stopwatch = new Stopwatch(Stopwatch.UNIT_NANO);
    stopwatch.start();
    table = setCase(metaData, table);
    pattern = setCase(metaData, pattern);
    if (StringUtil.isEmpty(pattern, true))
        pattern = null;
    String schema = null;
    int index = table.indexOf('.');
    if (index > 0) {
        schema = table.substring(0, index);
        table = table.substring(index + 1);
    }
    checkTable(metaData);
    Query qry = new QueryImpl(metaData.getColumns(dbname, schema, table, pattern), "query", pageContext.getTimeZone());
    int len = qry.getRecordcount();
    if (qry.getColumn(COLUMN_DEF, null) != null)
        qry.rename(COLUMN_DEF, COLUMN_DEFAULT_VALUE);
    else if (qry.getColumn(COLUMN_DEFAULT, null) != null)
        qry.rename(COLUMN_DEFAULT, COLUMN_DEFAULT_VALUE);
    // make sure decimal digits exists
    QueryColumn col = qry.getColumn(DECIMAL_DIGITS, null);
    if (col == null) {
        Array arr = new ArrayImpl();
        for (int i = 1; i <= len; i++) {
            arr.append(lucee.runtime.op.Constants.DOUBLE_ZERO);
        }
        qry.addColumn(DECIMAL_DIGITS, arr);
    }
    // add is primary
    Map primaries = new HashMap();
    String tblName;
    Array isPrimary = new ArrayImpl();
    Set set;
    Object o;
    for (int i = 1; i <= len; i++) {
        // decimal digits
        o = qry.getAt(DECIMAL_DIGITS, i, null);
        if (o == null)
            qry.setAtEL(DECIMAL_DIGITS, i, lucee.runtime.op.Constants.DOUBLE_ZERO);
        set = (Set) primaries.get(tblName = (String) qry.getAt(TABLE_NAME, i));
        if (set == null) {
            set = toSet(metaData.getPrimaryKeys(dbname, null, tblName), true, "COLUMN_NAME");
            primaries.put(tblName, set);
        }
        isPrimary.append(set.contains(qry.getAt(COLUMN_NAME, i)) ? "YES" : "NO");
    }
    qry.addColumn(IS_PRIMARYKEY, isPrimary);
    // add is foreignkey
    Map foreigns = new HashMap();
    Array isForeign = new ArrayImpl();
    Array refPrim = new ArrayImpl();
    Array refPrimTbl = new ArrayImpl();
    // Map map,inner;
    Map<String, Map<String, SVArray>> map;
    Map<String, SVArray> inner;
    for (int i = 1; i <= len; i++) {
        map = (Map) foreigns.get(tblName = (String) qry.getAt(TABLE_NAME, i));
        if (map == null) {
            map = toMap(metaData.getImportedKeys(dbname, schema, table), true, "FKCOLUMN_NAME", new String[] { "PKCOLUMN_NAME", "PKTABLE_NAME" });
            foreigns.put(tblName, map);
        }
        inner = map.get(qry.getAt(COLUMN_NAME, i));
        if (inner != null) {
            isForeign.append("YES");
            refPrim.append(inner.get("PKCOLUMN_NAME"));
            refPrimTbl.append(inner.get("PKTABLE_NAME"));
        } else {
            isForeign.append("NO");
            refPrim.append("N/A");
            refPrimTbl.append("N/A");
        }
    }
    qry.addColumn(IS_FOREIGNKEY, isForeign);
    qry.addColumn(REFERENCED_PRIMARYKEY, refPrim);
    qry.addColumn(REFERENCED_PRIMARYKEY_TABLE, refPrimTbl);
    qry.setExecutionTime(stopwatch.time());
    pageContext.setVariable(name, qry);
}

Example 9 with QueryColumn

use of lucee.runtime.type.QueryColumn in project Lucee by lucee.

the class Executer method testExecute.

private Query testExecute(PageContext pc, SQL sql, Query qr, ZQuery query, int maxrows) throws PageException {
    int recCount = qr.getRecordcount();
    Vector vSelects = query.getSelect();
    int selCount = vSelects.size();
    Map<Collection.Key, Object> selects = MapFactory.<Collection.Key, Object>getConcurrentMap();
    boolean isSMS = false;
    // headers
    for (int i = 0; i < selCount; i++) {
        ZSelectItem select = (ZSelectItem) vSelects.get(i);
        if (select.isWildcard() || (isSMS = select.getColumn().equals(SQLPrettyfier.PLACEHOLDER_ASTERIX))) {
            if (!isSMS && !select.getColumn().equals("*"))
                throw new DatabaseException("can't execute this type of query at the moment", null, sql, null);
            // Collection.Key[] keys = qr.keys();
            Iterator<Key> it = qr.keyIterator();
            Key k;
            while (it.hasNext()) {
                k = it.next();
                selects.put(k, k.getString());
            }
            isSMS = false;
        } else {
            // if(SQLPrettyfier.PLACEHOLDER_COUNT.equals(select.getAlias())) select.setAlias("count");
            // if(SQLPrettyfier.PLACEHOLDER_COUNT.equals(select.getColumn())) select.setExpression(new ZConstant("count",ZConstant.COLUMNNAME));
            String alias = select.getAlias();
            String column = select.getColumn();
            if (alias == null)
                alias = column;
            alias = alias.toLowerCase();
            selects.put(KeyImpl.init(alias), select);
        }
    }
    Key[] headers = selects.keySet().toArray(new Collection.Key[selects.size()]);
    // aHeaders.toArray(new String[aHeaders.size()]);
    QueryImpl rtn = new QueryImpl(headers, 0, "query", sql);
    // loop records
    Vector orders = query.getOrderBy();
    ZExp where = query.getWhere();
    // print.out(headers);
    // int newRecCount=0;
    boolean hasMaxrow = maxrows > -1 && (orders == null || orders.size() == 0);
    for (int row = 1; row <= recCount; row++) {
        sql.setPosition(0);
        if (hasMaxrow && maxrows <= rtn.getRecordcount())
            break;
        boolean useRow = where == null || Caster.toBooleanValue(executeExp(pc, sql, qr, where, row));
        if (useRow) {
            rtn.addRow(1);
            for (int cell = 0; cell < headers.length; cell++) {
                Object value = selects.get(headers[cell]);
                rtn.setAt(headers[cell], rtn.getRecordcount(), getValue(pc, sql, qr, row, headers[cell], value));
            }
        }
    }
    // Group By
    if (query.getGroupBy() != null)
        throw new DatabaseException("group by are not supported at the moment", null, sql, null);
    // Order By
    if (orders != null && orders.size() > 0) {
        int len = orders.size();
        for (int i = len - 1; i >= 0; i--) {
            ZOrderBy order = (ZOrderBy) orders.get(i);
            ZConstant name = (ZConstant) order.getExpression();
            rtn.sort(name.getValue().toLowerCase(), order.getAscOrder() ? Query.ORDER_ASC : Query.ORDER_DESC);
        }
        if (maxrows > -1) {
            rtn.cutRowsTo(maxrows);
        }
    }
    // Distinct
    if (query.isDistinct()) {
        String[] keys = rtn.getColumns();
        QueryColumn[] columns = new QueryColumn[keys.length];
        for (int i = 0; i < columns.length; i++) {
            columns[i] = rtn.getColumn(keys[i]);
        }
        int i;
        outer: for (int row = rtn.getRecordcount(); row > 1; row--) {
            for (i = 0; i < columns.length; i++) {
                if (!Operator.equals(QueryUtil.getValue(columns[i], row), QueryUtil.getValue(columns[i], row - 1), true))
                    continue outer;
            }
            rtn.removeRow(row);
        }
    }
    // UNION // TODO support it
    ZExpression set = query.getSet();
    if (set != null) {
        ZExp op = set.getOperand(0);
        if (op instanceof ZQuery)
            throw new DatabaseException("union is not supported at the moment", null, sql, null);
    // getInvokedTables((ZQuery)op, tablesNames);
    }
    return rtn;
}

Example 10 with QueryColumn

use of lucee.runtime.type.QueryColumn in project Lucee by lucee.

the class QoQ method execute.

/**
 * execute a SQL Statement against CFML Scopes
 */
public Query execute(PageContext pc, SQL sql, Selects selects, int maxrows) throws PageException {
    Column[] orders = selects.getOrderbys();
    Select[] arrSelects = selects.getSelects();
    QueryImpl target = new QueryImpl(new Collection.Key[0], 0, "query", sql);
    for (int i = 0; i < arrSelects.length; i++) {
        arrSelects[i].getFroms();
        Column[] froms = arrSelects[i].getFroms();
        if (froms.length > 1)
            throw new DatabaseException("can only work with single tables yet", null, sql, null);
        executeSingle(pc, arrSelects[i], getSingleTable(pc, froms[0]), target, arrSelects.length > 1 ? -1 : maxrows, sql, orders.length > 0);
    }
    // Order By
    if (orders.length > 0) {
        order(target, orders);
        if (maxrows > -1)
            target.cutRowsTo(maxrows);
    }
    // Distinct
    if (selects.isDistinct()) {
        // order to filter
        order(target, selects.getDistincts());
        // print.e(selects.getDistincts());
        Key[] _keys = target.getColumnNames();
        QueryColumn[] columns = new QueryColumn[_keys.length];
        for (int i = 0; i < columns.length; i++) {
            columns[i] = target.getColumn(_keys[i]);
        }
        int i;
        Object l, r;
        outer: for (int row = target.getRecordcount(); row > 1; row--) {
            for (i = 0; i < columns.length; i++) {
                l = columns[i].get(row, null);
                r = columns[i].get(row - 1, null);
                if (l == null || r == null) {
                    if (l != r)
                        continue outer;
                } else if (!Operator.equals(l, r, true))
                    continue outer;
            }
            target.removeRow(row);
        }
    }
    order(target, orders);
    return target;
}