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

Example 31 with QueryTable

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

the class RelatedValueList method createRelatedValuelistQuery.

public static Pair<QuerySelect, BaseQueryTable> createRelatedValuelistQuery(IServiceProvider application, ValueList valueList, Relation[] relations, IRecordInternal parentState) throws ServoyException {
    if (parentState == null) {
        return null;
    }
    FoundSetManager foundSetManager = (FoundSetManager) application.getFoundSetManager();
    SQLGenerator sqlGenerator = foundSetManager.getSQLGenerator();
    IGlobalValueEntry scopesScopeProvider = foundSetManager.getScopesScopeProvider();
    SQLSheet childSheet = sqlGenerator.getCachedTableSQLSheet(relations[0].getPrimaryDataSource());
    // this returns quickly if it already has a sheet for that relation, but optimize further?
    sqlGenerator.makeRelatedSQL(childSheet, relations[0]);
    QuerySelect select = AbstractBaseQuery.deepClone((QuerySelect) childSheet.getRelatedSQLDescription(relations[0].getName()).getSQLQuery());
    Object[] relationWhereArgs = foundSetManager.getRelationWhereArgs(parentState, relations[0], false);
    if (relationWhereArgs == null) {
        return null;
    }
    TablePlaceholderKey placeHolderKey = SQLGenerator.createRelationKeyPlaceholderKey(select.getTable(), relations[0].getName());
    if (!select.setPlaceholderValue(placeHolderKey, relationWhereArgs)) {
        // $NON-NLS-1$//$NON-NLS-2$
        Debug.error(new RuntimeException("Could not set relation placeholder " + placeHolderKey + " in query " + select));
        return null;
    }
    FlattenedSolution fs = application.getFlattenedSolution();
    BaseQueryTable lastTable = select.getTable();
    ITable foreignTable = fs.getTable(relations[0].getForeignDataSource());
    for (int i = 1; i < relations.length; i++) {
        foreignTable = fs.getTable(relations[i].getForeignDataSource());
        ISQLTableJoin join = SQLGenerator.createJoin(application.getFlattenedSolution(), relations[i], lastTable, new QueryTable(foreignTable.getSQLName(), foreignTable.getDataSource(), foreignTable.getCatalog(), foreignTable.getSchema()), true, scopesScopeProvider);
        select.addJoin(join);
        lastTable = join.getForeignTable();
    }
    List<SortColumn> defaultSort = foundSetManager.getSortColumns(relations[relations.length - 1].getForeignDataSource(), valueList.getSortOptions());
    foundSetManager.getSQLGenerator().addSorts(select, lastTable, scopesScopeProvider, foreignTable, defaultSort, true, true);
    int showValues = valueList.getShowDataProviders();
    int returnValues = valueList.getReturnDataProviders();
    int total = (showValues | returnValues);
    ArrayList<IQuerySelectValue> columns = new ArrayList<IQuerySelectValue>();
    if ((total & 1) != 0) {
        columns.add(getQuerySelectValue(foreignTable, lastTable, valueList.getDataProviderID1()));
    }
    if ((total & 2) != 0) {
        columns.add(getQuerySelectValue(foreignTable, lastTable, valueList.getDataProviderID2()));
    }
    if ((total & 4) != 0) {
        columns.add(getQuerySelectValue(foreignTable, lastTable, valueList.getDataProviderID3()));
    }
    select.setColumns(columns);
    // not allowed in all situations
    select.setDistinct(false);
    return new Pair<QuerySelect, BaseQueryTable>(select, lastTable);
}

Example 32 with QueryTable

use of com.servoy.j2db.query.QueryTable 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 33 with QueryTable

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

the class JSDatabaseManager method js_mergeRecords.

// strongly recommended to use a transaction
// currently does not support compound pks
/**
 * Merge records from the same foundset, updates entire datamodel (via foreign type on columns) with destination
 * record pk, deletes source record. Do use a transaction!
 *
 * This function is very handy in situations where duplicate data exists. It allows you to merge the two records
 * and move all related records in one go. Say the source_record is "Ikea" and the combined_destination_record is "IKEA", the
 * "Ikea" record is deleted and all records related to it (think of contacts and orders, for instance) will be related
 * to the "IKEA" record.
 *
 * The function takes an optional array of column names. If provided, the data in the named columns will be copied
 * from source_record to combined_destination_record.
 *
 * Note that it is essential for both records to originate from the same foundset, as shown in the sample code.
 *
 * @sample databaseManager.mergeRecords(foundset.getRecord(1),foundset.getRecord(2));
 *
 * @param sourceRecord The source JSRecord to copy from.
 * @param combinedDestinationRecord The target/destination JSRecord to copy into.
 * @param columnNames The column names array that should be copied.
 *
 * @return true if the records could me merged.
 */
public boolean js_mergeRecords(IRecordInternal sourceRecord, IRecordInternal combinedDestinationRecord, String[] columnNames) throws ServoyException {
    checkAuthorized();
    if (sourceRecord != null && combinedDestinationRecord != null) {
        FoundSetManager fsm = (FoundSetManager) application.getFoundSetManager();
        try {
            if (sourceRecord.getParentFoundSet() != combinedDestinationRecord.getParentFoundSet()) {
                return false;
            }
            Table mainTable = (Table) combinedDestinationRecord.getParentFoundSet().getTable();
            String mainTableForeignType = mainTable.getName();
            String transaction_id = fsm.getTransactionID(mainTable.getServerName());
            Object sourceRecordPK = null;
            Object combinedDestinationRecordPK = null;
            Column pkc = null;
            Iterator<Column> pk_it = mainTable.getRowIdentColumns().iterator();
            if (pk_it.hasNext()) {
                pkc = pk_it.next();
                sourceRecordPK = sourceRecord.getValue(pkc.getDataProviderID());
                if (sourceRecordPK == null)
                    sourceRecordPK = ValueFactory.createNullValue(pkc.getType());
                combinedDestinationRecordPK = combinedDestinationRecord.getValue(pkc.getDataProviderID());
                if (combinedDestinationRecordPK == null)
                    combinedDestinationRecordPK = ValueFactory.createNullValue(pkc.getType());
                // multipk not supported
                if (pk_it.hasNext())
                    return false;
            }
            List<SQLStatement> updates = new ArrayList<SQLStatement>();
            IServer server = application.getSolution().getServer(mainTable.getServerName());
            if (server != null) {
                Iterator<String> it = server.getTableNames(false).iterator();
                while (it.hasNext()) {
                    String tableName = it.next();
                    Table table = (Table) server.getTable(tableName);
                    // not supported
                    if (table.getRowIdentColumnsCount() > 1)
                        continue;
                    Iterator<Column> it2 = table.getColumns().iterator();
                    while (it2.hasNext()) {
                        Column c = it2.next();
                        if (c.getColumnInfo() != null) {
                            if (mainTableForeignType.equalsIgnoreCase(c.getColumnInfo().getForeignType())) {
                                // update table set foreigntypecolumn = combinedDestinationRecordPK where foreigntypecolumn = sourceRecordPK
                                QueryTable qTable = new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema());
                                QueryUpdate qUpdate = new QueryUpdate(qTable);
                                QueryColumn qc = c.queryColumn(qTable);
                                qUpdate.addValue(qc, combinedDestinationRecordPK);
                                ISQLCondition condition = new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, qc, sourceRecordPK);
                                qUpdate.setCondition(condition);
                                IDataSet pks = new BufferedDataSet();
                                // unknown number of records changed
                                pks.addRow(new Object[] { ValueFactory.createTableFlushValue() });
                                SQLStatement statement = new SQLStatement(ISQLActionTypes.UPDATE_ACTION, table.getServerName(), table.getName(), pks, transaction_id, qUpdate, fsm.getTableFilterParams(table.getServerName(), qUpdate));
                                updates.add(statement);
                            }
                        }
                    }
                }
            }
            IDataSet pks = new BufferedDataSet();
            pks.addRow(new Object[] { sourceRecordPK });
            QueryTable qTable = new QueryTable(mainTable.getSQLName(), mainTable.getDataSource(), mainTable.getCatalog(), mainTable.getSchema());
            QueryDelete qDelete = new QueryDelete(qTable);
            ISQLCondition condition = new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, pkc.queryColumn(qTable), sourceRecordPK);
            qDelete.setCondition(condition);
            SQLStatement statement = new SQLStatement(ISQLActionTypes.DELETE_ACTION, mainTable.getServerName(), mainTable.getName(), pks, transaction_id, qDelete, fsm.getTableFilterParams(mainTable.getServerName(), qDelete));
            // check that the row is really deleted
            statement.setExpectedUpdateCount(1);
            updates.add(statement);
            IFoundSetInternal sfs = sourceRecord.getParentFoundSet();
            if (combinedDestinationRecord.startEditing()) {
                if (columnNames != null) {
                    for (String element : columnNames) {
                        if (element == null)
                            continue;
                        if (sfs.getColumnIndex(element) >= 0) {
                            combinedDestinationRecord.setValue(element, sourceRecord.getValue(element));
                        }
                    }
                }
                fsm.getEditRecordList().stopEditing(true, combinedDestinationRecord);
            } else {
                return false;
            }
            Object[] results = fsm.getDataServer().performUpdates(fsm.getApplication().getClientID(), updates.toArray(new ISQLStatement[updates.size()]));
            for (int i = 0; results != null && i < results.length; i++) {
                if (results[i] instanceof ServoyException) {
                    throw (ServoyException) results[i];
                }
            }
            // sfs.deleteRecord(sfs.getRecordIndex(sourceRecord), true); not needed, will be flushed from memory in finally
            return true;
        } catch (Exception ex) {
            // $NON-NLS-1$
            application.handleException(// $NON-NLS-1$
            application.getI18NMessage("servoy.foundsetupdater.updateFailed"), new ApplicationException(ServoyException.SAVE_FAILED, ex));
        } finally {
            fsm.flushCachedDatabaseData(null);
        }
    }
    return false;
}

Example 34 with QueryTable

use of com.servoy.j2db.query.QueryTable 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 35 with QueryTable

use of com.servoy.j2db.query.QueryTable 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;
}