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

Example 1 with QueryInsert

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

Example 2 with QueryInsert

use of com.servoy.j2db.query.QueryInsert 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 3 with QueryInsert

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

the class RowManager method getRowUpdateInfo.

RowUpdateInfo getRowUpdateInfo(Row row, boolean tracking) throws ServoyException {
    try {
        if (row.getRowManager() != this) {
            // $NON-NLS-1$
            throw new IllegalArgumentException("I'm not the row manager from row");
        }
        if (adjustingForChangeByOtherPKHashKey.get() != null && adjustingForChangeByOtherPKHashKey.get().equals(row.getPKHashKey())) {
            row.flagExistInDB();
            // we ignore changes here because stored calc with time element are always changed,resulting in endlessloop between clients
            return null;
        }
        if (row.getLastException() instanceof DataException) {
            // cannot update an row which is not changed (which clears the dataexception)
            return null;
        }
        if (!row.isChanged())
            return null;
        boolean mustRequeryRow = false;
        List<Column> dbPKReturnValues = new ArrayList<Column>();
        SQLSheet.SQLDescription sqlDesc = null;
        int statement_action;
        ISQLUpdate sqlUpdate = null;
        IServer server = fsm.getApplication().getSolution().getServer(sheet.getServerName());
        boolean oracleServer = SQLSheet.isOracleServer(server);
        boolean usesLobs = false;
        Table table = sheet.getTable();
        boolean doesExistInDB = row.existInDB();
        List<String> aggregatesToRemove = new ArrayList<String>(8);
        List<String> changedColumns = null;
        if (doesExistInDB) {
            statement_action = ISQLActionTypes.UPDATE_ACTION;
            sqlDesc = sheet.getSQLDescription(SQLSheet.UPDATE);
            sqlUpdate = (QueryUpdate) AbstractBaseQuery.deepClone(sqlDesc.getSQLQuery());
            List<String> req = sqlDesc.getRequiredDataProviderIDs();
            List<String> old = sqlDesc.getOldRequiredDataProviderIDs();
            Object[] olddata = row.getRawOldColumnData();
            if (// for safety only, nothing changed
            olddata == null) {
                return null;
            }
            Object[] newdata = row.getRawColumnData();
            for (int i = 0; i < olddata.length; i++) {
                String dataProviderID = req.get(i);
                Column c = table.getColumn(dataProviderID);
                ColumnInfo ci = c.getColumnInfo();
                if (ci != null && ci.isDBManaged()) {
                    mustRequeryRow = true;
                } else {
                    Object modificationValue = c.getModificationValue(fsm.getApplication());
                    if (modificationValue != null) {
                        row.setRawValue(dataProviderID, modificationValue);
                    }
                    if (newdata[i] instanceof BlobMarkerValue) {
                        // because that would be a byte[]
                        continue;
                    }
                    if (!Utils.equalObjects(olddata[i], newdata[i])) {
                        if (sheet.isUsedByAggregate(dataProviderID)) {
                            aggregatesToRemove.addAll(sheet.getAggregateName(dataProviderID));
                        }
                        Object robj = c.getAsRightType(newdata[i]);
                        if (robj == null)
                            robj = ValueFactory.createNullValue(c.getType());
                        ((QueryUpdate) sqlUpdate).addValue(c.queryColumn(((QueryUpdate) sqlUpdate).getTable()), robj);
                        if (changedColumns == null) {
                            changedColumns = new ArrayList<String>(olddata.length - i);
                        }
                        changedColumns.add(c.getName());
                        if (oracleServer && !usesLobs) {
                            int type = c.getType();
                            if (type == Types.BLOB && robj instanceof byte[] && ((byte[]) robj).length > 4000) {
                                usesLobs = true;
                            } else if (type == Types.CLOB && robj instanceof String && ((String) robj).length() > 4000) {
                                usesLobs = true;
                            }
                        }
                    }
                }
            }
            if (// nothing changed after all
            changedColumns == null) {
                // clear the old data now else it will be kept and in a changed state.
                row.flagExistInDB();
                return null;
            }
            // add PK
            Object[] pkValues = new Object[old.size()];
            for (int j = 0; j < old.size(); j++) {
                String dataProviderID = old.get(j);
                pkValues[j] = row.getOldRequiredValue(dataProviderID);
            }
            // TODO: check for success
            AbstractBaseQuery.setPlaceholderValue(sqlUpdate, new TablePlaceholderKey(((QueryUpdate) sqlUpdate).getTable(), SQLGenerator.PLACEHOLDER_PRIMARY_KEY), pkValues);
        } else {
            List<Object> argsArray = new ArrayList<Object>();
            statement_action = ISQLActionTypes.INSERT_ACTION;
            sqlDesc = sheet.getSQLDescription(SQLSheet.INSERT);
            sqlUpdate = (ISQLUpdate) AbstractBaseQuery.deepClone(sqlDesc.getSQLQuery());
            List<String> req = sqlDesc.getRequiredDataProviderIDs();
            if (Debug.tracing())
                Debug.trace(sqlUpdate.toString());
            for (int i = 0; i < req.size(); i++) {
                String dataProviderID = req.get(i);
                if (sheet.isUsedByAggregate(dataProviderID)) {
                    aggregatesToRemove.addAll(sheet.getAggregateName(dataProviderID));
                }
                Column c = table.getColumn(dataProviderID);
                QueryColumn queryColumn = c.queryColumn(((QueryInsert) sqlUpdate).getTable());
                ColumnInfo ci = c.getColumnInfo();
                if (c.isDBIdentity()) {
                    dbPKReturnValues.add(c);
                    argsArray.add(row.getDbIdentValue());
                } else if (ci != null && ci.isDBManaged()) {
                    mustRequeryRow = true;
                } else {
                    int columnIndex = getSQLSheet().getColumnIndex(dataProviderID);
                    // HACK: DIRTY way, should use some kind of identifier preferably
                    if (c.getDatabaseDefaultValue() != null && row.getRawValue(columnIndex, false) == null && c.getRowIdentType() == IBaseColumn.NORMAL_COLUMN) {
                        // The database has a default value, and the value is null, and this is an insert...
                        // Remove the column from the query entirely and make sure the default value is requeried from the db.
                        mustRequeryRow = true;
                        ((QueryInsert) sqlUpdate).removeColumn(queryColumn);
                    } else {
                        Object robj = c.getAsRightType(row.getRawValue(columnIndex, false));
                        if (robj == null)
                            robj = ValueFactory.createNullValue(c.getType());
                        argsArray.add(robj);
                        if (oracleServer && !usesLobs) {
                            int type = c.getType();
                            if (type == Types.BLOB && robj instanceof byte[] && ((byte[]) robj).length > 4000) {
                                usesLobs = true;
                            } else if (type == Types.CLOB && robj instanceof String && ((String) robj).length() > 4000) {
                                usesLobs = true;
                            }
                        }
                    }
                }
            }
            AbstractBaseQuery.setPlaceholderValue(sqlUpdate, new TablePlaceholderKey(((QueryInsert) sqlUpdate).getTable(), SQLGenerator.PLACEHOLDER_INSERT_KEY), argsArray.toArray());
        }
        Object[] pk = row.getPK();
        IDataSet pks = new BufferedDataSet();
        pks.addRow(pk);
        String tid = null;
        GlobalTransaction gt = fsm.getGlobalTransaction();
        if (gt != null) {
            tid = gt.getTransactionID(sheet.getServerName());
        }
        QuerySelect requerySelect = null;
        if (mustRequeryRow) {
            requerySelect = (QuerySelect) AbstractBaseQuery.deepClone(sheet.getSQL(SQLSheet.SELECT));
            if (!requerySelect.setPlaceholderValue(new TablePlaceholderKey(requerySelect.getTable(), SQLGenerator.PLACEHOLDER_PRIMARY_KEY), pk)) {
                Debug.error(new RuntimeException(// $NON-NLS-1$
                "Could not set placeholder " + new TablePlaceholderKey(requerySelect.getTable(), SQLGenerator.PLACEHOLDER_PRIMARY_KEY) + " in query " + requerySelect + // $NON-NLS-1$//$NON-NLS-2$
                "-- continuing"));
            }
        }
        SQLStatement statement = new SQLStatement(statement_action, sheet.getServerName(), table.getName(), pks, tid, sqlUpdate, fsm.getTableFilterParams(sheet.getServerName(), sqlUpdate), requerySelect);
        // check that the row is updated (skip check for insert)
        if (doesExistInDB)
            statement.setExpectedUpdateCount(1);
        if (changedColumns != null) {
            statement.setChangedColumns(changedColumns.toArray(new String[changedColumns.size()]));
        }
        statement.setOracleFixTrackingData(usesLobs && !tracking);
        statement.setIdentityColumn(dbPKReturnValues.size() == 0 ? null : dbPKReturnValues.get(0));
        if (tracking || usesLobs) {
            statement.setTrackingData(sheet.getColumnNames(), row.getRawOldColumnData() != null ? new Object[][] { row.getRawOldColumnData() } : null, row.getRawColumnData() != null ? new Object[][] { row.getRawColumnData() } : null, fsm.getApplication().getUserUID(), fsm.getTrackingInfo(), fsm.getApplication().getClientID());
        }
        return new RowUpdateInfo(row, statement, dbPKReturnValues, aggregatesToRemove);
    } catch (RemoteException e) {
        throw new RepositoryException(e);
    }
}

Example 4 with QueryInsert

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

the class EditRecordList method mergeInsertStatements.

/**
 * Merge the src statement into the target statement.
 */
private void mergeInsertStatements(ISQLStatement targetStatement, ISQLStatement srcStatement) {
    QueryInsert sqlUpdateTarget = (QueryInsert) targetStatement.getUpdate();
    Placeholder placeholderTarget = (Placeholder) sqlUpdateTarget.getValues();
    // Placeholder placeholderTarget = sqlUpdateTarget.getPlaceholder(new TablePlaceholderKey(sqlUpdateTarget.getTable(), SQLGenerator.PLACEHOLDER_INSERT_KEY));
    Object[][] valTarget = (Object[][]) placeholderTarget.getValue();
    QueryInsert sqlUpdateSrc = (QueryInsert) srcStatement.getUpdate();
    Placeholder placeholderSrc = (Placeholder) sqlUpdateSrc.getValues();
    // Placeholder placeholderSrc = sqlUpdateSrc.getPlaceholder(new TablePlaceholderKey(sqlUpdateSrc.getTable(), SQLGenerator.PLACEHOLDER_INSERT_KEY));
    Object[][] valSrc = (Object[][]) placeholderSrc.getValue();
    // Copy insert values into the target insert placeholder
    for (int i = 0; i < valTarget.length; i++) {
        valTarget[i] = Utils.arrayJoin(valSrc[i], valTarget[i]);
    }
    // Copy the pks into the target pks
    IDataSet targetpKs = targetStatement.getPKs();
    IDataSet srcpKs = srcStatement.getPKs();
    for (int row = 0; row < srcpKs.getRowCount(); row++) {
        targetpKs.addRow(srcpKs.getRow(row));
    }
}