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

Example 26 with IQuerySelectValue

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

the class RowManager method getBlob.

/**
 * @param row
 * @return
 */
Blob getBlob(Row row, int columnIndex) throws Exception {
    QuerySelect blobSelect = new QuerySelect(new QueryTable(sheet.getTable().getSQLName(), sheet.getTable().getDataSource(), sheet.getTable().getCatalog(), sheet.getTable().getSchema()));
    String blobColumnName = sheet.getColumnNames()[columnIndex];
    Column blobColumn = sheet.getTable().getColumn(blobColumnName);
    blobSelect.addColumn(new QueryColumn(blobSelect.getTable(), blobColumn.getID(), blobColumn.getSQLName(), blobColumn.getType(), blobColumn.getLength(), blobColumn.getScale(), blobColumn.getNativeTypename(), blobColumn.getFlags(), false));
    String[] pkColumnNames = sheet.getPKColumnDataProvidersAsArray();
    IQuerySelectValue[] pkQuerycolumns = new IQuerySelectValue[pkColumnNames.length];
    Object[][] pkValues = new Object[pkColumnNames.length][];
    Object[] pk = row.getPK();
    for (int k = 0; k < pkValues.length; k++) {
        Column pkcolumn = sheet.getTable().getColumn(pkColumnNames[k]);
        pkQuerycolumns[k] = new QueryColumn(blobSelect.getTable(), pkcolumn.getID(), pkcolumn.getSQLName(), pkcolumn.getType(), pkcolumn.getLength(), pkcolumn.getScale(), pkcolumn.getNativeTypename(), pkcolumn.getFlags(), false);
        pkValues[k] = new Object[] { pk[k] };
    }
    // $NON-NLS-1$
    blobSelect.addCondition("blobselect", new SetCondition(IBaseSQLCondition.EQUALS_OPERATOR, pkQuerycolumns, pkValues, true));
    String serverName = sheet.getServerName();
    String transaction_id = null;
    GlobalTransaction gt = fsm.getGlobalTransaction();
    if (gt != null) {
        transaction_id = gt.getTransactionID(sheet.getServerName());
    }
    return fsm.getApplication().getDataServer().getBlob(fsm.getApplication().getClientID(), serverName, blobSelect, fsm.getTableFilterParams(sheet.getServerName(), blobSelect), transaction_id);
}

Example 27 with IQuerySelectValue

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

the class JSDatabaseManager method js_convertToDataSet.

/**
 * @clonedesc js_convertToDataSet(IFoundSetInternal)
 *
 * @sampleas js_convertToDataSet(IFoundSetInternal)
 *
 * @param foundset The foundset to be converted.
 * @param dataproviderNames Array with column names.
 *
 * @return JSDataSet with the data.
 */
public JSDataSet js_convertToDataSet(IFoundSetInternal foundset, String[] dataproviderNames) throws RepositoryException {
    if (foundset == null) {
        return null;
    }
    // $NON-NLS-1$
    String[] dpnames = { "id" };
    ColumnType[] dptypes = { ColumnType.getInstance(IColumnTypes.INTEGER, Integer.MAX_VALUE, 0) };
    List<Object[]> lst = new ArrayList<Object[]>();
    FoundSet fs = (FoundSet) foundset;
    if (fs.getTable() != null) {
        if (dataproviderNames != null) {
            dpnames = dataproviderNames;
        } else {
            dpnames = fs.getSQLSheet().getPKColumnDataProvidersAsArray();
        }
        FoundSetManager fsm = (FoundSetManager) application.getFoundSetManager();
        boolean getInOneQuery = !fs.isInFindMode() && (fs.hadMoreRows() || fs.getSize() > fsm.config.pkChunkSize()) && !fsm.getEditRecordList().hasEditedRecords(fs);
        dptypes = new ColumnType[dpnames.length];
        Table table = fs.getSQLSheet().getTable();
        Map<String, Column> columnMap = new HashMap<String, Column>();
        for (int i = 0; i < dpnames.length; i++) {
            IDataProvider dp = application.getFlattenedSolution().getDataProviderForTable(table, dpnames[i]);
            dptypes[i] = dp == null ? ColumnType.getInstance(0, 0, 0) : ColumnType.getInstance(dp instanceof Column ? ((Column) dp).getType() : dp.getDataProviderType(), dp.getLength(), dp instanceof Column ? ((Column) dp).getScale() : 0);
            if (getInOneQuery) {
                // only columns and data we can get from the foundset (calculations only when stored)
                if (dp instanceof Column) {
                    columnMap.put(dpnames[i], (Column) dp);
                    // Blobs require special resultset handling
                    getInOneQuery = !SQLGenerator.isBlobColumn((Column) dp);
                } else {
                    // aggregates, globals
                    getInOneQuery = fs.containsDataProvider(dpnames[i]);
                }
            }
        }
        if (getInOneQuery && columnMap.size() > 0) {
            // large foundset, query the columns in 1 go
            QuerySelect sqlSelect = AbstractBaseQuery.deepClone(fs.getQuerySelectForReading());
            ArrayList<IQuerySelectValue> cols = new ArrayList<IQuerySelectValue>(columnMap.size());
            ArrayList<String> distinctColumns = new ArrayList<String>(columnMap.size());
            for (String dpname : dpnames) {
                Column column = columnMap.get(dpname);
                if (column != null && !distinctColumns.contains(dpname)) {
                    distinctColumns.add(dpname);
                    cols.add(column.queryColumn(sqlSelect.getTable()));
                }
            }
            boolean hasJoins = sqlSelect.getJoins() != null;
            if (hasJoins) {
                // add pk columns so distinct-in-memory can be used
                List<Column> rowIdentColumns = ((Table) fs.getTable()).getRowIdentColumns();
                for (Column column : rowIdentColumns) {
                    if (!columnMap.containsKey(column.getDataProviderID())) {
                        cols.add(column.queryColumn(sqlSelect.getTable()));
                    }
                }
            }
            sqlSelect.setColumns(cols);
            try {
                SQLSheet sheet = fs.getSQLSheet();
                IConverterManager<IColumnConverter> columnConverterManager = ((FoundSetManager) fs.getFoundSetManager()).getColumnConverterManager();
                SQLStatement trackingInfo = null;
                if (fsm.getEditRecordList().hasAccess(sheet.getTable(), IRepository.TRACKING_VIEWS)) {
                    trackingInfo = new SQLStatement(ISQLActionTypes.SELECT_ACTION, sheet.getServerName(), sheet.getTable().getName(), null, null);
                    trackingInfo.setTrackingData(sqlSelect.getColumnNames(), new Object[][] {}, new Object[][] {}, fsm.getApplication().getUserUID(), fsm.getTrackingInfo(), fsm.getApplication().getClientID());
                }
                IDataSet dataSet = fsm.getDataServer().performQuery(fsm.getApplication().getClientID(), sheet.getServerName(), fsm.getTransactionID(sheet), sqlSelect, null, fsm.getTableFilterParams(sheet.getServerName(), sqlSelect), hasJoins, 0, -1, IDataServer.FOUNDSET_LOAD_QUERY, trackingInfo);
                lst = new ArrayList<Object[]>(dataSet.getRowCount());
                for (int i = 0; i < dataSet.getRowCount(); i++) {
                    Object[] row = new Object[dpnames.length];
                    // may contain more data: pk columns for distinct-in-memory
                    Object[] dataseRow = dataSet.getRow(i);
                    for (int j = 0; j < dpnames.length; j++) {
                        Column column = columnMap.get(dpnames[j]);
                        if (column == null) {
                            // fs.containsDataProvider returned true for this dpname
                            row[j] = fs.getDataProviderValue(dpnames[j]);
                        } else {
                            row[j] = sheet.convertValueToObject(dataseRow[distinctColumns.indexOf(dpnames[j])], sheet.getColumnIndex(dpnames[j]), columnConverterManager);
                        }
                    }
                    lst.add(row);
                }
            } catch (RepositoryException e) {
                throw e;
            } catch (Exception e) {
                Debug.error(e);
                throw new RepositoryException(e.getMessage());
            }
        } else {
            // loop over the records
            for (int i = 0; i < fs.getSize(); i++) {
                IRecordInternal record = fs.getRecord(i);
                Object[] pk = new Object[dpnames.length];
                for (int j = 0; j < dpnames.length; j++) {
                    pk[j] = record.getValue(dpnames[j]);
                }
                lst.add(pk);
            }
        }
    }
    return new JSDataSet(application, BufferedDataSetInternal.createBufferedDataSet(dpnames, dptypes, lst, false));
}

Example 28 with IQuerySelectValue

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

the class LookupListModel method addOrCondition.

protected void addOrCondition(String dataProviderId, BaseQueryTable qTable, String likeValue, String[] displayValues, OrCondition overallOr) {
    IQuerySelectValue querySelect = DBValueList.getQuerySelectValue(table, qTable, dataProviderId);
    if (displayValues != null) {
        for (String displayValue : displayValues) {
            overallOr.addCondition(SQLGenerator.createLikeCompareCondition(querySelect, table.getColumnType(dataProviderId), displayValue));
        }
    }
    // also just add the complete value, for the possibility that it was a value with a separator.
    overallOr.addCondition(SQLGenerator.createLikeCompareCondition(querySelect, table.getColumnType(dataProviderId), likeValue));
}

Example 29 with IQuerySelectValue

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

the class DBValueList method createValuelistQuery.

public static QuerySelect createValuelistQuery(IServiceProvider application, ValueList valueList, ITable table) {
    if (table == null)
        return null;
    FoundSetManager foundSetManager = ((FoundSetManager) application.getFoundSetManager());
    // do not add the default pk-sort, only add real configured sort columns on value list
    List<SortColumn> sortColumns = valueList.getSortOptions() == null ? null : foundSetManager.getSortColumns(table, valueList.getSortOptions());
    int showValues = valueList.getShowDataProviders();
    int returnValues = valueList.getReturnDataProviders();
    int total = (showValues | returnValues);
    QuerySelect select = new QuerySelect(new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema()));
    ArrayList<IQuerySort> orderColumns = new ArrayList<IQuerySort>();
    ArrayList<IQuerySelectValue> columns = new ArrayList<IQuerySelectValue>();
    boolean useDefinedSort = sortColumns != null && sortColumns.size() > 0;
    if (useDefinedSort) {
        for (SortColumn sc : sortColumns) {
            SortOptions sortoptions = application.getFoundSetManager().getSortOptions(sc.getColumn());
            orderColumns.add(new QuerySort(getQuerySelectValue(table, select.getTable(), sc.getDataProviderID()), sc.getSortOrder() == SortColumn.ASCENDING, sortoptions));
        }
    }
    if ((total & 1) != 0) {
        IQuerySelectValue cSQLName = getQuerySelectValue(table, select.getTable(), valueList.getDataProviderID1());
        columns.add(cSQLName);
        if ((showValues & 1) != 0 && !useDefinedSort) {
            SortOptions sortoptions = application.getFoundSetManager().getSortOptions(table.getColumn(valueList.getDataProviderID1()));
            orderColumns.add(new QuerySort(cSQLName, true, sortoptions));
        }
    }
    if ((total & 2) != 0) {
        IQuerySelectValue cSQLName = getQuerySelectValue(table, select.getTable(), valueList.getDataProviderID2());
        columns.add(cSQLName);
        if ((showValues & 2) != 0 && !useDefinedSort) {
            SortOptions sortoptions = application.getFoundSetManager().getSortOptions(table.getColumn(valueList.getDataProviderID2()));
            orderColumns.add(new QuerySort(cSQLName, true, sortoptions));
        }
    }
    if ((total & 4) != 0) {
        IQuerySelectValue cSQLName = getQuerySelectValue(table, select.getTable(), valueList.getDataProviderID3());
        columns.add(cSQLName);
        if ((showValues & 4) != 0 && !useDefinedSort) {
            SortOptions sortoptions = application.getFoundSetManager().getSortOptions(table.getColumn(valueList.getDataProviderID3()));
            orderColumns.add(new QuerySort(cSQLName, true, sortoptions));
        }
    }
    // check if we can still use distinct
    select.setDistinct(SQLGenerator.isDistinctAllowed(columns, orderColumns));
    select.setColumns(columns);
    select.setSorts(orderColumns);
    return select;
}

Example 30 with IQuerySelectValue

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

the class JSDatabaseManager method convertFoundSet.

public FoundSet convertFoundSet(Object foundset, Object related) throws ServoyException {
    checkAuthorized();
    if (foundset instanceof FoundSet && ((FoundSet) foundset).getTable() != null) {
        FoundSet fs_old = (FoundSet) foundset;
        try {
            String relationName;
            if (related instanceof RelatedFoundSet) {
                relationName = ((RelatedFoundSet) related).getRelationName();
            } else if (related instanceof String) {
                relationName = (String) related;
            } else {
                // $NON-NLS-1$
                Debug.warn("convertFoundSet: invalid argument " + related);
                return null;
            }
            Relation relation = application.getFlattenedSolution().getRelation(relationName);
            if (relation == null || relation.isMultiServer() || fs_old.getTable() == null || !fs_old.getTable().equals(application.getFlattenedSolution().getTable(relation.getPrimaryDataSource()))) {
                // $NON-NLS-1$
                Debug.warn("convertFoundSet: cannot use relation " + relationName);
                return null;
            }
            ITable ft = application.getFlattenedSolution().getTable(relation.getForeignDataSource());
            FoundSet fs_new = (FoundSet) application.getFoundSetManager().getNewFoundSet(ft, null, application.getFoundSetManager().getDefaultPKSortColumns(ft.getDataSource()));
            QuerySelect sql = fs_old.getPksAndRecords().getQuerySelectForModification();
            SQLSheet sheet_new = fs_old.getSQLSheet().getRelatedSheet(relation, ((FoundSetManager) application.getFoundSetManager()).getSQLGenerator());
            if (sheet_new != null) {
                BaseQueryTable oldTable = sql.getTable();
                ISQLTableJoin join = (ISQLTableJoin) sql.getJoin(oldTable, relation.getName());
                if (join == null) {
                    join = SQLGenerator.createJoin(application.getFlattenedSolution(), relation, oldTable, new QueryTable(ft.getSQLName(), ft.getDataSource(), ft.getCatalog(), ft.getSchema()), true, fs_old);
                    sql.addJoin(join);
                }
                BaseQueryTable mainTable = join.getForeignTable();
                // invert the join
                sql.setTable(mainTable);
                // $NON-NLS-1$
                join.invert("INVERTED." + join.getName());
                // set the columns to be the PKs from the related table
                ArrayList<IQuerySelectValue> pkColumns = new ArrayList<IQuerySelectValue>();
                Iterator<Column> pks = sheet_new.getTable().getRowIdentColumns().iterator();
                while (pks.hasNext()) {
                    Column column = pks.next();
                    pkColumns.add(column.queryColumn(mainTable));
                }
                sql.setColumns(pkColumns);
                // sorting will be on the original columns, when distinct is set, this will conflict with the related pk columns
                sql.setDistinct(false);
                fs_new.setSQLSelect(sql);
                return fs_new;
            }
        } catch (Exception e) {
            Debug.error(e);
        }
    }
    return null;
}