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

Example 1 with ISQLSelect

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

the class TestNGClient method createDataServer.

@Override
protected IDataServer createDataServer() {
    return new IDataServer() {

        private final HashMap<String, IDataSet> dataSetMap = new HashMap<String, IDataSet>();

        @Override
        public void setServerMaintenanceMode(boolean maintenanceMode) throws RemoteException {
        }

        @Override
        public void logMessage(String msg) throws RemoteException {
        }

        @Override
        public boolean isInServerMaintenanceMode() throws RemoteException {
            return false;
        }

        @Override
        public boolean releaseLocks(String client_id, String server_name, String table_name, Set<Object> pkhashkeys) throws RemoteException, RepositoryException {
            return false;
        }

        @Override
        public IDataSet acquireLocks(String client_id, String server_name, String table_name, Set<Object> pkhashkeys, QuerySelect lockSelect, String transaction_id, ArrayList<TableFilter> filters, int chunkSize) throws RemoteException, RepositoryException {
            return null;
        }

        @Override
        public String startTransaction(String clientId, String server_name) throws RepositoryException, RemoteException {
            return null;
        }

        @Override
        public Object[] performUpdates(String clientId, ISQLStatement[] statements) throws ServoyException, RemoteException {
            return statements;
        }

        @Override
        public IDataSet[] performQuery(String client_id, String server_name, String transaction_id, QueryData[] array) throws ServoyException, RemoteException {
            if (array.length > 0) {
                String ds = array[0].getSqlSelect().getTable().getDataSource();
                if ("mem:relatedtest".equals(ds)) {
                    IDataSet set = dataSetMap.get(ds);
                    IDataSet[] returnDataSet = new IDataSet[array.length];
                    for (int i = 0; i < array.length; i++) {
                        returnDataSet[i] = new BufferedDataSet();
                        for (int k = 0; k < set.getRowCount(); k++) {
                            Object[][] value = (Object[][]) ((Placeholder) ((SetCondition) ((QuerySelect) array[i].getSqlSelect()).getConditions().values().iterator().next().getConditions().get(0)).getValues()).getValue();
                            if (set.getRow(k)[1].equals(value[0][0])) {
                                returnDataSet[i].addRow(new Object[] { set.getRow(k)[0], set.getRow(k)[1], set.getRow(k)[2], set.getRow(k)[3] });
                            }
                        }
                    }
                    return returnDataSet;
                }
            }
            return null;
        }

        @Override
        public IDataSet performQuery(String client_id, String server_name, String driverTableName, String transaction_id, String sql, Object[] questiondata, int startRow, int rowsToRetrieve, boolean updateIdleTimestamp) throws ServoyException, RemoteException {
            // don't know the
            return dataSetMap.values().iterator().next();
        // datasource,
        // just return
        // the first
        // dataset
        }

        @Override
        public IDataSet performQuery(String client_id, String server_name, String transaction_id, ISQLSelect sqlSelect, ColumnType[] resultTypes, ArrayList<TableFilter> filters, boolean distinctInMemory, int startRow, int rowsToRetrieve, boolean updateIdleTimestamp) throws ServoyException, RemoteException {
            return dataSetMap.get(sqlSelect.getTable().getDataSource());
        }

        @Override
        public IDataSet performQuery(String client_id, String server_name, String driverTableName, String transaction_id, String sql, Object[] questiondata, int startRow, int rowsToRetrieve, int type) throws ServoyException, RemoteException {
            // don't know the
            return dataSetMap.values().iterator().next();
        // datasource,
        // just return
        // the first
        // dataset
        }

        @Override
        public IDataSet performQuery(String client_id, String server_name, String transaction_id, ISQLSelect sqlSelect, ColumnType[] resultTypes, ArrayList<TableFilter> filters, boolean distinctInMemory, int startRow, int rowsToRetrieve, int type, ITrackingSQLStatement trackingInfo) throws ServoyException, RemoteException {
            return dataSetMap.get(sqlSelect.getTable().getDataSource());
        }

        @Override
        public IDataSet performQuery(String client_id, String server_name, String transaction_id, ISQLSelect sqlSelect, ColumnType[] resultTypes, ArrayList<TableFilter> filters, boolean distinctInMemory, int startRow, int rowsToRetrieve, int type) throws ServoyException, RemoteException {
            IDataSet set = dataSetMap.get(sqlSelect.getTable().getDataSource());
            if (sqlSelect instanceof QuerySelect && ((QuerySelect) sqlSelect).getColumns().size() == 1) {
                // pk select
                int lastRow = Math.min(set.getRowCount(), startRow + rowsToRetrieve);
                BufferedDataSet ds = BufferedDataSetInternal.createBufferedDataSet(null, null, new SafeArrayList<Object[]>(0), lastRow < set.getRowCount());
                for (int i = startRow; i < lastRow; i++) {
                    ds.addRow(new Object[] { set.getRow(i)[0] });
                }
                return ds;
            }
            return set;
        }

        @Override
        public IDataSet performQuery(String client_id, String server_name, String driverTableName, String transaction_id, String sql, Object[] questiondata, int startRow, int rowsToRetrieve) throws ServoyException, RemoteException {
            // don't know the
            return dataSetMap.values().iterator().next();
        // datasource,
        // just return
        // the first
        // dataset
        }

        @Override
        public IDataSet performQuery(String client_id, String server_name, String transaction_id, ISQLSelect sqlSelect, ColumnType[] resultTypes, ArrayList<TableFilter> filters, boolean distinctInMemory, int startRow, int rowsToRetrieve) throws ServoyException, RemoteException {
            return dataSetMap.get(sqlSelect.getTable().getDataSource());
        }

        @Override
        public IDataSet performCustomQuery(String client_id, String server_name, String driverTableName, String transaction_id, ISQLSelect sqlSelect, ArrayList<TableFilter> filters, int startRow, int rowsToRetrieve) throws ServoyException, RemoteException {
            return dataSetMap.get(sqlSelect.getTable().getDataSource());
        }

        @Override
        public boolean notifyDataChange(String client_id, String server_name, String tableName, IDataSet pks, int action, String transaction_id) throws RemoteException {
            return false;
        }

        @Override
        public ITable insertQueryResult(String client_id, String queryServerName, String queryTid, ISQLSelect sqlSelect, ArrayList<TableFilter> filters, boolean distinctInMemory, int startRow, int rowsToRetrieve, int type, String dataSource, String targetServerName, String targetTableName, String targetTid, ColumnType[] columnTypes, String[] pkNames) throws ServoyException, RemoteException {
            return null;
        }

        @Override
        public InsertResult insertDataSet(String client_id, IDataSet set, final String dataSource, String serverName, String tableName, String tid, ColumnType[] columnTypes, String[] pkNames, HashMap<String, ColumnInfoDef> columnInfoDefinitions) throws ServoyException, RemoteException {
            dataSetMap.put(dataSource, set);
            Table table = new Table(serverName, serverName, true, ITable.TABLE, null, null);
            table.setDataSource(dataSource);
            for (int i = 0; i < set.getColumnCount(); i++) {
                Column col = new Column(table, set.getColumnNames()[i], set.getColumnTypes()[i], 50, 50, true);
                table.addColumn(col);
                if (Arrays.binarySearch(pkNames, col.getName()) >= 0) {
                    col.setDatabasePK(true);
                }
            }
            return new InsertResult(table, new Object[0]);
        }

        @Override
        public QuerySet getSQLQuerySet(String serverName, ISQLQuery sqlQuery, ArrayList<TableFilter> filters, int startRow, int rowsToRetrieve, boolean forceQualifyColumns, boolean disableUseArrayForIn) throws RepositoryException, RemoteException {
            QuerySet qs = new QuerySet();
            qs.setSelect(new QueryString("select from test", false));
            return qs;
        }

        @Override
        public Object getNextSequence(String serverName, String tableName, String columnName, int columnInfoID, String columnInfoServer) throws RepositoryException, RemoteException {
            return null;
        }

        @Override
        public Blob getBlob(String clientId, String serverName, ISQLSelect blobSelect, ArrayList<TableFilter> filters, String tid) throws RepositoryException, RemoteException {
            return null;
        }

        @Override
        public boolean endTransactions(String client_id, String[] transaction_id, boolean commit) throws RepositoryException, RemoteException {
            return false;
        }

        @Override
        public void dropTemporaryTable(String client_id, String serverName, String tableName) throws RemoteException, RepositoryException {
        }

        @Override
        public ISQLStatement createSQLStatement(int action, String server_name, String tableName, Object[] pkColumnData, String tid, ISQLUpdate sqlUpdate, ArrayList<TableFilter> filters) throws RemoteException {
            return null;
        }

        @Override
        public ISQLStatement createSQLStatement(int action, String server_name, String tableName, Object[] pkColumnData, String tid, String sql, Object[] questiondata) throws RemoteException, RepositoryException {
            return null;
        }

        @Override
        public void addClientAsTableUser(String client_id, String serverName, String tableName) throws RemoteException, RepositoryException {
        }

        @Override
        public IDataSet[] executeProcedure(String clientId, String server_name, String tid, Procedure procedure, Object[] arguments) throws RepositoryException, RemoteException {
            return null;
        }
    };
}

Example 2 with ISQLSelect

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

the class RelatedFoundSet method createRelatedFoundSets.

/**
 * Create multiple related foundsets in one call to the data server.
 *
 * @param factory
 * @param app
 * @param parents same length as whereArsgLists
 * @param relation
 * @param sheet
 * @param whereArsgLists
 * @param defaultSortColumns
 * @return
 * @throws ServoyException
 */
public static IFoundSetInternal[] createRelatedFoundSets(IFoundSetFactory factory, IFoundSetManagerInternal app, IRecordInternal[] parents, Relation relation, SQLSheet sheet, Object[][] whereArsgLists, List<SortColumn> defaultSortColumns) throws ServoyException {
    if (sheet == null) {
        // $NON-NLS-1$
        throw new IllegalArgumentException(app.getApplication().getI18NMessage("servoy.foundSet.error.sqlsheet"));
    }
    FoundSetManager fsm = (FoundSetManager) app;
    List<SortColumn> sortColumns;
    if (defaultSortColumns == null || defaultSortColumns.size() == 0) {
        sortColumns = sheet.getDefaultPKSort();
    } else {
        sortColumns = defaultSortColumns;
    }
    QuerySelect cleanSelect = fsm.getSQLGenerator().getPKSelectSqlSelect(fsm.getScopesScopeProvider(), sheet.getTable(), null, null, true, null, sortColumns, false);
    QuerySelect relationSelect = (QuerySelect) sheet.getRelatedSQLDescription(relation.getName()).getSQLQuery();
    // don't select all columns in pk select
    cleanSelect.setColumns(AbstractBaseQuery.relinkTable(relationSelect.getTable(), cleanSelect.getTable(), relationSelect.getColumnsClone()));
    // copy the where (is foreign where)
    cleanSelect.setCondition(SQLGenerator.CONDITION_RELATION, AbstractBaseQuery.relinkTable(relationSelect.getTable(), cleanSelect.getTable(), relationSelect.getConditionClone(SQLGenerator.CONDITION_RELATION)));
    TablePlaceholderKey placeHolderKey = SQLGenerator.createRelationKeyPlaceholderKey(cleanSelect.getTable(), relation.getName());
    // all queries
    QuerySelect[] sqlSelects = new QuerySelect[whereArsgLists.length];
    // all aggregates
    QuerySelect[] aggregateSelects = new QuerySelect[whereArsgLists.length];
    List<Integer> queryIndex = new ArrayList<Integer>(whereArsgLists.length);
    Map<Integer, Row> cachedRows = new HashMap<Integer, Row>();
    List<QueryData> queryDatas = new ArrayList<QueryData>(whereArsgLists.length);
    String transactionID = fsm.getTransactionID(sheet);
    String clientID = fsm.getApplication().getClientID();
    ArrayList<TableFilter> sqlFilters = fsm.getTableFilterParams(sheet.getServerName(), cleanSelect);
    for (int i = 0; i < whereArsgLists.length; i++) {
        Object[] whereArgs = whereArsgLists[i];
        if (whereArgs == null || whereArgs.length == 0) {
            // $NON-NLS-1$
            throw new IllegalArgumentException(app.getApplication().getI18NMessage("servoy.relatedfoundset.error.noFK") + relation.getName());
        }
        QuerySelect sqlSelect;
        if (i == whereArsgLists.length - 1) {
            // the last one, use the template, no clone needed
            sqlSelect = cleanSelect;
        } else {
            sqlSelect = AbstractBaseQuery.deepClone(cleanSelect);
        }
        if (!sqlSelect.setPlaceholderValue(placeHolderKey, whereArgs)) {
            // $NON-NLS-1$//$NON-NLS-2$ //$NON-NLS-3$
            Debug.error(new RuntimeException("Could not set placeholder " + placeHolderKey + " in query " + sqlSelect + "-- continuing"));
        }
        sqlSelects[i] = sqlSelect;
        // Check for non-empty where-arguments, joins on null-conditions are not allowed (similar to FK constraints in databases)
        if (!whereArgsIsEmpty(whereArgs)) {
            Row cachedRow = null;
            if (relation.isFKPKRef(fsm.getApplication().getFlattenedSolution())) {
                // optimize for FK->PK relation, if the data is already cached, do not query
                RowManager rowManager = fsm.getRowManager(relation.getForeignDataSource());
                if (rowManager != null) {
                    cachedRow = rowManager.getCachedRow(whereArgs).getLeft();
                }
            }
            if (cachedRow != null) {
                if (Debug.tracing()) {
                    // $NON-NLS-1$
                    Debug.trace(Thread.currentThread().getName() + ": Found cached FK record");
                }
                cachedRows.put(Integer.valueOf(i), cachedRow);
            } else if (!parents[i].existInDataSource() && !fsm.config.loadRelatedRecordsIfParentIsNew() && relation.hasPKFKCondition(fsm.getApplication().getFlattenedSolution())) {
            /*
					 * Optimize for init of related foundsets on a parent record that is new and where the relation includes equal conditions for all the parent
					 * rowIdentifier columns
					 *
					 * In this case no query has to be made to the DB to fetch existing records, as there wouldn't be any.
					 */
            } else {
                ISQLSelect selectStatement = AbstractBaseQuery.deepClone((ISQLSelect) sqlSelect);
                // Note: put a clone of sqlSelect in the queryDatas list, we will compress later over multiple queries using pack().
                // Clone is needed because packed queries may not be save to manipulate.
                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(sheet.getColumnNames(), new Object[][] {}, new Object[][] {}, fsm.getApplication().getUserUID(), fsm.getTrackingInfo(), fsm.getApplication().getClientID());
                }
                queryDatas.add(new QueryData(selectStatement, sqlFilters, !sqlSelect.isUnique(), 0, fsm.config.initialRelatedChunkSize(), IDataServer.RELATION_QUERY, trackingInfo));
                queryIndex.add(Integer.valueOf(i));
                QuerySelect aggregateSelect = FoundSet.getAggregateSelect(sheet, sqlSelect);
                if (aggregateSelect != null) {
                    // Note: see note about clone above.
                    queryDatas.add(new QueryData(AbstractBaseQuery.deepClone((ISQLSelect) aggregateSelect), fsm.getTableFilterParams(sheet.getServerName(), aggregateSelect), false, 0, 1, IDataServer.AGGREGATE_QUERY, null));
                    // same index for aggregates
                    queryIndex.add(Integer.valueOf(i));
                    aggregateSelects[i] = aggregateSelect;
                }
            }
        }
    }
    IDataSet[] dataSets = null;
    if (queryDatas.size() > 0) {
        try {
            // pack is safe here because queryDatas contains only cloned ISQLSelect objects
            QueryData[] qDatas = queryDatas.toArray(new QueryData[queryDatas.size()]);
            AbstractBaseQuery.acceptVisitor(qDatas, new PackVisitor());
            int size = 0;
            if (// trace the message size
            Debug.tracing()) {
                try {
                    ByteArrayOutputStream bs = new ByteArrayOutputStream();
                    ObjectOutputStream os = new ObjectOutputStream(bs);
                    os.writeObject(qDatas);
                    os.close();
                    size = bs.size();
                } catch (Exception e) {
                    Debug.trace(e);
                }
            }
            long time = System.currentTimeMillis();
            dataSets = fsm.getDataServer().performQuery(clientID, sheet.getServerName(), transactionID, qDatas);
            if (Debug.tracing()) {
                Debug.trace(// $NON-NLS-1$ //$NON-NLS-2$
                Thread.currentThread().getName() + ": Relation query: " + relation.getName() + " with: " + qDatas.length + " queries,query size: " + size + ",time: " + (System.currentTimeMillis() - time) + // $NON-NLS-1$//$NON-NLS-2$ //$NON-NLS-3$
                "ms");
            }
        } catch (RepositoryException re) {
            testException(clientID, re);
            throw re;
        } catch (RemoteException e) {
            testException(clientID, e.getCause());
            throw new RepositoryException(e);
        }
    }
    IFoundSetInternal[] foundsets = new RelatedFoundSet[whereArsgLists.length];
    int d = 0;
    for (int i = 0; i < whereArsgLists.length; i++) {
        IDataSet data;
        IDataSet aggregateData = null;
        int index = (d >= queryIndex.size()) ? -1 : queryIndex.get(d).intValue();
        if (index == i) {
            // regular query
            data = dataSets[d++];
            // optionally followed by aggregate
            index = (d >= queryIndex.size()) ? -1 : queryIndex.get(d).intValue();
            if (index == i) {
                // aggregate
                aggregateData = dataSets[d++];
            }
        } else {
            data = new BufferedDataSet();
            Row row = cachedRows.get(Integer.valueOf(i));
            if (row != null) {
                // cached
                data.addRow(row.getRawColumnData());
            }
        // else whereArgsIsEmpty
        }
        foundsets[i] = factory.createRelatedFoundSet(data, sqlSelects[i], app, parents[i], relation.getName(), sheet, sortColumns, aggregateSelects[i], aggregateData);
        if (aggregateData != null && foundsets[i] instanceof FoundSet) {
            ((FoundSet) foundsets[i]).fillAggregates(aggregateSelects[i], aggregateData);
        }
    }
    if (d != queryIndex.size()) {
        // $NON-NLS-1$
        throw new RepositoryException("Related query parameters out of sync " + d + '/' + queryIndex.size());
    }
    return foundsets;
}