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
  52     public EntityImpl(Bookmark bookmark, String schema, String name, String type) {
  53         this.schema = schema;
  54         this.name = name;
  55         this.type = type;
  56         this.bookmark = bookmark;
  57     }
  58     public Bookmark getBookmark() {
  59         return this.bookmark;
  60     }
  61     public String getName() {
  62         return this.name;
  63     }
  64     public String getSchema() {
  65         return this.schema;
  66     }
  67     public String getType() {
  68         return this.type;
  69     }
  70     public String getQualifiedName() {
  71         return (this.schema == null || this.schema.length() == 0) ?
  72             this.name : this.schema + "." + this.name;
  73     }
  74     public Column getColumn(String columnName) throws NotConnectedException, SQLException  {
  75         Column column = null;
  76         Column[] columns = getColumns();
  77         for (int i = 0, length = (columns == null) ? 0 : columns.length;
  78             column == null && i < length;
  79             i++) {
  80             if (columnName != null && columnName.equals(columns[i].getName())) {
  81                 column = columns[i];
  82             }
  83         }
  84         return column;
  85     }
  86     public Column[] getColumns() throws NotConnectedException, SQLException {
  87
  88         Connection connection = this.bookmark.getConnection();
  89         try {
  90                 return getColumnsFromMetaData(connection);
  91         } catch (SQLException e) {
  92                 if (SQLStates.ODBC_DRIVER_NOT_CAPABLE.equals(e.getSQLState())
  93                                 && AdapterFactory.JDBC_ODBC_BRIDGE.equals(
  94                                                 getBookmark().getJDBCDriver().getType())) {
  95                         return getColumnsFromQuery(connection);
  96                 } else {
  97                         throw e;
  98                 }
  99         }
 100
 101     }
 102
 103     /**
 104          * @param connection
 105          * @return
 106          * @throws SQLException
 107          */
 108         private Column[] getColumnsFromMetaData(Connection connection) throws SQLException {
 109                 Map temp = new HashMap();
 110                 DatabaseMetaData metaData = connection.getMetaData();
 111                 ResultSet resultSet = metaData.getColumns(null, getSchema(), getName(), null);
 112                 try {
 113                     while (resultSet.next()) {
 114                         ColumnImpl column = new ColumnImpl(
 115                             this,
 116                             resultSet.getString(COLUMN_METADATA_COLUMN_NAME),
 117                             resultSet.getString(COLUMN_METATDATA_TYPE_NAME),
 118                             resultSet.getInt(COLUMN_METATDATA_DATA_TYPE),
 119                             resultSet.getInt(COLUMN_METADATA_COLUMN_SIZE),
 120                             resultSet.getInt(COLUMN_METADATA_DECIMAL_DIGITS),
 121                             "YES".equalsIgnoreCase(resultSet.getString(COLUMN_METADATA_IS_NULLABLE)),
 122                             resultSet.getInt(COLUMN_METADATA_ORDINAL_POSITION),
 123                                         getComments(
 124                                                         resultSet.getString(COLUMN_METADATA_REMARKS),
 125                                                         getQualifiedName(),
 126                                                         resultSet.getString(COLUMN_METADATA_COLUMN_NAME))
 127                             );
 128                         temp.put(column.getName(), column);
 129                     }
 130                 } finally {
 131                         resultSet.close();
 132                 }
 133
 134                 resultSet = metaData.getPrimaryKeys(null, getSchema(), getName());
 135                 try {
 136                     while (resultSet.next()) {
 137                         String name = resultSet.getString(PRIMARY_KEYS_METADATA_COLUMN_NAME);
 138                         short keySequence = resultSet.getShort(PRIMARY_KEYS_METADATA_KEY_SEQ);
 139                         ColumnImpl column = (ColumnImpl) temp.get(name);
 140                         if (column != null) {
 141                             column.setPrimaryKeyOrder(keySequence);
 142                         }
 143
 144                     }
 145
 146                     List columnList = Collections.synchronizedList(
 147                         new ArrayList(temp.values()));
 148                     Collections.sort(columnList);
 149                     return (Column[]) columnList.toArray(new Column[columnList.size()]);
 150
 151                 } finally {
 152                         resultSet.close();
 153                 }
 154         }
 155
 156         /**
 157          * Some databases, (in particular, MS Access under ODBC) aren't terribly friendly
 158          * about supporting metadata.  This method scrapes out the data the old-fashioned way.
 159          *
 160          * @param temp
 161          * @param connection
 162          * @throws SQLException
 163          */
 164         private Column[] getColumnsFromQuery(Connection connection) throws SQLException {
 165                 List temp = new ArrayList();
 166                 SQLMetaDataResults results =
 167                                 (SQLMetaDataResults) MultiSQLServer.getInstance().getMetaData(
 168                                         this, connection);
 169                 SQLMetaDataResults.Row[] rows = results.getRows();
 170                 for (int i = 0, length = results.getRowCount(); i < length; i++) {
 171                     ColumnImpl column = new ColumnImpl(
 172                             this,
 173                             (String) rows[i].get(1),
 174                             (String) rows[i].get(2),
 175                             ((Integer) rows[i].get(7)).intValue(),
 176                             ((Integer) rows[i].get(3)).intValue(),
 177                             ((Integer) rows[i].get(4)).intValue(),
 178                             "Nullable".equalsIgnoreCase((String) rows[i].get(5)),
 179                             i+1, "");
 180                         temp.add(column);
 181                 }
 182                 return (Column[]) temp.toArray(new Column[temp.size()]);
 183         }
 184         /**
 185      * Some JDBC drivers (Oracle for example) won't return the comments
 186      * We recheck with a custom query, if it's defined
 187          * @param iniComment The already got comment
 188          * @param tableName The fully qualified table name
 189          * @param columnName The column name
 190          */
 191         private String getComments( String iniComment, String tableName, String columnName) {
 192                 if (iniComment != null && iniComment.length() > 0)
 193                         return iniComment;
 194                 String comment = "";
 195                 try {
 196                         Connection con = this.bookmark.getConnection();
 197                         DatabaseAdapter adapter = this.bookmark.getAdapter();
 198                         Statement stmt = con.createStatement();
 199                         try {
 200                                 if (adapter != null && stmt != null
 201                                                 && adapter.getCommentsQuery(tableName, columnName) != null) {
 202
 203                                         stmt.execute(adapter.getCommentsQuery(tableName, columnName));
 204                                         ResultSet set = stmt.getResultSet();
 205                                         try {
 206                                                 if (set.next()) {
 207                                                         comment = set.getString(1);
 208                                                 }
 209                                         } finally {
 210                                                 set.close();
 211                                         }
 212                                 }
 213                         } finally {
 214                                 stmt.close();
 215                         }
 216                 } catch (NotConnectedException e) {
 217                 } catch (SQLException e) {
 218                 }
 219
 220                 return comment;
 221         }
 222
 223         public Index[] getIndexes() {
 224
 225         List indexList = new ArrayList();
 226         Map temp = new HashMap();
 227         try {
 228             Connection connection = this.bookmark.getConnection();
 229             DatabaseMetaData metaData = connection.getMetaData();
 230             ResultSet resultSet = metaData.getIndexInfo(null, getSchema(), getName(), false, false);
 231
 232             while (resultSet.next()) {
 233                 String indexName = resultSet.getString(INDEX_METADATA_INDEX_NAME);
 234                 IndexImpl index = (IndexImpl) temp.get(indexName);
 235                 if (index == null) {
 236                     index = new IndexImpl(this, indexName);
 237                     temp.put(indexName, index);
 238                 }
 239                 String columnName = resultSet.getString(INDEX_METADATA_COLUMN_NAME);
 240                 String ascending = resultSet.getString(INDEX_METADATA_ASC_OR_DESC);
 241                 index.addColumn(columnName, ascending == null
 242                     ? null : (ascending.toUpperCase().startsWith("A")
 243                         ? Boolean.TRUE : Boolean.FALSE));
 244             }
 245             resultSet.close();
 246             indexList.addAll(temp.values());
 247
 248         } catch (NotConnectedException e) {
 249         } catch (SQLException e) {
 250         }
 251         return (Index[]) indexList.toArray(new Index[indexList.size()]);
 252     }
 253
 254     public Boolean exists() {
 255         return this.exists;
 256     }
 257
 258
 259     /**
 260      * @see com.quantum.model.Entity#getQuotedTableName()
 261      */
 262     public String getQuotedTableName() {
 263         return getBookmark().getAdapter().filterTableName(getQualifiedName());
 264     }
 265
 266     public ForeignKey[] getExportedKeys() throws SQLException, NotConnectedException {
 267         return this.bookmark.getDatabase().getExportedKeys(getSchema(), getName());
 268     }
 269
 270     public ForeignKey[] getImportedKeys() throws SQLException, NotConnectedException {
 271         return this.bookmark.getDatabase().getImportedKeys(getSchema(), getName());
 272     }
 273     public ForeignKey[] getReferences() throws SQLException, NotConnectedException {
 274         ForeignKey[] importedKeys = getImportedKeys();
 275         ForeignKey[] exportedKeys = getExportedKeys();
 276
 277         List list = new ArrayList(); // if we could guarantee JDK 1.4, we'd use LinkedHashSet
 278         for (int i = 0, length = importedKeys == null ? 0 : importedKeys.length; i < length; i++) {
 279                         list.add(importedKeys[i]);
 280                 }
 281         for (int i = 0, length = exportedKeys == null ? 0 : exportedKeys.length; i < length; i++) {
 282                 if (!list.contains(exportedKeys[i])) {
 283                         list.add(exportedKeys[i]);
 284                 }
 285                 }
 286         return (ForeignKey[]) list.toArray(new ForeignKey[list.size()]);
 287     }
 288
 289     public int compareTo(Object object) {
 290                 Entity that = (Entity) object;
 291                 if (that.getQualifiedName() == null && this.getQualifiedName() != null) {
 292                         return 1;
 293                 } else if (this.getQualifiedName() == null && that.getQualifiedName() != null) {
 294                         return -1;
 295                 } else if (this.getQualifiedName() == null && that.getQualifiedName() == null) {
 296                         return 0;
 297                 } else {
 298                         return this.getQualifiedName().compareTo(that.getQualifiedName());
 299                 }
 300         }
 301 }