Implemeted action for uploading Wikipedia articles (thanks to D.Wunsch)
[phpeclipse.git] / archive / net.sourceforge.phpeclipse.quantum.sql / src / com / quantum / model / Database.java
index b6dee05..29d1993 100644 (file)
@@ -5,53 +5,85 @@ import java.sql.DatabaseMetaData;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.util.ArrayList;
+import java.util.Collection;
 import java.util.Collections;
 import java.util.HashSet;
 import java.util.List;
 import java.util.Set;
 
-import com.quantum.IQuantumConstants;
 import com.quantum.adapters.DatabaseAdapter;
 import com.quantum.sql.MultiSQLServer;
-import com.quantum.sql.SQLResults;
+import com.quantum.sql.SQLResultSetResults;
 
 /**
  * @author BC
  */
 public class Database {
     
-    private DatabaseAdapter databaseAdapter;
+       private static final int TABLE_METADATA_TABLE_SCHEM = 2;
+       private static final int TABLE_METADATA_TABLE_NAME = 3;
+       
+       private static final int TABLE_TYPE_METADATA_TABLE_TYPE = 1;
+
+       private static final int FOREIGN_KEY_METADATA_PKTABLE_SCHEM = 2;
+       private static final int FOREIGN_KEY_METADATA_PKTABLE_NAME = 3;
+       private static final int FOREIGN_KEY_METADATA_PKCOLUMN_NAME = 4;
+       private static final int FOREIGN_KEY_METADATA_FKTABLE_SCHEM = 6;
+       private static final int FOREIGN_KEY_METADATA_FKTABLE_NAME = 7;
+       private static final int FOREIGN_KEY_METADATA_FKCOLUMN_NAME = 8;
+       private static final int FOREIGN_KEY_METADATA_KEY_SEQ = 9;
+       private static final int FOREIGN_KEY_METADATA_DELETE_RULE = 11;
+       private static final int FOREIGN_KEY_METADATA_FK_NAME = 12;
+       
+       private static final int TYPE_INFO_METADATA_TYPE_NAME = 1;
+       private static final int TYPE_INFO_METADATA_DATA_TYPE = 2;
+       private static final int TYPE_INFO_METADATA_PRECISION = 3;
+       private static final int TYPE_INFO_METADATA_LITERAL_PREFIX = 4;
+       private static final int TYPE_INFO_METADATA_LITERAL_SUFFIX = 5;
+       private static final int TYPE_INFO_METADATA_CREATE_PARMS = 6;
+       
+       private DatabaseAdapter databaseAdapter;
     private Bookmark bookmark;
     
+    private List entityTypes;
+    
     public Database(Bookmark bookmark) {
         this.bookmark = bookmark;
         this.databaseAdapter = bookmark.getAdapter();
     }
 
-    private static final String[] ALL_TYPES = { 
-        IQuantumConstants.Table, 
-        IQuantumConstants.View, 
-        IQuantumConstants.Sequence };
+    private static final String[] ALL_TABLE_TYPES = { 
+        Entity.TABLE_TYPE, 
+        Entity.VIEW_TYPE, 
+        Entity.SEQUENCE_TYPE };
         
     private static final List STANDARD_TABLE_TYPES = 
         Collections.synchronizedList(new ArrayList());
         
     static {
-        for (int i = 0, length = (ALL_TYPES == null) ? 0 : ALL_TYPES.length;
+        for (int i = 0, length = (ALL_TABLE_TYPES == null) ? 0 : ALL_TABLE_TYPES.length;
             i < length;
             i++) {
-            STANDARD_TABLE_TYPES.add(ALL_TYPES[i]);
+            STANDARD_TABLE_TYPES.add(ALL_TABLE_TYPES[i]);
         }
     }
     
-    public String[] getEntityTypes() 
+    public synchronized String[] getEntityTypes() 
         throws NotConnectedException, SQLException {
-        return getEntityTypes(this.bookmark.getConnection(), this.bookmark.getSchemas()[0]);
+       if (this.entityTypes == null) {
+               Collection collection = initializeEntityTypes(this.bookmark.getConnection());
+            this.entityTypes = Collections.synchronizedList(new ArrayList(collection));
+       }
+        return (String[]) this.entityTypes.toArray(new String[this.entityTypes.size()]);
+    }
+    
+    public String getUsername() throws NotConnectedException, SQLException {
+       return getMetaData().getUserName();
     }
 
 
     /**
-     * <p>This method returns a list of entity types supported by the database
+     * <p>This method returns a set of entity types supported by the database
      * adapter.  This list will always be limited to Tables, Views and 
      * Sequences.</p>
      * 
@@ -60,44 +92,38 @@ public class Database {
      * Tables, Views and Sequences.</p>
      * 
      * @param connection
-     * @param schema -
-     *      This parameter is somewhat bogus.  It is used to determine if the
-     *      adapter defines an SQL statement for finding entities of a 
-     *      particular types.  
-     * @return
+     * @return a set of Strings, typically "TABLE", "VIEW", and "SEQUENCE"
      * @throws SQLException
      */
-    public String[] getEntityTypes(Connection connection, Schema schema) 
-        throws SQLException {
+    private Set initializeEntityTypes(Connection connection) throws SQLException {
         
         Set set = new HashSet();
-        if (this.databaseAdapter.getShowTableQuery(schema.getName(), false) != null) {
-            set.add(IQuantumConstants.Table);
-        } else if (this.databaseAdapter.getShowViewQuery(schema.getName(), false) != null) {
-            set.add(IQuantumConstants.View);
-        } else if (this.databaseAdapter.getShowSequenceQuery(schema.getName(), false) != null) {
-            set.add(IQuantumConstants.Sequence);
+        if (this.databaseAdapter.getShowTableQuery(this.bookmark.getUsername()) != null) {
+            set.add(Entity.TABLE_TYPE);
+        } else if (this.databaseAdapter.getShowViewQuery(this.bookmark.getUsername()) != null) {
+            set.add(Entity.VIEW_TYPE);
+        } else if (this.databaseAdapter.getShowSequenceQuery(this.bookmark.getUsername()) != null) {
+            set.add(Entity.SEQUENCE_TYPE);
         }
         
         DatabaseMetaData metaData = connection.getMetaData();
         ResultSet resultSet = metaData.getTableTypes();
         while (resultSet.next()) {
-            String type = resultSet.getString("TABLE_TYPE");
+            String type = resultSet.getString(TABLE_TYPE_METADATA_TABLE_TYPE);
             if (type != null) {
+               // Informix, in particular, pads this with extra spaces
                 type = type.trim();
             }
             if (STANDARD_TABLE_TYPES.contains(type)) {
                 set.add(type);
             }
         }
-        
-        return (String[]) set.toArray(new String[set.size()]);
+        return set;
     }
 
     public String getInformation() throws SQLException {
         try {
-            Connection connection = this.bookmark.getConnection();
-            DatabaseMetaData metaData = connection.getMetaData();
+            DatabaseMetaData metaData = getMetaData();
         
             return metaData == null ? null : metaData.getDatabaseProductName() + " " 
                     + metaData.getDatabaseProductVersion();
@@ -137,10 +163,12 @@ public class Database {
         throws SQLException {
         
         List list = new ArrayList();
-        String[] types = (type == null) ? ALL_TYPES : new String[] { type };
+        String[] types = (type == null) ? ALL_TABLE_TYPES : new String[] { type };
             
         for (int i = 0; i < types.length; i++) {
             list.addAll(getEntitiesList(bookmark, connection, types[i], schema));
+            // TODO: This should be polished so that synonyms can be shown with different icons as regular Entities
+            list.addAll(getSynonymsList(bookmark, connection, types[i], schema));
         }
 
         return (Entity[]) list.toArray(new Entity[list.size()]);
@@ -149,66 +177,223 @@ public class Database {
     protected List getEntitiesList(Bookmark bookmark, Connection connection, String type, Schema schema)
         throws SQLException {
 
-        String sql = getSQL(bookmark, type, schema);
         List list = new ArrayList();
-        SQLResults results = null;
+        //      We try first the JDBC driver
+        DatabaseMetaData metaData = connection.getMetaData();
+        // getTables needs a null schema to get all the schemas. So we don't pass a "" schema, but a null one
+               ResultSet set = null;
+               if (metaData.supportsSchemasInTableDefinitions()) {
+               set = metaData.getTables(null, (schema != null) ? schema.getName() : null, "%", new String[] { type });
+               } else {
+                       set = metaData.getTables(null, null, "%", new String[] { type });
+               }
+           
+        while (set.next()) {
+            String tempSchema = set.getString(TABLE_METADATA_TABLE_SCHEM);
+            tempSchema = (tempSchema == null) ? "" : tempSchema.trim();
+            String tableName = set.getString(TABLE_METADATA_TABLE_NAME);
+            tableName = (tableName == null) ? "" : tableName.trim();
+
+            if (tableName != null && tableName.length() > 0) {
+                Entity entity = EntityFactory.getInstance().create(bookmark, tempSchema, tableName, type, false);
+                if (entity != null) {
+                    list.add(entity);
+                }
+            }
+        }
+        set.close();
+        // If we have some results, then the JDBC driver is working, 
+        // so we return the results and quit
+        if (list.size() > 0) 
+               return list;
+        
+        
+        // If no results, we check also the sql query to get the list of entities
+        SQLResultSetResults results = null;
+        // Get the proper sql query to the appropiate type of entity
+        String sql = getSQL(bookmark, type, schema);
+        // If nothing returned, too bad, it seems there is no sql query for that database and entity type
         if (sql != null) {
-            results = MultiSQLServer.getInstance().execute(connection, sql);
+            results = (SQLResultSetResults) MultiSQLServer.getInstance().execute(
+                       bookmark, connection, sql, Integer.MAX_VALUE);
             for (int i = 1, size = (results == null) ? 0 : results.getRowCount(); i <= size; i++) {
                 String schemaName = results.getColumnCount() == 1 
                     ? schema.getName() : results.getElement(1, i).toString();
+                if (schemaName != null) {
+                       schemaName = schemaName.trim();
+                }
                 String tableName = results.getColumnCount() == 1 
                     ? results.getElement(1, i).toString() 
                     : results.getElement(2, i).toString();
                 if (tableName != null && tableName.length() > 0) {
                     Entity entity = EntityFactory.getInstance().create(
-                        bookmark, schemaName, tableName, type);
+                        bookmark, schemaName, tableName, type, false);
                     if (entity != null) {
                         list.add(entity);
                     }
                 }
             }
         }
-        // If we have some results, we go back
-        if (results != null) return list;
-        // Else, we try the JDBC driver
-        DatabaseMetaData metaData = connection.getMetaData();
-        // getTables needs a null schema to get all the schemas. So we don't pass a "" schema, but a null one
-               ResultSet set = null;
-               if (metaData.supportsSchemasInTableDefinitions())
-               set = metaData.getTables(null, (schema != null) ? schema.getName() : null, "%", new String[] { type });
-           else
-                       set = metaData.getTables(null, null, "%", new String[] { type });
-           
-        while (set.next()) {
-            String tempSchema = set.getString("TABLE_SCHEM");
-            tempSchema = (tempSchema == null) ? "" : tempSchema.trim();
-            String tableName = set.getString("TABLE_NAME");
-            tableName = (tableName == null) ? "" : tableName.trim();
+        return list;
+    }
+    /**
+     * Returns a list with the synonym objects of the given type, using a query  
+     * @param bookmark
+     * @param connection
+     * @param type
+     * @param schema
+     * @return
+     * @throws SQLException
+     */
+    protected List getSynonymsList(Bookmark bookmark, Connection connection, String type, Schema schema)
+    throws SQLException {
 
+    List list = new ArrayList();
+    //      We try first the JDBC driver
+    DatabaseMetaData metaData = connection.getMetaData();
+    SQLResultSetResults results = null;
+    // Get the proper sql query to the appropiate type of entity
+    String sql = this.databaseAdapter.getShowSynonymsQuery(schema.getName(), type);
+    // If nothing returned, too bad, it seems there is no sql query for that database and entity type
+    if (sql != null) {
+        results = (SQLResultSetResults) MultiSQLServer.getInstance().execute(
+                       bookmark, connection, sql, Integer.MAX_VALUE);
+        for (int i = 1, size = (results == null) ? 0 : results.getRowCount(); i <= size; i++) {
+            String schemaName = results.getColumnCount() == 1 
+                ? schema.getName() : results.getElement(1, i).toString();
+            if (schemaName != null) {
+               schemaName = schemaName.trim();
+            }
+            String tableName = results.getColumnCount() == 1 
+                ? results.getElement(1, i).toString() 
+                : results.getElement(2, i).toString();
             if (tableName != null && tableName.length() > 0) {
-                Entity entity = EntityFactory.getInstance().create(bookmark, tempSchema, tableName, type);
+                Entity entity = EntityFactory.getInstance().create(
+                    bookmark, schemaName, tableName, type, true);
                 if (entity != null) {
                     list.add(entity);
                 }
             }
         }
-        set.close();
-        return list;
+    }
+    return list;
+}
+    
+    public DataType[] getTypes() throws NotConnectedException, SQLException {
+       DatabaseMetaData metaData = getMetaData();
+       List list = new ArrayList();
+       ResultSet results = metaData.getTypeInfo();
+       try {
+               while (results.next()) {
+                       list.add(new DataType(
+                                       results.getInt(TYPE_INFO_METADATA_DATA_TYPE),
+                                       results.getString(TYPE_INFO_METADATA_TYPE_NAME),
+                                               results.getLong(TYPE_INFO_METADATA_PRECISION),
+                                               results.getString(TYPE_INFO_METADATA_LITERAL_PREFIX),
+                                               results.getString(TYPE_INFO_METADATA_LITERAL_SUFFIX),
+                                               results.getString(TYPE_INFO_METADATA_CREATE_PARMS)
+                                               ));
+               }
+       } finally {
+               results.close();
+       }
+       return (DataType[]) list.toArray(new DataType[list.size()]);
     }
 
 
-    private String getSQL(Bookmark bookmark, String type, Schema schema) {
+    /**
+        * @return
+        * @throws NotConnectedException
+        * @throws SQLException
+        */
+       private DatabaseMetaData getMetaData() throws NotConnectedException, SQLException {
+               Connection connection = this.bookmark.getConnection();
+       DatabaseMetaData metaData = connection.getMetaData();
+               return metaData;
+       }
+
+       private String getSQL(Bookmark bookmark, String type, Schema schema) {
         if (Entity.TABLE_TYPE.equals(type)) {
-            return this.databaseAdapter.getShowTableQuery(schema.getName(), schema.isDefault());
+            return this.databaseAdapter.getShowTableQuery(schema.getName());
         } else if (Entity.VIEW_TYPE.equals(type)) {
-            return this.databaseAdapter.getShowViewQuery(schema.getName(), schema.isDefault());
+            return this.databaseAdapter.getShowViewQuery(schema.getName());
         } else if (Entity.SEQUENCE_TYPE.equals(type)) {
-            return this.databaseAdapter.getShowSequenceQuery(schema.getName(), schema.isDefault());
+            return this.databaseAdapter.getShowSequenceQuery(schema.getName());
         } else {
             return null;
         }
     }
-    
+       
+       public ForeignKey[] getExportedKeys(String schema, String entityName) 
+                       throws NotConnectedException, SQLException {
+               DatabaseMetaData metaData = getMetaData();
+               List list = new ArrayList();
+               return getForeignKeys(list, metaData.getExportedKeys(null, schema, entityName));
+       }
+
+       public ForeignKey[] getImportedKeys(String schema, String entityName) 
+                       throws NotConnectedException, SQLException {
+               DatabaseMetaData metaData = getMetaData();
+               List list = new ArrayList();
+               return getForeignKeys(list, metaData.getImportedKeys(null, schema, entityName));
+       }
+
+       /**
+        * @param list
+        * @param resultSet
+        * @return
+        * @throws SQLException
+        */
+       private ForeignKey[] getForeignKeys(List list, ResultSet resultSet) throws SQLException {
+               ForeignKeyImpl foreignKey = null;
+               
+               int lowestKeySequence = Integer.MAX_VALUE;
+               try {
+                       while (resultSet.next()) {
+                               int keySequence = resultSet.getInt(FOREIGN_KEY_METADATA_KEY_SEQ);
+                               lowestKeySequence = Math.min(lowestKeySequence, keySequence);
+                               
+                               if (keySequence == lowestKeySequence || foreignKey == null) {
+                                       foreignKey = new ForeignKeyImpl();
+                                       list.add(foreignKey);
+                                       foreignKey.setName(resultSet.getString(FOREIGN_KEY_METADATA_FK_NAME));
+                                       foreignKey.setDeleteRule(resultSet.getShort(FOREIGN_KEY_METADATA_DELETE_RULE));
+                                       foreignKey.setForeignEntitySchema(resultSet.getString(FOREIGN_KEY_METADATA_FKTABLE_SCHEM));
+                                       foreignKey.setForeignEntityName(resultSet.getString(FOREIGN_KEY_METADATA_FKTABLE_NAME));
+                                       foreignKey.setLocalEntitySchema(resultSet.getString(FOREIGN_KEY_METADATA_PKTABLE_SCHEM));
+                                       foreignKey.setLocalEntityName(resultSet.getString(FOREIGN_KEY_METADATA_PKTABLE_NAME));
+                               }
+                               
+                               foreignKey.addColumns(
+                                               resultSet.getString(FOREIGN_KEY_METADATA_PKCOLUMN_NAME), 
+                                               resultSet.getString(FOREIGN_KEY_METADATA_FKCOLUMN_NAME));
+                       }
+                       return (ForeignKey[]) list.toArray(new ForeignKey[list.size()]);
+               } finally {
+                       resultSet.close();
+               }
+       }
 
+       /**
+        * @return
+        * @throws SQLException
+        * @throws NotConnectedException
+        */
+       public Schema[] getSchemas() throws NotConnectedException, SQLException {
+               DatabaseMetaData metaData = getMetaData();
+               List list = new ArrayList();
+               
+               if (metaData.supportsSchemasInTableDefinitions()) {
+                       ResultSet resultSet = metaData.getSchemas();
+                       try {
+                               while (resultSet.next()) {
+                                       String schemaName = resultSet.getString("TABLE_SCHEM");
+                                       list.add(new Schema(schemaName));
+                               }
+                       } finally {
+                               resultSet.close();
+                       }
+               }
+               return (Schema[]) list.toArray(new Schema[list.size()]);
+       }
 }