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

Example 11 with Table

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

the class FoundSet method matchesMainTablename.

/**
 * Check if the tableName matches the main table.
 *
 * For example: "THECAT"."THESCHEMA"."THETABLE" matches thetable.
 */
private boolean matchesMainTablename(String tableName) {
    Table table = sheet.getTable();
    if (table.getName().equalsIgnoreCase(tableName) || table.getSQLName().equalsIgnoreCase(tableName)) {
        return true;
    }
    String[] catschemtab = tableName.split("\\.");
    if (catschemtab.length == 2) {
        // catalogorschema.table
        String catschem = dequote(catschemtab[0]);
        String tab = dequote(catschemtab[1]);
        if ((catschem.equalsIgnoreCase(table.getCatalog()) || catschem.equalsIgnoreCase(table.getSchema())) && (table.getName().equalsIgnoreCase(tab) || table.getSQLName().equalsIgnoreCase(tab))) {
            return true;
        }
    } else if (catschemtab.length == 3) {
        // catalog.schema.table
        String cat = dequote(catschemtab[0]);
        String schem = dequote(catschemtab[1]);
        String tab = dequote(catschemtab[2]);
        if (cat.equalsIgnoreCase(table.getCatalog()) && schem.equalsIgnoreCase(table.getSchema()) && (table.getName().equalsIgnoreCase(tab) || table.getSQLName().equalsIgnoreCase(tab))) {
            return true;
        }
    }
    // no match
    return false;
}

Example 12 with Table

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

the class SQLGenerator method createTableFiltercondition.

private static QueryFilter createTableFiltercondition(BaseQueryTable qTable, Table table, QuerySelect filterQuery) {
    QuerySelect filterQueryClone = deepClone(filterQuery);
    filterQueryClone.relinkTable(filterQueryClone.getTable(), qTable);
    List<QueryColumn> pkColumns = table.getRowIdentColumns().stream().map(column -> column.queryColumn(qTable)).collect(toList());
    return new QueryFilter(filterQueryClone.getJoins(), pkColumns, filterQueryClone.getWhere());
}

Example 13 with Table

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

the class SQLGenerator method createAggregates.

private void createAggregates(SQLSheet sheet, QueryTable queryTable) throws RepositoryException {
    Table table = sheet.getTable();
    Iterator<AggregateVariable> it = application.getFlattenedSolution().getAggregateVariables(table, false);
    while (it.hasNext()) {
        AggregateVariable aggregate = it.next();
        QuerySelect sql = new QuerySelect(queryTable);
        sql.addColumn(new QueryAggregate(aggregate.getType(), new QueryColumn(queryTable, -1, aggregate.getColumnNameToAggregate(), aggregate.getDataProviderType(), aggregate.getLength(), 0, null, aggregate.getFlags()), aggregate.getName()));
        sheet.addAggregate(aggregate.getDataProviderID(), aggregate.getDataProviderIDToAggregate(), sql);
    }
}

Example 14 with Table

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

the class SQLGenerator method createDynamicPKSetConditionForFoundset.

static SetCondition createDynamicPKSetConditionForFoundset(FoundSet foundSet, BaseQueryTable queryTable, IDataSet pks) {
    Table table = (Table) foundSet.getTable();
    List<Column> rowIdentColumns = table.getRowIdentColumns();
    QueryColumn[] pkQueryColumns = new QueryColumn[rowIdentColumns.size()];
    // getPrimaryKeys from table
    for (int i = 0; i < rowIdentColumns.size(); i++) {
        Column column = rowIdentColumns.get(i);
        pkQueryColumns[i] = column.queryColumn(queryTable);
    }
    // Dynamic PK condition, the special placeholder will be updated when the foundset pk set changes
    Placeholder placeHolder = new Placeholder(new TablePlaceholderKey(queryTable, SQLGenerator.PLACEHOLDER_FOUNDSET_PKS));
    placeHolder.setValue(new DynamicPkValuesArray(rowIdentColumns, pks.clone()));
    return new SetCondition(IBaseSQLCondition.EQUALS_OPERATOR, pkQueryColumns, placeHolder, true);
}

Example 15 with Table

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

the class JSFoundSetUpdater method js_performUpdate.

/**
 * Do the actual update in the database, returns true if successful. It will first try to save all editing records (from all foundsets), if cannot save will return false before doing the update.
 * There are 3 types of possible use with the foundset updater
 * 1) update entire foundset by a single sql statement; that is not possible when the table of the foundset has tracking enabled then it will loop over the whole foundset.
 *    When a single sql statement is done, modification columns will not be updated and associated Table Events won't be triggered, because it does the update directly in the database, without getting the records.
 *   NOTE: this mode will refresh all foundsets based on same datasource
 * 2) update part of foundset, for example the first 4 row (starts with selected row)
 * 3) safely loop through foundset (starts with selected row)
 *
 * after the perform update call there are no records in edit mode, that where not already in edit mode, because all of them are saved directly to the database,
 * or in mode 1 the records are not touched at all and the database is updated directly.
 *
 * @sample
 * //1) update entire foundset
 * var fsUpdater = databaseManager.getFoundSetUpdater(foundset)
 * fsUpdater.setColumn('customer_type',1)
 * fsUpdater.setColumn('my_flag',0)
 * fsUpdater.performUpdate()
 *
 * //2) update part of foundset, for example the first 4 row (starts with selected row)
 * var fsUpdater = databaseManager.getFoundSetUpdater(foundset)
 * fsUpdater.setColumn('customer_type',new Array(1,2,3,4))
 * fsUpdater.setColumn('my_flag',new Array(1,0,1,0))
 * fsUpdater.performUpdate()
 *
 * //3) safely loop through foundset (starts with selected row)
 * controller.setSelectedIndex(1)
 * var count = 0
 * var fsUpdater = databaseManager.getFoundSetUpdater(foundset)
 * while(fsUpdater.next())
 * {
 * 	fsUpdater.setColumn('my_flag',count++)
 * }
 *
 * @return true if succeeded, false if failed.
 */
public boolean js_performUpdate() throws ServoyException {
    if (list.size() == 0 || foundset.getTable() == null) {
        return false;
    }
    if (!foundset.hasAccess(IRepository.UPDATE)) {
        throw new ApplicationException(ServoyException.NO_MODIFY_ACCESS, new Object[] { foundset.getTable().getName() });
    }
    // first stop all edits, 'force' stop the edit by saying that it is a javascript stop
    if (application.getFoundSetManager().getEditRecordList().stopEditing(true) != ISaveConstants.STOPPED)
        return false;
    try {
        QuerySelect sqlParts;
        IDataSet currentPKs;
        synchronized (foundset.getPksAndRecords()) {
            sqlParts = foundset.getPksAndRecords().getQuerySelectForReading();
            currentPKs = foundset.getPksAndRecords().getPks();
        }
        FoundSetManager fsm = (FoundSetManager) application.getFoundSetManager();
        if (rowsToUpdate == -1 && !foundset.hasAccess(IRepository.TRACKING) && sqlParts.getJoins() == null && // does not have join to other table
        !fsm.hasTableFiltersWithJoins(foundset.getTable().getServerName(), sqlParts)) {
            // all rows at once, via sql
            Table table = (Table) foundset.getTable();
            SQLSheet sheet = foundset.getSQLSheet();
            QueryUpdate sqlUpdate = new QueryUpdate(sqlParts.getTable());
            for (Pair<String, Object> p : list) {
                String name = p.getLeft();
                Object val = p.getRight();
                int columnIndex = sheet.getColumnIndex(name);
                VariableInfo variableInfo = sheet.getCalculationOrColumnVariableInfo(name, columnIndex);
                if (// do not convert null to 0 incase of numbers, this means the calcs the value whould change each time //$NON-NLS-1$
                val != null && !("".equals(val) && Column.mapToDefaultType(variableInfo.type) == IColumnTypes.TEXT)) {
                    val = sheet.convertObjectToValue(name, val, foundset.getFoundSetManager().getColumnConverterManager(), foundset.getFoundSetManager().getColumnValidatorManager(), null);
                }
                Column c = table.getColumn(name);
                if (val == null) {
                    val = ValueFactory.createNullValue(c.getType());
                }
                sqlUpdate.addValue(c.queryColumn(sqlParts.getTable()), val);
            }
            sqlUpdate.setCondition(sqlParts.getWhereClone());
            IDataSet pks;
            boolean allFoundsetRecordsLoaded = currentPKs != null && currentPKs.getRowCount() <= fsm.config.pkChunkSize() && !currentPKs.hadMoreRows();
            if (allFoundsetRecordsLoaded) {
                pks = currentPKs;
            } else {
                pks = new BufferedDataSet();
                pks.addRow(new Object[] { ValueFactory.createTableFlushValue() });
            }
            String transaction_id = fsm.getTransactionID(foundset.getSQLSheet());
            try {
                SQLStatement statement = new SQLStatement(ISQLActionTypes.UPDATE_ACTION, table.getServerName(), table.getName(), pks, transaction_id, sqlUpdate, fsm.getTableFilterParams(table.getServerName(), sqlUpdate));
                if (allFoundsetRecordsLoaded) {
                    statement.setExpectedUpdateCount(pks.getRowCount());
                }
                Object[] results = fsm.getDataServer().performUpdates(fsm.getApplication().getClientID(), new ISQLStatement[] { statement });
                for (int i = 0; results != null && i < results.length; i++) {
                    if (results[i] instanceof ServoyException) {
                        if (((ServoyException) results[i]).getErrorCode() == ServoyException.UNEXPECTED_UPDATE_COUNT) {
                            performLoopUpdate();
                            clear();
                            return true;
                        }
                        fsm.flushCachedDatabaseData(fsm.getDataSource(table));
                        throw (ServoyException) results[i];
                    }
                }
                fsm.flushCachedDatabaseData(fsm.getDataSource(table));
                clear();
                return true;
            } catch (ApplicationException aex) {
                if (allFoundsetRecordsLoaded || aex.getErrorCode() != ServoyException.RECORD_LOCKED) {
                    throw aex;
                }
                // a record was locked by another client, try per-record
                Debug.log("foundsetUpdater could not update all records in 1 statement (a record may be locked), trying per-record");
            }
        }
        performLoopUpdate();
    } catch (Exception ex) {
        // $NON-NLS-1$
        application.handleException(// $NON-NLS-1$
        application.getI18NMessage("servoy.foundsetupdater.updateFailed"), new ApplicationException(ServoyException.SAVE_FAILED, ex));
        return false;
    }
    clear();
    return true;
}