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

Example 21 with QueryTable

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

the class MetaDataUtils method loadMetadataInTable.

public static int loadMetadataInTable(ITable table, String json) throws IOException, ServoyException, JSONException {
    // parse dataset
    BufferedDataSet dataSet = MetaDataUtils.deserializeTableMetaDataContents(json);
    // check if all columns exist
    List<String> missingColumns = null;
    for (String colname : dataSet.getColumnNames()) {
        if (table.getColumn(colname) == null) {
            if (missingColumns == null) {
                missingColumns = new ArrayList<String>();
            }
            missingColumns.add(colname);
        }
    }
    if (missingColumns != null) {
        StringBuilder message = new StringBuilder("Missing columns from meta data for table '").append(table.getName()).append("'").append(" in server '").append(table.getServerName()).append("' : ");
        for (String name : missingColumns) {
            message.append('\'').append(name).append("' ");
        }
        throw new RepositoryException(message.toString());
    }
    // delete existing data
    ApplicationServerRegistry.get().getDataServer().performUpdates(ApplicationServerRegistry.get().getClientId(), new ISQLStatement[] { new // 
    SQLStatement(// 
    IDataServer.META_DATA_QUERY, // 
    table.getServerName(), // 
    table.getName(), // 
    null, // delete entire table
    new QueryDelete(new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema()))) });
    // insert the data
    ApplicationServerRegistry.get().getDataServer().insertDataSet(ApplicationServerRegistry.get().getClientId(), dataSet, table.getDataSource(), table.getServerName(), table.getName(), null, null, null, null);
    return dataSet.getRowCount();
}

Example 22 with QueryTable

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

the class FoundSetManager method getTableCount.

public int getTableCount(ITable table) {
    if (table != null) {
        try {
            long time = System.currentTimeMillis();
            IDataServer ds = application.getDataServer();
            String transaction_id = getTransactionID(table.getServerName());
            QuerySelect countSelect = new QuerySelect(new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema()));
            // $NON-NLS-1$
            countSelect.addColumn(new QueryAggregate(QueryAggregate.COUNT, new QueryColumnValue(Integer.valueOf(1), "n", true), null));
            IDataSet set = ds.performQuery(application.getClientID(), table.getServerName(), transaction_id, countSelect, null, getTableFilterParams(table.getServerName(), countSelect), false, 0, 10, IDataServer.FOUNDSET_LOAD_QUERY);
            if (Debug.tracing()) {
                Debug.trace(// $NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$
                "Table count time: " + (System.currentTimeMillis() - time) + " thread: " + Thread.currentThread().getName() + ", SQL: " + countSelect.toString());
            }
            if (set.getRowCount() > 0) {
                Object[] row = set.getRow(0);
                if (row.length > 0) {
                    return Utils.getAsInteger(row[0]);
                }
            }
        } catch (Exception e) {
            Debug.error(e);
        }
    }
    return -1;
}

Example 23 with QueryTable

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

the class SQLGenerator method createExistsCondition.

public static ISQLCondition createExistsCondition(IDataProviderHandler flattenedSolution, QuerySelect sqlSelect, ISQLCondition condition, List<IRelation> relations, BaseQueryTable columnTable, IGlobalValueEntry provider, BaseQueryColumn[] pkQueryColumns) throws RepositoryException {
    // exists (select 1 from innermain join related1 ... join relatedn where innermain.pk = main.pk having aggregate(relatedn))
    if (relations.size() == 0) {
        // searching for aggregate in main table, does no make sense.. ignore in search
        return null;
    }
    QuerySelect existsSelect = new QuerySelect(new QueryTable(sqlSelect.getTable().getName(), sqlSelect.getTable().getDataSource(), sqlSelect.getTable().getCatalogName(), sqlSelect.getTable().getSchemaName()));
    existsSelect.addColumn(new QueryColumnValue(Integer.valueOf(1), null, true));
    // innermain.pk = main.pk
    QueryColumn[] innerPkColumns = new QueryColumn[pkQueryColumns.length];
    for (int p = 0; p < pkQueryColumns.length; p++) {
        BaseQueryColumn pk = pkQueryColumns[p];
        innerPkColumns[p] = new QueryColumn(existsSelect.getTable(), pk.getId(), pk.getName(), pk.getColumnType().getSqlType(), pk.getColumnType().getLength(), pk.getColumnType().getScale(), pk.getNativeTypename(), pk.getFlags(), pk.isIdentity());
        // group by on the inner pk, some dbs (hxtt dbf) require that
        existsSelect.addGroupBy(innerPkColumns[p]);
    }
    existsSelect.addCondition("AGGREGATE-SEARCH", new // $NON-NLS-1$
    SetCondition(// $NON-NLS-1$
    new int[] { IBaseSQLCondition.EQUALS_OPERATOR }, // $NON-NLS-1$
    innerPkColumns, pkQueryColumns, true));
    // add the joins
    BaseQueryTable prevTable = existsSelect.getTable();
    for (IRelation relation : relations) {
        ITable foreignTable = flattenedSolution.getTable(relation.getForeignDataSource());
        QueryTable foreignQtable = new QueryTable(foreignTable.getSQLName(), foreignTable.getDataSource(), foreignTable.getCatalog(), foreignTable.getSchema());
        existsSelect.addJoin(createJoin(flattenedSolution, relation, prevTable, foreignQtable, true, provider));
        prevTable = foreignQtable;
    }
    existsSelect.addHaving(AbstractBaseQuery.relinkTable(columnTable, prevTable, condition));
    return new ExistsCondition(existsSelect, true);
}

Example 24 with QueryTable

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

the class SQLGenerator method createAggregateSelect.

/**
 * Create the sql for a single aggregate on a column.
 *
 * @param aggregee
 * @return
 */
public static QuerySelect createAggregateSelect(int aggregateType, ITable table, Object aggregee) {
    Column column = null;
    if (aggregee instanceof Column) {
        column = (Column) aggregee;
    }
    QuerySelect select = new QuerySelect(new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema()));
    select.addColumn(new QueryAggregate(aggregateType, // $NON-NLS-1$
    (column == null) ? // $NON-NLS-1$
    (IQuerySelectValue) new QueryColumnValue(aggregee, "n", aggregee instanceof Integer || QueryAggregate.ASTERIX.equals(aggregee)) : column.queryColumn(select.getTable()), // $NON-NLS-1$
    "maxval"));
    return select;
}

Example 25 with QueryTable

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

the class SQLGenerator method createTableSQL.

private SQLSheet createTableSQL(String dataSource, boolean cache) throws ServoyException {
    if (dataSource == null) {
        return createNoTableSQL(cache);
    }
    Table table = (Table) application.getFoundSetManager().getTable(dataSource);
    if (table == null) {
        // $NON-NLS-1$
        throw new RepositoryException("Cannot create sql: table not found for data source '" + dataSource + '\'');
    }
    SQLSheet retval = new SQLSheet(application, table.getServerName(), table);
    // never remove this line, due to recursive behaviour, register a state when immediately!
    if (cache)
        cachedDataSourceSQLSheets.put(dataSource, retval);
    QueryTable queryTable = new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema());
    QuerySelect select = new QuerySelect(queryTable);
    QueryDelete delete = new QueryDelete(queryTable);
    QueryInsert insert = new QueryInsert(queryTable);
    QueryUpdate update = new QueryUpdate(queryTable);
    List<Column> columns = new ArrayList<Column>();
    Iterator<Column> it1 = table.getColumns().iterator();
    while (it1.hasNext()) {
        Column c = it1.next();
        ColumnInfo ci = c.getColumnInfo();
        if (ci != null && ci.isExcluded()) {
            continue;
        }
        columns.add(c);
    }
    List<String> requiredDataProviderIDs = new ArrayList<String>();
    Iterator<Column> pks = table.getRowIdentColumns().iterator();
    if (!pks.hasNext()) {
        throw new RepositoryException(ServoyException.InternalCodes.PRIMARY_KEY_NOT_FOUND, new Object[] { table.getName() });
    }
    List<QueryColumn> pkQueryColumns = new ArrayList<QueryColumn>();
    while (pks.hasNext()) {
        Column column = pks.next();
        if (!columns.contains(column))
            columns.add(column);
        requiredDataProviderIDs.add(column.getDataProviderID());
        pkQueryColumns.add(column.queryColumn(queryTable));
    }
    Iterator<Column> it2 = columns.iterator();
    select.setColumns(makeQueryColumns(it2, queryTable, insert));
    SetCondition pkSelect = new SetCondition(IBaseSQLCondition.EQUALS_OPERATOR, pkQueryColumns.toArray(new QueryColumn[pkQueryColumns.size()]), new Placeholder(new TablePlaceholderKey(queryTable, PLACEHOLDER_PRIMARY_KEY)), true);
    select.setCondition(CONDITION_SEARCH, pkSelect);
    delete.setCondition(deepClone(pkSelect));
    update.setCondition(deepClone(pkSelect));
    // fill dataprovider map
    List<String> dataProviderIDsDilivery = new ArrayList<String>();
    for (Column col : columns) {
        dataProviderIDsDilivery.add(col.getDataProviderID());
    }
    retval.addSelect(select, dataProviderIDsDilivery, requiredDataProviderIDs, null);
    retval.addDelete(delete, requiredDataProviderIDs);
    retval.addInsert(insert, dataProviderIDsDilivery);
    retval.addUpdate(update, dataProviderIDsDilivery, requiredDataProviderIDs);
    // related stuff
    createAggregates(retval, queryTable);
    return retval;
}