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

Example 26 with QueryTable

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

the class SQLGenerator method createAggregateSelect.

public static QuerySelect createAggregateSelect(QuerySelect sqlSelect, Collection<QuerySelect> aggregates, List<Column> pkColumns) {
    QuerySelect selectClone = deepClone(sqlSelect);
    selectClone.clearSorts();
    selectClone.setDistinct(false);
    selectClone.setColumns(null);
    selectClone.removeUnusedJoins(true);
    QuerySelect aggregateSqlSelect;
    if (selectClone.getJoins() == null) {
        // simple case, no joins
        // Select count(pk) from main where <condition>
        aggregateSqlSelect = selectClone;
    } else {
        // we have joins, change to an exists-query to make the aggregates correct, otherwise duplicate records
        // in the main table cause incorrect aggregate values
        // Select count(pk) from main main1 where exists (select 1 from main main2 join detail on detail.FK = main2.FK where main1.PK = main2.PK and <condition>)
        QuerySelect innerSelect = selectClone;
        ArrayList<IQuerySelectValue> innerColumns = new ArrayList<IQuerySelectValue>();
        innerColumns.add(new QueryColumnValue(Integer.valueOf(1), null, true));
        innerSelect.setColumns(innerColumns);
        BaseQueryTable innerTable = innerSelect.getTable();
        QueryTable outerTable = new QueryTable(innerTable.getName(), innerTable.getDataSource(), innerTable.getCatalogName(), innerTable.getSchemaName());
        aggregateSqlSelect = new QuerySelect(outerTable);
        for (Column column : pkColumns) {
            // $NON-NLS-1$
            innerSelect.addCondition(// $NON-NLS-1$
            "EXISTS", new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, column.queryColumn(innerTable), column.queryColumn(outerTable)));
        }
        // $NON-NLS-1$
        aggregateSqlSelect.addCondition("EXISTS", new ExistsCondition(innerSelect, true));
    }
    ArrayList<IQuerySelectValue> columns = new ArrayList<IQuerySelectValue>();
    for (QuerySelect aggregate : aggregates) {
        columns.addAll(AbstractBaseQuery.relinkTable(aggregate.getTable(), aggregateSqlSelect.getTable(), aggregate.getColumnsClone()));
    }
    aggregateSqlSelect.setColumns(columns);
    return aggregateSqlSelect;
}

Example 27 with QueryTable

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

the class I18NUtil method loadSortedMessagesFromRepository.

public static TreeMap<String, MessageEntry> loadSortedMessagesFromRepository(IRepository repository, IDataServer dataServer, String clientID, String i18NServerName, String i18NTableName, String filterName, String[] filterValue, IFoundSetManagerInternal fm) throws Exception {
    TreeMap<String, MessageEntry> sortedMessages = new TreeMap<String, MessageEntry>();
    IServer i18NServer = repository.getServer(i18NServerName);
    if (i18NServer != null) {
        Table i18NTable = (Table) i18NServer.getTable(i18NTableName);
        if (i18NTable != null) {
            QueryTable messagesTable = new QueryTable(i18NTable.getSQLName(), i18NTable.getDataSource(), i18NTable.getCatalog(), i18NTable.getSchema());
            QuerySelect sql = new QuerySelect(messagesTable);
            QueryColumn msgLang = new QueryColumn(messagesTable, -1, "message_language", Types.VARCHAR, 150, 0, null, 0);
            QueryColumn msgKey = new QueryColumn(messagesTable, -1, "message_key", Types.VARCHAR, 150, 0, null, 0);
            QueryColumn msgVal = new QueryColumn(messagesTable, -1, "message_value", Types.VARCHAR, 2000, 0, null, 0);
            sql.addColumn(msgLang);
            sql.addColumn(msgKey);
            sql.addColumn(msgVal);
            // Filter to only include records with the default (null) value for columns flagged as Tenant column
            for (Column column : i18NTable.getTenantColumns()) {
                QueryColumn tenantColumn = new QueryColumn(messagesTable, column.getID(), column.getSQLName(), column.getType(), column.getLength(), column.getScale(), null, column.getFlags());
                CompareCondition cc = new CompareCondition(IBaseSQLCondition.ISNULL_OPERATOR, tenantColumn, null);
                sql.addCondition("_svy_tenant_id_filter_" + column.getName(), cc);
            }
            if (filterName != null) {
                Column filterColumn = i18NTable.getColumn(filterName);
                if (filterColumn != null && filterValue != null && filterValue.length > 0) {
                    QueryColumn columnFilter = new QueryColumn(messagesTable, filterColumn.getID(), filterColumn.getSQLName(), filterColumn.getType(), filterColumn.getLength(), filterColumn.getScale(), null, filterColumn.getFlags());
                    CompareCondition cc = new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, columnFilter, new QueryColumnValue(filterValue[0], null));
                    // $NON-NLS-1$
                    sql.addCondition("FILTER", cc);
                }
            }
            sql.addSort(new QuerySort(msgLang, true, SortOptions.NONE));
            sql.addSort(new QuerySort(msgKey, true, SortOptions.NONE));
            IDataSet set = dataServer.performQuery(clientID, i18NServerName, null, sql, null, fm != null ? fm.getTableFilterParams(i18NServerName, sql) : null, false, 0, Integer.MAX_VALUE, IDataServer.MESSAGES_QUERY);
            int rowCount = set.getRowCount();
            if (rowCount > 0) {
                for (int i = 0; i < rowCount; i++) {
                    Object[] row = set.getRow(i);
                    MessageEntry messageEntry = new MessageEntry((String) row[0], (String) row[1], (String) row[2]);
                    sortedMessages.put(messageEntry.getLanguageKey(), messageEntry);
                }
            }
        }
    }
    return sortedMessages;
}

Example 28 with QueryTable

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

the class I18NUtil method writeMessagesToRepository.

public static void writeMessagesToRepository(String i18NServerName, String i18NTableName, IRepository repository, IDataServer dataServer, String clientID, TreeMap<String, MessageEntry> messages, boolean noUpdates, boolean noRemoves, TreeMap<String, MessageEntry> remoteMessages, String filterName, String[] filterValue, IFoundSetManagerInternal fm) throws Exception {
    // get remote messages snapshot
    if (remoteMessages == null)
        remoteMessages = loadSortedMessagesFromRepository(repository, dataServer, clientID, i18NServerName, i18NTableName, filterName, filterValue, fm);
    if (remoteMessages != null) {
        IServer i18NServer = repository.getServer(i18NServerName);
        Table i18NTable = null;
        if (i18NServer != null) {
            i18NTable = (Table) i18NServer.getTable(i18NTableName);
        }
        if (i18NTable != null) {
            // runtime exception when no ident columns
            Column pkColumn = i18NTable.getRowIdentColumns().get(0);
            QueryTable messagesTable = new QueryTable(i18NTable.getSQLName(), i18NTable.getDataSource(), i18NTable.getCatalog(), i18NTable.getSchema());
            QueryColumn pkCol = pkColumn.queryColumn(messagesTable);
            QueryColumn msgLang = new QueryColumn(messagesTable, -1, "message_language", Types.VARCHAR, 150, 0, null, 0);
            QueryColumn msgKey = new QueryColumn(messagesTable, -1, "message_key", Types.VARCHAR, 150, 0, null, 0);
            QueryColumn msgVal = new QueryColumn(messagesTable, -1, "message_value", Types.VARCHAR, 2000, 0, null, 0);
            ArrayList<SQLStatement> updateStatements = new ArrayList<SQLStatement>();
            // go thorough messages, update exiting, add news to remote
            // in case we need to insert a record, we must know if it is database managed or servoy managed
            boolean logIdIsServoyManaged = false;
            ColumnInfo ci = pkColumn.getColumnInfo();
            if (ci != null) {
                int autoEnterType = ci.getAutoEnterType();
                int autoEnterSubType = ci.getAutoEnterSubType();
                logIdIsServoyManaged = (autoEnterType == ColumnInfo.SEQUENCE_AUTO_ENTER) && (autoEnterSubType != ColumnInfo.NO_SEQUENCE_SELECTED) && (autoEnterSubType != ColumnInfo.DATABASE_IDENTITY);
            }
            List<Column> tenantColumns = i18NTable.getTenantColumns();
            Iterator<Map.Entry<String, MessageEntry>> messagesIte = messages.entrySet().iterator();
            Map.Entry<String, MessageEntry> messageEntry;
            while (messagesIte.hasNext()) {
                messageEntry = messagesIte.next();
                String key = messageEntry.getKey();
                String value = messageEntry.getValue().getValue();
                String lang = messageEntry.getValue().getLanguage();
                if (lang.equals(""))
                    lang = null;
                String messageKey = messageEntry.getValue().getKey();
                if (// insert
                !remoteMessages.containsKey(key)) {
                    QueryInsert insert = new QueryInsert(messagesTable);
                    QueryColumn[] insertColumns = null;
                    Object[] insertColumnValues = null;
                    if (logIdIsServoyManaged) {
                        Object messageId = dataServer.getNextSequence(i18NServerName, i18NTableName, pkColumn.getName(), -1, i18NServerName);
                        if (lang == null) {
                            insertColumns = new QueryColumn[] { pkCol, msgKey, msgVal };
                            insertColumnValues = new Object[] { messageId, messageKey, value };
                        } else {
                            insertColumns = new QueryColumn[] { pkCol, msgKey, msgLang, msgVal };
                            insertColumnValues = new Object[] { messageId, messageKey, lang, value };
                        }
                    } else {
                        if (lang == null) {
                            insertColumns = new QueryColumn[] { msgKey, msgVal };
                            insertColumnValues = new Object[] { messageKey, value };
                        } else {
                            insertColumns = new QueryColumn[] { msgKey, msgLang, msgVal };
                            insertColumnValues = new Object[] { messageKey, lang, value };
                        }
                    }
                    Column filterColumn = i18NTable.getColumn(filterName);
                    if (filterColumn != null && filterValue != null && filterValue.length > 0) {
                        insertColumns = Utils.arrayAdd(insertColumns, filterColumn.queryColumn(messagesTable), true);
                        insertColumnValues = Utils.arrayAdd(insertColumnValues, filterValue[0], true);
                    }
                    insert.setColumnValues(insertColumns, insertColumnValues);
                    updateStatements.add(new SQLStatement(ISQLActionTypes.INSERT_ACTION, i18NServerName, i18NTableName, null, null, insert, null));
                } else if (// update
                !remoteMessages.get(key).getValue().equals(value) && !noUpdates) {
                    QueryUpdate update = new QueryUpdate(messagesTable);
                    update.addValue(msgVal, value);
                    update.addCondition(new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, msgKey, messageKey));
                    update.addCondition(new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, msgLang, lang));
                    if (filterName != null) {
                        Column filterColumn = i18NTable.getColumn(filterName);
                        if (filterColumn != null && filterValue != null && filterValue.length > 0) {
                            QueryColumn columnFilter = filterColumn.queryColumn(messagesTable);
                            CompareCondition cc = new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, columnFilter, new QueryColumnValue(filterValue[0], null));
                            // $NON-NLS-1$
                            update.addCondition("FILTER", cc);
                        }
                    }
                    // Add condition to update only records having the default tenant value (null)
                    for (Column column : tenantColumns) {
                        QueryColumn tenantColumn = column.queryColumn(messagesTable);
                        CompareCondition cc = new CompareCondition(IBaseSQLCondition.ISNULL_OPERATOR, tenantColumn, null);
                        update.addCondition(cc);
                    }
                    updateStatements.add(new SQLStatement(ISQLActionTypes.UPDATE_ACTION, i18NServerName, i18NTableName, null, null, update, fm != null ? fm.getTableFilterParams(i18NServerName, update) : null));
                }
            }
            if (!noRemoves) {
                // go thorough remote, remove if not existing locally
                Iterator<Map.Entry<String, MessageEntry>> remoteMessagesIte = remoteMessages.entrySet().iterator();
                Map.Entry<String, MessageEntry> remoteMessageEntry;
                while (remoteMessagesIte.hasNext()) {
                    remoteMessageEntry = remoteMessagesIte.next();
                    String key = remoteMessageEntry.getKey();
                    if (// delete
                    !messages.containsKey(key)) {
                        String lang = remoteMessageEntry.getValue().getLanguage();
                        if (lang.equals(""))
                            lang = null;
                        String messageKey = remoteMessageEntry.getValue().getKey();
                        QueryDelete delete = new QueryDelete(messagesTable);
                        delete.addCondition(new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, msgKey, messageKey));
                        delete.addCondition(new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, msgLang, lang));
                        if (filterName != null) {
                            Column filterColumn = i18NTable.getColumn(filterName);
                            if (filterColumn != null && filterValue != null && filterValue.length > 0) {
                                QueryColumn columnFilter = filterColumn.queryColumn(messagesTable);
                                CompareCondition cc = new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, columnFilter, new QueryColumnValue(filterValue[0], null));
                                delete.addCondition(cc);
                            }
                        }
                        // Add condition to remove only records having the default tenant value (null)
                        for (Column column : tenantColumns) {
                            QueryColumn tenantColumn = column.queryColumn(messagesTable);
                            CompareCondition cc = new CompareCondition(IBaseSQLCondition.ISNULL_OPERATOR, tenantColumn, null);
                            delete.addCondition(cc);
                        }
                        updateStatements.add(new SQLStatement(ISQLActionTypes.DELETE_ACTION, i18NServerName, i18NTableName, null, null, delete, fm != null ? fm.getTableFilterParams(i18NServerName, delete) : null));
                    }
                }
            }
            for (SQLStatement st : updateStatements) {
                st.setDataType(ISQLStatement.I18N_DATA_TYPE);
            }
            dataServer.performUpdates(clientID, updateStatements.toArray(new ISQLStatement[updateStatements.size()]));
        }
    }
}

Example 29 with QueryTable

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

the class Messages method loadMessagesFromDatabaseRepositorySinglefilter.

private static void loadMessagesFromDatabaseRepositorySinglefilter(IServer server, Table table, String clientId, IDataServer dataServer, Properties properties, Properties localeProperties, Locale language, int loadingType, String searchKey, String searchText, Column filterColumn, String singleColumnValueFilter, IFoundSetManagerInternal fm) throws RemoteException, ServoyException {
    // $NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$
    Debug.trace("Loading messages from DB: Server: " + server.getName() + " Table: " + table.getName() + " Language: " + language);
    if (loadingType == ALL_LOCALES || loadingType == DEFAULT_LOCALE) {
        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";
        sql.addCondition(condMessages, new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR | IBaseSQLCondition.ORNULL_MODIFIER, msgLang, new QueryColumnValue("", null)));
        if (filterColumn != null) {
            QueryColumn columnFilter = filterColumn.queryColumn(messagesTable);
            CompareCondition cc = new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, columnFilter, new QueryColumnValue(singleColumnValueFilter, 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)));
            }
            // $NON-NLS-1$
            String condLang = "LANGUAGE";
            subselect.addCondition(condLang, new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, msgLangSub, new QueryColumnValue(localeToString(language), null)));
            subselect.addConditionOr(condLang, // $NON-NLS-1$
            new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR | IBaseSQLCondition.ORNULL_MODIFIER, msgLangSub, new QueryColumnValue("", 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, server.getName(), null, sql, null, fm != null ? fm.getTableFilterParams(server.getName(), sql) : null, false, 0, Integer.MAX_VALUE, IDataServer.MESSAGES_QUERY);
        for (int i = 0; i < set.getRowCount(); i++) {
            Object[] row = set.getRow(i);
            if (row[0] != null && row[1] != null) {
                properties.setProperty((String) row[0], (String) row[1]);
            }
        }
    }
    if (loadingType == ALL_LOCALES || loadingType == SPECIFIED_LANGUAGE) {
        fillLocaleMessages(clientId, dataServer, table, server.getName(), filterColumn, singleColumnValueFilter, searchKey, searchText, language, localeProperties != null ? localeProperties : properties, SPECIFIED_LANGUAGE, fm);
    }
    if (loadingType == ALL_LOCALES || loadingType == SPECIFIED_LOCALE) {
        fillLocaleMessages(clientId, dataServer, table, server.getName(), filterColumn, singleColumnValueFilter, searchKey, searchText, language, localeProperties != null ? localeProperties : properties, SPECIFIED_LOCALE, fm);
    }
}

Example 30 with QueryTable

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

the class FoundSetManager method getTableFilterParams.

/**
 * Get the table filters that are applicable on the sql for the server. Returns an array of table filters, the resulting array may be modified by the
 * caller.
 *
 * @param serverName
 * @param sql
 * @return
 */
public ArrayList<TableFilter> getTableFilterParams(String serverName, IQueryElement sql) {
    final List<TableFilter> serverFilters = tableFilterParams.get(serverName);
    Object[] tenantValue = application.getTenantValue();
    if (serverFilters == null && tenantValue == null) {
        return null;
    }
    // get the sql table names in the query
    final Set<String> tableSqlNames = new HashSet<String>();
    // find the filters for the tables found in the query
    final ArrayList<TableFilter>[] filters = new ArrayList[] { null };
    sql.acceptVisitor(o -> {
        try {
            if (o instanceof QueryTable && ((QueryTable) o).getDataSource() != null && ((QueryTable) o).getName() != null && tableSqlNames.add(((QueryTable) o).getName())) {
                QueryTable qTable = (QueryTable) o;
                Table table = (Table) getTable(qTable.getDataSource());
                if (table == null) {
                    // should never happen
                    throw new RuntimeException("Could not find table '" + qTable.getDataSource() + "' for table filters");
                }
                if (tenantValue != null) {
                    for (Column tenantColumn : table.getTenantColumns()) {
                        addFilter(filters, createTenantFilter(table, tenantColumn, tenantValue));
                    }
                }
                for (TableFilter filter : iterate(serverFilters)) {
                    TableFilterdefinition tableFilterdefinition = filter.getTableFilterdefinition();
                    if (filter.getTableName() == null && tableFilterdefinition instanceof DataproviderTableFilterdefinition) {
                        DataproviderTableFilterdefinition dataproviderTableFilterdefinition = (DataproviderTableFilterdefinition) tableFilterdefinition;
                        // filter is on all tables with specified dataProvider as column
                        Column column = table.getColumn(dataproviderTableFilterdefinition.getDataprovider());
                        if (column != null) {
                            // Use filter with table name filled in.
                            // When table was null value was not yet converted, convert now.
                            Object value = convertFilterValue(table, column, dataproviderTableFilterdefinition.getValue());
                            TableFilter useFilter = new TableFilter(filter.getName(), filter.getServerName(), table.getName(), table.getSQLName(), dataproviderTableFilterdefinition.getDataprovider(), dataproviderTableFilterdefinition.getOperator(), value);
                            addFilter(filters, useFilter);
                        }
                    } else if (filter.getTableSQLName().equals(qTable.getName())) {
                        addFilter(filters, filter);
                    }
                }
            }
        } catch (Exception e) {
            // big trouble, this is security filtering, so bail out on error
            throw new RuntimeException(e);
        }
        return o;
    });
    return filters[0];
}