List of usage examples for java.sql DatabaseMetaData getImportedKeys
ResultSet getImportedKeys(String catalog, String schema, String table) throws SQLException;
From source file:com.nextep.designer.sqlgen.mysql.impl.MySqlCapturer.java
private void fillForeignKeys(DatabaseMetaData md, IProgressMonitor monitor, IBasicTable table, Map<String, IKeyConstraint> keysMap, Map<String, IBasicColumn> columnsMap) throws SQLException { final String tabName = table.getName(); ResultSet rset = null;//from w ww . j a v a 2s.c om // Creating foreign keys for this table try { rset = md.getImportedKeys(null, null, tabName); ForeignKeyConstraint fk = null; while (rset.next()) { monitor.worked(1); String fkName = rset.getString("FK_NAME"); //$NON-NLS-1$ String colName = rset.getString("FKCOLUMN_NAME"); //$NON-NLS-1$ String remoteTableName = rset.getString("PKTABLE_NAME"); //$NON-NLS-1$ final short onUpdateRule = rset.getShort("UPDATE_RULE"); //$NON-NLS-1$ final short onDeleteRule = rset.getShort("DELETE_RULE"); //$NON-NLS-1$ if (fk == null || (fk != null && !fkName.equals(fk.getName()))) { fk = new ForeignKeyConstraint(fkName, "", table); //$NON-NLS-1$ // Retrieving primary key from loaded keys IKeyConstraint refConstraint = keysMap.get(remoteTableName.toUpperCase()); // We have a reference to a non-imported constraint if (refConstraint == null) { try { IBasicTable remoteTable = (IBasicTable) CorePlugin.getService(IReferenceManager.class) .findByTypeName(IElementType.getInstance(IBasicTable.TYPE_ID), DBVendor.MYSQL.getNameFormatter().format(remoteTableName)); refConstraint = DBGMHelper.getPrimaryKey(remoteTable); if (refConstraint == null) { LOGGER.warn( MessageFormat.format(MySQLMessages.getString("capturer.mysql.fkIgnored"), //$NON-NLS-1$ fkName)); continue; } LOGGER.warn(MessageFormat.format(MySQLMessages.getString("capturer.mysql.fkRelinked"), //$NON-NLS-1$ fkName, refConstraint.getName())); } catch (ReferenceNotFoundException e) { LOGGER.warn(MessageFormat.format(MySQLMessages.getString("capturer.mysql.fkIgnored"), //$NON-NLS-1$ fkName)); continue; } } fk.setRemoteConstraint(refConstraint); fk.setOnUpdateAction(CaptureHelper.getForeignKeyAction(onUpdateRule)); fk.setOnDeleteAction(CaptureHelper.getForeignKeyAction(onDeleteRule)); table.addConstraint(fk); } final String columnKey = CaptureHelper.getUniqueObjectName(tabName, colName); final IBasicColumn fkColumn = columnsMap.get(columnKey); if (fkColumn != null) { fk.addColumn(fkColumn); } else { LOGGER.warn(MessageFormat.format(MySQLMessages.getString("capturer.mysql.foreignKeyNotFound"), //$NON-NLS-1$ columnKey)); } } } finally { CaptureHelper.safeClose(rset, null); } }
From source file:jp.co.tis.gsp.tools.dba.s2jdbc.gen.DbTableMetaReaderWithView.java
@Override protected List<DbForeignKeyMeta> getDbForeignKeyMetaList(DatabaseMetaData metaData, DbTableMeta tableMeta) { @SuppressWarnings("unchecked") Map<String, DbForeignKeyMeta> map = new ArrayMap(); Dialect gspDialect = DialectUtil.getDialect(); try {/*from w ww .j a va 2 s. co m*/ String typeName = getObjectTypeName(metaData, tableMeta); String tableName = tableMeta.getName(); ViewAnalyzer viewAnalyzer = null; if (StringUtils.equals(typeName, "VIEW")) { String sql = gspDialect.getViewDefinition(metaData.getConnection(), tableMeta.getName(), tableMeta); viewAnalyzer = new ViewAnalyzer(); viewAnalyzer.parse(sql); if (viewAnalyzer.isSimple()) { tableName = viewAnalyzer.getTableName(); } else { return Collections.emptyList(); } } ResultSet rs = metaData.getImportedKeys(tableMeta.getCatalogName(), tableMeta.getSchemaName(), tableName); try { while (rs.next()) { String name = rs.getString("FK_NAME"); if (!map.containsKey(name)) { DbForeignKeyMeta fkMeta = new DbForeignKeyMeta(); fkMeta.setName(name); fkMeta.setPrimaryKeyCatalogName(rs.getString("PKTABLE_CAT")); fkMeta.setPrimaryKeySchemaName(rs.getString("PKTABLE_SCHEM")); fkMeta.setPrimaryKeyTableName(rs.getString("PKTABLE_NAME")); map.put(name, fkMeta); } DbForeignKeyMeta fkMeta = map.get(name); fkMeta.addPrimaryKeyColumnName(rs.getString("PKCOLUMN_NAME")); fkMeta.addForeignKeyColumnName(rs.getString("FKCOLUMN_NAME")); } } finally { ResultSetUtil.close(rs); } if (viewAnalyzer != null && !map.isEmpty()) { Map<String, DbForeignKeyMeta> tmpMap = new ArrayMap(map); for (DbForeignKeyMeta fkMeta : tmpMap.values()) { boolean fkContains = true; for (String fkColumn : fkMeta.getForeignKeyColumnNameList()) { fkContains &= viewAnalyzer.getColumnNames().contains(fkColumn.toUpperCase()); } if (!fkContains) map.remove(fkMeta.getName()); } } DbForeignKeyMeta[] array = map.values().toArray(new DbForeignKeyMeta[map.size()]); return Arrays.asList(array); } catch (SQLException ex) { throw new SQLRuntimeException(ex); } }
From source file:com.nextep.designer.sqlgen.generic.impl.JDBCCapturer.java
/** * Returns a <code>Collection</code> of the foreign keys of the specified * table present in the data source pointed to by the connection object * provided by the specified <code>context</code> and notifies the specified * <code>monitor</code> while capturing. * /*w w w. ja v a 2s . c o m*/ * @param context * a {@link ICaptureContext} to store the captured objects * @param monitor * the {@link IProgressMonitor} to notify while capturing objects * @param allTables * a <code>Map</code> of all tables previously captured * @param allTablesColumns * a <code>Map</code> of all columns previously captured * @param table * the {@link IBasicTable} for which foreign keys must be * captured * @return a {@link Collection} of {@link ForeignKeyConstraint} objects if * the specified table has foreign keys, an empty * <code>Collection</code> otherwise */ private Collection<ForeignKeyConstraint> getTableForeignKeys(ICaptureContext context, IProgressMonitor monitor, Map<String, IBasicTable> allTables, Map<String, IBasicColumn> allTablesColumns, IBasicTable table) { Collection<ForeignKeyConstraint> foreignKeys = new ArrayList<ForeignKeyConstraint>(); IFormatter formatter = getConnectionVendor(context).getNameFormatter(); final String tableName = table.getName(); try { final DatabaseMetaData md = ((Connection) context.getConnectionObject()).getMetaData(); ResultSet rset = null; if (md != null) { rset = md.getImportedKeys(getObjectOrContextCatalog(context, table), getObjectOrContextSchema(context, table), tableName); CaptureHelper.updateMonitor(monitor, getCounter(), 1, 1); } if (rset != null) { ForeignKeyConstraint currFk = null; String currFkName = null; boolean keyIsValid = false; try { while (rset.next()) { final String fkName = rset.getString(COLUMN_NAME_FK_NAME); final String fkColumnName = rset.getString(COLUMN_NAME_FKCOLUMN_NAME); final String pkTableName = rset.getString(COLUMN_NAME_PKTABLE_NAME); final String pkName = rset.getString(COLUMN_NAME_PK_NAME); final short onUpdateRule = rset.getShort(COLUMN_NAME_UPDATE_RULE); final short onDeleteRule = rset.getShort(COLUMN_NAME_DELETE_RULE); final short deferrability = rset.getShort(COLUMN_NAME_DEFERRABILITY); if (fkName != null && !"".equals(fkName.trim())) { //$NON-NLS-1$ if (LOGGER.isDebugEnabled()) { String logPrefix = "[" + tableName + "][" + fkName + "]"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ LOGGER.debug("= " + logPrefix + " Foreign Key Metadata ="); //$NON-NLS-1$ //$NON-NLS-2$ LOGGER.debug(logPrefix + "[" + COLUMN_NAME_FKCOLUMN_NAME + "] " //$NON-NLS-1$ //$NON-NLS-2$ + fkColumnName); LOGGER.debug(logPrefix + "[" + COLUMN_NAME_PKTABLE_NAME + "] " //$NON-NLS-1$ //$NON-NLS-2$ + pkTableName); LOGGER.debug(logPrefix + "[" + COLUMN_NAME_PK_NAME + "] " + pkName); //$NON-NLS-1$ //$NON-NLS-2$ LOGGER.debug(logPrefix + "[" + COLUMN_NAME_UPDATE_RULE + "] " //$NON-NLS-1$ //$NON-NLS-2$ + onUpdateRule); LOGGER.debug(logPrefix + "[" + COLUMN_NAME_DELETE_RULE + "] " //$NON-NLS-1$ //$NON-NLS-2$ + onDeleteRule); LOGGER.debug(logPrefix + "[" + COLUMN_NAME_DEFERRABILITY + "] " //$NON-NLS-1$ //$NON-NLS-2$ + deferrability); } if (null == currFkName || !currFkName.equals(fkName) || keyIsValid) { currFkName = fkName; final String formatFkName = formatter.format(fkName); final String formatFkColumnName = formatter.format(fkColumnName); /* * We need to check for each foreign key's * column that the referenced table exists in * the current context because some columns * might be pointing to a synonym. */ final String formatPkTableName = formatter.format(pkTableName); IBasicTable pkTable = allTables.get(formatPkTableName); if (pkTable != null) { if (null == currFk || !formatFkName.equals(currFk.getName())) { final IKeyConstraint refPk = DBGMHelper.getPrimaryKey(pkTable); if (refPk != null) { /* * FIXME [BGA]: The * TypedObjectFactory does not work * as UniqueKeyConstraint and * ForeignKeyConstraint classes have * the same super interface * IKeyConstraint. We use an * explicit constructor instead. */ // currFk = typedObjFactory // .create(ForeignKeyConstraint.class); // currFk.setName(formatFkName); // currFk.setConstrainedTable(pkTable); currFk = new ForeignKeyConstraint(formatFkName, "", //$NON-NLS-1$ pkTable); currFk.setRemoteConstraint(refPk); currFk.setOnUpdateAction( CaptureHelper.getForeignKeyAction(onUpdateRule)); currFk.setOnDeleteAction( CaptureHelper.getForeignKeyAction(onDeleteRule)); foreignKeys.add(currFk); keyIsValid = true; } else { LOGGER.warn("Foreign key [" + formatFkName + "] has been ignored during import because the referenced primary key [" + formatPkTableName + "[" //$NON-NLS-1$ + formatter.format(pkName) + "]] could not be found in the current workspace"); keyIsValid = false; continue; } } final IBasicColumn column = allTablesColumns .get(CaptureHelper.getUniqueObjectName(tableName, formatFkColumnName)); if (column != null) { /* * Columns are ordered by PKTABLE_NAME, * KEY_SEQ in the returned ResultSet, so * we don't have to specify the position * of the constrained column when adding * it to the foreign key constraint. */ currFk.addColumn(column); } else { LOGGER.warn("Foreign key [" + formatFkName + "] has been ignored during import because the referencing column [" + tableName + "[" + formatFkColumnName //$NON-NLS-1$ + "]] could not be found in the current workspace"); keyIsValid = false; /* * Now the foreign key is invalid, we * remove it from the foreign keys list * that will be returned to the caller * of this method. */ foreignKeys.remove(currFk); } } else { if (LOGGER.isDebugEnabled()) { LOGGER.debug("Foreign key column [" + formatFkName + "[" //$NON-NLS-2$ + formatFkColumnName + "]] has been ignored during import because the referenced table [" + formatPkTableName + "] could not be found in the current workspace"); } } } } } } finally { CaptureHelper.safeClose(rset, null); } } } catch (SQLException sqle) { LOGGER.error("Unable to fetch foreign keys for table [" + tableName + "] from " + getConnectionVendorName(context) + " server: " + sqle.getMessage(), sqle); } return foreignKeys; }
From source file:org.apache.syncope.core.util.ImportExport.java
private List<String> sortByForeignKeys(final Connection conn, final Set<String> tableNames, final String schema) throws SQLException { Set<MultiParentNode<String>> roots = new HashSet<MultiParentNode<String>>(); final DatabaseMetaData meta = conn.getMetaData(); final Map<String, MultiParentNode<String>> exploited = new TreeMap<String, MultiParentNode<String>>( String.CASE_INSENSITIVE_ORDER); final Set<String> pkTableNames = new HashSet<String>(); for (String tableName : tableNames) { MultiParentNode<String> node = exploited.get(tableName); if (node == null) { node = new MultiParentNode<String>(tableName); roots.add(node);/*from w w w . java2 s.c o m*/ exploited.put(tableName, node); } ResultSet rs = null; pkTableNames.clear(); try { rs = meta.getImportedKeys(conn.getCatalog(), readSchema(), tableName); // this is to avoid repetition while (rs.next()) { pkTableNames.add(rs.getString("PKTABLE_NAME")); } } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { LOG.error("While closing tables result set", e); } } } for (String pkTableName : pkTableNames) { if (!tableName.equalsIgnoreCase(pkTableName)) { MultiParentNode<String> pkNode = exploited.get(pkTableName); if (pkNode == null) { pkNode = new MultiParentNode<String>(pkTableName); roots.add(pkNode); exploited.put(pkTableName, pkNode); } pkNode.addChild(node); if (roots.contains(node)) { roots.remove(node); } } } } final List<String> sortedTableNames = new ArrayList<String>(tableNames.size()); MultiParentNodeOp.traverseTree(roots, sortedTableNames); Collections.reverse(sortedTableNames); return sortedTableNames; }
From source file:it.eng.spagobi.meta.initializer.PhysicalModelInitializer.java
private void addForeignKeys(DatabaseMetaData dbMeta, PhysicalModel model, PhysicalTable table) { List<PhysicalForeignKey> foreignKeys; ResultSet rs;/* w ww . ja v a2 s. c o m*/ PhysicalForeignKey foreignKey; foreignKeys = new ArrayList(); foreignKey = null; try { rs = dbMeta.getImportedKeys(model.getCatalog(), model.getSchema(), table.getName()); /* * 1. PKTABLE_CAT String => primary key table catalog (may be null) 2. PKTABLE_SCHEM String => primary key table schema (may be null) 3. * PKTABLE_NAME String => primary key table name 4. PKCOLUMN_NAME String => primary key column name 5. FKTABLE_CAT String => foreign key table * catalog (may be null) being exported (may be null) 6. FKTABLE_SCHEM String => foreign key table schema (may be null) being exported (may be null) * 7. FKTABLE_NAME String => foreign key table name being exported 8. FKCOLUMN_NAME String => foreign key column name being exported 9. KEY_SEQ * short => sequence number within foreign key 10. UPDATE_RULE short => What happens to foreign key when primary is updated: importedNoAction - do * not allow update of primary key if it has been imported importedKeyCascade - change imported key to agree with primary key update * importedKeySetNull - change imported key to NULL if its primary key has been updated importedKeySetDefault - change imported key to default * values if its primary key has been updated importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x compatibility) 11. DELETE_RULE short * => What happens to the foreign key when primary is deleted: importedKeyNoAction - do not allow delete of primary key if it has been imported * importedKeyCascade - delete rows that import a deleted key importedKeySetNull - change imported key to NULL if its primary key has been deleted * importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x compatibility) importedKeySetDefault - change imported key to default if its * primary key has been deleted 12. FK_NAME String => foreign key name (may be null) 13. PK_NAME String => primary key name (may be null) 14. * DEFERRABILITY short => can the evaluation of foreign key constraints be deferred until commit: importedKeyInitiallyDeferred - see SQL92 for * definition importedKeyInitiallyImmediate - see SQL92 for definition importedKeyNotDeferrable - see SQL92 for definition */ String fkName = null; PhysicalTable sourceTable = null; PhysicalTable destinationTable = null; String pkName = null; while (rs.next()) { fkName = rs.getString("FK_NAME"); sourceTable = model.getTable(rs.getString("FKTABLE_NAME")); destinationTable = model.getTable(rs.getString("PKTABLE_NAME")); if (destinationTable == null || sourceTable == null) { // skip this foreign key because table is not found in the // physical model log("Foreign Key skipped because table was not found in the physical model"); } else { if (foreignKey == null) { // OK it's the first iteration foreignKey = FACTORY.createPhysicalForeignKey(); getPropertiesInitializer().addProperties(foreignKey); foreignKey.setName(fkName); foreignKey.setSourceName(fkName); foreignKey.setSourceTable(sourceTable); foreignKey.setDestinationName(rs.getString("PK_NAME")); foreignKey.setDestinationTable(destinationTable); pkName = rs.getString("PK_NAME"); } else if (!foreignKey.getSourceName().equals(fkName)) { // we // have // finished // with // the // previous // fk // table.getForeignKeys().add(foreignKey); model.getForeignKeys().add(foreignKey); foreignKey = FACTORY.createPhysicalForeignKey(); getPropertiesInitializer().addProperties(foreignKey); foreignKey.setName(fkName); foreignKey.setSourceName(fkName); foreignKey.setSourceTable(sourceTable); foreignKey.setDestinationName(rs.getString("PK_NAME")); foreignKey.setDestinationTable(destinationTable); pkName = rs.getString("PK_NAME"); } PhysicalColumn c = sourceTable.getColumn(rs.getString("FKCOLUMN_NAME")); foreignKey.getSourceColumns().add(sourceTable.getColumn(rs.getString("FKCOLUMN_NAME"))); c = destinationTable.getColumn(rs.getString("PKCOLUMN_NAME")); foreignKey.getDestinationColumns() .add(destinationTable.getColumn(rs.getString("PKCOLUMN_NAME"))); } } // add the last or the only foreign key found if (foreignKey != null) { if (destinationTable == null || sourceTable == null) { // skip this foreign key because table was not found in the // physical model log("Foreign Key skipped because table was not found in the physical model"); } else { model.getForeignKeys().add(foreignKey); foreignKey = FACTORY.createPhysicalForeignKey(); getPropertiesInitializer().addProperties(foreignKey); foreignKey.setName(fkName); foreignKey.setSourceName(fkName); foreignKey.setSourceTable(sourceTable); foreignKey.setDestinationName(pkName); foreignKey.setDestinationTable(destinationTable); } } rs.close(); } catch (Throwable t) { throw new RuntimeException("Impossible to initialize foreignKeys metadata", t); } }
From source file:it.unibas.spicy.persistence.relational.DAORelational.java
private void loadForeignKeys(IDataSourceProxy dataSource, DatabaseMetaData databaseMetaData, String catalog, String schemaName, boolean source, int scenarioNo) throws DAOException { IConnectionFactory connectionFactory = new SimpleDbConnectionFactory(); Connection connection = getConnectionToPostgres(connectionFactory); try {//from w w w .j av a 2s.co m Statement statement = connection.createStatement(); String[] tableTypes = new String[] { "TABLE" }; ResultSet tableResultSet = databaseMetaData.getTables(catalog, schemaName, null, tableTypes); while (tableResultSet.next()) { String tableName = tableResultSet.getString("TABLE_NAME"); if (!this.dataDescription.checkLoadTable(tableName)) { continue; } if (logger.isDebugEnabled()) logger.debug("Searching foreign keys. ANALYZING TABLE = " + tableName); ResultSet resultSet = databaseMetaData.getImportedKeys(catalog, null, tableName); List<String> listOfPrimaryKey = new ArrayList<String>(); List<String> listOfForeignKey = new ArrayList<String>(); String previousTableName = ""; while (resultSet.next()) { String pkTableName = resultSet.getString("PKTABLE_NAME"); String pkColumnName = resultSet.getString("PKCOLUMN_NAME"); String keyPrimaryKey = pkTableName + "." + pkColumnName; //AttributeNode primaryKey = (AttributeNode)DataSourceFactory.getNode(keyPrimary); String fkTableName = resultSet.getString("FKTABLE_NAME"); String fkColumnName = resultSet.getString("FKCOLUMN_NAME"); String keyForeignKey = fkTableName + "." + fkColumnName; if (logger.isDebugEnabled()) logger.debug("Analyzing foreign key: " + keyForeignKey + " references " + keyPrimaryKey); if (!this.dataDescription.checkLoadTable(pkTableName) || !this.dataDescription.checkLoadTable(fkTableName)) { if (logger.isDebugEnabled()) logger.debug("Check load tables. Foreign key discarded: " + keyForeignKey + " references " + keyPrimaryKey); continue; } if (!this.dataDescription.checkLoadAttribute(pkTableName, pkColumnName) || !this.dataDescription.checkLoadAttribute(fkTableName, fkColumnName)) { if (logger.isDebugEnabled()) logger.debug("Check load attributes. Foreign key discarded: " + keyForeignKey + " references " + keyPrimaryKey); continue; } if (logger.isDebugEnabled()) logger.debug("Analyzing Primary Key: " + keyPrimaryKey + " Found a Foreign Key: " + fkColumnName + " in table " + fkTableName); //giannisk alter table, add foreign key String fkTable, pkTable; if (source) { fkTable = SpicyEngineConstants.SOURCE_SCHEMA_NAME + String.valueOf(scenarioNo) + ".\"" + fkTableName + "\""; pkTable = SpicyEngineConstants.SOURCE_SCHEMA_NAME + String.valueOf(scenarioNo) + ".\"" + pkTableName + "\""; } else { fkTable = SpicyEngineConstants.TARGET_SCHEMA_NAME + String.valueOf(scenarioNo) + ".\"" + fkTableName + "\""; pkTable = SpicyEngineConstants.TARGET_SCHEMA_NAME + String.valueOf(scenarioNo) + ".\"" + pkTableName + "\""; } statement.executeUpdate("ALTER TABLE " + fkTable + " ADD FOREIGN KEY (" + fkColumnName + ") REFERENCES " + pkTable + " (" + pkColumnName + ");"); if (!listOfPrimaryKey.contains(keyPrimaryKey) && (previousTableName.equals("") || previousTableName.equals(pkTableName))) { if (logger.isDebugEnabled()) logger.debug("Adding nodes to collection: " + keyPrimaryKey + " - " + keyForeignKey); listOfPrimaryKey.add(keyPrimaryKey); listOfForeignKey.add(keyForeignKey); } else if (!listOfPrimaryKey.isEmpty() && !listOfForeignKey.isEmpty()) { if (logger.isDebugEnabled()) logger.debug("Generating constraint: " + listOfForeignKey + " reference " + listOfPrimaryKey); DAORelationalUtility.generateConstraint(listOfForeignKey.toArray(), listOfPrimaryKey.toArray(), dataSource); listOfPrimaryKey.clear(); listOfForeignKey.clear(); listOfPrimaryKey.add(keyPrimaryKey); listOfForeignKey.add(keyForeignKey); } previousTableName = pkTableName; } if (logger.isDebugEnabled()) logger.debug("Main loop: " + listOfForeignKey + " reference " + listOfPrimaryKey); if (!listOfPrimaryKey.isEmpty() && !listOfForeignKey.isEmpty()) { DAORelationalUtility.generateConstraint(listOfForeignKey.toArray(), listOfPrimaryKey.toArray(), dataSource); } if (logger.isDebugEnabled()) logger.debug("Foreign keys loaded. Exiting"); } } catch (SQLException ex) { logger.error(ex); } finally { //close connection if (connection != null) connectionFactory.close(connection); } }
From source file:de.erdesignerng.dialect.JDBCReverseEngineeringStrategy.java
/** * Reverse engineer relations for a table. * * @param aModel the model/*from ww w . j av a2 s. c o m*/ * @param aOptions the options * @param aNotifier the notifier * @param aTableEntry the table entry * @param aConnection the connection * @throws SQLException is thrown in case of an error * @throws ReverseEngineeringException is thrown in case of an error */ protected void reverseEngineerRelations(Model aModel, ReverseEngineeringOptions aOptions, ReverseEngineeringNotifier aNotifier, TableEntry aTableEntry, Connection aConnection) throws SQLException, ReverseEngineeringException { DatabaseMetaData theMetaData = aConnection.getMetaData(); String theSchemaName = null; String theCatalogName = null; if (aTableEntry != null) { theSchemaName = aTableEntry.getSchemaName(); theCatalogName = aTableEntry.getCatalogName(); } int theSysCounter = 0; List<Relation> theNewRelations = new ArrayList<>(); String theImportingTableName = aModel.getDialect().getCastType().cast(aTableEntry.getTableName()); aNotifier.notifyMessage(ERDesignerBundle.ENGINEERINGRELATION, theImportingTableName); Table theImportingTable; switch (aOptions.getTableNaming()) { case STANDARD: theImportingTable = aModel.getTables().findByName(theImportingTableName); break; case INCLUDE_SCHEMA: theImportingTable = aModel.getTables().findByNameAndSchema(theImportingTableName, theSchemaName); break; default: throw new RuntimeException("Not supported naming type"); } if (theImportingTable == null) { throw new ReverseEngineeringException("Cannot find table in model : " + theImportingTableName); } String theOldFKName = null; // Foreign keys Relation theNewRelation = null; ResultSet theForeignKeys = theMetaData.getImportedKeys(theCatalogName, theSchemaName, aTableEntry.getTableName()); while (theForeignKeys.next()) { String theFKName = theForeignKeys.getString("FK_NAME"); if ((theNewRelation == null) || (!theFKName.equals(theOldFKName))) { theOldFKName = theFKName; String thePKTableName = theForeignKeys.getString("PKTABLE_NAME"); String thePKTableSchema = theForeignKeys.getString("PKTABLE_SCHEM"); String theUpdateRule = theForeignKeys.getString("UPDATE_RULE"); String theDeleteRule = theForeignKeys.getString("DELETE_RULE"); Table theExportingTable; switch (aOptions.getTableNaming()) { case INCLUDE_SCHEMA: theExportingTable = aModel.getTables() .findByNameAndSchema(dialect.getCastType().cast(thePKTableName), thePKTableSchema); break; case STANDARD: theExportingTable = aModel.getTables().findByName(dialect.getCastType().cast(thePKTableName)); break; default: throw new RuntimeException("Naming not supported : " + aOptions.getTableNaming()); } if (theExportingTable != null) { // The relation is only added to the model // if the exporting table is also part of the model String theRelationName = dialect.getCastType().cast(theFKName); theNewRelation = aModel.getRelations().findByName(theRelationName); boolean addNew = false; if (theNewRelation == null) { addNew = true; } else { if (!theNewRelation.getExportingTable().equals(theExportingTable) || !theNewRelation.getImportingTable().equals(theImportingTable)) { theRelationName = "ERRELSYS_" + theSysCounter++; addNew = true; } } if (addNew) { theNewRelation = new Relation(); theNewRelation.setName(dialect.getCastType().cast(theRelationName)); theNewRelation.setOriginalName(theRelationName); theNewRelation.setExportingTable(theExportingTable); theNewRelation.setImportingTable(theImportingTable); if (theUpdateRule != null) { int theType = Integer.parseInt(theUpdateRule); theNewRelation.setOnUpdate(getCascadeType(theType)); } else { theNewRelation.setOnUpdate(CascadeType.NOTHING); } if (theDeleteRule != null) { int theType = Integer.parseInt(theDeleteRule); theNewRelation.setOnDelete(getCascadeType(theType)); } else { theNewRelation.setOnDelete(CascadeType.NOTHING); } theNewRelations.add(theNewRelation); } } } if ((theNewRelation != null) && (theNewRelation.getImportingTable() != null) && (theNewRelation.getExportingTable() != null)) { String thePKColumnName = dialect.getCastType().cast(theForeignKeys.getString("PKCOLUMN_NAME")); String theFKColumnName = dialect.getCastType().cast(theForeignKeys.getString("FKCOLUMN_NAME")); Attribute<Table> theExportingAttribute = theNewRelation.getExportingTable().getAttributes() .findByName(dialect.getCastType().cast(thePKColumnName)); if (theExportingAttribute == null) { throw new ReverseEngineeringException("Cannot find column " + thePKColumnName + " in table " + theNewRelation.getExportingTable().getName()); } Index thePrimaryKey = theNewRelation.getExportingTable().getPrimarykey(); if (thePrimaryKey == null) { throw new ReverseEngineeringException("Table " + theNewRelation.getExportingTable().getName() + " does not have a primary key"); } IndexExpression theExpression = thePrimaryKey.getExpressions().findByAttributeName(thePKColumnName); if (theExpression == null) { throw new RuntimeException("Cannot find attribute " + thePKColumnName + " in primary key for table " + theNewRelation.getExportingTable().getName()); } Attribute<Table> theImportingAttribute = theNewRelation.getImportingTable().getAttributes() .findByName(theFKColumnName); if (theImportingAttribute == null) { throw new ReverseEngineeringException("Cannot find column " + theFKColumnName + " in table " + theNewRelation.getImportingTable().getName()); } theNewRelation.getMapping().put(theExpression, theImportingAttribute); } } theForeignKeys.close(); try { for (Relation theRelation : theNewRelations) { try { aModel.addRelation(theRelation); } catch (ElementAlreadyExistsException e) { // This might happen for instance on DB2 databases. We will try to generate a new name here!!! int counter = 0; String theNewName = null; while (counter == 0 || aModel.getRelations().findByName(dialect.getCastType().cast(theNewName)) != null) { counter++; theNewName = theRelation.getExportingTable().getName() + "_" + theRelation.getImportingTable() + "_FK" + counter; } LOGGER.warn("Relation " + theRelation.getName() + " exists. Renaming it to " + theNewName); theRelation.setName(theNewName); aModel.addRelation(theRelation); } } } catch (ElementInvalidNameException | VetoException | ElementAlreadyExistsException e) { throw new ReverseEngineeringException(e.getMessage(), e); } }
From source file:jef.database.DbMetaData.java
/** * /* w w w. j ava 2s .c o m*/ * * @param schema * @param tableName * @return * @throws SQLException */ public List<ForeignKeyItem> getForeignKey(String schema, String tableName) throws SQLException { tableName = info.profile.getObjectNameToUse(tableName); Connection conn = getConnection(false); DatabaseMetaData databaseMetaData = conn.getMetaData(); ResultSet rs = null; try { rs = databaseMetaData.getImportedKeys(null, schema, tableName); List<ForeignKeyItem> fks = ResultPopulatorImpl.instance .toPlainJavaObject(new ResultSetImpl(rs, this.getProfile()), FK_TRANSFORMER); return fks; } finally { DbUtils.close(rs); releaseConnection(conn); } }
From source file:com.netspective.axiom.policy.AnsiDatabasePolicy.java
public void reverseEngineer(Writer writer, Connection conn, String catalog, String schemaPattern) throws IOException, SQLException { Map dataTypesMap = prepareJdbcTypeInfoMap(); DatabaseMetaData dbmd = conn.getMetaData(); TextUtils textUtils = TextUtils.getInstance(); writer.write("<?xml version=\"1.0\"?>\n\n"); writer.write("<!-- Reverse engineered by Axiom\n"); writer.write(" driver: " + dbmd.getDriverName() + "\n"); writer.write(" driver-version: " + dbmd.getDriverVersion() + "\n"); writer.write(" product: " + dbmd.getDatabaseProductName() + "\n"); writer.write(" product-version: " + dbmd.getDatabaseProductVersion() + "\n"); writer.write(" available catalogs:"); ResultSet rs = null;/*from w w w .j a v a 2s .co m*/ try { rs = dbmd.getCatalogs(); while (rs.next()) { writer.write(" " + rs.getObject(1).toString()); } } finally { if (rs != null) rs.close(); } writer.write("\n"); writer.write(" available schemas:"); try { rs = dbmd.getSchemas(); while (rs.next()) { writer.write(" " + rs.getObject(1).toString()); } } finally { if (rs != null) rs.close(); } writer.write("\n"); writer.write("-->\n\n"); writer.write("<component xmlns:xdm=\"http://www.netspective.org/Framework/Commons/XMLDataModel\">\n"); writer.write(" <xdm:include resource=\"com/netspective/axiom/conf/axiom.xml\"/>\n"); writer.write(" <schema name=\"" + catalog + "." + schemaPattern + "\">\n"); Map dbmdTypeInfoByName = new HashMap(); Map dbmdTypeInfoByJdbcType = new HashMap(); ResultSet typesRS = null; try { typesRS = dbmd.getTypeInfo(); while (typesRS.next()) { int colCount = typesRS.getMetaData().getColumnCount(); Object[] typeInfo = new Object[colCount]; for (int i = 1; i <= colCount; i++) typeInfo[i - 1] = typesRS.getObject(i); dbmdTypeInfoByName.put(typesRS.getString(1), typeInfo); dbmdTypeInfoByJdbcType.put(new Integer(typesRS.getInt(2)), typeInfo); } } finally { if (typesRS != null) typesRS.close(); } ResultSet tables = null; try { tables = dbmd.getTables(catalog, schemaPattern, null, new String[] { "TABLE" }); while (tables.next()) { String tableNameOrig = tables.getString(3); String tableName = textUtils.fixupTableNameCase(tableNameOrig); writer.write(" <table name=\"" + tableName + "\">\n"); Map primaryKeys = new HashMap(); ResultSet pkRS = null; try { pkRS = dbmd.getPrimaryKeys(null, null, tableNameOrig); while (pkRS.next()) { primaryKeys.put(pkRS.getString(4), pkRS.getString(5)); } } catch (Exception e) { // driver may not support this function } finally { if (pkRS != null) pkRS.close(); } Map fKeys = new HashMap(); ResultSet fkRS = null; try { fkRS = dbmd.getImportedKeys(null, null, tableNameOrig); while (fkRS.next()) { fKeys.put(fkRS.getString(8), textUtils.fixupTableNameCase(fkRS.getString(3)) + "." + fkRS.getString(4).toLowerCase()); } } catch (Exception e) { // driver may not support this function } finally { if (fkRS != null) fkRS.close(); } // we keep track of processed columns so we don't duplicate them in the XML Set processedColsMap = new HashSet(); ResultSet columns = null; try { columns = dbmd.getColumns(null, null, tableNameOrig, null); while (columns.next()) { String columnNameOrig = columns.getString(4); if (processedColsMap.contains(columnNameOrig)) continue; processedColsMap.add(columnNameOrig); String columnName = columnNameOrig.toLowerCase(); writer.write(" <column name=\"" + columnName + "\""); try { if (fKeys.containsKey(columnNameOrig)) writer.write(" lookup-ref=\"" + fKeys.get(columnNameOrig) + "\""); else { short jdbcType = columns.getShort(5); String dataType = (String) dataTypesMap.get(new Integer(jdbcType)); if (dataType == null) dataType = Short.toString(jdbcType); writer.write(" type=\"" + dataType + "\""); } if (primaryKeys.containsKey(columnNameOrig)) writer.write(" primary-key=\"yes\""); if (columns.getString(18).equals("NO")) writer.write(" required=\"yes\""); String defaultValue = columns.getString(13); if (defaultValue != null) writer.write(" default=\"" + defaultValue + "\""); String remarks = columns.getString(12); if (remarks != null) writer.write(" descr=\"" + remarks + "\""); } catch (Exception e) { } writer.write("/>\n"); } } finally { if (columns != null) columns.close(); } writer.write(" </table>\n"); } } finally { tables.close(); } writer.write(" </schema>\n"); writer.write("</component>"); }
From source file:com.oltpbenchmark.catalog.Catalog.java
/** * Construct the set of Table objects from a given Connection handle * @param conn/*from w w w . j a v a 2 s . co m*/ * @return * @throws SQLException * @see http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html */ protected void init() throws SQLException { // Load the database's DDL this.benchmark.createDatabase(DB_TYPE, this.conn); // TableName -> ColumnName -> <FkeyTable, FKeyColumn> Map<String, Map<String, Pair<String, String>>> foreignKeys = new HashMap<String, Map<String, Pair<String, String>>>(); DatabaseMetaData md = conn.getMetaData(); ResultSet table_rs = md.getTables(null, null, null, new String[] { "TABLE" }); while (table_rs.next()) { if (LOG.isDebugEnabled()) LOG.debug(SQLUtil.debug(table_rs)); String internal_table_name = table_rs.getString(3); String table_name = origTableNames.get(table_rs.getString(3).toUpperCase()); assert (table_name != null) : "Unexpected table '" + table_rs.getString(3) + "' from catalog"; LOG.debug(String.format("ORIG:%s -> CATALOG:%s", internal_table_name, table_name)); String table_type = table_rs.getString(4); if (table_type.equalsIgnoreCase("TABLE") == false) continue; Table catalog_tbl = new Table(table_name); // COLUMNS if (LOG.isDebugEnabled()) LOG.debug("Retrieving COLUMN information for " + table_name); ResultSet col_rs = md.getColumns(null, null, internal_table_name, null); while (col_rs.next()) { if (LOG.isTraceEnabled()) LOG.trace(SQLUtil.debug(col_rs)); String col_name = col_rs.getString(4); int col_type = col_rs.getInt(5); String col_typename = col_rs.getString(6); Integer col_size = col_rs.getInt(7); String col_defaultValue = col_rs.getString(13); boolean col_nullable = col_rs.getString(18).equalsIgnoreCase("YES"); boolean col_autoinc = false; // FIXME col_rs.getString(22).toUpperCase().equals("YES"); Column catalog_col = new Column(catalog_tbl, col_name, col_type, col_typename, col_size); catalog_col.setDefaultValue(col_defaultValue); catalog_col.setAutoincrement(col_autoinc); catalog_col.setNullable(col_nullable); // FIXME col_catalog.setSigned(); if (LOG.isDebugEnabled()) LOG.debug( String.format("Adding %s.%s [%s / %d]", table_name, col_name, col_typename, col_type)); catalog_tbl.addColumn(catalog_col); } // WHILE col_rs.close(); // PRIMARY KEYS if (LOG.isDebugEnabled()) LOG.debug("Retrieving PRIMARY KEY information for " + table_name); ResultSet pkey_rs = md.getPrimaryKeys(null, null, internal_table_name); SortedMap<Integer, String> pkey_cols = new TreeMap<Integer, String>(); while (pkey_rs.next()) { String col_name = pkey_rs.getString(4); assert (catalog_tbl.getColumnByName(col_name) != null) : String .format("Unexpected primary key column %s.%s", table_name, col_name); int col_idx = pkey_rs.getShort(5); // HACK: SQLite doesn't return the KEY_SEQ, so if we get back // a zero for this value, then we'll just length of the pkey_cols map if (col_idx == 0) col_idx = pkey_cols.size(); LOG.debug(String.format("PKEY[%02d]: %s.%s", col_idx, table_name, col_name)); assert (pkey_cols.containsKey(col_idx) == false); pkey_cols.put(col_idx, col_name); } // WHILE pkey_rs.close(); catalog_tbl.setPrimaryKeyColumns(pkey_cols.values()); // INDEXES if (LOG.isDebugEnabled()) LOG.debug("Retrieving INDEX information for " + table_name); ResultSet idx_rs = md.getIndexInfo(null, null, internal_table_name, false, false); while (idx_rs.next()) { if (LOG.isDebugEnabled()) LOG.debug(SQLUtil.debug(idx_rs)); boolean idx_unique = (idx_rs.getBoolean(4) == false); String idx_name = idx_rs.getString(6); int idx_type = idx_rs.getShort(7); int idx_col_pos = idx_rs.getInt(8) - 1; String idx_col_name = idx_rs.getString(9); String sort = idx_rs.getString(10); SortDirectionType idx_direction; if (sort != null) { idx_direction = sort.equalsIgnoreCase("A") ? SortDirectionType.ASC : SortDirectionType.DESC; } else idx_direction = null; Index catalog_idx = catalog_tbl.getIndex(idx_name); if (catalog_idx == null) { catalog_idx = new Index(catalog_tbl, idx_name, idx_type, idx_unique); catalog_tbl.addIndex(catalog_idx); } assert (catalog_idx != null); catalog_idx.addColumn(idx_col_name, idx_direction, idx_col_pos); } // WHILE idx_rs.close(); // FOREIGN KEYS if (LOG.isDebugEnabled()) LOG.debug("Retrieving FOREIGN KEY information for " + table_name); ResultSet fk_rs = md.getImportedKeys(null, null, internal_table_name); foreignKeys.put(table_name, new HashMap<String, Pair<String, String>>()); while (fk_rs.next()) { if (LOG.isDebugEnabled()) LOG.debug(table_name + " => " + SQLUtil.debug(fk_rs)); assert (fk_rs.getString(7).equalsIgnoreCase(table_name)); String colName = fk_rs.getString(8); String fk_tableName = origTableNames.get(fk_rs.getString(3).toUpperCase()); String fk_colName = fk_rs.getString(4); foreignKeys.get(table_name).put(colName, Pair.of(fk_tableName, fk_colName)); } // WHILE fk_rs.close(); tables.put(table_name, catalog_tbl); } // WHILE table_rs.close(); // FOREIGN KEYS if (LOG.isDebugEnabled()) LOG.debug("Foreign Key Mappings:\n" + StringUtil.formatMaps(foreignKeys)); for (Table catalog_tbl : tables.values()) { Map<String, Pair<String, String>> fk = foreignKeys.get(catalog_tbl.getName()); for (Entry<String, Pair<String, String>> e : fk.entrySet()) { String colName = e.getKey(); Column catalog_col = catalog_tbl.getColumnByName(colName); assert (catalog_col != null); Pair<String, String> fkey = e.getValue(); assert (fkey != null); Table fkey_tbl = tables.get(fkey.first); if (fkey_tbl == null) { throw new RuntimeException("Unexpected foreign key parent table " + fkey); } Column fkey_col = fkey_tbl.getColumnByName(fkey.second); if (fkey_col == null) { throw new RuntimeException("Unexpected foreign key parent column " + fkey); } if (LOG.isDebugEnabled()) LOG.debug(catalog_col.fullName() + " -> " + fkey_col.fullName()); catalog_col.setForeignKey(fkey_col); } // FOR } // FOR return; }