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

Example 11 with IQuerySelectValue

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

the class SQLGenerator method createUpdateLockSelect.

public static QuerySelect createUpdateLockSelect(Table table, Object[][] pkValues, boolean lockInDb) {
    QuerySelect lockSelect = new QuerySelect(new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema()));
    if (lockInDb)
        lockSelect.setLockMode(ISQLSelect.LOCK_MODE_LOCK_NOWAIT);
    LinkedHashMap<Column, QueryColumn> allQueryColumns = new LinkedHashMap<>();
    for (Column column : table.getColumns()) {
        allQueryColumns.put(column, column.queryColumn(lockSelect.getTable()));
    }
    lockSelect.setColumns(new ArrayList<IQuerySelectValue>(allQueryColumns.values()));
    // get the pk columns, make sure the order is in pk-order (alphabetical)
    ArrayList<QueryColumn> pkQueryColumns = new ArrayList<>();
    for (Column pkColumn : table.getRowIdentColumns()) {
        pkQueryColumns.add(allQueryColumns.get(pkColumn));
    }
    // values is an array as wide as the columns, each element consists of the values for that column
    Object[][] values = new Object[pkQueryColumns.size()][];
    for (int k = 0; k < pkQueryColumns.size(); k++) {
        values[k] = new Object[pkValues.length];
        for (int r = 0; r < pkValues.length; r++) {
            values[k][r] = pkValues[r][k];
        }
    }
    lockSelect.setCondition(CONDITION_LOCK, new SetCondition(IBaseSQLCondition.EQUALS_OPERATOR, pkQueryColumns.toArray(new QueryColumn[pkQueryColumns.size()]), values, true));
    return lockSelect;
}

Example 12 with IQuerySelectValue

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

the class SQLGenerator method getEmptyDataSetForDummyQuery.

/**
 * check if the query is will never return any rows, in that case just return an empty dataset.
 */
public static IDataSet getEmptyDataSetForDummyQuery(ISQLSelect sqlSelect) {
    if (sqlSelect instanceof QuerySelect && ((QuerySelect) sqlSelect).getCondition(CONDITION_SEARCH) != null) {
        // all named conditions in QuerySelecta are AND-ed, if one always results to false, skip the query
        for (IBaseSQLCondition condition : ((QuerySelect) sqlSelect).getCondition(CONDITION_SEARCH).getConditions()) {
            boolean skipQuery = false;
            if (condition instanceof SetCondition && ((SetCondition) condition).isAndCondition()) {
                // check for EQUALS_OPERATOR
                int ncols = ((SetCondition) condition).getKeys().length;
                int[] operators = ((SetCondition) condition).getOperators();
                boolean eqop = true;
                for (int i = 0; i < ncols; i++) {
                    if (operators[i] != IBaseSQLCondition.EQUALS_OPERATOR) {
                        eqop = false;
                    }
                }
                if (eqop) {
                    Object value = ((SetCondition) condition).getValues();
                    if (value instanceof Placeholder) {
                        Object phval = ((Placeholder) value).getValue();
                        // cleared foundset
                        skipQuery = phval instanceof DynamicPkValuesArray && ((DynamicPkValuesArray) phval).getPKs().getRowCount() == 0;
                    } else if (value instanceof Object[][]) {
                        skipQuery = ((Object[][]) value).length == 0 || ((Object[][]) value)[0].length == 0;
                    }
                }
            }
            if (skipQuery) {
                // no need to query, dummy condition (where 1=2) here
                List<IQuerySelectValue> columns = ((QuerySelect) sqlSelect).getColumns();
                String[] columnNames = new String[columns.size()];
                ColumnType[] columnTypes = new ColumnType[columns.size()];
                for (int i = 0; i < columns.size(); i++) {
                    IQuerySelectValue col = columns.get(i);
                    columnNames[i] = col.getAliasOrName();
                    BaseColumnType columnType = col.getColumnType();
                    columnTypes[i] = columnType == null ? ColumnType.getInstance(Types.OTHER, 0, 0) : ColumnType.getInstance(columnType.getSqlType(), columnType.getLength(), columnType.getScale());
                }
                return BufferedDataSetInternal.createBufferedDataSet(columnNames, columnTypes, new SafeArrayList<Object[]>(0), false);
            }
        }
    }
    // query needs to be run
    return null;
}

Example 13 with IQuerySelectValue

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

the class LookupListModel method getSortColumnsForQuery.

private ArrayList<IQuerySort> getSortColumnsForQuery(QuerySelect query) {
    ArrayList<IQuerySort> sortColumnsForQuery = null;
    if (defaultSort != null && defaultSort.size() > 0) {
        sortColumnsForQuery = new ArrayList<IQuerySort>();
        ArrayList<IQuerySelectValue> queryColumns = query.getColumns();
        for (SortColumn sortColumn : defaultSort) {
            for (IQuerySelectValue column : queryColumns) {
                if (sortColumn.getName().trim().equalsIgnoreCase(column.getColumn().getName().trim())) {
                    sortColumnsForQuery.add(new QuerySort(column, sortColumn.getSortOrder() == SortColumn.ASCENDING, application.getFoundSetManager().getSortOptions(sortColumn.getColumn())));
                    break;
                }
            }
        }
        if (sortColumnsForQuery.size() == 0)
            sortColumnsForQuery = null;
    }
    return sortColumnsForQuery;
}

Example 14 with IQuerySelectValue

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

the class JSDatabaseManager method js_getFoundSetDataProviderAsArray.

/**
 * This method is deprecated, use databaseManager.convertToDataSet(foundset, pkNames) instead.
 *
 * @sample
 * var dataSet = databaseManager.convertToDataSet(foundset,['order_id']);
 *
 * @deprecated As of release 6.0, replaced by {@link #convertToDataSet(Object[])}.
 *
 * @param foundset The foundset
 * @param dataprovider The dataprovider for the values of the array.
 *
 * @return An Array with the column values.
 */
@Deprecated
public Object[] js_getFoundSetDataProviderAsArray(Object foundset, String dataprovider) throws ServoyException {
    checkAuthorized();
    if (foundset instanceof FoundSet && ((FoundSet) foundset).getSQLSheet().getTable() != null) {
        FoundSet fs = (FoundSet) foundset;
        FoundSetManager fsm = (FoundSetManager) application.getFoundSetManager();
        SQLSheet sheet = fs.getSQLSheet();
        Column column = sheet.getTable().getColumn(dataprovider);
        if (column != null) {
            IDataSet dataSet = null;
            if ((fs.hadMoreRows() || fs.getSize() > fsm.config.pkChunkSize()) && !fsm.getEditRecordList().hasEditedRecords(fs)) {
                // large foundset, query the column in 1 go
                QuerySelect sqlSelect = AbstractBaseQuery.deepClone(fs.getQuerySelectForReading());
                ArrayList<IQuerySelectValue> cols = new ArrayList<IQuerySelectValue>(1);
                cols.add(column.queryColumn(sqlSelect.getTable()));
                sqlSelect.setColumns(cols);
                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(new String[] { column.getSQLName() }, new Object[][] {}, new Object[][] {}, fsm.getApplication().getUserUID(), fsm.getTrackingInfo(), fsm.getApplication().getClientID());
                }
                try {
                    dataSet = fsm.getDataServer().performQuery(fsm.getApplication().getClientID(), sheet.getServerName(), fsm.getTransactionID(sheet), sqlSelect, null, fsm.getTableFilterParams(sheet.getServerName(), sqlSelect), false, 0, -1, IDataServer.FOUNDSET_LOAD_QUERY, trackingInfo);
                } catch (RemoteException e) {
                    Debug.error(e);
                    return new Object[0];
                } catch (ServoyException e) {
                    Debug.error(e);
                    return new Object[0];
                }
            } else {
                // small foundset or there are edited records
                List<Column> pks = fs.getSQLSheet().getTable().getRowIdentColumns();
                if (// if is pk optimize
                pks.size() == 1 && pks.get(0).equals(column)) {
                    PksAndRecordsHolder pksAndRecordsCopy;
                    PKDataSet pkds;
                    boolean queryForMore;
                    int rowCount;
                    synchronized (fs.getPksAndRecords()) {
                        pksAndRecordsCopy = fs.getPksAndRecords().shallowCopy();
                        pkds = pksAndRecordsCopy.getPks();
                        queryForMore = pkds == null || pkds.hadMoreRows();
                        rowCount = pkds == null ? 0 : pkds.getRowCount();
                    }
                    if (queryForMore) {
                        fs.queryForMorePKs(pksAndRecordsCopy, rowCount, -1, true);
                    }
                    dataSet = pkds;
                }
            }
            if (dataSet != null) {
                Object[] retval = new Object[dataSet.getRowCount()];
                for (int i = 0; i < retval.length; i++) {
                    Object[] dataSetRow = dataSet.getRow(i);
                    if (dataSetRow == null) {
                        Debug.warn(// $NON-NLS-1$//$NON-NLS-2$
                        "js_getFoundSetDataProviderAsArray - null row at index: " + i + " when getting dataprovider: " + dataprovider + " from foundset: " + // $NON-NLS-1$
                        foundset);
                        retval[i] = null;
                    } else {
                        Object value = dataSetRow[0];
                        if (column.hasFlag(IBaseColumn.UUID_COLUMN)) {
                            // this is a UUID column, first convert to UUID (could be string or byte array (media)) - so we can get/use it as a valid uuid string
                            value = Utils.getAsUUID(value, false);
                        }
                        retval[i] = value;
                    }
                }
                return retval;
            }
        }
        // cannot het the data via a dataset, use the records (could be slow)
        List<Object> lst = new ArrayList<Object>();
        for (int i = 0; i < fs.getSize(); i++) {
            IRecordInternal r = fs.getRecord(i);
            Object value = r.getValue(dataprovider);
            if (value instanceof Date) {
                value = new Date(((Date) value).getTime());
            }
            lst.add(value);
        }
        return lst.toArray();
    }
    return new Object[0];
}

Example 15 with IQuerySelectValue

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

the class SQLGenerator method createRelatedCondition.

public static ISQLCondition createRelatedCondition(IServiceProvider app, Relation relation, QueryTable foreignTable) throws RepositoryException {
    IDataProvider[] primary = relation.getPrimaryDataProviders(app.getFlattenedSolution());
    Column[] foreign = relation.getForeignColumns(app.getFlattenedSolution());
    int[] operators = relation.getOperators();
    IQuerySelectValue[] keys = new IQuerySelectValue[primary.length];
    int[] swapped = new int[primary.length];
    for (int x = 0; x < primary.length; x++) {
        // need all keys as columns on the left side......
        int operator = RelationItem.swapOperator(operators[x]);
        if (operator == -1) {
            throw new RepositoryException("Cannot swap relation operator for relation " + relation.getName());
        }
        // column = ? construct
        IQuerySelectValue key = foreign[x].queryColumn(foreignTable);
        // When we have a text and non-text column we can cast the non-text column to string
        int primaryType = primary[x].getDataProviderType();
        int foreignType = mapToDefaultType(key.getColumn().getColumnType());
        if (!"uuid".equalsIgnoreCase(key.getColumn().getNativeTypename()) && foreignType == IColumnTypes.TEXT && primaryType != IColumnTypes.TEXT && primaryType != 0) {
            // key is text, value is non-text, cast the value to text when we supply it
            operator |= IBaseSQLCondition.CAST_TO_MODIFIER;
        } else if (primaryType == IColumnTypes.TEXT && foreignType != IColumnTypes.TEXT) {
            // value is text, key is non-text, cast the key to text
            key = new QueryFunction(cast, new IQuerySelectValue[] { key, new QueryColumnValue(IQueryConstants.TYPE_STRING, null, true) }, null);
        }
        keys[x] = key;
        swapped[x] = operator;
    }
    return new SetCondition(swapped, keys, new Placeholder(createRelationKeyPlaceholderKey(foreignTable, relation.getName())), true);
}