*** empty log message ***
[phpeclipse.git] / archive / net.sourceforge.phpeclipse.quantum.sql / src / com / quantum / model / EntityImpl.java
index a960fbb..41ffad2 100644 (file)
@@ -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