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();
}
/**
* <p>This method returns a set of entity types supported by the database
* adapter. This list will always be limited to Tables, Views and
* Sequences.</p>
*
* <p>Not all databases support all types. MySQL only supports
* Tables. Informix supports Tables and Views. Oracle and DB2 support
* Tables, Views and Sequences.</p>
*
* @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));
// 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()]);
}
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, 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 = (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, false);
if (entity != null) {
list.add(entity);
}
}
}
}
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, schemaName, tableName, type, true);
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.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()]);
}
/**
* @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()]);
}
}