X-Git-Url: http://git.phpeclipse.com diff --git a/archive/net.sourceforge.phpeclipse.quantum.sql/src/com/quantum/model/EntityImpl.java b/archive/net.sourceforge.phpeclipse.quantum.sql/src/com/quantum/model/EntityImpl.java index a960fbb..41ffad2 100644 --- a/archive/net.sourceforge.phpeclipse.quantum.sql/src/com/quantum/model/EntityImpl.java +++ b/archive/net.sourceforge.phpeclipse.quantum.sql/src/com/quantum/model/EntityImpl.java @@ -1,4 +1,4 @@ - package com.quantum.model; +package com.quantum.model; import java.sql.Connection; import java.sql.DatabaseMetaData; @@ -13,6 +13,9 @@ import java.util.Map; import com.quantum.adapters.AdapterFactory; import com.quantum.adapters.DatabaseAdapter; +import com.quantum.sql.MultiSQLServer; +import com.quantum.sql.SQLMetaDataResults; +import com.quantum.util.sql.SQLStates; /** * This class models a table or view. @@ -20,17 +23,42 @@ import com.quantum.adapters.DatabaseAdapter; * @author bcholmes */ abstract class EntityImpl implements Entity { - private String schema; + + // The JDBC-ODBC Driver is more happy if you look up metadata values + // using the column number than if you use the column name + + private static final int INDEX_METADATA_INDEX_NAME = 6; + private static final int INDEX_METADATA_COLUMN_NAME = 9; + private static final int INDEX_METADATA_ASC_OR_DESC = 10; + + private static final int PRIMARY_KEYS_METADATA_COLUMN_NAME = 4; + private static final int PRIMARY_KEYS_METADATA_KEY_SEQ = 5; + + private static final int COLUMN_METADATA_COLUMN_NAME = 4; + private static final int COLUMN_METATDATA_DATA_TYPE = 5; + private static final int COLUMN_METATDATA_TYPE_NAME = 6; + private static final int COLUMN_METADATA_COLUMN_SIZE = 7; + private static final int COLUMN_METADATA_DECIMAL_DIGITS = 9; + private static final int COLUMN_METADATA_REMARKS = 12; + private static final int COLUMN_METADATA_ORDINAL_POSITION = 17; + private static final int COLUMN_METADATA_IS_NULLABLE = 18; + + private String schema; private String name; private String type; private Bookmark bookmark; private Boolean exists = Boolean.TRUE; + private boolean isSynonym = false; // Tells if its a synonym or not + // Columns will be cached in this array, as sometimes asking for them to the JDBC driver is very costy + // (for example in Oracle when synonyms and remarks are asked for ) + private Column[] columns = null; - public EntityImpl(Bookmark bookmark, String schema, String name, String type) { + public EntityImpl(Bookmark bookmark, String schema, String name, String type, boolean isSynonym) { this.schema = schema; this.name = name; this.type = type; this.bookmark = bookmark; + this.isSynonym = isSynonym; } public Bookmark getBookmark() { return this.bookmark; @@ -44,76 +72,130 @@ abstract class EntityImpl implements Entity { public String getType() { return this.type; } - public String getCondQualifiedName() { + public String getQualifiedName() { return (this.schema == null || this.schema.length() == 0) ? this.name : this.schema + "." + this.name; } - public Column getColumn(String columnName) { + public Column getColumn(String columnName) throws NotConnectedException, SQLException { Column column = null; - Column[] columns = getColumns(); - for (int i = 0, length = (columns == null) ? 0 : columns.length; + if (this.columns == null) this.columns = getColumns(); + for (int i = 0, length = (this.columns == null) ? 0 : this.columns.length; column == null && i < length; i++) { - if (columnName != null && columnName.equals(columns[i].getName())) { - column = columns[i]; + if (columnName != null && columnName.equals(this.columns[i].getName())) { + column = this.columns[i]; } } return column; } - public Column[] getColumns() { - - Column[] columns = new Column[0]; + public Column[] getColumns() throws NotConnectedException, SQLException { + if (this.columns != null) return this.columns; + Connection connection = this.bookmark.getConnection(); try { - // TODO: Some DBs (like DB2) don't support metadata - Map temp = new HashMap(); - Connection connection = this.bookmark.getConnection(); - DatabaseMetaData metaData = connection.getMetaData(); - ResultSet resultSet = metaData.getColumns(null, getSchema(), getName(), null); - - while (resultSet.next()) { - ColumnImpl column = new ColumnImpl( - this, - resultSet.getString("COLUMN_NAME"), - resultSet.getString("TYPE_NAME"), - resultSet.getInt("DATA_TYPE"), - resultSet.getInt("COLUMN_SIZE"), - resultSet.getInt("DECIMAL_DIGITS"), - "YES".equalsIgnoreCase(resultSet.getString("IS_NULLABLE")), - resultSet.getInt("ORDINAL_POSITION"), - getComments(resultSet.getString("REMARKS"),getCondQualifiedName(), resultSet.getString("COLUMN_NAME")) - ); - temp.put(column.getName(), column); - } - resultSet.close(); - - resultSet = metaData.getPrimaryKeys(null, getSchema(), getName()); - while (resultSet.next()) { - String name = resultSet.getString("COLUMN_NAME"); - short keySequence = resultSet.getShort("KEY_SEQ"); - ColumnImpl column = (ColumnImpl) temp.get(name); - if (column != null) { - column.setPrimaryKeyOrder(keySequence); - } - } - resultSet.close(); - - List columnList = Collections.synchronizedList( - new ArrayList(temp.values())); - Collections.sort(columnList); - columns = (Column[]) columnList.toArray(new Column[columnList.size()]); - - } catch (NotConnectedException e) { + this.columns = getColumnsFromMetaData(connection); + return this.columns; } catch (SQLException e) { + if (SQLStates.ODBC_DRIVER_NOT_CAPABLE.equals(e.getSQLState()) + && AdapterFactory.JDBC_ODBC_BRIDGE.equals( + getBookmark().getJDBCDriver().getType())) { + this.columns = getColumnsFromQuery(connection); + return this.columns; + } else { + throw e; + } } - return columns; + } /** + * @param connection + * @return + * @throws SQLException + */ + private Column[] getColumnsFromMetaData(Connection connection) throws SQLException { + Map temp = new HashMap(); + DatabaseMetaData metaData = connection.getMetaData(); + ResultSet resultSet = metaData.getColumns(null, getSchema(), getName(), null); + try { + while (resultSet.next()) { + ColumnImpl column = new ColumnImpl( + this, + resultSet.getString(COLUMN_METADATA_COLUMN_NAME), + resultSet.getString(COLUMN_METATDATA_TYPE_NAME), + resultSet.getInt(COLUMN_METATDATA_DATA_TYPE), + resultSet.getInt(COLUMN_METADATA_COLUMN_SIZE), + resultSet.getInt(COLUMN_METADATA_DECIMAL_DIGITS), + "YES".equalsIgnoreCase(resultSet.getString(COLUMN_METADATA_IS_NULLABLE)), + resultSet.getInt(COLUMN_METADATA_ORDINAL_POSITION), + resultSet.getString(COLUMN_METADATA_REMARKS) + ); + temp.put(column.getName(), column); + } + } finally { + resultSet.close(); + } + + resultSet = metaData.getPrimaryKeys(null, getSchema(), getName()); + try { + while (resultSet.next()) { + String name = resultSet.getString(PRIMARY_KEYS_METADATA_COLUMN_NAME); + short keySequence = resultSet.getShort(PRIMARY_KEYS_METADATA_KEY_SEQ); + ColumnImpl column = (ColumnImpl) temp.get(name); + if (column != null) { + column.setPrimaryKeyOrder(keySequence); + } + + } + + List columnList = Collections.synchronizedList( + new ArrayList(temp.values())); + Collections.sort(columnList); + return (Column[]) columnList.toArray(new Column[columnList.size()]); + + } finally { + resultSet.close(); + } + } + + /** + * Some databases, (in particular, MS Access under ODBC) aren't terribly friendly + * about supporting metadata. This method scrapes out the data the old-fashioned way. + * + * @param temp + * @param connection + * @throws SQLException + */ + private Column[] getColumnsFromQuery(Connection connection) throws SQLException { + List temp = new ArrayList(); + SQLMetaDataResults results = + (SQLMetaDataResults) MultiSQLServer.getInstance().getMetaData( + this, connection); + SQLMetaDataResults.Row[] rows = results.getRows(); + for (int i = 0, length = results.getRowCount(); i < length; i++) { + ColumnImpl column = new ColumnImpl( + this, + (String) rows[i].get(1), + (String) rows[i].get(2), + ((Integer) rows[i].get(7)).intValue(), + ((Long) rows[i].get(3)).longValue(), + ((Integer) rows[i].get(4)).intValue(), + "Nullable".equalsIgnoreCase((String) rows[i].get(5)), + i+1, ""); + temp.add(column); + } + return (Column[]) temp.toArray(new Column[temp.size()]); + } + /** * Some JDBC drivers (Oracle for example) won't return the comments * We recheck with a custom query, if it's defined * @param iniComment The already got comment * @param tableName The fully qualified table name * @param columnName The column name + * + * NO LONGER USED, there is a parameter (remarksReporting) in the JDBC connection that makes ORACLE return the + * remarks for tables and columns. Is slower, so an option will be used. + * + * The function is kept in case other JDBC drivers have the same problem */ private String getComments( String iniComment, String tableName, String columnName) { if (iniComment != null && iniComment.length() > 0) @@ -121,14 +203,24 @@ abstract class EntityImpl implements Entity { String comment = ""; try { Connection con = this.bookmark.getConnection(); + DatabaseAdapter adapter = this.bookmark.getAdapter(); Statement stmt = con.createStatement(); - DatabaseAdapter adapter = AdapterFactory.getInstance().getAdapter(this.bookmark.getType()); - if (adapter != null && stmt != null && adapter.getCommentsQuery(tableName, columnName) != null) { - - stmt.execute(adapter.getCommentsQuery(tableName, columnName)); - ResultSet set = stmt.getResultSet(); - if (set.next()) - comment = set.getString(1); + try { + if (adapter != null && stmt != null + && adapter.getCommentsQuery(tableName, columnName) != null) { + + stmt.execute(adapter.getCommentsQuery(tableName, columnName)); + ResultSet set = stmt.getResultSet(); + try { + if (set.next()) { + comment = set.getString(1); + } + } finally { + set.close(); + } + } + } finally { + stmt.close(); } } catch (NotConnectedException e) { } catch (SQLException e) { @@ -136,6 +228,7 @@ abstract class EntityImpl implements Entity { return comment; } + public Index[] getIndexes() { List indexList = new ArrayList(); @@ -146,14 +239,14 @@ abstract class EntityImpl implements Entity { ResultSet resultSet = metaData.getIndexInfo(null, getSchema(), getName(), false, false); while (resultSet.next()) { - String indexName = resultSet.getString("INDEX_NAME"); + String indexName = resultSet.getString(INDEX_METADATA_INDEX_NAME); IndexImpl index = (IndexImpl) temp.get(indexName); if (index == null) { index = new IndexImpl(this, indexName); temp.put(indexName, index); } - String columnName = resultSet.getString("COLUMN_NAME"); - String ascending = resultSet.getString("ASC_OR_DESC"); + String columnName = resultSet.getString(INDEX_METADATA_COLUMN_NAME); + String ascending = resultSet.getString(INDEX_METADATA_ASC_OR_DESC); index.addColumn(columnName, ascending == null ? null : (ascending.toUpperCase().startsWith("A") ? Boolean.TRUE : Boolean.FALSE)); @@ -176,7 +269,50 @@ abstract class EntityImpl implements Entity { * @see com.quantum.model.Entity#getQuotedTableName() */ public String getQuotedTableName() { - return getBookmark().getAdapter().filterTableName(getCondQualifiedName()); + return getBookmark().getAdapter().filterTableName(getQualifiedName()); + } + + public ForeignKey[] getExportedKeys() throws SQLException, NotConnectedException { + return this.bookmark.getDatabase().getExportedKeys(getSchema(), getName()); } + public ForeignKey[] getImportedKeys() throws SQLException, NotConnectedException { + return this.bookmark.getDatabase().getImportedKeys(getSchema(), getName()); + } + public ForeignKey[] getReferences() throws SQLException, NotConnectedException { + ForeignKey[] importedKeys = getImportedKeys(); + ForeignKey[] exportedKeys = getExportedKeys(); + + List list = new ArrayList(); // if we could guarantee JDK 1.4, we'd use LinkedHashSet + for (int i = 0, length = importedKeys == null ? 0 : importedKeys.length; i < length; i++) { + list.add(importedKeys[i]); + } + for (int i = 0, length = exportedKeys == null ? 0 : exportedKeys.length; i < length; i++) { + if (!list.contains(exportedKeys[i])) { + list.add(exportedKeys[i]); + } + } + return (ForeignKey[]) list.toArray(new ForeignKey[list.size()]); + } + + public int compareTo(Object object) { + Entity that = (Entity) object; + if (that.getQualifiedName() == null && this.getQualifiedName() != null) { + return 1; + } else if (this.getQualifiedName() == null && that.getQualifiedName() != null) { + return -1; + } else if (this.getQualifiedName() == null && that.getQualifiedName() == null) { + return 0; + } else { + return this.getQualifiedName().compareTo(that.getQualifiedName()); + } + } + + /** + * @return Returns the isSynonym. + */ + public boolean isSynonym() { + return isSynonym; + } + } \ No newline at end of file