xmltables.java

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

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

import java.io.File;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Hashtable;

import com.icbcsdc.ddlexp.pub.staticLog.Logger;
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 XMLTables extends XMLNode {
	public static final String NAME="Tables";
	Hashtable refTables=new Hashtable();
	boolean[][] refRelation=null;
	ArrayList sortedTabs=new ArrayList();

	String sqlTables="SELECT TABLE_NAME,TABLESPACE_NAME FROM DBA_TABLES WHERE OWNER=? ORDER BY TABLE_NAME";
	String sqlRefRelations="SELECT TAB_REF.table_name,TAB.table_name FROM DBA_CONSTRAINTS TAB,DBA_CONSTRAINTS TAB_REF WHERE TAB.CONSTRAINT_TYPE='R' AND TAB.r_constraint_name=TAB_REF.constraint_name AND TAB_REF.owner=TAB.owner AND TAB.owner=?";
	String sqlIsolatedTabs="SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER=? MINUS(SELECT table_name FROM DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE='R' AND OWNER=? union SELECT table_name FROM DBA_CONSTRAINTS TAB_REF where TAB_REF.owner=? AND CONSTRAINT_NAME in (select R_CONSTRAINT_NAME FROM DBA_CONSTRAINTS TAB WHERE CONSTRAINT_TYPE='R' AND TAB.owner=?))";
	String sqlRefTables="SELECT table_name FROM DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE='R' AND OWNER=? union SELECT table_name FROM DBA_CONSTRAINTS TAB_REF where TAB_REF.owner=? AND CONSTRAINT_NAME in (select R_CONSTRAINT_NAME FROM DBA_CONSTRAINTS TAB WHERE CONSTRAINT_TYPE='R' AND TAB.owner=? )";

	String crComparedTable="SELECT SRC_TABLE_NAME,ATTR_NAME,SRC_ATTR_VALUE,DST_TABLE_NAME FROM COMPARE_TABLES WHERE SRC_OWNER=? AND ATTR_NAME='MYSTYLE'";
	String upComparedTable="SELECT SRC_TABLE_NAME,ATTR_NAME,SRC_ATTR_VALUE,DST_ATTR_VALUE FROM COMPARE_TABLES WHERE SRC_OWNER=? AND ATTR_NAME<>'MYSTYLE'";	
	
	public XMLTables(){
		super();
		this.name=NAME;
	} 
	
	/**
	 * @see com.icbcsdc.ddlexp.pub.xml.nodeinfo.XMLNode#getType()
	 */
	public int getType() {
		return XMLNode.XML_TABLES;
	}

	/**
	 * @see com.icbcsdc.ddlexp.pub.xml.nodeinfo.XMLNode#refresh()
	 */
	public void refreshDDLInfo() throws Exception{
		//this.clear();
		JDBCPool cnn=null;
		//try {
			//XMLManager.getInstance().
		XMLDatabase db=this.getDatabase();
		if(db!=null) cnn = db.getConnPool();
		else return;

		if(db.getVersion() == XMLDatabase.VERSION_ORACLE8I){
			String sqlGenDDL = "BEGIN GEN_TAB_DDL(?); END;";
			CallableStatement cStmt = cnn.prepareCall(sqlGenDDL);
			cStmt.setString(1,this.getSchema().name);
			cStmt.execute();
		}

		//获取schema列表
		PreparedStatement pStmt=cnn.prepareStatement(sqlTables);
		pStmt.setString(1,this.getParent().getName().toUpperCase());
		ResultSet rsTables=pStmt.executeQuery();

		while(rsTables.next()){
			XMLTable table=new XMLTable(rsTables.getString(1),rsTables.getString(2));
			//System.out.println(rsTables.getString(1));
			this.addChild(table);
			//table.refresh();
		}
		
		rsTables.close();
		pStmt.close();
		/**
		} catch (Exception e) {
			Logger.log(Logger.ERROR,e.getMessage());
		}
		**/
		//super.refresh();
	}

	/**从数据库中获取数据对象比较后的结果*/
	public void refreshChangedProperties()throws Exception{
		JDBCPool cnn=null;

			XMLDatabase db=this.getDatabase();
			if(JDBCPoolManager.getInstance().isDebug()) 
				System.out.println(this.getClass().toString());
				
			if(db!=null) cnn = db.getConnPool();
			else return;

			//先创建比较节点
			PreparedStatement pStmt=cnn.prepareStatement(crComparedTable);
			pStmt.setString(1,this.getParent().getName().toUpperCase());
			ResultSet rsCrTables=pStmt.executeQuery();
			while(rsCrTables.next()){
				XMLTable table=null;
				byte status=XMLNode.toCompareStatus(rsCrTables.getString("SRC_ATTR_VALUE"));
				if(status==XMLNode.SRC_NULL_DST_EXIST)
					table=new XMLTable(rsCrTables.getString("DST_TABLE_NAME"));
				else
					table=new XMLTable(rsCrTables.getString("SRC_TABLE_NAME"));

				table.setCompareStatus(status);
				this.addChild(table);

			}
			rsCrTables.close();

			//再修改比较节点
			pStmt=cnn.prepareStatement(upComparedTable);
			pStmt.setString(1,this.getParent().getName().toUpperCase());
			ResultSet rsUpTables=pStmt.executeQuery();
			while(rsUpTables.next()){
				XMLTable table1=(XMLTable)this.getDirectChildren(XMLNode.XML_TABLE,rsUpTables.getString("SRC_TABLE_NAME"));
				table1.putChangedProperty(rsUpTables.getString("ATTR_NAME"),rsUpTables.getString("SRC_ATTR_VALUE"),rsUpTables.getString("DST_ATTR_VALUE"));				
			}

			rsUpTables.close();
			pStmt.close();

	}
	
	/**
	 * @see com.icbcsdc.ddlexp.pub.xml.nodeinfo.XMLNode#refresh()
	 */
	public void refresh() throws Exception{
		refreshNodeNeedAccessDB();		
		super.refresh();
	}

	/**
	 * @see com.icbcsdc.ddlexp.pub.xml.nodeinfo.XMLNode#chkChildType(XMLNode)
	 */
	public boolean chkChildType(XMLNode node) {
		if(node.getType()==XMLNode.XML_TABLE) return true;
		return false;
	}

	/**
	 * @see com.icbcsdc.ddlexp.pub.xml.nodeinfo.XMLNode#getSqlDDL()
	 */
	public String getSqlDDL() {
		
		return null;
	}

	private void setRefTables() throws Exception{
		JDBCPool cnn=null;
		refTables.clear();
		//第三步：获取可用的空闲连接
		try {
			XMLDatabase db=this.getDatabase();
			if(JDBCPoolManager.getInstance().isDebug()) 
				System.out.println(this.getClass().toString());
				
			if(db!=null) cnn = db.getConnPool();
			else return;

			PreparedStatement pStmt=cnn.prepareStatement(sqlRefTables);
			pStmt.setString(1,this.getParent().getName().toUpperCase());
			pStmt.setString(2,this.getParent().getName().toUpperCase());
			pStmt.setString(3,this.getParent().getName().toUpperCase());

			ResultSet rs=pStmt.executeQuery();
			int index=0;
			//System.out.println("in setRefTables");
			while(rs.next()){
				refTables.put(rs.getString(1),new Integer(index));
				//System.out.println(rs.getString(1)+index);
				index++;
			}		
			rs.close();
			pStmt.close();

		} catch (Exception e) {
			throw e;
		}	
	}

	private void setRelations() throws Exception{
		JDBCPool cnn=null;
		
		this.setRefTables();
				
		int relSize=refTables.size();
		if(relSize<=0) return;
		refRelation=new boolean[relSize][relSize];
		
		//第三步：获取可用的空闲连接
		try {
			XMLDatabase db=this.getDatabase();
			if(JDBCPoolManager.getInstance().isDebug()) 
				System.out.println(this.getClass().toString());
				
			if(db!=null) cnn = db.getConnPool();
			else return;

			PreparedStatement pStmt=cnn.prepareStatement(sqlRefRelations);
			pStmt.setString(1,this.getParent().getName().toUpperCase());
			ResultSet rs=pStmt.executeQuery();

			while(rs.next()){
				if(refTables.get(rs.getString(1))==null||refTables.get(rs.getString(2))==null) continue;
				int horIndex=((Integer)refTables.get(rs.getString(1))).intValue();
				int verIndex=((Integer)refTables.get(rs.getString(2))).intValue();
				refRelation[horIndex][verIndex]=true;
			}			

			rs.close();
		} catch (Exception e) {
			throw e;
		}				
	}

	public void writeDDLToFil(File scriptFile) throws Exception {
		this.sortTables();
		
		for(int i=0;i<this.sortedTabs.size();i++){
			Logger.log(Logger.INFO,(String)sortedTabs.get(i));
			XMLNode tab=this.getDirectChildren(XMLNode.XML_TABLE,(String)sortedTabs.get(i));
			if(tab.isSelected()) tab.writeDDLToFil(scriptFile);
		}
			
	}

	private void sortTables() throws Exception{
		//STEP1:把所有的孤立点拿出来
		System.out.println("call getIsolatedTables()");
		getIsolatedTables();	
		//printSortedTabs();
		
		//STEP2:构造依赖图
		System.out.println("call setRelations()");
		this.setRelations();
		//printRefTables();
		//printRelations();
		
		//STEP3:处理依赖图
		while(refTables.size()>0){
			Enumeration keys=refTables.keys();			
			while(keys.hasMoreElements()){
				String tabname=(String)keys.nextElement();		
				//处理依赖其他表，但是自己不被依赖的表
				if(this.dependOthers(tabname)&&(!this.dependByOthers(tabname))){
					sortedTabs.add(tabname);
					this.deletePointFromRel(tabname);
					refTables.remove(tabname);
					continue;
				}					
				//处理依赖其他表，自己也不被依赖的表
				if(!this.dependOthers(tabname)&&(!this.dependByOthers(tabname))){
					sortedTabs.add(tabname);
					refTables.remove(tabname);
				}					
			}
			//this.printRelations();
			//System.out.println("reftables size:"+refTables.size());
		}
		
		//System.out.println("sort tables complete...");
		//this.printSortedTabs();
	}
	
	private boolean dependOthers(String tabname){
		int verIndex=((Integer)refTables.get(tabname)).intValue();
		for(int j=0;j<refRelation.length;j++){
			if(refRelation[j][verIndex]) return true;
		}
				
		return false;
	}
	
	private boolean dependByOthers(String tabname){
		int horIndex=((Integer)refTables.get(tabname)).intValue();
		for(int i=0;i<refRelation[horIndex].length;i++){
			if(refRelation[horIndex][i]) return true;
		}
		
		return false;
	}

	private void deletePointFromRel(String tabname){
		int verIndex=((Integer)refTables.get(tabname)).intValue();
		for(int j=0;j<refRelation.length;j++){
			if(refRelation[j][verIndex])refRelation[j][verIndex]=false;
		}	
	}
	
	private void getIsolatedTables() throws Exception{
		JDBCPool cnn=null;
		sortedTabs.clear();
		//第三步：获取可用的空闲连接
		try {
			XMLDatabase db=this.getDatabase();

			if(JDBCPoolManager.getInstance().isDebug()) 
				System.out.println(this.getClass().toString());
				
			if(db!=null) cnn = db.getConnPool();
			else return;

			PreparedStatement pStmt=cnn.prepareStatement(sqlIsolatedTabs);
			pStmt.setString(1,this.getParent().getName().toUpperCase());
			pStmt.setString(2,this.getParent().getName().toUpperCase());
			pStmt.setString(3,this.getParent().getName().toUpperCase());
			pStmt.setString(4,this.getParent().getName().toUpperCase());
			ResultSet rs=pStmt.executeQuery();

			while(rs.next()){
				this.sortedTabs.add(rs.getString(1));
			}			
			rs.close();

		} catch (Exception e) {
			throw e;
		}	
	}
	
	private void printSortedTabs(){
		for(int i=0;i<sortedTabs.size();i++){
			System.out.println(sortedTabs.get(i)+"\t"+i);
		}
	}
	
	private void printRefTables(){
		Enumeration keys=refTables.keys();
		
		while(keys.hasMoreElements()){
			String tabname=(String)keys.nextElement();
			System.out.println(tabname+"\t"+((Integer)refTables.get(tabname)).intValue());
		}
	}
	
	private void printRelations(){
		for(int i=0;i<refRelation.length;i++){
			for(int j=0;j<refRelation[i].length;j++){
				if(refRelation[i][j]) System.out.print("true"+"\t");	
				else System.out.print("false"+"\t");
			}
			System.out.println("\n");
		}
		
	}


	public static void main(String[] args) {
	}
}