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             // TODO: This should be polished so that synonyms can be shown with different icons as regular Entities
 171             list.addAll(getSynonymsList(bookmark, connection, types[i], schema));
 172         }
 173
 174         return (Entity[]) list.toArray(new Entity[list.size()]);
 175     }
 176
 177     protected List getEntitiesList(Bookmark bookmark, Connection connection, String type, Schema schema)
 178         throws SQLException {
 179
 180         List list = new ArrayList();
 181         //      We try first the JDBC driver
 182         DatabaseMetaData metaData = connection.getMetaData();
 183         // getTables needs a null schema to get all the schemas. So we don't pass a "" schema, but a null one
 184                 ResultSet set = null;
 185                 if (metaData.supportsSchemasInTableDefinitions()) {
 186                 set = metaData.getTables(null, (schema != null) ? schema.getName() : null, "%", new String[] { type });
 187                 } else {
 188                         set = metaData.getTables(null, null, "%", new String[] { type });
 189                 }
 190
 191         while (set.next()) {
 192             String tempSchema = set.getString(TABLE_METADATA_TABLE_SCHEM);
 193             tempSchema = (tempSchema == null) ? "" : tempSchema.trim();
 194             String tableName = set.getString(TABLE_METADATA_TABLE_NAME);
 195             tableName = (tableName == null) ? "" : tableName.trim();
 196
 197             if (tableName != null && tableName.length() > 0) {
 198                 Entity entity = EntityFactory.getInstance().create(bookmark, tempSchema, tableName, type, false);
 199                 if (entity != null) {
 200                     list.add(entity);
 201                 }
 202             }
 203         }
 204         set.close();
 205         // If we have some results, then the JDBC driver is working,
 206         // so we return the results and quit
 207         if (list.size() > 0)
 208                 return list;
 209
 210
 211         // If no results, we check also the sql query to get the list of entities
 212         SQLResultSetResults results = null;
 213         // Get the proper sql query to the appropiate type of entity
 214         String sql = getSQL(bookmark, type, schema);
 215         // If nothing returned, too bad, it seems there is no sql query for that database and entity type
 216         if (sql != null) {
 217             results = (SQLResultSetResults) MultiSQLServer.getInstance().execute(
 218                         bookmark, connection, sql, Integer.MAX_VALUE);
 219             for (int i = 1, size = (results == null) ? 0 : results.getRowCount(); i <= size; i++) {
 220                 String schemaName = results.getColumnCount() == 1
 221                     ? schema.getName() : results.getElement(1, i).toString();
 222                 if (schemaName != null) {
 223                         schemaName = schemaName.trim();
 224                 }
 225                 String tableName = results.getColumnCount() == 1
 226                     ? results.getElement(1, i).toString()
 227                     : results.getElement(2, i).toString();
 228                 if (tableName != null && tableName.length() > 0) {
 229                     Entity entity = EntityFactory.getInstance().create(
 230                         bookmark, schemaName, tableName, type, false);
 231                     if (entity != null) {
 232                         list.add(entity);
 233                     }
 234                 }
 235             }
 236         }
 237         return list;
 238     }
 239     /**
 240      * Returns a list with the synonym objects of the given type, using a query
 241      * @param bookmark
 242      * @param connection
 243      * @param type
 244      * @param schema
 245      * @return
 246      * @throws SQLException
 247      */
 248     protected List getSynonymsList(Bookmark bookmark, Connection connection, String type, Schema schema)
 249     throws SQLException {
 250
 251     List list = new ArrayList();
 252     //      We try first the JDBC driver
 253     DatabaseMetaData metaData = connection.getMetaData();
 254     SQLResultSetResults results = null;
 255     // Get the proper sql query to the appropiate type of entity
 256     String sql = this.databaseAdapter.getShowSynonymsQuery(schema.getName(), type);
 257     // If nothing returned, too bad, it seems there is no sql query for that database and entity type
 258     if (sql != null) {
 259         results = (SQLResultSetResults) MultiSQLServer.getInstance().execute(
 260                         bookmark, connection, sql, Integer.MAX_VALUE);
 261         for (int i = 1, size = (results == null) ? 0 : results.getRowCount(); i <= size; i++) {
 262             String schemaName = results.getColumnCount() == 1
 263                 ? schema.getName() : results.getElement(1, i).toString();
 264             if (schemaName != null) {
 265                 schemaName = schemaName.trim();
 266             }
 267             String tableName = results.getColumnCount() == 1
 268                 ? results.getElement(1, i).toString()
 269                 : results.getElement(2, i).toString();
 270             if (tableName != null && tableName.length() > 0) {
 271                 Entity entity = EntityFactory.getInstance().create(
 272                     bookmark, schemaName, tableName, type, true);
 273                 if (entity != null) {
 274                     list.add(entity);
 275                 }
 276             }
 277         }
 278     }
 279     return list;
 280 }
 281
 282     public DataType[] getTypes() throws NotConnectedException, SQLException {
 283         DatabaseMetaData metaData = getMetaData();
 284         List list = new ArrayList();
 285         ResultSet results = metaData.getTypeInfo();
 286         try {
 287                 while (results.next()) {
 288                         list.add(new DataType(
 289                                         results.getInt(TYPE_INFO_METADATA_DATA_TYPE),
 290                                         results.getString(TYPE_INFO_METADATA_TYPE_NAME),
 291                                                 results.getLong(TYPE_INFO_METADATA_PRECISION),
 292                                                 results.getString(TYPE_INFO_METADATA_LITERAL_PREFIX),
 293                                                 results.getString(TYPE_INFO_METADATA_LITERAL_SUFFIX),
 294                                                 results.getString(TYPE_INFO_METADATA_CREATE_PARMS)
 295                                                 ));
 296                 }
 297         } finally {
 298                 results.close();
 299         }
 300         return (DataType[]) list.toArray(new DataType[list.size()]);
 301     }
 302
 303
 304     /**
 305          * @return
 306          * @throws NotConnectedException
 307          * @throws SQLException
 308          */
 309         private DatabaseMetaData getMetaData() throws NotConnectedException, SQLException {
 310                 Connection connection = this.bookmark.getConnection();
 311         DatabaseMetaData metaData = connection.getMetaData();
 312                 return metaData;
 313         }
 314
 315         private String getSQL(Bookmark bookmark, String type, Schema schema) {
 316         if (Entity.TABLE_TYPE.equals(type)) {
 317             return this.databaseAdapter.getShowTableQuery(schema.getName());
 318         } else if (Entity.VIEW_TYPE.equals(type)) {
 319             return this.databaseAdapter.getShowViewQuery(schema.getName());
 320         } else if (Entity.SEQUENCE_TYPE.equals(type)) {
 321             return this.databaseAdapter.getShowSequenceQuery(schema.getName());
 322         } else {
 323             return null;
 324         }
 325     }
 326
 327         public ForeignKey[] getExportedKeys(String schema, String entityName)
 328                         throws NotConnectedException, SQLException {
 329                 DatabaseMetaData metaData = getMetaData();
 330                 List list = new ArrayList();
 331                 return getForeignKeys(list, metaData.getExportedKeys(null, schema, entityName));
 332         }
 333
 334         public ForeignKey[] getImportedKeys(String schema, String entityName)
 335                         throws NotConnectedException, SQLException {
 336                 DatabaseMetaData metaData = getMetaData();
 337                 List list = new ArrayList();
 338                 return getForeignKeys(list, metaData.getImportedKeys(null, schema, entityName));
 339         }
 340
 341         /**
 342          * @param list
 343          * @param resultSet
 344          * @return
 345          * @throws SQLException
 346          */
 347         private ForeignKey[] getForeignKeys(List list, ResultSet resultSet) throws SQLException {
 348                 ForeignKeyImpl foreignKey = null;
 349
 350                 int lowestKeySequence = Integer.MAX_VALUE;
 351                 try {
 352                         while (resultSet.next()) {
 353                                 int keySequence = resultSet.getInt(FOREIGN_KEY_METADATA_KEY_SEQ);
 354                                 lowestKeySequence = Math.min(lowestKeySequence, keySequence);
 355
 356                                 if (keySequence == lowestKeySequence || foreignKey == null) {
 357                                         foreignKey = new ForeignKeyImpl();
 358                                         list.add(foreignKey);
 359                                         foreignKey.setName(resultSet.getString(FOREIGN_KEY_METADATA_FK_NAME));
 360                                         foreignKey.setDeleteRule(resultSet.getShort(FOREIGN_KEY_METADATA_DELETE_RULE));
 361                                         foreignKey.setForeignEntitySchema(resultSet.getString(FOREIGN_KEY_METADATA_FKTABLE_SCHEM));
 362                                         foreignKey.setForeignEntityName(resultSet.getString(FOREIGN_KEY_METADATA_FKTABLE_NAME));
 363                                         foreignKey.setLocalEntitySchema(resultSet.getString(FOREIGN_KEY_METADATA_PKTABLE_SCHEM));
 364                                         foreignKey.setLocalEntityName(resultSet.getString(FOREIGN_KEY_METADATA_PKTABLE_NAME));
 365                                 }
 366
 367                                 foreignKey.addColumns(
 368                                                 resultSet.getString(FOREIGN_KEY_METADATA_PKCOLUMN_NAME),
 369                                                 resultSet.getString(FOREIGN_KEY_METADATA_FKCOLUMN_NAME));
 370                         }
 371                         return (ForeignKey[]) list.toArray(new ForeignKey[list.size()]);
 372                 } finally {
 373                         resultSet.close();
 374                 }
 375         }
 376
 377         /**
 378          * @return
 379          * @throws SQLException
 380          * @throws NotConnectedException
 381          */
 382         public Schema[] getSchemas() throws NotConnectedException, SQLException {
 383                 DatabaseMetaData metaData = getMetaData();
 384                 List list = new ArrayList();
 385
 386                 if (metaData.supportsSchemasInTableDefinitions()) {
 387                         ResultSet resultSet = metaData.getSchemas();
 388                         try {
 389                                 while (resultSet.next()) {
 390                                         String schemaName = resultSet.getString("TABLE_SCHEM");
 391                                         list.add(new Schema(schemaName));
 392                                 }
 393                         } finally {
 394                                 resultSet.close();
 395                         }
 396                 }
 397                 return (Schema[]) list.toArray(new Schema[list.size()]);
 398         }
 399 }