xmldatabase.java

导出ORACLE数据库对象DDL语句的程序

package com.icbcsdc.ddlexp.pub.xml.nodeinfo;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Hashtable;

import com.icbcsdc.ddlexp.pub.staticLog.Logger;
import com.icbcsdc.ddlexp.pub.util.MessageBox;
import com.icbcsdc.ddlexp.pub.util.StrUtil;
import com.icbcsdc.ddlexp.ui.CompareFrame;
import com.icbcsdc.ddlexp.pub.connectionpool.JDBCCnn;
import com.icbcsdc.ddlexp.pub.connectionpool.JDBCException;
import com.icbcsdc.ddlexp.pub.connectionpool.JDBCPool;
import com.icbcsdc.ddlexp.pub.connectionpool.JDBCPoolManager;


/**
 * @author zhangyc
 *
 * To change this generated comment edit the template variable "typecomment":
 * Window>Preferences>Java>Templates.
 * To enable and disable the creation of type comments go to
 * Window>Preferences>Java>Code Generation.
 */
public class XMLDatabase extends XMLNode{
	private String ORACLE_HOME="";
	private String ORACLE_BASE="";
	private String DATA_DIR="";
	private static String CreateDB="CreateDB.sql";
	private static String CreateDBFiles="CreateDBFiles.sql";
	private static String CreateDBCatalog="CreateDBCatalog.sql";
	private static String postDBCreation="postDBCreation.sql";
	
	public static final int WINDOWS=1;
	public static final int UNIX=2;
	
	private static String getLogsSQL="SELECT 'GROUP '||A.GROUP#||'(''',A.MEMBER,''') SIZE '||B.BYTES/1024||'K' FROM V$LOGFILE A, V$LOG B WHERE  A.GROUP#=B.GROUP#";
	private static String getCharSetSQL="SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET'";
	private static String getNationalCharSetSQL=" SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET'";
	private static String getTempTbsSQL="select TEMPORARY_TABLESPACE from dba_users where username='SYSTEM'";
	private static String getUndoTbsSQL="select VALUE from v$parameter where NAME='undo_tablespace'";
	private static String getPlatformSQL="select * from v$version where upper(banner) like '%WIN%'";
	private static String getVersionOracle8i="select * from v$version where banner like 'Oracle8i%'";
	private static String getVersionOracle9i="select * from v$version where banner like 'Oracle9i%'";
		
	private int platform=UNIX;
	private int version = VERSION_UNKNOWN;
	private String status=	STATUS_UNKNOWN;
	
	private static String systemTbsName="SYSTEM";
	String tempTbsName=null;
	String undoTbsName=null;
	String fileSeperator="\\";	

	public static final String STATUS_UNKNOWN="UNKNOWN";
	public static final String STATUS_OPEN="OPEN";
	
	public static final int VERSION_ORACLE9I=1;
	public static final int VERSION_ORACLE8I=2;
	public static final int VERSION_UNKNOWN=3;
	
	//数据库组件 for Oracle9i
	public static final int COMPNT_9I_SPATIAL = 1;
	public static final int COMPNT_9I_ULTRASEARCH = 2;
	public static final int COMPNT_9I_LABELSECURITY = 3;
	public static final int COMPNT_9I_DATAMINING = 4;
	public static final int COMPNT_9I_OLAP = 5;
	public static final int COMPNT_9I_EXAMPLESCHEMAS = 6;
	public static final int COMPNT_9I_JVM = 7;
	public static final int COMPNT_9I_INTERMEDIA = 8;
	public static final int COMPNT_9I_TEXT = 9;
	public static final int COMPNT_9I_XML = 10;
	
	//数据库组件 for Oracle8i
	public static final int COMPNT_8I_TIMESERIES = 11;
	public static final int COMPNT_8I_SPATIAL = 12;
	public static final int COMPNT_8I_JSERVER = 13;
	public static final int COMPNT_8I_INTERMEDIA = 14;
	public static final int COMPNT_8I_VISUALINFORMATIONRETRIEVAL = 15;
	public static final int COMPNT_8I_ADVANCEDREPLICATION = 16;
	public static final int COMPNT_8I_SQLPLUSHELP = 17;
	
	public static Hashtable comp2StrContainer=new Hashtable();
	static{
		comp2StrContainer.put(new Integer(COMPNT_9I_SPATIAL),"Spatial");
		comp2StrContainer.put(new Integer(COMPNT_9I_ULTRASEARCH),"Ultra Search");
		comp2StrContainer.put(new Integer(COMPNT_9I_LABELSECURITY),"Label Security");
		comp2StrContainer.put(new Integer(COMPNT_9I_DATAMINING),"Data Mining");
		comp2StrContainer.put(new Integer(COMPNT_9I_OLAP),"OLAP");
		comp2StrContainer.put(new Integer(COMPNT_9I_EXAMPLESCHEMAS),"Example schemas");
		comp2StrContainer.put(new Integer(COMPNT_9I_JVM),"JVM");
		comp2StrContainer.put(new Integer(COMPNT_9I_INTERMEDIA),"Intermedia");
		comp2StrContainer.put(new Integer(COMPNT_9I_TEXT),"Context");
		comp2StrContainer.put(new Integer(COMPNT_9I_XML),"XML");
		comp2StrContainer.put(new Integer(COMPNT_8I_TIMESERIES),"Time Series");
		comp2StrContainer.put(new Integer(COMPNT_8I_SPATIAL),"Spatial");
		comp2StrContainer.put(new Integer(COMPNT_8I_JSERVER),"JServer");
		comp2StrContainer.put(new Integer(COMPNT_8I_INTERMEDIA),"Intermedia");
		comp2StrContainer.put(new Integer(COMPNT_8I_VISUALINFORMATIONRETRIEVAL),"Visual Information Retrieval");
		comp2StrContainer.put(new Integer(COMPNT_8I_ADVANCEDREPLICATION),"Advanced Replication");
		comp2StrContainer.put(new Integer(COMPNT_8I_SQLPLUSHELP),"SQL*PLUS Help");
	}
	
//	public ArrayList component = new ArrayList();
	public Object[][] component = null;
	
	String driverClassName = "oracle.jdbc.driver.OracleDriver";
	String url =null;
	String dbUser = null;
	String dbPassword = null;
	String IP=null;
	int port=1521;
	String srvName=null;
	String sqlStatus="SELECT STATUS FROM V$INSTANCE";
	String sqlInit="SELECT '*.'||NAME||'=',VALUE,TYPE FROM V$PARAMETER A WHERE A.ISDEFAULT='FALSE' and A.name not in ('control_files','background_dump_dest','user_dump_dest','core_dump_dest','local_listener','db_domain')";
	
	String srcUrl="";
	String srcUser="";
	String dstUrl="";
	String dstUser="";
	
	public String getDBUser(){
		return dbUser;
	}
	
	public String getDBPassword(){
		return dbPassword;
	}
	/**
	 * @return Returns the driverClassName.
	 */
	public String getDriverClassName() {
		return driverClassName;
	}
	/**
	 * @param driverClassName The driverClassName to set.
	 */
	public void setDriverClassName(String driverClassName) {
		this.driverClassName = driverClassName;
	}
	/**
	 * @return Returns the sqlStatus.
	public String getSqlStatus() {
		return sqlStatus;
	}
	 */

	/**
	 * @param sqlStatus The sqlStatus to set.
	public void setSqlStatus(String sqlStatus) {
		this.sqlStatus = sqlStatus;
	}
	 */

	/**
	 * @return Returns the url.
	 */
	public String getUrl() {
		return url;
	}
	/**
	 * @param url The url to set.
	 */
	public void setUrl(String url) {
		this.url = url;
	}
	/**
	 * @param ip The iP to set.
	 */
	public void setIP(String ip) {
		IP = ip;
	}
	/**
	 * @param port The port to set.
	 */
	public void setPort(int port) {
		this.port = port;
	}
	/**
	 * @param srvName The srvName to set.
	 */
	public void setSrvName(String srvName) {
		this.srvName = srvName;
	}
	
	/**
	 * Constructor for XMLDatabase.
	 */
	public XMLDatabase(String ip,int port,String srvName, String dbUser,String dbPassword) throws ClassNotFoundException, NodeException {
		super();
		this.driverClassName="oracle.jdbc.driver.OracleDriver";
		this.IP=ip;
		this.port=port;
		this.srvName=srvName;
		this.url= "jdbc:oracle:thin:@"+this.IP+":"+this.port+":"+srvName;
		this.dbUser=dbUser;
		this.dbPassword=dbPassword;
		this.name=srvName+"["+ip+"]";

//	    Logger.log(Logger.DEBUG,"测试："+XMLDatabase.comp2StrContainer.get(new Integer(1)));
	}


		
	public JDBCPool getConnPool() throws JDBCException{
		JDBCPool jcnn=null;		
		JDBCPoolManager manager=JDBCPoolManager.getInstance();

		jcnn=manager.getJDBCPool(driverClassName,url,dbUser,dbPassword);

		return jcnn;
	}

	/**
	 * Manager类型的节点下面只能添加Database类型的节点
	 * */
	public boolean chkChildType(XMLNode node){
		if(node.getType()==XMLNode.XML_SCHEMAS) 
			return true;
		if(node.getType()==XMLNode.XML_SECURITY) 
			return true;
		if(node.getType()==XMLNode.XML_STORAGE) 
			return true;
			
		return false;
	}

	
	public int getType(){
		return XMLNode.XML_DATABASE;
	}


	/**从数据库中获取数据对象比较后的结果*/
	public void refreshChangedProperties()throws Exception{
		String sqlstr="select * from compare_db where rownum<2";
		Logger.log(Logger.INFO,"refreshChangedProperties:"+sqlstr);
		
		JDBCPool pool=this.getConnPool();
		java.sql.ResultSet rs=pool.executeQuery(sqlstr);
		
		//SRC_DB_NAME,SRC_OWNER,DST_DB_NAME,DST_OWNER
		if(rs.next()){
			this.setSrcUrl(rs.getString("SRC_DB_NAME"));
			this.setSrcUser(rs.getString("SRC_OWNER"));
			this.setDstUrl(rs.getString("DST_DB_NAME"));
			this.setDstUser(rs.getString("DST_OWNER"));
		}
		
		Logger.log(Logger.INFO,"refreshChangedProperties source url:"+this.getSrcUrl());
		Logger.log(Logger.INFO,"refreshChangedProperties source user:"+this.getSrcUser());
		Logger.log(Logger.INFO,"refreshChangedProperties dest url:"+this.getDstUrl());
		Logger.log(Logger.INFO,"refreshChangedProperties dest user:"+this.getDstUser());
		
		XMLSchemas schemas=new XMLSchemas();
		CompareFrame.getInstance().setResultXMLDB();
		schemas.setCompareStatus(ComparedNode.COMPARED);
		this.addChild(schemas);
	}
	
	/**从数据库中获取数据对象定义*/
	public void refreshDDLInfo()throws Exception{
		setVersion();
		XMLSchemas schemas=new XMLSchemas();
		this.addChild(schemas);
		
		XMLSecurity security=new XMLSecurity();
		this.addChild(security);
		
		XMLStorage storage=new XMLStorage();
		this.addChild(storage);
	}


	/**
	 * 更新schema列表，存储对象列表，安全性列表
	 * */

	public void refresh() throws Exception{
		Logger.log(Logger.INFO,"refresh XMLDatabase "+this.getName());
		refreshNodeNeedAccessDB();		
		super.refresh();
	}



	
	public static void main(String[] args) {
	}
	/**
	 * Returns the iP.
	 * @return String
	 */
	public String getIP() {
		return IP;
	}

	/**
	 * Returns the port.
	 * @return int
	 */
	public int getPort() {
		return port;
	}

	/**
	 * Returns the srvName.
	 * @return String
	 */
	public String getSrvName() {
		return srvName;
	}

	public boolean canDoubleClick(){
		return  false;
	}
	
	public void setStatus() throws Exception{
		JDBCPool cnn=null;
		ResultSet rs=null;
		try {
			System.out.println("start call setStatus");
			if(JDBCPoolManager.getInstance().isDebug()) 
				System.out.println(this.getClass().toString());
				
			cnn=this.getConnPool();
			rs=cnn.executeQuery(sqlStatus);
			if(rs.next()&&rs.getString(1).equals("OPEN")){				
				this.status =STATUS_OPEN;
			}else{
				this.status =STATUS_UNKNOWN;	
			}	
			rs.close();
			System.out.println("end call setStatus");
		} catch (Exception e) {
			System.out.println("end call setStatus");
			throw e;
		}	
	}

	/**
	 * Returns the 返回定义导出语句.
	 * @return String
	 */
	public String getSqlDDL(){
		return null;	
	}
	

	/**
	 * @param dbPassword The dbPassword to set.
	 */
	public void setDbPassword(String dbPassword) {
		this.dbPassword = dbPassword;
	}

	/**
	 * @param dbUser The dbUser to set.
	 */
	public void setDbUser(String dbUser) {
		this.dbUser = dbUser;
	}
	
		/**将DDL语句写入文件*/
	public void writeDDLToFil(File scriptFile) throws Exception {
		BufferedWriter datIn=new BufferedWriter(new FileWriter(scriptFile,true));

		//write storage
		File storeFile=new File(scriptFile.getParent(),"storage.sql");
		if((this.getDirectChildren(XMLNode.XML_STORAGE,XMLStorage.NAME)).isSelected()){
			(this.getDirectChildren(XMLNode.XML_STORAGE,XMLStorage.NAME)).writeDDLToFil(storeFile);
			datIn.write("@storage.sql");	
			datIn.write("\n");		
		}
		
		//write security
		File secureFile=new File(scriptFile.getParent(),"security.sql");
		if((this.getDirectChildren(XMLNode.XML_SECURITY,XMLSecurity.NAME)).isSelected()){
			(this.getDirectChildren(XMLNode.XML_SECURITY,XMLSecurity.NAME)).writeDDLToFil(secureFile);
			datIn.write("@security.sql");	
			datIn.write("\n");		
		}
		
		//write schemas
		File schemasFile=new File(scriptFile.getParent(),"schemas.sql");
		if((this.getDirectChildren(XMLNode.XML_SCHEMAS,XMLSchemas.NAME)).isSelected()){
			(this.getDirectChildren(XMLNode.XML_SCHEMAS,XMLSchemas.NAME)).writeDDLToFil(schemasFile);
			datIn.write("@schemas.sql");	
			datIn.write("\n");						
		}
		
		datIn.close();
	}

	private void crInitFile(String datDir) throws Exception{
		JDBCPool cnn=null;
		ResultSet rs=null;
		File fileName=new File(datDir,"init"+this.srvName+".ora");
		FileWriter writer=new  FileWriter(fileName);			

		try {
			if(JDBCPoolManager.getInstance().isDebug()) 
				System.out.println(this.getClass().toString());
				
			cnn=this.getConnPool();
			rs=cnn.executeQuery(sqlInit);
			while(rs.next()){
				if(rs.getInt(3)==2) writer.write(rs.getString(1)+"'"+rs.getString(2)+"'");
				else writer.write(rs.getString(1)+rs.getString(2));
				writer.write("\n");
			}
			rs.close();
			
		} catch (Exception e) {
			writer.close();
		}				
		String controlfiles="*.control_files=(";
		String udump="*.user_dump_dest=";