specificoracle.java

java开源的企业总线.xmlBlaster

            names.add(rs.getString(1));
         log.info("processing '" + names.size() + "' entries");
         return names.size();
      }
      finally {
         try {
            if (rs != null)   
               rs.close();
         }
         catch (Throwable ex) { ex.printStackTrace(); }
         try {
            if (st != null)
               st.close();
         }
         catch (Throwable ex) { ex.printStackTrace(); }
      }
   }

   /**
    * Gets the number of connected users belonging to this schema.
    * @param conn
    * @param schema
    * @return
    * @throws Exception
    */
   private void checkSchemaConnections(Connection conn, String schema) throws Exception {
      String testRequest = "select count(*) from V$SESSION where USERNAME='" + schema + "'";
      Statement st = null;
      try {
         st = conn.createStatement();
         ResultSet rs = st.executeQuery(testRequest);
         if (rs.next()) {
            int connectedUsers = rs.getInt(1);
            if (connectedUsers != 0) {
               String txt = "There are '" + connectedUsers + "' connected users to the schema '" + schema + 
                            "' which we want to wipe out. Make sure to log out such users manually. To find out more execute \"" + 
                            "select * from V$SESSION where USERNAME='" + schema + "'\"";
               log.warning(txt);
               if (this.wipeoutExIfConnected)
                  throw new Exception(txt);
            }
         }
         else {
            log.warning("The statement '" + testRequest + "' did not return any result, can not determine the number of connected users");
         }
      }
      finally {
         if (st != null)
            st.close(); // this also closes the result set
      }
   }
   
   private int wipeoutSchemaSingleSweep(String catalog, String schema, boolean[] objectsToWipeout) throws Exception {
      Connection conn = null;
      int sum = 0;
      try {
         conn = this.dbPool.reserve();
         conn.setAutoCommit(true);
         
         // test to see if there are users which are logged in (there should not)
         checkSchemaConnections(conn, schema);
         try {  
            // TRIGGERS
            if (objectsToWipeout[WIPEOUT_TRIGGERS] && this.wipeoutTriggers) {
               ArrayList names = new ArrayList();
               String sql = "SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER='" + schema + "' AND OBJECT_TYPE='TRIGGER'";
               log.info("going to execute sql statement '" + sql + "'");
               sum += invokeListStatement(conn, names, sql);
               // since cleanupOp does not really return the number of effectively removed entries
               if (names.size() > 0)
                  cleanupOp(conn, names, schema, "DROP TRIGGER", "");
            }
         }
         catch (Exception ex) {
            ex.printStackTrace();
         }
         
         try {  // SEQUENCES
            if (objectsToWipeout[WIPEOUT_SEQUENCES] && this.wipeoutSequences) {
               ArrayList names = new ArrayList();
               String sql = "SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER='" + schema + "' AND OBJECT_TYPE='SEQUENCE'";
               log.info("going to execute sql statement '" + sql + "'");
               sum += invokeListStatement(conn, names, sql);
               // since cleanupOp does not really return the number of effectively removed entries
               if (names.size() > 0)
                  cleanupOp(conn, names, schema, "DROP SEQUENCE", "");
            }
         }
         catch (Exception ex) {
            ex.printStackTrace();
         }
         try {  // FUNCTIONS
            if (objectsToWipeout[WIPEOUT_FUNCTIONS] && this.wipeoutFunctions) {
               ArrayList names = new ArrayList();
               String sql = "SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER='" + schema + "' AND OBJECT_TYPE='FUNCTION'";
               log.info("going to execute sql statement '" + sql + "'");
               sum += invokeListStatement(conn, names, sql);
               // since cleanupOp does not really return the number of effectively removed entries
               if (names.size() > 0)
                  cleanupOp(conn, names, schema, "DROP FUNCTION", "");
            }
         }
         catch (Exception ex) {
            ex.printStackTrace();
         }
         try {  // PACKAGES
            if (objectsToWipeout[WIPEOUT_PACKAGES] && this.wipeoutPackages) {
               ArrayList names = new ArrayList();
               String sql = "SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER='" + schema + "' AND OBJECT_TYPE='PACKAGE'";
               log.info("going to execute sql statement '" + sql + "'");
               sum += invokeListStatement(conn, names, sql);
               // since cleanupOp does not really return the number of effectively removed entries
               if (names.size() > 0)
                  cleanupOp(conn, names, schema, "DROP PACKAGE", "");
            }
         }
         catch (Exception ex) {
            ex.printStackTrace();
         }
         try {  // PROCEDURES
            if (objectsToWipeout[WIPEOUT_PROCEDURES] && this.wipeoutProcedures) {
               ArrayList names = new ArrayList();
               String sql = "SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER='" + schema + "' AND OBJECT_TYPE='PROCEDURE'";
               log.info("going to execute sql statement '" + sql + "'");
               sum += invokeListStatement(conn, names, sql);
               // since cleanupOp does not really return the number of effectively removed entries
               if (names.size() > 0)
                  cleanupOp(conn, names, schema, "DROP PROCEDURE", "");
            }
         }
         catch (Exception ex) {
            ex.printStackTrace();
         }
         try {  // VIEWS 
            if (objectsToWipeout[WIPEOUT_VIEWS] && this.wipeoutViews) {
               ArrayList names = new ArrayList();
               String sql = "SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER='" + schema + "' AND OBJECT_TYPE='VIEW'";
               log.info("going to execute sql statement '" + sql + "'");
               sum += invokeListStatement(conn, names, sql);
               // since cleanupOp does not really return the number of effectively removed entries
               if (names.size() > 0)
                  cleanupOp(conn, names, schema, "DROP VIEW", "CASCADE CONSTRAINTS");
            }
         }
         catch (Exception ex) {
            ex.printStackTrace();
         }
         try {  // TABLES  
            if (objectsToWipeout[WIPEOUT_TABLES] && this.wipeoutTables) {
               ArrayList names = new ArrayList();
               String sql = "SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER='" + schema + "' AND OBJECT_TYPE='TABLE'";
               log.info("going to execute sql statement '" + sql + "'");
               sum += invokeListStatement(conn, names, sql);
               // since cleanupOp does not really return the number of effectively removed entries
               if (names.size() > 0)
                  cleanupOp(conn, names, schema, "DROP TABLE", "CASCADE CONSTRAINTS");
            }
         }
         catch (Exception ex) {
            ex.printStackTrace();
         }
         try {  // SYNONYMS
            if (objectsToWipeout[WIPEOUT_SYNONYMS] && this.wipeoutSynonyms) {
               ArrayList names = new ArrayList();
               String sql = "SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER='" + schema + "' AND OBJECT_TYPE='SYNONYM'";
               log.info("going to execute sql statement '" + sql + "'");
               sum += invokeListStatement(conn, names, sql);
               // since cleanupOp does not really return the number of effectively removed entries
               if (names.size() > 0)
                  cleanupOp(conn, names, schema, "DROP SYNONYM", "");
            }
         }
         catch (Exception ex) {
            ex.printStackTrace();
         }
         try {  // INDEXES
            if (objectsToWipeout[WIPEOUT_INDEXES] && this.wipeoutIndexes) {
               ArrayList names = new ArrayList();
               String sql = "SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER='" + schema + "' AND OBJECT_TYPE='INDEX'";
               log.info("going to execute sql statement '" + sql + "'");
               sum += invokeListStatement(conn, names, sql);
               // since cleanupOp does not really return the number of effectively removed entries
               if (names.size() > 0)
                  cleanupOp(conn, names, schema, "DROP INDEX", "FORCE");
            }
         }
         catch (Exception ex) {
            ex.printStackTrace();
         }

      }
      catch (SQLException ex) {
        conn = removeFromPool(conn, ROLLBACK_NO);
      }
      finally {
         conn = releaseIntoPool(conn, COMMIT_NO);
      }
      return sum;
   }

   /**
    * @see org.xmlBlaster.contrib.replication.I_DbSpecific#getContentFromGuid(java.lang.String, java.lang.String, java.lang.String, java.lang.String)
    */
   public String getContentFromGuid(String guid, String catalog, String schema, String table, I_AttributeTransformer transformer) throws Exception {
      // throw new Exception("SpecificOracle.getContentFromGuid is not implemented yet for table='" + table + "' and guid='" + guid + "'");
      SqlInfo obj = new SqlInfo(this.info);
      Connection conn = null;
      Statement st = null;
      String completeTable = schema;
      if (completeTable != null)
         completeTable += "." + table;
      else
         completeTable = table;
      try {
         conn = this.dbPool.reserve();
         st = conn.createStatement();
         String sql = "select * from " + completeTable + " WHERE rowid=CHARTOROWID('" + guid + "')";
         ResultSet rs = st.executeQuery(sql);
         if (rs.next()) {
            obj.fillOneRowWithObjects(rs, transformer);
            SqlRow row = (SqlRow)obj.getRows().get(0);
            rs.close();
            return row.toXml("", false);
         }
         else {
            log.severe("The entry guid='" + guid + "' for table '" + completeTable + "' was not found (anymore)");
            return "";
         }
            
      }
      catch (Exception ex) {
         conn = removeFromPool(conn, ROLLBACK_NO);
         throw ex;
      }
      finally { 
         if (st != null) {
            try {
               st.close();
            }
            catch (Exception e) {
               e.printStackTrace();
            }
         }
         conn = releaseIntoPool(conn, COMMIT_NO);
      }
   }

   /**
    * returns true if the sequence exists already.
    */
   protected boolean sequenceExists(Connection conn, String sequenceName) throws Exception {
      Statement st = null;
      try {
         st = conn.createStatement();
         ResultSet rs = st.executeQuery("SELECT * from ALL_SEQUENCES WHERE SEQUENCE_NAME='" + sequenceName + "'");
         return rs.next();
      }
      finally {
         if (st != null) {
            try {
               st.close();
            }
            catch (Exception ex) {
               log.warning("An exception occured when closing the statement, but the result will be considered: " + ex.getMessage());
               ex.printStackTrace();
            }
         }
      }
   }

   protected boolean triggerExists(Connection conn, String triggerName) throws Exception {
      Statement st = null;
      try {
         st = conn.createStatement();
         ResultSet rs = st.executeQuery("SELECT * from ALL_TRIGGERS WHERE TRIGGER_NAME='" + triggerName + "'");
         return rs.next();
      }
      finally {
         if (st != null) {
            try {
               st.close();
            }
            catch (Exception ex) {
               log.warning("An exception occured when closing the statement, but the result will be considered: " + ex.getMessage());
               ex.printStackTrace();
            }
         }
      }
   }
   
   
   /**
    * @see org.xmlBlaster.contrib.replication.I_DbSpecific#triggerExists(java.sql.Connection, org.xmlBlaster.contrib.replication.TableToWatchInfo)
    */
   public boolean triggerExists(Connection conn, TableToWatchInfo tableToWatch) throws Exception {
      PreparedStatement st = null;
      try {
         String sql = "SELECT table_name, table_owner, status FROM all_triggers where trigger_name=?";
         st = conn.prepareStatement(sql);
         String triggerName = tableToWatch.getTrigger();
         st.setString(1, triggerName);
         ResultSet rs = st.executeQuery();
         if (rs.next()) {
            String tableName = rs.getString(1);
            String tableOwner = rs.getString(2);
            String status = rs.getString(3);
            boolean ret = true;
            if (!tableName.equalsIgnoreCase(tableToWatch.getTable())) {
               log.warning("trigger '" + triggerName + "' exists, is on table '" + tableName + "' but is expected to be on '" + tableToWatch.getTable() + "'");
               ret = false;
            }
            if (!tableOwner.equalsIgnoreCase(tableToWatch.getSchema())) {
               log.warning("trigger '" + tableToWatch.getTrigger() + "' exists, is onwed by schema '" + tableOwner + "' but is expected to be on '" + tableToWatch.getSchema() + "'");
               ret = false;
            }
            if (!status.equalsIgnoreCase("ENABLED")) {
               log.warning("trigger '" + tableToWatch.getTrigger() + "' exists but is not enabled");
               ret = false;
            }
            if (!ret) {
               tableToWatch.setStatus(TableToWatchInfo.STATUS_REMOVE);
               tableToWatch.storeStatus(this.replPrefix, this.dbPool);
            }
            return ret;
         }
         return false;
      }
      finally {
         if (st != null)
            st.close();
      }
   }
   
   public String getOwnSchema() {
      return this.ownSchema;
   }
   
}