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

Example 1 with QuerySelect

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

Example 2 with QuerySelect

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

the class Messages method loadMessagesFromDatabaseRepositoryInternal.

/**
 * CURRENTLY FOR INTERNAL USE ONLY, DO NOT CALL.
 *
 * @exclude
 */
public static void loadMessagesFromDatabaseRepositoryInternal(String i18nDatasource, String clientId, Properties settings, IDataServer dataServer, IRepository repository, Properties properties, Properties localeProperties, Locale language, int loadingType, String searchKey, String searchText, String columnNameFilter, String[] columnValueFilter, IFoundSetManagerInternal fm) {
    noConnection = false;
    String[] names = getServerTableNames(i18nDatasource, settings);
    String serverName = names[0];
    String tableName = names[1];
    if (serverName == null || tableName == null || "".equals(serverName) || "".equals(tableName)) {
        noConnection = true;
        return;
    }
    try {
        // $NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$
        Debug.trace("Loading messages from DB: Server: " + serverName + " Table: " + tableName + " Language: " + language);
        IServer server = repository.getServer(serverName);
        if (server == null) {
            noConnection = true;
            return;
        }
        Table table = (Table) server.getTable(tableName);
        if (table == null) {
            noConnection = true;
            return;
        }
        Column filterColumn = null;
        if (columnNameFilter != null) {
            // check if column exists
            filterColumn = table.getColumn(columnNameFilter);
            if (filterColumn == null) {
                return;
            }
        }
        String[] iColumnValueFilter = columnValueFilter;
        boolean isColumnValueFilterChanged = false;
        if (filterColumn == null && fm != null) {
            QueryTable messagesTable = new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema());
            QuerySelect sql = new QuerySelect(messagesTable);
            ArrayList<TableFilter> tableFilters = fm.getTableFilterParams(serverName, sql);
            if (tableFilters != null) {
                for (TableFilter tableFilter : tableFilters) {
                    TableFilterdefinition tableFilterdefinition = tableFilter.getTableFilterdefinition();
                    if (tableFilterdefinition instanceof DataproviderTableFilterdefinition) {
                        DataproviderTableFilterdefinition dpTtableFilterdefinition = (DataproviderTableFilterdefinition) tableFilterdefinition;
                        Object value = dpTtableFilterdefinition.getValue();
                        if (value instanceof Object[]) {
                            filterColumn = table.getColumn(dpTtableFilterdefinition.getDataprovider());
                            iColumnValueFilter = new String[((Object[]) value).length];
                            for (int i = 0; i < ((Object[]) value).length; i++) {
                                iColumnValueFilter[i] = ((Object[]) value)[i] != null ? ((Object[]) value)[i].toString() : null;
                            }
                            isColumnValueFilterChanged = true;
                            break;
                        }
                    }
                }
            }
        }
        if (!isColumnValueFilterChanged) {
            loadMessagesFromDatabaseRepositorySinglefilter(server, table, clientId, dataServer, properties, localeProperties, language, loadingType, searchKey, searchText, filterColumn, null, fm);
        }
        if (iColumnValueFilter != null) {
            for (int i = iColumnValueFilter.length - 1; i >= 0; i--) {
                loadMessagesFromDatabaseRepositorySinglefilter(server, table, clientId, dataServer, properties, localeProperties, language, loadingType, searchKey, searchText, filterColumn, iColumnValueFilter[i], fm);
            }
        }
    } catch (Exception e) {
        // $NON-NLS-1$
        Debug.error("Couldn't get the default messages");
        Debug.error(e);
        invalidConnection = true;
    }
}

Example 3 with QuerySelect

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

the class QBWhereCondition method getCondition.

/**
 * Get a named condition in the query where-clause.
 *
 * @param name The condition name.
 *
 * @sampleas conditionnames()
 */
@JSFunction
public QBCondition getCondition(String name) {
    QuerySelect query = getRoot().getQuery(false);
    AndCondition condition = query == null ? null : query.getCondition(name);
    return condition == null ? null : new QBCondition(getRoot(), getParent(), AbstractBaseQuery.deepClone(condition));
}

Example 4 with QuerySelect

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

the class FormPreviewPanel method process.

// build the chain and fill the renderers,returns number of pages
public int process() throws Exception {
    // clear
    root = null;
    // set size of this panel
    orgWidth = new Dimension((int) (currentPageFormat.getWidth() * (1 / factor)), (int) (currentPageFormat.getHeight() * (1 / factor)));
    applySize();
    part_panels = createPartPanels();
    Form form = controllerBeingPreviewed.getForm();
    // otherwise you cannot print multiple columns   (int) (application.getPageFormat().getImageableWidth()*(1/factor));
    int w = form.getWidth();
    try {
        // $NON-NLS-1$
        application.getRuntimeProperties().put("isPrinting", Boolean.TRUE);
        Map componentsUsingSliding = application.getDataRenderFactory().completeRenderers(application, form, controllerBeingPreviewed.getScriptExecuter(), part_panels, w, true, null, null);
        PropertyCopy.copyExistingPrintableProperties(application, controllerBeingPreviewed, part_panels);
        Iterator<DataRenderer> panels = part_panels.values().iterator();
        while (panels.hasNext()) {
            DataRenderer panel = panels.next();
            panel.setComponentsUsingSliding(componentsUsingSliding);
            DataRendererFactory.addSpringsBetweenComponents(application, panel);
        }
        // $NON-NLS-1$
        Debug.trace("usesSliding " + (componentsUsingSliding.size() != 0));
    } finally {
        // $NON-NLS-1$
        application.getRuntimeProperties().put("isPrinting", null);
    }
    // create list
    renderParent = application.getPrintingRendererParent();
    plist = new PageList(application, this, renderParent);
    PartNode node = null;
    // create the chain based on the sort,LAST node must be the body part (is virtal added if not present)
    Part body = null;
    FormController fp = ((FormManager) application.getFormManager()).leaseFormPanel(controllerBeingPreviewed.getName());
    if (fp != null && !fp.isShowingData()) {
        // List lst = fp.getFormModel().getLastSearchColumns();
        if (fp.wantEmptyFoundSet()) {
            if (fp.getFormModel() != null)
                fp.getFormModel().clear();
        } else {
            fp.loadAllRecords();
        }
    // fp.getFormModel().sort(lst);
    }
    List<SortColumn> sortColumns = ((FoundSet) formData).getLastSortColumns();
    if (formData.getSize() != 0) {
        if (sortColumns != null) {
            Set<String> consumed = new HashSet<String>();
            for (int i = 0; i < sortColumns.size(); i++) {
                SortColumn sc = sortColumns.get(i);
                Iterator<Part> it = part_panels.keySet().iterator();
                while (it.hasNext()) {
                    Part part = it.next();
                    DataRenderer dr = part_panels.get(part);
                    if (part.getPartType() == Part.BODY) {
                        body = part;
                        continue;
                    }
                    if (part.getPartType() != Part.LEADING_SUBSUMMARY && part.getPartType() != Part.TRAILING_SUBSUMMARY) {
                        IRecordInternal state = new PageNumberState(formData, plist);
                        plist.setNonRepeatingPart(part.getPartType(), new DataRendererDefinition(this, renderParent, part, dr, state));
                        continue;
                    }
                    boolean match = false;
                    int inlineCount = 0;
                    List<SortColumn> partSortColumns = new ArrayList<SortColumn>();
                    SortColumn lastMatch = sc;
                    String groupByDataproviders = part.getGroupbyDataProviderIDs() != null ? part.getGroupbyDataProviderIDs() : "";
                    // $NON-NLS-1$ //$NON-NLS-2$
                    StringTokenizer tk = new StringTokenizer("" + groupByDataproviders.toLowerCase(), ", ");
                    int tokenCount = tk.countTokens();
                    String[] ids = new String[tokenCount];
                    for (; inlineCount < tokenCount; inlineCount++) {
                        String id = tk.nextToken();
                        ids[inlineCount] = id;
                        if (lastMatch.getDataProviderID().equals(id)) {
                            partSortColumns.add(lastMatch);
                            if ((i + inlineCount + 1) < sortColumns.size()) {
                                lastMatch = sortColumns.get(i + inlineCount + 1);
                                if (part.getPartType() == Part.LEADING_SUBSUMMARY && consumed.contains(lastMatch)) {
                                    break;
                                }
                            } else {
                                break;
                            }
                        } else {
                            break;
                        }
                    }
                    if (// did all match?
                    tokenCount > 0 && partSortColumns.size() == tokenCount) {
                        match = true;
                        if (part.getPartType() == Part.LEADING_SUBSUMMARY) {
                            for (String element : ids) {
                                consumed.add(element);
                            }
                        }
                    }
                    if (match) {
                        SortColumn[] array = new SortColumn[partSortColumns.size()];
                        partSortColumns.toArray(array);
                        if (// create root
                        root == null) {
                            root = new PartNode(this, part, dr, renderParent, array);
                            node = root;
                        } else {
                            if (!tryToPlaceInExistingNodes(part, dr, array)) {
                                PartNode newNode = new PartNode(this, part, dr, renderParent, array);
                                node.setChild(newNode);
                                node = newNode;
                            }
                        }
                    }
                }
            }
            PartNode newNode = null;
            if (body == null) {
                // a virtual body (when no body is placed in the parts)
                newNode = new PartNode(this, null, null, renderParent, null);
            } else {
                // the body
                newNode = new PartNode(this, body, part_panels.get(body), renderParent, null);
            }
            if (node != null) {
                node.setChild(newNode);
            } else {
                root = newNode;
            }
        } else // no sort...
        {
            if (// search for body
            body == null) {
                Iterator<Part> it = part_panels.keySet().iterator();
                while (it.hasNext()) {
                    Part part = it.next();
                    DataRenderer dr = part_panels.get(part);
                    IRecordInternal state = new PageNumberState(formData, plist);
                    if (part.getPartType() == Part.BODY) {
                        body = part;
                        continue;
                    }
                    if (part.getPartType() != Part.LEADING_SUBSUMMARY && part.getPartType() != Part.TRAILING_SUBSUMMARY) {
                        plist.setNonRepeatingPart(part.getPartType(), new DataRendererDefinition(this, renderParent, part, dr, state));
                        continue;
                    }
                }
            }
            if (body == null) {
                // a virtual body (when no body is placed in the parts)
                root = new PartNode(this, null, null, renderParent, null);
            } else // if (body != null)
            {
                // the body
                root = new PartNode(this, body, part_panels.get(body), renderParent, null);
            }
        }
    }
    try {
        // $NON-NLS-1$
        application.getRuntimeProperties().put("isPrinting", Boolean.TRUE);
        long t1 = System.currentTimeMillis();
        // fill the renderers with data
        if (root != null) {
            // dump chain
            // $NON-NLS-1$
            Debug.trace("Root " + root);
            QuerySelect sqlString = ((FoundSet) formData).getQuerySelectForReading();
            Table table = formData.getSQLSheet().getTable();
            FoundSet fs = (FoundSet) ((FoundSetManager) application.getFoundSetManager()).getNewFoundSet(table, null, sortColumns);
            fs.browseAll(sqlString);
            long t3 = System.currentTimeMillis();
            List<DataRendererDefinition> childRetval = root.process(this, fs, table, sqlString);
            long t4 = System.currentTimeMillis();
            if (Debug.tracing()) {
                // $NON-NLS-1$ //$NON-NLS-2$
                Debug.trace("Database queries took " + ((t4 - t3) / 1000f) + " second");
            }
            if (childRetval != null) {
                for (int i = 0; i < childRetval.size(); i++) {
                    plist.addPanel(childRetval.get(i));
                }
            }
        }
        plist.finish();
        long t2 = System.currentTimeMillis();
        int pageCount = plist.getNumberOfPages();
        // dump
        if (Debug.tracing()) {
            Debug.trace(plist);
            // $NON-NLS-1$ //$NON-NLS-2$
            Debug.trace("Generated " + pageCount / ((t2 - t1) / 1000f) + " printable pages per second");
        }
    } finally {
        // $NON-NLS-1$
        application.getRuntimeProperties().put("isPrinting", null);
    }
    renderParent.removeAll();
    return plist.getNumberOfPages();
}

Example 5 with QuerySelect

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

the class QueryBuilderSerializer method marshall.

public Object marshall(SerializerState state, Object p, Object o) throws MarshallException {
    if (!(o instanceof QBSelect)) {
        throw new MarshallException("QueryBuilderSerializer cannot marshall class " + o.getClass());
    }
    QBSelect qbSelect = (QBSelect) o;
    QuerySelect query = qbSelect.getQuery(false);
    // make sure that queries are serialized in full, standard serialization optimizations result in missing data that cannot be resolved
    Writer writer = new StringWriter();
    getXstream().marshal(query, new CompactWriter(writer));
    String xml = writer.toString();
    JSONObject obj = new JSONObject();
    try {
        if (ser.getMarshallClassHints()) {
            obj.put("javaClass", o.getClass().getName());
        }
        // required
        obj.put("query", xml);
        // required
        obj.put("datasource", qbSelect.getDataSource());
        // optional
        obj.put("alias", qbSelect.getTableAlias());
    } catch (JSONException e) {
        throw new MarshallException(e.getMessage(), e);
    }
    return obj;
}