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

Example 1 with QueryTable

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

the class FoundSetManager method insertToDataSource.

public Object[] insertToDataSource(String name, IDataSet dataSet, ColumnType[] columnTypes, String[] pkNames, boolean create, boolean skipOnLoad, String server) throws ServoyException {
    if (name == null) {
        return null;
    }
    String dataSource = IServer.VIEW_SERVER.equals(server) ? DataSourceUtils.createViewDataSource(name) : DataSourceUtils.createInmemDataSource(name);
    IDataSet fixedDataSet = dataSet;
    List<ColumnType> fixedColumnTypes;
    if (columnTypes == null) {
        ColumnType[] dataSetTypes = BufferedDataSetInternal.getColumnTypeInfo(dataSet);
        fixedColumnTypes = dataSetTypes == null ? null : asList(dataSetTypes);
    } else {
        fixedColumnTypes = asList(columnTypes);
    }
    // get column def from the first in-mem datasource found
    ServoyJSONObject columnsDef = null;
    Iterator<TableNode> tblIte = application.getFlattenedSolution().getTableNodes(dataSource);
    int onLoadMethodId = -1;
    while (tblIte.hasNext()) {
        TableNode tn = tblIte.next();
        if (columnsDef == null)
            columnsDef = tn.getColumns();
        if (onLoadMethodId == -1)
            onLoadMethodId = tn.getOnFoundSetLoadMethodID();
    }
    HashMap<String, ColumnInfoDef> columnInfoDefinitions = null;
    boolean[] rowsToStringserialize = null;
    if (columnsDef == null) {
        // if we have array columns, convert values using StringSerializer
        if (containsArrayType(fixedColumnTypes)) {
            rowsToStringserialize = new boolean[fixedColumnTypes.size()];
            for (int i = 0; i < fixedColumnTypes.size(); i++) {
                ColumnType columnType = fixedColumnTypes.get(i);
                if (columnType.getSqlType() == Types.ARRAY) {
                    fixedColumnTypes.set(i, ColumnType.getColumnType(IColumnTypes.TEXT));
                    rowsToStringserialize[i] = true;
                }
            }
        }
    } else {
        TableDef tableInfo = DatabaseUtils.deserializeTableInfo(columnsDef);
        columnInfoDefinitions = new HashMap<String, ColumnInfoDef>();
        List<String> inmemColumnNames = new ArrayList<>();
        List<String> inmemPKs = new ArrayList<>();
        List<ColumnType> inmemColumnTypes = new ArrayList<>();
        for (int j = 0; j < tableInfo.columnInfoDefSet.size(); j++) {
            ColumnInfoDef cid = tableInfo.columnInfoDefSet.get(j);
            if (cid.autoEnterType != ColumnInfo.SEQUENCE_AUTO_ENTER || cid.autoEnterSubType != ColumnInfo.DATABASE_IDENTITY) {
                inmemColumnNames.add(cid.name);
                inmemColumnTypes.add(cid.columnType);
            }
            if ((cid.flags & IBaseColumn.IDENT_COLUMNS) != 0) {
                inmemPKs.add(cid.name);
            }
            columnInfoDefinitions.put(cid.name, cid);
            // apply stringserializer on designed datasources
            if (JSONSerializerWrapper.STRING_SERIALIZER_NAME.equals(cid.converterName)) {
                if (rowsToStringserialize == null) {
                    rowsToStringserialize = new boolean[fixedColumnTypes.size()];
                }
                rowsToStringserialize[j] = true;
            }
        }
        if (pkNames == null && inmemPKs.size() > 0) {
            pkNames = inmemPKs.toArray(new String[inmemPKs.size()]);
        }
        if (!asList(dataSet.getColumnNames()).equals(inmemColumnNames) || !compareColumnTypes(fixedColumnTypes, inmemColumnTypes)) {
            if (dataSet.getColumnCount() > 0 && !asList(dataSet.getColumnNames()).equals(inmemColumnNames)) {
                Debug.warn("Dataset column names definition does not match inmem table definition for datasource : " + dataSource + " columns of dataset: " + Arrays.toString(dataSet.getColumnNames()) + ", columns of in mem definition: " + inmemColumnNames);
            }
            if (fixedColumnTypes != null && !compareColumnTypes(fixedColumnTypes, inmemColumnTypes)) {
                Debug.warn("Dataset column types definition does not match inmem table definition for datasource : " + dataSource + " types of dataset: " + fixedColumnTypes + ", types of in mem definition: " + inmemColumnTypes);
            }
            fixedColumnTypes = inmemColumnTypes;
            fixedDataSet = BufferedDataSetInternal.createBufferedDataSet(inmemColumnNames.toArray(new String[inmemColumnNames.size()]), fixedColumnTypes.toArray(new ColumnType[fixedColumnTypes.size()]), new ArrayList<Object[]>(), false);
        }
    }
    // - in the else branch above (already defined in the table node), columns that are not auto sequences or dbidents; see inmemColumnNames/inmemColumnTypes(which is at this point the same as fixedColumnTypes)
    if (dataSet.getRowCount() > 0 && dataSet.getRow(0).length != fixedColumnTypes.size()) {
        // $NON-NLS-1$
        throw new RepositoryException("Data set rows do not match column count");
    }
    if (rowsToStringserialize != null) {
        replaceValuesWithSerializedString(dataSet, rowsToStringserialize);
    }
    try {
        ITable table = IServer.VIEW_SERVER.equals(server) ? viewDataSources.get(dataSource) : inMemDataSources.get(dataSource);
        if (table == null && !create) {
            throw new RepositoryException("Appending to non-existing datasource: " + dataSource);
        }
        GlobalTransaction gt = getGlobalTransaction();
        String tid = null;
        String serverName = server == null ? (table == null ? IServer.INMEM_SERVER : table.getServerName()) : server;
        if (gt != null) {
            tid = gt.getTransactionID(serverName);
        }
        if (create && table != null) {
            // temp table was used before, delete all data in it
            FoundSet foundSet = (FoundSet) getSharedFoundSet(dataSource);
            foundSet.removeLastFound();
            try {
                QueryDelete delete = new QueryDelete(new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema(), true));
                SQLStatement deleteStatement = new SQLStatement(ISQLActionTypes.DELETE_ACTION, table.getServerName(), table.getName(), null, tid, delete, null);
                application.getDataServer().performUpdates(application.getClientID(), new ISQLStatement[] { deleteStatement });
            } catch (Exception e) {
                Debug.log(e);
                table = null;
            }
            RowManager element = rowManagers.get(dataSource);
            if (element != null) {
                element.flushAllCachedRows();
            }
        }
        InsertResult insertResult = application.getDataServer().insertDataSet(application.getClientID(), fixedDataSet, dataSource, table == null ? IServer.INMEM_SERVER : table.getServerName(), table == null ? null : table.getName(), /* create temp table when null */
        tid, fixedColumnTypes == null ? null : fixedColumnTypes.toArray(new ColumnType[fixedColumnTypes.size()]), /* inferred from dataset when null */
        pkNames, columnInfoDefinitions);
        if (insertResult != null) {
            table = insertResult.getTable();
            // then call insertDataSet again so the data is inserted with the columns defined in the the dataset.
            if (dataSet != fixedDataSet && dataSet.getRowCount() > 0) {
                insertResult = application.getDataServer().insertDataSet(application.getClientID(), dataSet, dataSource, table.getServerName(), table.getName(), tid, fixedColumnTypes == null ? null : fixedColumnTypes.toArray(new ColumnType[fixedColumnTypes.size()]), /* inferred from dataset when null */
                pkNames, columnInfoDefinitions);
            }
            if (IServer.INMEM_SERVER.equals(serverName)) {
                inMemDataSources.put(dataSource, table);
            } else {
                viewDataSources.put(dataSource, table);
            }
            fireTableEvent(table);
            if (!skipOnLoad && fixedDataSet.getRowCount() == 0 && onLoadMethodId > 0) {
                IFoundSetInternal sharedFoundSet = getSharedFoundSet(dataSource);
                executeFoundsetTriggerReturnFirst(sharedFoundSet.getTable(), new Object[] { DataSourceUtils.getInmemDataSourceName(dataSource) }, StaticContentSpecLoader.PROPERTY_ONFOUNDSETLOADMETHODID, false, (Scriptable) sharedFoundSet);
            }
            if (create) {
                // only refresh when it is a new full load, when adding data to an existing table, it is only applicable to the (shared) foundset
                refreshFoundSetsFromDB(dataSource, null, false);
            }
            return insertResult.getGeneratedPks();
        }
    } catch (RemoteException e) {
        throw new RepositoryException(e);
    }
    return null;
}

Example 2 with QueryTable

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

the class FlattenedSolution method getValuelistSortRelation.

/**
 * Get the internal relation that can be used to sort on this value list using the display values.
 * @param valueList
 * @param callingTable
 * @param dataProviderID
 * @param foundSetManager
 * @return
 * @throws RepositoryException
 */
public Relation getValuelistSortRelation(ValueList valueList, Table callingTable, String dataProviderID, IFoundSetManagerInternal foundSetManager) throws RepositoryException {
    if (callingTable == null || valueList == null) {
        return null;
    }
    String destDataSource;
    Relation[] relationSequence;
    String relationPrefix;
    switch(valueList.getDatabaseValuesType()) {
        case IValueListConstants.TABLE_VALUES:
            // create an internal relation
            relationSequence = null;
            // $NON-NLS-1$
            relationPrefix = "";
            destDataSource = valueList.getDataSource();
            break;
        case IValueListConstants.RELATED_VALUES:
            // replace the last relation in the sequence with an internal relation
            relationSequence = getRelationSequence(valueList.getRelationName());
            if (relationSequence == null) {
                return null;
            }
            if (relationSequence.length > 1) {
                for (Relation r : relationSequence) {
                    if (r.getJoinType() != INNER_JOIN) {
                        // outer join on the intermediate tables causes extra results that influence the sorting result
                        return null;
                    }
                }
            }
            StringBuilder sb = new StringBuilder();
            for (Relation r : relationSequence) {
                sb.append('-').append(r.getName());
            }
            relationPrefix = sb.toString();
            destDataSource = relationSequence[relationSequence.length - 1].getForeignDataSource();
            break;
        default:
            return null;
    }
    if (destDataSource == null || !DataSourceUtils.isSameServer(callingTable.getDataSource(), destDataSource)) {
        // do not create a cross-server relation
        return null;
    }
    Table destTable = (Table) foundSetManager.getTable(destDataSource);
    if (destTable == null) {
        return null;
    }
    String relationName = // $NON-NLS-1$
    Relation.INTERNAL_PREFIX + "VL-" + callingTable.getDataSource() + '-' + dataProviderID + relationPrefix + '-' + valueList.getName() + '-';
    synchronized (this) {
        Column callingColumn = callingTable.getColumn(dataProviderID);
        if (callingColumn == null) {
            return null;
        }
        Relation relation = getRelation(relationName);
        if (relation == null) {
            // create in internal relation
            String dp;
            int returnValues = valueList.getReturnDataProviders();
            if ((returnValues & 1) != 0) {
                dp = valueList.getDataProviderID1();
            } else if ((returnValues & 2) != 0) {
                dp = valueList.getDataProviderID2();
            } else if ((returnValues & 4) != 0) {
                dp = valueList.getDataProviderID3();
            } else {
                return null;
            }
            Column destColumn = destTable.getColumn(dp);
            if (destColumn == null) {
                return null;
            }
            // create internal value list relation
            QueryTable callingQTable = new QueryTable(callingTable.getSQLName(), callingTable.getDataSource(), callingTable.getCatalog(), callingTable.getSchema());
            QueryTable destQTable = new QueryTable(destTable.getSQLName(), destTable.getDataSource(), destTable.getCatalog(), destTable.getSchema());
            List<ISQLTableJoin> joins = new ArrayList<ISQLTableJoin>();
            ISQLTableJoin lastJoin = null;
            if (relationSequence == null) {
                // table values
                joins.add(lastJoin = new QueryJoin(relationName, callingQTable, destQTable, new AndCondition(), LEFT_OUTER_JOIN, false));
                if (// apply name as filter on column valuelist_name
                valueList.getUseTableFilter()) {
                    lastJoin.getCondition().addCondition(new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, new QueryColumn(destQTable, DBValueList.NAME_COLUMN), valueList.getName()));
                }
            } else {
                // related values
                QueryTable primaryQTable = callingQTable;
                for (int i = 0; i < relationSequence.length; i++) {
                    Relation r = relationSequence[i];
                    QueryTable foreignQTable;
                    if (i == relationSequence.length - 1) {
                        // last one
                        foreignQTable = destQTable;
                    } else {
                        ITable relForeignTable = getTable(r.getForeignDataSource());
                        if (relForeignTable == null) {
                            return null;
                        }
                        foreignQTable = new QueryTable(relForeignTable.getSQLName(), relForeignTable.getDataSource(), relForeignTable.getCatalog(), relForeignTable.getSchema());
                    }
                    lastJoin = SQLGenerator.createJoin(this, r, primaryQTable, foreignQTable, false, new IGlobalValueEntry() {

                        public Object setDataProviderValue(String dpid, Object value) {
                            return null;
                        }

                        public Object getDataProviderValue(String dpid) {
                            // A value will be added when the relation is used, see SQLGenerator.createJoin
                            return new Placeholder(new ObjectPlaceholderKey<int[]>(null, dpid));
                        }

                        public boolean containsDataProvider(String dpid) {
                            return false;
                        }
                    });
                    joins.add(lastJoin);
                    primaryQTable = foreignQTable;
                }
            }
            // add condition for return dp id
            lastJoin.getCondition().addCondition(new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, destColumn.queryColumn(destQTable), callingColumn.queryColumn(callingQTable)));
            relation = getSolutionCopy().createNewRelation(new ScriptNameValidator(this), relationName, callingTable.getDataSource(), destDataSource, LEFT_OUTER_JOIN);
            ISQLTableJoin join;
            if (joins.size() == 1) {
                join = lastJoin;
            } else {
                // combine joins
                join = new QueryCompositeJoin(relationName, joins);
            }
            relation.setRuntimeProperty(Relation.RELATION_JOIN, join);
        }
        return relation;
    }
}

Example 3 with QueryTable

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

the class FindState method createFindStateJoins.

/**
 * Find all processable related find states and create joins. A find state is processable when it has changed or when a related find state has changed.
 * @param sqlSelect
 * @param relations path to this state
 * @param selectTable
 * @param provider
 * @return
 * @throws RepositoryException
 */
public List<RelatedFindState> createFindStateJoins(QuerySelect sqlSelect, List<IRelation> relations, BaseQueryTable selectTable, IGlobalValueEntry provider) throws RepositoryException {
    List<RelatedFindState> relatedFindStates = null;
    List<Relation> searchRelations = getValidSearchRelations();
    // find processable find states of related find states
    for (Relation relation : searchRelations) {
        if (relation != null) {
            IFoundSetInternal set = relatedStates.get(relation.getName());
            if (set != null && set.getSize() > 0) {
                ISQLTableJoin existingJoin = (ISQLTableJoin) sqlSelect.getJoin(selectTable, relation.getName());
                BaseQueryTable foreignQTable;
                if (existingJoin == null) {
                    ITable foreignTable = parent.getFoundSetManager().getApplication().getFlattenedSolution().getTable(relation.getForeignDataSource());
                    foreignQTable = new QueryTable(foreignTable.getSQLName(), foreignTable.getDataSource(), foreignTable.getCatalog(), foreignTable.getSchema());
                } else {
                    foreignQTable = existingJoin.getForeignTable();
                }
                FindState fs = (FindState) set.getRecord(0);
                List<IRelation> nextRelations = new ArrayList<IRelation>(relations);
                nextRelations.add(relation);
                List<RelatedFindState> rfs = fs.createFindStateJoins(sqlSelect, nextRelations, foreignQTable, provider);
                if (rfs != null && rfs.size() > 0) {
                    // changed related findstate, add self with join
                    if (relatedFindStates == null) {
                        relatedFindStates = rfs;
                    } else {
                        relatedFindStates.addAll(rfs);
                    }
                    if (existingJoin == null) {
                        sqlSelect.addJoin(SQLGenerator.createJoin(parent.getFoundSetManager().getApplication().getFlattenedSolution(), relation, selectTable, foreignQTable, false, provider));
                    }
                }
            }
        }
    }
    // add yourself if you have changed or one or more related states has changed
    if (isChanged() || (relatedFindStates != null && relatedFindStates.size() > 0)) {
        if (relatedFindStates == null) {
            relatedFindStates = new ArrayList<RelatedFindState>();
        }
        relatedFindStates.add(new RelatedFindState(this, relations, selectTable));
    }
    return relatedFindStates;
}

Example 4 with QueryTable

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

the class Messages method deleteKey.

public static boolean deleteKey(String key, String i18nDatasource, String clientId, Properties settings, IDataServer dataServer, IRepository repository, IFoundSetManagerInternal fm) {
    String[] names = getServerTableNames(i18nDatasource, settings);
    String serverName = names[0];
    String tableName = names[1];
    if (serverName != null && tableName != null) {
        try {
            IServer server = repository.getServer(serverName);
            if (server == null) {
                return false;
            }
            Table table = (Table) server.getTable(tableName);
            if (table == null) {
                return false;
            }
            QueryTable messagesTable = new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema());
            // $NON-NLS-1$
            QueryColumn msgKey = new QueryColumn(messagesTable, -1, "message_key", Types.VARCHAR, 150, 0, null, 0);
            QueryDelete delete = new QueryDelete(messagesTable);
            delete.addCondition(new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, msgKey, key));
            ISQLStatement sqlStatement = new SQLStatement(ISQLActionTypes.DELETE_ACTION, serverName, tableName, null, null, delete, fm != null ? fm.getTableFilterParams(serverName, delete) : null);
            dataServer.performUpdates(clientId, new ISQLStatement[] { sqlStatement });
        } catch (Exception e) {
            return false;
        }
    }
    return true;
}

Example 5 with QueryTable

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

the class Messages method fillLocaleMessages.

private static void fillLocaleMessages(String clientId, IDataServer dataServer, Table table, String serverName, Column filterColumn, Object columnValueFilter, String searchKey, String searchText, Locale language, Properties properties, int loadingType, IFoundSetManagerInternal fm) throws ServoyException, RemoteException {
    QueryTable messagesTable = new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema());
    QuerySelect sql = new QuerySelect(messagesTable);
    // $NON-NLS-1$
    QueryColumn msgKey = new QueryColumn(messagesTable, -1, "message_key", Types.VARCHAR, 150, 0, null, 0);
    // $NON-NLS-1$
    QueryColumn msgVal = new QueryColumn(messagesTable, -1, "message_value", Types.VARCHAR, 2000, 0, null, 0);
    // $NON-NLS-1$
    QueryColumn msgLang = new QueryColumn(messagesTable, -1, "message_language", Types.VARCHAR, 150, 0, null, 0);
    sql.addColumn(msgKey);
    sql.addColumn(msgVal);
    // $NON-NLS-1$
    String condMessages = "MESSAGES";
    String langValue = (loadingType == SPECIFIED_LOCALE) ? localeToString(language) : language.getLanguage();
    // default
    sql.addCondition(condMessages, new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, msgLang, new QueryColumnValue(langValue, null)));
    if (filterColumn != null) {
        CompareCondition cc = new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, filterColumn.queryColumn(messagesTable), new QueryColumnValue(columnValueFilter, null));
        sql.addCondition(condMessages, cc);
    }
    // Filter to only include records with the default (null) value for columns flagged as Tenant column
    for (Column column : table.getTenantColumns()) {
        CompareCondition cc = new CompareCondition(IBaseSQLCondition.ISNULL_OPERATOR, column.queryColumn(messagesTable), null);
        sql.addCondition("_svy_tenant_id_filter_" + column.getName(), cc);
    }
    if (searchKey != null || searchText != null) {
        QueryTable subselectTable = new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema());
        QuerySelect subselect = new QuerySelect(subselectTable);
        // $NON-NLS-1$
        QueryColumn msgKeySub = new QueryColumn(subselectTable, -1, "message_key", Types.VARCHAR, 150, 0, null, 0);
        // $NON-NLS-1$
        QueryColumn msgValueSub = new QueryColumn(subselectTable, -1, "message_value", Types.VARCHAR, 2000, 0, null, 0);
        // $NON-NLS-1$
        QueryColumn msgLangSub = new QueryColumn(subselectTable, -1, "message_language", Types.VARCHAR, 150, 0, null, 0);
        subselect.addColumn(msgKeySub);
        // $NON-NLS-1$
        String condSearch = "SEARCH";
        if (searchKey != null) {
            subselect.addCondition(condSearch, new CompareCondition(IBaseSQLCondition.LIKE_OPERATOR, msgKeySub, new QueryColumnValue('%' + searchKey + '%', null)));
        }
        if (searchText != null) {
            subselect.addConditionOr(condSearch, new CompareCondition(IBaseSQLCondition.LIKE_OPERATOR, msgValueSub, new QueryColumnValue('%' + searchText + '%', null)));
        }
        sql.addCondition(condMessages, new SetCondition(IBaseSQLCondition.EQUALS_OPERATOR, new QueryColumn[] { msgKey }, subselect, true));
    }
    // $NON-NLS-1$
    if (Debug.tracing())
        Debug.trace("Loading messages from DB: SQL: " + sql);
    IDataSet set = dataServer.performQuery(clientId, serverName, null, sql, null, fm != null ? fm.getTableFilterParams(serverName, sql) : null, false, 0, Integer.MAX_VALUE, IDataServer.MESSAGES_QUERY);
    for (int i = 0; i < set.getRowCount(); i++) {
        Object[] row = set.getRow(i);
        if (// $NON-NLS-1$
        row[1] != null && !"".equals(row[1])) {
            properties.setProperty((String) row[0], (String) row[1]);
        }
    }
}