Examples with SQL lucee.runtime.db.SQL used on opensource projects

Example 1 with SQL

use of lucee.runtime.db.SQL in project Lucee by lucee.

the class Query method doEndTag.

@Override
public int doEndTag() throws PageException {
    if (hasChangedPSQ)
        pageContext.setPsq(orgPSQ);
    String strSQL = bodyContent.getString().trim();
    if (strSQL.isEmpty())
        throw new DatabaseException("no sql string defined, inside query tag", null, null, null);
    try {
        // cannot use attribute params and queryparam tag
        if (!items.isEmpty() && params != null)
            throw new DatabaseException("you cannot use the attribute params and sub tags queryparam at the same time", null, null, null);
        // create SQL
        SQL sql;
        if (params != null) {
            if (params instanceof Argument)
                sql = QueryParamConverter.convert(strSQL, (Argument) params);
            else if (Decision.isArray(params))
                sql = QueryParamConverter.convert(strSQL, Caster.toArray(params));
            else if (Decision.isStruct(params))
                sql = QueryParamConverter.convert(strSQL, Caster.toStruct(params));
            else
                throw new DatabaseException("value of the attribute [params] has to be a struct or a array", null, null, null);
        } else {
            sql = items.isEmpty() ? new SQLImpl(strSQL) : new SQLImpl(strSQL, items.toArray(new SQLItem[items.size()]));
        }
        // lucee.runtime.type.Query query=null;
        QueryResult queryResult = null;
        String cacheHandlerId = null;
        String cacheId = null;
        long exe = 0;
        boolean useCache = (cachedWithin != null) || (cachedAfter != null);
        CacheHandler cacheHandler = null;
        if (useCache) {
            cacheId = CacheHandlerCollectionImpl.createId(sql, datasource != null ? datasource.getName() : null, username, password, returntype);
            CacheHandlerCollectionImpl coll = (CacheHandlerCollectionImpl) pageContext.getConfig().getCacheHandlerCollection(Config.CACHE_TYPE_QUERY, null);
            cacheHandler = coll.getInstanceMatchingObject(cachedWithin, null);
            if (cacheHandler == null && cachedAfter != null)
                cacheHandler = coll.getTimespanInstance(null);
            if (cacheHandler != null) {
                // cacheHandlerId specifies to queryResult the cacheType and therefore whether the query is cached or
                cacheHandlerId = cacheHandler.id();
                if (cacheHandler instanceof CacheHandlerPro) {
                    CacheItem cacheItem = ((CacheHandlerPro) cacheHandler).get(pageContext, cacheId, (cachedWithin != null) ? cachedWithin : cachedAfter);
                    if (cacheItem instanceof QueryResultCacheItem)
                        queryResult = ((QueryResultCacheItem) cacheItem).getQueryResult();
                } else {
                    // FUTURE this else block can be removed when all cache handlers implement CacheHandlerPro
                    CacheItem cacheItem = cacheHandler.get(pageContext, cacheId);
                    if (cacheItem instanceof QueryResultCacheItem) {
                        QueryResultCacheItem queryCachedItem = (QueryResultCacheItem) cacheItem;
                        Date cacheLimit = cachedAfter;
                        if (cacheLimit == null || queryCachedItem.isCachedAfter(cacheLimit))
                            queryResult = queryCachedItem.getQueryResult();
                    }
                }
            } else {
                List<String> patterns = pageContext.getConfig().getCacheHandlerCollection(Config.CACHE_TYPE_QUERY, null).getPatterns();
                throw new ApplicationException("cachedwithin value [" + cachedWithin + "] is invalid, valid values are for example [" + ListUtil.listToList(patterns, ", ") + "]");
            }
        // query=pageContext.getQueryCache().getQuery(pageContext,sql,datasource!=null?datasource.getName():null,username,password,cachedafter);
        }
        // cache not found, process and cache result if needed
        if (queryResult == null) {
            // QoQ
            if ("query".equals(dbtype)) {
                lucee.runtime.type.Query q = executeQoQ(sql);
                if (returntype == RETURN_TYPE_ARRAY)
                    // TODO this should be done in queryExecute itself so we not have to convert afterwards
                    queryResult = QueryArray.toQueryArray(q);
                else if (returntype == RETURN_TYPE_STRUCT) {
                    if (columnName == null)
                        throw new ApplicationException("attribute columnKey is required when return type is set to struct");
                    // TODO this should be done in queryExecute itself so we not have to convert
                    queryResult = QueryStruct.toQueryStruct(q, columnName);
                // afterwards
                } else
                    queryResult = (QueryResult) q;
            } else // ORM and Datasource
            {
                long start = System.nanoTime();
                Object obj;
                if ("orm".equals(dbtype) || "hql".equals(dbtype))
                    obj = executeORM(sql, returntype, ormoptions);
                else
                    obj = executeDatasoure(sql, result != null, pageContext.getTimeZone());
                if (obj instanceof QueryResult) {
                    queryResult = (QueryResult) obj;
                } else {
                    if (setReturnVariable) {
                        rtn = obj;
                    } else if (!StringUtil.isEmpty(name)) {
                        pageContext.setVariable(name, obj);
                    }
                    if (result != null) {
                        Struct sct = new StructImpl();
                        sct.setEL(KeyConstants._cached, Boolean.FALSE);
                        long time = System.nanoTime() - start;
                        sct.setEL(KeyConstants._executionTime, Caster.toDouble(time / 1000000));
                        sct.setEL(KeyConstants._executionTimeNano, Caster.toDouble(time));
                        sct.setEL(KeyConstants._SQL, sql.getSQLString());
                        if (Decision.isArray(obj)) {
                        } else
                            sct.setEL(KeyConstants._RECORDCOUNT, Caster.toDouble(1));
                        pageContext.setVariable(result, sct);
                    } else
                        setExecutionTime((System.nanoTime() - start) / 1000000);
                    return EVAL_PAGE;
                }
            }
            if (cachedWithin != null) {
                CacheItem cacheItem = QueryResultCacheItem.newInstance(queryResult, tags, datasource, null);
                if (cacheItem != null)
                    cacheHandler.set(pageContext, cacheId, cachedWithin, cacheItem);
            }
            exe = queryResult.getExecutionTime();
        } else {
            queryResult.setCacheType(cacheHandlerId);
        }
        if (pageContext.getConfig().debug() && debug) {
            boolean logdb = ((ConfigImpl) pageContext.getConfig()).hasDebugOptions(ConfigImpl.DEBUG_DATABASE);
            if (logdb) {
                boolean debugUsage = DebuggerImpl.debugQueryUsage(pageContext, queryResult);
                DebuggerImpl di = (DebuggerImpl) pageContext.getDebugger();
                di.addQuery(debugUsage ? queryResult : null, datasource != null ? datasource.getName() : null, name, sql, queryResult.getRecordcount(), getPageSource(), exe);
            }
        }
        if (setReturnVariable) {
            rtn = queryResult;
        } else if ((queryResult.getColumncount() + queryResult.getRecordcount()) > 0 && !StringUtil.isEmpty(name)) {
            pageContext.setVariable(name, queryResult);
        }
        // Result
        if (result != null) {
            Struct sct = new StructImpl();
            sct.setEL(KeyConstants._cached, Caster.toBoolean(queryResult.isCached()));
            if ((queryResult.getColumncount() + queryResult.getRecordcount()) > 0) {
                String list = ListUtil.arrayToList(queryResult instanceof lucee.runtime.type.Query ? ((lucee.runtime.type.Query) queryResult).getColumnNamesAsString() : CollectionUtil.toString(queryResult.getColumnNames(), false), ",");
                sct.setEL(KeyConstants._COLUMNLIST, list);
            }
            int rc = queryResult.getRecordcount();
            if (rc == 0)
                rc = queryResult.getUpdateCount();
            sct.setEL(KeyConstants._RECORDCOUNT, Caster.toDouble(rc));
            sct.setEL(KeyConstants._executionTime, Caster.toDouble(queryResult.getExecutionTime() / 1000000));
            sct.setEL(KeyConstants._executionTimeNano, Caster.toDouble(queryResult.getExecutionTime()));
            sct.setEL(KeyConstants._SQL, sql.getSQLString());
            // GENERATED KEYS
            lucee.runtime.type.Query qi = Caster.toQuery(queryResult, null);
            if (qi != null) {
                lucee.runtime.type.Query qryKeys = qi.getGeneratedKeys();
                if (qryKeys != null) {
                    StringBuilder generatedKey = new StringBuilder(), sb;
                    Collection.Key[] columnNames = qryKeys.getColumnNames();
                    QueryColumn column;
                    for (int c = 0; c < columnNames.length; c++) {
                        column = qryKeys.getColumn(columnNames[c]);
                        sb = new StringBuilder();
                        int size = column.size();
                        for (int row = 1; row <= size; row++) {
                            if (row > 1)
                                sb.append(',');
                            sb.append(Caster.toString(column.get(row, null)));
                        }
                        if (sb.length() > 0) {
                            sct.setEL(columnNames[c], sb.toString());
                            if (generatedKey.length() > 0)
                                generatedKey.append(',');
                            generatedKey.append(sb);
                        }
                    }
                    if (generatedKey.length() > 0)
                        sct.setEL(GENERATEDKEY, generatedKey.toString());
                }
            }
            // sqlparameters
            SQLItem[] params = sql.getItems();
            if (params != null && params.length > 0) {
                Array arr = new ArrayImpl();
                sct.setEL(SQL_PARAMETERS, arr);
                for (int i = 0; i < params.length; i++) {
                    arr.append(params[i].getValue());
                }
            }
            pageContext.setVariable(result, sct);
        } else // cfquery.executiontime
        {
            setExecutionTime(exe / 1000000);
        }
        // listener
        ((ConfigWebImpl) pageContext.getConfig()).getActionMonitorCollector().log(pageContext, "query", "Query", exe, queryResult);
        // log
        Log log = pageContext.getConfig().getLog("datasource");
        if (log.getLogLevel() >= Log.LEVEL_INFO) {
            log.info("query tag", "executed [" + sql.toString().trim() + "] in " + DecimalFormat.call(pageContext, exe / 1000000D) + " ms");
        }
    } catch (PageException pe) {
        // log
        pageContext.getConfig().getLog("datasource").error("query tag", pe);
        throw pe;
    } finally {
        ((PageContextImpl) pageContext).setTimestampWithTSOffset(previousLiteralTimestampWithTSOffset);
        if (tmpTZ != null) {
            pageContext.setTimeZone(tmpTZ);
        }
    }
    return EVAL_PAGE;
}

Example 2 with SQL

use of lucee.runtime.db.SQL in project Lucee by lucee.

the class Update method doEndTag.

@Override
public int doEndTag() throws PageException {
    Object ds = DBInfo.getDatasource(pageContext, datasource);
    DataSourceManager manager = pageContext.getDataSourceManager();
    DatasourceConnection dc = ds instanceof DataSource ? manager.getConnection(pageContext, (DataSource) ds, username, password) : manager.getConnection(pageContext, Caster.toString(ds), username, password);
    try {
        Struct meta = null;
        try {
            meta = Insert.getMeta(dc, tablequalifier, tableowner, tablename);
        } catch (SQLException se) {
            meta = new StructImpl();
        }
        String[] pKeys = getPrimaryKeys(dc);
        SQL sql = createSQL(dc, pKeys, meta);
        if (sql != null) {
            lucee.runtime.type.Query query = new QueryImpl(pageContext, dc, sql, -1, -1, null, "query");
            if (pageContext.getConfig().debug()) {
                String dsn = ds instanceof DataSource ? ((DataSource) ds).getName() : Caster.toString(ds);
                boolean logdb = ((ConfigImpl) pageContext.getConfig()).hasDebugOptions(ConfigImpl.DEBUG_DATABASE);
                if (logdb) {
                    boolean debugUsage = DebuggerUtil.debugQueryUsage(pageContext, query);
                    pageContext.getDebugger().addQuery(debugUsage ? query : null, dsn, "", sql, query.getRecordcount(), pageContext.getCurrentPageSource(), query.getExecutionTime());
                }
            }
            // log
            Log log = pageContext.getConfig().getLog("datasource");
            if (log.getLogLevel() >= Log.LEVEL_INFO) {
                log.info("update tag", "executed [" + sql.toString().trim() + "] in " + DecimalFormat.call(pageContext, query.getExecutionTime() / 1000000D) + " ms");
            }
        }
        return EVAL_PAGE;
    } catch (PageException pe) {
        pageContext.getConfig().getLog("datasource").error("update tag", pe);
        throw pe;
    } finally {
        manager.releaseConnection(pageContext, dc);
    }
}

Example 3 with SQL

use of lucee.runtime.db.SQL in project Lucee by lucee.

the class Ansi92 method clean.

@Override
public void clean(Config config, DatasourceConnection dc, int type, StorageScopeEngine engine, DatasourceStorageScopeCleaner cleaner, StorageScopeListener listener, Log log) throws PageException {
    String strType = VariableInterpreter.scopeInt2String(type);
    // select
    SQL sqlSelect = new SQLImpl("select cfid,name from " + PREFIX + "_" + strType + "_data where expires<=?", new SQLItem[] { new SQLItemImpl(System.currentTimeMillis(), Types.VARCHAR) });
    Query query;
    try {
        query = new QueryImpl(ThreadLocalPageContext.get(), dc, sqlSelect, -1, -1, null, "query");
    } catch (Throwable t) {
        ExceptionUtil.rethrowIfNecessary(t);
        // possible that the table not exist, if not there is nothing to clean
        return;
    }
    int recordcount = query.getRecordcount();
    String cfid, name;
    for (int row = 1; row <= recordcount; row++) {
        cfid = Caster.toString(query.getAt(KeyConstants._cfid, row, null), null);
        name = Caster.toString(query.getAt(KeyConstants._name, row, null), null);
        if (listener != null)
            listener.doEnd(engine, cleaner, name, cfid);
        ScopeContext.info(log, "remove " + strType + "/" + name + "/" + cfid + " from datasource " + dc.getDatasource().getName());
        engine.remove(type, name, cfid);
        SQLImpl sql = new SQLImpl("delete from " + StorageScopeDatasource.PREFIX + "_" + strType + "_data where cfid=? and name=?", new SQLItem[] { new SQLItemImpl(cfid, Types.VARCHAR), new SQLItemImpl(name, Types.VARCHAR) });
        new QueryImpl(ThreadLocalPageContext.get(), dc, sql, -1, -1, null, "query");
    }
}

Example 4 with SQL

use of lucee.runtime.db.SQL in project Lucee by lucee.

the class ColumnInfo method doEndTag.

@Override
public int doEndTag() throws PageException {
    Object ds = DBInfo.getDatasource(pageContext, datasource);
    DataSourceManager manager = pageContext.getDataSourceManager();
    DatasourceConnection dc = ds instanceof DataSource ? manager.getConnection(pageContext, (DataSource) ds, username, password) : manager.getConnection(pageContext, Caster.toString(ds), username, password);
    try {
        Struct meta = null;
        try {
            meta = getMeta(dc, tablequalifier, tableowner, tablename);
        } catch (SQLException se) {
            meta = new StructImpl();
        }
        SQL sql = createSQL(meta);
        if (sql != null) {
            lucee.runtime.type.Query query = new QueryImpl(pageContext, dc, sql, -1, -1, null, "query");
            if (pageContext.getConfig().debug()) {
                String dsn = ds instanceof DataSource ? ((DataSource) ds).getName() : Caster.toString(ds);
                boolean logdb = ((ConfigImpl) pageContext.getConfig()).hasDebugOptions(ConfigImpl.DEBUG_DATABASE);
                if (logdb) {
                    boolean debugUsage = DebuggerImpl.debugQueryUsage(pageContext, query);
                    pageContext.getDebugger().addQuery(debugUsage ? query : null, dsn, "", sql, query.getRecordcount(), pageContext.getCurrentPageSource(), query.getExecutionTime());
                }
            }
            // log
            Log log = pageContext.getConfig().getLog("datasource");
            if (log.getLogLevel() >= Log.LEVEL_INFO) {
                log.info("insert tag", "executed [" + sql.toString().trim() + "] in " + DecimalFormat.call(pageContext, query.getExecutionTime() / 1000000D) + " ms");
            }
        }
        return EVAL_PAGE;
    } catch (PageException pe) {
        pageContext.getConfig().getLog("datasource").error("insert tag", pe);
        throw pe;
    } finally {
        manager.releaseConnection(pageContext, dc);
    }
}

Example 5 with SQL

use of lucee.runtime.db.SQL in project Lucee by lucee.

the class QueryUtil method toDumpData.

public static DumpData toDumpData(Query query, PageContext pageContext, int maxlevel, DumpProperties dp) {
    maxlevel--;
    Collection.Key[] keys = CollectionUtil.keys(query);
    DumpData[] heads = new DumpData[keys.length + 1];
    // int tmp=1;
    heads[0] = new SimpleDumpData("");
    for (int i = 0; i < keys.length; i++) {
        heads[i + 1] = new SimpleDumpData(keys[i].getString());
    }
    StringBuilder comment = new StringBuilder();
    // table.appendRow(1, new SimpleDumpData("SQL"), new SimpleDumpData(sql.toString()));
    String template = query.getTemplate();
    if (!StringUtil.isEmpty(template))
        comment.append("Template: ").append(template).append("\n");
    // table.appendRow(1, new SimpleDumpData("Template"), new SimpleDumpData(template));
    // in Query dump maxlevel is used as Top
    int top = dp.getMaxlevel();
    comment.append("Execution Time: ").append(Caster.toString(FormatUtil.formatNSAsMSDouble(query.getExecutionTime()))).append(" ms \n");
    comment.append("Record Count: ").append(Caster.toString(query.getRecordcount()));
    if (query.getRecordcount() > top)
        comment.append(" (showing top ").append(Caster.toString(top)).append(")");
    comment.append("\n");
    comment.append("Cached: ").append(query.isCached() ? "Yes\n" : "No\n");
    if (query.isCached() && query instanceof Query) {
        comment.append("Cache Type: ").append(query.getCacheType()).append("\n");
    }
    comment.append("Lazy: ").append(query instanceof SimpleQuery ? "Yes\n" : "No\n");
    SQL sql = query.getSql();
    if (sql != null)
        comment.append("SQL: ").append("\n").append(StringUtil.suppressWhiteSpace(sql.toString().trim())).append("\n");
    // table.appendRow(1, new SimpleDumpData("Execution Time (ms)"), new SimpleDumpData(exeTime));
    // table.appendRow(1, new SimpleDumpData("recordcount"), new SimpleDumpData(getRecordcount()));
    // table.appendRow(1, new SimpleDumpData("cached"), new SimpleDumpData(isCached()?"Yes":"No"));
    DumpTable recs = new DumpTable("query", "#cc99cc", "#ffccff", "#000000");
    recs.setTitle("Query");
    if (dp.getMetainfo())
        recs.setComment(comment.toString());
    recs.appendRow(new DumpRow(-1, heads));
    // body
    DumpData[] items;
    int recordcount = query.getRecordcount();
    int columncount = query.getColumnNames().length;
    for (int i = 0; i < recordcount; i++) {
        items = new DumpData[columncount + 1];
        items[0] = new SimpleDumpData(i + 1);
        for (int y = 0; y < keys.length; y++) {
            try {
                Object o = query.getAt(keys[y], i + 1);
                if (o instanceof String)
                    items[y + 1] = new SimpleDumpData(o.toString());
                else if (o instanceof Number)
                    items[y + 1] = new SimpleDumpData(Caster.toString(((Number) o)));
                else if (o instanceof Boolean)
                    items[y + 1] = new SimpleDumpData(((Boolean) o).booleanValue());
                else if (o instanceof Date)
                    items[y + 1] = new SimpleDumpData(Caster.toString(o));
                else if (o instanceof Clob)
                    items[y + 1] = new SimpleDumpData(Caster.toString(o));
                else
                    items[y + 1] = DumpUtil.toDumpData(o, pageContext, maxlevel, dp);
            } catch (PageException e) {
                items[y + 1] = new SimpleDumpData("[empty]");
            }
        }
        recs.appendRow(new DumpRow(1, items));
        if (i == top - 1)
            break;
    }
    if (!dp.getMetainfo())
        return recs;
    // table.appendRow(1, new SimpleDumpData("result"), recs);
    return recs;
}