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

Example 31 with RepositoryException

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

the class SQLGenerator method getPKSelectSqlSelect.

/*
 * _____________________________________________________________ The methods below belong to this class
 */
// SQL pk(s) select for foundset,concatenating those strings will always deliver a executable SQL
// Note: removeUnusedJoins must be false when the resulting query is changed afterwards (like adding columns)
QuerySelect getPKSelectSqlSelect(IGlobalValueEntry provider, Table table, QuerySelect oldSQLQuery, List<IRecordInternal> findStates, boolean reduce, IDataSet omitPKs, List<SortColumn> orderByFields, boolean removeUnusedJoins) throws ServoyException {
    if (table == null) {
        throw new RepositoryException(ServoyException.InternalCodes.TABLE_NOT_FOUND);
    }
    QuerySelect retval;
    if (oldSQLQuery != null) {
        retval = deepClone(oldSQLQuery);
        retval.setGroupBy(null);
        // will be generated based on foundset sorting
        if (orderByFields != null)
            retval.clearSorts();
        // remove all servoy conditions, except filter, search and relation
        for (String conditionName : retval.getConditionNames()) {
            if (conditionName.startsWith(SERVOY_CONDITION_PREFIX) && !(CONDITION_FILTER.equals(conditionName) || CONDITION_SEARCH.equals(conditionName) || CONDITION_RELATION.equals(conditionName))) {
                retval.setCondition(conditionName, null);
            }
        }
    } else {
        retval = new QuerySelect(new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema()));
    }
    // Example:-select pk1,pk2 from tablename1 where ((fieldname1 like '%abcd%') or ((fieldname2 like '%xyz%')) (retrieve max 200 rows)
    ArrayList<IQuerySelectValue> pkQueryColumns = new ArrayList<IQuerySelectValue>(3);
    ArrayList<Column> pkColumns = new ArrayList<Column>(3);
    // getPrimaryKeys from table
    Iterator<Column> pks = table.getRowIdentColumns().iterator();
    // make select
    if (!pks.hasNext()) {
        throw new RepositoryException(ServoyException.InternalCodes.PRIMARY_KEY_NOT_FOUND, new Object[] { table.getName() });
    }
    while (pks.hasNext()) {
        Column column = pks.next();
        pkColumns.add(column);
        pkQueryColumns.add(column.queryColumn(retval.getTable()));
    }
    retval.setColumns(pkQueryColumns);
    if (omitPKs != null && omitPKs.getRowCount() != 0) {
        // omit is rebuild each time
        retval.setCondition(CONDITION_OMIT, createSetConditionFromPKs(IBaseSQLCondition.NOT_OPERATOR, pkQueryColumns.toArray(new QueryColumn[pkQueryColumns.size()]), pkColumns, omitPKs));
    } else if (oldSQLQuery != null) {
        retval.setCondition(CONDITION_OMIT, oldSQLQuery.getConditionClone(CONDITION_OMIT));
    }
    if (// new
    findStates != null && findStates.size() != 0) {
        ISQLCondition moreWhere = null;
        for (IRecordInternal obj : findStates) {
            if (obj instanceof FindState) {
                moreWhere = OrCondition.or(moreWhere, createConditionFromFindState((FindState) obj, retval, provider, pkQueryColumns));
            }
        }
        if (moreWhere != null) {
            if (reduce) {
                retval.addCondition(CONDITION_SEARCH, moreWhere);
            } else {
                retval.addConditionOr(CONDITION_SEARCH, moreWhere);
            }
            if (retval.getJoins() != null) {
                // check if the search condition has an or-condition
                final boolean[] hasOr = { false };
                retval.getCondition(CONDITION_SEARCH).acceptVisitor(new IVisitor() {

                    public Object visit(Object o) {
                        if (o instanceof OrCondition && ((OrCondition) o).getConditions().size() > 1) {
                            hasOr[0] = true;
                            return new VisitorResult(o, false);
                        }
                        return o;
                    }
                });
                if (hasOr[0]) {
                    // override join type to left outer join, a related OR-search should not make the result set smaller
                    for (ISQLJoin join : retval.getJoins()) {
                        if (join instanceof QueryJoin && ((QueryJoin) join).getJoinType() == IQueryConstants.INNER_JOIN) {
                            ((QueryJoin) join).setJoinType(IQueryConstants.LEFT_OUTER_JOIN);
                        }
                    }
                }
            }
        }
    }
    // make orderby
    if (orderByFields != null || retval.getSorts() == null) {
        List<SortColumn> orderBy = orderByFields == null ? new ArrayList<SortColumn>(3) : orderByFields;
        if (orderBy.size() == 0) {
            for (Column pkColumn : pkColumns) {
                orderBy.add(new SortColumn(pkColumn));
            }
        }
        addSorts(retval, retval.getTable(), provider, table, orderBy, true, false);
    }
    if (removeUnusedJoins) {
        // remove unneeded joins, some may have been added because of a previous sort and are no longer needed.
        retval.removeUnusedJoins(false);
    }
    // 1 do not remove sort or groupby test, will cause invalid queries
    // 1 this one causes error and can not be fixed,
    // 1 if (joinswherepart.length() != 0 && !sortIsRelated && groupbyKeyword == STRING_EMPTY && table.getPrimaryKeyCount() == 1)
    // 1 sql select distinct(s_contacts.contactsid) from s_contacts,s_companies where s_contacts.company_id = s_companies.company_id order by s_contacts.surname  ERROR:  For SELECT DISTINCT, ORDER BY expressions must appear in target list
    // retval may have set distinct and plainPKSelect flag based on previous sort columns, make sure to reset first
    retval.setDistinct(false);
    retval.setPlainPKSelect(false);
    if (// if joined pks comes back multiple times
    retval.getJoins() != null && retval.getColumns().size() == 1 && isDistinctAllowed(retval.getColumns(), retval.getSorts())) {
        retval.setDistinct(true);
    } else if (// plain pk select
    retval.getJoins() == null && retval.getColumns().size() == pkColumns.size()) {
        retval.setPlainPKSelect(true);
    }
    return retval;
}

Example 32 with RepositoryException

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

the class SQLGenerator method makeRelatedSQL.

synchronized void makeRelatedSQL(SQLSheet relatedSheet, Relation r) {
    if (relatedSheet.getRelatedSQLDescription(r.getName()) != null)
        return;
    try {
        FlattenedSolution fs = application.getFlattenedSolution();
        if (!Relation.isValid(r, fs) || r.isParentRef()) {
            return;
        }
        ITable ft = fs.getTable(r.getForeignDataSource());
        if (ft == null) {
            return;
        }
        // add primary keys if missing
        QueryTable foreignQTable = new QueryTable(ft.getSQLName(), ft.getDataSource(), ft.getCatalog(), ft.getSchema());
        QuerySelect relatedSelect = new QuerySelect(foreignQTable);
        List<String> parentRequiredDataProviderIDs = new ArrayList<String>();
        Column[] relcols = r.getForeignColumns(fs);
        for (Column column : relcols) {
            parentRequiredDataProviderIDs.add(column.getDataProviderID());
        }
        relatedSelect.setCondition(CONDITION_RELATION, createRelatedCondition(application, r, foreignQTable));
        Collection<Column> rcolumns = ft.getColumns();
        relatedSelect.setColumns(makeQueryColumns(rcolumns.iterator(), foreignQTable, null));
        // fill dataprovider map
        List<String> dataProviderIDsDilivery = new ArrayList<String>();
        Iterator<Column> it = rcolumns.iterator();
        while (it.hasNext()) {
            Column col = it.next();
            dataProviderIDsDilivery.add(col.getDataProviderID());
        }
        relatedSheet.addRelatedSelect(r.getName(), relatedSelect, dataProviderIDsDilivery, parentRequiredDataProviderIDs, null);
        createAggregates(relatedSheet, foreignQTable);
    } catch (RepositoryException e) {
        Debug.error(e);
    }
}

Example 33 with RepositoryException

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

the class SQLSheet method getNewRowData.

/**
 * Returns raw (not using column converters) row data for a new record
 * @param app
 * @param fs
 * @return
 */
Object[] getNewRowData(IServiceProvider app, FoundSet fs) {
    Object[][] creationArgs = null;
    // INSERT
    SQLDescription desc = getSQLDescription(SELECT);
    // RequiredDataProviderIDs();
    List<?> list = desc.getDataProviderIDsDilivery();
    Column[] fcols = null;
    Relation relation = null;
    String relationName = fs.getRelationName();
    if (relationName != null) {
        try {
            relation = app.getFlattenedSolution().getRelation(relationName);
            if (relation != null) {
                fcols = relation.getForeignColumns(app.getFlattenedSolution());
                QuerySelect creationSQLString = fs.getCreationSqlSelect();
                Placeholder ph = creationSQLString.getPlaceholder(SQLGenerator.createRelationKeyPlaceholderKey(creationSQLString.getTable(), relation.getName()));
                if (ph != null && ph.isSet()) {
                    // a matrix as wide as the relation keys and 1 deep
                    creationArgs = (Object[][]) ph.getValue();
                }
            }
        } catch (RepositoryException e) {
            Debug.error(e);
        }
    }
    Object[] array = new Object[list.size()];
    for (int i = 0; i < list.size(); i++) {
        try {
            boolean filled = false;
            Column c = table.getColumn((String) list.get(i));
            if (c.isDBIdentity()) {
                array[i] = ValueFactory.createDbIdentValue();
                filled = true;
            } else {
                ColumnInfo ci = c.getColumnInfo();
                if (c.getRowIdentType() != IBaseColumn.NORMAL_COLUMN && ci != null && ci.hasSequence()) {
                    // this is here for safety, it can happen that a form has (unwanted) still a related foundset which is created by relation based on primary key
                    array[i] = c.getNewRecordValue(app);
                    filled = true;
                } else {
                    if (// created via relation, so fill the foreign key with foreign value
                    creationArgs != null && creationArgs.length != 0 && fcols != null) {
                        for (int j = 0; j < fcols.length; j++) {
                            if (c.equals(fcols[j]) && ((relation.getOperators()[j] & IBaseSQLCondition.OPERATOR_MASK) == IBaseSQLCondition.EQUALS_OPERATOR)) {
                                // creationArgs is a matrix as wide as the relation keys and 1 deep
                                array[i] = creationArgs[j][0];
                                filled = true;
                                break;
                            }
                        }
                    }
                }
            }
            if (!filled) {
                array[i] = c.getNewRecordValue(app);
            }
        } catch (Exception ex) {
            Debug.error(ex);
        }
    }
    return array;
}

Example 34 with RepositoryException

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

the class RelatedFoundSet method getWhereArgs.

public Object[] getWhereArgs(boolean onlyEqualsConditions) {
    Placeholder ph = creationSqlSelect.getPlaceholder(SQLGenerator.createRelationKeyPlaceholderKey(creationSqlSelect.getTable(), getRelationName()));
    if (ph == null || !ph.isSet()) {
        if (!findMode) {
            Debug.error(// $NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$
            "RelatedFoundset, creation args not found\nplaceholder=" + ph + "\nrelation=" + getRelationName() + "\ncreationSqlSelect=" + creationSqlSelect, new RuntimeException("RelatedFoundset, creation args not found!!"));
        }
        // how can this happen (other then in find mode) ??
        return null;
    }
    Relation relation = fsm.getApplication().getFlattenedSolution().getRelation(relationName);
    if (relation == null) {
        // $NON-NLS-1$
        throw new IllegalStateException("Relation not found for related foundset: " + relationName);
    }
    Object[][] foreignData = (Object[][]) ph.getValue();
    Column[] columns;
    try {
        columns = relation.getForeignColumns(fsm.getApplication().getFlattenedSolution());
    } catch (RepositoryException e) {
        Debug.error(e);
        // $NON-NLS-1$
        throw new IllegalStateException("Relation columns not found for related foundset: " + relationName);
    }
    if (columns.length != foreignData.length) {
        // $NON-NLS-1$
        throw new IllegalStateException("Relation where-args inconsistent with columns for relation" + relationName);
    }
    IntStream columnIndexesStream;
    if (onlyEqualsConditions) {
        int[] columnIndexes = getIndexesEqualsEntries();
        if (columnIndexes.length == 0) {
            return null;
        }
        columnIndexesStream = IntStream.of(columnIndexes);
    } else {
        columnIndexesStream = IntStream.range(0, columns.length);
    }
    return columnIndexesStream.mapToObj(i -> {
        // Use converted value for hash
        int colindex = getSQLSheet().getColumnIndex(columns[i].getDataProviderID());
        return getSQLSheet().convertValueToObject(foreignData[i][0], colindex, fsm.getColumnConverterManager());
    }).toArray();
}

Example 35 with RepositoryException

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