specificoracle.java

java开源的企业总线.xmlBlaster

      buf.append("            oldCont, '").append(this.replVersion).append("');\n");
      // INSERT + UPDATE instead of only INSERT since it appears that the sequence is incremented outside the transaction
      buf.append("    transId := DBMS_TRANSACTION.LOCAL_TRANSACTION_ID(FALSE);\n");
      buf.append("    if transId = NULL THEN\n");
      buf.append("       transId := CHR(replKey);\n");
      buf.append("    END IF;\n");
      buf.append("    UPDATE " + this.replPrefix + "items SET trans_key=transId WHERE repl_key=replKey;\n");
      
      // clean up (close) the used lobs. Note that if the table contains longs then the
      // newClob has not been opened and shall therefore not be closed.
      if (containsLongs) {
         buf.append("    IF NOT INSERTING THEN\n");
         buf.append("       dbms_lob.close(oldCont);\n");
         buf.append("       dbms_lob.freetemporary(oldCont);\n");
         buf.append("    END IF;\n");
      }
      else {
         buf.append("    IF INSERTING THEN\n");
         buf.append("       dbms_lob.close(newCont);\n");
         buf.append("       dbms_lob.freetemporary(newCont);\n");
         buf.append("    ELSIF DELETING THEN\n");
         buf.append("       dbms_lob.close(oldCont);\n");
         buf.append("       dbms_lob.freetemporary(oldCont);\n");
         buf.append("    ELSE\n");
         buf.append("       dbms_lob.close(oldCont);\n");
         buf.append("       dbms_lob.close(newCont);\n");
         buf.append("       dbms_lob.freetemporary(oldCont);\n");
         buf.append("       dbms_lob.freetemporary(newCont);\n");
         buf.append("    END IF;\n");
      }
      buf.append("END ").append(triggerName).append(";\n");
      buf.append("\n");
      return buf.toString();
   }
   
   private final boolean cleanupType(String schema, String objName, String sql, String postfix) {
      Connection conn = null;
      try {
         conn = this.dbPool.reserve();
         conn.setAutoCommit(true);
         List names = new ArrayList();
         log.info(sql);
         Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery(sql);
         while (rs.next()) {
            names.add(rs.getString(1));
         }
         st.close();
         rs.close();
         st = null;
         rs = null;
         for (int i = 0; i < names.size(); i++) {
            String name = (String) names.get(i);
            if (name != null) {
               sql = "DROP " + objName + " " + name + postfix;
               log.info(sql);
               st = conn.createStatement();
               try {
                  st.executeUpdate(sql);
               }
               catch (Exception e) {
                  e.printStackTrace();
               }
               finally {
                  st.close();
                  st = null;
               }
            }
         }
         return true;
      }
      catch (Exception ex) {
         ex.printStackTrace();
         conn = removeFromPool(conn, ROLLBACK_NO);
         return false;
      }
      finally {
         conn = releaseIntoPool(conn, COMMIT_NO);
      }
   }

   /**
    * Cleans up the specified schema for the specified type.
    * @param schema can not be null.
    * @param type can be null. If null all types are cleaned up, otherwise only the ones contained in the string will be cleaned up.
    * For example "table alltriggers" will clean up both 'table' and 'trigger' types. The types must be specified in lowercase.
    * Allowed types are synonym,trigger,package,procedure,function,view,table,sequence.
    * @param referencedSchema is the schema which is referenced by the object. It only has an effect on triggers where the 
    * owner of the trigger would be the schema but the table on which the trigger resides it the referenced schema. If null, all
    * schemas referenced are deleted. 
    */
   public void cleanupSchema(String schema, String type, String referencedSchema) {
      String sql = "SELECT synonym_name FROM all_synonyms WHERE owner='" + schema + "'";
      if (type == null || type.indexOf("synonym") != -1)
         cleanupType(schema, "synonym", sql, "");
      if (referencedSchema == null || referencedSchema.trim().length() < 1)
         sql = "SELECT trigger_name FROM all_triggers WHERE owner='" + schema + "'";
      else
         sql = "SELECT trigger_name FROM all_triggers WHERE owner='" + schema + "' AND table_owner='" + referencedSchema + "'";
      if (type == null || type.indexOf("trigger") != -1)
         cleanupType(schema, "trigger", sql, "");
      // sql = "SELECT name FROM all_source WHERE owner='" + schema + "' AND
      // LINE=1";
      // cleanupType(schema, "function", sql, "");
      sql = "SELECT NAME FROM all_source WHERE owner='" + schema + "' AND type='PACKAGE' AND LINE=1";
      if (type == null || type.indexOf("package") != -1)
         cleanupType(schema, "package", sql, "");
      sql = "SELECT NAME FROM all_source WHERE owner='" + schema + "' AND type='PROCEDURE' AND LINE=1";
      if (type == null || type.indexOf("procedure") != -1)
         cleanupType(schema, "procedure", sql, "");
      sql = "SELECT NAME FROM all_source WHERE owner='" + schema + "' AND type='FUNCTION' AND LINE=1";
      if (type == null || type.indexOf("function") != -1)
         cleanupType(schema, "function", sql, "");
      // sql = "SELECT procedure_name FROM all_procedures WHERE owner='" +
      // schema + "'";
      // cleanupType(schema, "function", sql, "");
      // sql = "SELECT procedure_name FROM all_procedures WHERE owner='" +
      // schema + "'";
      // cleanupType(schema, "procedure", sql, "");
      sql = "SELECT view_name FROM all_views WHERE owner='" + schema + "'";
      if (type == null || type.indexOf("view") != -1)
         cleanupType(schema, "view", sql, " CASCADE CONSTRAINTS");
      sql = "SELECT table_name FROM all_tables WHERE owner='" + schema + "'";
      if (type == null || type.indexOf("table") != -1)
         cleanupType(schema, "table", sql, " CASCADE CONSTRAINTS");
      sql = "SELECT sequence_name FROM all_sequences WHERE sequence_owner='" + schema + "'";
      if (type == null || type.indexOf("sequence") != -1)
         cleanupType(schema, "sequence", sql, "");
   }

   /**
    * Helper method used to construct the CREATE TABLE statement part belonging
    * to a single COLUMN.
    * 
    * There is currently no way to distinguish the following:
    * <ul>
    * <li>DECIMAL from SMALLINT and INTEGER (they are all threaded as INTEGER)</li>
    * <li>CHAR are all threated the same, so: CHAR(10) is the same as CHAR(10
    * BYTE) which is the same as CHAR(10 CHAR)</li>
    * <li></li>
    * 
    * </ul>
    * 
    * @param colInfoDescription
    * @return
    */
   public StringBuffer getColumnStatement(SqlColumn colInfoDescription) {
      StringBuffer buf = new StringBuffer(colInfoDescription.getColName());
      buf.append(" ");
      String type = colInfoDescription.getType();
      int precision = colInfoDescription.getPrecision();
      int sqlType = colInfoDescription.getSqlType();
      if (sqlType == Types.CHAR || sqlType == Types.VARCHAR) {
         buf.append(type).append("(").append(precision).append(")");
      }
      else if (sqlType == Types.OTHER) {
         if (type.equalsIgnoreCase("NCHAR")) { // two bytes per character
            buf.append(type);
            if (precision > 0) buf.append("(").append(precision).append(")");
         }
         else {
            buf.append(type);
         }
      }
      else if (sqlType == Types.LONGVARCHAR || sqlType == Types.CLOB || sqlType == Types.BLOB) {
         buf.append(type);
      }
      else if (sqlType == Types.DECIMAL) {
         int scale = colInfoDescription.getScale();
         buf.append(type);
         if (precision > 0) {
            buf.append("(").append(precision);
            if (scale > 0) buf.append(",").append(scale);
            buf.append(")");
         }
      }
      else if (sqlType == Types.FLOAT) {
         buf.append(type).append("(").append(precision).append(")");
      }
      else if (sqlType == Types.DATE) {
         buf.append(type);
      }
      else if (sqlType == Types.VARBINARY) {
         buf.append(type);
         int width = colInfoDescription.getColSize();
         if (width > 0) buf.append("(").append(width).append(")");
      }
      else if (sqlType == Types.LONGVARBINARY) {
         buf.append(type);
      }
      else {
         buf.append(type);
         /*
          * if (type.equalsIgnoreCase("BFILE")) { // for example BFILE (sqlType =
          * -13) buf.append(type); } else if
          * (type.equalsIgnoreCase("BINARY_FLOAT")) { // binaryfloat (100)
          * buf.append(type); } else if (type.equalsIgnoreCase("BINARY_DOUBLE")) { //
          * binaryfloat (100) buf.append(type); } else { buf.append(type); }
          */
      }
      return buf;
   }

   /**
    */
   public static void main(String[] args) {
      try {
         // System.setProperty("java.util.logging.config.file", "testlog.properties");
         // LogManager.getLogManager().readConfiguration();
         // ---- Database settings -----
         if (System.getProperty("jdbc.drivers", null) == null) {
            System.setProperty("jdbc.drivers", "oracle.jdbc.driver.OracleDriver");
         }
         if (System.getProperty("db.url", null) == null) {
            System.setProperty("db.url", "jdbc:oracle:thin:@localhost:1521:test");
         }
         if (System.getProperty("db.user", null) == null) {
            System.setProperty("db.user", "xmlblaster");
         }
         if (System.getProperty("db.password", null) == null) {
            System.setProperty("db.password", "xbl");
         }
         SpecificOracle oracle = new SpecificOracle();
         I_Info info = new PropertiesInfo(System.getProperties());
         oracle.init(info);
         I_DbPool pool = (I_DbPool) info.getObject("db.pool");
         Connection conn = pool.reserve();
         String objectTypes = info.get("objectTypes", null);
         String schema = info.get("schema", "AIS");
         String referencedSchema = info.get("referencedSchema", null);
         oracle.cleanupSchema(schema, objectTypes, referencedSchema);
         conn = SpecificDefault.releaseIntoPool(conn, COMMIT_NO, pool);
      }
      catch (Throwable e) {
         System.err.println("SEVERE: " + e.toString());
         e.printStackTrace();
      }
   }

   /**
    * If the triggerName is null, then the own schema triggers are deleted. If
    * at least one of the triggers has been removed, it returns true.
    */
   public boolean removeTrigger(String triggerName, String tableName, boolean isSchemaTrigger) {
      boolean ret = false;
      if (triggerName == null) {
         try {
            this.dbPool.update("DROP TRIGGER " + this.replPrefix + "drtg_" + this.ownSchema);
           ret = true;
         }
         catch (Exception ex) {
         }
         try {
            this.dbPool.update("DROP TRIGGER " + this.replPrefix + "altg_" + this.ownSchema);
           ret = true;
         }
         catch (Exception ex) {
         }
         try {
            this.dbPool.update("DROP TRIGGER " + this.replPrefix + "crtg_" + this.ownSchema);
           ret = true;
         }
         catch (Exception ex) {
         }
         return ret;
      }
      try {
         this.dbPool.update("DROP TRIGGER " + triggerName);
         return true;
      }
      catch (Exception ex) {
         return false;
      }
   }
   
   private int cleanupOp(Connection conn, ArrayList names, String schema, String prefix, String postfix) throws Exception {
      int sum = 0;
      for (int i=0; i < names.size(); i++) {
         Statement st = null;
         try {
            String name = (String)names.get(i);
            if (name.indexOf('$') > -1)
               continue;
            if (schema != null)
               name = schema + "." + name;
            // String sql = "DROP TABLE "  + name + " CASCADE CONSTRAINTS";
            String sql = prefix + " " + name + " " + postfix;
            st = conn.createStatement();
            log.fine("statement: " + sql + "' for cleanup");
            sum += st.executeUpdate(sql);
         }
         catch (Exception ex) {
            ex.printStackTrace();
         }
         finally {
            if (st != null)
               st.close();
         }
      }
      return sum;
   }
   
   public int wipeoutSchema(String catalog, String schema, boolean[] objectsToWipeout) throws Exception {
      if (objectsToWipeout == null)
         objectsToWipeout = WIPEOUT_ALL;
      int sum = 0;
      int count = 0;
      int maxCount = 50;
      int oldSum = 0;
      while (count < maxCount) {
         sum = wipeoutSchemaSingleSweep(catalog, schema, objectsToWipeout);
         count++;
         log.info("sweep '" + count + "' for schema '" + schema + "' has erased '" + sum + "' objects");
         if (sum == oldSum)
            break;
         oldSum = sum;
      }
      if (sum != 0)
         log.warning("Could not clean up properly all objects belonging to '" + schema + "' '" + sum + "' objects remain. Continuing anyway");
      if (count == maxCount)
         throw new Exception("Could not clean up complete schema '" + schema + "' after maximum sweeps '" + maxCount + "'. Processed/deleted '" + sum + "' objects");
      return sum;
   }

   /**
    * 
    * @param conn
    * @param st
    * @param rs
    * @return
    * @throws SQLException
    */
   private int invokeListStatement(Connection conn, List names, String sql) throws SQLException {
      Statement st = null;
      ResultSet rs = null;
      
      try {
         st = conn.createStatement();
         rs = st.executeQuery(sql);
         while (rs.next())