package com.quantum.model; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Collection; import java.util.Collections; import java.util.HashSet; import java.util.List; import java.util.Set; import com.quantum.adapters.DatabaseAdapter; import com.quantum.sql.MultiSQLServer; import com.quantum.sql.SQLResultSetResults; /** * @author BC */ public class Database { private static final int TABLE_METADATA_TABLE_SCHEM = 2; private static final int TABLE_METADATA_TABLE_NAME = 3; private static final int TABLE_TYPE_METADATA_TABLE_TYPE = 1; private static final int FOREIGN_KEY_METADATA_PKTABLE_SCHEM = 2; private static final int FOREIGN_KEY_METADATA_PKTABLE_NAME = 3; private static final int FOREIGN_KEY_METADATA_PKCOLUMN_NAME = 4; private static final int FOREIGN_KEY_METADATA_FKTABLE_SCHEM = 6; private static final int FOREIGN_KEY_METADATA_FKTABLE_NAME = 7; private static final int FOREIGN_KEY_METADATA_FKCOLUMN_NAME = 8; private static final int FOREIGN_KEY_METADATA_KEY_SEQ = 9; private static final int FOREIGN_KEY_METADATA_DELETE_RULE = 11; private static final int FOREIGN_KEY_METADATA_FK_NAME = 12; private static final int TYPE_INFO_METADATA_TYPE_NAME = 1; private static final int TYPE_INFO_METADATA_DATA_TYPE = 2; private static final int TYPE_INFO_METADATA_PRECISION = 3; private static final int TYPE_INFO_METADATA_LITERAL_PREFIX = 4; private static final int TYPE_INFO_METADATA_LITERAL_SUFFIX = 5; private static final int TYPE_INFO_METADATA_CREATE_PARMS = 6; private DatabaseAdapter databaseAdapter; private Bookmark bookmark; private List entityTypes; public Database(Bookmark bookmark) { this.bookmark = bookmark; this.databaseAdapter = bookmark.getAdapter(); } private static final String[] ALL_TABLE_TYPES = { Entity.TABLE_TYPE, Entity.VIEW_TYPE, Entity.SEQUENCE_TYPE }; private static final List STANDARD_TABLE_TYPES = Collections.synchronizedList(new ArrayList()); static { for (int i = 0, length = (ALL_TABLE_TYPES == null) ? 0 : ALL_TABLE_TYPES.length; i < length; i++) { STANDARD_TABLE_TYPES.add(ALL_TABLE_TYPES[i]); } } public synchronized String[] getEntityTypes() throws NotConnectedException, SQLException { if (this.entityTypes == null) { Collection collection = initializeEntityTypes(this.bookmark.getConnection()); this.entityTypes = Collections.synchronizedList(new ArrayList(collection)); } return (String[]) this.entityTypes.toArray(new String[this.entityTypes.size()]); } public String getUsername() throws NotConnectedException, SQLException { return getMetaData().getUserName(); } /** *

This method returns a set of entity types supported by the database * adapter. This list will always be limited to Tables, Views and * Sequences.

* *

Not all databases support all types. MySQL only supports * Tables. Informix supports Tables and Views. Oracle and DB2 support * Tables, Views and Sequences.

* * @param connection * @return a set of Strings, typically "TABLE", "VIEW", and "SEQUENCE" * @throws SQLException */ private Set initializeEntityTypes(Connection connection) throws SQLException { Set set = new HashSet(); 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_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 set; } public String getInformation() throws SQLException { try { DatabaseMetaData metaData = getMetaData(); return metaData == null ? null : metaData.getDatabaseProductName() + " " + metaData.getDatabaseProductVersion(); } catch (NotConnectedException e) { // TODO: think about this... return ""; } } /** * Get a list of entities (tables, views, sequences) for a particular * bookmark. This function is usually not redefined because it gives * an external interface. You will usually redefine the getShowTableQuery(), * getShowViewQuery(), etc. * * @param bookmark - * the bookmark that describes the database that is being accessed. * @param passwordFinder - * a utility class that knows how to obtain a password, if required * @param schema - * the schema from which to extract * @param type - * the type ("VIEW", "TABLE", etc.) of entities to extract or null * if all entity types should be extracted * @return * an array of entity objects representing the tables, views and sequences. * @throws SQLException */ public Entity[] getEntities(Bookmark bookmark, Schema schema, String type) throws SQLException, NotConnectedException { Connection connection = bookmark.getConnection(); Entity[] result = getEntities(bookmark, connection, schema, type); return (result == null) ? new Entity[0] : result; } protected Entity[] getEntities(Bookmark bookmark, Connection connection, Schema schema, String type) throws SQLException { List list = new ArrayList(); 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)); } return (Entity[]) list.toArray(new Entity[list.size()]); } protected List getEntitiesList(Bookmark bookmark, Connection connection, String type, Schema schema) throws SQLException { List list = new ArrayList(); // 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); 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 = (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); if (entity != null) { list.add(entity); } } } } 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.getInt(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()]); } /** * @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()); } else if (Entity.VIEW_TYPE.equals(type)) { return this.databaseAdapter.getShowViewQuery(schema.getName()); } else if (Entity.SEQUENCE_TYPE.equals(type)) { 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()]); } }