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 }