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 8973ffd..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; @@ -11,7 +11,11 @@ import java.util.HashMap; import java.util.List; 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. @@ -19,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; @@ -49,68 +78,124 @@ abstract class EntityImpl implements Entity { } 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() throws NotConnectedException, SQLException { - - Map temp = new HashMap(); + if (this.columns != null) return this.columns; Connection connection = this.bookmark.getConnection(); - DatabaseMetaData metaData = connection.getMetaData(); - ResultSet resultSet = metaData.getColumns(null, getSchema(), getName(), null); - try { - 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"),getQualifiedName(), resultSet.getString("COLUMN_NAME")) - ); - temp.put(column.getName(), column); - } - } finally { - resultSet.close(); - } - - resultSet = metaData.getPrimaryKeys(null, getSchema(), getName()); try { - 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); - return (Column[]) columnList.toArray(new Column[columnList.size()]); - } finally { - resultSet.close(); + 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; + } } + } /** + * @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) @@ -118,14 +203,24 @@ abstract class EntityImpl implements Entity { String comment = ""; try { Connection con = this.bookmark.getConnection(); - Statement stmt = con.createStatement(); DatabaseAdapter adapter = this.bookmark.getAdapter(); - 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); + Statement stmt = con.createStatement(); + 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) { @@ -133,6 +228,7 @@ abstract class EntityImpl implements Entity { return comment; } + public Index[] getIndexes() { List indexList = new ArrayList(); @@ -143,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)); @@ -211,4 +307,12 @@ abstract class EntityImpl implements Entity { return this.getQualifiedName().compareTo(that.getQualifiedName()); } } + + /** + * @return Returns the isSynonym. + */ + public boolean isSynonym() { + return isSynonym; + } + } \ No newline at end of file