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

Example 6 with QueryUpdate

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

the class JSDatabaseManager method js_mergeRecords.

// strongly recommended to use a transaction
// currently does not support compound pks
/**
 * Merge records from the same foundset, updates entire datamodel (via foreign type on columns) with destination
 * record pk, deletes source record. Do use a transaction!
 *
 * This function is very handy in situations where duplicate data exists. It allows you to merge the two records
 * and move all related records in one go. Say the source_record is "Ikea" and the combined_destination_record is "IKEA", the
 * "Ikea" record is deleted and all records related to it (think of contacts and orders, for instance) will be related
 * to the "IKEA" record.
 *
 * The function takes an optional array of column names. If provided, the data in the named columns will be copied
 * from source_record to combined_destination_record.
 *
 * Note that it is essential for both records to originate from the same foundset, as shown in the sample code.
 *
 * @sample databaseManager.mergeRecords(foundset.getRecord(1),foundset.getRecord(2));
 *
 * @param sourceRecord The source JSRecord to copy from.
 * @param combinedDestinationRecord The target/destination JSRecord to copy into.
 * @param columnNames The column names array that should be copied.
 *
 * @return true if the records could me merged.
 */
public boolean js_mergeRecords(IRecordInternal sourceRecord, IRecordInternal combinedDestinationRecord, String[] columnNames) throws ServoyException {
    checkAuthorized();
    if (sourceRecord != null && combinedDestinationRecord != null) {
        FoundSetManager fsm = (FoundSetManager) application.getFoundSetManager();
        try {
            if (sourceRecord.getParentFoundSet() != combinedDestinationRecord.getParentFoundSet()) {
                return false;
            }
            Table mainTable = (Table) combinedDestinationRecord.getParentFoundSet().getTable();
            String mainTableForeignType = mainTable.getName();
            String transaction_id = fsm.getTransactionID(mainTable.getServerName());
            Object sourceRecordPK = null;
            Object combinedDestinationRecordPK = null;
            Column pkc = null;
            Iterator<Column> pk_it = mainTable.getRowIdentColumns().iterator();
            if (pk_it.hasNext()) {
                pkc = pk_it.next();
                sourceRecordPK = sourceRecord.getValue(pkc.getDataProviderID());
                if (sourceRecordPK == null)
                    sourceRecordPK = ValueFactory.createNullValue(pkc.getType());
                combinedDestinationRecordPK = combinedDestinationRecord.getValue(pkc.getDataProviderID());
                if (combinedDestinationRecordPK == null)
                    combinedDestinationRecordPK = ValueFactory.createNullValue(pkc.getType());
                // multipk not supported
                if (pk_it.hasNext())
                    return false;
            }
            List<SQLStatement> updates = new ArrayList<SQLStatement>();
            IServer server = application.getSolution().getServer(mainTable.getServerName());
            if (server != null) {
                Iterator<String> it = server.getTableNames(false).iterator();
                while (it.hasNext()) {
                    String tableName = it.next();
                    Table table = (Table) server.getTable(tableName);
                    // not supported
                    if (table.getRowIdentColumnsCount() > 1)
                        continue;
                    Iterator<Column> it2 = table.getColumns().iterator();
                    while (it2.hasNext()) {
                        Column c = it2.next();
                        if (c.getColumnInfo() != null) {
                            if (mainTableForeignType.equalsIgnoreCase(c.getColumnInfo().getForeignType())) {
                                // update table set foreigntypecolumn = combinedDestinationRecordPK where foreigntypecolumn = sourceRecordPK
                                QueryTable qTable = new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema());
                                QueryUpdate qUpdate = new QueryUpdate(qTable);
                                QueryColumn qc = c.queryColumn(qTable);
                                qUpdate.addValue(qc, combinedDestinationRecordPK);
                                ISQLCondition condition = new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, qc, sourceRecordPK);
                                qUpdate.setCondition(condition);
                                IDataSet pks = new BufferedDataSet();
                                // unknown number of records changed
                                pks.addRow(new Object[] { ValueFactory.createTableFlushValue() });
                                SQLStatement statement = new SQLStatement(ISQLActionTypes.UPDATE_ACTION, table.getServerName(), table.getName(), pks, transaction_id, qUpdate, fsm.getTableFilterParams(table.getServerName(), qUpdate));
                                updates.add(statement);
                            }
                        }
                    }
                }
            }
            IDataSet pks = new BufferedDataSet();
            pks.addRow(new Object[] { sourceRecordPK });
            QueryTable qTable = new QueryTable(mainTable.getSQLName(), mainTable.getDataSource(), mainTable.getCatalog(), mainTable.getSchema());
            QueryDelete qDelete = new QueryDelete(qTable);
            ISQLCondition condition = new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, pkc.queryColumn(qTable), sourceRecordPK);
            qDelete.setCondition(condition);
            SQLStatement statement = new SQLStatement(ISQLActionTypes.DELETE_ACTION, mainTable.getServerName(), mainTable.getName(), pks, transaction_id, qDelete, fsm.getTableFilterParams(mainTable.getServerName(), qDelete));
            // check that the row is really deleted
            statement.setExpectedUpdateCount(1);
            updates.add(statement);
            IFoundSetInternal sfs = sourceRecord.getParentFoundSet();
            if (combinedDestinationRecord.startEditing()) {
                if (columnNames != null) {
                    for (String element : columnNames) {
                        if (element == null)
                            continue;
                        if (sfs.getColumnIndex(element) >= 0) {
                            combinedDestinationRecord.setValue(element, sourceRecord.getValue(element));
                        }
                    }
                }
                fsm.getEditRecordList().stopEditing(true, combinedDestinationRecord);
            } else {
                return false;
            }
            Object[] results = fsm.getDataServer().performUpdates(fsm.getApplication().getClientID(), updates.toArray(new ISQLStatement[updates.size()]));
            for (int i = 0; results != null && i < results.length; i++) {
                if (results[i] instanceof ServoyException) {
                    throw (ServoyException) results[i];
                }
            }
            // sfs.deleteRecord(sfs.getRecordIndex(sourceRecord), true); not needed, will be flushed from memory in finally
            return true;
        } catch (Exception ex) {
            // $NON-NLS-1$
            application.handleException(// $NON-NLS-1$
            application.getI18NMessage("servoy.foundsetupdater.updateFailed"), new ApplicationException(ServoyException.SAVE_FAILED, ex));
        } finally {
            fsm.flushCachedDatabaseData(null);
        }
    }
    return false;
}