improved codetemplate wizards; new html tag wizards
[phpeclipse.git] / archive / net.sourceforge.phpeclipse.quantum.sql / src / com / quantum / model / Database.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.util.ArrayList;
8 import java.util.Collection;
9 import java.util.Collections;
10 import java.util.HashSet;
11 import java.util.List;
12 import java.util.Set;
13
14 import com.quantum.adapters.DatabaseAdapter;
15 import com.quantum.sql.MultiSQLServer;
16 import com.quantum.sql.SQLResultSetResults;
17
18 /**
19  * @author BC
20  */
21 public class Database {
22     
23         private static final int TABLE_METADATA_TABLE_SCHEM = 2;
24         private static final int TABLE_METADATA_TABLE_NAME = 3;
25         
26         private static final int TABLE_TYPE_METADATA_TABLE_TYPE = 1;
27
28         private static final int FOREIGN_KEY_METADATA_PKTABLE_SCHEM = 2;
29         private static final int FOREIGN_KEY_METADATA_PKTABLE_NAME = 3;
30         private static final int FOREIGN_KEY_METADATA_PKCOLUMN_NAME = 4;
31         private static final int FOREIGN_KEY_METADATA_FKTABLE_SCHEM = 6;
32         private static final int FOREIGN_KEY_METADATA_FKTABLE_NAME = 7;
33         private static final int FOREIGN_KEY_METADATA_FKCOLUMN_NAME = 8;
34         private static final int FOREIGN_KEY_METADATA_KEY_SEQ = 9;
35         private static final int FOREIGN_KEY_METADATA_DELETE_RULE = 11;
36         private static final int FOREIGN_KEY_METADATA_FK_NAME = 12;
37         
38         private static final int TYPE_INFO_METADATA_TYPE_NAME = 1;
39         private static final int TYPE_INFO_METADATA_DATA_TYPE = 2;
40         private static final int TYPE_INFO_METADATA_PRECISION = 3;
41         private static final int TYPE_INFO_METADATA_LITERAL_PREFIX = 4;
42         private static final int TYPE_INFO_METADATA_LITERAL_SUFFIX = 5;
43         private static final int TYPE_INFO_METADATA_CREATE_PARMS = 6;
44         
45         private DatabaseAdapter databaseAdapter;
46     private Bookmark bookmark;
47     
48     private List entityTypes;
49     
50     public Database(Bookmark bookmark) {
51         this.bookmark = bookmark;
52         this.databaseAdapter = bookmark.getAdapter();
53     }
54
55     private static final String[] ALL_TABLE_TYPES = { 
56         Entity.TABLE_TYPE, 
57         Entity.VIEW_TYPE, 
58         Entity.SEQUENCE_TYPE };
59         
60     private static final List STANDARD_TABLE_TYPES = 
61         Collections.synchronizedList(new ArrayList());
62         
63     static {
64         for (int i = 0, length = (ALL_TABLE_TYPES == null) ? 0 : ALL_TABLE_TYPES.length;
65             i < length;
66             i++) {
67             STANDARD_TABLE_TYPES.add(ALL_TABLE_TYPES[i]);
68         }
69     }
70     
71     public synchronized String[] getEntityTypes() 
72         throws NotConnectedException, SQLException {
73         if (this.entityTypes == null) {
74                 Collection collection = initializeEntityTypes(this.bookmark.getConnection());
75             this.entityTypes = Collections.synchronizedList(new ArrayList(collection));
76         }
77         return (String[]) this.entityTypes.toArray(new String[this.entityTypes.size()]);
78     }
79     
80     public String getUsername() throws NotConnectedException, SQLException {
81         return getMetaData().getUserName();
82     }
83
84
85     /**
86      * <p>This method returns a set of entity types supported by the database
87      * adapter.  This list will always be limited to Tables, Views and 
88      * Sequences.</p>
89      * 
90      * <p>Not all databases support all types.  MySQL only supports 
91      * Tables.  Informix supports Tables and Views.  Oracle and DB2 support
92      * Tables, Views and Sequences.</p>
93      * 
94      * @param connection
95      * @return a set of Strings, typically "TABLE", "VIEW", and "SEQUENCE"
96      * @throws SQLException
97      */
98     private Set initializeEntityTypes(Connection connection) throws SQLException {
99         
100         Set set = new HashSet();
101         if (this.databaseAdapter.getShowTableQuery(this.bookmark.getUsername()) != null) {
102             set.add(Entity.TABLE_TYPE);
103         } else if (this.databaseAdapter.getShowViewQuery(this.bookmark.getUsername()) != null) {
104             set.add(Entity.VIEW_TYPE);
105         } else if (this.databaseAdapter.getShowSequenceQuery(this.bookmark.getUsername()) != null) {
106             set.add(Entity.SEQUENCE_TYPE);
107         }
108         
109         DatabaseMetaData metaData = connection.getMetaData();
110         ResultSet resultSet = metaData.getTableTypes();
111         while (resultSet.next()) {
112             String type = resultSet.getString(TABLE_TYPE_METADATA_TABLE_TYPE);
113             if (type != null) {
114                 // Informix, in particular, pads this with extra spaces
115                 type = type.trim();
116             }
117             if (STANDARD_TABLE_TYPES.contains(type)) {
118                 set.add(type);
119             }
120         }
121         return set;
122     }
123
124     public String getInformation() throws SQLException {
125         try {
126             DatabaseMetaData metaData = getMetaData();
127         
128             return metaData == null ? null : metaData.getDatabaseProductName() + " " 
129                     + metaData.getDatabaseProductVersion();
130         } catch (NotConnectedException e) {
131             // TODO: think about this...
132             return "";
133         }
134     }
135     
136     /**
137      * Get a list of entities (tables, views, sequences) for a particular
138      * bookmark. This function is usually not redefined because it gives
139      * an external interface. You will usually redefine the getShowTableQuery(), 
140      * getShowViewQuery(), etc.
141      * 
142      * @param bookmark -
143      *     the bookmark that describes the database that is being accessed.
144      * @param passwordFinder -
145      *     a utility class that knows how to obtain a password, if required
146      * @param schema -
147      *     the schema from which to extract
148      * @param type -
149      *     the type ("VIEW", "TABLE", etc.) of entities to extract or null
150      *     if all entity types should be extracted
151      * @return 
152      *     an array of entity objects representing the tables, views and sequences.
153      * @throws SQLException
154      */
155     public Entity[] getEntities(Bookmark bookmark, Schema schema, String type) 
156         throws SQLException, NotConnectedException {
157         Connection connection = bookmark.getConnection();
158         Entity[] result = getEntities(bookmark, connection, schema, type);
159         return (result == null) ? new Entity[0] : result;
160     }
161     
162     protected Entity[] getEntities(Bookmark bookmark, Connection connection, Schema schema, String type) 
163         throws SQLException {
164         
165         List list = new ArrayList();
166         String[] types = (type == null) ? ALL_TABLE_TYPES : new String[] { type };
167             
168         for (int i = 0; i < types.length; i++) {
169             list.addAll(getEntitiesList(bookmark, connection, types[i], schema));
170         }
171
172         return (Entity[]) list.toArray(new Entity[list.size()]);
173     }
174
175     protected List getEntitiesList(Bookmark bookmark, Connection connection, String type, Schema schema)
176         throws SQLException {
177
178         List list = new ArrayList();
179         //      We try first the JDBC driver
180         DatabaseMetaData metaData = connection.getMetaData();
181         // getTables needs a null schema to get all the schemas. So we don't pass a "" schema, but a null one
182                 ResultSet set = null;
183                 if (metaData.supportsSchemasInTableDefinitions()) {
184                 set = metaData.getTables(null, (schema != null) ? schema.getName() : null, "%", new String[] { type });
185                 } else {
186                         set = metaData.getTables(null, null, "%", new String[] { type });
187                 }
188             
189         while (set.next()) {
190             String tempSchema = set.getString(TABLE_METADATA_TABLE_SCHEM);
191             tempSchema = (tempSchema == null) ? "" : tempSchema.trim();
192             String tableName = set.getString(TABLE_METADATA_TABLE_NAME);
193             tableName = (tableName == null) ? "" : tableName.trim();
194
195             if (tableName != null && tableName.length() > 0) {
196                 Entity entity = EntityFactory.getInstance().create(bookmark, tempSchema, tableName, type);
197                 if (entity != null) {
198                     list.add(entity);
199                 }
200             }
201         }
202         set.close();
203         // If we have some results, then the JDBC driver is working, 
204         // so we return the results and quit
205         if (list.size() > 0) 
206                 return list;
207         
208         
209         // If no results, we check also the sql query to get the list of entities
210         SQLResultSetResults results = null;
211         // Get the proper sql query to the appropiate type of entity
212         String sql = getSQL(bookmark, type, schema);
213         // If nothing returned, too bad, it seems there is no sql query for that database and entity type
214         if (sql != null) {
215             results = (SQLResultSetResults) MultiSQLServer.getInstance().execute(
216                         bookmark, connection, sql, Integer.MAX_VALUE);
217             for (int i = 1, size = (results == null) ? 0 : results.getRowCount(); i <= size; i++) {
218                 String schemaName = results.getColumnCount() == 1 
219                     ? schema.getName() : results.getElement(1, i).toString();
220                 if (schemaName != null) {
221                         schemaName = schemaName.trim();
222                 }
223                 String tableName = results.getColumnCount() == 1 
224                     ? results.getElement(1, i).toString() 
225                     : results.getElement(2, i).toString();
226                 if (tableName != null && tableName.length() > 0) {
227                     Entity entity = EntityFactory.getInstance().create(
228                         bookmark, schemaName, tableName, type);
229                     if (entity != null) {
230                         list.add(entity);
231                     }
232                 }
233             }
234         }
235         return list;
236     }
237     
238     public DataType[] getTypes() throws NotConnectedException, SQLException {
239         DatabaseMetaData metaData = getMetaData();
240         List list = new ArrayList();
241         ResultSet results = metaData.getTypeInfo();
242         try {
243                 while (results.next()) {
244                         list.add(new DataType(
245                                         results.getInt(TYPE_INFO_METADATA_DATA_TYPE),
246                                         results.getString(TYPE_INFO_METADATA_TYPE_NAME),
247                                                 results.getInt(TYPE_INFO_METADATA_PRECISION),
248                                                 results.getString(TYPE_INFO_METADATA_LITERAL_PREFIX),
249                                                 results.getString(TYPE_INFO_METADATA_LITERAL_SUFFIX),
250                                                 results.getString(TYPE_INFO_METADATA_CREATE_PARMS)
251                                                 ));
252                 }
253         } finally {
254                 results.close();
255         }
256         return (DataType[]) list.toArray(new DataType[list.size()]);
257     }
258
259
260     /**
261          * @return
262          * @throws NotConnectedException
263          * @throws SQLException
264          */
265         private DatabaseMetaData getMetaData() throws NotConnectedException, SQLException {
266                 Connection connection = this.bookmark.getConnection();
267         DatabaseMetaData metaData = connection.getMetaData();
268                 return metaData;
269         }
270
271         private String getSQL(Bookmark bookmark, String type, Schema schema) {
272         if (Entity.TABLE_TYPE.equals(type)) {
273             return this.databaseAdapter.getShowTableQuery(schema.getName());
274         } else if (Entity.VIEW_TYPE.equals(type)) {
275             return this.databaseAdapter.getShowViewQuery(schema.getName());
276         } else if (Entity.SEQUENCE_TYPE.equals(type)) {
277             return this.databaseAdapter.getShowSequenceQuery(schema.getName());
278         } else {
279             return null;
280         }
281     }
282         
283         public ForeignKey[] getExportedKeys(String schema, String entityName) 
284                         throws NotConnectedException, SQLException {
285                 DatabaseMetaData metaData = getMetaData();
286                 List list = new ArrayList();
287                 return getForeignKeys(list, metaData.getExportedKeys(null, schema, entityName));
288         }
289
290         public ForeignKey[] getImportedKeys(String schema, String entityName) 
291                         throws NotConnectedException, SQLException {
292                 DatabaseMetaData metaData = getMetaData();
293                 List list = new ArrayList();
294                 return getForeignKeys(list, metaData.getImportedKeys(null, schema, entityName));
295         }
296
297         /**
298          * @param list
299          * @param resultSet
300          * @return
301          * @throws SQLException
302          */
303         private ForeignKey[] getForeignKeys(List list, ResultSet resultSet) throws SQLException {
304                 ForeignKeyImpl foreignKey = null;
305                 
306                 int lowestKeySequence = Integer.MAX_VALUE;
307                 try {
308                         while (resultSet.next()) {
309                                 int keySequence = resultSet.getInt(FOREIGN_KEY_METADATA_KEY_SEQ);
310                                 lowestKeySequence = Math.min(lowestKeySequence, keySequence);
311                                 
312                                 if (keySequence == lowestKeySequence || foreignKey == null) {
313                                         foreignKey = new ForeignKeyImpl();
314                                         list.add(foreignKey);
315                                         foreignKey.setName(resultSet.getString(FOREIGN_KEY_METADATA_FK_NAME));
316                                         foreignKey.setDeleteRule(resultSet.getShort(FOREIGN_KEY_METADATA_DELETE_RULE));
317                                         foreignKey.setForeignEntitySchema(resultSet.getString(FOREIGN_KEY_METADATA_FKTABLE_SCHEM));
318                                         foreignKey.setForeignEntityName(resultSet.getString(FOREIGN_KEY_METADATA_FKTABLE_NAME));
319                                         foreignKey.setLocalEntitySchema(resultSet.getString(FOREIGN_KEY_METADATA_PKTABLE_SCHEM));
320                                         foreignKey.setLocalEntityName(resultSet.getString(FOREIGN_KEY_METADATA_PKTABLE_NAME));
321                                 }
322                                 
323                                 foreignKey.addColumns(
324                                                 resultSet.getString(FOREIGN_KEY_METADATA_PKCOLUMN_NAME), 
325                                                 resultSet.getString(FOREIGN_KEY_METADATA_FKCOLUMN_NAME));
326                         }
327                         return (ForeignKey[]) list.toArray(new ForeignKey[list.size()]);
328                 } finally {
329                         resultSet.close();
330                 }
331         }
332
333         /**
334          * @return
335          * @throws SQLException
336          * @throws NotConnectedException
337          */
338         public Schema[] getSchemas() throws NotConnectedException, SQLException {
339                 DatabaseMetaData metaData = getMetaData();
340                 List list = new ArrayList();
341                 
342                 if (metaData.supportsSchemasInTableDefinitions()) {
343                         ResultSet resultSet = metaData.getSchemas();
344                         try {
345                                 while (resultSet.next()) {
346                                         String schemaName = resultSet.getString("TABLE_SCHEM");
347                                         list.add(new Schema(schemaName));
348                                 }
349                         } finally {
350                                 resultSet.close();
351                         }
352                 }
353                 return (Schema[]) list.toArray(new Schema[list.size()]);
354         }
355 }