966329a7fea0229f7f2b1ba3a8668727e8e8d290
[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     
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 }