Examples with DBSForeignKeyModifyRule org.jkiss.dbeaver.model.struct.rdb.DBSForeignKeyModifyRule used on opensource projects

Example 1 with DBSForeignKeyModifyRule

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

the class MySQLTable method loadForeignKeys.

private List<MySQLTableForeignKey> loadForeignKeys(DBRProgressMonitor monitor, boolean references) throws DBException {
    List<MySQLTableForeignKey> fkList = new ArrayList<>();
    if (!isPersisted()) {
        return fkList;
    }
    try (JDBCSession session = DBUtils.openMetaSession(monitor, getDataSource(), "Load table relations")) {
        Map<String, MySQLTableForeignKey> fkMap = new HashMap<>();
        Map<String, MySQLTableConstraint> pkMap = new HashMap<>();
        JDBCDatabaseMetaData metaData = session.getMetaData();
        // Load indexes
        JDBCResultSet dbResult;
        if (references) {
            dbResult = metaData.getExportedKeys(getContainer().getName(), null, getName());
        } else {
            dbResult = metaData.getImportedKeys(getContainer().getName(), null, getName());
        }
        try {
            while (dbResult.next()) {
                String pkTableCatalog = JDBCUtils.safeGetString(dbResult, JDBCConstants.PKTABLE_CAT);
                String pkTableName = JDBCUtils.safeGetString(dbResult, JDBCConstants.PKTABLE_NAME);
                String pkColumnName = JDBCUtils.safeGetString(dbResult, JDBCConstants.PKCOLUMN_NAME);
                String fkTableCatalog = JDBCUtils.safeGetString(dbResult, JDBCConstants.FKTABLE_CAT);
                String fkTableName = JDBCUtils.safeGetString(dbResult, JDBCConstants.FKTABLE_NAME);
                String fkColumnName = JDBCUtils.safeGetString(dbResult, JDBCConstants.FKCOLUMN_NAME);
                int keySeq = JDBCUtils.safeGetInt(dbResult, JDBCConstants.KEY_SEQ);
                int updateRuleNum = JDBCUtils.safeGetInt(dbResult, JDBCConstants.UPDATE_RULE);
                int deleteRuleNum = JDBCUtils.safeGetInt(dbResult, JDBCConstants.DELETE_RULE);
                String fkName = JDBCUtils.safeGetString(dbResult, JDBCConstants.FK_NAME);
                String pkName = JDBCUtils.safeGetString(dbResult, JDBCConstants.PK_NAME);
                DBSForeignKeyModifyRule deleteRule = JDBCUtils.getCascadeFromNum(deleteRuleNum);
                DBSForeignKeyModifyRule updateRule = JDBCUtils.getCascadeFromNum(updateRuleNum);
                MySQLTable pkTable = getDataSource().findTable(monitor, pkTableCatalog, pkTableName);
                if (pkTable == null) {
                    log.debug("Can't find PK table " + pkTableName);
                    if (references) {
                        continue;
                    }
                }
                MySQLTable fkTable = getDataSource().findTable(monitor, fkTableCatalog, fkTableName);
                if (fkTable == null) {
                    log.warn("Can't find FK table " + fkTableName);
                    if (!references) {
                        continue;
                    }
                }
                MySQLTableColumn pkColumn = pkTable == null ? null : pkTable.getAttribute(monitor, pkColumnName);
                if (pkColumn == null) {
                    log.debug("Can't find PK table " + pkTableName + " column " + pkColumnName);
                    if (references) {
                        continue;
                    }
                }
                MySQLTableColumn fkColumn = fkTable == null ? null : fkTable.getAttribute(monitor, fkColumnName);
                if (fkColumn == null) {
                    log.debug("Can't find FK table " + fkTableName + " column " + fkColumnName);
                    if (!references) {
                        continue;
                    }
                }
                // Find PK
                MySQLTableConstraint pk = null;
                if (pkTable != null && pkName != null) {
                    pk = DBUtils.findObject(pkTable.getConstraints(monitor), pkName);
                    if (pk == null) {
                        log.warn("Unique key '" + pkName + "' not found in table " + pkTable.getFullyQualifiedName(DBPEvaluationContext.DDL));
                    }
                }
                if (pk == null && pkTable != null) {
                    Collection<MySQLTableConstraint> constraints = pkTable.getConstraints(monitor);
                    if (constraints != null) {
                        for (MySQLTableConstraint pkConstraint : constraints) {
                            if (pkConstraint.getConstraintType().isUnique() && DBUtils.getConstraintAttribute(monitor, pkConstraint, pkColumn) != null) {
                                pk = pkConstraint;
                                break;
                            }
                        }
                    }
                }
                if (pk == null && pkTable != null) {
                    log.warn("Can't find primary key for table " + pkTable.getFullyQualifiedName(DBPEvaluationContext.DDL));
                    // Too bad. But we have to create new fake PK for this FK
                    String pkFullName = pkTable.getFullyQualifiedName(DBPEvaluationContext.DDL) + "." + pkName;
                    pk = pkMap.get(pkFullName);
                    if (pk == null) {
                        pk = new MySQLTableConstraint(pkTable, pkName, null, DBSEntityConstraintType.PRIMARY_KEY, true);
                        pk.addColumn(new MySQLTableConstraintColumn(pk, pkColumn, keySeq));
                        pkMap.put(pkFullName, pk);
                    }
                }
                // Find (or create) FK
                MySQLTableForeignKey fk = null;
                if (references && fkTable != null) {
                    fk = DBUtils.findObject(fkTable.getAssociations(monitor), fkName);
                    if (fk == null) {
                        log.warn("Can't find foreign key '" + fkName + "' for table " + fkTable.getFullyQualifiedName(DBPEvaluationContext.DDL));
                    // No choice, we have to create fake foreign key :(
                    } else {
                        if (!fkList.contains(fk)) {
                            fkList.add(fk);
                        }
                    }
                }
                if (fk == null) {
                    fk = fkMap.get(fkName);
                    if (fk == null) {
                        fk = new MySQLTableForeignKey(fkTable, fkName, null, pk, deleteRule, updateRule, true);
                        fkMap.put(fkName, fk);
                        fkList.add(fk);
                    }
                    MySQLTableForeignKeyColumn fkColumnInfo = new MySQLTableForeignKeyColumn(fk, fkColumn, keySeq, pkColumn);
                    fk.addColumn(fkColumnInfo);
                }
            }
        } finally {
            dbResult.close();
        }
        return fkList;
    } catch (SQLException ex) {
        throw new DBException(ex, getDataSource());
    }
}

Example 2 with DBSForeignKeyModifyRule

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

the class GenericTable method loadReferences.

private synchronized List<GenericTableForeignKey> loadReferences(DBRProgressMonitor monitor) throws DBException {
    if (!isPersisted() || !getDataSource().getInfo().supportsReferentialIntegrity()) {
        return new ArrayList<>();
    }
    try (JDBCSession session = DBUtils.openMetaSession(monitor, getDataSource(), "Load table relations")) {
        // Read foreign keys in two passes
        // First read entire resultset to prevent recursive metadata requests
        // some drivers don't like it
        final GenericMetaObject fkObject = getDataSource().getMetaObject(GenericConstants.OBJECT_FOREIGN_KEY);
        final List<ForeignKeyInfo> fkInfos = new ArrayList<>();
        JDBCDatabaseMetaData metaData = session.getMetaData();
        // Load indexes
        try (JDBCResultSet dbResult = metaData.getExportedKeys(getCatalog() == null ? null : getCatalog().getName(), getSchema() == null ? null : getSchema().getName(), getName())) {
            while (dbResult.next()) {
                ForeignKeyInfo fkInfo = new ForeignKeyInfo();
                fkInfo.pkColumnName = GenericUtils.safeGetStringTrimmed(fkObject, dbResult, JDBCConstants.PKCOLUMN_NAME);
                fkInfo.fkTableCatalog = GenericUtils.safeGetStringTrimmed(fkObject, dbResult, JDBCConstants.FKTABLE_CAT);
                fkInfo.fkTableSchema = GenericUtils.safeGetStringTrimmed(fkObject, dbResult, JDBCConstants.FKTABLE_SCHEM);
                fkInfo.fkTableName = GenericUtils.safeGetStringTrimmed(fkObject, dbResult, JDBCConstants.FKTABLE_NAME);
                fkInfo.fkColumnName = GenericUtils.safeGetStringTrimmed(fkObject, dbResult, JDBCConstants.FKCOLUMN_NAME);
                fkInfo.keySeq = GenericUtils.safeGetInt(fkObject, dbResult, JDBCConstants.KEY_SEQ);
                fkInfo.updateRuleNum = GenericUtils.safeGetInt(fkObject, dbResult, JDBCConstants.UPDATE_RULE);
                fkInfo.deleteRuleNum = GenericUtils.safeGetInt(fkObject, dbResult, JDBCConstants.DELETE_RULE);
                fkInfo.fkName = GenericUtils.safeGetStringTrimmed(fkObject, dbResult, JDBCConstants.FK_NAME);
                fkInfo.pkName = GenericUtils.safeGetStringTrimmed(fkObject, dbResult, JDBCConstants.PK_NAME);
                fkInfo.defferabilityNum = GenericUtils.safeGetInt(fkObject, dbResult, JDBCConstants.DEFERRABILITY);
                fkInfos.add(fkInfo);
            }
        }
        List<GenericTableForeignKey> fkList = new ArrayList<>();
        Map<String, GenericTableForeignKey> fkMap = new HashMap<>();
        for (ForeignKeyInfo info : fkInfos) {
            DBSForeignKeyModifyRule deleteRule = JDBCUtils.getCascadeFromNum(info.deleteRuleNum);
            DBSForeignKeyModifyRule updateRule = JDBCUtils.getCascadeFromNum(info.updateRuleNum);
            DBSForeignKeyDefferability defferability;
            switch(info.defferabilityNum) {
                case DatabaseMetaData.importedKeyInitiallyDeferred:
                    defferability = DBSForeignKeyDefferability.INITIALLY_DEFERRED;
                    break;
                case DatabaseMetaData.importedKeyInitiallyImmediate:
                    defferability = DBSForeignKeyDefferability.INITIALLY_IMMEDIATE;
                    break;
                case DatabaseMetaData.importedKeyNotDeferrable:
                    defferability = DBSForeignKeyDefferability.NOT_DEFERRABLE;
                    break;
                default:
                    defferability = DBSForeignKeyDefferability.UNKNOWN;
                    break;
            }
            if (info.fkTableName == null) {
                log.debug("Null FK table name");
                continue;
            }
            //String fkTableFullName = DBUtils.getFullyQualifiedName(getDataSource(), info.fkTableCatalog, info.fkTableSchema, info.fkTableName);
            GenericTable fkTable = getDataSource().findTable(monitor, info.fkTableCatalog, info.fkTableSchema, info.fkTableName);
            if (fkTable == null) {
                log.warn("Can't find FK table " + info.fkTableName);
                continue;
            }
            GenericTableColumn pkColumn = this.getAttribute(monitor, info.pkColumnName);
            if (pkColumn == null) {
                log.warn("Can't find PK column " + info.pkColumnName);
                continue;
            }
            GenericTableColumn fkColumn = fkTable.getAttribute(monitor, info.fkColumnName);
            if (fkColumn == null) {
                log.warn("Can't find FK table " + fkTable.getFullyQualifiedName(DBPEvaluationContext.DDL) + " column " + info.fkColumnName);
                continue;
            }
            // Find PK
            GenericPrimaryKey pk = null;
            if (!CommonUtils.isEmpty(info.pkName)) {
                pk = DBUtils.findObject(this.getConstraints(monitor), info.pkName);
                if (pk == null) {
                    log.debug("Unique key '" + info.pkName + "' not found in table " + this.getFullyQualifiedName(DBPEvaluationContext.DDL));
                }
            }
            if (pk == null) {
                Collection<GenericPrimaryKey> uniqueKeys = this.getConstraints(monitor);
                if (uniqueKeys != null) {
                    for (GenericPrimaryKey pkConstraint : uniqueKeys) {
                        if (pkConstraint.getConstraintType().isUnique() && DBUtils.getConstraintAttribute(monitor, pkConstraint, pkColumn) != null) {
                            pk = pkConstraint;
                            break;
                        }
                    }
                }
            }
            if (pk == null) {
                log.warn("Can't find unique key for table " + this.getFullyQualifiedName(DBPEvaluationContext.DDL) + " column " + pkColumn.getName());
                // Too bad. But we have to create new fake PK for this FK
                //String pkFullName = getFullyQualifiedName() + "." + info.pkName;
                pk = new GenericPrimaryKey(this, info.pkName, null, DBSEntityConstraintType.PRIMARY_KEY, true);
                pk.addColumn(new GenericTableConstraintColumn(pk, pkColumn, info.keySeq));
                // Add this fake constraint to it's owner
                this.addUniqueKey(pk);
            }
            // Find (or create) FK
            GenericTableForeignKey fk;
            if (CommonUtils.isEmpty(info.fkName)) {
                // Make fake FK name
                info.fkName = info.fkTableName.toUpperCase() + "_FK" + info.keySeq;
                fk = DBUtils.findObject(fkTable.getAssociations(monitor), info.fkName);
            } else {
                fk = DBUtils.findObject(fkTable.getAssociations(monitor), info.fkName);
                if (fk == null) {
                    log.warn("Can't find foreign key '" + info.fkName + "' for table " + fkTable.getFullyQualifiedName(DBPEvaluationContext.DDL));
                // No choice, we have to create fake foreign key :(
                }
            }
            if (fk != null && !fkList.contains(fk)) {
                fkList.add(fk);
            }
            if (fk == null) {
                fk = fkMap.get(info.fkName);
                if (fk == null) {
                    fk = new GenericTableForeignKey(fkTable, info.fkName, null, pk, deleteRule, updateRule, defferability, true);
                    fkMap.put(info.fkName, fk);
                    fkList.add(fk);
                }
                GenericTableForeignKeyColumnTable fkColumnInfo = new GenericTableForeignKeyColumnTable(fk, fkColumn, info.keySeq, pkColumn);
                fk.addColumn(fkColumnInfo);
            }
        }
        return fkList;
    } catch (SQLException ex) {
        throw new DBException(ex, getDataSource());
    }
}

Example 3 with DBSForeignKeyModifyRule

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

the class ForeignKeysCache method fetchObject.

@Nullable
@Override
protected GenericTableForeignKey fetchObject(JDBCSession session, GenericStructContainer owner, GenericTable parent, String fkName, JDBCResultSet dbResult) throws SQLException, DBException {
    String pkTableCatalog = GenericUtils.safeGetStringTrimmed(foreignKeyObject, dbResult, JDBCConstants.PKTABLE_CAT);
    String pkTableSchema = GenericUtils.safeGetStringTrimmed(foreignKeyObject, dbResult, JDBCConstants.PKTABLE_SCHEM);
    String pkTableName = GenericUtils.safeGetStringTrimmed(foreignKeyObject, dbResult, JDBCConstants.PKTABLE_NAME);
    String fkTableCatalog = GenericUtils.safeGetStringTrimmed(foreignKeyObject, dbResult, JDBCConstants.FKTABLE_CAT);
    String fkTableSchema = GenericUtils.safeGetStringTrimmed(foreignKeyObject, dbResult, JDBCConstants.FKTABLE_SCHEM);
    int keySeq = GenericUtils.safeGetInt(foreignKeyObject, dbResult, JDBCConstants.KEY_SEQ);
    int updateRuleNum = GenericUtils.safeGetInt(foreignKeyObject, dbResult, JDBCConstants.UPDATE_RULE);
    int deleteRuleNum = GenericUtils.safeGetInt(foreignKeyObject, dbResult, JDBCConstants.DELETE_RULE);
    String pkName = GenericUtils.safeGetStringTrimmed(foreignKeyObject, dbResult, JDBCConstants.PK_NAME);
    int defferabilityNum = GenericUtils.safeGetInt(foreignKeyObject, dbResult, JDBCConstants.DEFERRABILITY);
    DBSForeignKeyModifyRule deleteRule = JDBCUtils.getCascadeFromNum(deleteRuleNum);
    DBSForeignKeyModifyRule updateRule = JDBCUtils.getCascadeFromNum(updateRuleNum);
    DBSForeignKeyDefferability defferability;
    switch(defferabilityNum) {
        case DatabaseMetaData.importedKeyInitiallyDeferred:
            defferability = DBSForeignKeyDefferability.INITIALLY_DEFERRED;
            break;
        case DatabaseMetaData.importedKeyInitiallyImmediate:
            defferability = DBSForeignKeyDefferability.INITIALLY_IMMEDIATE;
            break;
        case DatabaseMetaData.importedKeyNotDeferrable:
            defferability = DBSForeignKeyDefferability.NOT_DEFERRABLE;
            break;
        default:
            defferability = DBSForeignKeyDefferability.UNKNOWN;
            break;
    }
    if (pkTableName == null) {
        log.debug("Null PK table name");
        return null;
    }
    String pkTableFullName = DBUtils.getSimpleQualifiedName(pkTableCatalog, pkTableSchema, pkTableName);
    GenericTable pkTable = parent.getDataSource().findTable(session.getProgressMonitor(), pkTableCatalog, pkTableSchema, pkTableName);
    if (pkTable == null) {
        // Try to use FK catalog/schema
        pkTable = parent.getDataSource().findTable(session.getProgressMonitor(), fkTableCatalog, fkTableSchema, pkTableName);
        if (pkTable == null) {
            log.warn("Can't find PK table " + pkTableName);
            return null;
        } else {
            log.debug("PK table " + pkTableFullName + " was taken from FK container.");
        }
    }
    // Find PK
    GenericPrimaryKey pk = null;
    if (!CommonUtils.isEmpty(pkName)) {
        pk = DBUtils.findObject(pkTable.getConstraints(session.getProgressMonitor()), pkName);
        if (pk == null) {
            log.debug("Unique key '" + pkName + "' not found in table " + pkTable.getFullyQualifiedName(DBPEvaluationContext.DDL));
        }
    }
    if (pk == null) {
        String pkColumnName = GenericUtils.safeGetStringTrimmed(foreignKeyObject, dbResult, JDBCConstants.PKCOLUMN_NAME);
        GenericTableColumn pkColumn = pkTable.getAttribute(session.getProgressMonitor(), pkColumnName);
        if (pkColumn == null) {
            log.warn("Can't find PK table " + pkTable.getFullyQualifiedName(DBPEvaluationContext.DDL) + " column " + pkColumnName);
            return null;
        }
        Collection<GenericPrimaryKey> uniqueKeys = pkTable.getConstraints(session.getProgressMonitor());
        if (uniqueKeys != null) {
            for (GenericPrimaryKey pkConstraint : uniqueKeys) {
                if (pkConstraint.getConstraintType().isUnique() && DBUtils.getConstraintAttribute(session.getProgressMonitor(), pkConstraint, pkColumn) != null) {
                    pk = pkConstraint;
                    break;
                }
            }
        }
        if (pk == null) {
            log.warn("Can't find unique key for table " + pkTable.getFullyQualifiedName(DBPEvaluationContext.DDL) + " column " + pkColumn.getName());
            // Too bad. But we have to create new fake PK for this FK
            String pkFullName = pkTable.getFullyQualifiedName(DBPEvaluationContext.DDL) + "." + pkName;
            pk = pkMap.get(pkFullName);
            if (pk == null) {
                pk = new GenericPrimaryKey(pkTable, pkName, null, DBSEntityConstraintType.PRIMARY_KEY, true);
                pkMap.put(pkFullName, pk);
                // Add this fake constraint to it's owner
                pk.getTable().addUniqueKey(pk);
            }
            pk.addColumn(new GenericTableConstraintColumn(pk, pkColumn, keySeq));
        }
    }
    if (CommonUtils.isEmpty(fkName)) {
        // [JDBC] Some drivers return empty foreign key names
        fkName = parent.getName().toUpperCase() + "_FK_" + pkTable.getName().toUpperCase(Locale.ENGLISH);
    }
    return new GenericTableForeignKey(parent, fkName, null, pk, deleteRule, updateRule, defferability, true);
}