Examples with SQLQuery org.jkiss.dbeaver.model.sql.SQLQuery used on opensource projects

Example 6 with SQLQuery

use of org.jkiss.dbeaver.model.sql.SQLQuery in project dbeaver by serge-rider.

the class SQLQueryJob method run.

@Override
protected IStatus run(DBRProgressMonitor monitor) {
    RuntimeUtils.setThreadName("SQL script execution");
    statistics = new DBCStatistics();
    try {
        DBCExecutionContext context = getExecutionContext();
        DBCTransactionManager txnManager = DBUtils.getTransactionManager(context);
        DBCExecutionPurpose purpose = queries.size() > 1 ? DBCExecutionPurpose.USER_SCRIPT : DBCExecutionPurpose.USER;
        try (DBCSession session = context.openSession(monitor, purpose, "SQL Query")) {
            // Set transaction settings (only if autocommit is off)
            QMUtils.getDefaultHandler().handleScriptBegin(session);
            boolean oldAutoCommit = txnManager == null || txnManager.isAutoCommit();
            boolean newAutoCommit = (commitType == SQLScriptCommitType.AUTOCOMMIT);
            if (txnManager != null && !oldAutoCommit && newAutoCommit) {
                txnManager.setAutoCommit(monitor, true);
            }
            monitor.beginTask(this.getName(), queries.size());
            // Notify job start
            if (listener != null) {
                try {
                    listener.onStartScript();
                } catch (Exception e) {
                    log.error(e);
                }
            }
            resultSetNumber = 0;
            for (int queryNum = 0; queryNum < queries.size(); ) {
                // Execute query
                SQLQuery query = queries.get(queryNum);
                fetchResultSetNumber = resultSetNumber;
                boolean runNext = executeSingleQuery(session, query, true);
                if (!runNext) {
                    // Ask to continue
                    if (lastError != null) {
                        log.error(lastError);
                    }
                    boolean isQueue = queryNum < queries.size() - 1;
                    ExecutionQueueErrorResponse response = ExecutionQueueErrorJob.showError(isQueue ? "SQL script execution" : "SQL query execution", lastError, isQueue);
                    boolean stopScript = false;
                    switch(response) {
                        case STOP:
                            // just stop execution
                            stopScript = true;
                            break;
                        case RETRY:
                            // just make it again
                            continue;
                        case IGNORE:
                            // Just do nothing
                            break;
                        case IGNORE_ALL:
                            errorHandling = SQLScriptErrorHandling.IGNORE;
                            break;
                    }
                    if (stopScript) {
                        break;
                    }
                }
                // Check monitor
                if (monitor.isCanceled()) {
                    break;
                }
                monitor.worked(1);
                queryNum++;
            }
            showExecutionResult(session);
            monitor.done();
            // Commit data
            if (txnManager != null && !oldAutoCommit && commitType != SQLScriptCommitType.AUTOCOMMIT) {
                if (lastError == null || errorHandling == SQLScriptErrorHandling.STOP_COMMIT) {
                    if (commitType != SQLScriptCommitType.NO_COMMIT) {
                        monitor.beginTask("Commit data", 1);
                        txnManager.commit(session);
                        monitor.done();
                    }
                } else {
                    monitor.beginTask("Rollback data", 1);
                    txnManager.rollback(session, null);
                    monitor.done();
                }
            }
            // Restore transactions settings
            if (txnManager != null && !oldAutoCommit && newAutoCommit) {
                txnManager.setAutoCommit(monitor, false);
            }
            QMUtils.getDefaultHandler().handleScriptEnd(session);
            // Return success
            return new Status(Status.OK, DBeaverCore.getCorePluginID(), "SQL job completed");
        }
    } catch (Throwable ex) {
        return new Status(Status.ERROR, DBeaverCore.getCorePluginID(), "Error during SQL job execution: " + ex.getMessage());
    } finally {
        // Notify job end
        if (listener != null) {
            try {
                listener.onEndScript(statistics, lastError != null);
            } catch (Exception e) {
                log.error(e);
            }
        }
    }
}

Example 7 with SQLQuery

use of org.jkiss.dbeaver.model.sql.SQLQuery in project dbeaver by dbeaver.

the class SQLQueryTransformerAllRows method transformQuery.

@Override
public SQLQuery transformQuery(SQLDataSource dataSource, SQLSyntaxManager syntaxManager, SQLQuery query) throws DBException {
    SQLQuery allRowsQuery = new SQLQuery(dataSource, query.getText(), query);
    allRowsQuery.setResultSetLimit(0, 0);
    return allRowsQuery;
}

Example 8 with SQLQuery

use of org.jkiss.dbeaver.model.sql.SQLQuery in project dbeaver by dbeaver.

the class ResultSetUtils method bindAttributes.

public static void bindAttributes(@NotNull DBCSession session, @Nullable DBCResultSet resultSet, @NotNull DBDAttributeBindingMeta[] bindings, @Nullable List<Object[]> rows) throws DBException {
    final DBRProgressMonitor monitor = session.getProgressMonitor();
    final DBPDataSource dataSource = session.getDataSource();
    boolean readMetaData = dataSource.getContainer().getPreferenceStore().getBoolean(DBeaverPreferences.RESULT_SET_READ_METADATA);
    if (!readMetaData) {
        return;
    }
    boolean readReferences = dataSource.getContainer().getPreferenceStore().getBoolean(DBeaverPreferences.RESULT_SET_READ_REFERENCES);
    final Map<DBCEntityMetaData, DBSEntity> entityBindingMap = new IdentityHashMap<>();
    monitor.beginTask("Discover resultset metadata", 3);
    try {
        SQLQuery sqlQuery = null;
        DBSEntity entity = null;
        if (resultSet != null) {
            DBCStatement sourceStatement = resultSet.getSourceStatement();
            if (sourceStatement != null && sourceStatement.getStatementSource() != null) {
                DBCExecutionSource executionSource = sourceStatement.getStatementSource();
                monitor.subTask("Discover owner entity");
                DBSDataContainer dataContainer = executionSource.getDataContainer();
                if (dataContainer instanceof DBSEntity) {
                    entity = (DBSEntity) dataContainer;
                }
                DBCEntityMetaData entityMeta = null;
                if (entity == null) {
                    // Discover from entity metadata
                    Object sourceDescriptor = executionSource.getSourceDescriptor();
                    if (sourceDescriptor instanceof SQLQuery) {
                        sqlQuery = (SQLQuery) sourceDescriptor;
                        entityMeta = sqlQuery.getSingleSource();
                    }
                    if (entityMeta != null) {
                        entity = getEntityFromMetaData(monitor, dataSource, entityMeta);
                        if (entity != null) {
                            entityBindingMap.put(entityMeta, entity);
                        }
                    }
                }
            }
        }
        final Map<DBSEntity, DBDRowIdentifier> locatorMap = new IdentityHashMap<>();
        monitor.subTask("Discover attributes");
        for (DBDAttributeBindingMeta binding : bindings) {
            monitor.subTask("Discover attribute '" + binding.getName() + "'");
            DBCAttributeMetaData attrMeta = binding.getMetaAttribute();
            // We got table name and column name
            // To be editable we need this resultset contain set of columns from the same table
            // which construct any unique key
            DBSEntity attrEntity = null;
            final DBCEntityMetaData attrEntityMeta = attrMeta.getEntityMetaData();
            if (attrEntityMeta != null) {
                attrEntity = entityBindingMap.get(attrEntityMeta);
                if (attrEntity == null) {
                    if (entity != null && entity instanceof DBSTable && ((DBSTable) entity).isView()) {
                        // If this is a view then don't try to detect entity for each attribute
                        // MySQL returns source table name instead of view name. That's crazy.
                        attrEntity = entity;
                    } else {
                        attrEntity = getEntityFromMetaData(monitor, dataSource, attrEntityMeta);
                    }
                }
                if (attrEntity != null) {
                    entityBindingMap.put(attrEntityMeta, attrEntity);
                }
            }
            if (attrEntity == null) {
                attrEntity = entity;
            }
            if (attrEntity == null) {
                if (attrEntityMeta != null) {
                    log.debug("Table '" + DBUtils.getSimpleQualifiedName(attrEntityMeta.getCatalogName(), attrEntityMeta.getSchemaName(), attrEntityMeta.getEntityName()) + "' not found in metadata catalog");
                }
            } else {
                DBDPseudoAttribute pseudoAttribute = DBUtils.getPseudoAttribute(attrEntity, attrMeta.getName());
                if (pseudoAttribute != null) {
                    binding.setPseudoAttribute(pseudoAttribute);
                }
                DBSEntityAttribute tableColumn;
                if (binding.getPseudoAttribute() != null) {
                    tableColumn = binding.getPseudoAttribute().createFakeAttribute(attrEntity, attrMeta);
                } else {
                    tableColumn = attrEntity.getAttribute(monitor, attrMeta.getName());
                }
                if (sqlQuery != null) {
                    if (tableColumn != null && tableColumn.getTypeID() != attrMeta.getTypeID()) {
                        // !! Do not try to use table column handlers for custom queries if source data type
                        // differs from table data type.
                        // Query may have expressions with the same alias as underlying table column
                        // and this expression may return very different data type. It breaks fetch completely.
                        // There should be a better solution but for now let's just disable this too smart feature.
                        binding.setEntityAttribute(tableColumn, false);
                        continue;
                    }
                /*
                        final SQLSelectItem selectItem = sqlQuery.getSelectItem(attrMeta.getName());
                        if (selectItem != null && !selectItem.isPlainColumn()) {
                            // It is not a column.
                            // It maybe an expression, function or anything else
                            continue;
                        }
*/
                }
                if (tableColumn != null && binding.setEntityAttribute(tableColumn, true) && rows != null) {
                    // E.g. we fetched strings and found out that we should handle them as LOBs or enums.
                    try {
                        int pos = attrMeta.getOrdinalPosition();
                        for (Object[] row : rows) {
                            row[pos] = binding.getValueHandler().getValueFromObject(session, tableColumn, row[pos], false);
                        }
                    } catch (DBCException e) {
                        log.warn("Error resolving attribute '" + binding.getName() + "' values", e);
                    }
                }
            }
        }
        monitor.worked(1);
        // Init row identifiers
        monitor.subTask("Detect unique identifiers");
        for (DBDAttributeBindingMeta binding : bindings) {
            // monitor.subTask("Find attribute '" + binding.getName() + "' identifier");
            DBSEntityAttribute attr = binding.getEntityAttribute();
            if (attr == null) {
                continue;
            }
            DBSEntity attrEntity = attr.getParentObject();
            if (attrEntity != null) {
                DBDRowIdentifier rowIdentifier = locatorMap.get(attrEntity);
                if (rowIdentifier == null) {
                    DBSEntityReferrer entityIdentifier = getBestIdentifier(monitor, attrEntity, bindings);
                    if (entityIdentifier != null) {
                        rowIdentifier = new DBDRowIdentifier(attrEntity, entityIdentifier);
                        locatorMap.put(attrEntity, rowIdentifier);
                    }
                }
                binding.setRowIdentifier(rowIdentifier);
            }
        }
        monitor.worked(1);
        if (readReferences && rows != null) {
            monitor.subTask("Late bindings");
            // Read nested bindings
            for (DBDAttributeBinding binding : bindings) {
                binding.lateBinding(session, rows);
            }
        }
        monitor.subTask("Complete metadata load");
        // Reload attributes in row identifiers
        for (DBDRowIdentifier rowIdentifier : locatorMap.values()) {
            rowIdentifier.reloadAttributes(monitor, bindings);
        }
    } finally {
        monitor.done();
    }
}

Example 9 with SQLQuery

use of org.jkiss.dbeaver.model.sql.SQLQuery in project dbeaver by serge-rider.

the class SQLQueryTransformerAllRows method transformQuery.

@Override
public SQLQuery transformQuery(DBPDataSource dataSource, SQLSyntaxManager syntaxManager, SQLQuery query) throws DBException {
    SQLQuery allRowsQuery = new SQLQuery(dataSource, query.getText(), query);
    allRowsQuery.setResultSetLimit(0, 0);
    return allRowsQuery;
}

Example 10 with SQLQuery

use of org.jkiss.dbeaver.model.sql.SQLQuery in project dbeaver by serge-rider.

the class DBExecUtils method bindAttributes.

public static void bindAttributes(@NotNull DBCSession session, @Nullable DBSEntity sourceEntity, @Nullable DBCResultSet resultSet, @NotNull DBDAttributeBinding[] bindings, @Nullable List<Object[]> rows) throws DBException {
    final DBRProgressMonitor monitor = session.getProgressMonitor();
    final DBPDataSource dataSource = session.getDataSource();
    boolean readMetaData = dataSource.getContainer().getPreferenceStore().getBoolean(ModelPreferences.RESULT_SET_READ_METADATA);
    if (!readMetaData && sourceEntity == null) {
        // Do not read metadata if source entity is not known
        return;
    }
    boolean readReferences = dataSource.getContainer().getPreferenceStore().getBoolean(ModelPreferences.RESULT_SET_READ_REFERENCES);
    final Map<DBCEntityMetaData, DBSEntity> entityBindingMap = new IdentityHashMap<>();
    monitor.beginTask("Discover resultset metadata", 3);
    try {
        SQLQuery sqlQuery = null;
        DBSEntity entity = null;
        if (sourceEntity != null) {
            entity = sourceEntity;
        } else if (resultSet != null) {
            DBCStatement sourceStatement = resultSet.getSourceStatement();
            if (sourceStatement != null && sourceStatement.getStatementSource() != null) {
                DBCExecutionSource executionSource = sourceStatement.getStatementSource();
                monitor.subTask("Discover owner entity");
                DBSDataContainer dataContainer = executionSource.getDataContainer();
                if (dataContainer instanceof DBSEntity) {
                    entity = (DBSEntity) dataContainer;
                }
                DBCEntityMetaData entityMeta = null;
                if (entity == null) {
                    // Discover from entity metadata
                    Object sourceDescriptor = executionSource.getSourceDescriptor();
                    if (sourceDescriptor instanceof SQLQuery) {
                        sqlQuery = (SQLQuery) sourceDescriptor;
                        entityMeta = sqlQuery.getSingleSource();
                    }
                    if (entityMeta != null) {
                        entity = DBUtils.getEntityFromMetaData(monitor, session.getExecutionContext(), entityMeta);
                        if (entity != null) {
                            entityBindingMap.put(entityMeta, entity);
                        }
                    }
                }
            }
        }
        final Map<DBSEntity, DBDRowIdentifier> locatorMap = new IdentityHashMap<>();
        monitor.subTask("Discover attributes");
        for (DBDAttributeBinding binding : bindings) {
            monitor.subTask("Discover attribute '" + binding.getName() + "'");
            DBCAttributeMetaData attrMeta = binding.getMetaAttribute();
            if (attrMeta == null) {
                continue;
            }
            // We got table name and column name
            // To be editable we need this resultset contain set of columns from the same table
            // which construct any unique key
            DBSEntity attrEntity = null;
            final DBCEntityMetaData attrEntityMeta = attrMeta.getEntityMetaData();
            if (attrEntityMeta != null) {
                attrEntity = entityBindingMap.get(attrEntityMeta);
                if (attrEntity == null) {
                    if (entity != null && entity instanceof DBSTable && ((DBSTable) entity).isView()) {
                        // If this is a view then don't try to detect entity for each attribute
                        // MySQL returns source table name instead of view name. That's crazy.
                        attrEntity = entity;
                    } else {
                        attrEntity = DBUtils.getEntityFromMetaData(monitor, session.getExecutionContext(), attrEntityMeta);
                    }
                }
                if (attrEntity != null) {
                    entityBindingMap.put(attrEntityMeta, attrEntity);
                }
            }
            if (attrEntity == null) {
                attrEntity = entity;
            }
            if (attrEntity == null) {
                if (attrEntityMeta != null) {
                    log.debug("Table '" + DBUtils.getSimpleQualifiedName(attrEntityMeta.getCatalogName(), attrEntityMeta.getSchemaName(), attrEntityMeta.getEntityName()) + "' not found in metadata catalog");
                }
            } else if (binding instanceof DBDAttributeBindingMeta) {
                DBDAttributeBindingMeta bindingMeta = (DBDAttributeBindingMeta) binding;
                DBDPseudoAttribute pseudoAttribute = DBUtils.getPseudoAttribute(attrEntity, attrMeta.getName());
                if (pseudoAttribute != null) {
                    bindingMeta.setPseudoAttribute(pseudoAttribute);
                }
                DBSEntityAttribute tableColumn;
                if (bindingMeta.getPseudoAttribute() != null) {
                    tableColumn = bindingMeta.getPseudoAttribute().createFakeAttribute(attrEntity, attrMeta);
                } else {
                    tableColumn = attrEntity.getAttribute(monitor, attrMeta.getName());
                }
                if (tableColumn != null && // - Database doesn't support column name collisions (default)
                (sourceEntity != null || bindingMeta.getMetaAttribute().getEntityMetaData() != null || !bindingMeta.getDataSource().getInfo().needsTableMetaForColumnResolution()) && bindingMeta.setEntityAttribute(tableColumn, ((sqlQuery == null || tableColumn.getTypeID() != attrMeta.getTypeID()) && rows != null))) {
                    // E.g. we fetched strings and found out that we should handle them as LOBs or enums.
                    try {
                        int pos = attrMeta.getOrdinalPosition();
                        for (Object[] row : rows) {
                            row[pos] = binding.getValueHandler().getValueFromObject(session, tableColumn, row[pos], false, false);
                        }
                    } catch (DBCException e) {
                        log.warn("Error resolving attribute '" + binding.getName() + "' values", e);
                    }
                }
            }
        }
        monitor.worked(1);
        {
            // Init row identifiers
            monitor.subTask("Detect unique identifiers");
            for (DBDAttributeBinding binding : bindings) {
                if (!(binding instanceof DBDAttributeBindingMeta)) {
                    continue;
                }
                DBDAttributeBindingMeta bindingMeta = (DBDAttributeBindingMeta) binding;
                // monitor.subTask("Find attribute '" + binding.getName() + "' identifier");
                DBSEntityAttribute attr = binding.getEntityAttribute();
                if (attr == null) {
                    bindingMeta.setRowIdentifierStatus("No corresponding table column");
                    continue;
                }
                DBSEntity attrEntity = attr.getParentObject();
                if (attrEntity != null) {
                    DBDRowIdentifier rowIdentifier = locatorMap.get(attrEntity);
                    if (rowIdentifier == null) {
                        DBSEntityConstraint entityIdentifier = getBestIdentifier(monitor, attrEntity, bindings, readMetaData);
                        if (entityIdentifier != null) {
                            rowIdentifier = new DBDRowIdentifier(attrEntity, entityIdentifier);
                            locatorMap.put(attrEntity, rowIdentifier);
                        } else {
                            bindingMeta.setRowIdentifierStatus("Cannot determine unique row identifier");
                        }
                    }
                    bindingMeta.setRowIdentifier(rowIdentifier);
                }
            }
            monitor.worked(1);
        }
        if (readMetaData && readReferences && rows != null) {
            monitor.subTask("Read results metadata");
            // Read nested bindings
            for (DBDAttributeBinding binding : bindings) {
                binding.lateBinding(session, rows);
            }
        }
        /*
            monitor.subTask("Load transformers");
            // Load transformers
            for (DBDAttributeBinding binding : bindings) {
                binding.loadTransformers(session, rows);
            }
*/
        monitor.subTask("Complete metadata load");
        // Reload attributes in row identifiers
        for (DBDRowIdentifier rowIdentifier : locatorMap.values()) {
            rowIdentifier.reloadAttributes(monitor, bindings);
        }
    } finally {
        monitor.done();
    }
}