This method returns a list of entity types supported by the database + *
This method returns a set of entity types supported by the database * adapter. This list will always be limited to Tables, Views and * Sequences.
* @@ -60,44 +92,38 @@ public class Database { * Tables, Views and Sequences. * * @param connection - * @param schema - - * This parameter is somewhat bogus. It is used to determine if the - * adapter defines an SQL statement for finding entities of a - * particular types. - * @return + * @return a set of Strings, typically "TABLE", "VIEW", and "SEQUENCE" * @throws SQLException */ - public String[] getEntityTypes(Connection connection, Schema schema) - throws SQLException { + private Set initializeEntityTypes(Connection connection) throws SQLException { Set set = new HashSet(); - if (this.databaseAdapter.getShowTableQuery(schema.getName(), false) != null) { - set.add(IQuantumConstants.Table); - } else if (this.databaseAdapter.getShowViewQuery(schema.getName(), false) != null) { - set.add(IQuantumConstants.View); - } else if (this.databaseAdapter.getShowSequenceQuery(schema.getName(), false) != null) { - set.add(IQuantumConstants.Sequence); + if (this.databaseAdapter.getShowTableQuery(this.bookmark.getUsername()) != null) { + set.add(Entity.TABLE_TYPE); + } else if (this.databaseAdapter.getShowViewQuery(this.bookmark.getUsername()) != null) { + set.add(Entity.VIEW_TYPE); + } else if (this.databaseAdapter.getShowSequenceQuery(this.bookmark.getUsername()) != null) { + set.add(Entity.SEQUENCE_TYPE); } DatabaseMetaData metaData = connection.getMetaData(); ResultSet resultSet = metaData.getTableTypes(); while (resultSet.next()) { - String type = resultSet.getString("TABLE_TYPE"); + String type = resultSet.getString(TABLE_TYPE_METADATA_TABLE_TYPE); if (type != null) { + // Informix, in particular, pads this with extra spaces type = type.trim(); } if (STANDARD_TABLE_TYPES.contains(type)) { set.add(type); } } - - return (String[]) set.toArray(new String[set.size()]); + return set; } public String getInformation() throws SQLException { try { - Connection connection = this.bookmark.getConnection(); - DatabaseMetaData metaData = connection.getMetaData(); + DatabaseMetaData metaData = getMetaData(); return metaData == null ? null : metaData.getDatabaseProductName() + " " + metaData.getDatabaseProductVersion(); @@ -137,10 +163,12 @@ public class Database { throws SQLException { List list = new ArrayList(); - String[] types = (type == null) ? ALL_TYPES : new String[] { type }; + String[] types = (type == null) ? ALL_TABLE_TYPES : new String[] { type }; for (int i = 0; i < types.length; i++) { list.addAll(getEntitiesList(bookmark, connection, types[i], schema)); + // TODO: This should be polished so that synonyms can be shown with different icons as regular Entities + list.addAll(getSynonymsList(bookmark, connection, types[i], schema)); } return (Entity[]) list.toArray(new Entity[list.size()]); @@ -149,66 +177,223 @@ public class Database { protected List getEntitiesList(Bookmark bookmark, Connection connection, String type, Schema schema) throws SQLException { - String sql = getSQL(bookmark, type, schema); List list = new ArrayList(); - SQLResults results = null; + // We try first the JDBC driver + DatabaseMetaData metaData = connection.getMetaData(); + // getTables needs a null schema to get all the schemas. So we don't pass a "" schema, but a null one + ResultSet set = null; + if (metaData.supportsSchemasInTableDefinitions()) { + set = metaData.getTables(null, (schema != null) ? schema.getName() : null, "%", new String[] { type }); + } else { + set = metaData.getTables(null, null, "%", new String[] { type }); + } + + while (set.next()) { + String tempSchema = set.getString(TABLE_METADATA_TABLE_SCHEM); + tempSchema = (tempSchema == null) ? "" : tempSchema.trim(); + String tableName = set.getString(TABLE_METADATA_TABLE_NAME); + tableName = (tableName == null) ? "" : tableName.trim(); + + if (tableName != null && tableName.length() > 0) { + Entity entity = EntityFactory.getInstance().create(bookmark, tempSchema, tableName, type, false); + if (entity != null) { + list.add(entity); + } + } + } + set.close(); + // If we have some results, then the JDBC driver is working, + // so we return the results and quit + if (list.size() > 0) + return list; + + + // If no results, we check also the sql query to get the list of entities + SQLResultSetResults results = null; + // Get the proper sql query to the appropiate type of entity + String sql = getSQL(bookmark, type, schema); + // If nothing returned, too bad, it seems there is no sql query for that database and entity type if (sql != null) { - results = MultiSQLServer.getInstance().execute(connection, sql); + results = (SQLResultSetResults) MultiSQLServer.getInstance().execute( + bookmark, connection, sql, Integer.MAX_VALUE); for (int i = 1, size = (results == null) ? 0 : results.getRowCount(); i <= size; i++) { String schemaName = results.getColumnCount() == 1 ? schema.getName() : results.getElement(1, i).toString(); + if (schemaName != null) { + schemaName = schemaName.trim(); + } String tableName = results.getColumnCount() == 1 ? results.getElement(1, i).toString() : results.getElement(2, i).toString(); if (tableName != null && tableName.length() > 0) { Entity entity = EntityFactory.getInstance().create( - bookmark, schemaName, tableName, type); + bookmark, schemaName, tableName, type, false); if (entity != null) { list.add(entity); } } } } - // If we have some results, we go back - if (results != null) return list; - // Else, we try the JDBC driver - DatabaseMetaData metaData = connection.getMetaData(); - // getTables needs a null schema to get all the schemas. So we don't pass a "" schema, but a null one - ResultSet set = null; - if (metaData.supportsSchemasInTableDefinitions()) - set = metaData.getTables(null, (schema != null) ? schema.getName() : null, "%", new String[] { type }); - else - set = metaData.getTables(null, null, "%", new String[] { type }); - - while (set.next()) { - String tempSchema = set.getString("TABLE_SCHEM"); - tempSchema = (tempSchema == null) ? "" : tempSchema.trim(); - String tableName = set.getString("TABLE_NAME"); - tableName = (tableName == null) ? "" : tableName.trim(); + return list; + } + /** + * Returns a list with the synonym objects of the given type, using a query + * @param bookmark + * @param connection + * @param type + * @param schema + * @return + * @throws SQLException + */ + protected List getSynonymsList(Bookmark bookmark, Connection connection, String type, Schema schema) + throws SQLException { + List list = new ArrayList(); + // We try first the JDBC driver + DatabaseMetaData metaData = connection.getMetaData(); + SQLResultSetResults results = null; + // Get the proper sql query to the appropiate type of entity + String sql = this.databaseAdapter.getShowSynonymsQuery(schema.getName(), type); + // If nothing returned, too bad, it seems there is no sql query for that database and entity type + if (sql != null) { + results = (SQLResultSetResults) MultiSQLServer.getInstance().execute( + bookmark, connection, sql, Integer.MAX_VALUE); + for (int i = 1, size = (results == null) ? 0 : results.getRowCount(); i <= size; i++) { + String schemaName = results.getColumnCount() == 1 + ? schema.getName() : results.getElement(1, i).toString(); + if (schemaName != null) { + schemaName = schemaName.trim(); + } + String tableName = results.getColumnCount() == 1 + ? results.getElement(1, i).toString() + : results.getElement(2, i).toString(); if (tableName != null && tableName.length() > 0) { - Entity entity = EntityFactory.getInstance().create(bookmark, tempSchema, tableName, type); + Entity entity = EntityFactory.getInstance().create( + bookmark, schemaName, tableName, type, true); if (entity != null) { list.add(entity); } } } - set.close(); - return list; + } + return list; +} + + public DataType[] getTypes() throws NotConnectedException, SQLException { + DatabaseMetaData metaData = getMetaData(); + List list = new ArrayList(); + ResultSet results = metaData.getTypeInfo(); + try { + while (results.next()) { + list.add(new DataType( + results.getInt(TYPE_INFO_METADATA_DATA_TYPE), + results.getString(TYPE_INFO_METADATA_TYPE_NAME), + results.getLong(TYPE_INFO_METADATA_PRECISION), + results.getString(TYPE_INFO_METADATA_LITERAL_PREFIX), + results.getString(TYPE_INFO_METADATA_LITERAL_SUFFIX), + results.getString(TYPE_INFO_METADATA_CREATE_PARMS) + )); + } + } finally { + results.close(); + } + return (DataType[]) list.toArray(new DataType[list.size()]); } - private String getSQL(Bookmark bookmark, String type, Schema schema) { + /** + * @return + * @throws NotConnectedException + * @throws SQLException + */ + private DatabaseMetaData getMetaData() throws NotConnectedException, SQLException { + Connection connection = this.bookmark.getConnection(); + DatabaseMetaData metaData = connection.getMetaData(); + return metaData; + } + + private String getSQL(Bookmark bookmark, String type, Schema schema) { if (Entity.TABLE_TYPE.equals(type)) { - return this.databaseAdapter.getShowTableQuery(schema.getName(), schema.isDefault()); + return this.databaseAdapter.getShowTableQuery(schema.getName()); } else if (Entity.VIEW_TYPE.equals(type)) { - return this.databaseAdapter.getShowViewQuery(schema.getName(), schema.isDefault()); + return this.databaseAdapter.getShowViewQuery(schema.getName()); } else if (Entity.SEQUENCE_TYPE.equals(type)) { - return this.databaseAdapter.getShowSequenceQuery(schema.getName(), schema.isDefault()); + return this.databaseAdapter.getShowSequenceQuery(schema.getName()); } else { return null; } } - + + public ForeignKey[] getExportedKeys(String schema, String entityName) + throws NotConnectedException, SQLException { + DatabaseMetaData metaData = getMetaData(); + List list = new ArrayList(); + return getForeignKeys(list, metaData.getExportedKeys(null, schema, entityName)); + } + + public ForeignKey[] getImportedKeys(String schema, String entityName) + throws NotConnectedException, SQLException { + DatabaseMetaData metaData = getMetaData(); + List list = new ArrayList(); + return getForeignKeys(list, metaData.getImportedKeys(null, schema, entityName)); + } + + /** + * @param list + * @param resultSet + * @return + * @throws SQLException + */ + private ForeignKey[] getForeignKeys(List list, ResultSet resultSet) throws SQLException { + ForeignKeyImpl foreignKey = null; + + int lowestKeySequence = Integer.MAX_VALUE; + try { + while (resultSet.next()) { + int keySequence = resultSet.getInt(FOREIGN_KEY_METADATA_KEY_SEQ); + lowestKeySequence = Math.min(lowestKeySequence, keySequence); + + if (keySequence == lowestKeySequence || foreignKey == null) { + foreignKey = new ForeignKeyImpl(); + list.add(foreignKey); + foreignKey.setName(resultSet.getString(FOREIGN_KEY_METADATA_FK_NAME)); + foreignKey.setDeleteRule(resultSet.getShort(FOREIGN_KEY_METADATA_DELETE_RULE)); + foreignKey.setForeignEntitySchema(resultSet.getString(FOREIGN_KEY_METADATA_FKTABLE_SCHEM)); + foreignKey.setForeignEntityName(resultSet.getString(FOREIGN_KEY_METADATA_FKTABLE_NAME)); + foreignKey.setLocalEntitySchema(resultSet.getString(FOREIGN_KEY_METADATA_PKTABLE_SCHEM)); + foreignKey.setLocalEntityName(resultSet.getString(FOREIGN_KEY_METADATA_PKTABLE_NAME)); + } + + foreignKey.addColumns( + resultSet.getString(FOREIGN_KEY_METADATA_PKCOLUMN_NAME), + resultSet.getString(FOREIGN_KEY_METADATA_FKCOLUMN_NAME)); + } + return (ForeignKey[]) list.toArray(new ForeignKey[list.size()]); + } finally { + resultSet.close(); + } + } + /** + * @return + * @throws SQLException + * @throws NotConnectedException + */ + public Schema[] getSchemas() throws NotConnectedException, SQLException { + DatabaseMetaData metaData = getMetaData(); + List list = new ArrayList(); + + if (metaData.supportsSchemasInTableDefinitions()) { + ResultSet resultSet = metaData.getSchemas(); + try { + while (resultSet.next()) { + String schemaName = resultSet.getString("TABLE_SCHEM"); + list.add(new Schema(schemaName)); + } + } finally { + resultSet.close(); + } + } + return (Schema[]) list.toArray(new Schema[list.size()]); + } }