Examples with IColumn com.servoy.j2db.persistence.IColumn used on opensource projects

Example 6 with IColumn

use of com.servoy.j2db.persistence.IColumn in project servoy-client by Servoy.

the class DataProviderEditor method getValue.

public Object getValue() {
    Object retval = null;
    Object[] selections = list.getSelectedValues();
    IDataProvider[] array = new IDataProvider[selections.length];
    for (int i = 0; i < selections.length; i++) {
        Object o = selections[i];
        if (o instanceof IDataProvider) {
            Object item = relationsComboBox.getSelectedItem();
            if (item instanceof Relation && o instanceof IColumn) {
                array[i] = new ColumnWrapper((IColumn) o, new Relation[] { ((Relation) item) });
            } else {
                array[i] = (IDataProvider) o;
            }
        }
    }
    if (selections.length == 1) {
        retval = array[0];
    } else {
        retval = array;
    }
    if (// if used to setDataProviderID
    returnValueAsString) {
        if (array != null && array.length != 0 && array[0] != null) {
            retval = array[0].getDataProviderID();
        } else {
            return null;
        }
    }
    relation = null;
    return retval;
}

Example 7 with IColumn

use of com.servoy.j2db.persistence.IColumn in project servoy-client by Servoy.

the class FoundSet method alldataproviders.

/**
 * Get all dataproviders of the foundset.
 *
 * @sample
 * var dataprovidersNames = %%prefix%%alldataproviders;
 * application.output("This foundset has " + dataprovidersNames.length + " data providers.")
 * for (var i=0; i<dataprovidersNames.length; i++)
 * 	application.output(dataprovidersNames[i]);
 *
 * @special
 */
@JSReadonlyProperty
public String[] alldataproviders() {
    List<String> al = new ArrayList<String>();
    Table table = (Table) getTable();
    if (table != null) {
        try {
            Iterator<Column> columnsIt = table.getColumnsSortedByName();
            while (columnsIt.hasNext()) {
                IColumn c = columnsIt.next();
                al.add(c.getDataProviderID());
            }
            Iterator<AggregateVariable> aggIt = fsm.getApplication().getFlattenedSolution().getAggregateVariables(table, true);
            while (aggIt.hasNext()) {
                AggregateVariable av = aggIt.next();
                al.add(av.getDataProviderID());
            }
            Iterator<ScriptCalculation> scriptIt = fsm.getApplication().getFlattenedSolution().getScriptCalculations(table, true);
            while (scriptIt.hasNext()) {
                ScriptCalculation sc = scriptIt.next();
                if (al.contains(sc.getDataProviderID()))
                    al.remove(sc.getDataProviderID());
                al.add(sc.getDataProviderID());
            }
        } catch (Exception ex) {
            Debug.error(ex);
        }
    }
    return al.toArray(new String[al.size()]);
}

Example 8 with IColumn

use of com.servoy.j2db.persistence.IColumn 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 9 with IColumn

use of com.servoy.j2db.persistence.IColumn in project servoy-client by Servoy.

the class SQLGenerator method addSorts.

public void addSorts(QuerySelect sqlSelect, BaseQueryTable selectTable, IGlobalValueEntry provider, ITable table, List<SortColumn> orderByFields, boolean includeRelated, boolean permanentJoins) throws RepositoryException {
    List<Column> unusedRowidentColumns = new ArrayList<Column>(table.getRowIdentColumns());
    for (int i = 0; orderByFields != null && i < orderByFields.size(); i++) {
        SortColumn sc = orderByFields.get(i);
        // can be column or aggregate
        IColumn column = sc.getColumn();
        if (column.getDataProviderType() == MEDIA && (column.getFlags() & (IDENT_COLUMNS | UUID_COLUMN)) == 0) {
            // skip cannot sort blob columns
            continue;
        }
        SortOptions sortOptions = application.getFoundSetManager().getSortOptions(sc.getColumn());
        Relation[] relations = sc.getRelations();
        // compare on server objects, relation.foreignServerName may be different in case of duplicates
        boolean doRelatedJoin = (includeRelated && relations != null);
        if (doRelatedJoin) {
            FlattenedSolution fs = application.getFlattenedSolution();
            for (Relation relation : relations) {
                if (relation.isMultiServer() && !fs.getTable(relation.getForeignDataSource()).getServerName().equals(table.getServerName())) {
                    doRelatedJoin = false;
                    break;
                }
            }
        }
        if (doRelatedJoin) // related sort, cannot join across multiple servers
        {
            BaseQueryTable primaryQtable = selectTable;
            BaseQueryTable foreignQtable = null;
            for (Relation relation : relations) {
                // join must be re-created as it is possible to have globals involved;
                // first remove, then create it
                ISQLTableJoin join = (ISQLTableJoin) sqlSelect.getJoin(primaryQtable, relation.getName());
                if (join != null)
                    sqlSelect.getJoins().remove(join);
                if (join == null) {
                    ITable foreignTable = application.getFlattenedSolution().getTable(relation.getForeignDataSource());
                    foreignQtable = new QueryTable(foreignTable.getSQLName(), foreignTable.getDataSource(), foreignTable.getCatalog(), foreignTable.getSchema());
                } else {
                    foreignQtable = join.getForeignTable();
                }
                sqlSelect.addJoin(createJoin(application.getFlattenedSolution(), relation, primaryQtable, foreignQtable, permanentJoins, provider));
                primaryQtable = foreignQtable;
            }
            IQuerySelectValue queryColumn;
            if (column instanceof Column) {
                queryColumn = ((Column) column).queryColumn(foreignQtable);
                unusedRowidentColumns.remove(column);
            } else if (column instanceof AggregateVariable) {
                AggregateVariable aggregate = (AggregateVariable) column;
                queryColumn = new QueryAggregate(aggregate.getType(), new QueryColumn(foreignQtable, -1, aggregate.getColumnNameToAggregate(), aggregate.getDataProviderType(), aggregate.getLength(), 0, null, aggregate.getFlags()), aggregate.getName());
                // there has to be a group-by clause for all selected fields
                List<IQuerySelectValue> columns = sqlSelect.getColumns();
                for (IQuerySelectValue selectVal : columns) {
                    List<IQuerySelectValue> groupBy = sqlSelect.getGroupBy();
                    if (selectVal instanceof QueryColumn && (groupBy == null || !groupBy.contains(selectVal))) {
                        sqlSelect.addGroupBy(selectVal);
                    }
                }
                // if the aggregate has not been selected yet, add it and skip it in the result
                QueryAggregate skippedAggregate = new QueryAggregate(aggregate.getType(), QueryAggregate.ALL, new QueryColumn(foreignQtable, -1, aggregate.getColumnNameToAggregate(), aggregate.getDataProviderType(), aggregate.getLength(), 0, null, aggregate.getFlags()), aggregate.getName(), null, true);
                if (!columns.contains(skippedAggregate)) {
                    sqlSelect.addColumn(skippedAggregate);
                }
            } else {
                // $NON-NLS-1$
                Debug.log("Skipping sort on unexpected related column type " + column.getClass());
                continue;
            }
            sqlSelect.addSort(new QuerySort(queryColumn, sc.getSortOrder() == ASCENDING, sortOptions));
        } else {
            // make sure an invalid sort is not possible
            if (column instanceof Column && column.getTable().getName().equals(table.getName())) {
                sqlSelect.addSort(new QuerySort(((Column) column).queryColumn(selectTable), sc.getSortOrder() == ASCENDING, sortOptions));
                unusedRowidentColumns.remove(column);
            } else {
                // $NON-NLS-1$ //$NON-NLS-2$
                Debug.log("Skipping sort on unrelated column " + column.getName() + '.' + column.getTable().getName() + " for table " + table.getName());
            }
        }
    }
    // Make sure pk is part of the sort, in case of non-unique sort columns, the sorted result may not be the same in each fetch
    if (enforcePkInSort) {
        for (Column column : unusedRowidentColumns) {
            SortOptions sortOptions = application.getFoundSetManager().getSortOptions(column);
            sqlSelect.addSort(new QuerySort(column.queryColumn(selectTable), true, sortOptions));
        }
    }
}

Example 10 with IColumn

use of com.servoy.j2db.persistence.IColumn in project servoy-client by Servoy.

the class SQLGenerator method createConditionFromFindState.

private ISQLCondition createConditionFromFindState(FindState s, QuerySelect sqlSelect, IGlobalValueEntry provider, List<IQuerySelectValue> pkQueryColumns) throws RepositoryException {
    ISQLCondition and = null;
    List<RelatedFindState> relatedFindStates = s.createFindStateJoins(sqlSelect, Collections.<IRelation>emptyList(), sqlSelect.getTable(), provider);
    for (int i = 0; relatedFindStates != null && i < relatedFindStates.size(); i++) {
        RelatedFindState rfs = relatedFindStates.get(i);
        FindState state = rfs.getFindState();
        BaseQueryTable columnTable = rfs.getPrimaryTable();
        SQLSheet sheet = state.getParentFoundSet().getSQLSheet();
        Table table = sheet.getTable();
        Iterator<Map.Entry<String, Object>> it = state.getColumnData().entrySet().iterator();
        while (it.hasNext()) {
            Map.Entry<String, Object> elem = it.next();
            final String dataProviderID = elem.getKey();
            Object raw = elem.getValue();
            if (raw == null)
                continue;
            int dataProviderType = -1;
            ConverterInfo columnConverterInfo = null;
            IColumnConverter columnConverter = null;
            IQuerySelectValue qCol = null;
            IColumn c = table.getColumn(dataProviderID);
            if (c != null) {
                dataProviderType = c.getDataProviderType();
                columnConverterInfo = sheet.getColumnConverterInfo(dataProviderID);
                if (columnConverterInfo != null) {
                    columnConverter = application.getFoundSetManager().getColumnConverterManager().getConverter(columnConverterInfo.converterName);
                    if (columnConverter instanceof ITypedColumnConverter) {
                        int convType = ((ITypedColumnConverter) columnConverter).getToObjectType(columnConverterInfo.props);
                        if (convType != Integer.MAX_VALUE) {
                            dataProviderType = mapToDefaultType(convType);
                        }
                    }
                }
                // a column
                qCol = ((Column) c).queryColumn(columnTable);
            } else {
                // not a column, check for aggregates
                Iterator<AggregateVariable> aggregateVariables = application.getFlattenedSolution().getAggregateVariables(sheet.getTable(), false);
                while (c == null && aggregateVariables.hasNext()) {
                    AggregateVariable agg = aggregateVariables.next();
                    if (dataProviderID.equals(agg.getDataProviderID())) {
                        // found aggregate
                        c = agg;
                    }
                }
                if (c != null) {
                    dataProviderType = c.getDataProviderType();
                    Map<String, QuerySelect> aggregates = sheet.getAggregates();
                    if (aggregates != null) {
                        QuerySelect aggregateSelect = aggregates.get(dataProviderID);
                        if (aggregateSelect != null) {
                            qCol = ((List<IQuerySelectValue>) AbstractBaseQuery.relinkTable(aggregateSelect.getTable(), columnTable, aggregateSelect.getColumnsClone())).get(0);
                        }
                    }
                }
            }
            if (qCol == null) {
                // not a column and not an aggregate
                // $NON-NLS-1$ //$NON-NLS-2$
                Debug.log("Ignoring search on unknown/unsupported data provider '" + dataProviderID + "'");
                continue;
            }
            ParsedFormat format = state.getFormat(dataProviderID);
            String formatString = null;
            if (format != null) {
                formatString = format.getEditFormat();
                if (formatString == null) {
                    formatString = format.getDisplayFormat();
                }
            }
            if (Utils.stringIsEmpty(formatString)) {
                formatString = TagResolver.getDefaultFormatForType(application, dataProviderType);
            }
            ISQLCondition or = null;
            if (raw.getClass().isArray()) {
                int length = Array.getLength(raw);
                Object[] elements = new Object[length];
                for (int e = 0; e < length; e++) {
                    Object obj = Array.get(raw, e);
                    if (obj instanceof Wrapper) {
                        obj = ((Wrapper) obj).unwrap();
                    }
                    // Have to use getAsRightType twice here, once to parse using format (getAsType(dataProviderType, formatString))
                    // and once to convert for query (getAsType(c.getDataProviderType(), null))
                    Object converted = convertFromObject(application, columnConverter, columnConverterInfo, dataProviderID, c.getDataProviderType(), Column.getAsRightType(dataProviderType, c.getFlags(), obj, formatString, c.getLength(), null, false, false), false);
                    elements[e] = Column.getAsRightType(c.getDataProviderType(), c.getFlags(), converted, null, c.getLength(), null, false, false);
                }
                // where qCol in (e1, e2, ..., en)
                or = new SetCondition(IBaseSQLCondition.EQUALS_OPERATOR, new IQuerySelectValue[] { qCol }, new Object[][] { elements }, true);
            } else {
                final IColumnConverter fColumnConverter = columnConverter;
                final ConverterInfo fColumnConverterInfo = columnConverterInfo;
                final int fDataProviderType = c.getDataProviderType();
                or = (ISQLCondition) BaseSQLGenerator.parseFindExpression(QueryFactory.INSTANCE, raw, qCol, columnTable, dataProviderType, formatString, c, rfs.getRelations().size() > 0 && relatedNullSearchAddPkCondition(), new IValueConverter() {

                    @Override
                    public Object convertFromObject(Object value) {
                        return SQLGenerator.convertFromObject(application, fColumnConverter, fColumnConverterInfo, dataProviderID, fDataProviderType, value, false);
                    }
                }, new ITypeConverter() {

                    @Override
                    public Object getAsRightType(int type, int flags, Object obj, int l, boolean throwOnFail) {
                        return Column.getAsRightType(type, flags, obj, l, throwOnFail, false);
                    }

                    @Override
                    public Object getAsRightType(int type, int flags, Object obj, String format, int l, boolean throwOnFail) {
                        return Column.getAsRightType(type, flags, obj, format, l, null, throwOnFail, false);
                    }
                }, table.getRowIdentColumns().get(0), Debug.LOGGER);
            }
            if (or != null) {
                ISQLCondition condition;
                if (c instanceof AggregateVariable) {
                    condition = createExistsCondition(application.getFlattenedSolution(), sqlSelect, or, rfs.getRelations(), columnTable, provider, pkQueryColumns.toArray(new QueryColumn[pkQueryColumns.size()]));
                } else {
                    condition = or;
                }
                and = AndCondition.and(and, condition);
            }
        }
    }
    return and;
}