List of usage examples for java.sql DatabaseMetaData getPrimaryKeys
ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException;
From source file:org.biomart.configurator.controller.MartController.java
public void updateDatasetFromSource(Dataset ds) throws SQLException, DataModelException { Mart mart = ds.getParentMart();//from ww w.j a v a2s. c om List<SourceSchema> sss = mart.getIncludedSchemas(); // assuming that only one for now if (McUtils.isCollectionEmpty(sss)) return; SourceSchema ss = sss.get(0); final DatabaseMetaData dmd = ss.getConnection().getMetaData(); final String catalog = ss.getConnection().getCatalog(); // List of objects storing orphan key column and its table name List<ForeignKey> orphanFKList = new ArrayList<ForeignKey>(); StringBuffer orphanSearch = new StringBuffer(); boolean orphanBool = false; /* * try { orphanBool = findOrphanFKFromDB(orphanFKList, orphanSearch, mart); if (orphanBool) { Frame frame = new * Frame(); Object[] options = { "Proceed", "Abort Synchronization" }; int n = JOptionPane .showOptionDialog( * frame, * "Some columns in relations no longer exist in source DB. This may be caused by renaming/dropping tables/columns in source DB.\n" * + * "When choose 'Proceed', you will be prompted to save this information for later use. Do you want to proceed?" * +"\n", "Schema Update Warning", JOptionPane.YES_NO_OPTION, JOptionPane.WARNING_MESSAGE, null, // do // not // * use a // custom // Icon options, // the titles of buttons options[1]); // default button title if (n == * JOptionPane.NO_OPTION) { return; } else{ new SaveOrphanKeyDialog("Orphan Relation", orphanSearch.toString()); * } } } catch (Exception e) { e.printStackTrace(); } // Now that user decides to sync GUI model to DB schema, * remove orphan foreign key clearOrphanForeignKey(orphanFKList); */ // Create a list of existing tables. During this method, we remove // from this list all tables that still exist in the database. At // the end of the method, the list contains only those tables // which no longer exist, so they will be dropped. final Collection<Table> tablesToBeDropped = new HashSet<Table>(ss.getTables()); // Load tables and views from database, then loop over them. ResultSet dbTables; String sName = ss.getJdbcLinkObject().getJdbcType().useSchema() ? ss.getJdbcLinkObject().getSchemaName() : catalog; dbTables = dmd.getTables(catalog, sName, "%", new String[] { "TABLE", "VIEW", "ALIAS", "SYNONYM" }); // Do the loop. final Collection<Table> tablesToBeKept = new HashSet<Table>(); while (dbTables.next()) { // Check schema and catalog. final String catalogName = dbTables.getString("TABLE_CAT"); final String schemaName = dbTables.getString("TABLE_SCHEM"); String schemaPrefix = schemaName; // What is the table called? final String dbTableName = dbTables.getString("TABLE_NAME"); Log.debug("Processing table " + catalogName + "." + dbTableName); // Look to see if we already have a table by this name defined. // If we do, reuse it. If not, create a new table. Table dbTable = ss.getTableByName(dbTableName); if (dbTable == null) try { dbTable = new SourceTable(ss, dbTableName); dbTable.setVisibleModified(true); ss.addTable(dbTable); } catch (final Throwable t) { throw new BioMartError(t); } // Add schema prefix to list. if (schemaPrefix != null) dbTable.addInPartitions(schemaPrefix); // Table exists, so remove it from our list of tables to be // dropped at the end of the method. tablesToBeDropped.remove(dbTable); tablesToBeKept.add(dbTable); } dbTables.close(); // Loop over all columns. for (final Iterator<Table> i = tablesToBeKept.iterator(); i.hasNext();) { final Table dbTable = (Table) i.next(); final String dbTableName = dbTable.getName(); // Make a list of all the columns in the table. Any columns // remaining in this list by the end of the loop will be // dropped. final Collection<Column> colsToBeDropped = new HashSet<Column>(dbTable.getColumnList()); // Clear out the existing schema partition info on all cols. for (final Iterator<Column> j = dbTable.getColumnList().iterator(); j.hasNext();) ((Column) j.next()).cleanInPartitions(); // Load the table columns from the database, then loop over // them. Log.debug("Loading table column list for " + dbTableName); ResultSet dbTblCols; dbTblCols = dmd.getColumns(catalog, sName, dbTableName, "%"); // FIXME: When using Oracle, if the table is a synonym then the // above call returns no results. while (dbTblCols.next()) { // Check schema and catalog. final String catalogName = dbTblCols.getString("TABLE_CAT"); final String schemaName = dbTblCols.getString("TABLE_SCHEM"); String schemaPrefix = null; // No prefix if partitions are empty; /* * if (!this.getPartitions().isEmpty()) { if ("".equals(dmd.getSchemaTerm())) // Use catalog name to get * prefix. schemaPrefix = (String) this.getPartitions().get( catalogName); else // Use schema name to * get prefix. schemaPrefix = (String) this.getPartitions().get( schemaName); // Don't want to include * if prefix is still null. if (schemaPrefix == null) continue; } */ // What is the column called, and is it nullable? final String dbTblColName = dbTblCols.getString("COLUMN_NAME"); Log.debug("Processing column " + dbTblColName); // Look to see if the column already exists on this table. // If it does, reuse it. Else, create it. Column dbTblCol = (Column) dbTable.getColumnByName(dbTblColName); if (dbTblCol == null) try { dbTblCol = new SourceColumn((SourceTable) dbTable, dbTblColName); dbTblCol.setVisibleModified(true); dbTable.addColumn(dbTblCol); } catch (final Throwable t) { throw new BioMartError(t); } // Column exists, so remove it from our list of columns to // be dropped at the end of the loop. colsToBeDropped.remove(dbTblCol); if (schemaPrefix != null) dbTblCol.addInPartitions(schemaPrefix); } dbTblCols.close(); // Drop all columns that are left in the list, as they no longer // exist in the database. for (final Iterator<Column> j = colsToBeDropped.iterator(); j.hasNext();) { final Column column = (Column) j.next(); Log.debug("Dropping redundant column " + column.getName()); dbTable.getColumnList().remove(column); } } // Remove from schema all tables not found in the database, using // the list we constructed above. for (final Iterator<Table> i = tablesToBeDropped.iterator(); i.hasNext();) { final Table existingTable = (Table) i.next(); Log.debug("Dropping redundant table " + existingTable); final String tableName = existingTable.getName(); // By clearing its keys we will also clear its relations. for (final Iterator<Key> j = existingTable.getKeys().iterator(); j.hasNext();) { j.next().removeAllRelations(); } existingTable.setPrimaryKey(null); existingTable.getForeignKeys().clear(); ss.removeTableByName(tableName); } // Get and create primary keys. // Work out a list of all foreign keys currently existing. // Any remaining in this list later will be dropped. final Collection<ForeignKey> fksToBeDropped = new HashSet<ForeignKey>(); for (final Iterator<Table> i = ss.getTables().iterator(); i.hasNext();) { final Table t = (Table) i.next(); fksToBeDropped.addAll(t.getForeignKeys()); // Obtain the primary key from the database. Even in databases // without referential integrity, the primary key is still // defined and can be obtained from the metadata. Log.debug("Loading table primary keys"); String searchCatalog = catalog; String searchSchema = sName; /* * if (!t.getSchemaPartitions().isEmpty()) { // Locate partition with first prefix. final String prefix = * (String) t.getSchemaPartitions() .iterator().next(); String schemaName = (String) new InverseMap(this * .getPartitions()).get(prefix); if (schemaName == null) // Should never happen. throw new BioMartError(); * if ("".equals(dmd.getSchemaTerm())) searchCatalog = schemaName; searchSchema = schemaName; } */ final ResultSet dbTblPKCols = dmd.getPrimaryKeys(searchCatalog, searchSchema, t.getName()); // Load the primary key columns into a map keyed by column // position. // In other words, the first column in the key has a map key of // 1, and so on. We do this because we can't guarantee we'll // read the key columns from the database in the correct order. // We keep the map sorted, so that when we iterate over it later // we get back the columns in the correct order. final Map<Short, Column> pkCols = new TreeMap<Short, Column>(); while (dbTblPKCols.next()) { final String pkColName = dbTblPKCols.getString("COLUMN_NAME"); final Short pkColPosition = new Short(dbTblPKCols.getShort("KEY_SEQ")); pkCols.put(pkColPosition, t.getColumnByName(pkColName)); } dbTblPKCols.close(); // Did DMD find a PK? If not, which is really unusual but // potentially may happen, attempt to find one by looking for a // single column with the same name as the table or with '_id' // appended. // Only do this if we are using key-guessing. if (pkCols.isEmpty() && ss.getJdbcLinkObject().isKeyGuessing()) { Log.debug("Found no primary key, so attempting to guess one"); // Plain version first. Column candidateCol = (Column) t.getColumnByName(t.getName()); // Try with '_id' appended if plain version turned up // nothing. if (candidateCol == null) candidateCol = (Column) t.getColumnByName(t.getName() + Resources.get("primaryKeySuffix")); // Found something? Add it to the primary key columns map, // with a dummy key of 1. (Use Short for the key because // that // is what DMD would have used had it found anything // itself). if (candidateCol != null) pkCols.put(Short.valueOf("1"), candidateCol); } // Obtain the existing primary key on the table, if the table // previously existed and even had one in the first place. final PrimaryKey existingPK = t.getPrimaryKey(); // Did we find a PK on the database copy of the table? if (!pkCols.isEmpty()) { // Yes, we found a PK on the database copy of the table. So, // create a new key based around the columns we identified. PrimaryKey candidatePK; try { candidatePK = new PrimaryKey(new ArrayList<Column>(pkCols.values())); } catch (final Throwable th) { throw new BioMartError(th); } // If the existing table has no PK, or has a PK which // matches and is not incorrect, or has a PK which does not // match // and is not handmade, replace that PK with the one we // found. // This way we preserve any existing handmade PKs, and don't // override any marked as incorrect. try { if (existingPK == null) t.setPrimaryKey(candidatePK); else if (existingPK.equals(candidatePK) && existingPK.getStatus().equals(ComponentStatus.HANDMADE)) existingPK.setStatus(ComponentStatus.INFERRED); else if (!existingPK.equals(candidatePK) && !existingPK.getStatus().equals(ComponentStatus.HANDMADE)) t.setPrimaryKey(candidatePK); } catch (final Throwable th) { throw new BioMartError(th); } } else // No, we did not find a PK on the database copy of the // table, so that table should not have a PK at all. So if the // existing table has a PK which is not handmade, remove it. if (existingPK != null && !existingPK.getStatus().equals(ComponentStatus.HANDMADE)) try { t.setPrimaryKey(null); } catch (final Throwable th) { throw new BioMartError(th); } } // Are we key-guessing? Key guess the foreign keys, passing in a // reference to the list of existing foreign keys. After this call // has completed, the list will contain all those foreign keys which // no longer exist, and can safely be dropped. if (ss.getJdbcLinkObject().isKeyGuessing()) this.synchroniseKeysUsingKeyGuessing(ss, fksToBeDropped); // Otherwise, use DMD to do the same, also passing in the list of // existing foreign keys to be updated as the call progresses. Also // pass in the DMD details so it doesn't have to work them out for // itself. else this.synchroniseKeysUsingDMD(ss, fksToBeDropped, dmd, sName, catalog); // Drop any foreign keys that are left over (but not handmade ones). for (final Iterator<ForeignKey> i = fksToBeDropped.iterator(); i.hasNext();) { final Key k = (Key) i.next(); if (k.getStatus().equals(ComponentStatus.HANDMADE)) continue; Log.debug("Dropping redundant foreign key " + k); for (final Iterator<Relation> r = k.getRelations().iterator(); r.hasNext();) { final Relation rel = (Relation) r.next(); rel.getFirstKey().getRelations().remove(rel); rel.getSecondKey().getRelations().remove(rel); } k.getTable().getForeignKeys().remove(k); } // rebuild mart this.rebuildMartFromSource(mart); }