Examples with ISQLJoin com.servoy.j2db.query.ISQLJoin used on opensource projects

Example 1 with ISQLJoin

use of com.servoy.j2db.query.ISQLJoin in project servoy-client by Servoy.

the class FoundSet method findQueryRelationSequence.

private List<Relation> findQueryRelationSequence(QuerySelect sqlSelect, BaseQueryTable qTable) {
    if (sqlSelect.getTable() == qTable) {
        // column on base table, not related
        return Collections.emptyList();
    }
    // find the join to this table
    for (ISQLJoin join : iterate(sqlSelect.getJoins())) {
        if (join.getName() != null && join instanceof QueryJoin && ((QueryJoin) join).getForeignTable() == qTable) {
            Relation relation = fsm.getRelation(join.getName());
            if (relation != null && relation.getForeignDataSource().equals(qTable.getDataSource())) {
                List<Relation> subRelated = findQueryRelationSequence(sqlSelect, ((QueryJoin) join).getPrimaryTable());
                if (subRelated != null) {
                    // found matching relation sequence
                    List<Relation> relationSequence = new ArrayList<>(subRelated);
                    relationSequence.add(relation);
                    return relationSequence;
                }
            }
        }
    }
    // not found
    return null;
}

Example 2 with ISQLJoin

use of com.servoy.j2db.query.ISQLJoin in project servoy-client by Servoy.

the class FoundSet method addFilterconditions.

private QuerySelect addFilterconditions(QuerySelect select, List<TableFilter> filters) {
    for (TableFilter tf : iterate(filters)) {
        QueryFilter filtercondition = SQLGenerator.createTableFiltercondition(select.getTable(), sheet.getTable(), tf);
        select.addCondition(SQLGenerator.CONDITION_FILTER, filtercondition.getCondition());
        for (ISQLJoin join : iterate(filtercondition.getJoins())) {
            join.setOrigin(tf);
            select.addJoin(join);
        }
    }
    return select;
}

Example 3 with ISQLJoin

use of com.servoy.j2db.query.ISQLJoin in project servoy-client by Servoy.

the class FoundSet method loadByQuery.

public boolean loadByQuery(String query, Object[] args) throws ServoyException {
    if (query == null || sheet.getTable() == null)
        return false;
    int from_index = -1;
    int order_by_index;
    // check requirements
    if (!SQLGenerator.isSelectQuery(query)) {
        throw new IllegalArgumentException(SQLGenerator.SQL_QUERY_VALIDATION_MESSAGE + ':' + query);
    }
    String sql_lowercase = Utils.toEnglishLocaleLowerCase(query);
    // $NON-NLS-1$
    order_by_index = sql_lowercase.lastIndexOf("order by");
    boolean analyse_query_parts = (order_by_index != -1);
    if (analyse_query_parts) {
        // subquery. NOTE: this means that the ordering defined in the order-by part is lost.
        if (// $NON-NLS-1$
        ((from_index = sql_lowercase.indexOf("from")) == -1) || (sql_lowercase.indexOf(Utils.toEnglishLocaleLowerCase(sheet.getTable().getSQLName())) == -1) || // $NON-NLS-1$
        (sql_lowercase.indexOf("group by") != -1) || // $NON-NLS-1$
        (sql_lowercase.indexOf("having") != -1) || // $NON-NLS-1$
        (sql_lowercase.indexOf("union") != -1) || // $NON-NLS-1$
        (sql_lowercase.indexOf("join") != -1) || // $NON-NLS-1$
        (sql_lowercase.indexOf(".") == -1)) {
            analyse_query_parts = false;
        }
    }
    if (initialized && (getFoundSetManager().getEditRecordList().stopIfEditing(this) != ISaveConstants.STOPPED)) {
        fsm.getApplication().reportJSError(// $NON-NLS-1$
        "couldn't load dataset because foundset had edited records but couldn't save it: " + this + ", edited record(s): " + Utils.stringJoin(getFoundSetManager().getEditRecordList().getEditedRecords(this), '.') + ", failed record(s): " + Utils.stringJoin(getFoundSetManager().getEditRecordList().getFailedRecords(this), '.'), null);
        return false;
    }
    QuerySelect originalQuery = pksAndRecords.getQuerySelectForReading();
    QuerySelect sqlSelect = AbstractBaseQuery.deepClone(creationSqlSelect);
    sqlSelect.clearCondition(SQLGenerator.CONDITION_RELATION);
    sqlSelect.clearCondition(SQLGenerator.CONDITION_OMIT);
    if (rowManager != null)
        rowManager.clearAndCheckCache();
    initialized = true;
    Object[] whereArgs = null;
    if (args != null) {
        whereArgs = new Object[args.length];
        for (int i = 0; i < args.length; i++) {
            Object o = args[i];
            if (o != null && o.getClass().equals(Date.class)) {
                o = new Timestamp(((Date) o).getTime());
            }
            whereArgs[i] = o;
        }
    }
    // for instance, loadRecords(SQL) followed by extended search (S) and invertrecords executes query 'NOT(SQL OR S)'
    if (!analyse_query_parts) {
        // do not analyze the parts of the query, just create a set-condition that compares the pk columns with the result of the subquery
        Iterator<Column> pkIt = ((Table) getTable()).getRowIdentColumns().iterator();
        if (!pkIt.hasNext()) {
            throw new RepositoryException(ServoyException.InternalCodes.PRIMARY_KEY_NOT_FOUND, new Object[] { getTable().getName() }).setContext(this.toString());
        }
        List<QueryColumn> pkQueryColumns = new ArrayList<QueryColumn>();
        while (pkIt.hasNext()) {
            Column c = pkIt.next();
            pkQueryColumns.add(c.queryColumn(sqlSelect.getTable()));
        }
        // must strip of the order-by part because not all databases (Oracle, who else) like order-by in subselect
        String customQuery = query;
        if (order_by_index > 0) {
            // query contains order-by clause, find the next a closing bracket if it exists.
            // order-by has to be removed because some dbs do not allow that inside subselect.
            char[] chars = query.toCharArray();
            int level = 1;
            int i;
            for (i = order_by_index; level > 0 && i < chars.length; i++) {
                switch(chars[i]) {
                    case ')':
                        level--;
                        break;
                    case '(':
                        level++;
                        break;
                }
            }
            // in that case we can leave the ordering in place because it it not the sorting of the top-level query.
            if (level == 1) {
                // order-by clause was at the end
                customQuery = query.substring(0, order_by_index);
            }
        }
        sqlSelect.setCondition(SQLGenerator.CONDITION_SEARCH, new SetCondition(IBaseSQLCondition.IN_OPERATOR, pkQueryColumns.toArray(new QueryColumn[pkQueryColumns.size()]), new QueryCustomSelect(customQuery, whereArgs), true));
        // set the previous sort, add all joins that are needed for this sort
        List<IQuerySort> origSorts = originalQuery.getSorts();
        if (origSorts != null) {
            ArrayList<IQuerySort> sorts = new ArrayList<IQuerySort>();
            // find which sorts we will use and which tables are needed for that
            Set<BaseQueryTable> sortTables = new HashSet<BaseQueryTable>();
            for (IQuerySort isort : origSorts) {
                if (isort instanceof QuerySort) {
                    QuerySort sort = (QuerySort) isort;
                    IQuerySelectValue icolumn = sort.getColumn();
                    if (icolumn instanceof QueryColumn) {
                        QueryColumn column = (QueryColumn) icolumn;
                        sortTables.add(column.getTable());
                        sorts.add(sort);
                    }
                }
            // ignore custom sorts and sorts on other things than columns
            }
            // try to find the joins that are needed to satisfy tablesToResolve
            List<BaseQueryTable> tablesToResolve = new ArrayList<BaseQueryTable>();
            tablesToResolve.addAll(sortTables);
            List<BaseQueryTable> resolvedTables = new ArrayList<BaseQueryTable>();
            resolvedTables.add(sqlSelect.getTable());
            ArrayList<ISQLJoin> requiredJoins = new ArrayList<ISQLJoin>();
            boolean found = true;
            while (found && tablesToResolve.size() > 0) {
                BaseQueryTable table = tablesToResolve.remove(0);
                if (resolvedTables.contains(table)) {
                    continue;
                }
                found = false;
                ArrayList<ISQLJoin> joins = originalQuery.getJoins();
                if (joins != null) {
                    for (ISQLJoin ijoin : joins) {
                        if (!found && ijoin instanceof ISQLTableJoin) {
                            ISQLTableJoin join = (ISQLTableJoin) ijoin;
                            if (table.equals(join.getForeignTable())) {
                                // have to add this join
                                tablesToResolve.add(join.getPrimaryTable());
                                resolvedTables.add(table);
                                requiredJoins.add(join);
                                found = true;
                            }
                        }
                    }
                }
            }
            if (found) {
                sqlSelect.setJoins(requiredJoins);
                sqlSelect.setSorts(sorts);
            } else {
                // $NON-NLS-1$
                Debug.log("Could not restore order by in loadRecords(): couild not find all tables for sorting in " + originalQuery);
            }
        }
    } else {
        // create a query with the different parts as custom elements
        sqlSelect.clearJoins();
        // not needed when you have no joins and may conflict with order by
        sqlSelect.setDistinct(false);
        String tables;
        // $NON-NLS-1$
        int where_index = sql_lowercase.indexOf("where");
        if (where_index == -1) {
            tables = query.substring(from_index + 4, order_by_index);
            // no where-clause, remove the search condition (was set to FALSE in clear()
            sqlSelect.clearCondition(SQLGenerator.CONDITION_SEARCH);
        } else {
            tables = query.substring(from_index + 4, where_index);
            sqlSelect.setCondition(SQLGenerator.CONDITION_SEARCH, new CustomCondition(query.substring(where_index + 5, order_by_index).trim(), whereArgs));
        }
        // pick the foundset main table from the tables in the query (does not have to be the first one, we generate sql ourselves
        // that puts the main table at the end, see QueryGenerator)
        boolean foundTable = false;
        String mainTable = sheet.getTable().getName();
        StringBuilder otherTables = new StringBuilder();
        // $NON-NLS-1$
        StringTokenizer tok = new StringTokenizer(tables, ",");
        // default alias to table name
        String mainTableAlias = mainTable;
        // $NON-NLS-1$
        String whitespace = "\\s+";
        while (tok.hasMoreElements()) {
            String tableName = tok.nextToken().trim();
            String[] lcTableName = tableName.toLowerCase().split(whitespace);
            if (matchesMainTablename(lcTableName[0])) {
                foundTable = true;
                // either 'tabname', 'tabname aliasname' or 'tabname AS aliasname', when no alias is given, use table name as alias
                mainTableAlias = tableName.split(whitespace)[lcTableName.length - 1];
            } else {
                if (otherTables.length() > 0) {
                    // $NON-NLS-1$
                    otherTables.append(", ");
                }
                otherTables.append(tableName);
            }
        }
        // set table alias or unalias table when no alias was used
        BaseQueryTable qTable = sqlSelect.getTable();
        sqlSelect.relinkTable(sqlSelect.getTable(), new QueryTable(qTable.getName(), qTable.getDataSource(), qTable.getCatalogName(), qTable.getSchemaName(), mainTableAlias));
        if (otherTables.length() > 0) {
            // $NON-NLS-1$
            if (!foundTable)
                throw new IllegalArgumentException(fsm.getApplication().getI18NMessage("servoy.foundSet.query.error.firstTable"));
            // $NON-NLS-1$
            sqlSelect.addJoin(new QueryCustomJoin("foundset.loadbyquery", sqlSelect.getTable(), otherTables.toString()));
        }
        ArrayList<IQuerySort> sorts = new ArrayList<IQuerySort>();
        // $NON-NLS-1$
        Enumeration<Object> sortParts = new StringTokenizer(query.substring(order_by_index + 8), ",");
        while (sortParts.hasMoreElements()) {
            sorts.add(new QueryCustomSort(((String) sortParts.nextElement()).trim()));
        }
        sqlSelect.setSorts(sorts);
    }
    return loadByQuery(sqlSelect);
}

Example 4 with ISQLJoin

use of com.servoy.j2db.query.ISQLJoin in project servoy-client by Servoy.

the class SQLGenerator method getPKSelectSqlSelect.

/*
 * _____________________________________________________________ The methods below belong to this class
 */
// SQL pk(s) select for foundset,concatenating those strings will always deliver a executable SQL
// Note: removeUnusedJoins must be false when the resulting query is changed afterwards (like adding columns)
QuerySelect getPKSelectSqlSelect(IGlobalValueEntry provider, Table table, QuerySelect oldSQLQuery, List<IRecordInternal> findStates, boolean reduce, IDataSet omitPKs, List<SortColumn> orderByFields, boolean removeUnusedJoins) throws ServoyException {
    if (table == null) {
        throw new RepositoryException(ServoyException.InternalCodes.TABLE_NOT_FOUND);
    }
    QuerySelect retval;
    if (oldSQLQuery != null) {
        retval = deepClone(oldSQLQuery);
        retval.setGroupBy(null);
        // will be generated based on foundset sorting
        if (orderByFields != null)
            retval.clearSorts();
        // remove all servoy conditions, except filter, search and relation
        for (String conditionName : retval.getConditionNames()) {
            if (conditionName.startsWith(SERVOY_CONDITION_PREFIX) && !(CONDITION_FILTER.equals(conditionName) || CONDITION_SEARCH.equals(conditionName) || CONDITION_RELATION.equals(conditionName))) {
                retval.setCondition(conditionName, null);
            }
        }
    } else {
        retval = new QuerySelect(new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema()));
    }
    // Example:-select pk1,pk2 from tablename1 where ((fieldname1 like '%abcd%') or ((fieldname2 like '%xyz%')) (retrieve max 200 rows)
    ArrayList<IQuerySelectValue> pkQueryColumns = new ArrayList<IQuerySelectValue>(3);
    ArrayList<Column> pkColumns = new ArrayList<Column>(3);
    // getPrimaryKeys from table
    Iterator<Column> pks = table.getRowIdentColumns().iterator();
    // make select
    if (!pks.hasNext()) {
        throw new RepositoryException(ServoyException.InternalCodes.PRIMARY_KEY_NOT_FOUND, new Object[] { table.getName() });
    }
    while (pks.hasNext()) {
        Column column = pks.next();
        pkColumns.add(column);
        pkQueryColumns.add(column.queryColumn(retval.getTable()));
    }
    retval.setColumns(pkQueryColumns);
    if (omitPKs != null && omitPKs.getRowCount() != 0) {
        // omit is rebuild each time
        retval.setCondition(CONDITION_OMIT, createSetConditionFromPKs(IBaseSQLCondition.NOT_OPERATOR, pkQueryColumns.toArray(new QueryColumn[pkQueryColumns.size()]), pkColumns, omitPKs));
    } else if (oldSQLQuery != null) {
        retval.setCondition(CONDITION_OMIT, oldSQLQuery.getConditionClone(CONDITION_OMIT));
    }
    if (// new
    findStates != null && findStates.size() != 0) {
        ISQLCondition moreWhere = null;
        for (IRecordInternal obj : findStates) {
            if (obj instanceof FindState) {
                moreWhere = OrCondition.or(moreWhere, createConditionFromFindState((FindState) obj, retval, provider, pkQueryColumns));
            }
        }
        if (moreWhere != null) {
            if (reduce) {
                retval.addCondition(CONDITION_SEARCH, moreWhere);
            } else {
                retval.addConditionOr(CONDITION_SEARCH, moreWhere);
            }
            if (retval.getJoins() != null) {
                // check if the search condition has an or-condition
                final boolean[] hasOr = { false };
                retval.getCondition(CONDITION_SEARCH).acceptVisitor(new IVisitor() {

                    public Object visit(Object o) {
                        if (o instanceof OrCondition && ((OrCondition) o).getConditions().size() > 1) {
                            hasOr[0] = true;
                            return new VisitorResult(o, false);
                        }
                        return o;
                    }
                });
                if (hasOr[0]) {
                    // override join type to left outer join, a related OR-search should not make the result set smaller
                    for (ISQLJoin join : retval.getJoins()) {
                        if (join instanceof QueryJoin && ((QueryJoin) join).getJoinType() == IQueryConstants.INNER_JOIN) {
                            ((QueryJoin) join).setJoinType(IQueryConstants.LEFT_OUTER_JOIN);
                        }
                    }
                }
            }
        }
    }
    // make orderby
    if (orderByFields != null || retval.getSorts() == null) {
        List<SortColumn> orderBy = orderByFields == null ? new ArrayList<SortColumn>(3) : orderByFields;
        if (orderBy.size() == 0) {
            for (Column pkColumn : pkColumns) {
                orderBy.add(new SortColumn(pkColumn));
            }
        }
        addSorts(retval, retval.getTable(), provider, table, orderBy, true, false);
    }
    if (removeUnusedJoins) {
        // remove unneeded joins, some may have been added because of a previous sort and are no longer needed.
        retval.removeUnusedJoins(false);
    }
    // 1 do not remove sort or groupby test, will cause invalid queries
    // 1 this one causes error and can not be fixed,
    // 1 if (joinswherepart.length() != 0 && !sortIsRelated && groupbyKeyword == STRING_EMPTY && table.getPrimaryKeyCount() == 1)
    // 1 sql select distinct(s_contacts.contactsid) from s_contacts,s_companies where s_contacts.company_id = s_companies.company_id order by s_contacts.surname  ERROR:  For SELECT DISTINCT, ORDER BY expressions must appear in target list
    // retval may have set distinct and plainPKSelect flag based on previous sort columns, make sure to reset first
    retval.setDistinct(false);
    retval.setPlainPKSelect(false);
    if (// if joined pks comes back multiple times
    retval.getJoins() != null && retval.getColumns().size() == 1 && isDistinctAllowed(retval.getColumns(), retval.getSorts())) {
        retval.setDistinct(true);
    } else if (// plain pk select
    retval.getJoins() == null && retval.getColumns().size() == pkColumns.size()) {
        retval.setPlainPKSelect(true);
    }
    return retval;
}