3 new apache icons; thanks to Anderson Forteleza
[phpeclipse.git] / 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 }