archive/net.sourceforge.phpeclipse.quantum.sql/src/com/quantum/model/EntityImpl.java

   1 package com.quantum.model;
   2
   3 import java.sql.Connection;
   4 import java.sql.DatabaseMetaData;
   5 import java.sql.ResultSet;
   6 import java.sql.SQLException;
   7 import java.sql.Statement;
   8 import java.util.ArrayList;
   9 import java.util.Collections;
  10 import java.util.HashMap;
  11 import java.util.List;
  12 import java.util.Map;
  13
  14 import com.quantum.adapters.AdapterFactory;
  15 import com.quantum.adapters.DatabaseAdapter;
  16 import com.quantum.sql.MultiSQLServer;
  17 import com.quantum.sql.SQLMetaDataResults;
  18 import com.quantum.util.sql.SQLStates;
  19
  20 /**
  21  * This class models a table or view.
  22  *
  23  * @author bcholmes
  24  */
  25 abstract class EntityImpl implements Entity {
  26
  27         // The JDBC-ODBC Driver is more happy if you look up metadata values
  28         // using the column number than if you use the column name
  29
  30         private static final int INDEX_METADATA_INDEX_NAME = 6;
  31         private static final int INDEX_METADATA_COLUMN_NAME = 9;
  32         private static final int INDEX_METADATA_ASC_OR_DESC = 10;
  33
  34         private static final int PRIMARY_KEYS_METADATA_COLUMN_NAME = 4;
  35         private static final int PRIMARY_KEYS_METADATA_KEY_SEQ = 5;
  36
  37         private static final int COLUMN_METADATA_COLUMN_NAME = 4;
  38         private static final int COLUMN_METATDATA_DATA_TYPE = 5;
  39         private static final int COLUMN_METATDATA_TYPE_NAME = 6;
  40         private static final int COLUMN_METADATA_COLUMN_SIZE = 7;
  41         private static final int COLUMN_METADATA_DECIMAL_DIGITS = 9;
  42         private static final int COLUMN_METADATA_REMARKS = 12;
  43         private static final int COLUMN_METADATA_ORDINAL_POSITION = 17;
  44         private static final int COLUMN_METADATA_IS_NULLABLE = 18;
  45
  46         private String schema;
  47     private String name;
  48     private String type;
  49     private Bookmark bookmark;
  50     private Boolean exists = Boolean.TRUE;
  51     private boolean isSynonym = false;  // Tells if its a synonym or not
  52     // Columns will be cached in this array, as sometimes asking for them to the JDBC driver is very costy
  53     // (for example in Oracle when synonyms and remarks are asked for )
  54     private Column[] columns = null;
  55
  56     public EntityImpl(Bookmark bookmark, String schema, String name, String type, boolean isSynonym) {
  57         this.schema = schema;
  58         this.name = name;
  59         this.type = type;
  60         this.bookmark = bookmark;
  61         this.isSynonym = isSynonym;
  62     }
  63     public Bookmark getBookmark() {
  64         return this.bookmark;
  65     }
  66     public String getName() {
  67         return this.name;
  68     }
  69     public String getSchema() {
  70         return this.schema;
  71     }
  72     public String getType() {
  73         return this.type;
  74     }
  75     public String getQualifiedName() {
  76         return (this.schema == null || this.schema.length() == 0) ?
  77             this.name : this.schema + "." + this.name;
  78     }
  79     public Column getColumn(String columnName) throws NotConnectedException, SQLException  {
  80         Column column = null;
  81         if (this.columns == null) this.columns = getColumns();
  82         for (int i = 0, length = (this.columns == null) ? 0 : this.columns.length;
  83             column == null && i < length;
  84             i++) {
  85             if (columnName != null && columnName.equals(this.columns[i].getName())) {
  86                 column = this.columns[i];
  87             }
  88         }
  89         return column;
  90     }
  91     public Column[] getColumns() throws NotConnectedException, SQLException {
  92         if (this.columns != null) return this.columns;
  93         Connection connection = this.bookmark.getConnection();
  94         try {
  95                 this.columns = getColumnsFromMetaData(connection);
  96                 return this.columns;
  97         } catch (SQLException e) {
  98                 if (SQLStates.ODBC_DRIVER_NOT_CAPABLE.equals(e.getSQLState())
  99                                 && AdapterFactory.JDBC_ODBC_BRIDGE.equals(
 100                                                 getBookmark().getJDBCDriver().getType())) {
 101                         this.columns = getColumnsFromQuery(connection);
 102                         return this.columns;
 103                 } else {
 104                         throw e;
 105                 }
 106         }
 107
 108     }
 109
 110     /**
 111          * @param connection
 112          * @return
 113          * @throws SQLException
 114          */
 115         private Column[] getColumnsFromMetaData(Connection connection) throws SQLException {
 116                 Map temp = new HashMap();
 117                 DatabaseMetaData metaData = connection.getMetaData();
 118                 ResultSet resultSet = metaData.getColumns(null, getSchema(), getName(), null);
 119                 try {
 120                     while (resultSet.next()) {
 121                         ColumnImpl column = new ColumnImpl(
 122                             this,
 123                             resultSet.getString(COLUMN_METADATA_COLUMN_NAME),
 124                             resultSet.getString(COLUMN_METATDATA_TYPE_NAME),
 125                             resultSet.getInt(COLUMN_METATDATA_DATA_TYPE),
 126                             resultSet.getInt(COLUMN_METADATA_COLUMN_SIZE),
 127                             resultSet.getInt(COLUMN_METADATA_DECIMAL_DIGITS),
 128                             "YES".equalsIgnoreCase(resultSet.getString(COLUMN_METADATA_IS_NULLABLE)),
 129                             resultSet.getInt(COLUMN_METADATA_ORDINAL_POSITION),
 130                                         resultSet.getString(COLUMN_METADATA_REMARKS)
 131                             );
 132                         temp.put(column.getName(), column);
 133                     }
 134                 } finally {
 135                         resultSet.close();
 136                 }
 137
 138                 resultSet = metaData.getPrimaryKeys(null, getSchema(), getName());
 139                 try {
 140                     while (resultSet.next()) {
 141                         String name = resultSet.getString(PRIMARY_KEYS_METADATA_COLUMN_NAME);
 142                         short keySequence = resultSet.getShort(PRIMARY_KEYS_METADATA_KEY_SEQ);
 143                         ColumnImpl column = (ColumnImpl) temp.get(name);
 144                         if (column != null) {
 145                             column.setPrimaryKeyOrder(keySequence);
 146                         }
 147
 148                     }
 149
 150                     List columnList = Collections.synchronizedList(
 151                         new ArrayList(temp.values()));
 152                     Collections.sort(columnList);
 153                     return (Column[]) columnList.toArray(new Column[columnList.size()]);
 154
 155                 } finally {
 156                         resultSet.close();
 157                 }
 158         }
 159
 160         /**
 161          * Some databases, (in particular, MS Access under ODBC) aren't terribly friendly
 162          * about supporting metadata.  This method scrapes out the data the old-fashioned way.
 163          *
 164          * @param temp
 165          * @param connection
 166          * @throws SQLException
 167          */
 168         private Column[] getColumnsFromQuery(Connection connection) throws SQLException {
 169                 List temp = new ArrayList();
 170                 SQLMetaDataResults results =
 171                                 (SQLMetaDataResults) MultiSQLServer.getInstance().getMetaData(
 172                                         this, connection);
 173                 SQLMetaDataResults.Row[] rows = results.getRows();
 174                 for (int i = 0, length = results.getRowCount(); i < length; i++) {
 175                     ColumnImpl column = new ColumnImpl(
 176                             this,
 177                             (String) rows[i].get(1),
 178                             (String) rows[i].get(2),
 179                             ((Integer) rows[i].get(7)).intValue(),
 180                             ((Long) rows[i].get(3)).longValue(),
 181                             ((Integer) rows[i].get(4)).intValue(),
 182                             "Nullable".equalsIgnoreCase((String) rows[i].get(5)),
 183                             i+1, "");
 184                         temp.add(column);
 185                 }
 186                 return (Column[]) temp.toArray(new Column[temp.size()]);
 187         }
 188         /**
 189      * Some JDBC drivers (Oracle for example) won't return the comments
 190      * We recheck with a custom query, if it's defined
 191          * @param iniComment The already got comment
 192          * @param tableName The fully qualified table name
 193          * @param columnName The column name
 194          *
 195          *  NO LONGER USED, there is a parameter (remarksReporting) in the JDBC connection that makes ORACLE return the
 196          * remarks for tables and columns. Is slower, so an option will be used.
 197          *
 198          * The function is kept in case other JDBC drivers have the same problem
 199          */
 200         private String getComments( String iniComment, String tableName, String columnName) {
 201                 if (iniComment != null && iniComment.length() > 0)
 202                         return iniComment;
 203                 String comment = "";
 204                 try {
 205                         Connection con = this.bookmark.getConnection();
 206                         DatabaseAdapter adapter = this.bookmark.getAdapter();
 207                         Statement stmt = con.createStatement();
 208                         try {
 209                                 if (adapter != null && stmt != null
 210                                                 && adapter.getCommentsQuery(tableName, columnName) != null) {
 211
 212                                         stmt.execute(adapter.getCommentsQuery(tableName, columnName));
 213                                         ResultSet set = stmt.getResultSet();
 214                                         try {
 215                                                 if (set.next()) {
 216                                                         comment = set.getString(1);
 217                                                 }
 218                                         } finally {
 219                                                 set.close();
 220                                         }
 221                                 }
 222                         } finally {
 223                                 stmt.close();
 224                         }
 225                 } catch (NotConnectedException e) {
 226                 } catch (SQLException e) {
 227                 }
 228
 229                 return comment;
 230         }
 231
 232         public Index[] getIndexes() {
 233
 234         List indexList = new ArrayList();
 235         Map temp = new HashMap();
 236         try {
 237             Connection connection = this.bookmark.getConnection();
 238             DatabaseMetaData metaData = connection.getMetaData();
 239             ResultSet resultSet = metaData.getIndexInfo(null, getSchema(), getName(), false, false);
 240
 241             while (resultSet.next()) {
 242                 String indexName = resultSet.getString(INDEX_METADATA_INDEX_NAME);
 243                 IndexImpl index = (IndexImpl) temp.get(indexName);
 244                 if (index == null) {
 245                     index = new IndexImpl(this, indexName);
 246                     temp.put(indexName, index);
 247                 }
 248                 String columnName = resultSet.getString(INDEX_METADATA_COLUMN_NAME);
 249                 String ascending = resultSet.getString(INDEX_METADATA_ASC_OR_DESC);
 250                 index.addColumn(columnName, ascending == null
 251                     ? null : (ascending.toUpperCase().startsWith("A")
 252                         ? Boolean.TRUE : Boolean.FALSE));
 253             }
 254             resultSet.close();
 255             indexList.addAll(temp.values());
 256
 257         } catch (NotConnectedException e) {
 258         } catch (SQLException e) {
 259         }
 260         return (Index[]) indexList.toArray(new Index[indexList.size()]);
 261     }
 262
 263     public Boolean exists() {
 264         return this.exists;
 265     }
 266
 267
 268     /**
 269      * @see com.quantum.model.Entity#getQuotedTableName()
 270      */
 271     public String getQuotedTableName() {
 272         return getBookmark().getAdapter().filterTableName(getQualifiedName());
 273     }
 274
 275     public ForeignKey[] getExportedKeys() throws SQLException, NotConnectedException {
 276         return this.bookmark.getDatabase().getExportedKeys(getSchema(), getName());
 277     }
 278
 279     public ForeignKey[] getImportedKeys() throws SQLException, NotConnectedException {
 280         return this.bookmark.getDatabase().getImportedKeys(getSchema(), getName());
 281     }
 282     public ForeignKey[] getReferences() throws SQLException, NotConnectedException {
 283         ForeignKey[] importedKeys = getImportedKeys();
 284         ForeignKey[] exportedKeys = getExportedKeys();
 285
 286         List list = new ArrayList(); // if we could guarantee JDK 1.4, we'd use LinkedHashSet
 287         for (int i = 0, length = importedKeys == null ? 0 : importedKeys.length; i < length; i++) {
 288                         list.add(importedKeys[i]);
 289                 }
 290         for (int i = 0, length = exportedKeys == null ? 0 : exportedKeys.length; i < length; i++) {
 291                 if (!list.contains(exportedKeys[i])) {
 292                         list.add(exportedKeys[i]);
 293                 }
 294                 }
 295         return (ForeignKey[]) list.toArray(new ForeignKey[list.size()]);
 296     }
 297
 298     public int compareTo(Object object) {
 299                 Entity that = (Entity) object;
 300                 if (that.getQualifiedName() == null && this.getQualifiedName() != null) {
 301                         return 1;
 302                 } else if (this.getQualifiedName() == null && that.getQualifiedName() != null) {
 303                         return -1;
 304                 } else if (this.getQualifiedName() == null && that.getQualifiedName() == null) {
 305                         return 0;
 306                 } else {
 307                         return this.getQualifiedName().compareTo(that.getQualifiedName());
 308                 }
 309         }
 310
 311     /**
 312          * @return Returns the isSynonym.
 313          */
 314         public boolean isSynonym() {
 315                 return isSynonym;
 316         }
 317
 318 }