Search in sources :

Example 1 with DBSEntityAttributeRef

use of org.jkiss.dbeaver.model.struct.DBSEntityAttributeRef in project dbeaver by serge-rider.

the class ExasolUtils method generateDDLforTable.

@SuppressWarnings("rawtypes")
public static String generateDDLforTable(DBRProgressMonitor monitor, ExasolDataSource dataSource, ExasolTable exasolTable) throws DBException {
    StringBuilder ddlOutput = new StringBuilder();
    ddlOutput.append("CREATE TABLE \"" + exasolTable.getSchema().getName() + "\".\"" + exasolTable.getName() + "\" (");
    try (JDBCSession session = DBUtils.openMetaSession(monitor, dataSource, "Get Table DDL")) {
        try (JDBCStatement dbStat = session.createStatement()) {
            JDBCResultSet rs = dbStat.executeQuery(String.format(TABLE_QUERY_COLUMNS, quoteString(exasolTable.getSchema().getName()), quoteString(exasolTable.getName())));
            // column infos
            List<String> columns = new ArrayList<String>();
            // distribution key infos
            List<String> distKey = new ArrayList<String>();
            while (rs.next()) {
                StringBuilder columnString = new StringBuilder("");
                // double quotation mark for column as the name could be a
                // reserved word
                columnString.append("\n\t\t\"" + rs.getString("COLUMN_NAME") + "\" " + rs.getString("COLUMN_TYPE") + " ");
                // has default value?
                if (rs.getString("COLUMN_DEFAULT") != null)
                    columnString.append("DEFAULT " + rs.getString("COLUMN_DEFAULT") + " ");
                // has identity
                if (rs.getBigDecimal("COLUMN_IDENTITY") != null)
                    columnString.append("IDENTITY " + rs.getBigDecimal("COLUMN_IDENTITY").toString() + " ");
                // has identity
                if (!rs.getBoolean("COLUMN_IS_NULLABLE"))
                    columnString.append("NOT NULL ");
                // comment
                if (rs.getString("COLUMN_COMMENT") != null)
                    // replace ' to double ' -> escape for SQL
                    columnString.append("COMMENT IS '" + rs.getString("COLUMN_COMMENT").replaceAll("'", "''") + "'");
                // if distkey add column to distkey
                if (rs.getBoolean("COLUMN_IS_DISTRIBUTION_KEY"))
                    distKey.add(rs.getString("COLUMN_NAME"));
                columns.add(columnString.toString());
            }
            ddlOutput.append(CommonUtils.joinStrings(",", columns));
            // do we have a distkey?
            if (distKey.size() > 0) {
                ddlOutput.append(",\n\t\t DISTRIBUTE BY " + CommonUtils.joinStrings(",", distKey));
            }
            ddlOutput.append("\n);\n");
        }
        //primary key
        Collection<ExasolTableUniqueKey> pks = exasolTable.getConstraints(monitor);
        if (pks != null & pks.size() > 0) {
            //get only first as there is only 1 primary key
            ExasolTableUniqueKey pk = null;
            pk = pks.iterator().next();
            ArrayList<String> columns = new ArrayList<String>();
            for (DBSEntityAttributeRef c : pk.getAttributeReferences(monitor)) {
                columns.add("\"" + c.getAttribute().getName() + "\"");
            }
            ddlOutput.append("\nALTER TABLE \"" + exasolTable.getSchema().getName() + "\".\"" + exasolTable.getName() + "\" ADD CONSTRAINT " + pk.getName() + " PRIMARY KEY (" + CommonUtils.joinStrings(",", columns) + ") " + (pk.getEnabled() ? "ENABLE" : "") + " ;\n");
        }
        //foreign key
        Collection<ExasolTableForeignKey> fks = exasolTable.getAssociations(monitor);
        if (fks != null & fks.size() > 0) {
            //look keys
            for (ExasolTableForeignKey fk : fks) {
                ArrayList<String> columns = new ArrayList<String>();
                for (DBSEntityAttributeRef c : fk.getAttributeReferences(monitor)) {
                    columns.add("\"" + c.getAttribute().getName() + "\"");
                }
                ddlOutput.append("\nALTER TABLE \"" + exasolTable.getSchema().getName() + "\".\"" + exasolTable.getName() + "\" ADD CONSTRAINT " + fk.getName() + " FOREIGN KEY (" + CommonUtils.joinStrings(",", columns) + ") REFERENCES \"" + fk.getReferencedTable().getSchema().getName() + "\".\"" + fk.getReferencedTable().getName() + "\" " + (fk.getEnabled() ? "ENABLE" : "") + " ;\n");
            }
        }
        return ddlOutput.toString();
    } catch (SQLException e) {
        throw new DBException(e, dataSource);
    } finally {
        monitor.done();
    }
}
Also used : JDBCSession(org.jkiss.dbeaver.model.exec.jdbc.JDBCSession) DBException(org.jkiss.dbeaver.DBException) SQLException(java.sql.SQLException) ArrayList(java.util.ArrayList) DBSEntityAttributeRef(org.jkiss.dbeaver.model.struct.DBSEntityAttributeRef) JDBCStatement(org.jkiss.dbeaver.model.exec.jdbc.JDBCStatement) JDBCResultSet(org.jkiss.dbeaver.model.exec.jdbc.JDBCResultSet)

Example 2 with DBSEntityAttributeRef

use of org.jkiss.dbeaver.model.struct.DBSEntityAttributeRef in project dbeaver by serge-rider.

the class SQLConstraintManager method appendConstraintDefinition.

protected void appendConstraintDefinition(StringBuilder decl, DBECommandAbstract<OBJECT_TYPE> command) {
    //$NON-NLS-1$
    decl.append(" (");
    // Get columns using void monitor
    try {
        List<? extends DBSEntityAttributeRef> attrs = command.getObject().getAttributeReferences(VoidProgressMonitor.INSTANCE);
        if (attrs != null) {
            boolean firstColumn = true;
            for (DBSEntityAttributeRef constraintColumn : attrs) {
                final DBSEntityAttribute attribute = constraintColumn.getAttribute();
                if (attribute == null) {
                    continue;
                }
                //$NON-NLS-1$
                if (!firstColumn)
                    decl.append(",");
                firstColumn = false;
                decl.append(DBUtils.getQuotedIdentifier(attribute));
            }
        }
    } catch (DBException e) {
        log.warn("Can't obtain attribute references", e);
    }
    //$NON-NLS-1$
    decl.append(")");
}
Also used : DBSEntityAttributeRef(org.jkiss.dbeaver.model.struct.DBSEntityAttributeRef) DBException(org.jkiss.dbeaver.DBException) DBSEntityAttribute(org.jkiss.dbeaver.model.struct.DBSEntityAttribute)

Example 3 with DBSEntityAttributeRef

use of org.jkiss.dbeaver.model.struct.DBSEntityAttributeRef in project dbeaver by serge-rider.

the class DBDRowIdentifier method reloadAttributes.

public void reloadAttributes(@NotNull DBRProgressMonitor monitor, @NotNull DBDAttributeBinding[] bindings) throws DBException {
    this.attributes.clear();
    Collection<? extends DBSEntityAttributeRef> refs = CommonUtils.safeCollection(entityIdentifier.getAttributeReferences(monitor));
    for (DBSEntityAttributeRef cColumn : refs) {
        DBDAttributeBinding binding = DBUtils.findBinding(bindings, cColumn.getAttribute());
        if (binding != null) {
            this.attributes.add(binding);
        } else {
            // If at least one attribute is missing - this ID won't work anyway
            // so let's just clean it up
            this.attributes.clear();
            break;
        }
    }
}
Also used : DBSEntityAttributeRef(org.jkiss.dbeaver.model.struct.DBSEntityAttributeRef)

Example 4 with DBSEntityAttributeRef

use of org.jkiss.dbeaver.model.struct.DBSEntityAttributeRef in project dbeaver by serge-rider.

the class EditForeignKeyPage method handleUniqueKeySelect.

private void handleUniqueKeySelect() {
    curConstraint = null;
    fkColumns.clear();
    ownColumns = null;
    columnsTable.removeAll();
    if (curConstraints.isEmpty() || uniqueKeyCombo.getSelectionIndex() < 0) {
        return;
    }
    curConstraint = curConstraints.get(uniqueKeyCombo.getSelectionIndex());
    try {
        // Read column nodes with void monitor because we already cached them above
        for (DBSEntityAttributeRef pkColumn : curConstraint.getAttributeReferences(VoidProgressMonitor.INSTANCE)) {
            FKColumnInfo fkColumnInfo = new FKColumnInfo(pkColumn.getAttribute());
            // Try to find matched column in own table
            Collection<? extends DBSEntityAttribute> tmpColumns = ownTable.getAttributes(VoidProgressMonitor.INSTANCE);
            ownColumns = tmpColumns == null ? Collections.<DBSTableColumn>emptyList() : new ArrayList<>(ownTable.getAttributes(VoidProgressMonitor.INSTANCE));
            if (!CommonUtils.isEmpty(ownColumns)) {
                for (DBSEntityAttribute ownColumn : ownColumns) {
                    if (ownColumn.getName().equals(pkColumn.getAttribute().getName()) && ownTable != pkColumn.getAttribute().getParentObject()) {
                        fkColumnInfo.ownColumn = ownColumn;
                        break;
                    }
                }
            }
            fkColumns.add(fkColumnInfo);
            TableItem item = new TableItem(columnsTable, SWT.NONE);
            if (fkColumnInfo.ownColumn != null) {
                item.setText(0, fkColumnInfo.ownColumn.getName());
                item.setImage(0, getColumnIcon(fkColumnInfo.ownColumn));
                item.setText(1, fkColumnInfo.ownColumn.getFullTypeName());
            }
            item.setText(2, pkColumn.getAttribute().getName());
            item.setImage(2, getColumnIcon(pkColumn.getAttribute()));
            item.setText(3, pkColumn.getAttribute().getFullTypeName());
            item.setData(fkColumnInfo);
        }
    } catch (DBException e) {
        UIUtils.showErrorDialog(getShell(), CoreMessages.dialog_struct_edit_fk_error_load_constraint_columns_title, CoreMessages.dialog_struct_edit_fk_error_load_constraint_columns_message, e);
    }
    UIUtils.packColumns(columnsTable, true);
}
Also used : DBSEntityAttributeRef(org.jkiss.dbeaver.model.struct.DBSEntityAttributeRef) DBException(org.jkiss.dbeaver.DBException) DBSEntityAttribute(org.jkiss.dbeaver.model.struct.DBSEntityAttribute) ArrayList(java.util.ArrayList)

Example 5 with DBSEntityAttributeRef

use of org.jkiss.dbeaver.model.struct.DBSEntityAttributeRef in project dbeaver by serge-rider.

the class SQLForeignKeyManager method getNestedDeclaration.

@Override
protected StringBuilder getNestedDeclaration(TABLE_TYPE owner, DBECommandAbstract<OBJECT_TYPE> command) {
    OBJECT_TYPE foreignKey = command.getObject();
    boolean legacySyntax = isLegacyForeignKeySyntax(owner);
    // Create column
    String constraintName = DBUtils.getQuotedIdentifier(foreignKey.getDataSource(), foreignKey.getName());
    StringBuilder decl = new StringBuilder(40);
    if (!legacySyntax || !foreignKey.isPersisted()) {
        decl.append("CONSTRAINT ");
    }
    if (!legacySyntax) {
        //$NON-NLS-1$
        decl.append(constraintName).append(" ");
    }
    //$NON-NLS-1$
    decl.append(foreignKey.getConstraintType().getName().toUpperCase(Locale.ENGLISH)).append(//$NON-NLS-1$
    " (");
    try {
        // Get columns using void monitor
        final Collection<? extends DBSEntityAttributeRef> columns = command.getObject().getAttributeReferences(VoidProgressMonitor.INSTANCE);
        boolean firstColumn = true;
        for (DBSEntityAttributeRef constraintColumn : CommonUtils.safeCollection(columns)) {
            final DBSEntityAttribute attribute = constraintColumn.getAttribute();
            //$NON-NLS-1$
            if (!firstColumn)
                decl.append(",");
            firstColumn = false;
            if (attribute != null) {
                decl.append(DBUtils.getQuotedIdentifier(attribute));
            }
        }
    } catch (DBException e) {
        log.error("Can't obtain reference attributes", e);
    }
    final DBSEntityConstraint refConstraint = foreignKey.getReferencedConstraint();
    //$NON-NLS-1$ //$NON-NLS-2$
    decl.append(") REFERENCES ").append(refConstraint == null ? "<?>" : DBUtils.getObjectFullName(refConstraint.getParentObject(), DBPEvaluationContext.DDL)).append("(");
    if (refConstraint instanceof DBSEntityReferrer) {
        try {
            boolean firstColumn = true;
            List<? extends DBSEntityAttributeRef> columns = ((DBSEntityReferrer) refConstraint).getAttributeReferences(VoidProgressMonitor.INSTANCE);
            for (DBSEntityAttributeRef constraintColumn : CommonUtils.safeCollection(columns)) {
                //$NON-NLS-1$
                if (!firstColumn)
                    decl.append(",");
                firstColumn = false;
                final DBSEntityAttribute attribute = constraintColumn.getAttribute();
                if (attribute != null) {
                    decl.append(DBUtils.getQuotedIdentifier(attribute));
                }
            }
        } catch (DBException e) {
            log.error("Can't obtain ref constraint reference attributes", e);
        }
    }
    //$NON-NLS-1$
    decl.append(")");
    if (foreignKey.getDeleteRule() != null && !CommonUtils.isEmpty(foreignKey.getDeleteRule().getClause())) {
        //$NON-NLS-1$
        decl.append(" ON DELETE ").append(foreignKey.getDeleteRule().getClause());
    }
    if (foreignKey.getUpdateRule() != null && !CommonUtils.isEmpty(foreignKey.getUpdateRule().getClause())) {
        //$NON-NLS-1$
        decl.append(" ON UPDATE ").append(foreignKey.getUpdateRule().getClause());
    }
    if (legacySyntax) {
        //$NON-NLS-1$
        decl.append(" CONSTRAINT ").append(constraintName);
    }
    return decl;
}
Also used : DBSEntityAttributeRef(org.jkiss.dbeaver.model.struct.DBSEntityAttributeRef) DBException(org.jkiss.dbeaver.DBException) DBSEntityReferrer(org.jkiss.dbeaver.model.struct.DBSEntityReferrer) DBSEntityConstraint(org.jkiss.dbeaver.model.struct.DBSEntityConstraint) DBSEntityAttribute(org.jkiss.dbeaver.model.struct.DBSEntityAttribute)

Aggregations

DBSEntityAttributeRef (org.jkiss.dbeaver.model.struct.DBSEntityAttributeRef)5 DBException (org.jkiss.dbeaver.DBException)4 DBSEntityAttribute (org.jkiss.dbeaver.model.struct.DBSEntityAttribute)3 ArrayList (java.util.ArrayList)2 SQLException (java.sql.SQLException)1 JDBCResultSet (org.jkiss.dbeaver.model.exec.jdbc.JDBCResultSet)1 JDBCSession (org.jkiss.dbeaver.model.exec.jdbc.JDBCSession)1 JDBCStatement (org.jkiss.dbeaver.model.exec.jdbc.JDBCStatement)1 DBSEntityConstraint (org.jkiss.dbeaver.model.struct.DBSEntityConstraint)1 DBSEntityReferrer (org.jkiss.dbeaver.model.struct.DBSEntityReferrer)1