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